Template Form in Oracle Applications

Template Form in Oracle Apps

This document provides an overview of the template form. This form derives its importance from the fact that this form is the starting point of all development involving forms. The document highlights the importance of Template.fmb in forms development and also provides a detailed explanation of the various components of the Template form.

Overview of the Template Form

The TEMPLATE form is the starting point for all development of new forms. The first step in creating a form for use in Oracle Applications is to copy the template form from $AU_TOP/forms/US, to a local directory and renaming it.

The Template form is unique because it contains some special libraries and triggers that render the application using the template form some standard characteristics. The components of the template form are:

· References to object groups: The template form contains platform–independent references to predefined standard object groups in the APPSTAND form. STANDARD_PC_AND_VA, STANDARD_TOOLBAR, and STANDARD_CALENDAR).

· Libraries: The template form contains platform–independent attachments of several libraries (including FNDSQF, APPCORE, and APPDAYPK).

· Special triggers: The template form contains several form–level triggers with required code. These are responsible for standard the behavior of the form.

· Predefined Program Units: The template form contains predefined program units that include a spec and a body for the package APP_CUSTOM, which contains default behavior for window opening and closing events.

· Applications Color Palette: The template form contains the application color palette. This gives the forms developed using the template form the look and feel of Oracle Applications

· Many referenced objects (from the object groups) that support the Calendar, the toolbar, alternative regions, and the menu. These objects include LOVs, blocks, parameters, and property classes, and so on.

· The TEMPLATE form contains sample objects that can be seen as examples for the expected layout cosmetics. These samples can be completely removed from the form later as they are only examples and are not required. The following objects are the samples and can be removed:



§ Canvas–view: BLOCKNAME

Hence, the template form comes along with many attachments, predefined program units, and defined visual attributes as well as examples that not only give the forms that are developed using the template.fmb a standard look and feel, but also make t easier to develop forms with consistent and standard functionality.

Libraries in the Template form

As stated above, the template form contains platform–independent attachments of several libraries. These libraries are used while running the form as a part of Oracle Applications. Hence, these libraries should not be changed or modified. There are three main libraries that are attached to the template form:


APPCORE: APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc. APPCORE contains the procedures to support this standard runtime behavior. APPCORE also contains various other utilities for exceptional handling, message levels, and so on.

APPDAYPK: The APPDAYPK library contains the packages that control the Oracle Applications Calendar feature. The calendar (or the date picker) is a utility that oracle apps provide to pick the dates for a date type field.

FNDSQF: FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing. It also has various other utilities for navigation, multicurrency, WHO, etc.

Other Libraries à The template form also contains a few other libraries that are not linked directly to the template form, but are linked to the three libraries listed above. Although, while using the form it makes no difference whether the library is linked directly to template or to another library that is linked to template. These are discussed below.

CUSTOM library: The CUSTOM library (CUSTOM.pll) is probably the most widely used and customized in the libraries attached to the template form. This library allows extension of Oracle Applications forms without modification of Oracle Applications code. Any form goes to the CUSTOM.pll whenever any event fires on the form. Code can be written in the CUSTOM.pll with the logic branching based on the form, block and trigger on which you want it to run.

You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.

GLOBE: The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms
without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can
take effect based on these events. The GLOBE library calls routines in
the JA, JE, and JL libraries.

VERT: The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library. Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.

JA: The JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.

JE: The JE library contains code specific to the EMEA (Europe/ MiddleEast/ Africa) region and is called by the GLOBE library.

JL: The JL library contains code specific to the Latin America region and is called by the GLOBE library.

Thanks Shivmohan Purohit

Overview of Concurrent Programs and Requests

Overview of Concurrent Programs and Requests

A concurrent program is an executable file that runs simultaneously with other concurrent programs and with online operations, fully utilizing your hardware capacity. Typically, a concurrent program is a long-running, data-intensive task, such as posting a journal or generating a report.

Request Groups and Request Sets

Reports and concurrent programs can be assembled into request groups and request sets.

o A request group is a collection of reports or concurrent programs. A System Administrator defines report groups in order to control user access to reports and concurrent programs. Only a System Administrator can create a request group.

o Request sets define run and print options, and possibly, parameter values, for a collection of reports or concurrent program. End users and System Administrators can define request sets. A System Administrator has request set privileges beyond those of an end user.

Standard Request Submission and Request Groups

Standard Request Submission is an Oracle Applications feature that allows you to select and run all your reports and other concurrent programs from a single, standard form. The standard submission form is called Submit Request, although it can be customized to display a different title.

o The reports and concurrent programs that may be selected from the Submit Requests form belong to a request security group, which is a request group assigned to a responsibility.

o The reports and concurrent programs that may be selected from a customized Submit Request form belong to a request group that uses a code.

As System Administrator you can limit the number of requests that may be active (status of Running) for an individual user. This ensures that a user cannot monopolize the request queue. For example, if a user with an Active Request Limit of 5 submits 20 requests, only 5 requests will be run at the same time. The remaining requests will be run when the number of active requests for the user drops below 5. Use the Profile Options window to set the Concurrent: Active Request Limit profile. To set a global limit for all users, set this option at the site level. You can then modify limits for individual users by setting this profile option at the User level.

do share you feedback and comments. thanks – shivmohan purohit

How to restrict functions and menus available to a user in menus

Hello Friends, Using this article, i wanted to give brief on how we can exclude some forms/ function as security in oracle applications. plz see i am trying to use two different way to get the same functionality.
There are two possibilities:
A – Restrict an existing menu:
1) Log in as System Administrator responsibility
2) Find the name of the appropriate menu      Navigator: Application -> Menu
3) Create a new responsibility       Navigator: Security -> Responsibility -> Define
4) Assign the appropriate menu to the new responsibility
5) Enter functions in the Function and Menu Exclusions section of the form Responsibilities
6) Save
7) Assign the new responsibility to the appropriate user

Example:  Restrict a user to be able to do only Assignments in the Asset Workbench.
1) Log in as the System Administrator responsibility
2) Find the name of the appropriate menu:
Description: %Asset%
2) Create a new responsibility: ASSET – assignments
3) Assign the menu FA_TRANSACTION to the responsibility ASSET – assignements
4) Enter all functions from the LOV in the Function and Menu Exclusions
section except of the Responsibilities form except “Asset Workbench” and
5) Save
6) Assign the responsibility ASSET – assignments to the appropriate user

B – Create a new menu:
1) log in as System Administrator responsibility
2) Create a new menu: Navigator: Application -> Menu      The new menu should contain only the functions and menus which should be accessible for the user.
3) Create a new responsibility       Navigator: Security -> Responsibility -> Define
4) Assign the appropriate menu to the new responsibility
5) Save
6) Assign the new responsibility to the appropriate user

Example:  Restrict a user to be able to do only Assignments in the Asset Workbench.
1) Log in with the System Administrator responsibility
2) Create the new menu menu:
Navigator: Application -> Menu
User Menu Name: FA Assignement
Description: FA Assignement
Seq: 1 Navigator Prompt: Assignment Function: Asset Workbench
Seq: 2 Navigator Prompt: Assignment Function: Assets:Assignements
3) Create a new responsibility: ASSET – assignments
4) Assign the menu FA_ASSIGN to the responsibility ASSET – assignements
5) Save
6) Assign the responsibility ASSET – assignments to the appropriate user

Please share your feedback and also if you think this article is helpful for you. or if this is difficult to understand and use, so i can improve on my future articles. thanks – Shivmohan Purohit

Oracle 10g Cost Based Optimizer

Hello Friends, here i like to give some brief on Cost based optimizer in Oracle, i am not covering Rule based optimizer as it is not much in use as well not recommended. plz share ur feedback or if u like to contribute more on this topic,

Oracle 10g Cost Based Optimizer

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. To figure out the optimal execution path for a statement, the optimizers consider the following:

  • The syntax you’ve specified for the statement
  • Any conditions that the data must satisfy (the WHERE clauses)
  • The database tables your statement will need to access
  • All possible indexes that can be used in retrieving data from the table
  • The Oracle RDBMS version
  • The current optimizer mode
  • SQL statement hints
  • All available object statistics (generated via the ANALYZE command)
  • The physical table location (distributed SQL)
  • INIT.ORA settings (parallel query, async I/O, etc.)

Understanding the Cost-Based Optimizer

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. Plz note The ANALYZE command and the DBMS_STATS functions collect statistics about tables, clusters, and indexes, and store those statistics in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

  • OPTIMIZER_MODE = CHOOSE has been specified in the INIT.ORA file, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been executed, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (or ALL_ROWS) command has been executed, and statistics exist for at least one table involved in the statement
  • A statement uses the FIRST_ROWS or ALL_ROWS hint (e.g., SELECT /*+ FIRST_ROWS */. . .)

ANALYZE command

The way that you analyze your tables can have a dramatic effect on your SQL performance. If your DBA forgets to analyze tables or indexes after a table re-build, the impact on performance can be devastating. If your DBA analyzes each weekend, a new threshold may be reached and Oracle may change its execution plan. The new plan will more often than not be an improvement, but will occasionally be worse.

If you do want to analyze frequently, use DBMS_STATS.EXPORT_SCHEMA_STATS to back up the existing statistics prior to re-analyzing. This gives you the ability to revert back to the previous statistics if things screw up. When you analyze, you can have Oracle look at all rows in a table (ANALYZE COMPUTE) or at a sampling of rows (ANALYZE ESTIMATE). Typically, When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

Following are some sample ANALYZE statements:



If you analyze a table by mistake, you can delete the statistics. For example:


Analyzing can take an excessive amount of time if you use the COMPUTE option on large objects. We find that on almost every occasion, ANALYZE ESTIMATE 5 PERCENT on a large table forces the optimizer make the same decision as ANALYZE COMPUTE.

Inner workings of the cost-based optimizer

The cost-based optimizer is flexible and can adapt to its environment. This adaptation is possible only once the necessary underlying object statistics have been refreshed (re-analyzed). What is constant is the method by which the cost-based optimizer calculates each possible execution plan and evaluates its cost (efficiency).

The cost-based optimizer’s functionality can be (loosely) broken into the following steps:

1.       Parse the SQL (check syntax, object privileges, etc.).

2.       Generate a list of all potential execution plans.

3.       Calculate (estimate) the cost of each execution plan using all available object statistics.

4.       Select the execution plan with the lowest cost.

The cost-based optimizer will be used only if at least one table within a SQL statement has statistics (table statistics for unanalyzed tables are estimated). If no statistics are available for any table involved in the SQL, the RDBMS will resort to the rule-based optimizer, unless the cost-based optimizer is forced via statement-level HINTS or by an optimizer goal of ALL_ROWS or FIRST_ROWS.

To understand how the cost-based optimizer works and, ultimately, how to exploit it, we need to understand how it thinks.

Primary key and/or UNIQUE index equality —A UNIQUE index’s selectivity is recognized as 100%. No other indexed access method is more precise. For this reason, a unique index is always used when available.

Non-UNIQUE index equality —For non-UNIQUE indexes, index selectivity is calculated. The cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option of the ANALYZE. That option will make the cost-based optimizer aware of how the data in the indexed columns is skewed.

Range evaluation —For index range execution plans, selectivity is evaluated. This evaluation is based on a column’s most recent high-value and low-value statistics. Again, the cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option when analyzing the table.

Range evaluation over bind variables—For index range execution plans, selectivity is guessed. Prior to Oracle9i, because bind variable values are not available at parse time (values are passed to the cursor after the execution plan has been decided), the optimizer cannot make decisions based on bind variable values. The optimizer assumes a rule of thumb of 25% selectivity for unbounded bind variable ranges (e.g., WHERE dept_no = :b1) and 50% selectivity for bounded ranges (WHERE dept_no > :b1 AND dept_no < :b2). Beginning with Oracle9i, the cost-based optimizer obtains bind variable values prior to determining an execution plan.

System resource usage —By default, the cost-based optimizer assumes that you are the only person accessing the database. Oracle9i gives you the ability to store information about system resource usage, and can make much better informed decisions based on workload (read up on the DBMS_STATS.GATHER_SYSTEM_STATS package).

Current statistics are important —The cost-based optimizer can make poor execution plan choices when a table has been analyzed but its indexes have not been, or when indexes have been analyzed but not the tables.

Distinction between configuration, personalization, and extensibility.

Distinction between configuration, personalization, and extensibility.

Configuration provides setup and administrative choices using the native features of the product. Some configuration examples include:

  • Profile Options
  • User-defined fields (Flexfield)
  • Function Security Setup
  • Data Security Setup
  • and a lot more….

Personalization enables you to declaratively tailor the UI look-and-feel, layout or visibility of page content to suit a business need or a user preference. Some personalization examples include:

  • Tailor the order in which table columns are displayed.
  • Tailor a query result.
  • Tailor the color scheme of the UI.
  • Folder Forms
  • Forms Personalization
  • Oracle Application Framework (OAF)

Extensibility is about extending the functionality of an application beyond what can be done through personalization. Some extensibility examples include:

  • Add new functional flows
  • Extend or override existing business logic
  • Using Oracle Forms Developer, Oracle JDeveloper and Oracle Workflow

Oracle Applications – Interview Questions (All Modules)

Oracle Applications – Interview Questions (All Modules)

Question: What are the key benefits of forms personalization over custom.pll?
Answer: Multiple users can develop forms personalization at any given point in time.
It is fairly easy to enable and disable forms personalization.
–>A programmer is not required to do simple things such as hide/disable fields or buttons.
–>Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
–>Can’t create record group queries, hence can’t implement LOV Query changes.
–>Can’t make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

Question: How can you import invoices into Oracle Receivables?

You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice for Receivables Invoice Import.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?

Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option. This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?

Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?

Answer: “Gather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled,

Question: How do you know if a specific Oracle patch has been applied in apps to your environment?

Answer: Use table ad_bugs, in which column bug_number is the patch number.

SELECT bug_number ,to_char(creation_date, ‘DD-MON-YYYY HH24:MI:SS’) dated

FROM apps.ad_bugs

WHERE bug_number = TRIM(‘&bug_number’) ;

Question: How to make concurrent program end with warning?

Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter. For a Java Concurrent program, use the code similar to below ReqCompletion lRC; //get handle on request completion object for reporting status IRC = pCpContext.getReqCompletion(); lRC.setCompletion(ReqCompletion.WARNING, “WARNING”);

Question: Which table is used to provide drill down from Oracle GL into sub-ledger?


Question: You have just created two concurrent programs namely “XX PO Prog1” & “XX PO Prog2”. Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?


a) Define a request group, lets say with name “XX_PO_PROGS”

b) Add these two concurrent programs to the request group “XX_PO_PROGS”

c) Define a new Form Function that is attached to Form “Run Reports”

d) In the parameter field of Form Function screen, enter REQUEST_GROUP_CODE=”XX_PO_PROGS” REQUEST_GROUP_APPL_SHORT_NAME=”XXPO” TITLE=”XXPO:XX_PO_PROGS” e) Attach this form function to the desired menu.

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?

Answer: “Run Reports”. And why not, the Form Function for this screen has a parameter to which we pass name of the “Request Group”, hence securing the list of Concurrent Programs that are visible in “Run Request” Form. Just so that you know, there are over 600 form functions for “Run Reports”

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions from one environment to another without Keying? Will you use FNDLOAD?

Answer: FNDLOAD can not be used in the scenario. You can use migrator available in “Oracle iSetup” Responsibility

Question: How can an end-user be given control to run a script developed by a developer, given that an end user will never have access to apps password (and rightly so)?

Answer: This script can be attached to a Concurrent Program via a concurrent program executable. The user will then be given access to this Concurrent Program.

Question: But how will the end user or Oracle Apps make this script run every 10hours daily?

Answer: A concurrent program can be scheduled to run at desired intervals. The schedule is defined at the time of submission.

Question: What are the basic steps when defining a concurrent program?

Answer: Broadly speaking there are three steps when developing a concurrent program in Oracle Apps Step 1. Make Oracle Apps identify the executable Step 2. Provide a handle to the executable by means of defining a concurrent program Step 3. Make this concurrent program accesible to selected users via their responsibility.

thanks - shivmohan purohit

Oracle Applications – Technical Interview Questions

Hello Friends, Upon requests on some of my friends, i am posting few more generic oracle applications’ questions. hope this will give you overview of generic features. do share your feedback if these are good n useful.

what is a flexfield qualifier ?

Oracle Applications products use flexfield qualifiers to identify certain segments used for specific purposes. eg: segment named ‘Account’ is qualified as ‘Natural Account’so that it identifies Accounts, similarly a segment named ‘Company’ can be qualified as ‘Balancing Segment’, i.e, for these segment values B/S can be brought out in Accounting Flexfield in GL.

A flexfield is made of segments and oracle identifies if particular segment in the accounting flexfield is an account segment or a company segment, based on some predefined values called as flexfield qualifiers which are assigned to the segments to look meaningful for defining the accounting flexfield. Eg: Account segment uses Natural Account segment and the company segment uses Balancing segment are mandatory for defining the accounting flexfield. Other values are Cost center segment, Inter company segment, secondary tracking segment

What is the difference between configuration, conversion, and customization?

Conversion ,customization and configuration are part of an oracle implementation project they come in this order

1. configuration/set up

2. Customization

3. Conversion

Configuration is setting up customer specific requirement which generally exist in oracle apps customization is filling the gap between oracle apps and Customer requirement by developing some extension and solution design

Customization is relatively different thing, something that is not a part of oracle apps core product but the client requirement demands it we go for Customization. If the functionality demanded by the client exists in the application, it is to be configured according to his needs by taking some data, this is called configuration

Conversion is structural change of customer data in a form which is compatible with oracle database best of luck. Conversion is the process of mapping the tables from a legacy system to apps system. Sometimes conversions can be from one version to other

Where we can check the status of PO.

In po_headers_all authorization_status coloumn is their we can find through this coloumn

What is back order in OM

There are few reasons that a scheduled order could be Backordered during pick release process.

1. In-sufficient onhand

2. Order might be put as ‘Pick release hold’

3. If it’s a lot controlled item being involved then some of the factors will be checking at picking rule setup (like Lot- expiration date, etc) and tured to ‘Backordered’ incase the criteria does not meet.

Where we find the status of order information.

Order header status is in oe_order_headers_all table reference with flow_status_code column and order line status would be oe_order_lines_all table reference with flow_status_code column.

What are the tables of auto invoice?


For report i have to parameters those are from_date and to_date ,so to_date should be greater when compare to from_date ,if we are giving to_date is less then it must shows some error how we will make it.

While defining Parameters you can set range option(in concurrent prog parameters window). Select low for from_Date and high for to_Date.Then it will not allow for from_date > to_date.

How to find the custom directory in front end.

From Application Developer responsibility, navigate to Application –> Register. Query for the custom application name. The value in the field Basepath, is the OS system variable that stores the actual directory info.

What are the execution methods in oracle apps?

1. Host

2. Immediate

3. Java Stored Procedure

4. Java Concurrent Program

5. Multi Language Function

6. Oracle Reports

7. Request Set Stage Function

8. Pl/Sql Stored Procedure

9. Spawned

10. Sql*Lodar

11. Sql*Plus

12. Perl Concurrent Program

What are APIs

API stands for application program interface. Oracle has its own api facility to transfer data from a external source to oracle base table so api is an standard oracle tool to transfer data to oracle database. It is a very simple method and a person with no technical background also can use api. Example-uploading journal and budget data through API.

Thanks – plz share questions if you have more in this area. also share your comment/feedback. Shivmohan Purohit