PER Requisition Summary
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Requisition Summary Report
Application: Human Resources
Source: Requisition Summary Report (XML)
Short Name: PERRPREQ_XML
DB package: PER_PERRPREQ_XMLP_PKG
Description: Requisition Summary Report
Application: Human Resources
Source: Requisition Summary Report (XML)
Short Name: PERRPREQ_XML
DB package: PER_PERRPREQ_XMLP_PKG
SELECT DISTINCT a.ORGANIZATION_ID, -- nvl(a.PEOPLE_GROUP_ID,-999), nvl(a.PEOPLE_GROUP_ID,-999) NVL_A_PEOPLE_GROUP_ID_999, -- nvl(a.JOB_ID,-999), nvl(a.JOB_ID,-999) NVL_A_JOB_ID_999, -- nvl(a.POSITION_ID,-999), nvl(a.POSITION_ID,-999) NVL_A_POSITION_ID_999, -- nvl(a.GRADE_ID,-999), nvl(a.GRADE_ID,-999) NVL_A_GRADE_ID_999, -- nvl(a.LOCATION_ID,-999), nvl(a.LOCATION_ID,-999) NVL_A_LOCATION_ID_999, -- nvl(a.VACANCY_ID,-999), nvl(a.VACANCY_ID,-999) NVL_A_VACANCY_ID_999, -- nvl(a.RECRUITMENT_ACTIVITY_ID,-999), nvl(a.RECRUITMENT_ACTIVITY_ID,-999) NVL_A_RECRUITMENT_ACTIVITY_ID1, -- nvl(a.RECRUITER_ID,-999), nvl(a.RECRUITER_ID,-999) NVL_A_RECRUITER_ID_999, o.NAME ORG_NAME, pg.GROUP_NAME PEOPLE_GROUP, jbt.NAME JOB_NAME, hr_general.decode_position_latest_name(a.position_id) POSITION_NAME, gdt.NAME GRADE_NAME, ltl.LOCATION_CODE LOCATION, v.NAME VACANCY_NAME, v.NUMBER_OF_OPENINGS PLACES, lu.MEANING VACANCY_STATUS, (select FULL_NAME from per_all_people_f where person_id = a.recruiter_id and SSPP.EFFECTIVE_DATE between effective_start_date and effective_end_date) RECRUITER, pr.NAME REQUISITION_NAME, pr.DATE_FROM, pr.DATE_TO, (select FULL_NAME from per_all_people_f where person_id = pr.PERSON_ID and SSPP.EFFECTIVE_DATE between effective_start_date and effective_end_date) RAISED_BY, ra.NAME RECRUITMENT_ACTIVITY /* Commented during DT Fixes from per_all_people_f where person_id = a.recruiter_id and SSPP.EFFECTIVE_DATE between effective_start_date and effective_end_date) RECRUITER, pr.NAME REQUISITION_NAME, pr.DATE_FROM, pr.DATE_TO, (select FULL_NAME from per_all_people_f where person_id = pr.PERSON_ID and SSPP.EFFECTIVE_DATE between effective_start_date and effective_end_date) RAISED_BY, ra.NAME RECRUITMENT_ACTIVITY */ FROM PER_ASSIGNMENTS_F a, HR_ORGANIZATION_UNITS o, PAY_PEOPLE_GROUPS pg, PER_JOBS_TL jbt, PER_GRADES_TL gdt, HR_LOCATIONS_ALL_TL ltl, PER_VACANCIES v, HR_LOOKUPS lu, PER_REQUISITIONS pr, PER_RECRUITMENT_ACTIVITIES ra, FND_SESSIONS SSPP WHERE a.ORGANIZATION_ID = o.ORGANIZATION_ID AND a.PEOPLE_GROUP_ID = pg.PEOPLE_GROUP_ID(+) AND a.JOB_ID = jbt.JOB_ID(+) AND jbt.language(+) = userenv('LANG') AND a.GRADE_ID = gdt.GRADE_ID(+) AND gdt.language(+) = userenv('LANG') AND a.LOCATION_ID = ltl.LOCATION_ID(+) AND a.VACANCY_ID = v.VACANCY_ID(+) AND v.STATUS = lu.LOOKUP_CODE(+) AND lu.LOOKUP_TYPE(+) = 'VACANCY_STATUS' AND a.RECRUITMENT_ACTIVITY_ID = ra.RECRUITMENT_ACTIVITY_ID(+) AND (:SESSION_DATE) between a.effective_start_date AND a.effective_end_date AND a.assignment_type = 'A' AND nvl(pr.DATE_FROM,nvl(:P_REQ_START_DATE,to_date('19000101','YYYYMMDD'))) between nvl(:P_REQ_START_DATE,to_date('19000101','YYYYMMDD')) and nvl(:P_REQ_END_DATE,to_date('47121231','YYYYMMDD')) AND nvl(pr.DATE_TO,nvl(:P_REQ_END_DATE,to_date('47121231','YYYYMMDD'))) between nvl(:P_REQ_START_DATE,to_date('19000101','YYYYMMDD')) and nvl(:P_REQ_END_DATE,to_date('47121231','YYYYMMDD')) AND a.business_group_id = :p_business_group_id AND v.REQUISITION_ID = pr.REQUISITION_ID AND a.ORGANIZATION_ID in (select organization_id from hr_all_organization_units where organization_id = nvl(:P_ORGANIZATION_ID,organization_id)) AND (:P_PEOPLE_GROUP is null or :P_PEOPLE_GROUP = pg.GROUP_NAME) AND (:P_JOB_ID is null or :P_JOB_ID = a.JOB_ID) AND (:P_POSITION_ID is null or :P_POSITION_ID = a.POSITION_ID) AND (:P_GRADE_ID is null or :P_GRADE_ID = a.GRADE_ID) AND (:P_LOCATION_ID is null or :P_LOCATION_ID = a.LOCATION_ID) AND (:P_RECRUITMENT_ACTIVITY_ID is null or :P_RECRUITMENT_ACTIVITY_ID = a.RECRUITMENT_ACTIVITY_ID) AND (:P_REQUISITION_ID is null or :P_REQUISITION_ID = pr.REQUISITION_ID) AND (:P_VACANCY_STATUS is null or :P_VACANCY_STATUS = v.STATUS) AND ltl.LANGUAGE (+) = userenv('LANG') AND SSPP.SESSION_ID=USERENV('sessionid') ORDER BY pr.NAME, v.NAME, o.NAME, jbt.NAME, pg.GROUP_NAME, gdt.NAME, ltl.LOCATION_CODE |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Effective Date |
|
Date | |
Start Date |
|
Date | |
End Date |
|
Date | |
Requisition |
|
LOV Oracle | |
Vacancy Status |
|
LOV Oracle | |
Organization |
|
LOV Oracle | |
Job |
|
LOV Oracle | |
People Group |
|
Char | |
Position |
|
LOV Oracle | |
Grade |
|
LOV Oracle | |
Location |
|
LOV Oracle | |
Recruitment Activity |
|
LOV Oracle | |
Applicant Status |
|
LOV Oracle |