Hi All, we are newly live on Epicor and I am trying to reconcile the stock status report to my Inventory account.
The main thing that is throwing me off is that one of my parts is showing up on the report with a qty of 15 but a cost of 0. The part isn’t configured any differently than similar parts and I can’t find any odd transactions. The part got loaded with an initial qty of 3 units at like $400 each. We then purchased, received and processed the invoice for 12 more in the month. None have been issued out or have demand for them. When I run the report for the first day of the month it agrees to my starting balance for the part.
Stock Status can do the quantity in time, it’s the costing that doesn’t work. Strategically, you’d want to start with a Stock Status in a point of time and then back off PartTransactions until to get to the date you need.
If you print Stock Status with “Activity From Cut Off Date” checked, then it will show all the transactions that it thinks occurred since the date you enter in the as of date (so enter a date that is the last time your Stock Status matched your inventory GL, if that is possible). Sometimes one of these transactions will point to a problem.
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.
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
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.
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.
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
