Monday, May 14, 2018

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect