View: dbo.GL_Transaction_For_Use_By_Enter_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_For_Use_By_Enter_Transaction_View]
AS
SELECT
_T1.bank_transaction_displayable_status_KEY,
_T1.bank_transaction_bank_transaction_KEY AS bank_transaction_KEY,
_T1.bank_transaction_checkbook_KEY AS checkbook_KEY,
_T2."client_KEY",
_T1.bank_transaction_deposit AS deposit,
ISNULL(_T1."does_not_affect_accounting", 0) AS "does_not_affect_accounting",
ISNULL(_T1.gl_account_number_list , N'') AS "gl_account_number_list",
-- Substring gl_account_number_list upto 5000 characters to display it in grid, As grid support maximum 5000 chars
ISNULL(LEFT(_T1.gl_account_number_list, 5000) , N'') AS "gl_account_number_list_up_to_5000_chars",
_T1."gl_balancing_entry_KEY",
_T28."gl_note_display",
_T1."gl_period_KEY",
CASE WHEN
(_T1."transaction_status_KEY" IN (2 /* Deleted */, 3 /* Voided */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */))
THEN
CAST(0.00 as decimal(17,2))
ELSE
_T1."amount"
END AS "gl_transaction_amount",
_T1.transaction_date AS "gl_transaction_date",
_T1.description AS "gl_transaction_description",
CAST(CASE WHEN (((_T1."transaction_status_KEY" IN (2 /* Deleted */, 3 /* Voided */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */))
)) THEN CAST(0.00 as decimal(17,2)) ELSE _T1."amount" END AS nvarchar(max)) AS "gl_transaction_display_amount",
_T1."gl_transaction_KEY",
_T1.reference_number AS "gl_transaction_reference_number",
_T1.reference_number_sortable AS "gl_transaction_reference_number_sortable",
_T1."gl_transaction_type_KEY",
_T1.bank_transaction_id as "id",
_T1.bank_transaction_id_sortable as "id_sortable",
_T1.journal_entry_KEY,
_T1."journal_KEY",
_T1.bank_transaction_memo as "memo",
_T1.bank_transaction_payment as "payment",
ISNULL(_T1.transaction_date, CONVERT(DATETIME, '2079-06-06T00:00:00.000', 126)) AS "sort_date",
CAST(_T1.bank_transaction_sort_deposit as decimal(17,2)) as "sort_deposit",
CAST(_T1.bank_transaction_sort_payment as decimal(17,2)) as "sort_payment",
_T1."transaction_status_KEY" AS "transaction_status_KEY",
_T1.bank_transaction_vendor_KEY AS "vendor_KEY"
FROM (
SELECT glt.gl_transaction_KEY ,
glt.journal_KEY ,
glt.gl_period_KEY ,
glt.override_source_type_to_display_as_journal_entry ,
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 ,
bank_transaction_displayable_status_KEY ,
glt.is_posting_period_overridden,
gl_account_number_list,
transaction_status_KEY,
amount,
does_not_affect_accounting,
bank_transaction_transaction_status, --null for non bank transaction types
bank_transaction_is_impound_checkbook, --null for non bank transaction types
bank_transaction_bank_transaction_KEY, --null for non bank transaction types
bank_transaction_checkbook_KEY, --null for non bank transaction types
bank_transaction_deposit, --null for non bank transaction types
bank_transaction_payment, --null for non bank transaction types
bank_transaction_sort_deposit, --null for non bank transaction types
bank_transaction_sort_payment, --null for non bank transaction types
bank_transaction_id, --null for non bank transaction types
bank_transaction_id_sortable, --null for non bank transaction types
bank_transaction_memo, --null for non bank transaction types
bank_transaction_vendor_KEY --null for non bank transaction types
FROM
dbo.GL_Transaction AS glt
--------------------------------------------- bank transaction ------------------------------------
---------------------------------------------------------------------------------------------------
LEFT OUTER 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.bank_transaction_displayable_status_KEY AS bank_transaction_displayable_status_KEY,
CASE WHEN LEN(SUBSTRING(LTRIM(bt.btx_gl_account_number),1,1))>0 and LEN(SUBSTRING(LTRIM(bt.dist_gl_account_number_list),1,1))>0 THEN
bt.btx_gl_account_number + N', ' + bt.dist_gl_account_number_list
WHEN LEN(SUBSTRING(LTRIM(bt.btx_gl_account_number),1,1))>0 THEN
bt.btx_gl_account_number
ELSE
bt.dist_gl_account_number_list
END AS gl_account_number_list,
bt.transaction_status_KEY AS transaction_status_KEY,
bt.amount AS amount,
bt.does_not_affect_accounting AS does_not_affect_accounting,
bt.transaction_status_KEY AS bank_transaction_transaction_status,
bt.is_impound_checkbook AS bank_transaction_is_impound_checkbook,
bt.bank_transaction_KEY AS bank_transaction_bank_transaction_KEY,
bt.checkbook_KEY AS bank_transaction_checkbook_KEY,
bt.deposit AS bank_transaction_deposit,
bt.payment AS bank_transaction_payment,
bt.sort_deposit AS bank_transaction_sort_deposit,
bt.sort_payment AS bank_transaction_sort_payment,
bt.id AS bank_transaction_id,
bt.id_sortable AS bank_transaction_id_sortable,
bt.memo AS bank_transaction_memo,
bt.vendor_KEY AS bank_transaction_vendor_KEY
FROM
dbo.GL_Transaction_N_Bank_Transaction AS nbt
JOIN
dbo.GL_Transaction_Bank_Transaction_Shared_View AS bt
ON
nbt.bank_transaction_KEY = bt.bank_transaction_KEY
--------------------------------------------- balancing entry ------------------------------------
--------------------------------------------------------------------------------------------------
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 ,
NULL AS bank_transaction_displayable_status_KEY,
ACNL.gl_account_number_list,
1 AS transaction_status_KEY, -- Live transaction status
be.amount,
0 AS does_not_affect_accounting,
NULL AS bank_transaction_transaction_status,
NULL AS bank_transaction_is_impound_checkbook,
NULL AS bank_transaction_bank_transaction_KEY, -- (only from bank transactions)
NULL AS bank_transaction_checkbook_KEY,
NULL AS bank_transaction_deposit,
NULL AS bank_transaction_payment,
NULL AS bank_transaction_sort_deposit,
NULL AS bank_transaction_sort_payment,
NULL AS bank_transaction_id,
NULL AS bank_transaction_id_sortable,
NULL AS bank_transaction_memo,
NULL AS bank_transaction_vendor_KEY
FROM dbo.GL_Transaction_N_GL_Balancing_Entry AS nbe
JOIN (
SELECT
gl_balancing_entry_KEY = glbe.gl_balancing_entry_KEY
, reference_number = glbe.reference_number
, transaction_date = glbe.transaction_date
, amount = CAST(SUM(COALESCE(bt.amount,0)) AS decimal(17,2))
, description = glbe.description
, gl_balancing_entry_type_KEY = glbe.gl_balancing_entry_type_KEY
, reference_number_sortable = glbe.reference_number_sortable
, creation_date = glbe.creation_date
, staff_KEY = glbe.staff_KEY
FROM
dbo.GL_Balancing_Entry AS glbe
left join dbo.GL_Balancing_Entry_N_Bank_Transaction AS blbenbt
ON blbenbt.gl_balancing_entry_KEY = glbe.gl_balancing_entry_KEY
left join
(
SELECT
bank_transaction_KEY, bank_transaction_type_KEY, amount
FROM
dbo.Bank_Transaction_View
WHERE
transaction_status_KEY = 1 -- Live
AND does_not_affect_accounting = 0 -- Only include the portion that actually affects the GL balance.
) AS bt on bt.bank_transaction_KEY = blbenbt.bank_transaction_KEY
GROUP BY
glbe.gl_balancing_entry_KEY
, glbe.reference_number
, glbe.transaction_date
, glbe.description
, glbe.gl_balancing_entry_type_KEY
, glbe.reference_number_sortable
, glbe.creation_date
, glbe.staff_KEY) AS be ON nbe.gl_balancing_entry_KEY = be.gl_balancing_entry_KEY
LEFT OUTER JOIN
( select
glbenbt.gl_balancing_entry_KEY
, gl_account_number_list = isnull(gla.gl_account_number, cugla_gla.gl_account_number)
from
(
select gl_balancing_entry_KEY, min(bank_transaction_KEY) as bank_transaction_KEY
from dbo.GL_Balancing_Entry_N_Bank_Transaction
group by gl_balancing_entry_KEY
) as glbenbt
INNER JOIN dbo.Bank_Transaction as bt
on bt.bank_transaction_KEY = glbenbt.bank_transaction_KEY
INNER JOIN dbo.Checkbook as cbk
on bt.checkbook_KEY = cbk.checkbook_KEY
INNER JOIN dbo.Client_Undistributed_GL_Account as cugla on cugla.client_KEY = cbk.client_KEY
INNER JOIN dbo.GL_Account as cugla_gla on cugla_gla.gl_account_KEY = cugla.gl_account_KEY
LEFT JOIN dbo.GL_Account as gla
ON gla.gl_account_KEY = cbk.gl_account_KEY
)
AS ACNL ON nbe.gl_balancing_entry_KEY = ACNL.gl_balancing_entry_KEY
--------------------------------------------- journal entry ------------------------------------
--------------------------------------------------------------------------------------------------
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,
NULL AS bank_transaction_displayable_status_KEY, --Matches with Bank_Transaction displayable transaction status key.
JNL.gl_account_number_list,
je.transaction_status_KEY, --Matches with Bank_Transaction transaction status key.
JEA.amount,
0 AS does_not_affect_accounting,
NULL AS bank_transaction_transaction_status,
NULL AS bank_transaction_is_impound_checkbook,
NULL AS bank_transaction_bank_transaction_KEY,
NULL AS bank_transaction_checkbook_KEY,
NULL AS bank_transaction_deposit,
NULL AS bank_transaction_payment,
NULL AS bank_transaction_sort_deposit,
NULL AS bank_transaction_sort_payment,
NULL AS bank_transaction_id,
NULL AS bank_transaction_id_sortable,
NULL AS bank_transaction_memo,
NULL AS bank_transaction_vendor_KEY
FROM dbo.GL_Transaction_N_Journal_Entry AS nje
INNER JOIN dbo.Journal_Entry AS je ON nje.journal_entry_KEY = je.journal_entry_KEY
LEFT JOIN
(SELECT
journal_entry_KEY = target.journal_entry_KEY,
gl_account_number_list = REPLACE(RTRIM((
SELECT CAST(outerView.gl_account_number AS varchar(MAX)) + N' '
FROM dbo.Journal_Entry_Account_List_Detail_View AS outerView
WHERE (outerView.journal_entry_KEY = target.journal_entry_KEY)
AND NOT EXISTS (
SELECT 1
FROM dbo.Journal_Entry_Account_List_Detail_View AS innerView
WHERE outerView.journal_entry_KEY = innerView.journal_entry_KEY
AND outerView.sequence_number > innerView.sequence_number
-- We cannot elimintate duplicates on sequence number, and are living with that
)
ORDER BY sequence_number
FOR XML PATH (N'') ) )
,N' '
,N', ')
FROM
dbo.Journal_Entry as target) AS JNL ON nje.journal_entry_KEY = JNL.journal_entry_KEY
INNER JOIN
(SELECT CAST(SUM(MJED."amount") AS decimal(17,2)) AS "amount",
MJED."journal_entry_KEY"
FROM ( SELECT JED."amount",
JED."description",
JED."gl_account_KEY",
JED."is_subsidiary_client_summary",
JED."journal_entry_distribution_KEY",
JED."journal_entry_KEY",
JED."sequence_number",
JED."workpaper_reference"
FROM dbo.Journal_Entry_Distribution AS JED
WHERE (JED."amount" >= 0)
) AS MJED
GROUP BY MJED."journal_entry_KEY") AS JEA ON je."journal_entry_KEY" = JEA."journal_entry_KEY"
) AS base ON glt.gl_transaction_KEY = base.gl_transaction_KEY
) AS _T1
----------------------------------------- -------------- --------------------------------------
-----------------------------------------------------------------------------------------------
LEFT OUTER JOIN dbo.Journal AS _T2 ON _T1."journal_KEY" = _T2."journal_KEY"
LEFT OUTER JOIN
(SELECT CONVERT(bit, 1) AS "gl_note_display",
_T27."gl_transaction_KEY"
FROM
(SELECT _T26."gl_transaction_KEY"
FROM dbo.GL_Transaction_Note AS _T26
) AS _T27
GROUP BY _T27."gl_transaction_KEY"
) AS _T28 ON _T1."gl_transaction_KEY" = _T28."gl_transaction_KEY"
WHERE
(_T1."gl_transaction_type_KEY" IN (
1 /*Check*/,
2 /*Deposit*/,
3 /*Payment*/,
4 /*Adjustment*/,
5 /*PayrollCheck*/,
6 /*Addition*/,
10 /*ApCheck*/,
11 /*ApPayment*/,
12 /*Ar Deposit*/,
7 /*HistoricalPayment*/,
8 /*HistoricalPayrollCheck*/,
9 /*ThirdPartySickPayPayrollCheck*/,
101 /*JournalEntry*/,
103 /*AdjustingJournalEntry*/,
104 /*TaxAdjustmentJournalEntry*/,
105 /*ReclassifyingJournalEntry*/,
106 /*OtherJournalEntry*/,
107 /*PotentialJournalEntry*/,
301 /*CheckSummaryMemo*/,
302 /*DepositSummaryMemo*/))
AND ( (_T1."bank_transaction_transaction_status" IS NULL)
OR (_T1."bank_transaction_transaction_status" IN (1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 9 /* ImpoundMemo */, 14 /* ImpoundMemoDeleted*/, 15 /* ImpoundMemoVoided */)))
AND ( (_T1."bank_transaction_is_impound_checkbook" IS NULL)
OR NOT (1 = (_T1."bank_transaction_is_impound_checkbook")))