AFPRSP Failed Due To ORA-01000: Maximum Open Cursors Exceeded Using QP: Bulk Load of Price Lists (Doc ID 1634910.1)

Last updated on FEBRUARY 20, 2017

Applies to:

Oracle Advanced Pricing - Version 11.5.10 and later
Information in this document applies to any platform.
bulk loader
QPXVBLK: QP: Bulk Import of Price List

Symptoms

On : 12.1.3 version, Price List

When attempting to run QP: Bulk Import of Price Lists
the following errors occur.

ERRORS
-----------------------
ORACLE error 1000 in AFPRSP

Cause: AFPRSP failed due to ORA-01000: maximum open cursors exceeded
.

The SQL statement being executed at the time of the error was: SELECT R.Conc_Login_Id, R.Request_Id, R.Phase_Code, R.Status_Code, P.A and w
ORA-01000: maximum open cursors exceeded

Routine AFPPRD has encountered a fatal error. ORA-01000: maximum open cursors exceeded


Contact your system administrator or support representative.

Shutting down Concurrent Manager : 22-JAN-2014 22:43:42
ORACLE error 1000 in AFPRSP

Cause: AFPRSP failed due to ORA-01000: maximum open cursors exceeded
.

The SQL statement being executed at the time of the error was: savepoint FND_LOCK_REQUEST and was executed from the file 6672937/fnd/lib/af


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Populate the QP interface tables
select count(*) from QP_INTERFACE_LIST_HEADERS
Where process_id = 10065--0

select count(*) from QP_INTERFACE_LIST_lines
Where process_id = 10065---601

SELECT count(*) from QP_INTERFACE_PRICING_ATTRIBS
Where process_id = 10065--601

SELECT count(*) from QP_INTERFACE_Qualifiers
Where process_id = 10065--0
2. Responsibility: Oracle Pricing Manager
3. Navigate to: Reports
4. Select name = QP: Bulk Import of Price Lists
  Enter parameters including number of threads = 60

EXPECTATIONS
-------------------
For test case outlined above expects this to be processed in around 1-1.5 minutes

CURRENT PERFORMANCE
------------------------------------
For 90 Price Break Lines it is taking around 3 minutes to process
For 600 Price Break Lines it is taking around 12 minutes to process

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Client is already in Production and needs to import more Price Lists which have 200 k Lines

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