The vdvCustomerPayment view has been around for many versions but in Sage 500 v7.6 they made a change to this View. The change was to add Misc Cash Receipts. This is a nice addition but not if you have to wait 15 minutes for the View to return results.
After doing some major analysis on this View what we have uncovered is:
- This was covered up by the fact that if you do not have large datasets it does not impact performance.
- The SELECT DISTINCT was masking some of the poor JOIN conditions.
- A JOIN requiring a more specific condition
So here is a revised version of the vdvCustomerPayment.
/*
Revised vdvCustomerPayment for Sage 500 v7.6
You may want to save a copy of your current View before deleting it and creating this new one
*/
-- Make sure you select proper database
USE [mas500_app]
GO
-- Check to see if the View exists and Drop the View
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vdvCustomerPayment]'))
DROP VIEW [dbo].[vdvCustomerPayment]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Create new View
CREATE VIEW vdvCustomerPayment
as
SELECT DISTINCT
CASE WHEN BL.BatchKey IS NOT NULL THEN BTYP.BatchTypeID ELSE BTYP2.BatchTypeID END BatchType,
CASE WHEN BL.BatchKey IS NOT NULL THEN BL.PostUserID ELSE BL2.PostUserID END PostUserID,
dbo.tarCustPmtLog.CompanyID,
dbo.tarCustPmt.CreateDate,
dbo.tarCustPmt.CreateType,
dbo.tarCustPmt.CreateUserID,
dbo.tarCustPmt.CurrExchRate,
dbo.tmcCurrExchSchd.CurrExchSchdID,
dbo.tarCustPmt.CurrID,
dbo.tarCustClass.CustClassID,
dbo.tarCustClass.CustClassName,
dbo.tarCustomer.CustID,
dbo.tarCustomer.CustName,
dbo.tarCustPmt.DiscTakenAmt,
CAST(dbo.tarCustPmt.GuarntdExchRate AS bit) AS GuarntdExchRate,
dbo.tarCustPmt.PmtRcptDate,
dbo.tarCustPmt.PostDate,
dbo.tcmCashRcptDetl.Amount AS TranAmt,
dbo.tarCustPmt.TranAmtHC,
CASE WHEN dbo.tarCustPmt.CustKey IS NULL
THEN dbo.tglTransaction.PostCmnt
ELSE dbo.tarCustPmt.TranCmnt
END AS TranCmnt,
dbo.tarCustPmtLog.TranDate,
dbo.tarCustPmt.UnappliedAmt,
dbo.tarCustPmt.UnappliedAmtHC,
dbo.tarCustPmt.UpdateDate,
dbo.tarCustPmt.UpdateUserID,
dbo.tarCustPmt.CustPmtKey,
CASE WHEN BL.BatchKey IS NOT NULL THEN BL.BatchKey ELSE BL2.BatchKey END BatchKey,
dbo.tarCustClass.CustClassKey,
dbo.tarCustomer.CustKey,
CASE WHEN BL.BatchKey IS NOT NULL THEN BL.BatchID ELSE BL2.BatchID END BatchID,
dbo.tarCustPmtLog.TranID,
dbo.tciTranTypeCompany.TranTypeID AS TranType,
dbo.tsmLocalString.LocalText AS TranTypeDesc,
dbo.vListValidationString.LocalText AS CreateTypeAsText,
dbo.tcmCashTran.TranID AS Deposit,
dbo.tcmCashRcptDetl.CrCardAuthNo,
dbo.tcmCashRcptDetl.CrCardExpiration,
dbo.tcmTenderType.TenderTypeKey,
dbo.tcmTenderType.TenderTypeID,
CASE WHEN tcmCashRcptSummary.CashAcctKey IS NULL
THEN CA1.CashAcctKey
ELSE CA2.CashAcctKey
END AS CashAcctKey,
dbo.tarCustPmtLog.TranNo,
CASE WHEN tcmCashRcptSummary.CashAcctKey IS NULL
THEN CA1.CashAcctID
ELSE CA2.CashAcctID
END AS CashAcctID,
dbo.tglAccount.GLAcctNo,
dbo.tglAccount.Description AS GLAcctDesc,
dbo.tglAcctRef.AcctRefCode
FROM dbo.tarCustPmtLog
LEFT OUTER JOIN dbo.tarCustPmt -- misc cash receipts don't have tarCustPmt record
ON dbo.tarCustPmtLog.CustPmtKey = dbo.tarCustPmt.CustPmtKey
LEFT OUTER JOIN dbo.tcmCashRcptDetl -- write offs dn't have tcmCashRcptDetl record
ON dbo.tcmCashRcptDetl.CustPmtKey = dbo.tarCustPmtLog.CustPmtKey
LEFT OUTER JOIN dbo.tcmCashRcptSummary
ON dbo.tcmCashRcptDetl.SourceBatchKey = dbo.tcmCashRcptSummary.SourceBatchKey
--> RKL BEGIN Additional more specific condition
and dbo.tcmCashRcptDetl.CashTranKey = dbo.tcmCashRcptSummary.CashTranKey
--< RKL END
LEFT OUTER JOIN dbo.tcmTenderType
ON dbo.tcmCashRcptDetl.TenderTypeKey = dbo.tcmTenderType.TenderTypeKey
LEFT OUTER JOIN dbo.tarPendCustPmt
ON dbo.tcmCashRcptDetl.CustPmtKey = tarPendCustPmt.CustPmtKey
LEFT OUTER JOIN dbo.tciBatchLog BL
ON dbo.tarCustPmt.BatchKey = BL.BatchKey -- payments, write offs have tarCustPmt record, misc payment does not
LEFT OUTER JOIN dbo.tciBatchLog BL2
ON dbo.tcmCashRcptDetl.SourceBatchKey = BL2.BatchKey -- payments, misc cash have tcmCashRcptDetl, write off does not
LEFT OUTER JOIN dbo.tciBatchTypCompany BTYP -- batch type for all but misc
ON BL.BatchType = BTYP.BatchType
--> RKL BEGIN Additional more specific condition
and bl.SourceCompanyID = BTYP.CompanyID
--< RKL END
LEFT OUTER JOIN dbo.tciBatchTypCompany BTYP2 -- batch type for misc
ON BL2.BatchType = BTYP2.BatchType
--> RKL BEGIN Additional more specific condition
and bl2.SourceCompanyID = BTYP2.CompanyID
--< RKL END
LEFT OUTER JOIN dbo.tcmCashTran
ON dbo.tcmCashRcptDetl.CashTranKey = dbo.tcmCashTran.CashTranKey
LEFT OUTER JOIN dbo.tcmCashAcct CA1
ON dbo.tcmCashTran.CashAcctKey = CA1.CashAcctKey
LEFT OUTER JOIN dbo.tcmCashAcct CA2
ON dbo.tcmCashRcptSummary.CashAcctKey = CA2.CashAcctKey
LEFT OUTER JOIN tcmCashAcct
ON dbo.tcmCashTran.CashAcctKey = dbo.tcmCashAcct.CashAcctKey
LEFT OUTER JOIN dbo.tarCustomer
ON dbo.tarCustPmt.CustKey = dbo.tarCustomer.CustKey
LEFT OUTER JOIN dbo.tarCustClass
ON dbo.tarCustPmt.CustClassKey = dbo.tarCustClass.CustClassKey
LEFT OUTER JOIN dbo.tmcCurrExchSchd
ON dbo.tarCustPmt.CurrExchSchdKey = dbo.tmcCurrExchSchd.CurrExchSchdKey
LEFT OUTER JOIN dbo.tciTranTypeCompany
ON dbo.tarCustPmtLog.TranType = dbo.tciTranTypeCompany.TranType
--> RKL BEGIN Additional more specific condition
and tarCustPmtLog.CompanyID = dbo.tciTranTypeCompany.CompanyID
--< RKL END
LEFT OUTER JOIN dbo.tciTranType
ON dbo.tciTranTypeCompany.TranType = dbo.tciTranType.TranType
LEFT OUTER JOIN dbo.tsmLocalString
ON dbo.tciTranType.TranDescStrNo = dbo.tsmLocalString.StringNo
LEFT OUTER JOIN dbo.vListValidationString
ON dbo.tarCustPmt.CreateType = dbo.vListValidationString.DBValue
AND dbo.vListValidationString.TableName = 'tarCustPmt'
AND dbo.vListValidationString.ColumnName = 'CreateType'
LEFT OUTER JOIN dbo.tglAccount
ON dbo.tcmCashRcptDetl.OffsetAcctKey = dbo.tglAccount.GlAcctKey
LEFT OUTER JOIN dbo.tglTransaction
ON dbo.tcmCashRcptDetl.CustPmtKey = dbo.tglTransaction.TranKey
AND dbo.tarCustPmtLog.TranType = dbo.tglTransaction.TranType
AND dbo.tcmCashRcptDetl.OffsetAcctKey = dbo.tglTransaction.GLAcctKey
LEFT OUTER JOIN dbo.tglAcctRef
ON dbo.tcmCashRcptDetl.AcctRefKey = dbo.tglAcctRef.AcctRefKey
WHERE dbo.tarCustPmtLog.TranStatus = 3 --Posted
GO
-- Assign rights to the View
GRANT DELETE ON [dbo].[vdvCustomerPayment] TO [ApplicationDBRole]
GO
GRANT INSERT ON [dbo].[vdvCustomerPayment] TO [ApplicationDBRole]
GO
GRANT SELECT ON [dbo].[vdvCustomerPayment] TO [ApplicationDBRole]
GO
GRANT UPDATE ON [dbo].[vdvCustomerPayment] TO [ApplicationDBRole]
GO
Start write the next paragraph here
I hope you found this helpful. The result of this for the client who reported the issue to us, remember taking sometimes over 10 minutes or more to return the results, it is now seconds!
