Post Order Run
Post order run is used to fine-tune the AGR order proposal. Through this very specialized and advanced logic can be applied to any conditions we seek to adjust. We use post order run instead of changing the AGR code order logic itself. Typical example is the allocaiton logic seen at the bottom here where we automatically adjust the order proposal if we exhaust WH stock with the store demand. The overall goal should be to improve the system and reduce manual steps needed by the customer.
Post order run behaves slightly differently on manual or scheduled orders. The latter type can take in more custom actions if needed. Often, we only want to run specific post order run steps on larger scheduled orders (often orders created from specific MBE order reports).
Scheduled orders
Those orders have slightly different structure for possible post order runs. If needed we can put special logic into dbo.scheduled_orders_post_order_run_custom. Procedure dbo.post_order_run runs on all orders.
dbo.scheduled_orders_run_executedbo.order_run_executedbo.post_order_run- [custom logic could be called here]
dbo.scheduled_orders_post_order_run(adds “Scheduled order: " prefix to orderDescriptionfield)dbo.scheduled_orders_post_order_run_custom(post processing only on scheduled orders)
dbo.scheduled_orders_post
Manual orders
In standard AGR these are the procedures run in manual orders:
dbo.order_run_executedbo.post_order_run(which can then call custom logic as this examples)dbo.post_order_run_allocate
Post order run setup
Step 1
Make sure the following code is in dbo.order_run_execute. Set in after dbo.order_run_post_populate is executed. This step is normally already done by AGR install.
EXEC [dbo].[post_order_run]
@batch_id = @batch_id, @origin_id = @origin_id, @parent_id = @event_id,
@order_run_id = @order_run_id
Step 2. The main dbo.post_order_run procedure
This procedure is run every time a manual or scheduled order is created. This example shows how a complicated setup can be broken into easily understood modules making this procedure and its dependencies much easier to develop, test and maintain.
USE [agr_prod]
GO
/****** Object: StoredProcedure [dbo].[post_order_run] Script Date: 03.12.2020 09.59.20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sigurdur Skarphedinsson
-- Description: Post order run logic run on all orders
--
-- 20.12.2019.GH Removal of dependencies on scheduled order post order run, simplified flow
-- =============================================
ALTER PROCEDURE [dbo].[post_order_run]
(
@batch_id INT = NULL,
@origin_id TINYINT = NULL,
@parent_id INT = NULL,
@order_run_id INT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @event_id INT
EXEC @event_id = core.event_task_start @task_name = @@PROCID, @batch_id = @batch_id, @origin_id = @origin_id, @parent_id = @parent_id, @print = 1
SELECT 1 as returnValue
EXEC core.event_task_complete @event_id = @event_id, @print = 1
END TRY
BEGIN CATCH
EXEC core.catch_error @event_id = @event_id
END CATCH
ENDBreak logic up into independent parts. For clarity sake it is better to best to split post order run actions into separate procedures and then call them from dbo.post_order_run as seen below. Then dbo.post_order_run itself will be smaller and becomes much simpler to manage. This is a good principle to follow, even on smaller, simpler setups. Then each post order run part can be improved, tested and logged independent of the other logic in place. If we are dealing with many post order run procedures it is helpful to have them all use the same prefix, like dbo.post_order_run_[postfix].
EXEC dbo.post_order_run_allocate
@batch_id = @batch_id, @origin_id = @origin_id, @parent_id = @event_id,
@order_run_id = @order_run_id
Step 3. Adding custom post order run code as needed
Example code 1. Allocate logic (on order run id)
This allocates order from stores to warehouse. It processes all item numbers, and if stock in warehouse < sum of orders from stores for the item, it then allocates to all the stores by prioritizing the orders from highest to lowest store demand. We then start by giving the stores order multiples until we run out of stock. Have in mind that in this procedure, available WH stock has already been calculated and is stored in table dbo.item_extra_info.
USE [AGR_prod]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Elena
-- Description: Post order allocation
-- 13.03.2017.EL Only for stores ordering from WH
-- If the total amount stores are ordering from WH is greater than available stock at WH,
-- then the amount is adjusted to available WH amount proportionally to store demand and
-- rounded by min order and order multiple. If the amount is still larger than available stock at WH
-- then the stores ordered by their demand, reduce order qty by order multiple starting from the store with the smallest demand
-- 28.06.2017.GB Optimized by adding indexes and changing update
-- 28.06.2017.GB Added locking and unlocking of orderlines
-- 14.02.2019.GH Changed wh_stocks to wh_stocks_available column (which excludes previous sales lines to stores)
-- 28.06.2019.GH Added "Allocation. No available WH stocks" text to initial update query (if no available WH Stocks)
-- 04.11.2019.GH Logging of #order_allocation table for debugging porposes, minor syntax fixes
-- 05.05.2020.GH Changed to use WH stock available comm to support multipl replenishment order runs in one schedule
-- =============================================
-- exec [dbo].[post_order_run] 123, 527
ALTER PROCEDURE [dbo].[post_order_run_allocate]
(
@batch_id INT = NULL,
@origin_id TINYINT = NULL,
@parent_id INT = NULL,
@order_run_id AS INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @extra_info_string nvarchar(100)
SET @extra_info_string = 'Order run id: ' + cast(@order_run_id AS NVARCHAR(100))
EXEC @action_exec_id = dbo.log_action_execution @action_exec_id = @action_exec_id, @proc_id = @@PROCID, @extra_info = @extra_info_string
BEGIN TRY
DECLARE @order_run_iid int = @order_run_id
DECLARE @wh_orders int
SET @wh_orders =
(select count(*) from dbo.orders
where order_run_id = @order_run_iid
and order_from_location_id in (select id from dbo.locations where location_type = 'warehouse')
)
if @wh_orders > 0
BEGIN
-- Let's put order lines to zero if we have no Available WH stock
UPDATE ol
SET ol.[unit_qty_chg] = 0
, ol.description = CASE WHEN len(ol.description) > 0 THEN ol.description + '; ' + 'Allocation. No available WH stocks'
ELSE 'Allocation. No available WH stocks'
END
FROM [dbo].[order_lines] ol
join
(
SELECT
ol.id,
ei.wh_stocks_available_comm
FROM [dbo].[orders] o
INNER JOIN [dbo].[order_lines] ol ON o.id = ol.order_id
INNER JOIN dbo.items i ON ol.[item_id] = i.id
inner join dbo.item_extra_info ei on ei.item_id = i.id
WHERE o.[status] = 0 AND o.[deleted] = 0 AND o.[order_run_id] = @order_run_iid
and ISNULL(ei.wh_stocks_available_comm,0) <= 0
) ol_wh on ol.id = ol_wh.id
-- Create the working temp table
--28.06.2017.GB added priority into the clustered index
CREATE TABLE #order_allocation
(
[order_run_id] [int] NOT NULL,
[order_id] [int] NOT NULL,
[item_id] [int] NOT NULL,
[item_no] [nvarchar](255) NOT NULL,
[min_order] [int] NULL,
[order_multiple] [int] NOT NULL,
[min_order_multiple] [int] NOT NULL,
[unit_qty] [decimal](18, 4) NULL,
[new_unit_qty] [decimal](18, 4) NULL,
[total_unit_qty] [decimal](38, 4) NULL,
[pct] [decimal](38, 20) NULL,
[location_id] [int] NOT NULL,
[order_from_location_id] [int] NOT NULL,
[id] [int] NOT NULL,
[stock_available] [decimal](18, 4) NOT NULL,
[priority] [int] NOT NULL,
[description] [nvarchar](500) NULL,
[done] [int] NULL,
[used] [int] NULL,
PRIMARY KEY CLUSTERED (priority,order_run_id,[order_id],[item_id])
)
-- Let's populate the temp table with initial values
;WITH order_run_total
AS
(
SELECT
o.[order_run_id]
,i.item_no
,SUM(ol.[unit_qty_chg]) AS total_unit_qty
--, ei.wh_stocks_available
, ei.wh_stocks_available_comm
,o.[order_from_location_id]
FROM [dbo].[orders] o
INNER JOIN [dbo].[order_lines] ol ON o.id = ol.order_id
INNER JOIN dbo.items i ON ol.[item_id] = i.id
INNER JOIN dbo.item_extra_info ei on ei.item_id = i.id
WHERE o.[status] = 0 AND o.[deleted] = 0 AND o.[order_run_id] = @order_run_iid
GROUP BY o.[order_run_id], i.[item_no], o.[order_from_location_id], ei.wh_stocks_available_comm
having SUM(ol.[unit_qty_chg]) > ISNULL(ei.wh_stocks_available_comm,0) and ISNULL(ei.wh_stocks_available_comm,0) > 0
)
INSERT INTO #order_allocation
SELECT
o.[order_run_id]
,ol.[order_id]
,ol.[item_id]
,i.item_no
,ior.min_order
,ior.order_multiple
, case when ior.order_multiple > ior.min_order then ior.order_multiple else ior.min_order end
,ol.[unit_qty_chg]
,ol.[unit_qty_chg]
,ort.total_unit_qty
,ISNULL(ol.[unit_qty_chg]/NULLIF(ort.total_unit_qty, 0),0) AS pct
,o.[location_id]
,o.[order_from_location_id]
,ofi.id
,ort.wh_stocks_available_comm
,ROW_NUMBER() OVER(PARTITION BY o.[order_run_id], i.item_no, o.order_from_location_id ORDER BY ol.unit_qty_calculated desc, o.[location_id] )
-- ,''
,'Allocation. Demand exceeds available WH stock, qty adjusted'
,0
,0
FROM [dbo].[orders] o
INNER JOIN [dbo].[order_lines] ol ON o.id = ol.order_id
INNER JOIN [dbo].[item_order_routes] ior ON ol.item_id = ior.item_id AND o.order_from_location_id = ior.order_from_location_id
INNER JOIN dbo.items i ON ol.[item_id] = i.id
INNER JOIN dbo.items ofi ON o.order_from_location_id = ofi.location_id AND i.item_no = ofi.item_no
INNER JOIN order_run_total ort ON ort.[order_run_id] = o.order_run_id AND i.item_no = ort.item_no
INNER JOIN dbo.locations l ON l.id = o.order_from_location_id
WHERE o.[status] = 0 AND o.[deleted] = 0 AND ort.total_unit_qty > ort.wh_stocks_available_comm and ort.wh_stocks_available_comm > 0
GROUP BY o.[order_run_id]
,ol.[order_id]
,ol.[item_id]
,ior.min_order
,ior.order_multiple
,ol.[unit_qty_chg]
,ort.total_unit_qty
,o.[location_id]
,o.[order_from_location_id]
,ofi.id
,l.location_no
,i.item_no
,ort.wh_stocks_available_comm
,ol.unit_qty_calculated
--28.06.2017.GB added index
CREATE NONCLUSTERED INDEX [IX_temp_OrderAllocation_item_no]
ON [#order_allocation] ([item_no])
INCLUDE ([order_run_id],[order_id],[item_id])
--28.06.2017.GB added index
CREATE NONCLUSTERED INDEX [IX_temp_order_allocation_item_id]
ON [#order_allocation] ([item_id])
--select * from #order_allocation
--order by item_no, priority
-- Set qty to zero if available stock < 1 or unit_qty is negative
UPDATE oa
SET new_unit_qty = 0,
used = 1,
done = 1
FROM
#order_allocation oa
WHERE stock_available <= 0 or unit_qty < 0
UPDATE oa
SET new_unit_qty =
case
WHEN floor(stock_available*pct/order_multiple)*order_multiple < min_order_multiple then 0
ELSE floor(stock_available*pct/order_multiple)*order_multiple
end
FROM
#order_allocation oa
WHERE done = 0
UPDATE oa
SET stock_available = oa.stock_available - used.used_qty
FROM #order_allocation oa
INNER JOIN
(
SELECT
order_run_id
, item_no
, order_from_location_id
, SUM([new_unit_qty]) as used_qty
, COUNT(*) AS order_count
FROM #order_allocation oa
WHERE done = 0
GROUP BY [order_run_id], item_no, order_from_location_id
) used ON used.item_no = oa.item_no AND used.order_from_location_id = oa.order_from_location_id
UPDATE oa
SET done = 1,
used = 1
FROM
#order_allocation oa
WHERE done = 0 AND ((new_unit_qty = 0 and stock_available < min_order_multiple) or (new_unit_qty > 0 and stock_available < order_multiple))
--select * from #order_allocation
--order by item_no, priority
DECLARE @item_id INT
DECLARE @item_no [nvarchar](255)
DECLARE @order_from_location_id INT
DECLARE @order_multiple INT
DECLARE @min_order_multiple INT
DECLARE @unit_qty [decimal](18, 4)
DECLARE @stock_available [decimal](18, 4)
DECLARE @done INT
declare @orig_unit_qty [decimal](18, 4)
WHILE (SELECT count(*) FROM #order_allocation WHERE done = 0) > 0
BEGIN
SET @done = 0
SET @unit_qty = 0
IF (SELECT count(*) FROM #order_allocation WHERE used = 0) <= 0
BEGIN
UPDATE #order_allocation
SET used = 0
WHERE done = 0
END
SELECT TOP (1)
@item_id =item_id,
@order_multiple = order_multiple,
@order_from_location_id = order_from_location_id,
@item_no = item_no,
@stock_available = stock_available,
@min_order_multiple = min_order_multiple,
@unit_qty = new_unit_qty,
@orig_unit_qty = unit_qty
FROM #order_allocation
WHERE done = 0 and used = 0
ORDER BY priority
IF @unit_qty >= 0 and @orig_unit_qty > 0
begin
if @stock_available >= @order_multiple -- enough stock to allocate
begin
SET @unit_qty = @order_multiple
end
else -- not enough stock to allocate
begin
set @done = 1
end
end
else -- no stock allocated before
begin
if @stock_available >= @min_order_multiple
begin
SET @unit_qty = @min_order_multiple
end
else -- not enough stock to allocate
begin
set @done = 1
end
end
IF @done = 0 -- still stock available
BEGIN
UPDATE oa
SET new_unit_qty = new_unit_qty + @unit_qty,
done = @done,
used = 1
FROM #order_allocation oa
where item_id = @item_id
UPDATE oa
SET stock_available = stock_available-@unit_qty
FROM #order_allocation oa
where item_no = @item_no AND order_from_location_id = @order_from_location_id
END
ELSE
BEGIN
UPDATE oa
SET done = @done,
used = 1
FROM #order_allocation oa
where item_id = @item_id
END
update #order_allocation
set @done = 1
where item_no = @item_no
and stock_available <= 0
--select * from #order_allocation
--order by item_no, priority
END
update oa
set total_unit_qty = toa.new_qty
from #order_allocation oa
join (
SELECT SUM(new_unit_qty) AS new_qty, item_no, order_from_location_id
FROM #order_allocation
GROUP BY item_no, order_from_location_id
) toa ON oa.item_no = toa.item_no and oa.order_from_location_id = toa.order_from_location_id
--select * from #order_allocation
--order by item_no, priority
-- 04.11.2019.GH Let's store the temp values in an actual table for later review
-- But only do this on scheduled orders each day (origin 2)
INSERT INTO cus.post_order_run_allocation_raw
(order_run_id, order_id, item_id, item_no, min_order, order_multiple, min_order_multiple, unit_qty, new_unit_qty, total_unit_qty, pct,
location_id, order_from_location_id, id, stock_available, priority, description, done, used)
SELECT
oa.order_run_id, oa.order_id, oa.item_id, oa.item_no, oa.min_order, oa.order_multiple, oa.min_order_multiple, oa.unit_qty, oa.new_unit_qty, oa.total_unit_qty, pct,
oa.location_id, oa.order_from_location_id, oa.id, oa.stock_available, oa.priority, oa.description, oa.done, oa.used
FROM
#order_allocation oa
INNER JOIN dbo.orders o ON o.id = oa.order_id
WHERE
o.origin_id = 2
AND o.description like '%Daily orders%'
-- Actual update of order lines from the working table
UPDATE ol
SET ol.unit_qty_chg = oa.new_unit_qty,
ol.description =
CASE
WHEN len(ol.description) > 0 THEN ol.description + '; ' + oa.description + ' to ' + cast(CAST(oa.new_unit_qty AS INT) AS varchar(10))
ELSE oa.description + ' to ' + cast(CAST(oa.new_unit_qty as int) AS varchar(10))
END
OUTPUT inserted.id, 8, inserted.unit_qty_chg INTO cus.bi_log_order_line_changes (order_line_id, event_type_id, qty)
FROM dbo.order_lines ol
INNER JOIN #order_allocation oa ON ol.order_id = oa.order_id AND ol.item_id = oa.item_id
WHERE oa.new_unit_qty != ol.unit_qty_chg -- 12.09.2018.GH Only update lines that are actually changed
END
--@wh_orders > 0
END TRY
BEGIN CATCH
EXEC core.catch_error @event_id = @event_id
END CATCH
ENDSpecific operations in post order run
Adjusting “Description” field on order lines
It is strongly recommended to write useful information into the order line “Description” column when post order adjusts the order line in any way. This helps the customers make sense of what is going on and can also dramatically speed up consulting debugging on order lines. It is best to include as much useful information as possible (prior qty value, which logic changed it), without being too verbose. Care should be taken not to overwrite already exiting description texts and thus we test for it in the example below and append to the end of the string, if any.
When not to write to the Description field. Example of this use might be something like special qty rounding rules that would update too many order lines to be useful to flag them as changed, then we avoid both the green line and the Description field updates.
UPDATE ol
SET
ol.unit_qty_chg = ior.order_multiple,
ol.description =
CASE
WHEN LEN(ol.description) > 0
THEN CAST(ol.description, '; ', ol.unit_qty_chg AS INT), ' adjusted to ' , cast(ior.order_multiple AS INT) ,' (#4)')
ELSE
CAST(ol.unit_qty_chg AS INT), ' adjusted to ' , CAST(ior.order_multiple AS INT) ,' (#4)')
END
FROM dbo.orders o
INNER JOIN dbo.order_lines ol ON ol.order_id = o.id
INNER JOIN dbo.items i ON i.id = ol.item_id
INNER JOIN dbo.item_order_routes ior ON ior.item_id = i.id
WHERE
o.description like '%Forbruk alle dage%'
AND o.order_run_id = @order_run_id Running only on specific report
Often, we only want to run the post order run on some orders, like orders created specific report name. In the example below we only run this logic on the large Scheduled daily order. Manual order from that same MBE report would not activate this logic.
IF (@origin_id = 1 AND (@order_description LIKE '%Daily orders%' '))
BEGIN
EXEC dbo.post_order_run_super_planner @action_exec_id = @action_exec_id, @order_run_id = @order_run_id
END
Labeling
Often it is useful to give each post order run procedure a “label” or number, since then we can talk about “#4 seems to be wrong on this product group” instead of something vague.
Improved logging of post order run
With one simple change to the post order run procedures we can vastly improve logging of their operations and make debugging much quicker. We only need to adjust the following to start writing better info to the logging tables.
DECLARE @extra_info_string NVARCHAR(100)
SET @extra_info_string = 'Order run id: ' + CAST(@order_run_id AS NVARCHAR(100))
EXEC @action_exec_id = dbo.log_action_execution @action_exec_id = @action_exec_id, @proc_id = @@PROCID, @extra_info = @extra_info_string
This would look something like this example of a main scheduled daily order showing all post order run operations on it.
Avoiding the vertical green line in order view
Some customers do not want to see the green vertical line, indicating an edited order line, when they open their orders after post order run has completed. Seeing the green line can cause confusion as to if a person has perhaps already edited the order line already or not.
In those cases, we simply update both columns unit_qty and unit_qty_chg in the post order run updates. We could update the “Description” field even in those cases - if that makes sense in those conditions.