eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PER_KR_ASSIGNMENTS_V

Source

View Text - Preformatted


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 (+))

View Text - HTML Formatted

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 (+))