Dynamics GP 2010 Incorrect Item Balance in Item Stock Enquiry

(1) Share

Report

Posted on by Community Member Microsoft Employee

For a number of items, the Item Stock Enquiry window shows an incorrect item balance. At least one of the methods that will recreate the scenario is listed below. In this case the problem is that adding a serial number through the Sales Serial Number Entry window increases the quantity on hand. It acts as a stock increasing transaction. Has anyone else come across a similar issue and a fix or a workaround for it?

Create an item with Tracking set to ‘None’
Create a Purchase Order for that item, quantity of 1
System allows you to change the tracking option of the item at this stage, but leave it as ‘None’
Do the stock receipt with a shipment
System still allows you to change the tracking option of the item at this stage
Change the Tracking to ‘Serial Numbers’ (I thought the system will prevent this, but it’s not)
Post the batch for the receiving transaction
System no longer allows you to change the tracking option of the item
Enter a Sales Order for the item, quantity of 1
Item Stock Enquiry, quantity on hand is 1
System prompts you for a serial number when you try to allocate quantity within the Sales Order
Enter the serial number on the ‘Sales Serial Number Entry’ window when prompted (Since no serial is recorded in the system up to now)
Item Stock Enquiry, quantity on hand is 2, allocated 1 and available 1
Stock enquiry screen shows a single receipt of quantity 1 but the balance is 2

*This post is locked for comments

All responses (5)

Answers (1)

Mahmoud Saadi 32,738 on at

Like (2)

Report

RE: Dynamics GP 2010 Incorrect Item Balance in Item Stock Enquiry

The problem is that the System is not checking any "unposted" transactions, and that could be generalized to include (POP, SOP, MFG) as part of the supply chain.

So when changing the tracking option, the SQL Profiler shows that only the "Quantity on Hand" field is being checked, and therefore, it allows you to proceed with changing the tracking option smoothly.

You could customize a built-in code to work on certain event (changing the tracking option, opining the option window under item card maintenance ... etc) to pop up a warning message in case there is any unposed transaction in the supply chain modules.

That will definitely prevent such disasters. Until that time, we could populate this issue and see whether this need to be escalated and published on Microsoft Connect.

Hope others could share their opinions.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP 2010 Incorrect Item Balance in Item Stock Enquiry

Very good...

Cheers!

Sanjay
Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP 2010 Incorrect Item Balance in Item Stock Enquiry

Very good...

Cheers!

Sanjay
Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP 2010 Incorrect Item Balance in Item Stock Enquiry

Thank you very much for your time and effort. You have made it crystal clear.

I wonder whether it is possible to use VBA or similar to overcome the limitation of system only checking for a balance inside IV00102 and not looking at un-posted receipts etc.
Verified answer

Mahmoud Saadi 32,738 on at

Like (2)

Report
RE: Dynamics GP 2010 Incorrect Item Balance in Item Stock Enquiry

This is such an interesting inventory case, that I have definitely had good time working on. And here is my justification for the system behavior, I will be explaining throroughly every single step in order to understanad How, and Why the case has occured the way you described it. First of all, the following tables will mentioned quite often within the post which are;

IV30300 | Inventory Transaction Amounts History

IV10200 | Purchase Receipt Work

IV10201 | Purchase Receipt Detail

SEE30303 | Historical Inventory Trial Balance

IV00200 | Item Serial Number Master

Be patient, the explanation is huge :)

Process Explanation:

It has always been recommended not to change the item "tracking option" unless the item current balance is zero, which is applied by the system (The current balance in IV00102 Item Quantities Master is zero at the time of changing the tracking option, since the receiving has not been posted yet) . Therefore, it allowed you to change it smoothly with no warning messages.

Now, posting the receiving batch (including the inventory cost layer, with no serial number) will definitely cause problems, because this cost layer doesn't have a corresponding serial record in IV00200 (Item Serial Number Master).

After posting the receiving, you will have the following values within your inventory module;

Item Quantity on Hand: 1

Existing Cost Layer in IV10200 (QTYRECVD = 1), with (RCPTSOLD = 0), which means, the layer is available to be consumed

No Serial Record in IV00200, (Item Serial Number Master)

Item Stock Inquiry shows available quantity (1) resulted from the receiving

Item Quantity Inquiry will show Quantity on Hand 1

Now, entering a sales order/invoice or any other "withdrawing" transaction such as adjustment out for instance, will check the Item Tracking Option (which is serial now) and look for corresponding layer in IV00200 (Item Serial Master). A stored procedure will be called to create a new record with quantity (1). As a result, this value will be added to the IV00102 (item quantity master). At this point, before posting, you will have the following values in your inventory; (problems in red)

Item Quantity on Hand: 2

Existing Cost Layer in IV10200 (QTYRECVD = 1), with (RCPTSOLD = 0)

Serial Record in IV00200, (Item Serial Number Master)

Item Stock Inquiry, the quantity on hand is retrieved from IV00102 (2), will show incorrect balance

Item Quantity Inquiry will show Quantity on Hand 2

Now, when posting the Sales document, what will happen precisely is as follows:

The serial number record will be deleted from IV00200

The sales transaction will be recorded only in (IV30300, and SEE30303).

The sales transaction will not affect most importantly (IV10200 and IV10201) Purchase receipt layer and Details. (which is a disaster !)

Further Remedy Solution:

As always, the first two prerequisites to start with would be Reconcile and Check links.

The quantity on Hand will be re calculated based on IV10200, and corrected accordingly (but it will still be incorrect, because Purchase Receipt Layer is missing the Sales Transaction Values)

Added Serial Number record for the sales transaction

Current Inventory Balances (according to the IV Tables);

Correct | IV30300 (Receiving 1 , SOLD -1) Balance = 0

Incorrect | IV10200 (Receiving 1) Balance = 1

Incorrect | IV10201 (nothing)

Correct | SEE30303 (Receiving 1, SOLD -1) Balance = 0

Corrective Adjustment Out is required with (-1) to affect only IV10200, and IV201,

The Transaction is not to be posted to GL. (GL is correct, as it follows the records within SEE30303)

After posting the transaction, go to Remove transaction History Window and delete the transaction (this will delete the transaction only from IV30300)

Delete it manually on the database level from SEE30303.

That's it !

Recommendation

I absolutely believe that changing the tracking option should meet the prerequisite of having zero balance before any change, and the system will not allow you to do so unless the balance is zero. Therefore, we could simply stick to the way the process was designed to avoid such disaster.

Please never hesitate to share any further inquiries, this definitely has been enjoyable issue to work on.

Hope this helps,