Partial Shipments of a Sales Order Using API

Partial Shipments of a Sales Order Using API:
==================================

For partial shipment of the sales order, we need to call the WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes API to update the corresponding delivery details to ship all/entered quantity in the delivery details.

-- OM - Script to Ship Partial Quantities in a SO using WSH_DELIVERY_PUB API --
--===========================================================

DECLARE

p_sales_order NUMBER := 10014445;
p_line_number NUMBER := 1.1;
p_org_id NUMBER := 308;
l_shipped_quantity NUMBER := 5;
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
l_commit VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
x_return_status VARCHAR2 (3);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
p_validation_level NUMBER;
v_errbuf VARCHAR2 (2000);
v_retcode VARCHAR2 (20);
v_released_status wsh_delivery_details.released_status%TYPE;
v_inv_interfaced_flag wsh_delivery_details.inv_interfaced_flag%TYPE;
v_oe_interfaced_flag wsh_delivery_details.oe_interfaced_flag%TYPE;
v_source_code wsh_delivery_details.source_code%TYPE;
v_pending_interface_flag wsh_trip_stops.pending_interface_flag%TYPE;
l_changed_attributes wsh_delivery_details_pub.changedattributetabtype;
l_source_code VARCHAR2 (30) := 'OE';
-- Parameters for WSH_DELIVERIES_PUB
p_delivery_name VARCHAR2 (30);
p_action_code VARCHAR2 (15);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_intransit_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_sc_defer_interface_flag VARCHAR2 (60);
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
fail_api EXCEPTION;
x_debug_file VARCHAR2 (100);
l_ship_method_code VARCHAR2 (100);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_ord_details
IS

SELECT DISTINCT det.source_header_number sales_order, det.org_id,
det.source_line_number, det.source_header_id,
det.source_line_id, det.source_header_type_name,
det.inventory_item_id, det.requested_quantity,
det.delivery_detail_id,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id =
det.inventory_item_id
AND organization_id = det.organization_id)
ordered_item,
det.organization_id, det.src_requested_quantity,
det.shipped_quantity, del.delivery_id,
del.status_code delivery_status_code,
det.released_status pick_release_status,
det.oe_interfaced_flag, det.inv_interfaced_flag
FROM wsh_delivery_details det,
wsh_delivery_assignments asn,
wsh_new_deliveries del
WHERE 1 = 1
AND det.delivery_detail_id = asn.delivery_detail_id
AND asn.delivery_id = del.delivery_id(+)
AND det.source_header_number = p_sales_order
AND det.source_line_number = p_line_number
AND det.org_id = p_org_id
AND shipped_quantity IS NULL
AND NVL (del.status_code, 'OP') <> 'CL'
AND det.released_status = 'Y';

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

FOR i IN c_ord_details
LOOP
DBMS_OUTPUT.put_line
('Initializing the Application for Shipping Transactions');
-- Mandatory initialization for R12
mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('ONT');
-- Ship Confirming
p_delivery_name := TO_CHAR (i.delivery_id);

DBMS_OUTPUT.put_line
('Before Shipping, Calling WSH_DELIVERY_DETAILS_PUB API to Update Shipping Attributes'
);
DBMS_OUTPUT.put_line ('=============================================');
l_changed_attributes (1).delivery_detail_id := i.delivery_detail_id;
l_changed_attributes (1).shipped_quantity := l_shipped_quantity;
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_changed_attributes,
p_source_code => l_source_code
);

IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
RAISE fail_api;
DBMS_OUTPUT.put_line ('Failed to Update the Shipping Attributes');
ELSE
DBMS_OUTPUT.put_line ('Successfully Updated the Shipping Attributes');
END IF;

BEGIN
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE order_number = i.sales_order AND org_id = i.org_id;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_intransit_flag := 'Y';
--In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := l_ship_method_code; -- The ship method code
p_sc_defer_interface_flag := 'Y';
p_sc_stage_del_flag := 'Y';
p_sc_create_bol_flag := 'N';
p_wv_override_flag := 'N';

-- API Call for Ship Confirmation
DBMS_OUTPUT.put_line
('Calling WSH_DELIVERIES_PUB to Perform Ship Confirmation');
DBMS_OUTPUT.put_line ('=============================================');

wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => i.delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_stop_seq => p_asg_pickup_stop_seq,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_stop_seq => p_asg_dropoff_stop_seq,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => p_sc_intransit_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => p_sc_defer_interface_flag,
p_sc_send_945_flag => p_sc_send_945_flag,
p_sc_rule_id => p_sc_rule_id,
p_sc_rule_name => p_sc_rule_name,
p_wv_override_flag => p_wv_override_flag,
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
('Ship confirm has not been Completed For SO => ');
ROLLBACK;
RAISE fail_api;
ELSE
DBMS_OUTPUT.put_line
('Ship confirm Successfully Completed For SO => ');
COMMIT;

DBMS_OUTPUT.put_line
('Checking the Delivery Status after delivery action API Call');
DBMS_OUTPUT.put_line ('==========================================');

SELECT wdd.source_code, wdd.released_status,
wdd.inv_interfaced_flag, wdd.oe_interfaced_flag,
wts.pending_interface_flag
INTO v_source_code, v_released_status,
v_inv_interfaced_flag, v_oe_interfaced_flag,
v_pending_interface_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = 'Y'
AND wdd.inv_interfaced_flag <> 'Y'
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = p_delivery_name
AND wdd.source_line_id = i.source_line_id;

IF ( v_source_code = 'OE'
AND v_released_status = 'C'
AND v_inv_interfaced_flag <> 'Y'
AND v_oe_interfaced_flag <> 'Y'
AND v_pending_interface_flag = 'Y'
)
THEN

DBMS_OUTPUT.put_line
('The Delivery has been Shipped & the Next Step is - Run Interface'
);
DBMS_OUTPUT.put_line
('===========================================');
-- API Call for Submitting Interface Trip Stop

wsh_ship_confirm_actions.interface_all_wrp
(errbuf => v_errbuf,
retcode => v_retcode,
p_mode => 'ALL',
p_stop_id => NULL,
p_delivery_id => p_delivery_name,
p_log_level => 0,
p_batch_id => NULL,
p_trip_type => NULL,
p_organization_id => i.organization_id,
p_num_requests => 1,
p_stops_per_batch => 1
);

ELSE
DBMS_OUTPUT.put_line ('The Delivery has not Shipped Properly');
END IF;
END IF;
END LOOP;

EXCEPTION
WHEN fail_api
THEN

DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line ('Error Details If Any');
DBMS_OUTPUT.put_line ('==============');

wsh_util_core.get_messages (p_init_msg_list => 'Y',
x_summary => x_msg_summary,
x_details => x_msg_details,
x_count => 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;

Removing Enddate from Responsibilities given to Users

Sometimes while working on a support projects, we used to have access to the read only responsibilities ,or though we have given the access to the super users initially, but those accesses might have revoked after system went live. But in test environment, we may require those accesses back so as to fix the bugs or to test the functionality.
The removing of end date from a responsibility which is already assigned to a user, can be done using fnd_user_resp_groups_api API.

Sample Procedure for removing end date from Responsibilities given to Users:
--------------------------------------------------------------------------------------------


DECLARE

p_user_name VARCHAR2 (50) := 'A42485';
p_resp_name VARCHAR2 (50) := 'Order Management Super User';
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;

BEGIN

BEGIN
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE UPPER (user_name) = UPPER (p_user_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('User not found');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding User.');
RAISE;
END;

BEGIN
SELECT application_id, responsibility_id
INTO v_application_id, v_responsibility_id
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Responsibility not found.');
RAISE;
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line
('More than one responsibility found with this name.');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding responsibility.');
RAISE;
END;

BEGIN

DBMS_OUTPUT.put_line ('Initializing The Application');

fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_responsibility_id,
resp_appl_id => v_application_id
);

DBMS_OUTPUT.put_line
('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

fnd_user_resp_groups_api.update_assignment
(user_id => v_user_id,
responsibility_id => v_responsibility_id,
responsibility_application_id => v_application_id,
security_group_id => 0,
start_date => SYSDATE,
end_date => NULL,
description => NULL
);

DBMS_OUTPUT.put_line
('The End Date has been removed from responsibility');
COMMIT;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END;

Transact Move Order Using INV_PICK_WAVE_PICK_CONFIRM_PUB

R12 - INV - Sample Script to Transact Move Order Using
INV_PICK_WAVE_PICK_CONFIRM_PUB API

DECLARE

l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_move_order_type NUMBER := 1;
l_transaction_mode NUMBER := 1;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_transaction_date DATE := SYSDATE;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_mo_details IS

SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type,
mtrh.organization_id, mtrl.line_id, mtrl.line_number,
mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity,
revision, mtrl.from_locator_id,
(SELECT DISTINCT operating_unit
FROM org_organization_definitions
WHERE organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.request_number = '332557'
AND mtrh.organization_id = 381;

BEGIN

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 = 'Inventory';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_mo_details

LOOP

mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');
l_trolin_tbl (1).line_id := i.line_id;
-- call API to create move order header
DBMS_OUTPUT.put_line
('=======================================================');
DBMS_OUTPUT.put_line
('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API');

inv_pick_wave_pick_confirm_pub.pick_confirm
(p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_move_order_type => i.move_order_type,
p_transaction_mode => l_transaction_mode,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mmtt_tbl,
x_trolin_tbl => x_trolin_tbl,
p_transaction_date => l_transaction_date
);

DBMS_OUTPUT.put_line
('=======================================================');
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (x_msg_count);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;
DBMS_OUTPUT.put_line
('=======================================================');
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE ':' SQLERRM);
DBMS_OUTPUT.put_line
('=======================================================');
END;

DBMS Output:
=======================================================
Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API
=======================================================
S
=======================================================

Verification:

select * from mtl_material_transactions_temp
where transaction_temp_id = 1094230

No Rows Returned

-- Link the Move Order with the Material Transcations Table

SELECT
mmt.transaction_id,
mtrl.organization_id,
mtrh.request_number,
mtrh.header_id,
mtrl.line_number,
mtrl.line_id,
mtrl.inventory_item_id,
mtrh.description,
mtrh.move_order_type,
mtrl.line_status,
(select meaning from mfg_lookups
where lookup_type = 'MTL_TXN_REQUEST_STATUS'
and lookup_code = mtrl.line_status) Line_status_meaning,
mtrl.quantity,
mtrl.quantity_delivered,
mmt.transaction_type_id,
mmt.transaction_date
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_material_transactions mmt
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.organization_id = mtrl.organization_id
AND mtrl.line_id = mmt.move_order_line_id
AND mtrh.request_number = 332557
AND mtrh.organization_id = 381

Allocating Move Order INV_REPLENISH_DETAIL_PUB

R12 - INV - Sample Script to Allocate Move Order using
INV_REPLENISH_DETAIL_PUB API

DECLARE

l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_return_values VARCHAR2 (2) := fnd_api.g_false;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_user_id NUMBER ;
l_resp_id NUMBER ;
l_appl_id NUMBER ;
l_row_cnt NUMBER := 1;
l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_trohdr_rec inv_move_order_pub.trohdr_rec_type;
x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_number_of_rows NUMBER ;
x_transfer_to_location NUMBER ;
x_expiration_date DATE;
x_transaction_temp_id NUMBER ;

CURSOR c_mo_details IS

SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type,
mtrh.organization_id, mtrl.line_id, mtrl.line_number,
mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity,
revision,mtrl.from_locator_id,
(select distinct operating_unit from org_organization_definitions
where organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.request_number = '332557'
AND mtrh.organization_id = 381;

BEGIN

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 = 'Inventory';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_mo_details

LOOP

mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');

SELECT COUNT (*)
INTO x_number_of_rows
FROM mtl_txn_request_lines
WHERE header_id = i.header_id;

DBMS_OUTPUT.put_line ('Calling INV_REPLENISH_DETAIL_PUB to Allocate MO');
-- Allocate each line of the Move Order

inv_replenish_detail_pub.line_details_pub
(p_line_id => i.line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => i.quantity,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => i.revision,
x_locator_id => i.from_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => i.lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => NULL,
p_transaction_mode => NULL,
p_move_order_type => i.move_order_type,
p_serial_flag => fnd_api.g_false,
p_plan_tasks => FALSE,
p_auto_pick_confirm => FALSE,
p_commit => FALSE
);

DBMS_OUTPUT.put_line
('==========================================================');
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (x_msg_count);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;

IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('Trx temp ID: ');
DBMS_OUTPUT.put_line (x_transaction_temp_id);
END IF;
DBMS_OUTPUT.put_line
('==========================================================');
END LOOP;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE ':' SQLERRM);
DBMS_OUTPUT.put_line
('=======================================================');
END;

DBMS Output: -


Calling INV_REPLENISH_DETAIL_PUB to Allocate MO
==========================================================
S
Trx temp ID: 1094230
==========================================================


select * from mtl_material_transactions_temp
where transaction_temp_id = 1094230

1 Row Returned

Creating Move Order Using INV_MOVE_ORDER_PUB

Move Order:

Move orders are requests for the movement of material within a single organization. They allow planners and facility managers to request the movement of material within a warehouse or facility for purposes like replenishment, material storage relocations, and quality handling. We can generate move orders either manually or automatically depending on the source type we use. Move orders are restricted to transactions within an organization.

Move Order source types

There are three Move Order source types:

A. Move Order Requisition
Generated by:

* Manually generated request inside Inventory

The requisition is a manually generated request for a Move Order. May optionally utilize the approval processing - the requisition can optionally go through an Oracle Workflow approval process before it becomes a Move Order. If no approval process is used, the requisition becomes a move order immediately.

B. Replenishment Move Order

Generated by:

* Kanban Cards when pull sequence calls for subinventory transfer (intra-org Kanbans)

* Min Max Planning Report planned at the Subinventory Level

* Replenishment Counts

Replenishment Move Orders are pre-approved and ready to be transacted.

C. Wave Pick Move Order

Generated by:

* Sales Order picking

* Internal Sales Order picking

The Order Management pick release process generates move orders to bring material from its source location in Inventory to a staging location, which is defined as a subinventory. In other words, a subinventory transfer. Pick Wave Move Orders are pre-approved and ready to transact. Pick slips can also be generated for these Move Orders from Inventory with the release of the Oracle Order Management module.

Tables used to store the details about move order process

1) MTL_TXN_REQUEST_HEADERS: Move order headers, this stores the move order number in column (REQUEST_NUMBER). It has a status, but this is not used as much as the lines status to drive functionality.
2) MTL_TXN_REQUEST_LINES: Move order lines, this is the one that drives most queries and status checks for the move order as each line can be transacted individually.
3) MTL_MATERIAL_TRANSACTIONS_TEMP: Pending material transactions table also called the transaction temporary table, this holds allocations that act like reservations on inventory. An allocation is where you pick a specific item in inventory down to the lot, locator, serial, revision to move, but you do not actually perform the move yet.
4) MTL_MATERIAL_TRANSACTIONS : Finally the Material Transactions will get into this table.

R12 - Sample Script to Create Move Order Using INV_MOVE_ORDER_PUB.

DECLARE

x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_hdr_rec inv_move_order_pub.trohdr_rec_type:= inv_move_order_pub.g_miss_trohdr_rec;
x_hdr_rec inv_move_order_pub.trohdr_rec_type:= inv_move_order_pub.g_miss_trohdr_rec;
l_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl;
x_line_tbl inv_move_order_pub.trolin_tbl_type:= inv_move_order_pub.g_miss_trolin_tbl;
x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type;
v_msg_index_out NUMBER;
l_rsr_type inv_reservation_global.mtl_reservation_tbl_type;
l_code_combination_id gl_code_combinations.code_combination_id%type;

CURSOR c_itm_onhand IS

SELECT a.organization_id, c.operating_unit org_id,
a.inventory_item_id,b.concatenated_segments,
a.subinventory_code,a.lot_number,
SUM (a.primary_transaction_quantity) total_onhand
FROM mtl_onhand_quantities_detail a,
mtl_system_items_kfv b,
org_organization_definitions c,
mtl_lot_numbers d
WHERE a.inventory_item_id = b.inventory_item_id
AND a.lot_number = d.lot_number
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = b.organization_id
AND a.organization_id = c.organization_id
AND a.organization_id = d.organization_id
AND b.concatenated_segments = 'RC0805FR-0768KL^YAGEO'
AND a.lot_number = 'A6680983'
AND c.organization_code = 'A66'
GROUP BY a.organization_id,c.operating_unit,
a.inventory_item_id, b.concatenated_segments,
a.subinventory_code, a.lot_number;

BEGIN

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 = 'Inventory';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_itm_onhand LOOP

mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');

select code_combination_id
into l_code_combination_id
from gl_code_combinations_kfv
where concatenated_segments = '6420-00000-0000-999999-91000-0000-00000-0-0';

l_line_tbl.DELETE;
x_line_tbl.DELETE;
l_hdr_rec.date_required := SYSDATE;
l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
l_hdr_rec.organization_id := i.organization_id;
l_hdr_rec.status_date := SYSDATE;
l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
l_hdr_rec.db_flag := fnd_api.g_true;
l_hdr_rec.operation := inv_globals.g_opr_create;
l_hdr_rec.description := 'Test Move Order One';
l_hdr_rec.to_account_id := l_code_combination_id;
l_hdr_rec.from_subinventory_code := i.subinventory_code;
l_line_tbl (1).date_required := SYSDATE;
l_line_tbl (1).inventory_item_id := i.inventory_item_id;
l_line_tbl (1).line_id := fnd_api.g_miss_num;
l_line_tbl (1).line_number := 1;
l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
l_line_tbl (1).transaction_type_id := inv_globals.g_type_transfer_order_issue;
l_line_tbl (1).organization_id := i.organization_id;
l_line_tbl (1).quantity := 5000;
l_line_tbl (1).status_date := SYSDATE;
l_line_tbl (1).uom_code := 'EA';
l_line_tbl (1).db_flag := fnd_api.g_true;
l_line_tbl (1).operation := inv_globals.g_opr_create;
l_line_tbl (1).from_subinventory_code := i.subinventory_code;
l_line_tbl (1).to_account_id := l_code_combination_id;
l_line_tbl (1).lot_number := i.lot_number;

DBMS_OUTPUT.put_line ('Calling INV_MOVE_ORDER_PUB to Create MO');
DBMS_OUTPUT.put_line ('===================================');

INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trohdr_rec => l_hdr_rec,
p_trolin_tbl => l_line_tbl,
x_trohdr_rec => x_hdr_rec,
x_trohdr_val_rec => x_hdr_val_rec,
x_trolin_tbl => x_line_tbl,
x_trolin_val_tbl => x_line_val_tbl);

DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);

IF x_return_status = 'S'
THEN
COMMIT;

DBMS_OUTPUT.put_line ('Move Order Successfully Created');
DBMS_OUTPUT.put_line ('Move Order Number is :=> ');
DBMS_OUTPUT.put_line (x_hdr_rec.request_number);

ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ('Move Order Creation Failed Due to Following Reasons');
END IF;

IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);

END LOOP;
END IF;
END LOOP;

END;

Sample DBMS Output: -

Calling INV_MOVE_ORDER_PUB to Create MO
===================================
S
0
Move Order Successfully Created
Move Order Number is :=> 332557

Performing Back Order on a SO Line using Wsh_Delivery_Pub API

-- R12 - OM - Script to Back Order SO Line using Wsh_Delivery_Pub API --
--===================================================== --

DECLARE

x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
--Parameters for WSH_DELIVERIES_PUB
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_ship_method_code VARCHAR2 (100);

CURSOR c_ord_details
IS

SELECT DISTINCT det.source_header_number sales_order, det.org_id,
det.source_line_number, det.source_header_id,
det.source_line_id, det.source_header_type_name,
det.inventory_item_id, det.requested_quantity,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = det.inventory_item_id
AND organization_id = det.organization_id) ordered_item,
det.organization_id, det.src_requested_quantity,
det.shipped_quantity, del.delivery_id,
del.status_code delivery_status_code,
det.released_status pick_release_status,
det.oe_interfaced_flag, det.inv_interfaced_flag
FROM wsh_delivery_details det,
wsh_delivery_assignments asn,
wsh_new_deliveries del
WHERE 1 = 1
AND det.delivery_detail_id = asn.delivery_detail_id
AND asn.delivery_id = del.delivery_id(+)
AND det.source_header_number = '10001059'
AND det.org_id = 308
AND shipped_quantity IS NULL
AND NVL (del.status_code, 'OP') <> 'CL'
AND det.released_status = 'Y';

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

FOR i IN c_ord_details

LOOP

-- Mandatory initialization for R12

mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('ONT');

BEGIN
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE order_number = i.sales_order AND org_id = i.org_id;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

/* Initialize return status*/

x_return_status := wsh_util_core.g_ret_sts_success;

p_action_code := 'CONFIRM'; -- The action code
p_delivery_id := i.delivery_id;-- The delivery that needs to be confirmed
p_delivery_name := TO_CHAR (i.delivery_id); -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity
p_sc_stage_del_flag := 'N'; --
p_sc_trip_ship_method := l_ship_method_code; -- The ship method code

DBMS_OUTPUT.put_line ('Calling WSH_DELIVERIES_PUB to Perform Back Order');
DBMS_OUTPUT.put_line ('=============================================');

wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => i.delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_stop_seq => p_asg_pickup_stop_seq,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_stop_seq => p_asg_dropoff_stop_seq,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => 'N',
p_sc_close_trip_flag => 'N',
p_sc_create_bol_flag => 'N',
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => 'Y',
p_sc_send_945_flag => p_sc_send_945_flag,
p_sc_rule_id => p_sc_rule_id,
p_sc_rule_name => p_sc_rule_name,
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 ('Back Order has not been Completed For SO');

ROLLBACK;
RAISE fail_api;

ELSE
DBMS_OUTPUT.put_line ('Back Order Successfully Completed For SO');
COMMIT;

END IF;
END LOOP;

EXCEPTION

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

Performing Shipping Transaction Using WSH_DELIVERY_PUB API

Ship Confirm Process
================

What happened during Ship Confirm:
******************************************
The results of the picking process are recorded against a Delivery.

• Ship Confirm can only be performed on Deliveries with Delivery Lines that have been Pick Confirmed.
• Ship confirming a delivery records the results of the picking process. These results could be shipped, backordered, staged or cycle count or a combination of all 4.
• The workflow activity will be completed when the quantity picked is recorded as “Shipped”.
• If the ordered item is set up with the item attribute “Shippable” checked, Ship Confirming is a prerequisite for Invoicing.

Shipped Quantities:
************************
• Once the delivery is closed the Order Line is updated with the shipped quantities and the status of the line is changed to “Shipped”. This enables the order line to proceed to its next workflow activity.
• The Ship Confirm transaction initiates the Inventory Interface to generate the “Issue of Stores” transactions which will decrement inventory and remove the material reservation. Then the OM interfaces is initiated to update the Sales Order Line with Shipped quantities, freight charges, etc.
• The Cost of goods sold account number that is passed to inventory is workflow generated. In Inventory it creates a Material Distribution record that is ultimately passed to the General Ledger.
• If Ship confirm is partial the remaining quantity can be either staged or backordered.

Backordered Quantities:
****************************
• Backordered quantities are left in the Staging Subinventory. They are not automatically returned to their source location.
• A new pick release will be required before they can be ship confirmed.
• The backordered quantity is removed from the delivery being Ship Confirmed.and the reservation is removed making the quantity available to ATP.
• The Sales Order line splits into shipment schedules. One schedule will have the quantity that was shipped and a status of “Shipped”. The other schedule will have the quantity that was backordered and a status of “Awaiting Shipment”.

Staged Quantities:
**********************
• Staged quantities are left in the Staging subinventory and can be Ship Confirmed at a later time. The staged quantity is removed from the delivery being confirmed and optionally linked to a new delivery number.
• The Sales Order line splits into shipment schedules. One schedule will have the quantity that was shipped and a status of “Shipped”. The other schedule will have the quantity that remained Staged with a status of “Picked”.

Different Delivery Line Statuses:
*************************************
select * from wsh_lookups
where lookup_type = 'PICK_STATUS'

- Not Applicable (Code X)
The delivery line is invoiceable but non-shippable, for example, a service line or a warranty line.

- Not Ready for Release (Code N)
The delivery line is not eligible for pick release. Occurs when the order line is manually imported into Oracle Shipping Execution using the Import Delivery Line concurrent process. It has not reached the Awaiting Shipping workflow activity.

- Ready for Release (Code R)
The delivery line is eligible for pick release. Occurs when the order line has reached the Awaiting Shipping workflow activity (it is booked, scheduled, and in Oracle Shipping Execution).

- Submitted to Warehouse (Code S)
Pick release has processed the delivery line and has: Created move order headers and lines. Found available quantity and created inventory allocations. Not pick confirmed. If you are using auto-pick confirm, it changes release status to Staged. If you are not using auto-pick confirm and want to progress the delivery lines, navigate to Oracle Inventory Move Order Transaction window and perform manual pick confirm.

- Staged (Code Y)
The delivery line is pick confirmed; inventory is transferred from storage subinventory to staging subinventory. It remains staged until ship confirm.

- Backordered (Code B)
Any of the following circumstances occurs: Pick release has processed the delivery line and cannot find the entire quantity. This typically occurs when the Oracle Inventory inventory balance indicates that there is not enough material (either because there is not enough material or because the inventory balance is incorrect).
At ship confirm, you: Enter Shipped Quantity that is less than Original Requested Quantity Backorder the entire delivery quantity Transfer a reservation to cycle count. This typically occurs when the material that you want to ship: Has become unavailable, for example, damaged, between picking and shipping. Is available and you backorder material for specific business reasons. For example, all available material has been allocated to a specific customer when you find out additional supply for other orders will be delayed. For information on the backorder processing in pick release and ship confirm,

- Shipped (Code C)
The delivery line’s delivery is ship confirmed and posted as intransit, OM Interface and Inventory Interface have processed, and the trip is closed.

- Cancelled (Code D)
The order line that the delivery line supports is cancelled.
=================================
:WSH_DELIVERIES_PUB.Delivery_Action:
=================================
We can call “WSH_DELIVERIES_PUB.Delivery_Action” API in order to ship confirm the delivery Programatically.

Specific Parameters:

p_action_code(Required) => action to be performed on Delivery
p_delivery_id (Required) => delivery id on which the action is performed
p_trip_name => Trip identifier for assignment of trip to delivery
p_asg_pickup_loc_code => Stop location code for pickup assignment
p_asg_pickup_dep_date => Stop location departure date for pickup assignment
p_asg_dropoff_loc_code => Stop location code for dropoff assignment
p_asg_dropoff_dep_date => Stop location departure date for dropoff assignment
p_sc_action_flag => Ship Confirm option - S, B, T, A, C. Used
p_sc_intransit_flag => Ship Confirm set in-transit flag.
p_sc_close_trip_flag => Ship Confirm close trip flag.
p_sc_create_bol_flag => Ship Confirm create Bill of Lading flag
p_sc_stage_del_flag => Ship Confirm create delivery for stage quantity flag
p_sc_trip_ship_method => Ship Confirm trip ship method.
p_wv_override_flag => Override flag for weight volume calculations.
x_trip_name => Name of autocreated trip.

What Are the P_SC_ACTION_FLAG Possible Values S, B, T, A, C?
These are the possible values and what they mean:
'S' - Ship Entered Quantities, Ship Unspecified
'B' - Ship Entered Quantities, Backorder Unspecified
'T' - Ship Entered Quantities, Stage Unspecified
'A' - Ship All
'C' - Completely Backordered

-- R12 - OM - Sample Script to Ship SO using WSH_DELIVERY_PUB API --========================================================


DECLARE

p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
x_return_status VARCHAR2 (3);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
p_validation_level NUMBER;
v_errbuf VARCHAR2 (2000);
v_retcode VARCHAR2 (20);
v_released_status wsh_delivery_details.released_status%TYPE;
v_inv_interfaced_flag wsh_delivery_details.inv_interfaced_flag%TYPE;
v_oe_interfaced_flag wsh_delivery_details.oe_interfaced_flag%TYPE;
v_source_code wsh_delivery_details.source_code%TYPE;
v_pending_interface_flag wsh_trip_stops.pending_interface_flag%TYPE;
-- Parameters for WSH_DELIVERIES_PUB
p_delivery_name VARCHAR2 (30);
p_action_code VARCHAR2 (15);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_intransit_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_sc_defer_interface_flag VARCHAR2 (60);
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
fail_api EXCEPTION;
x_debug_file VARCHAR2 (100);
l_ship_method_code VARCHAR2 (100);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_ord_details IS

SELECT DISTINCT det.source_header_number sales_order, det.org_id,
det.source_line_number, det.source_header_id,
det.source_line_id, det.source_header_type_name,
det.inventory_item_id,det.requested_quantity,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = det.inventory_item_id
AND organization_id = det.organization_id) ordered_item,
det.organization_id, det.src_requested_quantity,
det.shipped_quantity, del.delivery_id,
del.status_code delivery_status_code,
det.released_status pick_release_status,
det.oe_interfaced_flag, det.inv_interfaced_flag
FROM wsh_delivery_details det,
wsh_delivery_assignments asn,
wsh_new_deliveries del
WHERE 1 = 1
AND det.delivery_detail_id = asn.delivery_detail_id
AND asn.delivery_id = del.delivery_id(+)
AND det.source_header_number = '10013618'
AND det.org_id = 308
AND shipped_quantity IS NULL
AND NVL (del.status_code, 'OP') <> 'CL'
AND det.released_status = 'Y';

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

FOR i IN c_ord_details

LOOP

-- Mandatory initialization for R12
mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('ONT');

-- Ship Confirming

p_delivery_name := TO_CHAR (i.delivery_id);
DBMS_OUTPUT.put_line (p_delivery_name);

BEGIN
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE order_number = i.sales_order
AND org_id = i.org_id;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_intransit_flag := 'Y';
--In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := l_ship_method_code; -- The ship method code
p_sc_defer_interface_flag := 'Y';
p_sc_stage_del_flag := 'Y';
p_sc_create_bol_flag := 'N';
p_wv_override_flag := 'N';

-- API Call for Ship Confirmation

DBMS_OUTPUT.put_line
('Calling WSH_DELIVERIES_PUB to Perform Ship Confirmation');
DBMS_OUTPUT.put_line ('=============================================');

wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => i.delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_stop_seq => p_asg_pickup_stop_seq,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_stop_seq => p_asg_dropoff_stop_seq,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => p_sc_intransit_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => p_sc_defer_interface_flag,
p_sc_send_945_flag => p_sc_send_945_flag,
p_sc_rule_id => p_sc_rule_id,
p_sc_rule_name => p_sc_rule_name,
p_wv_override_flag => p_wv_override_flag,
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('Ship confirm has not been Completed For SO');
ROLLBACK;
RAISE fail_api;

ELSE
DBMS_OUTPUT.put_line ('Ship confirm Successfully Completed For SO');
COMMIT;

DBMS_OUTPUT.put_line
('Checking the Delivery Status after delivery action API Call');
DBMS_OUTPUT.put_line ('==========================================');

SELECT wdd.source_code, wdd.released_status,
wdd.inv_interfaced_flag, wdd.oe_interfaced_flag,
wts.pending_interface_flag
INTO v_source_code, v_released_status,
v_inv_interfaced_flag, v_oe_interfaced_flag,
v_pending_interface_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = 'Y'
AND wdd.inv_interfaced_flag <> 'Y'
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = p_delivery_name
AND wdd.source_line_id = i.source_line_id;

IF ( v_source_code = 'OE'
AND v_released_status = 'C'
AND v_inv_interfaced_flag <> 'Y'
AND v_oe_interfaced_flag <> 'Y'
AND v_pending_interface_flag = 'Y'
)

THEN

DBMS_OUTPUT.put_line
('The Delivery has been Shipped & the Next Step is - Run Interface');
DBMS_OUTPUT.put_line('Calling Interface Trip Stop');
DBMS_OUTPUT.put_line ('======================================');

-- API Call for Submitting Interface Trip Stop

wsh_ship_confirm_actions.interface_all_wrp
(errbuf => v_errbuf,
retcode => v_retcode,
p_mode => 'ALL',
p_stop_id => NULL,
p_delivery_id => p_delivery_name,
p_log_level => 0,
p_batch_id => NULL,
p_trip_type => NULL,
p_organization_id => i.organization_id,
p_num_requests => 1,
p_stops_per_batch => 1
);

DBMS_OUTPUT.put_line (v_retcode);
DBMS_OUTPUT.put_line (v_errbuf);

ELSE

DBMS_OUTPUT.put_line ('The Delivery has not Shipped Properly');
END IF;
END IF;

END LOOP;

EXCEPTION
WHEN fail_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;