Search Results wsh_document_instances_u1

Overview

WSH.WSH_DOCUMENT_INSTANCES is a transactional table in the Oracle E-Business Suite Shipping (WSH) schema that stores instances of shipping documents such as Bills of Lading, Packing Slips, and Commercial Invoices. Each row represents a single generated document tied to an underlying business entity — most commonly a delivery leg — and captures the header-level legal, logistics, and carrier information required to print and reprint that document. The table is a core component of the Oracle Shipping Execution document generation and Bill of Lading features, and is used extensively by shipping confirmation, delivery, and transportation reporting flows.

Under the heuristic Data Vault classification provided in the ETRM metadata, this object is modeled as standalone rather than a hub, link, or satellite. This suggests it functions as an independent transactional record whose foreign key footprint is minimal — it references only WSH_DOC_SEQUENCE_CATEGORIES via DOC_SEQUENCE_CATEGORY_ID — and it is not centrally joined through a business-key relationship to other entities in the shipping model.

Key Information Stored

The table comprises 67 documented columns. The most significant are:

• DOCUMENT_INSTANCE_ID — Surrogate primary key (NUMBER), enforced by the unique index WSH_DOCUMENT_INSTANCES_U1. This is the standard business-key candidate on the table.
• ENTITY_NAME and ENTITY_ID — Identify the owning business entity (for example, WSH_DELIVERY_LEGS) and its primary key value. Together these form the polymorphic link between document instances and the source record.
• DOCUMENT_TYPE — Lookup code indicating the document category (Bill of Lading, Packing Slip, etc.), referencing FND_LOOKUPS.LOOKUP_CODE.
• SEQUENCE_NUMBER — Human-readable document number assigned to the instance.
• STATUS — Lifecycle state of the document instance.
• FINAL_PRINT_DATE — Timestamp of the final version print, used to control reprint privileges.
• DOC_SEQUENCE_CATEGORY_ID — Foreign key to WSH_DOC_SEQUENCE_CATEGORIES, governing numbering rules.
• POD_FLAG, POD_BY, POD_DATE, EXPECTED_POD_DATE — Proof-of-delivery tracking attributes.
• BOL_* columns (BOL_DATE_ISSUED, BOL_ISSUED_BY, BOL_ISSUE_OFFICE, SHIPPER_SIGNED_BY, CARRIER_SIGNED_BY, CARRIER_DATE, SHIPPER_DATE) — Bill of Lading signature and issuance data.
• CARRIED_BY, PORT_OF_LOADING, PORT_OF_DISCHARGE, BOOKING_NUMBER, SERVICE_CONTRACT — Transportation and carrier booking details.
• AETC_NUMBER, SUPPLIER_CODE, SHIPPER_EXPORT_REF, CARRIER_EXPORT_REF — Customs and export compliance references.
• COD_AMOUNT, COD_CURRENCY_CODE, COD_REMIT_TO, COD_CHARGE_PAID_BY — Cash-on-delivery attributes.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive Flexfield columns available for customer extensions.
• WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_ID, REQUEST_ID, PROGRAM_UPDATE_DATE, PROGRAM_APPLICATION_ID) — Standard audit and concurrent program context.

Common Use Cases and Queries

Typical use cases include reprint control for delivery documents, proof-of-delivery reporting, Bill of Lading reconciliation, and carrier/customs auditing. A frequent query pattern retrieves all documents for a given delivery leg:

• SELECT DOCUMENT_INSTANCE_ID, DOCUMENT_TYPE, SEQUENCE_NUMBER, STATUS FROM WSH.WSH_DOCUMENT_INSTANCES WHERE ENTITY_NAME = 'WSH_DELIVERY_LEGS' AND ENTITY_ID = :delivery_leg_id;
• SELECT * FROM WSH.WSH_DOCUMENT_INSTANCES WHERE DOCUMENT_TYPE = 'BOL' AND TRUNC(FINAL_PRINT_DATE) = TRUNC(SYSDATE);
• SELECT ai.*, sc.SEQUENCE_CATEGORY_NAME FROM WSH.WSH_DOCUMENT_INSTANCES ai JOIN WSH.WSH_DOC_SEQUENCE_CATEGORIES sc ON ai.DOC_SEQUENCE_CATEGORY_ID = sc.DOC_SEQUENCE_CATEGORY_ID;

The nonunique indexes WSH_DOCUMENT_INSTANCES_N1 (ENTITY_NAME, ENTITY_ID, DOCUMENT_TYPE, STATUS), N2 (POD_FLAG), and N3 (SEQUENCE_NUMBER) support the most common lookup and reporting predicates, and should be leveraged in custom queries to avoid full scans.

Related Objects

• WSH.WSH_DOC_SEQUENCE_CATEGORIES — Referenced via DOC_SEQUENCE_CATEGORY_ID; defines document numbering categories.
• WSH_DELIVERY_LEGS — Primary source entity, referenced polymorphically via ENTITY_NAME / ENTITY_ID.
• WSH_DELIVERIES — Parent delivery records associated with document instances through the delivery leg linkage.
• FND_LOOKUPS — Source of DOCUMENT_TYPE lookup codes.
• FND_DOCUMENTS / FND_ATTACHED_DOCUMENTS — Attachment context for generated document outputs.
• WSH_DOCUMENT_PRINT_PVT / WSH_DOCUMENT_PRINT_PKG — Shipping Execution APIs used to generate and reprint document instances.
• WSH_DELIVERY_DETAILS — Provides line-level detail reported on packing slips and BOLs.