APP-SQLAP-10771

APXINWKB - Unable to Update or Cancel Invoice APP-SQLAP-10771 Could Not Reserve Record [ID 1200053.1]

Applies to:

Oracle Payables - Version: 11.5.10.0 to 12.1.3 - Release: 11.5.10 to 12.1
Information in this document applies to any platform.
APXINWKB - Invoice Workbench

APP-SQLAP-10771

Symptoms

Join our growing Oracle Payables Community and learn from your peers and Oracle on how to address your unique issues in AP.

-- Problem Description ---
When attempting to update fields at Header, Line or Distribution levels on a specific invoice, the following error occurs.

APP-SQLAP-10771: Could not reserve record.

Error can also occur whilst trying to cancel invoice.

Other invoices do not give the same issue.
Occurs for all users that attempt to update this invoice.

-- Steps to Reproduce --
Payables > Invoices > Entry > Invoices
Query Invoice
Attempt an update

-- Business Impact
Due to this issue, users cannot update invoices.

Cause

Database lock from another session is present on the table AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL for the same record that user was trying to modify from application.

Solution

1) To determine what session has a lock on this record, please execute the following steps:

a) Run the following to determine what tables are locked:

SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.object_name like 'AP_%'
ORDER BY b.object_name;

b) Look at the results and insert whatever AP_% tables are returned from a) into the script below:

SELECT l.*, o.owner object_owner, o.object_name
FROM SYS.all_objects o, v$lock l
WHERE l.TYPE = 'TM'
AND o.object_id = l.id1
AND o.object_name in ('AP_INVOICES_ALL', 'AP_INVOICE_LINES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');

c) SELECT SID, SERIAL#
FROM v$session
WHERE SID = <SID from b)>;

2) Once the locking sessions have been identified, please use the below command to kill such sessions.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

NOTE: SID and serial# to be taken from the output from 1c).


Alternative Solution
------------------
If there are many items locked you may find it easier to follow this alternative solution:

a) Ensure no-one is working and no processes are running in the system.
b) Bounce the database.
c) Retest the issue.