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

Daily Close and End of Day Reporting

Client Industry - Banking and Financial Institution

Summary - One of my banking clients had a business need to enhance end of day reporting by using a new "processing date" field, by preventing posting of future dated transactions, and storing daily balances to support end of day reporting requirements.

Standard Functionality Limitations - since Oracle General Ledger (GL) is a real-time system, the standard posting functionality will update the account balances of detail and summary accounts. When you post to an earlier effective date or open period, actual balances roll forward through the latest open period. If you post a journal entry into a prior year, Oracle GL adjusts your retained earnings balance for the effect on your income and expense accounts. End of day reporting is complicated with a single instance and server time stamp worldwide.

Business Case - Banks and Financial Institutions in US are regulated Corporations subject to daily, weekly, monthly, quarterly and annual report requirements as defined by the SEC, the Federal Reserve, and IRS. Additionally, the global branches and subsidiaries are subject to foreign regulatory agencies. These banks requires that the Oracle Financials Application's GL support the requirement that transaction processing be cut off based on an end of day and reporting generated as a result of that end of day. Currently, the standard functionality of Oracle GL does not support the concept of end of day. The Open/Close is controlled by Accounting Periods in months, not days.

Solution Overview: A custom solution was developed incorporating three types of changes that are required to support end of day processing requirements:
- Add Processing Date
- Store Daily Balances
- Prevent future dated transactions from affecting end of day balances

Welcome Note

Welcome to Real World Oracle Apps !

In this blog, you will find documentation and useful information which I have gathered over 10 years by designing and implementing Oracle Apps for various clients.

Please leave your feedback which I will use to enhance this site with more information.

If you or your client are interested in learning more about any of the Real Worl Oracle Apps solutions described in this blog, feel free to reach me at swathi00@yahoo.com

Thank You!