<?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>Outstanding</L1>
    <L2>Quittances impayées</L2>
  </Label>
  <Description>
    <L1>Outstanding debit notes at the selected date</L1>
    <L2>Quittances impayées à la date 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_extraction</Name>
        <Type>date</Type>
        <Label>
          <L1>Extraction date</L1>
          <L2>Date d'extraction</L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>600</TotalWidth>
      </Parameter>
    </Parameters>
    <SQL>
			
		SELECT	[Devise]					= a.a_currency_code, 
				[Département]				= p.pol_department_code,
				[Compagnie]					= cie.adr_name, 
				[Famille de produit]		= f.pf_family_code,
				[ID Quittance]				= i.inv_invoice_id,
				[Numéro de police]			= ps.pols_number,
				[ID Risque]					= r.riss_number,				
				[Nom]						= cli.adr_complete_name,
				[Début d'effet]				= CAST(i.inv_start_date AS DATE),
				[Fin d'effet]				= CAST(i.inv_end_date AS DATE),
				[Prime TTC]					= a.a_amount,
				[Prime nette]				= tf.total,
				[Taxes]						= tx.total,
				[Commissions]				= cions.total,
				[Frais de gestion]			= fg.total,
				[Acompte affecté]			= acomptes.total_acomptes

		FROM	acc_invoices i
				INNER JOIN acc_accounts a					ON	a.a_account_id = i.inv_invoice_id		
				INNER JOIN pol_policies_situations ps		ON	ps.pols_policy_situation_id = i.inv_policy_situation_id
				INNER JOIN pol_policies p					ON	p.pol_policy_id = ps.pols_policy_id
				INNER JOIN pr_products pr					ON	pr.prd_product_code = p.pol_product_code
				INNER JOIN pr_families f					ON	f.pf_family_code = pr.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

				-- prime nette
				INNER JOIN	(
							SELECT	invt_invoice_id, SUM(invt_tf) as total
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as tf												ON	tf.invt_invoice_id = i.inv_invoice_id
				-- taxes
				INNER JOIN	(
							SELECT	invt_invoice_id, SUM(invt_taxes) as total
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as tx												ON	tx.invt_invoice_id = i.inv_invoice_id
				-- commissions
				INNER JOIN	(
							SELECT	invt_invoice_id, SUM(invt_commission_ti) as total
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as cions											ON	cions.invt_invoice_id = i.inv_invoice_id
				-- frais de gestion (Frais courtier contrat BPC, Frais courtier risque BRC, Honoraire courtier risque BPF, Honoraire courtier contrat BRF)
				LEFT JOIN	(
							SELECT	invt_invoice_id, SUM(invt_tf) as total
							FROM	vw_acc_invoices_totals
							WHERE	invt_variable_type_code IN ('BPC', 'BRC', 'BPF', 'BRF')
							GROUP BY invt_invoice_id
							) as fg											ON	fg.invt_invoice_id = i.inv_invoice_id


				-- acomptes affectés
				LEFT JOIN	(
							SELECT	adv.adv_policy_id, SUM(ABS(adv.a_amount)) AS total_acomptes
							FROM	vw_acc_advances adv
							WHERE	adv.pol_last_policy_situation_id IS NOT NULL 
							AND		adv.adv_taken = 0
							AND		adv.a_currency_code = @currency_code
							GROUP BY adv.adv_policy_id
							) acomptes										ON acomptes.adv_policy_id = p.pol_policy_id
							
				LEFT JOIN 	(
							SELECT	ir.invris_invoice_id, 
									CASE 
									WHEN MIN(rs.riss_number) != MAX(rs.riss_number) THEN '-'
									ELSE MIN(rs.riss_number)
									END AS riss_number
							FROM	acc_invoices_risks_situations ir
									INNER JOIN ris_risks_situations rs ON rs.riss_risk_situation_id = ir.invris_risk_situation_id
							GROUP BY ir.invris_invoice_id
							) AS r 											ON 	r.invris_invoice_id = i.inv_invoice_id
							

		WHERE	a.a_currency_code = @currency_code
		AND		inv_db_date IS NOT NULL AND inv_db_date BETWEEN '01/01/1900' AND ISNULL(@date_extraction, '01/01/2100')

		AND		inv_rmb_date IS NULL
		AND		inv_an_date IS NULL
		AND		inv_ec_date IS NULL
		ORDER BY	[Département], 
					[Compagnie],
					[ID Quittance]
	
    </SQL>
	</Stat>

</AisMenuItem>