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