Question : We have already covered Descriptive Flexfield in detail in previous article, should we still bother learning key flexfield?
Answer : Indeed we must learn this. When I myself learnt Oracle Apps, all I had was the cryptic definitions in Oracle Manual, and it took me weeks to get my head around the differences between Key Flexfields and Descriptive Flexfields. Now I hope you can learn this crystal clear in minutes.

Question: Key Flexfields help us capture additional fields, and so does descriptive flexfield too? What is the deal here?
Answer: Ok, let’s assume for a minute that there is no such thing as a key flexfield. All we have is a descriptive flex (lets assume).
Requirement is this:-
Your client wants to capture values in following additional fields for a purchase order transaction and invoices...
Company name: GM
Cost Centre: IT
Project: OFP --means Oracle Fusion Project
Expense Type: OCC  -- Oracle Consultant Cost

In a DFF ONLY WORLD, when your client raises Purchase Order to IT Consulting Company, in PO_DISTRIBUTIONS_ALL table record you will store
ATTRIBUTE1 :- GM
ATTRIBUTE2 :- IT
ATTRIBUTE3 :- OFP
ATTRIBUTE4 :- OCC

When an invoice is received from consulting company, the Payables clerk will capture the Invoice Line accounting as below in AP_INVOICE_DISTRIBUTIONS_ALL
ATTRIBUTE1 :- GM
ATTRIBUTE2 :- IT
ATTRIBUTE3 :- OFP
ATTRIBUTE4 :- OCC
These 4 text values for fields(above) are physically being duplicated in each module, for the related/unrelated transactions.

Imagine further when this transaction flows to Oracle General Ledger, would you again expect oracle to physically store the 4 columns into table GL_JE_LINES? If so your table GL_JE_LINES will have following values in its DFF (Descriptive Flex) columns....
ATTRIBUTE1 :- GM
ATTRIBUTE2 :- IT
ATTRIBUTE3 :- OFP
ATTRIBUTE4 :- OCC

Surely, such design using a descriptive flexfield will be flawed, as it causes duplication of data at various places.
Now that you understand why Descriptive flexfield does not fit into this design, lets consider a new scenario.

Consider an alternate approach.( using KFF )
Let’s have a table named gl_code_combinations with following columns.
CODE_COMBINATION_ID
SEGMENT1
SEGMENT2
SEGMENT3
SEGMENT4

Let’s capture A SINGLE record in this table as below:-
CODE_COMBINATION_ID : 10902
SEGMENT1 : GM
SEGMENT2 : IT
SEGMENT3 : OFP
SEGMENT4 : OCC

Note the above combination of 4 fields can be uniquely identified by 10902(CODE_COMBINATION_ID).

In PO_DISTRIBUTIONS_ALL table, we will have below column with value
CODE_COMBINATION_ID : 10902
NOTE: Now we are not storing all four columns here in PO Dist table, as we store the Unique ID of the record in Key Flexfield table.

Again, in Account Payables, even though the clerk will enter in screen values for four columns (four each segment), the database will only store value 10902 in column CODE_COMBINATION_ID of payables distributions table.
Ditto for the entry in GL_JE_LINES table in oracle general ledger, only the ID that references those 4 columns will be stored.

Hence all the tables(PO Dist, AP Dist, GL JE Lines) will reference just the CODE_COMBINATION_ID.

Now some Q & A below

Question: Does this mean, for each key flexfield, there will be a dedicated table? And such table will hold the unique combination of field values that can be reused?
Answer: correct. For gl accounting key flexfield, there is a table named gl_code_combinations. Other examples are grades in oracle human resources. A grade can be defined as a combination of say Clerk + Senior or Clerk + Junior. These combinations will be stored in per_grades table.

Question: do all the tables which are used for storing key Flexfields have columns named segment1,segment2...segmentx?
Answer : Correct, it is a standard practice used by oracle. Thee segments columns are generic columns so that each client can call them by whatever name as they desire.

Question: Does Oracle deliver Key-Flexfields out of the box, which will pop-up a window with relevant fields, as configured during setup.
Answer : Yes, and if value sets are attached, the fields can be validated too.

Question : Is there a step by step example of setting up key flexfield.
Answer : Have a look at article for Setting up Special Information Types in HRMS using Key FlexField .
For change in perspective, I have covered a HRMS Key Flexfield, named Special Information Types.

Comments (26)

Subscribe to this comment's feed

...
written by Bhanu , November 18, 2006

quite well explained, i should bookmark ur site

...
written by Bhanu , November 18, 2006

quite well explained, i should bookmark ur site

...
written by Anil Passi , November 19, 2006

Thanks Bhanu, You may also decide to register, so that you can receive a monthly email update on new articles.

...
written by Anil Passi , November 19, 2006

Thanks Bhanu, You may also decide to register, so that you can receive a monthly email update on new articles.

...
written by seenujanu , November 20, 2006

Thanks by giving valuable information,now i cleared the doubts from the flexfields

...
written by seenujanu , November 20, 2006

Thanks by giving valuable information,now i cleared the doubts from the flexfields

...
written by Mukesh , December 01, 2006

Hi anil

Please let me know if i have a key flexfied in which 2 segment are there and i want to add another segment however in earlier 2 segments data is there , can i add another segment.

Thanks,
Mukesh

...
written by gayathri , December 01, 2006

Thanks Anil for the Article.

...
written by Mukesh , December 01, 2006

Hi anil

Please let me know if i have a key flexfied in which 2 segment are there and i want to add another segment however in earlier 2 segments data is there , can i add another segment.

Thanks,
Mukesh

...
written by gayathri , December 01, 2006

Thanks Anil for the Article.

...
written by Mukesh , December 04, 2006

Hi Anil,
Thanks for the update,but in general
CONCATENATED_SEGMENTS automatically update automatically when we insert any values so in this case do we need to append.

please let me knw the procedure for updatting the CONCATENATED_SEGMENTS.

Thanks,
Mukesh

...
written by Mukesh , December 04, 2006

Hi Anil,
Thanks for the update,but in general
CONCATENATED_SEGMENTS automatically update automatically when we insert any values so in this case do we need to append.

please let me knw the procedure for updatting the CONCATENATED_SEGMENTS.

Thanks,
Mukesh

...
written by Mukesh , December 08, 2006

Hi Anil,
Can you please put some light on flexfield qualifier ,segment qualifier ,Dynamic insertion and cross validation in flexfield via example.

I read in document concepts are still not much clear on these topics.

Thanks & Regards,
Mukesh

...
written by Mukesh , December 08, 2006

Hi Anil,
Can you please put some light on flexfield qualifier ,segment qualifier ,Dynamic insertion and cross validation in flexfield via example.

I read in document concepts are still not much clear on these topics.

Thanks & Regards,
Mukesh

...
written by Jyothi , December 18, 2006

Hi Anil,

I found this site very excellent.

Thanks Anil for sharing your knowledge with every one.

Thanks
Jyothi

...
written by Jyothi , December 18, 2006

Hi Anil,

I found this site very excellent.

Thanks Anil for sharing your knowledge with every one.

Thanks
Jyothi

...
written by Ramya Laxman , February 17, 2007

Anil, Can you also give some info about the required flex fields in apps i.e. which need to be setup in different modules says assets, service contracts, inventory, etc.

...
written by Ramya Laxman , February 22, 2007

Thanks for the info, Anil. I asked this question about the required flex fields as this was a question posed during an interview. How many necessary flex fields should be setup in Apps and what are their names in each module. Let me know if I had understood the question incorrectly. Thank you.

...
written by Florin , March 05, 2007

Hello Anil,

PER_ANALYSIS_CRITERIA does not have any CONCATENATED_SEGMENTS column. this table is used for a KFF. do you have any idea why it does not have this column ?
thanks,

...
written by Anil Passi , March 24, 2007

Thanks Siva. It appears 22 are mandatory.
And if you run below
select count(*) from fnd_id_flexs ;
You will see 37. So, it means 15KFF are optional.

...
written by Prabhakar , May 01, 2007

Yes , you have a wonderful blogspot and it helps all oracle consultants also. Keep up your good work...

...
written by sanjay , May 27, 2007

Another great piece of work done by a great Indian.

...
written by Gowri , August 30, 2007

Hi Anil,
Great website and very good articles.
I have a specific question on KFF. You have written that Oracle will store a ID(key) for the KFF instead of individual values of segments. I understand that peice, But I do not understand When and How ID's are generated. Is it done dynamically on the fly, or at the time of creation of KFF.
Also, Say a KFF has 3 segments with 1st segment having a valueset of say 2 distinct values, 2nd with 3 distinct values, and 3rd with 2 distinct values. In that case, would oracle generate 2x3x2=12 combinations with ID's. What if one of the segment has a valueset of 100 values, or even say if it is free-text..
Would Oracle create all combinations and ID's, at the time of KFF creation and store in some place, Or would it create them dynamically when the actual value is stored in the table.
Obviously 2nd approach is a preferred one, but just want to confirm the same from you, as I am not sure of it.

~Regards,
Gowri

...
written by Anil Passi , September 01, 2007

Hi Gowri

The unique ID is generated from a sequence.
When you register a Key Flex Field in Application developer responsibility, at that time ayou specify the UNIQUE ID COLUMN for the KFF.

The most common example is accounting flexfield, which uses a sequence named gl_code_combinations_s.nextval to generate the code_combination_id

Thanks,
Anil Passi

Selecting Key flexfield Structure
written by Manpreet Singh , October 13, 2007

If we have multiple structures for a keyflexfiled, how to specify whicih structure should be used by the KFF?

...
written by James , February 12, 2008

Hi Anil,

I have posted this request earlier under different thread in your site. I realised that this question is much more appropriate in this thread.

I am trying to create Key Flexfields in OA Framework page. I had tried the steps outlined in the Declarative Implementation as mentioned in OA Framework Developers Guide (Metalink note 269138.1). I am getting following error.

The data that defines the flexfield on this field may be inconsistent. Inform your system administrator that the function: KeyFlexfieldDefinitionFactory.getStructureNumber could not find the structure definition for the flexfield specified by Application = SQLGL, Code = GL# and Structure number =

I wonder why the structure number is null? How can I pass structure number to OAKeyFlexBean?

My supspecion is that I have missed out something while creating EO, AO or VO. In the developers guide it had mentioned to Ensure that the view object underlying the region includes the CCID column (the foreign key reference). I am bit confused about creating entity object that contain a foreign key reference to the code combinations table. Is it refering to creating Association object between the base EO and EO for the code combination table?

Thanks,

James