Search Results ben_ext_smart_total




Overview
Explore in AI Chat

Overview

The APPS.BEN_EXT_SMART_TOTAL package is a component of the Oracle E-Business Suite Advanced Benefits (Oracle HRMS) module. Its documented purpose is to generate smart totals for the header and trailer sections of benefit extract output files. Benefit extracts in Oracle EBS produce flat files containing enrollment, eligibility, and related benefit data that are transmitted to carriers, third-party administrators, and payroll providers. These files typically contain summary or aggregate values — such as record counts, monetary sums, or grouped totals — in header and trailer records.

The "smart" designation reflects the package's ability to compute conditional totals based on configurable criteria rather than simple numeric aggregation. The package is declared AUTHID CURRENT_USER, meaning it executes with the privileges of the calling user, and it was introduced in 1998 with the header identifier benxsttl.pkh 120.0. The source metadata indicates the package is associated with the Benefit Extract Smart Totals feature, whose historical revisions added conditional logic, formatting masks, and sub-grouping support over time.

Key Procedures and Functions

The ETRM metadata documents a single procedure in this package:

  • CALC_SMART_TOTAL — This is the core procedure that computes a smart total value for a benefit extract. Based on the documented parameter names in the package specification, it accepts an extract result identifier, a total function code (defining the aggregation operation), identifiers for the total sum data element and the conditional data element, the base extract data element, a format mask code, an extract file identifier, a business group identifier, and two optional sub-grouping values (p_group_val_01 and p_group_val_02). It returns the computed total through an OUT NOCOPY parameter named p_smart_total.

The package history notes that an earlier get_value function was moved to benxutil.pkh in version 115.3, and that the p_frmt_mask_cd parameter was added to the procedure in version 115.4. The multiple-conditions enhancement and the sub-grouping parameters (p_group_val_01 and p_group_val_02) were added in later revisions to support subheader calculations.

Tables Accessed

The package references the following tables through APPS synonyms:

  • BEN_EXT_DATA_ELMT — Defines the extract data elements available for totaling, including the total function and conditional logic attached to each element.
  • BEN_EXT_DATA_ELMT_IN_RCD — Maps data elements to extract record structures, identifying where in the extract layout the total is to be placed.
  • BEN_EXT_WHERE_CLAUSE — Stores configurable WHERE clause fragments used to restrict the rows included in a conditional total.
  • DBMS_SQL — The Oracle-supplied dynamic SQL package, used to construct and execute dynamic queries at runtime against the extract result set.

Usage Notes

BEN_EXT_SMART_TOTAL is not a user-facing API; it is invoked internally during benefit extract execution. The extract process, driven by configuration in the Benefit Extracts setup forms, calls CALC_SMART_TOTAL when it encounters header or trailer total definitions in the extract layout. The procedure assembles and executes dynamic SQL against the extract results to compute the requested total, applies the configured format mask, and returns the formatted value to the extract generator for inclusion in the output file.

The referenced-by metadata indicates that one other package calls this package, confirming it operates within a controlled internal call chain rather than being exposed as a public interface. Customizations that require totals in extract headers or trailers should be implemented through the standard extract configuration — defining data elements, total functions, and where-clause conditions — rather than by modifying or directly invoking this package. Direct calls are possible from custom PL/SQL, but doing so risks incompatibility across patches because the package signature and internal logic are owned by Oracle support.