[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
;
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 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;
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');
/* 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');