Results for “pn_var_grp_dates_u1”

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

Overview

PN.PN_VAR_GRP_DATES_ALL is a transactional table in the Oracle E-Business Suite Property Manager (PN) schema that stores the critical dates associated with variable rent agreements. Rows are produced by the periods generation program, which derives them from information maintained in PN_VAR_DATES_ALL. Each row represents a "group date" — a computed scheduling element that drives invoicing, reporting, and volume or deduction processing for variable rent contracts. The table is partitioned by organization (ORG_ID) and resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10.

Within Oracle EBS 12.1.1 and 12.2.2, this table occupies the operational core of the variable rent engine. Group start and end dates, invoice start and end dates, the group date itself, the invoice date, and the proration factor are all persisted here so that downstream programs — billing, volume history capture, rent summarization, and variance analysis — have a stable, date-keyed anchor. From a heuristic Data Vault modeling perspective, the mined foreign-key structure classifies this object as hub-leaning: GRP_DATE_ID behaves as a durable business key, while variable rent, period, and organizational attributes function as descriptive context.

Key Information Stored

The surrogate primary key is GRP_DATE_ID (PN_VAR_GRP_DATES_PK), which uniquely identifies each group dates row. The unique index PN_VAR_GRP_DATES_U1 also enforces uniqueness on GRP_DATE_ID, making it the sole documented business-key candidate. The most significant columns include:

• VAR_RENT_ID — associates the row with a variable rent record in PN_VAR_RENTS_ALL.
• PERIOD_ID — associates the row with a period record in PN_VAR_PERIODS_ALL.
• GRP_START_DATE / GRP_END_DATE — the group date range, derived from PN_VAR_RENT_DATES_ALL by the generate periods program.
• GROUP_DATE — the derived group date computed from the variable rent dates table.
• REPTG_DUE_DATE — the reporting due date for the group period.
• INV_START_DATE / INV_END_DATE / INVOICE_DATE / INV_SCHEDULE_DATE — the invoice window and scheduled billing date.
• PRORATION_FACTOR — the factor applied when a period is split across group dates.
• ACTUAL_EXP_CODE / FORECASTED_EXP_CODE / VARIANCE_EXP_CODE — expenditure classification codes supporting variance reporting.
• ORG_ID — the operating unit partition key.
• Standard WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) — audit trail fields.

Common Use Cases and Queries

The most frequent access path is by VAR_RENT_ID (PN_VAR_GRP_DATES_N1) or PERIOD_ID (PN_VAR_GRP_DATES_N2). A typical query retrieves billing dates for a variable rent agreement:

• SELECT grp_date_id, group_date, inv_start_date, inv_end_date, invoice_date, proration_factor FROM pn_var_grp_dates_all WHERE var_rent_id = :p_var_rent_id AND org_id = :p_org_id ORDER BY group_date;
• SELECT g.grp_date_id, g.group_date, p.period_name FROM pn_var_grp_dates_all g, pn_var_periods_all p WHERE g.period_id = p.period_id AND g.period_id = :p_period_id;

Reporting teams use this table to reconcile invoiced versus scheduled dates, to validate that proration factors sum correctly across a period, and to drive variance analysis via the expenditure code columns. Because rows are regenerated by the periods generation program, verification queries commonly check for orphaned group date rows or mismatches between GRP_START_DATE/GRP_END_DATE and the corresponding PN_VAR_RENT_DATES_ALL entries.

Related Objects

PN_VAR_GRP_DATES_ALL sits between parent variable rent and period definitions and a broad set of dependent transactional tables. The most significant related objects are:

• PN_VAR_RENTS_ALL — joined on VAR_RENT_ID; defines the variable rent agreement.
• PN_VAR_PERIODS_ALL — joined on PERIOD_ID; supplies period context.
• PN_VAR_DEDUCTIONS_ALL — references GRP_DATE_ID; holds variable rent deductions.
• PN_VAR_RENT_SUMM_ALL — references GRP_DATE_ID; stores summarized rent amounts.
• PN_VAR_VOL_HIST_ALL — references GRP_DATE_ID; captures volume history per group date.
• PN_VAR_REPORT_DATES_ALL — references GRP_DATE_ID; supports reporting date derivation.
• PN_VAR_TRX_HEADERS_ALL — references GRP_DATE_ID; links transactions to group dates.
• PN_VAR_VOL_ARCH_ALL / PN_VAR_DEDUCT_ARCH_ALL — archival counterparts that retain GRP_DATE_ID references.

These relationships confirm that GRP_DATE_ID acts as the principal integration point for variable rent downstream processing, and that any data repair or purge operation on PN_VAR_GRP_DATES_ALL must account for these dependent foreign keys.