3 Replies Latest reply: Oct 8, 2012 5:18 AM by Sanjay Desai EBS RSS

    History data in PO Module

    user12227822
      Hi,

      I want to know the total price of the PO which was first formed prior to the revision (revision num = 0).
      Is there a query / function that can give me an information about the unit price in PO Approved which revision num = 0 ?
      As I know, when PO revised, PO_LINES_ARCHIVE_ALL doesn't INSERT data from line PO, but do update data from line PO (CMIIW).

      Need your help.

      Thank You.
      Arie Pratama
        • 1. Re: History data in PO Module
          Sanjay Desai EBS
          Hi,

          pl. find the SQL . pl. try after necessary changes.

          SELECT SEGMENT1 PO_NUMBER, SUM(POL.UNIT_PRICE * POL.QUANTITY) PO_VALUE
          FROM PO_HEADERS_ALL POH , PO_LINES_ALL POL
          WHERE POH.ORG_ID = 41
          AND POH.CREATION_DATE BETWEEN '01-MAR-2012' AND '03-SEP-2012'
          AND POH.REVISION_NUM = 0
          AND NVL(POH.CLOSED_CODE, 'OPEN' ) = 'OPEN'
          AND POH.AUTHORIZATION_STATUS = 'APPROVED'
          AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
          AND NVL(POL.CANCEL_FLAG, 'X' ) <> 'Y'
          AND ( POL.CLOSED_CODE = 'OPEN' OR POL.CLOSED_CODE = 'OPEN FOR RECEIVING' OR POL.CLOSED_CODE IS NULL )
          GROUP BY SEGMENT1

          HTH
          Sanjay
          • 2. Re: History data in PO Module
            user12227822
            The query that you provide cannot shown historical data in PO.

            Thank You.
            • 3. Re: History data in PO Module
              Sanjay Desai EBS
              You try following SQL. This is just example . you have to pass your parameters.

              SELECT SEGMENT1 PO_NUMBER, SUM(POL.UNIT_PRICE * POL.QUANTITY) PO_VALUE
              FROM PO_HEADERS_ALL POH , PO_LINES_ALL POL
              WHERE POH.ORG_ID = :P_ORG_ID --42
              AND POH.SEGMENT1 = :PO_NUMBER --98813
              AND POH.REVISION_NUM = 0
              AND NVL(POH.CLOSED_CODE, 'OPEN' ) = 'OPEN'
              AND POH.AUTHORIZATION_STATUS = 'APPROVED'
              AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
              AND NVL(POL.CANCEL_FLAG, 'X' ) != 'Y'
              AND ( POL.CLOSED_CODE = 'OPEN' OR POL.CLOSED_CODE = 'OPEN FOR RECEIVING' OR POL.CLOSED_CODE IS NULL )
              GROUP BY SEGMENT1

              HTH
              Sanjay

              Edited by: Sanjay Desai on Oct 8, 2012 3:48 PM