Application Report Queries

QUERY TO CHECK LIST OF RUNNING REQUEST

SELECT q.concurrent_queue_name
qname,
f.user_name,
a.request_id
“Req Id”,
Decode(a.parent_request_id, –1, NULL,
a.parent_request_id)
“Parent”,
a.concurrent_program_id
“Prg Id”,
a.phase_code,
a.status_code
— ,b.os_process_id “OS”
— ,vs.sid
— ,vs.serial# “Serial#”
— ,vp.spid
,
a.oracle_process_id
“spid”,
( Nvl(a.actual_completion_date, SYSDATE) – a.actual_start_date ) * 1440
“Time”,
c.concurrent_program_name
|| ‘ – ‘
|| c2.user_concurrent_program_name
|| ‘ ‘
|| a.description
“Program”
FROM   applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_programs_tl c2,
applsys.fnd_concurrent_programs c,
applsys.fnd_user f
— ,v$session vs
— ,v$process vp
WHERE  a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ( ‘I’, ‘P’, ‘R’, ‘T’ )
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = ‘US’
— and vs.process (+) = b.os_process_id
— and vs.paddr = vp.addr (+)
ORDER  BY 9 DESC;

 

QUERY TO CHECK TOTAL SCHEDULED REQUESTS

SELECT request_id                                            id,
Nvl(meaning, ‘UNKNOWN’)                               status,
user_concurrent_program_name                          pname,
To_char(request_date, ‘DD-MON-RR HH24:MI:SS’)         submitd,
To_char(requested_start_date, ‘DD-MON-RR HH24:MI:SS’) requestd
FROM   fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE  phase_code = ‘P’
AND ( fcr.requested_start_date >= SYSDATE
OR status_code = ‘P’ )
AND lookup_type = ‘CP_STATUS_CODE’
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER  BY pname,
request_date;

 

QUERY TO CHECK PENDING REQUEST pending requests Kept (on hold)

SELECT request_id                                    id,
Nvl(meaning, ‘UNKNOWN’)                       status,
user_concurrent_program_name                  pname,
To_char(request_date, ‘DD-MON-RR HH24:MI:SS’) submitd
FROM   fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE  phase_code = ‘P’
AND hold_flag = ‘Y’
AND fcr.requested_start_date <= SYSDATE
AND status_code != ‘P’
AND lookup_type = ‘CP_STATUS_CODE’
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER  BY request_date,
request_id;

 

Terminating Report From Backend

SELECT oracle_process_id,
Decode(status_code, ‘R’, ‘Running’,
‘D’, ‘Canceled’,
‘E’, ‘Error’,
‘X’, ‘Terminated’,
‘G’, ‘Warning’,
‘T’, ‘Terminating’)               “Status_code”,
phase_code,
To_char(actual_start_date, ‘DD-MON-YYYY=>hh24:mi:ss’) “Login Time”
FROM   apps.fnd_concurrent_requests
WHERE  request_id = ‘RequestID’ — “Enter The Request ID Here from Request Form”
SELECT s.sid,
s.serial#,
module,
s.status
FROM   v$session s,
v$process p
WHERE  s.paddr = p.addr
AND p.spid = ‘Process_ID’ — “Enter Process id here From Above Query Result”

UPDATE fnd_concurrent_requests
SET    phase_code = ‘C’,
status_code = ‘X’
WHERE  request_id = ‘RequestID’; — “Enter The Request ID Here from Request Form”
COMMIT;

QUERY To find the reports finished with ERROR CODE

SELECT a.request_id                        “Req Id”,
a.phase_code,
a.status_code,
actual_start_date,
actual_completion_date,
c.concurrent_program_name
|| ‘: ‘
|| ctl.user_concurrent_program_name “program”
FROM   applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_programs c,
applsys.fnd_concurrent_programs_tl ctl
WHERE  a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = ‘E’
AND a.phase_code = ‘C’
AND actual_start_date > SYSDATE – 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = ‘US’
ORDER  BY 5 DESC;

 

QUERY TO FIND REQUEST SUBMITTED BY USER

SELECT user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM   fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE  fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.responsibility_id = fr.responsibility_id
AND fcr.requested_by = fu.user_id
AND user_name = ‘username’ — “Enter The User Name Here”
AND actual_start_date > SYSDATE – 1
— “Enter The Number for how many days, Here 1 means for 1 day”
ORDER  BY request_date ASC;

 

QUERY TO FIND concurrent program enabled with TRACE

SELECT A.concurrent_program_name                     “Program_Name”,
Substr(A.user_concurrent_program_name, 1, 40) “User_Program_Name”,
Substr(B.user_name, 1, 15)                    “Last_Updated_By”,
Substr(B.description, 1, 25)                  DESCRIPTION
FROM   apps.fnd_concurrent_programs_vl A,
applsys.fnd_user B
WHERE  A.enable_trace = ‘Y’
AND A.last_updated_by = B.user_id;

 

QUERY To find concurrent program NAME, PHASE CODE,STATUS CODE for a given Request ID

SELECT request_id,
user_concurrent_program_name,
Decode(phase_code, ‘C’, ‘Completed’,
phase_code)                     phase_code,
Decode(status_code, ‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘T’, ‘Terminating’,
‘M’, ‘No Manager’,
‘X’, ‘Terminated’,
‘C’, ‘Normal’,
status_code)                   status_code,
To_char(actual_start_date, ‘dd-mon-yy:hh24:mi:ss’) Start_Date,
To_char(actual_completion_date, ‘dd-mon-yy:hh24:mi:ss’),
completion_text
FROM   apps.fnd_conc_req_summary_v
WHERE  request_id = ‘Request_id’ — “Enter the request_id here”
ORDER  BY 6 DESC;

 

QUERY TO COUNT PENDING REQUEST

SELECT
/*+ choose */ Count(*)
FROM   fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv
WHERE  fcr.concurrent_program_id = c.concurrent_program_id
AND fcr.program_application_id = c.application_id
AND fcr.concurrent_program_id = fcrv.concurrent_program_id
AND fcr.request_id = fcrv.request_id
AND fcrv.requested_start_date < SYSDATE + 10 / 1440
AND fcr.phase_code IN ( ‘P’ )
AND fcr.hold_flag != ‘Y’

 

QUERY to find all REPORTS STATUS with USER NAME

SELECT
/*+ choose */ U.user_name                                             User_name,
fcr.request_id                                          request_id
,
To_char(Round(( Nvl(fcr.actual_completion_date,
SYSDATE) –
fcr.actual_start_date ) *
1440))                            TIME,
c.concurrent_program_name
|| ‘ – ‘
|| Substr(fcrv.program, 1, 45)                          PROGRAM,
fcr.concurrent_program_id                               Program_id
,
fcr.parent_request_id
Parent_id,
To_char(fcrv.requested_start_date, ‘DD-MON-RR HH24:MI’) Scheduled,
Decode(fcr.phase_code, ‘C’, ‘Completed’,
‘I’, ‘Inactive’,
‘P’, ‘Pending’,
‘R’, ‘Running’)
|| ‘ – ‘
|| Decode(fcr.status_code, ‘A’, ‘Waiting’,
‘B’, ‘Resuming’,
‘C’, ‘Normal’,
‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘I’, ‘Normal’,
‘M’, ‘No Manager’,
‘P’, ‘Scheduled’,
‘Q’, ‘Standby’,
‘R’, ‘Normal’,
‘S’, ‘Suspended’,
‘T’, ‘Terminating’,
‘U’, ‘Disabled’,
‘W’, ‘Paused’,
‘X’, ‘Terminated’,
‘Z’, ‘ Waiting’)             Status
FROM   fnd_concurrent_requests fcr,
fnd_concurrent_programs c,
fnd_conc_req_summary_v fcrv,
fnd_user U
WHERE  fcr.concurrent_program_id = c.concurrent_program_id
AND fcr.program_application_id = c.application_id
AND fcr.concurrent_program_id = fcrv.concurrent_program_id
AND fcr.request_id = fcrv.request_id
AND fcrv.requested_start_date < SYSDATE + 10 / 1440
AND fcr.phase_code IN ( ‘P’ )
AND fcr.hold_flag != ‘Y’
AND fcr.requested_by = U.user_id
ORDER  BY 1,
2 ASC

 

QUERY TO CHECK SCHEDULED CONCURRENT REQUESTS

SELECT fcr.request_id,
fcr.parent_request_id,
fu.user_name                                                requestor,
To_char(fcr.requested_start_date, ‘MON-DD-YYYY HH24:MM:SS’) START_DATE,
fr.responsibility_key
responsibility,
fcp.concurrent_program_name,
fcpt.user_concurrent_program_name,
Decode(fcr.status_code, ‘A’, ‘Waiting’,
‘B’, ‘Resuming’,
‘C’, ‘Normal’,
‘D’, ‘Cancelled’,
‘E’, ‘Error’,
‘F’, ‘Scheduled’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘I’, ‘Normal’,
‘M’, ‘No Manager’,
‘Q’, ‘Standby’,
‘R’, ‘Normal’,
‘S’, ‘Suspended’,
‘T’, ‘Terminating’,
‘U’, ‘Disabled’,
‘W’, ‘Paused’,
‘X’, ‘Terminated’,
‘Z’, ‘Waiting’)                     status,
Decode(fcr.phase_code, ‘C’, ‘Completed’,
‘I’, ‘Inactive’,
‘P’, ‘Pending’,
‘R’, ‘Running’)                      phase,
fcr.completion_text
FROM   fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu,
fnd_responsibility fr
WHERE  fcr.status_code IN ( ‘Q’, ‘I’ )
AND fcr.hold_flag = ‘N’
AND fcr.requested_start_date > SYSDATE
AND fu.user_id = fcr.requested_by
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = fr.responsibility_id
ORDER  BY fcr.requested_start_date,
fcr.request_id;

 

QUERY TO Find SCHEDULED CONCURRENT REQUEST

SELECT cr.request_id,
Decode(cp.user_concurrent_program_name, ‘Report Set’, ‘Report Set:’
|| cr.description,
cp.user_concurrent_program_name)
NAME,
argument_text,
cr.resubmit_interval,
Nvl2(cr.resubmit_interval, ‘PERIODICALLY’,
Nvl2(cr.release_class_id, ‘ON SPECIFIC DAYS’, ‘ONCE’))
schedule_type,
Decode(Nvl2(cr.resubmit_interval, ‘PERIODICALLY’,
Nvl2(cr.release_class_id, ‘ON SPECIFIC DAYS’, ‘ONCE’)),
‘PERIODICALLY’, ‘EVERY ‘
|| cr.resubmit_interval
|| ‘ ‘
|| cr.resubmit_interval_unit_code
|| ‘ FROM ‘
|| cr.resubmit_interval_type_code
|| ‘ OF PREV RUN’,
‘ONCE’,
‘AT :’
||
To_char(cr.requested_start_date, ‘DD-MON-RR HH24:MI’),
‘EVERY: ‘
||
fcr.class_info)
SCHEDULE,
fu.user_name,
requested_start_date
FROM   apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE  cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = ‘P’
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = ‘US’
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

 

QUERY TO FIND RESPONSIBILITY NAME from a Concurrent Program NAME

SELECT DISTINCT FCPL.user_concurrent_program_name,
FCP.concurrent_program_name,
FAPP.application_name,
FRG.request_group_name,
FNRTL.responsibility_name
FROM   apps.fnd_request_groups FRG,
apps.fnd_application_tl FAPP,
apps.fnd_request_group_units FRGU,
apps.fnd_concurrent_programs FCP,
apps.fnd_concurrent_programs_tl FCPL,
apps.fnd_responsibility FNR,
apps.fnd_responsibility_tl FNRTL
WHERE  FRG.application_id = fapp.application_id
AND FRG.application_id = FRGU.application_id
AND FRG.request_group_id = FRGU.request_group_id
AND FRG.request_group_id = FNR.request_group_id
AND FRG.application_id = FNR.application_id
AND FNR.responsibility_id = FNRTL.responsibility_id
AND FRGU.request_unit_id = FCP.concurrent_program_id
AND FRGU.unit_application_id = FCP.application_id
AND FCP.concurrent_program_id = FCPL.concurrent_program_id
AND FCPL.user_concurrent_program_name LIKE ‘%&Conc_Prog_name%’
AND FNRTL.LANGUAGE = ‘US’
AND FAPP.LANGUAGE = ‘US’;

 

QUERY TO COUNT NUMBER OF REPORTS REGISTERED IN EACH MODULE

SELECT fa.application_short_name,
Decode (fcpv.execution_method_code, ‘B’, ‘Request Set Stage Function’,
‘Q’, ‘SQL*Plus’,
‘H’, ‘Host’,
‘L’, ‘SQL*Loader’,
‘A’, ‘Spawned’,
‘I’, ‘PL/SQL Stored Procedure’,
‘P’, ‘Oracle Reports’,
‘S’, ‘Immediate’,
fcpv.execution_method_code)
exe_method,
Count (concurrent_program_id)                                   COUNT
FROM   fnd_concurrent_programs_vl fcpv,
fnd_application fa
WHERE  fcpv.application_id = fa.application_id
AND fcpv.execution_method_code = ‘P’ –“Write execution_method_code Here”
— and fa.application_short_name in (‘SQLAP’,’PN’,’FA’,’CM’,’GL’,’AR’,’OM’) –“Write Included Application Short Name Here”
GROUP  BY fa.application_short_name,
fcpv.execution_method_code
ORDER  BY 1;

 

QUERY TO FIND all REPORTS REGISTERED WITHIN GIVEN APPLICATION

SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
Decode (fcpv.execution_method_code, ‘B’, ‘Request Set Stage Function’,
‘Q’, ‘SQL*Plus’,
‘H’, ‘Host’,
‘L’, ‘SQL*Loader’,
‘A’, ‘Spawned’,
‘I’, ‘PL/SQL Stored Procedure’,
‘P’, ‘Oracle Reports’,
‘S’, ‘Immediate’,
fcpv.execution_method_code)
exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM   fnd_concurrent_programs_vl fcpv,
fnd_application fa
WHERE  fcpv.application_id = fa.application_id
AND fcpv.execution_method_code = ‘P’ –“Write execution_method_code Here”
AND fa.application_short_name IN ( ‘SQLAP’, ‘PN’, ‘FA’, ‘CM’,
‘GL’, ‘AR’, ‘OM’ )
–“Write Included Application Short Name Here”
ORDER  BY description