This discussion is archived
3 Replies Latest reply: Oct 8, 2012 3:18 AM by Sanjay Desai RSS

History data in PO Module

966071 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    966071 Newbie
    Currently Being Moderated
    The query that you provide cannot shown historical data in PO.

    Thank You.
  • 3. Re: History data in PO Module
    Sanjay Desai Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points