Results for “enrt_ctfn_rqd_flag”

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

Overview

BENBV_PRTT_RT_VAL_CTFN_PRVDD_V is a reporting and integration view in the APPS schema belonging to the Oracle Advanced Benefits (BEN) product. Its purpose is to identify the certifications that have been provided for an activity rate within the benefits configuration model. In the Oracle EBS 12.1.1 and 12.2.2 data models, activity rates represent the parameters and rates that drive benefits calculations, and participants or plan configurations may be required to provide certification evidence before such a rate is authorized. This view surfaces the certification records associated with those rates, including the dates on which certifications were received and are due, together with the certification type and a flag indicating whether the certification is mandatory.

The view is defined with a WITH READ ONLY clause, indicating that it is intended strictly for query, reporting, and integration consumption rather than for transactional data manipulation. It also applies business group security through the HR_BIS.GET_SEC_PROFILE_BG_ID function, so results returned to a session are filtered to the business group established by the user's security profile. Because it exposes descriptive flexfield placeholders, lookup-decoded values, WHO audit columns, and ID columns, the view is well suited to concurrent program extracts, OBIEE or BI Publisher reporting layers, and interface programs that must reconcile certification data without joining the base table directly.

Underlying Base Objects

The view is defined over a single documented base object, BEN_PRTT_RT_VAL_CTFN_PRVDD, which is referenced in the APPS schema through a synonym. The view text aliases this table as RVC and selects all of its principal columns. A second documented referenced object is the HR_BIS package, which supplies supporting utility functions. Specifically, HR_BIS.BIS_DECODE_LOOKUP is called twice to translate the certification type code (lookup BEN_ENRT_CTFN_TYP) and the required flag (lookup YES_NO) into their user-facing meanings, and HR_BIS.GET_SEC_PROFILE_BG_ID supplies the security business group identifier used in the WHERE clause.

The relationship between view and table is therefore direct and one-to-one in terms of row cardinality: each row of BEN_PRTT_RT_VAL_CTFN_PRVDD produces one row in the view, subject to business group filtering and code translation. No aggregation or joining to other benefit tables occurs inside the view definition, so all foreign-key relationships, such as the link back to the parent activity rate record, must be resolved by the report or interface that consumes the view.

Key Columns

The columns exposed by the view fall into several functional groups. Certification timing is captured by ENRT_CTFN_RECD_DT, the date the certification was received, and ENRT_CTFN_DND_DT, the date by which the certification is due. These two columns are central to monitoring whether required certifications have been satisfied on time. ENRT_CTFN_TYP_CD holds the certification type code, while the corresponding computed column ENRT_CTFN_TYP_CD_M carries the decoded display value produced by BIS_DECODE_LOOKUP. Similarly, ENRT_CTFN_RQD_FLAG stores the Y/N indicator of whether the certification is required, with ENRT_CTFN_RQD_FLAG_M providing the decoded text.

Identifier columns include PRTT_RT_VAL_CTFN_PRVDD_ID, the primary key of the certification record; PRTT_RT_VAL_ID, the foreign key to the associated activity rate value; and BUSINESS_GROUP_ID, the business group that owns the row. Concurrent program context is provided by REQUEST_ID, PROGRAM_APPLICATION_ID, and PROGRAM_ID, while PROGRAM_UPDATE_DATE supports integration change detection. Standard WHO audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY — are also present. The "_DF:RVC" column is a descriptive flexfield placeholder that enables the BEN_PRTT_RT_VAL_CTFN_PRVDD_F flexfield context to be surfaced in forms and reports.

Common Use Cases and Queries

The most frequent use of this view is to report outstanding or received certifications against activity rates. Because the search term enrt_ctfn_recd_dt maps directly to a column in this view, users typically filter or sort on that date to review certification activity within a period. A representative query is:

• SELECT prtt_rt_val_id, enrt_ctfn_typ_cd_m, enrt_ctfn_recd_dt, enrt_ctfn_dnd_dt, enrt_ctfn_rqd_flag_m FROM benbv_prtt_rt_val_ctfn_prvdd_v WHERE enrt_ctfn_recd_dt BETWEEN :p_start AND :p_end ORDER BY enrt_ctfn_recd_dt DESC;
• To isolate mandatory certifications that remain outstanding, filter on enrt_ctfn_rqd_flag = 'Y' and compare enrt_ctfn_dnd_dt to SYSDATE.
• For integration extracts, select the ID columns, PROGRAM_UPDATE_DATE, and WHO columns to support incremental change tracking keyed on LAST_UPDATE_DATE.
• To join certification data back to the parent activity rate, relate PRTT_RT_VAL_ID to the activity rate value identifier in the corresponding benefits rate tables.

Because business group security is embedded in the view, reports built on it automatically respect the security profile of the executing user, reducing the need for additional data restriction logic in the reporting layer. This makes the view a reliable foundation for both operational monitoring and downstream data warehouse loads in Oracle EBS 12.1.1 and 12.2.2 environments.