Oracle R12: Script to update Serial Numbers on Install Base

/* Formatted on 2011/01/25 14:10 (Formatter Plus v4.8.0) */
DECLARE
CURSOR ib_cur
IS
SELECT cii.instance_id, cii.serial_number, cii.inventory_item_id,
cii.object_version_number
FROM csi_item_instances cii
WHERE 1 = 1 AND cii.instance_id = :ip_instance_id
ORDER BY 1;

TYPE ib_rec_tbl_type IS TABLE OF ib_cur%ROWTYPE;

ib_rec_tbl ib_rec_tbl_type;
l_api_version CONSTANT NUMBER := 1.0;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_instance_id_lst csi_datastructures_pub.id_tbl;
l_instance_header_rec csi_datastructures_pub.instance_header_rec;
l_party_header_tbl csi_datastructures_pub.party_header_tbl;
l_party_acct_header_tbl csi_datastructures_pub.party_account_header_tbl;
l_org_unit_header_tbl csi_datastructures_pub.org_units_header_tbl;
l_instance_rec csi_datastructures_pub.instance_rec;
l_party_tbl csi_datastructures_pub.party_tbl;
l_account_tbl csi_datastructures_pub.party_account_tbl;
l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
l_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
l_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl;
l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
l_txn_rec csi_datastructures_pub.transaction_rec;
l_return_status VARCHAR2 (5);
lc_init_msg_lst VARCHAR2 (1) := 'T';
ln_validation_level NUMBER;
lc_error_text VARCHAR2 (4000);
BEGIN
OPEN ib_cur;

FETCH ib_cur
BULK COLLECT INTO ib_rec_tbl;

CLOSE ib_cur;

IF ib_rec_tbl.COUNT > 0
THEN
DBMS_OUTPUT.put_line ('Begin loop');

FOR i IN ib_rec_tbl.FIRST .. ib_rec_tbl.LAST
LOOP
l_instance_header_rec.instance_id := ib_rec_tbl (i).instance_id;
csi_item_instance_pub.get_item_instance_details
(p_api_version => l_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_header_rec,
p_get_parties => fnd_api.g_true,
p_party_header_tbl => l_party_header_tbl,
p_get_accounts => fnd_api.g_true,
p_account_header_tbl => l_party_acct_header_tbl,
p_get_org_assignments => fnd_api.g_true,
p_org_header_tbl => l_org_unit_header_tbl,
p_get_pricing_attribs => fnd_api.g_false,
p_pricing_attrib_tbl => l_pricing_attribs_tbl,
p_get_ext_attribs => fnd_api.g_false,
p_ext_attrib_tbl => l_ext_attrib_tbl,
p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
p_get_asset_assignments => fnd_api.g_false,
p_asset_header_tbl => l_asset_header_tbl,
p_resolve_id_columns => fnd_api.g_false,
p_time_stamp => SYSDATE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
lc_error_text := NULL;
l_instance_rec.instance_id := l_instance_header_rec.instance_id;
l_txn_rec.transaction_id := fnd_api.g_miss_num;
l_txn_rec.transaction_date := SYSDATE;
l_txn_rec.source_transaction_date := SYSDATE;
l_txn_rec.transaction_type_id := 205;
l_instance_rec.serial_number := :ip_new_serial_number;
l_instance_rec.object_version_number :=
l_instance_header_rec.object_version_number;
DBMS_OUTPUT.put_line ( 'Updating IB record for IB# '
|| ib_rec_tbl (i).instance_id
);
csi_item_instance_pub.update_item_instance
(p_api_version => l_api_version,
p_commit => 'F',
p_init_msg_list => lc_init_msg_lst,
p_validation_level => ln_validation_level,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
DBMS_OUTPUT.put_line
( 'Error updating the install base for IB# '
|| ib_rec_tbl (i).instance_id
);

FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => -1,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index
);
lc_error_text := lc_error_text
|| (SUBSTR (l_msg_data, 1, 255));
END LOOP;

DBMS_OUTPUT.put_line (lc_error_text);
ELSE
DBMS_OUTPUT.put_line
( 'Install base update successful for IB# '
|| ib_rec_tbl (i).instance_id
);
lc_error_text := 'SUCCESS!';
COMMIT;
END IF;
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No records to process');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error in update_ib procedure');
DBMS_OUTPUT.put_line (TO_CHAR (SQLCODE) || ' - ' || SQLERRM);
END;