Extracting Menu Structures using SQL

Menus that are assigned to Oracle EBS responsibilities are often nested structures which contain lots of levels. The SQLs on this page explore that a bit.

Here are some basic details about menus assigned to responsibilities:

-- ##############################################################################
--      RESPONSIBILITIES ASSIGNED TO MENUS 
-- ##############################################################################

    SELECT fat.application_name
         , fa.application_short_name
         , frt.responsibility_id
         , frt.responsibility_name
         , fmt.user_menu_name
      FROM applsys.fnd_responsibility fr
      JOIN applsys.fnd_responsibility_tl frt ON fr.application_id =    frt.application_id 
      JOIN applsys.fnd_menus_tl fmt          ON fr.responsibility_id = frt.responsibility_id AND fr.menu_id = fmt.menu_id
      JOIN applsys.fnd_application fa        ON frt.application_id =   fa.application_id 
      JOIN applsys.fnd_application_tl fat    ON fr.application_id =    fa.application_id AND fa.application_id = fat.application_id 
     WHERE 1 = 1
       AND NVL(fr.end_date, SYSDATE + 1) > SYSDATE -- responsibility is active
       AND fmt.user_menu_name = 'PO_NAVIGATION'
  ORDER BY fa.application_short_name
         , frt.responsibility_name;

-- ##############################################################################
--       COUNT OF RESPONSIBILITIES ASSIGNED TO A MENU
-- ##############################################################################

SELECT fmt.user_menu_name
     , COUNT(DISTINCT frt.responsibility_id) count
  FROM applsys.fnd_responsibility fr
  JOIN applsys.fnd_responsibility_tl frt ON fr.responsibility_id = frt.responsibility_id AND fr.application_id = frt.application_id 
  JOIN applsys.fnd_menus_tl fmt ON fr.menu_id = fmt.menu_id
 WHERE fmt.user_menu_name = 'INV_NAVIGATE'
GROUP BY fmt.user_menu_name;

This SQL is useful because you can enter in the Menu Name, and the SQL will export the menu structure, showing menus, functions and prompts. The CONNECT BY is used to extract the menu levels / hierarchy.

-- ##############################################################################
--      MENU TREE VIEW
-- ##############################################################################
           SELECT fmev.entry_sequence seq
                , LEVEL
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmv.user_menu_name menu
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
                , fmv.menu_name
                , fmv.user_menu_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT user_function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END user_function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT vvv.description
                           FROM apps.fnd_form_functions_vl vvv
                          WHERE function_id = fmev.function_id)
                  END function_description
                , fmev.description entry_description
             FROM apps.fnd_menus_vl fmv
             JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
            WHERE 1 = 1
              AND fmev.prompt IS NOT NULL
--              AND LEVEL = 1
       CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       START WITH fmv.user_menu_name = 'PO_NAVIGATION'
ORDER SIBLINGS BY fmev.entry_sequence;

If you want a “flat” view of a menu, with just the top level menu / function details as you see on the SysAdmin Menu form, this can be used.

-- ##############################################################################
--      MENU FLAT VIEW
-- ##############################################################################
SELECT fmv.menu_name
     , fmev.entry_sequence seq
     , fmev.prompt
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT user_menu_name FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu                   
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT user_function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END user_fcn_name
     , fmev.description
  FROM apps.fnd_menus_vl fmv
  JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
   AND user_menu_name = 'PO_NAVIGATION'
   AND 1 = 1;

Here are some more variations, looking only at menus instead of menus and functions together.

-- ##############################################################################
--      MENU TREE WALK EXCLUDING FUNCTIONS
-- ##############################################################################

-- v1

    SELECT LPAD(' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
         , LEVEL
         , fmev.entry_sequence
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
       AND fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmv.menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence ;

-- v2

    SELECT LPAD(' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
         , LEVEL
         , fmev.entry_sequence
         , fmt.user_menu_name
         , fmev.creation_date
         , fu.user_name
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
 LEFT JOIN applsys.fnd_menus_tl fmt ON fmv.menu_id = fmt.menu_id 
      JOIN applsys.fnd_user fu ON fmev.created_by = fu.user_id
     wHERE fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmt.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

Here are lots more menu tree walking examples.

-- ##############################################################################
--      MORE MENU TREE WALKING EXAMPLES
-- ##############################################################################

-- v1

    SELECT fmev.entry_sequence seq
         , LEVEL
         , LPAD(' ', (LEVEL - 1) * 4, ' ') || fmev.prompt prompt
      FROM apps.fnd_menus_vl fmv
                 JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id          = fmv.menu_id
           LEFT JOIN apps.fnd_responsibility_vl frv ON frv.menu_id           = fmv.menu_id
           LEFT JOIN apps.fnd_menus_vl fmv2         ON fmv2.menu_id          = fmev.sub_menu_id
           LEFT JOIN apps.fnd_resp_functions frf    ON frf.responsibility_id = frv.responsibility_id AND frf.action_id = fmev.sub_menu_id
     WHERE fmev.prompt IS NOT NULL
       AND frf.responsibility_id IS NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       AND fmev.prompt IS NOT NULL
       AND frf.responsibility_id IS NULL
START WITH frv.responsibility_name = 'System Administrator'
ORDER SIBLINGS BY fmev.entry_sequence;

-- v2

    SELECT fmev.entry_sequence seq
         , LPAD(' ', (LEVEL - 1) * 4, ' ') || fmev.prompt prompt
         , fmev.menu_id
         , fmev.sub_menu_id         
         , fmev.function_id
      FROM apps.fnd_menus_vl fmv
      JOIN applsys.fnd_menus_tl fmt ON fmv.menu_id = fmt.menu_id
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
 LEFT JOIN apps.fnd_responsibility_vl frv ON fmv.menu_id = frv.menu_id
     WHERE fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       AND fmev.prompt IS NOT NULL
START WITH fmt.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;
  
-- v3

      SELECT fmev.entry_sequence seq
           , LPAD(' ', (LEVEL - 1) * 10, ' ') || fmev.prompt prompt
           , fmv.menu_name
           , CASE
                 WHEN fmev.function_id IS NOT NULL
                    THEN 'Function'
                 ELSE 'Menu'
              END m_f        
           , CASE
                 WHEN fmev.function_id IS NOT NULL
                    THEN (SELECT 'Function: ' || function_name
                            FROM apps.fnd_form_functions_vl
                           WHERE function_id = fmev.function_id)
                 ELSE (SELECT 'Menu: ' || menu_name
                         FROM apps.fnd_menus_vl e
                        WHERE e.menu_id = fmev.sub_menu_id)
              END this
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmv.user_menu_name = 'CST_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

-- v4
-- https://community.oracle.com/thread/535595

    SELECT LEVEL
         , fm.menu_id
         , fm.menu_name
         , fm.user_menu_name
         , fme.sub_menu_id
         , fme.function_id
         , fff.function_name
         , fff.user_function_name
      FROM apps.fnd_menus_vl fm
         , apps.fnd_menu_entries fme
         , apps.fnd_form_functions_vl fff
     WHERE fme.menu_id = fm.menu_id
       AND fff.function_id(+) = fme.function_id
CONNECT BY fm.menu_id = PRIOR fme.sub_menu_id
START WITH fm.user_menu_name = 'INV_NAVIGATE';

-- v5
-- https://community.oracle.com/thread/1057001

       SELECT LPAD(' ',(LEVEL - 1) * 20)
           || TRIM(user_menu_name)
           || ' -> '
           || CASE
                 WHEN me.function_id IS NOT NULL
                    THEN (SELECT user_function_name
                            FROM apps.fnd_form_functions_vl
                           WHERE function_id = me.function_id)
                 ELSE (SELECT user_menu_name
                         FROM apps.fnd_menus_vl e
                        WHERE e.menu_id = me.sub_menu_id)
              END nam
      FROM apps.fnd_menus_vl fm
         , apps.fnd_menu_entries me
     WHERE me.menu_id = fm.menu_id
START WITH fm.user_menu_name = 'INV_NAVIGATE'
CONNECT BY PRIOR me.sub_menu_id = fm.menu_id;

-- v6
-- another one

    SELECT m.menu_name
         , m.user_menu_name
         , m.sub_menu
         , LEVEL
         , SYS_CONNECT_BY_PATH(m.sub_menu, '/') PATH
      FROM (SELECT fmv.menu_name
                 , fmv.user_menu_name
                 , fmv.user_menu_name sub_menu
                 , fmev.sub_menu_id
                 , fmv.menu_id
              FROM apps.fnd_menu_entries_vl fmev
                 , apps.fnd_menus_vl fmv
             WHERE fmv.menu_id = fmev.menu_id) m
CONNECT BY PRIOR sub_menu_id = menu_id
START WITH user_menu_name = 'INV_NAVIGATE';

HR Records – Supervisor Hierarchy

These are some SQLs I used to use in a job where we used HR, and used the standard supervisor hierarchy.

Sections:

  1. HR Supervisor Hierarchy
  2. Staff List with Supervisor Loop

HR Supervisor Hierarchy

If you use the HR Supervisor Hierarchy, there are lots of different SQLs you can use to extract information to do with that kind of thing.

The key thing to note in the SQL is that this bit of SQL:

CONNECT BY PRIOR person_id = supervisor_id
    START WITH employee_number = :empno;

Means that when you run the code, you need to enter the employee number of the most senior person, and the SQL will loop through the records and show the chain going down to the most junior.

If you change the “…CONNECT BY…” to this:

CONNECT BY PRIOR supervisor_id = person_id

You can enter the employee number of the most junior person, and it’ll loop through with them at the top and go down to the most senior at the bottom.

Staff List with Supervisor Loop

Enter a junior’s employee number, to loop through all the way through to the big cheese boss person at the top of the greasy pole!

/*##############################################################################
#   STAFF LIST WITH SUPERVISOR CHECKING
#   http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=599866
/*############################################################################*/

        SELECT e.bus_gp
             , e.full_name
             , e.employee_number empno
             , LEVEL
             , SYS_CONNECT_BY_PATH(e.last_name, '/') PATH
             , e.limit_req_min
             , e.limit_req_max
             , e.limit_po_min
             , e.limit_po_max
             , e.limit_cpa_min
             , e.limit_cpa_max
             , e.job
             , e.user_name
             , e.end_date
             , e.email_address
             , e.user_location
             , e.hr_org
             , e.default_charge_account
             , e.personid
          FROM (SELECT ppf.person_id
                     , ppf.employee_number
                     , ppf.full_name
                     , ppf.last_name
                     , ppf.person_id personid
                     , hlat.location_code user_location
                     , haou.NAME hr_org
                     , paf.supervisor_id
                     , pj.NAME job
                     , ppf.email_address
                     , fu.user_name
                     , fu.end_date
                     , pg.NAME grade
                     , bus_gp.NAME bus_gp
                     , gcc.concatenated_segments default_charge_account
                     , (SELECT min(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'REQUISITION') limit_req_min
                     , (SELECT max(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'REQUISITION') limit_req_max
                     , (SELECT min(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PO') limit_po_min
                     , (SELECT max(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PO') limit_po_max
                     , (SELECT min(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PA') limit_cpa_min
                     , (SELECT max(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PA') limit_cpa_max
                  FROM hr.per_all_people_f ppf
                  JOIN hr.per_all_assignments_f paf             ON ppf.person_id =                      paf.person_id
             LEFT JOIN hr.per_jobs pj                           ON paf.job_id =                         pj.job_id
             LEFT JOIN hr.per_grades pg                         ON paf.grade_id =                       pg.grade_id
             LEFT JOIN hr.hr_all_organization_units haou        ON paf.organization_id =                haou.organization_id
             LEFT JOIN hr.hr_locations_all_tl hlat              ON paf.location_id =                    hlat.location_id
             LEFT JOIN applsys.fnd_user fu                      ON paf.person_id =                      fu.employee_id
             LEFT JOIN hr.hr_all_organization_units_tl bus_gp   ON paf.business_group_id =              bus_gp.organization_id
             LEFT JOIN apps.gl_code_combinations_kfv gcc        ON paf.default_code_comb_id =           gcc.code_combination_id
                 WHERE 1 = 1
                   AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
                   AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
                   AND paf.primary_flag = 'Y'
                   AND paf.assignment_type = 'E'
                   AND 1 = 1) e
    CONNECT BY PRIOR person_id = supervisor_id
    START WITH employee_number = 123456;

Here are some different versions, which are more cut down. Start with the employee number of the most senior person.

-- ##############################################################################
--   HR Manager Looping SQL
-- ############################################################################*/
    SELECT     LPAD(' ', (LEVEL - 1) * 10, ' ') || h.person_id person_id
             , LEVEL
             , f.full_name
             , pg.name gd
             , f.employee_number empno
             , u.user_name user_name
             , u.description
             , pj.NAME job_title
             , haou.name hr_org
          FROM hr.per_all_people_f f
          JOIN hr.per_all_assignments_f h           ON f.person_id = h.person_id
     LEFT JOIN hr.hr_all_organization_units haou    ON h.organization_id = haou.organization_id
     LEFT JOIN hr.per_jobs pj                       ON h.job_id = pj.job_id
     LEFT JOIN hr.per_grades pg                     ON h.grade_id = pg.grade_id
     LEFT JOIN applsys.fnd_user u                   ON h.person_id = u.employee_id
    START WITH SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND f.employee_number = :employee_number
    CONNECT BY PRIOR h.person_id = supervisor_id
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date;

    -- trimmed version HR tables only
    -- start with employee number of person at the top of the chain (e.g. most senior person)
    SELECT     LPAD(' ', (LEVEL - 1) * 10, ' ') || h.person_id person_id
             , LEVEL
             , h.person_id
             , h.supervisor_id
             , f.full_name
             , f.employee_number
          FROM hr.per_all_people_f f
          JOIN hr.per_all_assignments_f h ON  f.person_id = h.person_id
    START WITH SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND f.employee_number = :employee_number
    CONNECT BY PRIOR h.person_id = h.supervisor_id
           AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date;

EAM Assets

-- ##############################################################################
--       EAM ASSETS
-- ############################################################################*/

-- version 1

    SELECT inventory_item_id
         , serial_number
         , parent_item_id
         , parent_serial_number
         , descriptive_text
         , concatenated_segments
         , asset_group_description
         , wip_accounting_class_code
         , owning_department_id
         , owning_department
         , maintained_unit
      FROM apps.mtl_eam_asset_numbers_all_v
     WHERE 1 = 1
    --   AND descriptive_text = 'BUILDING 00001'
       AND lower(descriptive_text) like '%dining%'
    --   AND parent_item_id = 174738
    --   AND inventory_item_id = 174738
       AND 2 = 2;

-- version 2

    SELECT nm.inventory_item_id
         , nm.serial_number
         , nm.descriptive_text
         , nm.current_organization_id
         , nm.creation_date
         , nm.concatenated_segments
         , nm.eam_item_type
         , nm.asset_group_description
         , nm.maintainable_flag
         , nm.area_id
         , nm.area
         , nm.owning_department
         , nm.parent_serial_number
         , nm.parent_item_id
--         , nm.
      FROM apps.mtl_eam_asset_numbers_all_v nm
--      JOIN apps.mtl_eam_asset_attr_values_v attr ON attr.inventory_item_id = nm.inventory_item_id AND attr.organization_id = nm.current_subinventory_code
     WHERE 1 = 1
--       AND LOWER (descriptive_text) LIKE '%hall%'
       AND nm.serial_number IN ('ABC123')
  ORDER BY nm.creation_Date DESC;
       
-- ##############################################################################
--       EAM ASSETS - HIERARCHY
-- ############################################################################*/

    SELECT LPAD (' ', (LEVEL - 1) * 5, '_') || serial_number serial_num
         , LEVEL
         , serial_number
         , descriptive_text
         , asset_group_description
         , owning_department
         , parent_serial_number
         , parent_item_id
      FROM apps.mtl_eam_asset_numbers_all_v
START WITH serial_number = '000'
CONNECT BY PRIOR serial_number = parent_serial_number;

    SELECT COUNT(*)
      FROM apps.mtl_eam_asset_numbers_all_v
      WHERE LEVEL > 1
START WITH serial_number = '102AA'
CONNECT BY PRIOR serial_number = parent_serial_number;

SELECT     LEVEL
         , LPAD(' ', (LEVEL - 1) * 5, ' ') || serial_number asset
         , descriptive_text
      FROM inv.mtl_serial_numbers msn
     WHERE 1 = 1
CONNECT BY msn.parent_serial_number = PRIOR msn.serial_number
START WITH serial_number = 'ABC123';

Concurrent Requests – Hierarchy

Often if you look in the fnd_concurrent_requests table you’ll see that for some requests, the parent_request_id column is populated.

This can be the case if a concurrent request is part of a Request Set, where a series of jobs run, one after the other.

At other times, one request can launch / or spawn child requests. A common example in Release 12 of such a request is the Create Accounting job.

You can use the Connect By Prior condition of a Hierarchical Query to extract the hierarchy of a set of requests.

For example – this is sample output for the Create Accounting job:

Here are the 2 versions of the SQL, one using Oracle syntax, the other using ANI syntax.

Oracle Syntax SQL

-- ##############################################################################
--      HIERARCHY
-- ############################################################################*/

SELECT   LPAD(' ', (LEVEL - 1) * 10, ' ') || fcr.request_id id
       , LEVEL
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , 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
       , fcr.actual_start_date start_ 
       , fcr.actual_completion_date end_
       , fcr.argument_text
       , fu.user_name
       , fcr.completion_text
    FROM applsys.fnd_concurrent_requests fcr
       , applsys.fnd_user fu
       , applsys.fnd_concurrent_programs_tl fcpt
   WHERE fcr.requested_by = fu.user_id
     AND fcr.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcpt.application_id
     AND fcr.phase_code != 'P'   
START WITH fcr.request_id = 20491922
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;

ANSI Syntax SQL

-- ##############################################################################
--       HIERARCHY
-- ############################################################################*/

SELECT   LPAD(' ', (LEVEL - 1) * 10, ' ') || fcr.request_id id
       , LEVEL
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , 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
       , fcr.actual_start_date start_ 
       , fcr.actual_completion_date end_
       , fcr.argument_text
       , fcr.request_type
       , fu.user_name
       , (replace(replace(fcr.completion_text,chr(10),''),chr(13),' ')) completion_text
    FROM applsys.fnd_concurrent_requests fcr
    JOIN applsys.fnd_user fu ON fcr.requested_by = fu.user_id
    JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id = fcpt.concurrent_program_id
                                                AND fcr.program_application_id = fcpt.application_id
   WHERE fcr.phase_code != 'P'
START WITH fcr.request_id = 20491922
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;