VIEW: APPS.PA_UPDATE_TEAM_ROLES_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)