View: PER_ASSIGNMENTS_V2

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_ASSIGNMENTS_V2

View Text

SELECT A.ROWID ROW_ID
, A.BUSINESS_GROUP_ID
, A.PERSON_ID
, A.PERIOD_OF_SERVICE_ID
, A.ASSIGNMENT_NUMBER
, A.EFFECTIVE_START_DATE
, A.ORGANIZATION_ID
, A.JOB_ID
, A.POSITION_ID
, A.GRADE_ID
, A.PEOPLE_GROUP_ID
, A.PAYROLL_ID
, A.ASSIGNMENT_ID
, A.LOCATION_ID
, A.ASSIGNMENT_STATUS_TYPE_ID
, A.CHANGE_REASON
, A.ASSIGNMENT_SEQUENCE
, A.SUPERVISOR_ID
, A.DEFAULT_CODE_COMB_ID
, A.NORMAL_HOURS
, A.ASS_ATTRIBUTE_CATEGORY
, A.ASS_ATTRIBUTE1
, A.ASS_ATTRIBUTE2
, A.ASS_ATTRIBUTE3
, A.ASS_ATTRIBUTE4
, A.ASS_ATTRIBUTE5
, A.ASS_ATTRIBUTE6
, A.ASS_ATTRIBUTE7
, A.ASS_ATTRIBUTE8
, A.ASS_ATTRIBUTE9
, A.ASS_ATTRIBUTE10
, A.ASS_ATTRIBUTE11
, A.ASS_ATTRIBUTE12
, A.ASS_ATTRIBUTE13
, A.ASS_ATTRIBUTE14
, A.ASS_ATTRIBUTE15
, A.ASS_ATTRIBUTE16
, A.ASS_ATTRIBUTE17
, A.ASS_ATTRIBUTE18
, A.ASS_ATTRIBUTE19
, A.ASS_ATTRIBUTE20
, A.ASS_ATTRIBUTE21
, A.ASS_ATTRIBUTE22
, A.ASS_ATTRIBUTE23
, A.ASS_ATTRIBUTE24
, A.ASS_ATTRIBUTE25
, A.ASS_ATTRIBUTE26
, A.ASS_ATTRIBUTE27
, A.ASS_ATTRIBUTE28
, A.ASS_ATTRIBUTE29
, A.ASS_ATTRIBUTE30
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, ORGTL.NAME ORGANIZATION
, GR.NAME GRADE
, PAY.PAYROLL_NAME PAYROLL
, JOB.NAME JOB
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID) POSITION /*
, POS.NAME POSITION */
, LOCTL.LOCATION_CODE
, LU2.MEANING EMPLOYMENT_CATEGORY
, PPB.NAME SALARY_BASIS
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS) STATUS
, LU.MEANING REASON
FROM HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_GRADES GR
, PAY_ALL_PAYROLLS_F PAY
, PER_JOBS JOB
, HR_LOOKUPS LU /*
, PER_ALL_POSITIONS POS */
, HR_LOCATIONS_NO_JOIN LOC
, HR_LOCATIONS_ALL_TL LOCTL
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTATL
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, PER_ALL_ASSIGNMENTS_F A
, PER_PAY_BASES PPB
, HR_LOOKUPS LU2
WHERE A.ASSIGNMENT_TYPE = 'E'
AND NOT EXISTS (SELECT NULL
FROM PER_ASSIGNMENTS_F A2
WHERE A2.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND A2.ORGANIZATION_ID = A.ORGANIZATION_ID
AND A2.ASSIGNMENT_NUMBER = A.ASSIGNMENT_NUMBER
AND A2.ASSIGNMENT_STATUS_TYPE_ID = A.ASSIGNMENT_STATUS_TYPE_ID
AND NVL(A2.JOB_ID
, 9.9) = NVL(A.JOB_ID
, 9.9)
AND NVL(A2.POSITION_ID
, 9.9) = NVL(A.POSITION_ID
, 9.9)
AND NVL(A2.GRADE_ID
, 9.9) = NVL(A.GRADE_ID
, 9.9)
AND NVL(A2.PAYROLL_ID
, 9.9) = NVL(A.PAYROLL_ID
, 9.9) /* FIX TO BUG 293432 - KHABIBUL */
AND NVL(A2.LOCATION_ID
, 9.9) = NVL(A.LOCATION_ID
, 9.9) /* END OF FIX */ /* FIX TO BUG 317753 - AFORTE */
AND NVL(A2.PAY_BASIS_ID
, 9.9) = NVL(A.PAY_BASIS_ID
, 9.9) /* END OF FIX */ /* FIX TO BUG 410755 - MSHAH */
AND NVL(A2.EMPLOYMENT_CATEGORY
, 'XXX') = NVL(A.EMPLOYMENT_CATEGORY
, 'XXX') /* END OF FIX */
AND NVL(A2.PEOPLE_GROUP_ID
, 9.9) = NVL(A.PEOPLE_GROUP_ID
, 9.9)
AND NVL(A2.CHANGE_REASON
, '9.9') = NVL(A.CHANGE_REASON
, '9.9')
AND NVL(A2.SUPERVISOR_ID
, 9.9) = NVL(A.SUPERVISOR_ID
, 9.9)
AND NVL(A2.DEFAULT_CODE_COMB_ID
, 9.9) = NVL(A.DEFAULT_CODE_COMB_ID
, 9.9)
AND NVL(A2.NORMAL_HOURS
, 9.9999) = NVL(A.NORMAL_HOURS
, 9.9999)
AND NVL(A2.ASS_ATTRIBUTE1
, 'XXX') = NVL(A.ASS_ATTRIBUTE1
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE2
, 'XXX') = NVL(A.ASS_ATTRIBUTE2
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE3
, 'XXX') = NVL(A.ASS_ATTRIBUTE3
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE4
, 'XXX') = NVL(A.ASS_ATTRIBUTE4
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE5
, 'XXX') = NVL(A.ASS_ATTRIBUTE5
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE6
, 'XXX') = NVL(A.ASS_ATTRIBUTE6
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE7
, 'XXX') = NVL(A.ASS_ATTRIBUTE7
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE8
, 'XXX') = NVL(A.ASS_ATTRIBUTE8
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE9
, 'XXX') = NVL(A.ASS_ATTRIBUTE9
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE10
, 'XXX') = NVL(A.ASS_ATTRIBUTE10
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE11
, 'XXX') = NVL(A.ASS_ATTRIBUTE11
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE12
, 'XXX') = NVL(A.ASS_ATTRIBUTE12
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE13
, 'XXX') = NVL(A.ASS_ATTRIBUTE13
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE14
, 'XXX') = NVL(A.ASS_ATTRIBUTE14
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE15
, 'XXX') = NVL(A.ASS_ATTRIBUTE15
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE16
, 'XXX') = NVL(A.ASS_ATTRIBUTE16
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE17
, 'XXX') = NVL(A.ASS_ATTRIBUTE17
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE18
, 'XXX') = NVL(A.ASS_ATTRIBUTE18
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE19
, 'XXX') = NVL(A.ASS_ATTRIBUTE19
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE20
, 'XXX') = NVL(A.ASS_ATTRIBUTE20
, 'XXX') /* FIX FOR BUG. 1576939 - STLOCKE*/
AND NVL(A2.ASS_ATTRIBUTE21
, 'XXX') = NVL(A.ASS_ATTRIBUTE21
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE22
, 'XXX') = NVL(A.ASS_ATTRIBUTE22
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE23
, 'XXX') = NVL(A.ASS_ATTRIBUTE23
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE24
, 'XXX') = NVL(A.ASS_ATTRIBUTE24
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE25
, 'XXX') = NVL(A.ASS_ATTRIBUTE25
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE26
, 'XXX') = NVL(A.ASS_ATTRIBUTE26
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE27
, 'XXX') = NVL(A.ASS_ATTRIBUTE27
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE28
, 'XXX') = NVL(A.ASS_ATTRIBUTE28
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE29
, 'XXX') = NVL(A.ASS_ATTRIBUTE29
, 'XXX')
AND NVL(A2.ASS_ATTRIBUTE30
, 'XXX') = NVL(A.ASS_ATTRIBUTE30
, 'XXX') /*END OF FIX FOR BUG. 1576939 - STLOCKE*/
AND A2.EFFECTIVE_START_DATE = (SELECT MAX(A3.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F A3
WHERE A3.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND A3.ASSIGNMENT_TYPE = 'E'
AND A3.EFFECTIVE_START_DATE < A.EFFECTIVE_START_DATE))
AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND A.GRADE_ID = GR.GRADE_ID (+)
AND A.PAYROLL_ID = PAY.PAYROLL_ID (+)
AND A.EFFECTIVE_START_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE
, A.EFFECTIVE_START_DATE)
AND NVL(PAY.EFFECTIVE_END_DATE
, A.EFFECTIVE_END_DATE)
AND A.JOB_ID = JOB.JOB_ID (+) /*
AND A.POSITION_ID = POS.POSITION_ID (+) */
AND A.LOCATION_ID = LOC.LOCATION_ID (+)
AND A.PAY_BASIS_ID = PPB.PAY_BASIS_ID(+)
AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND A.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND A.BUSINESS_GROUP_ID + 0 = ASTA.BUSINESS_GROUP_ID(+) + 0
AND A.CHANGE_REASON = LU.LOOKUP_CODE (+)
AND LU.LOOKUP_TYPE (+) = 'EMP_ASSIGN_REASON'
AND A.EMPLOYMENT_CATEGORY = LU2.LOOKUP_CODE(+)
AND LU2.LOOKUP_TYPE(+) = 'EMP_CAT'
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID
AND ORGTL.LANGUAGE = USERENV('LANG')
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTTL.LANGUAGE = USERENV('LANG')
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTATL.ASS_STATUS_TYPE_AMEND_ID (+)
AND DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, ASTATL.LANGUAGE) = DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, USERENV('LANG'))
AND LOC.LOCATION_ID = LOCTL.LOCATION_ID (+)
AND DECODE(LOCTL.LOCATION_ID
, NULL
, '1'
, LOCTL.LANGUAGE)= DECODE(LOCTL.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))

Columns