Change Tracking Queries Failing For Specific GL Objects with ORA-00907: missing right parenthesis (Doc ID 1511909.1)

Last updated on OCTOBER 10, 2023

Applies to:

Symptoms

====Issue Clarification====
 
On :  5.5.1 version, Integra Apps

When attempting to to use change tracking queries GL 12.1.3 Journal Authorization Limits or Open and Close Periods from PROD  
the following error occurs.

ERROR
-----------------------
#14:31:20 - APSChgTrkAction action: objects, pageMode:
#14:31:20 - APSChgTrkDB.java::refreshCTObjects(, b4 exec: call tickchgt_pkg.refresh_ct_objects(26,100,'9',100,'AB8267CC59290297E630','E1FFCCCAB6','2011-08-31')
#14:31:20 - APSChgTrkDB.java: refreshCTObjects(): Procedure-call
#14:31:20 - APSChgTrkDB.java::refreshCTObjects(, after exec: tickchgt_pkg.refresh_ct_objects(26,100,'9',100,'AB8267CC59290297E630','E1FFCCCAB6','2011-08-31')
#14:31:22 - Procedure - call refresh_ct_objects_details
#14:31:26 - Procedure - call sp_apply_security_ledgers
#10:19:58 - java.sql.SQLException: ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_SQL", line 1010
ORA-06512: at "AMHOME.TICKCHGT_PKG", line 2787
ORA-06512: at line 1

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. From the CCG Workbench, click on Change Tracker.
2. Then click on Query to take you to the screen where your various queries are defined.
3. Click on the Query to use.   GL Open and Close Periods, 0r Journal Authorization Limits.
4. Review am.log file.

Note : Deleting the GL query , then accessing Gl changes in change tracking also produces the error.

WHAT WORKS
---------------------
Creating a query for GL 12.1 which includes all objects except Journal Authorization Limits or Open and Close Periods will produce data.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.