View: PER_ASSIGNMENTS_V5

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

View Text

SELECT PA.ASSIGNMENT_ID
, PA.ROWID ROW_ID
, PA.EFFECTIVE_START_DATE
, DECODE(PA.EFFECTIVE_END_DATE
, TO_DATE('31124712'
, 'DDMMYYYY')
, NULL
, PA.EFFECTIVE_END_DATE) D_EFFECTIVE_END_DATE
, PA.EFFECTIVE_END_DATE
, PA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PA.RECRUITMENT_ACTIVITY_ID
, RA.NAME D_RECRUITMENT_ACTIVITY_ID
, RA.TYPE C_REC_ACT_TYPE
, HR_GENERAL.DECODE_LOOKUP('REC_TYPE'
, PA.SOURCE_TYPE) RECRUITMENT_ACTIVITY_TYPE
, PA.GRADE_ID
, GDT.NAME D_GRADE_ID
, PA.POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) D_POSITION_ID
, PA.JOB_ID
, JBT.NAME D_JOB_ID
, PA.ASSIGNMENT_STATUS_TYPE_ID
, NVL(AMDTL.USER_STATUS
, STTL.USER_STATUS) STATUS
, NVL(AMD.PER_SYSTEM_STATUS
, ST.PER_SYSTEM_STATUS) PER_SYSTEM_STATUS
, PA.LOCATION_ID
, LOCTL.LOCATION_CODE D_LOCATION_ID
, PA.SUPERVISOR_ID
, SUP.FULL_NAME SUPERVISOR_NAME
, NVL(SUP.EMPLOYEE_NUMBER
, SUP.NPW_NUMBER) SUPERVISOR_EMPLOYEE_NUMBER
, PA.PERSON_ID
, PA.ORGANIZATION_ID
, O1TL.NAME D_ORGANIZATION_ID
, PA.PEOPLE_GROUP_ID
, PA.ASSIGNMENT_SEQUENCE
, PA.PRIMARY_FLAG
, PA.ASSIGNMENT_NUMBER
, PA.CHANGE_REASON
, HR_GENERAL.DECODE_LOOKUP('APL_ASSIGN_REASON'
, PA.CHANGE_REASON) CHANGE_REASON_MEANING
, PA.COMMENT_ID
, COM.COMMENT_TEXT
, PA.DATE_PROBATION_END
, TO_CHAR(PA.DATE_PROBATION_END
, 'DD-MON-YYYY') D_DATE_PROBATION_END
, PA.FREQUENCY
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PA.FREQUENCY) FREQUENCY_MEANING
, PA.MANAGER_FLAG
, PA.NORMAL_HOURS
, PA.PROBATION_PERIOD
, PA.PROBATION_UNIT
, HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, PA.PROBATION_UNIT) PROBATION_UNIT_MEANING
, TO_DATE(PA.TIME_NORMAL_FINISH
, 'HH24:MI') TIME_NORMAL_FINISH
, TO_DATE(PA.TIME_NORMAL_START
, 'HH24:MI') TIME_NORMAL_START
, PA.LAST_UPDATE_DATE
, PA.LAST_UPDATED_BY
, PA.LAST_UPDATE_LOGIN
, PA.CREATED_BY
, PA.CREATION_DATE
, PA.SAL_REVIEW_PERIOD
, PA.RECRUITER_ID
, REC.FULL_NAME D_RECRUITER_ID
, NVL( REC.EMPLOYEE_NUMBER
, REC.NPW_NUMBER) RECRUITER_NUMBER
, PA.PERSON_REFERRED_BY_ID
, REF.FULL_NAME D_PERSON_REFERRED_BY_ID
, NVL( REF.EMPLOYEE_NUMBER
, REF.NPW_NUMBER) D_PERSON_REFERRED_NUMBER
, PA.SOURCE_ORGANIZATION_ID
, O2TL.NAME D_SOURCE_ORGANIZATION_ID
, PA.VACANCY_ID
, VAC.NAME D_VACANCY_ID
, VAC.ORGANIZATION_ID V_ORGANIZATION_ID
, VAC.PEOPLE_GROUP_ID V_PEOPLE_GROUP_ID
, VAC.JOB_ID V_JOB_ID
, VAC.POSITION_ID V_POSITION_ID
, VAC.GRADE_ID V_GRADE_ID
, VAC.LOCATION_ID V_LOCATION_ID
, VAC.RECRUITER_ID V_RECRUITER_ID
, PA.ASSIGNMENT_TYPE
, PA.APPLICATION_ID
, PA.SOURCE_TYPE
, PA.REQUEST_ID
, PA.PROGRAM_APPLICATION_ID
, PA.PROGRAM_ID
, PA.PROGRAM_UPDATE_DATE
, PA.ASS_ATTRIBUTE_CATEGORY
, PA.ASS_ATTRIBUTE1
, PA.ASS_ATTRIBUTE2
, PA.ASS_ATTRIBUTE3
, PA.ASS_ATTRIBUTE4
, PA.ASS_ATTRIBUTE5
, PA.ASS_ATTRIBUTE6
, PA.ASS_ATTRIBUTE7
, PA.ASS_ATTRIBUTE8
, PA.ASS_ATTRIBUTE9
, PA.ASS_ATTRIBUTE10
, PA.ASS_ATTRIBUTE11
, PA.ASS_ATTRIBUTE12
, PA.ASS_ATTRIBUTE13
, PA.ASS_ATTRIBUTE14
, PA.ASS_ATTRIBUTE15
, PA.ASS_ATTRIBUTE16
, PA.ASS_ATTRIBUTE17
, PA.ASS_ATTRIBUTE18
, PA.ASS_ATTRIBUTE19
, PA.ASS_ATTRIBUTE20
, PA.ASS_ATTRIBUTE21
, PA.ASS_ATTRIBUTE22
, PA.ASS_ATTRIBUTE23
, PA.ASS_ATTRIBUTE24
, PA.ASS_ATTRIBUTE25
, PA.ASS_ATTRIBUTE26
, PA.ASS_ATTRIBUTE27
, PA.ASS_ATTRIBUTE28
, PA.ASS_ATTRIBUTE29
, PA.ASS_ATTRIBUTE30
, PA.ESTABLISHMENT_ID
, PA.COLLECTIVE_AGREEMENT_ID
, PA.CONTRACT_ID
, PA.CAGR_GRADE_DEF_ID
, PA.CAGR_ID_FLEX_NUM
, CA.NAME AGREEMENT_NAME
, O3.NAME ESTABLISHMENT_NAME
, CO.REFERENCE REFERENCE
, PA. NOTICE_PERIOD
, PA.NOTICE_PERIOD_UOM
, HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, PA.NOTICE_PERIOD_UOM) NOTICE_PERIOD_UOM_MEANING
, PA.EMPLOYEE_CATEGORY
, PA. WORK_AT_HOME
, PA. JOB_POST_SOURCE_NAME
, PA.GRADE_LADDER_PGM_ID
, PA.SUPERVISOR_ASSIGNMENT_ID
, PGM.NAME GRADE_LADDER_NAME
, PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
FROM PER_ASSIGNMENTS_F PA
, PER_ASSIGNMENTS_F PA2
, PER_RECRUITMENT_ACTIVITIES RA
, PER_GRADES PG
, PER_GRADES_TL GDT
, PER_JOBS J
, PER_JOBS_TL JBT
, PER_ASSIGNMENT_STATUS_TYPES ST
, PER_ASSIGNMENT_STATUS_TYPES_TL STTL
, PER_ASS_STATUS_TYPE_AMENDS AMD
, PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL
, HR_LOCATIONS_ALL_TL LOCTL
, HR_LOCATIONS_NO_JOIN LOC
, PER_ALL_PEOPLE_F SUP
, PER_ALL_PEOPLE_F REC
, PER_ALL_PEOPLE_F REF
, PER_ALL_VACANCIES VAC
, HR_ALL_ORGANIZATION_UNITS O1
, HR_ALL_ORGANIZATION_UNITS_TL O1TL
, HR_ALL_ORGANIZATION_UNITS O2
, HR_ALL_ORGANIZATION_UNITS_TL O2TL
, HR_ALL_ORGANIZATION_UNITS O3
, HR_COMMENTS COM
, FND_SESSIONS FND
, PER_COLLECTIVE_AGREEMENTS CA
, PER_CONTRACTS_F CO
, BEN_PGM_F PGM
WHERE PA.ASSIGNMENT_TYPE = 'A'
AND PA.RECRUITMENT_ACTIVITY_ID = RA.RECRUITMENT_ACTIVITY_ID(+)
AND PA.VACANCY_ID = VAC.VACANCY_ID(+)
AND PA.ORGANIZATION_ID = O1.ORGANIZATION_ID
AND PA.SOURCE_ORGANIZATION_ID = O2.ORGANIZATION_ID (+)
AND PA.GRADE_ID = PG.GRADE_ID (+)
AND PA.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND PA.JOB_ID = J.JOB_ID (+)
AND PA.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PA.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
AND PA.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
AND PA.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
AND PA.LOCATION_ID = LOC.LOCATION_ID (+)
AND PA.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND PA.RECRUITER_ID = REC.PERSON_ID (+)
AND PA.PERSON_REFERRED_BY_ID = REF.PERSON_ID (+)
AND PA.COMMENT_ID = COM.COMMENT_ID (+)
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN NVL(PA2.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(PA2.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND FND.EFFECTIVE_DATE BETWEEN NVL(REC.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(REC.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND FND.EFFECTIVE_DATE BETWEEN NVL(SUP.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(SUP.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND FND.EFFECTIVE_DATE BETWEEN NVL(REF.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(REF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND O1.ORGANIZATION_ID = O1TL.ORGANIZATION_ID
AND O1TL.LANGUAGE = USERENV('LANG')
AND O2.ORGANIZATION_ID = O2TL.ORGANIZATION_ID (+)
AND DECODE(O2TL.ORGANIZATION_ID
, NULL
, '1'
, O2TL.LANGUAGE) = DECODE(O2TL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
AND STTL.LANGUAGE = USERENV('LANG')
AND AMD.ASS_STATUS_TYPE_AMEND_ID = AMDTL.ASS_STATUS_TYPE_AMEND_ID (+)
AND DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, AMDTL.LANGUAGE) = DECODE(AMDTL.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 PA.ESTABLISHMENT_ID = O3.ORGANIZATION_ID (+)
AND CA.COLLECTIVE_AGREEMENT_ID (+) = PA.COLLECTIVE_AGREEMENT_ID
AND CO.CONTRACT_ID (+) = PA.CONTRACT_ID
AND FND.EFFECTIVE_DATE BETWEEN NVL(CO.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(CO.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND PA.GRADE_LADDER_PGM_ID = PGM.PGM_ID (+)
AND FND.EFFECTIVE_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND PA2.ASSIGNMENT_ID(+) = PA.SUPERVISOR_ASSIGNMENT_ID

Columns