<?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>Monthly payment modes movements export</L1>
    <L2>Journal des modes de paiement</L2> 
  </Label>
  <Description>
    <L1>All the payment modes used during the selected period</L1>
    <L2>Liste de tous les modes de paiement utilisés pendant la période sélectionnée</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_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>
    </Parameters>
	
    <SQL>

<![CDATA[
SELECT	
		[vir_no] = DENSE_RANK() OVER(	
											ORDER BY trn.acc_bank_name__branch, pm.pm_payment_code,		
											trn.a_actor_id, 
											trn.a_account_id,
											trn.a_lettering),


		[vir_order] = ROW_NUMBER() OVER(	PARTITION BY trn.a_lettering
											ORDER BY trn.acc_bank_name__branch, pm.pm_payment_code,		
											trn.a_actor_id, 
											trn.a_lettering,	
											CASE 
											WHEN r.a_type_code = 'T' THEN 0
											ELSE 1
											END,
											trn.a_account_id),


		[vir_bank_name] =	CASE 
							WHEN r.a_type_code = 'T' THEN trn.acc_bank_name__branch
							ELSE NULL
							END,

		[IBAN] =			CASE 
							WHEN r.a_type_code = 'T' THEN trn.acc_account_number__branch
							ELSE NULL
							END,

		[vir_payment_mode_code] = CASE 
							WHEN r.a_type_code = 'T' THEN pm.pm_payment_code
							ELSE NULL
							END,
		[vir_payment_mode] = CASE 
							WHEN r.a_type_code = 'T' THEN pm.pm_l1
							ELSE NULL
							END,
		[vir_payment_date] = CASE 
							WHEN r.a_type_code = 'T' THEN trn.a_operation_utc_date
							ELSE NULL
							END,
		[type] =			acct.atyp_l1,
		[Type Cie] =		bc.bdrcie_type_code,
		[vir_lettering] =	trn.a_lettering,	
		[vir_amount_hs] =	CASE 
							WHEN act.act_type_code = 'CLI' AND r.a_type_code = 'A' THEN -r.a_amount
							WHEN act.act_type_code <> 'CLI' AND r.a_type_code IN ( 'P', 'CSD') THEN -r.a_amount
							ELSE NULL
							END,

		[vir_amount_db] =	CASE
							WHEN act.act_type_code = 'CLI' AND UPPER(r.a_type_code) <> 'A' and r.a_amount > 0 then r.a_amount
							WHEN act.act_type_code <> 'CLI' AND UPPER(r.a_type_code) NOT IN ('P', 'CSD') and r.a_amount < 0 then ABS(r.a_amount)
							ELSE NULL
							END,

		[vir_amount_cr] =	CASE
							WHEN act.act_type_code = 'CLI' AND UPPER(r.a_type_code) <> 'A' and r.a_amount < 0 then abs(r.a_amount)
							WHEN act.act_type_code <> 'CLI' AND UPPER(r.a_type_code) NOT IN ('P', 'CSD') and r.a_amount > 0 then r.a_amount
							ELSE NULL
							END,

		[vir_client_name] = cli.adr_complete_name,
		[vir_department_name] = COALESCE(bd1.brdep_l1, bd2.brdep_l1, bd3.brdep_l1),
		[vir_policy_no] = COALESCE(ps.pols_number, adv_pol.pols_number, psc.pols_number),		-- contrat - quittance, contrat - acompte, contrat - sinistre


		[vir_reference]	=	CASE 
							-- Invoices bordereau - recupérer la référence de la transaction
							WHEN r.a_type_code = 'B' THEN ISNULL(bdr.bdr_reference, CAST(r.a_account_id AS VARCHAR(50)))

							-- transactions - recupérer la référence de la transaction
							WHEN r.a_type_code = 'T' THEN ISNULL(trn.atrn_reference, CAST(r.a_account_id AS VARCHAR(50)))
							ELSE CAST(r.a_account_id AS VARCHAR(50))
							END,

		[vir_cie_name]	=	cie.adr_complete_name,
		[vir_app_name]	=	app.adr_complete_name,
		[vir_taxent_name]	=	tax.adr_complete_name,

		[vir_claim_internal_reference] =	c.cl_internal_reference

	
FROM	dbo.acc_trn_bordereaux	b
		INNER JOIN dbo.acc_trn_bordereaux_details bd	ON	bd.trnbd_bordereau_id = b.trnb_bordereau_id
		INNER JOIN dbo.vw_acc_transactions trn			ON	trn.atrn_transaction_id = bd.trnbd_transaction_id
		
		INNER JOIN dbo.nm_payment_modes pm				ON	pm.pm_payment_code = trn.atrn_payment_code

		
		CROSS APPLY	(
					SELECT	x.a_account_id, x.a_actor_id, x.a_type_code, x.a_amount
					FROM	dbo.acc_accounts x
					WHERE	x.a_actor_id = trn.a_actor_id
					AND		x.a_lettering = trn.a_lettering
					AND		x.a_type_code <> 'A'

					-- recupération les acomptes
					UNION ALL 

					SELECT	xadv.a_account_id, xadv.a_actor_id, xadv.a_type_code, xadv.a_amount
					FROM	dbo.vw_acc_advances xadv
					WHERE	xadv.a_actor_id = trn.a_actor_id
					AND		(
								xadv.adv_lettering = trn.a_lettering		
								OR
								xadv.a_lettering = trn.a_lettering			
							)

					) r

		LEFT JOIN dbo.act_actors act					ON	act.act_actor_id = r.a_actor_id
		LEFT JOIN dbo.acc_accounts_types acct			ON	acct.atyp_type_code = r.a_type_code

		-- encaissement d'une quittance ?
		LEFT JOIN dbo.acc_invoices i					ON	i.inv_invoice_id = r.a_account_id
		LEFT JOIN dbo.pol_policies_situations ps		ON	ps.pols_policy_situation_id = i.inv_policy_situation_id
		LEFT JOIN dbo.pol_policies p1					ON 	p1.pol_policy_id = ps.pols_policy_id
		LEFT JOIN dbo.act_branches_departments bd1		ON 	bd1.brdep_branch_id = p1.pol_branch_id
														AND bd1.brdep_department_code = p1.pol_department_code	
		
		LEFT JOIN dbo.vw_act_clients cli				ON	cli.cli_actor_id = r.a_actor_id

		-- acompte ? essayer de recupérer les numéro de contrat
		LEFT JOIN dbo.acc_advances adv					ON	adv.adv_advance_id = r.a_account_id
        
		OUTER APPLY (
					SELECT TOP(1) advps.pols_number
					FROM	dbo.pol_policies_situations advps
					WHERE	advps.pols_policy_id = adv.adv_policy_id
					ORDER BY advps.pols_policy_situation_id DESC 
					) AS adv_pol

		
		LEFT JOIN dbo.acc_accounts acc					ON	acc.a_account_id = r.a_account_id
		LEFT JOIN dbo.acc_bordereaux bdr				ON	bdr.bdr_bordereau_id = r.a_account_id

		LEFT JOIN dbo.pol_policies p2					ON	p2.pol_policy_id = adv.adv_policy_id
		LEFT JOIN dbo.act_branches_departments bd2		ON 	bd2.brdep_branch_id = p2.pol_branch_id
														AND bd2.brdep_department_code = p2.pol_department_code	

		-- bordereau compagnie?
		LEFT JOIN dbo.vw_act_cies cie					ON	cie.act_actor_id = acc.a_actor_id
		LEFT JOIN dbo.acc_bordereaux_companies bc		ON	bc.bdrcie_bordereau_id = r.a_account_id

		-- bordereau apporteur?
		LEFT JOIN dbo.vw_act_introducers app			ON	app.act_actor_id = acc.a_actor_id

		-- bodereaux taxes 
		LEFT JOIN dbo.vw_act_tax_entities tax			ON	tax.act_actor_id = acc.a_actor_id

		-- règlement sin ?
		LEFT JOIN dbo.claim_variables_accounts cv		ON	cv.clacc_account_id = r.a_account_id
		LEFT JOIN dbo.claim_claims c					ON	c.cl_claim_id = cv.clacc_claim_id
		LEFT JOIN dbo.pol_policies_situations psc		ON	psc.pols_policy_situation_id = c.cl_policy_situation_id
		LEFT JOIN dbo.pol_policies p3					ON	p3.pol_policy_id = psc.pols_policy_id
		LEFT JOIN dbo.act_branches_departments bd3		ON 	bd3.brdep_branch_id = p3.pol_branch_id
														AND bd3.brdep_department_code = p3.pol_department_code	


WHERE	b.trnb_tmp = 0
AND		1 =		CASE
				WHEN ISNULL(@currency_code, '') = '' THEN 1
				WHEN b.trnb_currency_code = @currency_code THEN 1
				ELSE 0
				END
AND		b.trnb_operation_utc_date BETWEEN ISNULL(@date_de, '01/01/1900') AND ISNULL(@date_a, '01/01/2100')

ORDER BY trn.acc_bank_name__branch,
		pm.pm_payment_code,		
		trn.a_actor_id, 
		trn.a_lettering,	
		CASE 
		WHEN r.a_type_code = 'T' THEN 0
		ELSE 1
		END,
		trn.a_account_id



]]>

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