Changing the status of a purchase order to Closed when all lines are received/booked in
- Beth Blok (Unlicensed)
- Julie Hanlon
Owned by Beth Blok (Unlicensed)
Purchase orders retain the status of Sent until the status is manually set to 'Closed'.
To update historical POs, use the script below.
To automatically set them to closed in the future, use the plugin attached.
Step-by-step guide
SQL skills are required
SQL skills are required to complete this task
Run the following script and set as a repeating task to set the purchase order status to Closed when all lines are received
-- sets the status of a purchase order to Closed where all lines have been received / booked in
--List of orders to be closed
SELECT
OrderNo, OrderedDate, AccountNo, Name
FROM
PO_Main
JOIN CR_Main ON PO_Main.CreditorID = CR_Main.CreditorID
JOIN (SELECT
PO_Main.OrderID
FROM
PO_Main
JOIN PO_Lines ON PO_Main.OrderID = PO_Lines.OrderID
WHERE
PO_Lines.LineType <> 0
AND PO_Main.Status <> 10
GROUP BY
PO_Main.OrderID
HAVING
SUM(CASE WHEN PO_Lines.Delivered >= PO_Lines.Quantity THEN 1 ELSE 0 END) = SUM(1)) AS DeliveredOrders ON PO_Main.OrderID = DeliveredOrders.OrderID
--Close orders
UPDATE
PO_Main
SET
Status = 10
FROM
PO_Main
JOIN (SELECT
PO_Main.OrderID
FROM
PO_Main
JOIN PO_Lines ON PO_Main.OrderID = PO_Lines.OrderID
WHERE
PO_Lines.LineType <> 0
AND PO_Main.Status <> 10
GROUP BY
PO_Main.OrderID
HAVING
SUM(CASE WHEN PO_Lines.Delivered >= PO_Lines.Quantity THEN 1 ELSE 0 END) = SUM(1)) AS DeliveredOrders ON PO_Main.OrderID = DeliveredOrders.OrderID