APPS.PA_PROJECT_OPEN_ASSNS_STAFF_V
[Home] [Help]
| Product: | PA - Projects |
|---|---|
| Description: | |
| Implementation/DBA Data: |
APPS.PA_PROJECT_OPEN_ASSNS_STAFF_V
|
SELECT -999
, PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.CARRYING_OUT_ORGANIZATION_ID
, ORG.NAME
, PA_RESOURCE_PVT.GET_MANAGER_ID(PROJ.PROJECT_ID)
, PA_RESOURCE_PVT.GET_MANAGER_NAME(PROJ.PROJECT_ID)
, PA_RESOURCE_PVT.GET_MANAGER_RESOURCE_ID()
, ASGN.ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_TYPE
, ASGN.START_DATE
, ASGN.END_DATE
, ASGN.MIN_RESOURCE_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL
, ASGN.RECORD_VERSION_NUMBER
, NVL(PS.PROJECT_STATUS_NAME
, PL.MEANING)
, ASGN.STATUS_CODE
, ASGN.LOCATION_ID
, ASGN.STAFFING_PRIORITY_CODE
, LKP.MEANING
, NVL(ASGN.NO_OF_ACTIVE_CANDIDATES
, 0)
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, LOC.CITY
, LOC.REGION
, LOC.COUNTRY_CODE
, FTT.TERRITORY_SHORT_NAME COUNTRY
, ASGN.ASSIGNMENT_EFFORT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) + 1 ) ASSIGNMENT_DURATION
, CAL.CALENDAR_NAME
, ASGN.PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.WORK_TYPE_ID
, WT.NAME
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')'
, PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PROJ.PROJECT_ID)
, ASGN.ASSIGNMENT_NUMBER
, PSP.PROJECT_SUBTEAM_ID
, PST.NAME
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.EXTENSION_POSSIBLE
, ASGN.EXPENSE_OWNER
, ASGN.EXPENSE_LIMIT
, ASGN.SEARCH_MIN_AVAILABILITY
, PROJ.ENABLE_AUTOMATED_SEARCH
, POS.NAME SEARCH_EXP_ORG_HIERARCHY
, ASGN.SEARCH_EXP_ORG_STRUCT_VER_ID SEARCH_EXP_ORG_STRUCT_VER_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.SEARCH_EXP_START_ORG_ID) SEARCH_EXP_START_ORG
, ASGN.SEARCH_EXP_START_ORG_ID SEARCH_EXP_START_ORG_ID
, FTT2.TERRITORY_SHORT_NAME SEARCH_COUNTRY_NAME
, ASGN.SEARCH_COUNTRY_CODE SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, 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_JOB_GROUP_ID
, PJG.DISPLAYED_NAME
, ASGN.FCST_JOB_ID
, PJ.NAME
, ASGN.BILL_RATE_OVERRIDE
, ASGN.TP_RATE_OVERRIDE
, ASGN.ENABLE_AUTO_CAND_NOM_FLAG
, ASETS.ACTION_SET_ID
, ASETS.ACTION_SET_NAME
, ASGN.MULTIPLE_STATUS_FLAG
, PSP.PROJECT_SUBTEAM_PARTY_ID
, ASGN.STAFFING_OWNER_PERSON_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(ASGN.STAFFING_OWNER_PERSON_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
, LKP1.MEANING ACTION_SET_STATUS_NAME
, ASETS.STATUS_CODE ACTION_SET_STATUS_CODE
, ASGN.CREATION_DATE
, ASGN.LAST_UPDATE_DATE
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_STATUSES PS
, PA_PROJECT_STATUSES PS2
, HR_ALL_ORGANIZATION_UNITS ORG
, PA_LOOKUPS PL
, PA_LOOKUPS LKP
, PA_LOOKUPS LKP1
, (SELECT DECODE(EMPLOYEE_ID
, NULL
, PA_UTILS.GET_PARTY_ID(USER_ID)
, EMPLOYEE_ID) PERSON_ID
, DECODE(EMPLOYEE_ID
, NULL
, 112
, 101) RESOURCE_TYPE_ID
, DECODE(EMPLOYEE_ID
, NULL
, 'HZ_PARTY:'
, 'PER:') KEY
, PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_PROJ_AUTH') MENU_ID
, PA_SECURITY_PVT.GET_GRANTEE_KEY GRANTEE_KEY
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID) TEMP
, (SELECT FUNCTION_ID
FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_NAME = 'PA_REQ_SO_VIEW') SO_FUNCTION
, (SELECT INSTANCE_SET_ID
FROM FND_OBJECT_INSTANCE_SETS
WHERE INSTANCE_SET_NAME = 'PA_PROJECT_ROLES') INSTANCE_SET
, (SELECT OBJECT_ID
FROM FND_OBJECTS
WHERE OBJ_NAME = 'PA_PROJECTS') PROJECT_OBJECT
, PA_LOCATIONS LOC
, FND_TERRITORIES_TL FTT
, JTF_CALENDARS_TL CAL
, PA_PROJECT_ROLE_TYPES PRT
, PA_WORK_TYPES_V WT
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PER_ORGANIZATION_STRUCTURES POS
, PER_ORG_STRUCTURE_VERSIONS POSV
, FND_TERRITORIES_TL FTT2
, PER_JOB_GROUPS PJG
, PER_JOBS PJ
, PA_ACTION_SETS ASETS
WHERE ASGN.ASSIGNMENT_TYPE = 'OPEN_ASSIGNMENT'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.CALENDAR_ID = CAL.CALENDAR_ID (+)
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND CAL.LANGUAGE(+) = USERENV('LANG')
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND (PS.PROJECT_SYSTEM_STATUS_CODE = 'OPEN_ASGMT' OR PS.PROJECT_SYSTEM_STATUS_CODE IS NULL)
AND PL.LOOKUP_TYPE = 'MULTIPLE_STATUS_TEXT'
AND PL.LOOKUP_CODE = 'MULTIPLE'
AND PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASGN.STAFFING_PRIORITY_CODE = LKP.LOOKUP_CODE (+)
AND PROJ.PROJECT_STATUS_CODE = PS2.PROJECT_STATUS_CODE
AND LKP.LOOKUP_TYPE (+) = 'STAFFING_PRIORITY_CODE'
AND PROJ.PROJECT_STATUS_CODE <> 'CLOSED'
AND FTT.LANGUAGE(+) = USERENV('LANG')
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.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.FCST_JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND ASGN.FCST_JOB_ID = PJ.JOB_ID (+)
AND ASGN.ASSIGNMENT_ID = ASETS.OBJECT_ID
AND ASETS.STATUS_CODE <> 'DELETED'
AND ASETS.OBJECT_TYPE = 'OPEN_ASSIGNMENT'
AND ASETS.ACTION_SET_TYPE_CODE = 'ADVERTISEMENT'
AND ( ASGN.STAFFING_OWNER_PERSON_ID = TEMP.PERSON_ID OR EXISTS ( SELECT '1'
FROM FND_GRANTS FG
, FND_OBJECTS FOB
, PA_ADVERTISED_OPEN_REQ_V AOR
WHERE FG.INSTANCE_PK1_VALUE = TO_CHAR(PROJ.CARRYING_OUT_ORGANIZATION_ID)
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = TEMP.MENU_ID
AND FG.GRANTEE_TYPE = 'USER'
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND FG.GRANTEE_KEY = TEMP.GRANTEE_KEY
AND ASGN.ASSIGNMENT_ID = AOR.ASSIGNMENT_ID) OR EXISTS (SELECT 'Y'
FROM FND_GRANTS FG
, FND_COMPILED_MENU_FUNCTIONS FCMF
, PA_PROJECT_PARTIES PP
WHERE PP.RESOURCE_SOURCE_ID = TEMP.PERSON_ID
AND PP.RESOURCE_TYPE_ID = TEMP.RESOURCE_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PP.START_DATE_ACTIVE)
AND TRUNC(NVL(PP.END_DATE_ACTIVE
, SYSDATE))
AND PP.PROJECT_ID = PROJ.PROJECT_ID
AND FCMF.FUNCTION_ID = SO_FUNCTION.FUNCTION_ID
AND FG.MENU_ID = FCMF.MENU_ID
AND FG.OBJECT_ID = PROJECT_OBJECT.OBJECT_ID
AND FG.GRANTEE_KEY = TEMP.GRANTEE_KEY
AND FG.INSTANCE_SET_ID = INSTANCE_SET.INSTANCE_SET_ID
AND FG.PARAMETER1 = PP.PROJECT_ROLE_ID
AND ( FG.PARAMETER2 = 'NON_STATUS_BASED' OR (FG.PARAMETER2 = 'USER'
AND FG.PARAMETER3 = PROJ.PROJECT_STATUS_CODE) OR (FG.PARAMETER2 = 'SYSTEM'
AND FG.PARAMETER3 = PS2.PROJECT_SYSTEM_STATUS_CODE) OR (FG.PARAMETER2 = 'DEFAULT'
AND NOT EXISTS (SELECT 'Y'
FROM PA_ROLE_STATUS_MENU_MAP
WHERE ROLE_ID = PP.PROJECT_ROLE_ID
AND (STATUS_CODE = PROJ.PROJECT_STATUS_CODE OR STATUS_CODE = PS2.PROJECT_SYSTEM_STATUS_CODE)
AND ROWNUM=1)))) )
AND ASETS.STATUS_CODE = LKP1.LOOKUP_CODE(+)
AND LKP1.LOOKUP_TYPE(+) = 'ACTION_SET_STATUS'