R12: AP: Upgrade to R12.2 Patch Fails with Error ORA-907 While Executing File apwaud.odf (Doc ID 2922022.1)

Last updated on JANUARY 18, 2023

Applies to:

Oracle Payables - Version 12.2.2 and later
Information in this document applies to any platform.

Symptoms

Upgrading from 11i to R12.2. Update pack patch application fails with error ORA-00907 while applying file apwaud.odf.

AD Worker Log file shows the following

...
...
In table AP_AUD_RULE_ASSIGNMENTS_ALL, the column ORG_ID
is nullable when it should be not null.

Updating the column using the default value :
'to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',nul'

Adding this column and default value to update statement.
Update column with the default value ...

The fetch statement is:
SELECT ROWID FROM AP.AP_AUD_RULE_ASSIGNMENTS_ALL WHERE ORG_ID IS NULL

The update statement is:
UPDATE AP.AP_AUD_RULE_ASSIGNMENTS_ALL
SET ORG_ID = NVL(ORG_ID,
(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',nul))
WHERE
ROWID = :selrid

AD Worker error:
The following ORACLE error:
ORA-00907: missing right parenthesis
occurred while executing the SQL statement:

EXEC SQL PREPARE UPDSTMT1 FROM :upd_stmt1

where the update statement is:

UPDATE AP.AP_AUD_RULE_ASSIGNMENTS_ALL
SET ORG_ID = NVL(ORG_ID,
(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',nul))
WHERE
ROWID = :selrid

ERROR: The update was not completed due to the above error.

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above
...
...

Steps to reproduce: