Calculating the number of fiscal periods between two dates

I developed a Purchasing Invoice integration for a client that has been running well for over a year.  Recently, the client requested some help automatically handling invoice posting dates during month end close.

The client typically kept their period open for 3 extra days while they gathered invoices related the prior period, but any invoices that arrived after those 3 days should go into the current period.  So if a 9/28 invoice arrived on 10/2, it would post to 9/28.  But if a 9/25 invoice arrived on 10/4, it would post to 10/1.

This was a challenge for them with the Purchasing Invoice integration because the vendor invoice data files would often contain invoices with a mix of dates.  A file received on 10/8 could have invoice dates from 9/15 to 10/7.  Sometimes a data file would contain an invoice dated over one month in the past.

And this client uses 4-4-5 fiscal periods, so I couldn't just use calendar months for the periods--I would need to query the GP fiscal period tables to determine the start and end dates for every period.

Once I knew the fiscal period dates, I had to create logic to check the invoice date, compare it to the current period dates, and determine whether the invoice should post to the current period or the prior period.

The first step was to query the GP fiscal period tables.  Whenever I think of "GP" and "query", I think of Victoria Yudin, the Dynamics GP MVP and reporting guru, who has a few billion queries posted on her blog.  Sure enough, a quick Google search of "dynamics gp fiscal period query" sent me right to her blog. (Thank you Victoria!)

http://victoriayudin.com/2010/11/24/sql-queries-for-fiscal-years-and-periods-in-dynamics-gp/

I borrowed Victoria's second query and adjusted it to only return info about the current fiscal period.

SELECT D.PERIODID PeriodNumber, D.PERNAME PeriodName, D.PERIODDT StartingDate, D.PERDENDT EndingDate, D.YEAR1 FiscalYear
FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
   and GETDATE() between H.FSTFSCDY and H.LSTFSCDY
   and GETDATE() between D.PERIODDT and D.PERDENDT
ORDER BY D.PERIODID

Now that I had the current fiscal period start and end dates, I could calculate if the period "cutoff window" (3 days) was still open for the prior period, and whether an invoice should post in the current or prior period.  That worked out well for invoices dated in the current period or the immediate prior period.

But what about an invoice dated 8/1 that arrives on 10/10?  I needed a way to determine how many periods "old" the invoice date is for these older invoices.  Since the client uses fiscal periods and not calendar periods, I can't count the number of months difference between the current date and the invoice date.

I created another version of Victoria's fiscal period query to get the period info for the invoice date.  My .NET code substitutes in the invoice date in the WHERE clause.

SELECT D.PERIODID PeriodNumber, D.PERNAME PeriodName, D.PERIODDT StartingDate, D.PERDENDT EndingDate, D.YEAR1 FiscalYear
FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
   and '07/15/2012' between H.FSTFSCDY and H.LSTFSCDY
   and '07/15/2012' between D.PERIODDT and D.PERDENDT
ORDER BY D.PERIODID

In this example, the 7/15/2012 date would return fiscal period 7.  If the current date is 10/10/2012, then I know that 10 - 7 = 3, so this particular invoice cannot post to the immediate prior period, and must post to the current period (October).

But that 10 - 7 calculation only works for periods in the same year.  What if it is January 2013 and the invoice is dated December 2012?  I can't use 12 - 1 = 11, as that is incorrect.

The trick here is to multiply the fiscal years by 12, then add the period numbers, then find the difference between those two values.  So using the example above:

(Current Year * 12 + Current Period) - (Invoice Year * 12 + Invoice Period)

(2013 * 12 + 1) - (2012 * 12 + 12) = 1

24157 - 24156 = 1

And if an old invoice dated May 2012 arrives in January 2013:

(2013 * 12 + 1) - (2012 * 12 + 5) = 8

24157 - 24149 = 8

And that is how you calculate the number of fiscal periods between two dates.

The last minor challenge I faced was that the eConnect taPopEnterMatchInvHdr object does not have a property for GL posting date--it only allows you to import the invoice date.  This is not uncommon for eConnect transaction headers, but it is annoying.  So, after you import each document, you have to directly update the transaction GL posting date yourself.

In my case, I updated the POP10300 table.

UPDATE POP10300 SET GLPOSTDT = @GLPOSTDT WHERE POPTYPE = 2 AND POPRCTNM = @POPRCTNM AND VENDORID = @VENDORID AND VNDDOCNM = @VNDDOCNM AND receiptdate = @INVOICEDATE


And then I was finally done.


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com