Delivery is Shipped Status but Order is Not Closed

Delivery is Shipped Status but Order is Not Closed

Introduction

In Oracle EBS, there are instances where a delivery reaches the Shipped status, but the associated Trip remains open. This can happen when the Interface Trip Stop program does not trigger correctly. As a result, the delivery gets closed, but the trip remains open, causing inconsistencies in order processing and logistics tracking.

To resolve this issue, we need a method to identify these records and close the trips programmatically using APIs.

Identifying Shipments with Open Trips

To find deliveries in the Shipped status with open trips, we can use the following SQL query:

SELECT ooha.order_number, ooha.header_id, wt.trip_id, wdv.delivery_id,

wdv.released_status_name, wdd.requested_quantity, wdd.shipped_quantity,

pickup_stop.stop_id p1, dropoff_stop.stop_id p2

FROM oe_order_headers_all ooha,

oe_order_lines_all oola,

wsh_delivery_details wdd,

wsh_carriers wca,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd,

wsh_delivery_legs wdl,

wsh_document_instances wdi,

wsh_trip_stops pickup_stop,

wsh_trip_stops dropoff_stop,

wsh_trips wt,

wsh_deliverables_v wdv

WHERE 1 = 1

AND wdd.source_code = ‘OE’

AND wdd.released_status = ‘Y’

AND wnd.status_code = ‘CO’

AND wt.status_code = ‘OP’

AND TRUNC (ooha.creation_date) >= TRUNC (SYSDATE-3)

AND ooha.flow_status_code = ‘BOOKED’

AND ooha.header_id = oola.header_id

AND wdl.pick_up_stop_id = pickup_stop.stop_id

AND wdl.drop_off_stop_id = dropoff_stop.stop_id

AND pickup_stop.trip_id = wt.trip_id

AND NVL (wnd.shipment_direction, ‘O’) IN (‘O’, ‘IO’)

AND ooha.header_id = wdd.source_header_id(+)

AND oola.line_id = wdd.source_line_id(+)

AND wda.delivery_detail_id = wdd.delivery_detail_id

AND wca.carrier_id(+) = wdd.carrier_id

AND wnd.delivery_id = wda.delivery_id

AND wnd.delivery_id = wdl.delivery_id(+)

AND wdi.entity_id(+) = wdl.delivery_leg_id

AND wdi.entity_name(+) = ‘WSH_DELIVERY_LEGS’

AND wdi.status(+) <> ‘CANCELLED’

AND wdd.delivery_detail_id = wdv.delivery_detail_id(+)

AND pickup_stop.status_code = ‘OP’

This query identifies trips where the delivery is shipped but the trip is still open.

Closing the Trip Using API

Once we identify the affected trips, we can close them using the WSH_TRIP_STOPS_PUB.STOP_ACTION. Below is an example of how to use this API to close an open trip:

DECLARE

l_operation              VARCHAR2 (30)   := ‘Closing Stop’;

x_return_status          VARCHAR2 (1);

x_msg_data               VARCHAR2 (2000);

x_msg_count              VARCHAR2 (2000);

–Standard Parameters.

p_api_version_number     NUMBER;

init_msg_list            VARCHAR2 (30);

x_msg_details            VARCHAR2 (3000);

x_msg_summary            VARCHAR2 (3000);

p_validation_level       NUMBER;

p_commit                 VARCHAR2 (30);

–Parameters for WSH_TRIP_STOPS_PUB.STOP_ACTION

p_action_code            VARCHAR2 (200);

p_stop_id                NUMBER;

p_trip_id                NUMBER;

p_trip_name              VARCHAR2 (200);

p_stop_location_id       NUMBER;

p_stop_location_code     VARCHAR2 (200);

p_planned_dep_date       DATE;

p_actual_date            DATE;

p_defer_interface_flag   VARCHAR2 (200);

—

BEGIN

—

fnd_global.apps_initialize (user_id           => 1,

resp_id           => 21623,

resp_appl_id      => 660

);

x_return_status := wsh_util_core.g_ret_sts_success;

p_api_version_number := 1.0;

p_action_code := ‘CLOSE’;

p_stop_id := p_trip_stop_id;

–P_TRIP_ID := 7292207;–recopen.tripid;

p_trip_name := NULL;

p_stop_location_id := NULL;

p_stop_location_code := NULL;

p_planned_dep_date := NULL;

p_actual_date := SYSDATE;

p_defer_interface_flag := ‘N’;

–========================================================================

— Delivery Action API (Reopening a Delivery)

–========================================================================

wsh_trip_stops_pub.stop_action

(p_api_version_number        => 1.0,

p_init_msg_list             => init_msg_list,

x_return_status             => x_return_status,

x_msg_count                 => x_msg_count,

x_msg_data                  => x_msg_data,

p_action_code               => p_action_code,

p_stop_id                   => p_stop_id,

p_trip_id                   => p_trip_id,

p_trip_name                 => p_trip_name,

p_stop_location_id          => p_stop_location_id,

p_stop_location_code        => p_stop_location_code,

p_planned_dep_date          => p_planned_dep_date,

p_actual_date               => p_actual_date,

p_defer_interface_flag      => p_defer_interface_flag

);

 

–========================================================================

— COMMIT/ROLLBACK

–========================================================================

IF x_return_status = wsh_util_core.g_ret_sts_success

THEN

DBMS_OUTPUT.put_line (l_operation || ‘ done successfully.’);

COMMIT;

ELSE

DBMS_OUTPUT.put_line (‘Failure.’);

DBMS_OUTPUT.put_line (‘Return Status = ‘ || x_return_status);

wsh_util_core.get_messages (‘Y’, x_msg_data, x_msg_details,

x_msg_count);

DBMS_OUTPUT.put_line (l_operation || ‘: ‘);

DBMS_OUTPUT.put_line (‘Summary: ‘ || SUBSTRB (x_msg_data, 1, 200));

DBMS_OUTPUT.put_line (‘Detail: ‘ || SUBSTRB (x_msg_details, 1, 200));

ROLLBACK;

END IF;

END;

/

Conclusion

Ensuring that Trips are properly closed when deliveries reach the Shipped status is crucial for maintaining clean logistics records. By leveraging SQL queries and Oracle APIs, you can efficiently identify and close open trips, thereby improving order management and reporting accuracy.