[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_ITEM_UPLOAD
Source
1 PACKAGE BODY ICX_POR_ITEM_UPLOAD AS
2 /* $Header: ICXIULDB.pls 115.72 2004/08/03 00:58:30 kaholee ship $ */
3
4 -- Interface table row
5 type tRootDescriptors is table of Varchar2(700) index by binary_integer;
6 type tLocalDescriptors is table of Varchar2(700) index by binary_integer;
7
8 /* Debug Handling */
9 g_error_message varchar2(4000) := '';
10
11 /* Used for saving price errors */
12 type tITPriceRecord IS RECORD (
13 line_number NUMBER,
14 system_action VARCHAR2(20),
15 supplier_name VARCHAR2(2000),
16 supplier_part_num VARCHAR2(2000),
17 supplier_part_auxid VARCHAR2(255),--Bug#2611529
18 supplier_site_code VARCHAR2(255),--Bug#2709997
19 unit_price VARCHAR2(60),
20 currency VARCHAR2(30),
21 unit_of_measure VARCHAR2(30),
22 buyer_name VARCHAR2(255),
23 price_list_name VARCHAR2(250),
24 error_message VARCHAR2(4000),
25 error_flag VARCHAR2(1)
26 );
27
28 /* Used for saving item errors */
29 type tITRowRecord is record (
30 line_number NUMBER ,
31 row_type VARCHAR2(20) ,
32 processed_flag VARCHAR2(1) ,
33 language VARCHAR2(4) ,
34 action VARCHAR2(20),
35 system_action VARCHAR2(20),
36 error_flag VARCHAR2(1),
37 error_message VARCHAR2(2000),
38 required_descriptors VARCHAR2(4000),
39 required_tl_descriptors VARCHAR2(4000),
40 has_cat_attribute_flag VARCHAR2(1),
41 rt_category_id NUMBER,
42 category_name VARCHAR2(250),
43 org_id NUMBER,
44 supplier_id NUMBER,
45 supplier VARCHAR2(700),
46 supplier_part_num VARCHAR2(700),
47 supplier_part_auxid VARCHAR2(255),
48 manufacturer VARCHAR2(700),
49 manufacturer_part_num VARCHAR2(700),
50 description VARCHAR2(2000),
51 comments VARCHAR2(700),
52 alias VARCHAR2(700),
53 picture VARCHAR2(700),
54 picture_url VARCHAR2(700),
55 thumbnail_image VARCHAR2(700),
56 attachment_url VARCHAR2(700),
57 supplier_url VARCHAR2(700),
58 manufacturer_url VARCHAR2(700),
59 long_description VARCHAR2(2000),
60 unspsc_code VARCHAR2(700),
61 availability VARCHAR2(700),
62 lead_time NUMBER,
63 item_type VARCHAR2(700),
64 contract_num VARCHAR2(700),
65 contract_id NUMBER,
66 roots tRootDescriptors,
67 locals tLocalDescriptors,
68 unit_price NUMBER,
69 currency VARCHAR2(15),
70 unit_of_measure VARCHAR2(30),
71 supplier_site_id NUMBER,
72 supplier_site_code VARCHAR2(100),
73 price_list_name VARCHAR2(250),
74 price_list_id NUMBER,
75 buyer_name VARCHAR2(255),
76 LAST_UPDATE_LOGIN NUMBER,
77 LAST_UPDATED_BY NUMBER,
78 LAST_UPDATE_DATE DATE,
79 CREATED_BY NUMBER,
80 CREATION_DATE DATE
81 );
82
83 /* Holds information about a category
84 - updateTLSQL is for updating/deleting translateable attributes of an existing items
85 - updateNonTLSQL is for updating/deleting Non-translateable attributes of an existing items
86 - translateSQL is for add an existing item in a new language
87 - for translate, the updateNonTLSQL takes care of updating the non-translatable attributes
88 and creating the correct number of rows in the languages the item already
89 exists in
90 */
91 TYPE CategoryInfo IS RECORD (
92 category_name icx_cat_categories_tl.category_name%TYPE,
93 descriptor_count PLS_INTEGER := 0,
94 searchable_desc_count PLS_INTEGER := 0,
95 updateTLSQL VARCHAR2(32767),
96 updateNonTLSQL VARCHAR2(32767),
97 translateSQL VARCHAR2(32767)
98 );
99
100 TYPE CategoryInfoTab IS TABLE OF CategoryInfo INDEX BY BINARY_INTEGER;
101 --Bug#2827814
102 TYPE ShortVarchar2Tab IS TABLE OF VARCHAR2(100) INDEX BY
103 BINARY_INTEGER;
104 TYPE KeyTab IS TABLE OF VARCHAR2(250) INDEX BY
105 BINARY_INTEGER;
106 gCategoryInfo CategoryInfoTab;
107
108 BATCH_SIZE PLS_INTEGER := 2500;
109 CACHE_SIZE PLS_INTEGER := 5000;
110
111 BLANK_DATE_STRING VARCHAR2(30) := '1799/12/31 00:00:00';
112 DEFAULT_DATE_FORMAT VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS';
113
114 /* Global tables to store information needed for bulk binding
115 Whenever a new global table is added, make sure it's also cleared in
116 the clear_tables procedure */
117 gRowids dbms_sql.urowid_table;
118 gSystemActions dbms_sql.varchar2_table;
119 gRowTypes ShortVarchar2Tab;
120 gItemIds dbms_sql.number_table;
121 gCurrentItemIds dbms_sql.number_table;
122 gCategoryIds dbms_sql.number_table;
123 gCategoryNames dbms_sql.varchar2_table;
124 gOldCategoryIds dbms_sql.number_table;
125 gDistinctCategoryIds dbms_sql.number_table;
126 gOrgIds dbms_sql.number_table;
127 gPricelistIds dbms_sql.number_table;
128 gSupplierSiteIds dbms_sql.number_table;
129 gExtractorUpdatedFlags dbms_sql.varchar2_table;
130 gActiveFlags dbms_sql.varchar2_table;
131 -- added by bluk, to be used in move_prices_no_validation, process_batch_addupdate_prices
132 gSupplierIds dbms_sql.number_table;
133 gPriceTypes dbms_sql.varchar2_table;
134 gUoms dbms_sql.varchar2_table;
135
136 -- Category_Change
137 gChangedCatItemIds dbms_sql.number_table;
138 gChangedOldCatIds dbms_sql.number_table;
139 gChangedNewCatIds dbms_sql.number_table;
140 gChangedCatActions dbms_sql.varchar2_table;
141
142 -- BUYER NORMALIZATION
143 gDistinctItemIds dbms_sql.number_table;
144 gDistinctBuyerIds dbms_sql.number_table;
145
146 --Bug#3396442
147 --gRootDescKeys is only used in save_failed_item to insert into failed lines
148 --This is not needed. save_failed_item has a new cursor to get the non-seeded
149 --root descriptors
150 --gRootDescKeys KeyTab;
151
152 gErrorRowids dbms_sql.urowid_table;
153 gErrorCatId NUMBER;
154 gErrorCatDescKeys KeyTab;
155
156 gTranslateItemsSQL VARCHAR2(32767);
157 gUpdateItemsTLSQL VARCHAR2(32767);
158 gUpdateItemsNonTLSQL VARCHAR2(32767);
159 gAddRootCtxSQL ICX_POR_CTX_SQL_PKG.SQLTab;
160 gUpdateRootCtxSQL ICX_POR_CTX_SQL_PKG.SQLTab;
161
162 gBaseLanguage fnd_languages.language_code%TYPE;
163 gJobLanguage fnd_languages.language_code%TYPE;
164 --Bug#2611529 gSupplierId NUMBER;
165 gJobNumber NUMBER;
166 gUserId NUMBER;
167 gUserLogin NUMBER;
168 --Bug#2611529
169 gCatalogName varchar2(255);
170
171 --Bug#3107596
172 gNegotiatedPrice varchar2(5);
173
174 -- Bug# 3366614 sosingha: global variable to make a conditional call to move_items with processed_flag 'D'
175 gDuplicatesExists BOOLEAN := false;
176
177 /* These are used for populating ICX_POR_CTX_TL
178 We can't store these in gCategoryInfo since PL/SQL would not
179 allow PL/SQL tables inside PL/SQL tables, so we can only store
180 info about the most recently used category */
181 gCurrentCatId NUMBER;
182 gAddCatCtxSQL ICX_POR_CTX_SQL_PKG.SQLTab;
183 gUpdateCatCtxSQL ICX_POR_CTX_SQL_PKG.SQLTab;
184
185 /* Debug Handling */
186 procedure Debug(p_message in varchar2) is
187 begin
188 g_error_message := substr(g_error_message || p_message, 4000);
189 end;
190
191 /* Clears the error table */
192 PROCEDURE clear_error_tables IS
193 BEGIN
194 gErrorRowids.DELETE;
195 END;
196
197 /* Clears the pl/sql tables before processing another batch */
198 PROCEDURE clear_tables IS
199 BEGIN
200 gRowids.DELETE;
201 gRowTypes.DELETE;
202 gSystemActions.DELETE;
203 gItemIds.DELETE;
204 gPriceTypes.DELETE;
205 gCurrentItemIds.DELETE;
206 gCategoryIds.DELETE;
207 gCategoryNames.DELETE;
208 gOldCategoryIds.DELETE;
209 gDistinctCategoryIds.DELETE;
210 -- Category_Change
211 gChangedCatItemIds.DELETE;
212 gChangedOldCatIds.DELETE;
213 gChangedNewCatIds.DELETE;
214 gChangedCatActions.DELETE;
215
216 gOrgIds.DELETE;
217 gPricelistIds.DELETE;
218 gSupplierSiteIds.DELETE;
219 gSupplierIds.DELETE;
220 gExtractorUpdatedFlags.DELETE;
221 gActiveFlags.DELETE;
222
223 -- BUYER NORMALIZATION
224 gDistinctItemIds.DELETE;
225 gDistinctBuyerIds.DELETE;
226 END clear_tables;
227
228 /* Clears all the global variables before processing a job */
229 PROCEDURE clear_all IS
230 BEGIN
231 gUpdateItemsNonTLSQL := null;
232 gUpdateItemsTLSQL := null;
233 gTranslateItemsSQL := null;
234 gCategoryInfo.DELETE;
235 gAddRootCtxSQL.DELETE;
236 gUpdateRootCtxSQL.DELETE;
237 gCurrentCatId := -1;
238 gAddCatCtxSQL.DELETE;
239 gUpdateCatCtxSQL.DELETE;
240 --Bug#3396442
241 --gRootDescKeys is only used in save_failed_item to insert into failed lines
242 --This is not needed. save_failed_item has a new cursor to get the non-seeded
243 --root descriptors
244 --gRootDescKeys.DELETE;
245 gErrorCatId := -1;
246 gErrorCatDescKeys.DELETE;
247 clear_tables;
248 clear_error_tables;
249 END clear_all;
250
251 /**
252 ** Proc : get_distinct
253 ** Desc : Gets the distinct values from a pl/sql number table
254 **/
255 PROCEDURE get_distinct(pNumbers IN dbms_sql.number_table,
256 pDistinctNumbers OUT NOCOPY dbms_sql.number_table) IS
257 i NUMBER;
258 j NUMBER;
259 v_current_num NUMBER;
260 v_found BOOLEAN;
261 v_temp_num dbms_sql.number_table;
262 v_empty_tab dbms_sql.number_table;
263 xErrLoc PLS_INTEGER := 100;
264 BEGIN
265 -- Initialize
266 pDistinctNumbers := v_empty_tab;
267 v_current_num := NULL;
268
269 IF (pNumbers.COUNT = 0) THEN
270 RETURN;
271 ELSE
272
273 xErrLoc := 200;
274 IF (pNumbers(1) IS NOT NULL) THEN
275 v_current_num := pNumbers(1);
276 v_temp_num(v_current_num) := 1;
277 END IF;
278 END IF;
279 xErrLoc := 300;
280
281 FOR i IN pNumbers.FIRST..pNumbers.LAST LOOP
282
283 -- No need to check if same as last entry or if null
284 IF (pNumbers(i) IS NOT NULL AND
285 (pNumbers(i) <> v_current_num OR v_current_num IS NULL)) THEN
286 v_current_num := pNumbers(i);
287
288 -- Value does not appear in the distinct array yet
289 IF NOT v_temp_num.EXISTS(v_current_num) THEN
290 v_temp_num(v_current_num) := 1;
291 END IF;
292
293 END IF;
294 xErrLoc := 300+i;
295
296 END LOOP;
297 xErrLoc := 10000;
298
299 IF (v_temp_num.COUNT = 0) THEN
300 -- All entries in the incoming table are null
301 RETURN;
302 END IF;
303 xErrLoc := 10010;
304
305 v_current_num := v_temp_num.FIRST;
306 pDistinctNumbers(1) := v_current_num;
307 j := v_temp_num.FIRST;
308
309 xErrLoc := 10020;
310 FOR i IN 2..v_temp_num.COUNT LOOP
311 j := v_temp_num.NEXT(j);
312 pDistinctNumbers(i) := j;
313 END LOOP;
314 xErrLoc := 10030;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 ROLLBACK;
319
320 RAISE_APPLICATION_ERROR(-20000,
321 'Exception at ICX_POR_ITEM_UPLOAD.get_distinct('
322 || xErrLoc || '): ' || SQLERRM);
323 END get_distinct;
324
325 /* Constructs the SQL statements used to populate ICX_POR_CTX_TL
326 for a given category */
327 PROCEDURE build_category_ctx_sql(p_category_id IN NUMBER) IS
328 vSQLTab ICX_POR_CTX_SQL_PKG.SQLTab;
329 xErrLoc PLS_INTEGER := 100;
330 BEGIN
331
332 IF (p_category_id <> gCurrentCatId) THEN
333 gCurrentCatId := p_category_id;
334
335 -- For Add CTX SQL we want to use rowid where clause
336 xErrLoc := 200;
337 ICX_POR_CTX_SQL_PKG.build_ctx_sql(p_category_id,
338 ICX_POR_CTX_SQL_PKG.ROWID_WHERE_CLAUSE, null,
339 ICX_POR_CTX_SQL_PKG.DEFAULT_MAX_LENGTH,
340 gAddCatCtxSQL, vSQLTab);
341
342 -- For Update CTX SQL we need to use itemid where clause
343 xErrLoc := 300;
344 ICX_POR_CTX_SQL_PKG.build_ctx_sql(p_category_id,
345 ICX_POR_CTX_SQL_PKG.ITEMID_WHERE_CLAUSE, null,
346 ICX_POR_CTX_SQL_PKG.DEFAULT_MAX_LENGTH, vSQLTab, gUpdateCatCtxSQL);
347
348 END IF;
349 EXCEPTION
350 WHEN OTHERS THEN
351 ROLLBACK;
352
353 RAISE_APPLICATION_ERROR(-20000,
354 'Exception at ICX_POR_ITEM_UPLOAD.build_category_ctx_sql('
355 || xErrLoc || '): ' || SQLERRM);
356 END build_category_ctx_sql;
357
358 /* Constructs the SQL statements used to populate ICX_POR_C######_TL for
359 a given category */
360 PROCEDURE build_category_sql(p_category_id IN NUMBER,
361 p_category_info IN OUT NOCOPY CategoryInfo,
362 p_table_name IN VARCHAR2) IS
363
364 vUpdateItemsNonTLSQL1 VARCHAR2(32767) := null;
365 vUpdateItemsNonTLSQL2 VARCHAR2(32767) := null;
366 vUpdateItemsTLSQL1 VARCHAR2(32767) := null;
367 vUpdateItemsTLSQL2 VARCHAR2(32767) := null;
368
369 vTranslateItemsSQL1 VARCHAR2(32767) := null;
370 vTranslateItemsSQL2 VARCHAR2(32767) := null;
371 vCategoryInfo CategoryInfo;
372 i pls_integer;
373 CURSOR local_descriptors_cr(v_category_id NUMBER) IS
374 select d.rt_descriptor_id,
375 d.type,
376 d.key,
377 d.section_tag,
378 d.stored_in_column,
379 d.stored_in_table,
380 d.searchable
381 from icx_cat_descriptors_tl d
382 where d.rt_category_id = v_category_id
383 and d.language = gJobLanguage
384 order by d.rt_descriptor_id;
385 vColName VARCHAR2(30);
386 xErrLoc PLS_INTEGER := 100;
387 vDescCount PLS_INTEGER := 0;
388 vAllDescCount PLS_INTEGER := 0;
389 vTLDescCount PLS_INTEGER := 0;
390 vTableName VARCHAR2(30);
391 vDescInfo ICX_POR_CTX_SQL_PKG.DescriptorInfo;
392 vDescInfoTab ICX_POR_CTX_SQL_PKG.DescriptorInfoTab;
393 vSearchableDescCount PLS_INTEGER := 0;
394 vSQLTab ICX_POR_CTX_SQL_PKG.SQLTab;
395 BEGIN
396
397 vTableName := 'ICX_CAT_EXT_ITEMS_TLP';
398
399 -- Category_Change
400 vUpdateItemsNonTLSQL1 := 'update ' || vTableName ||
401 ' set (last_update_login, last_updated_by, last_update_date ';
402 vUpdateItemsTLSQL1 := 'update ' || vTableName ||
403 ' set (request_id, last_update_login, last_updated_by, last_update_date ';
404
405 vTranslateItemsSQL1 := 'insert into ' || vTableName ||
406 ' (rt_item_id, language, org_id, '||
407 ' rt_category_id, primary_flag, '||
408 ' created_by, creation_date, ' ||
409 ' last_updated_by, last_update_date, last_update_login ';
410
411 FOR local_desc IN local_descriptors_cr(p_category_id) LOOP
412 vDescInfo := null;
413 vAllDescCount := vAllDescCount + 1;
414
415 vColName := local_desc.stored_in_column;
416
417 xErrLoc := 200;
418
419 IF (local_desc.searchable = 1) THEN
420 p_category_info.searchable_desc_count :=
421 p_category_info.searchable_desc_count + 1;
422 END IF;
423
424 -- The decode...#DEL part is used to set the attribute value to null
425 -- if the user specifies #DEL in their file
426 IF (local_desc.type = 2) THEN
427 -- This is a translated text attribute. The decode on the language
428 -- makes sure the attribute value is updated only in the job language
429 vTLDescCount := vTLDescCount + 1;
430 vUpdateItemsTLSQL1 := vUpdateItemsTLSQL1 || ', ' || vColName;
431 vUpdateItemsTLSQL2 := vUpdateItemsTLSQL2 ||
432 ', decode(i.language, :p_language, decode(it.' ||
433 vColName || ',''#DEL'',null,null,i.' ||
434 vColName || ',it.' || vColName ||
435 '),i.' || vColName || ') ';
436 vTranslateItemsSQL1 := vTranslateItemsSQL1 || ', ' || vColName;
437 vTranslateItemsSQL2 := vTranslateItemsSQL2 || ', decode(it.' ||
438 vColName || ', ''#DEL'', null, null, i.' || vColName ||
439 ', it.' || vColName || ') ';
440 ELSIF (local_desc.type IN (1,5)) THEN
441 vDescCount := vDescCount + 1;
442 vUpdateItemsNonTLSQL1 := vUpdateItemsNonTLSQL1 || ', ' || vColName;
443 vUpdateItemsNonTLSQL2 := vUpdateItemsNonTLSQL2 || ', decode(it.' ||
444 vColName || ',''#DEL'',to_number(null),null,i.' ||
445 vColName || ',to_number(it.' || vColName || ')) ';
446 vTranslateItemsSQL1 := vTranslateItemsSQL1 || ', ' || vColName;
447 vTranslateItemsSQL2 := vTranslateItemsSQL2 || ', decode(it.' ||
448 vColName || ',''#DEL'',to_number(null),null,i.' ||
449 vColName || ',to_number(it.' || vColName || ')) ';
450 ELSIF (local_desc.type in (0,4)) THEN
451 vDescCount := vDescCount + 1;
452 vUpdateItemsNonTLSQL1 := vUpdateItemsNonTLSQL1 || ', ' || vColName;
453 vUpdateItemsNonTLSQL2 := vUpdateItemsNonTLSQL2 || ', decode(it.' ||
454 vColName || ', ''#DEL'', null, null, i.' || vColName ||
455 ', it.' || vColName || ') ';
456 vTranslateItemsSQL1 := vTranslateItemsSQL1 || ', ' || vColName;
457 vTranslateItemsSQL2 := vTranslateItemsSQL2 || ', decode(it.' ||
458 vColName || ', ''#DEL'', null, null, i.' || vColName ||
459 ', it.' || vColName || ') ';
460 END IF;
461
462 END LOOP;
463
464 IF (vAllDescCount > 0) THEN
465 xErrLoc := 300;
466
467 p_category_info.descriptor_count := vAllDescCount;
468 xErrLoc := 301;
469 -- Assumption is that all the non tl descriptors will
470 -- hold the same value for an item across language, so pick
471 -- the first matching row(rownum=1);
472 p_category_info.updateNonTLSQL := vUpdateItemsNonTLSQL1 ||
473 ') = (SELECT :p_user_login, :p_user_id, sysdate' ||
474 vUpdateItemsNonTLSQL2 ||
475 ' FROM ' || p_table_name || ' it, icx_cat_ext_items_tlp i ' ||
476 ' WHERE i.rt_item_id = :old_item_id' ||
477 ' AND it.rowid = :p_rowid AND ' || to_char(p_category_id) ||
478 ' = :p_category_id AND :update_action = :p_action and rownum=1)' ||
479 ' WHERE rt_item_id = :old_item_id' ||
480 ' AND rt_category_id = '|| to_char(p_category_id); --Category_Change
481 xErrLoc := 302;
482 -- Category_Change
483 p_category_info.updateTLSQL := vUpdateItemsTLSQL1 ||
484 ') = (SELECT :p_request_id, :p_user_login, :p_user_id, sysdate' ||
485 vUpdateItemsTLSQL2 ||
486 ' FROM ' || p_table_name || ' it, icx_cat_ext_items_tlp i ' ||
487 ' WHERE i.rt_item_id = :old_item_id' ||
488 ' AND it.rowid = :p_rowid AND ' || to_char(p_category_id) ||
489 ' = :p_category_id AND :update_action = :p_action' ||
490 ' AND it.language = i.language)' ||
491 ' WHERE rt_item_id = :old_item_id' ||
492 ' AND rt_category_id = '|| to_char(p_category_id) || --Category_Change
493 ' AND language = :p_language';
494 xErrLoc := 303;
495 ELSE
496 xErrLoc := 306;
497 p_category_info.descriptor_count := 0;
498 p_category_info.updateTLSQL := null;
499 p_category_info.updateNonTLSQL := null;
500 --Bug#3657792
501 --Insert into icx_cat_ext_items_tlp should be done even if there are no local descriptors
502 --vTranslateItemsSQL1 and vTranslateItemsSQL2 will be null, if there are no local descriptors,
503 --so, insert into icx_cat_ext_items_tlp will only insert the required columns
504 --like rt_item_id, language etc
505 --p_category_info.translateSQL := null;
506 END IF;
507
508 --Bug#3657792
509 --Insert into icx_cat_ext_items_tlp should be done even if there are no local descriptors
510 --vTranslateItemsSQL1 and vTranslateItemsSQL2 will be null, if there are no local descriptors,
511 --so, insert into icx_cat_ext_items_tlp will only insert the required columns
512 --like rt_item_id, language etc
513 xErrLoc := 305;
514 p_category_info.translateSQL := vTranslateItemsSQL1 || ') SELECT ' ||
515 'i.rt_item_id, it.language, it.org_id,'||
516 'it.rt_category_id, ''Y'',' ||
517 ':p_user_id, sysdate, ' ||
518 ':p_user_id, sysdate, :p_user_login ' ||
519 vTranslateItemsSQL2 || ' FROM icx_cat_ext_items_tlp i, ' || p_table_name ||
520 ' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND ' ||
521 to_char(p_category_id) || ' = :p_category_id AND '||
522 ' :update_action = :p_action and it.language<>i.language ' ||
523 ' AND rownum = 1 ';
524
525 xErrLoc := 304;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 ROLLBACK;
530
531 RAISE_APPLICATION_ERROR(-20000,
532 'Exception at ICX_POR_ITEM_UPLOAD.build_category_sql('
533 || xErrLoc || '): ' || SQLERRM);
534 END build_category_sql;
535
536 /* Loads information about the categories in the current batch */
537 PROCEDURE load_distinct_categories IS
538 v_distinct_catids dbms_sql.number_table;
539 v_category_info CategoryInfo;
540 i PLS_INTEGER;
541 CURSOR get_category_name(p_category_id NUMBER) IS
542 SELECT category_name FROM icx_cat_categories_tl
543 WHERE rt_category_id = p_category_id
544 AND language = gJobLanguage;
545 v_current_catid NUMBER;
546 xErrLoc PLS_INTEGER := 100;
547 BEGIN
548 IF (gCategoryInfo.COUNT >= CACHE_SIZE) THEN
549 -- Flush the cache
550 gCategoryInfo.DELETE;
551 END IF;
552
553 get_distinct(gCategoryIds, gDistinctCategoryIds);
554
555 xErrLoc := 200;
556
557 FOR i IN 1..gDistinctCategoryIds.COUNT LOOP
558 v_current_catid := gDistinctCategoryIds(i);
559
560 IF (NOT gCategoryInfo.EXISTS(v_current_catid)) THEN
561 xErrLoc := 300;
562
563 OPEN get_category_name(v_current_catid);
564 FETCH get_category_name INTO v_category_info.category_name;
565
566 IF (NOT get_category_name%NOTFOUND) THEN
567 xErrLoc := 400;
568
569 build_category_sql(v_current_catid, v_category_info,
570 'ICX_CAT_ITEMS_GT');
571 gCategoryInfo(v_current_catid) := v_category_info;
572 END IF;
573
574 CLOSE get_category_name;
575 END IF;
576
577 END LOOP;
578
579 EXCEPTION
580 WHEN OTHERS THEN
581 ROLLBACK;
582
583 RAISE_APPLICATION_ERROR(-20000,
584 'Exception at ICX_POR_ITEM_UPLOAD.load_distinct_categories('
585 || xErrLoc || '): ' || SQLERRM);
586 END load_distinct_categories;
587
588 /* Constructs the SQL used to populate ICX_POR_ITEMS and ICX_POR_ITEMS_TL */
589 PROCEDURE build_root_sql(pTableName IN VARCHAR2) IS
590 vUpdateItemsTLSQL1 VARCHAR2(32767) := null;
591 vUpdateItemsTLSQL2 VARCHAR2(32767) := null;
592 vUpdateItemsNonTLSQL1 VARCHAR2(32767) := null;
593 vUpdateItemsNonTLSQL2 VARCHAR2(32767) := null;
594 vTranslateItemsSQL1 VARCHAR2(32767) := null;
595 vTranslateItemsSQL2 VARCHAR2(32767) := null;
596 vDescInfo ICX_POR_CTX_SQL_PKG.DescriptorInfo;
597 vDescInfoTab ICX_POR_CTX_SQL_PKG.DescriptorInfoTab;
598 vSQLTab ICX_POR_CTX_SQL_PKG.SQLTab;
599 i pls_integer;
600 CURSOR non_price_root_descriptors_cr(p_category_id NUMBER) IS
601 select rt_descriptor_id,
602 type,
603 key,
604 section_tag,
605 stored_in_column,
606 stored_in_table,
607 searchable
608 from icx_cat_descriptors_tl
609 where rt_category_id = p_category_id
610 and language = gJobLanguage
611 and stored_in_table = 'ICX_CAT_ITEMS_TLP'
612 order by rt_descriptor_id;
613 vColName VARCHAR2(30);
614 xErrLoc PLS_INTEGER := 100;
615 vRootDescCount PLS_INTEGER := 0;
616 vRootAllDescCount PLS_INTEGER := 0;
617 vRootTLDescCount PLS_INTEGER := 0;
618 vSearchableDescCount PLS_INTEGER := 0;
619 BEGIN
620
621 -- No need to put any value in the ctx_<lang> column, so it's not
622 -- included
623 vUpdateItemsTLSQL1 := 'update icx_cat_items_tlp set ( ' ||
624 'request_id, last_updated_by, last_update_date, last_update_login, primary_category_name ';
625 --Bug#2729038
626 vUpdateItemsNonTLSQL1 := 'update icx_cat_items_tlp set ( ctx_desc, ' ||
627 'primary_category_id, last_updated_by, last_update_date, last_update_login ';
628 vTranslateItemsSQL1 := 'insert into icx_cat_items_tlp ( ctx_desc, ' ||
629 ' rt_item_id, language, org_id, supplier_id, '||
630 ' item_source_type, search_type, primary_category_id, primary_category_name, '||
631 ' created_by, creation_date, ' ||
632 ' last_updated_by, last_update_date, last_update_login ';
633
634 xErrLoc := 200;
635
636 FOR root_desc IN non_price_root_descriptors_cr(0) LOOP
637 vDescInfo := null;
638 vRootAllDescCount := vRootAllDescCount + 1;
639 vColName := root_desc.stored_in_column;
640
641 --Bug#3396442
642 --gRootDescKeys is only used in save_failed_item to insert into failed lines
643 --This is not needed. save_failed_item has a new cursor to get the non-seeded
644 --root descriptors
645 --gRootDescKeys(vRootAllDescCount) := root_desc.key;
646
647 IF (root_desc.searchable = 1 OR root_desc.key = 'SELLABLE_ITEM') THEN
648 vDescInfo.descriptor_id := root_desc.rt_descriptor_id;
649 vDescInfo.descriptor_key := root_desc.key;
650 vDescInfo.descriptor_index := vRootAllDescCount;
651 vDescInfo.descriptor_type := root_desc.type;
652 vDescInfo.section_tag := root_desc.section_tag;
653 -- OEX_IP_PORTING
654 vDescInfo.stored_in_column := root_desc.stored_in_column;
655 vDescInfo.stored_in_table := root_desc.stored_in_table;
656
657 IF (root_desc.type IN (0,2)) THEN
658
659 IF (root_desc.key = 'DESCRIPTION') THEN
660 vDescInfo.descriptor_length := 2000;
661 ELSIF (root_desc.key = 'SELLABLE_ITEM') THEN
662 vDescInfo.descriptor_length := 20;
663 ELSE
664 vDescInfo.descriptor_length := 700;
665 END IF;
666
667 ELSE
668 vDescInfo.descriptor_length := 100;
669 END IF;
670
671 vSearchableDescCount := vSearchableDescCount + 1;
672 vDescInfoTab(vSearchableDescCount) := vDescInfo;
673 END IF;
674
675 -- For all root descriptors that are updateable and
676 -- are stored in icx_cat_items_tlp, formulate the update sql...
677 -- Some of the descriptors are not updateable. check can_update() function
678 IF (can_update(root_desc.key) AND (root_desc.stored_in_table = 'ICX_CAT_ITEMS_TLP')) then
679
680 IF (root_desc.type = 2) THEN
681 vRootTLDescCount := vRootTLDescCount + 1;
682 vUpdateItemsTLSQL1 := vUpdateItemsTLSQL1 || ', ' || vColName;
683 vUpdateItemsTLSQL2 := vUpdateItemsTLSQL2 ||
684 ', decode(i.language, :p_language, decode(it.'||vColName ||
685 ',''#DEL'',null,null,i.' || vColName ||
686 ',it.' || vColName || '),i.' || vColName || ')';
687
688 vTranslateItemsSQL1 := vTranslateItemsSQL1 || ', ' || vColName;
689 vTranslateItemsSQL2 := vTranslateItemsSQL2 ||
690 ', decode(it.'||vColName ||
691 ',''#DEL'',null, it.' || vColName || ')';
692 ELSE
693 vUpdateItemsNonTLSQL1 := vUpdateItemsNonTLSQL1 || ', ' || vColName;
694 vUpdateItemsNonTLSQL2 := vUpdateItemsNonTLSQL2 ||
695 ', decode(it.'||vColName ||
696 ',''#DEL'',null,null,i.' || vColName ||
697 ',it.' || vColName || ')';
698
699 vTranslateItemsSQL1 := vTranslateItemsSQL1 || ', ' || vColName;
700 vTranslateItemsSQL2 := vTranslateItemsSQL2 ||
701 ', decode(it.'||vColName ||
702 ',''#DEL'',null,null,i.' || vColName ||
703 ',it.' || vColName || ')';
704 END IF; -- if (root_desc.type = 2)
705
706 END IF; -- if(root_desc.stored_in_table = 'ICX_CAT_ITEMS_TLP')
707
708 END LOOP; -- FOR root_desc IN ....
709
710 xErrLoc := 300;
711
712 -- Update only non-translated descriptors
713 -- Assumption is that all the non tl descriptors will hold the same value for
714 -- an item across language, so pick the first matching row(rownum=1);
715 -- update the ctx_desc to '1'
716 --Bug#2729038
717 gUpdateItemsNonTLSQL := vUpdateItemsNonTLSQL1 || ') = (SELECT ' ||
718 '''1'''||
719 ', it.rt_category_id, :p_user_id, sysdate, :p_user_login ' ||
720 vUpdateItemsNonTLSQL2 || ' FROM icx_cat_items_tlp i, ' || pTableName ||
721 ' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND' ||
722 ' :update_action = :p_action and rownum=1) WHERE rt_item_id = :old_item_id';
723
724 -- Update only translated descriptors
725 gUpdateItemsTLSQL := vUpdateItemsTLSQL1 || ') = (SELECT ' ||
726 ':p_request_id, :p_user_id, sysdate, :p_user_login, it.category_name ' ||
727 vUpdateItemsTLSQL2 || ' FROM icx_cat_items_tlp i, ' || pTableName ||
728 ' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND' ||
729 ' :update_action = :p_action and it.language=i.language)' ||
730 ' WHERE language = :p_language' ||
731 ' AND rt_item_id = :old_item_id';
732
733 -- Update only translated descriptors
734 gTranslateItemsSQL := vTranslateItemsSQL1 || ') SELECT ' ||
735 '''1'''||
736 ', i.rt_item_id, it.language, it.org_id, it.supplier_id,'||
737 '''SUPPLIER'', ''SUPPLIER'', '||
738 'it.rt_category_id, it.category_name,' ||
739 'i.created_by, i.creation_date, ' ||
740 ':p_user_id, sysdate, :p_user_login ' ||
741 vTranslateItemsSQL2 || ' FROM icx_cat_items_tlp i, ' || pTableName ||
742 ' it WHERE i.rt_item_id = :old_item_id AND it.rowid = :p_rowid AND' ||
743 ' :update_action = :p_action and it.language<>i.language ' ||
744 ' AND rownum = 1 ' ;
745
746 -- For Add CTX SQL we want to use rowid where clause
747 xErrLoc := 400;
748 ICX_POR_CTX_SQL_PKG.build_ctx_sql(0, vDescInfoTab,
749 ICX_POR_CTX_SQL_PKG.ROWID_WHERE_CLAUSE, null,
750 ICX_POR_CTX_SQL_PKG.DEFAULT_MAX_LENGTH, gAddRootCtxSQL,
751 vSQLTab);
752
753 -- For Update CTX SQL we need to use itemid where clause
754 xErrLoc := 500;
755 ICX_POR_CTX_SQL_PKG.build_ctx_sql(0, vDescInfoTab,
756 ICX_POR_CTX_SQL_PKG.ITEMID_WHERE_CLAUSE, null,
757 ICX_POR_CTX_SQL_PKG.DEFAULT_MAX_LENGTH, vSQLTab,
758 gUpdateRootCtxSQL);
759
760 EXCEPTION
761 WHEN OTHERS THEN
762 ROLLBACK;
763
764 RAISE_APPLICATION_ERROR(-20000,
765 'Exception at ICX_POR_ITEM_UPLOAD.build_root_sql('
766 || xErrLoc || '): ' || SQLERRM);
767 END build_root_sql;
768
769 /* Processes the new prices in the current batch */
770 PROCEDURE process_batch_add_prices(p_batch_type IN VARCHAR2) IS
771 i PLS_INTEGER;
772 v_action VARCHAR2(10) := 'ADD';
773 xErrLoc PLS_INTEGER := 100;
774 v_sequence PLS_INTEGER := 10001;
775 v_buyer_ids dbms_sql.number_table;
776 v_item_ids dbms_sql.number_table;
777 v_count PLS_INTEGER := 0;
778 BEGIN
779 -- This is used only for ITEM_PRICES. New prices or updates in the PRICE
780 -- section is handled by process_batch_addupdate_prices
781
782 IF (p_batch_type = 'PRICE') THEN
783 RETURN;
784 ELSE
785
786 xErrLoc := 150;
787 FORALL i IN 1..gRowids.COUNT
788 INSERT into icx_cat_item_prices(rt_item_id, price_type, supplier_site_id, org_id,
789 active_flag, search_type, unit_price, currency, unit_of_measure,
790 supplier_site_code, price_list_id, request_id, created_by, creation_date,
791 last_updated_by, last_update_login, last_update_date, object_version_number, contract_num, contract_id,
792 negotiated_by_preparer_flag) --Bug#3107596
793 SELECT gItemIds(i), gPriceTypes(i), it.supplier_site_id, it.org_id,
794 'Y', 'SUPPLIER',
795 it.unit_price, it.currency, it.unit_of_measure,
796 it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
797 gUserLogin, sysdate , 1,
798 it.contract_num, it.contract_id, -- OEX_IP_PORTING
799 gNegotiatedPrice --Bug#3107596
800 FROM ICX_CAT_ITEMS_GT it
801 WHERE rowid = gRowIds(i)
802 AND gRowTypes(i) = p_batch_type
803 AND gSystemActions(i) = v_action;
804
805 -- BUYER NORMALIZATION
806 -- This is a new item, so no need to check existing entries
807 xErrLoc := 300;
808 -- We want to only process the rows that are applicable
809 FOR i IN 1..gRowids.COUNT LOOP
810
811 IF (gRowTypes(i) = p_batch_type AND gSystemActions(i) = v_action AND
812 gOrgIds(i) IS NOT NULL) THEN
813 v_count := v_count + 1;
814 v_item_ids(v_count) := gItemIds(i);
815 v_buyer_ids(v_count) := gOrgIds(i);
816 END IF;
817
818 END LOOP;
819
820 xErrLoc := 400;
821
822 FORALL i IN 1..v_count
823 INSERT INTO icx_cat_items_ctx_tlp
824 (rt_item_id, language, sequence, ctx_desc, org_id)
825 VALUES
826 (v_item_ids(i), gJobLanguage, v_sequence,
827 to_char(v_buyer_ids(i)), v_buyer_ids(i));
828
829 xErrLoc := 500;
830 END IF;
831
832 EXCEPTION
833 WHEN OTHERS THEN
834 ROLLBACK;
835
836 RAISE_APPLICATION_ERROR(-20000,
837 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_add_prices('
838 || xErrLoc || '): ' || SQLERRM);
839 END process_batch_add_prices;
840
841 /* Processes the price updates in the current batch */
842 -- icx_por_ext_item.getBulkLoadActiveFlag() returns,
843 -- 'A' if the bulkloaded price is active and there is an ASL
844 -- 'Y' if the bulkloaded price is active and there is No ASL
845 -- 'N' if the bulkloaded price is inactive
846 PROCEDURE process_batch_addupdate_prices(p_batch_type IN VARCHAR2,
847 p_action IN VARCHAR2 DEFAULT 'UPDATE',
848 p_num_failed_lines OUT NOCOPY NUMBER) IS
849 i PLS_INTEGER;
850 v_price_updated dbms_sql.number_table;
851 xErrLoc PLS_INTEGER := 100;
852 v_sequence PLS_INTEGER := 10001;
853 numErrors NUMBER := 0;
854 BEGIN
855 p_num_failed_lines := 0; -- initialize
856
857 -- Get the number of errors already logged.
858 -- We need to add the error rows from this procedure into gErrorRowids
859 -- from the correct location without erasing the
860 -- previous errors(like say duplicate error)
861 numErrors := gErrorRowids.COUNT;
862
863 -- When updating existing items with prices specified, there are
864 -- two possibilities: updating existing price or adding new price
865 -- This SQL takes care of both cases
866
867 -- This SQL also works for both new and exsiting prices if we are
868 -- processing the price section
869
870 -- First move over the existing price if any to the price history table
871
872 --Bug#2719434: price type of "BULKLOAD" and "CONTRACT" are of both bulkloader
873 -- price type. Verified that there cannot be any extracted price
874 -- of type CONTRACT
875 FORALL i in 1..gRowIds.COUNT
876 INSERT into icx_cat_price_history(rt_item_id, status, price_type, asl_id, supplier_site_id,
877 contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
878 active_flag, search_type, unit_price, currency, unit_of_measure, functional_price,
879 supplier_site_code, contract_num, contract_line_num, price_list_id, archived_date,
880 last_update_login, last_updated_by, last_update_date, created_by, creation_date,
881 request_id, program_application_id, program_id, program_update_date, object_version_number)
882 SELECT rt_item_id, 'OUTDATED', price_type, asl_id, supplier_site_id,
883 contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
884 active_flag, search_type, unit_price, currency, unit_of_measure, functional_price,
885 supplier_site_code, contract_num, contract_line_num, price_list_id, sysdate,
886 last_update_login, last_updated_by, last_update_date, created_by, creation_date,
887 request_id, program_application_id, program_id, program_update_date,object_version_number
888 FROM icx_cat_item_prices
889 WHERE rt_item_id = gItemIds(i)
890 AND price_list_id = gPricelistIds(i)
891 AND org_id = gOrgIds(i)
892 AND supplier_site_id = gSupplierSiteIds(i)
893 AND gRowTypes(i) = p_batch_type
894 AND price_type in ('BULKLOAD', 'CONTRACT') --Bug#2719434
895 AND gSystemActions(i) = p_action;
896
897 xErrLoc := 200;
898
899 -- Figure out which rows need to be update and which insert
900
901 FOR i in 1..gRowIds.COUNT LOOP
902 v_price_updated(i) := SQL%BULK_ROWCOUNT(i);
903
904 if (gUoms(i) is null AND v_price_updated(i) = 0) then
905 -- For add operation you need the UOM,
906 -- For add, if UOM is not specified then reject the line
907 reject_line(gRowids(i), 'PRICE', '.UOM:ICX_POR_CAT_FIELD_REQUIRED');
908
909 p_num_failed_lines := p_num_failed_lines + 1;
910 gErrorRowids(numErrors+p_num_failed_lines) := gRowids(i);
911
912 -- Also set the v_price_updated(i) to -1 so that the insert operation
913 -- for rejected prices does not happen
914 v_price_updated(i) := -1;
915 end if;
916
917 END LOOP;
918
919 xErrLoc := 300;
920
921 IF (p_batch_type = 'PRICE') THEN
922 -- insert the new prices
923 FORALL i in 1..gRowIds.COUNT
924 INSERT into icx_cat_item_prices(rt_item_id, price_type, supplier_site_id, org_id,
925 active_flag, search_type, unit_price, currency, unit_of_measure,
926 supplier_site_code, price_list_id, request_id, created_by, creation_date,
927 last_updated_by, last_update_login, last_update_date, contract_num, contract_id, object_version_number,
928 negotiated_by_preparer_flag) --Bug#3107596
929 SELECT gItemIds(i), gPriceTypes(i), it.supplier_site_id, it.org_id,
930 gActiveFlags(i),
931 'SUPPLIER', it.unit_price, it.currency, it.unit_of_measure,
932 it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
933 gUserLogin, sysdate, it.contract_num, it.contract_id , 1,
934 gNegotiatedPrice --Bug#3107596
935 FROM ICX_CAT_PRICES_GT it
936 WHERE rowid = gRowIds(i)
937 AND gRowTypes(i) = p_batch_type
938 AND gSystemActions(i) = p_action
939 AND v_price_updated(i) = 0;
940
941 xErrLoc := 400;
942
943 -- update the existing prices
944 FORALL i in 1..gRowIds.COUNT
945 UPDATE icx_cat_item_prices ip
946 SET (ip.unit_price, ip.currency,
947 ip.unit_of_measure,
948 ip.supplier_site_code, ip.request_id, ip.last_updated_by,
949 ip.last_update_login, ip.last_update_date, ip.contract_num,
950 ip.contract_id, ip.object_version_number,
951 ip.price_type, --Bug#3503280
952 negotiated_by_preparer_flag) --Bug#3107596
953 = (SELECT it.unit_price, it.currency,
954 nvl(it.unit_of_measure, ip.unit_of_measure),
955 it.supplier_site_code, gJobNumber, gUserId, gUserLogin, sysdate,
956 it.contract_num, it.contract_id, 1,
957 gPriceTypes(i), --Bug#3503280
958 gNegotiatedPrice --Bug#3107596
959 FROM ICX_CAT_PRICES_GT it
960 WHERE rowid = gRowIds(i)
961 AND gRowTypes(i) = p_batch_type
962 AND gSystemActions(i) = p_action
963 AND v_price_updated(i) = 1)
964 WHERE ip.rt_item_id = gItemIds(i)
965 AND ip.price_list_id = gPricelistIds(i)
966 AND ip.org_id = gOrgIds(i)
967 AND ip.supplier_site_id = gSupplierSiteIds(i)
968 AND gRowTypes(i) = p_batch_type
969 AND v_price_updated(i) > 0
970 AND ip.price_type in ( 'BULKLOAD', 'CONTRACT') --Bug#2719434
971 AND gSystemActions(i) = p_action;
972 ELSE
973
974 -- insert the new prices
975 FORALL i in 1..gRowIds.COUNT
976 INSERT into icx_cat_item_prices(rt_item_id, price_type, supplier_site_id, org_id,
977 active_flag, search_type, unit_price, currency, unit_of_measure,
978 supplier_site_code, price_list_id, request_id, created_by, creation_date,
979 last_updated_by, last_update_login, last_update_date, contract_num, contract_id, object_version_number,
980 negotiated_by_preparer_flag) --Bug#3107596
981 SELECT gItemIds(i), gPriceTypes(i), it.supplier_site_id, it.org_id, gActiveFlags(i), 'SUPPLIER',
982 it.unit_price, it.currency, it.unit_of_measure,
983 it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
984 gUserLogin, sysdate, it.contract_num, it.contract_id , 1,
985 gNegotiatedPrice --Bug#3107596
986 FROM ICX_CAT_ITEMS_GT it
987 WHERE rowid = gRowIds(i)
988 AND gRowTypes(i) = p_batch_type
989 AND gSystemActions(i) = p_action
990 AND v_price_updated(i) = 0;
991
992 xErrLoc := 400;
993
994 -- update the existing prices
995 FORALL i in 1..gRowIds.COUNT
996 UPDATE icx_cat_item_prices ip
997 SET (ip.unit_price, ip.currency, ip.unit_of_measure, ip.supplier_site_id,
998 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,
999 ip.price_type, --Bug#3503280
1000 negotiated_by_preparer_flag) --Bug#3107596
1001 = (SELECT it.unit_price, it.currency, nvl(it.unit_of_measure, ip.unit_of_measure), it.supplier_site_id,
1002 it.supplier_site_code, gJobNumber, gUserId, gUserLogin, sysdate,
1003 it.contract_num, it.contract_id, 1,
1004 gPriceTypes(i), --Bug#3503280
1005 gNegotiatedPrice --Bug#3107596
1006 FROM ICX_CAT_ITEMS_GT it
1007 WHERE rowid = gRowIds(i)
1008 AND gRowTypes(i) = p_batch_type
1009 AND gSystemActions(i) = p_action
1010 AND v_price_updated(i) = 1)
1011 WHERE ip.rt_item_id = gItemIds(i)
1012 AND ip.price_list_id = gPricelistIds(i)
1013 AND ip.org_id = gOrgIds(i)
1014 AND ip.supplier_site_id = gSupplierSiteIds(i)
1015 AND gRowTypes(i) = p_batch_type
1016 AND v_price_updated(i) > 0
1017 AND ip.price_type in ( 'BULKLOAD', 'CONTRACT') --Bug#2719434
1018 AND gSystemActions(i) = p_action;
1019 END IF; -- end of if p_batch_type = PRICE
1020
1021 -- OEX_IP_PORTING
1022 -- ASL rows for which new bulkloaded price has been added should be set
1023 -- to active_flag = 'N'
1024 FORALL i in 1..gRowIds.COUNT
1025 UPDATE icx_cat_item_prices
1026 SET active_flag = 'N'
1027 WHERE
1028 rt_item_id = gItemIds(i)
1029 AND gSystemActions(i) = p_action
1030 AND price_type = 'ASL'
1031 AND v_price_updated(i) = 0;
1032
1033 -- BUYER NORMALIZATION
1034 -- No need to check gRowTpye and gSystemActions, this is only called
1035 -- with 'PRICE','ADD'
1036 -- If gSystemActions(i) is DELETE, then it will not be in gDistinctItemIds
1037 xErrLoc := 150;
1038
1039 FORALL i IN 1..gDistinctItemIds.COUNT
1040 INSERT INTO icx_cat_items_ctx_tlp
1041 (rt_item_id, language, sequence, ctx_desc, org_id)
1042 SELECT gDistinctItemIds(i), tl.language, v_sequence,
1043 to_char(gDistinctBuyerIds(i)), gDistinctBuyerIds(i)
1044 FROM icx_cat_items_tlp tl
1045 WHERE tl.rt_item_id = gDistinctItemIds(i)
1046 AND NOT EXISTS
1047 (SELECT 1 FROM icx_cat_items_ctx_tlp
1048 WHERE rt_item_id = gDistinctItemIds(i)
1049 AND org_id = gDistinctBuyerIds(i));
1050
1051 xErrLoc := 160;
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 ROLLBACK;
1055
1056 RAISE_APPLICATION_ERROR(-20000,
1057 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_addupdate_prices('
1058 || xErrLoc || '): ' || SQLERRM);
1059 END process_batch_addupdate_prices;
1060
1061 PROCEDURE process_batch_addupdate_prices(p_batch_type IN VARCHAR2,
1062 p_action IN VARCHAR2 DEFAULT 'UPDATE') IS
1063 xErrLoc PLS_INTEGER := 100;
1064 -- process_batch_addupdate_prices is capable of returning
1065 -- number of failed lines (vNumFailedLines) while processing.
1066 -- But for item handling there will not be any failed lines
1067 -- in process_batch_addupdate_prices, so we are ignoring
1068 -- this value. May be useful in future..This parameter
1069 -- is useful only when calling from process_batch_prices
1070 vNumFailedLines NUMBER := 0;
1071
1072 BEGIN
1073 -- process_batch_prices needs vNumFailedLines, so it should call the
1074 -- 3 parameter implementation of this method
1075 process_batch_addupdate_prices(p_batch_type, p_action, vNumFailedLines);
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 ROLLBACK;
1080
1081 RAISE_APPLICATION_ERROR(-20000,
1082 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_addupdate_prices('
1083 || xErrLoc || ') 2 arg: ' || SQLERRM);
1084 END process_batch_addupdate_prices;
1085
1086 /* Processes the price deletes in the current batch */
1087 -- icx_por_ext_item.getBulkLoadActiveFlag() returns,
1088 -- 'A' if the bulkloaded price is active and there is an ASL
1089 -- 'Y' if the bulkloaded price is active and there is No ASL
1090 -- 'N' if the bulkloaded price is inactive
1091 PROCEDURE process_batch_delete_prices(p_batch_type IN VARCHAR2) IS
1092 i PLS_INTEGER;
1093 v_action VARCHAR2(10) := 'DELETE';
1094 xErrLoc PLS_INTEGER := 100;
1095 BEGIN
1096
1097 -- First move over the existing price line to the price history table
1098 -- only for batch_type = PRICE
1099 -- for ITEM PRICE we clean everything up
1100
1101 IF (p_batch_type = 'PRICE') THEN
1102 FORALL i in 1..gRowIds.COUNT
1103 INSERT into icx_cat_price_history(rt_item_id, status, price_type, asl_id, supplier_site_id,
1104 contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
1105 active_flag, search_type, unit_price, currency, unit_of_measure,
1106 supplier_site_code, contract_num, contract_line_num, price_list_id, archived_date,
1107 last_update_login, last_updated_by, last_update_date, created_by, creation_date,
1108 request_id, program_application_id, program_id, program_update_date, object_version_number)
1109 SELECT rt_item_id, 'DELETED', price_type, asl_id, supplier_site_id,
1110 contract_id, contract_line_id, template_id, template_line_id, mtl_category_id, org_id,
1111 active_flag, search_type, unit_price, currency, unit_of_measure,
1112 supplier_site_code, contract_num, contract_line_num, price_list_id, sysdate,
1113 last_update_login, last_updated_by, last_update_date, created_by, creation_date,
1114 request_id, program_application_id, program_id, program_update_date, object_version_number
1115 FROM icx_cat_item_prices
1116 WHERE rt_item_id = gItemIds(i)
1117 AND price_list_id = gPricelistIds(i)
1118 AND org_id = gOrgIds(i)
1119 AND supplier_site_id = gSupplierSiteIds(i)
1120 AND gRowTypes(i) = p_batch_type
1121 AND gSystemActions(i) = v_action;
1122 END IF;
1123
1124 xErrLoc := 150;
1125 -- If it was a delete operation then the ASL active flag
1126 -- will be set to "Y", if it did not have contracts/templates..
1127 -- If the icx_por_ext_item.getBulkLoadActiveFlag returns 'A' or 'Y' then it means that
1128 -- there are no contract, templates and this bulkloaded item was the last
1129 -- active price. So after deletion of this price, active flag of ASL price
1130 -- will be set to 'Y'. If the icx_por_ext_item.getBulkLoadActiveFlag returned 'N', the
1131 -- there is a contract/template, So set the active_flag to 'N'
1132 -- (Active flag constraint used to pick the right index.)
1133 FORALL i in 1..gRowIds.COUNT
1134 update icx_cat_item_prices
1135 set active_flag = gActiveFlags(i)
1136 where rt_item_id = gItemIds(i)
1137 and active_flag = 'N'
1138 and org_id = gOrgIds(i)
1139 and supplier_site_id = gSupplierSiteIds(i)
1140 and price_type = 'ASL'
1141 and gRowTypes(i) = p_batch_type
1142 and gSystemActions(i) = v_action;
1143
1144 xErrLoc := 200;
1145
1146 -- Now delete the lines from the item_prices table
1147 FORALL i in 1..gRowIds.COUNT
1148 DELETE from icx_cat_item_prices
1149 WHERE rt_item_id = gItemIds(i)
1150 AND price_list_id = gPricelistIds(i)
1151 AND org_id = gOrgIds(i)
1152 AND supplier_site_id = gSupplierSiteIds(i)
1153 AND gRowTypes(i) = p_batch_type
1154 AND gSystemActions(i) = v_action;
1155
1156 xErrLoc := 300;
1157
1158 -- If batch type is ITEM PRICE this means we are deleting the item
1159 -- so we not only delete the lines from item prices we also clean up the history table
1160 IF (p_batch_type = 'ITEM_PRICE') THEN
1161 FORALL i in 1..gRowIds.COUNT
1162 DELETE from icx_cat_price_history
1163 WHERE rt_item_id = gItemIds(i)
1164 AND price_list_id = gPricelistIds(i)
1165 AND org_id = gOrgIds(i)
1166 AND supplier_site_id = gSupplierSiteIds(i)
1167 AND gRowTypes(i) = p_batch_type
1168 AND gSystemActions(i) = v_action;
1169 END IF;
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 ROLLBACK;
1173
1174 RAISE_APPLICATION_ERROR(-20000,
1175 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_delete_prices('
1176 || xErrLoc || '): ' || SQLERRM);
1177 END process_batch_delete_prices;
1178
1179 /* Populates ICX_POR_CATEGORY_ITEMS for items in the current batch
1180 Also updates the category information in icx_cat_items_tlp
1181 when category is changed for an item
1182 */
1183 PROCEDURE process_batch_common IS
1184 i PLS_INTEGER;
1185 vCount PLS_INTEGER;
1186 vItemIds dbms_sql.number_table;
1187 vCategoryIds dbms_sql.number_table;
1188 xErrLoc PLS_INTEGER := 100;
1189
1190 BEGIN
1191
1192 vCount := 0;
1193
1194 xErrLoc := 100;
1195
1196 -- For add we insert into category items
1197 FOR i in 1..gRowIds.COUNT LOOP
1198 IF (gSystemActions(i) = 'ADD') THEN
1199 vCount := vCount + 1;
1200 vItemIds(vCount) := gItemIds(i);
1201 vCategoryIds(vCount) := gCategoryIds(i);
1202 END IF;
1203 END LOOP;
1204
1205 xErrLoc := 200;
1206
1207 FORALL i in 1..vCount
1208 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)
1209 VALUES (vCategoryIds(i), vItemIds(i), gUserLogin, gUserId, sysdate, gUserId, sysdate);
1210
1211 xErrLoc := 300;
1212
1213 --For update/translate we update category items
1214 -- There could be template reference in the category items table, so just
1215 -- update only for the genus category specified in the catalog file.
1216 FORALL i in 1..gItemIds.COUNT
1217 UPDATE icx_cat_category_items
1218 SET rt_category_id = gCategoryIds(i)
1219 WHERE gSystemActions(i) in ('UPDATE', 'TRANSLATE')
1220 and rt_item_id = gItemIds(i)
1221 and rt_category_id = gOldCategoryIds(i); --Bug#2714487
1222 xErrLoc := 350;
1223
1224 -- For delete we delete from icx_cat_category_items
1225 FORALL i in 1..gItemIds.COUNT
1226 DELETE from icx_cat_category_items
1227 WHERE gSystemActions(i) = 'DELETE'
1228 and rt_category_id = gCategoryIds(i)
1229 and rt_item_id = gItemIds(i);
1230
1231 xErrLoc := 400;
1232
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 ROLLBACK;
1236
1237 RAISE_APPLICATION_ERROR(-20000,
1238 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_common('
1239 || xErrLoc || '): ' || SQLERRM);
1240 END process_batch_common;
1241
1242 /* Processes the new items in the current batch */
1243 PROCEDURE process_batch_add IS
1244 i PLS_INTEGER;
1245 xErrLoc PLS_INTEGER := 100;
1246 v_action VARCHAR2(10) := 'ADD';
1247 v_cursor_id NUMBER;
1248 v_catid PLS_INTEGER;
1249 v_count NUMBER;
1250 BEGIN
1251
1252 -- ICX_CAT_ITEMS_B
1253 FORALL i in 1..gRowIds.COUNT
1254 INSERT into icx_cat_items_b(rt_item_id, org_id, supplier_id,supplier,
1255 supplier_part_num, supplier_part_auxid, catalog_name, --Bug#2611529
1256 extractor_updated_flag, request_id, created_by,
1257 creation_date, last_updated_by, last_update_login, last_update_date, OBJECT_VERSION_NUMBER)
1258 SELECT gItemIds(i), it.org_id, it.supplier_id, it.supplier,
1259 it.supplier_part_num, it.supplier_part_auxid, gCatalogName, --Bug#2611529
1260 'N', gJobNumber, gUserId, sysdate, gUserId,
1261 gUserLogin, sysdate, 1
1262 FROM ICX_CAT_ITEMS_GT it
1263 WHERE it.rowid = gRowids(i)
1264 AND v_action = gSystemActions(i);
1265
1266 xErrLoc := 200;
1267
1268 -- ICX_CAT_ITEMS_TLP
1269 FORALL i in 1..gRowIds.COUNT
1270 INSERT into icx_cat_items_tlp(rt_item_id, language, org_id, supplier_id,
1271 item_source_type, search_type, primary_category_id,
1272 primary_category_name, supplier, supplier_part_num,
1273 supplier_part_auxid, manufacturer,--Bug#2611529
1274 manufacturer_url, supplier_url, --Bug#2611529
1275 manufacturer_part_num, description, comments, alias, picture,
1276 picture_url, attachment_url, long_description, unspsc_code,
1277 thumbnail_image, availability, lead_time, item_type,
1278 text_base_attribute1, text_base_attribute2, text_base_attribute3,
1279 text_base_attribute4, text_base_attribute5, text_base_attribute6,
1280 text_base_attribute7, text_base_attribute8, text_base_attribute9,
1281 text_base_attribute10, text_base_attribute11, text_base_attribute12,
1282 text_base_attribute13, text_base_attribute14, text_base_attribute15,
1283 text_base_attribute16, text_base_attribute17, text_base_attribute18,
1284 text_base_attribute19, text_base_attribute20, text_base_attribute21,
1285 text_base_attribute22, text_base_attribute23, text_base_attribute24,
1286 text_base_attribute25, text_base_attribute26, text_base_attribute27,
1287 text_base_attribute28, text_base_attribute29, text_base_attribute30,
1288 text_base_attribute31, text_base_attribute32, text_base_attribute33,
1289 text_base_attribute34, text_base_attribute35, text_base_attribute36,
1290 text_base_attribute37, text_base_attribute38, text_base_attribute39,
1291 text_base_attribute40, text_base_attribute41, text_base_attribute42,
1292 text_base_attribute43, text_base_attribute44, text_base_attribute45,
1293 text_base_attribute46, text_base_attribute47, text_base_attribute48,
1294 text_base_attribute49, text_base_attribute50, text_base_attribute51,
1295 text_base_attribute52, text_base_attribute53, text_base_attribute54,
1296 text_base_attribute55, text_base_attribute56, text_base_attribute57,
1297 text_base_attribute58, text_base_attribute59, text_base_attribute60,
1298 text_base_attribute61, text_base_attribute62, text_base_attribute63,
1299 text_base_attribute64, text_base_attribute65, text_base_attribute66,
1300 text_base_attribute67, text_base_attribute68, text_base_attribute69,
1301 text_base_attribute70, text_base_attribute71, text_base_attribute72,
1302 text_base_attribute73, text_base_attribute74, text_base_attribute75,
1303 text_base_attribute76, text_base_attribute77, text_base_attribute78,
1304 text_base_attribute79, text_base_attribute80, text_base_attribute81,
1305 text_base_attribute82, text_base_attribute83, text_base_attribute84,
1306 text_base_attribute85, text_base_attribute86, text_base_attribute87,
1307 text_base_attribute88, text_base_attribute89, text_base_attribute90,
1308 text_base_attribute91, text_base_attribute92, text_base_attribute93,
1309 text_base_attribute94, text_base_attribute95, text_base_attribute96,
1310 text_base_attribute97, text_base_attribute98, text_base_attribute99,
1311 text_base_attribute100,
1312 num_base_attribute1, num_base_attribute2, num_base_attribute3,
1313 num_base_attribute4, num_base_attribute5, num_base_attribute6,
1314 num_base_attribute7, num_base_attribute8, num_base_attribute9,
1315 num_base_attribute10, num_base_attribute11, num_base_attribute12,
1316 num_base_attribute13, num_base_attribute14, num_base_attribute15,
1317 num_base_attribute16, num_base_attribute17, num_base_attribute18,
1318 num_base_attribute19, num_base_attribute20, num_base_attribute21,
1319 num_base_attribute22, num_base_attribute23, num_base_attribute24,
1320 num_base_attribute25, num_base_attribute26, num_base_attribute27,
1321 num_base_attribute28, num_base_attribute29, num_base_attribute30,
1322 num_base_attribute31, num_base_attribute32, num_base_attribute33,
1323 num_base_attribute34, num_base_attribute35, num_base_attribute36,
1324 num_base_attribute37, num_base_attribute38, num_base_attribute39,
1325 num_base_attribute40, num_base_attribute41, num_base_attribute42,
1326 num_base_attribute43, num_base_attribute44, num_base_attribute45,
1327 num_base_attribute46, num_base_attribute47, num_base_attribute48,
1328 num_base_attribute49, num_base_attribute50, num_base_attribute51,
1329 num_base_attribute52, num_base_attribute53, num_base_attribute54,
1330 num_base_attribute55, num_base_attribute56, num_base_attribute57,
1331 num_base_attribute58, num_base_attribute59, num_base_attribute60,
1332 num_base_attribute61, num_base_attribute62, num_base_attribute63,
1333 num_base_attribute64, num_base_attribute65, num_base_attribute66,
1334 num_base_attribute67, num_base_attribute68, num_base_attribute69,
1335 num_base_attribute70, num_base_attribute71, num_base_attribute72,
1336 num_base_attribute73, num_base_attribute74, num_base_attribute75,
1337 num_base_attribute76, num_base_attribute77, num_base_attribute78,
1338 num_base_attribute79, num_base_attribute80, num_base_attribute81,
1339 num_base_attribute82, num_base_attribute83, num_base_attribute84,
1340 num_base_attribute85, num_base_attribute86, num_base_attribute87,
1341 num_base_attribute88, num_base_attribute89, num_base_attribute90,
1342 num_base_attribute91, num_base_attribute92, num_base_attribute93,
1343 num_base_attribute94, num_base_attribute95, num_base_attribute96,
1344 num_base_attribute97, num_base_attribute98, num_base_attribute99,
1345 num_base_attribute100,
1346 tl_text_base_attribute1, tl_text_base_attribute2, tl_text_base_attribute3,
1347 tl_text_base_attribute4, tl_text_base_attribute5, tl_text_base_attribute6,
1348 tl_text_base_attribute7, tl_text_base_attribute8, tl_text_base_attribute9,
1349 tl_text_base_attribute10, tl_text_base_attribute11, tl_text_base_attribute12,
1350 tl_text_base_attribute13, tl_text_base_attribute14, tl_text_base_attribute15,
1351 tl_text_base_attribute16, tl_text_base_attribute17, tl_text_base_attribute18,
1352 tl_text_base_attribute19, tl_text_base_attribute20, tl_text_base_attribute21,
1353 tl_text_base_attribute22, tl_text_base_attribute23, tl_text_base_attribute24,
1354 tl_text_base_attribute25, tl_text_base_attribute26, tl_text_base_attribute27,
1355 tl_text_base_attribute28, tl_text_base_attribute29, tl_text_base_attribute30,
1356 tl_text_base_attribute31, tl_text_base_attribute32, tl_text_base_attribute33,
1357 tl_text_base_attribute34, tl_text_base_attribute35, tl_text_base_attribute36,
1358 tl_text_base_attribute37, tl_text_base_attribute38, tl_text_base_attribute39,
1359 tl_text_base_attribute40, tl_text_base_attribute41, tl_text_base_attribute42,
1360 tl_text_base_attribute43, tl_text_base_attribute44, tl_text_base_attribute45,
1361 tl_text_base_attribute46, tl_text_base_attribute47, tl_text_base_attribute48,
1362 tl_text_base_attribute49, tl_text_base_attribute50, tl_text_base_attribute51,
1363 tl_text_base_attribute52, tl_text_base_attribute53, tl_text_base_attribute54,
1364 tl_text_base_attribute55, tl_text_base_attribute56, tl_text_base_attribute57,
1365 tl_text_base_attribute58, tl_text_base_attribute59, tl_text_base_attribute60,
1366 tl_text_base_attribute61, tl_text_base_attribute62, tl_text_base_attribute63,
1367 tl_text_base_attribute64, tl_text_base_attribute65, tl_text_base_attribute66,
1368 tl_text_base_attribute67, tl_text_base_attribute68, tl_text_base_attribute69,
1369 tl_text_base_attribute70, tl_text_base_attribute71, tl_text_base_attribute72,
1370 tl_text_base_attribute73, tl_text_base_attribute74, tl_text_base_attribute75,
1371 tl_text_base_attribute76, tl_text_base_attribute77, tl_text_base_attribute78,
1372 tl_text_base_attribute79, tl_text_base_attribute80, tl_text_base_attribute81,
1373 tl_text_base_attribute82, tl_text_base_attribute83, tl_text_base_attribute84,
1374 tl_text_base_attribute85, tl_text_base_attribute86, tl_text_base_attribute87,
1375 tl_text_base_attribute88, tl_text_base_attribute89, tl_text_base_attribute90,
1376 tl_text_base_attribute91, tl_text_base_attribute92, tl_text_base_attribute93,
1377 tl_text_base_attribute94, tl_text_base_attribute95, tl_text_base_attribute96,
1378 tl_text_base_attribute97, tl_text_base_attribute98, tl_text_base_attribute99,
1379 tl_text_base_attribute100,
1380 ctx_desc, request_id, created_by, creation_date, last_updated_by,
1381 last_update_login, last_update_date)
1382 SELECT gItemIds(i), gJobLanguage, it.org_id,
1383 it.supplier_id, 'SUPPLIER', 'SUPPLIER',
1384 it.rt_category_id, it.category_name,
1385 it.supplier, it.supplier_part_num,
1386 it.supplier_part_auxid, it.manufacturer, --Bug#2611529
1387 it.manufacturer_url, it.supplier_url, --Bug#2611529
1388 it.manufacturer_part_num, it.description, it.comments, it.alias, it.picture,
1389 it.picture_url, it.attachment_url, it.long_description, it. unspsc_code,
1390 it.thumbnail_image, it.availability, it.lead_time, it.item_type,
1391 it.text_base_attribute1, it.text_base_attribute2, it.text_base_attribute3,
1392 it.text_base_attribute4, it.text_base_attribute5, it.text_base_attribute6,
1393 it.text_base_attribute7, it.text_base_attribute8, it.text_base_attribute9,
1394 it.text_base_attribute10, it.text_base_attribute11, it.text_base_attribute12,
1395 it.text_base_attribute13, it.text_base_attribute14, it.text_base_attribute15,
1396 it.text_base_attribute16, it.text_base_attribute17, it.text_base_attribute18,
1397 it.text_base_attribute19, it.text_base_attribute20, it.text_base_attribute21,
1398 it.text_base_attribute22, it.text_base_attribute23, it.text_base_attribute24,
1399 it.text_base_attribute25, it.text_base_attribute26, it.text_base_attribute27,
1400 it.text_base_attribute28, it.text_base_attribute29, it.text_base_attribute30,
1401 it.text_base_attribute31, it.text_base_attribute32, it.text_base_attribute33,
1402 it.text_base_attribute34, it.text_base_attribute35, it.text_base_attribute36,
1403 it.text_base_attribute37, it.text_base_attribute38, it.text_base_attribute39,
1404 it.text_base_attribute40, it.text_base_attribute41, it.text_base_attribute42,
1405 it.text_base_attribute43, it.text_base_attribute44, it.text_base_attribute45,
1406 it.text_base_attribute46, it.text_base_attribute47, it.text_base_attribute48,
1407 it.text_base_attribute49, it.text_base_attribute50, it.text_base_attribute51,
1408 it.text_base_attribute52, it.text_base_attribute53, it.text_base_attribute54,
1409 it.text_base_attribute55, it.text_base_attribute56, it.text_base_attribute57,
1410 it.text_base_attribute58, it.text_base_attribute59, it.text_base_attribute60,
1411 it.text_base_attribute61, it.text_base_attribute62, it.text_base_attribute63,
1412 it.text_base_attribute64, it.text_base_attribute65, it.text_base_attribute66,
1413 it.text_base_attribute67, it.text_base_attribute68, it.text_base_attribute69,
1414 it.text_base_attribute70, it.text_base_attribute71, it.text_base_attribute72,
1415 it.text_base_attribute73, it.text_base_attribute74, it.text_base_attribute75,
1416 it.text_base_attribute76, it.text_base_attribute77, it.text_base_attribute78,
1417 it.text_base_attribute79, it.text_base_attribute80, it.text_base_attribute81,
1418 it.text_base_attribute82, it.text_base_attribute83, it.text_base_attribute84,
1419 it.text_base_attribute85, it.text_base_attribute86, it.text_base_attribute87,
1420 it.text_base_attribute88, it.text_base_attribute89, it.text_base_attribute90,
1421 it.text_base_attribute91, it.text_base_attribute92, it.text_base_attribute93,
1422 it.text_base_attribute94, it.text_base_attribute95, it.text_base_attribute96,
1423 it.text_base_attribute97, it.text_base_attribute98, it.text_base_attribute99,
1424 it.text_base_attribute100,
1425 it.num_base_attribute1, it.num_base_attribute2, it.num_base_attribute3,
1426 it.num_base_attribute4, it.num_base_attribute5, it.num_base_attribute6,
1427 it.num_base_attribute7, it.num_base_attribute8, it.num_base_attribute9,
1428 it.num_base_attribute10, it.num_base_attribute11, it.num_base_attribute12,
1429 it.num_base_attribute13, it.num_base_attribute14, it.num_base_attribute15,
1430 it.num_base_attribute16, it.num_base_attribute17, it.num_base_attribute18,
1431 it.num_base_attribute19, it.num_base_attribute20, it.num_base_attribute21,
1432 it.num_base_attribute22, it.num_base_attribute23, it.num_base_attribute24,
1433 it.num_base_attribute25, it.num_base_attribute26, it.num_base_attribute27,
1434 it.num_base_attribute28, it.num_base_attribute29, it.num_base_attribute30,
1435 it.num_base_attribute31, it.num_base_attribute32, it.num_base_attribute33,
1436 it.num_base_attribute34, it.num_base_attribute35, it.num_base_attribute36,
1437 it.num_base_attribute37, it.num_base_attribute38, it.num_base_attribute39,
1438 it.num_base_attribute40, it.num_base_attribute41, it.num_base_attribute42,
1439 it.num_base_attribute43, it.num_base_attribute44, it.num_base_attribute45,
1440 it.num_base_attribute46, it.num_base_attribute47, it.num_base_attribute48,
1441 it.num_base_attribute49, it.num_base_attribute50, it.num_base_attribute51,
1442 it.num_base_attribute52, it.num_base_attribute53, it.num_base_attribute54,
1443 it.num_base_attribute55, it.num_base_attribute56, it.num_base_attribute57,
1444 it.num_base_attribute58, it.num_base_attribute59, it.num_base_attribute60,
1445 it.num_base_attribute61, it.num_base_attribute62, it.num_base_attribute63,
1446 it.num_base_attribute64, it.num_base_attribute65, it.num_base_attribute66,
1447 it.num_base_attribute67, it.num_base_attribute68, it.num_base_attribute69,
1448 it.num_base_attribute70, it.num_base_attribute71, it.num_base_attribute72,
1449 it.num_base_attribute73, it.num_base_attribute74, it.num_base_attribute75,
1450 it.num_base_attribute76, it.num_base_attribute77, it.num_base_attribute78,
1451 it.num_base_attribute79, it.num_base_attribute80, it.num_base_attribute81,
1452 it.num_base_attribute82, it.num_base_attribute83, it.num_base_attribute84,
1453 it.num_base_attribute85, it.num_base_attribute86, it.num_base_attribute87,
1454 it.num_base_attribute88, it.num_base_attribute89, it.num_base_attribute90,
1455 it.num_base_attribute91, it.num_base_attribute92, it.num_base_attribute93,
1456 it.num_base_attribute94, it.num_base_attribute95, it.num_base_attribute96,
1457 it.num_base_attribute97, it.num_base_attribute98, it.num_base_attribute99,
1458 it.num_base_attribute100,
1459 it.tl_text_base_attribute1, it.tl_text_base_attribute2, it.tl_text_base_attribute3,
1460 it.tl_text_base_attribute4, it.tl_text_base_attribute5, it.tl_text_base_attribute6,
1461 it.tl_text_base_attribute7, it.tl_text_base_attribute8, it.tl_text_base_attribute9,
1462 it.tl_text_base_attribute10, it.tl_text_base_attribute11, it.tl_text_base_attribute12,
1463 it.tl_text_base_attribute13, it.tl_text_base_attribute14, it.tl_text_base_attribute15,
1464 it.tl_text_base_attribute16, it.tl_text_base_attribute17, it.tl_text_base_attribute18,
1465 it.tl_text_base_attribute19, it.tl_text_base_attribute20, it.tl_text_base_attribute21,
1466 it.tl_text_base_attribute22, it.tl_text_base_attribute23, it.tl_text_base_attribute24,
1467 it.tl_text_base_attribute25, it.tl_text_base_attribute26, it.tl_text_base_attribute27,
1468 it.tl_text_base_attribute28, it.tl_text_base_attribute29, it.tl_text_base_attribute30,
1469 it.tl_text_base_attribute31, it.tl_text_base_attribute32, it.tl_text_base_attribute33,
1470 it.tl_text_base_attribute34, it.tl_text_base_attribute35, it.tl_text_base_attribute36,
1471 it.tl_text_base_attribute37, it.tl_text_base_attribute38, it.tl_text_base_attribute39,
1472 it.tl_text_base_attribute40, it.tl_text_base_attribute41, it.tl_text_base_attribute42,
1473 it.tl_text_base_attribute43, it.tl_text_base_attribute44, it.tl_text_base_attribute45,
1474 it.tl_text_base_attribute46, it.tl_text_base_attribute47, it.tl_text_base_attribute48,
1475 it.tl_text_base_attribute49, it.tl_text_base_attribute50, it.tl_text_base_attribute51,
1476 it.tl_text_base_attribute52, it.tl_text_base_attribute53, it.tl_text_base_attribute54,
1477 it.tl_text_base_attribute55, it.tl_text_base_attribute56, it.tl_text_base_attribute57,
1478 it.tl_text_base_attribute58, it.tl_text_base_attribute59, it.tl_text_base_attribute60,
1479 it.tl_text_base_attribute61, it.tl_text_base_attribute62, it.tl_text_base_attribute63,
1480 it.tl_text_base_attribute64, it.tl_text_base_attribute65, it.tl_text_base_attribute66,
1481 it.tl_text_base_attribute67, it.tl_text_base_attribute68, it.tl_text_base_attribute69,
1482 it.tl_text_base_attribute70, it.tl_text_base_attribute71, it.tl_text_base_attribute72,
1483 it.tl_text_base_attribute73, it.tl_text_base_attribute74, it.tl_text_base_attribute75,
1484 it.tl_text_base_attribute76, it.tl_text_base_attribute77, it.tl_text_base_attribute78,
1485 it.tl_text_base_attribute79, it.tl_text_base_attribute80, it.tl_text_base_attribute81,
1486 it.tl_text_base_attribute82, it.tl_text_base_attribute83, it.tl_text_base_attribute84,
1487 it.tl_text_base_attribute85, it.tl_text_base_attribute86, it.tl_text_base_attribute87,
1488 it.tl_text_base_attribute88, it.tl_text_base_attribute89, it.tl_text_base_attribute90,
1489 it.tl_text_base_attribute91, it.tl_text_base_attribute92, it.tl_text_base_attribute93,
1490 it.tl_text_base_attribute94, it.tl_text_base_attribute95, it.tl_text_base_attribute96,
1491 it.tl_text_base_attribute97, it.tl_text_base_attribute98, it.tl_text_base_attribute99,
1492 it.tl_text_base_attribute100,
1493 null, gJobNumber, gUserId, sysdate, gUserId, gUserLogin, sysdate
1494 FROM ICX_CAT_ITEMS_GT it
1495 WHERE it.rowid = gRowids(i)
1496 AND v_action = gSystemActions(i);
1497
1498 xErrLoc := 300;
1499
1500 -- ICX_CAT_EXT_ITEMS_TLP
1501 -- Category_Change
1502 FORALL i in 1..gRowIds.COUNT
1503 INSERT into icx_cat_ext_items_tlp(rt_item_id, language, org_id, request_id,
1504 rt_category_id, primary_flag,
1505 text_cat_attribute1, text_cat_attribute2, text_cat_attribute3,
1506 text_cat_attribute4, text_cat_attribute5, text_cat_attribute6,
1507 text_cat_attribute7, text_cat_attribute8, text_cat_attribute9,
1508 text_cat_attribute10, text_cat_attribute11, text_cat_attribute12,
1509 text_cat_attribute13, text_cat_attribute14, text_cat_attribute15,
1510 text_cat_attribute16, text_cat_attribute17, text_cat_attribute18,
1511 text_cat_attribute19, text_cat_attribute20, text_cat_attribute21,
1512 text_cat_attribute22, text_cat_attribute23, text_cat_attribute24,
1513 text_cat_attribute25, text_cat_attribute26, text_cat_attribute27,
1514 text_cat_attribute28, text_cat_attribute29, text_cat_attribute30,
1515 text_cat_attribute31, text_cat_attribute32, text_cat_attribute33,
1516 text_cat_attribute34, text_cat_attribute35, text_cat_attribute36,
1517 text_cat_attribute37, text_cat_attribute38, text_cat_attribute39,
1518 text_cat_attribute40, text_cat_attribute41, text_cat_attribute42,
1519 text_cat_attribute43, text_cat_attribute44, text_cat_attribute45,
1520 text_cat_attribute46, text_cat_attribute47, text_cat_attribute48,
1521 text_cat_attribute49, text_cat_attribute50,
1522 num_cat_attribute1, num_cat_attribute2, num_cat_attribute3,
1523 num_cat_attribute4, num_cat_attribute5, num_cat_attribute6,
1524 num_cat_attribute7, num_cat_attribute8, num_cat_attribute9,
1525 num_cat_attribute10, num_cat_attribute11, num_cat_attribute12,
1526 num_cat_attribute13, num_cat_attribute14, num_cat_attribute15,
1527 num_cat_attribute16, num_cat_attribute17, num_cat_attribute18,
1528 num_cat_attribute19, num_cat_attribute20, num_cat_attribute21,
1529 num_cat_attribute22, num_cat_attribute23, num_cat_attribute24,
1530 num_cat_attribute25, num_cat_attribute26, num_cat_attribute27,
1531 num_cat_attribute28, num_cat_attribute29, num_cat_attribute30,
1532 num_cat_attribute31, num_cat_attribute32, num_cat_attribute33,
1533 num_cat_attribute34, num_cat_attribute35, num_cat_attribute36,
1534 num_cat_attribute37, num_cat_attribute38, num_cat_attribute39,
1535 num_cat_attribute40, num_cat_attribute41, num_cat_attribute42,
1536 num_cat_attribute43, num_cat_attribute44, num_cat_attribute45,
1537 num_cat_attribute46, num_cat_attribute47, num_cat_attribute48,
1538 num_cat_attribute49, num_cat_attribute50,
1539 tl_text_cat_attribute1, tl_text_cat_attribute2, tl_text_cat_attribute3,
1540 tl_text_cat_attribute4, tl_text_cat_attribute5, tl_text_cat_attribute6,
1541 tl_text_cat_attribute7, tl_text_cat_attribute8, tl_text_cat_attribute9,
1542 tl_text_cat_attribute10, tl_text_cat_attribute11, tl_text_cat_attribute12,
1543 tl_text_cat_attribute13, tl_text_cat_attribute14, tl_text_cat_attribute15,
1544 tl_text_cat_attribute16, tl_text_cat_attribute17, tl_text_cat_attribute18,
1545 tl_text_cat_attribute19, tl_text_cat_attribute20, tl_text_cat_attribute21,
1546 tl_text_cat_attribute22, tl_text_cat_attribute23, tl_text_cat_attribute24,
1547 tl_text_cat_attribute25, tl_text_cat_attribute26, tl_text_cat_attribute27,
1548 tl_text_cat_attribute28, tl_text_cat_attribute29, tl_text_cat_attribute30,
1549 tl_text_cat_attribute31, tl_text_cat_attribute32, tl_text_cat_attribute33,
1550 tl_text_cat_attribute34, tl_text_cat_attribute35, tl_text_cat_attribute36,
1551 tl_text_cat_attribute37, tl_text_cat_attribute38, tl_text_cat_attribute39,
1552 tl_text_cat_attribute40, tl_text_cat_attribute41, tl_text_cat_attribute42,
1553 tl_text_cat_attribute43, tl_text_cat_attribute44, tl_text_cat_attribute45,
1554 tl_text_cat_attribute46, tl_text_cat_attribute47, tl_text_cat_attribute48,
1555 tl_text_cat_attribute49, tl_text_cat_attribute50,
1556 created_by, creation_date, last_updated_by,
1557 last_update_login, last_update_date)
1558 SELECT gItemIds(i), gJobLanguage, it.org_id, gJobNumber,
1559 it.rt_category_id, 'Y',
1560 it.text_cat_attribute1, it.text_cat_attribute2, it.text_cat_attribute3,
1561 it.text_cat_attribute4, it.text_cat_attribute5, it.text_cat_attribute6,
1562 it.text_cat_attribute7, it.text_cat_attribute8, it.text_cat_attribute9,
1563 it.text_cat_attribute10, it.text_cat_attribute11, it.text_cat_attribute12,
1564 it.text_cat_attribute13, it.text_cat_attribute14, it.text_cat_attribute15,
1565 it.text_cat_attribute16, it.text_cat_attribute17, it.text_cat_attribute18,
1566 it.text_cat_attribute19, it.text_cat_attribute20, it.text_cat_attribute21,
1567 it.text_cat_attribute22, it.text_cat_attribute23, it.text_cat_attribute24,
1568 it.text_cat_attribute25, it.text_cat_attribute26, it.text_cat_attribute27,
1569 it.text_cat_attribute28, it.text_cat_attribute29, it.text_cat_attribute30,
1570 it.text_cat_attribute31, it.text_cat_attribute32, it.text_cat_attribute33,
1571 it.text_cat_attribute34, it.text_cat_attribute35, it.text_cat_attribute36,
1572 it.text_cat_attribute37, it.text_cat_attribute38, it.text_cat_attribute39,
1573 it.text_cat_attribute40, it.text_cat_attribute41, it.text_cat_attribute42,
1574 it.text_cat_attribute43, it.text_cat_attribute44, it.text_cat_attribute45,
1575 it.text_cat_attribute46, it.text_cat_attribute47, it.text_cat_attribute48,
1576 it.text_cat_attribute49, it.text_cat_attribute50,
1577 it.num_cat_attribute1, it.num_cat_attribute2, it.num_cat_attribute3,
1578 it.num_cat_attribute4, it.num_cat_attribute5, it.num_cat_attribute6,
1579 it.num_cat_attribute7, it.num_cat_attribute8, it.num_cat_attribute9,
1580 it.num_cat_attribute10, it.num_cat_attribute11, it.num_cat_attribute12,
1581 it.num_cat_attribute13, it.num_cat_attribute14, it.num_cat_attribute15,
1582 it.num_cat_attribute16, it.num_cat_attribute17, it.num_cat_attribute18,
1583 it.num_cat_attribute19, it.num_cat_attribute20, it.num_cat_attribute21,
1584 it.num_cat_attribute22, it.num_cat_attribute23, it.num_cat_attribute24,
1585 it.num_cat_attribute25, it.num_cat_attribute26, it.num_cat_attribute27,
1586 it.num_cat_attribute28, it.num_cat_attribute29, it.num_cat_attribute30,
1587 it.num_cat_attribute31, it.num_cat_attribute32, it.num_cat_attribute33,
1588 it.num_cat_attribute34, it.num_cat_attribute35, it.num_cat_attribute36,
1589 it.num_cat_attribute37, it.num_cat_attribute38, it.num_cat_attribute39,
1590 it.num_cat_attribute40, it.num_cat_attribute41, it.num_cat_attribute42,
1591 it.num_cat_attribute43, it.num_cat_attribute44, it.num_cat_attribute45,
1592 it.num_cat_attribute46, it.num_cat_attribute47, it.num_cat_attribute48,
1593 it.num_cat_attribute49, it.num_cat_attribute50,
1594 it.tl_text_cat_attribute1, it.tl_text_cat_attribute2, it.tl_text_cat_attribute3,
1595 it.tl_text_cat_attribute4, it.tl_text_cat_attribute5, it.tl_text_cat_attribute6,
1596 it.tl_text_cat_attribute7, it.tl_text_cat_attribute8, it.tl_text_cat_attribute9,
1597 it.tl_text_cat_attribute10, it.tl_text_cat_attribute11, it.tl_text_cat_attribute12,
1598 it.tl_text_cat_attribute13, it.tl_text_cat_attribute14, it.tl_text_cat_attribute15,
1599 it.tl_text_cat_attribute16, it.tl_text_cat_attribute17, it.tl_text_cat_attribute18,
1600 it.tl_text_cat_attribute19, it.tl_text_cat_attribute20, it.tl_text_cat_attribute21,
1601 it.tl_text_cat_attribute22, it.tl_text_cat_attribute23, it.tl_text_cat_attribute24,
1602 it.tl_text_cat_attribute25, it.tl_text_cat_attribute26, it.tl_text_cat_attribute27,
1603 it.tl_text_cat_attribute28, it.tl_text_cat_attribute29, it.tl_text_cat_attribute30,
1604 it.tl_text_cat_attribute31, it.tl_text_cat_attribute32, it.tl_text_cat_attribute33,
1605 it.tl_text_cat_attribute34, it.tl_text_cat_attribute35, it.tl_text_cat_attribute36,
1606 it.tl_text_cat_attribute37, it.tl_text_cat_attribute38, it.tl_text_cat_attribute39,
1607 it.tl_text_cat_attribute40, it.tl_text_cat_attribute41, it.tl_text_cat_attribute42,
1608 it.tl_text_cat_attribute43, it.tl_text_cat_attribute44, it.tl_text_cat_attribute45,
1609 it.tl_text_cat_attribute46, it.tl_text_cat_attribute47, it.tl_text_cat_attribute48,
1610 it.tl_text_cat_attribute49, it.tl_text_cat_attribute50,
1611 gUserId, sysdate, gUserId, gUserLogin, sysdate
1612 FROM ICX_CAT_ITEMS_GT it
1613 WHERE it.rowid = gRowids(i)
1614 AND v_action = gSystemActions(i);
1615
1616 xErrLoc := 500;
1617
1618 FOR i IN 1..gDistinctCategoryIds.COUNT LOOP
1619 v_catid := gDistinctCategoryIds(i);
1620
1621 xErrLoc := 600;
1622 IF (gCategoryInfo(v_catid).searchable_desc_count > 0) THEN
1623 xErrLoc := 650;
1624 build_category_ctx_sql(v_catid);
1625 xErrLoc := 700;
1626
1627 FOR j IN 1..gAddCatCtxSQL.COUNT LOOP
1628 xErrLoc := 800;
1629 v_cursor_id := DBMS_SQL.open_cursor;
1630 DBMS_SQL.parse(v_cursor_id, gAddCatCtxSQL(j), dbms_sql.native);
1631 DBMS_SQL.bind_array(v_cursor_id, ':p_item_id', gItemIds);
1632 DBMS_SQL.bind_variable(v_cursor_id, ':p_language',
1633 gJobLanguage);
1634 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence', 5000 + j);
1635 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1636 DBMS_SQL.bind_variable(v_cursor_id, ':current_category_id', v_catid);
1637 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
1638 DBMS_SQL.bind_variable(v_cursor_id, ':action_name', v_action);
1639 DBMS_SQL.bind_array(v_cursor_id, ':p_system_action', gSystemActions);
1640 v_count := DBMS_SQL.execute(v_cursor_id);
1641 DBMS_SQL.close_cursor(v_cursor_id);
1642 END LOOP;
1643
1644 END IF;
1645
1646
1647 END LOOP;
1648
1649 -- ICX_POR_ITEMS_CTX_TLP
1650
1651 FOR i IN 1..gAddRootCtxSQL.COUNT LOOP
1652 xErrLoc := 900 + i;
1653 v_cursor_id := DBMS_SQL.open_cursor;
1654
1655 DBMS_SQL.parse(v_cursor_id, gAddRootCtxSQL(i), dbms_sql.native);
1656 DBMS_SQL.bind_array(v_cursor_id, ':p_item_id', gItemIds);
1657 DBMS_SQL.bind_variable(v_cursor_id, ':p_language', gJobLanguage);
1658
1659 IF (i = gAddRootCtxSQL.COUNT - 1) THEN
1660 -- This is the <buyid> line
1661 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',10000);
1662 ELSIF (i = gAddRootCtxSQL.COUNT) THEN
1663 -- This is the </buyid> line
1664 DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':p_sequence',15000);
1665 ELSE
1666 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence', i);
1667 END IF;
1668
1669 IF (i = 1) THEN
1670 -- The first SQL contains category id and name
1671 DBMS_SQL.bind_array(v_cursor_id, ':p_supplier_id', gSupplierIds);
1672 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
1673 DBMS_SQL.bind_array(v_cursor_id, ':p_category_name', gCategoryNames);
1674 END IF;
1675
1676 DBMS_SQL.bind_variable(v_cursor_id, ':action_name', v_action);
1677 DBMS_SQL.bind_array(v_cursor_id, ':p_system_action', gSystemActions);
1678 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1679 v_count := DBMS_SQL.execute(v_cursor_id);
1680 DBMS_SQL.close_cursor(v_cursor_id);
1681 END LOOP;
1682
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 ROLLBACK;
1686
1687 RAISE_APPLICATION_ERROR(-20000,
1688 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_add('
1689 || xErrLoc || '): ' || SQLERRM);
1690 END process_batch_add;
1691
1692 /* Processes the item updates in the current batch */
1693 PROCEDURE process_batch_update IS
1694 i PLS_INTEGER;
1695 j PLS_INTEGER;
1696 v_cursor_id PLS_INTEGER;
1697 v_count PLS_INTEGER;
1698 v_catid PLS_INTEGER;
1699 v_action VARCHAR2(10) := 'UPDATE';
1700 v_delete_ctx_sql VARCHAR2(255);
1701 vUpdateItemsBSQL VARCHAR2(1000);
1702 v_sequence PLS_INTEGER := 10001;
1703 xErrLoc PLS_INTEGER := 100;
1704 BEGIN
1705
1706 -- ICX_CAT_ITEMS_B
1707 -- Update the catalog name, job#, object version#, last_update_date
1708 xErrLoc := 10;
1709 FORALL i in 1..gCurrentItemIds.COUNT
1710 UPDATE icx_cat_items_b
1711 SET catalog_name = gCatalogName,
1712 last_updated_by = gUserId,
1713 last_update_login = gUserLogin,
1714 last_update_date = sysdate,
1715 request_id = gJobNumber,
1716 object_version_number = object_version_number+1
1717 WHERE rt_item_id = gCurrentItemIds(i) ;
1718
1719
1720 -- gUpdateItemsNonTLSQL: SQL to update the non-translated portion
1721 -- of the ICX_CAT_ITEMS_TLP
1722 -- gUpdateItemsTLSQL: SQL to update the translated portion
1723 -- of the ICX_CAT_ITEMS_TLP
1724
1725 -- ICX_CAT_ITEMS_TLP: Update the Non-Translated Root descriptors
1726 xErrLoc := 100;
1727 v_cursor_id := DBMS_SQL.open_cursor;
1728 DBMS_SQL.parse(v_cursor_id, gUpdateItemsNonTLSQL, DBMS_SQL.NATIVE);
1729 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
1730 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
1731 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
1732 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1733 DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'UPDATE', 10);
1734 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
1735 v_count := DBMS_SQL.execute(v_cursor_id);
1736 DBMS_SQL.close_cursor(v_cursor_id);
1737
1738 -- ICX_CAT_ITEMS_TLP: Update the Translated Root descriptors
1739 xErrLoc := 200;
1740 v_cursor_id := DBMS_SQL.open_cursor;
1741 DBMS_SQL.parse(v_cursor_id, gUpdateItemsTLSQL, DBMS_SQL.NATIVE);
1742 DBMS_SQL.bind_variable(v_cursor_id, ':p_language', gJobLanguage, 4);
1743 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
1744 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
1745 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
1746 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1747 DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'UPDATE', 10);
1748 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
1749 DBMS_SQL.bind_variable(v_cursor_id, ':p_request_id', gJobNumber);
1750 v_count := DBMS_SQL.execute(v_cursor_id);
1751 DBMS_SQL.close_cursor(v_cursor_id);
1752
1753 xErrLoc := 300;
1754
1755 -- Delete the ctx entries for the items that we update now.
1756 FORALL i in 1..gItemIds.COUNT
1757 DELETE FROM icx_cat_items_ctx_tlp
1758 WHERE rt_item_id = gItemIds(i)
1759 AND gSystemActions(i) = 'UPDATE';
1760
1761 -- When there is a category Change, update all the descriptor values
1762 -- with the one from the interface table. The update is for ALL languages.
1763 -- The following sql will clear out all existing descriptor values
1764 -- for that item
1765
1766
1767 xErrLoc := 400;
1768
1769 -- Erase out the existing local descriptor values when category is changed.
1770 -- do it only when there is atleast one local descriptor for
1771 -- the old category
1772 handle_category_change('UPDATE');
1773
1774 xErrLoc := 420;
1775
1776 FOR i in 1..gDistinctCategoryIds.COUNT LOOP
1777 v_catid := gDistinctCategoryIds(i);
1778
1779 -- If Local descriptors exist for the given category
1780 IF (gCategoryInfo(v_catid).descriptor_count > 0) THEN
1781 xErrLoc := 500;
1782
1783 -- Update the Non-Translateable local descriptors for the given category
1784 v_cursor_id := DBMS_SQL.open_cursor;
1785 DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).updateNonTLSQL,
1786 DBMS_SQL.NATIVE);
1787 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
1788 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1789 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
1790 DBMS_SQL.bind_variable(v_cursor_id, ':update_action',
1791 'UPDATE', 10);
1792 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
1793 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
1794 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
1795 v_count := DBMS_SQL.execute(v_cursor_id);
1796 DBMS_SQL.close_cursor(v_cursor_id);
1797
1798 xErrLoc := 600;
1799
1800 -- Update the Translateable local descriptors for the given category
1801 v_cursor_id := DBMS_SQL.open_cursor;
1802 DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).updateTLSQL,
1803 DBMS_SQL.NATIVE);
1804 DBMS_SQL.bind_variable(v_cursor_id, ':p_request_id', gJobNumber);
1805 DBMS_SQL.bind_variable(v_cursor_id, ':p_language',
1806 gJobLanguage, 4);
1807 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
1808 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1809 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
1810 DBMS_SQL.bind_variable(v_cursor_id, ':update_action',
1811 'UPDATE', 10);
1812 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
1813 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
1814 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
1815 v_count := DBMS_SQL.execute(v_cursor_id);
1816 DBMS_SQL.close_cursor(v_cursor_id);
1817
1818 xErrLoc := 700;
1819
1820 END IF; -- end of if local descriptors exist
1821
1822 -- CTX_DESC Update
1823
1824 IF (gCategoryInfo(v_catid).searchable_desc_count > 0) THEN
1825 xErrLoc := 800;
1826 build_category_ctx_sql(v_catid);
1827 xErrLoc := 900;
1828
1829 FOR j IN 1..gUpdateCatCtxSQL.COUNT LOOP
1830 v_cursor_id := DBMS_SQL.open_cursor;
1831 DBMS_SQL.parse(v_cursor_id, gUpdateCatCtxSQL(j),
1832 dbms_sql.native);
1833 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence', 5000 + j);
1834 DBMS_SQL.bind_array(v_cursor_id, ':p_item_id', gItemIds);
1835 DBMS_SQL.bind_variable(v_cursor_id, ':current_category_id', v_catid);
1836 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
1837 DBMS_SQL.bind_variable(v_cursor_id, ':action_name', v_action);
1838 DBMS_SQL.bind_array(v_cursor_id, ':p_system_action', gSystemActions);
1839 v_count := DBMS_SQL.execute(v_cursor_id);
1840 DBMS_SQL.close_cursor(v_cursor_id);
1841 END LOOP;
1842 END IF;
1843
1844 END LOOP;
1845
1846 -- ICX_POR_CTX_TL
1847 xErrLoc := 1100;
1848
1849 FOR i IN 1..gUpdateRootCtxSQL.COUNT LOOP
1850 v_cursor_id := DBMS_SQL.open_cursor;
1851
1852 DBMS_SQL.parse(v_cursor_id, gUpdateRootCtxSQL(i), dbms_sql.native);
1853
1854 IF (i = gUpdateRootCtxSQL.COUNT - 1) THEN
1855 -- This is the <buyid> line
1856 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',10000);
1857 ELSIF (i = gUpdateRootCtxSQL.COUNT) THEN
1858 -- This is the </buyid> line
1859 DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':p_sequence',15000);
1860 ELSE
1861 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence', i);
1862 END IF;
1863
1864 DBMS_SQL.bind_variable(v_cursor_id, ':action_name', v_action);
1865 DBMS_SQL.bind_array(v_cursor_id, ':p_system_action', gSystemActions);
1866 DBMS_SQL.bind_array(v_cursor_id, ':p_item_id', gItemIds);
1867 v_count := DBMS_SQL.execute(v_cursor_id);
1868 DBMS_SQL.close_cursor(v_cursor_id);
1869 END LOOP;
1870
1871 -- BUYER ID NORMALIZATION
1872 -- Inserts the buyer ids for the items
1873 -- This should work for both UPDATE and TRANSLATE
1874 xErrLoc := 1200;
1875
1876 FORALL i IN 1..gRowids.COUNT
1877 INSERT INTO icx_cat_items_ctx_tlp
1878 (rt_item_id, language, sequence, ctx_desc, org_id,
1879 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE,
1880 CREATED_BY, CREATION_DATE)
1881 SELECT gItemIds(i), tl.language, v_sequence,
1882 to_char(pll.org_id), pll.org_id,
1883 gUserLogin, gUserId, sysdate,
1884 gUserId, sysdate
1885 FROM icx_cat_items_tlp tl,
1886 (SELECT DISTINCT org_id
1887 FROM icx_cat_item_prices pll
1888 WHERE rt_item_id = gCurrentItemIds(i)
1889 ) pll
1890 WHERE tl.rt_item_id = gItemIds(i)
1891 AND gSystemActions(i) = v_action;
1892
1893 EXCEPTION
1894 WHEN OTHERS THEN
1895 ROLLBACK;
1896
1897 IF (DBMS_SQL.is_open(v_cursor_id)) THEN
1898 DBMS_SQL.close_cursor(v_cursor_id);
1899 END IF;
1900
1901 RAISE_APPLICATION_ERROR(-20000,
1902 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_update('
1903 || xErrLoc || '): ' || SQLERRM);
1904 END process_batch_update;
1905
1906 /* Processes the item deletions in the current batch */
1907 PROCEDURE process_batch_delete IS
1908 i PLS_INTEGER;
1909 v_action VARCHAR2(10) := 'DELETE';
1910 xErrLoc PLS_INTEGER := 100;
1911 BEGIN
1912
1913 -- ICX_CAT_ITEMS_B
1914 FORALL i in 1..gRowIds.COUNT
1915 DELETE from icx_cat_items_b
1916 WHERE rt_item_id = gItemIds(i)
1917 AND v_action = gSystemActions(i);
1918
1919 xErrLoc := 200;
1920
1921 -- ICX_CAT_ITEMS_TLP
1922 FORALL i in 1..gRowIds.COUNT
1923 DELETE from icx_cat_items_tlp
1924 WHERE rt_item_id = gItemIds(i)
1925 AND v_action = gSystemActions(i);
1926
1927 xErrLoc := 250;
1928 -- ICX_CAT_ITEMS_CTX_TLP
1929 FORALL i in 1..gRowIds.COUNT
1930 DELETE from icx_cat_items_ctx_tlp
1931 WHERE rt_item_id = gItemIds(i)
1932 AND v_action = gSystemActions(i);
1933
1934 xErrLoc := 300;
1935
1936 -- ICX_CAT_EXT_ITEMS_TLP
1937 FORALL i in 1..gRowIds.COUNT
1938 DELETE from icx_cat_ext_items_tlp
1939 WHERE rt_item_id = gItemIds(i)
1940 AND v_action = gSystemActions(i);
1941
1942 xErrLoc := 400;
1943
1944 -- ICX_CAT_CATEGORY_ITEMS
1945 FORALL i in 1..gRowIds.COUNT
1946 DELETE from icx_cat_category_items
1947 WHERE rt_item_id = gItemIds(i)
1948 AND v_action = gSystemActions(i);
1949
1950 xErrLoc := 450;
1951
1952 -- Favorite Lists: POR_FAVORITE_LIST_LINES
1953 FORALL i in 1..gRowIds.COUNT
1954 delete from por_favorite_list_lines
1955 where rt_item_id = gItemIds(i)
1956 AND v_action = gSystemActions(i);
1957
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 ROLLBACK;
1961
1962 RAISE_APPLICATION_ERROR(-20000,
1963 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_delete('
1964 || xErrLoc || '): ' || SQLERRM);
1965 END process_batch_delete;
1966
1967 /* Processes the item translations in the current batch */
1968 PROCEDURE process_batch_translate IS
1969 i PLS_INTEGER;
1970 j PLS_INTEGER;
1971 k PLS_INTEGER;
1972 v_orig_ctx VARCHAR2(10);
1973 v_new_ctx VARCHAR2(10);
1974 v_cursor_id NUMBER;
1975 v_count NUMBER;
1976 v_catid NUMBER;
1977 v_action VARCHAR2(10) := 'TRANSLATE';
1978 v_sequence PLS_INTEGER := 10001;
1979 xErrLoc PLS_INTEGER := 100;
1980 v_delete_ctx_sql VARCHAR2(255);
1981 BEGIN
1982 -- ICX_CAT_ITEMS_TLP: Insert new Row
1983 xErrLoc := 100;
1984 v_cursor_id := DBMS_SQL.open_cursor;
1985 DBMS_SQL.parse(v_cursor_id, gTranslateItemsSQL, DBMS_SQL.NATIVE);
1986 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
1987 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
1988 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
1989 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
1990 DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE', 10);
1991 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
1992 -- DBMS_SQL.bind_variable(v_cursor_id, ':p_language', gJobLanguage);
1993 v_count := DBMS_SQL.execute(v_cursor_id);
1994 DBMS_SQL.close_cursor(v_cursor_id);
1995
1996 xErrLoc := 200;
1997 -- ICX_CAT_ITEMS_TLP: Update the Non-Translated Root descriptors
1998 v_cursor_id := DBMS_SQL.open_cursor;
1999 DBMS_SQL.parse(v_cursor_id, gUpdateItemsNonTLSQL, DBMS_SQL.NATIVE);
2000 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
2001 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
2002 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
2003 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
2004 DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE', 10);
2005 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
2006 v_count := DBMS_SQL.execute(v_cursor_id);
2007 DBMS_SQL.close_cursor(v_cursor_id);
2008
2009 -- Delete the ctx entries for the items that we translate now.
2010 v_delete_ctx_sql := 'delete from icx_cat_items_ctx_tlp where rt_item_id = :rt_item_id and :p_action=:update_action';
2011 v_cursor_id := DBMS_SQL.open_cursor;
2012 DBMS_SQL.parse(v_cursor_id, v_delete_ctx_sql, dbms_sql.native);
2013 DBMS_SQL.bind_array(v_cursor_id, ':rt_item_id', gItemIds);
2014 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
2015 DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE');
2016 v_count := DBMS_SQL.execute(v_cursor_id);
2017 DBMS_SQL.close_cursor(v_cursor_id);
2018 xErrLoc := 300;
2019
2020 xErrLoc := 600;
2021 -- Erase out the existing local descriptor values when category is changed.
2022 -- do it only when there is atleast one local descriptor for the old category
2023 handle_category_change('TRANSLATE');
2024
2025 FOR i IN 1..gDistinctCategoryIds.COUNT LOOP
2026 xErrLoc := 300+i;
2027 v_catid := gDistinctCategoryIds(i);
2028
2029 --Bug#3657792
2030 --Insert into icx_cat_ext_items_tlp should be done even if there are no local descriptors
2031 --So put the cursor gCategoryInfo(v_catid).translateSQL to insert into icx_cat_ext_items_tlp
2032 --outside of the if check (gCategoryInfo(v_catid).descriptor_count > 0)
2033 -- Insert into ICX_CAT_EXT_ITEMS_TLP for the new language
2034 xErrLoc := 500;
2035 v_cursor_id := DBMS_SQL.open_cursor;
2036 DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).translateSQL,
2037 DBMS_SQL.NATIVE);
2038 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
2039 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
2040 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
2041 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
2042 DBMS_SQL.bind_variable(v_cursor_id, ':update_action', 'TRANSLATE', 10);
2043 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
2044 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
2045 v_count := DBMS_SQL.execute(v_cursor_id);
2046 DBMS_SQL.close_cursor(v_cursor_id);
2047
2048 IF (gCategoryInfo(v_catid).descriptor_count > 0) THEN
2049 -- Handle category changes
2050 xErrLoc := 400;
2051
2052 -- Update the Non-Translateable local descriptors for the given category
2053 -- updateNonTLSQL is reused for Translate here
2054 v_cursor_id := DBMS_SQL.open_cursor;
2055 DBMS_SQL.parse(v_cursor_id, gCategoryInfo(v_catid).updateNonTLSQL,
2056 DBMS_SQL.NATIVE);
2057 DBMS_SQL.bind_array(v_cursor_id, ':old_item_id', gCurrentItemIds);
2058 DBMS_SQL.bind_array(v_cursor_id, ':p_rowid', gRowids);
2059 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
2060 DBMS_SQL.bind_variable(v_cursor_id, ':update_action',
2061 'TRANSLATE', 10);
2062 DBMS_SQL.bind_array(v_cursor_id, ':p_action', gSystemActions);
2063 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_id', gUserId);
2064 DBMS_SQL.bind_variable(v_cursor_id, ':p_user_login', gUserLogin);
2065 v_count := DBMS_SQL.execute(v_cursor_id);
2066 DBMS_SQL.close_cursor(v_cursor_id);
2067
2068 IF (gCategoryInfo(v_catid).searchable_desc_count > 0) THEN
2069 xErrLoc := 700;
2070 build_category_ctx_sql(v_catid);
2071
2072 FOR j IN 1..gUpdateCatCtxSQL.COUNT LOOP
2073 v_cursor_id := DBMS_SQL.open_cursor;
2074 DBMS_SQL.parse(v_cursor_id, gUpdateCatCtxSQL(j),
2075 dbms_sql.native);
2076 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence', 5000 + j);
2077 DBMS_SQL.bind_variable(v_cursor_id, ':current_category_id', v_catid);
2078 DBMS_SQL.bind_array(v_cursor_id, ':p_category_id', gCategoryIds);
2079 DBMS_SQL.bind_variable(v_cursor_id, ':action_name', v_action);
2080 DBMS_SQL.bind_array(v_cursor_id, ':p_system_action', gSystemActions);
2081 DBMS_SQL.bind_array(v_cursor_id, ':p_item_id', gItemIds);
2082 v_count := DBMS_SQL.execute(v_cursor_id);
2083 DBMS_SQL.close_cursor(v_cursor_id);
2084 END LOOP;
2085
2086 END IF;
2087
2088 END IF;
2089
2090 END LOOP;
2091
2092 -- ICX_POR_CTX_TL
2093 xErrLoc := 800;
2094
2095 FOR i IN 1..gUpdateRootCtxSQL.COUNT LOOP
2096 v_cursor_id := DBMS_SQL.open_cursor;
2097 DBMS_SQL.parse(v_cursor_id, gUpdateRootCtxSQL(i), dbms_sql.native);
2098
2099 IF (i = gUpdateRootCtxSQL.COUNT - 1) THEN
2100 -- This is the <buyid> line
2101 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',10000);
2102 ELSIF (i = gUpdateRootCtxSQL.COUNT) THEN
2103 -- This is the </buyid> line
2104 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence',15000);
2105 ELSE
2106 DBMS_SQL.bind_variable(v_cursor_id, ':p_sequence', i);
2107 END IF;
2108
2109 DBMS_SQL.bind_variable(v_cursor_id, ':action_name', v_action);
2110 DBMS_SQL.bind_array(v_cursor_id, ':p_system_action', gSystemActions);
2111 DBMS_SQL.bind_array(v_cursor_id, ':p_item_id', gItemIds);
2112 v_count := DBMS_SQL.execute(v_cursor_id);
2113 DBMS_SQL.close_cursor(v_cursor_id);
2114 END LOOP;
2115
2116 -- BUYER ID NORMALIZATION
2117 -- Inserts the buyer ids for the items
2118 -- This should work for both UPDATE and TRANSLATE
2119 xErrLoc := 900;
2120
2121 FORALL i IN 1..gRowids.COUNT
2122 INSERT INTO icx_cat_items_ctx_tlp
2123 (rt_item_id, language, sequence, ctx_desc, org_id)
2124 SELECT gItemIds(i), tl.language, v_sequence,
2125 to_char(pll.org_id), pll.org_id
2126 FROM icx_cat_items_tlp tl,
2127 (SELECT DISTINCT org_id
2128 FROM icx_cat_item_prices pll
2129 WHERE rt_item_id = gCurrentItemIds(i)
2130 ) pll
2131 WHERE tl.rt_item_id = gItemIds(i)
2132 AND gSystemActions(i) = v_action;
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 ROLLBACK;
2136
2137 IF (DBMS_SQL.is_open(v_cursor_id)) THEN
2138 DBMS_SQL.close_cursor(v_cursor_id);
2139 END IF;
2140
2141 RAISE_APPLICATION_ERROR(-20000,
2142 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_translate('
2143 || xErrLoc || '): ' || SQLERRM);
2144 END process_batch_translate;
2145
2146 /* Processes the current price batch*/
2147 PROCEDURE process_batch_prices(pHasAdd IN BOOLEAN, pHasDelete IN BOOLEAN, pNumFailedLines IN OUT NOCOPY NUMBER) IS
2148 xErrLoc PLS_INTEGER := 100;
2149 BEGIN
2150
2151 xErrLoc := 200;
2152 IF (pHasAdd) THEN
2153 xErrLoc := 300;
2154 process_batch_addupdate_prices('PRICE', 'ADD', pNumFailedLines);
2155 END IF;
2156
2157 IF (pHasDelete) THEN
2158 xErrLoc := 500;
2159 process_batch_delete_prices('PRICE');
2160 END IF;
2161
2162 EXCEPTION
2163 WHEN OTHERS THEN
2164 ROLLBACK;
2165
2166 RAISE_APPLICATION_ERROR(-20000,
2167 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_prices('
2168 || xErrLoc || '): ' || SQLERRM);
2169 END process_batch_prices;
2170
2171 /* Processes the current item batch */
2172 PROCEDURE process_batch_items(pHasAdd IN BOOLEAN,
2173 pHasUpdate IN BOOLEAN, pHasDelete IN BOOLEAN,
2174 pHasTranslate IN BOOLEAN, pHasPrices IN BOOLEAN) IS
2175 xErrLoc PLS_INTEGER := 100;
2176
2177 BEGIN
2178
2179 xErrLoc := 100;
2180 load_distinct_categories;
2181
2182 xErrLoc := 200;
2183
2184 process_batch_common;
2185
2186 IF (pHasAdd) THEN
2187 xErrLoc := 600;
2188 process_batch_add;
2189
2190 IF (pHasPrices) THEN
2191 xErrLoc := 700;
2192 process_batch_add_prices('ITEM_PRICE');
2193 END IF;
2194
2195 END IF;
2196
2197 IF (pHasUpdate) THEN
2198 xErrLoc := 800;
2199 process_batch_update;
2200
2201 IF (pHasPrices) THEN
2202 xErrLoc := 900;
2203 process_batch_addupdate_prices('ITEM_PRICE','UPDATE');
2204 END IF;
2205
2206 END IF;
2207 commit;
2208
2209 IF (pHasTranslate) THEN
2210 xErrLoc := 1000;
2211 process_batch_translate;
2212
2213 IF (pHasPrices) THEN
2214 xErrLoc := 1100;
2215 process_batch_addupdate_prices('ITEM_PRICE','TRANSLATE');
2216 END IF;
2217
2218 END IF;
2219
2220 IF (pHasDelete) THEN
2221 xErrLoc := 1200;
2222 process_batch_delete;
2223
2224 IF (pHasPrices) THEN
2225 xErrLoc := 1300;
2226 process_batch_delete_prices('ITEM_PRICE');
2227 END IF;
2228
2229 END IF;
2230
2231 EXCEPTION
2232 WHEN OTHERS THEN
2233 ROLLBACK;
2234
2235 RAISE_APPLICATION_ERROR(-20000,
2236 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_items('
2237 || xErrLoc || '): ' || SQLERRM);
2238 END process_batch_items;
2239
2240 /**
2241 ** Proc : save_failed_line_message
2242 ** Desc : Parse the error_message of Interface table, the format
2243 ** is '.KEY1:MESSAGE1.KEY2:MESSAGE2.KEY3:MESSAGE3......',
2244 ** insert these pairs of error messages into table
2245 ** icx_por_failed_line_messages.
2246 **/
2247 PROCEDURE save_failed_line_message (p_request_id NUMBER,
2248 p_line_number NUMBER, p_error_message VARCHAR2) IS
2249 xErrLoc INTEGER := 0;
2250 xLocation number := 0;
2251 xStart number := 1;
2252 xString varchar2(200) := '';
2253 xIndex number := 0;
2254 xKey varchar2(100) := '';
2255 xMessage varchar2(100) := '';
2256 BEGIN
2257 xErrLoc := 100;
2258
2259 if (p_error_message is null) then
2260 return;
2261 end if;
2262
2263 LOOP
2264 xLocation := instr(p_error_message, '.', xStart);
2265
2266 xErrLoc := 150;
2267
2268 if (xLocation = 0) then
2269 xString := substr(p_error_message, xStart);
2270 else
2271 xString := substr(p_error_message, xStart, xLocation-xStart);
2272 end if;
2273 if (xString is null) then
2274 xIndex := 0;
2275 else
2276 xIndex := instr(xString, ':');
2277 end if;
2278
2279 xErrLoc := 200;
2280
2281 if (xIndex <> 0) then
2282 xErrLoc := 300;
2283
2284 xKey := substr(xString, 1, xIndex-1);
2285 xMessage := substr(xString, xIndex+1);
2286 xErrLoc := 350;
2287
2288 insert into icx_por_failed_line_messages
2289 (job_number, line_number, descriptor_key, message_name)
2290 values
2291 (p_request_id, p_line_number, xKey, xMessage);
2292
2293 end if;
2294
2295 xStart := xLocation + 1;
2296
2297 if (xLocation = 0) then
2298 EXIT;
2299 end if;
2300
2301 END LOOP;
2302 commit;
2303
2304 xErrLoc := 400;
2305
2306 EXCEPTION
2307 WHEN OTHERS THEN
2308 ROLLBACK;
2309
2310 RAISE_APPLICATION_ERROR(-20000,
2311 'Exception at ICX_POR_ITEM_UPLOAD.save_failed_line_message('
2312 || xErrLoc || '): ' || SQLERRM);
2313
2314 END save_failed_line_message;
2315
2316 /* Retrieves attributes for a given category */
2317 PROCEDURE fetch_local_descriptors(pCategoryId IN NUMBER) IS
2318 i pls_integer := 0;
2319 CURSOR local_descriptors_cr(v_category_id NUMBER) IS
2320 select d.key
2321 from icx_cat_descriptors_tl d
2322 where d.rt_category_id = v_category_id
2323 -- and d.class in ('POM_CAT_ATTR')
2324 and d.language = gJobLanguage
2325 order by d.rt_descriptor_id;
2326 xErrLoc PLS_INTEGER := 100;
2327 BEGIN
2328
2329 IF (gErrorCatId <> pCategoryId) THEN
2330 gErrorCatId := pCategoryId;
2331 gErrorCatDescKeys.DELETE;
2332
2333 FOR rec IN local_descriptors_cr(pCategoryId) LOOP
2334 i := i + 1;
2335 gErrorCatDescKeys(i) := rec.key;
2336 END LOOP;
2337
2338 END IF;
2339
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 ROLLBACK;
2343
2344 RAISE_APPLICATION_ERROR(-20000,
2345 'Exception at ICX_POR_ITEM_UPLOAD.fetch_local_descriptors('
2346 || xErrLoc || '): ' || SQLERRM);
2347 END fetch_local_descriptors;
2348
2349 /* Saves a failed item */
2350 PROCEDURE save_failed_item(pJobNumber IN NUMBER,
2351 p_failed_item IN ICX_CAT_ITEMS_GT%ROWTYPE) IS
2352 v_rec tITRowRecord;
2353 i PLS_INTEGER;
2354 xErrLoc PLS_INTEGER;
2355 vDescKey ICX_POR_FAILED_LINES.DESCRIPTOR_KEY%TYPE;
2356 --Bug#3396442
2357 v_section_tag PLS_INTEGER;
2358 CURSOR custom_non_price_desc_cr(p_category_id NUMBER) IS
2359 select rt_descriptor_id, type, key,
2360 section_tag, stored_in_column, stored_in_table,
2361 searchable
2362 from icx_cat_descriptors_tl
2363 where rt_category_id = p_category_id
2364 and language = gJobLanguage
2365 and stored_in_table = 'ICX_CAT_ITEMS_TLP'
2366 and rt_descriptor_id >= 100000
2367 order by rt_descriptor_id;
2368 BEGIN
2369 xErrLoc := 100;
2370 -- First convert p_failed_item into v_rec for easier manipulation
2371 v_rec.line_number := p_failed_item.line_number;
2372 v_rec.row_type := p_failed_item.row_type;
2373 v_rec.processed_flag := p_failed_item.processed_flag;
2374 v_rec.language := p_failed_item.language;
2375 v_rec.action := p_failed_item.action;
2376 v_rec.system_action := p_failed_item.system_action;
2377 --bug#3593728
2378 --In some cases if system_action is null then replace it with action
2379 --this system_action is only used to insert into icx_por_failed_lines table
2380 IF ( v_rec.system_action IS NULL ) THEN
2381 xErrLoc := 110;
2382 icx_por_ext_utl.debug(icx_por_ext_utl.MUST_LEVEL,
2383 'system_action is null in save_failed_item;' ||
2384 ' v_rec.action:' ||v_rec.action||
2385 ', v_rec.system_action:' ||v_rec.system_action||
2386 ', v_rec.error_flag:' ||v_rec.error_flag||
2387 ', v_rec.supplier_part_num:' ||v_rec.supplier_part_num||
2388 ', v_rec.error_message:' ||v_rec.error_message);
2389 xErrLoc := 120;
2390 v_rec.system_action := v_rec.action;
2391 END IF;
2392 v_rec.error_flag := p_failed_item.error_flag;
2393 v_rec.error_message := p_failed_item.error_message;
2394 v_rec.required_descriptors := p_failed_item.required_descriptors;
2395 v_rec.required_tl_descriptors := p_failed_item.required_tl_descriptors;
2396 v_rec.has_cat_attribute_flag := p_failed_item.has_cat_attribute_flag;
2397 v_rec.rt_category_id := p_failed_item.rt_category_id;
2398 v_rec.category_name := p_failed_item.category_name;
2399 v_rec.org_id := p_failed_item.org_id;
2400 v_rec.supplier_id := p_failed_item.supplier_id;
2401 v_rec.supplier := p_failed_item.supplier;
2402 v_rec.supplier_part_num := p_failed_item.supplier_part_num;
2403 v_rec.supplier_part_auxid := p_failed_item.supplier_part_auxid;
2404 v_rec.manufacturer := p_failed_item.manufacturer;
2405 v_rec.manufacturer_part_num := p_failed_item.manufacturer_part_num;
2406 v_rec.description := p_failed_item.description;
2407 v_rec.comments := p_failed_item.comments;
2408 v_rec.alias := p_failed_item.alias;
2409 v_rec.picture := p_failed_item.picture;
2410 v_rec.picture_url := p_failed_item.picture_url;
2411 v_rec.thumbnail_image := p_failed_item.thumbnail_image;
2412 v_rec.attachment_url := p_failed_item.attachment_url;
2413 v_rec.supplier_url := p_failed_item.supplier_url;
2414 v_rec.manufacturer_url := p_failed_item.manufacturer_url;
2415 v_rec.long_description := p_failed_item.long_description;
2416 v_rec.unspsc_code := p_failed_item.unspsc_code;
2417 v_rec.availability := p_failed_item.availability;
2418 v_rec.lead_time := p_failed_item.lead_time;
2419 v_rec.item_type := p_failed_item.item_type;
2420 v_rec.contract_num := p_failed_item.contract_num;
2421 v_rec.contract_id := p_failed_item.contract_id;
2422 v_rec.roots(1) := p_failed_item.text_base_attribute1;
2423 v_rec.roots(2) := p_failed_item.text_base_attribute2;
2424 v_rec.roots(3) := p_failed_item.text_base_attribute3;
2425 v_rec.roots(4) := p_failed_item.text_base_attribute4;
2426 v_rec.roots(5) := p_failed_item.text_base_attribute5;
2427 v_rec.roots(6) := p_failed_item.text_base_attribute6;
2428 v_rec.roots(7) := p_failed_item.text_base_attribute7;
2429 v_rec.roots(8) := p_failed_item.text_base_attribute8;
2430 v_rec.roots(9) := p_failed_item.text_base_attribute9;
2431 v_rec.roots(10) := p_failed_item.text_base_attribute10;
2432 v_rec.roots(11) := p_failed_item.text_base_attribute11;
2433 v_rec.roots(12) := p_failed_item.text_base_attribute12;
2434 v_rec.roots(13) := p_failed_item.text_base_attribute13;
2435 v_rec.roots(14) := p_failed_item.text_base_attribute14;
2436 v_rec.roots(15) := p_failed_item.text_base_attribute15;
2437 v_rec.roots(16) := p_failed_item.text_base_attribute16;
2438 v_rec.roots(17) := p_failed_item.text_base_attribute17;
2439 v_rec.roots(18) := p_failed_item.text_base_attribute18;
2440 v_rec.roots(19) := p_failed_item.text_base_attribute19;
2441 v_rec.roots(20) := p_failed_item.text_base_attribute20;
2442 v_rec.roots(21) := p_failed_item.text_base_attribute21;
2443 v_rec.roots(22) := p_failed_item.text_base_attribute22;
2444 v_rec.roots(23) := p_failed_item.text_base_attribute23;
2445 v_rec.roots(24) := p_failed_item.text_base_attribute24;
2446 v_rec.roots(25) := p_failed_item.text_base_attribute25;
2447 v_rec.roots(26) := p_failed_item.text_base_attribute26;
2448 v_rec.roots(27) := p_failed_item.text_base_attribute27;
2449 v_rec.roots(28) := p_failed_item.text_base_attribute28;
2450 v_rec.roots(29) := p_failed_item.text_base_attribute29;
2451 v_rec.roots(30) := p_failed_item.text_base_attribute30;
2452 v_rec.roots(31) := p_failed_item.text_base_attribute31;
2453 v_rec.roots(32) := p_failed_item.text_base_attribute32;
2454 v_rec.roots(33) := p_failed_item.text_base_attribute33;
2455 v_rec.roots(34) := p_failed_item.text_base_attribute34;
2456 v_rec.roots(35) := p_failed_item.text_base_attribute35;
2457 v_rec.roots(36) := p_failed_item.text_base_attribute36;
2458 v_rec.roots(37) := p_failed_item.text_base_attribute37;
2459 v_rec.roots(38) := p_failed_item.text_base_attribute38;
2460 v_rec.roots(39) := p_failed_item.text_base_attribute39;
2461 v_rec.roots(40) := p_failed_item.text_base_attribute40;
2462 v_rec.roots(41) := p_failed_item.text_base_attribute41;
2463 v_rec.roots(42) := p_failed_item.text_base_attribute42;
2464 v_rec.roots(43) := p_failed_item.text_base_attribute43;
2465 v_rec.roots(44) := p_failed_item.text_base_attribute44;
2466 v_rec.roots(45) := p_failed_item.text_base_attribute45;
2467 v_rec.roots(46) := p_failed_item.text_base_attribute46;
2468 v_rec.roots(47) := p_failed_item.text_base_attribute47;
2469 v_rec.roots(48) := p_failed_item.text_base_attribute48;
2470 v_rec.roots(49) := p_failed_item.text_base_attribute49;
2471 v_rec.roots(50) := p_failed_item.text_base_attribute50;
2472 v_rec.roots(51) := p_failed_item.text_base_attribute51;
2473 v_rec.roots(52) := p_failed_item.text_base_attribute52;
2474 v_rec.roots(53) := p_failed_item.text_base_attribute53;
2475 v_rec.roots(54) := p_failed_item.text_base_attribute54;
2476 v_rec.roots(55) := p_failed_item.text_base_attribute55;
2477 v_rec.roots(56) := p_failed_item.text_base_attribute56;
2478 v_rec.roots(57) := p_failed_item.text_base_attribute57;
2479 v_rec.roots(58) := p_failed_item.text_base_attribute58;
2480 v_rec.roots(59) := p_failed_item.text_base_attribute59;
2481 v_rec.roots(60) := p_failed_item.text_base_attribute60;
2482 v_rec.roots(61) := p_failed_item.text_base_attribute61;
2483 v_rec.roots(62) := p_failed_item.text_base_attribute62;
2484 v_rec.roots(63) := p_failed_item.text_base_attribute63;
2485 v_rec.roots(64) := p_failed_item.text_base_attribute64;
2486 v_rec.roots(65) := p_failed_item.text_base_attribute65;
2487 v_rec.roots(66) := p_failed_item.text_base_attribute66;
2488 v_rec.roots(67) := p_failed_item.text_base_attribute67;
2489 v_rec.roots(68) := p_failed_item.text_base_attribute68;
2490 v_rec.roots(69) := p_failed_item.text_base_attribute69;
2491 v_rec.roots(70) := p_failed_item.text_base_attribute70;
2492 v_rec.roots(71) := p_failed_item.text_base_attribute71;
2493 v_rec.roots(72) := p_failed_item.text_base_attribute72;
2494 v_rec.roots(73) := p_failed_item.text_base_attribute73;
2495 v_rec.roots(74) := p_failed_item.text_base_attribute74;
2496 v_rec.roots(75) := p_failed_item.text_base_attribute75;
2497 v_rec.roots(76) := p_failed_item.text_base_attribute76;
2498 v_rec.roots(77) := p_failed_item.text_base_attribute77;
2499 v_rec.roots(78) := p_failed_item.text_base_attribute78;
2500 v_rec.roots(79) := p_failed_item.text_base_attribute79;
2501 v_rec.roots(80) := p_failed_item.text_base_attribute80;
2502 v_rec.roots(81) := p_failed_item.text_base_attribute81;
2503 v_rec.roots(82) := p_failed_item.text_base_attribute82;
2504 v_rec.roots(83) := p_failed_item.text_base_attribute83;
2505 v_rec.roots(84) := p_failed_item.text_base_attribute84;
2506 v_rec.roots(85) := p_failed_item.text_base_attribute85;
2507 v_rec.roots(86) := p_failed_item.text_base_attribute86;
2508 v_rec.roots(87) := p_failed_item.text_base_attribute87;
2509 v_rec.roots(88) := p_failed_item.text_base_attribute88;
2510 v_rec.roots(89) := p_failed_item.text_base_attribute89;
2511 v_rec.roots(90) := p_failed_item.text_base_attribute90;
2512 v_rec.roots(91) := p_failed_item.text_base_attribute91;
2513 v_rec.roots(92) := p_failed_item.text_base_attribute92;
2514 v_rec.roots(93) := p_failed_item.text_base_attribute93;
2515 v_rec.roots(94) := p_failed_item.text_base_attribute94;
2516 v_rec.roots(95) := p_failed_item.text_base_attribute95;
2517 v_rec.roots(96) := p_failed_item.text_base_attribute96;
2518 v_rec.roots(97) := p_failed_item.text_base_attribute97;
2519 v_rec.roots(98) := p_failed_item.text_base_attribute98;
2520 v_rec.roots(99) := p_failed_item.text_base_attribute99;
2521 v_rec.roots(100) := p_failed_item.text_base_attribute100;
2522 --Bug#2785949
2523 --Removed the to_char function as
2524 --p_failed_item.num_base_attribute1..p_failed_item.num_base_attribute100
2525 --is already VARCHAR type
2526 v_rec.roots(101) := p_failed_item.num_base_attribute1;
2527 v_rec.roots(102) := p_failed_item.num_base_attribute2;
2528 v_rec.roots(103) := p_failed_item.num_base_attribute3;
2529 v_rec.roots(104) := p_failed_item.num_base_attribute4;
2530 v_rec.roots(105) := p_failed_item.num_base_attribute5;
2531 v_rec.roots(106) := p_failed_item.num_base_attribute6;
2532 v_rec.roots(107) := p_failed_item.num_base_attribute7;
2533 v_rec.roots(108) := p_failed_item.num_base_attribute8;
2534 v_rec.roots(109) := p_failed_item.num_base_attribute9;
2535 v_rec.roots(110) := p_failed_item.num_base_attribute10;
2536 v_rec.roots(111) := p_failed_item.num_base_attribute11;
2537 v_rec.roots(112) := p_failed_item.num_base_attribute12;
2538 v_rec.roots(113) := p_failed_item.num_base_attribute13;
2539 v_rec.roots(114) := p_failed_item.num_base_attribute14;
2540 v_rec.roots(115) := p_failed_item.num_base_attribute15;
2541 v_rec.roots(116) := p_failed_item.num_base_attribute16;
2542 v_rec.roots(117) := p_failed_item.num_base_attribute17;
2543 v_rec.roots(118) := p_failed_item.num_base_attribute18;
2544 v_rec.roots(119) := p_failed_item.num_base_attribute19;
2545 v_rec.roots(120) := p_failed_item.num_base_attribute20;
2546 v_rec.roots(121) := p_failed_item.num_base_attribute21;
2547 v_rec.roots(122) := p_failed_item.num_base_attribute22;
2548 v_rec.roots(123) := p_failed_item.num_base_attribute23;
2549 v_rec.roots(124) := p_failed_item.num_base_attribute24;
2550 v_rec.roots(125) := p_failed_item.num_base_attribute25;
2551 v_rec.roots(126) := p_failed_item.num_base_attribute26;
2552 v_rec.roots(127) := p_failed_item.num_base_attribute27;
2553 v_rec.roots(128) := p_failed_item.num_base_attribute28;
2554 v_rec.roots(129) := p_failed_item.num_base_attribute29;
2555 v_rec.roots(130) := p_failed_item.num_base_attribute30;
2556 v_rec.roots(131) := p_failed_item.num_base_attribute31;
2557 v_rec.roots(132) := p_failed_item.num_base_attribute32;
2558 v_rec.roots(133) := p_failed_item.num_base_attribute33;
2559 v_rec.roots(134) := p_failed_item.num_base_attribute34;
2560 v_rec.roots(135) := p_failed_item.num_base_attribute35;
2561 v_rec.roots(136) := p_failed_item.num_base_attribute36;
2562 v_rec.roots(137) := p_failed_item.num_base_attribute37;
2563 v_rec.roots(138) := p_failed_item.num_base_attribute38;
2564 v_rec.roots(139) := p_failed_item.num_base_attribute39;
2565 v_rec.roots(140) := p_failed_item.num_base_attribute40;
2566 v_rec.roots(141) := p_failed_item.num_base_attribute41;
2567 v_rec.roots(142) := p_failed_item.num_base_attribute42;
2568 v_rec.roots(143) := p_failed_item.num_base_attribute43;
2569 v_rec.roots(144) := p_failed_item.num_base_attribute44;
2570 v_rec.roots(145) := p_failed_item.num_base_attribute45;
2571 v_rec.roots(146) := p_failed_item.num_base_attribute46;
2572 v_rec.roots(147) := p_failed_item.num_base_attribute47;
2573 v_rec.roots(148) := p_failed_item.num_base_attribute48;
2574 v_rec.roots(149) := p_failed_item.num_base_attribute49;
2575 v_rec.roots(150) := p_failed_item.num_base_attribute50;
2576 v_rec.roots(151) := p_failed_item.num_base_attribute51;
2577 v_rec.roots(152) := p_failed_item.num_base_attribute52;
2578 v_rec.roots(153) := p_failed_item.num_base_attribute53;
2579 v_rec.roots(154) := p_failed_item.num_base_attribute54;
2580 v_rec.roots(155) := p_failed_item.num_base_attribute55;
2581 v_rec.roots(156) := p_failed_item.num_base_attribute56;
2582 v_rec.roots(157) := p_failed_item.num_base_attribute57;
2583 v_rec.roots(158) := p_failed_item.num_base_attribute58;
2584 v_rec.roots(159) := p_failed_item.num_base_attribute59;
2585 v_rec.roots(160) := p_failed_item.num_base_attribute60;
2586 v_rec.roots(161) := p_failed_item.num_base_attribute61;
2587 v_rec.roots(162) := p_failed_item.num_base_attribute62;
2588 v_rec.roots(163) := p_failed_item.num_base_attribute63;
2589 v_rec.roots(164) := p_failed_item.num_base_attribute64;
2590 v_rec.roots(165) := p_failed_item.num_base_attribute65;
2591 v_rec.roots(166) := p_failed_item.num_base_attribute66;
2592 v_rec.roots(167) := p_failed_item.num_base_attribute67;
2593 v_rec.roots(168) := p_failed_item.num_base_attribute68;
2594 v_rec.roots(169) := p_failed_item.num_base_attribute69;
2595 v_rec.roots(170) := p_failed_item.num_base_attribute70;
2596 v_rec.roots(171) := p_failed_item.num_base_attribute71;
2597 v_rec.roots(172) := p_failed_item.num_base_attribute72;
2598 v_rec.roots(173) := p_failed_item.num_base_attribute73;
2599 v_rec.roots(174) := p_failed_item.num_base_attribute74;
2600 v_rec.roots(175) := p_failed_item.num_base_attribute75;
2601 v_rec.roots(176) := p_failed_item.num_base_attribute76;
2602 v_rec.roots(177) := p_failed_item.num_base_attribute77;
2603 v_rec.roots(178) := p_failed_item.num_base_attribute78;
2604 v_rec.roots(179) := p_failed_item.num_base_attribute79;
2605 v_rec.roots(180) := p_failed_item.num_base_attribute80;
2606 v_rec.roots(181) := p_failed_item.num_base_attribute81;
2607 v_rec.roots(182) := p_failed_item.num_base_attribute82;
2608 v_rec.roots(183) := p_failed_item.num_base_attribute83;
2609 v_rec.roots(184) := p_failed_item.num_base_attribute84;
2610 v_rec.roots(185) := p_failed_item.num_base_attribute85;
2611 v_rec.roots(186) := p_failed_item.num_base_attribute86;
2612 v_rec.roots(187) := p_failed_item.num_base_attribute87;
2613 v_rec.roots(188) := p_failed_item.num_base_attribute88;
2614 v_rec.roots(189) := p_failed_item.num_base_attribute89;
2615 v_rec.roots(190) := p_failed_item.num_base_attribute90;
2616 v_rec.roots(191) := p_failed_item.num_base_attribute91;
2617 v_rec.roots(192) := p_failed_item.num_base_attribute92;
2618 v_rec.roots(193) := p_failed_item.num_base_attribute93;
2619 v_rec.roots(194) := p_failed_item.num_base_attribute94;
2620 v_rec.roots(195) := p_failed_item.num_base_attribute95;
2621 v_rec.roots(196) := p_failed_item.num_base_attribute96;
2622 v_rec.roots(197) := p_failed_item.num_base_attribute97;
2623 v_rec.roots(198) := p_failed_item.num_base_attribute98;
2624 v_rec.roots(199) := p_failed_item.num_base_attribute99;
2625 v_rec.roots(200) := p_failed_item.num_base_attribute100;
2626 v_rec.roots(201) := p_failed_item.tl_text_base_attribute1;
2627 v_rec.roots(202) := p_failed_item.tl_text_base_attribute2;
2628 v_rec.roots(203) := p_failed_item.tl_text_base_attribute3;
2629 v_rec.roots(204) := p_failed_item.tl_text_base_attribute4;
2630 v_rec.roots(205) := p_failed_item.tl_text_base_attribute5;
2631 v_rec.roots(206) := p_failed_item.tl_text_base_attribute6;
2632 v_rec.roots(207) := p_failed_item.tl_text_base_attribute7;
2633 v_rec.roots(208) := p_failed_item.tl_text_base_attribute8;
2634 v_rec.roots(209) := p_failed_item.tl_text_base_attribute9;
2635 v_rec.roots(210) := p_failed_item.tl_text_base_attribute10;
2636 v_rec.roots(211) := p_failed_item.tl_text_base_attribute11;
2637 v_rec.roots(212) := p_failed_item.tl_text_base_attribute12;
2638 v_rec.roots(213) := p_failed_item.tl_text_base_attribute13;
2639 v_rec.roots(214) := p_failed_item.tl_text_base_attribute14;
2640 v_rec.roots(215) := p_failed_item.tl_text_base_attribute15;
2641 v_rec.roots(216) := p_failed_item.tl_text_base_attribute16;
2642 v_rec.roots(217) := p_failed_item.tl_text_base_attribute17;
2643 v_rec.roots(218) := p_failed_item.tl_text_base_attribute18;
2644 v_rec.roots(219) := p_failed_item.tl_text_base_attribute19;
2645 v_rec.roots(220) := p_failed_item.tl_text_base_attribute20;
2646 v_rec.roots(221) := p_failed_item.tl_text_base_attribute21;
2647 v_rec.roots(222) := p_failed_item.tl_text_base_attribute22;
2648 v_rec.roots(223) := p_failed_item.tl_text_base_attribute23;
2649 v_rec.roots(224) := p_failed_item.tl_text_base_attribute24;
2650 v_rec.roots(225) := p_failed_item.tl_text_base_attribute25;
2651 v_rec.roots(226) := p_failed_item.tl_text_base_attribute26;
2652 v_rec.roots(227) := p_failed_item.tl_text_base_attribute27;
2653 v_rec.roots(228) := p_failed_item.tl_text_base_attribute28;
2654 v_rec.roots(229) := p_failed_item.tl_text_base_attribute29;
2655 v_rec.roots(230) := p_failed_item.tl_text_base_attribute30;
2656 v_rec.roots(231) := p_failed_item.tl_text_base_attribute31;
2657 v_rec.roots(232) := p_failed_item.tl_text_base_attribute32;
2658 v_rec.roots(233) := p_failed_item.tl_text_base_attribute33;
2659 v_rec.roots(234) := p_failed_item.tl_text_base_attribute34;
2660 v_rec.roots(235) := p_failed_item.tl_text_base_attribute35;
2661 v_rec.roots(236) := p_failed_item.tl_text_base_attribute36;
2662 v_rec.roots(237) := p_failed_item.tl_text_base_attribute37;
2663 v_rec.roots(238) := p_failed_item.tl_text_base_attribute38;
2664 v_rec.roots(239) := p_failed_item.tl_text_base_attribute39;
2665 v_rec.roots(240) := p_failed_item.tl_text_base_attribute40;
2666 v_rec.roots(241) := p_failed_item.tl_text_base_attribute41;
2667 v_rec.roots(242) := p_failed_item.tl_text_base_attribute42;
2668 v_rec.roots(243) := p_failed_item.tl_text_base_attribute43;
2669 v_rec.roots(244) := p_failed_item.tl_text_base_attribute44;
2670 v_rec.roots(245) := p_failed_item.tl_text_base_attribute45;
2671 v_rec.roots(246) := p_failed_item.tl_text_base_attribute46;
2672 v_rec.roots(247) := p_failed_item.tl_text_base_attribute47;
2673 v_rec.roots(248) := p_failed_item.tl_text_base_attribute48;
2674 v_rec.roots(249) := p_failed_item.tl_text_base_attribute49;
2675 v_rec.roots(250) := p_failed_item.tl_text_base_attribute50;
2676 v_rec.roots(251) := p_failed_item.tl_text_base_attribute51;
2677 v_rec.roots(252) := p_failed_item.tl_text_base_attribute52;
2678 v_rec.roots(253) := p_failed_item.tl_text_base_attribute53;
2679 v_rec.roots(254) := p_failed_item.tl_text_base_attribute54;
2680 v_rec.roots(255) := p_failed_item.tl_text_base_attribute55;
2681 v_rec.roots(256) := p_failed_item.tl_text_base_attribute56;
2682 v_rec.roots(257) := p_failed_item.tl_text_base_attribute57;
2683 v_rec.roots(258) := p_failed_item.tl_text_base_attribute58;
2684 v_rec.roots(259) := p_failed_item.tl_text_base_attribute59;
2685 v_rec.roots(260) := p_failed_item.tl_text_base_attribute60;
2686 v_rec.roots(261) := p_failed_item.tl_text_base_attribute61;
2687 v_rec.roots(262) := p_failed_item.tl_text_base_attribute62;
2688 v_rec.roots(263) := p_failed_item.tl_text_base_attribute63;
2689 v_rec.roots(264) := p_failed_item.tl_text_base_attribute64;
2690 v_rec.roots(265) := p_failed_item.tl_text_base_attribute65;
2691 v_rec.roots(266) := p_failed_item.tl_text_base_attribute66;
2692 v_rec.roots(267) := p_failed_item.tl_text_base_attribute67;
2693 v_rec.roots(268) := p_failed_item.tl_text_base_attribute68;
2694 v_rec.roots(269) := p_failed_item.tl_text_base_attribute69;
2695 v_rec.roots(270) := p_failed_item.tl_text_base_attribute70;
2696 v_rec.roots(271) := p_failed_item.tl_text_base_attribute71;
2697 v_rec.roots(272) := p_failed_item.tl_text_base_attribute72;
2698 v_rec.roots(273) := p_failed_item.tl_text_base_attribute73;
2699 v_rec.roots(274) := p_failed_item.tl_text_base_attribute74;
2700 v_rec.roots(275) := p_failed_item.tl_text_base_attribute75;
2701 v_rec.roots(276) := p_failed_item.tl_text_base_attribute76;
2702 v_rec.roots(277) := p_failed_item.tl_text_base_attribute77;
2703 v_rec.roots(278) := p_failed_item.tl_text_base_attribute78;
2704 v_rec.roots(279) := p_failed_item.tl_text_base_attribute79;
2705 v_rec.roots(280) := p_failed_item.tl_text_base_attribute80;
2706 v_rec.roots(281) := p_failed_item.tl_text_base_attribute81;
2707 v_rec.roots(282) := p_failed_item.tl_text_base_attribute82;
2708 v_rec.roots(283) := p_failed_item.tl_text_base_attribute83;
2709 v_rec.roots(284) := p_failed_item.tl_text_base_attribute84;
2710 v_rec.roots(285) := p_failed_item.tl_text_base_attribute85;
2711 v_rec.roots(286) := p_failed_item.tl_text_base_attribute86;
2712 v_rec.roots(287) := p_failed_item.tl_text_base_attribute87;
2713 v_rec.roots(288) := p_failed_item.tl_text_base_attribute88;
2714 v_rec.roots(289) := p_failed_item.tl_text_base_attribute89;
2715 v_rec.roots(290) := p_failed_item.tl_text_base_attribute90;
2716 v_rec.roots(291) := p_failed_item.tl_text_base_attribute91;
2717 v_rec.roots(292) := p_failed_item.tl_text_base_attribute92;
2718 v_rec.roots(293) := p_failed_item.tl_text_base_attribute93;
2719 v_rec.roots(294) := p_failed_item.tl_text_base_attribute94;
2720 v_rec.roots(295) := p_failed_item.tl_text_base_attribute95;
2721 v_rec.roots(296) := p_failed_item.tl_text_base_attribute96;
2722 v_rec.roots(297) := p_failed_item.tl_text_base_attribute97;
2723 v_rec.roots(298) := p_failed_item.tl_text_base_attribute98;
2724 v_rec.roots(299) := p_failed_item.tl_text_base_attribute99;
2725 v_rec.roots(300) := p_failed_item.tl_text_base_attribute100;
2726 v_rec.locals(1) := p_failed_item.text_cat_attribute1;
2727 v_rec.locals(2) := p_failed_item.text_cat_attribute2;
2728 v_rec.locals(3) := p_failed_item.text_cat_attribute3;
2729 v_rec.locals(4) := p_failed_item.text_cat_attribute4;
2730 v_rec.locals(5) := p_failed_item.text_cat_attribute5;
2731 v_rec.locals(6) := p_failed_item.text_cat_attribute6;
2732 v_rec.locals(7) := p_failed_item.text_cat_attribute7;
2733 v_rec.locals(8) := p_failed_item.text_cat_attribute8;
2734 v_rec.locals(9) := p_failed_item.text_cat_attribute9;
2735 v_rec.locals(10) := p_failed_item.text_cat_attribute10;
2736 v_rec.locals(11) := p_failed_item.text_cat_attribute11;
2737 v_rec.locals(12) := p_failed_item.text_cat_attribute12;
2738 v_rec.locals(13) := p_failed_item.text_cat_attribute13;
2739 v_rec.locals(14) := p_failed_item.text_cat_attribute14;
2740 v_rec.locals(15) := p_failed_item.text_cat_attribute15;
2741 v_rec.locals(16) := p_failed_item.text_cat_attribute16;
2742 v_rec.locals(17) := p_failed_item.text_cat_attribute17;
2743 v_rec.locals(18) := p_failed_item.text_cat_attribute18;
2744 v_rec.locals(19) := p_failed_item.text_cat_attribute19;
2745 v_rec.locals(20) := p_failed_item.text_cat_attribute20;
2746 v_rec.locals(21) := p_failed_item.text_cat_attribute21;
2747 v_rec.locals(22) := p_failed_item.text_cat_attribute22;
2748 v_rec.locals(23) := p_failed_item.text_cat_attribute23;
2749 v_rec.locals(24) := p_failed_item.text_cat_attribute24;
2750 v_rec.locals(25) := p_failed_item.text_cat_attribute25;
2751 v_rec.locals(26) := p_failed_item.text_cat_attribute26;
2752 v_rec.locals(27) := p_failed_item.text_cat_attribute27;
2753 v_rec.locals(28) := p_failed_item.text_cat_attribute28;
2754 v_rec.locals(29) := p_failed_item.text_cat_attribute29;
2755 v_rec.locals(30) := p_failed_item.text_cat_attribute30;
2756 v_rec.locals(31) := p_failed_item.text_cat_attribute31;
2757 v_rec.locals(32) := p_failed_item.text_cat_attribute32;
2758 v_rec.locals(33) := p_failed_item.text_cat_attribute33;
2759 v_rec.locals(34) := p_failed_item.text_cat_attribute34;
2760 v_rec.locals(35) := p_failed_item.text_cat_attribute35;
2761 v_rec.locals(36) := p_failed_item.text_cat_attribute36;
2762 v_rec.locals(37) := p_failed_item.text_cat_attribute37;
2763 v_rec.locals(38) := p_failed_item.text_cat_attribute38;
2764 v_rec.locals(39) := p_failed_item.text_cat_attribute39;
2765 v_rec.locals(40) := p_failed_item.text_cat_attribute40;
2766 v_rec.locals(41) := p_failed_item.text_cat_attribute41;
2767 v_rec.locals(42) := p_failed_item.text_cat_attribute42;
2768 v_rec.locals(43) := p_failed_item.text_cat_attribute43;
2769 v_rec.locals(44) := p_failed_item.text_cat_attribute44;
2770 v_rec.locals(45) := p_failed_item.text_cat_attribute45;
2771 v_rec.locals(46) := p_failed_item.text_cat_attribute46;
2772 v_rec.locals(47) := p_failed_item.text_cat_attribute47;
2773 v_rec.locals(48) := p_failed_item.text_cat_attribute48;
2774 v_rec.locals(49) := p_failed_item.text_cat_attribute49;
2775 v_rec.locals(50) := p_failed_item.text_cat_attribute50;
2776 --Bug#2785949
2777 --Removed the to_char function as
2778 --p_failed_item.num_cat_attribute1..p_failed_item.num_cat_attribute50
2779 --is already VARCHAR type
2780 v_rec.locals(51) := p_failed_item.num_cat_attribute1;
2781 v_rec.locals(52) := p_failed_item.num_cat_attribute2;
2782 v_rec.locals(53) := p_failed_item.num_cat_attribute3;
2783 v_rec.locals(54) := p_failed_item.num_cat_attribute4;
2784 v_rec.locals(55) := p_failed_item.num_cat_attribute5;
2785 v_rec.locals(56) := p_failed_item.num_cat_attribute6;
2786 v_rec.locals(57) := p_failed_item.num_cat_attribute7;
2787 v_rec.locals(58) := p_failed_item.num_cat_attribute8;
2788 v_rec.locals(59) := p_failed_item.num_cat_attribute9;
2789 v_rec.locals(60) := p_failed_item.num_cat_attribute10;
2790 v_rec.locals(61) := p_failed_item.num_cat_attribute11;
2791 v_rec.locals(62) := p_failed_item.num_cat_attribute12;
2792 v_rec.locals(63) := p_failed_item.num_cat_attribute13;
2793 v_rec.locals(64) := p_failed_item.num_cat_attribute14;
2794 v_rec.locals(65) := p_failed_item.num_cat_attribute15;
2795 v_rec.locals(66) := p_failed_item.num_cat_attribute16;
2796 v_rec.locals(67) := p_failed_item.num_cat_attribute17;
2797 v_rec.locals(68) := p_failed_item.num_cat_attribute18;
2798 v_rec.locals(69) := p_failed_item.num_cat_attribute19;
2799 v_rec.locals(70) := p_failed_item.num_cat_attribute20;
2800 v_rec.locals(71) := p_failed_item.num_cat_attribute21;
2801 v_rec.locals(72) := p_failed_item.num_cat_attribute22;
2802 v_rec.locals(73) := p_failed_item.num_cat_attribute23;
2803 v_rec.locals(74) := p_failed_item.num_cat_attribute24;
2804 v_rec.locals(75) := p_failed_item.num_cat_attribute25;
2805 v_rec.locals(76) := p_failed_item.num_cat_attribute26;
2806 v_rec.locals(77) := p_failed_item.num_cat_attribute27;
2807 v_rec.locals(78) := p_failed_item.num_cat_attribute28;
2808 v_rec.locals(79) := p_failed_item.num_cat_attribute29;
2809 v_rec.locals(80) := p_failed_item.num_cat_attribute30;
2810 v_rec.locals(81) := p_failed_item.num_cat_attribute31;
2811 v_rec.locals(82) := p_failed_item.num_cat_attribute32;
2812 v_rec.locals(83) := p_failed_item.num_cat_attribute33;
2813 v_rec.locals(84) := p_failed_item.num_cat_attribute34;
2814 v_rec.locals(85) := p_failed_item.num_cat_attribute35;
2815 v_rec.locals(86) := p_failed_item.num_cat_attribute36;
2816 v_rec.locals(87) := p_failed_item.num_cat_attribute37;
2817 v_rec.locals(88) := p_failed_item.num_cat_attribute38;
2818 v_rec.locals(89) := p_failed_item.num_cat_attribute39;
2819 v_rec.locals(90) := p_failed_item.num_cat_attribute40;
2820 v_rec.locals(91) := p_failed_item.num_cat_attribute41;
2821 v_rec.locals(92) := p_failed_item.num_cat_attribute42;
2822 v_rec.locals(93) := p_failed_item.num_cat_attribute43;
2823 v_rec.locals(94) := p_failed_item.num_cat_attribute44;
2824 v_rec.locals(95) := p_failed_item.num_cat_attribute45;
2825 v_rec.locals(96) := p_failed_item.num_cat_attribute46;
2826 v_rec.locals(97) := p_failed_item.num_cat_attribute47;
2827 v_rec.locals(98) := p_failed_item.num_cat_attribute48;
2828 v_rec.locals(99) := p_failed_item.num_cat_attribute49;
2829 v_rec.locals(100) := p_failed_item.num_cat_attribute50;
2830 v_rec.locals(101) := p_failed_item.tl_text_cat_attribute1;
2831 v_rec.locals(102) := p_failed_item.tl_text_cat_attribute2;
2832 v_rec.locals(103) := p_failed_item.tl_text_cat_attribute3;
2833 v_rec.locals(104) := p_failed_item.tl_text_cat_attribute4;
2834 v_rec.locals(105) := p_failed_item.tl_text_cat_attribute5;
2835 v_rec.locals(106) := p_failed_item.tl_text_cat_attribute6;
2836 v_rec.locals(107) := p_failed_item.tl_text_cat_attribute7;
2837 v_rec.locals(108) := p_failed_item.tl_text_cat_attribute8;
2838 v_rec.locals(109) := p_failed_item.tl_text_cat_attribute9;
2839 v_rec.locals(110) := p_failed_item.tl_text_cat_attribute10;
2840 v_rec.locals(111) := p_failed_item.tl_text_cat_attribute11;
2841 v_rec.locals(112) := p_failed_item.tl_text_cat_attribute12;
2842 v_rec.locals(113) := p_failed_item.tl_text_cat_attribute13;
2843 v_rec.locals(114) := p_failed_item.tl_text_cat_attribute14;
2844 v_rec.locals(115) := p_failed_item.tl_text_cat_attribute15;
2845 v_rec.locals(116) := p_failed_item.tl_text_cat_attribute16;
2846 v_rec.locals(117) := p_failed_item.tl_text_cat_attribute17;
2847 v_rec.locals(118) := p_failed_item.tl_text_cat_attribute18;
2848 v_rec.locals(119) := p_failed_item.tl_text_cat_attribute19;
2849 v_rec.locals(120) := p_failed_item.tl_text_cat_attribute20;
2850 v_rec.locals(121) := p_failed_item.tl_text_cat_attribute21;
2851 v_rec.locals(122) := p_failed_item.tl_text_cat_attribute22;
2852 v_rec.locals(123) := p_failed_item.tl_text_cat_attribute23;
2853 v_rec.locals(124) := p_failed_item.tl_text_cat_attribute24;
2854 v_rec.locals(125) := p_failed_item.tl_text_cat_attribute25;
2855 v_rec.locals(126) := p_failed_item.tl_text_cat_attribute26;
2856 v_rec.locals(127) := p_failed_item.tl_text_cat_attribute27;
2857 v_rec.locals(128) := p_failed_item.tl_text_cat_attribute28;
2858 v_rec.locals(129) := p_failed_item.tl_text_cat_attribute29;
2859 v_rec.locals(130) := p_failed_item.tl_text_cat_attribute30;
2860 v_rec.locals(131) := p_failed_item.tl_text_cat_attribute31;
2861 v_rec.locals(132) := p_failed_item.tl_text_cat_attribute32;
2862 v_rec.locals(133) := p_failed_item.tl_text_cat_attribute33;
2863 v_rec.locals(134) := p_failed_item.tl_text_cat_attribute34;
2864 v_rec.locals(135) := p_failed_item.tl_text_cat_attribute35;
2865 v_rec.locals(136) := p_failed_item.tl_text_cat_attribute36;
2866 v_rec.locals(137) := p_failed_item.tl_text_cat_attribute37;
2867 v_rec.locals(138) := p_failed_item.tl_text_cat_attribute38;
2868 v_rec.locals(139) := p_failed_item.tl_text_cat_attribute39;
2869 v_rec.locals(140) := p_failed_item.tl_text_cat_attribute40;
2870 v_rec.locals(141) := p_failed_item.tl_text_cat_attribute41;
2871 v_rec.locals(142) := p_failed_item.tl_text_cat_attribute42;
2872 v_rec.locals(143) := p_failed_item.tl_text_cat_attribute43;
2873 v_rec.locals(144) := p_failed_item.tl_text_cat_attribute44;
2874 v_rec.locals(145) := p_failed_item.tl_text_cat_attribute45;
2875 v_rec.locals(146) := p_failed_item.tl_text_cat_attribute46;
2876 v_rec.locals(147) := p_failed_item.tl_text_cat_attribute47;
2877 v_rec.locals(148) := p_failed_item.tl_text_cat_attribute48;
2878 v_rec.locals(149) := p_failed_item.tl_text_cat_attribute49;
2879 v_rec.locals(150) := p_failed_item.tl_text_cat_attribute50;
2880 v_rec.unit_price := p_failed_item.unit_price;
2881 v_rec.currency := p_failed_item.currency;
2882 v_rec.unit_of_measure := p_failed_item.unit_of_measure;
2883 v_rec.supplier_site_id := p_failed_item.supplier_site_id;
2884 v_rec.supplier_site_code := p_failed_item.supplier_site_code;
2885 v_rec.price_list_name := p_failed_item.price_list_name;
2886 v_rec.price_list_id := p_failed_item.price_list_id;
2887 v_rec.buyer_name := p_failed_item.buyer_name;
2888 v_rec.LAST_UPDATE_LOGIN := p_failed_item.LAST_UPDATE_LOGIN;
2889 v_rec.LAST_UPDATED_BY := p_failed_item.LAST_UPDATED_BY;
2890 v_rec.LAST_UPDATE_DATE := p_failed_item.LAST_UPDATE_DATE;
2891 v_rec.CREATED_BY := p_failed_item.CREATED_BY;
2892 v_rec.CREATION_DATE := p_failed_item.CREATION_DATE;
2893
2894 -- Insert root descriptors
2895 xErrLoc := 200;
2896
2897 --Bug#3396442
2898 FOR root_desc IN custom_non_price_desc_cr(0) LOOP
2899 --Check section_tag - 1000 to find the value of that particular descriptor value
2900 v_section_tag := root_desc.section_tag-1000;
2901 IF (v_rec.roots(v_section_tag) IS NOT NULL) THEN
2902 xErrLoc := 200;
2903
2904 insert into icx_por_failed_lines
2905 (job_number, line_number, action,
2906 row_type, descriptor_key, descriptor_value)
2907 values
2908 (gJobNumber, v_rec.line_number, v_rec.system_action,
2909 v_rec.row_type, root_desc.key, v_rec.roots(v_section_tag));
2910 END IF;
2911 END LOOP;
2912
2913 -- Insert category key
2914 xErrLoc := 300;
2915
2916 IF (v_rec.category_name IS NOT NULL) THEN
2917 vDescKey := 'CATEGORY_NAME';
2918
2919 insert into icx_por_failed_lines
2920 (job_number, line_number, action,
2921 row_type, descriptor_key, descriptor_value)
2922 values
2923 (gJobNumber, v_rec.line_number, v_rec.system_action,
2924 v_rec.row_type, vDescKey, v_rec.category_name);
2925 END IF;
2926
2927 -- Process category attributes
2928 IF (v_rec.rt_category_id IS NOT NULL) THEN
2929 xErrLoc := 400;
2930
2931 fetch_local_descriptors(v_rec.rt_category_id);
2932
2933 xErrLoc := 410;
2934
2935 FOR i IN 1..gErrorCatDescKeys.COUNT loop
2936
2937 IF (v_rec.locals(i) IS NOT NULL) THEN
2938 insert into icx_por_failed_lines
2939 (job_number, line_number, action,
2940 row_type, descriptor_key, descriptor_value)
2941 values
2942 (gJobNumber, v_rec.line_number, v_rec.system_action,
2943 v_rec.row_type, gErrorCatDescKeys(i), v_rec.locals(i));
2944 END IF;
2945
2946 END LOOP;
2947
2948 END IF;
2949
2950 --Bug#2729328
2951 -- Insert price columns
2952 IF ( v_rec.row_type in ('ITEM_PRICE') OR
2953 ( v_rec.row_type in ('ITEM') AND v_rec.system_action = 'ADD')) THEN
2954 xErrLoc := 500;
2955
2956 -- can only have price (numeric) or price code
2957 -- so either this code sets the price(numeric)
2958 -- or the code at the end sets the price code
2959 -- IF (v_rec.unit_price IS NOT NULL) THEN
2960 vDescKey := 'PRICE';
2961
2962 insert into icx_por_failed_lines
2963 (job_number, line_number, action,
2964 row_type, descriptor_key, descriptor_value)
2965 values
2966 (gJobNumber, v_rec.line_number, v_rec.system_action,
2967 v_rec.row_type, vDescKey, v_rec.unit_price);
2968 -- END IF;
2969
2970 xErrLoc := 510;
2971
2972 -- IF (v_rec.currency IS NOT NULL) THEN
2973 vDescKey := 'CURRENCY';
2974
2975 insert into icx_por_failed_lines
2976 (job_number, line_number, action,
2977 row_type, descriptor_key, descriptor_value)
2978 values
2979 (gJobNumber, v_rec.line_number, v_rec.system_action,
2980 v_rec.row_type, vDescKey, v_rec.currency);
2981 -- END IF;
2982
2983 xErrLoc := 520;
2984
2985 -- IF (v_rec.unit_of_measure IS NOT NULL) THEN
2986 vDescKey := 'UOM';
2987
2988 insert into icx_por_failed_lines
2989 (job_number, line_number, action,
2990 row_type, descriptor_key, descriptor_value)
2991 values
2992 (gJobNumber, v_rec.line_number, v_rec.system_action,
2993 v_rec.row_type, vDescKey, v_rec.unit_of_measure);
2994 -- END IF;
2995
2996 xErrLoc := 530;
2997 -- IF (v_rec.buyer_name IS NOT NULL) THEN
2998 vDescKey := 'BUYER';
2999
3000 insert into icx_por_failed_lines
3001 (job_number, line_number, action,
3002 row_type, descriptor_key, descriptor_value)
3003 values
3004 (gJobNumber, v_rec.line_number, v_rec.system_action,
3005 v_rec.row_type, vDescKey, v_rec.buyer_name);
3006 -- END IF;
3007
3008 xErrLoc := 540;
3009
3010 -- IF (v_rec.price_list_name IS NOT NULL) THEN
3011 vDescKey := 'PRICELIST';
3012
3013 insert into icx_por_failed_lines
3014 (job_number, line_number, action,
3015 row_type, descriptor_key, descriptor_value)
3016 values
3017 (gJobNumber, v_rec.line_number, v_rec.system_action,
3018 v_rec.row_type, vDescKey, v_rec.price_list_name);
3019 -- END IF;
3020
3021 xErrLoc := 550;
3022
3023 --Bug#2729328
3024 -- IF (v_rec.supplier IS NOT NULL) THEN
3025 vDescKey := 'SUPPLIER';
3026
3027 insert into icx_por_failed_lines
3028 (job_number, line_number, action,
3029 row_type, descriptor_key, descriptor_value)
3030 values
3031 (gJobNumber, v_rec.line_number, v_rec.system_action,
3032 v_rec.row_type, vDescKey, v_rec.supplier);
3033 -- END IF;
3034
3035 xErrLoc := 560;
3036
3037 -- IF (v_rec.supplier_part_num IS NOT NULL) THEN
3038 vDescKey := 'SUPPLIER_PART_NUM';
3039
3040 insert into icx_por_failed_lines
3041 (job_number, line_number, action,
3042 row_type, descriptor_key, descriptor_value)
3043 values
3044 (gJobNumber, v_rec.line_number, v_rec.system_action,
3045 v_rec.row_type, vDescKey, v_rec.supplier_part_num);
3046 -- END IF;
3047
3048 xErrLoc := 570;
3049
3050 --Bug#2709997
3051 -- IF (v_rec.supplier_site_code IS NOT NULL) THEN
3052 vDescKey := 'SUPPLIER_SITE';
3053
3054 insert into icx_por_failed_lines
3055 (job_number, line_number, action,
3056 row_type, descriptor_key, descriptor_value)
3057 values
3058 (gJobNumber, v_rec.line_number, v_rec.system_action,
3059 v_rec.row_type, vDescKey, v_rec.supplier_site_code);
3060 -- END IF;
3061
3062 xErrLoc := 580;
3063
3064 -- IF (v_rec.supplier_part_auxid IS NOT NULL) THEN
3065 vDescKey := 'SUPPLIER_PART_AUXID';
3066
3067 insert into icx_por_failed_lines
3068 (job_number, line_number, action,
3069 row_type, descriptor_key, descriptor_value)
3070 values
3071 (gJobNumber, v_rec.line_number, v_rec.system_action,
3072 v_rec.row_type, vDescKey, v_rec.supplier_part_auxid);
3073 -- END IF;
3074
3075 xErrLoc := 590;
3076
3077 --Bug#2729328
3078 --For an item txt file with no supplier_part_num specified,
3079 --description not getting logged into icx_por_failed_lines
3080 IF (v_rec.description IS NOT NULL) THEN
3081 vDescKey := 'DESCRIPTION';
3082
3083 insert into icx_por_failed_lines
3084 (job_number, line_number, action,
3085 row_type, descriptor_key, descriptor_value)
3086 values
3087 (gJobNumber, v_rec.line_number, v_rec.system_action,
3088 v_rec.row_type, vDescKey, v_rec.description);
3089 END IF;
3090
3091 xErrLoc := 600;
3092 /*
3093 save_failed_price_break(gJobNumber, v_rec.line_number,
3094 v_rec.system_action, v_rec.row_type, v_rec.price_break_count);
3095 */
3096 END IF;
3097 EXCEPTION
3098 WHEN OTHERS THEN
3099 ROLLBACK;
3100
3101 RAISE_APPLICATION_ERROR(-20000,
3102 'Exception at ICX_POR_ITEM_UPLOAD.save_failed_item('
3103 || xErrLoc || '): ' || SQLERRM);
3104 END save_failed_item;
3105
3106 /* Saves a failed price */
3107 PROCEDURE save_failed_price(pJobNumber IN NUMBER,
3108 p_line IN tITPriceRecord) IS
3109 xErrLoc PLS_INTEGER;
3110 vDescKey ICX_POR_FAILED_LINES.DESCRIPTOR_KEY%TYPE;
3111 vRowType VARCHAR2(30) := 'PRICE';
3112 BEGIN
3113 xErrLoc := 100;
3114
3115 --if (p_line.supplier_name is not null) then
3116 vDescKey := 'SUPPLIER';
3117
3118 insert into icx_por_failed_lines
3119 (job_number, line_number, action,
3120 row_type, descriptor_key, descriptor_value)
3121 values
3122 (gJobNumber, p_line.line_number, p_line.system_action,
3123 vRowType, vDescKey, p_line.supplier_name);
3124 --end if;
3125
3126 xErrLoc := 110;
3127
3128 --if (p_line.supplier_part_num is not null) then
3129 vDescKey := 'SUPPLIER_PART_NUM';
3130
3131 insert into icx_por_failed_lines
3132 (job_number, line_number, action,
3133 row_type, descriptor_key, descriptor_value)
3134 values
3135 (gJobNumber, p_line.line_number, p_line.system_action,
3136 vRowType, vDescKey, p_line.supplier_part_num);
3137 --end if;
3138
3139 xErrLoc := 115;
3140
3141 --Bug#2611529
3142 --if (p_line.supplier_part_auxid is not null) then
3143 vDescKey := 'SUPPLIER_PART_AUXID';
3144
3145 insert into icx_por_failed_lines
3146 (job_number, line_number, action,
3147 row_type, descriptor_key, descriptor_value)
3148 values
3149 (gJobNumber, p_line.line_number, p_line.system_action,
3150 vRowType, vDescKey, p_line.supplier_part_auxid);
3151 --end if;
3152
3153 xErrLoc := 120;
3154
3155 --Bug#2709997
3156 --if (p_line.supplier_site_code is not null) then
3157 vDescKey := 'SUPPLIER_SITE';
3158
3159 insert into icx_por_failed_lines
3160 (job_number, line_number, action,
3161 row_type, descriptor_key, descriptor_value)
3162 values
3163 (gJobNumber, p_line.line_number, p_line.system_action,
3164 vRowType, vDescKey, p_line.supplier_site_code);
3165 --end if;
3166
3167 xErrLoc := 125;
3168
3169 -- can only have price (numeric) or price code
3170 -- so either this code sets the price(numeric)
3171 -- or the code at the end sets the price code
3172 --if (p_line.unit_price is not null) then
3173 vDescKey := 'PRICE';
3174
3175 insert into icx_por_failed_lines
3176 (job_number, line_number, action,
3177 row_type, descriptor_key, descriptor_value)
3178 values
3179 (gJobNumber, p_line.line_number, p_line.system_action,
3180 vRowType, vDescKey, p_line.unit_price);
3181 --end if;
3182
3183 xErrLoc := 130;
3184
3185 --if (p_line.currency is not null) then
3186 vDescKey := 'CURRENCY';
3187
3188 insert into icx_por_failed_lines
3189 (job_number, line_number, action,
3190 row_type, descriptor_key, descriptor_value)
3191 values
3192 (gJobNumber, p_line.line_number, p_line.system_action,
3193 vRowType, vDescKey, p_line.currency);
3194 --end if;
3195
3196 xErrLoc := 140;
3197
3198 --if (p_line.unit_of_measure is not null) then
3199 vDescKey := 'UOM';
3200
3201 insert into icx_por_failed_lines
3202 (job_number, line_number, action,
3203 row_type, descriptor_key, descriptor_value)
3204 values
3205 (gJobNumber, p_line.line_number, p_line.system_action,
3206 vRowType, vDescKey, p_line.unit_of_measure);
3207 --end if;
3208
3209 xErrLoc := 150;
3210
3211 --if (p_line.buyer_name is not null) then
3212 vDescKey := 'BUYER';
3213
3214 insert into icx_por_failed_lines
3215 (job_number, line_number, action,
3216 row_type, descriptor_key, descriptor_value)
3217 values
3218 (gJobNumber, p_line.line_number, p_line.system_action,
3219 vRowType, vDescKey, p_line.buyer_name);
3220 --end if;
3221
3222 xErrLoc := 160;
3223
3224 --if (p_line.price_list_name is not null) then
3225 vDescKey := 'PRICELIST';
3226
3227 insert into icx_por_failed_lines
3228 (job_number, line_number, action,
3229 row_type, descriptor_key, descriptor_value)
3230 values
3231 (gJobNumber, p_line.line_number, p_line.system_action,
3232 vRowType, vDescKey, p_line.price_list_name);
3233 --end if;
3234
3235 xErrLoc := 170;
3236
3237 EXCEPTION
3238 WHEN OTHERS THEN
3239 ROLLBACK;
3240 RAISE_APPLICATION_ERROR(-20000,
3241 'Exception at ICX_POR_ITEM_UPLOAD.save_failed_price('
3242 || xErrLoc || '): ' || SQLERRM);
3243 END save_failed_price;
3244
3245 /** Proc : save_required_descriptor_errors
3246 ** Desc : Saves the error messages related to missing values for
3247 ** required descriptors
3248 **/
3249 PROCEDURE save_required_desc_errors(p_request_id IN NUMBER,
3250 p_line_number IN NUMBER, p_desc_names IN VARCHAR2) IS
3251 vBreakPos PLS_INTEGER := 1;
3252 vStartPos PLS_INTEGER := 1;
3253 vDescName ICX_CAT_DESCRIPTORS_TL.KEY%TYPE;
3254 BEGIN
3255 IF (p_desc_names IS NULL) THEN
3256 RETURN;
3257 END IF;
3258
3259 LOOP
3260 vBreakPos := INSTR(p_desc_names, '`', vStartPos);
3261
3262 IF (vBreakPos = 0) THEN
3263 EXIT;
3264 END IF;
3265
3266 vDescName := SUBSTR(p_desc_names, vStartPos, vBreakPos - vStartPos);
3267
3268 INSERT INTO icx_por_failed_line_messages
3269 (job_number, line_number, descriptor_key, message_name)
3270 VALUES
3271 (p_request_id, p_line_number, vDescName, 'ICX_POR_CAT_FIELD_REQUIRED');
3272
3273 vStartPos := vBreakPos + 1;
3274 END LOOP;
3275
3276 END save_required_desc_errors;
3277
3278 /* Processes failed prices in the current batch */
3279 PROCEDURE process_batch_price_errors(pErrorRowids IN
3280 dbms_sql.urowid_table) IS
3281 i PLS_INTEGER;
3282 CURSOR get_failed_price(p_rowid IN VARCHAR2) IS
3283 SELECT line_number, decode(action, 'SYNC','ADD',action) system_action,
3284 supplier, supplier_part_num, supplier_part_auxid, --Bug#2611529
3285 supplier_site_code, --Bug#
3286 --Bug#2785949
3287 --Removed the to_char function as
3288 --unit_price in ICX_CAT_PRICES_GT is already VARCHAR type
3289 unit_price, currency, unit_of_measure,
3290 buyer_name, price_list_name,
3291 error_message, error_flag
3292 FROM ICX_CAT_PRICES_GT
3293 WHERE rowid = p_rowid;
3294 v_rec tITPriceRecord;
3295 xErrLoc PLS_INTEGER := 100;
3296 BEGIN
3297
3298 FOR i IN 1..pErrorRowids.COUNT LOOP
3299 xErrLoc := 200;
3300
3301 OPEN get_failed_price(pErrorRowids(i));
3302 FETCH get_failed_price INTO v_rec;
3303
3304 IF (NOT get_failed_price%NOTFOUND) THEN
3305 xErrLoc := 300;
3306
3307 IF (v_rec.error_message IS NOT NULL) THEN
3308 xErrLoc := 400;
3309 save_failed_line_message(gJobNumber, v_rec.line_number,
3310 v_rec.error_message);
3311 END IF;
3312
3313 IF (v_rec.error_flag IS NULL) THEN
3314 xErrLoc := 500;
3315 save_failed_price(gJobNumber, v_rec);
3316 ELSIF (v_rec.error_flag = 'Y') THEN
3317 xErrLoc := 510;
3318
3319 UPDATE icx_por_failed_lines SET action = v_rec.system_action
3320 WHERE job_number = gJobNumber
3321 AND line_number = v_rec.line_number;
3322 END IF;
3323
3324 END IF;
3325
3326 CLOSE get_failed_price;
3327 END LOOP;
3328
3329 EXCEPTION
3330 WHEN OTHERS THEN
3331 ROLLBACK;
3332
3333 RAISE_APPLICATION_ERROR(-20000,
3334 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_price_errors('
3335 || xErrLoc || '): ' || SQLERRM);
3336 END process_batch_price_errors;
3337
3338 /* Processes failed items in the current batch */
3339 PROCEDURE process_batch_item_errors(pErrorRowids IN
3340 dbms_sql.urowid_table) IS
3341 i PLS_INTEGER;
3342 CURSOR get_failed_line(p_rowid IN VARCHAR2) IS
3343 SELECT * FROM ICX_CAT_ITEMS_GT
3344 WHERE rowid = p_rowid;
3345 v_rec ICX_CAT_ITEMS_GT%ROWTYPE;
3346 xErrLoc PLS_INTEGER := 100;
3347 BEGIN
3348
3349 FOR i IN 1..pErrorRowids.COUNT LOOP
3350 xErrLoc := 200;
3351
3352 OPEN get_failed_line(pErrorRowids(i));
3353 FETCH get_failed_line INTO v_rec;
3354
3355 IF (NOT get_failed_line%NOTFOUND) THEN
3356
3357 IF (v_rec.system_action = 'ADD') THEN
3358 save_required_desc_errors(gJobNumber, v_rec.line_number,
3359 v_rec.required_descriptors);
3360 save_required_desc_errors(gJobNumber, v_rec.line_number,
3361 v_rec.required_tl_descriptors);
3362 ELSIF (v_rec.system_action = 'TRANSLATE') THEN
3363 save_required_desc_errors(gJobNumber, v_rec.line_number,
3364 v_rec.required_tl_descriptors);
3365 END IF;
3366
3367 IF (v_rec.error_message IS NOT NULL) THEN
3368 xErrLoc := 300;
3369 save_failed_line_message(gJobNumber, v_rec.line_number,
3370 v_rec.error_message);
3371 END IF;
3372
3373 IF (v_rec.error_flag IS NULL) THEN
3374 xErrLoc := 400;
3375 save_failed_item(gJobNumber, v_rec);
3376 ELSIF (v_rec.error_flag = 'Y') THEN
3377 xErrLoc := 410;
3378
3379 UPDATE icx_por_failed_lines SET action = v_rec.system_action
3380 WHERE job_number = gJobNumber
3381 AND line_number = v_rec.line_number;
3382 END IF;
3383
3384 END IF;
3385
3386 CLOSE get_failed_line;
3387 END LOOP;
3388
3389 EXCEPTION
3390 WHEN OTHERS THEN
3391 ROLLBACK;
3392
3393 RAISE_APPLICATION_ERROR(-20000,
3394 'Exception at ICX_POR_ITEM_UPLOAD.process_batch_item_errors('
3395 || xErrLoc || '): ' || SQLERRM);
3396 END process_batch_item_errors;
3397
3398 /* Saves the error message for duplicate lines */
3399 PROCEDURE reject_line(p_row_id IN UROWID, p_row_type IN VARCHAR2,
3400 p_error_message IN VARCHAR2) IS
3401
3402 BEGIN
3403 IF (p_row_type = 'ITEM') THEN
3404 UPDATE ICX_CAT_ITEMS_GT SET error_message = error_message ||
3405 p_error_message
3406 WHERE rowid = p_row_id;
3407 ELSE
3408 UPDATE ICX_CAT_PRICES_GT SET error_message = error_message ||
3409 p_error_message
3410 WHERE rowid = p_row_id;
3411 END IF;
3412
3413 COMMIT;
3414
3415 EXCEPTION
3416 WHEN OTHERS THEN
3417 ROLLBACK;
3418 RAISE_APPLICATION_ERROR(-20000,
3419 'Exception at ICX_POR_ITEM_UPLOAD.reject_line: ' || SQLERRM);
3420 END reject_line;
3421
3422 /* Fail the duplicate prices. This needs to be done before processing
3423 prices since we want to avoid sorting every time. */
3424 PROCEDURE reject_duplicate_prices IS
3425 --Bug#2611529
3426 CURSOR it_table_csr IS
3427 SELECT it.rowid, it.supplier,
3428 it.supplier_part_num, it.supplier_part_auxid, it.org_id,
3429 it.supplier_site_id, it.currency
3430 FROM ICX_CAT_PRICES_GT it
3431 ORDER BY it.supplier asc, it.supplier_part_num asc,
3432 it.supplier_part_auxid asc,
3433 it.org_id asc, it.currency asc, it.supplier_site_id asc,
3434 it.line_number desc;
3435
3436 vRowids dbms_sql.urowid_table;
3437 vSupplierPartNums dbms_sql.varchar2_table;
3438 vSupplierPartAuxids dbms_sql.varchar2_table; --Bug#2611529
3439 vSupplierNames dbms_sql.varchar2_table; --Bug#2611529
3440 vCurrencies dbms_sql.varchar2_table;
3441 vOrgIds dbms_sql.number_table;
3442 vSupplierSiteIds dbms_sql.number_table;
3443
3444 vPrevPartNum VARCHAR2(2000) := null;
3445 vPrevPartAuxid VARCHAR2(2000) := null; --Bug#2611529
3446 vPrevSupplierName VARCHAR2(2000) := null; --Bug#2611529
3447 vPrevOrgId NUMBER := -1;
3448 vPrevCurrency VARCHAR2(30);
3449 vPrevSupplierSiteId NUMBER := -1;
3450
3451 xErrLoc PLS_INTEGER := 100;
3452 BEGIN
3453 OPEN it_table_csr;
3454
3455 LOOP
3456 vRowids.DELETE;
3457 vSupplierPartNums.DELETE;
3458 vSupplierNames.DELETE; -- Bug#2611529
3459 vOrgIds.DELETE;
3460 vSupplierSiteIds.DELETE;
3461 vCurrencies.DELETE;
3462
3463 --Bug#2611529
3464 FETCH it_table_csr BULK COLLECT INTO
3465 vRowids, vSupplierNames, vSupplierPartNums,
3466 vSupplierPartAuxids, vOrgIds, vSupplierSiteIds, vCurrencies
3467 LIMIT BATCH_SIZE;
3468
3469 EXIT WHEN vRowids.COUNT = 0;
3470
3471 FOR i IN 1..vRowids.COUNT LOOP
3472
3473 IF (vSupplierPartNums(i) IS NOT NULL AND
3474 vSupplierPartAuxids(i) IS NOT NULL AND --Bug#2611529
3475 vSupplierNames(i) IS NOT NULL AND --Bug#2611529
3476 vOrgIds(i) IS NOT NULL AND
3477 vCurrencies(i) IS NOT NULL AND
3478 vSupplierSiteIds(i) IS NOT NULL) THEN
3479
3480 IF (vSupplierPartNums(i) = vPrevPartNum AND
3481 vSupplierPartAuxids(i) = vPrevPartAuxid AND --Bug#2611529
3482 vSupplierNames(i) = vPrevSupplierName AND --Bug#2611529
3483 vOrgIds(i) = vPrevOrgId AND
3484 vCurrencies(i) = vPrevCurrency AND
3485 vSupplierSiteIds(i) = vPrevSupplierSiteId) THEN
3486 reject_line(vRowids(i), 'PRICE',
3487 --ErrMsg'.SUPPLIER_PART_NUM:POM_CATALOG_DUP_PRICE_IN_FILE');
3488 '.BUYER:ICX_POR_DUP_PRICE_LIST1');
3489 ELSE
3490 vPrevPartNum := vSupplierPartNums(i);
3491 vPrevPartAuxid := vSupplierPartAuxids(i); --Bug#2611529
3492 vPrevSupplierName := vSupplierNames(i); --Bug#2611529
3493 vPrevOrgId := vOrgIds(i);
3494 vPrevCurrency := vCurrencies(i);
3495 vPrevSupplierSiteId := vSupplierSiteIds(i);
3496 END IF;
3497
3498 ELSE
3499
3500 IF (vSupplierPartNums(i) IS NULL) THEN
3501 reject_line(vRowids(i), 'PRICE',
3502 '.SUPPLIER_PART_NUM:ICX_POR_CAT_FIELD_REQUIRED');
3503 END IF;
3504
3505 END IF;
3506
3507 END LOOP;
3508
3509 IF (vRowids.COUNT < BATCH_SIZE) THEN
3510 EXIT;
3511 END IF;
3512
3513 END LOOP;
3514
3515 CLOSE it_table_csr;
3516
3517 EXCEPTION
3518 WHEN OTHERS THEN
3519 IF (it_table_csr%ISOPEN) THEN
3520 CLOSE it_table_csr;
3521 END IF;
3522
3523 ROLLBACK;
3524
3525 RAISE_APPLICATION_ERROR(-20000,
3526 'Exception at ICX_POR_ITEM_UPLOAD.reject_duplicate_prices('
3527 || xErrLoc || '): ' || SQLERRM);
3528 END reject_duplicate_prices;
3529
3530
3531 /* Processes the valid prices */
3532 PROCEDURE move_prices(p_succ_line OUT NOCOPY NUMBER, p_failed_line OUT NOCOPY NUMBER,
3533 p_max_failed_line IN NUMBER) IS
3534 -- ItemUniqueness: Changes in any Pricing related uniqueness rules
3535 -- should be implemented in the below sql
3536 -- make sure the g_ plsql table is populated fine
3537 CURSOR it_table_csr IS
3538 SELECT /*+ USE_NL(it i ui) LEADING(it) */ it.rowid,
3539 --DUPE FAILED LINES Contract Auto Sourcing
3540 it.line_number,
3541 it.supplier_id,
3542 decode(it.contract_num, null, 'BULKLOAD', 'CONTRACT'),
3543 it.supplier_part_num,
3544 i.rt_item_id,
3545 decode(i.extractor_updated_flag, 'N', 'Y', 'Y', decode(icx_por_ext_item.getBulkLoadActiveFlag(it.action, i.rt_item_id), 'N', 'N', 'Y')),
3546 decode(it.action, 'SYNC', 'ADD', it.action),
3547 decode(it.error_flag, 'Y', 'Y',
3548 decode(it.error_message, NULL, 'N', 'Y')),
3549 it.row_type,
3550 it.org_id,
3551 it.price_list_id,
3552 it.supplier_site_id,
3553 it.unit_of_measure
3554 FROM ICX_CAT_PRICES_GT it,
3555 icx_cat_items_b i
3556 WHERE it.supplier = i.supplier (+)
3557 AND it.supplier_part_num = i.supplier_part_num (+)
3558 AND it.supplier_part_auxid = i.supplier_part_auxid (+) -- Bug#2611529
3559 AND it.org_id = i.org_id(+)
3560 AND it.processed_flag = 'N';
3561
3562 vRowids dbms_sql.urowid_table;
3563 --DUPE FAILED LINES Contract Auto Sourcing
3564 vLineNums dbms_sql.number_table;
3565 vSupplierPartNums dbms_sql.varchar2_table;
3566 vCurrentItemIds dbms_sql.number_table;
3567 vSystemActions dbms_sql.varchar2_table;
3568 vActiveFlags dbms_sql.varchar2_table;
3569 vHasErrors dbms_sql.varchar2_table;
3570 vRowTypes dbms_sql.varchar2_table;
3571 vPriceTypes dbms_sql.varchar2_table;
3572 vOrgIds dbms_sql.number_table;
3573 vPricelistIds dbms_sql.number_table;
3574 vSupplierSiteIds dbms_sql.number_table;
3575 vSupplierIds dbms_sql.number_table; --Bug#2611529
3576 vUoms dbms_sql.varchar2_table;
3577
3578 -- BUYER NORMALIZATION
3579 vItemJobNums dbms_sql.number_table;
3580 vPrevItemId NUMBER := -1;
3581 vPrevOrgId NUMBER := -1;
3582 vDistinctBuyerCount NUMBER := 0;
3583
3584 vCount PLS_INTEGER := 0;
3585 vASLCount PLS_INTEGER := 0;
3586 vHasAdd BOOLEAN := FALSE;
3587 vHasDelete BOOLEAN := FALSE;
3588 vErrorCount PLS_INTEGER := 0;
3589 vNumFailedLines NUMBER := 0;
3590
3591 vPrevPriceListId NUMBER := null;
3592 vAbort BOOLEAN := FALSE;
3593 xErrLoc PLS_INTEGER := 100;
3594 --DUPE FAILED LINES Contract Auto Sourcing
3595 vPrevLineNum NUMBER := -1;
3596 vCurLineNum NUMBER := -1;
3597
3598 BEGIN
3599 clear_all;
3600 p_succ_line := 0;
3601 p_failed_line := 0;
3602
3603 -- Reject all the duplicates first
3604 reject_duplicate_prices;
3605
3606 xErrLoc := 200;
3607 -- Loop thru the interface table and divide the prices into the prices batch
3608 -- and the error batch.
3609
3610 LOOP
3611 OPEN it_table_csr;
3612
3613 xErrLoc := 210;
3614 --DUPE FAILED LINES Contract Auto Sourcing
3615 FETCH it_table_csr BULK COLLECT INTO
3616 vRowids, vLineNums, vSupplierIds, vPriceTypes, vSupplierPartNums,
3617 vCurrentItemIds, vActiveFlags,
3618 vSystemActions, vHasErrors, vRowTypes, vOrgIds, vPricelistIds,
3619 vSupplierSiteIds, vUoms
3620 LIMIT BATCH_SIZE;
3621
3622 -- 01/31/02 - STO Bug 2209191 Added close cursor here
3623 CLOSE it_table_csr;
3624
3625 EXIT WHEN vRowids.COUNT = 0;
3626
3627 FOR i IN 1..vRowids.COUNT LOOP
3628 xErrLoc := 220;
3629 --DUPE FAILED LINES Contract Auto Sourcing
3630 vCurLineNum := vLineNums(i);
3631 xErrLoc := 221;
3632 IF (vSupplierPartNums(i) IS NOT NULL AND
3633 vCurrentItemIds(i) IS NULL) THEN
3634 vHasErrors(i) := 'Y';
3635 xErrLoc := 222;
3636 --DUPE FAILED LINES Contract Auto Sourcing
3637 IF ( vPrevLineNum <> vCurLineNum ) THEN
3638 reject_line(vRowids(i), 'PRICE',
3639 --ErrMsg'.SUPPLIER_PART_NUM:ICX_POR_PRC_INVALID_SUP_PART');
3640 '.BUYER:ICX_POR_PRC_INVALID_SUP_PART');
3641 END IF;
3642 END IF;
3643
3644 xErrLoc := 223;
3645 IF (vHasErrors(i) = 'Y') THEN
3646 --DUPE FAILED LINES Contract Auto Sourcing
3647 IF( vPrevLineNum <> vCurLineNum) THEN
3648 p_failed_line := p_failed_line + 1;
3649 vErrorCount := vErrorCount + 1;
3650 gErrorRowids(vErrorCount) := vRowids(i);
3651 END IF;
3652
3653 IF (p_max_failed_line >= 0 AND p_failed_line > p_max_failed_line) THEN
3654 -- Exceeded max failed lines. Save all the failed lines and return
3655 vAbort := TRUE;
3656 EXIT;
3657 END IF;
3658 ELSE
3659 xErrLoc := 270;
3660 p_succ_line := p_succ_line + 1;
3661 vCount := vCount + 1;
3662 gRowids(vCount) := vRowids(i);
3663 gCurrentItemIds(vCount) := vCurrentItemIds(i);
3664 -- For price item ids are same as current item ids
3665 gItemIds(vCount) := vCurrentItemIds(i);
3666 gSystemActions(vCount) := vSystemActions(i);
3667 gRowTypes(vCount) := vRowTypes(i);
3668 gPriceTypes(vCount) := vPriceTypes(i);
3669 gOrgIds(vCount) := vOrgIds(i);
3670 gPricelistIds(vCount) := vPricelistIds(i);
3671 gSupplierSiteIds(vCount) := vSupplierSiteIds(i);
3672 gActiveFlags(vCount) := vActiveFlags(i);
3673 gUoms(vCount) := vUoms(i);
3674 gSupplierIds(vCount) := vSupplierIds(i); --Bug#2611529
3675
3676 xErrLoc := 271;
3677 -- New or existing prices are processed in the same procedure
3678 IF (vSystemActions(i) = 'ADD') THEN
3679 vHasAdd := TRUE;
3680
3681 -- BUYER NORMALIZATION
3682 -- These pl/sql tables may not be distinct, since we don't
3683 -- retrieve rows ordered by item and buyer anymore
3684 -- However, the logic in process_batch_addupdate_prices can handle
3685 -- dupliate item/buyer pairs and will not insert duplicate into
3686 -- icx_cat_items_ctx_tlp
3687 IF (vPrevItemId <> vCurrentItemIds(i) OR
3688 vPrevOrgId <> vOrgIds(i)) THEN
3689 vDistinctBuyerCount := vDistinctBuyerCount + 1;
3690 gDistinctItemIds(vDistinctBuyerCount) := vCurrentItemIds(i);
3691 gDistinctBuyerIds(vDistinctBuyerCount) := vOrgIds(i);
3692 vPrevItemId := vCurrentItemIds(i);
3693 vPrevOrgId := vOrgIds(i);
3694 END IF;
3695
3696 ELSIF (vSystemActions(i) = 'DELETE') THEN
3697 vHasDelete := TRUE;
3698 END IF;
3699
3700 END IF;
3701
3702 --DUPE FAILED LINES Contract Auto Sourcing
3703 vPrevLineNum := vCurLineNum;
3704 END LOOP;
3705
3706 IF (vCount > 0 AND NOT vAbort) THEN
3707 xErrLoc := 300;
3708 process_batch_prices(vHasAdd, vHasDelete, vNumFailedLines);
3709 -- If there are some errors while processing the price lines, then
3710 -- update your successful/failed lines as per the
3711 -- value in vNumFailedLines.
3712 -- vNumFailedLines stores the failed lines while
3713 -- doing process_batch_prices
3714 p_failed_line := p_failed_line + vNumFailedLines;
3715 p_succ_line := p_succ_line - vNumFailedLines;
3716 vErrorCount := vErrorCount + vNumFailedLines;
3717 vNumFailedLines := 0; -- reset
3718
3719 commit;
3720 clear_tables;
3721 vCount := 0;
3722 vHasAdd := FALSE;
3723 vHasDelete := FALSE;
3724
3725 END IF;
3726
3727 IF (vErrorCount > 0) THEN
3728 xErrLoc := 400;
3729 process_batch_price_errors(gErrorRowids);
3730 commit;
3731 clear_error_tables;
3732 vErrorCount := 0;
3733 END IF;
3734
3735 IF (vRowids.COUNT < BATCH_SIZE OR vAbort) THEN
3736 EXIT;
3737 -- Update the processed_flag to null
3738 ELSE
3739 FORALL i IN 1..vRowids.COUNT
3740 UPDATE ICX_CAT_PRICES_GT
3741 SET processed_flag = 'Y' --Bug#2587763: set to "Y" instead of null.
3742 WHERE rowid = vRowids(i);
3743
3744 COMMIT;
3745
3746 END IF;
3747
3748 END LOOP;
3749
3750 EXCEPTION
3751 WHEN OTHERS THEN
3752 IF (it_table_csr%ISOPEN) THEN
3753 CLOSE it_table_csr;
3754 END IF;
3755
3756 ROLLBACK;
3757
3758 RAISE_APPLICATION_ERROR(-20000,
3759 'Exception at ICX_POR_ITEM_UPLOAD.move_prices('
3760 || xErrLoc || '): ' || SQLERRM);
3761 END move_prices;
3762
3763 PROCEDURE reject_duplicates IS
3764 -- Bug#2611529
3765 CURSOR it_table_csr(p_language IN VARCHAR2) IS
3766 -- Bug# 3366614 sosingha: include supplier_site_id also for Item Uniqueness check
3767 SELECT it.rowid, it.supplier, it.supplier_part_num, it.supplier_part_auxid, it.org_id, row_type, it.supplier_site_id, it.currency
3768 FROM ICX_CAT_ITEMS_GT it
3769 WHERE language = p_language
3770 ORDER BY supplier asc, supplier_part_num asc, supplier_part_auxid asc, org_id asc, supplier_site_id asc, currency asc, line_number desc;
3771
3772 vRowids dbms_sql.urowid_table;
3773 vSupplierPartNums dbms_sql.varchar2_table;
3774 vSupplierPartAuxids dbms_sql.varchar2_table; --Bug#2611529
3775 vSupplierNames dbms_sql.varchar2_table; --Bug#2611529
3776 vRowTypes dbms_sql.varchar2_table;
3777 vPrevPartNum VARCHAR2(2000) := null;
3778 vPrevPartAuxid VARCHAR2(2000) := null; --Bug#2611529
3779 vPrevSupplierName VARCHAR2(2000) := null; --Bug#2611529
3780 vOrgIds dbms_sql.varchar2_table;
3781 vCurrencies dbms_sql.varchar2_table;
3782 vSupplierSiteIds dbms_sql.number_table; -- Bug# 3366614 sosingha: for supp site ids
3783 vDupeRowIds dbms_sql.urowid_table; -- Bug# 3366614 sosingha: for row ids of duplicates
3784 vPrevCurrency VARCHAR2(30);
3785 vPrevSupplierSiteId NUMBER := -1; -- Bug# 3366614 sosingha: previous supp site ids
3786 vPrevOrgId NUMBER;
3787 xErrLoc PLS_INTEGER := 100;
3788 BEGIN
3789 OPEN it_table_csr(gJobLanguage);
3790
3791 LOOP
3792 vRowids.DELETE;
3793 vOrgIds.DELETE;
3794 vSupplierPartNums.DELETE;
3795 vSupplierPartAuxids.DELETE; -- Bug#2611529
3796 vSupplierNames.DELETE; -- Bug#2611529
3797 vCurrencies.DELETE;
3798 vRowTypes.DELETE;
3799 vSupplierSiteIds.DELETE;
3800 vDupeRowIds.DELETE;
3801
3802 xErrLoc := 200;
3803
3804 --Bug#2611529
3805 FETCH it_table_csr BULK COLLECT INTO
3806 -- Bug# 3366614 sosingha: fetch supplier_site_id also into the the table
3807 vRowids, vSupplierNames, vSupplierPartNums, vSupplierPartAuxids, vOrgIds, vRowTypes, vSupplierSiteIds, vCurrencies
3808 LIMIT BATCH_SIZE;
3809
3810 EXIT WHEN vRowids.COUNT = 0;
3811
3812 xErrLoc := 300;
3813
3814 FOR i IN 1..vRowids.COUNT LOOP
3815 IF (vSupplierPartNums(i) IS NOT NULL
3816 AND vSupplierNames(i) IS NOT NULL --Bug#2611529
3817 AND vSupplierPartAuxids(i) IS NOT NULL --Bug#2611529
3818 AND vCurrencies(i) IS NOT NULL
3819 AND vOrgIds(i) IS NOT NULL) THEN
3820
3821 IF (vSupplierPartNums(i) = vPrevPartNum
3822 AND vSupplierNames(i) = vPrevSupplierName --Bug#2611529
3823 AND vSupplierPartAuxids(i) = vPrevPartAuxid --Bug#2611529
3824 AND vCurrencies(i) = vPrevCurrency
3825 AND vOrgIds(i) = vPrevOrgId
3826 -- Bug# 3366614 sosingha: Check for supplier_site_id also
3827 AND vSupplierSiteIds(i) = vPrevSupplierSiteId) THEN
3828 -- Duplicate item, reject this
3829 reject_line(vRowids(i), 'ITEM',
3830 --ErrMsg'.SUPPLIER_PART_NUM:POM_CATALOG_DUP_ITEM_IN_FILE');
3831 '.BUYER:ICX_POR_DUP_SUPPLIER_PART');
3832 /* if 4 primary attributes are same and either Currency or Supplier Site are different
3833 store these rowIds cwwhich we need to mark it as 'D' to insert into item_prices by
3834 calling move_items with processed_flag as 'D' for these rows */
3835 ELSIF (vSupplierPartNums(i) = vPrevPartNum
3836 AND vSupplierNames(i) = vPrevSupplierName
3837 AND vSupplierPartAuxids(i) = vPrevPartAuxid
3838 AND vOrgIds(i) = vPrevOrgId) THEN
3839 vDupeRowIds(vDupeRowIds.COUNT + 1) := vRowids(i);
3840 gDuplicatesExists := true;
3841 ELSE
3842 vPrevPartNum := vSupplierPartNums(i);
3843 vPrevPartAuxid := vSupplierPartAuxids(i); --Bug#2611529
3844 vPrevSupplierName := vSupplierNames(i); --Bug#2611529
3845 vPrevOrgId := vOrgIds(i);
3846 vPrevCurrency := vCurrencies(i);
3847 vPrevSupplierSiteId := vSupplierSiteIds(i);
3848 END IF;
3849
3850 ELSE
3851 IF ( vSupplierPartNums(i) IS NULL ) THEN
3852 reject_line(vRowids(i), 'ITEM',
3853 '.SUPPLIER_PART_NUM:ICX_POR_CAT_FIELD_REQUIRED');
3854 END IF;
3855 /* Currenccy required error not needed to be thrown here,
3856 since for ITEM_PRICE or PRICE it is already caught in
3857 PriceListElementValidator.java
3858 IF ( vCurrencies(i) IS NULL AND vRowTypes(i) <> 'ITEM') THEN
3859 reject_line(vRowids(i), 'ITEM',
3860 '.CURRENCY:ICX_POR_CAT_FIELD_REQUIRED');
3861 END IF;
3862 */
3863
3864 END IF;
3865
3866 END LOOP;
3867
3868 xErrLoc := 400;
3869 -- Bug# 3366614 sosingha: Update rows with duplicates values for 4 primary item uniqueness attributes with processed flag as 'D'
3870 FOR i in 1..vDupeRowIds.COUNT LOOP
3871 UPDATE ICX_CAT_ITEMS_GT
3872 SET PROCESSED_FLAG = 'D'
3873 WHERE ROWID = vDupeRowIds(i);
3874 COMMIT;
3875 END LOOP;
3876
3877 xErrLoc := 500;
3878 IF (vRowids.COUNT < BATCH_SIZE) THEN
3879 EXIT;
3880 END IF;
3881
3882 END LOOP;
3883
3884 CLOSE it_table_csr;
3885
3886 EXCEPTION
3887 WHEN OTHERS THEN
3888 IF (it_table_csr%ISOPEN) THEN
3889 CLOSE it_table_csr;
3890 END IF;
3891
3892 ROLLBACK;
3893
3894 RAISE_APPLICATION_ERROR(-20000,
3895 'Exception at ICX_POR_ITEM_UPLOAD.reject_duplicates('
3896 || xErrLoc || '): ' || SQLERRM);
3897 END reject_duplicates;
3898
3899 /*Processes the valid items*/
3900 /* Bug# 3366614 sosingha: add an additional parameter p_processed_flag to differentiate
3901 Duplicate 4 primary attributes for item uniqueness with 'D' */
3902 PROCEDURE move_items( p_succ_line OUT NOCOPY NUMBER, p_failed_line OUT NOCOPY NUMBER,
3903 p_max_failed_line IN NUMBER, p_processed_flag VARCHAR2) IS
3904
3905 -- ItemUniqueness: Changes in any Item related uniqueness rules
3906 -- should be implemented in the below sql
3907 -- Make sure the g_ plsql table is populated
3908 -- also get the system_action populated for Item template file
3909 -- to UPDATE/DELETE, system_action is used to check if the item exists
3910 -- and throw the error BUYER:ICX_POR_PRC_INVALID_SUP_PART
3911 CURSOR it_table_csr IS
3912 SELECT /*+ USE_NL(it i tl) LEADING(it) */
3913 it.rowid,
3914 it.supplier, --Bug#2729328
3915 it.supplier_id, --Bug#2611529
3916 it.supplier_part_num,
3917 it.supplier_part_auxid, --Bug#2611529
3918 i.rt_item_id,
3919 decode(it.action, 'SYNC',
3920 decode(i.rt_item_id, NULL, 'ADD',
3921 decode(tl.rt_item_id, NULL, 'TRANSLATE', 'UPDATE')), it.action),
3922 decode(it.error_flag, 'Y', 'Y',
3923 decode(it.error_message, NULL, 'N', 'Y')),
3924 decode(it.error_flag, 'Y', i.rt_item_id,
3925 decode(it.action, 'SYNC',
3926 decode(i.rt_item_id, NULL, icx_por_itemid.NEXTVAL,
3927 i.rt_item_id), i.rt_item_id)),
3928 it.row_type,
3929 decode(it.contract_num, null, 'BULKLOAD', 'CONTRACT'),
3930 it.rt_category_id,
3931 tl.primary_category_id,
3932 it.category_name,
3933 it.org_id,
3934 it.price_list_id,
3935 it.supplier_site_id,
3936 i.extractor_updated_flag,
3937 decode(i.extractor_updated_flag, 'N', 'Y', 'Y', decode(icx_por_ext_item.getBulkLoadActiveFlag(it.action, i.rt_item_id), 'N', 'N', 'Y')),
3938 decode(it.required_descriptors, NULL, 'N', 'Y'),
3939 decode(it.required_tl_descriptors, NULL, 'N', 'Y'),
3940 it.unit_price,
3941 it.unit_of_measure
3942 ,it.system_action
3943 FROM ICX_CAT_ITEMS_GT it,
3944 icx_cat_items_b i, --Bug#2714487: dont join with category_items
3945 icx_cat_items_tlp tl
3946 WHERE it.supplier = i.supplier (+)
3947 AND it.supplier_part_num = i.supplier_part_num (+)
3948 AND it.supplier_part_auxid = i.supplier_part_auxid (+) --Bug#2611529
3949 AND it.org_id = i.org_id(+)
3950 AND i.rt_item_id = tl.rt_item_id (+)
3951 AND gJobLanguage = tl.language (+)
3952 AND gJobLanguage = it.language
3953 -- Bug# 3366614 sosingha: p_processed_flag willl have a value 'N' first and then 'D' if duplicates exists
3954 -- AND it.processed_flag = 'N';
3955 AND it.processed_flag = p_processed_flag;
3956
3957 vRowids dbms_sql.urowid_table;
3958 vSupplierPartNums dbms_sql.varchar2_table;
3959 vSuppliers dbms_sql.varchar2_table; --Bug#2729328
3960 vSupplierPartAuxids dbms_sql.varchar2_table; --Bug#2611529
3961 vCurrentItemIds dbms_sql.number_table;
3962 vSystemActions dbms_sql.varchar2_table;
3963 vHasErrors dbms_sql.varchar2_table;
3964 vItemIds dbms_sql.number_table;
3965 vRowTypes dbms_sql.varchar2_table;
3966 vPriceTypes dbms_sql.varchar2_table;
3967 vCategoryIds dbms_sql.number_table;
3968 vOldCategoryIds dbms_sql.number_table;
3969 vCategoryNames dbms_sql.varchar2_table;
3970 vOrgIds dbms_sql.number_table;
3971 vPricelistIds dbms_sql.number_table;
3972 vSupplierSiteIds dbms_sql.number_table;
3973 vExtractorUpdatedFlags dbms_sql.varchar2_table;
3974 vActiveFlags dbms_sql.varchar2_table;
3975 vMissingRequired dbms_sql.varchar2_table;
3976 vMissingTLRequired dbms_sql.varchar2_table;
3977 vUnitPrices dbms_sql.varchar2_table; --Bug#2733716
3978 vSupplierIds dbms_sql.number_table; --Bug#2611529
3979 vUoms dbms_sql.varchar2_table; --Bug#2611529
3980 vGTSystemAction dbms_sql.varchar2_table;
3981
3982 vCount PLS_INTEGER := 0;
3983 vASLCount PLS_INTEGER := 0;
3984 vHasAdd BOOLEAN := FALSE;
3985 vHasUpdate BOOLEAN := FALSE;
3986 vHasDelete BOOLEAN := FALSE;
3987 vHasTranslate BOOLEAN := FALSE;
3988 vErrorCount PLS_INTEGER := 0;
3989 vHasPrices BOOLEAN := FALSE;
3990
3991 vAbort BOOLEAN := FALSE;
3992 --Category_Change
3993 vChangedCatItemIndex PLS_INTEGER := 0;
3994 vCurrentBatch PLS_INTEGER := 0;
3995 xErrLoc PLS_INTEGER := 100;
3996 BEGIN
3997 clear_all;
3998 p_succ_line := 0;
3999 p_failed_line := 0;
4000
4001 xErrLoc := 200;
4002 build_root_sql('ICX_CAT_ITEMS_GT');
4003 xErrLoc := 300;
4004
4005 /* Bug# 3366614 sosingha: move this call to move_data as we have to always call this before calling move_items
4006 -- Reject all the duplicates first
4007 reject_duplicates;
4008 */
4009 xErrLoc := 400;
4010
4011 -- Loop thru the interface table and divide the items into the items batch
4012 -- and the error batch.
4013
4014 LOOP
4015 OPEN it_table_csr;
4016 xErrLoc := 500;
4017 --Bug#3570709
4018 --Before each batch instantiate the vChangedCatItemIndex to 0
4019 vChangedCatItemIndex := 0;
4020 vCurrentBatch := vCurrentBatch+1;
4021 --Add the Debug info for the current batch
4022 icx_por_ext_utl.debug(icx_por_ext_utl.MUST_LEVEL,
4023 'Currently processing batch number:'||to_char(vCurrentBatch) );
4024
4025 FETCH it_table_csr BULK COLLECT INTO
4026 --Bug#2729328
4027 vRowids, vSuppliers, vSupplierIds, vSupplierPartNums, vSupplierPartAuxids, --Bug#2611529
4028 vCurrentItemIds, vSystemActions, vHasErrors,
4029 vItemIds, vRowTypes, vPriceTypes, vCategoryIds, vOldCategoryIds,
4030 vCategoryNames, vOrgIds, vPricelistIds,
4031 vSupplierSiteIds, vExtractorUpdatedFlags, vActiveFlags,
4032 vMissingRequired, vMissingTLRequired, vUnitPrices, vUoms
4033 , vGTSystemAction
4034 LIMIT BATCH_SIZE;
4035 xErrLoc := 600;
4036
4037 CLOSE it_table_csr;
4038
4039 EXIT WHEN vRowids.COUNT = 0;
4040
4041 xErrLoc := 700;
4042 FOR i IN 1..vRowids.COUNT LOOP
4043
4044 -- deleting supplier part number that does not exist
4045 IF (vSystemActions(i) = 'DELETE' AND
4046 vSupplierPartNums(i) IS NOT NULL AND
4047 vCurrentItemIds(i) IS NULL) THEN
4048 vHasErrors(i) := 'Y';
4049 reject_line(vRowids(i), 'ITEM',
4050 --ErrMsg'.SUPPLIER_PART_NUM:ICX_POR_PRC_INVALID_SUP_PART');
4051 '.BUYER:ICX_POR_PRC_INVALID_SUP_PART');
4052 END IF;
4053 xErrLoc := 800;
4054
4055 -- Cannot delete extractor updated items.
4056 IF (vSystemActions(i) = 'DELETE' AND
4057 vSupplierPartNums(i) IS NOT NULL AND
4058 vCurrentItemIds(i) IS NOT NULL AND vExtractorUpdatedFlags(i) = 'Y') THEN
4059 vHasErrors(i) := 'Y';
4060 reject_line(vRowids(i), 'ITEM',
4061 '.SUPPLIER_PART_NUM:ICX_POR_DELETE_EXTRACTED_ITEM');
4062 END IF;
4063 xErrLoc := 900;
4064
4065 IF (vSystemActions(i) = 'ADD' AND
4066 (vMissingRequired(i) = 'Y' OR vMissingTLRequired(i) = 'Y')) OR
4067 (vSystemActions(i) = 'TRANSLATE' AND vMissingTLRequired(i) = 'Y') THEN
4068 vHasErrors(i) := 'Y';
4069 END IF;
4070
4071 xErrLoc := 1000;
4072 IF ( vGTSystemAction(i) IN ('UPDATE', 'DELETE') AND
4073 vCurrentItemIds(i) IS NULL ) THEN
4074 --This will happen only when item template files is used to update
4075 --an item that does not exists
4076 xErrLoc := 1005;
4077 vHasErrors(i) := 'Y';
4078 reject_line(vRowids(i), 'ITEM','BUYER:ICX_POR_PRC_INVALID_SUP_PART');
4079 -- Trying to add a new item without a price ? Reject the line !
4080 -- Item add without price is not allowed in IP.
4081 -- Bug#2729328
4082 -- Added the severity level when to log PRICE REQD Error
4083 ELSIF ( vSystemActions(i) = 'ADD' AND vUnitPrices(i) is null AND
4084 vSuppliers(i) IS NOT NULL AND vSupplierPartNums(i) IS NOT NULL AND
4085 vCategoryNames(i) IS NOT NULL ) THEN
4086 xErrLoc := 1010;
4087 vHasErrors(i) := 'Y';
4088 reject_line(vRowids(i), 'ITEM','.PRICE:ICX_POR_CAT_FIELD_REQUIRED');
4089 END IF;
4090
4091 xErrLoc := 1100;
4092 IF (vHasErrors(i) = 'Y') THEN
4093 p_failed_line := p_failed_line + 1;
4094 vErrorCount := vErrorCount + 1;
4095 gErrorRowids(vErrorCount) := vRowids(i);
4096
4097 xErrLoc := 1200;
4098 IF (p_max_failed_line >= 0 AND p_failed_line > p_max_failed_line) THEN
4099 -- Exceeded max failed lines. Save all the failed lines and return
4100 vAbort := TRUE;
4101 EXIT;
4102 END IF;
4103
4104 ELSE
4105 p_succ_line := p_succ_line + 1;
4106 vCount := vCount + 1;
4107 gRowids(vCount) := vRowids(i);
4108 gCurrentItemIds(vCount) := vCurrentItemIds(i);
4109 gSystemActions(vCount) := vSystemActions(i);
4110 gItemIds(vCount) := vItemIds(i);
4111 gPriceTypes(vCount) := vPriceTypes(i);
4112 gRowTypes(vCount) := vRowTypes(i);
4113 gCategoryIds(vCount) := vCategoryIds(i);
4114 gCategoryNames(vCount) := vCategoryNames(i);
4115 gOldCategoryIds(vCount) := vOldCategoryIds(i);
4116 gOrgIds(vCount) := vOrgIds(i);
4117 gPricelistIds(vCount) := vPricelistIds(i);
4118 gSupplierSiteIds(vCount) := vSupplierSiteIds(i);
4119 gExtractorUpdatedFlags(vCount) := vExtractorUpdatedFlags(i);
4120 gActiveFlags(vCount) := vActiveFlags(i);
4121 gUoms(vCount) := vUoms(i);
4122 gSupplierIds(vCount) := vSupplierIds(i);
4123 -- Category has changed ?
4124 -- Category_Change
4125 if(vOldCategoryIds(i) <> vCategoryIds(i)) then
4126 --Index of all gChanged.. plssql_tables should start with 1
4127 --instead of i
4128 vChangedCatItemIndex := vChangedCatItemIndex +1;
4129 --Debugging
4130 icx_por_ext_utl.debug(icx_por_ext_utl.MUST_LEVEL,
4131 'Category Change seen; vChangedCatItemIndex:' ||to_char(vChangedCatItemIndex)
4132 ||', variables at position i:'||to_char(i)
4133 ||': vOldCategoryIds:'||to_char(vOldCategoryIds(i))
4134 ||', vCategoryIds:' ||to_char(vCategoryIds(i))
4135 ||', gItemIds:' ||to_char(gItemIds(i))
4136 ||', vSystemActions:' ||vSystemActions(i) );
4137 gChangedCatItemIds(vChangedCatItemIndex) := gItemIds(i);
4138 gChangedOldCatIds(vChangedCatItemIndex) := vOldCategoryIds(i);
4139 gChangedNewCatIds(vChangedCatItemIndex) := vCategoryIds(i);
4140 gChangedCatActions(vChangedCatItemIndex) := vSystemActions(i);
4141 end if;
4142
4143 xErrLoc := 1300;
4144 IF (vSystemActions(i) = 'ADD') THEN
4145 vHasAdd := TRUE;
4146 ELSIF (vSystemActions(i) = 'UPDATE') THEN
4147 vHasUpdate := TRUE;
4148 ELSIF (vSystemActions(i) = 'DELETE') THEN
4149 vHasDelete := TRUE;
4150 ELSIF (vSystemActions(i) = 'TRANSLATE') THEN
4151 vHasTranslate := TRUE;
4152 END IF;
4153
4154 xErrLoc := 1300;
4155 IF (vRowTypes(i) IN ('ITEM_PRICE')) THEN
4156 vHasPrices := TRUE;
4157 END IF;
4158
4159 END IF;
4160
4161 END LOOP;
4162 xErrLoc := 1400;
4163
4164 IF (vCount > 0 AND NOT vAbort) THEN
4165 xErrLoc := 1500;
4166 process_batch_items(vHasAdd, vHasUpdate, vHasDelete,
4167 vHasTranslate, vHasPrices);
4168 xErrLoc := 1600;
4169 commit;
4170 clear_tables;
4171 vCount := 0;
4172 vHasAdd := FALSE;
4173 vHasUpdate := FALSE;
4174 vHasDelete := FALSE;
4175 vHasTranslate := FALSE;
4176 vHasPrices := FALSE;
4177 END IF;
4178
4179 xErrLoc := 1700;
4180 IF (vErrorCount > 0) THEN
4181 xErrLoc := 1800;
4182 -- Need to first populate the system action column in the it table
4183 FORALL i IN 1..vRowids.COUNT
4184 UPDATE ICX_CAT_ITEMS_GT
4185 SET system_action = vSystemActions(i)
4186 WHERE rowid = vRowids(i)
4187 AND vHasErrors(i) = 'Y';
4188
4189 xErrLoc := 1900;
4190 process_batch_item_errors(gErrorRowids);
4191 commit;
4192 clear_error_tables;
4193 vErrorCount := 0;
4194 END IF;
4195
4196 xErrLoc := 2000;
4197 IF (vRowids.COUNT < BATCH_SIZE OR vAbort) THEN
4198 EXIT;
4199 -- Update the processed_flag to null
4200 ELSE
4201 xErrLoc := 2100;
4202 FORALL i IN 1..vRowids.COUNT
4203 UPDATE ICX_CAT_ITEMS_GT
4204 SET processed_flag = 'Y' --Bug#2958208: set to "Y" instead of null.
4205 WHERE rowid = vRowids(i);
4206
4207 COMMIT;
4208 END IF;
4209
4210 END LOOP;
4211 xErrLoc := 2200;
4212
4213 EXCEPTION
4214 WHEN OTHERS THEN
4215 IF (it_table_csr%ISOPEN) THEN
4216 CLOSE it_table_csr;
4217 END IF;
4218
4219 ROLLBACK;
4220
4221 RAISE_APPLICATION_ERROR(-20000,
4222 'Exception at ICX_POR_ITEM_UPLOAD.move_items('
4223 || xErrLoc || '): ' || SQLERRM);
4224 END move_items;
4225
4226 /* Processes the lines for a given job */
4227 --Bug#2611529: Added another parameter for catalog name
4228 PROCEDURE move_data(p_app_name IN VARCHAR2, p_request_id IN NUMBER,
4229 p_data_type IN VARCHAR2, p_supplier_id IN NUMBER, p_langs IN LANG_ARRAY,
4230 p_user_id IN NUMBER, p_user_login IN NUMBER, p_batch_size IN NUMBER,
4231 p_succ_line OUT NOCOPY NUMBER, p_failed_line OUT NOCOPY NUMBER,
4232 p_max_failed_line IN NUMBER DEFAULT -1,
4233 p_catalog_name IN VARCHAR2, --Bug#2611529
4234 p_negotiated_price IN VARCHAR2 --Bug#2611529
4235 ) IS
4236 xErrLoc PLS_INTEGER := 100;
4237 l_cursor INTEGER;
4238 l_ret INTEGER;
4239
4240 --Bug#2611529
4241 succ_line PLS_INTEGER := 0;
4242 failed_line PLS_INTEGER := 0;
4243
4244 BEGIN
4245 -- For any PRICE/ITEM_PRICE, org_id, price_list_id, and supplier_site_id should be populated
4246 -- if they are valid.
4247
4248 IF (p_batch_size > 0) THEN
4249 BATCH_SIZE := p_batch_size;
4250 ELSE
4251 BATCH_SIZE := 1000;
4252 END IF;
4253
4254 --Add the Debug info for the current batch
4255 icx_por_ext_utl.debug(icx_por_ext_utl.MUST_LEVEL,
4256 'BATCH_SIZE set to:'||to_char(BATCH_SIZE) );
4257
4258 CACHE_SIZE := 2*BATCH_SIZE;
4259
4260 gJobNumber := p_request_id;
4261 --Bug#2611529 gSupplierId := p_supplier_id;
4262
4263 SELECT language_code INTO gBaseLanguage
4264 FROM fnd_languages
4265 WHERE installed_flag = 'B';
4266
4267 --Bug#2611529 gJobLanguage := p_language;
4268 gUserId := p_user_id;
4269 gUserLogin := p_user_login;
4270 gCatalogName := p_catalog_name; -- Bug#2611529
4271
4272 -- a Yes/No lookup already exists with Y/N(instead of 1/0)
4273 if(p_negotiated_price = 'Y') then -- Bug#3107596
4274 gNegotiatedPrice := 1;
4275 else
4276 gNegotiatedPrice := 0;
4277 end if;
4278
4279 p_succ_line := 0;
4280 p_failed_line := 0;
4281
4282 FOR i IN 1..p_langs.COUNT loop
4283 gJobLanguage := p_langs(i);
4284
4285 IF (p_data_type = 'ITEM') THEN
4286 xErrLoc := 200;
4287 -- Bug# 3366614 sosingha: Reject all the duplicates first then call move_items with processed_flag 'N' and then with processed_flag 'D'
4288 reject_duplicates;
4289 move_items(succ_line, failed_line, p_max_failed_line, 'N');
4290 p_succ_line := p_succ_line + succ_line;
4291 p_failed_line := p_failed_line + failed_line;
4292 IF(gDuplicatesExists = true) THEN
4293 move_items(succ_line, failed_line, p_max_failed_line, 'D');
4294 p_succ_line := p_succ_line + succ_line;
4295 p_failed_line := p_failed_line + failed_line;
4296 END IF;
4297 ELSIF (p_data_type = 'PRICE') THEN
4298 xErrLoc := 300;
4299 move_prices(p_succ_line, p_failed_line, p_max_failed_line);
4300 END IF;
4301 END LOOP;
4302
4303
4304 EXCEPTION
4305 WHEN OTHERS THEN
4306 ROLLBACK;
4307
4308 RAISE_APPLICATION_ERROR(-20000,
4309 'Exception at ICX_POR_ITEM_UPLOAD.move_data('
4310 || xErrLoc || '): ' || SQLERRM);
4311 END move_data;
4312
4313 PROCEDURE move_unsaved_failed_lines(p_request_id IN NUMBER,
4314 p_data_type IN VARCHAR2, p_supplier_id IN NUMBER,
4315 p_user_id IN NUMBER, p_user_login IN NUMBER,
4316 p_language IN VARCHAR2, p_lines_to_save_count IN NUMBER,
4317 p_failed_lines_saved_count OUT NOCOPY NUMBER) IS
4318 TYPE FailedLinesCsrTyp IS REF CURSOR;
4319 vFailedLinesCsr FailedLinesCsrTyp;
4320 vTableName VARCHAR2(30);
4321 vSQL VARCHAR2(2000);
4322 vRowId UROWID;
4323 vErrorCount NUMBER := 0;
4324 xErrLoc PLS_INTEGER := 100;
4325 v_lines_to_save_count NUMBER := p_lines_to_save_count;
4326 BEGIN
4327 BATCH_SIZE := 2500;
4328
4329 gJobNumber := p_request_id;
4330 --Bug#2611529 gSupplierId := p_supplier_id;
4331
4332 SELECT language_code INTO gBaseLanguage
4333 FROM fnd_languages
4334 WHERE installed_flag = 'B';
4335
4336 gJobLanguage := p_language;
4337 gUserId := p_user_id;
4338 gUserLogin := p_user_login;
4339
4340 clear_all;
4341
4342 IF (p_data_type = 'ITEM') THEN
4343 vTableName := 'ICX_CAT_ITEMS_GT';
4344 ELSIF (p_data_type = 'PRICE') THEN
4345 vTableName := 'ICX_CAT_PRICES_GT';
4346 ELSE
4347 p_failed_lines_saved_count := 0;
4348 RETURN;
4349 END IF;
4350
4351 vSQL := 'SELECT rowid FROM ' || vTableName ||
4352 ' WHERE error_flag IS NULL and (error_message IS NOT NULL OR ' ||
4353 ' (system_action IN (''ADD'') and required_descriptors IS NOT NULL) OR ' ||
4354 ' (system_action IN (''ADD'', ''TRANSLATE'') ' ||
4355 ' and required_tl_descriptors IS NOT NULL))';
4356
4357 OPEN vFailedLinesCsr FOR vSQL;
4358
4359 LOOP
4360 FETCH vFailedLinesCsr INTO vRowId;
4361 EXIT WHEN vFailedLinesCsr%NOTFOUND OR vErrorCount = v_lines_to_save_count;
4362
4363 vErrorCount := vErrorCount + 1;
4364 gErrorRowids(vErrorCount) := vRowid;
4365
4366 IF (vErrorCount >= BATCH_SIZE) THEN
4367
4368 IF (p_data_type = 'ITEM') THEN
4369 process_batch_item_errors(gErrorRowids);
4370 ELSE
4371 process_batch_price_errors(gErrorRowids);
4372 END IF;
4373
4374 commit;
4375 clear_error_tables;
4376 p_failed_lines_saved_count := p_failed_lines_saved_count + vErrorCount;
4377 v_lines_to_save_count := v_lines_to_save_count - vErrorCount;
4378 vErrorCount := 0;
4379 END IF;
4380
4381 END LOOP;
4382
4383 IF (vErrorCount > 0) THEN
4384
4385 IF (p_data_type = 'ITEM') THEN
4386 process_batch_item_errors(gErrorRowids);
4387 ELSE
4388 process_batch_price_errors(gErrorRowids);
4389 END IF;
4390
4391 commit;
4392 clear_error_tables;
4393 p_failed_lines_saved_count := p_failed_lines_saved_count + vErrorCount;
4394 vErrorCount := 0;
4395 END IF;
4396
4397 EXCEPTION
4398 WHEN OTHERS THEN
4399 ROLLBACK;
4400
4401 RAISE_APPLICATION_ERROR(-20000,
4402 'Exception at ICX_POR_ITEM_UPLOAD.move_unsaved_failed_lines('
4403 || xErrLoc || '): ' || SQLERRM);
4404 END move_unsaved_failed_lines;
4405
4406 PROCEDURE create_price_list(p_price_list_name IN VARCHAR2,
4407 p_buyer_id in NUMBER,
4408 p_supplier_id IN NUMBER,
4409 p_currency IN VARCHAR2,
4410 p_begindate IN VARCHAR2,
4411 p_enddate IN VARCHAR2,
4412 p_user_id IN NUMBER,
4413 p_request_id IN NUMBER,
4414 p_header_id OUT NOCOPY NUMBER,
4415 p_type OUT NOCOPY VARCHAR2) IS
4416 xErrLoc INTEGER;
4417 l_type VARCHAR2(1);
4418 invalid_buyer EXCEPTION;
4419 BEGIN
4420 xErrLoc := 100;
4421
4422 p_type := 'B';
4423
4424 xErrLoc := 200;
4425
4426 INSERT INTO icx_cat_price_lists (price_list_id, name, supplier_id,
4427 buyer_id, description, currency, begindate, enddate, action, status,
4428 type, parent_header_id, creation_date, published_date, approval_date,
4429 created_by, last_update_date, last_updated_by,
4430 last_update_login, request_id) values
4431 (icx_por_price_lists_s.nextval, p_price_list_name,
4432 p_supplier_id, p_buyer_id,null,p_currency,
4433 to_date(p_begindate, DEFAULT_DATE_FORMAT),
4434 to_date(p_enddate, DEFAULT_DATE_FORMAT),
4435 'ADD', 'APPROVED', p_type, null, sysdate, sysdate, sysdate,
4436 p_user_id, sysdate, p_user_id, p_user_id, p_request_id)
4437 RETURNING price_list_id INTO p_header_id;
4438
4439 xErrLoc := 300;
4440 commit;
4441
4442
4443 EXCEPTION
4444 WHEN OTHERS THEN
4445 ROLLBACK;
4446
4447 RAISE_APPLICATION_ERROR(-20000,
4448 'Exception at ICX_POR_ITEM_UPLOAD.create_price_list('
4449 || xErrLoc || '): ' || SQLERRM);
4450 END create_price_list;
4451
4452 PROCEDURE update_price_list( p_header_id IN NUMBER,
4453 p_begindate IN VARCHAR2,
4454 p_enddate IN VARCHAR2,
4455 p_user_id IN NUMBER,
4456 p_request_id IN NUMBER) IS
4457 xErrLoc INTEGER;
4458 p_type icx_por_price_lists.type%TYPE;
4459 BEGIN
4460 xErrLoc := 100;
4461 -- Delete unapproved price lists first
4462 DELETE FROM icx_cat_price_lists
4463 WHERE price_list_id = p_header_id
4464 AND status IN ('UNPUBLISHED', 'PUBLISHED');
4465
4466 xErrLoc := 200;
4467
4468 INSERT INTO icx_cat_price_lists (price_list_id, name, supplier_id,
4469 buyer_id, description, currency, begindate, enddate, action, status,
4470 type, parent_header_id,
4471 creation_date, created_by, last_update_date, last_updated_by,
4472 last_update_login, request_id)
4473 SELECT pl.price_list_id , pl.name,
4474 pl.supplier_id, pl.buyer_id, pl.description, pl.currency,
4475 decode(p_begindate, null, pl.begindate,
4476 to_date(p_begindate, DEFAULT_DATE_FORMAT)),
4477 decode(p_enddate, null, pl.enddate,
4478 to_date(p_enddate, DEFAULT_DATE_FORMAT)) ,
4479 'UPDATE', 'UNPUBLISHED',
4480 pl.type, pl.parent_header_id, pl.creation_date,
4481 pl.created_by, sysdate, p_user_id, p_user_id, p_request_id
4482 FROM icx_cat_price_lists pl
4483 WHERE pl.price_list_id = p_header_id
4484 AND pl.status = 'APPROVED';
4485
4486 -- added by bluk for group pricing project
4487 -- propagate changes for child lists
4488 xErrLoc := 300;
4489
4490 SELECT type INTO p_type FROM icx_cat_price_lists
4491 WHERE price_list_id = p_header_id
4492 AND status = 'APPROVED';
4493
4494 xErrLoc := 310;
4495 commit;
4496
4497 EXCEPTION
4498 WHEN OTHERS THEN
4499 ROLLBACK;
4500
4501 RAISE_APPLICATION_ERROR(-20000,
4502 'Exception at ICX_POR_ITEM_UPLOAD.update_price_list('
4503 || xErrLoc || '): ' || SQLERRM);
4504 END update_price_list;
4505
4506 PROCEDURE delete_price_list( p_header_id IN NUMBER ) IS
4507 xErrLoc INTEGER;
4508 p_type icx_cat_price_lists.type%TYPE;
4509 BEGIN
4510 xErrLoc := 100;
4511 DELETE FROM icx_cat_price_lists
4512 WHERE price_list_id = p_header_id;
4513 commit;
4514
4515 EXCEPTION
4516 WHEN OTHERS THEN
4517 ROLLBACK;
4518
4519 RAISE_APPLICATION_ERROR(-20000,
4520 'Exception at ICX_POR_ITEM_UPLOAD.delete_price_list('
4521 || xErrLoc || '): ' || SQLERRM);
4522 END delete_price_list;
4523
4524 --Bug#2709997: Pass in supplier site code also
4525 PROCEDURE save_failed_price(p_request_id IN NUMBER,
4526 p_line_number IN NUMBER,
4527 p_action IN VARCHAR2,
4528 p_amount IN VARCHAR2,
4529 p_currency IN VARCHAR2,
4530 p_uom IN VARCHAR2,
4531 p_buyer_name IN VARCHAR2,
4532 p_supplier_name IN VARCHAR2,
4533 p_supplier_part_num IN VARCHAR2,
4534 p_price_list_name IN VARCHAR2,
4535 p_price_code IN VARCHAR2,
4536 p_supplier_comments IN VARCHAR2,
4537 p_begin_date IN VARCHAR2,
4538 p_end_date IN VARCHAR2,
4539 p_supplier_part_auxid IN VARCHAR2,
4540 p_supplier_site_code IN VARCHAR2) IS
4541 l_progress VARCHAR2(5) := '100';
4542 BEGIN
4543
4544 --IF p_amount IS NOT NULL THEN
4545 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4546 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4547 p_line_number, p_action, 'PRICE', 'PRICE', p_amount);
4548 --END IF;
4549
4550 l_progress := '110';
4551
4552 --IF p_currency IS NOT NULL THEN
4553 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4554 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4555 p_line_number, p_action, 'PRICE', 'CURRENCY', p_currency);
4556 --END IF;
4557
4558 --IF p_uom IS NOT NULL THEN
4559 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4560 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4561 p_line_number, p_action, 'PRICE', 'UOM', p_uom);
4562 --END IF;
4563
4564 --IF p_buyer_name IS NOT NULL THEN
4565 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4566 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4567 p_line_number, p_action, 'PRICE', 'BUYER', p_buyer_name);
4568 --END IF;
4569
4570 --IF p_supplier_name IS NOT NULL THEN
4571 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4572 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4573 p_line_number, p_action, 'PRICE', 'SUPPLIER', p_supplier_name);
4574 --END IF;
4575
4576 --IF p_supplier_part_num IS NOT NULL THEN
4577 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4578 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4579 p_line_number, p_action, 'PRICE', 'SUPPLIER_PART_NUM', p_supplier_part_num);
4580 --END IF;
4581
4582 --Bug#2611529
4583 --IF p_supplier_part_auxid IS NOT NULL THEN
4584 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4585 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4586 p_line_number, p_action, 'PRICE', 'SUPPLIER_PART_AUXID', p_supplier_part_auxid);
4587 --END IF;
4588
4589 --Bug#2709997
4590 --IF p_supplier_site_code IS NOT NULL THEN
4591 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4592 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4593 p_line_number, p_action, 'PRICE', 'SUPPLIER_SITE', p_supplier_site_code);
4594 --END IF;
4595
4596 --IF p_price_list_name IS NOT NULL THEN
4597 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4598 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4599 p_line_number, p_action, 'PRICE', 'PRICELIST', p_price_list_name);
4600 --END IF;
4601
4602 /* Bug#2729328
4603 IF p_price_code IS NOT NULL THEN
4604 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4605 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4606 p_line_number, p_action, 'PRICE', 'PRICE', p_price_code);
4607 END IF;
4608
4609 IF p_supplier_comments IS NOT NULL THEN
4610 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4611 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4612 p_line_number, p_action, 'PRICE', 'SUPPLIERCOMMENTS', p_price_list_name);
4613 END IF;
4614
4615 IF p_begin_date IS NOT NULL THEN
4616 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4617 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4618 p_line_number, p_action, 'PRICE', 'BEGINDATE', p_begin_date);
4619 END IF;
4620
4621 IF p_end_date IS NOT NULL THEN
4622 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4623 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4624 p_line_number, p_action, 'PRICE', 'ENDDATE', p_end_date);
4625 END IF;
4626 */
4627
4628 END save_failed_price;
4629
4630 PROCEDURE save_failed_price_list(p_request_id IN NUMBER,
4631 p_line_number IN NUMBER,
4632 p_action IN VARCHAR2,
4633 p_price_list_name IN VARCHAR2,
4634 p_currency IN VARCHAR2,
4635 p_buyer_name IN VARCHAR2,
4636 p_supplier_name IN VARCHAR2,
4637 p_begin_date IN VARCHAR2,
4638 p_end_date IN VARCHAR2) IS
4639 l_progress VARCHAR2(5) := '100';
4640 BEGIN
4641 IF p_price_list_name IS NOT NULL THEN
4642 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4643 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4644 p_line_number, p_action, 'PRICELISTHEADER', 'PRICELIST', p_price_list_name);
4645 END IF;
4646
4647 l_progress := '110';
4648
4649 IF p_currency IS NOT NULL THEN
4650 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4651 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4652 p_line_number, p_action, 'PRICELISTHEADER', 'CURRENCY', p_currency);
4653 END IF;
4654
4655 IF p_buyer_name IS NOT NULL THEN
4656 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4657 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4658 p_line_number, p_action, 'PRICELISTHEADER', 'BUYER', p_buyer_name);
4659 END IF;
4660
4661 IF p_supplier_name IS NOT NULL THEN
4662 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4663 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4664 p_line_number, p_action, 'PRICELISTHEADER', 'SUPPLIER', p_supplier_name);
4665 END IF;
4666
4667 IF p_begin_date IS NOT NULL THEN
4668 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4669 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4670 p_line_number, p_action, 'PRICELISTHEADER', 'BEGINDATE', p_begin_date);
4671 END IF;
4672
4673 IF p_end_date IS NOT NULL THEN
4674 INSERT INTO icx_por_failed_lines (job_number, line_number, action,
4675 row_type, descriptor_key, descriptor_value) VALUES (p_request_id,
4676 p_line_number, p_action, 'PRICELISTHEADER', 'ENDDATE', p_end_date);
4677 END IF;
4678
4679 END save_failed_price_list;
4680
4681 --
4682 -- Move the error message from ICX_POR_CONTRACT_REFERENCE table
4683 -- into ICX_POR_FAILED_LINES and ICX_POR_FAILED_LINE_MESSAGES table
4684 --
4685 -- bug#1968033
4686 PROCEDURE save_failed_admin_data (p_request_id in number)
4687 IS
4688 --sbgeorge
4689 --changed to have a cursor instead of dynamic sql
4690 cursor c_contract_references is
4691 select request_id, line_number, buyer_id, buyer_name,
4692 contract_reference_id, contract_reference_num, supplier_id,
4693 supplier_site, pricelist_id, currency_code, error_message
4694 from icx_por_contract_references
4695 where request_id = p_request_id;
4696
4697 l_progress varchar2(10) := '100';
4698 BEGIN
4699 FOR l_contracts in c_contract_references LOOP
4700 l_progress := '200';
4701 if (l_contracts.error_message is not null) then
4702 save_failed_admin( l_contracts.request_id,
4703 l_contracts.line_number,
4704 l_contracts.buyer_name,
4705 l_contracts.contract_reference_num);
4706
4707 l_progress := '300';
4708 ICX_POR_ITEM_UPLOAD.save_failed_line_message(l_contracts.request_id,
4709 l_contracts.line_number,
4710 l_contracts.error_message);
4711 end if;
4712 END LOOP;
4713
4714 EXCEPTION
4715 WHEN OTHERS then
4716 RAISE_APPLICATION_ERROR (-20000, 'Exception at '||
4717 'ICX_POR_ITEM_UPLOAD.save_failed_admin_data '||
4718 '(ErrLoc = ' || l_progress ||') ' ||
4719 'SQL Error : ' || SQLERRM);
4720
4721 END save_failed_admin_data;
4722
4723 -- Check if the descriptor value is updateable.
4724 -- Certain descriptors like internal item number, CONTRACT_PRICE
4725 -- are non updateable
4726 FUNCTION can_update(descriptor_key IN VARCHAR2) return boolean
4727 is
4728 BEGIN
4729 if (descriptor_key in
4730 (
4731 'UOM', 'PRICE',
4732 'CURRENCY', 'INTERNAL_ITEM_NUM', 'CONTRACT_NUM',
4733 'CONTRACT_LINE', 'CONTRACT_PRICE', 'CONTRACT_CURRENCY',
4734 'CONTRACT_RATE_TYPE', 'CONTRACT_RATE_DATE', 'CONTRACT_RATE',
4735 'FUNCTIONAL_PRICE', 'FUNCTIONAL_CURRENCY', 'SUPPLIER_SITE',
4736 'BUYER', 'PRICELIST'))
4737 then
4738 return false;
4739 else
4740 return true;
4741 end if;
4742 END can_update;
4743
4744 --
4745 -- Validate the Contract Reference section
4746 -- Validate if all the contract references have the same supplier
4747 -- and currency
4748 --
4749 PROCEDURE validate_contracts(p_request_id in number, p_line_number in number,
4750 p_buyer in varchar2, p_contract in varchar2,
4751 p_supplier OUT NOCOPY varchar2, p_currency OUT NOCOPY varchar2,
4752 p_error_message OUT NOCOPY varchar2)
4753 IS
4754
4755 l_progress INTEGER := 0;
4756 l_count INTEGER := 0;
4757 l_valid boolean := true;
4758
4759 BEGIN
4760 l_progress := 110;
4761 p_error_message := null;
4762 p_supplier := null;
4763 p_currency := null;
4764
4765 select count(distinct supplier_id)
4766 into l_count
4767 from icx_por_contract_references
4768 where supplier_id is not null
4769 and request_id=p_request_id;
4770
4771 l_progress := 120;
4772 -- Are there multiple Suppliers ?
4773 -- Error out..Dont even check for other errors(like multiple currencies)
4774 if l_count > 1 then
4775 --ErrMsg p_error_message := '.SUPPLIER:ICX_POR_DUPE_SUPPLIER';
4776 p_error_message := '.CONTRACT_NUM:ICX_POR_DUPE_SUPPLIER';
4777 l_progress := 130;
4778 l_valid := false;
4779 else
4780 -- Are there multiple currencies ?
4781 -- Error out..
4782 select count(distinct currency_code)
4783 into l_count
4784 from icx_por_contract_references
4785 where currency_code is not null
4786 and request_id=p_request_id;
4787 l_progress := 140;
4788
4789 if l_count > 1 then
4790 -- Bug#2075574
4791 --ErrMsg p_error_message := '.CURRENCY:ICX_POR_DUPE_CURRENCY';
4792 p_error_message := '.CONTRACT_NUM:ICX_POR_DUPE_CURRENCY';
4793 l_progress := 150;
4794 l_valid := false;
4795 end if;
4796 end if;
4797 -- Bug#2075574
4798 -- Dont add failed lines here. Will be added in the Admin validator!
4799 l_progress := 150;
4800 if l_valid = true then
4801 -- Get the Supplier Name and Currency
4802 get_global_supplier_currency(p_request_id, p_supplier, p_currency);
4803
4804 end if;
4805
4806
4807 exception
4808 when others then
4809 Debug('[validate_contracts-'||l_progress||'] '||SQLERRM);
4810 RAISE_APPLICATION_ERROR (-20000, 'Exception at ' ||
4811 'ICX_POR_ITEM_UPLOAD.validate_contracts' ||
4812 '(ErrLoc = ' || l_progress ||') ' ||
4813 'SQL Error : ' || SQLERRM);
4814
4815 end ;
4816
4817 --
4818 -- Get the Global Supplier and currency that is used by ALL the
4819 -- Contract references in the contract section
4820 --
4821 PROCEDURE get_global_supplier_currency( p_request_id IN NUMBER,
4822 p_supplier OUT NOCOPY VARCHAR,
4823 p_currency OUT NOCOPY VARCHAR)
4824 IS
4825 l_progress INTEGER := 0;
4826 BEGIN
4827
4828 -- Get the Globally used Supplier in the Contract
4829 -- Reference Section: Get for Valid contracts only
4830
4831 select distinct vendor_name into p_supplier
4832 from po_vendors poV, icx_por_contract_references icxC
4833 where poV.vendor_id = icxC.supplier_id
4834 and icxC.request_id=p_request_id
4835 and icxC.supplier_id is not null;
4836
4837 l_progress := 220;
4838
4839 -- Get the Globally used Currency in the Contract
4840 -- Reference Section: Get for Valid contracts only
4841
4842 select distinct currency_code into p_currency
4843 from icx_por_contract_references icxC
4844 where icxC.request_id=p_request_id
4845 and icxC.currency_code is not null;
4846
4847 l_progress := 230;
4848
4849 exception
4850 when others then
4851 Debug('[get_global_supplier_currency-'||l_progress||'] '||SQLERRM);
4852 -- Bug#2025348
4853 p_supplier := null;
4854 p_currency := null;
4855 END;
4856
4857 --
4858 -- Get the Number of Contract references that have passed/failed
4859 -- during Validation
4860 --
4861 PROCEDURE get_contracts_pass_failed( p_request_id IN NUMBER,
4862 p_succ_count OUT NOCOPY number,
4863 p_failed_count OUT NOCOPY number)
4864 IS
4865 l_progress VARCHAR2(5) := '100';
4866 BEGIN
4867
4868 p_succ_count := 0;
4869 p_failed_count := 0;
4870
4871 -- Get the Number of successful Contract references
4872 select count(0) into p_succ_count
4873 from icx_por_contract_references
4874 where error_message is null
4875 and request_id = p_request_id;
4876
4877 l_progress := '110';
4878
4879 -- Get the Number of Failed Contract references
4880 select count(0) into p_failed_count
4881 from icx_por_contract_references
4882 where error_message is not null
4883 and request_id = p_request_id;
4884
4885 l_progress := '120';
4886
4887 EXCEPTION
4888 WHEN OTHERS then
4889 RAISE_APPLICATION_ERROR
4890 (-20000, 'Exception at ICX_POR_ITEM_UPLOAD.get_contracts_pass_failed(ErrLoc = ' || l_progress ||') ' ||
4891 'SQL Error : ' || SQLERRM);
4892 END;
4893
4894 --
4895 -- Save the Failed Line for Contract reference section errors.
4896 --
4897 PROCEDURE save_failed_admin(p_request_id IN NUMBER,
4898 p_line_number IN NUMBER,
4899 p_buyer IN VARCHAR2,
4900 p_contract_ref_num IN VARCHAR2
4901 ) IS
4902 l_progress VARCHAR2(5) := '100';
4903 BEGIN
4904
4905 IF p_buyer IS NOT NULL THEN
4906 INSERT INTO icx_por_failed_lines
4907 (job_number, line_number, action, row_type, descriptor_key, descriptor_value)
4908 VALUES
4909 (p_request_id, p_line_number, 'SYNC', 'ADMIN', 'BUYER', p_buyer);
4910 END IF;
4911
4912 l_progress := '110';
4913
4914 IF p_contract_ref_num IS NOT NULL THEN
4915 INSERT INTO icx_por_failed_lines
4916 (job_number, line_number, action, row_type, descriptor_key, descriptor_value)
4917 VALUES
4918 (p_request_id, p_line_number, 'SYNC', 'ADMIN', 'CONTRACT_NUM', p_contract_ref_num);
4919 END IF;
4920
4921 l_progress := '120';
4922
4923 EXCEPTION
4924 WHEN OTHERS then
4925 RAISE_APPLICATION_ERROR (-20000, 'Exception at ' ||
4926 'ICX_POR_ITEM_UPLOAD.save_failed_admin' ||
4927 '(ErrLoc = ' || l_progress ||') ' ||
4928 'SQL Error : ' || SQLERRM);
4929 END save_failed_admin;
4930
4931 -- Reject the Catalog
4932 -- This is used only for Contract reference sections
4933 -- When the Contract supplier/currency does not match with items section
4934 -- the Catalog is rejected
4935 --
4936 PROCEDURE reject_catalog(p_request_id IN NUMBER,
4937 p_line_type IN VARCHAR2,
4938 p_descriptor_key IN VARCHAR2,
4939 p_descriptor_val IN VARCHAR2,
4940 p_error_message IN VARCHAR2
4941 )
4942 IS
4943 l_progress VARCHAR2(5) := '100';
4944 BEGIN
4945 l_progress := '110';
4946 -- When a catalog is rejected, Dont show any other Failed Lines
4947 DELETE FROM icx_por_failed_lines where job_number = p_request_id;
4948
4949 l_progress := '120';
4950 -- Insert Buyer Reference
4951 INSERT INTO icx_por_failed_lines
4952 (job_number, line_number, action, row_type, descriptor_key, descriptor_value)
4953 --Bug#2729328
4954 --VALUES (p_request_id, 1, 'SYNC', p_line_type, p_descriptor_key, p_descriptor_key);
4955 VALUES (p_request_id, 1, 'SYNC', p_line_type, p_descriptor_key, p_descriptor_val);
4956
4957 -- Delete all the Messages for the request_id#
4958 delete from icx_por_failed_line_messages
4959 where job_number = p_request_id;
4960
4961 -- Save the Failed Line message now
4962 ICX_POR_ITEM_UPLOAD.save_failed_line_message(p_request_id,
4963 1,
4964 p_error_message);
4965
4966 EXCEPTION
4967 WHEN OTHERS then
4968 RAISE_APPLICATION_ERROR (-20000, 'Exception at ' ||
4969 'ICX_POR_ITEM_UPLOAD.reject_catalog' ||
4970 '(ErrLoc = ' || l_progress ||') ' ||
4971 'SQL Error : ' || SQLERRM);
4972 END reject_catalog;
4973
4974
4975 -- Category_Change
4976 PROCEDURE handle_category_change (p_action IN VARCHAR2) IS
4977 l_progress VARCHAR2(5) := '100';
4978
4979 xLangArray DBMS_SQL.VARCHAR2_TABLE;
4980 CURSOR langCsr IS
4981 SELECT language_code
4982 FROM fnd_languages
4983 WHERE installed_flag in ('B', 'I')
4984 AND language_code <> gJobLanguage;
4985
4986 BEGIN
4987
4988 l_progress := '100';
4989
4990 -- Fetch all the distinct category ids that have changed for the items
4991 -- in this batch.
4992
4993 if ( gChangedCatItemIds.COUNT > 0 ) then
4994 icx_por_ext_utl.debug(icx_por_ext_utl.MUST_LEVEL, 'handle_category_change called with action:'||p_action
4995 ||', gChangedCatItemIds.COUNT:'||to_char(gChangedCatItemIds.COUNT)
4996 ||', gChangedOldCatIds.COUNT:'||to_char(gChangedOldCatIds.COUNT)
4997 ||', gChangedNewCatIds.COUNT:'||to_char(gChangedNewCatIds.COUNT)
4998 ||', gChangedCatActions.COUNT:'||to_char(gChangedCatActions.COUNT));
4999 end if;
5000
5001 FORALL i in 1..gChangedCatItemIds.COUNT
5002 update icx_cat_ext_items_tlp set rt_category_id=gChangedNewCatIds(i),
5003 text_cat_attribute1 = null, text_cat_attribute2 = null, text_cat_attribute3 = null,
5004 text_cat_attribute4 = null, text_cat_attribute5 = null, text_cat_attribute6 = null,
5005 text_cat_attribute7 = null, text_cat_attribute8 = null, text_cat_attribute9 = null,
5006 text_cat_attribute10 = null, text_cat_attribute11 = null, text_cat_attribute12 = null,
5007 text_cat_attribute13 = null, text_cat_attribute14 = null, text_cat_attribute15 = null,
5008 text_cat_attribute16 = null, text_cat_attribute17 = null, text_cat_attribute18 = null,
5009 text_cat_attribute19 = null, text_cat_attribute20 = null, text_cat_attribute21 = null,
5010 text_cat_attribute22 = null, text_cat_attribute23 = null, text_cat_attribute24 = null,
5011 text_cat_attribute25 = null, text_cat_attribute26 = null, text_cat_attribute27 = null,
5012 text_cat_attribute28 = null, text_cat_attribute29 = null, text_cat_attribute30 = null,
5013 text_cat_attribute31 = null, text_cat_attribute32 = null, text_cat_attribute33 = null,
5014 text_cat_attribute34 = null, text_cat_attribute35 = null, text_cat_attribute36 = null,
5015 text_cat_attribute37 = null, text_cat_attribute38 = null, text_cat_attribute39 = null,
5016 text_cat_attribute40 = null, text_cat_attribute41 = null, text_cat_attribute42 = null,
5017 text_cat_attribute43 = null, text_cat_attribute44 = null, text_cat_attribute45 = null,
5018 text_cat_attribute46 = null, text_cat_attribute47 = null, text_cat_attribute48 = null,
5019 text_cat_attribute49 = null, text_cat_attribute50 = null,
5020 num_cat_attribute1 = null, num_cat_attribute2 = null, num_cat_attribute3 = null,
5021 num_cat_attribute4 = null, num_cat_attribute5 = null, num_cat_attribute6 = null,
5022 num_cat_attribute7 = null, num_cat_attribute8 = null, num_cat_attribute9 = null,
5023 num_cat_attribute10 = null, num_cat_attribute11 = null, num_cat_attribute12 = null,
5024 num_cat_attribute13 = null, num_cat_attribute14 = null, num_cat_attribute15 = null,
5025 num_cat_attribute16 = null, num_cat_attribute17 = null, num_cat_attribute18 = null,
5026 num_cat_attribute19 = null, num_cat_attribute20 = null, num_cat_attribute21 = null,
5027 num_cat_attribute22 = null, num_cat_attribute23 = null, num_cat_attribute24 = null,
5028 num_cat_attribute25 = null, num_cat_attribute26 = null, num_cat_attribute27 = null,
5029 num_cat_attribute28 = null, num_cat_attribute29 = null, num_cat_attribute30 = null,
5030 num_cat_attribute31 = null, num_cat_attribute32 = null, num_cat_attribute33 = null,
5031 num_cat_attribute34 = null, num_cat_attribute35 = null, num_cat_attribute36 = null,
5032 num_cat_attribute37 = null, num_cat_attribute38 = null, num_cat_attribute39 = null,
5033 num_cat_attribute40 = null, num_cat_attribute41 = null, num_cat_attribute42 = null,
5034 num_cat_attribute43 = null, num_cat_attribute44 = null, num_cat_attribute45 = null,
5035 num_cat_attribute46 = null, num_cat_attribute47 = null, num_cat_attribute48 = null,
5036 num_cat_attribute49 = null, num_cat_attribute50 = null,
5037 tl_text_cat_attribute1 = null, tl_text_cat_attribute2 = null, tl_text_cat_attribute3 = null,
5038 tl_text_cat_attribute4 = null, tl_text_cat_attribute5 = null, tl_text_cat_attribute6 = null,
5039 tl_text_cat_attribute7 = null, tl_text_cat_attribute8 = null, tl_text_cat_attribute9 = null,
5040 tl_text_cat_attribute10 = null, tl_text_cat_attribute11 = null, tl_text_cat_attribute12 = null,
5041 tl_text_cat_attribute13 = null, tl_text_cat_attribute14 = null, tl_text_cat_attribute15 = null,
5042 tl_text_cat_attribute16 = null, tl_text_cat_attribute17 = null, tl_text_cat_attribute18 = null,
5043 tl_text_cat_attribute19 = null, tl_text_cat_attribute20 = null, tl_text_cat_attribute21 = null,
5044 tl_text_cat_attribute22 = null, tl_text_cat_attribute23 = null, tl_text_cat_attribute24 = null,
5045 tl_text_cat_attribute25 = null, tl_text_cat_attribute26 = null, tl_text_cat_attribute27 = null,
5046 tl_text_cat_attribute28 = null, tl_text_cat_attribute29 = null, tl_text_cat_attribute30 = null,
5047 tl_text_cat_attribute31 = null, tl_text_cat_attribute32 = null, tl_text_cat_attribute33 = null,
5048 tl_text_cat_attribute34 = null, tl_text_cat_attribute35 = null, tl_text_cat_attribute36 = null,
5049 tl_text_cat_attribute37 = null, tl_text_cat_attribute38 = null, tl_text_cat_attribute39 = null,
5050 tl_text_cat_attribute40 = null, tl_text_cat_attribute41 = null, tl_text_cat_attribute42 = null,
5051 tl_text_cat_attribute43 = null, tl_text_cat_attribute44 = null, tl_text_cat_attribute45 = null,
5052 tl_text_cat_attribute46 = null, tl_text_cat_attribute47 = null, tl_text_cat_attribute48 = null,
5053 tl_text_cat_attribute49 = null, tl_text_cat_attribute50 = null
5054 where rt_category_id=gChangedOldCatIds(i)
5055 and rt_item_id=gChangedCatItemIds(i)
5056 and p_action=gChangedCatActions(i);
5057
5058 OPEN langCsr;
5059 FETCH langCsr BULK COLLECT into xLangArray;
5060 CLOSE langCsr;
5061
5062 l_progress := '150';
5063
5064 -- When the category of an item is changed, we need to update the
5065 -- primary_category_name of the translated rows. We need to do this here
5066 -- since primary_category_name is not updated for the translated rows in
5067 -- build_root_sql() as primary_category_name is a translated attribute.
5068 FOR i in 1..xLangArray.COUNT LOOP
5069 FORALL j in 1..gChangedCatItemIds.COUNT
5070 update icx_cat_items_tlp
5071 set primary_category_name = (select category_name from icx_cat_categories_tl
5072 where rt_category_id = gChangedNewCatIds(j)
5073 and language = xLangArray(i))
5074 where rt_item_id = gChangedCatItemIds(j) and language = xLangArray(i);
5075 END LOOP;
5076
5077 l_progress := '200';
5078
5079 EXCEPTION
5080 WHEN OTHERS then
5081 RAISE_APPLICATION_ERROR
5082 (-20000, 'Exception at ICX_POR_ITEM_UPLOAD.handle_category_change(ErrLoc = ' || l_progress ||') ' ||
5083 'SQL Error : ' || SQLERRM);
5084 END handle_category_change;
5085
5086 END ICX_POR_ITEM_UPLOAD;