MOAC

KPIT Cummins Infosystems Limited

Oracle Applications

Module Overview Document

General Ledger

Author: Vinod Muchalambkar/Himanshu Dumbhare

Version: 1.3

Date: 19-Jan-2007

Revisions :

#	Date	By	Remarks
1.0	Aug 21, 2006	Vinod Muchalambkar/Himanshu Dumbhare	Initial Document Creation
1.1	Aug 22, 2006	Vasudeo Joshi	Reviewed first 2 chapters
1.2	Jan,05, 2007	Vinod / Himanshu	Added chapters 3 to 11
1.3	Jan 19, 2007	Vasudeo Joshi	Reviewed all chapters

Table of Contents

1. Introduction 4

1.1. Overview 4

1.2. Key Components of an Entry in General Ledger 7

1.3. General Ledger : It’s All about Balance 7

1.4. The General Ledger Cycle 8

1.5. Other Benefits 9

2. Oracle Apps General Ledger Features 10

2.1 General Ledger: Business Function 11

2.2 Sets of Books 12

2.3 Types of Journals 12

2.4 Other Features 13

2.4.1 Intercompany Accounting – 13

2.4.2 Multiple Reporting Currencies - 13

2.4.3 Consolidation - 13

2.4.4 Budgets - 13

2.4.5 Revaluation - 14

2.4.6 Translating Balances 14

2.4.7 Open Interfaces 14

2.5 Reporting 14

2.6 Advanced Features 15

2.6.1 Financial Statement Generator 15

2.6.2 Application Desktop Integrator 15

2.6.3 Oracle Financial Analyzer 15

1. Oracle Apps General Ledger Setup 16

3.1 Types of Setup 16

3.2 Set of Books 17

3.2.1 Chart of Accounts 18

3.2.2 Accounting Calendar 19

3.2.3 Currencies 20

3.2.4 Account Combination 20

3.2.5 Rates 21

3.3 Oracle General Ledger Setups 22

3.3.1 Sources 22

3.3.2 Categories 22

3.3.3 Suspense Accounts 23

3.3.4 Summary Accounts 23

3.3.5 Statistical Unit of Measure 24

3.3.6 Document Sequences 25

3.3.7 Historical Rates 26

3.3.8 Open and Close Accounting Periods 26

3. Transactions 29

4.1 Overview of Transactions 29

4.1.1 Journals Imported from GL Interface Tables 29

4.1.2 Journals entered manually 29

4.2 Recurring Journal Entries 31

4.2.1 Generate Recurring Journals 32

4.3 Mass Allocations 33

4.3.1 Define Mass Allocations 34

4.3.2 Generate MassAllocation Entries 34

4.4 Revaluation 36

4.5 Translation 37

4.6 Processing Budget Journals Transactions 38

4.6.1 Budgeting and Encumbrances 38

4.6.2 Understanding the GL Budgeting Process 38

4.7 Global Consolidation System (GCS) 39

4.8 Multiple Reporting Currencies (MRC) 40

4. Oracle Apps General Ledger Reports 42

5.1 Standards Reports and Listings 42

5.1.1 Account Analysis 42

5.1.2 Budget 43

5.1.3 Chart of Accounts 44

5.1.4 Multi-Company Accounting and Consolidation 44

5.1.5 Currency 45

5.1.6 Financial Statement Generator 46

5.1.7 General Ledger 46

5.1.8 Journals 47

5.1.9 Trial Balance 48

5.1.10 Execution 49

5.1.11 Other 50

5.2 Financial Statement Generator (FSG) 50

5. Oracle Apps General Ledger: Technical 51

6.1 Set Up 52

6.2 Journals 55

6.3 Journal Import 56

6.4 Other Important tables 56

2. Oracle Apps General Ledger: Interfaces, Concurrent & APIs 59

7.1 Journal Import 64

7.1.1 Exporting Data From Your Feeder System 65

7.1.2 Assigning Values for Accounts 66

7.1.3 Assigning Values for Additional Required and Conditionally Required Columns 68

7.1.4 Assigning Values for Currency Conversion 70

Required NULL Columns in the GL_INTERFACE Table 71

7.1.5 About Journal Import Validation 71

7.1.6 Importing Information to GL 74

7.2 GL Daily Rates Interface 74

7.2.1 Concepts You Should Know 74

7.2.2 Foreign Currency Concepts 74

7.2.3 Conversion 75

7.2.4 The GL_DAILY_RATES_INTERFACE Table 75

7.2.5 Required and Conditionally Required Columns 75

Optional Columns 77

7.2.6 Other Columns 77

7.2.7 Daily Rate Conversion Entry 77

7.3 GL Budget Accounting Cycle 78

7.3.1 Defining Budgets and Budget Organizations 78

7.3.2 Entering Budgets 78

7.3.3 Reviewing, Correcting, and Freezing Budgets 79

7.3.4 Report on Budgets 79

7.3.5 Uploading Budgets from External Sources 79

7.3.6 Software Compatibility 79

7.3.7 Spreadsheet Design 79

7.3.8 Understanding the Budget Interface Table 79

7.3.9 Creating Your Budget Spreadsheet 80

7.3.10 Upload Budgets 81

7.3.11 About Budget Upload Validation 81

6. Oracle Apps General Ledger: Workflows & Alerts 82

8.1 Oracle Workflow 82

8.2 Workflows in Oracle General Ledger 83

8.2.1 Journal Approval Process 83

8.2.2 AutoAllocations Process 86

8.2.3 Global Intercompany System 86

8.2.4 Global Consolidation System Cross Instance Data Transfer 87

7. Oracle Apps General Ledger: Other / Support Input 88

8. Oracle Apps General Ledger: Exercises 90

9. Oracle Apps General Ledger Interview Questions (Functional & Technical 96

1. Introduction

1. Overview

There are many types of businesses and based on the primary profit-generating activities of a business can be classified as follows:

Manufacturing., Service, Retailers and Distributors, Agriculture, mining, Financial businesses including banks, Real estate, Transportationetc.

Manufacturing:

Manufacturers produce products, from raw materials or component parts, which they then sell at a profit. Companies that make physicalgoods, such as cars or pipes, are considered manufacturers.

Service:

Service businesses offer intangible goods or services and typically generate a profit by charging for labor or other services provided to other businesses or consumers. Organizations ranging from house painters to consulting firms to restaurants and even to entertainers are types of service businesses

Bookkeeping is necessary for recording all financial transactions undertaken by a business .

Currently the double-entry bookkeeping (or double-entry accounting) system is used by businesses and other organizations to record financial transactions. Every transaction is recorded by entries in at least two accounts. The total of the debit values must equal the total value of the credit values. Most transactions consist of two entries, but can have three or more entries e.g. Supplier Invoice Total = Net value + taxes. Normally there are only two transactions and hence the name double entry, with the transaction recorded once on the debit side and once on the credit side. e.g. Supplier Invoice recorded on the debit side in expense a/c and on the credit side in supplier a/c.

The General Ledger is the core of company’s financial records. Every transaction flows through the general ledger. These records remain as a permanent track of the history of all financial transactions since day one of the life of the company.

The general ledger is the main accounting record of a business which uses double-entry bookkeeping. It usually include accounts for such items as fixed assets, current assets and liabilities, profit and loss or income and expenditure

items, and funds or reserves. The general ledger is a summary of all of the transactions that occur in the company. It is taken directly from the journals, where each transaction is recorded.

The balance sheet and the profit/loss statement are both derived from the general ledger. Because it is organized by accounts, the general ledger allows you to observe the impact of all the transactions affecting each account at any given time. The general ledger is where posting occurs, which is the process of recording credits and debits in the general ledger. The actual balances are called the trial balance.

The general ledger includes the date, description and balance entries for each account. It is usually divided into at least five main categories. These categories include assets, liabilities, revenue and expenses and equity (capital held by the owners of the company). The main categories of the general ledger may be subdivided into sub-ledgers to include details such as accounts payable, accounts receivable, etc.

Because each transaction debits one account and credits another to an equal extent an accounting system will ensure that the general ledger will always be in balance. The general ledger is the main accounting record of a business which uses double-entry bookkeeping. It usually include accounts for such items as fixed assets, current assets and liabilities, profit and loss or income and expenditure items, and funds or reserves. The general ledger is a summary of all of the transactions that occur in the company. It is taken directly from the general journal, where each transaction is recorded.

The balance sheet and the profit/loss statement are both derived from the general ledger. Because it is organized by accounts, the general ledger allows you to observe the impact of all the transactions affecting each account at any given time. The general ledger is where posting occurs, which is the process of recording credits and debits in the general ledger. The listing of all the actual balances is called the trial balance.

The general ledger includes the date, description and balance entries for each account. It is usually divided into at least five main categories. The main categories of the general ledger may be subdivided into subledgers to include details such as accounts payable, accounts receivable, etc.

Because each transaction debits one account and credits another to an equal extent an accounting system will ensure that the general ledger will always be in balance. Business consist of activities like manufacturing of goods (e.x automobiles, consumer electronics etc) or providing some services to customer (maintenance, consultancy etc) in the market. As part of this process many other activities are performed in different forms. Every transaction has a financial impact. When there is financial impact, it is required to record financial impact with correct calculations.

These activities includes different functions viz. Ordering, Design, Marketing, Sales, Services, Purchase, Material Planning, A/c Receivable, A/c Payable etc. As stated above every thing has financial impact attached and there are transactions performed in each of the above activities.

Transactions performed are accounted in respective modules and those modules are mainly classified as: Purchasing, Production and Sales. In addition to this organization spend money on different types of overheads(eg land, electricity, labour cost, maintenance). If we see these modules as individual module, those will not provide a complete picture of business. Transactions of different kinds are required to be consolidated to see the business health or business status and profit or loss figures and plan corrective / improvement actions wherever required. All the transactions need to be consolidated in Finance and Accounting module which will ultimately give these figures.

General Ledger is the heart of the financial module. The general ledger is a permanent summary of journals from supporting modules, such as the purchasing, sales and cash receipts journal and the cash disbursements journals(eg suppliers, contractors, employees). General Ledger is also a single point view of most of business activities . All types of financial transactions have to be compulsorily accounted in general ledger to give a complete picture of a business financial health.

2. Key Components of an Entry in General Ledger

1) Account Code

To account different types of transactions and latter reporting as per requirement of different agencies those transactions require to be identified by some code. This code is called as Account Code, which is combination of information such as

• A/c Type (Expenses, Revenue, Asset, Liabilities)
• In case of Payable or Receivable Transaction the respective Supplier or Customer Number
• In case of Expenses, respective Division, Department, Cost Center etc
• In case transaction related to fixed asset, Asset Number.

For ease in accounting, organization prepares coding structure to group the transactions. This helps to group and consolidate the transactions in respective heads and to prepare the statutory reports.

2) Period (Date in which entry is made).

3) Amounts (Debit or Credit)

Every accounting entry in the general ledger contains both a debit and a credit. Further, all debits must equal all credits. If they don't, the entry is out of balance.

4) Currency of transaction

Transactions can be recorded in different currencies depending upon the geographical location of the entity. However to report to government/legal/reporting authorities it may be required to convert transactions from one currency code to other or consolidate transactions.

3. General Ledger : It’s All about Balance

Using a system of Debits and Credits, called double-entry accounting, accountants use a General Ledger to track funds as it flows in and out of a business. They record each financial transaction on a balance sheet, which provides a snapshot of a business's financial condition. This balance sheet and any report in General Ledger shows the balance ( Debit/Credit ) of each account code. Accountants record every financial transaction in a way that keeps the following equation balanced i.e. Assets = Liabilities + Owner's Equity (Capital) where assets, liabilities, Owners Equity are all group of accounts which belong to either of these categories.

4. The General Ledger Cycle

Accounting is based on the periodic reporting of financial data. The basic General Ledger cycle includes:

• Recording business transactions. Businesses keep a daily record of transactions in different modules e.g. Purchase, Sales, Inventory, Cash, Bank etc.
• Posting debits and credits to a general ledger. A general ledger is a summary of all business journals. An up-to-date general ledger shows current information about Accounts Payable, Accounts Receivable, Owners' Equity and Other Accounts.
• Making adjustments to the general ledger. General-ledger adjustments let businesses account for items that don't get recorded in daily journals, such as bad debts, and accrued interest or taxes. By adjusting entries, businesses can match revenues with expenses within each accounting period.
• Periodic Reporting: After accounting transactions into respective module those are transferred to General Ledger and reports are prepared. These reports are used by different levels of management for decision making and statutory reporting.
• Closing the books. After all revenues and expenses are accounted for, any net profit gets posted in the owners' equity account. Revenue and expense accounts are always brought to a zero balance before a new accounting cycle begins.
• Preparing Financial Statements. At the end of a period, businesses prepare financial reports — income statements, statements of capital, balance sheets, cash-flow statements and other reports — that summarize all of the financial activity for that period.

At the end of a period — either annually or more frequently, depending on the length of a business's accounting cycle — accountants create financial statements that show the financial health (or decline) of a business. The standard statements are Profit & Loss A/c and Balance Sheet and some more reports as per the local statutory requirements.

Many people inside and outside a company use the information found in financial statements to analyze the business health and future prospects. Business owners and managers use the data in financial statements to chart the course of their companies, project revenues and expenses, monitor cash flow, keep tabs on costs and plan for the future. Present and prospective employees also use this information to see their employers' financial performance.

Stakeholders and investors closely examine financial statements to check a company's performance. They want to compare a business's financial statements with those of other companies to guide their investment choices. Bankers look at a company's most recent financial statements when they make lending decisions.

Financial statements also used by accountants to prepare tax returns and report financial information to the government authorities.

5. Other Benefits

General ledger enforce organization to follow adequate polices, practices, Procedure, and internal controls regarding its assets, liabilities, and equity.

• Every organization has to operate in accordance with established laws, rules and regulations and accounting norms where in corrective actions are initiated when the business is made aware of deficiencies or violations in policies, practices, procedures, or internal controls.

• Assets are recorded to represent the investment of funds in the business.

• Liabilities and equity are properly recognized and recorded in the books and

2.Oracle Apps General Ledger Features

2.1 General Ledger: Business Function

Oracle corporations Oracle Applications provides the E–business Suite, a fully integrated suite of more than 70 software modules including financial management. The financial module mainly consist of Oracle Payables, Oracle Receivables, Oracle Assets and Oracle General Ledger. The Oracle General Ledger is one of the most advanced and multi-featured module available for General Ledger Accounting. It provides robust but user friendly features for basic general ledger accounting along with some advanced features.

Oracle General Ledger is a comprehensive financial management solution that facilitates the following tasks:

• Financial controls
• Data collection
• Information access
• Financial reporting and analysis

Oracle General Ledger is the central repository for accounting information, receiving transactions from financial and manufacturing sub ledgers like Payables, Receivables, Assets, Projects, Purchasing, Cost Management/Inventory.

Using Oracle General Ledger you can

• Record and Review Accounting Information

Import data from subsidiary ledgers, or enter journals to record actual or budget transactions directly into Oracle General Ledger. Enter encumbrance journals to track encumbrances through the purchase approval process and to control spending against budgeted amounts. Review account balances online or through standard reports.

• Manipulate Accounting Information

Correct actual, budget, and encumbrance information. Revalue and translate balances denominated in foreign currencies. Consolidate balances from multiple sets of books.

• Analyze Accounting Information

Integrate Oracle General Ledger with Oracle Financial Analyzer to simplify the budgeting and forecasting process, to efficiently answer ad hoc requests, and to identify areas for profit improvement. Quickly prepare “What If” analyses and pro forma reports.

2.2 Sets of Books

For basic accounting Set of Books need to be defined. Set of Books consists of Three C’s i.e Chart of Accounts, Currency, Calendar. Once we define all these three we have to assign them to set of books. The backbone of any financial system is to define appropriate Set of Books. Oracle Apps facilitate to use any number of sets of Books provided Multi Org is enabled.

For creating account code in chart of accounts Oracle provides a powerful feature of Flexfield.

A flexfield is a flexible data field that your organization can customize to your business needs without programming. Oracle Applications uses two types of flexfields, key flexfields and descriptive flexfields. A key flexfield is a mandatory flexfield you have to customize to enter multi–segment values such as part numbers, account numbers, and so on. A descriptive flexfield is an optional field you customize to enter additional information for which your Oracle Applications product has not already provided a field. Oracle General Ledger provides Accounting Flexfield for setting the Accounting Code Combination in which you can have a combination of many segments like Cost Centre, Department, Account code, Project code etc each having a list of predefined values called Value Sets which validates data being fed into the segments.

2.3 Types of Journals

It imports data from subsidiary ledgers when you post your transactional information from AP, AR, FA, CM and creates Journal or you can enter Journals directly into Oracle General Ledger. You can enter into Oracle general ledger different types of Journals based on the business and accounting requirements. Some of the Journal Entry Types are

1. Basic Journal Entry – This is the standard Journal which is used frequently for business transactions such Revenues, Expenses, Adjustments, Depreciation.
2. Reversal Journal Entry – In this type the original journal is reversed. You can reverse any journal entry and post it to the current or any future open accounting period.
3. Recurring Entries – These types of Journals are defined once and then generated for the subsequent accounting Period.
4. Mass Allocations – These Journal uses entry formula to allocate revenue and expenses across a group of Cross Centers and departments and through automated process create the required Journal.

Journals can be entered for Functional currency, Foreign currency or Statistical( e.g Headcount, Square Feet etc).

2.4 Other Features

2.4.1 Intercompany Accounting –

If your companies have different account structures, accounting calendars, or functional currencies, you will need to create a set of books for each company. You also need separate sets of books if you use multiple Oracle Applications instances for your companies.

If your organization has multiple companies within a single set of books, you can use intercompany accounting to track transactions between companies. GIS provides a controlled, central location for subsidiaries to conduct intercompany transactions throughout a global organization.

2. Multiple Reporting Currencies -

Multiple Reporting Currencies is a set of unique features in Oracle Applications that allow organizations to report and maintain accounting records in multiple currencies.

Multiple Reporting Currencies is beneficial for those organizations that must routinely report their transactions and financial results in multiple currencies, other than their primary functional currency. Transactions entered in Primary books are automatically converted to each reporting set of Books.

2. Consolidation -

In Oracle General Ledger, you can consolidate any number of subsidiaries into parents represented by different sets of books and even those with different charts of accounts, currencies and calendars.

2. Budgets -

Oracle budgets can be set up to calculate the same way actuals are calculated.

We use budgeting to enter estimated account balances for a specified range of periods. You can use these estimated amounts to compare actual balances with projected results, or to control actual and anticipated expenditures. General Ledger gives you a variety of tools to create, maintain, and track your budgets, including the ability to upload budget amounts from your spreadsheet software.

2. Revaluation -

Use the Revalue Balances window to revalue foreign currency–denominated balances. This window launches a process that revalues the functional currency equivalent balances for the accounts and currencies you select, using the appropriate current market rate for each currency. Resulting gain or loss amounts are posted to the gain/loss or cumulative translation adjustment account you specify. This process creates a revaluation batch containing separate journal entries for each revalued foreign currency.

You can revalue a single account or ranges of accounts, for both income statement and balance sheet reporting. Income statement accounts are revalued on the basis of their period–to–date or year–to–date balances. Balance sheet accounts are always revalued on the basis of their year end balances.

2. Translating Balances

You can translate your actual and budget account balances from your functional currency to another currency. If average balance processing is enabled, you can translate both average and standard balances. If you want to report financial results in Euro, you can use General Ledger’s translation feature to translate your account balances from your functional currency to Euro.

2. Open Interfaces

• You can integrate with external systems by using published open interfaces.
• You can write import programs to import data from an external system or from your previous accounting system
• You can initiate a full account validation process during import

4. Reporting

General Ledger provides the following categories of standard reports and listings

• Account Analysis
• Budget
• Chart of Accounts
• Multi–Company Accounting and Consolidation
• Currency
• Financial Statement Generator
• General Ledger
• Journals
• Trial Balance
• Other

4. Advanced Features

1. Financial Statement Generator

Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible financial report building tool you can use to build your own custom financial reports without programming.

2. Application Desktop Integrator

Oracle ADI is a spreadsheet-based extension to Oracle Applications that offers full cycle accounting and asset management within the comfort and familiarity of a spreadsheet. Oracle ADI combines a spreadsheet’s ease of use with the power of Oracle Applications, to provide true desktop integration during every phase of your accounting cycle. You can create budgets, record transactions, add assets, reconcile inventory, and run financial statements and inventory reports all without leaving your spreadsheet.

3. Oracle Financial Analyzer

Oracle Financial Analyzer enables you to quickly analyze performance, develop new plans, and quickly revise budgets and forecasts. Executives, managers, and analysts can develop and analyze performance metrics, evaluate risks, and formulate future directions. Sales and marketing teams identify new opportunities, measure promotional effectiveness, track sales, and forecast future results.

1.Oracle Apps General Ledger Setup

1. Types of Setup

As a part of implementing Oracle General Ledger certain setups need to be done before data transfer (from old system) if any and roll-out of new system. The Setups are categorized as

• Required (some with default values): The Required setups with defaults comes as seeded, however should be reviewed and selected as per our requirements
• Optional: The optional features are required only if we are using the related feature.

As shown above Setups are divided mainly into 2 groups, Set of Books Setup and General Ledger Setup. Each one is described briefly below

2. Set of Books

A set of books determines the functional currency, account structure, and accounting calendar for each company or group of companies. Each set of books has a number of options that indicate the accounting practices you want to follow for that set of books.

Complete the three tasks necessary to create a set of books.

1. Chart of Accounts

• Your chart of accounts is the account structure you define to fit the specific

needs of your organization.

• You can choose the number of account segments as well as the length,

name, and order of each segment.

Accounting Flexfield

You can define a flexible chart of accounts structure using the powerful Accounting Flexfield feature of Oracle General Ledger.

• Create up to 30 segments of up to 25 characters per segment, limited to 240 characters total.
• Define each segment name and the order in which it appears. e.x Company, cost centre, account, product, project code etc.
• Attach a list of valid values to each segment (Value Set).
• Account Ranges: Throughout General Ledger, you can use ranges to quickly specify a group of accounts.
• Segment Qualifiers: When you define a segment value in the Segment Values window, you can assign qualifiers, which determine the account type (asset, liability, or expense), whether budgeting is allowed, whether posting is allowed and other information specific to the segment value.

2. Accounting Calendar

• The accounting calendar defines an accounting year and the periods it

contains.

• You can define multiple calendars and assign a different calendar to each set of books.

3. Currencies

• You can select the functional currency for your set of books as well as other

Currencies that you use to transact business.

4. Account Combination

Define new accounts by creating new combinations of account segment

values.

5. Rates

If you are entering foreign currency journals you can enter rates for conversions between 2 currencies. There are some pre-defined rate types for which you can enter conversion rates between currencies.

RateType	Description
Spot	An exchange rate used to perform conversion based on the rate on a specific date.
Corporate	An exchange rate used to standardize rates for your company.
User	An exchange rate you specify when you enter a foreign currency journal entry.
EMU Fixed Rate	An exchange rate General Ledger provides automatically when you enter journals (after the EMU effective starting date) when translating values from one EMU currency to another.

3. Oracle General Ledger Setups

1. Sources

Journal source identify the origin of journals, from sub ledgers like payables, receivables etc or from other feeder systems or manual entry. For each journal source, you can specify whether to import detail reference information for summary journals you import from your feeder systems. You can also choose to freeze the journal source, preventing users from making changes to any unposted journals from that source. If you have journal approval enabled for your set of books, you can require that higher management levels approve journals with a specific journal source before the journal can be posted. General Ledger provides some pre-defined sources.

2. Categories

For differentiating journal entries by purpose or type such as payments, accruals or receipts journal categories have to be defined. General Ledger provides some pre-defined categories. Categories can be associated with particular document sequence for further differentiation.

3. Suspense Accounts

Suspense Account is a feature which, if enabled for a Set of Book, allows General Ledger to balance journals for specific sources and categories. For such journals if the total of debit and credit amounts is not balanced, the difference gets accounted to this suspense account. Suspense account can be assigned to Set of Books as default or can be separately defined for each source and category.

4. Summary Accounts

Summary accounts store the sum of the balances of groups of detail accounts. They are extremely useful for performing online summary inquiries, and speed the processing of financials reports since balances for group of accounts are already available and hence need not be calculated at runtime.

The first step in defining summary accounts is to determine the need for them. Determine the groups of accounts needed for reports, say management reports or dashboard reports for enquiries on cash balances. Then choose ways to summarize your accounting information depending on structure of your account and needs. e.x group by all accounts for a particular cost centre for each company. After determining your needs and organizing summary account structure, parent and roll-up groups have to be defined. Detail accounts in which actual entries are made and balances stored are assigned to parent accounts which does not store balances or allow posting. These parent accounts are further associated with roll-up groups which are one step upper in this hierarchy.

The next step for setting up summary accounts is to plan summary templates based on which summary accounts are created. While for any segment a value of D (Detail), T (Total) or Roll-up group is given, summary accounts are created as

Number of Detail Segment Values (For each segment with a “D” value) x Number of Parent Segment Values in the Rollup Group(For each segment with a Rollup Group Name) x

1 (for each segment with a “T” value)

General Ledger calculates and maintain balances for these summary accounts from the earliest period given.

5. Statistical Unit of Measure

General Ledger allows entry of journals in Statistical Accounts as head count, number of units produced or sold. To define statistical unit of measure, we have to enter account segment value (only detail accounts)to be associated with a statistical unit of measure, then enter a unit of measure and description.

By enabling the profile option Journals:Mix Statistical and Monetary you can enter both statistical and monetary amounts for the same account within a single journal entry.

6. Document Sequences

Document Sequence is a unique sequence of numbering for documents created in Oracle applications. In General ledger it can be used to number the journals entered. Document sequence can be enabled/disabled according to date range specified. Once a document sequence is assigned it cannot be changed.

Once a Document sequence is created, it is assigned to a Journal Category.

7. Historical Rates

Historical Rates can be entered for translating actual or budget account balances. They are assigned to accounts either individually or in range. Generally historical rates are entered for specific balance sheet accounts e.x for translating non-monetary and selected owners equity account balances.

8. Open and Close Accounting Periods

Periods created for Calendars have to be opened to allow entry and posting of journals in General Ledger and subsequently closed after period end, to control journal entry and compute period-end and year-end actual and budget account balances for reporting. The general cycle is shown below.

Accounting periods can have one of the following statuses:

Open: Journal entry and posting allowed.

Closed: Journal entry and posting not allowed until accounting period is reopened. Reporting and inquiry allowed.

Permanently Closed: Journal entry and posting not allowed. You cannot change this period status. Reporting and inquiry allowed.

Never Opened: Journal entry and posting are not allowed. General Ledger assigns this status to any period preceding the first period ever opened in your calendar, or to any period that has been defined, but is not yet future–enterable. You cannot change this period status.

Future–Entry: Journal entry is allowed, but posting is not. Your period is not yet open, but falls within the range of future-enterable periods you designated in the Set of Books window. You cannot change this period status without using the concurrent process to open the period.

3.Transactions

4.1 Overview of Transactions

You can enter Journals directly into Oracle General Ledger. You can enter different types of Journals into Oracle general ledger based on the business and accounting requirements or you can import data from subsidiary ledgers when you post your transactional information from AP, AR, FA, CM modules.

4.1.1 Journals Imported from GL Interface Tables

The Journals can be imported using GL_Interface Table:

The GL_INTERFACE table receives data from other systems. When you run GL Import, it validates and converts your interface data into journal entries within your General Ledger application. The GL_INTERFACE table is organized by columns in which your General Ledger application categorizes and stores specific accounting data. For example, journal entry source information is stored in the column called JE_SOURCE_NAME.

4.1.2 Journals entered manually

Typically, manual journals are created to:

• Correct or adjust prior journal entries
• Move expenses from one funding source to another
• Account for the internal sale of goods or services
• Transfer funds

Batches are groups of related journals. Journal batches can help you retrieve, review, and reverse journals. If you create only one journal, the system creates (by default) a batch for that one journal with the journal header information becoming the batch information

Journals can be created in several ways by any of the following methods:

• Direct entry using Enter Journals window
• Importing using Journal Import window
• Creating reversals for existing entries
• Creating recurring journals using defined formulas
• Creating MassAllocations using defined formulas
•  Creating Eliminations using defined Elimination Sets

4.2 Recurring Journal Entries

With the Recurring Journal Entries function of GL, journal entries can be created using fixed amounts and accounts. Multiple journals can be created with the same or similar information. Recurring journals are useful for:

• Processing internal sales of goods or services
• Transferring funds between units
• Distributing expenses among a variety of depts and accounts

There are two types of recurring journals: standard and skeleton.

• Standard recurring journals let you create and save a journal template that includes all chart of account values and amounts for the journal (they could be used to allocate rent expenses since the same amount is charged each month for rent).
• Skeleton recurring journals let you create and save a journal template that includes all chart of account values, but does not include any amounts for the journal (they could be used to allocate phone expenses to a number of code combinations since the phone bill changes each month).

4.2.1 Generate Recurring Journals

Use the Generate Recurring Journals window to generate unposted journal entries based on the templates or formula defined as previously mentioned. The window lists predefined recurring templates or formulas. Select the batches to generate from this list. The generation of recurring journals can be scheduled as concurrent processes.

4.3 Mass Allocations

Use MassAllocations to create multiple entries from a single source account by using a formula. The source account can either be a summary account or contain parent values. First, you must define a formula for MassAllocation and then generate MassAllocation entries using the formula.

4.3.1 Define Mass Allocations

Use the Define MassAllocations window to create MassAllocation definitions. GL prompts to start a concurrent program to validate the MassAllocation. Without successful validation, formulas cannot be generated. When working with actual balances, you can select Full Balance or Entered Currency to allocate. The first option allocates both functional currency and converted functional currency amounts, resulting in functional currency entries. The second option only posts the entered currency, ignoring the converted functional currency amount. This option results in transactions of the entered currency.

4.3.2 Generate MassAllocation Entries

Use the Generate MassAllocation Journals window to generate journals from the MassAllocation formula defined in the previous step. MassAllocations can be generated for a range of periods. To generate for one period, enter the same period in the from and to fields. In Release 11i, to accomplish a multistep allocation, step-down and parallel allocation sets have been added. These allocation sets can optionally contain recurring journals. Allocations and recurring journals can now be scheduled and submitted through a concurrent process.

MassAllocations can be run in Full and Incremental modes. In Full mode, the allocations are run once. In Incremental mode, the allocation is made for the differential amount where there is a change in the original balances.

4.4 Revaluation

When transactions are entered in a foreign currency, the currency exchange rates might change by the date the amounts are paid or realized. Revaluation reflects these changes in conversion rates. Revalue account balances to update functional currency equivalents. The most commonly revalued accounts are receivables and payables. GL posts the change in converted balances to an Unrealized Gain or Loss Account.

Revalue the required accounts as per the following steps:

1. Define an Unrealized Gain/Loss Account for posting the gain or loss resulting

from revaluation.

2. Define a Revaluation Rate (use the Period Rates window). Enter either a

Period-End Rate or the Revaluation Rate. GL calculates the other

automatically.

3. Run a revaluation concurrent request. This creates an unposted revaluation

Journal batch. After reporting, restore original balances by reversing this batch

of journals.

4. When a foreign currency transaction amount is paid or received, the

functional currency value of the foreign currency amount is recomputed, and

the difference between this amount and the original transaction amount is

posted to the Realized Gain or Loss Account.

4.5 Translation

GL allows reporting of functional currency transactions in another currency using translation. Translation does not affect functional currency balances, nor does it create journals. The translated values are stored and used for reporting. To consolidate a set of books with a different functional currency than the parent company, translation must be run in that set of books to convert to the currency of the parent company.

Translate actual or budget balances to foreign currencies for online inquiries, reports, and consolidations. GL translates balance sheet accounts using a period-end rate, and income and expense accounts using a period average rate. GL posts the difference on translation to a translation adjustment account specified in the set of books. When different rates are used to translate different accounts resulting in an out-of-balance Balance Sheet, GL posts the discrepancy to a cumulative translation adjustment account (CTA). Define a CTA account as an owner's equity account or an income account. An income account typically is used for countries experiencing hyper-inflation. Run the Translated Trial Balance.

4.6 Processing Budget Journals Transactions

Budget Journals are journal entries that are identified by journal type Budget. Simple budget journals can be entered the same way as Actual journal entries as discussed previously, using the Enter Journals window. Budget Journals are used as a means of entering budgets when Budgetary control is enabled. GL also provides some special techniques to manage budgets, which are discussed in this section.

4.6.1 Budgeting and Encumbrances

Budgeting in GL implies the process starting from defining budgets, entering budget amounts, defining budgetary controls, and letting GL perform funds checks and control transactions based on availability of funds against the budget for these transactions.

Encumbrance simply is burdening or reserving funds for specific transactions. GL automatically creates encumbrances when budgetary control is enabled. You can also enter encumbrances manually like journal entries. These two topics are discussed in this section on budgeting and the following section on encumbrances.

4.6.2 Understanding the GL Budgeting Process

Budgetary control in GL or in Oracle Financials is optional and only certain types of organizations use these financial techniques. You can use budgetary control by checking the Enable Budgetary Control option in a set of books definition. To begin the budgetary control process, it is necessary to decide the degree of control by way of the following options:

• Detail or Summary
• Absolute or Advisory
• The Budgetary Control Group rules
• Limits/budget amounts

Budgetary control requires three components to be set up: periods, accounts (including the degree of control), and amounts. A budget definition has periods to which the budget applies. This is defined using the Define Budget window. Accounts are defined using the Define Budget Organization window. Amounts can be entered in different ways, including entry of budget amounts and journals, Upload, MassBudgets, Formulas, Transfer, and ADI.

Finally, when you freeze Budgets and Budget Organizations, you prevent any updates to the budget. If changes must be made, you must open or unfreeze the budget.

When the preceding setup is in place, the budgetary control activity takes place online. GL allows only those journals to be posted that pass the funds check (assuming absolute control). GL, in integration with subledgers, tracks encumbrances for all the transactions and updates budgets/encumbrances online. GL prevents overspending by online notification of funds availability. You can build a budget hierarchy to control allocation of budget amount. You have the flexibility to enter Master/Detail budgets to suit the actual budgeting needs of the business.

4.7 Global Consolidation System (GCS)

GCS provides a controlled, central location for subsidiaries to conduct intercompany transactions throughout a global organization.

• Transactions between subsidiary companies are entered, exchanged, and

resolved (if disagreements occur) within GIS.

• Oracle Workflow operates in conjunction with GIS to notify parties of entered

transactions, rejections, approvals, and transaction results.

• Ensure that both parties to an intercompany transaction review the transaction

details.

• Ensure that intercompany journal entries are made in both subsidiary’s sets of

books, to the correct intercompany accounts.

• Simplify the intercompany reconciliation process by ensuring that transaction

amounts made to reciprocal intercompany accounts are the same.

4.8 Multiple Reporting Currencies (MRC)

Use Multiple Reporting Currencies (MRC) to maintain your transactions and account balances in multiple currencies. For example, you can maintain a primary set of books in CAD (Canadian Dollars) and have General Ledger automatically maintain reporting sets of books in USD (U.S. Dollars), FRF (French Francs), and the Euro — the single currency of the European Monetary Union (EMU).

From any of your reporting sets of books, you can:

1. Perform online inquiries and produce any of the standard General Ledger

reports, in your reporting currency, without first having to perform a separate

translation operation.

2. Use the Financial Statement Generator (FSG) to create custom reports in your

reporting currencies.

3. Consolidate a reporting set of books directly to a parent set of books, bypassing the need to separately translate the balances in your subsidiary’s primary set of books.

4.Oracle Apps General Ledger Reports

General Ledger provides 2 types of reporting capabilities

• Standards Reports and Listings.
• Financial Statement Generator (FSG).

Standard reports comes seeded with Oracle General Ledger and can be both financial in nature ((journal listings, GL reports, trial balances, account analysis and others) and non-financial in nature (chart of accounts, consolidations, report definition, massallocation definition, and other reports). FSG reports are customizable to meet the customer’s business needs.

1. Standards Reports and Listings

Oracle General Ledger gives you a complete set of standard reports. Categories of standard reports and listings include

1. Account Analysis

Accumulated balances of a range of accounts and all journal entry lines that affect that range.

• Account Analysis – Contra Account

Print balances by account segment and a secondary segment, list the contra account for each journal entry, and list the subledger document number for transactions imported from subledgers.

• Account Analysis Report

Review source, category and reference information to trace your functional currency or STAT transactions back to their original source.

• Account Analysis Report with Payables Detail

Review functional currency or STAT balances and transactions for any account(s). You can use this report to reconcile asset additions imported into General Ledger from Oracle Payables.

• Account Analysis Report with Subledger Detail

Review the details of subledger activity that has been posted to your General Ledger accounts. The report displays detail amounts for a specific journal source and category, in your functional currency or STAT.

• Average Balance Audit Report

Review the detail activity which created the aggregate balances and related average balances for a set of books when average balance processing is enabled.

• Foreign Account Analysis Report

Review source, category and reference information to trace your foreign currency transactions back to their original source.

• Foreign Account Analysis Report with Payables Detail

Review foreign currency balances and transactions for any account(s).

2. Budget

Information about your budgets and budget organizations, including account assignments and budget

• Budget Hierarchy Listing

Review all master budgets and their associated detail budgets for your current set of books.

• Budget Journals by Flexfield Report

Review the status and details of your budget journals for a particular account, currency, and fiscal year.

• Budget Organization Listing

Review the details of a specific budget organization.

• Budget Organization Range Listing

Review the details of the account ranges assigned to a specific budget organization.

• Budgetary Control Transactions Report

Review the details of your funds check or reservation requests.

• Frozen Budget Accounts Listing

Review frozen budget components, including budgets, budget organizations, and account ranges.

• Funds Available Analysis Report

Use the Funds Available Analysis Report to measure budgets against expenditures and encumbrances to determine the balance of funds available for your future expenditures.

• Master/Detail Budget Report

Review the available funds for all detail budgets controlled by a specific master budget.

• Summary/Detail Budget Report

This report shows the balance and journal information for each detail account for the period you specify.

• Unbudgeted Master/Detail Accounts Report

Review the detail budget amounts for which there are no corresponding budgeted amounts in the master budget.

3. Chart of Accounts

Information about the accounts in your chart of accounts, including segment

values, rollup ranges, and suspense accounts.

• Account Hierarchy Report

A list of the detail accounts that roll up into each summary account for all summary templates.

• Chart of Accounts Listing

The chart of accounts for your current set of books, including detail and summary accounts.

• Rollup Detail Listing

This listing includes descriptions for both the parent and child segment values and the rollup group (if any) to which your parent segment value belongs.

• Rollup Range Listing

This listing includes information about each parent segment value, such as the rollup group to which each parent segment value belongs, whether each parent segment value is enabled and its range of child segment

• Inactive Accounts Listing

List of disabled and expired accounts.

• Segment Values Listing

This listing includes information about each segment value, such as whether your segment value is enabled, whether it is a parent, and whether posting and budgeting are allowed.

• Suspense Accounts Listing

This listing provides the suspense account for each journal entry source and

category.

4. Multi-Company Accounting and Consolidation

Information about your multi-company accounting and consolidation activities. Intercompany transactions made can be viewed using GL’s GIS feature.

• Consolidation Audit Report

The report prints the total of all subsidiary account balances that were consolidated into each account in your parent set of books.

• Consolidation Exception Report: Disabled Parent Accounts

Review all disabled accounts in your parent set of books for which you tried to consolidate balances or transactions.

• Consolidation Exception Report: Unmapped Subsidiary Accounts

Use this report to determine if your consolidation is complete prior to posting.

• Consolidation Journals Report

The report lists subsidiary journal lines and the parent accounts used for your consolidation.

• Consolidation Rules Report

Review both the segment and account rules you defined for a specific consolidation mapping.

• Intercompany Transactions Detail Report

Review intercompany transactions sent and received by a GIS subsidiary.

• Intercompany Transactions Activity Summary

Review beginning and ending account balances, along with transaction data for all approved intercompany transactions.

• Unapproved Intercompany Transactions Listing

Review an aging of intercompany transactions that have been rejected or not yet reviewed.

5. Currency

Daily, period, and historical rates that you defined for foreign currencies.

• Daily Conversion Rates Listing

This listing prints the rate type for each conversion rate as well as the date on which you defined the rate.

• Historical Rates Listing

Review the historical rates or amounts, and weighted–average rates used in foreign currency translation.

• Period Rates Listing

This listing includes the period–average and period–end translation rates you have defined. This listing also prints the revaluation rate (the reciprocal of the period–end rate), for each period–end rate in your accounting period.

6. Financial Statement Generator

Summary or detail information about the definitions of your Financial Statement Generator report components, reports, and report sets.

• Column Set Detail Listing
• Column Set Summary Listing
• Content Set Detail Listing
• Content Set Summary Listing
• Report Detail Listing
• Report Set Detail Listing
• Report Set Summary Listing
• Report Summary Listing
• Row Order Detail Listing
• Row Set Detail Listing
• Row Set Summary Listing
• Where Used Report

7. General Ledger

Beginning and ending account balances, and all journal entry lines affecting each

account balance in your functional and foreign currencies.

• Foreign Currency General Ledger Report

Review general ledger activity entered in a foreign currency and reconcile revaluation journals. General Ledger prints a new page for each balancing segment value. For each journal line entered in a foreign currency, the report prints the account affected, the description of the account segment value, the journal line amount in both your functional and foreign currency, and the beginning and ending account balances in both your functional and foreign currency.

• General Ledger Report

Review journal information to trace each transaction back to its original source.

General Ledger prints a separate page for each balancing segment value. For each journal line, the report prints the account affected, the concatenated description, the journal line amount, and the beginning and ending account balance. Additionally, for each journal line, the report prints journal details including source, category, journal name, and effective date.

8. Journals

Journal information in functional and foreign currencies, including posted, unposted, and error journals. You can also review journal activity for particular periods and balancing segments.

• Dual Currency Journals Report
• Foreign Currency Journals Report

This report prints the line number, account, transaction date, description, reference information, and the foreign, functional and statistical debit or credit amounts for each foreign currency journal line.

• General Journals Reports

The General Journals Report is available in three different formats. You can request a report of Posted Journals, Unposted Journals or Error Journals. Review your posted, unposted, error journal batches and the journals associated with each posted journal batch.

• Journal Batch Summary Report

The report provides information on Actual balances for your journal batches, source, batch and posting dates, total entered debits and credits and sorts the information by journal batch within each journal entry category.

• Journal Entry Report

The report prints the accounting date, category, journal name, reference, journal batch name, entered debit or credit amounts, net balance, and account total for each journal.

• Journal Line Report

For each journal line, the report prints the transaction date, account, reference, journal line description, entered amounts, and accounted amounts.

• Journals by Document Number Report

For each journal, the report prints the creation date, batch name, journal name, category, posting status, posted date, currency, and journal amounts. The report also indicates which document numbers do not have an associated journal.

• Journals – General

The report prints the line number, account, transaction date, description, line/source item, the debit or credit amount, and the statistical amount for each journal line.

• Journals – Day Book

Report shows posted journal entries and journal details chronologically by accounting date for a specified range of dates, journal source, and journal category.

• Journals – Voucher

Use this report to produce output for journal vouchers.

• Journal Check Report

Use the Journal Check Report to check the data entry of manually entered journals prior to posting.

• Tax Journals Report

To review the journal taxable lines and the tax lines they generated, for posted or unposted journals.

9. Trial Balance

Account balances and activity for functional and foreign currencies, budgets,

encumbrances, and actuals.

• Average Balance Trial Balance Report

This report provides a listing of standard and average balances for selected accounts based on an as–of date you specify.

• Budget Trial Balance Report

Review your general ledger budget account balances and activity for a specific currency.

• Detail Trial Balance Report

Review your general ledger actual account balances and activity in detail. You can run this report for balances and activity entered in your functional currency or STAT, or translated to a foreign currency.

• Encumbrance Trial Balance Report

Review your general ledger encumbrance account balances and activity for a specific encumbrance type.

• Expanded Trial Balance Report

Review the beginning, ending and net balances as well as period activity for a set of accounts.

• Foreign Currency Detail Trial Balance Report

Review actual general ledger account balances and activity entered in a foreign currency.

• Foreign Currency Summary 1 Trial Balance Report

Review summarized general ledger balances and activity entered in a foreign currency. The report summarizes balances and activity by account segment value.

• Summary 1 Trial Balance Report

Review general ledger actual account balances and activity summarized by account segment value. You can run this report for balances and activity entered in your functional currency or STAT, or translated to a foreign currency.

• Summary 2 Trial Balance Report

Review general ledger account balances and activity for combinations of account segment values with the values of a secondary segment you specify.

• Translation Trial Balance Report

Review your account balances and period activity after running translation.

10. Execution

Automatically generated after concurrent processes have completed. Use these reports to track the status of errors that occurred during your concurrent processing, or to see the results of a successful concurrent process.

• Archive and Purge Audit Report
• Assign Budget Account Ranges Execution Report
• AutoPost Execution Report
• Budget Assignments AutoCopy Execution Report
• Budget Spreadsheet Upload Execution Report
• Calendar Validation Execution Report
• Carry Forward Execution Report
• Create Journal Entries Execution Report
• Delete Journal Import Data Execution Report
• Historical Rates Execution Report
• Intercompany Transfer Program Execution Report
• Journal Import Execution Report
• MassAllocations/MassBudgeting Validation Report
• MassAllocations/MassBudgeting Execution Report
• Mass Funds Check/Reservation Journal Execution Report
• Posting Execution Report
• Recurring Intercompany Transactions Execution Report
• Revaluation Execution Report
• Segment Value Inheritance Execution Report

11. Other

Information about MassAllocation/MassBudget definitions, recurring

journal formulas, statistical units of measure, and value-added taxes received and paid.

• MassAllocation Formula Listing
• Open Encumbrance Balance With Transaction Detail Report
• Recurring Formula Listing
• Units of Measure Report
• Value–Added Tax Report

2. Financial Statement Generator (FSG)

Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible financial report building tool you can use to build your own custom financial reports without programming. You can define reports with complete control over the rows, columns, contents, and calculations in your report. With FSG, you can

• Generate financial reports, such as income statements and balance sheets, based upon data in your general ledger.
• Apply security rules to control what financial information can be printed by specific users and responsibilities in any reports they run using FSG.
• Define your reports with reusable report objects, making it easy to create new reports from the components of reports you’ve already defined.
• Design custom financial reports to meet specific business needs.
• Print as many reports as you need, simultaneously.
• Print the same report for multiple companies, cost centers, departments, or any other segment of your account structure, in the same report request.

• Schedule reports to run automatically.
• Produce ad hoc reports whenever you need them.
• Print reports to tab–delimited files for easy import into client–based spreadsheet programs.

With FSG you use this fundamental row/column concept to build your

own financial reports:

1. Decide which rows and columns will make up your report.

2. Define the rows and columns, then tell FSG what attributes those rows and columns have.

3. Build a report using those rows and columns.

5.Oracle Apps General Ledger: Technical

Main tables in General Ledger can be categorized into following groups

• Set Up
• Journals
• Journal Formulas
• Journal Import
• Budgets
• Budgetary Control
• GCS (Global Consolidation System)
• GIS (Global Intercompany System)
• Oracle Financial Analyzer Link
• Financial Statement Generator

A summarized diagram of important tables and links is shown below

We will discuss tables in important groups as follows

1. Set Up

As we have seen in setups the main setups required for General Ledger are Source, Categories, Periods, Set of Books, Rates (daily, period, historical), Currencies, Chart of accounts, Summary Accounts, Account Code Combinations.

The corresponding tables and interrelations are shown in ERD below

Source: GL_JE_SOURCES_TL

This is the base table for Journal source form. It stores journal entry source names in column JE_SOURCE_NAME and descriptions. Each journal entry in your Oracle General Ledger application is assigned a source name to indicate how it was created. Columns JE_SOURCE_NAME and LANGUAGE are the primary key for this table.

Category: GL_JE_CATEGORIES_TL

This is the base table for Journal category form. It stores journal entry category names in column JE_CATEGORY_NAME and descriptions. Each journal entry in your Oracle General Ledger application is assigned a category name to identify its purpose. Columns JE_CATEGORY_NAME and LANGUAGE are the primary key for this table.

Period: GL_PERIOD_SETS, GL_PERIODS, GL_PERIOD_STATUSES

GL_PERIOD_SETS stores the calendars you define using the Accounting Calendar form. Each row includes the name (PERIOD_SET_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.

GL_PERIODS stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the period name (PERIOD_NAME), start date and end date of the period, the period type, the fiscal year, the period number, and other information.

GL_PERIOD_STATUSES stores the statuses of your accounting periods(in GL_PERIODS). Each row includes the accounting period name and status. CLOSING_STATUS is either ’O’ for open, ’F’ for future enterable, ’C’ for closed, ’P’ for permanently closed, or ’N’ for never opened.

Set of Books: 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.

Rates: GL_DAILY_RATES, GL_TRANSLATION_RATES, GL_HISTORICAL_RATES

GL_DAILY_RATES stores the daily conversion rates for foreign currency transactions. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. This table corresponds to the Daily Rates form.

GL_TRANSLATION_RATES stores your period end and period average exchange rates. This table corresponds to the Period Rates form.

GL_HISTORICAL_RATES stores information about historical foreign currency translation rates. This table corresponds to the Historical Rates form.

Currency: FND_CURRENCIES

FND_CURRENCIES stores information about currencies. Each row includes the currency code (CURRENCY_CODE)established by ISO standard, the name of the currency ,a flag to indicate whether the currency is enabled for use at your site ,a flag to indicate if this is a currency or a statistical unit, precision, symbol denoting the currency, a description of the currency, and information on when the currency becomes active and inactive etc.

Chart of Accounts: FND_ID_FLEX_STRUCTURES

FND_ID_FLEX_STRUCTURES stores structure information about key flexfields. Each row includes the flexfield code and the structure number (ID_FLEX_NUM), which together identify the structure, and the name and description of the structure. Each row also includes values that indicate whether the flexfield structure is currently frozen, whether rollup groups are frozen, whether users can dynamically insert new combinations of segment values through the flexfield pop–up window, and whether the flexfield should use segment cross–validation rules.

Summary Accounts: GL_SUMMARY_TEMPLATES

GL_SUMMARY_TEMPLATES stores the summary account templates you define using the Summary Accounts form. Each row includes the set of books identifier, the start period name, the earliest budget name, the description, and the template contents.

Code Combinations: 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.

2. Journals

As seen in chapter 4 for transactions ,journals in General Ledger are stored in 3 tables for Journal Batch, Journal Header and Journal Lines.

Journal Batch: 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. Important column is JE_BATCH_ID, which stores the unique journal batch identifier.

Journal Header: 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. The unique identifier column is JE_HEADER_ID. Each journal has an associated batch id.

Journal Lines: 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. The unique identifier columns are JE_HEADER_ID,JE_LINE_NUM.

3. Journal Import

GL_INTERFACE

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. The important columns will be discussed in more detail in next chapter.

4. Other Important tables

GL_BALANCES

This is the most frequently used table in GL. 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 and code combinations created. ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. 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 foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. All summary account balances have TEMPLATE_ID not NULL.

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.

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.

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.

GL_INTERCOMPANY_ACCOUNTS

GL_INTERCOMPANY_ACCOUNTS stores information about the intercompany accounts you set up in your Oracle General Ledger application. This table corresponds to the Intercompany Accounts form.

GL_REVALUATION

GL_REVALUATION stores the default gain/loss account which is used when you run Revaluation. The default account is always the last gain/loss account that you used for a particular set of books. You may choose to accept the default account or select a different account when you use the Revalue Balances form.

GL_SUSPENSE_ACCOUNTS

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.

GL_IEA_TRANSACTIONS

GL_IEA_TRANSACTIONS contains the Intercompany Transactions you define using the Enter Intercompany Transaction form. Each row contains the transaction type, the transaction currency, the transaction number, the sending and receiving subsidiaries, and various other information. There is a one–to–many relationship between a row in this table and the rows in the GL_IEA_TRANSACTION_LINES table.

GL_IEA_TRANSACTION_LINES

GL_IEA_TRANSACTION_LINES contains the Intercompany Transaction lines you define using the Enter Intercompany Transaction form. It contains both the sender and receiver lines. Each row contains a line number, the account, the intercompany line debits or credits, and other information. There is a one–to–many relationship between an Intercompany Transaction in the GL_IEA_TRANSACTIONS table and lines in this table.

GL_ACCOUNT_HIERARCHIES

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.

2.Oracle Apps General Ledger: Interfaces, Concurrent & APIs

Following diagram illustrates the flow of data within Oracle General Ledger. Only the important entities are shown

7.1 Journal Import

Journal Import creates journal entries from accounting data you import from Oracle and non-Oracle feeder systems. You can review, change and post imported journal entries the same as any other journal entry. Journal Import supports multiple charts of accounts, as well as foreign currency, Intercompany, statistical, budget, and encumbrance journals. Journal Import creates journal entries from data in the GL_INTERFACE table. Oracle feeder systems automatically populate this table. If you are using a non–Oracle feeder system, you must populate this table yourself. General Ledger validates all data in the interface table before creating journal entries.

7.1.1 Exporting Data From Your Feeder System

Journal Import receives accounting data from the GL_INTERFACE table. For non–Oracle applications, you must import data from your feeder systems to this table. Use an import utility, or have your on–site MIS personnel or Oracle consultant develop an import program for you.

Your import program should convert data from your feeder system into a standard data format that Journal Import can read from the GL_INTERFACE table. Journal Import can then convert your import data into your General Ledger application journal entries. You can write an import program to import data from a non-Oracle system, or you can write an import program to import historical data from your previous accounting system.

The GL_INTERFACE Table

The GL_INTERFACE table is where Journal Import receives accounting data that you import from other systems. When Journal Import receives this data, it validates and converts your import data into journal entries within your General Ledger application. The GL_INTERFACE table is organized by columns in which your General Ledger application categorizes and stores specific accounting data. For example, journal entry source information is stored in the column called JE_SOURCE_NAME. The GL_INTERFACE table contains the following columns:

Column Name Null? Type

STATUS NOT NULL VARCHAR2 (50)

SET_OF_BOOKS_ID NOT NULL NUMBER (15)

USER_JE_SOURCE_NAME NOT NULL VARCHAR2 (25)

USER_JE_CATEGORY_NAME NOT NULL VARCHAR2 (25)

ACCOUNTING_DATE NOT NULL DATE

CURRENCY_CODE NOT NULL VARCHAR2 (15)

DATE_CREATED NOT NULL DATE

CREATED_BY NOT NULL NUMBER (15)

ACTUAL_FLAG NOT NULL VARCHAR2 (1)

ENCUMBRANCE_TYPE_ID NUMBER

BUDGET_VERSION_ID NUMBER

CURRENCY_CONVERSION_DATE DATE

USER_CURRENCY_CONVERSION_TYPE VARCHAR2 (30)

CURRENCY_CONVERSION_RATE NUMBER

SEGMENT1 through SEGMENT30 VARCHAR (25)

ENTERED_DR NUMBER

ENTERED_CR NUMBER

ACCOUNTED_DR NUMBER

ACCOUNTED_CR NUMBER

TRANSACTION_DATE DATE

REFERENCE1 VARCHAR2 (100)

REFERENCE2 VARCHAR2 (240)

REFERENCE3 VARCHAR2 (100)

REFERENCE4 VARCHAR2 (100)

REFERENCE5 VARCHAR2 (240)

REFERENCE6 through REFERENCE9 VARCHAR2 (100)

REFERENCE10 VARCHAR2 (240)

Column Name Null? Type

REFERENCE11 through REFERENCE20 VARCHAR2 (100)

REFERENCE21 through REFERENCE30 VARCHAR2 (240)

GROUP_ID NUMBER (15)

JE_BATCH_ID NUMBER (15)

PERIOD_NAME VARCHAR2 (15)

JE_HEADER_ID NUMBER (15)

JE_LINE_NUM NUMBER (15)

CHART_OF_ACCOUNTS_ID NUMBER (15)

FUNCTIONAL_CURRENCY_CODE VARCHAR2 (15)

CODE_COMBINATION_ID NUMBER (15)

DATE_CREATED_IN_GL DATE

WARNING_CODE VARCHAR2 (4)

STATUS_DESCRIPTION VARCHAR2 (240)

DESCR_FLEX_ERROR_MESSAGE VARCHAR2 (240)

STAT_AMOUNT NUMBER

REQUEST_ID NUMBER (15)

SUBLEDGER_DOC_SEQUENCE_ID NUMBER

SUBLEDGER_DOC_SEQUENCE_VALUE NUMBER

USSGL_TRANSACTION_CODE VARCHAR2 (30)

ATTRIBUTE1 through ATTRIBUTE20 VARCHAR2 (150)

CONTEXT VARCHAR2 (150)

CONTEXT2 VARCHAR2 (150)

CONTEXT3 VARCHAR2 (150)

INVOICE_DATE DATE

INVOICE_AMOUNT NUMBER

INVOICE_IDENTIFIER VARCHAR2 (20)

TAX_CODE VARCHAR2 (15)

7.1.2 Assigning Values for Accounts

You can specify your accounts in the GL_INTERFACE table in one of two ways: segment specification or code combination ID specification.

• Segment Specification

Assign an account value for each segment that you enabled in your General Ledger application. For example, if you enabled four account segments, you need to first determine into which columns of the GL_INTERFACE table you should enter data. This can be done by looking at the Column field of each segment in the Key Flexfield Segments window. In this example we find that:

1. Segment 1 corresponds to the column SEGMENT1
2. Segment 2 corresponds to the column SEGMENT2
3. Segment 3 corresponds to the column SEGMENT4
4. Segment 4 corresponds to the column SEGMENT5

Note: The column named SEGMENT3 is not used. Given the above information above, you should load the data as follows:

Data for Flexfield Load Into:

Segment 1 GL_INTERFACE.SEGMENT1

Segment 2 GL_INTERFACE.SEGMENT2

Segment 3 GL_INTERFACE.SEGMENT4

Segment 4 GL_INTERFACE.SEGMENT5

Load valid enabled segment values for your enabled segments into the GL_INTERFACE table. The segment values must already be defined in your General Ledger application. For example, value 01 is not the same as value 1. You can specify Maximum Size and right–justify Zero–fill Numbers when you define your value sets in the Value Sets form. Maximum Size indicates the maximum width of each segment value that Journal Import expects. Right–justify Zero–fill Numbers indicates whether your account should right justify and zero–fill numbers when you enter values for a particular value set. If you have the Right–justify Zero–fill Numbers option enabled, and your Maximum Size is three, then your segment value would be 001. However, if your Maximum Size is four, then your segment value wouldbe 0001. Journal Import does not allow null values in enabled segments.

• Code Combination ID Specification

Alternatively, you can enter a code combination ID to identify your account segments. You can find a list of valid account code combinations and their corresponding code combination IDs in the GL_CODE_COMBINATIONS table. If you want Journal Import to use the code combination ID to create your journal entries, enter the appropriate code combination ID in the CODE_COMBINATION_ID column of the GL_INTERFACE table and do not enter values in the SEGMENT1 through SEGMENT30 columns.

If you enter values for your account segments in the SEGMENT1 through SEGMENT30 columns and enter a value in the CODE_COMBINATION_ID column, Journal Import uses the Segment column values to create your journal entries.

If you enter segment values for an invalid account in the GL_INTERFACE table, General Ledger prints the invalid account in your Journal Import Execution Report. If you enter a code combination ID and if suspense posting is disabled, General Ledger prints the invalid code combination ID in your Journal Import Execution Report. If you enter a code combination ID and if suspense posting is enabled, General Ledger prints only the segment value separators in your Journal Import Execution Report. Therefore, we recommend that you disable suspense posting if entering code combination IDs.

7.1.3 Assigning Values for Additional Required and Conditionally Required Columns

You must enter values in all columns of the GL_INTERFACE table that require values, which includes all of the not null columns, in order for Journal Import to successfully convert your import data into journal entries. Enter values in the following required columns of the GL_INTERFACE table:

STATUS: Enter the value NEW to indicate that you are bringing new data into your General Ledger application.

SET_OF_BOOKS_ID: Enter the appropriate set of books ID for your transaction. You define your set of books in the Set of Books form of your General Ledger application. 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).

Suggestion: You may use the following SQL*Statement to access the appropriate set of books ID: SELECT SET_OF_BOOKS_ID, NAME FROM GL_SETS_OF_BOOKS;

USER_JE_SOURCE_NAME: Enter the journal entry source name for your transaction. You define journal sources in the Journal Sources form of your General Ledger application. You can find a list of valid values in the USER_JE_SOURCE_NAME column of the Journal Entry Sources table (GL_JE_SOURCES.USER_JE_SOURCE_NAME).

USER_JE_CATEGORY_NAME: Enter the journal category name for your transaction. You define journal categories in the Journal Categories form of your General Ledger application. You can find a list of valid values in the USER_JE_CATEGORY_ NAME column of the Journal Entry Categories table (GL_JE_CATEGORIES.USER_JE_ CATEGORY_NAME).

ACCOUNTING_DATE: Enter the accounting date on which your transaction occurred. Your General Ledger application automatically assigns your journal batch to the accounting period that includes your accounting date. If you have average balance processing enabled, General Ledger uses your defined Effective Date Rules to validate the accounting date against your transaction calendar to determine the transaction’s effective date.

CURRENCY_CODE: Enter the currency code for your transaction. You define new currency codes in the Currencies form of your General Ledger application. You can find a list of valid values in the CURRENCY_CODE column of the Currencies table (FND_CURRENCIES.CURRENCY_CODE).

DATE_CREATED: Enter the date your import journal entry line was created. The information you enter here is for your own records, and does not appear in your General Ledger application.

CREATED_BY: Enter an ID that you can use to identify the data coming from your feeder system. The ID you enter provides you with an audit trail from Journal Import data to your feeder system. However, your Journal Import data will be removed from the GL_INTERFACE table after it is successfully imported, and this ID will not appear in your General Ledger application.

ACTUAL_FLAG: Enter the value A for actual amounts, B for Budget amounts, or E for encumbrance amounts.

ENCUMBRANCE_TYPE_ID: If you entered the value E in the ACTUAL_FLAG column of the GL_INTERFACE table, you must enter the appropriate encumbrance ID. You define new encumbrance types in the Encumbrance Types form of your General Ledger application. You can find a list of valid values in the ENCUMBRANCE_TYPE_ID column of the Encumbrance Types table (GL_ ENCUMBRANCE_TYPES. ENCUMBRANCE_TYPE_ID).

Suggestion: We recommend you use the following SQL*Statement to identify the appropriate encumbrance type ID: SELECT ENCUMBRANCE_TYPE_ID, ENCUMBRANCE_TYPE FROM GL_ENCUMBRANCE_TYPES WHERE ENABLED_FLAG = ’Y’;

BUDGET_VERSION_ID: If you entered the value B in the ACTUAL_FLAG column of the GL_INTERFACE table, you must enter the appropriate budget ID. You define new budget versions in the Define Budget form of your General Ledger application. You can find a list of valid values in the BUDGET_ VERSION_ID column of the Budget Versions table (GL_BUDGET_VERSIONS.BUDGET_VERSION_ ID).

Suggestion: We recommend you use the following SQL*Statement to identify the appropriate budget version ID: SELECT BUDGET_VERSION_ID, BUDGET_NAME FROM GL_BUDGET_VERSIONS WHERE STATUS IN (’C’,’O’);

PERIOD_NAME: Enter a period name for your budget transactions (ACTUAL_FLAG = B) only. This column is required when you are importing budget data using Journal Import. If you want to import budget data using Journal Import, you must supply a period name instead of an accounting date. And, your period name must be associated with an open budget fiscal year.

ENTERED_DR: Enter the debit amount for each line of your transaction. Enter a value for the ENTERED_DR or the ENTERED_CR column in a given row, but not both values in one row.

ENTERED_CR: Enter the credit amount for each line of your transaction. Enter a value for the ENTERED_DR or the ENTERED_CR column in a given row, but not both values in one row.

7.1.4 Assigning Values for Currency Conversion

You can enter values for your actual foreign currency data in one of two ways. You can specify the entered amount along with a conversion rate type and date and let your General Ledger application calculate the converted amount for you. Or, you can directly specify the entered and converted amounts and not specify the conversion rate, type and date. Do not enter values in the following columns for encumbrance and budget foreign currency data. Enter values for your actual foreign currency data only in the following columns of the GL_INTERFACE table:

System–Calculated Conversion

USER_CURRENCY_CONVERSION_TYPE: Enter a currency conversion type for your actual foreign currency transactions. Acceptable values are User, Spot, Corporate, or any other type you define in the Conversion Rate Types form. If you enter a rate type of User, then you must also enter a conversion rate in the CURRENCY_CONVERSION_ RATE column. For all other conversion types you must enter a conversion date in the CURRENCY_ CONVERSION_DATE column. You can find a list of valid values in the USER_CURRENCY_CONVERSION_TYPE column of the Conversion Types table (GL_DAILY_CONVERSION_TYPES.USER_CURRENCY_CONVERSION_TYPE)

CURRENCY_CONVERSION_DATE: Enter a currency conversion date for your actual foreign currency transactions. If you enter a conversion type other than User in the USER_CURRENCY_CONVERSION_TYPE column, you must enter a value in this column. If your conversion type is User, the default value for this column is the accounting date.

CURRENCY_CONVERSION_RATE: Enter a currency conversion date for your actual foreign currency transactions. If you enter a conversion type of User in the USER_CURRENCY_CONVERSION_TYPE column, you must enter a value in this column. If you enter a conversion type other than USER, do not enter anything in this column.

User–Entered Conversion

ACCOUNTED_DR: Enter a converted debit amount for your actual foreign currency transactions. Enter a value for the ACCOUNTED_DR or the ACCOUNTED_CR column in a given row, but not both values in one row. You must enter a value for ENTERED_DR if you entered a value for ACCOUNTED_DR.

ACCOUNTED_CR: Enter a converted credit amount for your actual foreign currency transactions. Enter a value for the ACCOUNTED_DR or the ACCOUNTED_CR column in a given row, but not both values in one row. You must enter a value for ENTERED_CR if you entered a value for ACCOUNTED_CR.

Required NULL Columns in the GL_INTERFACE Table

Some columns in the GL_INTERFACE table must be NULL as Journal Import uses them for internal processing or does not use them in the current release. The following columns must be NULL in your General Ledger application:

REFERENCE3: Do not enter a value in this column.

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

TRANSACTION_DATE: Do not enter a value in this column.

JE_BATCH_ID: Do not enter a value in this column.

JE_HEADER_ID: Do not enter a value in this column.

JE_LINE_NUM: Do not enter a value in this column.

CHART_OF_ACCOUNTS_ID: Do not enter a value in this column.

FUNCTIONAL_CURRENCY_CODE: Do not enter a value in this column.

DATE_CREATED_IN_GL: Do not enter a value in this column.

WARNING_CODE: Do not enter a value in this column.

STATUS_DESCRIPTION: Do not enter a value in this column.

DESC_FLEX_ERROR_MESSAGE: Do not enter a value in this column.

REQUEST_ID: Do not enter a value in this column.

SUBLEDGER_DOC_SEQUENCE_ID: Do not enter a value in this column.

SUBLEDGER_DOC_SEQUENCE_VALUE: Used for communication between General Ledger and the subledgers. Do not populate with your own data.

7.1.5 About Journal Import Validation

Journal Import validates all of your data before it creates journal entries in your General Ledger application. If you allow suspense posting for

Batch Level Validation

Journal Level Validation

Journal Entry Line Level Validation

your set of books, Journal Import will assign lines with invalid accounts to that account. Journal Import rejects all other invalid lines, and they remain in the GL_INTERFACE table where you can correct them online in the Correct Journal Import Data form or in your feeder system. Journal Import also prints your error lines in the Journal Import Execution Report. Journal Import validates the following attributes to ensure that a batch with the same name does not already exist for the same set of books and period in your General Ledger application:

• Set of books

• Period name

• Batch name

Journal Import also checks to ensure that more than one journal entry with the same name does not exist for a batch. Journal Import validates the following attributes to ensure that your journals contain the appropriate accounting data:

• Set of books

• Period name

• Source name

• Journal entry name

• Currency code

• Category name

• Actual flag

• Encumbrance type ID

• User conversion type

• Accounting date

• Budget version ID

• Reversal period (GL_INTERFACE.REFERENCE8)

Journal Import validates the following attributes to ensure that your journal entry lines contain the appropriate accounting data:

Account Validation

Journal Import validates your account code combinations in a number of ways. Journal Import will successfully import your accounting data if your code combinations meet the following validation requirements:

• You allow detail posting to segment combinations.

• You have enabled your code combinations for the accounting date you specify.

• Your code combinations do not include summary accounts.

Effective Date Validation

Journal Import validates each transaction’s Accounting Date to be sure it is a valid business day. If the date is a valid business day, General Ledger uses it as the transaction’s effective date. If the Accounting Date is not a valid business day, Journal Import uses your defined Effective Date Rules to determine how to handle the transaction. If the Effective Date Rule is:

Fail: Journal Import will reject transactions when the Accounting Date is not a valid business day (no posting takes place). The Accounting Date is considered the effective date.

Leave Alone: Journal import will accept all transactions regardless of the Accounting Date. The Accounting Date is considered the effective date.

Roll Date: Journal Import will accept the transaction, but roll the Accounting Date back to the nearest valid business day (within the same period) to determine the transaction’s effective date. If there is no prior valid business day within the same period, the Accounting Date is rolled forward to determine the effective date.

Additional Information: If you specify a reversing effective date, Journal Import will validate the date using the same process and rules noted above for accounting dates.

Descriptive Flexfield Validation

Journal Import validates your descriptive flexfield segments in a number of ways depending on the particular descriptive flexfield. If your descriptive flexfield segments are null, then Journal Import does not validate the descriptive flexfield. Otherwise, Journal Import will successfully import your descriptive flexfield data if your descriptive flexfield segments meet the following validation requirements:

Journals – Journal Entry Line Descriptive Flexfield

• The descriptive flexfield global segments have valid values.

• The descriptive flexfield context is a valid value.

• The descriptive flexfield context dependent segments have valid values.

Journals – Captured Information Descriptive Flexfield

• The descriptive flexfield global segments have valid values.

• The descriptive flexfield context dependent segments have valid values.

Value Added Tax Descriptive Flexfield

The descriptive flexfield context is set to Yes or No.

• The descriptive flexfield context dependent segments have valid values.

USSGL Transaction Code

If you use Public Sector General Ledger, Journal Import validates the USSGL Transaction Code to ensure that it has been defined in the Public Sector Transaction Codes window.

7.1.6 Importing Information to GL

Except for Oracle Assets, all sub-ledgers integrate with Oracle General Ledger (GL) through the GL_INTERFACE table.

Run the Journal Import process automatically from the sub-ledger at transaction transfer time or manually from Oracle General Ledger at a later time.

Transfer information to Oracle General Ledger in detail (show all journal entry lines for transactions against each account within a category) or summary (show only totals for transactions against each account within a category)

Note: Oracle assets transfer information directly into the GL_JE_BATCHES, GL_JE_HEADERS, and GL_JE_LINES tables.

7.2 GL Daily Rates Interface

7.2.1 Concepts You Should Know

Set of Books

A set of books consists of the following elements: an accounting calendar, an account structure and currency. If one of these elements is different, you must create a separate set of books.

Multiple Reporting Set of Books

MRC is beneficial for companies who must regularly and routinely report its transactions and financial results in multiple currencies, other than its primary functional currency. With MRC, you create a primary set of books and then associate several reporting sets of books (with different currencies) to that primary set of books.

7.2.2 Foreign Currency Concepts

There are three key concepts in Oracle General Ledger that pertain to foreign currency:

• Conversion

This refers to foreign currency transactions that are immediately converted at the time of entry to the functional currency of the set of books in which the transaction takes place.

• Revaluation

This adjusts liability or asset accounts that may be materially understated or overstated at the end of a period due to significant fluctuation in the exchange rate between the time the transaction was entered and the end of the period.

• Translation

This refers to the act of restating an entire set of books of balances for a company from the functional currency to a foreign currency.

7.2.3 Conversion

Oracle General Ledger automatically converts the amounts in foreign currency journals to functional currency equivalents using supplied daily exchange rates (GL_DAILY_RATES).

GL Daily rates interface creates regular as well as inverse entry of conversion rates into the regular GL_DAILY_RATES table.

7.2.4 The GL_DAILY_RATES_INTERFACE Table

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 rate information.

The columns in GL_DAILY_RATES_INTERFACE are described below.

Column Name Null? Type

FROM_CURRENCY NOT NULL VARCHAR2 (15)

TO_CURRENCY NOT NULL VARCHAR2 (15)

FROM_CONVERSION_DATE NOT NULL DATE

TO_CONVERSION_DATE NOT NULL DATE

USER_CONVERSION_TYPE NOT NULL VARCHAR2 (30)

CONVERSION_RATE NOT NULL NUMBER

MODE_FLAG NOT NULL VARCHAR2 (1)

INVERSE_CONVERSION_RATE NUMBER

USER_ID NUMBER (15)

ERROR_CODE VARCHAR2 (30)

LAUNCH_RATE_CHANGE VARCHAR2 (1)

CONTEXT VARCHAR2 (150)

ATTRIBUTE1 to ATTRIBUTE15 VARCHAR2 (150)

USED_FOR_AB_TRANSLATION VARCHAR2 (1)

7.2.5 Required and Conditionally Required Columns

The field descriptions below are based on the example below.

FROM_CURRENCY: The source currency applicable to the conversion rate. The amount denominated in the from–currency multiplied by the conversion rate gives the amount denominated in the to–currency.

TO_CURRENCY: The target currency applicable to the conversion rate.

FROM_CONVERSION_DATE: The starting date of the range of dates for which rows will be inserted into GL_DAILY_RATES. General Ledger will insert one row for each date in the range. Each date will have the same conversion rate you specify.

TO_CONVERSION_DATE: The ending date of the range of dates for which rows will be inserted into GL_DAILY_RATES.

Additional Information: The range of dates specified by FROM_CONVERSION_DATE and TO_CONVERSION_DATE cannot exceed 366 days.

USER_CONVERSION_TYPE: The conversion type that users see displayed in the Daily Rates window. General Ledger automatically converts the user conversion type into the conversion type ID that is stored in the GL_DAILY_RATES table.

CONVERSION_RATE: The currency conversion rate. This is the rate by which the amount denominated in the from–currency is multiplied to arrive at the amount denominated in the to–currency.

Additional Information: If the row you are entering in the interface table is to delete rates in GL_DAILY_RATES, enter a dummy CONVERSION_RATE.

MODE_FLAG: For each row, enter ’D’ if you want to delete matching rows from the GL_DAILY_RATES table. Enter ’I’ if you want to insert new rows. Additional Information: If you specify ’I’ as the MODE_FLAG and the combination of from–currency, to–currency, conversion date, and user conversion type already exist in GL_DAILY_RATES, the existing rate will be updated with the new rate you specified in the interface table. If you specify ’D’ as the MODE_FLAG, General Ledger will also delete corresponding inverse rates rows in GL_DAILY_RATES.

Note: Any rows you enter in GL_DAILY_RATES_INTERFACE that fail validation will remain in the interface table and will not be moved to GL_DAILY_RATES. Also, the mode flag will change to X and the error code column will be populated. Use a SQL*Plus SELECT statement to check if any of the rows you loaded into the interface table failed validation. You cannot reprocess rejected rows that remain in the interface table after failing validation. To process the correct data, you must first delete the rejected rows from the interface table then enter the correct data as new rows in the table. The new data will be processed as usual.

Optional Columns

INVERSE_CONVERSION_RATE: The inverse of the conversion rate. This is the rate by which the amount denominated in the to–currency is multiplied to arrive at the amount denominated in the from–currency.

Additional Information: If you do not provide this value, General Ledger will calculate the inverse rate from the CONVERSION_RATE column and insert the appropriate inverse rate rows into GL_DAILY_RATES.

USER_ID: The user ID of the person who is adding rows to the interface table. To determine the user ID for a specific user name, use the following SQL*Plus statement: select user_id from fnd_user where user.name=’<user name>’

LAUNCH_RATE_CHANGE: If you want the rate change program to run automatically, enter a ’Y’ in the LAUNCH_RATE_CHANGE column for one row of the rates you are loading. Leave this column blank for the remaining rows. Otherwise, multiple concurrent requests will be launched when only one is required to load all of your rates. When a daily rate has changed, the rate change program will outdate average translations in those average balance sets of books that use the changed daily rate.

CONTEXT: The descriptive flexfield context.

ATTRIBUTE1 through ATTRIBUTE15: Any descriptive flexfield information associated with the daily rate.

7.2.6 Other Columns

ERROR_CODE: The text of the error message you receive if the row in the interface table failed validation. This column is used by the system. No user entry is needed.

USED_FOR_AB_TRANSLATION: This column is used internally by General Ledger when copying rates to GL_DAILY_RATES. Do not make any entries in this column.

7.2.7 Daily Rate Conversion Entry

Before you can enter foreign currency transactions there are number of steps you must complete.

• Define Currencies
• Enable Currencies
• Define Sets of Books
• Enter period rates
• Define daily conversion rate types
• Enter daily rates.

7.3 GL Budget Accounting Cycle

7.3.1 Defining Budgets and Budget Organizations

1. Define up to 60 budget periods in your budget.
2. Define budget organizations at any level: cost center, division, and so on.

Assign passwords to budgets and create new organizations by copying

existing budget organizations.

7.3.2 Entering Budgets

1. Enter budget data using standard, formula-based, and automatic allocations.
2. Enter amounts using annual spread, fixed amounts, and calculated methods using an individual account layout or a worksheet-type screen layout.
3. Transfer budget amounts from one account to another.

7.3.3 Reviewing, Correcting, and Freezing Budgets

1. Review budget amounts and variances by period and account combination.
2. Perform online review and compare summary balances of master-detail budgets.
3. Freeze budgets to prevent further update to completed budgets.

7.3.4 Report on Budgets

1. Run standard or customized budget reports.

7.3.5 Uploading Budgets from External Sources

There are 2 main considerations for uploading budgets from external sources.

7.3.6 Software Compatibility

• Use a spreadsheet to create a budget, then save it in ASCII format.
• Use any file transfer program to move the file from your PC to a server. Then use SQL*Loader to move the information from the file to the GL_BUDGET_INTERFACE table

7.3.7 Spreadsheet Design

Use a different spreadsheet for each budget organization.

Make certain that each spreadsheet contains all the information's required, such as Budget Name, Budget Organization Name, Account Combinations, Periods, and Amounts.

Oracle General Ledger validates uploaded data and reports error in the Budget Spreadsheet Upload Status Report.

7.3.8 Understanding the Budget Interface Table

The first step in transferring your budget data from an outside source to your General Ledger application is to load your data into your General Ledger application Budget Interface table. Once you load your budget information into the Budget Interface table, you can run Budget Upload to post your budget data into your General Ledger application. Budget Upload uses the Budget Interface table GL_BUDGET_INTERFACE to upload budget information. The Budget Interface table is organized into columns in which your General Ledger application categorizes and stores specific budget information. For example, the name of your budget is stored in the column called Budget_Name. You must specify valid values for each of the required columns in this table to successfully complete a Budget Upload. You may specify values for the optional columns within this table. These values are validated before your General Ledger application updates budget balances. The Budget Interface table contains the following columns:

Column Name Null? Type

BUDGET_NAME NOT NULL VARCHAR2 (15)

BUDGET_ENTITY_NAME NOT NULL VARCHAR2 (25)

CURRENCY_CODE NOT NULL VARCHAR2 (15)

FISCAL_YEAR NOT NULL NUMBER (15)

UPDATE_LOGIC_TYPE NOT NULL VARCHAR2 (1)

BUDGET_ENTITY_ID NUMBER (15)

SET_OF_BOOKS_ID NUMBER (15)

CODE_COMBINATION_ID NUMBER (15)

BUDGET_VERSION_ID NUMBER (15)

PERIOD_TYPE VARCHAR2 (15)

DR_FLAG VARCHAR2 (1)

STATUS VARCHAR2 (1)

ACCOUNT_TYPE VARCHAR2 (1)

LAST_UPDATE_DATE DATE

LAST_UPDATED_BY NUMBER (15)

REQUEST_ID NUMBER (15)

PERIOD1_AMOUNT through PERIOD60_AMOUNT NUMBER

SEGMENT1 through SEGMENT30 VARCHAR2 (25)

7.3.9 Creating Your Budget Spreadsheet

If you plan to import budget information from a spreadsheet, you should use a separate spreadsheet or each budget organization. Your budget spreadsheet should contain all of the budget information you need for your company or agency. However, before you can upload your budgets from a spreadsheet you must organize your budget information according to the structure of the Budget Interface table. Therefore, each column of your spreadsheet should correspond to a column in the Budget Interface table.

You can also organize your budget information in any way you want in the working section, and then automatically copy the information in your working section to an interface section that corresponds to the Budget Interface table.

To enter required budget information in your spreadsheet:

Define columns that correspond to the required columns in the Budget Interface table. The following is a list of required columns and the values you must enter:

Currency_Code: Enter the currency for your account.

Budget_Name: Enter the name of the budget to which you want to budget amounts. You can only upload budgets for current or open budgets.

Budget_Entity_Name: Enter the budget organization to which you want to budget amounts.

Fiscal_Year: Enter the fiscal year to which you want to budget. You can only upload budget amounts for open budget fiscal years within a budget. You can open a new budget fiscal year in the Define Budget window.

Update_Logic_Type: Enter the value R or A. Enter the value R if you want the amounts you enter to replace existing amounts. Enter the value A if you want the amounts you enter to add to existing amounts.

Period1_Amount through Period60_ Amount: Enter an amount for each budget period in your open fiscal year. You can enter amounts for up to 60 periods for each open fiscal year.

Segment1 through Segment30: Enter existing or new valid account segment values in your spreadsheet for each account segment you enabled in your General Ledger application.

7.3.10 Upload Budgets

How to Upload Budgets:

• Open the Upload Budget window and enter the budget and budget organization that will receive the uploaded budget amounts. You can upload budgets for all of your budget organizations by entering "All" for your budget organization.
• Select Upload. Oracle General Ledger submits a concurrent process to upload budget information from the GL_BUDGET_INTERFACE table.
• Review the Budget Spreadsheet Upload Execution Report for the status of your uploaded budget information.

Oracle general Ledger prompts for a password if the specified budget organization is protected by a password.

7.3.11 About Budget Upload Validation

Budget Upload validates all of your budget information for compatibility with General Ledger. Budget Upload validates your data by ensuring that the columns of the Budget Interface table reference the appropriate values and columns in your spreadsheet. Budget Upload also checks to make sure that the following conditions are true:

• Your account is assigned to a budget organization

The budget entry type for your account is Entered

• Your budget is not Frozen

• Your budget organization is not Frozen

• Your budget fiscal year is open for your budget

General Ledger automatically deletes budget records in the Budget Interface table for all accounts it can successfully update. General Ledger does not delete budget records in the Budget Interface table or update budget balances for accounts containing errors. If Budget Upload encounters any problems, such as invalid data during the upload process, it lists the accounts containing errors as well as the associated budget and budget organization in the Budget Spreadsheet Upload Execution report.

6.Oracle Apps General Ledger: Workflows & Alerts

1. Oracle Workflow

Oracle Workflow is a tool which lets you automate and continuously improve business processes, routing information of any type according to business rules.

Main features of Oracle Workflow are

Routing Information

Oracle Workflow lets you provide each person with all the information they

need to take action. Oracle Workflow can route supporting information to each decision maker in a business process.

Defining and Modifying Business Rules

Oracle Workflow lets you define and continuously improve your business processes using a drag–and–drop process designer. Unlike workflow systems that simply route documents from one user to another with some approval steps, Oracle Workflow lets you model sophisticated business processes. You can define processes that loop, branch into parallel flows and then rendezvous, decompose into subflows, and more. Because Oracle Workflow can decide which path to take based on the result of a stored procedure, you can use the full power of PL/SQL, the language of the Oracle8 Server, to express any business rule that affects a workflow process.

Delivering Electronic Notifications

Oracle Workflow extends the reach of business process automation throughout the enterprise and beyond to include any E–mail or Internet user. Oracle Workflow lets people receive notifications of items awaiting their attention via E–mail, and act based on their E–mail responses. You can even view your list of things to do, including necessary supporting information, and take action using a standard Web browser.

2. Workflows in Oracle General Ledger

There are 4 Workflows in General Ledger out of which Journal Approval is more often used and explained in detail

1. Journal Approval Process

The GL Journal Approval Process obtains the necessary management approvals for manual journal batches. The process validates the journal batch, determines if approval is required, submits the batch to approvers (if required), then notifies appropriate individuals of the approval results. The process has a result type of GL Journal Approval Process Result that gives one of four results:

Approval Not Required: The journal batch does not need approval.

Approved: The journal batch was approved by all necessary approvers. In some cases, this may be the preparer.

Rejected: The journal batch was rejected by an approver.

Validation Failed: The journal batch failed the validation process and was never submitted to the approver. The process consists of 5 unique activities, some of which are reused, to comprise the 9 activity nodes that appear in the workflow diagram:

A brief description of each node is given below

Node 1 (Start): This activity marks the start of the process.

Node 2 (GL Initialization & Validation Process) :

This activity is a subprocess that performs initialization, then validates the journal batch. If the journal batch is valid, the subprocess also determines whether the batch requires approval.

Node3 (GL Preparer Approval Process) :

This activity is a subprocess that determines if the journal batch preparer is authorized to approve his/her own journal batch. If so, the batch is approved, the approver name is set, and notifications are sent.

Node4 (GL Approval Process) :

This activity is a subprocess that finds all necessary approvers, seeks journal batch approval, and sends notifications of approval or rejection.

Node5 (End) :

This function activity marks the end of the process. Although the activity itself does not have a result type, each node of this activity in the process must have a process result assigned to it.

Customizing Journal Approval

You can customize Journal Approval to meet your organization’s specific needs through three mechanisms:

Profile options: There are two profile options that affect how Journal Approval operates:

• Journals: Allow Preparer Approval: Determines whether

preparers can approve their own journals.

• Journals: Find Approver Method: Sets the default method for

seeking approval.

Workflow activity settings: You can change the default settings for the:

• Request Approval From Approver timeout: The standard setting is 7 days. After this time has expired, Journal Approval notifies the preparer that no approver response has been received.

• Reached Manager Notification Resend Limit: The standard setting is 1 notification. Journal Approval will resend notifications to the approver until the limit is reached.

• Default Error Notification: Journal Approval uses Oracle Workflow’s standard error processing to handle runtime errors. You can choose to send a notification to your system administrator whenever such errors occur. Open the Journal Approval workflow file in Oracle Workflow and set the Performer for the Default Error Notification, in the Default Error process, to your system administrator’s userid.

Customizable activities: You can customize four activities and one

process:

• Customizable: Is Journal Batch Valid activity

• Customizable: Does Journal Batch Need Approval activity

• Customizable: Is Preparer Authorized to Approve activity

• Customizable: Verify Authority activity

• Customizable: Verify Authority Process

2. AutoAllocations Process

When you generate step-down AutoAllocations, the workflow process initiates the AutoAllocation process and validates and generates the Mass Allocation and Recurring Journal batches that are defined in the AutoAllocation. The workflow process also determines whether journal approval is required for each generated journal batch, submits the batches to the appropriate users for approval if required, and notifies the appropriate users of the approval results. If an error occurs during the AutoAllocation process, the designated user or users can choose to roll back the AutoAllocation process, which reverses any posted journals.

3. Global Intercompany System

The Global Intercompany System (formerly CENTRA) is an enhanced feature for Release 11i, and has been backported to Release 11. It provides an environment for multiple companies to exchange intercompany transactions. The workflow process notifies the receiver company when a sender company initiates an intercompany transaction and requires approval from the receiver, or when the sender company recalls or reverses an intercompany transaction. The workflow process notifies the sender company when a receiver company approves or rejects an intercompany transaction that the sender had initiated. In addition, a threshold amount can be set to limit the volume of notifications. The workflow process is initiated when the sender submits, recalls, or reverses an intercompany transaction, or when the receiver rejects or accepts an intercompany transaction.

4. Global Consolidation System Cross Instance Data Transfer

You can automate the Global Consolidation System to consolidate data from remote subsidiary ledger database instances to a central consolidation database instance and optionally use workflow notifications to notify users of the cross instance consolidation status. Each notification provides the user with consolidation transfer details including source database name, mapping rule, set of books, group IDs, and concurrent request ID. When a user chooses to automatically run Journal Import or AutoPost on the central consolidation database, the workflow notification cites the status of success or failure of the concurrent request. If the Journal Import or AutoPost process fails, the workflow notification recommends reviewing the request log on the central consolidation database for further details.

7. Oracle Apps General Ledger: Other / Support Input

Here are few queries which a beginner has to know when they start working on Financial Modules.

ap375496.sql – AP Trial Balance Rebuild script

This script can be useful if your AP Trial Balance report is not reporting correctly on outstanding liability. The script is a data fix script that can be run to recreate the trial balance records in the AP_TRIAL_BALANCE table for all records (payments and invoice distributions) that have been transferred to GL within a specified range of accounting dates. This script is contained in patch 375496 and can be downloaded from Metalink.

Account Generator FA Test Script

This script assists in diagnosing problems in Oracle Assets concurrent processes that fail due to account generation. It replaces the Flexbuilder Test screen used in Release 10. The output from the script tells the User the following –

1. The code combination id that Account Generator attempted to build.
2. The reason why Account Generator could not build this code combination.
3. If Dynamic insertion is set to ‘Yes’.

When you run the script, it will prompt you for a number of parameters. All the information that feeds this script is contained in the log file from the failed run.

Release 11 of Oracle Assets is delivered with this SQL*Plus test script called faxagtst.sql. You should find this in the $FA_TOP/admin/sql directory. The latest script comes with 11.03 Minipack. Contact Oracle Support Services for the latest version.

When developing new reports ensure you have the following parameters (defined using the Oracle Reports Parameter Screen) which will be used in the user exit calls and SQL statements.

Name: P_CONC_REQUEST_ID

Data Data Type: NUMBER

Width: 15

Initial Value: 0

You always create this lexical parameter. "FND SRWINIT" uses this parameter to retrieve information about this concurrent request.

You should have the following parameter:

Name: P_MIN_PRECISION

Data Type: NUMBER

Width: 2

Initial Value:

You reference this lexical parameter in your FND FORMAT_CURRENCY user exit call.

This will provide you with several pieces of useful information, including the Database version, the Applications version, the Form Name and Form Version

8. Oracle Apps General Ledger: Exercises

1. Develop a Form which accepts the following parameters. Parameter details

are given under prerequisites section. The form layout is shown below. Template form can be found under au_top directory.

Journal Inquiry

Period Name(example, MAR-99) -------- Batch Name(% allowed) ----------

Source Code (% allowed) -------- JE Name (% allowed) ----------

Category Code (% allowed) -------- JE Creator User ID (RACF ID) -------

Posted Status -------- Part of Creator’s Name(% allowed) -------

Posted Date(format, yyyymmdd) -------- Effective Date(format, yyyymmdd) -------

Sort By

O JE Line Number

O Account and Department

O Department and Account

<Clear Form> <Cancel>

Prequisites

Period Name

Character field length of 7 Char. Enter Period Name, or Select from LOV. The value entered is validated against LOV provided for Periods.

Batch Name

Character Field Length (50) Char, User may enter the Batch Name in the text field provided. The user is allowed to enter partial values for Batch Name. The Batch Name can also be selected from LOV.

Source Code

Character Field Length (30) Char, User may enter the Source Code in the text field provided. The user is allowed to enter partial values for Source Code. The Source Code can also be selected from LOV.

JE Name

Character Field Length (50) Char, User may enter the JE Name in the text field provided. The user is allowed to enter partial values for JE Name. The JE Name can also be selected from LOV.

Category Code

Character Field Length (30) Char, User may enter the JE Name in the text field provided. The user is allowed to enter partial values for JE Name. The JE Name can also be selected from LOV.

JE Creator User ID (RACF ID)

Character Field Length (30) Char, User may enter the JE Creator User ID in the text field provided. The JE Creator User ID can also be selected from LOV.

Posted Status

Character field Length (1) Char. User shall select one value from a static list item (not LOV) .This is a set of 4 values defaulted to ‘Any Status’. User can only select from this list. User cannot enter any value into the field.

Return Values Display values: -

A Any Status

P Posted

U Unposted

E Error

Part of Creator’s Name

Character Field Length (30) Char, User may enter the Creator’s Name in the text field provided. The user is allowed to enter partial values forCreator’s Name.

Posted Date

Character Field Length (8) Char, User may enter the Posted Date in the text field provided. The date format entered is validated against LOV.

Effective Date

Character Field Length (8) Char, User may enter the Effective Date in the text field provided. The date format entered is validated against LOV.

Sort By

Char Field Length (1). Select one of the Sort By options. The Different options are

(o) JE Line Number (default)

(o) Account and Department

(o) Department and Account

Button Pallet

There are 2 buttons in the pallet

1. Clear Form – Clears the form

2. Cancel – Exits the form

The table below lists the Item name, parameter Length, datatype, Default value,

LOV query.

Field/Column	Type & Field Length	Reqd Y/N?	Default	Validation Logic	LOV / QuickPick Query
Period Name/ p_period_month	Varchar2(7)	Y	None	Validation against LOV	SELECT gp.period_name FROM gl_periods gp , gl_sets_of_books gsb , gl_period_types gpt WHERE gp.period_set_name = gsb.period_set_name AND gsb.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') AND gp.period_type = gpt.period_type AND gpt.user_period_type = 'Month_Adj' ORDER BY gp.start_date DESC
Source Code/ p_user_je_s_name	Varchar2(30)	N	None	Partial entry of the values with (%) is allowed and not validated against LOV	SELECT SUBSTR(user_je_source_name,1,30) user_je_source_name , SUBSTR(description,1,50) description FROM gl_je_sources_tl ORDER BY SUBSTR(user_je_source_name,1,30)
Category Code / p_user_je_cat_name	Varchar2(30)	N		Partial entry of the values with (%) is allowed and not validated against LOV	SELECT SUBSTR(user_je_category_name,1,30) user_je_category_name , SUBSTR(description,1,50) description FROM gl_je_categories_vl ORDER BY SUBSTR(user_je_category_name,1,30)
Posted Status/ p_status	Varchar2(1)	Y	Any Status	Validate Posted Status from following list of values Any Status Posted Unposted Error	List Item
Posted Date/ p_posted_date	Date (8)	N		The Date format entered is validated against LOV
Effective Date / p_effective_date	Date (8)	N		The Date format entered is validated against LOV
Batch Name / p_batch_name	Varchar2(50)	Y		Partial entry of the values with (%) is allowed and not validated against LOV	SELECT SUBSTR(name,1,30) name , SUBSTR(description,1,60) description FROM gl_je_batches ORDER BY SUBSTR(name,1,30)
JE Name / p_name	Varchar2(50)	Y		Partial entry of the values with (%) is allowed and not validated against LOV	SELECT DISTINCT SUBSTR(name,1,30) name , SUBSTR(description,1,50) description FROM gl_je_headers ORDER BY SUBSTR(name,1,30)
JE Creator User ID (RACF ID ) / p_user_name	Varchar2(30)	N		Validation against LOV	SELECT SUBSTR(user_name,1,30) user_name , SUBSTR(description,1,50) description FROM fnd_user ORDER BY SUBSTR(TRIM(user_name),1,30)
Part of creator’s Name / p_user	Varchar2(30)	N		Partial entry of the values with (%) is allowed and not validated against LOV
Sort By / p_sortorder	Varchar2(1)	Y	JE Line Number	Radio Group with 3 radio buttons having values ‘JE Line Number’ J ,‘Account and Department’A ,’Department and Account’ D	Radio Group

2. Generate fmx file and transfer the fmx to form directory under GL_top
3. Log on Sys Admin responsibility and Register the form and define the form as function.
4. Attach it to the Menu and under GL responsibility. Check you have the access for that particular responsibility.
5. Switch to GL responsibility and open the form.

1. Develop a report which accepts the following parameters. Parameter details

are given under prerequisites section. The report layout is shown below.

Journal Inquiry

Program: GLC0343 Page 1 of 1

Run Date: 01/31/06 04:02:00

Period Name: NOV-05

Source Code: Payables

Category Code:

Batch Name:

JE Name:

Posting Status: Any Status

Posted Date:

Effective Date:

Created By:

Sort By: Account and Department

Orig Base Base

Curr Period Curr Curr

Batch JE Name Source Category Code Name Status Debit Credit

15954 Payables 2210 Payments CAD Payables Payments CAD Nov-05 Posted 112413.43 112413.43

15954 Payables 2210 Payments EUR Payables Payments EUR Nov-05 Posted 264228.24 264228.24

16267 Payables 5332 Payments USD Payables Payments EUR Nov-05 Posted 536246.44 536246.44

35454 Payables 3521 Payments CAD Payables Payments CAD Nov-05 Posted 613413.23 613413.23

15954 Payables 4321 Payments JPY Payables Payments JPY Nov-05 Posted 764228.64 764228.64

16267 Payables 4212 Payments USD Payables Payments USD Nov-05 Posted 234246.84 234246.84

*******End of the Report*******

Batch

The Batch Name for the entered period is printed here. Left aligned

Data type Varchar2 (30)

JE Name

The JE Name for the entered period is printed here. Left aligned

Data type Varchar2 (25)

Source

The Source from where the Journals are created or imported is printed here.

Left aligned. Data type Varchar2 (15)

Category

The Category name of the Journals is printed here. Left aligned

Data type Varchar2 (15)

Orig Curr Code

The Orig Curr Code of Journals is printed here. Left aligned

Data type Varchar2 (15)

Period Name

The Period Name associated with the Journals is printed here. Left aligned

Data type Varchar2 (15)

Status

The Status of the Journals is printed here. Left aligned

Data type Varchar2 (8)

Base Curr Debit

The Debit amount of the Journals is printed here. Right aligned

Data type Number

Base Curr Credit

The Credit amount of the Journals is printed here. Right aligned

Data type Number

Use the tables listed below to the build the query

Table Name	Select	Insert	Update	Delete
gl_je_lines	X
gl_je_headers	X
gl_je_batches	X
fnd_user	X
gl_je_sources	X
gl_je_categories	X

3. Logon on to Sys Admin and Register Your Report within Oracle Applications
4. Define Concurrent Manager Submission Information

5. Add your Report to the Appropriate Request Groups.

9. Oracle Apps General Ledger Interview Questions (Functional & Technical

1) Can you disable budgetary control for a set of books?

Yes you can, however existing encumbrances are not cleared from the feeder systems. Therefore it is not recommended.

If you do change the budgetary control options for an existing set of books, you must do two things for the change to be reflected.

-- Run the Period Map Maintenance concurrent request, it must complete

successfully.

-- Exit Oracle Applications and restart. You must completely exit the

application. It is not sufficient to select Sign on Again from the Oracle

Applications Special menu.

2) Is there a limit to the number of periods in a budget year or how many years a budget can span?

Your budget can include up to 60 periods per year and can span an unlimited number of fiscal years.

3) I was able to post a budget journal to a closed period, why?

A budget journal can be posted to any period that is in an open budget year for that budget. This is regardless of the status of that period (closed, opened, or future enterable).

4) How many 'Current' budgets can you have?

You can only have one current budget. The only distinction between a 'current' and an 'open' budget is that the current budget defaults into the budget field on several budget related forms. It can be replaced however by any 'open' budget in the field.

5) What is a funding budget?

It is a budget that requires journal entries, and is assigned to a summary template or account range in the budget org, where the funds check level is set at Absolute or Advisory. It is the assignment that makes it a 'funding budget', it is not done at the budget definition level.

6) Can I delete a budget?

No. Budget data can be deleted using the archive and purge functionality, but the budget definition itself cannot be deleted.

7) How many times can a budget be purged?

A budget can only be purged one time. The archive/purge functionality was not designed to be a maintenace tool in the budgeting process.

8) What is MRC?

MRC is a feature that allows for transactions to be recorded in more than one set of books/functional currency by way of assigning reporting set of books to a primary set of books. The chart of accounts and calendar has to be the same for the primary and reporting set of books. ***Inventory does not support MRC.***

9) Can I use the same responsibility for both Primary and Reporting set of books?

No, two responsibilities have to be set up, one for the primary and reporting set of books respectively.

10) Does opening the period in the Primary set of books also open it in the reporting set of books?

No, periods have to be opened in both sets of books individually.

11) Can I use a different chart of accounts for my reporting set of books?

No, both primary and reporting sets of books must use the same chart of accounts and calendar. Only the functional currency can be different .

12) In what way does the profile option MO - Operating unit affect the MRC?

If a reporting set of books is assigned to a primary set of books then this profile option should be set to the same operating unit for both the Primary and Reporting set of books .

13) What are the year types 'Fiscal' and 'Calendar' used for?

The Year Type (Fiscal or Calendar), is used only to determine which two digits to append to the system generated period name. Regardless of which Year Type is used, the 'Year' entered on the Calendar form must be the same for all periods in your fiscal year - whether it is a calendar year or a fiscal year.

If Year Type = Calendar: The last 2 digits of the 'From' date for the period are used. If Year Type = Fiscal: The last 2 digits of the value in the 'Year' are used.

14) What are Transaction tables? What is its function?

Transaction tables are tables that store day-to-day transaction data,

such as payable invoices, receivable invoices, and journal entries.

15) What are OPEN INTERFACES

Oracle Applications provides open interfaces to help integration among

Oracle Applications modules, as well as with non-Oracle applications.

These open interfaces consist of interface tables which store data that

must be imported into Oracle Applications

16) What are benefits of OPEN INTERFACES ?

Easy to use

Standard inputs and outputs

Data validation

Error reporting

17) What are OPEN INTERFACES provided by Oracle General Ledger?

Journal Import, Budget Upload OPEN INTERFACES provided by Oracle

General Ledger

18) How many basic types of currency implementations?

There are two basic types of multiple-currency implementations:

1. Transactions and balances are maintained in a single currency (called the functional currency). Journals showing other currencies translate their values to the functional currency before posting.

2. Transactions and balances are maintained in multiple functional

19) What are Master tables ,Setup tables and Transaction tables?

Master tables store static data-DATA that changes less frequently than transactional data. Master tables include accounts, suppliers, customers, items, and assets.

Setup tables store setup data such as application parameters and lookup tables. They are shared within an Oracle Applications package but are seldom shared across Oracle Applications packages.

Transaction tables store data about day-to-day operations. Transactional data includes invoices, payments, receipts, and purchase orders

20) What is legal entity, operating unit, inventory organization?

Each legal entity has its own employer tax identification number.

Each operating unit has its own payables, receivables, cash management,

order entry, and purchasing transactions.

Each inventory organization has its own capacity, inventory, engineering,

master scheduling, bill of materials, work in process, and material

requirement planning data.

KPIT Cummins Infosystems Limited

34 & 35, Rajeev Gandhi Infotech Park,Hinjewadi, Pune 411 057, MS - India

Page 100 of 100