Search Results csp_packlist_headers_u1

Overview

In the Oracle EBS 12.1.1 / 12.2.2 environment, the database object CSP.CSP_PACKLIST_HEADERS is a transactional base table owned by the CSP (Channel/Spares Management) schema. It functions as the parent record for packing and shipment data, storing the shipping information for parts being shipped. The table behaves as a header-level container holding packlist metadata (identifiers, organization, carrier details, key milestone dates, and descriptive flexfield attributes), while the actual inventory items and box contents are stored in child tables. Its purpose is to consolidate the lifecycle states of a packlist—from creation to packing, shipping, and final receipt.

From a heuristic Data Vault modeling perspective, the object exhibits a satellite-leaning classification. Although it contains a surrogate primary key (PACKLIST_HEADER_ID), its columns are predominantly descriptive attributes and timestamps (dates for created, packed, shipped, and received). This suggests it operates as a satellite attached to a business key rather than as a pure hub in a classic Data Vault architecture. It is a classic header table where the natural business key (packlist number) is supplemented by a surrogate system identifier.

Key Information Stored

The table is defined in the APPS_TS_TX_DATA tablespace with 37 documented columns. The most significant columns include:

• PACKLIST_HEADER_ID (NUMBER, mandatory): The surrogate primary key and unique identifier. It is enforced by the unique index CSP_PACKLIST_HEADERS_U1, making it the system-level business-key candidate.
• PACKLIST_STATUS (VARCHAR2(30)): Represents the lifecycle state of the packlist line—Packed, Shipped, or Received. This drives workflow and reporting logic.
• DATE_CREATED, DATE_PACKED, DATE_SHIPPED, DATE_RECEIVED (DATE): These four timestamps capture the progressive milestones of the packlist lifecycle, enabling lead-time and turnaround reporting.
• PACKLIST_NUMBER (VARCHAR2(10)): The user-facing document number, distinct from the internal ID.
• ORGANIZATION_ID (NUMBER): The inventory organization identifier, indexed non-uniquely (CSP_PACKLIST_HEADERS_N1) along with status.
• SUBINVENTORY_CODE (VARCHAR2(10)): The subinventory from which items are packed; it carries a foreign key to MTL_SECONDARY_INVENTORIES.
• CARRIER, SHIPMENT_METHOD, WAYBILL (VARCHAR2(60)): Logistics attributes recording the freight carrier, method of shipment, and the carrier's waybill number for shipment tracking.
• LOCATION_ID (NUMBER): The ship-to or operational location identifier.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 (VARCHAR2): Descriptive flexfield structure and segment columns for capturing organization-specific data.
• SECURITY_GROUP_ID (NUMBER): Foreign key to FND_SECURITY_GROUPS for data access control.
• COMMENTS (VARCHAR2(240)): Free-text notes on the packlist.

Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) can be found here for audit compliance.

Common Use Cases and Queries

This table supports several reporting scenarios, such as analyzing shipment turnaround from creation to receipt, tracking carrier performance using WAYBILL, and monitoring outstanding packlists by status and organization. Because the status and dates live together, a common query filters the latest activity by organization.

A sample query to retrieve outstanding shipped packlists is:

SELECT p.packlist_number,
       p.packlist_status,
       p.carrier,
       p.waybill,
       p.date_shipped
FROM   csp.csp_packlist_headers p
WHERE  p.organization_id = :org_id
AND    p.packlist_status = 'SHIPPED'
ORDER BY p.date_shipped;

Another frequent use case is joining the header to its child lines to view full shipment contents, or calculating the time to pack using DATE_PACKED minus DATE_CREATED. Given the non-unique index on ORGANIZATION_ID and PACKLIST_STATUS, queries filtering by these columns perform efficiently.

Related Objects

The table participates in several key relationships within the CSP schema:

• CSP_PACKLIST_BOXES: References this table via PACKLIST_HEADER_ID, storing boxes associated with the packlist header.
• CSP_PACKLIST_LINES: References this table via PACKLIST_HEADER_ID, storing the individual item lines being shipped.
• MTL_SECONDARY_INVENTORIES: Referenced by SUBINVENTORY_CODE, linking to inventory subinventory definitions.
• FND_SECURITY_GROUPS: Referenced by SECURITY_GROUP_ID, controlling data access.
• FND_USER: Implicitly referenced by CREATED_BY and LAST_UPDATED_BY for audit tracking.

When querying, the primary key PACKLIST_HEADER_ID is the standard join column to child tables, while ORGANIZATION_ID and SUBINVENTORY_CODE link to inventory master data.