Open Forum

Expand all | Collapse all

Sales Line Items with Available to Promise

  • 1.  Sales Line Items with Available to Promise

    GOLD CONTRIBUTOR
    Posted Sep 20, 2017 02:59 PM
    Using GP 2013 R2 -

    I am trying to create a SmartList or SQL view which will show me "Sales Line Items" information along with info from the "Available to Promise Inquiry".

    I initially attempted to add the "Existing qty Available" but found out it does not match the information on the AVAIL to Promise Inquiry.

    Following is a screen shot from SmartList>Sales>Sales Line Items
    you will see that I have a column for QTY, QTY to invoice (why they're not the same I do not know)
    QTY b/o, On PO, Drop Ship

    sales line items
    I would like to add the columns from the AVAILABLE to Promise Inquiry window:  ON Hand, Allocated, Available

    Available to Promise Inquiry
    Creating a SmartList (or SQL view) for this would allow me to look at ONE screen for all my information.
    I review our orders and look to see if there is another order I can liberate material from that is not ready to ship and have a PO committed to the liberated order rather than a rush order that needs to go.

    I would appreciate any insight/info that anyone would care to share.  I was unable to marry my information together on my own.

    ------------------------------
    Pam Palmer
    Projects/Database Admin
    Temple, Inc.
    Decatur AL
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Sales Line Items with Available to Promise

    Posted Sep 21, 2017 09:40 AM
    Pam,

    You are on the right track. Let me first explain what you numbers mean that you have in your query.  The QTY field is the Quantity ordered,  Quantity to Invoice is the quantity allocated , and of course the Qty back ordered  is self evident .

    I believe what you are trying  to bring in is the information from IV00102  Or the Item Site. This table contains a summary from all sites (RCRDTYPE =1) or each individual site (RCRDTYPE=2) .  which contains  an over summary of the item quantities that you are looking for with similar field names from the SOP line.    Depending on the number of sites you have and your business you may want to link to the summary or detail site record.

    Let me know if this is not clear I can send you a sample query.

    ------------------------------
    William Weathersby
    President
    Intelligent Technologies, Inc.
    Greensboro NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Sales Line Items with Available to Promise

    TOP CONTRIBUTOR
    Posted Sep 21, 2017 09:47 AM
    William is correct about the IV00102 table but I would caution that it's accuracy can come into question.  Maybe it's just our system but we find discrepancies and have to reconcile inventory quantities to correct the values in the IV00102 table.

    ------------------------------
    Tom Monfre
    Batteries Plus Bulbs, LLC
    Hartland WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


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