[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_error_id
INTO l_error_id
FROM csi_txn_errors
WHERE source_type = 'CSEFATIE'
AND source_id = l_error_rec.source_id
AND rownum < 2;
UPDATE csi_txn_errors
SET error_text = l_error_rec.error_text,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
WHERE transaction_error_id = l_error_id;
debug(' error updated. transaction_error_id : '||l_error_id);
SELECT object_version_number
INTO l_txn_rec.object_version_number
FROM csi_transactions
WHERE transaction_id = l_txn_rec.transaction_id;
csi_transactions_pvt.update_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_transaction_rec => l_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_src_type_id,
mmt.ship_to_location_id location_id
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mmt.transaction_id = mut.transaction_id
AND mmt.transaction_id < p_mtl_txn_id
UNION
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_src_type_id,
mmt.ship_to_location_id location_id
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mtln.organization_id = mut.organization_id
AND mtln.transaction_date = mut.transaction_date
AND mtln.serial_transaction_id = mut.transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_id < p_mtl_txn_id
ORDER BY 1 desc, 2 desc;
SELECT 'Y'
FROM csi_transactions ct,
csi_item_instances_h ciih
WHERE ciih.instance_id = p_instance_id
AND ct.transaction_id = ciih.transaction_id
AND ct.transaction_date < p_transaction_date
AND ct.transaction_type_id in (110, 108, 132, 133);
SELECT ct.transaction_id
FROM csi_transactions ct,
csi_item_instances_h ciih
WHERE ciih.instance_id = p_instance_id
AND ct.transaction_id = ciih.transaction_id
AND ct.transaction_id < p_csi_txn_id
AND ct.transaction_status_code = 'PENDING';
SELECT poh.rate_type,
poh.currency_code,
pod.rate,
poh.rate_date,
sob.currency_code,
pod.set_of_books_id
FROM po_distributions_all pod,
po_headers_all poh,
gl_sets_of_books sob
WHERE pod.po_distribution_id = p_po_distribution_id
AND poh.po_header_id = pod.po_header_id
AND sob.set_of_books_id = pod.set_of_books_id ;
SELECT gsob.currency_code
FROM gl_sets_of_books gsob,
fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND gsob.set_of_books_id = fbc.set_of_books_id ;
SELECT decode(fc.minimum_accountable_unit,
NULL, ROUND(p_amount, FC.precision),
ROUND(p_amount/FC.minimum_accountable_unit) * FC.minimum_accountable_unit)
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code;
SELECT fa_location_id
FROM csi_a_locations
WHERE location_table in ('LOCATION_CODES', p_location_table)
AND location_id = p_location_id
AND sysdate BETWEEN nvl(active_start_date, sysdate - 1)
AND nvl(active_end_date, sysdate + 1);
SELECT 'Y' INTO l_hz_or_hr
FROM hz_locations
WHERE location_id = p_location_id;
SELECT source_transaction_type
INTO l_asset_attrib_rec.source_transaction_type
FROM csi_txn_types
WHERE transaction_type_id = l_inst_tbl(l_ind).csi_txn_type_id;
SELECT concatenated_segments
INTO l_asset_category
FROM fa_categories_b_kfv
WHERE category_id = l_asset_category_id;
SELECT default_group_asset_id
INTO l_default_group_asset_id
FROM fa_category_books
WHERE category_id = l_asset_category_id
AND book_type_code = l_book_type_code;
SELECT poh.po_header_id,
poh.segment1,
poh.vendor_id
INTO l_inst_tbl(l_ind).po_header_id,
l_inst_tbl(l_ind).po_number,
l_inst_tbl(l_ind).po_vendor_id
FROM po_headers_all poh,
po_distributions_all pod
WHERE pod.po_distribution_id = l_inst_tbl(l_ind).po_distribution_id
AND poh.po_header_id = pod.po_header_id;
:= 'SELECT fad.asset_id, fad.asset_number, fad.asset_category_id, fad.asset_key_ccid, '||
'fad.tag_number, fad.description, fad.manufacturer_name, fad.serial_number, '||
'fad.model_number, fad.current_units, fb.book_type_code, '||
'fb.date_placed_in_service, fb.cost, cia.instance_asset_id '||
'FROM fa_books fb, fa_additions fad, csi_i_assets cia, csi_item_instances cii '||
'WHERE fb.asset_id = fad.asset_id '||
'AND fb.date_ineffective is null '||
'AND cia.fa_asset_id = fad.asset_id '||
'AND cii.instance_id = cia.instance_id ';
'SELECT fma.mass_addition_id, '||
'fma.model_number, '||
'fma.serial_number, '||
'fma.manufacturer_name, '||
'fma.description, '||
'fma.tag_number, '||
'fma.asset_key_ccid, '||
'fma.asset_category_id, '||
'fma.asset_number, '||
'fma.date_placed_in_service, '||
'fma.reviewer_comments, '||
'fma.feeder_system_name, '||
'cia.instance_asset_id '||
'FROM fa_mass_additions fma, csi_i_assets cia, csi_item_instances cii ';
SELECT asset_quantity + p_inst_rec.quantity,
object_version_number
INTO l_inst_asset_rec.asset_quantity,
l_inst_asset_rec.object_version_number
FROM csi_i_assets
WHERE instance_asset_id = l_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 => l_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);
debug('instance asset rec updated. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
l_inst_asset_rec.update_status := 'IN_SERVICE';
l_inst_asset_rec.update_status := 'IN_SERVICE';
SELECT asset_quantity + p_inst_rec.quantity,
object_version_number
INTO l_inst_asset_rec.asset_quantity,
l_inst_asset_rec.object_version_number
FROM csi_i_assets
WHERE instance_asset_id = l_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 => l_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);
debug('instance asset rec updated. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
l_inst_asset_rec.update_status := 'IN_SERVICE';
l_p_mass_add_rec.last_update_date := sysdate;
l_p_mass_add_rec.last_update_login := fnd_global.login_id;
SELECT default_group_asset_id
INTO l_p_mass_add_rec.group_asset_id
FROM fa_category_books
WHERE category_id = l_p_mass_add_rec.asset_category_id
AND book_type_code = l_p_mass_add_rec.book_type_code;
cse_asset_util_pkg.insert_mass_add(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_mass_add_rec => l_p_mass_add_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
cse_asset_util_pkg.insert_mass_add(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_mass_add_rec => l_c_mass_add_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_c_mass_add_rec.last_update_date := sysdate;
l_c_mass_add_rec.last_update_login := fnd_global.login_id;
cse_asset_util_pkg.insert_mass_add(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_mass_add_rec => l_c_mass_add_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE fa_mass_additions
SET payables_units = payables_units + NVL(l_c_mass_add_rec.payables_units,0),
fixed_assets_units = fixed_assets_units + NVL(l_c_mass_add_rec.fixed_assets_units,0)
WHERE mass_addition_id = p_mass_addition_id;
SELECT distribution_id,
units_assigned
FROM fa_distribution_history
WHERE asset_id = p_mass_add_rec.asset_id
AND book_type_code = p_mass_add_rec.book_type_code
AND location_id = p_mass_add_rec.location_id
AND code_combination_id = nvl(p_mass_add_rec.expense_code_combination_id , code_combination_id)
AND nvl(assigned_to,-1) = nvl(p_mass_add_rec.assigned_to, -1)
AND date_ineffective IS null;
l_mass_add_rec.last_update_date := sysdate;
l_mass_add_rec.last_update_login := fnd_global.login_id;
SELECT date_placed_in_service
INTO l_mass_add_rec.date_placed_in_service
FROM fa_books
WHERE asset_id = p_asset_id
AND book_type_code = p_instance_rec.book_type_code
AND date_ineffective is null;
cse_asset_util_pkg.insert_mass_add(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_mass_add_rec => l_mass_add_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT ct.transaction_type_id,
ct.transaction_id,
ct.transaction_date,
ct.inv_material_transaction_id,
ct.source_dist_ref_id2,
ct.source_dist_ref_id1
FROM csi_transactions ct
WHERE ct.transaction_type_id IN (117, 129, 128, 105, 112, 118, 119, 133, 132, 73) --Add WIP Assembly Completion for bug 7489949
AND ct.transaction_status_code = l_pending_status
AND ct.inv_material_transaction_id is not null
AND exists (
SELECT 1 FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = ct.inv_material_transaction_id
AND mmt.inventory_item_id = nvl(p_inventory_item_id, mmt.inventory_item_id)
AND mmt.organization_id = nvl(p_organization_id, mmt.organization_id))
ORDER BY ct.inv_material_transaction_id;
SELECT mut.serial_number serial_number,
to_char(null) lot_number,
1 quantity
FROM mtl_unit_transactions mut
WHERE mut.transaction_id = p_mtl_txn_id
UNION
SELECT mut.serial_number serial_number,
mtln.lot_number lot_number,
1 quantity
FROM mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut
WHERE mtln.transaction_id = p_mtl_txn_id
AND mut.transaction_id = mtln.serial_transaction_id;
SELECT cii.instance_id,
cii.lot_number,
cii.location_type_code,
cii.location_id,
cii.instance_usage_code,
cii.quantity,
nvl(ciih.old_quantity,0) old_quantity,
ciih.new_quantity
FROM csi_item_instances_h ciih,
csi_item_instances cii
WHERE ciih.transaction_id = p_csi_txn_id
AND cii.instance_id = ciih.instance_id
AND cii.inventory_item_id = p_inventory_item_id
AND nvl(ciih.new_quantity, 0) - nvl(ciih.old_quantity,0) > 0;
l_inst_tbl.delete;
SELECT inventory_item_id,
organization_id,
transaction_type_id,
transaction_date,
subinventory_code,
abs(primary_quantity),
source_project_id,
source_task_id,
distribution_account_id,
ship_to_location_id,
transaction_quantity
INTO l_inventory_item_id,
l_organization_id,
l_mtl_txn_type_id,
l_mtl_txn_date,
l_subinventory_code,
l_quantity,
l_pa_project_id,
l_pa_project_task_id,
l_distribution_acct_id,
l_ship_to_location_id,
l_mmt_quantity
FROM mtl_material_transactions
WHERE transaction_id = csi_txn_rec.inv_material_transaction_id;
SELECT transaction_type_name
INTO l_mtl_txn_type_name
FROM mtl_transaction_types
WHERE transaction_type_id = l_mtl_txn_type_id;
SELECT serial_number_control_code,
primary_uom_code,
asset_creation_code,
description,
concatenated_segments,
nvl(eam_item_type, 0)
INTO l_serial_code,
l_primary_uom_code,
l_asset_creation_code,
l_item_description,
l_item,
l_eam_item_type
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT location_id
INTO l_location_id
FROM mtl_secondary_inventories
WHERE organization_id = l_organization_id
AND secondary_inventory_name = l_subinventory_code;
SELECT location_id
INTO l_location_id
FROM hr_all_organization_units
WHERE organization_id = l_organization_id;
SELECT deliver_to_location_id
INTO l_location_id
FROM rcv_transactions
WHERE transaction_id = csi_txn_rec.source_dist_ref_id2;
SELECT instance_id
INTO l_instance_id
FROM csi_item_instances
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = srl_rec.serial_number;
SELECT distribution_id
,book_type_code
,location_id
,code_combination_id
,assigned_to
,units_assigned
FROM fa_distribution_history
WHERE asset_id = p_asset_query_rec.asset_id
AND book_type_code = NVL(p_asset_query_rec.book_type_code,book_type_code)
AND location_id = NVL(p_mass_add_rec.location_id , location_id)
AND code_combination_id = NVL(p_mass_add_rec.expense_code_combination_id , code_combination_id)
AND NVL(assigned_to, -1) = NVL(p_mass_add_rec.assigned_to, -1)
AND date_ineffective IS NULL;
SELECT distribution_id
,book_type_code
,location_id
,code_combination_id
,assigned_to
FROM fa_distribution_history
WHERE asset_id = p_asset_query_rec.asset_id
AND book_type_code = NVL(p_asset_query_rec.book_type_code,book_type_code)
AND date_ineffective IS NULL ;
SELECT distribution_id
,location_id
,assigned_to
,code_combination_id
,units_assigned
FROM fa_distribution_history
WHERE asset_id = p_asset_query_rec.asset_id
AND book_type_code = p_asset_query_rec.book_type_code
AND location_id = NVL(p_asset_query_rec.location_id,location_id)
AND code_combination_id = NVL(p_asset_query_rec.deprn_expense_ccid,code_combination_id)
AND NVL(assigned_to,-1) = NVL(p_asset_query_rec.employee_id,NVL(assigned_to,-1))
AND date_ineffective IS NULL ;
SELECT DECODE(attribute15,cse_asset_util_pkg.G_MTL_INDICATOR,SUM(fixed_assets_cost),0)
Material_cost ,
DECODE(attribute15,cse_asset_util_pkg.G_MTL_INDICATOR,0,SUM(fixed_assets_cost))
Non_Material_cost
FROM fa_asset_invoices
WHERE date_ineffective IS NULL
AND asset_id = p_asset_query_rec.asset_id
GROUP BY attribute15 ;
SELECT fab.asset_id,
fab.asset_number,
fab.asset_category_id,
fab.asset_key_ccid,
fab.tag_number,
fab.description,
fab.manufacturer_name,
fab.serial_number,
fab.model_number,
fab.current_units,
cii.inventory_item_id,
fb.book_type_code,
fb.date_placed_in_service,
fb.cost
FROM csi_item_instances cii,
csi_i_assets cia,
fa_books fb,
fa_additions fab
WHERE cii.inventory_item_id = NVL(p_asset_query_rec.inventory_item_id, cii.inventory_item_id)
AND cii.instance_id = cia.instance_id
AND cia.fa_asset_id = fab.asset_id
AND cia.fa_book_type_code = fb.book_type_code
AND TRUNC(fb.date_placed_in_service) =
TRUNC(NVL(p_asset_query_rec.date_placed_in_service, fb.date_placed_in_service))
AND fb.book_type_code = NVL(p_asset_query_rec.book_type_code, fb.book_type_code)
AND fb.date_ineffective IS NULL
AND fb.asset_id = fab.asset_id
AND NVL(fab.model_number, '!@#^') = NVL(p_asset_query_rec.model_number, NVL(fab.model_number, '!@#^') )
AND NVL(fab.serial_number, '!@#^') = NVL(p_asset_query_rec.serial_number, '!@#^')
AND NVL(fab.manufacturer_name, '!@#^') = NVL(p_asset_query_rec.manufacturer_name,NVL(fab.manufacturer_name, '!@#^') )
AND NVL(fab.tag_number, '!@#^') = NVL(p_asset_query_rec.tag_number, NVL(fab.tag_number, '!@#^') )
AND NVL(fab.asset_key_ccid, -1) = NVL(p_asset_query_rec.asset_key_ccid,NVL(fab.asset_key_ccid, -1) )
AND fab.asset_category_id = NVL(p_asset_query_rec.category_id,fab.asset_category_id)
AND fab.asset_number = NVL(p_asset_query_rec.asset_number,fab.asset_number)
AND fab.asset_id = NVL(p_asset_query_rec.asset_id,fab.asset_id)
ORDER BY fb.date_placed_in_service DESC, fab.asset_id DESC ;
SELECT fab.asset_id
,fab.asset_number
,fab.asset_category_id
,fab.asset_key_ccid
,fab.tag_number
,fab.description
,fab.manufacturer_name
,fab.serial_number
,fab.model_number
,fab.current_units
,cii.inventory_item_id
,fb.book_type_code
,fb.date_placed_in_service
,fb.cost
FROM csi_item_instances cii
,csi_i_assets cia
,fa_books fb
,fa_additions fab
WHERE cii.inventory_item_id = NVL(p_asset_query_rec.inventory_item_id,
cii.inventory_item_id)
AND cii.instance_id = cia.instance_id
AND cia.fa_asset_id = fab.asset_id
AND cia.fa_book_type_code = fb.book_type_code
AND TRUNC(fb.date_placed_in_service) =
TRUNC(NVL(p_asset_query_rec.date_placed_in_service, fb.date_placed_in_service))
AND fb.book_type_code = NVL(p_asset_query_rec.book_type_code, fb.book_type_code)
AND fb.date_ineffective IS NULL
AND fb.asset_id = fab.asset_id
AND NVL(fab.model_number, '!@#^') = NVL(p_asset_query_rec.model_number, NVL(fab.model_number, '!@#^') )
AND NVL(fab.serial_number, '!@#^') = NVL(p_asset_query_rec.serial_number, '!@#^')
AND NVL(fab.manufacturer_name, '!@#^') = NVL(p_asset_query_rec.manufacturer_name,NVL(fab.manufacturer_name, '!@#^') )
AND NVL(fab.tag_number, '!@#^') = NVL(p_asset_query_rec.tag_number, NVL(fab.tag_number, '!@#^') )
AND NVL(fab.asset_key_ccid, -1) = NVL(p_asset_query_rec.asset_key_ccid,NVL(fab.asset_key_ccid, -1) )
AND fab.asset_category_id = NVL(p_asset_query_rec.category_id,fab.asset_category_id)
AND fab.asset_number = NVL(p_asset_query_rec.asset_number,fab.asset_number)
AND fab.asset_id = NVL(p_asset_query_rec.asset_id,fab.asset_id)
ORDER BY fb.date_placed_in_service , fab.asset_id ;
SELECT fad.current_units
FROM fa_additions fad
WHERE fad.asset_id = c_asset_id ;
SELECT date_placed_in_service
FROM fa_books
WHERE asset_id = c_asset_id
AND book_type_code = c_book_type_code ;
SELECT default_group_asset_id
FROM fa_category_books
WHERE category_id = c_asset_category_id
AND book_type_code = c_book_type_code ;
SELECT fcgd.retirement_prorate_convention
FROM fa_category_book_defaults fcgd
,fa_books fb
,fa_additions_b fab
WHERE fb.date_placed_in_service BETWEEN fcgd.start_dpis AND
NVL(fcgd.end_dpis, fb.date_placed_in_service)
AND fb.date_ineffective IS NULL
AND fb.book_type_code = fcgd.book_type_code
AND fb.asset_id = fab.asset_id
AND fcgd.book_type_code = c_book_type_code
AND fcgd.category_id = fab.asset_category_id
AND fab.asset_id = c_asset_id ;
cse_asset_util_pkg.insert_mass_add(
1.0,
fnd_api.g_false,
fnd_api.g_true,
l_mass_add_rec,
x_return_status,
x_msg_count,
x_msg_data);
cse_asset_util_pkg.insert_mass_add(
1.0,
fnd_api.g_false,
fnd_api.g_true,
l_mass_add_rec,
x_return_status,
x_msg_count,
x_msg_data );
SELECT fa_mass_ext_retirements_s.nextval
INTO l_mass_external_retire_id
FROM dual ;
l_ext_ret_rec.last_updated_by := fnd_global.user_id;
l_ext_ret_rec.last_update_date := l_sysdate;
l_ext_ret_rec.last_update_login := fnd_global.login_id;
cse_asset_adjust_pkg.insert_retirement(
l_ext_ret_rec,
x_return_status,
x_error_msg) ;
debug('Insert into Retirements table failed ');