View: HRFV_EMPLOYEE_ASSIGNMENTS

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.TITLE 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
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASS.MANAGER_FLAG) MANAGER_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASS.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, ASS.NORMAL_HOURS WORKING_HOURS_AMOUNT
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASS.FREQUENCY) WORKING_HOURS_FREQUENCY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPS.DATE_START HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PHN.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, PPB.NAME SALARY_BASIS
, ORGT.NAME 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
, 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
, ASS.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASS.MANAGER_FLAG MANAGER_FLAG_CODE
, ASS.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASS.FREQUENCY FREQUENCY_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_STATUS_CODE
, '_KF:PAY:GRP:PGR'
, '_DF:PER:PER_ASSIGNMENTS:ASS'
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GRD.GRADE_ID GRADE_ID
, JOB.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, ASS.ORGANIZATION_ID ORGANIZATION_ID
, PAY.PAYROLL_ID PAYROLL_ID
, ASS.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASS.PERSON_ID PERSON_ID
, POS.POSITION_ID POSITION_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, PPB.PAY_BASIS_ID SALARY_BASIS_ID
, ASS.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_X ASS
, 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 ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASS.POSITION_ID = POS.POSITION_ID(+)
AND ASS.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASS.JOB_ID = JOB.JOB_ID(+)
AND ASS.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND ASS.GRADE_ID = GRD.GRADE_ID(+)
AND ASS.SUPERVISOR_ID = SUP.PERSON_ID(+)
AND ASS.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND ASS.VACANCY_ID = VAC.VACANCY_ID (+)
AND ASS.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID (+)
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.PERSON_ID = PHN.PARENT_ID (+)
AND PHN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND PHN.PHONE_TYPE (+) = 'W1'
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.EFFECTIVE_START_DATE BETWEEN PHN.DATE_FROM (+)
AND NVL(PHN.DATE_TO (+)
, HR_GENERAL.END_OF_TIME)
AND ASS.EFFECTIVE_START_DATE BETWEEN SUP.EFFECTIVE_START_DATE (+)
AND SUP.EFFECTIVE_END_DATE (+)
AND ASS.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE (+)
AND PAY.EFFECTIVE_END_DATE (+)
AND ASS.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY

Columns