Results for “op_cust_shp”

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

Overview

OP_CUST_SHP is a table owned by the GML schema within Oracle E-Business Suite, belonging to the Process Manufacturing Logistics product module. It stores preferred shippers (carriers) and ship methods associated with customers. In operational terms, the table acts as a customer-level shipping preference registry, allowing the application to default carrier, ship method, warehouse, and shipping class information when processing outbound logistics for a given customer. This reduces manual data entry and enforces consistent shipping behavior across order fulfillment.

Based on the foreign key structure documented in the ETRM metadata, the heuristic Data Vault classification for OP_CUST_SHP is a link. This is a modeling suggestion derived from the fact that the table's primary key is a surrogate (CUSTSHIP_ID) while its foreign keys reference multiple independent hubs and reference tables, including OP_CUST_MST, IC_WHSE_MST, OP_SHIP_MST, OP_SHIP_MTH, IC_SHIP_CLS, and OP_TEXT_HDR. The table therefore captures associations between customers, warehouses, shippers, and ship methods rather than serving as a standalone master hub.

Key Information Stored

The table contains 23 documented columns. The most business-relevant columns are summarized below.

• CUSTSHIP_ID — Surrogate primary key, enforced through the OP_CUST_SHP_PK index and OP_CUST_SHP_U1 unique index. It uniquely identifies each preferred shipper/ship method record.
• CUST_ID and OPM_CUST_ID — Customer identifiers linking the preference to a customer record; CUST_ID references OP_CUST_MST.
• WHSE_CODE — Warehouse code from IC_WHSE_MST, scoping the preference to a specific shipping warehouse.
• SHIPPER_CODE — Preferred shipper or carrier, referencing OP_SHIP_MST.
• SHIP_MTHD — Ship method for the customer, referencing OP_SHIP_MTH.
• ICSHIP_CLASS — Shipping class, referencing IC_SHIP_CLS.
• SHIP_CATEGORY_ID — Category grouping for the shipping preference.
• PREFERENCE — Rank or priority used when multiple preferences exist for the same customer.
• FRT_ZONE — Freight zone associated with the preference.
• TRANSIT_TIME — Expected transit duration used for planning and delivery estimates.
• TEXT_CODE — Reference to OP_TEXT_HDR for associated textual notes.
• TRANS_CNT and DELETE_MARK — Concurrency counter and logical delete flag used within Process Manufacturing conventions.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — Concurrent program context columns.

The surrogate key (CUSTSHIP_ID) should be distinguished from business-key candidates. The unique index OP_CUST_SHP_U1 covers CUST_ID, WHSE_CODE, ICSHIP_CLASS, SHIP_MTHD, and SHIPPER_CODE, indicating those columns collectively form the natural business key.

Common Use Cases and Queries

Typical scenarios include defaulting carrier and ship method at order entry, analyzing carrier usage by customer, and auditing customer shipping setups. A representative query retrieves preferred shippers for a customer:

SELECT c.CUSTSHIP_ID, c.CUST_ID, c.WHSE_CODE, c.SHIPPER_CODE,
       c.SHIP_MTHD, c.ICSHIP_CLASS, c.PREFERENCE, c.TRANSIT_TIME
FROM   GML.OP_CUST_SHP c
WHERE  c.CUST_ID = :cust_id
AND    c.DELETE_MARK = 0
ORDER BY c.PREFERENCE;

Reporting may join shipper and ship method masters to present descriptive names. Excluding logically deleted rows via DELETE_MARK is standard practice.

Related Objects

• OP_CUST_MST — joined on OP_CUST_SHP.CUST_ID = OP_CUST_MST.CUST_ID; the customer master.
• IC_WHSE_MST — joined on OP_CUST_SHP.WHSE_CODE = IC_WHSE_MST.WHSE_CODE; warehouse master.
• OP_SHIP_MST — joined on OP_CUST_SHP.SHIPPER_CODE = OP_SHIP_MST.SHIPPER_CODE; shipper master.
• OP_SHIP_MTH — joined on OP_CUST_SHP.SHIP_MTHD = OP_SHIP_MTH.SHIP_MTHD; ship method master.
• IC_SHIP_CLS — joined on OP_CUST_SHP.ICSHIP_CLASS = IC_SHIP_CLS.ICSHIP_CLASS; shipping class reference.
• OP_TEXT_HDR — joined on OP_CUST_SHP.TEXT_CODE = OP_TEXT_HDR.TEXT_CODE; text header for notes.

These relationships position OP_CUST_SHP as the central link table connecting Process Manufacturing Logistics customer, warehouse, and carrier reference data.