Last cost on a large list of parts

Epicor ERP 9
1

Hi all,

So I’ve received a request here from upper management. I was provided a spreadsheet with a very long list of parts and they want me to get the last cost of each out of Epicor. Somehow I need to input this list of parts, hit the PARTTRAN table and gather the last cost. Any ideas? I was thinking that maybe I could with Service Connect but I don’t see PARTTRAN as an option…

Thanks,
Chris

2

What is the backend database?

3

It is SQL

4

Have you looked at the erp.partcost table?

Does that not provide you with you need?

5

I have but I still need to be able to query for over 500 parts. I think parttran may work best since we are setup for average costing.

6

Sure export the partcost table to excel and then with your 500+ list use the excel function vlookup.

7

Ah that may just work… Let me try that. Good idea and thank you!

8

another option is to hijack a UD table, enter all those part numbers in the Key1 field, and then match the UD table against the PartCost table.

I always keep a UD table free for my own nefarious purposes…

9

I think you may be overcomplicating things. PartCost keeps track of Average and Last automatically always, regardless of your costing method.
You should be able to lookup the last cost there and be 100% confident in the results.

10

Right, but I have over 500 parts to look up…

11

Genius! I’ll populate the UD table using DMT… Now why didn’t I think of this? Thank you

12

We have found that the Average Cost is only updated for Stock Receipts. If you ship direct from a job or drop ship from a PO, the Average Cost will not include these transactions.

Mark W.

13

Yikes! Learn something new every day! So yes, PartTran it is then.

Jason Woods
http://LinkedIn.com/in/jasoncwoods

14

Mark. That should be revised to Receipts and returns (issue reversals).

A material return from a job will revise the costs as well. Sometimes adversely.

Any transaction that adds a quantity to inventory (with the exception of a quantity adjustment) will affect the costing of that inventory. Whether it be manufactured or purchased.

Any transaction that relieves inventory will NOT affect the costing of that inventory but moves the existing costing to the receiving source (such as Cost of sales or Work In Process).

The only other inventory transaction that affects the inventory costing is a cost adjustment (obviously). Any adjustment to inventory moves the costs to the inventory adjustment account.

Charlie

1 Like
15

Agreed which is why I set criteria based on TranType and also TranQty > 0.

Chris