[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT UNIQUE msn.serial_number,
msn.current_subinventory_code,
msn.current_locator_id,
msn.lot_number,
0,
msn.current_status,
mms.status_code
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3)
AND msn.status_id = mms.status_id(+)
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
-- added for bug 13691739 ends
ORDER BY LPAD(msn.serial_number, 20);
SELECT UNIQUE msn.serial_number,
msn.current_subinventory_code,
msn.current_locator_id,
msn.lot_number,
0,
msn.current_status,
mms.status_code
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.group_mark_id = 1
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3)
AND msn.status_id = mms.status_id(+)
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
AND NVL ( mcce.number_of_counts , 0 ) = NVL ( mcsn.number_of_counts , 0 ) -- Bug 4533713
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
-- added for bug 13691739 ends
ORDER BY LPAD(msn.serial_number, 20);
UPDATE mtl_serial_numbers
SET group_mark_id = -1
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number IN
(SELECT UNIQUE msn.serial_number
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3)
AND msn.status_id = mms.status_id(+)
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
-- added for bug 13691739 ends
);
SELECT COUNT(*)
INTO l_exist_temp
FROM DUAL
WHERE EXISTS
(SELECT 'multiple-serial'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number IN
(SELECT UNIQUE msn.serial_number
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3, 4) -- For bug 14144558, added 4 in the list and commented below check
--OR (msn.last_txn_source_type_id in (9,10) AND msn.current_status = 4)) --Bug# 3595723 Bug11875440
AND msn.status_id = mms.status_id(+)
AND msn.serial_number = p_serial_number
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
-- added for bug 13691739 ends
));
UPDATE mtl_serial_numbers
SET group_mark_id = 1
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = p_serial_number;
SELECT COUNT(*)
INTO l_exist_temp
FROM DUAL
WHERE EXISTS
(SELECT 'multiple-serial'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number IN
(SELECT UNIQUE msn.serial_number
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3, 4) -- For bug 14144558, added 4 in the list
AND msn.status_id = mms.status_id(+)
AND msn.serial_number = p_serial_number
AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
-- added for bug 13691739 ends
));
UPDATE mtl_serial_numbers
SET group_mark_id = -1
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = p_serial_number;
UPDATE mtl_serial_numbers
SET group_mark_id = 1
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number IN
(SELECT UNIQUE msn.serial_number
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3)
AND msn.status_id = mms.status_id(+)
AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND NVL(mcsn.unit_status_current,-1) <> 2 -- bug 13511103
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
-- added for bug 13691739 ends
);
UPDATE mtl_serial_numbers
SET group_mark_id = 1
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number IN
(SELECT UNIQUE msn.serial_number
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3)
AND msn.status_id = mms.status_id(+)
AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND NVL(mcsn.unit_status_current,-1) <> 2
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.subinventory = msn.current_subinventory_code -- Bug 13481846 addition
AND NVL(mcce.locator_id, -99999) = NVL(msn.current_locator_id, -99999) -- Bug 13481846 addition
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
)
);
print_debug('Number of rows updated in MTL_SERIAL_NUMBERS: ' || SQL%ROWCOUNT);
SELECT COUNT(*)
INTO x_number
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.group_mark_id = 1
AND msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3)
AND msn.status_id = mms.status_id(+)
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- added for bug 13691739 starts
AND NOT EXISTS (SELECT 1
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.wms_task_status = 4
AND mmtt.wms_task_type <> 2
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number);
SELECT 'exists'
INTO l_temp_buf
FROM mtl_Serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id;
/* Inserts the serial number into mtl_cc_Serial_numbers if its new, else updates the same */
PROCEDURE insert_serial_number
(p_serial_number IN VARCHAR2 ,
p_cycle_count_header_id IN NUMBER,
p_organization_id IN NUMBER ,
p_subinventory IN VARCHAR2 ,
p_locator_id IN NUMBER := NULL ,
p_inventory_item_id IN NUMBER ,
p_revision IN VARCHAR2 := NULL ,
p_lot_number IN VARCHAR2 := NULL ,
p_parent_lpn_id IN NUMBER := NULL
-- Adding for bug#9959346
, p_serial_attribute_category IN VARCHAR2 := NULL
, p_orgination_date IN DATE := NULL
, p_c_attribute1 IN VARCHAR2 := NULL
, p_c_attribute2 IN VARCHAR2 := NULL
, p_c_attribute3 IN VARCHAR2 := NULL
, p_c_attribute4 IN VARCHAR2 := NULL
, p_c_attribute5 IN VARCHAR2 := NULL
, p_c_attribute6 IN VARCHAR2 := NULL
, p_c_attribute7 IN VARCHAR2 := NULL
, p_c_attribute8 IN VARCHAR2 := NULL
, p_c_attribute9 IN VARCHAR2 := NULL
, p_c_attribute10 IN VARCHAR2 := NULL
, p_c_attribute11 IN VARCHAR2 := NULL
, p_c_attribute12 IN VARCHAR2 := NULL
, p_c_attribute13 IN VARCHAR2 := NULL
, p_c_attribute14 IN VARCHAR2 := NULL
, p_c_attribute15 IN VARCHAR2 := NULL
, p_c_attribute16 IN VARCHAR2 := NULL
, p_c_attribute17 IN VARCHAR2 := NULL
, p_c_attribute18 IN VARCHAR2 := NULL
, p_c_attribute19 IN VARCHAR2 := NULL
, p_c_attribute20 IN VARCHAR2 := NULL
, p_d_attribute1 IN DATE := NULL
, p_d_attribute2 IN DATE := NULL
, p_d_attribute3 IN DATE := NULL
, p_d_attribute4 IN DATE := NULL
, p_d_attribute5 IN DATE := NULL
, p_d_attribute6 IN DATE := NULL
, p_d_attribute7 IN DATE := NULL
, p_d_attribute8 IN DATE := NULL
, p_d_attribute9 IN DATE := NULL
, p_d_attribute10 IN DATE := NULL
, p_n_attribute1 IN NUMBER := NULL
, p_n_attribute2 IN NUMBER := NULL
, p_n_attribute3 IN NUMBER := NULL
, p_n_attribute4 IN NUMBER := NULL
, p_n_attribute5 IN NUMBER := NULL
, p_n_attribute6 IN NUMBER := NULL
, p_n_attribute7 IN NUMBER := NULL
, p_n_attribute8 IN NUMBER := NULL
, p_n_attribute9 IN NUMBER := NULL
, p_n_attribute10 IN NUMBER := NULL
, p_territory_code IN VARCHAR2 := NULL
, p_time_since_new IN NUMBER := NULL
, p_cycles_since_new IN NUMBER := NULL
, p_time_since_overhaul IN NUMBER := NULL
, p_cycles_since_overhaul IN NUMBER := NULL
, p_time_since_repair IN NUMBER := NULL
, p_cycles_since_repair IN NUMBER := NULL
, p_time_since_visit IN NUMBER := NULL
, p_cycles_since_visit IN NUMBER := NULL
, p_time_since_mark IN NUMBER := NULL
, p_cycles_since_mark IN NUMBER := NULL
, p_number_of_repairs IN NUMBER := NULL
, p_attribute_category IN VARCHAR2 := NULL
, p_attribute1 IN VARCHAR2 := NULL
, p_attribute2 IN VARCHAR2 := NULL
, p_attribute3 IN VARCHAR2 := NULL
, p_attribute4 IN VARCHAR2 := NULL
, p_attribute5 IN VARCHAR2 := NULL
, p_attribute6 IN VARCHAR2 := NULL
, p_attribute7 IN VARCHAR2 := NULL
, p_attribute8 IN VARCHAR2 := NULL
, p_attribute9 IN VARCHAR2 := NULL
, p_attribute10 IN VARCHAR2 := NULL
, p_attribute11 IN VARCHAR2 := NULL
, p_attribute12 IN VARCHAR2 := NULL
, p_attribute13 IN VARCHAR2 := NULL
, p_attribute14 IN VARCHAR2 := NULL
, p_attribute15 IN VARCHAR2 := NULL
) IS
l_number_of_counts NUMBER;
print_debug('Call to insert_serial_number');
SELECT cycle_count_entry_id, approval_condition
INTO l_cycle_count_entry_id , l_approval_condition
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND entry_status_code IN (1,3)
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND nvl(locator_id,-999) = nvl(p_locator_id,-999)
AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
AND nvl(revision,-999) = nvl(p_revision,-999)
AND nvl(parent_lpn_id,-999) = -999;
SELECT cycle_count_entry_id, approval_condition
INTO l_cycle_count_entry_id , l_approval_condition
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND entry_status_code IN (1,3)
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
AND nvl(revision,-999) = nvl(p_revision,-999)
AND parent_lpn_id = p_parent_lpn_id ;
SELECT number_of_counts, unit_status_current, unit_status_first
INTO l_number_of_counts, l_unit_status_prior, l_unit_status_first
FROM mtl_cc_serial_numbers
WHERE cycle_count_entry_id = l_cycle_count_entry_id
AND serial_number = p_serial_number;
/* The serial number exists. Update the data */
IF (l_unit_status_prior = 2 ) THEN --9725018, this is for the case of recount.
l_pos_adjustment_qty := 1; --The serial was added in first count, so it is +ve adj
UPDATE MTL_CC_SERIAL_NUMBERS
SET
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
-- number_of_counts = nvl(l_number_of_counts,0) + 1, commented for bug 9681558
unit_status_current = l_unit_status_current,
unit_status_prior = l_unit_status_prior,
unit_status_first = l_unit_status_first,
approval_condition = l_approval_condition,
pos_adjustment_qty = l_pos_adjustment_qty,
neg_adjustment_qty = l_neg_adjustment_qty
WHERE cycle_count_entry_id = l_cycle_count_entry_id
AND serial_number = p_serial_number;
/* Serial number does not exist. Insert the serial number */
l_number_of_counts := NULL;
INSERT INTO MTL_CC_SERIAL_NUMBERS(
cycle_count_entry_id,
serial_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number_of_counts,
unit_status_current,
unit_status_prior,
unit_status_first,
approval_condition,
pos_adjustment_qty,
neg_adjustment_qty
) VALUES (
l_cycle_count_entry_id,
p_serial_number,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_number_of_counts,
l_unit_status_current,
l_unit_status_prior,
l_unit_status_first,
l_approval_condition,
l_pos_adjustment_qty,
l_neg_adjustment_qty
);
INSERT INTO MTL_SERIAL_NUMBERS (
inventory_item_id,
serial_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
initialization_date,
current_status,
revision,
lot_number,
current_subinventory_code,
current_locator_id,
current_organization_id,
last_txn_source_type_id,
last_receipt_issue_type,
last_txn_source_id,
gen_object_id
-- Adding for bug#9959346
, serial_attribute_category
, origination_date
, 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
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
) VALUES (
p_inventory_item_id,
p_serial_number,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
1,
p_revision,
p_lot_number,
p_subinventory,
p_locator_id,
p_organization_id,
9,
4,
l_cycle_count_entry_id,
mtl_gen_object_id_s.NEXTVAL,
-- Adding for bug#9959346
p_serial_attribute_category
, p_orgination_date
, p_c_attribute1
, p_c_attribute2
, p_c_attribute3
, p_c_attribute4
, p_c_attribute5
, p_c_attribute6
, p_c_attribute7
, p_c_attribute8
, p_c_attribute9
, p_c_attribute10
, p_c_attribute11
, p_c_attribute12
, p_c_attribute13
, p_c_attribute14
, p_c_attribute15
, p_c_attribute16
, p_c_attribute17
, p_c_attribute18
, p_c_attribute19
, p_c_attribute20
, p_d_attribute1
, p_d_attribute2
, p_d_attribute3
, p_d_attribute4
, p_d_attribute5
, p_d_attribute6
, p_d_attribute7
, p_d_attribute8
, p_d_attribute9
, p_d_attribute10
, p_n_attribute1
, p_n_attribute2
, p_n_attribute3
, p_n_attribute4
, p_n_attribute5
, p_n_attribute6
, p_n_attribute7
, p_n_attribute8
, p_n_attribute9
, p_n_attribute10
, p_territory_code
, p_time_since_new
, p_cycles_since_new
, p_time_since_overhaul
, p_cycles_since_overhaul
, p_time_since_repair
, p_cycles_since_repair
, p_time_since_visit
, p_cycles_since_visit
, p_time_since_mark
, p_cycles_since_mark
, p_number_of_repairs
, p_attribute_category
, p_attribute1
, p_attribute2
, p_attribute3
, p_attribute4
, p_attribute5
, p_attribute6
, p_attribute7
, p_attribute8
, p_attribute9
, p_attribute10
, p_attribute11
, p_attribute12
, p_attribute13
, p_attribute14
, p_attribute15);
END insert_Serial_number;
SELECT serial_number,
pos_adjustment_qty
FROM mtl_cc_Serial_numbers
WHERE cycle_count_entry_id = l_cycle_count_entry_id;
SELECT cycle_count_entry_id
INTO l_cycle_count_entry_id
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND entry_status_code IN (1,3)
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND nvl(locator_id,-999) = nvl(p_locator_id,-999)
AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
AND nvl(revision,-999) = nvl(p_revision,-999)
AND nvl(parent_lpn_id,-999) = nvl(p_parent_lpn_id,-999);
DELETE FROM mtl_cc_serial_numbers
WHERE cycle_count_entry_id = l_cycle_count_entry_id
AND serial_number = l_serial_number;
print_debug('Exception while trying to delete from mtl_cc_serial_numbers for serial ' || l_serial_number);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id;