View: HRIBV_ASGMVORGHRCHY

Product:	HRI - Human Resources Intelligence (Obsolete)
Description:
Implementation/DBA Data:	APPS.HRIBV_ASGMVORGHRCHY

View Text

SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ 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
, BGR1.NAME BUSINESS_GROUP_NAME
, BGR2.NAME OTHER_BUSINESS_GROUP_NAME
, 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_ID_FROM
, ORG2.ORGANIZATION_ID ORGANIZATION_ID_TO
FROM HRI_ORG_HRCHY_SUMMARY OSS1
, PER_ORG_STRUCTURE_VERSIONS OSV1
, PER_ORGANIZATION_STRUCTURES OST
, PER_ASSIGNMENTS_F ASG1
, PER_ASSIGNMENTS_F ASG2
, PER_ORG_STRUCTURE_VERSIONS OSV2
, HRI_ORG_HRCHY_SUMMARY OSS2
, PER_ALL_PEOPLE_F PEO
, HR_ALL_ORGANIZATION_UNITS ORG1
, HR_ALL_ORGANIZATION_UNITS ORG2
, HR_ALL_ORGANIZATION_UNITS ORGTOP
, HR_ALL_ORGANIZATION_UNITS BGR1
, HR_ALL_ORGANIZATION_UNITS BGR2
WHERE ASG1.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND ASG1.ORGANIZATION_ID = OSS1.SUB_ORGANIZATION_ID
AND ASG2.ORGANIZATION_ID = OSS2.SUB_ORGANIZATION_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSV1.ORG_STRUCTURE_VERSION_ID
AND OSS2.ORG_STRUCTURE_VERSION_ID = OSV2.ORG_STRUCTURE_VERSION_ID
AND OSV1.ORGANIZATION_STRUCTURE_ID = OST.ORGANIZATION_STRUCTURE_ID
AND OSS1.ORG_STRUCTURE_VERSION_ID = OSS2.ORG_STRUCTURE_VERSION_ID
AND TRUNC(ASG2.EFFECTIVE_START_DATE) BETWEEN OSV1.DATE_FROM
AND NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND TRUNC(ASG2.EFFECTIVE_START_DATE) BETWEEN OSV2.DATE_FROM
AND NVL(OSV2.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND ASG1.BUSINESS_GROUP_ID = BGR1.ORGANIZATION_ID
AND ASG2.BUSINESS_GROUP_ID = BGR2.ORGANIZATION_ID
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID
AND OSS2.ORGANIZATION_ID = ORGTOP.ORGANIZATION_ID
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 (TRUNC(ASG1.EFFECTIVE_END_DATE)+1 = TRUNC(ASG2.EFFECTIVE_START_DATE))
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
, BGR2.NAME BUSINESS_GROUP_NAME
, BGR1.NAME OTHER_BUSINESS_GROUP_NAME
, 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_ID_FROM
, ORG2.ORGANIZATION_ID ORGANIZATION_ID_TO
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 ORG1
, HR_ALL_ORGANIZATION_UNITS ORG2
, HR_ALL_ORGANIZATION_UNITS ORGTOP
, HR_ALL_ORGANIZATION_UNITS BGR1
, HR_ALL_ORGANIZATION_UNITS BGR2
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 TRUNC(ASG2.EFFECTIVE_START_DATE) BETWEEN OSV2.DATE_FROM
AND NVL(OSV2.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND ASG1.BUSINESS_GROUP_ID = BGR1.ORGANIZATION_ID
AND ASG2.BUSINESS_GROUP_ID = BGR2.ORGANIZATION_ID
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID
AND OSS2.ORGANIZATION_ID = ORGTOP.ORGANIZATION_ID
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 (TRUNC(ASG1.EFFECTIVE_END_DATE)+1 = TRUNC(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 TRUNC(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
, BGR1.NAME BUSINESS_GROUP_NAME
, BGR2.NAME OTHER_BUSINESS_GROUP_NAME
, 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_ID_FROM
, ORG2.ORGANIZATION_ID ORGANIZATION_ID_TO
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 ORG1
, HR_ALL_ORGANIZATION_UNITS ORG2
, HR_ALL_ORGANIZATION_UNITS ORGTOP
, HR_ALL_ORGANIZATION_UNITS BGR1
, HR_ALL_ORGANIZATION_UNITS BGR2
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 TRUNC(ASG2.EFFECTIVE_START_DATE) BETWEEN OSV1.DATE_FROM
AND NVL(OSV1.DATE_TO
, HR_GENERAL.END_OF_TIME)
AND ASG1.BUSINESS_GROUP_ID = BGR1.ORGANIZATION_ID
AND ASG2.BUSINESS_GROUP_ID = BGR2.ORGANIZATION_ID
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID
AND OSS1.ORGANIZATION_ID = ORGTOP.ORGANIZATION_ID
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 (TRUNC(ASG1.EFFECTIVE_END_DATE)+1 = TRUNC(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 TRUNC(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

Columns