set serveroutput on;
DECLARE
l number;
m number;
l_item_type varchar2(30) :='OEOL';
l_std_type varchar2(30) :='WFSTD';
   CURSOR c1 (p_item_type IN VARCHAR2)
   IS
      SELECT display_name,NAME
        FROM wf_item_types_tl
       WHERE NAME = p_item_type;

   CURSOR c2 (p_item_type IN VARCHAR2)
   IS
      SELECT *
        FROM wf_runnable_processes_v
       WHERE item_type = p_item_type;
    

   CURSOR c3 (p_item_type IN VARCHAR2, p_process_name IN VARCHAR2,p_std_type in varchar2)
   IS
      SELECT   a.process_name, e.description, b.NAME activity_name,
               d.description act_desc, c.TYPE process_type, b.TYPE activity_type,
               b.FUNCTION, substr(icon_geometry,1,(instr(a.icon_geometry,',')-1))
          FROM wf_process_activities a,
               wf_activities b,
               wf_activities c,
               wf_activities_tl d,
               wf_activities_tl e
         WHERE process_item_type=p_item_type
--and activity_item_type in (p_item_type,p_std_type)
           AND process_name = p_process_name
           AND a.activity_name = b.NAME
           AND process_item_type = b.item_type
           AND b.end_date IS NULL
           AND c.end_date IS NULL
           AND a.process_name = c.NAME
           AND c.item_type = a.process_item_type
           AND a.process_version = c.VERSION
           AND b.item_type = d.item_type
           AND b.NAME = d.NAME
           AND c.item_type = e.item_type
           AND c.NAME = e.NAME
           AND b.VERSION = d.VERSION
           AND c.VERSION = e.VERSION
      ORDER BY to_number(substr(a.icon_geometry,1,(instr(a.icon_geometry,',')-1)));

CURSOR c4 (p_item_type IN VARCHAR2, p_process_name IN VARCHAR2,p_std_type in varchar2)
   IS
      SELECT   a.process_name, e.description, b.NAME activity_name,
               d.description act_desc, c.TYPE process_type, b.TYPE activity_type,
               b.FUNCTION, substr(icon_geometry,1,(instr(a.icon_geometry,',')-1))
          FROM wf_process_activities a,
               wf_activities b,
               wf_activities c,
               wf_activities_tl d,
               wf_activities_tl e
         WHERE process_item_type=p_item_type
--and activity_item_type in (p_item_type,p_std_type)
           AND process_name = p_process_name
           AND a.activity_name = b.NAME
           AND process_item_type = b.item_type
           AND b.end_date IS NULL
           AND c.end_date IS NULL
           AND a.process_name = c.NAME
           AND c.item_type = a.process_item_type
           AND a.process_version = c.VERSION
           AND b.item_type = d.item_type
           AND b.NAME = d.NAME
           AND c.item_type = e.item_type
           AND c.NAME = e.NAME
           AND b.VERSION = d.VERSION
           AND c.VERSION = e.VERSION
      ORDER BY to_number(substr(a.icon_geometry,1,(instr(a.icon_geometry,',')-1)));
   assertion_failure   EXCEPTION;

   PROCEDURE pl (
      str         IN   VARCHAR2,
      len         IN   INTEGER := 80,
      expand_in   IN   BOOLEAN := TRUE
   )
   IS
      v_len   PLS_INTEGER     := LEAST (len, 255);
      v_str   VARCHAR2 (2000);
   BEGIN
      IF LENGTH (str) > v_len
      THEN
         v_str := SUBSTR (str, 1, v_len);
         DBMS_OUTPUT.put_line (v_str);
         pl (SUBSTR (str, len + 1), v_len, expand_in);
      ELSE
         v_str := str;
         DBMS_OUTPUT.put_line (v_str);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF expand_in
         THEN
            DBMS_OUTPUT.ENABLE (1000000);
            DBMS_OUTPUT.put_line (v_str);
         ELSE
            RAISE;
         END IF;
   END pl;

BEGIN
 l:=1;
 m:=1;
   FOR i IN c1 (l_item_type)
   LOOP
   pl('===========================================================');
   pl('ITEM TYPE : '||i.display_name);
   pl('===========================================================');
      FOR j IN c2 (i.name)
      LOOP
      pl('---------------------------------------------------------');
      pl('MAIN RUNNABLE PROCESS : ' ||j.display_name);
      pl('---------------------------------------------------------');
      l:=1;
         FOR k IN c3 (l_item_type, j.process_name,l_std_type)
          LOOP
          pl('---'||l||'.'||k.act_desc||'-'||k.function);
            IF k.process_type = 'PROCESS'
            THEN
             m:=1;
            FOR j IN c4 (l_item_type, k.activity_name,l_std_type)
            LOOP
              PL('----'||l||'.'||m||':'||j.act_desc);
                m:=m+1;
               END LOOP;
            END IF;
             l:=l+1;
         END LOOP;
      END LOOP;
   END LOOP;
END;