SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policies_situations_invoices_missing_retrocession]
	@input_invoices type_table_id readonly
as

	set nocount on;

	declare @all_invoices table(policy_situation_id int, invoice_id int)

	if (select count(*) from @input_invoices) > 0
	begin
		insert into @all_invoices (				invoice_id)
		select						distinct	id
		from @input_invoices
	end
	else
	begin

		insert into @all_invoices (				invoice_id)
		select						distinct	inv_invoice_id
		from pol_policies_situations pps
			inner join pol_policies_situations_introducers ppsi on ppsi.polsi_policy_situation_id = pps.pols_policy_situation_id
			inner join acc_invoices ai on ai.inv_policy_situation_id = pps.pols_policy_situation_id
			inner join pol_mvts pm on pm.pmvt_mvt_code = pps.pols_mvt_code
			inner join int_retrocessions ir on ir.iret_retro_code = ppsi.polsi_retro_code
		where		pmvt_type_code in ('TER', 'ACT', 'SUS')
				and (		(pps.pols_act_code != 'AMEND') 
						or	(pps.pols_act_code = 'AMEND' and iret_subscription_only = 0)
					) --cette condition est pour détécter les anomalies sur les avenants 

				and (ir.iret_value != 0 or exists(	select 1	
													from int_retrocessions_years iry 
													where		iry.irety_retro_code = ir.iret_retro_code 
															and iry.irety_value != 0)
					)
	end

	declare @invoices table(invoice_id int) 

	insert into @invoices(				invoice_id)
	select					distinct	aipv.invpolv_invoice_id
	from acc_invoices_policies_variables aipv
		inner join  @all_invoices ai on ai.invoice_id = aipv.invpolv_invoice_id
		inner join pr_products_variables ppv on ppv.prdv_variable_id = aipv.invpolv_variable_id and prdv_retroceded = 1
		left join  acc_invoices_policies_variables_introducers aipvi on			aipvi.invpolvi_invoice_id = aipv.invpolv_invoice_id
	where aipvi.invpolvi_invoice_id is null


	insert into @invoices(				invoice_id)
	select					distinct	airv.invrisv_invoice_id
	from acc_invoices_risks_variables airv
		inner join  @all_invoices ai on ai.invoice_id = airv.invrisv_invoice_id
		inner join pr_products_variables ppv on ppv.prdv_variable_id = airv.invrisv_variable_id and prdv_retroceded = 1
		left join  acc_invoices_risks_variables_introducers airvi on			airvi.invrisvi_invoice_id = airv.invrisv_invoice_id
		left join @invoices i on i.invoice_id = airv.invrisv_invoice_id
	where		airvi.invrisvi_invoice_id is null
			and i.invoice_id is null 
			

	select pps.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id

	select pp.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
		inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id

	select aipv.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
		inner join acc_invoices_policies_variables aipv on		aipv.invpolv_invoice_id = ai.inv_invoice_id
															and aipv.invpolv_policy_situation_id = pps.pols_policy_situation_id 
	select rrs.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
		inner join ris_risks_situations rrs on rrs.riss_policy_situation_id = pps.pols_policy_situation_id



	select airv.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
		inner join ris_risks_situations rrs on rrs.riss_policy_situation_id = pps.pols_policy_situation_id
		inner join acc_invoices_risks_variables airv on			airv.invrisv_invoice_id = ai.inv_invoice_id
															and	airv.invrisv_risk_situation_id = rrs.riss_risk_situation_id



	select	aa.*,
			ai.*,
			npo.*,
			pps.*,
			vai.adr_complete_name,
			ir.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
		inner join nm_pol_operations npo on npo.nmpop_operation_code = pps.pols_operation_code
		inner join acc_accounts aa on aa.a_account_id = ai.inv_invoice_id
		inner join pol_policies_situations_introducers ppsi on ppsi.polsi_policy_situation_id = pps.pols_policy_situation_id
		inner join vw_act_introducers vai on vai.act_actor_id = ppsi.polsi_introducer_id
		inner join int_retrocessions ir on ir.iret_retro_code = ppsi.polsi_retro_code


	select ai.*
	from acc_invoices ai
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id

	select ppsi.*, ir.*
	from acc_invoices ai 
		inner join @invoices i on i.invoice_id = ai.inv_invoice_id
		inner join pol_policies_situations_introducers ppsi on ppsi.polsi_policy_situation_id = ai.inv_policy_situation_id
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
		inner join int_retrocessions ir on ir.iret_retro_code = ppsi.polsi_retro_code
		


GO
