Delimiter //
/**
 * Updates the FI_ANA table from views
 * il faut , au préalable, appeler les procédures
 *	- BUSINESS_FI_ANA_INSERT_NEW_ACCOUNTS
 * @author Benoit
 * @ticket #4025
 * en fonction de la modification des champs (ajoutés, modifiés ou supprimés) 
 * requière des sunchronisation avec les procédures : 
 * - BUSINESS_FI_ANA_INSERT_NEW_ACCOUNTS
 * - et/ou BUSINESS_FI_ANA_INSERT_NEW_MEMBERS
 * - et/ou BUSINESS_UPDATE_FIANA_MEMBERS
 * - et/ou BUSINESS_BACKUP_FIANA
 * - et/ou BUSINESS_BACKUP_FIANA_MEMBERS
 * - et/ou BUSINESS_BACKUP_FIANA_MEMBERS_FROM_HISTO
 * - et/ou LOADER_ACCOUNTS_FIANA_INSERT
 *
 * @see BUSINESS_FI_ANA_INSERT_NEW_ACCOUNTS
 * @see BUSINESS_FI_ANA_INSERT_NEW_MEMBERS
 * @see BUSINESS_UPDATE_FIANA_MEMBERS
 * @see BUSINESS_BACKUP_FIANA
 * @see BUSINESS_BACKUP_FIANA_MEMBERS
 * @see BUSINESS_BACKUP_FIANA_MEMBERS_FROM_HISTO
 * @see LOADER_ACCOUNTS_COPY_FIANA_INSERT
 * @see LOADER_ACCOUNTS_COPY_FIANA_BACKUP
 */ 
Drop Procedure if Exists BUSINESS_UPDATE_FIANA//
 
Create Procedure BUSINESS_UPDATE_FIANA(In idaccounts Int unsigned, In identities Int unsigned)
Begin
    Declare refDate DateTime;
    set refdate = AMO_BAL_REFDATE();
-- effet de bord, ne pas modifier la date ici a cause des dates d'échéances
-- 	if refdate = curdate() Then
-- 		set refdate = date_add(refdate, interval 2 MONTH); -- pour les écriture anti-daté (futur)
-- 	end if;

    main: begin
        Set @bfu_idaccounts = idaccounts;
        Set @bfu_identities = identities;
        Set @bfu_refdate = refdate;
#         Set @bfu_refdate = AMO_BAL_REFDATE();
#         Set @bfu_idaccounts = null;

        -- todo doit être en lancé en 1er sinon erreur creation tmp_v_acc_aging_bal_bas_det. Verifier pourquoi
        call BUSINESS_FI_ANA_TMP_ACCOUNTS_ACD_BPDSO(@bfu_refdate, @bfu_idaccounts, @bfu_identities);

        call BUSINESS_FI_ANA_TMP_ACCOUNTS_AGING_BALANCE_BASE(@bfu_refdate, @bfu_idaccounts, @bfu_identities);
        call BUSINESS_FI_ANA_TMP_ACCOUNTS_AGING_BALANCE_BATCH(@bfu_refdate, @bfu_idaccounts, @bfu_identities);


        if idaccounts Is Null Then
            Set @bfu_idaccounts_min = 0;
            Set @bfu_idaccounts_max = 1000000;
        Else
            Set @bfu_idaccounts_min = idaccounts;
            Set @bfu_idaccounts_max = idaccounts;
        End if;

        if identities Is Null Then
            Set @bfu_identities_min = 0;
            Set @bfu_identities_max = 1000;
        Else
            Set @bfu_identities_min = identities;
            Set @bfu_identities_max = identities;
        End if;

 -- filtre appliqué par jointure
        -- Update from view
        updatingRow: begin
            -- explain
            Update 
                FI_ANA F
--                 Left Join tmp_v_acc_aging_bal_bas_det V
--                     On F.ACCOUNTS_ID_ACCOUNTS = V.ID_ACCOUNTS
--                 Left Join tmp_acc_aging_bal_batch V_ACCOUNTS_AGING_BALANCE_BATCH
--                     On F.ACCOUNTS_ID_ACCOUNTS = V_ACCOUNTS_AGING_BALANCE_BATCH.ID_ACCOUNTS
--                 Left Join tmp_accouts_acd_bpdso V_ACCOUNTS_ACD_BPDSO
--                     On F.ACCOUNTS_ID_ACCOUNTS = V_ACCOUNTS_ACD_BPDSO.ID_ACCOUNTS
                Inner Join (
                    tmp_v_acc_aging_bal_bas_det V
                    Inner Join ACCOUNTS acc
                        On V.ID_ACCOUNTS = acc.ID_ACCOUNTS
                    Inner Join ENTITIES ent on acc.ENTITIES_ID_ENTITIES = ent.ID_ENTITIES
                    LEFT JOIN tmp_acc_aging_bal_batch V_ACCOUNTS_AGING_BALANCE_BATCH
                        ON acc.ID_ACCOUNTS = V_ACCOUNTS_AGING_BALANCE_BATCH.ID_ACCOUNTS
                    LEFT JOIN tmp_accouts_acd_bpdso V_ACCOUNTS_ACD_BPDSO
                        ON acc.ID_ACCOUNTS = V_ACCOUNTS_ACD_BPDSO.ID_ACCOUNTS
                )
                    On acc.ID_ACCOUNTS = F.ACCOUNTS_ID_ACCOUNTS
            Set
                F.FI_ANA_NOT_OVERDUE = Coalesce(V.NOT_OVERDUE, 0)
                , F.FI_ANA_UNBOUND_NOT_OVERDUE = Coalesce(V.UNBOUND_NOT_OVERDUE, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD1 = Coalesce(V.P1, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD2 = Coalesce(V.P2, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD3 = Coalesce(V.P3, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD4 = Coalesce(V.P4, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD5 = Coalesce(V.P5, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD6 = Coalesce(V.P6, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD7 = Coalesce(V.P7, 0)
                , F.FI_ANA_AGE_PORTFOLIO_PERIOD8 = Coalesce(V.P8, 0)
                , F.FI_ANA_OUTSTANDING = Coalesce(V.OUTSTANDING, 0)
                , F.FI_ANA_UNBOUND_OUTSTANDING = Coalesce(V.UNBOUND_OUTSTANDING, 0)
--                 , F.FI_ANA_OVERDUE = V.FI_ANA_OVERDUE
                , F.FI_ANA_OVERDUE = Coalesce(V.OVERDUE, 0)
                , F.FI_ANA_UNBOUND_OVERDUE = Coalesce(V.UNBOUND_OVERDUE, 0)
				-- #7664 modif RDC bloqué : ne prendre en compte que l'échu
--                 , F.FI_ANA_FROZEN_AMOUNT = Coalesce(V.`BLOCKED`,0)
                , F.FI_ANA_FROZEN_AMOUNT = Coalesce(V.`BLOCKED_OVERDUE`,0)
                , F.FI_ANA_UNBOUND_FROZEN_AMOUNT = Coalesce(V.`UNBOUND_BLOCKED_OVERDUE`,0)
                , F.FI_ANA_BLOCKED_ALL = Coalesce(V.`BLOCKED`,0)
                , F.FI_ANA_UNBOUND_BLOCKED_ALL = Coalesce(V.`UNBOUND_BLOCKED`,0)
                , F.FI_ANA_BLOCKED_NOTOVERDUE = Coalesce(V.`BLOCKED`,0) - Coalesce(V.`BLOCKED_OVERDUE`,0)
                , F.FI_ANA_UNBOUND_BLOCKED_NOTOVERDUE = Coalesce(V.`UNBOUND_BLOCKED`,0) - Coalesce(V.`UNBOUND_BLOCKED_OVERDUE`,0)

                , F.FI_ANA_DUE = Coalesce(V.DUE, 0)
                , F.FI_ANA_UNBOUND_DUE = Coalesce(V.UNBOUND_DUE, 0)
#                 , F.FI_ANA_DUE = Coalesce(V.OVERDUE - V.BLOCKED_OVERDUE - V.UNBLOCKED_OVERDUE_WTH_PRMESS, 0)
#                 , F.FI_ANA_UNBOUND_DUE = Coalesce(V.UNBOUND_OVERDUE - V.UNBOUND_BLOCKED_OVERDUE - V.UNBOUND_UNBLOCKED_OVERDUE_WTH_PRMESS, 0)


                , F.FI_ANA_AGE_ACCOUNT_PERIOD1 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.P1, 0)
                , F.FI_ANA_AGE_ACCOUNT_PERIOD2 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.P2, 0)
                , F.FI_ANA_AGE_ACCOUNT_PERIOD3 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.P3, 0)
                , F.FI_ANA_AGE_ACCOUNT_PERIOD4 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.P4, 0)
                , F.FI_ANA_ACD = V_ACCOUNTS_ACD_BPDSO.ACD
                , F.FI_ANA_ACD_LASTUPDAT = @bfu_refdate
                , F.FI_ANA_BPDSO = V_ACCOUNTS_ACD_BPDSO.BPDSO
                , F.FI_ANA_BPDSO_LASTUPDAT = @bfu_refdate
                , F.FI_ANA_AGE_CUSTMMDE_PERIOD1 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.CUSTMMDE_P1, 0)
                , F.FI_ANA_AGE_CUSTMMDE_PERIOD2 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.CUSTMMDE_P2, 0)
                , F.FI_ANA_AGE_CUSTMMDE_PERIOD3 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.CUSTMMDE_P3, 0)
                , F.FI_ANA_AGE_CUSTMMDE_PERIOD4 = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.CUSTMMDE_P4, 0)
                , F.FI_ANA_AGE_CUSTMMDE_DEBIT = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.CUSTMMDE_DEBIT, 0)
                , F.FI_ANA_AGE_CUSTMMDE_CREDIT = Coalesce(V_ACCOUNTS_AGING_BALANCE_BATCH.CUSTMMDE_CREDIT, 0)
            Where 
                F.ACCOUNTS_ID_ACCOUNTS Between @bfu_idaccounts_min And @bfu_idaccounts_max
                And acc.ENTITIES_ID_ENTITIES Between @bfu_identities_min and @bfu_identities_max
            ;
        end ;
        purgeblvk: Begin
            Drop Temporary Table If exists tmp_v_acc_aging_bal_bas_det;
            Drop Temporary Table If exists tmp_acc_aging_bal_batch;
            Drop Temporary Table If exists tmp_accouts_acd_bpdso;
        end ;
-- 
        dependProc: Begin
            -- Managment level
            Call BUSINESS_UPDATE_ACCOUNT_MNGT_LVL(@bfu_idaccounts, @bfu_identities);

            -- @ticket #5082
            call BUSINESS_CALCULATE_SALES_AND_PAYEMENT(@bfu_refdate, 0, @bfu_idaccounts, @bfu_identities);
			-- #7961
            call BUSINESS_FI_ANA_UPDATE_DELIVRED_AND_ORDER(@bfu_refdate, @bfu_idaccounts, @bfu_identities);

            -- DSO Rollback
--             Call BUSINESS_DSO_ROLLBACK;
            
        end ;
    end;

End //
Delimiter ;
