[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM mtl_uom_classes_tl
WHERE uom_class = p_uom_class;
SELECT APPLICATION_COLUMN_NAME
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE DESCRIPTIVE_FLEXFIELD_NAME = L_DESC_FLEX_NAME
AND ENABLED_FLAG = 'Y'
ORDER BY TO_NUMBER(SUBSTR(APPLICATION_COLUMN_NAME, 10)) ;
SELECT UNIT_OF_MEASURE
INTO L_UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UNIT_OF_MEASURE_TL = P_BASE_UNIT_OF_MEASURE
AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT UOM_CODE
INTO L_UOM_CODE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = P_BASE_UOM_CODE
AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT UNIT_OF_MEASURE,
UOM_CODE
INTO L_UNIT_OF_MEASURE ,
L_UOM_CODE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CLASS = P_UOM_CLASS
AND UNIT_OF_MEASURE_TL = P_BASE_UNIT_OF_MEASURE
AND UOM_CODE = P_BASE_UOM_CODE ;
mtl_uom_classes_tl_pkg.insert_row(x_rowid => l_row_id,
x_uom_class => p_uom_class,
x_uom_class_tl => p_uom_class_tl,
x_description => p_description,
x_disable_date => p_disable_date,
x_attribute_category => p_attribute_category,
x_attribute1 => L_INV_ATTRIBUTES_TBL(1) ,
x_attribute2 => L_INV_ATTRIBUTES_TBL(2) ,
x_attribute3 => L_INV_ATTRIBUTES_TBL(3) ,
x_attribute4 => L_INV_ATTRIBUTES_TBL(4) ,
x_attribute5 => L_INV_ATTRIBUTES_TBL(5) ,
X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
x_attribute8 => L_INV_ATTRIBUTES_TBL(8) ,
X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
x_attribute15 => L_INV_ATTRIBUTES_TBL(15) ,
x_request_id => To_number(NULL),
x_program_id => To_number(NULL),
X_PROGRAM_APPLICATION_ID => TO_NUMBER(NULL),
x_program_update_date => sysdate,
x_creation_date => Sysdate,
x_created_by => L_USERID,
x_last_update_date => Sysdate,
x_last_updated_by => L_USERID,
X_LAST_UPDATE_LOGIN => L_LOGINID
);
INV_LOG_UTIL.TRACE('After inserting uom class, X_RETURN_STATUS:'|| X_RETURN_STATUS, 'inv_uom_api_pub', '9');
MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW(X_ROW_ID => L_ROW_ID,
X_UNIT_OF_MEASURE => P_BASE_UNIT_OF_MEASURE ,
X_UNIT_OF_MEASURE_TL => P_BASE_UNIT_OF_MEASURE,
X_ATTRIBUTE_CATEGORY => P_UOM_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => L_UOM_ATTRIBUTES_TBL(1),
X_ATTRIBUTE2 => L_UOM_ATTRIBUTES_TBL(2),
X_ATTRIBUTE3 => L_UOM_ATTRIBUTES_TBL(3),
X_ATTRIBUTE4 => L_UOM_ATTRIBUTES_TBL(4),
X_ATTRIBUTE5 => L_UOM_ATTRIBUTES_TBL(5),
X_ATTRIBUTE6 => L_UOM_ATTRIBUTES_TBL(6),
X_ATTRIBUTE7 => L_UOM_ATTRIBUTES_TBL(7),
X_ATTRIBUTE8 => L_UOM_ATTRIBUTES_TBL(8),
X_ATTRIBUTE9 => L_UOM_ATTRIBUTES_TBL(9),
X_ATTRIBUTE10 => L_UOM_ATTRIBUTES_TBL(10),
X_ATTRIBUTE11 => L_UOM_ATTRIBUTES_TBL(11),
X_ATTRIBUTE12 => L_UOM_ATTRIBUTES_TBL(12),
X_ATTRIBUTE13 => L_UOM_ATTRIBUTES_TBL(13),
X_ATTRIBUTE14 => L_UOM_ATTRIBUTES_TBL(14),
X_ATTRIBUTE15 => L_UOM_ATTRIBUTES_TBL(15),
X_REQUEST_ID => To_number(NULL),
X_DISABLE_DATE => P_DISABLE_DATE,
X_BASE_UOM_FLAG => 'Y',
X_UOM_CODE => P_BASE_UOM_CODE,
X_UOM_CLASS => P_UOM_CLASS,
X_DESCRIPTION => P_UOM_DESCRIPTION,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => L_USERID,
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => L_USERID,
X_LAST_UPDATE_LOGIN => L_LOGINID,
X_PROGRAM_APPLICATION_ID => TO_NUMBER(NULL),
X_PROGRAM_ID => TO_NUMBER(NULL),
X_PROGRAM_UPDATE_DATE => SYSDATE );
INSERT
INTO MTL_UOM_CONVERSIONS
(
INVENTORY_ITEM_ID,
UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CONVERSION_RATE,
DEFAULT_CONVERSION_FLAG
)
VALUES
(
0,
P_BASE_UNIT_OF_MEASURE,
P_BASE_UOM_CODE,
P_UOM_CLASS,
SYSDATE,
L_USERID , --fnd_global.user_id,
SYSDATE,
L_USERID, -- fnd_global.user_id,
L_LOGINID,
1,
'N'
);
INSERT
INTO MTL_UOM_CLASS_CONVERSIONS
(
INVENTORY_ITEM_ID,
FROM_UNIT_OF_MEASURE,
FROM_UOM_CODE,
FROM_UOM_CLASS,
TO_UNIT_OF_MEASURE,
TO_UOM_CODE,
TO_UOM_CLASS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CONVERSION_RATE
)
VALUES
(
0,
P_BASE_UNIT_OF_MEASURE,
P_BASE_UOM_CODE,
P_UOM_CLASS,
P_BASE_UNIT_OF_MEASURE,
P_BASE_UOM_CODE,
P_UOM_CLASS,
SYSDATE,
L_USERID , -- fnd_global.user_id,
SYSDATE,
L_USERID , -- fnd_global.user_id,
L_LOGINID,
1
);
MTL_UOM_CLASSES_TL_PKG.UPDATE_ROW ( X_UOM_CLASS => P_UOM_CLASS,
X_UOM_CLASS_TL => P_UOM_CLASS_TL,
X_DESCRIPTION => P_DESCRIPTION,
x_disable_date => p_disable_date,
x_attribute_category => p_attribute_category,
x_attribute1 => L_INV_ATTRIBUTES_TBL(1) ,
x_attribute2 => L_INV_ATTRIBUTES_TBL(2) ,
x_attribute3 => L_INV_ATTRIBUTES_TBL(3) ,
x_attribute4 => L_INV_ATTRIBUTES_TBL(4) ,
x_attribute5 => L_INV_ATTRIBUTES_TBL(5) ,
X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
x_attribute8 => L_INV_ATTRIBUTES_TBL(8) ,
X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
x_attribute15 => L_INV_ATTRIBUTES_TBL(15) ,
x_request_id => To_number(NULL),
x_last_update_date => Sysdate,
x_last_updated_by => L_USERID,
X_LAST_UPDATE_LOGIN => L_LOGINID
);
INV_LOG_UTIL.TRACE('After update uom class , X_RETURN_STATUS:'|| X_RETURN_STATUS, 'inv_uom_api_pub', '9');
SELECT APPLICATION_COLUMN_NAME
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE DESCRIPTIVE_FLEXFIELD_NAME = L_DESC_FLEX_NAME
AND ENABLED_FLAG = 'Y'
ORDER BY TO_NUMBER(SUBSTR(APPLICATION_COLUMN_NAME, 10)) ;
SELECT UNIT_OF_MEASURE , UOM_CODE , BASE_UOM_FLAG
INTO L_UNIT_OF_MEASURE , L_UOM_CODE , L_BASE_UOM_FLAG
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CLASS = P_UOM_CLASS
AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
AND (UNIT_OF_MEASURE_TL = P_UNIT_OF_MEASURE
OR UOM_CODE = P_UOM_CODE) ;
SELECT 1
into l_class_count
FROM MTL_UOM_CLASSES
WHERE UOM_CLASS = P_UOM_CLASS
and NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT UNIT_OF_MEASURE , UOM_CODE
INTO L_UNIT_OF_MEASURE , L_UOM_CODE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UNIT_OF_MEASURE_TL = P_UNIT_OF_MEASURE
-- AND uom_code = p_uom_code
OR UOM_CODE = P_UOM_CODE
AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW(X_ROW_ID => L_ROW_ID,
X_UNIT_OF_MEASURE => P_UNIT_OF_MEASURE ,
X_UNIT_OF_MEASURE_TL => P_UNIT_OF_MEASURE,
X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => L_INV_ATTRIBUTES_TBL(1) ,
X_ATTRIBUTE2 => L_INV_ATTRIBUTES_TBL(2) ,
X_ATTRIBUTE3 => L_INV_ATTRIBUTES_TBL(3) ,
X_ATTRIBUTE4 => L_INV_ATTRIBUTES_TBL(4) ,
X_ATTRIBUTE5 => L_INV_ATTRIBUTES_TBL(5) ,
X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
X_ATTRIBUTE8 => L_INV_ATTRIBUTES_TBL(8) ,
X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
X_ATTRIBUTE15 => l_inv_attributes_tbl(15) ,
X_REQUEST_ID => TO_NUMBER(NULL),
X_DISABLE_DATE => P_DISABLE_DATE,
X_BASE_UOM_FLAG => L_BASE_UOM_FLAG,
X_UOM_CODE => P_UOM_CODE,
X_UOM_CLASS => P_UOM_CLASS,
X_DESCRIPTION => P_DESCRIPTION,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => L_USERID ,
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => L_USERID ,
X_LAST_UPDATE_LOGIN => L_LOGINID ,
X_PROGRAM_APPLICATION_ID => TO_NUMBER(NULL),
X_PROGRAM_ID => TO_NUMBER(NULL),
X_PROGRAM_UPDATE_DATE => SYSDATE );
ELSIF p_action_type = 'U' THEN -- object type is update
MTL_UNITS_OF_MEASURE_TL_PKG.UPDATE_ROW (X_UNIT_OF_MEASURE => P_UNIT_OF_MEASURE ,
X_UNIT_OF_MEASURE_TL => P_UNIT_OF_MEASURE ,
X_UOM_CODE => P_UOM_CODE ,
X_UOM_CLASS => P_UOM_CLASS,
X_BASE_UOM_FLAG => L_BASE_UOM_FLAG,
X_DESCRIPTION => P_DESCRIPTION ,
X_DISABLE_DATE => P_DISABLE_DATE ,
X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => L_INV_ATTRIBUTES_TBL(1) ,
X_ATTRIBUTE2 => L_INV_ATTRIBUTES_TBL(2) ,
X_ATTRIBUTE3 => L_INV_ATTRIBUTES_TBL(3) ,
X_ATTRIBUTE4 => L_INV_ATTRIBUTES_TBL(4) ,
X_ATTRIBUTE5 => L_INV_ATTRIBUTES_TBL(5) ,
X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
X_ATTRIBUTE8 => L_INV_ATTRIBUTES_TBL(8) ,
X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
X_ATTRIBUTE15 => L_INV_ATTRIBUTES_TBL(15) ,
X_REQUEST_ID => TO_NUMBER(NULL),
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => L_USERID ,
X_LAST_UPDATE_LOGIN => L_LOGINID
);
P_ONHAND_UPDATE IN NUMBER,
P_UPDATE_TYPE IN NUMBER,
p_action_type IN VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
IS
CURSOR GET_NON_BATCH_TXNS
IS
SELECT 1
FROM dual
WHERE EXISTS
(SELECT mtln.transaction_id
FROM mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt
WHERE mtln.transaction_id = mmt.transaction_id
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = p_org_id
AND mtln.lot_number = p_lot_number
AND ( (mmt.transaction_source_type_id NOT IN (13, 5))
OR (mmt.transaction_action_id NOT IN ( 1 , 27 , 2 , 3 , 31 , 32)) )
);
SELECT DISTINCT b.batch_no,
d.line_type,
b.batch_status,
b.batch_id,
b.parentline_id,
d.phantom_id,
D.DTL_UM,
t.opm_costed_flag
FROM gme_batch_header b,
gme_material_details d,
mtl_material_transactions t,
mtl_transaction_lot_numbers l
WHERE t.inventory_item_id = p_item_id
AND t.organization_id = p_org_id
AND T.TRANSACTION_SOURCE_TYPE_ID = 5
AND NOT EXISTS
(SELECT transaction_id1
FROM gme_transaction_pairs
WHERE transaction_id1 = t.transaction_id
AND pair_type = 1
)
AND l.transaction_id = t.transaction_id
AND l.lot_number = p_lot_number
AND d.material_detail_id = t.trx_source_line_id
AND d.batch_id = t.transaction_source_id
AND B.BATCH_ID = D.BATCH_ID
ORDER BY b.parentline_id;
SELECT REVISION,
SUBINVENTORY_CODE,
LPN_ID,
LOCATOR_ID,
on_hand,
secondary_onhand
FROM mtl_lot_onhand_sum_v
WHERE inventory_item_id = p_item_id
AND lot_number = p_lot_number
AND ORGANIZATION_ID = P_ORG_ID
ORDER BY organization_code,
revision,
lpn,
subinventory_code ,
LOCATOR;
SELECT SUM(mtln.primary_quantity),
SUM(mtln.secondary_transaction_quantity)
FROM mtl_material_transactions mmt ,
mtl_transaction_lot_numbers mtln
WHERE mmt.transaction_source_id = L_BATCH_ID
AND mmt.transaction_source_type_id = 5
AND NOT EXISTS
(SELECT transaction_id1
FROM gme_transaction_pairs
WHERE transaction_id1 = mmt.transaction_id
AND pair_type = 1
)
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = p_org_id
AND mmt.transaction_id = mtln.transaction_id
AND MTLN.LOT_NUMBER = P_LOT_NUMBER;
SELECT * FROM GME_BATCH_HEADER WHERE BATCH_ID = NVL(X_PHANTOM_ID, X_BATCH_ID);
SELECT *
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = X_BATCH_ID
AND LINE_TYPE = X_LINE_TYPE
and inventory_item_id = P_ITEM_ID;
SELECT UOM_CLASS
FROM MTL_UNITS_OF_MEASURE
WHERE uom_code = v_uom;
L_QTY_TBL MTL_LOT_UOM_CONV_PUB.QUANTITY_UPDATE_REC_TYPE;
SELECT count(*)
into l_qr
FROM MTL_RESERVATIONS
WHERE INVENTORY_ITEM_ID = P_ITEM_ID ;
SELECT count(*)
into L_PENDING_COUNT
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE INVENTORY_ITEM_ID = P_ITEM_ID;
SELECT UNIT_OF_MEASURE , UOM_CLASS , BASE_UOM_FLAG
INTO L_FROM_UNIT_OF_MEASURE , L_FROM_CLASS , P_FROM_BASE_UOM_FLAG
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = P_FROM_UOM_CODE
AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT UNIT_OF_MEASURE , UOM_CLASS , BASE_UOM_FLAG
INTO L_TO_UNIT_OF_MEASURE , L_TO_CLASS , P_TO_BASE_UOM_FLAG
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = P_TO_UOM_CODE
AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
UPDATE MTL_UOM_CONVERSIONS
SET DISABLE_DATE = P_DISABLE_DATE
WHERE INVENTORY_ITEM_ID = NVL(P_ITEM_ID,0)
AND UOM_CODE = P_TO_UOM_CODE;
UPDATE MTL_UOM_CLASS_CONVERSIONS
SET DISABLE_DATE = P_DISABLE_DATE
WHERE INVENTORY_ITEM_ID = NVL(P_ITEM_ID,0)
AND FROM_UOM_CODE = P_FROM_UOM_CODE
AND TO_UOM_CODE = P_TO_UOM_CODE;
SELECT 1
INTO L_COUNT
FROM MTL_UOM_CONVERSIONS
WHERE INVENTORY_ITEM_ID = nvl(P_ITEM_ID,0)
AND UOM_CODE = P_TO_UOM_CODE ;
UPDATE MTL_UOM_CONVERSIONS
SET CONVERSION_RATE = P_UOM_RATE,
DISABLE_DATE = P_DISABLE_DATE
WHERE INVENTORY_ITEM_ID = nvl(P_ITEM_ID,0)
AND UOM_CODE = P_TO_UOM_CODE ;
SELECT 1
INTO L_COUNT
FROM MTL_UOM_CLASS_CONVERSIONS
WHERE INVENTORY_ITEM_ID = P_ITEM_ID
AND FROM_UOM_CODE = P_FROM_UOM_CODE
AND TO_UOM_CODE = P_TO_UOM_CODE ;
UPDATE MTL_UOM_CLASS_CONVERSIONS
SET CONVERSION_RATE = P_UOM_RATE,
DISABLE_DATE = P_DISABLE_DATE
WHERE INVENTORY_ITEM_ID = P_ITEM_ID
AND FROM_UOM_CODE = P_FROM_UOM_CODE
AND TO_UOM_CODE = P_TO_UOM_CODE ;
SELECT 1
INTO l_ORG_EXISTS
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = P_ORG_ID;
SELECT 1
INTO l_ITEM_EXISTS
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT PRIMARY_UOM_CODE,
SECONDARY_UOM_CODE,
SECONDARY_DEFAULT_IND,
TRACKING_QUANTITY_IND,
LOT_DIVISIBLE_FLAG
INTO l_primary_uom_code,
l_secondary_uom_code,
l_secondary_def_ind,
l_tracking_quantity_ind,
l_lot_divisible_flag
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = p_item_id
AND ORGANIZATION_ID = P_ORG_ID;
SELECT UOM_CODE , UOM_CLASS
INTO l_uom_code , l_from_uom_class
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = P_FROM_UOM_CODE
AND base_uom_flag = 'Y'
AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT UOM_CODE
INTO L_PRIMARY_UOM_CODE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = L_PRIMARY_UOM_CODE
and uom_class = l_from_uom_class
AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT uom_code
INTO l_uom_code
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = P_TO_UOM_CODE
AND BASE_UOM_FLAG = 'Y'
AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
SELECT LOT_CONTROL_CODE , SHELF_LIFE_CODE
INTO V_LOT_CONTROL_CODE , l_shelf_life_code
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = P_ITEM_ID
AND ORGANIZATION_ID = P_ORG_ID;
SELECT LOT_NUMBER
INTO l_lot_number
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT LOT_NUMBER
INTO L_LOT_NUMBER
FROM MTL_LOT_NUMBERS
WHERE LOT_NUMBER = P_LOT_NUMBER
AND INVENTORY_ITEM_ID = P_ITEM_ID
AND ORGANIZATION_ID = P_ORG_ID
AND EXPIRATION_DATE > SYSDATE;
SELECT COUNT(1)
INTO L_COUNT
FROM MTL_LOT_UOM_CLASS_CONVERSIONS
WHERE ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID = P_ITEM_ID
AND LOT_NUMBER = P_LOT_NUMBER
AND TO_UOM_CODE = P_TO_UOM_CODE ;
SELECT 1 --COUNT(1)
INTO l_count
FROM mtl_lot_uom_class_conversions
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND LOT_NUMBER = P_LOT_NUMBER
AND TO_UOM_CODE = P_TO_UOM_CODE
AND from_uom_code = P_FROM_UOM_CODE ;
SELECT 1
into l_reason_count
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = P_REASON_ID
AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE;
IF P_ONHAND_UPDATE = 2 THEN
BEGIN
IF ( P_UPDATE_TYPE NOT IN (1, 2, 3, 4) OR P_ONHAND_UPDATE NOT IN (1, 2) ) THEN
IF (L_DEBUG = 1) THEN
INV_LOG_UTIL.TRACE('Invalid Update type ', 'inv_uom_api_pub', '9');
FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_UPDATETYPE_INVALID');
IF (P_UPDATE_TYPE IN (1, 2)) THEN
IF (L_TRACKING_QUANTITY_IND <> 'PS') THEN
FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_TYPE0_ERROR');
ELSIF (P_UPDATE_TYPE IN (3, 4)) THEN
IF (L_TRACKING_QUANTITY_IND = 'P') THEN
FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_TYPE_NOT_ALLOWED');
l_qty_tbl(l_temp).transaction_update_flag := 1;
IF ( P_UPDATE_TYPE = 4 OR P_UPDATE_TYPE = 2 ) THEN
-- calculate sec qty based on conversion.
NEW_PRIMARY_QTY := L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY;
IF (P_UPDATE_TYPE = 3 OR P_UPDATE_TYPE = 4 ) THEN
TRANSACTION_PRIMARY_QTY := (NEW_PRIMARY_QTY - L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY);
IF (P_UPDATE_TYPE = 1 OR P_UPDATE_TYPE = 2) THEN
OPEN BATCH_TXNS;
IF (P_UPDATE_TYPE = 1) THEN
TRANSACTION_PRIMARY_QTY := (P_UOM_RATE * (L_QTY_TBL(L_TEMP).OLD_SECONDARY_QTY - X_BATCH_QTY2)) - (L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY - X_BATCH_QTY1);
ELSIF (P_UPDATE_TYPE = 2) THEN
TRANSACTION_SECONDARY_QTY := ((L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY - X_BATCH_QTY1) / P_UOM_RATE) - (L_QTY_TBL(L_TEMP).OLD_SECONDARY_QTY - X_BATCH_QTY2);
SELECT 1
INTO L_PENDING_COUNT
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE MMTT.INVENTORY_ITEM_ID = P_ITEM_ID
AND MMTT.ORGANIZATION_ID = P_ORG_ID
AND EXISTS( SELECT 1 FROM MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
AND MTLT.LOT_NUMBER = L_LOT_NUMBER
);
ELSIF P_ONHAND_UPDATE NOT IN (1, 2) THEN
FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
FND_MESSAGE.SET_TOKEN('VALUE1', 'P_ONHAND_UPDATE');
SELECT UNIT_OF_MEASURE , UOM_CLASS
INTO L_FROM_UNIT_OF_MEASURE , L_FROM_UOM_CLASS
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = P_FROM_UOM_CODE;
SELECT UNIT_OF_MEASURE , UOM_CLASS
INTO L_TO_UNIT_OF_MEASURE , L_TO_UOM_CLASS
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = P_TO_UOM_CODE;
SELECT MTL_CONVERSION_ID_S.NEXTVAL
INTO L_CONVERSION_ID
FROM FND_DUAL;
l_conv_rec.last_updated_by := L_USERID ;
L_CONV_REC.LAST_UPDATE_DATE := SYSDATE;
l_conv_rec.program_update_date := SYSDATE;
P_UPDATE_TYPE_INDICATOR => P_UPDATE_TYPE ,
p_reason_id => p_reason_id ,
P_BATCH_ID => L_BATCH_ID ,
P_PROCESS_DATA => 'Y' , --'N' ,
P_LOT_UOM_CONV_REC => L_CONV_REC , -- pl/sql table of lot uom conversion record.
P_QTY_UPDATE_TBL => L_QTY_TBL , -- pl/sql table of onhand.
X_RETURN_STATUS => L_RETURN_STATUS ,
X_MSG_COUNT => L_MSG_COUNT ,
x_msg_data => l_msg_data ,
X_SEQUENCE => L_SEQUENCE );