Currently the PartBin table stores the last inventory, but I want to calculate the past inventory based on PartTran but I don’t fully understand Epicor’s Trantypes
I will do 1 BPM data for PartTran table every time this table is generated I will record Onhand at the time of PartTran line generation based on PartBin table
select * from PartBin where Company='01' and PartNum='BB0000000075'
I want to know how Epicor calculates past inventory based on PartTran so I can use BAQ passing PartNum and TranDate parameters (when I want to see total inventory of all Bins of Part there)
yes there are a lot but I have not found the correct answer here I just need to determine the condition in PartTran which line is input and which line is output then I can do BAQ to recalculate inventory at any time . and I will also do 1 BPM data for the PartTran table every time a data line is generated will record the inventory at that time so that later I can know the inventory at the time the data line is generated
i would remove the ‘ADJ-CST’ and ‘STK-STK’ transaction types from the case statement.
ADJ-CST transaction really is adjustment to cost, so it would not impact inventory count. yes, it does +/- the qty from old cost to new cost, but net inventory count change would be 0.
STK-STK transaction is moving a qty from one bin to another, so it also give net qty transaction of 0. (-1 qty + 1 qty = 0)
Tran Types that affect inventory in a bin:
a. ADJ-QTY
b. Anything ending in STK
c. Anything beginning with STK
Does it add or subtract from inventory?
a. ADJ-QTY: adds (multiply by positive 1)
b. Anything ending in STK: adds (multiply by positive 1)
c. Anything beginning with STK: subtracts: (multiply by negative 1)
d. Anything else: has no effect (multiply by zero)
e. All of that assumes the Inventory quantity is positive. If it’s negative, then it does the opposite
I’d also note that non-nettable bins do TRACK the quantity in them, but they do not account for any of the on-hand total.
Ah, one more note. Obviously if the part is non-quantity-bearing, it does not do anything to inventory.
STK-STK doesn’t fit in the beginning/ending logic does it? The solution posted here looks wrong as written it has STK-STK in both when clauses, and it seems like it assumes all ADJ-QTY transactions will be positive, to me it reads like this would result in wrong numbers.
Unless I’m missing something.
e: I guess the STK-STKs would cancel each other out? @althomas is right though, probably better to leave them out.
@Banderson Thank you so much this is the answer I needed
with [OnHandCurrent] as
(select
[PartBin].[Company] as [PartBin_Company],
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[DimCode] as [PartBin_DimCode],
(--số lượng tồn kho hiện tại
sum(PartBin.OnhandQty)) as [Calculated_TotalOnHandCurrent]
from Erp.PartBin as PartBin
where (PartBin.PartNum = @PartNum)
group by [PartBin].[Company],
[PartBin].[PartNum],
[PartBin].[DimCode])
select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranDate] as [PartTran_TranDate],
(--xác định dữ liệu nhập xuất
(case
when PartTran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') then -1
when PartTran.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK') then 1
else 0
end) * PartTran.TranQty) as [Calculated_SignedTranQty],
(SUM(SignedTranQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)) as [Calculated_RunningQOH],
[PartTran].[UM] as [PartTran_UM],
[OnHandCurrent].[Calculated_TotalOnHandCurrent] as [Calculated_TotalOnHandCurrent],
[OnHandCurrent].[PartBin_DimCode] as [PartBin_DimCode]
from Erp.PartTran as PartTran
inner join OnHandCurrent as OnHandCurrent on
PartTran.Company = OnHandCurrent.PartBin_Company
and PartTran.PartNum = OnHandCurrent.PartBin_PartNum
where (PartTran.PartNum = @PartNum and PartTran.TranDate <= @TranDateTo)
