Document of Record in Oracle HRMS Query

SELECT
    per.employee_number,
    hdt.category_code,
    hdt.system_document_type,
    dei.document_number,
    date_from,
    date_to,
    issued_date,
    dei_information_category,
    dei_information1,
    dei_information1,
    dei_information2,
    dei_information3,
    dei_information4,
    dei_information5,
    dei_information6,
    dei_information7
FROM
    hr_document_extra_info dei,
    per_all_people_f per,
    hr_document_types hdt
WHERE
    per.person_id = dei.person_id
    AND   per.business_group_id = 81
    AND   per.employee_number =:p_employee_number
    AND   per.current_employee_flag = 'Y'
    AND   trunc(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
    AND   hdt.document_type_id = dei.document_type_id




Quey With Attachement Deatils



SELECT  per.employee_number, hdt.category_code , DBMS_LOB.getlength (fl.file_data) file_length,
             fl.file_data,
             fl.file_name
        FROM hr_document_extra_info dei,
             per_all_people_f per,
             hr_document_types hdt,
             fnd_attached_documents fat,
             fnd_documents fd,
             fnd_lobs fl
       WHERE     per.person_id = dei.person_id
             AND per.business_group_id = 81
              --     AND per.employee_number =  :p_employee_number
             AND per.current_employee_flag = 'Y'
             AND TRUNC (SYSDATE) BETWEEN per.effective_start_date
                                     AND per.effective_end_date
             AND hdt.document_type_id = dei.document_type_id
             AND fat.entity_name = 'R_DOCUMENT_EXTRA_INFO'
             AND pk1_value = dei.document_extra_info_id
             AND fd.document_id = fat.document_id
             AND fl.file_id = fd.media_id