SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE view [dbo].[vw_acc_transactions]
as
	select	a.*, t.*,

			-- compte bancaire de l'entité
			acc_account_number__branch	= bcpt.acc_account_number,
			acc_bank_name__branch		= bbnk.adr_complete_name,
			acc_bic__branch				= bcpt.acc_bic,
			acc_iban__branch			= bcpt.acc_iban, 
			acc_holder__branch			= bcpt.acc_holder,
			acc_residence__branch		= bcpt.acc_residence,
			acc_bank_counter__branch	= bcpt.acc_bank_counter,
			acc_bank_counter_2__branch	= bcpt.acc_bank_counter_2,
			acc_account_key__branch		= bcpt.acc_account_key,
			acc_info__branch			= bcpt.acc_info,

			-- compte bancaire de l'acteur
			acc_account_number__actor	= acpt.acc_account_number,
			acc_bank_name__actor		= abnk.adr_complete_name,
			acc_bic__actor				= acpt.acc_bic,
			acc_iban__actor				= acpt.acc_iban, 
			acc_holder__actor			= acpt.acc_holder,
			acc_residence__actor		= acpt.acc_residence,
			acc_bank_counter__actor		= acpt.acc_bank_counter,
			acc_bank_counter_2__actor	= acpt.acc_bank_counter_2,
			acc_account_key__actor		= acpt.acc_account_key,
			acc_info__actor				= acpt.acc_info,

			atrn_bordereau_id			=	( 
											select max(bd.trnbd_bordereau_id)
											from	acc_trn_bordereaux_details bd
											where	bd.trnbd_transaction_id = t.atrn_transaction_id
											)
											

	from	acc_accounts a
			inner join acc_transactions t	on	t.atrn_transaction_id = a.a_account_id
			
			-- compte bancaire de l'entité
			left join act_accounts bcpt	on	bcpt.acc_actor_id = t.atrn_branch_id
											and	bcpt.acc_account_number = t.atrn_account_number__branch
			-- nom de la banque du compte bancaire entité
			left join vw_act_banks bbnk	on	bbnk.act_actor_id = bcpt.acc_bank_id


			-- compte bancaire de l'acteur, si spécifié
			left join act_accounts acpt		on	acpt.acc_actor_id = t.atrn_actor_id
											and	acpt.acc_account_number = t.atrn_account_number__actor
			-- nom de la banque du compte bancaire acteur
			left join vw_act_banks abnk		on	abnk.act_actor_id = acpt.acc_bank_id





GO
