[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
Select inventory_item_id,
description,
Revision_qty_control_code,
lot_control_code,
serial_number_control_code,
restrict_locators_code,
location_control_code,
restrict_subinventories_code
INTO l_Item_Info
FROM MTL_SYSTEM_ITEMS_KFV
WHERE concatenated_segments = p_Concatenated_Segments and
organization_id = p_Organization_Id and
mtl_transactions_enabled_flag = 'Y';
Select current_locator_id,
current_subinventory_code,
revision,
lot_number
INTO l_SN_Info
FROM MTL_SERIAL_NUMBERS
WHERE inventory_item_id = p_Inventory_Item_Id and
current_organization_id = p_Current_Organization_Id and
serial_number = p_Serial_Number;
SELECT expiration_date INTO x_Expiration_Date
FROM mtl_lot_numbers
WHERE lot_number = x_Lot_Number AND
inventory_item_id = p_Inventory_Item_Id AND
organization_id = p_Current_Organization_Id;
SELECT concatenated_segments INTO x_Concatenated_Segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = x_Current_Locator_Id AND
organization_id = p_Current_Organization_Id;
SELECT current_status INTO l_curr_stat
FROM mtl_serial_numbers
WHERE inventory_item_id = p_Inventory_Item_Id AND
current_organization_id = p_Current_Organization_Id AND
serial_number = p_Serial_Number;
SELECT negative_inv_receipt_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT cost_group_id,transaction_source_type_id, subinventory_code, locator_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select count(*)
from mtl_onhand_quantities_detail
-- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and subinventory_code = p_subinventory_code
and locator_id = p_locator_id
and nvl(lot_number, '###') = nvl(p_lot_number, nvl(lot_number,'###'))
and containerized_flag =2
and cost_group_id <> x_cost_group_id;
select lpn_id
from wms_license_plate_numbers
where outermost_lpn_id = p_lpn_id
and organization_id = p_org_id;
select count(*)
into l_cnt
from mtl_reservations
where lpn_id = l_lpn_id
and organization_id = p_org_id;
select lpn_id
from wms_license_plate_numbers
where outermost_lpn_id = p_lpn_id
and organization_id = p_org_id;
select count(*)
into l_cnt
from mtl_material_transactions_temp
where allocated_lpn_id = l_lpn_id
and organization_id = p_org_id;
select lpn_id
from wms_license_plate_numbers
where outermost_lpn_id = p_lpn_id
and organization_id = p_org_id;
select serial_number, inventory_item_id
from mtl_serial_numbers
where lpn_id = p_lpnid
and current_organization_id = p_org_id;
select 1
into l_cnt
from mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
where mmtt.organization_id = p_org_id
and mmtt.inventory_item_id = l_inventory_item_id
and mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
and l_serial_number between msnt.fm_serial_number and nvl(msnt.to_serial_number,msnt.fm_serial_number)
and msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id);
select 1
into l_cnt
from mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
where mmtt.organization_id = p_org_id
and mmtt.inventory_item_id = p_item_id
and mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
and p_serial_number between msnt.fm_serial_number and nvl(msnt.to_serial_number,msnt.fm_serial_number)
and msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id);
select transaction_action_id
into l_action_id
From mtl_transaction_types
where transaction_type_id = p_transaction_Type_id
And transaction_Source_Type_id = p_source_type_id;
select count(wdd.delivery_detail_id)
into l_count
From wsh_delivery_details wdd, wms_license_plate_numbers wlpn
WHere wdd.lpn_id = wlpn.lpn_id
and wlpn.lpn_context = wms_Container_pub.LPN_Context_Picked
and wlpn.lpn_id = p_lpn_id
and wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT wlc.inventory_item_id,
wlc.parent_lpn_id, -- lpn reservation change
--SUM(wlc.quantity) sumqty, lpn reservation change
wlc.revision,
wlpn.lpn_context, wlc.lot_number
FROM wms_lpn_contents wlc,
wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_organization_id
AND wlc.parent_lpn_id = wlpn.lpn_id
GROUP BY wlc.parent_lpn_id, wlc.inventory_item_id, wlc.revision, wlpn.lpn_context,wlc.lot_number ;
SELECT DISTINCT subinventory_code, locator_id
INTO l_subinventory_code, l_locator_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT revision_qty_control_code ,
serial_number_control_code,
lot_control_code
INTO l_revison_control_code,
l_serial_number_control_code,
l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_organization_id;
/*need to update qty tree */
inv_quantity_tree_pub.update_quantities
( 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_organization_id => p_organization_id
, p_inventory_item_id => l_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => l_is_revision_control
, p_is_lot_control => l_is_lot_control
, p_is_serial_control => l_is_serial_control
, p_demand_source_type_id => p_source_type_id
, p_revision => l_revision
, p_lot_number => l_lot_number
, p_subinventory_code => l_subinventory_code
, p_locator_id => l_locator_id
, p_primary_quantity => l_updt_qoh
, p_quantity_type => inv_quantity_tree_pvt.g_qoh
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => l_parent_lpn_id --added for lpn reservation
);
SELECT wlc.inventory_item_id,
wlc.parent_lpn_id, -- lpn reservation change
-- SUM(wlc.quantity) sumqty,
wlc.revision,
wlpn.lpn_context, wlc.lot_number
FROM wms_lpn_contents wlc,
wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_organization_id
AND wlc.parent_lpn_id = wlpn.lpn_id
GROUP BY wlc.parent_lpn_id, wlc.inventory_item_id, wlc.revision, wlpn.lpn_context,wlc.lot_number ;
SELECT DISTINCT subinventory_code, locator_id
INTO l_subinventory_code, l_locator_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT revision_qty_control_code ,
serial_number_control_code,
lot_control_code
INTO l_revison_control_code,
l_serial_number_control_code,
l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_organization_id;
/*need to update qty tree */
inv_quantity_tree_pub.update_quantities
( 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_organization_id => p_organization_id
, p_inventory_item_id => l_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => l_is_revision_control
, p_is_lot_control => l_is_lot_control
, p_is_serial_control => l_is_serial_control
, p_demand_source_type_id => p_source_type_id
, p_revision => l_revision
, p_lot_number => l_lot_number
, p_subinventory_code => l_subinventory_code
, p_locator_id => l_locator_id
, p_primary_quantity => l_updt_qoh
, p_quantity_type => inv_quantity_tree_pvt.g_qoh
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_transfer_subinventory_code => p_to_subinventory_code
, p_lpn_id => l_parent_lpn_id --added for lpn reservation
);
SELECT SUM(quantity)
INTO x_lpn_onhand
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 (p_lot_number IS NULL OR lot_number = p_lot_number)
AND (p_revision IS NULL OR revision = p_revision);
SELECT SUM(quantity)
INTO x_lpn_onhand
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 (p_lot_number IS NULL OR lot_number = p_lot_number)
AND (p_revision IS NULL OR revision = p_revision);
SELECT SUM(quantity)
INTO x_lpn_onhand
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 (p_lot_number IS NULL OR lot_number = p_lot_number)
AND (p_revision IS NULL OR revision = p_revision);
select transaction_temp_id, allocated_lpn_id
from mtl_material_transactions_temp
where organization_id = p_org_id
and inventory_item_id = p_item_id
and nvl(revision,'@@@') = nvl(p_rev, nvl(revision,'@@@'));
select 1
into l_serial_exist
from mtl_serial_numbers_temp
where transaction_temp_id = l_transaction_temp_id
and p_serial between fm_serial_number and nvl(to_serial_number, fm_serial_number);
select 1
into l_serial_exist
from mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
where mtlt.transaction_temp_id = l_transaction_temp_id
and mtlt.lot_number = p_lot
and msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
and p_serial between msnt.fm_serial_number and nvl(msnt.to_serial_number, msnt.fm_serial_number);
select lot_number,
SUM(inv_decimals_pub.get_primary_quantity(p_org_id,inventory_item_id,uom_code,Nvl(quantity,0))) primary_quantity
from wms_lpn_contents
where inventory_item_id = p_item_id
and parent_lpn_id = p_lpn_id
and ((revision = p_revision and p_revision is not null) or
(p_revision is null and revision is null))
and source_name in ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING')
and organization_id = p_org_id
group by lot_number;
select sum(quantity)
from wms_lpn_contents
where inventory_item_id = p_item_id
and parent_lpn_id = p_lpn_id
and ((revision = p_revision and p_revision is not null) or
(p_revision is null and revision is null))
and source_name in ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING')
and organization_id = p_org_id;
SELECT restrict_locators_code
INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org;
SELECT a.inventory_location_id
, a.concatenated_segments
, a.description
FROM mtl_item_locations_kfv a, mtl_secondary_locators b
WHERE b.organization_id = l_org
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
AND a.concatenated_segments LIKE (p_concatenated_segments||'%')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY a.concatenated_segments;
SELECT inventory_location_id
, concatenated_segments
, description
FROM mtl_item_locations_kfv
WHERE organization_id = l_org
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND concatenated_segments LIKE (p_concatenated_segments||'%')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY concatenated_segments;
SELECT restrict_locators_code
INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_org;
SELECT a.inventory_location_id
, inv_project.get_locsegs(a.inventory_location_id,l_org)
, NVL(a.description, -1)
FROM mtl_item_locations a, mtl_secondary_locators b
WHERE b.organization_id = l_org
AND b.inventory_item_id = p_inventory_item_id
AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND b.subinventory_code = p_subinventory_code
AND a.inventory_location_id = b.secondary_locator
AND inv_project.get_locsegs(a.inventory_location_id, l_org) LIKE (p_concatenated_segments||'%')
AND NVL(a.project_id, -1) = NVL(p_project_id, -1)
AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT inventory_location_id
, inv_project.get_locsegs(inventory_location_id, l_org)
, description
FROM mtl_item_locations
WHERE organization_id = l_org
AND subinventory_code = p_subinventory_code
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND inv_project.get_locsegs(inventory_location_id, l_org) LIKE (p_concatenated_segments||'%')
AND NVL(project_id, -1) = NVL(p_project_id, -1)
AND NVL(task_id, -1) = NVL(p_task_id, -1)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
ORDER BY 2;
SELECT reservation_id, group_mark_id INTO l_reservation_id,
l_group_mark_id FROM mtl_serial_numbers WHERE
serial_number = p_serial_number AND inventory_item_id = p_inventory_item_id;
inv_quantity_tree_pub.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 => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_tree_mode => l_tree_mode,
p_is_revision_control => l_is_revision_control,
p_is_lot_control => l_is_lot_control,
p_is_serial_control => TRUE,
p_demand_source_type_id => p_source_type_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_primary_quantity => -1,
p_quantity_type => l_quantity_type,
p_onhand_source => l_onhand_source,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr);
FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
inv_quantity_tree_pub.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 => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_tree_mode => l_tree_mode,
p_is_revision_control => l_is_revision_control,
p_is_lot_control => l_is_lot_control,
p_is_serial_control => TRUE,
p_demand_source_type_id => p_source_type_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_subinventory_code => p_to_subinventory_code,
p_locator_id => p_to_locator_id,
p_primary_quantity => 1,
p_quantity_type => l_quantity_type,
p_onhand_source => l_onhand_source,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr);
FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
update mtl_serial_numbers
set group_mark_id = 1
where inventory_item_id = p_inventory_item_id
and serial_number = p_serial_number;
inv_log_util.trace('Updated Serial ' || p_serial_number || ' Item ID ' || p_inventory_item_id, 'process_serial_subxfr');
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO x_transaction_header_id
FROM dual;
inv_log_util.trace('Calling Insert MMTT', 'process_serial_subxfr');
inv_trx_util_pub.insert_line_trx
(p_trx_hdr_id => x_transaction_header_id,
p_item_id => p_inventory_item_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_revision => p_revision,
p_org_id => p_organization_id,
p_trx_action_id => p_transaction_action_id,
p_subinv_code => p_subinventory_code,
p_tosubinv_code => p_to_subinventory_code,
p_locator_id => p_locator_id,
p_tolocator_id => p_to_locator_id,
p_xfr_org_id => p_organization_id,
p_trx_type_id => p_transaction_type_id,
p_trx_src_type_id => p_source_type_id,
p_trx_qty => 1,
p_pri_qty => 1,
p_uom => p_primary_uom_code,
p_date => Sysdate,
p_reason_id => p_reason_id,
p_user_id => p_user_id,
x_trx_tmp_id => l_transaction_temp_id,
x_proc_msg => l_proc_msg);
inv_log_util.trace('SQL Error while inserting MTTT: ' || Sqlerrm, 'process_serial_subxfr');
inv_log_util.trace('Inserted MMTT record', 'process_serial_subxfr');
inv_trx_util_pub.insert_lot_trx
(p_trx_tmp_id => l_transaction_temp_id,
p_user_id => p_user_id,
p_lot_number => p_lot_number,
p_trx_qty => 1,
p_pri_qty => 1,
x_ser_trx_id => l_serial_transaction_temp_id,
x_proc_msg => l_proc_msg);
inv_log_util.trace('Inserted MTLT record', 'process_serial_subxfr');
inv_trx_util_pub.insert_ser_trx
(p_trx_tmp_id => l_transaction_temp_id,
p_user_id => p_user_id,
p_fm_ser_num => p_serial_number,
p_to_ser_num => p_serial_number,
x_proc_msg => l_proc_msg);
inv_log_util.trace('Inserted MSNT record', 'process_serial_subxfr');
SELECT negative_inv_receipt_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT cost_group_id,transaction_source_type_id, subinventory_code, locator_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select lpn_id
from wms_license_plate_numbers
where outermost_lpn_id = p_lpn_id
and organization_id = p_org_id;
select count(1)
into l_count
from mtl_material_transactions_temp mmtt
where (mmtt.lpn_id = l_lpn_id or mmtt.content_lpn_id = l_lpn_id)
and mmtt.organization_id = p_org_id;