Open Forum

Expand all | Collapse all

Invalid Item Allocation

  • 1.  Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 14 days ago
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
      |   view attached
    @Don McCready
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
    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 I\V00102  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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
    Edited by Don McCready 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Invalid Item Allocation

    TOP CONTRIBUTOR
    Posted 11 days ago

    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role