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