View: dbo.AR_Receivable_Transaction_View
View definition
-- This view surfaces all Invoice and Finance Charge AR transactions.
CREATE VIEW dbo.AR_Receivable_Transaction_View
AS
select
artv.gl_transaction_KEY
, artv.customer_KEY
, artv.ar_transaction_type_KEY
, artv.transaction_status_KEY
, artv.transaction_date
, artv.reference_number
, artv.reference_number_sortable
, artv.message
, artv.payment_discount_amount_original
, artv.payment_discount_amount
, artv.payment_discount_amount_override
, artv.payment_term_KEY
, artv.due_date
, artv.purchase_order_number
, artv.purchase_order_number_sortable
, artv.gl_account_KEY
, artv.payment_discount_expiration_date
, artv.payment_discount__gl_account_KEY
, artv.shipping__contact_address_type_KEY
, artv.shipping_date
, artv.shipping_salutation
, artv.shipping_name
, artv.amount
, artv.cost_amount
, isnull(arpav.paid_amount, 0) as paid_amount
, isnull(arpav.discount_honored_amount, 0) as discount_honored_amount
, cast(artv.amount - isnull(arpav.paid_amount, 0) - isnull(arpav.discount_honored_amount, 0) as decimal(17,2))
as gross_open_amount
, cast(case when artv.amount = isnull(arpav.paid_amount, 0) + isnull(arpav.discount_honored_amount, 0)
then 1
else 0 end as bit) as is_closed
--the ar_receivable_transaction_KEY is ONLY used for filtering purposes. The gl_transaction_KEY is still the
--the key/column used for identifiying records in this view.
, artv.gl_transaction_KEY as ar_receivable_transaction_KEY
, artv.generated_by__transaction_template_KEY
from
dbo.AR_Transaction_View as artv
left join
(
select
receivable__gl_transaction_KEY
-- Force the two calculated amounts to be decimal(17,2) columns.
, cast(sum(paid_amount) as decimal(17,2)) as paid_amount
, cast(sum(discount_honored_amount) as decimal(17,2)) as discount_honored_amount
from dbo.AR_Payment_Application_View
group by receivable__gl_transaction_KEY
) as arpav
on artv.gl_transaction_KEY = arpav.receivable__gl_transaction_KEY
where
artv.ar_transaction_type_KEY in (501, 503) -- Invoice, Finance Charge
;