Results for “fte_message_address”

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

Overview

FTE_MESSAGE_ADDRESS is a transactional table in the FTE (Transportation Execution) schema of Oracle E-Business Suite, validated in releases 12.1.1 and 12.2.2. Its documented purpose is to store the addresses that appear within inbound and outbound tracking messages exchanged with trading partners during transportation execution. Tracking messages—status updates, shipment notifications, tenders, and event feeds—frequently carry origin, destination, billing, or contact addresses, and this table persists each such address as an independently addressable row linked back to the parent message context through the partner reference.

The object carries a surrogate primary key, FTE_MESSAGE_ADDRESS_PK, defined on the ID column. The single documented foreign key maps ADDRESS_TO_ID to FTE_MESSAGE_PARTNER, indicating that addresses are resolved against the partner master rather than stored as unstructured free text alone. From a Data Vault modeling perspective, the metadata classification is satellite-leaning: the table holds descriptive, multi-valued attributes (address lines, contact points) that change over time, hanging off a business key represented elsewhere. This classification is a heuristic suggestion derived from the foreign key structure and should not be treated as a prescriptive design mandate.

Key Information Stored

The table is physically wide, with 46 documented columns in ETRM 12.2.2, reflecting the need to capture complete postal and contact detail for each address occurrence. The most significant columns are:

• ID — surrogate primary key (FTE_MESSAGE_ADDRESS_PK); no natural business key is documented.
• ADDRESS_TO_ID — foreign key to FTE_MESSAGE_PARTNER; the principal business-key candidate and join anchor.
• ADDRESS_TO_TABLE — discriminator identifying which entity type the address points to, supporting polymorphic resolution.
• ADDR_TYPE — classification of the address (for example, ship-from, ship-to, or bill-to).
• ADDR_LINE1 through ADDR_LINE9 — multiline street address components, accommodating long international formats.
• ADDR_CITY, ADDR_STATE, ADDR_POSTAL_CODE, ADDR_COUNTRY, ADDR_REGION, ADDR_COUNTY — geographic components used for routing, rating, and geography validation.
• ADDR_TAX_JURISDICTION — tax jurisdiction associated with the location.
• TELEPHONE1 through TELEPHONE9 and FAX1 through FAX9 — repeating contact number slots captured from the message payload.
• TRANSACTION_ID — correlates the row to the originating transaction or message event.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

Common Use Cases and Queries

Typical usage centers on reconstructing the address content of a received or transmitted tracking message, validating partner location data, and feeding downstream shipment or delivery reporting. A common pattern joins the address rows to the partner master and back to the parent message:

• Retrieve all addresses for a partner: SELECT * FROM fte.fte_message_address WHERE address_to_id = :partner_id;
• Resolve only ship-to addresses: add AND addr_type = 'SHIP_TO'.
• Join to partners: SELECT a.id, a.addr_city, a.addr_state, p.* FROM fte.fte_message_address a, fte.fte_message_partner p WHERE a.address_to_id = p.id;
• Audit recent captures: filter on creation_date or transaction_id to trace a specific message run.

Reporting uses include partner address quality checks, duplicate detection across address lines, and reconciliation of message-supplied addresses against the master partner record. Because telephone and fax columns repeat in nine slots, queries often pivot or use CASE expressions to surface the first populated value.

Related Objects

• FTE_MESSAGE_PARTNER — referenced by ADDRESS_TO_ID; the parent partner record for each address.
• FTE_MESSAGE — the tracking message header that drives address capture, joined via TRANSACTION_ID.
• FTE_MESSAGE_ADDRESS_PK — the primary key constraint on ID.
• FTE_MESSAGE_PARTNER_PK — the referenced unique key on the partner table.
• FTE_TRANSACTION — likely transaction context underlying TRANSACTION_ID.
• FTE_LOCATION / HZ_LOCATIONS — master location entities against which message addresses are often compared.
• FTE_LOOKUPS — value sets supplying ADDR_TYPE codes.