Delimiter //
/**
    In batchnum Int : num du lot
*/

Drop Procedure if exists LOADER_MEMBERSACCRED_REF_DATA_INSERT//

Create Procedure LOADER_MEMBERSACCRED_REF_DATA_INSERT (
    In batchnum int
    , In refSequence smallint
    , out lastRefDataId Int Unsigned
)
Begin
    
        Set @batchnum = batchnum;
        Set @refSequence = refSequence;
        call CASH_AUTOINCREMENT_VALUE('ENTITIES_REF_DATAS', @lastRefDataId);
        Set @query = Concat( ''
                , 'Insert Into ENTITIES_REF_DATAS ( '
                ,     'ID_ENTITIES_REF_DATAS '
                ,     ', ENTITIES_REF_ID_ENTITIES_REF '
                ,     ', ENTITIES_REF_DATAS_CODE '
                , ') '
                , 'Select Distinct '
--                 ,         'ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW + ? ' 
                ,         'ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW + ', @lastRefDataId, ' '  
--                 ,         'ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW, Cast(? as Decimal(10)) ' -- pour debug
                ,         ', ARD.ID_ENTITIES_REF_', @refSequence, ' '
                ,         ', ARD.ACCRED_CODE_', @refSequence, ' '
                , 'From LOADER_STATUS sts '
                ,     'Inner Join LOADER_MEMBERSACCRED ARD '
                ,         'On ARD.LOADER_STATUS_ID_LOADER_STATUS = sts.ID_LOADER_STATUS '
                , 'Where  '
                ,     'sts.LOADER_STATUS_ERRCOUNT = 0 '
                ,     'And ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW Is Not Null '
                ,     'And ARD.ID_ENTITIES_REF_DATAS_', @refSequence, ' Is Null '
                ,     'And sts.LOADER_STATUS_BATCH_NUM = ? '
            )
        ;
        Prepare stmt From @query;
        execute stmt 
            using 
--                 @lastRefDataId, @batchnum
                @batchnum
            ;
        Deallocate prepare stmt;

    Set lastRefDataId = @lastRefDataId;
End //

Delimiter ;
