Search This Blog

Saturday, November 25, 2017

Contact i.e. Dependents Query

SELECT papf.person_number
      ,papf.person_id
       /* ,hdor.documents_of_record_id
       ,hdor.dei_attribute4 self_or_dependent
       ,hdor.dei_attribute8 confirm_colleague_id*/
       --,hdor.dei_attribute6 dependent_number
       --,hdor.dei_attribute1 uid_number
      -- ,hdor.dei_attribute2 eid_number
      -- ,hdor.dei_attribute3 mol_number
   ,pcrf.CONT_ATTRIBUTE5  authorised_dependent
   ,pcrf.CONT_ATTRIBUTE2  eligible_for_idcard
   ,pcrf.CONT_ATTRIBUTE1  eligible_for_medicalcard
   ,papfc.person_number dependent_number
   ,pcrf.EMERGENCY_CONTACT_FLAG
,      ppnfc.first_name
      ,ppnfc.last_name
       ,hr_general.decode_lookup ('CONTACT'
                             ,pcrf.contact_type) relationship
   ,TO_CHAR (pcrf.effective_start_date
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') effective_start_date
   ,pc.legislation_code nationality
      ,ppnfc.title
      ,pplf.sex gender
      ,TO_CHAR (pp.date_of_birth
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') date_of_birth
      /* ,TO_CHAR (hdor.dei_attribute_date2
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') mol_expiry_date
       ,TO_CHAR (hdor.dei_attribute_date1
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') eid_expiry_date
      ,NULL visa_number
      ,NULL visa_expiry_date
      ,NULL passport_number
      ,NULL passport_expiry_date
     
     
      ,NULL Passport_issue_date
      ,NULL passport_issue_country
      ,NULL Visa_issue_date
      ,NULL Visa_type
      ,NULL sponser_type
      ,NULL Sponser_name
      ,NULL Visa_permit_status */
/*-----------------------------------National Identifier Details----------------------*/
,(SELECT TO_CHAR (hdor.dei_attribute_date1
                       ,'YYYY/MM/DD'
                       ,'nls_date_language=AMERICAN')
        FROM   hr_documents_of_record hdor
              ,hr_document_types_tl hdtt
        WHERE  papf.person_id = hdor.person_id
        AND    hdor.document_type_id = hdtt.document_type_id
        AND    hdtt.LANGUAGE = 'US'
        AND    hdtt.document_type = 'Update National Identifier'
        AND    TRIM (hdor.dei_attribute4) = 'Dependent'
        AND    papfc.person_number = hdor.dei_attribute6) eid_expiry_date
      , (SELECT TO_CHAR (hdor.dei_attribute_date2
                        ,'YYYY/MM/DD'
                        ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update National Identifier'
         AND    TRIM (hdor.dei_attribute4) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute6) mol_expiry_date
      , (SELECT hdor.dei_attribute2
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update National Identifier'
         AND    TRIM (hdor.dei_attribute4) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute6) eid_number
      , (SELECT hdor.dei_attribute3
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update National Identifier'
         AND    TRIM (hdor.dei_attribute4) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute6) mol_number
      /*------------------- Visa Details ---------------------*/
  ,      (SELECT hdor.dei_attribute3
        FROM   hr_documents_of_record hdor
              ,hr_document_types_tl hdtt
        WHERE  papf.person_id = hdor.person_id
        AND    hdor.document_type_id = hdtt.document_type_id
        AND    hdtt.LANGUAGE = 'US'
        AND    hdtt.document_type LIKE 'Update Visa De%'
        AND    TRIM (hdor.dei_attribute1) = 'Dependent'
        AND    papfc.person_number = hdor.dei_attribute2) visa_number
      , (SELECT TO_CHAR (hdor.dei_attribute_date2
                        ,'YYYY/MM/DD'
                        ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) visa_expiry_date
       , (SELECT hdor.dei_attribute4
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) visa_type
      , (SELECT hdor.dei_attribute5
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) sponser_type
      , (SELECT hdor.dei_attribute6
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) sponser_name
      , (SELECT hdor.dei_attribute7
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) visa_permit_status
  
/*----------------------------------------Passport Details-----------------------*/
,(SELECT hdor.dei_attribute7
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2) passport_number
,(SELECT TO_CHAR (hdor.dei_attribute_date3
                 ,'YYYY/MM/DD'
                 ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2) passport_expiry_date
,(SELECT TO_CHAR (hdor.DEI_ATTRIBUTE_DATE2
                 ,'YYYY/MM/DD'
                 ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2)  passport_issue_date
,(SELECT hdor.DEI_ATTRIBUTE6
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2) passport_issue_country
        
/*---------------------------------END of sub queries---------------------------*/        
        
        
FROM   per_person_names_f ppnf
      ,per_all_people_f papf
      ,per_person_names_f ppnfc
      ,per_all_people_f papfc
      ,per_contact_relships_f pcrf
      ,per_citizenships pc
      ,per_people_legislative_f pplf
      ,per_persons pp
WHERE  pp.person_id(+) = papfc.person_id
AND    pplf.person_id = papfc.person_id
AND    pc.person_id = papfc.person_id
AND    TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date
                           AND ppnf.effective_end_date
AND    ppnf.name_type = 'GLOBAL'
AND    ppnf.person_id = papf.person_id
AND    pcrf.contact_person_id = papfc.person_id
AND    pcrf.person_id = papf.person_id
AND    TRUNC (SYSDATE) BETWEEN pcrf.effective_start_date
                           AND pcrf.effective_end_date
AND    TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date
AND    TRUNC (SYSDATE) BETWEEN ppnfc.effective_start_date
                           AND ppnfc.effective_end_date
AND    ppnfc.name_type = 'GLOBAL'
AND    ppnfc.person_id = papfc.person_id
AND    TRUNC (SYSDATE) BETWEEN papfc.effective_start_date
                           AND papfc.effective_end_date
AND    TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date
ORDER BY papfc.person_number

No comments:

Post a Comment