I am trying to develop a dashboard to look at inventory transactions tied to a specific part number within a specific warehouse. The main query is showing an on-hand quantity of 34 for part ABC. The second query is records from the PartTran table and when summing the PartTran.TranQty column returns 40 for part ABC. There is one transaction that is STK-CUS with a TranQty of 3, and one transaction that is MFG-STK. I know the STK-CUS should be removed from the PartTran.TranQty column sum so that should leave an on-hand quantity of 37. There is only a single bin in this warehouse flagged as non-nettable, so the PartWhse.NonNettableQty field always matches the PartBin.OnhandQty field.
What logic am I missing to get the PartTran.TranQty sum to equal the PartWhse.NonNettableQty?
I have a main query that publishes and a second query that subscribes to the main on PartWhse.WarehouseCode, PartWhse.PartNum, and PartBin.BinNum to the relative
PartTran fields.
The BAQ that drives the main is as follows:
“select
[PartWhse].[WarehouseCode] as [PartWhse_WarehouseCode],
[PartWhse].[PartNum] as [PartWhse_PartNum],
[PartWhse].[NonNettableQty] as [PartWhse_NonNettableQty],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[PartBin].[BinNum] as [PartBin_BinNum]
from Erp.PartWhse as PartWhse
inner join Erp.PartBin as PartBin on
PartWhse.Company = PartBin.Company
and PartWhse.PartNum = PartBin.PartNum
and PartWhse.WarehouseCode = PartBin.WarehouseCode
where (PartWhse.WarehouseCode = ‘RENT’)
order by PartWhse.PartNum”
The subscribed query is as follows:
“select
[PartTran].[WareHouseCode] as [PartTran_WareHouseCode],
[PartTran].[EntryPerson] as [PartTran_EntryPerson],
[PartTran].[SysDate] as [PartTran_SysDate],
[PartTran].[TranDate] as [PartTran_TranDate],
(Ice.StringTime(PartTran.SysTime, ‘HH:MM:SS’)) as [Calculated_TranTime],
[PartTran].[TranNum] as [PartTran_TranNum],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[BinNum] as [PartTran_BinNum],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[TranQty] as [PartTran_TranQty],
[PartTran].[TranReference] as [PartTran_TranReference],
[PartTran].[BinNum2] as [PartTran_BinNum2]
from Erp.PartTran as PartTran
where PartTran.WareHouseCode = ‘RENT’
order by PartTran.TranReference, PartTran.TranNum”
Was the STK-CUS a return? (resulting in a positive value)?
Or was it a Shipment to the Customer? (which SHOULD result in a negative value)?
If it should be a negative… then that could be your difference. Change the +3 to -3 and your new total is 34.
Its confusing because the PartTran table shows it as a positive number (the transaction was for a positive number of parts)… but the impact on your onhand inventory is a negative.
If you look at the image summation it shows 40 as the column total. The STK-CUS transaction was associated with a shipment to a customer. Removing this 3 gives a total of 37 while the part warehouse shows 34.
Originally I filtered out the STK-CUS transactions because I didnt want them to show in the total. I removed my trantype filters to show all activity once I found this variance. This is not the only part that has a variance between the part transaction summations and the part warehouse quantity. I must be missing something but I cant figure out what it is.
I understand the sum total results in 40. However, the STK-CUS PartTran transaction is a positive number because it was a positive number of parts being shipped.
BUT, it is a “negative” when it comes to how many parts you still have OnHand.
Think of it as a financial transaction… PartTran (debit)… Inventory (credit)… the equation balances.
If you make a calculated field, you can flip that transaction to a negative:
Bad choice of words, I meant it in the sense of subtracting. But I understand where my logic was flawed now and you helped me get there. For illustrative purposes if the stk-cus transaction was the last transaction in the parttran records, the total would have been 37 before it occured, once it occured it would drop to 34. I was having a problem seeing that mentally. Thanks for your help.
