Here we have used two APIs: The first API will create the delivery using the deliver detail ID and then the second API will release that delivery.
1) Create Delivery API --- wsh_delivery_details_pub.autocreate_deliveries
2) Release that Delivery API --- 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_commit VARCHAR2 (30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
api_exception EXCEPTION;
lc_process_flag VARCHAR2(5) := 'Y';
lc_inv_period VARCHAR2(10);
ln_hold_cnt NUMBER;
ln_onhand_quantity NUMBER := 0;
lc_run_mode VARCHAR2(10);
ln_request_id NUMBER := 0;
lg_request_id NUMBER := 0;
lc_export_phase_code VARCHAR2(2);
--
ln_qoh NUMBER := 0;
ln_rqoh NUMBER := 0;
ln_atr NUMBER := 0;
ln_att NUMBER := 0;
ln_qr NUMBER := 0;
ln_qs NUMBER := 0;
lb_lot_control_code BOOLEAN;
lb_serial_control_code BOOLEAN;
--
ln_delivery_detail_id NUMBER := 0;
ln_inv_item_id NUMBER := 0;
ln_organization_id NUMBER := 0;
ln_requested_qty NUMBER := 0;
ln_so_line_id NUMBER := 0;
lc_subinventory oe_order_lines_all.subinventory%TYPE;
BEGIN
--
BEGIN
SELECT wdd.delivery_detail_id, wdd.inventory_item_id, wdd.organization_id, wdd.requested_quantity,
ola.line_id, ola.subinventory
INTO ln_delivery_detail_id, ln_inv_item_id, ln_organization_id, ln_requested_qty, ln_so_line_id, lc_subinventory
FROM apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.wsh_delivery_details wdd,
apps.mtl_system_items_b msib
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 wdd.inventory_item_id = msib.inventory_item_id
AND wdd.organization_id = msib.organization_id
AND oha.booked_flag = 'Y'
AND NVL (ola.cancelled_flag, 'N') <> 'Y'
AND wdd.released_status IN ('R', 'B') -- Ready to Release, Backordered
AND ola.flow_status_code = 'AWAITING_SHIPPING'
AND ola.line_number = 1
AND oha.order_number = '3657782'
AND oha.org_id = 101;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(' Order delivery details are not found.');
WHEN others THEN
dbms_output.put_line(' Error while getting order delivery details: '||SQLERRM);
END;
--
x_return_status := wsh_util_core.g_ret_sts_success;
--Check Hold applied or not on SO Line using SO_Line_ID
BEGIN
SELECT count(*) INTO ln_hold_cnt
FROM oe_order_holds_all
WHERE line_id = ln_so_line_id
AND released_flag = 'N'
AND hold_release_id IS NULL;
EXCEPTION
WHEN no_data_found THEN
lc_process_flag := 'Y';
WHEN others THEN
dbms_output.put_line( ' Error while checking on hand quantity: '||SQLERRM);
lc_process_flag := 'N';
END;
--
IF (ln_hold_cnt > 0) THEN
dbms_output.put_line( ' Validation Error: Hold has applied on Selected SO Line');
lc_process_flag := 'N';
END IF;
-- Check Inventory period is open or not
BEGIN
SELECT period_name INTO lc_inv_period
FROM org_acct_periods_v
WHERE organization_id = ln_organization_id
AND period_name = TO_CHAR(TRUNC(sysdate),'MON-RR')
AND status = 'Open';
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line( ' Validation Error: Current Date Inventory Period is not opened. Please alter.');
lc_process_flag := 'N';
END;
-- Check On Hand quantity against SO Line item
inv_quantity_tree_pub.query_quantities (
p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => ln_organization_id,
p_inventory_item_id => ln_inv_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => lb_lot_control_code,
p_is_serial_control => lb_serial_control_code,
p_revision => NULL,
p_lot_number => NULL,
p_lot_expiration_date => SYSDATE,
p_subinventory_code => lc_subinventory,
p_locator_id => NULL,
p_onhand_source => 3, -- All subinventories
x_qoh => ln_qoh,
x_rqoh => ln_rqoh,
x_qr => ln_qr,
x_qs => ln_qs,
x_att => ln_att, -- available to transact
x_atr => ln_atr -- available to reserve
);
--
IF ( ln_att < ln_requested_quantity) OR ln_att IS NULL THEN
dbms_output.put_line( ' Validation Error: Onhand quantity is not available.');
dbms_output.put_line( ' -- Requested quantity : '||ln_requested_quantity);
dbms_output.put_line( ' -- Available to transact quantity : '||ln_att);
lc_process_flag := 'N';
END IF;
--
IF (lc_process_flag = 'Y') THEN
p_line_rows (1) := ln_delivery_detail_id; -- we can create delivery using Delivery Detail ID
--
dbms_output.put_line( ' ');
dbms_output.put_line( ' Call Auto Create Delivery API.. With Delivery Detail ID: '||ln_delivery_detail_id);
--1) API Call for Auto Create Deliveries
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
);
IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
dbms_output.put_line(' --> Failed to Auto create delivery for Sales Order');
dbms_output.put_line(' --> API Returned error message :'||x_msg_data);
RAISE api_exception;
ELSE
COMMIT;
END IF;
--
p_delivery_id := x_del_rows (1);
p_delivery_name := TO_CHAR (x_del_rows (1));
--
--2) Call API to create Pick Release.
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
);
IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN
dbms_output.put_line(' Failed to Pick Release the sales order');
RAISE api_exception;
ELSE
COMMIT;
dbms_output.put_line(' ');
lc_api_flag := 'Y';
END IF;
END IF; -- lc_process_flag
--
EXCEPTION
WHEN api_exception THEN
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( ' Msg Data1: '||x_msg_data);
ELSE
x_msg_data := x_msg_summary || x_msg_details;
dbms_output.put_line( ' Msg Data2: '||x_msg_data);
END IF;
END;
/
