[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(sum(primary_transaction_quantity), 0) SYSTEM_QUANTITY
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = itemid
AND organization_id = org
AND subinventory_code = subinv
AND NVL(lot_number, '@') = NVL(lot, '@')
AND NVL(revision, '@') = NVL(rev, '@')
AND NVL(locator_id, 99) = NVL(loc, 99)
AND NVL(cost_group_id, -1) = NVL(cost, -1)
AND NVL(containerized_flag, 2) = 2;
SELECT
NVL(sum(DECODE(msn.current_status, 3, 1, 0)), 0) SYSTEM_QUANTITY
FROM mtl_serial_numbers msn
WHERE msn.serial_number = NVL(ser, serial_number)
AND msn.inventory_item_id = itemid
AND msn.current_organization_id = org
AND msn.current_subinventory_code = subinv
AND NVL(msn.LOT_NUMBER, 'XX') = NVL(lot, 'XX')
AND NVL(msn.REVISION, 'XXX') = NVL(rev, 'XXX')
AND NVL(msn.CURRENT_LOCATOR_ID, -2) = NVL(loc, -2);
SELECT NVL (SUM (primary_transaction_quantity), 0) system_quantity,
NVL (SUM (secondary_transaction_quantity), 0) secondary_system_quantity
FROM mtl_onhand_quantities_detail
WHERE inventory_item_id = itemid
AND organization_id = org
AND subinventory_code = subinv
AND NVL (lot_number, '@') = NVL (lot, '@')
AND NVL (revision, '@') = NVL (rev, '@')
AND NVL (locator_id, 99) = NVL (loc, 99)
AND NVL (cost_group_id, -1) = NVL (COST, -1)
AND NVL (containerized_flag, 2) = 2;
SELECT NVL (SUM (DECODE (msn.current_status, 3, 1, 0)),
0
) system_quantity
FROM mtl_serial_numbers msn
WHERE msn.serial_number = NVL (ser, serial_number)
AND msn.inventory_item_id = itemid
AND msn.current_organization_id = org
AND msn.current_subinventory_code = subinv
AND NVL (msn.lot_number, 'XX') = NVL (lot, 'XX')
AND NVL (msn.revision, 'XXX') = NVL (rev, 'XXX')
AND NVL (msn.current_locator_id, -2) = NVL (loc, -2);
SELECT decode(current_status,6,1,current_status),
revision,
lot_number,
current_subinventory_code,
current_locator_id,
current_organization_id
INTO L_current_status,
L_current_revision,
L_current_lot_number,
L_current_subinventory,
L_current_locator_id,
L_current_organization_id
FROM MTL_SERIAL_NUMBERS
WHERE inventory_item_id = P_Item_id
AND serial_number = P_serial_number;
SELECT 'x'
INTO L_nothing
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = P_serial_number
AND CURRENT_ORGANIZATION_ID + 0 = P_organization_id;
SELECT 'x'
INTO L_nothing
FROM MTL_SERIAL_NUMBERS S,
MTL_PARAMETERS P
WHERE S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
AND S.SERIAL_NUMBER = P_serial_number
AND P.SERIAL_NUMBER_TYPE = 3;
INSERT INTO MTL_SERIAL_NUMBERS
(INVENTORY_ITEM_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INITIALIZATION_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CURRENT_STATUS,
CURRENT_ORGANIZATION_ID)
VALUES
(P_item_id, P_SERIAL_NUMBER, sysdate,
L_user_id, sysdate, sysdate,
L_user_id, -1, 6,P_organization_id);
SELECT 'x'
INTO L_nothing
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = P_serial_number;
MDEBUG( 'CheckSerl - Serl No inserting - 1');
INSERT INTO MTL_SERIAL_NUMBERS
(INVENTORY_ITEM_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INITIALIZATION_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CURRENT_STATUS,
CURRENT_ORGANIZATION_ID)
VALUES
(P_item_id, P_SERIAL_NUMBER, sysdate,
L_user_id, sysdate, sysdate,
L_user_id, -1, 6, P_ORGANIZATION_ID);
MDEBUG( 'CheckSerl - Serl No inserting Except- 1');
MDEBUG( 'CheckSerl - Serl No inserting Except- 2');
SELECT 'x'
INTO L_nothing
FROM MTL_SERIAL_NUMBERS S,
MTL_PARAMETERS P
WHERE S.INVENTORY_ITEM_ID = P_item_id
AND S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
AND S.SERIAL_NUMBER = P_serial_number
AND P.SERIAL_NUMBER_TYPE = 1;
INSERT INTO MTL_SERIAL_NUMBERS
(INVENTORY_ITEM_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INITIALIZATION_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CURRENT_STATUS,
CURRENT_ORGANIZATION_ID)
VALUES
(P_item_id, P_SERIAL_NUMBER, sysdate,
L_user_id, sysdate, sysdate,
L_user_id, -1, 6, P_ORGANIZATION_ID);
MDEBUG( 'CheckSerl - Serl No inserting Except- 4');
MDEBUG( 'CheckSerl - Serl No inserting Except- 4');
SELECT 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 nvl(sum(quantity),0) --BUG3026540
INTO x_lpn_systemqty
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(lot_number, '@') = NVL(p_lot_number, '@')
AND NVL(revision, '@') = NVL(p_revision, '@')
AND NVL(serial_number, '@') = NVL(p_serial_number, '@')
AND NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1);
select nvl(sum(quantity),0)
into l_loaded_sys_qty
from wms_loaded_quantities_v
where nvl(content_lpn_id,nvl(lpn_id,-1)) = p_lpn_id
and inventory_item_id = p_inventory_item_id
and NVL(lot_number, '@') = NVL(p_lot_number, '@')
and NVL(revision, '@') = NVL(p_revision, '@');
SELECT COUNT(*)
INTO x_lpn_systemqty
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = p_serial_number
AND NVL(lot_number, '@') = NVL(p_lot_number, '@')
AND NVL(revision, '@') = NVL(p_revision, '@')
AND NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1)
AND INV_CYC_LOVS.is_serial_loaded(p_organization_id,p_inventory_item_id,p_serial_number,p_lpn_id) = 2;
SELECT 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 NVL (SUM (primary_quantity), 0)
, NVL (SUM (secondary_quantity), 0)
INTO x_lpn_systemqty
, x_lpn_sec_systemqty
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL (lot_number, '@') = NVL (p_lot_number, '@')
AND NVL (revision, '@') = NVL (p_revision, '@')
AND NVL (serial_number, '@') = NVL (p_serial_number, '@')
AND NVL (cost_group_id, -1) = NVL (p_cost_group_id, -1);
SELECT NVL (SUM (quantity), 0)
, NVL (SUM (secondary_quantity), 0)
INTO l_loaded_sys_qty
, l_loaded_sec_sys_qty
FROM wms_loaded_quantities_v
WHERE NVL (content_lpn_id, NVL (lpn_id, -1) ) = p_lpn_id
AND inventory_item_id = p_inventory_item_id
AND NVL (lot_number, '@') = NVL (p_lot_number, '@')
AND NVL (revision, '@') = NVL (p_revision, '@');
SELECT COUNT (*)
INTO x_lpn_systemqty
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = p_serial_number
AND NVL (lot_number, '@') = NVL (p_lot_number, '@')
AND NVL (revision, '@') = NVL (p_revision, '@')
AND NVL (cost_group_id, -1) = NVL (p_cost_group_id, -1)
AND inv_cyc_lovs.is_serial_loaded (p_organization_id
, p_inventory_item_id
, p_serial_number
, p_lpn_id
) = 2;
SELECT inventory_item_id
FROM MTL_CYCLE_COUNT_ENTRIES
WHERE organization_id = p_organization_id
AND parent_lpn_id = p_parent_lpn_id
AND inventory_item_id = p_inventory_item_id
AND NVL(lot_number, '@') = NVL(p_lot_number, '@')
AND NVL(revision, '@') = NVL(p_revision, '@')
AND NVL(serial_number, '@') = NVL(p_serial_number, '@')
AND NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1)
AND entry_status_code IN (1, 3);
SELECT inventory_item_id
FROM MTL_CYCLE_COUNT_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND cycle_count_header_id = p_cc_header_id;
DELETE
FROM MTL_ITEM_BULKLOAD_RECS
WHERE REQUEST_ID = p_request_id;