VIEW: APPS.HR_HIER_ASG_PEOPLE_V

Source

View Text - Preformatted

SELECT pp.full_name , pp.employee_number , pp.sex , pp.date_of_birth , pp.known_as , pp.national_identifier , pp.title , asg.assignment_number , asg.primary_flag , pp.email_address , pp.mailstop , pph.phone_number , pp.date_employee_data_verified , asg.time_normal_start , asg.time_normal_finish , loc.location_code , asg.manager_flag , asg.people_group_id , asg.payroll_id , asg.person_id , asg.supervisor_id , asg.assignment_id , asg.organization_id , asg.position_id , asg.job_id , asg.location_id , asg.grade_id , org.name , asg.business_group_id , HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE FROM per_phones pph ,hr_locations_all loc ,hr_all_organization_units org , per_all_people_f pp , per_all_assignments_f asg WHERE pp.person_id = asg.person_id AND asg.primary_flag = 'Y' AND org.organization_id = asg.business_group_id AND pp.person_id = pph.parent_id(+) AND pph.parent_table(+) = 'PER_ALL_PEOPLE_F' AND pph.phone_type(+) = 'W1' AND asg.location_id = loc.location_id(+) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN pph.date_from(+) AND NVL(pph.date_to(+),to_date('30-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN pp.effective_start_date AND pp.effective_end_date AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN asg.effective_start_date AND asg.effective_end_date

View Text - HTML Formatted

SELECT PP.FULL_NAME
, PP.EMPLOYEE_NUMBER
, PP.SEX
, PP.DATE_OF_BIRTH
, PP.KNOWN_AS
, PP.NATIONAL_IDENTIFIER
, PP.TITLE
, ASG.ASSIGNMENT_NUMBER
, ASG.PRIMARY_FLAG
, PP.EMAIL_ADDRESS
, PP.MAILSTOP
, PPH.PHONE_NUMBER
, PP.DATE_EMPLOYEE_DATA_VERIFIED
, ASG.TIME_NORMAL_START
, ASG.TIME_NORMAL_FINISH
, LOC.LOCATION_CODE
, ASG.MANAGER_FLAG
, ASG.PEOPLE_GROUP_ID
, ASG.PAYROLL_ID
, ASG.PERSON_ID
, ASG.SUPERVISOR_ID
, ASG.ASSIGNMENT_ID
, ASG.ORGANIZATION_ID
, ASG.POSITION_ID
, ASG.JOB_ID
, ASG.LOCATION_ID
, ASG.GRADE_ID
, ORG.NAME
, ASG.BUSINESS_GROUP_ID
, HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE
FROM PER_PHONES PPH
, HR_LOCATIONS_ALL LOC
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F ASG
WHERE PP.PERSON_ID = ASG.PERSON_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND ORG.ORGANIZATION_ID = ASG.BUSINESS_GROUP_ID
AND PP.PERSON_ID = PPH.PARENT_ID(+)
AND PPH.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PPH.PHONE_TYPE(+) = 'W1'
AND ASG.LOCATION_ID = LOC.LOCATION_ID(+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PPH.DATE_FROM(+)
AND NVL(PPH.DATE_TO(+)
, TO_DATE('30-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE