The Feature

How to Create Custom WebADI Integrators

November 11, 2008 on 10:40 pm | by Marian Crkon | In How To Guides | 7 Comments |  Print |  Email

Web Applications Desktop Integrator (Web ADI) is a self-service Oracle application, accessed using a browser. Unlike ADI, the  Web ADI software is not installed on individual client machines, as the processing takes place on the server rather than on individual client PCs.

Web ADI generates Microsoft Excel or Word documents on your desktop, and downloads the data you have selected from the application. You can modify the data in the Microsoft Excel or Word documents, working either online or offline, and upload the data using Web ADI. In addition to the Web ADI integrators supplied with Oracle Applications, you can create your own integrators to allow download and upload data from Oracle windows or direct from Web ADI.

Except for HRMS, it seems to me that other Oracle Development teams have not utilized Web ADI to its full potential.  There were some discussions in forums and Oracle blogs on whether this approach is even needed. I feel like every available interface and API that let you integrate with legacy or 3rd party applications should come with a standard Web ADI integrator.

Creating Custom Integrators

Thanks to the HRMS Web ADI, you can define your own custom HRMS and non-HRMS integrators and in a combination with the seeded Oracle APIs and interfaces, implement elegant solutions for downloading or uploading data. Examples for frequently used custom integrators include upload of employees, users, POs and requisitions, AP invoices, project events, AR transactions, etc.

Creating custom integrators is done by a system administrator. Let’s define an integrator to create employees to explain how to create custom integrators. Employees and employee assignments can be created using the standard HR_EMPLOYEE_API. Our custom integrator will upload data to this API.

Grant Access to Required Functions

As System Administrator, navigate to Application > Menu, and query up ‘Desktop Integration Menu’. Make sure to include functions required for respective integrators. In case of custom Create Employee integrator, you’ll need to include the following functions (no prompts are necessary):

• HR Integrator Setup Integrator
• HR Maintain Integrator Form Functions Integrator

Create ‘HR Integrator Setup’ Web ADI Document

• Log in as Desktop Integration responsibility. Select Create Document from the menu. The Settings page of the Web ADI wizard appears.
• In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
• In the Integrator page, select the seeded integrator “HR Integrator Setup”.
• In the Layout page, select the default layout “HR Integrator Setup”.
• In the Content page, select None.
• In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.
• A spreadsheet document is created, containing all the fields you need to enter to create your own integrator.

• Define all fields to create your Create Employee integrator and upload it to the database.

Controlling Access to Custom Integrators

Custom integrators, like predefined integrators, must be associated with form functions. Follow the steps below to associate your Create Employee integrator with the functions required to define Web ADI layout and Web ADI document.

• Select Create Document from the Web ADI menu.
• Select a spreadsheet viewer. Leave the Reporting check box unselected to download data for updating.
• Select HR Maintain Integrator Form Functions Integrator.
• Enter the Application Short Name (for example, your custom application name). Optionally, enter the integrator name.
• The displayed spreadsheet shows your selected custom integrators, and has a column for associating form functions. Enter the form function required for controlling access to the integrator.
• Upload your changes to the database.

Define Layout of Custom Integrator

Each integrator must have at least one layout specifying the columns to appear in the spreadsheet. To define a layout:

• Choose Define Layout from the Web ADI menu. The Layout page appears.
• Select the integrator for which you want to define the layout, and choose Go.
• Choose the Define Layout button to define a new layout.
• In the Define Layout page, enter a unique name for the layout. This appears whenever the user is required to select the layout, so should clearly indicate the purpose of the layout.
• Enter a unique key. The key is used internally by the system to identify the mapping.
• The Field List defaults to the complete list of columns available in the view or API.
• Choose Continue. In case of Create Employee integrator, the layout is based on the the HR_EMPLOYEE_API columns. In the next page, you select a subset of these fields to include in your layout.

Create ‘Create Employee’ Spreadsheet

And finally, using the custom integrator and layout defined above, create a new ‘Create Employee’ WebADI upload spreadsheet:

• From the Navigator window (Oracle Personal Home Page), click on ‘Desktop Integration’ responsibility. The Settings page of the Web ADI wizard appears.
• In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
• In the Integrator page, select your new integrator ‘Create Employee’.
• In the Layout page, select the default layout “Create Employee”.
• In the Content page, select None.
• In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.

• A spreadsheet document is created and ready to use for creating employees.
• Save a local copy to your PC.

Tips and Tricks For Oracle Key Flexfields

May 27, 2008 on 10:55 am | by Melanie Cameron | In How To Guides | Enter Comments |  Print |  Email

Key Flexfields are widely used in Oracle Applications. In general, they house the codes used by organizations to identify such things as part numbers and General Ledger accounts. Setting up these segments can improve the usability of Oracle, decrease the work load of the users, and increase data accuracy. The Oracle Key Flexfields Tips and Tricks paper will provide an overview of the options available and recommended best practices. When first setting up a Key Flexfield, there are some features that will greatly affect the usability and required maintenance. Read the white paper for more details…

E-Business Suite Financial Integration

May 12, 2008 on 9:50 am | by Melanie Cameron | In How To Guides | 1 Comment |  Print |  Email

Oracle E-Business Suite (EBS) consists of tightly integrated modules that result in a company’s financial statements. Tracking transactions through the system back to the source, then to the setups that affected the way the source transactions behaved is difficult at best. The Oracle EBS Integration Overview white paper (based on R11.5.10) will help you understand where these transactions are coming from, back through the interfaces to the transactions, then even farther into the setups dictating their behavior.

Tips and Tricks for Oracle FSGs

April 29, 2008 on 2:17 pm | by Melanie Cameron | In Feature of the Week, How To Guides | 1 Comment |  Print |  Email

Financial Statement Generators (FSG’s) are a necessary evil at any company running Oracle’s E-Business Suite General Ledger. Row Sets, Column Sets, Content Sets, Parents accounts or Ranges, Publish with XML, ADI. . .the combinations and possibilities are endless. And the decisions you make when creating and maintaining your FSGs can affect their ease of use and maintainability in the future.

See my Tips and Tricks for Oracle FSGs white paper for Best Practices, Tips and Tricks to making this task a little more manageable and less time consuming. When working with FSGs, perhaps the most important thing to remember is the FSG functionality is old. I have been a heavy user of General Ledger for 13 years, and except for generating outputs, it is basically the same.

That means that the majority of older installs (implemented prior to 11i) had one-off patches that greatly affected the way FSGs behave. A report created three years ago that is copied or mimicked for a new report-well, they just give different results. Research shows the new report is behaving as Oracle documentation explains it should, but the old report is not.

So I leave you with my biggest tip of the paper: Tip: If it works, don’t change it! See the while paper for more details…

How To Triage Your Oracle Support Calls: Few Helpdesk Tips

February 5, 2008 on 6:07 pm | by Melanie Cameron | In How To Guides | 6 Comments |  Print |  Email

Here is a Part II to the How To Triage Your Support Calls article, with a few practical tips for resolving basic support issues.

Printing Issues:

Here are some basic steps to resolve a printing issue.

• Ensure the report had output to print.

• Ensure the report was set to print 1 copy and not 0.

• Confirm that the User sent the job to the right printer.

• Read the log file and confirm that printing was not disabled for this report.

• Print something from the User’s computer—not Oracle.

• Don’t forget the basics. Is the printer plugged in and on line?

What does Support need?

• Oracle access to view all concurrent requests, output, and Logs.
• A list of Oracle printer names, network printer names, the relationship if they are not the same, and their physical locations.
• Training on reading the Log Files in Oracle.

Access Issues: User Needs More Responsibilities

Support can be assigned this duty with the proper approvals or they can assist the User through it if they are familiar with the process.

What does Support need?

• Proper Oracle access to users.
• A list of valid responsibilities and when they should be assigned to whom.
• A list of Segregation of Duties violations for those responsibilities if you do not have software to do this automatically.

Access Issues: User Can Not Access Specific Responsibilities

If you are using Self Service applications (iExpense, iProcurement, etc) and a User can not access these specific responsibilities, the Securing Attributes were not assigned correctly for the responsibility by the system. Normally Oracle assigns these automatically, but if you set up a User, save the record and then assign them iExpenses, ICX_HR_PERSON_ID will not get assigned automatically and need to be manually assigned.

What does Support need?

• A list of the Securing Attributes used in the system and in which cases they are used.
• Access to Users in Oracle.

Access Issues: Sign-on Problems

A User can not sign-on on their PC, nor can anyone else. However they can sign on using their user name and password on another PC. This is a Cache issue.

What should Support do to correct this?

• Clear the Cache on Internet Explorer.
• Clear the jCache on the jInitiator.

Access Issues: Form-based Applications

User can sign into Oracle and get to the first page as well as Web-applications, but can not sign into form-based applications. The culprit is always one of two things:

• Popup blocker.

• First time using Oracle on this PC and the jInitiator can not be installed because of the Internet Security settings.

What should Support do to correct this?

• Turn off the Pop-up Blocker. It may either be IE’s standard pop-up blocker or a Toolbar > Popup Blocker downloaded automatically.
• Change the Internet Security Settings to allow ActiveX Downloads. These can be changedback once the jInitiator is installed. Reminder: This is a software installation so the person logged on will need admin rights to the PC in order for it to install.

• Train your Support Team to click on Grant Always for the Oracle Certificate so the Users do not have to click on it every time they sign in. If they see your Support Team “Grant This Session,” they will do the same!

Forgotten Passwords

Forgotten passwords are a problem for all companies. Support should be able to handle all password issues on the first call.

What should Support do to correct this?

• Make sure support knows what security you have set up for passwords in Oracle. They will need to educate Users from time to time.
• Users use all instances, not just production. Support should have access to all instances and be able to reset passwords in all instances.
• Turn on Oracle’s Forgotten Password functionality which allows Oracle to reset a User password and Email it to them (see Metalink Note 399766.1).
• If you have Oracle set to lockout Users after a certain number of attempts, be aware that in later versions of Oracle this invalidates the encryption of the password as opposed to disabling the User Account. This is not visible on the User Record so ensure your Support Team is given to tools to see this (SQL, Discoverer, an alert, or a modification to the form) and knows to try to reset the password first.

Cannot Export Data From Oracle

The user tries to export data and it gives the message “EXPORTING,” blips, and disappears every time. They call up frustrated asking where they can find their data.

What can Support do?

• Under Internet Options, change the Security Setting for File Download to “Enable”.
• The User will get prompted to save the file after the export is complete at which point they can select the name and path of the file.

ADI (Application Desktop Integrator) Sign-on Issues

There are three common issues here.

• The User needs to set up a Database or access something other than production.

• The User is unsure of what User Name and Password to enter.

• ADI/Oracle can not resolve TNS Names error message.

What does Support need to resolve this?

• Provide the Support Desk with a cheat sheet they can send to Users for setting up databases.
• Inform the Support Desk that ADI uses Oracle User Names and Passwords. This sounds simple enough but how else would they know?
• Ensure the Support Desk has a current copy of the TNSNames.ora and SQLnet.ora files and knows where they go on the PC. If your company has remote access setup, Support can quickly resolve the problem by places the files in the proper location. If not, provide dummy proof documentation they can forward to the Users.

Oracle is DOWN!

This is one of the biggest, most frustrating issues that Support must handle is one that they can not resolve. However, Support can keep the Users calm and informed.

How can Support help?

• Have a scheduled down-time? Let Support know ahead of time.
• Bringing the system down for an emergency? Provide Support with an anticipated up-time (if known) so they are prepared for the flood of calls and can answer them appropriately.
• Do you have alerts that inform your Database Analysts of system issues? Copy Support on them so they can truthfully say, “Yes we are aware of the problem and are working on it.” This will also allow key folks to start resolving the issue immediately without having to contact Support.
• Provide the Support Desk with access to Oracle so they can confirm if Oracle is down when a User calls. If you have multiple locations, this is especially important in identifying if it is an Oracle or network issue so they can route the call appropriately.

The front end always dies first so a User often discovers that the system is down prior to the alerts and IT finding out. Use your Support Center as the barometer and the face of IT to keep it cool when the system does go down. Nobody wants to hear, “Nope, I have not heard it is down.”

Down issues are usually smoothed out by two main communication streams from IT. Regular status reports if it is a long outage (more the 30 minutes for a User). Remember, while the IT department is running around going crazy, Users are sitting idly speculating—usually about how the IT Department is doing nothing.

Immediate communication when the system is back up. Everyone thinks less of IT when they find out on their own that a system is back up and they were not informed by the people fixing the problem.

Querying Data in Forms Does Not Return Expected Results

“I can’t find any invoice batches but John can.” “All the Purchase Orders disappeared since yesterday!”

How can Support resolve the problem?

There is a folder with a saved query. Train Support to:

• Identify if a folder is being used to confirm the problem (icon with name next to it).
• Walk the User though turning the folder off. Navigate to Folder > Save. Set Autoquery = Never, and Include query = No. Make sure your default folder does not include a query.

Errors Saving Work

These problems can vary. Support cannot resolve most of them but they can gather information and occasionally resolve them.

How can Support help?

• Get the ENTIRE error message, including ORA-APP 192478, and a screen shot if possible.
• Get the Responsibility name, Navigation Path and Form when receiving the error. Asking specific questions will get specific answers. This is important. Do not ask, “What are you doing?” as the answers will range from “My Job” to “There is a delivery on the dock that will not be unloaded until the back receivables are cleared.” Translation? Cutting a Quick Check from the Invoice screen. More specific questions are “What are you trying to do in Oracle?” or “What process are you using?” if you have formal documented procedures.
• Ask the User if they tried signing out, back in, and repeating the process. Yes, shutting down Oracle and starting it up again does work sometimes. Remember, it is just a computer. This serves two purposes. It clears the Cache and it tests if the problem is repeatable.

Concurrent Processes Error Out

Often times a concurrent process will error out because it is looking for something to be done prior to it completing. Oracle is getting better in the later versions about making these messages clearer to understand.

How can Support help?

• Give Support access to view All Concurrent Requests, especially the Log Files.
• Teach them how to read a log file.
• Do a search on “ERR.”
• Read from the end not the beginning. The errors are usually toward the end of the file.
• Have them read error messages to the User (e.g. Please Roll Back Depreciation). The User will know what to do from there if it is a functional error message. If not, they can contact their supervisor or the call can be passed on to the Support Analyst with the corresponding log file.

Interfaces

Third party interfaces into and out of Oracle are usually run on a set schedule. Ensure the Support Desk knows about this schedule and any upcoming changes to it.

Conclusion

So what does it really take to run an efficient Oracle Help Desk? A little training, a lot of listening, and good communication. Remember that it does not stop with your current Support Team but needs to be passed on to each new team member.
As in all areas of Oracle and complicated systems, take the time to document your company processes and system specific knowledge. Front Line Support exists to diagnose what the issue is—not solve it. Support can only solve an issue if they have the solution for it in their hands.

The more solutions you put in their hands, the more they can resolve on the first call. Here is a rule of thumb. If it is a common, easy-to-identify, and easy-to-resolve issue, it should be handled by Front Line Support. Take the time to document issues and train the Support Desk. Your customers, aka co-workers, will respect you for it and actually not dread calling the Help Desk.

How to Triage Your Oracle Support Calls

February 4, 2008 on 1:21 pm | by Melanie Cameron | In How To Guides | 1 Comment |  Print |  Email

Let me introduce Melanie Cameron as a guest author to this blog. Melanie is a consultant for MSS Technologies, specializing in Oracle Financials related to SOX, Process Improvements, Upgrades, Implementations, and Workflow. Based in Phoenix, Melanie serves on the Board of Directors of the AzOAUG and can be found at most Oracle events in the Valley of the Sun.

Does this exchange sound familiar? User: “Oracle doesn’t work!” Support: “It’s up. What’s the problem?” User: “I told you! It’s not working!!!”

I hear this all the time at client sites. Running a Help Desk is one of the toughest jobs in the company due to disparate systems, Users who are unfamiliar with what information Support Technicians need to know, and Support Technicians who are unfamiliar with the systems.

Underlying this is a constant level of tension because nobody calls support when everything is working great. There is ALWAYS a problem when Users call in and those problems prevent them from doing their jobs. Every User feels that their problem should be the Support Technician’s top priority.

So how does the Help Desk quickly identify level-one priorities, resolve basic issues, provide great customer service, and keep things running smoothly with minimal training? On top of that, how can they support an extremely complicated system and keep their customers (aka co-workers) happy?

There are a few keys elements to a successful Help Desk:

• Communication
• Empathy
• Training and Tools

Communication

Let’s get the obvious out of the way. If the Support Technician speaks Armenian and the User speaks English, they will not communicate effectively. But the communication issue goes deeper then this.

A User may call up and say, “ADI is not running on the new PC. It says TNS Names could not be resolved.”

If the Support Technician does not know what “ADI” means or what “TNS Names” is, how can they resolve the issue, or even route it to the correct team to resolve? The User may as well be speaking a foreign language!

Support must understand acronyms, the tools installed related to Oracle, and whether they are Server or Client-based. This way they can route calls properly, which will reduce confusion, frustration, and the time required to resolve them. Don’t make this list once and forget about! Rolling out a new product? Upgrading? Inform the Support Center of changes before they occur so Technicians can stay current.

To more efficiently pass off support calls, especially in larger support organizations, Support Technicians should have a reference guide of the basic tools, modules, and acronyms used with Oracle and the Support Analyst or Technical Person who will handle the call if it can not be resolved (ex. Frances gets Financials and John gets Purchasing).

Empathy

Users call Support because something they need to do their job is broken. They are frustrated. They are upset. They do NOT want to hear, “Log a ticket and someone will get back to you.” Users want to know that the Support Technician is listening to their problem, understands the issue, and is treating it with the importance it deserves. A better response is, “What is the problem? How is it affecting your job? What is your deadline? Is there another tool you can use while I route this to the person who can help you?”

Asking the aforementioned questions will enable both the Support Technician AND the User to understand what the required level of support really is and what urgency should be.

Training and Tools

Oracle E-Business Suite is HUGE and nobody knows it all. We all specialize in different areas and set boundaries. I have been doing this for 13 years and probably have another 15 more years before I retire. I will learn something new about Oracle every day until I walk out the door to retirement land.

But there are some basics in the system that Support Technicians can be trained on so that they can resolve these issues on the first call without added time and resources. Imagine what that will do for your department’s reputation outside IT!

Keep reading for Few Helpdesk Tips in part II of this article.

Few Navigation Helpers for New Oracle EBS Users

February 4, 2008 on 1:08 pm | by Mohan Iyer | In How To Guides | Enter Comments |  Print |  Email

I am sure there have been many times that you have sat down at your desk – intent on researching a couple of transactions that the auditor has asked you some questions on. You spend a couple of minutes trying to sift through what needs to be done and start. A little while later you are just a couple of steps into your task, but far from complete. This is a frustrating reality for many Oracle Applications users. They are capable of getting results, but most users are unaware of what to do to get to information quickly.The purpose of this (and other)posts will be to lead the readers down a path of understanding and help them learn tips and tricks that enable more efficient and effective use of the system. It is not intended as a discussion about how frustratingly unfamiliar or unfriendly Oracle E-Business Suite is. This paper is limited to Oracle Applications E-Business Suite, and primarily Financials with some aspects of Purchasing and Order Management. Though the key facets discussed should work in most other modules as well.

When you first log in to your “Home Page,” preferences settings are available in addition to your list of Responsibilities that you see for the first time.

The most interesting and useful feature in the preferences tab is the Date Format. I have heard so many people say that the Oracle Date format (dd-mon-yyyy) is terrible and they want to use their good old mm-dd-yy or dd-mm-yy date format. You can change your preference here. You can also change the way the numbers (amounts) appear on the data entry form, including your password.

You can also change it so that when you log in the first responsibility and form are set to be the one you use most. However, when you do this there are two problems(?) – you lose the Home Page, and if you want to change these setting you need to have a Web based form like the one that is in the ‘i’ products (iExpenses, iProcurement, etc….). This is not a major issue as almost everyone should have the Notification function on one of their Responsibilities, that can be used to change the setting back to default.

Once you log in to the application you see the Oracle forms - the good old forms screen with blue and dark grey colors after you log in. These are the default colors; if your System Administrator has not changed them. Once you reach these screens you see the Navigator - also called the menu list. You have a couple of icons on the toolbar that you may want to check out - the ones that are available for use - Change Responsibility, Print and Help.

In addition, you should see menu options on the toolbar menu - File, Edit, View, Tools, Window and Help. These are available throughout the applications on all the forms.

Take a close look at the following Menu Options as these are handy and are typically available from any form within the application:
Edit > Preferences - used to change password and view and set profile options at user level.

The other option that is used regularly is View à Requests to view concurrent programs that you have submitted and view the output and/or log.

One of the most overlooked is the Tools à Close Other Forms choice. This menu option allows multiple forms to be open within a given responsibility. This allows you to look at Customer, Invoice, and Receipt all together without closing each one of the forms.

The last menu option used also has some valuable choices - Help.

the Help à Record History and Help à About Oracle Applications choices are used very frequently by users to understand and locate generic data about the record and details about the module you in use. The Help à Record History is very crucial if you are trying to figure out what went wrong and would like to know who enterer or updated a particular transaction. This option shows you who created the data set (i.e. created by), when as well as who last updated it and when (last updated by). This information in transaction data entry forms helps when doing some research.

The Help à About Oracle Applications choice can provide you with beneficial information - instance you are using, User Name you are logged in as, Form Name that you are using currently and its version details. All these are very helpful when working with Oracle support for an issue.

The Diagnostics choice is one that, in most cases, you need the Apps password. Unless there is a technical request for you to perform, you will probably not use it. Also, the functions under the Diagnostics choice are very complex and need additional training. This discussion is outside the scope of this post.

On the navigator or the menu list there are submenus and functions. The submenus can be many times deep and to get to a specific function you may have to look for it, and this could be time-consuming. You can then use this option that is a big savior - CTRL-L on the menu form lists all functions available to a specific Responsibility.

I am sure there are many people that understand the Top Ten feature on the menu form and every user should configure this functionality when they start working with the system regularly. The hidden trick however, is that the number assigned to a Top Ten choice; can also be used as a short cut to open the function/form.

In many cases the system is notorious for giving inaccurate or inconsistent messaging, however, the status line for a given form is almost always accurate. The Status Line is shown at the bottom of the screen and in many cases is hidden by the toolbar (Windows toolbar). You can make it visible by clicking on the maximize icon on the top right hand corner of your applications form. Once the status bar becomes visible and messages there are quite useful - especially when you are stuck!

There are multiple tabs on the Navigator menu form - Functions, Documents and Processes. The functions tab gives and lists all the menus and functions associated with the menus for use in the normal working tasks on a day to day basis. The Documents tab can be used to place documents (single transactions) that you may be researching and need to get to quickly until you finish your research, you can have multiple documents (transactions) on the tab, but each is saved separately. The last tab is – Processes. It can be customized to be hidden or not available. The processes tab helps you walk through a process, e.g. Enter Journals through Posting, Reviewing Account Balances, Account Inquiry and Reporting.

The use of this set of functions is a longer discussion and is not covered as part of this post . There is more to come as it would be a little easier to make short posts.

Sample Ad-Hoc Queries for Oracle Financials

January 11, 2008 on 8:06 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is a compilation of several ad-hoc SQL queries I created over time to perform various analytical tasks withing Oracle Financials. I am by no means a SQL guru, I just know enough to keep myself out of trouble. These are simple queries, which at different occasions provided me with the information I needed. I am posting them here to find them again when I need them and to share them with those of you who may find them useful too.

Active Employees and Supervisors

Find a list of active employees and supervisors with their accounting detail. This is needed for instance when defining the AP approval limits for iExpenses.

Active Employees and Supervisors

AP Signing Limits with Employee Information

Extract AP Signing Limits with employee information.

AP Signing Limits with Employee Information

AR AutoInvoice

Analyze the AR Interface tables used by AutoInvoice.

AR AutoInvoice

Asset Categories and Lives

Get a listing of asset categories with the accounting and depreciation information.

Asset Categories and Lives

Asset Details Extracts

Extract asset detail information. Surprisingly, it is not easy to extract assets from Oracle Assets to convert them back to Oracle Assets.

Asset Details Extracts

Employee Email Address Query

Compare employee and user email information.

Employee Email Address Query

Employees and Users

Compare employee and user information. Find out which employees are not set up as users and vice versa.

Employees and Users

Employees with Default Accounting Information

Select employee information from the HR tables with the default accounting information.

Employees with Default Accounting Information

Employees, Organizations, Users and Responsibilities

Analyze by organization what users and responsibilities are set up for employees.

Employees, Organizations, Users and Responsibilities

Expense Report Template Details

Extract expense report template details.

Expense Report Template Details

Future-Dated Expense Report Items Query

Find future-dated expense report items entered incorrectly in the future. Prevent this problem by setting the respective OIE options.

Future-Dated Expense Report Items

HR Managers

Get a list of HR managers.

HR Managers

OIE Expense Report Import Query

Analyze project expense reports imported from iExpenses.

OIE Expense Reports

Update Asset Lives

Unfortunately, the Mass Additions process does not let you provide an asset life during the upload via FA_MASS_ADDITONS table. The life for converted assets always defaults to the life defined in the category. I believe this has changed in R12, but in prior release, you might need to use this script. As a workaround, we put the new life in ATTRIBUTE4 in FA_MASS_ADDTIONS, and updated FA_BOOKS table after the assets were created.

Update Asset Life

Update Email Preference

Update a default email preference.

Update Email Preference

How to Convert Legacy Expenditure Balances to Oracle Projects

October 12, 2007 on 3:58 pm | by Marian Crkon | In How To Guides | 2 Comments |  Print |  Email

Here is another piece on converting legacy balances into Oracle Projects. To see how to convert your legacy project revenue and invoice balances, check here.

Before you start the legacy cost data conversion process, your Oracle Financials and Projects modules need to be fully configured and your prerequisites created. When migrating from your legacy system to Oracle Projects, you may need to bring your ending legacy balances as your opening balances in Oracle. This will allow you to seamlessly manage and report on your project activity.

For the purposes of this article, let’s take for example a scenario when you go live with Oracle Projects on January 1, 2008, and let’s assume we need to convert legacy cost balances in summary. When we extract the balances from the legacy system, we would summarize them by project, task, organization, expenditure type, and date. The balances will be created and processed as miscellaneous transactions in Oracle Projects.

Step 1 (GL): Review your chart of account values. If needed, add new values for revenue accounts, companies, departments, products, etc.

Step 2 (HR): Define your project organizations. Classify organizations that will own projects as Project/Task Owning Organizations, and classify organizations that will incur revenue as Project Expenditure/Event Organizations.

Step 3 (HR): Include new project organizations to your project organizational hierarchy.

Step 4 (HR): Create your employees and employee assignments. Each project has to have a valid employee assigned as project manager in order to generate client invoices.

Step 5 (PA): Review and update project structures: project types, project templates, expenditure categories, revenue categories, expenditure types, lookup sets…

Step 6 (PA): Create your projects and tasks. Make sure to select a correct project type for each project, i.e. (Time and Material, Fixed Price, Cost Plus, etc.) You cannot change project type once you generated project costs.

Note:

If you are converting costs from multiple years, make sure to open prior periods (e.g. DEC-06 for 2006 costs) in Projects in order to determine correct PA and GL Dates! See more details on PA and GL dates in this article. If you do not open respective prior periods, transaction GL and PA dates will be assigned in the current opened period, and your project reporting may be inaccurate.

Step 7 (PA): Define your Transaction Source. Transaction sources identify the source of external transactions you import into Oracle Projects using Transaction Import. You can define a new the transaction source ‘Conversion’ to identify your legacy costs from from your legacy system. When you create a transaction source, you control the Transaction Import processing by the options that you select. Here are few recommended settings for some key options:

• Transaction Source - Define your transaction source, e.g. ‘Conversion’.

• Default Expenditure Type Class - The system uses the default expenditure type class that you assign to a transaction source if an expenditure type class is not specified in the interface table. Enter ‘Miscellaneous Transaction’.

• Raw Cost GL Accounted - Select this option to indicate whether transactions imported from this transaction source have already been accounted for in GL. Oracle Projects expects that the external system has already posted the raw cost to the appropriate debit and credit accounts. None of the Oracle Projects processes will transfer these costs to GL or AP. If you enable tis option, you will also need to provide valid debit and credit code combination IDs. When you select this option, the Import Raw Cost Amounts option is automatically selected.

• Import Raw Cost Amounts - When a transaction source has this option enabled, the raw cost amount of the transactions has already been calculated (costed) in your external system. None of the Oracle Projects processes will calculate raw cost amounts for these transactions.

• Import Burdened Amounts - When this option is selected for a transaction source, Oracle Projects expects the external system to provide burdened costs. If the transaction does not have a burdened cost amount, Transaction Import will reject the transaction. When you select this option, the Import Raw Cost Amounts option is automatically selected.

• Allow Duplicate Reference - Enable this option to allow multiple transactions with this transaction source to use the same original system reference. If you enable this option, you cannot uniquely identify the item by transaction source and original system reference.

• Allow Interface Modifications - This option allows you to modify rejected transactions in the Review Transactions window after the import process is completed.

• Purge After Import - If you select this option, items successfully imported from the transaction source are automatically purged from the interface table when the import process is completed.

• Allow Reversals - If you enable this option, Oracle Projects allows reversals of expenditure batches or expenditure items for the transaction source. When you enable this option, the Allow Adjustments option is automatically enabled. Disable reversals for legacy balances, which should not change, i.e. balances posted in prior periods, and enable it for balances, which need to be processed as “new transactions” in Oracle Projects, e.g. unbilled billable transactions or uncapitalized capitalizable transactions.

• Allow Adjustments - If you enable this option, you can adjust imported transactions in Oracle Projects after you load them via Transaction Import. Disable adjustments for legacy balances, which should not change, i.e. balances posted in prior periods and enable it for balances, which need to be processed as “new transactions” in Oracle Projects, e.g. unbilled billable transactions or uncapitalized capitalizable transactions.

• Processing Set Size - Enter the size of the processing set. The value entered indicates the number of records to be processed in each set. When interfacing large amounts of data, you can reduce the impact of unexpected errors by processing transactions in sets. The import process issues a database commit after each set is complete. If an error occurs and a rollback is issued, only the transactions in the current set are affected.

• Effective Dates - make sure your ‘From’ effective date is before your oldest expenditure item date you intend to upload.

Step 8 (PA): Disable cost transfer to General Ledger. Navigate to the Implementation Options screen and disable the Interface Usage Costs flag. This will prevent your legacy cost balances from being interfaced to Oracle General Ledger since the assumption is they were already posted by your legacy system.

Step 9 (WebADI): Create a WebADI integrator for Project Transaction Import. You can either use one of the seeded Project Integrators, or define your own, which matches your Transaction Source.

Step 10 (PA): Upload legacy cost balances as miscellaneous expenditure transactions. For high volume uploads, create a custom script and upload your data directly into PA_TRANSACTION_INTERFACE table. For medium-size uploads of several thousands records, use the WebADI Upload spreadsheet you created in Step 9 above.

Step 11 (PA): Run Transaction Import. When using WebADI, you can submit this step during your upload. Oracle lets you fix any rejections either in your upload spreadsheet, or in the interface table using the Review Transactions form.

Note

One comment about Transaction Import Status. Even though you can correct your most of your rejections in the Review Transactions screen, Oracle Projects does not let you change transaction status from Rejected (R) to Pending (P) in the Projects Transactions Interface table. Use this Set Transaction Import Status script to re-set the status.

Step 12 (PA): Distribute transaction costs. Run the PRC: Distribute Usage and Miscellaneous Costs process to calculate the amounts and generate account distribution lines. Make sure to run this process with the right period open and the right Through Date to assign correct PA and GL dates to your legacy transactions.

Step 13 (PA): Run the interface to General Ledger. Run the PRC: Interface Usage and Miscellaneous Costs to General Ledger process. This will set the legacy cost balances to look like they are interfaced to GL. If you disabled the GL Transfer option in Step 10, no journal entries will be created. Alternatively, you can run the PRC: Submit Interface Streamline Processes with option ‘DXU: Distribute/Interface Usage and Misc. Costs to GL’ to complete steps 12 and 13 together.

Step 14 (PA): Turn back on the GL interface you disabled in Step 8. Navigate to the Implementation Options screen and enable the Interface Usage Costs flag.

And that’s it. The exact sequence of steps will, of course, depend on your company’s specific business. Hopefully, this article provided a good starting point for understanding this process.

How to Change GL Calendar without Reimplementing Your Oracle Financials

October 5, 2007 on 8:02 am | by Marian Crkon | In How To Guides | 18 Comments |  Print |  Email

We had a pressing business need to change our corporate calendar from a fiscal year ending on March 31 to a calendar year ending on December 31 to be consistent with our parent company, which acquired us this year. Oracle’s recommendation in this situation is to create a new set of books and use the consolidation feature to move account balances from the original set of books to the new set of books. This assumes that the periods that need changing already have data or have a status other than Never Opened which is usually the case.

Creating a new set of books, however, becomes a big problem if General Ledger is not the only application you use. It means creating new operating units and replicating configurations in all your sub-ledgers. It also means supplier and customer sites and all open purchase orders, sales orders, invoices and other pending transactions must be converted. Basically, we are talking about re-implementing your system.

You Are Not Alone

Many other businesses find themselves in a similar situation and have no time or desire to re-implement their system to accommodate this request. The good news is that Oracle Support acknowledged this pressing need and came up with a couple of recommendations.

After reading the Metalink note 102460.1 and OAUG white paper on this subject, we were encouraged there was another way. Changing the GL calendar without defining a new SOB saved us from re-implementing our sub-ledgers in three operating units, which used the current set of books and calendar. The system allows you to make changes to the existing calendar when you meet the following conditions:

• All the periods are in a status of Never Opened.

• A budget year that includes the periods to be changed is no to yet opened.

• An encumbrance year that includes the periods to be changed is not yet opened.

• The periods to be changed have not yet been copied from the GL Calendar to Project Accounting.

The status of the periods for all applications is maintained in the gl_period_statuses table. There is a record for every period for every application. The status in this table of the periods being changed must be ‘N’ for Never Opened for every application. Most subsidiary financial applications can control their own periods and it is maintained in that table. The exception is Project Accounting, which in addition to having its period statuses maintained in the gl_period_statuses table, it also stores its project status information in the pa_periods_all table.

Looking at our GL_PERIOD_STATUSES table, there were future periods that were not in ‘Never Opened’ status in GL, Projects and Payables applications. In Projects because of the way the standard functionality copied the whole calendar from GL to PA. Luckily, we did not have any budgets or encumbrances posted to those future periods.

Here Is How To Do it

We did the following to change our existing corporate calendar:

In General Ledger

• Identified the future periods in calendar, which need to be redefined.

• Updated period statuses to ‘Never Opened’ for all applications and applicable SOBs.

• Identified and deleted any GL Balances in future periods.

• Re-defined our corporate calendar. In our case that meant defining three adjusting periods in year 2007 to make 2007 a short year ending with Dec-07, and re-defining future periods to match the calendar year. (You can use this DataLoad to do so).

In Projects

• Deleted (old) future periods (beyond Jan-08) from Projects in all operating units
• Copied newly-defined GL periods from GL to Projects


Remember, you should not use this solution if any of the periods you wish to modify are in use. This means if the period has a status of something other than Never Opened, or if the period is in an open budget year or open encumbrance year you will not be able to change it on the form.

Fixed Assets

We use use Oracle Assets with asset calendar starting on April 1, 2001. We had about 2,000 assets in our corporate books, which are associated with the set of books and the fiscal calendar ending on March 31. After considering several alternatives including not doing anything, we decided it was best to create a new calendar to be consistent with General Ledger. We couldn’t simply adjust the current calendar. We need to create a new asset calendar.

After creating a new asset calendar we also had to:

• Create new Corporate Books. (Manually)
• Create a new Prorate Conventions. (Manually)
• Assign Asset Categories to new that were linked to the new corporate book. (DataLoad)
• Extract and upload all active assets to the new corporate books. (SQL script to extract, and ADI to upload)

Test, Test and Test Again

To test the modified calendar, we did the following:

• Closed all 2007 periods in all sub-ledgers and GL through Dec-07
• Created transactions in Dec-07 in all sub-ledgers, including POs, invoices, expense reports, payments, INV transactions, AR transactions, cash receipts, timecards, project adjustments, and assets additions and retirements.
• Ran depreciation for Dec-07.
• Closed Dec-07 in all sub-ledgers and GL.
• Opened Jan-2008 all sub-ledgers and GL and “rolled into” the next fiscal year.