TriniDBA

Problem: Running the PRC: Interface Supplier Costs completes in error. The output log shows the following error:

ORA-20003: import1_prog:ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "APPS.PA_TRX_IMPORT", line 11279
ORA-06512: at "APPS.PA_TRX_IMPORT", line 9201
ORA-01400: cannot insert NU

Solution: Refer to “PAAPIMP – PRC: Interface Supplier Costs fails with Error ORA-01400: cannot insert NULL into (“PA”.”PA_EXPENDITURE_ITEMS_ALL”.”BILLABLE_FLAG”) (Doc ID 1265338.1)”. This issue was caused by a data error. Open an SR to get a datafix for the issue.

To troubleshoot this issue, first the “PRC: Interface Supplier Costs” concurrent job was run from the Cost Management responsibility with the default parameters (Interface Supplier Invoices: Yes, Interface Receipt Accruals: No, Interface AP Discounts: No). Because the error was a database error (it started with ORA-), the alert log was checked. It showed:

ORA-00060: Deadlock detected. More info in file /u01/oradata/prod/prod/db/tech_st/11.2.0/admin/PROD_apple/diag/rdbms/prod/PROD/trace/PROD_ora_17960.trc.

Checking the /u01/oradata/prod/prod/db/tech_st/11.2.0/admin/PROD_apple/diag/rdbms/prod/PROD/trace/PROD_ora_17960.trc showed the following:

*** 2014-11-29 21:09:39.489
*** SESSION ID:(2178.7221) 2014-11-29 21:09:39.489
*** CLIENT ID:(XXXXXXXX) 2014-11-29 21:09:39.489
*** SERVICE NAME:(SYS$USERS) 2014-11-29 21:09:39.489
*** MODULE NAME:(PAAPIMP_SI) 2014-11-29 21:09:39.489
*** ACTION NAME:(Concurrent Request) 2014-11-29 21:09:39.489



*** 2014-11-29 21:09:39.489
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a0020-0001d8bf        55    2178     X             55    2178           X

session 2178: DID 0001-0037-000007CB    session 2178: DID 0001-0037-000007CB

Rows waited on:
  Session 2178: obj - rowid = 0001E7CE - AAAefOAAnAAAaDyAAA
  (dictionary objn - 124878, file - 39, block - 106738, slot - 0)

----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=arwwdkxfugbcb) -----
UPDATE PA_BC_PACKETS SET STATUS_CODE = 'T', RESULT_CODE = 'F140' WHERE STATUS_CODE = 'P' AND RE
QUEST_ID = :B1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
43ef83e88     11259  package body APPS.PA_TRX_IMPORT
43ef83e88      9201  package body APPS.PA_TRX_IMPORT
43ef83e88      9316  package body APPS.PA_TRX_IMPORT
3cceb9750      1226  package body APPS.PAAPIMP_PKG
3cceb9750      4666  package body APPS.PAAPIMP_PKG
3cceb9750       243  package body APPS.PAAPIMP_PKG
43e8f26d8         1  anonymous block

This showed that the ‘blocker’ and ‘waiter’ process was the same i.e. the session seemed to be blocking itself.

Next, I checked to see if the APPS.PA_TRX_IMPORT and APPS.PAAPIMP_PKG packages were invalid. However, all were valid.

I noticed that when individual projects were submitted in “PRC: Interface Supplier Costs” (using the

When “PRC: Interface Supplier Costs” is run, it spawns another concurrent job “AUD: Supplier Costs Interface Audit“. I checked the “AUD: Supplier Costs Interface Audit” output and it showed:

An ORACLE error has caused Transaction Import to abort

Checking My Oracle Support for the above error, I got a hit on “PAAPIMP – PRC: Interface Supplier Costs fails with Error ORA-01400: cannot insert NULL into (“PA”.”PA_EXPENDITURE_ITEMS_ALL”.”BILLABLE_FLAG”) (Doc ID 1265338.1)”

The SQL statement in the above MOS document, when run, returned two rows, which were the error records. To resolve the issue, an SR was opened with Oracle who, after verifying that the symptoms were a match via an OWC, provided the datafix in the form of two SQL UPDATE statements. These were applied to the TEST instance and then the “PRC: Interface Supplier Costs” was run. It completed successfully.

After backing up the PROD instance, the fix was applied to production and the “PRC: Interface Supplier Costs” was successfully run.