92 lines
3.4 KiB
MySQL
92 lines
3.4 KiB
MySQL
|
--------------------------------------------------------
|
||
|
|
-- 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
|
||
|
|
) )
|
||
|
|
;
|