[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cia.instance_asset_id,
cia.asset_quantity,
cia.object_version_number,
cia.fa_asset_id,
cia.active_end_date
FROM csi_i_assets cia
WHERE cia.instance_id = p_instance_id
AND cia.fa_asset_id = p_asset_id
AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1);
SELECT cia.fa_asset_id
FROM csi_i_assets cia
WHERE instance_id = p_instance_id
AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1);
SELECT ct.transaction_id
FROM csi_item_instances_h ciih,
csi_transactions ct
WHERE ciih.instance_id = p_instance_id
AND ct.transaction_id = ciih.transaction_id
AND ct.transaction_type_id IN (117, 129, 128, 105, 112, 118, 119)
AND ct.transaction_status_code = l_pending_status
AND ct.inv_material_transaction_id is not null;
SELECT cia.fa_mass_addition_id
FROM csi_i_assets cia
WHERE instance_id = p_instance_id
AND fa_asset_id is null
AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1);
SELECT accounting_class_code,
location_type_code,
inventory_item_id,
last_vld_organization_id,
instance_number,
serial_number
INTO l_acct_class_code,
l_location_type_code,
l_inventory_item_id,
l_organization_id,
l_inst_num,
l_serial_number
FROM csi_item_instances
WHERE instance_id = px_inst_asset_rec.instance_id;
SELECT asset_creation_code,
segment1,
serial_number_control_code
INTO l_asset_creation_code,
l_inventory_item,
l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT fb.book_type_code
FROM fa_books fb,
fa_book_controls fbc
WHERE fb.asset_id = p_asset_id
AND fb.date_ineffective is null
AND fbc.book_type_code = fb.book_type_code
AND fbc.book_class = 'CORPORATE';
SELECT fa_location_id
FROM csi_a_locations
WHERE location_table = p_table
AND location_id = p_loc_id;
SELECT location_id,
distribution_id,
units_assigned
FROM fa_distribution_history
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND date_ineffective is null
ORDER BY date_effective desc; -- latest one first
SELECT location_type_code,
location_id,
quantity
INTO l_location_type_code,
l_location_id,
l_instance_quantity
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
SELECT location_id
INTO l_location_id
FROM hz_party_sites
WHERE party_site_id = l_location_id;
SELECT eam_item_type,
serial_number_control_code
INTO l_eam_item_type,
l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_instance_rec.inventory_item_id
AND organization_id = p_instance_rec.vld_organization_id;
l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
l_party_tbl.delete;
SELECT instance_party_id,
object_version_number,
party_id
INTO l_party_tbl(1).instance_party_id,
l_party_tbl(1).object_version_number,
l_party_tbl(1).party_id
FROM csi_i_parties
WHERE instance_id = l_instance_tbl(1).instance_id -- for copy children case need to change
AND relationship_type_code = 'OWNER';
csi_item_instance_pub.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_instance_rec,
p_ext_attrib_values_tbl => l_eav_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_party_acct_tbl,
p_pricing_attrib_tbl => l_pricing_attribs_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_txn_rec => px_csi_txn_rec,
p_asset_assignment_tbl => l_inst_asset_tbl,
x_instance_id_lst => l_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT eam_item_type,
serial_number_control_code
INTO l_eam_item_type,
l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_instance_rec.inventory_item_id
AND organization_id = p_instance_rec.vld_organization_id;
l_instance_asset_tbl(ia_ind).update_status := 'IN_SERVICE';
l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
PROCEDURE update_asset_association(
p_instance_asset_tbl IN csi_datastructures_pub.instance_asset_tbl,
px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
x_return_status OUT nocopy varchar2,
x_error_message OUT nocopy varchar2)
IS
l_instance_asset_rec csi_datastructures_pub.instance_asset_rec;
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 => l_instance_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);
fnd_message.set_token('API_NAME','csi_fa_instance_grp.update_asset_association');
END update_asset_association;