@Mike We have the exact same issues, did you end up finding a solution to your issue?
As Mark said, the SSR is only “date aware” for quantity. The current cost is used, regardless of the date entered.
If you use any cost method other than STD, the SSR should only be run for Qty’s, not inventory value.
One thing we do is have the SSR run every night at 12:01 AM, with the report emailed to me. I file these away (with an Outlook rule), and then can recall them at a later date.
There are some shortcomings… if a transaction is back dated (say entered on 3/4/19, but with a Tran date of 3/1/19), then running the SSRs that were emailed to you on 3/1, 3/2, and 3/3 would be “incorrect” as the don’t include that Tran you entered on 3/4. Running the SSR on 3/4 (with a date of 3/2) woukd include the trans.
@Wasey_Shah I started to try to make a BAQ to duplicate the stock status report but never finished. To fill the gap we are doing an inventory rollforward in excel every month which pretty much produces the report at month end for us.
For the rollforward, we are mostly just pulling the transactions from a BAQ on either PartTran or TranGLC. Probably PartTran. Then just doing a few pivot tables to get qty and cost of the additions and subtractions and calculating out the final inventory. Then verifying the total dollars matches the G/L and the total qty(by part) matches the stock status report.
@Mike
i have created a BAQ to calculate stock qty at any given date, i worked out the logic based on Epicor part transaction history_running Total Qty which represent the stock level at each TranDate as,
at any given transaction date, the total stock qty =
[Total TranQty of all transaction types %-STK where sysdate less than or equal to this given transaction date] - [Total TranQty of all transaction types STK-% where sysdate less than or equal to this given transaction date]+[Total TranQty of all transaction types ADJ-STK where sysdate less than or equal to this given transaction date]
i can share it here if you want, and you can alter it to add the cost bit the way you want.
I would be interested in the BAQ. Thanks!
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
3 Likes
Thank you, I will run and test the BAQ. Thank you for sharing 
By any chance have you attempted to create a BAQ that mimics journal tracker. For the purpose of reconciliation I need to pull out detailed entries that have hit the general ledger. The problem I face journal entries are posted in batches, in order to see those transaction I need to drill down into part cost, invoice, labor. The end goal is to get a detailed listing of transactions from the general journal without having to drill into each batch.
Thanks
You had attached a BAQ below but I don’t see where the criteria mentioned above is applied to the BAQ?
Do you mean journals that are generated by the Capture COS/WIP Process? There is a flag in the COS/WIP posting rules where you can select if transactions are summarized and only the net debit/credit posted to the GL, or if every transaction is posted to the GL separately to give you full visibility directly from the journals.
the criteria exist in each one of the three subquery level, it is clear in the attached SQL code as well:
no i have not, my company is not using the finance module yet, but you can use the concept of my BAQ and construct yours to gather any data you want in the past if you know what transaction type are you looking for
Yes, that looks correct. It’s been a year or so since we looked at this, but the application help should have more details.
Dear Baeisa, We have checked that it is working fine. But when we are trying to add MtlUnitCost and MtlBurUnitCost that doesn’t match with Stcok Status report.
Could you suggest, how to add costing part?
Hi @Hari_Dutt,
finance module is not set in my environment, so i can not help you on that, however if i were you i would validate what cost method you want first, then see where Epicor is calculating it, even if that happened -on the fly-, it is much easier to capture Epicor view values at any UI and save it on UD fields than trying to work out the logic behind each method, i have developed something similar to capture Quote Cost/Unit price values per quantity which Epicor calculate it -on the fly- and view it on the Quote UI only when quantity line selected on grid.
Now with regard to the multiple plants issue, try to add PlantID as another parameter to the query and use it as filter in each of sub-query as well as the top one.
We are using FIFO Method and please let me know how they are calculating ?
In Part Tracker cost is showing 22.880 but in Stock Status Report is appearing 22.873. The part is setup as FIFO.
Hello this BAQ is great. What would I need to change in it to get negative results as well?