Help with building a BAQ that shows indented BOM for all open sales line item

Hi Epicor Team,

Can anybody guide me an idea on how to build a dashboard that shows indented BOM with Routing for all sales order line items? This is to get an idea on our order intake per week

Jay

The Epicor BAQ Help has a walk through on how to build an indented BOM using a CTE. Also there are a LOT of posts on this forum which talk about / tackle this problem. Have you checked those out yet?
image

I did built that rudimentry BAQ based on the tutorial but there are couple of things that I couldn’t figured out. I am struggling a little bit with revision (getting the latest REV) and also how to connect it to the sales order line items. In addition how to make sure that I stop when the part is purchased. I dont want to look at the indented BOM for parts changed to purchased. I want the proposed BAQ sorted based on the order date

That’s going to be an aggregate function like Max(PartRev.Effectivedate) or something like that. You’ll make a calculated field for that.

That’s going to be a criteria in your table. Part.TypeCode = M or Part.TypeCode != P. You’ll have to set somewhere in the CTE.

OrderBy is next to the field display tab. You can sort be whatever you want. (or just sort in your dashboard.)

Thanks Brandon. How do I connect it to the sales line item?

This is the format I am looking at

OrderNum OrderLine OrderDate RequestDate PartNum MtlPartNum OpCode OpHours
116445 1 1/17/2019 1/22/2019 10898-01 10898-01 BG0070 0.12
116445 1 1/17/2019 1/22/2019 10898-01 10898-01 BG0080 0.25
116445 1 1/17/2019 1/22/2019 10898-01 10898-01 BG0200 0.08
116445 1 1/17/2019 1/22/2019 10898-01 10898-01 BG0210 0.15
116445 1 1/17/2019 1/22/2019 10898-01 10898-01 BG0270 0
116445 1 1/17/2019 1/22/2019 10898-01 10898-02 BG0020 0.08
116445 1 1/17/2019 1/22/2019 10898-01 10898-02 BG0055 0.25
WITH 
[SaleData] as
	(
		select a.PartNum, a.OrderNum, a.OrderLine, oh.OrderDate, oh.RequestDate, a.SellingQuantity
		from orderdtl a
		join OrderHed as oh on oh.OrderNum = a.OrderNum
		join part h on a.partnum = h.partnum
		left join PartPlant f on f.partnum = a.partnum and f.plant = 'BG'
		left join Person e on e.PersonID = f.PersonID
		where a.openline = 1 and h.typecode ='M' and e.personid <> 'Plangrp1'
	)
,[BOMData] as
	(
		select pr.PartNum, pr.RevisionNum, pm.MtlPartNum, pr.EffectiveDate, pm.QtyPer
		from PartRev pr
		join PartPlant as pp on pp.PartNum = pr.PartNum and pp.Plant = pr.Plant
		left join PartRev as prl on prl.PartNum = pr.PartNum and prl.Approved = 1 and prl.EffectiveDate > pr.EffectiveDate and prl.AltMethod = pr.AltMethod
		join PartMtl as pm on pm.PartNum = pr.PartNum and pm.RevisionNum = pr.RevisionNum and pm.AltMethod = pr.AltMethod
		where pr.AltMethod = '' and pr.Approved = 1 and prl.RevisionNum is null
			and pp.SourceType in ('M','T')
	)
,[DataHours] as 
	(
		select po.PartNum, po.OpCode, sum(po.prodStandard) as [Hours]
		from PartOpr po
		join PartRev as pr on pr.PartNum = po.PartNum and pr.RevisionNum = po.RevisionNum and pr.AltMethod = po.AltMethod
		--join PartPlant as pp on pp.PartNum = po.PartNum and pp.Plant = pr.Plant
		join part as pp on pp.PartNum = po.PartNum
		left join PartRev as prl on prl.PartNum = pr.PartNum and prl.Approved = 1 and prl.EffectiveDate > pr.EffectiveDate and prl.AltMethod = pr.AltMethod
		where pr.Approved = 1 and prl.RevisionNum is null and pr.AltMethod = ''
			--and pp.SourceType in ('M','T')
			and pp.TypeCode = 'M'
		group by po.PartNum, po.OpCode 
	)
,[IndBOM] AS 
       (
			--start with only part intrested in as the material part
			select p.PartNum
				,p.PartNum as [MtlPartNum]
				,p.PartNum as [Parent]
				,cast(1 as decimal(15,5)) as [MtlTotalQty]
				--,cast(1 as int) as [Level]
				--,cast(p.PartNum as varchar(max)) as [Path]
			from part p
			--where p.PartNum in (select s.PartNum from SalePart s)
			where p.PartNum in (select distinct s.PartNum from SaleData s)

			UNION all

			select b.PartNum
				,b.MtlPartNum
				,c.Parent as [Parent]
				,cast(round(b.QtyPer * c.[MtlTotalQty],5) as decimal(15,5)) as [MtlTotalQty]
				--,cast(c.Level + 1 as int) as [Level]
				--,cast(c.path + '/' + b.MtlPartNum  as varchar(max)) as [Path]
			from BOMData b
			join IndBOM as c on c.MtlPartNum = b.PartNum  --plant select in WHERE statement
       )
,[OpsPerPart] as
	(
		select b.Parent as [Source], b.MtlPartNum, h.OpCode
			,sum(h.[Hours] * b.[MtlTotalQty]) as [SourceHours]
		from IndBOM b
		join DataHours as h on h.PartNum = b.MtlPartNum  --parent is included as a material, so do materials for all part ops
		group by b.Parent, b.MtlPartNum, h.OpCode
	)
--select * from opsperpart
select s.OrderNum, s.OrderLine, s.OrderDate, s.RequestDate, s.PartNum,  p.MtlPartNum, p.OpCode, p.SourceHours as [OpHours]
from SaleData s
join OpsPerPart as p on p.[Source] = s.PartNum

This is what I got, it runs fine in the SSMS but timesout in BAQ mode

Can you reformat your code block please?

Before we get any farther on this. When you get your orders do you create your jobs right away? And are your jobs constructed so that you make everything for the order on the job (of the manufactured parts). And do you make your jobs to order?

If that is the case, then it would be easier to query the job tables, as you don’t need the CTE as getting the details on the jobs has done that for you already.

Does it work if you limit it to one sales order? It looks like you are running it for every open sales order in your system.

No. It doesn’t work for even one sales order. I kind of think I am over complicating things here

That is the difficult none of the jobs are make to order. Everything is made to stock so only way to get the information through the method of the part on order

ok. So I’m not a SQL guru, so we’ll have to break things down a bit. If you go back to the CTE example from the tools guide, you are anchoring on a single part number right? That anchor is a parameter on part number. We can extend that by adding the OrderDtl table, and join the part number to that table, then set the parameter to Order Number instead of part number. See if you can get that working, just with the indented BOM.

If you know all of the order line parts use that to join to the BOM.

Then you can group by order line part on the dashboard to see the BOM for the order line part.

please format your code posts, its really hard to read this.

SalesHrs.baq (146.9 KB)
Attached is the BAQ

I am trying to get it formatted. Little bit of struggle to get it on the post

I wouldn’t try and put the order data in the CTE. Order information is more of a flat list.

The CTE is to find the parts associated with the parent part on the order, correct?

Yes

Do you have the query to show order line data?