SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_rep_trn_bf_pp_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

	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

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

	where	bd.trnbd_bordereau_id = @bordereau_id

	order by a_operation_utc_date







GO
