Search Results pooled_ship_to_v

Overview

WSH_SS_PARTNER_V is an APPS-owned view in the Oracle E-Business Suite Shipping Execution (WSH) module, valid in both release 12.1.1 and 12.2.2. It exposes trading-partner and ship-to address information associated with deliveries held in WSH_NEW_DELIVERIES, presenting one consolidated row per partner/location combination as determined by the DISTINCT clause in the view definition. The view is best understood as a shipping-facing projection of Oracle Trading Community Architecture (TCA) data: party names, customer site locations, addresses, and contact points are joined to delivery records so that shipment documentation and integration flows can retrieve consignee details without querying the HZ schema objects directly.

The object is principally used in outbound shipping communications and reporting — carrier manifests, packing slips, EDI/XML partner transmissions, and shipping dashboards — where the ship-to party name, address, telephone, and fax are required alongside the delivery identifier.

Underlying Base Objects

The documented base objects for WSH_SS_PARTNER_V are HZ_PARTIES, HZ_PARTY_SITES, HZ_CUST_ACCT_SITES, HZ_CUST_SITE_USES, HZ_LOCATIONS, HZ_CONTACT_POINTS (all accessed through APPS synonyms), and WSH_NEW_DELIVERIES. The view also references HR_GENERAL, HR_LOCATIONS, HR_SECURITY, and ORG_ORGANIZATION_DEFINITIONS, consistent with Oracle's standard multi-org and security constructs applied to shipping views.

The join path is anchored on WSH_NEW_DELIVERIES, which supplies DELIVERY_ID. HZ_PARTIES provides PARTY_NAME and DUNS_NUMBER. HZ_PARTY_SITES bridges parties to locations, HZ_LOCATIONS supplies the structured address elements, and HZ_CUST_ACCT_SITES with HZ_CUST_SITE_USES identify the ship-to site use (SITE_USE_CODE = 'SHIP_TO', STATUS = 'A'). Two inline subqueries resolve contact points: one filtered on CONTACT_POINT_TYPE = 'GEN' (telephone, URL) and one on CONTACT_POINT_TYPE = 'FAX', both restricted to OWNER_TABLE_NAME = 'HZ_PARTY_SITES'.

Key Columns

• PARTY_NAME — the trading partner or customer name from HZ_PARTIES.
• PARTNER_LOCATION, INTMED_SHIP_TO_LOCATION, POOLED_SHIP_TO_LOCATION — location identifiers from the ship-to site use and the two inline contact-point subqueries.
• PARTNER_TYPE — hard-coded as 'SHIPTO' in the view text.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE (aliased from REGION), COUNTY, POSTAL_CODE, COUNTRY — the formatted ship-to address.
• FAX_NUMBER — aliased from POOLED_SHIP_TO_V.RAW_PHONE_NUMBER; this is the column returned when users search for "fax_number". It is populated only where a contact point of type FAX exists for the party site.
• TELEPHONE — aliased from INTMED_SHIP_TO_V.RAW_PHONE_NUMBER (contact point type GEN).
• URL — the contact point URL for the ship-to site.
• DUNS_NUMBER — the party's D-U-N-S identifier.
• DELIVERY_ID — the WSH delivery key linking the row back to WSH_NEW_DELIVERIES.
• CURRENCY — exposed as NULL in the view definition.

Common Use Cases and Queries

The most frequent query pattern retrieves fax and telephone details for a specific delivery, typically for carrier notification or shipment documentation:

• SELECT delivery_id, party_name, fax_number, telephone, address1, city, state, postal_code FROM apps.wsh_ss_partner_v WHERE delivery_id = :p_delivery_id;
• SELECT party_name, fax_number FROM apps.wsh_ss_partner_v WHERE fax_number IS NOT NULL; — to identify partners with a fax contact point available.
• Joining to WSH_NEW_DELIVERIES or WSH_DELIVERY_ASSIGNMENTS to enrich delivery-level reports with consignee name, address, and fax.

Because the view is defined with SELECT DISTINCT and joins through multiple TCA site-use and contact-point relationships, result sets can multiply where a party site holds several active ship-to uses or multiple contact points of the same type; queries should therefore filter on DELIVERY_ID or PARTNER_LOCATION to ensure deterministic results. FAX_NUMBER is nullable, so reports relying on it should handle missing values explicitly.