Oracle GL Key Tables

GL_SETS_OF_BOOKS
GL_SETS_OF_BOOKS stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES
GL_IMPORT_REFERENCES stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction?s origin by entering ?Yes? in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ?Yes?, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES
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. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table

GL_PERIODS
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_JE_HEADERS
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. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_LINES
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_BATCHES
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_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 account’s year–to–date 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 re–translated). ’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.

GL_CODE_COMBINATIONS
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 the account 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.

GL_ACCOUNT_HIERARCHIES
stores lists of the detail accounts associated with each summary account. When you create a summary account, the list of its detail accounts is stored in this table, one detail account per row. This table stores one such list for each summary account.

GL_ALLOC_BATCHES
stores information about MassAllocation and MassBudget batches. Each row includes a batch’s unique ID, name, status, and description. This table corresponds to the Define MassAllocations and Define MassBudget windows of the Define MassAllocations and Define MassBudgets forms.

GL_ALLOC_FORMULAS
stores information about MassAllocation and MassBudget formulas.

GL_ALLOC_FORMULA_LINES
stores information about MassAllocation and MassBudget formula lines. This table corresponds to the Formula window of the Define MassAllocations and Define MassBudgets forms. There are four or five lines associated with each MassBudget formula, and there are five lines associated with each MassAllocation formula.

GL_ALLOC_HISTORY
stores one row for each batch produced by generating MassAllocation and MassBudget journals. Use information in this table to determine when you generated your MassAllocation and MassBudget journals, and for which accounting periods.

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.

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_ASSIGNMENTS
stores the accounts that are assigned to each budget organization.

GL_BUDGET_ASSIGNMENT_RANGES
stores the account ranges that you want to assign to a budget organization.

GL_DAILY_RATES_INTERFACE
is the interface you use to create, update, and delete daily conversion rates. Customers should use this table to load rates into the GL_DAILY_RATES table.

Foreign Keys
Primary Key Table Primary Key Column Foreign Key Column
FND_CURRENCIES CURRENCY_CODE FROM_CURRENCY
FND_CURRENCIES CURRENCY_CODE TO_CURRENCY
FND_USER USER_ID USER_ID
GL_DAILY_CONVERSION_TYPES USER_CONVERSION_TYPE USER_CONVERSION_TYPE

Not null columns
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG

GL_IEA_INTERFACE
is used to import data into the Global Intercompany System from external systems. The data entered into this table is processed by the GIS Import program.

Foreign Keys
Primary Key Table Primary Key Column Foreign Key Column
FND_CONCURRENT _REQUESTS REQUEST_ID REQUEST_ID
FND_CURRENCIES CURRENCY_CODE CURRENCY_CODE
GL_CODE_COMBINATIONS CODE_COMBINATION_ID SENDER_CODE_COMBINATION_ID
GL_CODE_COMBINATIONS CODE_COMBINATION_ID SENDER_CODE_COMBINATION_ID
GL_IEA_SUBSIDIARIES SUBSIDIARY_ID SENDER_SUBSIDIARY_ID
GL_IEA_SUBSIDIARIES SUBSIDIARY_ID RECEIVER_SUBSIDIARY_ID
GL_IEA_TRANSACTION_TYPES TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID

NOT NULL Columns
GROUP_ID
TRANSACTION_TYPE_ID
TRANSACTION_STATUS_CODE
CURRENCY_CODE
GL_DATE
SENDER_SUBSIDIARY_ID
RECEIVER_SUBSIDIARY_ID
LINE_TYPE

GL_INTERFACE
is the table you use to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals form to create journal batches. You must supply values for all NOT NULL columns.

GL_SETS_OF_BOOKS,
GL_IMPORT_REFERENCES ,
GL_DAILY_RATES,
GL_JE_LINES,
GL_PERIODS,
GL_JE_HEADERS,
GL_JE_BATCHES,
GL_BALANCES,
GL_CODE_COMBINATIONS