Project Accounting Queries

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'

/

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

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