1. Tables and Views for Project Management
  2. GMS_AWARDS_PER_PI_USER_V

GMS_AWARDS_PER_PI_USER_V

Details

  • Schema: FUSION

  • Object owner: GMS

  • Object type: VIEW

Columns

Name

ID

Query

SQL_Statement

SELECT AwardPersonnelEO.AWARD_ID AS ID

FROM GMS_AWARD_PERSONNEL AwardPersonnelEO,

(SELECT ProjectRoleTypePEO.PROJECT_ROLE_ID,

PerUserPEO.person_id

FROM PJF_PROJ_ROLE_TYPES_b ProjectRoleTypePEO,

PER_USERS PerUserPEO,

per_user_roles PerUserRoles,

FND_SESSION_ROLE_SETS SROLE,

per_roles_dn_vl RolePEO

WHERE PerUserPEO.person_id = hrc_session_util.get_user_personid

AND PerUserRoles.user_id = PerUserPEO.user_id

AND PerUserRoles.role_id = RolePEO.role_id

AND SROLE.SESSION_ROLE_SET_KEY = FND_GLOBAL.SESSION_ROLE_SET_KEY

AND SROLE.ROLE_NAME = RolePEO.ROLE_COMMON_NAME

AND ProjectRoleTypePEO.ROLE_ID = RolePEO.role_id

AND (TRUNC(SYSDATE) BETWEEN TRUNC(ProjectRoleTypePEO.START_DATE_ACTIVE) AND TRUNC(NVL(ProjectRoleTypePEO.End_DATE_ACTIVE,SYSDATE)))

AND ( ProjectRoleTypePEO.PROJECT_ROLE_ID = 3

OR ProjectRoleTypePEO.ROLE_ID =

(SELECT a.ROLE_ID PI_ROLE_ID

FROM PJF_PROJ_ROLE_TYPES_B a

WHERE a.PROJECT_ROLE_ID = 2

))

) PI_ROLE_USER

WHERE AwardPersonnelEO.ROLE_ID = PI_ROLE_USER.PROJECT_ROLE_ID

AND AwardPersonnelEO.personnel_id = PI_ROLE_USER.person_id

AND (TRUNC(SYSDATE) BETWEEN TRUNC(AwardPersonnelEO.START_DATE) AND TRUNC(NVL(AwardPersonnelEO.END_DATE,SYSDATE)))