Oracle Apps Reporting Tools

Introduction : Oracle provides over a thousand standard reports within the application. These standard reports are developed to cover common generic needs. Before creating any new reports, one should examine the standard report sets to determine if any meet the requirements. If requirements cannot be met with the standard Oracle report sets, tools are available to create custom reports.

The following matrix addresses reporting tool options, including:

A brief description
Who should be provided the access to the tool?
Advantages
Disadvantages
When is it appropriate to use?

(Click anywhere on the below image to ZOOM and read the complete table):

SOX, SOD and Oracle Apps

Why SOX Compliance is critical - Top Ten IT Control Deficiencies ( Source: Ken Vander Wal, Partner, National Quality Leader, E&YISACA Sarbanes Conference , 4/6/04):

1.Unidentified or unresolved segregation of duties
2.Operating System access controls supporting financial applications or Portal not secure
3.Database access controls supporting financial applications not secure
4.Development staff can run business transactions in production
5.Large number of users with access to “super user” transactions
6.Former employees or consultants continue to have system access
7.Posting periods not restricted within GL application
8.Custom programs, tables and interfaces are not secured
9.Procedures for manual processes do not exist or are not followed
10.System documentation does not match actual process


Segregation of Duties (SOD) Definition:

Segregation of duties (SOD) provides the assurance that no one individual has the physical and system access to control all phases of a business process or transaction: from authorization to custody to record keeping. A person or group has too much access or authority – resulting in risk exposure to the business.


SOD Examples:

Oracle AIM Document Templates

1. Business Process Architecture (BP)
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

2. Business Requirements Definition (RD)
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

3. Business Requirements Mapping
BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

4. Application and Technical Architecture (TA)
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

5. Module Design and Build (MD)
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

6. Data Conversion (CV)
CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

7. Documentation (DO)
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

8. Business System Testing (TE)
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

9. PERFORMACE TESTING(PT)
PT.010 - Define Performance Testing Strategy
PT.020 - Identify Performance Test Scenarios
PT.030 - Identify Performance Test Transaction
PT.040 - Create Performance Test Scripts
PT.050 - Design Performance Test Transaction Programs
PT.060 - Design Performance Test Data
PT.070 - Design Test Database Load Programs
PT.080 - Create Performance Test TransactionPrograms
PT.090 - Create Test Database Load Programs
PT.100 - Construct Performance Test Database
PT.110 - Prepare Performance Test Environment
PT.120 - Execute Performance Test

10. Adoption and Learning (AP)
AP.010 - Define Executive Project Strategy
AP.020 - Conduct Initial Project Team Orientation
AP.030 - Develop Project Team Learning Plan
AP.040 - Prepare Project Team Learning Environment
AP.050 - Conduct Project Team Learning Events
AP.060 - Develop Business Unit Managers’Readiness Plan
AP.070 - Develop Project Readiness Roadmap
AP.080 - Develop and Execute CommunicationCampaign
AP.090 - Develop Managers’ Readiness Plan
AP.100 - Identify Business Process Impact onOrganization
AP.110 - Align Human Performance SupportSystems
AP.120 - Align Information Technology Groups
AP.130 - Conduct User Learning Needs Analysis
AP.140 - Develop User Learning Plan
AP.150 - Develop User Learningware
AP.160 - Prepare User Learning Environment
AP.170 - Conduct User Learning Events
AP.180 - Conduct Effectiveness Assessment

11. Production Migration (PM)
PM.010 - Define Transition Strategy
PM.020 - Design Production Support Infrastructure
PM.030 - Develop Transition and Contingency Plan
PM.040 - Prepare Production Environment
PM.050 - Set Up Applications
PM.060 - Implement Production Support Infrastructure
PM.070 - Verify Production Readiness
PM.080 - Begin Production
PM.090 - Measure System Performance
PM.100 - Maintain System
PM.110 - Refine Production System
PM.120 - Decommission Former Systems
PM.130 - Propose Future Business Direction
PM.140 - Propose Future Technical Direction

Currency Exchange Rate Interface

Client Industry - Applicable to clients implementing Oracle General Ledger

Business Case - Almost all clients who implement Oracle General Ledger make use of the currency exchange rate interface, since it provides a convenient and automated way for multicurrency processing within Oracle Apps. Many clients make use of this interface to report financials in a common currency as well as to perform inter-company transactions between companies that have 2 different functional currencies.

Solution – A simple custom interface program can be written which will import rates automatically from a vendor and will also load these rates into the core GL daily rates table using the delivered daily rates interface tables. Find below a high-level process flowchart which can be used as a starting point to design this interface:

1. Request file is sent to Vendor (Reuters, Bloomberg, Oanda).
2. The encrypted file “XXGLDAILYRATES_XXXX.txt” from the vendor is obtained where XXXX is “mmdd”.
3. If the file is available, continue step 4 thro 11. Else, go to step 12.
4. Invoke the Shell script to decrypt the file and load the data from the file into the daily rates interface table .
5. Call the concurrent program “Program - Daily Rates Import and Calculation”
6. Load the daily rates from interface table into Oracle base tables till the end of next fiscal month.
7. Calculate the Period average rates.
8. Load the period rates into the interface table (gl_daily_rates_interface) and Call the concurrent program “Program - Daily Rates Import and Calculation” to load the period rates from interface table into Oracle base tables.
9. Calculate the Period average and end rates for NON - US currencies.
10. Load the period average and end rates into Oracle base tables (gl_translation_rates)
11. Archive the decrypted data file as “XXGLDAILYRATES_XXXX.txt” in ARCHIVE/inbound folder where XXXX is “mmdd”.
12. Page the Finance on-call support.

Month End Close Dashboard

Client Industry - Applicable to all using Oracle Apps

Month End Close Dashboard - One of my client's accouting department wanted to know the close status of GL and other Sub-Ledgers in a single screen in Oracle. Currently Oracle does not have the funtionality to display month end close status collectively for all installed applications in a single screen. Hence a custom form was developed to acheive this functionality.

Business Case - Client needed a form for viewing the close status of P&L and Balance Sheet accounts for each Company in Oracle General Ledger in a Dashboard. The Dashboard will reflect the close status of Oracle Sub-ledgers as well. A Custom Closing Process Dashboard will need to be developed which will be used by the Accounting department for monitoring the close process in Oracle Financials and initiate requisite action based on certain key reports.

Solution Overview - We developed a custom Closing Process Dashboard which will be utilized by Accounting users to monitor the following during close process in the Various Primary Set of Books:
GL Status
View legal entity-wise P&L and Balance Sheet close status for the period selected.

· P&L Accounts
o User to view the P&L close schedule for each legal entity based on the local site closing time
o User to view the P&L close Status for each legal entity in comparison to the close schedule based on the local site’s closing time
o The three Statuses for P&L accounts are as below :
§ Open: P&L accounts fall within the closing schedule
§ Open and Action Required: Journal entries with P&L accounts do not fall within the closing schedule and there are suspense account balances & / or unapproved journals for P&L accounts
§ Close: Journal entries with P&L accounts do not fall within the closing schedule and there are neither suspense account balances nor unapproved journals for P&L accounts
o Display the reason for non-closure (Suspense Accounts and / or Unapproved Journals) of P&L accounts

· Balance Sheet Accounts
o User to view the Balance Sheet close schedule for each legal entity selected based on the local site closing time.
o User to view the Balance Sheet close Status for each legal entity as compared to the close schedule
o The three Statuses for Balance Sheet accounts are as below:
§ Open: Balance Sheet accounts fall within the closing schedule
§ Open and Action Required: Journal entries with Balance Sheet accounts do not fall within the closing schedule and there are suspense account balances & / or unapproved journals for Balance Sheet accounts
§ Close: Journal entries with Balance Sheet accounts do not fall within the closing schedule and there are neither suspense account balances nor unapproved journals for Balance Sheet accounts
o Display the reason for non-closure (Suspense Accounts and / or Unapproved Journals) of Balance Sheet accounts

Sub-Ledger Status
· Legal Entity Level
o User to view the GL period Status for each legal entity and Set of Book and also the close status of all Sub-ledgers for the period selected
· Set of Book Level
o User to view at a Set of Book level the close status of each Oracle Sub-Ledger for the selected period

Suspense Account Balances
· View suspense account balances by legal entity for functional and transactional currency showing MTD, QTD and YTD for both Standard and Average balances
· Suspense account balance reports to be generated everyday and sent to Site & Corporate Approver based on legal entity

Unapproved Journals
· Report to View Unapproved Journals at a Journal Batch level
· Unapproved journal entries report to be generated everyday and sent to Site & Corporate Approver based on legal entity

Audit Reports
· User to view the GL Status details in the form of a report for record / audit purposes