We have a custom BAQ Inventory report that gives us the QOH for all parts. However, this does not go into serial numbers. It should be pretty easy to add the serials on to this report. This BAQ feeds a BAQ Report, but the data is helpful by itself.
select
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[Warehse].[Description] as [Warehse_Description],
[PartBin].[BinNum] as [PartBin_BinNum],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[ClassID] as [Part_ClassID],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[PartLot].[LotNum] as [PartLot_LotNum],
[PartLot].[PartLotDescription] as [PartLot_PartLotDescription],
[Part].[TypeCode] as [Part_TypeCode],
[PartLot].[LastRefDate] as [PartLot_LastRefDate],
[PartLot].[FirstRefDate] as [PartLot_FirstRefDate],
(PartCost.LastLaborCost+PartCost.LastBurdenCost+PartCost.LastMaterialCost+PartCost.LastSubContCost+PartCost.LastMtlBurCost) as [Calculated_TotalLastCost]
from Erp.Part as Part
inner join Erp.PartBin as PartBin on
Part.Company = PartBin.Company
and Part.PartNum = PartBin.PartNum
inner join Erp.PartLot as PartLot on
PartBin.Company = PartLot.Company
and PartBin.PartNum = PartLot.PartNum
and PartBin.LotNum = PartLot.LotNum
inner join Erp.PartCost as PartCost on
PartLot.Company = PartCost.Company
and PartLot.PartNum = PartCost.PartNum
inner join Erp.PartWhse as PartWhse on
PartBin.Company = PartWhse.Company
and PartBin.PartNum = PartWhse.PartNum
and PartBin.WarehouseCode = PartWhse.WarehouseCode
and ( PartWhse.OnHandQty > 0 )
inner join Erp.Warehse as Warehse on
PartWhse.Company = Warehse.Company
and PartWhse.WarehouseCode = Warehse.WarehouseCode
The only thing this BAQ looks for is that there is some quantity on hand. I hope this helps!
Good luck!
I appreciate the answer but it doesn’t help what I need.
I need to be able to see what the On hand qty and demand qty were at a certain date in time.
So if I needed to know each part’s on hand on January 31, 2024, I would run the dashboard and it would give me what the on hand and demand are at the end of january instead of what it is right now.
Does that make sense?
I feel like I am going to have to dive into part transaction history tracker and somehow build the quantities on the fly for the date indicated.
If I do, I am unsure of the trantypes that affect part quantity specifically.
If I am looking for too much work and there is a better way, somebody please tell me!
Well, the way to get to past states is by starting from current on hand and working backwards from Erp.PartTran.
Just don’t take the transaction quantity at face value. Positive/negative in some cases depends on the perspective of the transacting process rather than inventory’s perspective, and things that don’t change on hand quantities (like cost adjust) record an affected quantity in the same field.
That’s the only hard part. From there you can rewind to past state with a relative windowed function in SQL over ROWS, power query / power bi have lots of options, if you copy to excel there’s sumif, etc.
I have a not quite finished thing that’s been on the back burner for a bit. For all I know there’s a map of the various meanings of TranQty tucked away in the N*10^5 pages of documentation that I overlooked. I didn’t see anything at the time so I started mapping by hand.
This is what I came up with. Keep in mind that even if this was substantially tested, testing would be limited to the scope of our own transaction history.
case
when PartTran.TranClass = 'A'
then case
when PartTran.TranType = 'ADJ-QTY'
then 1
when PartTran.TranType = 'ADJ-CST'
then 0
when PartTran.TranType = 'ADJ-PUR'
then 0
when PartTran.TranType = 'MFG-VAR'
then 0
when PartTran.TranType = 'STK-PLT'
then 1
when PartTran.TranType = 'STK-STK'
then 1
else null
end
when PartTran.TranClass = 'S'
then case
when PartTran.TranType = 'MFG-CUS'
then 0
when PartTran.TranType = 'MFG-VEN'
then 1
when PartTran.TranType = 'STK-CUS'
then -1
when PartTran.TranType = 'STK-KIT'
then -1
when PartTran.TranType = 'KIT-CUS'
then 0
else null
end
when PartTran.TranClass = 'R'
then case
when PartTran.TranType = 'DMR-STK'
then null
when PartTran.TranType = 'INS-MTL'
then null
when PartTran.TranType = 'INS-STK'
then 1
when PartTran.TranType = 'INS-SUB'
then null
when PartTran.TranType = 'MFG-PLT'
then null
when PartTran.TranType = 'MFG-STK'
then 1
when PartTran.TranType = 'MFG-WIP'
then 0
when PartTran.TranType = 'PLT-STK'
then null
when PartTran.TranType = 'PUR-INS'
then null
when PartTran.TranType = 'PUR-MTL'
then null
when PartTran.TranType = 'PUR-STK'
then 1
when PartTran.TranType = 'PUR-SUB'
then null
when PartTran.TranType = 'PUR-UKN'
then null
when PartTran.TranType = 'RMA-INS'
then null
when PartTran.TranType = 'STK-DMR'
then null
when PartTran.TranType = 'SVG-STK'
then 1
else null
end
when PartTran.TranClass = 'X'
then case
when PartTran.TranType = 'ADJ-MTL'
then 0
when PartTran.TranType = 'ADJ-SUB'
then null
else null
end
when PartTran.TranClass = 'I'
then case
when PartTran.TranType = 'ASM-INS'
then 0
when PartTran.TranType = 'INS-ASM'
then 0
when PartTran.TranType = 'INS-REJ'
then null
when PartTran.TranType = 'MTL-INS'
then 0
when PartTran.TranType = 'PLT-ASM'
then null
when PartTran.TranType = 'PLT-MTL'
then null
when PartTran.TranType = 'STK-ASM'
then -1
when PartTran.TranType = 'STK-INS'
then null
when PartTran.TranType = 'STK-MTL'
then -1
when PartTran.TranType = 'STK-SRV'
then -1
when PartTran.TranType = 'STK-UKN'
then 0
when PartTran.TranType = 'SUB-DMR'
then null
when PartTran.TranType = 'SUB-INS'
then null
when PartTran.TranType = 'WIP-MFG'
then 0
else null
end
else null
end * PartTran.TranQty
Checkout table SNTran - it appears like it could help you out.
There is no quantity field because each transaction is quantity one.
PartTranSNTran is another, but summarized. These tables are used in SN Tracker to show the activity of each serial number.
The bookDtl table is a good table that keeps track of the changes of the orderrel and OrderDtl tables, not part transactions.
The demand quantity is not one that you can reach back and capture.
If you wanted to track demand over time, look into the PartDtl which will show you supply and demand. Using an executive query you could collect the demand records over time to report against.
We snapshotted the tables we wanted and added a snapshot date column to the front of the table. That was the best way I found to keep inventory, or whatever, in an easy way. We used a SQL job to do it.