SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_renewal_header]
	@product_code ais_code_short,
	@policy_number	ais_code_long,
	@renewal_debit int,
	@max_worker_risks_number int = 0	
as

-- selecteaza datele necesare pentru terme, contracte TACIT si active
declare @pol_policies_risks_table as type_pol_policies_risks

set @policy_number = isnull(nullif(@policy_number,''),'%')

--tabela de contracte
declare @policies_situations table(policy_situation_id int primary key, policy_id int, policy_next_renewal_date date, client_id int)

--cautare contracte
insert into @policies_situations(	policy_situation_id,		policy_id,		policy_next_renewal_date,	client_id	)
select								pols_policy_situation_id,	pols_policy_id,	pols_next_renewal_date,		pol_client_id 
from pol_policies_situations 
	inner join pol_policies on pol_policy_id = pols_policy_id and pol_last_policy_situation_id = pols_policy_situation_id
	inner join pol_mvts on pmvt_mvt_code = pols_mvt_code and pmvt_type_code in ('ACT','A_L') -- MVT ACTIF si ACTIF_AND_LOCKED
where	pols_renew_type_code = 'TACIT'
		and pols_next_renewal_debit = @renewal_debit 
		and ( @policy_number = '%' or pols_number = @policy_number)
		
				
-- cautare riscuri
insert into @pol_policies_risks_table(	tppr_policy_situation_id,	tppr_policy_id,	tppr_risk_situation_id,	tppr_risk_id,	tppr_client_id)
select									riss_policy_situation_id,	riss_policy_id,	riss_risk_situation_id,	riss_risk_id,	client_id
from ris_risks_situations a
	inner join ris_risks on riss_risk_id = ris_risk_id and riss_risk_situation_id = ris_last_risk_situation_id 
	inner join @policies_situations on riss_policy_id = policy_id 
where	
	riss_movement_date =	(
									select max(riss_movement_date)
									from ris_risks_situations m
									where	a.riss_risk_id = m.riss_risk_id
											and policy_next_renewal_date >= m.riss_movement_date
											and m.riss_retire_date is null 
											and not exists	(	
																	select 1
																	from  ris_risks_situations t
																	where	m.riss_risk_id = t.riss_risk_id
																			and t.riss_movement_date > m.riss_movement_date
																			and t.riss_retire_date <= policy_next_renewal_date	
															) 
							)
							
select * from @pol_policies_risks_table
								
								


	

	
		
	





GO
