Results for “oe_lotserial_acks”

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

Overview

ONT.OE_LOTSERIAL_ACKS is a transactional table within the Oracle E-Business Suite Order Management (ONT) module. It stores acknowledgement records for lot and serial number information associated with return lines. In Oracle EBS releases 12.1.1 and 12.2.2, this table supports the inbound processing of return authorizations where trading partners (customers, suppliers, or third-party logistics providers) transmit confirmations of specific lot and serial number assignments against return order lines.

The object is classified as VALID and resides in the ONT schema. Its documented purpose is narrow and well-defined: capturing acknowledgement details for lot-controlled and serial-controlled return lines. Because the table records the state of acknowledgements received against a source transaction, its heuristic Data Vault classification is satellite-leaning. In Data Vault modeling terms, this suggests the table behaves as a descriptive satellite attached to a business key (the original system document/line/shipment reference combination), rather than acting as an independent hub or a pure link between two hubs. Analysts designing a Data Vault or dimensional layer over EBS Order Management data should treat ORIG_SYS_DOCUMENT_REF, ORIG_SYS_LINE_REF, and ORIG_SYS_SHIPMENT_REF as the natural business key context, with the remaining columns as descriptive attributes captured over time.

Key Information Stored

The table contains 49 documented columns. The most operationally significant are outlined below.

• ORIG_SYS_DOCUMENT_REF, ORIG_SYS_LINE_REF, ORIG_SYS_SHIPMENT_REF, ORIG_SYS_LOT_SERIAL_REF — The original source-system reference identifiers that tie each acknowledgement row back to the external document, line, shipment, and lot/serial entity. These form the primary business-key candidates for identifying a specific acknowledgement.
• LOT_NUMBER, SUBLOT_NUMBER, FROM_SERIAL_NUMBER, TO_SERIAL_NUMBER — The lot and serial number ranges being acknowledged. The FROM/TO range format supports bulk serial acknowledgements.
• QUANTITY, QUANTITY2 — Quantities associated with the acknowledgement, supporting reconciliation of acknowledged versus expected return quantities.
• ACKNOWLEDGMENT_FLAG, FIRST_ACK_CODE, LAST_ACK_CODE, FIRST_ACK_DATE, LAST_ACK_DATE — The acknowledgement status and code history, including timestamps for the first and most recent acknowledgements received.
• ORDER_SOURCE_ID — Foreign key to OE_ORDER_SOURCES, identifying the order source context for the acknowledgement.
• OPERATION_CODE, ERROR_FLAG, INTERFACE_STATUS — Interface-processing control columns used by concurrent programs to determine create/update/delete operations and to flag and track processing errors.

Standard EBS audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN), change-tracking columns (CHANGE_DATE, CHANGE_SEQUENCE), and a CONTEXT with ATTRIBUTE1 through ATTRIBUTE15 flexfield columns are also present. Surrogate-key/primary-key columns are not explicitly documented among the 49 columns; where a unique identifier is required, the ORIG_SYS_*_REF combination typically serves as the business unique key, while INST_ID appears as an installation identifier.

Common Use Cases and Queries

Typical use cases center on reconciliation of return authorizations and integration monitoring.

• Reconciling acknowledged returns — Identify return lines where the acknowledged lot or serial quantities differ from expected quantities, using QUANTITY against the source return line.
• Integration error monitoring — Query for rows where INTERFACE_STATUS or ERROR_FLAG indicates failed processing, filtered by REQUEST_ID or PROGRAM_ID to isolate a specific concurrent run.
• Acknowledgement trend reporting — Aggregate FIRST_ACK_DATE and LAST_ACK_DATE to measure turnaround time between return creation and partner acknowledgement.

A representative query joining the order source:

SELECT a.ORIG_SYS_DOCUMENT_REF, a.ORIG_SYS_LINE_REF,
       a.LOT_NUMBER, a.FROM_SERIAL_NUMBER, a.TO_SERIAL_NUMBER,
       a.QUANTITY, a.ACKNOWLEDGMENT_FLAG, a.INTERFACE_STATUS
FROM   ONT.OE_LOTSERIAL_ACKS a,
       ONT.OE_ORDER_SOURCES s
WHERE  a.ORDER_SOURCE_ID = s.ORDER_SOURCE_ID
AND    a.INTERFACE_STATUS = 'ERROR';

Related Objects

• OE_ORDER_SOURCES — Referenced via the documented foreign key OE_LOTSERIAL_ACKS.ORDER_SOURCE_ID → OE_ORDER_SOURCES. This is the only formally documented foreign key relationship for the table.
• OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL — Return order headers and lines that provide the transactional context the ORIG_SYS_*_REF columns resolve against.
• OE_RETURN_LOTS / OE_RETURN_SERIALS — Lot and serial detail tables for return lines, aligned with the LOT_NUMBER and serial range columns.
• MTL_LOT_NUMBERS / MTL_SERIAL_NUMBERS — Inventory master tables validating the lot and serial values being acknowledged.
• Order Management Returns APIs — Public ONT APIs and concurrent programs that process inbound return acknowledgements write to this table via the OPERATION_CODE and INTERFACE_STATUS interface columns.