Project Parties (Project Key Members) query

/* Formatted on 10/3/2016 7:57:57 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   (SELECT   DISTINCT
                   PPP.PROJECT_ID project_id,
                   DECODE (PA.ASSIGNMENT_ID,
                           NULL, PPRT.MEANING,
                           PA.ASSIGNMENT_NAME)
                      project_role_meaning,
                   PPP.RESOURCE_SOURCE_ID resource_source_id,
                   PE.FULL_NAME resource_source_name,
                   PPP.PROJECT_ROLE_ID project_role_id,
                   PPRT.PROJECT_ROLE_TYPE project_role_type,
                   PPP.START_DATE_ACTIVE start_date_active,
                   ppp.end_date_active end_date_active,
                   pa_project_parties_utils.active_party (
                      ppp.start_date_active,
                      ppp.end_date_active
                   )
                      active,
                   'EMPLOYEE' party_type
            FROM   PA_PROJECT_PARTIES PPP,
                   PA_PROJECTS_ALL PPA,
                   PA_PROJECT_ROLE_TYPES PPRT,
                   PER_ALL_PEOPLE_F PE,
                   PA_PROJECT_ASSIGNMENTS PA,
                   fnd_user u,
                   (SELECT   pj.name job_name,
                             haou.organization_id org_id,
                             haou.name org_name,
                             paf.person_id,
                             paf.assignment_type
                      FROM   per_all_assignments_f paf,
                             per_jobs pj,
                             hr_all_organization_units haou
                     WHERE   TRUNC (SYSDATE) BETWEEN TRUNC(paf.effective_start_date)
                                                 AND  TRUNC(paf.effective_end_date)
                             AND paf.primary_flag = 'Y'
                             AND paf.organization_id = haou.organization_id
                             AND NVL (paf.job_id, -99) = pj.job_id(+)) prd
           WHERE       PPP.RESOURCE_TYPE_ID = 101
                   AND PPP.PROJECT_ID = PPA.PROJECT_ID
                   AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
                   AND PPP.RESOURCE_SOURCE_ID = PE.PERSON_ID
                   AND PE.EFFECTIVE_START_DATE =
                         (SELECT   MIN (PAPF.EFFECTIVE_START_DATE)
                            FROM   PER_ALL_PEOPLE_F PAPF
                           WHERE   PAPF.PERSON_ID = PE.PERSON_ID
                                   AND PAPF.EFFECTIVE_END_DATE >=
                                         TRUNC (SYSDATE))
                   AND PE.EFFECTIVE_END_DATE >= TRUNC (SYSDATE)
                   AND PPP.PROJECT_PARTY_ID = PA.PROJECT_PARTY_ID(+)
                   AND NVL (prd.assignment_type, '-99') IN
                            ('C',
                             DECODE (
                                DECODE (
                                   PE.CURRENT_EMPLOYEE_FLAG,
                                   'Y',
                                   'Y',
                                   DECODE (PE.CURRENT_NPW_FLAG,
                                           'Y', 'Y',
                                           'N')
                                ),
                                'Y',
                                'E',
                                'B'
                             ),
                             'E',
                             '-99')
                   AND ppp.resource_source_id = prd.person_id(+)
                   AND u.employee_id(+) = ppp.resource_source_id
                   AND ppp.object_type = 'PA_PROJECTS'
                   AND ppp.object_id = ppa.project_id
          UNION ALL
          SELECT   DISTINCT
                   ppp.project_id,
                   pprt.meaning,
                   ppp.resource_source_id,
                   hzp.party_name,
                   ppp.project_role_id,
                   pprt.project_role_type,
                   ppp.start_date_active,
                   ppp.end_date_active,
                   pa_project_parties_utils.active_party (
                      ppp.start_date_active,
                      ppp.end_date_active
                   ),
                   'PERSON'
            FROM   pa_project_parties ppp,
                   pa_projects_all ppa,
                   pa_project_role_types pprt,
                   hz_parties hzp,
                   hz_parties hzo,
                   hz_relationships hzr,
                   hz_contact_points hzcp,
                   fnd_user u
           WHERE       ppp.resource_type_id = 112
                   AND ppp.project_id = ppa.project_id
                   AND ppp.project_role_id = pprt.project_role_id
                   AND ppp.resource_source_id = hzp.party_id
                   AND hzp.party_type = 'PERSON'
                   AND hzo.party_type = 'ORGANIZATION'
                   AND hzr.relationship_code IN ('EMPLOYEE_OF', 'CONTACT_OF')
                   AND hzr.status = 'A'
                   AND hzr.subject_id = hzp.party_id
                   AND hzr.object_id = hzo.party_id
                   AND hzr.object_table_name = 'HZ_PARTIES'
                   AND hzr.directional_flag = 'F'
                   AND hzcp.owner_table_name(+) = 'HZ_PARTIES'
                   AND hzcp.owner_table_id(+) = hzp.party_id
                   AND hzcp.contact_point_type(+) = 'PHONE'
                   AND hzcp.phone_line_type(+) = 'GEN'
                   AND hzcp.primary_flag(+) = 'Y'
                   AND u.person_party_id(+) = ppp.resource_source_id
                   AND ppp.object_type = 'PA_PROJECTS'
                   AND ppp.object_id = ppa.project_id
          UNION ALL
          SELECT   DISTINCT
                   ppp.project_id,
                   pprt.meaning,
                   ppp.resource_source_id,
                   hzo.party_name,
                   ppp.project_role_id,
                   pprt.project_role_type,
                   ppp.start_date_active,
                   ppp.end_date_active,
                   pa_project_parties_utils.active_party (
                      ppp.start_date_active,
                      ppp.end_date_active
                   ),
                   'ORGANIZATION'
            FROM   pa_project_parties ppp,
                   pa_projects_all ppa,
                   pa_project_role_types_vl pprt,
                   hz_parties hzo,
                   hz_contact_points hzcp
           WHERE       ppp.resource_type_id = 112
                   AND ppp.project_id = ppa.project_id
                   AND ppp.project_role_id = pprt.project_role_id
                   AND ppp.resource_source_id = hzo.party_id
                   AND hzo.party_type = 'ORGANIZATION'
                   AND hzcp.owner_table_name(+) = 'HZ_PARTIES'
                   AND hzcp.owner_table_id(+) = hzo.party_id
                   AND hzcp.contact_point_type(+) = 'PHONE'
                   AND hzcp.phone_line_type(+) = 'GEN'
                   AND hzcp.primary_flag(+) = 'Y'
                   AND ppp.object_type = 'PA_PROJECTS'
                   AND ppp.object_id = ppa.project_id
          UNION ALL
          SELECT   ppc.project_id,
                   'Customer Person' meaning,
                   NULL,
                   ppc.customer_name,
                   NULL,
                   NULL,
                   NULL,
                   NULL,
                   DECODE (ppc.customer_status,
                           'A',
                           'Y',
                           'I',
                           'N'),
                   'ORGANIZATION' party_type
            FROM   pa_project_customers_v ppc,
                   hz_parties hzo,
                   hz_contact_points hzcp
           WHERE       hzcp.owner_table_name(+) = 'HZ_PARTIES'
                   AND hzcp.owner_table_id(+) = hzo.party_id
                   AND hzcp.contact_point_type(+) = 'PHONE'
                   AND hzcp.phone_line_type(+) = 'GEN'
                   AND hzcp.primary_flag(+) = 'Y'
                   AND ppc.party_type = 'PERSON'
                   AND ppc.party_id = hzo.party_id)
 WHERE   (project_id = :p_project_id AND party_type <> 'ORGANIZATION'
          AND TRUNC (SYSDATE) BETWEEN start_date_active
                                  AND  NVL (end_date_active, TRUNC (SYSDATE)));