GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

E-Business Suite R12 – 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

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado.