--------------------------------------------------------
--  DDL for View HIERARCHICAL_REQUEST_AUDIT_VEW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "ENVX_OPER"."HIERARCHICAL_REQUEST_AUDIT_VEW" ("LEVEL_ALIAS", "LEVEL_ID", "PARENT_ID", "GROUP_ID", "LEVEL_PATH_ID", "LEVEL_ORDER", "LEVEL_NAME", "LEVEL_STATUS", "DESCRIPTION", "LEVEL_REAL_ID") AS 
  SELECT
    'R' AS level_alias,
    'R'||r.id_request AS level_id, -- 'R'||id_request
    NULL AS parent_id,
    r.id_request AS group_id,
    to_char(r.id_request) AS level_path_id,
    0 AS level_order,
    'Solicitud: '
    || (
        SELECT
            hg.req_hgroup_name
        FROM
            deployment_req_hgroup hg
        WHERE
            hg.id_request = r.id_request
            AND hg.id_req_hgroup_level = 'R'
    ) AS level_name,
    NULL AS level_status,
    NULL AS description,
    to_char(r.id_request) AS level_real_id
               -------------------- FUNCTIONAL INFO -----------------------------
                -----------------------------------------------------------------
FROM
    deployment_request r
UNION ALL
SELECT UNIQUE
    'S' AS level_alias,
    'S'||sw.id_step_flow   AS level_id, 
    'R'||rf.id_request     AS parent_id,
    rf.id_request AS group_id,
    rf.id_request
    || ','
    || sw.id_step_flow AS level_path_id,
    sw.step_order     AS level_order,
    'Paso: ' || sw.step_name AS level_name,
    NULL AS level_status,
    NULL AS description,
    to_char(sw.id_step_flow) AS level_real_id
               -------------------- FUNCTIONAL INFO -----------------------------
                -----------------------------------------------------------------
FROM
    request_exec_flow       rf,
    request_workflow        wf,
    request_step_workflow   sw
WHERE
    rf.id_step_flow = sw.id_step_flow
    AND sw.id_req_flow = wf.id_req_flow
ORDER BY
    6,
    5
;