/* Formatted on 31-Dec-14 1:29:47 PM (QP5 v5.114.809.3010) */
DECLARE
x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
--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_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);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
p_sc_defer_interface_flag VARCHAR2(1);
p_init_msg_list VARCHAR2(30);
/*Handle exceptions*/
fail_api EXCEPTION;
Cursor c1 is SELECT DELIVERY_ID,
name,
status_name,
ORGANIZATION_ID,
(SELECT organization_code
FROM org_organization_definitions ood
WHERE ood.organization_id = wn.organization_id
) Org_name
FROM WSH_NEW_DELIVERIES_V wn
WHERE status_code = 'CO'
and wn.organization_id IN ( select ORGANIZATION_ID from org_organization_definitions where SET_OF_BOOKS_ID = 2021)
-- and delivery_id = 2488014
order by 4;
BEGIN
/* Initialize return status*/
for rec in c1
Loop
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
/* Call this procedure to initialize applications parameters. To determine
parameter values, refer to the Application Paramater Initialization section of
this chapter. */
FND_GLOBAL.APPS_INITIALIZE (user_id => 1204,
resp_id => 53370,
resp_appl_id => 660);
/* Values to be set for action code 3 are */
p_action_code := 'RE-OPEN'; -- The action code for ship confirm
p_delivery_id := rec.delivery_id; -- The delivery that needs to be confirmed. See note below
-- p_delivery_name := '5341'; -- The delivery name. This is probably not needed
-- p_sc_action_flag := 'C'; -- Backorder all commented by Rushi
-- P_sc_stage_del_flag := 'N'; -- commented by Rushi
-- p_sc_trip_ship_method := 'GROUND'; -- The ship method code. If backordering deliveries, this does not need to be set
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
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 => 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,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);
dbms_output.put_line('The REOPEN action on the delivery '||p_delivery_id||' is successful');
IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
THEN
RAISE fail_api;
END IF;
----------------------------------------------------------
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := rec.delivery_id; -- 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);
----------------------------------------------------------------------------
dbms_output.put_line('The confirm action on the delivery '||p_delivery_id||' is successful');
end loop;
EXCEPTION
WHEN fail_api
THEN
dbms_output.put_line('Delivery id '||p_delivery_id||' is unsuccessful');
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;
END;