Open Forum

Expand all | Collapse all

Inventory transfer query

  • 1.  Inventory transfer query

    SILVER CONTRIBUTOR
    Posted 5 days ago
    We have numerous items in our inventory that are sold under different name and item numbers.
    For example:
      item 1000 "Item A" might be sold as item 1050 "Item B" and item 1060 "Item C".

    We normally keep inventory in one item and then do an inventory transfer when needed.

    I'm trying to get a report for all the "base" item numbers and the other items numbers it has been transferred to.
    Something like this:
      item 1000 -> transferred to 1050, 1060, 1070, etc.

    I'd like to do this with a sql query.

    Can someone point me in the right direction for the tables I need to access?

    ------------------------------
    Rick Roen
    Seeds by Design
    Willows CA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Inventory transfer query

    TOP CONTRIBUTOR
    Posted 4 days ago
    Hi Rick,

    The main table you want is the IV30300 - Inventory Transaction Amounts History. If you are transferring from one item number to another, are you doing a negative adjustment to 1000 and a positive adjustment to the second item number? That would good to know, to help in creating a SQL query.


    ------------------------------
    Mark Wiley
    Dynamics Credentialed Professional
    Senior Solutions Architect
    GraVoc
    Ohio Office
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Inventory transfer query

    SILVER CONTRIBUTOR
    Posted 4 days ago
    Thanks Mark,

    That table is a good starting point.  I am going trace through some of these transactions and see if I can translate back to the table structure.

    -Rick

    ------------------------------
    Rick Roen
    Seeds by Design
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Inventory transfer query

    SILVER CONTRIBUTOR
    Posted 4 days ago
    Hi Rick,

    In addition to Mark's answer, you may want to consider using Customer Items in the Sales module so you can accomplish selling under different item numbers without the need to transfer quantities.

    ------------------------------
    John Kirsch
    GP Product Lead
    Dynamic Consulting
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Inventory transfer query

    SILVER CONTRIBUTOR
    Posted 4 days ago
    Thanks John,

    In our case all items that can be transferred to another are available to all customers.  I see where this can be assigned on an individual basis in the Customer Item Maintenance, but is it also available in some other place where it would apply to all?

    -Rick

    ------------------------------
    Rick Roen
    Seeds by Design
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Inventory transfer query

    GPUG ALL STAR
    Posted 4 days ago
    Richard,
    I don't think there is a way to mass-change or assign items to customers with their own item numbers... The table that holds that information is the SOP60300 and the Customer Item substitute table is the SOP00300.
    You might be able to import bulk data directly in one of the 2 tables, as I don't think there is any other dependencies...
    As last resort, you could create a GP macro with mail-merge process to bulk-add customer items to this table thru the front-end.


    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


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