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_PEOPLE_V3

Source

View Text - Preformatted


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, A.EFFECTIVE_START_DATE) 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'))

View Text - HTML Formatted

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
, A.EFFECTIVE_START_DATE) 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'))