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 | |||