Search Results set_ext_lvls

Overview

APPS.BEN_EXTRACT is the core PL/SQL package body that drives the extraction engine for Oracle Advanced Benefits (OAB) in Oracle E-Business Suite 12.1.1 and 12.2.2. It provides the runtime logic that assembles outbound benefit data extracts — the files organizations generate to feed payroll interfaces, third-party carriers, benefits administrators, and statutory reporting. The package interprets the extract definitions configured in the BEN_EXT_* setup tables (extract definitions, records, fields, criteria, and data elements) and translates those definitions into a structured, sequenced output that conforms to the configured record layout.

Because benefit extracts must reconcile person-level, assignment-level, and plan-level information against criteria profiles, BEN_EXTRACT serves as the orchestration layer between the setup metadata and the person-processing routines found in companion packages such as BEN_EXTRACT_PERSON. The header comment references benxtrct.pkb 120.11.12010000.2, confirming its inclusion in the standard 12.1.1/12.2.2 code line. The package is classified as OTHER rather than a public API, meaning it is supporting infrastructure rather than a documented integration interface.

Key Procedures and Functions

ETRM documents three procedures within this package body.

• XTRCT_SKLTN — The "extract skeleton" routine. This establishes the foundational processing loop through the extract records defined for a given extract file, effectively providing the structural backbone that downstream logic walks in order to emit each record type.
• SET_EXT_LVLS — Sets up the extract levels for processing. This procedure establishes the hierarchy of level codes (person, assignment/records, plan, and similar) that determine the order and grouping in which benefit data is retrieved and formatted. It is the routine most directly associated with the search term set_ext_lvls and governs which level code drives subsequent criteria evaluation.
• SETUP_RCD_TYP_LVL — A private procedure that prepares record-type-level information for a specific extract file. Its logic identifies required detail-type records (rcd_type_cd = 'D') attached to the extract file, explicitly excluding header and trailer records ('H'/'T') from required-flag validation, a fix introduced under Bug 1702733. It also excludes person-level records (low_lvl_cd = 'P') from the required-record cursor and collects required sequencing information for detail and subheader records.

Tables Accessed

The package operates almost entirely against the extract definition schema. BEN_EXT_RCD supplies record definitions and their level codes, while BEN_EXT_RCD_IN_FILE maps records to specific extract files and carries the required flag and sequence number. BEN_EXT_FILE and BEN_EXT_DFN define the extract and its driving definition. BEN_EXT_FLD, BEN_EXT_DATA_ELMT, and BEN_EXT_DATA_ELMT_IN_RCD describe the fields and data elements that populate output records. Criteria handling draws on BEN_EXT_CRIT_TYP, BEN_EXT_CRIT_PRFL, and BEN_EXT_CRIT_VAL, while BEN_BATCH_RANGES supports batch partitioning. FND_NEW_MESSAGES and PLITBLM are used for message retrieval and PL/SQL table management respectively.

Usage Notes

BEN_EXTRACT is invoked indirectly. The concurrent program that runs benefit extracts calls into this package to build and validate the extract structure, after which person-level processing completes record population. Because ETRM lists it as referenced by eighteen other packages, changes here propagate broadly across the benefits extract subsystem. Custom code should not call these procedures directly; the supported approach is to configure extract definitions through the standard Benefits extract setup forms and let the concurrent process drive execution. Debugging is enabled through HR_UTILITY.DEBUG_ENABLED, which sets the package's global debug flag.