Oracle Approvals Management (AME) is a self-service Web application that enables users to define business rules governing the process for approving transactions in Oracle Applications .This enables development of custom rules to manage transaction approvals that is integrated with multiple Oracle EBS applications such as HR, PO, AP and UMX.

If you want to migrate the AME setup like rules,conditions,approval groups etc. from one instance to another instance, you can use some of them by mean of FNDLOAD utility. The process is similar to what we are regularly using for other AOL Objects.

FNDLOAD will have such ldt file through which you can use.

You should read the lct first before using any FNDLOAD Command. In the lct, there is an Entity section, where the Entity details are mentioned.Newcomer in OracleApps if you are not familiar with lcts, i would suggest you to go through the DOWNLOAD section and find the bind variables, which are mandatory.

Here are the details steps for each setup’s availability till date.

Read the rest of this entry »

Previous Post:

• Are seeking auditing ability in EBS…use ‘AuditTrail’

Steps by step: Enabling audit trial

As per the below example the ‘Define An application user’ is a user table name for FND_USER,the same steps you can follow for your own tables.

Read the rest of this entry »

What is an AuditTrail?

An AuditTrail is one of functionality for retaining a history of changes to data. What ,who and when can be identified on a particular table or column if the functionality is enabled.

When you enter or update data in your forms, you change the database tables underlying those forms. An audit trail tracks which row in the database was updated at what time, and which user was logged in using the associated form(s).

If you are seeking auditing ability to track changes on a particular table of Oracle this post might helpful to you.

Enabling the Functionality of AuditTrail

You can turn AuditTrail on or off (Yes or No). Normally the default setting is No (Off). When you enter or update data in your forms, you change the database tables underlying the forms you see and use. AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s). Also..

• Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
• AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
• All the forms that support AuditTrail are referred to as an audit set. You should also note not all forms may be enabled to support AuditTrail.
• To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developer responsibility.
• Users cannot see nor change this profile option.
• This profile option is visible and updatable at the site and application levels.

 

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

Setting Up AuditTrail(>11i )

You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

The steps for setting up AuditTrail include:

Yuu need to verify Select Privileges on SYS.DBA_TABLES

Have your database administrator grant SELECT privileges on SYS.DBA_TABLES to the APPLSYS account. Normally, this step would Normally taken care during the installation of Oracle.

Define Audit Groups

This is very very important.These are groups of tables and columns, where you do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables see at the end).

A given table can belong to more than one audit group. If so, the table is audited according to the highest “state” of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.

Navigation: Security -> AuditTrail -> Groups

Define Audit Installations

You choose the registered Oracle IDs at your site that you want to audit. This allows you to audit across multiple application installations. When a table is added to an audit group, auditing will automatically be enabled for all installations of the table for which audit is enabled.

Navigation: Security -> AuditTrail -> Install

Run the Audit Trail Update Tables Report to Enable Auditing

Your AuditTrail definitions (and auditing) do not take effect until you run the Audit Trail Update Tables Report. If you change any of your definitions later, you must rerun this program. You run the Audit Trail Update Tables Report from the standard submission (Submit Reports) form.

Audit Trail Update Tables Report

This program creates database triggers on the tables in your audit groups for your installations. It also creates shadow tables, one for each audited table, to contain the audit information. If you have changed your audit definitions or disabled auditing for an audit group, the program drops or modifies the auditing triggers and shadow tables appropriately.

The program also builds special views you can use to retrieve your audit data for reporting.

You can check SQL*Plus to see if the Shadow Tables have been created or not. Shadow Table name is the same 26 Characters of the Table being audited followed by a suffix of “_A” ,suffix of “_AI” for Insert Triggers, “_AU” for Update triggers , “_AD” for Delete Triggers,suffix of “_AIP” for Insert Procedures “_AUP” for Update Procedures and “_ADP” for Delete Procedures.

AuditTrail Limitations

These are limitation of AuditTrail:

• Your table should consist of maximum 240 columns
• You cann’t use the column for audit trail whose data type is LONG, RAW, or LONG RAW
• Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically.
• Once you have added a column to an audit group, you cannot remove it.
• AuditTrail requires two database connections. If your operating platform does not automatically support two database connections
  (e.g., VMS or MPE/XL), then add the environment variable ‘FDATDB=<database connect string>’ to your environment file.
• Because the structure of the audited table may change between product versions, AuditTrail does NOT support upgrading existing shadow tables or audited data. Before an upgrade, you should archive the shadow tables and perform all necessary reporting on the audited data.
• Oracle recommended Disabling AuditTrail feature Prior to Upgrade to higher version.
• If your plan is to use some auditTrail on table which name is bit longer(> 26 characters), you can’t achieve this. As there is Bug(#3872242
  ) reported by Oracle that restrict you from this, as AuditTrail Update Tables Errors out on audited tables that are > 26 characters.

Sometime back we found while enabling this on these tables.

• PO_REQUISITION_HEADERS_ALL
• PO_REQUISITION_LINES_ALL
• PO_REQ_DISTRIBUTIONS_ALL

What are the primary Auditing Tables?

• FND_AUDIT_COLUMNS
• FND_AUDIT_GROUPS
• FND_AUDIT_SCHEMAS
• FND_AUDIT_TABLES

Client Dilema : HOW TO ENABLE AUDITING AT “FORMS” AND “USER” LEVEL TOGETHER

Customer has set the profile “Sign-on: Audit Level” to FORMS to collect information about the forms sessions at a particular time .
By default the value for this profile was USER. Since he has set the value to Forms can he collect the User related information

Yes you can …
If the profile “Sign-on: Audit Level” is set to FORMS then it will collect Form sessions information in addition to user session information.
So you will get information related to User session as well as forms session. There is no harm in setting this profile value to FORMS.

We knows ERP is critical for any company’s operation.Once ERP is live , there are n number of transaction happen every hours ,days depending upon how big is your ERP operation. The identification of tool and setting helpdesk is most important task for management and IT .Any deviation from meeting deadlines may hit your operation , thus it is very important to understand the need and evaluate some of tools to cater the need.

Any ERP is meant for three kind of user community some of them purely subject wise user(Super User) also called process owner , end user(Data entry) who enters the data , never bother what is happen inside and third supporting IT group for assisting both of them to best use of tool as they know the process are alien with product processes. Typical characteristic of tools which is required for handling issue management should must these criteria:

• Manage work orders from submission to resolution.
• Create work order tickets and assign to technicians automatically.
• Know where each ticket is in the resolution process and ensure no ticket slips through the cracks.
• Improve responsiveness by enabling your customer to submit work orders and obtain status via standard Web browsers, 24 hours a day, 7 days a week.
• Track and enforce your Help Desk processes.
• Report and measure the effectiveness of your Help Desk processes.
• Create and maintain search able knowledge base (frequently asked questions) to allow customers to help themselves.
• The result is streamlined Help Desk, increased customer satisfaction, meeting SLA’s, and higher productivity and lower cost.
• Merge geographically or organizationally separate help desks into a single point of control
• Identify business services affected by incidents and problems to prioritize resources accordingly

Lets evaluate some of Products options for Issue Management tools

1. Open view

This is one of great tool provided by HP , this will monitor system failures and perform a pre-determined set of actions to resolve the problem. The good things is that it will support for problem management process is unparalleled (root cause analysis, trend analysis and performance reporting capabilities are unmatched).

2. Peregrine Service Center

This is yet another tool provide by vendor Peregrine , it is typically a administrative tool that end users and support personnel use to manage help desk trouble tickets.

3.CA Unicenter Service Plus Service Desk

This is another tool provided by vendor CA, have been observed if the user base is big and operated into different geography, this is one of great product to cater all the need.These are highly scalable across complex IT environments.

4.Microsoft Excel based application

This is typically some sort of excel spreadsheet normally kept on a LAN with columns for SIR, priority, assigned to, status, and resolution.

This good for such kind of this is Simple to create. Everyone has access but the bad is hang problem if someone is updating. Good for small or medium sized company to use such kind of tools.

5.Remedy IT Service Management

This is provide by vendor Remedy which is now part of BMC software.This with Change and Configuration Management this product will provide best practice processes to streamline and automate key activities to identify, respond to, and control changes.This Product also covers ITIL Change Management, Release Management,and Configuration Management like:

• Identify — IT environment related to critical business services
• Respond — to changing business and ITrequirements
• Control — all business critical configurations in the IT environment

Wide variety of reporting requirements varies from environment to environment and business to business.This Article is going to Focus on the some of reporting tools which is normally used in typical 11i environment.

Reporting Tool Functionalities

Data is totally useless until it does come in presentation layer, with proper security . Hence its is important to have such tool with great capability, and tools must have these features:

Reporting Tool overview with respect to EBS Suites

• Brio ONE
  The Brio ONE solution is specifically designed to enable everyone in the extended enterprise to fully realize the promise of e-business by delivering dynamic, reliable, easy access to real-time business information, regardless of where that information is stored or of the user’s location. The Brio ONE solution enables your organization to build and deliver business intelligence, enterprise reporting, self-service information exchange, and analytic applications to the broadest possible range of users in client/server, Web-based, and hosted application environments-all with unmatched ease of experience and scalability.
• Business Objects
  Streamlining operations, reducing costs, increasing revenues, improving customer satisfaction… These are just some of the measurable benefits that customers of Business Objects have realized directly by implementing e-business intelligence (e-BI) solutions from Business Objects.
  Whether they need to access prepared information via a wireless portal or perform sophisticated analysis on a networked PC, BusinessObjects can help you offer your employees, customers, partners, and suppliers a set of easy-to-use tools to turn information into knowledge into profit.
• NoetixViews
  NoetixViews enables super-users to develop ad hoc reports from Oracle Applications data. NoetixViews takes the information in your Oracle Applications database and repackages it in a set of user-friendly, plug-and-play business views that provide relevant information on a given business topic. With these views, users can query their data using common business terms and client/server query and reporting tools, without dealing with raw tables. With typical installations requiring less than one day of training, NoetixViews dramatically reduces specialized consulting costs and IT involvement.
• Oracle Discoverer
  Oracle Discoverer is an intuitive ad hoc reporting tool. It enables a developer or knowledgeable end user to create reports with impressive formatting and attractive exporting and publishing functionality. Oracle Discoverer empowers your user population by placing business and decision critical data at their fingertips.
• Business Intelligence System (BI)

Oracle BI tools is a set of tools to provide high level information for the managers (decision makers) to run their business. The information this tool provides helps managers to take the right decision with the daily data that is upload on their systems.

• Hyperion

Hyperion is a Business Intelligence (BI) and Business Performance Management (BPM ) Tool. Its a market leader in Financial, Operational and Strategic Planning. It is having applications for Planning, consolidation, scorecarding, reporting, dashboards, Analysis, Workspace, Master Data Management and Foundation.

• GL wand

Gl wand is a formula driven approach to reporting on Oracle General Ledger data using Microsoft productive tool like Excel.GL Wand will be used for monthly Balance Sheet reconciliations and account balance checks,i.e. Trial Balance. This tool is bit popular in UK.

For more information, adviced to look at the product company website for latest updated info.

1. Brio ONE http://www.brio.com
2. Business Objects http://www.businessobjects.com
3. NoetixViews - http://www.noetix.com
4. Information gathered from http://www.oracle.com
5. Hyperion - www.hyperion.com
6. Glwand - http://www.excel4apps.com