Project Accounting Queries

/* Formatted on 7/8/2014 9:44:50 AM (QP5 v5.115.810.9015) */
SELECT ppa1.project_id,
       ppa1.segment1 "ACCOUNT_VALUE",
       ppa2.segment1 "INDIAN PROJECTS"
FROM pa_tasks pt,
     pa_projects_all ppa1,
     pa_projects_all ppa2,
     pa_project_customers ppc,
     pa_project_types_all ppt
WHERE     pt.task_id = ppc.receiver_task_id
      AND pt.project_id = ppa1.project_id
      AND ppt.project_type = ppa2.project_type
      AND ppc.project_id = ppa2.project_id
      AND ppt.project_type_class_code = 'CONTRACT'
      AND ppa2.project_status_code = 'APPROVED'
      AND ppa1.template_flag = 'N'
UNION
SELECT ppa.project_id, ppa.segment1, ppa.segment1
FROM pa_projects_all ppa, pa_project_types_all ppt
WHERE     ppt.project_type = ppa.project_type
      AND ppt.project_type_class_code <> 'CONTRACT'
      AND ppa.template_flag = 'N'
UNION
SELECT ppa1.project_id,
       ppa1.segment1 "ACCOUNT_VALUE",
       ppa1.segment1 "INDIAN PROJECTS"
FROM pa_projects_all ppa1, pa_project_customers ppc, pa_project_types_all ppt
WHERE     ppt.project_type = ppa1.project_type
      AND ppc.project_id = ppa1.project_id
      AND ppt.project_type_class_code = 'CONTRACT'
      AND ppc.bill_another_project_flag = 'N'
      AND ppa1.project_status_code = 'APPROVED'
      AND ppa1.template_flag = 'N';
/

/* Formatted on 7/8/2014 9:45:04 AM (QP5 v5.115.810.9015) */
SELECT ppa.project_id,
       ppa.name "PROJECT NAME",
       ppa.long_name "PROJECT ALIAS",
       ppa.description "PROJECT DESCRIPTION",
       ppa.start_date "START DATE",
       ppa.completion_date "END DATE",
       prc.customer_name "PRIMARY CUSTOMER",
       prc1.customer_name "SECONDARY CUSTOMER",
       hou.name "BUSINESS UNIT",
       lob.class_code "LINE OF BUSINESS",
       pra.class_code "PRACTICE",
       prloc.class_code "PROGRAM LOCATION",
       so.class_code "SERVICE OFFERING",
       bm.class_code "BUSINESS MODEL",
       sb.class_code "SETUP BILLING",
       0 "PARENT PROJECT ID",
       ppa.segment1 "PROJECT NUMBER",
       '0' "TASK NUMBER",
       ppa.project_type "PROJECT TYPE",
       pm."PROJECT MANAGER",
       pgm."PROGRAM MANAGER",
       vpp."VICE PRESIDENT",
       prc.project_relationship_code "PARENT RELATIONSHIP"
FROM pa_projects_all ppa,
     hr_all_organization_units hou,
     pa_project_customers_v prc,
     disc_pa_sec_customers prc1,
     pa_project_classes_v lob,
     pa_project_classes_v pra,
     pa_project_classes_v prloc,
     pa_project_classes_v so,
     pa_project_classes_v sb,
     disc_pa_biz_model bm,
     --PA_PROJECT_CLASSES_V BIL,
     disc_pa_pm pm,
     disc_pa_pgm pgm,
     disc_pa_vpp vpp
WHERE     ppa.carrying_out_organization_id = hou.organization_id
      AND ppa.template_flag != 'Y'
      AND ppa.project_id = prc.project_id(+)
      AND ppa.project_id = prc1.project_id(+)
      AND ppa.project_id = lob.project_id(+)
      AND lob.class_category = 'LINE OF BUSINESS'
      AND ppa.project_id = pra.project_id(+)
      AND pra.class_category = 'PRACTICE'
      AND ppa.project_id = prloc.project_id(+)
      AND prloc.class_category = 'PROGRAM LOCATION'
      AND ppa.project_id = so.project_id(+)
      AND so.class_category = 'SERVICE OFFERING'
      AND ppa.project_id = sb.project_id(+)
      AND sb.class_category = 'SETUP BILLING'
      AND ppa.project_id = bm.project_id(+)
      --AND BM.CLASS_CATEGORY = 'BUSINESS MODEL'
      --AND PPA.PROJECT_ID = BIL.PROJECT_ID (+) AND BIL.CLASS_CATEGORY (+) =
      --'FINAL PROJECTS FOR GO LIVE' AND BIL.CLASS_CODE = 'YES'
      AND ppa.project_id = pm.project_id(+)
      -- AND UPPER (prm.ROLE) = 'PROJECT MANAGER'
      --AND UPPER(PPA.PROJECT_TYPE) LIKE 'PROGRAM%'
      --AND PRC.PROJECT_RELATIONSHIP_CODE IN ('PARENT','INTERNAL');
      AND ppa.project_id = pgm.project_id(+)
      -- AND UPPER (pm.ROLE) = 'PROGRAM MANAGER'
      AND ppa.project_id = vpp.project_id(+)
-- AND UPPER (vpp.ROLE) = 'VICE PRESIDENT-PROGRAM'
-- and ppa.segment1 = '10242'
/

PA Task Master

/* Formatted on 7/8/2014 9:45:15 AM (QP5 v5.115.810.9015) */
SELECT ppa.project_id,
       ppa.segment1 project_number,
       ppa.name project_name,
       pt.task_id,
       pt.task_number,
       pt.task_name,
       pt.service_type_code task_service,
       ptop.task_id top_task_id,
       ptop.task_number top_task_number,
       ptop.task_name top_task_name,
       --PTOP.START_DATE,
       ptop.service_type_code top_task_service,
       sb.class_code setup_billing,
       pexc.start_date execution_start_date
--PT.ATTRIBUTE1
FROM pa_projects_all ppa,
     pa_tasks pt,
     pa_tasks ptop,
     pa_tasks pexc,
     pa_project_classes_v sb
WHERE     ppa.project_id = pt.project_id
      --AND PT.BILLABLE_FLAG='Y'
      AND ppa.template_flag = 'N'
      AND pt.top_task_id <> pt.task_id
      --AND UPPER(PPA.PROJECT_TYPE)='PROGRAM-US'
      AND ptop.task_id = pt.top_task_id
      AND ptop.top_task_id = ptop.task_id
      AND ppa.project_id = sb.project_id(+)
      AND sb.class_category = 'SETUP BILLING'
      AND pexc.project_id = ppa.project_id
      AND pexc.service_type_code = 'EXECUTION PHASE'
      AND pexc.top_task_id = pexc.task_id
--AND PEXC.TASK_NUMBER = '2.0'
--and PPA.SEGMENT1 = '10189';