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