EGO_CO_APPROVER_REVIEWR

Details

Schema: FUSION
Object owner: EGO
Object type: VIEW

Columns

Name
CHANGE_ID CHANGE_APPROVER CHANGE_REVIEWER STEP_ACTIVITY_TYPE

Name

CHANGE_ID

CHANGE_APPROVER

CHANGE_REVIEWER

STEP_ACTIVITY_TYPE

Query

SQL_Statement
select distinct change_id CHANGE_ID ,assignee_id CHANGE_APPROVER, assignee_id CHANGE_REVIEWER , STEP_ACTIVITY_TYPE from ( select lcs.entity_id1 change_id, rp.assignee_id,step.STEP_ACTIVITY_TYPE from ego_lifecycle_statuses lcs, ego_routes routes, ego_route_steps step, ego_route_people rp where lcs.active_flag = 'Y' and routes.object_pk1 = lcs.entity_id1 and routes.OBJECT_PK2 = lcs.CHANGE_LIFECYCLE_STATUS_ID and step.object_pk1 = routes.object_pk1 and step.route_id = routes.route_id and step.TASK_STAGE_TYPE = 'EGO_HEADER_STAGE' and rp.object_pk1 = step.object_pk1 and rp.step_id = step.step_id and rp.assignee_type_code='EGO_PERSON' and (rp.response_code is null or rp.response_code <> 'EGO_REASSIGNED') union all select lcs.entity_id1 change_id, pusers.user_guid assignee_id, step.STEP_ACTIVITY_TYPE from ego_lifecycle_statuses lcs, ego_routes routes, ego_route_steps step, ego_route_people rp, per_roles_dn proles, PER_USER_ROLES user_roles, per_users pusers where lcs.active_flag = 'Y' and routes.object_pk1 = lcs.entity_id1 and routes.OBJECT_PK2 = lcs.CHANGE_LIFECYCLE_STATUS_ID and step.object_pk1 = routes.object_pk1 and step.route_id = routes.route_id and step.TASK_STAGE_TYPE = 'EGO_HEADER_STAGE' and rp.object_pk1 = step.object_pk1 and rp.step_id = step.step_id and rp.assignee_type_code='EGO_ROLE' and proles.role_guid=rp.assignee_id and user_roles.role_id=proles.role_id and pusers.user_id = user_roles.user_id and not exists (select 1 from ego_route_people rpInner where rpInner.step_id = step.step_id and rpInner.assignee_id = pusers.user_guid and rpInner.assignee_type_code='EGO_PERSON') )

SQL_Statement

select distinct change_id CHANGE_ID ,assignee_id CHANGE_APPROVER, assignee_id CHANGE_REVIEWER , STEP_ACTIVITY_TYPE from (

select lcs.entity_id1 change_id, rp.assignee_id,step.STEP_ACTIVITY_TYPE

from ego_lifecycle_statuses lcs, ego_routes routes, ego_route_steps step, ego_route_people rp

where lcs.active_flag = 'Y'

and routes.object_pk1 = lcs.entity_id1

and routes.OBJECT_PK2 = lcs.CHANGE_LIFECYCLE_STATUS_ID

and step.object_pk1 = routes.object_pk1

and step.route_id = routes.route_id

and step.TASK_STAGE_TYPE = 'EGO_HEADER_STAGE'

and rp.object_pk1 = step.object_pk1

and rp.step_id = step.step_id

and rp.assignee_type_code='EGO_PERSON'

and (rp.response_code is null or rp.response_code <> 'EGO_REASSIGNED')

union all

select lcs.entity_id1 change_id, pusers.user_guid assignee_id, step.STEP_ACTIVITY_TYPE

from ego_lifecycle_statuses lcs, ego_routes routes, ego_route_steps step, ego_route_people rp, per_roles_dn proles, PER_USER_ROLES user_roles, per_users pusers

where lcs.active_flag = 'Y'

and routes.object_pk1 = lcs.entity_id1

and routes.OBJECT_PK2 = lcs.CHANGE_LIFECYCLE_STATUS_ID

and step.object_pk1 = routes.object_pk1

and step.route_id = routes.route_id

and step.TASK_STAGE_TYPE = 'EGO_HEADER_STAGE'

and rp.object_pk1 = step.object_pk1

and rp.step_id = step.step_id

and rp.assignee_type_code='EGO_ROLE'

and proles.role_guid=rp.assignee_id

and user_roles.role_id=proles.role_id

and pusers.user_id = user_roles.user_id

and not exists (select 1 from ego_route_people rpInner

where rpInner.step_id = step.step_id and rpInner.assignee_id = pusers.user_guid

and rpInner.assignee_type_code='EGO_PERSON')

)