ORIGINAL PROMISE DATE OF PO

Scenario:
We have bulit a report on Purchase Order Receipts. In that we are having three columns i.e. Current Promise Date, Need by Date, Original Promise Date.

NOW – the problem is that the ORIGINAL PROMISE date is not accurate when there is multiple shipments for a PO Line.It is pulling the original promise date from shipment #1 in all cases instead of taking it from the correct shipment number.

EXAMPLES WHERE PO LINE HAS MULTIPLE SHIPMENTS:

PO 100004
LINE – 1
SHIPMENT – 6
Report is showing Original Promise Date as 10/11/2011, but in Oracle the Original Promise is 12/21/11 (IT IS PULLING THE ORIGINAL PROMISE DATE FROM SHIPMENT #1)
and same is the case for shipments 5,6,7,8 are incorrect as well.

In the source Extract query to pull Original Promise Date:
SELECT PO_LINE_ID,PROMISED_DATE AS ORIGINAL_PROMISED_DATE
FROM
(
SELECT LINE_LOCATION_ID,PO_LINE_ID,PROMISED_DATE,LAST_UPDATE_DATE,REVISION_NUM,ROW_NUMBER() OVER (PARTITION BY PO_LINE_ID ORDER BY REVISION_NUM ASC) RN
FROM APPS.PO_LINE_LOCATIONS_ARCHIVE_ALL
) ORIG_PROMIS_DATE WHERE RN=1) ORIGIN_PROMISED_DT
 
Solution:
So instead of doing PARTITION BY  PO_LINE_ID , We should do a "PARTITION BY line_location_id" ; not po_line_id.
If you do it by po_line_id, it is giving one record for each line even if you have multiple shipments.
You need one record for each shipment.