SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_invoice_unit_rate_situations_and_invoices_list]
	@policy_situation_id int

as
	set nocount on;

	declare @policy_situations table (policy_situation_id int)

	insert into @policy_situations(	policy_situation_id)
	select							pps.pols_policy_situation_id
	from pol_policies_situations pps
		inner join pol_policies_situations src on src.pols_policy_id = pps.pols_policy_id
		inner join pol_mvts pm on pm.pmvt_mvt_code = pps.pols_mvt_code
		inner join pol_acts pa on pa.pact_act_code = pps.pols_act_code
	where		src.pols_policy_situation_id = @policy_situation_id
			and	pa.pact_act_code in ('POL', 'AMEND', 'AMADM', 'REGUL', 'REINST', 'RENEW')


	select	pols_policy_situation_id,
			pols_number,
			pols_mvt_date,
			pols_endorsment_description = isnull(pols_endorsment_description,''),
			npo.*
	from pol_policies_situations pps
		inner join @policy_situations ps on ps.policy_situation_id = pps.pols_policy_situation_id
		inner join nm_pol_operations npo on npo.nmpop_operation_code = pols_operation_code

	select	inv_invoice_id,
			inv_policy_situation_id,
			inv_start_date,
			inv_end_date,
			inv_operation_code__last,
			aa.a_amount,
			nio.*
	from acc_invoices ai
		inner join @policy_situations ps on ps.policy_situation_id = ai.inv_policy_situation_id
		inner join nm_invoices_operations nio on nio.op_operation_code = inv_operation_code__last
		inner join acc_accounts aa on aa.a_account_id = ai.inv_invoice_id
GO
