Oracle EBS Faq

Date Parameter in a PL/SQL Concurrent program

suppose we want to pass the date parameter in a PL/SQL concurrent program, declaring a date parameter in a PL/SQL code mapping to the concurrent program parameter errors the program, and we should define the character parameter in PL/SQL and convert the same to date as per the usage. but we have a problem of using the date format to convert the character to date. so to avoid these date format errors oracle provides an API, fnd_date.canonical_to_date.

suppose we have a character variable holding date to be p_start_date,
fnd_date.canonical_to_date(p_start_date) returns date format value.

Installed Patches in Oracle Applications

Oracle stores the list of installed patches in AD_APPLIED_PATCHES in APPLSYS schema

Oracle Diagnostic Report

Oracle Diagnostic Report is basically the report of all the existing Application Objects and files validation status report, whcih can be generated by following the below steps

1. Select responsibility 'Oracle Diagnostics Tool'.
2. Select Application: Applications DBA
3. Select Advanced Tab
4. Enter the Reponsibility Id, Application for the Data Collection.

And this will generate the report of Diagnostics for the corresponding Application.


Show Requests Window after Each Request Submission

To show a small request window whether user would like to submit another request to retain Request submission screen, set the following profile option:

Concurrent: Show Requests Summary After Each Request Submission to 'No'.

The default value for this profile option is 'Yes', which shows the Request Summary screen each time you submit the request.

If 'No' is chosen for this profile option then a decision window is opened asking if you wish to submit another request.

Please check the link for additional Profile option settings

Disabling Concurrent program output:

Sometimes we may be required to disable the output of the concurrent program, considering to disable the output of a concurrent program we need to update the output file, output node location to NULL in the table FND_CONCURRENT_REQUESTS as below,

UPDATE FND_CONCURRENT_REQUESTS
SET outfile_name = null
,outfile_node_name = null
WHERE request_id = :CONC_REQUEST_ID;

UTL_FILE Usage:

If you need to Outbound the data from the Table to an Excel sheet, In that case there are lot of options in implementing this as below:

1. Create a PL/SQL Concurrent program CSV output File and then use FTP tool to FTP to client machine, and open in Excel.
2. Create a PL/SQL Concurrent program, and using OWA_SYLK Package as detailed in blog http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html

For the First method, you need to write a file to the Database directory and then move the same to the UNIX directory. The following UTL_FIL commands writes the data to a database file and then moves the same to the UNIX directory,

DECLARE

lc_file_type UTL_FILE.file_type;
lc_out_dir VARCHAR2(200) := 'TEST_DIR';
lc_file_name VARCHAR2(200) := 'UTL_File_Testing.txt';

BEGIN

lc_file_type := UTL_FILE.FOPEN(lc_out_dir, lc_file_name,'W', 500);

for i in 1..100 LOOP
UTL_FILE.put_line(lc_file_type, 'This is for record:'||i, TRUE);
END LOOp;

UTL_FILE.fclose(lc_file_type);

END;


Inventory organization accesss to a Responsibility:

Usually the Organization list is contriolled at the responsibility level i.e., in the same responsibility we can change from one org to another. This is controlled by the Organization access form at:

Inventory --> Setup --> Organization Access


How to Make a responsibility as Read Only for a User:

1. Open the CUSTOM.pll in the Form Builder
2. Add the following sample code in the Procedure event for the event 'WHEN-NEW-FORM-INSTANCE'.
3. Compile the CUSTOM.pll and plce under the directory $AU_TOP/resource

The Following sample code makes a responsibility Read-Only for a user,

BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN

BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

WHILE (blockname is not null) LOOP

IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN

SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);

END LOOP;

END query_only_mode;

END;

Please check metalink note 363298.1 for any additional info


Trace File Location in Oracle Server:

The trace files are usually located in the udump directory for the Oracle Server.
The exact path can be found from the V$Parameter table which is usually the initial setup procedure for Oracle server:

SELECT name,value from v$parameter WHERE name like 'user_dump_dest';


Oracle Auditing:

Oracle Applications Audit Trails maintains full history of changes made at a table level and column level.
Audit Trails are enabled by a shadow table (Table name appended with _A) of the audited table and triggers on the audited columns.
Concurrent program ‘Run Audit Trail’ is used to create the shadow table and triggers.

Auditing database row changes is a very performance intensive and can cause significant database performance problems. Careful planning and reviews should be done with DBA before enabling auditing. Only a minimal amount of auditing should be done and only to Non-transactional data. Auditing on transactional data could cause significant performance degradation of the entire application, also table with more than a few changes a hour in customer business usage should not be considered for auditing.

The Following setup should be done before proceeding with the Audit:
1. Set the profile option Audit Trail: Activate to ‘Yes’.
2. Select the Audit installations
System Administrator -> Security -> Audi Trail -> Install
All the Schemas for which Auditing to be enabled must be checked.
3. Create a new Audit Group which contains the tables to be audited secured under particular application.
Define Table Columns to be audited,
System Administrator -> Security -> Audi Trail -> Tables

And the following columns should not be included as the user information is automatically added,
Creation Date, Created By, Last update Login, Last Update Date, Last Updated By
4. Run Audit Trail Update Program
This activates the auditing, and
· Creates a shadow table with the same name as original table but appended by ‘_A’.
· Creates trigger for each audited column.
· Creates 2 views for each audited column appended with ‘_AC#’ AND ‘_AV#’ where # is a sequential number.

The AuditTrail table data can only be accessed by the underlying shadow tables via SQL only, there are no Standard Oracle Application reports to access Audit Trail Data.

The AuditTrail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging,

the Procedure
·System Administrator -> Security -> Audi Trail -> Groups
· Select the 'Security Audit' group and set the group state to 'Disable – Purge Table'
· Run the ‘Audit Trail Update Tables’ Report
· Purge the unwanted data from the shadow tables, be careful that the Reports developed on the historic data could be impacted with this process.
· System Administrator -> Security -> Audi Trail -> Groups
· Select the “Security Audit” group and set the group state to “Enable” Run the ‘Audit Trail Update Tables’ Report


FNDLOAD Commands

Most of the times Oracle Application Custom Developers creates custom menu and a custom responsibility in one of the development instance. The following commands are useful in migrating these to other environments using FNDLOAD/FNDSLOAD.
Please find the commands to download the data using FNDLOAD utility

Printer Styles:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

Lookups:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod"
LOOKUP_TYPE="lookup name"

Descriptive Flexfield with all of specific Contexts:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

Key Flexfield Structures:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

Concurrent Programs:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

Value Sets:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

Value Sets with values:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VSET_VALUE FLEX_VALUE ="flexfield segment value"

Profile Options:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"

Request Groups:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

Request Sets:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

Request Set Stage Links:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET_LINKS
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

Responsibilities:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

Menus:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Messages:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME="message_name" APPLICATION_SHORT_NAME=”prod”

XDO Definition:
FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=”prod” DATA_SOURCE_CODE=”Data Def Name”

Functions:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=”Function Name”

Templates from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbptmpl.lct file_name.ldt AR_BPA_TEMPLATES

Items from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbpitem.lct file_name.ldt AR_BPA_ITEM

Data Sources from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbpds.lct file_name.ldt AR_BPA_DATA_SOURCES

Oracle Alerts:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME=”prod”

Form Personalization:
FND_TOP/bin/FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

Standard Package Customizations in Oracle:

Providing 100% customization is nearly impossible by any Application provider, as it holds different technology limitations, In some cases we need to modify a bits of standard code to obtain a specific customization and those can be PL/SQL Packages, Workflow Objects, Forms, Reports, OA Pages etc.,

Workflow, Forms, Reports have different methodologies not impacting the Oracle Patching process, but suppose we modify the PL/SQL Package then there is a specific tweaking method we need to follow.

Oracle provides a file applcust.txt located at $APPL_TOP/admin directory listing the files customized so that when an Oracle patch is applied, it lists the files the oracle patch is about to override and IT DOES NOT STOP applying the oracle patch. so once oracle patch is applied, we need to take care of the packages modified and re-write/copy the custom code back to the modified packages from oracle.

# --------------------------------------------------------------------------
# Sprd Src Dir Src Fname Dprd Dest Dir Dest Fname
# --------------------------------------------------------------------------

# fnd bin found45 # considered CBM because no destination listed
# Real entries
#
# Package Spec and Body of PO_WF_REQ_NOTIFICATION
po patch/115/sql POXWPA6S.pls abc sql ABCPOXWPA6S.pls
po patch/115/sql POXWPA6B.pls abc sql ABCPOXWPA6B.pls
# Package Body of PO_WF_PO_NOTIFICATION
po patch/115/sql POXWPA7B.pls abc sql ABCPOXWPA7B.pls


TKPROF version not in Sync with the Database:

Sometimes while taking the TKPROF you observe that the TKPROF version is not in sync with the Databse, this happens if you login with applmgr, which is mapped to $ORACLE_HOME with 8.0.6 version.

suppose if you login with any other oracle login, then $ORACLE_HOME is mapped to your current version of Oracle DB, may be 9.2.0 where the TKPROF version is 9.2.0.X

There are three ORACLE_HOME in Oracle Apps, Two for Application Tier (Middleware) and One in Database Tier.

* ORACLE_HOME 1 : On Application Tier used to store 8.0.6 techstack software. this is used by forms, reports & discoverer. ORACLE_HOME should point to this ORACLE_HOME while applying Apps Patch.

* ORACLE_HOME 2: On Application Tier used by iAS (web server) techstack software, this is used by Web Listener & Apache.

* ORACLE_HOME 3: On Database Tier used by Database Software 8i,9i or 10g database.

Oracle apps concepts guide http://download-uk.oracle.com/docs/cd/B25516_14/current/acrobat/11iconcepts.pdf


Requisition Notification Error:

The Requisition Approver gets the following error while the Requisition is sent for Approval,

An Error occurred in the following Workflow.

Item Type = REQAPPRV
Item Key = 202764-314174
User Key =151217

Error Name = WF_ERROR
Error Message = [WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: sqlerrm

wf_notification.GetAttrDoc2(656906, PO_REQ_APPROVE_MSG, text/html)
Wf_Notification.GetAttrDoc(656906, PO_REQ_APPROVE_MSG, text/html)
Wf_Notification.GetText(656906, text/html)
Wf_Notification.GetBody(656906, text/html)
WF_NOTIFICATION.GetFullBody(nid => 656906, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 656906, r_ntf_pref => MAILHTML)' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK=
WF_MAIL.GetLOBMessage3(656906, WFMAIL, 2020: Error when getting notification content. Caused by: sqlerrm

wf_notification.GetAttrDoc2(656906, PO_REQ_APPROVE_MSG, text/html)
Wf_Notification.GetAttrDoc(656906, PO_REQ_APPROVE_MSG, text/html)
Wf_Notification.GetText(656906, text/html)
Wf_Notification.GetBody(656906, text/html)
WF_NOTIFICATION.GetFullBody(nid => 656906, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 656906, r_ntf_pref => MAILHTML), Step -> Getting text/html body)
WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 656906)
WF_XML.Generate(oracle.apps.wf.notification.send, 656906)
WF_XML.Generate(oracle.apps.wf.notification.send, 656906)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 656906, WF_XML.Generate)
Wf_Event.dispatch_internal()
Error Stack =

Activity Id = 93509
Activity Label = NOTIFY_APPROVER_PROCESS:PO_REQ_APPROVE-1
Result Code = #MAIL
Notification Id = 656906
Assigned User = JAD024

Solution:

1. Stop and Start the Workflow Notification Mailer
2. Bounce the DB, Middle Tier and Clear the Cache
3. Retry the Error Notification from the Action History.

Viewing the Hidden Characters in Unix:

We can View the hidden characters in Unix with the following command,

od -a file_name


Print This Post