Search Results old_sp_fix_date

Overview

PA.PA_FP_SPREAD_CALC_TMP is a global temporary table in the Oracle Projects (PA) schema, registered under FND Design Data as PA.PA_FP_SPREAD_CALC_TMP with a status of VALID. It exists to store transient working data generated during internal processing of the calculate and spread APIs that underpin forecast, budget, and planning spread computations. It is not an end-user or reporting object; it is a staging area consumed by PL/SQL processing logic.

Because the table is defined as a global temporary table with a data duration of SYS$SESSION, each session sees only the rows it inserts, and all rows are purged when the session ends. Physical storage parameters include PCT FREE 10 and PCT USED 40, reflecting an insert-and-consume workload rather than long-term retention. The table is classified by heuristics mined from its foreign-key structure as a standalone object. In data vault modeling terms, this suggests it does not participate as a persistent hub, link, or satellite; its role is procedural rather than dimensional, and it should generally be excluded from enterprise data warehouse extraction.

Key Information Stored

With 153 documented columns, the table carries the full context needed to recalculate spread amounts for a budget line and resource assignment. The most significant columns fall into several groups.

• Identity and relationships: PROJECT_ID, BUDGET_VERSION_ID, BUDGET_LINE_ID, RESOURCE_ASSIGNMENT_ID, and TXN_CURRENCY_CODE form the working key context. BUDGET_LINE_ID is documented as a foreign key to PA_BUDGET_LINES, providing the strongest link to the base planning model.
• Dates and periods: START_DATE, END_DATE, PERIOD_NAME, ETC_START_DATE, OLD_BL_START_DATE, OLD_BL_END_DATE, OLD_PLAN_START_DATE, OLD_PLAN_END_DATE, NEW_PLAN_START_DATE, and NEW_PLAN_END_DATE support before-and-after comparisons of plan dates.
• Spread configuration and change tracking: OLD_SPREAD_CURVE_ID, NEW_SPREAD_CURVE_ID, OLD_SP_FIX_DATE, NEW_SP_FIX_DATE, SP_FIX_DATE_CHANGE_FLAG, SP_CURVE_CHANGE_FLAG, and SPREAD_AMTS_FLAG. The column the user searched for, OLD_SP_FIX_DATE, holds the prior spread fix date used to detect whether a spread point change requires recalculation.
• Amounts and quantities: TXN_RAW_COST, TXN_BURDENED_COST, TXN_REVENUE, QUANTITY, and the corresponding initial values, plus PROJECT_RAW_COST, PROJECT_BURDENED_COST, and PROJECT_REVENUE.
• Rate context: COST_RATE, BURDEN_COST_RATE, BILL_RATE, their override counterparts, and BILL_MARKUP_PERCENTAGE.
• Processing control: PROCESSED_FLAG, SKIP_RECORD_FLAG, CALLING_CONTEXT, and the family of CHANGE_FLAG columns that drive conditional recalculation.

No unique index is documented. Two non-unique indexes exist: PA_FP_SPREAD_CALC_TMP_N1 on RESOURCE_ASSIGNMENT_ID, TXN_CURRENCY_CODE, and START_DATE, and PA_FP_SPREAD_CALC_TMP_N2 on BUDGET_VERSION_ID. There is therefore no documented surrogate primary key; PROJECT_ID and BUDGET_VERSION_ID act as candidate business-key discriminators.

Common Use Cases and Queries

The primary use case is diagnostic: tracing which records a concurrent process placed on the temporary table during a failed or unexpected spread calculation. Because rows are session-scoped, a direct SQL query from a separate session returns nothing. Meaningful inspection requires either running the query inside the same session as the calling process or temporarily instrumenting the spread API to log rows before they are consumed.

A typical diagnostic pattern filters on the change flags to isolate affected records:

• SELECT BUDGET_LINE_ID, RESOURCE_ASSIGNMENT_ID, OLD_SP_FIX_DATE, NEW_SP_FIX_DATE, SP_FIX_DATE_CHANGE_FLAG FROM PA_FP_SPREAD_CALC_TMP WHERE SP_FIX_DATE_CHANGE_FLAG = 'Y';
• SELECT BUDGET_VERSION_ID, COUNT(*) FROM PA_FP_SPREAD_CALC_TMP GROUP BY BUDGET_VERSION_ID, using the N2 index;
• SELECT PROJECT_ID, BUDGET_LINE_ID, TXN_RAW_COST, PROJECT_RAW_COST FROM PA_FP_SPREAD_CALC_TMP WHERE PROCESSED_FLAG IS NULL;

Reporting use cases are limited by design; the table should never be used as a source for permanent extracts or reconciliation reports.

Related Objects

• PA.PA_BUDGET_LINES — referenced through BUDGET_LINE_ID; the authoritative budget line record the temporary row augments.
• PA.PA_BUDGET_VERSIONS — joined via BUDGET_VERSION_ID for version type and status context.
• PA.PA_PROJECTS — joined via PROJECT_ID for project attributes.
• PA.PA_RESOURCE_ASSIGNMENTS — joined via RESOURCE_ASSIGNMENT_ID for resource, rate, and quantity context.
• PA.PA_SPREAD_CURVES — referenced through OLD_SPREAD_CURVE_ID and NEW_SPREAD_CURVE_ID.
• PA.PA_MFC_COST_TYPES — referenced through OLD_MFC_COST_TYPE_ID and NEW_MFC_COST_TYPE_ID.
• PA_FP_SPREAD_CALC_TMP_N1 / _N2 — the two non-unique indexes supporting access paths on assignment and version.
• Calculate and Spread APIs — the internal PA program units that populate and consume this table.