Had a great time today, and finished the day at the geography-based roundtables for GPUG. I was sitting at the IA, KS, MO table, and we had a full group. A lot of different topics of discussion- the upcoming regional chapter meeting on April 17th, everyone's background and experience in GP, the future of manufacturing and project accounting in GP, and reporting dilemmas. One user at the table asked for ideas on forecasting cash flow....so the conversation shifted to the Cash Flow module and then on to forecasting cash flow for AR based on average days to pay. We were wondering aloud about views or reports that might be available for that, and I thought a view would not be hard at all. So here it is, a view to determine the estimated pay date (EST_PAYDATE) based on average days to pay.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PredictPayDate]
AS
SELECT dbo.RM00103.CUSTNMBR, dbo.RM00103.AVDTPLIF, dbo.RM20101.DOCNUMBR, dbo.RM20101.RMDTYPAL, dbo.RM20101.DOCDATE,
dbo.RM20101.DOCDATE + dbo.RM00103.AVDTPLIF AS EST_PAYDATE, dbo.RM20101.DUEDATE, dbo.RM20101.CURTRXAM, dbo.RM20101.VOIDSTTS
FROM dbo.RM00103 INNER JOIN
dbo.RM20101 ON dbo.RM00103.CUSTNMBR = dbo.RM20101.CUSTNMBR
WHERE (dbo.RM20101.VOIDSTTS = 0) AND (dbo.RM20101.CURTRXAM > 0) AND (dbo.RM20101.RMDTYPAL < 7)
GO
Obviously can be expanded to include more information, or used in a SQL report to summarize predicted cash income by month.
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
3 comments:
I noticed ADP for the current year isn't updated until invoices have been closed, leaving it zero rolling into the new year. I've added a CASE statement to look at each ADP field, in order of most recent, to resolve this issue:
SELECT dbo.RM00103.CUSTNMBR,
CASE WHEN dbo.RM00103.AVGDTPYR > 0 THEN dbo.RM00103.AVGDTPYR ELSE CASE WHEN dbo.RM00103.AVDTPLYR > 0 THEN dbo.RM00103.AVDTPLYR ELSE CASE WHEN dbo.RM00103.AVDTPLIF > 0 THEN dbo.RM00103.AVDTPLIF ELSE 0 END END END AS DaysToPay,
dbo.RM00103.AVGDTPYR, dbo.RM00103.AVDTPLYR, dbo.RM00103.AVDTPLIF, dbo.RM20101.DOCNUMBR, dbo.RM20101.RMDTYPAL, dbo.RM20101.DOCDATE,
dbo.RM20101.DOCDATE + (CASE WHEN dbo.RM00103.AVGDTPYR > 0 THEN dbo.RM00103.AVGDTPYR ELSE CASE WHEN dbo.RM00103.AVDTPLYR > 0 THEN dbo.RM00103.AVDTPLYR ELSE CASE WHEN dbo.RM00103.AVDTPLIF > 0 THEN dbo.RM00103.AVDTPLIF ELSE 0 END END END) AS EST_PAYDATE, dbo.RM20101.DUEDATE, dbo.RM20101.CURTRXAM, dbo.RM20101.VOIDSTTS
FROM dbo.RM00103 INNER JOIN
dbo.RM20101 ON dbo.RM00103.CUSTNMBR = dbo.RM20101.CUSTNMBR
WHERE (dbo.RM20101.VOIDSTTS = 0) AND (dbo.RM20101.CURTRXAM > 0) AND (dbo.RM20101.RMDTYPAL < 7)
Nice! Thanks for sharing :)
Post a Comment