--------------------------------------------------------
--  DDL for View REQUEST_USER_TRAY
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "ENVX_OPER"."REQUEST_USER_TRAY" ("ID_REQUEST", "USER_NAME", "CREATION_DATE", "DEPLOYMENT_TYPE", "ENVIROMENT_TYPE", "ENVIROMENT_CLASS", "STEP", "Q_ENV_HGROUP", "Q_ENV", "Q_OBJECT", "STATUS", "ID_ENVIROMENT_TYPE", "ID_DEPLOYMENT_TYPE", "ID_LAST_SEQ_STEP", "PROJECT_TITTLE", "USE_CASE", "REQUEST_TITTLE") AS 
  SELECT dr.id_request                                                    AS numero_orden,
          dr.user_create                                                   AS usuario,
          dr.creation_date                                                 AS fecha_creacion,
          (
             SELECT deployment_type
               FROM deployment_type d
              WHERE d.id_deployment_type = dr.id_deployment_type
          )                                                                AS tipo_de_despliegue,
          (
             SELECT enviroment_type_name
               FROM enviroment_type e
              WHERE e.id_enviroment_type = dr.id_enviroment_type
          )                                                                AS tipo_de_ambiente,
          (
             SELECT decode(enviroment_type_class, 0, 'No Productivo', 1, 'Productivo', '') AS env
               FROM enviroment_type e
              WHERE e.id_enviroment_type = dr.id_enviroment_type
          )                                                                AS enviroment_class,
          'PASO'                                                           AS step,
          (
             SELECT COUNT(1)
               FROM deployment_req_hgroup hg
              WHERE hg.id_request           = dr.id_request
                AND hg.id_parent_req_hgroup = 'R' || dr.id_request
          )                                                                AS cant_hgroup,
          (
             SELECT COUNT(1)
               FROM deployment_req_env re
              WHERE re.id_request = dr.id_request
          )                                                                AS cant_env,
          (
             SELECT SUM(q.q_obj_env) total_obj_request
               FROM (
                       SELECT rv.id_req_hgroup,
                              COUNT(1) * (SELECT COUNT(1) FROM deployment_req_object ro WHERE ro.id_req_hgroup LIKE rv.id_req_hgroup || '%') AS q_obj_env
                         FROM deployment_req_hgroup rh,
                              deployment_req_env rv
                        WHERE rh.id_req_hgroup = rv.id_req_hgroup
                          AND rh.id_request    = dr.id_request
                        GROUP BY rv.id_req_hgroup
                    ) q
          )                                                                AS cant_object,
          dr.request_status,
          dr.id_enviroment_type,
          dr.id_deployment_type,
          dr.id_last_seq_step,
          (SELECT tittle FROM project p WHERE p.id_project = dr.id_project) AS project_tittle,
          dr.use_case,
          dr.tittle                                                           AS request_tittle
     FROM deployment_request dr
    ORDER BY dr.creation_date
;