View: dbo.Payable_Payment_Application_View | |||
View definition | |||
-- The Payable Payment Application view extends the Payable Payment Application table to include two -- calculated fields. Paid Amount is a sum of all the distributed paid amounts for the Payable Payment -- Application Distributions and discount taken amount is a sum of all the discount taken amounts for the -- same distributions. -- Note this view is indexed. CREATE VIEW dbo.Payable_Payment_Application_View WITH SCHEMABINDING AS ( SELECT ppa.gl_transaction_KEY , ppa.payable_transaction_KEY , ppa.payment__gl_transaction_KEY , ppa.application_date , ppa.payment__reference_number , ppa.description , ppa.origin__accounting_application_KEY , ppa.data_origin_KEY , SUM(ppapd.distributed__paid_amount) AS paid_amount , ppa.discount_taken_amount -- rollup_count is required because this view involves a GROUP BY. Sql Server -- uses the information to optimize maintenance of the index. , COUNT_BIG(*) AS rollup_count FROM dbo.Payable_Payment_Application AS ppa -- Grab the payment application's payable distribution amounts. INNER JOIN dbo.Payable_Payment_Application_Payable_Distribution AS ppapd ON ppa.gl_transaction_KEY = ppapd.gl_transaction_KEY GROUP BY ppa.gl_transaction_KEY , ppa.payable_transaction_KEY , ppa.payment__gl_transaction_KEY , ppa.application_date , ppa.payment__reference_number , ppa.description , ppa.origin__accounting_application_KEY , ppa.data_origin_KEY , ppa.discount_taken_amount ) | |||