APP-SQLAP-10771: could not reserve record

http://dbissues.blogspot.com/2016/02/app-sqlap-10771-could-not-reserve-record.html

R12: Unable to update an Invoice in Invoice Entry form (APXINWKB): APP-SQLAP-10771: could not reserve record.

ERROR

APP-SQLAP-10771: Could not reserve record

Got this error when trying to pay Full Invoice. This error comes normally due to database lock from another sessions on AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL tables.

SOLUTION 

Step 1. First determine what session has lock this record, please execute the following steps:

a) Run the following to query to determine which 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 run the below given Query by inserting that is returned from above script :

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) Now, run the below given query and enter the SID returned by above query.

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

Step 2. As the locking sessions have been identified, please use the below command to kill sessions.

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

SID and serial# to be taken from the output of 1C.

-----END----

#################################################################################

Objects Locked in Module

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;

Table wise Objects Locked

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_DISTRIBUTIONS_ALL');

SELECT SID, SERIAL# FROM v$session WHERE SID in (306, 312);

ALTER SYSTEM KILL SESSION '312,322' IMMEDIATE;

-----------------------------------------------------------------------------------------------------------

Object Locked in User

 select
 a.object_id, a.session_id, substr(c.object_name, 1, 40), b.CLIENT_IDENTIFIER
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

All in One Query

select
 o.owner,l.SID, b.SERIAL#,a.object_id, a.session_id, substr(c.object_name, 1, 40), b.CLIENT_IDENTIFIER, b.LOGON_TIME
from
   v$locked_object a ,
   v$session b,
   dba_objects c,
   SYS.all_objects o,
   v$lock l
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
   AND o.object_id = l.id1
   and o.object_id=a.OBJECT_ID
   and o.object_id=c.object_id
   and l.ID1=a.OBJECT_ID
   and l.ID1=c.object_id
   and l.TYPE = 'TM'
   and b.SID=l.SID
      order by b.LOGON_TIME asc ;