View: PER_ASSIGNMENTS_V

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

View Text

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
, NVL(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 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(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

Columns