Results for “pa_forecast_items”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PA_FORECAST_ITEMS is a transactional detail table in the Oracle Projects (PA) schema that underpins Oracle Project Resource Management forecasting. It stores day-by-day forecast information for each requirement and assignment, and also records comparable daily entries for unassigned capacity belonging to all resources that are schedulable. Each row therefore represents a single forecast item for a given date, resource, and organizational context, making the table the atomic grain for resource forecasting, capacity planning, availability analysis, and overcommitment tracking.

Reflecting its dependency structure, the table is best characterized as satellite-leaning under a Data Vault heuristic classification. Its primary key, FORECAST_ITEM_ID, identifies each forecast event, while a set of foreign keys (ASSIGNMENT_ID, JOB_ID, EXPENDITURE_ORGANIZATION_ID, PROJECT_ORGANIZATION_ID) anchors the record to descriptive business entities. In Data Vault terms, the surrounding assignment, job, and organization references behave as hub-like anchors, and PA_FORECAST_ITEMS serves as a satellite capturing the changing daily state and measures.

Key Information Stored

The surrogate primary key is FORECAST_ITEM_ID, enforced by the index PA_FORECAST_ITEMS_PK and additionally documented as the business-key unique index PA_FORECAST_ITEMS_U1. Around this key, the most significant stored attributes include:

• FORECAST_ITEM_TYPE — classifies the forecast record (for example requirement-driven versus assignment-driven or unassigned).
• PROJECT_ID, PROJECT_TYPE_CLASS — links the item to the project and its project type classification.
• PERSON_ID and RESOURCE_ID — the person or schedulable resource the forecast pertains to.
• ASSIGNMENT_ID — the project assignment associated with the forecast, referencing PA_PROJECT_ASSIGNMENTS.
• JOB_ID — the job classification used in scheduling, referencing PER_JOBS.
• ITEM_DATE — the effective day for the forecast item, the essential time dimension for daily forecasting.
• ITEM_UOM and ITEM_QUANTITY — the unit of measure and the forecast quantity for the day.
• CAPACITY_QUANTITY, AVAILABILITY_QUANTITY, OVERCOMMITMENT_QUANTITY — capacity, available, and overcommitted amounts, with corresponding AVAILABILITY_FLAG and OVERCOMMITMENT_FLAG indicators.
• PROVISIONAL_QTY, CONFIRMED_QTY, OVERPROVISIONAL_QTY, OVER_PROV_CONF_QTY — the provisional, confirmed, and overprovisional breakdowns of the forecast.
• PROJECT_ORGANIZATION_ID and EXPENDITURE_ORGANIZATION_ID — organizational contexts referencing HR_ALL_ORGANIZATION_UNITS.
• Cost and revenue measures — TXN_RAW_COST, TXN_BURDENED_COST, TXN_REVENUE, TXN_TRANSFER_PRICE, with project, project-functional, and expenditure-functional currency equivalents such as PROJECT_RAW_COST, PROJFUNC_BURDENED_COST, and EXPFUNC_RAW_COST.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and the REQUEST_ID/PROGRAM_ID concurrency fields.

Common Use Cases and Queries

The table supports resource forecasting, capacity and availability reporting, overcommitment detection, and financial measure analysis tied to forecasts. A typical pattern joins forecast items to assignments and projects for a given person and date range:

• Daily forecast by resource: Select ITEM_DATE, RESOURCE_ID, ITEM_QUANTITY, and ITEM_UOM from PA_FORECAST_ITEMS where PERSON_ID = :person_id and ITEM_DATE between :start and :end, ordered by ITEM_DATE.
• Overcommitment review: Filter on OVERCOMMITMENT_FLAG = 'Y' or OVERCOMMITMENT_QUANTITY > 0 to identify resources scheduled beyond capacity.
• Capacity versus availability analysis: Aggregate CAPACITY_QUANTITY, AVAILABILITY_QUANTITY, and CONFIRMED_QTY by PROJECT_ORGANIZATION_ID for a period.
• Link to assignment detail: Join ASSIGNMENT_ID to PA_PROJECT_ASSIGNMENTS, and JOB_ID to PER_JOBS, to enrich forecasts with assignment and job attributes.
• Financial projection: Sum PROJFUNC_RAW_COST, PROJFUNC_BURDENED_COST, and PROJFUNC_REVENUE by project for forecasting reports.

Related Objects

PA_FORECAST_ITEMS participates in a well-defined network of relationships:

• PA_PROJECT_ASSIGNMENTS — referenced via ASSIGNMENT_ID; supplies assignment context.
• PER_JOBS — referenced via JOB_ID; provides the job classification.
• HR_ALL_ORGANIZATION_UNITS — referenced twice, via EXPENDITURE_ORGANIZATION_ID and PROJECT_ORGANIZATION_ID.
• PA_FI_AMOUNT_DETAILS — references PA_FORECAST_ITEMS through its own FORECAST_ITEM_ID column, storing detailed amount breakdowns.
• PA_FORECAST_ITEM_DETAILS — child table keyed by FORECAST_ITEM_ID.
• PA_FRCST_ITEMS_AR and PA_FRCST_ITEM_DTLS_AR — archive counterparts retaining forecast items and their details by FORECAST_ITEM_ID.
• PA_FI_AMOUNT_DETAILS_AR — archived amount details referencing the same key.

Together these relationships confirm the table's role as the central grain for project resource forecasting, with FORECAST_ITEM_ID acting as the consistent join key across operational tables and their archive equivalents.