Results for “overprovisional_qty”

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

Overview

PA_FRCST_ITEM_DTLS_AR is a Projects (PA) module table that stores detailed amount information for each forecast item. The most common illustration of the values held here is labor hours, though the table supports a wider range of quantity and currency-denominated measures. The _AR suffix indicates this is an archival or staging companion object associated with the forecast detailed-amount tables, and it carries its own purge-related columns (PURGE_BATCH_ID, PURGE_PROJECT_ID, PURGE_RELEASE) so archived detail rows can be identified and managed in purge batches.

Its role in Oracle EBS is to persist the granular, transaction-level amounts that roll up into forecast items. Rather than storing a single summarized figure per forecast item, this table keeps one row per detail line, carrying quantity, amount, currency, resource, and period dimensions. From a Data Vault modeling perspective, the heuristic classification derived from the foreign-key structure is satellite-leaning. This suggests the table is best modeled as a satellite attached to a forecast-item hub or link, capturing descriptive and measurable attributes keyed to the parent forecast item rather than acting as an independent hub or link itself.

Key Information Stored

The table is documented with 55 columns. The most significant are:

• FORECAST_ITEM_ID — the principal business-key candidate, linking each detail row to its parent forecast item in PA_FORECAST_ITEMS.
• AMOUNT_TYPE_ID — identifies the amount type (for example, labor hours or cost) that the row represents, referencing PA_AMOUNT_TYPES_B.
• ITEM_DATE — the effective date of the detail line.
• ITEM_QUANTITY and ITEM_UOM — the quantity and its unit of measure.
• PROJ_AMOUNT and PROJ_CURRENCY_CODE — the project-currency amount and currency.
• DENOM_AMOUNT and DENOM_CURRENCY_CODE — the denominated amount and currency.
• PVDR_ACCT_AMOUNT / RCVR_ACCT_AMOUNT (with their currency codes) — provider and receiver accounting amounts.
• RESOURCE_ID and RESOURCE_TYPE_CODE — the resource and its type for which the amount applies.
• PROJECT_ID and EXPENDITURE_ORGANIZATION_ID — the owning project and expenditure organization.
• LINE_NUM and LINE_NUM_REVERSED — ordering and reversal indicators within a forecast item.
• PURGE_BATCH_ID, PURGE_PROJECT_ID, PURGE_RELEASE — archival and purge control.

The documented metadata does not identify an explicitly named single-column surrogate primary key; FORECAST_ITEM_ID together with amount type, date, line number, and resource forms the natural business-key combination. Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide auditability, and the PROGRAM_*/REQUEST_ID columns identify the concurrent program that inserted the row.

Common Use Cases and Queries

Typical uses include reconciling detailed forecast quantities against summarized balances, reporting forecast labor hours by resource or organization, and supporting purge/archival processing.

• Retrieve all detail lines for a forecast item: SELECT * FROM PA_FRCST_ITEM_DTLS_AR WHERE FORECAST_ITEM_ID = :item_id;
• Aggregate forecast hours by resource and period: SELECT RESOURCE_ID, TRUNC(ITEM_DATE,'MM') mth, SUM(ITEM_QUANTITY) FROM PA_FRCST_ITEM_DTLS_AR WHERE PROJECT_ID = :proj GROUP BY RESOURCE_ID, TRUNC(ITEM_DATE,'MM');
• Identify rows staged for purging: SELECT * FROM PA_FRCST_ITEM_DTLS_AR WHERE PURGE_BATCH_ID IS NOT NULL;
• Join to amount types to label measures: SELECT d.*, t.AMOUNT_TYPE FROM PA_FRCST_ITEM_DTLS_AR d, PA_AMOUNT_TYPES_B t WHERE d.AMOUNT_TYPE_ID = t.AMOUNT_TYPE_ID;

Related Objects

• PA_FORECAST_ITEMS — joined on FORECAST_ITEM_ID; the parent forecast item definition.
• PA_AMOUNT_TYPES_B — joined on AMOUNT_TYPE_ID; classifies the measure stored.
• HR_ALL_ORGANIZATION_UNITS — joined on EXPENDITURE_ORGANIZATION_ID; the expenditure organization.
• PA_PURGE_BATCHES_ALL — joined on PURGE_BATCH_ID; purge batch control.