Introduction
This Post illustrates the steps required to pick release for sales order through oracle API script in Oracle EBS R12
Script to Oracle API Script for pick Release for Sales order(wsh_deliveries_pub.delivery_action
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;
What we expect in the script.
This script helps us to comprehend how the Oracle API Script is used for pick Release for Sales order(wsh_deliveries_pub.delivery_action. A couple of tables which is being used in the scripts are
Wsh_Delivery_Details,Fnd_Responsibility,etc.
Summary
This Post described the script for Oracle API Script for pick Release for Sales order(wsh_deliveries_pub.delivery_action) in Oracle EBS R12.
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
