PER Requisition Summary

Description
Categories: BI Publisher, Human Resources
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 NameSQL textValidation
Group KeyFlex Structure Id
 
Number
Business Group Id
 
Number
Applicant Status
 
LOV Oracle
Recruitment Activity
 
LOV Oracle
Location
 
LOV Oracle
Grade
 
LOV Oracle
Position
 
LOV Oracle
People Group
 
Job
 
LOV Oracle
Organization
 
LOV Oracle
Vacancy Status
 
LOV Oracle
Requisition
 
LOV Oracle
End Date
 
Date
Start Date
 
Date
Effective Date
 
Date