Documents of Records DOR Query

SELECT papf.person_number
      , (SELECT documents_of_record_id
         FROM   hr_documents_of_record hrd
         WHERE  hrd.person_id = papf.person_id
                AND ROWNUM = 1)
          documents_of_record_id
           , (SELECT DEI_ATTRIBUTE4
         FROM   hr_documents_of_record hrd
         WHERE  hrd.person_id = papf.person_id
                AND ROWNUM = 1)
          self_dependant
      , (SELECT hdr.dei_attribute1 uid_number
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE  ROWNUM = 1
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id)
                AND hdr.document_type_id = hdt.document_type_id
                AND hdr.person_id = papf.person_id
                AND hdt.language = 'US'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.dei_attribute_category = 'Update National Identifier')
          uid_number
      , (SELECT hdr.dei_attribute2
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          eid_number
      , (SELECT TO_CHAR (hdr.dei_attribute_date1
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
                   eid_expiry
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          eid_expiry
      , (SELECT hdr.dei_attribute3 mol_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          mol_num
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          mol_expiry
      , (SELECT hdr.dei_attribute3 visa_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_num
      , (SELECT TO_CHAR (hdr.dei_attribute_date1
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
                   visa_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_issue_date
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_expiry_date
      , (SELECT hdr.dei_attribute6 sponsor_name
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          sponsor_name
      , (SELECT hdr.dei_attribute7
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passprot_number
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passport_issue_date
      , (SELECT TO_CHAR (hdr.dei_attribute_date3
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passport_expiry_date
      , (SELECT hdr.dei_attribute6
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passprot_country
FROM   per_all_people_f papf
WHERE  TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date