問題記錄_AP_錯誤 APP-SQLAP-10000: ORA-01400

Symptoms
--------------------------------------------------------------------
應付模組下，商業發票比對採購單時提示下列錯誤資訊：

APP-SQLAP-10000: ORA-01400: cannot insert NULL into ("AP"."AP_INVOICE_LINE_ALL"."AMOUNT") occurred in
Insert_Invoice_Line<-AP_MATCHING_PKG.base_credit_rcv_match<-
APXRMTCH
with parameters (Invoice_ED = 13161, Invoice Line Number =
10, PO Distribution Id = 20399, Project Id = ,Task_Id =,
Expenditure Type = , Expenditure_Organization_id =)
while performing the following operation:
Inserting Item Line Matched to a RCV

Issue
--------------------------------------------------------------------
重啟應用後，有問題的商業發票就可以進行比對了，仍待觀察。

問題記錄_AP_錯誤 APP-SQLAP-10771:無法保留記錄

Symptoms
--------------------------------------------------------------------
錄入付款之商業發票，比對完成後無法保留紀錄情況，無法進行驗證動作。

Cause
--------------------------------------------------------------------
引起原因是記錄(record)被鎖定(lock)了


Solution

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

1)運行下例SQL語句找出被鎖定的AP session：

select t3.object_name, -- 对象名称

       t3.object_type, -- 对象类型

       t1.locked_mode 锁模式,

       count(t3.object_name) over(partition by t3.object_name order by t3.object_name range unbounded preceding) 被锁定的会话数r,

       decode(t1.locked_mode,

              0,

              'none',

              1,

              '空',

              2,

              '行共享(RS)-共享表锁',

              3,

              '行专用(RX)-用于行的修改',

              4,

              '共享锁(S)-阻止其他DML操作',

              5,

              '共享行专用(SRX)-阻止其他事务操作',

              6,

              '专用(X)-独立访问使用') 锁类型, --锁模式

       t2.logon_time 会话连接时间,

       round((sysdate - t2.logon_time) * 24 * 60, 2) 连接持续时间,

       t2.sid sid, --SESSION标识，常用于连接其它列

       t2.serial# serial#, --SERIAL#：如果某个SID又被其它的session使用的话则此数值自增加(当一个       SESSION结束，另一个SESSION开始并使用了同一个SID)。

       t2.status, -- Achtive：正执行SQL语句(waiting for/using a resource)   Inactive：等待操作(即等待需要执行的SQL语句    Killed：被标注为删除

       t2.audsid, --审查session ID唯一性，确认它通常也用于当寻找并行查询模式

       t2.username, --当前session在oracle中的用户名。

       t2.process, --客户端进程的ID

       t2.osuser, --客户端操作系统用户名

       t2.terminal, --客户端运行的终端

       t2.machine, --客户端执行的机器

       t2.program, --客户端执行的客户端程序

       t2.module, -- DBMS_APPLICATION_INFO中设置

       t2.action, --DBMS_APPLICATION_INFO中设置

       t2.logon_time,

       t1.oracle_username as ora_username, -- ORACLE系统用户名称

       t1.os_user_name as os_username, -- 操作系统用户名称

       t1.process as process, --进程编号

       'alter system kill session''' || t2.sid || ',' || t2.serial# || '''' kill_session

from   v$locked_object t1,

       v$session       t2,

       all_objects     t3

where  t1.session_id = t2.sid

and    t3.object_id = t1.object_id

and    t3.object_name not like '%$%'

--   and t3.object_name like '%表名%'

order  by 4 desc,

          1;

2)

找出對應的kill session欄位，執行該欄位的alter system kill session 'number'，再重新進行驗證動作。

Reference

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

Sign off application, bounce the database and the lock will be released.  Or run the following scripts to identify the locked record and release the lock:
1. Identify the Oracle serial ID, SID ID and terminate without shutting the database down.
2. Make sure that the user is logged off.
3. Type ps -ef |grep
4. Kill all processes related to that user.
5. Identify SID, serial#
          select distinct           acc.object, ses.osuser, ses.process,           ses.sid, ses.serial#           from v$access acc,           v$session ses           where (acc.owner != 'SYS'           or acc.object = 'PLAN_TABLE')           and acc.sid = ses.sid           and ses.status != 'INACTIVE'           and ses.type != 'BACKGROUND'           and acc.object not in ('V$ACCESS','V$SESSION')           and ses.audsid != userenv('SESSIONID')           order by 1,2,3           /     6  Double-check the identified SID and serial ID:          SELECT osuser,          username,          process,          sid,          serial#,          status,          to_char(logon_time,'DD-MON HH24:MI:SS') logon_time,          machine,          program          FROM v$session          WHERE sid = &SID_NUM          /        7  ALTER SYSTEM KILL SESSION '&SID_NUM,&SERIAL_NUM';            Alternatively use the following scripts to identify the blocking session:            -- check for locked tables            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            order by b.object_name ;            --find_blocked.sql            select sid,            decode(block ,0,'NO','YES') BLOCKER,            decode(request,0,'NO','YES') WAITER            from v$lock            where request > 0 or block > 0 order by block desc            /            SELECT 'alter system kill session '''||vs.sid||','||vs.serial#||'''' ,al.object_name, al.object_type, vs.status,            fu.user_name,vs.process,vs.osuser,vs.username,            to_char(vs.logon_time,'DD-MON HH24:MI:SS') logon_time, vs.program            FROM fnd_logins fl, fnd_user fu, all_objects al, v$lock vl, v$session vs            WHERE fl.pid = vl.sid            AND vl.id1 = al.object_id (+)            AND fl.user_id = fu.user_id            AND to_char (start_time, 'DD-MON-RR') = to_char (sysdate, 'DD-MON-RR')            and vs.sid=vl.sid            and vl.sid = &sid            'ALTERSYSTEMKILLSESSION'''||VS.SID||','||VS.SERIAL#||''''