VIEW: APPS.HRIBV_ASGMVORGHRCHY

Source

View Text - Preformatted

SELECT hr_bis.bis_decode_lookup('HRI_MOVE_TYPES','ORGHRCHY_WITHIN') move_type , hr_bis.bis_decode_lookup('HRI_GAIN_TYPES','NA_EDW') gain_type , hr_bis.bis_decode_lookup('HRI_LOSS_TYPES','NA_EDW') loss_type , ost.name organization_hrchy_name , osv1.version_number org_hierarchy_version , orgtop.name Top_Organization_name , org1.name Organization_name , org2.name other_Organization_name , org1.name from_organization_name , org2.name to_organization_name , peo.full_name Employee_name , asg1.assignment_number Assignment_Number , asg2.effective_start_date change_date , asg1.effective_start_date asg_effective_start_date , asg1.effective_end_date asg_effective_end_date , asg2.effective_start_date other_asg_effective_start_date , asg2.effective_end_date other_asg_effective_end_date , osv1.date_from hierarchy_version_date_from , osv1.date_to hierarchy_version_date_to , NVL(osv1.date_to, hr_general.end_of_time) hierarchy_version_date_to_nn , 'ORGHRCHY_WITHIN' move_type_code , 'NA_EDW' gain_type_code , 'NA_EDW' loss_type_code , ost.organization_structure_id organization_structure_id , oss1.org_structure_version_id org_structure_version_id , oss1.organization_id parent_org_id , asg1.assignment_id assignment_id , asg1.organization_id organization_id , asg1.business_group_id business_group_id , asg1.person_id person_id , asg2.organization_id other_organization_id , asg2.business_group_id other_business_group_id , org1.organization_id organization_from_id , org2.organization_id organization_to_id FROM hri_org_hrchy_summary oss1 , per_org_structure_versions osv1 , per_organization_structures ost , per_assignments_f asg1 , per_assignments_f asg2 , per_all_people_f peo , hr_all_organization_units_TL org1 , hr_all_organization_units_TL org2 , hr_all_organization_units_TL orgtop WHERE asg1.assignment_id = asg2.assignment_id AND asg1.organization_id = oss1.sub_organization_id AND oss1.org_structure_version_id = osv1.org_structure_version_id AND osv1.organization_structure_id = ost.organization_structure_id AND asg2.effective_start_date BETWEEN osv1.date_from AND nvl(osv1.date_to,hr_general.end_of_time) AND asg1.organization_id = org1.organization_id AND org1.language = USERENV('LANG') AND asg2.organization_id = org2.organization_id AND org2.language = USERENV('LANG') AND oss1.organization_id = orgtop.organization_id AND orgtop.language = USERENV('LANG') AND asg2.person_id = peo.person_id AND asg2.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg1.assignment_type = 'E' AND asg2.assignment_type = 'E' AND asg1.organization_id <> asg2.organization_id AND (asg1.effective_end_date+1 = asg2.effective_start_date) AND EXISTS (SELECT 1 FROM hri_org_hrchy_summary oss2 , per_org_structure_versions osv2 WHERE asg2.organization_id = oss2.sub_organization_id AND oss1.org_structure_version_id = oss2.org_structure_version_id AND oss2.org_structure_version_id = osv2.org_structure_version_id AND asg2.effective_start_date BETWEEN osv2.date_from AND nvl(osv2.date_to,hr_general.end_of_time) AND oss1.organization_id = oss2.organization_id ) UNION ALL SELECT hr_bis.bis_decode_lookup('HRI_MOVE_TYPES','ORGHRCHY_IN') move_type , hr_bis.bis_decode_lookup('HRI_GAIN_TYPES','GAIN_ORG') gain_type , hr_bis.bis_decode_lookup('HRI_GAIN_TYPES','NA_EDW') loss_type , ost.name organization_hrchy_name , osv2.version_number org_hierarchy_version , orgtop.name Top_Organization_name , org2.name Organization_name , org1.name other_Organization_name , org1.name from_organization_name , org2.name to_organization_name , peo.full_name Employee_name , asg2.assignment_number Assignment_Number , asg2.effective_start_date change_date , asg2.effective_start_date asg_effective_start_date , asg2.effective_end_date asg_effective_end_date , asg1.effective_start_date other_asg_effective_start_date , asg1.effective_end_date other_asg_effective_end_date , osv2.date_from hierarchy_version_date_from , osv2.date_to hierarchy_version_date_to , NVL(osv2.date_to, hr_general.end_of_time) hierarchy_version_date_to_nn , 'ORGHRCHY_IN' move_type_code , 'GAIN_ORG' gain_type_code , 'NA_EDW' loss_type_code , ost.organization_structure_id organization_structure_id , oss2.org_structure_version_id org_structure_version_id , oss2.organization_id parent_org_id , asg1.assignment_id assignment_id , asg1.organization_id organization_id , asg1.business_group_id business_group_id , asg1.person_id person_id , asg2.organization_id other_organization_id , asg2.business_group_id other_business_group_id , org1.organization_id organization_from_id , org2.organization_id organization_to_id FROM hri_org_hrchy_summary oss2 , per_org_structure_versions osv2 , per_organization_structures ost , per_assignments_f asg2 , per_assignments_f asg1 , per_all_people_f peo , hr_all_organization_units_TL org1 , hr_all_organization_units_TL org2 , hr_all_organization_units_TL orgtop WHERE asg1.assignment_id = asg2.assignment_id AND asg2.organization_id = oss2.sub_organization_id AND oss2.org_structure_version_id = osv2.org_structure_version_id AND osv2.organization_structure_id = ost.organization_structure_id AND asg2.effective_start_date BETWEEN osv2.date_from AND nvl(osv2.date_to,hr_general.end_of_time) AND asg1.organization_id = org1.organization_id AND org1.language = USERENV('LANG') AND asg2.organization_id = org2.organization_id AND org2.language = USERENV('LANG') AND oss2.organization_id = orgtop.organization_id AND orgtop.language = USERENV('LANG') AND asg2.person_id = peo.person_id AND asg2.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg1.assignment_type = 'E' AND asg2.assignment_type = 'E' AND asg1.organization_id <> asg2.organization_id AND (asg1.effective_end_date+1 = asg2.effective_start_date) AND NOT EXISTS (SELECT 1 FROM hri_org_hrchy_summary oss1 , per_org_structure_versions osv1 WHERE asg1.organization_id = oss1.sub_organization_id AND oss2.org_structure_version_id = oss1.org_structure_version_id AND oss1.org_structure_version_id = osv1.org_structure_version_id AND asg2.effective_start_date BETWEEN osv1.date_from AND nvl(osv1.date_to,hr_general.end_of_time) AND oss2.organization_id = oss1.organization_id ) UNION ALL SELECT hr_bis.bis_decode_lookup('HRI_MOVE_TYPES','ORGHRCHY_OUT') move_type , hr_bis.bis_decode_lookup('HRI_LOSS_TYPES','NA_EDW') gain_type , hr_bis.bis_decode_lookup('HRI_LOSS_TYPES','LOSS_ORG') loss_type , ost.name organization_hrchy_name , osv1.version_number org_hierarchy_version , orgtop.name Top_Organization_name , org1.name Organization_name , org2.name other_Organization_name , org1.name from_organization_name , org2.name to_organization_name , peo.full_name Employee_name , asg1.assignment_number Assignment_Number , asg2.effective_start_date change_date , asg1.effective_start_date asg_effective_start_date , asg1.effective_end_date asg_effective_end_date , asg2.effective_start_date other_asg_effective_start_date , asg2.effective_end_date other_asg_effective_end_date , osv1.date_from hierarchy_version_date_from , osv1.date_to hierarchy_version_date_to , NVL(osv1.date_to, hr_general.end_of_time) hierarchy_version_date_to_nn , 'ORGHRCHY_OUT' move_type_code , 'NA_EDW' gain_type_code , 'LOSS_ORG' loss_type_code , ost.organization_structure_id organization_structure_id , oss1.org_structure_version_id org_structure_version_id , oss1.organization_id parent_org_id , asg1.assignment_id assignment_id , asg1.organization_id organization_id , asg1.business_group_id business_group_id , asg1.person_id person_id , asg2.organization_id other_organization_id , asg2.business_group_id other_business_group_id , org1.organization_id organization_from_id , org2.organization_id organization_to_id FROM hri_org_hrchy_summary oss1 , per_org_structure_versions osv1 , per_organization_structures ost , per_assignments_f asg1 , per_assignments_f asg2 , per_all_people_f peo , hr_all_organization_units_TL org1 , hr_all_organization_units_TL org2 , hr_all_organization_units_TL orgtop WHERE asg1.assignment_id = asg2.assignment_id AND asg1.organization_id = oss1.sub_organization_id AND oss1.org_structure_version_id = osv1.org_structure_version_id AND osv1.organization_structure_id = ost.organization_structure_id AND asg2.effective_start_date BETWEEN osv1.date_from AND nvl(osv1.date_to,hr_general.end_of_time) AND asg1.organization_id = org1.organization_id AND org1.language = USERENV('LANG') AND asg2.organization_id = org2.organization_id AND org2.language = USERENV('LANG') AND oss1.organization_id = orgtop.organization_id AND orgtop.language = USERENV('LANG') AND asg2.person_id = peo.person_id AND asg2.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg1.assignment_type = 'E' AND asg2.assignment_type = 'E' AND asg1.organization_id <> asg2.organization_id AND (asg1.effective_end_date+1 = asg2.effective_start_date) AND NOT EXISTS (SELECT 1 FROM hri_org_hrchy_summary oss2 , per_org_structure_versions osv2 WHERE asg2.organization_id = oss2.sub_organization_id AND oss1.org_structure_version_id = oss2.org_structure_version_id AND oss2.org_structure_version_id = osv2.org_structure_version_id AND asg2.effective_start_date BETWEEN osv2.date_from AND nvl(osv2.date_to,hr_general.end_of_time) AND oss1.organization_id = oss2.organization_id ) WITH READ ONLY

View Text - HTML Formatted

SELECT HR_BIS.BIS_DECODE_LOOKUP('HRI_MOVE_TYPES'
, 'ORGHRCHY_WITHIN') MOVE_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HRI_GAIN_TYPES'
, 'NA_EDW') GAIN_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HRI_LOSS_TYPES'
, 'NA_EDW') LOSS_TYPE
, OST.NAME ORGANIZATION_HRCHY_NAME
, OSV1.VERSION_NUMBER ORG_HIERARCHY_VERSION
, ORGTOP.NAME TOP_ORGANIZATION_NAME
, ORG1.NAME ORGANIZATION_NAME
, ORG2.NAME OTHER_ORGANIZATION_NAME
, ORG1.NAME FROM_ORGANIZATION_NAME
, ORG2.NAME TO_ORGANIZATION_NAME
, PEO.FULL_NAME EMPLOYEE_NAME
, ASG1.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG2.EFFECTIVE_START_DATE CHANGE_DATE
, ASG1.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG1.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, ASG2.EFFECTIVE_START_DATE OTHER_ASG_EFFECTIVE_START_DATE
, ASG2.EFFECTIVE_END_DATE OTHER_ASG_EFFECTIVE_END_DATE
, OSV1.DATE_FROM HIERARCHY_VERSION_DATE_FROM
, OSV1.DATE_TO HIERARCHY_VERSION_DATE_TO
, NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME) HIERARCHY_VERSION_DATE_TO_NN
, 'ORGHRCHY_WITHIN' MOVE_TYPE_CODE
, 'NA_EDW' GAIN_TYPE_CODE
, 'NA_EDW' LOSS_TYPE_CODE
, OST.ORGANIZATION_STRUCTURE_ID ORGANIZATION_STRUCTURE_ID
, OSS1.ORG_STRUCTURE_VERSION_ID ORG_STRUCTURE_VERSION_ID
, OSS1.ORGANIZATION_ID PARENT_ORG_ID
, ASG1.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG1.ORGANIZATION_ID ORGANIZATION_ID
, ASG1.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG1.PERSON_ID PERSON_ID
, ASG2.ORGANIZATION_ID OTHER_ORGANIZATION_ID
, ASG2.BUSINESS_GROUP_ID OTHER_BUSINESS_GROUP_ID
, ORG1.ORGANIZATION_ID ORGANIZATION_FROM_ID
, ORG2.ORGANIZATION_ID ORGANIZATION_TO_ID
FROM HRI_ORG_HRCHY_SUMMARY OSS1
, PER_ORG_STRUCTURE_VERSIONS OSV1
, PER_ORGANIZATION_STRUCTURES OST
, PER_ASSIGNMENTS_F ASG1
, PER_ASSIGNMENTS_F ASG2
, PER_ALL_PEOPLE_F PEO
, HR_ALL_ORGANIZATION_UNITS_TL ORG1
, HR_ALL_ORGANIZATION_UNITS_TL ORG2
, HR_ALL_ORGANIZATION_UNITS_TL ORGTOP
WHERE ASG1.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND ASG1.ORGANIZATION_ID = OSS1.SUB_ORGANIZATION_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSV1.ORG_STRUCTURE_VERSION_ID
AND OSV1.ORGANIZATION_STRUCTURE_ID = OST.ORGANIZATION_STRUCTURE_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN OSV1.DATE_FROM
AND NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
AND ORG1.LANGUAGE = USERENV('LANG')
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID
AND ORG2.LANGUAGE = USERENV('LANG')
AND OSS1.ORGANIZATION_ID = ORGTOP.ORGANIZATION_ID
AND ORGTOP.LANGUAGE = USERENV('LANG')
AND ASG2.PERSON_ID = PEO.PERSON_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG1.ASSIGNMENT_TYPE = 'E'
AND ASG2.ASSIGNMENT_TYPE = 'E'
AND ASG1.ORGANIZATION_ID <> ASG2.ORGANIZATION_ID
AND (ASG1.EFFECTIVE_END_DATE+1 = ASG2.EFFECTIVE_START_DATE)
AND EXISTS (SELECT 1
FROM HRI_ORG_HRCHY_SUMMARY OSS2
, PER_ORG_STRUCTURE_VERSIONS OSV2
WHERE ASG2.ORGANIZATION_ID = OSS2.SUB_ORGANIZATION_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSS2.ORG_STRUCTURE_VERSION_ID
AND OSS2.ORG_STRUCTURE_VERSION_ID = OSV2.ORG_STRUCTURE_VERSION_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN OSV2.DATE_FROM
AND NVL(OSV2.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND OSS1.ORGANIZATION_ID = OSS2.ORGANIZATION_ID ) UNION ALL SELECT HR_BIS.BIS_DECODE_LOOKUP('HRI_MOVE_TYPES'
, 'ORGHRCHY_IN') MOVE_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HRI_GAIN_TYPES'
, 'GAIN_ORG') GAIN_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HRI_GAIN_TYPES'
, 'NA_EDW') LOSS_TYPE
, OST.NAME ORGANIZATION_HRCHY_NAME
, OSV2.VERSION_NUMBER ORG_HIERARCHY_VERSION
, ORGTOP.NAME TOP_ORGANIZATION_NAME
, ORG2.NAME ORGANIZATION_NAME
, ORG1.NAME OTHER_ORGANIZATION_NAME
, ORG1.NAME FROM_ORGANIZATION_NAME
, ORG2.NAME TO_ORGANIZATION_NAME
, PEO.FULL_NAME EMPLOYEE_NAME
, ASG2.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG2.EFFECTIVE_START_DATE CHANGE_DATE
, ASG2.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG2.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, ASG1.EFFECTIVE_START_DATE OTHER_ASG_EFFECTIVE_START_DATE
, ASG1.EFFECTIVE_END_DATE OTHER_ASG_EFFECTIVE_END_DATE
, OSV2.DATE_FROM HIERARCHY_VERSION_DATE_FROM
, OSV2.DATE_TO HIERARCHY_VERSION_DATE_TO
, NVL(OSV2.DATE_TO
, HR_GENERAL.END_OF_TIME) HIERARCHY_VERSION_DATE_TO_NN
, 'ORGHRCHY_IN' MOVE_TYPE_CODE
, 'GAIN_ORG' GAIN_TYPE_CODE
, 'NA_EDW' LOSS_TYPE_CODE
, OST.ORGANIZATION_STRUCTURE_ID ORGANIZATION_STRUCTURE_ID
, OSS2.ORG_STRUCTURE_VERSION_ID ORG_STRUCTURE_VERSION_ID
, OSS2.ORGANIZATION_ID PARENT_ORG_ID
, ASG1.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG1.ORGANIZATION_ID ORGANIZATION_ID
, ASG1.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG1.PERSON_ID PERSON_ID
, ASG2.ORGANIZATION_ID OTHER_ORGANIZATION_ID
, ASG2.BUSINESS_GROUP_ID OTHER_BUSINESS_GROUP_ID
, ORG1.ORGANIZATION_ID ORGANIZATION_FROM_ID
, ORG2.ORGANIZATION_ID ORGANIZATION_TO_ID
FROM HRI_ORG_HRCHY_SUMMARY OSS2
, PER_ORG_STRUCTURE_VERSIONS OSV2
, PER_ORGANIZATION_STRUCTURES OST
, PER_ASSIGNMENTS_F ASG2
, PER_ASSIGNMENTS_F ASG1
, PER_ALL_PEOPLE_F PEO
, HR_ALL_ORGANIZATION_UNITS_TL ORG1
, HR_ALL_ORGANIZATION_UNITS_TL ORG2
, HR_ALL_ORGANIZATION_UNITS_TL ORGTOP
WHERE ASG1.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND ASG2.ORGANIZATION_ID = OSS2.SUB_ORGANIZATION_ID
AND OSS2.ORG_STRUCTURE_VERSION_ID = OSV2.ORG_STRUCTURE_VERSION_ID
AND OSV2.ORGANIZATION_STRUCTURE_ID = OST.ORGANIZATION_STRUCTURE_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN OSV2.DATE_FROM
AND NVL(OSV2.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
AND ORG1.LANGUAGE = USERENV('LANG')
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID
AND ORG2.LANGUAGE = USERENV('LANG')
AND OSS2.ORGANIZATION_ID = ORGTOP.ORGANIZATION_ID
AND ORGTOP.LANGUAGE = USERENV('LANG')
AND ASG2.PERSON_ID = PEO.PERSON_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG1.ASSIGNMENT_TYPE = 'E'
AND ASG2.ASSIGNMENT_TYPE = 'E'
AND ASG1.ORGANIZATION_ID <> ASG2.ORGANIZATION_ID
AND (ASG1.EFFECTIVE_END_DATE+1 = ASG2.EFFECTIVE_START_DATE)
AND NOT EXISTS (SELECT 1
FROM HRI_ORG_HRCHY_SUMMARY OSS1
, PER_ORG_STRUCTURE_VERSIONS OSV1
WHERE ASG1.ORGANIZATION_ID = OSS1.SUB_ORGANIZATION_ID
AND OSS2.ORG_STRUCTURE_VERSION_ID = OSS1.ORG_STRUCTURE_VERSION_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSV1.ORG_STRUCTURE_VERSION_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN OSV1.DATE_FROM
AND NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND OSS2.ORGANIZATION_ID = OSS1.ORGANIZATION_ID ) UNION ALL SELECT HR_BIS.BIS_DECODE_LOOKUP('HRI_MOVE_TYPES'
, 'ORGHRCHY_OUT') MOVE_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HRI_LOSS_TYPES'
, 'NA_EDW') GAIN_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HRI_LOSS_TYPES'
, 'LOSS_ORG') LOSS_TYPE
, OST.NAME ORGANIZATION_HRCHY_NAME
, OSV1.VERSION_NUMBER ORG_HIERARCHY_VERSION
, ORGTOP.NAME TOP_ORGANIZATION_NAME
, ORG1.NAME ORGANIZATION_NAME
, ORG2.NAME OTHER_ORGANIZATION_NAME
, ORG1.NAME FROM_ORGANIZATION_NAME
, ORG2.NAME TO_ORGANIZATION_NAME
, PEO.FULL_NAME EMPLOYEE_NAME
, ASG1.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG2.EFFECTIVE_START_DATE CHANGE_DATE
, ASG1.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG1.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, ASG2.EFFECTIVE_START_DATE OTHER_ASG_EFFECTIVE_START_DATE
, ASG2.EFFECTIVE_END_DATE OTHER_ASG_EFFECTIVE_END_DATE
, OSV1.DATE_FROM HIERARCHY_VERSION_DATE_FROM
, OSV1.DATE_TO HIERARCHY_VERSION_DATE_TO
, NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME) HIERARCHY_VERSION_DATE_TO_NN
, 'ORGHRCHY_OUT' MOVE_TYPE_CODE
, 'NA_EDW' GAIN_TYPE_CODE
, 'LOSS_ORG' LOSS_TYPE_CODE
, OST.ORGANIZATION_STRUCTURE_ID ORGANIZATION_STRUCTURE_ID
, OSS1.ORG_STRUCTURE_VERSION_ID ORG_STRUCTURE_VERSION_ID
, OSS1.ORGANIZATION_ID PARENT_ORG_ID
, ASG1.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG1.ORGANIZATION_ID ORGANIZATION_ID
, ASG1.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG1.PERSON_ID PERSON_ID
, ASG2.ORGANIZATION_ID OTHER_ORGANIZATION_ID
, ASG2.BUSINESS_GROUP_ID OTHER_BUSINESS_GROUP_ID
, ORG1.ORGANIZATION_ID ORGANIZATION_FROM_ID
, ORG2.ORGANIZATION_ID ORGANIZATION_TO_ID
FROM HRI_ORG_HRCHY_SUMMARY OSS1
, PER_ORG_STRUCTURE_VERSIONS OSV1
, PER_ORGANIZATION_STRUCTURES OST
, PER_ASSIGNMENTS_F ASG1
, PER_ASSIGNMENTS_F ASG2
, PER_ALL_PEOPLE_F PEO
, HR_ALL_ORGANIZATION_UNITS_TL ORG1
, HR_ALL_ORGANIZATION_UNITS_TL ORG2
, HR_ALL_ORGANIZATION_UNITS_TL ORGTOP
WHERE ASG1.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND ASG1.ORGANIZATION_ID = OSS1.SUB_ORGANIZATION_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSV1.ORG_STRUCTURE_VERSION_ID
AND OSV1.ORGANIZATION_STRUCTURE_ID = OST.ORGANIZATION_STRUCTURE_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN OSV1.DATE_FROM
AND NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
AND ORG1.LANGUAGE = USERENV('LANG')
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID
AND ORG2.LANGUAGE = USERENV('LANG')
AND OSS1.ORGANIZATION_ID = ORGTOP.ORGANIZATION_ID
AND ORGTOP.LANGUAGE = USERENV('LANG')
AND ASG2.PERSON_ID = PEO.PERSON_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG1.ASSIGNMENT_TYPE = 'E'
AND ASG2.ASSIGNMENT_TYPE = 'E'
AND ASG1.ORGANIZATION_ID <> ASG2.ORGANIZATION_ID
AND (ASG1.EFFECTIVE_END_DATE+1 = ASG2.EFFECTIVE_START_DATE)
AND NOT EXISTS (SELECT 1
FROM HRI_ORG_HRCHY_SUMMARY OSS2
, PER_ORG_STRUCTURE_VERSIONS OSV2
WHERE ASG2.ORGANIZATION_ID = OSS2.SUB_ORGANIZATION_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSS2.ORG_STRUCTURE_VERSION_ID
AND OSS2.ORG_STRUCTURE_VERSION_ID = OSV2.ORG_STRUCTURE_VERSION_ID
AND ASG2.EFFECTIVE_START_DATE BETWEEN OSV2.DATE_FROM
AND NVL(OSV2.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND OSS1.ORGANIZATION_ID = OSS2.ORGANIZATION_ID ) WITH READ ONLY