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. 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
No comments:
Post a Comment