Query to Get Oracle Projects Revenue and Billing Details

-->Summary

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,

    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 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 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

-->Detail   

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 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 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 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

    ,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