Search Results delivery_template_id

Overview

WSH_DELIVERY_TEMPLATES is a transactional configuration table owned by the OE (Order Entry) schema that stores shipping delivery templates used by Oracle Shipping Execution. A delivery template defines the reusable characteristics applied when planning and grouping deliveries, including the departure schedule to follow, the customer and ship-to routing, weight and volume units of measure, and the expected arrival offsets relative to the planned departure. Templates reduce manual effort and enforce consistency when consolidations, trips, and stops are generated for outbound shipments.

Under the heuristic Data Vault classification derived from the foreign key structure, this table is best modeled as a link. It sits at the intersection of several reference entities — a departure template, a customer, an ultimate ship-to, and an intermediate ship-to — and therefore behaves as an associative object carrying the relationships plus its own descriptive attributes. The surrogate primary key is DELIVERY_TEMPLATE_ID, constrained by WSH_DELIVERY_TEMPLATES_PK.

Key Information Stored

The table contains 64 documented columns in the 12.2.2 physical schema. The most significant are:

• DELIVERY_TEMPLATE_ID — surrogate primary key uniquely identifying each template.
• NAME — user-defined template name; part of the unique business key.
• ORGANIZATION_ID — the inventory organization that owns the template; part of the unique business key.
• PLANNED_DEP_TEMPLATE_ID — foreign key to WSH_DEPARTURE_TEMPLATES, defining the departure schedule; part of the unique business key.
• SEQUENCE_NUMBER — ordering value used when multiple templates apply.
• LOADING_ORDER_FLAG — controls whether loading order rules are applied during trip building.
• CUSTOMER_ID — foreign key to RA_CUSTOMERS; restricts the template to a specific customer.
• ULTIMATE_SHIP_TO_ID and INTERMEDIATE_SHIP_TO_ID — foreign keys to RA_SITE_USES_ALL defining the final and intermediate ship-to destinations.
• POOLED_SHIP_TO_ID — ship-to used for pooled consolidation scenarios.
• WEIGHT_UOM_CODE and VOLUME_UOM_CODE — units of measure applied to template capacity calculations.
• EXPECTED_ARRIVAL_DAY_OFFSET, EXPECTED_ARRIVAL_HOUR_OFFSET, EXPECTED_ARRIVAL_MIN_OFFSET — offsets from departure used to derive expected arrival.
• PLANNED_FREQUENCY, PLANNED_DAY, PLANNED_TIME — recurrence parameters for the template.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 and the GLOBAL_ATTRIBUTE1–20 flexfields — extensibility placeholders.
• Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID.

The unique index WSH_DELIVERY_TEMPLATES_U1 (PLANNED_DEP_TEMPLATE_ID, ORGANIZATION_ID, NAME) is the documented business-key candidate, distinguishing a template by departure template, organization, and name.

Common Use Cases and Queries

Functional and technical teams query this table to audit template definitions, validate ship-to assignments, and reconcile planning parameters before running the shipping planner.

• Listing active templates for an organization: SELECT delivery_template_id, name, planned_dep_template_id FROM wsh_delivery_templates WHERE organization_id = :org_id;
• Joining to customers and ship-tos to report routing: SELECT dt.name, c.customer_name, s.location FROM wsh_delivery_templates dt, ra_customers c, ra_site_uses_all s WHERE dt.customer_id = c.customer_id AND dt.ultimate_ship_to_id = s.site_use_id;
• Verifying departure template linkage: SELECT dt.name, dep.name FROM wsh_delivery_templates dt, wsh_departure_templates dep WHERE dt.planned_dep_template_id = dep.departure_template_id;
• Checking arrival offset configuration across templates for planning accuracy.

Related Objects

• WSH_DEPARTURE_TEMPLATES — joined via PLANNED_DEP_TEMPLATE_ID; defines the schedule each delivery template inherits.
• RA_CUSTOMERS — joined via CUSTOMER_ID; supplies the customer associated with the template.
• RA_SITE_USES_ALL — joined via ULTIMATE_SHIP_TO_ID and INTERMEDIATE_SHIP_TO_ID; provides ship-to locations.
• WSH_DELIVERIES and related delivery/stop tables — consume templates when planning actual deliveries.
• Shipping Execution concurrent programs and the shipping planner reference this table indirectly through the departure template hierarchy.