[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(msi.REVISION_QTY_CONTROL_CODE,1)
,nvl(msi.LOT_CONTROL_CODE,1)
,nvl(msi.SERIAL_NUMBER_CONTROL_CODE,1)
from MTL_SYSTEM_ITEMS msi
where ORGANIZATION_ID = p_organization_id
and INVENTORY_ITEM_ID = p_inventory_item_id
;
l_update_mol BOOLEAN;
SELECT from_organization_id
, lot_number
, revision
, from_subinventory_code
, from_locator_id
, lpn_id
, reservation_id
, sum(primary_quantity) primary_quantity
, sum(transaction_quantity) transaction_quantity
, sum(secondary_quantity) secondary_quantity
, revision || ' - ' || lot_number || ' - ' || from_subinventory_code || ' - ' || from_locator_id || ' - ' || lpn_id as sugg_str
FROM wms_transactions_temp
WHERE line_type_code = 2
GROUP BY from_organization_id,
lot_number, from_subinventory_code, revision,
from_locator_id, lpn_id, reservation_id
ORDER BY sugg_str, reservation_id;
SELECT mr.organization_id org_id,
mr.reservation_id res_id
FROM mtl_reservations mr
WHERE mr.organization_id = p_mo_line_rec.organization_id
AND mr.demand_source_type_id = INV_GLOBALS.G_SOURCETYPE_WIP
AND mr.demand_source_header_id = p_demand_source_header_id
AND mr.demand_source_line_id = p_demand_source_line_id
AND NVL(mr.lot_number,'@@@') = '@@@'
AND NVL(mr.subinventory_code, '@@@') = '@@@'
AND NVL(mr.locator_id, '-999') = '-999'
AND NVL(mr.lpn_id, '-999') = '-999'
AND NVL(mr.revision, '@@@') = '@@@'
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date, mr.reservation_id;
SELECT serial_number
FROM wms_transactions_temp
WHERE line_type_code = 2
AND from_organization_id = lc_from_org
AND from_subinventory_code = lc_from_sub
AND nvl(from_locator_id,-888) = nvl(lc_from_loc,-888)
AND nvl(revision,'@@@') = nvl(lc_from_rev,'@@@')
AND nvl(lot_number,'@@@') = nvl(lc_lot_num,'@@@')
AND nvl(lpn_id,-888) = nvl(lc_lpn_id, -888)
AND nvl(reservation_id,-888) = nvl(lc_res_id, -888);
SELECT from_organization_id
, to_organization_id
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, to_subinventory_code
, from_locator_id
, to_locator_id
, lpn_id
, reservation_id
, serial_number
, grade_code
, from_cost_group_id
, to_cost_group_id
, sum(primary_quantity) primary_quantity
, sum(transaction_quantity) transaction_quantity
, sum(secondary_quantity) secondary_quantity
FROM wms_transactions_temp
WHERE line_type_code = 2
GROUP BY from_organization_id, to_organization_id, revision,
lot_number, lot_expiration_date, from_subinventory_code,
to_subinventory_code, from_locator_id, to_locator_id, lpn_id, reservation_id,
serial_number, grade_code, from_cost_group_id, to_cost_group_id;
SELECT mr.primary_reservation_quantity
FROM mtl_reservations mr
WHERE mr.reservation_id = p_rsv_id;
update mtl_txn_request_lines
set quantity = l_reservable_qty,
primary_quantity = l_reservable_primary_qty,
secondary_quantity = l_reservable_sec_qty
where line_id = p_mo_line_rec.line_id;
log_event(l_api_name, 'Suggest_Reservations','Updated number of rows: ' || SQL%ROWCOUNT);
log_event(l_api_name, 'Suggest_Reservations','Update the INV Cache');
l_update_mol := INV_CACHE.set_mol_rec(p_mo_line_rec.line_id);
log_event(l_api_name, 'Suggest_Reservations','Update the p_mo_line_rec');
DELETE FROM WMS_TRANSACTIONS_TEMP WHERE line_type_code = 2;
fnd_msg_pub.delete_msg();
log_statement(l_api_name, 'update_tree', 'Updating qty tree');
inv_quantity_tree_pvt.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_tree_id => l_tree_id
, p_revision => l_grp_sugg_rec.revision
, p_lot_number => l_grp_sugg_rec.lot_number
, p_subinventory_code => l_grp_sugg_rec.from_subinventory_code
, p_locator_id => l_grp_sugg_rec.from_locator_id
, p_primary_quantity => -1 * l_grp_sugg_rec.primary_quantity
, p_secondary_quantity => -1 * l_grp_sugg_rec.secondary_quantity -- INVCONV
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- INVCONV
, x_srqoh => l_srqoh -- INVCONV
, x_sqr => l_sqr -- INVCONV
, x_sqs => l_sqs -- INVCONV
, x_satt => l_satt -- INVCONV
, x_satr => l_satr -- INVCONV
, p_transfer_subinventory_code => null
, p_cost_group_id => null
, p_lpn_id => l_grp_sugg_rec.lpn_id
);
log_statement(l_api_name, 'uerr_update_qty', 'Unexpected error in inv_quantity_tree_pvt.update_quantities');
log_statement(l_api_name, 'err_update_qty', 'Error in inv_quantity_tree_pvt.update_quantities');
inv_reservation_pvt.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_orig_reservation
, p_to_rsv_rec => l_new_reservation
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_reserved_serials
, p_validation_flag => fnd_api.g_false
, p_check_availability => fnd_api.g_false
);
log_error(l_api_name, 'Suggest_Reservations','error in update reservation');
fnd_message.set_name('INV', 'INV_UPDATE_RSV_FAILED');
/* Update the current reservation with the quantities from the new reservation */
l_new_reservation.primary_reservation_quantity := l_last_reservation.primary_reservation_quantity + l_new_reservation.primary_reservation_quantity;
inv_reservation_pvt.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_last_reservation
, p_to_rsv_rec => l_new_reservation
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_reserved_serials
, p_validation_flag => 'Q'
, p_check_availability => fnd_api.g_false
);
log_error(l_api_name, 'Suggest_Reservations','error in update reservation');
fnd_message.set_name('INV', 'INV_UPDATE_RSV_FAILED');
inv_reservation_pvt.delete_reservation (
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_rec
, p_original_serial_number => l_original_serial_number
, p_validation_flag => NULL
);
log_event(l_api_name, 'Deleting Reservations', 'When deleting high level reservation inv_reservation_pub.delete_reservation returned ' || l_return_status);