View: HRIFV_US_EMPASGDTL_EMPXASG

Product:	HRI - Human Resources Intelligence (Obsolete)
Description:
Implementation/DBA Data:	APPS.HRIFV_US_EMPASGDTL_EMPXASG

View Text

SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ /* UNIQUE ATTRIBUTES */ BGRT.NAME BUSINESS_GROUP_NAME
, GREATEST(PEO.EFFECTIVE_START_DATE
, ASS.EFFECTIVE_START_DATE) RECORD_START_DATE
, LEAST(PEO.EFFECTIVE_END_DATE
, ASS.EFFECTIVE_END_DATE) RECORD_END_DATE
, PEO.EFFECTIVE_START_DATE EMPLOYEE_START_DATE
, PEO.EFFECTIVE_END_DATE EMPLOYEE_END_DATE
, ASS.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASS.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE /* EMPLOYEE ATTRIBUTES */
, PEO.FULL_NAME EMPLOYEE_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, DECODE(PEO.SEX
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX)) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, DECODE(PEO.REGISTERED_DISABLED_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG)) REGISTERED_DISABLED
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, PEO.COUNTRY_OF_BIRTH COUNTRY_OF_BIRTH
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, DECODE(PEO.CURRENT_APPLICANT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_APPLICANT_FLAG)) CURRENT_APPLICANT_FLAG
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_EMPLOYEE_FLAG)) CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE /* REGULAR ASSIGNMENT ATTRIBUTES */
, ASS.TITLE ASSIGNMENT_TITLE
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASS.TIME_NORMAL_START NORMAL_START_TIME
, ASS.TIME_NORMAL_FINISH NORMAL_END_TIME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASS.PRIMARY_FLAG) PRIMARY_FLAG
, ASS.INTERNAL_ADDRESS_LINE INTERNAL_ADDRESS
, DECODE(ASS.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASS.MANAGER_FLAG)) MANAGER_FLAG
, DECODE(ASS.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASS.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY
, ASS.NORMAL_HOURS WORKING_HOURS_AMOUNT
, DECODE(ASS.FREQUENCY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASS.FREQUENCY)) WORKING_HOURS_FREQUENCY /* US LEGISLATIVE LOOKUPS */
, DECODE(PEO.PER_INFORMATION1
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_ETHNIC_GROUP'
, PEO.PER_INFORMATION1)) ETHNIC_ORIGIN
, DECODE(PEO.PER_INFORMATION2
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION2)) I9
, FND_DATE.CANONICAL_TO_DATE(PEO.PER_INFORMATION3) I9_EXPIRY_DATE
, DECODE(PEO.PER_INFORMATION4
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_VISA_TYPE'
, PEO.PER_INFORMATION4)) VISA_TYPE
, DECODE(PEO.PER_INFORMATION5
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_VETERAN_STATUS'
, PEO.PER_INFORMATION5)) VIETNAM_VETS_STATUS
, DECODE(PEO.PER_INFORMATION6
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION6)) STUDENT
, DECODE(PEO.PER_INFORMATION7
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_NEW_HIRE_STATUS'
, PEO.PER_INFORMATION7)) NEW_HIRE_STATUS
, PEO.PER_INFORMATION8 EXCLUSION_REASON
, DECODE(PEO.PER_INFORMATION9
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION9)) CHILD_SUPPORT_OBLIGATION /* FOREIGN KEY RESOLUTIONS */
, PEO.NATIONAL_IDENTIFIER SOCIAL_SECURITY_NUMBER
, NVL(ASTAT.USER_STATUS
, ASTT.USER_STATUS) USER_ASSIGNMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('PER_ASS_SYS_STATUS'
, AST.PER_SYSTEM_STATUS) PER_SYSTEM_ASSIGNMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('PAY_ASS_SYS_STATUS'
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)) PAY_SYSTEM_ASSIGNMENT_STATUS /* DESCRIPTIVE FLEXFIELD */
, '_DF:PER:PER_ASSIGNMENTS:ASS'
, '_DF:PER:PER_PEOPLE:PEO' /* COLUMN SYSTEM CODES NOT TRANSLATED */
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS MAIL_DESTINATION_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, ASS.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASS.MANAGER_FLAG MANAGER_FLAG_CODE
, ASS.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASS.FREQUENCY WORKING_HOURS_FREQUENCY_CODE
, PEO.PER_INFORMATION1 ETHNIC_ORIGIN_CODE
, PEO.PER_INFORMATION2 I9_CODE
, PEO.PER_INFORMATION4 VISA_TYPE_CODE
, PEO.PER_INFORMATION5 VIETNAM_VETS_STATUS_CODE
, PEO.PER_INFORMATION6 STUDENT_CODE
, PEO.PER_INFORMATION7 NEW_HIRE_STATUS_CODE
, PEO.PER_INFORMATION9 CHILD_SUPPORT_OBLIGATION_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_ASGN_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_ASGN_STATUS_CODE /* IDS */
, PEO.PERSON_ID PERSON_ID
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.BENEFIT_GROUP_ID BENEFIT_GROUP_ID
, PEO.PERSON_TYPE_ID PERSON_TYPE_ID
, PEO.COMMENT_ID PERSON_COMMENT_ID
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.GRADE_ID GRADE_ID
, ASS.JOB_ID JOB_ID
, ASS.LOCATION_ID LOCATION_ID
, ASS.ORGANIZATION_ID ORGANIZATION_ID
, ASS.PAYROLL_ID PAYROLL_ID
, ASS.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASS.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASS.POSITION_ID POSITION_ID
, ASS.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, ASS.PAY_BASIS_ID SALARY_BASIS_ID
, ASS.SUPERVISOR_ID SUPERVISOR_ID
, ASS.SPECIAL_CEILING_STEP_ID SPECIAL_CEILING_STEP_ID
, ASS.COMMENT_ID ASSIGNMENT_COMMENT_ID
, ASS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, ASS.COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID /* APPLICABLE TO EMPLOYEES? */
, ASS.CONTRACT_ID CONTRACT_ID /* FOR APPLICANTS ONLY? */
, ASS.VACANCY_ID VACANCY_ID
, ASS.RECRUITER_ID RECRUITER_ID
, ASS.PERSON_REFERRED_BY_ID PERSON_REFERRED_BY_ID
, ASS.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
FROM PER_ALL_PEOPLE_F PEO
, PER_BUSINESS_GROUPS PBG
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ASSIGNMENTS_F ASS /* HISTORIC VIEW ON ASSIGNMNET */
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTT
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTAT
, PER_PERIODS_OF_SERVICE PPS
WHERE ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.PERSON_ID = PEO.PERSON_ID
AND PEO.BUSINESS_GROUP_ID = PBG.ORGANIZATION_ID
AND PBG.LEGISLATION_CODE = 'US'
AND ASS.ASSIGNMENT_TYPE = 'E'
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASS.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTT.ASSIGNMENT_STATUS_TYPE_ID
AND ASTT.LANGUAGE = USERENV('LANG')
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASS.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID (+)
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTAT.ASS_STATUS_TYPE_AMEND_ID (+)
AND NVL(ASTAT.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND ( (ASS.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASS.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASS.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASS.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) )
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND ((PEO.EMPLOYEE_NUMBER IS NULL) OR (PEO.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PEO.PERSON_ID
AND PPS1.DATE_START <= PEO.EFFECTIVE_END_DATE)) )
AND ASS.BUSINESS_GROUP_ID = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY

Columns