SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_acc_cts_add_fees_fn_invoices]
	@table_id type_table_id READONLY,		-- liste des id quittances (inv_invoice_id)
	@date_operation		datetime,
	@user_code			ais_code_medium,
	@obs				nvarchar(64),
	@branch_id			int,
	@department_code	ais_code_medium

AS
	
	/*
	DGE
	Cette procedure ajoute les frais de mise en demeure (formal notice, fs) aux quittances spécifiées dans la table
	@table_id, mais une fois par contrat. 
	
	*/
	
	DECLARE @t TABLE
					(
					policy_id int not null, 
					invoice_id int not null,
					fee ais_decimal2 null
					)
	
	insert into @t(policy_id, invoice_id, fee)
	select	p.pol_policy_id, MAX(i.inv_invoice_id), coalesce(pb.prdb_cts_fee, ch2.ctsch_cts_fee, ch.ctsch_cts_fee)
	from	acc_invoices i
			inner join @table_id t					on	t.id = i.inv_invoice_id 
			inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = i.inv_policy_situation_id
			inner join pol_policies p				on	p.pol_policy_id = ps.pols_policy_id
			inner join pr_products prd				on	prd.prd_product_code = p.pol_product_code 
			inner join act_branches br				on	br.br_actor_id = p.pol_branch_id
			left join cts_chains ch					on	ch.ctsch_chain_code = br.br_chain_code 
			left join cts_chains ch2				on	ch2.ctsch_chain_code = prd.prd_chain_code 
			left join pr_products_branches pb		on	pb.prdb_product_code = prd.prd_product_code
													and	pb.prdb_branch_id = p.pol_branch_id

	group by p.pol_policy_id, coalesce(pb.prdb_cts_fee, ch2.ctsch_cts_fee, ch.ctsch_cts_fee)
	
	update	a
	set		a_amount = isnull(a.a_amount, 0) + isnull(t.fee, 0)
	from	acc_accounts a
			inner join @t t on t.invoice_id = a.a_account_id 
			
	update	i
	set		i.inv_cts_fee = isnull(t.fee, 0)
	from	acc_invoices i 
			inner join @t t on t.invoice_id = i.inv_invoice_id 

	INSERT INTO [dbo].[acc_invoices_operations]
			   ([invop_invoice_id]
			   ,[invop_operation_utc_date]
			   ,[invop_operation_code]
			   ,[invop_user_code]
			   ,[invop_obs]
			   ,[invop_branch_id]
			   ,[invop_department_code])
	select	t.id,
			@date_operation,
			'SF',
			@user_code,
			@obs,
			@branch_id,
			@department_code
	from	@table_id t

GO
