10 Replies Latest reply on Jan 20, 2017 3:25 AM by Sanjay Desai EBS

    Query behind PO Revision History page

    skull

      Hi,

       

      Is it possible to get the query that is used to populate the results on PO Revision History page? I am uploading the screen shot as an example.

      PO Revision History 2016-11-18_11-06-55-2.png

       

      Thanks.

        • 1. Re: Query behind PO Revision History page
          ShameerKappil

          Hi Skull,

           

          You can try this blog post

          How to get the queries behind a form and an OAF Page | Oracle Apps

           

          Thanks

          Shameer Kappil

          • 3. Re: Query behind PO Revision History page
            Sanjay Desai EBS

            You can directly get the PO Revision details from following tables as well.  

             

            PO_HEADERS_ARCHIVE_ALL

             

            PO_LINES_ARCHIVE_ALL

             

            PO_LINE_LOCATIONS_ARCHIVE_ALL

             

            PO_DISTRIBUTIONS_ARCHIVE_ALL

             

            • 4. Re: Query behind PO Revision History page
              skull

              Thanks Shameer Kappil & Sanjay Desai for your responses.

               

              My apologizes - I should have mentioned I already looked at the CompareResult VO and this VO uses POS_PO_REVISIONS_GT table which is a temp table. It is always empty and Oracle must be populating at run time.  Here is the query from that VO.

               

              CompareResultImpl.java 120.0 CompareResultRowImpl.java 120.2

               

                SELECT *
                  FROM (SELECT diff.line_num                      AS line_number,
                               diff.item                          AS item,
                               diff.location_num                  AS shipment_number,
                               diff.revision_num                  AS revision,
                               DECODE (
                                   diff.field_altered,
                                   'ICX_ACCEPTANCE_DUE_DATE', fnd_message.get_String (
                                                                  'POS',
                                                                  'POS_ACCEPTANCE_DUE_DATE'),
                                   'ICX_ACCEPTANCE_REQUIRED', fnd_message.get_String (
                                                                  'POS',
                                                                  'POS_ACCEPTANCE_REQUIRED'),
                                   'ICX_AMOUNT', fnd_message.get_String ('POS', 'POS_AMOUNT'),
                                   'ICX_AMOUNT_AGREED', fnd_message.get_String (
                                                            'POS',
                                                            'POS_AMOUNT_AGREED'),
                                   'ICX_AMOUNT_DUE_DATE', fnd_message.get_String (
                                                              'POS',
                                                              'POS_AMOUNT_DUE_DATE'),
                                   'ICX_AMOUNT_LIMIT', fnd_message.get_String (
                                                           'POS',
                                                           'POS_AMOUNT_LIMIT'),
                                   'ICX_BILL_TO', fnd_message.get_String ('POS',
                                                                          'POS_BILL_TO'),
                                   'ICX_BUYER', fnd_message.get_String ('POS', 'POS_BUYER'),
                                   'ICX_CANCELLED', fnd_message.get_String ('POS',
                                                                            'POS_CANCELLED'),
                                   'ICX_CHARGE_ACCT', fnd_message.get_String (
                                                          'POS',
                                                          'POS_CHARGE_ACCT'),
                                   'ICX_CLOSED_CODE', fnd_message.get_String (
                                                          'POS',
                                                          'POS_CLOSED_CODE'),
                                   'ICX_CONFIRMED', fnd_message.get_String ('POS',
                                                                            'POS_CONFIRMED'),
                                   'ICX_CONTRACT_NUMBER', fnd_message.get_String (
                                                              'POS',
                                                              'POS_CONTRACT_NUMBER'),
                                   'ICX_DISTRIBUTION', fnd_message.get_String (
                                                           'POS',
                                                           'POS_DISTRIBUTION'),
                                   'ICX_EFFECTIVE_DATE', fnd_message.get_String (
                                                             'POS',
                                                             'POS_EFFECTIVE_DATE'),
                                   'ICX_ERROR', fnd_message.get_String ('POS', 'POS_ERROR'),
                                   'ICX_EXPIRATION_DATE', fnd_message.get_String (
                                                              'POS',
                                                              'POS_EXPIRATION_DATE'),
                                   'ICX_FOB', fnd_message.get_String ('POS', 'POS_FOB'),
                                   'ICX_FREIGHT_TERMS', fnd_message.get_String (
                                                            'POS',
                                                            'POS_FREIGHT_TERMS'),
                                   'ICX_HAZARD_CLASS', fnd_message.get_String (
                                                           'POS',
                                                           'POS_HAZARD_CLASS'),
                                   'ICX_HEADER', fnd_message.get_String ('POS', 'POS_HEADER'),
                                   'ICX_ITEM', fnd_message.get_String ('POS', 'POS_ITEM'),
                                   'ICX_JOB', fnd_message.get_String ('POS', 'POS_JOB'),
                                   'ICX_ITEM_DESCRIPTION', fnd_message.get_String (
                                                               'PO',
                                                               'PO_WF_NOTIF_DOC_DESCRIPTION'),
                                   'ICX_CATEGORY', fnd_message.get_String (
                                                       'PON',
                                                       'PON_SHOPPING_CAT'),
                                   'ICX_ITEM_REVISION', fnd_message.get_String (
                                                            'POS',
                                                            'POS_ITEM_REVISION'),
                                   'ICX_LAST_ACCEPT_DATE', fnd_message.get_String (
                                                               'POS',
                                                               'POS_LAST_ACCEPT_DATE'),
                                   'ICX_LINE', fnd_message.get_String ('POS', 'POS_LINE'),
                                   'ICX_LINE_NUMBER', fnd_message.get_String (
                                                          'POS',
                                                          'POS_LINE_NUMBER'),
                                   'ICX_NEED_BY_DATE', fnd_message.get_String (
                                                           'POS',
                                                           'POS_NEED_BY_DATE'),
                                   'ICX_NEW', fnd_message.get_String ('POS', 'POS_NEW'),
                                   'ICX_NOTE_TO_VENDOR', fnd_message.get_String (
                                                             'POS',
                                                             'POS_NOTE_TO_SUPPLIER'),
                                   'ICX_NO_DIFFERENCE', fnd_message.get_String (
                                                            'POS',
                                                            'POS_NO_DIFFERENCE'),
                                   'ICX_PAYMENT_TERMS', fnd_message.get_String (
                                                            'POS',
                                                            'POS_PAYMENT_TERMS'),
                                   'ICX_PRICE_BREAK', fnd_message.get_String (
                                                          'POS',
                                                          'POS_PRICE_BREAK'),
                                   'ICX_PRICE_TYPE', fnd_message.get_String (
                                                         'POS',
                                                         'POS_PRICE_TYPE'),
                                   'ICX_PROMISED_DATE', fnd_message.get_String (
                                                            'POS',
                                                            'POS_PROMISED_DATE'),
                                   'ICX_QUANTITY', fnd_message.get_String ('POS',
                                                                           'POS_QUANTITY'),
                                   'ICX_QUANTITY_AGREED', fnd_message.get_String (
                                                              'POS',
                                                              'POS_QUANTITY_AGREED'),
                                   'ICX_RELEASE_DATE', fnd_message.get_String (
                                                           'POS',
                                                           'POS_RELEASE_DATE'),
                                   'ICX_RELEASE_NUMBER', fnd_message.get_String (
                                                             'POS',
                                                             'POS_RELEASE_NUMBER'),
                                   'ICX_REQUESTOR', fnd_message.get_String ('POS',
                                                                            'POS_REQUESTOR'),
                                   'ICX_SHIPMENT', fnd_message.get_String ('POS',
                                                                           'POS_SHIPMENT'),
                                   'ICX_SHIP_NUM', fnd_message.get_String ('POS',
                                                                           'POS_SHIP_NUM'),
                                   'ICX_SHIP_TO', fnd_message.get_String ('POS',
                                                                          'POS_SHIP_TO'),
                                   'ICX_SHIP_VIA', fnd_message.get_String ('POS',
                                                                           'POS_SHIP_VIA'),
                                   'ICX_SOURCE_QT_HEADER', fnd_message.get_String (
                                                               'POS',
                                                               'POS_SOURCE_QT_HEADER'),
                                   'ICX_SOURCE_QT_LINE', fnd_message.get_String (
                                                             'POS',
                                                             'POS_SOURCE_QT_LINE'),
                                   'ICX_SUPPLIER_CONTACT', fnd_message.get_String (
                                                               'POS',
                                                               'POS_SUPPLIER_CONTACT'),
                                   'ICX_SUPPLIER_ITEM_NUM', fnd_message.get_String (
                                                                'POS',
                                                                'POS_SUPPLIER_ITEM_NUM'),
                                   'ICX_TAXABLE_FLAG', fnd_message.get_String (
                                                           'POS',
                                                           'POS_TAXABLE_FLAG'),
                                   'ICX_UNIT_PRICE', fnd_message.get_String (
                                                         'POS',
                                                         'POS_UNIT_PRICE'),
                                   'ICX_UN_NUMBER', fnd_message.get_String ('POS',
                                                                            'POS_UN_NUMBER'),
                                   'ICX_UOM', fnd_message.get_String ('POS', 'POS_UOM'),
                                   'ICX_VENDOR_SITE', fnd_message.get_String (
                                                          'POS',
                                                          'POS_VENDOR_SITE'),
                                   'ICX_START_EFF_DATE', fnd_message.get_String (
                                                             'POS',
                                                             'POS_START_EFF_DATE'),
                                   'ICX_END_EFF_DATE', fnd_message.get_String (
                                                           'POS',
                                                           'POS_END_EFF_DATE'),
                                   'ICX_CONTERMS_DELIV_DATE', fnd_message.get_String (
                                                                  'POS',
                                                                  'POS_CONT_DELV_CHG'),
                                   'ICX_CONTERMS_ARTICLES_DATE', fnd_message.get_String (
                                                                     'POS',
                                                                     'POS_CONT_ART_CHG'),
                                   'ICX_CONTERMS_ARTICLES_DATE_NEW', fnd_message.get_String (
                                                                         'POS',
                                                                         'POS_NEW_CONTRACT_TERM'),
                                   'ICX_PURCHASING_ORG', fnd_message.get_String (
                                                             'POS',
                                                             'POS_PURCHASING_ORG'),
                                   'ICX_ENABLED_VENDOR_SITE', fnd_message.get_String (
                                                                  'POS',
                                                                  'POS_ENABLED_VENDOR_SITE'),
                                   'ICX_ENABLED_FLAG', fnd_message.get_String (
                                                           'POS',
                                                           'POS_ENABLED_FLAG'),
                                   'ICX_MULTIPLIER', fnd_message.get_String (
                                                         'POS',
                                                         'POS_MULTIPLIER'),
                                   'ICX_MIN_MULTIPLIER', fnd_message.get_String (
                                                             'POS',
                                                             'POS_MIN_MULTIPLIER'),
                                   'ICX_MAX_MULTIPLIER', fnd_message.get_String (
                                                             'POS',
                                                             'POS_MAX_MULTIPLIER'),
                                   'ICX_CONTRACTOR_FIRST_NAME', fnd_message.get_String (
                                                                    'POS',
                                                                    'POS_CONTRACTOR_FIRST_NAME'),
                                   'ICX_CONTRACTOR_LAST_NAME', fnd_message.get_String (
                                                                   'POS',
                                                                   'POS_CONTRACTOR_LAST_NAME'),
                                   'ICX_START_DATE', fnd_message.get_String (
                                                         'POS',
                                                         'POS_START_DATE'),
                                   'POS_UNIT_PRICE_LIMIT', fnd_message.get_String (
                                                               'POS',
                                                               'POS_UNIT_PRICE_LIMIT'),
                                   'ICX_RETAINAGE_RATE', fnd_message.get_String (
                                                             'POS',
                                                             'POS_RETAINAGE_RATE'),
                                   'ICX_MAX_RETAINAGE_AMOUNT', fnd_message.get_String (
                                                                   'POS',
                                                                   'POS_MAX_RETAINAGE_AMOUNT'),
                                   'ICX_PROGRESS_PAYMENT_RATE', fnd_message.get_String (
                                                                    'POS',
                                                                    'POS_PROGRESS_PAYMENT_RATE'),
                                   'ICX_RECOUPMENT_RATE', fnd_message.get_String (
                                                              'POS',
                                                              'POS_RECOUPMENT_RATE'),
                                   'ICX_OWNER', fnd_message.get_String ('POS', 'POS_OWNER'),
                                   'ICX_PAY_DESCRIPTION', fnd_message.get_String (
                                                              'POS',
                                                              'POS_PAY_DESCRIPTION'),
                                   'ICX_PAY_TYPE', fnd_message.get_String (
                                                       'POS',
                                                       'POS_PAYMENT_TYPE'),
                                   'ICX_ADVANCE_AMOUNT', fnd_message.get_String (
                                                             'POS',
                                                             'POS_ADVANCE_AMOUNT'),
                                   'ICX_VALUE_PERCENT', fnd_message.get_String (
                                                            'POS',
                                                            'POS_VALUE_PERCENT'),
                                   diff.field_altered)
                                   AS field_altered,
                               DECODE (
                                   diff.field_altered,
                                   'ICX_ITEM_REVISION', diff.changes_from,
                                   DECODE (
                                       diff.changes_from,
                                       'D', fnd_message.get_string ('POS',
                                                                    'POS_ACC_REQ_DOC_ONLY'),
                                       'N', fnd_message.get_string ('POS',
                                                                    'POS_ACC_REQ_NONE'),
                                       'S', fnd_message.get_string (
                                                'POS',
                                                'POS_ACC_REQ_DOC_AND_SIG'),
                                       'Y', fnd_message.get_string (
                                                'POS',
                                                'POS_ACC_REQ_DOC_OR_SHIP'),
                                       diff.changes_from))
                                   AS changed_from,
                               DECODE (
                                   diff.field_altered,
                                   'ICX_ITEM_REVISION', diff.changes_to,
                                   DECODE (
                                       diff.changes_to,
                                       'D', fnd_message.get_string ('POS',
                                                                    'POS_ACC_REQ_DOC_ONLY'),
                                       'N', fnd_message.get_string ('POS',
                                                                    'POS_ACC_REQ_NONE'),
                                       'S', fnd_message.get_string (
                                                'POS',
                                                'POS_ACC_REQ_DOC_AND_SIG'),
                                       'Y', fnd_message.get_string (
                                                'POS',
                                                'POS_ACC_REQ_DOC_OR_SHIP'),
                                       diff.changes_to))
                                   AS changed_to,
                               diff.header_id                     AS header_id,
                               diff.line_seq                      AS seq,
                               NVL (diff.line_num, 0)             AS hide_line,
                               DECODE (diff.field_altered,
                                       'ICX_CONTERMS_DELIV_DATE', 'PosDelvUrl',
                                       'ICX_CONTERMS_ARTICLES_DATE', 'PosArtUrl',
                                       'ICX_CONTERMS_ARTICLES_DATE_NEW', 'PosArtNew',
                                       'PosFieldText')
                                   AS FIELD_ALTERED_TYPE,
                               diff.enabled_org_name,
                               diff.price_diff_num,
                               NVL (diff.location_num, -1)        AS ship_num_sort,
                               DECODE (diff.field_altered,
                                       'ICX_PROMISED_DATE', 'PosDateTypeFrom',
                                       'ICX_NEED_BY_DATE', 'PosDateTypeFrom',
                                       'ICX_ACCEPTANCE_DUE_DATE', 'PosDateTypeFrom',
                                       'ICX_START_DATE', 'PosDateTypeFrom',
                                       'ICX_EXPIRATION_DATE', 'PosDateTypeFrom',
                                       'ICX_ACCEPTANCE_DUE_DATE', 'PosDateTypeFrom',
                                       'ICX_RELEASE_DATE', 'PosDateTypeFrom',
                                       'ICX_LAST_ACCEPT_DATE', 'PosDateTypeFrom',
                                       'ICX_START_EFF_DATE', 'PosDateTypeFrom',
                                       'ICX_END_EFF_DATE', 'PosDateTypeFrom',
                                       'PosTextTypeFrom')
                                   AS PosFromField,
                               DECODE (diff.field_altered,
                                       'ICX_PROMISED_DATE', 'PosDateTypeTo',
                                       'ICX_NEED_BY_DATE', 'PosDateTypeTo',
                                       'ICX_ACCEPTANCE_DUE_DATE', 'PosDateTypeTo',
                                       'ICX_START_DATE', 'PosDateTypeTo',
                                       'ICX_EXPIRATION_DATE', 'PosDateTypeTo',
                                       'ICX_ACCEPTANCE_DUE_DATE', 'PosDateTypeTo',
                                       'ICX_RELEASE_DATE', 'PosDateTypeTo',
                                       'ICX_LAST_ACCEPT_DATE', 'PosDateTypeTo',
                                       'ICX_START_EFF_DATE', 'PosDateTypeTo',
                                       'ICX_END_EFF_DATE', 'PosDateTypeTo',
                                       'PosTextTypeTo')
                                   AS PosToField,
                               change_from_date                   AS PosChangeFromDate,
                               change_to_date                     PosChangeToDate,
                               diff.distribution_num,
                               NVL (diff.distribution_num, -1)    AS diff_num_sort,
                               ''                                 AS FILE_NAME,
                               ''                                 AS FILE_CONTENT_TYPE,
                               NULL                               AS FILE_DATA,
                               pos_compare_revisions.get_nextval () AS unique_key,
                               diff.item || diff.job              AS item_job
                          FROM pos_po_revisions_gt diff) QRSLT
                 WHERE (seq = 592439)
              ORDER BY revision DESC,
                       hide_line ASC,
                       ship_num_sort ASC,
                       diff_num_sort ASC,
                       enabled_org_name ASC
              
              • 5. Re: Query behind PO Revision History page
                skull

                I am uploading the screen shot of the VO, for your reference.  Thanks again.

                 

                PO Revision History VO screen shot 2017-01-16_17-02-20.png

                • 6. Re: Query behind PO Revision History page
                  skull

                  Any pointers Shameer?

                  • 7. Re: Query behind PO Revision History page
                    skull

                    Any pointers Sanjay?

                    • 8. Re: Query behind PO Revision History page
                      Sanjay Desai EBS

                      Hi ,

                       

                      I have given the base table affected at the time of PO Revision.

                      you can get the desired o/p as per business need using these base tables ( by po_header_id & org_id  ).

                      If you still have any problem, pl . explain.

                       

                      regards

                      Sanjay

                      • 9. Re: Query behind PO Revision History page
                        skull

                        Sanjay,

                         

                        I was hoping there would be a query to get the output as displayed on the page (original screen shot).  But looks like I will have to build it using the tables you suggested.  Right?

                         

                        Thanks.

                        • 10. Re: Query behind PO Revision History page
                          Sanjay Desai EBS

                          Hi,

                           

                          Yes, you have to use the suggested base tables for PO revision.

                           

                          regards

                          Sanjay