Project Accounting Queries

PA ALL TABLES

PA_PROJECTS_ALL: Information about projects

PA_AGREEMENTS_ALL: Customer contracts that serve as the basis for work authorization

PA_BILL_RATES_ALL: Information about bill rates and markups of standard bill rate schedules
PA_BILLING_ASSIGNMENTS_ALL: Assignments of billing extensions to a project type, project, or task
PA_COST_DISTRIBUTION_LINES_ALL: Information about expenditure item cost distribution
PA_CUST_REV_DIST_LINES_ALL: Information about expenditure item revenue distribution

PA_DRAFT_INVOICE_DETAILS_ALL: Inter company invoice details for cross charged transactions

PA_DRAFT_INVOICES_ALL: Information about draft invoices generated for projects

PA_DRAFT_REVENUES_ALL: Information about draft revenue generated for projects

PA_EXPENDITURE_COST_RATES_ALL: Cost rates for non-labor expenditure types

PA_EXPENDITURE_GROUPS_ALL: Groups of pre-approved expenditures

PA_EXPENDITURE_ITEMS_ALL: The smallest units of expenditure charged to projects and tasks

PA_EXPENDITURES_ALL: Groups of expenditure items incurred by employees or organizations for an expenditure period

PA_PERIODS_ALL: Implementation-defined periods against which project performance is measured

PA_PROJECT_ASSET_LINES_ALL: Summarized project CIP costs

PA_PROJECT_ASSETS_ALL: Assets defined for capital projects

PA_EXPENDITURE_TYPES: Implementation-defined classifications of expenditures charged to projects and tasks

PA_EXPENDITURE_CATEGORIES: Implementation-defined groupings of expenditure types by type of cost

PA_CLASS_CATEGORIES: Implementation-defined categories for classifying projects

PA_CLASS_CODES: Implementation-defined values within class categories that can be used to classify projects

PA_PROJECT_CLASSES: Class codes of class categories that are used to classify projects

PA_BUDGET_VERSIONS: Versions of project budgets

PA_BUDGET_LINES: Detail lines of project and task budgets

CREATE OR REPLACE VIEW DISC_PROJECT_PROG_MAP
(PROJECT_ID, ACCOUNT_VALUE, "INDIAN PROJECTS")
AS
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

CREATE OR REPLACE VIEW DISC_PROJ_TASK_MASTER
(PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME, TASK_ID, TASK_NUMBER,
TASK_NAME, TASK_SERVICE, TOP_TASK_ID, TOP_TASK_NUMBER, TOP_TASK_NAME,
TOP_TASK_SERVICE, SETUP_BILLING, EXECUTION_START_DATE)
AS
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';
/