Last Purchased Date for a Part

Epicor ERP 10
1

Hi everyone,
I am writing a BAQ and one of the fields I need is the last date a part was purchased. I just want to return one date. Can anybody tell me the table and the syntax for just returning the last date please?
Thank you
Adrian.

2

may i ask why not using Purchase advisor Epicor Custom View as it is already done that for you?

image
image.png1432Ɨ408 50 KB

3

Hi Al, thanks for your reply. I am trying to put together a report for all our parts qtys, values etc to run before and after our annual stock check. One of the fields I have been asked to include is the last purchased date. I looked at the purchase adviser as shown in your screen shot, but could not work out how to get the most recent date into the BAQ.

4

the easiest way to create a subquery based on PartTran table filter it to ā€˜PUR-STKā€™ trantype or/and the relevant criteria to your environment i.e. your business task/process rules, then groupby company, partnum, trantype, to return Max. sysdate, this will return the latest transaction date which is in my environment the latest purchase (received to stock) date

image
image.png737Ɨ715 131 KB

1 Like
5

this is a custom tracker view i.e calculate, filter and display these values -on the fly- or/and on an intermediate tables i.e. not direct call from any Db tables, that is why you wont be able to call it, you are doing something similar when you use subquery/groupby but to suit your need within your BAQ

6

Thanks Al, I got it working using your solution. Much appreciated.
Best regards
Adrian.

1 Like
7

To play devils advocate ā€¦ :smiling_imp:

The max PartTran.SysDate might not be the most recent date.

For example, A PO Receipt is entered on 10/1 with a receipt date of 10/1. Then on 11/1 someone opens up the receipt and makes a ā€œcorrectionā€, and then saves it. The SysDate would be 11/1, not 10/1 when it was truly last received.

1 Like
8

And to pile on :smiling_imp:, if you ordered something and havenā€™t received it yet, you will not see it in PartTran. Maybe PODetail is a better (and smaller) table to use?

Mark W.

3 Likes
9

To be a :smiling_imp: back at youā€¦

Define ā€œLast Purchasedā€ ā€¦ PO created, PO confirmed, PO line updated, Receipt received date, ā€¦

1 Like
10

I will play in the middle hereā€¦:face_with_raised_eyebrow:

How about RcvDtl tableā€¦here you would have the date received as your date to look for getting the latest received record of that partā€¦ It may appear on a PODetail line but never receivedā€¦
and I agree using PartTran should be avoided as much as possible as soo many records to go through vs other tables mentionnedā€¦

Pierre

1 Like