[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
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
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);
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)
OR (msn.last_txn_source_type_id = 9 AND msn.current_status = 4)) --Bug# 3595723
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));
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)
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));
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 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);
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;
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
) 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) = nvl(p_parent_lpn_id,-999);
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 */
l_unit_status_current := 1; -- 1 -> Present in the count location, 2 -> Absent
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,
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
) 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);
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;