Pages

Sunday, August 21, 2016

Oracle General Ledger – Overview of Table Structure

This document aims at
elucidating the technicaltable structure of the Oracle Financials general ledger module.

Transactions from
across various Oracle subledgers/External Source systems are accounted through
the Accounting Engine in Oracle R12 through a set of Accounting rules where
Journal Entry Debit/Credits are determined and Posted into General Ledger
module through Journal Import.

The journal Import
interface uses the GL_INTERFACE table as a bridge between external systems and
the Oracle General Ledger base tables. For each journal line that needs to be
imported, a single record is inserted into GL_INTERFACE table.

For transfers
initiated from Oracle sub ledgers, the system automatically populates the
GL_INTERFACE table, and then starts the journal import process.

GL Data Model:

GL_JE_BATCHES - Journal Batches

GL_JE_HEADERS - Journals

GL_JE_LINES - Journal Lines

GL_BALANCES - Balances for every
code combination, currency and period

A journal batch is a
group of related journals that are posted together. The period in which the
journals belong is stored at batch level. Each journal in a batch gives rise to
a separate record in the GL_JE_HEADERS. A journal is a group of journal lines
that balance. The sum of the debits is equal tothe sum of credits.
The currency code is stored at the journal level. The GL_JE_LINES table holds a
record for each journal line. The lines record theaccounted amounts,
either debit or credit, and the account code combination that the amount will
be posted to.

GL_LEDGERS - A row for each Ledger

GL_PERIODS - A row for each
calendar period

GL_CODE_COMBINATIONS - All the Account Flex
field segment values

FND_CURRENCIES - A row for each
currency

GL_BALANCES - Balances for every
account code, currency, and period combination.

GL Posting Process:

The posting process
takes a journal batch and updates the account balances in GL_BALANCES according
to all the individual debits and credits that are contained in the GL_JE_LINES
tables belonging to that journal batch. Once the journal batch has been posted
, the attribute GL_BATCHES.POSTED is set to Yes, so that it cannot be posted a
second time. The GL_BALANCES is used to improve performance.

Many processes such
as standard reporting and all FSG reporting require the use of account balances
that are contained in the GL_BALANCES.

GL_BALANCES – Stores actual,
budget, and encumbrance balances for detail and summary accounts. This table stores
functional currency, foreign currency, and statistical balances for each accounting
period that has ever been opened.

ACTUAL_FLAG is either
A, B, or E for actual, budget, or encumbrance balances, respectively. If
ACTUAL_FLAG is B, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is E, then
ENCUMBRANCE_TYPE_ID is required.

GL_BALANCES stores
period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns.
The table stores the period beginning balances in BEGIN_BALANCE_DR and
BEGIN_BALANCE_CR.

An accounts
yeartodate balance is calculated as BEGIN_BALANCE_DR BEGIN_BALANCE_CR + PERIOD_NET_DR PERIOD_NET_CR. Detail and summary foreign
currency balances that are the result of posted foreign currency journal
entries have TRANSLATED_FLAG set to R, to indicate that the row is a candidate
for revaluation.

For foreign currency
rows, the begin balance and period net columns contain the foreign currency
balance, while the begin balance and period net BEQ columns contain the
converted functional currency balance. Detail foreign currency balances that
are the result of foreign currency translation have TRANSLATED_FLAG set to Y or
N. N indicates that the translation is out of date (i.e., the account needs to
be retranslated). Y indicates that the translation is current.

Summary foreign
currency balances that are the result of foreign currency translation have
TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not
NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS
column is not used.

Ledger Tables:

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

GL_CODE_COMBINATIONS - Stores
valid account combinations for each Accounting Flexfield structure within your
Oracle General Ledger application. Associated with each account are certain
codes and flags, including whether theaccount is enabled, whether detail posting or detail budgeting is
allowed, and others.

Segment values are stored in the SEGMENT columns. Note that each
Accounting Flexfield structure may use different SEGMENT columns within the
table to store the flexfield value combination. Moreover, the SEGMENT columns
that are used are not guaranteed to be in any order.

The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores
information about which column in this table is used for each segment of each
Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = Y and
TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = N and TEMPLATE_ID
NULL.

Consolidations &
Conversion Tables:

GL_CONSOLIDATION:Stores
information about your consolidation mappings. Each row includes a mapping's
ID, name, description, and other information. This table corresponds to the
first window of the Consolidation Mappings form.You need one
row for each consolidation mapping you define.

GL_CONSOLIDATION_ACCOUNTS:Stores the
account ranges that you enter when you consolidate balances using the Transfer
Consolidation Data form. This table corresponds to the Account Ranges window of
the Transfer Consolidation Data form.

GL_DAILY_RATES:Stores the
daily conversion rates for foreign currency transactions. It replaces the
GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting
between two currencies for a given conversion date and conversion type.

GL_DAILY_BALANCES:Stores daily
aggregate balances for detail and summary balance sheet accounts in sets of
books with average balances enabled.

Journal Tables:

GL_JE_BATCHES stores journal
entry batches. Each row includes the batch name, description, status, running
total debits and credits, and other information. This table corresponds to the
Batch window of the Enter Journals form. STATUS is 'U' for unposted, 'P' for
posted, 'S' for selected, 'I' for in the process of being posted. Other values
of status indicate an error condition. STATUS_VERIFIED is 'N' when you create
or modify an unposted journal entry batch. The posting program changes
STATUS_VERIFIED to 'I' when posting is in process and 'Y' after posting is
complete.

GL_JE_HEADERS stores journal
entries. There is a one-to-many relationship between journal entry batches and
journal entries. Each row in this table includes the associated batch ID, the
journal entry name and description, and other information about the journal
entry. This table corresponds to the Journals window of the Enter Journals
form. STATUS is 'U' for unposted, 'P' for posted. Other statuses indicate that
an error condition was found.

GL_JE_LINES : Stores the journal
entry lines that you enter in the Enter Journals form. There is a one-to-many
relationship between journal entries and journal entry lines. Each row in this
table stores the associated journal entry header ID, the line number, the
associated code combination ID, and the debits or credits associated with the
journal line. STATUS is 'U' for unposted or 'P' for posted.

GL_JE_SOURCES: Stores journal
entry source names and descriptions. Each journal entry in your Oracle General
Ledger application is assigned a source name to indicate how it was created.
This table corresponds to the Journal Sources form.
GL_JE_CATEGORIES_TL stores journal entry categories. Each row includes the
category name and description. Each journal entry in your Oracle General Ledger
application is assigned a journal entry category to identify its purpose. This
table corresponds to the Journal Categories formPeriod Tables:

GL_PERIODS : Stores information
about the accounting periods you define using the Accounting Calendar form.
Each row includes the start date and end date of the period, the period type,
the fiscal year, the period number, and other information. There is a
one-to-many relationship between a row in the GL_PERIOD_SETS table and rows in
this table.

GL_PERIOD_SETS Stores the
calendars you define using the Accounting Calendar form.

GL_PERIOD_TYPES stores the period
types you define using the Period Types form. Each row includes the period type
name, the number of periods per fiscal year, and other information.
YEAR_TYPE_IN_NAME is 'C' for calendar or 'F' for fiscal. This determines the
system-assigned name of your accounting period in the Accounting Calendar
form.

GL_AUTHORIZATION_LIMITS stores
information about authorization limits for employees. Each row contains an
employee and the employee's authorization limit. This table corresponds to the
Journal Authorization Limits window of the Journal Authorization Limits
form

GL_APPLICATION_GROUPS replaces
FND_APPLICATION_GROUPS. It holds rows for individual application product
groups. For each application product group listed in this table, Oracle General
Ledger's Accounting Calendar form maintains a separate set of accounting period
statuses. Thus for each period defined in the Accounting Calendar form, a row
is inserted into GL_PERIOD_STATUSES for each row in GL_APPLICATION_GROUPS.

Revaluation
Tables

GL_REVALUATIONS Stores Revaluation
definitions. Each row includes a revaluation?s id, name, description, the
corresponding set of books, the currency or currencies to be revalued, and
other revaluation options. They include the currency conversion options, the
unrealized gain account, the unrealized loss account, and the automatic post
flag. There is a one-to-many relationship from each revaluation stored in this
table to the revaluation account ranges stored in
GL_REVAL_ACCOUNT_RANGES.

GL_REVAL_ACCOUNT_RANGES stores the
account ranges to be processed for a revaluation. Each row includes the
revaluation id, the account range and two flags to indicate whether the
balancing and natural account segments are parent values that should be
expanded within the specified account range. Each account range stored in this
table should be related to one and only one revaluation defined in
GL_REVALUATIONS.

GL_RX_TRIAL_BALANCE_ITF stores the
data for country-specific RX trial balance reports. Each time a
country-specific RX trial balance report is run, a new set of data is inserted
into this table.

GL_SUSPENSE_ACCOUNTS stores the
additional suspense accounts you have specified for each source and category.
This is the base table for the Suspense Accounts form in your Oracle General
Ledger application. The posting program in your Oracle General Ledger
application uses the suspense account you specify in the Suspense Accounts form
to balance journal entries where running debits and running credits are not
equal.

Budget Tables:

GL_BUDGETS stores information
about your budgets. Each row includes a budget's name, first and last periods,
date created, and status. This table corresponds to the Define Budget form.
Oracle General Ledger supports only one budget type ('STANDARD'), so you can
uniquely identify a row with only the budget name. The CURRENT_VERSION_ID
column is not currently used.

GL_BUDGET_TYPES stores information
about budget types. Oracle General Ledger supports only one budget type,
'STANDARD'. Therefore, this table always contains only one row. This table has no
foreign keys other than the standard Who columns

GL_BUDGET_ASSIGNMENTS stores the
accounts that are assigned to each budget organization. Each row includes the
currency assigned to the account and the entry code for the account. The entry
code is either 'E' for entered or 'C' for calculated. This table corresponds to
the Account Assignments window of the Define Budget Organization form.

GL_BUDGET_INTERIM is used internally
by Oracle General Ledger applications to post budget balances to the GL_BALANCES
table. Rows are added to this table whenever you run the budget posting
program. The budget posting program updates the appropriate budget balances in
GL_BALANCES based on the rows in this table, and then deletes the rows in this
table that it used.

Interface
Tables:

GL_INTERFACE: It is used to
import journal entry batches through Journal Import. You insert rows in this
table and then use the Import Journals window to create journal batches.

GL_INTERFACE_CONTROL: It is used to
control Journal Import execution. Whenever you start Journal Import from the
Import Journals form, a row is inserted into this table for each source and
group id that you specified. When Journal Import completes, it deletes these
rows from the table.

GL_BUDGET_INTERFACE: It is used to
upload budget data into your Oracle General Ledger application from a
spreadsheet program or other external source. Each row includes one fiscal
year’s worth of budget amounts for an account.