View: dbo.GL_Transaction_Event_Date_View
View definition
--This view will be used for filter the transactions through created date or finalize date.
--The views GL_Transaction_View, GL_Transaction_For_Use_By_Enter_Transaction_View,
--GL_Transaction_For_Use_By_Enter_Transaction_Via_Checkbook_View,
--GL_Transaction_For_Use_By_Enter_Transaction_Edit_Multiple_View and
--GL_Transaction_Event_Date_View contain much of the same logic.
--Changes to any of these views may necessitate changes in the others.
--In this view AR_Transaction and AR_Payment tables gets join which in not present in above view.
--Added bnak_transaction_KEY, gl_balancing_entry_KEY, journal_entry_KEY and payable_transaction_KEY
--so that this view can be use for all filter where created date or finalized date need to add.
CREATE VIEW [dbo].[GL_Transaction_Event_Date_View]
AS
SELECT glt.gl_transaction_KEY,
created_date,
finalized_date,
bank_transaction_KEY,
gl_balancing_entry_KEY,
journal_entry_KEY,
payable_transaction_KEY
FROM dbo.GL_Transaction AS glt
LEFT JOIN ( SELECT nbt.gl_transaction_KEY,
bt.created_date,
bt.finalized_date,
nbt.bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
NULL AS payable_transaction_KEY
FROM dbo.GL_Transaction_N_Bank_Transaction AS nbt
JOIN ( SELECT bank_transaction_KEY = BT.bank_transaction_KEY,
BTE.created_date, BTE.finalized_date
FROM dbo.Bank_Transaction BT WITH (FORCESEEK)
JOIN (SELECT DISTINCT BTE1.bank_transaction_KEY, created_date, finalized_date FROM
(SELECT bank_transaction_KEY, event_date AS created_date
FROM Bank_Transaction_Event WHERE bank_transaction_event_type_KEY = 1) AS BTE1
LEFT JOIN ( SELECT bank_transaction_KEY, event_date AS finalized_date
FROM Bank_Transaction_Event WHERE bank_transaction_event_type_KEY = 2 ) AS BTE2
ON BTE1.bank_transaction_KEY = BTE2.bank_transaction_KEY
) AS BTE ON BTE.bank_transaction_KEY = BT.bank_transaction_KEY
) AS bt ON nbt.bank_transaction_KEY = bt.bank_transaction_KEY
UNION ALL
SELECT nbe.gl_transaction_KEY,
be.creation_date AS created_date,
NULL AS finalized_date,
NULL AS bank_transaction_KEY,
nbe.gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
NULL AS payable_transaction_KEY
FROM dbo.GL_Transaction_N_GL_Balancing_Entry AS nbe
LEFT 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,
je.created_date,
je.finalized_date,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
nje.journal_entry_KEY,
NULL AS payable_transaction_KEY
FROM dbo.GL_Transaction_N_Journal_Entry AS nje
JOIN ( SELECT journal_entry_KEY = JE.journal_entry_KEY,
jee.created_date,
jee.finalized_date
FROM dbo.Journal_Entry AS JE
JOIN (SELECT distinct BTE1.journal_entry_KEY, created_date, finalized_date FROM
(SELECT journal_entry_KEY, event_date AS created_date FROM Journal_Entry_Event
WHERE journal_entry_event_type_KEY = 1) AS BTE1
LEFT JOIN (SELECT journal_entry_KEY, event_date AS finalized_date FROM Journal_Entry_Event
WHERE journal_entry_event_type_KEY = 2)AS BTE2
ON BTE1.journal_entry_KEY = BTE2.journal_entry_KEY
) AS jee ON jee.journal_entry_KEY = JE.journal_entry_KEY
) AS je ON nje.journal_entry_KEY = je.journal_entry_KEY
UNION ALL
SELECT npt.gl_transaction_KEY,
pt.created_date,
NULL AS finalized_date,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
pt.payable_transaction_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 gl_transaction_KEY,
created_date,
NULL AS finalized_date,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
NULL AS payable_transaction_KEY
FROM dbo.AR_Transaction
UNION ALL
SELECT gl_transaction_KEY,
created_date,
NULL AS finalized_date,
NULL AS bank_transaction_KEY,
NULL AS gl_balancing_entry_KEY,
NULL AS journal_entry_KEY,
NULL AS payable_transaction_KEY
FROM dbo.AR_Payment
) AS base ON glt.gl_transaction_KEY = base.gl_transaction_KEY
;