DECLARE
v_object_id NUMBER;
v_return_status VARCHAR2 (1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
l_org_code VARCHAR2 (240);
retval NUMBER;
ln_interface_id NUMBER;
l_return_status VARCHAR2 (100);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2 (500);
l_trans_count NUMBER;
v_header_id NUMBER;
ln_out NUMBER;
ln_quantity NUMBER;
ln_trn_type_id NUMBER;
l_error VARCHAR2 (240);
l_locator_id NUMBER;
l_uom VARCHAR2 (240);
p_sub_inv VARCHAR2 (240) := 'HIMPRD';
l_revision VARCHAR2 (240);
CURSOR c1
IS
SELECT *
FROM mtl_lot_numbers
WHERE lot_number IN
('035636');
BEGIN
FOR i IN c1
LOOP
BEGIN
SELECT inventory_location_id
INTO l_locator_id
FROM mtl_item_locations
WHERE segment1 = 'W300';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT primary_uom_code
INTO l_uom
FROM mtl_system_items_b
WHERE organization_id = i.organization_id
AND inventory_item_id = i.inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT MAX (revision)
INTO l_revision
FROM mtl_item_revisions
WHERE inventory_item_id IN (
SELECT inventory_item_id
FROM mtl_lot_numbers
WHERE lot_number = i.lot_number
AND organization_id = i.organization_id)
AND organization_id = i.organization_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO ln_interface_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
ln_interface_id := 0;
END;
BEGIN
SELECT transaction_type_id
INTO ln_trn_type_id
FROM mtl_transaction_types
WHERE transaction_type_name LIKE 'Miscellaneous receipt%';
EXCEPTION
WHEN OTHERS
THEN
ln_trn_type_id := 0;
END;
BEGIN
INSERT INTO mtl_transactions_interface
(source_code, source_line_id, source_header_id,
process_flag, transaction_mode, inventory_item_id,
organization_id, subinventory_code, locator_id,
transaction_quantity, transaction_uom,
transaction_date, transaction_type_id,
transfer_subinventory, lock_flag, last_update_date,
last_updated_by, created_by, creation_date,
transaction_interface_id, transfer_locator, revision,
distribution_account_id, primary_quantity
)
VALUES ('Miscellaneous receipt', 99, 99,
1, 3, i.inventory_item_id,
i.organization_id, p_sub_inv, l_locator_id,
10000, l_uom,
SYSDATE, ln_trn_type_id,
NULL, 2, SYSDATE,
3842, 3842, SYSDATE,
ln_interface_id, NULL, l_revision,
35449, 10000
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error while inserting into MTL_TRANSACTION_INTERFACE'
|| SQLERRM
|| SQLCODE
);
END;
ln_quantity := 10000;
BEGIN
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, lot_number,
lot_expiration_date, transaction_quantity,
last_update_date, last_updated_by, creation_date,
created_by, primary_quantity
)
VALUES (ln_interface_id, i.lot_number,
i.expiration_date, 10000,
SYSDATE, 3842, SYSDATE,
3842, 10000
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE'
|| SQLERRM
|| SQLCODE
);
END;
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_header_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
v_header_id := 0;
END;
BEGIN
UPDATE mtl_transactions_interface
SET transaction_header_id = v_header_id,
transaction_date = SYSDATE
WHERE source_code = 'Miscellaneous receipt'
AND transaction_header_id IS NULL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('Error while updating MTL_TRANSACTIONS_INTERFACE');
END;
COMMIT;
BEGIN
retval :=
apps.inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => v_header_id
);
DBMS_OUTPUT.put_line ('l_msg_data:' || l_msg_data);
DBMS_OUTPUT.put_line ('l_return_status:' || l_return_status);
DBMS_OUTPUT.put_line ('v_header_id:' || v_header_id);
DBMS_OUTPUT.put_line ('retval:' || retval);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error := SQLERRM || SQLCODE;
DBMS_OUTPUT.put_line ('ERROR occur:' || l_error);
END;
END LOOP;
END;
