[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_FPI_UPGRADE
Source
1 PACKAGE BODY ICX_CAT_FPI_UPGRADE AS
2 /* $Header: ICXUPGIB.pls 120.0.12020000.2 2013/02/12 11:16:12 bpulivar ship $*/
3
4 --------------------------------------------------------------
5 -- Cursors and Types --
6 --------------------------------------------------------------
7 TYPE tItemRecord IS RECORD (
8 rt_item_id NUMBER,
9 rt_category_id NUMBER);
10
11 TYPE tTemplateItemRecord IS RECORD (
12 template_id ICX_CAT_ITEM_PRICES.template_id%TYPE,
13 rt_item_id NUMBER,
14 hash_value NUMBER);
15
16 TYPE tTemplateItemCache IS TABLE OF tTemplateItemRecord
17 INDEX BY BINARY_INTEGER;
18
19 --------------------------------------------------------------
20 -- Caches --
21 --------------------------------------------------------------
22 gTemplateItemCache tTemplateItemCache;
23 gHashBase PLS_INTEGER;
24 gHashSize PLS_INTEGER;
25
26 --------------------------------------------------------------
27 -- Global Variables --
28 --------------------------------------------------------------
29 -- Current Item
30 gCurrentItem tItemRecord;
31 -- Dynamic SQL to update ICX_CAT_ITEMS_TLP, ICX_CAT_EXT_ITEMS_TLP
32 gDynSqlBaseAttributes VARCHAR2(4000);
33 gDynSqlCatAttributes VARCHAR2(4000);
34 gReturnErr VARCHAR2(4000) := NULL;
35 gLogLevel PLS_INTEGER := ICX_POR_EXT_UTL.DEBUG_LEVEL;
36 gLogFile VARCHAR2(200) := 'icxupgfi.log';
37 gCommitSize PLS_INTEGER := 2000;
38 gContinueExtItemTlp BOOLEAN := FALSE;
39
40 --------------------------------------------------------------
41 -- Global PL/SQL Tables --
42 --------------------------------------------------------------
43 -- Global PL/SQL tables for ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP,
44 -- ICX_CAT_EXT_ITEMS_TLP, ICX_CAT_CATEGORY_ITEMS
45 gIRtItemIds DBMS_SQL.NUMBER_TABLE;
46 gIOldRtItemIds DBMS_SQL.NUMBER_TABLE;
47 gIOrgIds DBMS_SQL.NUMBER_TABLE;
48 gISupplierPartNums DBMS_SQL.VARCHAR2_TABLE;
49 gIRtCategoryIds DBMS_SQL.NUMBER_TABLE;
50 gIExtractorUpdatedFlags DBMS_SQL.VARCHAR2_TABLE;
51
52 -- Global PL/SQL tables for description of ICX_CAT_ITEMS_TLP,
53 gITRtItemIds DBMS_SQL.NUMBER_TABLE;
54 gITItemDescriptions DBMS_SQL.VARCHAR2_TABLE;
55
56 -- Global PL/SQL tables for template headers of ICX_CAT_CATEGORY_ITEMS
57 gCIRtItemIds DBMS_SQL.NUMBER_TABLE;
58 gCITemplateIds DBMS_SQL.VARCHAR2_TABLE;
59
60 -- Global PL/SQL tables for ICX_CAT_ITEM_PRICES
61 -- Extracted price records
62 gEPRtItemIds DBMS_SQL.NUMBER_TABLE;
63 gEPActiveFlags DBMS_SQL.VARCHAR2_TABLE;
64 gEPOrgIds DBMS_SQL.NUMBER_TABLE;
65 gEPPriceTypes DBMS_SQL.VARCHAR2_TABLE;
66 gEPRowIds DBMS_SQL.UROWID_TABLE;
67 gEPRateTypes DBMS_SQL.VARCHAR2_TABLE;
68 gEPRateDates DBMS_SQL.DATE_TABLE;
69 gEPRates DBMS_SQL.NUMBER_TABLE;
70 gEPSupplierNumbers DBMS_SQL.VARCHAR2_TABLE;
71 gEPSupplierContactIds DBMS_SQL.NUMBER_TABLE;
72 gEPItemRevisions DBMS_SQL.VARCHAR2_TABLE;
73 gEPLineTypeIds DBMS_SQL.NUMBER_TABLE;
74 gEPBuyerIds DBMS_SQL.NUMBER_TABLE;
75
76 -- Bulkloaded price records
77 gBPRtItemIds DBMS_SQL.NUMBER_TABLE;
78 gBRActiveFlgs DBMS_SQL.VARCHAR2_TABLE;
79 gBPOrgIds DBMS_SQL.NUMBER_TABLE;
80 gBPSupplierSiteIds DBMS_SQL.NUMBER_TABLE;
81 gBPPriceTypes DBMS_SQL.VARCHAR2_TABLE;
82 gBPRowIds DBMS_SQL.UROWID_TABLE;
83
84 -- POR_FAVORITE_LIST_LINES
85 gUpFavRowIds DBMS_SQL.UROWID_TABLE;
86 gUpFavRtItemIds DBMS_SQL.NUMBER_TABLE;
87 gInFavRowIds DBMS_SQL.UROWID_TABLE;
88 gInFavRtItemIds DBMS_SQL.NUMBER_TABLE;
89
90 --------------------------------------------------------------
91 -- Procedures --
92 --------------------------------------------------------------
93
94 PROCEDURE cleanTables(pMode VARCHAR2) IS
95 BEGIN
96 IF pMode IN ('ALL', 'ITEM') THEN
97 gIRtItemIds.DELETE;
98 gIOldRtItemIds.DELETE;
99 gIOrgIds.DELETE;
100 gISupplierPartNums.DELETE;
101 gIRtCategoryIds.DELETE;
102 gIExtractorUpdatedFlags.DELETE;
103 END IF;
104
105 IF pMode IN ('ALL', 'TLP') THEN
106 gITRtItemIds.DELETE;
107 gITItemDescriptions.DELETE;
108 END IF;
109
110 IF pMode IN ('ALL', 'CAT_ITEM') THEN
111 gCIRtItemIds.DELETE;
112 gCITemplateIds.DELETE;
113 END IF;
114
115 IF pMode IN ('ALL', 'EXTRACTED_PRICE') THEN
116 gEPRtItemIds.DELETE;
117 gEPActiveFlags.DELETE;
118 gEPOrgIds.DELETE;
119 gEPPriceTypes.DELETE;
120 gEPRowIds.DELETE;
121 gEPRateTypes.DELETE;
122 gEPRateDates.DELETE;
123 gEPRates.DELETE;
124 gEPSupplierNumbers.DELETE;
125 gEPSupplierContactIds.DELETE;
126 gEPItemRevisions.DELETE;
127 gEPLineTypeIds.DELETE;
128 gEPBuyerIds.DELETE;
129 END IF;
130
131 IF pMode IN ('ALL', 'BULKLOADED_PRICE') THEN
132 gBPRtItemIds.DELETE;
133 gBRActiveFlgs.DELETE;
134 gBPOrgIds.DELETE;
135 gBPSupplierSiteIds.DELETE;
136 gBPPriceTypes.DELETE;
137 gBPRowIds.DELETE;
138 END IF;
139 END cleanTables;
140
141
142 FUNCTION snapShot(pIndex IN PLS_INTEGER,
143 pMode IN VARCHAR2) RETURN VARCHAR2
144 IS
145 xShot VARCHAR2(2000) := 'Snap Shot('||pMode||')['||pIndex||']--';
146 BEGIN
147 IF pMode = 'ITEM' THEN
148 xShot := xShot || ' gIRtItemIds: ' ||
149 ICX_POR_EXT_UTL.getTableElement(gIRtItemIds, pIndex) || ', ';
150 xShot := xShot || ' gIOldRtItemIds: ' ||
151 ICX_POR_EXT_UTL.getTableElement(gIOldRtItemIds, pIndex) || ', ';
152 xShot := xShot || ' gIOrgIds: ' ||
153 ICX_POR_EXT_UTL.getTableElement(gIOrgIds, pIndex) || ', ';
154 xShot := xShot || ' gISupplierPartNums: ' ||
155 ICX_POR_EXT_UTL.getTableElement(gISupplierPartNums, pIndex) || ', ';
156 xShot := xShot || ' gIRtCategoryIds: ' ||
157 ICX_POR_EXT_UTL.getTableElement(gIRtCategoryIds, pIndex) || ', ';
158 xShot := xShot || ' gIExtractorUpdatedFlags: ' ||
159 ICX_POR_EXT_UTL.getTableElement(gIExtractorUpdatedFlags, pIndex);
160 ELSIF pMode = 'TLP' THEN
161 xShot := xShot || ' gITRtItemIds: ' ||
162 ICX_POR_EXT_UTL.getTableElement(gITRtItemIds, pIndex) || ', ';
163 xShot := xShot || ' gITItemDescriptions: ' ||
164 ICX_POR_EXT_UTL.getTableElement(gITItemDescriptions, pIndex);
165 ELSIF pMode = 'CAT_ITEM' THEN
166 xShot := xShot || ' gCIRtItemIds: ' ||
167 ICX_POR_EXT_UTL.getTableElement(gCIRtItemIds, pIndex) || ', ';
168 xShot := xShot || ' gCITemplateIds: ' ||
169 ICX_POR_EXT_UTL.getTableElement(gCITemplateIds, pIndex);
170 ELSIF pMode = 'EXTRACTED_PRICE' THEN
171 xShot := xShot || ' gEPRtItemIds: ' ||
172 ICX_POR_EXT_UTL.getTableElement(gEPRtItemIds, pIndex) || ', ';
173 xShot := xShot || ' gEPActiveFlags: ' ||
174 ICX_POR_EXT_UTL.getTableElement(gEPActiveFlags, pIndex) || ', ';
175 xShot := xShot || ' gEPOrgIds: ' ||
176 ICX_POR_EXT_UTL.getTableElement(gEPOrgIds, pIndex) || ', ';
177 xShot := xShot || ' gEPPriceTypes: ' ||
178 ICX_POR_EXT_UTL.getTableElement(gEPPriceTypes, pIndex) || ', ';
179 xShot := xShot || ' gEPRateTypes: ' ||
180 ICX_POR_EXT_UTL.getTableElement(gEPRateTypes, pIndex) || ', ';
181 xShot := xShot || ' gEPRateDates: ' ||
182 ICX_POR_EXT_UTL.getTableElement(gEPRateDates, pIndex) || ', ';
183 xShot := xShot || ' gEPRates: ' ||
184 ICX_POR_EXT_UTL.getTableElement(gEPRates, pIndex) || ', ';
185 xShot := xShot || ' gEPSupplierNumbers: ' ||
186 ICX_POR_EXT_UTL.getTableElement(gEPSupplierNumbers, pIndex) || ', ';
187 xShot := xShot || ' gEPSupplierContactIds: ' ||
188 ICX_POR_EXT_UTL.getTableElement(gEPSupplierContactIds, pIndex) || ', ';
189 xShot := xShot || ' gEPItemRevisions: ' ||
190 ICX_POR_EXT_UTL.getTableElement(gEPItemRevisions, pIndex) || ', ';
191 xShot := xShot || ' gEPLineTypeIds: ' ||
192 ICX_POR_EXT_UTL.getTableElement(gEPLineTypeIds, pIndex) || ', ';
193 xShot := xShot || ' gEPBuyerIds: ' ||
194 ICX_POR_EXT_UTL.getTableElement(gEPBuyerIds, pIndex) || ', ';
195 xShot := xShot || ' gEPRowIds: ' ||
196 ICX_POR_EXT_UTL.getTableElement(gEPRowIds, pIndex);
197 ELSIF pMode = 'BULKLOADED_PRICE' THEN
198 xShot := xShot || ' gBPRtItemIds: ' ||
199 ICX_POR_EXT_UTL.getTableElement(gBPRtItemIds, pIndex) || ', ';
200 xShot := xShot || ' gBRActiveFlgs: ' ||
201 ICX_POR_EXT_UTL.getTableElement(gBRActiveFlgs, pIndex) || ', ';
202 xShot := xShot || ' gBPOrgIds: ' ||
203 ICX_POR_EXT_UTL.getTableElement(gBPOrgIds, pIndex) || ', ';
204 xShot := xShot || ' gBPSupplierSiteIds: ' ||
205 ICX_POR_EXT_UTL.getTableElement(gBPSupplierSiteIds, pIndex) || ', ';
206 xShot := xShot || ' gBPPriceTypes: ' ||
207 ICX_POR_EXT_UTL.getTableElement(gBPPriceTypes, pIndex) || ', ';
208 xShot := xShot || ' gBPRowIds: ' ||
209 ICX_POR_EXT_UTL.getTableElement(gBPRowIds, pIndex);
210 END IF;
211
212 RETURN xShot;
213 END snapShot;
214
215 --------------------------------------------------------------
216 -- Process Caching Data --
217 --------------------------------------------------------------
218 PROCEDURE clearCache IS
219 BEGIN
220 gTemplateItemCache.DELETE;
221 END clearCache;
222
223 PROCEDURE setHashRange(pHashBase IN NUMBER,
224 pHashSize IN NUMBER) IS
225 xErrLoc PLS_INTEGER := 100;
226 BEGIN
227 xErrLoc := 100;
228 clearCache;
229 gHashBase := pHashBase;
230 gHashSize := pHashSize;
231 END setHashRange;
232
233 PROCEDURE initCaches IS
234 xErrLoc PLS_INTEGER := 100;
235 xHashSize PLS_INTEGER;
236 BEGIN
237 xErrLoc := 100;
238 -- Caculate hash size based on gCommitSize, but at least 1024
239 -- A power of 2 for the hash_size parameter is best
240 xHashSize := GREATEST(POWER(2,ROUND(LOG(2,gCommitSize*10))),
241 POWER(2, 10));
242 xErrLoc := 200;
243 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
244 'Cache hash size is ' || xHashSize);
245 setHashRange(1, xHashSize);
246 END initCaches;
247
248 -- A hash value based on the input string. For example,
249 -- to get a hash value on a string where the hash value
250 -- should be between 1000 and 3047, use 1000 as the base
251 -- value and 2048 as the hash_size value. Using a power
252 -- of 2 for the hash_size parameter works best.
253 FUNCTION getHashValue(pHashString IN VARCHAR2)
254 RETURN NUMBER
255 IS
256 xErrLoc PLS_INTEGER := 100;
257 BEGIN
258 xErrLoc := 100;
259 RETURN DBMS_UTILITY.get_hash_value(pHashString,
260 gHashBase,
261 gHashSize);
262 END getHashValue;
263
264 FUNCTION findTemplateItemCache(pTemplateItem IN OUT NOCOPY tTemplateItemRecord)
265 RETURN BOOLEAN
266 IS
267 xErrLoc PLS_INTEGER := 100;
268 xHashString VARCHAR2(2000);
269 xHashValue PLS_INTEGER;
270 xTemplateItem tTemplateItemRecord;
271 BEGIN
272 xErrLoc := 100;
273 IF pTemplateItem.hash_value > ICX_POR_EXT_ITEM.NULL_NUMBER THEN
274 RETURN TRUE;
275 END IF;
276
277 xHashString := pTemplateItem.template_id || pTemplateItem.rt_item_id;
278
279 xErrLoc := 200;
280 xHashValue := getHashValue(xHashString);
281
282 xErrLoc := 300;
283 WHILE (TRUE) LOOP
284 -- It is impossible to have cache full, so we don't need
285 -- to worry about caching replacement
286 IF gTemplateItemCache.EXISTS(xHashValue) THEN
287 xTemplateItem := gTemplateItemCache(xHashValue);
288 xErrLoc := 320;
289 -- All NULL value is replace by NULL_NUMBER
290 IF (xTemplateItem.template_id = pTemplateItem.template_id AND
291 xTemplateItem.rt_item_id = pTemplateItem.rt_item_id)
292 THEN
293 pTemplateItem.hash_value := xTemplateItem.hash_value;
294 RETURN TRUE;
295 ELSE
296 xHashValue := xHashValue + 1;
297 END IF;
298 ELSE
299 pTemplateItem.hash_value := xHashValue;
300 RETURN FALSE;
301 END IF;
302 END LOOP;
303
304 RETURN FALSE;
305 EXCEPTION
306 when others then
307 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.findTemplateItemCache-'||
308 xErrLoc||' '||SQLERRM);
309 raise ICX_POR_EXT_UTL.gException;
310 END findTemplateItemCache;
311
312 PROCEDURE putTemplateItemCache(pTemplateItem IN tTemplateItemRecord) IS
313 xErrLoc PLS_INTEGER := 100;
314 BEGIN
315 xErrLoc := 100;
316
317 IF pTemplateItem.hash_value = ICX_POR_EXT_ITEM.NULL_NUMBER THEN
318 RETURN;
319 END IF;
320
321 xErrLoc := 200;
322 gTemplateItemCache(pTemplateItem.hash_value) := pTemplateItem;
323 EXCEPTION
324 when others then
325 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.putTemplateItemCache-'||
326 xErrLoc||' '||SQLERRM);
327 raise ICX_POR_EXT_UTL.gException;
328 END putTemplateItemCache;
329
330
331 FUNCTION getOldPrimaryCategoryId(pRtItemId IN NUMBER)
332 RETURN NUMBER
333 IS
334 xRtCategoryId NUMBER;
335 BEGIN
336 SELECT ci.rt_category_id
337 INTO xRtCategoryId
338 FROM icx_por_category_items ci
339 WHERE ci.rt_item_id = pRtItemId
340 AND EXISTS (SELECT 'primary category'
341 FROM icx_por_categories_tl cat
342 WHERE cat.rt_category_id = ci.rt_category_id
343 AND cat.type = ICX_POR_EXT_CLASS.CATEGORY_TYPE)
344 AND ROWNUM = 1;
345 RETURN xRtCategoryId;
346 END getOldPrimaryCategoryId;
347
348 FUNCTION getPrimaryCategoryId(pRtItemId IN NUMBER)
349 RETURN NUMBER
350 IS
351 xRtCategoryId NUMBER;
352 BEGIN
353 SELECT ci.rt_category_id
354 INTO xRtCategoryId
355 FROM icx_cat_category_items ci
356 WHERE ci.rt_item_id = pRtItemId
357 AND EXISTS (SELECT 'primary category'
358 FROM icx_cat_categories_tl cat
359 WHERE cat.rt_category_id = ci.rt_category_id
360 AND cat.type = ICX_POR_EXT_CLASS.CATEGORY_TYPE)
361 AND ROWNUM = 1;
362 RETURN xRtCategoryId;
363 END getPrimaryCategoryId;
364
365 -- Fetch category attributes
366 PROCEDURE fetchAttributes(pRtCategoryId NUMBER) IS
367 CURSOR cCatAttributes(cpRtCategoryId NUMBER) IS
368 SELECT rt_descriptor_id,
369 key, type,
370 stored_in_table,
371 stored_in_column
372 FROM icx_cat_descriptors_tl
373 WHERE rt_category_id = cpRtCategoryId
374 AND language = (SELECT language_code
375 FROM fnd_languages
376 WHERE installed_flag = 'B');
377
378 xRtDescriptorIds DBMS_SQL.NUMBER_TABLE;
379 xKeys DBMS_SQL.VARCHAR2_TABLE;
380 xTypes DBMS_SQL.NUMBER_TABLE;
381 xStoredInTables DBMS_SQL.VARCHAR2_TABLE;
382 xStoredInColumns DBMS_SQL.VARCHAR2_TABLE;
383
384 xUpdateColumns VARCHAR2(2000) := NULL;
385 xSelectColumns VARCHAR2(2000) := NULL;
386
387 xErrLoc PLS_INTEGER;
388 xReturnErr VARCHAR2(2000);
389
390 BEGIN
391 xErrLoc := 50;
392 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
393 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
394 'Fetch attributes for category ' || pRtCategoryId);
395 END IF;
396
397 OPEN cCatAttributes(pRtCategoryId);
398 FETCH cCatAttributes
399 BULK COLLECT INTO xRtDescriptorIds, xKeys, xTypes,
400 xStoredInTables, xStoredInColumns;
401
402 xErrLoc := 100;
403
404 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
405 FOR i in 1..xRtDescriptorIds.COUNT LOOP
406 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
407 'rt_descriptor_id: ' || xRtDescriptorIds(i) ||
408 ', key: ' || xKeys(i) || ', type: ' || xTypes(i) ||
409 ', stored_in_table: ' || xStoredInTables(i) ||
410 ', stored_in_column: ' || xStoredInColumns(i));
411 END LOOP;
412 END IF;
413
414 xErrLoc := 150;
415 -- Build dynamic SQL
416 FOR i in 1..xRtDescriptorIds.COUNT LOOP
417 xErrLoc := 200;
418
419 -- Let's skip all seeded base attributes and pricing attributes
420 -- 'SUPPLIER', 'SUPPLIER_ID', 'SUPPLIER_PART_NUM',
421 -- 'MANUFACTURER', 'MANUFACTURER_PART_NUM', 'UOM',
422 -- 'DESCRIPTION', 'COMMENTS', 'ALIAS',
423 -- 'PRICE', 'CURRENCY', 'INTERNAL_ITEM_NUM',
424 -- 'PICTURE', 'PICTURE_URL', 'CONTRACT_NUM',
425 -- 'CONTRACT_LINE', 'CONTRACT_PRICE', 'CONTRACT_CURRENCY',
426 -- 'CONTRACT_RATE_TYPE', 'CONTRACT_RATE_DATE', 'CONTRACT_RATE',
427 -- 'ATTACHMENT_URL', 'LONG_DESCRIPTION', 'UNSPSC',
428 -- 'AVAILABILITY', 'LEAD_TIME', 'FUNCTIONAL_PRICE',
429 -- 'FUNCTIONAL_CURRENCY', 'ITEM_TYPE', 'SUPPLIER_SITE',
430 -- 'BUYER', 'PRICELIST',
431 IF (pRtCategoryId > 0 OR
432 xRtDescriptorIds(i) > 100)
433 THEN
434 IF (xStoredInColumns(i) IS NULL) THEN
435 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
436 'Empty stored_in_column for attribute: ' || xKeys(i));
437 ELSE
438 xErrLoc := 240;
439 IF (xUpdateColumns IS NOT NULL) THEN
440 xUpdateColumns := xUpdateColumns || ',';
441 xSelectColumns := xSelectColumns || ',';
442 END IF;
443
444 xUpdateColumns := xUpdateColumns || xStoredInColumns(i);
445
446 xErrLoc := 260;
447 IF (pRtCategoryId = 0) THEN
448 IF (xTypes(i) = 0) THEN
449 xSelectColumns := xSelectColumns || 'i.A' ||
450 xRtDescriptorIds(i);
451 ELSIF (xTypes(i) = 1) THEN
452 xSelectColumns := xSelectColumns || 'to_number(i.A' ||
453 xRtDescriptorIds(i) || ')';
454 ELSE
455 xSelectColumns := xSelectColumns || 'tl.A' ||
456 xRtDescriptorIds(i);
457 END IF;
458 ELSE
459 IF (xTypes(i) = 1) THEN
460 xSelectColumns := xSelectColumns || 'to_number(c.A' ||
461 xRtDescriptorIds(i) || ')';
462 ELSE
463 xSelectColumns := xSelectColumns || 'c.A' ||
464 xRtDescriptorIds(i);
465 END IF;
466 END IF;
467
468 END IF;
469 END IF;
470 END LOOP;
471
472 xErrLoc := 300;
473
474 IF (pRtCategoryId = 0) THEN
475 IF (xUpdateColumns IS NOT NULL) THEN
476 xErrLoc := 350;
477 gDynSqlBaseAttributes :=
478 'UPDATE ICX_CAT_ITEMS_TLP tlp ' ||
479 'SET (' || xUpdateColumns || ') = ' ||
480 '(SELECT ' || xSelectColumns ||
481 ' FROM ICX_POR_ITEMS i, ICX_POR_ITEMS_TL tl ' ||
482 ' WHERE i.rt_item_id = :old_rt_item_id ' ||
483 ' AND i.rt_item_id = tl.rt_item_id ' ||
484 ' AND tlp.language = tl.language) ' ||
485 'WHERE tlp.rt_item_id = :new_rt_item_id';
486 ELSE
487 gDynSqlBaseAttributes := NULL;
488 END IF;
489
490 ELSE
491 IF (xUpdateColumns IS NOT NULL) THEN
492 xErrLoc := 370;
493 gDynSqlCatAttributes :=
494 'UPDATE ICX_CAT_EXT_ITEMS_TLP tlp ' ||
495 'SET (' || xUpdateColumns || ') = ' ||
496 '(SELECT ' || xSelectColumns ||
497 ' FROM ICX_POR_C' || pRtCategoryId || '_TL c' ||
498 ' WHERE c.rt_item_id = :old_rt_item_id ' ||
499 ' AND c.language = tlp.language) ' ||
500 'WHERE tlp.rt_item_id = :new_rt_item_id ' ||
501 'AND tlp.rt_category_id = ' || pRtCategoryId;
502 ELSE
503 gDynSqlCatAttributes := NULL;
504 END IF;
505 END IF;
506
507 xErrLoc := 400;
508 IF (pRtCategoryId = 0) THEN
509 IF (gDynSqlBaseAttributes IS NOT NULL) THEN
510 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
511 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
512 gDynSqlBaseAttributes);
513 END IF;
514 END IF;
515 ELSE
516 IF (gDynSqlCatAttributes IS NOT NULL) THEN
517 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
518 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
519 gDynSqlCatAttributes);
520 END IF;
521 END IF;
522 END IF;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 ROLLBACK;
527 xReturnErr :=
528 'fetchAttributes(' ||xErrLoc||'): '||sqlerrm;
529 gReturnErr := gReturnErr || '-->' || xReturnErr;
530 ICX_POR_EXT_UTL.pushError(xReturnErr);
531 raise ICX_POR_EXT_UTL.gException;
532 END fetchAttributes;
533
534 -- Process item records based on gCurrentItem
535 PROCEDURE processItems IS
536 /*
537 The possible price records sharing the same RT_ITEM_ID:
538 1. Both SUPPLIER and SUPPLIER_PART_NUM are not null
539 a> Templates with contract reference
540 b> Contracts
541 c> Templates without contract reference
542 d> ASLs
543 e> Master items
544 f> Bulkloaded items
545 2. Either SUPPLIER or SUPPLIER_PART_NUM is null, not both
546 a> Templates with contract reference
547 b> Contracts
548 c> Templates without contract reference
549 d> ASLs
550 e> Master items
551 3. Both SUPPLIER and SUPPLIER_PART_NUM are null
552 a> Templates with contract reference
553 b> Contracts
554 c> Templates without contract reference
555 e> Master items
556 f> Internal templates
557 g> Intenal items
558
559 */
560 -- Cursor for all item records: subtable records and price list lines
561 CURSOR cItemRecords(p_rt_item_id IN NUMBER) IS
562 SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
563 NVL(ph.vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
564 pl.vendor_product_num supplier_part_num,
565 NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
566 7 type, -- template_contracts
567 greatest(pl.last_update_date,
568 ph.last_update_date) last_update_date,
569 -- pcreddy : Bug # 3234875 : Price type should be TEMPLATE
570 -- for template lines copied from blankets
571 -- ph.type_lookup_code price_type, -- 'BLANKET' or 'QUOTATION'
572 'TEMPLATE' as price_type,
573 pl.item_description item_description,
574 sub.orc_template_id template_id,
575 ph.rate_type,
576 ph.rate_date,
577 ph.rate,
578 pv.segment1 supplier_number,
579 NVL(ph.vendor_contact_id, prl.suggested_vendor_contact_id) supplier_contact_id,
580 prl.item_revision,
581 prl.line_type_id,
582 prl.suggested_buyer_id buyer_id,
583 sub.rowid row_id
584 FROM icx_por_oracle_item_subtable sub,
585 po_reqexpress_lines_all prl,
586 po_headers_all ph,
587 po_lines_all pl,
588 po_vendors pv
589 WHERE sub.rt_item_id = p_rt_item_id
590 AND sub.orc_template_id is not null
591 AND sub.orc_contract_id is not null
592 AND sub.orc_template_id = prl.express_name
593 AND sub.orc_template_line_id = prl.sequence_num
594 AND (sub.orc_operating_unit_id is NULL AND
595 prl.org_id is NULL OR
596 prl.org_id = sub.orc_operating_unit_id)
597 AND sub.orc_contract_id = ph.po_header_id
598 AND sub.orc_contract_line_id = pl.po_line_id
599 AND prl.suggested_vendor_id = pv.vendor_id (+)
600 UNION ALL
601 SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
602 NVL(ph.vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
603 pl.vendor_product_num supplier_part_num,
604 NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
605 6 type, -- contracts
606 greatest(pl.last_update_date,
607 ph.last_update_date) last_update_date,
608 ph.type_lookup_code price_type, -- 'BLANKET' or 'QUOTATION'
609 pl.item_description item_description,
610 NVL(sub.orc_template_id, TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER)) template_id,
611 ph.rate_type,
612 ph.rate_date,
613 ph.rate,
614 pv.segment1 supplier_number,
615 ph.vendor_contact_id supplier_contact_id,
616 pl.item_revision,
617 pl.line_type_id,
618 ph.agent_id buyer_id,
619 sub.rowid row_id
620 FROM icx_por_oracle_item_subtable sub,
621 po_headers_all ph,
622 po_lines_all pl,
623 po_vendors pv
624 WHERE sub.rt_item_id = p_rt_item_id
625 AND sub.orc_contract_id is not null
626 AND sub.orc_contract_id = ph.po_header_id
627 AND sub.orc_contract_line_id = pl.po_line_id
628 AND ph.vendor_id = pv.vendor_id (+)
629 UNION ALL
630 SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
631 NVL(prl.suggested_vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
632 prl.suggested_vendor_product_code supplier_part_num,
633 NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
634 5 type, -- templates
635 greatest(prl.last_update_date,
636 prh.last_update_date) last_update_date,
637 'TEMPLATE' price_type,
638 prl.item_description item_description,
639 sub.orc_template_id template_id,
640 TO_CHAR(NULL) rate_type,
641 TO_DATE(NULL) rate_date,
642 TO_NUMBER(NULL) rate,
643 pv.segment1 supplier_number,
644 prl.suggested_vendor_contact_id supplier_contact_id,
645 prl.item_revision,
646 prl.line_type_id,
647 prl.suggested_buyer_id buyer_id,
648 sub.rowid row_id
649 FROM icx_por_oracle_item_subtable sub,
650 po_reqexpress_headers_all prh,
651 po_reqexpress_lines_all prl,
652 po_vendors pv
653 WHERE sub.rt_item_id = p_rt_item_id
654 AND sub.orc_template_id is not null
655 AND sub.orc_contract_id is null
656 AND prh.express_name = sub.orc_template_id
657 AND (sub.orc_operating_unit_id is NULL AND
658 prh.org_id is NULL OR
659 prh.org_id = sub.orc_operating_unit_id)
660 AND prl.express_name = sub.orc_template_id
661 AND prl.sequence_num = sub.orc_template_line_id
662 AND (sub.orc_operating_unit_id is NULL AND
663 prl.org_id is NULL OR
664 prl.org_id = sub.orc_operating_unit_id)
665 AND prl.suggested_vendor_id = pv.vendor_id (+)
666 UNION ALL
667 SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
668 NVL(prl.suggested_vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
669 prl.suggested_vendor_product_code supplier_part_num,
670 NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
671 4 type, -- internal templates
672 greatest(prl.last_update_date,
673 prh.last_update_date) last_update_date,
674 'INTERNAL_TEMPLATE' price_type,
675 prl.item_description item_description,
676 sub.orc_template_id template_id,
677 TO_CHAR(NULL) rate_type,
678 TO_DATE(NULL) rate_date,
679 TO_NUMBER(NULL) rate,
680 TO_CHAR(NULL) supplier_number,
681 TO_NUMBER(NULL) supplier_contact_id,
682 prl.item_revision,
683 prl.line_type_id,
684 prl.suggested_buyer_id buyer_id,
685 sub.rowid row_id
686 FROM icx_por_oracle_item_subtable sub,
687 po_reqexpress_headers_all prh,
688 po_reqexpress_lines_all prl
689 WHERE sub.rt_item_id = p_rt_item_id
690 AND sub.orc_template_id is not null
691 AND sub.orc_contract_id is null
692 AND sub.search_type = 'INTERNAL'
693 AND prh.express_name = sub.orc_template_id
694 AND (sub.orc_operating_unit_id is NULL AND
695 prh.org_id is NULL OR
696 prh.org_id = sub.orc_operating_unit_id)
697 AND prl.express_name = sub.orc_template_id
698 AND prl.sequence_num = sub.orc_template_line_id
699 AND (sub.orc_operating_unit_id is NULL AND
700 prl.org_id is NULL OR
701 prl.org_id = sub.orc_operating_unit_id)
702 UNION ALL
703 SELECT prl.buyer_id org_id,
704 NVL(prl.supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
705 item.a3 supplier_part_num,
706 NVL(pvs.vendor_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
707 3 type, -- Bulk Loaded
708 prl.last_update_date last_update_date,
709 --Bug#3148018
710 --For lines with contract_reference_num, should have a price_type
711 --of CONTRACT
712 decode(prl.contract_reference_num, null, 'BULKLOAD', 'CONTRACT') price_type,
713 TO_CHAR(NULL) item_description,
714 TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER) template_id,
715 TO_CHAR(NULL) rate_type,
716 TO_DATE(NULL) rate_date,
717 TO_NUMBER(NULL) rate,
718 TO_CHAR(NULL) supplier_number,
719 TO_NUMBER(NULL) supplier_contact_id,
720 TO_CHAR(NULL) item_revision,
721 TO_NUMBER(NULL) line_type_id,
722 TO_NUMBER(NULL) buyer_id,
723 prl.rowid row_id
724 FROM icx_por_price_list_lines prl,
725 icx_por_items item,
726 po_vendor_sites_all pvs
727 WHERE prl.item_id = p_rt_item_id
728 AND prl.buyer_approval_status = 'APPROVED'
729 AND item.rt_item_id = p_rt_item_id
730 AND prl.supplier_site = pvs.vendor_site_code (+)
731 AND prl.supplier_id = pvs.vendor_id (+)
732 AND prl.buyer_id = pvs.org_id (+)
733 UNION ALL
734 SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
735 NVL(pasl.vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
736 pasl.primary_vendor_item supplier_part_num,
737 NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
738 2 type, -- ASLs
739 pasl.last_update_date last_update_date,
740 'ASL' price_type,
741 TO_CHAR(NULL) item_description,
742 TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER) template_id,
743 TO_CHAR(NULL) rate_type,
744 TO_DATE(NULL) rate_date,
745 TO_NUMBER(NULL) rate,
746 TO_CHAR(NULL) supplier_number,
747 TO_NUMBER(NULL) supplier_contact_id,
748 TO_CHAR(NULL) item_revision,
749 TO_NUMBER(NULL) line_type_id,
750 TO_NUMBER(NULL) buyer_id,
751 sub.rowid row_id
752 FROM icx_por_oracle_item_subtable sub,
753 po_approved_supplier_list pasl
754 WHERE sub.rt_item_id = p_rt_item_id
755 AND sub.orc_template_id is null
756 AND sub.orc_contract_id is null
757 AND sub.orc_asl_id is not null
758 AND pasl.asl_id = sub.orc_asl_id
759 AND (sub.orc_operating_unit_id is NULL AND
760 pasl.owning_organization_id is NULL OR
761 pasl.owning_organization_id =
762 (SELECT fspa.inventory_organization_id
763 FROM financials_system_params_all fspa
764 WHERE fspa.org_id = sub.orc_operating_unit_id
765 AND rownum = 1))
766 UNION ALL
767 SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
768 TO_NUMBER(ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
769 TO_CHAR(NULL) supplier_part_num,
770 TO_NUMBER(ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
771 1 type, -- Master Items
772 msi.last_update_date last_update_date,
773 DECODE(sub.search_type, 'SUPPLIER',
774 'PURCHASING_ITEM', 'INTERNAL_ITEM') price_type,
775 TO_CHAR(NULL) item_description,
776 TO_CHAR(NULL) template_id,
777 TO_CHAR(NULL) rate_type,
778 TO_DATE(NULL) rate_date,
779 TO_NUMBER(NULL) rate,
780 TO_CHAR(NULL) supplier_number,
781 TO_NUMBER(NULL) supplier_contact_id,
782 TO_CHAR(NULL) item_revision,
783 TO_NUMBER(NULL) line_type_id,
784 TO_NUMBER(NULL) buyer_id,
785 sub.rowid row_id
786 FROM icx_por_oracle_item_subtable sub,
787 icx_por_items item,
788 mtl_system_items msi
789 WHERE sub.rt_item_id = p_rt_item_id
790 AND sub.orc_template_id is null
791 AND sub.orc_contract_id is null
792 AND sub.orc_asl_id is null
793 AND sub.rt_item_id = item.rt_item_id
794 AND item.orc_item_id is not null
795 AND msi.inventory_item_id = item.orc_item_id
796 AND (sub.orc_operating_unit_id is NULL AND
797 msi.organization_id is NULL OR
798 msi.organization_id =
799 (SELECT fspa.inventory_organization_id
800 FROM financials_system_params_all fspa
801 WHERE fspa.org_id = sub.orc_operating_unit_id
802 AND rownum = 1))
803 -- pcreddy : Bug # 3234875 : Order by type desc
804 ORDER BY 1, 2, 3, 5 DESC, 6 DESC;
805 -- org_id, supplier_id, supplier_part_num,
806 -- type, last_update_date;
807
808 xOrgIds DBMS_SQL.NUMBER_TABLE;
809 xSupplierIds DBMS_SQL.NUMBER_TABLE;
810 xSupplierPartNums DBMS_SQL.VARCHAR2_TABLE;
811 xSupplierSiteIds DBMS_SQL.NUMBER_TABLE;
812 xTypes DBMS_SQL.NUMBER_TABLE;
813 xLastUpdateDates DBMS_SQL.DATE_TABLE;
814 xPriceTypes DBMS_SQL.VARCHAR2_TABLE;
815 xItemDescriptions DBMS_SQL.VARCHAR2_TABLE;
816 xTemplateIds DBMS_SQL.VARCHAR2_TABLE;
817 xRateTypes DBMS_SQL.VARCHAR2_TABLE;
818 xRateDates DBMS_SQL.DATE_TABLE;
819 xRates DBMS_SQL.NUMBER_TABLE;
820 xSupplierNumbers DBMS_SQL.VARCHAR2_TABLE;
821 xSupplierContactIds DBMS_SQL.NUMBER_TABLE;
822 xItemRevisions DBMS_SQL.VARCHAR2_TABLE;
823 xLineTypeIds DBMS_SQL.NUMBER_TABLE;
824 xBuyerIds DBMS_SQL.NUMBER_TABLE;
825
826 xRowIds DBMS_SQL.UROWID_TABLE;
827
828 xErrLoc PLS_INTEGER;
829 xReturnErr VARCHAR2(2000);
830
831 xOrgId NUMBER;
832 xSupplierId NUMBER;
833 xSupplierPartNum ICX_CAT_ITEMS_B.supplier_part_num%TYPE;
834 xSupplierSiteId NUMBER;
835 xRtItemId NUMBER;
836 xType PLS_INTEGER;
837 xActiveFlag VARCHAR2(1);
838 xCount PLS_INTEGER;
839
840 xTemplateItem tTemplateItemRecord;
841
842 BEGIN
843 xErrLoc := 50;
844 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL THEN
845 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
846 'Process item records for item: ' || gCurrentItem.rt_item_id);
847 END IF;
848 clearCache;
849
850 xErrLoc := 60;
851 OPEN cItemRecords(gCurrentItem.rt_item_id);
852
853 xErrLoc := 70;
854 FETCH cItemRecords
855 BULK COLLECT INTO xOrgIds, xSupplierIds,
856 xSupplierPartNums, xSupplierSiteIds,
857 xTypes, xLastUpdateDates,
858 xPriceTypes, xItemDescriptions,
859 xTemplateIds, xRateTypes, xRateDates,
860 xRates, xSupplierNumbers,
861 xSupplierContactIds, xItemRevisions,
862 xLineTypeIds, xBuyerIds, xRowIds;
863
864 xErrLoc := 80;
865 IF xOrgIds.COUNT = 0 THEN
866 RETURN;
867 END IF;
868
869 xErrLoc := 100;
870 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
871 FOR i in 1..xOrgIds.COUNT LOOP
872 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
873 'org_id: ' || xOrgIds(i) ||
874 ', supplier_id: ' || xSupplierIds(i) ||
875 ', supplier_part_num: ' || xSupplierPartNums(i) ||
876 ', supplier_site_id: ' || xSupplierSiteIds(i) ||
877 ', type: ' || xTypes(i) ||
878 ', last_update_date: ' || xLastUpdateDates(i) ||
879 ', price_type: ' || xPriceTypes(i) ||
880 ', item_description: ' || xItemDescriptions(i) ||
881 ', template_id: ' || xTemplateIds(i) ||
882 ', rate_type: ' || xRateTypes(i) ||
883 ', rate_date: ' || xRateDates(i) ||
884 ', rate: ' || xRates(i) ||
885 ', supplier_number: ' || xSupplierNumbers(i) ||
886 ', supplier_contact_id: ' || xSupplierContactIds(i) ||
887 ', item_revision: ' || xItemRevisions(i) ||
888 ', line_type_id: ' || xLineTypeIds(i) ||
889 ', buyer_id: ' || xBuyerIds(i) ||
890 ', rowid: ' || xRowIds(i));
891 END LOOP;
892 END IF;
893
894 -- Set first item uniqueness criteria
895 xOrgId := xOrgIds(1);
896 xSupplierId := xSupplierIds(1);
897 xSupplierPartNum := xSupplierPartNums(1);
898 xSupplierSiteId := xSupplierSiteIds(1);
899 xRtItemId := gCurrentItem.rt_item_id;
900 xType := 0;
901
902 -- Set global PL/SQL tables for Items
903 xCount := gIRtItemIds.COUNT + 1;
904 gIRtItemIds(xCount) := xRtItemId;
905 gIOldRtItemIds(xCount) := gCurrentItem.rt_item_id;
906 gIOrgIds(xCount) := xOrgId;
907 gISupplierPartNums(xCount) := xSupplierPartNum;
908 gIRtCategoryIds(xCount) := gCurrentItem.rt_category_id;
909 IF (xTypes(1) = 3) THEN
910 -- Bulkloaded item
911 gIExtractorUpdatedFlags(xCount) := 'N';
912 ELSE
913 gIExtractorUpdatedFlags(xCount) := 'Y';
914 END IF;
915 -- Only update item description from template/contracts
916 IF (xItemDescriptions(1) is not null) THEN
917 xCount := gITRtItemIds.COUNT + 1;
918 gITRtItemIds(xCount) := xRtItemId;
919 gITItemDescriptions(xCount) := xItemDescriptions(1);
920 END IF;
921
922 xErrLoc := 150;
923 FOR i in 1..xRowIds.COUNT LOOP
924 -- Check item uniqueness
925 IF (xOrgIds(i) = xOrgId AND
926 xSupplierIds(i) = xSupplierId AND
927 (xSupplierPartNums(i) IS NULL AND xSupplierPartNum IS NULL OR
928 xSupplierPartNums(i) = xSupplierPartNum))
929 THEN
930 xErrLoc := 200;
931 -- Set extractor updated flag
932 IF xTypes(i) <> 3 THEN
933 gIExtractorUpdatedFlags(gIExtractorUpdatedFlags.COUNT) := 'Y';
934 END IF;
935
936 -- Set active flag
937 -- No longer check supplier_site
938 -- IF xSupplierSiteIds(i) = xSupplierSiteId THEN
939 IF xTypes(i) = 7 THEN
940 -- Template_contract
941 xActiveFlag := 'Y';
942 ELSIF xTypes(i) = 6 THEN
943 -- Contract
944 IF xTemplateIds(i) <> TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER) THEN
945 -- Contract with template reference
946 xActiveFlag := 'N';
947 -- Clear template_id
948 xTemplateIds(i) := TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER);
949 ELSE
950 xActiveFlag := 'Y';
951 END IF;
952 ELSIF (xTypes(i) in (4, 5)) THEN
953 -- Template, Internal template
954 IF (xType = 0) THEN
955 -- No documents with higher priority exist
956 -- Only the first template line is active
957 xActiveFlag := 'Y';
958 ELSE
959 xActiveFlag := 'N';
960 END IF;
961 ELSIF (xTypes(i) = 3) THEN
962 -- Bulkloaded
963 IF (xType = 0) THEN
964 -- No documents with higher priority exist
965 -- All Bulkloaded price list lines are active
966 xActiveFlag := 'Y';
967 ELSIF (xType = 3) THEN
968 -- set active_flag as the previous record
969 xActiveFlag := xActiveFlag;
970 ELSE
971 -- set active_flag to 'N'
972 xActiveFlag := 'N';
973 END IF;
974 ELSIF (xTypes(i) = 2) THEN
975 -- ASL
976 IF (xType = 0) THEN
977 -- No documents with higher priority exist
978 -- All ASLs are active
979 xActiveFlag := 'Y';
980 ELSIF (xType = 2) THEN
981 -- set active_flag as the previous record
982 xActiveFlag := xActiveFlag;
983 ELSE
984 -- set active_flag to 'N'
985 xActiveFlag := 'N';
986 END IF;
987 ELSIF (xTypes(i) = 1) THEN
988 -- Master item record in subtable means no
989 -- documents with higher priority exist
990 xActiveFlag := 'Y';
991 END IF; -- IF (xTypes(i) ... )
992
993 -- Set xType
994 xType := xTypes(i);
995 -- No longer check supplier_site
996 /*
997 ELSE
998 -- Different supplier_site_id
999 xActiveFlag := 'Y';
1000 -- Reset xType
1001 xType := 0;
1002 END IF; -- IF (xSupplierSiteIds(i) = xSupplierSiteId)
1003 */
1004
1005 ELSE
1006 xErrLoc := 240;
1007 -- Create new rt_item_id
1008 SELECT icx_por_itemid.nextval
1009 INTO xRtItemId
1010 FROM sys.dual;
1011
1012 xErrLoc := 300;
1013 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1014 'Create a new item: ' || xRtItemId ||
1015 ' for item[old_rt_item_id: ' ||
1016 gCurrentItem.rt_item_id || ', org_id: ' ||
1017 xOrgIds(i) || ', supplier_id: ' ||
1018 xSupplierIds(i) || ', supplier_part_num: ' ||
1019 xSupplierPartNums(i) || ']' );
1020
1021 xErrLoc := 310;
1022 -- Set global PL/SQL tables for Items
1023 xCount := gIRtItemIds.COUNT + 1;
1024 gIRtItemIds(xCount) := xRtItemId;
1025 gIOldRtItemIds(xCount) := gCurrentItem.rt_item_id;
1026 gIOrgIds(xCount) := xOrgIds(i);
1027 gISupplierPartNums(xCount) := xSupplierPartNums(i);
1028 gIRtCategoryIds(xCount) := gCurrentItem.rt_category_id;
1029 IF (xTypes(i) = 3) THEN
1030 -- Bulkloaded item
1031 gIExtractorUpdatedFlags(xCount) := 'N';
1032 ELSE
1033 gIExtractorUpdatedFlags(xCount) := 'Y';
1034 END IF;
1035 -- Only update item description from template/contracts
1036 IF (xItemDescriptions(i) is not null) THEN
1037 xCount := gITRtItemIds.COUNT + 1;
1038 gITRtItemIds(xCount) := xRtItemId;
1039 gITItemDescriptions(xCount) := xItemDescriptions(i);
1040 END IF;
1041 IF xTypes(i) = 6 THEN
1042 -- Clear template_id for Contract
1043 xTemplateIds(i) := TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER);
1044 END IF;
1045 xErrLoc := 320;
1046 -- Set active flag
1047 xActiveFlag := 'Y';
1048 -- Reset xType
1049 xType := 0;
1050 END IF;
1051
1052 -- Set global PL/SQL tables for template headers for an item
1053 IF (xTemplateIds(i) <> TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER)) THEN
1054 -- Check for duplicate records
1055 xTemplateItem.template_id := xTemplateIds(i);
1056 xTemplateItem.rt_item_id := xRtItemId;
1057 IF NOT findTemplateItemCache(xTemplateItem) THEN
1058 xCount := gCIRtItemIds.COUNT + 1;
1059 gCIRtItemIds(xCount) := xRtItemId;
1060 gCITemplateIds(xCount) := xTemplateIds(i);
1061 putTemplateItemCache(xTemplateItem);
1062 END IF;
1063 END IF;
1064
1065 xOrgId := xOrgIds(i);
1066 xSupplierId := xSupplierIds(i);
1067 xSupplierPartNum := xSupplierPartNums(i);
1068 xSupplierSiteId := xSupplierSiteIds(i);
1069
1070 -- Set global PL/SQL tables for price records for an item
1071 IF (xTypes(i) <> 3) THEN
1072 -- Extracted price records
1073 xErrLoc := 500;
1074 xCount := gEPRtItemIds.COUNT + 1;
1075 gEPRtItemIds(xCount) := xRtItemId;
1076 gEPOrgIds(xCount) := xOrgIds(i);
1077 gEPActiveFlags(xCount) := xActiveFlag;
1078 gEPPriceTypes(xCount) := xPriceTypes(i);
1079 gEPRateTypes(xCount) := xRateTypes(i);
1080 gEPRateDates(xCount) := xRateDates(i);
1081 gEPRates(xCount) := xRates(i);
1082 gEPSupplierNumbers(xCount) := xSupplierNumbers(i);
1083 gEPSupplierContactIds(xCount) := xSupplierContactIds(i);
1084 gEPItemRevisions(xCount) := xItemRevisions(i);
1085 gEPLineTypeIds(xCount) := xLineTypeIds(i);
1086 gEPBuyerIds(xCount) := xBuyerIds(i);
1087 gEPRowIds(xCount) := xRowIds(i);
1088 ELSE
1089 -- Bulkloaded price records
1090 xErrLoc := 600;
1091 xCount := gBPRtItemIds.COUNT + 1;
1092 gBPRtItemIds(xCount) := xRtItemId;
1093 gBPOrgIds(xCount) := xOrgIds(i);
1094 gBPSupplierSiteIds(xCount) := xSupplierSiteIds(i);
1095 gBRActiveFlgs(xCount) := xActiveFlag;
1096 gBPPriceTypes(xCount) := xPriceTypes(i);
1097 gBPRowIds(xCount) := xRowIds(i);
1098 END IF;
1099
1100 END LOOP;
1101
1102 xErrLoc := 800;
1103 CLOSE cItemRecords;
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 IF (cItemRecords%ISOPEN) THEN
1107 CLOSE cItemRecords;
1108 END IF;
1109
1110 ROLLBACK;
1111 xReturnErr :=
1112 'processItems(' ||xErrLoc||'): '||sqlerrm;
1113 gReturnErr := gReturnErr || '-->' || xReturnErr;
1114 ICX_POR_EXT_UTL.pushError(xReturnErr);
1115 raise ICX_POR_EXT_UTL.gException;
1116 END processItems;
1117
1118 -- Move data into ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP, ICX_CAT_CATEGORY_ITEMS
1119 PROCEDURE moveItems IS
1120 xErrLoc PLS_INTEGER;
1121 xReturnErr VARCHAR2(2000);
1122
1123 xSqlString VARCHAR2(4000);
1124 xCursorId NUMBER;
1125 xResultCount NUMBER;
1126 xMode VARCHAR2(20) := 'ITEM';
1127
1128 BEGIN
1129 xErrLoc := 50;
1130 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1131 'moveItem[Count: ' || gIRtItemIds.COUNT || ']');
1132
1133 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
1134 FOR i in 1..gIRtItemIds.COUNT LOOP
1135 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1136 snapShot(i, xMode));
1137 END LOOP;
1138 END IF;
1139
1140 xErrLoc := 100;
1141 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'ICX_CAT_ITEMS_B');
1142
1143 -- Let's use object_version_number to store old_rt_item_id for now
1144 FORALL i IN 1..gIRtItemIds.COUNT
1145 INSERT INTO ICX_CAT_ITEMS_B
1146 (rt_item_id, object_version_number, org_id,
1147 supplier_id,
1148 supplier, supplier_part_num, supplier_part_auxid,
1149 internal_item_id, internal_item_num,
1150 extractor_updated_flag, last_update_login, last_updated_by, last_update_date,
1151 created_by, creation_date, request_id,
1152 program_application_id, program_id, program_update_date)
1153 SELECT gIRtItemIds(i), gIOldRtItemIds(i), gIOrgIds(i),
1154 NVL(item.supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1155 item.A1, gISupplierPartNums(i), '##NULL##',
1156 item.orc_item_id, item.orc_item_num,
1157 gIExtractorUpdatedFlags(i),
1158 gUpgradeUserId, gUpgradeUserId, sysdate,
1159 gUpgradeUserId, item.creation_date, gUpgradeUserId,
1160 gUpgradeUserId, gUpgradeUserId, sysdate
1161 FROM ICX_POR_ITEMS item
1162 WHERE item.rt_item_id = gIOldRtItemIds(i);
1163
1164 COMMIT;
1165
1166 xErrLoc := 150;
1167 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'ICX_CAT_ITEMS_TLP');
1168
1169 FORALL i IN 1..gIRtItemIds.COUNT
1170 INSERT INTO ICX_CAT_ITEMS_TLP
1171 (rt_item_id, language, org_id,
1172 supplier_id, item_source_type, search_type,
1173 primary_category_id, primary_category_name,
1174 internal_item_id, internal_item_num,
1175 supplier, supplier_part_num, supplier_part_auxid,
1176 manufacturer, manufacturer_part_num, description,
1177 comments, alias,
1178 picture, picture_url, thumbnail_image,
1179 attachment_url, long_description,
1180 unspsc_code, availability, lead_time, item_type,
1181 ctx_desc, last_update_login, last_updated_by, last_update_date,
1182 created_by, creation_date, request_id,
1183 program_application_id, program_id, program_update_date)
1184 SELECT gIRtItemIds(i), tl.language, gIOrgIds(i),
1185 NVL(item.supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1186 item.item_source_type, item.search_type,
1187 gIRtCategoryIds(i), cat.category_name,
1188 item.orc_item_id, item.orc_item_num,
1189 item.A1, gISupplierPartNums(i), '##NULL##',
1190 item.A4, item.A5, tl.A7,
1191 tl.A8, tl.A9,
1192 NVL(item.A13, item.A14), item.A14, NVL(item.A13, item.A14),
1193 item.A22, tl.A23,
1194 item.A24, item.A25, to_number(item.A26), item.A29,
1195 NULL, gUpgradeUserId, gUpgradeUserId, sysdate,
1196 gUpgradeUserId, tl.creation_date, gUpgradeUserId,
1197 gUpgradeUserId, gUpgradeUserId, sysdate
1198 FROM ICX_POR_ITEMS item,
1199 ICX_POR_ITEMS_TL tl,
1200 ICX_POR_CATEGORY_ITEMS ci,
1201 ICX_POR_CATEGORIES_TL cat
1202 WHERE item.rt_item_id = gIOldRtItemIds(i)
1203 AND item.rt_item_id = tl.rt_item_id
1204 AND ci.rt_item_id = item.rt_item_id
1205 AND cat.rt_category_id = ci.rt_category_id
1206 AND cat.rt_category_id = gIRtCategoryIds(i)
1207 AND tl.language = cat.language;
1208
1209 COMMIT;
1210
1211 xErrLoc := 200;
1212 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1213 'Dynamic SQL to update base attributes of ICX_CAT_ITEMS_TLP');
1214
1215 IF (gDynSqlBaseAttributes IS NOT NULL) THEN
1216 xErrLoc := 210;
1217 xCursorId := DBMS_SQL.open_cursor;
1218 xErrLoc := 220;
1219 DBMS_SQL.parse(xCursorId, gDynSqlBaseAttributes, DBMS_SQL.NATIVE);
1220 xErrLoc := 230;
1221 DBMS_SQL.bind_array(xCursorId, ':new_rt_item_id', gIRtItemIds);
1222 xErrLoc := 240;
1223 DBMS_SQL.bind_array(xCursorId, ':old_rt_item_id', gIOldRtItemIds);
1224 xErrLoc := 250;
1225 xResultCount := DBMS_SQL.execute(xCursorId);
1226 xErrLoc := 260;
1227 DBMS_SQL.close_cursor(xCursorId);
1228
1229 xErrLoc := 270;
1230 COMMIT;
1231 END IF;
1232
1233 xErrLoc := 300;
1234 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1235 'Primary category into ICX_CAT_CATEGORY_ITEMS');
1236
1237 FORALL i IN 1..gIRtItemIds.COUNT
1238 INSERT INTO ICX_CAT_CATEGORY_ITEMS
1239 (rt_item_id, rt_category_id,
1240 last_update_login, last_updated_by, last_update_date,
1241 created_by, creation_date, request_id,
1242 program_application_id, program_id, program_update_date)
1243 VALUES(gIRtItemIds(i), gIRtCategoryIds(i),
1244 gUpgradeUserId, gUpgradeUserId, sysdate,
1245 gUpgradeUserId, sysdate, gUpgradeUserId,
1246 gUpgradeUserId, gUpgradeUserId, sysdate);
1247
1248 COMMIT;
1249
1250 xErrLoc := 400;
1251 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1252 'ICX_CAT_EXT_ITEMS_TLP');
1253
1254 FORALL i IN 1..gIRtItemIds.COUNT
1255 INSERT INTO ICX_CAT_EXT_ITEMS_TLP
1256 (rt_item_id, language, org_id,
1257 rt_category_id, primary_flag,
1258 last_update_login, last_updated_by, last_update_date,
1259 created_by, creation_date, request_id,
1260 program_application_id, program_id, program_update_date)
1261 SELECT gIRtItemIds(i), tl.language, gIOrgIds(i),
1262 gIRtCategoryIds(i), NULL,
1263 gUpgradeUserId, gUpgradeUserId, sysdate,
1264 gUpgradeUserId, sysdate, gUpgradeUserId,
1265 gUpgradeUserId, gUpgradeUserId, sysdate
1266 FROM ICX_POR_ITEMS_TL tl
1267 WHERE tl.rt_item_id = gIOldRtItemIds(i);
1268
1269 COMMIT;
1270
1271 xErrLoc := 500;
1272 cleanTables(xMode);
1273
1274 xErrLoc := 600;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 ROLLBACK;
1278 xReturnErr :=
1279 'moveItems(' ||xErrLoc||'): '||sqlerrm;
1280 gReturnErr := gReturnErr || '-->' || xReturnErr;
1281 ICX_POR_EXT_UTL.pushError(xReturnErr);
1282 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
1283 raise ICX_POR_EXT_UTL.gException;
1284 END moveItems;
1285
1286 -- Update item_description of ICX_CAT_ITEMS_TLP
1287 PROCEDURE updateItemsTLP IS
1288 xErrLoc PLS_INTEGER;
1289 xReturnErr VARCHAR2(2000);
1290 xMode VARCHAR2(20) := 'TLP';
1291
1292 BEGIN
1293 xErrLoc := 50;
1294 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1295 'updateItemsTLP[Count: ' || gITRtItemIds.COUNT || ']');
1296
1297 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
1298 FOR i in 1..gITRtItemIds.COUNT LOOP
1299 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1300 snapShot(i, xMode));
1301 END LOOP;
1302 END IF;
1303
1304 xErrLoc := 100;
1305 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1306 'Update item_description of ICX_CAT_ITEMS_TLP');
1307
1308 FORALL i IN 1..gITRtItemIds.COUNT
1309 UPDATE ICX_CAT_ITEMS_TLP
1310 SET description = gITItemDescriptions(i)
1311 WHERE rt_item_id = gITRtItemIds(i)
1312 AND language = (SELECT language_code
1313 FROM fnd_languages
1314 WHERE installed_flag = 'B');
1315
1316 COMMIT;
1317
1318 xErrLoc := 150;
1319 cleanTables(xMode);
1320
1321 xErrLoc := 200;
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 ROLLBACK;
1325 xReturnErr :=
1326 'updateItemsTLP(' ||xErrLoc||'): '||sqlerrm;
1327 gReturnErr := gReturnErr || '-->' || xReturnErr;
1328 ICX_POR_EXT_UTL.pushError(xReturnErr);
1329 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
1330 raise ICX_POR_EXT_UTL.gException;
1331 END updateItemsTLP;
1332
1333 -- Create template headers into ICX_CAT_CATEGORY_ITEMS
1334 PROCEDURE createTempCategoryItems IS
1335 xErrLoc PLS_INTEGER;
1336 xReturnErr VARCHAR2(2000);
1337 xMode VARCHAR2(20) := 'CAT_ITEM';
1338
1339 BEGIN
1340 xErrLoc := 50;
1341 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1342 'createTempCategoryItems[Count: ' || gCIRtItemIds.COUNT || ']');
1343
1344 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1345 'Template headers into ICX_CAT_CATEGORY_ITEMS');
1346
1347 xErrLoc := 100;
1348 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
1349 FOR i in 1..gCIRtItemIds.COUNT LOOP
1350 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1351 snapShot(i, xMode));
1352 END LOOP;
1353 END IF;
1354
1355 FORALL i IN 1..gCIRtItemIds.COUNT
1356 INSERT INTO ICX_CAT_CATEGORY_ITEMS
1357 (rt_item_id, rt_category_id,
1358 last_update_login, last_updated_by, last_update_date,
1359 created_by, creation_date, request_id,
1360 program_application_id, program_id, program_update_date)
1361 SELECT gCIRtItemIds(i), cat.rt_category_id,
1362 gUpgradeUserId, gUpgradeUserId, sysdate,
1363 gUpgradeUserId, sysdate, gUpgradeUserId,
1364 gUpgradeUserId, gUpgradeUserId, sysdate
1365 FROM ICX_POR_CATEGORIES_TL cat
1366 WHERE cat.key = gCITemplateIds(i) || '_tmpl'
1367 AND cat.type = 3
1368 AND cat.language = (SELECT language_code
1369 FROM fnd_languages
1370 WHERE installed_flag = 'B');
1371
1372 COMMIT;
1373
1374 xErrLoc := 150;
1375 cleanTables(xMode);
1376
1377 xErrLoc := 200;
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 ROLLBACK;
1381 xReturnErr :=
1382 'createTempCategoryItems(' ||xErrLoc||'): '||sqlerrm;
1383 gReturnErr := gReturnErr || '-->' || xReturnErr;
1384 ICX_POR_EXT_UTL.pushError(xReturnErr);
1385 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
1386 raise ICX_POR_EXT_UTL.gException;
1387 END createTempCategoryItems;
1388
1389 -- Move data into ICX_CAT_ITEM_PRICES from ICX_POR_ORACLE_ITEM_SUBTABLE
1390 PROCEDURE moveExtractedPrices IS
1391 xErrLoc PLS_INTEGER;
1392 xReturnErr VARCHAR2(2000);
1393 xMode VARCHAR2(20) := 'EXTRACTED_PRICE';
1394
1395 BEGIN
1396 xErrLoc := 50;
1397 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1398 'moveExtractedPrices[Count: ' || gEPRtItemIds.COUNT || ']');
1399
1400 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
1401 FOR i in 1..gEPRtItemIds.COUNT LOOP
1402 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1403 snapShot(i, xMode));
1404 END LOOP;
1405 END IF;
1406
1407 xErrLoc := 100;
1408 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1409 'ICX_CAT_ITEM_PRICES from ICX_POR_ORACLE_ITEM_SUBTABLE');
1410
1411 FORALL i IN 1..gEPRtItemIds.COUNT
1412 INSERT INTO ICX_CAT_ITEM_PRICES
1413 (rt_item_id, price_type,
1414 active_flag, object_version_number,
1415 asl_id, supplier_site_id,
1416 contract_id, contract_line_id,
1417 template_id, template_line_id,
1418 inventory_item_id,
1419 mtl_category_id, org_id,
1420 search_type, unit_price,
1421 currency, unit_of_measure,
1422 functional_price, supplier_site_code,
1423 contract_num, contract_line_num,
1424 rate_type, rate_date, rate,
1425 supplier_number, supplier_contact_id,
1426 item_revision, line_type_id, buyer_id,
1427 price_list_id, last_update_login,
1428 last_updated_by, last_update_date,
1429 created_by, creation_date, request_id,
1430 program_application_id, program_id, program_update_date)
1431 SELECT gEPRtItemIds(i), gEPPriceTypes(i),
1432 gEPActiveFlags(i), 1,
1433 NVL(sub.orc_asl_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1434 NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1435 NVL(sub.orc_contract_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1436 NVL(sub.orc_contract_line_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1437 -- PCREDDY: 3234875 : No template id for Contract lines
1438 DECODE(gEPPriceTypes(i), 'BLANKET', TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER),
1439 NVL(sub.orc_template_id, TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER))),
1440 DECODE(gEPPriceTypes(i), 'BLANKET', TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER),
1441 NVL(sub.orc_template_line_id, TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER))),
1442 NVL(item.orc_item_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1443 sub.orc_category_id, gEPOrgIds(i),
1444 sub.search_type, sub.unit_price,
1445 sub.currency, sub.unit_of_measure,
1446 sub.functional_price, sub.orc_supplier_site_code,
1447 sub.orc_contract_num, sub.orc_contract_line_num,
1448 gEPRateTypes(i), gEPRateDates(i), gEPRates(i),
1449 gEPSupplierNumbers(i), gEPSupplierContactIds(i),
1450 gEPItemRevisions(i), gEPLineTypeIds(i), gEPBuyerIds(i),
1451 NULL, gUpgradeUserId, gUpgradeUserId, sysdate,
1452 gUpgradeUserId, sub.creation_date, gUpgradePhaseId,
1453 gUpgradeUserId, gUpgradeUserId, sysdate
1454 FROM ICX_POR_ORACLE_ITEM_SUBTABLE sub,
1455 ICX_POR_ITEMS item
1456 WHERE sub.rowid = gEPRowIds(i)
1457 AND item.rt_item_id = sub.rt_item_id;
1458
1459 COMMIT;
1460
1461 xErrLoc := 150;
1462 cleanTables(xMode);
1463
1464 xErrLoc := 200;
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 ROLLBACK;
1468 xReturnErr :=
1469 'moveExtractedPrices(' ||xErrLoc||'): '||sqlerrm;
1470 gReturnErr := gReturnErr || '-->' || xReturnErr;
1471 ICX_POR_EXT_UTL.pushError(xReturnErr);
1472 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
1473 raise ICX_POR_EXT_UTL.gException;
1474 END moveExtractedPrices;
1475
1476 -- Move data into ICX_CAT_ITEM_PRICES from ICX_POR_PRICE_LIST_LINES
1477 PROCEDURE moveBulkloadedPrices IS
1478 xErrLoc PLS_INTEGER;
1479 xReturnErr VARCHAR2(2000);
1480 xMode VARCHAR2(20) := 'BULKLOADED_PRICE';
1481
1482 BEGIN
1483 xErrLoc := 50;
1484 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1485 'moveBulkloadedPrices[Count: ' || gBPRtItemIds.COUNT || ']');
1486
1487 xErrLoc := 100;
1488 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
1489 FOR i in 1..gBPRtItemIds.COUNT LOOP
1490 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1491 snapShot(i, xMode));
1492 END LOOP;
1493 END IF;
1494
1495 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1496 'ICX_CAT_ITEM_PRICES from ICX_POR_PRICE_LIST_LINES');
1497
1498 xErrLoc := 200;
1499 FORALL i IN 1..gBPRtItemIds.COUNT
1500 INSERT INTO ICX_CAT_ITEM_PRICES
1501 (rt_item_id, price_type,
1502 active_flag, object_version_number,
1503 asl_id, supplier_site_id,
1504 contract_id, contract_line_id,
1505 template_id, template_line_id,
1506 inventory_item_id,
1507 mtl_category_id, org_id,
1508 search_type, unit_price,
1509 currency, unit_of_measure,
1510 functional_price,
1511 supplier_site_code,
1512 contract_num, contract_line_num,
1513 price_list_id, last_update_login,
1514 last_updated_by, last_update_date,
1515 created_by, creation_date, request_id,
1516 program_application_id, program_id, program_update_date)
1517 SELECT gBPRtItemIds(i), gBPPriceTypes(i),
1518 gBRActiveFlgs(i), 1,
1519 ICX_POR_EXT_ITEM.NULL_NUMBER,
1520 NVL(gBPSupplierSiteIds(i), ICX_POR_EXT_ITEM.NULL_NUMBER),
1521 NVL(prl.contract_reference_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
1522 ICX_POR_EXT_ITEM.NULL_NUMBER,
1523 TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER),
1524 ICX_POR_EXT_ITEM.NULL_NUMBER,
1525 ICX_POR_EXT_ITEM.NULL_NUMBER,
1526 ICX_POR_EXT_ITEM.NULL_NUMBER, gBPOrgIds(i),
1527 'SUPPLIER', prl.unit_price,
1528 prl.currency_code, prl.uom,
1529 NULL, -- Leave functional_price as NULL
1530 prl.supplier_site,
1531 prl.contract_reference_num, NULL,
1532 prl.header_id,
1533 gUpgradeUserId, gUpgradeUserId, sysdate,
1534 gUpgradeUserId, prl.creation_date, gUpgradePhaseId,
1535 gUpgradeUserId, gUpgradeUserId, sysdate
1536 FROM ICX_POR_PRICE_LIST_LINES prl
1537 WHERE prl.rowid = gBPRowIds(i);
1538
1539 COMMIT;
1540
1541 xErrLoc := 300;
1542 cleanTables(xMode);
1543
1544 xErrLoc := 400;
1545 EXCEPTION
1546 WHEN OTHERS THEN
1547 ROLLBACK;
1548 xReturnErr :=
1549 'moveBulkloadedPrices(' ||xErrLoc||'): '||sqlerrm;
1550 gReturnErr := gReturnErr || '-->' || xReturnErr;
1551 ICX_POR_EXT_UTL.pushError(xReturnErr);
1552 ICX_POR_EXT_UTL.pushError(snapShot(SQL%ROWCOUNT+1, xMode));
1553 raise ICX_POR_EXT_UTL.gException;
1554 END moveBulkloadedPrices;
1555
1556 -- Move data
1557 PROCEDURE moveData (pMode VARCHAR2) IS
1558 xErrLoc PLS_INTEGER;
1559 xReturnErr VARCHAR2(2000);
1560
1561 BEGIN
1562 xErrLoc := 50;
1563
1564 IF ((pMode = 'OUTLOOP' AND gIRtItemIds.COUNT > 0) OR
1565 gIRtItemIds.COUNT >= gCommitSize)
1566 THEN
1567 xErrLoc := 100;
1568 moveItems;
1569 END IF;
1570 IF ((pMode = 'OUTLOOP' AND gITRtItemIds.COUNT > 0) OR
1571 gITRtItemIds.COUNT >= gCommitSize)
1572 THEN
1573 xErrLoc := 200;
1574 updateItemsTLP;
1575 END IF;
1576 IF ((pMode = 'OUTLOOP' AND gCIRtItemIds.COUNT > 0) OR
1577 gCIRtItemIds.COUNT >= gCommitSize)
1578 THEN
1579 xErrLoc := 300;
1580 createTempCategoryItems;
1581 END IF;
1582 IF ((pMode = 'OUTLOOP' AND gEPRtItemIds.COUNT > 0) OR
1583 gEPRtItemIds.COUNT >= gCommitSize)
1584 THEN
1585 xErrLoc := 400;
1586 moveExtractedPrices;
1587 END IF;
1588 IF ((pMode = 'OUTLOOP' AND gBPRtItemIds.COUNT > 0) OR
1589 gBPRtItemIds.COUNT >= gCommitSize)
1590 THEN
1591 xErrLoc := 500;
1592 moveBulkloadedPrices;
1593 END IF;
1594
1595 xErrLoc := 600;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 ROLLBACK;
1599 xReturnErr :=
1600 'moveData(' ||xErrLoc||'): '||sqlerrm;
1601 gReturnErr := gReturnErr || '-->' || xReturnErr;
1602 ICX_POR_EXT_UTL.pushError(xReturnErr);
1603 raise ICX_POR_EXT_UTL.gException;
1604 END moveData;
1605
1606 -- Update category attributes of ICX_CAT_EXT_ITEMS_TLP
1607 -- Note: We use icx_cat_items_b.object_version_number to
1608 -- store old_rt_item_id during the upgrade process
1609 PROCEDURE updateExtItemsTLP IS
1610 CURSOR cAllCategories IS
1611 SELECT cat.rt_category_id
1612 FROM icx_cat_categories_tl cat
1613 WHERE cat.type = ICX_POR_EXT_CLASS.CATEGORY_TYPE
1614 AND cat.language = (SELECT language_code
1615 FROM fnd_languages
1616 WHERE installed_flag = 'B')
1617 AND EXISTS (SELECT 'category attributes'
1618 FROM icx_por_descriptors_tl des
1619 WHERE des.rt_category_id = cat.rt_category_id)
1620 AND EXISTS (SELECT 'items belong to this category'
1621 FROM icx_cat_category_items ci,
1622 icx_cat_items_b i
1623 WHERE cat.rt_category_id = ci.rt_category_id
1624 AND i.rt_item_id = ci.rt_item_id);
1625
1626 -- Cursor for all rt_item_ids with a rt_category_id
1627 CURSOR cCatItems(pRtCategoryId IN NUMBER) IS
1628 SELECT i.rt_item_id rt_item_id,
1629 decode(i.object_version_number, 1,
1630 i.rt_item_id,
1631 i.object_version_number) old_rt_item_id
1632 FROM icx_cat_category_items ci,
1633 icx_cat_items_b i
1634 WHERE ci.rt_category_id = pRtCategoryId
1635 AND ci.rt_item_id = i.rt_item_id;
1636
1637 xErrLoc PLS_INTEGER;
1638 xReturnErr VARCHAR2(2000);
1639
1640 xRtItemIds DBMS_SQL.NUMBER_TABLE;
1641 xOldRtItemIds DBMS_SQL.NUMBER_TABLE;
1642
1643 xSqlString VARCHAR2(4000);
1644 xCursorId NUMBER;
1645 xResultCount NUMBER;
1646
1647 BEGIN
1648 xErrLoc := 50;
1649 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1650 'Update category attributes of ICX_CAT_EXT_ITEMS_TLP');
1651
1652 FOR all_category IN cAllCategories LOOP
1653 xErrLoc := 100;
1654 -- Fetch category attributes
1655 fetchAttributes(all_category.rt_category_id);
1656
1657 IF (gDynSqlCatAttributes IS NOT NULL) THEN
1658 xErrLoc := 150;
1659 OPEN cCatItems(all_category.rt_category_id);
1660
1661 xErrLoc := 180;
1662 LOOP
1663 xRtItemIds.DELETE;
1664 xOldRtItemIds.DELETE;
1665
1666 xErrLoc := 200;
1667 FETCH cCatItems
1668 BULK COLLECT INTO xRtItemIds, xOldRtItemIds
1669 LIMIT gCommitSize;
1670 EXIT WHEN xRtItemIds.COUNT = 0;
1671
1672 xErrLoc := 210;
1673 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
1674 FOR i in 1..xRtItemIds.COUNT LOOP
1675 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1676 'xRtItemIds('||i||'): '||xRtItemIds(i)||', '||
1677 'xOldRtItemIds('||i||'): '||xOldRtItemIds(i));
1678 END LOOP;
1679 END IF;
1680
1681 -- Dynamic SQL to update category attributes of ICX_CAT_EXT_ITEMS_TLP
1682
1683 xErrLoc := 220;
1684 xCursorId := DBMS_SQL.open_cursor;
1685 xErrLoc := 230;
1686 DBMS_SQL.parse(xCursorId, gDynSqlCatAttributes, DBMS_SQL.NATIVE);
1687 xErrLoc := 240;
1688 DBMS_SQL.bind_array(xCursorId, ':new_rt_item_id', xRtItemIds);
1689 xErrLoc := 260;
1690 DBMS_SQL.bind_array(xCursorId, ':old_rt_item_id', xOldRtItemIds);
1691 xErrLoc := 270;
1692 xResultCount := DBMS_SQL.execute(xCursorId);
1693 xErrLoc := 280;
1694 DBMS_SQL.close_cursor(xCursorId);
1695
1696 xErrLoc := 300;
1697 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL THEN
1698 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1699 'Restore ICX_CAT_ITEMS_B.object_version_number to 1');
1700 END IF;
1701
1702 xErrLoc := 320;
1703 -- Let's restore object_version_number to 1
1704 FORALL i IN 1..xRtItemIds.COUNT
1705 UPDATE icx_cat_items_b
1706 SET object_version_number = 1
1707 WHERE rt_item_id = xRtItemIds(i);
1708
1709 COMMIT;
1710 xErrLoc := 350;
1711 END LOOP;
1712
1713 xErrLoc := 400;
1714 CLOSE cCatItems;
1715 END IF;
1716
1717 END LOOP;
1718
1719 xErrLoc := 700;
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 IF (cAllCategories%ISOPEN) THEN
1723 CLOSE cAllCategories;
1724 END IF;
1725 IF (cCatItems%ISOPEN) THEN
1726 CLOSE cCatItems;
1727 END IF;
1728
1729 ROLLBACK;
1730 xReturnErr :=
1731 'updateExtItemsTLP(' ||xErrLoc||'): '||sqlerrm;
1732 gReturnErr := gReturnErr || '-->' || xReturnErr;
1733 ICX_POR_EXT_UTL.pushError(xReturnErr);
1734 raise ICX_POR_EXT_UTL.gException;
1735 END updateExtItemsTLP;
1736
1737 -- Create unextracted internal item price record
1738 -- Only need to create row in icx_cat_item_prices
1739 PROCEDURE createInternalItemPrices IS
1740 CURSOR cInternalItemPrices IS
1741 SELECT p.rowid
1742 FROM icx_cat_item_prices p
1743 WHERE p.price_type = 'INTERNAL_TEMPLATE'
1744 AND NOT EXISTS (SELECT 'already upgraded'
1745 FROM icx_cat_item_prices p2
1746 WHERE p2.rt_item_id = p.rt_item_id
1747 AND p2.price_type = 'INTERNAL_ITEM');
1748 xErrLoc PLS_INTEGER;
1749 xReturnErr VARCHAR2(2000);
1750 xRowIds DBMS_SQL.UROWID_TABLE;
1751 BEGIN
1752 xErrLoc := 50;
1753 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1754 'create missing internal item prices');
1755
1756 OPEN cInternalItemPrices;
1757 xErrLoc := 100;
1758 LOOP
1759 xRowIds.DELETE;
1760 xErrLoc := 120;
1761 FETCH cInternalItemPrices
1762 BULK COLLECT INTO xRowIds
1763 LIMIT gCommitSize;
1764 EXIT WHEN xRowIds.COUNT = 0;
1765
1766 xErrLoc := 160;
1767 FORALL i IN 1..xRowIds.COUNT
1768 INSERT INTO ICX_CAT_ITEM_PRICES
1769 (rt_item_id, price_type,
1770 active_flag, object_version_number,
1771 asl_id, supplier_site_id,
1772 contract_id, contract_line_id,
1773 template_id, template_line_id,
1774 inventory_item_id,
1775 mtl_category_id, org_id,
1776 search_type, unit_price,
1777 currency, unit_of_measure,
1778 functional_price,
1779 supplier_site_code,
1780 contract_num, contract_line_num,
1781 price_list_id, last_update_login,
1782 last_updated_by, last_update_date,
1783 created_by, creation_date, request_id,
1784 program_application_id, program_id, program_update_date)
1785 SELECT p.rt_item_id, 'INTERNAL_ITEM',
1786 'N', 1,
1787 ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
1788 ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
1789 ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
1790 mi.inventory_item_id,
1791 p.mtl_category_id, p.org_id,
1792 'INTERNAL',
1793 mi.list_price_per_unit unit_price,
1794 gsb.currency_code currency,
1795 NVL(muom.uom_code, mi.primary_uom_code) unit_of_measure,
1796 mi.list_price_per_unit functional_price,
1797 NULL, NULL, NULL, NULL,
1798 gUpgradeUserId, gUpgradeUserId, sysdate,
1799 gUpgradeUserId, sysdate, gUpgradeUserId,
1800 gUpgradeUserId, gUpgradeUserId, sysdate
1801 FROM icx_cat_item_prices p,
1802 mtl_system_items_kfv mi,
1803 gl_sets_of_books gsb,
1804 financials_system_params_all fsp,
1805 mtl_units_of_measure_tl muom
1806 WHERE p.inventory_item_id = mi.inventory_item_id
1807 AND p.org_id = fsp.org_id
1808 AND mi.organization_id = fsp.inventory_organization_id
1809 AND mi.unit_of_issue = muom.unit_of_measure(+)
1810 AND muom.language(+) = ICX_POR_EXTRACTOR.gBaseLang
1811 AND fsp.set_of_books_id = gsb.set_of_books_id
1812 AND p.rowid = xRowIds(i);
1813
1814 COMMIT;
1815 xErrLoc := 180;
1816 END LOOP;
1817 xErrLoc := 300;
1818 CLOSE cInternalItemPrices;
1819
1820 xErrLoc := 600;
1821 EXCEPTION
1822 WHEN OTHERS THEN
1823 ROLLBACK;
1824 IF (cInternalItemPrices%ISOPEN) THEN
1825 CLOSE cInternalItemPrices;
1826 END IF;
1827 xReturnErr :=
1828 'createInternalItemPrices(' ||xErrLoc||'): '||sqlerrm;
1829 gReturnErr := gReturnErr || '-->' || xReturnErr;
1830 ICX_POR_EXT_UTL.pushError(xReturnErr);
1831 raise ICX_POR_EXT_UTL.gException;
1832 END createInternalItemPrices;
1833
1834 -- Create unextracted purchasing item price record
1835 -- If this item has internal part, will reuse the internal rt_item_id
1836 -- This procedure has a cursor on item_prices and also modifies the
1837 -- icx_cat_item_prices. This will cause snapshot too old error.
1838 -- To avoid this, insert into item_prices with request_id=-20 and
1839 -- make the cursor not pick these request ids(I.e. dont select the
1840 -- rows which have been inserted by this phase)
1841 PROCEDURE createPurchasingItemPrices IS
1842 snap_shot_too_old EXCEPTION;
1843 PRAGMA EXCEPTION_INIT(snap_shot_too_old, -1555);
1844 CURSOR cPurchasingItemPrices IS
1845 SELECT NVL(p.rt_item_id, icx_por_itemid.nextval) rt_item_id,
1846 i.rt_item_id old_rt_item_id,
1847 p.rt_item_id internal_rt_item_id,
1848 i.internal_item_id inventory_item_id,
1849 i.org_id org_id,
1850 getPrimaryCategoryId(i.rt_item_id) rt_category_id
1851 FROM icx_cat_items_b i,
1852 icx_cat_item_prices p
1853 WHERE i.internal_item_id IS NOT NULL
1854 AND p.request_id <> gUpgradePhaseId
1855 AND EXISTS (SELECT 'supplier sourced documents'
1856 FROM icx_cat_item_prices p2
1857 WHERE p2.inventory_item_id = i.internal_item_id
1858 AND p2.org_id = i.org_id
1859 AND p2.price_type IN ('BLANKET', 'QUOTATION',
1860 'TEMPLATE', 'ASL'))
1861 AND i.internal_item_id = p.inventory_item_id (+)
1862 AND i.org_id = p.org_id (+)
1863 AND p.price_type(+) = 'INTERNAL_ITEM'
1864 AND NOT EXISTS (SELECT 'already upgraded'
1865 FROM icx_cat_item_prices p2
1866 WHERE p2.rt_item_id = i.internal_item_id
1867 AND p2.org_id = i.org_id
1868 AND p2.price_type = 'PURCHASING_ITEM');
1869 xErrLoc PLS_INTEGER;
1870 xCount PLS_INTEGER;
1871 xReturnErr VARCHAR2(2000);
1872
1873 xRtItemIds DBMS_SQL.NUMBER_TABLE;
1874 xOldRtItemIds DBMS_SQL.NUMBER_TABLE;
1875 xInternalRtItemIds DBMS_SQL.NUMBER_TABLE;
1876 xInventoryItemIds DBMS_SQL.NUMBER_TABLE;
1877 xOrgIds DBMS_SQL.NUMBER_TABLE;
1878 xRtCategoryIds DBMS_SQL.NUMBER_TABLE;
1879 xLanguage VARCHAR2(4);
1880
1881 BEGIN
1882 xErrLoc := 100;
1883 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1884 'create missing purchasing item prices');
1885
1886 xErrLoc := 101;
1887 SELECT language_code INTO xLanguage
1888 FROM fnd_languages
1889 WHERE installed_flag = 'B';
1890
1891 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1892 'base language:' ||xLanguage);
1893
1894 xErrLoc := 102;
1895 OPEN cPurchasingItemPrices;
1896 LOOP
1897
1898 BEGIN
1899 xRtItemIds.DELETE;
1900 xOldRtItemIds.DELETE;
1901 xInternalRtItemIds.DELETE;
1902 xInventoryItemIds.DELETE;
1903 xOrgIds.DELETE;
1904 xRtCategoryIds.DELETE;
1905
1906 xErrLoc := 120;
1907 FETCH cPurchasingItemPrices
1908 BULK COLLECT INTO xRtItemIds,
1909 xOldRtItemIds,
1910 xInternalRtItemIds,
1911 xInventoryItemIds,
1912 xOrgIds,
1913 xRtCategoryIds
1914 LIMIT gCommitSize;
1915 EXIT WHEN xRtItemIds.COUNT = 0;
1916
1917 xErrLoc := 160;
1918 FORALL i IN 1..xRtItemIds.COUNT
1919 INSERT INTO ICX_CAT_ITEM_PRICES
1920 (rt_item_id, price_type,
1921 active_flag, object_version_number,
1922 asl_id, supplier_site_id,
1923 contract_id, contract_line_id,
1924 template_id, template_line_id,
1925 inventory_item_id,
1926 mtl_category_id, org_id,
1927 search_type, unit_price,
1928 currency, unit_of_measure,
1929 functional_price,
1930 supplier_site_code,
1931 contract_num, contract_line_num,
1932 price_list_id, last_update_login,
1933 last_updated_by, last_update_date,
1934 created_by, creation_date, request_id,
1935 program_application_id, program_id, program_update_date)
1936 SELECT xRtItemIds(i), 'PURCHASING_ITEM',
1937 'N', 1,
1938 ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
1939 ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
1940 ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
1941 mi.inventory_item_id,
1942 mic.category_id mtl_category_id, xOrgIds(i),
1943 'SUPPLIER',
1944 mi.list_price_per_unit unit_price,
1945 gsb.currency_code currency,
1946 mi.primary_uom_code unit_of_measure,
1947 mi.list_price_per_unit functional_price,
1948 NULL, NULL, NULL, NULL,
1949 gUpgradeUserId, gUpgradeUserId, sysdate,
1950 gUpgradeUserId, sysdate, gUpgradePhaseId,
1951 gUpgradeUserId, gUpgradeUserId, sysdate
1952 FROM mtl_system_items_kfv mi,
1953 mtl_item_categories mic,
1954 gl_sets_of_books gsb,
1955 financials_system_params_all fsp,
1956 --Bug#3581356
1957 --Since categories are already upgraded,
1958 --so join with icx_cat_categories_tl to get the valid category
1959 icx_cat_categories_tl ictl
1960 WHERE mi.inventory_item_id = xInventoryItemIds(i)
1961 AND fsp.org_id = xOrgIds(i)
1962 AND mi.organization_id = fsp.inventory_organization_id
1963 AND mi.inventory_item_id = mic.inventory_item_id
1964 AND mic.organization_id = mi.organization_id
1965 --Bug#3581356
1966 --Join with icx_cat_categories_tl to get the valid category
1967 --Add the join between gl_sets_of_books and
1968 --financials_system_params_all
1969 AND fsp.set_of_books_id = gsb.set_of_books_id
1970 AND ictl.key = to_char(mic.category_id)
1971 AND ictl.language = xLanguage
1972 AND ictl.type = 2;
1973 COMMIT;
1974
1975 xErrLoc := 180;
1976 FOR i IN 1..xRtItemIds.COUNT LOOP
1977 IF xInternalRtItemIds IS NULL THEN
1978 -- Set global PL/SQL tables for Items
1979 gContinueExtItemTlp := TRUE;
1980 xCount := gIRtItemIds.COUNT + 1;
1981 gIRtItemIds(xCount) := xRtItemIds(i);
1982 gIOldRtItemIds(xCount) := xOldRtItemIds(i);
1983 gIOrgIds(xCount) := xOrgIds(i);
1984 gISupplierPartNums(xCount) := NULL;
1985 gIRtCategoryIds(xCount) := xRtCategoryIds(i);
1986 gIExtractorUpdatedFlags(xCount) := 'Y';
1987 END IF;
1988 END LOOP;
1989
1990 xErrLoc := 200;
1991 moveData('INLOOP');
1992
1993 xErrLoc := 280;
1994 EXCEPTION
1995 WHEN snap_shot_too_old THEN
1996 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
1997 'ORA-01555: snapshot too old: caught at ' ||
1998 'ICX_CAT_FPI_UPGRADE.upgrade-' ||xErrLoc ||
1999 ', SQLERRM:' ||SQLERRM ||
2000 '; so close the cursor and repoen the cursor');
2001 xErrLoc := 282;
2002 ICX_POR_EXT_UTL.extAFCommit;
2003 IF (cPurchasingItemPrices%ISOPEN) THEN
2004 xErrLoc := 284;
2005 CLOSE cPurchasingItemPrices;
2006 xErrLoc := 286;
2007 OPEN cPurchasingItemPrices;
2008 END IF;
2009 END;
2010
2011 xErrLoc := 288;
2012
2013
2014 END LOOP;
2015
2016 gUpgradePhaseId := gUpgradeUserId;
2017
2018 xErrLoc := 300;
2019 moveData('OUTLOOP');
2020 CLOSE cPurchasingItemPrices;
2021
2022 xErrLoc := 600;
2023 EXCEPTION
2024 WHEN OTHERS THEN
2025 ROLLBACK;
2026 IF (cPurchasingItemPrices%ISOPEN) THEN
2027 CLOSE cPurchasingItemPrices;
2028 END IF;
2029 xReturnErr :=
2030 'createPurchasingItemPrices(' ||xErrLoc||'): '||sqlerrm;
2031 gReturnErr := gReturnErr || '-->' || xReturnErr;
2032 ICX_POR_EXT_UTL.pushError(xReturnErr);
2033 raise ICX_POR_EXT_UTL.gException;
2034 END createPurchasingItemPrices;
2035
2036
2037 --update the request_id of icx_cat_item_prices from -20 to -9
2038 -- (-20) is the phase of create unextracted purchasing items
2039 --
2040 PROCEDURE updateRequestId IS
2041
2042 xErrLoc PLS_INTEGER;
2043 xRtItemIds DBMS_SQL.NUMBER_TABLE;
2044 xReturnErr VARCHAR2(2000);
2045
2046 CURSOR cRequestId IS
2047 SELECT p.rt_item_id
2048 FROM icx_cat_item_prices p
2049 WHERE p.request_id = CREATE_PURCHASING_PHASE;
2050
2051 BEGIN
2052 xErrLoc := 710;
2053 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
2054 'update request_id of item prices table');
2055
2056 OPEN cRequestId;
2057 LOOP
2058 xRtItemIds.DELETE;
2059
2060 xErrLoc := 720;
2061 FETCH cRequestId
2062 BULK COLLECT INTO xRtItemIds
2063 LIMIT gCommitSize;
2064 EXIT WHEN xRtItemIds.COUNT = 0;
2065
2066 xErrLoc := 740;
2067 FORALL i IN 1..xRtItemIds.COUNT
2068 UPDATE ICX_CAT_ITEM_PRICES
2069 SET request_id = gUpgradeUserId
2070 WHERE rt_item_id = xRtItemIds(i);
2071 COMMIT;
2072
2073 END LOOP;
2074
2075 xErrLoc := 760;
2076 CLOSE cRequestId;
2077
2078 EXCEPTION
2079 WHEN OTHERS THEN
2080 ROLLBACK;
2081 IF (cRequestId%ISOPEN) THEN
2082 CLOSE cRequestId;
2083 END IF;
2084 xReturnErr :=
2085 'updateRequestId(' ||xErrLoc||'): '||sqlerrm;
2086 gReturnErr := gReturnErr || '-->' || xReturnErr;
2087 ICX_POR_EXT_UTL.pushError(xReturnErr);
2088 raise ICX_POR_EXT_UTL.gException;
2089 END updateRequestId;
2090
2091 -- Move records from icx_por_price_lists to icx_cat_price_lists
2092 PROCEDURE movePriceLists IS
2093 xErrLoc PLS_INTEGER;
2094 xReturnErr VARCHAR2(2000);
2095 BEGIN
2096 xErrLoc := 50;
2097 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
2098 'Move records from icx_por_price_lists to icx_cat_price_lists');
2099
2100 xErrLoc := 100;
2101 LOOP
2102 INSERT INTO ICX_CAT_PRICE_LISTS
2103 (price_list_id, name, supplier_id, buyer_id,
2104 description, currency, creation_date, created_by,
2105 last_update_date, last_updated_by, last_update_login,
2106 request_id, begindate, enddate, status,
2107 published_date, outdated_date, approval_date,
2108 rejected_date, deleted_date, buyercomments,
2109 action, type, parent_header_id)
2110 SELECT
2111 header_id, name, supplier_id, buyer_id,
2112 description, currency_code, creation_date, created_by,
2113 sysdate, gUpgradeUserId, gUpgradeUserId,
2114 job_number, begindate, enddate, status,
2115 published_date, outdated_date, approval_date,
2116 rejected_date, deleted_date, buyercomments,
2117 action, type, parent_header_id
2118 FROM ICX_POR_PRICE_LISTS old_list
2119 WHERE NOT EXISTS (SELECT 'Already upgraded'
2120 FROM ICX_CAT_PRICE_LISTS new_list
2121 WHERE old_list.header_id = new_list.price_list_id)
2122 AND ROWNUM <= gCommitSize;
2123
2124 EXIT WHEN SQL%ROWCOUNT < gCommitSize;
2125
2126 COMMIT;
2127 xErrLoc := 200;
2128 END LOOP;
2129 xErrLoc := 300;
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132 ROLLBACK;
2133 xReturnErr :=
2134 'movePriceLists(' ||xErrLoc||'): '||sqlerrm;
2135 gReturnErr := gReturnErr || '-->' || xReturnErr;
2136 ICX_POR_EXT_UTL.pushError(xReturnErr);
2137 raise ICX_POR_EXT_UTL.gException;
2138 END movePriceLists;
2139
2140 -- Update POR_FAVORITE_LIST_LINES
2141 PROCEDURE updateFavoriteList IS
2142 xErrLoc PLS_INTEGER;
2143 xReturnErr VARCHAR2(2000);
2144 BEGIN
2145 xErrLoc := 100;
2146 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2147 FOR i in 1..gUpFavRowIds.COUNT LOOP
2148 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2149 'rt_item_id: ' || gUpFavRtItemIds(i) ||
2150 ', rowid: ' || gUpFavRowIds(i));
2151 END LOOP;
2152 END IF;
2153
2154 xErrLoc := 120;
2155 FORALL i IN 1..gUpFavRowIds.COUNT
2156 UPDATE por_favorite_list_lines
2157 SET rt_item_id = gUpFavRtItemIds(i)
2158 WHERE rowid = gUpFavRowIds(i);
2159 xErrLoc := 200;
2160 gUpFavRtItemIds.DELETE;
2161 gUpFavRowIds.DELETE;
2162 xErrLoc := 300;
2163 COMMIT;
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 ROLLBACK;
2167 xReturnErr :=
2168 'updateFavoriteList(' ||xErrLoc||'): '||sqlerrm;
2169 gReturnErr := gReturnErr || '-->' || xReturnErr;
2170 ICX_POR_EXT_UTL.pushError(xReturnErr);
2171 raise ICX_POR_EXT_UTL.gException;
2172 END updateFavoriteList;
2173
2174 -- Insert POR_FAVORITE_LIST_LINES
2175 PROCEDURE insertFavoriteList IS
2176 xErrLoc PLS_INTEGER;
2177 xReturnErr VARCHAR2(2000);
2178 BEGIN
2179 xErrLoc := 100;
2180 IF (ICX_POR_EXT_UTL.gDebugLevel = ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2181 FOR i in 1..gInFavRowIds.COUNT LOOP
2182 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2183 'rt_item_id: ' || gInFavRtItemIds(i) ||
2184 ', rowid: ' || gInFavRowIds(i));
2185 END LOOP;
2186 END IF;
2187
2188 xErrLoc := 120;
2189 FORALL i IN 1..gInFavRowIds.COUNT
2190 INSERT INTO por_favorite_list_lines
2191 (favorite_list_line_id,
2192 favorite_list_id,
2193 last_update_date,
2194 last_updated_by,
2195 creation_date,
2196 created_by,
2197 last_update_login,
2198 source_doc_header_id,
2199 source_doc_line_id,
2200 item_id,
2201 item_description,
2202 line_type_id,
2203 item_revision,
2204 category_id,
2205 unit_meas_lookup_code,
2206 unit_price,
2207 suggested_vendor_id,
2208 suggested_vendor_name,
2209 suggested_vendor_site_id,
2210 suggested_vendor_site,
2211 suggested_vendor_contact_id,
2212 suggested_vendor_contact,
2213 supplier_url,
2214 suggested_buyer_id,
2215 suggested_buyer,
2216 supplier_item_num,
2217 manufacturer_id,
2218 manufacturer_name,
2219 manufacturer_part_number,
2220 rfq_required_flag,
2221 attribute_category,
2222 attribute1,
2223 attribute2,
2224 attribute3,
2225 attribute4,
2226 attribute5,
2227 attribute6,
2228 attribute7,
2229 attribute8,
2230 attribute9,
2231 attribute10,
2232 attribute11,
2233 attribute12,
2234 attribute13,
2235 attribute14,
2236 attribute15,
2237 category,
2238 rt_item_id,
2239 rt_category_id,
2240 suggested_vendor_contact_phone,
2241 new_supplier,
2242 asl_id,
2243 template_name,
2244 template_line_num,
2245 price_list_id,
2246 currency,
2247 rate_type,
2248 rate)
2249 SELECT
2250 por_favorite_list_lines_s.nextval,
2251 favorite_list_id,
2252 sysdate,
2253 gUpgradeUserId,
2254 sysdate,
2255 gUpgradeUserId,
2256 gUpgradeUserId,
2257 source_doc_header_id,
2258 source_doc_line_id,
2259 item_id,
2260 item_description,
2261 line_type_id,
2262 item_revision,
2263 category_id,
2264 unit_meas_lookup_code,
2265 unit_price,
2266 suggested_vendor_id,
2267 suggested_vendor_name,
2268 suggested_vendor_site_id,
2269 suggested_vendor_site,
2270 suggested_vendor_contact_id,
2271 suggested_vendor_contact,
2272 supplier_url,
2273 suggested_buyer_id,
2274 suggested_buyer,
2275 supplier_item_num,
2276 manufacturer_id,
2277 manufacturer_name,
2278 manufacturer_part_number,
2279 rfq_required_flag,
2280 attribute_category,
2281 attribute1,
2282 attribute2,
2283 attribute3,
2284 attribute4,
2285 attribute5,
2286 attribute6,
2287 attribute7,
2288 attribute8,
2289 attribute9,
2290 attribute10,
2291 attribute11,
2292 attribute12,
2293 attribute13,
2294 attribute14,
2295 attribute15,
2296 category,
2297 gInFavRtItemIds(i),
2298 rt_category_id,
2299 suggested_vendor_contact_phone,
2300 new_supplier,
2301 asl_id,
2302 template_name,
2303 template_line_num,
2304 price_list_id,
2305 currency,
2306 rate_type,
2307 rate
2308 FROM por_favorite_list_lines
2309 WHERE rowid = gInFavRowIds(i);
2310 xErrLoc := 200;
2311 gInFavRowIds.DELETE;
2312 gInFavRowIds.DELETE;
2313 xErrLoc := 300;
2314 COMMIT;
2315 EXCEPTION
2316 WHEN OTHERS THEN
2317 ROLLBACK;
2318 xReturnErr :=
2319 'insertFavoriteList(' ||xErrLoc||'): '||sqlerrm;
2320 gReturnErr := gReturnErr || '-->' || xReturnErr;
2321 ICX_POR_EXT_UTL.pushError(xReturnErr);
2322 raise ICX_POR_EXT_UTL.gException;
2323 END insertFavoriteList;
2324
2325 -- Upgrade POR_FAVORITE_LIST_LINES
2326 PROCEDURE upgradeFavoriteList IS
2327 CURSOR cFavoriteListLines IS
2328 SELECT rowid,
2329 nvl(source_doc_header_id, ICX_POR_EXT_ITEM.NULL_NUMBER) contract_id,
2330 nvl(source_doc_line_id, ICX_POR_EXT_ITEM.NULL_NUMBER) contract_line_id,
2331 nvl(asl_id, ICX_POR_EXT_ITEM.NULL_NUMBER) asl_id,
2332 nvl(template_name, to_char(ICX_POR_EXT_ITEM.NULL_NUMBER)) template_id,
2333 nvl(template_line_num, ICX_POR_EXT_ITEM.NULL_NUMBER) template_line_id,
2334 nvl(item_id, ICX_POR_EXT_ITEM.NULL_NUMBER) inventory_item_id,
2335 price_list_id,
2336 rt_item_id
2337 FROM por_favorite_list_lines
2338 WHERE rt_item_id IS NOT NULL;
2339
2340 TYPE tCursorType IS REF CURSOR;
2341 cMatchRtItemIds tCursorType;
2342
2343 xErrLoc PLS_INTEGER;
2344 xReturnErr VARCHAR2(2000);
2345 xRtItemIds DBMS_SQL.NUMBER_TABLE;
2346 xCount PLS_INTEGER;
2347
2348 BEGIN
2349 xErrLoc := 50;
2350 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
2351 'upgrade POR_FAVORITE_LIST_LINES');
2352
2353 xErrLoc := 100;
2354 FOR favorite IN cFavoriteListLines LOOP
2355 xErrLoc := 120;
2356 IF favorite.price_list_id IS NULL THEN
2357 OPEN cMatchRtItemIds FOR
2358 SELECT distinct rt_item_id
2359 FROM icx_cat_item_prices
2360 WHERE contract_id = favorite.contract_id
2361 AND contract_line_id = favorite.contract_line_id
2362 AND asl_id = favorite.asl_id
2363 AND template_id = favorite.template_id
2364 AND template_line_id = favorite.template_line_id
2365 AND inventory_item_id = favorite.inventory_item_id
2366 AND price_list_id IS NULL;
2367 ELSE
2368 OPEN cMatchRtItemIds FOR
2369 SELECT distinct p.rt_item_id
2370 FROM icx_cat_item_prices p,
2371 icx_cat_items_b i,
2372 icx_por_items oi
2373 WHERE p.price_list_id = favorite.price_list_id
2374 AND p.rt_item_id = i.rt_item_id
2375 AND oi.rt_item_id = favorite.rt_item_id
2376 AND i.supplier = oi.a1
2377 AND i.supplier_part_num = oi.a3;
2378 END IF;
2379
2380 xRtItemIds.DELETE;
2381 xErrLoc := 140;
2382 FETCH cMatchRtItemIds
2383 BULK COLLECT INTO xRtItemIds;
2384 EXIT WHEN xRtItemIds.COUNT = 0;
2385
2386 xErrLoc := 160;
2387 IF xRtItemIds.COUNT = 1 THEN
2388 IF favorite.rt_item_id <> xRtItemIds(1) THEN
2389 xErrLoc := 180;
2390 xCount := gUpFavRowIds.COUNT + 1;
2391 gUpFavRowIds(xCount) := favorite.rowid;
2392 gUpFavRtItemIds(xCount) := xRtItemIds(1);
2393 END IF;
2394 ELSE
2395 xErrLoc := 200;
2396 xCount := gUpFavRowIds.COUNT + 1;
2397 gUpFavRowIds(xCount) := favorite.rowid;
2398 gUpFavRtItemIds(xCount) := xRtItemIds(1);
2399 FOR i IN 2..xRtItemIds.COUNT LOOP
2400 xErrLoc := 220;
2401 xCount := gInFavRowIds.COUNT + 1;
2402 gInFavRowIds(xCount) := favorite.rowid;
2403 gInFavRtItemIds(xCount) := xRtItemIds(i);
2404 END LOOP;
2405 END IF;
2406 xErrLoc := 240;
2407 IF gUpFavRowIds.COUNT >= gCommitSize THEN
2408 updateFavoriteList;
2409 END IF;
2410 xErrLoc := 260;
2411 IF gInFavRowIds.COUNT >= gCommitSize THEN
2412 insertFavoriteList;
2413 END IF;
2414 xErrLoc := 280;
2415 END LOOP;
2416
2417 xErrLoc := 300;
2418 updateFavoriteList;
2419 xErrLoc := 320;
2420 insertFavoriteList;
2421
2422 xErrLoc := 600;
2423 EXCEPTION
2424 WHEN OTHERS THEN
2425 ROLLBACK;
2426 IF (cFavoriteListLines%ISOPEN) THEN
2427 CLOSE cFavoriteListLines;
2428 END IF;
2429 xReturnErr :=
2430 'upgradeFavoriteList(' ||xErrLoc||'): '||sqlerrm;
2431 gReturnErr := gReturnErr || '-->' || xReturnErr;
2432 ICX_POR_EXT_UTL.pushError(xReturnErr);
2433 raise ICX_POR_EXT_UTL.gException;
2434 END upgradeFavoriteList;
2435
2436 PROCEDURE setLog (pLogLevel IN NUMBER,
2437 pLogFile IN VARCHAR2)
2438 IS
2439 BEGIN
2440 gLogLevel := pLogLevel;
2441 gLogFile := pLogFile;
2442 END setLog;
2443
2444 PROCEDURE startLog IS
2445 BEGIN
2446 ICX_POR_EXT_UTL.setDebugLevel(gLogLevel);
2447 ICX_POR_EXT_UTL.setUseFile(1);
2448 ICX_POR_EXT_UTL.setFilePath(ICX_POR_EXT_UTL.UTL_FILE_DIR);
2449 ICX_POR_EXT_UTL.openLog(gLogFile);
2450 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Start...');
2451 END startLog;
2452
2453 PROCEDURE endLog IS
2454 BEGIN
2455 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'End...');
2456 ICX_POR_EXT_UTL.closeLog;
2457 END endLog;
2458
2459 PROCEDURE setCommitSize (pCommitSize IN NUMBER)
2460 IS
2461 BEGIN
2462 gCommitSize := pCommitSize;
2463 END setCommitSize;
2464
2465 PROCEDURE cleanupJobTables IS
2466 xErrLoc PLS_INTEGER;
2467 xReturnErr VARCHAR2(2000);
2468 xIcxSchema VARCHAR2(20);
2469
2470 BEGIN
2471 xErrLoc := 50;
2472 xIcxSchema := ICX_POR_EXT_UTL.getIcxSchema;
2473
2474 xErrLoc := 100;
2475 -- ICX_POR_BATCH_JOBS
2476 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2477 xIcxSchema || '.icx_por_batch_jobs';
2478
2479 xErrLoc := 200;
2480 -- ICX_POR_FAILED_LINES
2481 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2482 xIcxSchema || '.icx_por_failed_lines';
2483
2484 xErrLoc := 300;
2485 -- ICX_POR_FAILED_LINE_MESSAGES
2486 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2487 xIcxSchema || '.icx_por_failed_line_messages';
2488
2489 xErrLoc := 400;
2490 -- ICX_POR_CONTRACT_REFERENCES
2491 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2492 xIcxSchema || '.icx_por_contract_references';
2493
2494 xErrLoc := 500;
2495 EXCEPTION
2496 WHEN OTHERS THEN
2497 ROLLBACK;
2498 xReturnErr :=
2499 'cleanupJobTables(' ||xErrLoc||'): '||sqlerrm;
2500 raise_application_error(-20000, xReturnErr);
2501 END cleanupJobTables;
2502
2503 FUNCTION isAlreadyUpgraded RETURN NUMBER
2504 IS
2505 xResult NUMBER;
2506 BEGIN
2507 SELECT 1
2508 INTO xResult
2509 FROM dual
2510 WHERE EXISTS (SELECT 'schema records'
2511 FROM icx_cat_categories_tl
2512 WHERE rt_category_id > 0)
2513 OR EXISTS (SELECT 'data records'
2514 FROM icx_cat_items_b);
2515 RETURN xResult;
2516 EXCEPTION
2517 WHEN NO_DATA_FOUND THEN
2518 xResult := 0;
2519 RETURN xResult;
2520 END isAlreadyUpgraded;
2521
2522 PROCEDURE rollbackUpgrade IS
2523 xErrLoc PLS_INTEGER;
2524 xReturnErr VARCHAR2(2000);
2525 xIcxSchema VARCHAR2(20);
2526
2527 BEGIN
2528 xErrLoc := 50;
2529 xIcxSchema := ICX_POR_EXT_UTL.getIcxSchema;
2530
2531 xErrLoc := 100;
2532 -- ICX_CAT_ITEMS_B
2533 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2534 xIcxSchema || '.icx_cat_items_b';
2535
2536 xErrLoc := 200;
2537 -- ICX_CAT_ITEMS_TLP
2538 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2539 xIcxSchema || '.icx_cat_items_tlp';
2540
2541 xErrLoc := 300;
2542 -- ICX_CAT_EXT_ITEMS_TLP
2543 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2544 xIcxSchema || '.icx_cat_ext_items_tlp';
2545
2546 xErrLoc := 400;
2547 -- ICX_CAT_CATEGORY_ITEMS
2548 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2549 xIcxSchema || '.icx_cat_category_items';
2550
2551 xErrLoc := 500;
2552 -- ICX_CAT_ITEM_PRICES
2553 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2554 xIcxSchema || '.icx_cat_item_prices';
2555
2556 xErrLoc := 600;
2557 -- ICX_CAT_ITEM_PRICES
2558 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2559 xIcxSchema || '.icx_cat_price_lists';
2560
2561 xErrLoc := 700;
2562 EXCEPTION
2563 WHEN OTHERS THEN
2564 ROLLBACK;
2565 xReturnErr :=
2566 'rollbackUpgrade(' ||xErrLoc||'): '||sqlerrm;
2567 raise_application_error(-20000, xReturnErr);
2568 END rollbackUpgrade;
2569
2570 -- Main upgrade procedure
2571 PROCEDURE upgrade IS
2572 xErrLoc PLS_INTEGER;
2573 xReturnErr VARCHAR2(2000);
2574 CURSOR cAllItems IS
2575 SELECT item.rt_item_id,
2576 getOldPrimaryCategoryId(item.rt_item_id) rt_category_id
2577 FROM icx_por_items item
2578 WHERE NOT EXISTS (SELECT 'already upgraded'
2579 FROM icx_cat_items_b new_item
2580 WHERE item.rt_item_id = new_item.rt_item_id)
2581 AND (EXISTS (SELECT 'extracted price'
2582 FROM icx_por_oracle_item_subtable sub
2583 WHERE sub.rt_item_id = item.rt_item_id) OR
2584 EXISTS (SELECT 'bulkloaded price'
2585 FROM icx_por_price_list_lines pll
2586 WHERE pll.item_id = item.rt_item_id));
2587 BEGIN
2588 xErrLoc := 50;
2589 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Start Data Upgrade...');
2590
2591 xErrLoc := 60;
2592 -- Bug 2813141, job tables should be cleaned before odf applied
2593 -- move this procedure into a pre-upgrade script: icxprupi.sql
2594 -- cleanupJobTables;
2595 initCaches;
2596
2597 xErrLoc := 70;
2598 -- Fetch base attributes
2599 fetchAttributes(0);
2600
2601 cleanTables('ALL');
2602
2603 xErrLoc := 100;
2604 FOR all_item IN cAllItems LOOP
2605 xErrLoc := 140;
2606 gCurrentItem := all_item;
2607 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL THEN
2608 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2609 'gCurrentItem[rt_item_id: ' || gCurrentItem.rt_item_id ||
2610 ', rt_category_id: ' || gCurrentItem.rt_category_id || ']');
2611 END IF;
2612
2613 xErrLoc := 180;
2614 IF gCurrentItem.rt_category_id IS NOT NULL THEN
2615 gContinueExtItemTlp := TRUE;
2616 xErrLoc := 200;
2617 processItems;
2618 xErrLoc := 300;
2619 moveData('INLOOP');
2620 END IF;
2621 END LOOP;
2622 xErrLoc := 400;
2623 moveData('OUTLOOP');
2624
2625 xErrLoc := 500;
2626 movePriceLists;
2627 xErrLoc := 600;
2628 createInternalItemPrices;
2629 xErrLoc := 700;
2630 gUpgradePhaseId := CREATE_PURCHASING_PHASE;
2631 createPurchasingItemPrices;
2632 xErrLoc := 750;
2633 updateRequestId;
2634 xErrLoc := 850;
2635 gUpgradePhaseId := gUpgradeUserId;
2636 IF gContinueExtItemTlp THEN
2637 updateExtItemsTLP;
2638 END IF;
2639
2640 -- sosingha bug# 3285223: Merge b2847879.sql into the main procedure
2641 xErrLoc := 900;
2642 upgradeFavoriteList;
2643
2644 xErrLoc := 1000;
2645 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'End Data Upgrade...');
2646 EXCEPTION
2647 WHEN OTHERS THEN
2648 IF (cAllItems%ISOPEN) THEN
2649 CLOSE cAllItems;
2650 END IF;
2651
2652 rollback;
2653 xReturnErr :=
2654 'upgrade(' || xErrLoc || '): ' || sqlerrm;
2655 gReturnErr := gReturnErr || '-->' || xReturnErr;
2656 ICX_POR_EXT_UTL.pushError(xReturnErr);
2657 ICX_POR_EXT_UTL.printStackTrace;
2658 raise_application_error(-20000, gReturnErr);
2659 END upgrade;
2660
2661 END ICX_CAT_FPI_UPGRADE;