Results for “ce_upg_loc_rec”

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

Overview

CE_UPG_LOC_REC is a Cash Management (CE) module table in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It stores address records for bank branches, including the primary address flag that designates which address should be treated as the identifying location for a given bank entity. The table is part of the CE product's upgrade infrastructure: the CE_UPGRADE_ID column indicates that rows are populated and staged during the bank/branch data upgrade process, and UPGRADE_STATUS tracks the movement of each record through that process.

From a Data Vault modeling perspective, the mined foreign-key structure yields a heuristic classification of standalone. No FK relationships to parent entities were documented, so the row does not behave as a classic link (which would resolve two or more hubs) nor does it sit under a single parent hub in the metadata as provided. A standalone classification suggests the table is best modeled as an independent staging or reference set, keyed by its own composite identifier, rather than as a dependent satellite. This is a modeling suggestion, not a documented constraint, and should be validated against the actual upgrade flow before being relied upon.

Key Information Stored

The table's documented physical schema contains 23 columns. Its primary key, CE_UPG_LOC_REC_PK, is a composite key comprising CE_UPGRADE_ID, BANK_ENTITY_TYPE, and IDENTIFYING_ADDRESS_FLAG. A unique index, CE_UPG_LOC_REC_U1, covers the same three columns, making it the business-key candidate: the combination of upgrade run, bank entity type, and identifying-address flag uniquely identifies a staged address record.

• CE_UPGRADE_ID — identifies the upgrade batch or run that produced the record; first component of the primary key.
• BANK_ENTITY_TYPE — the type of bank entity (for example, bank versus branch) to which the address belongs; second key component.
• IDENTIFYING_ADDRESS_FLAG — flags the primary or identifying address for the entity; third key component and the column referenced in the table description.
• UPGRADE_STATUS — tracks the processing state of the staged address during the upgrade.
• COUNTRY, STATE, PROVINCE, COUNTY, CITY — geographic components of the branch address.
• ADDRESS1 through ADDRESS4 — the free-form address lines.
• ADDRESS_STYLE — the style or format applied to render the address.
• POSTAL_CODE — postal or ZIP code for the address.
• ADDRESS_LINE_PHONETIC — phonetic representation used for address matching or search.
• LOCATION_ID — the location identifier ultimately associated with the address.
• CREATED_BY_MODULE — the module that created the record, indicating the originating process.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns tracking who created and last modified each row.

Common Use Cases and Queries

The primary use case is monitoring and reconciling the Cash Management bank branch address upgrade. Report authors and support analysts query this table to confirm that every expected branch address was staged and processed, and to identify rows that remain in an incomplete UPGRADE_STATUS.

A typical reconciliation query lists staged addresses by upgrade run and status:

SELECT ce_upgrade_id, bank_entity_type, identifying_address_flag,
       country, city, postal_code, upgrade_status
FROM   ce.ce_upg_loc_rec
WHERE  ce_upgrade_id = :p_upgrade_id
ORDER  BY bank_entity_type, identifying_address_flag;

To find the identifying (primary) address for each bank entity within a run, filter on the flag:

SELECT bank_entity_type, country, city, address1, location_id
FROM   ce.ce_upg_loc_rec
WHERE  ce_upgrade_id = :p_upgrade_id
AND    identifying_address_flag = 'Y';

Exception reporting identifies addresses still pending after an upgrade window by selecting rows whose UPGRADE_STATUS is not terminal, and audit-oriented queries join the creation and update audit columns to determine when each staged row was last touched.

Related Objects

The documented relationship data classifies CE_UPG_LOC_REC as standalone, meaning no explicit foreign keys to parent tables are recorded in the metadata. The following related objects are therefore identified by their functional role in the Cash Management bank and branch model rather than by documented FK constraints, and joins should be validated against the actual data model in each environment.

• CE_BANK_BRANCHES — the master bank branch table; the staged addresses in CE_UPG_LOC_REC are intended to populate or update branch address data, linked through the bank entity and location identifiers.
• CE_BANKS — the parent bank entity referenced conceptually by BANK_ENTITY_TYPE.
• HZ_LOCATIONS — the Trading Community Architecture location table where finalized address data resides via LOCATION_ID.
• CE_UPGRADE (or the corresponding CE upgrade control table) — the header object that defines the CE_UPGRADE_ID used as the first component of the primary key.
• CE_UPG_* staged tables — sibling staging objects populated in the same upgrade run, joined on CE_UPGRADE_ID.
• CE_UPG_LOC_REC_PK / CE_UPG_LOC_REC_U1 — the primary key constraint and unique index that enforce the composite business key.

Because no FK metadata is documented, integrators should confirm actual join paths against the ETRM and the live schema before relying on any of the relationships above for production reporting.