The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_item_desc_element_table.DELETE(icoi_rec_dup);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
, p_delete_rec_flag IN NUMBER
, p_commit_flag IN NUMBER
, p_prog_appid IN NUMBER
, p_prog_id IN NUMBER
, p_request_id IN NUMBER
, p_user_id IN NUMBER
, p_login_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_Item_Catalog_grp_recs';
SELECT
mdei_dup.rowid
, mdei_dup.transaction_id
, mdei_dup.element_name
FROM
mtl_desc_elem_val_interface mdei_dup
WHERE
mdei_dup.rowid > (select rowid
FROM mtl_desc_elem_val_interface mdei
WHERE mdei.set_process_id = g_xset_id
AND mdei.process_flag IN (1, 2)
--bug 9693293,8490530
AND rownum =1
--end bug 9693293
AND (mdei.inventory_item_id = mdei_dup.inventory_item_id
OR mdei.item_number = mdei_dup.item_number )
AND mdei.element_name = mdei_dup.element_name
)
FOR UPDATE OF mdei_dup.transaction_id;
SELECT
mdei.rowid, mdei.transaction_id
, mdei.inventory_item_id
, mdei.element_name, mdei.element_value
, mdei.element_sequence, mdei.item_number
, mdei.default_element_flag
FROM
mtl_desc_elem_val_interface mdei
WHERE
mdei.set_process_id = g_xset_id
AND mdei.process_flag IN (1, 2, 4) --R12C
ORDER BY mdei.item_number,mdei.inventory_item_id
FOR UPDATE OF mdei.transaction_id;
UPDATE mtl_desc_elem_val_interface mdei
SET process_flag = 2
WHERE
mdei.set_process_id = g_xset_id
AND mdei.process_flag = 1;
SELECT mtl_system_items_interface_s.NEXTVAL
INTO l_transaction_id
FROM dual;
UPDATE mtl_desc_elem_val_interface
SET
transaction_id = l_transaction_id,
request_id = g_request_id,
process_flag = 3
WHERE
CURRENT OF icoi_csr;
UPDATE mtl_desc_elem_val_interface
SET
inventory_item_id = l_inventory_item_id
WHERE
CURRENT OF icoi_csr;
INV_ITEM_MSG.Write_List (p_delete => TRUE);
SELECT mtl_system_items_interface_s.NEXTVAL
INTO l_transaction_id
FROM dual;
UPDATE mtl_desc_elem_val_interface
SET
transaction_id = l_transaction_id,
request_id = g_request_id,
process_flag = 3
WHERE
CURRENT OF icoi_csr_dup;
INV_ITEM_MSG.Write_List (p_delete => TRUE);
SELECT mtl_system_items_interface_s.NEXTVAL
INTO l_transaction_id
FROM dual;
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Debug(Mctx, 'update interface record');
UPDATE mtl_desc_elem_val_interface
SET
transaction_id = l_transaction_id
, inventory_item_id = l_inventory_item_id
, item_number = l_item_number
, process_flag = l_process_flag
, program_application_id = g_prog_appid
, program_id = g_prog_id
, program_update_date= SYSDATE
, request_id = g_request_id
, last_update_date = SYSDATE
, last_updated_by = g_user_id
, last_update_login = g_login_id
WHERE
CURRENT OF icoi_csr;
IF (p_delete_rec_flag = 1) THEN
INV_ITEM_MSG.Debug(Mctx, 'calling delete_OI_records');
INV_ITEM_CATALOG_ELEM_PUB.delete_OI_records
( p_commit => l_commit
, p_rec_set_id => g_xset_id
, x_return_status => l_return_status
);
INV_ITEM_MSG.Debug(Mctx, 'done delete_OI_records: return_status=' || l_return_status);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
END IF; -- p_delete_rec_flag = 1
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
PROCEDURE delete_OI_records
(
p_commit IN VARCHAR2
, p_rec_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_OI_records';
l_del_process_flag NUMBER := 7; -- process_flag value for records to be deleted
DELETE FROM mtl_desc_elem_val_interface
WHERE set_process_id = p_rec_set_id
AND process_flag = l_del_process_flag
AND rownum < G_ROWS_TO_COMMIT;
INV_ITEM_MSG.Debug(Mctx, 'deleted ' || TO_CHAR(SQL%ROWCOUNT) || ' record(s)');
END delete_OI_records;