Results for “pstl_zip_rng_id”

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

Overview

BEN_PSTL_ZIP_RNG_D is a reporting view owned by the APPS schema within the BEN (Advanced Benefits) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents postal ZIP code range definitions used by Oracle Advanced Benefits to determine eligibility, plan availability, and rate qualification based on the participant's postal code. The view denormalizes the underlying base table by joining it to FND_USER so that the identity of the last person to update a range can be presented in human-readable form rather than as a numeric user identifier.

Because the object is a view rather than a table, it is read-only at the application layer and is intended strictly for inquiry and reporting. It exposes the descriptive and administrative attributes of ZIP ranges — effective dating, the from/to postal bound values, the business group partitioning key, and the descriptive flexfield structure column RZR_ATTRIBUTE_CATEGORY. The suffix "_D" reflects the EBS convention for a detail-type view that carries the transactional columns of the underlying "_F" (fact/base) entity.

Underlying Base Objects

The view is defined over two documented objects, both referenced through synonyms in the APPS schema:

• BEN_PSTL_ZIP_RNG_F — the base table holding the postal ZIP range records. The view aliases this table as RZR and selects its primary key, effective dates, range boundaries, business group, DFF category, and audit columns.
• FND_USER — the Oracle Application Object Library users table, aliased as FUSER. It is joined with an outer join operator on USER_ID to LAST_UPDATED_BY, so a row is returned even when the updating user cannot be resolved.

The join predicate FUSER.USER_ID (+) = RZR.LAST_UPDATED_BY guarantees that no ZIP range is suppressed when its LAST_UPDATED_BY value is null or does not match a current FND_USER record. Notably, the view does not expose LAST_UPDATED_BY itself; it exposes the resolved USER_NAME in its place, while retaining LAST_UPDATE_LOGIN as a separate audit column.

Key Columns

• PSTL_ZIP_RNG_ID — surrogate primary key of the postal ZIP range record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective window during which the range definition is valid, enabling date-tracked queries against historic or future-dated configurations.
• FROM_VALUE / TO_VALUE — the lower and upper postal code boundaries defining the inclusive ZIP range.
• BUSINESS_GROUP_ID — the business group that owns the range, providing the multi-tenant partition for the definition.
• RZR_ATTRIBUTE_CATEGORY — the descriptive flexfield context (attribute category) assigned to the range record, identifying which DFF segments apply to that row. This is the object the user searched for under the term rzr_attribute_category.
• USER_NAME — the FND_USER user name of the person who last updated the record, resolved through the outer join.
• LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit and concurrency columns.
• OBJECT_VERSION_NUMBER — the optimistic locking token used by the underlying entity.

Common Use Cases and Queries

The view is typically queried to verify which postal ranges are active for a business group on a given date, to inspect the descriptive flexfield category associated with each range, and to audit which user last maintained a definition.

Active ranges for a business group as of the current date:

• SELECT pstl_zip_rng_id, from_value, to_value, effective_start_date, effective_end_date FROM ben_pstl_zip_rng_d WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date ORDER BY from_value;

Inspect the flexfield category and audit trail for a specific range:

• SELECT pstl_zip_rng_id, rzr_attribute_category, user_name, last_update_date FROM ben_pstl_zip_rng_d WHERE pstl_zip_rng_id = :p_id;

Locate the range that contains a supplied postal code:

• SELECT pstl_zip_rng_id, from_value, to_value, rzr_attribute_category FROM ben_pstl_zip_rng_d WHERE business_group_id = :p_bg_id AND :p_zip BETWEEN from_value AND to_value AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because all access is read-only and the outer join to FND_USER may return a null user name, queries relying on USER_NAME should be written to tolerate nulls. Reports and extracts joining this view to BEN eligibility or rate configuration tables should always constrain by BUSINESS_GROUP_ID and by the effective date window to avoid returning obsolete or out-of-tenant ranges.