View: dbo.Bank_Transaction_Firm_Client_Uncleared_Combined_View
View definition
`--Union together an indexed view with a direct deposit data for uncleared bank transactions. UNIONs and self --joins cannot be included with an indexed view so we are starting with the general indexed view and using a UNION --to include the special case direct deposit transactions. We cannot use an indexed view for the special case direct --deposit transactions since that query must contain a self reference. --Explanation of special case direct deposits: The second part of the query related to direct deposits returns --the bank transactions that were marked to only have the direct deposit portion funded. --Those are left out of Bank_Transaction_Firm_Client_Uncleared_General_View. --The combination will give us all bank transactions applicable to impound bank reconciliation. CREATE VIEW dbo.Bank_Transaction_Firm_Client_Uncleared_Combined_View AS SELECT ugv.bank_transaction_KEY, ugv.transaction_date, ugv.bank_transaction_type_KEY, ugv.checkbook_KEY, ugv.transaction_status_KEY, ugv.amount, ugv.data_origin_KEY, ugv.reference_number, ugv.description, ugv.auto_clear_in_bank_rec, ugv.client_KEY FROM dbo.Bank_Transaction_Firm_Client_Uncleared_General_View AS ugv UNION SELECT bt.bank_transaction_KEY, bt.transaction_date, bt.bank_transaction_type_KEY, ibt.checkbook_KEY, bt.transaction_status_KEY, bt.amount, bt.data_origin_KEY, bt.reference_number, bt.description, bt.auto_clear_in_bank_rec, ic.client_KEY FROM dbo.Bank_Transaction AS bt --If the transaction was for a checkbook that was marked to only fund direct deposit we need to join --addtional tables to get from the client checkbook on the bank transaction to the corresponding --impound checkbook. If only direct deposit is funded the bank transaction is represented in the --database in a unique manner. The checkbook key is for the client and this does not represent the --direct deposit portion. INNER JOIN dbo.Direct_Deposit_Bank_Transaction AS ddbt ON bt.bank_transaction_KEY = ddbt.bank_transaction_KEY INNER JOIN dbo.Funding_Withdrawal_Bank_Transaction_Detail AS fwbtd ON bt.bank_transaction_KEY = fwbtd.detail__bank_transaction_KEY INNER JOIN dbo.Transfer_Bank_Transaction tbt ON fwbtd.funding_withdrawal__bank_transaction_KEY = tbt.client__bank_transaction_KEY INNER JOIN dbo.Bank_Transaction AS ibt ON tbt.impound__bank_transaction_KEY = ibt.bank_transaction_KEY INNER JOIN dbo.Checkbook AS ichk ON ibt.checkbook_KEY = ichk.checkbook_KEY INNER JOIN dbo.Client AS ic ON ichk.client_KEY = ic.client_KEY WHERE ic.client_type_KEY = 2 /* Firm client */ AND bt.bank_reconciliation_clear_status_KEY <> 2 /* Only not cleared */ AND ddbt.fund_payroll_transaction_type_KEY = 2; /* Marked to fund direct deposit only */`