Create analysis using Subject Area ‘General Ledger- Balances Real Time’ In Oracle Fusion

1.      Run or schedule Process “Create Balances Cube” In ESS. This process create Essbase cube.

2.      Run or schedule Process “Create Rules XML File for BI Extender Automation”

3.      Run or schedule Process “Import Oracle Fusion Data Extensions for Transactional Business Intelligence”

4.      Mandatory parameters to use the subject area as follows:-

i)        Navigate to Ledger folder and pull Ledger Name, Chart of Accounts

ii)      Navigate to Time folder and pull Name, Accounting Period Name column.

iii)     Navigate To Currency folder and pull Currency, Currency Type column

iv)     Navigate to Amount Type and pull Amount Type column

v)      Navigate to Scenario folder and pull Scenario column.

vi)     Navigate to Balance folder and pull Beginning Balance and Ending Balance

vii)   Navigate to Cost Center Segment folder and pull Cost Center Code

viii)  Navigate to Natural Account Segment folder and pull Account Code

ix)     Navigate to Balancing Segment folder and pull Balancing Segment Code

x)      Filter on Chart of Accounts

xi)     Filter on Fiscal Calendar Name

xii)   Filter on Accounting Period name for a period which has balances.

xiii)  Run the report.

GL Cross Validation Rules in Oracle Fusion Cloud

Business Requirement : 

If Busines has Cost Center Segment values with Common for Balance sheet and this should not use for P&L Account 

Then business may require COA Restriction required for entering Cost Center as 000- Common and GL Project 0000-Common for all Expense and Revenue GL Account.

Go to System and Maintenance >  Manage Cross-Validation Rule

s

Create Rule :for Example

• Code: CC01_BS
• Description  Common(000)GL Costcenter Restriction for all Expense and Revenue Account
• Effective Date from
• Effective Date to
• Enable : yes
• Filter Conditions : Select where condition GL Account or any Segment on which base system should trigger cross validation rule.
• Ex: Filter Condition Details: account Greater than or equals to 30000000 ( Make sure if this is restricted Account for which Common cost center should not allow)

Condition Filter 1 Filter Conditions Defined Condition Details account Greater than or equals to 30000000 Error Message Name GL_CVR_1005121_300000922647489

Then enter Validation for which system to restrict or allow the Code for any segment:

For this case we need to excluse Common Cost center should not allow for Above GL Account greather than or equal to 30000000 

*Validation Filter	1 Filter Conditions Defined
Validation Details	costCenter Not equals to 000

Finally Check in journal page

Financial Reporting in Oracle Fusion Cloud ERP

Financial Reporting in Oracle Cloud ERP

In this blog I will discuss how financial reports can be created directly from Oracle Cloud ERP. Financial Reports in Oracle Cloud ERP are built off of the Financial Reporting Studio using Metadata and Data from the Oracle Cloud ERP General Ledger Balance Cubes. GL Balances cube stores financial balances in a multidimensional database for interactive reporting and analysis. When the accounting configuration process submitted for a primary or secondary ledger that uses a unique combination of chart of accounts and calendar, a balances cube is created. A ledger determines the currency, chart of accounts, accounting calendar and accounting method. Balances cubes are named after the chart of accounts.

Balances cube stores financial balances in a multidimensional cube for real time, interactive financial reporting, and analysis. Pre-Aggregates balances at every possible point of summarization, ensuring immediate access to financial data and eliminating the need for an external data warehouse for financial reporting. Average balances are tracked in a separate balances cube. Most of the configuration steps while setting up the General Ledger application will cover the creation and population of the GL Balance Cubes.

“Create Cubes” child process which is part of “Create Accounting Configuration” Process creates the accounting configuration and a balances cube when setup is completed. Both Standard and Average Daily Balances cubes if enabled are processed in the same request.

“Publish Chart of Account Dimension Members and Hierarchies to Balances Cubes” process creates the structural hierarchy for the chart of accounts. Both Standard and Average Daily Balances if enabled are processed in the same request.

“Create Ledger Set Dimension Members” child process part of “Create Ledger Set” parent process creates, and updates ledger set members in a balances cube.

Balances cubes are automatically synchronized by the following general ledger processes: open period, posting and translation.

Opening a period lets Journal Entry and Posting allowed.

Posting updates account balances by the recorded debit or credit amount in each journal entry to the related accounts in general ledger.

Translate General Ledger Account Balances process transfers and converts balances from the specified ledger to the reporting currency.

Cubes consists of a set of defining business entities called dimensions. Dimensions in a cube determine how data is accumulated for reporting and analytical purposes. Balances cubes are referred to as an application or database connection in the user interfaces for Financial Reports, Smart View, and Calculation Manager.

There are two types of Oracle Fusion General Ledger Balances cubes: Standard Balances cubes and Average Balances cubes. Balances cubes are named after the chart of accounts they contain.

Standard Balances Cubes

A new standard balances cube is created whenever an accounting configuration is submitted for a ledger, either primary or secondary, that uses a new unique combination of chart of accounts and accounting calendar.

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

Dimensions

• Accounting Period
• Ledger or Ledger Set
• Chart of Accounts Segments (Depending on the Chart of Accounts new dimension for each segment will be created. Example Company, Cost Center, Department & GL Account are created as separate dimensions)
  • Company
  • Cost Center
  • Department
  • GL Account
• Scenario
• Balance Amount
• Amount Type
• Currency
• Currency Type

Average Balances Cubes

Average balances cubes use different dimensions than the standard balances cubes therefore require their results be stored in separate cubes. If the average balances option is enabled for a ledger, a second average balances cube is automatically created based upon the same criteria of a unique combination of chart of accounts and accounting calendar. Average balances cubes are named with ADB (average daily balances) plus the name of the associated chart of accounts.

Dimensions

• Accounting Period
• Ledger or Ledger Set
• Chart of Accounts Segments(Depending on the Chart of Accounts new dimension for each segment will be created. Example Company, Cost Center, Department & GL Account are created as separate dimensions)
  • Company
  • Cost Center
  • Department
  • GL Account
• Scenario
• Amount Type
• Currency
• Currency Type

Financial Reporting Center

Financial Reporting functionality in Oracle Cloud ERP is provided under Financial Reporting Center. Financial Reporting Center is a powerful tool for reviewing, designing, and presenting financial reports and analytic data.

Financial Reports are built off of the Oracle Financial Reporting Studio using data in the General Ledger Balances Cube like Income Statements, Balance Sheets etc.

Configure Workspace Database Connection

Administrators will be able to create database connections from workspace once the General Ledger Balances Cubes are created.

To define a database Connection, Navigate to Financial Reporting Center. From the Financial Reporting Center task panel, Select Open Workspace for Financial Reporting. Select Tools menu and choose Database Connection Manager.

Select New Button.

Enter a user-friendly name for the Database Connection Name.

Enter Essbase as the Type, server name, username, password, and choose the Application (Cube) , Database from the list of values. Expand the Application Name to see the related Database. Click OK to save the selection and close button in the Database Connection Manager window to save the connection.

This database connection will be available in both Workspace and Financial Reporting Studio.

Hyperion Financial Reporting Studio/Web Studio enables to use an object-oriented graphical report layout with report objects such as text boxes, grids, images, and charts.

We will not be discussing how to build the report using the Financial Reporting Studio here. Once the report is developed and saved it will be available under the workspace either under My Folders or under Shared Folders depending the location where the report is saved. End users would be able to run and generated the output in HTML or PDF and is tablet and smartphone friendly.

Financial Reports can be generated by selecting Open or double clicking the Report a pop up will be displayed to choose the parameter values.

Once the parameter values are selected and ok is selected report output will be displayed as below.

Report output can be exported to Excel Smartview in Query Ready format.