View: PER_CONTRACTS_V2

Product:	PER - Human Resources
Description:	Used by Manage Contracts form.
Implementation/DBA Data:	APPS.PER_CONTRACTS_V2

View Text

SELECT PER.BUSINESS_GROUP_ID
, PER.PERSON_ID
, PER.FULL_NAME
, DECODE(PPT.SYSTEM_PERSON_TYPE
, 'EMP'
, 'EB'
, 'EMP_APL'
, 'AB'
, NULL)
, PER.NATIONAL_IDENTIFIER
, PER_MANAGE_CONTRACTS_PKG.CONTRACT_ASSOCIATION(PER.PERSON_ID)
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SES.EFFECTIVE_DATE
, PER.PERSON_ID)
, PPT.SYSTEM_PERSON_TYPE
, PER.EMPLOYEE_NUMBER
, PER.APPLICANT_NUMBER
, PPS.DATE_START
, PER.DATE_OF_BIRTH
, CON.CONTRACT_ID
, CON.REFERENCE
, CON.TYPE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CONTRACT_TYPE'
, CON.TYPE)
, 1
, 80)
, CON.STATUS
, SUBSTR(HR_GENERAL.DECODE_LOOKUP( 'CONTRACT_STATUS'
, CON.STATUS)
, 1
, 80)
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CONTRACT_STATUS_REASON'
, CON.STATUS_REASON)
, 1
, 80)
, CON.DOC_STATUS
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DOCUMENT_STATUS'
, CON.DOC_STATUS)
, 1
, 80)
, CON.DOC_STATUS_CHANGE_DATE
, CON.DESCRIPTION
, CON.DURATION
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, CON.DURATION_UNITS)
, 1
, 80)
, CON.CONTRACTUAL_JOB_TITLE
, CON.PARTIES
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CONTRACT_START_REASON'
, CON.START_REASON)
, 1
, 80)
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CONTRACT_END_REASON'
, CON.END_REASON)
, 1
, 80)
, CON.NUMBER_OF_EXTENSIONS
, CON.EXTENSION_REASON
, CON.EXTENSION_PERIOD
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, CON.EXTENSION_PERIOD_UNITS)
, 1
, 80)
, CON.CTR_INFORMATION_CATEGORY
, CON.CTR_INFORMATION1
, CON.CTR_INFORMATION2
, CON.CTR_INFORMATION3
, CON.CTR_INFORMATION4
, CON.CTR_INFORMATION5
, CON.CTR_INFORMATION6
, CON.CTR_INFORMATION7
, CON.CTR_INFORMATION8
, CON.CTR_INFORMATION9
, CON.CTR_INFORMATION10
, CON.CTR_INFORMATION11
, CON.CTR_INFORMATION12
, CON.CTR_INFORMATION13
, CON.CTR_INFORMATION14
, CON.CTR_INFORMATION15
, CON.CTR_INFORMATION16
, CON.CTR_INFORMATION17
, CON.CTR_INFORMATION18
, CON.CTR_INFORMATION19
, CON.CTR_INFORMATION20
, CON.ATTRIBUTE_CATEGORY
, CON.ATTRIBUTE1
, CON.ATTRIBUTE2
, CON.ATTRIBUTE3
, CON.ATTRIBUTE4
, CON.ATTRIBUTE5
, CON.ATTRIBUTE6
, CON.ATTRIBUTE7
, CON.ATTRIBUTE8
, CON.ATTRIBUTE9
, CON.ATTRIBUTE10
, CON.ATTRIBUTE11
, CON.ATTRIBUTE12
, CON.ATTRIBUTE13
, CON.ATTRIBUTE14
, CON.ATTRIBUTE15
, CON.ATTRIBUTE16
, CON.ATTRIBUTE17
, CON.ATTRIBUTE18
, CON.ATTRIBUTE19
, CON.ATTRIBUTE20
, GREATEST(CON.EFFECTIVE_START_DATE
, PER.EFFECTIVE_START_DATE)
, LEAST(CON.EFFECTIVE_END_DATE
, PER.EFFECTIVE_END_DATE)
, CON.OBJECT_VERSION_NUMBER
, ASG.ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER
, RES.INDICATOR
, FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS('PER_CONTRACTS'
, CON.CONTRACT_ID
, NULL
, NULL
, NULL
, NULL
, 'PERWSCTR'
, 'O')
, ASG.PEOPLE_GROUP_ID
FROM PER_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASG
, PER_PERSON_TYPES PPT
, PER_PERIODS_OF_SERVICE PPS
, FND_SESSIONS SES
, (SELECT 'Y' INDICATOR
FROM DUAL UNION ALL SELECT 'N' INDICATOR
FROM DUAL) RES
, PER_CONTRACTS_F CON
WHERE CON.PERSON_ID (+) = PER.PERSON_ID
AND PPS.PERSON_ID = PER.PERSON_ID
AND ASG.PERSON_ID = PER.PERSON_ID
AND PPT.PERSON_TYPE_ID = PER.PERSON_TYPE_ID
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN NVL(CON.EFFECTIVE_START_DATE
, PER.EFFECTIVE_START_DATE)
AND NVL(CON.EFFECTIVE_END_DATE
, PER.EFFECTIVE_END_DATE)
AND SES.EFFECTIVE_DATE BETWEEN PPS.DATE_START
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, HR_GENERAL.END_OF_TIME)

Columns