R12 Performance issue in Payables Open Interface Import Program (Doc ID 2207351.1)

Last updated on DECEMBER 31, 2016

Applies to:

Oracle Payables - Version 12.1.1 to 12.2.6 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms

On : 12.1.0 version, Other-MiscIss & KeyIndReport

ACTUAL BEHAVIOR
---------------
Users are facing performance issue with Payables open interface import.

EXPECTED BEHAVIOR
-----------------------
Payables open interface import program will get complete within time and it will not create any performance issue.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to Payables Responsibility > Run Payables open interface import program.
2. The program is taking long time and it is creating performance issue.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users are facing performance issue while running Payables open interface import program.

Changes

 API: AP_ETAX_SERVICES_PKG.Return_Default_Import

Below query of cursor Trx_Lines_c taking huge time:

SELECT * FROM ZX_TRANSACTION_LINES_GT WHERE
TRX_ID = :B2 AND TRX_LINE_ID = :B1

To fix issue added more filters in query so that
index ZX_TRANSACTION_LINES_GT_U1 get picked up to improve performance.

CURSOR Trx_Lines_c (c_line_number IN NUMBER) IS
SELECT *
FROM zx_transaction_lines_gt
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND event_class_code IN ('STANDARD INVOICES',
'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND trx_id = P_Invoice_Header_Rec.invoice_id
AND trx_line_id = c_line_number;

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms