[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CHILD_CODE, COUNT(PARENT_CODE) COUNT
FROM ENI_VSET_HRCHY_TEMP
WHERE HRCHY_FLAG = 'Y'
GROUP BY CHILD_CODE
HAVING COUNT(PARENT_CODE) > 1;
SELECT B.SEGMENT1, COUNT(INVENTORY_ITEM_ID) NUMBER_ITEMS
FROM MTL_ITEM_CATEGORIES A, MTL_CATEGORIES_B B, ENI_VSET_HRCHY_TEMP C
WHERE A.CATEGORY_SET_ID = g_catset_id
AND A.CATEGORY_ID = b.category_id
AND B.STRUCTURE_ID = g_struct_id
AND B.SEGMENT1 = C.PARENT_CODE
AND C.HRCHY_FLAG = 'Y'
GROUP BY B.SEGMENT1;
SELECT CHILD_CODE FROM ENI_VSET_HRCHY_TEMP
WHERE HRCHY_FLAG = 'Y'
MINUS
SELECT SEGMENT1 FROM MTL_CATEGORIES_B
WHERE STRUCTURE_ID = g_struct_id;
SELECT A.segment1 INTO l_catg
FROM mtl_categories_B A,
mtl_category_sets_b B -- ,ENI_VSET_HRCHY_TEMP C
WHERE A.category_id = B.default_category_id
AND A.structure_id = B.structure_id
AND B.CATEGORY_SET_ID = g_catset_id
AND NOT EXISTS (SELECT child_code FROM eni_vset_hrchy_temp
WHERE child_code = a.segment1
AND hrchy_flag = 'Y');
SELECT a.segment1 INTO l_catg
FROM mtl_categories_b a,
mtl_category_sets_b b, eni_vset_hrchy_temp c
WHERE a.category_id = b.default_category_id
AND a.structure_id = b.structure_id
AND b.category_set_id = g_catset_id
AND a.segment1 = c.parent_code
AND c.hrchy_flag = 'Y'
AND ROWNUM = 1;
PROCEDURE UPDATE_CATSET_FROM_VSET (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_top_node IN VARCHAR2,
p_validation_mode IN VARCHAR2) IS
l_cnt NUMBER;
l_insert NUMBER;
l_update NUMBER;
SELECT segment1, a.category_id, b.category_set_id
FROM mtl_categories_b a, mtl_category_set_valid_cats b, mtl_category_sets_b c
WHERE a.structure_id = g_struct_id
AND b.category_set_id = g_catset_id
AND a.category_id = b.category_id
AND a.structure_id = c.structure_id
AND b.category_set_id = c.category_set_id
AND a.category_id <> c.default_category_id
AND a.category_id NOT IN (SELECT category_id
FROM mtl_item_categories
WHERE category_id = a.category_id
AND category_set_id = b.category_set_id
AND ROWNUM = 1)
AND NOT EXISTS(SELECT child_code FROM eni_vset_hrchy_temp
WHERE a.segment1 = child_code
AND hrchy_flag = 'Y'
AND child_code <> p_top_node);
SELECT segment1, a.category_id --, b.category_set_id
FROM mtl_categories_b a
WHERE structure_id = g_struct_id
AND EXISTS (SELECT 'X' FROM mtl_item_categories b
WHERE a.category_id = b.category_id
AND b.category_set_id = g_catset_id)
AND NOT EXISTS(SELECT child_code FROM eni_vset_hrchy_temp
WHERE A.segment1 = child_code
AND hrchy_flag = 'Y')
AND p_validation_mode = 'Y';
SELECT a.category_id, a.segment1, flag
FROM (
SELECT a.category_id, segment1, 1 flag -- create in valid cats
FROM mtl_categories_b a
WHERE a.structure_id = g_struct_id
AND NOT EXISTS(
SELECT child_code FROM eni_vset_hrchy_temp
WHERE hrchy_flag = 'Y'
AND child_code = a.segment1)
AND NOT EXISTS(
SELECT category_id FROM mtl_category_set_valid_cats
WHERE a.category_id = category_id
AND category_set_id = g_catset_id)
UNION ALL
SELECT a.category_id, b.segment1, 2 flag -- update in valid cats
FROM mtl_category_set_valid_cats a, mtl_categories_b b
WHERE a.category_set_id = g_catset_id
AND a.category_id = b.category_id
AND b.structure_id = g_struct_id
-- AND NOT EXISTS(
-- SELECT child_code FROM eni_vset_hrchy_temp
-- WHERE hrchy_flag = 'Y'
-- AND child_code = b.segment1)
) a
WHERE EXISTS(
SELECT category_id FROM mtl_item_categories b
WHERE a.category_id = b.category_id
AND b.category_set_id = g_catset_id)
UNION ALL
SELECT category_id, segment1,2 flag
FROM
mtl_categories_b
WHERE
category_id = g_default_cat_id
AND NOT EXISTS (
SELECT b.category_id
FROM
mtl_item_categories b
WHERE b.category_id = g_default_cat_id
AND ROWNUM = 1
);
SELECT 1 exist_flag FROM DUAL
WHERE NOT EXISTS(SELECT child_code
FROM eni_vset_hrchy_temp
WHERE child_code = segment
AND hrchy_flag = 'Y'
AND rownum = 1)
AND p_validation_mode = 'N';
SELECT
v.category_id VSET_CHILD_ID,
v.segment1 VSET_CHILD_CODE,
DECODE(v1.category_id,l_catg,NULL, v1.category_id) VSET_PARENT_ID,
h.category_id CAT_CHILD_ID,
h.parent_category_id CAT_PARENT_ID,
g_catset_id CATEGORY_SET_ID
FROM eni_vset_hrchy_temp f, mtl_categories_b v,
mtl_categories_b v1, mtl_category_set_valid_cats h
WHERE v.structure_id = g_struct_id
AND v1.structure_id(+) = g_struct_id
AND f.child_code = v.segment1
AND f.parent_code = v1.segment1(+)
AND f.hrchy_flag = 'Y'
AND h.category_set_id(+) = g_catset_id
AND h.category_id(+) = v.category_id
AND V.segment1 <> p_top_node;
SELECT STRUCTURE_ID,DEFAULT_CATEGORY_ID INTO g_struct_id,g_default_cat_id
FROM MTL_CATEGORY_SETS_B
WHERE CATEGORY_SET_ID = g_catset_id;
INSERT INTO ENI_VSET_HRCHY_TEMP(
CHILD_CODE,
PARENT_CODE,
HRCHY_FLAG)
SELECT
FLEX_VALUE CHILD_CODE,
PARENT_FLEX_VALUE PARENT_CODE,
'N'
FROM FND_FLEX_VALUE_CHILDREN_V
WHERE FLEX_VALUE_SET_ID = g_value_set_id
AND FLEX_VALUE <> p_top_node
UNION ALL
SELECT p_top_node, NULL, 'N' FROM DUAL;
INSERT INTO ENI_VSET_HRCHY_TEMP (
CHILD_CODE,
PARENT_CODE,
HRCHY_FLAG)
SELECT CHILD_CODE, PARENT_CODE, 'Y'
FROM ENI_VSET_HRCHY_TEMP H
CONNECT BY PRIOR CHILD_CODE = PARENT_CODE
START WITH CHILD_CODE = p_top_node;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted: ' || sql%rowcount);
INV_ITEM_CATEGORY_PVT.Update_Valid_Category(
p_api_version => 1,
p_category_set_id => g_catset_id,
p_category_id => i.category_id,
p_parent_category_id => null,
x_return_status => l_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'DELETED CATEGORIES: Removing categories from the default category ');
SELECT COUNT(CATEGORY_ID) INTO l_catg
FROM MTL_CATEGORY_SET_VALID_CATS
WHERE CATEGORY_SET_ID = i.CATEGORY_SET_ID
AND CATEGORY_ID = i.CATEGORY_ID;
INV_ITEM_CATEGORY_PUB.Delete_Valid_Category(
p_api_version => 1,
p_category_set_id => i.category_set_id,
p_category_id => i.category_id,
x_return_status => l_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_insert := 0;
l_update := 0;
SELECT category_id INTO l_catg
FROM mtl_categories_b
WHERE structure_id = g_struct_id
AND segment1 = p_top_node;
IF l_insert = 0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, '');
l_insert := 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while inserting '||i.vset_child_code||' into product hierarchy');
IF l_update = 0 then
FND_FILE.PUT_LINE(FND_FILE.LOG, '');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'UPDATED CATEGORIES: Updating categories with the new parent-child relationship in the value set');
l_update := 1;
INV_ITEM_CATEGORY_PVT.Update_Valid_Category(
p_api_version => 1,
p_category_set_id => i.category_set_id,
p_category_id => i.vset_child_id,
p_parent_category_id => i.vset_parent_id,
x_return_status => l_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);