Flexfield values ... 1

[zMonk]

Hi,
I just recently started to work with Oracle Fin. (a common start of a sentence). So far a I can write some selects mainly on Gl_balances, gl_je_lines , gl_CCid and etc. Which I found in tech reference , but I can not figure out how can I pull FlexField value description, e.g. I selected balances based on conditions , but I need to swon description of an account (segment2) and entity (segment1)... which tables should I quiery?

Thanks in advance.

 

[jimbopalmer]

I am not at work, but the table is like GL_Code_Combinations I tried to remain child-like, all I acheived was childish.

 

[zMonk]

Let me re-frase... I can join gl_code_combinations with gl_balances to get an appropriate result, but I also need to list the description of an account e.g. for inctance I will select segment1, segment2, segment3 (segment2 in my case is an account ) I also need description of segment2 like "12345" - "Cash" or "34567" - "Dividends". I think this is stored in Account chart valye set or something like this...

 

[jimbopalmer]

SELECT
FLEX_SEGMENT1.FLEX_VALUE,
FLEX_SEGMENT1.DESCRIPTION,
FLEX_SEGMENT2.FLEX_VALUE,
FLEX_SEGMENT2.DESCRIPTION,
GL_CODE_COMBINATIONS.SEGMENT3,
FLEX_SEGMENT3.DESCRIPTION
FROM
GL_CODE_COMBINATIONS,
FND_FLEX_VALUES FLEX_SEGMENT1,
FND_FLEX_VALUES FLEX_SEGMENT2,
FND_FLEX_VALUES FLEX_SEGMENT3
WHERE
( FLEX_SEGMENT3.FLEX_VALUE=GL_CODE_COMBINATIONS.SEGMENT3 )
AND ( GL_CODE_COMBINATIONS.SEGMENT2=FLEX_SEGMENT2.FLEX_VALUE )
AND ( FLEX_SEGMENT1.FLEX_VALUE=GL_CODE_COMBINATIONS.SEGMENT1 )
AND ( GL_CODE_COMBINATIONS.CHART_OF_ACCOUNTS_ID=101 )
AND ( FLEX_SEGMENT1.FLEX_VALUE_SET_ID = 1000166 AND
FLEX_SEGMENT1.ENABLED_FLAG = 'Y' and FLEX_SEGMENT1.SUMMARY_FLAG = 'N' )
AND ( FLEX_SEGMENT2.FLEX_VALUE_SET_ID = 50209 AND
FLEX_SEGMENT2.ENABLED_FLAG = 'Y' and FLEX_SEGMENT2.SUMMARY_FLAG = 'N' )
AND ( FLEX_SEGMENT3.FLEX_VALUE_SET_ID = 50210 AND
FLEX_SEGMENT3.ENABLED_FLAG = 'Y' and FLEX_SEGMENT3.SUMMARY_FLAG = 'N' )




the IDs will vary system to system but the idea is the same I tried to remain child-like, all I acheived was childish.

 

[zMonk]

Thanks, for the reply... This table(FND_FLEX_VALUES) was my first on "assumptions list", but heare is a describe on the above table in my enviroment:

SQL> desc FND_FLEX_VALUES
Name Null? Type
------------------------------- -------- ----
FLEX_VALUE_SET_ID NOT NULL NUMBER(10)
FLEX_VALUE_ID NOT NULL NUMBER(15)
FLEX_VALUE NOT NULL VARCHAR2(150)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NUMBER(15)
ENABLED_FLAG NOT NULL VARCHAR2(1)
SUMMARY_FLAG NOT NULL VARCHAR2(1)
START_DATE_ACTIVE DATE
END_DATE_ACTIVE DATE
PARENT_FLEX_VALUE_LOW VARCHAR2(60)
PARENT_FLEX_VALUE_HIGH VARCHAR2(60)
STRUCTURED_HIERARCHY_LEVEL NUMBER(15)
HIERARCHY_LEVEL VARCHAR2(30)
COMPILED_VALUE_ATTRIBUTES VARCHAR2(2000)
VALUE_CATEGORY VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(240)
ATTRIBUTE2 VARCHAR2(240)
ATTRIBUTE3 VARCHAR2(240)
ATTRIBUTE4 VARCHAR2(240)
ATTRIBUTE5 VARCHAR2(240)
ATTRIBUTE6 VARCHAR2(240)
ATTRIBUTE7 VARCHAR2(240)
ATTRIBUTE8 VARCHAR2(240)
ATTRIBUTE9 VARCHAR2(240)
ATTRIBUTE10 VARCHAR2(240)
ATTRIBUTE11 VARCHAR2(240)
ATTRIBUTE12 VARCHAR2(240)
ATTRIBUTE13 VARCHAR2(240)
ATTRIBUTE14 VARCHAR2(240)
ATTRIBUTE15 VARCHAR2(240)
ATTRIBUTE16 VARCHAR2(240)
ATTRIBUTE17 VARCHAR2(240)
ATTRIBUTE18 VARCHAR2(240)
ATTRIBUTE19 VARCHAR2(240)
ATTRIBUTE20 VARCHAR2(240)
ATTRIBUTE21 VARCHAR2(240)
ATTRIBUTE22 VARCHAR2(240)
ATTRIBUTE23 VARCHAR2(240)
ATTRIBUTE24 VARCHAR2(240)
ATTRIBUTE25 VARCHAR2(240)
ATTRIBUTE26 VARCHAR2(240)
ATTRIBUTE27 VARCHAR2(240)
ATTRIBUTE28 VARCHAR2(240)
ATTRIBUTE29 VARCHAR2(240)
ATTRIBUTE30 VARCHAR2(240)
ATTRIBUTE31 VARCHAR2(240)
ATTRIBUTE32 VARCHAR2(240)
ATTRIBUTE33 VARCHAR2(240)
ATTRIBUTE34 VARCHAR2(240)
ATTRIBUTE35 VARCHAR2(240)
ATTRIBUTE36 VARCHAR2(240)
ATTRIBUTE37 VARCHAR2(240)
ATTRIBUTE38 VARCHAR2(240)
ATTRIBUTE39 VARCHAR2(240)
ATTRIBUTE40 VARCHAR2(240)
ATTRIBUTE41 VARCHAR2(240)
ATTRIBUTE42 VARCHAR2(240)
ATTRIBUTE43 VARCHAR2(240)
ATTRIBUTE44 VARCHAR2(240)
ATTRIBUTE45 VARCHAR2(240)
ATTRIBUTE46 VARCHAR2(240)
ATTRIBUTE47 VARCHAR2(240)
ATTRIBUTE48 VARCHAR2(240)
ATTRIBUTE49 VARCHAR2(240)
ATTRIBUTE50 VARCHAR2(240)


I do not have "description" column. We use 11i , is it the case that this version data architecture is different? I tried to select attributes, but they have null values...

 

[jimbopalmer]

Rats I am on 10.7SC sorry! I tried to remain child-like, all I acheived was childish.

 

[desiindian]

Hi,

You will have to pick up the description from the table fnd_flex_values_tl which is linked to the fnd_flex_values by flex_value_id.

Hope this helps.

 

[zMonk]

Bingo!!! Thank you!!! Also, I see that this table contains multiple lines based on the same id but for different languages ... Do you know why? It does not make sence for me since I see only one (US) entry from front end.

 

[lava1178]

Hi,
To answer your Q:

Any table which ends with "_tl " means it is used for translation purpose. And hence you see many records in the backend corresponding to the same id in different languages.

The front end language actually corresponds to the language which it is set. Which can be obtained using USER_ENV(NLS_LANG).

Hope this helps