VIEW: APPS.HRFV_SUPERVISORS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name person_name ,ass.assignment_number assignment_number ,peo.employee_number employee_number ,orgT.name organization_name ,jobT.name job_name ,grdT.name grade_name ,posT.name position_name ,locT.location_code location_name ,sup.full_name supervisor_name ,sup.employee_number supervisor_employee_number ,sup.title supervisor_title ,sup.middle_names supervisor_middle_names ,sup.known_as supervisor_preferred_name ,sup.first_name supervisor_first_name ,sup.last_name supervisor_last_name ,sup.pre_name_adjunct supervisor_prefix ,sup.suffix supervisor_suffix ,hr_bis.bis_decode_lookup('YES_NO', NVL(sup.current_employee_flag,null)) supervisor_current_emp_flag ,sup.internal_location supervisor_internal_location ,sup.mailstop supervisor_mailstop ,sup.email_address supervisor_email_address ,sorgT.name supervisor_organization_name ,sjobT.name supervisor_job_name ,sgrdT.name supervisor_grade_name ,sposT.name supervisor_position_name ,slocT.location_code supervisor_location_name , '_DF:PER:PER_PEOPLE:peo' , '_DF:PER:PER_PEOPLE:sup' ,ass.assignment_id assignment_id ,ass.business_group_id business_group_id ,ass.person_id person_id ,ass.supervisor_id supervisor_id ,orgT.organization_id emp_organization_id ,jobT.job_id emp_job_id ,grdT.grade_id emp_grade_id ,posT.position_id emp_position_id ,locT.location_id emp_location_id ,sorgT.organization_id sup_organization_id ,sjobT.job_id sup_job_id ,sgrdT.grade_id sup_grade_id ,sposT.position_id sup_position_id ,slocT.location_id sup_location_id FROM hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,hr_locations_all_tl locT ,per_assignments_x ass ,per_all_people_f sup ,per_people_x peo ,per_jobs_tl jobT ,per_grades_tl grdT ,hr_all_positions_f_tl posT ,hr_all_organization_units_tl sorgT ,per_jobs_tl sjobT ,per_grades_tl sgrdT ,hr_all_positions_f_tl sposT ,hr_locations_all_tl slocT ,per_all_assignments_f sasg WHERE peo.person_id = ass.person_id AND peo.current_employee_flag = 'Y' AND peo.employee_number is not null AND ass.assignment_type = 'E' AND ass.supervisor_id = sup.person_id (+) AND ass.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND ass.location_id = locT.location_id(+) AND locT.language (+) = userenv('LANG') AND ass.job_id = jobT.job_id (+) AND jobT.language (+) = userenv('LANG') AND ass.grade_id = grdT.grade_id (+) AND grdT.language (+) = userenv('LANG') AND ass.position_id = posT.position_id (+) AND posT.language (+) = userenv('LANG') AND sup.person_id = sasg.person_id (+) AND sasg.assignment_type (+) = 'E' AND sasg.primary_flag (+) = 'Y' AND sasg.organization_id = sorgT.organization_id (+) AND sorgT.language (+) = userenv('LANG') AND sasg.job_id = sjobT.job_id (+) AND sjobT.language (+) = userenv('LANG') AND sasg.grade_id = sgrdT.grade_id (+) AND sgrdT.language (+) = userenv('LANG') AND sasg.position_id = sposT.position_id (+) AND sposT.language (+) = userenv('LANG') AND sasg.location_id = slocT.location_id(+) AND slocT.language (+) = userenv('LANG') AND TRUNC(sysdate) BETWEEN sup.effective_start_date (+) AND sup.effective_end_date (+) AND TRUNC(sysdate) BETWEEN sasg.effective_start_date (+) AND sasg.effective_end_date (+) AND ass.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND ass.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, ORGT.NAME ORGANIZATION_NAME
, JOBT.NAME JOB_NAME
, GRDT.NAME GRADE_NAME
, POST.NAME POSITION_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_EMPLOYEE_NUMBER
, SUP.TITLE SUPERVISOR_TITLE
, SUP.MIDDLE_NAMES SUPERVISOR_MIDDLE_NAMES
, SUP.KNOWN_AS SUPERVISOR_PREFERRED_NAME
, SUP.FIRST_NAME SUPERVISOR_FIRST_NAME
, SUP.LAST_NAME SUPERVISOR_LAST_NAME
, SUP.PRE_NAME_ADJUNCT SUPERVISOR_PREFIX
, SUP.SUFFIX SUPERVISOR_SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(SUP.CURRENT_EMPLOYEE_FLAG
, NULL)) SUPERVISOR_CURRENT_EMP_FLAG
, SUP.INTERNAL_LOCATION SUPERVISOR_INTERNAL_LOCATION
, SUP.MAILSTOP SUPERVISOR_MAILSTOP
, SUP.EMAIL_ADDRESS SUPERVISOR_EMAIL_ADDRESS
, SORGT.NAME SUPERVISOR_ORGANIZATION_NAME
, SJOBT.NAME SUPERVISOR_JOB_NAME
, SGRDT.NAME SUPERVISOR_GRADE_NAME
, SPOST.NAME SUPERVISOR_POSITION_NAME
, SLOCT.LOCATION_CODE SUPERVISOR_LOCATION_NAME
, '_DF:PER:PER_PEOPLE:PEO'
, '_DF:PER:PER_PEOPLE:SUP'
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASS.PERSON_ID PERSON_ID
, ASS.SUPERVISOR_ID SUPERVISOR_ID
, ORGT.ORGANIZATION_ID EMP_ORGANIZATION_ID
, JOBT.JOB_ID EMP_JOB_ID
, GRDT.GRADE_ID EMP_GRADE_ID
, POST.POSITION_ID EMP_POSITION_ID
, LOCT.LOCATION_ID EMP_LOCATION_ID
, SORGT.ORGANIZATION_ID SUP_ORGANIZATION_ID
, SJOBT.JOB_ID SUP_JOB_ID
, SGRDT.GRADE_ID SUP_GRADE_ID
, SPOST.POSITION_ID SUP_POSITION_ID
, SLOCT.LOCATION_ID SUP_LOCATION_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_LOCATIONS_ALL_TL LOCT
, PER_ASSIGNMENTS_X ASS
, PER_ALL_PEOPLE_F SUP
, PER_PEOPLE_X PEO
, PER_JOBS_TL JOBT
, PER_GRADES_TL GRDT
, HR_ALL_POSITIONS_F_TL POST
, HR_ALL_ORGANIZATION_UNITS_TL SORGT
, PER_JOBS_TL SJOBT
, PER_GRADES_TL SGRDT
, HR_ALL_POSITIONS_F_TL SPOST
, HR_LOCATIONS_ALL_TL SLOCT
, PER_ALL_ASSIGNMENTS_F SASG
WHERE PEO.PERSON_ID = ASS.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PEO.EMPLOYEE_NUMBER IS NOT NULL
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASS.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND ASS.JOB_ID = JOBT.JOB_ID (+)
AND JOBT.LANGUAGE (+) = USERENV('LANG')
AND ASS.GRADE_ID = GRDT.GRADE_ID (+)
AND GRDT.LANGUAGE (+) = USERENV('LANG')
AND ASS.POSITION_ID = POST.POSITION_ID (+)
AND POST.LANGUAGE (+) = USERENV('LANG')
AND SUP.PERSON_ID = SASG.PERSON_ID (+)
AND SASG.ASSIGNMENT_TYPE (+) = 'E'
AND SASG.PRIMARY_FLAG (+) = 'Y'
AND SASG.ORGANIZATION_ID = SORGT.ORGANIZATION_ID (+)
AND SORGT.LANGUAGE (+) = USERENV('LANG')
AND SASG.JOB_ID = SJOBT.JOB_ID (+)
AND SJOBT.LANGUAGE (+) = USERENV('LANG')
AND SASG.GRADE_ID = SGRDT.GRADE_ID (+)
AND SGRDT.LANGUAGE (+) = USERENV('LANG')
AND SASG.POSITION_ID = SPOST.POSITION_ID (+)
AND SPOST.LANGUAGE (+) = USERENV('LANG')
AND SASG.LOCATION_ID = SLOCT.LOCATION_ID(+)
AND SLOCT.LANGUAGE (+) = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE (+)
AND SUP.EFFECTIVE_END_DATE (+)
AND TRUNC(SYSDATE) BETWEEN SASG.EFFECTIVE_START_DATE (+)
AND SASG.EFFECTIVE_END_DATE (+)
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY