Knowledgebase: Tutorials
[QODBC-Desktop] Troubleshooting - How do I see backorder items that have been invoiced but not shipped
Posted by Jack - QODBC Support on 09 May 2016 10:04 AM

Troubleshooting - How do I see backorder items that have been invoiced but not shipped

Problem Description:

Could someone kindly provide me with a sample, or is there a stored procedure that would allow me to only pull out back ordered invoices from the invoice table?

If a customer orders some items, we create an invoice. Then if some things are back-ordered, when the packing slip is made, it shows they were not sent out, and that item is back-ordered. I was looking for a query or an SP that would lead me to only invoices that have not been completely shipped or all the items in backorder.
 

Solution:

All you need to do is use the ItemInventory table to pick up the quantity on hand and compare it with the ordered quantity ordered and invoiced to date (invoiced or not) and call the column "Qty to Pick."

Something along the lines of:

SELECT SalesOrderLine.SalesOrderLIneItemRefFullName as Type, SalesOrderLine.TxnDate as Date, SalesOrderLine.DueDate, SalesOrderLine.RefNumber as Num, SalesOrderLine.CustomerRefFullName as Name, SalesOrderLine.SalesOrderLineQuantity as "Qty Ordered," SalesOrderLine.SalesOrderLineInvoiced as "Qty Invoiced," (SalesOrderLine.SalesOrderLineQuantity - SalesOrderLine.SalesOrderLineInvoiced) as "Qty to Pick," ItemInventory.QuantityOnHand as "Qty Available," ItemInventory.QuantityOnOrder as "Qty on Back Order" FROM SalesOrderLine, ItemInventory where SalesOrderLine.SalesOrderLineItemRefListID = ItemInventory.ListID and SalesOrderLine.IsFullyInvoiced = FALSE and SalesOrderLine.SalesOrderLineInvoiced < SalesOrderLine.SalesOrderLineQuantity

(1 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).