OracleApps Epicenter

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.

MOAC allows users of a single responsibility to perform task in multiple operating units without having to switch responsibilities.Out of Box functionality of MOAC, there is something called “Multi-Org Preferences”.

What is “Multi-Org Preferences”?

Multi-Org Preferences allows you to control the list of operating units to which you have access.

Lets say you have around 20 OU’s divided into three major Region (North America, Europe and APAC). Lets say within APAC region you are responsible for all 7 OU’s within Region.

Read the rest of this entry »

As we know in R12 , the existing Multiorg architecture includes a new feature Multiple Organizations Access Control (MOAC). Lot of queries and question were asked by my readers, therefore this post completely focus on some of technical Insight because of architectural change.The minimum you should suppose to know is that Access Control feature has backward compatible. Technically means that there are no code or procedural changes if MOAC is not implemented (i.e. The user is assigned one operating unit for a responsibility).

Read the rest of this entry »

What is Report Manager?

In Oracle EBS “report manager” is a product that has been offered to manage your reporting needs.

Report Manager is basically a secure point-in-time report repository offering centralized management and distribution of reports.

There are different levels of access that you can assign to reports that are generated by the Report Manager.

You can tie these access rights to your department assignments, to employees, or can build your own.

The report manager reports are stored in the database and can be published to a wider audience. The good is that you can manage access/availability to multiple groups of people. This is run as a menu option from the main menu of a specific application (AP, AR, etc.).

Report Manager benefits

• You can access business information from a central site using a standard browser
• You can provide that reports are with proper access
• You can store and distribute ANY type of report or file
• Preview reports prior to mass distribution

R12 Report Manager

The Report Manager seeded functions need to be assigned manually by the System administrators to appropriate responsibilities based on the types of reports a user submits and publishes in their job. For example, a system administrator might assign the Financial Report Template Editor and Financial Report Submission functions to an existing General Ledger responsibility already used for submitting Financial Reports by General Ledger users.

The seeded Report Manager functions are:

• Standard Report Submission
• Security Workbench
• Repository Management
• Financial Report Submission
• Upload File
• Variable Report Submission
• Financial Report Template Editor
• Ad Hoc FSG Submission
• Define Report Set Publishing Options
• Report Set Presentation Options
• Define Request Set Publishing Options
• Request Set Presentation Options

Release 12, ADI & Report Manager

In Applications Release 12, WebADI and Report Manager must be used instead of ADI, as client/server ADI is not supported for that release. Also in Release 12 the WebADI functionality for GL is integrated into the standard GL menu structures. These functions are ‘Launch Journal Wizard’, ‘Launch Budget Wizard’, and the spreadsheet upload of Currency Rates, via ‘Currency Rates Manager’.

When To Use Report Manager(Implementation consideration)

This is very important to understand when offering to customer for this product. Oracle Report Manager you need to:

• Provide a centralized report distribution system for point-in-time reports.
• Submit and publish Financial Statement Generator (FSG), Ad Hoc FSG, Variable Format, and Standard reports to a central repository.
• Present reports in the repository to information consumers via menu items on the Oracle E-Business Suite Home page or portal.
• Approve reports before making them available for general viewing.
• Permit report availability in a future time frame.
• View spreadsheets or PDF files for further analysis.
• Secure reports by utilizing three security paradigms: User to Value, custom, and flex field segment.
• Archive reports for future reference that no longer need to be displayed to report consumers.
• Publish reports that have been submitted as part of request sets or report sets.
• Upload any type of file and publish it to a central repository.
• Reports in the repository can then distributed to the Oracle E-Business Suite home page.

This is yet another extension of previous post, will discuss how to manage and define layout of the WebADI integrator.

Managing/Defining Layout

You can also manage /Define layouts, means you can build the spreadsheet by modifying the Layouts.

You must be in responsibility with access to the integrator to define the layout.

• Responsibility: Desk Integration
• Menu Options : Define Layout

Read the rest of this entry »

As mention in earlier post (#1,#2,#3 ) , WebAdi is tool that can be used to load many different types of data from spreadsheet based excel application into various application of Oracle APPS. There are number of Seeded Integrator comes with Oracle, but still Oracle provides the capability to its expandability. Various Integrators to load different types of information. However, you can do setups new, custom spreadsheet can be created to load different types of information into Oracle.

Basic User Procedure we have seen for a webADI is

1. Use the webADI page Consist of creating spreadsheet by
   1. Specific to data type which we called as integrator
   2. Specific to Layout which is for controlling purpose of the spreadsheet.
2. Adding Data to the excel Spreadsheet.
3. In spreadsheet, you need to select the Oracle/Upload menu option.This is menu option that you can use from WebAdi added from Excel menu.
4. Than the data get uploaded to Oracle target table, which might be custom, or base. Any error will be flagged and the error reason will be placed on corresponding row in the spreadsheet.

Read the rest of this entry »

In last two post we have seen , Web ADI is a Browser based application, which enables users to log into the Oracle database and upload ADI spreadsheets, without having the ADI application installed onto the users machine.We have already seen step by step setup in 11i , now this post is extension of previous post with a case of R12.

1. Responsibility: As mention earlier WebADI User linked to the correct Set of Books for your business. In R12 , the starting point would be responsibility called Desktop Integration

Read the rest of this entry »

This post will briefly Let you take quick step by step guide for uploading Journal through WebADI in 11i .

Uploading a Journal using Web ADI

The Web ADI screens are viewed in sequence, each one selecting an aspect of Journal to be uploaded.

• It start with selection of the ‘Viewer’ the Excel version to be used to view the sheets into which the Journal data will be entered.
• Then followed by selection of the Integrator, which is the type of form to use to upload.
• After that the selection of Layout.. this is the type of type of Enter the Content, of the Journal
• The end of process consist of reviewing the details that you have entered prior to submitting the upload.
• and Finally uploading the Journal

Read the rest of this entry »

There is one white papers from Oracle in area R12 Oracle Subledger Accounting. This was released sometime in last year Sept 2007, seems this is updated with latest version of April 2008.

• The Business Benefits of Oracle Subledger Accounting
  An Oracle White Paper
  September 2007 (latest version is of April 2008 )

Subledger & General Ledger

Those who are not having accounting background should understand , every accounting system will have a number of subsidiary ledgers (called subledgers) for items such as cash, accounts receivable, accounts payable, inventory , purchasing like wise. All the entries that are entered (called posted or booked) to these subledgers will transact through the general ledger account. For example, when a credit sale posted in the account receivable subledger turns into cash due to a payment, the transaction will be posted to the general ledger and the two (cash and accounts receivable) subledgers as well.

There are instances when items will go directly to the general ledger without any subledger.These items will be linked to your balance sheet but not to your profit and loss statement.

The Concept is more or less addressed same in newly R12 SLA accounting , therefore you should note:

• A transactional application that generates accounting impact.
• Used to store detailed information not needed for a general ledger
• Subledgers post summarized activity to a general ledger periodically to maintain centralized account balances for the company

More Reading on SLA

• R12 SLA: Analyzing Subledger Accounting
• R12 SLA: From Product Accounting to Subledger Accounting
• R12 SLA: Transaction Account Builder
• R12 SLA: Accounting Methods Builder