Introduction
This Post illustrates the steps required to ship conform the sales order using Oracle API Script in Oracle EBS R12
Script to Oracle API for SHIP CONFORMATION Using(WSH_DELIVERIES_PUB.Delivery_Action)
Declare
–Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
–Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
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_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);
p_sc_defer_interface_flag VARCHAR2(1);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
–out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
l_header_id number;
l_delivery_id number;
l_delivery_detail_id NUMBER;
— Handle exceptions
vApiErrorException EXCEPTION;
BEGIN
SELECT header_id
INTO l_header_id
FROM oe_order_headers_all
WHERE order_number=’66404′ AND ROWNUM=1;
SELECT MAX(delivery_detail_id)
INTO l_delivery_detail_id
FROM wsh_delivery_details
WHERE source_header_id=l_header_id;
— Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
SELECT DELIVERY_ID INTO l_delivery_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id=l_delivery_detail_id;
— Call this procedure to initialize applications parameters
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
, resp_id => 21623
, resp_appl_id => 660);
— Values for Ship Confirming the delivery
p_action_code := ‘CONFIRM’; — The action code for ship confirm
p_delivery_id :=l_delivery_id;–6312910; — The delivery that needs to be confirmed
p_sc_action_flag := ‘S’; — Ship entered quantity.
p_sc_close_trip_flag := ‘Y’; — Close the trip after ship confirm
p_sc_trip_ship_method := ‘DHL’; — The ship method code
p_sc_defer_interface_flag := ‘N’;
— Call to WSH_DELIVERIES_PUB.Delivery_Action.
WSH_DELIVERIES_PUB.Delivery_Action(
p_api_version_number => 1.0,
p_init_msg_list => p_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_delivery_id => p_delivery_id,
p_delivery_name => p_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_wv_override_flag => p_wv_override_flag,
p_sc_defer_interface_flag => p_sc_defer_interface_flag ,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
THEN
RAISE vApiErrorException;
ELSE
dbms_output.put_line(‘The confirm action on the delivery ‘||p_delivery_id||’ is successful’);
END IF;
EXCEPTION
WHEN vApiErrorException
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;
— dbms_output.put_line(‘Message Data : ‘||x_msg_data);—comment 29/5
ELSE
x_msg_data := x_msg_summary;
— dbms_output.put_line(‘Message Data : ‘||x_msg_data);—–comment 29/5
END IF;
WHEN OTHERS
THEN
dbms_output.put_line(‘Unexpected Error: ‘||SQLERRM);
commit;
END ;
What we expect in the script.
This script helps us to comprehend how the oracle API Script is used for ship conform the sales order.A couple of tables which is being used in the scripts are wsh_Delivery_Details,wsh_Delivery_Assignments etc
Summary
This Post described the script for ship conform the sales order using Oracle API in Oracle EBS R12.
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
