Correct process for making resource inactive

We have resources setup in resource groups. This is used to do very basic capacity calculations (number of resources in resource group x production calendar hours). Within a resource group, the resources may have different capabilities but we do not specify the resource on the route, when we schedule the job, it just takes the first resource in the list and adds all of the load onto that.

We have retired some shop floor equipment so in our test environment I deactivated the relevant resources, which is some cases was the first resource in the list, to reflect this. I tested booking labour through and in the affected operations I started to get error messages about inactive resources which would require the operator to chose an alternative resource to run the job on, which is a decision too many for our shop floor operators.

I quickly worked out what was going on and if I go to job entry | Job Details | Operations | Scheduling Resources | Scheduling Resources I can see the inactive resource is specified for an operation. Using field help and I can see the epibinding is to JobResources - a table that does not exist and does not seem to be queryable.

I can find no other way of querying which jobs have been scheduled to run on this inactive resource (not in jobop, jobopdtl etc) and the only fix I can find is to manually reschedule every single job (global reschedule didn’t do anything), which moves the load onto the next active resource in the resource group. As we have 100’s of jobs open at any given time, I’m struggling to see how I can find a window to make a resource inactive, without creating a ton of manual work.

What I would hope to find is a way of finding which jobs are scheduled on inactive resources and also the best means of quickly updating this via DMT.

Alternatively if there is a robust process for making a resource inactive and rescheduling the jobs that use that inactive resource on an active resource then I would appreciate knowing how to do this.

In E9, I have a sql query that joins JobOpDtl to ResourceTimeUsed and checks when they don’t match. We move our inactive resources weekly to another resource group since Epicor will still count an inactive resource as it could potentially be reactivated.

Then depending on the amount found either DMT or have planners change. We are infinite scheduled and our resources are generic and I noticed that scheduling would sometime use the same resource twice on a job, so I reassign the inactive resource to 01 and it works for us.

Hopefully this will get you started.

Greg

USE Epicor905
SELECT TOP 1000 
	JobOper.JobNum	
	,JobHead.ShortChar04
	,JobOper.OprSeq
	,JobOper.OpCode
	,JobOpDtl.ResourceGrpID
	,JobOpDtl.OpDtlDesc
	,ResourceTimeUsed.ResourceGrpID
	,ResourceTimeUsed.ResourceID
	,case when SUBSTRING(JobOpDtl.ResourceGrpID,1,3) ='ENG' Then Upper(substring(JobOpDtl.ResourceGrpID,1,6)) + 'T' + 'M01' 
			when SUBSTRING(JobOpDtl.ResourceGrpID,1,4) ='MOLD' Then 'MOLDM01' 
		  when SUBSTRING(JobOpDtl.ResourceGrpID,1,4) ='OCUT' Then 'OEMCUTM01' 
			else Upper(substring(JobOpDtl.ResourceGrpID,1,6)) + 'M01' 
			end as NewResource

FROM JobOper
INNER JOIN JobOpDtl ON JobOper.Company = JobOpDtl.Company
	AND JobOper.JobNum = JobOpDtl.JobNum
	AND JobOper.AssemblySeq = JobOpDtl.AssemblySeq
	AND JobOper.OprSeq = JobOpDtl.OprSeq
INNER JOIN ResourceTimeUsed ON ResourceTimeUsed.Company = JobOpDtl.Company
	AND ResourceTimeUsed.JobNum = JobOpDtl.JobNum
	AND ResourceTimeUsed.AssemblySeq = JobOpDtl.AssemblySeq
	AND ResourceTimeUsed.OprSeq = JobOpDtl.OprSeq
	AND ResourceTimeUsed.OpDtlSeq = JobOpDtl.OpDtlSeq
	AND ResourceTimeUsed.WhatIf=0
INNER JOIN JobHead ON JobOper.Company = JobHead.Company
	AND JobOper.JobNum = JobHead.JobNum


where substring(JobOpDtl.ResourceGrpID,1,6) <> substring(ResourceTimeUsed.ResourceGrpID,1,6)
 	

Greg,

Thanks that is really helpful. You mention that you then do an update using the DMT - which table/template is it that you update - resourcetimeused?

I update resourcetimeused, but it is not a DMT table so you have to send in the data with a UD and use a bpm to update resourcetimeused.