4 --*****************************************************************************
1 PACKAGE BODY AS_CATALOG_MIGRATION as
2 /* $Header: asxmcatb.pls 120.4 2005/12/15 23:03:15 sumani noship $ */
3
5 -- GLOBAL VARIABLES AND CONSTANTS
6 --
7 -- This variable is used to store the inventory create api version numbers.
8 G_CREATE_CAT_API_VER NUMBER := 1.0;
9
10 -- This variable is used to store the application id for ASF
14 G_CONTROL_LEVEL_MASTER NUMBER := 1;
11 G_APPLICATION_ID NUMBER := 522;
12
13 -- Category Set Control Levels
15 G_CONTROL_LEVEL_ORG NUMBER := 2;
16
17 G_DEBUG BOOLEAN := false;
18
19 -- Functional area for product catalog
20 G_FUNCTIONAL_AREA Constant NUMBER := 11;
21
22 G_SME_CATEGORY_SET_NAME Constant Varchar2(20) := 'SME Product Catalog';
23
24 G_PKG_NAME Constant Varchar2(22) := 'AS_CATALOG_MIGRATION';
25
26 --*****************************************************************************
27 -- Declarations
28 --
29 TYPE Name_Count_Tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
30
31 PROCEDURE Process_Categories(p_int_typ_cod_id IN NUMBER,
32 p_structure_id IN NUMBER,
33 p_old_structure_id IN NUMBER,
34 p_category_set_id IN NUMBER,
35 p_control_level IN NUMBER,
36 p_mult_item_cat_assign_flag IN VARCHAR2,
37 p_parent_category_id IN NUMBER,
38 p_category_name IN VARCHAR2,
39 p_description IN VARCHAR2,
40 p_interest_level IN NUMBER,
41 p_expected_purchase IN VARCHAR2,
42 p_level0_enabled_flag IN VARCHAR2,
43 p_level1_enabled_flag IN VARCHAR2,
44 p_level2_enabled_flag IN VARCHAR2,
45 p_attr_group_id IN NUMBER,
46 p_name_count_tab IN OUT NOCOPY Name_Count_Tab,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2,
50 x_category_id OUT NOCOPY NUMBER,
51 x_warning_flag OUT NOCOPY VARCHAR2);
52
53 PROCEDURE Check_Duplicate_Category(
54 x_return_status OUT NOCOPY VARCHAR2);
55
56 PROCEDURE Find_Duplicate_Category_Id(
57 p_structure_id IN NUMBER,
58 p_category_name IN VARCHAR2,
59 x_category_id OUT NOCOPY NUMBER
60 );
61 PROCEDURE Create_SME_Category_Set(x_category_set_id OUT NOCOPY NUMBER);
62 PROCEDURE Attach_SME_Set_To_Func_Area(p_category_set_id IN NUMBER);
63 PROCEDURE Make_SME_Set_Hierarchical(p_category_set_id IN NUMBER);
64 PROCEDURE Retrieve_Category_Set(x_category_set_id OUT NOCOPY NUMBER,
65 x_warning_flag OUT NOCOPY VARCHAR2);
66 PROCEDURE Pre_Process_Categories(p_name_count_tab IN OUT NOCOPY Name_Count_Tab,
67 p_category_name IN VARCHAR2,
68 p_create_legacy IN VARCHAR2,
69 x_category_name OUT NOCOPY VARCHAR2,
70 x_legacy_category_name OUT NOCOPY VARCHAR2,
71 x_warning_flag OUT NOCOPY VARCHAR2);
72
73 PROCEDURE Trunc_Name(p_name IN VARCHAR2,
74 p_trunc_length IN NUMBER,
75 x_trunc_name OUT NOCOPY VARCHAR2);
76
77 PROCEDURE Grant_Access_To_Catalog(p_category_set_id IN NUMBER,
78 x_warning_flag OUT NOCOPY VARCHAR2);
79
80 PROCEDURE Assign_Item_To_Category(p_category_set_id IN NUMBER,
81 p_organization_id IN NUMBER,
82 p_inventory_item_id IN NUMBER,
83 p_category_id IN NUMBER,
84 p_control_level IN NUMBER,
85 p_mult_item_cat_assign_flag IN VARCHAR2,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_errorcode OUT NOCOPY NUMBER,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2);
90
91 PROCEDURE Cleanup_Legacy_Categories(p_category_set_id IN NUMBER,
92 p_category_id IN NUMBER);
93 --*****************************************************************************
94 -- Public API
95 --
96
97 /*
98 This procedure creates new categories corresponding to interest types/codes
99 if required and then map these categories to interest types/codes. It will also
100 associate items to these newly created categories based on the old association
101 between items and interest types/codes
102 This will be called by concurrent program 'Product Catalog Mapping'
103 */
104 PROCEDURE Migrate_Categories (
105 ERRBUF OUT NOCOPY VARCHAR2,
106 RETCODE OUT NOCOPY VARCHAR2,
107 p_Debug_Flag IN VARCHAR2 Default 'N'
108 ) IS
109
110 -- Variables
111 l_Debug_Flag VARCHAR2(12); l_count NUMBER := 0;
112 l_old_category_id NUMBER := 0;
113 l_msg_count NUMBER := 0;
114 l_msg_data VARCHAR2(2000);
115 l_return_status VARCHAR2(1); -- Local return status equal to p_return_status
116 l_structure_id NUMBER;
117 l_old_structure_id NUMBER;
118 l_category_set_id NUMBER;
119 l_int_type_cat_id NUMBER;
120 l_pri_int_code_cat_id NUMBER;
121 l_sec_int_code_cat_id NUMBER;
122 l_control_level NUMBER;
123 l_status BOOLEAN;
127 l_warning_flag VARCHAR2(1) := 'N';
124 l_attr_group_id NUMBER;
125 l_val NUMBER;
126 l_warning VARCHAR2(1) := 'N';
128 l_category_name VARCHAR2(120);
129 l_name_count_tab Name_Count_Tab;
130 l_mult_item_cat_assign_flag VARCHAR2(1);
131
132 -- Cursors
133 CURSOR C_Get_Int_Type IS
134 select B.interest_type_id, TL.interest_type, TL.description, B.Expected_Purchase_Flag,B.enabled_flag
135 from as_interest_types_b B, as_interest_types_tl TL
136 where B.interest_type_id = TL.interest_type_id
137 and TL.language = userenv('LANG');
138
139
140 CURSOR C_Get_Pri_Int_Code(c_interest_type_id Number) IS
141 select B.interest_code_id, TL.code, TL.description,B.enabled_flag
142 from as_interest_codes_b B, as_interest_codes_tl TL
143 where B.interest_code_id = TL.interest_code_id
144 and TL.language = userenv('LANG')
145 and B.interest_type_id = c_interest_type_id
146 and B.parent_interest_code_id is null;
147
148 CURSOR C_Get_Sec_Int_Code(c_interest_code_id Number) IS
149 select B.interest_code_id, TL.code, TL.description,B.enabled_flag
150 from as_interest_codes_b B, as_interest_codes_tl TL
151 where B.interest_code_id = TL.interest_code_id
152 and TL.language = userenv('LANG')
153 and B.parent_interest_code_id = c_interest_code_id;
154
155 BEGIN
156 fnd_profile.put('AFLOG_ENABLED', 'Y');
157 fnd_profile.put('AFLOG_LEVEL', '1');
158 if (upper(p_Debug_Flag) = 'Y') then
159 G_DEBUG := true;
160 end if;
161
162 FND_FILE.PUT_LINE(FND_FILE.LOG,'Category mapping started');
163 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
164
165 -- Initialize retcode to success
166 RETCODE := FND_API.G_RET_STS_SUCCESS;
167
168 -- Retrieve the category set id for SME Product Catalog
169 Retrieve_Category_Set(x_category_set_id => l_category_set_id,
170 x_warning_flag => l_warning_flag);
171
172 if (l_warning_flag = 'Y') then
173 l_warning := 'Y';
174 end if;
175
176 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
177 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Parameters are as below:');
178 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','(a) New Category Set Id = ' || l_category_set_id);
179 end if;
180
181 BEGIN
182 -- Get structure id corresponding to Product Catalog
183 select C.structure_id,C.control_level,C.mult_item_cat_assign_flag
184 into l_structure_id, l_control_level, l_mult_item_cat_assign_flag
185 from MTL_CATEGORY_SETS C where C.category_set_id = l_category_set_id;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unable to find structure id corresponding to Product Catalog');
190 RAISE;
191 END;
192
193 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
194 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','(b) New Control Level = ' || l_control_level);
195 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','(c) New Structure Id = ' || l_structure_id);
196 end if;
197
198 BEGIN
199 -- Get structure id corresponding to sales and marketing category set
200 select FIFS.ID_FLEX_NUM into l_old_structure_id
201 from FND_ID_FLEX_STRUCTURES FIFS
202 where FIFS.ID_FLEX_CODE = 'MCAT' AND FIFS.APPLICATION_ID = 401 AND FIFS.ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES';
203 EXCEPTION
204 WHEN OTHERS THEN
205 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unable to find structure id corresponding to sales and marketing category set');
206 RAISE;
207 END;
208
209 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
210 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','(d) Old Structure Id = ' || l_old_structure_id);
211 end if;
212
213 BEGIN
214 -- EGO Application Id is 431
215 SELECT ATTR_GROUP_ID INTO l_attr_group_id FROM EGO_FND_DSC_FLX_CTX_EXT WHERE APPLICATION_ID = 431 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET' AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';
216 EXCEPTION
217 WHEN OTHERS THEN
218 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unable to find attribute group for Sales and Marketing');
219 RAISE;
220 END;
221
222 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
223 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','(e) Attribute Group Id = ' || l_attr_group_id);
224 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','(f) Multiple Item Category Assignment Flag = ' || l_mult_item_cat_assign_flag);
225 end if;
226
227 BEGIN
228 -- For each interest type
229 FOR scr in C_Get_Int_Type
230 LOOP
231 FND_MESSAGE.Set_Name('AS', 'API_PROCESSING_INTEREST_TYPE');
232 FND_MESSAGE.Set_Token('NAME', scr.interest_type);
233 FND_FILE.PUT_LINE(FND_FILE.LOG,'->' || FND_MESSAGE.Get());
234
235 -- Create Mtl categories and associate to inventory items
236 Process_Categories(p_int_typ_cod_id => scr.interest_type_id,
237 p_structure_id => l_structure_id,
238 p_old_structure_id => l_old_structure_id,
239 p_category_set_id => l_category_set_id,
240 p_control_level => l_control_level,
244 p_description => scr.description,
241 p_mult_item_cat_assign_flag => l_mult_item_cat_assign_flag,
242 p_parent_category_id => null,
243 p_category_name => scr.interest_type,
245 p_interest_level => 0,
246 p_expected_purchase => scr.expected_purchase_flag,
247 p_level0_enabled_flag => scr.enabled_flag,
248 p_level1_enabled_flag => scr.enabled_flag,
249 p_level2_enabled_flag => scr.enabled_flag,
250 p_attr_group_id => l_attr_group_id,
251 p_name_count_tab => l_name_count_tab,
252 x_return_status => l_return_status,
253 x_msg_count => l_msg_count,
254 x_msg_data => l_msg_data,
255 x_category_id => l_int_type_cat_id,
256 x_warning_flag => l_warning_flag);
257
258 if (l_warning_flag = 'Y') then
259 l_warning := 'Y';
260 GOTO end_loop1;
261 end if;
262
263 -- Update the mapping between interest type and product category
264 Update AS_INTEREST_TYPES_B set product_category_id = l_int_type_cat_id, product_cat_set_id = l_category_set_id where interest_type_id = scr.interest_type_id;
265
266 -- For each primary interest code corresponding to selected interest type
267 FOR scr2 in C_Get_Pri_Int_Code(scr.interest_type_id)
268 LOOP
269 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
270 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Now processing primary interest code '||scr2.code);
271 end if;
272
273 -- Create Mtl categories and associate to inventory items
274 Process_Categories(p_int_typ_cod_id => scr2.interest_code_id,
275 p_structure_id => l_structure_id,
276 p_old_structure_id => l_old_structure_id,
277 p_category_set_id => l_category_set_id,
278 p_control_level => l_control_level,
279 p_mult_item_cat_assign_flag => l_mult_item_cat_assign_flag,
280 p_parent_category_id => l_int_type_cat_id,
281 p_category_name => scr2.code,
282 p_description => scr2.description,
283 p_interest_level => 1,
284 p_expected_purchase => scr.expected_purchase_flag,
285 p_level0_enabled_flag => scr.enabled_flag,
286 p_level1_enabled_flag => scr2.enabled_flag,
287 p_level2_enabled_flag => scr2.enabled_flag,
288 p_attr_group_id => l_attr_group_id,
289 p_name_count_tab => l_name_count_tab,
290 x_return_status => l_return_status,
291 x_msg_count => l_msg_count,
292 x_msg_data => l_msg_data,
293 x_category_id => l_pri_int_code_cat_id,
294 x_warning_flag => l_warning_flag);
295
296 if (l_warning_flag = 'Y') then
297 l_warning := 'Y';
298 GOTO end_loop2;
299 end if;
300
301 -- Update the mapping between primary interest code and product category
302 Update AS_INTEREST_CODES_B set product_category_id = l_pri_int_code_cat_id, product_cat_set_id = l_category_set_id where interest_code_id = scr2.interest_code_id;
303
304 -- For each secondary interest code corresponding to selected primary interest code
305 FOR scr3 in C_Get_Sec_Int_Code(scr2.interest_code_id)
306 LOOP
307 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
308 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Now processing secondary interest code '||scr3.code);
309 end if;
310
311 -- Create Mtl categories and associate to inventory items
312 Process_Categories(p_int_typ_cod_id => scr3.interest_code_id,
313 p_structure_id => l_structure_id,
314 p_old_structure_id => l_old_structure_id,
315 p_category_set_id => l_category_set_id,
316 p_control_level => l_control_level,
317 p_mult_item_cat_assign_flag => l_mult_item_cat_assign_flag,
318 p_parent_category_id => l_pri_int_code_cat_id,
319 p_category_name => scr3.code,
320 p_description => scr3.description,
321 p_interest_level => 2,
322 p_expected_purchase => scr.expected_purchase_flag,
323 p_level0_enabled_flag => scr.enabled_flag,
324 p_level1_enabled_flag => scr2.enabled_flag,
325 p_level2_enabled_flag => scr3.enabled_flag,
326 p_attr_group_id => l_attr_group_id,
327 p_name_count_tab => l_name_count_tab,
328 x_return_status => l_return_status,
329 x_msg_count => l_msg_count,
333
330 x_msg_data => l_msg_data,
331 x_category_id => l_sec_int_code_cat_id,
332 x_warning_flag => l_warning_flag);
334 if (l_warning_flag = 'Y') then
335 l_warning := 'Y';
336 GOTO end_loop3;
337 end if;
338
339 -- Update the mapping between secondary interest code and product category
340 Update AS_INTEREST_CODES_B set product_category_id = l_sec_int_code_cat_id, product_cat_set_id = l_category_set_id where interest_code_id = scr3.interest_code_id;
341 <<end_loop3>>
342 NULL;
343 END LOOP;
344 <<end_loop2>>
345 NULL;
346 END LOOP;
347 <<end_loop1>>
348 NULL;
349 END LOOP;
350 END;
351
352 FND_FILE.PUT_LINE(FND_FILE.LOG,'Category mapping finished successfully');
353 FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
354
355 COMMIT;
356
357 if (l_warning = 'Y') then
358 l_status := fnd_concurrent.set_completion_status('WARNING',FND_MESSAGE.Get_String('AS','API_REQUEST_WARNING_STATUS'));
359 end if;
360 EXCEPTION
361 WHEN OTHERS THEN
362 Rollback;
363
364 RETCODE := FND_API.G_RET_STS_ERROR;
365
366 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
367 IF l_status = TRUE THEN
368 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, can not complete Concurrent Program') ;
369 END IF ;
370
371 ERRBUF := 'Error in category mapping '||SQLERRM;
372 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
373
374 fnd_msg_pub.count_and_get( p_encoded => 'F'
375 , p_count => l_msg_count
376 , p_data => l_msg_data);
377 FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status:' || nvl(l_return_status,'xxx'));
378 --=================message display part begins
379 for k in 1 .. l_msg_count loop
380 l_msg_data := fnd_msg_pub.get( p_msg_index => k,
381 p_encoded => 'F'
382 );
383 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error msg: '||substr(l_msg_data,1,240));
384 ERRBUF := substr(ERRBUF || ';Error msg: '|| l_msg_data,1,3900);
385 end loop;
386 --=================message display part ends
387
388 End Migrate_Categories;
389
390 --*****************************************************************************
391 -- Private API
392 --
393
394 PROCEDURE Pre_Process_Categories(p_name_count_tab IN OUT NOCOPY Name_Count_Tab,
395 p_category_name IN VARCHAR2,
396 p_create_legacy IN VARCHAR2,
397 x_category_name OUT NOCOPY VARCHAR2,
398 x_legacy_category_name OUT NOCOPY VARCHAR2,
399 x_warning_flag OUT NOCOPY VARCHAR2) IS
400 l_name_hash_val NUMBER;
401 l_category_name VARCHAR2(35);
402 l_legacy_category_name VARCHAR2(28);
403 BEGIN
404
405 -- Since there is limitation of 40 characters on size of category name (due to length of segment1),
406 -- truncate the category name to 35 characters. Leave buffer of 5 characters to store the duplicate
407 -- count if any .e,g, After truncation, you might have a category name as 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
408 -- and duplicate category with name as 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx(1)'. Thus leaving a buffer
409 -- of 5 characters will allow 999 duplicates!
410 -- Since number of bytes per character differ in different languages, the substr to 35 characters
411 -- might return more than 35 bytes in languages other than english. There are two possible solutions
412 -- to this problem:
413 -- Solution1:
414 -- Calculate the number of bytes/character and then calculate the actual number of characters to substring.
415 -- To achieve this, we will first calculate the ratio bytes/char by dividing lengthb by length.
416 -- Using this ratio, we will then calculate number of characters equivalent to 35 bytes and then
417 -- do a substr based on this number(after truncating the decimal portion). The problem with this
418 -- solution is that this wouldn't work for european languages since europen languages mix english
419 -- and non-english characters. So one character might be 1 byte, whereas other might be 2 bytes. So
420 -- it is not possible to get a correct bytes/char ratio. Solution1 can be implemented as:
421 -- l_category_name := substr(p_category_name, 1, trunc((35/lengthb(p_category_name)) * length(p_category_name)));
422 -- Solution2:
423 -- We will do a substr to 35 characters and store it in varchar2(35). If it throws an exception,
424 -- we will substring to 35/2 characters. If it still fails we try with 35/3 characters. If it still
425 -- fails, we give a warning to the user to truncate manually. This solution will work fine for most
426 -- languages. Hence we will use this solution here.
427
428 BEGIN
429 Trunc_Name(p_name => p_category_name,
430 p_trunc_length => 35,
431 x_trunc_name => l_category_name);
432 EXCEPTION
433 WHEN OTHERS THEN
434 if (SQLCODE = '-6502') then
435 -- Warning! Unable to create category for interest type/code as category name is too long.
436 FND_MESSAGE.Set_Name('AS', 'API_WARNING_CREATE_CATEGORY');
437 FND_MESSAGE.Set_Token('NAME', p_category_name);
438 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
439
440 x_warning_flag := 'Y';
441 return;
442 end if;
446 -- Append the count if duplicate found
443 RAISE;
444 END;
445
447 BEGIN
448 -- Get the hash value of the category name
449 l_name_hash_val := DBMS_UTILITY.GET_HASH_VALUE(l_category_name,1,10000000);
450 p_name_count_tab(l_name_hash_val) := p_name_count_tab(l_name_hash_val) + 1;
451 x_category_name := l_category_name || '(' || p_name_count_tab(l_name_hash_val) || ')';
452 EXCEPTION
453 -- NO_DATA_FOUND Exception implies that the name occurs for the very first time in array
454 When NO_DATA_FOUND Then
455 p_name_count_tab(l_name_hash_val) := 1;
456 x_category_name := l_category_name;
457 END;
458
459 -- Now pre-process legacy categories
460 IF (p_create_legacy = 'Y') THEN
461 BEGIN
462 Trunc_Name(p_name => p_category_name,
463 p_trunc_length => 28,
464 x_trunc_name => l_legacy_category_name);
465 EXCEPTION
466 WHEN OTHERS THEN
467 if (SQLCODE = '-6502') then
468 -- Warning! Unable to create legacy category for interest type/code as legacy category name is too long.
469 FND_MESSAGE.Set_Name('AS', 'API_WARNING_CREATE_LEGACY');
470 FND_MESSAGE.Set_Token('NAME', p_category_name);
471 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
472
473 x_warning_flag := 'Y';
474 return;
475 end if;
476 RAISE;
477 END;
478
479 -- Append the count if duplicate found
480 BEGIN
481 -- Get the hash value of the category name
482 l_name_hash_val := DBMS_UTILITY.GET_HASH_VALUE(l_legacy_category_name,1,10000000);
483 p_name_count_tab(l_name_hash_val) := p_name_count_tab(l_name_hash_val) + 1;
484 x_legacy_category_name := l_legacy_category_name || '(' || p_name_count_tab(l_name_hash_val) || ')' || ' LEGACY';
485 EXCEPTION
486 -- NO_DATA_FOUND Exception implies that the name occurs for the very first time in array
487 When NO_DATA_FOUND Then
488 p_name_count_tab(l_name_hash_val) := 1;
489 x_legacy_category_name := l_legacy_category_name || ' LEGACY';
490 END;
491 END IF;
492
493
494 END Pre_Process_Categories;
495
496 PROCEDURE Trunc_Name(p_name IN VARCHAR2,
497 p_trunc_length IN NUMBER,
498 x_trunc_name OUT NOCOPY VARCHAR2) IS
499 BEGIN
500 -- First Try
501 x_trunc_name := substr(p_name, 1, p_trunc_length);
502 EXCEPTION
503 WHEN OTHERS THEN
504 if (SQLCODE = '-6502') then
505 BEGIN
506 -- Second Try
507 x_trunc_name := substr(p_name, 1, trunc(p_trunc_length/2));
508 EXCEPTION
509 WHEN OTHERS THEN
510 if (SQLCODE = '-6502') then
511 BEGIN
512 -- Third and Final Try
513 x_trunc_name := substr(p_name, 1, trunc(p_trunc_length/3));
514 EXCEPTION
515 WHEN OTHERS THEN
516 RAISE;
517 END;
518 else
519 RAISE;
520 end if;
521 END;
522 else
523 RAISE;
524 end if;
525 END;
526
527
528
529 PROCEDURE Retrieve_Category_Set(x_category_set_id OUT NOCOPY NUMBER,
533 from MTL_CATEGORY_SETS
530 x_warning_flag OUT NOCOPY VARCHAR2) IS
531 CURSOR C_Get_Cat_Set_Id IS
532 select category_set_id, hierarchy_enabled
534 where category_set_name = G_SME_CATEGORY_SET_NAME;
535
536 CURSOR C_Check_Functional_Area(c_category_set_id NUMBER) IS
537 select 1
538 from MTL_CATEGORY_SETS S, MTL_DEFAULT_CATEGORY_SETS D
539 where S.category_set_id = D.category_set_id
540 and D.functional_area_id = G_FUNCTIONAL_AREA
541 and D.category_set_id = c_category_set_id;
542
543 l_category_set_id NUMBER;
544 l_hierarchy_enabled VARCHAR2(1);
545 l_val NUMBER;
546 BEGIN
547 OPEN C_Get_Cat_Set_Id;
548 FETCH C_Get_Cat_Set_Id INTO l_category_set_id, l_hierarchy_enabled;
549 IF C_Get_Cat_Set_Id%NOTFOUND THEN
553 Attach_SME_Set_To_Func_Area(l_category_set_id);
550 Close C_Get_Cat_Set_Id;
551 Create_SME_Category_Set(x_category_set_id => l_category_set_id);
552 Grant_Access_To_Catalog(l_category_set_id,x_warning_flag);
554 ELSE
555 Close C_Get_Cat_Set_Id;
556
557 if (upper(l_hierarchy_enabled) <> 'Y') then
558 Make_SME_Set_Hierarchical(l_category_set_id);
559 end if;
560
561 Grant_Access_To_Catalog(l_category_set_id,x_warning_flag);
562
563 Open C_Check_Functional_Area(l_category_set_id);
564 Fetch C_Check_Functional_Area into l_val;
565 if C_Check_Functional_Area%NOTFOUND then
566 Close C_Check_Functional_Area;
567 Attach_SME_Set_To_Func_Area(l_category_set_id);
568 else
569 Close C_Check_Functional_Area;
570 end if;
571 END IF;
572 x_category_set_id := l_category_set_id;
573 EXCEPTION
574 WHEN OTHERS THEN
575 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occured while retrieving category set');
576 RAISE;
577 END Retrieve_Category_Set;
578
579 PROCEDURE Create_SME_Category_Set(x_category_set_id OUT NOCOPY NUMBER) IS
580 l_structure_id NUMBER;
581 l_control_level NUMBER;
582 l_row_id VARCHAR2(100);
583 l_next_val NUMBER;
584 BEGIN
585 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
586 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Creating Category Set');
587 end if;
588
589 -- Get structure id and control level associated with functional area 11
590 select C.structure_id, C.control_level into l_structure_id, l_control_level
591 from MTL_DEFAULT_CATEGORY_SETS D, MTL_CATEGORY_SETS C
592 where D.functional_area_id = G_FUNCTIONAL_AREA and D.category_set_id = C.category_set_id;
593
594 select MTL_CATEGORY_SETS_S.NEXTVAL into l_next_val from dual;
595
596 MTL_CATEGORY_SETS_PKG.INSERT_ROW (
597 X_ROWID => l_row_id,
598 X_CATEGORY_SET_ID => l_next_val,
599 X_CATEGORY_SET_NAME => G_SME_CATEGORY_SET_NAME,
600 X_DESCRIPTION => G_SME_CATEGORY_SET_NAME,
601 X_STRUCTURE_ID => l_structure_id,
602 X_VALIDATE_FLAG => 'Y',
603 X_MULT_ITEM_CAT_ASSIGN_FLAG => 'N',
604 X_CONTROL_LEVEL_UPDT_FLAG => 'N',
605 X_MULT_ITEM_CAT_UPDT_FLAG => 'N',
606 X_VALIDATE_FLAG_UPDT_FLAG => 'N',
607 X_HIERARCHY_ENABLED => 'Y',
608 X_CONTROL_LEVEL => l_control_level,
609 X_DEFAULT_CATEGORY_ID => null,
610 X_LAST_UPDATE_DATE => SYSDATE,
611 X_LAST_UPDATED_BY => 0,
612 X_CREATION_DATE => SYSDATE,
613 X_CREATED_BY => 0,
614 X_LAST_UPDATE_LOGIN => 0 );
615
616 x_category_set_id := l_next_val;
617 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
618 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Finished Creating Category Set');
619 end if;
620 END Create_SME_Category_Set;
621
622 -- Grant catalog manager role for all users to SME Product Catalog
623 -- For more details refer script EGOCSGRA.sql
624 PROCEDURE Grant_Access_To_Catalog(p_category_set_id IN NUMBER,
625 x_warning_flag OUT NOCOPY VARCHAR2) IS
626 l_catalog_manager_resp VARCHAR2(2000) := 'EGO_CATEGORY_SET_MANAGER';
627 l_category_set_object VARCHAR2(2000) := 'EGO_CATEGORY_SET';
628 l_instance_type VARCHAR2(2000) := 'INSTANCE';
629 l_all_users_grantee_type VARCHAR2(2000) := 'GLOBAL';
630 l_num_cat_set_grants NUMBER;
631
632 x_grant_guid RAW(16);
633 x_return_status VARCHAR2(1);
634 x_errorcode VARCHAR2(1);
635
636 BEGIN
637
638 SELECT count(*) into l_num_cat_set_grants
639 FROM fnd_grants fg, fnd_objects fo
640 WHERE fg.object_id = fo.object_id
641 and fo.obj_name = l_category_set_object
642 and fg.instance_pk1_value=p_category_set_id;
643
644
645 IF l_num_cat_set_grants = 0 THEN
646
647 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
648 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Granting Access to Catalog');
649 end if;
650
651 fnd_grants_pkg.grant_function(
652 p_api_version => 1.0,
653 p_menu_name => l_catalog_manager_resp ,
654 p_object_name => l_category_set_object,
655 p_instance_type => l_instance_type,
656 p_instance_set_id => null,
657 p_instance_pk1_value => p_category_set_id,
658 p_instance_pk2_value => null,
659 p_instance_pk3_value => null,
660 p_instance_pk4_value => null,
661 p_instance_pk5_value => null,
662 p_grantee_type => l_all_users_grantee_type,
663 p_grantee_key => null,
664 p_start_date => sysdate,
665 p_end_date => null,
666 p_program_name => null,
667 p_program_tag => null,
668 x_grant_guid => x_grant_guid,
669 x_success => x_return_status,
670 x_errorcode => x_errorcode
671 );
672
673 if (x_return_status = FND_API.G_FALSE) then
674 x_warning_flag := 'Y';
675
679 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
676 -- Warning! Unable to grant access to catalog (Error code:' || x_errorcode || '). Please notify the administrator about this error.
677 FND_MESSAGE.Set_Name('AS', 'API_WARNING_GRANT_CATALOG');
678 FND_MESSAGE.Set_Token('ERRORCODE', x_errorcode);
680 end if;
681
682 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
683 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Grant guid=' || x_grant_guid);
684 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Done Granting Access to Catalog');
685 end if;
686 END IF;
687 END Grant_Access_To_Catalog;
688
689 PROCEDURE Attach_SME_Set_To_Func_Area(p_category_set_id IN NUMBER) IS
690 BEGIN
691 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
692 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Attaching Category Set to functional area 11');
693 end if;
694
695 Update MTL_DEFAULT_CATEGORY_SETS
696 set category_set_id = p_category_set_id
697 where functional_area_id = G_FUNCTIONAL_AREA;
698
699 -- If nothing updated then this is an error
700 IF (SQL%NOTFOUND) THEN
701 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No functional area found corresponding to product catalog');
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
705 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Done Attaching Category Set to functional area 11');
706 end if;
707 END Attach_SME_Set_To_Func_Area;
708
709 PROCEDURE Make_SME_Set_Hierarchical(p_category_set_id IN NUMBER) IS
710 BEGIN
711 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
712 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Making SME set hierarchical');
713 end if;
714
715 Update Mtl_Category_Sets
716 set hierarchy_enabled = 'Y'
717 where category_set_id = p_category_set_id;
718 END Make_SME_Set_Hierarchical;
719
720 /*
721 This procedure creates new MTL categories corresponding to the interest type/code passed in.
722 It creates/updates records in following MTL tables:
723 a) MTL_CATEGORIES_B
724 b) MTL_CATEGORIES_TL
725 c) MTL_CATEGORY_SET_VALID_CATS
726 d) MTL_ITEM_CATEGORIES
727 */
728 PROCEDURE Process_Categories(p_int_typ_cod_id IN NUMBER,
729 p_structure_id IN NUMBER,
730 p_old_structure_id IN NUMBER,
731 p_category_set_id IN NUMBER,
732 p_control_level IN NUMBER,
733 p_mult_item_cat_assign_flag IN VARCHAR2,
734 p_parent_category_id IN NUMBER,
735 p_category_name IN VARCHAR2,
736 p_description IN VARCHAR2,
737 p_interest_level IN NUMBER,
741 p_level2_enabled_flag IN VARCHAR2,
738 p_expected_purchase IN VARCHAR2,
739 p_level0_enabled_flag IN VARCHAR2,
740 p_level1_enabled_flag IN VARCHAR2,
742 p_attr_group_id IN NUMBER,
743 p_name_count_tab IN OUT NOCOPY Name_Count_Tab,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_count OUT NOCOPY NUMBER,
746 x_msg_data OUT NOCOPY VARCHAR2,
747 x_category_id OUT NOCOPY NUMBER,
748 x_warning_flag OUT NOCOPY VARCHAR2) IS
749
750 CURSOR C_Get_Items(c_structure_id Number, c_type_code_id Number, c_interest_level Number) IS
751 select MIC.INVENTORY_ITEM_ID,
752 MIC.ORGANIZATION_ID
753 from
754 ( SELECT CATEGORY_ID
755 FROM MTL_CATEGORIES_B MC
756 WHERE MC.STRUCTURE_ID = c_structure_id
757 and DECODE(c_interest_level,0,MC.SEGMENT1,1,MC.SEGMENT2,2,MC.SEGMENT3,NULL) = c_type_code_id
758 and DECODE(c_interest_level,0,MC.SEGMENT2,1,MC.SEGMENT3,NULL) IS NULL
759 and DECODE(c_interest_level,0,MC.SEGMENT3,NULL) IS NULL
760 ) MC1,
761 MTL_ITEM_CATEGORIES MIC
762 where MIC.CATEGORY_ID = MC1.CATEGORY_ID;
763
764 CURSOR C_Valid_Cat_Exists(c_category_set_id Number, c_category_id Number) IS
765 select 1
766 from MTL_CATEGORY_SET_VALID_CATS
767 where
768 category_id = c_category_id
769 and category_set_id = c_category_set_id;
770
771 CURSOR C_EXISTS_EXTN_ID(c_category_set_id Number, c_category_id Number) IS
772 select 1
773 from EGO_PRODUCT_CAT_SET_EXT
774 where category_set_id = c_category_set_id
775 and category_id = c_category_id;
776
777
778 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
779 l_out_category_id NUMBER := 0;
780 l_out_legacy_category_id NUMBER := 0;
781 l_val1 NUMBER;
782 l_val2 NUMBER;
783 l_val NUMBER;
784 l_msg_count NUMBER := 0;
785 l_msg_data VARCHAR2(2000);
786 l_return_status VARCHAR2(1); -- Local return status equal to p_return_status
787 l_return_status2 VARCHAR2(1);
788 l_error_code NUMBER;
789 l_dup_category_id NUMBER;
790 l_dup_legacy_category_id NUMBER;
791 l_skip_valid_cat VARCHAR2(1);
792 l_category_name VARCHAR2(40);
793 l_legacy_category_name VARCHAR2(40);
794 l_category_desc VARCHAR2(240);
795 l_legacy_category_desc VARCHAR2(240);
796 l_create_legacy VARCHAR2(1);
797 l_item_found_flag VARCHAR2(1) := 'N';
798 l_exclude_user_view VARCHAR2(1) := 'N';
799
800 BEGIN
801 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
802 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Inside process categories for interest type/code:' || p_category_name || ',interest type/code id:' || p_int_typ_cod_id || ',parent category id:' || p_parent_category_id);
803 end if;
804
805 x_warning_flag := 'N';
806 l_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 IF p_level0_enabled_flag <> 'Y' OR p_level1_enabled_flag <> 'Y' OR p_level2_enabled_flag <> 'Y' then
809 l_exclude_user_view := 'Y';
810 END IF;
811
812 -- Check if any items exist for the current interest type id/interest code id
813 Open C_Get_Items(p_old_structure_id, p_int_typ_cod_id, p_interest_level);
814 FETCH C_Get_Items into l_val1, l_val2;
815 IF C_Get_Items%FOUND then
816 l_item_found_flag := 'Y';
817 END IF;
818 close C_Get_Items;
819
820 IF (l_item_found_flag = 'Y' and p_interest_level <> 2) THEN
821 l_create_legacy := 'Y';
822 END IF;
823
824 Pre_Process_Categories(p_name_count_tab => p_name_count_tab,
825 p_category_name => p_category_name,
826 p_create_legacy => l_create_legacy,
827 x_category_name => l_category_name,
828 x_legacy_category_name => l_legacy_category_name,
829 x_warning_flag => x_warning_flag);
830
831 IF (x_warning_flag = 'Y') THEN
832 return;
833 END IF;
834
835 -- Initialize API return status to success
836 x_return_status := FND_API.G_RET_STS_SUCCESS;
837 l_return_status := FND_API.G_RET_STS_SUCCESS;
838
839 -- Create Records in MTL_CATEGORIES_B and MTL_CATEGORIES_TL
840 l_category_rec.segment1 := l_category_name;
841 l_category_rec.structure_id := p_structure_id;
842 l_category_rec.description := p_category_name; -- This is the original non-truncated name
843
844 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
845 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Creating category with name:' || l_category_rec.segment1 || ' and description:' || l_category_rec.description);
846 end if;
847
848 INV_ITEM_CATEGORY_PUB.Create_Category (
849 p_api_version => G_CREATE_CAT_API_VER,
850 p_init_msg_list => FND_API.G_FALSE,
851 p_commit => FND_API.G_FALSE,
852 x_return_status => l_return_status,
853 x_errorcode => l_error_code,
854 x_msg_count => l_msg_count,
855 x_msg_data => l_msg_data,
856 p_category_rec => l_category_rec,
860 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
857 x_category_id => l_out_category_id
858 );
859
861 l_return_status2 := FND_API.G_RET_STS_SUCCESS;
862 -- check if error is due to duplicate category
863 Check_Duplicate_Category(x_return_status => l_return_status2);
864 IF (l_return_status2 <> FND_API.G_RET_STS_SUCCESS) THEN
865 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
866 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Error occured while calling INV_ITEM_CATEGORY_PUB.Create_Category');
867 end if;
868 RAISE FND_API.G_EXC_ERROR;
869 ELSE -- if duplicate found
870 Find_Duplicate_Category_Id(
871 p_structure_id => p_structure_id,
872 p_category_name => l_category_name,
873 x_category_id => l_dup_category_id
874 );
875 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
876 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Duplicate category found with id:' || l_dup_category_id);
877 end if;
878 -- Assign duplicate category id to category id (that otherwise should have been created)
879 l_out_category_id := l_dup_category_id;
880 END IF;
881 END IF;
882
883 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb', 'Category Id:' || l_out_category_id);
885 END IF;
886
887 l_skip_valid_cat := 'N';
888 -- If a duplicate category was found, there is a chance that a record for this category
889 -- already exists in MTL_CATEGORY_SET_VALID_CATS. If such a record exists then skip
890 -- creating a new record in MTL_CATEGORY_SET_VALID_CATS table
891 IF (l_dup_category_id is not null) THEN
892 OPEN C_Valid_Cat_Exists(p_category_set_id, l_dup_category_id);
893 FETCH C_Valid_Cat_Exists INTO l_val;
894 IF (C_Valid_Cat_Exists%FOUND) THEN
895 l_skip_valid_cat := 'Y';
896 END IF;
897
898 CLOSE C_Valid_Cat_Exists;
899 END IF;
900
901 IF (l_skip_valid_cat = 'N') THEN
902 -- Create Record in MTL_CATEGORY_SET_VALID_CATS
903 INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
904 p_api_version => G_CREATE_CAT_API_VER,
905 p_init_msg_list => FND_API.G_FALSE,
906 p_commit => FND_API.G_FALSE,
907 p_category_set_id => p_category_set_id,
908 p_category_id => l_out_category_id,
909 p_parent_category_id => p_parent_category_id,
910 x_return_status => l_return_status,
911 x_errorcode => l_error_code,
912 x_msg_count => l_msg_count,
913 x_msg_data => l_msg_data
914 );
915
916 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
917 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
918 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Error occured while calling INV_ITEM_CATEGORY_PUB.Create_Valid_Category');
919 end if;
920
921 RAISE FND_API.G_EXC_ERROR;
922 END IF;
923 END IF;
924
925 Open C_EXISTS_EXTN_ID(p_category_set_id,l_out_category_id);
926 FETCH C_EXISTS_EXTN_ID into l_val;
927 IF C_EXISTS_EXTN_ID%NOTFOUND
928 THEN
929 -- do the insert here
930 INSERT INTO EGO_PRODUCT_CAT_SET_EXT ( EXTENSION_ID, CATEGORY_SET_ID, CATEGORY_ID, ATTR_GROUP_ID,
931 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
932 INCLUDE_IN_FORECAST, EXPECTED_PURCHASE, EXCLUDE_USER_VIEW )
933 VALUES (EGO_EXTFWK_S.NEXTVAL, p_category_set_id, l_out_category_id, p_attr_group_id, -1, sysdate, -1, sysdate, -1, 'Y', p_expected_purchase, l_exclude_user_view);
934 ELSE
935 UPDATE EGO_PRODUCT_CAT_SET_EXT
936 SET EXPECTED_PURCHASE = p_expected_purchase,
937 EXCLUDE_USER_VIEW = l_exclude_user_view
938 WHERE CATEGORY_SET_ID = p_category_set_id
939 AND CATEGORY_ID = l_out_category_id;
940 END IF;
941 Close C_EXISTS_EXTN_ID;
942
943 IF (l_item_found_flag = 'Y')
944 THEN
945 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
946 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Items found for category:' || p_category_name || ',interest type/code:' || p_int_typ_cod_id );
947 end if;
948
949 -- For secondary interest codes don't create legacy categories
950 IF l_create_legacy = 'Y' THEN
951 BEGIN
952
953
954 -- Create Legacy Records in MTL_CATEGORIES_B and MTL_CATEGORIES_TL
955 l_category_rec.segment1 := l_legacy_category_name;
956 l_category_rec.structure_id := p_structure_id;
957 l_category_rec.description := p_category_name || ' LEGACY';
958
959 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
960 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Creating legacy category with name:' || l_category_rec.segment1 || ' and description:' || l_category_rec.description);
961 end if;
962
963 INV_ITEM_CATEGORY_PUB.Create_Category (
964 p_api_version => G_CREATE_CAT_API_VER,
965 p_init_msg_list => FND_API.G_FALSE,
966 p_commit => FND_API.G_FALSE,
970 x_msg_data => l_msg_data,
967 x_return_status => l_return_status,
968 x_errorcode => l_error_code,
969 x_msg_count => l_msg_count,
971 p_category_rec => l_category_rec,
972 x_category_id => l_out_legacy_category_id
973 );
974
975 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
976 l_return_status2 := FND_API.G_RET_STS_SUCCESS;
977 -- check if error is due to duplicate legacy category
978 Check_Duplicate_Category(x_return_status => l_return_status2);
979 IF (l_return_status2 <> FND_API.G_RET_STS_SUCCESS) THEN
980 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
981 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Error occured while calling INV_ITEM_CATEGORY_PUB.Create_Category for legacy category');
982 end if;
983 RAISE FND_API.G_EXC_ERROR;
984 ELSE -- if duplicate found
985 Find_Duplicate_Category_Id(
986 p_structure_id => p_structure_id,
987 p_category_name => l_legacy_category_name,
988 x_category_id => l_dup_legacy_category_id
989 );
990 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
991 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Duplicate legacy category found with id:' || l_dup_legacy_category_id);
992 end if;
993 -- Assign duplicate category id to category id (that otherwise should have been created)
994 l_out_legacy_category_id := l_dup_legacy_category_id;
995 END IF;
996 END IF;
997
998 l_skip_valid_cat := 'N';
999 -- If a duplicate category was found, there is a chance that a record for this category
1000 -- already exists in MTL_CATEGORY_SET_VALID_CATS. If such a record exists then skip
1001 -- creating a new record in MTL_CATEGORY_SET_VALID_CATS table
1002 IF (l_dup_legacy_category_id is not null) THEN
1003 OPEN C_Valid_Cat_Exists(p_category_set_id, l_dup_legacy_category_id);
1004 FETCH C_Valid_Cat_Exists INTO l_val;
1005 IF (C_Valid_Cat_Exists%FOUND) THEN
1006 l_skip_valid_cat := 'Y';
1007 END IF;
1008
1009 CLOSE C_Valid_Cat_Exists;
1010 END IF;
1011
1012 IF (l_skip_valid_cat = 'N') THEN
1013 -- Create Legacy Records in MTL_CATEGORY_SET_VALID_CATS
1014 INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
1015 p_api_version => G_CREATE_CAT_API_VER,
1016 p_init_msg_list => FND_API.G_FALSE,
1017 p_commit => FND_API.G_FALSE,
1018 p_category_set_id => p_category_set_id,
1019 p_category_id => l_out_legacy_category_id,
1020 p_parent_category_id => l_out_category_id,
1021 x_return_status => l_return_status,
1022 x_errorcode => l_error_code,
1023 x_msg_count => l_msg_count,
1024 x_msg_data => l_msg_data
1025 );
1026
1027 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1028 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1029 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Error occured while calling INV_ITEM_CATEGORY_PUB.Create_Valid_Category for legacy category');
1030 end if;
1031
1032 RAISE FND_API.G_EXC_ERROR;
1033 END IF;
1034 END IF;
1035
1036 Open C_EXISTS_EXTN_ID(p_category_set_id,l_out_legacy_category_id);
1037 FETCH C_EXISTS_EXTN_ID into l_val;
1038 IF C_EXISTS_EXTN_ID%NOTFOUND
1039 THEN
1040 -- do the insert here
1041 INSERT INTO EGO_PRODUCT_CAT_SET_EXT ( EXTENSION_ID, CATEGORY_SET_ID, CATEGORY_ID, ATTR_GROUP_ID,
1042 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
1043 INCLUDE_IN_FORECAST, EXPECTED_PURCHASE, EXCLUDE_USER_VIEW )
1044 VALUES ( EGO_EXTFWK_S.NEXTVAL, p_category_set_id, l_out_legacy_category_id, p_attr_group_id, -1, sysdate, -1, sysdate, -1, 'Y', p_expected_purchase, l_exclude_user_view);
1045 ELSE
1046 UPDATE EGO_PRODUCT_CAT_SET_EXT
1047 SET EXPECTED_PURCHASE = p_expected_purchase,
1048 EXCLUDE_USER_VIEW = l_exclude_user_view
1049 WHERE CATEGORY_SET_ID = p_category_set_id
1050 AND CATEGORY_ID = l_out_legacy_category_id;
1051 END IF;
1052 Close C_EXISTS_EXTN_ID;
1053 END;
1054 ELSE -- IF (l_create_legacy...)
1055 l_out_legacy_category_id := l_out_category_id;
1056 END IF; --END IF (l_create_legacy...)
1057
1058 -- Before attaching items to categories, cleanup the non-required legacy categories (Bug 3495005)
1059 IF p_interest_level = 2 THEN
1060 Cleanup_Legacy_Categories(p_category_set_id, l_out_legacy_category_id);
1061 END IF;
1062
1063 -- Create Legacy Records in MTL_ITEM_CATEGORIES
1064 FOR scr in C_Get_Items(p_old_structure_id, p_int_typ_cod_id, p_interest_level)
1065 LOOP
1069 p_inventory_item_id=>scr.inventory_item_id,
1066
1067 Assign_Item_To_Category(p_category_set_id=>p_category_set_id,
1068 p_organization_id=>scr.organization_id,
1070 p_category_id=>l_out_legacy_category_id,
1071 p_control_level=>p_control_level,
1072 p_mult_item_cat_assign_flag => p_mult_item_cat_assign_flag,
1073 x_return_status => l_return_status,
1074 x_errorcode => l_error_code,
1075 x_msg_count => l_msg_count,
1076 x_msg_data => l_msg_data);
1077
1078
1079
1080
1081 END LOOP;
1082 END IF;
1083 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1084 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Finished Process_Categories, Returning category_id ' || l_out_category_id);
1085 end if;
1086
1090 WHEN OTHERS THEN
1087 x_category_id := l_out_category_id;
1088
1089 EXCEPTION
1091 if l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1092 l_return_status := FND_API.G_RET_STS_ERROR;
1093 end if;
1094
1095 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in Process_Categories');
1096 --FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Process_Categories');
1097 x_return_status := l_return_status;
1098 x_msg_count := l_msg_count;
1099 x_msg_data := l_msg_data;
1100 x_category_id := l_out_category_id;
1101
1102 RAISE;
1103
1104 END Process_Categories;
1105
1106 PROCEDURE Assign_Item_To_Category(p_category_set_id IN NUMBER,
1107 p_organization_id IN NUMBER,
1108 p_inventory_item_id IN NUMBER,
1109 p_category_id IN NUMBER,
1110 p_control_level IN NUMBER,
1111 p_mult_item_cat_assign_flag IN VARCHAR2,
1112 x_return_status OUT NOCOPY VARCHAR2,
1113 x_errorcode OUT NOCOPY NUMBER,
1114 x_msg_count OUT NOCOPY NUMBER,
1115 x_msg_data OUT NOCOPY VARCHAR2) IS
1116
1117 l_create_item_rec VARCHAR2(1);
1118 l_val NUMBER;
1119 l_the_item_assign_count NUMBER;
1120 l_the_cat_assign_count NUMBER;
1121 l_exists VARCHAR2(1);
1122
1123 CURSOR C_Check_Master_Org(c_organization_id NUMBER) IS
1124 select 1
1125 from mtl_parameters
1126 where organization_id = master_organization_id
1127 and organization_id = c_organization_id;
1128
1129 CURSOR item_cat_assign_count_csr
1130 ( p_inventory_item_id NUMBER
1131 , p_organization_id NUMBER
1132 , p_category_set_id NUMBER
1133 , p_category_id NUMBER
1134 ) IS
1135 SELECT COUNT( category_id ), COUNT( DECODE(category_id, p_category_id,1, NULL) )
1136 FROM mtl_item_categories
1137 WHERE
1138 inventory_item_id = p_inventory_item_id
1139 AND organization_id = p_organization_id
1140 AND category_set_id = p_category_set_id;
1141
1142 CURSOR org_item_exists_csr
1143 ( p_inventory_item_id NUMBER
1144 , p_organization_id NUMBER
1145 ) IS
1146 SELECT 'x' --2879647
1147 FROM mtl_system_items_b
1148 WHERE inventory_item_id = p_inventory_item_id
1149 AND organization_id = p_organization_id;
1150
1151 CURSOR category_exists_csr (p_category_id NUMBER)
1152 IS
1153 SELECT 'x'
1154 FROM mtl_categories_b
1155 WHERE category_id = p_category_id
1156 AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE;
1157
1158 BEGIN
1159 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1160 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Associating item ' || p_inventory_item_id || ' with category ' || p_category_id || ' for org id ' || p_organization_id || ' and set id ' || p_category_set_id);
1161 end if;
1162
1163 /*
1164 -- Update MTL_ITEM_CATEGORIES
1165 Update MTL_ITEM_CATEGORIES
1166 Set category_id = p_category_id
1167 where category_set_id = p_category_set_id
1168 and organization_id = p_organization_id
1169 and inventory_item_id = p_inventory_item_id;
1170
1171 -- If nothing is updated, then insert
1172 --IF (SQL%NOTFOUND) THEN
1173 */
1174
1175 l_create_item_rec := 'Y';
1176
1177 -- Check if item exists for that org
1178 OPEN org_item_exists_csr (p_inventory_item_id, p_organization_id);
1179 FETCH org_item_exists_csr INTO l_exists;
1180 IF (org_item_exists_csr%NOTFOUND) THEN
1181 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1182 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Warning! Ignoring item ' || p_inventory_item_id || ' as this item is not associated with organization ' || p_organization_id);
1183 end if;
1184
1185 l_create_item_rec := 'N';
1186 END IF;
1187 CLOSE org_item_exists_csr;
1188
1189 -- Check if category exists and is enabled
1190 IF (l_create_item_rec = 'Y') THEN
1191 OPEN category_exists_csr (p_category_id);
1192 FETCH category_exists_csr INTO l_exists;
1193 IF (category_exists_csr%NOTFOUND) THEN
1194 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1195 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Warning! Unable to associate items to category ' || p_category_id || ' as the category is disabled');
1196 end if;
1197 l_create_item_rec := 'N';
1198 END IF;
1199 CLOSE category_exists_csr;
1200 END IF;
1201
1202 -- Following validation ensures that the below mentioned business rule is satisfied:
1203 -- 'Cannot Create/Delete Item Controlled category set from Organization Items.'
1204 -- Basically, if the control level is 'Master', items can be created only for master org
1205 -- This validation required here otherwise INV_ITEM_CATEGORY_PUB.Create_Category_Assignment
1206 -- throws this as an error
1207 IF (l_create_item_rec = 'Y' AND p_control_level = G_CONTROL_LEVEL_MASTER) THEN
1208 open C_Check_Master_Org(p_organization_id);
1209 fetch C_Check_Master_Org into l_val;
1210 IF C_Check_Master_Org%NOTFOUND THEN
1214 l_create_item_rec := 'N';
1211 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1212 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Organization ' || p_organization_id || ' is not a master org');
1213 end if;
1215 END IF;
1216 Close C_Check_Master_Org;
1217 END IF;
1218
1219 IF (l_create_item_rec = 'Y') THEN
1220
1221 -- Get this item all category assignments count, and this category assignments count
1222 OPEN item_cat_assign_count_csr (p_inventory_item_id,
1223 p_organization_id,
1224 p_category_set_id,
1225 p_category_id);
1226
1227 FETCH item_cat_assign_count_csr INTO l_the_item_assign_count, l_the_cat_assign_count;
1228
1229 -- If a Category Set is defined with the MULT_ITEM_CAT_ASSIGN_FLAG set to 'N'
1230 -- then an Item may be assigned to only one Category in the Category Set.
1231 -- if more categories are assigned, then delete those associations first
1232 IF (p_mult_item_cat_assign_flag = 'N'
1233 AND (l_the_item_assign_count - l_the_cat_assign_count) > 0 ) THEN
1234 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1235 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Deleting existing item associations');
1236 end if;
1237
1238 DELETE FROM mtl_item_categories
1239 WHERE organization_id = p_organization_id
1240 AND inventory_item_id = p_inventory_item_id
1241 AND category_set_id = p_category_set_id;
1242 END IF;
1243
1244 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1245 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Creating new item associations');
1246 end if;
1247 INV_ITEM_CATEGORY_PUB.Create_Category_Assignment (
1248 p_api_version => G_CREATE_CAT_API_VER,
1249 p_init_msg_list => FND_API.G_FALSE,
1250 p_commit => FND_API.G_FALSE,
1251 x_return_status => x_return_status,
1252 x_errorcode => x_errorcode,
1253 x_msg_count => x_msg_count,
1254 x_msg_data => x_msg_data,
1255 p_category_id => p_category_id,
1256 p_category_set_id => p_category_set_id,
1257 p_inventory_item_id => p_inventory_item_id,
1258 p_organization_id => p_organization_id
1259 );
1260
1261 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1262 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1263 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Error occured while calling INV_ITEM_CATEGORY_PUB.Create_Category_Assignment for inventory_item_id=' || p_inventory_item_id);
1264 end if;
1265
1266 RAISE FND_API.G_EXC_ERROR;
1267 END IF;
1268 END IF;
1269 END Assign_Item_To_Category;
1270
1271 /*
1272 Return success if duplicate category is found.
1273 Parses the error message returned by create API to find out if create failed due to duplicate category
1274 */
1275 PROCEDURE Check_Duplicate_Category(
1276 x_return_status OUT NOCOPY VARCHAR2) IS
1277
1278 l_msg_data VARCHAR2(2000);
1279 l_msg_count NUMBER;
1280 BEGIN
1281 x_return_status := FND_API.G_RET_STS_ERROR;
1282 fnd_msg_pub.count_and_get( p_encoded => 'F'
1283 , p_count => l_msg_count
1284 , p_data => l_msg_data);
1285 for k in 1 .. l_msg_count loop
1286 l_msg_data := fnd_msg_pub.get( p_msg_index => k,
1287 p_encoded => 'F'
1288 );
1289 -- If this token is found in the message, it signifies a duplicate
1290 if (INSTR(l_msg_data,'Category Segment Combination')> 0)
1291 then
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293 FND_MSG_PUB.Delete_Msg(k);
1294 exit;
1295 end if;
1296 end loop;
1297 END Check_Duplicate_Category;
1298
1299 /*
1300 Given a category name, find the category id by looking into MTL_CATEGORIES_B table
1301 */
1302 PROCEDURE Find_Duplicate_Category_Id(
1303 p_structure_id IN NUMBER,
1304 p_category_name IN VARCHAR2,
1305 x_category_id OUT NOCOPY NUMBER
1306 ) IS
1307
1308 l_category_id NUMBER;
1309
1310 CURSOR C_Get_Category_Id(c_structure_id Number, c_category_name VARCHAR2) IS
1311 select category_id
1312 from MTL_CATEGORIES_B
1313 where structure_id = c_structure_id
1314 and segment1 = c_category_name
1315 and segment2 is null
1316 and segment3 is null
1317 and segment4 is null
1318 and segment5 is null
1319 and segment6 is null
1320 and segment7 is null
1321 and segment8 is null
1322 and segment9 is null
1323 and segment10 is null
1324 and segment11 is null
1325 and segment12 is null
1326 and segment13 is null
1327 and segment14 is null
1328 and segment15 is null
1329 and segment16 is null
1330 and segment17 is null
1331 and segment18 is null
1332 and segment19 is null
1333 and segment20 is null;
1334 BEGIN
1335 OPEN C_Get_Category_Id(p_structure_id, p_category_name);
1336 FETCH C_Get_Category_Id into l_category_id;
1340 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Unable to find category with name ' || p_category_name);
1337 IF (C_Get_Category_Id%NOTFOUND) THEN
1338 CLOSE C_Get_Category_Id;
1339 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1341 end if;
1342 RAISE FND_API.G_EXC_ERROR;
1343 ELSE
1344 CLOSE C_Get_Category_Id;
1345 END IF;
1346
1347 x_category_id := l_category_id;
1348 END Find_Duplicate_Category_Id;
1349
1350 PROCEDURE Cleanup_Legacy_Categories(p_category_set_id IN NUMBER,
1351 p_category_id IN NUMBER) IS
1352
1353 CURSOR C_Get_Legacy_Category_Id(c_category_set_id NUMBER, c_category_id NUMBER) IS
1354 select B.category_id, B.segment1 category_name from mtl_category_set_valid_cats V, mtl_categories_b B
1355 where V.category_set_id=c_category_set_id
1356 and V.parent_category_id=c_category_id
1357 and V.category_id = B.category_id;
1358 --and B.segment1 like '%LEGACY';
1359 BEGIN
1360 FOR scr in C_Get_Legacy_Category_Id(p_category_set_id, p_category_id)
1361 LOOP
1362 IF G_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1363 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmcatb','Deleting unused legacy category with id:' || scr.category_id || ' and name:' || scr.category_name);
1364 end if;
1365 -- No need to update transaction tables since they are handled in a seperate migration program.
1366 update mtl_item_categories
1367 set category_id=p_category_id
1368 where category_id=scr.category_id
1369 and category_set_id=p_category_set_id;
1370 delete from ego_product_cat_set_ext
1371 where category_id=scr.category_id and category_set_id=p_category_set_id;
1372 delete from mtl_category_set_valid_cats
1373 where category_id=scr.category_id and category_set_id=p_category_set_id;
1374 delete from mtl_categories_b where category_id=scr.category_id;
1375 delete from mtl_categories_tl where category_id=scr.category_id;
1376 END LOOP;
1377
1378 END Cleanup_Legacy_Categories;
1379
1380
1381 /* For testing purposes we can use the following statements.
1382 delete from mtl_categories_tl where category_id in
1383 (select category_id from mtl_categories_b where structure_id in
1384 (select structure_id from mtl_category_sets where category_set_name='SME Product Catalog'));
1385 delete from mtl_categories_b where structure_id in
1386 (select structure_id from mtl_category_sets where category_set_name='SME Product Catalog');
1387 delete from mtl_category_set_valid_cats where category_set_id in
1388 (select category_set_id from mtl_category_sets where category_set_name='SME Product Catalog');
1389 delete from mtl_item_categories where category_set_id in
1390 (select category_set_id from mtl_category_sets where category_set_name='SME Product Catalog');
1391 delete from EGO_PRODUCT_CAT_SET_EXT where category_set_id in
1392 (select category_set_id from mtl_category_sets where category_set_name='SME Product Catalog');
1393 commit;
1394 */
1395
1396 END AS_CATALOG_MIGRATION;
1397
[Home] [Help]