View: HRFV_PERSON_ASSIGNMENT_HISTORY

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_PERSON_ASSIGNMENT_HISTORY

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, GREATEST(PEO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE) RECORD_START_DATE
, LEAST(PEO.EFFECTIVE_END_DATE
, ASG.EFFECTIVE_END_DATE) RECORD_END_DATE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, PEO.FULL_NAME PERSON_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
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG) REGISTERED_DISABLED
, 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
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) 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
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PHN.PHONE_NUMBER WORK_TELELPHONE_NUMBER
, '_DF:PER:PER_PEOPLE:PEO'
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG.TIME_NORMAL_START NORMAL_START_TIME
, ASG.TIME_NORMAL_FINISH NORMAL_END_TIME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.PRIMARY_FLAG) PRIMARY_FLAG
, ASG.INTERNAL_ADDRESS_LINE INTERNAL_ADDRESS
, HR_BIS.BIS_DECODE_LOOKUP('EMP_APL'
, ASG.ASSIGNMENT_TYPE) ASSIGNMENT_TYPE
, 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
, DECODE(ASG.MANAGER_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.MANAGER_FLAG)) MANAGER_FLAG
, DECODE(ASG.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASG.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASG.FREQUENCY) WORKING_HOURS_FREQUENCY
, ASG.CHANGE_REASON CHANGE_REASON
, DECODE(ASG.SOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('REC_TYPE'
, ASG.SOURCE_TYPE)) SOURCE_TYPE
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, PPB.PAY_BASIS SALARY_BASIS
, ORGT.NAME ASSIGNMENT_ORGANIZATION_NAME
, POS.NAME POSITION_NAME
, JOB.NAME JOB_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, GRD.NAME GRADE_NAME
, PGR.GROUP_NAME PEOPLE_GROUP
, PAY.PAYROLL_NAME PAYROLL_NAME
, RAC.NAME RECRUITMENT_ACTIVITY_NAME
, VAC.NAME VACANCY_NAME
, '_KF:PAY:GRP:PGR'
, '_DF:PER:PER_ASSIGNMENTS:ASG'
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, ASG.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_STATUS_CODE
, ASG.MANAGER_FLAG MANAGER_FLAG_CODE
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, ASG.FREQUENCY FREQUENCY_CODE
, ASG.SOURCE_TYPE SOURCE_TYPE_CODE
, ASG.APPLICATION_ID APPLICATION_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ORGT.ORGANIZATION_ID ASSIGNMENT_ORGANIZATION_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GRD.GRADE_ID GRADE_ID
, JOB.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, PAY.PAYROLL_ID PAYROLL_ID
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASG.PERSON_ID PERSON_ID
, PEO.PERSON_TYPE_ID PERSON_TYPE_ID
, POS.POSITION_ID POSITION_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, PPB.PAY_BASIS_ID SALARY_BASIS_ID
, ASG.SUPERVISOR_ID SUPERVISOR_ID
, VAC.VACANCY_ID VACANCY_ID
FROM PER_ALL_PEOPLE_F PEO
, PER_ALL_PEOPLE_F SUP
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_RECRUITMENT_ACTIVITIES RAC
, PER_ALL_VACANCIES VAC
, PER_PAY_BASES PPB
, PER_ALL_POSITIONS POS
, PAY_ALL_PAYROLLS_F PAY
, PER_JOBS JOB
, HR_LOCATIONS_ALL_TL LOCT
, PER_GRADES GRD
, PAY_PEOPLE_GROUPS PGR
, PER_ASSIGNMENTS_F ASG
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, 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_PHONES PHN
, PER_PERIODS_OF_SERVICE PPS
WHERE ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND ASG.POSITION_ID = POS.POSITION_ID(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.JOB_ID = JOB.JOB_ID(+)
AND ASG.LOCATION_ID = LOCT.LOCATION_ID(+)
AND ASG.GRADE_ID = GRD.GRADE_ID(+)
AND ASG.SUPERVISOR_ID = SUP.PERSON_ID(+)
AND ASG.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND ASG.VACANCY_ID = VAC.VACANCY_ID (+)
AND ASG.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID (+)
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTT.ASSIGNMENT_STATUS_TYPE_ID
AND ASTT.LANGUAGE = USERENV('LANG')
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASG.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 ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND ORGT.LANGUAGE = USERENV('LANG')
AND BGRT.LANGUAGE = USERENV('LANG')
AND PPTT.LANGUAGE = USERENV('LANG')
AND PPT.ACTIVE_FLAG = 'Y'
AND ASG.PERSON_ID = PHN.PARENT_ID (+)
AND PHN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND PHN.PHONE_TYPE (+) = 'W1'
AND ASG.EFFECTIVE_START_DATE BETWEEN PHN.DATE_FROM (+)
AND NVL(PHN.DATE_TO (+)
, HR_GENERAL.END_OF_TIME)
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND PEO.EFFECTIVE_START_DATE >= PPS.DATE_START (+)
AND ( (PEO.CURRENT_APPLICANT_FLAG IS NOT NULL) OR (PEO.CURRENT_EMPLOYEE_FLAG 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)) OR (PEO.CURRENT_APPLICANT_FLAG IS NULL
AND PEO.CURRENT_EMPLOYEE_FLAG IS 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 ASG.EFFECTIVE_START_DATE BETWEEN SUP.EFFECTIVE_START_DATE (+)
AND SUP.EFFECTIVE_END_DATE (+)
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE (+)
AND PAY.EFFECTIVE_END_DATE (+)
AND ( (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASG.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) ) WITH READ ONLY

Columns