Results for “ddr_attribute30”

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

Overview

BEN_DSGN_RQMT_V is a view in the APPS schema belonging to the BEN (Advanced Benefits) product module in Oracle E-Business Suite 12.1.1 and 12.2.2. The object carries the metadata description "- Retrofitted," indicating that its definition was migrated or re-registered as part of an ETRM upgrade cycle rather than authored anew. The view exposes rows from the design requirement entity, one of the configuration structures used by Advanced Benefits to control how plan design elements, eligibility rules, and dependent coverage options behave for a given effective period.

The view is significant to users searching for mx_dpnts_alwd_num (maximum dependents allowed). That attribute is exposed by the view under the column name MX_DPNTS_ALWD_NUM, drawing from the underlying base table BEN_DSGN_RQMT_F. Its role in reporting and integration is to present an effective-dated, session-filtered projection of design requirements so that concurrent programs, extracts, and custom reports return only the row version valid as of the current application session date.

Underlying Base Objects

The documented referenced base objects for the view are BEN_DSGN_RQMT_F and FND_SESSIONS, both accessed through synonyms in the APPS schema. BEN_DSGN_RQMT_F is the design requirement base (F) table, and it supplies every functional and descriptive column selected by the view. FND_SESSIONS is the Applications session table, joined only to constrain the result set to the single effective version of each design requirement applicable to the current session.

The join condition filters FND_SESSIONS on SESSION_ID equal to USERENV('SESSIONID') and evaluates this between the design requirement's EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. As a result, the view behaves as a date-effective (datetracked) single-row-per-entity projection rather than a raw listing of all historical versions. Because the view selects DDR.ROWID as ROW_ID, consumers must treat rows as read-only; ROWID is a physical locator, not a stable primary key, and DML through the view is not supported for this pattern.

Key Columns

• DSGN_RQMT_ID — Primary identifier of the design requirement record, carried from BEN_DSGN_RQMT_F.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective bounds of the current version, also used in the FND_SESSIONS join.
• MX_DPNTS_ALWD_NUM — Maximum number of dependents allowed for the design requirement; this is the attribute matched by the search term mx_dpnts_alwd_num.
• MN_DPNTS_RQD_NUM — Minimum number of dependents required; paired with the maximum to define the allowed dependent range.
• NO_MN_NUM_DFND_FLAG / NO_MX_NUM_DFND_FLAG — Flags indicating that no minimum or no maximum number has been defined, so the numeric columns should not be interpreted as real limits in those cases.
• CVR_ALL_ELIG_FLAG — Coverage-all-eligible indicator affecting how the design requirement is applied.
• OIPL_ID, PL_ID, OPT_ID — Foreign keys linking the requirement to the organization, plan, and option that it configures.
• GRP_RLSHP_CD — Group relationship code qualifying the population affected by the requirement.
• DSGN_TYP_CD — Design type code identifying the category of the design requirement.
• BUSINESS_GROUP_ID — Business group partitioning column, essential for multi-organization filtering.
• DDR_ATTRIBUTE_CATEGORY and DDR_ATTRIBUTE1 through DDR_ATTRIBUTE30 — Descriptive flexfield segments for customer-defined extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — Standard WHO columns supporting audit, change tracking, and optimistic locking.

Common Use Cases and Queries

Typical usage is a read-only lookup of the currently effective dependent limits for a plan or option, either for a report, an extract, or a validation script. The pattern below returns design requirements with a defined maximum dependent count for a business group:

• SELECT dsgn_rqmt_id, pl_id, opt_id, mx_dpnts_alwd_num, mn_dpnts_rqd_num FROM apps.ben_dsgn_rqmt_v WHERE business_group_id = :p_business_group_id AND no_mx_num_dfnd_flag = 'N';
• SELECT dsgn_rqmt_id, mx_dpnts_alwd_num FROM apps.ben_dsgn_rqmt_v WHERE pl_id = :p_plan_id ORDER BY effective_start_date DESC;

Because the view already restricts to the session-effective row, no additional date predicate is required, though adding effective_start_date bounds is harmless for reporting parity. Joins are commonly made to benefit plan and option tables on PL_ID and OPT_ID, and to organization or group relationship tables on GRP_RLSHP_CD and BUSINESS_GROUP_ID. Where all historical versions are needed instead of the session-effective row, the base table BEN_DSGN_RQMT_F should be queried directly rather than this view. When NO_MX_NUM_DFND_FLAG is 'Y', MX_DPNTS_ALWD_NUM should be ignored by downstream logic, as no maximum has been defined for that row.