Search Results rzr_attribute_category




Overview
Explore in AI Chat

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

Overview

BEN_PSTL_ZIP_RNG_F is a core configuration table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. The table stores the range of postal codes used by Oracle Advanced Benefits when evaluating eligibility, rate, and variable rule criteria against a participant's residential or mailing address. Because many benefits programs are regionally differentiated — for example, health plan provider networks, HMO availability, or location-specific life insurance premiums — the postal code range definition is a foundational reference object that rule engines interrogate during benefits processing.

The table is registered in the BEN schema with the primary key BEN_PSTL_ZIP_RNG_F_PK, composed of PSTL_ZIP_RNG_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The presence of paired effective date columns indicates this is a date-tracked (date-effective) entity. The metadata describes the table as having 43 documented columns. From a Data Vault modeling perspective, the mined relationship profile classifies this object as standalone, suggesting it would most naturally be modeled as a hub (with PSTL_ZIP_RNG_ID as the business key) with an adjacent satellite capturing the effective-dated descriptive attributes. No foreign-key linkage to other tables was surfaced in the mined metadata, reinforcing its role as an independent reference/lookup dimension.

Key Information Stored

The table's identity is anchored by the composite primary key:

  • PSTL_ZIP_RNG_ID — surrogate identifier for the postal code range definition; the leading column of the PK and the closest analog to a business key.
  • EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — together with PSTL_ZIP_RNG_ID, form the business-key candidate enforced by the unique index BEN_PSTL_ZIP_RNG_F_PK, enabling date-effective versioning.
  • FROM_VALUE and TO_VALUE — the lower and upper bounds of the postal code range; together these define the interval matched against a participant's postal code.
  • BUSINESS_GROUP_ID — the enterprise/legal entity partition to which the range belongs, supporting multi-organization deployments.
  • RZR_ATTRIBUTE_CATEGORY and RZR_ATTRIBUTE1 through RZR_ATTRIBUTE30 — a descriptive flexfield (DFF) style attribute block (31 columns) allowing customer-specific extension of the range definition without schema changes.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard Who columns for auditability.
  • OBJECT_VERSION_NUMBER — optimistic locking control used by the Oracle Application Framework (OAF) and concurrent programs.

The distinction between the surrogate PSTL_ZIP_RNG_ID and the full unique business key matters for integration design: external systems should not rely on the surrogate identifier alone, because a single range may have multiple effective-dated versions.

Common Use Cases and Queries

Typical uses include auditing configured ranges, resolving which range covers a given postal code, and joining to benefits eligibility results to validate rule outcomes. A representative lookup is:

  • Retrieve the active range for a given code and date: SELECT PSTL_ZIP_RNG_ID, FROM_VALUE, TO_VALUE FROM BEN_PSTL_ZIP_RNG_F WHERE :p_zip BETWEEN FROM_VALUE AND TO_VALUE AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE AND BUSINESS_GROUP_ID = :p_bg_id;
  • Identify overlapping or gap ranges within a business group for data-quality reporting.
  • Extract the RZR_ATTRIBUTE columns for downstream reporting when a customer has extended the range model.
  • Reconcile ranges against plan-year effective dates during benefits open enrollment validation.

Reports should always constrain on BUSINESS_GROUP_ID and the effective date window, since the date-tracked design permits multiple concurrent rows per identifier.

Related Objects

Because the mined relationship profile reports this table as standalone, no direct foreign-key relationships were documented. In practice, the object participates in the wider Advanced Benefits rule framework and would be referenced by rule-evaluation logic associated with the eligibility and rate engines. Significant related objects include the BEN_PSTL_ZIP_RNG_F primary key constraint (BEN_PSTL_ZIP_RNG_F_PK) itself as the governing uniqueness rule, and the Advanced Benefits rule and eligibility tables that consume postal code range definitions when processing participant records. Join patterns should rely on PSTL_ZIP_RNG_ID or on the FROM_VALUE/TO_VALUE interval logic rather than on undocumented FK columns. Administrators should also treat the RZR_ATTRIBUTE flexfield context defined by RZR_ATTRIBUTE_CATEGORY as the extension point for any site-specific enrichment.