Concurrent Manager/Request Tuning(Supply Chain Cost Rollup - Print Report)

Concurrent Manager/Request Tuning(Supply Chain Cost Rollup - Print Report)

Recently I was engaged with a Concurrent Processing performance tuning. Queue for "Cost Rollup Manager" was growing rapidly.
This manager is responsible to run only "Supply Chain Cost Rollup - Print Report" and it should not take more than 30 seconds to complete normally.

Though its underlying code was causing the issue, writing in this blog. Same informations can be used to tune other manager and requests as well.

Find the Requests a Manager would Run

select  ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
  from Fnd_Concurrent_Requests t,
       FND_CONCURRENT_PROCESSES k,
       Fnd_Concurrent_Queues_TL QTL,
       Fnd_Concurrent_Programs_TL PTL 
  where k.concurrent_process_id = t.controlling_manager
    and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
    and ptl.concurrent_program_id=t.concurrent_program_id
    AND QTL.LANGUAGE='US'
    AND PTL.USER_CONCURRENT_PROGRAM_NAME LIKE '%'
    AND qtl.user_concurrent_queue_name ='Cost Rollup Manager'
ORDER BY ptl.user_concurrent_program_name DESC;

Find the Requests Running More than 30 Minutes

undefine start_date
undefine end_date

set pages 10000
set verify off
column request_id format 99999999 heading 'REQUEST'
column user_name format a17
column phase format a10
column status format a12
column start_date format a5
column completion_date format a5 heading 'END'
column avg_run_time format 9999 heading 'AVG TIME'
column min_run_time format 9999 heading 'MIN TIME'
column max_run_time format 9999 heading 'MAX TIME'
column program_name format a50

select
    p.user_concurrent_program_name program_name,
    count(r.request_id),
    avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) avg_run_time,
    min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) min_run_time,
    max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) max_run_time
from
    apps.fnd_concurrent_requests r,
    apps.fnd_concurrent_processes c,
    apps.fnd_concurrent_queues q,
    apps.fnd_concurrent_programs_vl p
where
    p.concurrent_program_id = r.concurrent_program_id
    and p.application_id = r.program_application_id
    and c.concurrent_process_id = r.controlling_manager
    and q.concurrent_queue_id = c.concurrent_queue_id
    and p.application_id >= &&ApplicationId
    and r.actual_start_date >= sysdate-31
    and r.status_code = 'C'
    and r.phase_code in ('C')
    and (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 * 60 > 30
    and p.user_concurrent_program_name not like 'Gather%Statistics%'
    and (
      (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 > 16
      or
      (r.actual_start_date-trunc(r.actual_start_date)) * 24 between 9 and 17
      or
      (r.actual_completion_date-trunc(r.actual_completion_date)) * 24 between 9 and 17
    )
group by p.user_concurrent_program_name
/

For a particular Date how many Requests are submitted for a particular concurrent requests and its performance analysis.

SELECT -- /*+ first_rows*/
  fcr.request_id req_id,
  fcp.concurrent_program_name conc_prg,
  PT.USER_CONCURRENT_PROGRAM_NAME USR_CONC_PRG,
  TO_CHAR (FCR.ACTUAL_START_DATE, 'DD-MON-YY HH24:MI:SS') START_DATE,
  ---NVL (TO_CHAR (fcr.actual_completion_date, 'mm-MON-yy HH24:MI:SS'), 'Not complete ') end_date,
  SUBSTR ( DECODE ( TRUNC (actual_completion_date - actual_start_date), 0, NULL, TRUNC (actual_completion_date - actual_start_date)
  || 'D' )
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24, 24 )), 2, 0 )
  || ':'
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24 * 60, 60 )), 2, 0 )
  || ':'
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24 * 60 * 60, 60 )), 2, 0 ), 1, 10 ) TIME,
  flv1.meaning phase,
  flv2.meaning status,
  fcr.argument_text parameters,
  fcr.oracle_process_id
FROM applsys.fnd_concurrent_programs fcp,
  applsys.fnd_concurrent_programs_tl pt,
  applsys.fnd_concurrent_requests fcr,
  fnd_lookup_values flv1,
  fnd_lookup_values flv2
WHERE FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND TRUNC (fcr.ACTUAL_START_DATE) BETWEEN TO_DATE ('17-JAN-2014', 'DD-MON-YYYY') AND TO_DATE ('26-JAN-2014', 'DD-MON-YYYY')
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id         = pt.application_id
AND fcp.concurrent_program_id  = pt.concurrent_program_id
AND pt.LANGUAGE                = 'US'
AND fcr.phase_code             = flv1.lookup_code
AND flv1.lookup_type           = 'CP_PHASE_CODE'
AND flv1.LANGUAGE              = 'US'
AND flv1.view_application_id   = 0
AND fcr.status_code            = flv2.lookup_code
AND flv2.lookup_type           = 'CP_STATUS_CODE'
AND flv2.LANGUAGE              = 'US'
AND FLV2.VIEW_APPLICATION_ID   = 0
AND PT.USER_CONCURRENT_PROGRAM_NAME LIKE '%Supply Chain Cost Rollup - Print Report%'
--ORDER BY FCR.ACTUAL_START_DATE DESC;
ORDER BY time DESC;

Solutions are outlined in following notes

Supply Chain Cost Rollup - Print Report Run Very Long time (Doc ID 1463306.1)
Supply Chain Cost Rollup - Print Report Performance Issues (Doc ID 1588101.1)

Explanation of Various Parameters for Workflow Background Process Engine

Explanation of Various Parameters for Workflow Background Process Engine

ITEM TYPE:
Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any deferred activity regardless of its item type.

MINIMUM THRESHOLD:
Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.

MAXIMUM THRESHOLD:
Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second.
By using Minimum Threshold and Maximum Threshold multiple background engines can be created to handle very specific types of activities. The default values for these arguments are 0 and 100 so that the background engine runs activities regardless of cost.

PROCESS DEFERRED: 
Specify whether this background engine checks for deferred activities. Setting this parameter to YES allows the engine to check for deferred activities.

PROCESS TIME OUT: 
Specify whether this background engine checks for activities that have timed out. Setting this parameter to YES allows the engine to check for timed out activities.

PROCESS STUCK: 
Specify whether this background engine checks for stuck processes. Setting this parameter to YES allows the engine to check for stuck processes.

FNDREVIVER - Theories and Concepts to remember

FNDREVIVER - Theories and Concepts to remember

Theories on FNDREVIVER 

FNDREVIVER (also recognized as reviver.sh) is used for momentary disconnects in the system where the concurrent managers and/or forms go down, and forms is later reconnected while the concurrent managers are not. FNDREVIVER revives the Internal Concurrent Manager (ICM) when it fails.

When ICM can no longer get a database connection, it kills itself and spawns the reviver. Reviver loops every 30 seconds, attempting to login to the database as apps user. Once login is successful, it starts up the ICM again.

If the failure is due to a brief network outage, or database issue, the managers are restarted, so the client does not have to restart the managers manually.

Reviver is recovery mechanism runs in the background. In a Real Applications Cluster (RAC) environment, when the primary node goes down and ICM is set to migrate to the secondary node, the reviver parameter will be passed to the secondary node.

The easiest way to determine if reviver.sh exists is by checking the $FND_TOP/bin directory.

The variable resides in the context file under 's_cp_reviver' and can be set to "enabled" or "disabled". Based on the value of s_cp_reviver in the context file, AFCPDNR is started with a value of either "enabled" or "disabled" .

The reviver is started when starting the ICM, by passing a parameter reviver="enabled". You do this on the node you start the manager, and if the ICM is set to migrate to the second node, this parameter will be passes to the second node. 
A common misconception is that users must start the reviver.sh manually, however this is not the intended use. It is automatically enabled when the parameter REVIVER_PROCESS="enabled" is passed via the adcmctl.sh concurrent manager startup script. 

On a single node concurrent processing system, FNDREVIVER is the only way to recover from a database connection loss. 

On a two node system, there is another factor, the Internal Monitor (FNDIMON).The FNDIMON will race to restart the internal manager in a multi node setup, and by the time the reviver starts it will likely see that the ICM is already running and exit accordingly. 

FNDIMON checks whether it can connect to the database in order to determine if the ICM is running, and if the database connection is not available it fails to run and exits accordingly. The reviver is a shell script which loops until a connection is obtained, and then starts the manager accordingly. The reviver's job is the last line of defense after a database connection failure, as FNDIMON only works when the database connection is available. 

In the event the ICM goes down due to a network outage, then the reviver would be needed to bring the ICM back up. 

Context File Parameters related to FNDREVIVER

The following parameters can be set in the context file, and then autoconfig should be re-run to enable reviver: 

Concurrent Processing Reviver Process (s_cp_reviver) [Allowed values are {enabled, disabled}]
<cp_reviver oa_var="s_cp_reviver">enabled</cp_reviver> 

Reviver Process PID Directory Location (s_fndreviverpiddir) 
This variable specifies the path where ICM reviver process pid file will be created. Oracle recommends using a local disk as the PID file location because the reviver process may run when the network is down. 
<fndreviverpiddir oa_var="s_fndreviverpiddir">/u02/oracle/visappl/fnd/11.5.0/log</fndreviverpiddir> 

Delete Concurrent Program and Executable

Delete Concurrent Program and Executable

Begin
  fnd_program.delete_program('AG_HR_TRANSFER_PROG', 'MKK Group Custom');
  fnd_program.delete_executable('AG_HR_TRANSFER_PROG', 'MKK Group Custom');
  commit;
End;

MKK Group Custom >>> Is your custom Application registered name.

Recreate FND_CONCURRENT_QUEUES Information

Recreate FND_CONCURRENT_QUEUES Information

Publishing this on request of a friend.... It was applicable for him after clone.

Run FND_CONC_CLONE
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and Web tiers to repopulate the required system tables. 

Connect to SQLPLUS as APPS user and run the following statement :
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not return any value please do the following:
cd $FND_TOP/patch/115/sql
START afdcm037.sql;

Check again that FNDSM entries now exist:
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

Run CMCLEAN.sql and start the Managers.

This would help after cloning if the Managers are not coming up.

iRecruitment Index Synchronization - All Details

iRecruitment Index Synchronization - All Details

Why iRecruitment Index Synchronization?

To keep the text indexes up to date for iRecruitment documents and job postings run the iRecruitment Index Synchronization process. Oracle iRecruitment uses Oracle Text to perform content-based searches on resumes and job posting details. When candidates upload resumes or managers post new job details, you must synchronize the index at a regular interval to keep the user searches accurate.

Recommended way to run iRecruitment Index Synchronization(from MOS Documents)

• Posting Index indicates index of job postings that managers post.
• Document Index indicates index of candidates' resumes.
• Online index rebuild - to run every 5 minutes

Online index rebuild - to run every 5 minutes
Note: If the online synchronization process starts before the previous one has completed, then the process will display an error. Ensure that you set it to run 5 minutes after completion, and not the start.
In the Online mode, the process adds new entries to the index, enabling simultaneous searches.

Full index rebuild - to run each night
In the Full mode, the process defragments the index, reducing its size, and optimizing the performance. The process does not add new entries to the index.

Impact if Not Done

This is recommended to enhance the performance of iRecruitment and should be done, as it is a very normal DBA Activity.

Log and Output file Reference in Conc Request Tables

If Request out and logfile needs to be viewed after cloning and site migration update the following:

UPDATE FND_CONCURRENT_REQUESTS
SET LOGFILE_NAME = REPLACE(LOGFILE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


UPDATE FND_CONCURRENT_REQUESTS
SET OUTFILE_NAME = REPLACE(OUTFILE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


UPDATE FND_CONCURRENT_REQUESTS
SET LOGFILE_NODE_NAME = REPLACE(LOGFILE_NODE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


UPDATE FND_CONCURRENT_REQUESTS
SET OUTFILE_NODE_NAME = REPLACE(OUTFILE_NODE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


Also update apps.fnd_conc_req_outputs table:

update apps.fnd_conc_req_outputs set FILE_NAME='<<<correct_out_path>>>/LMAPCHKRNA_11797166_1.PDF' where CONCURRENT_REQUEST_ID='11797166';
update apps.fnd_conc_req_outputs set FILE_NODE_NAME='<<<<correct_conc_processing_host_name>>>>' where CONCURRENT_REQUEST_ID='11797166';
You should be able to view

SQL_Query - Find the Schedule Concurrent Requests and Request Sets

SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%'
ORDER BY CONC_PROG_NAME;

Workflow Related Scheduled Concurrent Program for DBAs

Purge Obsolete Workflow Runtime Data

Oracle Workflow accesses several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes. The size of these tables and indexes can adversely affect performance. You should purge these tables on a regular basis using FNDWFPR.
This program calls database package WF_Purge.Total.  This program deletes old Workflow runtime data for completed Workflows. It is recommended that this program or package is run regularly as required.

From Workflow Administrator's Guide PDF Document:
The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

Parameter Explanation:
Item Type: The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
Item Key: The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
Age: Minimum age of data to purge, in days.
Persistence Type: The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
Core Workflow Only: Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
Commit Frequency: Enter the number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
Signed Notifications: Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.
Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.

Workflow Control Queue Cleanup

Parameters: NONE
Schedule:
Frequency:    Run every 12 Hours
Responsibility:  System Administrator
Please tick the checkbox “From the start of the prior run”

Workflow Background Process

This program needs to be submitted in various permutation and combination.

Type-1: which Runs Every 3 Mins for Parameters:
Process Deferred: Yes
Process Timeout:  No
Process Stuck:  No

Type-2: which Runs Every 30 Mins for Parameters:
Process Deferred:  No
Process Timeout:  Yes
Process Stuck:  No

Type-3: which Runs Every 1 Hours for Parameters:
Process Deferred:  No
"Process Timeout:  No
Process Stuck:  Yes"

Type-4: which Runs Every 2 Hours for Parameters:
Process Deferred:  Yes
"Process Timeout:  Yes
Process Stuck:  Yes"

How to Submit a Workflow Background Process Engine [ID 182936.1], Follow the Note mentioned at the End of this Metalink Note

Workflow Mailer Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Work Items Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Agent Activity Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Gather Schema Statistics Schedule:

Make sure Gather Shema Stats is running on periodical basis, for XXXXX we have scheduled to run every 15 Days.
Following points needs to be noted for the Gather Schema Statistics:
●● Do not gather statistics excessively on entire schemas or the entire database such as nightly or weekly.
●● Do not gather statistics on permanent objects during peak intervals.
●● Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
●● Gathering statistics requires dictionary and object level locks.
●● The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have changes above the specified 'Modification Threshold' (percentage of DML compared to the number of rows of the table).
●● Plans are not likely to change if the data distribution has not changed.
●● Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent Programs.
●● Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results in sub-optimal plans.

Following CONCSUB Command can be used to schedule the Gather Schema Statistics concurrent Program:
"CONCSUB apps/${xx_appspassword} SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND FNDGSCST START='""01-MAR-2012 02:00:00""'
REPEAT_DAYS=10 'ALL','10','18','NOBACKUP','','LASTRUN','GATHER','','Y'"

To Submit the Program Manually from SQLPlus following commands can be used:
For One Schema:
SQL> exec fnd_stats.gather_schema_statistics('MRP');
For All Schema:
SQL> exec fnd_stats.gather_schema_statistics('ALL');
For One Table:
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');

To Submit the Gather Schema Statistics from EBS Front End:
●● Make sure you are logged in as SYSADMIN User
●● Use the System Administrator Responsibility to Submit the Concurrent Program.

Notification Mailer Override Address and Status

Set Override Address

Override address is set in test instance using "Workflow Administrator Web Application" responsibility in test instance so that all notification mails goes to a single mail ID.

How to Update Override Address from Backend?

select fscpv.parameter_value, fscpt.parameter_id from fnd_svc_comp_params_tl fscpt,fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Test Address' and fscpt.parameter_id = fscpv.parameter_id
    
UPDATE FND_SVC_COMP_PARAM_VALS SET PARAMETER_VALUE='mkhawas@nomail.com'
WHERE parameter_id=(select fscpv.parameter_id from fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Test Address' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID)

exec FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW ( x_component_name => 'Workflow Notification Mailer', x_parameter_name => 'TEST_ADDRESS', x_parameter_value => 'mkhawas@nomail.com', x_customization_level => 'L', x_object_version_number => -1, x_owner => 'ORACLE' );
commit;


Update SMTP Server from Backend:

UPDATE FND_SVC_COMP_PARAM_VALS SET PARAMETER_VALUE='<<<<<SMTP Server IP or Host>>>>>>'
WHERE parameter_id IN (select fscpv.parameter_id from fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Outbound Server Name' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID);


How to check Notification Mailer is Up/Down from Backend:

select SC.COMPONENT_TYPE, SC.COMPONENT_NAME,
FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS
from FND_SVC_COMPONENTS SC
order by 1, 2;

Cheers !!!!!

Purge Concurrent Request and/or Manager Data

Ensure "Purge Concurrent Request and/or Manager Data, FNDCPPUR,"  is run at regular intervals with "Entity" parameter as "ALL".  A high number of records in FND_CONCURRENT tables can degrade the performance.
Functions of FNDCPPUR: It purges data on FND_CONCURRENT tables and log/output files.

Name: Purge Concurrent Request and/or Manager Data
Short Name: FNDCPPUR
Description: Purges Concurrent requests and/or Concurrent Manager data and log/output files

Parameter Listing:
1. Entity: ALL
2. Mode: Age
3. Mode Value : 10
4. Oracle ID: NULL
5. User Name: NULL
6. Responsibility Application: NULL
7. Responsibility: NULL
8. Program Application: NULL
9. Program: NULL
10. Manager Application: NULL
11. Manager: NULL
12. Report: Y
13. Purge Other: Y

For slow performance of "Purge Concurrent Request and/or Manager Data" set the system profile option "Concurrent: Force Local Output File Mode" to "Yes" and then retest. You may get a better performance of the program.

As per our recent purging strategy we are running “Purge Concurrent Request and/or Manager Data” concurrent request everyday at 21:30:00.
Short Name: FNDCPPUR
This request basically deletes data from FND_CONC related tables and log/out files from physical file system, but I have noticed that this request is not performing the task as required. Even we do have log files older than 2-3 Months.

As per Note ID: 822368.1 we have following workarounds(Oracle Apps Version: 11.5.10.2)
1. Apply patch#7834670
2. Set Profile option “Concurrent: Force Local Output File Mode” value to Yes (As of now value is No)
3. Retest the issue

Cheers !!!!
Malay

HOW TO Add Concurrent Program to Request Group from Back End

HOW TO Add Concurrent Program to Request Group from Back End:

set serveroutput on
DECLARE
BEGIN
APPS.FND_PROGRAM.add_to_group
(
PROGRAM_SHORT_NAME => 'XXCTO_GPO_PRINT_REPORT',
PROGRAM_APPLICATION => 'XXCTO',
REQUEST_GROUP => 'XXCTO_REPORT_TEST',
GROUP_APPLICATION => 'SYSADMIN'
);
COMMIT;
exception
when others then
dbms_output.put_line('Request Already Exixts in Request Group');
END;
/

How to Resend Failed/Error Workflow Notifications?

How to Resend Failed/Error Workflow Notifications?

Run "Resend Failed/Error Workflow Notifications" concurrent program to resend FAILED notifications.
This concurrent program will resend all the failed notifications to the user again.

IMPORTANT SQLs - Related to Concurrent Processing

Find the OPP Logfile of a Request ID:


SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM apps.fnd_conc_pp_actions fcpp, apps.fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &req_id;

==================************==================

Query to Find Out the Schedule Concurrent Requests and Request Sets:

SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%'
ORDER BY CONC_PROG_NAME;

Cheers !!!!!!!

After Cloning Managers are not coming up

After Cloning Managers are not coming up

Method#1:
● Clean the FND_NODE Info:
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
●● Run Autoconfig on DB node and then Apps Node
●●● Run $FND_TOP/patch/115/sql/afdcm037.sql script
●●●● Start Services (adstrtal.sh)

Method#2:
● Ensure Concurrent :GSM Enabled profile is set to ‘Y’
●● Run $FND_TOP/patch/115/sql/afdcm037.sql
●●● Do Relinking of FND Conc Libraries:
cd $FND_TOP/bin
adrelink.sh force=y "fnd FNDLIBR"
adrelink.sh force=y "fnd FNDSM"
adrelink.sh force=y "fnd FNDFS"
adrelink.sh force=y "fnd FNDCRM"

Before bringing the managers up, execute CMCLEAN….. In most of the cases it helped….


Cheers !!!!!!!

HOW TO - Send Output of Concurrent Request to User's Mail ID

HOW TO - Send Output of Concurrent Request to User's Mail ID

Setup SMTP Related Profile Option:
FND:SMTP Host
FND:SMTP Port

In Submit Request window click on Delivery Option, Go to Email Tab and key in the Mail Details.

Review the Logfile for following Messages:

+------------- 2) DELIVER -------------+
Emailing output to user1@mail.com
Beginning post-processing of request 2065107 on node <<<<<HOSTNAME>>> at 20-JUN-2012 09:50:56.
Post-processing of request 2065107 completed at 20-JUN-2012 09:51:07.
+--------------------------------------+

Cheers !!!!!!!