View: dbo.AR_Deposit_View | |||
View definition | |||
CREATE VIEW dbo.AR_Deposit_View AS -- we want the records for bank transactions of type AR Deposit, and the other records that will give us -- the complete AR Deposit transaction. SELECT generalLedgerTransaction.gl_transaction_KEY , bankTransaction.bank_transaction_KEY , bankTransaction.transaction_date , bankTransaction.description , bankTransaction.bank_transaction_type_KEY , bankTransaction.transaction_status_KEY , bankTransaction.checkbook_KEY , bankTransaction.reference_number , checkbook.client_KEY , generalLedgerTransaction.journal_KEY , generalLedgerTransaction.gl_period_KEY , arPaymentTable.payment_total_amount, -- the cash back transaction distribution amount will typically be stored as a negative but -- in the view we want the cash_back_amount to be positive. -cashBackBankTransDistribution.amount AS cash_back_amount , bankTransaction.amount AS deposit_amount -- get bank transaction and gl transaction records. FROM dbo.Bank_Transaction AS bankTransaction INNER JOIN dbo.GL_Transaction_N_Bank_Transaction AS gltnbt ON bankTransaction.bank_transaction_KEY = gltnbt.bank_transaction_KEY INNER JOIN dbo.GL_Transaction AS generalLedgerTransaction ON gltnbt.gl_transaction_KEY = generalLedgerTransaction.gl_transaction_KEY INNER JOIN dbo.Checkbook AS checkbook ON bankTransaction.checkbook_KEY = checkbook.checkbook_KEY -- get the cash back bank transaction distribution record, if any, that matches the AR Deposit bank transaction. -- this will give us the key to the bank transaction distribution that is designated as the -- cash back distribution. LEFT JOIN dbo.AR_Deposit_Cash_Back_Distribution AS arDepositCashBackDistribution ON bankTransaction.bank_transaction_KEY = arDepositCashBackDistribution.ar_deposit__bank_transaction_KEY -- get the total payment amount for the AR Payments that are linked to the AR Deposit bank transaction. CROSS APPLY ( SELECT SUM(amount) AS payment_total_amount FROM dbo.AR_Payment WHERE bankTransaction.bank_transaction_KEY = ar_deposit__bank_transaction_KEY ) AS arPaymentTable -- get the cash back bank transaction distribution amount, if any. -- if we had a record in the arDepositCashBackDistribution table with a match on bank_transaction_KEY, -- then we should find a match here, on the bank_transaction_distribution_KEY. LEFT JOIN dbo.Bank_Transaction_Distribution AS cashBackBankTransDistribution ON cashBackBankTransDistribution.bank_transaction_distribution_KEY = arDepositCashBackDistribution.cash_back__bank_transaction_distribution_KEY WHERE -- we only want bank transactions of type AR Deposit ( bankTransaction.bank_transaction_type_KEY IN ( 12 ) ) -- AR Deposit | |||