
Delimiter //
Drop Procedure if Exists LOADER_CONTACTS_NORMALIZE_MAP_COUNTRY_PROVIDED//
 
Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_COUNTRY_PROVIDED (
    In batchnum int
)
Begin
 
    Declare idAdminParams int;
    Set idAdminParams = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);
 
    UPDATE
        LOADER_CONTACTS C
        INNER JOIN LOADER_STATUS S
            ON S.ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
        INNER JOIN ADMIN_PARAMS A
            ON A.ID_ADMIN_PARAMS = idAdminParams
        Left Join DF_COUNTRY SDFC
            On C.DF_COUNTRY_SHORT_CODE= SDFC.DF_COUNTRY_SHORT_CODE
        Left Join DF_COUNTRY LDFC
            On C.DF_COUNTRY_LONG_CODE = LDFC.DF_COUNTRY_LONG_CODE
		
    SET
        C.DF_COUNTRY_ID_DF_COUNTRY = Coalesce(SDFC.ID_DF_COUNTRY, LDFC.ID_DF_COUNTRY, A.DF_COUNTRY_ID_DF_COUNTRY)
    WHERE 
        S.LOADER_STATUS_BATCH_NUM = batchnum
--         AND C.ID_CONTACTS_NEW IS NOT NULL
    ;
    
End //
Delimiter ;