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