Script to Find High Impact Concurrent Programs

Sql Script to find High Impact concurrent Programs

#Script to find High Impact Concurrent Programs in html format
set linesize 4000
set verify off
set feedback off
set pagesize 999
set markup html on entmap on spool on preformat off
spool Highimpactconcurrentprograms.html

PROMPT High Impact Concurrent Programs

select substr(cname.user_concurrent_program_name,1,80) "prog name",
exec_date,creq.occur,creq.max_time,creq.min_time,creq.avg_time
from (select
concurrent_program_id,trunc(actual_start_date) exec_date,
count(concurrent_program_id) occur,
round(min(actual_completion_date-actual_start_date)*24*60,2) min_time,
round(max(actual_completion_date-actual_start_date)*24*60,2) max_time,
round(avg(actual_completion_date-actual_start_date)*24*60,2) avg_time
from apps.fnd_concurrent_requests
where status_code='C'
and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id ,trunc(actual_start_date)
having max(actual_completion_date-actual_start_date)*24*60 >30) creq
,apps.fnd_concurrent_programs_tl cname
where creq.concurrent_program_id=cname.concurrent_program_id
and cname.language='US'
order by max_time,occur;

set markup html off entmap off spool off preformat on
set linesize 2000 verify on feedback on
spool off

Adding a New Language(s) to the Oracle Applications

Most often when Customers want to add a New Language, the DBA team goes through the process of searching tons of their Emails, Tickets, CSR's for all patches's that need to be applied to keep in synch with US Language. Most often this list can make it messy and go uncontrolled.

This is the simple way to add a new Language(s) to the Oracle Applications provided by Oracle.

Verify Prerequisites

Release 11i

Verify AD Patchset Level : You must be at least on AD mini-pack I (11i.AD.I.2)

Apply Update : If you are not on 11i.AD.I.6 or later, apply update 5837664. This update delivers the latest tool for generating manifest file.

Prepare the Applications Instance for New Language(s): If you are planning to request Translation Synchronization patch for a language which has not yet been activated in your instance, you need to do the following:
· Activate new language(s) using the Oracle Applications Manager (OAM) License Manager.
· Run the AD Administration utility to maintain multilingual tables from the Maintain Applications Database Entities menu.

Maintain Snapshot Information : Recommended

Release 12:

Verify AD Patchset Level: If you are not on R12.AD.A.1 or later, apply update 5635729.
Prepare the Applications Instance for New Language(s): See Installing Translations in Oracle Applications NLS Release Notes, Release 12.0.4.

Requesting Translation Synchronization Patch

Generate and Upload the Manifest of Customer Specific Files :

1. Log on to your Applications' administration node as the APPLMGR user

2. Source the APPL_TOP environment file.

3. Use the manifest generation tool to generate customer specific manifest file by executing the following command:
perl $AD_TOP/bin/adgennls.pl

4. The ouput generated from the manifest tool is in the format :
$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt

5. The manifest contains all of your active languages. You do not need to run the tool for each individual language.

Once the manifest is generated, you can go to http://updates.oracle.com/TransSync & Follow the instructions on the screen to upload the manifest file.

A Translation synchronization patch will be generated specifically based on your manifest for each of your active languages.The patch will synchronize the language file versions and American English file versions in your Applications instance.

If you select the option to get latest translations, the patch will also bring your translations up-to-date. If your current translations are already up-to-date and in sync with your American English file versions, you will be notified by e-mail that there is no need to apply Translation Synchronization patch.

Applying Translation Synchronization Patch

Download and Apply the Customer Specific NLS Patch

1. Download it from OracleMetaLink. Follow the instructions in the README file to apply the patch.

2. A Merged Translation patch may be generated if full translations are not available.

3. You can merge multiple language Translation Synchronization Patches and apply the single merged patch to your system.

Post Install Steps:

If you apply Translation Synchronization patch to add a new language, you need to run the AD Administration utility to generate message files from the Generate Applications Files menu.

Reference : Metalink Doc: 252422.1

--Srinivas Ramineni
Oracle Apps DBA

To Find ICM log file path

Smal Script to Find ICM log file path

SELECT 'ICM_LOG_NAME=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';


ICM logfile: Default is std.mgr, can be changed with the mgrname

Startup Parameter
Concurrent manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr

--
Srinivas Ramineni
Oracle Apps DBA