Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Don McCready]

Greetings All,

We’re running GP2018R2 and have an inventory item allocation that is invalid and needs to be removed.  The data appear corrupted and will apparently require fixes in the tables.  I’m hoping someone can advise which tables I should look at.

The record that displays on the Item Allocation Inquiry screen shows a recent date and current customer but the document number and allocation qty are from an order in 2005 that was fulfilled for a different customer.  This record is totally invalid and we’d like to delete it.

Thanks in advance for any assistance!

——————————
Don McCready
Retired Controller and IT Director
The Schaefer Group, Inc.
Beavercreek OH
——————————

[Chase Landorf]

On most windows in GP, there is a Tools tab that you can select. From there you can select Table Finder. A new window will open, that gives you all of the information that you will need. If you select an option in the left side of the window, it will give you the table information on the right side, even a select query for SQL.?Take a look at the attachment.

——————————
Chase Landorf
Director of I.T.
Ideal Deals, LLC dba Ideal Aluminum Products
Saint Augustine FL
——————————
——————————————-

[Don McCready]

Thanks, Chase. 

I’ve looked at that already and the allocated qty field (ATYALLOC) in IV00102 shows the total allocated (8 items) which includes some valid allocations (6) and the invalid one (2).  If I change it to be the correct amount, I’d expect the reconciliation process to adjust it back to the incorrect total.
I can’t find where the bad allocation qty of 2 items is stored.  Am I wrong in thinking that and should just change the 8 qty to 6 in IV00102?

Here’s a 2011 suggestion I found from Leslie Vale about the same problem.  It indicates that I should just force the ATYALLOC to be the correct total but I’m skeptical that that’s all that’s needed since the record is also tied to a specific customer.
You’ve done reconcile, that usually fixes it. The other reconcile you could try in the reconcile in PSTL. However, over the years I’ve learned that if reconcile won’t fix it you need to go in to the IV00102  and change the qty allocated field for both the site record and the overall record.

Thanks again.

——————————
Don McCready
Retired Controller and IT Director
The Schaefer Group, Inc.
Beavercreek OH
——————————
——————————————-

[Chase Landorf]

You may need to do a little digging. You have the item number, so you can look at the IV30300 table. That has all inventory transactions listed with transaction sources, document types. You can search it on the item in question, from there.

——————————
Chase Landorf
Director of I.T.
Ideal Deals, LLC dba Ideal Aluminum Products
Saint Augustine FL
——————————
——————————————-

[Don McCready]

Thanks again, Chris.  I looked at IV30300 and it seems to have only valid data for valid sales docs.

——————————
Don McCready
Retired Controller and IT Director
The Schaefer Group, Inc.
Beavercreek OH
——————————
——————————————-

[Chris Donnelly]

Don,

Here are some more tables to check.  It depends on whether you use multi-bin and the tracking (serial or lot) of the item if they are populated.

IV00102
IV00112
IV00300

Chris

——————————
Chris Donnelly
Sr Mgr of Info Systems and Financial Reporting
Healthmark Industries
——————————
——————————————-

[Don McCready]

Thanks, Chris.  I looked at IV00112 and IV00300.  They are both empty, possibly because we don’t use bins or serial numbers.  IV00102 is the table that confuses me because it has a total allocated for the location and the overall company (main) total but no way to tie its allocations specific customers.

Thanks again.

——————————
Don McCready
Retired Controller and IT Director
The Schaefer Group, Inc.
Beavercreek OH
——————————
——————————————-

[Chris Donnelly]

Ahhhh.  I’ve got a handy script for finding all transactions (SO, MO, etc.) holding allocations.  If you email me, I’ll send it to you.  cdonnelly@hmark.com

Chris

——————————
Chris Donnelly
Sr Mgr of Info Systems and Financial Reporting
Healthmark Industries
——————————
——————————————-

[Don McCready]

Thanks to Chase Landorf and Chris Donnelly for their time to weigh in on this.  Based on Chris’s suggestion, I found an orphaned record in SOP10200 (no matching record in SOP10100).  Deleting that record and reconciling Sales resolved the issue.

(It goes without saying that the SQL databases should be backed up before working on them… so I won’t even mention that.)    😉

——————————
Don McCready
Retired Controller and IT Director
The Schaefer Group, Inc.
Beavercreek OH
——————————
——————————————-