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);
l_exception 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 = 10001059
AND oha.org_id = 308;
BEGIN
/*--
Initializing the Applications
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'A42485';
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Order
Management Super User';
fnd_global.apps_initialize (l_user_id,
l_resp_id, l_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 l_exception;
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 l_exception;
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 l_exception
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;
