~If you want to recursively traverse the BOM you may need a CTE query?~

[Michael_Ramsey]

Hello fellow EpiUsers. I have a request to allow the shop floor “bird-dog” to quickly identify Parts in sub-assemblies for specific jobs that are “Not Completed” yet. Our job structures are identical to the structure described in this older post:

BAQ calculated field to show part number of parent assembly

The main assembly on the job has 6 different operations needed to complete it, one of which is machine shop. under that assembly there are 8 subassemblies, some of which may be assigned to that top level machine shop operation. Some of those have subassemblies under them that may require a machine shop operation, which I have nested under them. Here’s what my structure generally looks like, notice how the first subassembly is not assigned to a specific op(although it will probably end up being …

I would really like to utilize a Dashboard if possible, to allow the user to enter in a Job, and either a Part Number or specific Assembly Sequence, and then produce output that is all children Parts where the individual sub-assembly “final” operation is not marked complete. So basically, the “bird dog” can get in a quick glance, all the Parts remaining to be built to be able to complete a parent sub-assembly. This is an example that I manually filtered just to show what would be desired,

I feel like I could customize the Job Traveler to get pretty close, but I could spend a lot of time just tweaking the layout of the report, when output to screen is just fine. Having looked at this kind of request several months ago, and for the last 24 hours additionally, I do not believe it is a trivial matter to allow “parameterized” selection of parent/children in the middle of a structure, where there are multiple levels. Heck, there could be multiple operations even.

Has anyone ever approached this issue and found a really interesting solution? Would you be able to share? I surely would appreciate any suggestions.

[SteveFossey]

edit I just realized this is in the K21 section - I haven’t looked at that and have no idea if K21 schema is the same. Our query works in 10.2.700./edit

We’ve had to use a recursive query using CTE.

It’s trickier (and slower) to run in a BAQ but it can be done.

Here’s what we do. Replace XXX with whatever is required to identify your revisions (in our case it’s our plant code), and abcdefg with a part number

-- Define CTE

WITH cte_BOM (Family, PartNum, RevNum, EffectiveDate, BOMLevel, QtyPer, UOM)
AS (
	-- Initial query
	SELECT
		CAST(rev.PartNum AS VARCHAR(500)) AS fam,
		rev.PartNum,
		rev.RevisionNum,
		rev.EffectiveDate,
		1 AS BOMLevel,
		CAST(1.0 AS DECIMAL(18,8)) AS QtyPer,
		CAST('EA' AS VARCHAR(6))AS UOM
	FROM (
		SELECT 
			PartNum,
			RevisionNum,
			EffectiveDate
		FROM (
			SELECT
				PartNum,
				RevisionNum,
				EffectiveDate,
				ROW_NUMBER() OVER (PARTITION BY PartNum ORDER BY RevisionNum DESC) as rowNum
			FROM Production.Erp.PartRev
			WHERE RIGHT(RevisionNum,3)='XXX'
			) AS a
		WHERE rowNum=1
		) AS rev
	INNER JOIN (
		SELECT 
			PartNum,
			RevisionNum,
			ROW_NUMBER() OVER (PARTITION BY PartNum, RevisionNum ORDER BY MtlPartNum ASC) as rowNum
		FROM Production.Erp.PartMtl
		) AS mtl
		ON mtl.RevisionNum = rev.RevisionNum
		AND mtl.PartNum = rev.PartNum
		AND mtl.rowNum = 1 INNER JOIN
		Production.dbo.Part ON Rev.PartNum = Part.PartNum
	WHERE Part.PartNum like 'abcdefg' -- Add partnumber to break down

	UNION ALL

	-- Recursive query, based on results of previous iteration 
	-- (loops until no results are found in this query)
	SELECT
		CAST(CONCAT(cte_BOM.Family,' --> ',PartMtl.MtlPartNum) AS VARCHAR(500)),
		PartMtl.MtlPartNum,
		rev.RevisionNum,
		rev.EffectiveDate,
		cte_BOM.BOMLevel + 1,
		CAST(cte_BOM.QtyPer*PartMtl.QtyPer AS DECIMAL(18,8)),
		CAST(PartMtl.UOMCode AS VARCHAR(6))
	FROM cte_BOM
	INNER JOIN Production.Erp.PartMtl
		ON cte_BOM.PartNum = PartMtl.PartNum
	INNER JOIN (
		SELECT 
			PartNum,
			RevisionNum,
			EffectiveDate
		FROM (
			SELECT
				PartNum,
				RevisionNum,
				EffectiveDate,
				ROW_NUMBER() OVER (PARTITION BY PartNum ORDER BY RevisionNum DESC) as rowNum
			FROM Production.Erp.PartRev
			WHERE RIGHT(RevisionNum,3)='XXX'
			) AS a
		WHERE rowNum=1
		) AS rev
		ON PartMtl.RevisionNum = rev.RevisionNum
		AND PartMtl.PartNum = rev.PartNum
	)

-- Return results once recursion is complete
SELECT
	CASE
		WHEN CHARINDEX(' --> ', Family) = 0 THEN Family
		ELSE LEFT(Family, CHARINDEX(' --> ', Family)-1)
	END AS TopLevelPart,
	Family,
	PartNum,
	RevNum,
	EffectiveDate,
	BOMLevel,
	QtyPer,
	UOM
FROM cte_BOM
ORDER BY Family

[Michael_Ramsey]

No worries, it should work the same, and I do have already a working BAQ setup with the CTE as described for part materials or BOM listing. I am trying to branch out from there and into the Job Operations. So I will look for the “final operation” where it is not closed, and then I want to show the Part associated with that Assemblies “still open” job final operation". If that makes sense.

Unfortunately, it looks like “Final Operation” is not an actual database field.

But it is alas! The final operation, if it is identified, is in JobAsmbl.FinalOper

I have plenty of subassembly examples where the operations do not have a final operation identified. That is going to be problematic and will likely change the logic. Again. Sigh.

[Michael_Ramsey]

In case someone may find this example useful, here is the Epicor example version for looking at the Menu structure.
MENU_Full_CTE.baq (40.6 KB)

[jtownsend (John Townsend)]

JobAsmbl.FinalOpr is what you’re looking for.

[Michael_Ramsey]

Unfortunately I’ve too many examples where there is no Final Operation operation identified. I guess I will have to look for another way to identify whether there are still open operations. Something that works when there are multiple operations. An assembly complete flag would be nice.

Ideas?

[jtownsend (John Townsend)]

Cast the opComplete boolean field as an int and compare the sum of completed ops to the count of ops?

[Mark_Wonsil]

Hi @Michael_Ramsey,

Are you talking about already engineered jobs or are you looking for missing assemblies not yet created?

If the former, I would build this from the bottom up:

Create a subQuery that gets all open jobs and links to JobOper. Group by job, assembly and sort by Oper. Select Company, JobNum, Assembly, JobHead.PartNum, and the JobOper.Complete flag (ungrouped, last operation). Now you have a subquery of all the jobs on the floor and the status of the last operation.

Next I would create a new subquery that filters for the open jobs. (You might do it in the first step but it helps me with debugging by keeping them separate and you might use that for other reports that includes closed

Now you have a list of jobs/assys that the last operation is open. From here you can add your filter by job/part and any other content you may want to display.

Just a starting point.

[SteveFossey]

If you’re basing this on incomplete operations, I use the job completion log to identify them once “Auto Job Completion” has run.

(And then I use this query to paste into Excel for DMT, and upload to complete them all. Bit of a hack I know, but finance is more interested in closing them than in finding why they don’t complete (for now) ).

Could you use the job completion log to identify the JobMtl associated with the incomplete op? Or have I completely misunderstood what you’re looking for?

-- If this query returns any results, resolve them and re-run auto job completion before proceeding
declare @JobExceptions table (JobNum varchar(50));

insert into @JobExceptions 

select distinct
	l.JobNum
from
	Production.erp.jobclosinglog l
	
where
	l.AuditSource = 'JobCompletion'
	and l.JobNum not like 'sub%'

select
	'XXXXX' Company,
	h.PartNum,
	'true' OpComplete,
	j.JobNum,
	o.AssemblySeq,
	o.OprSeq,
	o.OpCode,
	'true' ProdComplete,
	'true' SetupComplete,
	h.Plant

	from
	@JobExceptions j
	inner join Production.erp.JobOper o on o.JobNum = j.JobNum and o.OpComplete = '0'
	inner join Production.erp.JobHead h on h.JobNum = j.JobNum and h.JobComplete = '0' and h.JobClosed = '0'

[Michael_Ramsey]

I am talking about already engineered and released. The job is on the shop floor. The goal is to highlight all the subassemblies that are not-Complete.

After encountering the weirdness that was how Epicor handles the “Final Operation” - if Zero, then use the last Operation as final - then I had started down the path of creating one subquery for JobAsmbl + JobOper that shows everything with a non-Zero final operation. Then I added a subquery to JobAsmbl + JobOper for everything where JobAsmbl final operation is Zero. Unfortunately right now that subquery2 finds all the operations and I only want the last one in order to replicate the “final operation” logic. So at that point, I am working out how to do “MAX” or something similar in the BAQ, if it is even possible.

[Michael_Ramsey]

That is an interesting angle. I am not familiar with this table so I could not say up front whether it would work, but I will approach the idea and see.

[Michael_Ramsey]

@jtownsend

Cast the opComplete boolean field as an int and compare the sum of completed ops to the count of ops?

Conceptually I can understand this approach and it certainly makes handling of multiple operations so much easier and it ignores the whole “Final Operation” concept, which is good.

But unfortunately I lack the technical expertise to code it up so it presents a bit of a learning curve.

[jtownsend (John Townsend)]

You can do this in a BAQ subquery too, and if you need I can walk your through that, but basically:

SELECT JobNum
	,SUM(CAST(OpComplete as int)) AS OpsCompleted
	-- You have to cast the OpComplete to zeros and ones, because SQL doesn't let you sum Booleans directly.
	,COUNT(OprSeq) AS TotalOps
	-- I put in COUNT(OprSeq) as the counter for ops, but you could do ROWCOUNT or count any field in the table.
FROM erp.JobOper
WHERE JobComplete = 0
GROUP BY JobNum
HAVING SUM(CAST(OpComplete as int)) = COUNT(OprSeq)
--HAVING is like WHERE but is computed *after* the GROUP BY and lets you compare aggregates like this.

EDITED because I forgot the where clause the first time. Also added notes.

[Michael_Ramsey]

OK, @jtownsend bear with me as I try it the hard way today first.
Everything was going good, trying @Mark_Wonsil concept of start at the bottom and build up.
So I just started with a simpler concept of grabbing from JobAsmbl and JobOper in one subquery to compute the MAX Job Operation for Final Operation…

and using the Calculated Field,

case JobAsmbl.FinalOpr
when 0 then max(JobOper.OprSeq)
else JobAsmbl.FinalOpr
end

He gives me the proper number of Assemblies and shows the proper JobOprCase to check for completion down the road,

Speaking of checking for completion, similar path, use BaseJobs, add the Operation Complete Status for the Final Operation, and output to a top level like so,

So far, so good.

So to make this into a recursive concept I assumed that what I would do is make the BaseJobs return only “parent” assemblies, so any assembly where the parent is Assembly 0 would suffice. The result was a list of the 17 expected assemblies, counting “the parent” 0.
Then I would add the CTE.
Then I would add the Recursing or “Union All” subquery.
Then I would have the structure to put into a TopLevel.

The problem came in with the recursive Union All query. You are probably already aware but this subquery’s Display fields have to line up with the other subqueries and ultimately the TopLevel aligns with them all. Well, since I built my Base or “anchor” table out of 2 queries in order to make 2 hops to grab the Final Operation and then the Status of that Final Operation, I attempted to duplicate that effort and just insert 2 InnerSubqueries which I would then use the one with the Status as input for the recursion. Like so,

But, even though all the Display fields look fine,

So, I wonder if I have applied the UnionAll in the proper order and defined the TopLevel appropriately,

I just wanted to leave an in depth update. Tomorrow I will revisit, try to learn where the UnionAll needs to be, and if that fails I’ll rework it with the calculated field option to try to remove the need for using the InnerSubs and whatnot.
Thanks everyone.

[Mark_Wonsil]

@Michael_Ramsey

So to make this into a recursive concept I assumed that what I would do is make the BaseJobs return only “parent” assemblies, so any assembly where the parent is Assembly 0 would suffice. The result was a list of the 17 expected assemblies, counting “the parent” 0.
Then I would add the CTE.

Do you really need it?

Now you can link this table to JobHead and you should have the top level and each assembly with its job status. Is that not what you want?

[Michael_Ramsey]

@Mark_Wonsil thank you Mark. Ultimately I want to include “Parent Part Number” as a parameter and I would want the output to show all of the “Children” assemblies. So, I assumed I would need the recursive query to step in, in order to pick up the generations of “child assemblies”. Do you know what I mean?

For example, in my output where it shows that assembly 18, the user might enter the Asm Part there. And the BAQ would be expected to recurse through the “children” returning only those records.

[Mark_Wonsil]

Gotcha. You have subassemblies of subassemblies. Now I understand. How deep can they get?

[Michael_Ramsey]

I haven’t run across more than just a handful of levels in total maybe 4-5.

[Michael_Ramsey]

@jtownsend thank you!

There’s a lot going on in this thread!
Looks like I learned something new today, and the day is still young!

[Michael_Ramsey]

@jtownsend I thought this approach had a great shot at working, but something isn’t quite tying down right as soon as the “recursive” subquery is added.
JOBOPS_CntOpsRecursion_HWM.baq (60.8 KB)
JOBOPS_CntOps_HWM.baq (45.3 KB)
This piece works fine. As soon as I add the “UnionAll” I start getting SQL Errors.