[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select category_set_id, functional_area_id
from mtl_default_category_sets
where functional_area_id in(1,11);
INSERT INTO eni_oltp_item_star(
id,
value,
inventory_item_id,
organization_id,
inv_category_id,
inv_category_set_id,
inv_concat_seg,
vbh_category_id,
vbh_category_set_id,
vbh_concat_seg,
master_id,
creation_date,
last_update_date,
item_catalog_group_id,
primary_uom_code
)
SELECT
mti.inventory_item_id || '-' || mti.organization_id id,
mti.concatenated_segments || ' (' || mtp.organization_code || ')' value,
mti.inventory_item_id inventory_item_id,
mti.organization_id organization_id,
nvl(mic.category_id,-1) inv_category_id,
nvl(mic.category_Set_id,l_inv_category_set) inv_category_Set_id,
nvl(kfv.concatenated_segments,'Unassigned') inv_concat_seg,
nvl(mic1.category_id, -1) vbh_category_id,
nvl(mic1.category_set_id, l_vbh_category_set) vbh_category_set_id,
nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
decode(mti.organization_id,mtp.master_organization_id,null,mti.inventory_item_id || '-' || mtp.master_organization_id)
master_id,
mti.creation_date creation_date,
mti.last_update_date last_update_date,
nvl(mti.item_catalog_group_id,-1) item_catalog_group_id,
mti.primary_uom_code
FROM mtl_system_items_b_kfv mti,
mtl_parameters mtp,
mtl_item_categories mic ,
mtl_item_categories mic1 ,
mtl_categories_b_kfv kfv ,
mtl_categories_b_kfv kfv1
WHERE mtp.organization_id=mti.organization_id
AND mti.inventory_item_id = l_inventory_item_id
AND mic.organization_id(+) = mti.organization_id
AND mic.inventory_item_id(+) = mti.inventory_item_id
AND mic.category_id = kfv.category_id (+)
and mic.category_set_id(+) = l_inv_category_set
AND mic1.organization_id(+) = mti.organization_id
AND mic1.inventory_item_id(+) = mti.inventory_item_id
AND mic1.category_id = kfv1.category_id (+)
AND mic1.category_set_id(+) = l_vbh_category_set
AND NOT EXISTS(SELECT null FROM eni_oltp_item_star
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = mti.organization_id);