15 August 2008

God (and His servants, the writers of the Microsoft Dynamics GP accounting/MRP system) has been particularly gracious to me today.

El Presidente here and the accounting department have been struggling with our MRP system's Sales Forecasting module, because it would import the previous year's sales history properly, but there was no way to copy that data into the next year's forecast as a default value. It was then put to me to figure out a way to do so. I found the following tables to be relevant to the MRP Sales Forecasting module:

dbo.SC020030
dbo.SC020130
dbo.SC020230
dbo.SC020330
dbo.SC020430
dbo.SC020530
dbo.SC020630
dbo.SC020730

That's a good start, and I noted that ...230 contained the forecast data, while ...630 contained line item sales history, albeit not summarized per each configured date period in the forecast. I was getting ready to wade into an enormously complex SQL script drawing from several of these tables that would compute the sales history for each period and write it over to the ...230 table, but then I looked at the ...230 table again and saw that it had a field QTYTORDR. Oh sweet heavens, do my eyes deceive me...no, it was indeed the sales history quantity for each period, summed up and ready to go!

One quick SQL statement later:
UPDATE SC020230
SET QTYTOFORECOST_I = QTYTORDR
WHERE PLANNAME_I = '[plan name to be updated]'
...and problem solved. I celebrated with my third cup of coffee.

3 comments:

A Pilgrim's Porridge said...

unlike art history, i've got nothing...none the less, I am very proud of you.

derek van neufeld said...

ahhh... brilliant!

The Angry Coder said...

Though the solution was simple, the analysis that led to the simple solution is what was brilliant and, no doubt, took 95% of the time. A lesser admin would've just opened a call with MicroSoft and you'd still be waiting for an answer. That's why you get paid the big bucks ;-)