Search Results tp_reference_ext2




Overview
Explore in AI Chat

Overview

WSH_RCO_ADDRESS_V is a Shipping Execution (WSH) view in Oracle E-Business Suite 12.1.1 and 12.2.2 that exposes recipient contact information combined with associated address details. Its name reflects the three-part join that defines it: RCO (RA_CONTACTS), C (RA_CUSTOMERS), and O (address). The view returns one row per contact-to-address relationship, enriched with customer name and address attributes and with the Trading Partner (TP) reference extension columns defined by Oracle's trading partner data model.

The view is significant because it surfaces TP_REFERENCE_EXT1 and TP_REFERENCE_EXT2, the indexed flexible reference fields on the trading partner address. These columns are commonly used by customers to store third-party identifiers such as EDI location codes, carrier account numbers, or external system keys. A user searching for "tp_reference_ext2" therefore lands on this view because it is one of the objects that projects that column directly.

Per the documented metadata, this view is obsolete from Patchset I onwards. It should not be used in new development, and references to it should be migrated to supported alternatives. It exists in the ETRM reference for backward compatibility and for interpreting legacy customizations.

Underlying Base Objects

The view text documents the following base objects:

  • RA_CONTACTS RCO — the driving table, supplying contact_id, last_name, first_name, and job_title.
  • WSH_ADDRESS_TP_V WTA — an intermediate view supplying address_id, the TP reference extension columns, and the geographic address attributes. The join to this object is an outer join (RCO.ADDRESS_ID = WTA.ADDRESS_ID (+)), so contacts without a matching trading partner address are still returned.
  • RA_CUSTOMERS RCU — supplies customer_id and customer_name, joined on RCO.CUSTOMER_ID = RCU.CUSTOMER_ID.

Two calls to the packaged function WSH_ECE_VIEWS_DEF are embedded in the select list: GET_CONT_AREA_CODE and GET_CONT_PHONE_NUMBER, which derive the contact's telephone area code and phone number at query time. No base objects are separately documented in the ETRM metadata for 12.2.2, so the view text above is the authoritative definition.

Key Columns

  • CUSTOMER_ID, CUSTOMER_NAME — the owning customer and its display name.
  • CONTACT_ID, LAST_NAME, FIRST_NAME, JOB_TITLE — recipient contact identity and role.
  • ADDRESS_ID, ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — the physical address of the contact.
  • ORIG_SYSTEM_REFERENCE — the source system key from the trading partner model, used for cross-system correlation.
  • TP_LOCATION_CODE_EXT, TP_REFERENCE_EXT1, TP_REFERENCE_EXT2 — the external trading partner location code and the two flexible reference fields; TP_REFERENCE_EXT2 is frequently mapped to an EDI or carrier-specific identifier.
  • AREA_CODE, PHONE_NUMBER — derived at runtime via WSH_ECE_VIEWS_DEF, not stored.

Common Use Cases and Queries

Typical uses include shipping document generation, EDI/XML outbound interfaces that require an external location reference, and recipient lookup reports. A representative query is:

SELECT customer_name, last_name, first_name, tp_reference_ext2, city, postal_code, phone_number FROM wsh_rco_address_v WHERE tp_reference_ext2 IS NOT NULL;

A second common pattern resolves a contact by external reference:

SELECT contact_id, customer_id, address1, city FROM wsh_rco_address_v WHERE orig_system_reference = :p_ref AND tp_reference_ext1 = :p_code;

Because the view is obsolete from Patchset I, migrations should target the underlying trading partner and address views directly, selecting TP_REFERENCE_EXT2 from WSH_ADDRESS_TP_V. Queries against WSH_RCO_ADDRESS_V should be treated as legacy only.