Useful Queries

-- Checking the duplicated schedules of the same program with the same arguments

SELECT request_id, NAME, argument_text, user_name FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t1 WHERE EXISTS ( SELECT 1 FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t2 WHERE t1.NAME = t2.NAME AND t1.argument_text = t2.argument_text AND t1.user_name = t2.user_name GROUP BY NAME, argument_text, user_name HAVING COUNT (*) > 1) ORDER BY user_name, NAME

-- Request scheduled

SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, cr.resubmit_interval, NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ) schedule_type, DECODE (NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ), 'PERIODICALLY', 'EVERY ' cr.resubmit_interval ' ' cr.resubmit_interval_unit_code ' FROM ' cr.resubmit_interval_type_code ' OF PREV RUN', 'ONCE', 'AT :' TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 'EVERY: ' fcr.class_info ) schedule, fu.user_name, requested_start_date FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu, apps.fnd_conc_release_classes fcr WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fcr.release_class_id(+) = cr.release_class_id AND fcr.application_id(+) = cr.release_class_app_id;

-- Average pending time per request

SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, concurrent_queue_name, (SUM ( ( actual_start_date - (CASE WHEN requested_start_date > request_date THEN requested_start_date ELSE request_date END ) ) * 24 * 60 * 60 ) ) / COUNT (*) "Wait_Time_per_Req_in_Secs" FROM apps.fnd_concurrent_requests cr, apps.fnd_concurrent_processes fcp, apps.fnd_concurrent_queues fcq WHERE cr.phase_code = 'C' AND cr.actual_start_date IS NOT NULL AND cr.requested_start_date IS NOT NULL AND cr.controlling_manager = fcp.concurrent_process_id AND fcp.queue_application_id = fcq.application_id AND fcp.concurrent_queue_id = fcq.concurrent_queue_id GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name ORDER BY 2

-- Checking which manager is going to execute a program

SELECT user_concurrent_program_name, user_concurrent_queue_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_queue_content cqc, apps.fnd_concurrent_queues_tl cq WHERE cqc.type_application_id(+) = cp.application_id AND cqc.type_id(+) = cp.concurrent_program_id AND cqc.type_code(+) = 'P' AND cqc.include_flag(+) = 'I' AND cp.LANGUAGE = 'US' AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND NVL (cqc.queue_application_id, 0) = cq.application_id AND cq.LANGUAGE = 'US'

-- To see all the pending / Running requests per each manager wise

SELECT request_id, phase_code, status_code, user_name, user_concurrent_queue_name FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id AND cq.LANGUAGE = 'US' AND cwr.requested_by = fu.user_id ORDER BY 5 -- Checking the incompatibilities between the programs SELECT a2.application_name, a1.user_concurrent_program_name, DECODE (running_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) "Type", b2.application_name "Incompatible App", b1.user_concurrent_program_name "Incompatible_Prog", DECODE (to_run_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) incompatible_type FROM apps.fnd_concurrent_program_serial cps, apps.fnd_concurrent_programs_tl a1, apps.fnd_concurrent_programs_tl b1, apps.fnd_application_tl a2, apps.fnd_application_tl b2 WHERE a1.application_id = cps.running_application_id AND a1.concurrent_program_id = cps.running_concurrent_program_id AND a2.application_id = cps.running_application_id AND b1.application_id = cps.to_run_application_id AND b1.concurrent_program_id = cps.to_run_concurrent_program_id AND b2.application_id = cps.to_run_application_id AND a1.language = 'US' AND a2.language = 'US' AND b1.language = 'US' AND b2.language = 'US'

Useful Queries

-- Command to move the tables

select 'alter table ' table_name ' move tablespace ' tablespace_name ';' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

-- Command to rebuild the indexes in the moved tables

select 'alter index ' owner '.' index_name ' rebuild tablespace ' tablespace_name ';' from dba_indexes where index_type='NORMAL' and table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

-- Check the status of the indexes

select status from dba_indexes where index_type='NORMAL' and table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

select status,index_name from dba_indexes where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

select 'alter table ' owner '.' table_name ' move tablespace ' tablespace_name ';' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

select * from fnd_concurrent_programs_vl where user_concurrent_program_name like 'Purge%Workflow%';

-- Command to move LOB's

select 'alter table ' owner '.'table_name ' move tablespace hyp_ss_tst STORAGE(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) lob ('column_name ')
store as ' segment_name ' (TABLESPACE hyp_ss_tst STORAGE (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0));'
from all_lobs where owner='HYP_SS_TST'

-- Command to make a tablespace as autoextend

select 'alter database datafile ' file_name ' ' ' autoextend on;' from dba_data_files where tablespace_name='MDCCDATA'

-- Gather stats for table

select 'analyze table ' owner '.' table_name ' estimate statistics;' from dba_tables where table_name in ('WF_ITEM_ACTIVITY_STATUSES',
'WF_ITEM_ATTRIBUTE_VALUES','WF_NOTIFICATION_ATTRIBUTES')

-- Move Queue Tables

select 'exec move_aqt.move('''''a.owner''''','''''a.queue_table''''');' from
dba_queue_tables a, dba_segments b where a.queue_table =
b.segment_name and a.owner = b.owner and b.tablespace_name='APPS_TS_QUEUES' and a.owner not in
('SYS','SYSTEM');

How To Determine Printer Setup Used When Printing For A Particular Concurrent Request

Purpose

This note explains how retrieve printer setup used when you are facing a concurrent request printing issues.
In other words what to do when the printing is not compliant with what was expected and you want to know all the printer
setup involved in the printing phase done by concurrent manager.

Troubleshooting Details

The 3 following sql*plus statements will provide you the printer setup used by the concurrent request facing the issue.
What you have to do is to

1) note the ID of the concurrent request having printing troubles,

2) access to the database using sql*plus tool via apps oracle account

3) run the 3 following sql*plus statements

4) provide the request ID you noted when requested for a REQID value.
set pages 999

SELECT NUMBER_OF_COPIES , NLS_LANGUAGE , NLS_TERRITORY , PRINTER , PRINT_STYLE , COMPLETION_TEXT , OUTPUT_FILE_TYPE , NLS_CODESET , OUTFILE_NODE_NAME, OUTFILE_NAME FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &REQID/

SELECT PRINTER_STYLE_NAME , SRW_DRIVER , WIDTH , LENGTH , ORIENTATION FROM FND_PRINTER_STYLESWHERE PRINTER_STYLE_NAME= ( SELECT PRINT_STYLE FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &REQID )/

SELECT PRINTER_DRIVER_NAME, USER_PRINTER_DRIVER_NAME , PRINTER_DRIVER_METHOD_CODE , SPOOL_FLAG , SRW_DRIVER , COMMAND_NAME , ARGUMENTS , INITIALIZATION , RESET FROM FND_PRINTER_DRIVERSWHERE PRINTER_DRIVER_NAME =( SELECT PRINTER_DRIVER FROM FND_PRINTER_INFORMATION WHERE PRINTER_STYLE=( SELECT PRINT_STYLE FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &REQID ) AND PRINTER_TYPE=( SELECT PRINTER_TYPE FROM FND_PRINTER WHERE PRINTER_NAME=( SELECT PRINTER FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID= &REQID ) ) )/

The first sql*plus statement will provide general information about printer setup used for the request. (printer name, printer style, orientation, number of copies, NLS setup, log and output file name etc ...)

The second one will provide information about the printer style used.(Printer style name, number of lines / columns, orientation, SRW driver associated)

The third one all you need to know about the printer driver used.(Printer driver name, printing method, spool, associated SRW driver,command used for printing, initialization and reset
strings)

Script to purge old database backups

This script can be used to purge database backups taken using the script in the previous post. (replace the argument after “mtime” with the required number of days beyond which the backups can be deleted).

========================================================
DATE=`date +%d%b%y_%H_%M_%S`
LOGFILE='/t2/backups/logs/dbtech_backup_'${DATE}'.log'
ORACLE_BASE=/t2/oracleORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0
ORACLE_SID=PROD
WORK=/t2/scripts
EXEC_DIR=/t2/scripts
LOG_HOME=/t2/backups/logs
DB_BACKUP_LOC=/t2/backups/dbtech
export ORACLE_BASE ORACLE_SID ORACLE_HOME LOG_HOME DB_BACKUP_LOC
TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp
SHELL=/usr/bin/ksh
LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export TNS_ADMIN SHELL LD_LIBRARY_PATH
PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export PATH
TERM=vt100
export TERM
NLS_LANG=American_America.UTF8
export NLS_LANG
find $LOG_HOME -type f -mtime +7 xargs rm -f
find $ORACLE_HOME/admin/PROD_perp/bdump -type f -mtime +14 xargs rm -f
find $ORACLE_HOME/admin/PROD_perp/udump -type f -mtime +14 xargs rm -f
find $ORACLE_HOME/admin/PROD_perp/cdump -type f -mtime +14 xargs rm -f
find $DB_BACKUP_LOC -type f -mtime +6 > $WORK/purge.list
if [ -s $WORK/purge.list ]then
for
file in `cat $EXEC_DIR/purge.list`
do
rm ${file}
done
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
mail -s "${ORACLE_SID} - DB Purge Success Log" $address < $WORK/purge.list
sleep 1
fi
done
else
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
echo "No Files found in backup directory to purge!!" mail -s "${ORACLE_SID} - DB Purge Failure Log" $address
fi
done
fi
find $WORK/purge.list xargs rm -f
========================================================

Script to take backup of database stack

This script can be used to take a backup of the database stack. A database backup purge script is given in the next post which can be used with this script.

=====================================================
DATE=`date +%d%b%y_%H_%M_%S`
LOGFILE='/t2/backups/logs/dbtech_backup_'${DATE}'.log'
ORACLE_BASE=/t2/oracle
ORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0
ORACLE_SID=PROD
WORK=/t2/scripts
EXEC_DIR=/t2/scripts
export ORACLE_BASE ORACLE_SID ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp
SHELL=/usr/bin/ksh
LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export TNS_ADMIN SHELL LD_LIBRARY_PATH
PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export PATH
TERM=vt100
export TERM
NLS_LANG=American_America.UTF8
export NLS_LANG
APPS_PASSWD=`cat apps_pass.txt`
echo $APPS_PASSWD perl /t2/oracle/PROD/db/tech_st/10.2.0/appsutil/scripts/PROD_perp/adpreclone.pl dbTier >> $LOGFILE # 781539.1
tar -czvf /t2/backups/dbtech/PROD_db_${DATE}.tgz $ORACLE_HOME >> $LOGFILE
returncode=$?
echo $returncode return code >> $LOGFILE
date >> $LOGFILE
if [ $returncode -ne 0 ]; then
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
tail -n 100 $LOGFILE mail -s "TAR Database Techstack Backup Failure Log" $address echo "TAR Database Techstack Backup Failure Log" >> $LOGFILE
fi
done
else
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
tail -n 100 $LOGFILE mail -s "TAR Database Techstack Backup Successful Log" $address echo "TAR Database Techstack Backup Successful Log" >> $LOGFILE
fi
done
fi
=====================================================

Script to purge old backups

This script can be used to used to purge backups older than a given number of days (replace the argument after “mtime” with the required number of days). This script can be used in conjunction with the backup script in the previous post.

==========================================================
DATE=`date +%d%b%y_%H_%M_%S`
LOGFILE='/t2/backups/logs/appstech_backup_'${DATE}'.log'
ORACLE_BASE=/t2/oracle
ORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0
ORACLE_SID=PROD
WORK=/t2/scripts
EXEC_DIR=/t2/scripts
LOG_HOME=/t2/backups/logs
APPS_BACKUP_LOC=/t2/backups/appstech
export ORACLE_BASE ORACLE_SID ORACLE_HOME LOG_HOME APPS_BACKUP_LOC
TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp
SHELL=/usr/bin/ksh
LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export TNS_ADMIN SHELL LD_LIBRARY_PATH
PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export PATH
TERM=vt100
export TERMNLS_LANG=American_America.UTF8
export NLS_LANG
find $LOG_HOME -type f -mtime +7 xargs rm -f
find $APPS_BACKUP_LOC -type f -mtime +6 > $EXEC_DIR/apps_purge.list
if [ -s $EXEC_DIR/apps_purge.list ]
then
for file in `cat $EXEC_DIR/apps_purge.list`
do
rm ${file}
done
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
mail -s "${ORACLE_SID} - Apps Purge Success Log" $address < $EXEC_DIR/apps_purge.list
sleep 1
fi
done
else
for address in `cat $EXEC_DIR/mail_list`doif [ "`echo $address cut -c1`" != "#" ]; then
echo "No Files found in backup directory to purge!!" mail -s "${ORACLE_SID} - Apps Purge Failiure Log" $address
fi
done
fi
find $EXEC_DIR/apps_purge.list xargs rm -f
==========================================================

Script to take backup of Apps stack

This script can be used to take a backup of the Apps stack

========================================================
DATE=`date +%d%b%y_%H_%M_%S`
LOGFILE='/t2/backups/logs/appstech_backup_'${DATE}'.log'ORACLE_BASE=/t2/oracle
ORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0
ORACLE_SID=PROD
WORK=/t2/scripts
EXEC_DIR=/t2/scripts
export ORACLE_BASE ORACLE_SID ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perpSHELL=/usr/bin/ksh
LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export TNS_ADMIN SHELL LD_LIBRARY_PATH
PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export PATH
TERM=vt100
export TERM
NLS_LANG=American_America.UTF8
export NLS_LANG
perl /t1/oracle/PROD/inst/apps/PROD_perp/admin/scripts/adpreclone.pl appsTier >> $LOGFILE # 781539.1
tar -czvf /t2/backups/appstech/PROD_apps_${DATE}.tgz /t1/oracle/PROD >> $LOGFILE
returncode=$?
echo $returncode return code >> $LOGFILE
date >> $LOGFILE
if [ $returncode -ne 0 ];
then
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
tail -n 200 $LOGFILE mail -s "TAR Applications Techstack Backup Failure Log" $address echo "TAR Applications Techstack Backup Failure Log" >> $LOGFILE
fi
done
else
for address in `cat $EXEC_DIR/mail_list`
do
if [ "`echo $address cut -c1`" != "#" ]; then
tail -n 200 $LOGFILE mail -s "TAR Applications Techstack Backup Successful Log" $address echo "TAR Applications Techstack Backup Successful Log" >> $LOGFILE
fi
done
fi

========================================================

How To Compile and Run Forms with a User Different from Oracle?

Starting with Forms version 9.0.4 and 10.1.2 when trying to compile forms with a user different from Oracle result in errors like:"Permission denied", "error while loading shared libraries: libig.so.0", FRM-91500, FRM-91108
This is the expected behavior and it is caused by a change in security rights:In previous versions the default permission was 755 but starting in these new versions „the default permission is 700 for the majority of binaries in the technology stack). This was also implemented by CPU patches.”.
To work around this issue, perform the following steps:

You can use 2 different solutions

A.)
Change permissions only for a small set of files1. Connect with the "oracle" user and execute the following script (specify a correct path for the ORACLE_HOME):ORACLE_HOME=/specify-the-path-to-the-ORACLE_HOMEexport ORACLE_HOMEchmod +rx $ORACLE_HOMEchmod +rx $ORACLE_HOME/binchmod +rx $ORACLE_HOME/bin/frm*chmod -R +rx $ORACLE_HOME/libchmod +rx $ORACLE_HOME/formschmod -R +rx $ORACLE_HOME/forms/mesgchmod -R +rx $ORACLE_HOME/forms/adminchmod -R +rx $ORACLE_HOME/oracorechmod +rx $ORACLE_HOME/nlschmod -R +rx $ORACLE_HOME/nls/datachmod +rx $ORACLE_HOME/rdbmschmod -R +rx $ORACLE_HOME/rdbms/mesgchmod +rx $ORACLE_HOME/ldapchmod -R +rx $ORACLE_HOME/ldap/mesgchmod -R +rx $ORACLE_HOME/ldap/adminchmod +rx $ORACLE_HOME/networkchmod -R +rx $ORACLE_HOME/network/admin

2. In order to avoid FRM-91500 one may have to add the following lines in the frmcmp_batch.sh:TERM=vt220export TERM

B.)
Change permissions for all files in ORACLE_HOME
1. Login into Server as Oracle or user who installed the Oracle Software
2. cd ORACLE_HOME
3. run this command: chmod -R +rx *Reason: This is required to be executed from ORACLE_HOME because there are several filesthroughout the Oracle Home that have the setuid bit set.
4. Logout and login as non-oracle user and execute the forms executable.

COMMON ERRORS ENCOUNTERED WHEN COMPILING FORMS WITH A "NON - ORACLE" user

1. Permission denied when running the forms executables
Cause: Non-oracle user does not have access to the ORACLE_HOME/bin/frm* executables
Solution: Allow access and execution of ORACLE_HOME/bin/frm*chmod +rx $ORACLE_HOMEchmod +rx $ORACLE_HOME/binchmod +rx $ORACLE_HOME/bin/frm*

2. Solaris: ld.so.1: frmcmp_batch: fatal: ORACLE_HOME/lib/libig.so.0: Permission deniedSUSE/Linux: error while loading shared libraries: libig.so.0: cannot open shared object file: No such file or directory
Cause: Non-oracle user does not have access to the libraries required by the compiler.
Solution: Allow read/execute access to ORACLE_HOME/libchmod -R +rx ORACLE_HOME/lib

3. Message file ORACLE_HOME/forms/mesg/fmcus.msb not found.
Cause: Forms is trying to display a message (error) but does not have access to the messages file.
Solution: Allow access to Forms messages files:chmod +rx ORACLE_HOME/formschmod -R +rx ORACLE_HOME/forms/mesg

4. FRM-91500: Unable to start/complete the build.
Cause: This error is generic but it may suggest that non-oracle user does not have access to the needed terminal file (.res) or that the environment is not correctly set.
Solution:a) Allow access to the terminal files:chmod -R +rx ORACLE_HOME/forms/admin
b) Edit frmcmp_batch.sh and addTERM=vt220export TERM
For additional troubleshooting of this specific error please check Note 756384.1 and Note 266731.1

5. FRM-91108: Internal Error: unable to initialize PL/SQL.
Cause: User does not have access to the timezone files
Solution: Allow access to "ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"chmod +rx ORACLE_HOME/oracorechmod -R +rx ORACLE_HOME/oracore/zoneinfo

6. Error while trying to retrieve text for error ORA-12154
Cause: Forms is trying to display a message (non-forms error) but does not have access to the messages file.
Solution: Allow access to the RDBMS messages fileschmod +rx ORACLE_HOME/rdbmschmod -R +rx ORACLE_HOME/rdbms/mesg

7. ORA-12154: TNS:could not resolve the connect identifier specified
Cause: Forms executables cannot read the required ".ora" files required for making a successful DB connection.
Solution: Allow access to the "$ORACLE_HOME/network/admin/" folderchmod +rx ORACLE_HOME/network/chmod -R +rx ORACLE_HOME/network/admin/*

8. FRM-10043: Cannot open file. orFRM-10044: Cannot create file.
Cause: Incorrect access to the FMB specified
Solution: Check if all the folders contained in the "module" parameter are having "rx" right for other users.Also, check if the "non-oracle" user has "rwx" access to the FMB

9. FRM-30087: Unable to create form file $ORACLE_HOME/forms/test.fmx
Cause: Non-oracle user cannot write in the "output" directory or the mentioned fmx exists but user cannot overwrite it.
Solution: By default the FMX is being created in the same directory with the FMB.Give write permission in that folder and check if FMXs with the same name are already there and if the non-oracle user can overwrite them.Also, make use of the "output_file=" parameter within the compile command to specify a location (with correct access) where the FMX should be created.

OC4J configuration assistant fails during application of patch 5983622

While upgrading 10gAS forms and reports in Release 12 using patch 5983622, the OC4J configuration assistant will fail during the runInstaller run. This is expected for EBS customers so you can click NEXT to finish the installation safely. The forms and reports will be upgraded properly now.

Error while rebuilding Forms and Reports during the upgradation of 12.0.6 to 12.1.1

while upgrading the oracle Application from 12.0.6 to 12.1.1you may see the following errors during a relink of the 10.1.2 Oracle Home:

/usr/lib/libXtst.so.6: undefined reference to `__stack_chk_fail@GLIBC_2.4'
/usr/lib/libXtst.so.6: undefined reference to `__fprintf_chk@GLIBC_2.3.4'
/usr/lib/libXtst.so.6: undefined reference to `__sprintf_chk@GLIBC_2.3.4'

Rebuild Forms and Reports executables
cd $ORACLE_HOME/forms/lib32 Note: if this directory does not exist: cd $ORACLE_HOME/forms/lib $ make -f ins_forms.mk install
cd $ORACLE_HOME/reports/lib32 Note: if this directory does not exist: cd $ORACLE_HOME/reports/lib $ make -f ins_reports.mk install
Link to Motif library in Oracle Application Server 10.1.2 (on OEL 5 and RHEL 5 only)
Perform the following command (as root on your system) to update a required link to a Motif library prior to relinking or patching the 10.1.2 Application Server Oracle Home:

# unlink /usr/lib/libXtst.so.6# ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6
Since the ldconfig command overrides this link, the above link command (ln) will have to be re-issued after running the ldconfig command.

Can FNDLOAD Run In Parallel?

It is ok to run multiple FNDLOAD commands in parallel to UPLOAD AOL data like concurrent programs, responsibilities, etc.
From FNDLOAD perspective, there is absolute no limitation on parallel execution.Parallel upload of concurrent programs and responsibilities should through fine unless upload logic in LCT file restrict the same.

11I.PJ_PF.M Family Pack Installation Fails When Processing egot016.odf and egov028.odf

An error occurs when installing11i.PJ_PF.M (Projects Suite) via Patch 3485155 when processing the files egot016.odf and egov028.odf. Errors similar to the following can be seen in the adpatch worker logs:

ATTENTION: All workers either have failed or are waiting:
FAILED: file egot016.odf on worker 2. ...
adwork002.log: ...
Comparing objects in username EGO with ODF file
/u01/app/TEST/testappl/ego/11.5.0/patch/115/odf/egot016.odf
Reading objects from ODF file
AD Worker - adussfrt: INFO: Do not understand token
START_OF_AD_ODF_FILE in ODF file. Line is:
START_OF_BUILDING_BLOCK AD Worker - adussfrh:
ERROR: ODF file does not begin with standard header information
AD Worker - adusrtrf: ERROR: Unable to read header information from the ODF file
AD Worker - aiccep: ERROR: Unable to read ODF file
Time when worker failed: Mon Jul 04 2005 15:35:34
Evaluating symbolic arguments ....
Done evaluating symbolic arguments.
Time when worker started job:
Mon Jul 04 2005 15:35:34
Start time for file is: Mon Jul 04 2005 15:35:34
Comparing objects in username EGO with ODF file /u01/app/TEST/testappl/ego/11.5.0/patch/115/odf/egot016.odf
Reading objects from ODF file AD Worker - adussfrt:
INFO: Do not understand token START_OF_AD_ODF_FILE in ODF file.
Line is: START_OF_BUILDING_BLOCK
AD Worker - adussfrh:
ERROR: ODF file does not begin with standard header information
AD Worker - adusrtrf: ERROR: Unable to read header information from the ODF file
AD Worker - aiccep: ERROR: Unable to read ODF file


To work around the problem perform the following steps:

- Backup your current versions of egot016.odf and egov028.odf.
- Edit the file egot016.odf and make the following change. This change effectively provides the 115.1.50400.2 version of the file.

Replace:

# $Header: egot016.odf 115.1 2004/10/31 23:55:50 svdubey noship $
With:

START_OF_AD_ODF_FILE2.1.3Current system time is 04-FEB-2005 16:56:54# $Header: egot016.odf 115.1.50400.2 2005/02/04 17:09:18 kdenisen noship $

- Edit the file egov028.odf and make the following change. This change effectively provides the 115.2.50400.2 version of the file. -

Replace:

# $Header: egov028.odf 115.2 2004/10/31 23:46:41 svdubey noship $

With:

START_OF_AD_ODF_FILE2.1.3Current system time is 04-FEB-2005 16:56:58# $Header: egov028.odf 115.2.50400.2 2005/02/04 17:10:40 kdenisen noship $

Restart the failed worker and the patch completes successfully.

How to Skip Failed Worker and Proceed with Patching

To skip the job of a failed worker perform the following at the command line:

1. Manually complete the failed job
If this does not succeed, do NOT perform the actions below!

2. start adctrl

3. Option 8 (hidden option) to skip failed worker

Now the job will skip and the next waiting workers will start running

Forms Version Not Showing On 'About Oracle Applications' Form From Help Menu

While doing post-installation step for upgrading Forms/Reports 10g, version number is Blank under 'Forms Server' on 'About Application'form from Help menu.

The issue is caused by the following setup:Missing setup of FND: Diagnostics profile option

To implement the solution, execute the following steps:

- Go into the responsibility: System Administrator
- Navigate to MENUPATH > System -> Profile.
- FND: Diagnostics set to Y = Yes
- Bounce middle-tier server for this change to take affect.
- Retest the issue.

Access to The Requested Uniform Resource Identifier (URI) Has Been Blocked by The Uniform Resource Locator (URL) Firewall

When attempting to create a bid as a supplier, the following error is encountered:
Access to the requested URI has been blocked by the URL Firewall
ERROR: Access to the requested URI has been blocked by the URL Firewall

This is because of the use of rewrite rules in the fw_url.conf file with the firewall.

to work around the problem, perform the following steps:

- Create a directory under $FND_TOP/admin/template called custom.
- Copy the url_fw.conf from $FND_TOP/admin/template/ to the custom directory
- Add your Custom rules in that file
- Run Autoconfig.
- Retest the issue.
- Migrate the solution as appropriate to other environments.

Register A Supplier From An External URL

The URL for the suppliers to execute their own registration is:

http://[server_name]:[port]/OA_HTML/jsp/pos/suppreg/SupplierRegister.jsp?ouid=[replace_with_the_HASHKEY]

Replace the server name and port.
To get the hashkey execute:

1. Login to sqlplus as the user with "write" permission
2. Enter "set serveroutput on"
3. Run $POS_TOP/patch/115/sql/get_org_hash.sql from Sqlplus as the user.
4. Script will prompt for "operating_unit_id" value.
Enter "operating unit"
5. Script will output "hash key is (hash)"

Execute the following to check if hash key was generated:

SQL> select ORG_ID, HASHKEY from POS_ORG_HASH;

The person who registers the supplier will be added as supplier user. This supplier user will have the responsibility assigned from the profile POS: Default Responsibility for Newly Registered Supplier Users.

Enable Web Access By External Supplier Users to Oracle iSupplier Portal

Required Patches for Oracle iSupplier Portal:

if you are on Procurement Family Pack I or Oracle E-Business Suite Release 11.5.9 the following patch should be applied:

- 3824408 Enable the framework based login page for iSupplier Portal

if you are on Procurement Family Pack J or Oracle E-Business Suite Release 11.5.10 or higher, no patches are required.

To ensure that newly registered external users can register, login, and receive system notifications, perform the following configuration:

- Using the System Administrator responsibility, go to profiles -> system
- Search for the profile option "POS: External URL"
- Set the profile option to:
http or https://external_web_server_machine:port/
- Search for the profile option "POS: Internal URL"
- Set the internal profile option to:
http or https://internal_web_server:port/
- After setting the above profile options run the following script as APPS: $POS_TOP/patch/115/sql/pos_upg_usr.sql

The above script updates the user level values of profile option Applications Framework Agent and Application Servlet Agent for all supplier users using the new values in the profile option POS: External URL.

Every time the value of profile option POS: External URL is changed, it is necessary to run this script.

How to create a FRD (Forms Runtime Diagnostic) Log in EBusiness Suite R12 using Forms 10g

To create a FRD (Forms Runtime Diagnostic) Log in EBusiness Suite R12 using Forms 10g, perform the following steps:
1. Make the USER value for profile option 'ICX: Forms Launcher' the same as the SITE value
2. Append the USER value of ICX: Forms Launcher with the "?record=collect" (without the quotes)
e.g. update the value of ICX: Forms Launcher for the user to be"http://testserver.oracle.com:8005/forms/frmservlet?record=collect"
3. Save this change at the USER level.
4. Logout and Log back in again, for this profile to take effect. When you login again, a message box pops up, which alerts you that Diagnostics is enabled.
5. Go to Help->About Oracle Applications, go to the section

----------------------------------------
Forms Server Environment Variables
----------------------------------------

note the value of FORMS_TRACE_DIR. If it is empty,This value can be defined by the following steps:

- Bring down the Forms server
- Open the context ile and define the "s_forms_trace_dir" context variable in the middle tier (Forms tier) to point to a valid directory.
- Run Autoconfig- bring back up the Forms sever.

6. Reproduce the Forms-Issue, which you are trying to diagnose.
7. Go to the directory specified in the FORMS_TRACE_DIR variable. List the latest files. The FRD Log is the one with the string "collect" in its name.

Unable to connect the database from sqlplus.

When we try to connect the database through the sqlplus from the client machine (Windows Vista) installed with oracle 10g client . It trys to connect the database and the sql window disappears without any error message.

If you type the sqlplus on the command prompt you will receive the following error message.

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly.

This issue can be resolved as shown below.

Goto start  Control panel  User accounts  Turn User Account Control On or Off 
Uncheck the below option.

Use User Account Control(UAC) to help protect your computer.

Restart the machine and try to connect. It works fine

sqlplus does not connect as a non Oracle user

Users different than the owner of the installation (Oracle user is usually the owner) are not able to run SQL*Plus. The following error is thrown.

"SP2-0642: SQL*Plus internal error state 2165, context 4294967295:0:0Unable to proceed"

the reason is that sqlplus is unable to access required files. The error message is indicating following file is missing from install or inadequate permissions to access to the folder "oracore" under the $ORACLE_HOME.

To implement the work around, follow these steps:

1. Manually change the privileges for the $ORACLE_HOME/oracore directory and files.

cd to $ORACLE_HOME and type :

chmod -R 755 oracore

sqlplus will connect now.

Pre-requisite checks fails and runInstaller quits

While installing an Oracle software or an oracle patchset runInstaller checks the OS version and may sometimes quit saying that the OS version failed the pre requisite check. to work around the problem, perform the following steps.

- change directory to "install" under the installation disk.- open the oraparam.ini file.
- Search for the following line(s) and comment it using a '#' symbol. some oraparam.ini files may have only some of the lines listed below. Comment them.

[Certified Versions] #You can customise error message shown for failure through CERTIFIED_VERSION_FAILURE_MESSAGE
#Solaris=5.8,5.9
#Windows=4.0,5.0,5.1,5.2
#Linux=redhat-3
#HPUX=B.11.11
#Decunix=V5.1A,V5.1
#AIX=5.1.0.0

- Restart runInstaller.

Now the runInstaller will not perform any OS version checks and proceeds to installation window.

Useful Scripts

To get list of responsibilities.
SELECT (SELECT application_short_name FROM fnd_application fa WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name FROM apps.fnd_responsibility_tl frt;
======================
To get Menus Associated with responsibility
SELECT DISTINCT a.responsibility_name, c.user_menu_name FROM apps.fnd_responsibility_tl a, apps.fnd_responsibility b, apps.fnd_menus_tl c, apps.fnd_menus d, apps.fnd_application_tl e, apps.fnd_application f WHERE a.responsibility_id(+) = b.responsibility_id AND a.responsibility_id = ‘20538’ AND b.menu_id = c.menu_id AND b.menu_id = d.menu_id AND e.application_id = f.application_id AND f.application_id = b.application_id AND a.LANGUAGE = 'US';
================
To get submenus and Function attached to this Main menu.
SELECT c.prompt, c.description FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';
=================
To get assigned responsibility to a user.
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60);
================
To get responsibility and attached request groups.
SELECT responsibility_name responsibility, request_group_name, frg.description FROM fnd_request_groups frg, fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id ORDER BY responsibility_name
=================
To get modified profile options.
SELECT t.user_profile_option_name, profile_option_value, v.creation_date, v.last_update_date, v.creation_date - v.last_update_date "Change Date", (SELECT UNIQUE user_name FROM fnd_user WHERE user_id = v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o, fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id = v.profile_option_id AND o.application_id = v.application_id AND start_date_active <= SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B' UNION SELECT nls_language FROM fnd_languages WHERE installed_flag = 'B') ORDER BY user_profile_option_name;
=================
To get modified profile options.
SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE, ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event, ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query, (SELECT user_name FROM fnd_user fu WHERE fu.user_id = ffcr.created_by) "Created By " FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft WHERE ffcr.ID = ffft.function_id ORDER BY 1;
=====================
To get Patch Level.
SELECT a.application_name, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id;
================
To get all Functions
SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl order by order by user_function_name;
==============
To get all Request attached to a responsibility
SELECT responsibility_name , frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = 'P' AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name; /;
====================
To get all request with application
SELECT fa.application_short_name, fcpv.user_concurrent_program_name, description, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, output_file_type, program_type, printer_name, minimum_width, minimum_length, concurrent_program_name, concurrent_program_id FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id ORDER BY description
===============
To Count Module Wise Report
SELECT fa.application_short_name, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY fa.application_short_name, fcpv.execution_method_code ORDER BY 1;
=========
To calculate request time
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , f.actual_start_date actual_start_date , f.actual_completion_date actual_completion_date, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600) ' HOURS ' floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) ' MINUTES ' round((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 - (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) ' SECS ' time_difference , DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name'['f.description']',p.concurrent_program_name) concurrent_program_name , decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase , f.status_code FROM apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl pt , apps.fnd_concurrent_requests f WHERE f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null ORDER by f.actual_completion_date-f.actual_start_date desc; =================
Check responsibility assigned to a specific USER
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and a.application_name = 'Purchasing' ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60)
===============
Check Current Applied Patch
SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC
=================