Long Running Program and SQL Query

LONG RUNNING PROGRAM

SELECT a.request_id

,a.oracle_process_id "SPID"

,frt.responsibility_name

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name

,a.description

,a.ARGUMENT_TEXT

,b.node_name

,b.db_instance

,a.logfile_name

,a.logfile_node_name

,a.outfile_name

,q.concurrent_queue_name

,a.phase_code,a.status_code, a.completion_text

, actual_start_date

, actual_completion_date

, fu.user_name

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C') avg_mins

,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours

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

,apps.fnd_user fu

,apps.FND_RESPONSIBILITY_TL frt

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.phase_code = 'R'

AND a.status_code = 'R'

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 a.requested_by = fu.user_id

AND a.responsibility_id = frt.responsibility_id

ORDER BY a.actual_start_date DESC

GET THE SESSION ID FROM PREVIOUS QUERY

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time, ROUND((SYSDATE - b.logon_time) * 1440, 2)   "Runtime (in Minutes)"  

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND a.spid = 'SESSION ID PUT'

ORDER BY a.spid, c.piece

PROGRAM CONSUMING TIME

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date - rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"   ,  

       ROUND((sysdate - rq.actual_start_date) * 1440, 2)   "Runing (in Minutes)" ,

       ROUND((sysdate - rq.actual_start_date) * 1440/60, 2)   "Runing (in Hours)"

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

--   AND rq.actual_start_date IS NOT NULL

--   AND rq.actual_completion_date IS NOT NULL

  and  request_id=202319707

Without Looping check the Collection data

ARRAY TYPE

declare

  CURSOR CUR IS

    select TRANSACTION_TYPE_ID

      from MTL_TRANSACTION_TYPES

     where TRANSACTION_TYPE_NAME IN

           (select MEANING

              from FND_LOOKUP_VALUES_VL

             WHERE LOOKUP_TYPE = 'LOOKUP TYPE'

               AND ENABLED_FLAG = 'Y'

               AND TRUNC(START_DATE_ACTIVE) <= TRUNC(SYSDATE)

               AND (END_DATE_ACTIVE IS NULL OR

                   TRUNC(END_DATE_ACTIVE) >= TRUNC(SYSDATE)));

  type t_values is table of number(20) INDEX BY pls_integer; --varchar2(20);

  a_values t_values;

  lv_num integer := 1;

begin

  -- Test statements here

  /*OPEN CUR;

  FETCH CUR BULK COLLECT INTO a_values;--l_column_list;

  CLOSE CUR;*/

  for c_cursor in cur loop

    a_values(c_cursor.TRANSACTION_TYPE_ID) := c_cursor.TRANSACTION_TYPE_ID;

  end loop;

  DBMS_OUTPUT.put_line(a_values.count);

  begin

    IF a_values(lv_num) = lv_num THEN

      DBMS_OUTPUT.put_line(lv_num);

    end if;

  exception

    when others then

      DBMS_OUTPUT.put_line('error');

  end;

end;

/*2

86

123*/

NESTED TABLE TYPE 

DECLARE

  TYPE type_cd IS TABLE OF NUMBER;

  v_my_list type_cd;

  lv_bum number := 86;

begin

  BEGIN

 

    select TRANSACTION_TYPE_ID --LISTAGG(TRANSACTION_TYPE_ID, '|') WITHIN GROUP(ORDER BY 1)

           bulk collect

      into v_my_list

    --  INTO lv_trans_types_ids

      from MTL_TRANSACTION_TYPES

     where TRANSACTION_TYPE_NAME IN

           (select MEANING

              from FND_LOOKUP_VALUES_VL

             WHERE LOOKUP_TYPE = 'LOOKUP TYPE'

               AND ENABLED_FLAG = 'Y'

               AND TRUNC(START_DATE_ACTIVE) <= TRUNC(SYSDATE)

               AND (END_DATE_ACTIVE IS NULL OR

                   TRUNC(END_DATE_ACTIVE) >= TRUNC(SYSDATE)));

    --  lv_trans_types_ids:='^('||lv_trans_types_ids||')$';         

  EXCEPTION

    WHEN OTHERS THEN

      v_my_list := NULL;

  END;

  IF  lv_bum MEMBER OF v_my_list THEN

 

    dbms_output.put_line('chida');

  END IF;

END;