[Pam Palmer]

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

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

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

[William Weathersby]

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

[Tom Monfre]

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