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