Search Results msc_interorg_ship_methods_pk

Overview

MSC.MSC_INTERORG_SHIP_METHODS is a table owned by the MSC schema (Advanced Supply Chain Planning) that stores the different shipment methods between organization–location pairs together with the attributes that govern their availability and cost. Conceptually, it is a transportation-capacity and shipment-routing reference table used by Advanced Supply Chain Planning to determine how material may move from a source organization/location to a destination organization/location within a plan. Each row ties a shipping method for a specific plan, source organization, source location, destination organization, and destination location, and records capacity, cost, and in-transit timing characteristics.

From a Data Vault modeling perspective, the metadata's heuristic classification for this object is standalone. The primary key is composite and combines many descriptive and identifying attributes, which suggests modeling this object as a link that connects from-organization/from-location, to-organization/to-location, plan, and ship method, with the capacity, cost, and timing columns behaving as a satellite payload. This is offered as a modeling suggestion only; the physical implementation is a single Oracle table with no mined FK dependencies.

Key Information Stored

The table contains 59 documented columns. The most significant are the keys and the shipment-capacity/cost/timing attributes, described below.

• Primary key columns (surrogate/business key): FROM_ORGANIZATION_ID, SR_INSTANCE_ID, TO_ORGANIZATION_ID, SR_INSTANCE_ID2, PLAN_ID, FROM_LOCATION_ID, TO_LOCATION_ID, and SHIP_METHOD together form the composite primary key MSC_INTERORG_SHIP_METHODS_PK.
• Business-key candidates (unique indexes): MSC_INTERORG_SHIP_METHODS_U1 extends the PK columns with TO_REGION_ID and FROM_REGION_ID, making each row unique per plan, org/location pair, ship method, and region. MSC_INTERORG_SHIP_METHODS_U2 uses PLAN_ID and TRANSACTION_ID, indicating a transaction-scoped uniqueness constraint.
• Location descriptions: FROM_LOCATION_NAME, TO_LOCATION_NAME, FROM_LOCATION_DESCR, and TO_LOCATION_DESCR provide display-level identities for the source and destination locations referenced by FROM_LOCATION_ID and TO_LOCATION_ID.
• Shipment method: SHIP_METHOD and SHIP_METHOD_TEXT identify and describe the carrier/service method, while DEFAULT_FLAG marks the default method for a given lane.
• Capacity and cost: WEIGHT_CAPACITY, WEIGHT_UOM, VOLUME_CAPACITY, VOLUME_UOM, COST_PER_WEIGHT_UNIT, COST_PER_VOLUME_UNIT, TRANSPORT_CAP_OVER_UTIL_COST, and CURRENCY define how much can be shipped and at what cost.
• Timing: INTRANSIT_TIME, TIME_UOM_CODE, and LEADTIME_VARIABILITY describe transit duration and variability.
• Shipment/other: SHIPMENT_WEIGHT, SHIPMENT_VOLUME, SHIPMENT_WEIGHT_UOM, SHIPMENT_VOLUME_UOM, TO_REGION_ID, FROM_REGION_ID, COLLECTED_FLAG, and REFRESH_NUMBER support planning calculations and data-collection tracking. Standard who-columns (LAST_UPDATE_DATE, CREATED_BY, ATTRIBUTE1..15) are also present.

Common Use Cases and Queries

The table is typically queried by planning or supply-chain reporting to list shipment methods available between a from-location and a to-location for a given plan. Because the user searched for from_location_id, a common pattern is a lane lookup:

SELECT plan_id, from_organization_id, from_location_id, to_organization_id,
       to_location_id, ship_method, intransit_time, time_uom_code,
       weight_capacity, volume_capacity, default_flag
FROM   msc.msc_interorg_ship_methods
WHERE  from_location_id = :p_from_location_id
AND    to_location_id   = :p_to_location_id
AND    plan_id          = :p_plan_id;

Reporting use cases include identifying the default ship method per lane, calculating transport cost per weight or volume unit for sourcing decisions, surfacing in-transit lead times and variability for planning buffers, and flagging lanes where capacity may be over-utilized using TRANSPORT_CAP_OVER_UTIL_COST. The PLAN_ID and TRANSACTION_ID unique index supports point lookups by transaction. Because the table is populated by collection into ASCP, the REFRESH_NUMBER and COLLECTED_FLAG columns are useful for data-freshness checks.

Related Objects

The metadata classifies this object as standalone, so no mined foreign keys exist. In practice, the most significant related objects are those implied by the key columns:

• MSC_ORGANIZATIONS / ORG_ORGANIZATION_DEFINITIONS — joined on FROM_ORGANIZATION_ID and TO_ORGANIZATION_ID to resolve organization names.
• Location tables referenced by FROM_LOCATION_ID and TO_LOCATION_ID, typically sourced from Oracle Inventory/WMS locations.
• ASCP plan metadata (e.g., MSC_PLANS) — joined on PLAN_ID.
• Region reference data — joined on TO_REGION_ID and FROM_REGION_ID.
• MSC_INTERORG_SHIP_METHODS_U1 and MSC_INTERORG_SHIP_METHODS_U2 — unique indexes enforcing business-key uniqueness on the table itself.
• Transaction context for the TRANSACTION_ID column, as constrained by MSC_INTERORG_SHIP_METHODS_U2.