VIEW: APPS.PER_ALL_ASSIGNMENTS_D

Source

View Text - Preformatted

SELECT ASSIGN.ASSIGNMENT_ID , ASSIGN.EFFECTIVE_START_DATE , ASSIGN.EFFECTIVE_END_DATE , ( SELECT PPF.FULL_NAME FROM PER_ALL_PEOPLE_F PPF WHERE PPF.PERSON_ID = REC.PERSON_ID AND PPF.EFFECTIVE_START_DATE = ( SELECT MAX (PPF1.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PPF1 WHERE PPF1.PERSON_ID = PPF.PERSON_ID AND PPF1.EFFECTIVE_START_DATE <= HR_GENERAL.EFFECTIVE_DATE ) ) RECRUITER , 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) , ( SELECT PAY.PAYROLL_NAME FROM PAY_ALL_PAYROLLS_F PAY WHERE PAY.PAYROLL_ID = PAYROLL.PAYROLL_ID AND PAY.EFFECTIVE_START_DATE = ( SELECT MAX (PAY1.EFFECTIVE_START_DATE) FROM PAY_ALL_PAYROLLS_F PAY1 WHERE PAY1.PAYROLL_ID = PAY.PAYROLL_ID AND PAY1.EFFECTIVE_START_DATE <= HR_GENERAL.EFFECTIVE_DATE ) ) PAYROLL , LOC.LOCATION_CODE , ( SELECT PPF.FULL_NAME FROM PER_ALL_PEOPLE_F PPF WHERE PPF.PERSON_ID = SUP.PERSON_ID AND PPF.EFFECTIVE_START_DATE = ( SELECT MAX (PPF1.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PPF1 WHERE PPF1.PERSON_ID = PPF.PERSON_ID AND PPF1.EFFECTIVE_START_DATE <= HR_GENERAL.EFFECTIVE_DATE ) ) SUPERVISOR , POINT.SPINAL_POINT , HR_GENERAL.DECODE_ORGANIZATION(ASSIGN.ORGANIZATION_ID) , FND_FLEX_EXT.GET_SEGS('PAY','GRP',GRP.ID_FLEX_NUM,GRP.PEOPLE_GROUP_ID) , 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 , ctr.reference 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
, ( SELECT PPF.FULL_NAME
FROM PER_ALL_PEOPLE_F PPF
WHERE PPF.PERSON_ID = REC.PERSON_ID
AND PPF.EFFECTIVE_START_DATE = ( SELECT MAX (PPF1.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PPF1
WHERE PPF1.PERSON_ID = PPF.PERSON_ID
AND PPF1.EFFECTIVE_START_DATE <= HR_GENERAL.EFFECTIVE_DATE ) ) RECRUITER
, 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)
, ( SELECT PAY.PAYROLL_NAME
FROM PAY_ALL_PAYROLLS_F PAY
WHERE PAY.PAYROLL_ID = PAYROLL.PAYROLL_ID
AND PAY.EFFECTIVE_START_DATE = ( SELECT MAX (PAY1.EFFECTIVE_START_DATE)
FROM PAY_ALL_PAYROLLS_F PAY1
WHERE PAY1.PAYROLL_ID = PAY.PAYROLL_ID
AND PAY1.EFFECTIVE_START_DATE <= HR_GENERAL.EFFECTIVE_DATE ) ) PAYROLL
, LOC.LOCATION_CODE
, ( SELECT PPF.FULL_NAME
FROM PER_ALL_PEOPLE_F PPF
WHERE PPF.PERSON_ID = SUP.PERSON_ID
AND PPF.EFFECTIVE_START_DATE = ( SELECT MAX (PPF1.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PPF1
WHERE PPF1.PERSON_ID = PPF.PERSON_ID
AND PPF1.EFFECTIVE_START_DATE <= HR_GENERAL.EFFECTIVE_DATE ) ) SUPERVISOR
, POINT.SPINAL_POINT
, HR_GENERAL.DECODE_ORGANIZATION(ASSIGN.ORGANIZATION_ID)
, FND_FLEX_EXT.GET_SEGS('PAY'
, 'GRP'
, GRP.ID_FLEX_NUM
, GRP.PEOPLE_GROUP_ID)
, 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
, CTR.REFERENCE
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)