Search Results create_program_result

Overview

APPS.BEN_PDW_COPY_BEN_TO_STG is a staging and data-warehouse copy utility that belongs to the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite. Its principal business function is to copy Benefits configuration and eligibility data from the operative BEN schema into staging tables, where it can be processed by downstream extraction, transformation, and load (ETL) routines. The package header carries the revision identifier bepdwstg.pkh 120.7.12010000.1, which places its last shipped change in the 12.1.1 code line; the same header remains valid in 12.2.2 because the Benefits data model was not materially restructured between the two releases.

The package supports several distinct copy scenarios. It can copy a single plan type record, copy a plan record together with its plan type, copy an entire plan hierarchy and its child rows, and copy a plan year period record. Many of the procedures return a copy entity result identifier through an OUT NOCOPY parameter, which links the copied rows back to the row in BEN_COPY_ENTITY_RESULTS that governs the copy transaction. This result identifier is the anchor that caller packages and concurrent programs use to reconcile staging content with its source.

Within the ETRM classification this package is registered as an OTHER API, meaning Oracle does not publish it as a supported public interface. It is an internal implementation package referenced by three other packages in the Benefits family.

Key Procedures and Functions

ETRM documents 37 procedures and functions. Among the most significant are the copy routines that give the package its name.

• copy_pl_typ_record — copies a plan type record from BEN_PL_TYP_F into the staging area and returns the copy entity result identifier for the new transaction.
• copy_pln_record_pcp — copies a plan record and invokes the plan-copy procedure chain; it is the entry point used when a complete plan definition must be replicated.
• copy_pln_record_all — the broadest copy routine; it copies the plan, its plan type, and all dependent child records, returning both a plan-level and plan-type-level result identifier.
• remove_dpnt_rows — deletes all dependent rows for a given foreign-key identifier, using a table alias to identify the parent table. This is the cleanup counterpart to the copy routines.
• create_ler_result — copies a BEN_LER_F (learning/eligibility) record and its children into staging. It exposes validation, effective date, business group, and number-of-copies controls.
• create_elig_prfl_results, create_dep_elig_crtr_results, create_dep_elpro_results, and create_vapro_results — generate staging results for eligibility profiles, their criteria, dependent eligibility, and variable-activity profiles respectively.
• get_prfl_name and get_dpnt_prfl_name — name-resolution helpers that translate profile identifiers into display names during staging.
• get_stage_object_name, get_balance_name, and get_currency — metadata lookups used to label staged objects consistently.
• copy_bnft_bal and copy_drvd_factor / fetch_drvd_factor_result — copy benefit balance and derived factor definitions into staging.
• pre_processor, interim_coverage_lookup, max_sequence, and dump_elig_prfls — orchestration, sequencing, and diagnostic helpers that support the copy runs.

The header also declares three table-alias constants (CPP, PLN, LPR1) and two DML operation constants (REUSE, INSERT) that drive how the copy logic decides between inserting new staging rows and reusing existing ones.

Tables Accessed

The package reads from and writes to the following documented tables through APPS synonyms.

• BEN_PL_TYP_F and BEN_PL_TYP_OPT_TYP_F — plan type and plan type option type definitions.
• BEN_PL_F — the plan definition itself, the primary object of the copy routines.
• BEN_PGM_F — program definitions, used to resolve the owning program of a plan.
• BEN_ELIGY_PRFL_F — eligibility profiles copied by the eligibility result routines.
• BEN_BENFTS_GRP, BEN_BNFTS_BAL_F, BEN_OIPL_F, and BEN_PTIP_F — benefit groups, balances, and plan-in-program / plan-type-in-program relationships.
• BEN_POPL_YR_PERD and BEN_POPL_YR_PERD_S — plan year period definitions and their sequence surrogate.
• BEN_PER_INFO_CHG_CS_LER_F and BEN_PER_INFO_CHG_CS_LER_F_S — person information change life-event records and the sequence table.
• BEN_PSTL_ZIP_RNG_F — postal ZIP range criteria used in eligibility processing.
• BEN_COPY_ENTITY_RESULTS — the result table into which every copy transaction writes its result identifier.

Usage Notes

BEN_PDW_COPY_BEN_TO_STG is an internal worker package rather than a user-facing API. It is normally invoked indirectly by Benefits forms and concurrent programs that perform plan or eligibility copies, and it is explicitly referenced by three other packages in the application. Because the ETRM classification is OTHER, Oracle does not guarantee the procedure signatures across patches, and custom code that calls it directly risks breakage on upgrade.

The package is primarily relevant to data-warehouse and staging scenarios in which Benefits configuration must be replicated outside the source business group. Callers must supply a valid effective date, business group identifier, and copy entity transaction identifier, and should capture the returned copy entity result identifier so that the staged rows can be joined back to their governing copy transaction. The DML constants REUSE and INSERT indicate that the package can, under certain conditions, update an existing staging result rather than insert a duplicate; this behavior is governed by the copy entity transaction context. In 12.1.1 and 12.2.2 no distinct version of this package is shipped, so the same logic applies to both releases.