View: PER_PEOPLE_V3

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

View Text

SELECT PEO.ROWID ROW_ID
, PEO.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PEO.PERSON_TYPE_ID
, PPT.SYSTEM_PERSON_TYPE
, PEO.PERSON_ID
, PEO.EFFECTIVE_START_DATE
, PEO.EFFECTIVE_END_DATE
, PEO.LAST_NAME
, PEO.START_DATE
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(PEO.START_DATE
, PEO.PERSON_ID)
, PEO.FIRST_NAME
, PEO.TITLE
, LO1.MEANING D_TITLE
, PEO.SEX
, LO2.MEANING D_SEX
, HR_GENERAL.GET_WORK_PHONE(PEO.PERSON_ID) WORK_TELEPHONE
, PEO.APPLICANT_NUMBER
, PEO.COMMENT_ID
, PEO.CURRENT_APPLICANT_FLAG
, PEO.CURRENT_EMP_OR_APL_FLAG
, PEO.CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_BIRTH
, PEO.EMAIL_ADDRESS
, NVL(PEO.EMPLOYEE_NUMBER
, PEO.NPW_NUMBER)
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, PEO.FULL_NAME
, PEO.ORDER_NAME
, PEO.KNOWN_AS
, PEO.MARITAL_STATUS
, PEO.MIDDLE_NAMES
, PEO.NATIONALITY
, PEO.NATIONAL_IDENTIFIER
, PEO.PREVIOUS_LAST_NAME
, PEO.REGISTERED_DISABLED_FLAG
, PEO.VENDOR_ID
, A.ASSIGNMENT_STATUS_TYPE_ID
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS) STATUS
, NVL(ASTA.PER_SYSTEM_STATUS
, AST.PER_SYSTEM_STATUS) SYSTEM_STATUS
, PEO.REQUEST_ID
, PEO.PROGRAM_APPLICATION_ID
, PEO.PROGRAM_ID
, PEO.PROGRAM_UPDATE_DATE
, PEO.ATTRIBUTE_CATEGORY
, PEO.ATTRIBUTE1
, PEO.ATTRIBUTE2
, PEO.ATTRIBUTE3
, PEO.ATTRIBUTE4
, PEO.ATTRIBUTE5
, PEO.ATTRIBUTE6
, PEO.ATTRIBUTE7
, PEO.ATTRIBUTE8
, PEO.ATTRIBUTE9
, PEO.ATTRIBUTE10
, PEO.ATTRIBUTE11
, PEO.ATTRIBUTE12
, PEO.ATTRIBUTE13
, PEO.ATTRIBUTE14
, PEO.ATTRIBUTE15
, PEO.ATTRIBUTE16
, PEO.ATTRIBUTE17
, PEO.ATTRIBUTE18
, PEO.ATTRIBUTE19
, PEO.ATTRIBUTE20
, PEO.ATTRIBUTE21
, PEO.ATTRIBUTE22
, PEO.ATTRIBUTE23
, PEO.ATTRIBUTE24
, PEO.ATTRIBUTE25
, PEO.ATTRIBUTE26
, PEO.ATTRIBUTE27
, PEO.ATTRIBUTE28
, PEO.ATTRIBUTE29
, PEO.ATTRIBUTE30
, PEO.LAST_UPDATE_DATE
, PEO.LAST_UPDATED_BY
, PEO.LAST_UPDATE_LOGIN
, PEO.CREATED_BY
, PEO.CREATION_DATE
, PEO.PER_INFORMATION_CATEGORY
, PEO.PER_INFORMATION1
, PEO.PER_INFORMATION2
, PEO.PER_INFORMATION3
, PEO.PER_INFORMATION4
, PEO.PER_INFORMATION5
, PEO.PER_INFORMATION6
, PEO.PER_INFORMATION7
, PEO.PER_INFORMATION8
, PEO.PER_INFORMATION9
, PEO.PER_INFORMATION10
, PEO.PER_INFORMATION11
, PEO.PER_INFORMATION12
, PEO.PER_INFORMATION13
, PEO.PER_INFORMATION14
, PEO.PER_INFORMATION15
, PEO.PER_INFORMATION16
, PEO.PER_INFORMATION17
, PEO.PER_INFORMATION18
, PEO.PER_INFORMATION19
, PEO.PER_INFORMATION20
, PEO.PER_INFORMATION21
, PEO.PER_INFORMATION22
, PEO.PER_INFORMATION23
, PEO.PER_INFORMATION24
, PEO.PER_INFORMATION25
, PEO.PER_INFORMATION26
, PEO.PER_INFORMATION27
, PEO.PER_INFORMATION28
, PEO.PER_INFORMATION29
, PEO.PER_INFORMATION30
, PEO.WORK_SCHEDULE
, PEO.CORRESPONDENCE_LANGUAGE
, PEO.STUDENT_STATUS
, PEO.FTE_CAPACITY
, PEO.ON_MILITARY_SERVICE
, PEO.SECOND_PASSPORT_EXISTS
, PEO.BACKGROUND_CHECK_STATUS
, PEO.BACKGROUND_DATE_CHECK
, PEO.BLOOD_TYPE
, PEO.LAST_MEDICAL_TEST_DATE
, PEO.LAST_MEDICAL_TEST_BY
, PEO.REHIRE_RECOMMENDATION
, PEO.REHIRE_AUTHORIZOR
, PEO.REHIRE_REASON
, PEO.RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED
, PEO.OFFICE_NUMBER
, PEO.INTERNAL_LOCATION
, PEO.MAILSTOP
, PEO.PROJECTED_START_DATE
, PEO.HONORS
, PEO.PRE_NAME_ADJUNCT
, PEO.HOLD_APPLICANT_DATE_UNTIL
, PEO.ORIGINAL_DATE_OF_HIRE
, /* ADDRESS INFORMATION */ ADDR.STYLE
, PER_APPLICANT_PKG.GET_STYLE_NAME (ADDR.STYLE) TERRITORY_SHORT_NAME
, ADDR.ADDRESS_LINE1
, ADDR.ADDRESS_LINE2
, ADDR.ADDRESS_LINE3
, ADDR.ADDRESS_TYPE
, ADDR.COUNTRY
, ADDR.POSTAL_CODE
, ADDR.REGION_1
, ADDR.REGION_2
, ADDR.REGION_3
, ADDR.TELEPHONE_NUMBER_1
, ADDR.TELEPHONE_NUMBER_2
, ADDR.TELEPHONE_NUMBER_3
, ADDR.TOWN_OR_CITY
, ADDR.ADDR_ATTRIBUTE_CATEGORY
, ADDR.ADDR_ATTRIBUTE1
, ADDR.ADDR_ATTRIBUTE2
, ADDR.ADDR_ATTRIBUTE3
, ADDR.ADDR_ATTRIBUTE4
, ADDR.ADDR_ATTRIBUTE5
, ADDR.ADDR_ATTRIBUTE6
, ADDR.ADDR_ATTRIBUTE7
, ADDR.ADDR_ATTRIBUTE8
, ADDR.ADDR_ATTRIBUTE9
, ADDR.ADDR_ATTRIBUTE10
, ADDR.ADDR_ATTRIBUTE11
, ADDR.ADDR_ATTRIBUTE12
, ADDR.ADDR_ATTRIBUTE13
, ADDR.ADDR_ATTRIBUTE14
, ADDR.ADDR_ATTRIBUTE15
, ADDR.ADDR_ATTRIBUTE16
, ADDR.ADDR_ATTRIBUTE17
, ADDR.ADDR_ATTRIBUTE18
, ADDR.ADDR_ATTRIBUTE19
, ADDR.ADDR_ATTRIBUTE20
, /* APPLICATION INFORMATION */ APP.APPLICATION_ID
, APP.DATE_RECEIVED
, APP.CURRENT_EMPLOYER
, APP.APPL_ATTRIBUTE_CATEGORY
, APP.APPL_ATTRIBUTE1
, APP.APPL_ATTRIBUTE2
, APP.APPL_ATTRIBUTE3
, APP.APPL_ATTRIBUTE4
, APP.APPL_ATTRIBUTE5
, APP.APPL_ATTRIBUTE6
, APP.APPL_ATTRIBUTE7
, APP.APPL_ATTRIBUTE8
, APP.APPL_ATTRIBUTE9
, APP.APPL_ATTRIBUTE10
, APP.APPL_ATTRIBUTE11
, APP.APPL_ATTRIBUTE12
, APP.APPL_ATTRIBUTE13
, APP.APPL_ATTRIBUTE14
, APP.APPL_ATTRIBUTE15
, APP.APPL_ATTRIBUTE16
, APP.APPL_ATTRIBUTE17
, APP.APPL_ATTRIBUTE18
, APP.APPL_ATTRIBUTE19
, APP.APPL_ATTRIBUTE20
, A.RECRUITMENT_ACTIVITY_ID
, RA.NAME D_RECRUITMENT_ACTIVITY_ID
, LO3.MEANING RECRUITMENT_ACTIVITY_TYPE
, A.SOURCE_ORGANIZATION_ID
, ORG1TL.NAME D_SOURCE_ORGANIZATION_ID
, A.PERSON_REFERRED_BY_ID
, REF.FULL_NAME D_PERSON_REFERRED_BY_ID
, REF.EMPLOYEE_NUMBER D_PERSON_REFERRED_NUMBER
, /* ASSIGNMENT INFORMATION */ A.ASSIGNMENT_ID
, A.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
, A.RECRUITER_ID
, REC.FULL_NAME D_RECRUITER_ID
, A.ORGANIZATION_ID
, ORG2TL.NAME D_ORGANIZATION_ID
, A.PEOPLE_GROUP_ID
, A.JOB_ID
, JBT.NAME D_JOB_ID
, A.POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID) D_POSITION_ID
, A.GRADE_ID
, GDT.NAME D_GRADE_ID
, A.LOCATION_ID
, LOCTL.LOCATION_CODE D_LOCATION_ID
, A.SOURCE_TYPE
, A.ASSIGNMENT_TYPE
, 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
, ADDR.ADD_INFORMATION13
, ADDR.ADD_INFORMATION14
, ADDR.ADD_INFORMATION15
, ADDR.ADD_INFORMATION16
, ADDR.ADD_INFORMATION17
, ADDR.ADD_INFORMATION18
, ADDR.ADD_INFORMATION19
, ADDR.ADD_INFORMATION20
FROM PER_ADDRESSES ADDR
, PER_APPLICATIONS APP
, PER_ALL_ASSIGNMENTS_F A
, HR_LOOKUPS LO1
, HR_LOOKUPS LO2
, HR_LOOKUPS LO3
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTATL
, FND_SESSIONS FND
, PER_RECRUITMENT_ACTIVITIES RA
, HR_ALL_ORGANIZATION_UNITS ORG1
, HR_ALL_ORGANIZATION_UNITS ORG2
, HR_ALL_ORGANIZATION_UNITS_TL ORG1TL
, HR_ALL_ORGANIZATION_UNITS_TL ORG2TL
, PER_ALL_PEOPLE_F REF
, PER_ALL_PEOPLE_F REC
, PER_ALL_VACANCIES VAC
, PER_JOBS_TL JBT
, PER_GRADES_TL GDT
, HR_LOCATIONS_ALL_TL LOCTL
, HR_LOCATIONS_NO_JOIN LOC
, PER_PERSON_TYPES PPT
, PER_PEOPLE_F PEO
WHERE /**********************************************/ /* SYSTEM PERSON TYPE * / /**********************************************/ PPT.PERSON_TYPE_ID = PEO.PERSON_TYPE_ID /**********************************************/ /* ADDRESSES - ONLY PICK THE PRIMARY ADDRESS */ /**********************************************/
AND PEO.PERSON_ID = ADDR.PERSON_ID (+)
AND ADDR.PRIMARY_FLAG (+) = 'Y'
AND (ADDR.PERSON_ID IS NULL OR FND.EFFECTIVE_DATE BETWEEN ADDR.DATE_FROM
AND NVL(ADDR.DATE_TO
, TO_DATE('30-12-4712'
, 'DD-MM-YYYY'))) /****************/ /* APPLICATIONS */ /****************/
AND APP.PERSON_ID = PEO.PERSON_ID /************************/ /* RECRUITMENT ACTIVITY */ /************************/
AND A.RECRUITMENT_ACTIVITY_ID = RA.RECRUITMENT_ACTIVITY_ID (+) /*****************************/ /* RECRUITMENT ACTIVITY TYPE */ /*****************************/
AND A.SOURCE_TYPE = LO3.LOOKUP_CODE (+)
AND LO3.LOOKUP_TYPE (+) = 'REC_TYPE' /************************/ /* SOURCE ORGANIZATION */ /************************/
AND A.SOURCE_ORGANIZATION_ID = ORG1.ORGANIZATION_ID (+) /************************/ /* PERSON REFERRED BY */ /************************/
AND A.PERSON_REFERRED_BY_ID = REF.PERSON_ID (+)
AND ( REF.PERSON_ID IS NULL OR FND.EFFECTIVE_DATE BETWEEN REF.EFFECTIVE_START_DATE
AND REF.EFFECTIVE_END_DATE ) /****************/ /* VACANCY NAME */ /****************/
AND A.VACANCY_ID = VAC.VACANCY_ID (+) /****************/ /* RECRUITER */ /****************/
AND A.RECRUITER_ID = REC.PERSON_ID (+)
AND ( REC.PERSON_ID IS NULL OR FND.EFFECTIVE_DATE BETWEEN REC.EFFECTIVE_START_DATE
AND REC.EFFECTIVE_END_DATE ) /****************/ /* ORGANIZATION */ /****************/
AND A.ORGANIZATION_ID = ORG2.ORGANIZATION_ID /****************/ /* JOB */ /****************/
AND A.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE (+) = USERENV('LANG') /****************/ /* GRADE */ /****************/
AND A.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE (+) = USERENV('LANG') /****************/ /* LOCATION */ /****************/
AND A.LOCATION_ID = LOC.LOCATION_ID (+) /****************/ /* ASSIGNMENT */ /****************/
AND A.ASSIGNMENT_ID = (SELECT MIN(A2.ASSIGNMENT_ID)
FROM PER_ALL_ASSIGNMENTS A2
WHERE A2.APPLICATION_ID = APP.APPLICATION_ID
AND A2.ASSIGNMENT_TYPE = 'A' )
AND FND.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE /*********************/ /* ASSIGNMENT STATUS */ /*********************/
AND AST.ASSIGNMENT_STATUS_TYPE_ID = A.ASSIGNMENT_STATUS_TYPE_ID
AND NVL(AST.BUSINESS_GROUP_ID
, A.BUSINESS_GROUP_ID) = A.BUSINESS_GROUP_ID + 0
AND ASTA.ASSIGNMENT_STATUS_TYPE_ID (+) = A.ASSIGNMENT_STATUS_TYPE_ID
AND ASTA.BUSINESS_GROUP_ID (+) = A.BUSINESS_GROUP_ID + 0 /******************/ /* PERSON DETAILS */ /******************/
AND LO1.LOOKUP_TYPE (+) = 'TITLE'
AND LO1.LOOKUP_CODE (+) = PEO.TITLE
AND LO2.LOOKUP_TYPE (+) = 'SEX'
AND LO2.LOOKUP_CODE (+) = PEO.SEX
AND PEO.CURRENT_APPLICANT_FLAG = 'Y'
AND FND.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE /****************/ /* SESSION DATE */ /****************/
AND USERENV('SESSIONID') = FND.SESSION_ID
AND ORG1.ORGANIZATION_ID = ORG1TL.ORGANIZATION_ID (+)
AND ORG2.ORGANIZATION_ID = ORG2TL.ORGANIZATION_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTATL.ASS_STATUS_TYPE_AMEND_ID (+)
AND DECODE(ORG1TL.ORGANIZATION_ID
, NULL
, '1'
, ORG1TL.LANGUAGE) = DECODE(ORG1TL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND ORG2TL.LANGUAGE = USERENV('LANG')
AND ASTTL.LANGUAGE = USERENV('LANG')
AND DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, ASTATL.LANGUAGE) = DECODE(ASTATL.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'))

Columns