[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mmtt.transaction_temp_id
, mmtt.transaction_type_id
, mmtt.move_order_line_id
, mol.line_status
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mol
WHERE mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.move_order_line_id = mol.line_id
AND mol.line_status = inv_globals.g_to_status_cancel_by_source;
SELECT mmtt.transaction_temp_id
, ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mol_id
AND NOT EXISTS(
SELECT wdt.transaction_temp_id
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.transaction_temp_id IS NOT NULL
AND wdt.transaction_temp_id <> p_temp_id);
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_tmp_id;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(transaction_temp_id)
INTO cnt
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
SELECT move_order_line_id
, organization_id
, inventory_item_id
, content_lpn_id
, transfer_lpn_id
, wms_task_type
INTO mol_id
, l_org_id
, l_item_id
, l_content_lpn_id
, l_transfer_lpn_id
, l_wms_task_types
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT line_status
INTO line_status
FROM mtl_txn_request_lines
WHERE line_id = mol_id;
, p_quantity_to_delete => l_quantity
);
mydebug(' alloc quantity deleted ' || l_del_quantity);
UPDATE mtl_txn_request_lines
SET quantity_detailed =(quantity_detailed - l_del_quantity)
WHERE line_id = mol_id;
mydebug('updated mol:' || mol_id);
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
mydebug('deleted from wms_dispatched_tasks ');
SELECT COUNT(transaction_temp_id)
INTO cnt
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mol_id;
UPDATE mtl_txn_request_lines
SET line_status = inv_globals.g_to_status_closed
WHERE line_id = mol_id;
mydebug(' updated the mo line status to ' || inv_globals.g_to_status_closed);
SELECT msi.lot_control_code
, msi.serial_number_control_code
INTO v_lot_control_code
, v_serial_control_code
FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT nvl(mp.allocate_serial_flag,'N') /*Bug#4003553.Added NVL function*/
INTO v_allocate_serial_flag
FROM mtl_parameters mp, mtl_material_transactions_temp mmtt
WHERE mp.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
--Bug 2940878 fix added org and item restriction
AND current_organization_id = l_org_id
AND inventory_item_id = l_item_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
--Bug 2940878 fix added org and item restriction
AND current_organization_id = l_org_id
AND inventory_item_id = l_item_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_transaction_temp_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id);
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = NULL
WHERE transaction_temp_id = p_temp_id;
mydebug(' update done ');
UPDATE mtl_material_transactions_temp
SET lpn_id = NULL
, content_lpn_id = NULL
, transfer_lpn_id = NULL
WHERE transaction_temp_id = p_temp_id;
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
mydebug('deleted WDT with temp_id ' || p_temp_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(1)
INTO l_count
FROM mtl_material_transactions_temp
WHERE transfer_lpn_id = l_transfer_lpn_id;
SELECT lpn_context INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_transfer_lpn_id;
SELECT 'E'
INTO l_processed
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_header_id
AND process_flag = 'E');
mydebug('Before the select:');
SELECT 'Y'
INTO l_processed
FROM DUAL
WHERE EXISTS(SELECT transaction_set_id
FROM mtl_material_transactions
WHERE transaction_set_id = p_header_id);
mydebug('After the select: l_processed ' || l_processed);
SELECT *
FROM mtl_material_transactions
WHERE transaction_set_id = p_set_id;
SELECT *
FROM mtl_transaction_lot_numbers
WHERE transaction_id IN(SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_set_id = p_set_id);
SELECT *
FROM mtl_unit_transactions
WHERE transaction_id IN(SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_set_id = p_set_id);
SELECT *
FROM mtl_unit_transactions
WHERE transaction_id IN(SELECT serial_transaction_id
FROM mtl_transaction_lot_numbers
WHERE transaction_id IN(SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_set_id = p_set_id));
SELECT msi.primary_uom_code INTO l_item_uom_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id=l_item_id
AND msi.organization_id=l_org_id;
mmtt_row.last_update_date := mmt_row.last_update_date;
mmtt_row.last_updated_by := mmt_row.last_updated_by;
mmtt_row.last_update_login := mmt_row.last_update_login;
mmtt_row.program_update_date := mmt_row.program_update_date;
SELECT lpn_controlled_flag
INTO l_lpn_control_flag
FROM mtl_secondary_inventories
WHERE organization_id = mmt_row.organization_id
AND secondary_inventory_name = Nvl(mmt_row.transfer_subinventory, mmt_row.subinventory_code);
SELECT lpn_controlled_flag
INTO l_lpn_control_flag
FROM mtl_secondary_inventories
WHERE organization_id = mmt_row.organization_id
AND secondary_inventory_name = Nvl(mmt_row.subinventory_code, mmt_row.transfer_subinventory);
SELECT wlpn.lpn_context INTO l_lpn_ctx
FROM WMS_LICENSE_PLATE_NUMBERS wlpn
WHERE wlpn.lpn_id = mmtt_row.allocated_lpn_id ;
SELECT lot_control_code
, serial_number_control_code
INTO v_lot_control_code
, v_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT allocate_serial_flag
INTO v_allocate_serial_flag
FROM mtl_parameters
WHERE organization_id = l_org_id;
mtlt_row.last_update_date := mtln_row.last_update_date;
mtlt_row.last_updated_by := mtln_row.last_updated_by;
mtlt_row.last_update_login := mtln_row.last_update_login;
msnt_row.last_update_date := mut_row.last_update_date;
msnt_row.last_updated_by := mut_row.last_updated_by;
msnt_row.last_update_login := mut_row.last_update_login;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = txn_tmp_id;
SELECT *
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = txn_tmp_id;
SELECT 'Y'
INTO l_crossdocked
FROM DUAL
WHERE EXISTS(
SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl, mtl_material_transactions mmt
WHERE mtrl.line_id = mmt.move_order_line_id
AND mtrl.backorder_delivery_detail_id IS NOT NULL
AND mmt.transaction_set_id = p_old_header_id);
SELECT mtl_material_transactions_s.NEXTVAL
INTO new_txn_header_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO new_txn_temp_id
FROM DUAL;
SELECT revision_qty_control_code
, lot_control_code
, serial_number_control_code
, primary_uom_code
INTO v_rev_control_code
, v_lot_control_code
, v_serial_control_code
, l_uom
FROM mtl_system_items
WHERE inventory_item_id = mmtt_table(cnt).inventory_item_id
AND organization_id = mmtt_table(cnt).organization_id;
inv_loc_wms_utils.update_loc_sugg_capacity_nauto
( x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => mmtt_row.organization_id
, p_inventory_location_id => mmtt_row.transfer_to_location
, p_inventory_item_id => mmtt_row.inventory_item_id
, p_primary_uom_flag => 'Y'
, p_transaction_uom_code => NULL
, p_quantity => mmtt_row.primary_quantity
);
mydebug('Unexpected error in update_loc_suggested_capacity');
mydebug('Error in update_loc_suggested_capacity');
SELECT reference,reference_type_code,reference_id
INTO l_ref,l_ref_type, l_ref_id
FROM mtl_txn_request_lines
WHERE
line_id = mmtt_row.move_order_line_id;
l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_trohdr_rec.last_update_date := sysdate;
l_trohdr_rec.last_update_login := FND_GLOBAL.USER_ID;
l_trolin_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_tbl(1).last_update_date := sysdate;
l_trolin_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_tbl(1).last_update_date := sysdate;
l_trolin_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT allocate_serial_flag
INTO v_allocate_serial_flag
FROM mtl_parameters
WHERE organization_id = mmtt_table(cnt).organization_id;
inv_rcv_common_apis.insert_mtlt(lot_row);
SELECT 'Y' INTO l_already_used FROM dual WHERE exists
(SELECT 1
FROM mtl_serial_numbers
WHERE
--Bug 2940878 fix added current_organization_id ,
--inventory_item_id in the query
-- also changed the condition on group_mark_id
current_organization_id = mmtt_row.organization_id AND
inventory_item_id = mmtt_row.inventory_item_id AND
serial_number >= ser_row.fm_serial_number AND
serial_number <= ser_row.to_serial_number AND
--group_mark_id IS NOT NULL
Nvl(group_mark_id, -1) <> -1
);
inv_rcv_common_apis.insert_msnt(ser_row);
inv_rcv_common_apis.insert_mtlt(lot_row);
/*Need to insert both lot and serial tables*/
IF (l_debug = 1) THEN
mydebug(' allocate_serial_flag is Y ');
SELECT mtl_material_transactions_s.NEXTVAL
INTO ser_transaction_temp_id
FROM dual;
SELECT 'Y' INTO l_already_used FROM dual WHERE exists
(SELECT 1
FROM mtl_serial_numbers
WHERE
--Bug 2940878 fix added current_organization_id ,
--inventory_item_id in the query
-- also changed the condition on group_mark_id
current_organization_id = mmtt_row.organization_id AND
inventory_item_id = mmtt_row.inventory_item_id AND
serial_number >= ser_row.fm_serial_number AND
serial_number <= ser_row.to_serial_number AND
--group_mark_id IS NOT NULL
Nvl(group_mark_id, -1) <> -1
);
inv_rcv_common_apis.insert_msnt(ser_row);
inv_rcv_common_apis.insert_mtlt(lot_row);
mydebug(' inserting the new row into mmtt using ' || 'wms_task_dispatch_engine.insert_mmtt ');
--Insert records into WMS_DEVICE_REQUESTS TABLE
wms_cartnzn_pub.insert_device_request_rec(mmtt_row);
wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec => mmtt_row);
l_deleted_quantity NUMBER := 0;
SELECT transaction_temp_id, device_request_id
FROM wms_dispatched_tasks
WHERE person_id = p_emp_id
AND(status <= 3 OR status = 9)
AND device_request_id IS NOT NULL;
SELECT mtrl.line_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE (mmtt.transaction_temp_id = p_temp_id OR mmtt.parent_line_id = p_temp_id)
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_id
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id)
AND NOT EXISTS(SELECT 1
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
SELECT count(*)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_id
AND NOT EXISTS ( SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id);
DELETE FROM wms_dispatched_tasks WHERE person_id = p_emp_id AND status IN(3, 9) and transaction_temp_id <> p_temp_id;
update wms_dispatched_tasks set status = 2 where transaction_temp_id = p_temp_id and person_id = p_emp_id;
/* mydebug('Rows update in wdt 3602199' || SQL%ROWCOUNT);*/
DELETE FROM wms_dispatched_tasks where person_id = p_emp_id and status in (3,9);
/* mydebug('All rows deleted from wdt' || SQL%ROWCOUNT);*/
l_deleted_quantity := 0;
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_txn_temp_id
);
mydebug('Not able to delete the Txn = ' || l_txn_temp_id);
l_deleted_quantity := l_deleted_quantity + l_txn_quantity;
UPDATE mtl_txn_request_lines
SET quantity_detailed =(quantity_detailed - l_deleted_quantity)
, line_status = DECODE(l_mmtt_count, 0, INV_GLOBALS.G_TO_STATUS_CLOSED, line_status)
WHERE line_id = l_mo_line_id;
SELECT transfer_lpn_id
INTO l_transfer_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id
AND content_lpn_id = transfer_lpn_id;
SELECT 'Y'
INTO l_multiple_rows
FROM DUAL
WHERE EXISTS(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transfer_lpn_id = l_transfer_lpn_id
AND transaction_temp_id <> p_temp_id);