SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE view [dbo].[vw_acc_account_actors]
as

	select	a.a_account_id,
			a.a_actor_id,
			a_branch_code = act.act_internal_reference,
			a_type_code,	
			bdr_operation_code = coalesce(bdr.bdr_operation_code, ca.clacc_operation_code, ba.clba_operation_code),
			a_operation_utc_date,
			a_db =	case 
					when UPPER(a.a_type_code) not in ('P', 'CSD') AND a_amount < 0 then abs(a_amount)
					else 0
					end,
			a_cr =	case 
					when UPPER(a.a_type_code) not in ('P', 'CSD') AND a_amount > 0 then a_amount
					else 0
					end,
			a_hs =	case
					when UPPER(a.a_type_code) = 'P' then -a_amount
					when UPPER(a.a_type_code) = 'CSD' then a_amount
					else 0
					end,
			a.a_currency_code,
			a.a_amount,
			a.a_lettering,
			p.pay_lettering, p.pay_taken,
			act.act_type_code,
			ISNULL(bdr.bdr_reference, bc.clb_reference) as bdr_reference 
	from	acc_accounts a
			inner join act_branches b on b.br_actor_id = a.a_branch_id 
			inner join act_actors act on act.act_actor_id = b.br_actor_id

			left join acc_payments p on p.pay_payment_id = a.a_account_id
			left join acc_bordereaux bdr on bdr.bdr_bordereau_id = a.a_account_id	

			left join claim_variables_accounts ca on ca.clacc_account_id = a.a_account_id
			
			left join acc_cl_bordereaux_accounts ba on ba.clba_account_id = a.a_account_id
			left join acc_cl_bordereaux bc ON bc.clb_bordereau_id = ba.clba_bordereau_id

GO
