tomhr
asked on
Split Lots in Dynamics GP
We upgraded Dynamics GP 10 last year from the RTM version to SP5 (774 > 1579). Since then, we have noticed that inventory lots are being split. For example, if we receive 14 items into a single lot, the receipt is recorded at 13 at the proper unit cost and 1 at a slightly different unit cost. They all remain in the same lot, however.
We do not use bins.
Any ideas?
We do not use bins.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I select out of the IV00300 table I see two lines but they have the same unit cost:
I've heard of this rounding issue, but that doesn't look like what is happening here.
ITEMNMBR LOCNCODE DATERECD DTSEQNUM LOTNUMBR QTYRECVD QTYSOLD ATYALLOC UNITCOST RCTSEQNM VNDRNMBR LTNUMSLD QTYTYPE BIN MFGDATE EXPNDATE DEX_ROW_ID
411-35-01-56-00065 SWZ 2012-08-29 00:00:00.000 16385.00000 109164 289.00000 0.00000 0.00000 3.72000 20 0101280 0 1 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 124001
411-35-01-56-00065 SWZ 2012-08-29 00:00:00.000 16386.00000 109164 1.00000 0.00000 0.00000 3.72000 21 0101280 0 1 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 124002I've heard of this rounding issue, but that doesn't look like what is happening here.
ASKER
The larger issue here is that when a user allocated inventory they have to, for example, allocate 499 out of a lot and then the single extra trailing on the same lot.
I haven't seen the issue when roudning wasn't a factor. I just ran a test with qty 10 and cost $10 and it only created one row in IV00300 for the lot. Are you using any 3rd party apps or customizations? Are these items received through the Receivings Transaction Entry window?
ASKER
They entered via the standard Receivings Transaction Entry window; no customizations.
The query I was running to find double rows was:
SELECT LOTNUMBR, UNITCOST FROM IV00300 GROUP BY LOTNUMBR HAVING COUNT(LOTNUMBR) > 1 ORDER BY LOTNUMBR, UNITCOST
Perhaps my query was wrong and I was missing better examples?
The query I was running to find double rows was:
SELECT LOTNUMBR, UNITCOST FROM IV00300 GROUP BY LOTNUMBR HAVING COUNT(LOTNUMBR) > 1 ORDER BY LOTNUMBR, UNITCOST
Perhaps my query was wrong and I was missing better examples?
ASKER
Thanks for the assistance. At least I know it's essentially operating as intended.
You're welcome. Yes, it is operating as designed.
By the way, the query you have above wouldn't work since the UNITCOST isn't included in an aggregate function or in the GROUP BY clause. Something like this would work though...
SELECT LOTNUMBR, UNITCOST FROM IV00300 WHERE LOTNUMBR IN (SELECT LOTNUMBR FROM IV00300 GROUP BY LOTNUMBR HAVING COUNT(LOTNUMBR) > 1) ORDER BY LOTNUMBR, UNITCOST
That'll show you duplicate lot records and the asssociated costs.
By the way, the query you have above wouldn't work since the UNITCOST isn't included in an aggregate function or in the GROUP BY clause. Something like this would work though...
SELECT LOTNUMBR, UNITCOST FROM IV00300 WHERE LOTNUMBR IN (SELECT LOTNUMBR FROM IV00300 GROUP BY LOTNUMBR HAVING COUNT(LOTNUMBR) > 1) ORDER BY LOTNUMBR, UNITCOST
That'll show you duplicate lot records and the asssociated costs.
This is a much bigger problem for me. My batch came in as 5,183 inches at $.02 and 1 inch at $21.24 per inch.
The other batch was similarly slewed with the one being valued 400 times the value of the rest of the items in the lot.
Obviously, this would skew the hell out of a variance report when that item actually used in an MO. So this is not a rounding error, as near as I can determine.
The other batch was similarly slewed with the one being valued 400 times the value of the rest of the items in the lot.
Obviously, this would skew the hell out of a variance report when that item actually used in an MO. So this is not a rounding error, as near as I can determine.
Adaflash,
It is the same issue. You're starting with 5184 inches for $124.90. If you divide 124.90 by 5184 you get 0.0240933641975309. GP rounds this to 0.02 and uses it for all but one inch. The remaining inch gets the remaining amount $124.90 - (5183 * 0.02) = $21.24.
You could increase the Currency Decimals to get a better result. If you increased Currency Decimals to 5, you'd get 5183 inches at $0.02409 and 1 inch at $0.04153. Which should be much more manageable.
Jim
It is the same issue. You're starting with 5184 inches for $124.90. If you divide 124.90 by 5184 you get 0.0240933641975309. GP rounds this to 0.02 and uses it for all but one inch. The remaining inch gets the remaining amount $124.90 - (5183 * 0.02) = $21.24.
You could increase the Currency Decimals to get a better result. If you increased Currency Decimals to 5, you'd get 5183 inches at $0.02409 and 1 inch at $0.04153. Which should be much more manageable.
Jim
I had a client that needed it to work that way so I wrote a customization a that triggered off the lot creation, combined the lots back together and created a GL journal for the roudning amount. That was back for version 8 or 9, I haven't looked at it in a while. I'm pretty sure GP 2010 works the same. I don't know if it'll be changed in 2013.