Profile Option in Oracle Apps

Profile Option in Oracle Apps

Profile Options provide flexibility to Oracle Apps. They are a key component of Oracle Applications.The profile option acts like a Global Variable in Oracle.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.

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 "XX Maximum Discount Allowed".
2. The short name of this profile option is "XX_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('XX_MAX_DISCOUNT')
THEN
message(
'You can’t give discount more than '
|| fnd_profile.value('XX_MAX_DISCOUNT') || '%' ) ;
raise form_trigger_failure ;-- I mean raise error after showing message
END IF ;

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 "XX Maximum Discount Allowed" to 5
For Sales Manager User(SMITH), set value of profile "XX Maximum Discount Allowed" to 15

Oracle apps tutorials Oracle apps Technical Tutorials Oracle apps BR100 MD50 MD70 profile options oracle apps material

Oracle Apps Concurrent Program Processing

This document talks about the concurrent processing in oracle applications. It will give the step-by-step procedure of creating concurrent programs in oracle applications. All types of concurrent programs are described in detail. Concurrent programs can be scheduled to run or manually run in oracle applications. In Oracle Applications, concurrent processing simultaneously executes programs running in the background with online operations to fully utilize your hardware capacity, hence the name ‘Concurrent Processing’. You can write a program (called a ”concurrent program”) that runs as a concurrent process. Typically, you create concurrent programs for long–running, data–intensive tasks, such as posting a journal or generating a report.

Concurrent Programs

A concurrent program is an instance of an execution file, along with parameter definitions and incompatibilities. Concurrent programs use concurrent program executables to locate the correct execution file. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilities.

Concurrent Program Executable

A concurrent program executable links an execution file or and the method used to execute it with a defined concurrent program. Under Concurrent Processing, an execution method may be a program written in a standard language, a reporting tool, or an operating system language.
An execution method can be a PL/SQL Stored Procedure, an Oracle Tool such as Oracle Reports or SQL*Plus, a spawned process, or an operating system host language.

Concurrent Program Execution File

A concurrent program execution file is an operating system file or database stored procedure which contains your application logic and can be executed by either invoking it directly on the command line or by invoking a program which acts upon it. For example, you run a Pro*C program by invoking it on the command line. You run a SQL script by running SQL*Plus and passing the name of the SQL script without the .sql extension.

Steps to Create a Concurrent Pro

1. Executable :

The first step in creating a concurrent program in oracle applications is to create the concurrent program executable. To do this, navigate to Application Developer responsibility after you login to Oracle Applications. To login, you can take the help of the ‘Topic11 - Oracle Applications Login Document’.

To open the Executable form follow the below mentioned navigation path.

Application Developer à Concurrent à Executable.

Figure 1. Executable Form Navigation
You will see the form as shown in figure 2 below.

Figure 2. Executable Form

Field Description

i) Executable - Enter executable name here. Enter any user friendly name.
ii) Short Name - Enter short name for your executable. This is used for mapping the executable with the concurrent program. Usually executable name are of 8 characters.

iii) Application - Give the application to which the executable belongs to. E.g. you want some program to run from oracle payables then account payables should be entered as application name.

iv) Description - Give the brief description of the executable in this field.

v) Execution Method - Here you need to choose the appropriate execution method for your executable. Following are the possible execution methods which are commonly used in oracle applications.

·  Oracle Reports – used for the RDF reports

·  Host – used for shell scripts, basically the language of the host operating system

·  PL/SQL Stored procedure – used to run the stored procedure through oracle applications

·  SQL*Loader – used to run the sql loader programs

·  SQL*Plus - used to run the anonymous PL/SQL blocks. It will get executed in the same fashion as you are running on SQL Plus.

·  Java Stored Procedure – The execution file is a Java stored procedure.

·  Java Concurrent Program – Used for program written in Java.

·  Spawned – used for c or pro*c Program. Mainly used by standard oracle interfaces.

·  Perl Concurrent Program – used for programs written in CGI Perl.

·  Request Set Stage Function – PL/SQL stored function that can be used to calculate the completion statuses of request set stages.

·  Immediate – execution file is a program written to run as subroutine of the concurrent manager. Oracle doesn’t recommend use of this executable type.

·  Multi-Language function – execution file is an MLS function that supports running concurrent program in multiple languages.

PL/SQL Stored Procedure, Oracle Reports, Host, SQL*loader, SQL*Plus are the most commonly used executable types.
vi) Executable File name – This should contain the name of the executable file. In case of PL/SQL Stored procedure or Java Stored Procedure it should be the fully defined name of the stored procedure.

vii) Subroutine Name – This field is only used when executable type is spawned or immediate.

viii) Execution File Path -

ix) The Stage Function Parameters button opens a window that allows you to enter parameters for the Request Set Stage Function. This button is only enabled when you select Request Set Stage Function as your Execution Method.

2. Concurrent Program :

Once you have defined the executable, the second step is to define the concurrent program. To define a concurrent program, you need to open the form using the navigation path below.
Navigation – Application Developer -> Concurrent ->Program

Figure 4. Concurrent Program Form Navigation
The form would look like as shown in the figure 4 below.

Figure 4. Concurrent Program Form

Field Description

i) Program – Give user friendly name for your concurrent program. This name will be displayed in Requests submission screen while submitting the requests.

ii) Short Name – Give short name for concurrent program. This is used within the database tables in oracle applications. It’s a common practice to have the Short name same for the executable and the concurrent program.

iii) Application – Give the application to which the concurrent program belongs to. E.g you want some program to run from oracle payables then account payables should be entered as application name.

iv) Description - Give the brief description of the concurrent program.
v) Executable - Enter the short name of the executable (Defined in Executables Screen) you want to attach to this concurrent program.

vi) Method - will be defaulted once you enter executable short name

vii) Options - will be defaulted once you enter executable short name
Normally default values are given for the other fields. Following is the significance of these fields.
viii) Request Type – Concurrent program can be associated to a predefined request type so that only few concurrent managers can run the program.
ix) Incrementor – To be used by Oracle only.
x) MLS Function - This feature allows the program to be submitted once by the user but runs it in the multiple languages.

xi) Use In SRS – Only when this check box is checked the Concurrent Program would be available in Standard Request Submission (SRS) through the request group.

xii) Allow Disable Value – This will allow the disabled values in the value sets to be used while entering the values of the parameters in the Concurrent Program.
xiii) Run Alone – Indicates that program is incompatible with all other concurrent programs and should be run alone.

xiv) Enable Trace – This will enable the SQL trace for the concurrent program and will generate the trace file when concurrent program is run. Only used in development environments to check the performance of the concurrent program.
xv) Restart on system Failure – This option is used to indicate that concurrent program should automatically be started when concurrent manager is restored after the system failure.

xvi) NLS Compliant – This box is checked if the program allows for a user to submit request of the program that will reflect a language and territory that are different from the language and territory that the users are operating in.
xvii) Output Format – Format in which output should be printed. Possible format values are

·  HTML

·  PDF

·  TEXT

·  PS (Post Script)

·  PCL(HP’s Printer Control Language)

xviii) SAVE – Check to indicate that output should be automatically saved in an operating system file.

xix) PRINT – Whether you want the output to be sent to printer for printing.

xx) Column / Rows – Column and Row length of the concurrent program output. Oracle Applications uses this information to decide the print style.

xxi) Style Required – Print Style

xxii) Printer – A particular printer on which output should be sent.

Save the data that you have entered using Ctrl+S or File à Save.

3. Concurrent Program Parameters :

The 3^rd step in defining concurrent program is to define the parameters. To do this, click on the Parameters button as shown at the bottom the form in figure 4. The following screen (figure 5) is displayed when you click on parameters button. This screen is used to define the parameters in the concurrent program.

Figure 5. Parameter Window

Field Description

i) Program – will be defaulted from Concurrent Program

ii) Application – will be defaulted from Concurrent Program

iii) Conflicts Domain - Enter the parameter which will hold the value of the conflict domain of the program.

iv) Security Group - This field is for HRMS security only.

v) Seq – Enter sequence number for the parameter

vi) Parameter – name of the parameter, will be displayed in parameter entry screen

vii) Description – description about the usage of the parameter

viii) Enabled – check box to enable or disable the parameter. Disable a parameter when you don’t want to use it.

ix) Value set – enter the name of the value set which you want to use to validate the value enter in the parameter field.

x) Description – will be defaulted from value set definition

xi) Default Type – choose the default type for the default value of the parameter. Possible default types are

·  Constant : The default value can be any literal value.

·  Profile : The default value is the current value in the user profile option defined in the Default Value field. Use the profile option name, not the end–user name. You do not need to include $PROFILE$.

·  SQL Statement : The default value is determined by the SQL statement you defined in the Default Value field.

·  Segment : The default value is the value entered in a prior segment of the same parameter window.

xii) Display size – Enter the field length in characters for this parameter. The user sees and fills in the field in the Parameters window of the Submit Requests window.

xiii) Token - For a parameter in an Oracle Reports program, the keyword or parameter appears here. The value is case insensitive. For other types of programs, you can skip this field.

4. Incompatible Programs Window :

To navigate to this form, click on the Incompatibilities button on the bottom of the form shown in figure 4. This window is used for defining the incompatibilities of the concurrent program. Identify concurrent programs that should not run with your concurrent program as they might interfere with its execution.

Figure 6. Incompatibilities Form

Field Description

i) Program – Defaulted from Concurrent Programs Window

ii) Application – Defaulted from Concurrent Programs Window
iii) Application – Application of the concurrent program which is incompatible to the defined concurrent program.

iv) Name – Name of the concurrent program which is incompatible to the defined concurrent program.

v) Scope – Used to identify if concurrent program is incompatible if the program or also with its child requests.
vi) Type - Enter Domain or Global. If you choose Domain, the incompatibility is resolved at a domain-specific level. If you choose Global, then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.

5. Session Control :

To navigate to this form, click on the Session Control button on the bottom of the form shown in figure 4. This is a new functionality given in 11.5.10 to control the database session while executing concurrent program using following three parameters. The form would look like as shown in figure 7.

Figure 7. Session Control Form

Field Description

i) Consumer Group – resource consumer group of the concurrent program can be specified. A resource consumer group defines a set of users who have similar resource usage requirements. An overall resource plan specifies how resources are distributed among the different resource consumer groups. Resource consumer groups and resource plans provide a method for specifying how to partition processing resources among different users.
ii) Rollback Segment – Rollback segment specified here would be used instead of the default rollback segment. If you specify a rollback segment here, your concurrent program must use the APIs FND_CONCURRENT.AF_COMMIT and FND_CONCURRENT.AF_ROLLBACK to use the specified rollback segment.
iii) Optimizer mode - Optionally specify an optimizer mode. You can choose ALL_ROWS, FIRST_ROWS, Rules, or Choose. You would specify an optimizer mode only for a custom program that may not perform well with the default cost-based optimizer (CBO) and needs tuning. You can use a different optimizer mode until your program is tuned for CBO.

6. Copy to Window :

To navigate to this form, click on the Copy To button on the bottom of the form shown in figure 4. This window can be used to copy the definition of the concurrent program. Give the program name to copy the definition.

Figure 8. Copy To Form

Field Description

i) Program – Enter the program name to be copied

ii) Short Name – Short Name of the program to be copied

iii) Application – Application name of the concurrent program to be copied

iv) Include Incompatible programs - Check this box if you want incompatibilities to be copied

v) Include Parameters – Check this box if you want parameters to be copied.

Oracle apps tutorials Oracle apps Technical Tutorials Oracle apps realtime  BR100 MD50 MD70 oracle apps queries oracle apps

Oracle Applications 11i File system

                      

Oracle Applications 11i File system

An Oracle Applications Release 11i system utilizes components from many Oracle products. These product files are stored below a number of key top-level directories on the database and application server machines. Depending on how you chose to install Applications, these product directories may be located on a single machine (the simplest case) or on multiple machines (the most common type of deployment). Operating system environment settings indicate the location of the various files in the file systems of the database and application server machines.
This topic discusses the association between these environment settings and the corresponding files and directories.
Note: is the name of your system determined through Rapid Install at the time of installation. For example, PROD.

·  The DATA or DATA_TOP directory is located on the database server machine, and contains the system tablespaces, redo log files, data tablespaces, index tablespaces, and database files.

·  The DB directory is located on the database server machine, and contains the ORACLE_HOME for the Oracle9i database.

·  The APPL or APPL_TOP directory contains the product directories and files for Oracle Applications.

·  The ORA directory contains the ORACLE_HOMEs for the Applications technology stack components.

·  The COMN or COMN_TOP (or COMMON_TOP) directory contains directories and files used across products.

Figure 2. Applications Directory Structure

The DATA Directory

The DATA file system contains the data (.dbf) files of the Oracle database. Rapid Install installs the system, data, and index files in directories below several mount points on the database server. You can specify the directory names of the mount points on the database server during installation.

DB and ORA Directories

Oracle Applications supports employing an Applications database of one version, while linking Applications programs using the tools from a second or third version of the database. This multiple ORACLE_HOMEs configuration allows new features of the database to be supported, while maintaining compatibility with earlier releases. Release 11i utilizes three ORACLE_HOMEs:

1. The 9.2.0 ORACLE_HOME (Applications database home) is located in the DB directory. It contains the files needed for running and maintaining the Oracle Applications database.

2. The 8.0.6 directory contains the ORACLE_HOME for the Developer 6i products (Forms, Reports, and Graphics). The product libraries in the 8.0.6 ORACLE_HOME are used to relink Oracle Applications executables.

3. The iAS directory, also under the ORA, contains the ORACLE_HOME for Oracle9i Application Server.

The APPL Directory

Oracle Applications files are stored in the APPL directory, which is known as the APPL_TOP directory.

The APPL_TOP directory contains:

1. The core technology files and directories.

2. The product files and directories (for all products).

3. The main applications environment file, called .env on UNIX, and .cmdon Windows.

4. The consolidated environment file, called APPS.env on UNIX, and APPS.cmd on Windows.

Product Directories

Each product has its own subdirectory under APPL_TOP. The subdirectories are named in accordance with the product’s standard abbreviation, such as gl for Oracle General Ledger. Within each product directory is a subdirectory that is named using the base Oracle Applications release number, such as 11.5.0. This directory contains the various subdirectories for the product files.

Figure 3. APPL_TOP Sub-directory structure

The COMN Directory

The COMN or COMMON_TOP directory contains files used by many different Oracle Applications products, and which may also be used with third-party products.

Figure 4. Common Top directory structure

The admin Directory

The admin directory, under the COMMON_TOP directory, is the default location for the concurrent manager log and output directories. When the concurrent managers run

Oracle Applications reports, they write the log files and temporary files to the log subdirectory of the admin directory, and the output files to the out subdirectory of the admin directory.

The html Directory

The OA_HTML environment setting points to the html directory. The Oracle Applications HTML-based sign-on screen and Oracle HTML-based Applications HTML files are installed here. The html directory also contains other files used by the HTML-based products, such as JavaServer Page (JSP) files, Java scripts, XML files, and style sheets. Rapid Install and the AD utilities copy the HTML-based product files from each _TOP directory to subdirectories in the html directory.

The java Directory

The JAVA_TOP environment setting points to the java directory. Rapid Install installs all

Oracle Applications JAR files in the Oracle namespace of this JAVA_TOP directory. The java directory also holds third-party Java files used by Oracle Applications, as well as other zip files.

The portal Directory

The portal directory contains the Rapid Install Portal files. The Rapid Install Portal is a web page that provides access to post-install tasks that may be necessary for your installation, plus server administration scripts, installation documentation, and online help. Using a browser, you can view the Rapid Install Portal after you run Rapid Install.

The temp Directory

The temp directory is used for caching by some products such as Oracle Reports.

The util Directory

The util directory contains the third-party utilities licensed to ship with Oracle Applications. These include, for example, the Java Runtime Environment (JRE), Java Development Kit (JDK), and the Zip utility.

The scripts Directory

The scripts directory contains application tier control scripts such as adstrtal.sh and adstpall.sh, which are located in the subdirectory.

Oracle apps tutorials Oracle apps Technical Tutorials Oracle apps realtime  BR100 MD50 MD70 oracle apps queries oracle apps

Getting started with Oracle Applications

This document will explains about Oracle Apps login, Choosing Responsibility, Choosing Function / Opening Form, Switch Responsibility, Query Form and Keyboard Shortcuts. This chapter would tell a user about the basics of login to the Oracle Applications instance. It would also demonstrate the basics of working with Oracle Applications. To understand the complete functionality of a particular responsibility please refers to the respective User Guide.

Oracle Applications Login

Before you connect to Oracle Applications you must know the URL of the Oracle Applications server you want to connect to. Clicking on the URL would lead you to the Oracle Applications Login Page as shown in figure 1 below.

Oracle Applications Login Page

Choosing Responsibility

To connect to the Oracle Applications, you must have an Oracle Apps login ID created by the System Administrator of the Oracle Applications instance.

Enter the appropriate Login ID and password and click login. If the login is successful it will lead you to the responsibility page as shown in figure 2 below. This page will show all the responsibilities that have been assigned to your Login ID by the System Administrator.

List of Responsibilities

Choosing Function / Opening Form

Once you click on a responsibility, it will show you the menus and functions associated with the responsibility. Click on the Sys Admin, Vision Services (USA) responsibility. The page will look like as shown in the figure 3 below.

Menus and Functions attached to a responsibility

Each function under a menu opens a form when clicked upon. The forms are basically an interface between you and the applications for various functionalities. These forms are run on Oracle Jinitiator. Once you click on a function, the jinitiator is invoked and it then opens the respective form. If you are login for the first time, Oracle Applications automatically downloads and installs the jinitiator for you. It, therefore, takes a longer time to open the form for the first time. Once the jinitiator is installed, it opens an intermediate window (figure 4) before the form is opened. Please do not close this window as it will close the Oracle Applications session. Wait till the form window opens up automatically. The intermediate page would go to the background.

Intermediate Window

Click on the Define link under the Concurrent: Program Menu. This will open the form to define the Concurrent Program in Oracle Applications. The form is shown in the Figure 5 below.

Figure 5. Concurrent Programs a Define form

Switch Responsibility

The details about using the above form are given in the document on Concurrent Programs. To switch to another responsibility, you can either go to the page which was shown in the figure 3 above and click on the required responsibility and follow the step 4 and step 5 or you can click on File tab and choose Switch Responsibility as shown in figure 6 below.

Figure 6. Swicth Responsibility

Clicking on Swicth Responsibility would lead you to the list of the available responsibilities to choose from as shown in figure 7 below.

Figure 7. List of Available Responsibilities

If you want to go to Application Developer responsibility to check the responsibilities attach to your username then follow these steps.


Click on File->Switch Responsibility
Choose Sys Admin responsibility among the list of responsibilities displayed.
Choose Security->User->Define from the following screen. The screen would look like as shown in the figure 8 below.

Figure 8. System Administrator à Security à User à Define

Once the Define Function is clicked, it will open the Define form which looks like as shown in the figure 9 below.

Figure 9. Define Form

Query Form

The Define shown in the figure 9 above is used to create Login Ids for the Oracle Applications users by the System Administrator. The same form can also be used to query the existing users’ data. To view (query) the existing data go to View->Query By Example->Enter to enter the query criteria as shown in the figure 10 below.

Figure 10. Query Method

Once you click on Enter as shown in the form above, the Define form switched mode to query mode and the color of the fields changes to BLUE. Enter a value in a field in the form above for which you want to query the data.

If you want to query for your username, type your username in the ‘User Name’ field on the form. The form would look like as shown in the figure 11 below.

Figure 11. Enter Query

To fetch the results matching the entered criteria, you need to run the query. This can be done by clicking on View->Query By Example->Run as shown in the figure 12 below.

Figure 12. Run Query

The data would be displayed in following fashion as shown in the figure 13 below. The form shows a lot of details about the username entered in the query. All the responsibilities attached to the user name are shown in the form.

Figure 13. Query Results

Keyboard Shortcuts

The same process can be used to query data on any form in Oracle Applications. You can also use the keyboard shortcuts for entering and running the query. For example, F11 key puts the form in the query mode and Ctrl + F11 runs the query. You can get the list of the complete key board shortcuts by clicking on Help->Keyboard Help as shown in the figure 14 below

Figure 13. Keyboard Shortcuts

Oracle apps tutorials Oracle apps Technical Tutorials Oracle apps realtime  BR100 MD50 MD70 oracle apps queries oracle apps