Welcome to Oracle ERP: Shipping

Showing posts with label Shipping. Show all posts

Wednesday, March 26, 2014

Script to Update Shipping Attributes in wsh_delivery_details

DECLARE
CURSOR dev_header_cur
IS
SELECT *
FROM apps.wsh_delivery_details
WHERE delivery_detail_id IN
(970938993, 970938998, 970938997); -- Provide delivery_detail_id

l_index NUMBER;
l_msg_return NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_changedattributetabtype wsh_delivery_details_pub.changedattributetabtype;
l_file_name VARCHAR2 (32767);
l_return_status VARCHAR2 (32767);
l_msg_data VARCHAR2 (32767);
l_msg_count NUMBER;
BEGIN
fnd_global.APPS_INITIALIZE (2861, 67384, 660); -- Provide user_id, resp_id and appl_id to initialize

fnd_profile.put ('WSH_DEBUG_MODULE', '%');
fnd_profile.put ('WSH_DEBUG_LEVEL', WSH_DEBUG_SV.C_STMT_LEVEL);
DBMS_OUTPUT.PUT_LINE ('Start');
wsh_debug_sv.start_debugger (l_file_name,
l_return_status,
l_msg_data,
l_msg_count);
l_index := 0;

FOR dev_header_rec IN dev_header_cur
LOOP
l_index := L_index + 1;

l_changedattributetabtype (l_index).delivery_detail_id :=
Dev_header_rec.delivery_detail_id;
l_changedattributetabtype (l_index).subinventory := 'ICENI'; -- Provide subinventory to update
END LOOP;

wsh_delivery_details_pub.Update_Shipping_Attributes (
p_api_version_number => 1.0,
p_init_msg_list => 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_changed_attributes => l_changedattributetabtype,
p_source_code => 'OE');

COMMIT;

IF x_return_status <> fnd_api.G_RET_STS_SUCCESS
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => I,
P_encoded => 'F',
P_data => X_msg_data,
P_msg_index_out => l_msg_return);
DBMS_OUTPUT.PUT_LINE (x_msg_data);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE ('S');
END IF;
END;

Thursday, November 7, 2013

Ship-Confirm API

CREATE OR REPLACE PROCEDURE ship_confirm (p_delivery_id IN NUMBER,
p_shipment_date IN DATE,
x_return_status OUT VARCHAR2,
x_error_message OUT VARCHAR2)
IS
v_api_version_number NUMBER := 1;
v_init_msg_list VARCHAR2 (300);
v_commit VARCHAR2 (300);
v_msg_data VARCHAR2 (10000);
v_msg_summary VARCHAR2 (32766);
v_msg_details VARCHAR2 (32766);
v_errbuf VARCHAR2 (1000);
v_retcode VARCHAR2 (1000);
v_mode VARCHAR2 (100);
v_action_code VARCHAR2 (15);
v_delivery_name VARCHAR2 (30);
v_detail_id VARCHAR2 (20);
v_shipped_quantity NUMBER (10);
v_ordered_quantity NUMBER (10);
v_counter_wsh_del_leg NUMBER (10);
v_temp_ship_quant NUMBER (10);
v_update_flg VARCHAR2 (1);
v_header_id NUMBER;
v_line_id NUMBER;
v_interface_all_result NUMBER;
v_ship_confirm_api_result VARCHAR2 (100);
v_source_code VARCHAR2 (150);
v_delivery_detail_id NUMBER;
v_lot_number VARCHAR2 (32);
v_subinventory VARCHAR2 (10);
v_delivery_id NUMBER;
v_msg_count NUMBER;
v_shipped_date DATE;
-------------------------------------------------------------------------------
/* OUT Parameters */
-------------------------------------------------------------------------------
v_trip_id VARCHAR2 (30);
v_trip_name VARCHAR2 (30);
v_rowid VARCHAR2 (30);
BEGIN
v_delivery_id := p_delivery_id;
v_action_code := 'CONFIRM';
-- The action code for creating trip
v_shipped_date := p_shipment_date; -- sysdate;

-------------------------------------------------------------------------------
-- Call to WSH_DELIVERIES_PUB.Delivery_Action.
-------------------------------------------------------------------------------
DBMS_OUTPUT.put_line ('In ship_confirm Delivery ID:' || v_delivery_id);
wsh_deliveries_pub.delivery_action (
p_api_version_number => 1.0,
p_init_msg_list => v_init_msg_list,
x_return_status => x_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_action_code => v_action_code,
p_delivery_id => v_delivery_id,
p_delivery_name => NULL,
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_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_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => 'S',
p_sc_intransit_flag => 'Y',
p_sc_close_trip_flag => 'Y',
p_sc_create_bol_flag => 'N',
p_sc_stage_del_flag => 'Y',
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => v_shipped_date,
--Provide Actual Ship Date
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => 'N',
p_wv_override_flag => NULL,
x_trip_id => v_trip_id,
x_trip_name => v_trip_name);
DBMS_OUTPUT.put_line (
'delivery_action SHIP-CONFIRM x_return_status : ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
wsh_util_core.get_messages ('Y',
v_msg_summary,
v_msg_details,
v_msg_count);

IF v_msg_count > 1
THEN
v_msg_data := v_msg_summary || v_msg_details;
ELSE
v_msg_data := v_msg_summary;
END IF;

x_error_message := v_msg_data;
x_return_status := 'E';
DBMS_OUTPUT.put_line (
'Error message from wsh_deliveries_pub.delivery_action v_msg_data:'
|| v_msg_data);

IF v_msg_data LIKE
'%Delivery ' || TRIM (v_delivery_id) || ' has null Weight%'
AND INSTR (UPPER (v_msg_data),
'ERROR',
1,
2) = 0
THEN
x_return_status := 'S';
x_error_message := NULL;
ELSE
ROLLBACK;
END IF;
ELSE
x_return_status := 'S';
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_error_message := SQLCODE || ':' || SUBSTR (SQLERRM, 1, 200);
DBMS_OUTPUT.put_line ('Error :' || x_error_message);
END;

Auto-create Deliveries API

CREATE OR REPLACE PROCEDURE create_deliveries (
p_delivery_detail_ids IN wsh_util_core.id_tab_type,
x_delivery_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_error_message OUT VARCHAR2)
IS
v_api_version_number NUMBER := 1;
v_init_msg_list VARCHAR2 (30);
v_commit VARCHAR2 (30);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_msg_summary VARCHAR2 (3000);
v_msg_details VARCHAR2 (3000);
v_line_rows wsh_util_core.id_tab_type;
v_del_rows wsh_util_core.id_tab_type;
BEGIN
--Delivery_detail_ids
DBMS_OUTPUT.put_line ('in create_deliveries--------');
v_line_rows := p_delivery_detail_ids;
--Autocreate Deliveries
wsh_delivery_details_pub.autocreate_deliveries (
p_api_version_number => v_api_version_number,
p_init_msg_list => v_init_msg_list,
p_commit => v_commit,
x_return_status => x_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_line_rows => v_line_rows,
x_del_rows => v_del_rows);
DBMS_OUTPUT.put_line (
'Auto create deliveries x_return_status : ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
wsh_util_core.get_messages ('Y',
v_msg_summary,
v_msg_details,
v_msg_count);

IF v_msg_count > 1
THEN
v_msg_data := v_msg_summary || v_msg_details;
ELSE
v_msg_data := v_msg_summary;
END IF;

x_error_message := v_msg_data;
x_return_status := 'E';
DBMS_OUTPUT.put_line (
'After call to wsh_delivery_details_pub.autocreate_deliveries: Error message v_msg_data '
|| v_msg_data);
ELSE
DBMS_OUTPUT.put_line ('Delivery_id=' || v_del_rows (1));
x_delivery_id := v_del_rows (1);
x_return_status := 'S';
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_error_message := SQLCODE || ':' || SQLERRM;
DBMS_OUTPUT.put_line ('Error :' || x_error_message);
RETURN;
END;

Transact Move Order API

CREATE OR REPLACE PROCEDURE transact_moveorder (p_delivery_id IN NUMBER)
IS
CURSOR c_deli_details
IS
SELECT wdd.source_header_id,
wdd.source_line_id,
wdd.inventory_item_id,
wdd.organization_id,
wdd.org_id,
ooh.order_number
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
oe_order_headers_all ooh
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND ooh.header_id = wdd.source_header_id
AND delivery_id = p_delivery_id;

c_del_det c_deli_details%ROWTYPE;
l_serial_number_control_code NUMBER := NULL;
l_request_number VARCHAR2 (30);
rt_mtl_txn_req_lin mtl_txn_request_lines%ROWTYPE;
g_line_tbl inv_move_order_pub.trolin_tbl_type;
l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (1000);
x_msg_count NUMBER;
l_header_id NUMBER;
l_move_order_type NUMBER;
x_number_of_rows NUMBER;
x_qty_detailed NUMBER;
l_revision NUMBER;
x_locator_id NUMBER;
x_transfer_to_location NUMBER;
x_lot_number VARCHAR2 (80);
x_expiration_date DATE;
x_transaction_temp_id NUMBER;
l_transaction_mode NUMBER;
xm_return_status VARCHAR2 (1);
xm_msg_count NUMBER;
xm_msg_data VARCHAR2 (1000);
l_error_message VARCHAR2 (3000);
l_msg_index_out NUMBER;
x_transaction_header_id NUMBER;
l_ser_number VARCHAR2 (50);
x NUMBER;
BEGIN
--fnd_global.APPS_Initialize(109543,68214,660);
OPEN c_deli_details;

LOOP
FETCH c_deli_details INTO c_del_det;

EXIT WHEN c_deli_details%NOTFOUND;

BEGIN
SELECT serial_number_control_code
INTO l_serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = c_del_det.inventory_item_id
AND ORGANIZATION_ID = c_del_det.organization_id;

DBMS_OUTPUT.put_line (
'l_serial_number_control_code :' || l_serial_number_control_code);

IF l_serial_number_control_code = 5
THEN
SELECT mtrh.request_number
INTO l_request_number
FROM mtl_txn_request_headers mtrh
WHERE mtrh.request_number = mtrh.request_number
AND mtrh.header_id IN
(SELECT mtrl.header_id
FROM mtl_txn_request_lines mtrl,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.move_order_line_id = mtrl.line_id
AND wdd.delivery_detail_id =
wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.organization_id =
c_del_det.organization_id);

DBMS_OUTPUT.put_line ('l_request_number :' || l_request_number);
fnd_file.put_line (fnd_file.LOG,
'l_request_number :' || l_request_number);

BEGIN
SELECT mtrl.*
INTO rt_mtl_txn_req_lin
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrh.request_number = l_request_number
AND mtrh.header_id = mtrl.header_id
AND mtrl.line_status IN (3, 7, 9)
--(3-approved,7-Pre approved, 9-cancelled by source)
AND mtrl.organization_id = c_del_det.organization_id
AND txn_source_line_id = c_del_det.source_line_id;

DBMS_OUTPUT.put_line (
'mtrl.header_id :' || rt_mtl_txn_req_lin.header_id);
fnd_file.put_line (
fnd_file.LOG,
'mtrl.header_id :' || rt_mtl_txn_req_lin.header_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Move order line not in 3,7,9');
fnd_file.put_line (fnd_file.LOG,
'Move order line not in 3,7,9');
fnd_file.put_line (fnd_file.LOG,
'Move order line not in 3,7,9 ');
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error while fetching the move order details, move order number: '
|| l_request_number
|| SQLCODE
|| ' '
|| SQLERRM);
END;

g_line_tbl.DELETE;
--l_counter := l_counter + 1;
-- fnd_file.put_line (fnd_file.LOG,'l_counter= '||l_counter);
g_line_tbl (1).attribute1 := rt_mtl_txn_req_lin.attribute1;
g_line_tbl (1).attribute10 := rt_mtl_txn_req_lin.attribute10;
g_line_tbl (1).attribute11 := rt_mtl_txn_req_lin.attribute11;
g_line_tbl (1).attribute12 := rt_mtl_txn_req_lin.attribute12;
g_line_tbl (1).attribute13 := rt_mtl_txn_req_lin.attribute13;
g_line_tbl (1).attribute14 := rt_mtl_txn_req_lin.attribute14;
g_line_tbl (1).attribute15 := rt_mtl_txn_req_lin.attribute15;
g_line_tbl (1).attribute2 := rt_mtl_txn_req_lin.attribute2;
g_line_tbl (1).attribute3 := rt_mtl_txn_req_lin.attribute3;
g_line_tbl (1).attribute4 := rt_mtl_txn_req_lin.attribute4;
g_line_tbl (1).attribute5 := rt_mtl_txn_req_lin.attribute5;
g_line_tbl (1).attribute6 := rt_mtl_txn_req_lin.attribute6;
g_line_tbl (1).attribute7 := rt_mtl_txn_req_lin.attribute7;
g_line_tbl (1).attribute8 := rt_mtl_txn_req_lin.attribute8;
g_line_tbl (1).attribute9 := rt_mtl_txn_req_lin.attribute9;
g_line_tbl (1).attribute_category :=
rt_mtl_txn_req_lin.attribute_category;
g_line_tbl (1).created_by := rt_mtl_txn_req_lin.created_by;
g_line_tbl (1).creation_date := rt_mtl_txn_req_lin.creation_date;
g_line_tbl (1).date_required := rt_mtl_txn_req_lin.date_required;
g_line_tbl (1).from_locator_id :=
rt_mtl_txn_req_lin.from_locator_id;
g_line_tbl (1).from_subinventory_code :=
rt_mtl_txn_req_lin.from_subinventory_code;
g_line_tbl (1).from_subinventory_id :=
rt_mtl_txn_req_lin.from_subinventory_id;
g_line_tbl (1).header_id := rt_mtl_txn_req_lin.header_id;
g_line_tbl (1).inventory_item_id :=
rt_mtl_txn_req_lin.inventory_item_id;
g_line_tbl (1).last_updated_by :=
rt_mtl_txn_req_lin.last_updated_by;
g_line_tbl (1).last_update_date :=
rt_mtl_txn_req_lin.last_update_date;
g_line_tbl (1).last_update_login :=
rt_mtl_txn_req_lin.last_update_login;
g_line_tbl (1).line_id := rt_mtl_txn_req_lin.line_id;
g_line_tbl (1).line_number := rt_mtl_txn_req_lin.line_number;
g_line_tbl (1).line_status := rt_mtl_txn_req_lin.line_status;
g_line_tbl (1).lot_number := rt_mtl_txn_req_lin.lot_number;
g_line_tbl (1).organization_id :=
rt_mtl_txn_req_lin.organization_id;
g_line_tbl (1).program_application_id :=
rt_mtl_txn_req_lin.program_application_id;
g_line_tbl (1).program_id := rt_mtl_txn_req_lin.program_id;
g_line_tbl (1).program_update_date :=
rt_mtl_txn_req_lin.program_update_date;
g_line_tbl (1).project_id := rt_mtl_txn_req_lin.project_id;
g_line_tbl (1).quantity := rt_mtl_txn_req_lin.quantity;
g_line_tbl (1).quantity_delivered :=
rt_mtl_txn_req_lin.quantity_delivered;
g_line_tbl (1).quantity_detailed :=
rt_mtl_txn_req_lin.quantity_detailed;
g_line_tbl (1).reason_id := rt_mtl_txn_req_lin.reason_id;
g_line_tbl (1).REFERENCE := rt_mtl_txn_req_lin.REFERENCE;
g_line_tbl (1).reference_id := rt_mtl_txn_req_lin.reference_id;
g_line_tbl (1).reference_type_code :=
rt_mtl_txn_req_lin.reference_type_code;
g_line_tbl (1).request_id := rt_mtl_txn_req_lin.request_id;
g_line_tbl (1).revision := rt_mtl_txn_req_lin.revision;
g_line_tbl (1).serial_number_end :=
rt_mtl_txn_req_lin.serial_number_end;
g_line_tbl (1).serial_number_start :=
rt_mtl_txn_req_lin.serial_number_start;
g_line_tbl (1).status_date := rt_mtl_txn_req_lin.status_date;
g_line_tbl (1).task_id := rt_mtl_txn_req_lin.task_id;
g_line_tbl (1).to_account_id := rt_mtl_txn_req_lin.to_account_id;
g_line_tbl (1).to_locator_id := rt_mtl_txn_req_lin.to_locator_id;
g_line_tbl (1).to_subinventory_code :=
rt_mtl_txn_req_lin.to_subinventory_code;
g_line_tbl (1).to_subinventory_id :=
rt_mtl_txn_req_lin.to_subinventory_id;
g_line_tbl (1).transaction_header_id :=
rt_mtl_txn_req_lin.transaction_header_id;
g_line_tbl (1).transaction_type_id :=
rt_mtl_txn_req_lin.transaction_type_id;
g_line_tbl (1).txn_source_id := rt_mtl_txn_req_lin.txn_source_id;
g_line_tbl (1).txn_source_line_id :=
rt_mtl_txn_req_lin.txn_source_line_id;
g_line_tbl (1).txn_source_line_detail_id :=
rt_mtl_txn_req_lin.txn_source_line_detail_id;
g_line_tbl (1).transaction_source_type_id :=
rt_mtl_txn_req_lin.transaction_source_type_id;
g_line_tbl (1).primary_quantity :=
rt_mtl_txn_req_lin.primary_quantity;
g_line_tbl (1).to_organization_id :=
rt_mtl_txn_req_lin.to_organization_id;
g_line_tbl (1).pick_strategy_id :=
rt_mtl_txn_req_lin.pick_strategy_id;
g_line_tbl (1).put_away_strategy_id :=
rt_mtl_txn_req_lin.put_away_strategy_id;
g_line_tbl (1).uom_code := rt_mtl_txn_req_lin.uom_code;
g_line_tbl (1).unit_number := rt_mtl_txn_req_lin.unit_number;
g_line_tbl (1).ship_to_location_id :=
rt_mtl_txn_req_lin.ship_to_location_id;
g_line_tbl (1).from_cost_group_id :=
rt_mtl_txn_req_lin.from_cost_group_id;
g_line_tbl (1).to_cost_group_id :=
rt_mtl_txn_req_lin.to_cost_group_id;
g_line_tbl (1).lpn_id := rt_mtl_txn_req_lin.lpn_id;
g_line_tbl (1).to_lpn_id := rt_mtl_txn_req_lin.to_lpn_id;
g_line_tbl (1).pick_methodology_id :=
rt_mtl_txn_req_lin.pick_methodology_id;
g_line_tbl (1).container_item_id :=
rt_mtl_txn_req_lin.container_item_id;
g_line_tbl (1).carton_grouping_id :=
rt_mtl_txn_req_lin.carton_grouping_id;
--g_line_tbl(1).return_status := rt_mtl_txn_req_lin.return_status;
g_line_tbl (1).db_flag := fnd_api.g_true;
g_line_tbl (1).operation := inv_globals.g_opr_create;
g_line_tbl (1).inspection_status :=
rt_mtl_txn_req_lin.inspection_status;
g_line_tbl (1).wms_process_flag :=
rt_mtl_txn_req_lin.wms_process_flag;
g_line_tbl (1).pick_slip_number :=
rt_mtl_txn_req_lin.pick_slip_number;
g_line_tbl (1).pick_slip_date := rt_mtl_txn_req_lin.pick_slip_date;
g_line_tbl (1).ship_set_id := rt_mtl_txn_req_lin.ship_set_id;
g_line_tbl (1).ship_model_id := rt_mtl_txn_req_lin.ship_model_id;
g_line_tbl (1).model_quantity := rt_mtl_txn_req_lin.model_quantity;
g_line_tbl (1).required_quantity :=
rt_mtl_txn_req_lin.required_quantity;

SELECT mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM DUAL;

SELECT move_order_type
INTO l_move_order_type
FROM mtl_txn_request_headers
WHERE header_id = g_line_tbl (1).header_id;

fnd_file.put_line (
fnd_file.LOG,
'material transaction sequence number :' || l_header_id);

IF (rt_mtl_txn_req_lin.quantity_detailed IS NULL)
OR (rt_mtl_txn_req_lin.quantity_detailed = 0)
THEN
inv_replenish_detail_pub.line_details_pub (
p_line_id => g_line_tbl (1).line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => x_qty_detailed,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => l_revision,
x_locator_id => x_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => x_lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => l_header_id,
p_transaction_mode => 2,
p_move_order_type => l_move_order_type,
p_serial_flag => NULL,
p_auto_pick_confirm => FALSE,
p_commit => FALSE);
ELSE
x_return_status := 'S';
END IF;

IF x_return_status = 'S'
THEN
fnd_file.put_line (
fnd_file.LOG,
'Return Status For Transact Move Order Allocation: '
|| x_return_status);
DBMS_OUTPUT.put_line (
'Return Status For Transact Move Order Allocation: '
|| x_return_status);

BEGIN
SELECT mmt.TRANSACTION_HEADER_ID, mmt.TRANSACTION_TEMP_ID
INTO x_transaction_header_id, x_transaction_temp_id
FROM mtl_txn_request_headers mtr,
mtl_txn_request_lines mtl,
mtl_material_transactions_temp mmt
WHERE mtr.header_id = mtl.header_id
AND mtr.request_number = l_request_number
AND mmt.move_order_line_id = mtl.line_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error getting Transaction temp id ');
END;

BEGIN
SELECT serial_number
INTO l_ser_number
FROM xxxx_flex_3b2_serial
WHERE so_header_id = c_del_det.source_header_id
AND so_line_id = c_del_det.source_line_id
AND so_number = c_del_det.order_number
AND ERROR_CODE IS NULL;

serial_check.inv_mark_serial (l_ser_number,
l_ser_number,
c_del_det.inventory_item_id,
c_del_det.organization_id,
x_transaction_header_id,
x_transaction_temp_id,
NULL,
x);

INSERT INTO mtl_serial_numbers_temp (transaction_temp_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
serial_prefix,
group_header_id)
VALUES (x_transaction_temp_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
l_ser_number,
l_ser_number,
1,
x_transaction_header_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error getting Serial number ');
END;

COMMIT;
l_transaction_mode := 2;
inv_pick_wave_pick_confirm_pub.pick_confirm (
p_api_version_number => 1.0 --API version number
,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => xm_return_status --API return status(S,U,E)
,
x_msg_count => xm_msg_count --No.of rows processd
,
x_msg_data => xm_msg_data --Error message
,
p_move_order_type => l_move_order_type -- 1
,
p_transaction_mode => l_transaction_mode,
p_trolin_tbl => g_line_tbl --contains all move order details
,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => l_mmtt_tbl,
x_trolin_tbl => l_trolin_tbl);

IF xm_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Move Order Transacted');
DBMS_OUTPUT.put_line ('Move Order Transacted');
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'ERROR: Issue while Transacting Move Order');
DBMS_OUTPUT.put_line (
'ERROR: Issue while Transacting Move Order');

IF (NVL (xm_msg_count, 0) = 0)
THEN
fnd_file.put_line (fnd_file.LOG, 'No Message Return');
DBMS_OUTPUT.put_line ('No Message Return');
ELSE
FOR i IN 1 .. xm_msg_count
LOOP
l_error_message :=
l_error_message || ':' || fnd_msg_pub.get (i, 'F');
fnd_file.put_line (fnd_file.LOG, l_error_message);
DBMS_OUTPUT.put_line (l_error_message);
END LOOP;
END IF;
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'ERROR: Unable to Update Allocation');
DBMS_OUTPUT.put_line ('ERROR: Unable to Update Allocation');

IF x_msg_count > 0
THEN
FOR l_quantity IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => l_quantity,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => l_msg_index_out);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
fnd_file.put_line (fnd_file.LOG, x_msg_data);
fnd_file.put_line (
fnd_file.LOG,
'ERROR: Allocation and Transaction not happen for the Move Order Line: '
|| l_trolin_tbl (l_quantity).line_id);
DBMS_OUTPUT.put_line (
'ERROR: Allocation and Transaction not happen for the Move Order Line: '
|| l_trolin_tbl (l_quantity).line_id);
fnd_file.put_line (
fnd_file.LOG,
'============================================================');
END LOOP;
END IF;

ROLLBACK;
fnd_file.put_line (fnd_file.LOG, 'ERROR: Allocation FAILED');
DBMS_OUTPUT.put_line ('ERROR: Allocation FAILED');
END IF;
END IF;
END;

l_error_message := NULL;
l_request_number := NULL;
l_msg_index_out := NULL;
l_serial_number_control_code := NULL;
l_request_number := NULL;
END LOOP;
END transact_moveorder;

Unprocessed Shipping Transactions Query

SELECT 'Unprocessed Shipping Transactions'||'~'||
o.organization_code ||'~'||
to_char(wdd.last_update_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
decode(trunc(sysdate)-trunc(wdd.last_update_date),0,'',1,'',2,'','Escalate')||'~'||
wdd.source_header_id ||'~'||
ooh.order_number||'~'||
wnd.delivery_id||'~'||
wnd.name ||'~'||
wdd.delivery_detail_id ||'~'||
wdl.pick_up_stop_id ||'~'||
wdd.inv_interfaced_flag||'~'||
wts.actual_departure_date||'~'||
ool.ship_from_org_id l_out
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
oe_order_headers_all ooh,
oe_order_lines_all ool,
org_organization_definitions o
WHERE wdd.source_code = 'OE'
AND wdd.released_status = 'C'
AND o.organization_id = ool.ship_from_org_id
AND (wdd.inv_interfaced_flag IN ('N' ,'P')
OR wdd.oe_interfaced_flag IN ('N' ,'P'))
AND wdd.organization_id = nvl(p_org_id,wdd.organization_id)
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.status_code IN ('CL','IT')
AND wdl.delivery_id = wnd.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdd.source_header_id = ooh.header_id
AND wdd.source_line_id = ool.line_id
AND wdd.inv_interfaced_flag != 'X'
and (:p_minutes is null or wdd.last_update_date < SYSDATE - (:p_minutes/(24*60)) )
order by o.organization_code, wdd.last_update_date;

Script to cancel delivery detail line

UPDATE wsh_delivery_details
SET released_status = 'D',
src_requested_quantity = 0,
requested_quantity = 0,
shipped_quantity = 0,
cycle_count_quantity = 0,
cancelled_quantity = 0,
subinventory = NULL,
locator_id = NULL,
lot_number = NULL,
revision = NULL,
inv_interfaced_flag = 'X',
oe_interfaced_flag = 'X'
WHERE delivery_detail_id IN (970233316, 970233320);

Wednesday, March 27, 2013

Backordered Lines

Steps to follow for Backordered lines in OM Shipping

1. Check in Shipping Transaction Form, make sure the order still in “Ready to Release”

2. There might be a lot of possibilities for this problem:
i) Order on hold
ii) Do not have enough qty
iii) Lot expired
iv) Wrong reservation (even inventory have enough qty)
v) Inventory reserved for other orders.
vi) Inventory picked up by other orders
vii) Previously return to stock not done properly.

3. Check the order type in Shipping Transaction Form - Detail > Source
Or go to WMS Control Board > Picking
Or go to OM Shipping View

4. Go to Material Workbench to check if the lot has expired or not having enough inventory for this order.

5. We can also check if any lot is reserved for other order as well.

6. In Material Work Bench, go check for each lot in the physical locator by clicking on the “Attribute” button. (lot level reservation)

7. Alternatively, go to Shipping Transaction Form - OM Shipping View, or go to “Lot Number” (Ctrl + L), click on Attribute column to check the SO reservation of the lot.

8. Compare the SO info with the order that are having problem.

9. Go to “Reserve Supply” form (Ctrl + L) to check if the order is wrongly reserved.

10. In the “Reserve Supply form, we can check if the qty is being picked up by other order as well. (order level reservation regardless of lot)

11. Also, can go to Material Workbench > Availability, to check for the available to reserve. It might have high possibility that the qty is taken up by order with different line, or other orders which have been auto launched. If this is the case, must do the backorder using below steps
- Go to Transact Move Order Form tab “Pick Wave” > View/Update allocation > Lot/Serial.
- Click on the checkbox for order which need to be backordered, then go to Tool ® Back Order Line.

12. Ask the user if the order has done return to stock previously. If yes, it might has high possibility that the return to stock not done properly.

13. Check the lot state, must be in “Resides in inventory”.

14. Check in Material Workbench to see if the lot is still packed with LPN or not.

15. Check also if the lot is in physical locator rather than logical locator.

Thursday, October 27, 2011

Pick Release API

CREATE OR REPLACE PROCEDURE APPS.xx_om_pick_release
(x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER
)
AS
CURSOR cur_delivery_details IS
SELECT 100 delivery_id,100 Trip_id
FROM wsh_delivery_details
WHERE delivery_detail_id = 100;---Hard coded value
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);
fail_api EXCEPTION;
l_picked_flag VARCHAR2(10);
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
ln_user_id number := fnd_profile.value('USER_ID');
ln_trip_id number;
lv_trip_name varchar2(30);
x_return_status varchar2(10);
x_msg_count number;
x_msg_data varchar2(500);

BEGIN
-- FND_GLOBAL.APPS_INITIALIZE (user_id, resp_id, app_id, NULL);
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
i := 0;
-- FOR row_delivery_details IN cur_delivery_details
-- LOOP
dbms_output.put_line( 'Before API');
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 => 13486,
p_delivery_name => 13486,
-- p_asg_trip_id = 100,
x_trip_id => ln_trip_id,
x_trip_name => lv_trip_name
);
commit;
IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
RAISE fail_api;
ELSE
fnd_file.put_line(fnd_file.log, 'Success: Pick release');
dbms_output.put_line( 'Success: Pick release'||' '||x_trip_id);
END IF;
-- END LOOP; --row_delivery_details

EXCEPTION
WHEN fail_api THEN
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;
ELSE
x_msg_data := x_msg_summary || x_msg_details;
END IF;

END xx_om_pick_release;

Welcome to Oracle ERP

Pages