/**************************************************************************************
* TYPE : Script *
* NAME : ship_confirm.sql *
* PURPOSE : This is to ship confirm the staged picked lines *
* *
* Author Date Ver Description *
* ------ ----------- ---- --------------------------- --------------------------*
* XXXXX 20-Feb-2020 1.00 Created *
**************************************************************************************/
SET SERVEROUT ON;
alter session set current_schema = apps;
DECLARE
--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
out_chr_errbuf VARCHAR2 (3000);
l_out_chr_errbuf VARCHAR2 (3000);
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_intransit_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);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
p_api_version_number NUMBER;
init_msg_list VARCHAR2(30);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
p_validation_level NUMBER;
l_chr_status VARCHAR2 (3) := 'E';
x_return_status VARCHAR2 (3);
l_chr_msg_data VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_delivery_detail_id wsh_util_core.id_tab_type;
l_del_rows wsh_util_core.id_tab_type;
l_num_index BINARY_INTEGER;
l_del_id NUMBER;
-- l_chr_check_delivery VARCHAR2 (3);
l_chr_return_status VARCHAR2(100);
l_chr_msg VARCHAR2(3000);
l_chr_result VARCHAR2(30);
l_num_line_id oe_order_lines_all.line_id%TYPE;
l_num_user_id NUMBER;
l_num_resp_id fnd_responsibility_vl.responsibility_id%TYPE;
l_num_resp_appl_id fnd_application.application_id%TYPE;
EXP_EXIT EXCEPTION;
f_log UTL_FILE.file_type;
l_num_count NUMBER;
CURSOR cur_get_del_details
IS
SELECT wdd.delivery_detail_id,ooha.order_number,
wda.delivery_id,wdd.organization_id
FROM wsh_delivery_details wdd,
oe_order_headers_all ooha,
wsh_delivery_assignments wda
WHERE ooha.header_id=wdd.source_header_id
AND wdd.released_status='Y'
AND wda.delivery_detail_id=wdd.delivery_detail_id
AND wdd.organization_id = 331
--AND ooha.header_id in (137508997)
and wdd.source_header_id in (135503888);
BEGIN
fnd_global.apps_initialize (11009, 67142, 20044);
l_delivery_detail_id.DELETE;
l_del_rows.DELETE;
l_num_count:=0;
FOR rec_get_del_details IN cur_get_del_details
LOOP
l_num_index:=1;
l_delivery_detail_id.DELETE;
l_del_rows.DELETE;
IF rec_get_del_details.delivery_id IS NULL THEN
l_delivery_detail_id(l_num_index):=rec_get_del_details.delivery_detail_id;
WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES (1.0
, fnd_api.g_true
, fnd_api.g_true
, l_chr_status
, l_num_msg_count
, l_chr_msg_data
, l_delivery_detail_id
, l_del_rows
);
l_del_id:=l_del_rows(l_num_index);
ELSE
l_del_id:=rec_get_del_details.delivery_id;
END IF;
--Ship confirm the delivery
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := l_del_id; -- The delivery that needs to be confirmed
p_delivery_name := to_char(l_del_id); -- The delivery name,
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_intransit_flag := 'Y'; -- In transit flag is set to 'Y' closes the
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
-- p_sc_trip_ship_method := 'GROUND'; -- The ship method code
-- Call to WSH_DELIVERIES_PUB.Delivery_Action.
begin
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_intransit_flag => p_sc_intransit_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);
exception when others then
null;
end;
l_num_count:=l_num_count+1;
begin
WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
exception when others then
null;
end;
END LOOP;
COMMIT;
BEGIN
WSH_SHIP_CONFIRM_ACTIONS.INTERFACE_ALL (errbuf => out_chr_errbuf,
retcode => l_out_chr_errbuf,
p_mode => 'ALL',
p_stop_id => NULL,
p_delivery_id => NULL,
p_log_level => 0,
p_batch_id => NULL,
p_trip_type => NULL,
p_organization_id => 331,
p_stops_per_batch => NULL
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error while ITT');
END;
EXCEPTION
WHEN OTHERS THEN
null;
END;
/
