SQL Query for chart of accounts

SQL Query for chart of accounts

In this post , we will be discuss about SQL Query for chart of accounts. Chart of accounts is uses to create the Accounting Flex fields structure in oracle. Chart of Accounts is the combination of many Segments in oracle and we attach the Value sets in these Chart of Accounts segments. This query helps to find the complete chart of accounts information's in oracle apps. Here below I am sharing the complete SQL Query for chart of accounts.

 

 

5 Important Tables of chart of accounts

1.FND_ID_FLEX_SEGMENTS
2.FND_ID_FLEX_SEGMENTS_TL
3.FND_ID_FLEX_STRUCTURES

 

 Example of SQL Query for chart of accounts

select
 b.FORM_LEFT_PROMPT "Chart Of Account Segment Name",
 b.description,
 a.segment_name "CCA_VALUSET",
 c.ID_FLEX_STRUCTURE_NAME "Chart Of Account Structure Name" ,
 b.application_column_name "Internal Segment"
 from
 apps.FND_ID_FLEX_SEGMENTS a,
 apps.FND_ID_FLEX_SEGMENTS_TL b,
 apps.FND_ID_FLEX_STRUCTURES_VL c
 where
 b.language = 'US'
and c.ID_FLEX_NUM = b.ID_FLEX_NUM
 and c.enabled_flag = 'Y'
and b.application_id = a.application_id
 and b.id_flex_num = a.id_flex_num
 and b.application_column_name = a.application_column_name
 and a.application_id = 101