/*CABERLE - Script to aid in determining at which level / Sublevel the most time is being spent at while MRP is still running*/
/*Step 2.1.1 - Identiry the ProcessID in question*/
--select * from ReqProcessList
/*Step 2.2.2 - Edit Query with @ProcessId variable from info from step 2.1.1*/
/*Replace 'XXXXXXXXXX' with the ReqProcess that is currently running that you want to investigate*/
/*Edit Filter in where clause to Processing Only to see what is currently running and time it has been running*/
/*Edit Filter by Processing & Ended with the Duration filter to see longest tasks without all the short duration noise*/
declare @ProcessId varchar(20)
set @ProcessId = 'XXXXXXXXXX'
declare @BeginTime as datetime
select @BeginTime = min(Starttime) from ReqCalcTasksBundle where Status in (10,30) and PROCESSID = @ProcessId
select case ProcessingState when 0 then 'Waiting' when 1 then 'Processing' when 2 then 'Ended' end as ProcessingStateText,
--Status as StatusNumber,
case a.STATUS
when 10 then 'Starting'
when 30 then 'Initializing'
when 35 then 'RecalculateItemLevels'
when 39 then 'PreUpdate'
when 40 then 'Update'
when 42 then 'InsertInterCompanyDemand'
when 43 then 'InsertRequisitionsDemand'
when 45 then 'PostUpdate'
when 59 then 'Precoverage'
when 60 then 'Coverage'
when 61 then 'PostCoverage'
when 69 then 'PreFutures'
when 70 then 'Futures'
when 79 then 'PreAction'
when 80 then 'Actions'
when 89 then 'PreAutoCoverage'
when 90 then 'AutoCoverage'
when 95then 'DEL_UpdatingItemLevel'
when 100 then 'InsertingLog'
when 110 then 'UpdatingDynamicPlan'
when 120 then 'AutoFirming'
when 125 then 'Statistics'
when 130 then 'Ended'
when 240 then 'Error'
end as StatusText, a.Level_ as BOMLevel,
--LEVELSTATE as LevelStateNumber,
case LEVELSTATE
when 0 then 'NotApplicable'
when 1 then 'InitLevel'
when 10 then 'Coverage'
when 13 then 'CoCoverage'
when 11 then 'FuturesReceipts'
when 12 then 'Actions'
when 20 then 'PartitionOrders'
when 30 then 'ScheduleResources'
when 40 then 'FinishedShcduling'
when 41 then 'FuturesIssues'
when 50 then 'FinalizeLevel'
end as LevelStateText,
case PROCESSINGSTATE when 0 then 0
when 1 then datediff(second, starttime, getutcdate())
when 2 then datediff(second, starttime, endtime)
end as DurationInSeconds,
case PROCESSINGSTATE when 0 then 0
when 1 then datediff(minute, starttime, getutcdate())
when 2 then datediff(minute, starttime, endtime)
end as DurationInMinutes,
LISTNUM,
b.ITEMID,
--c.REFID,
a.ProcessDataAreaid,
a.ProcessID,
Threadid,
STARTTIME,
EndTime,
case PROCESSINGSTATE when 0 then 0
when 1 then datediff(minute, @BeginTime, getutcdate())
when 2 then datediff(minute, @BeginTime, endtime)
end as MinutesSinceMRPStart
from ReqCalcTasksBundle a
left outer join ReqCalcTask b
on /*a.PARTITION = c.PARTITION and*/ a.PROCESSDATAAREAID = b.PROCESSDATAAREAID and a.RECID = b.BUNDLE
/*left outer join REQUNSCHEDULEDORDERSTrace c
on /*a.PARTITION = c.PARTITION and*/ a.PROCESSDATAAREAID = c.DATAAREAID and a.PROCESSID = c.PROCESSID and a.LEVEL_ = c.LEVEL_ and a.LISTNUM = c.ENGINEBUNDLE */
where a.PROCESSID = @ProcessId
/*Filter by 0=Waiting, 1=Processing, 2=Ended*/
and ProcessingState in (0,1,2)
/*Use this to filter tasks greater than 10 seconds that were Processing or Ended*/
--and case PROCESSINGSTATE when 0 then 0 when 1 then datediff(second, starttime, getutcdate()) when 2 then datediff(second, starttime, endtime) end >= 10
/*Use this to filter by Scheduling for example*/
--and levelstate = 30
order by ProcessingState desc, STARTTIME, BOMLevel, ListNUM
