During my career, there have been many situations requiring troubleshooting R12 Oracle applications.  This can include developing a brand new concurrent program or figuring out which view is queried to retrieve form values.  In order to troubleshoot what is happening within Oracle Applications, developers need to know how to print statements to a log, write to tables autonomously, and trap errors properly.

In some cases, developers may need to know which APIs are getting executed from a form when a button is pressed, or they may need to know what code is getting executed on an OA Framework form.

Troubleshooting errors and issue resolution are critical components for any developer.

Finding Current Form Record Info

Query current record and navigate to Help -> Record History

 

 

 

 

 

 

 

 

 

 

This will show who created and last updated the current record.  It will also show where the current record is getting pulled from, usually a view.

 

 

Troubleshooting Concurrent Programs

In the code below, we are looping through a cursor.  To help troubleshoot, we are doing a few things:

1.       Writing to the concurrent program log with messages

2.       Writing to a custom table using an autonomous transaction

3.       Using Anonymous Block to trap error

PROCEDURE insert_rows (errbuf OUT VARCHAR2,

retcode OUT NUMBER ) IS

CURSOR c_cursor IS

SELECT i.customername,

i.isa_id,

i.edi_loc_code,

i.cust_location,

i.transactions

FROM table i;

v_error_count NUMBER;

v_success_count NUMBER;

BEGIN

v_error_count := 0;

v_success_count := 0;

—

— Writing to concurrent program log

fnd_file.put_line(fnd_file.LOG,’Beginning Process…’);

—

FOR i IN c_cursor LOOP

—

IF i.edi_loc_code IS NOT NULL THEN

        — Write to custom table using autonomous transaction

xx_write_to_table (‘LOC CODE’, ‘LOC CODE FOUND:’ || i.edi_loc_code, SYSDATE);

/* Create Anonymous Block so we can trap error */

BEGIN

INSERT INTO

xxbpi.xxbpi_edi_generic_loc_code VALUES

(i.customername,

i.isa_id,

i.edi_loc_code,

i.cust_location,

i.transactions);

COMMIT;

/* Trapping Error so whole program doesn’t fail */

EXCEPTION WHEN OTHERS THEN

fnd_file.put_line(fnd_file.LOG,’Unable to Insert location: ‘ || i.cust_location || ‘|EDI Loc Code: ‘ || i.edi_loc_code);

ROLLBACK;

v_error_count := v_error_count + 1;

END;

—

v_success_count := v_success_count + 1;

ELSE

fnd_file.put_line(fnd_file.LOG,’Unable to Insert location: ‘ || i.cust_location);

xx_write_to_table (‘LOC CODE’, ‘LOC CODE Not Found for:’ || i.cust_location, SYSDATE);

 

v_error_count := v_error_count + 1;

END IF;

END LOOP;

  /* Write Counts to Log */

fnd_file.put_line(fnd_file.LOG,’****************************************’);

fnd_file.put_line(fnd_file.LOG,’Success Count:’||v_success_count);

fnd_file.put_line(fnd_file.LOG,’Error Count:’||v_error_count);

fnd_file.put_line(fnd_file.LOG,’****************************************’);

—

COMMIT;

EXCEPTION WHEN OTHERS THEN

fnd_file.put_line(fnd_file.LOG, ‘Error: ‘ || SQLERRM);

END insert_rows;

—

PROCEDURE xx_write_to_table (p_code IN VARCHAR2,

p_msg IN VARCHAR2,

p_date IN DATE) is

 

    pragma autonomous_transaction;

BEGIN

INSERT INTO xx_log_table

VALUES (p_code,

p_msg,

p_date);

COMMIT;

EXCEPTION WHEN OTHERS THEN

fnd_file.put_line(fnd_file.LOG, ‘Error: ‘ || SQLERRM);

END xx_write_to_table;

Running Trace from R12 Oracle Applications Form

In order to run a SQL trace from an R12 form, you must set the “Utilities:SQL Trace” profile for your user as shown below.

Navigate to System Administrator -> Profile -> System.

Populate User Name and “Utilities:SQL Trace” profile and click Find.

 

 

 

 

 

 

 

 

 

Update the user value to “Yes” and save.

 

 

In our example, we are going to see what code is being executed when we try to attach a file to an inventory item.

Navigate to Inventory -> Items -> Master Items and select organization.

Query up an item, then enable SQL Tracing by navigating to Help -> Diagnostics -> Trace -> Trace with Binds and Waits

 

 

 

 

 

 

 

 

 

 

 

Trace file is now being generated…

 

 

 

 

Press OK, then click on the paper clip at the top of the form:

 

 

 

 

Attach your sample file, then log out of applications to end trace session.

Ask your DBA to retrieve file from DB tier (or you can do this if you have access.)

Within raw trace file, you can search for “RPC Call” to see PL/SQL calls as well as Bind Variables that are being passed into the call.

RPC CALL:FUNCTION APPS.FND_GFM.CONSTRUCT_DOWNLOAD_URL(GFM_AGENT IN VARCHAR2, FILE_ID IN NUMBER, PURGE_ON_VIEW IN BOOLEAN) RETURN VARCHAR2;

RPC BINDS:

bind 0: dty=1 bfp=2b030b95b750 flg=08 avl=49 mxl=255 val=”http://URL:80/pls/dev/”

bind 1: dty=6 bfp=2b030b95b870 flg=00 avl=04 mxl=22 val=345101

bind 2: dty=3 bfp=2b030b95b8a8 flg=00 avl=04 mxl=04 val=00

bind 3: dty=1 bfp=2b030c9fff60 flg=0a avl=00 mxl=32767 val=””

 

You can also see SELECT and INSERT statements being executed from form along with binds used:

 

PARSING IN CURSOR #47292088994864 len=138 dep=1 uid=173 oct=2 lid=173 tim=1414603468486009 hv=4221660056 ad=’417579db0′ sqlid=’2qbp29rxu2uws’

INSERT INTO FND_LOB_ACCESS (ACCESS_ID, FILE_ID, TIMESTAMP) VALUES (FND_CRYPTO.RANDOMNUMBER, :B1 , SYSDATE+1) RETURNING ACCESS_ID INTO :O0

END OF STMT

PARSE #47292088994864:c=1000,e=896,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1414603468486008

BINDS #47292088994864:

Bind#0

oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0

kxsbbbfp=2b030c2a3f90  bln=22  avl=04  flg=05

value=345101

Bind#1i

oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00

oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0

kxsbbbfp=2b030c90f538  bln=21  avl=00  flg=09

 

This can be a powerful tool if you are looking for specific APIs or want to know how the form is processing the current transaction.  Remember, we only want to use public APIs in our custom coding practices.  The example above is for SQL Trace demonstration purposes only.

Note: If you run tkrpof on this trace file, you will lose the bind variable information.

Using Diagnostics on OA Framework Page

Diagnostics can be used to see information about OA Framework Page.  To enable this profile, navigate to System Administrator -> Profile -> System.

Populate User Name and “FND: Diagnostics” profile and click Find.

 

 

 

 

 

 

 

 

 

 

 

Populate with “Yes” for user and save.

 

Now we can login to an OA Framework Page like Customer Standard.

Navigate to Receivables Manager -> Customers -> Standard.  In top right of screen, click on Diagnostics.

Different Log options are available.

 

We will show statement level log on screen.

Information after setting log.

 

After returning to Customer Standard form, you will see a warning message at the top of the page:

 

Messages and session variables will appear at bottom of page.  Here are some sample messages including code getting executed:

 

[182]:STATEMENT:[ar.hz.components.base.server.HzPuiBaseVOImpl]:executeQuery = false extraWhereClause = class_category in ( select lookup_code from fnd_lookup_values lvalue where lvalue.lookup_type = ‘SIC_CODE_TYPE’ and lvalue.language = userenv(‘LANG’) and lvalue.view_application_id = 222 and lvalue.enabled_flag = ‘Y’ and trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate))) orderByClause = null [182]:STATEMENT:[ar.hz.components.base.server.HzPuiBaseVOImpl]:SELECT * FROM (select cc.class_category class_category, ltype.meaning class_category_meaning, ltype.description class_category_description, lvalue.lookup_code class_code, lvalue.meaning class_code_meaning, lvalue.description class_code_description, ltype.meaning || ‘ – ‘ || lvalue.meaning classification from hz_class_categories cc, fnd_lookup_types_tl ltype, fnd_lookup_values lvalue where cc.class_category = ltype.lookup_type and ltype.language = userenv(‘LANG’) and ltype.view_application_id = nvl(cc.class_owner_id, 222) and cc.class_category = lvalue.lookup_type and lvalue.language = userenv(‘LANG’) and lvalue.view_application_id = nvl(cc.class_owner_id, 222) and lvalue.enabled_flag = ‘Y’ and trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate))) QRSLT WHERE (class_category in ( select lookup_code from fnd_lookup_values lvalue where lvalue.lookup_type = ‘SIC_CODE_TYPE’ and lvalue.language = userenv(‘LANG’) and lvalue.view_application_id = 222 and lvalue.enabled_flag = ‘Y’ and trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate))))

[182]:PROCEDURE:[ar.hz.components.base.server.HzPuiBaseVOImpl]:HZPUI: oracle.apps.ar.hz.components.base.server.HzPuiBaseVOImpl.initQuery(StringBuffer, StringBuffer, Vector, Boolean) done.

[182]:PROCEDURE:[ar.hz.components.account.search.server.HzPuiAcctSearchFormAMImpl]:HZPUI: oracle.apps.ar.hz.components.account.search.server.HzPuiAcctSearchFormAMImpl.initQuery(String, StringBuffer, Vector, Boolean) done.

[182]:PROCEDURE:[ar.hz.components.account.search.webui.HzPuiAcctOrgSearchFormCO]:HZPUI: oracle.apps.ar.hz.components.account.search.webui.HzPuiAcctOrgSearchFormCO.makeUIChangesBeforeRestore(OAPageContext, OAWebBean) done.

 

These troubleshooting tips should help any developer get started with issue resolution within R12 Oracle Applications.