SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_renewal_list]
	@product_code ais_code_short,
	@policy_number	ais_code_long,
	@renewal_debit int,
	@renew_type_code ais_code_short
	
as

set nocount on;

declare @debit_month int

set @debit_month = convert(int, substring(convert(varchar,@renewal_debit),5,2))

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

select  pols_policy_situation_id,	
	   pols_policy_id,	
	   pols_next_principal_renewal_date,
	   pols_number,
	   pol_start_date, 	
	   pf_type_pol_code,			
	   vac.adr_complete_name, 
	   vac.act_internal_reference,
	   pols_renew_type_code,pols_renew_split_code,
	   nprts.nmprts_l1,
	   nprts.nmprts_l2,
	   nprts.nmprts_l3
from pol_policies_situations pps
	inner join pol_policies pp 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
	inner join pr_products on pol_product_code = prd_product_code
	inner join pr_families on prd_family_code = pf_family_code
	inner join vw_act_clients vac on vac.act_actor_id = pp.pol_client_id 
	inner join nm_pol_renew_types_split nprts on nprts.nmprts_renew_type_code = pps.pols_renew_type_code and nprts.nmprts_renew_split_code = pps.pols_renew_split_code
where		 ( @renew_type_code = '%' or pols_renew_type_code = @renew_type_code)
		and pol_product_code = @product_code
		and pf_delegation = 1
		and pols_next_principal_renewal_debit = @renewal_debit -- CATALIN, 16.12.2015, only principal renewal since we create all invoices from start -- and pols_next_renewal_debit = @renewal_debit 
		and ( @policy_number = '%' or pols_number = @policy_number)

GO
