Delimiter //
/**
 * Find the id of the given sequence
 * 
 */
Drop Procedure If Exists LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_ID_ENT_REF//
Create Procedure LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_ID_ENT_REF(
    In batchnum Int
    , In refSequence smallint
    , In refDataType varchar(25)
)
Begin
    Set @refDataType = Coalesce(refDataType, 'ACCRED_ACCOUNTS');
    Set @refSequence = Coalesce(refSequence, 0);
    Set @batchnum = Coalesce(batchnum, 0);
    Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);
    -- en raison du multi-cible il ne faut pas faire de jointure 
    -- direct entre LOADER_ATTACHEDMEMBERS_REFDATA et LOADER_STATUS
    -- il est nécssire de faire une joiture vers ID_LOADER_ATTACHEDMEMBERS
    lmnmier_main: Begin
        if refSequence Not Between 1 and 5 Then
            leave lmnmier_main;
        End if;

        Set @query = Concat(
                    'Update '
                    ,     'LOADER_STATUS sts '
                    ,     'Inner Join LOADER_ATTACHEDMEMBERS ARD '
                    ,         'On ARD.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS '
                    , 'Set '
                    ,     'ARD.ID_ENTITIES_REF_', @refSequence, ' = ( '
                    ,         'Select '
                                'ENTR.ID_ENTITIES_REF '
                    ,         'From '
                    ,             'ENTITIES_REF ENTR '
                    ,             'Inner Join ENTITIES_REF_TYPES ERT '
                    ,                 'On ENTR.ENT_REF_TYPE_ID_ENT_REF_TYPES = ERT.ID_ENTITIES_REF_TYPES '
                    ,         'Where '
                    ,             'ENTR.ENTITIES_ID_ENTITIES = ? '
                    ,             'And ENTR.ENTITIES_REF_SEQUENCE= ? '
                    ,             'And ERT.ENTITIES_REF_TYPES_CODE = ? '
                    ,     ') '
                    , 'Where '
                    ,     ' sts.LOADER_STATUS_BATCH_NUM = ? '
                )
            ;
        Prepare stmt From @query;
        execute stmt 
            using 
                @idEntities, @refSequence,  @refDataType, @batchnum
            ;
        Deallocate prepare stmt;

    End ;

End//
Delimiter ;