Search Results delete_interface_records

Overview

APPS.WSH_PROCESS_INTERFACED_PKG is a PL/SQL package in the Oracle E-Business Suite Shipping Execution (WSH) module. Its principal business function is to process records that have been staged in the Shipping open interface tables, validating and moving that data into the base shipping and delivery tables. In Oracle EBS 12.1.1 and 12.2.2, outbound and inbound shipping transactions, delivery assignments, delivery legs, and freight costs are frequently generated by external systems or feeder processes and deposited into interface tables before they are recognized by Shipping Execution. This package orchestrates that transition, converting staging rows into operational delivery, delivery detail, and transaction records.

The ETRM metadata classifies the object as an OTHER API type and records a status of VALID. It is documented as depending on WSH_DEBUG_SV for diagnostic logging and on WSH_TRANSACTIONS_HISTORY_PKG, indicating that transaction history is maintained as part of the interface processing. The package sits within a tightly coupled family of Shipping utilities, and its removal or modification would directly affect dependent packages.

Key Procedures and Functions

The ETRM documentation identifies four procedures or functions within the package. Parameter lists are not published in the metadata, so only their documented purpose is described.

• PROCESS_INBOUND — The primary driver. It processes inbound interface records, reading the staging tables and applying validation and transformation logic to create or update delivery, delivery detail, and transaction records in the Shipping Execution base tables.
• DERIVE_IDS — Derives the internal identifiers required to link interface records to their target parent and child entities, such as delivery, delivery assignment, and location identifiers, so that staging rows can be correlated with existing shipping data.
• DELETE_INTERFACE_RECORDS — Removes successfully processed rows from the interface tables, clearing the staging area after the data has been transferred into the base tables.
• NAME — A utility procedure or function that returns the name of the package, a common convention in Oracle EBS packages used for diagnostic and debugging output, particularly in conjunction with the WSH_DEBUG_SV debugging utility.

Tables Accessed

The package operates across shipping interface, setup, and base tables. The shipping interface tables — WSH_DEL_ASSGN_INTERFACE, WSH_DEL_DETAILS_INTERFACE, WSH_DEL_LEGS_INTERFACE, WSH_FREIGHT_COSTS_INTERFACE, and WSH_NEW_DEL_INTERFACE — are the staging sources from which records are consumed. Error rows encountered during processing are recorded in WSH_INTERFACE_ERRORS. Output is written to base shipping tables including WSH_NEW_DELIVERIES and WSH_TRANSACTIONS_HISTORY.

Reference and setup data is validated against Oracle master and Trading Community tables: MTL_SYSTEM_ITEMS for item validation, HR_ORGANIZATION_INFORMATION for organization and location context, WSH_LOCATIONS for shipping locations, and the HZ tables — HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, and HZ_PARTY_SITES — to resolve customer, site, and party identifiers. All references are made through APPS synonyms.

Usage Notes

WSH_PROCESS_INTERFACED_PKG is not normally called directly by end users. It is invoked by feeder packages and concurrent programs that populate the shipping interface tables and then trigger interface processing. The metadata confirms it is referenced by the following packages: WSH_INBOUND_SHIP_ADVICE_PKG, WSH_TRANSACTIONS_TPW_UTIL, WSH_TRANSACTIONS_UTIL, and by itself. This dependency pattern is consistent with its use in processing inbound ship advice, transportation planning, and general shipping transaction workflows.

Because it appears within the Shipping Execution processing chain, customizations should treat it as an internal package. Calling code must ensure the interface tables are populated before invocation and that error records in WSH_INTERFACE_ERRORS are monitored afterward. The DELETE_INTERFACE_RECORDS procedure reinforces the expectation that staged rows are transient and are purged after successful transfer, so any custom logic depending on interface row persistence must account for this behavior.