Display unprocessed Order details based on order quantity by stored procedure

Vaidehi · August 25, 2016, 6:26pm

I am new to database and trying out some stuff with stored procedure.I am using 3tbales in my stored procedure - Customer, OrderDetails and Order. I am displaying all the unprocessed orders based on the quantity field of orderdetails table.Whenever, the particular order is processed, in my OrderDetail table there is fillInfo field where multiple purchaseOrder number gets inserted from Order table.The data is being displayed but as soon as even 1 quantity is processed then the data related to that particular order disappears.Suppose, if customer orders 10 quantities in 1 order and if I process 2 of them, then that order should be still be displayed in unprocessed orders list because 8 quantities is still remaining to be delivered to customer.

My orderdetail table looks like

SELECT TOP 1000 [lineID]
      ,[orderID]
      ,[itemNumber]
      ,[itemDesc]
      ,[qty]
      ,[price]
      ,[priceType]
      ,[contract]
      ,[supplierPartID]
      ,[uom]
      ,[classDomain]
      ,[classValue]
      ,[comments]
      ,[shipping]
      ,[tax]
      ,[reqDeliveryDate]
      ,[fillInfo]
      ,[invoiceID]
      ,[Version]
      ,[productid]
      ,[eta]
      ,[lineOrderID]
  FROM [bfdb].[dbo].[OrderDetails]

here orderid comes from order table where order table looks like

 SELECT TOP 1000 [orderID]
      ,[customerID]
      ,[billAddressID]
      ,[shipAddressID]
      ,[orderDate]
      ,[employeeID]
      ,[salesTax]
      ,[poNumber]
      ,[dueDate]
      ,[terms]
      ,[shipMethod]
      ,[shippingFee]
      ,[shippingNotes]
      ,[oStatus]
      ,[createDate]
      ,[source]
      ,[comments]
      ,[cardType]
      ,[cardNumber]
      ,[expMonth]
      ,[expYear]
      ,[nameOnCard]
      ,[cvvCode]
      ,[cancelDate]
      ,[total]
      ,[url]
      ,[role]
      ,[consolidationPoint]
      ,[cxmlData]
      ,[authStatus]
      ,[authCode]
      ,[transactionID]
      ,[authDateTime]
      ,[avsResponse]
      ,[cvvResponse]
      ,[Version]
      ,[couponCode]
      ,[discountAmt]
      ,[cageCode]
      ,[awardID]
      ,[versionNumber]
      ,[invoiceEmail]
      ,[trackingEmail]
  FROM [bfdb].[dbo].[Orders]

1 orderid can have multiple item number with large quantities for same purchase order

I have attached the snapshot of my stored procedure result

and here is my stored procedure

	SELECT o.orderID, o.orderDate, o.oStatus, e.userName,
                                o.poNumber,
                                c.customerName, c.company, o.source, 
                                (
                                (SELECT SUM(od.price * od.qty) FROM OrderDetails od WHERE o.orderID = od.orderID) 
                                + o.salesTax + o.shippingFee
                                ) As Total,
                                ((SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID) 
                                -
                                (SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID AND (fillInfo != '' OR fillInfo != null))
                                ) As Filled
                                FROM Orders o 
                                JOIN Customers c on o.customerID = c.customerID
                                JOIN Employees e on o.employeeID = e.employeeID
                                WHERE (o.employeeID>0 AND o.oStatus='Active' AND
                                (((SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID) 
                                -
                                (SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID AND (fillInfo != '' OR fillInfo != null))
                                ) > 0))
                                ORDER BY o.orderID

There is filled column in stored procedure which shows the total quantities unprocessed . But still I am not able to display orders for the remaining quantities because as soon as even 1 quantity is processed for particular order, the data from unprocessed list goes away. The data in the unprocessed list is displayed from above stored procedure.

ScottPletcher · August 25, 2016, 7:30pm

SELECT o.orderID, o.orderDate, o.oStatus, e.userName,
                                o.poNumber,
                                c.customerName, c.company, o.source, 
                                od.extended_price + o.salesTax + o.shippingFee As Total,
                                od.total_qty - od.total_fillInfo As Unfilled,
                                od.total_fillInfo As Filled
                                FROM Orders o 
                                CROSS APPLY (
                                    SELECT ISNULL(SUM(od.price * od.qty), 0) As extended_price,
                                        ISNULL(SUM(od2.qty), 0) AS total_qty, 
                                        ISNULL(SUM(od2.fillInfo), 0) As total_fillInfo
                                    FROM dbo.OrderDetails od2
                                    WHERE od2.orderID = o.orderID
                                ) AS od
                                JOIN Customers c on o.customerID = c.customerID
                                JOIN Employees e on o.employeeID = e.employeeID                                
                                WHERE (o.employeeID>0 AND o.oStatus='Active') AND
                                    (od.total_qty > od.total_fillInfo)                                    
                                ORDER BY o.orderID

Vaidehi · August 25, 2016, 8:14pm

Hi,

It seems that you are trying to subtract the totalFillInfo from totalquantity. But I dont have any processed quantity in FillInfo. I have mentioned that it contains the poNumber of order table
this is my orderdetails table data

ScottPletcher · August 25, 2016, 8:22pm

Sorry. But you didn't provide a shipment table or other method of determining qty ordered vs qty filled. You are counting lines in the OrderDetails table, but I don't see how that would tell you what qtys are filled vs not filled.

Vaidehi · August 25, 2016, 8:27pm

Quantity ordered will be in orderdetails table only. As they are processed there poNumbers are entered automatically in FillInfo field which is order detail table itself. All the unprocessed order quantities are intially mention in the filled column generated from stored procedure

ScottPletcher · August 25, 2016, 8:49pm

Understood. And there are no partial fills then. A line is either only "filled" or "not filled". Different ordering systems handle that differently, so I wasn't sure. Also, you wrote this:

I am displaying all the unprocessed orders based on the quantity field of orderdetails table

At any rate, maybe something like this:

SELECT o.orderID, o.orderDate, o.oStatus, e.userName,
            o.poNumber,
            c.customerName, c.company, o.source, 
            od.extended_price + o.salesTax + o.shippingFee As Total,
            od.total_count - od.filled_count AS Filled
            FROM Orders o 
            CROSS APPLY (
                SELECT ISNULL(SUM(od2.price * od2.qty), 0) As extended_price,
                    SUM(CASE WHEN od2.fillInfo > '' THEN 1 ELSE 0 END) AS filled_count,
                    SUM(1) AS total_count
                FROM dbo.OrderDetails od2
                WHERE od2.orderID = o.orderID
            ) AS od
            JOIN Customers c on o.customerID = c.customerID
            JOIN Employees e on o.employeeID = e.employeeID                                
            WHERE (o.employeeID>0 AND o.oStatus='Active') AND
                (od.total_count > od.filled_count)
            ORDER BY o.orderID

Vaidehi · August 25, 2016, 8:54pm

Hey thanks a lot for your concern. It gives me the same result as mine. No difference. When I display this data, and after that If i process 1 quantity then the remaining quantities are also not being displayed. They also interpret as processed.

ScottPletcher · August 25, 2016, 9:51pm

Yeah. You're not distinguishing between "any fill" and "total fill". The tables you've given so far can't do it. Is there some type of "shipped" / "invoiced" / whatever table that contains the quantity actuallyprocessed, rather than just the po number(s).

Vaidehi · August 26, 2016, 9:00pm

Hey Hi,

This is the purchaseOrderDetails table which has qty and qty recieved field. Here is the table design. So here even if 1quantity is recieved, the item is not displayed in unprocessed orders.

SELECT TOP 1000 [podID]
      ,[poID]
      ,[distPN]
      ,[mfrID]
      ,[itemNumber]
      ,[itemName]
      ,[qty]
      ,[price]
      ,[invoiceID]
      ,[orderID]
      ,[itemReceived]
      ,[qtyReceived]
      ,[distConf]
      ,[distFillStatus]
      ,[distLastUpdate]
      ,[distTracking]
      ,[distFillDesc]
      ,[Version]
      ,[productid]
      ,[lineOrderID]
  FROM [bfdb].[dbo].[PurchaseOrderDetails]

Sample Data of PurchaseorderDetails Table