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