VIEW: APPS.PER_ASSIGNMENTS_V8

Source

View Text - Preformatted

SELECT DISTINCT PAF.ROWID , ASSIGNMENT_ID , PAF.EFFECTIVE_START_DATE , PAF.EFFECTIVE_END_DATE , PAF.BUSINESS_GROUP_ID + 0 , PAF.POSITION_ID , hr_general.decode_position_latest_name(paf.position_id) , PAF.JOB_ID , JBT.NAME , ASSIGNMENT_STATUS_TYPE_ID , LOC.LOCATION_CODE , PAF.LOCATION_ID , PER.FULL_NAME , SUPERVISOR_ID , PAF.PERSON_ID , PAF.ORGANIZATION_ID , HOU.NAME , ASSIGNMENT_SEQUENCE , ASSIGNMENT_TYPE , PRIMARY_FLAG , ASSIGNMENT_NUMBER , PAF.COMMENT_ID , DEFAULT_CODE_COMB_ID , PERIOD_OF_SERVICE_ID , PAF.SET_OF_BOOKS_ID , PAF.REQUEST_ID , PAF.PROGRAM_APPLICATION_ID , PAF.PROGRAM_ID , PAF.PROGRAM_UPDATE_DATE , ASS_ATTRIBUTE_CATEGORY , ASS_ATTRIBUTE1 , ASS_ATTRIBUTE2 , ASS_ATTRIBUTE3 , ASS_ATTRIBUTE4 , ASS_ATTRIBUTE5 , ASS_ATTRIBUTE6 , ASS_ATTRIBUTE7 , ASS_ATTRIBUTE8 , ASS_ATTRIBUTE9 , ASS_ATTRIBUTE10 , ASS_ATTRIBUTE11 , ASS_ATTRIBUTE12 , ASS_ATTRIBUTE13 , ASS_ATTRIBUTE14 , ASS_ATTRIBUTE15 , ASS_ATTRIBUTE16 , ASS_ATTRIBUTE17 , ASS_ATTRIBUTE18 , ASS_ATTRIBUTE19 , ASS_ATTRIBUTE20 , ASS_ATTRIBUTE21 , ASS_ATTRIBUTE22 , ASS_ATTRIBUTE23 , ASS_ATTRIBUTE24 , ASS_ATTRIBUTE25 , ASS_ATTRIBUTE26 , ASS_ATTRIBUTE27 , ASS_ATTRIBUTE28 , ASS_ATTRIBUTE29 , ASS_ATTRIBUTE30 , RECRUITER_ID , GRADE_ID , PAYROLL_ID , SPECIAL_CEILING_STEP_ID , RECRUITMENT_ACTIVITY_ID , SOURCE_ORGANIZATION_ID , PEOPLE_GROUP_ID , PAF.SOFT_CODING_KEYFLEX_ID , VACANCY_ID , PAY_BASIS_ID , APPLICATION_ID , PERSON_REFERRED_BY_ID , CHANGE_REASON , DATE_PROBATION_END , EMPLOYMENT_CATEGORY , PAF.FREQUENCY , PAF.INTERNAL_ADDRESS_LINE , MANAGER_FLAG , NORMAL_HOURS , PERF_REVIEW_PERIOD , PERF_REVIEW_PERIOD_FREQUENCY , PAF.PROBATION_PERIOD , PAF.PROBATION_UNIT , SAL_REVIEW_PERIOD , SAL_REVIEW_PERIOD_FREQUENCY , SOURCE_TYPE , PAF.TIME_NORMAL_FINISH , PAF.TIME_NORMAL_START , PAF.LAST_UPDATE_DATE , PAF.LAST_UPDATED_BY , PAF.LAST_UPDATE_LOGIN , PAF.CREATED_BY , PAF.CREATION_DATE , PAF.TITLE FROM PER_JOBS_TL JBT , HR_LOCATIONS LOC , PER_PEOPLE_F PER , HR_ORGANIZATION_UNITS HOU , PER_ASSIGNMENTS_F PAF WHERE PAF.JOB_ID = JBT.JOB_ID (+) AND JBT.LANGUAGE(+) = userenv('LANG') AND PAF.LOCATION_ID = LOC.LOCATION_ID (+) AND PAF.SUPERVISOR_ID = PER.PERSON_ID (+) AND greatest(nvl(PER.effective_start_date,to_date('01-01-0001', 'DD-MM-YYYY')),trunc(sysdate)) between nvl(PER.effective_start_date,to_date('01-01-0001', 'DD-MM-YYYY')) and nvl(PER.effective_end_date,to_date('31-12-4712', 'DD-MM-YYYY')) AND PAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID

View Text - HTML Formatted

SELECT DISTINCT PAF.ROWID
, ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, PAF.BUSINESS_GROUP_ID + 0
, PAF.POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAF.POSITION_ID)
, PAF.JOB_ID
, JBT.NAME
, ASSIGNMENT_STATUS_TYPE_ID
, LOC.LOCATION_CODE
, PAF.LOCATION_ID
, PER.FULL_NAME
, SUPERVISOR_ID
, PAF.PERSON_ID
, PAF.ORGANIZATION_ID
, HOU.NAME
, ASSIGNMENT_SEQUENCE
, ASSIGNMENT_TYPE
, PRIMARY_FLAG
, ASSIGNMENT_NUMBER
, PAF.COMMENT_ID
, DEFAULT_CODE_COMB_ID
, PERIOD_OF_SERVICE_ID
, PAF.SET_OF_BOOKS_ID
, PAF.REQUEST_ID
, PAF.PROGRAM_APPLICATION_ID
, PAF.PROGRAM_ID
, PAF.PROGRAM_UPDATE_DATE
, ASS_ATTRIBUTE_CATEGORY
, ASS_ATTRIBUTE1
, ASS_ATTRIBUTE2
, ASS_ATTRIBUTE3
, ASS_ATTRIBUTE4
, ASS_ATTRIBUTE5
, ASS_ATTRIBUTE6
, ASS_ATTRIBUTE7
, ASS_ATTRIBUTE8
, ASS_ATTRIBUTE9
, ASS_ATTRIBUTE10
, ASS_ATTRIBUTE11
, ASS_ATTRIBUTE12
, ASS_ATTRIBUTE13
, ASS_ATTRIBUTE14
, ASS_ATTRIBUTE15
, ASS_ATTRIBUTE16
, ASS_ATTRIBUTE17
, ASS_ATTRIBUTE18
, ASS_ATTRIBUTE19
, ASS_ATTRIBUTE20
, ASS_ATTRIBUTE21
, ASS_ATTRIBUTE22
, ASS_ATTRIBUTE23
, ASS_ATTRIBUTE24
, ASS_ATTRIBUTE25
, ASS_ATTRIBUTE26
, ASS_ATTRIBUTE27
, ASS_ATTRIBUTE28
, ASS_ATTRIBUTE29
, ASS_ATTRIBUTE30
, RECRUITER_ID
, GRADE_ID
, PAYROLL_ID
, SPECIAL_CEILING_STEP_ID
, RECRUITMENT_ACTIVITY_ID
, SOURCE_ORGANIZATION_ID
, PEOPLE_GROUP_ID
, PAF.SOFT_CODING_KEYFLEX_ID
, VACANCY_ID
, PAY_BASIS_ID
, APPLICATION_ID
, PERSON_REFERRED_BY_ID
, CHANGE_REASON
, DATE_PROBATION_END
, EMPLOYMENT_CATEGORY
, PAF.FREQUENCY
, PAF.INTERNAL_ADDRESS_LINE
, MANAGER_FLAG
, NORMAL_HOURS
, PERF_REVIEW_PERIOD
, PERF_REVIEW_PERIOD_FREQUENCY
, PAF.PROBATION_PERIOD
, PAF.PROBATION_UNIT
, SAL_REVIEW_PERIOD
, SAL_REVIEW_PERIOD_FREQUENCY
, SOURCE_TYPE
, PAF.TIME_NORMAL_FINISH
, PAF.TIME_NORMAL_START
, PAF.LAST_UPDATE_DATE
, PAF.LAST_UPDATED_BY
, PAF.LAST_UPDATE_LOGIN
, PAF.CREATED_BY
, PAF.CREATION_DATE
, PAF.TITLE
FROM PER_JOBS_TL JBT
, HR_LOCATIONS LOC
, PER_PEOPLE_F PER
, HR_ORGANIZATION_UNITS HOU
, PER_ASSIGNMENTS_F PAF
WHERE PAF.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PAF.LOCATION_ID = LOC.LOCATION_ID (+)
AND PAF.SUPERVISOR_ID = PER.PERSON_ID (+)
AND GREATEST(NVL(PER.EFFECTIVE_START_DATE
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
, TRUNC(SYSDATE)) BETWEEN NVL(PER.EFFECTIVE_START_DATE
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(PER.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID