Search Results pa_projects_pk

Overview

PA_PROJECTS_ALL is the master table in the Oracle Projects (PA) module of Oracle E-Business Suite, holding the highest units of work defined in Oracle Projects. Each row represents a single project — a container for tasks, budgets, expenditures, billing events, and revenue — and serves as the integration point between Project Management, Project Costing, Project Billing, and numerous external applications such as Payables, Assets, Purchasing, Grants, and Property Manager. The table resides in the PA schema and is the principal reference point for all project-related foreign keys across the EBS database.

In ETRM 12.2.2 the table is documented with 201 columns, and in a Data Vault modeling heuristic — mined from its foreign-key structure — PA_PROJECTS_ALL classifies as a hub, since it is the central anchor whose surrogate key is referenced by a very large number of dependent links and satellites across EBS.

Key Information Stored

The primary key is PA_PROJECTS_PK, defined on the PROJECT_ID surrogate column. Five unique indexes identify the business-key candidates: PA_PROJECTS_U1 (PROJECT_ID, SEGMENT1), PA_PROJECTS_U2 (SEGMENT1), PA_PROJECTS_U3 (NAME), PA_PROJECTS_U4 (PM_PROJECT_REFERENCE, PM_PRODUCT_CODE), and PA_PROJECTS_U5 (LONG_NAME).

The most significant columns include:

• PROJECT_ID — surrogate primary key and the value copied into all dependent tables.
• SEGMENT1 — the user-visible project number, the primary business key.
• NAME and LONG_NAME — the project's short and descriptive names.
• PROJECT_TYPE — foreign key to PA_PROJECT_TYPES_ALL, controlling project classification and associated behavior.
• PROJECT_STATUS_CODE — foreign key to PA_PROJECT_STATUSES, governing lifecycle state (e.g., approved, closed).
• CARRYING_OUT_ORGANIZATION_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, identifying the executing organization.
• START_DATE, COMPLETION_DATE, CLOSED_DATE — core lifecycle dates.
• ORG_ID — the multi-org operating unit discriminator.
• TEMPLATE_FLAG and CREATED_FROM_PROJECT_ID — designate templates and the source project for copies.
• COST_IND_RATE_SCH_ID, REV_IND_RATE_SCH_ID, INV_IND_RATE_SCH_ID — foreign keys to PA_IND_RATE_SCHEDULES_ALL_BG for indirect rate schedules.
• BILLING_CYCLE_ID and BILLING_CYCLE — billing cycle reference (foreign key to PA_BILLING_CYCLES).
• PUBLIC_SECTOR_FLAG, SECURITY_LEVEL, and REVENUE_ACCRUAL_METHOD — functional attributes governing accounting and access.
• LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY — standard audit columns.

Common Use Cases and Queries

PA_PROJECTS_ALL is queried in virtually every Projects report and is a frequent starting point for joins into cost, billing, and task data.

• Locating a project by number or name:
  SELECT project_id, name, segment1, project_status_code
FROM pa_projects_all
WHERE segment1 = :project_number;
• List active projects for an operating unit:
  SELECT p.project_id, p.segment1, p.name, s.project_status_name
FROM pa_projects_all p, pa_project_statuses s
WHERE p.project_status_code = s.project_status_code
AND p.org_id = :org_id;
• Join to PA_TASKS to enumerate the work breakdown structure for a project.
• Exclude templates when reporting:
  WHERE NVL(template_flag,'N') = 'N'
• Feed downstream reporting on billing setup (billing cycle, invoice formats, indirect rate schedules).

Related Objects

The table participates in an extensive relationship graph. The most significant dependents and referenced objects include:

• PA_TASKS — links on PROJECT_ID; the second-highest unit of work and the most common child of a project.
• PA_EXPENDITURE_ITEMS_ALL — links on PROJECT_ID; stores raw cost transactions charged to the project.
• PA_BUDGETS and PA_BUDGET_VERSIONS — link on PROJECT_ID; hold project budget data.
• PA_DRAFT_INVOICES_ALL and PA_DRAFT_REVENUES_ALL — link on PROJECT_ID; billing and revenue generation output.
• PA_PROJECT_ASSIGNMENTS and PA_PROJECT_PLAYERS — link on PROJECT_ID; hold team and role assignments.
• AP_INVOICES_ALL and AP_INVOICE_DISTRIBUTIONS_ALL — link on PROJECT_ID; Payables transactions referencing projects.
• FA_MASS_ADDITIONS and FA_ASSET_INVOICES — link on PROJECT_ID for capital projects feeding Assets.
• PO_REQ_DISTRIBUTIONS_ALL — links on PROJECT_ID for Purchasing distributions.
• PA_PROJECTS_ERP_EXT_B — the extension table for project attributes, joined on PROJECT_ID.
• HR_ALL_ORGANIZATION_UNITS, PA_PROJECT_TYPES_ALL, and PA_PROJECT_STATUSES — referenced by PA_PROJECTS_ALL via its own foreign keys.

Because of this breadth of referencing relationships, care should be taken before purging or reorganizing project rows, as hundreds of EBS tables depend on the PROJECT_ID values defined here.