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.
Standard Package Customizations in Oracle
Providing 100% customization is nearly impossible by any provider, 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.
here is the file format,
# --------------------------------------------------------------------------
# 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
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;
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')='
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@
Lookups:
FNDLOAD apps/apps@
LOOKUP_TYPE="lookup name"
Descriptive Flexfield with all of specific Contexts:
FNDLOAD apps/apps@
Key Flexfield Structures:
FNDLOAD apps/apps@
Concurrent Programs:
FNDLOAD apps/apps@
Value Sets:
FNDLOAD apps/apps@
Value Sets with values:
FNDLOAD apps/apps@
Profile Options:
FNDLOAD apps/apps@
Request Groups:
FNDLOAD apps/apps@
Request Sets:
FNDLOAD apps/apps@
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Request Set Stage Links:
FNDLOAD apps/apps@
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Responsibilities:
FNDLOAD apps/apps@
Menus:
FNDLOAD apps/apps@
Messages:
FNDLOAD apps/apps@
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
Posts
