Search Results refresh_pl_dsgn

Overview

APPS.BEN_CWB_PL_DSGN_PKG is a PL/SQL package within the Oracle EBS Advanced Benefits (OLTP) and Compensation Workbench (CWB) subsystem. Its name reflects its role: it manages the Plan Design (PL_DSGN) slice of Compensation Workbench data. Compensation Workbench allows organizations to allocate compensation components such as salary, bonus, and stock across a population of participants, and it depends upon Benefits plan design and enrollment data being consolidated into dedicated CWB staging tables. This package provides the programmatic glue that derives plan attributes, performs currency conversion, refreshes the CWB plan-design table from date-tracked Benefits source tables, and removes plan-design rows when they are no longer required. The header comment (bencwbpl.pkh 120.0) indicates the package has been stable since the early 11i/12.x line and is present, unchanged in structure, in both EBS 12.1.1 and 12.2.2. ETRM classifies the object as API classification OTHER, meaning it is a supporting internal utility package rather than a formally published public API.

Key Procedures and Functions

• GET_ACTUAL_FLAG — Determines whether a given plan is a standalone "actual" plan or whether it belongs to a group plan, returning a flag as a VARCHAR2. It evaluates the plan identifier together with the group plan identifier at a supplied effective date, which is important because CWB treats group-plan roll-ups differently from individual plan allocations.
• GET_EXCHG_RATE — Computes the exchange rate between a from-currency and a to-currency by delegating to HR_CURRENCY_PKG. If no rate information is available for the effective date and business group, the function returns a default of 1, effectively a one-to-one conversion. This supports CWB displays and allocations where participants and plans operate in differing currencies.
• REFRESH_PL_DSGN — Refreshes the BEN_CWB_PL_DSGN table. It accepts a group plan identifier, a life-event occurred date, an effective date, and a refresh-always directive. The effective date is used when querying date-tracked Benefits tables; the package documentation notes that the enrollment period freeze date in BEN_ENRT_PERD takes precedence when populated, otherwise the supplied effective date governs. The refresh-always parameter forces rebuilding of rows even where the data appears current.
• DELETE_PL_DSGN — The procedure matching the user's search term. It removes plan-design rows for a plan from the CWB plan-design table, allowing stale or superseded plan configuration to be purged before a fresh refresh cycle or when a plan is withdrawn from Compensation Workbench processing.

Tables Accessed

The package reads and writes the central staging table BEN_CWB_PL_DSGN, and additionally references BEN_CWB_PERSON_INFO for participant-level context. Source Benefits data is drawn from date-tracked plan and enrollment tables, including BEN_PL_F, BEN_OIPL_F, BEN_OPT_F, BEN_ACTY_BASE_RT_F, BEN_POPL_ENRT_TYP_CYCL_F, BEN_PL_EXTRA_INFO, and the period tables BEN_ENRT_PERD, BEN_WTHN_YR_PERD, and BEN_YR_PERD. Currency conversion draws on FND_CURRENCIES and DUAL, and PLITBLM supplies the PL/SQL index-by table type used internally for bulk collections.

Usage Notes

BEN_CWB_PL_DSGN_PKG is invoked indirectly rather than from end-user forms. It is referenced by five other packages, indicating it is called by higher-level Compensation Workbench processing packages that orchestrate plan-design population during enrollment-period setup, life-event processing, or batch refresh. The DELETE_PL_DSGN procedure is typically executed as a preparatory step before a full REFRESH_PL_DSGN run, ensuring duplicate or obsolete rows do not persist in BEN_CWB_PL_DSGN. Customizations should avoid calling these routines directly against production data without first validating the group plan identifier and effective dates, because the refresh logic depends on date-tracked Benefits records and enrollment period freeze dates. Because the package is classified as OTHER rather than a published API, Oracle does not guarantee signature stability across releases, so any custom code wrapping these procedures should be reviewed when applying EBS patches or upgrading between 12.1.1 and 12.2.2.