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;
Great job! It works as specified. Only tweak I made was passing the serial number to the procedure instead of getting from "jdsu_flex_3b2_serial". Also it complained that serial_check.inv_mark_serial does not exist
ReplyDeleteThanks for your great information. Sign up and Register Your Events Today! Upcoming Events in India. We make registration process easier and track number of students attending the event.Online Event Registration Websites
ReplyDeleteHello Guys
ReplyDeleteI am facing issue with Allocate and Transact Move Order open interface
Error:
When the one item having multiple move order at that time it was going to loop and first MO and second Mo also updating properly but third Mo facing some problem so that transaction was not updating for some items.
Can you please give any suggestion for this issue,
Thanks In advance.