Good Afternoon All,
I have had a look through the forums and found useful topics for Indented BOM’s. I have created the indented BOM from the epiweb link that people have shared:
I was wondering if there is a way to change this BAQ to allow the search to be aimed at the lower level parts (PartMtl_PartNum) which will then show the BOM working up from the lowest parts?
Kind Regards,
Adam
Check this out. It’s exactly what you describe. It starts with the indented BOM concept but instead joins the tables in such a way that it searches up. Try it out and let me know what you think.
Hi Dan,
Thanks for the quick reply! Sounds like it’s exactly what I need. Unfortunately I get errors trying to import baq files to our version as I think it’s too far behind everyone else… Could you provide me the Query Phrase so I can match it back from my original? I assumed it was just a reverse engineering of the one I have but was working out at which point the search is applied to the part.
Kind Regards,
Adam
What version are you on?
There’s a trick to getting them to import but I don’t recall it. Maybe @jgiese.wci is going to suggest that?
You’re right. If I recall, at it’s most fundamental level, I reversed the join on the recursion so instead of it being MtlPartNum = PartNum, it is PartNum = MtlPartNum… if that makes sense. That would be on the UnionAll Subquery.
10.1.400.19
I get:
Can’t import query definition from version latter than current: 3.2.300.0
Query import finished with error(s)
I assumed our ICE version is too old to import newer BAQ’s.
There is however…
That version is far too old to use the subqueries contained in this BAQ.
I think that BAQ was made in 10.2.300.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
with [SubQuery1] 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],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(0) as [Calculated_Hierarchy],
(case
when PartMtl.MtlSeq < 10 then cast(concat('000', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 100 then cast(concat('00', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 1000 then cast(concat('0', PartMtl.MtlSeq) as nvarchar(25))
else cast(PartMtl.MtlSeq as nvarchar(25))
end) as [Calculated_Ind1],
[PartMtl].[Company] as [PartMtl_Company],
[PartRev2].[Plant] as [PartRev2_Plant],
(cast(1 as decimal)) as [Calculated_ParentQty],
[PartRev2].[AltMethod] as [PartRev2_AltMethod]
from Erp.PartMtl as PartMtl
inner join Erp.PartRev as PartRev2 on
PartMtl.Company = PartRev2.Company
and PartMtl.PartNum = PartRev2.PartNum
and PartMtl.RevisionNum = PartRev2.RevisionNum
and PartMtl.AltMethod = PartRev2.AltMethod
and ( PartRev2.AltMethod = '' )
inner join (select
[PartRev3].[Company] as [PartRev3_Company],
[PartRev3].[PartNum] as [PartRev3_PartNum],
(max(PartRev3.RevisionNum)) as [Calculated_MaxRev]
from Erp.PartRev as PartRev3
where (PartRev3.Approved = true)
group by [PartRev3].[Company],
[PartRev3].[PartNum]) as GetMaxRev on
PartRev2.Company = GetMaxRev.PartRev3_Company
and PartRev2.PartNum = GetMaxRev.PartRev3_PartNum
and PartRev2.RevisionNum = GetMaxRev.Calculated_MaxRev
where (PartMtl.MtlPartNum = @PartNum)
union all
select
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[UOMCode] as [PartMtl1_UOMCode],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(case
when PartMtl1.MtlSeq < 10 then cast(concat(Calculated_Ind1, '-', '000', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 100 then cast(concat(Calculated_Ind1, '-', '00', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 1000 then cast(concat(Calculated_Ind1, '-', '0', PartMtl1.MtlSeq) as nvarchar(25))
else cast(PartMtl1.MtlSeq as nvarchar(25))
end) as [Calculated_Ind2],
[PartMtl1].[Company] as [PartMtl1_Company],
[PartRev].[Plant] as [PartRev_Plant],
(cast(PartMtl_QtyPer as decimal)) as [Calculated_ParentQty],
[SubQuery1].[PartRev2_AltMethod] as [PartRev2_AltMethod]
from SubQuery1 as SubQuery1
inner join Erp.PartMtl as PartMtl1 on
PartMtl1.Company = SubQuery1.PartMtl_Company
and PartMtl1.MtlPartNum = SubQuery1.PartMtl_PartNum
and PartMtl1.AltMethod = SubQuery1.PartRev2_AltMethod
inner join Erp.PartRev as PartRev on
PartMtl1.Company = PartRev.Company
and PartMtl1.PartNum = PartRev.PartNum
and PartMtl1.RevisionNum = PartRev.RevisionNum
and PartMtl1.AltMethod = PartRev.AltMethod
and ( PartRev.AltMethod = '' and PartRev.Approved = true ))
select
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
(SubQuery11.PartMtl_QtyPer * SubQuery11.Calculated_ParentQty) as [Calculated_QtyPer],
[Part].[PartDescription] as [Part_PartDescription],
[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[RunOut] as [Part_RunOut],
(isnull(Usage.Calculated_Qty, 0)) as [Calculated_UsageLY],
(isnull(Forecast1.Calculated_Qty, 0)) as [Calculated_ForecastNY],
(case
when PartPlant.PersonID <> ''
then PartPlant.PersonID
else ProdGrup.PersonID
end) as [Calculated_ProdLine]
from SubQuery1 as SubQuery11
inner join Erp.Part as Part on
SubQuery11.PartMtl_Company = Part.Company
and SubQuery11.PartMtl_PartNum = Part.PartNum
and ( Part.InActive = 0 )
left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(sum(PartTran.TranQty)) as [Calculated_Qty]
from Erp.PartTran as PartTran
where (PartTran.TranDate >= dateadd (year, -1, Constants.Today) and PartTran.TranType in ('MFG-CUS', 'STK-CUS'))
group by [PartTran].[Company],
[PartTran].[PartNum]) as Usage on
Part.Company = Usage.PartTran_Company
and Part.PartNum = Usage.PartTran_PartNum
left outer join (select
[Forecast].[Company] as [Forecast_Company],
[Forecast].[PartNum] as [Forecast_PartNum],
(sum(Forecast.ForeQty)) as [Calculated_Qty]
from Erp.Forecast as Forecast
where (Forecast.ForeDate >= @Today and Forecast.ForeDate <= dateadd (year, 1, Constants.Today))
group by [Forecast].[Company],
[Forecast].[PartNum]) as Forecast1 on
Part.Company = Forecast1.Forecast_Company
and Part.PartNum = Forecast1.Forecast_PartNum
left outer join Erp.ProdGrup as ProdGrup on
Part.Company = ProdGrup.Company
and Part.ProdCode = ProdGrup.ProdCode
inner join Erp.PartPlant as PartPlant on
SubQuery11.PartMtl_PartNum = PartPlant.PartNum
and SubQuery11.PartRev2_Plant = PartPlant.Plant
and SubQuery11.PartMtl_Company = PartPlant.Company
order by SubQuery11.PartMtl_PartNum, SubQuery11.PartMtl_RevisionNumI don’t believe subqueries existed in 10.1 in the capacity they do in 10.2.
@A.Bishop I updated your version number on your profile. It was 10.4.100
Thank you, Good Catch.
I will take a look at Dan’s Phrase and see if I can engineer it myself.
Interesting information regarding sub queries. I have a BAQ that outputs all the BOM information if I put in the top level part so I hope it works at the level needed to reverse the idea.
Thanks for all the help.
Kind Regards,
Adam
I updated the version packages on this copy see if it imports in a testing environment. I doubt it will but might as well try.
3.1.400.0.DMR_WhereUsedTopLevel.baq (204.4 KB)
It has imported successfully!
Very good work! I am getting some odd results but will work through and see why they are showing. Thank you very much for the conversion!
Kind Regards,
Adam
There is a bunch of extra stuff in there you could strip away.
Looks like the one @jgiese.wci provided is missing:
left outer join Erp.ProdGrup as ProdGrup on
Part.Company = ProdGrup.Company
and Part.ProdCode = ProdGrup.ProdCode
inner join Erp.PartPlant as PartPlant on
SubQuery11.PartMtl_PartNum = PartPlant.PartNum
and SubQuery11.PartRev2_Plant = PartPlant.Plant
and SubQuery11.PartMtl_Company = PartPlant.Company
Just working through it now to see why.
There was a QueryDependancy file that I was unable to extract and update. I’m guessing that is where the missing bits are.
I know this was a year ago post but I am seeing if anyone had the same issue with trying to make an indented BOM following these instructions
https://epicweb.epicor.com/Education/OnlineHelpFeatureSummary/Epicor%20ERP%2010/10.2.100/Help/enu/Standard/Tools_BAQs/CaseStudy.CTE.html
I keep getting invalid column name “calculated_hierarchy”
thanks
Hi Jeff,
We didn’t end up using it day to day but this version works for me when looking for all materials etc under a part:
IndentBOM.baq (27.2 KB)
Hope it helps.