SQLs úteis – Chart of accounts, SOB e accounting flexfield segments
Boa tarde pessoal !
Abaixo um SQL que já me quebrou um galho algumas vezes. Ele mostra o Chart of Account, SOB e os Accounts flexfield segments e seus atributos:
SELECT sob.name sob_name
,sob.set_of_books_id sob_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 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 = NVL(FND_PROFILE.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)
ORDER BY sob.name, sob.chart_of_accounts_id, ifs.application_column_name;Com algumas alterações, é possível trazer mais informações ainda ! 🙂
Espero que essa dica seja útil !
Abraço
