My Oracle Support Banner

Performance Issue In Invoice Workbench: It Takes Too Long Time When Changing The Supplier Site (Doc ID 1903005.1)

Last updated on JANUARY 16, 2018

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Performance problem in the Invoice workbench.
For an existing invoice that is accounted, when selecting another
supplier site from the list of values,
the form shows the window message:

APP-SQLAP-10131: When you update the supplier name, number or site for an existing invoice,
Oracle Payables does not update the invoice with the defaults from the new supplier or site.
Update each invoice value individually.

After clicking OK button in the window message,
the form takes 20-30 minutes to complete the change.

 

The following SQL statement is taking most of the time:


SQL ID: 7592btxqfhbtm

SELECT /*+ push_pred(s1) push_pred(s2) */ --Bug 18712856
            evt.event_id,
            evt.event_type_code,
            evt.event_date,
            evt.process_status_code,
            evt.reference_num_1          old_party_id,
            evt.reference_num_2          old_site_id,
            evt.reference_num_3          new_party_id,
            evt.reference_num_4          new_site_id,
            p1.third_party_number        original_party_number,
            s1.third_party_site_code     original_site_code,
            p2.third_party_number        new_party_number,
            s2.third_party_site_code     new_site_code,
            evt.reference_char_1         party_type,
            evt.reference_char_2         mapping_flag,
            ent.entity_id,
            ent.source_application_id,
            ent.ledger_id
     FROM   xla_events evt,
            xla_third_parties_v p1,
            xla_third_parties_v p2,
            xla_third_party_sites_v s1,
            xla_third_party_sites_v s2,
            xla_transaction_entities ent,
            xla_ledger_options lgopt,
            xla_launch_options lnopt
     WHERE  evt.APPLICATION_ID = 200
     AND evt.EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE')
     AND evt.PROCESS_STATUS_CODE not in ('P','F')
      AND evt.EVENT_ID = 144203647
     AND p1.THIRD_PARTY_ID = evt.REFERENCE_NUM_1
     AND p1.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
     AND p2.THIRD_PARTY_ID = evt.REFERENCE_NUM_3
     AND p2.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
     AND s1.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_1
     AND s1.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_2
     AND s1.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
     AND s2.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_3
     AND s2.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_4
     AND s2.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
     AND ent.APPLICATION_ID = evt.APPLICATION_ID
      AND  ent.LEDGER_ID = 1
     AND ent.ENTITY_ID = evt.ENTITY_ID
     AND ent.ENTITY_CODE = 'THIRD_PARTY_MERGE'
     AND lgopt.APPLICATION_ID = ent.APPLICATION_ID
     AND lgopt.LEDGER_ID = ent.LEDGER_ID
     AND lgopt.ENABLED_FLAG = 'Y'
     AND lnopt.APPLICATION_ID = lgopt.APPLICATION_ID
     AND lnopt.LEDGER_ID = lgopt.LEDGER_ID
     AND (    lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = 'Y'
           OR (lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = 'N'
               AND lnopt.ACCOUNTING_MODE_CODE = 'F'))
     AND (    lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = 'Y'
           OR (lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = 'N'
               AND lnopt.SUBMIT_TRANSFER_TO_GL_FLAG
                    = 'Y'))
     AND (    lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = 'Y'
           OR (lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = 'N'
               AND lnopt.SUBMIT_GL_POST_FLAG = 'N'))
     AND (   'N' = 'N'
          OR ('N' ='Y'
              AND NOT EXISTS
              (SELECT 'Ledger without access'
               FROM XLA_LEDGER_RELATIONSHIPS_V rs,
                    XLA_LEDGER_OPTIONS lgopt2,
                    gl_ledgers gld
               WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
               AND rs.ledger_id = gld.ledger_id
               AND gld.complete_flag = 'Y'
               AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
               AND DECODE('N'
                    , 'N', rs.PRIMARY_LEDGER_ID
                         , DECODE(rs.LEDGER_CATEGORY_CODE
                           , 'ALC', rs.PRIMARY_LEDGER_ID
                                  , rs.LEDGER_ID)) = lgopt.LEDGER_ID
               AND DECODE(rs.LEDGER_CATEGORY_CODE
                    , 'ALC', rs.PRIMARY_LEDGER_ID
                           , rs.LEDGER_ID) = lgopt2.LEDGER_ID
               AND lgopt2.APPLICATION_ID = lgopt.APPLICATION_ID
               AND lgopt2.ENABLED_FLAG = 'Y'
               AND lgopt2.MERGE_ACCT_OPTION_CODE <> 'NONE'
               AND rs.LEDGER_ID NOT IN
                   (SELECT asa.LEDGER_ID
                      FROM GL_ACCESS_SET_ASSIGNMENTS asa
                     WHERE asa.ACCESS_SET_ID
                                            IN ('1001'
              ,'-1')))))
     ORDER BY evt.EVENT_DATE, evt.EVENT_ID

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.