The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
, inventory_item_id
, segment1
FROM mtl_system_items_b msi
WHERE organization_id = p_organization_id
AND segment1
BETWEEN NVL(p_item_from,segment1) AND NVL(p_item_to,segment1);
/* UNION part from query is deleted for better performance. Bug 4951736
SELECT organization_id
, inventory_item_id
FROM mtl_system_items_kfv msik
WHERE organization_id = p_organization_id
AND concatenated_segments
BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments)
-- Following union clause added for bug 4357322
UNION
SELECT p_organization_id, -1
FROM dual where NOT EXISTS
( SELECT 1 FROM mtl_system_items_kfv
WHERE organization_id = p_organization_id
AND concatenated_segments
BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments)
) ;
SELECT organization_id
, inventory_item_id
FROM mtl_system_items_kfv msik
WHERE organization_id = p_organization_id
AND concatenated_segments
BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments);
SELECT msik.organization_id
, msik.inventory_item_id
FROM mtl_item_categories mic
, mtl_system_items_kfv msik
WHERE msik.organization_id = p_organization_id
AND msik.concatenated_segments
BETWEEN NVL(p_item_from, msik.concatenated_segments)
AND NVL(p_item_to,msik.concatenated_segments)
AND mic.inventory_item_id = msik.inventory_item_id
AND mic.organization_id = msik.organization_id --p_mstr_organization_id,
--fix bug 2210154 yawang
AND mic.category_set_id = p_category_set_id
AND mic.category_id = p_category_id
-- Following union clause added for bug 4357322
UNION
SELECT p_organization_id, -1
FROM dual where NOT EXISTS
( SELECT 1 FROM mtl_system_items_kfv msik, mtl_item_categories mic
WHERE msik.organization_id = p_organization_id
AND msik.concatenated_segments
BETWEEN NVL(p_item_from,msik.concatenated_segments)
AND NVL(p_item_to,msik.concatenated_segments)
AND mic.inventory_item_id = msik.inventory_item_id
AND mic.organization_id = msik.organization_id
AND mic.category_set_id = p_category_set_id
AND mic.category_id = p_category_id
)
;
SELECT msi.organization_id
, msi.inventory_item_id
, msi.segment1
FROM mtl_item_categories mic
, mtl_system_items_b msi
WHERE msi.organization_id = p_organization_id
AND msi.segment1
BETWEEN NVL(p_item_from, msi.segment1) AND NVL(p_item_to,msi.segment1)
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id --p_mstr_organization_id,fix bug 2210154 yawang
AND mic.category_set_id = p_category_set_id
AND mic.category_id = p_category_id;
x_item_tbl.DELETE;
SELECT master_organization_id
INTO l_master_organization_id
FROM mtl_parameters
WHERE organization_id = p_organization_tbl(1).id;
SELECT category_set_id
INTO l_category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = l_func_area_id_1; -- Bug 8926294