Search Results

Wednesday, April 29, 2009

GL Interface Tables

Used to upload budget data into your Oracle General Ledger application

Used to insert, update, or delete daily rates in the GL_DAILY_RATES table.

Used to create intercompany transaction data.

Used to create journal entries, Transfers source information into a target set of books, Creates accounting for Intercompany Transactions etc.

Base Tables
Following are the base table for Journal entry.

Concurrent program
Journal Import
Journal Posting


Usefull metalink notes
  1. Troubleshooting Journal Import
  2. Journal Import FAQ
  3. Oracle General Ledger Posting - FAQ
  4. Technical Reference Manual

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Implementation Documentation

Following are the list of standard documentation that needs to be prepared for Oracle Applications Implementation.

You can download the documentation templates from After installation you can find the templates in C:\Method\OM30\AIM30\AIM30FND\Wordlib path.

Among this list most important documentation for Oracle Apps implementation are MD030, MD040, MD120, BR.100, MD050, MD070, TE020.

Technical consultants read MD050 and write MD070, TE020.

Business Process Architecture

BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

Business Requirements Definition (RD)

RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

Business Requirements Mapping

BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

Application and Technical Architecture

TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

Module Design and Build

MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

Data Conversion

CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data


DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

Business System Testing

TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

Perfomance Testing

PT.010 - Define Performance Testing Strategy
PT.020 - Identify Performance Test Scenarios
PT.030 - Identify Performance Test Transaction
PT.040 - Create Performance Test Scripts
PT.050 - Design Performance Test Transaction Programs
PT.060 - Design Performance Test Data
PT.070 - Design Test Database Load Programs
PT.080 - Create Performance Test TransactionPrograms
PT.090 - Create Test Database Load Programs
PT.100 - Construct Performance Test Database
PT.110 - Prepare Performance Test Environment
PT.120 - Execute Performance Test

Adoption And Learning

AP.010 - Define Executive Project Strategy
AP.020 - Conduct Initial Project Team Orientation
AP.030 - Develop Project Team Learning Plan
AP.040 - Prepare Project Team Learning Environment
AP.050 - Conduct Project Team Learning Events
AP.060 - Develop Business Unit Managers’Readiness Plan
AP.070 - Develop Project Readiness Roadmap
AP.080 - Develop and Execute CommunicationCampaign
AP.090 - Develop Managers’ Readiness Plan
AP.100 - Identify Business Process Impact onOrganization
AP.110 - Align Human Performance SupportSystems
AP.120 - Align Information Technology Groups
AP.130 - Conduct User Learning Needs Analysis
AP.140 - Develop User Learning Plan
AP.150 - Develop User Learningware
AP.160 - Prepare User Learning Environment
AP.170 - Conduct User Learning Events
AP.180 - Conduct Effectiveness Assessment

Production Migration

PM.010 - Define Transition Strategy
PM.020 - Design Production Support Infrastructure
PM.030 - Develop Transition and Contingency Plan
PM.040 - Prepare Production Environment
PM.050 - Set Up Applications
PM.060 - Implement Production Support Infrastructure
PM.070 - Verify Production Readiness
PM.080 - Begin Production
PM.090 - Measure System Performance
PM.100 - Maintain System
PM.110 - Refine Production System
PM.120 - Decommission Former Systems
PM.130 - Propose Future Business Direction
PM.140 - Propose Future Technical Direction

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Oracle Open Interface

What is Oracle open interfaces?

Open interfaces used to integrate Oracle Applications with the non-oracle systems.
such as
1. Import data from your custom applications or legacy systems.
2. Migrate the old custom application data to the Oracle applications.
3. Import data from the banks, partners, suppliers, customers etc.

What is conversion?

Conversion is a one time Interface to migrate data from legacy application to the Oracle

Inbound Interface
The Interface which transforms and loads the data into the Oracle Applications system is called inbound interface.

Outbound Interface
The Interface which extracts the data from the oracle Application system for the purpose of loading into the external system is called outbound interface.

Common approach to interface is
  1. Data would be extracted from the source machine and loaded into the custom table (stagging table) in Oracle apps database. Depending upon the complexity of the data Stagging table can be avoided.
  2. The extracted data is then transformed into a form which oracle can recognize and pushed into interface tables.
  3. Final step is load the data from interface table to base table of Oracle applications.
This whole process is called extract load and transformation (ETL).

Oracle provides Interface tables for data intensive applications like Invoice, receipts, journals etc and PL/SQL APIs for master data interfaces like people, items, suppliers etc.

Traditional approach used sql Loader to load the data and pl/sql program to validate and extract data.
Modern approach uses BPEL, ESB for online data interfaces and Oracle Data Integrator for data intensive batch processing.

Common Interface technologies
1. XML Gateway
2. SQL*Loader
3. DB Link
4. Java Concurrent Program
5. BPEL or ESB
6. Oracle Data Integrator or ODI
7. PL/SQL package for reading file or XML data
8. Oracle Advanced Queuing

Some Useful Links

1. Oracle Integration Repository - List of Oracle open interfaces
2. Technical reference Manual - Column level details of Interface tables, base tables and APIs
3. Technical reference Manual for different versions.

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Call pl/sql in ADF

How to Call pl/sql from ADF?

You can use the following code to call a pl/sql block from the ADF bc Application module.

public void executePlsqlProcedure(){

CallableStatement updateStmt = null;
try {
// Put your PL/SQL block in a String variable.
String Stmt = "begin delete test_table where header_id = :1; "+
" :2 = xxx_pkg.yyy_function(); end;";
OADBTransaction txn = getDBTransaction();

// Pass the PL/SQL block to the callable Statement
updateStmt = txn.createCallableStatement(Stmt, 1);

// Set all the bind variables before calling the execute command
updateStmt.setInt(1, headerIdToDelete);

// And register the output parameter types
updateStmt.registerOutParameter(2, Types.DOUBLE);

// After execute you can get the value of pl/sql block output
Number amount = new Number(updateStmt.getDouble(2));

// Commit the transaction in the database

} catch(SQLException sqle) {


And you can call the above Application Modules method from managed bean using following code.

FacesContext facesContext = FacesContext.getCurrentInstance();
ExpressionFactory exp = facesContext.getApplication().getExpressionFactory();
DCBindingContainer bindingContainer = (DCBindingContainer)exp.createValueExpression(facesContext.getELContext(),"#{bindings}",DCBindingContainer.class).getValue(facesContext.getELContext());
DCIteratorBinding itr = bindingContainer.findIteratorBinding("Dept1Iterator");
DemoAppAMImpl svc = (DemoAppAMImpl)itr.getDataControl().getApplicationModule();

Or you can call this method by exposing into client interface.

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Wednesday, April 15, 2009

AQ creation script

Advanced queue is highly used in SOA to forward messages between different services through database. We will discuss AQ in detail in the future. Now we will see how to create AQ in database.

create or replace type AQSEC.CT_MESSAGE_TYPE as OBJECT (





DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => '"Username"."HR_Q"',

queue_payload_type => 'Username.CT_MESSAGE_TYPE',

storage_clause => '',

sort_list => 'enq_time',

multiple_consumers => false,

message_grouping => DBMS_AQADM.TRANSACTIONAL,

comment => '',compatible => '10.0');



begin DBMS_AQADM.CREATE_QUEUE (queue_name => '"HR_Q"',

queue_table => '"Username"."HR_Q"',


max_retries => 5,

retry_delay => 0,

retention_time => 0,

comment => '');



begin DBMS_AQADM.START_QUEUE (queue_name => '"Username"."HR_Q"',enqueue => true, dequeue => true);



Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

XPATH quick reference

under construction

Logging in Apps

How to do logging in the OAF controller?

You can do logging in OAF page using following statement
pageContext.writeDiagnostics(this,"building is :"+s_building,OAFwkConstants.STATEMENT);

void writeDiagnostics(Object module,
String messageText,
int logLevel)

Writes an entry to the diagnostics log according to a specific log level.

module - current module, usually the "this" pointer
messageText - message to be included in the log. Limit 4000 characters.
logLevel - category or type of log message. Valid values are are from OAFwkConstants. (UNEXPECTED, ERROR, EXCEPTION, EVENT, PROCEDURE, STATEMENT, PERFORMANCE)

How to do logging in the bc4j objects?

The pagecontext won't be available for Application module, View Object Impl and Entity object Impl. So in this case, you can use OADBTransactionImpl.writeDiagnostics to log your messages.


public void writeDiagnostics(Object module,
String messageText,
int logLevel)

Writes an entry to the diagnostics log according to a specific log level.

module - current module, usually the "this" pointer
messageText - message to be included in the log. Limit 4000 characters.
logLevel - category or type of log message. Valid values are are from OAFwkConstants. (UNEXPECTED, ERROR, EXCEPTION, EVENT, PROCEDURE, STATEMENT, PERFORMANCE)

if (OADBTranasctionImpl.isLoggingEnabled(OAFwkConstants.PROCEDURE)) {
OADBTransactionImpl.writeDiagnostics(this, "your message", AFwkConstants.PROCEDURE);

How to see the log?

You can see the output in the OAF Screen itself.
1. Select Diagnostics button from any page
2. In the Diagnostics page select Show Log on Screen option.

Now you will be able to see the application log appened to the bottom of the page.

How to debug pl/sql program?

The standard way to debug the plsql code is using fnd_log package.
set the following profile options to enable log.

FND: Debug Log Module = '%'
Enables log for all modules

FND: Debug Log Enabled = 'Yes'
Enables the logging

FND: Debug Log Level = 1
This will log all the statement level log messages.

You can use the following procedure to log the messages in pl/sql, forms or reports.

--Get the log enabled profile option
--Check if the log is enabled
IF (g_fnd_debug = 'Y') THEN
--Check if the log level is less than procedure level
--Log the message
FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||'.invoked', 'Log text');

How to see the pl/sql debug log?

All the FND_LOG.string messages would be logged in the fnd_log_messages table ordered by log_sequence column.

1. Note the sequence number before running the procedure
select max(log_sequence) from fnd_log_messages;

2. Run your pl/sql program

3. Now run the following query to see the log messages.
select count(*) from fnd_log_messages where log_sequence BETWEEN 1925713 and 1929506;

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Wednesday, April 08, 2009

Debug bc4j objects or ADF BC Objects

How to debug the VO, LOV, poplist queries?

You can see the executed queries and its bind values in the jdeveloper console or messages window.
By default, this option is not available in jdeveloper.

To enable the debug console option in 11g and 12c

I was just going through my previous posts and found the this option still works on Jdeveloper 11g and 12c. So you could use the debug option on the ADF BC - Model project and it will log all the queries on the jDeveloper console during execution.

Just follow same set of instructions as given for 10g jdeveloper.

To enable the debug console option in 10g

1. Right click on project and select project properties

2. Select Run/Debug

3. Ensure Default in selected in Run configurations

4. Select Edit button.

5. You can see the Launch Settings window.

6. Add the following parameter in the java options

7. Select OK

8. Rebuild the project and run again to see the debug information in jdeveloper log window

To enable the debug console option in Jdev 9i
1. Right click on project and select project properties

2. Navigate Configurations -> Development -> runner

3. Add the following parameter in the java options

4. Select OK

5. Rebuild the project and run again to see the debug information in jdeveloper log window

This option would be very helpful in finding out what going on in the bc4j objects. Including LOV queries, poplist queries, VOs etc. Whenever the query is executed through the AM or procedure is called, the bind variable values and executed sql will be displayed in the jdeveloper message window.

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Thursday, April 02, 2009



This is the most commonly asked question in web applications development in jdeveloper including OA Framework, ADF, ejb, web services etc.

Can I leave System.out.println or System.err.println in the production code?

System.out.println comes handy when debugging the application because you can see the messages pretty much easily in the jdeveloper console. And it takes relatively less time than the debugging option in Jdeveloper. So even experienced developers use it for debugging. You may think, "Anyway this doesnot any affect in the production system". But actually System.out.println statements will have serious performance issue in the production system if you fail to remove them in the final code.

The reason is that the application server will have only one output stream and one error stream in the JVM. Hence multiple threads calling the System.out.println() have to be synchronized.

Considering the production system with thousands of users, back end programs, webservices, scheduled process running parralley in the machine. Calling system.out.println() will potentially block the performance of whole system.

Also the application server doesn't redirect the std out to a file, and is lost.

Hence always remove the System.out.println() /System.err.println() from the production code.

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Manipulating OA Footer

Manipulating OA footer region

How to render copyright information and privacy statement for custom page?

To render standard footer just Set the Auto Footer property in the pageLayout region to 'True'. This will render the Footer with default copyright information and privacy statement.

To render different privacy statement and copyright information
1. Set the Auto Footer property in the pageLayout region to 'False'
2. Right click pageLayout region and select New -> Copyright
3. Right click pageLayout region and select New -> Privacy

How to display "About this page" link?

Set FND: Diagnostics profile option to 'Yes'.
This profile option renders "About this Page" link at the botton left corner of the page and Diagnostics link at the top right corner of the page.

How to personalize page footer items such as Privacy Statement, copyright, standard footer?

This personalization is somewhat tricky because the navigation to this item is different from normal personalization.

Follow these steps to personalize the OA footer
1. Login in SSHR.
2. Select Personalize Page link at top right of page.
3. Select Choose context button.
4. In the scope drop down box select OA Footer.
5. Click on apply.
6. Expand all if its not already expanded.
7. Now you can see all the footer elements.
8. Click on personalize pencil icon and personalize the desired items.

How to display custom copyright infomation in the standard page?

You can edit the copyright information in sshr by updating fnd_new_messages table
select message_text from fnd_new_messages where message_name='JTF-COPYRIGHT';

Other way is just personalize the page and update the prompt in copyright item.

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

Monday, January 05, 2009

SQL Trace

How to do SQL Trace on the Oracle Applications 11i?

In this article, I am going to explain what to get trace for various technology components of the Oracle applications.

Hence we are going to see

  • What is sql trace?
  • How to take sql trace for a session?
  • How to do sql trace for a Form?
  • How to do sql trace for a Report?
  • How to do sql trace for a OAF Page?

What is sql trace?
  • SQL Trace is a diagnostic tool for sql runtime and it gives a raw dump of SQL queries executed in the session and this dump can be read using tkprof command.
  • Statements are displayed in the order they are processed.
  • Every statement excuted will be displayed with statistics and optimizer routing.
  • You can see what values are being bound at runtime.
  • If you are getting a runtime error like ORA-942 or ORA-904, you can find out which statement is causing it.

How to take sql trace for a session?

Enabling trace for the current session
  • alter session set sql_trace=true;
  • alter session set events ‘10046 trace name context forever, level <x>’;
  • dbms_session.set_sql_trace(true);
  • dbms_support.start_trace(waits=>true,binds=>true);

Enabling trace for a different session
  • dbms_system.set_sql_trace_in_session (SID,SERIAL#,TRUE);

How to do sql trace for a Form?

  • Help -> Diagnostics -> Trace and then Choose the trace level.
  • Note the path of the trace file displayed in the Dialog box
  • Do the transcation that is causing the performance problme
  • Help -> Diagnostics -> No Trace, to disable the trace
  • Trace file XXX.trc is returned in the path displayed in the dialog box.
From Trace 1

Copy the path displayed in the alert window

Form Trace 2

How to do sql trace for a Report?

Add the following statement in the before report trigger
  • 2. Upload the report in Oracle Apps
  • 3. Goto Application Developer Resposibility
  • Select Concurrent -> Program
  • 4. Query for the Concurrent Program that executes the report
  • 5. Select "Enable Trace" checkbox and save the record
Reports Trace
  • 6. Now run the report to get the trace file. (don't forget to disable the trace after running the report)

How to do sql trace for a OAF Page?

  • Set profile FND : Diagnostics to Yes at user level
  • Login to Self Service as the above user
  • Click on Diagnostics icon at the top of page
oaf trace

  • Select ‘Set Trace Level’ and click Go
  • It Displays following options
    • Disable Trace
    • Trace (regular)
    • Trace with binds
    • Trace with waits
    • Trace with binds and waits
  • Select the desired trace level and click Save
oaf trace2
  • Perform the activity that you want to trace
  • Disable the Trace using Diagnostics Page.
  • Exit application
To determine where the raw trace file is located.
From SQLPlus execute following query:

SELECT value FROM v$parameter WHERE name = 'user_dump_dest'

How do I read the trace file or .trc file?
  • tkprof <tracefile> <outputfile> explain=username/password sort='(sorting options)'
  • Eg: tkprof POSTDI9837.trc output.prf explain=apps/apps sort=‘(prsela, exeela, fchela)’

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments