Open Sales Orders

SAP B1 9.1 Open sales order Quantity query

I have this query but couldn't translate CustomerRef into rows and columns.

SELECT T0.[DocDueDate],
    T0.[CardName],
    T0.[NumAtCard],
    T0.[DocNum],
    T1.[ItemCode],
    T1.[Dscription],
    T3.[WhsCode],
    T3.[OnHand] - SUM(OpenQty) OVER (Partition By T1.ItemCode ORDER BY DocDueDate, LineNum, NumAtCard, DocNum) + SUM(T1.OpenQty) AS 'Available',
    SUM(T1.[Quantity]) AS 'PO QTY',
    SUM(T1.OpenQty) AS 'To Release'
FROM
    ORDR T0 
    INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
    INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode] AND T1.[WhsCode] = T3.[WhsCode]
WHERE
    T0.[CardCode] = 'c00192' and
    T0.[DocStatus] = 'O' AND
    T1.[LineStatus] = 'O' AND
    T0.[CANCELED] = 'N' AND
    T1.[WhsCode] = '161'           
GROUP BY
    T0.[DocDueDate], 
    T0.[CardName], 
    T0.[NumAtCard], 
    T0.[DocNum], 
    T1.[ItemCode], 
    T1.[Dscription],
    T3.[OnHand],
    T3.[WhsCode],
    T1.[LineNum],
    T1.OpenQty
ORDER BY 
    T0.[DocDueDate], 
    T0.[NumAtCard], 
    T0.[DocNum]

Open Sales Order from Custom screen

docgraph.Document.Search<SOOrder.orderType>(SOOrderTypeConstants.SalesOrder); this code will return you first Order of SalesOrder type. Instead of this you can do the following:

1. Create an instance of SalesOrder.

2. Set the OrderType of the SalesOrder to the one you need.

3. Set docgraph.Document.Current to that SalesOrder.

Your code will be like the following(may some changes will be required):

SOOrderEntry docgraph = PXGraph.CreateInstance<SOOrderEntry>();
SOOrder newOrder = docgraph.Document.Insert();//create new Order
newOrder.OrderType = "IN"; // set the OrderType to the one you need.For example "IN"
newOrder = docgrapg.Document.Update(newOrder);//  update the order
docgraph.Document.Current = newOrder;// set your order as the current order of the BLC
throw new PXRedirectRequiredException(docgraph, true, "Order") { Mode = PXBaseRedirectException.WindowMode.Same };

What is the moment when the sales order is fully formed with its lines and the entire posting process is finished?

That's sort of impossible, because a SO can be reopened or if you have an open SO and your user has added 1 line...well who knows if the user is done adding lines? How do you know when it's "complete"? You could go off of the point of confirmation, but not everyone does confirmations. You need to determine some "point" in business that you consider "complete" and then go from there. You could trigger an event after each SO line is created if your code is written to be idempotent?

Sales Order status not changing after shipment created

The status is updated towards the end of the CreateShipment method based on SOOrder.UpdateShipmentCntr field value if no exception were thrown during shipment creation:

if (order.OpenShipmentCntr > 0)
{
   order.Status = SOOrderStatus.Shipping;
   order.Hold = false;
   soorder.Update(order);
}

The OpenShipmentCntr field is updated by the SOShipmentEntry.UpdateShipmentCntr method:

protected virtual void UpdateShipmentCntr(PXCache sender, object Row, short? Counter)
{
    SOOrder order = (SOOrder)PXParentAttribute.SelectParent(sender, Row, typeof(SOOrder));
    if (order != null)
    {
        order.ShipmentDeleted = (Counter == -1) ? true : (bool?)null;
        order.ShipmentCntr += Counter;
        if (((SOOrderShipment)Row).Confirmed == false)
        {
            order.OpenShipmentCntr += Counter;
        }
        soorder.Cache.SetStatus(order, PXEntryStatus.Updated);
    }
}

When creating the shipment, SOShipmentEntry should insert a SOOrderShipment record that links SOOrder (Sales Order) with SOShipment (Shipment) records. The SOOrderShipment should be visible in the Orders tab of the shipment:

After insertion of the SOOrderShipment record the SOOrderShipment RowInserted event in SOShipmentEntry is raised:

protected virtual void SOOrderShipment_RowInserted(PXCache sender, PXRowInsertedEventArgs e)
{
    UpdateShipmentCntr(sender, e.Row, (short)1);
}

I couldn't determine the reason why the status won't change so I'd suggest to debug the steps leading to sales order status change.

How do I get sales orders where every line of the sales order is closed?

SELECT so_ID
FROM sales_order_table 
GROUP BY so_ID
HAVING MAX(SO_line_status) = 'Closed' AND
       MIN(SO_line_status) = 'Closed' AND
       COUNT(CASE WHEN SO_line_status IS NULL THEN 1 END) = 0

You could also use EXCEPT if your RDBMS supports it

SELECT so_ID
FROM sales_order_table 
WHERE SO_line_status = 'Closed'
EXCEPT
SELECT so_ID
FROM sales_order_table 
WHERE SO_line_status IS NULL OR SO_line_status <> 'Closed' 

Simple Query that returns millions of records for sales orders, invoices etc... is too slow

You should examine the query plan (produced by EXPLAIN), but in general OR is notorious for not using indexes.

Try splitting the query into two parts and put each side of the OR in each:

SELECT 
   ...
FROM ...
WHERE T1.[DocDate] BETWEEN [%0] AND [%1]
UNION
SELECT 
   ...
FROM ...
WHERE T3.[DocDate] BETWEEN [%0] AND [%1]

Note also the use use of BETWEEN for more clarity.

---

Related Topics