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