DECLARE
x_return_status VARCHAR2(2);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2(200);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2(100);
i NUMBER;
l_commit VARCHAR2(30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2(10);
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
CURSOR c_ord_details IS
SELECT oha.order_number sales_order
,oha.org_id
,ola.line_number
,ola.shipment_number
,ola.flow_status_code
,wdd.delivery_detail_id
,wdd.inv_interfaced_flag
,wdd.oe_interfaced_flag
,wdd.released_status
FROM apps.oe_order_headers_all oha
,apps.oe_order_lines_all ola
,apps.wsh_delivery_details wdd
WHERE oha.header_id = ola.header_id
AND oha.org_id = ola.org_id
AND oha.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND oha.booked_flag = "Y"
AND nvl(ola.cancelled_flag, "N") <> "Y"
AND wdd.released_status IN ("R", "B")
AND ola.flow_status_code = "AWAITING_SHIPPING"
AND oha.order_number = 1506764261
AND oha.org_id = 121;
--
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
--
BEGIN
--
/* To get the user id details */
SELECT user_id INTO v_user_id FROM fnd_user WHERE user_name = "SETUP01";
/* To get the responsibility and responsibility application id */
SELECT f.responsibility_id, f.application_id
INTO v_resp_id, v_resp_appl_id
FROM fnd_responsibility_tl f
WHERE f.responsibility_name = "受注管理スーパーユーザー（SC）"
AND f.language = "JA";
dbms_output.put_line(v_user_id || " " || v_resp_id || " " ||
v_resp_appl_id);
--
dbms_output.put_line("Starting of script");
-- Setting the Enviroment --
fnd_global.apps_initialize(user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id);
--
x_return_status := wsh_util_core.g_ret_sts_success;
i := 0;
FOR i IN c_ord_details
LOOP
-- Mandatory initialization for R12
mo_global.set_policy_context("S", i.org_id);
mo_global.init("ONT");
p_line_rows(1) := i.delivery_detail_id;
-- API Call for Auto Create Deliveries
dbms_output.put_line("Calling WSH_DELIVERY_DETAILS_PUB to Perform AutoCreate Delivery");
dbms_output.put_line("====================================================");
wsh_delivery_details_pub.autocreate_deliveries(p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows);
dbms_output.put_line(x_return_status);
dbms_output.put_line(x_msg_count);
dbms_output.put_line(x_msg_data);
IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
dbms_output.put_line("Failed to Auto create delivery for Sales Order");
RAISE exep_api;
ELSE
dbms_output.put_line("Auto Create Delivery Action has successfully completed for SO");
dbms_output.put_line("=============================================");
END IF;
-- Pick release.
p_delivery_id := x_del_rows(1);
p_delivery_name := to_char(x_del_rows(1));
dbms_output.put_line("Calling WSH_DELIVERIS_PUB to Perform Pick Release of SO");
dbms_output.put_line("=============================================");
-- API Call for Pick Release
wsh_deliveries_pub.delivery_action(p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => "PICK-RELEASE",
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => "S",
p_sc_intransit_flag => "N",
p_sc_close_trip_flag => "N",
p_sc_create_bol_flag => "N",
p_sc_stage_del_flag => "Y",
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => "Y",
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
p_wv_override_flag => "N",
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
dbms_output.put_line(x_return_status);
dbms_output.put_line(x_msg_count);
dbms_output.put_line(x_msg_data);
IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
dbms_output.put_line("Failed to Pick Release the sales order");
RAISE exep_api;
ELSE
dbms_output.put_line("Sales Order has successfully Pick Released");
dbms_output.put_line("==============================");
END IF;
--for pick confirm
--COMMIT;
END LOOP;
EXCEPTION
WHEN exep_api THEN
dbms_output.put_line("==============");
dbms_output.put_line("Error Details If Any");
dbms_output.put_line("==============");
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(x_msg_data);
ELSE
x_msg_data := x_msg_summary || "---" || x_msg_details;
dbms_output.put_line(x_msg_data);
END IF;
END;DECLARE
x_return_status VARCHAR2(2);
x_m
阅读背景：
使用API进行销售订单的Pick Release和Pick Confirm
来源：互联网
分享到：
