(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')
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?
Our jobs dont have the indented structure 9everything is make to stock), so the only way to get it through partmtl table
Hmm.
A user can make changes to the BOM on the job though, don’t you want to see that?
plus it would make the query easier to write. no cte.
No. This is for planning so dont care about changes that much
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
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]
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?
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. .
I am looking at operation hours to build the part.
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 |
At the end this is what I am looking at
So OPCode BG0070 is associated to the MtlPartNum or PartNum?
Mtlpartnum
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.
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 .
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]
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.