VIEW: APPS.PA_CREATE_TEAM_ROLES_V

Source

View Text - Preformatted

SELECT rlm.alias Planning_Resource_Name, ra.resource_list_member_id, firstrow.person_id, rlm.RESOURCE_ID, PA_RESOURCE_UTILS.get_person_name_no_date(firstrow.person_id), ra.project_id, ra.budget_version_id, decode (ra.role_count, 1, firstrow.Named_Role, null) Named_Role, decode (ra.role_count, 1, firstrow.Project_Role_Id, null) Project_Role_Id, decode (ra.role_count, 1, ro.meaning, null) Project_Role, ra.min_date Task_assign_start_date, ra.max_date Task_assign_end_date, firstrow.RESOURCE_ASSIGNMENT_ID, firstrow.RES_TYPE_CODE from pa_resource_assignments firstrow ,pa_resource_list_members rlm ,pa_project_role_types_tl ro ,(select project_id , budget_version_id , resource_list_member_id , count(1) role_count , max(max_id) max_id , min(min_date) min_date , max(max_date) max_date from (select project_id , budget_version_id , resource_list_member_id , project_role_id , max(resource_assignment_id) max_id , min(SCHEDULE_START_DATE) min_date , max(SCHEDULE_END_DATE) max_date from pa_resource_assignments WHERE ta_display_flag = 'Y' and nvl(PROJECT_ASSIGNMENT_ID, -1) = -1 and resource_class_code = 'PEOPLE' group by project_id, budget_version_id, resource_list_member_id, project_role_id) res_roles group by project_id, budget_version_id, resource_list_member_id) ra WHERE ra.resource_list_member_id = rlm.resource_list_member_id and firstrow.resource_assignment_id = ra.max_id and firstrow.project_role_id = ro.project_role_id (+) and ro.language (+) = userenv('LANG')

View Text - HTML Formatted

SELECT RLM.ALIAS PLANNING_RESOURCE_NAME
, RA.RESOURCE_LIST_MEMBER_ID
, FIRSTROW.PERSON_ID
, RLM.RESOURCE_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(FIRSTROW.PERSON_ID)
, RA.PROJECT_ID
, RA.BUDGET_VERSION_ID
, DECODE (RA.ROLE_COUNT
, 1
, FIRSTROW.NAMED_ROLE
, NULL) NAMED_ROLE
, DECODE (RA.ROLE_COUNT
, 1
, FIRSTROW.PROJECT_ROLE_ID
, NULL) PROJECT_ROLE_ID
, DECODE (RA.ROLE_COUNT
, 1
, RO.MEANING
, NULL) PROJECT_ROLE
, RA.MIN_DATE TASK_ASSIGN_START_DATE
, RA.MAX_DATE TASK_ASSIGN_END_DATE
, FIRSTROW.RESOURCE_ASSIGNMENT_ID
, FIRSTROW.RES_TYPE_CODE
FROM PA_RESOURCE_ASSIGNMENTS FIRSTROW
, PA_RESOURCE_LIST_MEMBERS RLM
, PA_PROJECT_ROLE_TYPES_TL RO
, (SELECT PROJECT_ID
, BUDGET_VERSION_ID
, RESOURCE_LIST_MEMBER_ID
, COUNT(1) ROLE_COUNT
, MAX(MAX_ID) MAX_ID
, MIN(MIN_DATE) MIN_DATE
, MAX(MAX_DATE) MAX_DATE
FROM (SELECT PROJECT_ID
, BUDGET_VERSION_ID
, RESOURCE_LIST_MEMBER_ID
, PROJECT_ROLE_ID
, MAX(RESOURCE_ASSIGNMENT_ID) MAX_ID
, MIN(SCHEDULE_START_DATE) MIN_DATE
, MAX(SCHEDULE_END_DATE) MAX_DATE
FROM PA_RESOURCE_ASSIGNMENTS
WHERE TA_DISPLAY_FLAG = 'Y'
AND NVL(PROJECT_ASSIGNMENT_ID
, -1) = -1
AND RESOURCE_CLASS_CODE = 'PEOPLE' GROUP BY PROJECT_ID
, BUDGET_VERSION_ID
, RESOURCE_LIST_MEMBER_ID
, PROJECT_ROLE_ID) RES_ROLES GROUP BY PROJECT_ID
, BUDGET_VERSION_ID
, RESOURCE_LIST_MEMBER_ID) RA
WHERE RA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
AND FIRSTROW.RESOURCE_ASSIGNMENT_ID = RA.MAX_ID
AND FIRSTROW.PROJECT_ROLE_ID = RO.PROJECT_ROLE_ID (+)
AND RO.LANGUAGE (+) = USERENV('LANG')