oracle计划请求,Oracle EBS 计划请求

SELECT fcp.concurrent_program_name,

decode(fcre.description,

NULL,

fcpt.user_concurrent_program_name,

fcre.description|| '(' || fcpt.user_concurrent_program_name || ')'),

fu.user_name,

conc.request_id,

conc.root_request_id,

decode(fcre.status_code,'D','Cancelled','U','Disabled','E','Error','M','No Manager','R','Normal','H','On Hold','W','Paused','B','Resuming','I','Scheduled','Q','Standby','S','Suspended','X','Terminated','T','Terminating','A','Waiting','G','Warning') status,

fcre.requested_start_date last_start_date,

fcre.actual_start_date last_actual_start_date,

fcre.actual_completion_date last_completion_date,

fcre.argument_text,

sche.start_date,

sche.end_date,

sche.job_class,

sche.class_info

FROM apps.fnd_concurrent_programs fcp,

apps.fnd_concurrent_programs_tl fcpt,

apps.fnd_concurrent_requests fcre,

apps.fnd_user fu,

(SELECT owner_req_id,

date1 start_date,

date2 end_date,

decode(class_type,'S', 'Specific Days', 'P', 'Periodically') job_class,

decode(class_type,'P',

substr(class_info,1, instr(class_info, ':') - 1) ||decode(substr(class_info, instr(class_info,':') + 1, 1),'D','Days','H','Hours','N','Minutes','M','Months','W','Weeks'),'S',

class_info) class_info

FROM apps.fnd_conc_release_classes fcr

WHERE class_type IN ('S', 'P') --Periodically andSpecific Concurrent

AND enabled_flag= 'Y'AND (date2 IS NULL OR date2>SYSDATE)

AND owner_req_id IS NOT NULL) sche,

(SELECT MAX(request_id) request_id, root_request_id

FROM apps.fnd_concurrent_requests

WHERE root_request_id IN

(SELECT nvl(root_request_id, request_id) request_id

FROM apps.fnd_concurrent_requests

WHERE request_id IN

(SELECT owner_req_id

FROM apps.fnd_conc_release_classes fcr

WHERE class_type IN ('S', 'P') --Periodically andSpecific Concurrent

AND enabled_flag= 'Y'AND (date2 IS NULL OR date2>SYSDATE)

AND owner_req_id IS NOT NULL)

AND status_code<> 'D')

GROUP BY root_request_id) conc

WHERE fcp.concurrent_program_id=fcpt.concurrent_program_id

AND fcpt.language= 'US'AND fcp.concurrent_program_id=fcre.concurrent_program_id

AND fcre.status_code NOT IN ('D', 'X', 'U') --Cancelled and Terminated andDisabled

AND fcre.request_id=conc.request_id

AND conc.request_id=sche.owner_req_id

AND fcre.requested_by= fu.user_id