f Get Oracle Apps Traning Here

This work is licensed under a Creative Commons -NonCommercial 2.5 License.

Wednesday, December 06, 2006

Value Sets In Oracle Apps

Many thanks to Rakesh Sreenivasa for contributing yet another article for Get Apps Training

Value Sets

Value Set is a collection of values. It validates the values entered by the user for a flex-field, report parameters or a concurrent.

The navigation path: Application :- Validation :- Sets

Value Set is associated with the following validations which holds the key to the value sets.

Types of Validations:

  1. None :- Validation is minimal.

  1. Independent :- The data input is based on previously defined list of values.

  1. Dependent :- The data input is checked against a defined set of values.

  1. Table :- The data input is checked against values in a table.

  1. Special :- The data input is checked against a flex-field.

  1. Pair :- The data input is checked against two flex-field specified range of values.

  1. Translatable independent :- The data input is checked against previously defined list of values.

  1. Translatable dependent :- The data input is checked against defined set of values.

Significance of $FLEX$

$FLEX$: enables to match the prior segment with either value set name or segment name.

Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter then

In the value set definition of v2 = value $FLEX$.v1


Changes You Should Never Make

You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flex-field combinations data:

. Independent to Dependent

. Dependent to Independent

. None to Dependent

. Dependent to Table

. Table to Dependent

. Translatable Independent to Translatable Dependent

. Translatable Dependent to Translatable Independent

. None to Translatable Dependent

. Translatable Dependent to Table

. Table to Translatable Dependent

Tuesday, December 05, 2006

Oracle Payroll Elements with Element Links

Please find an article that explains with examples why Elements are used in Oracle Payroll, and what they actually mean.


Monday, December 04, 2006

Enable Audit Trail in APPS - Oracle

This article explains how to create Audit Groups using System Administrator, to generate audit trail on any Oracle Apps Table


Profile Options Hierarchy in Oracle

A new article has been added to explain Hierarchy Profile Options in Oracle Apps.
This feature was delivered in version 11.5.9

It covers the relationship between Site Level Profile, Application Level Profile, Responsibility Level Profile, User Level Profile Options, Server Level Profile Options, User Level Profile Options and Organization Level Profile Options


Oracle Java Concurrent Programs

I have written an article on Oracle Java Concurrent Programs.

This article contains:-
1. Sample code for developing Interface with non-Oracle database using Java Concurrent Program
2. How do report messages in Output or the Log file of Java Concurrent Program
3. How to make Java Concurrent Program end with warning.
4. How to set CLASSPATH Of Java Concurrent Program in Oracle


Many Thanks,
Anil Passi

Sunday, November 26, 2006

Develop Oracle Reports concurrent program from Scratch

Please find an article about that explains how to develop Oracle Reports from Scratch.

To take you a step beyond, it also contains step by step approach for using XML Publisher with Oracle Reports [Concurrent Program]

XML Publisher based Concurrent Program Report

Monday, November 20, 2006

How to customize Oracle Apps Reports

A step by step approach for customization of Oracle Apps Reports has been listed in this link

Anil Passi

Thursday, November 16, 2006

Article on Key flexfields in Oracle Apps

The article for KeyFlexfields can be accessed via the below Link.

Key Flexfields Article

Anil Passi

Wednesday, November 15, 2006

Data migration of People records in HR

During next week, I will be covering working examples of various APIs in Oracle HRMS that can be used during data migration.

The first article in that series can be accessed from this link

Case insensitive query in Oracle Apps

This is response to a question posted by a comment in Article..Why Is it Called Apps
Click on the field in question, and click on Help/Diagnostic/Properties/Item

In the property field, select case insensitive query

If the value is TRUE, then you will know that Case Insensitive Query is possible on this field.

Monday, November 13, 2006

Apps Data Migration - Example Program

Please find the link below, that explains how to design and build programs for doing data migration in Oracle Applications.

Example for Data Migration Program

Thursday, November 02, 2006

Steps for your first pl/sql Concurrent Program in Oracle Apps

I think this topic is already covered partially in one of the previous training lesson[ for concurrent programs], but I would like to touch base on this again.

Lets revisit some basics first

Question: What are the ingredients for a concurrent program?
Answer: A concurrent executable and a program attached to that executable.

Question: Will executable for a pl/sql concurrent program be a database stored procedure?
Answer: Yes, but in addition to above you can also register a procedure within a package as an executable. However, you can't make a Function to become the executable for a stored procedure.

Question: Does this stored procedure need to have some specific parameters, in order to become an executable of a concurrent program?
Answer: Yes, such procedure must have at least two parameters
( errbuff out VARCHAR2, retcode out NUMBER)

Question: Can we add additional parameters to such pl/sql procedures that happen to be Conc Prog Executables?
Answer: Sure you can, but those parameters must be defined after the first two parameters. Effectively I mean first two parameters must always be errbuff and retcode. The sequence of the remaining parameters must match with the sequence in which parameters are registered in define concurrent program-parameters window.

Question: Can those parameters be validated or will these parameters be free text?
Answer: These parameters can be attached to a value set, hence this will avoid users passing free text values.

Question: What are the possible things that a concurrent pl/sql program can do?
Answer: Firstly your stored procedure would have been created in apps. This concurrent program will connect to "apps schema" from where access to all the tabes across every module will be available.
You can do the following:-
1. Insert records in tables(usually interface or temp tables)
2. Update and delete records
3. Initiate workflows
4. Display messages in the output file or the log file of the concurrent program.
5. Make this concurrent program complete with status Error or Warning or Normal.

Question: Please give me an example of a pl/sql concurrent program in Oracle apps in real life?
Answer: Lets say you have an external application which is integrated with apps. Assume that it is Siebel application where the new customer records are created. Siebel is assumingly hosted on a Oracle database from where it has database links to your Oracle apps database.
In this case, siebel inserts sales order records into your custom staging tables.
You can then develop a concurrent process which will do the following:--------
Loop through the records in that staging table
Check if the customer already exists in Oracle AR TCA
If customer already exists, thencall the api to update existing customer
If this is a new customer, then update existing TCA Customer/Party record

Question: Ok, how do I do the above?
Answer: Find the steps below for doing so

Step 1
Connect xxschema/password
Create table xx_stage_siebel_customers ( customer_Id integer, customer name varchar2(400));
Grant all on xx_stage_siebel_customers to apps ;

Step 2
Connect apps/apps_password
Create or replace synonym xx_stage_siebel_customers for xxschema.xx_stage_siebel_customers ;

Step 3 ( again in apps schema)
Create or replace procedure xx_synch_siebel_cust ( errbuff out varchar2, retcode out varchar2 ) is
n_ctr INTEGER := 0 ;
for p_rec in ( select * from
xx_synch_siebel_cust ) LOOP
Select count(*) into n_ctr from hz_parties where party_number = p_rec.customer_number;
If n_ctr=0 then
Hz_party_create(pass appropriate parameters here).
pass appropriate parameters here);
End if;
delete from
xx_synch_siebel_cust ;
End xx_synch_siebel_cust

Step 4
Create concurrent program executable ( for example of screenshot, visit link )

Step 5
Create concurrent program for that executable

Step 6
Add this concurrent program to request group

Now your program is ready to roll....

Advanced Oracle Apps Training Index

Dear All,

In response to a query that I received asking "Once all the training index topics have been covered, will this blog be stopped?"

Other readers might have had a similar question in mind.

Just so that you know, once these topics have been exhausted, we will then work upon an "Advanced Apps Training" index, one that will have articles influenced by real life problems faced during implementation and their resolutions.


Oracle Forms Basic Concepts

ARTICLE AUTHOR: Rakesh Sreenivasa

Oracle Forms Basic Concepts

The focus of the document is for consultants who are new to Oracle Forms and needs a kick-start on the concepts for better understanding of the subject.

Let’s start understanding the basic but important concepts in Forms.

Form :It is a developmental tool that is used for designing data entry and query screens. It is a front-end tool that runs in a Graphical User Interface (GUI).

GUI Concepts:

These concepts holds good for any user-interface.

To develop an effective GUI there are 4 basic stages:

  1. Define User Requirements
  2. Plan the User Interface
  3. Build the User Interface Elements (Create/Modify elements/functionality)
  4. User Feedback (Holds Key on the functionality and basis of the requirement)

Let’s move on to Forms Developer

There are 3 components involved in the application development

  1. Form Builder
  2. Form Compiler
  3. Form Runtime

Form builder consists of following tools to perform a specific task

  1. Object Navigator
  2. Layout Editor
  3. Property Palette
  4. PL/SQL Editor
  5. Menu Editor
  6. Object Library

Object Navigator: It’s a hierarchal representation of all objects.

Layout Editor: It provides a virtual representation of the application user interface.

Property Palette: Each object in the form module has a property associated to it. Developer can view/set properties for one/multiple object.

PL/SQL Editor: Programmatically to enhance the functionality and appearance of an application.

Menu Editor: Create menu as per applications requirement and can add various functionality to various menu options.

Object Library: Creation of objects on some default specification. Storing some standard objects that can be re-used in other forms/menu.

Blocks: Logically related interface items are grouped into functional units called Blocks.

Types of Block:

Data Block: It is associated with or bound, to a database table or view or a set of stored procedures.

Control Block: It is not associated with any database table but items that will control the behavior of the application.

Let’s move on to the next scheme of things…

Canvas: It is a surface inside a window on which we place the interface that end user interacts.

Types of Canvas:

  1. Stacked Canvas
  2. Content Canvas
  3. Horizontal Toolbar
  4. Vertical Toolbar
  5. Tab Canvas

Let’s discuss briefly about the triggers in this section, for more information you can look through the Forms Builder Help Topics.

Note: The hierarchy of Objects in a form is





Triggers: These are program units which enhance the functionality of a form/application.

The following triggers can be used to enhance the functionality of the form:

Block Processing Triggers: It fires in response to events related to record management in block.

e.g., When_Create_Record,When_Clear_Block,…

Interface Event Triggers: It fires in response to events that occur in form interface.

e.g., When_Button_Pressed,When_Checkbox_Changed,…

Master-Detail Triggers: It fires automatically when defined master-detail relationship between blocks. (Master-Detail relationship discussed further in the document)


Message Handling Triggers: It fires to issue appropriate error and information messages in response to runtime events.


Navigational Triggers: It fires in response to Navigational Items.

e.g., Pre_Form, Post_Form, When_New_Form_Instance, When_New_Block_Instance,..

Query Time Triggers: It fires before/after the operator/application executes a query.


Transactional Triggers: It fires in response to wide variety of events that occur as a form interacts with data source.


Validation Triggers: It fires when it validates data in an item/record.


Mouse Event Triggers: It fires for a mouse event.


Key Triggers: It has one to one relationship with specific Keys.

e.g.,Key F1,Key Enter,..

There are lot number triggers that can be used, please use as per the requirement with reference to Form Builder Help Topics.

Master- Detail Relationship : It is an association between two datablocks.One block is called Master Block and other Detail block. The relationship signifies that there is a primary key to foreign key relationship between the tables on the blocks associated.

Properties associated with blocks in a master-detail relationship.

Isolated : If you delete master records, associated detail records are not deleted from the database.

Non-Isolated: You cannot delete master records if the associated detail records exist in database.

Cascading: If you delete master records then automatically detail records will be automatically deleted from the database.

Windows : It is a container for all visual objects that make up a form, including canvases.

There are 2 types of Windows:

Document Window : It typically display the main canvases and work areas of the application where most data entry, and data retrieval is performed. It always remains within the application window frame.

Dialog Window: are free-floating, windows typically used for modal dialogs that require immediate user interaction.

Modality of the window depends on the functionality required i.e., Modal or Modeless.

Alert : It is a modal window that displays message to inform user about some application condition. E.g., STOP,CAUTION,NOTE,…

Invoking an alert : show_alert(alert_name)

Return number;

Record Group: It is an internal form builder structure that has column/row structure similar to database table. Static and Query based record groups can be used on the functionality of the form.

List of Values (LOV) : It is a pop-up window that provides end user selection list. LOV’s can be invoked programmatically or statically based on the record group. It can be positional based or automatic display.

The most important features of LOV are it provides auto-reduction and search features due to which user can locate specific values easily.

Let’s get to items on canvas which holds the key points.

Boilerplate Text Tool is used to create or edit a graphics text in the form. Graphics text is a static text in the form. E.g. Labels for items

Text Item Tool is used to create text item. It is an interface control that displays and allows editing of a text. It can be single or multi-line format.

Display Item tool are similar to text items but display items only store and displayed fetched or assigned values.

Buttons is a tool to execute commands or initiate buttons. E.g., OK ,CANCEL,..

Types : Text and Iconic Buttons

List Item is a list of text elements. A list item displays a fixed number of elements.

Types: Tlist,Pop List, Combo Box

Checkbox: It is a control that has 2 states i.e., checked or unchecked. It is used to indicate whether a certain condition is true or false.

Radio Button/Box : It is a logical set of options.

Editors: are used to edit item values in form. There are three editors that can be used at run time: Default editor, System Editor, User Named Editor

Property Class: Form builder provides a facility to create a named list of common properties and their values. This object is known as property class. Once you create a property class, you can base other objects on it. It is similar to the OOPS concept in programming languages.

Visual attribute : is a list of font, color and pattern properties and their values. This visual attribute can be attached to various objects to define object’s visual attributes.


I started creating this document to include the necessary concepts required for a consultant who wants to harness his skills on Oracle Forms. I have tried to include most of the necessary topics required before jumping to develop a form. For further details on Forms, you can always refer the Form Builder Help Topics.

ARTICLE AUTHOR: Rakesh Sreenivasa

Wednesday, November 01, 2006

New Custom Form in Apps -Article Added

Please find the training article on how to create a "Custom Form" from scratch in Oracle Apps.
I assume that you have followed the instructions in previous chapters regarding TEMPLATE.fmb.

Link for the article

Your feedback is precious, in helping us improve the quality of the training material. Based upon the feedback, this article will be amened to add further details where required.

Welcome Rakesh Sreenivasa

For the very first time, I received an email from a reader that wanted to contribute to this blog.

I would like to Welcome Rakesh as a contributor to this blog. Rakesh Sreenivasa will be publishing some documents on Oracle Apps Forms and Apps Reports, and who knows...may be more.

Hence I will add Rakesh as a contributor to this blog. Welcome on board Rakesh.

IMPORTANT NOTE:- I received another email where someone desired to use AudioVisual Workflow Training material of the blog in their training course. Surely I have denied them this request, because I do not want the knowledge on this blog/website to be sold. This information is free and should remain free. In case anyone ever discovers a professional training course using the material on this website in exchange of money, then please bring that to my attention by mailing me at anilpassi@gmail.com

Tuesday, October 31, 2006

Oracle Apps Forms Customization - Step by Step approach

A new article has been added that explains the steps required to customize forms in Oracle Applications.

This article can be linked from here

Hope you are enjoying the Training Articles on Oracle Applications.

Your feedback is precious in improving the quality of training information being published here.

Monday, October 30, 2006

Setting up your PC for Oracle Apps Development

Dear readers,

A new article has been added, that explains the various things you need to do on your PC to set it up for Oracle Apps Development.

The article can be accessed via this link.

Anil Passi

Sunday, October 29, 2006

Training Articles on Descriptive Flexfields added

Two articles on Descriptive Flexfields have been added accessed from following links:-

Basics of Descriptive Flexfields in Oracle Applications, with example

Step by Step approach for Context Sensitive Descriptive Flexfields in Oracle Apps

Thursday, October 26, 2006

Difference between Lookups and Value Sets

I hope you have read the previous articles on Value Sets and also on Lookups.

It is important for the learners to read things in Sequence. Hence you may decide to browse through the Training Index Page.

Difference 1
Value sets can be attached to parameters of a concurrent program, whereas Lookups can't.

Difference 2
Certain types of Lookups are maintainable by the users too, for example HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.

Difference 3
Value sets can contain values that are a result of an SQL Statement.
Hence it is possible to make Value Set list of values dynamic.
On the contrary, Lookup Codes are Static list of values.

Here comes the end of another simple article...

Anil Passi

Article on Lookups Training in Oracle Apps

Dear all,

The most simplest article thus far in this series has now been added. This article happens to be for Lookups in Oracle apps, and can be accessed by clicking here


Wednesday, October 25, 2006

New Oracle Apps Training Articles Added

The training index has now been updated with three further articles....

What is a Concurrent Program

What is concurrent Manager

What is a Value set

Friday, October 20, 2006

Oracle Forms Functions Menus and their relationship

Every screen we develop delivers a business functionality of sorts. Notice the word functionality.
Hence the word function or call it "Form Function" is related to Oracle Forms.

You all know that a form is nothing but a screen from where users can enter or retrieve(query) existing data.

Qns: In Oracle why do we have a form and then also a Form Function? Isn't just having a Screen/Form just good enough?
Ans: What if, for the same form, you wish to provide slightly different functionalities depending upon which menu is clicked to invoke that form??

Question: Are there any examples?
Answer: Lets assume the following

There are two responsibilities
Payables Manager responsibility
Payables clerk responsibility

There is one form for invoices
Payables invoice screen

Our requirement
If invoice screen is invoked from Payables Manager Responsibility, then at startup of the screen we want to provide a "Search Window", so that manager can search for the invoices to review

If the invoice screen is invoked from "Payables Clerk Responsibility", then at startup of the screen we want to provide "Invoice Data Entry window", so that clerk can begin entering the invoices.

The form will have following code within it in when-new-form-instance.
If :parameter.invoice_mode='MANAGER' then
Elsif :parameter.invoice_mode='CLERK' then
End if;

Question: From above example it appears that form function will be passing parameter named invoice_mode to the form?
Answer. Correct in this case.
So that we understand how things hang together.
Invoice form is attached to a Form Function, when defining the Form Function we pass parameters to it. Then this Form Function is attached to the Menu, and it is the Menu that gets attached to responsibility.

Question : Oh dear,are the form functions all about parameters?
Answer: Parameters is the main feature of Form Functions. But there are others too.
As you know from previous training lessons, each form will be located in a specific directory on Unix box. For example, Purchasing Form executables will be in $PO_TOP/foms/US
And account payables form executables will be located in $AP_TOP/forms/US.

Did you wonder how Oracle will know to look for a payables form executable in AP_TOP and not in PO_TOP??
Oracle will think like this...hmm user clicked on a menu, what is the form function for this menu...hmm it is InvoiceReview....ok...what is the application assigned to the for form function...oh...its ap(account payables) application...right....what is the base path for this application...rite..it is AP_TOP...lets then run the fmx file from ap_top/forms/us

Screenshots for examples
A. Go to application developer click on menu /application/form
Define you form...remember, oracle will expect an executable of .fmx for this name

Click on menu /application/function
Define your two functions, invoice review, invoice entry, pass them parameters as below.

Login to Oracle Apps , and select responsibility "Application Developer"
Navigate the menu "Form"
Define the Form as below
Now, lets define the function

Do you wish to know the name of form that has approx 600 functions defined in oracle apps? To find the answer, click on interview questions link.

Wednesday, October 18, 2006

Oracle Apps Training Testing Environments

In every implementation, there are always more than one environments in Oracle Apps.

The rule of thumb states that below environment are a must:-
1. Development environment, where developers usually have System Administrator responsibility and also the apps password. If not System Administrator then at least they should have access to "Application Developer" responsibility.

2. Testing environment
Developers usually do not and must not have apps password to this environment . This is where users sign-off customizations or even setup changes.

3. Production environment
This is where the business runs

Optional environments:-

CRP environment
Conference room pilot environment is where usually implementation team gets buying to their product offering from user community, during implementation. This environment is usually used for sign off during new implementations.

Patching environment
This is where all new Oracle Patches are sanity tested.

Support environment
This environment is exclusive to support staff. This environment is usually the most frequently cloned environment in Oracle Apps site that has gone live. Frequent cloning helps the Oracle Applications support staff to reproduce production issues

Migration Environment
This is where repeated data migration can take place before migration code gets frozen and ready for UAT.

User Training Environment
Some clients mix this with the CRP itself. But post-goLive of APPS, when rolling out new module, an environment like this comes handy.

You as a developer do not need to worry about these environments, as your focus will be on Development Environment only.

Now some notes:-
1. There can be more than one development environment at any implementation site, when different big modules are being implemented, all having different timelines. However, the changes to each environment are UAT'ed on a common Test environment.
2. The development to production cycle is.... Developer does development & unit testing on Development Environment, and their code gets promoted to User Testing environment. Following this, the changes get applied to Production environment.
3. The changes done to "development environment" must be scripted in all cases where possible. As a thumb rule, everything except for Functional Configuration can be scripted. You may be intersted in FNDLOAD Link

Oracle Apps Deployment Training

When you are a fresh new Oracle Apps developer, your fundamental question is...where to find the piece of code that you are being asked to customize?
Before I begin to explain you this, first some fundamentals.

1. You will most likely find that any implementation of oracle apps will involve at least two machines, i.e. Database tier and then at least one web tier.

2. Oracle thought very logically to decide which executable runs on database tier and which on web/forms tier(also known as mid-tier).
Any executable that has intense database operations is stored at database tier. To give you some examples.... sql files, oracle report java concurrent programs, sql*loader are all deployed in database tier of Oracle Applications.

3. Any executable that has intense UI operations is deployed at forms tier. Examples are Oracle Forms fmx files, jsp files, pll/plx etc.

Qns: The above sounds good in most cases, but what if you have to build a form that has intense database processing.
Ans: Well the form will still be deployed in the mid-tier, otherwise your form will never be run. However, for such forms, you must handle most of the database processing within pl/sql packages. The api's that you build in pl/sql must have well defined parameters.
I am saddened to see that some apps programmers write tons of sql code/DMLs inside the oracle forms triggers. In this era of high speed networks, such aproach may be justified to an extent, but what if some other developer desires to use validations developed for your form in other areas of apps.? Hence building pl/sql api's is the preferred approach.

Qns: Why do we have multiple middle tiers for one database.
Ans: Most implementations install multiple mid-tiers to distribute the user load. The user requests are first sent to a load balancer switch, this switch the decides which middle tier to use. Hence, if one server has 1000 user cpacity, then you can have 5mid tiers to handle 5000 concurrent users.

Qns: That's fine for the theory, but how do multiple mid-tiers impact my forms deployment?
Ans: You will need to deploy your forms file to each middle tier machine (unles shared APPL_TOP) has been implemented.

Qns: Where do I pick the fmb files delivered by oracle?
Ans: These are picked from $AU_TOP/forms/us

Qns: Where do I deploy the fmx file on mid tier, assuming a purchasing screen has been customized.
Ans: This will be deployed at $XXPO_TOP/forms/us
Basically by deployment I mean that fmx file will be copied to xxpo_top/forms/us
Have you read the previous article on applications?

Qns: Where do I deploy a pl/sql package?
Ans: All of the pl/sql packages are installed in apps schema.
This includes your custom packages and also oracle delivered packages.

Qns: Ehere do we create the database views?
Ans: Views will be created in apps schema too.

Qns: How do I generate fmx, should this be done on pc or on the mid tier?
Ans: This must always be done on the mid tier
For example use below steps
Step 1

Step 2
export FORMS60_PATH

Step 3
cd $XXPO_TOP/forms/US

Step 4 ----Below in one single line
f60gen module=XXPOSCREEN.fmb userid=apps/apps module_type=form batch=no compile_all=special

Qns:I have deployed the form at $XXPO_TOP but I can't run it,I get message can not find form
Ans: Firstly find out the application to which this form is registered against. In reality the forms are attached to form functions and it is the form function that is attached to an application

Qns: How can I generate CUSTOM.pll or any other Forms Library
----Below statements in one single line
cd $AU_TOP/resource
f60gen module=$AU_TOP/resource/CUSTOM.pll userid=apps/apps output_file=./CUSTOM.plx module_type=LIBRARY

f60gen module=$AU_TOP/resource/XX_POENT.pll userid=apps/apps output_file=./XX_POENT.plx module_type=LIBRARY

Qns: All the above sounds good, but how on earth do I connect to Mid Tier to generate forms
Ans: For Mid-Tier server, you will be provided with a Unix Username & Password. Before you start moaning, let me clarify that I assume that hosting o/s will be Unix. As soon as you logon to your mid-tier, your environment should automatically be setup based on scripts within .profile file. To know whether your environment has been setup on Unix box, do the below:
echo $FND_TOP
If the above returns blank, then it means you need to contact your DBAs to find out why environment variables are not being populated on your sign-on to Unix
...Ditto for DB Tier

Please let me know if you have any questions.

Anil Passi

Monday, October 16, 2006

Applications in Oracle Apps

In this training chapter I will explain what Application means, in Oracles context.

As a thumbrule, for one module there is one single application.

Uptill 8 years ago, it was mandatory for each table to be registered against an Application in Oracle Financials; but now such a relation no longer exist.

Oracle is a mixture of various applications like Payables, General Ledger, Payroll, Human Resources, manufacturing. You can call these modules, but officially these are called applications. Hence the name Oracle Applications(i think)

Is there a relation between application and table:-
Indirectly/logically there is, as the table is owned by a specific schema, and then a schema is related to an Application( though logically, as this relation is not enforced by the System).

What precautions do I take when creating a new table in oracle apps:-
Ans: Find out from DBA's the schema in which your custom tables are meant to be created. Create the desired table in custom schema. Note: If your custom table will support multi-org, then its name must finish with _all and also it must have a integer column named org_id

Qns: For an Oracle Apps developer, what else must be the consideration with respect to Application.
Ans: I will jump the training ship to explain this. In case you do not understand, then wait for the training lesson on concurrent programs.
a. Each program has an executable. For example reports have rdf, sql*plus has .sql file, forms has .fmx, Unix Shell Script has .prog(in apps) & D2k libraries have .plx
b. When you register an executable with Oracle Apps, you must then register this against an Application.
c. Each application is mapped to a specific path,say to a directory in unix box. For example, Applicaton XXPO (that holds PO Customizations) may map to /home/oracle/apps/appl/po
d. Say you have developed/customized a report for PO Module. Assume your report executable name is XXPOPRINT.rdf . If you register this executable with XXPO applicaton, then this rdf must be copied/ftp'ed to /home/oracle/apps/appl/po
e. When running this report in Oracle Apps, oracle will ask below series of questions to itself
I see that user is running XXPOPRINT.rdf, which applicaton is this report registered against? Oh well, it is XXPO application, then where is the directory location where I can expect to find this file? Oh ok, application definition of XXPO is mapped to /home/oracle/apps/appl/po (as per application definition), hence lets pick the rdf from /home/oracle/apps/appl/po/reports/US
Here lies the significance of applicaton in oracle apps.

Now some notes:-
1. Profile options can be defined at application level too.
2. All the Forms & Reports are attached to an Application.
3. Each application has a base path, which effectively is also the Environment variable on operating system.

The below image can be clicked to see how applications are defined in Oracle Apps. One needs to navigate to System Administrator responsibilotu. and select Menu /Application/Register.
As a developer, it is not your responsibility to define this, however I suggest that you understand this well.

Friday, October 13, 2006

ORG_ID and Multi Org In Oracle Apps

In this Oracle Apps training article, we will learn about org_id. I hope that you have read and understood the significance of profile options that we discussed in the earlier chapter.

Before I tell you what is org_id, lets do some questions & answers:-

Why do we need org_id
In any global company, there will be different company locations that are autonomous in their back office operations. For example, lets take the example of a gaming company named GameGold Inc that has operations in both UK and France.

Please note the following carefully:-
1. This company(GameGold Inc) has offices in both London and Paris
2. UK has different taxation rules that France, and hence different tax codes are defined for these countries.
3.GameGold Inc has implemented Oracle Apps in single instance(one common Oracle Apps database for both UK & France).
4. When "UK order entry" or "UK Payables" user logs into Oracle Apps, they do not wish to see tax codes for their French sister company. This is important because French tax codes are not applicable to UK business.
5. Given the single database instance of GameGold Inc, there is just one table that holds list of taxes. Lets assume that the name of the Oracle table is ap_tax_codes_all
6. Lets assume there are two records in this table.
Record 1 tax code -"FRVAT"
Record 2 tax code - "UKVAT"
7. Lets further assume that there are two responsibilities
Responsibility 1 - "French order entry".
Responsibility 2 - "UK order entry"
8. Now, users in France are assigned responsibility 1 - "French order entry"
9. Users in UK will be using responsibility named "UK order entry"
10. In the Order Entry screen, there is a field named Tax Code(or VAT Code).
11. To the French user, from the vat field in screen, in list of values UKVAT must not be visible.
12. Also, the "French order entry" user should only be able to select "FRVAT" in the tax field.
13. Similarly, UK order entry user, that uses responsibility "UK Order Entry", only "UKVAT" should be pickable.

How can all this be achieved, without any hard coding in the screen.
Well....the answer is org_id

ORG_ID/Multi-Org/Operating Unit are the terminologies that get used interchangeably.

In brief steps, first the setup required to support this....
The screenshots are at the bottom of the article

1. You will be defining two organizations in apps named "French operations" and "UK Operations". This can be done by using organization definition screen.
2. In Oracle Apps, an organization can be classified as HRMS Org, or Inventory Warehouse Org, or Business Group, Operating Unit Org or much more types. Remember, Organization type is just a mean of tagging a flag to an organization definition.
3. The two organizations we define here will be of type operating unit. I will be using words org_Id & operating unit interchangeably.
4. Lets say, uk org has an internal organization_I'd =101
And french org has orgid =102.

Qns: How will you establish a relation betwee uk responsibility and uk organization.
Ans: By setting profile option MO : Operating unit to a value of UK Org, against uk order entry responsibility

Qns: How will the system know that UKVAT belongs to uk org?
Ans: In VAT code entry screen(where Tax Codes will be entered), following insert will be done
Insert into ap_vat_codes_all values(:screenblock.vatfield, fnd_profile.value('org_id').
Alternately, use USERENV('CLIENT_INFO')

Next question, when displaying VAT Codes in LOV, will oracle do: select * from ap_vat_codes_all where org_id=fnd_profile.value('ORG_ID')?
Answer: almost yes.

Oracle will do the following
1. At the tme of inserting data into multi-org table, it will do insert into (vatcode,org_id) ....
2. Creates a view in apps as below
Create or replace view ap_vat_codes as Select * from ap_vat_codes_all where org_id = fnd_profile.value('ORG_ID')
3. In the lov, select * from ap_vat_codes ,

If the above doesn't make sense, then keep reading.

May be quick revesion is necessary:_
1. In multi org environment(like uk + france in one db), each Multi-Org Table will have a column named org_id. Tables like invoices are org sensitive, because UK has no purpose to see and modify french invoices. Hence a invoice table is a candidate for ORG_ID column.
By doing so, UK Responsibities will filter just UK Invoices. This is possible because in Apps, Invoice screens will use ap_invoices in their query and not AP_INVOICES_ALL.
2. Vendor Sites/Locations are partitined too, because UK will place its ordersfrom dell.co.uk whereas france will raise orders from dell.co.fr. These are called vendor sites in Oracle Terminology.
Any table that is mutli-org (has column named org_id), then such table name will end with _all
4. For each _all table , Oracle provides a correspondong view without _all. For examples create or replace view xx_invoices as select * from xx_invoices_all where org_id=fnd _profile.value('org_id').
5. At the time of inserting records in such table, org_id column will always be populated.
6. If you ever wish to report across all operating units, then select from _all table.
7. _all object in APPS will be a synonym to the corresponding _all table in actual schema. For example po_headers_all in apps schema is a synonym for po_headers_all in PO schema.
8. When you connect to SQL*Plus do the below
connect apps/apps@dbapps ;
--assuming 101 is French Org Id
execute dbms_application_info.set_client_info ( 101 );
select tax_code from ap_tax_codes ;
---Returns FRVAT

--assuming 102 is UKOrg Id
execute dbms_application_info.set_client_info ( 102 );
select tax_code from ap_tax_codes ;
---Returns UKVAT

Now some screenshots

Anil Passi

Wednesday, October 11, 2006

Profile Options Examples Screenshots

You have reached this page from Profile Options Training Article

Firstly, lets define the responsibility for Clerk, as we discussed in Article.

Next, lets define the sales manager responsibility, as we discussed in Article.

Lets define user JOHN, that is Clerk

And also, lets define user SMITH ( Sales manager )

Now, we need to define the profile option for discount, hence go to responsibility "Application Developer"

When you click on menu "Profile" above, you will then see below screen for defining profile option. Please note that the "Name" field is the short name of profile option, and it is this name used in API call to FND_PROFILE.value

Now, after having defined a profile option, its time to assign these to JOHN & SMITH.
Hence go to responsibility "System Administrator"

Click on Menu Profile/System, as below

The profile option assignment screen looks like below. Enter JOHN in USER, OEPASSI% in Profile, to select profile named "OEPASSI Maximum Discount Allowed"

Assign a value of 5 to the user.

Click on torch, to return to search screen as below

and this time we will assign value of 15 against user SMITH.

OK, what if we have too many clerks, we can also simply assign profile value to Responsibility of Clerk. Doing so, all the users that use this responsibility, will inherit profile option value against Responsibility.

Assign 5% max discount for Clerks

Now, lets select Sales Manager responsibility in profile screen

Search on this...as below......

Assign value 15% to Sales Manager responsibility.

That's it for setup.
Any questions? leave your question by click on link Leave Comment for this article

Anil Passi

Tuesday, October 10, 2006

What are Profile Options in Oracle Apps ?

Profile Options provide flexibility to Oracle Apps. They are a key component of Oracle Applications, hence these much be understood properly. I will be taking multiple examples here to explain what profile options mean. I will also try to explain by stepping into Oracle shoes "How will you design a program that is flexible", by using Profile Options.

Following that, if you still have questions regarding profile options, then leave a comment and I promise to respond. For the learners of Oracle Apps, understanding profile options is mandatory.

What is profile option?
The profile option acts like a Global Variable in Oracle.

Why does Oracle provide profile options?
These are provided to keep the application flexible. The business rules in various countries and various companies can be different. Hence the profile options are delivered by Oracle in such a manner to avoid hard-coding of logic, and to let the implementation team at site decide the values of those variables.

For screenshots of below listed examples in this article, please click this link

Enough definitions, give me some scenarios where profile options are used by Oracle....
1. There are profile options which can turn the debugging on, to generate debug messages. Say one of 1000 users reports a problem, and hence you wish to enable debugging against just that specific user. In this case you can “Turn On” the debugging profile option "again that specific user".
2. There are profile options that control which user can give discount to their customers at the time of data entry. You can set profile option "Discount Allowed" to a value of either Yes or No against each Order Entry user.
3. Lets assume an Organization has department D1 and D2. Managers of both the Departments have "HRMS Employee View" responsibility. But you do not want Manager of D2 to be able to see the list of Employees in Organization D1. Hence you can set a profile option against the username of each of these users. The value assigned to such profile option will be "Name of the Organization" for which they can see the employees. Of course, the SQL in screen that displays list of employees will filter off the data based on “logged in users profile option value”.

Let’s take an example. Let’s assume you are a developer in Oracle Corporation building a screen in ERP. Let us further assume that you are developing an Order Entry screen.
Assume that business requirements for your development work is:-
1. Screen should be flexible to ensure that different users of the screen can give different levels of discounts. For example, a clerk Order Entry User can give no more than 5% discount. But Sales Manager can enter an Order with 15% discount.
2. There should not be any hard-coding regarding the maximum permissible discount.
3. In the screen there will be a discount field.
4. When the discount value is entered in discount field, an error will be raised if user violates the maximum permissible discount.

Here is how Oracle will code this screen
1. They will define a profile option named "OEPASSI Maximum Discount Allowed".
2. The short name of this profile option is "OEPASSI_MAX_DISCOUNT"
2. In the when-validate-item of the discount field(assuming Oracle Forms), following code will be written
IF :oe_line_block.discount_value > fnd_profile.value('OEPASSI_MAX_DISCOUNT')
'You can’t give discount more than '
|| fnd_profile.value('OEPASSI_MAX_DISCOUNT') || '%' ) ;
raise form_trigger_failure ;-- I mean raise error after showing message

Here is how, the client implementing Oracle Order Entry will configure their system.
1. Navigate to System administration and click on system profile menu.
2. For Clerk User(JOHN), set value of profile "OEPASSI Maximum Discount Allowed" to 5
For Sales Manager User(SMITH), set value of profile "OEPASSI Maximum Discount Allowed" to 15

Question: This sounds good, but what if you have 500 Order Entry Clerks and 100 Order Entry Sales Managers? Do we have to assign profile option values to each 600 users?
Answer : Well, in this case, each Clerk will be assigned Responsibility named say “XX Order Entry Clerk Responsibility”
Each Sales Manager will be assigned Responsibility named say “XX Order Entry Sales Manager Responsibility”
In this case, you can assign a profile option value to both these responsibilities.
“XX Order Entry Clerk Responsibility” will have a value 5% assigned against it. However, “XX Order Entry Sales Manager Responsibility” will have a profile option value of 15% assigned.
In the when-validate-item of the discount field, following code will then be written
IF :oe_line_block.discount_value > fnd_profile.value('OEPASSI_MAX_DISCOUNT')
'You can’t give discount more than '
|| fnd_profile.value('OEPASSI_MAX_DISCOUNT') || '%' ) ;
raise form_trigger_failure ;-- I mean raise error after showing message

Please note that our coding style does not change even though the profile option is now being assigned against responsibility. The reason is that API fnd_profile.value will follow logic similar to below.
Does Profile option value exist against User?
--Yes: Use the profile option value defined against the user.
--No: Does Profile option value exist against Responsibility
-----Yes: Use the profile option value defined against the current responsibility in which user has logged into.
-----No: Use the profile option value defined against Site level.

For screenshots of examples in this article, please refer this link