Results for “hist_org_id”

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

Overview

PN_VAR_DEDUCT_ARCH_ALL is a Property Manager (PN) transactional table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores deduction data modified during batch import processing. Deductions in Property Manager represent variable expense adjustments applied against tenant or owner participation, and the archive table captures the before-change state of deduction records each time a batch import modifies them. This provides an audit and recovery trail, allowing users to review what a prior deduction record contained before an import overwrote it, and to reconstruct or reconcile amounts when import results are disputed or must be rolled back.

From a heuristic Data Vault modeling perspective, the table is classified as standalone, meaning it has no child tables depending on it. It functions conceptually as a satellite-like history store, holding descriptive and numeric deduction attributes tied to a parent deduction record and group date, but because it carries its own surrogate key and is not joined to a hub-and-link structure in the documented schema, it is best treated as an independent historical snapshot rather than a normalized vault component.

Key Information Stored

The primary key is DEDUCT_ARCH_ID, enforced by the PN_VAR_DEDUCT_ARCH_PK constraint. A unique index, PN_VAR_DEDUCT_ARCH_U1, also exists on DEDUCT_ARCH_ID; no separate business-key candidate is documented beyond this surrogate identifier. The archive record is anchored to its source deduction through DEDUCTION_ID, which references PN_VAR_DEDUCTIONS_ALL, and to a billing or accounting grouping through GRP_DATE_ID, which references PN_VAR_GRP_DATES_ALL. DEDUCTION_NUM carries the human-readable deduction number, while LINE_ITEM_ID, PERIOD_ID, START_DATE, END_DATE, and GROUP_DATE and INVOICING_DATE supply the accounting period and timing context.

Financial content is captured in DEDUCTION_AMOUNT, DEDUCTION_TYPE_CODE, and GL_ACCOUNT_ID, identifying the amount archived, the deduction category, and the general ledger account affected. EXPORTED_CODE and COMMENTS record processing status and free-text notes. The historical audit columns HIST_CREATION_DATE, HIST_CREATED_BY, HIST_LAST_UPDATE_DATE, HIST_LAST_UPDATED_BY, and HIST_LAST_UPDATE_LOGIN preserve who created and last changed the archived version, while ORG_ID and HIST_ORG_ID provide multi-org partitioning. ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 remain available for descriptive flexfield data. Standard WHO columns such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN track the archive row itself.

Common Use Cases and Queries

Typical use cases include auditing batch imports that altered deduction records, reconciling deduction amounts before and after an import cycle, and reviewing historical GL account or deduction type assignments. A common pattern compares archived rows to their current counterparts:

• SELECT a.DEDUCTION_ID, a.DEDUCTION_AMOUNT, d.DEDUCTION_AMOUNT FROM PN_VAR_DEDUCT_ARCH_ALL a, PN_VAR_DEDUCTIONS_ALL d WHERE a.DEDUCTION_ID = d.DEDUCTION_ID AND a.DEDUCT_ARCH_ID = :id;
• SELECT DEDUCTION_NUM, HIST_LAST_UPDATE_DATE, HIST_LAST_UPDATED_BY, DEDUCTION_AMOUNT FROM PN_VAR_DEDUCT_ARCH_ALL WHERE ORG_ID = :org_id AND HIST_LAST_UPDATE_DATE >= :start_date ORDER BY HIST_LAST_UPDATE_DATE;
• SELECT a.* FROM PN_VAR_DEDUCT_ARCH_ALL a, PN_VAR_GRP_DATES_ALL g WHERE a.GRP_DATE_ID = g.GRP_DATE_ID AND g.GROUP_DATE BETWEEN :from_date AND :to_date;

Reporting extracts frequently join to PN_VAR_GRP_DATES_ALL for date-basis filtering and to GL account or period reference tables for financial reporting. Queries should always be constrained by ORG_ID in a multi-org environment and by HIST_LAST_UPDATE_DATE to limit scan ranges on this archiving table.

Related Objects

The principal related objects, based on the documented foreign key relationships and shared PN schema context, include:

• PN_VAR_DEDUCTIONS_ALL — the source deduction table; joined on DEDUCTION_ID.
• PN_VAR_GRP_DATES_ALL — group date master; joined on GRP_DATE_ID.
• PN_VAR_DEDUCT_ARCH_PK / PN_VAR_DEDUCT_ARCH_U1 — the primary key constraint and unique index enforcing DEDUCT_ARCH_ID uniqueness.
• GL_CODE_COMBINATIONS — resolved through GL_ACCOUNT_ID for account descriptions.
• PERIOD reference tables — resolved through PERIOD_ID for period name reporting.
• Property Manager batch import programs — the concurrent processes that populate this archive table during import execution.