VIEW: APPS.PER_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT PA.ASSIGNMENT_ID , PA.ROWID ROW_ID , PA.EFFECTIVE_START_DATE , DECODE(PA.EFFECTIVE_END_DATE , TO_DATE('4712/12/31', 'YYYY/MM/DD'), to_date(NULL), PA.EFFECTIVE_END_DATE) D_EFFECTIVE_END_DATE , PA.EFFECTIVE_END_DATE , PA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID , PA.GRADE_ID , GDT.NAME GRADE_NAME , PA.POSITION_ID , HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) POSITION_NAME , PA.JOB_ID , JBT.NAME JOB_NAME , PA.ASSIGNMENT_STATUS_TYPE_ID , NVL(AMDTL.USER_STATUS, STTL.USER_STATUS) USER_STATUS , NVL(AMD.PER_SYSTEM_STATUS, ST.PER_SYSTEM_STATUS) PER_SYSTEM_STATUS , PA.PAYROLL_ID , PAY.PAYROLL_NAME , PA.LOCATION_ID , LOCTL.LOCATION_CODE , PA.SUPERVISOR_ID , SUP.FULL_NAME SUPERVISOR_NAME , decode(sup.current_employee_flag ,'Y' ,sup.employee_number ,sup.npw_number) supervisor_employee_number , PA.SPECIAL_CEILING_STEP_ID , PSP.SPINAL_POINT , PSPS.SEQUENCE SPINAL_POINT_STEP_SEQUENCE , PA.PERSON_ID , PA.ORGANIZATION_ID , OTL.NAME ORGANIZATION_NAME , PA.PEOPLE_GROUP_ID , PA.ASSIGNMENT_SEQUENCE , PA.PRIMARY_FLAG , PA.ASSIGNMENT_NUMBER , PA.CHANGE_REASON , DECODE(PA.ASSIGNMENT_TYPE ,'E', HR_GENERAL.DECODE_LOOKUP('EMP_ASSIGN_REASON', PA.CHANGE_REASON) ,'C', HR_GENERAL.DECODE_LOOKUP('CWK_ASSIGN_REASON', PA.CHANGE_REASON)) CHANGE_REASON_MEANING , PA.COMMENT_ID , COM.COMMENT_TEXT , PA.DATE_PROBATION_END , PA.DATE_PROBATION_END D_DATE_PROBATION_END , PA.FREQUENCY , HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.FREQUENCY) FREQUENCY_MEANING , PA.INTERNAL_ADDRESS_LINE , PA.MANAGER_FLAG , PA.NORMAL_HOURS , PA.PROBATION_PERIOD , PA.PROBATION_UNIT , HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS', PA.PROBATION_UNIT) PROBATION_UNIT_MEANING , PA.TIME_NORMAL_FINISH , PA.TIME_NORMAL_START , PA.BARGAINING_UNIT_CODE , HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE', PA.BARGAINING_UNIT_CODE) BARGAINING_UNIT_CODE_MEANING , PA.LABOUR_UNION_MEMBER_FLAG , PA.HOURLY_SALARIED_CODE , HR_GENERAL.DECODE_LOOKUP('HOURLY_SALARIED_CODE', PA.HOURLY_SALARIED_CODE) , PA.LAST_UPDATE_DATE , PA.LAST_UPDATED_BY , PA.LAST_UPDATE_LOGIN , PA.CREATED_BY , PA.CREATION_DATE , PA.SAL_REVIEW_PERIOD , HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.SAL_REVIEW_PERIOD_FREQUENCY) SAL_REV_PERIOD_FREQ_MEANING , PA.SAL_REVIEW_PERIOD_FREQUENCY , PA.PERF_REVIEW_PERIOD , HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.PERF_REVIEW_PERIOD_FREQUENCY) PERF_REV_PERIOD_FREQ_MEANING , PA.PERF_REVIEW_PERIOD_FREQUENCY , PA.PAY_BASIS_ID , PB.NAME SALARY_BASIS , PB.PAY_BASIS PAY_BASIS , PA.RECRUITER_ID , PA.PERSON_REFERRED_BY_ID , PA.RECRUITMENT_ACTIVITY_ID , PA.SOURCE_ORGANIZATION_ID , PA.SOFT_CODING_KEYFLEX_ID , PA.VACANCY_ID , PA.ASSIGNMENT_TYPE , PA.APPLICATION_ID , PA.DEFAULT_CODE_COMB_ID , PA.PERIOD_OF_SERVICE_ID , PA.SET_OF_BOOKS_ID , GL.NAME D_SET_OF_BOOKS , GL.CHART_OF_ACCOUNTS_ID GL_KEYFLEX_STRUCTURE , 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.EMPLOYMENT_CATEGORY , DECODE(PA.ASSIGNMENT_TYPE, 'E', HR_GENERAL.DECODE_LOOKUP('EMP_CAT', PA.EMPLOYMENT_CATEGORY) ,'C', HR_GENERAL.DECODE_LOOKUP('CWK_ASG_CATEGORY', PA.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY_MEANING , PA.ESTABLISHMENT_ID , PA.COLLECTIVE_AGREEMENT_ID , PA.CONTRACT_ID , PA.CAGR_GRADE_DEF_ID , PA.CAGR_ID_FLEX_NUM , CA.NAME AGREEMENT_NAME , O1.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 , HR_GENERAL.DECODE_LOOKUP('EMPLOYEE_CATG', PA. EMPLOYEE_CATEGORY) EMPLOYEE_CATEGORY_MEANING , PA.WORK_AT_HOME , PA.JOB_POST_SOURCE_NAME , PA.TITLE , PA.PROJECT_TITLE , PA.PERIOD_OF_PLACEMENT_DATE_START , PA.VENDOR_ID , POV.VENDOR_NAME , PA.VENDOR_SITE_ID , POVS.VENDOR_SITE_CODE , PA.PO_HEADER_ID , POH.SEGMENT1 PO_NUMBER , PA.PO_LINE_ID , POL.LINE_NUM PO_LINE_NUMBER , PA.PROJECTED_ASSIGNMENT_END , PA.VENDOR_EMPLOYEE_NUMBER , PA.VENDOR_ASSIGNMENT_NUMBER , PA.ASSIGNMENT_CATEGORY , PA.GRADE_LADDER_PGM_ID , PA.SUPERVISOR_ASSIGNMENT_ID , PGM.NAME GRADE_LADDER_NAME , PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER FROM PER_ALL_ASSIGNMENTS_F PA , PER_ALL_ASSIGNMENTS_F PA2 , PER_GRADES PG , PER_JOBS J , PER_GRADES_TL GDT , 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 , PAY_ALL_PAYROLLS_F PAY , HR_LOCATIONS_ALL_TL LOCTL , HR_LOCATIONS_NO_JOIN LOC , PER_ALL_PEOPLE_F SUP , PER_SPINAL_POINT_STEPS_F PSPS , PER_SPINAL_POINTS PSP , HR_ALL_ORGANIZATION_UNITS O , HR_ALL_ORGANIZATION_UNITS_TL OTL , HR_COMMENTS COM , GL_SETS_OF_BOOKS GL , PER_PAY_BASES PB , FND_SESSIONS FND , PER_COLLECTIVE_AGREEMENTS CA , PER_CONTRACTS_F CO , HR_ALL_ORGANIZATION_UNITS O1 , BEN_PGM_F PGM , PO_VENDORS POV , PO_VENDOR_SITES_ALL POVS , PO_HEADERS_ALL POH , PO_LINES_ALL POL WHERE PA.ASSIGNMENT_TYPE IN ( 'E','C') AND PA.ORGANIZATION_ID = O.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 = JBT.JOB_ID (+) AND JBT.LANGUAGE(+) = userenv('LANG') AND PA.JOB_ID = J.JOB_ID (+) 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.PAYROLL_ID = PAY.PAYROLL_ID (+) AND PA.LOCATION_ID = LOC.LOCATION_ID (+) AND PA.SUPERVISOR_ID = SUP.PERSON_ID (+) AND PA.SPECIAL_CEILING_STEP_ID = PSPS.STEP_ID (+) AND PSPS.SPINAL_POINT_ID = PSP.SPINAL_POINT_ID (+) AND PA.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID (+) AND PA.COMMENT_ID = COM.COMMENT_ID (+) aND PA.PAY_BASIS_ID = PB.PAY_BASIS_ID (+) AND FND.SESSION_ID = USERENV('sessionid') AND FND.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE AND ( ( pa2.effective_start_date IS NULL AND pa2.effective_end_date IS NULL 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 OR fnd.effective_date BETWEEN pa2.effective_start_date AND 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(SUP.EFFECTIVE_START_DATE, FND.EFFECTIVE_DATE) AND NVL(SUP.EFFECTIVE_END_DATE, FND.EFFECTIVE_DATE) AND FND.EFFECTIVE_DATE BETWEEN NVL(PSPS.EFFECTIVE_START_DATE, FND.EFFECTIVE_DATE) AND NVL(PSPS.EFFECTIVE_END_DATE, FND.EFFECTIVE_DATE) AND FND.EFFECTIVE_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE, FND.EFFECTIVE_DATE) AND NVL(PAY.EFFECTIVE_END_DATE, FND.EFFECTIVE_DATE) AND FND.EFFECTIVE_DATE BETWEEN NVL(CO.EFFECTIVE_START_DATE, FND.EFFECTIVE_DATE) AND NVL(CO.EFFECTIVE_END_DATE, FND.EFFECTIVE_DATE) AND O.organization_id = OTL.organization_id AND OTL.language = 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 = O1.ORGANIZATION_ID (+) AND CA.COLLECTIVE_AGREEMENT_ID (+) = PA.COLLECTIVE_AGREEMENT_ID AND CO.CONTRACT_ID (+) = PA.CONTRACT_ID AND PA.GRADE_LADDER_PGM_ID = PGM.PGM_ID (+) AND PA.VENDOR_ID = POV.VENDOR_ID (+) AND PA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+) AND PA.PO_HEADER_ID = POH.PO_HEADER_ID (+) AND PA.PO_LINE_ID = POL.PO_LINE_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('4712/12/31'
, 'YYYY/MM/DD')
, TO_DATE(NULL)
, PA.EFFECTIVE_END_DATE) D_EFFECTIVE_END_DATE
, PA.EFFECTIVE_END_DATE
, PA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PA.GRADE_ID
, GDT.NAME GRADE_NAME
, PA.POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) POSITION_NAME
, PA.JOB_ID
, JBT.NAME JOB_NAME
, PA.ASSIGNMENT_STATUS_TYPE_ID
, NVL(AMDTL.USER_STATUS
, STTL.USER_STATUS) USER_STATUS
, NVL(AMD.PER_SYSTEM_STATUS
, ST.PER_SYSTEM_STATUS) PER_SYSTEM_STATUS
, PA.PAYROLL_ID
, PAY.PAYROLL_NAME
, PA.LOCATION_ID
, LOCTL.LOCATION_CODE
, PA.SUPERVISOR_ID
, SUP.FULL_NAME SUPERVISOR_NAME
, DECODE(SUP.CURRENT_EMPLOYEE_FLAG
, 'Y'
, SUP.EMPLOYEE_NUMBER
, SUP.NPW_NUMBER) SUPERVISOR_EMPLOYEE_NUMBER
, PA.SPECIAL_CEILING_STEP_ID
, PSP.SPINAL_POINT
, PSPS.SEQUENCE SPINAL_POINT_STEP_SEQUENCE
, PA.PERSON_ID
, PA.ORGANIZATION_ID
, OTL.NAME ORGANIZATION_NAME
, PA.PEOPLE_GROUP_ID
, PA.ASSIGNMENT_SEQUENCE
, PA.PRIMARY_FLAG
, PA.ASSIGNMENT_NUMBER
, PA.CHANGE_REASON
, DECODE(PA.ASSIGNMENT_TYPE
, 'E'
, HR_GENERAL.DECODE_LOOKUP('EMP_ASSIGN_REASON'
, PA.CHANGE_REASON)
, 'C'
, HR_GENERAL.DECODE_LOOKUP('CWK_ASSIGN_REASON'
, PA.CHANGE_REASON)) CHANGE_REASON_MEANING
, PA.COMMENT_ID
, COM.COMMENT_TEXT
, PA.DATE_PROBATION_END
, PA.DATE_PROBATION_END D_DATE_PROBATION_END
, PA.FREQUENCY
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PA.FREQUENCY) FREQUENCY_MEANING
, PA.INTERNAL_ADDRESS_LINE
, PA.MANAGER_FLAG
, PA.NORMAL_HOURS
, PA.PROBATION_PERIOD
, PA.PROBATION_UNIT
, HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, PA.PROBATION_UNIT) PROBATION_UNIT_MEANING
, PA.TIME_NORMAL_FINISH
, PA.TIME_NORMAL_START
, PA.BARGAINING_UNIT_CODE
, HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE'
, PA.BARGAINING_UNIT_CODE) BARGAINING_UNIT_CODE_MEANING
, PA.LABOUR_UNION_MEMBER_FLAG
, PA.HOURLY_SALARIED_CODE
, HR_GENERAL.DECODE_LOOKUP('HOURLY_SALARIED_CODE'
, PA.HOURLY_SALARIED_CODE)
, PA.LAST_UPDATE_DATE
, PA.LAST_UPDATED_BY
, PA.LAST_UPDATE_LOGIN
, PA.CREATED_BY
, PA.CREATION_DATE
, PA.SAL_REVIEW_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PA.SAL_REVIEW_PERIOD_FREQUENCY) SAL_REV_PERIOD_FREQ_MEANING
, PA.SAL_REVIEW_PERIOD_FREQUENCY
, PA.PERF_REVIEW_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PA.PERF_REVIEW_PERIOD_FREQUENCY) PERF_REV_PERIOD_FREQ_MEANING
, PA.PERF_REVIEW_PERIOD_FREQUENCY
, PA.PAY_BASIS_ID
, PB.NAME SALARY_BASIS
, PB.PAY_BASIS PAY_BASIS
, PA.RECRUITER_ID
, PA.PERSON_REFERRED_BY_ID
, PA.RECRUITMENT_ACTIVITY_ID
, PA.SOURCE_ORGANIZATION_ID
, PA.SOFT_CODING_KEYFLEX_ID
, PA.VACANCY_ID
, PA.ASSIGNMENT_TYPE
, PA.APPLICATION_ID
, PA.DEFAULT_CODE_COMB_ID
, PA.PERIOD_OF_SERVICE_ID
, PA.SET_OF_BOOKS_ID
, GL.NAME D_SET_OF_BOOKS
, GL.CHART_OF_ACCOUNTS_ID GL_KEYFLEX_STRUCTURE
, 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.EMPLOYMENT_CATEGORY
, DECODE(PA.ASSIGNMENT_TYPE
, 'E'
, HR_GENERAL.DECODE_LOOKUP('EMP_CAT'
, PA.EMPLOYMENT_CATEGORY)
, 'C'
, HR_GENERAL.DECODE_LOOKUP('CWK_ASG_CATEGORY'
, PA.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY_MEANING
, PA.ESTABLISHMENT_ID
, PA.COLLECTIVE_AGREEMENT_ID
, PA.CONTRACT_ID
, PA.CAGR_GRADE_DEF_ID
, PA.CAGR_ID_FLEX_NUM
, CA.NAME AGREEMENT_NAME
, O1.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
, HR_GENERAL.DECODE_LOOKUP('EMPLOYEE_CATG'
, PA. EMPLOYEE_CATEGORY) EMPLOYEE_CATEGORY_MEANING
, PA.WORK_AT_HOME
, PA.JOB_POST_SOURCE_NAME
, PA.TITLE
, PA.PROJECT_TITLE
, PA.PERIOD_OF_PLACEMENT_DATE_START
, PA.VENDOR_ID
, POV.VENDOR_NAME
, PA.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, PA.PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, PA.PO_LINE_ID
, POL.LINE_NUM PO_LINE_NUMBER
, PA.PROJECTED_ASSIGNMENT_END
, PA.VENDOR_EMPLOYEE_NUMBER
, PA.VENDOR_ASSIGNMENT_NUMBER
, PA.ASSIGNMENT_CATEGORY
, PA.GRADE_LADDER_PGM_ID
, PA.SUPERVISOR_ASSIGNMENT_ID
, PGM.NAME GRADE_LADDER_NAME
, PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
FROM PER_ALL_ASSIGNMENTS_F PA
, PER_ALL_ASSIGNMENTS_F PA2
, PER_GRADES PG
, PER_JOBS J
, PER_GRADES_TL GDT
, 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
, PAY_ALL_PAYROLLS_F PAY
, HR_LOCATIONS_ALL_TL LOCTL
, HR_LOCATIONS_NO_JOIN LOC
, PER_ALL_PEOPLE_F SUP
, PER_SPINAL_POINT_STEPS_F PSPS
, PER_SPINAL_POINTS PSP
, HR_ALL_ORGANIZATION_UNITS O
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, HR_COMMENTS COM
, GL_SETS_OF_BOOKS GL
, PER_PAY_BASES PB
, FND_SESSIONS FND
, PER_COLLECTIVE_AGREEMENTS CA
, PER_CONTRACTS_F CO
, HR_ALL_ORGANIZATION_UNITS O1
, BEN_PGM_F PGM
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
WHERE PA.ASSIGNMENT_TYPE IN ( 'E'
, 'C')
AND PA.ORGANIZATION_ID = O.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 = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PA.JOB_ID = J.JOB_ID (+)
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.PAYROLL_ID = PAY.PAYROLL_ID (+)
AND PA.LOCATION_ID = LOC.LOCATION_ID (+)
AND PA.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND PA.SPECIAL_CEILING_STEP_ID = PSPS.STEP_ID (+)
AND PSPS.SPINAL_POINT_ID = PSP.SPINAL_POINT_ID (+)
AND PA.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID (+)
AND PA.COMMENT_ID = COM.COMMENT_ID (+)
AND PA.PAY_BASIS_ID = PB.PAY_BASIS_ID (+)
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND ( ( PA2.EFFECTIVE_START_DATE IS NULL
AND PA2.EFFECTIVE_END_DATE IS NULL
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 OR FND.EFFECTIVE_DATE BETWEEN PA2.EFFECTIVE_START_DATE
AND 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(SUP.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(SUP.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND FND.EFFECTIVE_DATE BETWEEN NVL(PSPS.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(PSPS.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND FND.EFFECTIVE_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(PAY.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND FND.EFFECTIVE_DATE BETWEEN NVL(CO.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(CO.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = 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 = O1.ORGANIZATION_ID (+)
AND CA.COLLECTIVE_AGREEMENT_ID (+) = PA.COLLECTIVE_AGREEMENT_ID
AND CO.CONTRACT_ID (+) = PA.CONTRACT_ID
AND PA.GRADE_LADDER_PGM_ID = PGM.PGM_ID (+)
AND PA.VENDOR_ID = POV.VENDOR_ID (+)
AND PA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND PA.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND PA.PO_LINE_ID = POL.PO_LINE_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