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_V

Source

View Text - Preformatted


SELECT '0' ,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_resource_utils.get_organization_name(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_resource_utils.get_organization_name(asgn.expenditure_org_id) ,asgn.expenditure_organization_id ,pa_resource_utils.get_organization_name(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 ,asgn.creation_date ,asgn.last_update_date ,LKP1.MEANING ACTION_SET_STATUS_NAME ,ASETS.STATUS_CODE ACTION_SET_STATUS_CODE FROM pa_project_assignments asgn ,pa_projects_all proj ,pa_project_statuses ps ,hr_all_organization_units org ,pa_lookups pl ,pa_lookups lkp ,PA_LOOKUPS LKP1 ,pa_locations loc ,fnd_territories_tl ftt ,jtf_calendars_tl cal ,pa_project_role_types prt ,pa_work_types_v wt ,pa_advertised_open_req_v aor ,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 lkp.lookup_type (+) = 'STAFFING_PRIORITY_CODE' AND proj.project_status_code <> 'CLOSED' AND asgn.assignment_id = aor.assignment_id 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 ASETS.STATUS_CODE = LKP1.LOOKUP_CODE(+) AND LKP1.LOOKUP_TYPE(+) = 'ACTION_SET_STATUS'

View Text - HTML Formatted

SELECT '0'
, 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_RESOURCE_UTILS.GET_ORGANIZATION_NAME(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_RESOURCE_UTILS.GET_ORGANIZATION_NAME(ASGN.EXPENDITURE_ORG_ID)
, ASGN.EXPENDITURE_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(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
, ASGN.CREATION_DATE
, ASGN.LAST_UPDATE_DATE
, LKP1.MEANING ACTION_SET_STATUS_NAME
, ASETS.STATUS_CODE ACTION_SET_STATUS_CODE
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_STATUSES PS
, HR_ALL_ORGANIZATION_UNITS ORG
, PA_LOOKUPS PL
, PA_LOOKUPS LKP
, PA_LOOKUPS LKP1
, PA_LOCATIONS LOC
, FND_TERRITORIES_TL FTT
, JTF_CALENDARS_TL CAL
, PA_PROJECT_ROLE_TYPES PRT
, PA_WORK_TYPES_V WT
, PA_ADVERTISED_OPEN_REQ_V AOR
, 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 LKP.LOOKUP_TYPE (+) = 'STAFFING_PRIORITY_CODE'
AND PROJ.PROJECT_STATUS_CODE <> 'CLOSED'
AND ASGN.ASSIGNMENT_ID = AOR.ASSIGNMENT_ID
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 ASETS.STATUS_CODE = LKP1.LOOKUP_CODE(+)
AND LKP1.LOOKUP_TYPE(+) = 'ACTION_SET_STATUS'