Delimiter $$
/**
 * compute BUSINESS_COMPUTE_PENALTIES_PREP_ACCOUNTS_RULES_TABLE
 * détermine l'id de la regle des pénalités à appliquer par compte
 * la table temporraire TMP_COMPUTES_PENALTIES_ACCOUNTS doit à avoit été populé
 * elle contient la liste des compted pour lesquels la pénlité est calulé
 *  elle doit posséder un champ ACCOUNTS_ID_ACCOUNTS indexé
 * Cette procédure rempli la table temporaire TMP_PENALTIES_ACCOUNTS_WITH_RULES
 * 
 */ 
Drop Procedure if Exists BUSINESS_COMPUTE_PENALTIES_PREP_ACCOUNTS_RULES_TABLE $$
Create Procedure BUSINESS_COMPUTE_PENALTIES_PREP_ACCOUNTS_RULES_TABLE(In penalityRuleCode varchar(45), In cialDeptTypeId Int Unsigned)
Begin

    cpt_pntl_acc_pntl_rles_tbl_init: begin
		Set @cmptaccpntlpreprltble_cialDeptTypeId = cialDeptTypeId;

		Set @cmptaccpntlpreprltble_idPenRule = (Select ID_DF_PENALTIES_RULES_TYPES From DF_PENALTIES_RULES_TYPES where DF_PENALTIES_RULES_TYPES_CODE = penalityRuleCode);


		Drop Temporary Table If Exists TMP_PENALTIES_ACCOUNTS_WITH_RULES;
		Create Temporary Table TMP_PENALTIES_ACCOUNTS_WITH_RULES (
					ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null Primary Key
					, DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES Int Unsigned Not Null 
					, Index IX_TMP_PENALTIES_ACCOUNTS_WITH_RULES_01(DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES)
				)
			;

    end;
    
	cpt_pntl_acc_pntl_rles_tbl_main: begin
		Insert Into TMP_PENALTIES_ACCOUNTS_WITH_RULES(ACCOUNTS_ID_ACCOUNTS, DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES)
		Select
			ACC.ID_ACCOUNTS
			, Coalesce(ACCDPR.ID_DF_PENALTIES_RULES, ENTDPR.ID_DF_PENALTIES_RULES, 0) DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES
-- 			, ACCDPR.ID_DF_PENALTIES_RULES ID_DF_PENALTIES_RULES__ACC
-- 			, ENTDPR.ID_DF_PENALTIES_RULES ID_DF_PENALTIES_RULES__ENT
		From 
			TMP_COMPUTES_PENALTIES_ACCOUNTS tmpAcc
			Inner Join ACCOUNTS ACC
				On tmpAcc.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS

			Left Join (
				ACCOUNTS_has_DF_PENALTIES_RULES AHDPR
				Inner Join DF_PENALTIES_RULES ACCDPR
					On AHDPR.DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = ACCDPR.ID_DF_PENALTIES_RULES
					And ACCDPR.DF_PENALTIES_RULES_TYPES_ID_DF_PENALTIES_RULES_TYPES = @cmptaccpntlpreprltble_idPenRule
			) On ACC.ID_ACCOUNTS = AHDPR.ACCOUNTS_ID_ACCOUNTS

			Left Join (
				ENTITIES_has_DF_PENALTIES_RULES EHDPR
				Inner Join DF_PENALTIES_RULES ENTDPR
					On EHDPR.DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = ENTDPR.ID_DF_PENALTIES_RULES
					And ENTDPR.DF_PENALTIES_RULES_TYPES_ID_DF_PENALTIES_RULES_TYPES = @cmptaccpntlpreprltble_idPenRule 
			) On ACC.ENTITIES_ID_ENTITIES = EHDPR.ENTITIES_ID_ENTITIES

		Where
			-- vue avec olivier, si DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES alors commercial par defaut
			Coalesce(ACC.DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES, @cmptaccpntlpreprltble_cialDeptTypeId) = @cmptaccpntlpreprltble_cialDeptTypeId
		;

	end;
    cpt_pntl_acc_pntl_rles_tbl_clearunsed: begin
        Delete tar
        From TMP_PENALTIES_ACCOUNTS_WITH_RULES tar
        Where DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = 0
        ;
    end ;
end $$
Delimiter ;


