View for Chart of Accounts KFF

The script below allows you creates a view for your chart of accounts.
Modify to suit your requirements. Note that the value set name of each segment is passed as parameter.

[code]
CREATE OR REPLACE VIEW ABH_GL_ACC_CONCAT_SEGMENTS
AS
SELECT cc.code_combination_id,
cc.chart_of_accounts_id,
cc.detail_posting_allowed_flag post,
cc.detail_budgeting_allowed_flag budget,
cc.account_type,
cc.show_account_type,
cc.enabled_flag,
cc.summary_flag,
--segment: segments used in COA definition
cc.segment1,
cc.segment2,
cc.segment3,
cc.segment4,
cc.segment5,
cc.segment6,
concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(segment1, '-'), segment2),'-'), segment3), '-'), segment4), '-'), segment5), '-'), segment6) account_comb,
get_flex_vs_desc('ABH Company', cc.segment1, '') Company,
get_flex_vs_desc('ABH Cost Center', cc.segment2, '') "Cost Center",
get_flex_vs_desc('ABH Account', cc.segment3, '') "Account",
get_flex_vs_desc('ABH Sub Account', cc.segment4, cc.segment3) "Sub Account",
get_flex_vs_desc('ABH Location', cc.segment5, '') "Location",
get_flex_vs_desc('ABH Entity-Services', cc.segment6, '') "Entity-Services"
FROM GL_CODE_COMBINATIONS_V cc
[/code]

Refer to document 'Fetching Key Flexfield Value Description' for get_flex_vs_desc.
Shameem Bauccha

19 October 2009

Key Flexfields

Platform: 11.5.10, R12


Key Flexfield Architecture

Key Flexfield is a very important concept in Oracle. It allows you to define Key Structures in each Module. This gives you the flexibility to model your objects according to your requirements. Some examples of flexfields are as follow:

Accounting Flexfield in General Ledger.

Job, Position and Grade Key Flexfield in Human Resources.

[caption id="" align="alignnone" width="572" caption="Key Flexfield Architecture"][/caption]

A flexfield is a structure that you define for a Flexfield Title. The Flexfield Structures are registered within an application.

A structure will compose of one or more segments. Each segment will be attached to a value set which you would have defined. If required you will qualify the segment in question.

Define Key Flexfield Structure

System Administrator --> Flexfield --> Segments

Note: KFFs are accessible from other Modules as well.

[caption id="" align="alignnone" width="756" caption="Key Flexfield Segments"][/caption]

Query for the Key Flexfield in question. As an example I have queried the 'Training Resources' KFF.

[caption id="" align="alignnone" width="756" caption="Training Resources KFF"][/caption]

Add your structure, then click on ‘Segments’ to define the segments.

[caption id="" align="alignnone" width="755" caption="Segments Summary"][/caption]

Click on ‘Value Set’. Define your value set. The Value Set determines what values will be available for the segment in question. Specify the ‘Value Validation’.  Save the value set, close the form and attach it to the segment as shown above.

We have various ways of validating the values.

(Detailed document to follow on Value Sets)

Define Value Sets

[caption id="" align="alignnone" width="755" caption="Define Value Sets"][/caption]

In this case, the validation is based on a table. Click on ‘Edit Information’ to define it.

[caption id="" align="alignnone" width="755" caption="Validation Table Information"][/caption]

When you are done, click on ‘Open’ on the ‘Segment Summary’ Form. Specify whether it is required. By default all segments are marked as required.

[caption id="" align="alignnone" width="755" caption="Segments"][/caption]

Flexfield Qualifiers

Click on ‘Flexfield Qualifiers’ to qualify the segment. This is not valid for all Key Flexfields. Accounting Flexfield has Flexfield Qualifiers and is illustrated below.

[caption id="" align="alignnone" width="620" caption="Flexfield Qualifiers"][/caption]

Once you are done with all the segments close the ‘Segments Summary’ Form to return to the ‘Key Flexfield Segments’ window.

Compile Key Flexfield Structure

[caption id="" align="alignnone" width="740" caption="Compile KFF"][/caption]

To be able to use the KFF, you need to freeze the structure. To freeze the structure, check the ‘Freeze Flexfield Definition’ and click on ‘Compile’. A request is launched to compile the structure. The structure is now available in your application.

Note: The ‘Allow Dynamic Inserts’ checkbox enables KFF values to be generated while using the application. If you don’t allow dynamic insert, then you need to define all the valid value combinations for your structure before you can use these values in your application.

Shameem Bauccha

24 June 2009

Accounting Key Flexfield - Create Chart of Accounts

(doc will be ready tomorrow)

apologies

Refer to the document 'Define Key Flexfield Structure' for further details.

Shameem Bauccha

23 June 2009

Scripts - Flexfield Values

Key Flexfields are stored in the following tables:

1. FND_FLEX_VALUE_SETS


2. FND_FLEX_VALUES

SELECT *
FROM FND_FLEX_VALUES
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = :VALUE_SET_NAME
)
AND ENABLED_FLAG = 'Y' -- For Active Values
AND SUMMARY_FLAG = 'N' -- For Child Values

Pass the Value Set Name as Parameter