View: dbo.AP_Payable_View
View definition
create view dbo.AP_Payable_View
as
select
pt.payable_transaction_KEY
, pt.vendor_KEY
, pt.transaction_date
, pt.amount
, pt.description
, pt.due_date
, pt.payable__gl_account_KEY
, pt.transaction_status_KEY
, pt.origin__accounting_application_KEY
, pt.reference_number
, pt.purchase_order_number
, pt.vendor__contact_address_type_KEY
, pt.payment_term_KEY
, pt.discount_expiration_date
, pt.not_eligible_for_discount_allowed_amount
, pt.discount_allowed_amount
, pt.memo
, pt.ap_discount_method_KEY
, pt.is_discount_forced
, pt.reference_number_sortable
, pt.purchase_order_number_sortable
, coalesce(ppa.paid_amount, 0) as paid_amount
, coalesce(ppa.discount_taken_amount, 0) as discount_taken_amount
, (pt.discount_allowed_amount - coalesce(ppa.discount_taken_amount, 0)) as discount_unused_amount
, case
when pt.transaction_status_KEY = 3 -- Voided
then 0
else (pt.amount
- coalesce(ppa.paid_amount, 0)
- coalesce(ppa.discount_taken_amount, 0))
end as gross_open_balance_amount
, cast(case -- To be closed, a payable must have a gross open balance of zero or be voided.
when (pt.amount - coalesce(ppa.paid_amount, 0) - coalesce(ppa.discount_taken_amount, 0)) = 0 OR pt.transaction_status_KEY = 3
then 1
else 0
end
as bit) as is_closed
from
dbo.Payable_Transaction as pt
cross apply
(
-- Grab the payable payment application's payable distribution amounts and sum them for the payable.
select
sum(pad.distributed__paid_amount) as paid_amount ,
sum(pad.distributed__discount_taken_amount) as discount_taken_amount
from
dbo.Payable_Payment_Application as pa
inner join dbo.Payable_Payment_Application_Payable_Distribution as pad
on pa.gl_transaction_KEY = pad.gl_transaction_KEY
where
pt.payable_transaction_KEY = pa.payable_transaction_KEY
) as ppa
where
pt.payable_transaction_type_KEY = 204 -- PayableLiability
;