Inventory Aging Report

Hari_Dutt · August 1, 2019, 1:30pm

I am working on a request, from our CFO for an Aged Inventory Report. We are looking for part which is no longer used (OnHandQty) with aging for all plants and parts.

Anyone have any samples, they can share?

SELECT DISTINCT PB.PartNum, PB.WarehouseCode, PB.BinNum, PB.OnhandQty, PB.DimCode UM, PB.LotNum ,
Cast(Round(PT.MtlUnitCost,2) as decimal(17,2)) ‘Unit Cost’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 0 AND (DATEDIFF(Day, PT.TranDate, GETDATE())) <= 30 THEN CAST(PT.TranQty as Decimal(10,3)) ELSE 0 END) ‘30 Days’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 31 AND (DATEDIFF(Day, PT.TranDate, GETDATE())) <= 60 THEN CAST(PT.TranQty as Decimal(10,3)) ELSE 0 END) ‘60 Days’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 61 AND (DATEDIFF(Day, PT.TranDate, GETDATE())) <= 90 THEN CAST(PT.TranQty as Decimal(10,3)) ELSE 0 END) ‘90 Days’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 91 AND (DATEDIFF(Day, PT.TranDate, GETDATE())) <= 120 THEN CAST(PT.TranQty as Decimal(10,3)) ELSE 0 END) ‘120 Days’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 121 AND (DATEDIFF(Day, PT.TranDate, GETDATE())) <= 180 THEN CAST(PT.TranQty as Decimal(10,3)) ELSE 0 END) ‘180 Days’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 181 AND (DATEDIFF(Day, PT.TranDate, GETDATE())) <= 360 THEN CAST(PT.TranQty as Decimal(10,3))ELSE 0 END) ‘360 Days’,
(CASE WHEN (DATEDIFF(Day, PT.TranDate, GETDATE())) >= 361 THEN CAST(PT.TranQty as Decimal(10,3)) ELSE 0 END) ‘>360 Days’
FROM Erp.PartBin PB
INNER JOIN Erp.PartTran PT ON (PB.Company = PT.Company AND PB.PartNum = PT.PartNum AND PB.LotNum = PT.LotNum AND PB.WarehouseCode = PT.WarehouseCode AND PB.BinNum = PT.BinNum AND TranQty > 0)
INNER JOIN Erp.PartCost PC ON PB.Company = PC.Company AND PB.PartNum = PC.PartNum
WHERE PB.Company = ‘EPIC01’ AND PB.OnhandQty > 0

tsmith · August 1, 2019, 1:57pm

Have you looked at the Slow Moving Stock and/or Inventory Usage reports?

A.Baeisa · August 1, 2019, 1:59pm

Hi Hari,
this is a BAQ example found on EUG, i have altered a bit to suit my use, try it, it may help you constructing your BAQ report.

Part_Usage_3.baq (62.0 KB)

select 
	[PartPlant].[Plant] as [PartPlant_Plant],
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[ClassID] as [Part_ClassID],
	[Part].[ProdCode] as [Part_ProdCode],
	[PartPlant].[PrimWhse] as [PartPlant_PrimWhse],
	[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
	[PartPlant].[MaximumQty] as [PartPlant_MaximumQty],
	[PartPlant].[SafetyQty] as [PartPlant_SafetyQty],
	[PartPlant].[MinOrderQty] as [PartPlant_MinOrderQty],
	[PartPlant].[LeadTime] as [PartPlant_LeadTime],
	[PartPlant].[DaysOfSupply] as [PartPlant_DaysOfSupply],
	[PartPlant].[MinMfgLotSize] as [PartPlant_MinMfgLotSize],
	[PartPlant].[SourceType] as [PartPlant_SourceType],
	[PartPlant].[TransferPlant] as [PartPlant_TransferPlant],
	(sum( PartTran1.Calculated_ShippedQty_30 )) as [Calculated_QtyUsed_30],
	(sum( PartTran1.Calculated_ShippedQty_60 )) as [Calculated_QtyUsed_60],
	(sum( PartTran1.Calculated_ShippedQty_180 )) as [Calculated_QtyUsed_180],
	(sum( PartTran1.Calculated_ShippedQty )) as [Calculated_QtyUsed],
	(sum( PartTran1.Calculated_PurchQty )) as [Calculated_QtyPurch],
	(max( LastPurchased.Calculated_LastPurchDate )) as [Calculated_LastPurch],
	[OnHand].[Calculated_OnHand] as [Calculated_OnHand],
	[OnHand].[Calculated_Allocated] as [Calculated_Allocated],
	[PartWhse_Demand].[Calculated_Demand] as [Calculated_Demand],
	(OnHand.Calculated_OnHand * (PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost)) as [Calculated_OnHandCost],
	[PartCost].[AvgLaborCost] as [PartCost_AvgLaborCost],
	[PartCost].[AvgBurdenCost] as [PartCost_AvgBurdenCost],
	[PartCost].[AvgMaterialCost] as [PartCost_AvgMaterialCost],
	[PartCost].[AvgSubContCost] as [PartCost_AvgSubContCost],
	[PartCost].[AvgMtlBurCost] as [PartCost_AvgMtlBurCost],
	(QtyUsed / OnHand.Calculated_OnHand) as [Calculated_InvTurn]
from Erp.Part as Part
inner join Erp.PartPlant as PartPlant on 
	Part.Company = PartPlant.Company
And
	Part.PartNum = PartPlant.PartNum

left outer join  (select 
	[PartTran].[Company] as [PartTran_Company],
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[Plant] as [PartTran_Plant],
	[PartTran].[TranDate] as [PartTran_TranDate],
	[PartTran].[TranType] as [PartTran_TranType],
	(datediff ( month, PartTran.TranDate, Constants.Today)) as [Calculated_Age],
	((case when PartTran.TranType = 'STK-CUS' or PartTran.TranType = 'STK-ASM' or PartTran.TranType = 'STK-MTL' then 
 (case when Age < 13 then (SUM(PartTran.TranQty)) end) end)) as [Calculated_ShippedQty],
	((case when PartTran.TranType = 'PUR-STK' then (case when Age < 13 then (SUM(PartTran.TranQty)) end) end)) as [Calculated_PurchQty],
	(datediff ( day, PartTran.TranDate, Constants.Today)) as [Calculated_AgeDays],
	((case when PartTran.TranType = 'STK-CUS' or PartTran.TranType = 'STK-ASM' or PartTran.TranType = 'STK-MTL' then (case when AgeDays < 61 then (SUM(PartTran.TranQty)) end) end)) as [Calculated_ShippedQty_60],
	((case when PartTran.TranType = 'STK-CUS' or PartTran.TranType = 'STK-ASM' or PartTran.TranType = 'STK-MTL' then (case when AgeDays < 181 then (SUM(PartTran.TranQty)) end) end)) as [Calculated_ShippedQty_180],
	((case when PartTran.TranType = 'STK-CUS' or PartTran.TranType = 'STK-ASM' or PartTran.TranType = 'STK-MTL' then (case when AgeDays < 31 then (SUM(PartTran.TranQty)) end) end)) as [Calculated_ShippedQty_30]
from Erp.PartTran as PartTran
group by [PartTran].[Company],
	[PartTran].[PartNum],
	[PartTran].[Plant],
	[PartTran].[TranDate],
	[PartTran].[TranType])  as PartTran1 on 
	PartPlant.Company = PartTran1.PartTran_Company
And
	PartPlant.Plant = PartTran1.PartTran_Plant
And
	PartPlant.PartNum = PartTran1.PartTran_PartNum

left outer join  (select 
	[PartBin].[Company] as [PartBin_Company],
	(SUBSTRING(PartBin.WarehouseCode, 1, 2)) as [Calculated_Plant],
	[PartBin].[PartNum] as [PartBin_PartNum],
	(sum( PartBin.OnhandQty )) as [Calculated_OnHand],
	(sum( PartBin.AllocatedQty )) as [Calculated_Allocated]
from Erp.PartBin as PartBin
group by [PartBin].[Company],
	(SUBSTRING(PartBin.WarehouseCode, 1, 2)),
	[PartBin].[PartNum])  as OnHand on 
	OnHand.PartBin_Company = PartPlant.Company
And
	OnHand.Calculated_Plant = PartPlant.Plant
And
	OnHand.PartBin_PartNum = PartPlant.PartNum

left outer join Erp.PartCost as PartCost on 
	PartPlant.Company = PartCost.Company
And
	PartPlant.Plant = PartCost.CostID
And
	PartPlant.PartNum = PartCost.PartNum

left outer join  (select 
	[PartWhse].[Company] as [PartWhse_Company],
	[PartWhse].[PartNum] as [PartWhse_PartNum],
	(SUBSTRING(PartWhse.WarehouseCode, 1, 2)) as [Calculated_Plant],
	(sum( PartWhse.DemandQty )) as [Calculated_Demand]
from Erp.PartWhse as PartWhse
group by [PartWhse].[Company],
	[PartWhse].[PartNum],
	(SUBSTRING(PartWhse.WarehouseCode, 1, 2)))  as PartWhse_Demand on 
	PartWhse_Demand.PartWhse_Company = PartPlant.Company
And
	PartWhse_Demand.Calculated_Plant = PartPlant.Plant
And
	PartWhse_Demand.PartWhse_PartNum = PartPlant.PartNum

left outer join  (select 
	[PartTran2].[Company] as [PartTran2_Company],
	[PartTran2].[Plant] as [PartTran2_Plant],
	[PartTran2].[PartNum] as [PartTran2_PartNum],
	(max(PartTran2.TranDate  )) as [Calculated_LastPurchDate]
from Erp.PartTran as PartTran2
 where (PartTran2.TranType = 'PUR-STK')
group by [PartTran2].[Company],
	[PartTran2].[Plant],
	[PartTran2].[PartNum])  as LastPurchased on 
	PartPlant.Company = LastPurchased.PartTran2_Company
And
	PartPlant.Plant = LastPurchased.PartTran2_Plant
And
	PartPlant.PartNum = LastPurchased.PartTran2_PartNum

 where (Part.InActive = false  and Part.TypeCode = 'P')
group by [PartPlant].[Plant],
	[Part].[PartNum],
	[Part].[PartDescription],
	[Part].[ClassID],
	[Part].[ProdCode],
	[PartPlant].[PrimWhse],
	[PartPlant].[MinimumQty],
	[PartPlant].[MaximumQty],
	[PartPlant].[SafetyQty],
	[PartPlant].[MinOrderQty],
	[PartPlant].[LeadTime],
	[PartPlant].[DaysOfSupply],
	[PartPlant].[MinMfgLotSize],
	[PartPlant].[SourceType],
	[PartPlant].[TransferPlant],
	[OnHand].[Calculated_OnHand],
	[OnHand].[Calculated_Allocated],
	[PartWhse_Demand].[Calculated_Demand],
	(OnHand.Calculated_OnHand * (PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost)),
	[PartCost].[AvgLaborCost],
	[PartCost].[AvgBurdenCost],
	[PartCost].[AvgMaterialCost],
	[PartCost].[AvgSubContCost],
	[PartCost].[AvgMtlBurCost]

Vinaykamboj · August 1, 2019, 3:04pm

Slow Moving Stock Report is the way to go. Before using that report you will have to setup the parameters under Inventory>Setup>Stock Provision Report Format Maintainence.

We use that in combination with some dashboards we developed for related purposes.

Vinay Kamboj

Hari_Dutt · August 4, 2019, 7:53am

Dear Baeisa, I have gone through query but we found some wrong relationships as mentioned below

PartPlant.Plant = PartCost.CostID

Left Outer Join Erp.PartCost as PartCost on PartPlant.Company = PartCost.Company And PartPlant.Plant = PartCost.CostID And PartPlant.PartNum = PartCost.PartNum

PartWhse_Demand.Calculated_Plant (WarehouseCode) = PartPlant.Plant

as PartWhse_Demand on PartWhse_Demand.PartWhse_Company = PartPlant.Company And PartWhse_Demand.Calculated_Plant = PartPlant.Plant And PartWhse_Demand.PartWhse_PartNum = PartPlant.PartNum

could you please send EUG Baq?

Hari_Dutt · August 4, 2019, 9:48am

Dear Vinay, Please suggest me how to print this report for all plants?

RobHunter · August 5, 2019, 1:32am

We use Lot number for stock aging and report across all plantsAgedByRep.baq (50.1 KB)

A.Baeisa · August 5, 2019, 6:49am

Hi Hari,
i have not touched this part, so correct what you find wrong and test it in your environment is what i would do, finance/material department never used it, i imported it and modified in my Test environment in case if they ask for anything similar, but never actually test it against any data.

Vinaykamboj · August 6, 2019, 3:54pm

Hi Hari,

We are a single site setup and cannot offer any guidance on multi site/plants. Probably you would have to log into each plant separately.

Vinay Kamboj

Hari_Dutt · August 7, 2019, 8:11am

What is the relation between below tables

PartBin
PartCost
PartTran

Hari_Dutt · August 18, 2019, 7:46am

We are looking for Part which is no longer used (OnHandQty) with aging for all plants and parts. Please suggest anyone. We required the following columns
ProdCode, PartCode, PartDescription, Location, Warehouse, Bin, UOM, OnHandQty, Rate, Amount, Burden, Tot. Amount, Days in Stock, 30 Days 60 Days 90 Days 120 Days 180 Days 360 & Above

faez · June 14, 2020, 6:30am

Hi Vinay,

Where can I find details or steps to setup parameters in Stock Provision Report Format Maintenance.

Regards,
Faez

prash172 · June 14, 2020, 10:31am

Slow moving stock report will help to get the details. The aging bucket can be defined.
Default report provides below columns.

Slow moving format can be defined from “Stock provision report format maintenance”
This can be opened from mouse right click on the “Slow moving format code” drop down.

harry · June 15, 2020, 3:41am

This is how we are setting up aging buckets for the Slow Movement Stock. You might want to set up which transactions would be the conditions to identify last used date as well on Transactions tab.

Naveen_Kumaar_P · March 1, 2022, 12:11pm

Hi All,

Site wise coming Inventory Ageing Report, need all sites(Plant) in one report.