Last Received Date Report

Hello,

In Visual 6.5.2 I'm trying to find out when we last purchase an inventory item so I can determine obsolescence. I tried the Inactivity Report but it does not tell me a date. Is there another method / report I can use?

Best regards,

Dave

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

unknown

There is a purchase history button in material planning, or if you are referring to the tables - part id - purchase order line aught to give you the data

unknown

Thank you Patric,

I'm trying to get all of our parts (25000) in one list that I can manipulate in excel. Will either of these suggestions do this?

Dave

unknown

You might try Info/Inventory Transactions in Material Planning. That's what we use to check material usage.

0712131256598360.doc

unknown

crystal? access? sqltalk?

unknown

Thanks Tom, I tried, but it still won't give me Last Purchased Date

Dave

unknown

Access or Excel. I tried "Data from other sources" and was able to make the DB connection in excel, but could not get it to generate the report I needed.

Dave

unknown

I don't see a canned report. Seems like a straight forward report to make tho if you know Crystal.

unknown

go directly to the purc_order_line table, export all the data sorted by part_id, then manipulate from there -

unknown

Thanks Patric,

That seams to have given me something to work with, all 429938 rows.

Dave

unknown

SELECT
[VMFG].[dbo].[PURC_ORDER_LINE].[PURC_ORDER_ID]
, [VMFG].[dbo].[PURC_ORDER_LINE].[LINE_NO]
, [VMFG].[dbo].[PURC_ORDER_LINE].[PART_ID]
, [VMFG].[dbo].[PURC_ORDER_LINE].[LAST_RECEIVED_DATE]
,[vmfg].[dbo].[PURCHASE_ORDER].CREATE_DATE as Last_Order_Date
FROM [VMFG].[dbo].[PURC_ORDER_LINE] Inner join vmfg.dbo.PURCHASE_ORDER on PURCHASE_ORDER.ID = purc_order_line.PURC_ORDER_ID

unknown

Scott,

I'm getting stuck here;
,[vmfg].[dbo].[PURCHASE_ORDER].CREATE_DATE as Last_Order_Date
FROM [VMFG].[dbo].[PURC_ORDER_LINE] Inner join vmfg.dbo.PURCHASE_ORDER on PURCHASE_ORDER.ID = purc_order_line.PURC_ORDER_ID

I'm getting as far as MSQuery window. Can you elaborate further?
Not sure how to CREATE_DATE as Last_Order_Date
I tried inner join but don't get any different results. I assuming the result will be that it gives me only one Last Received Date for each Part ID.

Dave

unknown

You can leave of the "as Last_Order_Date". It is only to rename the results to something more meaningful. You may need to change the [vmfg] to match the name of your Visual database if it isn't VMFG. The only reason for the join was to get the create date in the purchase order table. It would be an inner join though as you should not have any lines without a matching record in the purchase order table.

Creating a Remote Workforce-20200625 1503-1.mp4

unknown

Thanks Scott,

I think I got it, I went into the SQL statement window and manually typed in Last_Order_Date. This what the statement is now.

SELECT PURC_ORDER_LINE.PURC_ORDER_ID, PURC_ORDER_LINE.LINE_NO, PURC_ORDER_LINE.PART_ID, PURC_ORDER_LINE.LAST_RECEIVED_DATE, PURCHASE_ORDER.CREATE_DATE AS 'Last_Order_Date'
FROM STGSS.dbo.PURC_ORDER_LINE PURC_ORDER_LINE, STGSS.dbo.PURCHASE_ORDER PURCHASE_ORDER
WHERE PURCHASE_ORDER.ID = PURC_ORDER_LINE.PURC_ORDER_ID

I'm just working on a formula to remove duplicate Part ID's leaving one with the Last_Order_Date.

I appreciate your help.

Dave

unknown

You need to have a distinct in the sql statement and take out the line detail. If you have nulls in your part number you may want to add the last line I put in.

SELECT DISTINCT PURC_ORDER_LINE.PURC_ORDER_ID, PURC_ORDER_LINE.PART_ID, PURC_ORDER_LINE.LAST_RECEIVED_DATE, PURCHASE_ORDER.CREATE_DATE AS 'Last_Order_Date'
FROM dbo.PURC_ORDER_LINE PURC_ORDER_LINE, dbo.PURCHASE_ORDER PURCHASE_ORDER
WHERE PURCHASE_ORDER.ID = PURC_ORDER_LINE.PURC_ORDER_ID
AND PART_ID IS NOT NULL

unknown

DISTINCT won't work in this case because the rows are already unique.

Since you're looking for the last order date, you should use the aggregate function MAX() on the ORDER_DATE column. Here's what I came up with (In MS Access SQL syntax):

SELECT dbo_PURC_ORDER_LINE.PART_ID, Max(dbo_PURCHASE_ORDER.ORDER_DATE) AS LAST_ORDER_DATE
FROM dbo_PURC_ORDER_LINE INNER JOIN dbo_PURCHASE_ORDER ON dbo_PURC_ORDER_LINE.PURC_ORDER_ID = dbo_PURCHASE_ORDER.ID
WHERE (((dbo_PURCHASE_ORDER.STATUS)<>"X"))
GROUP BY dbo_PURC_ORDER_LINE.PART_ID
HAVING (((dbo_PURC_ORDER_LINE.PART_ID) Is Not Null))
ORDER BY dbo_PURC_ORDER_LINE.PART_ID;

unknown

Thanks Jeremy, I'll give it a shot.

Dave

unknown

Hi, Dave,
If you want to check the date when last PO was issued, try my query below. You may put it in Excel, that will list all parts. I works in my 6.5.4 database.

SELECT DISTINCT
T.PART_ID, T.LAST_ORDER_DATE, T.ID,
PURC_ORDER_LINE_2.TOTAL_AMT_ORDERED / PURC_ORDER_LINE_2.ORDER_QTY * dbo.PURCHASE_ORDER.SELL_RATE AS UNIT_COST,
dbo.PURCHASE_ORDER.VENDOR_ID
FROM dbo.PURCHASE_ORDER INNER JOIN
dbo.PURC_ORDER_LINE AS PURC_ORDER_LINE_2 ON dbo.PURCHASE_ORDER.ID = PURC_ORDER_LINE_2.PURC_ORDER_ID INNER JOIN
dbo.PART ON PURC_ORDER_LINE_2.PART_ID = dbo.PART.ID INNER JOIN
(SELECT TOP (100) PERCENT D.PART_ID, D.LAST_ORDER_DATE, PO.ID
FROM dbo.PURCHASE_ORDER AS PO INNER JOIN
(SELECT dbo.PURC_ORDER_LINE.PART_ID, MAX(PURCHASE_ORDER_1.ORDER_DATE) AS LAST_ORDER_DATE
FROM dbo.PURC_ORDER_LINE INNER JOIN
dbo.PURCHASE_ORDER AS PURCHASE_ORDER_1 ON dbo.PURC_ORDER_LINE.PURC_ORDER_ID = PURCHASE_ORDER_1.ID
WHERE (PURCHASE_ORDER_1.STATUS = 'C' OR
PURCHASE_ORDER_1.STATUS = 'R') AND (dbo.PURC_ORDER_LINE.SERVICE_ID IS NULL) AND
(dbo.PURC_ORDER_LINE.TOTAL_AMT_ORDERED > 0)
GROUP BY dbo.PURC_ORDER_LINE.PART_ID) AS D ON PO.ORDER_DATE = D.LAST_ORDER_DATE INNER JOIN
dbo.PURC_ORDER_LINE AS PURC_ORDER_LINE_1 ON PO.ID = PURC_ORDER_LINE_1.PURC_ORDER_ID AND
D.PART_ID = PURC_ORDER_LINE_1.PART_ID) AS T ON PURC_ORDER_LINE_2.PURC_ORDER_ID = T.ID AND
PURC_ORDER_LINE_2.PART_ID = T.PART_ID
WHERE (PURC_ORDER_LINE_2.ORDER_QTY > 0) AND (PURC_ORDER_LINE_2.UNIT_PRICE > 0) AND (dbo.PURCHASE_ORDER.STATUS <> 'X') OR
(PURC_ORDER_LINE_2.ORDER_QTY > 0) AND (PURC_ORDER_LINE_2.UNIT_PRICE > 0) AND (dbo.PURCHASE_ORDER.STATUS <> 'X') AND
(NOT (dbo.PART.ENGINEERING_MSTR IS NOT NULL))
GROUP BY T.PART_ID, T.LAST_ORDER_DATE, T.ID, PURC_ORDER_LINE_2.TOTAL_AMT_ORDERED, PURC_ORDER_LINE_2.ORDER_QTY,
dbo.PURCHASE_ORDER.SELL_RATE, dbo.PURCHASE_ORDER.VENDOR_ID

Rhythm for Commerce Customer Newsletter October 2020.pdf

unknown

Thanks yingxuzhang,
Wow that's some query, it will take some time to adapt it. I let you know how it goes.

Dave

unknown

yingxuzhang,

All I did was copy and paste your query into SQL window and it worked. I was then able to Return Data to Excel for sorting.

Thank you very much!

Dave

unknown

Awesome, Dave.
My query was built to find the last purchase price of each part. That is why it is such a long script. You may take some time to turn it into a view it in SSMS. That would help you understand how it works.

0904080939293831.xls

Copyright © 2025 Infor. All rights reserved.