<?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>Claims payments and recoveries</L1>
    <L2>Paiements et encaissements sinistres</L2> 
  </Label>
  <Description>
    <L1>All the payments and recoveries during the selected period and excercise</L1>
    <L2>Liste de tous les paiemente et encaissements pour la période et l'exercice sélectionnées</L2>
  </Description>
  <Stat>
    <FileMajorVersion>1</FileMajorVersion>
    <FileMinorVersion>0</FileMinorVersion>
    <Parameters>
      <Parameter>
        <Name>@date_de</Name>
        <Type>date</Type>
        <Label>
          <L1>Date from</L1>
          <L2>Date de</L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>600</TotalWidth>
      </Parameter>
      <Parameter>
        <Name>@date_a</Name>
        <Type>date</Type>
        <Label>
          <L1>Date to</L1>
          <L2>Date à</L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>600</TotalWidth>
      </Parameter>
	  <Parameter>
        <Name>@exercise_year</Name>
        <Type>int</Type>
        <Label>
          <L1>Exercise(year)</L1>
          <L2>Exercise(année)</L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>600</TotalWidth>
      </Parameter>
	  
    </Parameters>
	
    <SQL>

<![CDATA[
/*
DECLARE @date_de DATE = '2017-01-01'
DECLARE @date_a DATE = '2017-12-31'
DECLARE @exercise_year INT = 2017
*/

SELECT 
	[Code compagnie] = aacie.act_internal_reference,
	[Branche] = '',
	[Produit] = pp1.prd_l2, -- pp1.prd_product_code,
	[N° Police] = pps.pols_number,
	[Nom de l'assuré] = vac.adr_complete_name,
	[Année Sin] = YEAR(cc.cl_date_setup),
	[N° Sin] = cc.cl_internal_reference,
	[Année de survenance] = YEAR(cc.cl_occurence_date),
    [Mois de survenance] = MONTH(cc.cl_occurence_date),
	[Survenu le] = cc.cl_occurence_date,
    [Date de déclaration] = cc.cl_declaration_date,
    [Date de création] = cc.cl_date_setup,
    [Mis à Jour le] = CONVERT(DATETIME, NULL),
	[Situation] = cm.cmvt_l1,
    [Nature] = cc.cl_nature_code,
	[Gtie] = vcv.nmpv_l2,
	[% Resp] = cc.cl_responsability_percent,
    [Conv.] = cc.cl_convention_code,
    [Bénéf] = sinactadr.act_internal_reference,
    [Recours] = CASE WHEN aaclacc.a_type_code='CR' THEN aaclacc.a_amount ELSE 0 end,
    [Reglt] = CASE WHEN aaclacc.a_type_code='CP' THEN aaclacc.a_amount ELSE 0 end,
    [Libellé] = cva.clacc_obs,
    [Opération] = aaclacc.a_type_code,
    [Date de l'opération] = aaclacc.a_operation_utc_date,
    [Année de règlt] = YEAR(aaclacc.a_operation_utc_date),
    [Mois de règlt] = MONTH(aaclacc.a_operation_utc_date),
    [Numéro de pièce] = ''
FROM vw_act_clients vac
	INNER JOIN pol_policies pp ON pp.pol_client_id = vac.act_actor_id
	INNER JOIN pr_products pp1 ON pp1.prd_product_code = pp.pol_product_code
	INNER JOIN pol_policies_situations pps ON pp.pol_policy_id = pps.pols_policy_id
	INNER JOIN act_actors aacie ON aacie.act_actor_id = pp.pol_company_id 
	INNER JOIN vw_act_branches vab ON vab.br_actor_id = pp.pol_branch_id
	INNER JOIN claim_claims cc ON pps.pols_policy_situation_id = cc.cl_policy_situation_id
	INNER JOIN claim_mvts cm ON cc.cl_mvt_code = cm.cmvt_mvt_code
	INNER JOIN claim_variables_accounts cva ON cc.cl_claim_id = cva.clacc_claim_id --AND cva.clacc_operation_code='IS' --emis
	INNER JOIN vw_claim_variables vcv ON vcv.clv_claim_id = cva.clacc_claim_id AND vcv.clv_variable_id = cva.clacc_variable_id 
	--LEFT JOIN claim_variables_provisions cvp ON cv.clv_claim_id = cvp.clvp_claim_id AND cv.clv_variable_id = cvp.clvp_variable_id
	--LEFT JOIN claim_variables_recoveries cvr ON cv.clv_claim_id = cvr.clvr_claim_id AND cv.clv_variable_id = cvr.clvr_variable_id
	INNER JOIN acc_accounts aaclacc ON cva.clacc_account_id = aaclacc.a_account_id
	left JOIN vw_act_actors_adresses sinactadr	on	sinactadr.act_actor_id = aaclacc.a_actor_id
WHERE		YEAR(cc.cl_date_setup) = @exercise_year
		AND aaclacc.a_operation_utc_date BETWEEN @date_de AND @date_a
	
	/*
	SELECT * FROM nm_accounts_operations nao
	SELECT * FROM claim_claims
	SELECT * FROM acc_accounts_types aat

	select * from claim_variables_accounts where clacc_operation_code='IS'

	SELECT cc.cl_internal_reference,cc.cl_occurence_date, aa.*,* 
	FROM claim_variables_accounts cva 
		INNER JOIN claim_variables cv ON cva.clacc_claim_id = cv.clv_claim_id AND cva.clacc_variable_id = cv.clv_variable_id 
		INNER JOIN claim_claims cc ON cv.clv_claim_id = cc.cl_claim_id
		INNER JOIN acc_accounts aa ON cva.clacc_account_id = aa.a_account_id

	SELECT * FROM acc_accounts aa
	SELECT * FROM claim_variables_accounts

	SELECT * FROM claim_variables_provisions
	*/
	/*
	select
		[Code compagnie] = Pol.Pol_Cie_id,
		[Branche] = MAX(case when Pol.Pol_Produit_id='2Rou' then 'B122 AUTO MONO 2 ROUES' when Pol.Pol_Produit_id in('auto2','autoe','autop','autot','m3t5a') then 'B123 AUTO MONO' when Pol.Pol_Produit_id in ('flott','xflot') then 'B121 AUTO FLOTTES ET GARAGES' when Pol.Pol_Produit_id in ('modma','mosma') then 'B093 MR HABITATION' when Pol.Pol_Produit_id in ('MPROM') then 'B065 MR COMMERCES' when Pol.Pol_Produit_id = 'MRE' then 'B055 DAB ENTREPRISES' when Pol.Pol_Produit_id = 'PNOC' then 'B091 MR IMMEUBLES' when Pol.Pol_Produit_id = 'RCC' then 'B072 RC ENTREPRISES' else 'B000 AUTRES' end ),
		[Produit] = MAX(case when Pol.Pol_Produit_id in ('autoe','autop','M3t5a','xflot','flott') then 'auto 4 R -3.5T' when Pol.Pol_Produit_id = '2rou' then 'auto 2 R' when Pol.Pol_Produit_id in ('autot','auto2') then 'P.L +3.5T' when Pol.Pol_Produit_id in ('pnoc','modma','mosma') then 'Inc.risque simple' when Pol.Pol_Produit_id in ('rcsc','rccf','rc-m') then 'R.C partic.(ordinaire)' when Pol.Pol_Produit_id = 'rcc' then 'R.C prof.' else Pol.Pol_Produit_id end ),
		[N° Police] = MAX(Pol.Pol_Contrat),
		[Nom de l'assuré] = MAX(Cli.Adr_Nom),
		[Année Sin] = MAX(Sin.Sin_Annee),
		[N° Sin] = MAX(Sin.Sin_SinistreCabinet),
		[Année de survenance] = MAX(year (Sin.Sin_DteSurvenance)),
		[Mois de survenance] = MAX(month (Sin.Sin_DteSurvenance)),
		[Survenu le] = MAX(Sin.Sin_DteSurvenance),
		[Date de déclaration] = Sin.Sin_DteDeclaration,
		[Date de création] = Sin.Sin_DteNew,
		[Mis à Jour le] = MAX(Sin.Sin_DteEdit),
		[Situation] = MAX(Sin.Gest_Intitule),
		[Nature] = MAX(Sin.Sin_Nature_id),
		[Gtie] = MAX(case when Singa.Singa_Garantie_id = 'RC' then 'R.C' when Singa.Singa_Garantie_id = 'vol' then '241 Vol particuliers' when Singa.Singa_Garantie_id = 'CAT' then '270 Catastrophes Naturelles' else 'Dommages' end ),
		[M/C] = MAX(SinCpt.Sincpt_TypeMC),
		[% Resp] = MAX(Sin.Sin_Respons),
		[Conv.] = MAX(Sin.Sin_Convention_id),
		[Bénéf] = MAX(SinCpt.Sincpt_CatBeneficiaire),
		[Recours] = SinCpt.Sincpt_Encaissement,
		[Reglt] = SinCpt.Sincpt_Reglement,
		[Libellé] = MAX(SinCpt.Sincpt_Intitule),
		[Opération] = MAX(SinCpt.Sincpt_CodeOperation),
		[Date de l'opération] = SinCpt.Sincpt_DteOperation,
		[Année de règlt] = year (SinCpt.Sincpt_DteOperation),
		[Mois de règlt] = month (SinCpt.Sincpt_DteOperation),
		[Numéro de pièce] = SinCpt.Sincpt_NumPiece
	from vw_clients as Cli
		left join vw_contrats as Pol on Cli.cli_client_id = Pol.pol_client_id
		left join vw_sinistres as Sin on Pol.pol_client_id = Sin.pol_client_id and Pol.Pol_Ordre_Contrat = Sin.Pol_Ordre_Contrat
		left join si_comptes as SinCpt on Sin.sin_sinistre_id = SinCpt.sincpt_sinistre_id
		left join si_garanties as SinGa on Sin.sin_sinistre_id = SinGa.singa_sinistre_id
		left join si_garanties_affect as SinGaAff on SinGa.singa_sinistre_id = SinGaAff.singaaf_sinistre_id and SinGa.singa_ordre = SinGaAff.singaaf_ordre
		left join si_garanties_eval as SinGaEval on SinGa.singa_sinistre_id = SinGaEval.singaev_sinistre_id and SinGa.singa_ordre = SinGaEval.singaev_ordre


	where
	(
		( SinCpt.Sincpt_DteOperation Between '01/12/2022' and '31/12/2022')
	and ( SinCpt.Sincpt_OrdreRegl = 0)
	and ( SinCpt.Sincpt_CodeOperation not in ('EPO','RPO'))
	and ( Cli.Adr_Nom != 'LE CONTINENT ASSURANCE')
	and ( Pol.Pol_Produit_id not in ('PLAI','VIE-C') )
	)
	group by
		Pol.Pol_Cie_id ,
		Sin.Sin_DteDeclaration ,
		Sin.Sin_DteNew ,
		SinCpt.Sincpt_Encaissement ,
		SinCpt.Sincpt_Reglement ,
		SinCpt.Sincpt_DteOperation ,
		year (SinCpt.Sincpt_DteOperation) ,
		month (SinCpt.Sincpt_DteOperation) ,
		SinCpt.Sincpt_NumPiece
	*/



]]>

    </SQL>
	
 </Stat>
 
</AisMenuItem>