View: PA_PROJECT_ASSIGNMENTS_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_PROJECT_ASSIGNMENTS_V

View Text

SELECT ASGN.ROWID
, ASGN.ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_TYPE
, ASGN.MULTIPLE_STATUS_FLAG
, ASGN.RECORD_VERSION_NUMBER
, ASGN.APPRVL_STATUS_CODE
, PS1.PROJECT_STATUS_NAME APPRVL_STATUS_NAME
, ASGN.STATUS_CODE STATUS_CODE
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL.MEANING
, PS.PROJECT_STATUS_NAME) STATUS_NAME
, ASGN.PROJECT_ID
, PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, ASGN.PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.RESOURCE_ID
, DECODE(RES.RESOURCE_NAME
, NULL
, DECODE(ASGN.ASSIGNMENT_TYPE
, 'OPEN_ASSIGNMENT'
, RES.RESOURCE_NAME
, PA_RESOURCE_UTILS.GET_RESOURCE_NAME(ASGN.RESOURCE_ID))
, RES.RESOURCE_NAME) RESOURCE_NAME
, RES.RESOURCE_ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(RES.RESOURCE_ORGANIZATION_ID) RESOURCE_ORGANIZATION
, RES2.RESOURCE_ORGANIZATION_ID RESOURCE_CUR_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(RES2.RESOURCE_ORGANIZATION_ID) RESOURCE_CUR_ORGANIZATION
, RES.MANAGER_ID MANAGER_ID
, RES.MANAGER_NAME MANAGER_NAME
, ASGN.PROJECT_PARTY_ID
, PSP.PROJECT_SUBTEAM_ID
, PST.NAME PROJECT_SUB_TEAM_NAME
, PSP.PROJECT_SUBTEAM_PARTY_ID
, ASGN.START_DATE
, ASGN.END_DATE
, PROJ.NAME
, PROJ.SEGMENT1
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, ASGN.ASSIGNMENT_EFFORT
, ASGN.EXTENSION_POSSIBLE
, ASGN.MIN_RESOURCE_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL
, ASGN.ASSIGNMENT_NUMBER
, ASGN.ADDITIONAL_INFORMATION
, ASGN.LOCATION_ID
, LOC.CITY
, LOC.REGION
, LOC.COUNTRY_CODE
, FTT.TERRITORY_SHORT_NAME
, ASGN.DESCRIPTION
, ASGN.NOTE_TO_APPROVER
, ASGN.CALENDAR_ID
, CALTL.CALENDAR_NAME
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.WORK_TYPE_ID
, WT.NAME
, ASGN.EXPENSE_OWNER
, PL1.MEANING
, ASGN.EXPENSE_LIMIT
, ASGN.EXPENSE_LIMIT_CURRENCY_CODE
, ASGN.FCST_JOB_ID
, PJ.NAME
, ASGN.FCST_JOB_GROUP_ID
, PJG.DISPLAYED_NAME
, ASGN.EXPENDITURE_ORG_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORG_ID)
, ASGN.EXPENDITURE_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORGANIZATION_ID)
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.EXPENDITURE_TYPE
, ASGN.FCST_TP_AMOUNT_TYPE
, PL2.MEANING
, ASGN.STAFFING_PRIORITY_CODE
, PL3.MEANING
, ASGN.STAFFING_OWNER_PERSON_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(ASGN.STAFFING_OWNER_PERSON_ID)
, PROJ.PROJECT_TYPE
, ASGN.REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE
, ASGN.MARKUP_PERCENT
, PS.PROJECT_SYSTEM_STATUS_CODE
, NVL(ASGN.NO_OF_ACTIVE_CANDIDATES
, 0)
, ASGN.SOURCE_ASSIGNMENT_ID
, ASGN.ASSIGNMENT_TEMPLATE_ID
, ASGN.MASS_WF_IN_PROGRESS_FLAG
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, ASGN.SEARCH_MIN_AVAILABILITY
, FTT2.TERRITORY_SHORT_NAME
, ASGN.SEARCH_COUNTRY_CODE
, POS.NAME
, ASGN.SEARCH_EXP_ORG_STRUCT_VER_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.SEARCH_EXP_START_ORG_ID)
, ASGN.SEARCH_EXP_START_ORG_ID
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.ENABLE_AUTO_CAND_NOM_FLAG
, ASGN.LAST_AUTO_SEARCH_DATE
, PROJ.ENABLE_AUTOMATED_SEARCH
, ASGN.PENDING_APPROVAL_FLAG
, ASGN.BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT_OVERRIDE
, ASGN.DISCOUNT_PERCENTAGE
, ASGN.RATE_DISC_REASON_CODE
, ASGN.TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE
, PROJ.MULTI_CURRENCY_BILLING_FLAG
, ASGN.CREATION_DATE
, PA_GENERATE_FORECAST_PUB.GET_FORECAST_GEN_DATE(ASGN.PROJECT_ID)
, ASGN.TRANSFER_PRICE_RATE
, ASGN.TRANSFER_PR_RATE_CURR
, ASGN.RESOURCE_LIST_MEMBER_ID
, PA_PLANNING_RESOURCE_UTILS.GET_PLAN_RES_COMBINATION(ASGN.RESOURCE_LIST_MEMBER_ID)
, ASGN.ATTRIBUTE_CATEGORY
, ASGN.ATTRIBUTE1
, ASGN.ATTRIBUTE2
, ASGN.ATTRIBUTE3
, ASGN.ATTRIBUTE4
, ASGN.ATTRIBUTE5
, ASGN.ATTRIBUTE6
, ASGN.ATTRIBUTE7
, ASGN.ATTRIBUTE8
, ASGN.ATTRIBUTE9
, ASGN.ATTRIBUTE10
, ASGN.ATTRIBUTE11
, ASGN.ATTRIBUTE12
, ASGN.ATTRIBUTE13
, ASGN.ATTRIBUTE14
, ASGN.ATTRIBUTE15
, RLM.ALIAS PLANNING_RESOURCE_NAME
, PS.PROJECT_STATUS_NAME
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_RESOURCES_DENORM RES
, PA_RESOURCES_DENORM RES2
, PA_PROJECT_ROLE_TYPES PRT
, PA_PROJECT_STATUSES PS
, PA_PROJECT_STATUSES PS1
, PA_LOCATIONS LOC
, FND_TERRITORIES_TL FTT
, FND_TERRITORIES_TL FTT2
, JTF_CALENDARS_TL CALTL
, PA_WORK_TYPES_V WT
, PA_PROJECTS_ALL PROJ
, PA_LOOKUPS PL
, PA_LOOKUPS PL1
, PA_LOOKUPS PL2
, PA_LOOKUPS PL3
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PER_JOBS PJ
, PER_JOB_GROUPS PJG
, PER_ORGANIZATION_STRUCTURES POS
, PER_ORG_STRUCTURE_VERSIONS POSV
, PA_RESOURCE_LIST_MEMBERS RLM
WHERE ASGN.RESOURCE_ID = RES.RESOURCE_ID(+)
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES.RESOURCE_EFFECTIVE_END_DATE(+)
AND RES.SCHEDULABLE_FLAG (+) = 'Y'
AND ASGN.RESOURCE_ID = RES2.RESOURCE_ID(+)
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES2.RESOURCE_EFFECTIVE_END_DATE(+)
AND RES2.SCHEDULABLE_FLAG (+) = 'Y'
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.APPRVL_STATUS_CODE = PS1.PROJECT_STATUS_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND ASGN.CALENDAR_ID = CALTL.CALENDAR_ID(+)
AND CALTL.LANGUAGE(+) = USERENV('LANG')
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND PL.LOOKUP_TYPE = 'MULTIPLE_STATUS_TEXT'
AND PL.LOOKUP_CODE = 'MULTIPLE'
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND ASGN.FCST_JOB_ID = PJ.JOB_ID (+)
AND ASGN.FCST_JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND PL1.LOOKUP_TYPE(+) = 'EXPENSE_OWNER_TYPE'
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE (+)
AND PL2.LOOKUP_TYPE(+) = 'TP_AMOUNT_TYPE'
AND ASGN.FCST_TP_AMOUNT_TYPE = PL2.LOOKUP_CODE(+)
AND PL3.LOOKUP_TYPE(+) = 'STAFFING_PRIORITY_CODE'
AND ASGN.STAFFING_PRIORITY_CODE = PL3.LOOKUP_CODE(+)
AND ASGN.SEARCH_EXP_ORG_STRUCT_VER_ID = POSV.ORG_STRUCTURE_VERSION_ID(+)
AND POSV.ORGANIZATION_STRUCTURE_ID = POS.ORGANIZATION_STRUCTURE_ID(+)
AND ASGN.SEARCH_COUNTRY_CODE = FTT2.TERRITORY_CODE (+)
AND FTT2.LANGUAGE(+) = USERENV('LANG')
AND ASGN.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID (+)

Columns