View: dbo.GL_Bank_Reconciliation_Clear_Status_View
View definition
`-- The UI in EBAT/ET shows a status of partially cleared in circumstances -- unrelated to the actual bank reconciliation clear status of the item. -- When an item has a distribution that’s to another bank account's account, -- then it is considered partially cleared if any of these proxy distributions -- are cleared. So Journal Entries and Bank Transactions can have a Bank -- Reconciliation Status of uncleared that’s related to distributions. -- Bank Transactions that are uncleared will be considered partially -- cleared if they have proxy distributions that are cleared. Journal Entries -- will have a status of partially cleared if they have proxy distributions -- that are cleared. If a Journal Entry has proxies and they are all not -- cleared, then the Journal entry is uncleared. CREATE VIEW dbo.GL_Bank_Reconciliation_Clear_Status_View AS SELECT gltnbt.gl_transaction_KEY , gltnbt.bank_transaction_KEY , journal_entry_KEY = cast(null as int) , bank_reconciliation_clear_status_KEY = CASE -- When a bank transaction doesn’t have any bank transaction reconciliation details or when none of -- it’s existing details have been cleared, then the transaction is Uncleared. UNLESS the bank -- transaction has one or more proxy bank transactions that have been cleared. In this case, the -- transction is considered Partially Cleared. Confusing? Yep, but that's what the Enter Transaction -- data entry view does so that's how the analyst wants this t-sql view to work. WHEN COUNT(btrd.bank_reconciliation_statement_KEY) = 0 THEN case when bt_with_cleared_proxy.bank_transaction_KEY is null then 1 -- Uncleared else 3 -- Partially Cleared end -- When all of a bank transaction's detail has been cleared, then the transaction is Cleared. WHEN COUNT(btrd.bank_transaction_KEY) = COUNT(btrd.bank_reconciliation_statement_KEY) THEN 2 -- Cleared -- Otherwise, some of the detail is cleared and some uncleared so the transaction is Partially Cleared. ELSE 3 -- Partially Cleared END FROM dbo.GL_Transaction_N_Bank_Transaction gltnbt LEFT JOIN dbo.Bank_Transaction_Reconciliation_Detail btrd ON gltnbt.bank_transaction_KEY = btrd.bank_transaction_KEY left join ( select btd.bank_transaction_KEY from dbo.Bank_Transaction_Accounting_Source_Override_Bank_Transaction_Distribution as proxy inner join dbo.Bank_Transaction_Reconciliation_Detail btrd on proxy.bank_transaction_KEY = btrd.bank_transaction_KEY inner join dbo.Bank_Transaction_Distribution as btd on proxy.bank_transaction_distribution_KEY = btd.bank_transaction_distribution_KEY where btrd.bank_reconciliation_statement_KEY is not null -- Cleared proxy details group by btd.bank_transaction_KEY ) as bt_with_cleared_proxy on gltnbt.bank_transaction_KEY = bt_with_cleared_proxy.bank_transaction_KEY GROUP BY gltnbt.bank_transaction_KEY, gltnbt.gl_transaction_KEY, bt_with_cleared_proxy.bank_transaction_KEY UNION ALL select gl_transaction_KEY , bank_transaction_KEY = cast(null as int) , GLTNJE.journal_entry_KEY -- Journal Entries that have cleared proxy bank transactions always have a clear status of Partially Cleared. , bank_reconciliation_clear_status_KEY = CASE WHEN CLEAR_COUNT = 0 THEN 1 -- Uncleared ELSE 3 -- Partially Cleared END from dbo.GL_Transaction_N_Journal_Entry GLTNJE JOIN ( select jed.journal_entry_KEY, COUNT(btrd.bank_reconciliation_statement_KEY) CLEAR_COUNT from dbo.Bank_Transaction_Accounting_Source_Override_Journal_Entry_Distribution as proxy inner join dbo.Journal_Entry_Distribution as jed on proxy.journal_entry_distribution_KEY = jed.journal_entry_distribution_KEY left join dbo.Bank_Transaction_Reconciliation_Detail as btrd on proxy.bank_transaction_KEY = btrd.bank_transaction_KEY GROUP BY jed.journal_entry_KEY ) X ON X.journal_entry_KEY = GLTNJE.journal_entry_KEY`