A day in the life of an Oracle Applications Consultant

Saturday, July 10, 2004

Automating the creation of account code combinations (CCID’s) in Oracle General Ledger

This post outlines two methods one can use to automate the creation of account code combinations in General Ledger

In certain instances it may be necessary to manually create account code combinations (CCID’s) in Oracle General Ledger. To some extent this is determined by whether your organization has decided to use dynamic insert or not. Dynamic insert is an option you can turn on or off when creating your accounting flexfield. If the option is turned on it will allow account code combinations to be automatically generated. If dynamic insert is turned off then it will certainly be necessary for a General Ledger Super User to go and create an approved set of combinations for use within your organization. Even if dynamic insert is turned on you may find that at the beginning of an implementation that there are not enough active account combinations. This means you will more than likely have to manually add account code combinations as presently Oracle has no automated facility to perform this. To manually add account code combinations one should login under General Ledger Super User and on the navigation menu go to Setup > Account > Combinations. Having navigated to the form displayed in Fig. 1 you can then insert the needed lines.

Figure 1: The Account Code Combinations Form Maintain General Ledger Account Code Combinations (CCID's) using this Oracle Form As manually adding account code combinations can be a rather tiresome job I investigated potential ways to automate the process and have come up with the following means of doing so: -

1. Journal Load via ADI

Using this option entails creating an excel spreadsheet journal via ADI with the account code combinations that you would like insert. The account code combinations should contain zero values. After uploading the spreadsheet journal from ADI an unposted journal will be created in General Ledger. The unposted journal can be deleted and the account code combinations that you created will remain behind. The reason for using the spreadsheet journal as opposed to using a journal in General Ledger is that the spreadsheet makes it easier to create the account code combinations you want to load as you can use the copy and paste features.

2. Use of DataLoad

A dataload template can be created and the account code combinations can be loaded directly into General Ledger.

Another tip

Personally I prefer to use the first method mentioned as it is much faster than using Dataload. Another word of advice when creating these combinations. One should ensure that you have correctly filled in the flexfield qualifiers (displayed in Fig. 2) when capturing your segment values as the account code combinations will use this information to determine how a CCID is created.

Figure 2: The Flexfield Segment Qualifiers

The Flexfield Segment Qualifiers should be correctly chosen to ensure proper creation of a CCID

For example, if for any of the accounts in the combination of segment values, you have allow budgeting and allow posting set to “no”, then the account code combinations as displayed in Figure 1 will be created with allow budgeting and allow posting set to “no”. If you create an account code combination with the incorrect value for allow budgeting and allow posting, you can go and manually change the value. However, if you have a large number of lines that have the incorrect values you cannot merely go and upload another blank journal after having corrected the mistake in the flexfield segment qualifiers. You will still have to manually make the amendments as once an account code combination has been created you cannot “overwrite” it.

Lastly, if anyone has come up with any other better method or an automated procedure for performing this activity then please add your comments.

Friday, July 09, 2004

DataLoad - a tool to facilitate system data migration

This post discusses the DataLoad Software - a tool that will assist you in application data migration.

In the early days of my present occupation, namely Oracle ERP Consultant, I was introduced to the DataLoad utility. I can’t remember who introduced me to the tool but I owe them a big thankyou. I have subsequently used it on every single implementation I have been involved in for the past 3 years and am sure I will continue to make use of it in the future. I was able to learn DataLoad incredibly quickly as it is very easy to use. In a former life I was a BaaN consultant, I only wish I had been introduced to this product then as DataLoad can work on any application that accepts inputs from a keyboard or mouse. DataLoad comes in two versions, namely Classic and Professional. Up until now I’ve managed to get by using the Classic version which is free but on my most recent implementation we had some fairly heavy duty data loads to perform and so decided to buy Professional. The differences between the two version are outlined on the DataLoad site. According to the DataLoad site: -

“DataLoad loads data into any application running in Windows, and contains extra functionality for loading data and setup into Oracle Applications.

DataLoad loads data through the forms. This means the data is indistinguishable from that entered by users. To load data using DataLoad you setup DataLoad to load into the forms and the forms load the data into your system. This non-technical approach means the load can be built and run by non-technical users, and by not having to write conventional interfaces into the database a huge amount of time and money can be saved”.

Essentially there are two types of data that one needs to think about when it comes to Enterprise Applications - master data and transactional data. Examples of Master Data are Customer or Supplier information whilst transactional data might consist of sales or purchase invoices. In the instance of Oracle Applications a further data type (which is essentially master data) can be outlined, namely Application Object Library (AOL) or Setup Data such as profile options and flexfield definitions.

Loading data of any type into your application from DataLoad Classic is a three step process: -

1. Obtain your data in Electronic Format (e.g text file, excel file)

2. Copy or Import the data into a DataLoad template.

3. Load the data in to your application from the DataLoad template.

The key to success is designing your DataLoad template in ways that ensures a smooth load of your data. The DataLoad template will be used to load your data by simulating the keystrokes and mouse clicks that you would normally use to capture the data manually. An example of what a DataLoad template looks like is as follows: -

DataLoad Template Example (for loading the Accounting Calendar)

An example of a DataLoad Template for loading the Accounting Calendar

For Oracle Applications users, Pre-Built Spreadsheets or templates can be downloaded from the DataLoad web site for each module - the obvious benefit here is you will save time by not having to build your own templates. The pre-built templates can also be edited and I find it best to start out with a pre-built template and modify it, rather than building a template from scratch.

Obviously DataLoad is not the only way to migrate or load data into your applications. Each alternative means of data migration should be carefully considered and the pros and cons established (the Accenture presentation mentioned below provides a good comparison of the alternatives). Where necessary a cost benefit analysis may need to be prepared.

Related Presentations

The following presentations will provide you with further information on DataLoad: -

Data Migration in Oracle E-Business Suite (679KB) by Trond Steensnaes of Accenture at an OAUG Forum Meeting in Paris.

Thursday, July 08, 2004

The Financial Systems Project (FSP) - an Oracle Implementation and Upgrade

This post contains details of a Financials Systems Implementation and Upgrade that was undertaken at the University of Waterloo and contains links to the associated documentation.

The University of Waterloo undertook an Oracle Applications R10.5 Implementation in 1996 and Upgraded to R10.7 in1998. Although the implementation and upgrade was carried out some time ago I thought that the documentation provided on their site would still be useful to any potential implementors of Oracle Applications. According to their site: -

The May 1996 implementation of the Financial Systems Project (FSP) was motivated by the realization that UW’s Computer-based central accounting systems had many limitations and inefficiencies. Its mission has been: “To conduct a comprehensive review of financial processes leading to the development of an integrated, effective and efficient system for managing the financial affairs of the University”.

The December 1998 upgrade of the Financial System was motivated by the need to achieve year 2000 compliance within the application software.

The Financial System Project consists of the implementation of Oracle Government Financials (General Ledger, Accounts Payable and Purchasing modules) from ORACLE Corporation plus the custom extensions written to accomodate interfaces with other systems used on campus.

The Financial Systems Project site contains the following types of documentation: -

  • Upgrade Project Summary
  • Project Team Structure and Membership
  • Architectural Diagram
  • Information Security Policy and Standards
  • ORACLE Government Financials Security
  • Technical and Functional Documentation

Of particular interest to those involved in Chart of Accounts design will be the Accounting Flexfield Values portion of the site. Here the Chart of Accounts segments structure and associated values that populate the structure is outlined.

The Applications Technology “department” of the University should also be looked at. According to the site: -

“Applications Technology is responsible for the application software supporting the University’s information systems. This responsibility encompasses all aspects of the System Development Life Cycle; including business process and system analysis, design, acquisition, development, implementation, documentation, and ongoing maintenance. In particular, this includes responsibility for designing and evolving the corporate database. As systems move into production, this group cooperates with Client Services and Electronic Workplace to train users and provide them with access to systems, and with Production Support to commission systems and provide second-level support to ongoing production”.

On the Applications Technology site you will find details of ongoing projects at the University in the areas of Co-operative Education & Career Services (CECS), Financial Systems, Human Resources, Strategic Consulting and Student Information Systems.

I’m interested to know whether the University has upgraded to 11i. I’ve fired a mail their way to find this out and will update this post with any further information or documentation I obtain.

Wednesday, July 07, 2004

Oracle Applications User Group (OAUG) Connection Point 2004 Conference Sessions now available

This post contains details of what sessions will be taking place at the OAUG Connection Point 2004 Conference in Orlando, Florida

The OAUG has just released a listing of the training sessions, presentations and panel sessions that will be delivered at Connection Point 2004. According to the OAUG: -

“This year’s event schedule has been revised to afford you more advocacy, educational and networking opportunities! Don’t miss your opportunity to:

  • EXPERIENCE over 200 sessions, including presentations, Q & As, Panels, Workshops and training sessions.
  • DISCOVER complementary vendor products and services in the Exhibit Hall.
  • EXAMINE Applications and solutions in the Hands-on Demo Area.
  • INTERACT with Special Interest and Geographic User Group members.
  • OBTAIN CPE Credits for Financial and Human Resource track sessions”.

If you plan to attend the conference or are just interested in what topics are spoken on at the conference then read more on the sessions here.

Oracle Applications Newsletters

This post contains details of the various Oracle Applications Newsletters that can be downloaded.

Solution Beacon - an Oracle Partner in America has a really good quarterly newsletter which will keep you up to date with all the latest developments in 11i E-Business Suite. Download it here

Assist - an Oracle Partner in Australia has a number of quarterly newsletters that can be downloaded here. Unfortunately these don’t seem to have been updated since 2002, however, the newsletters going back to 1999 still have some interesting and useful information.

A variety of regular E-newsletters can be subscribed to at ITtoolbox and

If any readers know of any other good newsletters that have an Applications or Functional focus please send me a mail or add a comment to this post.

Page 3 of 4 pages  < 1 2 3 4 >