Project Details Query

=======================     Project Details Query     ===========================

SELECT ppav.segment1                                        Project_Number,
       --PPEV.PROJECT_UNIT_NAME,
       PPAT.name                                            PROJECT_NAME,
       ppav.project_id,
       ppat.description                                     project_description,
       ppav.project_status_code,
       ppav.attribute3                                      ProjectPhase,
       To_char(ppav.attribute1_date, 'yyyy/mm/dd')          ModulesonRoofM1,
       To_char(ppav.attribute3_date, 'yyyy/mm/dd')          ESOM2,
       ppav.attribute2_number                               SystemSizeDCWattage,
       ppav.attribute2                                      TEFund_Value,
       To_char(ppav.start_date, 'yyyy/mm/dd')               project_start_date,
       To_char(ppav.completion_date, 'yyyy/mm/dd')          project_end_date,
       (SELECT hou.name
        FROM   hr_organization_units_f_tl hou
        WHERE  organization_id = ppav.project_unit_id
               AND ROWNUM = 1)                              project_organization
       ,
       -- pv.task_Number,
       PMGR.personname                                      Project_Manager_Name
       ,
       PMGR.person_number
       Project_Manager_Number,
       (SELECT b.name
        FROM   pjf_projects_all_vl b
        WHERE  b.project_id = ppav.created_from_project_id)
       SOURCE_TEMPLATE_NUMBER,
       (SELECT b.name
        FROM   pjf_projects_all_vl b
        WHERE  b.project_id = ppav.created_from_project_id) SOURCE_TEMPLATE_NAME
FROM   pjf_projects_all_b ppav,
       pjf_projects_all_tl ppat,
       (SELECT DISTINCT ppav.project_id,
                        ppav.name              projectname,
                        ppav.segment1          projectnumber,
                        'Y'                    report_layout,
                        ppnf.person_id         personid,
                        ppp.project_party_id   teammemberid,
                        ppp.start_date_active  startdate,
                        ppp.end_date_active    finishdate,
                        peremail.email_address personemail,
                        ppnf.display_name      personname,
                        pprt.name              projectrole,
                        PAP.person_number
        FROM   pjf_projects_all_vl ppav,
               pjf_project_parties ppp,
               pjf_project_statuses_b pps,
               per_users pu,
               pjt_project_roles_vl pprt,
               per_people_x ppx,
               per_all_people_f pap,
               per_person_names_f ppnf,
               (SELECT Max(email_address) email_address,
                       person_id
                FROM   per_email_addresses
                WHERE  1 = 1
                       AND Trunc(SYSDATE) BETWEEN Nvl(date_from, SYSDATE) AND
                                                  Nvl(date_to, SYSDATE)
                       AND email_type = 'W1'
                GROUP  BY person_id) peremail
        WHERE  1 = 1
               AND ppav.project_status_code = pps.project_status_code
               AND ( Trunc(SYSDATE) BETWEEN Nvl(ppp.start_date_active, SYSDATE)
                                            AND
                     Nvl(ppp.end_date_active, SYSDATE)
                      OR Trunc(SYSDATE) <= Nvl(ppp.start_date_active, SYSDATE) )
               AND ppav.project_id = ppp.project_id
               AND pu.person_id = ppp.resource_source_id
               AND ppp.project_role_id = pprt.project_role_id
               AND ppx.person_id = pu.person_id
               AND PAP.person_id = PPNF.person_id
               AND ppx.person_id = peremail.person_id
               AND pprt.name = 'Project Manager'
               AND ppnf.person_id = ppx.person_id
               AND ppnf.name_type = 'GLOBAL'
               AND Trunc(SYSDATE) BETWEEN Trunc(ppnf.effective_start_date) AND
                                          Trunc(ppnf.effective_end_date)
               AND ppav.template_flag = 'N')PMGR
WHERE  1 = 1
       AND ppat.LANGUAGE = 'US'
       AND ppav.project_id = ppat.project_id
       AND PPAV.project_id = PMGR.project_id
       AND Trunc(ppav.creation_date) = Nvl(:p_creation_date,
                                       Trunc(ppav.creation_date))