Results for “msc_cust_sup_org_v”

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

Overview

MSC_CUST_SUP_ORG_V is a read-only database view owned by the APPS schema and delivered as part of the MSC (Advanced Supply Chain Planning) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It consolidates trading partner information into a single denormalized result set that pairs a modeled customer entity with a modeled supplier entity for a given source system instance and organization. The view is primarily consumed by supply chain planning flows, integration extracts, and custom reporting where both the customer-side and supplier-side partner definitions must be resolved together — for example, when building sourcing rules, drop-ship relationships, or partner-level address verification extracts.

The user search term "partner_address" maps directly to the SHIP_TO_ADDRESS column, which is sourced from the PARTNER_ADDRESS attribute of MSC_TRADING_PARTNER_SITES. This view therefore serves as one of the standard access points for retrieving the physical address associated with a trading partner site without having to join the underlying site table manually.

Underlying Base Objects

The view is defined over two documented base objects, both exposed in the APPS schema as synonyms:

• MSC_TRADING_PARTNERS — the master table of trading partners (customers and suppliers) maintained by Advanced Supply Chain Planning.
• MSC_TRADING_PARTNER_SITES — the child table holding the site-level records belonging to each trading partner.

The view text joins MSC_TRADING_PARTNERS three times (aliases PT, PT1, PT2) and MSC_TRADING_PARTNER_SITES twice (aliases PTS1, PTS2). The driving alias PT supplies the source system identifiers and the modeled customer and supplier IDs. PT1 and PT2 resolve the customer and supplier names respectively, while PTS1 and PTS2 resolve the corresponding site-level address and site code. All four secondary joins are outer joins (marked with the (+) operator), so a row is returned whenever either the modeled customer ID or the modeled supplier ID is populated on the driving record.

Key Columns

• ORGANIZATION_ID / ORGANIZATION_CODE — identifier and code of the organization to which the trading partner record belongs.
• SR_INSTANCE_ID — identifies the source system instance from which the planning data was collected.
• CUSTOMER_ID / CUSTOMER_NAME — the modeled customer partner ID and its resolved partner name.
• SHIP_TO_SITE_ID / SHIP_TO_ADDRESS — the modeled customer site ID and the partner address associated with that site. SHIP_TO_ADDRESS is the column most frequently targeted by address-related queries.
• SUPPLIER_ID / SUPPLIER_NAME — the modeled supplier partner ID and its resolved partner name.
• SUPPLIER_SITE_ID / SUPPLIER_SITE — the modeled supplier site ID and the corresponding TP_SITE_CODE.

Columns on the customer and supplier sides may be null depending on which side of the relationship is populated on the driving trading partner record.

Common Use Cases and Queries

Typical uses include validating that partner site addresses are populated, extracting customer/supplier pairs for integration staging, and reconciling planning-side partner definitions against master data. Example:

• Retrieve shipping addresses for all modeled customers in an organization:

  SELECT organization_code, customer_id, customer_name,
         ship_to_site_id, ship_to_address
    FROM apps.msc_cust_sup_org_v
   WHERE ship_to_address IS NOT NULL
     AND organization_code = :org;

• Return supplier site codes alongside customer ship-to addresses for a source instance:

  SELECT sr_instance_id, customer_name, ship_to_address,
         supplier_name, supplier_site
    FROM apps.msc_cust_sup_org_v
   WHERE sr_instance_id = :instance_id;

• Find records where only one side of the partner relationship is populated:

  SELECT *
    FROM apps.msc_cust_sup_org_v
   WHERE customer_id IS NULL OR supplier_id IS NULL;

Because the view performs several outer joins across large planning tables, queries should filter on ORGANIZATION_ID, SR_INSTANCE_ID, or the partner IDs to keep execution within acceptable response times.