SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_tt_policies_info]
	@pol_policies_situations_table type_table_id readonly

as
	set NOCOUNT ON;
	
	select	pols_policy_id, pols_policy_situation_id, pol_branch_id,
			pol_client_id, act_address_id, cli_culture_code,
			clt_language_code = isnull(mc.clt_language_code,'en-US'),
			clt_culture_code,
			clt_l1,
			clt_l2,
			clt_l3,
			pols_number,
			ppb.prdb_notice_period
	from pol_policies_situations
		inner JOIN @pol_policies_situations_table on pols_policy_situation_id = id
		inner join pol_policies on pols_policy_id = pol_policy_id
		inner join act_actors on act_actor_id = pol_client_id
		inner join act_clients on act_actor_id = cli_actor_id
		inner JOIN pr_products on prd_product_code = pol_product_code
		left JOIN md_cultures mc on mc.clt_culture_code = cli_culture_code
		left join pr_products_branches ppb on		ppb.prdb_product_code = pol_product_code
												and ppb.prdb_branch_id = pol_branch_id

	select riss_risk_situation_id, riss_risk_id, riss_policy_situation_id, riss_policy_id, riss_movement_code, riss_movement_date, riss_movement_date
	from ris_risks_situations
		inner join @pol_policies_situations_table on riss_policy_situation_id = id


	-- invoices
	select	distinct inv_invoice_id
	from	acc_invoices i 
			inner join @pol_policies_situations_table t on i.inv_policy_situation_id = t.id

	-- advances
	select	distinct adv.adv_advance_id
	from	acc_advances adv
			inner join pol_policies_situations s on s.pols_policy_id = adv.adv_policy_id
			inner join @pol_policies_situations_table t on t.id = s.pols_policy_situation_id
GO
