I need to pull an exploded bom for jobs completed the previous day. I know the is a cte recursion in the help files but deleting the partnum parameter yields all boms. I’ve tried modifying but it runs extremely slow (10+mins to pull 400 lines). Any ideas?
I think this may be an XY Problem. Do you really need to pull all of the exploded BOMs or do you just need to see what was issued?
1 Like
Are you trying to pull the Engineering Master BOM (PartMtl table), or the Job BOM (JobMtl)?
Job BOM should be fairly simple, and wouldn’t require recursion.
SELECT {columns} from erp.JobMtl
INNER JOIN erp.JobHead ON JobHead.Company = JobMtl.Company AND JobHead.JobNum = JobMtl.JobNum
WHERE JobHead.CompletedDate = @yesterdayJob bom excludes phantom subassemblies and I need to see these.
Just posted this in another thread. It may work for you too?
Why do you need to see if material originated from a phantom? just curious.
We have a project going to realign production standards and we use them on our phantoms. In order to do this we analyze the kanban receipting each day and do an efficiency metric. Gets convoluted when you don’t clock in and out of jobs as you probably know kanban receipts have instant clock in and out times. So the epicor efficiency is always 100%.
That’s a good reason
I’ve got most of this working. I can pull an exploded bom of the kanban receipts however when I try to pull in production stds for the materials I either lose 90 percent of the bom or I get multiple different STDs caused by various unapproved revs. I’m joining partopr to partmtl.mtlpart and rev to rev. Any ideas?
Ok. Got this to work and it runs in 5k ms. However, I can’t get the main part and the material column to union or merge. It times out after 10 mins with no results. Any help is appreciated.
getting the revision thing working if you have multiple approved revisions can be very challenging. you have to find the “latest” revision (or currently active revision if you have future effectivity dates). Also, doing revision searching for each part as an inner sub-query can be very expensive. You might try creating a CTE query that extracts all the parts and the current active revision as your first query, and then use that to explode your BOMs. this might run faster.
1 Like
FYI, this is the exact reason I created the idea for Storing BAQ Subquery Result in a Temp Table. When I created a query in SSMS using a temp table for this problem, I was able to get it to run in under a couple seconds.
@astewart wouldn’t this require the phantom assembly to be noted on each bom or am I missing something?
For your situation, I would add a CTE or two that gets the final assemblies produced yesterday and traverse their BOMs looking only at the latest approved revisions for each part. While recursing down the BOMs, I would add another column to show which final assembly each row originated from.
I can try and do this if it would help, but it would certainly require a temp table or a table variable. So the only way you’d be able to use it would be to either run it outside of the ERP, or you’d have to create a new Table-Valued Function and an External BAQ that used it.
I have what you are describing now I need the originating part to show in the list of assemblies used to make the originating part. This would give me the total time to build.
I can’t find an example where I kept the top level part, but it should be something like this:
with [Base] as
(select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[UOMCode] as [PartMtl_UOMCode],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
[PartRev].[EffectiveDate] as [PartRev_EffectiveDate]
from Erp.PartMtl as PartMtl
inner join Erp.PartRev as PartRev on
PartMtl.Company = PartRev.Company
and PartMtl.PartNum = PartRev.PartNum
and PartMtl.RevisionNum = PartRev.RevisionNum
and PartMtl.AltMethod = PartRev.AltMethod
and ( PartRev.Approved = 1 ))
,[RecursiveBOM] as
(select
(CONVERT(NVARCHAR(MAX), NCHAR(Base.PartMtl_MtlSeq))) as [Calculated_SortStr],
(1) as [Calculated_Level],
[Base].[PartMtl_PartNum] as [Calculated_TopLevelPartNum],
[Base].[PartMtl_PartNum] as [PartMtl_PartNum],
[Base].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[Base].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[Base].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[Base].[PartMtl_QtyPer] as [PartMtl_QtyPer],
((Base.PartMtl_QtyPer)) as [Calculated_ExtQtyPer],
[Base].[PartMtl_UOMCode] as [PartMtl_UOMCode],
[Base].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[Base].[PartRev_EffectiveDate] as [PartRev_EffectiveDate]
from Base as Base
where (Base.PartMtl_PartNum = ltrim(rtrim(@PartNum)))
union all
select
(RecursiveBOM.Calculated_SortStr + NCHAR(RecursiveBase.PartMtl_MtlSeq)) as [Calculated_SortStr],
(RecursiveBOM.Calculated_Level + 1) as [Calculated_Level],
(RecursiveBOM.Calculated_TopLevelPartNum) as [Calculated_TopLevelPartNum],
[RecursiveBase].[PartMtl_PartNum] as [PartMtl_PartNum],
[RecursiveBase].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[RecursiveBase].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[RecursiveBase].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[RecursiveBase].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(CAST(RecursiveBase.PartMtl_QtyPer * RecursiveBOM.Calculated_ExtQtyPer AS DECIMAL(18,8))) as [Calculated_ExtQtyPer],
[RecursiveBase].[PartMtl_UOMCode] as [PartMtl_UOMCode],
[RecursiveBase].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[RecursiveBase].[PartRev_EffectiveDate] as [PartRev_EffectiveDate]
from Base as RecursiveBase
inner join RecursiveBOM as RecursiveBOM on
RecursiveBase.PartMtl_PartNum = RecursiveBOM.PartMtl_MtlPartNum)
SELECT * FROM [RecursiveBOM]
I’ve gotten it to union and it works correctly on 90 percent of the parts however some drop the component parts and just has 1 line for that part. What would cause this? Without the union it has the component parts. With the union it drops them…
Sounds like something is wrong with the join, I would double check that. Can we see that CTE or the whole query?
This is the iteration that works on 90% of the parts. Note I have a plant criteria set.
EarnedVsBurnedCombinedCell3.baq (87.6 KB)
Well it isn’t the join that I thought was wrong, it looks good:
inner join MainAssembly as MainAssembly on
PartMtl1.Company = MainAssembly.PartMtl_Company
and PartMtl1.PartNum = MainAssembly.PartMtl_MtlPartNum
Unfortunately, I think your issue is the MaxRev CTE. Since you’re filtering to the MFG-STK Part Transactions, it’ll only return parts that you’ve received into inventory.
In the base case subquery in your MainAssembly CTE, this is perfectly fine, it’ll return the materials for the assemblies you care about.
But in the recursive part (after the UNION ALL), you’re joining MaxRev to PartMtl again, which will return the same materials for the same assemblies. The problem here is you’re join to the MainAssembly CTE to do the actual recursion. It would only return a row if one of the assemblies received into inventory is in the BOM for one of the other assemblies received into inventory. This didn’t return any rows for me, and I’m guessing it’s not for you either, so right now it’s not doing anything, just making the query take longer.
You could try moving the PartTran join and filtering to the base case subquery in MainAssembly. But for me running this in SSMS, it took a minute and a half, even with a new index on the PartTran table.
I also tried to cache every CTE into temp tables, and was only barely able to bring the time to just under a minute.