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
)