Pick release API

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 in ('300302173',
                          '300302172',
                          '300302171',
                          '300302166',
                          '300302165')
and oha.org_id = 551;




BEGIN

-- Initializing the Applications

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'PERS'; --

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 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;