Important General Ledger Tables and detail information

Important General Ledger Tables and detail information

Step 1: Transfer to General Ledger

After transferring from Sub Ledger to GL you can import all the transactions, validate, review and post the journals to update account balance.

 GL_INTERFACE                      -- GL Interface Information

GL_INTERFACE_HISTORY stores the rows that are successfully

imported from the GL_INTERFACE table through Journal Import. You

use this information for historical reference only. Your Oracle General

Ledger application adds rows to this table every time you successfully

run Journal Import with the Archive Journal Import Data option

enabled. Your Oracle General Ledger application does not use the

information stored in this table.                       

 

 GL_INTERFACE_CONTROL             -- GL Interface control Information

GL_INTERFACE_CONTROL 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_INTERFACE_HISTORY             -- GL Interface history Information

GL_INTERFACE_HISTORY stores the rows that are successfully

imported from the GL_INTERFACE table through Journal Import. You

use this information for historical reference only. Your Oracle General

Ledger application adds rows to this table every time you successfully

run Journal Import with the Archive Journal Import Data option

enabled. Your Oracle General Ledger application does not use the

information stored in this table.

 

 GL_IMPORT_REFERENCES             -- Sub Ledger & GL interlink Information

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.

Step 2: Journal Import

 GL_JE_BATCHES                    -- GL Batches Information

 

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                    -- GL Headers Information

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. A complete list is below.

 

 GL_JE_LINES                       -- GL Lines Information

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_TL (GL_JE_SOURCES (VIEW))    -- GL Source Information

GL_JE_SOURCES_TL 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. This table has no foreign keys other than the standard Who columns.

 

GL_JE_CATEGORIES _TL (GL_JE_CATEGORIES (VIEW))  -- GL Categories Information

 

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

form. This table has no foreign keys, other than the standard Who columns.

 

GL_LEDGERS                   -- GL Ledger Information

GL_CODE_COMBINATIONS (GL_CODE_COMBINATIONS_KFV (VIEW))     -- GL Code combination 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 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.

 

Advance Journal Table Details

Define & Generate Recurring Journal Table Details

GL_RECURRING_BATCHES

GL_RECURRING_BATCHES stores your journal entry formula batches and your budget formula batches. Each row includes the batch name, description, and other information. This table corresponds to the Recurring Batch section of the Define Recurring Journal Formula form and the Define Budget Formula form. BUDGET_FLAG is ’Y’ for a budget formula batch and ’N’ for a journal entry formula batch.

GL_RECURRING_HEADERS

GL_RECURRING_HEADERS stores your journal entry formula headers and your budget formula headers. This table corresponds to the Journal Entry section of the Define Recurring Journal Formula form and the Define Budget Formula form.

GL_RECURRING_LINES

GL_RECURRING_LINES stores the target lines of your recurring journal and recurring budget formulas. This table corresponds to the Journal Entry Line window of the Define Recurring Journal Formula form and of the Define Budget Formula form.

GL_RECURRING_LINE_CALC_RULES

GL_RECURRING_LINE_CALC_RULES stores the formula lines of your recurring journal and recurring budget formulas. This table corresponds to the Formula section of the Define Recurring Journal Formula and the Define Budget Formula forms.

 

Define & Generate Mass Allocation Journal Table Details

GL_ALLOC_BATCHES

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.

You need one row for each MassAllocation or MassBudget batch. ACTUAL_FLAG values are ”A” or ”E” for MassAllocation batches and ”B” for MassBudget batches, denoting the balance type associated with your allocation entries.

VALIDATION_STATUS is ’N’ when you first define, or have recently changed but not validated a MassAllocation or MassBudget batch, and ’V’ after your batch has been successfully validated. If you submit your batch for validation, and it completes with errors, your VALIDATION_STATUS column contains the value ’E’.

VALIDATION_REQUEST_ID is the concurrent request id associated with the MassAllocation Validation Program run that validated your batch.

 

GL_ALLOC_FORMULAS

GL_ALLOC_FORMULAS stores information about MassAllocation and MassBudget formulas. Each row includes a formula’s ID, name, and description. There is a one–to–many relationship between MassAllocation/MassBudget batches and MassAllocation /MassBudget formulas. This table corresponds to the Formula window of the Define MassAllocations and Define MassBudgets forms. You need one row for each MassAllocation or MassBudget formula. VALIDATION_STATUS is ’N’ when you first define, or have recently changed but not validated a MassAllocation or MassBudget formula, and ’V’ after your formula has been successfully validated. If you

submit a formula for validation, and it completes with errors, your VALIDATION_STATUS contains the value ’E’. RUN_SEQUENCE is not used.

 

GL_ALLOC_FORMULA_LINES

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. Each row includes the allocation formula ID,line number, and other information about the line. Line numbers 1, 2, 3,4, and 5 correspond to rows A, B, C, T, and O, respectively, on the Define MassAllocations and Define MassBudgets forms. Segment values are stored in the SEGMENT columns appropriate to the chart of accounts

structure. Segment types (C, L, or S) are stored as a concatenated string

in the SEGMENT_TYPES_KEY column. LINE_TYPE is ’E’ for lines 1, 2,and 3, ’T’ for line 4, and ’O’ for line 5. OPERATOR is always ’E’ and is not used. SEGMENT_BREAK_KEY is not used.

 

Define & Generate Consolidation Journal Table Details

GL_CONSOLIDATION_ACCOUNTS

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_CONS_SET_ASSIGNMENTS

GL_CONS_SET_ASSIGNMENTS stores information about the

consolidation mappings in the consolidation sets that you define in the Consolidation Mapping Set form. For each mapping in a mapping set, there will be one row in this table

 

GL_CONSOLIDATION_SETS

GL_CONSOLIDATION_SETS stores the consolidation sets you define in the Consolidation Mapping Sets form. Each row contains the name of the consolidation mapping set, the ID of the parent set of books being consolidated to, the method of consolidation (Balances or Transactions),

and other information. There is a one–to–many relationship between rows in this table and rows in the GL_CONS_SET_ASSIGNMENTS table.

 

GL_CONSOLIDATION

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. FROM_SET_OF_BOOKS_ID is the

subsidiary set of books ID. TO_SET_OF_BOOKS_ID is the parent set of

books ID. METHOD is ’B’ for balances or ’T’ for transactions.

FROM_LOCATION and FROM_ORACLE_ID are not used in this

version of your Oracle General Ledger application

 

GL_CONSOLIDATION_AUDIT

GL_CONSOLIDATION_AUDIT stores consolidation information when you transfer consolidation data in audit mode. You get one row in this table for each consolidation line that is fed to Journal Import. You can delete rows from this table using the Purge Consolidation Audit Data form. The Segment1 through Segment30 columns store the destination code combination of the consolidation line.

 

GL_CONS_FLEXFIELD_MAP

GL_CONS_FLEXFIELD_MAP stores the account rules you define when you define a consolidation mapping. Each row contains a range of child accounts and the parent account they map to. This table corresponds to the Account Rules window of the Consolidation Mappings form.

GL_CONSOLIDATION_HISTORY

GL_CONSOLIDATION_HISTORY stores information about the

consolidation data transfers you’ve made. One row is inserted into this table each time you transfer consolidation data. FROM_PERIOD_NAME is the subsidiary’s accounting period name. TO_PERIOD_NAME is the parent’s accounting period name. ACTUAL_FLAG is ’A’ if you transfer actual amounts and ’B’ if you transfer budget amounts.

Step 11: Posting

GL_BALANCES                        -- GL Balance Information

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_PERIODS                   -- GL Period Information

 

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_TYPES             -- GL Period Information

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_PERIOD_STATUSES

 

GL_PERIOD_STATUSES stores the statuses of your accounting periods. Each row includes the accounting period name and status. Other applications maintain their calendars in this table, so each row also includes the relevant application identifier. CLOSING_STATUS is either ’O’ for open, ’F’ for future enterable, ’C’ for closed, ’P’ for permanently

closed, or ’N’ for never opened. Note that you cannot successfully open a period in your Oracle General Ledger application by changing a period’s status to ’O’ if it has never been opened before. You must use the Open and Close Periods form to properly open a period.

 

GL_PERIOD_SETS

GL_PERIOD_SETS stores the calendars you define using the Accounting Calendar form. Each row includes the name and description of your calendar. There is a one–to–many relationship between a row in this table and rows in the GL_PERIODS table. This table has no foreign keys other than the standard Who columns.

GL_BUDGET_INTERFACE

Use GL_BUDGET_INTERFACE 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. When you load this table, you must supply all NOT NULL columns with data. In addition, you must supply a valid account combination in the SEGMENT columns appropriate to your chart of accounts structure. Finally, you must supply the budget amounts in the appropriate AMOUNT columns. For more information about this table, see the Oracle General Ledger User Guide

 

 

GL_BUDGETS

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_ENTITIES

 

GL_BUDGET_ENTITIES stores information about your budget

organizations. Each row includes the organization name, description, set of books ID, and optional password. Each row also stores the

segment display order for the Enter Budget Amounts form. This table corresponds to the Define Budget Organization window of the Define Budget Organization form.

 

GL_BUDGET_ASSIGNMENTS

 

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_ENTITY_BUDGETS

 

GL_ENTITY_BUDGETS stores information about the relation between budget organizations and budgets. In particular, this table tells you whether a budget is frozen with respect to a budget organization. This table corresponds to the Organizations section of the Freeze Budgets form. FROZEN_FLAG is ’Y’ if a budget is frozen or ’N’ if it is not frozen. You need one row for each combination of budget organization and budget.

 

GL_BUDGET_VERSIONS

 

GL_BUDGET_VERSIONS stores information about budget versions. Oracle General Ledger does not support multiple versions of the same budget. Therefore, there is one row in this table for each row in the GL_BUDGETS table. The column VERSION_NUM is always set to 1.

 

GL_BUDGET_RANGE_INTERIM

GL_BUDGET_RANGE_INTERIM is used internally by Oracle General Ledger applications to post budget balances that you enter using the Enter Budget Amounts form. When you enter budget amounts using the Enter Budget Amounts form, you add rows to this table. The budget posting program deletes those rows when it posts your budget amounts.

 

GL_BUDGET_BATCHES

GL_BUDGET_BATCHES stores information about your budget formula batches. For each budget, it records when you last ran the formula batch and for which accounting periods you ran it. This table also tells you if your formula batch is frozen with respect to a budget. STATUS is ’F’ for frozen or ’U’ for unfrozen

 

GL_BUDGET_PERIOD_RANGES

GL_BUDGET_PERIOD_RANGES stores the period ranges that you can budget to using the Enter Budget Amounts form. Each open year of your budget is divided into one or more period ranges. Each range spans up to 13 periods, and a range cannot cross a fiscal year. The Open Budget Year program inserts one row into this table for each period range in the year that you open. The Open_Flag column is always ’O’.

 

GL_BC_PACKETS

 

GL_BC_PACKETS is Oracle General Ledger’s main table for the

Budgetary Control feature. This table stores the budgetary control packets that need to be approved before creating journal entries. A budgetary control packet contains information about a set of transaction(s) you want to approve before entering it into the system.

The Funds Checker program evaluates these packets and updates the RESULT_CODE column in GL_BC_PACKETS with a lookup code signifying the result of the funds check.

Once a packet passes its funds check and a journal entry batch is created, the packet is removed from GL_BC_PACKETS.

 

GL_BUDGET_ASSIGNMENT_RANGES

GL_BUDGET_ASSIGNMENT_RANGES stores the account ranges that

you want to assign to a budget organization. This table corresponds to the Account Ranges window of the Define Budget Organization form.

 

GL_BUDGET_FROZEN_RANGES

GL_BUDGET_FROZEN_RANGES stores the ranges of accounts that

you enter in the Account Ranges window of the Freeze Budgets form

 

GL_BUDGET_TYPES

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.

 

FSG Report Table Details

RG_REPORT_AXIS_SETS

RG_REPORT_AXIS_SETS stores information about all of the row sets and column sets currently defined in Financial Statement Generator. Each row includes an axis set identifier, a row set name or a column set name, and a structure identifier to assign a specific chart of accounts to the row set or column set. Financial Statement Generator uses the report axis sets as the basis which many report axes, axis contents, and report calculations can be linked for later reporting.

RG_REPORT_AXES

RG_REPORT_AXES stores information about all the rows and columns currently defined in Financial Statement Generator. Each row includes an axis set identifier to link the row or column to a row set or column set, a name, a currency and a parameter number to link the row or column to a specific budget or encumbrance. Each row also contains customizable formatting options. Each row contains information about

one particular row or column currently defined in Financial Statement Generator.

RG_REPORT_STANDARD_AXIS

 

RG_REPORT_AXIS_CONTENTS

RG_REPORT_AXIS_CONTENTS stores information about all of the

account assignments to rows and columns. Each row includes a specific account range, and an axis set identifier to link the account range to a specific row or column set. Each row also contains an axis sequence number to link the account range to a specific row or column.

RG_REPORT_CALCULATIONS

RG_REPORT_CALCULATIONS stores information about all of the

calculations defined in Financial Statement Generator.

Each row includes an axis set identifier and an axis sequence which are used to link the calculations to a particular row or column. It also contains information about one specific calculation operation defined in

Financial Statement Generator.

RG_REPORT_EXCEPTIONS

RG_REPORT_EXCEPTIONS stores information about the conditions that must be met in order for each exception to be true. Each row contains an axis set identifier, an axis sequence number, an exception identifier, and a condition.

Financial Statement Generator uses this information to link exception conditions and the exception flag to a particular column in a column set. You can define only one flag and one set of conditions per column.

RG_REPORT_EXCEPTION_FLAGS

RG_REPORT_EXCEPTION_FLAGS stores information about all of the symbols used to indicate exceptions and descriptions of the symbols. Each row contains information about one particular symbol used to indicate an exception, the description of the symbol, an axis set identifier, an axis sequence number, and an exception identifier.

Financial Statement Generator uses this information to link the exception f

 

RG_REPORTS

RG_REPORTS stores information about all of the reports currently defined in Financial Statement Generator, including the row and column sets associated with the report, any content set and row order that Financial Statement Generator uses, units of measure, and the budgets

or encumbrances associated with the report. Each row includes a name, row set identifier, column set identifier, content set identifier, row order identifier and the unit of measure (currency) that each report uses. Each

row contains information about one report defined in Financial Statement Generator.

Financial Statement Generator uses this information to link a row, a column, and an optional content set and row order together into one report that you can submit for a report request. If a budget or encumbrance amount type is assigned to a row or column in a row set or column set, a budget or encumbrance must also be linked to the report.

If a currency is assigned to particular rows or columns in a row set or column set, the currency assigned to the report that contains that row set and/or column set overrides the currencies in the rows or columns.

 

RG_REPORT_CONTENT_SETS

RG_REPORT_CONTENT_SETS stores information about all of the

content sets currently defined in Financial Statement Generator. Each row includes a content set identifier, a name, and a structure identifier that links account assignments to the content set. Financial Statement

Generator uses this information to link the content set to content overrides and then uses the combination of the content set and content overrides to override specific row or column account segment values atthe report level.

RG_ROW_SEGMENT_SEQUENCES

 

RG_ROW_SEGMENT_SEQUENCES stores different segment sequence

information currently defined in Financial Statement Generator. Financial Statement Generator uses this information to arrange segments and to link the segment arrangement in a particular row order.

 

RG_ROW_ORDERS

RG_ROW_ORDERS stores information about how to expand rows in a report. Each row includes a row order identifier, a row rank type (ascending or descending), the name and sequence number of the column used to rank the report and the row order name used to identify that particular row order. Financial Statement Generator uses this information to order the values in an expanded row, by linking that row order to a particular row and/or row ranking sequence.

RG_REPORT_DISPLAY_SETS

RG_REPORT_DISPLAY_SETS stores information about report display sets currently defined in Financial Statement Generator. Each row includes a report display set identifier, a report display set name, a row set identifier and a column set identifier. Financial Statement Generator uses the Display Set form to define report display sets for specific row and column sets or generic report display

sets which work with any row and column set. You can even create report display sets which work with only one row set but without any column set or vice versa. You cannot delete a report display set if it is used in any report

RG_REPORT_DISPLAY_GROUPS

RG_REPORT_DISPLAY_GROUPS stores report display groups

information which is used in report display sets. Each row includes a row set identifier, a column set identifier and a range of sequences.

RG_REPORT_DISPLAYS

RG_REPORT_DISPLAYS stores report display group assignments to report display sets. Each row includes a sequence number, a row group, a column group and a description. It also includes a display flag to determine whether the value will be displayed or not.

RG_REPORT_REQUESTS

RG_REPORT_REQUESTS stores information about the reports in a report set in Financial Statement Generator. It also tracks information about the report submissions.

Each row includes a report identifier, a row set identifier, a column set identifier, and a sequence number for a report in a report set. Financial Statement Generator uses this information to join all of the report components together for creating financial statements. The report request may belong to, and be submitted for, analysis as part of a report set, or the report request may be submitted as an independent request.