Concurrent Jobs completed through Scheduled

  SELECT b.user_name,

         a.USER_CONCURRENT_PROGRAM_NAME,

         a.Program,

         ROUND (

             (  (a.actual_completion_date - a.actual_start_date)

              * 24

              * 60

              * 60

              / 60),

             2)

             AS Process_time,

         a.request_id,

         TO_CHAR (a.request_date, 'DD-MON-YY HH24:MI:SS')

             AS Request_Date,

         TO_CHAR (a.actual_start_date, 'DD-MON-YY HH24:MI:SS')

             AS Actual_Start_Date,

         TO_CHAR (a.actual_completion_date, 'DD-MON-YY HH24:MI:SS')

             AS Actual_Completion_Date,

         ROUND ((a.actual_completion_date - a.request_date) * 24 * 60 * 60, 2)

             AS end_to_end,

         a.phase_code,

         a.status_code,

         a.argument_text,

         a.completion_text

    FROM apps.fnd_conc_req_summary_v a, apps.fnd_user b

   WHERE     a.requested_by = b.user_id

         AND (a.ACTUAL_COMPLETION_DATE) BETWEEN '<start date>' AND '<end date>'

         AND a.status_code = 'C'

         AND (   (    is_sub_request = 'Y'

                  AND request_type = 'P'

                  AND request_id <> priority_request_id

                  AND EXISTS

                          (SELECT 1

                             FROM fnd_concurrent_requests fc

                            WHERE     fc.request_id = a.priority_request_id

                                  AND fc.parent_request_id != -1))

              OR (    is_sub_request = 'N'

                  AND request_id = priority_request_id

                  AND parent_request_id != -1))

         AND b.user_name NOT LIKE 'XX__%' ESCAPE '_'

ORDER BY request_id DESC;

Oracle PL/SQL - PL SQL Cursor CURSOR Expressions

A CURSOR expression returns a nested cursor.

 It has this syntax:

 CURSOR ( subquery )

You can use a CURSOR expression in a SELECT statement or pass it to a function.

 You cannot use a cursor expression with an implicit cursor.

 

The following code declares and defines an explicit cursor for a query that includes a cursor expression.

/* Formatted on 3/16/2023 1:41:03 PM (QP5 v5.388) */
CREATE TABLE emp
(
    empid             NUMBER (6),
    first_name        VARCHAR2 (20),
    last_name         VARCHAR2 (25),
    email             VARCHAR2 (25),
    phone_number      VARCHAR2 (20),
    hire_date         DATE,
    job_id            VARCHAR2 (10),
    salary            NUMBER (8, 2),
    commission_pct    NUMBER (2, 2),
    manager_id        NUMBER (6),
    department_id     NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:11 PM (QP5 v5.388) */
INSERT INTO emp
     VALUES (100,
             'Steven',
             'King',
             'SKING',
             '123.123.4567',
             TO_DATE ('17-JUN-1987', 'dd-MON-yyyy'),
             'CODER',
             24000,
             NULL,
             NULL,
             10);
INSERT INTO emp
     VALUES (200,
             'Joe',
             'Lee',
             'abc',
             '123.123.9999',
             TO_DATE ('17-JUN-1980', 'dd-MON-yyyy'),
             'TESTER',
             25000,
             NULL,
             NULL,
             20);

/* Formatted on 3/16/2023 1:41:17 PM (QP5 v5.388) */
CREATE TABLE departments
(
    department_id      NUMBER (4),
    department_name    VARCHAR2 (30) CONSTRAINT dept_name_nn NOT NULL,
    manager_id         NUMBER (6),
    location_id        NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:20 PM (QP5 v5.388) */
INSERT INTO departments
     VALUES (10,
             'Administration',
             200,
             1700);
INSERT INTO departments
     VALUES (20,
             'Marketing',
             201,
             1000);
INSERT INTO departments
     VALUES (30,
             'Purchasing',
             114,
             1700);

INSERT INTO departments

     VALUES (40,
             'Human Resources',
             203,
             1000);

INSERT INTO departments

     VALUES (50,
             'Shipping',
             121,
             1700);

 

 DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
     emp_cur    emp_cur_typ;
     dept_name  departments.department_name%TYPE;
     emp_name   emp.last_name%TYPE;
     CURSOR c1 IS
       SELECT department_name,
         CURSOR ( SELECT e.last_name
                 FROM emp e
                 WHERE e.department_id = d.department_id
                 ORDER BY e.last_name
                 ) emp
       FROM departments d
       ORDER BY department_name;
 BEGIN
     OPEN c1;
     LOOP 
       FETCH c1 INTO dept_name, emp_cur;
       EXIT WHEN c1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
       LOOP 
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
       END LOOP;
     END LOOP;
     CLOSE c1;
 END;