Search Results abm_bor_hier_seq
Overview
APPS.MODIFY_ABM_SEQUENCES is a utility package body within the Oracle E-Business Suite Activity-Based Management (ABM) module. Its singular purpose is to realign Oracle sequences with the current high-water mark of the surrogate key columns they populate. In the ABM data model, several transactional and control tables maintain their own sequential identifier columns — for example, ABM_BATCH_CALCS uses M84_BATCH_PROG_ID_CTR, ABM_BOR_HIER uses M33_BOR_LINE_ID_CTR, and ABM_CALCULATIONS uses M03_CALC_PROC_ELEM_STEP_ID_CTR. When data is migrated, purged, or restored out of band, these counter columns and the corresponding database sequences can drift apart, producing duplicate-key or ORA-00001 errors. This package detects that drift and rebuilds the sequences so that the next generated number exceeds the maximum stored counter value. The header comment identifies the source file as abmseqb.pls (version 115.5, last shipped 2002/03/06), indicating this is long-standing maintenance code carried forward into EBS 12.1.1 and 12.2.2.
Key Procedures and Functions
The package exposes three documented program units, each performing one stage of the realignment workflow:
- GET_TABCOL_AND_SEQ — Accepts a sequence name and resolves it to the owning ABM table and its associated counter column through a hard-coded IF/ELSIF mapping. The documented mappings include ABM_BATCH_CALCS_SEQ to ABM_BATCH_CALCS.M84_BATCH_PROG_ID_CTR, ABM_BOR_HIER_SEQ to ABM_BOR_HIER.M33_BOR_LINE_ID_CTR, ABM_CALCS_LOG_SEQ to ABM_CALCS_LOG.M83_STEP_LOG_ID_CTR, ABM_CALCULATIONS_SEQ to ABM_CALCULATIONS.M03_CALC_PROC_ELEM_STEP_ID_CTR, ABM_CALC_PROCS_SEQ, ABM_CALC_PROC_STEPS_SEQ, ABM_CAL_EXT_COSTS_SEQ, and ABM_COMP_DS_VAL_SEQ. It then drives the remaining two procedures.
- MODIFY_TABLE_COLUMN_BY_VALUE — Modifies the stored value of the resolved counter column (incrementing it by the documented step of 5), producing the new high-water mark that the sequence must respect.
- CREATE_SEQUENCE_WITH_NEW_VALUE — Drops and recreates the named sequence using the incremented column value as the new starting point, ensuring subsequent NEXTVAL calls cannot collide with existing data.
No parameter lists are documented in the ETRM metadata; the source excerpt shows GET_TABCOL_AND_SEQ taking a single VARCHAR2 sequence name argument. Internally the body declares variables xSqlCode, xSqlErr, xUserException, table_column_name, table_name, connect_statement, and sequence_start_value.
Tables Accessed
The package reads and writes the ABM counter tables resolved by the mapping routine, including ABM_BATCH_CALCS, ABM_BOR_HIER, ABM_CALCS_LOG, ABM_CALCULATIONS, ABM_CALC_PROCS, ABM_CALC_PROC_STEPS, ABM_CAL_EXT_COSTS, and ABM_COMP_DS_VAL. In each case the access is targeted at the surrogate counter column (for example M84_BATCH_PROG_ID_CTR or T05_SEQUENCE_NUMBER). It also issues DDL against the corresponding Oracle sequence objects. No other packages are documented as referencing it, so its table footprint is limited to these ABM objects.
Usage Notes
Because the package uses dynamic SQL and DDL (creating sequences privileges), it is typically invoked by a DBA or system administrator rather than end-user forms. Common triggers include post-clone sequence synchronization, post-migration counter reconciliation, and troubleshooting of duplicate-key or ORA-01438/ORA-00001 errors in ABM transaction processing. Because the procedure increments the column value by 5 before rebuilding the sequence, callers should expect a deliberate gap in numbering and should invoke it only when the sequence is demonstrably behind the data, not during active concurrent ABM processing. The hard-coded sequence-to-column mapping means any new ABM sequence added in a later release will not be handled without a patch to this package body. Referenced by zero other packages, it is a leaf-level maintenance utility.