Search Results s_ship




Overview
Explore in AI Chat

Overview

QP_SHIP_TO_ORGS_V is an APPS-owned database view in Oracle E-Business Suite 12.1.1 and 12.2.2 that presents ship-to organization information by joining Oracle Trade Management (Advanced Pricing) party and customer data with HR organization units. The view resolves the ship-to sites defined in the Oracle Receivables / Trading Community Architecture (TCA) model to their owning operating unit organization, exposing the party name, site address, site use identifier, and organization identifier in a single denormalized result set. It is typically used by pricing and order capture processes, and by reporting and integration routines that must determine which operating unit a given ship-to address belongs to. The embedded /*+ first_rows */ hint indicates the view was designed for interactive query paths where fast retrieval of the first rows matters more than full-table throughput.

Underlying Base Objects

The view is defined over seven joined sources. Five are TCA entities accessed through APPS synonyms: HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES, HZ_PARTY_SITES, HZ_LOCATIONS, and HZ_CUST_SITE_USES_ALL. The seventh is the HR_ORGANIZATION_UNITS view, which supplies the organization identifier and name. The documented base objects also list the HR_GENERAL and HR_SECURITY packages, which HR_ORGANIZATION_UNITS depends on for organization classification and security (MO: Security Profile) filtering. In practice this means the rows returned can be constrained by the operating unit or security profile of the session, a behavior inherited from the HR organization layer.

Join logic is driven by strict equality and status filters: customer accounts and account sites must be active (status = 'A'); the account site must link to the party site through party_site_id; the party site must link to the location through location_id; the site use must link to the account site through cust_acct_site_id; and the site use's org_id must equal the HR organization ID. Only rows where site_use_code = 'SHIP_TO' are returned.

Key Columns

  • PARTY_NAME — the customer or party name from HZ_PARTIES.
  • LOCATION — the location code/identifier from HZ_CUST_SITE_USES_ALL (the ship-to site's location short name).
  • ADDRESS1, ADDRESS2, ADDRESS3 — address lines from HZ_LOCATIONS for the ship-to party site.
  • SITE_USE_ID — the unique identifier of the ship-to site use (HZ_CUST_SITE_USES_ALL.SITE_USE_ID), the primary key most commonly referenced by downstream tables.
  • ORGANIZATION_ID — the operating unit / organization identifier from HR_ORGANIZATION_UNITS (equal to the site use ORG_ID).
  • NAME — the organization name corresponding to the operating unit.

Common Use Cases and Queries

Typical uses include validating that a ship-to site exists for a given operating unit, resolving an organization from a ship-to site, and populating LOVs or reports in pricing and order management extensions.

  • Retrieve all ship-to organizations for a party:
    SELECT party_name, location, address1, site_use_id, organization_id, name FROM apps.qp_ship_to_orgs_v WHERE party_name = :p_party_name;
  • Resolve the operating unit for a specific ship-to site use:
    SELECT organization_id, name FROM apps.qp_ship_to_orgs_v WHERE site_use_id = :p_site_use_id;
  • List ship-to sites within an operating unit:
    SELECT party_name, location, address1 FROM apps.qp_ship_to_orgs_v WHERE organization_id = :p_org_id ORDER BY party_name;

Because the view performs multi-table joins on TCA and HR sources, queries should always filter on indexed keys such as SITE_USE_ID or ORGANIZATION_ID to avoid full scans. Any query executed under a restricted MO security profile will only return ship-to organizations visible to that profile.