Results for “ce_uk_val_subs_codes”

24 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CE_UK_VAL_SUBS_CODES table is a Cash Management (CE) module data object that stores the substitute sorting codes applied during United Kingdom Domestic Account Number validations. In Oracle E-Business Suite Release 12.1.1 and 12.2.2, the CE module performs bank account validation routines for external bank accounts and internal bank accounts, and the UK domestic validation logic compares a submitted sort code against a defined substitution map. When a presented sort code is not itself a valid UK clearing sort code, the validation engine consults this table to determine whether an equivalent substitute sort code exists, allowing the account to be validated against the correct clearing institution rather than being rejected outright.

The table is owned by the CE schema and is documented as VALID in the ETRM repository. Its physical definition consists of nine columns, with two unique indexes documented as business-key candidates: CE_UK_VAL_SUBS_CODES_U1 on SUBSTITUTION_ID and CE_UK_VAL_SUBS_CODES_U2 on ORIG_SORT_CODE. From a Data Vault modeling perspective, the metadata classifies this object as standalone — a heuristic suggestion indicating that it functions as an independent reference or lookup structure rather than as a dependent satellite. The single documented foreign key relationship, SUBSTITUTION_ID → GMD_ITEM_SUBSTITUTION_HDR_B, is notable because the column name coincides with a substitution identifier also used in Oracle Process Manufacturing item substitution data; administrators should treat this relationship as a naming and structural linkage rather than assume a purely functional dependency within Cash Management.

Key Information Stored

The table's nine documented columns fall into three functional groups: validation mapping data, primary and business keys, and standard Oracle audit columns.

  • SUBSTITUTION_ID — The surrogate primary key of the row and the column underlying the unique index CE_UK_VAL_SUBS_CODES_U1. It also carries the documented foreign key to GMD_ITEM_SUBSTITUTION_HDR_B.
  • ORIG_SORT_CODE — The original UK sort code presented for validation. This is the second documented business-key candidate, enforced by the unique index CE_UK_VAL_SUBS_CODES_U2, which guarantees that only one substitution mapping may exist per originating sort code.
  • SUBS_SORT_CODE — The substitute sort code returned by the mapping, representing the valid clearing sort code to be used in place of the original during account number validation.
  • REQUEST_ID — The concurrent request identifier associated with the row's creation or maintenance, supporting traceability back to the concurrent program that performed the load or update.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Oracle Applications who-columns, recording the user and timestamp of row creation and the most recent modification, along with the login identifier of the updating session.

Together, ORIG_SORT_CODE and SUBS_SORT_CODE constitute the effective business content of the table, while the remaining columns provide identity, referential linkage, request-level traceability, and audit history.

Common Use Cases and Queries

The principal use case is diagnostic: when a UK bank account fails validation during bank account setup in Cash Management, the substitution map is inspected to confirm whether the presented sort code has a defined substitute. Reporting queries typically retrieve the full mapping set or resolve a single sort code.

  • Listing all active substitution mappings for review or migration between environments: SELECT substitution_id, orig_sort_code, subs_sort_code FROM ce.ce_uk_val_subs_codes;
  • Resolving a specific sort code during validation troubleshooting: SELECT subs_sort_code FROM ce.ce_uk_val_subs_codes WHERE orig_sort_code = :p_sort_code;
  • Auditing recent maintenance of the mapping table: SELECT orig_sort_code, subs_sort_code, last_updated_by, last_update_date FROM ce.ce_uk_val_subs_codes ORDER BY last_update_date DESC;
  • Tracing rows loaded by a particular concurrent program run: SELECT * FROM ce.ce_uk_val_subs_codes WHERE request_id = :p_request_id;

Because ORIG_SORT_CODE is uniquely indexed, joins or lookups keyed on that column return at most one substitute code, which makes the table suitable for use as an inline lookup in validation and reconciliation reports without the risk of row multiplication.

Related Objects

The documented relationship data for this object is intentionally narrow, and the following objects are the most significant for navigation and dependency analysis.

  • GMD_ITEM_SUBSTITUTION_HDR_B — Referenced through the documented foreign key on SUBSTITUTION_ID (CE_UK_VAL_SUBS_CODES.SUBSTITUTION_ID → GMD_ITEM_SUBSTITUTION_HDR_B).
  • CE_UK_VAL_SUBS_CODES_U1 — Unique index on SUBSTITUTION_ID, enforcing the surrogate key.
  • CE_UK_VAL_SUBS_CODES_U2 — Unique index on ORIG_SORT_CODE, enforcing the business key for the originating sort code.
  • CE_BANK_ACCOUNTS — The Cash Management bank account entity whose UK domestic validation routines consume the substitution mappings.
  • CE_BANK_BRANCHES — Holds institution branch and sort code data against which substituted sort codes are ultimately resolved.
  • FND_CONCURRENT_REQUESTS — Joins on REQUEST_ID to attribute rows to the concurrent program that loaded or updated them.

No additional foreign keys or dependent objects are documented in the ETRM metadata beyond the single relationship above; the table should therefore be treated as a standalone reference structure that is read by validation logic rather than one that participates in a broad transactional dependency chain.