[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(Decode(uom_code,
p_uom_code,
quantity,
inv_convert.inv_um_convert (p_inventory_item_id,
5,
quantity,
uom_code,
p_uom_code,
NULL,
NULL
)
))
INTO x_total_qty
FROM wms_lpn_contents
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND parent_lpn_id = p_lpn_id
AND Nvl(revision,'#$%') = Nvl(p_revision,'#$%');
SELECT lpn_content_id
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND NVL(revision, '###') = NVL(p_revision, '###')
AND NVL(lot_number, '###') = NVL(p_lot_number, '###')
FOR UPDATE NOWAIT;
SELECT mtrl.line_id,
mtrl.quantity - NVL(mtrl.quantity_delivered, 0),
mtrl.uom_code,
--laks
mtrl.secondary_quantity - NVL(mtrl.secondary_quantity_delivered, 0),
mtrl.secondary_uom_code
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = p_lpn_id
AND mtrl.inventory_item_id = p_inventory_item_id
AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
ORDER BY 2 DESC;
SELECT mtrl.line_id
, Nvl(mtrl.inspection_status,0) inspection_status
, quantity-Nvl(quantity_delivered,0) avail_qty
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = p_lpn_id
AND mtrl.inventory_item_id = p_inventory_item_id
AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
AND ((l_lpn_context = 3) OR
(l_lpn_context = 1 AND l_mo_line_count = 0))
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
ORDER BY mtrl.inspection_status;
SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = p_lpn_id
AND mtrl.inventory_item_id = p_inventory_item_id
AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
AND l_lpn_context = 2
AND mtrl.reference_id IN (SELECT header_id
FROM wip_lpn_completions_serials
WHERE l_current_serial BETWEEN fm_serial_number AND
to_serial_number
AND NVL(lot_number, '###') = NVL(p_lot_number, '###'))
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away;
SELECT serial_number
, Nvl(inspection_status,0) inspection_status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND NVL(revision, '###') = NVL(p_revision, '###')
AND NVL(lot_number, '###') = NVL(p_lot_number, '###')
AND lpn_id = p_lpn_id
AND group_mark_id = p_serial_txn_temp_id
AND EXISTS (SELECT 1
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_txn_temp_id
AND serial_number BETWEEN fm_serial_number AND
to_serial_number)
ORDER BY Nvl(inspection_status,0),LPAD(serial_number, 20);
SELECT wlcs.fm_serial_number
FROM wip_lpn_completions_serials wlcs, mtl_serial_numbers msn
WHERE l_lpn_context = 2
AND NVL(wlcs.lot_number, '###') = NVL(p_lot_number, '###')
AND wlcs.header_id IN (SELECT reference_id
FROM mtl_txn_request_lines
WHERE line_id = l_mo_line_id
AND organization_id = p_organization_id)
AND wlcs.fm_serial_number = msn.serial_number
AND msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND NVL(msn.revision, '###') = NVL(p_revision, '###')
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.lpn_id = p_lpn_id
AND msn.group_mark_id = p_serial_txn_temp_id
AND EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_serial_txn_temp_id
AND msn.serial_number BETWEEN msnt.fm_serial_number AND
msnt.to_serial_number);
SELECT mtl_material_transactions_s.NEXTVAL INTO p_txn_header_id FROM dual;
SELECT lpn_context, NVL(subinventory_code, '###'), NVL(locator_id, -999)
INTO l_lpn_context, l_subinv_code, l_locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id
AND organization_id = p_organization_id;
SELECT NVL(subinventory_code, '###'), NVL(locator_id, -999)
INTO l_tosubinv_code, l_tolocator_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id = p_into_lpn_id;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT COUNT(*)
INTO l_mo_line_count
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = p_lpn_id
AND mtrl.inventory_item_id = p_inventory_item_id
AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away;
SELECT NVL(project_id, -999), NVL(task_id, -999)
INTO l_project_id, l_task_id
FROM mtl_item_locations
WHERE inventory_location_id = l_locator_id
AND organization_id = p_organization_id
AND subinventory_code = l_subinv_code;
SELECT DISTINCT NVL(cost_group_id, -999)
INTO l_cost_group_id
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '###') = NVL(p_revision, '###')
AND NVL(lot_number, '###') = NVL(p_lot_number, '###')
AND ROWNUM = 1;
print_debug('Updated the move order line entry in the table: ' ||
l_table_index);
-- Update the index whenever we insert a new entry
-- into the MOL table
l_index := l_index + 1;
-- Update the index whenever we insert a new entry
-- into the MOL table
l_index := l_index + 1;
print_debug('Updated the move order line entry in the table: ' ||l_table_index);
-- Update the index whenever we insert a new entry
-- into the MOL table
l_index := l_index + 1;
-- Update the index whenever we insert a new entry
-- into the MOL table
l_index := l_index + 1;
l_mol_ser_tb.DELETE(j);
l_mo_split_tb.DELETE;
SELECT NVL(crossdock_flag, 2) cdock
INTO l_cdock_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT backorder_delivery_detail_id
, to_subinventory_code
, to_locator_id
INTO l_backorder_delivery_detail_id
, l_to_sub_code
, l_to_loc_id
FROM mtl_txn_request_lines
WHERE line_id = l_tmp_mo_lines_tb(l_index).line_id;
FOR l_splitted_rec IN (SELECT line_id
, primary_quantity
FROM mtl_txn_request_lines
WHERE reference_detail_id = l_tmp_mo_lines_tb(l_index).line_id) LOOP
IF (l_debug = 1) THEN
print_debug('Splitted Line:'||l_splitted_rec.line_id);
UPDATE mtl_txn_request_lines
SET reference_detail_id = NULL
WHERE line_id = l_splitted_rec.line_id;
print_debug('For each move order line, insert a dummy MMTT record');
print_debug('For each move order line, insert a dummy MMTT record');
print_debug('Receiving LPN so update wms_process_flag on the MOL');
UPDATE mtl_txn_request_lines
SET wms_process_flag = 2
WHERE line_id = l_mo_lines_tb(l_index).line_id
AND organization_id = p_organization_id;
print_debug('Successfully updated wms_process_flag for MOL');
SELECT NVL(txn_source_id, -999), NVL(project_id, -999), NVL(task_id, -999)
INTO l_txn_src_id, l_project_id, l_task_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND line_id = l_mo_lines_tb(l_index).line_id;
l_return := inv_trx_util_pub.insert_line_trx
(p_trx_hdr_id => p_txn_header_id,
p_item_id => p_inventory_item_id,
p_revision => p_revision,
p_org_id => p_organization_id,
p_trx_action_id => l_trx_action_id,
p_subinv_code => l_subinv_code,
p_tosubinv_code => l_tosubinv_code,
p_locator_id => l_locator_id,
p_tolocator_id => l_tolocator_id,
p_xfr_org_id => l_xfr_org_id,
p_trx_type_id => l_trx_type_id,
p_trx_src_type_id => l_trx_src_type_id,
p_trx_qty => l_trx_qty,
p_pri_qty => l_mo_lines_tb(l_index).prim_qty,
p_uom => p_uom_code,
--laks
p_secondary_trx_qty => l_mo_lines_tb(l_index).sec_qty,
p_secondary_uom => l_sec_uom_code,
p_date => SYSDATE,
p_user_id => p_user_id,
p_cost_group => NULL,
p_from_lpn_id => p_lpn_id,
p_cnt_lpn_id => NULL,
p_xfr_lpn_id => p_into_lpn_id,
p_trx_src_id => l_txn_src_id,
x_trx_tmp_id => l_txn_temp_id,
x_proc_msg => x_msg_data,
p_xfr_cost_group => NULL,
p_project_id => l_project_id,
p_task_id => l_task_id,
p_move_order_line_id => l_mo_lines_tb(l_index).line_id,
p_posting_flag => 'N');
print_debug('Successfully inserted MMTT record: ' || l_txn_temp_id);
print_debug('Error occurred while calling inv_trx_util_pub.insert_line_trx');
UPDATE mtl_material_transactions_temp
SET wms_task_type = 2
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id = p_organization_id;
print_debug('Successfully updated wms_task_type for dummy MMTT record');
print_debug('Insert a record into MTLT for lot: ' || p_lot_number);
l_return := inv_trx_util_pub.insert_lot_trx
(p_trx_tmp_id => l_txn_temp_id,
p_user_id => p_user_id,
p_lot_number => p_lot_number,
p_trx_qty => l_trx_qty,
p_pri_qty => l_mo_lines_tb(l_index).prim_qty,
--laks
p_secondary_qty => l_mo_lines_tb(l_index).sec_qty,
p_secondary_uom => l_sec_uom_code,
x_ser_trx_id => l_ser_trx_id,
x_proc_msg => x_msg_data);
print_debug('Successfully inserted MTLT record');
print_debug('Error occurred while calling inv_trx_util_pub.insert_lot_trx');
-- Update the MTLT record to clear out the serial_transaction_temp_id column
-- since insert_lot_trx by default will insert a value for it.
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = NULL
WHERE transaction_temp_id = l_txn_temp_id;
-- serial transaction temp ID when inserting MTLT records
IF (p_lot_number IS NULL) THEN
l_ser_trx_id := l_txn_temp_id;
print_debug('Insert MSNT records for WIP');
print_debug('Insert a record into MSNT for serial: ' || l_current_serial);
l_return := inv_trx_util_pub.insert_ser_trx
(p_trx_tmp_id => l_ser_trx_id,
p_user_id => p_user_id,
p_fm_ser_num => l_current_serial,
p_to_ser_num => l_current_serial,
p_quantity => 1,
x_proc_msg => x_msg_data);
print_debug('Successfully inserted MSNT record');
print_debug('Error occurred while calling inv_trx_util_pub.insert_ser_trx');
print_debug('Insert MSNT records for RCV/INV');
print_debug('Insert a record into MSNT for serial: ' || l_current_serial);
l_return := inv_trx_util_pub.insert_ser_trx
(p_trx_tmp_id => l_ser_trx_id,
p_user_id => p_user_id,
p_fm_ser_num => l_current_serial,
p_to_ser_num => l_current_serial,
p_quantity => 1,
x_proc_msg => x_msg_data);
print_debug('Successfully inserted MSNT record');
print_debug('Error occurred while calling inv_trx_util_pub.insert_ser_trx');
print_debug('Finished inserting dummy MMTT records for all MOLs');
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id
AND transaction_header_id <> p_txn_header_id
AND NOT (transaction_source_type_id = 13 AND
transaction_type_id IN (89, 2) AND
transaction_action_id IN (52, 2))
AND move_order_line_id = l_mo_line_id;
SELECT DISTINCT move_order_line_id
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND transaction_header_id = p_txn_header_id
AND (transaction_source_type_id = 13 AND
transaction_type_id IN (89, 2) AND
transaction_action_id IN (52, 2))
AND move_order_line_id IS NOT NULL
ORDER BY move_order_line_id ASC;
SELECT mmtt.transaction_temp_id,
mmtt.inventory_item_id,
mmtt.revision,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_quantity,
mmtt.transaction_uom,
-- laks
mmtt.secondary_transaction_quantity,
mmtt.secondary_uom_code,
NVL(mmtt.transfer_subinventory, mmtt.subinventory_code),
NVL(mmtt.transfer_to_location, mmtt.locator_id),
mmtt.cost_group_id,
mmtt.lpn_id,
mmtt.transfer_lpn_id,
mtlt.lot_number,
mtlt.serial_transaction_temp_id
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transaction_header_id = p_txn_header_id
AND (mmtt.transaction_source_type_id = 13 AND
mmtt.transaction_type_id IN (89, 2) AND
mmtt.transaction_action_id IN (52, 2))
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+);
SELECT fm_serial_number, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_ser_trx_id;
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT NVL(transaction_source_type_id, -999),
NVL(transaction_type_id, -999)
INTO l_txn_source_type_id, l_txn_type_id
FROM mtl_txn_request_lines
WHERE line_id = l_mo_line_id;
l_mo_lines_tb.DELETE;
UPDATE RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_MODE_CODE = 'ONLINE'
WHERE GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
SELECT serial_number_control_code, lot_control_code
INTO l_serial_number_ctrl_code, l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = p_organization_id;
-- The dummy MMTT records are always inserted with the
-- item's primary UOM code for the transaction UOM.
IF (l_debug = 1) THEN
print_debug('Unpack the serials from the source LPN');
-- Delete the dummy MMTT/MTLT/MSNT records once we have processed them
IF (l_ser_trx_id IS NOT NULL) THEN
IF (l_debug = 1) THEN
print_debug('Delete the dummy MSNT records: ' || l_ser_trx_id);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_ser_trx_id;
print_debug('Delete the dummy MTLT records: ' || l_mmtt_temp_id);
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_mmtt_temp_id;
print_debug('Delete the dummy MMTT records: ' || l_mmtt_temp_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt_temp_id;
print_debug('Update the LPN context for Into LPN');
UPDATE wms_license_plate_numbers
SET lpn_context = 2
WHERE lpn_id = p_into_lpn_id
AND organization_id = p_organization_id;
print_debug('Successfully updated LPN context for Into LPN');
print_debug('Need to update the LPN ID and complete the operations');
print_debug('Query the move order lines table to update the LPN ID');
SELECT subinventory_code
, locator_id
INTO l_into_sub
, l_into_loc
FROM wms_license_plate_numbers
WHERE lpn_id = p_into_lpn_id;
-- Update the LPN ID in the MOL record
IF (l_debug = 1) THEN
print_debug('Update the LPN ID/SUB/LOC on MOL record');
UPDATE mtl_txn_request_lines
SET lpn_id = p_into_lpn_id
, from_subinventory_code = Decode(p_lpn_context,
WMS_CONTAINER_PUB.lpn_context_inv,
l_into_sub,
from_subinventory_code)
, from_locator_id = Decode(p_lpn_context,
WMS_CONTAINER_PUB.lpn_context_inv,
l_into_loc,
from_locator_id)
WHERE line_id = l_mo_line_id
AND organization_id = p_organization_id;
print_debug('Successfully updated LPN ID/SUB/LOC on MOL record');
-- Update the LPN ID and Allocated LPN ID in the MMTT record
IF (l_debug = 1) THEN
print_debug('Update the LPN ID/sub/loc on MMTT record');
UPDATE mtl_material_transactions_temp
SET lpn_id = p_into_lpn_id
, allocated_lpn_id = p_into_lpn_id
, subinventory_code = Decode(p_lpn_context,
WMS_CONTAINER_PUB.lpn_context_inv,
l_into_sub,
subinventory_code)
, locator_id = Decode(p_lpn_context,
WMS_CONTAINER_PUB.lpn_context_inv,
l_into_loc,
locator_id)
WHERE transaction_temp_id = l_mmtt_temp_id
AND organization_id = p_organization_id;
print_debug('Successfully updated LPN ID/sub/loc on MMTT record');
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE current_organization_id = p_organization_id
AND group_mark_id = p_serial_txn_temp_id
AND EXISTS (SELECT 1
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_txn_temp_id
AND serial_number BETWEEN fm_serial_number AND
to_serial_number);
print_debug('Delete the temporary MSNT records');
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_txn_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND group_mark_id = p_serial_txn_temp_id
AND EXISTS (SELECT 1
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_txn_temp_id
AND serial_number BETWEEN fm_serial_number AND
to_serial_number);
print_debug('Delete the temporary MSNT records');
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_txn_temp_id;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl,
wms_dispatched_tasks wdt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id
AND wdt.task_type = 2
AND wdt.organization_id = p_organization_id
AND wdt.move_order_line_id = mtrl.line_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 7
AND mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = p_lpn_id;
-- Non-receiving case so call delete_dispatched_task
IF (l_debug = 1) THEN
print_debug('Call delete_dispatched_task for MMTT: ' || l_mmtt_temp_id);
wms_op_runtime_pvt_apis.delete_dispatched_task
(p_source_task_id => l_mmtt_temp_id,
p_wms_task_type => WMS_GLOBALS.G_WMS_TASK_TYPE_PUTAWAY,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('Finished calling the delete_dispatched_task API');
-- Check to see if the delete_dispatched_task API returned successfully
IF (x_return_status = fnd_api.g_ret_sts_success) THEN
IF (l_debug = 1) THEN
print_debug('Success returned from delete_dispatched_task API');
print_debug('Failure returned from delete_dispatched_task API');