eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PER_CONTRACTS_V2

Source

View Text - Preformatted


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)

View Text - HTML Formatted

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)