[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATE_LOGIN NUMBER,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
CREATED_BY NUMBER,
CREATION_DATE DATE
);
- updateTLSQL is for updating/deleting translateable attributes of an existing items
- updateNonTLSQL is for updating/deleting Non-translateable attributes of an existing items
- translateSQL is for add an existing item in a new language
- for translate, the updateNonTLSQL takes care of updating the non-translatable attributes
and creating the correct number of rows in the languages the item already
exists in
*/
TYPE CategoryInfo IS RECORD (
category_name icx_cat_categories_tl.category_name%TYPE,
descriptor_count PLS_INTEGER := 0,
searchable_desc_count PLS_INTEGER := 0,
updateTLSQL VARCHAR2(32767),
updateNonTLSQL VARCHAR2(32767),
translateSQL VARCHAR2(32767)
);
gExtractorUpdatedFlags dbms_sql.varchar2_table;
gUpdateItemsTLSQL VARCHAR2(32767);
gUpdateItemsNonTLSQL VARCHAR2(32767);
gUpdateRootCtxSQL ICX_POR_CTX_SQL_PKG.SQLTab;
gUpdateCatCtxSQL ICX_POR_CTX_SQL_PKG.SQLTab;
gErrorRowids.DELETE;
gRowids.DELETE;
gRowTypes.DELETE;
gSystemActions.DELETE;
gItemIds.DELETE;
gPriceTypes.DELETE;
gCurrentItemIds.DELETE;
gCategoryIds.DELETE;
gCategoryNames.DELETE;
gOldCategoryIds.DELETE;
gDistinctCategoryIds.DELETE;
gChangedCatItemIds.DELETE;
gChangedOldCatIds.DELETE;
gChangedNewCatIds.DELETE;
gChangedCatActions.DELETE;
gOrgIds.DELETE;
gPricelistIds.DELETE;
gSupplierSiteIds.DELETE;
gSupplierIds.DELETE;
gExtractorUpdatedFlags.DELETE;
gActiveFlags.DELETE;
gDistinctItemIds.DELETE;
gDistinctBuyerIds.DELETE;
gUpdateItemsNonTLSQL := null;
gUpdateItemsTLSQL := null;
gCategoryInfo.DELETE;
gAddRootCtxSQL.DELETE;
gUpdateRootCtxSQL.DELETE;
gAddCatCtxSQL.DELETE;
gUpdateCatCtxSQL.DELETE;
gErrorCatDescKeys.DELETE;
ICX_POR_CTX_SQL_PKG.DEFAULT_MAX_LENGTH, vSQLTab, gUpdateCatCtxSQL);
vUpdateItemsNonTLSQL1 VARCHAR2(32767) := null;
vUpdateItemsNonTLSQL2 VARCHAR2(32767) := null;
vUpdateItemsTLSQL1 VARCHAR2(32767) := null;
vUpdateItemsTLSQL2 VARCHAR2(32767) := null;
select d.rt_descriptor_id,
d.type,
d.key,
d.section_tag,
d.stored_in_column,
d.stored_in_table,
d.searchable
from icx_cat_descriptors_tl d
where d.rt_category_id = v_category_id
and d.language = gJobLanguage
order by d.rt_descriptor_id;
vUpdateItemsNonTLSQL1 := 'update ' || vTableName ||
' set (last_update_login, last_updated_by, last_update_date ';
vUpdateItemsTLSQL1 := 'update ' || vTableName ||
' set (request_id, last_update_login, last_updated_by, last_update_date ';
vTranslateItemsSQL1 := 'insert into ' || vTableName ||
' (rt_item_id, language, org_id, '||
' rt_category_id, primary_flag, '||
' created_by, creation_date, ' ||
' last_updated_by, last_update_date, last_update_login ';
vUpdateItemsTLSQL1 := vUpdateItemsTLSQL1 || ', ' || vColName;
vUpdateItemsTLSQL2 := vUpdateItemsTLSQL2 ||
', decode(i.language, :p_language, decode(it.' ||
vColName || ',''#DEL'',null,null,i.' ||
vColName || ',it.' || vColName ||
'),i.' || vColName || ') ';
vUpdateItemsNonTLSQL1 := vUpdateItemsNonTLSQL1 || ', ' || vColName;
vUpdateItemsNonTLSQL2 := vUpdateItemsNonTLSQL2 || ', decode(it.' ||
vColName || ',''#DEL'',to_number(null),null,i.' ||
vColName || ',to_number(it.' || vColName || ')) ';
vUpdateItemsNonTLSQL1 := vUpdateItemsNonTLSQL1 || ', ' || vColName;
vUpdateItemsNonTLSQL2 := vUpdateItemsNonTLSQL2 || ', decode(it.' ||
vColName || ', ''#DEL'', null, null, i.' || vColName ||
', it.' || vColName || ') ';
p_category_info.updateNonTLSQL := vUpdateItemsNonTLSQL1 ||
') = (SELECT :p_user_login, :p_user_id, sysdate' ||
vUpdateItemsNonTLSQL2 ||
' FROM ' || p_table_name || ' it, icx_cat_ext_items_tlp i ' ||
' WHERE i.rt_item_id = :old_item_id' ||
' AND it.rowid = :p_rowid AND ' || to_char(p_category_id) ||
' = :p_category_id AND :update_action = :p_action and rownum=1)' ||
' WHERE rt_item_id = :old_item_id' ||
' AND rt_category_id = '|| to_char(p_category_id); --Category_Change
p_category_info.updateTLSQL := vUpdateItemsTLSQL1 ||
') = (SELECT :p_request_id, :p_user_login, :p_user_id, sysdate' ||
vUpdateItemsTLSQL2 ||
' FROM ' || p_table_name || ' it, icx_cat_ext_items_tlp i ' ||
' WHERE i.rt_item_id = :old_item_id' ||
' AND it.rowid = :p_rowid AND ' || to_char(p_category_id) ||
' = :p_category_id AND :update_action = :p_action' ||
' AND it.language = i.language)' ||
' WHERE rt_item_id = :old_item_id' ||
' AND rt_category_id = '|| to_char(p_category_id) || --Category_Change
' AND language = :p_language';
p_category_info.updateTLSQL := null;
p_category_info.updateNonTLSQL := null;
p_category_info.translateSQL := vTranslateItemsSQL1 || ') SELECT ' ||
'i.rt_item_id, it.language, it.org_id,'||
'it.rt_category_id, ''Y'',' ||
':p_user_id, sysdate, ' ||
':p_user_id, sysdate, :p_user_login ' ||
vTranslateItemsSQL2 || ' FROM icx_cat_ext_items_tlp i, ' || p_table_name ||
' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND ' ||
to_char(p_category_id) || ' = :p_category_id AND '||
' :update_action = :p_action and it.language<>i.language ' ||
' AND rownum = 1 ';
SELECT category_name FROM icx_cat_categories_tl
WHERE rt_category_id = p_category_id
AND language = gJobLanguage;
gCategoryInfo.DELETE;
vUpdateItemsTLSQL1 VARCHAR2(32767) := null;
vUpdateItemsTLSQL2 VARCHAR2(32767) := null;
vUpdateItemsNonTLSQL1 VARCHAR2(32767) := null;
vUpdateItemsNonTLSQL2 VARCHAR2(32767) := null;
select rt_descriptor_id,
type,
key,
section_tag,
stored_in_column,
stored_in_table,
searchable
from icx_cat_descriptors_tl
where rt_category_id = p_category_id
and language = gJobLanguage
and stored_in_table = 'ICX_CAT_ITEMS_TLP'
order by rt_descriptor_id;
vUpdateItemsTLSQL1 := 'update icx_cat_items_tlp set ( ' ||
'request_id, last_updated_by, last_update_date, last_update_login, primary_category_name ';
vUpdateItemsNonTLSQL1 := 'update icx_cat_items_tlp set ( ctx_desc, ' ||
'primary_category_id, last_updated_by, last_update_date, last_update_login ';
vTranslateItemsSQL1 := 'insert into icx_cat_items_tlp ( ctx_desc, ' ||
' rt_item_id, language, org_id, supplier_id, '||
' item_source_type, search_type, primary_category_id, primary_category_name, '||
' created_by, creation_date, ' ||
' last_updated_by, last_update_date, last_update_login ';
IF (can_update(root_desc.key) AND (root_desc.stored_in_table = 'ICX_CAT_ITEMS_TLP')) then
IF (root_desc.type = 2) THEN
vRootTLDescCount := vRootTLDescCount + 1;
vUpdateItemsTLSQL1 := vUpdateItemsTLSQL1 || ', ' || vColName;
vUpdateItemsTLSQL2 := vUpdateItemsTLSQL2 ||
', decode(i.language, :p_language, decode(it.'||vColName ||
',''#DEL'',null,null,i.' || vColName ||
',it.' || vColName || '),i.' || vColName || ')';
vUpdateItemsNonTLSQL1 := vUpdateItemsNonTLSQL1 || ', ' || vColName;
vUpdateItemsNonTLSQL2 := vUpdateItemsNonTLSQL2 ||
', decode(it.'||vColName ||
',''#DEL'',null,null,i.' || vColName ||
',it.' || vColName || ')';
gUpdateItemsNonTLSQL := vUpdateItemsNonTLSQL1 || ') = (SELECT ' ||
'''1'''||
', it.rt_category_id, :p_user_id, sysdate, :p_user_login ' ||
vUpdateItemsNonTLSQL2 || ' FROM icx_cat_items_tlp i, ' || pTableName ||
' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND' ||
' :update_action = :p_action and rownum=1) WHERE rt_item_id = :old_item_id';
gUpdateItemsTLSQL := vUpdateItemsTLSQL1 || ') = (SELECT ' ||
':p_request_id, :p_user_id, sysdate, :p_user_login, it.category_name ' ||
vUpdateItemsTLSQL2 || ' FROM icx_cat_items_tlp i, ' || pTableName ||
' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND' ||
' :update_action = :p_action and it.language=i.language)' ||
' WHERE language = :p_language' ||
' AND rt_item_id = :old_item_id';
gTranslateItemsSQL := vTranslateItemsSQL1 || ') SELECT ' ||
'''1'''||
', i.rt_item_id, it.language, it.org_id, it.supplier_id,'||
'''SUPPLIER'', ''SUPPLIER'', '||
'it.rt_category_id, it.category_name,' ||
'i.created_by, i.creation_date, ' ||
':p_user_id, sysdate, :p_user_login ' ||
vTranslateItemsSQL2 || ' FROM icx_cat_items_tlp i, ' || pTableName ||
' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND' ||
' :update_action = :p_action and it.language<>i.language ' ||
' AND rownum = 1 ' ;
gUpdateRootCtxSQL);
INSERT into icx_cat_item_prices(rt_item_id, price_type, supplier_site_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure,
supplier_site_code, price_list_id, request_id, created_by, creation_date,
last_updated_by, last_update_login, last_update_date, object_version_number, contract_num, contract_id,
negotiated_by_preparer_flag) --Bug#3107596
SELECT gItemIds(i), gPriceTypes(i), it.supplier_site_id, it.org_id,
'Y', 'SUPPLIER',
it.unit_price, it.currency, it.unit_of_measure,
it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
gUserLogin, sysdate , 1,
it.contract_num, it.contract_id, -- OEX_IP_PORTING
gNegotiatedPrice --Bug#3107596
FROM ICX_CAT_ITEMS_GT it
WHERE rowid = gRowIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = v_action;
INSERT INTO icx_cat_items_ctx_tlp
(rt_item_id, language, sequence, ctx_desc, org_id)
VALUES
(v_item_ids(i), gJobLanguage, v_sequence,
to_char(v_buyer_ids(i)), v_buyer_ids(i));
/* Processes the price updates in the current batch */
-- icx_por_ext_item.getBulkLoadActiveFlag() returns,
-- 'A' if the bulkloaded price is active and there is an ASL
-- 'Y' if the bulkloaded price is active and there is No ASL
-- 'N' if the bulkloaded price is inactive
PROCEDURE process_batch_addupdate_prices(p_batch_type IN VARCHAR2,
p_action IN VARCHAR2 DEFAULT 'UPDATE',
p_num_failed_lines OUT NOCOPY NUMBER) IS
i PLS_INTEGER;
v_price_updated dbms_sql.number_table;
INSERT into icx_cat_price_history(rt_item_id, status, price_type, asl_id, supplier_site_id,
contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure, functional_price,
supplier_site_code, contract_num, contract_line_num, price_list_id, archived_date,
last_update_login, last_updated_by, last_update_date, created_by, creation_date,
request_id, program_application_id, program_id, program_update_date, object_version_number)
SELECT rt_item_id, 'OUTDATED', price_type, asl_id, supplier_site_id,
contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure, functional_price,
supplier_site_code, contract_num, contract_line_num, price_list_id, sysdate,
last_update_login, last_updated_by, last_update_date, created_by, creation_date,
request_id, program_application_id, program_id, program_update_date,object_version_number
FROM icx_cat_item_prices
WHERE rt_item_id = gItemIds(i)
AND price_list_id = gPricelistIds(i)
AND org_id = gOrgIds(i)
AND supplier_site_id = gSupplierSiteIds(i)
AND gRowTypes(i) = p_batch_type
AND price_type in ('BULKLOAD', 'CONTRACT') --Bug#2719434
AND gSystemActions(i) = p_action;
v_price_updated(i) := SQL%BULK_ROWCOUNT(i);
if (gUoms(i) is null AND v_price_updated(i) = 0) then
-- For add operation you need the UOM,
-- For add, if UOM is not specified then reject the line
reject_line(gRowids(i), 'PRICE', '.UOM:ICX_POR_CAT_FIELD_REQUIRED');
v_price_updated(i) := -1;
INSERT into icx_cat_item_prices(rt_item_id, price_type, supplier_site_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure,
supplier_site_code, price_list_id, request_id, created_by, creation_date,
last_updated_by, last_update_login, last_update_date, contract_num, contract_id, object_version_number,
negotiated_by_preparer_flag) --Bug#3107596
SELECT gItemIds(i), gPriceTypes(i), it.supplier_site_id, it.org_id,
gActiveFlags(i),
'SUPPLIER', it.unit_price, it.currency, it.unit_of_measure,
it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
gUserLogin, sysdate, it.contract_num, it.contract_id , 1,
gNegotiatedPrice --Bug#3107596
FROM ICX_CAT_PRICES_GT it
WHERE rowid = gRowIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = p_action
AND v_price_updated(i) = 0;
UPDATE icx_cat_item_prices ip
SET (ip.unit_price, ip.currency,
ip.unit_of_measure,
ip.supplier_site_code, ip.request_id, ip.last_updated_by,
ip.last_update_login, ip.last_update_date, ip.contract_num,
ip.contract_id, ip.object_version_number,
ip.price_type, --Bug#3503280
negotiated_by_preparer_flag) --Bug#3107596
= (SELECT it.unit_price, it.currency,
nvl(it.unit_of_measure, ip.unit_of_measure),
it.supplier_site_code, gJobNumber, gUserId, gUserLogin, sysdate,
it.contract_num, it.contract_id, 1,
gPriceTypes(i), --Bug#3503280
gNegotiatedPrice --Bug#3107596
FROM ICX_CAT_PRICES_GT it
WHERE rowid = gRowIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = p_action
AND v_price_updated(i) = 1)
WHERE ip.rt_item_id = gItemIds(i)
AND ip.price_list_id = gPricelistIds(i)
AND ip.org_id = gOrgIds(i)
AND ip.supplier_site_id = gSupplierSiteIds(i)
AND gRowTypes(i) = p_batch_type
AND v_price_updated(i) > 0
AND ip.price_type in ( 'BULKLOAD', 'CONTRACT') --Bug#2719434
AND gSystemActions(i) = p_action;
INSERT into icx_cat_item_prices(rt_item_id, price_type, supplier_site_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure,
supplier_site_code, price_list_id, request_id, created_by, creation_date,
last_updated_by, last_update_login, last_update_date, contract_num, contract_id, object_version_number,
negotiated_by_preparer_flag) --Bug#3107596
SELECT gItemIds(i), gPriceTypes(i), it.supplier_site_id, it.org_id, gActiveFlags(i), 'SUPPLIER',
it.unit_price, it.currency, it.unit_of_measure,
it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
gUserLogin, sysdate, it.contract_num, it.contract_id , 1,
gNegotiatedPrice --Bug#3107596
FROM ICX_CAT_ITEMS_GT it
WHERE rowid = gRowIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = p_action
AND v_price_updated(i) = 0;
UPDATE icx_cat_item_prices ip
SET (ip.unit_price, ip.currency, ip.unit_of_measure, ip.supplier_site_id,
ip.supplier_site_code, ip.request_id, ip.last_updated_by, ip.last_update_login, ip.last_update_date, ip.contract_num, ip.contract_id, ip.object_version_number,
ip.price_type, --Bug#3503280
negotiated_by_preparer_flag) --Bug#3107596
= (SELECT it.unit_price, it.currency, nvl(it.unit_of_measure, ip.unit_of_measure), it.supplier_site_id,
it.supplier_site_code, gJobNumber, gUserId, gUserLogin, sysdate,
it.contract_num, it.contract_id, 1,
gPriceTypes(i), --Bug#3503280
gNegotiatedPrice --Bug#3107596
FROM ICX_CAT_ITEMS_GT it
WHERE rowid = gRowIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = p_action
AND v_price_updated(i) = 1)
WHERE ip.rt_item_id = gItemIds(i)
AND ip.price_list_id = gPricelistIds(i)
AND ip.org_id = gOrgIds(i)
AND ip.supplier_site_id = gSupplierSiteIds(i)
AND gRowTypes(i) = p_batch_type
AND v_price_updated(i) > 0
AND ip.price_type in ( 'BULKLOAD', 'CONTRACT') --Bug#2719434
AND gSystemActions(i) = p_action;
UPDATE icx_cat_item_prices
SET active_flag = 'N'
WHERE
rt_item_id = gItemIds(i)
AND gSystemActions(i) = p_action
AND price_type = 'ASL'
AND v_price_updated(i) = 0;
INSERT INTO icx_cat_items_ctx_tlp
(rt_item_id, language, sequence, ctx_desc, org_id)
SELECT gDistinctItemIds(i), tl.language, v_sequence,
to_char(gDistinctBuyerIds(i)), gDistinctBuyerIds(i)
FROM icx_cat_items_tlp tl
WHERE tl.rt_item_id = gDistinctItemIds(i)
AND NOT EXISTS
(SELECT 1 FROM icx_cat_items_ctx_tlp
WHERE rt_item_id = gDistinctItemIds(i)
AND org_id = gDistinctBuyerIds(i));
'Exception at ICX_POR_ITEM_UPLOAD.process_batch_addupdate_prices('
|| xErrLoc || '): ' || SQLERRM);
END process_batch_addupdate_prices;
PROCEDURE process_batch_addupdate_prices(p_batch_type IN VARCHAR2,
p_action IN VARCHAR2 DEFAULT 'UPDATE') IS
xErrLoc PLS_INTEGER := 100;
process_batch_addupdate_prices(p_batch_type, p_action, vNumFailedLines);
'Exception at ICX_POR_ITEM_UPLOAD.process_batch_addupdate_prices('
|| xErrLoc || ') 2 arg: ' || SQLERRM);
END process_batch_addupdate_prices;
/* Processes the price deletes in the current batch */
-- icx_por_ext_item.getBulkLoadActiveFlag() returns,
-- 'A' if the bulkloaded price is active and there is an ASL
-- 'Y' if the bulkloaded price is active and there is No ASL
-- 'N' if the bulkloaded price is inactive
PROCEDURE process_batch_delete_prices(p_batch_type IN VARCHAR2) IS
i PLS_INTEGER;
v_action VARCHAR2(10) := 'DELETE';
INSERT into icx_cat_price_history(rt_item_id, status, price_type, asl_id, supplier_site_id,
contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure,
supplier_site_code, contract_num, contract_line_num, price_list_id, archived_date,
last_update_login, last_updated_by, last_update_date, created_by, creation_date,
request_id, program_application_id, program_id, program_update_date, object_version_number)
SELECT rt_item_id, 'DELETED', price_type, asl_id, supplier_site_id,
contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
active_flag, search_type, unit_price, currency, unit_of_measure,
supplier_site_code, contract_num, contract_line_num, price_list_id, sysdate,
last_update_login, last_updated_by, last_update_date, created_by, creation_date,
request_id, program_application_id, program_id, program_update_date, object_version_number
FROM icx_cat_item_prices
WHERE rt_item_id = gItemIds(i)
AND price_list_id = gPricelistIds(i)
AND org_id = gOrgIds(i)
AND supplier_site_id = gSupplierSiteIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = v_action;
update icx_cat_item_prices
set active_flag = gActiveFlags(i)
where rt_item_id = gItemIds(i)
and active_flag = 'N'
and org_id = gOrgIds(i)
and supplier_site_id = gSupplierSiteIds(i)
and price_type = 'ASL'
and gRowTypes(i) = p_batch_type
and gSystemActions(i) = v_action;
DELETE from icx_cat_item_prices
WHERE rt_item_id = gItemIds(i)
AND price_list_id = gPricelistIds(i)
AND org_id = gOrgIds(i)
AND supplier_site_id = gSupplierSiteIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = v_action;
DELETE from icx_cat_price_history
WHERE rt_item_id = gItemIds(i)
AND price_list_id = gPricelistIds(i)
AND org_id = gOrgIds(i)
AND supplier_site_id = gSupplierSiteIds(i)
AND gRowTypes(i) = p_batch_type
AND gSystemActions(i) = v_action;
'Exception at ICX_POR_ITEM_UPLOAD.process_batch_delete_prices('
|| xErrLoc || '): ' || SQLERRM);
END process_batch_delete_prices;
Also updates the category information in icx_cat_items_tlp
when category is changed for an item
*/
PROCEDURE process_batch_common IS
i PLS_INTEGER;
INSERT into icx_cat_category_items(rt_category_id, rt_item_id, last_update_login, last_updated_by, last_update_date, created_by, creation_date)
VALUES (vCategoryIds(i), vItemIds(i), gUserLogin, gUserId, sysdate, gUserId, sysdate);
UPDATE icx_cat_category_items
SET rt_category_id = gCategoryIds(i)
WHERE gSystemActions(i) in ('UPDATE', 'TRANSLATE')
and rt_item_id = gItemIds(i)
and rt_category_id = gOldCategoryIds(i); --Bug#2714487
DELETE from icx_cat_category_items
WHERE gSystemActions(i) = 'DELETE'
and rt_category_id = gCategoryIds(i)
and rt_item_id = gItemIds(i);
INSERT into icx_cat_items_b(rt_item_id, org_id, supplier_id,supplier,
supplier_part_num, supplier_part_auxid, catalog_name, --Bug#2611529
extractor_updated_flag, request_id, created_by,
creation_date, last_updated_by, last_update_login, last_update_date, OBJECT_VERSION_NUMBER)
SELECT gItemIds(i), it.org_id, it.supplier_id, it.supplier,
it.supplier_part_num, it.supplier_part_auxid, gCatalogName, --Bug#2611529
'N', gJobNumber, gUserId, sysdate, gUserId,
gUserLogin, sysdate, 1
FROM ICX_CAT_ITEMS_GT it
WHERE it.rowid = gRowids(i)
AND v_action = gSystemActions(i);
INSERT into icx_cat_items_tlp(rt_item_id, language, org_id, supplier_id,
item_source_type, search_type, primary_category_id,
primary_category_name, supplier, supplier_part_num,
supplier_part_auxid, manufacturer,--Bug#2611529
manufacturer_url, supplier_url, --Bug#2611529
manufacturer_part_num, description, comments, alias, picture,
picture_url, attachment_url, long_description, unspsc_code,
thumbnail_image, availability, lead_time, item_type,
text_base_attribute1, text_base_attribute2, text_base_attribute3,
text_base_attribute4, text_base_attribute5, text_base_attribute6,
text_base_attribute7, text_base_attribute8, text_base_attribute9,
text_base_attribute10, text_base_attribute11, text_base_attribute12,
text_base_attribute13, text_base_attribute14, text_base_attribute15,
text_base_attribute16, text_base_attribute17, text_base_attribute18,
text_base_attribute19, text_base_attribute20, text_base_attribute21,
text_base_attribute22, text_base_attribute23, text_base_attribute24,
text_base_attribute25, text_base_attribute26, text_base_attribute27,
text_base_attribute28, text_base_attribute29, text_base_attribute30,
text_base_attribute31, text_base_attribute32, text_base_attribute33,
text_base_attribute34, text_base_attribute35, text_base_attribute36,
text_base_attribute37, text_base_attribute38, text_base_attribute39,
text_base_attribute40, text_base_attribute41, text_base_attribute42,
text_base_attribute43, text_base_attribute44, text_base_attribute45,
text_base_attribute46, text_base_attribute47, text_base_attribute48,
text_base_attribute49, text_base_attribute50, text_base_attribute51,
text_base_attribute52, text_base_attribute53, text_base_attribute54,
text_base_attribute55, text_base_attribute56, text_base_attribute57,
text_base_attribute58, text_base_attribute59, text_base_attribute60,
text_base_attribute61, text_base_attribute62, text_base_attribute63,
text_base_attribute64, text_base_attribute65, text_base_attribute66,
text_base_attribute67, text_base_attribute68, text_base_attribute69,
text_base_attribute70, text_base_attribute71, text_base_attribute72,
text_base_attribute73, text_base_attribute74, text_base_attribute75,
text_base_attribute76, text_base_attribute77, text_base_attribute78,
text_base_attribute79, text_base_attribute80, text_base_attribute81,
text_base_attribute82, text_base_attribute83, text_base_attribute84,
text_base_attribute85, text_base_attribute86, text_base_attribute87,
text_base_attribute88, text_base_attribute89, text_base_attribute90,
text_base_attribute91, text_base_attribute92, text_base_attribute93,
text_base_attribute94, text_base_attribute95, text_base_attribute96,
text_base_attribute97, text_base_attribute98, text_base_attribute99,
text_base_attribute100,
num_base_attribute1, num_base_attribute2, num_base_attribute3,
num_base_attribute4, num_base_attribute5, num_base_attribute6,
num_base_attribute7, num_base_attribute8, num_base_attribute9,
num_base_attribute10, num_base_attribute11, num_base_attribute12,
num_base_attribute13, num_base_attribute14, num_base_attribute15,
num_base_attribute16, num_base_attribute17, num_base_attribute18,
num_base_attribute19, num_base_attribute20, num_base_attribute21,
num_base_attribute22, num_base_attribute23, num_base_attribute24,
num_base_attribute25, num_base_attribute26, num_base_attribute27,
num_base_attribute28, num_base_attribute29, num_base_attribute30,
num_base_attribute31, num_base_attribute32, num_base_attribute33,
num_base_attribute34, num_base_attribute35, num_base_attribute36,
num_base_attribute37, num_base_attribute38, num_base_attribute39,
num_base_attribute40, num_base_attribute41, num_base_attribute42,
num_base_attribute43, num_base_attribute44, num_base_attribute45,
num_base_attribute46, num_base_attribute47, num_base_attribute48,
num_base_attribute49, num_base_attribute50, num_base_attribute51,
num_base_attribute52, num_base_attribute53, num_base_attribute54,
num_base_attribute55, num_base_attribute56, num_base_attribute57,
num_base_attribute58, num_base_attribute59, num_base_attribute60,
num_base_attribute61, num_base_attribute62, num_base_attribute63,
num_base_attribute64, num_base_attribute65, num_base_attribute66,
num_base_attribute67, num_base_attribute68, num_base_attribute69,
num_base_attribute70, num_base_attribute71, num_base_attribute72,
num_base_attribute73, num_base_attribute74, num_base_attribute75,
num_base_attribute76, num_base_attribute77, num_base_attribute78,
num_base_attribute79, num_base_attribute80, num_base_attribute81,
num_base_attribute82, num_base_attribute83, num_base_attribute84,
num_base_attribute85, num_base_attribute86, num_base_attribute87,
num_base_attribute88, num_base_attribute89, num_base_attribute90,
num_base_attribute91, num_base_attribute92, num_base_attribute93,
num_base_attribute94, num_base_attribute95, num_base_attribute96,
num_base_attribute97, num_base_attribute98, num_base_attribute99,
num_base_attribute100,
tl_text_base_attribute1, tl_text_base_attribute2, tl_text_base_attribute3,
tl_text_base_attribute4, tl_text_base_attribute5, tl_text_base_attribute6,
tl_text_base_attribute7, tl_text_base_attribute8, tl_text_base_attribute9,
tl_text_base_attribute10, tl_text_base_attribute11, tl_text_base_attribute12,
tl_text_base_attribute13, tl_text_base_attribute14, tl_text_base_attribute15,
tl_text_base_attribute16, tl_text_base_attribute17, tl_text_base_attribute18,
tl_text_base_attribute19, tl_text_base_attribute20, tl_text_base_attribute21,
tl_text_base_attribute22, tl_text_base_attribute23, tl_text_base_attribute24,
tl_text_base_attribute25, tl_text_base_attribute26, tl_text_base_attribute27,
tl_text_base_attribute28, tl_text_base_attribute29, tl_text_base_attribute30,
tl_text_base_attribute31, tl_text_base_attribute32, tl_text_base_attribute33,
tl_text_base_attribute34, tl_text_base_attribute35, tl_text_base_attribute36,
tl_text_base_attribute37, tl_text_base_attribute38, tl_text_base_attribute39,
tl_text_base_attribute40, tl_text_base_attribute41, tl_text_base_attribute42,
tl_text_base_attribute43, tl_text_base_attribute44, tl_text_base_attribute45,
tl_text_base_attribute46, tl_text_base_attribute47, tl_text_base_attribute48,
tl_text_base_attribute49, tl_text_base_attribute50, tl_text_base_attribute51,
tl_text_base_attribute52, tl_text_base_attribute53, tl_text_base_attribute54,
tl_text_base_attribute55, tl_text_base_attribute56, tl_text_base_attribute57,
tl_text_base_attribute58, tl_text_base_attribute59, tl_text_base_attribute60,
tl_text_base_attribute61, tl_text_base_attribute62, tl_text_base_attribute63,
tl_text_base_attribute64, tl_text_base_attribute65, tl_text_base_attribute66,
tl_text_base_attribute67, tl_text_base_attribute68, tl_text_base_attribute69,
tl_text_base_attribute70, tl_text_base_attribute71, tl_text_base_attribute72,
tl_text_base_attribute73, tl_text_base_attribute74, tl_text_base_attribute75,
tl_text_base_attribute76, tl_text_base_attribute77, tl_text_base_attribute78,
tl_text_base_attribute79, tl_text_base_attribute80, tl_text_base_attribute81,
tl_text_base_attribute82, tl_text_base_attribute83, tl_text_base_attribute84,
tl_text_base_attribute85, tl_text_base_attribute86, tl_text_base_attribute87,
tl_text_base_attribute88, tl_text_base_attribute89, tl_text_base_attribute90,
tl_text_base_attribute91, tl_text_base_attribute92, tl_text_base_attribute93,
tl_text_base_attribute94, tl_text_base_attribute95, tl_text_base_attribute96,
tl_text_base_attribute97, tl_text_base_attribute98, tl_text_base_attribute99,
tl_text_base_attribute100,
ctx_desc, request_id, created_by, creation_date, last_updated_by,
last_update_login, last_update_date)
SELECT gItemIds(i), gJobLanguage, it.org_id,
it.supplier_id, 'SUPPLIER', 'SUPPLIER',
it.rt_category_id, it.category_name,
it.supplier, it.supplier_part_num,
it.supplier_part_auxid, it.manufacturer, --Bug#2611529
it.manufacturer_url, it.supplier_url, --Bug#2611529
it.manufacturer_part_num, it.description, it.comments, it.alias, it.picture,
it.picture_url, it.attachment_url, it.long_description, it. unspsc_code,
it.thumbnail_image, it.availability, it.lead_time, it.item_type,
it.text_base_attribute1, it.text_base_attribute2, it.text_base_attribute3,
it.text_base_attribute4, it.text_base_attribute5, it.text_base_attribute6,
it.text_base_attribute7, it.text_base_attribute8, it.text_base_attribute9,
it.text_base_attribute10, it.text_base_attribute11, it.text_base_attribute12,
it.text_base_attribute13, it.text_base_attribute14, it.text_base_attribute15,
it.text_base_attribute16, it.text_base_attribute17, it.text_base_attribute18,
it.text_base_attribute19, it.text_base_attribute20, it.text_base_attribute21,
it.text_base_attribute22, it.text_base_attribute23, it.text_base_attribute24,
it.text_base_attribute25, it.text_base_attribute26, it.text_base_attribute27,
it.text_base_attribute28, it.text_base_attribute29, it.text_base_attribute30,
it.text_base_attribute31, it.text_base_attribute32, it.text_base_attribute33,
it.text_base_attribute34, it.text_base_attribute35, it.text_base_attribute36,
it.text_base_attribute37, it.text_base_attribute38, it.text_base_attribute39,
it.text_base_attribute40, it.text_base_attribute41, it.text_base_attribute42,
it.text_base_attribute43, it.text_base_attribute44, it.text_base_attribute45,
it.text_base_attribute46, it.text_base_attribute47, it.text_base_attribute48,
it.text_base_attribute49, it.text_base_attribute50, it.text_base_attribute51,
it.text_base_attribute52, it.text_base_attribute53, it.text_base_attribute54,
it.text_base_attribute55, it.text_base_attribute56, it.text_base_attribute57,
it.text_base_attribute58, it.text_base_attribute59, it.text_base_attribute60,
it.text_base_attribute61, it.text_base_attribute62, it.text_base_attribute63,
it.text_base_attribute64, it.text_base_attribute65, it.text_base_attribute66,
it.text_base_attribute67, it.text_base_attribute68, it.text_base_attribute69,
it.text_base_attribute70, it.text_base_attribute71, it.text_base_attribute72,
it.text_base_attribute73, it.text_base_attribute74, it.text_base_attribute75,
it.text_base_attribute76, it.text_base_attribute77, it.text_base_attribute78,
it.text_base_attribute79, it.text_base_attribute80, it.text_base_attribute81,
it.text_base_attribute82, it.text_base_attribute83, it.text_base_attribute84,
it.text_base_attribute85, it.text_base_attribute86, it.text_base_attribute87,
it.text_base_attribute88, it.text_base_attribute89, it.text_base_attribute90,
it.text_base_attribute91, it.text_base_attribute92, it.text_base_attribute93,
it.text_base_attribute94, it.text_base_attribute95, it.text_base_attribute96,
it.text_base_attribute97, it.text_base_attribute98, it.text_base_attribute99,
it.text_base_attribute100,
it.num_base_attribute1, it.num_base_attribute2, it.num_base_attribute3,
it.num_base_attribute4, it.num_base_attribute5, it.num_base_attribute6,
it.num_base_attribute7, it.num_base_attribute8, it.num_base_attribute9,
it.num_base_attribute10, it.num_base_attribute11, it.num_base_attribute12,
it.num_base_attribute13, it.num_base_attribute14, it.num_base_attribute15,
it.num_base_attribute16, it.num_base_attribute17, it.num_base_attribute18,
it.num_base_attribute19, it.num_base_attribute20, it.num_base_attribute21,
it.num_base_attribute22, it.num_base_attribute23, it.num_base_attribute24,
it.num_base_attribute25, it.num_base_attribute26, it.num_base_attribute27,
it.num_base_attribute28, it.num_base_attribute29, it.num_base_attribute30,
it.num_base_attribute31, it.num_base_attribute32, it.num_base_attribute33,
it.num_base_attribute34, it.num_base_attribute35, it.num_base_attribute36,
it.num_base_attribute37, it.num_base_attribute38, it.num_base_attribute39,
it.num_base_attribute40, it.num_base_attribute41, it.num_base_attribute42,
it.num_base_attribute43, it.num_base_attribute44, it.num_base_attribute45,
it.num_base_attribute46, it.num_base_attribute47, it.num_base_attribute48,
it.num_base_attribute49, it.num_base_attribute50, it.num_base_attribute51,
it.num_base_attribute52, it.num_base_attribute53, it.num_base_attribute54,
it.num_base_attribute55, it.num_base_attribute56, it.num_base_attribute57,
it.num_base_attribute58, it.num_base_attribute59, it.num_base_attribute60,
it.num_base_attribute61, it.num_base_attribute62, it.num_base_attribute63,
it.num_base_attribute64, it.num_base_attribute65, it.num_base_attribute66,
it.num_base_attribute67, it.num_base_attribute68, it.num_base_attribute69,
it.num_base_attribute70, it.num_base_attribute71, it.num_base_attribute72,
it.num_base_attribute73, it.num_base_attribute74, it.num_base_attribute75,
it.num_base_attribute76, it.num_base_attribute77, it.num_base_attribute78,
it.num_base_attribute79, it.num_base_attribute80, it.num_base_attribute81,
it.num_base_attribute82, it.num_base_attribute83, it.num_base_attribute84,
it.num_base_attribute85, it.num_base_attribute86, it.num_base_attribute87,
it.num_base_attribute88, it.num_base_attribute89, it.num_base_attribute90,
it.num_base_attribute91, it.num_base_attribute92, it.num_base_attribute93,
it.num_base_attribute94, it.num_base_attribute95, it.num_base_attribute96,
it.num_base_attribute97, it.num_base_attribute98, it.num_base_attribute99,
it.num_base_attribute100,
it.tl_text_base_attribute1, it.tl_text_base_attribute2, it.tl_text_base_attribute3,
it.tl_text_base_attribute4, it.tl_text_base_attribute5, it.tl_text_base_attribute6,
it.tl_text_base_attribute7, it.tl_text_base_attribute8, it.tl_text_base_attribute9,
it.tl_text_base_attribute10, it.tl_text_base_attribute11, it.tl_text_base_attribute12,
it.tl_text_base_attribute13, it.tl_text_base_attribute14, it.tl_text_base_attribute15,
it.tl_text_base_attribute16, it.tl_text_base_attribute17, it.tl_text_base_attribute18,
it.tl_text_base_attribute19, it.tl_text_base_attribute20, it.tl_text_base_attribute21,
it.tl_text_base_attribute22, it.tl_text_base_attribute23, it.tl_text_base_attribute24,
it.tl_text_base_attribute25, it.tl_text_base_attribute26, it.tl_text_base_attribute27,
it.tl_text_base_attribute28, it.tl_text_base_attribute29, it.tl_text_base_attribute30,
it.tl_text_base_attribute31, it.tl_text_base_attribute32, it.tl_text_base_attribute33,
it.tl_text_base_attribute34, it.tl_text_base_attribute35, it.tl_text_base_attribute36,
it.tl_text_base_attribute37, it.tl_text_base_attribute38, it.tl_text_base_attribute39,
it.tl_text_base_attribute40, it.tl_text_base_attribute41, it.tl_text_base_attribute42,
it.tl_text_base_attribute43, it.tl_text_base_attribute44, it.tl_text_base_attribute45,
it.tl_text_base_attribute46, it.tl_text_base_attribute47, it.tl_text_base_attribute48,
it.tl_text_base_attribute49, it.tl_text_base_attribute50, it.tl_text_base_attribute51,
it.tl_text_base_attribute52, it.tl_text_base_attribute53, it.tl_text_base_attribute54,
it.tl_text_base_attribute55, it.tl_text_base_attribute56, it.tl_text_base_attribute57,
it.tl_text_base_attribute58, it.tl_text_base_attribute59, it.tl_text_base_attribute60,
it.tl_text_base_attribute61, it.tl_text_base_attribute62, it.tl_text_base_attribute63,
it.tl_text_base_attribute64, it.tl_text_base_attribute65, it.tl_text_base_attribute66,
it.tl_text_base_attribute67, it.tl_text_base_attribute68, it.tl_text_base_attribute69,
it.tl_text_base_attribute70, it.tl_text_base_attribute71, it.tl_text_base_attribute72,
it.tl_text_base_attribute73, it.tl_text_base_attribute74, it.tl_text_base_attribute75,
it.tl_text_base_attribute76, it.tl_text_base_attribute77, it.tl_text_base_attribute78,
it.tl_text_base_attribute79, it.tl_text_base_attribute80, it.tl_text_base_attribute81,
it.tl_text_base_attribute82, it.tl_text_base_attribute83, it.tl_text_base_attribute84,
it.tl_text_base_attribute85, it.tl_text_base_attribute86, it.tl_text_base_attribute87,
it.tl_text_base_attribute88, it.tl_text_base_attribute89, it.tl_text_base_attribute90,
it.tl_text_base_attribute91, it.tl_text_base_attribute92, it.tl_text_base_attribute93,
it.tl_text_base_attribute94, it.tl_text_base_attribute95, it.tl_text_base_attribute96,
it.tl_text_base_attribute97, it.tl_text_base_attribute98, it.tl_text_base_attribute99,
it.tl_text_base_attribute100,
null, gJobNumber, gUserId, sysdate, gUserId, gUserLogin, sysdate
FROM ICX_CAT_ITEMS_GT it
WHERE it.rowid = gRowids(i)
AND v_action = gSystemActions(i);
INSERT into icx_cat_ext_items_tlp(rt_item_id, language, org_id, request_id,
rt_category_id, primary_flag,
text_cat_attribute1, text_cat_attribute2, text_cat_attribute3,
text_cat_attribute4, text_cat_attribute5, text_cat_attribute6,
text_cat_attribute7, text_cat_attribute8, text_cat_attribute9,
text_cat_attribute10, text_cat_attribute11, text_cat_attribute12,
text_cat_attribute13, text_cat_attribute14, text_cat_attribute15,
text_cat_attribute16, text_cat_attribute17, text_cat_attribute18,
text_cat_attribute19, text_cat_attribute20, text_cat_attribute21,
text_cat_attribute22, text_cat_attribute23, text_cat_attribute24,
text_cat_attribute25, text_cat_attribute26, text_cat_attribute27,
text_cat_attribute28, text_cat_attribute29, text_cat_attribute30,
text_cat_attribute31, text_cat_attribute32, text_cat_attribute33,
text_cat_attribute34, text_cat_attribute35, text_cat_attribute36,
text_cat_attribute37, text_cat_attribute38, text_cat_attribute39,
text_cat_attribute40, text_cat_attribute41, text_cat_attribute42,
text_cat_attribute43, text_cat_attribute44, text_cat_attribute45,
text_cat_attribute46, text_cat_attribute47, text_cat_attribute48,
text_cat_attribute49, text_cat_attribute50,
num_cat_attribute1, num_cat_attribute2, num_cat_attribute3,
num_cat_attribute4, num_cat_attribute5, num_cat_attribute6,
num_cat_attribute7, num_cat_attribute8, num_cat_attribute9,
num_cat_attribute10, num_cat_attribute11, num_cat_attribute12,
num_cat_attribute13, num_cat_attribute14, num_cat_attribute15,
num_cat_attribute16, num_cat_attribute17, num_cat_attribute18,
num_cat_attribute19, num_cat_attribute20, num_cat_attribute21,
num_cat_attribute22, num_cat_attribute23, num_cat_attribute24,
num_cat_attribute25, num_cat_attribute26, num_cat_attribute27,
num_cat_attribute28, num_cat_attribute29, num_cat_attribute30,
num_cat_attribute31, num_cat_attribute32, num_cat_attribute33,
num_cat_attribute34, num_cat_attribute35, num_cat_attribute36,
num_cat_attribute37, num_cat_attribute38, num_cat_attribute39,
num_cat_attribute40, num_cat_attribute41, num_cat_attribute42,
num_cat_attribute43, num_cat_attribute44, num_cat_attribute45,
num_cat_attribute46, num_cat_attribute47, num_cat_attribute48,
num_cat_attribute49, num_cat_attribute50,
tl_text_cat_attribute1, tl_text_cat_attribute2, tl_text_cat_attribute3,
tl_text_cat_attribute4, tl_text_cat_attribute5, tl_text_cat_attribute6,
tl_text_cat_attribute7, tl_text_cat_attribute8, tl_text_cat_attribute9,
tl_text_cat_attribute10, tl_text_cat_attribute11, tl_text_cat_attribute12,
tl_text_cat_attribute13, tl_text_cat_attribute14, tl_text_cat_attribute15,
tl_text_cat_attribute16, tl_text_cat_attribute17, tl_text_cat_attribute18,
tl_text_cat_attribute19, tl_text_cat_attribute20, tl_text_cat_attribute21,
tl_text_cat_attribute22, tl_text_cat_attribute23, tl_text_cat_attribute24,
tl_text_cat_attribute25, tl_text_cat_attribute26, tl_text_cat_attribute27,
tl_text_cat_attribute28, tl_text_cat_attribute29, tl_text_cat_attribute30,
tl_text_cat_attribute31, tl_text_cat_attribute32, tl_text_cat_attribute33,
tl_text_cat_attribute34, tl_text_cat_attribute35, tl_text_cat_attribute36,
tl_text_cat_attribute37, tl_text_cat_attribute38, tl_text_cat_attribute39,
tl_text_cat_attribute40, tl_text_cat_attribute41, tl_text_cat_attribute42,
tl_text_cat_attribute43, tl_text_cat_attribute44, tl_text_cat_attribute45,
tl_text_cat_attribute46, tl_text_cat_attribute47, tl_text_cat_attribute48,
tl_text_cat_attribute49, tl_text_cat_attribute50,
created_by, creation_date, last_updated_by,
last_update_login, last_update_date)
SELECT gItemIds(i), gJobLanguage, it.org_id, gJobNumber,
it.rt_category_id, 'Y',
it.text_cat_attribute1, it.text_cat_attribute2, it.text_cat_attribute3,
it.text_cat_attribute4, it.text_cat_attribute5, it.text_cat_attribute6,
it.text_cat_attribute7, it.text_cat_attribute8, it.text_cat_attribute9,
it.text_cat_attribute10, it.text_cat_attribute11, it.text_cat_attribute12,
it.text_cat_attribute13, it.text_cat_attribute14, it.text_cat_attribute15,
it.text_cat_attribute16, it.text_cat_attribute17, it.text_cat_attribute18,
it.text_cat_attribute19, it.text_cat_attribute20, it.text_cat_attribute21,
it.text_cat_attribute22, it.text_cat_attribute23, it.text_cat_attribute24,
it.text_cat_attribute25, it.text_cat_attribute26, it.text_cat_attribute27,
it.text_cat_attribute28, it.text_cat_attribute29, it.text_cat_attribute30,
it.text_cat_attribute31, it.text_cat_attribute32, it.text_cat_attribute33,
it.text_cat_attribute34, it.text_cat_attribute35, it.text_cat_attribute36,
it.text_cat_attribute37, it.text_cat_attribute38, it.text_cat_attribute39,
it.text_cat_attribute40, it.text_cat_attribute41, it.text_cat_attribute42,
it.text_cat_attribute43, it.text_cat_attribute44, it.text_cat_attribute45,
it.text_cat_attribute46, it.text_cat_attribute47, it.text_cat_attribute48,
it.text_cat_attribute49, it.text_cat_attribute50,
it.num_cat_attribute1, it.num_cat_attribute2, it.num_cat_attribute3,
it.num_cat_attribute4, it.num_cat_attribute5, it.num_cat_attribute6,
it.num_cat_attribute7, it.num_cat_attribute8, it.num_cat_attribute9,
it.num_cat_attribute10, it.num_cat_attribute11, it.num_cat_attribute12,
it.num_cat_attribute13, it.num_cat_attribute14, it.num_cat_attribute15,
it.num_cat_attribute16, it.num_cat_attribute17, it.num_cat_attribute18,
it.num_cat_attribute19, it.num_cat_attribute20, it.num_cat_attribute21,
it.num_cat_attribute22, it.num_cat_attribute23, it.num_cat_attribute24,
it.num_cat_attribute25, it.num_cat_attribute26, it.num_cat_attribute27,
it.num_cat_attribute28, it.num_cat_attribute29, it.num_cat_attribute30,
it.num_cat_attribute31, it.num_cat_attribute32, it.num_cat_attribute33,
it.num_cat_attribute34, it.num_cat_attribute35, it.num_cat_attribute36,
it.num_cat_attribute37, it.num_cat_attribute38, it.num_cat_attribute39,
it.num_cat_attribute40, it.num_cat_attribute41, it.num_cat_attribute42,
it.num_cat_attribute43, it.num_cat_attribute44, it.num_cat_attribute45,
it.num_cat_attribute46, it.num_cat_attribute47, it.num_cat_attribute48,
it.num_cat_attribute49, it.num_cat_attribute50,
it.tl_text_cat_attribute1, it.tl_text_cat_attribute2, it.tl_text_cat_attribute3,
it.tl_text_cat_attribute4, it.tl_text_cat_attribute5, it.tl_text_cat_attribute6,
it.tl_text_cat_attribute7, it.tl_text_cat_attribute8, it.tl_text_cat_attribute9,
it.tl_text_cat_attribute10, it.tl_text_cat_attribute11, it.tl_text_cat_attribute12,
it.tl_text_cat_attribute13, it.tl_text_cat_attribute14, it.tl_text_cat_attribute15,
it.tl_text_cat_attribute16, it.tl_text_cat_attribute17, it.tl_text_cat_attribute18,
it.tl_text_cat_attribute19, it.tl_text_cat_attribute20, it.tl_text_cat_attribute21,
it.tl_text_cat_attribute22, it.tl_text_cat_attribute23, it.tl_text_cat_attribute24,
it.tl_text_cat_attribute25, it.tl_text_cat_attribute26, it.tl_text_cat_attribute27,
it.tl_text_cat_attribute28, it.tl_text_cat_attribute29, it.tl_text_cat_attribute30,
it.tl_text_cat_attribute31, it.tl_text_cat_attribute32, it.tl_text_cat_attribute33,
it.tl_text_cat_attribute34, it.tl_text_cat_attribute35, it.tl_text_cat_attribute36,
it.tl_text_cat_attribute37, it.tl_text_cat_attribute38, it.tl_text_cat_attribute39,
it.tl_text_cat_attribute40, it.tl_text_cat_attribute41, it.tl_text_cat_attribute42,
it.tl_text_cat_attribute43, it.tl_text_cat_attribute44, it.tl_text_cat_attribute45,
it.tl_text_cat_attribute46, it.tl_text_cat_attribute47, it.tl_text_cat_attribute48,
it.tl_text_cat_attribute49, it.tl_text_cat_attribute50,
gUserId, sysdate, gUserId, gUserLogin, sysdate
FROM ICX_CAT_ITEMS_GT it
WHERE it.rowid = gRowids(i)
AND v_action = gSystemActions(i);
/* Processes the item updates in the current batch */
PROCEDURE process_batch_update IS
i PLS_INTEGER;
v_action VARCHAR2(10) := 'UPDATE';
v_delete_ctx_sql VARCHAR2(255);
vUpdateItemsBSQL VARCHAR2(1000);
UPDATE icx_cat_items_b
SET catalog_name = gCatalogName,
last_updated_by = gUserId,
last_update_login = gUserLogin,
last_update_date = sysdate,
request_id = gJobNumber,
object_version_number = object_version_number+1
WHERE rt_item_id = gCurrentItemIds(i) ;
DBMS_SQL.parse(v_cursor_id, gUpdateItemsNonTLSQL, DBMS_SQL.NATIVE);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'UPDATE', 10);
DBMS_SQL.parse(v_cursor_id, gUpdateItemsTLSQL, DBMS_SQL.NATIVE);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'UPDATE', 10);
DELETE FROM icx_cat_items_ctx_tlp
WHERE rt_item_id = gItemIds(i)
AND gSystemActions(i) = 'UPDATE';
handle_category_change('UPDATE');
DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).updateNonTLSQL,
DBMS_SQL.NATIVE);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action',
'UPDATE', 10);
DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).updateTLSQL,
DBMS_SQL.NATIVE);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action',
'UPDATE', 10);
FOR j IN 1..gUpdateCatCtxSQL.COUNT LOOP
v_cursor_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse(v_cursor_id, gUpdateCatCtxSQL(j),
dbms_sql.native);
FOR i IN 1..gUpdateRootCtxSQL.COUNT LOOP
v_cursor_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse(v_cursor_id, gUpdateRootCtxSQL(i), dbms_sql.native);
IF (i = gUpdateRootCtxSQL.COUNT - 1) THEN
-- This is the line
DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',10000);
ELSIF (i = gUpdateRootCtxSQL.COUNT) THEN
-- This is the line
DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':p_sequence',15000);
INSERT INTO icx_cat_items_ctx_tlp
(rt_item_id, language, sequence, ctx_desc, org_id,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
CREATED_BY, CREATION_DATE)
SELECT gItemIds(i), tl.language, v_sequence,
to_char(pll.org_id), pll.org_id,
gUserLogin, gUserId, sysdate,
gUserId, sysdate
FROM icx_cat_items_tlp tl,
(SELECT DISTINCT org_id
FROM icx_cat_item_prices pll
WHERE rt_item_id = gCurrentItemIds(i)
) pll
WHERE tl.rt_item_id = gItemIds(i)
AND gSystemActions(i) = v_action;
'Exception at ICX_POR_ITEM_UPLOAD.process_batch_update('
|| xErrLoc || '): ' || SQLERRM);
END process_batch_update;
PROCEDURE process_batch_delete IS
i PLS_INTEGER;
v_action VARCHAR2(10) := 'DELETE';
DELETE from icx_cat_items_b
WHERE rt_item_id = gItemIds(i)
AND v_action = gSystemActions(i);
DELETE from icx_cat_items_tlp
WHERE rt_item_id = gItemIds(i)
AND v_action = gSystemActions(i);
DELETE from icx_cat_items_ctx_tlp
WHERE rt_item_id = gItemIds(i)
AND v_action = gSystemActions(i);
DELETE from icx_cat_ext_items_tlp
WHERE rt_item_id = gItemIds(i)
AND v_action = gSystemActions(i);
DELETE from icx_cat_category_items
WHERE rt_item_id = gItemIds(i)
AND v_action = gSystemActions(i);
delete from por_favorite_list_lines
where rt_item_id = gItemIds(i)
AND v_action = gSystemActions(i);
'Exception at ICX_POR_ITEM_UPLOAD.process_batch_delete('
|| xErrLoc || '): ' || SQLERRM);
END process_batch_delete;
v_delete_ctx_sql VARCHAR2(255);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE', 10);
DBMS_SQL.parse(v_cursor_id, gUpdateItemsNonTLSQL, DBMS_SQL.NATIVE);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE', 10);
v_delete_ctx_sql := 'delete from icx_cat_items_ctx_tlp where rt_item_id = :rt_item_id and :p_action=:update_action';
DBMS_SQL.parse(v_cursor_id, v_delete_ctx_sql, dbms_sql.native);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE');
DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE', 10);
DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).updateNonTLSQL,
DBMS_SQL.NATIVE);
DBMS_SQL.bind_variable(v_cursor_id, ':update_action',
'TRANSLATE', 10);
FOR j IN 1..gUpdateCatCtxSQL.COUNT LOOP
v_cursor_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse(v_cursor_id, gUpdateCatCtxSQL(j),
dbms_sql.native);
FOR i IN 1..gUpdateRootCtxSQL.COUNT LOOP
v_cursor_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse(v_cursor_id, gUpdateRootCtxSQL(i), dbms_sql.native);
IF (i = gUpdateRootCtxSQL.COUNT - 1) THEN
-- This is the line
DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',10000);
ELSIF (i = gUpdateRootCtxSQL.COUNT) THEN
-- This is the line
DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',15000);
INSERT INTO icx_cat_items_ctx_tlp
(rt_item_id, language, sequence, ctx_desc, org_id)
SELECT gItemIds(i), 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 pll
WHERE rt_item_id = gCurrentItemIds(i)
) pll
WHERE tl.rt_item_id = gItemIds(i)
AND gSystemActions(i) = v_action;
PROCEDURE process_batch_prices(pHasAdd IN BOOLEAN, pHasDelete IN BOOLEAN, pNumFailedLines IN OUT NOCOPY NUMBER) IS
xErrLoc PLS_INTEGER := 100;
process_batch_addupdate_prices('PRICE', 'ADD', pNumFailedLines);
IF (pHasDelete) THEN
xErrLoc := 500;
process_batch_delete_prices('PRICE');
pHasUpdate IN BOOLEAN, pHasDelete IN BOOLEAN,
pHasTranslate IN BOOLEAN, pHasPrices IN BOOLEAN) IS
xErrLoc PLS_INTEGER := 100;
IF (pHasUpdate) THEN
xErrLoc := 800;
process_batch_update;
process_batch_addupdate_prices('ITEM_PRICE','UPDATE');
process_batch_addupdate_prices('ITEM_PRICE','TRANSLATE');
IF (pHasDelete) THEN
xErrLoc := 1200;
process_batch_delete;
process_batch_delete_prices('ITEM_PRICE');
** insert these pairs of error messages into table
** icx_por_failed_line_messages.
**/
PROCEDURE save_failed_line_message (p_request_id NUMBER,
p_line_number NUMBER, p_error_message VARCHAR2) IS
xErrLoc INTEGER := 0;
insert into icx_por_failed_line_messages
(job_number, line_number, descriptor_key, message_name)
values
(p_request_id, p_line_number, xKey, xMessage);
select d.key
from icx_cat_descriptors_tl d
where d.rt_category_id = v_category_id
-- and d.class in ('POM_CAT_ATTR')
and d.language = gJobLanguage
order by d.rt_descriptor_id;
gErrorCatDescKeys.DELETE;
select rt_descriptor_id, type, key,
section_tag, stored_in_column, stored_in_table,
searchable
from icx_cat_descriptors_tl
where rt_category_id = p_category_id
and language = gJobLanguage
and stored_in_table = 'ICX_CAT_ITEMS_TLP'
and rt_descriptor_id >= 100000
order by rt_descriptor_id;
v_rec.LAST_UPDATE_LOGIN := p_failed_item.LAST_UPDATE_LOGIN;
v_rec.LAST_UPDATED_BY := p_failed_item.LAST_UPDATED_BY;
v_rec.LAST_UPDATE_DATE := p_failed_item.LAST_UPDATE_DATE;
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, root_desc.key, v_rec.roots(v_section_tag));
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.category_name);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, gErrorCatDescKeys(i), v_rec.locals(i));
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.unit_price);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.currency);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.unit_of_measure);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.buyer_name);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.price_list_name);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.supplier);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.supplier_part_num);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.supplier_site_code);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.supplier_part_auxid);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, v_rec.line_number, v_rec.system_action,
v_rec.row_type, vDescKey, v_rec.description);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.supplier_name);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.supplier_part_num);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.supplier_part_auxid);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.supplier_site_code);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.unit_price);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.currency);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.unit_of_measure);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.buyer_name);
insert into icx_por_failed_lines
(job_number, line_number, action,
row_type, descriptor_key, descriptor_value)
values
(gJobNumber, p_line.line_number, p_line.system_action,
vRowType, vDescKey, p_line.price_list_name);
INSERT INTO icx_por_failed_line_messages
(job_number, line_number, descriptor_key, message_name)
VALUES
(p_request_id, p_line_number, vDescName, 'ICX_POR_CAT_FIELD_REQUIRED');
SELECT line_number, decode(action, 'SYNC','ADD',action) system_action,
supplier, supplier_part_num, supplier_part_auxid, --Bug#2611529
supplier_site_code, --Bug#
--Bug#2785949
--Removed the to_char function as
--unit_price in ICX_CAT_PRICES_GT is already VARCHAR type
unit_price, currency, unit_of_measure,
buyer_name, price_list_name,
error_message, error_flag
FROM ICX_CAT_PRICES_GT
WHERE rowid = p_rowid;
UPDATE icx_por_failed_lines SET action = v_rec.system_action
WHERE job_number = gJobNumber
AND line_number = v_rec.line_number;
SELECT * FROM ICX_CAT_ITEMS_GT
WHERE rowid = p_rowid;
UPDATE icx_por_failed_lines SET action = v_rec.system_action
WHERE job_number = gJobNumber
AND line_number = v_rec.line_number;
UPDATE ICX_CAT_ITEMS_GT SET error_message = error_message ||
p_error_message
WHERE rowid = p_row_id;
UPDATE ICX_CAT_PRICES_GT SET error_message = error_message ||
p_error_message
WHERE rowid = p_row_id;
SELECT it.rowid, it.supplier,
it.supplier_part_num, it.supplier_part_auxid, it.org_id,
it.supplier_site_id, it.currency
FROM ICX_CAT_PRICES_GT it
ORDER BY it.supplier asc, it.supplier_part_num asc,
it.supplier_part_auxid asc,
it.org_id asc, it.currency asc, it.supplier_site_id asc,
it.line_number desc;
vRowids.DELETE;
vSupplierPartNums.DELETE;
vSupplierNames.DELETE; -- Bug#2611529
vOrgIds.DELETE;
vSupplierSiteIds.DELETE;
vCurrencies.DELETE;
SELECT /*+ USE_NL(it i ui) LEADING(it) */ it.rowid,
--DUPE FAILED LINES Contract Auto Sourcing
it.line_number,
it.supplier_id,
decode(it.contract_num, null, 'BULKLOAD', 'CONTRACT'),
it.supplier_part_num,
i.rt_item_id,
decode(i.extractor_updated_flag, 'N', 'Y', 'Y', decode(icx_por_ext_item.getBulkLoadActiveFlag(it.action, i.rt_item_id), 'N', 'N', 'Y')),
decode(it.action, 'SYNC', 'ADD', it.action),
decode(it.error_flag, 'Y', 'Y',
decode(it.error_message, NULL, 'N', 'Y')),
it.row_type,
it.org_id,
it.price_list_id,
it.supplier_site_id,
it.unit_of_measure
FROM ICX_CAT_PRICES_GT it,
icx_cat_items_b i
WHERE it.supplier = i.supplier (+)
AND it.supplier_part_num = i.supplier_part_num (+)
AND it.supplier_part_auxid = i.supplier_part_auxid (+) -- Bug#2611529
AND it.org_id = i.org_id(+)
AND it.processed_flag = 'N';
vHasDelete BOOLEAN := FALSE;
ELSIF (vSystemActions(i) = 'DELETE') THEN
vHasDelete := TRUE;
process_batch_prices(vHasAdd, vHasDelete, vNumFailedLines);
vHasDelete := FALSE;
UPDATE ICX_CAT_PRICES_GT
SET processed_flag = 'Y' --Bug#2587763: set to "Y" instead of null.
WHERE rowid = vRowids(i);
SELECT it.rowid, it.supplier, it.supplier_part_num, it.supplier_part_auxid, it.org_id, row_type, it.supplier_site_id, it.currency
FROM ICX_CAT_ITEMS_GT it
WHERE language = p_language
ORDER BY supplier asc, supplier_part_num asc, supplier_part_auxid asc, org_id asc, supplier_site_id asc, currency asc, line_number desc;
vRowids.DELETE;
vOrgIds.DELETE;
vSupplierPartNums.DELETE;
vSupplierPartAuxids.DELETE; -- Bug#2611529
vSupplierNames.DELETE; -- Bug#2611529
vCurrencies.DELETE;
vRowTypes.DELETE;
vSupplierSiteIds.DELETE;
vDupeRowIds.DELETE;
store these rowIds cwwhich we need to mark it as 'D' to insert into item_prices by
calling move_items with processed_flag as 'D' for these rows */
ELSIF (vSupplierPartNums(i) = vPrevPartNum
AND vSupplierNames(i) = vPrevSupplierName
AND vSupplierPartAuxids(i) = vPrevPartAuxid
AND vOrgIds(i) = vPrevOrgId) THEN
vDupeRowIds(vDupeRowIds.COUNT + 1) := vRowids(i);
UPDATE ICX_CAT_ITEMS_GT
SET PROCESSED_FLAG = 'D'
WHERE ROWID = vDupeRowIds(i);
SELECT /*+ USE_NL(it i tl) LEADING(it) */
it.rowid,
it.supplier, --Bug#2729328
it.supplier_id, --Bug#2611529
it.supplier_part_num,
it.supplier_part_auxid, --Bug#2611529
i.rt_item_id,
decode(it.action, 'SYNC',
decode(i.rt_item_id, NULL, 'ADD',
decode(tl.rt_item_id, NULL, 'TRANSLATE', 'UPDATE')), it.action),
decode(it.error_flag, 'Y', 'Y',
decode(it.error_message, NULL, 'N', 'Y')),
decode(it.error_flag, 'Y', i.rt_item_id,
decode(it.action, 'SYNC',
decode(i.rt_item_id, NULL, icx_por_itemid.NEXTVAL,
i.rt_item_id), i.rt_item_id)),
it.row_type,
decode(it.contract_num, null, 'BULKLOAD', 'CONTRACT'),
it.rt_category_id,
tl.primary_category_id,
it.category_name,
it.org_id,
it.price_list_id,
it.supplier_site_id,
i.extractor_updated_flag,
decode(i.extractor_updated_flag, 'N', 'Y', 'Y', decode(icx_por_ext_item.getBulkLoadActiveFlag(it.action, i.rt_item_id), 'N', 'N', 'Y')),
decode(it.required_descriptors, NULL, 'N', 'Y'),
decode(it.required_tl_descriptors, NULL, 'N', 'Y'),
it.unit_price,
it.unit_of_measure
,it.system_action
FROM ICX_CAT_ITEMS_GT it,
icx_cat_items_b i, --Bug#2714487: dont join with category_items
icx_cat_items_tlp tl
WHERE it.supplier = i.supplier (+)
AND it.supplier_part_num = i.supplier_part_num (+)
AND it.supplier_part_auxid = i.supplier_part_auxid (+) --Bug#2611529
AND it.org_id = i.org_id(+)
AND i.rt_item_id = tl.rt_item_id (+)
AND gJobLanguage = tl.language (+)
AND gJobLanguage = it.language
-- Bug# 3366614 sosingha: p_processed_flag willl have a value 'N' first and then 'D' if duplicates exists
-- AND it.processed_flag = 'N';
vExtractorUpdatedFlags dbms_sql.varchar2_table;
vHasUpdate BOOLEAN := FALSE;
vHasDelete BOOLEAN := FALSE;
vSupplierSiteIds, vExtractorUpdatedFlags, vActiveFlags,
vMissingRequired, vMissingTLRequired, vUnitPrices, vUoms
, vGTSystemAction
LIMIT BATCH_SIZE;
IF (vSystemActions(i) = 'DELETE' AND
vSupplierPartNums(i) IS NOT NULL AND
vCurrentItemIds(i) IS NULL) THEN
vHasErrors(i) := 'Y';
IF (vSystemActions(i) = 'DELETE' AND
vSupplierPartNums(i) IS NOT NULL AND
vCurrentItemIds(i) IS NOT NULL AND vExtractorUpdatedFlags(i) = 'Y') THEN
vHasErrors(i) := 'Y';
'.SUPPLIER_PART_NUM:ICX_POR_DELETE_EXTRACTED_ITEM');
IF ( vGTSystemAction(i) IN ('UPDATE', 'DELETE') AND
vCurrentItemIds(i) IS NULL ) THEN
--This will happen only when item template files is used to update
--an item that does not exists
xErrLoc := 1005;
gExtractorUpdatedFlags(vCount) := vExtractorUpdatedFlags(i);
ELSIF (vSystemActions(i) = 'UPDATE') THEN
vHasUpdate := TRUE;
ELSIF (vSystemActions(i) = 'DELETE') THEN
vHasDelete := TRUE;
process_batch_items(vHasAdd, vHasUpdate, vHasDelete,
vHasTranslate, vHasPrices);
vHasUpdate := FALSE;
vHasDelete := FALSE;
UPDATE ICX_CAT_ITEMS_GT
SET system_action = vSystemActions(i)
WHERE rowid = vRowids(i)
AND vHasErrors(i) = 'Y';
UPDATE ICX_CAT_ITEMS_GT
SET processed_flag = 'Y' --Bug#2958208: set to "Y" instead of null.
WHERE rowid = vRowids(i);
SELECT language_code INTO gBaseLanguage
FROM fnd_languages
WHERE installed_flag = 'B';
SELECT language_code INTO gBaseLanguage
FROM fnd_languages
WHERE installed_flag = 'B';
vSQL := 'SELECT rowid FROM ' || vTableName ||
' WHERE error_flag IS NULL and (error_message IS NOT NULL OR ' ||
' (system_action IN (''ADD'') and required_descriptors IS NOT NULL) OR ' ||
' (system_action IN (''ADD'', ''TRANSLATE'') ' ||
' and required_tl_descriptors IS NOT NULL))';
INSERT INTO icx_cat_price_lists (price_list_id, name, supplier_id,
buyer_id, description, currency, begindate, enddate, action, status,
type, parent_header_id, creation_date, published_date, approval_date,
created_by, last_update_date, last_updated_by,
last_update_login, request_id) values
(icx_por_price_lists_s.nextval, p_price_list_name,
p_supplier_id, p_buyer_id,null,p_currency,
to_date(p_begindate, DEFAULT_DATE_FORMAT),
to_date(p_enddate, DEFAULT_DATE_FORMAT),
'ADD', 'APPROVED', p_type, null, sysdate, sysdate, sysdate,
p_user_id, sysdate, p_user_id, p_user_id, p_request_id)
RETURNING price_list_id INTO p_header_id;
PROCEDURE update_price_list( p_header_id IN NUMBER,
p_begindate IN VARCHAR2,
p_enddate IN VARCHAR2,
p_user_id IN NUMBER,
p_request_id IN NUMBER) IS
xErrLoc INTEGER;
DELETE FROM icx_cat_price_lists
WHERE price_list_id = p_header_id
AND status IN ('UNPUBLISHED', 'PUBLISHED');
INSERT INTO icx_cat_price_lists (price_list_id, name, supplier_id,
buyer_id, description, currency, begindate, enddate, action, status,
type, parent_header_id,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, request_id)
SELECT pl.price_list_id , pl.name,
pl.supplier_id, pl.buyer_id, pl.description, pl.currency,
decode(p_begindate, null, pl.begindate,
to_date(p_begindate, DEFAULT_DATE_FORMAT)),
decode(p_enddate, null, pl.enddate,
to_date(p_enddate, DEFAULT_DATE_FORMAT)) ,
'UPDATE', 'UNPUBLISHED',
pl.type, pl.parent_header_id, pl.creation_date,
pl.created_by, sysdate, p_user_id, p_user_id, p_request_id
FROM icx_cat_price_lists pl
WHERE pl.price_list_id = p_header_id
AND pl.status = 'APPROVED';
SELECT type INTO p_type FROM icx_cat_price_lists
WHERE price_list_id = p_header_id
AND status = 'APPROVED';
'Exception at ICX_POR_ITEM_UPLOAD.update_price_list('
|| xErrLoc || '): ' || SQLERRM);
END update_price_list;
PROCEDURE delete_price_list( p_header_id IN NUMBER ) IS
xErrLoc INTEGER;
DELETE FROM icx_cat_price_lists
WHERE price_list_id = p_header_id;
'Exception at ICX_POR_ITEM_UPLOAD.delete_price_list('
|| xErrLoc || '): ' || SQLERRM);
END delete_price_list;
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, 'PRICE', 'PRICE', p_amount);
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, 'PRICE', 'CURRENCY', p_currency);
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, 'PRICE', 'UOM', p_uom);
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, 'PRICE', 'BUYER', p_buyer_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, 'PRICE', 'SUPPLIER', p_supplier_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, 'PRICE', 'SUPPLIER_PART_NUM', p_supplier_part_num);
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, 'PRICE', 'SUPPLIER_PART_AUXID', p_supplier_part_auxid);
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, 'PRICE', 'SUPPLIER_SITE', p_supplier_site_code);
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, 'PRICE', 'PRICELIST', p_price_list_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, 'PRICE', 'PRICE', p_price_code);
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, 'PRICE', 'SUPPLIERCOMMENTS', p_price_list_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, 'PRICE', 'BEGINDATE', p_begin_date);
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, 'PRICE', 'ENDDATE', p_end_date);
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, 'PRICELISTHEADER', 'PRICELIST', p_price_list_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, 'PRICELISTHEADER', 'CURRENCY', p_currency);
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, 'PRICELISTHEADER', 'BUYER', p_buyer_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, 'PRICELISTHEADER', 'SUPPLIER', p_supplier_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, 'PRICELISTHEADER', 'BEGINDATE', p_begin_date);
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, 'PRICELISTHEADER', 'ENDDATE', p_end_date);
select request_id, line_number, buyer_id, buyer_name,
contract_reference_id, contract_reference_num, supplier_id,
supplier_site, pricelist_id, currency_code, error_message
from icx_por_contract_references
where request_id = p_request_id;
FUNCTION can_update(descriptor_key IN VARCHAR2) return boolean
is
BEGIN
if (descriptor_key in
(
'UOM', 'PRICE',
'CURRENCY', 'INTERNAL_ITEM_NUM', 'CONTRACT_NUM',
'CONTRACT_LINE', 'CONTRACT_PRICE', 'CONTRACT_CURRENCY',
'CONTRACT_RATE_TYPE', 'CONTRACT_RATE_DATE', 'CONTRACT_RATE',
'FUNCTIONAL_PRICE', 'FUNCTIONAL_CURRENCY', 'SUPPLIER_SITE',
'BUYER', 'PRICELIST'))
then
return false;
END can_update;
select count(distinct supplier_id)
into l_count
from icx_por_contract_references
where supplier_id is not null
and request_id=p_request_id;
select count(distinct currency_code)
into l_count
from icx_por_contract_references
where currency_code is not null
and request_id=p_request_id;
select distinct vendor_name into p_supplier
from po_vendors poV, icx_por_contract_references icxC
where poV.vendor_id = icxC.supplier_id
and icxC.request_id=p_request_id
and icxC.supplier_id is not null;
select distinct currency_code into p_currency
from icx_por_contract_references icxC
where icxC.request_id=p_request_id
and icxC.currency_code is not null;
select count(0) into p_succ_count
from icx_por_contract_references
where error_message is null
and request_id = p_request_id;
select count(0) into p_failed_count
from icx_por_contract_references
where error_message is not null
and request_id = p_request_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, 'SYNC', 'ADMIN', 'BUYER', p_buyer);
INSERT INTO icx_por_failed_lines
(job_number, line_number, action, row_type, descriptor_key, descriptor_value)
VALUES
(p_request_id, p_line_number, 'SYNC', 'ADMIN', 'CONTRACT_NUM', p_contract_ref_num);
DELETE FROM icx_por_failed_lines where job_number = p_request_id;
INSERT INTO icx_por_failed_lines
(job_number, line_number, action, row_type, descriptor_key, descriptor_value)
--Bug#2729328
--VALUES (p_request_id, 1, 'SYNC', p_line_type, p_descriptor_key, p_descriptor_key);
delete from icx_por_failed_line_messages
where job_number = p_request_id;
SELECT language_code
FROM fnd_languages
WHERE installed_flag in ('B', 'I')
AND language_code <> gJobLanguage;
update icx_cat_ext_items_tlp set rt_category_id=gChangedNewCatIds(i),
text_cat_attribute1 = null, text_cat_attribute2 = null, text_cat_attribute3 = null,
text_cat_attribute4 = null, text_cat_attribute5 = null, text_cat_attribute6 = null,
text_cat_attribute7 = null, text_cat_attribute8 = null, text_cat_attribute9 = null,
text_cat_attribute10 = null, text_cat_attribute11 = null, text_cat_attribute12 = null,
text_cat_attribute13 = null, text_cat_attribute14 = null, text_cat_attribute15 = null,
text_cat_attribute16 = null, text_cat_attribute17 = null, text_cat_attribute18 = null,
text_cat_attribute19 = null, text_cat_attribute20 = null, text_cat_attribute21 = null,
text_cat_attribute22 = null, text_cat_attribute23 = null, text_cat_attribute24 = null,
text_cat_attribute25 = null, text_cat_attribute26 = null, text_cat_attribute27 = null,
text_cat_attribute28 = null, text_cat_attribute29 = null, text_cat_attribute30 = null,
text_cat_attribute31 = null, text_cat_attribute32 = null, text_cat_attribute33 = null,
text_cat_attribute34 = null, text_cat_attribute35 = null, text_cat_attribute36 = null,
text_cat_attribute37 = null, text_cat_attribute38 = null, text_cat_attribute39 = null,
text_cat_attribute40 = null, text_cat_attribute41 = null, text_cat_attribute42 = null,
text_cat_attribute43 = null, text_cat_attribute44 = null, text_cat_attribute45 = null,
text_cat_attribute46 = null, text_cat_attribute47 = null, text_cat_attribute48 = null,
text_cat_attribute49 = null, text_cat_attribute50 = null,
num_cat_attribute1 = null, num_cat_attribute2 = null, num_cat_attribute3 = null,
num_cat_attribute4 = null, num_cat_attribute5 = null, num_cat_attribute6 = null,
num_cat_attribute7 = null, num_cat_attribute8 = null, num_cat_attribute9 = null,
num_cat_attribute10 = null, num_cat_attribute11 = null, num_cat_attribute12 = null,
num_cat_attribute13 = null, num_cat_attribute14 = null, num_cat_attribute15 = null,
num_cat_attribute16 = null, num_cat_attribute17 = null, num_cat_attribute18 = null,
num_cat_attribute19 = null, num_cat_attribute20 = null, num_cat_attribute21 = null,
num_cat_attribute22 = null, num_cat_attribute23 = null, num_cat_attribute24 = null,
num_cat_attribute25 = null, num_cat_attribute26 = null, num_cat_attribute27 = null,
num_cat_attribute28 = null, num_cat_attribute29 = null, num_cat_attribute30 = null,
num_cat_attribute31 = null, num_cat_attribute32 = null, num_cat_attribute33 = null,
num_cat_attribute34 = null, num_cat_attribute35 = null, num_cat_attribute36 = null,
num_cat_attribute37 = null, num_cat_attribute38 = null, num_cat_attribute39 = null,
num_cat_attribute40 = null, num_cat_attribute41 = null, num_cat_attribute42 = null,
num_cat_attribute43 = null, num_cat_attribute44 = null, num_cat_attribute45 = null,
num_cat_attribute46 = null, num_cat_attribute47 = null, num_cat_attribute48 = null,
num_cat_attribute49 = null, num_cat_attribute50 = null,
tl_text_cat_attribute1 = null, tl_text_cat_attribute2 = null, tl_text_cat_attribute3 = null,
tl_text_cat_attribute4 = null, tl_text_cat_attribute5 = null, tl_text_cat_attribute6 = null,
tl_text_cat_attribute7 = null, tl_text_cat_attribute8 = null, tl_text_cat_attribute9 = null,
tl_text_cat_attribute10 = null, tl_text_cat_attribute11 = null, tl_text_cat_attribute12 = null,
tl_text_cat_attribute13 = null, tl_text_cat_attribute14 = null, tl_text_cat_attribute15 = null,
tl_text_cat_attribute16 = null, tl_text_cat_attribute17 = null, tl_text_cat_attribute18 = null,
tl_text_cat_attribute19 = null, tl_text_cat_attribute20 = null, tl_text_cat_attribute21 = null,
tl_text_cat_attribute22 = null, tl_text_cat_attribute23 = null, tl_text_cat_attribute24 = null,
tl_text_cat_attribute25 = null, tl_text_cat_attribute26 = null, tl_text_cat_attribute27 = null,
tl_text_cat_attribute28 = null, tl_text_cat_attribute29 = null, tl_text_cat_attribute30 = null,
tl_text_cat_attribute31 = null, tl_text_cat_attribute32 = null, tl_text_cat_attribute33 = null,
tl_text_cat_attribute34 = null, tl_text_cat_attribute35 = null, tl_text_cat_attribute36 = null,
tl_text_cat_attribute37 = null, tl_text_cat_attribute38 = null, tl_text_cat_attribute39 = null,
tl_text_cat_attribute40 = null, tl_text_cat_attribute41 = null, tl_text_cat_attribute42 = null,
tl_text_cat_attribute43 = null, tl_text_cat_attribute44 = null, tl_text_cat_attribute45 = null,
tl_text_cat_attribute46 = null, tl_text_cat_attribute47 = null, tl_text_cat_attribute48 = null,
tl_text_cat_attribute49 = null, tl_text_cat_attribute50 = null
where rt_category_id=gChangedOldCatIds(i)
and rt_item_id=gChangedCatItemIds(i)
and p_action=gChangedCatActions(i);
update icx_cat_items_tlp
set primary_category_name = (select category_name from icx_cat_categories_tl
where rt_category_id = gChangedNewCatIds(j)
and language = xLangArray(i))
where rt_item_id = gChangedCatItemIds(j) and language = xLangArray(i);