Results for “pn_var_vol_hist_all”

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

Overview

PN_VAR_VOL_HIST_ALL is a Property Manager (PN) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores volume history related to a line item. In the context of property and lease management, variance and volume tracking represents the history of actual, forecasted, and variance amounts associated with a specific line item (such as a lease, cost line, or recovery obligation) across reporting periods. The table resides in the PN schema and is documented with 45 columns, making it a substantial transactional entity within the Property Manager volume variance reporting subsystem.

From a data modeling perspective, the heuristic Data Vault classification for this object is link. This classification is derived from the table's foreign key structure, which connects multiple business entities — line items, periods, group dates, and general ledger accounts — rather than storing a single natural business key with descriptors. The link classification suggests the table primarily serves to record the association and measurement of volume history events across these related dimensions.

Key Information Stored

The table is anchored by the surrogate primary key VOL_HIST_ID, enforced through the PN_VAR_VOL_HIST_PK constraint, with a corresponding unique index PN_VAR_VOL_HIST_U1 that serves as a business-key candidate on the same column. The human-readable business identifier is VOL_HIST_NUM, which provides a meaningful reference number for each volume history record.

The most significant descriptive and transactional columns include:

• LINE_ITEM_ID — foreign key to PN_VAR_LINES_ALL; identifies the line item to which the volume history belongs.
• PERIOD_ID — foreign key to PN_VAR_PERIODS_ALL; ties the record to a variance reporting period.
• GRP_DATE_ID and GROUP_DATE — foreign key to PN_VAR_GRP_DATES_ALL, with the corresponding group date value stored directly.
• START_DATE and END_DATE — define the effective date range for the volume history entry.
• REPORTING_DATE, DUE_DATE, and INVOICING_DATE — key milestone dates for reporting and billing workflows.
• ACTUAL_GL_ACCOUNT_ID and FOR_GL_ACCOUNT_ID — foreign keys to GL_CODE_COMBINATIONS for the actual and forecasted accounting distributions.
• ACTUAL_AMOUNT, FORECASTED_AMOUNT, and VARIANCE_EXP_CODE — the core quantitative measures and variance explanation.
• VOL_HIST_STATUS_CODE and REPORT_TYPE_CODE — control the lifecycle state and reporting classification of each record.
• CERTIFIED_BY — captures the individual who certified the volume history entry.
• DAILY_ACTUAL_AMOUNT — supports daily granularity of actual amounts within the period.

Standard EBS audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and the ORG_ID multi-tenant discriminator are also present, along with fifteen ATTRIBUTE flex columns and ATTRIBUTE_CATEGORY for extensibility.

Common Use Cases and Queries

The primary use case is variance reporting and reconciliation for property line items. Common queries retrieve volume history for a specific line item across periods, compare actual against forecasted amounts, or isolate records by status and report type.

• Line item history report: SELECT vol_hist_num, start_date, end_date, actual_amount, forecasted_amount FROM pn_var_vol_hist_all WHERE line_item_id = :line_item_id ORDER BY start_date;
• Variance analysis: filter on VARIANCE_EXP_CODE and REPORT_TYPE_CODE to examine period variances.
• GL reconciliation: join ACTUAL_GL_ACCOUNT_ID or FOR_GL_ACCOUNT_ID to GL_CODE_COMBINATIONS to verify accounting distributions.
• Certification tracking: query CERTIFIED_BY and VOL_HIST_STATUS_CODE to monitor approval progress.
• Period rollover: aggregate DAILY_ACTUAL_AMOUNT by PERIOD_ID for daily-to-period reporting.

Because PN_VAR_VOL_ARCH_ALL references VOL_HIST_ID, archival and purge routines should join on that key when moving historical records out of the active table.

Related Objects

The following objects are most significant based on documented foreign key relationships:

• PN_VAR_LINES_ALL — joined via LINE_ITEM_ID; the parent line item definition. This is the dominant join path for retrieving line item context.
• PN_VAR_GRP_DATES_ALL — joined via GRP_DATE_ID; supplies group date definitions.
• PN_VAR_PERIODS_ALL — joined via PERIOD_ID; supplies period definitions for reporting.
• GL_CODE_COMBINATIONS — joined twice via ACTUAL_GL_ACCOUNT_ID and FOR_GL_ACCOUNT_ID; resolves accounting flexfield combinations for actual and forecasted amounts.
• PN_VAR_VOL_ARCH_ALL — the archival counterpart, which references PN_VAR_VOL_HIST_ALL through VOL_HIST_ID.

These relationships should be respected in any query or ETL process to maintain referential integrity and report accuracy.