<?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>Products - fleet - list</L1>
    <L2>Produits - flottes - liste</L2>
  </Label>
  <Description>
    <L1>Fleets list</L1>
    <L2>Liste des flottes automobile</L2>
  </Description>
  <Stat>
    <FileMajorVersion>1</FileMajorVersion>
    <FileMinorVersion>0</FileMinorVersion>
    <Parameters>
      <Parameter>
        <Name>policy</Name>
        <Type>string</Type>
        <Label>
          <L1>Policy</L1>
          <L2>Police</L2>
        </Label>
      </Parameter>
    </Parameters>
    <SQL>
      select distinct
      [Entité] = br_short_code
      ,[Client] = cli.adr_complete_name
      ,[Police] = pols.pols_number
      ,[Produit] = prd_l2
      ,[Type contrat] = case when pols.pols_act_code = 'POL' then 'Affaire nouvelle' when pols.pols_act_code = 'QUOT' then 'Devis' when pols.pols_act_code = 'AMEND' then 'Avenant' end
      ,[Risque] = riss_number
      ,[Date de début] = pol.pol_start_date
      ,[Date de mouvement] = pols.pols_mvt_date
      ,[Date d'entrée] = riss_entrance_date
      ,[Date de sortie] = riss_retire_date
      ,[Coef Statistique] = pols.pols_bonus_value
      ,[Nature] = isnull(pfn_l2,pfn_l1)
      ,[Formule] = riss_formula_code
      ,[Prime nette] = (select sum(invrisv_tf) from
      acc_invoices_risks_variables
      inner join ris_risks_situations riss1 on invrisv_risk_situation_id = riss1.riss_risk_situation_id
      where riss1.riss_risk_id = ris.ris_risk_id)

      ,[Taxes] = (select sum(invrisv_ti) from
      acc_invoices_risks_variables
      acc_invoices_risks_variables
      inner join ris_risks_situations riss1 on invrisv_risk_situation_id = riss1.riss_risk_situation_id
      where riss1.riss_risk_id = ris.ris_risk_id) - (select sum(invrisv_tf) from
      acc_invoices_risks_variables
      inner join ris_risks_situations riss1 on invrisv_risk_situation_id = riss1.riss_risk_situation_id
      where riss1.riss_risk_id = ris.ris_risk_id)
      ,[Prime TTC] = (select sum(invrisv_ti) from
      acc_invoices_risks_variables
      inner join ris_risks_situations riss1 on invrisv_risk_situation_id = riss1.riss_risk_situation_id
      where riss1.riss_risk_id = ris.ris_risk_id)

      from
      pol_policies pol
      inner join pol_policies_situations pols on pol.pol_policy_id = pols.pols_policy_id
      inner join ris_risks ris on pol.pol_policy_id = ris.ris_policy_id
      inner join ris_risks_situations rrs on ris.ris_last_risk_situation_id = rrs.riss_risk_situation_id and rrs.riss_policy_situation_id = pols.pols_policy_situation_id
      inner join vw_act_clients cli on cli.cli_actor_id = pol.pol_client_id
      left join pr_products prd on prd.prd_product_code = pol.pol_product_code
      left join pr_families_natures on pfn_family_code = prd_family_code and pfn_nature_code = riss_nature_code
      inner join act_branches on pol.pol_branch_id = br_actor_id
      where pols.pols_number like @policy+'%'
      and prd.prd_family_code = 'MO6A'
      and pols.pols_number not like '%SUPPRIME%'
      and(
      (exists (select 1 from pol_policies_situations pols1 where pols1.pols_policy_id = pols.pols_policy_id and pols1.pols_act_code not like 'QUOT')
      and pols.pols_act_code not like 'QUOT'
      )
      or
      (not exists (select 1 from pol_policies_situations pols1 where pols1.pols_policy_id = pols.pols_policy_id and pols1.pols_act_code not like 'QUOT')
      and pol.pol_last_policy_situation_id = pols.pols_policy_situation_id
      )
      )
      order by cli.adr_complete_name
    </SQL>
</Stat>

</AisMenuItem>
