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
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
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
1 comments:
select distinct b.flex_value_set_name,a.flex_value,c.description, a.last_update_Date, d.user_name
from apps.fnd_flex_values a, apps.fnd_flex_value_sets b,apps.fnd_flex_values_tl c, apps.fnd_user d
where a.flex_value_set_id=b.flex_value_set_id
and a.flex_value_id=c.flex_value_id
and a.last_updated_by=d.user_id
and b.flex_value_set_name like 'XXC_GL%'
and a.enabled_flag='Y'
and a.last_update_date between '01-OCT-2021' and '15-MAY-2022'
order by b.flex_value_set_name;
Post a Comment