Search This Blog

Thursday, April 11, 2013

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.
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:
01select  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
11from    FND_ID_FLEXS FIF    ,
12        FND_ID_FLEX_STRUCTURES FIFS ,
13        FND_ID_FLEX_SEGMENTS FIFSE
14where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
15and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
16and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
17and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
18and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
19and     FIF.ID_FLEX_CODE LIKE 'GL#'
20and     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

No comments:

Post a Comment