Cost Adjustment

Epicor ERP 10
1

Dear experts, After Cost Adjustment there are four lines appearing for same part and quantity is showing double. Could you please explain why four lines appearing refer to attached screen

Cost%20Adjustment
Cost Adjustment.JPG1094×743 79.2 KB

2

Do you use LAST costing? If I recall a cost adj for Last, will first adjust current QOH to zero then re-adj to the new value.

EDIT

Actually, it makes 4 PartTran records and doesn’t affect the QOH

image
image.png956×151 4.64 KB

Note that

  • All 4 parttran records (which are sequentiall numbered), have the same exact SysTime.
  • the Running total is unaffected.
  • In my example, the prior unit cost was $252, and changed to $1.97. Because the UOM is in OZ, bad the $252 cost is per gallon
3

Hi Ckrusen,

How we can identify in PartTran table this is Old Price and this is New price for all parts?

4

Find sequential ADJ-CST trans, and look at the mtlUnitCost (rightmost column in my screenshot)

Here’s what I’d do:

  1. Make a BAQ on the PartTran, with TranType = ADJ-CST. Sorted by partnum, then trannum.
  2. Copy that to excel.
  3. In excel, make that data into a table
  4. Add a column with a formula to see if the trannum of this row equals the trannum of the row above plus 1, to the table.
  5. Filter the table on that calculated column.

Might want to make the formula in #4 check for the sequential tran in the row after, and the same PartNum too.

// assuming trannum is in column A, PartNum in B
=if(or(and(b2=b1, a2=a1+1), and (b2=b3, a2=a3-1)), 1, "")
5

Hi ckrusen,

We have run the Stock Status Report up-to 2019-07-31. In stock status reports unit cost is 24.969 for Part -SNCCLC4400001. We have run the queries PartTran table up-to TranDate <= ‘2019-07-31’ attached in excel file. Quantity is matching with stock Status report but Unit Cost not. We are using FIFO Cost Method for this Part. Could you please let me know how to match the Unit Cost?

PartTran
PartTran.JPG1441×442 82.7 KB

Part-SNCCLC4400001.xlsx (36.7 KB)

6

I believe it will show 1 set of cost adjustments per warehouse/bin combo. Is it that the stock is stored in 2 different bins?

7

Stock Status Report ALWAYS uses the current cost, and not the cost back on the date selected.

(this is my number one pet peeve of Epicor)

1 Like
8

I just noticed from your excel file that you have different plants and warehouses. Do they use different cost files?

Please confirm the following:

  • Cost Method (AVG, LAST, STD, FIFO, etc…)
  • How many Cost files?
  • what generated the 32 sequential ADJ-CST trans (trans 39743-39744) on 9/30/18?
  • How did the TranNum jump from 1441 to 39355 on the same day? Where there really 37,894 trans between them?
9

Hi Ckrusen,

  1. We have 7 Plants and some of the parts are exists in multiple plants.
  2. Cost Method FIFO and Lot FIFO.
  3. Only one cost file
  4. One functional guy did the cost adjustment.
  5. TranNum exists in DB from1441 to 39355.
10

If stock Status Report always uses the current cost and not the cost back date, could you please let me know how we can display same cost in BAQ query?

11

That is going to make tracking everything harder.

Your Excel file shows that most trans did not reference a Lot number (only 23 of the 439 records have a Lot num). I can only guess that was because there was a zero QOH when that adjustment was done.

12

That is very tricky. I did one once, but it only works for us because I didn’t make handle any condition that we wouldn’t have (like lot or S/N, multiple cost files, etc …).

A starting point for the BAQ, is to query the PartTran to find the last (use MAX(PartTran.TranNum) ) where the tran date <= to your desired SSR date. Then find the last transaction that includes a cost. You’d need to also do that for each Lot in each warehouse in each plant.

13

We are totally fed up with Stock Status Report, How they are calculating the unit cost. Already spent more than 60 hours to fetch unit cost.

14

Quantity is coming correct using below query

SELECT Company, Plant, PartNum, WareHouseCode, BinNum, LotNum, TranQty
FROM
(
SELECT Company, Plant, PartNum, WareHouseCode, BinNum, LotNum,
Sum(CASE When TranType = ‘ADJ-QTY’ Then TranQty When TranType LIKE ‘%-STK’ Then TranQty When TranType LIKE ‘STK-%’ Then (TranQty * (-1)) Else 0 End) TranQty
FROM Erp.PartTran
WHERE Company = ‘EPIC01’ AND Plant = ‘70’ AND TranDate <= ‘2019-07-31’ AND (TranType = ‘ADJ-QTY’ OR TranType Like ‘%-STK’ OR TranType Like ‘STK-%’)
Group By Company, Plant, PartNum, WareHouseCode, BinNum, LotNum
) A WHERE TranQty > 0
ORDER BY PartNum

15

I actually have the SSR emailed to me every night. Then if we ever need to know what the cost was on a particular date, I can go back into the email for that date.

And I don’t even have to worry about FIFO or Lot costs. I have one cost for a given part, regardless of the plant, warehouse, lot, etc…

It’s just a guess, but there must be a table that holds the FIFO qtys. Something that tracks like:

Action              QOH   Inv Value  Notes
1. Opening Balance    0          $0
2. Rcv'd 10 @ $15    10        $150
3. Rcv'd 5  @ $18    15        $240 (10@$15 + 5@$18)
4. Issue 7            8        $135 (3@$15 + 5@$18)
5. Rcv'd 10 @ $16    18        $295 (3@$15 + 5@$18 + 10@$16)
4. Issue 10           8        $128 (8@$16)

edit

Full disclosure - I’ve never used FIFO or Lot based costing, just Std, Avg and Last. So I’m going by how I would think that it would have to work.

16

If PartTran table is take care of each transaction then how it is possible epicor is not storing back dated material unit cost. In each transaction have material unit cost.
Cost Adjustment entry detail saved in PartTran.
Each other transaction detail saved in PartTran.
Qty Adjustment detail saved in PartTran table.

Could you please ellaborate if I am wrong?