List a parent request and all its children

Submitted by code_admin on Fri, 07/20/2018 - 12:48

Possible enhancements:-
Add Phase Code
Add Status Code
Add Detail Message

  1. set serveroutput on
  2.  
  3. create or replace package IC_RJMTMP as
  4.   procedure exec(p_requestID in number);
  5. end;
  6. /
  7. show errors;
  8. /
  9.  
  10. create or replace package body IC_RJMTMP as
  11.   g_space varchar(255) := '========================================================================================';
  12.   procedure outputRequestTREE(p_requestID in number, p_lev in number)
  13.   is
  14.     cursor children is
  15.       select
  16.         fcr.request_id
  17.       from fnd_concurrent_requests fcr
  18.       where fcr.parent_request_id=p_requestID
  19.       order by fcr.request_date
  20.     ;
  21.     children_rec children%rowtype;
  22.  
  23.     l_request_id fnd_concurrent_requests.request_id%type;
  24.     l_Description fnd_concurrent_requests.description%type;
  25.     l_parent_requesT_id fnd_concurrent_requests.parent_request_id%type;
  26.     l_argument_text fnd_concurrent_requests.argument_text%type;
  27.   begin
  28.     select
  29.       fcr.request_id,
  30.       fcpt.user_concurrent_program_name as description,
  31.       fcr.parent_requesT_id,
  32.       fcr.argument_Text
  33.     into
  34.       l_request_id,
  35.       l_description,
  36.       l_parent_request_id,
  37.       l_argument_text
  38.     from fnd_concurrent_requests fcr
  39.     left join (
  40.       select * from fnd_concurrent_programs_tl
  41.     ) fcpt on fcpt.concurrent_program_id=fcr.concurrent_program_id and fcpt.application_id=fcr.program_application_id
  42.     where (
  43.       request_id=p_requestID
  44.     )
  45.     order by request_date asc;
  46.    
  47.     dbms_output.put_line(substr(g_space,0,p_lev*2) || to_char(l_request_id) || ' ' || l_description || ' (' || l_argument_text || ')');
  48.     FOR children_rec IN children LOOP
  49.        outputRequestTREE(children_rec.request_id,p_lev+1);
  50.      END LOOP;
  51.   end;
  52.  
  53.   procedure exec(p_requestID in number)
  54.   is
  55.   begin
  56.     dbms_output.put_line('Start');
  57.     outputRequestTREE(p_requesTID,0);
  58.     dbms_output.put_line('End');
  59.   end;
  60. end;
  61. /
  62. show errors;
  63. /
  64. begin
  65.   IC_RJMTMP.exec(45243208);
  66. end;
  67. /
  68. drop package body IC_RJMTMP;
  69. drop package IC_RJMTMP;
  70. /

Codes

STATUS_CODE Column:

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

PHASE_CODE column

C - Completed
I - Inactive
P - Pending
R - Running

Tags

RJM Article Type
Quick Reference