-- Concurrent Program Details
SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name"
, fcp.concurrent_program_name "Short Name"
, fa.application_short_name "Module"
, fe.executable_name "Executable"
, fe.execution_file_name "Executable Short Name"
, DECODE (fcp.execution_method_code, 'X', 'FlexRpt', 'F', 'FlexSql', 'H', 'Host', 'S', 'Immediate', 'K', 'Java Concurrent Program', 'J', 'Java Stored Procedure', 'M', 'Multi Language Function', 'P', 'Oracle Reports', 'I', 'PL/SQL Stored Procedure', 'E', 'Perl Concurrent Program', 'B', 'Request Set Stage Function', 'L', 'SQL*Loader', 'Q', 'SQL*Plus', 'R', 'SQL*Report', 'Z', 'Shutdown Callback', 'A', 'Spawned') "Execution Method"
, fcpl.description "Program Description"
, fdfcuv.column_seq_num "Column Seq Number"
, fdfcuv.end_user_column_name "Parameter Name"
, fdfcuv.form_left_prompt "Prompt"
, fdfcuv.enabled_flag " Enabled Flag"
, fdfcuv.required_flag "Required Flag"
, fdfcuv.display_flag "Display Flag"
, ffvs.flex_value_set_name "Value Set Name"
FROM
apps.fnd_executables fe
, apps.fnd_concurrent_programs_tl fcpl
, apps.fnd_application fa
, apps.fnd_concurrent_programs fcp
, apps.fnd_descr_flex_col_usage_vl fdfcuv
, apps.fnd_flex_value_sets ffvs
, apps.fnd_lookup_values flv
WHERE
1 = 1
AND fe.executable_id = fcp.executable_id
AND fcpl.application_id = fa.application_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id
AND user_concurrent_program_name = :ip_user_conc_program_name -- give Concurrent program name here..
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = 'US'
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY
fdfcuv.column_seq_num
;
/
--CONCURRENT_PROGRAM Execution Details
SELECT
fcr.PARENT_REQUEST_ID PARENT_REQUEST_ID
, fcr.request_id child_request_id
, NVL (fu.description, fu.user_name) requested_by
, DECODE (fcp.concurrent_program_name, 'FNDRSSUB', 'Request Set - ' || fcr.description, fcpt.user_concurrent_program_name) concurrent_program
, DECODE (fcr.phase_code, 'I', 'Inactive', 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 'Pending'), 'R', 'Running', 'C', 'Complete', fcr.phase_code) phase
, DECODE ( fcr.status_code, 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T', 'Terminating', 'D', 'Cancelled', 'E', 'Error', 'F', 'Scheduled', 'G', 'Warning', 'H', 'On Hold', 'I', CASE
WHEN fcr.request_date < fcr.requested_start_date
THEN 'Scheduled'
ELSE 'Normal'
END, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', fcr.status_code) status
, fcr.argument_text
, TO_CHAR (fcr.request_date, 'DD-Mon-RRRR HH12:MI:SS AM') date_requested
, TO_CHAR (fcr.requested_start_date, 'DD-Mon-RRRR HH12:MI:SS AM') requested_start_date
, TO_CHAR (fcr.actual_start_date, 'DD-Mon-RRRR HH12:MI:SS AM') date_started
, TO_CHAR (fcr.actual_completion_date, 'DD-Mon-RRRR HH12:MI:SS AM') date_completed
, (fcr.actual_completion_date-fcr.request_date)*24*60*60 AS end_to_end
, (fcr.actual_start_date -fcr.request_date)*24*60*60 AS lag_time
, fcr.oracle_process_id
, fcr.os_process_id
, fcr.logfile_node_name || ': ' || fcr.logfile_name logfile_name
, fcr.outfile_node_name || ': ' || fcr.outfile_name output_name
, fcr.PRINTER
, FRRPA.PROGRAM_APPLICATION_ID
, FRRPA.PROGRAM_ID
, FRRPA.ARGUMENTS
, FRRPA.SEQUENCE
, FRRPA.NUMBER_OF_COPIES
, FRRPA.NLS_LANGUAGE
, FRRPA.ARGUMENT1
, FRRPA.ARGUMENT2
, FRRPA.ARGUMENT3
, FRRPA.ARGUMENT4
, FRRPA.ARGUMENT5
, FRRPA.ARGUMENT6
, FRRPA.ARGUMENT7
, FRRPA.ARGUMENT8
, FRRPA.ARGUMENT9
, FRRPA.ARGUMENT10
FROM
apps.fnd_concurrent_requests fcr
, apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpt
, apps.fnd_user fu
, apps.FND_RUN_REQ_PP_ACTIONS FRRPA
WHERE
fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcpt.language = 'US'
and fcr.PARENT_REQUEST_ID = FRRPA.parent_request_id
and fcpt.user_concurrent_program_name = :ip_user_conc_program_name -- give Concurrent program name here..
-- and fcr.argument_text like '%9032531%'
and FRRPA.PROGRAM_APPLICATION_ID is not null
-- and fcr.request_id = 172918239
and fcr.PARENT_REQUEST_ID = 172918239
ORDER BY
fcr.request_date DESC
;
/
--To find Responsibilities having a particular Concurrent Program
SELECT
frt.responsibility_name
, fcpt.user_concurrent_program_name
, (
Select distinct
APPLICATION_NAME
from
APPS.fnd_application_tl fat
, APPS.FND_APPLICATION FA
where
1 =1
AND FAT.APPLICATION_ID = FA.APPLICATION_ID
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FAT.LANGUAGE = 'US'
)
APPLICATION_NAME
FROM
apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpt
, apps.fnd_request_group_units frgu
, apps.fnd_responsibility fr
, apps.fnd_responsibility_tl frt
WHERE
fcp.concurrent_program_id = fcpt.concurrent_program_id
AND fcp.application_id = fcpt.application_id
AND fcp.concurrent_program_id = frgu.request_unit_id(+)
AND frgu.unit_application_id(+) = fcp.application_id
AND frgu.request_group_id = fr.request_group_id(+)
AND fr.responsibility_id = frt.responsibility_id(+)
AND fr.application_id = frt.application_id(+)
AND frgu.request_unit_type(+) = 'P'
AND frt.language(+) = 'US'
AND fcpt.language = 'US'
AND fcpt.user_concurrent_program_name LIKE :ip_user_conc_program_name -- give Concurrent program name here..
ORDER BY
frt.responsibility_name
;
--To find Concurrent Program assigned to particular APPLICATION
Select distinct
user_concurrent_program_name
, APPLICATION_NAME
from
(
SELECT
frt.responsibility_name responsibility_name
, fcpt.user_concurrent_program_name user_concurrent_program_name
, (
Select distinct
APPLICATION_NAME
from
APPS.fnd_application_tl fat
, APPS.FND_APPLICATION FA
where
1 =1
AND FAT.APPLICATION_ID = FA.APPLICATION_ID
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FAT.LANGUAGE = 'US'
)
APPLICATION_NAME
FROM
apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpt
, apps.fnd_request_group_units frgu
, apps.fnd_responsibility fr
, apps.fnd_responsibility_tl frt
WHERE
fcp.concurrent_program_id = fcpt.concurrent_program_id
AND fcp.application_id = fcpt.application_id
AND fcp.concurrent_program_id = frgu.request_unit_id(+)
AND frgu.unit_application_id(+) = fcp.application_id
AND frgu.request_group_id = fr.request_group_id(+)
AND fr.responsibility_id = frt.responsibility_id(+)
AND fr.application_id = frt.application_id(+)
AND frgu.request_unit_type(+) = 'P'
AND frt.language(+) = 'US'
AND fcpt.language = 'US'
-- AND fcpt.user_concurrent_program_name LIKE :ip_user_conc_program_name -- give Concurrent program name here..
)
where
APPLICATION_NAME = 'ShipConsole'
ORDER BY
user_concurrent_program_name