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 ; | |||