About Oracle Apps: Basic Concepts in Oracle GL

2.8 Consolidation

Consolidated Set of Books

Consolidation is the period-end process of combining the financial results of separate subsidiaries with the parent organization to form a single, combined statement of financial results. The Global Consolidation System (GCS) provides the flexibility to help you manage your consolidation needs regardless of your organization structure.

Consolidated data is required at the end of year usually when companies prepare their balance sheets. Decide the Global Set of Books Get the Parent SOB and the Subsidiary SOB; keep the Method as Transaction / Balance. Prepare the Mappings, Mapping Sets as we have to map the subsidiary A/C to Parent A/C.

Remember that we have to map the Subsidiary A/C to Parent A/C.

Use Accounting Rules

After defining the Accounting Rules for the Subsidiary Accounts Transfer the Consolidation Data Set. Enter the period , One can define the elimination set to eliminate the transactions .create eliminate set if u want to eliminate Automatic Interfund Eliminations or certain formulae based eli minations can be done.

3.0 Financial Statement Generator: Reporting tool

Generate financial reports, such as statements of revenues, expenditures, and changes in fund balance and balance sheets, based upon data in your general ledger.

Suppose we want to prepare a report as given below

INCOME

Accounts of Income

Other Services xxx

………………… xxx

…………………

TOTAL OF INCOME xxx

EXPENDITURE
Accounts of Expenditure

……………………. xxx

TOTAL EXPENDITURE xxx

NET PROFIT OR NET LOSS XXX

How we will prepare the above report by using FSG –

Define Rows

Line 1 – Heading, No account assignment is required for line 1.

Line 2 – Income Accounts – Go to Account Assignment for line 2

Sign Low High

+ zz.Parent zz.Parent

- If it is a parent it will auto take the Childs

(or We can Include various Income Lines if don’t want to specify Parent)

Line 3 – Total of Income

Before - = and After - =

Go to Calculation Part

Seq Operand Low High

1 + 2 2

This does the total of Income.

Same way we can define the Expenditure A/Cs as well

Suppose we are utilizing line 4 ,5, 6 for Expenditures

Next step is to find out the Net Profit or Loss

Line 7 – Line Item - Net Profits or Loss

Before- = After - =

Calculations

1 + 3 (total Exp) 6 (Total Income)

Define Column Set

Position - 45 (say)

Sequence - 1

Column Name – Amount

Attach the Column Set and the Row Set with the Report

Run the Financial report to get the above output .

GL -> Reports -> Define -> Row Set

Define - > Rows

Same way Define the column set for amount.

Run the Report.

4.0 Major Interfaces:

GL interface – GL_INTERFACE

GL Daily Rates Interface – GL_DAILY_RATES

GL Budget – GL_BUDGETS

Interface Details

4.1 Journal Interface (Overview)

General Steps

- Take sample entries

- Check how the entries are entered manually in APPS's FrontEnd

- Check in front end what mandatory columns have to be entered

Also check what are the columns that APPS's populated .

- Check what data from the sample entries i.e.not entered in the

Front End where for eg. We might have to create or use

Different values.

- During the transaction also check if the user does any manual

Validation or links

GENERAL LEDGER INTERFACE (GL INTERFACE)

Brief Description:

This interface will populate the GL_INTERFACE table. After Populating the GL_INTERFACE Table Journal Import Program should be Executed, Journal Import Program will populate the Base Tables e.g. GL_JE_LINES, GL_JE_HEADERS, GL_JE_BATCHES with the appropriate data. You can use the GL_JOURNAL_IMPORT_PKG and GL_INTERFACE_CONTROL table for Multi Table Import.

Journal Import Program EXE Name is: GLLEZL

- Journal import The Journal Import program requires approximately 1.4 megabytes of memory to run.

- If you want Journal Import to assign sequential numbers to your

Journal entries enable sequential numbering, specifying Automatic as both your numbering and document generation method.

- Disable dynamic insertion. Journal Import runs much faster when it does not have to create new account combinations dynamically.

- To give accounts u can use either segments or code combination id.ONLY used either the segment1, segment2... OR use code combination id for insert data. DO NOT USE BOTH.IN CASE BOTH ARE USED THE segment values are used to identify the account. Journal Import does not allow null values in enabled segments. The segment data has to be of exact length has defined in the segments. If an invalid code combination id is entered

CASE 1 Suspense posting is disabled

The invalid code combination ID is printed in Journal Import Execution Report.

CASE 2 Suspense posting is enabled

Prints only the segment value separators in Journal Import

Execution Report.

RECOMMENDED

Disabled suspense posting if entering code combination id.Journal Import data will be removed from the GL_INTERFACE table after it is successfully imported.

GENERAL LEDGER

SETUP > SYSTEM > CONTROL - to setup the history

List of Mandatory as well as non mandatory columns

1. STATUS - Enter the value NEW for bringing new data in GL

2. SET_OF_BOOKS_ID - You can find a list of valid values in the

SET_OF_BOOKS_ID column of the Sets of Books table

(GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID).

e.g. - SELECT SET_OF_BOOKS_ID, NAME FROM GL_SETS_OF_BOOKS;

3. USER_JE_SOURCE_NAME - journal entry source name list of valid values in > (GL_JE_SOURCES.USER_JE_SOURCE_NAME).

4. USER_JE_CATEGORY_NAME - journal category name list of valid values in > (GL_JE_CATEGORIES.USER_JE_ CATEGORY_NAME).

5. ACCOUNTING_DATE - GL automatically assigns your journal batch to the accounting period that includes your accounting date.

6. If average balance processing is enabled, Effective Date Rules to validate the accounting date against your transaction calendar to determine the transaction’s effective date.

7. CURRENCY_CODE: list of valid values in

(FND_CURRENCIES. CURRENCY_CODE)

8. DATE_CREATED : for info. Only, not reflected in GL application

9. CREATED_BY: Enter an ID that you can use to identify the data coming from your feeder system.

10. ACTUAL_FLAG: Enter the value A for actual amounts, B for Budget

Amounts or E for encumbrance amounts. The value entered here affects the entry in

1. Encumbrance_type_id

2. Budget_version_id

11. ENCUMBRANCE_TYPE_ID: IF 'E' is entered in actual flag.

List of values in > (GL_ ENCUMBRANCE_TYPES. ENCUMBRANCE_TYPE_ID).

SQL to get it > SELECT ENCUMBRANCE_TYPE_ID, ENCUMBRANCE_TYPE

FROM GL_ENCUMBRANCE_TYPES WHERE ENABLED_FLAG = ’Y’;

12. BUDGET_VERSION_ID: if 'B' is entered in actual flag.

13. PERIOD_NAME: Many a times User Parameter.

14ENTERED_DR:, ENTERED_CR:

15. USER_CURRENCY_CONVERSION_TYPE: put as 'USER'

16. CURRENCY_CONVERSION_RATE: Enter or take it from

17. ACCOUNTED_DR: converted dr amt.

18. ACCOUNTED_CR: converted cr amt.

19. REFERENCE1 (Batch Name): Enter the Batch Name.

20. REFERENCE2 (Batch Description):

21. REFERENCE4 (Journal entry name):

22. REFERENCE5 (Journal entry description):

23. REFERENCE6 (Journal entry reference):

24. REFERENCE24

25. GROUP_ID: Any unique string can serve as group id.

26. ATTRIBUTE1 through ATTRIBUTE 10: for descriptive flex fields

Required NULL Columns in the GL_INTERFACE Table

1. REFERENCE3: Do not enter a value in this column.

2. REFERENCE11 through REFERENCE20: Do not enter a value in this Column.

3. TRANSACTION_DATE: Do not enter a value in this column.

4. JE_BATCH_ID: Do not enter a value in this column.

5. JE_HEADER_ID: Do not enter a value in this column.

6. JE_LINE_NUM: Do not enter a value in this column.

7. CHART_OF_ACCOUNTS_ID: Do not enter a value in this column.

8. FUNCTIONAL_CURRENCY_CODE: Do not enter a value in this column.

9. DATE_CREATED_IN_GL: Do not enter a value in this column.

10. WARNING_CODE: Do not enter a value in this column.

11. STATUS_DESCRIPTION: Do not enter a value in this column.

12. DESC_FLEX_ERROR_MESSAGE: Do not enter a value in this column.

13. REQUEST_ID: Do not enter a value in this column.

14. SUBLEDGER_DOC_SEQUENCE_ID: Do not enter a value in this column.

15. SUBLEDGER_DOC_SEQUENCE_VALUE: Used for communication between

General Ledger and the sub ledgers. Do not populate with your own data.

THE SQL USED TO INPUT THE DATA INTO PRE-INTERFACE TABLE

MULTI TABLE IMPORT

Using alternative tables can help you improve performance since

Journal Import more efficiently processes high volumes of data from multiple tables than from the single GL_INTERFACE table. Professionals creating data load routines can choose which interface table to put the data in, and whether the table should be dropped when Journal Import completes successfully.

Prerequisites

1. Data must have both a source and group ID.

To use Multi–Table Journal Import

General Ledger provides you with the Journal Import Package (GL_JOURNAL_IMPORT_PKG) to create a new interface table and Populate the GL_INTERFACE_CONTROL table.

Below are the steps to follow to use Multi–Table Journal Import:

1. Create a new interface table. New interface tables must have the same columns as the GL_INTERFACE table but you can add more

If your needs require.

2. Populate the new interface table with data.

3. Populate the GL_INTERFACE_CONTROL table with one record for each source/group ID combination that was put into the Interface table. Specify a table name that the data is to be retrieved from for Each Combination. Specify what should be done with the data once it has been processed.

4. Start Journal Import using the Import Journals window. Specify each of the source/group ID combinations that you want to Import. If there are multiple tables, Journal Import will be launched multiple times.

5. If Journal Import indicates that the data is erroneous, then correct the data using the Correct Journal Import Data window or delete it using the Delete Journal Import Data window. If You choose to correct it, and then start Journal Import again using Import Journals window.

4.2 Daily Rates

II – Loading Multicurrency Rates

GL_DAILY_RATES INTERFACE:

This interface can be used to load the data in to GL_DAILY_RATES Table.

Eg. When the client is a organization dealing with share market then needs to keep the track of the Daily Currency rates.

General Ledger provides the GL_DAILY_RATES_INTERFACE Table that you can use to automatically insert, update, or delete daily rates in the GL_DAILY_RATES table. General Ledger validates the rows in the interface table before making changes in the GL_DAILY_RATES table.

One can specify a range of conversion dates; the system inserts, updates, or deletes one row in GL_DAILY_RATES for each date in your range.

EG. If the Currency conversion rate is specified for 01-oct-97 to 03-oct-97 then three rows will get inserted In GL_DAILY_RATES Table.

Specify

From To

Currency Currency

JPY USD 01–OCT–97 Spot .0083

JPY USD 02–OCT–97 Spot .0083

JPY USD 03–OCT–97 Spot .0083

As well the Reverse Currency Information also gets inserted automatically

From To

Currency Currency

USD JPY 01–OCT–97 Spot 120.482

USD JPY 02–OCT–97 Spot 120.482

USD JPY 03–OCT–97 Spot 120.482

The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE table.

You do not need to run any import programs. You only need to develop an automated process that populates the interface table with your daily rates information.

The columns in GL_DAILY_RATES_INTERFACE are described below.(columns marked with * are mandatory)

1. FROM_CURRENCY * Source Currency

2. TO_CURRENCY * Target Currency

3. FROM_CONVERSION_DATE * Starting Conversion Date

4. TO_CONVERSION_DATE * Ending Conversion Date

(The difference should not exceed 366 days)

5. USER_CONVERSION_TYPE * Spot / Corporate /

6. CONVERSION_RATE * Currency Conversion Rate

7. MODE_FLAG * - ‘D’ To delete the Matching Rows,

‘I’ – Insert new Rows.

8. INVERSE_CONVERSION_RATE – If not specified gets Inserted automatically.

9. USER_ID The following Query Can be used

Select user_id from fnd_user where user.name=’<user name>’

10. ERROR_CODE

11. LAUNCH_RATE_CHANGE – ‘Y’ - If you want the rate change program to run automatically

12. CONTEXT - The descriptive flex field context.

13. ATTRIBUTE1 – 14 - Any descriptive flex field information associated with the daily rate.

Important tables and their relationship

5.0 IMPORTANT TABLES:

1. GL_BALANCES

Stores actual, summary and encumbrance balances for detail and summary accounts. This table stores the functional currency, foreign currency and statistical balances for each accounting period that has ever been opened. Posting Performs update of GL_BALANCES and delete of GL_BC_PACKETS.

ACTUAL FLAG - "A" / "B" / "E" for actual, budget, encumbrance balances.

PERIOD_NET_DR, PERIOD_NET_CR – Period Activity is stored.

Also stores the BEGIN_BALANCE_DR, BEGIN_BALANCE_CR.

Year-to-date balance = begin_balance_dr + begin_balance_cr + period_net_dr - period_net_cr.

2. GL_CODE_COMBINATIONS: stores valid account combinations for each Accounting Flex field 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.

3. GL_SETS_OF_BOOKS: stores the set_of_books_id, chart_of_accounts_id, Name, Suspense_allowed_flag, Currency_code etc.

4. 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 journals.

4. 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.

5. 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.

6. GL_JE_SOURCES : stores journal entry source names and GL_JE_SOURCES _TL Stores the descriptions.

7. GL_PERIOD_STATUTES: 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.