The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('B', 'I');
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(key) = UPPER(p_key)
AND language = p_language);
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(key) = UPPER(p_key)
AND language = p_language
AND (rt_category_id = p_cat_id OR
rt_category_id = 0));
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(attribute_name) = UPPER(p_name)
AND language = p_language);
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(attribute_name) = UPPER(p_name)
AND language = p_language
AND (rt_category_id = p_cat_id OR
rt_category_id = 0));
SELECT COUNT(*)
INTO l_num_val
FROM icx_cat_attributes_tl
WHERE rt_category_id = 0
AND language = p_language
AND to_char(type) = p_type
AND attribute_id > 100;
SELECT instr(section_map, '0', l_start_pos, 1)
INTO l_first_zero_pos
FROM icx_cat_categories_tl
WHERE rt_category_id = 0
AND language = p_language;
SELECT COUNT(*)
INTO l_num_val
FROM icx_cat_attributes_tl
WHERE rt_category_id = p_cat_id
AND language = p_language
AND to_char(type) = p_type;
SELECT instr(section_map, '0', l_start_pos, 1)
INTO l_first_zero_pos
FROM icx_cat_categories_tl
WHERE rt_category_id = p_cat_id
AND language = p_language;
insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
'ICX_CAT_KEY', p_key);
insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
'ICX_CAT_NAME', p_name);
insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
'ICX_CAT_TYPE', p_type);
insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
'ICX_CAT_DESCRIPTION', p_description);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_KEY', p_key);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_NAME', p_name);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_TYPE', p_type);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_DESCRIPTION', p_description);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_OWNER_KEY', p_owner_key);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_OWNER_NAME', p_owner_name);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_SEQUENCE', p_sequence);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_SEARCHABLE', p_searchable);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_ITEM_DETAIL_VISIBLE', p_item_detail_visible);
insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
'ICX_CAT_SEARCH_RESULTS_VISIBLE', p_search_results_visible);
insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
'ICX_CAT_PARENT_KEY', p_parent_key);
insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
'ICX_CAT_PARENT_NAME', p_parent_name);
insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
'ICX_CAT_CHILD_KEY', p_child_key);
insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
'ICX_CAT_CHILD_NAME', p_child_name);
PROCEDURE insert_failed_line
(
p_request_id IN NUMBER,
p_line_number IN NUMBER,
p_action IN VARCHAR2,
p_row_type IN VARCHAR2,
p_descriptor_key IN VARCHAR2,
p_descriptor_value IN VARCHAR2
)
IS
l_err_loc PLS_INTEGER;
INSERT INTO icx_por_failed_lines
(job_number, line_number, action, row_type, descriptor_key, descriptor_value,
request_id, program_id, program_application_id, program_login_id)
VALUES (p_request_id, p_line_number, p_action, p_row_type, p_descriptor_key,
p_descriptor_value, p_request_id, fnd_global.conc_program_id,
fnd_global.prog_appl_id, fnd_global.conc_login_id);
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.insert_failed_line(' ||
l_err_loc || '), ' || SQLERRM);
END insert_failed_line;
PROCEDURE insert_failed_message
(
p_request_id IN NUMBER,
p_descriptor_key IN VARCHAR2,
p_message_name IN VARCHAR2,
p_line_number IN NUMBER
)
IS
l_err_loc PLS_INTEGER;
INSERT INTO icx_por_failed_line_messages
(job_number, descriptor_key, message_name, line_number, request_id, program_id,
program_application_id, program_login_id)
VALUES (p_request_id, p_descriptor_key, p_message_name, p_line_number, p_request_id,
fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.insert_failed_message(' ||
l_err_loc || '), ' || SQLERRM);
END insert_failed_message;
SELECT icx_por_categoryid.nextval
INTO x_category_id
FROM dual;
INSERT INTO icx_cat_categories_tl
(rt_category_id, language, source_lang, category_name, description, type,
key, upper_key, upper_category_name, request_id, rebuild_flag, section_map,
created_by, creation_date, last_updated_by, last_update_date, last_update_login,
program_id, program_application_id, program_login_id)
VALUES (x_category_id, language_row.language_code, p_language, p_name,
p_description, p_type, p_key, upper(p_key), upper(p_name), p_request_id,
'N', lpad('0', '300', '0'), p_user_id, sysdate, p_user_id, sysdate, p_login_id,
fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
PROCEDURE update_category
(
p_category_id IN NUMBER,
p_language IN VARCHAR2,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_type IN NUMBER,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER
)
IS
l_err_loc PLS_INTEGER;
SELECT language
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
and type = p_type
and source_lang = p_language
and source_lang <> language
UNION
SELECT p_language FROM dual;
UPDATE icx_cat_categories_tl
SET category_name = nvl(p_name, category_name),
upper_category_name = nvl(upper(p_name), upper_category_name),
description = decode(p_description, '#DEL', null, null, description, p_description),
source_lang = p_language,
last_updated_by = p_user_id,
last_update_date = sysdate,
last_update_login = p_login_id,
request_id = p_request_id,
rebuild_flag = decode(p_name, category_name, rebuild_flag, null, rebuild_flag, 'Y'),
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE rt_category_id = p_category_id
AND language = l_lang_table(i);
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ip_category_name = p_name
WHERE ip_category_id = p_category_id
AND language = l_lang_table(i);
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_category(' ||
l_err_loc || '), ' || SQLERRM);
END update_category;
PROCEDURE delete_category
(
p_category_id IN NUMBER,
p_language IN VARCHAR2,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER
)
IS
l_err_loc PLS_INTEGER;
SELECT browse.child_category_id, cat.type, count(*)
FROM icx_cat_browse_trees browse, icx_cat_categories_tl cat
WHERE browse.child_category_id IN
(SELECT child_category_id
FROM icx_cat_browse_trees
WHERE parent_category_id = p_category_id)
AND browse.child_category_id = cat.rt_category_id
AND language = p_language
GROUP BY browse.child_category_id, cat.type;
IF (can_category_be_deleted(p_category_id) = 0) THEN
l_err_loc := 300;
DELETE FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id;
DELETE FROM icx_cat_attributes_tl
WHERE rt_category_id = p_category_id;
DELETE FROM icx_por_category_order_map
WHERE rt_category_id = p_category_id;
DELETE FROM icx_por_category_data_sources
WHERE rt_category_id = p_category_id;
DELETE FROM icx_cat_browse_trees
WHERE child_category_id = p_category_id;
DELETE FROM icx_cat_browse_trees
WHERE parent_category_id = p_category_id
AND child_category_id = l_child_cat_table(i);
INSERT INTO icx_cat_browse_trees
(parent_category_id, child_category_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, request_id,
program_id, program_application_id, program_login_id)
SELECT 0, l_child_cat_table(i), p_user_id, sysdate, p_user_id, sysdate, p_login_id, p_request_id,
fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
FROM dual
WHERE l_child_cat_type_table(i) = 1
AND l_parent_cat_count_table(i) = 1;
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_category(' ||
l_err_loc || '), ' || SQLERRM);
END delete_category;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_KEY_REQUIRED',
p_line_number);
SELECT rt_category_id, type
INTO x_category_id, l_current_type
FROM icx_cat_categories_tl
WHERE upper_key = upper(p_key)
AND language = p_language
AND rownum = 1;
x_system_action := 'UPDATE';
x_system_action := 'DELETE';
IF (p_user_action = 'DELETE') THEN
l_err_loc := 260;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_DOES_NOT_EXIST',
p_line_number);
IF (x_system_action IN ('ADD', 'UPDATE')) THEN
-- we default type to 2 if add and not provided
IF (x_system_action = 'ADD' AND p_type is null) THEN
l_err_loc := 320;
SELECT lookup_code INTO x_converted_type
FROM fnd_lookup_values
WHERE lookup_type = 'ICX_CAT_TYPE'
AND UPPER(meaning) = UPPER(p_type)
AND language = p_language;
insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_INVALID_CAT_TYPE',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CATEGORY_NAME_REQUIRED',
p_line_number);
SELECT count(1) INTO l_count
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_name);
insert_failed_message(p_request_id, 'ICX_CAT_NAME', 'ICX_CAT_CAT_NAME_NONUNIQUE_ADD',
p_line_number);
ELSIF (x_system_action = 'UPDATE') THEN
l_err_loc := 600;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_UPD_ROOT_CAT',
p_line_number);
SELECT count(1) INTO l_count
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_name)
and rt_category_id <> x_category_id;
insert_failed_message(p_request_id, 'ICX_CAT_NAME', 'ICX_CAT_CAT_NAME_NONUNIQUE_UPD',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_CANNOT_CHANGE_CAT_TYPE',
p_line_number);
ELSIF (x_system_action = 'DELETE') THEN
l_err_loc := 700;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_DEL_ROOT_CAT',
p_line_number);
IF (can_category_be_deleted(x_category_id) = 1) THEN
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_HAS_ITEMS',
p_line_number);
FUNCTION can_category_be_deleted
(
p_ip_category_id IN NUMBER
)
RETURN NUMBER
IS
l_err_loc PLS_INTEGER;
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM icx_cat_items_ctx_hdrs_tlp
WHERE ip_category_id = p_ip_category_id);
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM po_lines_all
WHERE ip_category_id = p_ip_category_id);
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM po_lines_draft_all
WHERE ip_category_id = p_ip_category_id);
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_auction_item_prices_all
WHERE ip_category_id = p_ip_category_id);
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_item_prices_interface
WHERE ip_category_id = p_ip_category_id);
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_auc_items_interface
WHERE ip_category_id = p_ip_category_id);
SELECT 1
INTO l_category_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_item_prices_interface
WHERE ip_category_name IN (SELECT category_name
FROM icx_cat_categories_tl
WHERE rt_category_id = p_ip_category_id));
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.can_category_be_deleted(' ||
l_err_loc || '), ' || SQLERRM);
END can_category_be_deleted;
SELECT type
INTO l_type
FROM icx_cat_categories_tl
WHERE rt_category_id = p_parent_id
AND rownum = 1;
INSERT INTO icx_cat_browse_trees(parent_category_id, child_category_id,
created_by, creation_date, last_updated_by, last_update_date, last_update_login,
request_id, program_id, program_application_id, program_login_id)
SELECT p_parent_id, p_child_id, p_user_id, sysdate, p_user_id, sysdate, p_login_id,
p_request_id, fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM icx_cat_browse_trees
WHERE parent_category_id = p_parent_id
AND child_category_id = p_child_id);
DELETE FROM icx_cat_browse_trees
WHERE parent_category_id = 0
AND child_category_id = p_child_id;
INSERT INTO icx_cat_browse_trees(parent_category_id, child_category_id,
created_by, creation_date, last_updated_by, last_update_date, last_update_login,
request_id, program_id, program_application_id, program_login_id)
SELECT 0, p_parent_id, p_user_id, sysdate, p_user_id, sysdate, p_login_id,
p_request_id, fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM icx_cat_browse_trees
WHERE child_category_id = p_parent_id);
PROCEDURE delete_relationship
(
p_parent_id IN NUMBER,
p_child_id IN NUMBER,
p_request_id IN NUMBER,
p_line_number IN NUMBER,
p_action IN VARCHAR2
)
IS
l_err_loc PLS_INTEGER;
SELECT type
INTO l_type
FROM icx_cat_categories_tl
WHERE rt_category_id = p_parent_id
AND rownum = 1;
DELETE FROM icx_cat_browse_trees
WHERE parent_category_id = p_parent_id
AND child_category_id = p_child_id;
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_relationship(' ||
l_err_loc || '), ' || SQLERRM);
END delete_relationship;
x_system_action := 'DELETE';
insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY', 'ICX_CAT_PARENT_KEY_NAME_REQD',
p_line_number);
SELECT rt_category_id, type
INTO x_parent_id, l_parent_type
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_parent_key)
AND language = p_language;
insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
'ICX_CAT_PARENT_KEY_NOT_EXIST', p_line_number);
SELECT rt_category_id, type
INTO l_parent_id_from_name, l_parent_type
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_parent_name)
AND language = p_language;
insert_failed_message(p_request_id, 'ICX_CAT_PARENT_NAME',
'ICX_CAT_PARENT_NAME_NOT_EXIST', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
'ICX_CAT_PARENT_KEY_NAME_DIFF', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
'ICX_CAT_ITEM_CAT_CANNOT_PARENT', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_PARENT_NAME',
'ICX_CAT_ITEM_CAT_CANNOT_PARENT', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY', 'ICX_CAT_CHILD_KEY_NAME_REQD',
p_line_number);
SELECT rt_category_id
INTO x_child_id
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_child_key)
AND language = p_language;
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
'ICX_CAT_CHILD_KEY_NOT_EXIST', p_line_number);
SELECT rt_category_id
INTO l_child_id_from_name
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_child_name)
AND language = p_language;
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
'ICX_CAT_CHILD_NAME_NOT_EXIST', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
'ICX_CAT_CHILD_KEY_NAME_DIFF', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
'ICX_CAT_SAME_PARENT_CHILD', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
'ICX_CAT_SAME_PARENT_CHILD', p_line_number);
SELECT COUNT(*)
INTO l_count
FROM icx_cat_browse_trees
WHERE child_category_id = x_parent_id
START WITH parent_category_id = x_child_id
CONNECT BY NOCYCLE PRIOR child_category_id = parent_category_id;
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
'ICX_CAT_NO_CYCLIC_RELATIONSHIP', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
'ICX_CAT_NO_CYCLIC_RELATIONSHIP', p_line_number);
SELECT 1 INTO l_count
FROM dual
WHERE EXISTS (SELECT 1 FROM icx_cat_browse_trees
WHERE parent_category_id = x_parent_id
AND child_category_id = x_child_id);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
'ICX_CAT_REL_NOT_EXISTS', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
'ICX_CAT_REL_NOT_EXISTS', p_line_number);
SELECT floor(nvl(max(sequence), 0)) + 1
INTO l_sequence
FROM icx_cat_attributes_tl
WHERE rt_category_id = p_category_id
AND language = p_language;
SELECT icx_por_descriptorid.nextval
INTO x_descriptor_id
FROM dual;
INSERT INTO icx_cat_attributes_tl (attribute_id, rt_category_id, language,
source_lang, attribute_name, description, type, key, sequence, searchable,
search_results_visible, item_detail_visible, request_id, rebuild_flag,
created_by, creation_date, last_updated_by, last_update_date, last_update_login,
program_id, program_application_id, program_login_id)
SELECT x_descriptor_id, p_category_id, fnd_languages.language_code, p_language,
p_name, decode(p_description, '#DEL', null, p_description), to_number(l_type),
p_key, l_sequence, to_number(l_searchable), l_search_results_visible,
l_item_detail_visible, p_request_id, 'N', p_user_id, sysdate,
p_user_id, sysdate, p_login_id, fnd_global.conc_program_id,
fnd_global.prog_appl_id, fnd_global.conc_login_id
FROM fnd_languages
WHERE installed_flag IN ('B', 'I');
UPDATE icx_cat_schema_versions
SET version = version + 1,
last_updated_by = p_user_id,
last_update_date = sysdate,
last_update_login = p_login_id,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE descriptor_set_id = l_attribute_id;
SELECT section_tag, stored_in_table, stored_in_column
INTO p_section_tag, p_stored_in_table, p_stored_in_column
FROM icx_cat_attributes_tl
WHERE attribute_id = p_descriptor_id
AND rownum = 1;
SELECT section_map, INSTR(section_map, '0', 1, 1)
INTO l_section_map, p_section_tag
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
AND rownum = 1;
SELECT section_map, INSTR(section_map, '0', 101, 1)
INTO l_section_map, p_section_tag
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
AND rownum = 1;
SELECT section_map, INSTR(section_map, '0', 201, 1)
INTO l_section_map, p_section_tag
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
AND rownum = 1;
UPDATE icx_cat_categories_tl
SET section_map = l_section_map,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE rt_category_id = p_category_id;
UPDATE icx_cat_attributes_tl
SET section_tag = p_section_tag,
stored_in_table = p_stored_in_table,
stored_in_column = p_stored_in_column,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE attribute_id = p_descriptor_id;
SELECT section_map
INTO l_section_map
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
AND rownum = 1;
SELECT section_tag
INTO l_section_tag
FROM icx_cat_attributes_tl
WHERE attribute_id = p_descriptor_id
AND rownum = 1;
UPDATE icx_cat_attributes_tl
SET section_tag = null,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE attribute_id = p_descriptor_id;
UPDATE icx_cat_categories_tl
SET section_map = l_section_map,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE rt_category_id = p_category_id;
PROCEDURE update_descriptor
(
p_descriptor_id IN NUMBER,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_category_id IN VARCHAR2,
p_sequence IN NUMBER,
p_search_results_visible IN VARCHAR2,
p_item_detail_visible IN VARCHAR2,
p_searchable IN VARCHAR2,
p_language IN VARCHAR2,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER
)
IS
l_err_loc PLS_INTEGER;
SELECT to_char(searchable)
INTO l_searchable
FROM icx_cat_attributes_tl
WHERE attribute_id = p_descriptor_id
AND rownum = 1;
UPDATE icx_cat_attributes_tl
SET attribute_name = nvl (p_name, attribute_name),
description = decode(p_description, '#DEL', null, null, description, p_description),
source_lang = p_language,
last_updated_by = p_user_id,
last_update_date = sysdate,
last_update_login = p_login_id,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE attribute_id = p_descriptor_id
AND language = p_language;
UPDATE icx_cat_attributes_tl
SET sequence = decode (p_sequence, '#DEL', null, null, sequence, p_sequence),
searchable = to_number(nvl(p_searchable, searchable)),
search_results_visible = to_number(nvl(p_search_results_visible, search_results_visible)),
item_detail_visible = to_number(nvl(p_item_detail_visible, item_detail_visible)),
rebuild_flag = l_rebuild_flag,
last_updated_by = p_user_id,
last_update_date = sysdate,
last_update_login = p_login_id,
request_id = p_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_login_id = fnd_global.conc_login_id
WHERE attribute_id = p_descriptor_id;
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_descriptor(' ||
l_err_loc || '), ' || SQLERRM);
END update_descriptor;
PROCEDURE delete_descriptor
(
p_descriptor_id IN NUMBER,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER
)
IS
l_err_loc PLS_INTEGER;
IF (can_descriptor_be_deleted(p_descriptor_id) = 0) THEN
l_err_loc := 120;
SELECT rt_category_id, to_char(searchable)
INTO l_category_id, l_searchable
FROM icx_cat_attributes_tl
WHERE attribute_id = p_descriptor_id
AND rownum = 1;
DELETE from icx_cat_attributes_tl
WHERE attribute_id = p_descriptor_id;
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_descriptor(' ||
l_err_loc || '), ' || SQLERRM);
END delete_descriptor;
FUNCTION can_descriptor_be_deleted
(
p_descriptor_id IN NUMBER
)
RETURN NUMBER
IS
l_err_loc PLS_INTEGER;
SELECT rt_category_id, stored_in_table, stored_in_column
INTO l_category_id, l_stored_in_table, l_stored_in_column
FROM icx_cat_attributes_tl
WHERE attribute_id = p_descriptor_id
AND rownum = 1;
'SELECT 1 '||
'FROM dual ' ||
'WHERE EXISTS ( ' ||
'SELECT 1 ' ||
'FROM ' || l_stored_in_table ||
' WHERE ' || l_stored_in_table || '.' || l_stored_in_column || ' IS NOT NULL' ||
' AND ip_category_id = decode(' || l_category_id || ', 0, ip_category_id, ' || l_category_id || '))'
INTO l_descriptor_referenced;
'SELECT 1 '||
'FROM dual ' ||
'WHERE EXISTS ( ' ||
'SELECT 1 ' ||
'FROM ' || l_draft_stored_in_table ||
' WHERE ' || l_draft_stored_in_table || '.' || l_stored_in_column || ' IS NOT NULL' ||
' AND ip_category_id = decode(' || l_category_id || ', 0, ip_category_id, ' || l_category_id || '))'
INTO l_descriptor_referenced;
SELECT 1
INTO l_descriptor_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_auction_attributes
WHERE ip_descriptor_id = p_descriptor_id
AND ip_category_id = l_category_id);
SELECT 1
INTO l_descriptor_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_auc_attributes_interface
WHERE ip_descriptor_id = p_descriptor_id
AND ip_category_id = l_category_id);
SELECT 1
INTO l_descriptor_referenced
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_attributes_interface
WHERE ip_descriptor_id = p_descriptor_id
AND ip_category_id = l_category_id);
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.can_descriptor_be_deleted(' ||
l_err_loc || '), ' || SQLERRM);
END can_descriptor_be_deleted;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_OWNER_KEY_NAME_REQD',
p_line_number);
SELECT rt_category_id
INTO x_owner_id
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_owner_key)
AND language = p_language
AND type IN (0,2);
insert_failed_message(p_request_id, 'ICX_CAT_OWNER_KEY',
'ICX_CAT_INVALID_OWNER_KEY', p_line_number);
SELECT rt_category_id
INTO l_owner_id_from_name
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_owner_name)
AND language = p_language
AND type IN (0,2);
insert_failed_message(p_request_id, 'ICX_CAT_OWNER_NAME',
'ICX_CAT_INVALID_OWNER_NAME', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_OWNER_KEY',
'ICX_CAT_OWNER_KEY_NAME_DIFF', p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESCRIPTOR_KEY_REQD',
p_line_number);
SELECT attribute_id, to_char(type)
INTO x_descriptor_id, l_current_type
FROM icx_cat_attributes_tl
WHERE UPPER(key) = UPPER(p_key)
AND language = p_language
AND rt_category_id = x_owner_id
AND rownum = 1;
x_system_action := 'UPDATE';
ELSIF (p_user_action = 'DELETE') THEN
l_err_loc := 490;
x_system_action := 'DELETE';
ELSIF (p_user_action = 'DELETE') THEN
l_err_loc := 210;
x_system_action := 'DELETE';
insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_DESC_DOES_NOT_EXIST',
p_line_number);
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(key) = UPPER(p_key)
AND language = p_language);
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(key) = UPPER(p_key)
AND language = p_language
AND (rt_category_id = x_owner_id OR
rt_category_id = 0));
insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_DESC_KEY_NONUNIQUE',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESCRIPTOR_NAME_REQD',
p_line_number);
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(attribute_name) = UPPER(p_name)
AND language = p_language);
SELECT 1
INTO l_num_val
FROM DUAL
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(attribute_name) = UPPER(p_name)
AND language = p_language
AND (rt_category_id = x_owner_id OR
rt_category_id = 0));
insert_failed_message(p_request_id,'ICX_CAT_NAME','ICX_CAT_DES_NAME_NONUNIQUE_ADD',
p_line_number);
SELECT COUNT(*)
INTO l_num_val
FROM icx_cat_attributes_tl
WHERE rt_category_id = 0
AND language = p_language
AND to_char(type) = p_type
AND attribute_id > 100;
SELECT instr(section_map, '0', l_start_pos, 1)
INTO l_first_zero_pos
FROM icx_cat_categories_tl
WHERE rt_category_id = 0
AND language = p_language;
insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_BASE_ATT_NUM_EXCEED',
p_line_number);
SELECT COUNT(*)
INTO l_num_val
FROM icx_cat_attributes_tl
WHERE rt_category_id = x_owner_id
AND language = p_language
AND to_char(type) = p_type;
SELECT instr(section_map, '0', l_start_pos, 1)
INTO l_first_zero_pos
FROM icx_cat_categories_tl
WHERE rt_category_id = x_owner_id
AND language = p_language;
insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_CAT_ATT_NUM_EXCEED',
p_line_number);
ELSIF (x_system_action = 'UPDATE') THEN
l_err_loc := 690;
SELECT 1
INTO l_num_val
FROM dual
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(attribute_name) = UPPER(p_name)
AND attribute_id <> x_descriptor_id);
SELECT 1
INTO l_num_val
FROM dual
WHERE EXISTS (SELECT 1
FROM icx_cat_attributes_tl
WHERE UPPER(attribute_name) = UPPER(p_name)
AND (rt_category_id = x_owner_id OR
rt_category_id = 0)
AND attribute_id <> x_descriptor_id);
insert_failed_message(p_request_id,'ICX_CAT_NAME','ICX_CAT_DES_NAME_NONUNIQUE_UPD',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_CANNOT_CHANGE_DES_TYPE',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SEQUENCE',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SRCHABLE',
p_line_number);
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SRV',
p_line_number);
ELSIF (x_system_action = 'DELETE') THEN
l_err_loc := 770;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_DEL_SEEDED_DESC',
p_line_number);
IF (can_descriptor_be_deleted(x_descriptor_id) = 1) THEN
l_err_loc := 800;
insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESC_HAS_ITEMS',
p_line_number);
DELETE FROM icx_por_batch_jobs
WHERE submission_datetime <= (sysdate - l_number_of_days)
AND rownum <= l_commit_size
RETURNING job_number BULK COLLECT INTO l_job_number_tbl;
DELETE FROM icx_por_failed_line_messages
WHERE job_number = l_job_number_tbl(i);
DELETE FROM icx_por_failed_lines
WHERE job_number = l_job_number_tbl(i);
DELETE FROM icx_por_contract_references
WHERE job_number = l_job_number_tbl(i);
l_job_number_tbl.DELETE;
DELETE FROM icx_cat_batch_jobs
WHERE submission_datetime <= (sysdate - l_number_of_days)
AND rownum <= l_commit_size
RETURNING job_number BULK COLLECT INTO l_job_number_tbl;
DELETE FROM icx_por_failed_line_messages
WHERE job_number = l_job_number_tbl(i);
DELETE FROM icx_por_failed_lines
WHERE job_number = l_job_number_tbl(i);
DELETE FROM icx_cat_parse_errors
WHERE request_id = l_job_number_tbl(i);
SELECT rt_category_id, category_name, language
FROM icx_cat_categories_tl
WHERE request_id = p_request_id
AND rebuild_flag = 'Y';
SELECT key, searchable
FROM icx_cat_attributes_tl
WHERE request_id = p_request_id
AND rebuild_flag = 'Y'
AND rt_category_id = 0
AND key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
'SHOPPING_CATEGORY');
SELECT distinct rt_category_id
FROM icx_cat_attributes_tl
WHERE request_id = p_request_id
AND rebuild_flag = 'Y'
AND key NOT IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
'SHOPPING_CATEGORY');
UPDATE icx_cat_categories_tl
SET rebuild_flag = null
WHERE rebuild_flag is not null;
UPDATE icx_cat_attributes_tl
SET rebuild_flag = null
WHERE rebuild_flag is not null;
update_job_status(l_request_id, 'RUNNING');
update_job_status(l_request_id, 'COMPLETED');
update_job_status(l_request_id, 'ERROR');
update_job_status(l_request_id, 'RUNNING');
update_job_status(l_request_id, 'COMPLETED');
update_job_status(l_request_id, 'ERROR');
PROCEDURE update_job_status
(
p_job_number IN NUMBER,
p_job_status IN VARCHAR2
)
IS
l_err_loc PLS_INTEGER;
UPDATE icx_cat_batch_jobs
SET job_status = p_job_status,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE job_number = p_job_number;
'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(' ||
l_err_loc || '), ' || SQLERRM);
END update_job_status;