The Feature

Sample DataLoad Templates for Oracle Financials

August 25, 2010 on 9:13 pm | by Marian Crkon | In How To Guides | 5 Comments |  Print |  Email

Let me publish some sample DataLoad templates for Oracle Financials, along with their descriptions and navigation instructions. Note that these are only samples. You may need to modify the spreadsheets based on your application configurations. The blue columns represent DataLoad commands, the yellow columns is where you paste your data. Hope you will find the templates useful. Find more information and spreadsheets about DataLoad here. Share your own experiences with uploading and maintaining data in Oracle Applications as comments below.

Create GL Code Combinations

Define new accounts by creating new combinations of account segment values. You must define all new accounts manually if you do not allow dynamic insertion to create new accounts automatically (most people have it on), or if you need to pre-define your account combinations for data conversions [e.g. employees, assets, invoices, etc.].

Navigation: General Ledger Super User: Setup > Accounts > Combinations

Start Field: Account

DataLoad: Create GL Code Combinations

Create Employee AP Signing Limits

Create employee signing limits for expense report approvals. Managers can approve an expense report only if the total amount of the expense report does not exceed their pre-defined signing limit. The Manager (Spending) Approval Process in the Expense Reporting workflow uses the signing limits to determine which manager has authority to approve expense reports. When you assign signing limits to a manager, you also specify a cost center to which this signing limit applies and you can give managers signing limits for multiple cost centers.

Navigation: Payables Manager: Employees > Signing Limits

Start Field: Document Type

DataLoad: AP Employee Signing Limits

Create AP Expense Report Template

Define your Expense Report Template based on the expense report forms your company uses. You can define default values for expense items and you can choose those items from a list of values when you enter expense reports. During Expense Report Import, Payables uses the expense item information to create invoice distributions. If your employees will be entering project-related expense reports, you need to define project-related expense items and associate them with Oracle Projects expenditure types.

Navigation: Payables Manager: Employees > Signing Limits

Start Field: Document Type

DataLoad: AP Expense Report Template

Create Standard Suppliers

Create suppliers in Oracle Payables.

Navigation: Payables Manager: Suppliers > Entry

Start Field: Supplier Name

DataLoad: Create Suppliers

Create 1099 Suppliers

Create 1099 suppliers and their tax reporting options.

Navigation: Payables Manager: Suppliers > Entry

Start Field: Supplier Name

DataLoad: Create 1099 Suppliers

Create Customers

Create customers in Oracle Receivables. You can enter as much or as little information in the customer accounts as you want. The only information that is required for a new customer account is a party name, account number, and address.

Navigation: Receivables Manager: Customers > Standard

Start Field: Name

DataLoad: Create Customers

Delete AR Transactions

Use the Transaction window to delete the payment schedule by choosing the incomplete button in the and deleting the record. We used this method to remove legacy project invoices, which were generated in Projects (in order to create opening bill balances), and did not belong in Receivables.

Navigation: Receivables Manager: Transactions > Transactions

Start Field: Source

DataLoad: Delete AR Transactions

Create FA Categories

Create your asset categories. Category information is common for a group of assets. Oracle Assets defaults these depreciation rules when you add an asset, to help you add assets quickly. If the default does not apply, you can override many of the defaults for an individual asset in the Asset Details or Books windows. You have to set up default values for each category in each book. The default depreciation rules that you set up for a category also depend upon the date placed in service ranges you specify.

Navigation: Fixed Assets Manager: Setup > Asset System > Asset Categories

Start Field: Category

DataLoad: Create FA Categories

Assign Existing FA Categories To a New Book

Assign the existing asset categories to a new asset book.

Navigation: Fixed Assets Manager: Setup > Asset System > Asset Categories

Start Field: Category

DataLoad: Assign FA Categories To a New Book

Set GL Set of Books Name Profile Option

Set the GL: Set of Books Name profile option for all your responsibilities in the multi-org environment.

Navigation: System Administrator: Profile > System

Start Field: Responsibility in the Find System Profile Values. (Make sure to enable the flag.)

DataLoad: Set GL Set of Books Profile Option

Set MO Operating Unit Profile Option

Set the MO: Operating Unit profile option for all your responsibilities in a multi-org environment.

Navigation: System Administrator: Profile > System

Start Field: Responsibility in the Find System Profile Values. (Make sure to enable the flag.)

DataLoad: Set MO Operating Unit Profile

Set OIE Profile Options

Set the iExpense profile options to match you business requirements.

Navigation: System Administrator: Profile > System

Start Field: Profile

DataLoad: OIE Profiles

Find and Create Document Sequences

So today, I had to figure out which sequences existed and which ones had to be added for a new set of books. No rocket science but still a little messy exercise. I ended up with an ad-hoc query and a couple of DataLoads. Here they are for future reference:

• Create Document Sequences DataLoad
• Create Document Sequence Assignments DataLoad
• Select Document Sequence Assignments Query

How To Capitalize Legacy CIP Costs in Oracle Projects

August 10, 2010 on 8:55 pm | by Marian Crkon | In How To Guides, Ideas and Opinions | Enter Comments |  Print |  Email

Here is a quick summary of steps to convert legacy CIP costs on capital projects and capitalize them into assets. The instructions are applicable to both: Oracle EBS 11i and R12. This article assumes that you have already configured all prerequisites in your Oracle Project Costing and Fixed Assets. You will need Project Costing Super User and Fixed Assets Manager responsibilities, or the customized versions there of.

Define Capital Projects

Let’s start with defining the project template. This is where you can leverage a lot of good Oracle functionality to your benefit.  The details will vary in every instance, but in general, you need to complete the following steps:

PA1: Define Capital Project Template - Header

Navigate to Setup > Project > Project Templates to create a new template. Define the header-level information including the Number, Name, Project Type (make sure it is a Capital  project type class - you won’t be able to change it later), Organization, and Description.  If you want project created from this template to have a fixed duration (e.g. 12 months), define the Trans Duration. The system will populate the Project Finish dates based on the Project Start date and this duration for new projects.

PA2: Define Capital Project Template - Quick Entry Fields

Click on Setup Quick Entry. Define the fields for quick entry of projects. These are the “bare minimum fields” to enter when you copy a template into a project.

PA3: Define Capital Project Template - Tasks

Click on the blue box next to the Tasks option (I know, it not very intuitive), or click on Detail (while the cursor is on Tasks). Define your tasks, or work breakdown structure (WBS). Also determine which tasks should be capitalizable or not, i.e. can collect CIP costs to become fixed assets.

PA4: Define Capital Project Template - Define Project Assets

In this step, you can pre-define fixed assets you anticipate to complete as part of projects created from this template. Basically, you do here what you’d normally do in FA Mass Additions. You can use the Create Project Assets DataLoad to upload a higher volume of assets…

PA5: Define Capital Project Template - Assign Project Assets

Depending on your implementation approach, you assign default assets at project or task level. This functionality determines how the capitalizable expenditures will be assigned to future assets. In order to automate this, you probably want to assign assets at the task level so that you have one-to-one relationship between the costs and the assets in your tasks. The other options would be the costs in multiple tasks to be summarized into one asset at the project level… Refer to the Asset Summary and Detail Grouping Options in the Oracle Project Costing User Guide. You can use the Create Task Level Asset Assignments.

PA6: Define Capital Project Template - Create Task Transaction Controls

Following the example of the task-level asset assignment we defined in step PA5 above, it is critical, that the right CIP costs get collected in the right tasks. In other words, we want building-related costs be collected in building-specific task to capitalize them into a building asset assigned to the same task. To prevent invalid entries, you can create Transaction Controls. These are, basically, conditions that determine what expenditure types or categories are allowed in what projects or tasks. Again, use this sample Create Task Transaction Controls DataLoad to prepare and execute your upload. The trick will be finding the right tasks based on their WBS level. And one note about the Transaction Controls: make sure the Control Start Date and End Date are consistent with your Task Start Date and Finish Date.

PA6: Define Capital Project Template - Set Task-Level Capitalizable Flag

This is an optional step, and this should be taken care of by your Transaction Controls. However, you can make all expense tasks non-capitalizable by setting the Capitalizable flag to null… You can use this Update Expense Task Capitalizable Flag DataLoad.

PA7: Create Projects

Using the template created in steps PA1-6, create your capital projects. The quick entry fields will vary depending on your template, but as always, you can use the Create Projects DataLoad.

PA8: Upload Legacy CIP Costs

Depending on your conversion strategy, you can use a custom conversion program, or WebADI spreadsheet to upload your legacy CIP costs. Check this article for detail on how to convert legacy costs and how to resolve a couple of “features” during the Transaction Import.

PA9: Update the Project Status Inquiry Amounts

As Project Costing Super User, run the PRC: Update Project Summary Amounts

PA10: Complete Asset Definitions

Based on your implementation strategy and business process, you might be required to complete asset definitions in Projects before asset lines can be generated and interfaced to Oracle Assets. As Project Costing Super User, navigate to Capitalization > Capital Projects. Query up your project(s), click on Assets > Asset Details. Complete the asset definitions, just like you would in the Prepare Mass Additions.

PA11: Generate Asset Lines

As Project Costing Super User, run the PRC: Generate Asset Lines for a Range of Projects process. Watch the Date In Service parameter and PA Date parameter to make sure you select the right lines to generate.

PA12: Interface Asset Lines to Assets

As Project Costing Super User, run the PRC: Interface Assets to Oracle Assets process. It will create mass additions in FA MASS ADDITIONS table.

FA13: Post Mass Additions

If you required complete asset definition in Projects, your mass additions are ready to be posted. If asset lines are incomplete, you need to complete them in Prepare Mass Additions. In any case, once ready, log in as Fixed Assets Manager and navigate to Mass Additions > Post Mass Additions to create assets.

At this point, your legacy CIP costs were capitalized into fixed assets. Obviously, this summary is  simplified; refer to the Oracle Project Costing User Guide and Oracle Assets User Guide for more details.

How to Create Custom WebADI Integrators

November 11, 2008 on 10:40 pm | by Marian Crkon | In How To Guides | 8 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