Changing the status of a purchase order to Closed when all lines are received/booked in

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

Jiwa 7

Changing the status of a purchase order to Closed when all lines are received/booked in

Analytics

Step-by-step guide

SQL skills are required

Related articles

Related content