GL - Oracle Fusion Accounting Scripts

Query to fetch the Sub-Ledger Application and its mapping set in Oracle Cloud Fusion

  SELECT xst.application_name,

         xst.application_id,

         xst.description,

         xmsb.mapping_set_code,

         xmsb.mapping_set_id,

         xmsb.amb_context_code,

         xmsb.enabled_flag ms_enabled_flag,

         xmsb.updated_flag,

         xmsb.last_updated_by,

         xmsb.last_update_date,

         xmsv.mapping_set_value_id,

         xmsv.input_value_constant1,

         xmsv.value_constant,

         xmsv.effective_start_date,

         xmsv.effective_end_date,

         xmsv.enabled_flag

    FROM xla_subledgers_tl    xst,

         xla_mapping_sets_b   xmsb,

         xla_mapping_set_values xmsv

   WHERE     1 = 1

         AND xst.application_id = xmsb.application_id

         AND xmsb.application_id = xmsv.application_id

         AND xmsb.mapping_set_code = xmsv.mapping_set_code

         AND xmsb.amb_context_code = xmsv.amb_context_code

         AND xst.application_name = '<SUB_LEDGER_APPLICATION>'

         AND xst.language = 'US'

ORDER BY xst.application_name, xmsb.mapping_set_code

Query to fetch the GL Accounting Flex Fields Hierarchy Structure. Provided the example for the Company Hierarchy

SELECT tree.tree_structure_code,

       tree.tree_code,

       tree.tree_node_id,

       tree.tree.tree_version_id,

       tree.parent_pk1_value parent,

       tree.pk1_start_value child,

       tree.creation_date,

       tree.created_by,

       tree.last_updated_by,

       tree.last_update_date

  FROM fnd_tree_node tree

 WHERE     tree.tree_structure_code = 'GL_ACCT_FLEX' -- <Provide GL Account Structure Code>

       AND tree.tree_code = 'Company Hierarchy' --<Provide Hierarchy either it can be Company/Account/Dept/Cost_Center>

       AND tree.tree_version_id =

              (SELECT tree_version_id

                 FROM fnd_tree_version_vl

                WHERE tree_code = 'Company Hierarchy'

                      AND SYSDATE BETWEEN effective_start_date AND effective_end_date)

START WITH tree.parent_tree_node_id IS NULL

CONNECT BY PRIOR tree_node_id = parent_tree_node_id

Query to fetch the Accounting Period Status for all applications

SELECT fa.application_short_name,

       fa.application_id,

       fat.application_name,

       gps.set_of_books_id,

       gps.period_name,

       gps.ledger_id,

       gl.name,

       gps.created_by,

       gps.creation_date,

       gps.period_num,

       gps.period_year,

       gps.closing_status,

       DECODE (gps.closing_status,

               'O', 'Open',

               'C', 'Closed',

               'F', 'Future',

               'N', 'Never',

               gps.closing_status)

          gl_status,

       gps.start_date,

       gps.end_date,

       gps.last_updated_by,

       gps.last_update_date

  FROM fnd_application    fa,

       fnd_application_tl fat,

       gl_period_statuses gps,

       gl_ledgers         gl

 WHERE     1 = 1

       AND fat.language = 'US'

       AND fa.application_id = fat.application_id

       AND fa.application_id = gps.application_id

       AND gps.ledger_id = gl.ledger_id