Oracle EBS R12 SQL Queries For Ledgers And Legal Entities

Oracle EBS R12 SQL Queries For Ledgers And Legal Entities

1. GL_LEDGERS
GL_LEDGERS stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information. Some columns in this table are not applicable for ledger sets. In this case, default values would be inserted into these columns for ledger sets.

2. GL_LEDGER_CONFIG_DETAILS
GL_LEDGER_CONFIG_DETAILS stores information about a primary ledger, its related secondary ledgers and legal entities, and the state of each of their setup steps. The object type specifies the type of object stored in this row: primary ledger, secondary ledger, or legal entity. The object id and object name give the defining column and name of the object. Valid steps for objects include setup of ledgers, definition of reporting currencies, assignment of balancing segment values, mapping of primary ledger to secondary ledger, definition of operating units, definition of accounting sequences, definition of intercompany options, definition of intracompany options, and setup of subledger accounting.

3. GL_LEDGER_CONFIG_DETAILS and GL_LEDGER_RELATIONSHIPS
GL_LEDGER_RELATIONSHIPS stores the relationships between ledgers. Each relationship between a primary ledger and a secondary ledger or between a ledger and its reporting currency is stored in one or more rows in this table. GL_LEDGER_RELATIONSHIPS also stores the chart of accounts mapping, conversion, and initialization rules necessary to convert data from the source ledger to its secondary ledger or reporting currency. For upgrade cases, it may contain multiple rows for a single relationship; one row per application or per operating unit for which that relationship is maintained. GL_LEDGER_RELATIONSHIPS also always contains one "self" row for each ledger and for each subledger or journal level reporting currency, even if that ledger has no relationships defined for it.


Following are the SLA views that use the GL_LEDGER_RELATIONSHIPS table:
- XLA_ALT_CURR_LEDGERS_V : This view gives the details of ALC ledgers used by SLA. It gives the relationship between an ALC and its Primary ledger.

- XLA_LEDGER_RELATIONSHIPS_V : This view is supposed to give us all the ledger relationships (ALC to Primary, Secondary to Primary and Primary to Primary) that are used by SLA.

- XLA_SUBLEDGER_OPTIONS_V : This view joins the GL Ledgers tables and SLA setup tables to give a row for each combination of an application and a ledger (Primary and Secondary).


PLease find some sample queries below
1. GL_LEDGERS stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information. Some columns in this table are not applicable for ledger sets. In this case, default values would be inserted into these columns for ledger sets.

select * from gl_ledgers where name like <primary ledger name>;

2. GL_LEDGER_CONFIGURATIONS stores information about a primary ledger. The completion status code indicates the state of the primary ledger. The valid values are CONFIRMED and IN_PROGRESS. CONFIRMED means the primary ledger can be used; IN_PROGRESS means some of the setup has not yet been completed. This table has a one to many relationship to the GL_LEDGER_CONFIG_DETAILS table.

select * from gl_ledger_configurations where configuration_id = <configuration_id returned from first query>;

3. GL_LEDGER_CONFIG_DETAILS stores information about a primary ledger, its related secondary ledgers and legal entities, and the state of each of their setup steps. The object type specifies the type of object stored in this row: primary ledger, secondary ledger, or legal entity. The object id and object name give the defining column and name of the object. Valid steps for objects include setup of ledgers, definition of reporting currencies, assignment of balancing segment values, mapping of primary ledger to secondary ledger, definition of operating units, definition of accounting sequences, definition of intercompany options, definition of intracompany options, and setup of subledger accounting.

select * from gl_ledger_config_Details where configuration_id = <configuration_id returned from first query>;

4.GL_LE_VALUE_SETS stores balancing segment value sets assigned to a legal entity.

select * from gl_le_value_sets where legal_entity_id in(
select object_id from gl_ledger_config_details where configuration_id = <configuration_id returned from first query>
and object_type_code = 'LEGAL_ENTITY');

.
5. GL_LEGAL_ENTITIES_BSVS stores balancing segment values assigned to a legal entity.

select * from gl_legal_entities_bsvs where legal_entity_id in(
select object_id from gl_ledger_config_details where configuration_id = <configuration_id returned from first query>
and object_type_code = 'LEGAL_ENTITY');

6. GL_LEDGER_NORM_SEG_VALS stores information about the balancing segment values that are assigned to a ledger. Each row specifies a balancing segment value, the ledger it is assigned to, the start and end date of the assignment, and the legal entity, if any, that it is assigned to.

select * from gl_ledger_norm_seg_vals
where
ledger_id in (select OBJECT_ID from gl_ledger_config_Details where
configuration_id = <configuration_id returned from first query>
and object_type_code in ('PRIMARY','SECONDARY'))
or legal_entity_id in (select OBJECT_ID from gl_ledger_config_Details where
configuration_id = <configuration_id returned from first query>
and object_type_code in ('LEGAL_ENTITY') );

.
7.GL_LEDGER_SEGMENT_VALUES stores information about the balancing segment values that are assigned to a ledger. Each row specifies a balancing segment value, the ledger it is assigned to, and the start and end date of the assignment. This table stores the flattened information from the GL_LEDGER_NORM_SEGMENT_VALUES table. Each parent segment values stored in the GL_LEDGER_NORM_SEGMENT_VALUES table will be expanded into detail segment values before it is stored in this table.

select * from gl_ledger_segment_values
where
ledger_id in (select OBJECT_ID from gl_ledger_config_Details where
configuration_id = <configuration_id returned from first query>
and object_type_code in ('PRIMARY','SECONDARY'))
or legal_entity_id in (select OBJECT_ID from gl_ledger_config_Details where
configuration_id = <configuration_id returned from first query>
and object_type_code in ('LEGAL_ENTITY') );

8. GL_LEDGER_LE_V

select * from gl_ledger_le_v where ledger_id = &ledger_id
order by legal_entity_name

If you like please follow and comment