HI Mario,
I would love to have that BAQ as well.
Thanks,
Kyle
@PaulMorgan I’m currently working with support on an issue that seems similar to what you mentioned. Do you happen to have any specifics on the known issue? Maybe a KB or Case # I could have support reference? Thanks!
Hi Jeff,
sorry in the delay it was a public holiday here. Our case number was CS0000776821 if that helps. Epicor supplied us with a sql data fix which was named CR39940MPS then our version release, not sure if that will help. I will also attach the sql fix, but I would not suggest running it without Epicor’s blessing and certainly don’t run it in production without running and testing it in pilot first.
I hope this helps.
Regards,
Paul
FX_Upd_Field_Case.sql (26.1 KB)
Sorry guys, I do not longer have that BAQ. But I did send it to Aaron perhaps he can share it. Sorry
@hmwillett realize this is a very old thread but would you be willing to share your stock status BAQ with me?
@hmwillett I see this job variance BAQ but the one I was inquiring about was posted earlier in this thread and it was a Stock Status BAQ you said you’d been using to compare to the Stock Status report. Do you still have & use that BAQ?
Hey Hannah, do you happen to have a stock status baq that is linked to part tran table so we can see the stock status for a given date? @prince_gs2010 and I are looking for the same thing. Looping him on this convo in case you happen to have one you would like to share 
My goal is to use a custom stock status baq as a sub-ledger so Finance can reconcile our GL balance sheet for a given historical date.
Looks like the link to download the BAQ above still works. Outside of that, I have nothing.
@MDRussello Going down this road can be very frustrating. Stock Status is not a balancing report and trying to make a baq to match to it even as of current can be hard to pin down.
1 Like
Hey Melissa,
For years people have asked for BAQ Stock Status by date. As you know, the standard Stock Status doesn’t even work that way. I’ve yet to see anyone pull it off TBH. If they ever got it to work for all the various costing methods, they’ve kept it a guarded secret.
The best I have seen is something that @Rick_Bird does and that’s to copy the costs at a certain point (month end usually) and use that to get historical costs in a BAQ. One could do the same for the quantities.
That’s all I got. Others may know more.
2 Likes
Hi @MDRussello : I wrote the below SQL query for getting the stock as on date. You can use this as a temporary solution:
SELECT
TOP (100) PERCENT B.Company, B.Plant, B.LotNum, p.ProdCode, B.PartNum, p.PartDescription, B.TotRcvdQty AS OnhandQty,B.ClassID ,B.Description,
B.ProdCode, B.PGroup,B.UM,B.BinNum,B.WareHouseCode,
(CASE WHEN b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] - b.[3TO6_MONTHSQTY] - b.[1TO3_MONTHSQTY] > 0 THEN (CASE WHEN b.CURRENTMONTHQTY
- (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] - b.[3TO6_MONTHSQTY] - b.[1TO3_MONTHSQTY])
< 0 THEN 0 ELSE b.CURRENTMONTHQTY - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] - b.[3TO6_MONTHSQTY] - b.[1TO3_MONTHSQTY])
END) ELSE b.CURRENTMONTHQTY END) AS CURRENTMONTHQTY,
(CASE WHEN b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] - b.[3TO6_MONTHSQTY] > 0 THEN (CASE WHEN b.[1TO3_MONTHSQTY] - (b.IssuedQty
- b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] - b.[3TO6_MONTHSQTY])
< 0 THEN 0 ELSE b.[1TO3_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] - b.[3TO6_MONTHSQTY]) END)
ELSE b.[1TO3_MONTHSQTY] END) AS [1TO3_MONTHSQTY],
(CASE WHEN b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY] > 0 THEN (CASE WHEN b.[3TO6_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY
- b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY])
< 0 THEN 0 ELSE b.[3TO6_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] - b.[6TO9_MONTHSQTY]) END) ELSE b.[3TO6_MONTHSQTY] END)
AS [3TO6_MONTHSQTY],
(CASE WHEN b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY] > 0 THEN (CASE WHEN b.[6TO9_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY]
- b.[9TO12_MONTHSQTY]) < 0 THEN 0 ELSE b.[6TO9_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] - b.[9TO12_MONTHSQTY]) END) ELSE b.[6TO9_MONTHSQTY] END)
AS [6TO9_MONTHSQTY],
(CASE WHEN b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY] > 0 THEN (CASE WHEN b.[9TO12_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY])
< 0 THEN 0 ELSE b.[9TO12_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY - b.[12TO15_MONTHSQTY]) END) ELSE b.[9TO12_MONTHSQTY] END) AS [9TO12_MONTHSQTY],
(CASE WHEN B.OVER15MONTHS_QTY >= B.IssuedQty THEN B.OVER15MONTHS_QTY - B.IssuedQty ELSE 0 END) AS OVER15MONTHS_QTY,
(CASE WHEN B.IssuedQty - B.OVER15MONTHS_QTY > 0 THEN (CASE WHEN b.[12TO15_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY)
< 0 THEN 0 ELSE b.[12TO15_MONTHSQTY] - (b.IssuedQty - b.OVER15MONTHS_QTY) END) ELSE b.[12TO15_MONTHSQTY] END) AS [12TO15_MONTHSQTY],
CASE WHEN B.CostMethod = ‘T’ THEN pl.LotMaterialCost
ELSE Erp.PartCost.AvgMaterialCost
END AS MaterialCost,
CASE WHEN B.CostMethod = ‘T’ THEN pl.LotBurdenCost
ELSE Erp.PartCost.AvgMtlBurCost
END AS MtlBurCost,
CASE WHEN B.CostMethod = ‘T’ THEN pl.lotlaborcost ELSE Erp.PartCost.AvgLaborCost
END AS LaborCost,
CASE WHEN B.CostMethod = ‘T’ THEN pl.LotMaterialCost + pl.LotMtlBurCost ELSE Erp.PartCost.AvgBurdenCost + Erp.PartCost.AvgLaborCost + Erp.PartCost.AvgMaterialCost + Erp.PartCost.AvgMtlBurCost + Erp.PartCost.AvgSubContCost
END AS AvgCost,
CASE WHEN B.CostMethod = ‘T’ THEN (B.TotRcvdQty * (pl.LotMaterialCost + pl.LotMtlBurCost))
ELSE B.TotRcvdQty * (Erp.PartCost.AvgBurdenCost + Erp.PartCost.AvgLaborCost + Erp.PartCost.AvgMaterialCost + Erp.PartCost.AvgMtlBurCost + Erp.PartCost.AvgSubContCost)
END AS ExtCost
FROM (SELECT Company, Plant, CostID, PartNum, CostMethod,WareHouseCode, LotNum ,ClassID ,Description,
ProdCode, PGroup,UM,BinNum, TotRcvdQty, CURRENTMONTHQTY, [1TO3_MONTHSQTY], [3TO6_MONTHSQTY], [6TO9_MONTHSQTY], [9TO12_MONTHSQTY],
[12TO15_MONTHSQTY], OVER15MONTHS_QTY, IssuedQty
FROM (SELECT Company, Plant, CostID, PartNum, CostMethod,WareHouseCode, LotNum,ClassID ,Description,
ProdCode, PGroup,UM,BinNum, SUM(RcvdQty - (Consqty + Consqty1)) AS TotRcvdQty, SUM(CASE WHEN DATEDIFF(MM, A.TRANDATE, CONVERT(varchar,
getdate(), 111)) = 12 THEN (A.RCVDQTY) ELSE 0 END) AS CURRENTMONTHQTY, SUM(CASE WHEN DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111)) > 12 AND DATEDIFF(MM,
A.TRANDATE, CONVERT(varchar, getdate(), 111)) <= 3 THEN (A.RCVDQTY) ELSE 0 END) AS [1TO3_MONTHSQTY], SUM(CASE WHEN DATEDIFF(MM, A.TRANDATE, CONVERT(varchar,
getdate(), 111)) > 3 AND DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111)) <= 6 THEN (A.RCVDQTY) ELSE 0 END) AS [3TO6_MONTHSQTY], SUM(CASE WHEN DATEDIFF(MM,
A.TRANDATE, CONVERT(varchar, getdate(), 111)) > 6 AND DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111)) <= 9 THEN (A.RCVDQTY) ELSE 0 END) AS [6TO9_MONTHSQTY],
SUM(CASE WHEN DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111)) > 9 AND DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111)) <= 12 THEN (A.RCVDQTY)
ELSE 0 END) AS [9TO12_MONTHSQTY], SUM(CASE WHEN DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111)) > 12 AND DATEDIFF(MM, A.TRANDATE, CONVERT(varchar,
getdate(), 111)) <= 15 THEN (A.RCVDQTY) ELSE 0 END) AS [12TO15_MONTHSQTY], SUM(CASE WHEN DATEDIFF(MM, A.TRANDATE, CONVERT(varchar, getdate(), 111))
> 15 THEN (A.RCVDQTY) ELSE 0 END) AS OVER15MONTHS_QTY, SUM(Consqty + Consqty1) AS IssuedQty
FROM (SELECT (CASE WHEN pt.trantype IN (‘INS-STK’, ‘PUR-STK’, ‘DMR-STK’, ‘MFG-STK’, ‘ADJ-QTY’, ‘AST-STK’, ‘PLT-STK’) AND pt.TranQty > 0 THEN pt.tranqty ELSE 0 END) AS RcvdQty,
(CASE WHEN pt.trantype IN (‘STK-MTL’, ‘STK-UKN’, ‘STK-AST’, ‘STK-CUS’, ‘STK-INS’, ‘STK-PLT’) THEN pt.tranqty ELSE 0 END) AS Consqty,
(CASE WHEN pt.trantype IN (‘INS-STK’, ‘PUR-STK’, ‘DMR-STK’, ‘MFG-STK’, ‘ADJ-QTY’, ‘AST-STK’, ‘PLT-STK’) AND pt.TranQty < 0 THEN abs(pt.tranqty) ELSE 0 END)
AS Consqty1, pt.Company, pt.WareHouseCode, pt.PartNum,
pt.Plant, pt.CostID, pt.TranDate, pt.CostMethod,
pt.LotNum,pc.ClassID,pc.Description,
PG.ProdCode,Pg.Description as PGroup,pt.UM,Pt.BinNum
FROM Erp.PartTran AS pt WITH (nolock) INNER JOIN
dbo.Part AS p WITH (nolock) ON p.Company = pt.Company AND p.PartNum = pt.PartNum AND p.QtyBearing = 1 AND p.NonStock = 0 INNER JOIN
Erp.PartClass AS pc WITH (nolock) ON p.Company = pc.Company AND p.ClassID = pc.ClassID and p.ClassID = pc.ClassID INNER JOIN
Erp.PartCost AS PCost ON pt.Company = PCost.Company AND pt.PartNum = PCost.PartNum AND pt.CostID = PCost.CostID LEFT OUTER JOIN
Erp.PartLot AS pl ON pl.Company = pt.Company AND pl.PartNum = pt.PartNum AND pl.LotNum = pt.LotNum LEFT OUTER JOIN Erp.ProdGrup AS PG ON PG.Company = p.Company AND PG.ProdCode = p.ProdCode
WHERE pt.TranDate < = ‘2021-12-31’ and (pt.PartNum = (CASE WHEN ‘’ = ‘’ THEN pt.partnum ELSE ‘’ END))) AS a
GROUP BY Company, Plant, CostID, PartNum, CostMethod, LotNum ,ClassID,Description,
ProdCode,PGroup,UM,WareHouseCode,BinNum) AS Main) AS B INNER JOIN
dbo.Part AS p WITH (nolock) ON p.Company = B.Company AND p.PartNum = B.PartNum INNER JOIN
Erp.PartCost ON B.Company = Erp.PartCost.Company AND B.PartNum = Erp.PartCost.PartNum AND B.CostID = Erp.PartCost.CostID LEFT OUTER JOIN
Erp.PartLot AS pl ON pl.Company = B.Company AND pl.PartNum = B.PartNum AND pl.LotNum = B.LotNum INNER JOIN
Erp.Part ON B.Company = Erp.Part.Company AND B.PartNum = Erp.Part.PartNum
WHERE (B.TotRcvdQty <> 0)
ORDER BY B.Plant, B.PartNum
How do I open this file? It’s not letting me view it. Thank you!
You have to import it into the BAQ designer.