Delimiter //
/**
 * Author:  armen
 * Created: 08 oct. 2021
 */
Drop Procedure if Exists LOADER_ACCOUNTS_NORMALIZE_MAP_ACCOUNTSTATUS_PROVIDED//

Create Procedure LOADER_ACCOUNTS_NORMALIZE_MAP_ACCOUNTSTATUS_PROVIDED (
    In batchnum int
 )
Begin    
    Set @lanmasp_batchnum = batchnum;
    UPDATE
        LOADER_ACCOUNTS src
        INNER JOIN LOADER_STATUS sts
            ON sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS
        LEFT JOIN DF_ACCOUNTS_STATUS dfasts
            ON dfasts.DF_ACCOUNTS_STATUS_CODE = src.DF_ACCOUNTS_STATUS_CODE
    SET
        src.DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS = dfasts.ID_DF_ACCOUNTS_STATUS
        , sts.LOADER_STATUS_WARNCOUNT = (
                sts.LOADER_STATUS_WARNCOUNT
                + (CASE WHEN dfasts.ID_DF_ACCOUNTS_STATUS IS NULL THEN 1 ELSE 0 END)
            )
        , sts.LOADER_STATUS_WARNMSGS = 
            (CASE WHEN dfasts.ID_DF_ACCOUNTS_STATUS IS NULL
                THEN LOADER_CONCAT_ERR(sts.LOADER_STATUS_WARNMSGS, Concat('Status de compte inexistant :', src.DF_ACCOUNTS_STATUS_CODE ) )
                ELSE sts.LOADER_STATUS_WARNMSGS
            END)
    WHERE 
        Coalesce(src.DF_ACCOUNTS_STATUS_CODE, '') != ''
        AND sts.LOADER_STATUS_BATCH_NUM = @lanmasp_batchnum
    ;
End //
Delimiter ;