Results for “apps_ts_seed”

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

Overview

WSH.WSH_CALENDAR_ASSIGNMENTS is a Shipping Execution (WSH) seed table that stores the assignment of shipping, receiving, and carrier calendars to specific business entities within Oracle E-Business Suite. Each row binds a calendar defined in BOM_CALENDARS to an association such as a customer, customer site, vendor, vendor site, carrier, inventory organization, or HR location. The table functions as a configuration and reference repository that drives the calendar logic used by shipping and receiving transactions, including scheduling, lead-time calculation, and carrier pick-up windows in releases 12.1.1 and 12.2.2.

Under the heuristic Data Vault classification mined from its foreign key structure, WSH_CALENDAR_ASSIGNMENTS is best modelled as a link table. It connects the calendar dimension (BOM_CALENDARS) to multiple independent entity dimensions (customers, vendors, carriers, organizations, and locations) through association identifier columns, with descriptive and audit attributes attached directly to each association record.

Key Information Stored

The table is populated with 42 documented columns. The most operationally significant are:

• CALENDAR_ASSIGNMENT_ID – surrogate primary key (WSH_CALENDAR_ASSIGNMENTS_U1 unique index) for each assignment record.
• CALENDAR_CODE – foreign key to BOM_CALENDARS.CALENDAR_CODE, identifying the calendar being assigned.
• CALENDAR_TYPE – classifies the calendar as SHIPPING, RECEIVING, or CARRIER.
• ENABLED_FLAG – Y/N indicator activating or deactivating the assignment.
• ASSOCIATION_TYPE – defines the entity category: CUSTOMER, CUSTOMER_SITE, VENDOR, VENDOR_SITE, CARRIER, ORGANIZATION, or HR_LOCATION.
• LOCATION_ASSOCIATION_ID – references PO_LOCATION_ASSOCIATIONS for location-based rules.
• ORGANIZATION_ID – foreign key to HR_ORGANIZATION_UNITS.ORGANIZATION_ID.
• VENDOR_ID and VENDOR_SITE_ID – identify supplier and supplier site assignments.
• CUSTOMER_ID and CUSTOMER_SITE_USE_ID – reference HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES_ALL respectively.
• CARRIER_ID, CARRIER_SITE_ID, and FREIGHT_CODE/FREIGHT_ORG_ID – drive carrier and freight-specific calendar rules.
• Audit columns including CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and the WHO-style program columns (PROGRAM_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE).

Secondary non-unique indexes (N1 through N10) exist on CALENDAR_CODE, LOCATION_ASSOCIATION_ID, ORGANIZATION_ID, LOCATION_ID, VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_USE_ID, FREIGHT_CODE, and FREIGHT_ORG_ID, optimising lookups by association type. Apart from CALENDAR_ASSIGNMENT_ID, CALENDAR_CODE, and (where configured) pairs of association identifiers serve as the closest business-key candidates.

Common Use Cases and Queries

Typical uses include determining the effective shipping or receiving calendar for a given customer, vendor, or carrier during order scheduling; validating carrier pick-up schedules; and reporting on calendar coverage by organisation or location.

Example SQL to retrieve active shipping calendars for a customer:

• SELECT calendar_code, association_type, organization_id FROM wsh.wsh_calendar_assignments WHERE customer_id = :cust_id AND calendar_type = 'SHIPPING' AND enabled_flag = 'Y';

To join calendars for a vendor site:

• SELECT a.calendar_code, b.calendar_name FROM wsh.wsh_calendar_assignments a, bom.bom_calendars b WHERE a.calendar_code = b.calendar_code AND a.vendor_site_id = :site_id AND a.enabled_flag = 'Y';

Reporting use cases include coverage analysis (count of assignments by ASSOCIATION_TYPE), detection of disabled or orphaned assignments, and auditing changes via the standard audit columns. Because the table resides in the APPS_TS_SEED tablespace, it should be treated as seed/reference data subject to controlled migration through standard OAF or API-based maintenance rather than ad hoc DML.

Related Objects

The following objects are the most significant for joins and dependencies:

• BOM.BOM_CALENDARS – joined on CALENDAR_CODE to obtain calendar name, workday patterns, and shift definitions.
• PO.PO_LOCATION_ASSOCIATIONS – joined on LOCATION_ASSOCIATION_ID for location-based calendar rules.
• HR.HR_ORGANIZATION_UNITS – joined on ORGANIZATION_ID for inventory organisation context.
• PO.PO_VENDORS – joined on VENDOR_ID for supplier information.
• HZ.HZ_CUST_ACCOUNTS and RA_CUSTOMERS – joined on CUSTOMER_ID for party/account context.
• HZ.HZ_CUST_SITE_USES_ALL – joined on CUSTOMER_SITE_USE_ID for site-use resolution.
• ORG_FREIGHT – joined on FREIGHT_CODE for freight terms and carrier rules.
• WSH scheduling and shipping APIs that consult these assignments when computing ship dates and delivery windows.