What is an AuditTrail?

An AuditTrail is one of functionality for retaining a history of changes to data. What ,who and when can be identified on a particular table or column if the functionality is enabled.

When you enter or update data in your forms, you change the database tables underlying those forms. An audit trail tracks which row in the database was updated at what time, and which user was logged in using the associated form(s).

If you are seeking auditing ability to track changes on a particular table of Oracle this post might helpful to you.

Enabling the Functionality of AuditTrail

You can turn AuditTrail on or off (Yes or No). Normally the default setting is No (Off). When you enter or update data in your forms, you change the database tables underlying the forms you see and use. AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s). Also..

• Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
• AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
• All the forms that support AuditTrail are referred to as an audit set. You should also note not all forms may be enabled to support AuditTrail.
• To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developer responsibility.
• Users cannot see nor change this profile option.
• This profile option is visible and updatable at the site and application levels.

 

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

Setting Up AuditTrail(>11i )

You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

The steps for setting up AuditTrail include:

Yuu need to verify Select Privileges on SYS.DBA_TABLES

Have your database administrator grant SELECT privileges on SYS.DBA_TABLES to the APPLSYS account. Normally, this step would Normally taken care during the installation of Oracle.

Define Audit Groups

This is very very important.These are groups of tables and columns, where you do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables see at the end).

A given table can belong to more than one audit group. If so, the table is audited according to the highest “state” of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.

Navigation: Security -> AuditTrail -> Groups

Define Audit Installations

You choose the registered Oracle IDs at your site that you want to audit. This allows you to audit across multiple application installations. When a table is added to an audit group, auditing will automatically be enabled for all installations of the table for which audit is enabled.

Navigation: Security -> AuditTrail -> Install

Run the Audit Trail Update Tables Report to Enable Auditing

Your AuditTrail definitions (and auditing) do not take effect until you run the Audit Trail Update Tables Report. If you change any of your definitions later, you must rerun this program. You run the Audit Trail Update Tables Report from the standard submission (Submit Reports) form.

Audit Trail Update Tables Report

This program creates database triggers on the tables in your audit groups for your installations. It also creates shadow tables, one for each audited table, to contain the audit information. If you have changed your audit definitions or disabled auditing for an audit group, the program drops or modifies the auditing triggers and shadow tables appropriately.

The program also builds special views you can use to retrieve your audit data for reporting.

You can check SQL*Plus to see if the Shadow Tables have been created or not. Shadow Table name is the same 26 Characters of the Table being audited followed by a suffix of “_A” ,suffix of “_AI” for Insert Triggers, “_AU” for Update triggers , “_AD” for Delete Triggers,suffix of “_AIP” for Insert Procedures “_AUP” for Update Procedures and “_ADP” for Delete Procedures.

AuditTrail Limitations

These are limitation of AuditTrail:

• Your table should consist of maximum 240 columns
• You cann’t use the column for audit trail whose data type is LONG, RAW, or LONG RAW
• Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically.
• Once you have added a column to an audit group, you cannot remove it.
• AuditTrail requires two database connections. If your operating platform does not automatically support two database connections
  (e.g., VMS or MPE/XL), then add the environment variable ‘FDATDB=<database connect string>’ to your environment file.
• Because the structure of the audited table may change between product versions, AuditTrail does NOT support upgrading existing shadow tables or audited data. Before an upgrade, you should archive the shadow tables and perform all necessary reporting on the audited data.
• Oracle recommended Disabling AuditTrail feature Prior to Upgrade to higher version.
• If your plan is to use some auditTrail on table which name is bit longer(> 26 characters), you can’t achieve this. As there is Bug(#3872242
  ) reported by Oracle that restrict you from this, as AuditTrail Update Tables Errors out on audited tables that are > 26 characters.

Sometime back we found while enabling this on these tables.

• PO_REQUISITION_HEADERS_ALL
• PO_REQUISITION_LINES_ALL
• PO_REQ_DISTRIBUTIONS_ALL

What are the primary Auditing Tables?

• FND_AUDIT_COLUMNS
• FND_AUDIT_GROUPS
• FND_AUDIT_SCHEMAS
• FND_AUDIT_TABLES

Client Dilema : HOW TO ENABLE AUDITING AT “FORMS” AND “USER” LEVEL TOGETHER

Customer has set the profile “Sign-on: Audit Level” to FORMS to collect information about the forms sessions at a particular time .
By default the value for this profile was USER. Since he has set the value to Forms can he collect the User related information

Yes you can …
If the profile “Sign-on: Audit Level” is set to FORMS then it will collect Form sessions information in addition to user session information.
So you will get information related to User session as well as forms session. There is no harm in setting this profile value to FORMS.

In Release 12, profile options can now be defined to have a hierarchy type of server-responsibility.

1. Site
2. Server + Responsibility
3. User

What does it mean?

…Simply you can understand like ,when you are choosing this type, the profile can be set at Site level, User level, or a new combination Server+Responsibility. This hierarchy type is set in the profile definition, and cannot be changed at runtime.

At the Server+Responsibility level, both the server and responsibility are considered as a pair. Profile values can be set for combinations of a specific server with a specific responsibility, or for only a server or responsibility with a default for the other.

Oracle documentation clearly states “at runtime, the profile value is determined by looking for the value set at the most specific level”

1. A value set at the user level over-rides all others
2. If no user level value is set, look for value matching the current server and responsibility.
3. If no value is set matching both the current server and responsibility, look for a value matching the responsibility with a default server.
4. If no value is found there, look next for a value matching the server with a default responsibility.
5. Finally, if no value is set at any lower level, look for a site level value.

You can also do this in the hierarchy type can be chosen on the Profile Definition page. On this page, you can see that a type of “Server+Responsibility” is chosen. Once this hierarchy type is chosen, the profile can be made visible and updatable at the Site, Server+Responsibility, and User level. In this example, the profile could be set for Site or Server+Responsibility, but not User.

I have already posted note on FNDLOAD utility for doing movement of application setup data from one database to the other.There are instances when we need to move some AOL and seeded data from one Environment to another Environment, thus at that time it is necessary to understand what are different options Available within the Oracle. Here is extensive list for some of the objects , that should be migrated by FNDLOAD.

What is Descriptive Flexfield Context Synchronization

Descriptive Flexfield Context Synchronization allows the runtime descriptive flexfield (DFF) context to be determined from a current reference field value for the entity instance (current record) instead of a previous value of the reference field

How/where this can be actioned?

Typically as per AOL documentation it is enable by providing new check box “Synchronize with Reference Field” on the Descriptive Flexfields
Segments form (FNDFFMDC)

What is meant by Checking and unchecking

• Value of “Yes” means context is always synchronized with reference field value.
• Value of “No” denotes that context will be defaulted from reference field value, the first time the record is created and the DFF is touched. Thereafter, context would never be synchronized by the flex engine.Context prompt treated differently by DFF view generator.

Reference:

• Metalink Note:455659.1
• Refer ATG Schan’s Blog for more details for DFF context Synchronization

Implementing DFF in Form consist of two steps Process. The first steps consist of designing the table structure and registration and step 2 consist of enabling in the form.

Steps 1:

• Define DFF fields in the custom tables

ATTRIBUTE1…ATTRIBUTE15 and ATTRIBUTE_CATEGORY are the normal fields used for DFF

• Register the table with Oracle AOL
• Register the DFF with Oracle AOL

For this you have to login into application with :

Application Developer -> Flex Field -> Descriptive -> Register

Then you should specify the name of the DFF (Say BTL_AR_DFF) and specify the associated table.

Step 2:

• Create DFF fields in the custom form

1. Create a form based on the custom table

• Copy TEMPLATE form has example descriptive flexfield which we can use
• Use the TEXT_ITEM_DESC_FLEX property class
• Attach ENABLE_LIST_LAMP_LOV for the field

2. Create a non-base table text item in the block with property class TEXT_ITEM_DESC_FLEX
3. Make sure that DFF is mapped to fields (ATTRIBUTE) of the table and is enabled.

• Next is to call Flexfield Routines to add DFF built-ins in the form to invoke DFF.
  • This consist of these steps:

1. Write, a form level trigger WHEN-NEW-FORM-INSTANCE to invoke the DFF as

FND_DESCR_FLEX.DEFINE(
Block => ‘Name of the DFF block’,
Field => ‘BTL_DFF’,
Appl_short_name => ‘AR’,
Desc_flex_name => ‘BTL_AR_DFF’,
Title => ‘BPL AR More Info’ );

2. Write, block level triggers PRE-QUERY and POST-QUERY as

• FND_FLEX.EVENT(’PRE-QUERY’);
• FND_FLEX.EVENT(’POST-QUERY’);
• FND_FLEX.EVENT(’Pre-Query’);
• FND_FLEX.EVENT(’When-Validate-Record’);
• FND_FLEX.EVENT(’Post-Query’);
• FND_FLEX.EVENT(’When-New-Item-Instance’);
• FND_FLEX.EVENT(’Pre-Insert’);
• FND_FLEX.EVENT(’When-Validate-Item’);
• FND_FLEX.EVENT(’Pre-Update ‘);
  

If any one want to use Flexfield in report, there is minimum steps you have to follow to make things Functional. These are the standard steps as per AOL guide.

Step 1 Define your Before Report Trigger

Here you suppose to call FND SRWINIT from the Before Report Trigger like :

SRW.USER_EXIT(’FND SRWINIT’);

This user exit sets up information for use by flexfields, user profiles, the concurrent manager, and other Oracle Applications features as discussed in one of previous post.

Step 2 Define your After Report Trigger

Next, is you need to call FND SRWEXIT from the After Report Trigger like :

SRW.USER_EXIT(’FND SRWEXIT’);

Step 3 Define your required parameters

In this step you need to define the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

Lexical Parameters

You must always create the P_CONC_REQUEST_ID lexical parameter. “FND SRWINIT” uses this parameter to retrieve information about the concurrent request that started this report.

The P_FLEXDATA parameter holds the SELECT fragment of the SQL query. The initial value is used to check the validity of a query containing this parameter and to determine the width of the column as specified by the column alias. Its initial value is some string that contains columns with a cumulative width more than the expected width required to hold the data. Make sure the width of this column is sufficient. If there are total 30 segments in the table then the safest initial value will be:

(SEGMENT1||’\n’||SEGMENT2||’\n’||SEGMENT3 … SEGMENT30)
You determine the width by determining the length of that string. That length is roughly the number of characters in the table alias plus the length of the column name, times the number of segments your code combinations table contains, times the number of structures you expect, plus more for delimiter characters as shown in the string above.

Step 4 Define your rest other parameters

You define the rest of the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

Step 5 Call FND FLEXSQL from your Before Report Trigger to populate P_FLEXDATA

Next, given that you want to display flexfield information like concatenated values and descriptions, and arrange them in order, you make one call to FND FLEXSQL from the Before Report Trigger specifying the lexical parameters. This call changes the value of the lexical parameter P_FLEXDATA at runtime to the SQL fragment that selects all flexfields value data. For example, the parameter changes to (SEGMENT1||’\n’||SEGMENT2||’\n’||SEGMENT3||’\n’||SEGMENT4).

When you incorporate this lexical parameter into the SELECT clause of a query, it enables the query to return the concatenated segment values that are needed as input to other AOL user exits. These exits then retrieve the actual flexfield information for display purposes.

Here is an example FND FLEXSQL call. Notice that the arguments are very similar to other flexfield routine calls; CODE= and NUM= designate the key flexfield and its structure, respectively. For a report on a different key flexfield (such as the System Items flexfield), you would use a different CODE and NUM.

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT(’FND FLEXSQL
CODE=”GL#”
NUM=”:P_STRUCT_NUM”
APPL_SHORT_NAME=”SQLGL”
OUTPUT=”:P_FLEXDATA”
MODE=”SELECT”
DISPLAY=”ALL”‘);

You should always reference any source column/parameter that is used as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by “SRW.REFERENCE” call as shown above.

Step 6 Call FND FLEXSQL from your Before Report Trigger to populate other parameters
What you suppose ti do here is just need to call FND FLEXSQL once for every lexical parameter.

Step 7 Define your report query or queries
Define your report query Q_1:

SELECT &P_FLEXDATA C_FLEXDATA
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN
= &P_STRUCT_NUM

The query fetches the data required to be used as input for the FLEXIDVAL user exit later.

When the report runs, the call to FND FLEXSQL fills in the lexical parameters. As a result the second query would look something like:

SELECT (SEGMENT1||’-'||SEGMENT2||’-'||SEGMENT3||’-'||
SEGMENT4) C_FLEXDATA
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN = 101< /FONT >

Step 8 Create formula columns

Now create columns C_FLEXFIELD and C_DESC_ALL (and any others your report uses) corresponding to the values and descriptions displayed in the report. They all are in group G_1.

Step 9 Populate segment values formula column
Now you need to retrieve the concatenated flexfield segment values and description which you incorporate the flexfields user exits in these columns. In the column definition of C_FLEXFIELD, you incorporate the FND FLEXIDVAL user exit call in the formula field. You pass the concatenated segments along with other information to the user exit, and the user exit populates the concatenated values in this column as specified by the VALUE token. A typical call to populate segment values in this column looks as follows:

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT(’FND FLEXIDVAL
CODE=”GL#”
NUM=”:P_STRUCT_NUM”
APPL_SHORT_NAME=”SQLGL”
DATA=”:C_FLEXDATA”
VALUE=”:C_FLEXFIELD”
DISPLAY=”ALL”‘);
RETURN(:C_FLEXFIELD);

Refer this how to enable user exits in reports.

Step 10 Populate segment descriptions< /STRONG>

To populate the segment description use DESCRIPTION=”C_DESC_ALL” instead of VALUE=”C_FLEXFIELD” as in the previous call. The user exit call becomes:

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT(’FND FLEXIDVAL
CODE=”GL#”
NUM=”:P_STRUCT_NUM”
APPL_SHORT_NAME=”SQLGL”
DATA=”:C_FLEXDATA”
DESCRIPTION=”:C_DESC_ALL”
DISPLAY=”ALL”‘);
RETURN(:C_DESC_ALL);

You have created parameters and columns that are containers of all the values to be displayed. Now, in the following steps, you create the layout to display these values on the report.

Step 11 Create your default report layout

First choose Default Layout to generate the default layout. Deselect C_FLEXDATA. Specify a “Label” and a reasonable “Width” for the columns you want to display.

Step 12 Finish your report By adjusting report layout.

These are the steps required to implement KFF in customization.

• Define KFF fields in your database tables.

The custom table should contain a field named as XXX_ID (where XXX means the entity. For ex. PART_ID, CODE_COMBINATION_ID etc) to capture the selected code combination ID.

• Register the table with Oracle AOL.
• Register the KFF with Oracle AOL

Logon as

Application Developer -> Flex fields -> Key -> Register
Existing KFF can also be used. Ex. Accounting FF

• Create KFF in the custom form

1. Create a form based on the custom table.

2. In the block, create two non-base table text items of data type CHAR (2000). One text item is to store the code combinations (Say BTL_KFF ) and other one is to store description of the code (Say BTL_KFF_DESC. Make this item as non-updateable).

• Add KFF standard built-ins in the form to invoke KFF

  The custom table should contain a field named as XXX_ID (where XXX means the entity. For ex. PART_ID, CODE_COMBINATION_ID etc) to capture the selected code combination ID.

this is typical registration of KFF. For Customization in custom development here are the steps:

1. Write a form level trigger WHEN-NEW-FORM-INSTANCE to invoke the KFF as

Fnd_Key_Flex.Define(
Block => ‘block_name’,
Field => ‘concatenated_segments_field_name’,
[Description => ‘description_field_name’,]
[ID => ‘Unique_ID_field’
Appl_Short_Name => ‘application_short_name’,
Code => ‘key_flexfield_code’,
Num => ‘structure_number’,

In above defination take a note,

Usage

FND_KEY_FLEX.DEFINE(
block => 'Custom block',
Field => 'BTL_KFF',
Description => 'BTL_KFF_DESC',
ID => 'XXX_ID',
Appl_short_name => 'SQLGL',
Code => 'GL#',
Num => '101',
Vrule => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL=''SQLGL'';
name=Parent Values are not allowed\nN'
);

2. XXX_ID will store the code combination ID for each selection.

3.Invoke Flexfield functionality by calling FND_Flex.Event(event) from:

• PRE-QUERY
• POST-QUERY
• PRE-INSERT
• PRE-UPDATE
• WHEN-VALIDATE-RECORD
• WHEN-NEW-ITEM-INSTANCE
• WHEN-VALIDATE-ITEM

3. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on BTL_KFF as

FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE')

4. Write an item level trigger KEY-EDIT on BTL_KFF as

FND_FLEX.EVENT('KEY-EDIT');

5. Write an item level trigger WHEN-VALIDATE-ITEM on BTL_KFF as

FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

IF :CUSTOM_BLOCK.XXX_ID = -1 THEN
FND_MESSAGE.SET_STRING('You Have Selected An Undefined Code Combination !');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;

These steps makes you flexfield enable in your form.

A NOTE FOR FLEX FIELD DEFINATION

Lets take a close scan to understand some of the paramater used in Flex Field defination.

1. [Validate=> ‘{FOR_INSERT|FULL|PARTIAL|NONE|PARTIAL_IF_POSSIBLE}’,]

• This is used only for Key FlexFields only
• “For_Insert” used in a block where the code_combinations table is the base table
• “Full” used where the code_combination_id is a foreign key in the base table
• “None” when any value is allowed, such a range flexfield for query purposes
• “Partial” validates segment values but not the full combination
• “Partial_If_Possible” only used when the flexfield has dependent segments

2.[Vdate => ‘date’,]

• Validation date compared to segment value start/end dates

3.[Required=>’[Y|N’,]

• This is applicable only in the case for key flexfields only
• ‘Y’ requires user to enter a valid value for every required segment, even if the flexfield is never navigated to
• ‘N’ only requires required segments to be entered when the flexfield has been updated

4.[Displayable => ‘{ALL | flexfield_qualifier | segment_number} [\0{ALL|flexfield_qualifier|segment_number}]’,]

• Key Flexfields only. Default is “ALL”
• Naming flexfield qualifier or segment number causes only those segments to appear.
• Toggle this parameter by specifying more than one value separated with \0
• Display all but the first segment by specifying:
• DISPLAYABLE=>’ALL\01’
• Same parameters for Updateable and Insertable

5.[COPY=>’block.field\n{ALL|flexfield qualifier|segment number} [\0block.field\n{ALL|flexfield qualifier|segment number}]’,]

• Again, key flexfields only.
• Copy a form field value into the segment indicated by flexfield qualifier or segment number
• Value must be valid for segment or it will be erased
• Copy works in reverse when flexfield pop-up window closes
• Example copies a form global variable into the natural account segment of the Accounting Flexfield:
• COPY=>’GLOBAL.default_expense_obj\nGL_ACCOUNT’

6.[VRULE => ‘flexfield qualifier\n
segment qualifier\n
{I[nclude] | E[xclude]}\n
APPL=application_short_name;
NAME=Message Dictionary message name\n
Validation value1\n
Validation value2…
[\0flexfield qualifier\n
…

• Key or range flexfields
• Impose additional validation
• ‘\n’ is delimiter between parameters
• ‘\0’ is delimiter between groups of parameters

As we have seen DFF provide customizable expansion space in the form to capture additional information, which are not provided at present. A DFF describes an application entity, providing form and database expansion space that can be customized.

Why Descriptive Flex fields

• Customers require more data to be stored than what Oracle provides
• Oracle gave us additional “space” in most tables to store this data
• Descriptive Flexfields will survive an upgrade

Global vs Context Sensitive

• Global Data Elements mean the question will be asked for every occurrence of the Descriptive Flexfield.
• Context Sensitive Elements are questions asked “depending” on the answer to some other question. This “other” question can use a Reference field from above for its answer.

How Segments Use Underlying Columns

• A DFF uses columns that are added onto a database table. The table contains any columns that its entity requires.
  The DFF columns provide ‘blank’ columns that you can use to store information which is not already stored in another column of that table.
• A DFF requires one column for each possible segment and one additional column in which to store structure information(i.e. The context value).
• You can define as many segments in a single structures as you have DFF columns in your table.
  The DFF columns are usually named as ATTRIBUTEn, where n is a number.
  

Implementing Descriptive Flexfileds
For implementing DFF it requires five steps:

• Define DFF columns in your Database table
• Register your table with AOL/ Pl/SQL scripts
• Register Your DFF with Oracle AOL
• Create Your DFF in Your Form
• Add DFF routines to Your Form

Drawbacks of DFF

1. Disk space is wasted.
2. DFF has limited segment for global as well as context-sensitive attribute.
3. No query possible on DFF segment values.

In last post he have seen most businesses use codes made up of meaningful segments (intelligent keys) to identify accounts, part numbers and other business entities. For example a company might have a part number ‘PAD-NR-GRN’ indicating a Notepad, Narrow Ruled and Green. Another might have a different code structure for the same Notepad, Narrow Ruled and Green as ‘GRN-NR-PAD’. Hence KFF provides the flexibility to define the code structure of any business entity in the customer-desired format without reprogram the application.

We all know , oracle GL uses a KFF called Accounting flex field to uniquely identity a GL account. Oracle have customized this KFF to include 6 segments like Company Code, Cost Center, Product, Product Line and sub account. Valid values are available to each segment and cross validation rules are provided for segment combinations. This KFF can be customized depends upon the business needs.

When to use a KFF

• Used to uniquely identify an application entity with an intelligent key, where the key can be multipart and each part can have some meaning.
• Use KFF that Oracle Applications provides to integrate your applications seamlessly with Oracle Applications.

What End User’s want there business Perspective

These are the things normally any business user want if implementation team and management avoiding any customization.

• Customize the Flex fields Appearance
  • Flexfield title
  • Number and order of segments
  • Prompts
  • Value sets, values, and value description.
• Use Flexfield Functionality for Validation
  • Individual segment validation
  • Cross-segment validation
• Customize to the Situation
  • Flexfields allow the application to adjust to fit specific business needs.
  • Use multiple structures to match different needs in different situations.

The good things is that the above can be easily control and managed from screen, without any extra(Ordinary) skill ;)

Types of Key Flexfield Forms

In oracle application key Flexfield appears on three different types of application form :

1. Combinations Form
2. Foreign Key Form
3. Range Form

These form types correspond to the types of tables that contains key flexfield data.

Create Key Flexfield Combinations

A combination is a particular complete code or combination of segment values that makes up the code that uniquely identifies an object. For example, each part number would be a single combination, and if you had ten parts you would define ten combinations. A valid combination is simply a combination that may currently be used.

Combinations Table
Each key Flexfield has one corresponding combinations table, known as the Combinations table, where the flexfield stores a list of the complete codes, with one column for each segment of the code, together with the corresponding unique ID number(a code combination id number or CCID) for the code. Then the other tables in the application have a column that stores just the unique ID for the code

Foreign Key Form

• Use the flex field code combinations from a form with a foreign key reference to the combinations table.
• Internally the underlying Table Uses a Foreign Key
• The unique ID primary key column of the combinations table serves as a foreign key column here.
• Often the foreign key table contains a structure ID column as well. It can be best represented as figure below,

Range From

• Here table Contains Two Columns For Each Possible Segment
• Often the table contains a structure ID column as well.
• This form does not require actual combinations, and so does not need a foreign key link to the combinations table.
• Perform operation on ranges of combinations using this form.
  

Create New Combinations Dynamically

Allow the defination of new flexfield combinations from forms with a foreign key references, as mention in the following diagram.

Describe The Values In A Segment

• A segment qualifier is like the segment asking each value a question “What type of value are you?”
  • For example, assign an account type of “Expense” to the Account segment value 3003.
• Uses Segment Qualifiers with the Accounting Flexfield
  • Detail Budgeting Allowed.
  • Detail Posting Allowed.
  • Account Type: Asset, Expense, Liability, Ownership/Stockholde’s Equity, or Revenue.

Dynamically Insert Allowed

• Set and change this property while defining the flexfield structure on the Define
• Key Flexfield Segments form.
  • Sometimes this property is not technically feasible
  • If the combinations table includes mandatory columns that can not be filled by the flexfield.
  • Who columns, CCID columns, and enabled/activation date information can be filled by the flexfield.

This is all about the key flex field. Hopefully it would be good to start with , If you want to learn more about the concept, please do refer Flexfield user guide. Next will take another deep drive for DFF.

Some one requested last week to provide some information about flex fleld concept. Thought its good to take all into one.Next couple of post will focus on Flex field concept, need and design for custom requirement.

Oracle applications use key flex field to represent intelligent keys with unique ID numbers. ID numbers require only one column while intelligent keys will require multiple columns (one for each section or segment of the code).

Flexfield - A field made up of one or more segments. A segment corresponds to a column in an Oracle table.
Each segment has an assigned name and may have assigned data validation rules (Validation Sets).

Key Flexfield - A Key Flexfield represents an intelligent key that uniquely identifies an application entity. These keys identify accounts, part numbers etc. Each key flexfield has an assigned name and a specified set of valid values.
Examples: in G/L we have the Accounting Flexfield (Chart of Accounts); in Inventory it is the Item Flexfield, in Projects the Job Flexfield.

Descriptive Flexfield - Allows for the capture of additional information not stored in the default application, or strictly for reference when viewed on a screen.
You may create a Flexfield using any of “Attribute(n)” columns. Oracle reserved “Attribute(n)” columns on most of the tables to be setup during installation. These columns are not used by Oracle standard functionality.
You may expand the number of Flexfield values by using “context sensitive” feature.

Flexfield Types /What is the difference between a key flexfield and a descriptive flexfield?

Key Flexfield:

An intelligent key that uniquely identifies an application entity. Each key flexfield segment has a name you assign, and a set of valid values you specify.

Descriptive Flexfield

A field that your organization can extend to capture extra information not otherwise tracked by Oracle Applications. A descriptive flexfield appears in your window as a single character, unnamed field. Your organization can customize this field to capture additional information unique to your business. (Example: capturing state codes on selected journal lines)

Flexfield Key Terms

• Accounting Flexfield : The code you use to identify a general ledger (GL) account in Oracle.
• Segment: A subcomponent of a flexfield. Company and natural account are examples of possible segments in the accounting flexfield.
• Flexfield Qualifiers : Balancing segment, cost center segment, natural account, inter-company segment.
• Segment Values : Numbers or codes attributed to each segment.
  Value Set: A list of values for one segment, such as the list of companies, list of accounts, or the list of departments.
• Code Combination : One full accounting flexfield with all segment values.
• Dynamic Insertion / Cross-Validation : Automatic creation of account code combination based on rules defined with code combinations.

What is Accounting Flexfield

• Accounting (Key) Flexfield segment one of up to 30 different sections of the accounting flexfield, which together make up a GL account code.
• Accounting Flexfield structure - the combination of key flexfield segments defined to make up the account code combinations. If a segment(s) is added or removed or re-arranged, the result is a different structure.

When to use a KFF

• Used to uniquely identify an application entity with an intelligent key, where the key can be multipart and each part can have meaning.
• Use KFF that Oracle Applications provides to integrate your applications seamlessly with Oracle Applications.

Will take a deep drive for KFF and DFF in next post.