View: dbo.GL_Transaction_View
View definition
--The views GL_Transaction_View, GL_Transaction_For_Use_By_Enter_Transaction_View,
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View and
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View contain much of the
--same logic. Changes to any of these views may necessitate changes in the others.
--GL_Transaction_For_Use_By_Enter_Transaction_View provides only bank transaction,
--gl balancing entries and journal entries from GL_Transaction_View.
--GL_Transaction_For_Use_By_Enter_Transaction_View joins in additional information to these
--three types. These three types are a subset of the records returned by GL_Transaction_View.
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View provides only bank transactions
--and can only be used when filtering by the checkbook_KEY.
--Bank transactions are a subset of records returned by GL_Transaction_For_Use_By_Enter_Transaction_View.
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View can load data faster than
--GL_Transaction_For_Use_By_Enter_Transaction_View as it does not need to load data from the
--GL_Transaction table.
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View has been forked from
--GL_Transaction_For_Use_By_Enter_Transaction_View
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View is the same as
--GL_Transaction_For_Use_By_Enter_Transaction_View that was released with 2014.1.Q3.
--We needed to revert back to this view for enter transaction edit multiple to
--perform in a decent way. WI 573282 was written up for 2014.1.YE to use
--GL_Transaction_For_Use_By_Enter_Transaction_View for edit multiple as well as ET/EBAT
--context list transaction loading.
--NOTE: in the 2014 October release, using GL_Transaction_For_Use_By_Enter_Transaction_View
--for Enter Transactions journal mode context list loading introduced a serious performance
--regression for entering vendor checks. So, we changed Enter Transactions journal mode to use
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View. (If the comments in the
--block above this block are correct, then in effect that is the same as using the 2014.1.Q3
--version of GL_Transaction_For_Use_By_Enter_Transaction_View.) So, if the work required for
--WI 573282 includes no longer using GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View
--for journal mode context list loading, then that must be done in a manner which ensures that the
--vendor check entry performance regression does not reappear again.
--For WI 1528931, We have introduced two new views GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_With_Created_Date_View and
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_With_Created_Date_View which includes Created_Date logic.
--In WI 1621107, we are going to remove deprecated views GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View and
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View if no performance degradation noticed while using new views.
CREATE VIEW dbo.GL_Transaction_View
AS
SELECT glt.* ,
gl_transaction_source_KEY ,
gl_transaction_type_KEY ,
description ,
reference_number ,
transaction_date ,
bank_transaction_KEY ,
gl_balancing_entry_KEY ,
journal_entry_KEY ,
payable_transaction_KEY ,
reference_number_sortable ,
accounting_application_KEY ,
data_origin_KEY ,
bank_transaction_displayable_status_KEY
FROM dbo.GL_Transaction AS glt
LEFT JOIN ( SELECT nbt.gl_transaction_KEY ,
2 AS gl_transaction_source_KEY ,
bt.bank_transaction_type_KEY AS gl_transaction_type_KEY ,
bt.description AS description ,
bt.reference_number AS reference_number ,
bt.transaction_date AS transaction_date ,
nbt.bank_transaction_KEY AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY ,
NULL AS journal_entry_KEY ,
NULL AS payable_transaction_KEY ,
bt.reference_number_sortable AS reference_number_sortable ,
bt.origin__accounting_application_KEY AS accounting_application_KEY ,
bt.data_origin_KEY AS data_origin_KEY ,
bt.bank_transaction_displayable_status_KEY AS bank_transaction_displayable_status_KEY
FROM dbo.GL_Transaction_N_Bank_Transaction AS nbt
JOIN ( SELECT bank_transaction_KEY = BT.bank_transaction_KEY ,
transaction_date = BT.transaction_date ,
bank_transaction_type_KEY = BT.bank_transaction_type_KEY ,
reference_number = BT.reference_number ,
description = BT.description ,
BT.data_origin_KEY ,
BT.origin__accounting_application_KEY ,
( CASE -- Live, Impound Memo
WHEN BT.transaction_status_KEY IN (
1, 9 )
THEN CASE WHEN BT.bank_transaction_type_KEY IN (
1, 5, 10 )
AND BT.is_handwritten_check = 0
THEN 50 -- Printed
WHEN BT.bank_transaction_type_KEY IN (
1, 5, 10 )
AND BT.is_handwritten_check = 1
THEN 51 -- Handwritten
WHEN BT.bank_transaction_type_KEY IN (
7, 8 ) THEN 52 -- Historical
WHEN BT.bank_transaction_type_KEY = 9
THEN 53 -- Third Party Sick Pay
ELSE 1 -- Live
END
WHEN BT.transaction_status_KEY IN (
2, 4, 5 )
THEN BT.transaction_status_KEY
WHEN BT.transaction_status_KEY IN (
3, 15 )
THEN CASE WHEN BT.bank_transaction_type_KEY = 7
THEN 998 -- Invalid (cannot void a Historical Payment)
WHEN BT.bank_transaction_type_KEY = 8
THEN 54 -- Historical - Voided
WHEN BT.bank_transaction_type_KEY = 9
THEN 55 -- Third Party Sick Pay - Voided
ELSE 3 -- Voided
END
WHEN BT.transaction_status_KEY IN (
6, 7, 8, 13 ) THEN 998 -- Invalid
WHEN BT.transaction_status_KEY = 14
THEN 2 -- Deleted
ELSE 999 -- Unknown (an unknown transaction_status_KEY value was
END -- encountered; code should fail when encoutering this value)
) AS bank_transaction_displayable_status_KEY ,
-- The is_transfer column was created when we thought the Transfer_Bank_Transaction table would
-- contain records for non-impound related transfers. We never did, so this is simplified to
-- always return false. Someday we can clean this up more and remove the column.
BT.reference_number_sortable
FROM dbo.Bank_Transaction BT WITH (FORCESEEK)
) AS bt ON nbt.bank_transaction_KEY = bt.bank_transaction_KEY
UNION ALL
SELECT nbe.gl_transaction_KEY ,
CASE
WHEN be.gl_balancing_entry_type_KEY = 301 /* GLBalancingEntryType.CheckSummaryMemo */ THEN 3 /* GLTransactionSource.CheckSummaryMemo */
WHEN be.gl_balancing_entry_type_KEY = 302 /* GLBalancingEntryType.DepositSummaryMemo */ THEN 9 /* GLTransactionSource.DepositSummaryMemo */
END AS gl_transaction_source_KEY,
be.gl_balancing_entry_type_KEY AS gl_transaction_type_KEY,
be.description ,
be.reference_number ,
be.transaction_date ,
( SELECT MIN(bank_transaction_KEY) AS bank_transaction_KEY
FROM dbo.GL_Balancing_Entry_N_Bank_Transaction
WHERE gl_balancing_entry_KEY = be.gl_balancing_entry_KEY
) ,
nbe.gl_balancing_entry_KEY ,
NULL AS journal_entry_KEY,
NULL AS payable_transaction_KEY,
be.reference_number_sortable ,
1 AS accounting_application_KEY,
1 AS data_origin_KEY,
1 AS bank_transaction_displayable_status_KEY
FROM dbo.GL_Transaction_N_GL_Balancing_Entry AS nbe
JOIN dbo.GL_Balancing_Entry AS be ON nbe.gl_balancing_entry_KEY = be.gl_balancing_entry_KEY
UNION ALL
SELECT nje.gl_transaction_KEY ,
1 AS gl_transaction_source_KEY,
je.journal_entry_type_KEY AS gl_transaction_type_KEY,
je.description ,
je.reference_number ,
je.transaction_date ,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
nje.journal_entry_KEY ,
NULL AS payable_transaction_KEY,
je.reference_number_sortable ,
je.origin__accounting_application_KEY AS accounting_application_KEY,
je.data_origin_KEY ,
je.transaction_status_KEY AS bank_transaction_displayable_status_KEY
FROM dbo.GL_Transaction_N_Journal_Entry AS nje
LEFT JOIN dbo.Journal_Entry AS je ON nje.journal_entry_KEY = je.journal_entry_KEY
UNION ALL
SELECT npt.gl_transaction_KEY ,
4 AS gl_transaction_source_KEY,
pt.payable_transaction_type_KEY AS gl_transaction_type_KEY,
pt.description ,
pt.reference_number ,
pt.transaction_date ,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
pt.payable_transaction_KEY ,
pt.reference_number_sortable ,
pt.origin__accounting_application_KEY AS accounting_application_KEY,
NULL AS data_origin_KEY,
NULL AS bank_transaction_displayable_status_KEY
FROM dbo.GL_Transaction_N_Payable_Transaction AS npt
LEFT JOIN dbo.Payable_Transaction AS pt ON npt.payable_transaction_KEY = pt.payable_transaction_KEY
UNION ALL
SELECT PPA.gl_transaction_KEY ,
5 AS gl_transaction_source_KEY,
401 AS gl_transaction_type_KEY,
N'' AS description,
ISNULL(LEFT(( ISNULL(BT.reference_number,
PT.reference_number)
+ CASE WHEN ISNULL(BT.reference_number,
PT.reference_number) = N''
THEN N''
ELSE N'-'
END
+ ISNULL(MAPPO.reference_number_suffix,
N'PMTAPP') ) COLLATE Latin1_General_CI_AS,
16), N'') AS reference_number ,
PPA.application_date AS transaction_date,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
PPA.payable_transaction_KEY ,
-- We need to grab an appropriate reference_number_sortable here. We have to account
-- for the reference_number_suffix but generating a new reference_number_sortable with
-- the reference_number_suffix added to it is expensive. It's good enough for now to
-- sort by just the reference_number_sortable without the reference_number_suffix. We
-- do want sort by reference_number_suffix or PMTAPP (when suffix is null) when both
-- the BT and PT reference_number_sortable is null or empty.
CASE
WHEN BT.reference_number_sortable IS NOT NULL AND BT.reference_number_sortable <> N''
THEN BT.reference_number_sortable
WHEN PT.reference_number_sortable IS NOT NULL AND PT.reference_number_sortable <> N''
THEN PT.reference_number_sortable
WHEN MAPPO.reference_number_suffix IS NOT NULL
THEN MAPPO.reference_number_suffix
ELSE N'PMTAPP'
END AS reference_number_sortable,
ISNULL(BT.origin__accounting_application_KEY,
PT.origin__accounting_application_KEY) AS accounting_application_KEY,
BT.data_origin_KEY ,
NULL AS bank_transaction_displayable_status_KEY
FROM dbo.Payable_Payment_Application AS PPA
INNER JOIN dbo.GL_Transaction GLA ON PPA.payment__gl_transaction_KEY = GLA.gl_transaction_KEY
INNER JOIN dbo.Journal J ON GLA.journal_KEY = J.journal_KEY
LEFT JOIN dbo.GL_Transaction_N_Bank_Transaction GLTNBT ON PPA.payment__gl_transaction_KEY = GLTNBT.gl_transaction_KEY
LEFT JOIN dbo.Bank_Transaction BT ON GLTNBT.bank_transaction_KEY = BT.bank_transaction_KEY
LEFT JOIN dbo.GL_Transaction_N_Payable_Transaction GLTNPT ON PPA.payment__gl_transaction_KEY = GLTNPT.gl_transaction_KEY
LEFT JOIN dbo.Payable_Transaction PT ON GLTNPT.payable_transaction_KEY = PT.payable_transaction_KEY
LEFT JOIN dbo.Manage_AP_Payment_Option MAPPO ON J.client_KEY = MAPPO.client_KEY
) AS base ON glt.gl_transaction_KEY = base.gl_transaction_KEY
;