[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_IB_Instance (
p_instance_id IN NUMBER,
p_asset_quantity IN NUMBER,
p_Default_inst_status IN VARCHAR2,
p_active_end_date IN DATE,
px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
) IS
l_def_Instance_status_id NUMBER;
debug('Inside api cse_fa_txn_pkg.Update_IB_Instance');
l_u_instance_rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
SELECT object_version_number, serial_number, quantity, active_end_date
INTO l_u_instance_rec.object_version_number, l_serial_number, l_instance_qty, l_instance_end_date
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
SELECT old_inst_usage_code
INTO l_h_instance_usage_code
FROM csi_item_instances_h cih, csi_transactions ct
WHERE cih.instance_id = l_u_instance_rec.instance_id
AND cih.transaction_id = ct.transaction_id
AND ct.TRANSACTION_TYPE_ID = 104
AND old_inst_usage_code IS NOT NULL
AND rownum = 1
ORDER BY instance_history_id DESC;
SELECT old_instance_status_id
INTO l_h_Instance_status_id
FROM csi_item_instances_h cih, csi_transactions ct
WHERE cih.instance_id = l_u_instance_rec.instance_id
AND cih.transaction_id = ct.transaction_id
AND ct.TRANSACTION_TYPE_ID = 104
AND old_instance_status_id IS NOT NULL
AND rownum = 1
ORDER BY instance_history_id DESC;
SELECT instance_status_id
INTO l_def_Instance_status_id
FROM csi_instance_statuses
WHERE name = fnd_profile.value('CSI_DEFAULT_INSTANCE_STATUS')
AND ROWNUM =1 ;
debug('Calling API csi_item_instance_pub.update_item_instance');
csi_item_instance_pvt.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_txn_rec => l_u_csi_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_item_attribute_tbl => l_item_attribute_tbl,
p_location_tbl => l_location_tbl,
p_generic_id_tbl => l_generic_id_tbl,
p_lookup_tbl => l_lookup_tbl,
p_ins_count_rec => l_ins_count_rec,
p_oks_txn_inst_tbl => px_oks_txn_inst_tbl,
p_child_inst_tbl => px_child_inst_tbl);
END Update_IB_Instance;
PROCEDURE update_inst_asset(
px_inst_asset_rec IN OUT nocopy csi_datastructures_pub.instance_asset_rec,
px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
x_return_status OUT nocopy varchar2)
IS
l_lookup_tbl csi_asset_pvt.lookup_tbl;
SELECT object_version_number
INTO px_inst_asset_rec.object_version_number
FROM csi_i_assets
WHERE instance_asset_id = px_inst_asset_rec.instance_asset_id;
csi_asset_pvt.update_instance_asset (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_asset_rec => px_inst_asset_rec,
p_txn_rec => px_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_lookup_tbl => l_lookup_tbl,
p_asset_count_rec => l_asset_count_rec,
p_asset_id_tbl => l_asset_id_tbl,
p_asset_loc_tbl => l_asset_loc_tbl);
END update_inst_asset;
l_inst_asset_qry_rec.update_status := 'IN_SERVICE';
update_inst_asset(
px_inst_asset_rec => l_inst_asset_rec,
px_csi_txn_rec => px_csi_txn_rec,
x_return_status => l_return_status);
/* SELECT citdv.instance_id,citdv.*
FROM csi_i_assets_h ciah,
csi_item_instances_h ciih,
csi_transactions ct
WHERE ciah.transaction_id = ciih.transaction_id
AND citdv.transaction_type_id = 104
AND ciah.instance_asset_id = 75478
*/
--Update_IB_Instance Added for bug 13459669
debug(' Update_IB_Instance : '||l_inst_asset_tbl(1).Instance_Id);
Update_IB_Instance (
p_instance_id => l_inst_asset_tbl(1).Instance_Id,
p_asset_quantity => p_units ,
p_Default_inst_status => 'Y',
p_active_end_date => NULL,
px_csi_txn_rec => px_csi_txn_rec
);
l_inst_asset_rec.update_status := 'IN_SERVICE';
update_inst_asset(
px_inst_asset_rec => l_inst_asset_rec,
px_csi_txn_rec => px_csi_txn_rec,
x_return_status => l_return_status);
debug(' Update_IB_Instance : '||l_inst_asset_rec.Instance_Id);
Update_IB_Instance (
p_instance_id => l_inst_asset_rec.Instance_Id,
p_asset_quantity => p_units ,
p_Default_inst_status => 'Y',
p_active_end_date => NULL,
px_csi_txn_rec => px_csi_txn_rec
);
l_inst_asset_rec.update_status := 'RETIRED';
update_inst_asset(
px_inst_asset_rec => l_inst_asset_rec,
px_csi_txn_rec => px_csi_txn_rec,
x_return_status => l_return_status);
update_inst_asset(
px_inst_asset_rec => l_inst_asset_rec,
px_csi_txn_rec => px_csi_txn_rec,
x_return_status => l_return_status);
l_inst_asset_rec.update_status := 'IN_SERVICE';
l_inst_asset_qry_rec.update_status := 'IN_SERVICE';
SELECT distribution_id,
units_assigned,
transaction_units,
location_id,
assigned_to
FROM fa_distribution_history
WHERE retirement_id = p_retirement_id;
l_inst_asset_qry_rec.update_status := 'RETIRED';
SELECT fcbd.retirement_prorate_convention
FROM fa_category_book_defaults fcbd,
fa_books fb,
fa_additions_b fab
WHERE fab.asset_id = p_asset_id
AND fb.asset_id = fab.asset_id
and fb.book_type_code = p_book_type_code
AND fb.date_ineffective is null
AND fcbd.book_type_code = fb.book_type_code
AND fcbd.category_id = fab.asset_category_id;
SELECT distribution_id,
assigned_to,
units_assigned
FROM fa_distribution_history
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND location_id = p_fa_location_id
AND date_ineffective is null;
SELECT fa_mass_ext_retirements_s.nextval
INTO l_mass_ext_retire_id
FROM sys.dual ;
l_ext_ret_rec.last_updated_by := fnd_global.user_id;
l_ext_ret_rec.last_update_date := sysdate;
l_ext_ret_rec.last_update_login := fnd_global.login_id;
cse_asset_adjust_pkg.insert_retirement(
p_ext_ret_rec => l_ext_ret_rec,
x_return_status => l_return_status,
x_error_msg => l_error_message);