View: PER_KR_ASSIGNMENTS_V

Product:	PER - Human Resources
Description:	KR specific
Implementation/DBA Data:	APPS.PER_KR_ASSIGNMENTS_V

View Text

SELECT PAAF.ROWID ROW_ID
, PAAF.BUSINESS_GROUP_ID
, PAAF.PERSON_ID
, PAAF.PERIOD_OF_SERVICE_ID
, PAAF.ASSIGNMENT_NUMBER
, PAAF.EFFECTIVE_START_DATE
, PAAF.ORGANIZATION_ID
, PAAF.JOB_ID
, PAAF.POSITION_ID
, PAAF.GRADE_ID
, PAAF.PEOPLE_GROUP_ID
, PAAF.PAYROLL_ID
, PAAF.ASSIGNMENT_ID
, PAAF.LOCATION_ID
, PAAF.ASSIGNMENT_STATUS_TYPE_ID
, PAAF.CHANGE_REASON
, PAAF.ASSIGNMENT_SEQUENCE
, PAAF.ASS_ATTRIBUTE_CATEGORY
, PAAF.ASS_ATTRIBUTE1
, PAAF.ASS_ATTRIBUTE2
, PAAF.ASS_ATTRIBUTE3
, PAAF.ASS_ATTRIBUTE4
, PAAF.ASS_ATTRIBUTE5
, PAAF.ASS_ATTRIBUTE6
, PAAF.ASS_ATTRIBUTE7
, PAAF.ASS_ATTRIBUTE8
, PAAF.ASS_ATTRIBUTE9
, PAAF.ASS_ATTRIBUTE10
, PAAF.ASS_ATTRIBUTE11
, PAAF.ASS_ATTRIBUTE12
, PAAF.ASS_ATTRIBUTE13
, PAAF.ASS_ATTRIBUTE14
, PAAF.ASS_ATTRIBUTE15
, PAAF.ASS_ATTRIBUTE16
, PAAF.ASS_ATTRIBUTE17
, PAAF.ASS_ATTRIBUTE18
, PAAF.ASS_ATTRIBUTE19
, PAAF.ASS_ATTRIBUTE20
, PAAF.ASS_ATTRIBUTE21
, PAAF.ASS_ATTRIBUTE22
, PAAF.ASS_ATTRIBUTE23
, PAAF.ASS_ATTRIBUTE24
, PAAF.ASS_ATTRIBUTE25
, PAAF.ASS_ATTRIBUTE26
, PAAF.ASS_ATTRIBUTE27
, PAAF.ASS_ATTRIBUTE28
, PAAF.ASS_ATTRIBUTE29
, PAAF.ASS_ATTRIBUTE30
, PAAF.LAST_UPDATE_DATE
, PAAF.LAST_UPDATED_BY
, PAAF.LAST_UPDATE_LOGIN
, PAAF.CREATED_BY
, PAAF.CREATION_DATE
, ORGTL.NAME ORGANIZATION
, GDT.NAME GRADE
, PAY.PAYROLL_NAME PAYROLL
, JBT.NAME JOB
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAAF.POSITION_ID) POSITION
, LOCTL.LOCATION_CODE
, LU2.MEANING EMPLOYMENT_CATEGORY
, PPB.NAME SALARY_BASIS
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS) STATUS
, LU.MEANING REASON
, PAAF.SOFT_CODING_KEYFLEX_ID
, LU3.MEANING KR_JOB_TITLE
, LU4.MEANING KR_SENIORITY
, PKG.GRADE_NAME KR_SECOND_GRADE
, PKGP.GRADE_POINT_NAME KR_SECOND_POINT
FROM HR_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_GRADES_TL GDT
, PAY_PAYROLLS_F PAY
, PER_JOBS_TL JBT
, HR_LOOKUPS LU
, 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_ASSIGNMENTS_F PAAF
, PER_PAY_BASES PPB
, HR_LOOKUPS LU2
, HR_LOOKUPS LU3
, HR_LOOKUPS LU4
, HR_SOFT_CODING_KEYFLEX HSCK
, PER_KR_GRADES PKG
, PER_KR_G_POINTS PKGP
WHERE PAAF.ASSIGNMENT_TYPE = 'E'
AND NOT EXISTS ( SELECT NULL FROM PER_ASSIGNMENTS_F PAAF2 WHERE PAAF2.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF2.ORGANIZATION_ID = PAAF.ORGANIZATION_ID
AND PAAF2.ASSIGNMENT_NUMBER = PAAF.ASSIGNMENT_NUMBER
AND PAAF2.ASSIGNMENT_STATUS_TYPE_ID = PAAF.ASSIGNMENT_STATUS_TYPE_ID
AND NVL(PAAF2.JOB_ID
, 9.9) = NVL(PAAF.JOB_ID
, 9.9)
AND NVL(PAAF2.POSITION_ID
, 9.9) = NVL(PAAF.POSITION_ID
, 9.9)
AND NVL(PAAF2.GRADE_ID
, 9.9) = NVL(PAAF.GRADE_ID
, 9.9)
AND NVL(PAAF2.PAYROLL_ID
, 9.9) = NVL(PAAF.PAYROLL_ID
, 9.9)
AND NVL(PAAF2.LOCATION_ID
, 9.9) = NVL(PAAF.LOCATION_ID
, 9.9)
AND NVL(PAAF2.PAY_BASIS_ID
, 9.9) = NVL(PAAF.PAY_BASIS_ID
, 9.9)
AND NVL(PAAF2.SOFT_CODING_KEYFLEX_ID
, 9.9) = NVL(PAAF.SOFT_CODING_KEYFLEX_ID
, 9.9)
AND NVL(PAAF2.EMPLOYMENT_CATEGORY
, 'XXX') = NVL(PAAF.EMPLOYMENT_CATEGORY
, 'XXX')
AND NVL(PAAF2.PEOPLE_GROUP_ID
, 9.9) = NVL(PAAF.PEOPLE_GROUP_ID
, 9.9)
AND NVL(PAAF2.CHANGE_REASON
, '9.9') = NVL(PAAF.CHANGE_REASON
, '9.9')
AND NVL(PAAF2.ASS_ATTRIBUTE1
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE1
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE2
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE2
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE3
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE3
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE4
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE4
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE5
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE5
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE6
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE6
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE7
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE7
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE8
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE8
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE9
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE9
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE10
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE10
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE11
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE11
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE12
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE12
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE13
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE13
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE14
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE14
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE15
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE15
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE16
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE16
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE17
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE17
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE18
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE18
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE19
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE19
, 'XXX')
AND NVL(PAAF2.ASS_ATTRIBUTE20
, 'XXX') = NVL(PAAF.ASS_ATTRIBUTE20
, 'XXX')
AND PAAF2.EFFECTIVE_START_DATE = ( SELECT MAX(PAAF3.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F PAAF3
WHERE PAAF3.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF3.ASSIGNMENT_TYPE = 'E'
AND PAAF3.EFFECTIVE_START_DATE < PAAF.EFFECTIVE_START_DATE) )
AND PAAF.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PAAF.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND PAAF.PAYROLL_ID = PAY.PAYROLL_ID (+)
AND PAAF.EFFECTIVE_START_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE
, PAAF.EFFECTIVE_START_DATE)
AND NVL(PAY.EFFECTIVE_END_DATE
, PAAF.EFFECTIVE_END_DATE)
AND PAAF.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PAAF.LOCATION_ID = LOC.LOCATION_ID (+)
AND PAAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND PAAF.BUSINESS_GROUP_ID + 0 = ASTA.BUSINESS_GROUP_ID(+) + 0
AND PAAF.CHANGE_REASON = LU.LOOKUP_CODE (+)
AND LU.LOOKUP_TYPE (+) = 'EMP_ASSIGN_REASON'
AND PAAF.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'))
AND PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID (+)
AND HSCK.SEGMENT2 = LU3.LOOKUP_CODE(+)
AND LU3.LOOKUP_TYPE(+) = 'KR_JOB_TITLE'
AND HSCK.SEGMENT3 = LU4.LOOKUP_CODE(+)
AND LU4.LOOKUP_TYPE(+) = 'KR_SENIORITY'
AND HSCK.SEGMENT4 = TO_CHAR(PKG.GRADE_ID (+))
AND HSCK.SEGMENT5 = TO_CHAR(PKGP.GRADE_POINT_ID (+))

Columns