From what I know, I cannot have tables/fields on my subquery that are not included in also my CTE? They are all on my toplevel.
I have created similar BAQs with success. If you donāt want to go the subquery route. Add another calculated column in the CTE as your sort.
Here is an example of something similar.
WITH PartsExplosion (ParentPartNum, ParentSN, ChildPart, ChildSN, lv, rowid, Name, SORT, customer, TopSN, TopPart)
AS (
-- Anchor
SELECT p.PartNum as ParentPartNum,
sn.SerialNumber as ParentSN,
p.PartNum as ChildPart ,
sn.SerialNumber as ChildSN,
0 as lv,
cast('' as nvarchar(MAX)) as rowid,
CAST(p.PartDescription as nvarchar(100)) as Name,
CAST('\' + sn.SerialNumber as nvarchar(254)) as Sort,
p.shortchar10,
sn.SerialNumber as TopSN,
p.PartNum as TopPart
from part p
inner join SerialNo sn on sn.PartNum = p.PartNum
--and p.ClassID in ('FGA', 'FGD')
-- and sn.SerialNumber = '003-9760036'
-- Recursive Call
UNION ALL
SELECT
BOM.ParentPartNum,
BOM.ParentSerialNo,
BOM.ChildPartNum,
BOM.ChildSerialNo,
lv + 1,
cast('' as nvarchar(MAX)) As Rowid,
CAST(REPLICATE ('| ' , lv ) + BOM.PartDescription as nvarchar(100)) as Name,
CAST(cte.Sort + '\' + BOM.ChildSerialNo as nvarchar(254)) as Sort,
cte.customer,
CTE.TopSN,
CTE.TopPart
FROM PartsExplosion CTE
JOIN (SELECT sm.ParentPartNum, sm.ParentSerialNo, sm.ChildPartNum, sm.ChildSerialNo, p.PartDescription
from Erp.SerialMatch sm
inner join SerialNo sn on sm.ChildPartNum = sn.PartNum and sm.ChildSerialNo = sn.SerialNumber
inner join part p on p.PartNum = sn.PartNum
where sm.ParentSerialNo <> sm.ChildSerialNo
) AS BOM
ON CTE.ChildPart = BOM.ParentPartNum and CTE.ChildSN = BOM.ParentSerialNo
)
select PE.customer, PE.TopSN, PE.TopPart, PE.ParentPartNum, PE.ParentSN, PE.ChildPart as CompPart, PE.ChildSN as ComponentSN, PE.lv, PE.[Name] as Description,
sn.SNFirstShippedDt_c, sn.SNFirstShippedtoCusDt_c, sn.ShortChar01, oh.ordernum,oh.PONum
FROM PartsExplosion AS PE
inner join SerialNo as sn on sn.SerialNumber = pe.TopSn and sn.PartNum = PE.TopPart
inner join Erp.JobProd jp on jp.JobNum = sn.shortchar01
inner join OrderHed oh on jp.OrderNum = oh.OrderNum
ORDER BY sort
Let me backtrack and clarify as well. I made a mistake, my calculated fields are already part of my subqueries. The BAQ is working fine with my sorts and no syntax issues, until I try adding the calculated field on my top level that groups everything. Then I start getting select errors.
I wouldnāt mind having to just return back the first result either if I canāt get it to work with the groupings.
hmm If you donāt mind uploading the BAQ I can check it out. Might be easier to see the BAQ. I started to test it out in a sql editor but the calc fields are barking.
On another note.
Your Calculated_Indi field could be written like the following instead of the case statement.
FORMAT( PartMtl.MtlSeq, ā00000ā) as Calculated_Ind1
Think there is going to be a few issues here. Your anchor portion of the CTE needs to be examined a bit more.
PartMtl stores Parts that make up of the BOM from the PartRev table; PartNum and RevisionNum
The PartNum and RevisionNum make each BOM unique for the Final Part.
This is important because a Part could have multiple revision and you want to make sure that the BOM (MtlPartNum selected is associated to the correct PartNum and RevisionNum)
This ranks the record in the table by company, partnum. This will only work if there arenāt any revisions. If that is the case today, then you are good; though not really future proof.
(Row_Number() over (partition by Part.Company, TopLevel order by PartMtl.MtlSeq)) as [Calculated_MinMtlSeq1],
For the Anchor part of the CTE, I am guessing you should start with the PartRev table. Maybe include the Part for details???
Just trying to make this what you are looking for and to make it manageable to maintain.
@knash I totally understand. Iām newer at this whole BAQ thing and probably a lot of things that could be fixed efficiancy wise. From what I know now we use revisions, however anytime a new revision is released, it automatically gets replaced on BOMs i believe? Either way. Here is my BAQ if you want to take a look at it this way.
Thanks again!
DA-IndentedBOMBAQ.baq (75.5 KB)
Give this a try. Version 10.2.600
I tried to make this as close as possible to what you are looking for. There is no primary bin though. A few things when working with the PlantWhse table. A part could be in multiple plants and that will āaddā duplicates. You will have to create a subquery to return only ONE value.
Which is why the warehousecode on PartWhse to return only āStockā your warehousecode might be different. Think that is the only gotcha.
1test.baq (205.6 KB)
/*
* 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 [BOMReviewParent] as
(select
(Part.ShortChar10) as [Calculated_Customer],
(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 decimal(10,2))) 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 Erp.Part as Part
inner join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
where (PartRev.PartNum = @PartNum and PartRev.RevisionNum = @RevNum)
union all
select
[BOMReviewParent].[Calculated_Customer] as [Calculated_Customer],
[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].[Calculated_PartRevNum] as [Calculated_PartRevNum],
(BOMReviewParent.Calculated_lv + 1) as [Calculated_BOMlv],
(cast(BOMChildren.Calculated_ChildQtyPer as decimal(10,2))) 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 + '-' + FORMAT(BOMChildren.Calculated_MtlSeq, '0000') as nvarchar(500))) as [Calculated_BOMSort]
from (select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
(((select
[PartRevA].[RevisionNum] as [PartRevA_RevisionNum]
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() and PartRevB.PartNum = [PartMtl].[MtlPartNum])) as PartRevB1 on
PartRevA.PartNum = PartRevB1.PartRevB_PartNum
and PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum
where (PartRevB1.Calculated_PartRevB_RowNum = 1)))) as [Calculated_PartRevNum],
(cast(PartMtl.QtyPer as decimal(10,2))) as [Calculated_ChildQtyPer],
[PartBOMChild].[PartDescription] as [PartBOMChild_PartDescription],
(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) as BOMChildren
inner join BOMReviewParent as BOMReviewParent on
BOMChildren.PartMtl_PartNum = BOMReviewParent.Calculated_ChildPartNum
and BOMChildren.PartMtl_RevisionNum = BOMReviewParent.Calculated_ChildRevNum)
select
[BOMReviewParentTOP].[Calculated_Sort] as [Calculated_Sort],
[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],
[Part2].[ClassID] as [Part2_ClassID],
[BOMReviewParentTOP].[Calculated_PartName] as [Calculated_PartName],
[BOMReviewParentTOP].[Calculated_QtyPer] as [Calculated_QtyPer],
[Part2].[UnitPrice] as [Part2_UnitPrice],
[Part2].[UOMClassID] as [Part2_UOMClassID],
(PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost+ PartCost.StdMtlBurCost) as [Calculated_AvgCost],
(AvgCost*BOMReviewParentTOP.Calculated_QtyPer) as [Calculated_ExtCost],
[PartPlant].[MinOrderQty] as [PartPlant_MinOrderQty],
[PartXRefA].[PartXRefVendA_LeadTime] as [PartXRefVendA_LeadTime],
[PartXRefA].[PartXRefVendA_Number01] as [PartXRefVendA_Number01],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[PartWhse].[NonNettableQty] as [PartWhse_NonNettableQty],
[PartWhse].[ReservedQty] as [PartWhse_ReservedQty],
(CAST (ISNULL(PartWhse.OnHandQty,0)-ISNULL(PartWhse.ReservedQty,0) as INT)) as [Calculated_AvailableQTYOH],
(CAST( (ISNULL(JobDemand.Calculated_JMRequiredQTY, 0) - ISNULL(JobDemand.Calculated_JMIssuedQTY, 0)) + ISNULL(OrderDemand.Calculated_ODOrderQty, 0)as INT)) as [Calculated_PartDemand],
[Vendor].[Name] as [Vendor_Name],
[PartXRefA].[PartXRefVendA_VendPartNum] as [PartXRefVendA_VendPartNum],
[Manufacturer].[Name] as [Manufacturer_Name],
[PartXRefA].[PartXRefVendA_MfgPartNum] as [PartXRefVendA_MfgPartNum],
(SUBSTRING(PurAgent.Name, 1, 1) + SUBSTRING(PurAgent.Name, CHARINDEX(' ',PurAgent.Name)+1, 1)) as [Calculated_Buyer],
[PartPlant].[GenerateSugg] as [PartPlant_GenerateSugg],
[PartPlant].[VendorNum] as [PartPlant_VendorNum],
(Convert(varchar(10), RcvDetailMain.RcvDtlMain_ReceiptDate, 1)) as [Calculated_LastRcptDate],
[RcvDetailMain].[RcvDtlMain_OurQty] as [RcvDtlMain_OurQty],
(CAST(RcvDetailMain.RcvDtlMain_OurUnitCost AS DECIMAL(10,2))) as [Calculated_LastRcvdPrice],
[Part2].[InActive] as [Part2_InActive],
[BOMReviewParentTOP].[Calculated_lv] as [Calculated_lv]
from BOMReviewParent as BOMReviewParentTOP
left outer join Erp.Part as Part2 on
BOMReviewParentTOP.Calculated_ChildPartNum = Part2.PartNum
left outer join Erp.PartPlant as PartPlant on
Part2.Company = PartPlant.Company
and Part2.PartNum = PartPlant.PartNum
left outer join Erp.PurAgent as PurAgent on
PartPlant.Company = PurAgent.Company
and PartPlant.BuyerID = PurAgent.BuyerID
left outer join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
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].[Number01] as [PartXRefVendA_Number01]
from Erp.PartXRefVend as PartXRefVendA
inner join (select
[PartXRefVend].[SysRowID] as [PartXRefVend_SysRowID],
(ROW_NUMBER() OVER (PARTITION BY PartXRefVend.VendorNum, PartXRefVend.PartNum ORDER BY PartXRefVend.SysRevID DESC)) as [Calculated_RowNumPartXRefVend]
from Erp.PartXRefVend as PartXRefVend
where (PartXRefVend.PurchaseDefault = 1)) as PartXRefB on
PartXRefVendA.SysRowID = PartXRefB.PartXRefVend_SysRowID
where (PartXRefB.Calculated_RowNumPartXRefVend = 1)) as PartXRefA on
PartPlant.Company = PartXRefA.PartXRefVendA_Company
and PartPlant.PartNum = PartXRefA.PartXRefVendA_PartNum
and PartPlant.VendorNum = PartXRefA.PartXRefVendA_VendorNum
left outer join Erp.Manufacturer as Manufacturer on
PartXRefA.PartXRefVendA_Company = Manufacturer.Company
and PartXRefA.PartXRefVendA_MfgNum = Manufacturer.MfgNum
left outer join (select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[PartNum] as [JobMtl_PartNum],
(SUM(JobMtl.RequiredQty)) as [Calculated_JMRequiredQTY],
(SUM(JobMtl.IssuedQty)) as [Calculated_JMIssuedQTY]
from Erp.JobMtl as JobMtl
where (JobMtl.JobComplete = 0)
group by [JobMtl].[Company],
[JobMtl].[PartNum]) as JobDemand on
Part2.Company = JobDemand.JobMtl_Company
and Part2.PartNum = JobDemand.JobMtl_PartNum
left outer join (select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(SUM(OrderDtl.OrderQty)) as [Calculated_ODOrderQty]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OpenLine = 1)
group by [OrderDtl].[Company],
[OrderDtl].[PartNum]) as OrderDemand on
Part2.Company = OrderDemand.OrderDtl_Company
and Part2.PartNum = OrderDemand.OrderDtl_PartNum
left outer join (select
[RcvDtlMain].[PartNum] as [RcvDtlMain_PartNum],
[RcvDtlMain].[ReceiptDate] as [RcvDtlMain_ReceiptDate],
[RcvDtlMain].[OurQty] as [RcvDtlMain_OurQty],
[RcvDtlMain].[OurUnitCost] as [RcvDtlMain_OurUnitCost]
from Erp.RcvDtl as RcvDtlMain
inner join (select
[RcvDtl].[PartNum] as [RcvDtl_PartNum],
(MAX(rcvdtl.SysRevID)) as [Calculated_RDMaxSysRevID]
from Erp.RcvDtl as RcvDtl
group by [RcvDtl].[PartNum]) as RecDetailsList on
RcvDtlMain.SysRevID = RecDetailsList.Calculated_RDMaxSysRevID
and RcvDtlMain.PartNum = RecDetailsList.RcvDtl_PartNum) as RcvDetailMain on
Part2.PartNum = RcvDetailMain.RcvDtlMain_PartNum
left outer join Erp.PartCost as PartCost on
Part2.Company = PartCost.Company
and Part2.PartNum = PartCost.PartNum
left outer join Erp.PartWhse as PartWhse on
Part2.Company = PartWhse.Company
and Part2.PartNum = PartWhse.PartNum
and ( PartWhse.WarehouseCode = 'Stock' )
inner join Erp.Part as PartParentPart on
BOMReviewParentTOP.Calculated_TopPart = PartParentPart.PartNum
order by BOMReviewParentTOP.Calculated_Sort
@knash Thanks for taking more time into this! Are you able to tell me what this is doing, or how I can fix it?
I am getting this error.
You should be able to delete the UD table. That is probably something due to being upgrade from a previous version. Or more than likely you donāt have any UD fields on the PartXRefVend table.
I seem to be receiving some more issues. It wants me to add 2 more fields to the BOMReviewChild to match with the CTE.
Also, I had to delete the Part.ShortChar10 calculation, as we donāt have that in our tableā¦
Its been a few days. Everything working???
@knash Iām very sorry, it has been such a busy week that I just got a chance to continue looking over this.
I was able to get it down in the display to kind of what I had beforeā¦and added a DisplayIV so that I only see it once instead of for every part. This is what I am looking at now.
If I run it in the other BAQ, I see stuff like this
It looks like a bunch of supplier parts are not getting added in? Also, I donāt see anything showing up under my onhand qty, and my demand qty seems a little off? Also, It doesnāt look like I can use wildcards in my parameter anymoreā¦like āQS-REF-02*āā¦is that easy enough to add in without messing something up in my rev?
Overall this adds a ton of functionality and would be very useful in the future if we need to add more information, and start using the revs as well. So thanks!!
All of the changes you are looking for are all there on the Top Level. Our data and how we use Epicor are a bit different, so a few tweaks are needed. Demand is driven from orders and jobs not just the PartWhse DemandQty, which is what yours was.
You should be able to add the fields you want from partWhse and remove the ones you donāt want.
As for the supplier. we use the PurchaseDefault flag to pick the correct one. I am guessing you all arenāt using that. Just remove that criteria on the PartXRefB query.
Not sure about the search as it should work the same. What field was it on?
I see that there are dups in your return from the PartWhse. make sure you are selecting the one you want.
Part 425-162 It is showing twice. guessing parts have multiple Warehouse Codes
if things are still not right; repost the baq. There are many moving parts to this. hard to debug from an output picture.
1test.baq (202.8 KB)
@knash Thanks for the pointers! The image showing two of 425-162 I believe was my original BAQ which did have the duplicate issues. So far it doesnāt look like this is reproducing that which is good.
I see, thats good that the number is correct now then! I do want the PartWhse_OnHandQty field there, which is already added, but for some reason no numbers are in there, even though Iām fairly sure we have qty on hand.
I figured this one outā¦I just added back the criteria where PartNum MATCHES @PartNum parameter.
So really, I think the only thing that isnāt quite working for me yet is the QtyOnHand, and I also wanted to ask if it is possible to add the Primary Bin back, or how I go about it? I added the BAQ with recent changes as well. Thanks for the continued help!
You need to tweak the Top Level Query.
Remove the criteria on the Erp.PartWhse table. (by doing this you will see multiples of your part again.)
Add the partWhse.WarehouseCode field as a display field. Use that information to set a new criteriia on the PartWhse table.
Run with the query for the part in the above example . You should then learn what WarehouseCode you will want to use. Be careful though, it might not be this simple. (We only have service and stock warehousecodes and for this report we only show stock)
Add the criteria back with your newfound knowledge of warehouse codes to eliminate the duplicates.
1 Like
Thanks again! It looks like we are only using MAIN as our warehouse codeā¦so I changed the criteria and that aligns up nicely now.
I believe the only thing I have yet to do is add the primary bin back from the PlntWhse table.
**EDIT - Was able to add that pretty easily. Should be all taken care of now.
Thanks for all your help again!