Thursday, August 4, 2011

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect