SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE view [dbo].[vw_acc_bordereaux_variables_bordereaux_cie]
AS
	/*
	une variable (contrat ou police) peut se retrouver sur plusieurs bordereaux de la même compagnie
	*/

	SELECT	abv.bdrv_invoice_id, 
			abv.bdrv_variable_id, 
			a.a_actor_id ,
			bdrv_bdr_id__p = max(CASE WHEN abc.bdrcie_type_code = 'P' THEN abv.bdrv_bordereau_id ELSE NULL end),
			bdrv_bdr_id__c = max(CASE WHEN abc.bdrcie_type_code = 'C' THEN abv.bdrv_bordereau_id ELSE NULL end),
			bdrv_bdr_id__pc= max(CASE WHEN abc.bdrcie_type_code = 'PC' THEN abv.bdrv_bordereau_id ELSE NULL end)				
	FROM	dbo.acc_bordereaux_variables abv
			INNER JOIN dbo.acc_bordereaux_companies abc ON abc.bdrcie_bordereau_id = abv.bdrv_bordereau_id
			INNER JOIN acc_accounts a ON a.a_account_id = abv.bdrv_bordereau_id
	GROUP BY abv.bdrv_invoice_id, abv.bdrv_variable_id, a.a_actor_id
GO
