View: dbo.AP_Payment_View | |||
View definition | |||
CREATE VIEW dbo.AP_Payment_View AS select glt.gl_transaction_KEY , bt.bank_transaction_KEY , pt.payable_transaction_KEY , bt.bank_transaction_type_KEY , bt.checkbook_KEY , isnull(vbt.vendor_KEY, pt.vendor_KEY) as vendor_KEY , isnull(bt.transaction_date, pt.transaction_date) as transaction_date , isnull(bt.amount, pt.amount) as amount , isnull(bt.description, pt.description) as description , isnull(bt.transaction_status_KEY, pt.transaction_status_KEY) as transaction_status_KEY , isnull(bt.reference_number, pt.reference_number) as reference_number , isnull(bt.memo, pt.memo) as memo , isnull(pt.reference_number_sortable, bt.reference_number_sortable) as reference_number_sortable , sum(isnull(ppav.paid_amount,0)) as used_payment_amount , case when isnull(bt.transaction_status_KEY, pt.transaction_status_KEY) = 3 -- Voided then 0 else (isnull(bt.amount, pt.amount) - sum(isnull(ppav.paid_amount,0))) end as unused_payment_amount , cast(case -- To be exhausted, a payment must have a zero unused payment amount. when (isnull(bt.amount, pt.amount) - sum(isnull(ppav.paid_amount,0))) = 0 OR isnull(bt.transaction_status_KEY, pt.transaction_status_KEY) = 3 then 1 else 0 end as bit) as is_exhausted from dbo.GL_Transaction as glt left join dbo.GL_Transaction_N_Bank_Transaction as gltnbt on glt.gl_transaction_KEY = gltnbt.gl_transaction_KEY left join dbo.Bank_Transaction as bt on gltnbt.bank_transaction_KEY = bt.bank_transaction_KEY left join dbo.Vendor_Bank_Transaction as vbt on bt.bank_transaction_KEY = vbt.bank_transaction_KEY left join dbo.GL_Transaction_N_Payable_Transaction as gltnpt on glt.gl_transaction_KEY = gltnpt.gl_transaction_KEY left join dbo.Payable_Transaction as pt on gltnpt.payable_transaction_KEY = pt.payable_transaction_KEY left join dbo.Payable_Payment_Application_View as ppav on ppav.payment__gl_transaction_KEY = glt.gl_transaction_KEY where (bt.bank_transaction_KEY is not null and bt.bank_transaction_type_KEY in (10,11)) -- AP Check, AP Payment or (pt.payable_transaction_KEY is not null and pt.payable_transaction_type_KEY = 205) -- Credit Memo group by glt.gl_transaction_KEY , bt.bank_transaction_KEY , pt.payable_transaction_KEY , bt.bank_transaction_type_KEY , bt.checkbook_KEY , vbt.vendor_KEY, pt.vendor_KEY , bt.transaction_date, pt.transaction_date , bt.amount, pt.amount , bt.description, pt.description , bt.transaction_status_KEY, pt.transaction_status_KEY , bt.reference_number, pt.reference_number , bt.memo, pt.memo , pt.reference_number_sortable, bt.reference_number_sortable ; | |||