SQL – Retrieving the most recent receipt info for an item

November 27, 2009

It’s been quite a while since I have posted a SQL script, and it’s funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question on the Microsoft Dynamics GP public newsgroup, as follows:

“We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
unsuccessful.”

At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as simple. Luckily enough, we can take advatage of the latest T-SQL enhancements to use ranks and partitions on sets to deliver the requested query, as follows:


-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
, B.ITEMDESC
, B.CURRCOST
, ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
, ISNULL(R.UNITCOST, 0.00) AS UNITCOST
, ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
LEFT OUTER JOIN (
SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
(PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
FROM POP30310 C
LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
) Receipts WHERE RECEIPT_RANK = 1
) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

In Microsoft Dynamics GP v10, the POP Receipt history tables also hold In-Transit Transfer transactions. If you are looking to retrieve strictly vendor receipts, you may change the query as follows:


-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
, B.ITEMDESC
, B.CURRCOST
, ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
, ISNULL(R.UNITCOST, 0.00) AS UNITCOST
, ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
LEFT OUTER JOIN (
SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
(PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
FROM POP30310 C
LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
WHERE D.POPTYPE 8
) Receipts WHERE RECEIPT_RANK = 1
) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

Hope you find this query useful.

Until next post,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Happy Thanksgiving!

November 26, 2009

Folks, is Thanksgiving Day here in the United States and it is always an occasion to share with friends and loved ones. In particular, I would like to thank each and everyone of you for your readership and support shown over the last two years or so that my blog has been in circulation. The Dynamics GP Blogster is a success because of you! You certainly motivate me to continue writing and delivering the content you have come to rely on to get your job done or to find that unique aspect about the Microsoft Dynamics GP product that otherwise won’t be available elsewhere.

This year I have heard comments like “I no longer go to CustomerSource/PartnerSource, I search your blog first!” and I really appreacite them from the bottom of my heart. In fact, keep your comments comming! For now, wherever you are in the world, Happy Thanksgiving!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


The Technology Corner: Houston Neil weighs in on Microsoft Dynamics products

November 24, 2009

Fellow blogger Houston Neil addresses the proverbial question of what’s the difference between the Microsoft Dynamics products. The truth is, to understand the functional and technological differences, you have to go back a few years in history to look at the Microsoft Dynamics products acquisition process, the failed Microsoft Project Green, and perhaps, even look at Oracle’s attempt over the last 7 years to consolidate their products after their acquisition of PeopleSoft to understand that the ERP world is a very complex one with the customer base driving product decisions across the board.

I am a big Microsoft Dynamics GP proponent and defender, and in fact being an MVP allows me to carry out a lot of the product evangelism to customers and partners. Despite the product directions and strategies I have been witnessing over the past couple of years, I will be the first to say that there will be GP for years and years to come (see my article on Microsoft Dynamics GP Roadmap Update). Am I confident that 10 years from now I will be telling you the same story? I would hope so, but don’t bank on that one! After all, the market evolves and set the tone, and ultimately customers in any industry determine what stays and what goes… however, GP chances are looking pretty good from where I stand.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


IM – Integration Manager skipping records despite proper query relationships

November 22, 2009

Just when you thought there wasn’t anything more to learn about Integration Manager something else comes along to demistify that theory. I have been involved in a JD Edwards on DB2 and AS/400 systems migration for almost 4 months now and last month I blogged about how Integration Manager can be a powerful tool in multiplatform systems integrations.

Part of disengaging JD Edwards is to write a series of integrations to existing systems running on the AS/400 platform. In the process, the client needed a simple to use tool which required limited programming and maintenance, hence the choice of IM. After unit testing the integrations, everything was A-Ok to begin with the week long systems testing, which would exhause the integrations while allowing the customer to get the overall “feel” for GP and how it would address the existing business processes.

One particular business process — Expense Reimbursements — required an integration to Payables Management. How difficult could this be, right? I have done over 100 PM integrations in the past so this could not be any different. During the system testing, the end-user reported incomplete distributions throughout the integrations of hundreds of expense reports into GP and provided the following log file:


Integration Log
Integration: SC-ExpenseReimbursement (ID: 116)
Action: None
Start Time: 11/20/2009 12:09:14 PM

11/20/2009 12:09:15 PM Source: IIntegrationEngine_Run, Status Code: 0 Opening source query...
11/20/2009 12:09:16 PM Source: IIntegrationEngine_Run, Status Code: 0 Establishing source record count...
11/20/2009 12:09:28 PM Source: IIntegrationEngine_Run, Status Code: 0 Beginning integration...
1: 2 Insert Succeeded 5.31 Seconds
2: 3 Insert Succeeded 1.53 Seconds
3: 4 Insert Succeeded 1.86 Seconds
4: 5 Insert Succeeded 2.25 Seconds
5: 6 Insert Succeeded 3.56 Seconds
6: 7 Insert Succeeded 1.47 Seconds
7: 8 Insert Succeeded 1.44 Seconds
8: 9 Insert Succeeded 1.47 Seconds
9: 10 Insert Succeeded 1.34 Seconds
10: 11 Insert Succeeded 1.91 Seconds
11: 12 Insert Succeeded 1.53 Seconds
12: 13 Insert Succeeded 1.64 Seconds
13: 14 Insert Succeeded 2.63 Seconds
14: 15 Insert Succeeded 2.94 Seconds
15: 16 Insert Warning 3.22 Seconds
DOC 15 WARNING: Distributions for this transaction contain errors.
DOC 15 WARNING: This transaction will not post; it includes distributions with errors.
16: 17 Insert Warning 0.95 Seconds
DOC 16 WARNING: Distributions for this transaction contain errors.
DOC 16 WARNING: This transaction will not post; it includes distributions with errors.
17: 18 Insert Warning 1.17 Seconds
DOC 17 WARNING: Distributions for this transaction contain errors.
DOC 17 WARNING: This transaction will not post; it includes distributions with errors.
18: 19 Insert Warning 1. Seconds
DOC 18 WARNING: Distributions for this transaction contain errors.
DOC 18 WARNING: This transaction will not post; it includes distributions with errors.
19: 20 Insert Warning 0.98 Seconds
DOC 19 WARNING: Distributions for this transaction contain errors.
DOC 19 WARNING: This transaction will not post; it includes distributions with errors.
20: 21 Insert Warning 1.83 Seconds
DOC 20 WARNING: Distributions for this transaction contain errors.
DOC 20 WARNING: This transaction will not post; it includes distributions with errors.
21: 22 Insert Warning 0.95 Seconds
DOC 21 WARNING: Distributions for this transaction contain errors.
DOC 21 WARNING: This transaction will not post; it includes distributions with errors.
22: 23 Insert Succeeded 2.66 Seconds
11/20/2009 12:10:50 PM Source: IMProv.IntegrationContext.IIntegrationEngine_Run, Status Code: -2147221495 ERROR: Integration canceled during document integration.
11/20/2009 12:10:50 PM Source: FinishIntegration, Status Code: 3 Integration Failed
11/20/2009 12:10:50 PM Source: FinishIntegration, Status Code: 3 Integration Results
307 documents were read from the source query.
22 documents were attempted:
15 integrated without warnings.
7 integrated with warnings.
0 failed to integrate.

Finish Time: 11/20/2009 12:10:50 PM

Source Total: 22
Successfully Integrated: 22
Integrated With Warning: 7
Failed: 0

At first glance this would seem typical of transactions that are actually missing distributions, but I checked against the source staging table in DB2 and all distribution records for each transaction reported as missing were actually present. The only distributions in the source were those for the expenses. In addition, this integration had been setup to default any missing distributions in the source recordset, which would automatically create the payables side.

I then checked each vendor (employee) record in GP for a missing payables account. After all, this would explain the case of the missing distributions. All vendor records were properly setup. Now comes the actual troubleshooting process after checking the obvious. My source queries were very simple too:

ExpenseHeader


SELECT DISTINCT VENDORID, INVOICENUM, AMTDUEEMPLOYEE, LEDGERDATE, INVOICEDATE
FROM GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0 AND COMPANY = '10'

ExpenseDetail


SELECT VENDORID, INVOICENUM, GPACCOUNT, AMOUNT AS DEBIT, 0 AS CREDIT, 6 AS DISTTYPE
FROM SDDTA.GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0

I proceeded to limit the source queries to just a specific employee and expense document number reported in the log as failing and re-ran the integration. Voila! The record went through to GP without any issues. Now the question was, why is IM kicking back valid records for the integration without any apparent reason?

I also checked to make sure the UseOptimizedFiltering switch was set to False and that we were running IM service pack 4 — more on IM switches here. After a while of looking at the source records in the staging table, I realized that the first records that IM imported were in the same physical order in the table were data was being retrieved from. This was very simple as DB2 tables are VSAM tables, making them very different from SQL Server tables in the way the store data physically. However, for the records that failed, IM was attempting to locate a distribution record in the same order specified by the records being read from the header query.

To correct the problem, I decided to enforce the order of the records in both source queries by adding an ORDER BY clause to the queries, by VendorID and InvoiceNum. This way, as IM advanced through the header source query reading the records, it would consecutively find these in the distributions source query tables as specified by the query relationships (1..M). Now my source queries looked like this:

ExpenseHeader


SELECT DISTINCT VENDORID, INVOICENUM, AMTDUEEMPLOYEE, LEDGERDATE, INVOICEDATE
FROM GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0 AND COMPANY = '10'
ORDER BY VENDORID, INVOICENUM

ExpenseDetail


SELECT VENDORID, INVOICENUM, GPACCOUNT, AMOUNT AS DEBIT, 0 AS CREDIT, 6 AS DISTTYPE
FROM SDDTA.GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0
ORDER BY VENDORID, INVOICENUM

Upon executing the integration once more, all expense transactions were properly integrated without any missing distributions.

Now, I cannot say I experienced this before with SQL Server sources, but this happens to be a DB2 source and just maybe the ODBC engine works differently, but I can say that by reordering the source queries, I was able to resolve these missing distributions issues and improve performance two-fold.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/


Dyn – Why is my inventory-related transaction posting so slow?

November 19, 2009

It is not uncommon for Microsoft Dynamics GP v9 and v10 users to report system sluggishness when posting inventory related transactions in Sales Order Processing, Purchase Order Processing or Inventory, especially if using the Average Perpetual valuation method.

The main reason why you may experience slow posting of inventory-related transactions is because Microsoft Dynamics GP uses an “inventory ripple” effect approach to recalculate the average cost of each inventory receipt layer due to inflow and outflow transactions in SOP, IV or POP. This is normal because in GP9.0 and GP10.0, the system is already observing the so-called “moving average” calculation. In addition, the system now “ripples” all item transactions. Hence, if transactions are backdated, each transaction would have to “ripple” through approximately n lines in the Inventory Purchase Receipts Work table (dbo.IV10200).

This new costing functionality in GP9 and GP10 also includes a new table, the Inventory Purchase Receipts Detail table (dbo.IV10201). To illustrate, if one of those lines in the Inventory Purchase Receipts Work table (dbo.IV10200) has been “sold”, there should be a record of that sale in the IV10201 table. So now, we also have to “ripple” through the Inventory Purchase Receipts Detail table (dbo.IV10201) and each “layer” in that table, from the location of the “sale” record to the present. If you are a fairly high-volume transaction environment, this can take for ever!

A single transaction with a single line item, could very likely be updating quantity on hand and adjusted unit cost for over nth lines in the Inventory Purchase Receipts Work (dbo.IV10200) and an equal number in the Inventory Purchase Receipts Detail table (dbo.IV10201). This Microsoft Dynamics GP “inventory ripple” process may take considerable time to go through each receipt layer.

Average Perpetual and back-dated transactions seem to exacerbate the slowdown. But other know issues are also contributors, such as sold receipt layers not being removed during the Inventory Year-End Close (YEC) process.

These two issues could be the main causes why Microsoft Dynamics GP is slow in posting due to the “ripple effect” in inventory that they are producing.

Related Articles

To further explain the enhancements made to average cost calculation in GP 9.0 and above, please refer to KB 923960 – “Enhancements made to the calculation of average cost in Microsoft Dynamics GP”.

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;923960

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


Microsoft Dynamics GP Technical Conference 2009 – Time to say goodbye

November 15, 2009

The Conference ended with David and I dragging feet, with little to no sleep, but with the satisfaction that we delivered to the best of our abilities. I woke up the following morning after spending part of the previous night packing my suitcase, only to come to the realization that I left my pair of Louis Vuitton glasses somewhere along (ouch!).

After having breakfast downstairs, we (David & I) hopped a ride to the Microsoft campus with Mark Rockwell (Rockton Software) and Jim Peliksa (Rockton Software). It was time to go get some goodies for the trip back home. After the campus store opened, we went downstairs and I got a remote controlled Warthog (Halo 3), a cool MS presenter’s mouse, and an ultrasleek MS mouse for my wife. I also managed to pick up an embroided Bing hooddie sweater for my daughter.

We went back to the Oak room. David went off to a team meeting and came back after 1 hour. We then went to Scott Stephenson’s office to get our goodbye picture taken. I have to admit, it was a bit worst than an Internet break up. Dave and I had worked closely for the past 18 months on more than one blog and in the last 3, on the conference materials. We finally got to meet in person, then had to say goodbye.


Ok, I know what you thinking… After THAT hug, I felt we owed it to each other to bring our wives together.

David had a few more meetings and I had to go pack up my laptop and goodies to go meet the shuttle down stairs, not before saying goodbye to long time friends Dave Gaboury (a.k.a., Father Dexterity, now working on the AX team) and Emily Roen. Emily and Dave escorted me to the shuttle and my journey back to Atlanta began.

Upon arrival to Hector International (Fargo’s airport), I had to maneuver a bit with the goodies and pack them in my suitcase somehow.

As for David, I had a chance to speak to him today and he is on his 36-hour journey back to Aussie land. Good luck my friend and talk to you soon.

I have to thank the entire Microsoft Business Solutions organization for vouchering my participation at the Microsoft Dynamics GP Technical Conference 2009. It was one of the most gratifying experiences of my career as a Dynamics GP professional.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


Microsoft Dynamics GP Technical Conference 2009 – Day 2

November 12, 2009

The good results of Day 1 and the hard work we put into preparing for the double whammy Troubleshooting session ahead of us had began to take their toll on Dave, Leslie, and I. We had a good breakfast at the hotel before being shuttled back to the Commons building. Upon arrival, Dave, Leslie and I went back to the Oak room to perform one last pass on the presentation, print out the cheat sheets and try to relax a bit.

We were on at 10:30 AM and the presentation went really well with minor unnoticeable glitches. It felt good, but not good enough. At the end of the first session of our Troubleshooting topic, and despite being extremely tired, Dave, Leslie and I returned to the round table to analyze the things that went wrong in our first session. After a few touch ups here and there, Dave and I went off to the table topics. I joined Nick Hoban who runs the Microsoft Dynamics Community website to discuss some improvements to the site.

We were back on stage at 3:30 PM for our second session. This one went just smooth… no glitches, no issues, just like if we had done it…once before! Our session ended just 15 minutes before the closing session.

The closing session futured Mr. Dave Coulombe and the some of “The Powers to Be” at Microsoft answering tough questions from ISVs Mark Rockwell (Rockton Software) and Bill Marshall (mc2 Software). By their expressions on this picture, you can conclude Mark and Bill were not asking easy questions… 🙂

From left to right, Jeff Trosen, Chad Sogge, Gary Tronson, Scott Stephenson, and Errol Shoenfish

Note worthy of the session was the demo performed by John Hancock of PowerPivot the new cool add-on that will be made available with Microsoft Excel 2010. The demo featured the new in-memory BI engine which allows PowerPivot to process some 100 million records in less than 1 seconds and do all sort of data mining via SharePoint 2010.

The evening went along with the Study Hall session where I linked up with more friends including the good folks of the Tools Support and the Dexterity Development teams. Here are some pics from that evening:

I went to the hotel and crashed for the rest of the evening! I still had to get up early the following morning as I had one more final stop to do: the Microsoft Campus Store!

A special THANK YOU to MVP Leslie Vail for her invaluable contribution, time and patience into making our Troubleshooting session a success. Without her, we could not have got our presentation contents and timing refined and provided the level of criticism required to deliver the quality presentation expected by attendees.

Downloads:

For materials of the Troubleshooting Your Developed Solution session, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/