Results for “cmpltd_dt”

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

Overview

BENBV_PRTT_ENRT_ACTN_V is an APPS-owned database view within the Oracle Advanced Benefits (BEN) product module. It exposes the participant enrollment action records that represent outstanding items a person must resolve in order for their elections or life events to be unsuspended — or, at minimum, to be considered complete. In the Oracle EBS 12.1.1 and 12.2.2 data models, this view functions as a reporting and integration surface for the enrollment action workflow, allowing implementers and downstream processes to query pending or satisfied action items without accessing the underlying _F table directly.

The view is read-only (defined WITH READ ONLY) and is filtered by the session's security profile through the HR_BIS security function, making it suitable for concurrent programs, OAF-based pages, and ad hoc reporting where row-level business group security must be enforced.

Underlying Base Objects

The view is defined over a single documented base object, BEN_PRTT_ENRT_ACTN_F (referenced via a SYNONYM), and it also invokes the HR_BIS package. The SELECT statement aliases the base table as PEA and projects columns from it, while the WHERE clause applies PEA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PEA.BUSINESS_GROUP_ID). This means the HR_BIS package supplies the current user's security profile business group ID; when no security profile value is returned, all business groups are returned, effectively disabling the filter.

Because the view references BEN_PRTT_ENRT_ACTN_F, any changes to the enrollment action data (creation, update of due or completion dates, or requirement flags) are immediately reflected in the view without additional synchronization.

Key Columns

• CMPLTD_DT — the date on which the enrollment action item was completed. A NULL value indicates the action is still outstanding. This is the column most commonly referenced when users search for "cmpltd_dt".
• DUE_DT — the date by which the action must be completed, used to drive reminders and delinquency tracking.
• RQD_FLAG / RQD_FLAG_M — the raw required flag and its decoded YES/NO meaning, produced via HR_BIS.BIS_DECODE_LOOKUP('YES_NO', PEA.RQD_FLAG).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetracked effective range of the action record.
• PRTT_ENRT_ACTN_ID — primary identifier for the participant enrollment action.
• ACTN_TYP_ID — identifies the type of action required.
• PRTT_ENRT_RSLT_ID — links to the enrollment result the action applies to.
• PER_IN_LER_ID — the person's life event record associated with the action.
• PL_BNF_ID and ELIG_CVRD_DPNT_ID — the plan/benefit and eligible covered dependent references.
• PROGRAM_UPDATE_DATE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID — concurrent program audit context.
• WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE.
• "_DF:PEA" — a descriptive flexfield reference token rather than a physical data column.

Common Use Cases and Queries

A frequent requirement is to find participants with unresolved action items. The following query lists outstanding actions with their due dates:

• SELECT prtt_enrt_actn_id, actn_typ_id, due_dt, cmpltd_dt FROM apps.benbv_prtt_enrt_actn_v WHERE cmpltd_dt IS NULL ORDER BY due_dt;

To measure historical throughput, filter by completion date range:

• SELECT actn_typ_id, COUNT(*) FROM apps.benbv_prtt_enrt_actn_v WHERE cmpltd_dt BETWEEN :from_date AND :to_date GROUP BY actn_typ_id;

Because the view applies the HR_BIS security profile automatically, queries submitted from a secured responsibility return only rows for the permitted business group. Reports that must span all business groups should be run under a responsibility without such a profile restriction, since the NVL effectively degrades the filter to allow all groups.