PRC: Generate Revenue Accounting Events(PATTGL) ends with ORA-01732 Error

When working with PA Accounting, the standard program "PRC: Generate Revenue Accounting Events" failed with the Error - ORA-01732

To fix the above error, we have 2 recommendations

Recommendation 1:

Update the init.ora parameter to a value of 'False':
This can be done by editing the init.ora file which typically requires bouncing the database when completed.
(Option 1 requires DB bounce)

Recommendation 2:

Modify the session by running this SQL -- alter system set optimizer_secure_view_merging = false;
(NO DATABASE BOUNCE REQUIRED for Option 2)

Query to check the DB Parameter value,

select * from v$parameter where name like '%optimizer_secure_view_merging%';

Value: FALSE

Journal entry cannot be reversed as its not transferred to GL (Error in xla_DataFixes_Pub.Reverse_Journal_entries)

When working with Accounts Payable Trial Balance Report (ATBR) and you're getting the error while reversing the journal though Online Accounting then follow the below steps,

Error:

Journal entry cannot be reversed as its not transferred to General Ledger.
"-: XLA-95103: An internal error occurred. Please inform your system administrator or support representative that:

An internal error has occurred in the program xla.plsql.xla_datafixes_pub.reverse_journal_entries. Journal entry cannot be reversed as its not transferred to General Ledger.."

l_Return_Status = U

Undo_Accounting : Error in xla_DataFixes_Pub.Reverse_Journal_entries
:FND?FND_AS_UNEXPECTED_ERROR?N?PKG_NAME?xla.plsql.xla_datafixes_pub?N?PROCEDURE_NAME?reverse_journal_entries?N?ERROR_TEXT?ORA-20001: -: XLA-95103: An internal error occurred. Please inform your system administrator or support representative 

Step 1 : Check the current version of the file xlajedfp.pkb using the below string

strings -a $XLA_TOP/patch/115/sql/xlajedfp.pkb |grep '$Header'

(Login to APP Tier and execute the command under the Application Manager Access)

If it is less then the header version /* $Header: xlajedfp.pkb 120.14.12020000.11 2015/01/19 07:07:32 tasrivas ship $ */ then 

Step 2: kindly apply the below patch based on the EBS version 

For R12.1.x: xlajedfp.pkb 120.1.12010000.25
For R12.2.x: xlajedfp.pkb 120.14.12020000.11

Note: Before applying the patch kindly verify the patch is applied or not in your database using the query,

select ad_patch.is_patch_applied('R12',-1,19793092) from dual; 
NOT_APPLIED

Step 3: After Patch applied, re-confirm the header string and make sure its matches with updated one,

strings -a $XLA_TOP/patch/115/sql/xlajedfp.pkb |grep '$Header'

After patch applied, the patch verification will return the code EXPLICIT

select ad_patch.is_patch_applied('R12',-1,19793092) from dual; 
EXPLICIT

Oracle SQL-PLAN based on SQL query

To generate the cost for the query based on single table or a group of table pls follow the below syntax in TOAD / SQL Developer,

Step 1:

EXPLAIN PLAN FOR
select * from ap_suppliers;

Result =>
Explained.

Step 2:

select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

 Result =>
------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|   1 |  TABLE ACCESS FULL| AP_SUPPLIERS |
------------------------------------------

Step 3:

select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic'));

 Result =>

EXPLAINED SQL STATEMENT:
------------------------
select plan_table_output from table(dbms_xplan.display('plan_table',null
,'basic'))

Plan hash value: 2137789089

-----------------------------------------------------
| Id  | Operation                         | Name    |
-----------------------------------------------------
|   0 | SELECT STATEMENT                  |         |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |
-----------------------------------------------------

How to Find the exact Error line in SQL Scripts using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

If you encounter any error in SQL file and many of the times the error console never display the correct line number where the actual error exist. Instead it will display the wrong lines and which will re-route us in somewhere !!!

To avoid this we have an standard API DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and this show the correct line number where our real issue exist !

Sample Script:

DECLARE

err_code NUMBER;
err_msg  VARCHAR2(240);

BEGIN

{
...
.....
Logic of the Program
...
.....
}

EXCEPTION
     WHEN OTHERS THEN

      ROLLBACK;

err_code := SQLCODE;
err_msg  := SQLERRM;

FND_FILE.PUT_LINE(fnd_file.LOG,err_code||'->'||err_msg);
FND_FILE.PUT_LINE(fnd_file.LOG,DBMS_UTILITY.FORMAT_ERROR_STACK);
FND_FILE.PUT_LINE(fnd_file.LOG,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;
/

Note: If your SQL scripts contains commented lines like /* */ or -- or REM then kindly ignore those lines and start your count from actual SQL script starts

Ex: SQL Code 

REM Script Name

-- Development Script version 1.0

/* 

Comments 1

Comments 2

*/

Declare

Line 1

Line 2

BEGIN

Line 3

Line 4

Exception 

When Others then 

Line 5

END;

Total Lines:  23 but actual script contains only 16

Kindly Ignore the comment lines (i.e) first 6 lines and then consider the remaining lines for the error findings

How to check Patch Details in R12.2.x

select ad_patch.is_patch_applied('R12',-1,19283503) from dual;

Oracle Branding Logo Change

Oracle Branding Logo Change

Step 1 : Place the XXX.Png file under $OA_MEDIA directory in App Tier.

Step 2 : Query the Profile & Change the value . 

              System Administrator > Profile - System.

              Query profile "Corporate Branding Image for Oracle Applications"

              Change the SITE level value to the name of the custom image file { XX_CUSTOM_LOGO.jpg }

Step 3 :  Clear the Cache From Functional Administrator.

TOAD TNS Entry Issue

TOAD for Oracle 9 or TOAD 10 – 64-bit Windows – ORA-12154: could not resolve the connect identifier specified or ORA-6413: connection not open

I recently downloaded the new 10.x version of TOAD and could not connect to my databases. Still running TOAD 9.x, no problem. This really threw me off for a minute, so I thought I would share the resolution.

You are likely running a 64-bit Windows OS and have installed TOAD into C:\Program Files (x86)\....

I simply copied over my entire Quest Software directory from C:\Program Files (x86)\... to C:\Program Files\..., and restarted the TOAD.exe. Everything is working great, without having to reinstall anything.