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.

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.

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.

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 11.5.10.2.

Also I am in need information for M&A (merger and acquisition) perspective.
Say Company A acquires Company B. Company A is on 11.5.10.2 and Company B is on 11.5.9
Now A wants to get B on the same instance (11.5.10.2) 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.

What does the Account Generator process do?The Account Generator process builds charge, budget, accrual, and variance accounts for each purchase order, release, and requisition distribution based on the distribution’s destination type. It is a synchronous Workflow process.

What are the Pre-requisites to use Account Generator?Before using the Account Generator you must:
– Define your Accounting flexfield structure for each set of books.
– Define flexfield segment values and validation rules.
– Set up Oracle Workflow.
– Decide whether you want to use the Account Generator processes as seeded in Oracle Purchasing, or you need to customize them to meet your accounting needs

What are the steps to generate the WFSTAT output for Account Generator?

A: 1. Set the following profiles:
– Account Generator: Run in Debug Mode=> Yes
– Account Generator: Purge Runtime Data=> No
PO: Set Debug Workflow On => Yes
2. Make sure that the concurrent program “Purge Workflow Obsolete Runtime Data” is NOT running.
3. Open Purchase Order/Requisitions form, go to the distributions window, enter necessary fields and click on charge account field to start generating the charge account. After the account generator has done building the account, or errors out, do the following from the toolbar (DO NOT exit the form or navigate to any other block or record, otherwise this information would be lost):

Then tab out. The Item key will appear in the third field.
4. Now save the purchase order/requisition. If you are not able to save, then clear the distribution record and navigate back to the shipment window and save the form. Saving the form is must, because a commit is required to save the workflow information in tables, for generating the wfstat output.
5. If step#3 could not give you an item key, then use the following query to identify the relevant item key:

For PO, use ‘POWFPOAG’ item type in above query, and for requisition, use ‘POWFRQAG’.
6. To generate the WFSTAT output,
Run the sql in $FND_TOP/sql/wfstat.sql with above item_type and item_key. Spool the output.
7. To get the wf debug information, run the following query:

How do you determine the WF Item Key value to run wfstatus.sql for Account Generator workflows?The item key is required in order to run wfstatus.sql for account generator. The wfstatus.sql gives important debug messages, including error messages for processes that are erroring out. This provides the same information that the Workflow Monitor does, if the Workflow Monitor is setup.
Steps to determine the WF Item Key value:
The item key can be found in the Workflow–>Status form in Projects (Querying by item type and dates) or, more generally, via SQL*Plus from the table WF_ITEMS. A sample script for getting the key is below:

Select item_key, begin_date, end_date
from wf_items
where item_type = ‘PAAPINVW’
and begin_date between ‘&low_start’ and ‘&high_start’;

If this does not definitively identify your item you can query on the various items in the Workflow status form to give you more information about the activities associated with them.

How do I save my workflow definition to the database?There are three options to use when saving your workflow definition to the database:

Option #1

If you have the file open in workflow builder select File -> SaveAs -> Database. Enter the apps username and password and provide the connection string (from the tnsnames.ora in the %ORACLE_HOME%\network\admin directory).Option #2

The WFLOAD utility is used for loading and extracting workflow definition files to and from the database.

NOTE: The item type parameter is used only for the DOWNLOAD mode.Option #3

Run the concurrent program, Workflow Definitions Loader. The system administrator will need to add this concurrent program to a request security group for the responsibility that you want to run this program from.

How do I trace errors in my Workflow processes?A: If a customized workflow process errors you should check the error stack (using the Workflow Monitor and/or wfstatus.sql script), if the procedure in error does NOT begin with WF_.. OR _… then you need to debug your own pl/sql procedures.
If standards in the Workflow Guide for exception handling have not been followed, you can still tell whether the problem is with your code, because Oracle Workflow traps external exceptions and reports these as

Activity Status: ERROR
Activity Result: #EXCEPTION

This is what would show up in the workflow monitor or any status report or form inquiry for the failing activity.

How do I customize the Account Generator Workflow process for my company?A: Customers are expected to customize the Account Generator process. You can do this by editing the existing process or creating your own process, but this MUST be within the seeded ‘Item Type’. You should NOT create a NEW ‘Item Type’ as this would NEVER be run by Account Generator.
If you create a new process, you must ensure it is “Runnable.”
You must then assign the appropriate process to the Accounting Flexfield structure in the Account Generator Process window in Oracle Applications (login as System Administrator, Application -> Flexfields -> Key -> Accounts and query up the required Accounting flexfield structure).

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.

Example ——— 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

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 dataVision 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. 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 addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME
or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

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 ?

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. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#i53930

PL/SQL SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types 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:

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

Follow me on Twitter

Shivmohan Purohit - A Learner by Heart and ERP Professional by Work. Contribution to success of my organization and people around. Learning new things everyday.
Belongs to family, being a son, brother, a husband and a father of a joyful son. Looking to Collaborate with people around the work.
Looking forward to connect and learn with you