The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION validate_update_type (
p_update_type IN VARCHAR2)
return NUMBER
IS
CURSOR c_val_update_type
IS
SELECT 1
FROM mfg_lookups
WHERE lookup_code = p_update_type AND
lookup_type = 'INV_UPDATE_TYPE';
OPEN c_val_update_type;
FETCH c_val_update_type INTO l_count;
IF (c_val_update_type%NOTFOUND) THEN
CLOSE c_val_update_type;
CLOSE c_val_update_type;
FND_MESSAGE.SET_NAME('INV','INV_LOTC_UPDATETYPE_INVALID');
END VALIDATE_UPDATE_TYPE;
, p_quantity_updates IN NUMBER
, p_update_type IN VARCHAR2
, p_header_id IN NUMBER DEFAULT NULL
)
return NUMBER
IS
CURSOR get_uom_class (p_uom_code VARCHAR2) IS
SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
, p_update_type_indicator IN NUMBER DEFAULT 5
, p_reason_id IN NUMBER
, p_batch_id IN NUMBER
, p_lot_uom_conv_rec IN OUT NOCOPY mtl_lot_uom_class_conversions%ROWTYPE
, p_qty_update_tbl IN OUT NOCOPY mtl_lot_uom_conv_pub.quantity_update_rec_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_sequence OUT NOCOPY NUMBER
)
IS
GENERIC_ERROR EXCEPTION;
INSERT_ERROR EXCEPTION;
BATCH_UPDATE_ERROR EXCEPTION;
l_update_type varchar2(20);
SELECT conversion_rate, conversion_id
FROM mtl_lot_uom_class_conversions
WHERE organization_id = p_lot_uom_conv_rec.organization_id AND
inventory_item_id = p_lot_uom_conv_rec.inventory_item_id AND
lot_number = p_lot_uom_conv_rec.lot_number AND
from_uom_code = p_lot_uom_conv_rec.from_uom_code AND
to_uom_code = p_lot_uom_conv_rec.to_uom_code;
SELECT MTL_CONV_AUDIT_ID_S.NEXTVAL
FROM FND_DUAL;
SELECT MTL_CONV_AUDIT_DETAIL_ID_S.NEXTVAL
FROM FND_DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
FROM FND_DUAL;
SELECT primary_uom_code, secondary_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE
organization_id = p_lot_uom_conv_rec.organization_id AND
inventory_item_id = p_lot_uom_conv_rec.inventory_item_id;
SELECT mln.*
FROM mtl_lot_numbers mln
WHERE organization_id = p_lot_uom_conv_rec.organization_id
AND inventory_item_id = p_lot_uom_conv_rec.inventory_item_id
AND lot_number = p_lot_uom_conv_rec.lot_number;
SELECT *
FROM mtl_material_transactions mmt
WHERE transaction_source_id = p_batch_id
AND transaction_source_type_id = 5 -- gme_common_pvt.g_txn_source_type
AND NOT EXISTS ( SELECT transaction_id1
FROM gme_transaction_pairs
WHERE transaction_id1 = mmt.transaction_id
AND pair_type = 1) --gme_common_pvt.g_pairs_reversal_type
AND inventory_item_id = p_lot_uom_conv_rec.inventory_item_id
AND organization_id = p_lot_uom_conv_rec.organization_id
AND EXISTS (select 1
From mtl_transaction_lot_numbers
Where transaction_id = mmt.transaction_id
And lot_number = p_lot_uom_conv_rec.lot_number);
SELECT *
FROM mtl_transaction_lot_numbers
WHERE transaction_id = v_transaction_id
AND lot_number = p_lot_uom_conv_rec.lot_number;
l_batch_updated NUMBER := 0;
Insert or update mtl_lot_uom_class_conversions.
==================================================*/
IF (p_action_type = 'I') THEN
l_old_conversion_rate := NULL;
mtl_lot_uom_conv_pkg.insert_row(
p_lot_uom_conv_rec.conversion_id,
p_lot_uom_conv_rec.lot_number,
p_lot_uom_conv_rec.organization_id,
p_lot_uom_conv_rec.inventory_item_id,
p_lot_uom_conv_rec.from_unit_of_measure,
p_lot_uom_conv_rec.from_uom_code,
p_lot_uom_conv_rec.from_uom_class,
p_lot_uom_conv_rec.to_unit_of_measure,
p_lot_uom_conv_rec.to_uom_code,
p_lot_uom_conv_rec.to_uom_class,
p_lot_uom_conv_rec.conversion_rate,
p_lot_uom_conv_rec.disable_date,
p_lot_uom_conv_rec.event_spec_disp_id,
p_lot_uom_conv_rec.created_by,
p_lot_uom_conv_rec.creation_date,
p_lot_uom_conv_rec.last_updated_by,
p_lot_uom_conv_rec.last_update_date,
p_lot_uom_conv_rec.last_update_login,
p_lot_uom_conv_rec.request_id,
p_lot_uom_conv_rec.program_application_id,
p_lot_uom_conv_rec.program_id,
p_lot_uom_conv_rec.program_update_date,
x_return_status,
x_msg_count,
x_msg_data
);
mtl_lot_uom_conv_pkg.update_row(
p_lot_uom_conv_rec.conversion_id,
p_lot_uom_conv_rec.lot_number,
p_lot_uom_conv_rec.organization_id,
p_lot_uom_conv_rec.inventory_item_id,
p_lot_uom_conv_rec.from_unit_of_measure,
p_lot_uom_conv_rec.from_uom_code,
p_lot_uom_conv_rec.from_uom_class,
p_lot_uom_conv_rec.to_unit_of_measure,
p_lot_uom_conv_rec.to_uom_code,
p_lot_uom_conv_rec.to_uom_class,
p_lot_uom_conv_rec.conversion_rate,
p_lot_uom_conv_rec.disable_date,
p_lot_uom_conv_rec.event_spec_disp_id,
p_lot_uom_conv_rec.last_updated_by,
p_lot_uom_conv_rec.last_update_date,
p_lot_uom_conv_rec.last_update_login,
p_lot_uom_conv_rec.request_id,
p_lot_uom_conv_rec.program_application_id,
p_lot_uom_conv_rec.program_id,
p_lot_uom_conv_rec.program_update_date,
x_return_status,
x_msg_count,
x_msg_data
);
RAISE INSERT_ERROR;
IF (p_update_type_indicator IN ('1','2')) THEN
OPEN get_batch_transactions;
SELECT uom_class
INTO l_transaction_uom_class
FROM mtl_units_of_measure_vl
WHERE uom_code = x_batch_txns.transaction_uom;
IF (p_update_type_indicator = 1) THEN
l_old_primary_quantity := l_lot_transactions_tbl(i).primary_quantity;
FOR j in 1..p_qty_update_tbl.count
LOOP
IF (p_qty_update_tbl(j).subinventory_code = x_batch_txns.subinventory_code AND
nvl(p_qty_update_tbl(j).locator_id,-1) = nvl(x_batch_txns.locator_id,nvl(p_qty_update_tbl(j).locator_id,-1)) ) THEN
-- Bug 6317236
-- Commenting this code as the logic is now moved to the form.
/* p_qty_update_tbl(j).transaction_primary_qty := p_qty_update_tbl(j).transaction_primary_qty -
(l_lot_transactions_tbl(i).primary_quantity - l_old_primary_quantity) ;
new_ind := p_qty_update_tbl.count + 1;
p_qty_update_tbl(new_ind).organization_id := p_lot_uom_conv_rec.organization_id;
p_qty_update_tbl(new_ind).subinventory_code := x_batch_txns.subinventory_code;
p_qty_update_tbl(new_ind).locator_id := nvl(x_batch_txns.locator_id,p_qty_update_tbl(j).locator_id);
p_qty_update_tbl(new_ind).old_primary_qty := 0;
p_qty_update_tbl(new_ind).old_secondary_qty := 0;
p_qty_update_tbl(new_ind).new_primary_qty := -1 * l_lot_transactions_tbl(i).primary_quantity ;
p_qty_update_tbl(new_ind).transaction_primary_qty := -1 *
l_lot_transactions_tbl(i).primary_quantity;
p_qty_update_tbl(new_ind).transaction_update_flag := 1;
ELSIF (p_update_type_indicator = 2) THEN
l_old_secondary_quantity := l_lot_transactions_tbl(i).secondary_transaction_quantity;
FOR j in 1..p_qty_update_tbl.count
LOOP
IF (p_qty_update_tbl(j).subinventory_code = x_batch_txns.subinventory_code AND
nvl(p_qty_update_tbl(j).locator_id,-1) = nvl(x_batch_txns.locator_id,nvl(p_qty_update_tbl(j).locator_id,-1)) ) THEN
-- Bug 6317236
-- Commenting this code as the logic is now moved to the form.
/* p_qty_update_tbl(j).transaction_secondary_qty := p_qty_update_tbl(j).transaction_secondary_qty -
(l_lot_transactions_tbl(i).secondary_transaction_quantity - l_old_secondary_quantity) ;
new_ind := p_qty_update_tbl.count + 1;
p_qty_update_tbl(new_ind).organization_id := p_lot_uom_conv_rec.organization_id;
p_qty_update_tbl(new_ind).subinventory_code := x_batch_txns.subinventory_code;
p_qty_update_tbl(new_ind).locator_id := x_batch_txns.locator_id;
p_qty_update_tbl(new_ind).old_primary_qty := 0;
p_qty_update_tbl(new_ind).old_secondary_qty := 0;
p_qty_update_tbl(new_ind).new_secondary_qty := -1 *
l_lot_transactions_tbl(i).secondary_transaction_quantity ;
p_qty_update_tbl(new_ind).transaction_secondary_qty := -1 *
l_lot_transactions_tbl(i).secondary_transaction_quantity; p_qty_update_tbl(new_ind).transaction_update_flag := 1;
gme_transactions_pvt.update_material_txn
(p_mmt_rec => x_batch_txns
,p_mmln_tbl => l_lot_transactions_tbl
,x_return_status => l_return_status);
RAISE BATCH_UPDATE_ERROR;
l_batch_updated := 1;
END IF; -- p_update_type_indicator in 1,2
Insert Audit Record.
tempy check on conversion date.
tempy - event spec disp id.
==================================================*/
OPEN GET_AUDIT_SEQ;
p_lot_uom_conv_rec.created_by := p_lot_uom_conv_rec.last_updated_by;
p_lot_uom_conv_rec.creation_date := p_lot_uom_conv_rec.last_update_date;
mtl_lot_conv_audit_pkg.insert_row (
l_audit_seq,
p_lot_uom_conv_rec.conversion_id,
SYSDATE,
p_update_type_indicator,
p_batch_id,
p_reason_id,
l_old_conversion_rate,
p_lot_uom_conv_rec.conversion_rate,
p_lot_uom_conv_rec.event_spec_disp_id,
p_lot_uom_conv_rec.created_by,
p_lot_uom_conv_rec.creation_date,
p_lot_uom_conv_rec.last_updated_by,
p_lot_uom_conv_rec.last_update_date,
p_lot_uom_conv_rec.last_update_login,
x_return_status,
x_msg_count,
x_msg_data);
RAISE INSERT_ERROR;
l_update_type := 'LOT-CONV-INSERT';
L_UPDATE_TYPE := 'LOT-CONV-UPDATE';
Insert into MTL_UOM_CONVERSIONS_ERES_GTMP (
CONVERSION_TYPE,
INVENTORY_ITEM_ID,
FROM_UOM_CODE,
FROM_UOM_CLASS,
TO_UOM_CODE,
TO_UOM_CLASS,
LOT_NUMBER,
CONVERSION_ID,
CONV_AUDIT_ID)
VALUES
(L_UPDATE_TYPE,
p_lot_uom_conv_rec.inventory_item_id,
p_lot_uom_conv_rec.from_uom_code,
p_lot_uom_conv_rec.from_uom_class,
p_lot_uom_conv_rec.to_uom_code,
p_lot_uom_conv_rec.to_uom_class,
p_lot_uom_conv_rec.lot_number,
p_lot_uom_conv_rec.conversion_id,
l_audit_seq);
Insert transaction if necessary and insert
audit detail record.
=============================================*/
l_ind := 1;
IF (p_qty_update_tbl.EXISTS(l_ind)) THEN
IF (p_qty_update_tbl(l_ind).transaction_update_flag = '1') THEN
IF (p_qty_update_tbl(l_ind).transaction_primary_qty = 0 AND
NVL(p_qty_update_tbl(l_ind).transaction_secondary_qty,0) = 0) THEN
GOTO BYPASS;
IF (p_qty_update_tbl(l_ind).transaction_primary_qty = 0) THEN
IF (p_qty_update_tbl(l_ind).transaction_secondary_qty >= 0) THEN
/*====================
BUG#4320911
====================*/
l_transaction_type_id := 1004;
ELSIF (p_qty_update_tbl(l_ind).transaction_primary_qty >= 0) THEN
l_transaction_type_id := 1004;
Insert to Temp Table.
=============================*/
INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP (
transaction_header_id,
transaction_temp_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
acct_period_id,
organization_id,
inventory_item_id,
primary_quantity,
transaction_quantity,
transaction_uom,
secondary_transaction_quantity,
secondary_uom_code,
transaction_date,
process_flag,
lock_flag,
revision,
lot_number,
subinventory_code,
locator_id,
lpn_id,
last_update_date,
last_updated_by,
created_by,
creation_date)
VALUES (
l_head_seq,
l_temp_seq,
l_transaction_type_id,
l_transaction_action_id,
13,
l_period_id,
p_lot_uom_conv_rec.organization_id,
p_lot_uom_conv_rec.inventory_item_id,
p_qty_update_tbl(l_ind).transaction_primary_qty,
p_qty_update_tbl(l_ind).transaction_primary_qty,
l_primary_uom,
p_qty_update_tbl(l_ind).transaction_secondary_qty,
l_secondary_uom,
SYSDATE,
'Y',
2,
p_qty_update_tbl(l_ind).revision,
p_lot_uom_conv_rec.lot_number,
p_qty_update_tbl(l_ind).subinventory_code,
p_qty_update_tbl(l_ind).locator_id,
p_qty_update_tbl(l_ind).lpn_id,
SYSDATE,
p_lot_uom_conv_rec.last_updated_by,
p_lot_uom_conv_rec.last_updated_by,
SYSDATE
);
Insert to Lot Temp Table.
=============================*/
--Bug#5228919
OPEN c_get_attr;
INSERT INTO MTL_TRANSACTION_LOTS_TEMP (
transaction_temp_id,
lot_number,
primary_quantity,
transaction_quantity,
secondary_quantity,
secondary_unit_of_measure,
last_update_date,
last_updated_by,
created_by,
creation_date,
--Bug#5228919 Begin
reason_id,
grade_code,
maturity_date,
origination_date,
retest_date,
supplier_lot_number,
attribute_category,
lot_attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
c_attribute1,
c_attribute2,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute20,
d_attribute1,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
d_attribute10,
n_attribute1,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
n_attribute10 ) --Bug#5228919 End
VALUES (
l_temp_seq,
p_lot_uom_conv_rec.lot_number,
ABS(p_qty_update_tbl(l_ind).transaction_primary_qty),
ABS(p_qty_update_tbl(l_ind).transaction_primary_qty),
ABS(p_qty_update_tbl(l_ind).transaction_secondary_qty),
l_secondary_uom,
SYSDATE,
p_lot_uom_conv_rec.last_updated_by,
p_lot_uom_conv_rec.last_updated_by,
SYSDATE,
--Bug#5228919 Begin
p_reason_id,
l_lot_rec.grade_code,
l_lot_rec.maturity_date,
l_lot_rec.origination_date,
l_lot_rec.retest_date,
l_lot_rec.supplier_lot_number,
l_lot_rec.attribute_category,
l_lot_rec.lot_attribute_category,
l_lot_rec.attribute1,
l_lot_rec.attribute2,
l_lot_rec.attribute3,
l_lot_rec.attribute4,
l_lot_rec.attribute5,
l_lot_rec.attribute6,
l_lot_rec.attribute7,
l_lot_rec.attribute8,
l_lot_rec.attribute9,
l_lot_rec.attribute10,
l_lot_rec.attribute11,
l_lot_rec.attribute12,
l_lot_rec.attribute13,
l_lot_rec.attribute14,
l_lot_rec.attribute15,
l_lot_rec.c_attribute1,
l_lot_rec.c_attribute2,
l_lot_rec.c_attribute3,
l_lot_rec.c_attribute4,
l_lot_rec.c_attribute5,
l_lot_rec.c_attribute6,
l_lot_rec.c_attribute7,
l_lot_rec.c_attribute8,
l_lot_rec.c_attribute9,
l_lot_rec.c_attribute10,
l_lot_rec.c_attribute11,
l_lot_rec.c_attribute12,
l_lot_rec.c_attribute13,
l_lot_rec.c_attribute14,
l_lot_rec.c_attribute15,
l_lot_rec.c_attribute16,
l_lot_rec.c_attribute17,
l_lot_rec.c_attribute18,
l_lot_rec.c_attribute19,
l_lot_rec.c_attribute20,
l_lot_rec.d_attribute1,
l_lot_rec.d_attribute2,
l_lot_rec.d_attribute3,
l_lot_rec.d_attribute4,
l_lot_rec.d_attribute5,
l_lot_rec.d_attribute6,
l_lot_rec.d_attribute7,
l_lot_rec.d_attribute8,
l_lot_rec.d_attribute9,
l_lot_rec.d_attribute10,
l_lot_rec.n_attribute1,
l_lot_rec.n_attribute2,
l_lot_rec.n_attribute3,
l_lot_rec.n_attribute4,
l_lot_rec.n_attribute5,
l_lot_rec.n_attribute6,
l_lot_rec.n_attribute7,
l_lot_rec.n_attribute8,
l_lot_rec.n_attribute9,
l_lot_rec.n_attribute10
--Bug#5228919 End
);
Insert a audit detail record whether
there was a transaction or not.
======================================*/
<>
OPEN GET_AUD_DET_SEQ;
MTL_LOT_CONV_AUD_DET_PKG.INSERT_ROW(
X_CONV_AUDIT_DETAIL_ID => l_aud_det_seq,
X_CONV_AUDIT_ID => l_audit_seq,
X_REVISION => p_qty_update_tbl(l_ind).revision,
X_ORGANIZATION_ID => p_qty_update_tbl(l_ind).organization_id,
X_SUBINVENTORY_CODE => p_qty_update_tbl(l_ind).subinventory_code,
X_LPN_ID => p_qty_update_tbl(l_ind).lpn_id,
X_LOCATOR_ID => p_qty_update_tbl(l_ind).locator_id,
X_OLD_PRIMARY_QTY => p_qty_update_tbl(l_ind).old_primary_qty,
X_OLD_SECONDARY_QTY => p_qty_update_tbl(l_ind).old_secondary_qty,
X_NEW_PRIMARY_QTY => p_qty_update_tbl(l_ind).new_primary_qty,
X_NEW_SECONDARY_QTY => p_qty_update_tbl(l_ind).new_secondary_qty,
X_TRANSACTION_PRIMARY_QTY => p_qty_update_tbl(l_ind).transaction_primary_qty,
X_TRANSACTION_SECONDARY_QTY => p_qty_update_tbl(l_ind).transaction_secondary_qty,
X_TRANSACTION_UPDATE_FLAG => p_qty_update_tbl(l_ind).transaction_update_flag,
X_CREATED_BY => p_lot_uom_conv_rec.created_by,
X_CREATION_DATE => p_lot_uom_conv_rec.creation_date,
X_LAST_UPDATED_BY => p_lot_uom_conv_rec.last_updated_by,
X_LAST_UPDATE_DATE => p_lot_uom_conv_rec.last_update_date,
X_LAST_UPDATE_LOGIN => p_lot_uom_conv_rec.last_update_login,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WHEN INSERT_ERROR THEN
ROLLBACK;
WHEN BATCH_UPDATE_ERROR THEN
x_return_status := l_return_status;
CONVERSION_INSERT_ERROR EXCEPTION;
SELECT *
FROM mtl_lot_uom_class_conversions
WHERE nvl(disable_date, trunc(sysdate)+1) > trunc(sysdate)
AND organization_id = p_from_organization_id
AND lot_number = p_from_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT reason_id
FROM mtl_transaction_reasons
WHERE reason_name = 'Copy Lot Conversions';
l_qty_update_tbl mtl_lot_uom_conv_pub.quantity_update_rec_type;
SELECT 1
FROM mtl_lot_uom_class_conversions
WHERE organization_id = p_to_organization_id
AND lot_number = p_to_lot_number
AND inventory_item_id = p_inventory_item_id
AND from_uom_code = l_lot_uom_conv_rec.from_uom_code
AND to_uom_code = l_lot_uom_conv_rec.to_uom_code;
Insert Reason Logic here. tempy
============================================*/
l_lot_uom_conv_rec.lot_number := p_to_lot_number;
l_lot_uom_conv_rec.last_updated_by := p_user_id;
l_lot_uom_conv_rec.last_updated_by := l_user_id;
l_lot_uom_conv_rec.last_update_date := l_lot_uom_conv_rec.creation_date;
l_lot_uom_conv_rec.last_update_date := p_creation_date;
Insert the new conversion and audit record.
if conversion does not exist already.
==============================================*/
l_exists_cnt := 0;
l_qty_update_tbl,l_return_status,l_msg_count,l_error_message,l_seq);
RAISE CONVERSION_INSERT_ERROR;
WHEN CONVERSION_INSERT_ERROR THEN
ROLLBACK TO COPY_CONVERSION;
FND_MESSAGE.SET_NAME('INV','INV_LOTC_CONV_INSERT_ERROR');
SELECT 1
FROM mtl_transactions_interface
WHERE source_lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND transaction_type_id NOT IN (95,1004);
SELECT 1
FROM mtl_transactions_interface
WHERE source_lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND transaction_type_id NOT IN (95,1004)
AND transaction_header_id <> p_header_id;
SELECT 1
FROM mtl_material_transactions_temp
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT 1
FROM mtl_material_transactions_temp
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND transaction_header_id <> p_header_id
AND process_flag <> 'E';