[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select language_code
from fnd_languages
where installed_flag in ('B', 'I');
** Synopsis : Update the ctx_ columns for items belong to
** those categories which own rebuild_flags or their
** local descriptors' rebuild_flags are set to 'Y.'
**
** Parameter: p_request_id - number of the job to rebuild
**/
PROCEDURE populate_ctx_desc_indexes(p_request_id IN INTEGER := -1) IS
xErrLoc NUMBER := 0; -- execution location for error trapping
SELECT distinct catid
FROM (
SELECT
dl.rt_category_id catid
FROM icx_cat_descriptors_tl dl
WHERE dl.request_id = p_request_id
--OEX_IP_PORTING AND dl.class = 'ICX_CAT_ATTR'
AND dl.rebuild_flag = 'Y'
AND dl.rt_category_id<>0
UNION
SELECT ctl.rt_category_id catid
FROM icx_cat_categories_tl ctl
where ctl.request_id = p_request_id
AND ctl.rebuild_flag in ('D','B')
AND ctl.rt_category_id<>0
);
SELECT 1
INTO rebuildBase
FROM dual
WHERE EXISTS
(SELECT 1
FROM icx_cat_descriptors_tl
WHERE request_id = p_request_id
AND rebuild_flag = 'Y'
AND rt_category_id = 0)
--OEX_IP_PORTING AND class = 'ICX_BASE_ATTR')
OR EXISTS
(SELECT 1
FROM icx_cat_categories_tl
WHERE request_id = p_request_id
AND rebuild_flag = 'D'
AND rt_category_id = 0);
SELECT tl.rowid, tl.rt_item_id, tl.language
FROM icx_cat_items_tlp tl,
icx_cat_categories_tl ctl
where tl.primary_category_id = ctl.rt_category_id
and tl.language = ctl.language
and ctl.request_id = p_request_id
AND ctl.rebuild_flag in ('C','B')
AND ctl.rt_category_id<>0;
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_EXT_ITEMS_TLP '||
' WHERE rt_category_id = :catid';
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_POR_C'||vCategoryId||'_TL';
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_POR_ITEMS_TL '||
' WHERE primary_category_id = :catid)';
UPDATE icx_cat_categories_tl
SET rebuild_flag = NULL
WHERE request_id = p_request_id;
UPDATE icx_cat_descriptors_tl
SET rebuild_flag = NULL
WHERE request_id = p_request_id;
** Update the ctx_ columns for items belong to
** those categories which own rebuild_flags or their
** local descriptors' rebuild_flags are set to 'Y.'
**
** Parameter: p_request_id - number of the job to rebuild
**/
PROCEDURE populate_ctx_desc_indexes(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_request_id IN INTEGER := -1) IS
BEGIN
retcode := 0;
select type
into xType
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,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
CREATED_BY, CREATION_DATE)
SELECT p_parent_id, p_child_id,
p_login_id, p_user_id, sysdate, p_user_id, sysdate
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
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,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
CREATED_BY, CREATION_DATE)
SELECT 0, p_parent_id, p_login_id, p_user_id, sysdate, p_user_id, sysdate
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM icx_cat_browse_trees
WHERE child_category_id = p_parent_id);
** Proc : delete_child_category
** Desc : Delete a category as a child of another category.
** Also this parent category should be navigation type.
**/
PROCEDURE delete_child_category (p_parent_id IN NUMBER,
p_child_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
select type
into xType
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_POR_SCHEMA_UPLOAD.delete_child_category('
|| xErrLoc || '): ' || SQLERRM);
END delete_child_category;
select icx_por_categoryid.nextval
into p_category_id
from DUAL;
INSERT INTO ICX_CAT_CATEGORIES_TL
(RT_CATEGORY_ID, LANGUAGE, SOURCE_LANG, CATEGORY_NAME,
DESCRIPTION, TYPE, KEY, UPPER_KEY, TITLE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, UPPER_CATEGORY_NAME, REQUEST_ID, REBUILD_FLAG,SECTION_MAP)
VALUES
(p_category_id, language_row.language_code, p_language, p_name,
p_description, p_type, p_key, upper(p_key), NULL, p_user_id,
SYSDATE, p_user_id, SYSDATE, p_login_id, upper(p_name), p_request_id, 'N',
lpad('0','300','0'));
select floor(max(sequence))+1 into l_sequence
from icx_cat_descriptors_tl
where rt_category_id = p_category_id
and language = p_language;
select icx_por_descriptorid.nextval
into p_descriptor_id
from dual;
** Desc : Insert a new local descriptor into icx_cat_descriptors_tl.
** This method simply pulls out the metadata section of a descriptor
** that gets inserted into ICX_DESCRIPTORS_TL. This is done to
** separate the insertion of data, from the creation of a dynamic
** table. And is called directly in online category creation.
** This method assumes everything is validated before
** calling. And it will create rows for each installed language.
**/
/* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
PROCEDURE create_descriptor_metadata (p_descriptor_id IN NUMBER,
p_key IN VARCHAR2,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_type IN VARCHAR2,
p_sequence IN NUMBER,
p_search_results_visible IN VARCHAR2,
p_item_detail_visible IN VARCHAR2,
p_searchable IN VARCHAR2,
p_required IN VARCHAR2,
p_refinable IN VARCHAR2,
p_multivalue IN VARCHAR2,
p_default_value IN VARCHAR2,
p_language IN VARCHAR2,
p_category_id IN NUMBER,
p_request_id IN NUMBER DEFAULT -1,
p_rebuild_flag IN VARCHAR2,
p_descriptor_id_out OUT NOCOPY NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
select icx_por_descriptorid.nextval
into p_descriptor_id_out
from dual;
INSERT INTO icx_cat_descriptors_tl
(RT_DESCRIPTOR_ID, RT_CATEGORY_ID, LANGUAGE, SOURCE_LANG,
DESCRIPTOR_NAME, DESCRIPTION, TYPE, KEY, TITLE, SEQUENCE,
DEFAULTVALUE, MULTI_VALUE_TYPE, MULTI_VALUE_KEY,
REQUIRED, REFINABLE, SEARCHABLE, SEARCH_RESULTS_VISIBLE,
ITEM_DETAIL_VISIBLE , CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
request_id, REBUILD_FLAG, MULTIVALUE,CUSTOMIZATION_LEVEL, CLASS)
SELECT p_descriptor_id_out, p_category_id,
fnd_languages.language_code, p_language,
p_name,
decode(p_description,'#DEL',null,p_description),
to_number(p_type), p_key, NULL, p_sequence,
p_default_value, NULL, NULL,
p_required, p_refinable, to_number(p_searchable), p_search_results_visible,
p_item_detail_visible, p_user_id, sysdate,
p_user_id, sysdate, p_login_id, p_request_id, p_rebuild_flag, p_multivalue,
DECODE(p_type, URL_TYPE, '111011', DATE_TYPE, '111011', '111111'),
xClass
FROM fnd_languages
WHERE installed_flag in ('B', 'I');
** Proc : update_category
** Desc : Update an existing category for a sepcified language.
** If parent is specified, a new link will be created.
**/
/* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
PROCEDURE update_category (p_category_id IN NUMBER,
p_language IN VARCHAR2,
p_name IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_type IN NUMBER DEFAULT -1,
p_parent_id IN NUMBER DEFAULT -1,
p_request_id IN NUMBER DEFAULT -1,
p_user_id IN NUMBER,
p_login_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
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,
--p_name=CATEGORY_NAME, no change
rebuild_flag,
--p_name is null which means category name is not changed
null,
rebuild_flag,
--p_name<>CATEGORY_NAME, category name changed
decode(rebuild_flag,'D','B','B','B','C'))
WHERE RT_CATEGORY_ID = p_category_id
AND LANGUAGE = xLangArray(i);
UPDATE ICX_CAT_ITEMS_TLP
SET primary_category_name = p_name
WHERE primary_category_id = p_category_id
AND language = xLangArray(i)
AND primary_category_name <> p_name
AND rownum <= xCommitSize ;
UPDATE ICX_CAT_CATEGORIES_TL
SET TYPE = decode(p_type, -1, TYPE, p_type),
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = 0,
REQUEST_ID = p_request_id
WHERE RT_CATEGORY_ID = p_category_id;
'Exception at ICX_POR_SCHEMA_UPLOAD.update_category('
|| xErrLoc || '): ' || SQLERRM);
END update_category;
** Proc : update_descriptor
** Desc : Update a existing local descriptor for a specified language
** within a category.
**/
/* OEX_IP_PORTING: added 3 p-arameters for section tag, storedintable, storedincolumn */
/* Changes for userId, loginId Added two parameters p_user_id, p_login_id */
PROCEDURE update_descriptor (p_descriptor_id IN NUMBER,
p_language IN VARCHAR2,
p_name IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_default_value IN VARCHAR2 DEFAULT NULL,
p_sequence IN VARCHAR2 DEFAULT NULL,
p_search_results_visible IN VARCHAR2 DEFAULT NULL,
p_item_detail_visible IN VARCHAR2 DEFAULT NULL,
p_searchable IN VARCHAR2 DEFAULT NULL,
p_required IN VARCHAR2 DEFAULT NULL,
p_refinable IN VARCHAR2 DEFAULT NULL,
p_multivalue IN VARCHAR2 DEFAULT NULL,
p_request_id IN NUMBER DEFAULT -1,
p_section_tag OUT NOCOPY NUMBER,
p_stored_in_table OUT NOCOPY VARCHAR2 ,
p_stored_in_column OUT NOCOPY VARCHAR2 ,
p_user_id IN NUMBER,
p_login_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
select rt_category_id, to_char(searchable), to_char(type)
into xCategoryID, xSearchable, xType
from icx_cat_descriptors_tl
where rt_descriptor_id = p_descriptor_id
--OEX_IP_PORTING AND class IN ('ICX_BASE_ATTR','IPD_BASE_ATTR', 'POM_PRICE_ATTR', 'ICX_CAT_ATTR')
and rownum = 1;
UPDATE icx_cat_descriptors_tl
SET DESCRIPTOR_NAME = NVL(p_name, DESCRIPTOR_NAME),
DESCRIPTION = decode(p_description,'#DEL',null, null, DESCRIPTION,p_description),
SOURCE_LANG = p_language,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = p_login_id,
REQUEST_ID = p_request_id
WHERE RT_DESCRIPTOR_ID = p_descriptor_id
AND LANGUAGE = p_language;
UPDATE icx_cat_descriptors_tl
SET DEFAULTVALUE = NVL(p_default_value, DEFAULTVALUE),
SEQUENCE = decode(p_sequence,null,sequence,'#DEL',null,to_number(p_sequence)),
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)),
SEARCHABLE = TO_NUMBER(NVL(p_searchable, SEARCHABLE)),
REQUIRED = TO_NUMBER(NVL(p_required, REQUIRED)),
MULTIVALUE = TO_NUMBER(NVL(p_multivalue, MULTIVALUE)),
REFINABLE = TO_NUMBER(NVL(p_refinable, REFINABLE)),
CREATION_DATE = sysdate,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = p_login_id,
REQUEST_ID = p_request_id,
REBUILD_FLAG = xRebuildFlag
WHERE RT_DESCRIPTOR_ID = p_descriptor_id;
'Exception at ICX_POR_SCHEMA_UPLOAD.update_descriptor('
|| xErrLoc || '): ' || SQLERRM);
END update_descriptor;
** Proc : delete_category
** Desc : Delete category from icx_cat_categories_tl;
** Delete local descriptors from icx_cat_descriptors_tl;
** Delete links from icx_cat_browse_trees;
PROCEDURE delete_category (p_category_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
select child_category_id from icx_cat_browse_trees
where parent_category_id = p_category_id;
delete from icx_cat_categories_tl
where rt_category_id = p_category_id;
delete from icx_cat_descriptors_tl
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 child_category_id = c_child.child_category_id
and parent_category_id = p_category_id;
select count(*) into l_count
from icx_cat_browse_trees
where child_category_id = c_child.child_category_id;
SELECT type INTO l_type
FROM icx_cat_categories_tl
WHERE rt_category_id = c_child.child_category_id
AND language = USERENV('LANG');
INSERT INTO icx_cat_browse_trees
(parent_category_id, child_category_id,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
CREATED_BY, CREATION_DATE)
VALUES
(0, c_child.child_category_id, 0, 0, sysdate, 0, sysdate);
'Exception at ICX_POR_SCHEMA_UPLOAD.delete_category('
|| xErrLoc || '): ' || SQLERRM);
END delete_category;
** Proc : delete_category_tree
** Desc : Navigate the subtree, delete the whole subtree and items
** associated.
**/
PROCEDURE delete_category_tree (p_category_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
select distinct cat.rt_category_id,
cat.type
from icx_cat_categories_tl cat,
icx_cat_browse_trees toc
where toc.parent_category_id = p_category_id
and toc.child_category_id = cat.rt_category_id;
delete_category(p_category_id);
delete_category_tree(categories.rt_category_id);
delete_category(categories.rt_category_id);
'Exception at ICX_POR_SCHEMA_UPLOAD.delete_category_tree('
|| xErrLoc || '): ' || SQLERRM);
END delete_category_tree;
** Proc : delete_descriptor
** Desc : Delete the local descriptor within a category.
** If no local descriptors for this category, the
** dynamic table and view will be dropped.
**/
--Bug#3027134 Added who columns in icx_cat_deleted_attributes
--as part of ECM OA Rewrite
--So add two parameters for user_id and login_id to delete_descriptors
--to populate the who columns in icx_cat_deleted_attributes.
PROCEDURE delete_descriptor (p_descriptor_id IN NUMBER,
p_request_id IN NUMBER DEFAULT -1,
p_user_id IN NUMBER,
p_login_id IN NUMBER)
IS
xErrLoc INTEGER := 0;
select rt_category_id, to_char(searchable), to_char(type),
stored_in_table, stored_in_column
into xCategoryID, xSearchable, xType,
xStoredInTable, xStoredInColumn
from icx_cat_descriptors_tl
where rt_descriptor_id = p_descriptor_id
--OEX_IP_PORTING AND class IN ('ICX_BASE_ATTR', 'IPD_BASE_ATTR', 'POM_PRICE_ATTR', 'ICX_CAT_ATTR')
and rownum = 1;
delete from icx_cat_descriptors_tl
where rt_descriptor_id = p_descriptor_id;
update icx_cat_categories_tl
set rebuild_flag = decode(rebuild_flag,'C','B','B','B','D'),
REQUEST_ID =p_request_id
where rt_category_id = xCategoryID;
insert into icx_cat_deleted_attributes
(rt_category_id, rt_descriptor_id, stored_in_table, stored_in_column,
last_update_login, last_updated_by, last_update_date, created_by,
creation_date)
values (xCategoryID, p_descriptor_id, xStoredInTable, xStoredInColumn,
p_login_id, p_user_id, sysdate, p_user_id, sysdate);
'Exception at ICX_POR_SCHEMA_UPLOAD.delete_descriptor('
|| xErrLoc || '): ' || SQLERRM);
END delete_descriptor;
IF (p_user_action IN ('SYNC', 'DELETE')) THEN
-- Check key is not null and is unique
l_progress := '001_0';
InsertError(p_request_id, 'ICX_POR_CAT_ATTRIB_OWNER_KEY', 'ICX_POR_INVALID_CATEGORY',
p_line_number);
SELECT rt_category_id INTO p_owner_id
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_owner_key)
AND language = p_language
AND type IN (0,2);
InsertError(p_request_id, 'ICX_POR_CAT_ATTRIB_OWNER_KEY',
'ICX_POR_INVALID_CATEGORY', p_line_number);
SELECT rt_category_id INTO p_owner_id
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_owner_name)
AND language = p_language
AND type IN (0,2);
InsertError(p_request_id, 'ICX_POR_CAT_ATTRIB_OWNER_NAME',
'ICX_POR_INVALID_CATEGORY', p_line_number);
InsertError(p_request_id, 'ICX_POR_ATTRIB_KEY', 'ICX_POR_CAT_FIELD_REQUIRED', p_line_number);
ELSIF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
SELECT rt_descriptor_id, rt_category_id, to_char(type),
descriptor_name, sequence, search_results_visible,
to_char(searchable), item_detail_visible, class,
to_char(required), multivalue, customization_level
INTO p_descriptor_id, p_owner_id, l_current_type,
l_name, l_sequence, l_searchvisible, l_searchable,
l_detailvisible, l_class, l_required, l_multivalue,
l_customizelevel
FROM icx_cat_descriptors_tl
WHERE UPPER(key) = UPPER(p_key)
AND language = p_language
AND rt_category_id = p_owner_id
--OEX_IP_PORTING AND class in('ICX_BASE_ATTR','IPD_BASE_ATTR','POM_PRICE_ATTR','ICX_CAT_ATTR')
AND rownum = 1;
p_system_action := 'UPDATE';
ELSIF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
ELSIF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_CAT_INVALID_ATTRIB',
p_line_number);
ELSIF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
InsertError(p_request_id, 'ICX_POR_ATTRIB_NAME', 'ICX_POR_CAT_FIELD_REQUIRED',
p_line_number);
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_descriptors_tl
WHERE UPPER(descriptor_name) = UPPER(p_name));
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_descriptors_tl
WHERE UPPER(descriptor_name) = UPPER(p_name)
AND (rt_category_id = p_owner_id OR rt_category_id = 0));
InsertError(p_request_id,'ICX_POR_ATTRIB_NAME','ICX_POR_ATTRIB_NAME_UNIQUE_M',
p_line_number);
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_descriptors_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_descriptors_tl
WHERE UPPER(key) = UPPER(p_key)
AND (rt_category_id = p_owner_id OR rt_category_id = 0)
AND language = p_language);
InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_ATTRIB_KEY_UNIQUE_M',
p_line_number);
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_category_items);
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_category_items
WHERE rt_category_id = p_owner_id);
InsertError(p_request_id,'ICX_POR_CAT_ATTRIB_REQUIRED','ICX_POR_BASE_N_ADD_REQD',p_line_number);
InsertError(p_request_id,'ICX_POR_CAT_ATTRIB_REQUIRED','ICX_POR_CAT_N_ADD_REQD',p_line_number);
SELECT COUNT(*) INTO l_num_val
FROM icx_cat_descriptors_tl
WHERE rt_category_id = 0
AND language = p_language
AND to_char(type) = p_type; -- OEX_IP_PORTING
InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_BASE_ATT_NUM_EXCEED',
p_line_number);
SELECT COUNT(*) INTO l_num_val
FROM icx_cat_descriptors_tl
WHERE rt_category_id = p_owner_id
AND language = p_language
AND to_char(type) = p_type; -- OEX_IP_PORTING
InsertError(p_request_id,'ICX_POR_ATTRIB_KEY','ICX_POR_CAT_ATT_NUM_EXCEED',
p_line_number);
ELSIF (p_system_action = 'UPDATE') THEN
-- Update Action
-- If specified, check name is not already used by another category
-- We only check the other categories so the user can specify the same name
-- during update
-- jinwang l_progress := '005_0';
InsertError(p_request_id, 'ICX_POR_ATTRIB_KEY', 'POM_CAT_CHANGE_PRICE_ATTR',
p_line_number);
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_descriptors_tl
WHERE UPPER(descriptor_name) = UPPER(p_name)
AND rt_descriptor_id <> p_descriptor_id);
SELECT 1 INTO l_num_val
FROM dual WHERE EXISTS (
SELECT 1 FROM icx_cat_descriptors_tl
WHERE UPPER(descriptor_name) = UPPER(p_name)
AND (rt_category_id = p_owner_id OR rt_category_id = 0)
AND rt_descriptor_id <> p_descriptor_id);
InsertError(p_request_id, 'ICX_POR_ATTRIB_NAME', 'ICX_POR_ATTRIB_NAME_UNIQUE_M',
p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_CHANGE_TYPE',
p_line_number);
InsertError(p_request_id, 'ICX_POR_ATTRIB_NAME', 'ICX_POR_CAT_DELETE_RESERVED',
p_line_number);
InsertError(p_request_id, 'ICX_POR_ATTRIB_KEY', 'ICX_POR_CAT_DELETE_RESERVED',
p_line_number);
IF (p_user_action IN ('SYNC', 'DELETE')) THEN
-- Check key is not null
l_progress := '000';
InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_FIELD_REQUIRED',
p_line_number);
IF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
SELECT rt_category_id, type INTO p_category_id, l_current_type
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_key)
AND language = p_language
AND rownum = 1;
p_system_action := 'UPDATE';
ELSIF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
ELSIF (p_user_action = 'DELETE') THEN
p_system_action := 'DELETE';
InsertError(p_request_id,'ICX_POR_CATEGORY_KEY','ICX_POR_INVALID_CATEGORY',
p_line_number);
InsertError(p_request_id, 'ICX_POR_CATEGORY_NAME', 'ICX_POR_CAT_FIELD_REQUIRED',
p_line_number);
SELECT count(1) INTO l_num_val
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_name);
InsertError(p_request_id, 'ICX_POR_CATEGORY_NAME', 'ICX_POR_CAT_NAME_UNIQUE_M',
p_line_number);
ELSIF (p_system_action = 'UPDATE') THEN
-- Update Action
-- No need to check if key exists, since if it doesn't it would have
-- been an 'ADD' action
-- If specified, check name is not already used by another category
-- We only check the other categories so the user can specify the same name
-- during update
l_progress := '006';
SELECT count(1) INTO l_num_val
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_name)
--AND language = p_language
AND rt_category_id <> p_category_id;
InsertError(p_request_id, 'ICX_POR_CATEGORY_NAME', 'ICX_POR_CAT_NAME_UNIQUE_M',
p_line_number);
InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_ROOT_DELETE',
p_line_number);
ELSIF (p_system_action = 'DELETE') THEN
-- jinwang
-- validate rule: root category can not be deleted
IF (p_key IS NOT NULL AND p_category_id = 0) THEN
InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_ROOT_DELETE',
p_line_number);
SELECT 1 INTO l_num_val
FROM dual
WHERE EXISTS (SELECT 1 FROM icx_cat_category_items
WHERE rt_category_id = p_category_id);
InsertError(p_request_id, 'ICX_POR_CATEGORY_KEY', 'ICX_POR_CAT_HAS_ITEMS',
p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_INVALID_TYPE',
p_line_number);
SELECT lookup_code INTO p_type_value
FROM fnd_lookup_values
WHERE lookup_type = 'ICX_CAT_TYPE'
AND UPPER(meaning) = UPPER(p_type)
AND language = p_language;
InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_INVALID_TYPE',
p_line_number);
IF (p_system_action = 'UPDATE') THEN
IF (p_type_value is not null and l_current_type <> p_type_value) THEN
-- Cannot update type
InsertError(p_request_id, 'ICX_POR_CAT_CATEGORY_TYPE', 'ICX_POR_CAT_CHANGE_TYPE',
p_line_number);
ELSIF (p_system_action IN ('UPDATE')) THEN
p_type_value := l_current_type;
IF (p_user_action = 'SYNC' OR p_user_action = 'DELETE') THEN
-- Check parent is not null
l_progress := '000';
p_system_action := 'DELETE';
InsertError(p_request_id, 'ICX_POR_CAT_PARENT_KEY',
'ICX_POR_CAT_FIELD_REQUIRED',
p_line_number);
SELECT rt_category_id, type INTO p_parent_id, l_parent_type
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_parent_key)
AND language = p_language;
InsertError(p_request_id, 'ICX_POR_CAT_PARENT_KEY',
'ICX_POR_CAT_GENUS_PARENT', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_PARENT_KEY',
'ICX_POR_INVALID_CATEGORY', p_line_number);
SELECT rt_category_id, type INTO l_num_val, l_parent_type
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_parent_name)
AND language = p_language;
InsertError(p_request_id, 'ICX_POR_CAT_PARENT_NAME',
'ICX_POR_CAT_REL_DIFF_KEY_NAME',
p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_PARENT_NAME',
'ICX_POR_CAT_GENUS_PARENT', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_PARENT_NAME',
'ICX_POR_INVALID_CATEGORY', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY', 'ICX_POR_CAT_FIELD_REQUIRED',
p_line_number);
SELECT rt_category_id INTO p_child_id
FROM icx_cat_categories_tl
WHERE upper_key = UPPER(p_child_key)
AND language = p_language;
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY',
'ICX_POR_INVALID_CATEGORY', p_line_number);
SELECT rt_category_id INTO l_num_val
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_child_name)
AND language = p_language;
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
'ICX_POR_CAT_REL_DIFF_KEY_NAME', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
'ICX_POR_INVALID_CATEGORY', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY',
'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
ELSIF (p_system_action = 'DELETE') THEN
BEGIN
SELECT 1 INTO l_num_val
FROM dual
WHERE exists (SELECT 1 FROM icx_cat_browse_trees
WHERE parent_category_id = p_parent_id
AND child_category_id = p_child_id);
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_KEY',
--ErrMsg 'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
InsertError(p_request_id, 'ICX_POR_CAT_CHILD_NAME',
--ErrMsg 'ICX_POR_CAT_SAME_PARENT_CHILD', p_line_number);
PROCEDURE InsertError(p_request_id in out NOCOPY number,
p_descriptor_key in varchar2,
p_message_name in varchar2,
p_line_number in number
) IS
l_progress varchar2(10) := '000';
SELECT icx_por_batch_jobs_s.nextval
INTO p_request_id
FROM sys.dual;
INSERT into icx_por_failed_line_messages (
job_number,
descriptor_key,
message_name,
line_number
) VALUES (
p_request_id,
p_descriptor_key,
p_message_name,
p_line_number
);
(-20000, 'Exception at ICX_POR_SCHEMA_UPLOAD.InsertError(ErrLoc = ' || l_progress ||') ' ||
'SQL Error : ' || SQLERRM);
END InsertError;
** Desc : Called when a descriptor is to be deleted or made not searchable
** SHOULD BE CALLED BEFORE THE DESCRIPTOR IS ACTUALLY DELETED
** Before calling this the rows in icx_cat_categories_tl with the
** given rt_category_id should be locked thru a SELECT...FOR UPDATE
** to avoid concurrent access to the SECTION_MAP column. The calling
** code is responsible for committing the changes.
** Parameters:
** p_category_id - category to be modified
** p_descriptor_id - descriptor to be modified
**/
PROCEDURE release_section_tag(p_category_id IN NUMBER,
p_descriptor_id IN NUMBER) IS
v_bit_position PLS_INTEGER := 0;
SELECT section_map INTO v_section_map
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id AND ROWNUM = 1;
SELECT section_tag INTO v_bit_position
FROM icx_cat_descriptors_tl
WHERE rt_descriptor_id = p_descriptor_id
AND ROWNUM = 1;
UPDATE icx_cat_descriptors_tl SET section_tag = NULL
WHERE rt_descriptor_id = p_descriptor_id;
UPDATE icx_cat_categories_tl SET section_map = v_section_map
WHERE rt_category_id = p_category_id;
** given rt_category_id should be locked thru a SELECT...FOR UPDATE
** to avoid concurrent access to the SECTION_MAP column. The calling
** code is responsible for committing the changes.
** Parameters:
** p_category_id IN NUMBER - category to be modified
** p_descriptor_id IN NUMBER - descriptor to be modified
** p_section_tag OUT NUMBER - section tag assigned
**/
PROCEDURE assign_section_tag(p_category_id IN NUMBER,
p_descriptor_id IN NUMBER,
p_section_tag OUT NOCOPY NUMBER,
p_stored_in_table OUT NOCOPY VARCHAR2,
p_stored_in_column OUT NOCOPY VARCHAR2,
p_type IN VARCHAR2
) IS
v_section_map VARCHAR2(300);
SELECT section_tag, stored_in_table, stored_in_column
INTO l_section_tag, l_stored_in_table, l_stored_in_column
FROM icx_cat_descriptors_tl
WHERE rt_descriptor_id = p_descriptor_id
AND rownum = 1;
SELECT section_map, INSTR(section_map,'0', 1, 1) INTO v_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 v_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 v_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 = v_section_map
WHERE rt_category_id = p_category_id;
UPDATE icx_cat_descriptors_tl SET section_tag = p_section_tag,
stored_in_table = l_stored_in_table,
stored_in_column = l_stored_in_column
WHERE rt_descriptor_id = p_descriptor_id;
** or when batch update of a category is needed
** Before calling this the rows in icx_cat_categories_tl with the
** given rt_category_id should be locked thru a SELECT...FOR UPDATE
** to avoid concurrent access to the SECTION_MAP column. The calling
** code is responsible for committing the changes.
** Parameters:
** p_category_id - category to be modified
**/
PROCEDURE assign_all_section_tags(p_category_id IN NUMBER) IS
v_section_map VARCHAR2(300);
SELECT rt_descriptor_id, section_tag, type FROM icx_cat_descriptors_tl
WHERE rt_category_id = x_category_id
AND language = x_language
AND section_tag IS NOT NULL
AND rt_descriptor_id >1000
ORDER BY rt_descriptor_id
;
SELECT rt_descriptor_id, type FROM icx_cat_descriptors_tl
WHERE rt_category_id = x_category_id
AND language = x_language
AND section_tag IS NULL
AND rt_descriptor_id >1000
ORDER BY rt_descriptor_id
;
SELECT language_code INTO v_base_language
FROM fnd_languages WHERE installed_flag = 'B';
* have a section tag. This takes care of deleted attributes or attributes
* changed to not searchable since the corresponding bits will be cleared
*/
xErrLoc := 200;
UPDATE icx_cat_descriptors_tl
SET stored_in_column = l_stored_in_column,
stored_in_table = l_stored_in_table
WHERE rt_descriptor_id = rec.rt_descriptor_id
AND (stored_in_column is null OR stored_in_table is null);
UPDATE icx_cat_descriptors_tl
SET section_tag = v_stored_section_tag,
stored_in_column = l_stored_in_column,
stored_in_table = l_stored_in_table
WHERE rt_descriptor_id = rec.rt_descriptor_id;
UPDATE icx_cat_categories_tl SET section_map = v_section_map
WHERE rt_category_id = p_category_id;
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'CATEGORY', 'ICX_POR_CATEGORY_KEY', p_key);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'CATEGORY', 'ICX_POR_CATEGORY_NAME', p_name);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'CATEGORY', 'ICX_POR_CAT_CATEGORY_TYPE', p_type);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'CATEGORY', 'ICX_POR_CATEGORY_DESC', p_description);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'CATEGORY', 'ICX_POR_CAT_ATTRIB_OWNER_KEY', p_owner_key);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'CATEGORY', 'ICX_POR_CAT_ATTRIB_OWNER_NAME', p_owner_name);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_ATTRIB_KEY', p_key);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_ATTRIB_NAME', p_name);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_CATEGORY_TYPE', p_type);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CATEGORY_DESC', p_description);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_OWNER_KEY',
p_owner_key);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_OWNER_NAME',
p_owner_name);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_ATTRIB_SEQ', p_sequence);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_DEFAULT',
p_default_value);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_SEARCHABLE',
p_searchable);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTR_DETAILVISIBLE',
p_itemdetailvisible);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_SEARCH_VISIBLE',
p_searchresultsvisible);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_REQUIRED',
p_required);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, p_errortype, 'ICX_POR_CAT_ATTRIB_MULTIVALUE',
p_multivalue);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_PARENT_KEY',
p_parent_key);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_PARENT_NAME',
p_parent_name);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_CHILD_KEY',
p_child_key);
INSERT INTO icx_por_failed_lines (job_number, line_number, action,
row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
p_line_number, p_action, 'RELATIONSHIP', 'ICX_POR_CAT_CHILD_NAME',
p_child_name);
update icx_cat_schema_versions
set version=version+1
where descriptor_set_id=xAttribId;
INSERT INTO icx_por_failed_lines (job_number,
line_number,
action,
row_type,
descriptor_key,
descriptor_value)
VALUES (p_request_id,
p_line_number,
p_action,
'DESCRIPTOR',
'ICX_POR_ROOT_SECTION',
'root desc section');
InsertError(p_request_id, 'ICX_POR_ROOT_SECTION1',
'ICX_POR_ROOT_PROFILE_OFF',
p_line_number);
PROCEDURE sync_deleted_descriptors
IS
--Bug#3072827
--Removed the rt_descriptor_id from the cursor and add a distinct
--When descriptors are deleted from online, we add CR jobs which process
--sync_deleted_descriptors, so there could be a scenario, when rt_category_id,
--stored_in_table and stored_in_column are same
--with rt_descriptor_id different in icx_cat_deleted_attributes.
--In the above case without distinct, the sync_deleted_descriptors will
--fail with exception 'ORA-00957:duplicate column name'
CURSOR deleted_descriptors_csr IS
SELECT distinct rt_category_id, stored_in_table, stored_in_column
from icx_cat_deleted_attributes order by stored_in_table, rt_category_id;
update_tlp_sql_string VARCHAR2(4000) := null;
update_tlp_set_string VARCHAR2(4000) := null;
update_tlp_value_string VARCHAR2(4000) := null;
update_exttlp_sql_string VARCHAR2(4000) := null;
update_exttlp_set_string VARCHAR2(4000) := null;
update_exttlp_value_string VARCHAR2(4000) := null;
OPEN deleted_descriptors_csr;
vRtCategoryIds.DELETE;
vStoredInColumns.DELETE;
vStoredInTables.DELETE;
FETCH deleted_descriptors_csr BULK COLLECT INTO
vRtCategoryIds, vStoredInTables, vStoredInColumns
LIMIT BATCH_SIZE;
update_tlp_set_string := vStoredInColumns(i);
update_tlp_value_string := ' null';
update_tlp_set_string := update_tlp_set_string || ' , '|| vStoredInColumns(i) ;
update_tlp_value_string := update_tlp_value_string || ' , '|| 'null';
update_exttlp_set_string := vStoredInColumns(i);
update_exttlp_value_string := ' null';
update_exttlp_set_string := update_exttlp_set_string || ' , '|| vStoredInColumns(i) ;
update_exttlp_value_string := update_exttlp_value_string || ' , '|| 'null';
update_tlp_sql_string := 'UPDATE ICX_CAT_ITEMS_TLP SET ';
update_tlp_sql_string := update_tlp_sql_string ||' ('||update_tlp_set_string||' ) = (select ' || update_tlp_value_string|| ' from dual) where primary_category_id = :cat_id';
DBMS_SQL.parse(v_cursor_id, update_tlp_sql_string, dbms_sql.native);
update_tlp_set_string := null;
update_tlp_value_string := null;
update_exttlp_sql_string := 'UPDATE ICX_CAT_EXT_ITEMS_TLP SET ';
update_exttlp_sql_string := update_exttlp_sql_string||' ('||update_exttlp_set_string||' ) = (select ' || update_exttlp_value_string|| ' from dual) where rt_category_id = :cat_id';
DBMS_SQL.parse(v_cursor_id, update_exttlp_sql_string, dbms_sql.native);
update_exttlp_set_string := null;
update_exttlp_value_string := null;
CLOSE deleted_descriptors_csr;
v_sql := 'TRUNCATE TABLE ' || ICX_POR_EXT_UTL.getIcxSchema ||'.icx_cat_deleted_attributes';
'Exception at ICX_POR_SCHEMA_UPLOAD.sync_deleted_descriptors('
|| xErrLoc || '): ' || SQLERRM);
END sync_deleted_descriptors;
/** Proc : update_items_for_category
** Desc : Overloaded method for concurrent program executable.
** Executable for the Category Rename Concurrent Program.
** See : update_items_for_category [below]
**/
PROCEDURE update_items_for_category (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_category_name IN VARCHAR2,
p_category_id IN NUMBER,
p_language IN VARCHAR2,
p_request_id IN NUMBER DEFAULT -1)
IS
BEGIN
retcode := 0;
update_items_for_category(p_category_name, p_category_id,
p_language, p_request_id);
END update_items_for_category;
** Proc : update_items_for_category
** Desc : Update primary_category_name in items_tlp with the category name for a sepcified language.
**/
PROCEDURE update_items_for_category (p_category_name IN VARCHAR2,
p_category_id IN NUMBER,
p_language IN VARCHAR2,
p_request_id IN NUMBER DEFAULT -1)
IS
xErrLoc INTEGER := 0;
SELECT language
FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
and type = 2
and source_lang = p_language
and source_lang <> language
UNION
SELECT p_language FROM DUAL;
UPDATE icx_cat_items_tlp
SET primary_category_name = p_category_name,
request_id = p_request_id
WHERE primary_category_id=p_category_id
AND language = xLangArray(i)
AND nvl(request_id, -1) <> p_request_id
AND rownum <= xCommitSize;
'Exception at ICX_POR_SCHEMA_UPLOAD.update_items_for_category('
|| xErrLoc || '): ' || SQLERRM);
END update_items_for_category;
** Proc : handle_delete_descriptors
** Desc : Overloaded version. Handles the plsql call required
** when a descritpor is deleted from ecmanager
**/
PROCEDURE handle_delete_descriptors (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_searchable IN NUMBER,
p_rename_category_done IN VARCHAR2,
p_category_name IN VARCHAR2,
p_rt_category_id IN NUMBER,
p_language IN VARCHAR2,
p_request_id IN NUMBER DEFAULT -1)
IS
BEGIN
retcode := 0;
handle_delete_descriptors(p_searchable,
p_rename_category_done,
p_category_name,
p_rt_category_id,
p_language,
p_request_id);
END handle_delete_descriptors;
** Proc : handle_delete_descriptors
** Desc : Handles the plsql call required when a descritpor is deleted from ecmanager
**/
PROCEDURE handle_delete_descriptors (p_searchable IN NUMBER,
p_rename_category_done IN VARCHAR2,
p_category_name IN VARCHAR2,
p_rt_category_id IN NUMBER,
p_language IN VARCHAR2,
p_request_id IN NUMBER DEFAULT -1)
IS
xErrLoc INTEGER := 0;
sync_deleted_descriptors;
update_items_for_category(p_category_name, p_rt_category_id, p_language, p_request_id);
'Exception at ICX_POR_SCHEMA_UPLOAD.handle_delete_descriptors('
|| xErrLoc || '): ' || SQLERRM);
END handle_delete_descriptors;