Last cost on a large list of parts


(Chris Simpson) #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…


(Ken Nash) #2

What is the backend database?

(Chris Simpson) #3

It is SQL

(Ken Nash) #4

Have you looked at the erp.partcost table?

Does that not provide you with you need?

(Chris Simpson) #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.

(Ken Nash) #6

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

(Chris Simpson) #7

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

(Ernie Lowell) #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…

(Jason Woods) #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.

(Chris Simpson) #10

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

(Chris Simpson) #11

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

(Mark Wonsil) #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.

(Jason Woods) #13

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

Jason Woods

(Charlie Smith) #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.


(Chris Simpson) #15

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