PER Requisition Summary

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