eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PROJECT_OPEN_ASSNS_STAFF_V

Source

View Text - Preformatted


SELECT -999 ,proj.project_id ,proj.name ,proj.segment1 ,proj.carrying_out_organization_id ,org.name ,PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJECT_MANAGER(proj.project_id) ,PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJ_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'

View Text - HTML Formatted

SELECT -999
, PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.CARRYING_OUT_ORGANIZATION_ID
, ORG.NAME
, PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJECT_MANAGER(PROJ.PROJECT_ID)
, PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJ_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'