Oracle R12 AOL
Oracle R12 AOL
Oracle R12 AOL
e-Business Suite
The following are the most important TOP level directories in oracle apps.
APPL_TOP
Contains Oracle Application Product directories such as PO, INV, OM, GL, AP, AR etc.
COMMON_TOP
Contains Common directory and files (log files, java libraries) which is shared across
different modules.
ORACLE_HOME
Contains technology Stack component such as Oracle DB (10g/11g), Oracle Developer
Suite (Forms, Reports) etc.
DATA_TOP
Contains Data files.
INST-TOP
It is new top in R12. It contains configuration files of application & technology stack to share
between multiple instances.
Application Object
Library
(AOL)
Why AOL?
FND user
Responsibility
Data Group
Application
Oracle Schema
Request Group
Concurrent Prog.
Executable
Menu
Forms
FND Applications
Responsibility
FND_APPLICATION_TL
~ APPLICATION_ID
~ LANGUAGE
Why?
To register your application name, application short name, application base path,
and application description with Oracle Application Object Library.
Oracle Application Object
Library uses this information to identify application objects such as responsibilities
and forms as belonging to your application.
This identification with your custom application allows Oracle Applications to
preserve your application objects and customizations during upgrades.
We are creating a custom application to isolate custom code and/or data from shipped
Oracle Applications.
Use of Short name -
Oracle Applications use the application short name as an internal key; for example, when
identifying forms, menus, concurrent programs and other application components. The
short name is stored in hidden fields while the name displays for users.
Its is the name of an environment variable that represents the top directory of your
application's directory tree. Oracle Applications searches specific directories beneath the
base path for your application's files and scripts.
SUMMARY ::
Used to
View the Existing Applications & its base path.
When creating Custom application.
Below you can see the screen shot of oracle Purchasing Application definition.
Oracle UserName
Table: FND_ORACLE_USERID
~ ORACLE_ID
Oracle Schema
Responsibility
Forms
When?
The installation process always registers your ORACLE user name, so you
need not register it unless you create a custom application using Oracle Application
Object Library, (or).
To associate an additional ORACLE user name with Oracle Applications.
If you do not register and enable your ORACLE user name (or) if you disable a registered
ORACLE user name, your user cannot use Oracle Application Object Library features such
as menus and flex fields.
PASSWORD -
Why?
To restrict access to AOL features (menus, help text, and flex-fields) and its tables privilege is used.
Privilege Types -
(a) Enabled:
An enabled ORACLE user name has full privileges (insert, query, update, and delete) to the
Oracle Application Object Library database tables.
(b) Restricted:
Restricted ORACLE user name has only query privileges to the Oracle Application Object
Library database tables.
This ORACLE user name can view Oracle Application Object Library data, but cannot
(c) Disabled:
A disabled ORACLE user name has no privileges to the Oracle Application Object Library
database tables.
This ORACLE user name cannot insert, query, update, or delete Oracle Application Object
Library information and cannot use Oracle Application Object Library features.
Additional Privilege Types -
Two additional privilege types appear, associated with ORACLE user-names configured at
installation.
(i) Public:
The installation process registered an ORACLE user-name with the Public
privilege, allowing all users to access the Application Sign-On Security form where
they must enter a valid Oracle Applications user-name and password.
(ii)Applsys:
The installation process registered the Oracle Application Object Library ORACLE
user-name with the Applsys privilege.
Default Oracle User-Names in oracle apps
Table: FND_DATA_GROUPS
~ DATA_GROUP_ID
21
FND user
Responsibility
Application
Forms
Data Group contains -
Applications associated with user names
Application includes -
Concurrent programs executes commands on application table
Oracle ID -
An application uses an ORACLE ID to access tables in the database. Each ORACLE ID allows
access to a predefined set of tables in the database.
Data Group
|
Application (owns Concurrent pg)
|
Oracle Username ( DB Privilages - Select, Update, Delete )
|
Database ( Oralce 9i/10g/11g )
Data Group Purpose -
Identifies Oracle user name when forms Connect to when you select responsibility.
Concurrent managers use a data group to match the application that owns a
report or concurrent program (submitted by a user of the responsibility) with a Oracle
username.
For example, with two installations of Oracle Payables supporting two Sets of
Books, use data groups to indicate which Oracle Payables Oracle username to
access from a certain General Ledger responsibility.
Yes, also you can copy a Data Group by defining new name.
Register the application using "Application form".
Assign Oracle Usearname with Application.
Standard Data Group associated with Oracle ID APPS
Request Security Group (Or) Request Group
27
FND user
Responsibility
Concurrent Prog.
Where 2 define -
Request Group form
Where 2 assign -
In Responsibility form
Is Request Security Group Contains requests & request sets frm diff. application -
yes, using Data Groups
Is user can run request sets with single request not in Request Security group -
yes, If and only if the Request Set is in Request security group
Restriction:
- user cannot edit requests
- user cannot stop specific request while running
- Can edit request set by deleting requests but not single request
what do Request Group contains -
- Individual reports & Concurrent programs
- Request set ( Collection of reports & Concurrent pgs )
- Request set stage fns. ( Used to calculate status of stage withing request set )
A request group code is simply an argument that is passed from a menu to a customized
standard submission form
When a menu that calls the standard submission form uses the
code, that form lists only those programs in the request group
identified by the code.
Request Security -
Used to specify reports, request sets & Concurrent programs
31
Menus
Table: FND_MENUS
~ MENU_ID Navigation: Application > Menu
FND user
Responsibility
Forms
Oracle Schema
Sequence:
Form
Functions
Level 2
Level 3
Form Functions
FND_FORM_FUNCTIONS_TL
~ FUNCTION_ID
~ LANGUAGE
FND_FORM
~ APPLICATION_ID
~ FORM_ID
FND_FORM_TL
~ APPLICATION_ID
~ FORM_ID
~ LANGUAGE
Responsibility
Menu
Data Group Request Group
What is a Function?
Types of Functions
~ form functions
~ non-form functions
We always refer
DESCRIPTION-
Function :
Users do not see this unique function name. However, you
may use this name when calling your function pro-grammatically
Type :
Type is a free-form description of the function's use (function type will
be validated in a future version of this form). A function's type is passed back
when a developer tests the availability of a function. The developer can write code
that takes an action based on the function's type.
Maintenance Mode Support:
Reserve for Future.
Context Dependence :
Some functions are controlled by profile options, that affect what the user can
perform within the current context. context dependence are Responsibility,
Organization, Security Group, None.
Form-
Form /Application :
If you are defining a form function, select the name and application of your
form.
Parameters :
Enter the parameters you wish to pass to your function. Separate parameters
with a space.
Agent Name :
The second section of your function URL is the Oracle Web Agent. The Oracle Web Agent determines
which database is used when running your function. Defaults to the last agent used.
Icon :
Enter the name of the icon used for this function.
Secured :
Secured is only required when your function is accessed by Oracle Work flow Checking Secured
enables recipients of a work flow E-Mail notification to respond using E-Mail.
Encrypt Parameters:
Oracle R12 - AOL by Dinesh Kumar S 52
Checking Encrypt Parameters adds a layer of security to your function to ensure that a user cannot access
your function by altering the URL in their browser window.
53
FND user
Responsibility
Men
About-
- Application Name and Responsibility name uniquily identifies the responsibility.
- Responsibility Key: unique name for a responsibility that is used by loader programs.
- A responsibility can be associated with only one Application.
Mandatory Fields:
Type
- When you exclude a menu, all of its menu entries, that is, all the functions and
menus of functions that it selects, are excluded.
Attribute Control:
where?
They are first defined using the Web Applications Dictionary.
Excluding attributes prevent certain columns of data from being visible to specified
responsibilities.
Seeded Securing Attributes:
Attributes are defined using the Web Applications Dictionary.
Assign securing attribute values for each user, and for each securing
attribute assigned to all responsibilities for this user.
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/aic/icxhsecr.htm
Oracle Application Responsibility:
Web Application
Table: FND_USER
~ USER_ID
Application Concurrent Prog. Form & Sub functions
Executable
Responsibility
User-name Constraints -
must not contain more than one word
use only alphanumeric characters
Password Constraints -
5 - 100 Characters
use only alphanumeric characters
Direct Responsibilities -
Direct responsibilities are responsibilities assigned to the user directly.
Responsibility
Select the name of a responsibility you wish to assign to this application user. A
responsibility is uniquely identified by application name and responsibility name.
Security Group
This field is for HRMS security only.
Table: FND_EXECUTABLES
~ APPLICATION_ID
~ EXECUTABLE_ID
FND user
Responsibility
Men
Execution Method-
The execution method cannot be changed once the concurrent program executable has
been assigned to one or more concurrent programs in the Concurrent Programs window.
The possible execution methods are:
.
- Do not include spaces or periods ( ) in the execution file name, unless the execution
method is PL/SQL stored procedure or Request Set Stage Function.
- The maximum size of an execution file name is 60 characters.
Subroutine Name -
Enter the name of your C or Pro*C program subroutine here. Constraints as below
- Do not use spaces or periods (.) in this field.
- Only immediate programs or spawned programs using the Unified C API use
the subroutine field.
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.
Lets Consider a sample Oracle Report Executable
Location:
74
Oracle R12 - AOL by Dinesh Kumar S
Concurrent Program
Table: FND_CONCURRENT_PROGRAMS_TL
FND_CONCURRENT_PROGRAMS ~APPLICATION_ID
~APPLICATION_ID ~CONCURRENT_PROGRAM_ID
~CONCURRENT_PROGRAM_ID ~LANGUAGE
FND user
Responsibility
Men
Why?
It govern and control the Concurrent program and slave concurrent managers.
The master manager is called the Internal Concurrent Manager (ICM) because it
controls the behavior of all of the other managers, and because the ICM is the boss, it
must be running before any other managers can be activated.
The main functions of the ICM are to start up and shutdown the individual concurrent
managers, and reset the other managers after one them has a failure.
Standard Manager (SM)
The SM functions to run any reports and batch jobs that have not been defined to run in
any specific product manager.
Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry
Manager, and the Receivables Tax Manager.
Short Name: used to associate your concurrent program with a concurrent program
executable.
Application: determines what ORACLE user name your program runs in and where
to place the log and output files.
Enable: determines what ORACLE user name your program runs in and where to place
the log and output files.
Executable: Options
In case of running Report Executable, we can choose options to print like Landscape /
portrait (or) we can control the dimension like pagesize etc.
Executable: Priority
The concurrent managers process requests for this program at the priority you assign
here.
If you do not assign a priority, the user's profile option Concurrent:Priority sets the
request's priority at submission time.
Request: Type
Associate your program with a predefined request type. The request type can limit which
concurrent managers can run your concurrent program.
Request: Incrementor
Used only by oracle internal developers.
Why Multiple times either we can schedule the concurrent program? this question arise to us.
Multiple times in this context means in multiple languages. So a single program can run in multiple
language.
Note**
Beginning with Release 12.1, MLS fOunracctlieoRn1s2c-aAnOsLubpypDoirntesmh uKlutimplaer Sterritories
and numeric character 81
sets as well as multiple languages.
Request: Check box Options
SRS:
If a user wants to run Concurrent program in Standard Request Submission window, this options is
mandatory.
Run Alone:
If your program is incompatible with all programs in its logical database, including itself this option can
be choose
NLS Compliant:
This allows user to run concurrent prOorgacrlaemR1d2e-
fAinOeLdbiynDdiinffeeshreKnutmtearrrSitory/Country. If this option is blank82 then territory will default to
the territory of the concurrent manager environment.
Output
Window
Note**
An appropriate printer driver that handles HTML or
PDF files.
Print:
To send the output to printer to print the same.
Style:Portrait/Landscape options.
Printer:If u want to print the output to a specific printer choose this option.
If you want to restrict any data which cannot be simultaneously access or update by two
concurrent program conflict domain data should be mention.
Security Group:
Seq: To Specify the sequence in which program receives input values from
concurrent manager.
If a parameter to be validated, a value set can be used. We can use only 3 types of
value sets.
i. Independent
ii. Table
iii. Non-Validated
Since value set field is mandatory, what should be the value to be provided if no
validation required for the parameter?
For a non-validated parameter we can assign Default Value.
Required: To make the parameter mandatory for the request this option is used.
Enable Security: If security rules enabled for the value set this option can be used.
Range: If we want to validate the parameter against another parameter we need to provide
range as low or high.
For Example, If we want to end date an employee record, we have 2 date parameters,
then we need to do following validation for Effective_Start_date. Set range to low
with respective to Effective_End_Date.
Display Size: This represents the field length of the parameter in request submission
window. By default all value sets have 240 chars as length. If more than that the
characters will be truncated.
Concatenated Description Size: User see this value in the parameter description
window.
Token:
This is one of the important parameter when the execution file is of type reports. The
parameter name defined in the RDF should be given in this field.
Monitoring Requests
SELECT lookup_code,
meaning
FROM fnd_lookup_values
WHERE lookup_type =
'CP_PHASE_CODE' AND
LANGUAGE = 'US'
AND enabled_flag = 'Y';
LOOKUP_CODE MEANING
Dinesh Kumar S 90
Oracle R12 - AOL by
C Completed
I Inactive
P Pending
R Running
Value Meaning
Concurrent Program Status Codes U Disabled
92
Two Types of Requests:
Single Request
Request Set
Single Request
Request Set
SE
Q
10
Oracle R12 - AOL by Dinesh Kumar S 93
20
30
Execution of multiple concurrent program one by one or in parallel, with passing one
program output as input to other program in sequence or executing in a sequence
order.
Stage 1
Stage 2 Stage 3
Step 2 Step 2
Parallel Execution:
When we want to run all the requests simultaneously (or) in Parallel within a stage parallel
execution method is used.
Stage 1
Request 1
Request 2
Request 3
Request 4
S 95
When we want to run requests one by one in a sequence order, this execution
method is used.
Request 3
Request 1 Request 2 Request 4
Note**
Not only Requests executes in order even stages executes in order. Unless
stage1 is completed stage 2 & stage 3 will not be executed.
A stage is not complete when all the requests within the stage are completed.
(a) Success
(b) Warning
(c) Error
Linking Stages:
Based on the above statuses we can link stages (or) setup sequence of execution
of stages.
Stage 3
Error
Request 2
Stage 1
Request 1
Success
Request 3 Request 5
Request 4
Stage 4
Warning
Oracle R12 - AOL by Dinesh Kumar S 97
Stage 2
Creating Request Set:
Set Code: This is used for internal purpose probably during coding.
Active Dates [From To]: These are the dates for which the request set will be active.
If the TO field is left blank represents it will be active forever.
Print Together: If this check box is checked, after completion of the request, the
details of all the request [together] will be sent to printer for printing.
Allow Incompatibility: It will allow the system administrator to specify lists of programs
incompatible with this request set.
Oracle R12 - AOL by Dinesh Kumar S 99
Method 1: Using Request set Wizard
Step 2: Select execution option for requests to be executed. In this case I'm
selecting Sequential order.
100
Step 3: Select a Decision when the request sets ends with status ERROR. .
Step 6: Lists the concurrent programs in a sequence order of execution. Any concurrent
program of any application can be included.
Step 7: After clicking finish button, the following note will be displayed to the user.
Since we have define the request set using wizard, by default all the request will
be created in single stage.
Step 9: To view how stages are linked click Link Stages button.
By Default, when the Stage statuses in WARNING or SUCCESS,
the option Stage to Proceed to On will be defaulted to same program name.
Method 2: Manually Creating Request set
Considering the same programs in method 1. This time we are going to define separate
stage for each programs.
Stage 1
Stage 2 Stage 3
Stage 2 110
Stage 3
111
Step 5: Link Stages by clicking Link Stages button.
Navigation:
Requests:
Request Set:
If you want to
execute the
request
immediately click
option As soon
as possible.
115
Step 4: Set Schedule Status Notification.
If the request output to be transmitted via Fax,Email, printer etc. the details
needs to be defined here.
Step 6: Schedule Summary Details [Review Page].
118
Profile Options:
Scenario 1: We have 100 users defined in oracle application, Consider all users have responsibility
US Self Service Purchasing. Now we have a new requirement, out of 100 only 30 should access the
responsibility and the responsibility should not be end dated for 70 users.
Scenario 2: We all know that system administrator responsibility have Admin privileges to
create/define new objects in oracle application. Also, we can assign this responsibilities to any
number of users say 100. Now business came up with an requirement, we need to provide access to
only 20 users to assign secure responsibility to other users even though all have system admin
responsibility.
Scenario 3: Consider I have a Purchasing Buyer responsibility, I want to provide read only access
users should not have access to modify/change/edit anything using that responsibility.
Scenario 7: Consider we have 20 requester s, who are given access to create requisitions. Now
business wants to provide access to each requester that they can only modify/change requisition
created by them and they should not be allowed to edit other requester s requisition..
Scenario 8: In oracle application we have a Debug functionality, suppose business wants to enable
this functionality only for Developers. This can be achieved via profile options.
Scenario 9: Consider, we have created a custom report, now business wants for execute this reports
only for certain organization. This can be achieved via profile options.
These are only some scenarios, we can use the profile option functionality to achieve any custom
functionality or even we can bypass oracle seeded functionality.
Profile Levels:
Site Level
Application Level
Organization Level
Responsibility Level
User Level
Profile Hierarchy:
(High) User --> Responsibility --> Organization --> Application --> Site (Low)
Level Level ID
Site 10001
Application 10002
Oracle R12 - AOL by Dinesh Kumar S 121
Responsibility 10003
User 10004
Server 10005
Organization 10006
How To Check If a Profile Option Is Set In Oracle Applications? (Ref. Note: 470102.1)
8.Output contains
Product Installation Status and Patchset Level
Site Level Profile Options
Application Level Profile Options
Responsibility Level Profile Options
User Level Profile Options
References
Run the standard concurrent report "User Profile Option Value" available from the "System
Administrator" responsibility.
SELECT po.user_profile_option_name,
po.profile_option_name "NAME"
,
DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003',
'RESP', '10004', 'USER', '???') "LEV",
DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002',
app.application_short_name , '10003', rsp.responsibility_key, '10004', usr.user_name, '???')
"CONTEXT",
pov.profile_option_value "VALUE"
FROM fnd_profile_options_vl po,
fnd_profile_option_values pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp
WHERE (po.profile_option_name = upper('&profil_name'))
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE"
How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus?
(ref Note: 201945.1 )
1. Enable Spooling
2. Execute the script
Table Used:
FND_PROFILE_OPTIONS
FND_PROFILE_OPTIONS_TL
FND_PROFILE_OPTION_VALUES
128
Name: A unique name given to a profile.
User Profile Name: This is the name your users see as their profile option.
Description: User can give the usage/purpose of the profile option (or) any
other note.
Types of Hierarchy:
Server
Server + Responsibility
Oracle R12 - AOL by Dinesh Kumar S 130
Organization
Security
The Server hierarchy type is used when the system needs to determine the
server on which the user's session is running.
For Example, the profile "Applications Web Agent can be used for cookie
validation, the setting may be different for local servers and external
servers.
Profiles that use the Security hierarchy type follow the traditional
hierarchy: Site
> Application > Responsibility > User.
Profiles using the Server type use the hierarchy: Site > Server >User.
Profiles using the Organization type use the hierarchy: Site > Organization >
User.
Hierarchy type Access level
User Access
If you want your end user to be able to update profile option values in
the Profile Values window, you must provide user visible and updatable
access at the User level here.
Visible - Indicate whether your system administrator can see your profile
option while setting user profile option values for the specified profile level.
SQL Validation
If you want your profile option to provide a list of values (LOV) the
following syntax to be used in the SQL Validation field.
To validate your user profile option, select the profile option value into the
fields.
i. :PROFILE_OPTION_VALUE
ii. :VISIBLE_OPTION_VALUE
Oracle R12 - AOL by Dinesh Kumar S 136
Syntax:
SQL:
SQL=SELECT FULL_NAME
,EMPLOYEE_NUMBER
INTO
:PROFILE_OPTION_VALUE
, :VISIBLE_OPTION_VALUE
FROM PER_ALL_PEOPLE_F
ORDER BY
EMPLOYEE_NUMBER"
Note**
You can use GROUP BY or HAVING clauses in your SQL statement, but only
in your main query; you cannot use them in sub-queries. You can use
DISTINCT and ORDER BY clauses as you would normally.
Set functions such as MIN(), MAX(), SUM(), and COUNT() can be used in the
SELECT or HAVING clause, but you cannot use them on the columns that
you select into the PROFILE_OPTION_VALUE or VISIBLE_OPTION_VALUE
fields.
Though you can use a fairly complex WHERE clause and/or an ORDER BY
clause in your SQL definition, you cannot use UNION, INTERSECT, or
MINUS in your main query. If you need a UNION, INTERSECT, or MINUS to
select the proper values, you should create a view on your tables, then
select from the view, or include these operators as part of a sub-query.
Lists the names of columns (or column aliases) you want to display in
your LOV window, the order in which to display them, and their display
widths.
If you specify more than one column in your COLUMN option, your LOV
displays the columns in the order you list them. This order can differ from
the column order in your SQL statement.
You must specify column widths in the COLUMN= "..." parameter, although
any column widths you specify in the HEADING="..." option below override
these values.
You can specify static or dynamic column widths in your COLUMN option.
o Static column width by following the column name with the desired
width.
SQL=SELECT FULL_NAME
,EMPLOYEE_NUMBER
INTO
:PROFILE_OPTION_VALUE
, :VISIBLE_OPTION_VALUE
FROM PER_ALL_PEOPLE_F
ORDER BY
EMPLOYEE_NUMBER
COLUMN= FULL_NAME(100) , EMPLOYEE_NUMBER(*)
Include Spaces in Column Alias,
put a backslash and double quotes before and after the column alias,
so that the LOV routine recognizes the double quotes as real double
quotes, rather than the end of your parameter.
SQL=SELECT FULL_NAME
,EMPLOYEE_NUMBER
INTO
:PROFILE_OPTION_VALUE
, :VISIBLE_OPTION_VALUE
FROM PER_ALL_PEOPLE_F
ORDER BY
EMPLOYEE_NUMBER
COLUMN= \EMPLOYEE NAME\(100)
TITLE:
Text you want to display centered and highlighted on the top line of your
LOV window i.e. the window title message.
Any title starting with "*" is treated as a Message Dictionary name, and the
message contents are substituted for the title.
Note**
SQL=SELECT FULL_NAME FND: is Application Short
,EMPLOYEE_NUMBER Name followed by
INTO message name.
:PROFILE_OPTION_VALUE
, :VISIBLE_OPTION_VALUE
FROM PER_ALL_PEOPLE_F
ORDER BY
EMPLOYEE_NUMBER
COLUMN= FULL_NAME(100) , EMPLOYEE_NUMBER(*)
TITLE=*FND:MESSAGE_NAME
HEADING:
Lets you specify a list of column headings and column widths, separated by
spaces or commas.
There should be one heading in the HEADING="..." parameter for
each column in the COLUMN="..." parameter.
We can suppress headings in your LOV window altogether by setting
HEADING="N".
If you do not provide an explicit TITLE and HEADING in your SQL validation,
your profile has TITLE="user_profile_option_name" and HEADING="N"
appended to the definition at runtime.
SQL=SELECT FULL_NAME
,EMPLOYEE_NUMBER
INTO
:PROFILE_OPTION_VALUE
, :VISIBLE_OPTION_VALUE
FROM PER_ALL_PEOPLE_F
ORDER BY
EMPLOYEE_NUMBER
COLUMN= FULL_NAME(100) , EMPLOYEE_NUMBER(*)
HEADING=*FND:MESSAGE_NAME(*)
Value Sets
Oracle Application Object Library uses values, value sets and validation
tables as important components of key flexfields, descriptive flexfields,
and Standard Request Submission.
We define value sets first, either before or while you define your flexfield
segment structures.
List Type:
Choose List of Values if your value set should not provide the
LongList feature in Oracle Forms applications.
Choose Long List of Values if your value set should provide the
LongList feature in Oracle Forms Applications.
This feature requires a user to enter a partial segment value before
the list of values retrieves all available values.
You may not enable LongList for a value set that has a validation type of
None.
Choose Poplist if your value set should not provide the LongList
feature in Oracle Forms applications, but should provide a poplist in
Oracle Self-Service applications.
Guidelines for List type field,
Security Type:
Specify the Security Type you plan to use with any segments that
use this value set. Security does not apply to value sets of
validation type None, Special, or Pair.
Flexfield value security is not available for Translatable Independent
and Translatable Dependent value sets.
1. No Security
2. Hierarchical Security
3. Non-Hierarchical Security
No Security
Hierarchical Security
Non-Hierarchical Security
)
Character
)
Date
)
DateTime
)
Number
)
Time
)
Standard Date
)
Standard DateTime
Maximum & minimum value for the value set can be given.
Choosing the maximum size for your value set depends on what
flexfield you plan to use with your value set.
Oracle Applications does not allow you to assign a value set whose
values would not fit in the flexfield table.
This flow diagram
shows how
Valueset will
behave based on
the format
validation setups.
Step 2: Decide Usage and Need
Use a dependent set if you want your segment values to depend upon
the value chosen in a prior independent segment.
If we already have suitable values in an existing table, you should choose a
table set.
Value set Type compatibility chart with LOV
Step 3: Choosing a Validation Type
Note**
The Accounting Flexfield only supports Independent, Dependent, and Table validation.
We cannot change the validation type of an existing value set, since your changes affect
Table: FND_FLEX_VALUE_SETS
U Special
P Pair
I Independent
D Dependent
Y Transparent Dependent
X Transparent Independent
N None
F Table
None:
When you want to allow users to enter any value and it meets the
value set formatting rules.
Because a None value set is not validated, a segment that uses this
value set does not provide a list of values for your users.
The meaning of a value in this value set does not depend on the
value of any other segment.
use a table-validated set when you have a table whose values are
already maintained in an application table.
6
Oracle R12 - AOL by Dinesh Kumar S 167
Edit Information:
Special and Pair Value Sets:
Special and pair value sets provide a mechanism to allow a "flexfield-
within-a-flexfield". These value sets are primarily used for Standard
Request Submission parameters.
We do not generally use these value sets for normal flexfield segments.
Special validation value sets allow you to call key flexfield user exits to
validate a flexfield segment or report parameter using a flexfield
withinaflexfield mechanism.
Or 9
Oracle Seeded Valueset: PO_SRS_ITEM
For example, the Validate event occurs once a user enters a value,
and your
function would validate that value.
We can use a special validation value set to let your users enter an
entire key flexfield combination within a single segment of a
descriptive flexfield or report parameter.
For example, you may want to pass concatenated key flexfield segments
as a parameter to a report.
Special validation provides several special arguments you can use to pass
values to and from the user exits,
:!ID
:!VALUE
:!MEANING
!DIR
:!ID
You can use :!ID to pass different information depending upon the
circumstances,
For flexfield routines, :!ID can pass either a combination ID
number of an entire combination of segment values (key
flexfields only)
It can pass a concatenated string of the individual flexfield segment
values (either key or descriptive flexfields).
When you use :!ID to pass a concatenated string of individual segment
values, :!ID should contain the hidden ID values.
:!MEANING
Use :!MEANING to pass the concatenated descriptions of your flexfield values.
The value description appears as usual next to the flexfield
segment value and in the concatenated description field.
!DIR
Use !DIR for the NAVIGATE argument of key and descriptive flexfields routines.
!DIR allows the flexfields routines to determine the proper navigation
direction when you use a flexfield as a segment value set.
Do not use a colon when you specify !DIR for POPID or other flexfield
routines.
Example: (Reference Flexfield Guide)
Suppose you want to let your users pass a single combination of concatenated
Accounting Flexfield segments as a parameter to a report. To let your user choose a
single combination, you must provide a key flexfield window from within the
report parameters window on the Run Reports form.
To do this, you simply define a value set with Special validation and use your familiar
flexfield user exits.
Since you want to pass an existing combination (that is, you want to pass the ID
number of the combination) and this is a foreign key flexfield, you use
VALIDATE=FULL and the ID=:!ID argument. You do not use the DATA_FIELD=:!ID
argument.
Below Events are identified,
Data Entry
Data Query
Data Validation
179
Oracle R12 - AOL by Dinesh Kumar S 180
Pair Validation Valueset
Edit Information:
Example Pair Validation:
Suppose you want to let your users pass a range of concatenated Accounting
Flexfield segments as parameters to a report. For example, you want to let your
users request a report on all combinations where the second segment
value is between 001 and 101, inclusive.
To let your user choose such a range, you must provide a key flexfield range window
from within the report parameters window on the Run Reports form. To do this, you
simply define a value set with Pair validation and use your familiar
range flexfield user exits to pass a range of concatenated segment values.
For a range flexfield, you use VALIDATE=PARTIAL (or NONE). Since you use a
range flexfield, you cannot use the ID=:!ID argument. You do not use
DATA_FIELD=:!ID in this example (hidden ID value sets are not allowed with the
Accounting Flexfield), so you do not need a Load event.
Using Variables with Special and Pair Validation
Value_Set_Name is the name of either the value set for a prior segment. The
segment name of a prior segment in the same flexfield. Parameter window
that you want your validation table based values to depend on.
The $FLEX$ mechanism uses the closest prior segment with either a
matching value set name or segment name (it looks for the value set name
first, and uses the segment name second if there are no matching value set
names).
Value_Set_Name is casesensitive.
We can only use letters, numbers, and underscores (_) in your value set
names if you want to use them with a :$FLEX$.Value_Set_Name clause.
We cannot use quotes, spaces, or other special characters in these value set
names.
You can specify more than one :$FLEX$.Value_Set_Name in a single
WHERE clause, thereby creating a segment whose list of possible values
depends upon more than one previous segment.
Using this :block.field argument, however, gives you the same segments that
would normally appear, but changes the contents of the value set attached to
the segment depending on
what appears in your :block.field.
In some cases, you may wish to use a :block.field value set instead of a
descriptive flexfield reference field with many different contextsensitive
structures.
Note that if you use this argument, you must have the same :block.field on
every form where a value set based on this validation table could be used. For
example, if the same flexfield appears on seven forms, the all seven forms must
have this block.field.
Similarly, if you share your value set among more than one flexfield, then all
forms that use any of those flexfields must have this block.field.
Oracle R12 - AOL by Dinesh Kumar S 187
:$PROFILES$.profile_option
:NULL suffix
Use the :NULL suffix to make your bind variable optional, that is, allow null
values.
Instead of :block.field, :$PROFILES$.Option_name, or :
$FLEX$.Value_set_name, you would use
:block.field:NULL, $PROFILES$.Option_name:NULL, or :
$Flex$.Value_set_name:NULL, respectively.
For example, if your value set name is Car_Maker_Name_Value_Set, you
would use :
$FLEX$.Car_Maker_Name_Value_Set:NULL.
Flexfield
Customize your applications to conform to your current business practice for accounting
codes, product codes, and other codes.
Customize your applications to capture data that would not otherwise be tracked by your
application.
Have "intelligent fields" that are fields comprised of one or more segments, where each
segment has both a value and a meaning.
Rely upon your application to validate the values and the combination of values that you
enter in intelligent fields.
Have the structure of an intelligent field change depending on data in your form or
application data.
Customize data fields to your meet your business needs without programming.
No, not all modules will have a structure or Key flexfield defined. Only Some
modules have KFF. When implementing Multi-Org you will be understanding it
better.
Navigation: SYS ADMIN RESP Application Flexfield Key Segments
Flexfield segments
Note** in all the base tables you can see Segment Columns where the flexfield value
will be stored.
Application: Name of the application in which flexfield is defined.
Flexfield Title: This is the Key Flexfield Name that belongs to the application General
Ledger.
For those application flexfields that support more than one structure (such as the multiple charts
of accounts in the Accounting Flexfield), you can create a new structure for your flexfield by
inserting a row. If you are defining the first structure for your flexfield, select the default flexfield
structure that appears automatically. If you are modifying an existing structure, use your cursor
keys to select the title of the flexfield structure you want.
Check the Enabled check box so that this structure may be used in your key flexfield. You cannot
delete structures from this window because they are referenced elsewhere in the system, but you
can disable them at any time. A structure must be enabled before it can be used.
Select the character you want to use to separate your flexfield segment values or descriptions. This
separator will appear whenever your application forms display concatenated segment values or
descriptions.
It is recommended that you do not use a character as your segment separator if you expect that
character to appear frequently in your segment values or descriptions.
If you do use a character that appears in your segment values or descriptions, then that character
will be preceded by a backslash (\) when it appears in a value or a description. A backslash in your
values will be preceded by another backslash.
Select the Cross-Validate Segments check box if you want to cross-validate
multiple segments using cross-validation rules. You can define cross-
validation rules to describe valid combinations using the Cross-Validation
Rules form. Uncheck the box if you want to disable any existing cross-
validation rules
Indicate whether you want to freeze your rollup group definitions. If you
do, you prevent users from modifying rollup groups using the Segment
Values form. You can freeze rollup groups before or after you define your
flexfield structure.
If you want to allow dynamic inserts, check the Allow Dynamic Inserts
check box. You would allow dynamic inserts of new valid combinations into
your generic combinations table if you want users to create new
combinations from windows that do not use your combinations table. You
should prevent dynamic inserts if you want to enter new valid
combinations only from a single application window you create to maintain
your specific combinations table.
You can update this field only if your application flexfield has been built
to allow dynamic inserts, and the flexfield has been defined in the
Register Key Flexfields form with Dynamic Inserts Feasible checked.
Otherwise this field is display only.
Descriptive flexfields can be context sensitive, where the information your application
stores depends on other values your users enter in other parts of the form.
Field in a form
From a special field (a context field) inside the descriptive flexfield popup
window.
A DFF requires one column for each possible segment and one additional column in which
to store structure information (that is, the context value). The descriptive flexfield
columns are usually named ATTRIBUTEn where
n is a
Oracle R12 - AOL by Dinesh Kumar S 200
number.
Enabling DFF in a Purchase Order form:
DFF
2
Give the Block & field
values as given
3
Step 2: Enable the DFF
DFF Enabled
DFF Enabled
Hope this was a great learning