SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE VIEW [dbo].[vw_poema_compta_apporteur]
AS
	WITH cte_retro 
	AS	(
		 SELECT		invpolvi_invoice_id				AS invoice_id,
					invpolvi_introducer_id			AS introducer_id,
					invpolvi_value					AS val
		 FROM		dbo.acc_invoices_policies_variables_introducers 

		 UNION ALL 

		 SELECT		invrisvi_invoice_id,
					invrisvi_introducer_id,
					invrisvi_value
		 FROM		dbo.acc_invoices_risks_variables_introducers
		 )
	,cte_sum 
	AS	(
		SELECT	c.invoice_id, c.introducer_id, retro = SUM(c.val)
		FROM	cte_retro c
		--WHERE	c.invoice_id =50031794
		GROUP BY c.invoice_id, c.introducer_id
		)
	SELECT	[ID Quittance] = i.inv_invoice_id,
			[Code Apporteur] = vint.act_internal_reference,
			[Apporteur] = vint.adr_complete_name,
			[Date création] = i.inv_db_date,
			[Date effet] = i.inv_start_date,
			[Date annulation] = i.inv_an_date,
			[Date encaissement] = i.inv_ec_date,
			[Date rétrocession] = ta.a_operation_utc_date,
			[Montant prime client] = t.total_ti,
			[Commission Poe-ma] = t.total_commission,
			[Montant rétrocession] = x.bdrd_tf,
			[% Par rapport a Poe-ma] = CONVERT(DECIMAL(28,2),CASE WHEN ISNULL(t.total_commission, 0) = 0 THEN NULL ELSE (x.bdrd_tf / t.total_commission)*100 END)
	FROM	cte_sum s

			INNER JOIN dbo.vw_act_introducers vint ON vint.act_actor_id = s.introducer_id
			INNER JOIN dbo.acc_invoices i ON i.inv_invoice_id = s.invoice_id

			INNER JOIN	(
							SELECT	v.invt_invoice_id, total_ti = SUM(v.invt_ti), total_commission = SUM(invt_commission_ti)
							FROM	dbo.vw_acc_invoices_totals v
							GROUP BY v.invt_invoice_id
						) as t ON t.invt_invoice_id = s.invoice_id

			OUTER APPLY	(
							SELECT	a.a_actor_id, a.a_lettering, bdrd_tf = SUM(bd.bdrd_tf)
							FROM	dbo.acc_bordereaux b
									INNER JOIN dbo.acc_bordereaux_details bd ON bd.bdrd_bordereau_id = b.bdr_bordereau_id

									INNER JOIN dbo.acc_accounts a ON a.a_account_id = b.bdr_bordereau_id
									INNER JOIN dbo.act_actors bact ON bact.act_actor_id = a.a_actor_id
								
							WHERE	b.bdr_operation_code = 'P'		-- bordereaux payés
							AND		a.a_lettering IS NOT NULL
							AND		bact.act_type_code = 'INT'		-- bordereaux apporteur
							AND		a.a_actor_id = s.introducer_id
							AND		bd.bdrd_invoice_id = s.invoice_id
							GROUP BY a.a_actor_id, a.a_lettering
						) as x

			LEFT JOIN dbo.acc_accounts ta	ON	ta.a_actor_id = x.a_actor_id
											AND	ta.a_lettering = x.a_lettering
											AND ta.a_type_code = 'T'
	WHERE	i.inv_db_date IS NOT NULL 

GO
