11i Oracle General Ledger Technical Document.

 原文地址: http://arunrathod.blogspot.com/2009/02/11i-oracle-general-ledger-technical.html

 

 Sets of Books and Charts of Accounts govern Oracle’s General Ledger. The set of books keeps what   currency, chart of accounts structure and calendar that the book will use. The main tables for this are
GL_SETS_OF_BOOKS
FND_ID_FLEX_STRUCTURES

 

JOURNAL ENTRIES
Journal Entries can be added several ways. By upload from and external source, upload through ADI, transfer from the various modules, or manually keyed into the application from the form.

Manual Entry - The responsibility that the user is in for the entry determines the chart of accounts structure and set of books. The user will enter a Batch name and period (which will default to the current open period). The user will then enter a header record with a description, and a journal category chosen from the list of valid journal categories. The user may also override the currency defaulted from the set of books. This will cause the entry to be booked to that currency but it will translate to the functional currency of the set of books. To translate, it will use the data from the GL_DAILY_RATES table. The user will last enter the lines for the journal. The main tables needed for this are:
GL_CODE_COMBINATIONS
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_CATEGORIES
GL_DAILY_RATES (If needed)

Imported Journals - Journals can also come into the general ledger from the other financial modules or by an integration tool (either custom built of ADI). The path for all of these types of entries is the same. Data flows through the GL_INTERFACE where is groups the source into batches, headers and lines based on where the data originates. When the Journal Import process is submitted either manually as a concurrent process or by a transfer process from the other modules. A journal batch, header and lines are created in the base tables exactly like a fully manual journal entry. The processes that are submitted from the other modules to transfer data into the GL are
Payables Import to General Ledger
Create Journal Entries (Fixed Assets)
PRC: Interface Revenue to General Ledger (Projects)
Interface Receivables to General Ledger

Main GL tables
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_SOURCES
GL_JE_CATEGORIES
GL_SETS_OF_BOOKS
GL_CODE_COMBINATIONS
GL_PERIODS
GL_CURRENCIES

JOURNAL POSTING
When the user has completed a balance journal entry or the import process completes with no errors, the entries can be posted. The posting is a concurrent process submitted by the post form. When the entry is posted, the data is copied from the base tables into a temporary table called GL_POSTING_INTERIM. Here the program will use the information to group all lines with the same Currency, Code Combination, Set of Books, and Period to be used to update GL_BALANCES. The balances table will be updated with the debits and credits. The batches, headers and lines tables will also be updated for posting status. When the post is complete, the posting interim table is cleared.

Main tables
GL_POSTING_INTERIM
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_BALANCES

BALANCE TRANSLATIONS
Balances that require translation into one currency for reporting and/or consolidation purposes must run the translation process. This process requires that there be a period rate entered for set of books functional currency to the translation currency, i.e. EUR to USD. This rate is entered manually using the period rates form for each set of books. The user will then submit a process using the translation form to translate the balances to the new currency. The process will perform the following query to determine the balances available for translation.

SELECT DISTINCT CC.SEGMENT1
FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL
WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND GBAL.ACTUAL_FLAG = (user entered)
AND GBAL.SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND GBAL.PERIOD_NAME = (user entered)
AND GBAL.CURRENCY_CODE = (functional currency for the set of books)
AND GBAL.TEMPLATE_ID IS NULL
AND CC.DETAIL_POSTING_ALLOWED_FLAG = 'Y'

SELECT EARLIEST_EVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR
FROM GL_TRANSLATION_TRACKING
WHERE BAL_SEG_VALUE = :bal_seg_value
AND TARGET_CURRENCY = (user entered)
AND SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND AVERAGE_TRANSLATION_FLAG = 'N'
AND ACTUAL_FLAG = 'A'

The process will also retrieve the historical rates for equity accounts from the GL_HISTORICAL_RATES table for the prior period and insert new records based on a profile option set for the module (Year to Date or Period To Date).

The process will then insert into a temporary table all of the balances to be translated called the GL_TRANSLATION_INTERIM. From here the process will translate the balances and insert those records into the GL_XLATE_POSTING_INTERIM table. Last the data will be inserted into or updated in the GL_BALANCES table.

Main tables
GL_PERIOD_RATES
GL_HISTORICAL_RATES
GL_TRANSLATION_TRACKING
GL_TRANSALATION_INTERIM
GL_XLATE_POSTING_INTERIM
GL_BALANCES

 

 

以下摘制于:http://www.exforsys.com/forum/oracle-apps/96603-general-ledger-technical.html

 

General Ledger – Technical

 Overview
 SOB -Main Tables
 Journals-Tables
 Budgeting-Tables



Opening the Period
|
Enter Transactions
(Gl_JE_BATCHES,GL_JE_HEADERS,GL_JE_LINES) |
Review & Modify
|
Post (Gl_Balances)
|
Review/Revalidate/Report
|
Close the Period
Enter in GL Imported From Subledger
(Allocations/Adjustments) (Oracle Subledgers & Non | Oracle systems)
|-------------------------------|
Transactions Transactions
(Manually keyed) (Automatically Generated)
|
1. Reversal JV(Nullifying Impact of Wrong Entered JV)
2. Recurring JV /Formula JV (Rent,Paper etc repeated …)
3. Allocations (Special Case of Recurring JV)
4. Revaluation (Rev. is adjustment for Tr.currencies /Acc currency)
5. Consolidations (Dummy parent balance for Child accounts)


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.

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.

ENCUMBRANCE_TYPES
Stores information about encumbrance types, including their IDs and descriptions. This table corresponds to the Encumbrance Types form. This table has no foreign keys, other than the standard Who columns.

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.

Over view of Journals


Journals are of following types
1) Statistical JV (UOM & Quantity are must, Curr as STAT)
2) Mixed JV ( UOM,Quantity & Cr/Db accounts )
3) Monetary JV (Credit/Debits are required)
4) Reversal Journal (Reversal Journal reverse JV which r marked for Reversal)
If JV not posted, just open the JV and make amt as Zero.
5) Recurring JV: I) Standard- Db Rent a/c 10000
ii) Formula - Db Act1 (Form. To cal amt)
iii) Skeleton-Db Act1 Amt based on case to case user enters)
6) Mass Allocation (special Type of Recurring JV)


Journal Tables
 Journals
GL_INTERFACE (For Journal Import) 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. For a complete description of how to load this table, see the Oracle General Ledger User Guide.

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_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_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



Journal & Period Tables
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_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 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_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_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.


Budgeting
Budgeting is possible in Two ways
1) Online Budgeting Control
2) Post mortem Verification with transactions.
Setting up Budget is a three step process:
1) Define Budget (Name , Duration)
2) Define Budget Organization (A group of A/c heads(ranges) for each range we define a parameter
3) Enter Budget Amounts for Budget Organization.
Budget Tables
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_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.