Oracle EBS Projects Revenue and Billing Details Query

 SELECT 
    haou.name Organization,
   /* (select class_code 
      from(select project_id,class_code, dense_rank() over(order by class_code desc)r 
           from pa_project_classes ppc
           where class_category = 'Eka Product'
            and ppc.project_id = ppa.project_id
          ) where r=1
    ) Acc_num,*/
    ppa.project_type Project_Type,
    papf.full_Name Project_Manager,
    ppa.project_id,
    ppa.segment1 Project_Number,
    ppa.long_name Project_Name,
    paa.agreement_num Agrement_Number,
    TO_CHAR (ppa.start_date, 'DD-MON-YY') Project_Start_Date,
    TO_CHAR (ppa.completion_date, 'DD-MON-YY') Project_End_Date,
    pps.project_status_name Status,
    NVL ((SELECT SUM (NVL (allocated_amount, 0))
          FROM pa_project_fundings
         WHERE project_id = ppa.project_id
          AND agreement_id = paa.agreement_id),0
    ) Funding,
    SUM (NVL (projfunc_revenue_amount, 0)) Revenue_Recorded_To_Date,
    SUM (NVL (projfunc_bill_amount, 0)) Amount_Invoiced_To_Date,
    NVL (((SELECT SUM (NVL (allocated_amount, 0))
            FROM pa_project_fundings
           WHERE project_id = ppa.project_id
            AND agreement_id = paa.agreement_id)
        - SUM (NVL (projfunc_revenue_amount, 0))),0
    ) Balance_Rev_To_Record,
    NVL (((SELECT SUM (NVL (allocated_amount, 0))
            FROM pa_project_fundings
           WHERE project_id = ppa.project_id
                 AND agreement_id = paa.agreement_id)
        - SUM (NVL (projfunc_bill_amount, 0))),0
    ) Amount_Rem_To_Invoice
FROM 
    pa_project_types_all ppta,
    hr_all_organization_units haou,
    pa_projects_all ppa,
    per_all_people_f papf,
    PA_PROJECT_CUSTOMERS pc,
    pa_agreements_all paa,
    pa_project_statuses pps,
    (SELECT DISTINCT project_id, person_id
       FROM pa_project_players
      WHERE project_role_type = UPPER ('PROJECT MANAGER')) ppp,
    (SELECT DISTINCT agreement_id, project_id FROM PA_PROJECT_FUNDINGS) pdia,
    pa_events pe
WHERE 1=1
    AND ppa.project_type = ppta.project_type
    AND ppta.org_id = ppa.org_id
    AND  ppa.org_id = 286
    AND UPPER (ppta.project_type_class_code) = 'CONTRACT'
    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_id = ppa.project_id
    AND ppa.project_id = pdia.project_id(+)
    AND pdia.agreement_id = paa.agreement_id(+)
    AND pc.project_id = ppa.project_id
    AND ppa.project_id = pe.project_id(+)
    AND pps.project_status_name <> 'Closed'
    -- AND ppa.project_id BETWEEN NVL (:p_from_project_id, ppa.project_id) AND NVL (:p_to_project_id, ppa.project_id)
    -- 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 UPPER (pps.project_status_name) = UPPER (NVL (:p_status, pps.project_status_name))
GROUP BY 
    haou.name,
    ppa.project_type,
    papf.full_Name,
    ppa.segment1,
    ppa.long_name,
    paa.agreement_num,
    ppa.start_date,
    ppa.completion_date,
    pps.project_status_name,
    ppa.project_id,
    paa.agreement_id
ORDER BY 
    haou.name,
    ppa.project_type,
    ppa.segment1
/
---------------------------------------------------------------1    --------------------------

SELECT 
    haou.name Organization
    /*,(select class_code 
      from(select project_id,class_code, dense_rank() over(order by class_code desc)r 
           from pa_project_classes 
           where class_category = XXAA Account Number' 
            and project_id = ppa.project_id
          ) where r=1
    ) Acc_num*/
    ,ppa.project_type Project_Type
    ,papf.full_Name Project_Manager
    ,ppa.project_id
    ,ppa.segment1 Project_Number
    ,ppa.long_name Project_Name
    ,paa.agreement_num Agrement_Number
    ,to_char(ppa.start_date,'DD-MON-YY') Project_Start_Date
    ,to_char(ppa.completion_date,'DD-MON-YY') Project_End_Date
    ,pps.project_status_name Status
    ,nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings 
     where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) Funding
    ,pe.event_type Event_Type
    ,pe.event_num Event_Number
    ,pt.task_name Task_Name
    ,to_char(pe.completion_date,'DD-MON-YY') Event_Date
    ,pe.description Description
    --,pe.revenue_amount Revenue_Amount
    ,pe.BILL_TRANS_REV_AMOUNT Revenue_Amount
    ,pe.revenue_distributed_flag Revenue_Distributed
    --,pe.bill_amount Bill_Amt
    ,pe.BILL_TRANS_BILL_AMOUNT Bill_Amt
    ,pe.billed_flag Billed
    ,nvl(pe.projfunc_revenue_amount,0) Revenue_Recorded_To_Date
    ,nvl(pe.projfunc_bill_amount,0) Amount_Invoiced_To_Date
    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(projfunc_revenue_amount),0) from pa_events where project_id = ppa.project_id)) Balance_of_revenue_to_record
    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) -(select nvl(sum(projfunc_bill_amount),0) from pa_events where project_id = ppa.project_id)) sum_amount_invoiced_to_date
    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(BILL_TRANS_REV_AMOUNT),0) from pa_events where project_id =  ppa.project_id))unscheduled_rev_amt
    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(BILL_TRANS_BILL_AMOUNT),0) from pa_events where project_id = ppa.project_id))unscheduled_bill_event
FROM 
    pa_project_types_all ppta
    ,hr_all_organization_units haou
    ,pa_projects_all ppa
    ,per_all_people_f  papf
    ,PA_PROJECT_CUSTOMERS pc
    ,pa_agreements_all paa
    ,pa_project_statuses pps
    ,(SELECT DISTINCT project_id, person_id FROM pa_project_players WHERE project_role_type = UPPER('PROJECT MANAGER')) ppp
    ,(SELECT DISTINCT agreement_id,project_id FROM PA_PROJECT_FUNDINGS)  pdia
    ,pa_events pe
    ,pa_tasks pt
WHERE 1=1    
    AND ppa.project_type = ppta.project_type
    AND ppta.org_id = ppa.org_id
    AND  ppa.org_id = 286
    AND upper(ppta.project_type_class_code) = 'CONTRACT' 
    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_id = ppa.project_id
    AND ppa.project_id = pdia.project_id(+)
    AND pdia.agreement_id  = paa.agreement_id(+)
    AND pc.project_id = ppa.project_id
    AND ppa.project_id = pe.project_id(+)
    AND pe.task_id = pt.task_id(+)
    AND pps.project_status_name <> 'Closed'
GROUP BY 
    haou.name 
    ,ppa.project_type 
    ,papf.full_Name 
    ,ppa.segment1 
    ,ppa.long_name 
    ,paa.agreement_num 
    ,ppa.start_date 
    ,ppa.completion_date 
    ,pps.project_status_name 
    ,pe.event_type 
    ,pe.event_num 
    ,pt.task_name 
    ,pe.completion_date 
    ,pe.description 
    --,pe.revenue_amount 
    ,pe.BILL_TRANS_REV_AMOUNT 
    ,pe.revenue_distributed_flag 
    --,pe.bill_amount 
    ,pe.BILL_TRANS_BILL_AMOUNT 
    ,pe.billed_flag 
    ,pe.projfunc_revenue_amount
    ,pe.projfunc_bill_amount
    ,ppa.project_id
    ,paa.agreement_id
ORDER BY 
    haou.name
    ,ppa.project_type 
    ,ppa.project_id
    ,pe.event_num