Order to Cash Data Flow

Oracle General Ledger Interview Questions

1. What are the key functions provided by GL?

General Accounting                              Budgeting

Multiple Currencies                              Inter-company Accounting

Cost Accounting                                             Consolidation

Financial Reporting                              

2. What are the three types tables available in Oracle Applictions?

            Master Table: Store Static data,. This is shared with in each module as well as across all

of the Oracle financial application modules ex. ACCOUNTS, SUPPLIER, CUSTOMER etc.

Setup Table: it store setup data. That is never shared between applications such

as application parameters and LOOK UP tables.

Transaction Tables: these tables stores day-to-day transaction data such as payables invoices,

journal entries etc.

3. What are the Oracle General Ledger Setups?

1.    Chart of Accounts

2.    Account combinations (o)

3.    Period types

4.    Accounting Calendar

5.    Transaction Calendar (o)

6.    Currencies

7.    Set of Books

8.    Assign set of books

9.    Currency exchange rate types

10.  Currency exchange rates

11.  Journal Sources (o)

12.  Journal Categories (o)

13.  Suspense Accounts (o)

14.  Inter-Company Accounts (o)

15.  Summary Accounts

16.  Statistical Units measures (o)

17.  Historical Currency Exchange Rates

18.  Document sequences (o)

19.  Automatic Posting (o)

20.  Encumbrance Types (o)

21.  Concurrent program Controls (o)

22.  Storage Parameters (o)

23.  Budgetary Control Groups

24.  Profile options

            25. Descriptive Flexfileds

           26. Open and Close Accting periods

4. Chart Of accounts: A Chart of Accounts is the account structure we use to record accounting transactions and maintain accounting balances. It is a key flex field.

5. Flex Fied: A Flex Field is a combination of one or more data segments defined by the user. For Chart of accounts, we configure up to 30 segments in a flexfield and min is 2.

    Descriptive Flexfield: We can store the additional Information in customized form or  existing form.

6. Value Set: value set defines the valid values for each segment of our Chart of Accounts. There are 6 validations types 1. Dependent 2. Independent 3.none 4. Pair 5 special 6. Table 

INDEPENDENT: An independent value set provides a pre-defined list of values for a segment.

DEPENDENT: A dependent value set is similer to independent value set, or dependent value set depends on any independent or dependent value set.

TABLE: Table provides list of values like an independent set. But values will stored in application table.

NONE: No restrictions.

7.Transactions: Exchange of goods and services with the intension of earning

8.What is a Qualifier?

            Qualifier is a behavior of a segment.

9. Flex field Qualifiers

            Companies       ---------- Balancing segment    

            Departments   ----------Cost Centers Segment

            Accounts          ----------Natural Accounts Segment.

10. What are the format types available in Values sets?

            Character, date, date time, number, std date, std date time,. Time.

11. What are Accounting Qualifiers?

            Allow budgeting

            Allow positing

            Account type

            Control Account

            Reconsiliation flag

12. Security Rules:

            These are used to limit access to certain segment values for a particular segment.

13. Cross Validation Rules:

            These rules validate data across segments of a flex field.

14. Types of Calender:

            Normal: January to Dec.

            Fiscal: April to march.

15. Period Types:

            Daily, Month, Quarter, Year.

16. Accounting Calender:

            It is used to define the no. Of periods in the calendar year. Our calendar can contain both adjusting and non-adjusting accounting periods.

17. Transaction Calender:

            It is used to define the business days of an organanization.

18. Types of Currency:

            Functional currency: The currency we define in our SOB.

            Foreign currency

19. S.O.B:

            Put together information is called as Sets Of Books. It consists of Cart of Accounts, Currency, Calender and Six mandatory Accounts.

20. What are Mandatory Accounts:

Mandatory Account	Usage	Acct Type
Retained Earnings	Last Year closing balances C/F (carry forward) to current year balance	Ownership
Translation Adj Acct	When the currency conversion takes place the difference amt is stored in this acct	Asset/liability
Suspense Account	The variation of credit and debit amounts is suspense acct	Asset/liability
Rounding Difference Acct	The difference amt after rounding the amount is posted to this account.	Expense/Revenue
Net Income Acct	Surplus of profit and loss account.	Ownership No budget and No posting
Reserve for Encumbrance	Planning for reserving some amount(budget)	ownership

           

21. States of Periods.

            Open, Close, Future Entry, Permanently closed.

22. What is Journal?

            A journal is a form in which we enter the business transactions.

23. What are the Balance Types?

            Acutal, Budget, Encumbrance.

24.  Categories of Journals.

Ø  Batch Journal.

Ø  Source Journal

Ø  Statistical Journal

Ø  Suspense Journal

Ø  Encumbrance Journal

Ø  Reverse Journal

Ø  Tax Journal

Ø  Recurring Journal

§  Standard Recurring Journal.

§  Formula Recurring Journal.

§  Skelton Recurring Journal.

25.                 Batch Journal:

A group of common journals is called as a Batch

26. Source Journal:

            A source journal is a journal where we can get the journal information from other modules.

27. Statistical Journal:

            Statistical journal entries do not require balanced debit and credits. Here we use ratios to calculate amounts.

28. Suspense Journal:

            When the debit amount and credit amount are not equal in the journal entry then the deficit amount is added to suspense account and such types of journals are called as suspense journals.

29 Encumbrance Journal:

            For funding budgets we have to enter the encumberance journals. Using this amount we can perform the actual expenses. The part of the budget we reserve is called as reserve for encumberance. The journals involving this budget are called as encumbrance journals.

30. Reverse Journal:

            We cannot alter the posted journals. We can only post additional journals which contain reverse to that of credit and debit amounts of the original journal. This types of method is called as reverse journal.

31. Tax Journal:

            The tax journals will calculate the tax on the credit and debit amounts in the journal depending on the tax information.

32. Inter-company Journal:

            If multiple companies in our enterprise share the same SOB then we can automatically balance inter-company journals. Here we define intercompnay accounts for different combinations of sources, category and balancing segment value.

32. Recurring Journals:

            Journals which will be repeated automatically are called as Recurring journals.

The advantage of recurring journal is one journal can be posted in each and every month without creating each and every time.

            There are three types of recurring journals.

Standard R.J: Where we know the fixed amount and account information.

Formula R.J: Here we may or may not know the actual information. We calculate the amounts depending on a formula. In formula block first field should be “enter”

Skelton Journal: It is raised when we know only account information but not amount information.

Control Total:

This is used to raise the journal with a fixed amount. The error message is not displayed at the saving time but it is displayed while posting the journal.

33. Mass Allocation (Allocation journal):

            Mass allocation is used to avoid the repeating entry of journals for different departments and it considers only actual accounts.

                                    (Or)

            When we are trying to allocate an amount for a period for a combination of segment values is called as Mass Allocation.

            Mass allocation formula:

           

                        Cost pool * usage factor/total usage factor

Summary Accounts:

            Summary accounts store balances of multiple accounts. We need summary template to define a summary account.

Roll up groups:

            A roll up group is a collection of parent values for a given segment. This is used to provide a condition to the template.

Amount types:

            PTD (period-to-date)                            YTD (year-to-date)

            PJTD (project-to-date)              QTD (Quarter-to-Date) 

Financial Statement Generator A powerful and flexible report building tool for Oracle General Ledger. You can design and generate fiancial reports, apply security rules to control access to data via reports, and use specific features to improve reporting productivity.

Budget

            It is one of the management tool by using which we can estimate the amounts for a specific range of periods for an organization.

            Each budget can have maximum of 60 periods.

            Budget can have any one of the following states

            Current (the first budget we define in our sob)

            Open (To activate a budget)

            Frozen (to deactivate or close a budget)

 For using budgets we have to define a budget and a budget organization.

Budget Types:

Planning Budget:

We can just plan but we cannot raise journal entries.

We can convert it into funding budget by enabling Required Budget Journal option.

Fund check levels are:  none, advisory, absolute.

Funding Budget:

            This is the actual budget. Once the budget is approved, the organization can start spending the budget amount for various expenses.

Budget Journals:

            It is a combination of budget organization and budget. These offer an alternative way to enter budget amounts, and they assist in maintaining audit trials.

Budget Formulas

                        We can also enter budget amounts by using budget formulas. Budget formulas similar to recurring formulas for actual amounts. To use budget formulas we must first define the budget formula and generate it

            ALLOCATED AMOUNT=COSTPOOL * USAGE FACTOR/TOTAL USAGE

Cost pool: the total budget amount that has to be allocated to the child values in organizations      

Usage Factor: to allocate the budget amount to the child values the ratio by which you are going to distribute the cost pool amount.

TOTAL USAGE: the total ratio of usage factor would be the total usage.

Currency Translations:

Types of Rates:

Period rates, Liability rate, Historical Rate.

Foreign to functional:

Revaluation:  Within the company.

Consolidation:  Multiple companies

Functional to Foreign:

Translation: within the company

Mrc: Multiple companies.

Translation amd MRC will not affect the actual balances but revaluation and consolidation affects the actual balances.           

Translation:

1.    This is only for reporting purpose. It does not effect the actual balances

2.    It is used for converting functional currency to foreign within the single company.

3.    This is used for subsidiary corporations and we cannot perform for the first period of a calendar.

Revaluation:

1.    This effects on the actual balances.

2.    Before and after periods should be open.

Consolidation:

1.    This is used when the chart of accts differs between each other.

2.    This will effect the actual balances.

3.    The chart of accts and currency may be same or different but the period(calendar) must be same.

4.    Two typs of consolidation: Global Consolidation, Normal consolidation.

MRC

1.    This is used for converting functional transactions to foreign currency for reporting purpose.

2.    The CoA and calendar can be same but the currencies should be different.

3.    This allows us to maintain accounting transactions in more than one functional currency.

           

Multi-Org:

            Single installation of multiple operating units is called Multi-Org.

Flow of Multi-Org:

            Business Group.

            Set of Books.

            Location.

            Hr Organization.

            Legal Entity

            Operating Unit.

            Inventory.

Financial Statement Generator

Do you know many financial reports have been written using Oracle's Financial Statement Generator in Oracle EBS since very begining. This is one of robust tool that allows us to create reports which meet user defined criteria. Lets take quick overview of FSG and its usage.
What Are Oracle FSGs?
FSG is short form of Financial Statement Generator.
In brief , is a powerful reporting engine that supports interchangeable report objects, server-based processing for high performance, and report scheduling for efficient use of system resources.
A user defined report that allows for the rollup of GL Account Balances (i.e. combinations of accounting segments –GL Code Combinations) into logical groupings for reporting
The FSG allows you to?

• This is End user tool , which enables end business user for financial reporting without any programming
• Most of these reports are oriented toward financial statements
• These are very very easy to use :-)
• Transfer accounting information to a popular spreadsheet if you prefer to use your personal computer for analysis and planning.
• Report on both translated and entered foreign currency amounts.
• Access a wide variety of standard management and accounting reports for the detail information you need, including chart of account listings, journals, general ledgers and trial balances.
• Define the complex financial statements you need to analyse your business,including responsibility reports for business units, profit centres and cost centers.
• The good things is that all these reports run through concurrent manager
• Most important there is no additional setup required
• We can design our reports to meet your business needs
• Design to print directly to MS Excel
• Schedule reports to run directly from the application

What you can't do with FSG?

• Limited flexibility in formatting
• Access GL balances only - no details
• Minimal security

What are the reports which is based out of FSG Reports

• Financials-P&L, Bal Sht, Cash Flow, Rev/Exp detail, cost center detail
• Auditor Schedules - trial balance mapped to financial stmts
• Income tax workpapers
• Partial trial balances
• Income Statement

What are the FSG Component or Structure

• Row Sets
• Column Sets
• Content Sets
• Row Orders
• Display Sets
• Reports
• Report Sets

Row Set

• Description to appear on left hand side
• Define accounting flexfield ranges or summary accounts here
• Define row calculation - total, etc.

What are the Column Set

1. Column Sets typically define the format and content of the columns in an FSG report
2. Oracle provides seeded column sets, or you can build your own

Column Set Attributes-The commonly assumed attributes for a column set are:
-Time period (PTD, QTD, YTD, etc)
-Account type (Actual, Budget or Encumbrance)
-These two elements are defined by an Amount Type
What is Column Set: Proforma
Proforma is an accounting term that refers to an annual fiscal amount that is comprised of actual amounts and budget amounts together.

What is a Content Set?

• Content Sets are used to override row/column account assignments and display types to create multiple variations of existing reports
• You can generate many similar reports in a single run by using content sets
• Use to “expand” a range to give multiple values on one report OR separate reports for each value in the range
• Override applied separately to each segment in flexfield

What is a Row Order?

• Row Orders are used to control how detail rows are displayed and/or sorted on a report
• Used with Row Set expand and Content Sets
• Allow user to expand detail of a row
• Can display
  • Segment value
  • Segment description
  • Both
• List alphabetically or numerically

Report Display Group

• Grouping of rows or columns i.e. “hide column 20”
• Specify a sequence number range from your row set or column set i.e. from 20 to 20
• Used in your display set

Report Display Set

• Assign Display Group(s) to your Set i.e. “display column 1, 3, 4”
• Can assign multiple row and/or column groups to one set i.e. “hide column 20”
• For each assignment, can specify whether to display the values on the report (Display = Yes) or (Display = No)

The Rule of Thumb for FSG
One FSG = One row set + one column set + one content set
Underline technology of FSG?? any guess , C, SQL, Java...
Important to know FSG reports are created from a program written in 'C' code, not from Oracle Reports, as is the case with standard reports. Many modules and tables are used to create reports based on the definition and component of a particular FSG.
What is FSG (Financial Statement Generator) Controller Program?
The FSG (Financial Statement Generator) Controller runs when you generate an FSG with a content set 'Type' set to Parallel. The FSG Controller kicks off the sub requests for the FSG's that are created with the content set.
What is in Background–The FSG Query
As these report are developed in C, it's impossible to break the code, but if you do some trace on you will find , the code is based out of GL balances table, addition to the some other tables which keeps the structure on the front end.
here is the hidden query:

SELECT
nvl(bal.PERIOD_TYPE, ''),
nvl(bal.PERIOD_YEAR, -1),
bal.PERIOD_NAME,
nvl(bal.PERIOD_NUM, -1),
nvl(bal.PERIOD_NUM, -1),
bal.ACTUAL_FLAG,
decode(cc.TEMPLATE_ID, NULL, 'N', 'Y'),
nvl(bal.BUDGET_VERSION_ID, -1),
nvl(bal.ENCUMBRANCE_TYPE_ID, -1),
bal.CURRENCY_CODE,
bal.SET_OF_BOOKS_ID,
nvl(bal.TRANSLATED_FLAG, ''),
nvl(bal.PERIOD_NET_DR, 0) -nvl(bal.PERIOD_NET_CR, 0),
nvl(bal.PERIOD_NET_DR, 0),
nvl(bal.PERIOD_NET_CR, 0),
nvl(bal.QUARTER_TO_DATE_DR,0)-nvl(bal.QUARTER_TO_DATE_CR, 0),
nvl(bal.QUARTER_TO_DATE_DR, 0),
nvl(bal.QUARTER_TO_DATE_CR, 0),
nvl(bal.BEGIN_BALANCE_DR, 0) -nvl(bal.BEGIN_BALANCE_CR, 0),
nvl(bal.BEGIN_BALANCE_DR, 0),
nvl(bal.BEGIN_BALANCE_CR, 0),
nvl(bal.PROJECT_TO_DATE_DR, 0) -nvl(bal.PROJECT_TO_DATE_CR, 0),
nvl(bal.PROJECT_TO_DATE_DR, 0),
nvl(bal.PROJECT_TO_DATE_CR, 0) ,
nvl(SEGMENT1,''),
nvl(SEGMENT2,''),
nvl(SEGMENT3,''),
nvl(SEGMENT4,''),
nvl(SEGMENT5,''),nvl(SEGMENT6,''),nvl(SEGMENT7,'')
FROM GL_BALANCES bal,
GL_CODE_COMBINATIONS cc
WHERE bal.CODE_COMBINATION_ID= cc.CODE_COMBINATION_ID
AND cc.CHART_OF_ACCOUNTS_ID= 118
AND bal.SET_OF_BOOKS_ID= 1
AND nvl(bal.TRANSLATED_FLAG, 'x')in('Y','N','x')
AND cc.TEMPLATE_IDis NULL
AND ( (nvl(SEGMENT3,'') >= '4001'
AND nvl(SEGMENT3,'') <= '5999')OR (nvl(SEGMENT3,'') >= '6020' AND nvl(SEGMENT3,'') <= '6370') OR (nvl(SEGMENT3,'') >= '6390'
AND nvl(SEGMENT3,'') <= '7250')OR (nvl(SEGMENT3,'') >= '7510' AND nvl(SEGMENT3,'') <= '9100') OR (nvl(SEGMENT3, '') in ('9310','9320')))AND ( (nvl(SEGMENT2,'') >= '000'
AND nvl(SEGMENT2,'') <= '01'))
AND((bal.period_name in ('Jun-07','Jun-07')
AND ((nvl(bal.period_name,'') = 'Jun-07'
AND((bal.ACTUAL_FLAG= 'B'
AND bal.BUDGET_VERSION_ID= 1111
AND(bal.PERIOD_NET_DR!= 0 or bal.PERIOD_NET_CR!= 0))OR((bal.ACTUAL_FLAG= 'B'
AND bal.BUDGET_VERSION_ID= 1111)OR((bal.ACTUAL_FLAG= 'A')) ))) OR(nvl(bal.period_name,'') = 'Jun-07'
AND((bal.ACTUAL_FLAG= 'A')
OR
( (bal.ACTUAL_FLAG = 'A'
AND (bal.PERIOD_NET_DR != 0 or
bal.PERIOD_NET_CR != 0))))))
) ) < /P > < /P >
The data is loaded into memory, and then sorted according to report format

How do you define a FSG Report?

• Reports are created by specifying a combination of report components (ie Row Set, Column Set, etc). Typically, values specified when defining a report override values specified at the individual component level
• The value entered in the Title field will appear at the top of the report

Where to run FSG Reports:
In Oracle EBS Running FSG Reports can be achieved from 3 different ways:

• Run Financial Reports form
• Concurrent Manager
  • The name of the request is "Program -Run Financial Statement Generator"
• ADI’s Request Center

Few more to know
Online Report RequestsYou can request FSG reports on-line using only a few keystrokes. For example, you can choose from a list of report sets or predefined reports that Oracle General Ledger displays in a pop-up window. Or, you can create an ad hoc report by selecting from a list of predefined report components.
Exception Reporting
The Financial Statement Generator Exception Reporting feature allows you to highlight information requiring special attention. You can define the exception criteria and how you want to flag the rows in your report that you want noticed. This makes it easy to choose to display only the exception rows in your reports.
Responsibility Reporting
It is possible to construct one report to serve the summary reporting needs of multiple managers. With Responsibility Reporting you can ensure that each level of management spends time reviewing only the necessary level of detail.

Oracle EBS Report Manager

What is Report Manager?
In Oracle EBS "report manager" is a product that has been offered to manage your reporting needs.
Report Manager is basically a secure point-in-time report repository offering centralized management and distribution of reports.
There are different levels of access that you can assign to reports that are generated by the Report Manager.
You can tie these access rights to your department assignments, to employees, or can build your own.
The report manager reports are stored in the database and can be published to a wider audience. The good is that you can manage access/availability to multiple groups of people. This is run as a menu option from the main menu of a specific application (AP, AR, etc.).
Report Manager benefits

• You can access business information from a central site using a standard browser
• You can provide that reports are with proper access
• You can store and distribute ANY type of report or file
• Preview reports prior to mass distribution

R12 Report Manager
The Report Manager seeded functions need to be assigned manually by the System administrators to appropriate responsibilities based on the types of reports a user submits and publishes in their job. For example, a system administrator might assign the Financial Report Template Editor and Financial Report Submission functions to an existing General Ledger responsibility already used for submitting Financial Reports by General Ledger users.
The seeded Report Manager functions are:

• Standard Report Submission
• Security Workbench
• Repository Management
• Financial Report Submission
• Upload File
• Variable Report Submission
• Financial Report Template Editor
• Ad Hoc FSG Submission
• Define Report Set Publishing Options
• Report Set Presentation Options
• Define Request Set Publishing Options
• Request Set Presentation Options

Release 12, ADI & Report Manager
In Applications Release 12, WebADI and Report Manager must be used instead of ADI, as client/server ADI is not supported for that release. Also in Release 12 the WebADI functionality for GL is integrated into the standard GL menu structures. These functions are 'Launch Journal Wizard', 'Launch Budget Wizard', and the spreadsheet upload of Currency Rates, via 'Currency Rates Manager'.
When To Use Report Manager(Implementation consideration)
This is very important to understand when offering to customer for this product. Oracle Report Manager you need to:

• Provide a centralized report distribution system for point-in-time reports.
• Submit and publish Financial Statement Generator (FSG), Ad Hoc FSG, Variable Format, and Standard reports to a central repository.
• Present reports in the repository to information consumers via menu items on the Oracle E-Business Suite Home page or portal.
• Approve reports before making them available for general viewing.
• Permit report availability in a future time frame.
• View spreadsheets or PDF files for further analysis.
• Secure reports by utilizing three security paradigms: User to Value, custom, and flex field segment.
• Archive reports for future reference that no longer need to be displayed to report consumers.
• Publish reports that have been submitted as part of request sets or report sets.
• Upload any type of file and publish it to a central repository.
• Reports in the repository can then distributed to the Oracle E-Business Suite home page.