Results for “pl_typ”

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

Overview

APPS.BEN_PL_TYP_OPT_TYP_D is a reporting view in the Oracle E-Business Suite Benefits module. It presents configuration data that links a plan type (PL_TYP) to an option type (OPT_TYP) through the intersection entity BEN_PL_TYP_OPT_TYP_F. The view is a "_D" (denormalized detail) object, meaning it flattens several foreign-key references into human-readable descriptive values so that downstream reports, extracts, and integrations do not need to perform their own joins to lookups, plan types, and option types.

Functionally, the view answers the question: for a given plan type and effective date, which option types are permitted, and what are their descriptive names and legislative context? Because Benefits configuration is date-tracked (DateTrack), the view exposes both EFFECTIVE_START_DATE and EFFECTIVE_END_DATE so consumers can reason about the validity window of each row.

The view belongs to the APPS schema and is defined primarily for inquiry and reporting rather than transaction processing. No DML should be issued against it; rows in the underlying tables should be maintained through the standard Benefits setup forms or the public Benefits APIs.

Underlying Base Objects

The ETRM metadata documents the following base and referenced objects:

• BEN_PL_TYP_OPT_TYP_F — the driving intersection table (aliased PON) holding the plan-type-to-option-type relationship, its surrogate key, and DateTrack columns.
• BEN_PL_TYP_F — plan type definitions (aliased PL_TYP), providing the plan type name.
• BEN_OPT_F — option type definitions (aliased OPT), providing the option name.
• HR_LOOKUPS — lookup view (aliased PL_TYP_OPT_TYP) restricted to lookup_type = 'BEN_OPT_TYP', supplying the meaning of the option-type code.
• FND_USER_VIEW — resolves LAST_UPDATED_BY to a user name.
• HR_API — a referenced package used in the Benefits date-effective logic.

The joins to BEN_OPT_F, BEN_PL_TYP_F, HR_LOOKUPS, and FND_USER_VIEW are all outer joins (the (+) operator appears on the lookup side). This design ensures a row from BEN_PL_TYP_OPT_TYP_F is returned even if a descriptive name cannot be resolved. The BEN_OPT_F and BEN_PL_TYP_F joins are additionally constrained so the intersection row's EFFECTIVE_START_DATE falls between the referenced row's effective start date and itself — a common DateTrack correlation pattern that picks the effective-dated descriptive record valid on that date.

Key Columns

• ROW_ID — ROWID of the underlying BEN_PL_TYP_OPT_TYP_F row, useful as a surrogate identifier.
• PL_TYP_OPT_TYP_ID — primary key of the plan type / option type intersection row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — DateTrack validity window for the relationship.
• MEANING — decoded meaning of the option type lookup code from HR_LOOKUPS (lookup_type 'BEN_OPT_TYP').
• NAME (from OPT) — the option type name from BEN_OPT_F.
• NAME (from PL_TYP) — the plan type name from BEN_PL_TYP_F.
• LAST_UPDATE_DATE — audit timestamp for the latest change.
• USER_NAME — the application user who last updated the row, resolved via FND_USER_VIEW.
• LEGISLATION_CODE — the legislation (country/regulatory context) under which the relationship applies.
• LEGISLATION_SUBGROUP — a finer legislative classification within the legislation code.

Common Use Cases and Queries

Typical uses include Benefits setup validation, data migration verification, and cross-reference extracts that need plan type / option type combinations for a given legislation.

• List all option types available for a given plan type and legislation.
• Confirm which combinations are effective on a particular date.
• Audit recently changed rows and who changed them.

Sample query:

SELECT ROW_ID,
      PL_TYP_OPT_TYP_ID,
      EFFECTIVE_START_DATE,
      EFFECTIVE_END_DATE,
      MEANING,
      USER_NAME,
      LEGISLATION_CODE
FROM APPS.BEN_PL_TYP_OPT_TYP_D
WHERE EFFECTIVE_START_DATE <= SYSDATE
  AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE >= SYSDATE)
  AND LEGISLATION_CODE = 'US';

Because the view is built with outer joins, results are safe for reconciliation but may return NULL descriptive columns where a referenced setup record is missing or not effective on that date. Filters on LEGISLATION_CODE and effective dates should always be applied to limit row volume.