[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id, inventory_item_id,
primary_uom_code, secondary_uom_code, secondary_default_ind,
lot_control_code, tracking_quantity_ind, dual_uom_deviation_low,
dual_uom_deviation_high, enabled_flag, shippable_item_flag,
inventory_item_flag, lot_divisible_flag, container_item_flag,
reservable_type, mtl_transactions_enabled_flag, 'Y' valid_flag
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = v_organization_id
AND inventory_item_id = v_inventory_item_id;
SELECT STOCK_LOCATOR_CONTROL_CODE,
NEGATIVE_INV_RECEIPT_CODE,
SERIAL_NUMBER_TYPE
FROM MTL_PARAMETERS
WHERE organization_id = v_organization_id;
SELECT locator_type
FROM MTL_SUBINVENTORIES_TRK_VAL_V
WHERE organization_id = v_organization_id
AND secondary_inventory_name = v_subinventory;
SELECT *
INTO p_item
FROM MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = p_org.organization_id
AND INVENTORY_ITEM_ID = p_item.inventory_item_id;
SELECT DECODE(msi.location_control_code, 1, 'N',
'Y') loc_control_flag,
DECODE(msi.lot_control_code, 2, DECODE(v_pickable_flag, 'N', 'O',
'Y'),
3, 'N',
'N') lot_control_flag,
DECODE(msi.revision_qty_control_code, 2, DECODE(v_pickable_flag, 'N', 'O',
'Y'),
'N') rev_control_flag,
DECODE(msi.serial_number_control_code,2, 'Y', -- PreDefined
5, 'Y', -- At Receipt
6, 'D', -- AT SO Issue
'N') serial_control_flag,
msi.restrict_locators_code,
msi.restrict_subinventories_code,
msi.serial_number_control_code,
msi.location_control_code,
-- HW OPMCONV. No need for item_no
-- msi.segment1 ,
-- msi.container_item_flag,
msi.reservable_type,
msi.MTL_TRANSACTIONS_ENABLED_FLAG -- Bug 3599363
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.inventory_item_id = v_inventory_item_id
AND msi.organization_id = v_organization_id;
SELECT inventory_item_id
, organization_id
, subinventory
, container_flag
, pickable_flag
, source_code
, locator_id --Bug#13949115
, lpn_id --Bug#13949115
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT locator_type
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = cv_subinventory
AND msi.organization_id = cv_organization_id;
SELECT subinventory_code
INTO l_subinv
FROM wms_license_plate_numbers
WHERE lpn_id= l_lpn_id
AND organization_id= l_org_id;
SELECT container_flag
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT mtlsub.secondary_inventory_name
FROM mtl_item_sub_defaults mtlisd,
mtl_secondary_inventories mtlsub
WHERE mtlisd.inventory_item_id = p_inv_item_id
AND mtlisd.organization_id = p_org_id
AND mtlisd.default_type = 1
AND mtlsub.organization_id = mtlisd.organization_id
AND mtlsub.secondary_inventory_name = mtlisd.subinventory_code
AND mtlsub.quantity_tracked = 1
AND trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate));
SELECT mtldl.locator_id
FROM mtl_item_loc_defaults mtldl
WHERE mtldl.inventory_item_id = p_inv_item_id
and mtldl.organization_id = p_organization_id
and mtldl.default_type = 1
and mtldl.subinventory_code = p_subinventory
and ( nvl(p_loc_restricted_flag, 'N') = 'N'
OR
(nvl(p_loc_restricted_flag, 'N') = 'Y'
and nvl(mtldl.locator_id, -1) in
(select mtlsls.secondary_locator
from mtl_secondary_locators mtlsls
where mtlsls.organization_id = p_organization_id
and mtlsls.inventory_item_id = p_inv_item_id
and mtlsls.subinventory_code = p_subinventory)));
SELECT Inventory_Item_Id, Organization_Id,source_code,source_header_id,source_line_id --RTV changes
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT Fm_Serial_Number, To_Serial_Number,
Serial_Prefix
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT mtl_material_transactions_s.nextval
FROM dual;
SELECT Fm_Serial_Number, To_Serial_Number,
Serial_Prefix
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE transaction_temp_id = p_transaction_temp_id
FOR UPDATE OF fm_serial_number NOWAIT;
SELECT inventory_item_id,organization_id,source_code
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
DELETE FROM mtl_serial_numbers
WHERE inventory_item_id = l_inventory_item_id
AND current_status = 6
AND (group_mark_id IS NULL or group_mark_id = -1);
DELETE FROM MTL_SERIAL_NUMBERS_TEMP
WHERE transaction_temp_id = p_transaction_temp_id;
DELETE FROM mtl_serial_numbers
WHERE inventory_item_id = l_inventory_item_id
AND current_status = 6
AND (group_mark_id IS NULL or group_mark_id = -1);
PROCEDURE : Update_Locator_Subinv
PARAMETERS : p_organization_id - organization id for the delivery detail
p_locator_id - locator id for the delivery detail
-1 if dynamic insert and 1 if pre-defined.
p_subinventory - subinventory for the delivery detail
x_return_status - return status of the API
DESCRIPTION : This procedure takes in the inventory location id (locator id),
subinventory and org for the delivery detail and validates if
the locator id exists for the given organization and location.
If it can find it then it raises a duplicate locator exception,
else it updates the mtl item locations table with the
input subinventory for the given locator id and organization.
-----------------------------------------------------------------------------
*/
PROCEDURE Update_Locator_Subinv (
p_organization_id IN NUMBER,
p_locator_id IN NUMBER,
p_subinventory IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Check_Dup_Loc IS
SELECT 'Exist'
FROM Mtl_Item_Locations
WHERE organization_id = p_organization_id
AND inventory_location_id = p_locator_id
AND subinventory_code IS NOT NULL
AND subinventory_code <> p_subinventory;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_LOCATOR_SUBINV';
UPDATE Mtl_Item_Locations
SET subinventory_code = p_subinventory
WHERE organization_id = p_organization_id
AND inventory_location_id = p_locator_id;
WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Update_Locator_Subinv');
END Update_Locator_Subinv;
SELECT sum (serial_prefix)
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE transaction_temp_id = nvl(v_trx_temp_id,transaction_temp_id);
SELECT transaction_temp_id, inventory_item_id, shipped_quantity,
organization_id, serial_number
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_delivery_detail_id;
select nvl(dd.source_document_type_id, -9999) source_document_type_id, dd.source_code, dd.po_shipment_line_id
from wsh_delivery_details dd
where dd.delivery_detail_id = c_delivery_detail_id;
-1 if dynamic insert and 1 if pre-defined.
p_subinventory - subinventory for the delivery detail
p_quantity - Amount of quantitiy to be shipped
p_transaction_type_id
p_object_type
x_prefix - The prefix of serial number
x_return_status - return status of the API
x_result - The result of the API
DESCRIPTION : This procedure takes in the from_serial number and to_serial number
and validates if the serial numbers fall in Range and range is equal
to the given quantity.It also checks if the serial numbers falling
in the range are predefined for the item.
-----------------------------------------------------------------------------
*/
PROCEDURE Validate_Serial_Range(
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_revision IN VARCHAR2,
p_locator_id IN NUMBER,
p_quantity IN NUMBER,
p_transaction_type_id IN NUMBER DEFAULT NULL,
p_object_type IN VARCHAR2 DEFAULT NULL,
x_prefix OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_result OUT NOCOPY BOOLEAN)
IS
l_result NUMBER;
select nvl(dd.source_document_type_id, -9999) source_document_type_id, dd.source_code, dd.po_shipment_line_id
from wsh_delivery_details dd
where dd.delivery_detail_id = c_delivery_detail_id;
PROCEDURE : Update_Marked_Serial
PARAMETERS : p_from_serial_number - serial number to be marked with new
transaction_temp_id
p_to_serial_number - to serial number
p_inventory_item_id - inventory item
p_organization_id - organization_id
p_transaction_temp_id - newly generated transaction temp id
for serial number
x_return_status - return status of the API
DESCRIPTION : Call Inventory's update_marked_serial API which will take
serial number and new transaction_temp_id as input and
mark the serial number with the new transaction_temp_id
-----------------------------------------------------------------------------
*/
PROCEDURE Update_Marked_Serial (
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2 DEFAULT NULL,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_transaction_temp_id IN NUMBER,
p_delivery_detail_id IN NUMBER, --RTV changes
x_return_status OUT NOCOPY VARCHAR2)
IS
--
l_success BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_MARKED_SERIAL';
SELECT source_code,source_header_id,source_line_id --RTV changes
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_delivery_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit SERIAL_CHECK.INV_UPDATE_MARKED_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
Serial_Check.Inv_Update_Marked_Serial (
from_serial_number => p_from_serial_number,
to_serial_number => p_to_serial_number, -- this should be NULL for single serial
item_id => p_inventory_item_id,
org_id => p_organization_id,
temp_id => p_transaction_temp_id,
hdr_id => l_source_header_id,
lot_temp_id => l_source_line_id,
success => l_success);
Serial_Check.Inv_Update_Marked_Serial (
from_serial_number => p_from_serial_number,
to_serial_number => p_to_serial_number, -- this should be NULL for single serial
item_id => p_inventory_item_id,
org_id => p_organization_id,
temp_id => p_transaction_temp_id,
hdr_id => NULL,
lot_temp_id => NULL,
success => l_success);
WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Update_Marked_Serial');
END Update_Marked_Serial;
SELECT source_document_type_id, source_document_id, source_document_line_id
FROM oe_order_lines_all
WHERE line_id = c_order_line_id;
SELECT destination_type_code,
destination_subinventory,
source_organization_id,
destination_organization_id,
deliver_to_location_id,
pl.requisition_line_id,
pd.distribution_id,
pl.unit_price,
nvl(pd.budget_account_id,-1) budget_account_id,
decode(nvl(pd.prevent_encumbrance_flag,'N'),'N',nvl(pd.encumbered_flag,'N'),'N') encumbered_flag
FROM po_requisition_lines_all pl,
po_req_distributions_all pd
WHERE pl.requisition_line_id = c_po_line_id
AND pl.requisition_header_id = c_source_document_id
AND pl.requisition_line_id = pd.requisition_line_id;
select po_shipment_line_id from wsh_delivery_details
where source_line_id = p_source_line_id
and source_code = p_source_code;
SELECT intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = c_from_organization_id AND
to_organization_id = c_to_organization_id;