[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT physical_inventory_name,
physical_inventory_id,
description,
freeze_date,
adjustments_posted,
approval_required,
cost_variance_neg,
cost_variance_pos,
approval_tolerance_neg,
approval_tolerance_pos,
all_subinventories_flag,
dynamic_tag_entry_flag
FROM mtl_physical_inventories_v
WHERE organization_id = p_organization_id
AND snapshot_complete = 1
AND adjustments_posted <> 1
AND physical_inventory_name LIKE (p_phy_inv)
ORDER BY physical_inventory_name;
SELECT COUNT(*)
INTO x_number
FROM mtl_physical_inventory_tags
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND serial_num = p_serial_number
AND inventory_item_id = p_inventory_item_id
AND tag_quantity IS NOT NULL
AND tag_quantity <> 0
AND void_flag = 2
AND adjustment_id IN
(SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL);
SELECT all_subinventories_flag
INTO l_all_sub_flag
FROM mtl_physical_inventories
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id;
SELECT NVL(current_subinventory_code, '@@@@@')
INTO l_serial_sub
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND current_organization_id = p_organization_id;
SELECT COUNT(*)
INTO x_serial_in_scope
FROM mtl_physical_subinventories
WHERE organization_id = p_organization_id
AND physical_inventory_id = p_physical_inventory_id
AND subinventory = l_serial_sub;
SELECT *
FROM mtl_physical_inventory_tags
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
-- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
AND void_flag = 2
AND (adjustment_id IN
(SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL) OR adjustment_id IS NULL); --bug#9772069
SELECT *
FROM mtl_physical_inventory_tags
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
-- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
AND void_flag = 2
AND (adjustment_id IN
(SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL) OR adjustment_id IS NULL); --bug#9772069
SELECT *
FROM mtl_physical_inventory_tags
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
AND serial_num IS NULL --bug12799495
--AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
-- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
AND void_flag = 2
AND (adjustment_id IN
(SELECT adjustment_id
FROM mtl_physical_adjustments
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id
AND approval_status IS NULL) OR adjustment_id IS NULL);
update_row( p_tag_id => tag_record.tag_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_subinventory => p_subinventory,
p_locator_id => p_locator_id,
p_parent_lpn_id => p_parent_lpn_id,
p_inventory_item_id => p_inventory_item_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => l_current_serial,
p_tag_quantity => p_tag_quantity,
p_tag_uom => p_tag_uom,
p_user_id => p_user_id,
p_cost_group_id => tag_record.cost_group_id,
p_adjustment_id => l_adjustment_id
);
update_adjustment
(p_adjustment_id => l_adjustment_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_user_id => p_user_id
);
update_row( p_tag_id => tag_record.tag_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_subinventory => p_subinventory,
p_locator_id => p_locator_id,
p_parent_lpn_id => p_parent_lpn_id,
p_inventory_item_id => p_inventory_item_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => l_current_serial,
p_tag_quantity => p_tag_quantity,
p_tag_uom => p_tag_uom,
p_user_id => p_user_id,
p_cost_group_id => tag_record.cost_group_id,
p_adjustment_id => l_adjustment_id
);
update_adjustment
(p_adjustment_id => l_adjustment_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_user_id => p_user_id
);
print_debug('Dynamic serial tag entry to be inserted');
update_row( p_tag_id => tag_record.tag_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_subinventory => tag_record.subinventory,
p_locator_id => tag_record.locator_id,
p_parent_lpn_id => p_parent_lpn_id,
p_inventory_item_id => p_inventory_item_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => l_current_serial,
p_tag_quantity => 0,
p_tag_uom => p_tag_uom,
p_user_id => p_user_id,
p_cost_group_id => tag_record.cost_group_id,
p_adjustment_id => l_adjustment_id
);
update_adjustment
(p_adjustment_id => l_adjustment_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_user_id => p_user_id
);
insert_row( p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_subinventory => p_subinventory,
p_locator_id => p_locator_id,
p_parent_lpn_id => p_parent_lpn_id,
p_inventory_item_id => p_inventory_item_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => l_current_serial,
p_tag_quantity => p_tag_quantity,
p_tag_uom => p_tag_uom,
p_user_id => p_user_id,
p_cost_group_id => l_cost_group_id,
p_adjustment_id => l_adjustment_id
);
update_adjustment
(p_adjustment_id => l_adjustment_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_user_id => p_user_id
);
update_row( p_tag_id => tag_record.tag_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_subinventory => p_subinventory,
p_locator_id => p_locator_id,
p_parent_lpn_id => p_parent_lpn_id,
p_inventory_item_id => p_inventory_item_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => NULL,
p_tag_quantity => p_tag_quantity,
p_tag_uom => p_tag_uom,
p_user_id => p_user_id,
p_cost_group_id => tag_record.cost_group_id,
p_adjustment_id => l_adjustment_id
,p_tag_sec_quantity => p_tag_sec_quantity --INVCONV, NSRIVAST
);
update_adjustment
(p_adjustment_id => l_adjustment_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_user_id => p_user_id
);
print_debug('Dynamic non-serial tag entry to be inserted');
insert_row( p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_subinventory => p_subinventory,
p_locator_id => p_locator_id,
p_parent_lpn_id => p_parent_lpn_id,
p_inventory_item_id => p_inventory_item_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => NULL,
p_tag_quantity => p_tag_quantity,
p_tag_uom => p_tag_uom,
p_user_id => p_user_id,
p_cost_group_id => l_cost_group_id,
p_adjustment_id => l_adjustment_id
--INVCONV, NSRIVAST, START
,p_tag_sec_quantity => p_tag_sec_quantity
,p_tag_sec_uom => p_tag_sec_uom
--INVCONV, NSRIVAST, END
);
update_adjustment
(p_adjustment_id => l_adjustment_id,
p_physical_inventory_id => p_physical_inventory_id,
p_organization_id => p_organization_id,
p_user_id => p_user_id
);
print_debug('PI_ER.. Calling delete_duplicate_entries from process_tag>>> ');
delete_duplicate_entries(
p_physical_inventory_id,
p_organization_id,
p_parent_lpn_id,
p_inventory_item_id,
p_revision,
p_lot_number,
p_from_serial_number,
l_adjustment_id
);
PROCEDURE insert_row
(p_physical_inventory_id IN NUMBER,
p_organization_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_locator_id IN NUMBER,
p_parent_lpn_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_serial_number IN VARCHAR2,
p_tag_quantity IN NUMBER,
p_tag_uom IN VARCHAR2,
p_user_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_adjustment_id IN NUMBER
--INVCONV, NSRIVAST, START
,p_tag_sec_quantity IN NUMBER := NULL
,p_tag_sec_uom IN VARCHAR2 := NULL
--INVCONV, NSRIVAST, END
)
IS
l_tag_id NUMBER;
SELECT next_tag_number
FROM mtl_physical_inventories
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id;
print_debug('***insert_row***');
SELECT mtl_physical_inventory_tags_s.nextval
INTO l_tag_id
FROM dual;
SELECT MAX(tag_number)
INTO l_tag_number
FROM mtl_physical_inventory_tags
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id;
UPDATE MTL_PHYSICAL_INVENTORIES
SET next_tag_number = l_next_tag_number
WHERE physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id;
print_debug('Update physical inventory with next tag number: ' || l_next_tag_number);
SELECT primary_uom_code
INTO l_item_standard_uom
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT outermost_lpn_id
INTO l_outermost_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id;
SELECT DISTINCT(fus.employee_id)
INTO l_employee_id
FROM PER_WORKFORCE_CURRENT_X mec, fnd_user fus
WHERE fus.user_id = p_user_id
AND mec.person_id = fus.employee_id
AND rownum = 1;
/* Select clause Added for Bug8199582 */
BEGIN
Select expiration_date
into l_lot_expiration_date
from mtl_lot_numbers
where lot_number = p_lot_number
and inventory_item_id = p_inventory_item_id
and organization_id= p_organization_id
and expiration_date is not null;
print_debug('Inserting the new record here');
INSERT INTO MTL_PHYSICAL_INVENTORY_TAGS
(tag_id,
physical_inventory_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
void_flag,
tag_number,
adjustment_id,
inventory_item_id,
tag_quantity,
tag_uom,
tag_quantity_at_standard_uom,
standard_uom,
subinventory,
locator_id,
lot_number,
revision,
serial_num,
counted_by_employee_id,
parent_lpn_id,
outermost_lpn_id,
cost_group_id
--INVCONV, NSRIVAST, START
,tag_secondary_uom
,tag_secondary_quantity
,LOT_EXPIRATION_DATE -- -- Inserting Expiration Date , Bug8199582
--INVCONV, NSRIVAST, END
) VALUES
(l_tag_id,
p_physical_inventory_id,
p_organization_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
2,
l_tag_number,
p_adjustment_id,
p_inventory_item_id,
p_tag_quantity,
p_tag_uom,
l_tag_qty_at_standard_uom,
l_item_standard_uom,
p_subinventory,
p_locator_id,
p_lot_number,
p_revision,
p_serial_number,
l_employee_id,
p_parent_lpn_id,
l_outermost_lpn_id,
p_cost_group_id
--INVCONV, NSRIVAST, START
,p_tag_sec_uom
,p_tag_sec_quantity
,l_lot_expiration_date -- Inserting Expiration Date , Bug8199582
--INVCONV, NSRIVAST, END
);
END insert_row;
PROCEDURE update_row
(p_tag_id IN NUMBER,
p_physical_inventory_id IN NUMBER,
p_organization_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_locator_id IN NUMBER,
p_parent_lpn_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_serial_number IN VARCHAR2,
p_tag_quantity IN NUMBER,
p_tag_uom IN VARCHAR2,
p_user_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_adjustment_id IN NUMBER
,p_tag_sec_quantity IN NUMBER := NULL --INVCONV, NSRIVAST, START
)
IS
l_tag_qty_at_standard_uom NUMBER;
print_debug('***update_row***');
SELECT primary_uom_code
INTO l_item_standard_uom
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT outermost_lpn_id
INTO l_outermost_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id;
SELECT DISTINCT(fus.employee_id)
INTO l_employee_id
FROM PER_WORKFORCE_CURRENT_X mec, fnd_user fus
WHERE fus.user_id = p_user_id
AND mec.person_id = fus.employee_id
AND rownum = 1;
UPDATE MTL_PHYSICAL_INVENTORY_TAGS
SET
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_user_id,
adjustment_id = p_adjustment_id,
inventory_item_id = p_inventory_item_id,
tag_quantity = p_tag_quantity,
tag_uom = p_tag_uom,
tag_quantity_at_standard_uom = l_tag_qty_at_standard_uom,
standard_uom = l_item_standard_uom,
subinventory = p_subinventory,
locator_id = p_locator_id,
lot_number = p_lot_number,
revision = p_revision,
serial_num = p_serial_number,
counted_by_employee_id = l_employee_id,
parent_lpn_id = p_parent_lpn_id,
outermost_lpn_id = l_outermost_lpn_id,
cost_group_id = p_cost_group_id
,tag_secondary_quantity = p_tag_sec_quantity --INVCONV, NSRIVAST, START
WHERE tag_id = p_tag_id;
END update_row;
PROCEDURE update_adjustment
(p_adjustment_id IN NUMBER,
p_physical_inventory_id IN NUMBER,
p_organization_id IN NUMBER,
p_user_id IN NUMBER
)
IS
l_adj_count_quantity NUMBER;
print_debug('***update_adjustment***');
/* Fix for Bug#7591655. Added tag_secondary_quantity in following select. Secondary qty is always at
secondary uom. Hence no need of tag_secondary_at_standard_uom */
SELECT NVL(SUM(tag_quantity_at_standard_uom),0),
NVL(SUM(tag_secondary_quantity),0)
INTO l_adj_count_quantity,
l_adj2_count_quantity
FROM mtl_physical_inventory_tags
WHERE adjustment_id = p_adjustment_id
AND organization_id = p_organization_id
AND physical_inventory_id = p_physical_inventory_id
AND void_flag = 2;
/* Fix for Bug#7591655 . Added secondary_count_qty and secondary_adjustment_qty in following update */
UPDATE mtl_physical_adjustments
SET last_update_date = SYSDATE,
last_updated_by = NVL(p_user_id, -1),
count_quantity = l_adj_count_quantity,
adjustment_quantity = NVL(l_adj_count_quantity, NVL(system_quantity,0))
- NVL(system_quantity,0),
secondary_count_qty = l_adj2_count_quantity,
secondary_adjustment_qty = NVL(l_adj2_count_quantity, NVL(secondary_system_qty,0))
- NVL(secondary_system_qty,0),
approval_status = NULL,
approved_by_employee_id = NULL
WHERE adjustment_id = p_adjustment_id
AND physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id;
SELECT inventory_item_id, lot_number, serial_number, parent_lpn_id,
subinventory_name, locator_id, NVL(adjustment_quantity, 0)
INTO l_inventory_item_id, l_lot_number, l_serial_number,
l_lpn_id, l_subinventory, l_locator_id, l_adjustment_quantity
FROM mtl_physical_adjustments
WHERE adjustment_id = p_adjustment_id
AND physical_inventory_id = p_physical_inventory_id
AND organization_id = p_organization_id;
SELECT primary_uom_code
INTO l_standard_uom_code
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = p_organization_id;
END update_adjustment;
SELECT stock_locator_control_code
INTO l_org_locator_type
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT locator_type
INTO l_sub_locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory
AND organization_id = p_organization_id;
SELECT revision_qty_control_code, location_control_code,
lot_control_code, serial_number_control_code
INTO l_rev_code, l_location_control_code,
l_lot_control_code, l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT MIN(ADJUSTMENT_ID)
INTO l_adj_id
FROM MTL_PHYSICAL_ADJUSTMENTS
WHERE ORGANIZATION_ID = p_organization_id
AND PHYSICAL_INVENTORY_ID = p_physical_inventory_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND SUBINVENTORY_NAME = p_subinventory
AND ( NVL(REVISION,'@@@@@') = NVL(p_revision,'@@@@@')
OR l_rev_code = 1 )
AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999) --Bug 6929248 Posted adjustments should not be allowed to enter tags
AND (approval_status=3 OR NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999))
AND (NVL(LOCATOR_ID, -99999) = NVL(p_locator_id, -99999)
OR l_org_locator_type = 1
OR (l_org_locator_type = 4
AND (l_sub_locator_type = 1
OR (l_sub_locator_type = 5
AND l_location_control_code = 1)))
OR (l_location_control_code = 5
AND l_location_control_code = 1))
AND ( NVL(LOT_NUMBER,'@@@@@') = NVL(p_lot_number,'@@@@@')
OR l_lot_control_code = 1 )
AND ( NVL(SERIAL_NUMBER,'@@@@@') = NVL(p_serial_number,'@@@@@')
OR l_serial_control_code = 1 )
GROUP BY ORGANIZATION_ID,
PHYSICAL_INVENTORY_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_NAME,
REVISION,
LOCATOR_ID,
PARENT_LPN_ID,
COST_GROUP_ID,
LOT_NUMBER,
SERIAL_NUMBER;
select approval_status
into l_approval_status
from mtl_physical_adjustments
where adjustment_id = l_adj_id
and physical_inventory_id = p_physical_inventory_id;
print_debug('No adjustment record found so insert a new one');
SELECT NVL(process_enabled_flag, 'N')
INTO l_process_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT mtl_physical_adjustments_s.NEXTVAL
INTO l_adj_id
FROM dual;
SELECT outermost_lpn_id
INTO l_outermost_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id;
/* Select clause Added for Bug8199582 */
BEGIN
Select expiration_date
into l_lot_expiration_date
from mtl_lot_numbers
where lot_number = p_lot_number
and inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and expiration_date is not null;
print_debug('Inserting the new physical adjustment record');
/* Fix for Bug#7591655. Added secondary_count_qty and secondary_adjustment_qty in insert */
INSERT INTO mtl_physical_adjustments
( adjustment_id,
organization_id,
physical_inventory_id,
inventory_item_id,
subinventory_name,
system_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
count_quantity,
adjustment_quantity,
revision,
locator_id,
parent_lpn_id,
outermost_lpn_id,
cost_group_id,
lot_number,
serial_number,
actual_cost ,
secondary_count_qty,
secondary_adjustment_qty,
lot_expiration_date ) /* Inserting Expiration Date , Bug8199582 */
VALUES ( l_adj_id,
p_organization_id,
p_physical_inventory_id,
p_inventory_item_id,
p_subinventory,
0,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
0,
0,
p_revision,
p_locator_id,
p_parent_lpn_id,
l_outermost_lpn_id,
p_cost_group_id,
p_lot_number,
p_serial_number,
l_actual_cost,
0,
0,
l_lot_expiration_date); /* Inserting Expiration Date , Bug8199582 */
SELECT *
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = p_parent_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT *
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = l_current_lpn
AND NVL(serial_summary_entry, 2) = 2;
SELECT *
FROM MTL_SERIAL_NUMBERS
WHERE lpn_id = l_current_lpn;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_id.inventory_item_id
AND organization_id = v_lpn_id.organization_id;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
AND organization_id = v_lpn_serial_content.current_organization_id;
UPDATE mtl_serial_numbers
SET group_mark_id = -1
WHERE inventory_item_id = p_item_id
AND serial_number in
(SELECT DISTINCT serial_num
FROM mtl_physical_inventory_tags
WHERE organization_id = p_organization_id
AND physical_inventory_id = p_physical_inventory_id
AND inventory_item_id = p_item_id
AND serial_num is not null
)
AND nvl(group_mark_id,-1) <> -1;
print_debug('Updated ' || SQL%ROWCOUNT || ' Records in mtl_serial_numbers for the inventory_item_id ' || p_item_id);
SELECT NVL(SERIAL_NUMBER_TYPE, 0)
INTO x_serial_number_type
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT 1,MP.ORGANIZATION_CODE,ML.MEANING
INTO l_valid_serial,x_organization_code,x_current_status
FROM MTL_SERIAL_NUMBERS MSN , MTL_PARAMETERS MP, MFG_LOOKUPS ML
WHERE SERIAL_NUMBER like p_serial_num
AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
-- AND MSN.CURRENT_SUBINVENTORY_CODE like p_subinventory_code /*Bug14778466-Commeneted subinv*/
-- AND NVL(MSN.CURRENT_LOCATOR_ID,-9999) = NVL(p_locator_id, -9999)
AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
AND CURRENT_STATUS = 3;
SELECT MP.ORGANIZATION_CODE,ML.MEANING
INTO x_organization_code,x_current_status
FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
WHERE SERIAL_NUMBER like p_serial_num
AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
AND CURRENT_STATUS NOT IN (1,4);
SELECT MP.ORGANIZATION_CODE,ML.MEANING
INTO x_organization_code,x_current_status
FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
WHERE MSN.SERIAL_NUMBER like p_serial_num
AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
AND CURRENT_STATUS NOT IN (1,4);
SELECT MP.ORGANIZATION_CODE,ML.MEANING
INTO x_organization_code,x_current_status
FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
WHERE MSN.SERIAL_NUMBER like p_serial_num
AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
AND CURRENT_STATUS NOT IN (1,4);
SELECT MP.ORGANIZATION_CODE,ML.MEANING
INTO x_organization_code,x_current_status
FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
WHERE MSN.SERIAL_NUMBER like p_serial_num
AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
AND MSN.CURRENT_ORGANIZATION_ID = MP. ORGANIZATION_ID
AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
AND CURRENT_STATUS NOT IN (1,4);
/* Added the procedure DELETE_DUPLICATE_ENTRIES for Phy Inv ER - bug 13865417
Itis called from procedure PROCESS_TAG while PI Count using Mobile apps, and it is called in INVADPTE.pld file when the PI Count using FORMS Apps .
*/
PROCEDURE delete_duplicate_entries(
p_physical_inventory_id IN NUMBER,
p_organization_id IN NUMBER,
p_parent_lpn_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_serial_number IN VARCHAR2,
p_adjustment_id IN NUMBER
)
IS
l_adjustment_id NUMBER;
l_update BOOLEAN;
print_debug('PI_ER..*** Start delete_duplicate_entries ***');
SELECT 'Y', subinventory_name, locator_id INTO l_new_tag, l_subinv, l_locator -- added subinv, locator for bug 15926209
FROM MTL_PHYSICAL_ADJUSTMENTS
WHERE adjustment_id = p_adjustment_id
AND approval_status IS NULL
AND system_quantity = 0;
print_debug('PI_ER.. Adjustment/Tag is already existing, Hence no Adj is deleted >>> ');
SELECT NULLIF(MIN(adjustment_id),MAX(adjustment_id)) INTO l_adjustment_id
FROM mtl_physical_adjustments
WHERE organization_id = p_organization_id
AND physical_inventory_id = p_physical_inventory_id
AND inventory_item_id = p_inventory_item_id
AND parent_lpn_id = p_parent_lpn_id
AND nvl(approval_status,0) <> 3
AND NVL(revision,'@@@@') = NVL(p_revision,'@@@@')
AND NVL(lot_number,'@@@@') = NVL(p_lot_number,'@@@@')
AND NVL(serial_number,'@@@@') = NVL(p_serial_number,'@@@@')
AND adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_inventory_tags
WHERE organization_id = p_organization_id
AND physical_inventory_id = p_physical_inventory_id
AND inventory_item_id = p_inventory_item_id
AND parent_lpn_id = p_parent_lpn_id
AND NVL(revision,'@@@@') = NVL(p_revision,'@@@@')
AND NVL(lot_number,'@@@@') = NVL(p_lot_number,'@@@@')
AND NVL(serial_num,'@@@@') = NVL(p_serial_number,'@@@@')
AND void_flag = 2);
SELECT quantity, secondary_quantity INTO l_sys_qty, l_sec_sys_qty
FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlc
WHERE wlpn.lpn_id = wlc.parent_lpn_id
AND wlpn.lpn_id = p_parent_lpn_id
AND wlpn.subinventory_code = l_subinv
AND wlpn.locator_id = l_locator
AND Nvl(wlc.lot_number,'@@@') = Nvl(p_lot_number,'@@@')
AND Nvl(wlc.revision,'@@@') = Nvl(p_revision,'@@@')
AND wlc.inventory_item_id = p_inventory_item_id
AND wlc.organization_id = p_organization_id;
SELECT 1 INTO l_sys_qty
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_parent_lpn_id
AND wlpn.subinventory_code = l_subinv
AND wlpn.locator_id = l_locator;
l_update := true;
l_update := false;
IF l_update THEN
UPDATE mtl_physical_adjustments
SET system_quantity = l_sys_qty,
adjustment_quantity = Nvl(count_quantity,0) - Nvl(l_sys_qty,0),
secondary_system_qty = l_sec_sys_qty,
secondary_adjustment_qty = nvl(secondary_count_qty,0) - nvl(l_sec_sys_qty,0)
WHERE adjustment_id = p_adjustment_id;
DELETE FROM mtl_physical_inventory_tags
WHERE adjustment_id = l_adjustment_id;
print_debug('PI_ER.. Count of deleted recs for mtl_physical_inventory_tags >>> '||SQL%ROWCOUNT||' for LPN Id, Adj Id '||p_parent_lpn_id ||' , '||l_adjustment_id);
DELETE FROM mtl_physical_adjustments
WHERE adjustment_id = l_adjustment_id;
print_debug('PI_ER.. Count of deleted recs for mtl_physical_adjustments >>> '||SQL%ROWCOUNT||' for LPN Id, Adj Id '||p_parent_lpn_id ||' , '||l_adjustment_id);
print_debug('PI_ER..*** End delete_duplicate_entries ***');
print_debug('PI_ER.. Exception occur while delete_duplicate_entries >>> '||SUBSTR(SQLERRM,1,100)||' for LPN '||p_parent_lpn_id);
END delete_duplicate_entries;
SELECT mpa_xfr.physical_inventory_id,
mpa_xfr.adjustment_id xfr_adjustment_id,
mpa_sys.locator_id from_locator,
mpa_xfr.locator_id to_locator,
mpa_xfr.subinventory_name xfr_subinventory_name,
mpa_sys.system_quantity,
mpa_xfr.count_quantity,
(Nvl(mpa_xfr.count_quantity,0) - nvl(mpa_sys.system_quantity,0)) adjustment_quantity,
mpa_xfr.secondary_count_qty,
(Nvl(mpa_xfr.secondary_count_qty,0) - nvl(mpa_sys.secondary_system_qty,0)) secondary_adjustment_qty,
mpa_sys.inventory_item_id,
mpa_sys.organization_id,
mpa_sys.subinventory_name,
mpa_sys.cost_group_id,
mpa_sys.parent_lpn_id,
mpa_sys.lot_number,
mpa_sys.lot_expiration_date,
mpa_sys.serial_number,
mpa_sys.outermost_lpn_id,
mpa_sys.revision
FROM (SELECT wms.lpn_id parent_lpn_id,
wms.outermost_lpn_id,
wms.organization_id,
wms.inventory_item_id,
wms.subinventory_name,
wms.locator_id,
wms.lot_number,
msn.serial_number,
wms.revision,
wms.cost_group_id,
mln.expiration_date lot_expiration_date,
Decode (wms.serial_summary_entry, 1, 1, wms.primary_quantity) system_quantity,
Decode (wms.serial_summary_entry, 1, 0, wms.secondary_quantity) secondary_system_qty --bug 14778466
FROM (SELECT DISTINCT wlp.lpn_id,
wlp.outermost_lpn_id,
wlp.organization_id,
wlc.inventory_item_id,
wlp.subinventory_code subinventory_name,
wlp.locator_id,
wlc.lot_number,
wlc.primary_quantity,
wlc.secondary_quantity,
wlc.serial_summary_entry,
wlc.revision,
wlc.cost_group_id
FROM wms_license_plate_numbers wlp, mtl_physical_adjustments mpa, wms_lpn_contents wlc
WHERE wlp.lpn_id = wlc.parent_lpn_id
AND mpa.parent_lpn_id = wlp.lpn_id
AND mpa.organization_id = p_org_id
AND mpa.physical_inventory_id = p_phy_inv_id
AND ( mpa.subinventory_name <> wlp.subinventory_code OR mpa.locator_id <> wlp.locator_id )
AND Nvl(mpa.count_quantity, 0) <> 0
AND mpa.approval_status = 1
AND mpa.parent_lpn_id IS NOT NULL
AND mpa.adjustment_id IN (SELECT adjustment_id FROM mtl_physical_inventory_tags
WHERE organization_id = p_org_id
AND physical_inventory_id = p_phy_inv_id AND void_flag = 2)
) wms,
mtl_serial_numbers msn,
mtl_lot_numbers mln
WHERE wms.organization_id = msn.current_organization_id (+)
AND wms.inventory_item_id = msn.inventory_item_id (+)
AND wms.subinventory_name = msn.current_subinventory_code(+)
AND wms.locator_id = msn.current_locator_id(+)
--modified for bug 14778466
AND wms.lpn_id = msn.lpn_id (+)
AND wms.organization_id = mln.organization_id (+)
AND wms.inventory_item_id = mln.inventory_item_id (+)
AND Nvl(wms.cost_group_id,-999) = Nvl(msn.cost_group_id(+),-999)
AND Nvl(wms.revision,'@#@#@') = Nvl(msn.revision(+),'@#@#@')
AND Nvl(wms.lot_number,'@#@#@') = Nvl(msn.lot_number(+),'@#@#@')
AND Nvl(wms.lot_number,'@#@#@') = Nvl(mln.lot_number(+),'@#@#@')
) mpa_sys,
(SELECT mpa.*
FROM mtl_physical_adjustments mpa, mtl_parameters mp
WHERE mpa.organization_id= mp.organization_id
and mpa.organization_id = p_org_id
AND physical_inventory_id = p_phy_inv_id
AND system_quantity = 0
AND nvl(count_quantity,0) <> 0
AND approval_status = 1
AND parent_lpn_id IS NOT NULL
AND adjustment_id IN (SELECT adjustment_id FROM mtl_physical_inventory_tags
WHERE organization_id = p_org_id
AND physical_inventory_id = p_phy_inv_id
AND void_flag=2)
) mpa_xfr
WHERE mpa_sys.organization_id = mpa_xfr.organization_id(+)
AND mpa_sys.inventory_item_id = mpa_xfr.inventory_item_id(+)
AND mpa_sys.parent_lpn_id = mpa_xfr.parent_lpn_id(+)
AND mpa_sys.outermost_lpn_id = mpa_xfr.outermost_lpn_id(+)
AND Nvl(mpa_sys.cost_group_id,-999) = Nvl(mpa_xfr.cost_group_id(+),-999) -- bug 14778466
AND Nvl(mpa_sys.serial_number, '@@@') = Nvl(mpa_xfr.serial_number(+), '@@@')
AND Nvl(mpa_sys.lot_number, '@@@') = Nvl(mpa_xfr.lot_number(+), '@@@')
AND Nvl(mpa_sys.revision, '@@@') = Nvl(mpa_xfr.revision(+), '@@@')
AND mpa_sys.locator_id <> mpa_xfr.locator_id(+)
ORDER BY mpa_sys.outermost_lpn_id, mpa_sys.parent_lpn_id,mpa_xfr.subinventory_name,mpa_sys.inventory_item_id,mpa_sys.lot_number, mpa_xfr.adjustment_id;
l_last_updated_by number := fnd_global.user_id;
l_last_update_login number := fnd_global.login_id;
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_header_id
FROM dual;
SELECT description INTO l_transaction_reference
FROM mtl_physical_inventories
WHERE physical_inventory_id = p_physical_inv_id;
mydebug(' Inserting MMTT with l_transaction_header_id : '||l_transaction_header_id ||' content_lpn_id : '||i.parent_lpn_id);
INSERT INTO mtl_material_transactions_temp
(transaction_header_id
,transaction_temp_id
,transaction_mode
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,inventory_item_id
,organization_id
,subinventory_code
,locator_id
,transaction_quantity
,primary_quantity
,transaction_uom
,transaction_type_id
,transaction_action_id
,transaction_source_type_id
,transaction_source_id
,transaction_reference
,transaction_date
,acct_period_id
,distribution_account_id
,physical_adjustment_id
,transfer_subinventory
,transfer_to_location
,process_flag
,content_lpn_id
,transaction_batch_id
,transaction_batch_seq
)
VALUES ( l_transaction_header_id
,mtl_material_transactions_s.nextval
,l_txnprocmode
,sysdate
,l_last_updated_by
,sysdate
,l_last_updated_by
,-1
,-1
,p_organization_id
,i.subinventory_name
,i.from_locator
,1
,1
,l_uom_code
,l_txn_type_id
,l_txn_action_id
,l_txn_source_type_id
,p_physical_inv_id
,l_transaction_reference
,l_txn_date -- bug 16188610
,l_period_id
,p_gl_acct_id
,i.xfr_adjustment_id
,i.xfr_subinventory_name
,i.to_locator
,l_process_flag
,i.parent_lpn_id
,mtl_material_transactions_s.currval
,mtl_material_transactions_s.currval
);
UPDATE mtl_physical_adjustments
set locator_id = nvl(i.to_locator, l_prev_loctor)
, subinventory_name = nvl(i.xfr_subinventory_name,l_prev_subinv)
, system_quantity = i.system_quantity --bug 14778466
, adjustment_quantity = i.adjustment_quantity
, secondary_adjustment_qty = i.secondary_adjustment_qty
, approval_status = decode(approval_status, null,1, approval_status)
, last_update_date = sysdate
, last_updated_by = l_last_updated_by
, last_update_login = l_last_update_login
WHERE adjustment_id = i.xfr_adjustment_id
AND physical_inventory_ID = p_physical_inv_id
AND organization_id = p_organization_id;
/* --bug 14778466, commented below code since the count qty remains same, SO no need to update MPIT table .
BEGIN
mydebug(' Updating MPIT with adjustment_id : '||i.xfr_adjustment_id );
UPDATE mtl_physical_inventory_tags
set locator_id = nvl(i.to_locator, l_prev_loctor)
, subinventory = nvl(i.xfr_subinventory_name,l_prev_subinv)
, tag_quantity = i.count_quantity --bug 14778466
, tag_quantity_at_standard_uom = inv_convert.inv_um_convert
( item_id => inventory_item_id,
lot_number => lot_number,
organization_id => organization_id,
precision => 5,
from_quantity => i.count_quantity,
from_unit => tag_uom,
to_unit => l_uom_code,
from_name => NULL,
to_name => NULL)
, tag_secondary_quantity = i.secondary_adjustment_qty
, last_update_date = sysdate
, last_updated_by = l_last_updated_by
, last_update_login = l_last_update_login
WHERE adjustment_id = i.xfr_adjustment_id
AND physical_inventory_ID = p_physical_inv_id
AND organization_id = p_organization_id;
UPDATE mtl_physical_inventory_tags
set locator_id = nvl(i.to_locator, l_prev_loctor)
, subinventory = nvl(i.xfr_subinventory_name,l_prev_subinv)
, tag_quantity = i.count_quantity
, tag_secondary_quantity = i.secondary_count_qty --bug 14778466
, last_update_date = sysdate
, last_updated_by = l_last_updated_by
, last_update_login = l_last_update_login
WHERE adjustment_id = i.xfr_adjustment_id
AND physical_inventory_ID = p_physical_inv_id
AND organization_id = p_organization_id;
SELECT mtl_physical_adjustments_s.nextval INTO l_adj_id FROM dual;
SELECT NVL(process_enabled_flag, 'N')
INTO l_process_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;
mydebug(' Inserting MPA with adjustment_id : '||l_adj_id );
INSERT INTO mtl_physical_adjustments
( adjustment_id,
organization_id,
physical_inventory_id,
inventory_item_id,
subinventory_name,
system_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
count_quantity,
adjustment_quantity,
revision,
locator_id,
parent_lpn_id,
outermost_lpn_id,
cost_group_id,
lot_number,
serial_number,
actual_cost ,
secondary_count_qty,
secondary_adjustment_qty,
lot_expiration_date,
approval_status
)
VALUES ( l_adj_id,
p_organization_id,
p_physical_inv_id,
i.inventory_item_id,
l_prev_subinv,
i.system_quantity,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_updated_by,
l_last_update_login,
i.count_quantity,
i.adjustment_quantity,
i.revision,
l_prev_loctor,
i.parent_lpn_id,
i.outermost_lpn_id,
i.cost_group_id,
i.lot_number,
i.serial_number,
l_actual_cost,
i.secondary_count_qty,
i.secondary_adjustment_qty,
i.lot_expiration_date,
1 --by default approved status
);
mydebug(' Inserting MPIT with adjustment_id : '||l_adj_id );
insert_row( p_physical_inventory_id => p_physical_inv_id,
p_organization_id => p_organization_id,
p_subinventory => l_prev_subinv,
p_locator_id => l_prev_loctor,
p_parent_lpn_id => i.parent_lpn_id,
p_inventory_item_id => i.inventory_item_id,
p_revision => i.revision,
p_lot_number => i.lot_number,
p_serial_number => i.serial_number,
p_tag_quantity => i.count_quantity, --bug 14778466
p_tag_uom => l_uom_code,
p_user_id => l_last_updated_by,
p_cost_group_id => i.cost_group_id,
p_adjustment_id => l_adj_id
);
--bug 14778466, added below code to delete the old tag which is not counted at all for that lpn.
--Suppose, LPN1A in Loc1 with plain items IT1,IT2 as per system. User then counted LPN1A in Loc2 with item IT1 only and then approved it.
--So, while launch adj, we will insert a new adj rec for LPN1A with IT2 as -ve adj from Loc2, and delete adj tag which is there for Loc1.
BEGIN
-- Get the old adjustment id
SELECT NULLIF(MIN(adjustment_id),MAX(adjustment_id)) INTO l_adj_id
FROM mtl_physical_adjustments
WHERE organization_id = p_organization_id
AND physical_inventory_id = p_physical_inv_id
AND inventory_item_id = i.inventory_item_id
AND parent_lpn_id = i.parent_lpn_id
AND approval_status = 1
AND NVL(revision,'@@@@') = NVL(i.revision,'@@@@')
AND NVL(lot_number,'@@@@') = NVL(i.lot_number,'@@@@')
AND NVL(serial_number,'@@@@') = NVL(i.serial_number,'@@@@')
AND adjustment_id IN (SELECT adjustment_id
FROM mtl_physical_inventory_tags
WHERE organization_id = p_organization_id
AND physical_inventory_id = p_physical_inv_id
AND inventory_item_id = i.inventory_item_id
AND parent_lpn_id = i.parent_lpn_id
AND NVL(revision,'@@@@') = NVL(i.revision,'@@@@')
AND NVL(lot_number,'@@@@') = NVL(i.lot_number,'@@@@')
AND NVL(serial_num,'@@@@') = NVL(i.serial_number,'@@@@')
AND void_flag = 2);
mydebug(' There is no OLD adjustment to delete ');
mydebug(' Got the Adj id to delete recs from MPA, MPIT : '||l_adj_id);
DELETE FROM mtl_physical_inventory_tags
WHERE adjustment_id = l_adj_id
and physical_inventory_id = p_physical_inv_id
and organization_id = p_organization_id;
mydebug('Recs deleted from MPA >>> '||SQL%ROWCOUNT);
DELETE FROM mtl_physical_adjustments
WHERE adjustment_id = l_adj_id
and physical_inventory_id = p_physical_inv_id
and organization_id = p_organization_id;
mydebug('Recs deleted from MPIT >>> '||SQL%ROWCOUNT);
UPDATE mtl_physical_adjustments
set approval_status = 3
, system_quantity = i.system_quantity --bug 14778466
, adjustment_quantity = i.adjustment_quantity --bug 14778466
, secondary_adjustment_qty = i.secondary_adjustment_qty --bug 14778466
, last_update_date = sysdate
, last_updated_by = l_last_updated_by
, last_update_login = l_last_update_login
where adjustment_id = i.xfr_adjustment_id
and physical_inventory_id = p_physical_inv_id
and organization_id = p_organization_id;