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_ASSIGNMENTS_V5

Source

View Text - Preformatted


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 , PA.PAYROLL_ID , PA.PAY_BASIS_ID FROM PER_ASSIGNMENTS_F2 PA , PER_ALL_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)) OR (PA2.EFFECTIVE_START_DATE IS NOT NULL AND PA2.EFFECTIVE_END_DATE IS NOT NULL AND (FND.EFFECTIVE_DATE < PA2.EFFECTIVE_START_DATE OR FND.EFFECTIVE_DATE > PA2.EFFECTIVE_END_DATE) AND PA2.EFFECTIVE_END_DATE = (SELECT MAX(PA3.EFFECTIVE_END_DATE) FROM PER_ALL_ASSIGNMENTS_F PA3 WHERE PA3.ASSIGNMENT_ID = PA.SUPERVISOR_ASSIGNMENT_ID))) 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

View Text - HTML Formatted

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
, PA.PAYROLL_ID
, PA.PAY_BASIS_ID
FROM PER_ASSIGNMENTS_F2 PA
, PER_ALL_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)) OR (PA2.EFFECTIVE_START_DATE IS NOT NULL
AND PA2.EFFECTIVE_END_DATE IS NOT NULL
AND (FND.EFFECTIVE_DATE < PA2.EFFECTIVE_START_DATE OR FND.EFFECTIVE_DATE > PA2.EFFECTIVE_END_DATE)
AND PA2.EFFECTIVE_END_DATE = (SELECT MAX(PA3.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F PA3
WHERE PA3.ASSIGNMENT_ID = PA.SUPERVISOR_ASSIGNMENT_ID)))
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