Oracle A.I.M. Methodology – template list

Oracle A.I.M. Methodology encompasses a project management methodology with documentation templates that support the life cycle of an implementation. The life cycle methodology and documentation templates allows A.I.M. to be a very useful tool for managing implementation projects successfully. This is a depiction of the A.I.M. methodology life cycle:

Application Implementation Method is a proven approach for all the activities required to implement oracle applications. there are eleven processes of implementation.

1. Business Process Architecture [BP] – This phase outlines:

  • Existing Business Practices
  • Catalog change practices
  • Leading practices
  • Future practices
BP.010 Define Business and Process StrategyBP.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

2. Business Requirement Definition [RD] This phase explains about the initial baseline questionnaire and gathering of requirements.

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

3. Business Requirement Mapping [BR]In this phase the requirements of business are matched with the standard functionality of the oracle applications.

BR.010 Analyze High-Level GapsBR.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

4. Application and Technical Architecture [TA]This outlines the infrastructure requirements to implement oracle applications.

TA.010 Define Architecture Requirements and StrategyTA.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

5. Build and Module Design [MD]This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.

MD.010 Define Application Extension StrategyMD.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

6. Data Conversion [CV]Data Conversion is the process of converting or transferring the data from legacy system to oracle applications. Ex. Transferring customer records from the legacy to the Customer Master.

CV.010 Define data conversion requirements and strategyCV.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

7. Documentation [DO]Documentation prepared per module that includes user guides and implementation manuals.

DO.010 Define documentation requirements and strategyDO.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

8. Business System Testing [TE]A process of validating the setup’s and functionality by QA(functional consultant) to certify status.

TE.010 Define testing requirements and strategyTE.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

9. Performance Testing [PT] Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc

PT.010 – Define Performance Testing StrategyPT.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

10. Adoption and Learning [AP]This phase explains the removal of the legacy system and oracle application roll out enterprise wide.

AP.010 – Define Executive Project StrategyAP.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

11. Production Migration [PM]The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.

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

thanks – Shivmohan Purohit

Oracle Financials – A Case Study

Here sharing recent communication on options to implement oracle Apps, i like you all to share you thoughts on this below scenario

I need the list of points or concerns  we need to take care of at the time of migration from 11.5.9 to

Also I am in need information for M&A (merger and acquisition) perspective.
Say Company A acquires Company B. Company A is on and Company B is on 11.5.9
Now A wants to get B on the same instance ( as a separate operating unit. In this case what are the areas we need to concentrate on.
e.g. Common Item Master, Customer base.., B’s interfaces with some 3rd party system, Transaction between A and B…… etc

It would be very great if you could help me with this information …


Here if we consider this M&A , where Company A on 11.10 is target/ parent and company B is on 11.9 as legacy / secondary.

This case is like adding new operating unit in 11.10 for Company A , where it has advantage that Company B is already on Oracle with a lower version. Now here I think few thought to start with
GL Chart Of Accounts – This need to review the COA structure between A and B , having more different will make things more complex , having same will be great benefit. COA structure will drive the complexity of whole exerercise.
SOB and MRC – need to review the kind of SOB and MRC setup in both A & B , this will drive the kind of impact it will have and complexity of whole exercise.

As point 1 and 2 will form the foundation for merging B in A , and will outline the base architecture for remaining transaction and mater data to move in Company A.
Supplier/ Party / Customer / Employee/ Item / Inventory – all these master data need to review for company B and see if it duplicating or it need some mapping/ merging / cleaning or translation etc ,
GL transaction / GL history / GL Balance – this need to bring in A , this will depend on GL COA and GL SOB structure changes happened from B to A
AP ( open Invoice, payment history ) – this will business decision if they like to bring , the strong advice is to close all open invoice and keep history transaction in kind of archinve database for Company B till legally it require, This need to review if these are really require to bring in Company A where they may or may not useful.
AR ( Transaction , receipts  , history etc ) – This is recommended to bring in A , because this is surely impact the cash flow and allow lot of benefit to make future analysis and drive cash operations. Company A having any kind of Datawahrehouse , then I would recommend to bring all AP and AR transaction and history there directly rather bringing them in Apps and then send to DWH.
For fixed assets – I don’t see it could be much issue, all assets need to bring in A , and surely this can be manually if they are less in volume or  in automated way if large enough.

Another aspect to look for is Third party integration, Customization , Extension, Custom Reporting – As company B is moving to higher version so it need to consider as upgrade for them , from that point of view, you need to review if any changes needed in integration, or enhance/decommission customization, any more reports or less report need to migrate from B to A.

All above thoughts are surely very high level and brief , based on complete study , it will be more than 200 action items or more will outline only for fianancials to bring from Company  B to A , so it is very much driven from GL Structure between B and A and IT strategy of organization.

Thanks – Please share you thoughts to help in outline better approach

Oracle Database -External Table – How to populate/ outbound file creation using External table

As you know we can load data from OS flat files into the Oracle environment,Now here showing how to create an external file / populating external table

Lets take a simple example: –

Step 1: – Create a directory

SQL> create directory my_dir as ‘/usr/abc’; —

Make sure Oracle OS user hasprivilege to write on this directory.

Step 2: – Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: – Create the External Table:

SQL> CREATE TABLE scott.extract_emp_dept



LOCATION (’emp.exp’))

reject limit unlimited


select e.ename,d.dname from emp e, dept d where


SQL> select * from scott.extract_emp_dept;

And you will find a file generated i.e. ’emp.exp’ in /usr/abc directory. Now you can take this file to the target database and create an external table and associate the file with that table.

Step 4: – to be performed on the source database

SQL> set long 2000

SQL> Select dbms_metadata.get_ddl(‘TABLE’,’EXTRACT_EMP_DEPT’) from dual;

The above command will give you the DDL command to create the table at target database.

Oracle Database : External Tables Overview and Usage

What are External Tables?

External tables are like regular SQL tables with some exceptions:
          – The metadata of external tables is created using the SQL "CREATE TABLE … ORGANIZATION EXTERNAL" statement. 
          – The data resides outside the database in OS files, thus the EXTERNAL organization.
          – The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located. The owner of the files is Oracle. Users granted access via the Database can access the files through the external table. 
          – The data is read only. 
          – You cannot perform any DML operations, nor create indexes. 
          – The external table can be queried and joined directly, in parallel using the SQL statement SELECT.

A. Create the external table to extract the data without loading them inside the database

1. Create flat files emp1.dat

2 Create a directory that defines the location of the directory where the flat files reside on the OS
3 Create the external table (metadata)
4 Select data from the external table to verify that data are visible

  • Create flat files emp1.dat

$ vi /u01/ora9i/data/emp1.dat

Check OS level permissions and ensure the user has read/write privileges to this directory.

  • Create a logical directory to map the OS directory where the external files reside and grant permissions to SCOTT
  • SQL> create directory emp_dir as ‘/u01/ora9i/data’ ;
    Directory created.
    SQL> GRANT READ ON DIRECTORY emp_dir TO scott;
    Grant succeeded.
    Grant succeeded.

  • Create the external table :
  • SQL> create table scott.emp_ext
    (emp_id number(4), ename varchar2(12),
    job varchar2(12) , mgr_id number(4) ,
    hiredate date, salary number(8), comm number(8),
    dept_id number(2))
    organization external
    (type oracle_loader
    default directory emp_dir
    access parameters (records delimited by newline
    fields terminated by ‘,’)
    location (’emp1.dat’);
    Table created.

  • Check the data retrieval:

SQL> select * from scott.emp_ext;

—— ——-  ——–   ——- ——— ——  —– ——-
7369   SMITH    CLERK      7902    17-DEC-80 100     0     20
7499   ALLEN    SALESMAN   7698    20-FEB-81 250     0     30
7521   WARD     SALESMAN   7698    22-FEB-81 450     0     30
7566   JONES    MANAGER    7839    02-APR-81 1150    0     20
7654   MARTIN   SALESMAN   7698    28-SEP-81 1250    0     30
7698   BLAKE    MANAGER    7839    01-MAY-81 1550    0     30
7934   MILLER   CLERK      7782    23-JAN-82 3500    0     10
7 rows selected.

Very Common Oracle Apps DBA Questions

I collection few regular asked question for Oracle Apps DBA to hire Apps DBA for my team, now publishing for reader’s use. These question are even relevant for Oracle Technical experts / developer also.  I would like to request share your questions if you have any.

Q. What is difference between fresh database and vision database install types ?
Fresh Database –
Database is installed with Apps but with no data
Vision Database – Database installed with Apps with dummy data

Q. Whatis US directory in $AD_TOP or under various product TOP’s .
US directory is default language directory in Oracle Applications. If you have multiple language Installed in your Applications then you will see other language directories beside US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

Q. Whats main concurrent Manager types.
ICM – Internal Concurrent Manager which manage concurrent Managers
Standard Managers – Which Manage processing of requests.
CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.

Q. Where is Concurrent Manager log file location ?
By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.

. List some ad utilities and their functions?
adadmin, adctrl, adpatch, adlicmgr, admrgpch, adsplice and adworker

Q. List out the modules related to oracle Apps DBA ?

FND – Application Object Library, AU – Application Utilities, AD – Application DBA

Q. How many concurrent mangers can be used ? what is the limit ?
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).

Q. How would you check if the tablespace is in backup mode ?
– Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;

Q. What is “Custom TOP” in apps ?
– Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP

Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?
– System picks up dbc file based on profile option value “Applications Database ID

Q Where is applications start/stop scripts stored ?

Ans: $COMMON_TOP/admin/scripts/$CONTEXT_NAME

How to confirm if Report Server is Up & Running ?

Ans: Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like
ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_VISION
where VISION is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files

How you will start Discoverer in Oracle Apps 11i ?

Ans: In order to start dicoverer you can use script under $OAD_TOP/admin/scripts/$CONTEXT_NAME
or under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

How to check number of forms users at any time ?

Ans: Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc –l

What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?

Ans: 0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

Q. What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?

Ans: .ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

Q. What is dev60cgi & f60cgi ?

Ans: cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

Q. How to compile an Oracle Reports file ?

Ans: Utility adrepgen is used to compile Reports. Synatx is given below

adrepgen userid=apps\ source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

Oracle 11g – PL SQL – New features — Reference

I found a nice article, contain reference DocID from Metalink to know latest and new features of Oracle Database 11g PL/SQL

All the new features available with 11.1 release of oracle are listed together in
Note 869084.1 : New PL/SQL Features for 11g Release 1 (11.1).
The contents of the article is pasted below.

New PL/SQL Features for 11g Release 1 (11.1)

  • PL/SQL Native Compilation
    In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE , rather than the default value of INTERPRETED , code is compiled directly to machine code . When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.
    Note 462449.1 : PL/SQL Native Compilation on Oracle 11G


  • PL/SQL Function Result Cache
    The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
    Note 430887.1  : 11g New Feature PL/SQL Function Result Cache


  • PL/SQL Fine-Grained Dependencies
    Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view.That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn’t make view invalid.
    Note 430725.1 : 11g New Feature Fine-Grained Dependencies


  • PL/SQL Continue Statement
    In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages.
    Note 469584.1 : 11g New Feature PLSQL CONTINUE Statement


  • PL/SQL Automatic Subprogram Inlining
    Every call to a procedure or function causes a slight, but measurable, performance overhead, which is especially noticeable when the subprogram is called within a loop. Avoiding procedures and functions is not an option, as it goes against the concept of modular programming, making programs bulky and difficult to manage. Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.
    The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not. When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement.


    The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
    SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
    You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE=’NATIVE’, because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE=’INTERPRETED’, the performance improvement is smaller.
    Note 433091.1 : 11g New Features SIMPLE_INTEGER Datatype

  • Named and Mixed Notation in PL/SQL Subprogram Invocations
    Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notation are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.
    Note 433070.1 : 11g New Feature Using Named and Mixed notation in PL/SQL subprograms


  • Enhancements to Regular Expression Built-in SQL Functions
    The regular expression built-in functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression built-in function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL.


  • Sequences in PL/SQL Expressions
    The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier for you and improve run-time performance and scalability. You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.
    Note 433068.1 : Sequences in PL/SQL Expressions
  • Dynamic SQL Enhancements
    Both native dynamic SQL and the DBMS_SQL package have been enhanced.Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by allowing it to be a CLOB.
    In the DBMS_SQL package:
    • All data types that native dynamic SQL supports are supported.
    • The DBMS_SQL.PARSE function accepts a CLOB argument, allowing dynamic SQL statements larger than 32 KB.
    • The new DBMS_SQL.TO_REFCURSOR Function enables you to switch from the DBMS_SQL package to native dynamic SQL.
    • The new DBMS_SQL.TO_CURSOR_NUMBER Function enables you to switch from native dynamic SQL to the DBMS_SQL package.
    Note 435251.1   : How Native Dynamic Sql Supports Statements > 32K Characters
    Note 567856.1 : New Feature in 11g , DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.


  • Compound Trigger
    A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.
    Note 430847.1 : 11g NEW PL/SQL COMPOUND Trigger