Delimiter //
/**
* 
**/

Drop Procedure if exists LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_MBRS_WITH_CTCREF_PROVIDED//

Create Procedure LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_MBRS_WITH_CTCREF_PROVIDED (
    In batchnum int
)
Begin
    Set @id_admin_params = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);

    Update
        LOADER_STATUS sts
        Inner Join LOADER_ATTACHEDMEMBERS src
            On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS
        Inner Join CONTACTS ctc
            On src.CONTACTS_REF = ctc.CONTACTS_REF
        Inner Join CONTACTS_INDIVIDUAL cti
            On ctc.ID_CONTACTS = cti.CONTACTS_ID_CONTACTS
        Inner Join MEMBERS mbrs
            On cti.ID_CONTACTS_INDIVIDUAL = mbrs.CONTACT_INDIV_ID_CONTACT_INDIV
        Inner Join ADMIN_PARAMS admp
            On mbrs.UNIVERS_ID_UNIVERS = admp.UNIVERS_ID_UNIVERS
    Set
        src.ID_MEMBERS = mbrs.ID_MEMBERS
    Where 
        Trim(Coalesce(src.CONTACTS_REF, '')) <> ''
        And admp.ID_ADMIN_PARAMS = @id_admin_params
        And sts.LOADER_STATUS_BATCH_NUM = batchnum 
    ;
End //
Delimiter ;