4 Replies Latest reply on Dec 20, 2011 4:12 PM by tootricky

    PO History Changes Query

    827866
      Dears,


      I'm working on a query that help me to retrieve all changes that happened on a specific PO, but I couldn't find these tables.

      Can you help guys.


      Thanks
      Ahmed
        • 1. Re: PO History Changes Query
          Vinod
          You can hit these tables..
          po_headers_archive_all
          po_lines_archive_all
          po_line_locations_archive_all
          po_distributions_archive_all

          Each PO might have more than 1 record which implies.. 4 times edited => 4 history/archive records.
          Therefore, it needs a careful review with the last updated date etc..

          Some of the standard pages display an audit history of what happened to the PO.
          This is much easier than the above method.
          User guide talks about this page/report.

          Cheers
          Vinod
          • 2. Re: PO History Changes Query
            827866
            Thanks Vinod for immediate Response ..


            I mean with PO change history that function PO Change History (N:Purchase Orders>PO Change History).

            From which tables this function is reading?



            Thanks
            • 3. Re: PO History Changes Query
              tootricky
              You can see the query that is being used by the Search page by looking at the View Object (PosRevisionHistoryVO.xml) specified in the PosRevHistoryAM.xml file. As was previously mentioned, these include the po_headers_archive_all table but changes are also held in the po_lines_archive_all, po_line_locations_archive_all and po_distributions_archive_all tables.

              From VO:

              SELECT
              POH.COMMENTS,
              POH.REVISED_DATE,
              POH.REVISION_NUM,
              POH.AGENT_ID,
              POH.BILL_TO_LOCATION_ID,
              POH.PO_HEADER_ID,
              POH.SHIP_TO_LOCATION_ID,
              POH.VENDOR_CONTACT_ID,
              POH.VENDOR_ID,
              POH.VENDOR_SITE_ID,
              NVL(POH.CLOSED_CODE, 'OPEN') CLOSED_CODE,
              POH.CURRENCY_CODE,
              POH.TYPE_LOOKUP_CODE,
              POH.SEGMENT1 PO_NUM,
              POH.SEGMENT1 PONUM_RELNUM,
              FNM.MESSAGE_TEXT TYPE_NAME,
              NULL BUYER_NAME,
              V.VENDOR_NAME, VS.VENDOR_SITE_CODE, NULL LOCATION_CODE,
              NULL AUTHORIZATION_STATUS, NULL DOCUMENT_STATE,
              TO_NUMBER(NULL) RELEASE_NUM, NULL RELEASE_TYPE,
              TO_NUMBER(NULL) PO_RELEASE_ID,
              NULL TOTAL,
              POH.ORG_ID, HOU.NAME,POH.CREATION_DATE,
              POH.GLOBAL_AGREEMENT_FLAG,
              null LAST_SIGN_TYPE,
              PDTL.security_level_code,
              PDTL.access_level_code
              FROM PO_VENDORS V, PO_VENDOR_SITES_ALL VS,
              PO_HEADERS_ARCHIVE_ALL POH,
              HR_ALL_ORGANIZATION_UNITS_TL HOU,
              FND_NEW_MESSAGES FNM,
              PO_DOCUMENT_TYPES_ALL_B PDTL     
              WHERE
              V.VENDOR_ID = POH.VENDOR_ID AND
              VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND
              POH.TYPE_LOOKUP_CODE IN ('STANDARD', 'BLANKET','PLANNED','CONTRACT') AND
              POH.LATEST_EXTERNAL_FLAG = 'Y' AND
              HOU.ORGANIZATION_ID (+)= POH.ORG_ID AND
              HOU.LANGUAGE (+)= USERENV('LANG')
              AND FNM.LANGUAGE_CODE = USERENV('LANG') AND
              FNM.APPLICATION_ID = 177 AND
              FNM.MESSAGE_NAME = DECODE(POH.TYPE_LOOKUP_CODE,'BLANKET',
                   DECODE(POH.GLOBAL_AGREEMENT_FLAG,'Y','POS_POTYPE_GBLA','POS_POTYPE_BLKT'),
                   'CONTRACT','POS_POTYPE_CNTR',
                   'STANDARD','POS_POTYPE_STD',
                   'PLANNED','POS_POTYPE_PLND')
              AND PDTL.ORG_ID=POH.ORG_ID
              AND PDTL.document_subtype = POH.type_lookup_code
              AND PDTL.document_type_code in ('PO','PA')
              UNION ALL
              SELECT
              POH.COMMENTS,
              POR.REVISED_DATE,
              POR.REVISION_NUM,
              POR.AGENT_ID,
              POH.BILL_TO_LOCATION_ID,
              POR.PO_HEADER_ID,
              POH.SHIP_TO_LOCATION_ID,
              POH.VENDOR_CONTACT_ID,
              POH.VENDOR_ID,
              POH.VENDOR_SITE_ID,
              NVL(POR.CLOSED_CODE, 'OPEN') CLOSED_CODE,
              POH.CURRENCY_CODE,
              POH.TYPE_LOOKUP_CODE,
              POH.SEGMENT1 PO_NUM,
              POH.SEGMENT1||'-'||POR.RELEASE_NUM PONUM_RELNUM,
              FNM.MESSAGE_TEXT TYPE_NAME,
              NULL BUYER_NAME,
              V.VENDOR_NAME,
              VS.VENDOR_SITE_CODE,
              NULL LOCATION_CODE,
              NULL AUTHORIZATION_STATUS,
              NULL DOCUMENT_STATE,
              POR.RELEASE_NUM,
              POR.RELEASE_TYPE,
              POR.PO_RELEASE_ID,
              NULL TOTAL,
              POR.ORG_ID,
              HOU.NAME,POR.CREATION_DATE,
              null GLOBAL_AGREEMENT_FLAG,
              null LAST_SIGN_TYPE,
              PDTL.security_level_code,
              PDTL.access_level_code
              FROM PO_VENDORS V,PO_VENDOR_SITES_ALL VS,
              PO_RELEASES_ARCHIVE_ALL POR,PO_HEADERS_ARCHIVE_ALL POH,
              HR_ALL_ORGANIZATION_UNITS_TL HOU,
              FND_NEW_MESSAGES FNM,
              PO_DOCUMENT_TYPES_ALL_B PDTL
              WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID AND
              V.VENDOR_ID = POH.VENDOR_ID AND
              VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND
              POH.TYPE_LOOKUP_CODE IN ( 'BLANKET', 'PLANNED') AND
              POR.LATEST_EXTERNAL_FLAG = 'Y' AND
              POH.LATEST_EXTERNAL_FLAG = 'Y' AND
              HOU.ORGANIZATION_ID (+)= POR.ORG_ID AND
              HOU.LANGUAGE (+)= USERENV('LANG') AND
              FNM.LANGUAGE_CODE = USERENV('LANG') AND
              FNM.APPLICATION_ID = 177 AND
              FNM.MESSAGE_NAME = DECODE(POR.RELEASE_TYPE,'BLANKET','POS_POTYPE_BLKTR','SCHEDULED','POS_POTYPE_PLNDR')
              AND PDTL.ORG_ID=POH.ORG_ID
              AND PDTL.document_subtype = POH.type_lookup_code
              AND PDTL.document_type_code ='RELEASE'

              Thanks,
              Nic
              • 4. Re: PO History Changes Query
                tootricky
                Additionally the results appear to be using pos_po_revisions_gt but I would imagine this gets populated at run time so would be no use for reporting.

                Thanks,
                Nic