Bev_StockStatus.baq (41.8 KB)
My Stock Status BAQ to calculate Running Total On hand at any given date, and this is the query phrase in case if you are on early than 10.1.400.20 Epicor version:
select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[Part].[UOMClassID] as [Part_UOMClassID],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[InternalUnitPrice] as [Part_InternalUnitPrice],
((case when (TTLQtyToStock.Calculated_TTLQtyToStock) is null then 0 else TTLQtyToStock.Calculated_TTLQtyToStock end)) as [Calculated_ConvertQtyToStock],
((case when (TTLQtyFromStock.Calculated_TTLQtyFromStock) is null then 0 else TTLQtyFromStock.Calculated_TTLQtyFromStock end)) as [Calculated_ConvertQtyFromStock],
((case when (TTLAdjustQty.Calculated_TTLAdjustQty) is null then 0 else TTLAdjustQty.Calculated_TTLAdjustQty end)) as [Calculated_ConvertAdjustQty],
(ConvertQtyToStock - ConvertQtyFromStock + ConvertAdjustQty) as [Calculated_TTLRunningStock],
(TTLRunningStock * Part.InternalUnitPrice) as [Calculated_TTLStockValue]
from Erp.Part as Part
left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(sum(PartTran.TranQty)) as [Calculated_TTLQtyToStock]
from Erp.PartTran as PartTran
where (PartTran.TranType like '%-STK' and PartTran.TranDate <= @CutOffDate)
group by [PartTran].[Company],
[PartTran].[PartNum]) as TTLQtyToStock on
Part.Company = TTLQtyToStock.PartTran_Company
And
Part.PartNum = TTLQtyToStock.PartTran_PartNum
left outer join (select
[PartTran1].[Company] as [PartTran1_Company],
[PartTran1].[PartNum] as [PartTran1_PartNum],
(sum(PartTran1.TranQty)) as [Calculated_TTLQtyFromStock]
from Erp.PartTran as PartTran1
where (PartTran1.TranType like 'STK-%' and PartTran1.TranDate <= @CutOffDate)
group by [PartTran1].[Company],
[PartTran1].[PartNum]) as TTLQtyFromStock on
Part.Company = TTLQtyFromStock.PartTran1_Company
And
Part.PartNum = TTLQtyFromStock.PartTran1_PartNum
left outer join (select
[PartTran2].[Company] as [PartTran2_Company],
[PartTran2].[PartNum] as [PartTran2_PartNum],
(sum(PartTran2.TranQty)) as [Calculated_TTLAdjustQty]
from Erp.PartTran as PartTran2
where (PartTran2.TranType = 'ADJ-QTY' and PartTran2.TranDate <= @CutOffDate)
group by [PartTran2].[Company],
[PartTran2].[PartNum]) as TTLAdjustQty on
Part.Company = TTLAdjustQty.PartTran2_Company
And
Part.PartNum = TTLAdjustQty.PartTran2_PartNum
where (ConvertQtyToStock - ConvertQtyFromStock + ConvertAdjustQty) > 0