Key Tables for Project Costing and Overviews

Key Tables for Project Costing and Overviews

These are following areas with respect to Project. They are

1) Project Billing.
2) Project Contracts.
3) Project Costing
4) Project Management.
5) Project Portfolio Analysis.
6) Project Resource Management.

Overview of Oracle Project Billing

Oracle Project Billing provides you with the ability to define revenue and invoicing rules for your projects, generate revenue, create invoices, and integrate with other Oracle Applications to process revenue and invoices. Oracle Project Billing also integrates with Oracle Receivables.

Oracle Project Billing enables you to perform the following functions:
• Enter project customers and contacts with whom you have negotiated and contracted project work
• Enter agreements (contracts) from your customers and fund projects with those agreements
• Generate revenue using various methods including time and materials, percent  complete, and cost plus
• Create draft invoices from detail transactions and milestones for online approval by your project or accounting managers
• Generate revenue accounting events and create accounting in Oracle Sub ledger Accounting
• Report project revenue, invoice, and receivables status online and in reports

Overview of Project Costing

Costing is the processing of expenditures to calculate their cost to each project and determine the GL accounts to which the costs will be posted. Costing is performed for the following types of expenditures:
• Pre-approved expenditures.
• Labor
• Usages
• Miscellaneous Transactions
• Burden transactions.
• Expenditures submitted from Oracle Internet Expenses. See: Integrating Expense Reports with Oracle Payable and Oracle Internet Expenses.
• Supplier Costs.

• Imported expenditures.

• Adjusted expenditures in Oracle Projects that need re-costing.

Overview of Project Management

Oracle Project Management provides project managers a solution for maintaining control and visibility of all aspects of their projects.

As a project manager, you can view project information at a single source point enabling you to track and manage a project through the project life cycle, from creating and planning, through to completion. Using Oracle Project Management, you can:
• Create, manage, version, and view work plans.
• Track progress against the work plan.
• Link Microsoft Project with Oracle Projects enabling you to work with a project using both applications.
• Create budgets and forecasts to plan and manage the financial performance of projects throughout the project life cycle.
• Track and monitor the performance of a project, and analyze financial and effort information by task, resource, and time.
• View exceptions for key financial and schedule metrics, and send automated status report notifications to key project stakeholders.
• Provide a timely and consistent view of project status information to all audience members and project stakeholders.

Overview of Oracle Project Contracts

Oracle Project Contracts supports the contract management needs of project driven organizations, including commercial and government contractors, agencies, and  subcontractors. These organizations operate in a project centric environment characterized by:
• Changing contract specifications.
• Volatile demand and long lead-times.
• High percentage of procure-to-contract components and services.
• Contractual requirements for billing.
• Incremental funding in multiple currencies.
• Compliance of government regulations.
Oracle Project Contracts addresses budgetary constraints, contract margins, flow down of contract information to subcontractors, and prioritization of deliverable. The Contract Organizer window provides a single entry point to access and define contract documents. Through the Contract Organizer window, you can access different functional regions including authoring, deliverable tracking, funding, hold management, contract revisions, and other contract related activities.

Oracle Project Contracts is part of the Oracle e-Business Suite, an integrated set of e-business solutions for the enterprise. It provides comprehensive use of workflows, flexible attributes, API framework, and Internet development tools.

Features of Oracle Project Contracts include:
• Authoring of all types of contract documents.
• Workflow-based contract administration, including status control, hold and change management.
• Comprehensive contract funding.
• Deliverable tracking, integrated with ERP functions such as planning, production,and procurement.
• Contract costing, billing, and revenue recognition.
• Role-based access security.
• Contract flow down.

Name of Table	Column
pa_budget_lines
pa_budget_versions_draft_v	BUDGET_STATUS_M,PROJECT_TYPE
pa_valid_categories	CLASS_CATEGORY
pa_project_options_v

pa_expenditure_types_desc_v	EXPENDITURE_TYPE EXPENDITURE_CATEGORY
pa_budget_entry_methods	COST_QUANTITY_FLAG RAW_COST_FLAG BURDENED_COST_FLAG ENTRY_LEVEL_CODE

pa_resource_lists_v
pa_projects_all	SEGMENT1 is the project no
pa_commitment_txns
pa_txn_accum_details
pa_tasks
pa_implementations
pa_segment_value_lookup_sets

pa_project_types	ALLOW_REV_BUDGET_ENTRY_FLAG ALLOW_COST_BUDGET_ENTRY_FLAG
pa_projects_v	PROJECT_STATUS_CODE ORGANIZATION_NAME , ORG_ID

pa_expend_items_adjust2_v
ad_applied_patches
hr_organization_units
fnd_user_resp_groups_direct
fnd_user
pa_budg_control_res_grp_v

Query Related to Project Costing:-

To find Budget Status:-

select distinct BUDGET_STATUS_M from pa_budget_versions_draft_v

To find  last update date of a project:-

select * from pa_projects_all where last_update_date > '13-DEC-2011'

To find  last update date of a project with status:-

select * from pa_projects_all where last_update_date > '01-JAN-2012' and PROJECT_STATUS_CODE = 'APPROVED'

To find  Budget entry method of a project:-

select * from pa_budget_versions_draft_v where BUDGET_ENTRY_METHOD_CODE = 'Test Budget-1' and BUDGET_VERSION_ID = xxxxx

select budget_version_id,budget_status_m from pa_budget_versions_draft_v

To find  Budget status of a project:-

select budget_version_id,budget_status_m from pa_budget_versions_draft_v where budget_status_M = 'In Progress'

To find details of a project:-

select * from pa_projects_all where SEGMENT1 = 'xxx'

select * from pa_projects_all where TEMPLATE_FLAG = 'Y'

select * from pa_projects_all where TEMPLATE_FLAG = 'Y' and PROJECT_TYPE = 'HA-Direct'

select count(distinct project_status_code) from pa_projects_all

SELECT   CT.PROJECT_ID,

0,

0,

TAD.TXN_ACCUM_ID,

CT.VENDOR_ID,

CT.VENDOR_NAME,

CT.CMT_CREATION_DATE,

CT.LINE_TYPE,

L.MEANING,

CT.CMT_NUMBER,

CT.CMT_LINE_NUMBER,

CT.TOT_CMT_RAW_COST,

CT.TOT_CMT_BURDENED_COST,

CT.CMT_APPROVED_DATE,

CT.CMT_PROMISED_DATE,

CT.CMT_NEED_BY_DATE,

CT.DESCRIPTION,

T.TASK_NUMBER,

T.TASK_NAME,

CT.EXPENDITURE_TYPE,

CT.ORGANIZATION_ID,

O.NAME,

CT.CMT_APPROVED_FLAG,

CT.TOT_CMT_QUANTITY,

CT.UNIT_OF_MEASURE,

CT.CMT_REQUESTOR_NAME,

CT.CMT_BUYER_NAME,

CT.TRANSACTION_SOURCE,

L2.MEANING,

CT.DENOM_CURRENCY_CODE,

CT.DENOM_RAW_COST,

CT.DENOM_BURDENED_COST,

CT.ACCT_CURRENCY_CODE,

CT.ACCT_RAW_COST,

CT.ACCT_BURDENED_COST,

CT.ACCT_RATE_DATE,

CT.ACCT_RATE_TYPE,

CT.ACCT_EXCHANGE_RATE,

CT.RECEIPT_CURRENCY_CODE,

CT.RECEIPT_CURRENCY_AMOUNT,

CT.RECEIPT_EXCHANGE_RATE,

CT.PROJECT_CURRENCY_CODE,

CT.PROJECT_RATE_DATE,

CT.PROJECT_RATE_TYPE,

CT.PROJECT_EXCHANGE_RATE,

CT.CMT_REJECTION_CODE,

CT.cmt_distribution_id

FROM   PA_COMMITMENT_TXNS CT,

PA_TXN_ACCUM_DETAILS TAD,

PA_LOOKUPS L,

PA_LOOKUPS L2,

PA_TASKS T,

HR_ORGANIZATION_UNITS O

WHERE       TAD.LINE_TYPE = 'M'

AND TAD.CMT_LINE_ID = CT.CMT_LINE_ID

AND L.LOOKUP_CODE = CT.LINE_TYPE

AND L.LOOKUP_TYPE = 'COMMITMENT LINE TYPE'

AND L2.LOOKUP_CODE = CT.TRANSACTION_SOURCE

AND L2.LOOKUP_TYPE = 'COMMITMENT TXN SOURCE'

AND CT.TASK_ID = T.TASK_ID(+)

AND CT.ORGANIZATION_ID = O.ORGANIZATION_ID

--AND CT.PROJECT_ID = PA_STATUS.GETPROJID;