View: dbo.GL_Transaction_Hash_View | |||
View definition | |||
create view dbo.GL_Transaction_Hash_View as select gltx.gl_transaction_KEY, jetx.journal_entry_KEY, btx.bank_transaction_KEY, btx.amount as btx_amount, dbo.sf_SimpleNumericParse(jetx.reference_number) as jetx_ref_hash, dbo.sf_SimpleNumericParse(btx.reference_number) as btx_ref_hash from dbo.GL_Transaction as gltx left join dbo.GL_Transaction_N_Journal_Entry as gltx_n_jetx on gltx_n_jetx.gl_transaction_KEY = gltx.gl_transaction_KEY left join dbo.Journal_Entry as jetx on jetx.journal_entry_KEY = gltx_n_jetx.journal_entry_KEY left join dbo.GL_Transaction_N_Bank_Transaction as gltx_n_btx on gltx_n_btx.gl_transaction_KEY = gltx.gl_transaction_KEY left join dbo.Bank_Transaction_View as btx on btx.bank_transaction_KEY = gltx_n_btx.bank_transaction_KEY where -- exclude ALL balancing entry records gltx.gl_transaction_KEY NOT IN (select gl_transaction_KEY from dbo.GL_Transaction_N_GL_Balancing_Entry) AND ( -- include all je's btx.bank_transaction_KEY is null -- we don't want voided, deleted, reversed, or reversal bank transactions OR ( btx.transaction_status_KEY IN ( SELECT transaction_status_KEY FROM [dbo].[Transaction_Status] WHERE transaction_status_KEY NOT IN ( 2, 3, 14, 15 ) ) AND NOT EXISTS ( SELECT 1 FROM dbo.Bank_Transaction_Reversal WHERE reversal__bank_transaction_KEY = btx.bank_transaction_KEY OR reversed__bank_transaction_KEY = btx.bank_transaction_KEY ))) | |||