Query to get the Accounting Flexfield Structure

SELECT DISTINCT sob.name Ledger_Name
,sob.set_of_books_id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
,gl_sets_of_books sob
WHERE 1=1
AND fifs.id_flex_code = ‘GL#’
AND fifs.application_id = fifst.application_id
AND fifs.id_flex_code = fifst.id_flex_code
AND fifs.id_flex_num = fifst.id_flex_num
AND fifs.application_id = ifs.application_id
AND fifs.id_flex_code = ifs.id_flex_code
AND fifs.id_flex_num = ifs.id_flex_num
AND sav1.application_id = ifs.application_id
AND sav1.id_flex_code = ifs.id_flex_code
AND sav1.id_flex_num = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id = ifs.application_id
AND sav2.id_flex_code = ifs.id_flex_code
AND sav2.id_flex_num = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id = ifs.application_id
AND sav3.id_flex_code = ifs.id_flex_code
AND sav3.id_flex_num = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id = ifs.application_id
AND sav4.id_flex_code = ifs.id_flex_code
AND sav4.id_flex_num = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id = ifs.application_id
AND sav5.id_flex_code = ifs.id_flex_code
AND sav5.id_flex_num = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id = ifs.application_id
AND sav6.id_flex_code = ifs.id_flex_code
AND sav6.id_flex_num = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type = ‘GL_BALANCING’
AND sav2.segment_attribute_type = ‘FA_COST_CTR’
AND sav3.segment_attribute_type = ‘GL_ACCOUNT’
AND sav4.segment_attribute_type = ‘GL_INTERCOMPANY’
AND sav5.segment_attribute_type = ‘GL_SECONDARY_TRACKING’
AND sav6.segment_attribute_type = ‘GL_GLOBAL’
AND ifs.id_flex_num = sob.chart_of_accounts_id
AND ifs.flex_value_set_id = ffvs.flex_value_set_id
AND sob.set_of_books_id = Give ‘Set_Of_Books_Id’
ORDER BY sob.name, sob.chart_of_accounts_id, ifs.application_column_name;
———————————————————–R12———————————————————–
SELECT DISTINCT sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
WHERE 1=1
AND fifs.id_flex_code = ‘GL#’
AND fifs.application_id = fifst.application_id
AND fifs.id_flex_code = fifst.id_flex_code
AND fifs.id_flex_num = fifst.id_flex_num
AND fifs.application_id = ifs.application_id
AND fifs.id_flex_code = ifs.id_flex_code
AND fifs.id_flex_num = ifs.id_flex_num
AND sav1.application_id = ifs.application_id
AND sav1.id_flex_code = ifs.id_flex_code
AND sav1.id_flex_num = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id = ifs.application_id
AND sav2.id_flex_code = ifs.id_flex_code
AND sav2.id_flex_num = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id = ifs.application_id
AND sav3.id_flex_code = ifs.id_flex_code
AND sav3.id_flex_num = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id = ifs.application_id
AND sav4.id_flex_code = ifs.id_flex_code
AND sav4.id_flex_num = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id = ifs.application_id
AND sav5.id_flex_code = ifs.id_flex_code
AND sav5.id_flex_num = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id = ifs.application_id
AND sav6.id_flex_code = ifs.id_flex_code
AND sav6.id_flex_num = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type = ‘GL_BALANCING’
AND sav2.segment_attribute_type = ‘FA_COST_CTR’
AND sav3.segment_attribute_type = ‘GL_ACCOUNT’
AND sav4.segment_attribute_type = ‘GL_INTERCOMPANY’
AND sav5.segment_attribute_type = ‘GL_SECONDARY_TRACKING’
AND sav6.segment_attribute_type = ‘GL_GLOBAL’
AND ifs.id_flex_num = sob.chart_of_accounts_id
AND ifs.flex_value_set_id = ffvs.flex_value_set_id
AND sob.ledger_id =’Give Ledger ID or Set_Of_Books_Id’
ORDER BY sob.name, sob.chart_of_accounts_id, ifs.application_column_name;