Search Results pa_fp_spread_calc_tmp

Overview

PA_FP_SPREAD_CALC_TMP is an internal Oracle Projects (PA) table that stores temporary working data required by the Calculate and Spread API during its internal processing. It is defined as a global temporary table (GTT), meaning its rows are private to the session that inserts them and are typically purged on transaction commit or session termination. The table resides in the PA schema and is classified as VALID under Oracle EBS 12.1.1 and 12.2.2.

The object plays no direct role in persistent project forecasting or budgeting data; instead, it acts as a scratchpad where the forecasting and spreading engine stages intermediate results—currencies, rates, amounts, flags, and change-tracking indicators—before committing finalized values back to permanent tables. Its lifecycle is short-lived, and its contents are meaningful only while a Calculate or Spread API call is executing.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure is standalone. Because the table carries no downstream dependencies and exists solely to support transient processing, it is best treated as a transient staging structure rather than a hub, link, or satellite. Modelers should not attempt to persist or dimensionally model this object.

Key Information Stored

The table contains 153 documented columns, but only a subset is central to its role. The most important are:

• BUDGET_LINE_ID — Foreign key to PA_BUDGET_LINES; identifies the budget line whose spread is being calculated.
• PROJECT_ID, BUDGET_VERSION_ID, BUDGET_VERSION_TYPE — Contextual identifiers for the project and budget version under processing.
• RESOURCE_ASSIGNMENT_ID — Links to the resource assignment being spread.
• TXN_CURRENCY_CODE, PROJECT_CURRENCY_CODE, PROJFUNC_CURRENCY_CODE — Currency contexts for transaction, project, and project functional amounts.
• START_DATE, END_DATE, PERIOD_NAME — The time window over which spreading is computed.
• TXN_RAW_COST, TXN_BURDENED_COST, TXN_REVENUE, QUANTITY — Transaction-level amounts and quantities being spread.
• COST_RATE, BURDEN_COST_RATE, BILL_RATE — Rates applied during the calculation, with corresponding override columns.
• PROJECT_RAW_COST, PROJECT_BURDENED_COST, PROJECT_REVENUE, PROJFUNC_RAW_COST, PROJFUNC_BURDENED_COST, PROJFUNC_REVENUE — Converted amounts in project and project functional currency.
• OLD_SPREAD_CURVE_ID, NEW_SPREAD_CURVE_ID, OLD_SP_FIX_DATE, NEW_SP_FIX_DATE — The prior and proposed spread curve and fixed-date settings.
• SPREAD_AMTS_FLAG, RE_SPREAD_AMTS_FLAG, DELETE_BL_FLAG, MASS_ADJUST_FLAG — Control flags governing which operations the API performs.
• PROCESSED_FLAG, SKIP_RECORD_FLAG — Row-level processing state used by the API loop.
• CALLING_CONTEXT — Indicates the calling scenario (calculate vs. spread vs. mass adjust).

No surrogate primary key is documented for this table; it is a transient work area rather than a keyed entity. Business-key uniqueness is not enforced. Numerous SYSTEM_REFERENCE_NUM/VAR/DAT columns exist purely as generic scratch space for the API, and a large family of *_CHANGED_FLAG and *_G_MISS_NUM_FLAG columns tracks which attributes changed or are missing.

Common Use Cases and Queries

Because PA_FP_SPREAD_CALC_TMP is session-scoped, external querying is only meaningful during active API execution or when debugging a specific session. Typical diagnostic patterns include:

• Inspecting which budget lines are pending spread by filtering on PROCESSED_FLAG = 'N' or SKIP_RECORD_FLAG = 'Y'.
• Reconciling computed project and project functional amounts against source transaction amounts by comparing TXN_RAW_COST to PROJECT_RAW_COST and PROJFUNC_RAW_COST.
• Tracing rate application by examining COST_RATE, BURDEN_COST_RATE, BILL_RATE and their override counterparts.
• Reviewing spread-curve and fix-date changes by selecting OLD_SPREAD_CURVE_ID, NEW_SPREAD_CURVE_ID, OLD_SP_FIX_DATE, and NEW_SP_FIX_DATE.
• Diagnosing currency conversion failures via COST_REJECTION_CODE, REVENUE_REJECTION_CODE, PC_CUR_CONV_REJECTION_CODE, and PFC_CUR_CONV_REJECTION_CODE.

A representative query joins to the budget line hierarchy:

SELECT t.budget_line_id, t.project_id, t.txn_raw_cost, t.projfunc_raw_cost, t.spread_amts_flag
FROM pa_fp_spread_calc_tmp t
WHERE t.processed_flag = 'N'
AND t.budget_line_id = :budget_line_id;

Because the table is a GTT, any session other than the one running the API will not see its rows, so such queries are chiefly useful inside the API session or a dedicated debugging session.

Related Objects

The principal documented relationship is the foreign key from BUDGET_LINE_ID to PA_BUDGET_LINES, which anchors the temporary rows to the persistent budget line being spread. Other significant objects that participate in the same processing path include:

• PA_BUDGET_LINES — The parent budget line; joined via BUDGET_LINE_ID.
• PA_BUDGET_VERSIONS — Provides version context via BUDGET_VERSION_ID.
• PA_PROJECTS — Supplies project attributes via PROJECT_ID.
• PA_RESOURCE_ASSIGNMENTS — Referenced through RESOURCE_ASSIGNMENT_ID.
• PA_SPREAD_CURVES — Referenced through OLD_SPREAD_CURVE_ID and NEW_SPREAD_CURVE_ID.
• PA_FP_GEN_UTILS / PA_FP_SPREAD_UTIL — Public utility packages that invoke the Calculate and Spread APIs and populate this table.
• PA_FP_SPREAD_CALC_TMP itself is consumed internally by the Calculate and Spread API package, which reads and writes its rows during execution.

Because the object is a standalone transient table, no dedicated views or downstream permanent tables depend on it; its data is discarded once the API transaction completes.