I have posted a number of previous notes defining and discussing Oracle diagnostics. This tool is enhanced because of new security model.Let’s take a quick look on revisiting this utility.

Six reason why ‘Everyone like Oracle Diagnostic’

Oracle E-Business Suite Diagnostics is a framework and a repository of diagnostic tests. These are six top reason why every one liked this product.

1. This is shipped product within the box
2. This is FREE …
3. This utility based out of common known problems
4. Test Result with corrective actions information
5. Available for most of the products
6. You will get enough documentation on metalink.

Changes in R12.1

• Diagnostics now have role-based security
• Customizable security layer implemented using Role- Based Access Control (RBAC)
• The name got changed to “Oracle E-Business Suite Diagnostics” from ‘Support Diagnostic Tools”
• The access is now taken care by Grant via Diagnostics Roles.
• ‘Application Diagnostics’ responsibility is new Navigational menu.
• Within new security model Sensitivity Levels is now being introduced which have three value 1,2,3.
• In other word Sensitivity is basically an attribute of the test that indicates the kind of data it is dealing with.
• Oracle Diagnostic is now a high performing tool coupled with Multi-threaded execution engine.
• Oracle Diagnostic have capability to includes Data masking.
• You can design your Custom roles based out of your business need.
• Oracle Diagnostic output is tagged with your SR, by passing SR number.

Diagnostics Architecture

Read the rest of this entry »

ISD team of client side was struggling with such requirement.

Scenario :They have two request set name say ‘X’ and request set name ‘Y’.

Their requirement was to make these two incompatible to each. So that when X runs Y should wait for X to complete or vice versa?

This might be very similar requirement for other so thought to bring into post.

Lets understand ….. “INCOMPATIBLE”

So left hand side figure , is what defined in m-w.com

Lets revisit in Oracle Context..

INCOMPATIBLE means: Two incompatible reports or request set are not allowed to run in parallel. Incompatible reports /program can only run sequentially, the first one must terminate before the other is allowed to start.

So, it can happen that a report is pending for a long time, when it is waiting for the incompatible report to finish.

In Oracle Context you have to know that INCOMPATIBLE type may be global or domain . If you choose Domain, the incompatibility is resolved at a domain-specific level. If you choose Global, then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.Make sense.. Lets take a look how documentation define:

Example of Program Incompatibilities

You can understand like this: Oracle General Ledger’s Posting program,which is used to post journal entries.

If the Posting program’s incompatibility with other concurrent programs were not enforced, other financial reports running simultaneously with the Posting program could contain incorrect account balance information.

Definition of incompatible type “Domain” VS “Global”

If you choose Domain, the incompatibility is resolved at a domain-specific level.

If you choose Global(Fyi..The concept of “Global” incompatibilities was introduced with Patch 2364876), then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.

You can define a concurrent program to be globally incompatible with another program that is, the two programs cannot be run simultaneously at all; or you can define a concurrent program to be incompatible with another program in a Conflict Domain. Conflict domains are abstract representations of groups of data.

They can correspond to other group identifiers, such as sets of books, or they can be arbitrary.

Concurrent Conflict Domains Concept

As per Oracle system admin user guide, If two programs are defined as incompatible with one another, the data these programs cannot access simultaneously must also be identified.

In other words, to prevent two programs from concurrently accessing or updating the same data, you have to know where, in terms of data, they are incompatible. A Conflict Domain identifies the data where two incompatible programs cannot run simultaneously.

In Oracle Applications, data is stored in database tables that belong to a particular application. Each table may also contain information used to determine what conditions need to be met to access the individual records. These conditions may consist of one or more of the following data groupings:

• Set of books : This is based out of underline profile option i.e..GL_SET_OF_BOOKS
• Multiple Operating units :This is based out of underline profile option MO_OPERATING_UNIT)
• Multiple Orgs :This is based out of underline profile option ie. INV_ORGANIZATION_ID Manufacturing Applications
• HR may use business group as a conflict domain
• Fixed asset may use Fixed asset dep. book(FA)

A conflict domain is an abstract representation of the groupings used to partition your data. There is no limit to the number of domains that can be defined, but excessive domains may hurt performance.

More on Oracle Conflict Domains

A conflict domain is a set of related data stored in one or more ORACLE schemas and linked by grants and synonyms. Do not confuse logical databases with your ORACLE database. The ORACLE database contains all your Oracle Applications data, with each application’s data usually residing in one ORACLE schema. You can think of a logical Defining database as a line drawn around a set of related data for which you wish to define concurrent program incompatibilities. In other words, logical databases determine which concurrent programs cannot run at the same time.Make sense:)

Profile Options as part of standard setup

• Concurrent:Conflicts Domain :This option identifies the domain within which all the incompatibilities between programs has to be resolved.
  The profile can be set at Site, Application, Responsibility and User levels.This can be an operating unit name, a legal entity name, or a set of books name as the domain name. You are allowed to define as many as domains as you need. Since you cannot delete conflicts domains, you should keep the domains to a necessary minimum.

DEFINING CONFLICTS DOMAINS

Navigate to Concurrent > Conflicts Domains

How its Conflict Domains Works with conflict resolution manager

All programs are assigned a conflict domain when they are submitted. If a domain is defined as part of a parameter the concurrent manager will use it to resolve incompatibilities. If the domain is not defined by a parameter the concurrent manager uses the value defined for the profile option Concurrent:Conflicts Domain.

Lastly, if the domain is not provided by a program parameter and the Concurrent:Conflicts Domain profile option has not been defined the ‘Standard’ domain is used. The Standard domain is the default for all requests.

All programs use the Standard conflict domain unless a value is defined for the profile option Concurrent:Conflicts Domain or a conflict domain is defined through a program parameter.

Each request submitted uses parameters which identify the records that it will access.

For programs that are defined with incompatibility rules an additional parameter (conflict domain parameter) is used. The conflict domain may be set automatically based on such variables as a login ID, set of books, or the organization the user is working in. The conflict domain parameter may in some cases be selected in the parameters field of the Submit Requests form. Once the parameter is determined the Conflict Resolution Manager (CRM) uses the domain to ensure that incompatible programs do not run simultaneously in the same domain.

Read the rest of this entry »

Oracle Applications does not allow users to view other’s output. Internally Report Review Agent allows(RRA) only the user who submitting the report to view the report output. For those who are very new to Oracle can understood RRA as a default text viewer that Oracle Applications provides, which allows users to view report output and log files. Very often report Review Agent is also referred by the executable FNDFS.

That mean the output cann’t be utlized by another user unless untill you have provided the profile option setup for another user.

Sometime business user wants to see other user report submitted by another?

Minimum you have do is you need to alter the value of seeded profile options “Concurrent:Report Access Level”.

Business case

Within Oracle installation , lets say you have four different user profiles been maintained:

1. Super User : the one who has access to everything
2. Supervisor : the one who does not have access to all the functions but access to most of Function expect few setup.
3. User : the one who has access to limitted screens based on their responsibility
4. Inquiry :one who has access to limitted screens based on their responsibility.

Read the rest of this entry »

Many of EBS users take advantage of the ‘File-Export’ functionality. This functionality was specifically designed for use on ‘Folder’ forms so users could export the data displayed on the screen to Excel.

This is one of the option that allows users to export information from a row based data set into a desktop file so that they can utilize these data’s sheet for there own reference or any other reporting or feeding purpose .

This is yet another story of some basis functionality differences in two ERP Products(Oracle EBS and SAP), Here are some sets of of Q & A with core user discussion.

• Can the system export all data from all modules to external applications?
  • Oracle: In Oracle you can export all the data directly from form, where as you can’t export all report data into Excel.
  • SAP : The SAP situation is opposite, that mean you a need interface for exporting data from modules where as for reports you can can be downloaded in Excel.
• Is the export tool easy to use? What user skill-set is required?
  • Oracle :In oracle you do it From the menu it is FILE/EXPORT
  • SAP :Yes, the method for exporting reports is via menu paths from within the report overview. This method is common throughout SAP. An understanding of Microsoft products is required.
• Can exports be set with run-time parameters?
  • Oracle:Yes but only limited for Forms
  • SAP : , answer is Yes,ie exports can be defined to run at specific times, this would run the appropriate report in SAP and download to the necessary network location or printer.
• Export Functionality from Custom Form?
  • Oracle:If your form is based out of Template.fmb, you can get use of Export Functionality.
  • SAP : Not sure about this
• Integration with Other Non Oracle and Oracle system like Hyperion , Third Party Tax or Other Product for Forecasting and Budgeting?
  • Oracle:Depends , what format is used by these application. As per industry practice .csv format is standard , there fore all Exported can be saved into .CSV that will be easily recognize by other application.

Exporting Apps Reports Into Excel sheet

There is no such seeded functionality that you can get from EBS for exporting into Excel.

There are several options available which depends upon the usage and volume of data into Report.

1. You cannot directly see the output in Excel from view -> Request -> output.
   You need to make changes to report so that the output is saved as delimited text file and then open with excel with the delimiter. This way at least some data get into excel, easy but ugly format, if have header and report title details.
2. Use Oracle XML Publisher.
   It should give you an option for output in Excel files.But make sure, if you are pulling Financial data, you must tell financial and Controllers that there are certain limitation for:

   • Number of rows generated exceeds the Excel maximum

   • Use of formulae or macros can’t done in the Excel output

   • They can’t get Pivot sort of report into Excel based XMLPub.

   • Amount alignment because of numeric value ( Seems this should be fixed by assuming string value)

   • Decimal Placement

Data loading and formatting into excel . as whenever you have lot of rows then it will take some more time to dump into Excel , because of XHTML with the mime type set to get Excel to open it.

3.) Seeded Functionality of Excel Output

1. Login to apps, using “System Administrator” responsibility
2. Navigate to Install -> Viewer Options
3. Add the entries as below
   File Format=Text Mime Type=application/vnd.ms-excel Description=Microsoft Excel
   File Format=Text Mime Type=application/vnd.ms-word Description=Microsoft Word
4. Save the changes
5. Bounce Apache (Not sure if it works without bouncing Apache)
6. View the concurrent requests log (view log button)
7. Goto Tools -> Copy File. It will open a new window viewer option with the choices to select the Viewer to open the logfile. Select Microsoft Excel here and press ok.

4) Use concurrent program output format as XML. Change the output format to EXCEL in this path
Submit Request–> Options–>Layout–>Format.

Additional Issue which user might encounter

1. User can’t get Save Prompt
   • Check the version of the IE in desktop and do these additional Internet Explorer settings.
   • Tools>Internet Options>Security>Custom Level>Downloads
   • Automatic prompting for file downloads needs set to Enable
2. Another issue File-Export does not work in some pc’s many times. Not sure, what is Oracle Solutions. I had a trick , whenever such issue I told me user to Press CTRL key and then do the file export. This works…

In summary

Any thoughts :)

Disclaimer : SAP Functionality which is discussed is based out of Personal observation and discussion with SAP user, which they are using bit older version.Any Conflicts with SAP functionlity must be validated. Oracle Version which is used for reference is 11.5.10.2/R12.

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.

In Release 12, profile options can now be defined to have a hierarchy type of server-responsibility.

1. Site
2. Server + Responsibility
3. User

What does it mean?

…Simply you can understand like ,when you are choosing this type, the profile can be set at Site level, User level, or a new combination Server+Responsibility. This hierarchy type is set in the profile definition, and cannot be changed at runtime.

At the Server+Responsibility level, both the server and responsibility are considered as a pair. Profile values can be set for combinations of a specific server with a specific responsibility, or for only a server or responsibility with a default for the other.

Oracle documentation clearly states “at runtime, the profile value is determined by looking for the value set at the most specific level”

1. A value set at the user level over-rides all others
2. If no user level value is set, look for value matching the current server and responsibility.
3. If no value is set matching both the current server and responsibility, look for a value matching the responsibility with a default server.
4. If no value is found there, look next for a value matching the server with a default responsibility.
5. Finally, if no value is set at any lower level, look for a site level value.

You can also do this in the hierarchy type can be chosen on the Profile Definition page. On this page, you can see that a type of “Server+Responsibility” is chosen. Once this hierarchy type is chosen, the profile can be made visible and updatable at the Site, Server+Responsibility, and User level. In this example, the profile could be set for Site or Server+Responsibility, but not User.

I have already posted note on FNDLOAD utility for doing movement of application setup data from one database to the other.There are instances when we need to move some AOL and seeded data from one Environment to another Environment, thus at that time it is necessary to understand what are different options Available within the Oracle. Here is extensive list for some of the objects , that should be migrated by FNDLOAD.

What is Descriptive Flexfield Context Synchronization

Descriptive Flexfield Context Synchronization allows the runtime descriptive flexfield (DFF) context to be determined from a current reference field value for the entity instance (current record) instead of a previous value of the reference field

How/where this can be actioned?

Typically as per AOL documentation it is enable by providing new check box “Synchronize with Reference Field” on the Descriptive Flexfields
Segments form (FNDFFMDC)

What is meant by Checking and unchecking

• Value of “Yes” means context is always synchronized with reference field value.
• Value of “No” denotes that context will be defaulted from reference field value, the first time the record is created and the DFF is touched. Thereafter, context would never be synchronized by the flex engine.Context prompt treated differently by DFF view generator.

Reference:

• Metalink Note:455659.1
• Refer ATG Schan’s Blog for more details for DFF context Synchronization