Search Results create_final_intersect_rows

Overview

BEN_PD_COPY_TO_BEN_TWO is an Oracle EBS Advanced Benefits (BEN) PL/SQL package owned by the APPS schema. Its file header (bepdccp2.pkh, version 120.1.12000000.1, dated January 2007) reflects a TCS-driven enhancement to the Product Delivery Workbench (PDW) integration, indicating the package was extended to support copy-entity transaction processing within the Benefits configuration workbench. The package operationalizes the "copy to BEN" phase of a copy-entity transaction—the process by which a user purports to duplicate a Benefits object setup (for example, a program, plan, or eligibility profile) into a target business group.

Functionally, the package orchestrates the movement of transactional rows through staging and intersect layers and into the final Benefits tables. The TCS enhancement introduces the global cursor g_copy_entity_txn, joined against PQH_TRANSACTION_CATEGORIES, together with package globals g_copy_entity_txn_id and g_row_type_cd, to cache the row type of the currently processed transaction and avoid redundant database queries when the copy entity transaction identifier remains unchanged. The source comments explicitly caution that this cursor should be used only within the body of this package to preserve modularity.

Key Procedures and Functions

• SET_MAPPING — Establishes the mapping context for the copy-entity operation. It accepts the copy entity transaction identifier and is presumed to initialize the package globals consulted by the intersect-row logic, allowing downstream procedures to resolve source-to-target object relationships.
• CREATE_FINAL_INTERSECT_ROWS — Generates the final intersect rows for a copy transaction. Its parameter list includes a validation flag, the copy entity transaction identifier, an effective date, optional prefix/suffix text, a reuse-object flag, a target business group identifier, and a prefix/suffix code. It writes the terminal intersect set used to persist the copied Benefits configuration.
• CREATE_PGM_INTERSECT_ROWS — Creates program-level intersect rows. This procedure consumes the g_copy_entity_txn cursor and the associated globals to determine the row type without repeated queries, applying the same standard parameter set as the final intersect routine.
• CREATE_STG_TO_BEN_ROWS — Transfers staged rows into the Benefits tables. It shares the common signature (validation flag, copy entity transaction ID, effective date, prefix/suffix text, and related control parameters) and represents the concluding stage of the copy pipeline.

Tables Accessed

The package accesses a broad set of APPS synonyms. Control and transaction metadata reside in PQH_COPY_ENTITY_TXNS and PQH_TRANSACTION_CATEGORIES, which together determine the row type driving copy behavior. Results are written to BEN_COPY_ENTITY_RESULTS. Benefit configuration tables referenced include BEN_ACRS_PTIP_CVG_F, BEN_APLD_DPNT_CVG_ELIG_PRFL_F, BEN_BNFT_PRVDR_POOL_F, BEN_BNFT_RSTRN_CTFN_F, BEN_CUSTOM_REGION_ITEMS, BEN_CWB_WKSHT_GRP, BEN_DSGN_RQMT_F, BEN_DSGN_RQMT_RLSHP_TYP, BEN_ELIG_TO_PRTE_RSN_F, BEN_ENRT_CTFN_F, BEN_ENRT_PERD, and BEN_ENRT_PERD_FOR_PL_F. These cover across-plan coverage, dependent eligibility profiles, provider pools, benefit restrictions, design requirements, enrollment certifications, and enrollment periods.

Usage Notes

BEN_PD_COPY_TO_BEN_TWO is an internal (OTHER-classified) API rather than a public interface. It is typically invoked indirectly through the Benefits Product Delivery Workbench when a user submits a copy-entity action, or via related Benefits packages; ETRM metadata records four other packages referencing it. Because the documented procedures are not registered concurrent programs in their own right, custom code should not call them directly unless it first establishes a valid COPY_ENTITY_TXN_ID through the standard Benefits copy framework. Developers extending the copy flow should observe the source warning that the g_copy_entity_txn cursor is intended solely for this package body. The identical signature across the intersect and staging procedures suggests they are called in sequence—program intersect, final intersect, then staging-to-BEN—under a single effective date and target business group context, with the prefix/suffix parameters controlling object naming on copy.