Search Results user_abstracted




Overview
Explore in AI Chat

Overview

PN_VAR_VOL_HIST_V is a form-driven database view owned by the APPS schema within the Property Manager (PN) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its primary purpose, as documented in the ETRM metadata, is to serve as a form view used to input volume history related information. Rather than functioning as a standalone reporting object, the view acts as a user-facing abstraction layer that a Property Manager form references when capturing, editing, and validating volume history records associated with variance periods and variance lines.

Because it is a view and not a table, it does not store data. It presents a joined, denormalized projection of underlying transaction tables, exposing derived and translated columns such as VARIANCE_AMOUNT and the abstracted-by user name. This design simplifies form-level data handling while preserving the normalized structure of the base tables that persist the actual data. Users searching for "variance_amount" will find that this column is not a physically stored value but is computed dynamically within the view definition.

Underlying Base Objects

The documented view metadata identifies the referenced base objects as FND_USER (SYNONYM) and PN_VAR_VOL_HIST (SYNONYM), both resolving to the APPS schema. However, the view text in the ETRM documentation shows a four-way join across the following objects:

  • PN_VAR_VOL_HIST (aliased HIST) — the primary source of volume history records.
  • PN_VAR_PERIODS_ALL (aliased PER) — the variance period definition, joined on PERIOD_ID.
  • PN_VAR_LINES_ALL (aliased LINES) — the variance line item, joined on LINE_ITEM_ID.
  • FND_USER (aliased USER1) — the application user directory, outer-joined via USER_ID(+) to resolve the certified or abstracted-by user.

The joins enforce referential alignment between a volume history record and its parent period and line. The outer join to FND_USER allows history rows to remain visible even when the user reference is null or cannot be resolved. All physical persistence resides in PN_VAR_VOL_HIST; the remaining tables qualify and contextualize each row.

Key Columns

The view exposes the volume history identifier (VOL_HIST_ID), its human-readable number (VOL_HIST_NUM), and the owning LINE_ITEM_ID, PERIOD_ID, and ORG_ID, which together establish the record's variance and organization context. Date columns include START_DATE, END_DATE, GROUP_DATE, REPORTING_DATE, DUE_DATE, and INVOICING_DATE, supporting period-, group-, and reporting-level analysis.

Financial columns include ACTUAL_AMOUNT, FORECASTED_AMOUNT, and the derived VARIANCE_AMOUNT, whose formula in the view definition is (HIST.ACTUAL_AMOUNT - HIST.FORECASTED_AMOUNT). This column therefore always reflects the arithmetic difference between actual and forecasted values at query time. Associated explanation codes ACTUAL_EXP_CODE, FORECASTED_EXP_CODE, and VARIANCE_EXP_CODE classify the nature of each figure.

Status and classification are captured by VOL_HIST_STATUS_CODE and REPORT_TYPE_CODE. The certified user is exposed through CERTIFIED_BY, aliased as ABSTRACTED_BY_USER_ID, with the corresponding user name exposed as USER_ABSTRACTED. Standard EBS audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), the ROW_ID pseudo-column for form locking, and fifteen ATTRIBUTE columns complete the projection.

Common Use Cases and Queries

The principal use case is the Property Manager volume history entry form, which reads and writes through the view to populate variance period and line data. Reporting and integration scenarios frequently query it to surface actual-to-forecast gaps. For example, to list recorded variances for an organization:

  • SELECT vol_hist_num, period_id, line_item_id, actual_amount, forecasted_amount, variance_amount FROM pn_var_vol_hist_v WHERE org_id = :p_org_id AND variance_amount <> 0 ORDER BY reporting_date;

To identify forecast overruns by user and status:

  • SELECT user_abstracted, vol_hist_status_code, SUM(variance_amount) total_variance FROM pn_var_vol_hist_v GROUP BY user_abstracted, vol_hist_status_code;

Because VARIANCE_AMOUNT is computed, filtering and aggregation on it are valid but cannot be indexed, so large extracts should restrict on ORG_ID, PERIOD_ID, or date ranges first. All queries must run in the APPS schema context or be granted appropriate synonyms and privileges.