Query to Get the Account Flex field Structure

SELECT DISTINCT sob.name Ledger_Name ,
  sob.set_of_books_id,
  sob.chart_of_accounts_id coa_id ,
  fifst.id_flex_structure_name struct_name ,
  ifs.segment_name ,
  ifs.application_column_name column_name ,
  sav1.attribute_value BALANCING ,
  sav2.attribute_value COST_CENTER ,
  sav3.attribute_value NATURAL_ACCOUNT ,
  sav4.attribute_value INTERCOMPANY ,
  sav5.attribute_value SECONDARY_TRACKING ,
  sav6.attribute_value GLOBAL ,
  ffvs.flex_value_set_name ,
  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs ,
  fnd_id_flex_structures_tl fifst ,
  fnd_segment_attribute_values sav1 ,
  fnd_segment_attribute_values sav2 ,
  fnd_segment_attribute_values sav3 ,
  fnd_segment_attribute_values sav4 ,
  fnd_segment_attribute_values sav5 ,
  fnd_segment_attribute_values sav6 ,
  fnd_id_flex_segments ifs ,
  fnd_flex_value_sets ffvs ,
  gl_sets_of_books sob
WHERE 1                          =1
AND fifs.id_flex_code            = 'GL#'
AND fifs.application_id          = fifst.application_id
AND fifs.id_flex_code            = fifst.id_flex_code
AND fifs.id_flex_num             = fifst.id_flex_num
AND fifs.application_id          = ifs.application_id
AND fifs.id_flex_code            = ifs.id_flex_code
AND fifs.id_flex_num             = ifs.id_flex_num
AND sav1.application_id          = ifs.application_id
AND sav1.id_flex_code            = ifs.id_flex_code
AND sav1.id_flex_num             = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id          = ifs.application_id
AND sav2.id_flex_code            = ifs.id_flex_code
AND sav2.id_flex_num             = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id          = ifs.application_id
AND sav3.id_flex_code            = ifs.id_flex_code
AND sav3.id_flex_num             = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id          = ifs.application_id
AND sav4.id_flex_code            = ifs.id_flex_code
AND sav4.id_flex_num             = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id          = ifs.application_id
AND sav5.id_flex_code            = ifs.id_flex_code
AND sav5.id_flex_num             = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id          = ifs.application_id
AND sav6.id_flex_code            = ifs.id_flex_code
AND sav6.id_flex_num             = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type  = 'GL_BALANCING'
AND sav2.segment_attribute_type  = 'FA_COST_CTR'
AND sav3.segment_attribute_type  = 'GL_ACCOUNT'
AND sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
AND sav5.segment_attribute_type  = 'GL_SECONDARY_TRACKING'
AND sav6.segment_attribute_type  = 'GL_GLOBAL'
AND ifs.id_flex_num              = sob.chart_of_accounts_id
AND ifs.flex_value_set_id        = ffvs.flex_value_set_id;

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name,
  ffv.application_table_name,
  ffv.title,
  ap.application_name ,
  ffc.descriptive_flex_context_code ,
  ffc.descriptive_flex_context_name,
  ffc.description ,
  ffc.enabled_flag ,
  att.column_seq_num ,
  att.form_left_prompt ,
  att.application_column_name ,
  fvs.flex_value_set_name ,
  att.display_flag ,
  att.enabled_flag ,
  att.required_flag
FROM fnd_descriptive_flexs_vl ffv,
  fnd_descr_flex_contexts_vl ffc,
  fnd_descr_flex_col_usage_vl att,
  fnd_flex_value_sets fvs,
  fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ap.application_name              = 'Order Management'
AND ffv.application_table_name       ='OE_ORDER_HEADERS_ALL';

Flex Fields and Form Personalization in Oracle Apps

Flex fields:

• A flex field to capture information about your organization. Flexible has a flexible structure for storing key information.
• Like a company, cost, center, and account.
• They also give you a highly adaptable structure for storing customized information in an Oracle application.
•  A flex field is a field made up of subfields, or segments. There are two types of flex fields.

1. Key flex field
2. Descriptive flex field

Key flex field:

• A key flex field is made up of segments, where each segment has both a value and a meaning, which appears on your form as a normal text field with an appropriate prompt.
• An example of a key flex field is the accounting flex field. This flex field can always be customized to have as many segments Like the company, department, cost center, account, etc.

Descriptive flex field:

• DFF adds additional fields to the form to track additional information needed by the business that would not be captured by the standard form.
• Dff can be context-sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

Dff concepts:

• DFF has two different types of segments, global and context-sensitive.
• A global segment is a segment that always appears in the DFF pop-up window.
• A context-sensitive segment is a segment that may or may not appear depending on what other information is Present in your form.
•  A DFF uses columns that are added to a database table. A DFF requires one column for each possible segment and one additional column in which to store structure information. The dff is usually named Attribute n where n is a number. 

Difference between kff and dff:

• Kff captures mandatory business information like company department, account, etc.
• Dff captures additional business information like the name of the company’s MD etc.
• Kff is mandatory.
• DFF is optional.
• Kff displays like text items.
• Dff displays like [].
• Kff data are stored in the segments.
• Dff store in the attribute.
• 30 segment columns are reserved for kff data.
• 15 attribute columns are reserved for the dff data.

Benefits of flex fields:

•  Customize your application to conform to your current business practice for accounting codes, product codes, and other codes.
•  Customize your application to capture data that would not otherwise be tracked by your application.
•  Customize data fields to meet your business needs without programming.
•  Query intelligent fields for very specific information.

Tables in Dff:

       Select * from fnd_descriptive_flexs;

       Select * from fnd_descriptive_flexs 

        where application_table_name=’po_requisition_headers_all;

Key flex fields:

   A few Key Flex fields in different Oracle modules are:

     Module: GL

1.       Accounting

           Module: HRMS

1.  Job

2.  Position

3.  Grade

4.  Personal analysis

Module: Inventory

          Account aliases

           Item catalogs

           Item categories

           Sales orders

           Stock Location

           System items

Module: AR

         Sales tax location

         Territory

Main tables:

1. Fnd_id_flexs:

•  This table captures the information on all the key flex fields. The main columns in the tables are:

                 Application_id - Column consists of application id.

                 Id_flex_code - kff code (like ‘gl’,’ar’,etc)

                 Id_flex_name – kff name (‘accounting flexfield’,’category field’,etc)

                 Application_table_name

               

    2. Fnd_id_flex_structures:

• This table stores structure information about key flex fields.

            Application_id- module code

            Id_flex_code- code of kff

            Id_flex_num- number of a structure

    3. Fnd_id_flex_segments:

• It captures the information of segments.

            Application_id

            Id_flex_code

            Id_flex_num

            Seg_num

            Flex_value_set_id

   

   4. Fnd_flex_value_sets:

• This table captures the information on each segment's value set.

          Flex_value_set_id

    5. Fnd_fles_values:

• This table captures the information on each value code of a value set of a segment.

            Flex_value_set_id

            Flex_value_id

   6. Fnd_flex_values_tl:

• This table captures the information of each value description of a value set of a segment.

             Flex_value_id

Form personalization:

• Form personalization is a feature in oracle apps that allows us to change the behavior of form-based screens, including changing properties, executing built, displaying messages, and adding menu entries.
• It can be moved easily through and loaded from one instance to another, and it can be restricted at the site/user/responsibility level.

Uses:

• Personalization is stored in tables rather than files. 
• Can be moved easily through FNDLOAD from one instance to another.
•  Can be restricted at the site/responsibility/user level.
• Easy to disable/enable with the click of a button.
• Can be restricted to function or form.
•  Can be applied to new responsibilities/users easily.

What can be done through form personalization?

• Zoom from one form to another.
• Pass data from one form to another through global variables.
• Enable/disable/hide fields dynamically.
• Launch the URL directly from the oracle form.
• Call plsql procedure.

Meaning of special fields in form personalization:

• Menu (1-15) will be in tools.
• Populate tools menu (special 1-15)
• Populate reports menu (special 16-30)
• Populate action menu (special 31-45)
•  It is recommended to use the menu before tools.

To open form personalization go to help->diagnostics->custom code->personalize

Components used in form personalization:

       Trigger event:

       Processing mode: query mode or not in query mode or both.

       Context: at what level like user or responsibility level.

       Menu: This is used to call one form from another. It is used to implement the zoom functionality.

      Built-in: