Query: Contract Details

select
    ppf.employee_number                                              "Employee Number",
    --
    pcf1.reference                                                                  "Reference",
    hr_contract_api.get_meaning (pcf1.type, 'CONTRACT_TYPE')                        "Type",
    hr_contract_api.get_meaning (pcf1.status, 'CONTRACT_STATUS')                    "Status",
    hr_contract_api.get_meaning (pcf1.status_reason, 'CONTRACT_STATUS_REASON')      "Status Reason",
    pcf1.description                                                                "Description",
    hr_contract_api.get_meaning (pcf1.doc_status,'DOCUMENT_STATUS')                 "Document Status",
    doc_status_change_date                                                          "Date of Change",
    hr_contract_api.get_active_start_date(pcf1.contract_id,sysdate,pcf1.status)     "Active Dates:Start",
    hr_contract_api.get_active_end_date(pcf1.contract_id,sysdate,pcf1.status)       "Active Dates: End",
    hr_contract_api.get_pps_start_date(pcf1.person_id,
                hr_contract_api.get_active_start_date(pcf1.contract_id,
                        sysdate,pcf1.status))                                       "Per of Ser Dates:From",
    hr_contract_api.get_pps_end_date ( pcf1.person_id,
                hr_contract_api.get_active_start_date(pcf1.contract_id,
                        sysdate,pcf1.status))                                       "Per of Ser Dates:To",  
    pcf1.duration                                                                   "Duration",
    hr_contract_api.get_meaning (pcf1.duration_units,'QUALIFYING_UNITS')            "Units",
    hr_contract_api.get_meaning (pcf1.start_reason,'CONTRACT_START_REASON')         "Start Reason",
    hr_contract_api.get_meaning (pcf1.end_reason,'CONTRACT_END_REASON')             "End Reason",
    pcf1.contractual_job_title                                                      "Contractual Job",
    pcf1.parties                                                                    "Parties",
    pcf1.extension_period                                                           "Extension Period",
    hr_contract_api.get_meaning (pcf1.extension_period_units,'QUALIFYING_UNITS')    "Extension Period Units",
    pcf1.extension_reason                                                           "Extension Reason",
    pcf1.number_of_extensions                                                       "Number of Extensions",
    pcf1.effective_start_date                                                       "Effective Dates : From",
    pcf1.effective_end_date                                                         "Effective Dates : TO",
    --
    pcf1.contract_id,
    pcf1.person_id,
    pcf1.business_group_id
from
    per_contracts_f pcf1,
    per_people_f ppf,
    per_business_groups bg
where
    1=1
and pcf1.person_id = ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and ppf.business_group_id = bg.business_group_id
--
and bg.name = 'Vision Corporation'
and ppf.employee_number='388'
order by
    1,2,3;