View: dbo.AR_Payment_Application_View
View definition
-- This view surfaces all rows and columns from the AR payment application table. In addition, it provide several
-- calculated amount columns summed from the payment application�s distributions. This view is the read source for the
-- payment application entity.
-- drop view dbo.AR_Payment_Application_View
CREATE VIEW dbo.AR_Payment_Application_View
AS
select
pa.gl_transaction_KEY
, pa.receivable__gl_transaction_KEY
, pa.payment__gl_transaction_KEY
, pa.application_date
, pa.was_applied_at_payment_creation
-- This column surfaces the total amount of payment being applied by the payment application. Force the calculated
-- amount to a decimal(17,2) column.
, cast(sum(case when tigla.ar_transaction_item_gl_account_type_KEY not in (2, 3) -- Cost of Goods Sold, Inventory
then pad.distributed__paid_amount else 0 end) as decimal(17,2)) as paid_amount
-- This column surfaces the total amount of discount being honored by the payment application. Force the calculated
-- amount to a decimal(17,2) column.
, cast(sum(case when tigla.ar_transaction_item_gl_account_type_KEY not in (2, 3) -- Cost of Goods Sold, Inventory
then pad.distributed__discount_honored_amount else 0 end) as decimal(17,2)) as discount_honored_amount
-- This column determines the total amount of cost being recognized with this payment application. Recognition of an
-- item's cost is distributed in proportion to the amount of the item's price being paid by the payment application.
-- For example, given an item that cost $60 to make and sold for a price of $200, if $150 is paid by a payment
-- application, then $45 (75%) of the item's cost will also be recognized with the payment application. Force the
-- calculated amount to a decimal(17,2) column.
, cast(sum(case tigla.ar_transaction_item_gl_account_type_KEY when 2 -- Cost of Goods Sold
then pad.distributed__paid_amount else 0 end) as decimal(17,2)) as recognized_cost_amount
, cast( ISNULL(LEFT(
(coalesce(arp.reference_number, cm_art.reference_number, art.reference_number)
+ CASE WHEN coalesce(arp.reference_number, cm_art.reference_number, art.reference_number) = N'' then N'' else N'-' end
+ N'PMTAPP') collate Latin1_General_CI_AS
, 16), N'') as nvarchar(16) ) AS reference_number,
dbo.sf_Generate_Sortable_Reference_Number(
ISNULL(LEFT(
(coalesce(arp.reference_number, cm_art.reference_number, art.reference_number)
+ CASE WHEN coalesce(arp.reference_number, cm_art.reference_number, art.reference_number) = N'' then N'' else N'-' end
+ N'PMTAPP')
, 16), N'')) collate Latin1_General_CI_AS AS reference_number_sortable,
cast( ISNULL(
(coalesce(arp.customer_name, cust.customer_name)
+ CASE WHEN coalesce(arp.customer_name, cust.customer_name) = N'' then N'' else N' - ' end
+ N'Payment Application') collate Latin1_General_CI_AS
, N'') as nvarchar(120) ) AS description
from
dbo.AR_Payment_Application as pa
inner join dbo.AR_Payment_Application_Distribution as pad
on pa.gl_transaction_KEY = pad.payment_application__gl_transaction_KEY
inner join dbo.AR_Transaction_Item_GL_Account as tigla
on pad.ar_transaction_item_gl_account_KEY = tigla.ar_transaction_item_gl_account_KEY
inner join dbo.AR_Transaction as art
on art.gl_transaction_KEY = pa.receivable__gl_transaction_KEY
inner join dbo.Customer as cust
on cust.customer_KEY = art.customer_KEY
left join dbo.AR_Payment as arp
on arp.gl_transaction_KEY = pa.payment__gl_transaction_KEY
left join dbo.AR_Transaction as cm_art
on cm_art.gl_transaction_KEY = pa.payment__gl_transaction_KEY
group by
pa.gl_transaction_KEY
, pa.receivable__gl_transaction_KEY
, pa.payment__gl_transaction_KEY
, pa.application_date
, pa.was_applied_at_payment_creation
, arp.reference_number
, arp.customer_name
, art.reference_number
, cust.customer_name
, cm_art.reference_number
;