View: PA_UPDATE_TEAM_ROLES_V

Product:	PA - Projects
Description:	This view retrieves the team roles of planning resources linked/not linked to task assignments for updation purposes
Implementation/DBA Data:	APPS.PA_UPDATE_TEAM_ROLES_V

View Text

SELECT PA.RECORD_VERSION_NUMBER
, PA.ASSIGNMENT_ID
, RLM.ALIAS PLANNING_RESOURCE_NAME
, PA.RESOURCE_LIST_MEMBER_ID
, RTXN.PERSON_ID
, RTXN.RESOURCE_ID
, PER.FULL_NAME PERSON_FULL_NAME
, PA.PROJECT_ID
, PA.PROJECT_ROLE_ID
, RO.MEANING PROJECT_ROLE
, PA.ASSIGNMENT_NAME TEAM_ROLE
, PA_TASK_ASSIGNMENT_UTILS.GET_MIN_MAX_TASK_ASGMT_DATE(PA.RESOURCE_LIST_MEMBER_ID
, 'MIN'
, PA.PROJECT_ID
, RA.BUDGET_VERSION_ID) TASK_ASSIGN_START_DATE
, PA_TASK_ASSIGNMENT_UTILS.GET_MIN_MAX_TASK_ASGMT_DATE(PA.RESOURCE_LIST_MEMBER_ID
, 'MAX'
, PA.PROJECT_ID
, RA.BUDGET_VERSION_ID) TASK_ASSIGN_END_DATE
, SUM(RA.TOTAL_PLAN_QUANTITY) PLANNED_QUANTITY
, PA.START_DATE TEAM_ROLE_START
, PA.END_DATE TEAM_ROLE_END
, PA.ASSIGNMENT_EFFORT PROJECT_CAPACITY
, DECODE(SIGN(PA.ASSIGNMENT_EFFORT - SUM(RA.TOTAL_PLAN_QUANTITY))
, -1
, '(' || (PA.ASSIGNMENT_EFFORT-SUM(RA.TOTAL_PLAN_QUANTITY))*(-1) || ')'
, ' ' || (PA.ASSIGNMENT_EFFORT-SUM(RA.TOTAL_PLAN_QUANTITY)) || ' ' ) CAPACITY_OVER_UNDER
, PA.STATUS_CODE
, RA.BUDGET_VERSION_ID
, PROJ.SEGMENT1
, PA.ASSIGNMENT_TYPE
, PA.APPRVL_STATUS_CODE
, PA.MASS_WF_IN_PROGRESS_FLAG
, PA.PENDING_APPROVAL_FLAG
, PS.PROJECT_SYSTEM_STATUS_CODE
FROM PA_RESOURCE_ASSIGNMENTS RA
, PA_RESOURCE_LIST_MEMBERS RLM
, PER_ALL_PEOPLE_F PER
, PA_PROJECT_ASSIGNMENTS PA
, PA_RESOURCE_TXN_ATTRIBUTES RTXN
, PA_PROJECT_ROLE_TYPES_TL RO
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_STATUSES PS
WHERE PA.PROJECT_ID = PROJ.PROJECT_ID
AND PA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
AND PA.PROJECT_ID = RA.PROJECT_ID
AND PA.PROJECT_ROLE_ID = RO.PROJECT_ROLE_ID
AND RO.LANGUAGE = USERENV('LANG')
AND RA.RESOURCE_CLASS_CODE = 'PEOPLE'
AND RA.TA_DISPLAY_FLAG = 'Y'
AND RA.PROJECT_ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND PA.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND PS.PROJECT_SYSTEM_STATUS_CODE NOT IN ('OPEN_ASGMT_FILLED'
, 'OPEN_ASGMT_CANCEL'
, 'STAFFED_ASGMT_CANCEL')
AND RTXN.PERSON_ID = PER.PERSON_ID (+)
AND RTXN.RESOURCE_ID (+) = PA.RESOURCE_ID
AND PA.START_DATE >= NVL(PER.EFFECTIVE_START_DATE
, PA.START_DATE)
AND PA.START_DATE <= NVL(PER.EFFECTIVE_END_DATE
, PA.START_DATE) GROUP BY PROJ.SEGMENT1
, RA.PROJECT_ASSIGNMENT_ID
, RA.BUDGET_VERSION_ID
, PA.RECORD_VERSION_NUMBER
, PA.ASSIGNMENT_ID
, PA.ASSIGNMENT_TYPE
, PA.APPRVL_STATUS_CODE
, PA.MASS_WF_IN_PROGRESS_FLAG
, PA.PENDING_APPROVAL_FLAG
, RLM.ALIAS
, PA.RESOURCE_LIST_MEMBER_ID
, RTXN.PERSON_ID
, RTXN.RESOURCE_ID
, PER.FULL_NAME
, PA.PROJECT_ID
, PA.PROJECT_ROLE_ID
, RO.MEANING
, PA.ASSIGNMENT_NAME
, PA.START_DATE
, PA.END_DATE
, PA.ASSIGNMENT_EFFORT
, PA.STATUS_CODE
, RA.BUDGET_VERSION_ID
, PS.PROJECT_SYSTEM_STATUS_CODE UNION ALL SELECT PAP.RECORD_VERSION_NUMBER
, PAP.ASSIGNMENT_ID
, RL.ALIAS PLANNING_RESOURCE_NAME
, PAP.RESOURCE_LIST_MEMBER_ID
, RTXN.PERSON_ID
, RTXN.RESOURCE_ID
, PERP.FULL_NAME PERSON_FULL_NAME
, PAP.PROJECT_ID
, PAP.PROJECT_ROLE_ID
, PRO.MEANING PROJECT_ROLE
, PAP.ASSIGNMENT_NAME TEAM_ROLE
, TO_DATE(NULL) TASK_ASSIGN_START_DATE
, TO_DATE(NULL) TASK_ASSIGN_END_DATE
, TO_NUMBER(NULL) PLANNED_QUANTITY
, PAP.START_DATE TEAM_ROLE_START
, PAP.END_DATE TEAM_ROLE_END
, PAP.ASSIGNMENT_EFFORT PROJECT_CAPACITY
, ' '|| PAP.ASSIGNMENT_EFFORT || ' ' CAPACITY_OVER_UNDER
, PAP.STATUS_CODE
, TO_NUMBER(NULL) BUDGET_VERSION_ID
, PROJ.SEGMENT1
, PAP.ASSIGNMENT_TYPE
, PAP.APPRVL_STATUS_CODE
, PAP.MASS_WF_IN_PROGRESS_FLAG
, PAP.PENDING_APPROVAL_FLAG
, PS.PROJECT_SYSTEM_STATUS_CODE
FROM PA_RESOURCE_LIST_MEMBERS RL
, PER_ALL_PEOPLE_F PERP
, PA_RESOURCE_TXN_ATTRIBUTES RTXN
, PA_PROJECT_ASSIGNMENTS PAP
, PA_PROJECT_ROLE_TYPES_TL PRO
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_STATUSES PS
WHERE PROJ.PROJECT_ID = PAP.PROJECT_ID
AND PAP.RESOURCE_LIST_MEMBER_ID = RL.RESOURCE_LIST_MEMBER_ID
AND PAP.PROJECT_ROLE_ID = PRO.PROJECT_ROLE_ID
AND PRO.LANGUAGE = USERENV('LANG')
AND RTXN.PERSON_ID = PERP.PERSON_ID (+)
AND RTXN.RESOURCE_ID (+) = PAP.RESOURCE_ID
AND PAP.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND PS.PROJECT_SYSTEM_STATUS_CODE NOT IN ('OPEN_ASGMT_FILLED'
, 'OPEN_ASGMT_CANCEL'
, 'STAFFED_ASGMT_CANCEL')
AND NOT EXISTS (SELECT 1
FROM PA_RESOURCE_ASSIGNMENTS RA
, PA_BUDGET_VERSIONS BV
WHERE RA.PROJECT_ASSIGNMENT_ID = PAP.ASSIGNMENT_ID
AND RA.PROJECT_ID = PAP.PROJECT_ID
AND PAP.PROJECT_ID = BV.PROJECT_ID
AND BV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID)
AND PAP.START_DATE >= NVL(PERP.EFFECTIVE_START_DATE
, PAP.START_DATE)
AND PAP.START_DATE <= NVL(PERP.EFFECTIVE_END_DATE
, PAP.START_DATE)

Columns