Search Results igi_exp_num_schemes_u2




Overview
Explore in AI Chat

Overview

IGI.IGI_EXP_NUM_SCHEMES_ALL is a transactional setup table in the Oracle E-Business Suite Exchange Protocol (IGI) module. It stores the definition and runtime state of numbering schemes used to generate Exchange Protocol numbers for Dialog Units (DU) and Transmission Units (TU). The table is maintained through the Exchange Protocol Numbering Setup form and is the authoritative source for sequence generation, prefix/suffix formatting, and fiscal-year scoping of Exchange Protocol identifiers.

The table resides in the APPS_TS_TX_DATA tablespace and is documented as VALID in ETRM 12.2.2, with identical behavior in 12.1.1. A heuristic Data Vault classification mined from the foreign-key structure identifies this object as standalone. In Data Vault modeling terms, it can be treated as a hub-like reference entity keyed on NUM_SCHEME_ID, with the numbering attributes acting as descriptive content rather than participating in a classic hub/link/satellite chain.

Key Information Stored

The table contains 14 documented columns. The surrogate primary key is NUM_SCHEME_ID (NUMBER), a sequence-generated unique identifier. Two unique indexes constitute the business-key candidates:

The most significant descriptive columns are:

  • NUMBERING_TYPE — distinguishes schemes for Dialog Units versus Transmission Units.
  • NUMBERING_CLASS — identifies whether the scheme governs Order Numbering or Legal Numbering.
  • DU_TU_TYPE_ID — the Dialog Unit or Transmission Unit type to which the scheme applies.
  • FISCAL_YEAR — the fiscal year for which the scheme is valid, supporting year-over-year numbering resets.
  • NEXT_SEQ_VAL — the next sequence value to be assigned, the mutable counter driving number generation.
  • PREFIX and SUFFIX — VARCHAR2(100) formatting elements wrapped around the generated number.
  • ORG_ID — the operating unit (multi-org) identifier.
  • Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) provide auditability.

Common Use Cases and Queries

Typical uses include auditing active numbering schemes, diagnosing duplicate-number errors or sequence gaps, verifying fiscal-year rollover setups, and reporting on numbering configuration across operating units.

  • Retrieve the scheme applicable to a given DU/TU type, type, class, year, and operating unit: SELECT num_scheme_id, prefix, suffix, next_seq_val FROM igi.igi_exp_num_schemes_all WHERE numbering_type = :p_type AND numbering_class = :p_class AND du_tu_type_id = :p_dutu AND fiscal_year = :p_year AND org_id = :p_org_id;
  • Report all schemes for an operating unit and fiscal year: SELECT numbering_type, numbering_class, du_tu_type_id, prefix, suffix, next_seq_val FROM igi.igi_exp_num_schemes_all WHERE org_id = :p_org_id AND fiscal_year = :p_year ORDER BY numbering_type, numbering_class;
  • Detect schemes whose NEXT_SEQ_VAL is close to exhaustion or that have not advanced, indicating setup or generation problems.
  • Validate the uniqueness guaranteed by IGI_EXP_NUM_SCHEMES_U2 before inserting or copying scheme configurations between environments.

Related Objects

The table is classified as standalone from a FK perspective, so its primary relationships are through the business-key columns rather than declarative foreign keys. Relevant related objects include:

  • IGI_EXP_NUM_SCHEMES_U2 — the composite unique index on (NUMBERING_TYPE, NUMBERING_CLASS, DU_TU_TYPE_ID, FISCAL_YEAR, ORG_ID).
  • IGI_EXP_NUM_SCHEMES_U1 / IGI_EXP_NUM_SCHEMES_PK — unique index and primary key on NUM_SCHEME_ID.
  • Dialog Unit and Transmission Unit definition tables referenced indirectly via DU_TU_TYPE_ID.
  • FND_USER and FND_LOGINS — referenced by the CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN audit columns.
  • The Exchange Protocol Numbering Setup form, which is the primary maintenance interface for this table.
  • Exchange Protocol order and legal numbering processes that consume NEXT_SEQ_VAL during number generation.