In oracle Applications' GUI includes unique features to enhance the productivity of Oracle's application users. Folder is one the features, that enhance the feature for high productivity.

What is Folder..

As per documentation is say .."A folder is a special kind of window that allows users to customize the set of fields and records displayed for a particular business entity without programming. Using the folder customization menu or tool palette, users can specify the records and the fields they wish to see, as well as field sequence, field prompts, and field display widths. Users can customize folders at any time, saving them for personal use or sharing them with others."

The most common use of folder technology is in the gateway to a key applications entity. Users can customize the folder window to display the set of fields and records that are most relevant to the business at hand. From the folder window, users can perform an action involving one or more of the returned records. Or, they can select a single record, open a detail window that shows all the fields for that record, and perform an action involving that record.

Here are the 9 steps to enable folders in custom forms.

These Changes will make sure that folder can be saved and its query criteria are also saved.

1. Pick up Template.fmb from your $AU_TOP/resource directory on server where forms are residing.

2. Check out all the libraries get loaded successfully along with the Template.

3. Check for APPDAYPK.pll loaded fine, else copy it from APPSTAND.fmb

4. Attach APPFLDR library/pll

APPFLDR can be found in $AU_TOP/Resource directory on server where forms are residing.

5. Copy the STANDARD_FOLDER Object Group

STANDARD_FOLDER Object group can be referenced from APPSTAND form which resides in $AU_TOP/Resource. This will also copies the dependent objects required for object group.

This will copy Folder Related Blocks,Canvases and other necessary objects.

6. Let us say we have One Main block, Not concerned with Control Blocks
Main Block:

XXPH_RCPT_HIS then

Create <BLOCKNAME>_RECORD_COUNT parameter
as XXPH_RCPT_HIS_ RECORD_COUNT parameter
where BLOCKNAME is the name of the block containing columns to be displayed. Data Type should be Number and Put default Value of 2.

Canvas
You need two canvas- one content and one stacked. Let us name content canvas as XXPH_RCPT_HIS_CONTENT and stacked canvas as XXPH_RCPT_HIS_STACKED. Both the canvas have same window.

Windows
Create One Window. Name it XXPH_RCPT_HIS_WIN with view name as XXPH_RCPT_HIS_WIN

Blocks
Main Block:
XXPH_RCPT_HIS contains database columns. Items should be on Stacked canvas (XXPH_RCPT_HIS_STACKED).

7. Create following eight triggers:

WHEN-NEW-BLOCK-INSTANCE
app_folder.event('WHEN-NEW-BLOCK-INSTANCE');

KEY-PREV-ITEM
if (:parameter.XXPH_rcpt_his_record_count = 1) then
previous_item;
else
app_folder.event('KEY-PREV-ITEM');
end if;

KEY-NEXT-ITEM
if (:parameter.xxph_rcpt_his_record_count = 1) then
next_item;
else
app_folder.event('KEY-NEXT-ITEM');
end if;

PRE-BLOCK
app_folder.event('PRE-BLOCK');

POST-BLOCK
app_folder.event('POST-BLOCK');

PRE-QUERY
app_folder.event('PRE-QUERY');

KEY-EXEQRY
app_folder.event('KEY-EXEQRY');

POST-QUERY
app_folder.event('POST-QUERY');

8. PROMPT Block:

Create new block XXPH_RCPT_HIS_PROMPT: which will contain prompts for the column displayed in upper block.

Make subclass information as DYNAMIC_PROMPT.

This block should contain:

Items which will be Prompts for above block columns (Item name should be same as Column name in above block). Prompt Items should be on Stacked canvas (XXPH_RCPT_HIS_STACKED).

Create new Items specified below:
Item Name: FOLDER_OPEN, Item Type: Button, Canvas: XXPH_RCPT_HIS_CONTENT,Subclass:Dynamic_Title
Item Name: FOLDER_DUMMY, Item Type: Text Item , Canvas: TOOLBAR ,Subclass:Folder_Dummy
Item Name: FOLDER_TITLE, Item Type: Display Item, Canvas: XXPH_RCPT_HIS_CONTENT,Subclass:Dynamic_Title

Item Name: ORDER_BY1, Item Type: Button, Canvas: XXPH_RCPT_HIS_STACKED
Item Name: ORDER_BY2, Item Type: Button, Canvas: XXPH_RCPT_HIS_STACKED
Item Name: ORDER_BY3, Item Type: Button, Canvas: XXPH_RCPT_HIS_STACKED
Item Name:Folder_Switcher, Item Type:Text,Canvas: XXPH_RCPT_HIS_CONTENT,Subclass:SWITCHER

Check out your Both Canvases for setting these new buttons,prompts.

9. Form Level Triggers

FOLDER_ACTION
--
-- Remove the message and uncomment the line after it to activate the folder actions
--
-- message('You must modify the FOLDER_ACTION trigger in your form!');
app_folder.event(:global.folder_action);

WHEN-NEW-FORM-INSTANCE
app_folder.define_folder_block(' XXPORCHUK ', -- 'Object Name'
' XXPH_RCPT_HIS ', -- 'folder_block',
' XXPH_RCPT_HIS_PROMPT ', -- 'prompt_block',
'XXPH_RCPT_HIS_STACKED', --'stacked_canvas',
'XXPH_RCPT_HIS_WIN', --'window',
NULL); --'disabled functions');

Once Concurrent program get complete, there are two file generated as follow:

• The Log File

Log files are stored under $APPLCSF/$APPLLOG if $APPLCSF is set and under $APPLLOG for each application if $APPLCSF is not set. The log file naming convention is the letter L, followed by the concurrent request ID, followed by the extension .req .

• The output File

Output files are stored under $APPLCSF/$APPLOUT if $APPLCSF is set and under $APPLOUT for each application if $APPLCSF is not set. The output file naming convention is Application User name.Request ID.

For the Information purpose, here are few for your interest.

• APPL_TOP - this is the top level directory for the Applications
• APPLCSF - the top level directory where the concurrent manager stores the log and out files of concurrent requests
• APPLLOG - concurrent request log files( As describe above)
• APPLOUT - concurrent request out files( As describe above)
• APPLTMP - Applications temporary files
• APPLPTMP - PL/SQL temporary files

Is there any naming conventions for the log file and output file?

Request log files: l<request id>.req
Output files:

• If $APPCPNAM is not set: <username>.<request id>
• If $APPCPNAM = REQID: o<request id>.out
• If $APPCPNAM = USER: <username>.out

Where: <request id> = The request id of the concurrent request And: <username> = The id of the user that submitted the request

Where are the managers logfiles goes:

Similar to concurrent requset output and log, the concurrent manager does also generate some files and typical naming convention are as below:

ICM logfile: Default is std.mgr, can be changed with the mgrname

• Concurrent manager log: w<XXXXXX>.mgr
• Transaction manager log: t<XXXXXX>.mgr
• Conflict Resolution manager log: c<XXXXXX>.mgr

Where: <XXXXXX> is the concurrent process id of the manager.

Here is the explanation how you can define concurrent managers and specify when a manager is enabled.

A concurrent manager is itself a concurrent program that starts other concurrent programs running. When an application user submits a request to run a program, the request is entered into a database table that lists all of the requests. Concurrent managers read requests from the table and start programs running. See: Concurrent Managers.

Next , we explain how to specify when a manager is enabled, how to use managers to balance your applications processing workload across different time periods, and how to associate a library of immediate concurrent programs to be called by your manager.

Defining new managers

You can define as many concurrent managers as you want. When you define a manager, you:

• Assign a predefined library of immediate concurrent programs to your manager.
  • Immediate concurrent programs are subroutines associated with concurrent managers. All other concurrent programs are spawned as independent processes at run time.
• Assign work shifts to your manager, which determines what days and times the manager works.
• For each work shift, you define the maximum number of operating system processes the manager can run concurrently to read requests (start programs) during the work shift.
• Specialize your manager to read only certain kinds of requests.

Program Libraries

For a program that is spawned, a concurrent manager initiates or spawns another operating system process. A program that is immediate runs as part of the concurrent manager's operating system process.

A program library contains immediate concurrent programs that can be called by your manager.

An immediate concurrent program must be registered with a program library. Application developers using Oracle Application Object Library can register concurrent programs with a program library.

The Oracle Application Object Library FNDLIBR program library contains Oracle Applications immediate concurrent programs, and is assigned to the Standard concurrent manager. In most cases, you will include the FNDLIBR library with your manager's definition.

The Concurrent Manager Managing request

A typical Concurrent manager is managing request in typical the flow which is described below.

And if Concurrent manager is having work shift set up , one the top of above process, the best can be described as:

How many Concurrent Managers available in oracle Apps

Similar to other products in there are seeded, Transaction and Other Concurrent Managers available. The Creation of custom manager is also possible , I am unable to throw some light, as i hadn't exposed to setting up of these area . In a typical implementation , it would be taken care by DBA's , and you can find some documents in system admin user guide for further details.Here , I am going to list of the concurrent manager which are used for some of processing within the product.Lets start to explore options for managers in EBS Suite:

The Seeded Concurrent Managers
Oracle seeds the Internal Manager and Conflict Manager with every implementation. Here are the list of some of them:

• Internal Manager

The internal manager is the highest level manager that controls all the other managers

• Conflict Resolution Manager

Resolves request conflicts. When programs are defined with incompatibilities with other programs, both programs will show up in the Conflict Resolution Manager if they are both trying to run at the same time. Programs that are submitted but have no place to run because of incorrect setup choices for the concurrent managers will also show up under the Conflict Resolution Manager.

The Transaction Managers

• Inventory Manager
  • Runs Inventory Programs. As seeded, the Inventory Manager has a number of individual programs assigned to it
• MRP Manager
  • Runs the Planning Manager. The MRP Manager is seeded by Oracle to only run the Planning Manager program. You may consider assigning a small number of other concurrent requests, including the MRP processes, to this manager if you are a heavy manufacturing user, to pull these critical processes out from under the Standard Manager.
• PA Streamline Manager
• Standard Manager
  • The general queue for handling requests. The Standard Manager was set up to run all concurrent requests.

You can find the details in earlier post.

In last post I have already discussed about Concurrent program and bit on Concurrent manager. Concurrent program are one of core functionality that oracle offered to handle some internal process without affecting other processing. So let take a deep drive for Concurrent Program. Lets start the definition once again.

• A “Concurrent program” is an executable file that gets executed by Oracle Applications with the ability to run simultaneously and in the background with other programs.
• Typically, a concurrent program is a long-running, data-intensive task, such as posting a journal or generating a report.
• The execution of these programs is controlled by the Concurrent Manager.

The Flow

Typical flow of concurrent processing can be best describe in above diagram as discussed in Developer guide

3 W and 1 P for concurrent program

When A concurrent program actually starts ?

... Best answer is Concurrent Program normally running based on:

• When it is scheduled to start
• Whether it is placed on hold,
• Whether it is incompatible (cannot run) with other programs
• Its request priority

The priority of a concurrent request is determined by application user name, and is set by the System Administrator using the Concurrent:Priority user profile option.

The first available concurrent manager compares the request's priority to other requests it is eligible to process, and runs the request with the highest priority.

When choosing between requests of equal priority, the concurrent manager runs the oldest request first.

Often, several programs may be grouped together, as in a request set. Submitting the request set as a whole generates a request ID, and as each member of the set is submitted it receives its own request ID. The set's request ID identifies the Parent request, and each of the individual programs' request ID identifies a Child request.

Life Cycle of a Concurrent Request

A concurrent request has a life cycle consisting of the following phases:

Within each phase, a request's condition or status may change. Below appears a listing of each phase and the various states that a concurrent request can go through.

Concurrent Request Phase and Status

This is chart which shows the conditions by stage.

Administrating Oracle application, its important to understand the core components.In oracle application, lets take some of them start with some of the

• Responsibilities
• Menus
• Profile Options
• Concurrent Manager
• Security Concepts

• Responsibility

A responsibility defines and address these:

• • What functions a user can use
  • What data a user can access
  • What “Set of Books” a user can access
    • Normally in oracle application a “Set of Books” describes as significant piece of the enterprise that has a different currency, calendar, or chart of accounts
    • Is the top level in the hierarchy that a responsibility is assigned to
  • What “Organization” a user belongs to
    • An organization is a sub-set of the “Set of Books”, which is used to further restrict the data a user can access Multi-org ,which allows a subsidiary ledger, such as AP, to process invoices that will reside in multiple sets of books.
    • Users normally are assigned to multiple responsibilities.
  • The R12 there is concept called MOAC which describe the access over different OU level. Will discuss this in details some other day.
• Menus
  • A menu is a hierarchical arrangement of functions and menus of functions. Each responsibility has a menu assig ned to it.
    You should take a note functionality of a responsibility can be customized by modifying the menu associated with the responsibility.
  • The same menu can be used by many different Responsibilities.
  • In oracle application,menus can not be copied. New menus have to be built from scratch.
  • While creating a new menu hierarchy, create the lowest-level menus first. A menu must be defined before it can be selected as an entry on another menu.
  • When a menu’s name is changed, the menu entries are not affected. Other menus calling the menu by its old name, automatically call the same menu by its new name.
  • Menu structure can be documented by printing a Menu Report.
  • Use the Define Menu Form in the System Administration responsibility to create new Menus
  • You can create and define Menu Form in the System Administration responsibility to create new Menus.
• Profile Options

• These are a set of changeable options that affect the way your application runs.
• These can be set at different level, from 11.5.10 there are 6 different level where you can make take the options.
• Further control security by assigning a set of books to a responsibility,Application,or Site.
• Set of books is a company or group of companies that uses Oracle Applications that share a common Chart of Accounts,Currency,and Calendar.

Profiles are established at six different levels:

1. Site Site level profile values are used when the profile option can have only one value for the entire instance of Oracle. (example- EDI Input file path lets say if the entire server is using one directory to accept inbound files from EDI,then you would just set the profile option at the site level, then it flows down to all levels, and there is no need to set this at other levels).
2. Application
3. Server
4. Organization
5. Responsibility
6. User

• • If you are very new to oracle application , it should be noted that system Profile Options are set by the System Administrator and affect the behavior of the Application and its modules for everyone.
  • Personal Profile Options are set by the individual user and cover that individual’s interaction with the system.
• Concurrent Manager
  • In Oracle Applications, concurrent processing simultaneously executes programs running in the background with online operations. with system Administrator, you can manage when programs are run and how many operating system processes Oracle Applications devotes to running programs in the background.
  • When a user runs a report, a request to run the report is generated. The command to run the report is a concurrent request. The program that generates the report is a concurrent program. Concurrent programs are started by a concurrent manager.
  • Every time your users request a concurrent program to be run, their request is inserted into a database table, and is uniquely identified by a request ID. Concurrent managers read requests from this table.
  • Part of a manager's definition is how many operating system processes it can devote to running requests. This number is referred to as the manager's number of target processes.

A concurrent program actually starts running based on:

• • • When it is scheduled to start
    • Whether it is placed on hold,
    • Whether it is incompatible with other programs
    • Its request priority
  • The priority of a concurrent request is determined by application user name, and is set by the System Administrator using the Concurrent:Priority user profile option.
  • The first available concurrent manager compares the request's priority to other requests it is eligible to process, and runs the request with the highest priority.
  • When choosing between requests of equal priority, the concurrent manager runs the oldest request first.
  • Often, several programs may be grouped together, as in a request set. Submitting the request set as a whole generates a request ID, and as each member of the set is submitted it receives its own request ID. The set's request ID identifies the Parent request, and each of the individual programs' request ID identifies a Child request.

• Security Concept

In oracle application security can be classified in two broad area viz:

• Database Security
  • Each Oracle application module database objects ( tables, views etc.,) are owned by a different Oracle ID. For example, all General Ledger objects are owned by the Oracle ID called GL, all Accounts Payables objects are owned by AP and so on.
  • All programs under a responsibility are executed using these Oracle IDs. For example, the General Ledger responsibilities use the GL database user-id.
  • Custom tables created to interface with Oracle Applications should be placed in a separate Oracle id to facilitate upgrading the application when necessary.
• Function Security
  • Function security is the mechanism by which user access to application functionality is controlled. Function is a part of an application’s functionality that is registered under a unique name for the purpose of assigning it to, or excluding it from, a responsibility.
  • Two types of functions: Form function ( known as a form) and non-form function ( known as a sub-function).
    A form function invokes an Oracle Forms form. Forms appear in the Navigate Window and can be navigated to. Forms can exist on their own.
  • A non-form function ( sub-function) is a function executed from within a form. These are frequently associated with buttons or other graphical elements in the form. Sub-functions do not appear on the Navigate window.

AOL Integration

In AOL , Forms, Menus, Responsibilities, Profiles, Users are inter related. Here are diagram which best describe the dependency among each other.

 

 

 

 

 

 

 

 

 

 

The key types of concurrent managers in EBS are:

The Internal and the Standard concurrent managers

Conflict Resolution Manager (CRM)

• CRM enforces any incompatibility rules.

Product Specific Managers

• It includes inventory, MRP, PA and any user defined managers

Transaction Managers

• It supports synchronous request processing
• A small pool of server-side transaction manager processes service a large number of clients
• Ex. of this manager is approval of an order

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.

These are the user exits available in Oracle Reports that makes AOL integration.

• FND SRWINIT
• FND SRWEXIT
• FND FORMAT_CURRENCY
• FND FLEXIDVAL
• FND FLEXSQL

• FND SRWINIT
  • This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
  • FND SRWINIT also allows your report to use the correct organization automatically.
  • Can be used in BEFORE-REPORT Trigger.
• FND_SRWEXIT
  • This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
  • Can be used in AFTER- REPORT Trigger
• USER EXITS
  • are used in Oracle APPS to access user profile values and perform proper calculation.
  • Ex. of Other AOL User exits available in Reports are given below.
• FND FORMAT_CURRENCY
  • To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
  • You obtain the currency value from the database into an Oracle Reports column. Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value. A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax
FND FORMAT_CURRENCY
CODE=”:column containing currency code”
DISPLAY_WIDTH=”field width for display”
AMOUNT=”:source column name”
DISPLAY=”:display column name”
[MINIMUM_PRECISION=”:P_MIN_PRECISION”]
[PRECISION=”{STANDARD|EXTENDED}”]
[DISPLAY_SCALING_FACTOR=””:P_SCALING_FACTOR”]

• FND FLEXSQL
  • This user exits allows you to use Flex fields in Reports.

Syntax:
FND FLEXSQL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
OUTPUT=":output lexical parameter name"
MODE="{ SELECT | WHERE | HAVING | ORDER BY}"
[DISPLAY="{ALL | flexfield qualifier | segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

• FND FLEXIDVAL

Syntax:
FND FLEXIDVAL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
DATA=":source column name"
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]
[SECURITY=":column name"]

This is an AOL user exit available to populate key flexfields for display

CODE means Key flexfield code(GL# is for Accounting Flex field, for all other check the table FND_ID_FLEXS)
NUM is the structure of the key flex field(Chart of Accounts Number)
DATA is where you store the retrieved data (your sql output).

Example

SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":P_SEC_SEG"
MODE="SELECT"
DISPLAY=":P_SEC_SEG_VAL" ')

The userexit call FND FLEXIDVAL :

SRW.REFERENCE(:SEC_SEG);
SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
DATA=":SEC_SEG"
APPL_SHORT_NAME="SQLGL"
VALUE=":SEC_SEG_DISP"
DISPLAY="ALL"
NUM=":P_STRUCT_NUM" ');
return(:sec_seg_disp);

You can enter a value directly to a date field or use the calendar facility in APPS. Calendar lets you the following needs.

• Represent date and time graphically so that you can make a quick selection without typing.
• Select only those dates or date ranges that are suitable for the current field.

How to invoke a calendar in a form

To invoke a calendar in a custom form you need to do the following.

• Create a text item with data type DATE in the form.
• Set the LOV of the text item to ENABLE_LIST_LAMP in the property sheet.
• Set the LOV for Validation property of the text item to FALSE.
• Create an item level trigger KEY-LISTVAL as CALENDAR.SHOW

In oracle application there are several advanced techniques by which we can use when defining value sets for validating input. Normally such techniques use values obtained from a location other than a list specifically defined for the value set. These locations may includes:

• Values retrieved from an application table
• The current value for a particular profile option
• A value used earlier in a field on the form
• A value used earlier in another value set

Here are some information with advance validation options.

• Using $PROFILES$

This is used to reference the current value of a profile option in a WHERE clause by prefixing the name of the profile option with $PROFILES$.

Usage:

:$PROFILES$.profile_option_name

A typical example with the use this keyword in a WHERE clause to reference a profile option value.

....WHERE SET_OF_BOOKS_ID = :$PROFILES$.GL_SET_OF_BOOKS_ID

so what happen when ever the SET_OF_BOOKS_ID need to pass the $PROFILES$ options simply reference the value which is retrived at form level.

A list of available Profile options can be found in one of the last post.

• Using :Block.field

This is used to references the value of an earlier appearing field on the same form

Using :block.field is different from using a descriptive flex field reference field in that the flex field structure does not change based on the
different :block.field values.

By Using this value set only with flex fields on windows that have the same block.field available.

• Using $FLEX$

This is used to references the value from a value set used earlier on the same form

You can refer to the current value of a previously used value set on the same form by using $FLEX$.value_set_name.

Usage

......WHERE JOURNAL_TYPE = :$FLEX$.GL_SRS_JOURNAL_TYPE

What is value set in Oracle application AOL

• Value set is primarily a ”container” for your values, you define your value set such that it can control the types of values that are allowed into the value set (either predefined or non–validated). You can specify the format of your values.
• Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Standard Request Submission (value sets for report parameters for your reports that use the Standard Request Submission feature).

When to defining Values for Value Sets

• After you register your Flexfields & report parameters, if you are using independent or dependent value sets, you can enter values into each corresponding value set using the Segment Values form.
• Values for the Value Sets, we are defining will be kept in the Oracle Application Object Library’s tables.

How many Format Types the value set have

• Character
• Number
• Time
• Standard Date, Standard Date Time
• Date, Date Time

You should take a note that Date and Date Time value set formats will be obsolete now and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What is Security type in value set?

• By Security Rules window, we can define value security rules for ranges of flexfield and report parameter values.

There are two levels where you must activate Security, the one at value set level and other at individual segment or parameter level. You make Flex field Value Security available for your value set by choosing Hierarchical Security or Non-Hierarchical Security for the Security Type. When you make security available for a value set, all segments and report parameters that use that value set can use security. You then enable security for a particular segment or parameter.

• Choose Hierarchical Security, If you want Security on a parent value to “Cascade down” to its child value or else you can choose Non-Hierarchical Security.

How many Character Formatting Options have for value set?

• Numbers Only (0–9)
  • We cannot prevent users from entering a value that contains the radix character.
  • Cannot be used in Translatable Independent and Translatable Dependent value sets.
• Uppercase Only
  • Here also we cannot use in Translatable Independent and Translatable Dependent value sets.
• Right–justify and Zero–fill Numbers
  • If you have selected Numbers Only (0–9) flag, then it wont allow you to affect this flag.
  • We are recommended to use this in Accounting Flex fields.
• Minimum and Maximum Value Range
  • Your Minimum/maximum value may not be longer than the maximum size you specify for this value set.
  • Once you specify a range of values, you cannot define a new valid value that falls outside this range.
  • The Minimum Value and Maximum Value fields can therefore allow you to create a value set with a validation type of None.

How many validation Type does value set have?

There are several validation types that affect the way users enter and use segment or parameter values:

• None (not validated at all).
  • Allow users to enter any value.
  • Only Format Validations will be done.
• Independent.
  • Provides a predefined list of values.
  • Independent values are stored in an Oracle Application Object Library table.
    
• Dependent.
  • Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
  • Must define your independent value set before you define the dependent value set that depends on it.
  • Advisable to create your independent values first.
  • Must create at least one dependent value for each independent value, or else it wont allow you to enter into that segment or field.
• Table.
  • It use your own application tables as value sets for flex field segments and report parameters instead of the special values tables which Oracle Applications provides.
  • You can also use validation tables with other special arguments to make your segments depend on profile options or field values.
  • You can use any existing application table, view, or synonym as a validation table.
  • If we are using non–registered table for our value set, then we have to Create the necessary grants and synonyms to APPS Schema.
  • The value column and the defined ID column in the table must return a unique row for a given value or ID.
  • If the Hidden Id column is provided the value passed to the report will be Hidden and not the Value column.
  • Similarly, when you specify :$FLEX$.Value_Set_Name, your flex field segment or report parameter defaults to always use the hidden ID column to compare with your WHERE clause .
  • We can use Special BIND variable such as :$PROFILES$.Option_name, :$FLEX$.Value_set_name, :block.field in the WHERE clause.
  • We can also display additional columns.
    Ex: Column_name1 “Column Title” (Width), ……
    org_id “Organization” (30)

• Special.
  • Special validation value sets allow you to call key flex field user exits to validate a flex field segment or report parameter using a flex field–within–a–flex field mechanism. You can call flex field routines and use a complete flex field as the value passed by this value set.
• Pair.
  • Pair validation value set allows user to pass a range of concatenated Flex field segments as parameters to a report.
• Translatable Independent & Translatable Dependent
  • These value sets are similar to Independent and Dependent value sets except that translated values can be displayed to the user. Translatable Independent and Translatable Dependent value sets allow you to use hidden values and displayed (translated) values in your value sets. In this way your users can see a value in their preferred languages, yet the values will be validated against a hidden value that is not translated.
  • We can convert the Independent value set to a Translatable Independent value set, or a Dependent value set to a Translatable Dependent value set. These are the only types of conversions allowed.

Which Oracle table store Value sets and underline information

• FND_FLEX_VALUE_HIERARCHIES
• FND_FLEX_VALUE_SETS
• FND_ID_FLEX_SEGMENTS
• FND_FLEX_VALUE_NORM_HIERARCHY
• FND_FLEX_HIERARCHIES
• FND_FLEX_VALUE
• FND_FLEX_VALIDATION_EVENTS
• FND_FLEX_VALUE_RULE_LINES
• FND_FLEX_VALUE_RULE
• FND_FLEX_VALUE_RULE_USAGE
• FND_RESPONSIBLITY
• FND_TABLES
• FND_FLEX_VALIDATION_TABLES

Any method to upload flexfield value?

Yes, FNDLOAD is utility which can be used for moving value set across different environment.See the FNDload post.

Do we have any restriction on value set?
Yes, here are some listed one:

• Table Validated Value Sets
  • We cannot use table-validated id value sets for any accounting flexfield or any other key flexfields.
  • We cannot use :$FLEX$, :$PROFILES$ in table name, value and id of table validated value sets.
  • We cannot use DISTINCT clause in any of the column fields or in the WHERE clause of a table validate value set.
  • In an id value set, the value can be non-unique but id should be unique. In a non-id value set, value should be unique.
  • We can only use columns selected for the table-validated value set must be of type NUMBER, DATE or VARCHAR2.
  • Support for SQL expression in columns of Table Validated value sets will be obsolete in future release.
• Translatable Independent and Translatable Dependent Valuesets
  • The Numbers Only and Uppercase Only option cannot be used.
  • Must have "Char" format type.
• Special/Pair valuesets
  • Special/Pair value sets are user-exit value sets . PL/SQL APIs will not be able to validate them.