Our purchasing manager was stumbling upon scenarios where the average lead times that were being calculated by our ERP system weren't always reliable, causing issues with maintaining our customers' mix/max levels on certain items.
So she had the thought to come up with something that could/would calculate an average lead time for items she had to order in a way that she felt would be more reliable - by taking the last (up to) three completely received POs and using those to calculate an average that might be more in line with 'recent reality'.
We could then compare the ERP system's stated average lead time and compare it to our calculated average, and whichever one was the greater amount would be the one she would base her purchasing activities on in the hopes of having less "Oh crap, they're going to run out!" moments on critical items.
There was also the added bonus of starting a historical list of item order dates, final receipt dates, and the actual lead times for those completed POs to look back on should the need arise. You can never have too much data.
USE [REPORTINGSERVER]
GO
/****** Object: StoredProcedure [dbo].[getCompletePOsThenCalculateAveragesGETTERSANDDUMPERS] Script Date: 3/1/2022 1:13:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Scott Barrowcliffe
-- Create date: 03112021
-- Description: Trying to get to a point where we have a historical record of completed POs with which to calculate lead time averages.
-- =============================================
ALTER PROCEDURE [dbo].[getCompletePOsThenCalculateAveragesGETTERSANDDUMPERS]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- get a list of POs
SELECT *
INTO #POLIST
FROM
(
SELECT
POHEAD.po AS po
,POLINE.item AS item
,POHEAD.ord_date AS ord_date
,WAITTRANS.trans_date AS rcpt_date
,POLINE.q_ord AS q_ord
,WAITTRANS.qty_d AS q_rcv
,CAST(POHEAD.po AS NVARCHAR) + CAST(POLINE.item AS NVARCHAR) AS maxFinderHelper
FROM
OPENQUERY(DISTONE,'SELECT po, ord_date, rcv_date, ord_class, warehouse, rec_seq, invc_date,post_date FROM V2.PUB.po_head WHERE ord_class = ''W'' AND rec_type=''O'' AND company_it = ''MCI'' AND ord_date >= ADD_MONTHS(SYSDATE,-96)')
AS POHEAD INNER JOIN
OPENQUERY(DISTONE, 'SELECT po, rec_type, rec_seq, req_date, line, line_add, descr, dsc_pct, r_ext, item, q_ord, q_ord_d, q_rcv, q_rcv_d, vendor, received, invoice, order, invc_date FROM V2.PUB.po_line WHERE ord_class = ''W'' AND rec_type=''O'' AND warehouse = ''MCA''')
AS POLINE ON POHEAD.po = POLINE.po AND POHEAD.rec_seq = POLINE.rec_seq LEFT OUTER JOIN
OPENQUERY(DISTONE,'SELECT trans_no, item, ref_id, ref_line, trans_date, rec_type, qty_d FROM V2.PUB.wa_it_trans WHERE company_it = ''MCI'' AND warehouse = ''MCA'' AND rec_type = ''R''')
AS WAITTRANS ON WAITTRANS.item = POLINE.item AND WAITTRANS.ref_id = POLINE.po AND WAITTRANS.ref_line = POLINE.line_add INNER JOIN
(
SELECT item
FROM OPENQUERY(DISTONE, 'SELECT item, discontinued FROM V2.PUB.item WHERE company_it = ''MCI''')
WHERE discontinued = 'False'
) AS ITEM ON ITEM.item = POLINE.item
WHERE POLINE.item <> 'TOOL REPAIR'
) AS polist
-- ok, now get the complete ones
SELECT *
INTO #FINDINGCOMPLETE
FROM
(
SELECT
po
,item
,ord_date
,maxFinderHelper
FROM
(
SELECT
po
,item
,MAX(q_ord) AS q_ord
,SUM(q_rcv) AS sum_q_rcv
,MAX(ord_date) AS ord_date
,MAX(maxFinderHelper) AS maxFinderHelper
FROM #POLIST
GROUP BY po,item
) AS COMPLETE
WHERE q_ord - sum_q_rcv <= 0 -- all received
) AS findingcomplete
SELECT *
INTO #GETTHELASTRCVDATE
FROM
(
SELECT
po
,item
,MAX(rcpt_date) AS last_rcpt_date
FROM
#POLIST
WHERE maxFinderHelper IN (
SELECT DISTINCT maxFinderHelper
FROM #FINDINGCOMPLETE
)
GROUP BY po, item
) AS getthelastrcvdate
SELECT *
INTO #COMPLETEPOLIST
FROM
(
SELECT
FC.po
,FC.item
,FC.ord_date
,GD.last_rcpt_date
,DATEDIFF(DAY,FC.ord_date,GD.last_rcpt_date) AS leadTime
,CAST(FC.po AS NVARCHAR) + CAST(FC.item AS NVARCHAR) + CAST(DATEDIFF(DAY,FC.ord_date,GD.last_rcpt_date) AS NVARCHAR) AS thisIsAKey
FROM
#FINDINGCOMPLETE AS FC INNER JOIN
#GETTHELASTRCVDATE AS GD ON FC.po = GD.po AND FC.item = GD.item
) AS completepolist
-- now grab existing complete PO list to use as a filter on the
-- list we just compiled before we dump new ones to the table
SELECT *
INTO #GETEXISTINGFROMTABLE
FROM
(
SELECT DISTINCT thisIsAKey AS thisIsAkey
FROM [REPORTINGSERVER].[dbo].[CompletePOs] AS fromTheTable
) AS getexistingfrontable
INSERT INTO [REPORTINGSERVER].[dbo].[CompletePOs]
SELECT
CPL.po
,CPL.item
,CPL.ord_date
,CPL.last_rcpt_date
,CPL.leadTime
,CPL.thisIsAKey
,GETDATE()
FROM
#COMPLETEPOLIST AS CPL LEFT OUTER JOIN
#GETEXISTINGFROMTABLE AS GE ON CP.thisIsAKey = GE.thisIsAkey
WHERE
CPL.leadTime IS NOT NULL
AND GE.thisIsAKey IS NULL
--=========================================================================
--=========================================================================
-- now get the list of completed POs and add a row number
-- to count the number of times an item's PO was completed
SELECT *
INTO #GETCOMPLETEPOCOUNTS
FROM
(
SELECT
po
,item
,ord_date
,last_rcpt_date
,leadTime
,ROW_NUMBER() OVER (PARTITION BY item ORDER BY po,last_rcpt_date) AS occurrence
FROM [REPORTINGSERVER].[dbo].[CompletePOs]
) AS getcompleteposcounts
-- then get the last occurrences (up to three) for each item
SELECT *
INTO #GETLASTOCCURRENCES
FROM
(
SELECT
CAST(COUNTS.po AS NVARCHAR(MAX)) AS po
,CAST(GETMAX.item AS NVARCHAR(MAX)) AS item
,COUNTS.ord_date
,COUNTS.last_rcpt_date
,COUNTS.leadTime
FROM
#GETCOMPLETEPOCOUNTS AS COUNTS INNER JOIN
(
SELECT
item
,MAX(occurrence) AS maxOccurrence
FROM #GETCOMPLETEPOCOUNTS
GROUP BY item
) AS GETMAX ON COUNTS.item = GETMAX.item AND COUNTS.occurrence >= (GETMAX.maxOccurrence - 2)
) AS getlastoccurrences
-- and then calculate the average lead times based
-- on those last occurrences
SELECT *
INTO #GETAVERAGES
FROM
(
SELECT
item
,SUM(leadTime)/COUNT(item) AS averageLeadTime
,COUNT(item) AS basedOnXOccurrences
FROM #GETLASTOCCURRENCES
GROUP BY item
) AS getaverages
SELECT *
INTO #GETTHELASTORDERDATE
FROM
(
SELECT
item
,MAX(ord_date) AS last_ord_date
FROM
#FINDINGCOMPLETE
WHERE item IN (SELECT DISTINCT item FROM #FINDINGCOMPLETE)
GROUP BY item
) AS getthelastordvdate
-- This next bit is slow as balls, so we'll try adding
-- an index to the table it is going to abuse lol
CREATE CLUSTERED INDEX GETLASTOCCURRENCES_index ON #GETLASTOCCURRENCES (item,po,ord_date,last_rcpt_date,leadTime)
SELECT *
INTO #LASTOCCURRENCESSTUFFED
FROM
(
SELECT
a.item
,RIGHT(bb.lastPOs,LEN(bb.lastPOs) - 2) AS lastPOs
,RIGHT(cc.lastOrderDates,LEN(cc.lastOrderDates) - 2) AS lastOrderDates
,RIGHT(dd.lastReceiptDates,LEN(dd.lastReceiptDates) - 2) AS lastReceiptDates
,RIGHT(ee.lastLeadTimes,LEN(ee.lastLeadTimes) - 2) AS lastLeadTimes
FROM
(
SELECT DISTINCT item
FROM #GETLASTOCCURRENCES
) AS a CROSS APPLY
(
SELECT DISTINCT
', ' + CONVERT(VARCHAR, b.po, 111)
FROM #GETLASTOCCURRENCES b
WHERE b.item = a.item
FOR XML PATH('')
) bb (lastPOs) CROSS APPLY
(
SELECT DISTINCT
', ' + CONVERT(VARCHAR, c.ord_date, 111)
FROM #GETLASTOCCURRENCES c
WHERE c.item = a.item
FOR XML PATH('')
) cc (lastOrderDates) CROSS APPLY
(
SELECT DISTINCT
', ' + CONVERT(VARCHAR, d.last_rcpt_date, 111)
FROM #GETLASTOCCURRENCES d
WHERE d.item = a.item
FOR XML PATH('')
) dd (lastReceiptDates) CROSS APPLY
(
SELECT DISTINCT
', ' + CAST(e.leadTime AS VARCHAR)
FROM #GETLASTOCCURRENCES e
WHERE e.item = a.item
FOR XML PATH('')
) ee (lastLeadTimes)
) AS lastoccurrencesstuffed
SELECT *
INTO #INFOSUMMARY
FROM
(
SELECT
LO.item
,LO.lastPOs
,LO.lastOrderDates
,LO.lastReceiptDates
,LO.lastLeadTimes
,GA.averageLeadTime
,GA.basedOnXOccurrences
,LOD.last_ord_date
FROM
#LASTOCCURRENCESSTUFFED AS LO INNER JOIN
#GETAVERAGES AS GA ON LO.item = GA.item INNER JOIN
#GETTHELASTORDERDATE AS LOD ON LO.item = LOD.item
) AS infosummary
SELECT *
INTO #JOINITALLUP
FROM
(
SELECT
item
,Calculated_AVG_leadtime
,ERP_AVG_leadtime
,SafestChoice
,OccurrenceCount
,lastPOs
,lastOrderDates
,lastReceiptDates
,lastLeadTimes
,last_ord_date
,CASE
WHEN SafestChoice = 'LAST_3_AVG'
AND INFO.averageLeadTime - WAITEM.avg_lead_time <> 1
AND (1 - (WAITEM.avg_lead_time/INFO.averageLeadTime)) > 0.15
AND DATEADD(MONTH,-12,GETDATE()) < INFO.last_ord_date THEN 1
ELSE 0
END AS specialFilterColumn
FROM
(
SELECT
WAITEM.item
,INFO.averageLeadTime AS [Calculated_AVG_leadtime]
,WAITEM.avg_lead_time AS ERP_AVG_leadtime
,CASE
WHEN INFO.basedOnXOccurrences = 1 THEN 'ERP_AVG'
WHEN INFO.averageLeadTime > WAITEM.avg_lead_time THEN 'LAST_3_AVG'
WHEN INFO.averageLeadTime < WAITEM.avg_lead_time THEN 'ERP_AVG'
WHEN INFO.averageLeadTime IS NULL THEN 'ERP_AVG'
WHEN INFO.averageLeadTime = WAITEM.avg_lead_time THEN 'EQUAL'
ELSE 'USE JUDGEMENT'
END AS SafestChoice
,ISNULL(INFO.basedOnXOccurrences, 0) AS OccurrenceCount
,INFO.lastPOs
,INFO.lastOrderDates
,INFO.lastReceiptDates
,INFO.lastLeadTimes
,INFO.last_ord_date
FROM
(
SELECT
item
,avg_lead_time
FROM
OPENQUERY(ERP_DB, 'SELECT item,avg_lead_time FROM V2.PUB.wa_item WHERE company_it = ''REDACTED'' AND warehouse = ''REDACTED'' AND "min" > 0 AND "max" > 0') AS wa_item
) AS WAITEM LEFT OUTER JOIN
#INFOSUMMARY AS INFO ON WAITEM.item = INFO.ite
) AS blah
) AS joinitallup
-- empty and fill the storage table
TRUNCATE TABLE [REPORTINGSERVER].[dbo].[CalculatedVsERPAverageLeadTimes]
INSERT INTO [REPORTINGSERVER].[dbo].[CalculatedVsERPAverageLeadTimes]
SELECT
item
,Calculated_AVG_leadtime
,ERP_AVG_leadtime
,SafestChoice
,OccurrenceCount
,lastPOs
,lastOrderDates
,lastReceiptDates
,lastLeadTimes
,last_ord_date
,specialFilterColumn
,GETDATE()
FROM #JOINITALLUP AS alldone
END