VIEW: APPS.PER_ASSIGNMENTS_V7

Source

View Text - Preformatted

SELECT PAF.ROWID , ASSIGNMENT_ID , PAF.EFFECTIVE_START_DATE , PAF.EFFECTIVE_END_DATE , PAF.BUSINESS_GROUP_ID + 0 , PAF.POSITION_ID , hr_general.decode_position_latest_name(paf.position_id, paf.effective_start_date) , PAF.JOB_ID , JBT.NAME , ASSIGNMENT_STATUS_TYPE_ID , LOC.LOCATION_ID , SUPERVISOR_ID , SUP.FULL_NAME , PAF.PERSON_ID , PAF.ORGANIZATION_ID , HOU.NAME , ASSIGNMENT_SEQUENCE , ASSIGNMENT_TYPE , PRIMARY_FLAG , ASSIGNMENT_NUMBER , PAF.COMMENT_ID , DEFAULT_CODE_COMB_ID , PERIOD_OF_SERVICE_ID , PAF.SET_OF_BOOKS_ID , GSB.NAME , PAF.REQUEST_ID , PAF.PROGRAM_APPLICATION_ID , PAF.PROGRAM_ID , PAF.PROGRAM_UPDATE_DATE , ASS_ATTRIBUTE_CATEGORY , ASS_ATTRIBUTE1 , ASS_ATTRIBUTE2 , ASS_ATTRIBUTE3 , ASS_ATTRIBUTE4 , ASS_ATTRIBUTE5 , ASS_ATTRIBUTE6 , ASS_ATTRIBUTE7 , ASS_ATTRIBUTE8 , ASS_ATTRIBUTE9 , ASS_ATTRIBUTE10 , ASS_ATTRIBUTE11 , ASS_ATTRIBUTE12 , ASS_ATTRIBUTE13 , ASS_ATTRIBUTE14 , ASS_ATTRIBUTE15 , ASS_ATTRIBUTE16 , ASS_ATTRIBUTE17 , ASS_ATTRIBUTE18 , ASS_ATTRIBUTE19 , ASS_ATTRIBUTE20 , ASS_ATTRIBUTE21 , ASS_ATTRIBUTE22 , ASS_ATTRIBUTE23 , ASS_ATTRIBUTE24 , ASS_ATTRIBUTE25 , ASS_ATTRIBUTE26 , ASS_ATTRIBUTE27 , ASS_ATTRIBUTE28 , ASS_ATTRIBUTE29 , ASS_ATTRIBUTE30 , RECRUITER_ID , GRADE_ID , PAYROLL_ID , SPECIAL_CEILING_STEP_ID , RECRUITMENT_ACTIVITY_ID , SOURCE_ORGANIZATION_ID , PEOPLE_GROUP_ID , PAF.SOFT_CODING_KEYFLEX_ID , VACANCY_ID , PAY_BASIS_ID , PAF.APPLICATION_ID , PERSON_REFERRED_BY_ID , CHANGE_REASON , DATE_PROBATION_END , EMPLOYMENT_CATEGORY , PAF.FREQUENCY , PAF.INTERNAL_ADDRESS_LINE , MANAGER_FLAG , NORMAL_HOURS , PERF_REVIEW_PERIOD , PERF_REVIEW_PERIOD_FREQUENCY , PAF.PROBATION_PERIOD , PAF.PROBATION_UNIT , SAL_REVIEW_PERIOD , SAL_REVIEW_PERIOD_FREQUENCY , SOURCE_TYPE , PAF.TIME_NORMAL_FINISH , PAF.TIME_NORMAL_START , PAF.LAST_UPDATE_DATE , PAF.LAST_UPDATED_BY , PAF.LAST_UPDATE_LOGIN , PAF.CREATED_BY , PAF.CREATION_DATE , PAF.TITLE , LOCTL.LOCATION_CODE , ADDRESS_LINE_1 , ADDRESS_LINE_2 , ADDRESS_LINE_3 , LOC.BILL_TO_SITE_FLAG , COUNTRY , LOCTL.DESCRIPTION , LOC.DESIGNATED_RECEIVER_ID , LOC.IN_ORGANIZATION_FLAG , LOC.INACTIVE_DATE , LOC.INVENTORY_ORGANIZATION_ID , LOC.OFFICE_SITE_FLAG , POSTAL_CODE , LOC.RECEIVING_SITE_FLAG , LOC.TAX_NAME , LOC.SHIP_TO_LOCATION_ID , LOC.SHIP_TO_SITE_FLAG , REGION_1 , REGION_2 , REGION_3 , STYLE , FND.DESCRIPTIVE_FLEX_CONTEXT_NAME , TELEPHONE_NUMBER_1 , TELEPHONE_NUMBER_2 , TELEPHONE_NUMBER_3 , TOWN_OR_CITY , LOC.ATTRIBUTE_CATEGORY , LOC.ATTRIBUTE1 , LOC.ATTRIBUTE2 , LOC.ATTRIBUTE3 , LOC.ATTRIBUTE4 , LOC.ATTRIBUTE5 , LOC.ATTRIBUTE6 , LOC.ATTRIBUTE7 , LOC.ATTRIBUTE8 , LOC.ATTRIBUTE9 , LOC.ATTRIBUTE10 , LOC.ATTRIBUTE11 , LOC.ATTRIBUTE12 , LOC.ATTRIBUTE13 , LOC.ATTRIBUTE14 , LOC.ATTRIBUTE15 , LOC.ATTRIBUTE16 , LOC.ATTRIBUTE17 , LOC.ATTRIBUTE18 , LOC.ATTRIBUTE19 , LOC.ATTRIBUTE20 , LOC.LOC_INFORMATION13 , LOC.LOC_INFORMATION14 , LOC.LOC_INFORMATION15 , LOC.LOC_INFORMATION16 , LOC.LOC_INFORMATION17 , LOC.LOC_INFORMATION18 , LOC.LOC_INFORMATION19 , LOC.LOC_INFORMATION20 FROM PER_ASSIGNMENTS_F PAF , GL_SETS_OF_BOOKS GSB , HR_LOCATIONS_ALL_TL LOCTL , HR_LOCATIONS_NO_JOIN LOC , FND_DESCR_FLEX_CONTEXTS_TL FND, PER_JOBS_TL JBT, HR_ORGANIZATION_UNITS HOU , PER_PEOPLE_F SUP , HR_COMMENTS COM WHERE PAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID AND PAF.JOB_ID = JBT.JOB_ID (+) AND JBT.LANGUAGE(+) = userenv('LANG') AND PAF.LOCATION_ID = LOC.LOCATION_ID (+) AND LOC.STYLE = FND.DESCRIPTIVE_FLEX_CONTEXT_CODE (+) AND nvl(FND.APPLICATION_ID,800) = 800 AND nvl(FND.DESCRIPTIVE_FLEXFIELD_NAME,'Address Location') = 'Address Location' and nvl(FND.LANGUAGE,userenv('LANG')) = userenv('LANG') AND PAF.SUPERVISOR_ID = SUP.PERSON_ID (+) AND greatest(trunc(sysdate),nvl(sup.effective_start_date,to_date('01-01-0001', 'DD-MM-YYYY'))) between nvl(sup.effective_start_date,to_date('01-01-0001', 'DD-MM-YYYY')) and nvl(sup.effective_end_date,to_date('31-12-4712', 'DD-MM-YYYY')) AND PAF.COMMENT_ID = COM.COMMENT_ID (+) AND PAF.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID (+) AND loc.location_id = loctl.location_id (+) AND decode(loctl.location_id,null,'1',loctl.language)= decode(loctl.location_id,null,'1',userenv('LANG'))

View Text - HTML Formatted

SELECT PAF.ROWID
, ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, PAF.BUSINESS_GROUP_ID + 0
, PAF.POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAF.POSITION_ID
, PAF.EFFECTIVE_START_DATE)
, PAF.JOB_ID
, JBT.NAME
, ASSIGNMENT_STATUS_TYPE_ID
, LOC.LOCATION_ID
, SUPERVISOR_ID
, SUP.FULL_NAME
, PAF.PERSON_ID
, PAF.ORGANIZATION_ID
, HOU.NAME
, ASSIGNMENT_SEQUENCE
, ASSIGNMENT_TYPE
, PRIMARY_FLAG
, ASSIGNMENT_NUMBER
, PAF.COMMENT_ID
, DEFAULT_CODE_COMB_ID
, PERIOD_OF_SERVICE_ID
, PAF.SET_OF_BOOKS_ID
, GSB.NAME
, PAF.REQUEST_ID
, PAF.PROGRAM_APPLICATION_ID
, PAF.PROGRAM_ID
, PAF.PROGRAM_UPDATE_DATE
, ASS_ATTRIBUTE_CATEGORY
, ASS_ATTRIBUTE1
, ASS_ATTRIBUTE2
, ASS_ATTRIBUTE3
, ASS_ATTRIBUTE4
, ASS_ATTRIBUTE5
, ASS_ATTRIBUTE6
, ASS_ATTRIBUTE7
, ASS_ATTRIBUTE8
, ASS_ATTRIBUTE9
, ASS_ATTRIBUTE10
, ASS_ATTRIBUTE11
, ASS_ATTRIBUTE12
, ASS_ATTRIBUTE13
, ASS_ATTRIBUTE14
, ASS_ATTRIBUTE15
, ASS_ATTRIBUTE16
, ASS_ATTRIBUTE17
, ASS_ATTRIBUTE18
, ASS_ATTRIBUTE19
, ASS_ATTRIBUTE20
, ASS_ATTRIBUTE21
, ASS_ATTRIBUTE22
, ASS_ATTRIBUTE23
, ASS_ATTRIBUTE24
, ASS_ATTRIBUTE25
, ASS_ATTRIBUTE26
, ASS_ATTRIBUTE27
, ASS_ATTRIBUTE28
, ASS_ATTRIBUTE29
, ASS_ATTRIBUTE30
, RECRUITER_ID
, GRADE_ID
, PAYROLL_ID
, SPECIAL_CEILING_STEP_ID
, RECRUITMENT_ACTIVITY_ID
, SOURCE_ORGANIZATION_ID
, PEOPLE_GROUP_ID
, PAF.SOFT_CODING_KEYFLEX_ID
, VACANCY_ID
, PAY_BASIS_ID
, PAF.APPLICATION_ID
, PERSON_REFERRED_BY_ID
, CHANGE_REASON
, DATE_PROBATION_END
, EMPLOYMENT_CATEGORY
, PAF.FREQUENCY
, PAF.INTERNAL_ADDRESS_LINE
, MANAGER_FLAG
, NORMAL_HOURS
, PERF_REVIEW_PERIOD
, PERF_REVIEW_PERIOD_FREQUENCY
, PAF.PROBATION_PERIOD
, PAF.PROBATION_UNIT
, SAL_REVIEW_PERIOD
, SAL_REVIEW_PERIOD_FREQUENCY
, SOURCE_TYPE
, PAF.TIME_NORMAL_FINISH
, PAF.TIME_NORMAL_START
, PAF.LAST_UPDATE_DATE
, PAF.LAST_UPDATED_BY
, PAF.LAST_UPDATE_LOGIN
, PAF.CREATED_BY
, PAF.CREATION_DATE
, PAF.TITLE
, LOCTL.LOCATION_CODE
, ADDRESS_LINE_1
, ADDRESS_LINE_2
, ADDRESS_LINE_3
, LOC.BILL_TO_SITE_FLAG
, COUNTRY
, LOCTL.DESCRIPTION
, LOC.DESIGNATED_RECEIVER_ID
, LOC.IN_ORGANIZATION_FLAG
, LOC.INACTIVE_DATE
, LOC.INVENTORY_ORGANIZATION_ID
, LOC.OFFICE_SITE_FLAG
, POSTAL_CODE
, LOC.RECEIVING_SITE_FLAG
, LOC.TAX_NAME
, LOC.SHIP_TO_LOCATION_ID
, LOC.SHIP_TO_SITE_FLAG
, REGION_1
, REGION_2
, REGION_3
, STYLE
, FND.DESCRIPTIVE_FLEX_CONTEXT_NAME
, TELEPHONE_NUMBER_1
, TELEPHONE_NUMBER_2
, TELEPHONE_NUMBER_3
, TOWN_OR_CITY
, LOC.ATTRIBUTE_CATEGORY
, LOC.ATTRIBUTE1
, LOC.ATTRIBUTE2
, LOC.ATTRIBUTE3
, LOC.ATTRIBUTE4
, LOC.ATTRIBUTE5
, LOC.ATTRIBUTE6
, LOC.ATTRIBUTE7
, LOC.ATTRIBUTE8
, LOC.ATTRIBUTE9
, LOC.ATTRIBUTE10
, LOC.ATTRIBUTE11
, LOC.ATTRIBUTE12
, LOC.ATTRIBUTE13
, LOC.ATTRIBUTE14
, LOC.ATTRIBUTE15
, LOC.ATTRIBUTE16
, LOC.ATTRIBUTE17
, LOC.ATTRIBUTE18
, LOC.ATTRIBUTE19
, LOC.ATTRIBUTE20
, LOC.LOC_INFORMATION13
, LOC.LOC_INFORMATION14
, LOC.LOC_INFORMATION15
, LOC.LOC_INFORMATION16
, LOC.LOC_INFORMATION17
, LOC.LOC_INFORMATION18
, LOC.LOC_INFORMATION19
, LOC.LOC_INFORMATION20
FROM PER_ASSIGNMENTS_F PAF
, GL_SETS_OF_BOOKS GSB
, HR_LOCATIONS_ALL_TL LOCTL
, HR_LOCATIONS_NO_JOIN LOC
, FND_DESCR_FLEX_CONTEXTS_TL FND
, PER_JOBS_TL JBT
, HR_ORGANIZATION_UNITS HOU
, PER_PEOPLE_F SUP
, HR_COMMENTS COM
WHERE PAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PAF.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PAF.LOCATION_ID = LOC.LOCATION_ID (+)
AND LOC.STYLE = FND.DESCRIPTIVE_FLEX_CONTEXT_CODE (+)
AND NVL(FND.APPLICATION_ID
, 800) = 800
AND NVL(FND.DESCRIPTIVE_FLEXFIELD_NAME
, 'ADDRESS LOCATION') = 'ADDRESS LOCATION'
AND NVL(FND.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND PAF.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND GREATEST(TRUNC(SYSDATE)
, NVL(SUP.EFFECTIVE_START_DATE
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))) BETWEEN NVL(SUP.EFFECTIVE_START_DATE
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(SUP.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PAF.COMMENT_ID = COM.COMMENT_ID (+)
AND PAF.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID (+)
AND LOC.LOCATION_ID = LOCTL.LOCATION_ID (+)
AND DECODE(LOCTL.LOCATION_ID
, NULL
, '1'
, LOCTL.LANGUAGE)= DECODE(LOCTL.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))