Delimiter //
/**
 * If the Credit limit date is not provided, set as current date
 * 
 * @param int batchnum
 * @author Ben
 */ 
Drop Procedure If Exists LOADER_CREDITLIMIT_NORMALIZE_MAP_DATE_NOT_PROVIDED//
 
Create Procedure LOADER_CREDITLIMIT_NORMALIZE_MAP_DATE_NOT_PROVIDED (
    In batchnum Int
)
Begin
 
    Set @lcnmpnp_batchnum = batchnum;
--     UPDATE
--         LOADER_CREDITLIMIT C
--         INNER JOIN LOADER_STATUS sts
--             ON sts.ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
--     SET
--         C.CREDIT_LIMIT_DATE = CURDATE()
--     WHERE 
--         sts.LOADER_STATUS_BATCH_NUM = batchnum
--         AND C.ID_CREDIT_LIMIT_NEW IS NOT NULL
--         AND Coalesce(C.CREDIT_LIMIT_DATE, '') = ''
--     ;
    UPDATE
        LOADER_CREDITLIMIT_ACCOUNTS lca
        Inner Join LOADER_CREDITLIMIT lc
            On lca.LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT = lc.ID_LOADER_CREDITLIMIT
        INNER JOIN LOADER_STATUS sts
            ON sts.ID_LOADER_STATUS = lc.LOADER_STATUS_ID_LOADER_STATUS
    SET
        lc.CREDIT_LIMIT_DATE = CURDATE()
    WHERE 
        sts.LOADER_STATUS_BATCH_NUM = @lcnmpnp_batchnum
        AND lca.ID_CREDIT_LIMIT_NEW IS NOT NULL
        AND Coalesce(lc.CREDIT_LIMIT_DATE, '2000-01-01') <= '2000-01-01'
    ;
    
End //
Delimiter ;