VIEW: APPS.PER_ALL_ASSIGNMENTS_D

Source

View Text - Preformatted

SELECT ASSIGN.ASSIGNMENT_ID , ASSIGN.EFFECTIVE_START_DATE , ASSIGN.EFFECTIVE_END_DATE , REC.FULL_NAME , GRT.NAME , hr_general.DECODE_POSITION_LATEST_NAME(ASSIGN.position_id, ASSIGN.effective_start_date) position , JBT.NAME , HR_GENERAL.GET_USER_STATUS(ASSIGN.ASSIGNMENT_STATUS_TYPE_ID) , PAYROLL.PAYROLL_NAME , LOC.LOCATION_CODE , SUP.FULL_NAME , POINT.SPINAL_POINT , HR_GENERAL.DECODE_ORGANIZATION(ASSIGN.ORGANIZATION_ID) , GRP.GROUP_NAME , SCL.CONCATENATED_SEGMENTS , VAC.NAME , FND1.MEANING , ASSIGN.ASSIGNMENT_NUMBER , HR_GENERAL.DECODE_LOOKUP(decode(ASSIGN.ASSIGNMENT_TYPE,'E','EMP_ASSIGN_REASON' ,'A','APL_ASSIGN_REASON'), ASSIGN.CHANGE_REASON) , ASSIGN.DATE_PROBATION_END , HR_GENERAL.DECODE_LOOKUP('FREQUENCY', ASSIGN.FREQUENCY) , ASSIGN.INTERNAL_ADDRESS_LINE , FND2.MEANING , ASSIGN.NORMAL_HOURS , ASSIGN.PROBATION_PERIOD , HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS', ASSIGN.PROBATION_UNIT) , ASSIGN.TIME_NORMAL_FINISH , ASSIGN.TIME_NORMAL_START , ASSIGN.LAST_UPDATE_DATE , FUSER.USER_NAME , HR_GENERAL.DECODE_LOOKUP('EMP_CAT', ASSIGN.EMPLOYMENT_CATEGORY) , BASIS.NAME , ASSIGN.TITLE , RECACT.NAME , ASSIGN.CONTRACT_ID FROM PER_ASSIGNMENTS_F ASSIGN , PER_ALL_PEOPLE_F REC , PER_GRADES_TL GRT , PER_JOBS_TL JBT , PAY_ALL_PAYROLLS_F PAYROLL , HR_LOCATIONS LOC , PER_ALL_PEOPLE_F SUP , PER_SPINAL_POINT_STEPS_F STEP , PER_SPINAL_POINTS POINT , PAY_PEOPLE_GROUPS GRP , HR_SOFT_CODING_KEYFLEX SCL , PER_ALL_VACANCIES VAC , FND_LOOKUPS FND1 , FND_LOOKUPS FND2 , FND_USER FUSER , PER_PAY_BASES BASIS , PER_RECRUITMENT_ACTIVITIES RECACT ,PER_CONTRACTS_F CTR WHERE GRT.GRADE_ID (+)= ASSIGN.GRADE_ID AND GRT.LANGUAGE (+)= userenv('LANG') AND REC.PERSON_ID (+)= ASSIGN.RECRUITER_ID AND RECACT.RECRUITMENT_ACTIVITY_ID (+) = ASSIGN.RECRUITMENT_ACTIVITY_ID AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (REC.EFFECTIVE_START_DATE, ASSIGN.EFFECTIVE_START_DATE) AND NVL (REC.EFFECTIVE_END_DATE, ASSIGN.EFFECTIVE_START_DATE) AND JBT.JOB_ID (+)= ASSIGN.JOB_ID AND JBT.LANGUAGE (+)= userenv('LANG') AND PAYROLL.PAYROLL_ID (+)= ASSIGN.PAYROLL_ID AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (PAYROLL.EFFECTIVE_START_DATE, ASSIGN.EFFECTIVE_START_DATE) AND NVL (PAYROLL.EFFECTIVE_END_DATE, ASSIGN.EFFECTIVE_START_DATE) AND LOC.LOCATION_ID (+)= ASSIGN.LOCATION_ID AND SUP.PERSON_ID (+)= ASSIGN.SUPERVISOR_ID AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (SUP.EFFECTIVE_START_DATE, ASSIGN.EFFECTIVE_START_DATE) AND NVL (SUP.EFFECTIVE_END_DATE, ASSIGN.EFFECTIVE_START_DATE) AND STEP.STEP_ID (+)= ASSIGN.SPECIAL_CEILING_STEP_ID AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (STEP.EFFECTIVE_START_DATE, ASSIGN.EFFECTIVE_START_DATE)AND NVL (STEP.EFFECTIVE_END_DATE, ASSIGN.EFFECTIVE_START_DATE) AND POINT.SPINAL_POINT_ID (+)= STEP.SPINAL_POINT_ID AND GRP.PEOPLE_GROUP_ID (+)= ASSIGN.PEOPLE_GROUP_ID AND SCL.SOFT_CODING_KEYFLEX_ID (+)= ASSIGN.SOFT_CODING_KEYFLEX_ID AND VAC.VACANCY_ID (+)= ASSIGN.VACANCY_ID AND FND1.LOOKUP_CODE = ASSIGN.PRIMARY_FLAG AND FND1.LOOKUP_TYPE = 'YES_NO' AND FND2.LOOKUP_CODE (+)= ASSIGN.MANAGER_FLAG AND FND2.LOOKUP_TYPE (+)= 'YES_NO' AND BASIS.PAY_BASIS_ID (+)= ASSIGN.PAY_BASIS_ID AND FUSER.USER_ID (+)= ASSIGN.LAST_UPDATED_BY AND CTR.CONTRACT_ID (+) = ASSIGN.CONTRACT_ID AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL(CTR.EFFECTIVE_START_DATE,ASSIGN.EFFECTIVE_START_DATE) AND NVL(CTR.EFFECTIVE_END_DATE,ASSIGN.EFFECTIVE_START_DATE)

View Text - HTML Formatted

SELECT ASSIGN.ASSIGNMENT_ID
, ASSIGN.EFFECTIVE_START_DATE
, ASSIGN.EFFECTIVE_END_DATE
, REC.FULL_NAME
, GRT.NAME
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(ASSIGN.POSITION_ID
, ASSIGN.EFFECTIVE_START_DATE) POSITION
, JBT.NAME
, HR_GENERAL.GET_USER_STATUS(ASSIGN.ASSIGNMENT_STATUS_TYPE_ID)
, PAYROLL.PAYROLL_NAME
, LOC.LOCATION_CODE
, SUP.FULL_NAME
, POINT.SPINAL_POINT
, HR_GENERAL.DECODE_ORGANIZATION(ASSIGN.ORGANIZATION_ID)
, GRP.GROUP_NAME
, SCL.CONCATENATED_SEGMENTS
, VAC.NAME
, FND1.MEANING
, ASSIGN.ASSIGNMENT_NUMBER
, HR_GENERAL.DECODE_LOOKUP(DECODE(ASSIGN.ASSIGNMENT_TYPE
, 'E'
, 'EMP_ASSIGN_REASON'
, 'A'
, 'APL_ASSIGN_REASON')
, ASSIGN.CHANGE_REASON)
, ASSIGN.DATE_PROBATION_END
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, ASSIGN.FREQUENCY)
, ASSIGN.INTERNAL_ADDRESS_LINE
, FND2.MEANING
, ASSIGN.NORMAL_HOURS
, ASSIGN.PROBATION_PERIOD
, HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, ASSIGN.PROBATION_UNIT)
, ASSIGN.TIME_NORMAL_FINISH
, ASSIGN.TIME_NORMAL_START
, ASSIGN.LAST_UPDATE_DATE
, FUSER.USER_NAME
, HR_GENERAL.DECODE_LOOKUP('EMP_CAT'
, ASSIGN.EMPLOYMENT_CATEGORY)
, BASIS.NAME
, ASSIGN.TITLE
, RECACT.NAME
, ASSIGN.CONTRACT_ID
FROM PER_ASSIGNMENTS_F ASSIGN
, PER_ALL_PEOPLE_F REC
, PER_GRADES_TL GRT
, PER_JOBS_TL JBT
, PAY_ALL_PAYROLLS_F PAYROLL
, HR_LOCATIONS LOC
, PER_ALL_PEOPLE_F SUP
, PER_SPINAL_POINT_STEPS_F STEP
, PER_SPINAL_POINTS POINT
, PAY_PEOPLE_GROUPS GRP
, HR_SOFT_CODING_KEYFLEX SCL
, PER_ALL_VACANCIES VAC
, FND_LOOKUPS FND1
, FND_LOOKUPS FND2
, FND_USER FUSER
, PER_PAY_BASES BASIS
, PER_RECRUITMENT_ACTIVITIES RECACT
, PER_CONTRACTS_F CTR
WHERE GRT.GRADE_ID (+)= ASSIGN.GRADE_ID
AND GRT.LANGUAGE (+)= USERENV('LANG')
AND REC.PERSON_ID (+)= ASSIGN.RECRUITER_ID
AND RECACT.RECRUITMENT_ACTIVITY_ID (+) = ASSIGN.RECRUITMENT_ACTIVITY_ID
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (REC.EFFECTIVE_START_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND NVL (REC.EFFECTIVE_END_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND JBT.JOB_ID (+)= ASSIGN.JOB_ID
AND JBT.LANGUAGE (+)= USERENV('LANG')
AND PAYROLL.PAYROLL_ID (+)= ASSIGN.PAYROLL_ID
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (PAYROLL.EFFECTIVE_START_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND NVL (PAYROLL.EFFECTIVE_END_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND LOC.LOCATION_ID (+)= ASSIGN.LOCATION_ID
AND SUP.PERSON_ID (+)= ASSIGN.SUPERVISOR_ID
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (SUP.EFFECTIVE_START_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND NVL (SUP.EFFECTIVE_END_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND STEP.STEP_ID (+)= ASSIGN.SPECIAL_CEILING_STEP_ID
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL (STEP.EFFECTIVE_START_DATE
, ASSIGN.EFFECTIVE_START_DATE)AND NVL (STEP.EFFECTIVE_END_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND POINT.SPINAL_POINT_ID (+)= STEP.SPINAL_POINT_ID
AND GRP.PEOPLE_GROUP_ID (+)= ASSIGN.PEOPLE_GROUP_ID
AND SCL.SOFT_CODING_KEYFLEX_ID (+)= ASSIGN.SOFT_CODING_KEYFLEX_ID
AND VAC.VACANCY_ID (+)= ASSIGN.VACANCY_ID
AND FND1.LOOKUP_CODE = ASSIGN.PRIMARY_FLAG
AND FND1.LOOKUP_TYPE = 'YES_NO'
AND FND2.LOOKUP_CODE (+)= ASSIGN.MANAGER_FLAG
AND FND2.LOOKUP_TYPE (+)= 'YES_NO'
AND BASIS.PAY_BASIS_ID (+)= ASSIGN.PAY_BASIS_ID
AND FUSER.USER_ID (+)= ASSIGN.LAST_UPDATED_BY
AND CTR.CONTRACT_ID (+) = ASSIGN.CONTRACT_ID
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN NVL(CTR.EFFECTIVE_START_DATE
, ASSIGN.EFFECTIVE_START_DATE)
AND NVL(CTR.EFFECTIVE_END_DATE
, ASSIGN.EFFECTIVE_START_DATE)