View: PER_ASSIGNMENTS_V3

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

View Text

SELECT A.ROWID ROW_ID
, A.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, A.PERSON_ID
, A.PERIOD_OF_SERVICE_ID
, A.ASSIGNMENT_NUMBER
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.ORGANIZATION_ID
, A.JOB_ID
, A.POSITION_ID
, A.GRADE_ID
, A.PEOPLE_GROUP_ID
, A.PAYROLL_ID
, A.ASSIGNMENT_ID
, A.LOCATION_ID
, A.ASSIGNMENT_STATUS_TYPE_ID
, A.CHANGE_REASON
, A.ASSIGNMENT_SEQUENCE
, A.ASS_ATTRIBUTE_CATEGORY
, A.ASS_ATTRIBUTE1
, A.ASS_ATTRIBUTE2
, A.ASS_ATTRIBUTE3
, A.ASS_ATTRIBUTE4
, A.ASS_ATTRIBUTE5
, A.ASS_ATTRIBUTE6
, A.ASS_ATTRIBUTE7
, A.ASS_ATTRIBUTE8
, A.ASS_ATTRIBUTE9
, A.ASS_ATTRIBUTE10
, A.ASS_ATTRIBUTE11
, A.ASS_ATTRIBUTE12
, A.ASS_ATTRIBUTE13
, A.ASS_ATTRIBUTE14
, A.ASS_ATTRIBUTE15
, A.ASS_ATTRIBUTE16
, A.ASS_ATTRIBUTE17
, A.ASS_ATTRIBUTE18
, A.ASS_ATTRIBUTE19
, A.ASS_ATTRIBUTE20
, A.ASS_ATTRIBUTE21
, A.ASS_ATTRIBUTE22
, A.ASS_ATTRIBUTE23
, A.ASS_ATTRIBUTE24
, A.ASS_ATTRIBUTE25
, A.ASS_ATTRIBUTE26
, A.ASS_ATTRIBUTE27
, A.ASS_ATTRIBUTE28
, A.ASS_ATTRIBUTE29
, A.ASS_ATTRIBUTE30
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, A.ASSIGNMENT_TYPE
, HR_GENERAL.DECODE_PAY_BASIS (A.PAY_BASIS_ID) PAY_BASIS
, PP.FIRST_NAME
, PP.LAST_NAME
, PP.FULL_NAME
, PP.APPLICANT_NUMBER
, PP.EMAIL_ADDRESS
, PP.KNOWN_AS
, HR_GENERAL.DECODE_LOOKUP('MAR_STATUS'
, PP.MARITAL_STATUS) MARITAL_STATUS
, PP.MIDDLE_NAMES
, HR_GENERAL.DECODE_LOOKUP('NATIONALITY'
, PP.NATIONALITY) NATIONALITY
, PP.NATIONAL_IDENTIFIER
, PP.PREVIOUS_LAST_NAME
, PP.REGISTERED_DISABLED_FLAG
, HR_GENERAL.DECODE_LOOKUP('SEX'
, PP.SEX) SEX
, HR_GENERAL.GET_WORK_PHONE (PP.PERSON_ID) WORK_TELEPHONE
, PP.CURRENT_EMPLOYEE_FLAG CUR_EMP_FLAG /*
, --HR_GENERAL.DECODE_LOOKUP('EMP_OR_APL'
, PP.CURRENT_EMP_OR_APL_FLAG) -- CUR_EMP_OR_APL_FLAG */
, PP.CURRENT_APPLICANT_FLAG CUR_APL_FLAG
, HR_GENERAL.DECODE_LOOKUP('EMP_CAT'
, A.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PP.TITLE) TITLE
, ORGTL.NAME ORGANIZATION
, HR_GENERAL.DECODE_GRADE (A.GRADE_ID) GRADE
, HR_GENERAL.DECODE_PAYROLL (A.PAYROLL_ID) PAYROLL
, HR_GENERAL.DECODE_JOB (A.JOB_ID) JOB
, HR_GENERAL.DECODE_POSITION (A.POSITION_ID) POSITION
, HR_GENERAL.DECODE_LOCATION (A.LOCATION_ID) LOCATION_CODE
, NVL(HR_GENERAL.DECODE_ASS_STATUS_TYPE (A.ASSIGNMENT_STATUS_TYPE_ID
, A.BUSINESS_GROUP_ID)
, ASTTL.USER_STATUS) STATUS
, AST.PER_SYSTEM_STATUS
, HR_GENERAL.DECODE_LOOKUP(DECODE(A.ASSIGNMENT_TYPE
, 'A'
, 'APL_ASSIGN_REASON'
, 'EMP_ASSIGN_REASON')
, A.CHANGE_REASON) REASON
, PT.SYSTEM_PERSON_TYPE PERSON_TYPE /*
, PTTL.USER_PERSON_TYPE USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(F.EFFECTIVE_DATE
, PP.PERSON_ID) USER_PERSON_TYPE
, PT.PERSON_TYPE_ID
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, A.PROBATION_PERIOD
, HR_GENERAL.DECODE_LOOKUP('JP_SCHOOL_TYPE'
, AC.SEGMENT1) SCHOOL_TYPE
, AC.SEGMENT2 SCHOOL_CODE
, AC.SEGMENT3 SCHOOL_NAME
, A.PROBATION_UNIT
, AC.SEGMENT4 SCHOOL_NAME_KANA
, A.NOTICE_PERIOD
, AC.SEGMENT5 MAJOR
, A.NOTICE_PERIOD_UOM
, AC.SEGMENT6 MAJOR_KANA
, AC.SEGMENT7 ADVISOR
, AC.SEGMENT8 DATE_OF_GRADUATION
, A.EMPLOYEE_CATEGORY
, AC.SEGMENT9 NOTES
, AC.SEGMENT10 LAST_FLAG
, POS.DATE_START HIRE_DATE
, PP.NPW_NUMBER
, PP.CURRENT_NPW_FLAG
, A.PERIOD_OF_PLACEMENT_DATE_START
, A.VENDOR_ID
, A.VENDOR_EMPLOYEE_NUMBER
, A.VENDOR_ASSIGNMENT_NUMBER
, POV.VENDOR_NAME
, A.ASSIGNMENT_CATEGORY
, A.PROJECT_TITLE
FROM HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, FND_SESSIONS F
, PER_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
, PER_PERSON_TYPES_TL PTTL
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE POS
, PER_PERSON_ANALYSES PA
, PER_ANALYSIS_CRITERIA AC
, PO_VENDORS POV
, FND_SESSIONS FND
, FND_ID_FLEX_STRUCTURES B
WHERE ((A.ASSIGNMENT_TYPE = 'E'
AND A.EFFECTIVE_END_DATE < F.EFFECTIVE_DATE
AND A.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A.ASSIGNMENT_ID = A2.ASSIGNMENT_ID)) OR (A.ASSIGNMENT_TYPE IN ('A'
, 'E')
AND F.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE))
AND A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID + 0 = PT.PERSON_TYPE_ID
AND F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND A.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND POS.PERIOD_OF_SERVICE_ID (+) = A.PERIOD_OF_SERVICE_ID
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID
AND ORGTL.LANGUAGE = USERENV('LANG')
AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID
AND PTTL.LANGUAGE = USERENV ('LANG')
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTTL.LANGUAGE = USERENV ('LANG')
AND PA.PERSON_ID (+) = A.PERSON_ID
AND AC.ANALYSIS_CRITERIA_ID (+) = PA.ANALYSIS_CRITERIA_ID
AND PA.ID_FLEX_NUM (+) = 1
AND B.ID_FLEX_NUM (+) = PA.ID_FLEX_NUM
AND B.ID_FLEX_CODE (+) = 'PEA'
AND B.APPLICATION_ID (+) = 800
AND B.ID_FLEX_NUM (+) = 1
AND FND.SESSION_ID = USERENV ('SESSIONID')
AND (PA.DATE_FROM IS NULL OR (FND.EFFECTIVE_DATE >= PA.DATE_FROM
AND (PA.DATE_TO IS NULL OR FND.EFFECTIVE_DATE <= PA.DATE_TO) ) )
AND A.VENDOR_ID = POV.VENDOR_ID (+)

Columns