Delimiter //
/**
 * If the Guaranteed date is not provided, set as current date
 * 
 * @param int batchnum
 * @author Ben
 */ 
Drop Procedure If Exists LOADER_GUARANTEED_NORMALIZE_MAP_DATE_NOT_PROVIDED//
 
Create Procedure LOADER_GUARANTEED_NORMALIZE_MAP_DATE_NOT_PROVIDED (
    In batchnum Int
)
Begin
 
    UPDATE
        LOADER_GUARANTEED G
        INNER JOIN LOADER_STATUS S
            ON S.ID_LOADER_STATUS = G.LOADER_STATUS_ID_LOADER_STATUS
    SET
        G.GUARANTEED_CREAT_DATE = CURDATE()
    WHERE 
        S.LOADER_STATUS_BATCH_NUM = batchnum
        AND G.ID_GUARANTEED_NEW IS NOT NULL
        AND Coalesce(G.GUARANTEED_CREAT_DATE, '') = ''
    ;
    
End //
Delimiter ;