--------------------------------------------------------
--  DDL for View REQUEST_FLOW_ACTION
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "ENVX_OPER"."REQUEST_FLOW_ACTION" ("ID_ACTION", "ACTION_NAME", "ID_ACTION_CAT", "STATUS_RESULT", "STEP_ORDER", "ID_STEP_FLOW", "DEPLOY_ENABLE", "ID_NEXT_STEP", "ID_REQUEST", "ID_DEPLOYMENT_TYPE", "ID_ENVIROMENT_TYPE", "ID_USER", "USER_NAME", "ID_ROLE", "ID_SYS_PROFILE") AS 
  SELECT act.id_action,
       act.action_name,
       act.id_action_cat,
       act.status_result,
       step.step_order,
       step.id_step_flow,
       step.deploy_enable,
       nvl(act.id_step_go, pkg_workflow_coordinator.next_step_flow(act.id_action, act.id_step_flow))                  AS id_next_step,
       exec.id_request,
       flow.id_deployment_type,
       flow.id_enviroment_type,
       usr.id_user,
       upper(usr.user_name)                                                                                           AS user_name,
       usr.id_role,
       usr.id_sys_profile
  FROM request_action_flow    act,
       request_step_workflow  step,
       request_workflow       flow,
       user_roles             usr,
       request_exec_flow      exec
 WHERE act.action_status = 1
   AND step.step_status            = 1
   AND flow.request_flow_status    = 1
   AND step.id_req_flow            = flow.id_req_flow
   AND step.id_step_flow           = act.id_step_flow
   AND exec.id_step_flow           = step.id_step_flow
   AND exec.status_result IS NULL
   AND ( step.id_sys_profile IN (
	SELECT p.level_real_id AS id_sys_profile
	  FROM hierarchical_role_profiles p START WITH
		p.level_id = 'P'||usr.id_sys_profile
	CONNECT BY
		PRIOR level_id = parent_id
)
    OR usr.id_sys_profile IN (
	SELECT id_sys_profile
	  FROM request_sw_auxiliar_profile
	 WHERE id_step_flow = step.id_step_flow
) )
UNION ALL
SELECT act.id_action,
       act.action_name,
       act.id_action_cat,
       act.status_result,
       step.step_order,
       step.id_step_flow,
       step.deploy_enable,
       nvl(act.id_step_go, pkg_workflow_coordinator.next_step_flow(act.id_action, act.id_step_flow))                  AS id_next_step,
       req.id_request,
       flow.id_deployment_type,
       flow.id_enviroment_type,
       usr.id_user,
       upper(usr.user_name)                                                                                           AS user_name,
       usr.id_role,
       usr.id_sys_profile
  FROM request_action_flow    act,
       request_step_workflow  step,
       request_workflow       flow,
       user_roles             usr,
       deployment_request     req
 WHERE act.action_status = 1
   AND step.step_status            = 1
   AND flow.request_flow_status    = 1
   AND step.step_order             = 1
   AND step.id_req_flow            = flow.id_req_flow
   AND step.id_step_flow           = act.id_step_flow
   AND flow.id_deployment_type     = req.id_deployment_type
   AND flow.id_enviroment_type     = req.id_enviroment_type
   AND NOT EXISTS (
	SELECT exec.id_request
	  FROM request_exec_flow exec
	 WHERE exec.id_request = req.id_request
)
   AND ( step.id_sys_profile IN (
	SELECT p.level_real_id AS id_sys_profile
	  FROM hierarchical_role_profiles p START WITH
		p.level_id = 'P'||usr.id_sys_profile
	CONNECT BY
		PRIOR level_id = parent_id
)
    OR usr.id_sys_profile IN (
	SELECT id_sys_profile
	  FROM request_sw_auxiliar_profile
	 WHERE id_step_flow = step.id_step_flow
) )
;