PO change history query in oracle apps

PO change history query in oracle apps

In this post, we will be discussing about the PO change history query in oracle apps. We do know that when we do the changes in the Approved PO, then system create the new revision for the PO and keep the Old changes for that PO in the system. PO change history query helps to extract all the changes details for the PO from Old Revision to New Revision. We will get the OLD PO revision information’s and the latest/current PO information’s. We can also get the information’s about the when the changes happened for the PO and many other critical information’s in oracle apps. Here below I will be sharing the complete PO change history query in oracle apps.

3 Important Tables of PO change history query in oracle apps

These three tables help to store all the changes for the PO with Header, Line and PO distribution level.

1.PO_LINE_LOCATIONS_ARCHIVE_ALL

2.PO_LINES_ARCHIVE_ALL

3.PO_HEADERS_ARCHIVE_ALL

Detail PO change history query in oracle apps

Here below is the complete sql query help to extract the list po changes in oracle apps with PO revision no. We can run this report for specific Purchase order no or run for all PO’s also.

SELECT PHA.SEGMENT1 PO_NUM,

       PHA.REVISION_NUM,

       PLA.LINE_NUM PO_LINE_NUM,

       PLLA.SHIPMENT_NUM,

       PLA.ITEM_DESCRIPTION,     

       PLA.UNIT_MEAS_LOOKUP_CODE,

       PLA.LIST_PRICE_PER_UNIT,

       PLA.CANCEL_FLAG,

       PLA.CANCEL_DATE,

       PLA.QUANTITY PO_LINE_QUANTITY,

       PHA.AUTHORIZATION_STATUS HEADER_APPROVAL_STATUS,PHA.APPROVED_FLAG HEADER_APPROVED_FLAG,PHA.APPROVED_DATE HEADER_APPROVED_DATE,

       PHA.CLOSED_CODE HEADER_CLOSURE_STATUS,

       PLA.CLOSED_CODE LINE_CLOSURE_STATUS,

       PLLA.CLOSED_CODE SHIPMENT_CLORSURE_STATUS,    

       PLLA.APPROVED_FLAG SHIPMENT_APPROVED_FLAG,PLLA.APPROVED_DATE SHIPMENT_APPROVED_DATE, 

       PHA.CANCEL_FLAG PO_CANCEL_FLAG,PLA.CANCEL_FLAG LINE_CANCEL_FLAG,PLLA.CANCEL_FLAG SHIPMENT_CANCEL_FLAG

       ,PHA.ORG_ID,

       PV.VENDOR_NAME,

       APSS.VENDOR_SITE_CODE

  FROM PO.PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA,

       PO.PO_LINES_ARCHIVE_ALL PLA,

       PO.PO_HEADERS_ARCHIVE_ALL PHA,

       APPS.PO_VENDORS PV,

       APPS.AP_SUPPLIER_SITES_ALL APSS

 WHERE PLA.PO_LINE_ID = PLLA.PO_LINE_ID

   AND PHA.VENDOR_ID=PV.VENDOR_ID

   AND PHA.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

   AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID

   AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

   AND PHA.SEGMENT1 in('3000363')

   AND PHA.REVISION_NUM=PLA.REVISION_NUM

   AND PLA.REVISION_NUM=PLLA.REVISION_NUM

   AND PHA.REVISION_NUM=PLLA.REVISION_NUM

ORDER BY PHA.SEGMENT1,PLA.LINE_NUM，PLLA.SHIPMENT_NUM;