VIEW: APPS.HR_HIER_ORG_PEOPLE_V

Source

View Text - Preformatted

SELECT hou1.name ,loc.location_code ,hou1.internal_address_line ,os.name ,osv.version_number ,osv.date_from ,osv.date_to ,hr_general.decode_lookup('ORG_TYPE',hou1.type) ,pasg.full_name ,pasg.employee_number ,pasg.known_as ,pasg.email_address ,pasg.mailstop ,pasg.work_phone_number ,bg.name ,ose.organization_id_child ,ose.organization_id_parent ,osv.org_structure_version_id FROM hr_locations loc ,hr_hier_asg_people_v pasg ,hr_all_organization_units bg ,hr_all_organization_units hou1 ,per_org_structure_elements ose ,per_org_structure_versions osv ,per_organization_structures os WHERE os.organization_structure_id = osv.organization_structure_id AND pasg.manager_flag(+)='Y' AND osv.org_structure_version_id = ose.org_structure_version_id AND ose.organization_id_child = hou1.organization_id AND ose.organization_id_child = pasg.organization_id(+) AND hou1.location_id = loc.location_id(+) AND os.business_group_id = bg.organization_id(+) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN osv.date_from AND NVL(osv.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN hou1.date_from AND NVL(hou1.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(bg.date_from(+),to_date('01-01-0001','DD-MM-YYYY')) AND NVL(bg.date_to(+),to_date('31-12-4712','DD-MM-YYYY')) UNION Select distinct hou1.name ,loc.location_code ,hou1.internal_address_line ,os.name ,osv.version_number ,osv.date_from ,osv.date_to ,hr_general.decode_lookup('ORG_TYPE',hou1.type) org_type ,pasg.full_name ,pasg.employee_number ,pasg.known_as ,pasg.email_address ,pasg.mailstop ,pasg.work_phone_number ,bg.name ,ose.organization_id_parent ,0 ,osv.org_structure_version_id FROM hr_locations loc ,hr_hier_asg_people_v pasg ,hr_all_organization_units bg ,hr_all_organization_units hou1 ,per_org_structure_elements ose ,per_org_structure_versions osv ,per_organization_structures os WHERE os.organization_structure_id = osv.organization_structure_id AND pasg.manager_flag(+)='Y' AND osv.org_structure_version_id = ose.org_structure_version_id AND ose.organization_id_parent= hou1.organization_id AND ose.organization_id_parent = pasg.organization_id (+) AND ose.business_group_id = bg.organization_id(+) AND not exists ( SELECT null FROM per_org_structure_elements ose2 WHERE ose2.organization_id_child = ose.organization_id_parent) AND hou1.location_id = loc.location_id(+) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN osv.date_from AND NVL(osv.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN hou1.date_from AND NVL(hou1.date_to,to_date('31-12-4712','DD-MM-YYYY')) AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(bg.date_from(+),to_date('01-01-0001','DD-MM-YYYY')) AND NVL(bg.date_to(+),to_date('31-12-4712','DD-MM-YYYY'))

View Text - HTML Formatted

SELECT HOU1.NAME
, LOC.LOCATION_CODE
, HOU1.INTERNAL_ADDRESS_LINE
, OS.NAME
, OSV.VERSION_NUMBER
, OSV.DATE_FROM
, OSV.DATE_TO
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, HOU1.TYPE)
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, OSE.ORGANIZATION_ID_CHILD
, OSE.ORGANIZATION_ID_PARENT
, OSV.ORG_STRUCTURE_VERSION_ID
FROM HR_LOCATIONS LOC
, HR_HIER_ASG_PEOPLE_V PASG
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS HOU1
, PER_ORG_STRUCTURE_ELEMENTS OSE
, PER_ORG_STRUCTURE_VERSIONS OSV
, PER_ORGANIZATION_STRUCTURES OS
WHERE OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
AND PASG.MANAGER_FLAG(+)='Y'
AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID
AND OSE.ORGANIZATION_ID_CHILD = HOU1.ORGANIZATION_ID
AND OSE.ORGANIZATION_ID_CHILD = PASG.ORGANIZATION_ID(+)
AND HOU1.LOCATION_ID = LOC.LOCATION_ID(+)
AND OS.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID(+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN OSV.DATE_FROM
AND NVL(OSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN HOU1.DATE_FROM
AND NVL(HOU1.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(BG.DATE_FROM(+)
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(BG.DATE_TO(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) UNION SELECT DISTINCT HOU1.NAME
, LOC.LOCATION_CODE
, HOU1.INTERNAL_ADDRESS_LINE
, OS.NAME
, OSV.VERSION_NUMBER
, OSV.DATE_FROM
, OSV.DATE_TO
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, HOU1.TYPE) ORG_TYPE
, PASG.FULL_NAME
, PASG.EMPLOYEE_NUMBER
, PASG.KNOWN_AS
, PASG.EMAIL_ADDRESS
, PASG.MAILSTOP
, PASG.WORK_PHONE_NUMBER
, BG.NAME
, OSE.ORGANIZATION_ID_PARENT
, 0
, OSV.ORG_STRUCTURE_VERSION_ID
FROM HR_LOCATIONS LOC
, HR_HIER_ASG_PEOPLE_V PASG
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS HOU1
, PER_ORG_STRUCTURE_ELEMENTS OSE
, PER_ORG_STRUCTURE_VERSIONS OSV
, PER_ORGANIZATION_STRUCTURES OS
WHERE OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
AND PASG.MANAGER_FLAG(+)='Y'
AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID
AND OSE.ORGANIZATION_ID_PARENT= HOU1.ORGANIZATION_ID
AND OSE.ORGANIZATION_ID_PARENT = PASG.ORGANIZATION_ID (+)
AND OSE.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID(+)
AND NOT EXISTS ( SELECT NULL
FROM PER_ORG_STRUCTURE_ELEMENTS OSE2
WHERE OSE2.ORGANIZATION_ID_CHILD = OSE.ORGANIZATION_ID_PARENT)
AND HOU1.LOCATION_ID = LOC.LOCATION_ID(+)
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN OSV.DATE_FROM
AND NVL(OSV.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN HOU1.DATE_FROM
AND NVL(HOU1.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_THIRD_PARTY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(BG.DATE_FROM(+)
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(BG.DATE_TO(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))