Looking to make a BAQ/Dashboard for shop supervisors to have a better understanding of what they should be working on.
We are a custom job shop and many of our jobs include sub-assemblies.
Usually the header level assembly (0) has 2 operations (10 &20)- The first operation is tear down mechanical work. The second operation is put it back together mechanical work.
In-between operation 10 and 20, we work on the sub-assemblies. Most of these sub-assemblies can be worked on in sync.
I have gotten pretty far with the previous operation complete but still having trouble with that Asm 0 op 20 showing ready to work before all sub-assemblies are complete.
Any Ideas?
Show us what you have so far. I bet we can find a solution. My first step would be to try out the Priority Dispatch Report to see if that canned report covers what you need.
This is what I have so far. It works for 70% of what we need.
There is a calculated material status, previous operation complete, and then a ready to work which looks at both fields and tells the supervisors what they should be working on.
select
[JobOper].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
[JobHead].[GenDesc_c] as [JobHead_GenDesc_c],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[PurchaseDirect].[Calculated_MaterialStatus] as [Calculated_MaterialStatus01],
(Case
When PrevsubOpCmpt = 1 AND MaterialStatus = âAll Mtl. Receivedâ then 1
when PrevsubOpCmpt = 1 AND MaterialStatus = âNo Material Requiredâ then 1
else 0 end) as [Calculated_ReadyToWork],
((case when (((select distinct top (1)
[JobOper1].[OpComplete] as [JobOper1_OpComplete]
from Erp.JobOper as JobOper1
where (JobOper1.Company = JobOper.Company and JobOper1.JobNum = JobOper.JobNum and JobOper1.AssemblySeq = JobOper.AssemblySeq and JobOper1.OprSeq < JobOper.OprSeq)))) is null then 1 else ((select distinct top (1)
[JobOper1].[OpComplete] as [JobOper1_OpComplete]
from Erp.JobOper as JobOper1
where (JobOper1.Company = JobOper.Company and JobOper1.JobNum = JobOper.JobNum and JobOper1.AssemblySeq = JobOper.AssemblySeq and JobOper1.OprSeq < JobOper.OprSeq))) end)) as [Calculated_PrevSubOpCmpt],
(CASE
WHEN PurchaseDirect.Calculated_MaterialStatus = 1 THEN âPO Requiredâ
WHEN PurchaseDirect.Calculated_MaterialStatus = 2 THEN âMtl. On Orderâ
WHEN PurchaseDirect.Calculated_MaterialStatus = 3 THEN âAll Mtl. Receivedâ
WHEN PurchaseDirect.Calculated_MaterialStatus = 4 THEN âShortageâ
ELSE âNo Material Requiredâ END) as [Calculated_MaterialStatus],
[JobHead].[StartDate] as [JobHead_StartDate],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
(JobHead.Expedited_c) as [Calculated_Expedited],
[Customer].[Name] as [Customer_Name],
[JobOper].[StartDate] as [JobOper_StartDate],
[JobOper].[DueDate] as [JobOper_DueDate],
[JobOpDtl].[ProdStandard] as [JobOpDtl_ProdStandard],
[ResourceTimeUsed].[ResourceGrpID] as [ResourceTimeUsed_ResourceGrpID],
[ResourceGroup].[Description] as [ResourceGroup_Description],
[ResourceTimeUsed].[ResourceID] as [ResourceTimeUsed_ResourceID],
[Resource].[Description] as [Resource_Description],
[ResourceGroup].[JCDept] as [ResourceGroup_JCDept],
[Resource].[ResourceType] as [Resource_ResourceType]
from Erp.JobHead as JobHead
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
and ( JobOper.Company = @CurrentCompany and JobOper.OpComplete <> true )
inner join Erp.JobOpDtl as JobOpDtl on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
inner join Erp.ResourceTimeUsed as ResourceTimeUsed on
JobOpDtl.Company = ResourceTimeUsed.Company
and JobOpDtl.JobNum = ResourceTimeUsed.JobNum
and JobOpDtl.AssemblySeq = ResourceTimeUsed.AssemblySeq
and JobOpDtl.OprSeq = ResourceTimeUsed.OprSeq
and JobOpDtl.OpDtlSeq = ResourceTimeUsed.OpDtlSeq
inner join Erp.Resource as Resource on
ResourceTimeUsed.Company = Resource.Company
and ResourceTimeUsed.ResourceGrpID = Resource.ResourceGrpID
and ResourceTimeUsed.ResourceID = Resource.ResourceID
and ( Resource.ResourceGrpID = @Resource_Group and Resource.ResourceID = @Resource )
inner join Erp.ResourceGroup as ResourceGroup on
Resource.Company = ResourceGroup.Company
and Resource.ResourceGrpID = ResourceGroup.ResourceGrpID
inner join Erp.JobAsmbl as JobAsmbl on
JobOpDtl.Company = JobAsmbl.Company
and JobOpDtl.JobNum = JobAsmbl.JobNum
and JobOpDtl.AssemblySeq = JobAsmbl.AssemblySeq
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
left outer join Erp.OrderHed as OrderHed on
JobProd.Company = OrderHed.Company
and JobProd.OrderNum = OrderHed.OrderNum
left outer join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
left outer join (select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
(MIN(CASE
WHEN PORel.PONum IS NULL THEN 1
WHEN PORel.OpenRelease = âtrueâ THEN 2
WHEN JobMtl.IssuedComplete = âtrueâ THEN 3
WHEN JobMtl.IssuedComplete = âfalseâ THEN 4
when JobMtl.PartNum = âMOBILIZATIONâ THEN 5
When JobMtl.PartNum = âPERDIEMâ then 5
when JobMtl.PartNum = âMACHINERENTALâ then 5
when JobMtl.PartNum = âSUPPLYMATERIALâ then 5
when JobMtl.PartNum = âENGINEERINGâ then 5
ELSE 5 END
)) as [Calculated_MaterialStatus]
from Erp.JobMtl as JobMtl
inner join Erp.PORel as PORel on
JobMtl.Company = PORel.Company
and JobMtl.JobNum = PORel.JobNum
and JobMtl.AssemblySeq = PORel.AssemblySeq
and JobMtl.MtlSeq = PORel.JobSeq
and ( PORel.JobSeqType = âMâ )
where (JobMtl.BuyIt = TRUE)
group by [JobMtl].[Company],
[JobMtl].[JobNum]) as PurchaseDirect on
JobHead.Company = PurchaseDirect.JobMtl_Company
and JobHead.JobNum = PurchaseDirect.JobMtl_JobNum
where (JobHead.Company = @CurrentCompany and JobHead.JobClosed = false and JobHead.JobReleased = true and JobHead.JobEngineered = true and JobHead.JobNum = â302545-1-1â)
group by [JobOper].[Company],
[JobOper].[JobNum],
[JobHead].[GenDesc_c],
[JobOper].[AssemblySeq],
[JobOper].[OprSeq],
[PurchaseDirect].[Calculated_MaterialStatus],
[JobHead].[StartDate],
[JobHead].[DueDate],
[JobHead].[ReqDueDate],
(JobHead.Expedited_c),
[Customer].[Name],
[JobOper].[StartDate],
[JobOper].[DueDate],
[JobOpDtl].[ProdStandard],
[ResourceTimeUsed].[ResourceGrpID],
[ResourceGroup].[Description],
[ResourceTimeUsed].[ResourceID],
[Resource].[Description],
[ResourceGroup].[JCDept],
[Resource].[ResourceType]
Hopefully this helps in terms of a visualisation, we redeveloped our E10 dashboard when upgrading to Kinetic.
2 Likes
This might be better addressed by changing the way you create your Jobs. Instead of having the tear down on the 0 level, maybe the lowest assembly can have the tear down operation and the 0 level only have the put back together operation. That way you could use the Work Queue to see what needs to be worked on next.
I replicated the Work Queue in a dashboard at my old job. I used a MIN on the assembly table to find the lowest operation that was not complete and marked them with a 0. All other operations were marked with a 1. I could then filter the dashboard for what could currently be worked on and what was ânextâ.
1 Like
There is a related operation field on the job assembly. Try entering op 20 as the related operation on your assemblies. If the related operation on your assemblies is 0, Epicorâs scheduling logic assumes the subassembly can be started right away, but you want to start with Asm 0 op 10, then work on the subassemblies, then Asm 0 op 20. I believe with the related operation set to 20, Epicor would see that the assemblies need to be finished before op 20 can start. You could see if it reflects that way in the Epicor schedule, and then if so, you could use Work Queue or build that logic into your dashboard.
1 Like
After reviewing, the work queue is exactly what we need. I think doing a dashboard with the work queue BAQ will work for us.
What is the query would classify an operation to be in the âcurrentâ section as opposed to the âallâ section.
Running the query I have âallâ but looks like current would be exactly what we are needing just not sure how the filters are determining that.
I really like this option as well. Moving forward, we are planning to use this when engineering our jobs.