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
;