Oracle API Script for Pick Release for Sales order( wsh_deliveries_pub.delivery_action)
---sample script for pick release---
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 ='66404'--v_order
AND oha.org_id = 204--org id;
BEGIN
-- Initializing the Applications
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'OPERATIONS';
SELECT responsibility_id, application_id--, responsibility_name
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name like 'Order Management Super User, Vision Operations (USA)';
--fnd_global.apps_initialize (l_user_id,l_resp_id,l_appl_id);
fnd_global.apps_initialize (1318,21623,660);
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;
No comments:
Post a Comment