Delimiter //
/**
 * Author:  armen
 * Created: 31 janv. 2018
 */
Drop Procedure if exists LOADER_MEMBERSACCRED_ENTITIES_HAS_MBR_HAS_REFDATA_INSERT//

Create Procedure LOADER_MEMBERSACCRED_ENTITIES_HAS_MBR_HAS_REFDATA_INSERT (
    In batchnum int
    , In refSequence smallint
    , In lastEnttHsMbrId Int Unsigned
    , In lastRefDataId Int Unsigned
)
Begin

        Set @batchnum = batchnum;
        Set @refSequence = refSequence;
        Set @lastEnttHsMbrId = lastEnttHsMbrId;
        Set @lastRefDataId = lastRefDataId;
--         Set @lastRefDataId1 = lastRefDataId;
--         Set @lastRefDataId2 = lastRefDataId;

        Set @query = Concat( ''
--                     , 'Insert Into ENTT_has_MBRS_has_ENTT_RFDATAS ( '
--                     ,            'ENTT_has_MBRS_ID_ENTT_has_MBRS, ENTT_RF_DATAS_ID_ENTT_RF_DATAS '
--                     ,        ') '
                    , 'Select '
--                     ,     'ARD.ID_ENTITIES_has_MEMBERS_NEW + ? '
                    ,     'ARD.ID_ENTITIES_has_MEMBERS_NEW + ', @lastEnttHsMbrId,  ' '
                        -- ne pas changer l'ordre du coalesce a cause 0 utilisé
--                     ,     ', Coalesce(ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW + ?, ARD.ID_ENTITIES_REF_DATAS_', @refSequence, ') '
                    ,     ', Coalesce(ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW + ', @lastRefDataId ,', ARD.ID_ENTITIES_REF_DATAS_', @refSequence, ') '
--                     ,     ', ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW, Cast(? as Decimal (10)), ARD.ID_ENTITIES_REF_DATAS_', @refSequence, ' ' -- pour debug
                    , '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_has_MEMBERS_NEW Is Not Null '
                    ,     'And ARD.ID_ENTITIES_has_MEMBERS Is Null '
--                     ,     'And Coalesce(ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW + ?, ARD.ID_ENTITIES_REF_DATAS_', @refSequence, ', 0) <> 0 '
                    ,     'And Coalesce(ARD.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW + ', @lastRefDataId ,', ARD.ID_ENTITIES_REF_DATAS_', @refSequence, ', 0) <> 0 '
                    ,     'And sts.LOADER_STATUS_BATCH_NUM = ? '
                )
            ;
-- pour debug
--         Prepare stmt From @query;
--         execute stmt 
--             using 
-- --                 @lastEnttHsMbrId, @lastRefDataId1, @lastRefDataId2, @batchnum
--                 @batchnum
--             ;
--         Deallocate prepare stmt;

        Set @query = Concat( ''
                    , 'Insert Into ENTT_has_MBRS_has_ENTT_RFDATAS ( '
                    ,            'ENTT_has_MBRS_ID_ENTT_has_MBRS, ENTT_RF_DATAS_ID_ENTT_RF_DATAS '
                    ,        ') '
                    , @query
                )
            ;
        Prepare stmt From @query;
        execute stmt 
            using 
--                 @lastEnttHsMbrId, @lastRefDataId1, @lastRefDataId2, @batchnum
                @batchnum
            ;
        Deallocate prepare stmt;
End //

Delimiter ;
