Query to Get Oracle Projects Revenue and Billing Details

  SELECT ppc.class_code Company,

         haou.name Organization,

         ppa.project_type Project_Type,

         papf.full_Name Project_Manager,

         ppa.segment1 Project_Number,

         ppa.long_name Project_Name,

--         rc.customer_name Customer_Name,

         paa.agreement_num Agrement_Number,

         ppa.start_date Project_Start_Date,

         ppa.completion_date Project_End_Date,

         pps.project_status_name Status,

         paa.amount Funding,

         pe.event_type Event_Type,

         pe.event_num Event_Number,

         pt.task_name Task_Name,

         pe.completion_date Event_Date,

         pe.description Description,

         DECODE (pe.revenue_distributed_flag, 'Y', 0, pe.bill_trans_rev_amount) Revenue_Amount,

         DECODE (pe.billed_flag, 'Y', 0, pe.bill_trans_bill_amount) Bill_Amt

    FROM pa_project_classes ppc,

         pa_project_types_all ppta,

         hr_all_organization_units haou,

         pa_projects_all ppa,

         per_all_people_f papf,

--         ra_customers rc,

         pa_agreements_all paa,

         pa_project_statuses pps,

         pa_project_players ppp,

         pa_project_role_types pprt,

         pa_project_fundings ppf,

         pa_events pe,

         pa_tasks pt

   WHERE     ppa.project_id = ppc.project_id

         AND UPPER (ppta.project_type_class_code) = 'CONTRACT'

         AND UPPER (ppc.class_category) = 'SHIPPING ACCOUNT'

         AND pps.project_status_code = ppa.project_status_code

         AND ppa.CARRYING_OUT_ORGANIZATION_ID = haou.organization_id

         AND papf.person_id = ppp.person_id

         AND ppp.project_role_type = pprt.project_role_type

         AND UPPER (pprt.project_role_type) = 'PROJECT MANAGER'

         AND ppp.project_id = ppa.project_id

         AND ppa.project_id = ppf.project_id

         AND ppf.agreement_id = paa.agreement_id

--         AND paa.customer_id = rc.customer_id

         AND ppa.project_id = pe.project_id

         AND pe.task_id = pt.task_id(+)

         AND ppta.project_type = ppa.project_type

         AND ppa.org_id = ppta.org_id

--         AND UPPER (ppc.class_code) = UPPER (NVL (:p_company, ppc.class_code))

--         AND UPPER (haou.name) = UPPER (NVL (:p_organization, haou.name))

--         AND UPPER (ppa.project_type) = UPPER (NVL (:p_project_type, ppa.project_type))

--         AND UPPER (papf.full_name) = UPPER (NVL (:p_project_manager, papf.full_name))

--         AND pe.completion_date BETWEEN DECODE (:p_date_from,NULL, pe.completion_date,:p_date_from) AND DECODE (:p_date_to,NULL, pe.completion_date,:p_date_to)

         AND ( (pe.billed_flag = 'N' AND pe.revenue_distributed_flag = 'N')

              OR (    pe.billed_flag = 'Y'

                  AND pe.revenue_distributed_flag = 'N'

                  AND pe.bill_trans_rev_amount <> 0)

              OR (    pe.billed_flag = 'N'

                  AND pe.revenue_distributed_flag = 'Y'

                  AND pe.BILL_trans_bill_AMOUNT <> 0))

           AND ppa.segment1 = 'XX399-861'

GROUP BY ppa.project_id,

         ppc.class_code,

         haou.name,

         ppa.project_type,

         papf.full_Name,

         ppa.segment1,

         ppa.long_name,

--         rc.customer_name,

         paa.agreement_num,

         ppa.start_date,

         ppa.completion_date,

         pps.project_status_name,

         paa.amount,

         pe.event_type,

         pe.event_num,

         pt.task_name,

         pe.completion_date,

         pe.description,

         DECODE (pe.revenue_distributed_flag,'Y', 0,pe.bill_trans_rev_amount),

         DECODE (pe.billed_flag, 'Y', 0, pe.bill_trans_bill_amount)

ORDER BY ppc.class_code,

         haou.name,

         ppa.project_type,

         ppa.segment1,

         pe.event_num