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

JayKrishna · January 21, 2019, 4:38pm

(select 
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderHed].[RequestDate] as [OrderHed_RequestDate],
	[OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity]
	from Erp.OrderDtl as OrderDtl
	inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
	inner join Erp.Part as Part on 
	OrderDtl.Company = Part.Company
	and OrderDtl.PartNum = Part.PartNum
	left outer join Erp.PartPlant as PartPlant on 
	PartPlant.PartNum = OrderDtl.PartNum
	and ( PartPlant.Plant = 'BG'  )

	where OrderDtl.OpenLine = 1  and Part.TypeCode = 'M'  and partplant.PersonID <> 'Plangrp1')

knash · January 21, 2019, 4:40pm

As for the BOM.

You want the BOM to come from the Job? or from the PartMtl Table? I would guess from the Job as that would be what is really being called for. No?

JayKrishna · January 21, 2019, 4:42pm

Our jobs dont have the indented structure 9everything is make to stock), so the only way to get it through partmtl table

knash · January 21, 2019, 4:43pm

Hmm.

A user can make changes to the BOM on the job though, don’t you want to see that?

knash · January 21, 2019, 4:44pm

plus it would make the query easier to write. no cte.

JayKrishna · January 21, 2019, 4:56pm

No. This is for planning so dont care about changes that much

JayKrishna · January 21, 2019, 4:57pm

with 
[SaleData] as 
		(
			select 
				[OrderDtl].[PartNum] as [OrderDtl_PartNum],
				[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
				[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
				[OrderHed].[OrderDate] as [OrderHed_OrderDate],
				[OrderHed].[RequestDate] as [OrderHed_RequestDate],
				[OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity]

			from Erp.OrderDtl as OrderDtl
			inner join Erp.OrderHed as OrderHed on 
			OrderDtl.Company = OrderHed.Company
			and OrderDtl.OrderNum = OrderHed.OrderNum
			inner join Erp.Part as Part on 
			OrderDtl.Company = Part.Company
			and OrderDtl.PartNum = Part.PartNum
			left outer join Erp.PartPlant as PartPlant on 
			PartPlant.PartNum = OrderDtl.PartNum
			and ( PartPlant.Plant = 'BG'  )
			left outer join Erp.Person as Person on 
			PartPlant.Company = Person.Company
			and PartPlant.PersonID = Person.PersonID
			where OrderDtl.OpenLine = 1  and 
			Part.TypeCode = 'M' and partplant.PersonID <> 'Plangrp1'
		)
 ,[SalePart] as 
		(	
			select distinct 
			[S].[OrderDtl_PartNum] as [OrderDtl_PartNum]
			from  SaleData  as S
		)
 ,[BOMData] as 
		(
			select 
				[PartRev].[PartNum] as [PartRev_PartNum],
				[PartRev].[RevisionNum] as [PartRev_RevisionNum],
				[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
				[PartRev].[EffectiveDate] as [PartRev_EffectiveDate],
				[PartMtl].[QtyPer] as [PartMtl_QtyPer]

			from Erp.PartRev as PartRev
			inner join Erp.PartPlant as pp on 
			PartRev.PartNum = pp.PartNum
			and PartRev.Plant = pp.Plant
			left outer join Erp.PartRev as prl on 
			PartRev.PartNum = prl.PartNum
			and PartRev.AltMethod = prl.AltMethod
			and PartRev.EffectiveDate < prl.EffectiveDate
			and ( prl.Approved = 1  )
			inner join Erp.PartMtl as PartMtl on 
			PartMtl.Company = PartRev.Company
			and PartMtl.PartNum = PartRev.PartNum
			and PartMtl.RevisionNum = PartRev.RevisionNum
			and PartMtl.AltMethod = PartRev.AltMethod
			where PartRev.AltMethod = ''  and PartRev.Approved = 1  
			and prl.RevisionNum is null and pp.SourceType in ('M', 'T')
		)
 ,[DataHours] as 
		(
			select 
				(sum(PartOpr.ProdStandard)) as [Calculated_Hours],
				[PartOpr].[PartNum] as [PartOpr_PartNum],
				[PartOpr].[OpCode] as [PartOpr_OpCode]

			from Erp.PartOpr as PartOpr
			inner join Erp.PartRev as prl4 on 
			PartOpr.Company = prl4.Company
			and PartOpr.PartNum = prl4.PartNum
			and PartOpr.RevisionNum = prl4.RevisionNum
			and PartOpr.AltMethod = prl4.AltMethod
			left outer join Erp.PartRev as prl5 on 
			prl4.PartNum = prl5.PartNum
			and prl4.EffectiveDate < prl5.EffectiveDate
			and prl4.AltMethod = prl5.AltMethod
			and ( prl5.Approved = 1  )
			inner join Erp.PartPlant as pp1 on 
			PartOpr.PartNum = pp1.PartNum
			and ( pp1.Plant = prl4.Plant  )
			where prl4.Approved = 1  and prl4.AltMethod = ''  
			and pp1.SourceType in ('M', 'T')  and prl5.RevisionNum is null
			group by PartOpr.PartNum,
			PartOpr.OpCode
		)
 ,[IndBOM] as 
		(
			select 
				[p].[PartNum] as [p_PartNum],
				(p.PartNum) as [Calculated_MtlPartNum],
				(cast(1 as decimal(15,5))) as [Calculated_QtyPer],
				(p.PartNum) as [Calculated_Parent],
				(cast(1 as decimal(15,5))) as [Calculated_ParentQty],
				(cast(1 as int)) as [Calculated_Level],
				(cast(p.PartNum as varchar(max))) as [Calculated_Path]

			from Erp.Part as p
			where (p.PartNum in (select OrderDtl_PartNum from SalePart))

			union all

			select 
				[BOMData].[PartRev_PartNum] as [PartRev_PartNum],
				[BOMData].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
				(cast(round(BOMData.PartMtl_QtyPer,5) as decimal(15,5))) as [Calculated_Qtyper],
				[IndBOM].[Calculated_Parent] as [Calculated_Parent],
				(cast(round(BOMData.PartMtl_QtyPer,5) as decimal(15,5))) as [Calculated_ParentQty],
				(cast(IndBOM.Calculated_Level + 1 as int)) as [Calculated_Level],
				(cast(IndBOM.Calculated_Path + '/' + BOMData.PartMtl_MtlPartNum  as varchar(max))) as [Calculated_Path]

			from  BOMData  as BOMData
			inner join  IndBOM  as IndBOM on 
			BOMData.PartRev_PartNum = IndBOM.Calculated_MtlPartNum
		)
 ,[OpsPerPart] as 
		(
			select 
				[b].[Calculated_Parent] as [Calculated_Parent],
				[b].[Calculated_MtlPartNum] as [Calculated_MtlPartNum],
				(sum(DataHours.Calculated_Hours*b.Calculated_ParentQty)) as [Calculated_SourceHrs]

			from  IndBOM  as b
			inner join  DataHours  as DataHours on 
			b.Calculated_MtlPartNum = DataHours.PartOpr_PartNum
			group by b.Calculated_Parent,
			DataHours.PartOpr_OpCode,
			b.Calculated_MtlPartNum
		)

select 
		[SaleData].[OrderDtl_PartNum] as [OrderDtl_PartNum],
		[SaleData].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
		[SaleData].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
		[SaleData].[OrderHed_OrderDate] as [OrderHed_OrderDate],
		[SaleData].[OrderHed_RequestDate] as [OrderHed_RequestDate],
		[SaleData].[OrderDtl_SellingQuantity] as [OrderDtl_SellingQuantity],
		[OpsPerPart].[Calculated_Parent] as [Calculated_Parent],
		[OpsPerPart].[Calculated_MtlPartNum] as [Calculated_MtlPartNum]

	from  SaleData  as SaleData
	inner join  OpsPerPart  as OpsPerPart on 
	SaleData.OrderDtl_PartNum = OpsPerPart.Calculated_Parent

knash · January 21, 2019, 5:40pm

I am sure there is better way to get there, this will get you there.

Try this in a sql editor.

When it is gives you expected results either create a view and call an external BAQ or rewrite as a BAQ. Your call.

You will need to tweek a few lines for your parts. Post if you need help.

With [BOMReviewParent] AS 
(select 

	(PartRev.PartNum) as [Calculated_TopPart],
	(PartRev.RevisionNum) as [Calculated_TopPartRev],
	(part.PartNum) as [Calculated_ParentPartNum],
	(PartRev.RevisionNum) as [Calculated_ParentRevNum],
	(part.PartNum) as [Calculated_ChildPartNum],
	(PartRev.RevisionNum) as [Calculated_ChildRevNum],
	(0) as [Calculated_lv],
	(Cast(part.PurchasingFactor as int)) as [Calculated_QtyPer],
	(Cast(part.PartDescription as nvarchar(500))) as [Calculated_PartName],
	(0) as [Calculated_OpSeq],
	(CAST(part.PartNum + '-' + partrev.RevisionNum as nvarchar(500))) as [Calculated_Sort]
from dbo.Part as Part
inner join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
And
	Part.PartNum = PartRev.PartNum
 and ( PartRev.Approved = 1  )
 Where Part.TypeCode = 'M'

UNION ALL
select 

	[BOMReviewParent].[Calculated_TopPart] as [Calculated_TopPart],
	[BOMReviewParent].[Calculated_TopPartRev] as [Calculated_TopPartRev],
	[BOMChildren].[PartMtl_PartNum] as [PartMtl_PartNum],
	[BOMChildren].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[BOMChildren].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[BOMChildren].[PartRevA_RevisionNum] as [PartRevA_RevisionNum],
	(BOMReviewParent.Calculated_lv + 1) as [Calculated_BOMlv],
	(cast(BOMChildren.Calculated_ChildQtyPer as int)) as [Calculated_BOMQtyPer],
	(CAST(REPLICATE ('|     ' , BOMReviewParent.Calculated_lv +1) + BOMChildren.PartBOMChild_PartDescription as nvarchar(500))) as [Calculated_BOMPartName],
	(BOMChildren.Calculated_MtlSeq) as [Calculated_BOMMtlSeq],
	(CAST(BOMReviewParent.Calculated_Sort + '-' + RIGHT( '0000'+ Convert(varchar, BOMChildren.Calculated_SeqNum), 4)as nvarchar(500))) as [Calculated_BOMSort]
from  (select 
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[RealPartRev].[PartRevA_RevisionNum] as [PartRevA_RevisionNum],
	(cast(PartMtl.QtyPer as int)) as [Calculated_ChildQtyPer],
	[PartBOMChild].[PartDescription] as [PartBOMChild_PartDescription],
	(cast(PartMtl.MtlSeq as nvarchar(254))) as [Calculated_SeqNum],
	(CAST(PartMtl.MtlSeq as Int)) as [Calculated_MtlSeq]
from Erp.PartMtl as PartMtl
inner join Erp.Part as PartBOMChild on 
	PartMtl.Company = PartBOMChild.Company
And
	PartMtl.MtlPartNum = PartBOMChild.PartNum

inner join  (select 
	[PartRevA].[PartNum] as [PartRevA_PartNum],
	[PartRevA].[RevisionNum] as [PartRevA_RevisionNum],
	[PartRevA].[RevShortDesc] as [PartRevA_RevShortDesc],
	[PartRevA].[Company] as [PartRevA_Company]
from Erp.PartRev as PartRevA
inner join  (select 
	[PartRevB].[PartNum] as [PartRevB_PartNum],
	[PartRevB].[RevisionNum] as [PartRevB_RevisionNum],
	(ROW_NUMBER() OVER (PARTITION BY partrevb.PartNum ORDER BY  partrevb.ApprovedDate DESC)) as [Calculated_PartRevB_RowNum]
from Erp.PartRev as PartRevB
 where (PartRevB.Approved = 1  and PartRevB.EffectiveDate <= getdate()))  as PartRevB1 on 
	PartRevA.PartNum = PartRevB1.PartRevB_PartNum
And
	PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum

 where PartRevB1.Calculated_PartRevB_RowNum = 1)  as RealPartRev on 
	PartMtl.MtlPartNum = RealPartRev.PartRevA_PartNum
And
	PartMtl.Company = RealPartRev.PartRevA_Company)  as BOMChildren
inner join  BOMReviewParent  as BOMReviewParent on 
	BOMChildren.PartMtl_PartNum = BOMReviewParent.Calculated_ChildPartNum
And
	BOMChildren.PartMtl_RevisionNum = BOMReviewParent.Calculated_ChildRevNum)

select SalesPartList.OrderDtl_OrderNum, SalesPartList.OrderDtl_OrderLine, SalesPartList.OrderDtl_PartNum, 
SalesPartList.OrderDtl_RevisionNum, SalesPartList.OrderDtl_SellingQuantity,
	[BOMReviewParentTOP].[Calculated_TopPart] as [Calculated_TopPart],
	[BOMReviewParentTOP].[Calculated_TopPartRev] as [Calculated_TopPartRev],
	[BOMReviewParentTOP].[Calculated_ParentPartNum] as [Calculated_ParentPartNum],
	[BOMReviewParentTOP].[Calculated_ParentRevNum] as [Calculated_ParentRevNum],
	[BOMReviewParentTOP].[Calculated_OpSeq] as [Calculated_OpSeq],
	[BOMReviewParentTOP].[Calculated_ChildPartNum] as [Calculated_ChildPartNum],
	[BOMReviewParentTOP].[Calculated_ChildRevNum] as [Calculated_ChildRevNum],
	[BOMReviewParentTOP].[Calculated_PartName] as [Calculated_PartName],
	[BOMReviewParentTOP].[Calculated_QtyPer] as [Calculated_QtyPer],
  SalesPartList.OrderHed_RequestDate, SalesPartList.OrderHed_OrderDate,
	[BOMReviewParentTOP].[Calculated_lv] as [Calculated_lv],
  	[BOMReviewParentTOP].[Calculated_Sort] as [Calculated_Sort]
from  BOMReviewParent  as BOMReviewParentTOP
inner join (select 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderHed].[RequestDate] as [OrderHed_RequestDate],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
    OrderDtl.RevisionNum as [OrderDtl_RevisionNum],
  [OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity]

	from Erp.OrderDtl as OrderDtl
	inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
	inner join Erp.Part as Part on 
	OrderDtl.Company = Part.Company
	and OrderDtl.PartNum = Part.PartNum
	left outer join Erp.PartPlant as PartPlant on 
	PartPlant.PartNum = OrderDtl.PartNum
  where OrderDtl.OpenLine = 1 and OrderHed.OpenOrder = 1
  --and Part.TypeCode = 'M'  and partplant.PersonID and PartPlant.Plant = 'BG' )
  ) as SalesPartList on SalesPartList.OrderDtl_PartNum = [BOMReviewParentTOP].[Calculated_TopPart] 
        and SalesPartList.OrderDtl_RevisionNum = [BOMReviewParentTOP].[Calculated_TopPartRev]
        order by SalesPartList.OrderDtl_OrderNum, SalesPartList.OrderDtl_OrderLine, SalesPartList.OrderDtl_PartNum, 	[BOMReviewParentTOP].[Calculated_Sort]

JayKrishna · January 21, 2019, 6:55pm

Hi knash,

Thanks for taking the time to create the SQL query. If I were to add the operation information to the query where would I add?

knash · January 21, 2019, 7:19pm

Are you looking for all of the hours associated to the operations to build the part?

or are you looking for a particular operation?

Either way you should consider to add RevisionNum to your PartOpr query, otherwise the group by will sum multiple revisions. .

JayKrishna · January 21, 2019, 7:28pm

I am looking at operation hours to build the part.

JayKrishna · January 21, 2019, 7:29pm

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

JayKrishna · January 21, 2019, 7:29pm

At the end this is what I am looking at

knash · January 21, 2019, 7:31pm

So OPCode BG0070 is associated to the MtlPartNum or PartNum?

JayKrishna · January 21, 2019, 7:31pm

Mtlpartnum

knash · January 21, 2019, 7:47pm

I thought operations are on Parent Parts. Not components.

Are you asking to see the information when a MtlPart is a ParentPart? You can show the Opr detail then for the Parent MtlParts with another subquery. Again need to add RevisionNum.

Check out the PartOpr table for a few parts. Make a query that works for it. Then you can join it back to the main query.

JayKrishna · January 21, 2019, 7:51pm

I want to see the operations for all parts of the indented structure. I feel like I could make the sub query for all the operations. :" join it back to the main query." - are you reffering to the section after the union all .

knash · January 21, 2019, 8:00pm

For the example below:
The Parent Part is 005-874 Rev 01
There is PartOpr data for that part.

MtlPart 004-606 Rev 1
There is no PartOpr data for that part, because it is already in the Operation for the parentPart. Now if the MtlPart was a subassembly, then there would be PartOpr data.

You are saying that all of your parts have their own operations? OK I will accept that as I cannot see your data. I would then create a query to from the PartOpr table with what you want to show. This query would be stand alone at first. Then if it looks like what you want, then add it to the query as a subquery.

The main query is: ( I would add it there )

select SalesPartList.OrderDtl_OrderNum, SalesPartList.OrderDtl_OrderLine, SalesPartList.OrderDtl_PartNum, 
SalesPartList.OrderDtl_RevisionNum, SalesPartList.OrderDtl_SellingQuantity,
	[BOMReviewParentTOP].[Calculated_TopPart] as [Calculated_TopPart],
	[BOMReviewParentTOP].[Calculated_TopPartRev] as [Calculated_TopPartRev],
	[BOMReviewParentTOP].[Calculated_ParentPartNum] as [Calculated_ParentPartNum],
	[BOMReviewParentTOP].[Calculated_ParentRevNum] as [Calculated_ParentRevNum],
	[BOMReviewParentTOP].[Calculated_OpSeq] as [Calculated_OpSeq],
	[BOMReviewParentTOP].[Calculated_ChildPartNum] as [Calculated_ChildPartNum],
	[BOMReviewParentTOP].[Calculated_ChildRevNum] as [Calculated_ChildRevNum],
	[BOMReviewParentTOP].[Calculated_PartName] as [Calculated_PartName],
	[BOMReviewParentTOP].[Calculated_QtyPer] as [Calculated_QtyPer],
  SalesPartList.OrderHed_RequestDate, SalesPartList.OrderHed_OrderDate,
	[BOMReviewParentTOP].[Calculated_lv] as [Calculated_lv],
  	[BOMReviewParentTOP].[Calculated_Sort] as [Calculated_Sort]
from  BOMReviewParent  as BOMReviewParentTOP
inner join (select 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderHed].[RequestDate] as [OrderHed_RequestDate],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
    OrderDtl.RevisionNum as [OrderDtl_RevisionNum],
  [OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity]

	from Erp.OrderDtl as OrderDtl
	inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
	inner join Erp.Part as Part on 
	OrderDtl.Company = Part.Company
	and OrderDtl.PartNum = Part.PartNum
	
  where OrderDtl.OpenLine = 1 and OrderHed.OpenOrder = 1
  --and Part.TypeCode = 'M'  and partplant.PersonID and PartPlant.Plant = 'BG' )
  ) as SalesPartList on SalesPartList.OrderDtl_PartNum = [BOMReviewParentTOP].[Calculated_TopPart] 
        and SalesPartList.OrderDtl_RevisionNum = [BOMReviewParentTOP].[Calculated_TopPartRev]
        order by SalesPartList.OrderDtl_OrderNum, SalesPartList.OrderDtl_OrderLine, SalesPartList.OrderDtl_PartNum, 	[BOMReviewParentTOP].[Calculated_Sort]

timshuwy · January 22, 2019, 6:40am

Just curious… looking through this, I am wondering what the business issue is? What exactly are you looking for. Your initial question talks about “order intake” and “indented BOM with Routing”… but then the conversation seems to go other directions.
If you simply want to know how many hours of work there are on new orders, there may be a simpler way… (several simpler ways in fact).
Example:

if you are Standard Cost (or even if you are not) the system can do a cost roll, and the number of hours are rolled up in the PartCost table
if you can wait till MRP runs, you can look at all newly created jobs to see the total labor hours in the Job Assembly table.