[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_profile.value('INV_DEBUG_TRACE')
INTO l_trace_on
FROM dual;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE source_header_id = p_shipment_header_id
AND lpn_context = 7
AND source_type_id = 1;
DELETE mtl_serial_numbers
WHERE lpn_id = l_lpn_id;
DELETE wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id;
DELETE wms_license_plate_numbers
WHERE lpn_id = l_lpn_id;
SELECT license_plate_number,
lot_number,
from_serial_number,
to_serial_number,
item_description,
quantity,
uom_code,
serial_transaction_intf_id
FROM wms_lpn_contents_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT DISTINCT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn,
rcv_transactions_interface rti
WHERE wlpn.source_header_id = rti.shipment_header_id
AND wlpn.lpn_context = 7
AND wlpn.source_type_id = 1
AND rti.group_id = v_group_id;
SELECT group_id,
to_organization_id,
item_id,
item_revision,
shipment_header_id,
po_line_id,
quantity,
unit_of_measure,
uom_code,
header_interface_id,
shipment_num
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT uom_code
FROM wms_lpn_contents_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT *
INTO l_rcv_txn_interface_rec
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT COUNT(*)
INTO l_num_recs_per_group
FROM wms_lpn_contents_interface
WHERE group_id = l_rcv_txn_interface_rec.group_id
;
SELECT group_id,
item_id,
item_num,
validation_flag,
processing_status_code,
PO_LINE_LOCATION_ID,
SHIPMENT_NUM
FROM rcv_transactions_interface where group_id = l_rcv_txn_interface_rec.group_id
and item_id is not null )
loop
IF (l_debug = 1) THEN
print_debug(' 1. Group Id' || c_group.group_id , 4);
SELECT nvl(SUM(quantity),0)
INTO l_total_quantity
FROM wms_lpn_contents_interface
WHERE group_id = c_group.group_id
AND item_num = c_group.item_num
;
SELECT nvl(SUM(quantity),0)
INTO l_total_quantity_rti
FROM rcv_transactions_interface
WHERE group_id = c_group.group_id
AND item_id = c_group.item_id
;
SELECT uom_code
INTO l_rcv_txn_interface_rec.uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rcv_txn_interface_rec.unit_of_measure;
SELECT lpn_id
INTO l_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn_interface_rec.license_plate_number
AND source_header_id = l_shipment_header_id
AND lpn_context = 7
AND source_type_id = 1;
SELECT lot_control_code,
serial_number_control_code,
shelf_life_code,
shelf_life_days
INTO l_lot_control_code,
l_serial_control_code,
l_shelf_life_code,
l_shelf_life_days
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
print_debug('About to insert a lot - nothing will happen if this lot/item combination already exists', 4);
inv_lot_api_pub.insertlot
(p_api_version => l_api_version,
p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_lot_number => l_lpn_interface_rec.lot_number,
p_expiration_date => l_expiration_date,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
print_debug('Lot insertion failed. ', 4);
inv_serial_number_pub.insert_range_serial
(p_api_version => l_api_version,
p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_from_serial_number => l_lpn_interface_rec.from_serial_number,
p_to_serial_number => l_lpn_interface_rec.to_serial_number,
p_initialization_date => Sysdate,
p_completion_date => NULL,
p_ship_date => NULL,
p_revision => l_revision,
p_lot_number => l_lpn_interface_rec.lot_number,
p_current_locator_id => NULL,
p_subinventory_code => NULL,
p_trx_src_id => NULL,
p_unit_vendor_id => NULL,
p_vendor_lot_number => NULL,
p_vendor_serial_number => NULL,
p_receipt_issue_type => NULL,
p_txn_src_id => NULL,
p_txn_src_name => NULL,
p_txn_src_type_id => NULL,
p_transaction_id => NULL,
p_current_status => 5,
p_parent_item_id => NULL,
p_parent_serial_number => NULL,
p_cost_group_id => NULL,
p_transaction_action_id => 27,
p_transaction_temp_id => NULL,
p_status_id => NULL,
p_inspection_status => NULL,
x_object_id => l_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
wms_asn_lot_att.insert_range_serial
(p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_from_serial_number => l_lpn_interface_rec.from_serial_number,
p_to_serial_number => l_lpn_interface_rec.to_serial_number,
p_initialization_date => Sysdate,
p_completion_date => NULL,
p_ship_date => NULL,
p_revision => l_revision,
p_lot_number => l_lpn_interface_rec.lot_number,
p_current_locator_id => NULL,
p_subinventory_code => NULL,
p_trx_src_id => NULL,
p_unit_vendor_id => NULL,
p_vendor_lot_number => NULL,
p_vendor_serial_number => NULL,
p_receipt_issue_type => NULL,
p_txn_src_id => NULL,
p_txn_src_name => NULL,
p_txn_src_type_id => NULL,
p_transaction_id => NULL,
p_current_status => 5,
p_parent_item_id => NULL,
p_parent_serial_number => NULL,
p_cost_group_id => NULL,
p_serial_transaction_intf_id => l_lpn_interface_rec.serial_transaction_intf_id,
p_status_id => NULL,
p_inspection_status => NULL,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
DELETE wms_lpn_contents_interface
WHERE group_id = l_rcv_txn_interface_rec.group_id ;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE source_header_id = p_shipment_header_id
AND lpn_context = 7
AND source_type_id = 1;