Delimiter //
/**
 * 
 * @param int batchnum
 * @author Armen
 * @ticket #6420
 */ 
Drop Procedure If Exists LOADER_ACCOUNTS_NORMALIZE_MAP_AGENCY_CODE_PROVIDED//
Create Procedure LOADER_ACCOUNTS_NORMALIZE_MAP_AGENCY_CODE_PROVIDED(
    In batchnum Int
)
Begin

    Set @id_entity = GET_LOADER_INFO('ID_ENTITIES', batchnum);

    Update
        LOADER_STATUS LS
        Inner Join LOADER_ACCOUNTS LA
            On LA.LOADER_STATUS_ID_LOADER_STATUS = LS.ID_LOADER_STATUS
        Left Join ENTITIES_AGENCIES EA
            On EA.ENTITIES_AGENCIES_CODE = LA.ENTITIES_AGENCIES_CODE
            And EA.ENTITIES_ID_ENTITIES = @id_entity
    Set
        LA.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES = EA.ID_ENTITIES_AGENCIES
        , LS.LOADER_STATUS_WARNCOUNT = 
            (Case When EA.ID_ENTITIES_AGENCIES Is Null
                Then LS.LOADER_STATUS_WARNCOUNT + 1
                Else LS.LOADER_STATUS_WARNCOUNT
            End)
        , LS.LOADER_STATUS_WARNMSGS = 
            (Case When EA.ID_ENTITIES_AGENCIES Is Null
                Then LOADER_CONCAT_ERR(LS.LOADER_STATUS_WARNMSGS, Concat('Code agence inexistant : "', LA.ENTITIES_AGENCIES_CODE, '"'))
                Else LS.LOADER_STATUS_WARNMSGS
            End)
    Where
        LS.LOADER_STATUS_BATCH_NUM = batchnum 
        And Coalesce(LA.ENTITIES_AGENCIES_CODE, '') != '';
End//
Delimiter ;