Project Details - Auditor Query - FUSION

SELECT DISTINCT PPA.project_status_code
      ,HOU.name organization_name
      ,PPA.description     
      ,OCT.name contract_type
      ,PPT.project_type
      ,PPA.segment1 project_number
      ,PPA.name project_name
  ,TO_CHAR(PPA.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') start_date
      ,TO_CHAR(PPA.completion_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') completion_date
  ,TO_CHAR(PPA.scheduled_finish_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') closed_date
      ,OCH.contract_number
      ,OCH.estimated_amount contract_amount
      ,(SELECT SUM(PRD.cont_curr_revenue_amt) cont_curr_revenue_amt
          FROM pjb_rev_distributions PRD
         WHERE PRD.contract_id = OCH.id
   AND PRD.major_version = OCH.major_version) revenue_amount
      ,(SELECT DISTINCT ppn.full_name
          FROM pjf_project_parties proj_dir
              ,pjf_proj_role_types_tl prt
              ,per_all_assignments_f paf
              ,per_person_names_f ppn
         WHERE proj_dir.project_id = PPA.project_id
           AND proj_dir.project_role_id = prt.project_role_id
           AND prt.PROJECT_ROLE_NAME ='Project Director'
           AND proj_dir.resource_source_id = paf.person_id                                 
           AND paf.ASSIGNMENT_STATUS_TYPE='ACTIVE'
   AND paf.person_id = ppn.person_id     
           AND ppn.name_type = 'GLOBAL'
           AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND NVL (paf.effective_end_date, SYSDATE+1)
           AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active AND NVL(proj_dir.end_date_active, SYSDATE+1)
   AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND NVL (ppn.effective_end_date, SYSDATE+1)
           AND paf.primary_flag = 'Y'
   AND ROWNUM < 2) Director
               ,(SELECT DISTINCT ppn.full_name
          FROM pjf_project_parties proj_dir
              ,pjf_proj_role_types_tl prt
              ,per_all_assignments_f paf
              ,per_person_names_f ppn
         WHERE proj_dir.project_id = PPA.project_id
           AND proj_dir.project_role_id = prt.project_role_id
           AND prt.PROJECT_ROLE_NAME ='Project Manager'
           AND proj_dir.resource_source_id = paf.person_id                                 
           AND paf.ASSIGNMENT_STATUS_TYPE='ACTIVE'
   AND paf.person_id = ppn.person_id                                 
           AND ppn.name_type = 'GLOBAL'
           AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND NVL (paf.effective_end_date, SYSDATE+1)
           AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active AND NVL(proj_dir.end_date_active, SYSDATE+1)
   AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND NVL (ppn.effective_end_date, SYSDATE+1)
   AND paf.primary_flag = 'Y'
   AND ROWNUM < 2
         ) Manager
  FROM pjf_projects_all_vl PPA
      ,pjb_cntrct_proj_links PCPL
      ,okc_k_headers_all_b OCH
      ,hr_all_organization_units HOU
      ,okc_contract_types_vl OCT
      ,pjf_project_types_vl PPT
 WHERE PPA.project_id = PCPL.project_id (+)
   AND OCH.id = PCPL.contract_id
   AND PPA.carrying_out_organization_id = HOU.organization_id
   AND OCH.contract_type_id = OCT.contract_type_id
   AND PPA.project_type_id = PPT.project_type_id
   AND pcpl.major_version=OCH.major_version