[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (p_action_mode IN ('ALL', 'INSERT_MAPPING')) THEN
-- Insert into icx_por_category_data_sources and icx_por_category_order_map
gInsMapRtCategoryIdTbl.DELETE;
gInsMapCategoryKeyTbl.DELETE;
gInsMapLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_CATEGORY')) THEN
-- Insert into icx_cat_categories_tl
gInsRtCategoryIdTbl.DELETE;
gInsCategoryKeyTbl.DELETE;
gInsCategoryNameTbl.DELETE;
gInsLanguageTbl.DELETE;
gInsSourceLangTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_ITEM_CATEGORY')) THEN
-- Insert items into icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
gInsPOCategoryIdTbl.DELETE;
IF (p_action_mode IN ('ALL', 'UPDATE_CATEGORY')) THEN
-- Update icx_cat_categories_tl
gUpdRtCategoryIdTbl.DELETE;
gUpdCategoryNameTbl.DELETE;
gUpdLanguageTbl.DELETE;
gUpdSourceLangTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_CATEGORY')) THEN
-- Delete from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
gDelPoCategoryIdTbl.DELETE;
IF (p_action_mode = 'INSERT_MAPPING') THEN
-- Insert into icx_por_category_data_sources and icx_por_category_order_map
l_string := l_string || ' gInsMapRtCategoryIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gInsMapRtCategoryIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_CATEGORY') THEN
-- Insert into icx_cat_categories_tl
l_string := l_string || ' gInsRtCategoryIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gInsRtCategoryIdTbl, p_index) || ', ';
IF (p_action_mode IN ('ALL', 'INSERT_ITEM_CATEGORY')) THEN
-- Insert items into icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
l_string := l_string || ' gInsPOCategoryIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gInsPOCategoryIdTbl, p_index) || ', ';
IF (p_action_mode = 'UPDATE_CATEGORY') THEN
-- Update icx_cat_categories_tl
l_string := l_string || ' gUpdRtCategoryIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gUpdRtCategoryIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_CATEGORY') THEN
-- Delete from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp tables
l_string := l_string || ' gDelPoCategoryIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDelPoCategoryIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_MAPPING') THEN
l_err_loc := 500;
IF (p_action_mode = 'INSERT_CATEGORY') THEN
l_err_loc := 700;
IF (p_action_mode = 'INSERT_ITEM_CATEGORY') THEN
l_err_loc := 900;
IF (p_action_mode = 'UPDATE_CATEGORY') THEN
l_err_loc := 1000;
IF (p_action_mode = 'DELETE_CATEGORY') THEN
l_err_loc := 1200;
l_action_mode := 'INSERT_CATEGORY';
INSERT INTO icx_cat_categories_tl(
rt_category_id, category_name, key, title, type, language,
source_lang, upper_category_name, upper_key, section_map,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date, request_id,
program_application_id, program_id, program_login_id)
VALUES(gInsRtCategoryIdTbl(i), gInsCategoryNameTbl(i),
gInsCategoryKeyTbl(i), 'Oracle', 2,
gInsLanguageTbl(i), gInsSourceLangTbl(i),
upper(gInsCategoryNameTbl(i)), upper(gInsCategoryKeyTbl(i)),
rpad('0', 300, 0),
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate, ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id);
'Num. of rows inserted into categories_tl:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_MAPPING';
INSERT INTO icx_por_category_data_sources (
rt_category_id, category_key, external_source, external_source_key,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date, request_id,
program_application_id, program_id)
SELECT rt_category_id, key, 'Oracle', key,
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate, ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id
FROM icx_cat_categories_tl
WHERE rt_category_id = gInsMapRtCategoryIdTbl(i)
AND language = gInsMapLanguageTbl(i)
AND NOT EXISTS (SELECT 1
FROM icx_por_category_data_sources
WHERE external_source = 'Oracle'
AND external_source_key = key);
'Num. of rows inserted into category_data_sources:' ||SQL%ROWCOUNT);
INSERT INTO icx_por_category_order_map (
rt_category_id, external_source, external_source_key,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date)
VALUES(gInsMapRtCategoryIdTbl(i), 'Oracle', gInsMapCategoryKeyTbl(i),
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate);
'Num. of rows inserted into category_order_map:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_ITEM_CATEGORY';
PROCEDURE updateCategories
IS
l_api_name CONSTANT VARCHAR2(30) := 'updateCategories';
l_action_mode := 'UPDATE_CATEGORY';
l_rowid_tbl.DELETE;
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ctx_desc = null,
ip_category_name = gUpdCategoryNameTbl(i),
last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_update_date = sysdate,
internal_request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
WHERE ip_category_id = gUpdRtCategoryIdTbl(i)
AND language = gUpdLanguageTbl(i)
AND ip_category_name <> gUpdCategoryNameTbl(i)
AND rownum <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING rowid BULK COLLECT INTO l_rowid_tbl;
'No rows updated in icx_cat_ctx_hdrs_tlp for category rename');
'Num. of rows updated in icx_cat_ctx_hdrs_tlp for category rename:' ||
l_row_count);
UPDATE icx_cat_items_ctx_dtls_tlp dtls
SET ctx_desc = (SELECT '<' ||to_char(l_section_tag) ||'>' ||
gUpdCategoryNameTbl(i) || ''
FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.rowid = l_rowid_tbl(j)
AND hdrs.po_line_id = dtls.po_line_id
AND hdrs.req_template_name = dtls.req_template_name
AND hdrs.req_template_line_num = dtls.req_template_line_num
AND hdrs.inventory_item_id = dtls.inventory_item_id
AND hdrs.org_id = dtls.org_id
AND hdrs.language = dtls.language)
WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow
AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.po_line_id = dtls.po_line_id
AND hdrs.req_template_name = dtls.req_template_name
AND hdrs.req_template_line_num = dtls.req_template_line_num
AND hdrs.inventory_item_id = dtls.inventory_item_id
AND hdrs.org_id = dtls.org_id
AND hdrs.language = dtls.language
AND hdrs.rowid = l_rowid_tbl(j) );
'Num. of rows updated in icx_cat_ctx_dtls_tlp for category rename:' ||
SQL%ROWCOUNT);
UPDATE icx_cat_categories_tl
SET category_name = gUpdCategoryNameTbl(i),
upper_category_name = upper(gUpdCategoryNameTbl(i)),
source_lang = gUpdSourceLangTbl(i),
last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_update_date = sysdate,
request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
WHERE rt_category_id = gUpdRtCategoryIdTbl(i)
AND language = gUpdLanguageTbl(i);
'Num. of rows updated in icx_cat_categories_tl:' ||SQL%ROWCOUNT);
END updateCategories;
PROCEDURE deleteCategories
IS
l_api_name CONSTANT VARCHAR2(30) := 'deleteCategories';
l_action_mode := 'DELETE_CATEGORY';
l_po_line_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
DELETE FROM icx_cat_items_ctx_hdrs_tlp
WHERE po_category_id = gDelPoCategoryIdTbl(i)
AND source_type = 'MASTER_ITEM'
AND rownum <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id, language
BULK COLLECT INTO l_po_line_id_tbl, l_req_template_name_tbl, l_req_template_line_num_tbl,
l_inventory_item_id_tbl, l_org_id_tbl, l_language_tbl;
'Num. of rows deleted from icx_cat_ctx_hdrs_tlp for category delete:' ||
l_row_count);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(j)
AND req_template_name = l_req_template_name_tbl(j)
AND req_template_line_num = l_req_template_line_num_tbl(j)
AND inventory_item_id = l_inventory_item_id_tbl(j)
AND org_id = l_org_id_tbl(j)
AND language = l_language_tbl(j);
'Num. of rows delete from icx_cat_ctx_dtls_tlp for category delete:' ||
SQL%ROWCOUNT);
END deleteCategories;
' No category insert done.');
updateCategories;
' No category update done.');
deleteCategories;
' No category delete done.');
SELECT count(1)
INTO l_num_val
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_category_name);
SELECT count(1)
INTO l_num_val
FROM icx_cat_categories_tl
WHERE upper_category_name = UPPER(p_category_name)
AND rt_category_id <> p_rt_category_id;
l_mtl_category_id_tbl.DELETE;
l_mtl_category_name_tbl.DELETE;
l_mtl_language_tbl.DELETE;
l_mtl_source_lang_tbl.DELETE;
l_rt_category_id_tbl.DELETE;
l_old_category_name_tbl.DELETE;
l_end_date_active_tbl.DELETE;
l_disable_date_tbl.DELETE;
l_system_date_tbl.DELETE;
SELECT icx_por_categoryid.nextval
INTO l_rt_category_id
FROM dual;
IF (g_DML_TYPE = ICX_CAT_POPULATE_CATG_PVT.g_DML_INSERT_TYPE AND
g_auto_create_shop_catg = 'Y' AND
l_category_name_is_valid)
THEN
l_err_loc := 1400;
'; is invalid and has to be deleted');
SELECT DISTINCT mck.category_id category_id,
nvl(mctl.description, mck.concatenated_segments) category_name,
mctl.language language, mctl.source_lang source_lang,
icat.rt_category_id rt_category_id, icat2.category_name old_category_name,
nvl(mck.end_date_active, SYSDATE+1), nvl(mck.disable_date, SYSDATE+1),
SYSDATE system_date
FROM mtl_categories_kfv mck,
mtl_categories_tl mctl,
icx_cat_categories_tl icat,
icx_cat_categories_tl icat2
WHERE mck.category_id = P_CATEGORY_ID
AND mck.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
AND mctl.category_id = mck.category_id
AND mctl.language IN (SELECT language_code FROM fnd_languages WHERE installed_flag IN ('B', 'I'))
AND to_char(mctl.category_id) = icat.key (+)
AND to_char(mctl.category_id) = icat2.key (+)
AND mctl.language = icat2.language (+)
ORDER BY 1;
SELECT DISTINCT mck.category_id category_id,
nvl(mctl.description, mck.concatenated_segments) category_name,
mctl.language language, mctl.source_lang source_lang,
icat.rt_category_id rt_category_id, icat2.category_name old_category_name,
nvl(mck.end_date_active, SYSDATE+1), nvl(mck.disable_date, SYSDATE+1),
SYSDATE system_date
FROM mtl_categories_kfv mck,
mtl_categories_tl mctl,
mtl_category_set_valid_cats mcsvc,
icx_cat_categories_tl icat,
icx_cat_categories_tl icat2
WHERE mck.category_id = P_CATEGORY_ID
AND mck.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
AND mctl.category_id = mck.category_id
AND mctl.language IN (SELECT language_code FROM fnd_languages WHERE installed_flag IN ('B', 'I'))
AND to_char(mctl.category_id) = icat.key (+)
AND to_char(mctl.category_id) = icat2.key (+)
AND mctl.language = icat2.language (+)
AND mcsvc.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
AND mcsvc.category_id = mck.category_id
ORDER BY 1;
PROCEDURE populateValidCategorySetInsert
( P_CATEGORY_ID IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'populateValidCategorySetInsert';
END populateValidCategorySetInsert;
PROCEDURE populateValidCategorySetUpdate
( P_OLD_CATEGORY_ID IN NUMBER ,
P_NEW_CATEGORY_ID IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'populateValidCategorySetUpdate';
populateValidCategorySetDelete(P_OLD_CATEGORY_ID);
ICX_CAT_POPULATE_CATG_PVT.g_DML_TYPE := ICX_CAT_POPULATE_CATG_PVT.g_DML_INSERT_TYPE;
END populateValidCategorySetUpdate;
PROCEDURE populateValidCategorySetDelete
( P_CATEGORY_ID IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'populateValidCategorySetDelete';
gDelPoCategoryIdTbl.DELETE;
deleteCategories;
END populateValidCategorySetDelete;