SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_rep_trn_ck_bdx_details]
	@bordereau_id int
as 

	set nocount on;


	select	
			a.a_operation_utc_date,							-- date
			t.atrn_holder_bank,								-- Etablissement payeur, 			
			tt.trnt_l1, tt.trnt_l2, tt.trnt_l3,				-- type de transaction
			act.act_internal_reference,						-- n° de dossier
			adr.adr_complete_name,							-- nom complet
			t.atrn_reference,								-- n° chèque
			t.atrn_holder,									-- nom du tireur


			--mnt_sur_place =	abs(case 
			--					when t.atrn_check_out_of_town != 0 then 0
			--					else a.a_amount
			--					end),
			--mnt_hors_place = abs(case 
			--					when t.atrn_check_out_of_town != 0 then a.a_amount
			--					else 0
			--					end),

			mnt_sur_place_db =	case 
								when act.act_type_code = 'CLI' and a_amount > 0 then 	abs(case 
																						when t.atrn_check_out_of_town != 0 then 0
																						else a.a_amount
																						end)
								when act.act_type_code != 'CLI' and a_amount < 0 then	abs(case 
																						when t.atrn_check_out_of_town != 0 then 0
																						else a.a_amount
																						end)
								else 0
								end,


			mnt_sur_place_cr =	case 
								when act.act_type_code = 'CLI' and a_amount < 0 then	abs(case 
																						when t.atrn_check_out_of_town != 0 then 0
																						else a.a_amount
																						end)
								when act.act_type_code != 'CLI' and a_amount > 0 then	abs(case 
																						when t.atrn_check_out_of_town != 0 then 0
																						else a.a_amount
																						end)
								else 0
								end,

			mnt_hors_place_db = case 
								when act.act_type_code = 'CLI' and a_amount > 0 then 	abs(case 
																						when t.atrn_check_out_of_town != 0 then a.a_amount
																						else 0
																						end)
								when act.act_type_code != 'CLI' and a_amount < 0 then	abs(case 
																						when t.atrn_check_out_of_town != 0 then a.a_amount
																						else 0
																						end)
								else 0
								end,
			
			


			mnt_hors_place_cr = case 
								when act.act_type_code = 'CLI' and a_amount < 0 then	abs(case 
																						when t.atrn_check_out_of_town != 0 then a.a_amount
																						else 0
																						end)
								when act.act_type_code != 'CLI' and a_amount > 0 then	abs(case 
																						when t.atrn_check_out_of_town != 0 then a.a_amount
																						else 0
																						end)
								else 0
								end,

			mnt_db =			case 
								when act.act_type_code = 'CLI' and a_amount > 0 then 	a.a_amount
								when act.act_type_code != 'CLI' and a_amount < 0 then	abs(a.a_amount)
								end,
			mnt_cr =			case 
								when act.act_type_code = 'CLI' and a_amount < 0 then 	abs(a.a_amount)
								when act.act_type_code != 'CLI' and a_amount > 0 then	a.a_amount
								end,

			-- compte de l'entité
			branch_account_number = aa.acc_account_number,
			branch_iban = aa.acc_iban,
			branch_bank_counter = aa.acc_bank_counter,		-- Code banque
			branch_bank_counter_2 = aa.acc_bank_counter_2,	-- Code guichet
			branch_account_key = aa.acc_account_key			-- Clé compte



	from	acc_trn_bordereaux_details bd
			inner join acc_transactions t	on	t.atrn_transaction_id = bd.trnbd_transaction_id
			inner join acc_accounts a		on	a.a_account_id = t.atrn_transaction_id
			inner join act_actors act		on	act.act_actor_id = a.a_actor_id
			inner join adr_addresses adr	on	adr.adr_address_id = act.act_address_id

			inner join act_accounts aa		on	aa.acc_actor_id = t.atrn_branch_id
											and aa.acc_account_number = t.atrn_account_number__branch

			left join nm_transactions_types tt on tt.trnt_type_code = t.atrn_type_code

	where	bd.trnbd_bordereau_id = @bordereau_id

	order by aa.acc_account_number, a_operation_utc_date






GO
