<?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>Etat des échéances et primes</L1>
    <L2>Etat des échéances et primes</L2>
  </Label>
  <Description>
    <L1></L1>
    <L2></L2>
  </Description>
  <Stat>
    <FileMajorVersion>1</FileMajorVersion>
    <FileMinorVersion>0</FileMinorVersion>
    <Parameters>
      <Parameter>
        <Name>date_de</Name>
        <Type>date</Type>
        <Label>
          <L1>Start date</L1>
          <L2>Date de</L2>
        </Label>
      </Parameter>
      <Parameter>
        <Name>date_a</Name>
        <Type>date</Type>
        <Label>
          <L1>End date</L1>
          <L2>Date à</L2>
        </Label>
      </Parameter>	  
    </Parameters>
    <SQL>
		select
			Logiciel = 'CRYSTAL'
			,Compagnie = cie.adr_complete_name
			,[Assure] = cli.adr_complete_name
			,[Adresse_1] =  cli.adr_address1
			,[Adresse_2] =  cli.adr_address2
			,[Adresse_3] =  cli.adr_address3
			,[Code_postal] = cli.adr_postal_code
			,[Ville] = cli.adr_city
			,[Police] = pols_number
			,[Produit] = prd_l2
			,Effet = dateadd(year,-1,pols_next_principal_renewal_date)
			,[Echeance_Principale] = dateadd(day,-1,pols_next_principal_renewal_date)
			,Formule = riss_formula_code
			,Risque = convert(varchar(40),riss_number)
			,[primes_RC] = [risv_prime_rc]
			,[primes_Deffence] = [risv_prime_defence]
			,[primes_PJ] = [risv_prime_pj]
			,[primes_Assistance] = [risv_prime_assistance]
			,[primes_TOC_CATNAT] = [risv_prime_toc]
			,[primes_Dommages] = [risv_prime_dom]
			,[primes_Total] = [risv_prime_total]
			,[Charge_sinistre] = [singa_charge]
			--,[singa1_charge]
		from
			pol_policies
			inner join vw_act_cies cie on cie.act_actor_id = pol_company_id and cie_actor_id in (556,769,554,555,5)
			inner join pol_policies_situations on pols_policy_situation_id = pol_last_policy_situation_id
			inner join vw_act_clients cli on cli.act_actor_id = pol_client_id
			inner join pr_products on prd_product_code = pol_product_code

			inner join 
			(select
					lrisk_policy_id = pols_policy_id
					,lrisk_contrat_id = pols_policy_situation_id
					,lrisk_risk_id = riss_risk_id
					,lrisk_risque_id = max(riss_risk_situation_id)
					,lrisk_entree = min(riss_entrance_date)
					,lrisk_sortie = max(riss_retire_date)

				from
					ris_risks_situations risk
					inner join ris_risks on ris_risk_id = riss_risk_id
					inner join pol_policies_situations on pols_policy_id = riss_policy_id and riss_policy_situation_id &lt;= pols_policy_situation_id
				where
					riss_obsolete = 0
					and riss_entrance_date &lt; pols_next_renewal_date
					and isnull(riss_retire_date,pols_mvt_date) &gt;= pols_mvt_date
				group by
					pols_policy_id
					,pols_policy_situation_id
					,riss_risk_id
			)risk on lrisk_contrat_id = pols_policy_situation_id
			inner join ris_risks_situations on lrisk_risque_id = riss_risk_situation_id
			inner join (
				select distinct
					risv_risk_situation_id
					,[risv_prime_total] = sum(risv_tf)
					,[risv_prime_rc] = sum(case when prdv_variable_code in ('CREWLIA','GENPLIA','OWNELIA','PANDI','PASSLIA','THPALIA') then risv_tf else 0 end)
					,[risv_prime_defence] = sum(case when prdv_variable_code in ('DEFENCE','DEFRECO') then risv_tf else 0 end)
					,[risv_prime_pj] = sum(case when prdv_variable_code in ('LEGPROB','LEGPROA') then risv_tf else 0 end)
					,[risv_prime_assistance] = sum(case when prdv_variable_code in ('ASSISTB','PASP','ASSISTA') then risv_tf else 0 end)
					,[risv_prime_toc] = sum(case when prdv_variable_code in ('TOC','NATCATC') then risv_tf else 0 end)
					,[risv_prime_dom] = sum(case when prdv_variable_code in ('CREWLIA','GENPLIA','OWNELIA','PANDI','PASSLIA','THPALIA','DEFENCE','DEFRECO','LEGPROB','LEGPROA','ASSISTB','PASP','ASSISTA','TOC','NATCATC') then 0 else risv_tf end)
				from
					ris_variables primes
					inner join pr_products_variables on prdv_variable_id = risv_variable_id
					inner join nm_products_variables on nmpv_variable_code = prdv_variable_code and nmpv_type_code = 'PER'
				group by
					risv_risk_situation_id
			)primes on risv_risk_situation_id = riss_risk_situation_id


			left join
			(
				select
					[singa_origine_ris_id] = riss_risk_id
					,singa_effet = dateadd(year,-1,pols_next_principal_renewal_date)	

					,[singa_origine_cie_id] = isnull(singa.clv_company_id,pol_company_id)

					,[singa_charge] = 
						sum(convert(decimal(28,2),
							(isnull(singa.clv_payment,0)+isnull(singa.clv_provision,0)+isnull(singa.clv_provision_injury,0))
							-
							(isnull(singa.clv_received_recovery,0)+isnull(singa.clv_recovery,0))
						))
				from
					claim_variables singa
					inner join claim_claims sini on sini.cl_claim_id = singa.clv_claim_id
					inner join pol_policies_situations pol on pol.pols_policy_situation_id = sini.cl_policy_situation_id
					inner join pol_policies on pol_policy_id = pols_policy_id
					left join pr_products_variables gar on gar.prdv_variable_id = singa.clv_variable_id
					--risque
					left join ris_risks_situations risk on risk.riss_risk_situation_id = sini.cl_risk_situation_id
					left join ris_variables risv on risv.risv_risk_situation_id = risk.riss_risk_situation_id and risv.risv_variable_id = singa.clv_variable_id
					left join nm_formats fran on risv.risv_fmt_code__franchise = fran.fmt_fmt_code
					left join nm_formats cap on risv.risv_fmt_code__capital = cap.fmt_fmt_code
				group by
					riss_risk_id
					,pols_next_principal_renewal_date
					,isnull(singa.clv_company_id,pol_company_id)
			)singa on [singa_origine_ris_id] = riss_risk_id and [singa_origine_cie_id] = pol_company_id and singa_effet = dateadd(year,-1,pols_next_principal_renewal_date)

			
		where
			pols_act_code != 'QUOT'
			and pols_next_principal_renewal_date between @date_de AND @date_a
		
	</SQL>
</Stat>

</AisMenuItem>