Oracle Apps Technical

Procedure Registration Steps:
1)Develop the procedure and compile  at SQL prompt
2)Connect application select system administrator Responsbility create
  Executable by selecting the Execution Method as 'PL/SQL Stored Procedure'
3)Create Concurrent Program and attach Executable to the Program and add the
  Parameters and  incompatible programs.
4)Create Request group attach concurrent Progrtam
5)Attach Request group to the reponsibility  
6)Responsiboility  will be added to the user.
 User will submit the request from SRS Window.
Syntax:
Create Or Replace Procedure <ProcedureName> (Errbuf OUT varchar2,
    Retcode OUT varchar2,
    P1    IN NUMBER,
    P2    IN VARCHAR2,
    P3    IN DATE) AS
Local variable,Cursor,Collections Declare;
Begin
If statement
For Loop
Procedure Calling
DBMS_OUTPUT.Put_Line       *********{ -- This will not be used instead of this Fnd_File API will be used}
FND_FILE.PUT_LINE(Fnd_File.Log   ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output,'Message'||Variable Name);
Exception
When Other then
-Exception Statements;
End <Procedure Name>;
PL/SQL Procedure with Parameter:
================================
If we have any user defined Parameters then we have to register these parameters at the
time of Creating the Concurrent Program by selecting the Parameter button
enter the Seqno
 Parameter Name
 Value Set
Note : Token Field will be disabled.
Here First Parameter value will be passed to the first variable
Second Parameter will be passed to the second variable and so on........
When we are registering the report as C.P then only we required TOKEN field.
Because report builder bind variables may or may not be in the sequence that's why
we have to map with Token field.
Where as in Procedure variables position is fixed then TOKEN field will be disabled.

Oracle Apps Standards in Reports Registration:
==============================================
1)Define the Mandatroy parameter called P_CONC_REQUEST_ID
2)Call the Following User exit from the Before report Triggers
   SRW.USER_EXIT('FND SRWINIT');
3)Call another User Exit from After Report Trigger
   SRW.USR_EXIT('FND SRWEXIT');
User Exit:
==========
It is one of the Oracle  Reports 6i Built In program which will be used to stop the report execution and pass the control into some other 3GL and retrieves the data then complete the remaining report execution Process.

We have 5 User Exits in Oracle Applications:
FND SRWINIT
FND SRWEXIT
FND FLEXSQL
FND FLEXIDVAL
FND FORMATCURRENCY

FND SRWINIT : This User Exit we will initialize user Profile values we will call this
    user exit from Before Report Trigger.
FND SRWEXIT: This User Exit will frees the Memory which is Occupied by the User Profile
             values in the server. we will call this from After Report Trigger.
We have Five types of Report Triggers :
Before Parameter Form
After Parameter Form
Before Report Trigger
Between Pages
After Report Trigger
We are using Before Report trigger before data is retrieving  from data base system
will capture the user profiles data and as per that data will be retrieved from
database.
Once Output is generated we do not require the Information in the system for that
we are using after Report Trigger call the another User Exit.
P_CONC_REQUEST_ID : This is One of the mandatory parameter we have to define for every
        concurrent Program.when we submit C.P from SRS window which will generate the
        REQUEST_ID that request ID will be passed into this variable.
Reports parameter (value sets, default types,token)
Request group
Responsibility
user
SRS
user exits.
FND_USER
FND_USER_REPS

Purchasing   = functionality  500
      Imp tables Join

01243992026
CARD NO
NAME
CONTACT NO
REASON
12307RS

Value Set: Value set is nothing but list of values with validations which will be
used to to restrict the user without entering the invalid data in the Parameters

we will use value sets in two locations.
1)Concurrent Progam parameters
2)Flexfields
NONE:
----
We are not providing any LOV, we can apply some format conditions as per that
conditions user should enter the data

Notes: 1)Once we create the Value set we can not Delete if we would like to delete
 we have to release the value set from the all the concurrent program
  parameters then only we can delete.
  2)Value set name is case sensitive
  3)Once we create Value set we can use for multiple Program parameters.

Navigation:
-----------
System administrator => Application=>Validation=>set=>
Enter value set name
   format type
   max size
Select validation type = "None" to create None type of Value sets.

Independent:
------------

When we would like to provide list of values to the user then we will go for selection
of Independent value set.where we will provide LOV.
User must select the Value from the list otherwise values are not accepted.

Open the Value set form create value set by selecting the validation type=Independnent
Goto Values screen enter the value set name , Select Find Buttion
enter the values whatever we would like to display as LOV.
attach the value set to the Parameter.

Note:1)Once we enter the values we can not delete instead of that we can disable by
      selecting the Enabled check box
or Effective Dates.
Dependent value Set:
====================
This is another LOV which will be used to displays the
list of values which are depending on the previous parameter value.

Before going to create Dependent first we have to create Independent
then we have to create Dependent

First parameter will be Independent
Second parameter will be Dependent.

Note:Without Independent we can not create Dependent Value set.

Country IND
US
UK
City   Banglore Chennai Delhi Mumbai  Pune
       Chikago  California      Anderson
       London   Hungrant

1)We have to create Independent value set and enter the values.
2)Create Dependent value set attach independent and then enter values.


Job  Manager
 Developer
 Programmer

Position   Delivery Manager   Project manager Financce manager
  Software Developer Test Developer
  Trainee  Fresher
Navigation:
==========
1)Open the Value set form create Value set by selecting the validation type =Independent
2)Open the Values screen enter the VAlues .
3)Open the value set form enter Dependent value set by select validation type=Dependent
 Select the Button called Edit Information button enter the Independent value set
4)open the values form  enter the Dependent value set=>Find
  enter the values based on the Independent values.

Table Value set :
=================

Table value set will be used to displays the list of values from the
oracle apps base tables.
we have to give the table name and column name which will automatically
displays the values.

Note: If values are not stored in the database table then we have to
      go for Independent  value set.
      If values are there in the table then we will create table value
      set.

1.Open the value set form Select  validation type as table select the
   button called Edit Information enter table name and column name
   in the value field
2.Use where/Order By clause to implement Where/Order By clause.
3.Use Additional Columns field to displays extra columns for reference
  purpose.
4.Use the ID column to pass the internally other columns data
  for ex displaying username to the user and pass userID internally.
5.If multiple tables are required then enter the table names in the
  table name field with alias name and enter the Join Condition in the
  Where clause field.

6.If we know the table name we can find the Table application name from
  Application Developer responsibility
Application Developer => Application => Database => table
Query the records based on the table Name.

Note: If we are displaying additional Columns we are suppose to give the Alias Name

Translated Independent and Translated Dependent:
================================================
Both  value sets will work like Independent and Dependent value sets
will be used to displays the transalation values which will be enabled
if there is multilanguage implementation.

Special and Pair:
=================
Both Value sets will be used to displays the Flexfield data as LOV to
the User.

Multiorg :
=============
Use a single installation of any Oracle Applications product to
support any number of organizations, even if those organizations
use different sets of books.

BusinessGroup (HRMS=>Workstructure=>Organization=Description)
Set of Books  (GL=>Setup=>Financials=>Books=>Define)
Legal Entity
Operating Unit
Inventory Organization
Sub Inventory     (Inventory=.Setup=>organizations=>SubInventories)
Stock Locations   (Inventory=.Setup=>organizations=>Stock locatiors)
Items

Major Features
===============
1)Multiple Organizations in a Single Installation

2)Secure Access
    You can assign users to particular organizations. This ensures accurate
    transactions in the correct operating unit.

3)Multiple Organizations Reporting

You can set up your Oracle Applications implementation to allow
reporting across operating units by setting up the top reporting level.
You can run your reports at the set of books level, legal entity level, or
operating unit level

Business Group:
===============
The business group represents the highest level in the organization
structure, such as the consolidated enterprise, a major division, or an
operation company. The business group secures human resources
information. For example, when you request a list of employees, you
see all employees assigned to the business group of which your
organization is a part.

Set Of Books:
============
A financial reporting entity that uses a particular chart of accounts,
functional currency, and accounting calendar. Oracle General Ledger
secures transaction information (such as journal entries and balances)
by set of books. When you use Oracle General Ledger, you choose a
responsibility that specifies a set of books. You then see information for
that set of books only.


Legal Entity:
=============
A legal company for which you prepare fiscal or tax reports. You
assign tax identifiers and other legal entity information to this type of
organization.

Operating unit:
===============
An organization that uses Oracle Cash Management, Order
Management and Shipping Execution, Oracle Payables, Oracle
Purchasing, and Oracle Receivables. It may be a sales office, a division,
or a department. An operating unit is associated with a legal entity.
Information is secured by operating unit for these applications. Each
user sees information only for their operating unit.

Inventory Organization:
=======================
An organization for which you track inventory transactions and
balances, and/or an organization that manufactures or distributes
products. Examples include (but are not limited to) manufacturing
plants, warehouses, distribution centers, and sales offices. The
following applications secure information by inventory organization:
Oracle Inventory, Bills of Material, Engineering, Work in Process,
Master Scheduling/MRP, Capacity, and Purchasing receiving functions.

Subinventory:
=============
Which is another organization inside of the Inventory oganization will
be used to define the locations under these location items will be placed.


Multiorg Table:
===============
It is a table contains the data which is relted to multiple operating units
all the multiorg table names will be end with '_ALL'.

like  PO_HEADER_ALL
      PO_LINES_ALL
      AP_CHECKS_ALL  and so on

Note: In all these tables we will find one common column called "ORG_ID"
  This column will be populated internally by the system as per the User Operating
  Unit ID

Client_Info:
===========
It is one  the RDBMS vaiabel which contains the User Operating Unit value (ORG_ID)

Multiorg View:
=================
It is a view which is created based on the Multiorg table which contains the WHERE
clause WHERE Org_ID = :Client_Info.

Note: While development of RICE Components we are suppose to Use Multiorg Views
      not Multi Org Tables.

Because if we use Multiorg tables we will get all the operating units data
if we use multiorg view we will get the operating units data which is related for that
perticular user .


1)What is the Diff between ORG_ID and ORGANIZATION_ID?
Ans)ORG_ID              is at Operating Unit Level
    ORGANIZATION_ID     is at Inventory Organization level

2)Why the PO Receipt functionality will come at Inventory organization level?
Ans)user is creating receipt means indirectley he will be receiving the materials from
    suppliers.materials will be received at Gowdons ,Warehouses,manufcaturing plants
    all these organizations will come at Inventory Organization level.

3)how the System Will Identify user is working for so and so operating Unit?
Ans ) By using following Profile called
      MO:Operating Unit

4)What is Multiorg?
5)What is Client_info?
6)how to Implement Multiorg in Reports and at SQL prompt?
7)What is Business group, Legal Entity,Operating Unit,Inventory Organizations?
8)What are the Modules will come at operating Unit level?
9)What is the flow of Multiorg?
10)How to Identify the Multiorg Table?
11)Wat is the Diff between Multiorg Table and Multiorg View?
12)While Developing RICE Components we will use Multiorg Table or Multiorg View?
13)Why there is no _ALL for PO_VENDORS(11i),R12(AP_suppliers)
   and there is    _ALL for PO_VENDOR_SITES_ALL(11i)/AP_SUPPlIER_SITES?
Ans)Supplier is global
    Supplier site is Org Specific.
14)How will you findout Multiorg Succesfully Implemented?
Ans)

SELECT MULTI_ORG_FLAG ,RELEASE_NAME FROM FND_PRODUCT_GROUPS

Y - Succesfully Implemented
N - Not Implemented Succesfully.

Profile :
Profile is one of the changable option it will change the way of
application execution.
When User Log in to the application and select the the resp or Appl
system will automatically captures all the profile value as per the
profile values application will run.
Ex: If client have three Organizations 1)Hyd
      2)Ban
      3)Chn
If "hyd" users connect to the Application system will retrive the
data from database which is related to the Hyderabad branch.
If user is working for 'CHN' brnach then chennai branch setups or data
will be retrieved.
For every user we will assign the Profile value
Ex: Operation
Position - Profile Name
Profile Values
--------------
Manager
Supervisior
Clerk
Operator
Trainess
When we want assign any profile value we have four levels
we have to select any one of the level.
Profile Level Profile  Profile Value
------------- -------  --------------
User - OPERATIONS -  Print  - 10(This is for for Operations)
Responsibility  - 22Responsi -  Print  -  5(This is for 22resp users)
Application     - GL Applica -  Print  -  4(This is for GL App Users)
Site - ---        -  Print  -  2(This is for ALL Users)
Site : this is lowest level to assign the Profile values site values
are applicable for all the users.when we install Application by default
site level values will be assigned.
Application: These values are applicable for the users who are having
the access for the application. If user is eligible for both
application and site level values then application level value will
override the site level value.
Responsibility:We will select the responsibility name assign the value
which is applicable only for the users who are having the access for
specified responsibility.
Responsibility level value will override both application and site
level values.
User: This is highest level in the profile option.
we will select the user name and assign the profile value which is
applicable only for this user.
User level value will override all other profile level values.

Diff between Application and Responsibility:
============================================
Both are Group of Forms(Menu)
         Group of ConcurrentPrograms(Request Group)
         Group of Users (Data group)
But Application as per the Business functionality requirement
Responsibility will group as per the position requirement.
Some of the Imp Profile Names:
MO:Operating Unit
Hr:Business Groups
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
RESP_NAME and so on..........

Creation of New User:
1)Open the internet Explorer connect to Oracle Applications
2)Enter the         User Name  :OPERATIONS
   Password   :WELCOME
3)Select the Responsibility called 'System Administrator'
4)Open the User form.
 Security => User =>Define
5)Enter User Name and Password attach the Responsibilities whatever we required
for ex System Administrator
  Application Developer

6)Exit from the Appication
 File => Exit Oracle Applications
7)Connect to Oracle apps enter new user name password system will shows the message
like  'Password Has Expired'
8)Enter the New Password Press Ok Button
Short Cuts:
===========
1)To Query All the Records Press CTRL+F11
2)To Query Specific Records
     i)Open the Form
    ii)Press F11    (Form will comes into Query mode)
   iii)Enter Search Criteria in any field
    iV)Press CTRL+F11
3)To Close Form = F4
5)To Save the Records CTRL+S
   
Effective Date From and To:
===========================
In most of the Oracle Application forms we will find two field like
Effective Date From
Effective Date To

In some of the forms once we create records and save. We can not delete from database
that time we can go for Disable/Enable the record by using these two fields

Finding Table NameS/Column Names:
=================================

1)Help => Record History which will shows the Table Name
2)Help Menu=>Diagnastics=>Examine=>Enter the Password(APPS)=>We can find the Column Name
WHO Columns:
=============
WHO Column Will be used to find out the History of the record
we can find from front End Also
Help=>Record History

CREATED_BY      - Which User has created the Record(Userid)
CREATION_DATE   - at what time user has created (SYSDATE)
LAST_UPATED_BY   -Which User has updated recentley(UserID)
LAST_UPDATE_DATE -at what time user has Updated (SYSDATE)
LAST_LOGON_DATE - At what time user last Login Time

Find the Login UserName:
==========================
Help Menu=>About Oracle Applications

 SIMPLE CONTROL FILE USING SINGLE DATA FILE

1.create table in scott and give grant to apps then create synonym in app

 create table emp3(empno number(10),ename varchar2(10),job varchar2(10),sal number(10,2))

SQL> /

Table created.

SQL> grant all on emp3 to apps;

Grant succeeded.

SQL> conn apps/apps@prod
Connected.
SQL> create public synonym emp3 for scott.emp3;

Synonym created.

SQL> desc emp3;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                          NUMBER(10)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(10)
 SAL                                                            NUMBER(10,2)

2.data file

 3.control file
4.RUN CMD THEN GIVE COMMAND AS
D:\ORACLE\PRODDB\8.1.7\BIN> SQLLDR APPS/APPS@PROD
CONTROL =D:\DATA\EMP3.CTL

5.GOTO SQL PROMPT THEN VIEW THE OUT PUT:

SQL> SELECT * FROM EMP3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       100 'smith'    'mgr'            7000
       200 'ramu'     'clerk'          4000
       300 'raju'     'act'            6000

SQL>

          Multiple Data Files Into Single Database  table using single control file

1.Create second  data file:
         111,'RAVI','MGR',7000
         222,'KIRAN','CLERK',4000
         333,'CHANDRA','ASTMGR',10000

save this as EMP4.DAT  in same directory


2.Goto control file then assign this file as second infile to control file
   
 controlfile:


load data
infile 'd:\data\emp3.dat'
infile 'd:\data\EMP4.dat'
insert into table emp3
fields terminated by ','
(empno
,ename
,job
,sal
)

3.Again run cmd  as above

4.goto backend view data

   SQL> select * from emp3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       111 'RAVI'     'MGR'                    7000
       222 'KIRAN'    'CLERK'                4000
       333 'CHANDRA'  'ASTMGR'         10000
       100 'smith'    'mgr'                      7000
       200 'ramu'     'clerk'                    4000
       300 'raju'     'act'                         6000

6 rows selected.
Control file using sql functions

1) Assign the sql functions to the columns in Control file

load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'  
insert
into table emp3
(
empno   "S1.NEXTVAL",
ename    POSITION(4:7)  "UPPER(:ENAME)",
job       POSITION(8:10)  "INITCAP(:JOB)",
sal        POSITION(11:14) "NVL(:SAL*10,0)"
)
into table emp4 when projno!=' '
(  
empno   "S1.NEXTVAL",
ename    POSITION(4:7)  "UPPER(:ENAME)",
job        POSITION(8:10)   "INITCAP(:JOB)",
sal        POSITION(11:14)  "NVL(:SAL*10,0)",
projno   POSITION(15:17)
)
2
AGAIN RUN CMD:

2. THEN VIEW THE DATA  IN BACK END AS:

SQL> SELECT * FROM EMP3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       192 RAVI       Mgr             70000
       193 RAJU       Mgr             40000
       194 CHAN       Mgr             10000
       186 RAVI       Mgr             70000
       187 RAJU       Mgr             40000
       188 CHAN       Mgr             10000

6 rows selected.

SQL> SELECT * FROM EMP4;

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       195 RAVI       Mgr             70000        120
       196 RAJU       Mgr             40000        121
       197 CHAN       Mgr             10000        122
       189 RAVI       Mgr             70000        120
       190 RAJU       Mgr             40000        121
       191 CHAN       Mgr             10000        122

6 rows selected.
Using where clause in control file to update data into multiple tables
Multiple data files and Multiple tables


1.Create 2 tables and 2 data files
   first is emp3 which is already created
   Now created second table
     CREATE TABLE EMP4 AS SELECT * FROM  EMP3 WHERE 1=2
 
        1.1ALTER TABLE EMP4 ADD PROJNO NUMBER;                          /*for additing  additional column for secondtable */

        SQL> DESC EMP4;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------------
 EMPNO                                                          NUMBER(10)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(10)
 SAL                                                            NUMBER(10,2)
 PROJNO                                                         NUMBER


SQL> GRANT ALL ON EMP4 TO APPS;

Grant succeeded.

SQL> CONN APPS/APPS@PROD
Connected.
SQL> CREATE PUBLIC SYNONYM EMP4 FOR SCOTT.EMP4;

Synonym created.

2.create datafile as emp3

111RAVIMGR7000120
222RAJUMGR4000121
333CHANMGR1000122

3.create data file as emp4

111RAVIMGR7000120
222RAJUMGR4000121
333CHANMGR1000122

4.create control file as


load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'
insert
into table emp3
(
empno  POSITION(1:3),
ename   POSITION(4:7),
job       POSITION(8:10),
sal        POSITION(11:14)
)
into table emp4 when projno!=' '
(empno  POSITION(1:3),
ename   POSITION(4:7),
job        POSITION(8:10),
sal        POSITION(11:14),
projno   POSITION(15:17)
)


save;

5.run cmd as usual


6.view the output in backend


SQL> select * from emp3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       111 RAVI       MGR              7000
       222 RAJU       MGR              4000
       333 CHAN       MGR              1000
       111 RAVI       MGR              7000
       222 RAJU       MGR              4000
       333 CHAN       MGR              1000

6 rows selected.

SQL> select * from emp4;

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       111 RAVI       MGR              7000        120
       222 RAJU       MGR              4000        121
       333 CHAN       MGR              1000        122
       111 RAVI       MGR              7000        120
       222 RAJU       MGR              4000        121
       333 CHAN       MGR              1000        122

6 rows selected.


Using commit in control files for each 10 records


1 .we will create a sequence as s1


SQL> CONN SCOTT/TIGER@PROD
Connected.
SQL> CREATE SEQUENCE S1 INCREMENT  BY 1 START WITH 100;

Sequence created.

SQL> conn apps/apps@prod
Connected.
2.then we will create control file as


load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'
insert
into table emp3
(
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job       POSITION(8:10),
sal        POSITION(11:14)
)
into table emp4 when projno!=' '
(  
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job        POSITION(8:10),
sal        POSITION(11:14),
projno   POSITION(15:17)
)
 
then save;

4.then run as usual.




5 .view the data from backend as
SQL> /

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       106 RAVI       MGR              7000
       107 RAJU       MGR              4000
       108 CHAN       MGR              1000
       100 RAVI       MGR              7000
       101 RAJU       MGR              4000
       102 CHAN       MGR              1000

6 rows selected.

SQL> ed
Wrote file afiedt.buf

  1* select * from emp4
SQL> /

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       109 RAVI       MGR              7000        120
       110 RAJU       MGR              4000        121
       111 CHAN       MGR              1000        122
       103 RAVI       MGR              7000        120
       104 RAJU       MGR              4000        121
       105 CHAN       MGR              1000        122

6 rows selected

  To Create Commit Statement in cmd Prompt

                               

GOTO RUN CMD AND GIVE COMMAND AS  TO FOR EVERY 2 RECORDS

 TO SKIP RECORDS FROM LOADING USING SKIP KEYWORDS  IN CONTROL FILE
GO TO RUN CMD:THEN GIVE THE COMMAND AS:

 Goto backend and view the data:

SQL> SELECT * FROM EMP3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       148 CHAN       MGR              1000

                                           
TO SKIP COLUMNS IN CONTROL FILES USING FILLER KEYWORD
Use the FILLER keyword in control files as:

load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'  
insert
into table emp3
(
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job       filler,
sal        POSITION(11:14)
)
into table emp4 when projno!=' '
(  
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job        POSITION(8:10),
sal        POSITION(11:14),
projno   POSITION(15:17)
)

Then goto run cmd give the command as usual:
Then view the data in backend :

SQL> select * from emp3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       150   RAVI                     7000
       151  RAJU                                 4000
       152  CHAN                                1000
       156  RAVI                                  7000
       157  RAJU                                 4000
       158  CHAN                                1000

6 rows selected.

SQL> select * from emp4;

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       153 RAVI       MGR              7000        120
       154 RAJU      MGR              4000        121
       155 CHAN      MGR              1000        122
       159 RAVI       MGR              1000        120
       160 RAJU      MGR               4000        121
       161 CHAN      MGR              1000        122

6 rows selected.

OVERVIEW OF FLEXFIELD CONCEPTS

A flexfield is a field made up of sub-fields, or segments.  A flexfield appears on your form as a pop-up window that contains a prompt for each segment.   Each segment has a name and a set of valid values.  There are two types of flexfields: key flexfields and descriptive flexfields.

BASIC FLEXFIELD CONCEPTS

Segment - A segment is a single sub-field within a flexfield.  You define the appearance and meaning of individual segments when customizing a flexfield.  A segment is represented in your database as a single table column.

For a key flexfield, a segment usually describes a particular characteristic of the entity identified by the flexfield.  For example, you can have a key flex field that stores part numbers.  The key flexfield can contain the part number PAD-YEL-NR-8 1/2x14, which represents a yellow, narrow ruled, 8 1/2" x 14" note pad.  Each section in the part number, separated by a hyphen, describes a characteristic of the part.  The first segment describes the object, a note pad, the second segment describes the color of the object, yellow, and so on.

Note that we also refer to the fields in a descriptive flexfield pop-up window as segments even though they do not necessarily make up meaningful codes like the segments in key flexfields.  However, they do often describe a particular characteristic of the entity identified elsewhere on the form you are using.

Values, Validation and Value Sets - Your end user enters a segment value into a segment while using an application.  Generally, the flexfield validates each segment against a set of valid values (a "value set") that are usually predefined.  To "validate a segment" means that the flexfield compares the value a user enters in the segment against the values in the value set for that segment.

You can set up your flexfield so that it automatically validates segment values your end user enters against a table of valid values.  If your end user enters an invalid segment value, a list of valid values appears automatically so that the user can choose a valid value.  You can think of a value set as a "container" for your values.  You choose what types of values can fit into your value set:  their length, format, and so on.

A segment is usually validated, and usually each segment in a given flexfield uses a different value set.  You can assign a single value set to more than one segment, and you can even share value sets among different flexfields.  For most value sets, when you enter values into a flexfield segment, you can enter only values that already exist in the value set assigned to the segment.

Structure - A flexfield structure is a specific configuration of segments.  If you add or remove segments, or rearrange the order of segments in a flexfield, you get a different structure.

You can define multiple segment structures for the same flexfield (if that flexfield has been built to support more than one structure).  Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data.  Both key and descriptive flexfields may allow more than one structure.

In some applications, different users may need a different arrangement of the segments in a flexfield (key or descriptive).  Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.

Your Oracle General Ledger application, for example, provides different Accounting Flexfield (Chart of Accounts) structures for users of different sets of books.  The Oracle General Ledger application determines which flexfield structure to use based on the value of the GL Set of Books Name user profile option.