select
per.employee_number "Employee Number",
--
per.full_name "Full Name",
con.reference "Contract Reference",
asg.assignment_number "Assignment Number",
hr_person_type_usage_info.get_user_person_type (sysdate,per.person_id) "Person Type",
per.applicant_number "Applicant Number",
pps.date_start "Hire Date",
substr (hr_general.decode_lookup('CONTRACT_TYPE', con.type), 1, 80) "Contract Type",
substr (hr_general.decode_lookup('CONTRACT_STATUS', con.status),1,80) "Contract Status",
substr ( hr_general.decode_lookup('CONTRACT_STATUS_REASON',con.status_reason),1,80) "Contract Status Reason",
con.description "Contract Description",
con.duration "Duration",
substr ( hr_general.decode_lookup ('QUALIFYING_UNITS',con.duration_units), 1,80) "Duration Units",
con.contractual_job_title "Contractual Job Title",
con.parties "Parties",
substr (hr_general.decode_lookup ('CONTRACT_START_REASON',con.start_reason),1,80) "Start Reason",
substr (hr_general.decode_lookup ('CONTRACT_END_REASON',con.end_reason),1,80) "End Reason",
con.number_of_extensions "Number of Extensions",
con.extension_reason "Extension Reason",
con.extension_period "Extension Period",
substr (hr_general.decode_lookup ('QUALIFYING_UNITS',con.extension_period_units),1,80) "Extension Period Units",
greatest (con.effective_start_date, per.effective_start_date) "Effective Start Date",
least (con.effective_end_date, per.effective_end_date) "Effective End Date",
substr( hr_general.decode_lookup ('DOCUMENT_STATUS',con.doc_status),1,80) "Document Status",
con.doc_status_change_date "Date of Change",
fnd_attachment_util_pkg.get_atchmt_exists ('PER_CONTRACTS',
con.contract_id,null,null,null,null,
'PERWSCTR','O') "Attachment Exists",
--
asg.people_group_id,
per.business_group_id,
per.person_id
from
per_people_f per,
per_assignments_f asg,
per_person_types ppt,
per_periods_of_service pps,
per_contracts_f con,
per_business_groups bg
where
con.person_id(+) = per.person_id
and pps.person_id = per.person_id
and asg.person_id = per.person_id
and asg.assignment_type in ('E','C')
and ppt.person_type_id = per.person_type_id
and sysdate between asg.effective_start_date and asg.effective_end_date
and sysdate between per.effective_start_date and per.effective_end_date
and per.business_group_id = bg.business_group_id
--
and bg.name = 'Vision Corporation'
and per.employee_number='388'
order by
1,2,3,4;
No comments:
Post a Comment