Results for “wshitm_request_control_u1”

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

Overview

WSH.WSH_ITM_REQUEST_CONTROL is the Request Interface table for Oracle E-Business Suite's Integrated Transportation Management (ITM) integration framework. It functions as the inbound staging area through which external or integrating applications submit transportation-related requests into Oracle Shipping Execution. Each row in the table represents a single distinct request. The integrating application inserts records into this interface table and subsequently relies on the transaction control identifier to poll for processing status.

Processing status is governed by the PROCESS_FLAG column, which carries well-defined codes: 0 indicates an unprocessed request; 1 indicates processing completed with success; 2 indicates processing completed with errors; 3 indicates the request was overridden by the integrating application. Additional internal flag codes include -1 (picked up by the poller), -2 (picked up by the poller with tasks added to the queue), and 4 (history record). When a request has been processed successfully, the RESPONSE_HEADER_ID column points to the corresponding row in WSH_ITM_RESPONSE_HEADERS, which carries the response payload for the submitted request. This request/response pairing forms the core of the asynchronous ITM message exchange pattern.

From a modeling perspective, the heuristic Data Vault classification for this object is hub-leaning, suggesting it behaves primarily as a durable key-bearing entity around which dependent satellites and links are organized. The table is stored in the APPS_TS_TX_DATA tablespace with PCT Free of 10, and its indexes reside in APPS_TS_TX_IDX.

Key Information Stored

The physical schema documents 93 columns. The most significant include:

• REQUEST_CONTROL_ID — the surrogate primary key and the column underpinning the unique index WSHITM_REQUEST_CONTROL_U1. This is the single business-key candidate and the identifier returned to the integrating application for status polling.
• REQUEST_SET_ID — groups multiple requests into a logical request set; indexed by WSHITM_REQUEST_CONTROL_N3.
• APPLICATION_ID — identifies the calling application; foreign key to FND_APPLICATION and part of the composite index WSHITM_REQUEST_CONTROL_N2.
• PROCESS_FLAG — the request lifecycle status indicator, indexed together with ONLINE_FLAG by WSHITM_REQUEST_CONTROL_N1.
• ONLINE_FLAG — indicates whether the request is processed in online versus batch mode.
• RESPONSE_HEADER_ID — foreign key to WSH_ITM_RESPONSE_HEADERS, populated after successful processing.
• MASTER_ORGANIZATION_ID and ORGANIZATION_ID — master and calling organization identifiers of the requesting user.
• APPLICATION_USER_ID — the application user submitting the request.
• SERVICE_TYPE_CODE and TRANSACTION_DATE — the requested service and its transaction date.
• ORIGINAL_SYSTEM_REFERENCE and ORIGINAL_SYSTEM_LINE_REFERENCE — source-system correlation keys, indexed with APPLICATION_ID.
• SHIP_FROM_COUNTRY_CODE and SHIP_TO_COUNTRY_CODE — shipping geography attributes for the request.

The table also carries 15 name/value attribute pairs (ATTRIBUTE1_NAME through ATTRIBUTE15_NAME and ATTRIBUTE1_VALUE through ATTRIBUTE15_VALUE), plus a standard DFF block (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15), and Audit columns including LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The primary operational use case is status monitoring of submitted interface requests. An integrating application polls by REQUEST_CONTROL_ID to determine whether processing completed, failed, or remains pending:

• Retrieve pending work: filter on PROCESS_FLAG = 0 or the poller internal codes (-1, -2) to identify requests awaiting pickup.
• Error reconciliation: select rows where PROCESS_FLAG = 2 to isolate failed requests for correction and resubmission.
• Response retrieval: join RESPONSE_HEADER_ID to WSH_ITM_RESPONSE_HEADERS to obtain the response payload for successful requests.
• Set-level reporting: group by REQUEST_SET_ID to summarize outcome across a batch of logically clustered requests.
• Source-system traceability: query on APPLICATION_ID combined with ORIGINAL_SYSTEM_REFERENCE and ORIGINAL_SYSTEM_LINE_REFERENCE to reconcile back to the originating external document.

A representative pattern joins the status table to its response counterpart: select r.REQUEST_CONTROL_ID, r.PROCESS_FLAG, h.RESPONSE_HEADER_ID from WSH_ITM_REQUEST_CONTROL r left join WSH_ITM_RESPONSE_HEADERS h on r.RESPONSE_HEADER_ID = h.RESPONSE_HEADER_ID where r.PROCESS_FLAG = 2. Indexed access paths on PROCESS_FLAG/ONLINE_FLAG (N1), APPLICATION_ID/original references (N2), and REQUEST_SET_ID (N3) support these reporting patterns efficiently.

Related Objects

The table participates in a network of relationships anchored on REQUEST_CONTROL_ID:

• WSH_ITM_RESPONSE_HEADERS — child table referenced by RESPONSE_HEADER_ID and also holding REQUEST_CONTROL_ID as a foreign key; carries the response to each submitted request.
• WSH_ITM_ITEMS — references REQUEST_CONTROL_ID; holds line-level detail for submitted requests.
• WSH_ITM_PARTIES — references REQUEST_CONTROL_ID; stores party-level request information.
• XLA_UPGRADE_REQUESTS — references REQUEST_CONTROL_ID, linking request control into Subledger Accounting upgrade processing.
• FND_APPLICATION — referenced through APPLICATION_ID to identify the submitting application.
• PN_PAYMENT_TERMS_ALL — referenced through PAYMENT_TERM_ID to resolve payment term details on the request.

Together these objects form the ITM request/response interface, with WSH_ITM_REQUEST_CONTROL acting as the central hub for inbound request submission and status tracking.