Results for “ahl_osp_order_logs”

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

Overview

The AHL_OSP_ORDER_LOGS table is a transactional audit table within the AHL (Complex Maintenance Repair and Overhaul) product family of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It stores the log for Outside Processing (OSP) order transactions. In the OSP workflow, a maintenance or repair order component is sent to an external supplier for processing; each exchange of documents, status changes, and receipt or shipment activity against that OSP order generates a row in this table. The table therefore functions as the historical trail of OSP order activity, allowing users to reconstruct the sequence of events for a given order, diagnose processing failures, and report on turnaround performance.

From a heuristic Data Vault modeling perspective, the FK structure mined from the ETRM metadata classifies this table as standalone. That classification suggests the table behaves principally as a satellite-like record of transaction events, anchored by its own surrogate key rather than by a composite link of parent business keys. No hub-to-link parentage is implied by the documented foreign-key relationships, so modelers should treat AHL_OSP_ORDER_LOGS as a self-contained event repository whose integration point into a warehouse is its surrogate identifier and transaction attributes.

Key Information Stored

The table contains 32 documented columns. The most significant are summarized below.

• OSP_ORDER_LOG_ID — the surrogate primary key, enforced by the unique index AHL_OSP_ORDER_LOGS_U1 and the constraint AHLOSP_ORDER_LOG_ID_PK. This is the identifier most users search for (the ahlosp_order_log_id_pk query term) and the value to use when joining to child or log-detail structures.
• TRANSACTION_DATE — the timestamp at which the logged OSP transaction occurred; the primary axis for chronological reporting.
• TRANSACTION_TYPE_CODE — the lookup code classifying the nature of the transaction (for example, shipment, receipt, or status update) recorded by the row.
• TRANSACTION_STATUS_CODE — the outcome or processing state of the transaction, useful for identifying failed or pending OSP events.
• SOURCE_DOCUMENT_ID and SOURCE_DOCUMENT_TYPE_CODE — identify the originating document and its type, such as the OSP order or requisition from which the transaction was generated.
• DESTINATION_DOCUMENT_ID and DESTINATION_DOCUMENT_TYPE_CODE — identify the receiving document and its type, such as the receiving receipt or shipment confirmation.
• SECURITY_GROUP_ID — the operating unit or security context, referencing FND_SECURITY_GROUPS; it enforces multi-org data segregation.
• PROGRAM_ID — the concurrent program that created the log entry, valuable for tracing batch-driven OSP processing.
• OBJECT_VERSION_NUMBER — the optimistic locking column that guards against concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — the standard WHO columns documenting row audit history.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns allowing customers to extend the log with installation-specific data.

Beyond the primary key, the documented unique index AHL_OSP_ORDER_LOGS_U1 covers the same OSP_ORDER_LOG_ID column, so no separate business-key candidate is documented; the surrogate key doubles as the unique business identifier in the ETRM metadata.

Common Use Cases and Queries

Typical scenarios include tracing the complete transaction history of a single OSP order, reporting on OSP activity over a date range for a given operating unit, and diagnosing processing errors by filtering on transaction status or the originating concurrent program.

• Order history by source document: select all log rows where SOURCE_DOCUMENT_ID and SOURCE_DOCUMENT_TYPE_CODE match the OSP order in question, ordered by TRANSACTION_DATE.
• Status exception report: query rows where TRANSACTION_STATUS_CODE indicates failure, grouped by TRANSACTION_TYPE_CODE and PROGRAM_ID.
• Security-scoped activity: join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to restrict results to a specific operating unit. A representative pattern is SELECT l.osp_order_log_id, l.transaction_date, l.transaction_type_code, l.transaction_status_code FROM ahl.ahl_osp_order_logs l WHERE l.security_group_id = :p_security_group_id AND l.transaction_date BETWEEN :p_start AND :p_end ORDER BY l.transaction_date DESC;.
• Direct key lookup: when the user has the surrogate key, SELECT * FROM ahl.ahl_osp_order_logs WHERE osp_order_log_id = :p_id; is the fastest access path, since the primary key and the unique index both cover OSP_ORDER_LOG_ID.
• Extensibility-aware reporting: filter or select on ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 where custom DFF data has been captured.

Because the table records events rather than current state, reports should generally aggregate by order or transaction type rather than treating each row as the definitive status of an OSP order.

Related Objects

The following are the most significant objects associated with AHL_OSP_ORDER_LOGS.

• FND_SECURITY_GROUPS — referenced via the documented foreign key AHL_OSP_ORDER_LOGS.SECURITY_GROUP_ID → FND_SECURITY_GROUPS. The join supplies the operating unit or security context for each log row.
• AHL_OSP_ORDERS (and related OSP order tables) — the OSP order headers and lines that the SOURCE_DOCUMENT_ID/SOURCE_DOCUMENT_TYPE_CODE pairs typically identify. Joining on these columns links each log entry back to its originating OSP order.
• Transaction type and status lookup views (for example, FND_LOOKUPS-based views) — used to translate TRANSACTION_TYPE_CODE and TRANSACTION_STATUS_CODE into meaningful labels for reporting.
• FND_CONCURRENT_PROGRAMS — joinable via PROGRAM_ID to identify which concurrent program generated a given log entry.
• Receiving and shipping document tables (for example, receiving receipt or shipment headers) — the destination side of the DESTINATION_DOCUMENT_ID/DESTINATION_DOCUMENT_TYPE_CODE pair.
• AHL public APIs and OSP processing packages — the PL/SQL APIs that insert and update these log rows during OSP order processing; these are the programmatic entry points rather than direct DML targets.

Direct inserts into AHL_OSP_ORDER_LOGS are not recommended; the table is populated by the standard AHL OSP processing logic, and its surrogate key OSP_ORDER_LOG_ID should be treated as system-generated.