Results for “oe_return_line_id”

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

Overview

AHL_OSP_ORDER_LINES is a transaction table in the AHL (Complex Maintenance Repair and Overhaul) product family of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. The table stores the individual line-level details of an Outside Processing (OSP) order — the arrangement by which a repair or maintenance activity is routed to an external supplier for services such as component overhaul, exchange, or refurbishment. Each row represents one service line belonging to a parent OSP order header.

The object plays a central integration role in the depot repair and MRO flow. A single OSP order line can reference purchasing documents (requisition, purchase order line, line type), order management documents (ship and return lines), the internal work order driving the repair, and inventory item or serial/lot detail. This breadth of foreign keys means the table functions as the operational bridge between AHL work execution and the Oracle Purchasing and Order Management modules.

From a data-vault modeling perspective, the documented FK structure yields a heuristic classification of link. The table primarily resolves and records relationships between the OSP order header, purchasing lines, order management lines, and work orders rather than acting as a standalone descriptive hub. Modelers should treat it as a transactional link table whose descriptive attributes (status, dates, quantities) behave satellite-like, though no formal Data Vault design is documented by Oracle.

Key Information Stored

The documented physical schema contains 48 columns. The most consequential are:

• OSP_ORDER_LINE_ID — surrogate primary key (AHL_OSP_ORDER_LINES_PK).
• OSP_ORDER_ID — FK to AHL_OSP_ORDERS_B, the parent order header; combined with OSP_LINE_NUMBER this forms business-key unique index AHL_OSP_ORDER_LINES_U2.
• OSP_LINE_NUMBER — line sequence within the order.
• WORKORDER_ID — FK to AHL_WORKORDERS, tying the line to the maintenance work order.
• STATUS_CODE — lifecycle state of the OSP line.
• PO_LINE_ID — FK to PO_LINES_ALL; carries its own unique index (AHL_OSP_ORDER_LINES_U3).
• PO_LINE_TYPE_ID — FK to PO_LINE_TYPES_B.
• PO_REQ_LINE_ID — FK to PO_REQUISITION_LINES_ALL; unique index AHL_OSP_ORDER_LINES_U8.
• OE_SHIP_LINE_ID and OE_RETURN_LINE_ID — FKs to OE_ORDER_LINES_ALL for outbound shipment and return of the serviced item.
• SERVICE_ITEM_ID, SERVICE_ITEM_DESCRIPTION, SERVICE_ITEM_UOM_CODE — the external service being procured.
• NEED_BY_DATE and SHIP_BY_DATE — scheduling and expediting dates.
• QUANTITY — service quantity on the line.
• INVENTORY_ITEM_ID, INVENTORY_ORG_ID, SUB_INVENTORY, SERIAL_NUMBER, LOT_NUMBER, INVENTORY_ITEM_QUANTITY — material and inventory context.
• SECURITY_GROUP_ID — FK to FND_SECURITY_GROUPS for multi-org/security partitioning.
• OBJECT_VERSION_NUMBER and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — concurrency control and audit.

Unique indexes U1–U3 and U8 confirm the business-key candidates; the fifteen ATTRIBUTE columns hold flexfield-style descriptive data.

Common Use Cases and Queries

Typical reporting and reconciliation needs include tracking open OSP lines against their purchase orders, identifying lines with overdue NEED_BY_DATE, and tracing the ship/return movement of serialized items. A representative join pattern:

• Join AHL_OSP_ORDER_LINES to AHL_OSP_ORDERS_B on OSP_ORDER_ID to obtain order-level context.
• Join to PO_LINES_ALL on PO_LINE_ID for actual purchasing status and price.
• Join to AHL_WORKORDERS on WORKORDER_ID to correlate the repair activity.
• Left-join OE_ORDER_LINES_ALL twice (OE_SHIP_LINE_ID and OE_RETURN_LINE_ID) to report outbound and inbound logistics.

Sample predicate for outstanding supplier activity: select lines where STATUS_CODE indicates open and NEED_BY_DATE is less than SYSDATE, restricted by SECURITY_GROUP_ID to the operating unit. Reports frequently group by WORKORDER_ID or OSP_ORDER_ID and aggregate QUANTITY. Because PO_LINE_ID and PO_REQ_LINE_ID are uniquely indexed, these are efficient correlation points back to Purchasing.

Related Objects

• AHL_OSP_ORDERS_B — parent header; joined via OSP_ORDER_ID (mandatory).
• AHL_WORKORDERS — maintenance work order driving the line; joined via WORKORDER_ID.
• PO_LINES_ALL — purchase order line carrying the procured service; joined via PO_LINE_ID.
• PO_LINE_TYPES_B — PO line type reference; joined via PO_LINE_TYPE_ID.
• PO_REQUISITION_LINES_ALL — originating requisition line; joined via PO_REQ_LINE_ID.
• OE_ORDER_LINES_ALL — order management lines for shipment and return; joined via OE_SHIP_LINE_ID and OE_RETURN_LINE_ID.
• AHL_WARRANTY_ENTITLEMENTS — warranty coverage referencing this line through OSP_ORDER_LINE_ID.
• FND_SECURITY_GROUPS — security partitioning; joined via SECURITY_GROUP_ID.