Search Results csp_picklist_lines_v

Overview

CSP_PICKLIST_LINES_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the CSP (Spares Management) product family. Its documented description is simply "Picklist line information," reflecting its role as a denormalized, read-oriented projection of the picklist line entity used in spare parts and field service material picking flows. In Oracle EBS 12.1.1 and 12.2.2 the view is registered in the ETRM with a status of VALID, indicating that it is expected to compile and return data in a standard patched environment.

Functionally, the view exposes the attributes of each picklist line, including the identifier of the parent picklist header, the inventory item, the unit of measure, revision, and the quantity that has been picked. Because it is a view rather than a table, it carries no storage of its own; it is a stable interface layer that shields consumers from any restructuring of the underlying picklist line storage. Reporting tools such as Oracle Reports, BI Publisher, and third-party extract programs frequently reference such views instead of interrogating the base table directly, since the view name and column list are treated as a supported API surface across 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over a single base object: CSP_PICKLIST_LINES, which in the APPS schema is exposed through a synonym. The view text is a straightforward column projection with no joins, filters, grouping, or scalar expressions. Every column in the SELECT list maps one-to-one to a column of the same name on CSP_PICKLIST_LINES. As a result, row cardinality and row identity are identical between the view and the base table; PICKLIST_LINE_ID is unique per row in both.

During the 12.1.1 to 12.2.2 upgrade the online patching architecture changes how editioned objects are resolved, but because the view is a simple layer over the synonym, it continues to resolve against the current edition of the base table without change to its definition. No dependencies on other CSP or INV views are documented, and the ETRM record lists CSP_PICKLIST_LINES (SYNONYM) as the sole referenced base object.

Key Columns

• PICKLIST_LINE_ID — Primary identifier for the picklist line; unique within the view.
• PICKLIST_HEADER_ID — Foreign key to the parent picklist header, used to group lines into a picklist.
• PICKLIST_LINE_NUMBER — User-visible sequence number of the line within the picklist header.
• LINE_ID — Identifier of the originating demand or source line that generated the picklist line.
• INVENTORY_ITEM_ID — Inventory item being picked; joins to MTL_SYSTEM_ITEMS_B.
• UOM_CODE — Unit of measure for the picked quantity.
• REVISION — Item revision of the material to be picked.
• QUANTITY_PICKED — The quantity actually picked for the line; the column most often queried when measuring pick completion.
• TRANSACTION_TEMP_ID — Temporary transaction reference linking the line to inventory transaction processing.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield columns available for customer-specific extensions.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard WHO audit tracking.

Common Use Cases and Queries

Typical scenarios include measuring pick accuracy and completeness, reconciling picked quantities against requested quantities, and feeding downstream shipping or work order processes. The quantity_picked term in the user's search corresponds directly to the QUANTITY_PICKED column, which is the primary measure of interest in nearly all picklist reporting.

Listing all lines for a given picklist:

SELECT picklist_line_number,
       inventory_item_id,
       uom_code,
       revision,
       quantity_picked
FROM   apps.csp_picklist_lines_v
WHERE  picklist_header_id = :p_header_id
ORDER  BY picklist_line_number;

Summarizing picked quantity by item across all open picklists:

SELECT inventory_item_id,
       uom_code,
       SUM(quantity_picked) total_picked
FROM   apps.csp_picklist_lines_v
GROUP  BY inventory_item_id, uom_code;

Joining to the item master to obtain descriptions:

SELECT v.picklist_line_id,
       m.segment1,
       m.description,
       v.quantity_picked
FROM   apps.csp_picklist_lines_v v,
       apps.mtl_system_items_b m
WHERE  m.inventory_item_id = v.inventory_item_id
AND    m.organization_id   = :org_id;

Because the view performs no filtering, predicates such as organization or date range must be applied through joins to the header or item tables. Queries should always constrain on PICKLIST_HEADER_ID or the parent header where possible to avoid full scans of the underlying table in high-volume environments.