View: dbo.GL_Balancing_Entry_View
View definition
create view dbo.GL_Balancing_Entry_View
as
select
gl_balancing_entry_KEY = glbe.gl_balancing_entry_KEY
, reference_number = glbe.reference_number
, transaction_date = glbe.transaction_date
, amount = sum(coalesce(bt.amount,0))
, payroll_amount
= sum(case
when bt.bank_transaction_type_KEY is not null and bt.bank_transaction_type_KEY = 5 -- Payroll Check
then bt.amount
else 0
end )
, description = glbe.description
, gl_balancing_entry_type_KEY = glbe.gl_balancing_entry_type_KEY
, reference_number_sortable = glbe.reference_number_sortable
, creation_date = glbe.creation_date
, staff_KEY = glbe.staff_KEY
from
dbo.GL_Balancing_Entry as glbe
left join dbo.GL_Balancing_Entry_N_Bank_Transaction as blbenbt
on blbenbt.gl_balancing_entry_KEY = glbe.gl_balancing_entry_KEY
left join
(
select
bank_transaction_KEY, bank_transaction_type_KEY, amount
from
dbo.Bank_Transaction_View
where
transaction_status_KEY = 1 -- Live
and does_not_affect_accounting = 0 -- Only include the portion that actually affects the GL balance.
) as bt on bt.bank_transaction_KEY = blbenbt.bank_transaction_KEY
group by
glbe.gl_balancing_entry_KEY
, glbe.reference_number
, glbe.transaction_date
, glbe.description
, glbe.gl_balancing_entry_type_KEY
, glbe.reference_number_sortable
, glbe.creation_date
, glbe.staff_KEY