SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_poema_compta_sinistres]
AS 
SELECT	
		[ID transaction] = cva.clacc_claim_id,
		[No sinistre] = CAST(YEAR(c.cl_declaration_date) AS VARCHAR(4)) + '-' + c.cl_internal_reference,
		[Code Compagnie] = pcie.act_internal_reference,
		[Compagnie] = cie.cie_display_name,
		[Date transaction -> sinistré] = CAST(a.a_operation_utc_date AS DATE) ,
		[Date transaction -> compagnie] = cast(ta.a_operation_utc_date AS DATE),
		[Nature Transaction] =	CASE
								WHEN a.a_type_code = 'CP' THEN 'Règlement sinistre'
								ELSE 'Recours'
								END,
		[Montant poe-ma -> sinistré] =	CASE WHEN ract.act_type_code = 'CIE' THEN 0 ELSE ABS(a.a_amount) END,
		[Montant poe-ma -> compagnie] = CASE WHEN ract.act_type_code = 'CIE' THEN ABS(a.a_amount) ELSE 0 END

FROM	dbo.claim_variables_accounts cva
		INNER JOIN dbo.claim_claims c ON c.cl_claim_id = cva.clacc_claim_id
		INNER JOIN dbo.pol_policies_situations ps ON ps.pols_policy_situation_id = c.cl_policy_situation_id
		INNER JOIN dbo.pol_policies p ON p.pol_policy_id = ps.pols_policy_id
		INNER JOIN dbo.act_actors pcie ON pcie.act_actor_id = p.pol_company_id
		INNER JOIN dbo.act_cies cie ON cie.cie_actor_id = pcie.act_actor_id

		INNER JOIN dbo.acc_accounts a ON a.a_account_id = cva.clacc_account_id

		INNER JOIN dbo.act_actors ract ON ract.act_actor_id = a.a_actor_id 

		CROSS APPLY (	SELECT	TOP (1) t.a_operation_utc_date
						FROM	dbo.acc_accounts t
						WHERE	t.a_actor_id = a.a_actor_id
						AND		t.a_lettering = a.a_lettering
						AND		t.a_type_code = 'T'
						ORDER BY a_account_id DESC ) AS ta
		

WHERE	cva.clacc_disable_accounting = 0
AND		a.a_type_code IN ('CP', 'CR')
AND		a.a_lettering IS NOT NULL	-- règlements / recours validés
GO
