I have been searching and searching trying to figure out how to do this, but I can't seem to find the answer...
I have a list of delivery names saved in a custom table. Now what I want to assign all those deliveries to a trip, and ship confirm the trip.
Right now I have the attached code working. It will successfully create a unique trip for each delivery, and ship confirm each trip. But I really need to be able to create only ONE trip for all deliveries. Please help!
function f_download_order_ship_confirm return boolean
is
cursor c_deliveries is
select *
from tup_mexico_dlvrs_to_reconcile
where nvl(attribute1,'0') != 'SHIP_CONFIRMED'
order by delivery_name;
p_ship_conf_status varchar2(5000);
x_msg_data varchar2(5000);
p_api_version_number number;
init_msg_list varchar2(30);
x_msg_count number;
x_msg_details varchar2(32000);
x_msg_summary varchar2(32000);
p_validation_level number;
p_commit varchar2(30);
x_return_status varchar2(15);
source_code varchar2(15);
changed_attributes wsh_delivery_details_pub.changedattributetabtype;
p_action_code varchar2(15);
p_delivery_id number;
p_delivery_name varchar2(30);
p_asg_trip_id number;
p_asg_trip_name varchar2(30);
p_asg_pickup_stop_id number;
p_asg_pickup_loc_id number;
p_asg_pickup_loc_code varchar2(30);
p_asg_pickup_arr_date date;
p_asg_pickup_dep_date date;
p_asg_dropoff_stop_id number;
p_asg_dropoff_loc_id number;
p_asg_dropoff_loc_code varchar2(30);
p_asg_dropoff_arr_date date;
p_asg_dropoff_dep_date date;
p_sc_action_flag varchar2(10);
p_sc_close_trip_flag varchar2(10);
p_defer_iface varchar2(10);
p_sc_create_bol_flag varchar2(10);
p_sc_stage_del_flag varchar2(10);
p_sc_trip_ship_method varchar2(30);
p_sc_actual_dep_date varchar2(30);
p_sc_report_set_id number;
p_sc_report_set_name varchar2(60);
p_wv_override_flag varchar2(10);
x_trip_id varchar2(30);
x_trip_name varchar2(30);
p_msg_data varchar2(32000);
fail_api exception;
n_cursor_count number := 0;
v_first_delivery varchar2(30);
v_last_delivery varchar2(30);
begin
x_return_status := wsh_util_core.g_ret_sts_success;
p_action_code := 'CONFIRM';
p_sc_action_flag := 'B';
p_sc_close_trip_flag := 'Y';
p_defer_iface := 'N';
for x in c_deliveries loop
wsh_deliveries_pub.delivery_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 => p_msg_data
,p_action_code => p_action_code
,p_delivery_id => p_delivery_id
,p_delivery_name => x.delivery_name -- delivery name
,p_asg_trip_id => p_asg_trip_id
,p_asg_trip_name => p_asg_trip_name
,p_asg_pickup_stop_id => p_asg_pickup_stop_id
,p_asg_pickup_loc_id => p_asg_pickup_loc_id
,p_asg_pickup_loc_code => p_asg_pickup_loc_code
,p_asg_pickup_arr_date => p_asg_pickup_arr_date
,p_asg_pickup_dep_date => p_asg_pickup_dep_date
,p_asg_dropoff_stop_id => p_asg_dropoff_stop_id
,p_asg_dropoff_loc_id => p_asg_dropoff_loc_id
,p_asg_dropoff_loc_code => p_asg_dropoff_loc_code
,p_asg_dropoff_arr_date => p_asg_dropoff_arr_date
,p_asg_dropoff_dep_date => p_asg_dropoff_dep_date
,p_sc_action_flag => p_sc_action_flag
,p_sc_close_trip_flag => p_sc_close_trip_flag
,p_sc_create_bol_flag => p_sc_create_bol_flag
,p_sc_stage_del_flag => p_sc_stage_del_flag
,p_sc_trip_ship_method => p_sc_trip_ship_method
,p_sc_actual_dep_date => p_sc_actual_dep_date
,p_sc_report_set_id => p_sc_report_set_id
,p_sc_report_set_name => p_sc_report_set_name
,p_sc_defer_interface_flag => p_defer_iface
,p_wv_override_flag => p_wv_override_flag
,x_trip_id => x_trip_id
,x_trip_name => x_trip_name);
-- if api was not successful --
if (x_return_status <> wsh_util_core.g_ret_sts_success) then
wsh_util_core.get_messages('Y'
,x_msg_summary
,x_msg_details
,x_msg_count);
if x_msg_count > 1 then
x_msg_data := x_msg_summary || x_msg_details;
else
x_msg_data := x_msg_summary;
end if;
-- if api was successful --
else
-- mark as ship confirmed in custom table --
update tup_mexico_dlvrs_to_reconcile md
set md.attribute1 = 'SHIP_CONFIRMED'
,md.attribute5 = x_trip_id
where md.delivery_name = x.delivery_name;
end if;
end loop;
return true;
exception
when others then
return false;
end f_download_order_ship_confirm;
If you configure the Oracle EBS Shipping Execution module correctly, you can make it autocreate deliveries and assign them to open trips automatically.
Otherwise, you need to do that work yourself via the API.
The wsh_deliveries_pub public API, which you are already using, can do this if you specify the correct values for p_action_code
You'd probably want to call with "AUTOCREATE-TRIP" for the first delivery in the batch and then "ASSIGN-TRIP" for the subsequent ones.
Keep in mind the API will validate that the delivery can really be assigned to the trip you choose. There are rules. E.g., you cannot assign a delivery going to customer A to a trip that's only stopping at customer B.
Refer to Oracle's documentation for more about how to use the API with these action codes.