purging request

Purge Interface Tables

Purge Concurrent Request and/or Manager Data

　

https://4uappsdba.wordpress.com/2011/11/19/sql-to-find-pending-concurrent-requests/

https://4uappsdba.wordpress.com/category/useful-queries/

　

　

ordpress.com/category/useful-queries/

　

1.

To check the setting of the ICM in the Concurrent Manager environment

　

select 'PCP' "name", value

from apps.fnd_env_context

where variable_name = 'APPLDCP' and

concurrent_process_id = (select max(concurrent_process_id)

from apps.fnd_concurrent_processes where concurrent_queue_id = 1)

UNION ALL

select 'RAC' "name", decode(count(*), 0, 'N', 1, 'N', 'Y') "value" from V$thread

UNION ALL

select 'GSM' "name", NVL(v.profile_option_value, 'N') "value"

from apps.fnd_profile_options p, apps.fnd_profile_option_values v

where p.profile_option_name = 'CONC_GSM_ENABLED'

and p.profile_option_id = v.profile_option_id

UNION ALL

select name, value from apps.fnd_concurrent_queue_params

where queue_application_id = 0 and concurrent_queue_id = 1;

　

　

2.

To check the details for all the enabled Concurrent Manager

　

select fcq.application_id "Application Id",

fcq.concurrent_queue_name, fcq.user_concurrent_queue_name "Service",

fa.application_short_name, fcq.target_node "Node",

fcq.max_processes "Target",

fcq.node_name "Primary", fcq.node_name2 "Secondary",fcq.cache_size "Cache Size",

fcp.concurrent_processor_name "Program Library",

sleep_seconds

from apps.fnd_concurrent_queues_vl fcq, apps.fnd_application fa, apps.fnd_concurrent_processors fcp

where fcq.application_id = fa.application_id

and fcq.processor_application_id = fcp.application_id

and fcq.concurrent_processor_id = fcp.concurrent_processor_id and fcq.enabled_flag='Y';

　

　

SELECT DISTINCT frl.responsibility_name,

fu.user_name,

fcr.request_id,

(SELECT meaning

FROM apps.fnd_lookups

WHERE lookup_type='CP_PHASE_CODE'

AND lookup_code =fcr.phase_code

) Phase,

(SELECT meaning

FROM apps.fnd_lookups

WHERE lookup_type='CP_STATUS_CODE'

AND lookup_code =fcr.status_code

) Status,

fcs.program,

to_date(fcr.requested_start_date,'DD-MM-RRRR hh24:mi:ss') Start_Date,

fcr.resubmit_interval

||' '

||fcr.resubmit_interval_unit_code Resubmit_Interval,

NVL2(fcr.resubmit_interval,'PERIODICALLY',NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS','ONCE')) schedule_type,

fcs.argument_text

FROM apps.fnd_concurrent_requests fcr,

apps.fnd_concurrent_programs_tl fcp,

apps.fnd_responsibility_tl frl,

apps.fnd_user fu,

apps.fnd_conc_req_summary_v fcs

WHERE fcr.phase_code ='P'

AND fcr.request_id = fcs.request_id

AND frl.language ='US'

AND fcr.requested_by =fu.user_id

AND fcr.responsibility_id =frl.responsibility_id

AND fcr.status_code IN ('P','Q','I')
–AND fcp.language ='US'

AND fcp.source_lang ='US'

AND (NVL(fcr.request_type, 'X') != 'S')

AND fcr.concurrent_program_id =fcp.concurrent_program_id

AND fcr.requested_start_date >= SYSDATE

AND to_date(fcr.requested_start_date,'DD-MM-RRRR hh24:mi:ss') BETWEEN NVL(to_date(:p_from_date,'DD-MM-RRRR hh24:mi:ss'),fcr.requested_start_date) AND NVL(to_date(:p_to_date,'DD-MM-RRRR hh24:mi:ss'),fcr.requested_start_date)

ORDER BY program DESC