[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tl.rowid, tl.rt_item_id, tl.language
FROM icx_cat_items_tlp tl
WHERE tl.request_id = p_jobno;
SELECT tl.rowid, tl.rt_item_id, tl.language
FROM icx_cat_items_tlp tl
WHERE tl.request_id = p_jobno;
SELECT rowid FROM icx_cat_items_tlp
where request_id = p_jobno;
icx_por_ext_utl.debug('about to update icx_cat_items_tlp.ctx_desc');
UPDATE ICX_CAT_ITEMS_TLP
SET CTX_DESC = null
WHERE rowid = vRowids(i);
** Synopsis : Update the ctx_ for all the items in a given job.
** ActionPlan: 1. Collect root descriptors and local descriptors from
** cursors defined above.
** 2. Concatenate all from 1 into a 'allSelectList' and
** 3. Update ctx_ in icx_cat_items_ctx_tlp with 2 .
** 4. Update ctx_desc in icx_cat_items_tlp with null : Bug#3329169
** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
*/
PROCEDURE populateBaseAttributes(pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y', p_log_type IN VARCHAR2 DEFAULT 'LOADER')
IS
xErrLoc PLS_INTEGER := 0;
SELECT rowid,rt_item_id,language
FROM icx_cat_items_tlp;
populateCtxDescBaseAtt(getItemsTlCur, pDeleteYN, pUpdateYN, NULL, 'ROWID', p_log_type);
SELECT rowid FROM icx_cat_items_tlp;
UPDATE ICX_CAT_ITEMS_TLP
SET CTX_DESC = null
WHERE rowid = vRowids(i);
pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y',
p_log_type IN VARCHAR2 DEFAULT 'LOADER')
IS
vCategoryId INTEGER := 0;
SELECT cat.rt_category_id
FROM icx_cat_categories_tl cat
WHERE cat.language = p_lang
AND cat.rt_category_id <> 0
AND cat.type = 2
AND exists (select 'X'
from icx_cat_descriptors_tl des
where des.rt_category_id = cat.rt_category_id
and des.language = cat.language
and des.searchable = 1)
AND exists (select 'X'
from icx_cat_category_items cit, icx_cat_items_b it
where cit.rt_category_id = cat.rt_category_id
and cit.rt_item_id = it.rt_item_id
and it.request_id = p_jobNum);
SELECT language_code
INTO vLang
FROM fnd_languages
WHERE installed_flag = 'B';
vSqlString := 'SELECT tlp.rowid,tlp.rt_item_id,tlp.language FROM ICX_CAT_EXT_ITEMS_TLP tlp, icx_cat_items_b it where tlp.rt_category_id = :category_id and it.rt_item_id=tlp.rt_item_id and it.request_id=:request_id';
populateCtxDescCatAtt(vCategoryId, vItemSourceCursor,pDeleteYN,
pUpdateYN, NULL, 'ROWID', p_log_type);
PROCEDURE populateCategoryAttributes( pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y',
p_log_type IN VARCHAR2 DEFAULT 'LOADER')
IS
vCategoryId INTEGER := 0;
SELECT cat.rt_category_id
FROM icx_cat_categories_tl cat
WHERE cat.language = p_lang
AND cat.rt_category_id <> 0
AND cat.type = 2
AND exists (select 'X'
from icx_cat_descriptors_tl des
where des.rt_category_id = cat.rt_category_id
and des.language = cat.language
and des.searchable = 1)
AND exists (select 'X'
from icx_cat_category_items cit
where cit.rt_category_id = cat.rt_category_id);
SELECT language_code
INTO vLang
FROM fnd_languages
WHERE installed_flag = 'B';
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_EXT_ITEMS_TLP where rt_category_id = :category_id';
populateCtxDescCatAtt(vCategoryId, vItemSourceCursor,pDeleteYN,
pUpdateYN, NULL, 'ROWID', p_log_type);
pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y',
pLanguage IN VARCHAR2 DEFAULT NULL,
pSourceType IN VARCHAR2 DEFAULT 'ROWID',
p_log_type IN VARCHAR2 DEFAULT 'LOADER') IS
xErrLoc INTEGER := 0; -- execution location for error trapping
vInsertSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
vUpdateSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
ICX_POR_CTX_SQL_PKG.ROWID_WHERE_CLAUSE, pLanguage, vInsertSqlTab, vUpdateSqlTab);
ICX_POR_CTX_SQL_PKG.ITEMID_WHERE_CLAUSE, pLanguage, vInsertSqlTab, vUpdateSqlTab);
icx_por_ext_utl.debug('populateCtxDescBaseAtt, vUpdateSqlTab count:' ||
to_char(vUpdateSqlTab.COUNT) );
vRowidTab.DELETE;
vItemIdTab.DELETE;
vLangTab.DELETE;
IF (pDeleteYN = 'Y') THEN
FORALL i in 1..vItemIdTab.COUNT
DELETE FROM icx_cat_items_ctx_tlp
WHERE rt_item_id = vItemIdTab(i)
AND language = vLangTab(i)
AND (sequence < 5000 OR sequence = 10000 OR sequence = 15000);
FOR i in 1..vUpdateSqlTab.COUNT LOOP
xErrLoc := xErrLoc + 10;
vSqlString := vUpdateSqlTab(i);
IF (i = vUpdateSqlTab.COUNT - 1) THEN
-- This is the line
DBMS_SQL.BIND_VARIABLE(c_handle,':p_sequence',10000);
ELSIF (i = vUpdateSqlTab.COUNT) THEN
-- This is the line
DBMS_SQL.BIND_VARIABLE(c_handle,':p_sequence',15000);
pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y',
pLanguage IN VARCHAR2 DEFAULT NULL,
pSourceType IN VARCHAR2 DEFAULT 'ROWID',
p_log_type IN VARCHAR2 DEFAULT 'LOADER')
IS
p_lang VARCHAR2(10) := NULL;
vCInsertSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
vCUpdateSqlTab ICX_POR_CTX_SQL_PKG.SQLTab;
vCInsertSqlTab.DELETE;
vCUpdateSqlTab.DELETE;
vCInsertSqlTab, vCUpdateSqlTab);
vCInsertSqlTab, vCUpdateSqlTab);
icx_por_ext_utl.debug('populateCtxDescCatAtt, vCUpdateSqlTab count:' ||
to_char(vCUpdateSqlTab.COUNT) );
IF (pDeleteYN = 'Y') THEN
-- Delete the rows for the categories
xErrLoc := xErrLoc + 10;
DELETE FROM icx_cat_items_ctx_tlp
WHERE rt_item_id = vCItemIdTab(i)
AND sequence >= 5000
AND sequence < 10000
AND language = vCLangTab(i);
FOR i in 1..vCUpdateSqlTab.COUNT LOOP
xErrLoc := xErrLoc + 10;
vSqlString := vCUpdateSqlTab(i);
IF (pUpdateYN = 'Y') THEN
IF(pLanguage IS NULL) THEN
FOR language_row IN installed_languages_cur LOOP
p_lang := language_row.language_code;
vSqlString := 'UPDATE icx_cat_items_tlp SET ctx_desc ' ||
' = ''1'' WHERE rt_item_id = :p_item_id AND '||
':curr_lang = :p_lang AND language = :p_lang';
vSqlString := 'UPDATE icx_cat_items_tlp SET ctx_desc ' ||
' = ''1'' WHERE rt_item_id = :p_item_id AND '||
'language = :p_lang';
vCRowidTab.DELETE;
vCLangTab.DELETE;
vCItemIdTab.DELETE;
** Synopsis : Update the ctx_ for one item in a Lang.
** This is called from Item.insert() and Item.update().
*/
PROCEDURE populateCtxDescLang(p_item_id IN NUMBER,
p_category_id IN NUMBER,
p_lang IN VARCHAR2 DEFAULT NULL,
p_log_type IN VARCHAR2 DEFAULT 'LOADER') IS
xErrLoc PLS_INTEGER := 0; -- execution location for error trapping
SELECT rowid,rt_item_id, language
FROM ICX_CAT_ITEMS_TLP
WHERE RT_ITEM_ID= p_item_id;
IF (p_lang is NULL) THEN -- update item
xErrLoc := 220;
ELSE -- create new item, no need to delete or update
xErrLoc := 260;
IF (p_lang is NULL) THEN -- update item
xErrLoc := 500;
SELECT 1
INTO vCatTableExists
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM ICX_CAT_DESCRIPTORS_TL
WHERE RT_CATEGORY_ID=p_category_id
AND CLASS = 'POM_CAT_ATTR');
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_EXT_ITEMS_TLP' ||
' WHERE rt_item_id=:item_id and rt_category_id=:category_id';
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_CAT_ITEMS_TLP' ||
' WHERE rt_item_id=:item_id';
ELSE -- insert item
xErrLoc := 620;
SELECT 1
INTO vSearchableExists
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM ICX_CAT_DESCRIPTORS_TL
WHERE RT_CATEGORY_ID=p_category_id
AND CLASS = 'POM_CAT_ATTR'
AND SEARCHABLE = 1);
vSqlString := 'SELECT rowid,rt_item_id,language FROM ICX_POR_C'||p_category_id||'_TL' ||
' WHERE rt_item_id=:item_id';
select instr(versionString, '.') into majorReleasePos from dual;
select instr(substr(versionString,majorReleasePos), '.')
into minorReleasePos from dual;
** Synopsis : Update the ctx_ to include buyer id info for all the items
** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
*/
PROCEDURE populateBuyerInfo( pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y', p_log_type IN VARCHAR2 default 'LOADER')
IS
xErrLoc PLS_INTEGER := 0;
SELECT rowid, rt_item_id, language
FROM icx_cat_items_tlp;
populateCtxDescBuyerInfo(getItemsTlCur, pDeleteYN, pUpdateYN, null, 'ROWID', p_log_type);
pDeleteYN IN VARCHAR2 DEFAULT 'Y',
pUpdateYN IN VARCHAR2 DEFAULT 'Y',
pLanguage IN VARCHAR2 DEFAULT NULL,
pSourceType IN VARCHAR2 DEFAULT 'ROWID',
p_log_type IN VARCHAR2 DEFAULT 'LOADER') is
xErrLoc INTEGER := 0; -- execution location for error trapping
vRowidTab.DELETE;
vItemIdTab.DELETE;
vLangTab.DELETE;
IF (pDeleteYN = 'Y') THEN
FORALL i in 1..vItemIdTab.COUNT
DELETE FROM icx_cat_items_ctx_tlp
WHERE rt_item_id = vItemIdTab(i)
AND language = vLangTab(i)
AND sequence > 10000
AND sequence < 15000;
INSERT INTO icx_cat_items_ctx_tlp
(rt_item_id,language,sequence,ctx_desc,org_id)
SELECT tl.rt_item_id, tl.language, v_sequence,
to_char(pll.org_id), pll.org_id
FROM icx_cat_items_tlp tl,
(SELECT distinct org_id FROM icx_cat_item_prices
WHERE rt_item_id = vItemIdTab(i)
) pll
WHERE tl.rowid = vRowIdTab(i);
INSERT INTO icx_cat_items_ctx_tlp
(rt_item_id,language,sequence,ctx_desc,org_id)
SELECT tl.rt_item_id, tl.language, v_sequence,
to_char(pll.org_id), pll.org_id
FROM icx_cat_items_tlp tl,
(SELECT distinct org_id FROM icx_cat_item_prices
WHERE rt_item_id = vItemIdTab(i)
) pll
WHERE tl.rt_item_id = vItemIdTab(i)
AND tl.language = vLangTab(i);