Delimiter !
/**
 * compute BUSINESS_LATE_INTEREST_PREPARE_DATA
 * si @late_interest_debug <> 0 => non purge des tables temporraire utilisé
 * @author Armen
 */ 
Drop Procedure if Exists BUSINESS_LATE_INTEREST_PREPARE_DATA !
 
Create Procedure BUSINESS_LATE_INTEREST_PREPARE_DATA(In idaccounts Int unsigned, In identities Int unsigned)
Begin
    main: begin
-- 		Set @blipd_refdate = AMO_BAL_REFDATE(); -- pas de retor calcul des interets
		Set @blipd_refdate = curdate();


        if idaccounts Is null Then
            Set @blipd_accMinVal = 0;
            Set @blipd_accMaxVal = 4e7;
        Else
            Set @blipd_accMinVal = idaccounts;
            Set @blipd_accMaxVal = idaccounts;
        End if;


        if identities Is null Then
            Set @blipd_enttMinVal = 0;
            Set @blipd_enttMaxVal = 1000;
        Else
            Set @blipd_enttMinVal = identities;
            Set @blipd_enttMaxVal = identities;
        End if;

        Drop Temporary Table If exists TMP_ENTRIES_DUE_4_INTEREST_DATA;
        Create Temporary Table TMP_ENTRIES_DUE_4_INTEREST_DATA (
                    ID_ENTRIES Int Unsigned
                    , index (ID_ENTRIES)
                )
            ;
        Insert Into TMP_ENTRIES_DUE_4_INTEREST_DATA (
                ID_ENTRIES
            )
        -- explain
        Select ved.ID_ENTRIES
        From V_ENTRIES_DUE ved
            Inner Join ACCOUNTS acc
                On ved.ID_ACCOUNTS = acc.ID_ACCOUNTS
        Where
                ved.ID_ACCOUNTS Between @blipd_accMinVal And @blipd_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @blipd_enttMinVal And @blipd_enttMaxVal
                And ved.ID_DF_LANGUAGES = 1 -- dédoulonnage
                And ved.IS_PAYMENT_CENTER = 1 
            ;
    -- les entries à ne pas selectionner (non exigibles)
    -- - les non echu
    -- - les bloqué
    -- - les lettre totaux dont la date du dernier lettrage < date echéance
	-- - avec promesse non echus
    --  
    -- il reste
    -- les entries echu non bloqué sans promesse ou promesse échus
    --  non totalement lettre
    -- ou lettre apres la date echéance
        notOverdueOrBlocked: begin

            -- table pour les entries non echu et ou bloqué ou créditeur
            Drop temporary table if exists TMP_ENTRIES_SEL1;
            Create temporary Table TMP_ENTRIES_SEL1 (
                ENTRIES_ID_ENTRIES Int unsigned not null 
                    -- primary key
                , Index (ENTRIES_ID_ENTRIES )

            );
            Insert Into TMP_ENTRIES_SEL1(ENTRIES_ID_ENTRIES)
            -- explain
            Select  
                ent.ID_ENTRIES
            From
                ENTRIES ent
				Inner Join ENTRIES_has_ACCOUNTS EHA
					On ent.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
					And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
                Inner Join ACCOUNTS acc
                       On EHA.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
                Left Join TMP_ENTRIES_DUE_4_INTEREST_DATA ved
                    On ent.ID_ENTRIES = ved.ID_ENTRIES
            Where
				EHA.ACCOUNTS_ID_ACCOUNTS between @blipd_accMinVal And @blipd_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @blipd_enttMinVal And @blipd_enttMaxVal
				And (
                -- non echu
					ent.ENTRIES_DUE_DATE >= @blipd_refdate
					Or ent.ENTRIES_ENT_CREDIT > 0

					-- modification de la regle
					-- exclusion des écritures non exigibles
					-- exigibles =  ECHUS, non totalement léttré, sans promesse ou promesse ECHUS
                -- @todo le "not exists" peut être contre performant en mysql 5.6
                    Or ved.ID_ENTRIES Is Null
-- 					OR Not Exists (
-- -- 					OR ent.ID_ENTRIES Not In (
-- 						Select ved.ID_ENTRIES 
-- -- 						From V_ENTRIES_DUE ved
-- 						From TMP_ENTRIES_DUE_4_INTEREST_DATA ved
-- 						Where 1 = 1
-- 							And ent.ID_ENTRIES = ved.ID_ENTRIES
-- -- 							and ved.ID_DF_LANGUAGES = 1 -- simple dédoublonnage
--                         Limit 1
-- 					)
				)
				-- 
            ;
        end ;

        fullMatched: begin
            
            -- table pour les lettré totaux dont la date de lettrage < date echéance 
            -- -> mouvement vraissemblablement non echu au moment du lettrage  => sans retard
            Set @blipd_MtchState_Open = (select ID_DF_MATCHINGCODE_STATE from DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY' Limit 1);
            Set @blipd_MtchState_Partial = (select ID_DF_MATCHINGCODE_STATE from DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'PARTIAL MATCHING CODE' Limit 1);
            Drop temporary table if exists TMP_ENTRIES_SEL2;
            Create temporary Table TMP_ENTRIES_SEL2 (
                ENTRIES_ID_ENTRIES Int unsigned not null 
                    -- primary key
                , Index(ENTRIES_ID_ENTRIES )

            );
            Insert Into TMP_ENTRIES_SEL2(ENTRIES_ID_ENTRIES)    
            -- explain
            Select 
                lmc.ID_ENTRIES
            From 
                V_ENTRIES_MATCHINGCODES_LAST lmc
				Inner Join ENTRIES_has_ACCOUNTS EHA
					On lmc.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
					And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
                Inner Join ACCOUNTS acc
                       On EHA.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
--                 LEFT JOIN DF_MATCHINGCODE_STATE mcst
--                     On lmc.DF_MATCH_STAT_ID_DF_MATCH_STAT = mcst.ID_DF_MATCHINGCODE_STATE
            Where 
				EHA.ACCOUNTS_ID_ACCOUNTS  between @blipd_accMinVal And @blipd_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @blipd_enttMinVal And @blipd_enttMaxVal
                -- lettré total
--                 And mcst.DF_MATCHINGCODE_STATE_CLOSED  = 1
                And lmc.DF_MATCH_STAT_ID_DF_MATCH_STAT In (1, 2)
                --  date de letrage avant echéance
                And lmc.MATCHINGCODES_DATE < lmc.ENTRIES_DUE_DATE
            ;

        end ;

        withInterestENtries: begin
            
            -- pour le calcul
            -- on ne selectionne que les entries qui n'apprtiennent pas au 2 cas ci-dessus
			-- et affecté a un compte (ou une entite) avec un taux d'interet paramétré
            Drop temporary table if exists TMP_ENTRIES_SEL3;
            Create temporary Table TMP_ENTRIES_SEL3 (
                ENTRIES_ID_ENTRIES Int unsigned not null 
                    -- primary key
                , Index(ENTRIES_ID_ENTRIES)
            );
            Insert Into TMP_ENTRIES_SEL3(ENTRIES_ID_ENTRIES)

-- 			explain 
            Select 
                ent.ID_ENTRIES
            From
                ENTRIES ent
				Inner Join ENTRIES_has_ACCOUNTS EHAM
					On ent.ID_ENTRIES = EHAM.ENTRIES_ID_ENTRIES
					And EHAM.ENTRIES_PERMANENT_ATTACHEMENT = 1
                Inner Join ACCOUNTS acc
                       On EHAM.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
                Left Join TMP_ENTRIES_SEL1
                    On ent.ID_ENTRIES = TMP_ENTRIES_SEL1.ENTRIES_ID_ENTRIES
                Left Join TMP_ENTRIES_SEL2
                    On ent.ID_ENTRIES = TMP_ENTRIES_SEL2.ENTRIES_ID_ENTRIES
            Where 
				EHAM.ACCOUNTS_ID_ACCOUNTS  between @blipd_accMinVal And @blipd_accMaxVal
                And acc.ENTITIES_ID_ENTITIES Between @blipd_enttMinVal And @blipd_enttMaxVal
-- 				And Exists(
				And ent.ID_ENTRIES  In (
					-- filtre sur les ecriture affectte a des compte 
					-- dont le taux d'interé est paramtré
					--  sur sur le compte sur via l'entitié
					Select 
						eha.ENTRIES_ID_ENTRIES
					From ENTITIES ett
						Inner Join ACCOUNTS acc
							on acc.ENTITIES_ID_ENTITIES = ett.ID_ENTITIES
						Inner Join ENTRIES_has_ACCOUNTS eha
							On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
                            And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
						Inner Join DF_RATE_GRID dfr
							On dfr.ID_DF_RATE_GRID In (acc.DF_RATE_GRID_ID_DF_RATE_GRID
														, ett.DF_RATE_GRID_ID_DF_RATE_GRID
													)
					Where
						ent.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
						And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
-- 					Limit 1
				)
                And TMP_ENTRIES_SEL1.ENTRIES_ID_ENTRIES is null
                And TMP_ENTRIES_SEL2.ENTRIES_ID_ENTRIES is null
            ;


        end ;
        

        -- --------------------------------------------------------
        -- --------------------------------------------------------
        -- TMP_ENTRIES_SEL3 contient l'ensembles des mouvement succeptible
        -- d'etre prise en compte pour le caclul de la penatlité 
        -- --------------------------------------------------------
        -- --------------------------------------------------------

        firstClean: Begin
			if Coalesce(@late_interest_debug, 0) <> 1 Then
				Drop temporary table if exists TMP_ENTRIES_SEL1;
				Drop temporary table if exists TMP_ENTRIES_SEL2;
			End if;
        End ;
        -- -------------------------------------------------------
        -- -------------------------------------------------------
        -- recherche de l'element le plus ancien par groupe
        -- -------------------------------------------------------
        -- -------------------------------------------------------

        oldestInvoicPerMatchCode: Begin
            
            -- determination du mouvement sur lequel
            -- la intèrêt calculé sera appliqué
            -- -> facture la plus ancienne du lettrage
            --    dans la liste des mouvement selectionnés 
			-- -> pour les factures non lettrés c'est la facture elle même
            -- #9689 ce n'est pas uniquement les factures
            --  -> egalement les impayé ou a nouveau au débit
            Drop temporary table if exists TMP_MATCHCOD_MINDT;
            Create temporary Table TMP_MATCHCOD_MINDT (
                MATCHCOD_ID_MATCHCOD Int unsigned not null primary key
                , DUE_DATE Date not null
                , index(DUE_DATE)
            );

            Set @blipd_EntrType_Inv = (select ID_DF_ENTRIES_TYPE from DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'INV' Limit 1);
            Set @blipd_EntrType_Can = (select ID_DF_ENTRIES_TYPE from DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'CAN' Limit 1);
            Set @blipd_EntrType_Bal = (select ID_DF_ENTRIES_TYPE from DF_ENTRIES_TYPE Where DF_ENTRIES_TYPE_CODE = 'BAL' Limit 1);
            -- 1 : selection des écritures ayant lettrage
            Insert Into TMP_MATCHCOD_MINDT(MATCHCOD_ID_MATCHCOD, DUE_DATE)

            -- explain
            SELECT
                EMC.MATCHCOD_ID_MATCHCOD
                , min(ent.ENTRIES_DUE_DATE) DUE_DATE
            FROM
                TMP_ENTRIES_SEL3 TMPES3
                Inner Join ENTRIES ent
                    ON TMPES3.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
                Inner join ENTRIES_MATCHCOD EMC
                   On TMPES3.ENTRIES_ID_ENTRIES = EMC.ENTRIES_ID_ENTRIES
--                 Inner Join DF_ENTRIES_TYPE DFET
--                     On ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = DFET.ID_DF_ENTRIES_TYPE
            Where 
                -- 1 = 1
--                 And (DFET.DF_ENTRIES_TYPE_IS_DEBIT = 1)
--                 And (DFET.DF_ENTRIES_TYPE_ACCOUNTANT = 1)
                -- #9689
                -- ne calculer les interets que sur 
                --  - les factures, annulations
                --  - à nouveau au debit
--                 (DFET.DF_ENTRIES_TYPE_CODE in ('INV', 'CAN', 'BAL'))
                (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE in (@blipd_EntrType_Inv, @blipd_EntrType_Can, @blipd_EntrType_Bal))
                And (ent.ENTRIES_ENT_DEBIT > 0 )

            GROUP BY EMC.MATCHCOD_ID_MATCHCOD
            ;
            -- mouvent débit le plus ancien par lettrage
            --  permetrat de déterminer les mouvement sur lesquel
            -- les pénalité vont s'appliquer
            Drop temporary table if exists TMP_MATCHCOD_MINENTID;
            Create temporary Table TMP_MATCHCOD_MINENTID (
                MATCHCOD_ID_MATCHCOD Int unsigned null
                , ENTRIES_ID_ENTRIES Int unsigned not null 
                , index (MATCHCOD_ID_MATCHCOD)
                , index (ENTRIES_ID_ENTRIES)
            );
            Insert Into TMP_MATCHCOD_MINENTID(MATCHCOD_ID_MATCHCOD, ENTRIES_ID_ENTRIES)
            -- explain
            SELECT
                EMC.MATCHCOD_ID_MATCHCOD
                , min(ent.ID_ENTRIES) ENTRIES_ID_ENTRIES
            FROM
                TMP_ENTRIES_SEL3 TMPES3
                Inner Join ENTRIES ent
                    ON TMPES3.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
                Inner join ENTRIES_MATCHCOD EMC
                   On TMPES3.ENTRIES_ID_ENTRIES = EMC.ENTRIES_ID_ENTRIES
--                 Inner Join DF_ENTRIES_TYPE DFET
--                     On ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = DFET.ID_DF_ENTRIES_TYPE
                Inner Join TMP_MATCHCOD_MINDT MINDT
                    On EMC.MATCHCOD_ID_MATCHCOD = MINDT.MATCHCOD_ID_MATCHCOD
                    And ent.ENTRIES_DUE_DATE = MINDT.DUE_DATE
            Where 
--                 1 = 1
--                 And DFET.DF_ENTRIES_TYPE_IS_DEBIT = 1
--                 And DFET.DF_ENTRIES_TYPE_ACCOUNTANT = 1
                -- #9689
                -- ne calculer les interets que sur 
                --  - les factures, annulations
                --  - à nouveau au debit
--                 (DFET.DF_ENTRIES_TYPE_CODE in ('INV', 'CAN', 'BAL'))
                (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE in (@blipd_EntrType_Inv, @blipd_EntrType_Can, @blipd_EntrType_Bal))
                And (ent.ENTRIES_ENT_DEBIT > 0 )
            GROUP BY EMC.MATCHCOD_ID_MATCHCOD
            ;
            


        End ;
        
        scndClean: begin
			if Coalesce(@late_interest_debug, 0) <> 1 Then
				Drop temporary table if exists TMP_MATCHCOD_MINDT;
			End if;
        end;
        -- ici TMP_MATCHCOD_MINENTID contient les mouvement lettré sur lesquel la pénalité va s'appliquer
        -- il faut aussi prendre en compte les mouvement non lettre
        
        -- --------------------------------------------------------
        -- --------------------------------------------------------
        -- copy des taux de pénalité a appliquer
        -- --------------------------------------------------------
        -- --------------------------------------------------------
        prepareRates: Begin
			-- ne pas faire de delete mains un truncate pour forcer l'initialiser des id
			-- necessaire pour le bon fonctionnement du calcul des pénalités
            delete TMP_LATE_INTEREST_RATES From TMP_LATE_INTEREST_RATES;
--             truncate table TMP_LATE_INTEREST_RATES; -- !!!! auto commit de transaction
            -- table temporaire pour savoir quel base de taux par client
            Drop temporary table if exists TMP_INTERST_ACC_GRID;
            Create temporary table TMP_INTERST_ACC_GRID (
                LATE_INTEREST_NAMEID Int unsigned not null
                , ACCOUNTS_ID_ACCOUNTS Int unsigned not null
                , rate_factor decimal(7,5) default 1
                , rate_add  decimal(5,5) default 0
                , index(LATE_INTEREST_NAMEID)
                , index(ACCOUNTS_ID_ACCOUNTS)
            );
            Insert Into TMP_INTERST_ACC_GRID(LATE_INTEREST_NAMEID, ACCOUNTS_ID_ACCOUNTS)
            -- explain
            Select
                Coalesce(acc.DF_RATE_GRID_ID_DF_RATE_GRID, ent.DF_RATE_GRID_ID_DF_RATE_GRID) nameid
                , acc.ID_ACCOUNTS
                -- todo
                -- ajouter ici les facteur de modification propre au compte
            From
                ACCOUNTS acc
                Inner join ENTITIES ent
                    On acc.ENTITIES_ID_ENTITIES = ent.ID_ENTITIES
            Where
                acc.ID_ACCOUNTS in (
--                 Exists (
                    select eha.ACCOUNTS_ID_ACCOUNTS
                    From ENTRIES_has_ACCOUNTS eha
                        Inner Join TMP_ENTRIES_SEL3 sel3
                            On eha.ENTRIES_ID_ENTRIES = sel3.ENTRIES_ID_ENTRIES
--                             And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
                    Where
                        eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
						And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
--                     Limit 1
                )
            ;
            -- remplissage des la tables des taux

            insert into TMP_LATE_INTEREST_RATES  (
                TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID
                , TMP_LATE_INTEREST_RATES__DATE
                , TMP_LATE_INTEREST_RATES__RATE 
            )
            -- explain
            Select distinct
--                 irt.DF_RATE_GRID_ID_DF_RATE_GRID
                -- on utilise l'id accouts en raison des taux personnalisé
                tmpg.ACCOUNTS_Id_ACCOUNTS 
                , irt.DF_INTEREST_RATE_DATE
                , case when coalesce(tmpg.rate_add, 0) <> 0 then
                    tmpg.rate_add + irt.DF_INTEREST_RATE
                    Else 
                        Case when coalesce(tmpg.rate_factor, 1) <> 1 then
                            tmpg.rate_factor * irt.DF_INTEREST_RATE
                        Else
                            irt.DF_INTEREST_RATE
                        End
                    end /100 ratesrate

            from TMP_INTERST_ACC_GRID tmpg
                Inner Join DF_INTEREST_RATE irt
                    on tmpg.LATE_INTEREST_NAMEID = irt.DF_RATE_GRID_ID_DF_RATE_GRID
            ;
        end;


        -- -------------------------------------------------------
        -- -------------------------------------------------------
        -- copie des données
        -- -------------------------------------------------------
        -- -------------------------------------------------------

        copyingData: begin
            copyingTargetEntries: begin
                -- population des données dans la table de traitement
			-- ne pas faire de delete mains un truncate pour forcer l'initialiser des id
			-- necessaire pour le bon fonctionnement du calcul des pénalités
                Delete TMP_LATE_INTEREST_TRG_DATA From TMP_LATE_INTEREST_TRG_DATA;
--                 truncate table TMP_LATE_INTEREST_TRG_DATA; -- !!!! auto commit de transaction

                -- insertion des donné lettre et non letre correctement trié

                Insert Into TMP_LATE_INTEREST_TRG_DATA(
                    TMP_LATE_INTEREST_TRG_DATA__DATAID -- id de l'ecriture
                    , TMP_LATE_INTEREST_TRG_DATA__GRPID -- regroupement : le lettrage
                    , TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID -- grille de taux, l'id du compte, car regle spécifique à chaque compte
                )

                -- explain
                SELECT
                    ent.ID_ENTRIES
--                     , ent.ID_ENTRIES
					, null MATCHCOD_ID_MATCHCOD
                   , eha.ACCOUNTS_ID_ACCOUNTS
                FROM
                    TMP_ENTRIES_SEL3 TMPES3
                    Inner Join ENTRIES ent
                        ON TMPES3.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
--                     Inner Join DF_ENTRIES_TYPE DFET
--                         On ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = DFET.ID_DF_ENTRIES_TYPE
                    Inner Join ENTRIES_has_ACCOUNTS eha
                        On eha.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
                        And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
                    Left join ENTRIES_MATCHCOD EMC
                       On TMPES3.ENTRIES_ID_ENTRIES = EMC.ENTRIES_ID_ENTRIES
                Where 
--                     1 = 1
--                     And DFET.DF_ENTRIES_TYPE_IS_DEBIT = 1 
--                     And DFET.DF_ENTRIES_TYPE_ACCOUNTANT = 1 
                -- #9689
                -- ne calculer les interets que sur 
                --  - les factures, annulations
                --  - à nouveau au debit
--                     (DFET.DF_ENTRIES_TYPE_CODE in ('INV', 'CAN', 'BAL'))
                    (ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE in (@blipd_EntrType_Inv, @blipd_EntrType_Can, @blipd_EntrType_Bal))
                    And (ent.ENTRIES_ENT_DEBIT > 0 )
                    And EMC.ENTRIES_ID_ENTRIES Is Null
                Union All 
                -- explain
                Select 
					MINENTID.ENTRIES_ID_ENTRIES
-- 					, MINENTID.ENTRIES_ID_ENTRIES
					, MATCHCOD_ID_MATCHCOD
                    , eha.ACCOUNTS_ID_ACCOUNTS
                From TMP_MATCHCOD_MINENTID MINENTID
                    Inner Join ENTRIES_has_ACCOUNTS eha
                        On MINENTID.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
                        And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
                    ;
                

--                 -- mise a jour du champ pour le bareme des taux
--                 -- comme les bareme sont personnalisé par compte
--                 -- on se sert de l'id accounts
--                 Update TMP_LATE_INTEREST_TRG_DATA TRG_DATA
--                     Inner Join ENTRIES_has_ACCOUNTS eha
--                         On eha.ENTRIES_ID_ENTRIES = TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__DATAID
--                 Set 
--                     TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID = eha.ACCOUNTS_ID_ACCOUNTS
--                 ;


            end;
            copyingsrcData: begin
                -- -- remplissage des données de traitement

                -- une limitation mysql empechant d'utilisant 2 fois dans la même requete 
                -- une table temporraire on dois dubliquer la table TMP_ENTRIES_SEL3 TMPES3
                Drop temporary table if exists TMP_ENTRIES_SEL3BIS;
                Create temporary Table TMP_ENTRIES_SEL3BIS (
                    ENTRIES_ID_ENTRIES Int unsigned not null primary key
                );
                Insert Into TMP_ENTRIES_SEL3BIS(ENTRIES_ID_ENTRIES)
                Select ENTRIES_ID_ENTRIES
                From TMP_ENTRIES_SEL3;

            -- ci dessus le truncate n'est plus nécésssaire, la table re trier correctement par la PS de calcul
            			-- ne pas faire de delete mains un truncate pour forcer l'initialiser des id -- 
			-- necessaire pour le bon fonctionnement du calcul des pénalités
--                 truncate table TMP_LATE_INTEREST_SRC_DATA; -- !!!! auto commit de transaction

                Delete TMP_LATE_INTEREST_SRC_DATA From TMP_LATE_INTEREST_SRC_DATA;
                Insert Into TMP_LATE_INTEREST_SRC_DATA (
                    TMP_LATE_INTEREST_SRC_DATA__DATAID  -- id de l'ecriture
                --     , TMP_LATE_INTEREST_SRC_DATA__GRPID
                    , TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID -- id de la donné de regroupement
                    , TMP_LATE_INTEREST_SRC_DATA__DATE, TMP_LATE_INTEREST_SRC_DATA__AMOUNT
                )
                -- explain
                Select 
                    ent.ID_ENTRIES, TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__ID, ent.ENTRIES_DUE_DATE
                    , Coalesce(ent.ENTRIES_ENT_DEBIT, 0) - Coalesce(ent.ENTRIES_ENT_CREDIT, 0) amount
                From
                    TMP_ENTRIES_SEL3 TMPES3
                    Inner Join ENTRIES ent
                        ON TMPES3.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
--                     Inner Join DF_ENTRIES_TYPE DFET
--                         On ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = DFET.ID_DF_ENTRIES_TYPE
                    Inner join ENTRIES_MATCHCOD EMC
                       On TMPES3.ENTRIES_ID_ENTRIES = EMC.ENTRIES_ID_ENTRIES
                    Inner Join TMP_LATE_INTEREST_TRG_DATA TRG_DATA
                        On EMC.MATCHCOD_ID_MATCHCOD = TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__GRPID
                Union All
                -- explain
                Select 
                    ent.ID_ENTRIES, TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__ID, ent.ENTRIES_DUE_DATE
                    , Coalesce(ent.ENTRIES_ENT_DEBIT, 0) - Coalesce(ent.ENTRIES_ENT_CREDIT, 0) amount
                From
                    TMP_ENTRIES_SEL3BIS TMPES3
                    Inner Join ENTRIES ent
                        ON TMPES3.ENTRIES_ID_ENTRIES = ent.ID_ENTRIES
--                     Inner Join DF_ENTRIES_TYPE DFET
--                         On ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = DFET.ID_DF_ENTRIES_TYPE
                    Inner Join TMP_LATE_INTEREST_TRG_DATA TRG_DATA
                        On TMPES3.ENTRIES_ID_ENTRIES = TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__DATAID
                    Left join ENTRIES_MATCHCOD EMC
                       On TMPES3.ENTRIES_ID_ENTRIES = EMC.ENTRIES_ID_ENTRIES
                Where
                    EMC.ENTRIES_ID_ENTRIES is Null

                ;
            end ;

        end ;
        finalCleaning: begin
			if Coalesce(@late_interest_debug, 0) <> 1 Then
				Drop temporary table if exists TMP_MATCHCOD_MINENTID;
				Drop temporary table if exists TMP_ENTRIES_SEL3;
				Drop temporary table if exists TMP_ENTRIES_SEL3BIS;
			End if;
        end ;

    end ;
End !
Delimiter ;
