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