[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROWID
FROM icx_cat_items_ctx_hdrs_tlp;
l_rowid_tbl.DELETE;
SELECT ROWID
FROM icx_cat_items_ctx_hdrs_tlp
WHERE source_type <> 'MASTER_ITEM';
l_rowid_tbl.DELETE;
SELECT ROWID
FROM icx_cat_items_ctx_hdrs_tlp
WHERE source_type IN (l_source_type1, l_source_type2);
l_rowid_tbl.DELETE;
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date,
internal_request_id, request_id, created_by, creation_date,
sequence, ctx_desc)
SELECT dtls.inventory_item_id, dtls.po_line_id, dtls.req_template_name,
dtls.req_template_line_num, hdrs.org_id, dtls.language,
dtls.last_update_login, dtls.last_updated_by, dtls.last_update_date,
dtls.internal_request_id, dtls.request_id, dtls.created_by, dtls.creation_date,
dtls.sequence, dtls.ctx_desc
FROM icx_cat_items_ctx_dtls_tlp dtls, icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type = 'GLOBAL_BLANKET'
AND hdrs.inventory_item_id = dtls.inventory_item_id
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.language = dtls.language
AND hdrs.owning_org_id = dtls.org_id
AND dtls.sequence = p_repopulate_at_seq;
'Num. of rows inserted for ga:' || SQL%ROWCOUNT);
SELECT ROWID
FROM icx_cat_items_ctx_hdrs_tlp
WHERE source_type NOT IN ('MASTER_ITEM', 'GLOBAL_BLANKET');
SELECT ROWID
FROM icx_cat_items_ctx_hdrs_tlp
WHERE source_type = 'MASTER_ITEM';
l_rowid_tbl.DELETE;
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date,
internal_request_id, request_id, created_by, creation_date,
sequence, ctx_desc)
SELECT dtls.inventory_item_id, dtls.po_line_id, dtls.req_template_name,
dtls.req_template_line_num, hdrs.org_id, dtls.language,
dtls.last_update_login, dtls.last_updated_by, dtls.last_update_date,
dtls.internal_request_id, dtls.request_id, dtls.created_by, dtls.creation_date,
dtls.sequence, dtls.ctx_desc
FROM icx_cat_items_ctx_dtls_tlp dtls, icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type = 'GLOBAL_BLANKET'
AND hdrs.inventory_item_id = dtls.inventory_item_id
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.language = dtls.language
AND hdrs.owning_org_id = dtls.org_id
AND dtls.sequence BETWEEN ICX_CAT_BUILD_CTX_SQL_PVT.g_seqStartReqularBaseRow
AND ICX_CAT_BUILD_CTX_SQL_PVT.g_seqEndReqularBaseRow;
'Num. of rows inserted for ga:' || SQL%ROWCOUNT);
SELECT source_type, rowid
FROM icx_cat_items_ctx_hdrs_tlp
WHERE source_type IN (p_source_type1, p_source_type2)
AND internal_request_id = p_internal_request_id;
l_rowid_tbl.DELETE;
l_source_type_tbl.DELETE;
SELECT cat.rt_category_id
FROM icx_cat_categories_tl cat
WHERE cat.type = 2
AND cat.language = (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B')
AND EXISTS (SELECT 'atleast one searchable descriptor'
FROM icx_cat_attributes_tl att
where att.rt_category_id = cat.rt_category_id
and att.language = cat.language
and att.searchable = 1)
AND EXISTS (SELECT 'atleast one item loaded in the current internal_request_id'
FROM icx_cat_items_ctx_hdrs_tlp item
WHERE item.ip_category_id = cat.rt_category_id
AND item.internal_request_id = p_internal_request_id
AND item.source_type NOT IN ('MASTER ITEM', 'GLOBAL_BLANKET'));
SELECT rowid
FROM icx_cat_items_ctx_hdrs_tlp
WHERE ip_category_id = p_category_id
AND internal_request_id = p_internal_request_id
AND source_type NOT IN ('MASTER ITEM', 'GLOBAL_BLANKET');
l_special_metadata_tbl.DELETE;
l_regular_nontl_metadata_tbl.DELETE;
l_regular_tl_metadata_tbl.DELETE;
l_all_ctx_sql_tbl.DELETE;
l_rowid_tbl.DELETE;
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date,
internal_request_id, request_id, created_by, creation_date,
sequence, ctx_desc)
SELECT dtls.inventory_item_id, dtls.po_line_id, dtls.req_template_name,
dtls.req_template_line_num, hdrs.org_id, dtls.language,
dtls.last_update_login, dtls.last_updated_by, dtls.last_update_date,
dtls.internal_request_id, dtls.request_id, dtls.created_by, dtls.creation_date,
dtls.sequence, dtls.ctx_desc
FROM icx_cat_items_ctx_dtls_tlp dtls, icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type = 'GLOBAL_BLANKET'
AND hdrs.internal_request_id = p_internal_request_id
AND hdrs.inventory_item_id = dtls.inventory_item_id
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.owning_org_id = dtls.org_id
AND hdrs.language = dtls.language
AND dtls.sequence BETWEEN l_start_catg_att_seq AND l_end_catg_att_seq;
'Num. of rows inserted for ga:' || SQL%ROWCOUNT);
SELECT rowid
FROM icx_cat_items_ctx_hdrs_tlp
WHERE internal_request_id = p_internal_request_id;
l_rowid_tbl.DELETE;
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, 10000, ''
FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.rowid = l_rowid_tbl(i);
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, 10001, to_char(org_id)
FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.rowid = l_rowid_tbl(i);
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, 15000, ''
FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.rowid = l_rowid_tbl(i);
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
last_update_login, last_updated_by, last_update_date, created_by, creation_date, 15001,
'' || to_char(purchasing_org_id) || ' '
FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.rowid = l_rowid_tbl(i);
PROCEDURE updateItemsCtxHdrsTlp
( p_category_id IN NUMBER ,
p_internal_request_id IN OUT NOCOPY NUMBER ,
p_attribute_key IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'updateItemsCtxHdrsTlp';
SELECT ROWID, po_line_id, inventory_item_id,
req_template_name, req_template_line_num,
org_id, language
FROM icx_cat_items_ctx_hdrs_tlp
WHERE (p_category_id = 0 OR ip_category_id = p_category_id);
l_rowid_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence BETWEEN l_start_sequence AND l_end_sequence;
' Num. of rows deleted from dtls for category_id:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow;
'Num. of rows deleted from dtls for partNum, auxid:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow;
'Num. of rows deleted from dtls for supp:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForInternalItemNumRow;
'Num. of rows deleted from dtls for intItemNum:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSourceRow;
'Num. of rows deleted from dtls for source:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForItemRevisionRow;
'Num. of rows deleted from dtls for itemrev:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow;
'Num. of rows deleted from dtls for shopCatg:' || SQL%ROWCOUNT);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence BETWEEN l_start_sequence AND l_end_sequence;
'Num. of rows deleted from dtls for regular base attrs:' || SQL%ROWCOUNT);
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ctx_desc = NULL,
last_update_login = l_internal_request_id,
last_updated_by = l_internal_request_id,
last_update_date = sysdate,
internal_request_id = l_internal_request_id
WHERE rowid = l_rowid_tbl(i);
l_err_string := 'ICX_CAT_POPULATE_CTXSTRING_PVT.updateItemsCtxHdrsTlp' ||l_err_loc;
SELECT ROWID, po_line_id, inventory_item_id,
req_template_name, req_template_line_num,
org_id, language
FROM icx_cat_items_ctx_hdrs_tlp
WHERE (p_category_id = 0 OR ip_category_id = p_category_id)
AND internal_request_id <> l_internal_request_id;
END updateItemsCtxHdrsTlp;
updateItemsCtxHdrsTlp(p_category_id, l_internal_request_id, null);
updateItemsCtxHdrsTlp(0, l_internal_request_id, p_attribute_key);
l_all_ctx_sql_tbl.DELETE;
l_special_ctx_sql_tbl.DELETE;
l_regular_ctx_sql_tbl.DELETE;
l_all_ctx_sql_tbl.DELETE;
l_special_ctx_sql_tbl.DELETE;
l_regular_ctx_sql_tbl.DELETE;
SELECT ROWID, po_line_id, inventory_item_id,
req_template_name, req_template_line_num,
org_id, language
FROM icx_cat_items_ctx_hdrs_tlp
WHERE ip_category_id = p_category_id
AND language = p_language;
l_rowid_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
UPDATE icx_cat_items_ctx_dtls_tlp
SET ctx_desc = '<' || l_section_tag || '>' ||
replace(replace(p_category_name, '<', ' '), '>', ' ') ||
''
WHERE po_line_id = l_po_line_id_tbl(i)
AND inventory_item_id = l_inventory_item_id_tbl(i)
AND req_template_name = l_req_template_name_tbl(i)
AND req_template_line_num = l_req_template_line_num_tbl(i)
AND org_id = l_org_id_tbl(i)
AND language = l_language_tbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow;
'Num of rows updated in ctx_dtls for category rename:' || SQL%ROWCOUNT);
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ctx_desc = NULL,
ip_category_name = p_category_name,
last_update_login = l_internal_request_id,
last_updated_by = l_internal_request_id,
last_update_date = sysdate,
internal_request_id = l_internal_request_id
WHERE rowid = l_rowid_tbl(i);
SELECT ROWID, po_line_id, inventory_item_id,
req_template_name, req_template_line_num,
org_id, language
FROM icx_cat_items_ctx_hdrs_tlp
WHERE ip_category_id = p_category_id
AND language = p_language
AND internal_request_id <> l_internal_request_id;