Key Flexfields Basics

Key Flexfields Basics

Please find an article on the very basics of Key Flexfields.
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.