Key Flex Field KFF Query and Registration

KFF 

are used to capture mandatory or Key Business information of the Organization. Each Key Flex Field is having its won base Table.

A key flexfield is a field made up of segments, each of which has both a value and a meaning. You can think of a key flexfield as an “intelligent” field that your business can use to store information represented as codes.

Most organizations use codes to identify general ledger accounts, part numbers, and other business entities. Each segment in the code represents a characteristic of the entity. A combination of segment values, also known as a key flexfield code combination, uniquely describes a business entity stored in a key flexfield.

The organization decides the following four basic information for each key flexfield:

1. How many segments an entity has?
2. What each segment means?
3. What values each segment can have?
4. What each segment value means?

Few Key Flexfields in different oracle modules are:

Module: GL

1. Accounting

Module: HRMS

1. Job
2. Position
3. Grade
4. Personal Analysis   

Module: Inventory

1. Account Aliases
2. Item Catalogs
3. Item Categories
4. Sales Orders
5. Stock Locations
6. System Items

Module: Fixed Assets

1. Asset
2. Category
3. Location

Module: AR

1. Sales Tax Location
2. Territory

Note:

Flexfields consists of Structures > Structures consists of Segments > Segments consists of Value Set >Value Set consists of Parameters.

Main Tables:

FND_ID_FLEXS:

This table captures the information of all the Key FlexFields. The main columns in this table are:

• APPLICATION_ID ‐ Column consists of Application ID
• ID_FLEX_CODE ‐ Column KFF Code (like ‘GL#’, ‘AR#’ etc.)
• ID_FLEX_NAME  -  KFF Name (like ‘Accounting Flexfield’, ‘Category Flexfield’..etc.)
• APPLICATION_TABLE_NAME – Name of combination table (like ‘GL_CODE_COMBINATIONS’ , ‘FA_LOCATIONS’ etc.)

FND_ID_FLEX_STRUCTURES:

This table stores structure information about key Flexfields. Each Structure is uniquely identified by

• APPLICATION_ID – Module Code
• ID_FLEX_CODE  – Code of KFF
• ID_FLEX_NUM – Number of a Structure

FND_ID_FLEX_SEGMENTS:

It captures the information of Segments. Each Segment is Uniquely identified by

• APPLICATION_ID – Module Code
• ID_FLEX_CODE – Key Flexfield code
• ID_FLEX_NUM – Key flexfield structure number
• SEG_NUM – Segment number
• FLEX_VALUE_SET_ID – Flexfield value set identifier

FND_FLEX_VALUE_SETS:

This table captures the information of each Segment’s Value Set. Each Value Set is Uniquely identified by FLEX_VALUE_SET_ID as Foreign Key of FND_ID_FLEX_SEGMENTS Table.

FND_FLEX_VALUES:

This table captures the information each Value codes of a Value Set of a Segment. Each Value Code is uniquely identified by

• FLEX_VALUE_SET_ID
• FLEX_VALUE_ID

FND_FLEX_VALUES_TL:

This table captures the information of each Value Description of a Value Set of a Segment. Each Value Description is uniquely identified by FLEX_VALUE_ID.

Query:

01	select  FIF.APPLICATION_ID  ,

02	FIF.ID_FLEX_CODE    ,

03	FIF.ID_FLEX_NAME    ,

04	FIF.APPLICATION_TABLE_NAME ,

05	FIF.DESCRIPTION     ,

06	FIFS.ID_FLEX_NUM    ,

07	FIFS.ID_FLEX_STRUCTURE_CODE  ,

08	FIFSE.SEGMENT_NAME,

09	FIFSE.SEGMENT_NUM,

10	FIFSE.FLEX_VALUE_SET_ID

11	from    FND_ID_FLEXS FIF    ,

12	FND_ID_FLEX_STRUCTURES FIFS ,

13	FND_ID_FLEX_SEGMENTS FIFSE

14	where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID

15	and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE

16	and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID

17	and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE

18	and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM

19	and     FIF.ID_FLEX_CODE LIKE 'GL#'

20	and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

Steps required to register a New Key Flexfield (KFF):

1] Create a KFF Table in Module Specific Schema.

2] Create a Public Synonym in APPS Schema.

3] Register the Table with AOL Module.

Go to Application Developer > Functions > Application > Database > Table

We can also register a Table using Application DBA Data Definitions (AD_DD) Package from the Back End.

procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables.

4] Register the KFF with AOL Module.

Go to Application Developer > FlexField > Key > Register