eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HR_HIER_POS_PEOPLE_V

Source

View Text - Preformatted


SELECT pft.name ,pos.time_normal_start ,pos.time_normal_finish ,pasg.time_normal_start ,pasg.time_normal_finish ,jbt.name ,org.name ,ps.name ,psv.date_from ,psv.date_to ,psv.version_number ,loc.location_code ,pasg.assignment_location ,pasg.full_name ,pasg.employee_number ,pasg.known_as ,pasg.email_address ,pasg.mailstop ,pasg.work_phone_number ,bg.name ,pse.subordinate_position_id ,pse.parent_position_id FROM hr_hier_asg_people_v pasg ,hr_locations loc ,hr_all_organization_units bg ,hr_all_organization_units org ,per_jobs_tl jbt ,per_jobs job ,hr_all_positions_f_tl pft ,hr_all_positions_f pos ,per_pos_structure_elements pse ,per_pos_structure_versions psv ,per_position_structures ps WHERE ps.position_structure_id = psv.position_structure_id AND psv.pos_structure_version_id = pse.pos_structure_version_id AND pse.subordinate_position_id = pos.position_id AND ps.business_group_id = bg.organization_id AND pos.organization_id = org.organization_id AND pft.position_id = pos.position_id AND pft.language = userenv('LANG') AND pos.job_id = job.job_id AND jbt.job_id = job.job_id AND jbt.language = userenv('LANG') AND pos.location_id = loc.location_id (+) AND pos.position_id = pasg.position_id (+) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN psv.date_from AND NVL(psv.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN bg.date_from AND NVL(bg.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN org.date_from AND NVL(org.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN job.date_from AND NVL(job.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN pos.effective_start_date AND pos.effective_end_date UNION SELECT pft.name ,pos.time_normal_start ,pos.time_normal_finish ,pasg.time_normal_start ,pasg.time_normal_finish ,jbt.name ,org.name ,ps.name ,psv.date_from ,psv.date_to ,psv.version_number ,loc.location_code ,pasg.assignment_location ,pasg.full_name ,pasg.employee_number ,pasg.known_as ,pasg.email_address ,pasg.mailstop ,pasg.work_phone_number ,bg.name ,pse.parent_position_id ,0 FROM hr_hier_asg_people_v pasg ,hr_locations loc ,hr_all_organization_units bg ,hr_all_organization_units org ,per_jobs_tl jbt ,per_jobs job ,hr_all_positions_f_tl pft ,per_all_positions pos ,per_pos_structure_elements pse ,per_pos_structure_versions psv ,per_position_structures ps WHERE ps.position_structure_id = psv.position_structure_id AND psv.pos_structure_version_id = pse.pos_structure_version_id AND pse.parent_position_id = pos.position_id AND ps.business_group_id = bg.organization_id AND pos.organization_id = org.organization_id AND pft.position_id = pos.position_id AND pft.language = userenv('LANG') AND pos.job_id = job.job_id AND jbt.job_id = job.job_id AND jbt.language = userenv('LANG') AND pos.location_id = loc.location_id (+) AND pos.position_id = pasg.position_id (+) AND NOT EXISTS (SELECT null FROM per_pos_structure_elements pse2 WHERE pse.parent_position_id = pse2.subordinate_position_id) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN psv.date_from AND NVL(psv.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN bg.date_from AND NVL(bg.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN org.date_from AND NVL(org.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN job.date_from AND NVL(job.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN pos.date_effective AND NVL(pos.date_end,to_date('31-12-4712','DD-MM-YYYY')) WITH READ ONLY

View Text - HTML Formatted

SELECT PFT.NAME
, POS.TIME_NORMAL_START
, POS.TIME_NORMAL_FINISH
, PASG.TIME_NORMAL_START
, PASG.TIME_NORMAL_FINISH
, JBT.NAME
, ORG.NAME
, PS.NAME
, PSV.DATE_FROM
, PSV.DATE_TO
, PSV.VERSION_NUMBER
, LOC.LOCATION_CODE
, PASG.ASSIGNMENT_LOCATION
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, PSE.SUBORDINATE_POSITION_ID
, PSE.PARENT_POSITION_ID
FROM HR_HIER_ASG_PEOPLE_V PASG
, HR_LOCATIONS LOC
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_JOBS_TL JBT
, PER_JOBS JOB
, HR_ALL_POSITIONS_F_TL PFT
, HR_ALL_POSITIONS_F POS
, PER_POS_STRUCTURE_ELEMENTS PSE
, PER_POS_STRUCTURE_VERSIONS PSV
, PER_POSITION_STRUCTURES PS
WHERE PS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID
AND PSV.POS_STRUCTURE_VERSION_ID = PSE.POS_STRUCTURE_VERSION_ID
AND PSE.SUBORDINATE_POSITION_ID = POS.POSITION_ID
AND PS.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PFT.POSITION_ID = POS.POSITION_ID
AND PFT.LANGUAGE = USERENV('LANG')
AND POS.JOB_ID = JOB.JOB_ID
AND JBT.JOB_ID = JOB.JOB_ID
AND JBT.LANGUAGE = USERENV('LANG')
AND POS.LOCATION_ID = LOC.LOCATION_ID (+)
AND POS.POSITION_ID = PASG.POSITION_ID (+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PSV.DATE_FROM
AND NVL(PSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN BG.DATE_FROM
AND NVL(BG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN ORG.DATE_FROM
AND NVL(ORG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN JOB.DATE_FROM
AND NVL(JOB.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN POS.EFFECTIVE_START_DATE
AND POS.EFFECTIVE_END_DATE UNION SELECT PFT.NAME
, POS.TIME_NORMAL_START
, POS.TIME_NORMAL_FINISH
, PASG.TIME_NORMAL_START
, PASG.TIME_NORMAL_FINISH
, JBT.NAME
, ORG.NAME
, PS.NAME
, PSV.DATE_FROM
, PSV.DATE_TO
, PSV.VERSION_NUMBER
, LOC.LOCATION_CODE
, PASG.ASSIGNMENT_LOCATION
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, PSE.PARENT_POSITION_ID
, 0
FROM HR_HIER_ASG_PEOPLE_V PASG
, HR_LOCATIONS LOC
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_JOBS_TL JBT
, PER_JOBS JOB
, HR_ALL_POSITIONS_F_TL PFT
, PER_ALL_POSITIONS POS
, PER_POS_STRUCTURE_ELEMENTS PSE
, PER_POS_STRUCTURE_VERSIONS PSV
, PER_POSITION_STRUCTURES PS
WHERE PS.POSITION_STRUCTURE_ID = PSV.POSITION_STRUCTURE_ID
AND PSV.POS_STRUCTURE_VERSION_ID = PSE.POS_STRUCTURE_VERSION_ID
AND PSE.PARENT_POSITION_ID = POS.POSITION_ID
AND PS.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
AND POS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PFT.POSITION_ID = POS.POSITION_ID
AND PFT.LANGUAGE = USERENV('LANG')
AND POS.JOB_ID = JOB.JOB_ID
AND JBT.JOB_ID = JOB.JOB_ID
AND JBT.LANGUAGE = USERENV('LANG')
AND POS.LOCATION_ID = LOC.LOCATION_ID (+)
AND POS.POSITION_ID = PASG.POSITION_ID (+)
AND NOT EXISTS (SELECT NULL
FROM PER_POS_STRUCTURE_ELEMENTS PSE2
WHERE PSE.PARENT_POSITION_ID = PSE2.SUBORDINATE_POSITION_ID)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PSV.DATE_FROM
AND NVL(PSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN BG.DATE_FROM
AND NVL(BG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN ORG.DATE_FROM
AND NVL(ORG.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN JOB.DATE_FROM
AND NVL(JOB.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN POS.DATE_EFFECTIVE
AND NVL(POS.DATE_END
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) WITH READ ONLY