<?xml version="1.0" encoding="utf-8"?>
<AisMenuItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/AisMenuItem.xsd">
	<Action>stat</Action>
	<SecondAction></SecondAction>
	<Ico></Ico>
  <Label>
    <L1>Policies to be renewed</L1>
    <L2>Polices à renouveler</L2> 
  </Label>
  <Description>
    <L1>Policies other than cancelled, not yet renewed, to be renewed at the selected month and the previous ones</L1>
    <L2>Etat des polices autres que résiliées non encore renouvelées et donc à renouveler pour le mois sélectioné et les mois précédents</L2>
  </Description>
  <Stat>
    <FileMajorVersion>1</FileMajorVersion>
    <FileMinorVersion>0</FileMinorVersion>
    <Parameters>
      <Parameter>
        <Name>currency_code</Name>
        <Type>string</Type>
        <Label>
          <L1>Currency</L1>
          <L2>Devise</L2>
        </Label>
        <Combo>stat_params.spar_currency_code</Combo>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>800</TotalWidth>
      </Parameter>
      <Parameter>
        <Name>date_ref</Name>
        <Type>date</Type>
        <Label>
          <L1>Date</L1>
          <L2></L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>400</TotalWidth>
      </Parameter>
      
    </Parameters>
	
    <SQL>
	

	select	[DEVISE]						= pol_currency_code,
			[Compagnie]						= cie.adr_name,
			[DEPT]							= p.pol_department_code,
			[Famille de produit]			= pp.prd_family_code,
			[Numéro de police]				= ps.pols_number, 
			[ID Risque]						= r.riss_number,
			[Nom]							= cli.adr_complete_name,
			[Date d'échéance]				= ps.pols_term_date,
			[Date prochain terme]			= ps.pols_next_renewal_date,
			[Date prochain terme principal]	= ps.pols_next_principal_renewal_date
			
	from	pol_policies p 
			INNER JOIN pol_policies_situations ps		ON	ps.pols_policy_situation_id = p.pol_last_policy_situation_id
			INNER JOIN pr_products pp					ON	pp.prd_product_code = p.pol_product_code
			INNER JOIN pr_families f					ON	f.pf_family_code = pp.prd_family_code

			INNER JOIN vw_act_cies cie					ON	cie.act_actor_id = p.pol_company_id
			INNER JOIN vw_act_clients cli				ON	cli.act_actor_id = p.pol_client_id

			LEFT JOIN 	(
						SELECT	rs.riss_policy_situation_id,
								CASE 
								WHEN MIN(rs.riss_number) != MAX(rs.riss_number) THEN '-'
								ELSE MIN(rs.riss_number)
								END AS riss_number
						FROM	ris_risks_situations rs 
						GROUP BY rs.riss_policy_situation_id
						) AS r 											ON 	r.riss_policy_situation_id = ps.pols_policy_situation_id




	where	p.pol_currency_code = @currency_code
	AND		f.pf_delegation = 0
	AND		ps.pols_act_code IN ('AMADM', 'AMEND', 'POL', 'REGUL', 'REINST', 'RENEW')
	AND		(
			ps.pols_next_renewal_date BETWEEN '01/01/1900' AND @date_ref
			OR
			ps.pols_next_principal_renewal_date BETWEEN '01/01/1900' AND @date_ref
			)
    </SQL>
	
 </Stat>
 
</AisMenuItem>