View: dbo.Payable_Transaction_Processing_Date_View
View definition
--Create a view that adds a processing date field to the Payable_Transaction table
--for use in the manage impound payments screen.
--This view will contain a record for every record in the Payable_Transaction table.
--The processing_date field will be the Payable_Transaction due_date unless the
--lead_days value from the Payroll_Agent_Payment_Method_Lead_Days table or the
--Tax_Agent_Payment_Method_Lead_Days is greater than zero. The correct table is
--determined based on whether the vendor's current__vendor_type_KEY is a
--payroll agent or a tax agent. If the vendor type is not payroll agent or
--tax agent then the due_date is used.
CREATE VIEW dbo.Payable_Transaction_Processing_Date_View
AS
SELECT
payable_transaction_KEY = PT.payable_transaction_KEY,
processing_date = dbo.sf_CalculateLeadDate(PT.due_date, COALESCE(PAPMLD.lead_days, TAPMLD.lead_days, 0))
FROM
dbo.Payable_Transaction PT
INNER JOIN
dbo.Vendor VT
ON VT.vendor_KEY = PT.vendor_KEY
LEFT JOIN
dbo.Payroll_Agent_Payment_Method_Lead_Days PAPMLD
ON VT.default__vendor_payment_method_KEY = PAPMLD.vendor_payment_method_KEY AND VT.current__vendor_type_KEY = 2
LEFT JOIN
dbo.Tax_Agent_Payment_Method_Lead_Days TAPMLD
ON VT.default__vendor_payment_method_KEY = TAPMLD.vendor_payment_method_KEY AND VT.current__vendor_type_KEY = 3