Stock Status Report

Dear Experts, how to create BAQ to determine what inventory hasn’t had any activity in a certain period of time with aging ?

The partbin or partwhse tables have current stock as well as current demand. Partdtl has demand and supply. Parttran has historical usage.

3 Likes

can anyone have any sql or baq to check part status as on date?

1-PartTran.baq (18.9 KB)

Here is a simple BAQ to show the last Part Tran date for parts.

There is also a feature called Slow Moving Stock that you may use in Epicor.

have you seen this post

We have multiple plants under one company, Please suggest how to check all plant stock status in single report. Epicor standard Stock Status Report only works for individual plant wise. Please find below Stock Status Report format.

BAQ Report-RptInvAged (Slow Moving Stock Report)_76333.xlsx (11.2 KB)

Does any one know, how to get part stock status (baq or sql) for multiple plants with following columns

PartNum, Description, PartClass, WarehouseCode, BinNum, LotNum, OnHandQty, UnitCost, Mtl. Burden, Extended Cost., LastTranDate

SELECT PT.Company, PL.Name, PT.PartNum,PT.WareHouseCode, PT.LotNum, PT.UM,
CAST(ROUND(Sum(PF.TranQty), 2) AS DECIMAL(10,2)) OnHandQty
FROM Erp.PartTran PT
INNER JOIN Erp.Plant PL ON PT.Company = PL.Company AND PT.Plant = PL.Plant
INNER JOIN Erp.PartCost PC ON PT.Company = PC.Company AND PT.PartNum = PC.PartNum AND PT.CostID = PC.CostID
INNER JOIN
(
SELECT Company, PartNum, TranNum, Sum(TranQty) TranQty
FROM Erp.PartFIFOTran
Where TranType in (‘ADJ-PUR’, ‘ADJ-QTY’, ‘ADJ-CST’, ‘INS-STK’, ‘MFG-STK’, ‘PUR-STK’, ‘PLT-STK’, ‘STK-CUS’, ‘STK-KIT’, ‘STK-MTL’, ‘STK-PLT’, ‘STK-UKN’) AND
TranDate <= ‘2019-07-31’
Group By Company, PartNum, TranNum
) PF ON PT.Company = PF.Company AND PT.PartNum = PF.PartNum AND PT.TranNum = PF.TranNum
WHERE PT.Company = ‘EPIC01’ AND PT.Plant = ‘70’ AND TranDate <= ‘2019-07-31’ AND NULLIF(BinNum, ‘’) IS NOT NULL
Group By PT.Company, PL.Name, PT.PartNum, PT.WareHouseCode, PT.LotNum, PT.UM
Having Sum(PF.TranQty) != 0
ORDER BY PT.PartNum

It is working fine, But how to add cost in this query.

You could use the Unit Costs on the PartTran table from each parts latest/final transaction in the date range. This will give you the parts unit cost from its default costing method at the time your date range is set to.

But how to get Material Burden value?

PartTran.MtlBurUnitCost could work.

To add on to what Adam said, I have used two BAQ’s to pull this data. 1) Pulls the last transaction date by part. 2) uses this result set to pull the transaction data. Thus by part I can show the cost elements before a certain date. LastCost.baq (25.8 KB)

Hope this helps

1 Like

Dear All,

Ageing stock status needed. include available quantity and cost on date filter lot wise.

Hi Naveen,

Please find the below query. This will be sed for calculating the Aging with Avg Cost lot wise. Kindly change the aging buckets as per your requirement.

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 + 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 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-09-30'  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

1 Like

Are you talking the Epicor base stock status report or the SQL from Prince or the BAQ?

I was able to create a BAQ and dashbaord that shows last transaction date, last transaction type and on hand cost by part number. When we compare the total value it does not match the Stock Status report value because my query is including Supplier Managed Inventory and the report does not include SMI. Does anyone know how the Stock Status report excludes SMI? Trying to keep one row per part but when I introduce WhseBin.BinType i get multiple rows because of the bins.

@prince_gs2010 Avg Cost you are showing. can you tell me how you will make Lot avg cost for the last transaction date also you have taken the lot material cost? the backdated report needs to take a lot avg cost and will not come correct.

Regards,
Naveen Kumaar P