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

	
	select	a_account_id,
			a_actor_id,
			a_branch_code = act.act_internal_reference,
			pols_number = coalesce(ps.pols_number, psadv.pols_number, ps2.pols_number),
			a_type_code,	
			inv_operation_code__last,
			a_db =	case 
					when UPPER(a.a_type_code) != 'A' and a_amount > 0 then a_amount
					else 0
					end,
			a_cr =	case 
					when UPPER(a.a_type_code) != 'A' and a_amount < 0 then abs(a_amount)
					else 0
					end,
			a_hs =	case
					when UPPER(a.a_type_code) = 'A' then -a_amount
					else 0
					end,
			a.a_currency_code,
			i.inv_start_date,
			i.inv_end_date,
			a_operation_utc_date =	case	
									when UPPER(a.a_type_code) = 'I' then dte
									else a_operation_utc_date
									end,
			i.inv_lettering,
			a_amount,
			a_lettering =	case
							when UPPER(a.a_type_code) = 'T' then isnull(a.a_lettering, a.a_account_id)
							else a.a_lettering
							end,
			adv.adv_lettering,
			i.inv_cts_fee, 
			i.inv_cts_date, 
			i.inv_type_code__cts,

			pf_l1 = isnull(f2.pf_l1, f1.pf_l1), 
			pf_l2 = isnull(f2.pf_l2, f1.pf_l2), 
			pf_l3 = isnull(f2.pf_l3, f1.pf_l3)

	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_invoices i on a.a_account_id = i.inv_invoice_id 
			left join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
			left join pol_policies p1 on p1.pol_policy_id = ps.pols_policy_id
			left join 
					(
					select  invop_invoice_id, dte = MAX(invop_operation_utc_date)
					from	acc_invoices_operations
					group by invop_invoice_id
					) as t on t.invop_invoice_id = i.inv_invoice_id
					
			left join acc_advances adv on adv.adv_advance_id = a.a_account_id 
			left join pol_policies padv on padv.pol_policy_id = adv.adv_policy_id
			left join pol_policies_situations psadv on psadv.pols_policy_situation_id = padv.pol_last_policy_situation_id 
			left join claim_variables_accounts ca on ca.clacc_account_id = a.a_account_id
			left join claim_claims cc on cc.cl_claim_id = ca.clacc_claim_id
			left join pol_policies_situations ps2 on ps2.pols_policy_situation_id = cc.cl_policy_situation_id

			left join pr_products prd1	on	prd1.prd_product_code = p1.pol_product_code
			left join pr_families f1	on	f1.pf_family_code = prd1.prd_family_code

			left join pr_products prd2	on	prd2.prd_product_code = padv.pol_product_code
			left join pr_families f2	on	f2.pf_family_code = prd2.prd_family_code
			
					





GO
