Oracle 12i - How to create accounting and transfer JE's to GL using SLA

I. Create Accounting Program:

The Create Accounting program processes eligible accounting events to create subledger journal entries. To create the subledger journal entries, the Create Accounting program applies application accounting definitions that are created in the Accounting Methods Builder (AMB).

The Create Accounting program:
•Validates and creates subledger journal entries
•Optionally transfers the journal entries to GL
•Optionally posts the journal entries in GL
•Generates the Subledger Accounting Program Report, which documents the results of the Create Accounting program

Draft Accounting:
When you select draft accounting, Subledger Accounting creates the relevant journal entries in draft mode. Draft entries are not posted to General Ledger. You can review the resulting entries, update the transactions, or update the accounting rules. Any changes will be reflected when the transaction is processed again for accounting.

Online Accounting (Final):
Final entries are ready to be transferred to General Ledger and cannot be modified. The transactions are considered as processed for accounting. Any changes to the rules will not impact final entries.

Straight-Through Accounting (Final - Post):
If you select Final Post, Subledger Accounting posts the journal entries all the way through to General Ledger. This means that you can update GL balances straight from the invoice entry (or any other transaction entry) window.

Create Accounting Program:
The Create Accounting program creates subledger journal entries. In general, the parameters described in the table above determine which accounting events are processed.
Navigation Paths (example Payables)
Payables: Other > Requests > Run
Receivables: View > Requests (B) Submit a New Request

Paramaters:

1. Ledger - Required; limits accounting events selected for processing to those of a particular ledger. This program is run for primary ledgers or valuation method enabled secondary ledgers. Any reporting currency or secondary ledger associated with the selected primary ledger is also processed; i.e. entries are generated for the selected primary as well as reporting currencies and non-valuation method secondaries.

2. Process Category - Optional; restricts the events selected for accounting to a particular process category. For example, Invoices.

3. End Date - Required; end date for the Create Accounting program; processes only those events with event dates on or before the end date

4. Mode (Draft/Final) - Required; determines whether the subledger journal entries are created in Draft or Final mode

5. Errors Only (Yes/No) - Required; limits the creation of accounting to those events for which accounting has previously failed

6. Report (Summary/Detail/No Report) - Required; determines whether to generate a report showing the results of the Subledger Accounting program in summary or detail format

7. Transfer to General Ledger (Yes/No) - Required if Mode is set to Final; determines whether to transfer the subledger journal entries to General Ledger

8. Post in General Ledger (Yes/No) - Required if Mode is set to Final; determines whether to post subledger journal entries in General Ledger

9. General Ledger Batch Name - Optional; user-entered batch name that appears on the transferred General Ledger subledger journal entries. Transfer to GL option must be set to Yes.

10. Include User Transaction Identifiers (Yes/No) - Required; controls whether the report displays user identifiers' names and values.


Create Accounting Program:
The Create Accounting program generates one or more accounting programs depending on the volume to be processed. The Subledger Accounting Program report is generated by the Create Accounting program and documents the results of the Create Accounting program. It lists the following:
•Successful events and the subledger journal entries created for those events
•Errors for failed events
You can run the report in summary, detail, or no report mode which are described as follows:
•Summary mode provides a summary of events processed and detailed information about their errors.
•Detail mode provides details of subledger journal entries generated from the processing of completed events and a detailed error report.
•No report mode will show an error count without actually generating the report.

II. Transfer Journal Entries to GL Program:

The Transfer Journal Entries to GL program enables you to transfer any eligible journal entries to General Ledger, including those from previous runs that have not yet been transferred to General Ledger.

Note: This program is used if you run accounting online in Final mode (not Final Post) or if you run the Create Accounting program and set the Transfer to GL parameter to No.

The only reason you would want to run the Create Accounting program and set the Transfer to GL parameter to No is if you want to run accounting at different intervals than the GL transfer, for example, you may run accounting every hour but only transfer to GL nightly.

The Transfer Journal Entries to GL program consists of a subset of parameters used in the Create Accounting program as listed below:
–Ledger
–Process Category
–End Date
–Post in General Ledger
–General Ledger Batch Name

III. Oracle Subledger Accounting Program Report:

The Subledger Accounting Program Report is generated by the Create Accounting program and lists the following:
•Successful events and the subledger journal entries created for those events
•Errors for failed events

You can run the report in summary or detail mode as follows:
•Summary mode provides a summary of events processed and detailed information about any errors.
•Detail mode provides details of subledger journal entries generated from the processing of completed events and a detailed error report.

IV. Transfer Journal Entries to GL Report:

The Transfer Journal Entries to GL report is generated by the Transfer Journal Entries to GL program and lists the following:
•Transfer to GL Summary
•Errors

Setting Profile Options:
The profile options listed above relate to data access and security and impact how accounting is generated through SLA in R12.

1. SLA: Enable Subledger Transaction Security in GL
•Use this profile option to combine subledger transactions security with data access security for General Ledger responsibilities when drilling down to multi-organization enabled subledger application. Transaction security in the respective subledger application is always applied when drilling down from subledger transactions to subledger journal entries.

2. SLA: Enable Data Access Security in Subledger
•This profile option determines whether the General Ledger Access Set security mechanism is applied for a subledger application responsibility when viewing, reporting, or creating subledger journal entries associated with a given ledger. The General Ledger Access Set security mechanism is always applied for responsibilities associated with the General Ledger application.
•The profile option enables you to combine data access security with subledger transaction security and therefore control access to subledger journal entries depending on the ledger to which they belong. For example, you can implement a Multi-Org Security Profile that allows you to create Oracle Receivables Invoices for two different operating units each associated with different ledgers but restrict drill-down from the subledger transaction to the associated subledger journal entry based upon the destination ledger contained in the Access Set.

3. SLA: Additional Data Access Set
•The SLA: Additional Data Access Set profile option, in conjunction with the GL: Data Access Set profile option, controls which ledgers and balancing or management segment values you can access when logging onto a responsibility. If SLA: Enable Data Access Security in Subledgers is enabled for the responsibility, you have access only to the ledgers and balancing or management segment values included in the data access sets assigned to the SLA: Additional Data Access Set and GL: Data Access Set profile options.

4. SLA: Allow Reports Journal Source Override
•This profile option applies only to the following reports:
-Open Account Balances Listing
-Third Party Balances Report
•Enable this option to change the Journal Source parameter during report submission. If the option is set to No, then you cannot change the value defaulted during report submission.
For example:
•Should the general ledger data access set security be enforced when generating accounting? For example, should journal entries be created if the user does not have ledger clearance even if they may have multiorg access to the operating unit?
•Should the transaction security model be applied when drilling down from GL? For example, should the user be allowed to inquire on journal entries of certain operating units if they do not have MO access, but have ledger clearance?
•If there are secondary ledgers and data access set security is enforced in the subledger module, then an additional data access set needs to be assigned to the user to enable access to the secondary ledger.
•Should the user be able to run certain reports across data from multiple subledger applications?

Implementation considerations for R12 General Ledger setups

Many clients ask for best practices and implementation considerations for implementing a new GL in 12i, here are some important implementation considerations:

Chart of Accounts:
–Share the same value set for the balancing segment across charts of accounts

Determine the Number of Legal Entities to Assign in an Accounting Setup based on:
–Statutory and legal requirements for legal entity accounting, such as document sequencing, tax accounting, and intercompany accounting
–Business Needs

Types of Accounting Setups:

1. Accounting Setup with No Legal Entity (LE)
–Can be used for GL only implementations
–Used for a business need where no legal entity is required or management reporting
–Cannot maintain legal entity context in subledger transactions
–Cannot set up Operating Units
–Cannot use Advanced Global Intercompany Accounting System

2. Accounting Setup with One LE
–Cannot perform lump sum payments across legal entities in AP

3. Accounting Setup with Multiple LEs
–Cannot perform autonomous document sequencing and tax that is unique per legal entity
–Consider local laws that prohibit commingling transactions with multiple LEs

Choosing the Ledger to be the Corporate Representation:

Best practice is to choose the primary ledger to be the corporate representation in the local currency:
–Provides the most detail
–Allows you to assign a subledger level reporting currency if you want to maintain a detailed additional currency representation
–Note: Secondary Ledgers cannot have subledger level reporting currencies assigned

Legal Entity (LE) Accounting:

1. LE above Ledger
–If you require multiple primary ledgers to perform accounting for the same legal entity, you must create dummy legal entities because a legal entity can only be assigned to one accounting setup, not multiple accounting setups

2. LE = Ledger
–If legal entity equals the primary ledger, you may choose not to assign balancing segment values to the legal entity

3. LE Below Ledger
–Legal entities should be represented by one or more balancing segment values

Balancing Segment Value (BSV) Assignment:

1. No BSVs Assigned = All values available for data entry
2. Assign specific BSVs to LEs at any time
3. Behind the scenes, BSVs assigned to LEs are automatically assigned to the ledgers
4. No BSV validation performed across accounting setups
5. BSV assignment is required for Intercompany Accounting
6. Optionally assign BSVs to the ledger for non-legal entity related transactions, such as management adjustments
7. Before BSVs can be assigned to the ledger, they must be assigned to LEs first, if used

Reporting Currencies (RC):

1. Journal and Subledger level RCs act like regular ledgers
2. To automatically populate historical data, run the Create Opening Balance Journals in Reporting Currency program.
3. Conversion for subledger sources that uptake SLA are disabled for Subledger Level SLs
4. When posting a journal to the PL, journals for the RC will be automatically created/posted in the same batch
–Ensure same open periods for source ledger and RC
5. Data access set should include same write access to source ledger and RCs to prevent posting errors

Subledgers that uptake SLA (Assets, Budget Execution, Cash Management, Cost Management, Inventory, Oracle Loans, Payables, Payroll, Property Manager, Purchasing, and Receivables)

Secondary Ledgers (SL):

1. Can be added at any time with an unlimited number allowed
2. To populate historical data, use Consolidation (GCS)
3. Secondary Ledgers cannot have Subledger Level RCs assigned
4. COA Mapping required if COAs are different between the PL and SL
–COA Mapping can be used if the COAs are the same except for Subledger Level SLs
5. Conversion for subledger sources that uptake SLA are disabled for Subledger Level SLs

Implementing Oracle GCS (Part 2)

Client and Business Case - Global Manufacturing client wanted to implement GCS in Oracle GL to enable consolidation within Oracle. The steps involved in configuring GCS are detailed in the following sections.

I. DEFINE SETS OF BOOKS (SOB):
1. Where all subsidiaries share a single SOB which consists of the same COA, accounting calendar, and currency, it is purely a reporting kind of consolidation requiring FSG tool. No separate consolidation SOB is required
2. Where each subsidiary and the parent company require their own SOB meet their operational or local accounting needs:-
2.1 If Parent SOB can be touched, then data transfer can take place to the Parent SOB.
2.2 Else, a separate consolidation SOB is required where data transfer has to take place.
3. Where companies use non-Oracle Applications, Create dummy SOBs within the GCS parent’s database instance. Each of these SOBs represent the subsidiary SOBs on disparate application instances. The consolidation can happen to Parent SOB or a separate consolidation SOB.

II. GATHER DATA FROM DISPARATE INSTANCES OR NON–ORACLE GL SYSTEMS:
GCS provides a convenient interface to import subsidiary data from any external source,
such as a separate database instance of Oracle GL, or an entirely separate non–Oracle
accounting system. There are two options available for gathering data from diverse sources:
1. Applications Desktop Integrator (ADI) to work in a spreadsheet environment. Simply transfer the subsidiary information from the external system into ADI’s journal worksheet. Then upload the entries to the GCS system where it can be posted.
2. SQL Loader to load data from diverse sources into the GCS open interface.

III. MAP SUBSIDIARIES TO PARENT:
To consolidate multiple SOB that have different functional currencies, accounting calendars,
or charts of accounts, first map subsidiaries’ COA to parent’s COA. A consolidation mapping is a set of instructions for mapping accounts or entire account segments from a subsidiary SOB to the parent SOB. When one subsequently transfers amounts from a subsidiary to parent, GL creates an unposted consolidation journal batch in parent SOB based on the subsidiary’s mapping information.



IV. PREPARE YOUR SUBSIDIARY DATA:
Prepare subsidiary data by revaluing and translating balances before transferring the balances to parent.
1. Revalue Balances - If any subsidiary SOB has balance sheet accounts that are denominated in a foreign currency, revalue the balances to reflect the impact of any changes in exchange rates. Post the resulting revaluation journal.
2. Translate Balances - If any subsidiary SOB uses a functional currency different from parent, translate the account balances into the parent SOBs’ functional currency before transferring the subsidiary data to parent.
3. Multiple Reporting Currencies (MRC) - If MRC is used, then consolidate directly from subsidiary’s reporting SOB to parent SOB. Revaluation needs to be run on the primary and reporting SOB before consolidation. The primary issue to consider when deciding whether to consolidate directly from a subsidiary’s reporting SOB is: What accounting rules govern the parent’s and subsidiary’s business environments?

V. TRANSFER DATA:
1. Transfer the balances or transactions to be consolidated from subsidiary SOB to parent.
2. GL accumulates subsidiary information based on the mapping rules defined, then populates the GL_INTERFACE table with the consolidation data.
3. Journal Import must be run, to create an unposted consolidation journal batch in parent SOB.
4. One can initiate a subsidiary–to–parent transfer from the subsidiary or from the parent SOB.
5. One can transfer balances (Actual / Budget) or transactions (Actual only).


VI. POST CONSOLIDATED DATA:
Once the subsidiary data has been transferred to parent SOB, one needs to combine the subsidiary and parent data. This involves several steps:
• Run Journal Import if it was not chosen as one of consolidation run options.
• Review the unposted journal batch created by the transfer and subsequent Journal Import.
• Post the consolidation journal in parent SOB.

VII. ELIMINATE BALANCES:
The Oracle GL Automatic Intercompany Eliminations program eliminates intercompany balances. Create an elimination set which is a batch of one or more elimination journal entries.
1. Full Eliminations: Elimination sets can optionally use an elimination company to fully eliminate a group of intercompany elimination entries for a set of subsidiaries.
2. If your elimination journals are out of balance, you can specify balancing options to either allow out of balance journals to be created or to post the net difference to an alternative account. In addition, threshold rules can be applied to prevent the creation of elimination journals if the net difference exceeds a specific amount, a percentage of a particular account, or a percentage of the total journal.
3. You generate the elimination set every period to automatically create the elimination journal entries. You have the option of automatically posting the journal or wait till you review it.
4. You can also use the Consolidation Workbench to track the elimination status of your elimination sets and post any generated elimination sets.
5. Formula–Based Eliminations: If you have formula–based elimination entries or you want to eliminate average balances, use Oracle GL’s recurring journals feature.
6. The Automatic Intercompany Eliminations program automatically generates eliminating entries per the rules specified.

VIII. REPORT:
Use the FSG as the mechanism to sum up the subsidiaries to produce consolidated results in case of Single SOB. In case of companies having multiple SOB / non-Oracle Application, use FSG to report on consolidated results. Use the ADI to extend reporting to the spreadsheet environment. ADI allows to create and publish consolidated reports in HTML format to the Internet or corporate intranet.

IX. ANALYZE:
1. Use full drilldown capabilities to drill from consolidated balances down to subsidiary journal lines and subledger detail. GCS enables to drill from consolidated balances in the parent SOB directly to the subsidiary SOB within the same instance. In case of companies having disparate applications, one cannot drilldown to the subledger detail.
2. Directly link data to Oracle Financial Analyzer, an online analytical processing (OLAP) application, to analyze consolidated balance and prepare operational and financial analyses for your management team. One can also drill between a subsidiary’s translated balance to its original balance. GCS also provides you with the ability to drill between summary accounts, detail accounts, and the original journal entries, down to The subledger detail.

Consolidation in Oracle GL (Part 1)

Client Profile - Global clients who want to implement Consolidation functionality within Oracle GL. Other consolidation methods include Financial Consolidation Hub (FCH) and use of Hyperion cubes to generate Consolidated reports.

Consolidation - is the period–end process of combining the financial results of separate subsidiaries with the parent company to form a single, combined statement of financial results. Clients can consolidate any number of subsidiaries that use different SOBs and having different COA, currencies and calendars.

GL Consolidation Tools - There are basically two consolidation tools which can be used for consolidation within Oracle GL.
1. Financials Statement Generator (FSG) - Used for consolidating financial information for businesses using a single SOB or using different SOB that share the same calendar and COA.
2. Global Consolidation System (GCS) - GCS is a multi-source consolidation solution that can accumulate information from diverse financial systems, geographic locations, including Oracle and non-Oracle Applications. With GCS one can consolidate data from multiple SOBs, multiple instances and non-Oracle applications.

What Can Be Consolidated? - With GCS, one can consolidate any business dimension at any level of detail from any point of view:
1. Any Source: Data from any source system, including ledger, databases, or other accounting systems can be consolidated
2. Any Chart of Accounts: Subsidiaries can use separate COA from the parent to address unique operational accounting practices and meet local statutory requirements. GCS enables you to consolidate across diverse charts of accounts.
3. Any Calendar: Subsidiaries can use different accounting calendars from the parent. GCS enables you to consolidate across calendars.
4. Any Currency: Subsidiaries can use a functional currency which differs from the functional currency of the parent. GCS revalues and translates all subsidiary balances to ensure consistent consolidated results.
5. Any Level of Detail: Consolidate detail transactions, detail balances and summary balances.
6. Any Balance Type: Consolidate any balance type; including actual, average, translated, budget, and statistical balances.

Implementation Considerations -
•Do all subsidiaries share the same SOB? If yes, then FSG can be used to meet the requirement.
•Do all subsidiaries have different SOB? If yes, then:
They share the same COA and calendar – Use FSG
They reside in the same instance – Use FSG
•Do all lines of business use GL as their operational accounting system?
•Is a separate consolidation SOB required to keep consolidation data separate from transactional data or can the companies be consolidated in the parent SOB?
•Is a separate elimination company required to segregate eliminations or can it happen in the parent company?
•Are companies to be consolidated at Balance level or Transaction level?

Other Uses of GCS:
1. Changing Chart of Accounts (COA) after implementation. GCS can be use to map values from old COA to new COA and balances from old SOB can be transferred to new set of books.
2. Changing the Accounting Calendar after implementation. GCS can be used to map and transfer account balances from the old period to the new period.

In my next article, I will explain in detail the various steps involved in setting up GCS in Oracle GL.

Designing Simple Forms Personalizations for Functional Consultants

Forms Personalization: I kept reading about this amazingly new feature called 'Forms Personalization' but what really bothered me was almost all the articles / whitepapers had the targeted audience as ‘Admin / Developer’ who have some ‘knowledge of Oracle Developer, PL/SQL, Coding Standards and API’s’.

This really caught my attention and I spent about 2 hours trying to figure out what can a Functional Consultant like myself get out of this new feature. Good news is that you don’t need to have any technical knowledge to design some simple personalizations. So in this article, I have tried to explore some easy to design personalization’s which the Functional Consultants can easily build and test.

Why personalization’s are handy – Since Oracle Apps is a pre-built product, most of the clients would like to remove/ rename, change default values of some of the fields, buttons, tabs etc. Traditional method of getting all the above ‘extensions’ done was to ‘customize’ the form (by customizing the custom.pll) which was generally protected during upgrades / patching. Luckily most changes traditionally done using cutom.pll can be accomplished using Forms Personalization.

Functional Folks - How to quickly get started – The ‘personalize’ menu can be found at Help -> Diagnostics -> Custom Code -> Personalize. To prevent unauthorized users from changing the look and behavior of forms, please use the following profile options:
Utilities: Diagnostics = Yes / No
Hide Diagnostics = Yes / No

Four sections to define a Personalization – There are four sections in the define forms personalization form:
Rules Header – This is where you enter the rules and rule descriptions in plain English. You can sequence these rules using a number and enable/ disable any one rule by using the Enabled checkbox.
Conditions – Consists of Trigger Event: the event within the form that causes invocation of the rule, Trigger object: the context for the trigger event, such as a
particular block or item, Condition: an optional SQL fragment that, when it evaluates to TRUE, allows the rule to execute.
Context or Scope - The Scope is evaluated based on the current runtime context to determine if a Rule should be processed or not. The Scope can be at the Site, Responsibility, User, or Industry level. Each Rule can have one or more Scopes associated with it.
Actions – Determines what the personalization does when the condition and context are true. Various types of actions available include ‘Property’, ‘Message’, Builtin’ or a ‘Special’. Based on the type, additional definition

Real world examples you can try – One of my banking client wanted the Journal Enter form to be customized for the following business rules:

1. Change the ‘Journal’ prompt to display ‘Voucher’
2. Journal description should always be in UPPER CASE
3. Do not display the Budget field, as budgets were not implemented
4. Note to display warning to change Period for prior period Journals.

The following screenshots explain how these four business requirements were implemented using Forms Personalization:

1. Change the ‘Journal’ prompt to display ‘Voucher’:




2. Journal description should always be in UPPER CASE:



3. Do not display the Budget field, as budgets were not implemented:



4. Note to display warning to change Period for prior period Journals:



Journal Entry Form after Personalizations:

When opening the form, the following note gets displayed:



Journal Entry Form after Personalization (Note - Voucher, Budget not displayed and Description in Upper Case:

General Ledger Implementation Considerations - Sets of Books (Ledger for 12i) and Chart of Accounts

Set of Books (Ledger for 12i):
Set of books determines the functional Currency, Chart of account structure, and accounting Calendar for each company or group of companies, which are known as the 3 C's.

You need to create and set up one or more sets of books. The number of books you set up depend on various factors, such as:
Ø Whether you have multiple subsidiaries using different calendars, charts of accounts, and currencies requiring multiple sets of books
Ø Whether you have multiple subsidiaries that share the same calendar, chart of accounts, and currencies that allow them to share the same set of books
Ø Your organizational requirements or government reporting requirements.

General Ledger provides you with the flexibility to manage your financial information within any company structure. One can maintain multiple companies with similar or different accounting structures, and consolidate their results for meaningful financial reporting.

Chart of Accounts:
Chart of accounts uses Key Flexfield (Accounting Flexfield) that, comprises of Segments, Value sets, Values and Code Combination ID.

Defining the Chart of accounts:
Planning the Chart of accounts structure is the most important activity involving the structure, segments, segment validation and additional features. Analyze the organizational structure and the dimensions of the business before designing the Chart of accounts. By carefully evaluating the business needs, design the chart of accounts to take advantage of General Ledger’s flexible tools for recording and reporting the accounting information.

The account structure can comprise 30 segments with a maximum character length of 275 for the entire String. Each string of multiple-segments is called a code combination and stores a code combination ID based on which balances are maintained in GL. An accounting chart normally consists of Company code, Line of Business, Cost center, Natural Account, Intercompany and Future.

How to define Key Flexfield Structures like Chart of Accounts:
Ø Define descriptive information and validation information for each segment.
Ø Determine the appearance of your key flexfield window, the number and order of the segments, and the segment descriptions and default values.
Ø Freeze flexfield definition and save the changes once set up or when the structures/segments are modified. On saving, Flexfield compiles automatically to improve on–line performance.
Ø Compile the flexfield every time when the changes are made to this form, including enabling or disabling cross–validation rules and when the changes are made to the shorthand aliases window.
Ø Oracle Applications submits one or two Concurrent requests to generate database views of the flexfield combinations table.
Ø The flexfield changes immediately after freezing and recompiling. However, the changes affect other users only after they change responsibilities or exit the application and sign back on.


Determine the Chart of Accounts Structure That Best Suits Your Organization:
Ø Examine the organization structure to identify how performance and profitability are normally measured
Ø Also multiple organizational structures may be needed to allow views of the organization from multiple perspectives. Summary accounts can be used to roll up details.
Ø Visualize each segment of the account as a unit dimension of the business. Combine units that are based on similar dimensions to avoid using multiple segments that measure the same dimension.
Ø Identify the functions, products, programs, funding sources, regions, or any other business dimensions that are to be tracked
Ø Determine the reporting needs.

Consider the following questions before defining the COA structure:
Ø What information will better help one to manage one’s organization?
Ø What are the different ways in which one can look at one’s operations?
Ø What kinds of reports do managers ask for?
Ø What reports one prepares now with some difficulty?
Ø What reports are provided by other financial information systems?
Ø What statistical reporting does one want to perform?
Ø Is project reporting needed?
Ø At what levels of detail does one produce reports?

To Determine Your COA Segment Needs:
Ø Determine the segment that captures the natural account, such as assets, liabilities, expenses, and so on.
Ø Define a separate Accounting Flexfield segment for each dimension of your organization on which you want to report, such as regions, products, services, programs, and projects.
Ø Group similar business dimensions into one segment. This allows a more simplified and flexible account structure For example, you only need one segment to record and report on both districts and regions. Because regions are simply groups of districts, you can easily create regions within your district segment by defining a parent for each region with the relevant districts as children. Use these parents when defining summary accounts to maintain account balances and reporting hierarchies to perform regional reporting.

The 'X' Factor

'X' Concept - One of my clients wanted to get a Trial Balance in the US Sets of Books which will only have Transactions which are entered in US dollars. As per the standard Oracle functionality, if the transaction currency is the same as the SOB functional currency, the Entered Dr and Entered Cr columns are not populated. Only the Accounted Dr and Accounted Cr columns are populated. This creates an issue as the client wants to run a trial balance on transactions entered only in SOB functional currency.










High Level Solution - 'X' currency, to overcome this, a solution was developed to use a currency called ‘X’ as the USD books reporting currency. If we take USD (Primary SOB) and USX (Reporting SOB), all transactions in USD in USD SOB will appear as a transaction currency in the USX SOB (foreign currency transaction). This will enable the user to run foreign currency Trial Balance in the USX books on transactions entered in USD.

Summary Accounts, Parent Values and Rollup Groups

What are Summary Accounts?
• A summary account is an account whose balance is the sum of balances from multiple detail
accounts.
• Use summary accounts to perform online summary inquiries, as well as speed the processing of financial reports and Mass Allocations, and Recurring journal formulas.
• You do not enter or post transactions directly to summary accounts.

Summary Accounts Examples:

Company Segment - Summarize companies by major industry; by regions within a country; or by country group.

Cost Center Segment - Track functional areas at a detailed level but produce summary reports that group cost centers into one or more divisions.

Account Segment - Summarize your accounts by account type: Assets, Liabilities, Equity, Revenue & Expense or more detailed: Current Assets.

Detail versus Summary Accounts:
• Summary Accounts are updated when journals are posted to a corresponding detail account. They enable online summary inquiries and speed concurrent processing.

• Detail Accounts allow direct posting of business transactions and journals.

Summary Accounts versus Parent Values:


















Parent Values and Rollup Groups:











About Rollup Groups:

• A rollup group is a collection of parent segment values for a given segment.
• A value cannot belong to a rollup group unless it is a parent value that has child values.
• Parent values and child values belong to the same value set, which is attached to a key flexfield
segment.

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