2 Replies Latest reply on Mar 28, 2012 5:07 PM by Chandan

    Reg:-ORIGINAL PROMISE DATE OF PO

    Chandan
      Hi all,

      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 we are using this 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

      Please let me know whether the logic we are using is correct or not.

      Thanx in advance.