SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_acc_account_solde_by_policy_id]
	@client_id int,
	@policy_id int 									-- id du contrat
	
AS

	/*
	Solde du contrat:
	- quittances du contrat 
	- acomptes affectées au contrat
	*/

	select	a.a_currency_code, vir_solde = sum(a.a_amount)
	into	#t
	from	acc_accounts a
			--left join	(
			--			select	*
			--			from	acc_invoices 
			--					inner join pol_policies_situations on pols_policy_situation_id = inv_policy_situation_id
			--			where	isnull(inv_operation_code__last, '') not in ('CR', 'INF', 'AWR')
			--			and		pols_policy_id = @policy_id
			--			) as i	on i.inv_invoice_id = a.a_account_id 
			--left join	acc_advances adv	on	adv.adv_advance_id = a.a_account_id
			--								and	adv.adv_policy_id = @policy_id
	where	a.a_actor_id = @client_id
	and		a_type_code in ('I', 'A')
	and		1 =	case
				when a_type_code = 'I' and exists	(	select	1
														from	acc_invoices 
																inner join pol_policies_situations on pols_policy_situation_id = inv_policy_situation_id
														where	inv_invoice_id = a_account_id 
														and		isnull(inv_operation_code__last, '') in ('DB', 'RF', 'SF')
														and		pols_policy_id = @policy_id
													)	then 1

				when a_type_code = 'A' and exists	(	select	1
														from	acc_advances
														where	adv_advance_id = a_account_id
														and		adv_taken = 0
														and		adv_policy_id = @policy_id
													)	then 1
				else 0
				end
	group by a_currency_code

	if not exists ( select 1 from #t)
	begin
		insert	into #t 
		select	pol_currency_code, 0
		from	pol_policies
		where	pol_policy_id = @policy_id
	end


	select * from #t
GO
