It doesn’t stop for me if it’s a phantom. Can you explain a little more what you are seeing?
Hi guys,
I don’t want to be left out so here is my example , but i am going thru quote material records. Its been a while since i don’t run this, you will notice that I have to sort base on OrderNum,OrderLine,Release,Mtl Sequence, and Parent Material seq to keep it in the right order. In Addition I dont think I have a limit of how many levels I go deep, but again in our company we don’t have a lot of levels. Also If you put this into a dashboard I recommend to filter base on a sales order and line.
Reading your comments I did also encounter issues with Material revisions and couldn’t make it to work to find the most recently.
Here is the logic for the indented field (DisplayPartNumber)
(case when not SubQuery11.Calculated_MtlPartNum is null and len(SubQuery11.Calculated_MtlPartNum) > 0 then
Replace(SubQuery11.Calculated_QuoteMaterial,SUBSTRING(SubQuery11.Calculated_QuoteMaterial,1,CHARINDEX ( SubQuery11.Calculated_MtlPartNum , SubQuery11.Calculated_QuoteMaterial,1) - 1), Replicate( ’ ', Len(SUBSTRING(SubQuery11.Calculated_QuoteMaterial,1,CHARINDEX ( SubQuery11.Calculated_MtlPartNum ,SubQuery11.Calculated_QuoteMaterial,1) - 1))) )
else
SubQuery11.QuoteMtl_PartNum end)
Hope it helps
ETK-BOMReviewCosts.baq (210.8 KB)
This is my latest and greatest BOM with costs.
The BAQ gets the latest approved rev of the child part on the BOM, as it revision number.
This was missing on the last BAQ. The BAQ from the dashboard forced the child parts revision to be that of the parents revision number. This could cause the looping to stop as there is no Parent Part Rev in the system.
select
[ChildPartMtl].[PartNum] as [ChildPartMtl_PartNum],
[ChildPartMtl].[RevisionNum] as [ChildPartMtl_RevisionNum],
[MaterialCTE1].[Part_PartDescription] as [Part_PartDescription],
[ChildPartMtl].[MtlPartNum] as [ChildPartMtl_MtlPartNum],
**[MaterialCTE1].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],**
[ChildPartMtl].[MtlSeq] as [ChildPartMtl_MtlSeq],
(MaterialCTE1.Calculated_Level + 1) as [Calculated_Level2],
(Cast(MaterialCTE1.Calculated_Path + '.' as nvarchar(255))) as [Calculated_Path2],
(cast(MaterialCTE1.Calculated_LSeq + '\' + convert(varchar,ChildPartMtl.PartNum) + '\' + (RIGHT('00000' + CAST(ChildPartMtl.MtlSeq AS NVARCHAR(5)), 5 )) as nvarchar(255))) as [Calculated_LevSeq2],
[MaterialCTE1].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(cast(ChildPartMtl.QtyPer as decimal(10,1))) as [Calculated_MLQty]
from Erp.PartMtl as ChildPartMtl
inner join MaterialCTE as MaterialCTE1 on
ChildPartMtl.PartNum = MaterialCTE1.PartMtl_MtlPartNum
and ChildPartMtl.RevisionNum = MaterialCTE1.Calculated_Rev
where (ChildPartMtl.PartNum is not null))
1 Like
Are you able to follow the BAQ I posted? The data you are showing is not based on the BOM is it? How are you connecting the quote material to the BOM?
I thought once the material is put to a quote you are using a different table (not PartMtl) Can you share you whole query phrase? Might be a good idea to start a new thread with just this issue, so it doesn’t get lost.
@knash Here you go, I will have to refresh my memory cause its little bit dusty ,its been a while since i wrote it. What i am doing is to get the bom from quote material make an union to PartMtl, then calling Part Material and link it to my CTE query. Maybe is not the most efficient way of doing it but hell it gives me part of what i need even though I haven’t figure out how to get the last revision for the parts in the bom.
I will run the query as soon as I can been busy lately.
With [SubQuery1] AS
(select
[QuoteMtl].[Company] as [QuoteMtl_Company],
[QuoteMtl].[PartNum] as [QuoteMtl_PartNum],
(‘’) as [Calculated_MtlPartNum],
(Cast(QuoteMtl.QtyPer as decimal (10,2))) as [Calculated_QtyPer],
(’ ') as [Calculated_RevisionNumber],
(0) as [Calculated_QuoteMaterialSeq],
(0) as [Calculated_PartMtlSeq],
(-1) as [Calculated_PartLevel],
[QuoteMtl].[QuoteNum] as [QuoteMtl_QuoteNum],
[QuoteMtl].[QuoteLine] as [QuoteMtl_QuoteLine],
[QuoteMtl].[MtlSeq] as [QuoteMtl_MtlSeq],
(QuoteMtl.PartNum) as [Calculated_QuoteMaterial],
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
[OrderDtl1].[OrderLine] as [OrderDtl1_OrderLine],
[QuoteMtl].[FixedQty] as [QuoteMtl_FixedQty],
[OrderDtl1].[SalesCatID] as [OrderDtl1_SalesCatID]
from Erp.QuoteMtl as QuoteMtl
inner join Erp.OrderDtl as OrderDtl1 on
OrderDtl1.Company = QuoteMtl.Company
And
OrderDtl1.QuoteNum = QuoteMtl.QuoteNum
And
OrderDtl1.QuoteLine = QuoteMtl.QuoteLine
and ( OrderDtl1.OpenLine = 1 )UNION ALL
select
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
((case when QuoteMtl1.FixedQty = 1 THEN
cast(QuoteMtl1.QtyPer as decimal (10,2))
ELSE
Cast(PartMtl.QtyPer * QuoteMtl1.QtyPer as decimal (10,2) )
end)) as [Calculated_PartMaterialReqQty],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[ParentMtlSeq] as [PartMtl_ParentMtlSeq],
(0) as [Calculated_PartLevelMaterial],
[QuoteMtl1].[QuoteNum] as [QuoteMtl1_QuoteNum],
[QuoteMtl1].[QuoteLine] as [QuoteMtl1_QuoteLine],
[QuoteMtl1].[MtlSeq] as [QuoteMtl1_MtlSeq],
(cast(concat( cast(QuoteMtl1.PartNum AS NVARCHAR(255) ) , ‘/’, cast(PartMtl.MtlPartNum AS NVARCHAR(255)) ) AS NVARCHAR(255 ))) as [Calculated_QuotePartMaterial],
[OrderDtl2].[OrderNum] as [OrderDtl2_OrderNum],
[OrderDtl2].[OrderLine] as [OrderDtl2_OrderLine],
[QuoteMtl1].[FixedQty] as [QuoteMtl1_FixedQty],
[OrderDtl2].[SalesCatID] as [OrderDtl2_SalesCatID]
from Erp.PartMtl as PartMtl
inner join Erp.QuoteMtl as QuoteMtl1 on
QuoteMtl1.Company = PartMtl.Company
And
QuoteMtl1.PartNum = PartMtl.PartNuminner join Erp.OrderDtl as OrderDtl2 on
OrderDtl2.Company = QuoteMtl1.Company
And
OrderDtl2.QuoteNum = QuoteMtl1.QuoteNum
And
OrderDtl2.QuoteLine = QuoteMtl1.QuoteLine
and ( OrderDtl2.OpenLine = 1 )UNION ALL
select
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
(Cast(SubQuery1.Calculated_QtyPer * PartMtl1.QtyPer as decimal(10,2))) as [Calculated_RecursiveMaterialQty],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[ParentMtlSeq] as [PartMtl1_ParentMtlSeq],
(SubQuery1.Calculated_PartLevel + 1) as [Calculated_RecursivePartLevel],
[SubQuery1].[QuoteMtl_QuoteNum] as [QuoteMtl_QuoteNum],
[SubQuery1].[QuoteMtl_QuoteLine] as [QuoteMtl_QuoteLine],
[SubQuery1].[QuoteMtl_MtlSeq] as [QuoteMtl_MtlSeq],
(cast(concat(cast(SubQuery1.Calculated_QuoteMaterial AS nVARCHAR(255)) , ‘/’, cast( PartMtl1.MtlPartNum AS nVARCHAR(255)) ) AS nVARCHAR(255))) as [Calculated_RecursivePartMaterial],
[SubQuery1].[OrderDtl1_OrderNum] as [OrderDtl1_OrderNum],
[SubQuery1].[OrderDtl1_OrderLine] as [OrderDtl1_OrderLine],
[SubQuery1].[QuoteMtl_FixedQty] as [QuoteMtl_FixedQty],
[SubQuery1].[OrderDtl1_SalesCatID] as [OrderDtl1_SalesCatID]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
SubQuery1.QuoteMtl_Company = PartMtl1.Company
And
SubQuery1.Calculated_MtlPartNum = PartMtl1.PartNum
And
SubQuery1.Calculated_RevisionNumber = PartMtl1.RevisionNum)select
[SubQuery11].[QuoteMtl_QuoteNum] as [QuoteMtl_QuoteNum],
[SubQuery11].[QuoteMtl_QuoteLine] as [QuoteMtl_QuoteLine],
[SubQuery11].[QuoteMtl_MtlSeq] as [QuoteMtl_MtlSeq],
[SubQuery11].[QuoteMtl_PartNum] as [QuoteMtl_PartNum],
[SubQuery11].[Calculated_QtyPer] as [Calculated_QtyPer],
[SubQuery11].[Calculated_PartLevel] as [Calculated_PartLevel],
[SubQuery11].[Calculated_RevisionNumber] as [Calculated_RevisionNumber],
[SubQuery11].[Calculated_MtlPartNum] as [Calculated_MtlPartNum],
[SubQuery11].[Calculated_QuoteMaterialSeq] as [Calculated_QuoteMaterialSeq],
[SubQuery11].[Calculated_QuoteMaterial] as [Calculated_QuoteMaterial],
[PartMtl2].[MtlPartNum] as [PartMtl2_MtlPartNum],
[PartMtl2].[MtlSeq] as [PartMtl2_MtlSeq],
(case when not SubQuery11.Calculated_MtlPartNum is null and SubQuery11.Calculated_MtlPartNum <> ‘’ then
SubQuery11.Calculated_MtlPartNum
else
SubQuery11.QuoteMtl_PartNum end) as [Calculated_RequiredMaterial],
((case when not SubQuery11.Calculated_MtlPartNum is null and len(SubQuery11.Calculated_MtlPartNum) > 0 then
Replace(SubQuery11.Calculated_QuoteMaterial,SUBSTRING(SubQuery11.Calculated_QuoteMaterial,1,CHARINDEX ( SubQuery11.Calculated_MtlPartNum , SubQuery11.Calculated_QuoteMaterial,1) - 1), Replicate( ’ ', Len(SUBSTRING(SubQuery11.Calculated_QuoteMaterial,1,CHARINDEX ( SubQuery11.Calculated_MtlPartNum ,SubQuery11.Calculated_QuoteMaterial,1) - 1))) )
else
SubQuery11.QuoteMtl_PartNum end)) as [Calculated_DisplayPartNumber],
[Part].[PartDescription] as [Part_PartDescription],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderRel].[ReqDate] as [OrderRel_ReqDate],
[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[Part].[TypeCode] as [Part_TypeCode],
[OrderDtl].[SalesCatID] as [OrderDtl_SalesCatID],
[SubQuery11].[OrderDtl1_OrderNum] as [OrderDtl1_OrderNum],
[SubQuery11].[OrderDtl1_OrderLine] as [OrderDtl1_OrderLine],
[SalesCat].[Description] as [SalesCat_Description],
[SubQuery6].[PODetail_PONUM] as [PODetail_PONUM],
[SubQuery6].[PODetail_POLine] as [PODetail_POLine],
[SubQuery6].[PORel_DueDate] as [PORel_DueDate],
[SubQuery6].[PORel_PromiseDt] as [PORel_PromiseDt],
[SubQuery6].[Calculated_OpenPOQty] as [Calculated_OpenPOQty],
[SubQuery8].[Calculated_TotalQtyDemand] as [Calculated_TotalQtyDemand],
[SubQuery8].[Calculated_TotalQtyOnHand] as [Calculated_TotalQtyOnHand],
((case when SubQuery11.QuoteMtl_FixedQty = 1 then
SubQuery11.Calculated_QtyPer
else
OrderRel.SellingReqQty * SubQuery11.Calculated_QtyPer
end)) as [Calculated_MtlRequiredQty],
[JobProd].[JobNum] as [JobProd_JobNum],
(SubQuery8.Calculated_TotalQtyDemand - (case when JobProd.JobNum is null then 0 else MtlRequiredQty end)) as [Calculated_ActualDemand],
(SubQuery8.Calculated_TotalQtyOnHand - SubQuery8.Calculated_TotalQtyDemand) as [Calculated_Available],
((case when MtlRequiredQty <= SubQuery8.Calculated_TotalQtyOnHand - ActualDemand then
0
when SubQuery8.Calculated_TotalQtyOnHand - ActualDemand > 0 then
SubQuery8.Calculated_TotalQtyOnHand - ActualDemand
else
MtlRequiredQty
end
)) as [Calculated_MissingQty]
from SubQuery1 as SubQuery11
left outer join Erp.PartMtl as PartMtl2 on
PartMtl2.Company = SubQuery11.QuoteMtl_Company
And
PartMtl2.PartNum = SubQuery11.Calculated_MtlPartNum
And
PartMtl2.RevisionNum = SubQuery11.Calculated_RevisionNumberleft outer join Erp.Part as Part on
Part.Company = SubQuery11.QuoteMtl_Company
And
Part.PartNum = (case when not SubQuery11.Calculated_MtlPartNum is null and SubQuery11.Calculated_MtlPartNum <> ‘’ then SubQuery11.Calculated_MtlPartNum else SubQuery11.QuoteMtl_PartNum end)inner join Erp.OrderDtl as OrderDtl on
OrderDtl.Company = SubQuery11.QuoteMtl_Company
And
OrderDtl.OrderNum = SubQuery11.OrderDtl1_OrderNum
And
OrderDtl.OrderLine = SubQuery11.OrderDtl1_OrderLine
and ( OrderDtl.OpenLine = 1 )inner join Erp.OrderHed as OrderHed on
OrderDtl.Company = OrderHed.Company
And
OrderDtl.OrderNum = OrderHed.OrderNum
and ( OrderHed.OpenOrder = 1 )inner join Erp.OrderRel as OrderRel on
OrderRel.Company = OrderDtl.Company
And
OrderRel.OrderNum = OrderDtl.OrderNum
And
OrderRel.OrderLine = OrderDtl.OrderLine
and ( OrderRel.OrderNum = 77225 and OrderRel.OrderLine = 1 )left outer join Erp.SalesCat as SalesCat on
OrderDtl.Company = SalesCat.Company
And
OrderDtl.SalesCatID = SalesCat.SalesCatIDleft outer join (select
[PODetail].[Company] as [PODetail_Company],
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[POLine] as [PODetail_POLine],
[PODetail].[PartNum] as [PODetail_PartNum],
[PORel].[DueDate] as [PORel_DueDate],
[PORel].[PromiseDt] as [PORel_PromiseDt],
[PORel].[XRelQty] as [PORel_XRelQty],
[PORel].[ReceivedQty] as [PORel_ReceivedQty],
(PORel.XRelQty - PORel.ReceivedQty) as [Calculated_OpenPOQty]
from Erp.PODetail as PODetail
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
And
PODetail.PONUM = PORel.PONum
And
PODetail.POLine = PORel.POLine
and ( PORel.OpenRelease = 1 )where (PODetail.OpenLine = 1)
and PODetail.PONUM = (select Calculated_FirstPONum from ((select
[PODetail1].[Company] as [PODetail1_Company],
[PODetail1].[PartNum] as [PODetail1_PartNum],
(Min(PORel1.PONUM)) as [Calculated_FirstPONum],
(Min(PORel1.POLine)) as [Calculated_FirstPOLine],
(Min(PORel1.PORelNum)) as [Calculated_FirstPORelNum]
from Erp.PODetail as PODetail1
inner join Erp.PORel as PORel1 on
PODetail1.Company = PORel1.Company
And
PODetail1.PONUM = PORel1.PONum
And
PODetail1.POLine = PORel1.POLine
and ( PORel1.OpenRelease = 1 )where (PODetail1.OpenLine = 1 and PODetail1.PartNum = PODetail.PartNum)
group by [PODetail1].[Company],
[PODetail1].[PartNum])) as SubQuery7) and PODetail.POLine = (select Calculated_FirstPOLine from ((select
[PODetail1].[Company] as [PODetail1_Company],
[PODetail1].[PartNum] as [PODetail1_PartNum],
(Min(PORel1.PONUM)) as [Calculated_FirstPONum],
(Min(PORel1.POLine)) as [Calculated_FirstPOLine],
(Min(PORel1.PORelNum)) as [Calculated_FirstPORelNum]
from Erp.PODetail as PODetail1
inner join Erp.PORel as PORel1 on
PODetail1.Company = PORel1.Company
And
PODetail1.PONUM = PORel1.PONum
And
PODetail1.POLine = PORel1.POLine
and ( PORel1.OpenRelease = 1 )where (PODetail1.OpenLine = 1 and PODetail1.PartNum = PODetail.PartNum)
group by [PODetail1].[Company],
[PODetail1].[PartNum])) as SubQuery7) and PORel.PORelNum = (select Calculated_FirstPORelNum from ((select
[PODetail1].[Company] as [PODetail1_Company],
[PODetail1].[PartNum] as [PODetail1_PartNum],
(Min(PORel1.PONUM)) as [Calculated_FirstPONum],
(Min(PORel1.POLine)) as [Calculated_FirstPOLine],
(Min(PORel1.PORelNum)) as [Calculated_FirstPORelNum]
from Erp.PODetail as PODetail1
inner join Erp.PORel as PORel1 on
PODetail1.Company = PORel1.Company
And
PODetail1.PONUM = PORel1.PONum
And
PODetail1.POLine = PORel1.POLine
and ( PORel1.OpenRelease = 1 )where (PODetail1.OpenLine = 1 and PODetail1.PartNum = PODetail.PartNum)
group by [PODetail1].[Company],
[PODetail1].[PartNum])) as SubQuery7)) as SubQuery6 on
SubQuery6.PODetail_Company = Part.Company
And
SubQuery6.PODetail_PartNum = Part.PartNuminner join (select
[PartWhse1].[Company] as [PartWhse1_Company],
[PartWhse1].[PartNum] as [PartWhse1_PartNum],
(sum( PartQty.DemandQty)) as [Calculated_TotalQtyDemand],
(sum( PartQty.OnHandQty)) as [Calculated_TotalQtyOnHand]
from Erp.PartWhse as PartWhse1
inner join Erp.PartQty as PartQty on
PartQty.Company = PartWhse1.Company
And
PartQty.PartNum = PartWhse1.PartNum
And
PartQty.WarehouseCode = PartWhse1.WarehouseCodegroup by [PartWhse1].[Company],
[PartWhse1].[PartNum]) as SubQuery8 on
SubQuery8.PartWhse1_Company = Part.Company
And
SubQuery8.PartWhse1_PartNum = Part.PartNumleft outer join Erp.JobProd as JobProd on
JobProd.Company = OrderRel.Company
And
JobProd.OrderNum = OrderRel.OrderNum
And
JobProd.OrderLine = OrderRel.OrderLine
And
JobProd.OrderRelNum = OrderRel.OrderRelNum
order by SubQuery11.OrderDtl1_OrderNum , SubQuery11.OrderDtl1_OrderLine , SubQuery11.QuoteMtl_MtlSeq , SubQuery11.Calculated_QuoteMaterial , SubQuery11.Calculated_QuoteMaterialSeq
The revision number you are using is not the revision of the MtlPartNum (child part) it is the revision of PartNum ( Parent Part). You will have to figure out what the MtlPartNum’s revision number is from the PartRev table, because it is not stored in the PartMtl table.
I know this is an old post, but I made the same mistake as the original poster. The problem is with the JOIN in SubQuery2 between PartMtl and SubQuery1. The JOIN needs to be:
SubQuery1.PartMtl_MtlPartNum = PartMtl.PartNum (notice SubQuery1 is ‘MtlPartNum’. )
With this correction, the BAQ example in the ICE Tools User Guide will work correctly.
3 Likes
Ken,
I imported your BAQ an d received the below error message when I tested it.
Severity: Error, Table: , Field: , RowID: , Text: References to inaccessible tables detected:
subquery ‘PartXRefA’, table ‘Erp.PartXRefVend_UD’ with alias ‘PartXRefVendA_UD’
I am a newbie to writing and debugging code so if you could point me in the right direction, I would appreciate it.
Thanks.
Jim
There is a UD field in this query. You don’t have that field in your database.
Thanks Brandon. I am looking for it and cannot for the life of me, find it. I would assume to look in PartXRefVendA but I do not see a UD field there but I have known to be blind. 
Thanks Brandon. I am looking for it and cannot for the life of me, find it. I would assume to look in PartXRefVendA but I do not see a UD field there but I have known to be blind. 
I’ll probabably have to refer to @knash this one I guess. It’s not in the query that you posted. I don’t have the query in my system to poke around with it.
Does the error show when you analyze it? Only when you run it? It’s not a pop up right?
1 Like
Brandon,
The query is the one knash uploaded above called ETK-BOMReviewCosts.baq from Nov '18. When I analyze it, I get the below Query Execution Message:
References to inaccessible tables detected:
subquery ‘PartXRefA’, table ‘Erp.PartXRefVend_UD’ with alias ‘PartXRefVendA_UD’
Found it in the code. Now I just need to go find it in the Query Builder and remove it.
left outer join (select
[PartXRefVendA].[Company] as [PartXRefVendA_Company],
[PartXRefVendA].[PartNum] as [PartXRefVendA_PartNum],
[PartXRefVendA].[VendorNum] as [PartXRefVendA_VendorNum],
[PartXRefVendA].[VendPartNum] as [PartXRefVendA_VendPartNum],
[PartXRefVendA].[MfgNum] as [PartXRefVendA_MfgNum],
[PartXRefVendA].[MfgPartNum] as [PartXRefVendA_MfgPartNum],
[PartXRefVendA].[LeadTime] as [PartXRefVendA_LeadTime],
[PartXRefVendA_UD].[Number01] as [PartXRefVendA_Number01]
from Erp.PartXRefVend as PartXRefVendA
1 Like
Thanks Ken. I am not sure if it is 10.2.100 or something else because see the below screen shots. I am getting the error when I test or analyze it but it is not showing in the Query Builder display. What would you recommend using to edit the SQL if I were to export it, fix the code and then import it?
@knash is your database an uplift from E9?
@jimphillips, it looks like this is going to be a business specific BAQ. So you would use this as an example to build your own.
I would remove the following from the top level query. See what happens then. The BAQ has a lot going on.
a few calculated fields may need to be removed as well.
Hello Bruce,
As provided, the dashboard works well - thank you very much. There is a lot to learn in your example as well.
Regards,
Aaron