View: dbo.AR_Payment_Transaction_View
View definition
-- This view surfaces all AR payments - credit memos, check, cash, credit card, write offs.
CREATE VIEW dbo.AR_Payment_Transaction_View
AS
select
pmt.gl_transaction_KEY
, pmt.gl_transaction_type_KEY
, pmt.transaction_status_KEY
, pmt.customer_KEY
, pmt.customer_name
, pmt.reference_number
, pmt.reference_number_sortable
, pmt.transaction_date
, pmt.amount
, pmt.gl_account_KEY
, pmt.undeposited_payment__gl_account_KEY
, pmt.ar_deposit__bank_transaction_KEY
, isnull(arpav.used_payment_amount, 0) as used_payment_amount
, cast(pmt.amount - isnull(arpav.used_payment_amount, 0) as decimal(17,2)) as unused_payment_amount
, cast(case when pmt.amount = isnull(arpav.used_payment_amount, 0) AND pmt.amount <> 0
then 1
else 0 end as bit) as is_exhausted
from
(
-- Gather all AR payment types
select
arp.gl_transaction_KEY
, arp.ar_payment_type_KEY as gl_transaction_type_KEY
, arp.transaction_status_KEY
, arp.customer_KEY
, arp.customer_name
, arp.reference_number
, arp.reference_number_sortable
, arp.transaction_date
, arp.amount
, arp.unearned_revenue__gl_account_KEY as gl_account_KEY
, arp.undeposited_payment__gl_account_KEY
, arp.ar_deposit__bank_transaction_KEY
from
dbo.AR_Payment as arp
UNION ALL
-- Gather AR credit memos
select
artv.gl_transaction_KEY
, artv.ar_transaction_type_KEY as gl_transaction_type_KEY
, artv.transaction_status_KEY
, artv.customer_KEY
, cst.customer_name
, artv.reference_number
, artv.reference_number_sortable
, artv.transaction_date
, artv.amount
, artv.gl_account_KEY
, cast(null as int) as undeposited_payment__gl_account_KEY
, cast(null as int) as ar_deposit__bank_transaction_KEY
from
dbo.AR_Transaction_View as artv
inner join dbo.Customer as cst
on artv.customer_KEY = cst.customer_KEY
where
artv.ar_transaction_type_KEY = 502 -- Credit Memo
) as pmt
left join
(
select
payment__gl_transaction_KEY
-- Force the calculated amount to be a decimal(17,2) column.
, cast(sum(paid_amount) as decimal(17,2)) as used_payment_amount
from dbo.AR_Payment_Application_View
group by payment__gl_transaction_KEY
) as arpav
on pmt.gl_transaction_KEY = arpav.payment__gl_transaction_KEY
;