GL Interface Tables

GL_BUDGET_INTERFACE
Used to upload budget data into your Oracle General Ledger application

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

GL_IEA_INTERFACE
Used to create intercompany transaction data.

GL_INTERFACE
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.
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent program
Journal Import
Journal Posting


GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY



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 http://download-east.oracle.com/partners/265498.EXE. 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


Documentation

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);
updateStmt.executeUpdate();

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

// Commit the transaction in the database
txn.commit();


} catch(SQLException sqle) {
updateStmt.close();
}

}



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

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 (

PAYLOAD CLOB

)

/

begin

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');

end;

/

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

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

queue_type => DBMS_AQADM.NORMAL_QUEUE,

max_retries => 5,

retry_delay => 0,

retention_time => 0,

comment => '');

end;

/

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

end;

/

Article By:
Prasanna Jayaraman

Please post your Ideas and Comments

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);

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

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

Parameters:
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.

oracle.apps.fnd.framework.server.OADBTransactionImpl

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

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

Parameters:
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)


Eg:
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
g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
--Check if the log is enabled
IF (g_fnd_debug = 'Y') THEN
--Check if the log level is less than procedure level
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
--Log the message
FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||'.invoked', 'Log text');
END IF;
END IF;


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

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
-Djbo.debugoutput=console

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
-Djbo.debugoutput=console

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

System.out.println

System.out.println


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