SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_transactions_get_related_info]
	@account_id int -- id de la transaction
as 

	set nocount on;

	/*

	DGE
	Renvoie :
	- la ligne de la transaction;
	- les quittances liées (lettrage);
	- les lignes d'acompte liées (lettrage)


	*/

	declare @t type_table_id

	insert into @t 
	select	ac.a_account_id
	from	acc_accounts a
			inner join acc_transactions t on t.atrn_transaction_id = a.a_account_id
			inner join	(
						select	*
						from	acc_accounts a1
						)as ac	on	ac.a_actor_id = t.atrn_actor_id
								and	ac.a_lettering = a.a_lettering
	where	a.a_account_id = @account_id

	-- acc_accounts
	select	a.*
	from	acc_accounts a
			inner join @t t on t.id = a.a_account_id

	-- transaction
	select	trn.*
	from	acc_transactions trn
	where	trn.atrn_transaction_id = @account_id

	-- advances (acomptes)
	select	adv.*
	from	acc_advances adv
			inner join @t t on t.id = adv.adv_advance_id

	-- invoices (quittances)
	select	inv.*
	from	acc_invoices inv
			inner join @t t on t.id = inv.inv_invoice_id

	-- opérations quittances
	select	op.*
	from	acc_invoices_operations op
			inner join @t t on t.id = op.invop_invoice_id

GO
