[Home] [Help]
PACKAGE BODY: APPS.MTL_CATEGORIES_PKG
Source
1 package body MTL_CATEGORIES_PKG as
2 /* $Header: INVICAHB.pls 120.12.12010000.3 2009/03/02 21:42:05 sanmani ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_CATEGORY_ID in NUMBER,
7 X_DESCRIPTION in VARCHAR2,
8 X_STRUCTURE_ID in NUMBER,
9 X_DISABLE_DATE in DATE,
10 X_WEB_STATUS in VARCHAR2,
11 X_SUPPLIER_ENABLED_FLAG in VARCHAR2,
12 X_SEGMENT1 in VARCHAR2,
13 X_SEGMENT2 in VARCHAR2,
14 X_SEGMENT3 in VARCHAR2,
15 X_SEGMENT4 in VARCHAR2,
16 X_SEGMENT5 in VARCHAR2,
17 X_SEGMENT6 in VARCHAR2,
18 X_SEGMENT7 in VARCHAR2,
19 X_SEGMENT8 in VARCHAR2,
20 X_SEGMENT9 in VARCHAR2,
21 X_SEGMENT10 in VARCHAR2,
22 X_SEGMENT11 in VARCHAR2,
23 X_SEGMENT12 in VARCHAR2,
24 X_SEGMENT13 in VARCHAR2,
25 X_SEGMENT14 in VARCHAR2,
26 X_SEGMENT15 in VARCHAR2,
27 X_SEGMENT16 in VARCHAR2,
28 X_SEGMENT17 in VARCHAR2,
29 X_SEGMENT18 in VARCHAR2,
30 X_SEGMENT19 in VARCHAR2,
31 X_SEGMENT20 in VARCHAR2,
32 X_SUMMARY_FLAG in VARCHAR2,
33 X_ENABLED_FLAG in VARCHAR2,
34 X_START_DATE_ACTIVE in DATE,
35 X_END_DATE_ACTIVE in DATE,
36 X_ATTRIBUTE_CATEGORY in VARCHAR2,
37 X_ATTRIBUTE1 in VARCHAR2,
38 X_ATTRIBUTE2 in VARCHAR2,
39 X_ATTRIBUTE3 in VARCHAR2,
40 X_ATTRIBUTE4 in VARCHAR2,
41 X_ATTRIBUTE5 in VARCHAR2,
42 X_ATTRIBUTE6 in VARCHAR2,
43 X_ATTRIBUTE7 in VARCHAR2,
44 X_ATTRIBUTE8 in VARCHAR2,
45 X_ATTRIBUTE9 in VARCHAR2,
46 X_ATTRIBUTE10 in VARCHAR2,
47 X_ATTRIBUTE11 in VARCHAR2,
48 X_ATTRIBUTE12 in VARCHAR2,
49 X_ATTRIBUTE13 in VARCHAR2,
50 X_ATTRIBUTE14 in VARCHAR2,
51 X_ATTRIBUTE15 in VARCHAR2,
52 X_LAST_UPDATE_DATE in DATE,
53 X_LAST_UPDATED_BY in NUMBER,
54 X_CREATION_DATE in DATE,
55 X_CREATED_BY in NUMBER,
56 X_LAST_UPDATE_LOGIN in NUMBER
57 -- X_REQUEST_ID in NUMBER,
58 ) is
59
60 cursor C is
61 select ROWID
62 from MTL_CATEGORIES_B
63 where CATEGORY_ID = X_CATEGORY_ID ;
64
65 begin
66
67 insert into MTL_CATEGORIES_B (
68 CATEGORY_ID,
69 STRUCTURE_ID,
70 DISABLE_DATE,
71 WEB_STATUS,
72 SUPPLIER_ENABLED_FLAG,
73 SEGMENT1,
74 SEGMENT2,
75 SEGMENT3,
76 SEGMENT4,
77 SEGMENT5,
78 SEGMENT6,
79 SEGMENT7,
80 SEGMENT8,
81 SEGMENT9,
82 SEGMENT10,
83 SEGMENT11,
84 SEGMENT12,
85 SEGMENT13,
86 SEGMENT14,
87 SEGMENT15,
88 SEGMENT16,
89 SEGMENT17,
90 SEGMENT18,
91 SEGMENT19,
92 SEGMENT20,
93 SUMMARY_FLAG,
94 ENABLED_FLAG,
95 START_DATE_ACTIVE,
96 END_DATE_ACTIVE,
97 ATTRIBUTE_CATEGORY,
98 ATTRIBUTE1,
99 ATTRIBUTE2,
100 ATTRIBUTE3,
101 ATTRIBUTE4,
102 ATTRIBUTE5,
103 ATTRIBUTE6,
104 ATTRIBUTE7,
105 ATTRIBUTE8,
106 ATTRIBUTE9,
107 ATTRIBUTE10,
108 ATTRIBUTE11,
109 ATTRIBUTE12,
110 ATTRIBUTE13,
111 ATTRIBUTE14,
112 ATTRIBUTE15,
113 -- WH_UPDATE_DATE,
114 -- TOTAL_PROD_ID,
115 -- REQUEST_ID,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 CREATION_DATE,
119 CREATED_BY,
120 LAST_UPDATE_LOGIN
121 ) values (
122 X_CATEGORY_ID,
123 X_STRUCTURE_ID,
124 X_DISABLE_DATE,
125 X_WEB_STATUS,
126 X_SUPPLIER_ENABLED_FLAG,
127 X_SEGMENT1,
128 X_SEGMENT2,
129 X_SEGMENT3,
130 X_SEGMENT4,
131 X_SEGMENT5,
132 X_SEGMENT6,
133 X_SEGMENT7,
134 X_SEGMENT8,
135 X_SEGMENT9,
136 X_SEGMENT10,
137 X_SEGMENT11,
138 X_SEGMENT12,
139 X_SEGMENT13,
140 X_SEGMENT14,
141 X_SEGMENT15,
142 X_SEGMENT16,
143 X_SEGMENT17,
144 X_SEGMENT18,
145 X_SEGMENT19,
146 X_SEGMENT20,
147 X_SUMMARY_FLAG,
148 X_ENABLED_FLAG,
149 X_START_DATE_ACTIVE,
150 X_END_DATE_ACTIVE,
151 X_ATTRIBUTE_CATEGORY,
152 X_ATTRIBUTE1,
153 X_ATTRIBUTE2,
154 X_ATTRIBUTE3,
155 X_ATTRIBUTE4,
156 X_ATTRIBUTE5,
157 X_ATTRIBUTE6,
158 X_ATTRIBUTE7,
159 X_ATTRIBUTE8,
160 X_ATTRIBUTE9,
161 X_ATTRIBUTE10,
162 X_ATTRIBUTE11,
163 X_ATTRIBUTE12,
164 X_ATTRIBUTE13,
165 X_ATTRIBUTE14,
166 X_ATTRIBUTE15,
167 -- X_WH_UPDATE_DATE,
168 -- X_TOTAL_PROD_ID,
169 -- X_REQUEST_ID,
170 X_LAST_UPDATE_DATE,
171 X_LAST_UPDATED_BY,
172 X_CREATION_DATE,
173 X_CREATED_BY,
174 X_LAST_UPDATE_LOGIN
175 );
176
177 insert into MTL_CATEGORIES_TL (
178 CATEGORY_ID,
179 LANGUAGE,
180 SOURCE_LANG,
181 DESCRIPTION,
182 LAST_UPDATE_DATE,
183 LAST_UPDATED_BY,
184 CREATION_DATE,
185 CREATED_BY,
186 LAST_UPDATE_LOGIN
187 ) select
188 X_CATEGORY_ID,
189 L.LANGUAGE_CODE,
190 userenv('LANG'),
191 X_DESCRIPTION,
192 X_LAST_UPDATE_DATE,
193 X_LAST_UPDATED_BY,
194 X_CREATION_DATE,
195 X_CREATED_BY,
196 X_LAST_UPDATE_LOGIN
197 from FND_LANGUAGES L
198 where L.INSTALLED_FLAG in ('I', 'B')
199 and not exists
200 ( select NULL
201 from MTL_CATEGORIES_TL T
202 where T.CATEGORY_ID = X_CATEGORY_ID
203 and T.LANGUAGE = L.LANGUAGE_CODE );
204
205 open c;
206 fetch c into X_ROWID;
207 if (c%notfound) then
208 close c;
209 raise no_data_found;
210 end if;
211 close c;
212
213
214 --R12: Raise Business Events and Call APIs
215 BEGIN
216 INV_ITEM_EVENTS_PVT.Raise_Events(
217 p_event_name => 'EGO_WF_WRAPPER_PVT.G_CAT_CATEGORY_CHANGE_EVENT'
218 ,p_dml_type => 'CREATE'
219 ,p_category_id => X_CATEGORY_ID);
220 EXCEPTION
221 WHEN OTHERS THEN
222 NULL;
223 END;
224
225 --Call ICX APIs
226 BEGIN
227 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
228 p_entity_type => 'CATEGORY'
229 ,p_dml_type => 'CREATE'
230 ,p_category_id => X_CATEGORY_ID
231 ,p_structure_id => X_STRUCTURE_ID);
232 EXCEPTION
233 WHEN OTHERS THEN
234 NULL;
235 END;
236 --R12: Business Event Enhancement:
237
238
239
240 end INSERT_ROW;
241
242
243 procedure LOCK_ROW (
244 X_CATEGORY_ID in NUMBER,
245 X_DESCRIPTION in VARCHAR2,
246 X_STRUCTURE_ID in NUMBER,
247 X_DISABLE_DATE in DATE,
248 X_WEB_STATUS in VARCHAR2,
249 X_SUPPLIER_ENABLED_FLAG in VARCHAR2,
250 X_SEGMENT1 in VARCHAR2,
251 X_SEGMENT2 in VARCHAR2,
252 X_SEGMENT3 in VARCHAR2,
253 X_SEGMENT4 in VARCHAR2,
254 X_SEGMENT5 in VARCHAR2,
255 X_SEGMENT6 in VARCHAR2,
256 X_SEGMENT7 in VARCHAR2,
257 X_SEGMENT8 in VARCHAR2,
258 X_SEGMENT9 in VARCHAR2,
259 X_SEGMENT10 in VARCHAR2,
260 X_SEGMENT11 in VARCHAR2,
261 X_SEGMENT12 in VARCHAR2,
262 X_SEGMENT13 in VARCHAR2,
263 X_SEGMENT14 in VARCHAR2,
264 X_SEGMENT15 in VARCHAR2,
265 X_SEGMENT16 in VARCHAR2,
266 X_SEGMENT17 in VARCHAR2,
267 X_SEGMENT18 in VARCHAR2,
268 X_SEGMENT19 in VARCHAR2,
269 X_SEGMENT20 in VARCHAR2,
270 X_SUMMARY_FLAG in VARCHAR2,
271 X_ENABLED_FLAG in VARCHAR2,
272 X_START_DATE_ACTIVE in DATE,
273 X_END_DATE_ACTIVE in DATE,
274 X_ATTRIBUTE_CATEGORY in VARCHAR2,
275 X_ATTRIBUTE1 in VARCHAR2,
276 X_ATTRIBUTE2 in VARCHAR2,
277 X_ATTRIBUTE3 in VARCHAR2,
278 X_ATTRIBUTE4 in VARCHAR2,
279 X_ATTRIBUTE5 in VARCHAR2,
280 X_ATTRIBUTE6 in VARCHAR2,
281 X_ATTRIBUTE7 in VARCHAR2,
282 X_ATTRIBUTE8 in VARCHAR2,
283 X_ATTRIBUTE9 in VARCHAR2,
284 X_ATTRIBUTE10 in VARCHAR2,
285 X_ATTRIBUTE11 in VARCHAR2,
286 X_ATTRIBUTE12 in VARCHAR2,
287 X_ATTRIBUTE13 in VARCHAR2,
288 X_ATTRIBUTE14 in VARCHAR2,
289 X_ATTRIBUTE15 in VARCHAR2
290 -- X_REQUEST_ID in NUMBER,
291 ) is
292
293 cursor c is
294 select
295 STRUCTURE_ID,
296 DISABLE_DATE,
297 WEB_STATUS,
298 SUPPLIER_ENABLED_FLAG,
299 SEGMENT1,
300 SEGMENT2,
301 SEGMENT3,
302 SEGMENT4,
303 SEGMENT5,
304 SEGMENT6,
305 SEGMENT7,
306 SEGMENT8,
307 SEGMENT9,
308 SEGMENT10,
309 SEGMENT11,
310 SEGMENT12,
311 SEGMENT13,
312 SEGMENT14,
313 SEGMENT15,
314 SEGMENT16,
315 SEGMENT17,
316 SEGMENT18,
317 SEGMENT19,
318 SEGMENT20,
319 SUMMARY_FLAG,
320 ENABLED_FLAG,
321 START_DATE_ACTIVE,
322 END_DATE_ACTIVE,
323 ATTRIBUTE_CATEGORY,
324 ATTRIBUTE1,
325 ATTRIBUTE2,
326 ATTRIBUTE3,
327 ATTRIBUTE4,
328 ATTRIBUTE5,
329 ATTRIBUTE6,
330 ATTRIBUTE7,
331 ATTRIBUTE8,
332 ATTRIBUTE9,
333 ATTRIBUTE10,
334 ATTRIBUTE11,
335 ATTRIBUTE12,
336 ATTRIBUTE13,
337 ATTRIBUTE14,
338 ATTRIBUTE15
339 -- WH_UPDATE_DATE,
340 -- TOTAL_PROD_ID,
341 -- REQUEST_ID,
342 from MTL_CATEGORIES_B
343 where CATEGORY_ID = X_CATEGORY_ID
344 for update of CATEGORY_ID nowait;
345
346 recinfo c%rowtype;
347
348 cursor c1 is
349 select
350 DESCRIPTION,
351 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
352 from MTL_CATEGORIES_TL
353 where CATEGORY_ID = X_CATEGORY_ID
354 -- Commented out. All translation rows need to be locked.
355 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
356 for update of CATEGORY_ID nowait;
357
358 begin
359
360 open c;
361 fetch c into recinfo;
362 if (c%notfound) then
363 close c;
364 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
365 app_exception.raise_exception;
366 end if;
367 close c;
368
369 if ( (recinfo.STRUCTURE_ID = X_STRUCTURE_ID)
370 AND ((recinfo.DISABLE_DATE = X_DISABLE_DATE)
371 OR ((recinfo.DISABLE_DATE is null) AND (X_DISABLE_DATE is null)))
372 -- AND ((recinfo.WEB_STATUS = X_WEB_STATUS) Bug: 4494727
373 -- OR ((recinfo.WEB_STATUS is null) AND (X_WEB_STATUS is null)))
374 AND ((recinfo.SUPPLIER_ENABLED_FLAG = X_SUPPLIER_ENABLED_FLAG)
375 OR ((recinfo.SUPPLIER_ENABLED_FLAG is null) AND (X_SUPPLIER_ENABLED_FLAG is null)))
376 AND ((recinfo.SEGMENT1 = X_SEGMENT1)
377 OR ((recinfo.SEGMENT1 is null) AND (X_SEGMENT1 is null)))
378 AND ((recinfo.SEGMENT2 = X_SEGMENT2)
379 OR ((recinfo.SEGMENT2 is null) AND (X_SEGMENT2 is null)))
380 AND ((recinfo.SEGMENT3 = X_SEGMENT3)
381 OR ((recinfo.SEGMENT3 is null) AND (X_SEGMENT3 is null)))
382 AND ((recinfo.SEGMENT4 = X_SEGMENT4)
383 OR ((recinfo.SEGMENT4 is null) AND (X_SEGMENT4 is null)))
384 AND ((recinfo.SEGMENT5 = X_SEGMENT5)
385 OR ((recinfo.SEGMENT5 is null) AND (X_SEGMENT5 is null)))
386 AND ((recinfo.SEGMENT6 = X_SEGMENT6)
387 OR ((recinfo.SEGMENT6 is null) AND (X_SEGMENT6 is null)))
388 AND ((recinfo.SEGMENT7 = X_SEGMENT7)
389 OR ((recinfo.SEGMENT7 is null) AND (X_SEGMENT7 is null)))
390 AND ((recinfo.SEGMENT8 = X_SEGMENT8)
391 OR ((recinfo.SEGMENT8 is null) AND (X_SEGMENT8 is null)))
392 AND ((recinfo.SEGMENT9 = X_SEGMENT9)
393 OR ((recinfo.SEGMENT9 is null) AND (X_SEGMENT9 is null)))
394 AND ((recinfo.SEGMENT10 = X_SEGMENT10)
395 OR ((recinfo.SEGMENT10 is null) AND (X_SEGMENT10 is null)))
396 AND ((recinfo.SEGMENT11 = X_SEGMENT11)
397 OR ((recinfo.SEGMENT11 is null) AND (X_SEGMENT11 is null)))
398 AND ((recinfo.SEGMENT12 = X_SEGMENT12)
399 OR ((recinfo.SEGMENT12 is null) AND (X_SEGMENT12 is null)))
400 AND ((recinfo.SEGMENT13 = X_SEGMENT13)
401 OR ((recinfo.SEGMENT13 is null) AND (X_SEGMENT13 is null)))
402 AND ((recinfo.SEGMENT14 = X_SEGMENT14)
403 OR ((recinfo.SEGMENT14 is null) AND (X_SEGMENT14 is null)))
404 AND ((recinfo.SEGMENT15 = X_SEGMENT15)
405 OR ((recinfo.SEGMENT15 is null) AND (X_SEGMENT15 is null)))
406 AND ((recinfo.SEGMENT16 = X_SEGMENT16)
407 OR ((recinfo.SEGMENT16 is null) AND (X_SEGMENT16 is null)))
408 AND ((recinfo.SEGMENT17 = X_SEGMENT17)
409 OR ((recinfo.SEGMENT17 is null) AND (X_SEGMENT17 is null)))
410 AND ((recinfo.SEGMENT18 = X_SEGMENT18)
411 OR ((recinfo.SEGMENT18 is null) AND (X_SEGMENT18 is null)))
412 AND ((recinfo.SEGMENT19 = X_SEGMENT19)
413 OR ((recinfo.SEGMENT19 is null) AND (X_SEGMENT19 is null)))
414 AND ((recinfo.SEGMENT20 = X_SEGMENT20)
415 OR ((recinfo.SEGMENT20 is null) AND (X_SEGMENT20 is null)))
416 AND (recinfo.SUMMARY_FLAG = X_SUMMARY_FLAG)
417 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
418 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
419 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
420 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
421 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
422 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
423 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
424 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
425 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
426 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
427 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
428 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
429 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
430 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
431 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
432 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
433 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
434 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
435 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
436 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
437 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
438 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
439 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
440 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
441 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
442 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
443 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
444 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
445 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
446 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
447 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
448 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
449 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
450 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
451 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
452 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
453 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
454 -- AND ((recinfo.WH_UPDATE_DATE = X_WH_UPDATE_DATE)
455 -- OR ((recinfo.WH_UPDATE_DATE is null) AND (X_WH_UPDATE_DATE is null)))
456 -- AND ((recinfo.TOTAL_PROD_ID = X_TOTAL_PROD_ID)
457 -- OR ((recinfo.TOTAL_PROD_ID is null) AND (X_TOTAL_PROD_ID is null)))
458 -- AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
459 -- OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
460 ) then
461 null;
462 else
463 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
464 app_exception.raise_exception;
465 end if;
466
467 for tlinfo in c1 loop
468 if (tlinfo.BASELANG = 'Y') then
469 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
470 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
471 ) then
472 null;
473 else
474 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
475 app_exception.raise_exception;
476 end if;
477 end if;
478 end loop;
479
480 return;
481
482 end LOCK_ROW;
483
484
485 procedure UPDATE_ROW
486 (
487 X_CATEGORY_ID in NUMBER,
488 X_DESCRIPTION in VARCHAR2,
489 X_STRUCTURE_ID in NUMBER,
490 X_DISABLE_DATE in DATE,
491 X_WEB_STATUS in VARCHAR2,
492 X_SUPPLIER_ENABLED_FLAG in VARCHAR2,
493 X_SEGMENT1 in VARCHAR2,
494 X_SEGMENT2 in VARCHAR2,
495 X_SEGMENT3 in VARCHAR2,
496 X_SEGMENT4 in VARCHAR2,
497 X_SEGMENT5 in VARCHAR2,
498 X_SEGMENT6 in VARCHAR2,
499 X_SEGMENT7 in VARCHAR2,
500 X_SEGMENT8 in VARCHAR2,
501 X_SEGMENT9 in VARCHAR2,
502 X_SEGMENT10 in VARCHAR2,
503 X_SEGMENT11 in VARCHAR2,
504 X_SEGMENT12 in VARCHAR2,
505 X_SEGMENT13 in VARCHAR2,
506 X_SEGMENT14 in VARCHAR2,
507 X_SEGMENT15 in VARCHAR2,
508 X_SEGMENT16 in VARCHAR2,
509 X_SEGMENT17 in VARCHAR2,
510 X_SEGMENT18 in VARCHAR2,
511 X_SEGMENT19 in VARCHAR2,
512 X_SEGMENT20 in VARCHAR2,
513 X_SUMMARY_FLAG in VARCHAR2,
514 X_ENABLED_FLAG in VARCHAR2,
515 X_START_DATE_ACTIVE in DATE,
516 X_END_DATE_ACTIVE in DATE,
517 X_ATTRIBUTE_CATEGORY in VARCHAR2,
518 X_ATTRIBUTE1 in VARCHAR2,
519 X_ATTRIBUTE2 in VARCHAR2,
520 X_ATTRIBUTE3 in VARCHAR2,
521 X_ATTRIBUTE4 in VARCHAR2,
522 X_ATTRIBUTE5 in VARCHAR2,
523 X_ATTRIBUTE6 in VARCHAR2,
524 X_ATTRIBUTE7 in VARCHAR2,
525 X_ATTRIBUTE8 in VARCHAR2,
526 X_ATTRIBUTE9 in VARCHAR2,
527 X_ATTRIBUTE10 in VARCHAR2,
528 X_ATTRIBUTE11 in VARCHAR2,
529 X_ATTRIBUTE12 in VARCHAR2,
530 X_ATTRIBUTE13 in VARCHAR2,
531 X_ATTRIBUTE14 in VARCHAR2,
532 X_ATTRIBUTE15 in VARCHAR2,
533 X_LAST_UPDATE_DATE in DATE,
534 X_LAST_UPDATED_BY in NUMBER,
535 X_LAST_UPDATE_LOGIN in NUMBER
536 -- X_REQUEST_ID in NUMBER,
537 )
538 IS
539 l_return_status VARCHAR2(1);
540 l_msg_count NUMBER;
541 l_msg_data VARCHAR2(2000);
542 BEGIN
543
544 update MTL_CATEGORIES_B
545 set
546 STRUCTURE_ID = X_STRUCTURE_ID,
547 DISABLE_DATE = X_DISABLE_DATE,
548 WEB_STATUS = X_WEB_STATUS,
549 SUPPLIER_ENABLED_FLAG = X_SUPPLIER_ENABLED_FLAG,
550 SEGMENT1 = X_SEGMENT1,
551 SEGMENT2 = X_SEGMENT2,
552 SEGMENT3 = X_SEGMENT3,
553 SEGMENT4 = X_SEGMENT4,
554 SEGMENT5 = X_SEGMENT5,
555 SEGMENT6 = X_SEGMENT6,
556 SEGMENT7 = X_SEGMENT7,
557 SEGMENT8 = X_SEGMENT8,
558 SEGMENT9 = X_SEGMENT9,
559 SEGMENT10 = X_SEGMENT10,
560 SEGMENT11 = X_SEGMENT11,
561 SEGMENT12 = X_SEGMENT12,
562 SEGMENT13 = X_SEGMENT13,
563 SEGMENT14 = X_SEGMENT14,
564 SEGMENT15 = X_SEGMENT15,
565 SEGMENT16 = X_SEGMENT16,
566 SEGMENT17 = X_SEGMENT17,
567 SEGMENT18 = X_SEGMENT18,
568 SEGMENT19 = X_SEGMENT19,
569 SEGMENT20 = X_SEGMENT20,
570 SUMMARY_FLAG = X_SUMMARY_FLAG,
571 ENABLED_FLAG = X_ENABLED_FLAG,
572 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
573 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
574 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
575 ATTRIBUTE1 = X_ATTRIBUTE1,
576 ATTRIBUTE2 = X_ATTRIBUTE2,
577 ATTRIBUTE3 = X_ATTRIBUTE3,
578 ATTRIBUTE4 = X_ATTRIBUTE4,
579 ATTRIBUTE5 = X_ATTRIBUTE5,
580 ATTRIBUTE6 = X_ATTRIBUTE6,
581 ATTRIBUTE7 = X_ATTRIBUTE7,
582 ATTRIBUTE8 = X_ATTRIBUTE8,
583 ATTRIBUTE9 = X_ATTRIBUTE9,
584 ATTRIBUTE10 = X_ATTRIBUTE10,
585 ATTRIBUTE11 = X_ATTRIBUTE11,
586 ATTRIBUTE12 = X_ATTRIBUTE12,
587 ATTRIBUTE13 = X_ATTRIBUTE13,
588 ATTRIBUTE14 = X_ATTRIBUTE14,
589 ATTRIBUTE15 = X_ATTRIBUTE15,
590 -- WH_UPDATE_DATE = X_WH_UPDATE_DATE,
591 -- TOTAL_PROD_ID = X_TOTAL_PROD_ID,
592 -- REQUEST_ID = X_REQUEST_ID,
593 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
594 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
595 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
596 where
597 CATEGORY_ID = X_CATEGORY_ID;
598
599 if ( sql%notfound ) then
600 raise no_data_found;
601 end if;
602
603 update MTL_CATEGORIES_TL
604 set
605 DESCRIPTION = X_DESCRIPTION,
606 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
607 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
608 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
609 SOURCE_LANG = userenv('LANG')
610 where
611 CATEGORY_ID = X_CATEGORY_ID
612 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
613
614 if ( sql%notfound ) then
615 raise no_data_found;
616 end if;
617
618 --Bug: 2718703 checking for ENI product before calling their package
619 --
620 -- Sync category segments with item record in STAR.
621 --
622 IF ( INV_Item_Util.g_Appl_Inst.ENI <> 0 ) THEN
623
624 EXECUTE IMMEDIATE
625 ' BEGIN '||
626 ' ENI_ITEMS_STAR_PKG.Update_Categories '||
627 ' ( '||
628 ' p_api_version => 1.0 '||
629 ' , p_init_msg_list => FND_API.g_TRUE '||
630 ' , p_category_id => :X_CATEGORY_ID '||
631 ' , p_structure_id => :X_STRUCTURE_ID '||
632 ' , x_return_status => :l_return_status '||
633 ' , x_msg_count => :l_msg_count '||
634 ' , x_msg_data => :l_msg_data '||
635 ' ); '||
636 ' END;'
637 USING IN X_CATEGORY_ID, IN X_STRUCTURE_ID, OUT l_return_Status, OUT l_msg_count, OUT l_msg_data;
638
639 IF ( l_return_status = FND_API.g_RET_STS_ERROR ) THEN
640 FND_MESSAGE.Set_Encoded (l_msg_data);
641 APP_EXCEPTION.Raise_Exception;
642 ELSIF ( l_return_status = FND_API.g_RET_STS_UNEXP_ERROR ) THEN
643 FND_MESSAGE.Set_Encoded (l_msg_data);
644 APP_EXCEPTION.Raise_Exception;
645 END IF;
646
647 END IF;
648
649
650 --R12: Raise Business Events and Call APIs
651 BEGIN
652 INV_ITEM_EVENTS_PVT.Raise_Events(
653 p_event_name => 'EGO_WF_WRAPPER_PVT.G_CAT_CATEGORY_CHANGE_EVENT'
654 ,p_dml_type => 'UPDATE'
655 ,p_category_id => X_CATEGORY_ID);
656 EXCEPTION
657 WHEN OTHERS THEN
658 NULL;
659 END;
660
661 --Call ICX APIs
662 BEGIN
663 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
664 p_entity_type => 'CATEGORY'
665 ,p_dml_type => 'UPDATE'
666 ,p_category_id => X_CATEGORY_ID
667 ,p_structure_id => X_STRUCTURE_ID);
668 EXCEPTION
669 WHEN OTHERS THEN
670 NULL;
671 END;
672 --R12: Business Event Enhancement:
673
674 end UPDATE_ROW;
675
676
677 -- ----------------------------------------------------------------------
678 -- Deletion of categories is not supported.
679 -- ----------------------------------------------------------------------
680
681 procedure DELETE_ROW (
682 X_CATEGORY_ID in NUMBER
683 ) is
684 begin
685
686 /*
687 fnd_message.set_name('INV', 'CANNOT_DELETE_RECORD');
688 app_exception.raise_exception;
689 */
690 raise_application_error( -20000, 'CANNOT_DELETE_RECORD' );
691
692 -- This code is for future use when decided to validate
693 -- and delete categories.
694 /*
695 delete from MTL_CATEGORIES_TL
696 where CATEGORY_ID = X_CATEGORY_ID ;
697
698 if (sql%notfound) then
699 raise no_data_found;
700 end if;
701
702 delete from MTL_CATEGORIES_B
703 where CATEGORY_ID = X_CATEGORY_ID ;
704
705 if (sql%notfound) then
706 raise no_data_found;
707 end if;
708 */
709
710 end DELETE_ROW;
711
712
713 procedure ADD_LANGUAGE
714 is
715 begin
716
717 delete from MTL_CATEGORIES_TL T
718 where not exists
719 ( select NULL
720 from MTL_CATEGORIES_B B
721 where B.CATEGORY_ID = T.CATEGORY_ID
722 );
723
724 update MTL_CATEGORIES_TL T set (
725 DESCRIPTION
726 ) = ( select
727 B.DESCRIPTION
728 from MTL_CATEGORIES_TL B
729 where B.CATEGORY_ID = T.CATEGORY_ID
730 and B.LANGUAGE = T.SOURCE_LANG )
731 where (
732 T.CATEGORY_ID,
733 T.LANGUAGE
734 ) in ( select
735 SUBT.CATEGORY_ID,
736 SUBT.LANGUAGE
737 from MTL_CATEGORIES_TL SUBB,
738 MTL_CATEGORIES_TL SUBT
739 where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
740 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
741 and ( SUBB.DESCRIPTION <> SUBT.DESCRIPTION
742 or ( SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
743 or ( SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
744 );
745
746 insert into MTL_CATEGORIES_TL (
747 CREATED_BY,
748 LAST_UPDATE_LOGIN,
749 CATEGORY_ID,
750 DESCRIPTION,
751 LAST_UPDATE_DATE,
752 LAST_UPDATED_BY,
753 CREATION_DATE,
754 LANGUAGE,
755 SOURCE_LANG
756 ) select
757 B.CREATED_BY,
758 B.LAST_UPDATE_LOGIN,
759 B.CATEGORY_ID,
760 B.DESCRIPTION,
761 B.LAST_UPDATE_DATE,
762 B.LAST_UPDATED_BY,
763 B.CREATION_DATE,
764 L.LANGUAGE_CODE,
765 B.SOURCE_LANG
766 from MTL_CATEGORIES_TL B,
767 FND_LANGUAGES L
768 where L.INSTALLED_FLAG in ('I', 'B')
769 and B.LANGUAGE = userenv('LANG')
770 and not exists
771 ( select NULL
772 from MTL_CATEGORIES_TL T
773 where T.CATEGORY_ID = B.CATEGORY_ID
774 and T.LANGUAGE = L.LANGUAGE_CODE );
775
776 end ADD_LANGUAGE;
777
778
779 -- ----------------------------------------------------------------------
780 -- PROCEDURE: Translate_Row PUBLIC
781 --
782 -- PARAMETERS:
783 -- x_<developer key>
784 -- x_<translated columns>
785 -- x_owner user owning the row (SEED or other)
786 --
787 -- COMMENT:
788 -- Called from the FNDLOAD config file in 'NLS' mode to upload
789 -- translations.
790 -- ----------------------------------------------------------------------
791
792 PROCEDURE Translate_Row
793 (
794 x_category_name IN VARCHAR2
795 , x_structure_code IN VARCHAR2 --Bug 6975120
796 , x_description IN VARCHAR2
797 , x_owner IN VARCHAR2
798 , x_upload_to_functional_area IN VARCHAR2
799 , x_application_short_name IN VARCHAR2
800 )
801 IS
802 f_luby number; -- entity owner in file
803 l_category_id NUMBER;
804 l_structure_id NUMBER;
805
806 BEGIN
807 -- Translate owner to file_last_updated_by
808 f_luby := fnd_load_util.owner_id(x_owner);
809
810 -- **********************************
811 -- Get the correct structure based on the parameter passed in
812 -- upload_to_product_rpt. If it is "Y", then the structure
813 -- should be the structure of the default category set of
814 -- product reporting functional area. Else it will be the
815 -- structure of the downloaded category
816 -- **********************************
817
818 BEGIN
819
820 IF x_upload_to_functional_area <> '-1' THEN
821 SELECT B.STRUCTURE_ID
822 INTO l_structure_id
823 FROM MTL_DEFAULT_CATEGORY_SETS A,
824 MTL_CATEGORY_SETS_B B
825 WHERE FUNCTIONAL_AREA_ID = (select lookup_code from mfg_lookups
826 where lookup_type = 'MTL_FUNCTIONAL_AREAS' and upper(meaning) = upper(x_upload_to_functional_area))
827 AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
828 ELSE
829 SELECT ID_FLEX_NUM
830 INTO l_structure_id
831 FROM FND_ID_FLEX_STRUCTURES
832 WHERE APPLICATION_ID = (select application_id from fnd_application
833 where application_short_name =
834 x_application_short_name)
835 AND ID_FLEX_CODE = 'MCAT'
836 AND ID_FLEX_STRUCTURE_CODE = x_structure_code; /* Bug 6975120
837 Replacing x_structure_name with x_structure_code
838 AND LANGUAGE = 'US'; -- userenv('LANG'); Bug 6859576 */
839 END IF;
840 EXCEPTION
841 WHEN NO_DATA_FOUND THEN
842 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
843 fnd_message.set_token('ROUTINE','Category Migration');
844 IF x_upload_to_functional_area = 'Y' THEN
845 fnd_message.set_token('REASON','Default category set for ' || x_upload_to_functional_area || ' functional area does not exist');
846 ELSE
847 fnd_message.set_token('REASON','Flex structure does not exist');
848 END IF;
849 app_exception.raise_exception;
850 END;
851
852 -- find out the category_id based on the structure_id and the concat segments
853 BEGIN
854 SELECT category_id
855 INTO l_category_id
856 FROM mtl_categories_kfv
857 WHERE structure_id = l_structure_id
858 AND concatenated_segments = x_category_name;
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
862 fnd_message.set_token('ROUTINE','Category Migration');
863 fnd_message.set_token('REASON','Category does not exist');
864 app_exception.raise_exception;
865 END;
866
867 UPDATE mtl_categories_tl
868 SET description = NVL(x_description, description)
869 , last_update_date = SYSDATE
870 , last_updated_by = f_luby
871 , last_update_login = 0
872 , source_lang = userenv('LANG')
873 WHERE category_id = l_category_id
874 AND userenv('LANG') IN (language, source_lang);
875
876 IF ( SQL%NOTFOUND ) THEN
877 RAISE no_data_found;
878 END IF;
879
880 END Translate_Row;
881
882 -- ----------------------------------------------------------------------
883 -- PROCEDURE: Load_Row PUBLIC
884 --
885 -- PARAMETERS:
886 -- x_<developer key>
887 -- x_<columns>
888 -- x_owner user owning the row (SEED or other)
889 --
890 -- COMMENT:
891 -- Called from the FNDLOAD config file to upload Categories
892 -- ----------------------------------------------------------------------
893
894 PROCEDURE Load_Row
895 (
896 x_CATEGORY_NAME IN MTL_CATEGORIES_KFV.CONCATENATED_SEGMENTS%TYPE
897 ,x_STRUCTURE_CODE IN FND_ID_FLEX_STRUCTURES.ID_FLEX_STRUCTURE_CODE%TYPE --BUG 6975120
898 ,X_SEGMENT1 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
899 ,X_SEGMENT2 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
900 ,X_SEGMENT3 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
901 ,X_SEGMENT4 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
902 ,X_SEGMENT5 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
903 ,X_SEGMENT6 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
904 ,X_SEGMENT7 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
905 ,X_SEGMENT8 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
906 ,X_SEGMENT9 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
907 ,X_SEGMENT10 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
908 ,X_SEGMENT11 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
909 ,X_SEGMENT12 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
910 ,X_SEGMENT13 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
911 ,X_SEGMENT14 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
912 ,X_SEGMENT15 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
913 ,X_SEGMENT16 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
914 ,X_SEGMENT17 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
915 ,X_SEGMENT18 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
916 ,X_SEGMENT19 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
917 ,X_SEGMENT20 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
918 ,X_SUMMARY_FLAG IN MTL_CATEGORIES_B.SUMMARY_FLAG%TYPE
919 ,X_ENABLED_FLAG IN MTL_CATEGORIES_B.ENABLED_FLAG%TYPE
920 ,X_START_DATE_ACTIVE IN MTL_CATEGORIES_B.START_DATE_ACTIVE%TYPE
921 ,X_END_DATE_ACTIVE IN MTL_CATEGORIES_B.END_DATE_ACTIVE%TYPE
922 ,X_DISABLE_DATE IN MTL_CATEGORIES_B.DISABLE_DATE%TYPE
923 ,X_CATEGORY_SET_ID IN MTL_CATEGORY_SETS_B.CATEGORY_SET_ID%TYPE
924 ,X_CATEGORY_SET_NAME IN MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE
925 ,X_OWNER IN VARCHAR2
926 ,X_LAST_UPDATE_DATE IN MTL_CATEGORIES_B.LAST_UPDATE_DATE%TYPE
927 ,X_DESCRIPTION IN MTL_CATEGORIES_TL.DESCRIPTION%TYPE
928 ,X_APPLICATION_SHORT_NAME IN VARCHAR2
929 ,X_UPLOAD_TO_FUNCTIONAL_AREA IN VARCHAR2
930 ) IS
931
932 l_category_set_id MTL_CATEGORY_SETS_B.CATEGORY_SET_ID%TYPE;
933 l_structure_id MTL_CATEGORY_SETS_B.STRUCTURE_ID%TYPE;
934 l_category_id MTL_CATEGORIES_B.CATEGORY_ID%TYPE;
935 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
936 l_new_catg_id NUMBER;
937 l_category_set_name MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE := X_CATEGORY_SET_NAME ;
938 l_return_status VARCHAR2(1);
939 l_errorcode VARCHAR2(10);
940 l_msg_count NUMBER;
941 l_msg_data VARCHAR2(2000);
942 l_messages VARCHAR2(32000) :='';
943 l_segment_array FND_FLEX_EXT.SegmentArray;
944 l_n_segments NUMBER := 0 ;
945 l_delim VARCHAR2(10);
946 l_success BOOLEAN;
947 l_concat_segs VARCHAR2(2000) ;
948 l_flex_status NUMBER;
949 err_text VARCHAR2(2000);
950
951 CURSOR get_segments(l_structure_id NUMBER) is
952 SELECT application_column_name,rownum
953 FROM fnd_id_flex_segments
954 WHERE application_id = (select application_id from fnd_application
955 where application_short_name =
956 x_application_short_name)
957 AND id_flex_code = 'MCAT'
958 AND id_flex_num = l_structure_id
959 AND enabled_flag = 'Y'
960 ORDER BY segment_num ASC;
961
962 /*CURSOR get_category_id(cp_structure_id NUMBER
963 ,cp_concatenated_segs VARCHAR2) IS
964 SELECT CATEGORY_ID
965 FROM MTL_CATEGORIES_B_KFV
966 WHERE structure_id = cp_structure_id
967 AND CONCATENATED_SEGMENTS = cp_concatenated_segs;*/
968
969 begin
970
971 l_return_status := FND_API.G_RET_STS_SUCCESS;
972
973 -- **********************************
974 -- Get the correct structure based on the parameter passed in
975 -- upload_to_functional_area. If the later is passed as an argument,
976 -- then the structure should be the structure of the default
977 -- category set of the functional area passed. Else it will be the
978 -- structure of the downloaded category
979 -- **********************************
980
981 BEGIN
982
983 IF x_upload_to_functional_area <> '-1' THEN
984 SELECT A.CATEGORY_SET_ID, B.STRUCTURE_ID
985 INTO l_category_set_id, l_structure_id
986 FROM MTL_DEFAULT_CATEGORY_SETS A,
987 MTL_CATEGORY_SETS B
988 WHERE FUNCTIONAL_AREA_ID = (select lookup_code from mfg_lookups
989 where lookup_type = 'MTL_FUNCTIONAL_AREAS'
990 and upper(meaning) = upper(x_upload_to_functional_area))
991 AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
992 ELSE
993 SELECT ID_FLEX_NUM
994 INTO l_structure_id
995 FROM FND_ID_FLEX_STRUCTURES
996 WHERE APPLICATION_ID = (select application_id from fnd_application
997 where application_short_name =
998 x_application_short_name)
999 AND ID_FLEX_CODE = 'MCAT'
1000 AND ID_FLEX_STRUCTURE_CODE = x_structure_code;
1001 /*Bug 6975120 Replacing the x_structure_name with x_structure_code
1002 AND LANGUAGE = userenv('LANG'); */
1003
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN NO_DATA_FOUND THEN
1008 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
1009 fnd_message.set_token('ROUTINE','Category Migration');
1010 if x_upload_to_functional_area = '-1' then
1011 fnd_message.set_token('REASON','Default category set for ' || x_upload_to_functional_area || ' does not exist ');
1012 else
1013 fnd_message.set_token('REASON','Flex structure does not exist ');
1014 end if;
1015 app_exception.raise_exception;
1016 END;
1017
1018 -- IF (l_category_set_id IS NOT NULL)
1019 -- THEN
1020 -- Initialize the recrod
1021 l_category_rec.STRUCTURE_ID := l_structure_id ;
1022 l_category_rec.SEGMENT1 := null;
1023 l_category_rec.SEGMENT2 := null;
1024 l_category_rec.SEGMENT3 := null;
1025 l_category_rec.SEGMENT4 := null;
1026 l_category_rec.SEGMENT5 := null;
1027 l_category_rec.SEGMENT6 := null;
1028 l_category_rec.SEGMENT7 := null;
1029 l_category_rec.SEGMENT8 := null;
1030 l_category_rec.SEGMENT9 := null;
1031 l_category_rec.SEGMENT10 := null;
1032 l_category_rec.SEGMENT11 := null;
1033 l_category_rec.SEGMENT12 := null;
1034 l_category_rec.SEGMENT13 := null;
1035 l_category_rec.SEGMENT14 := null;
1036 l_category_rec.SEGMENT15 := null;
1037 l_category_rec.SEGMENT16 := null;
1038 l_category_rec.SEGMENT17 := null;
1039 l_category_rec.SEGMENT18 := null;
1040 l_category_rec.SEGMENT19 := null;
1041 l_category_rec.SEGMENT20 := null;
1042 l_category_rec.SUMMARY_FLAG := X_SUMMARY_FLAG ;
1043 l_category_rec.ENABLED_FLAG := X_ENABLED_FLAG ;
1044 l_category_rec.START_DATE_ACTIVE := X_START_DATE_ACTIVE ;
1045 l_category_rec.END_DATE_ACTIVE := X_END_DATE_ACTIVE ;
1046 l_category_rec.DISABLE_DATE := X_DISABLE_DATE ;
1047 l_category_rec.DESCRIPTION := X_DESCRIPTION ;
1048
1049
1050 -- Looping through the enabled segments in the target instance
1051 -- and setting the values for only those segments those are enabled
1052
1053 FOR c_segments in get_segments(l_structure_id) LOOP
1054 l_n_segments := c_segments.rownum;
1055 IF c_segments.application_column_name = 'SEGMENT1' THEN
1056 l_category_rec.SEGMENT1 := X_SEGMENT1;
1057 l_segment_array(c_segments.rownum):= X_SEGMENT1;
1058 ELSIF c_segments.application_column_name = 'SEGMENT2' THEN
1059 l_category_rec.SEGMENT2 := X_SEGMENT2;
1060 l_segment_array(c_segments.rownum):= X_SEGMENT2;
1061 ELSIF c_segments.application_column_name = 'SEGMENT3' THEN
1062 l_category_rec.SEGMENT3 := X_SEGMENT3;
1063 l_segment_array(c_segments.rownum):= X_SEGMENT3;
1064 ELSIF c_segments.application_column_name = 'SEGMENT4' THEN
1065 l_category_rec.SEGMENT4 := X_SEGMENT4;
1066 l_segment_array(c_segments.rownum):= X_SEGMENT4;
1067 ELSIF c_segments.application_column_name = 'SEGMENT5' THEN
1068 l_category_rec.SEGMENT5 := X_SEGMENT5;
1069 l_segment_array(c_segments.rownum):= X_SEGMENT5;
1070 ELSIF c_segments.application_column_name = 'SEGMENT6' THEN
1071 l_category_rec.SEGMENT6 := X_SEGMENT6;
1072 l_segment_array(c_segments.rownum):= X_SEGMENT6;
1073 ELSIF c_segments.application_column_name = 'SEGMENT7' THEN
1074 l_category_rec.SEGMENT7 := X_SEGMENT7;
1075 l_segment_array(c_segments.rownum):= X_SEGMENT7;
1076 ELSIF c_segments.application_column_name = 'SEGMENT8' THEN
1077 l_category_rec.SEGMENT8 := X_SEGMENT8;
1078 l_segment_array(c_segments.rownum):= X_SEGMENT8;
1079 ELSIF c_segments.application_column_name = 'SEGMENT9' THEN
1080 l_category_rec.SEGMENT9 := X_SEGMENT9;
1081 l_segment_array(c_segments.rownum):= X_SEGMENT9;
1082 ELSIF c_segments.application_column_name = 'SEGMENT10' THEN
1083 l_category_rec.SEGMENT10 := X_SEGMENT10;
1084 l_segment_array(c_segments.rownum):= X_SEGMENT10;
1085 ELSIF c_segments.application_column_name = 'SEGMENT11' THEN
1086 l_category_rec.SEGMENT11 := X_SEGMENT11;
1087 l_segment_array(c_segments.rownum):= X_SEGMENT11;
1088 ELSIF c_segments.application_column_name = 'SEGMENT12' THEN
1089 l_category_rec.SEGMENT12 := X_SEGMENT12;
1090 l_segment_array(c_segments.rownum):= X_SEGMENT12;
1091 ELSIF c_segments.application_column_name = 'SEGMENT13' THEN
1092 l_category_rec.SEGMENT13 := X_SEGMENT13;
1093 l_segment_array(c_segments.rownum):= X_SEGMENT13;
1094 ELSIF c_segments.application_column_name = 'SEGMENT14' THEN
1095 l_category_rec.SEGMENT14 := X_SEGMENT14;
1096 l_segment_array(c_segments.rownum):= X_SEGMENT14;
1097 ELSIF c_segments.application_column_name = 'SEGMENT15' THEN
1098 l_category_rec.SEGMENT15 := X_SEGMENT15;
1099 l_segment_array(c_segments.rownum):= X_SEGMENT15;
1100 ELSIF c_segments.application_column_name = 'SEGMENT16' THEN
1101 l_category_rec.SEGMENT16 := X_SEGMENT16;
1102 l_segment_array(c_segments.rownum):= X_SEGMENT16;
1103 ELSIF c_segments.application_column_name = 'SEGMENT17' THEN
1104 l_category_rec.SEGMENT17 := X_SEGMENT17;
1105 l_segment_array(c_segments.rownum):= X_SEGMENT17;
1106 ELSIF c_segments.application_column_name = 'SEGMENT18' THEN
1107 l_category_rec.SEGMENT18 := X_SEGMENT18;
1108 l_segment_array(c_segments.rownum):= X_SEGMENT18;
1109 ELSIF c_segments.application_column_name = 'SEGMENT19' THEN
1110 l_category_rec.SEGMENT19 := X_SEGMENT19;
1111 l_segment_array(c_segments.rownum):= X_SEGMENT19;
1112 ELSIF c_segments.application_column_name = 'SEGMENT20' THEN
1113 l_category_rec.SEGMENT20 := X_SEGMENT20;
1114 l_segment_array(c_segments.rownum):= X_SEGMENT20;
1115 END IF;
1116 END LOOP; -- loop to get all the enabled segments in the target inst.
1117
1118 l_delim := fnd_flex_ext.get_delimiter('INV','MCAT',l_structure_id);
1119
1120 l_concat_segs := fnd_flex_ext.concatenate_segments(l_n_segments,
1121 l_segment_array,
1122 l_delim);
1123 l_success := fnd_flex_keyval.validate_segs(
1124 operation => 'FIND_COMBINATION',
1125 appl_short_name => 'INV',
1126 key_flex_code => 'MCAT',
1127 structure_number => l_structure_id,
1128 concat_segments => l_concat_segs);
1129
1130 IF (NOT l_success ) THEN
1131
1132 -- First check if the category is disabled as of sysdate
1133 -- If it is, then ignore creating the category
1134 IF ((X_DISABLE_DATE is null OR X_DISABLE_DATE <> '') OR
1135 (X_DISABLE_DATE is not null AND X_DISABLE_DATE > SYSDATE)) THEN
1136
1137 -- Create a Category record
1138 INV_ITEM_CATEGORY_PUB.Create_Category (
1139 P_API_VERSION => 1.0,
1140 P_INIT_MSG_LIST => FND_API.G_FALSE,
1141 P_COMMIT => FND_API.G_FALSE,
1142 X_RETURN_STATUS => l_return_status ,
1143 X_ERRORCODE => l_errorcode,
1144 X_MSG_COUNT => l_msg_count ,
1145 X_MSG_DATA => l_msg_data ,
1146 P_CATEGORY_REC => l_category_rec,
1147 X_CATEGORY_ID => l_new_catg_id ) ;
1148
1149 ELSE
1150 FND_MESSAGE.SET_NAME('FND','GENERIC-INTERNAL ERROR');
1151 FND_MESSAGE.SET_TOKEN('ROUTINE','Category Migration');
1152 FND_MESSAGE.SET_TOKEN('REASON','Disabled category cannot be created');
1153 END IF; -- IF (DISABLE_DATE > SYSDATE) THEN
1154
1155 ELSE
1156
1157 --Bug 7659277
1158 --There is a chance that mtl_categories_b_kfv is not
1159 --prepared before this code runs.
1160 --hence using INVPUOPI.mtl_pr_parse_flex_name to get
1161 --the category id.
1162 /*OPEN get_category_id(l_structure_id,l_concat_segs);
1163 FETCH get_category_id INTO l_category_id;
1164 CLOSE get_category_id;*/
1165 l_flex_status := INVPUOPI.mtl_pr_parse_flex_name (
1166 0,
1167 'MCAT',
1168 l_concat_segs,
1169 l_category_id,
1170 X_CATEGORY_SET_ID,
1171 err_text,
1172 l_structure_id); /*Added l_structure_id for bug 8288281*/
1173 IF(l_flex_status <> 0)
1174 THEN
1175 FND_MESSAGE.SET_NAME('FND','GENERIC-INTERNAL ERROR');
1176 FND_MESSAGE.SET_TOKEN('ROUTINE','Category Migration');
1177 FND_MESSAGE.SET_TOKEN('REASON','Category to be updated not found.');
1178 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1179 END IF;
1180
1181
1182 l_category_rec.CATEGORY_ID := l_category_id ;
1183
1184 INV_ITEM_CATEGORY_PUB.Update_Category (
1185 P_API_VERSION => 1.0,
1186 P_INIT_MSG_LIST => FND_API.G_FALSE,
1187 P_COMMIT => FND_API.G_FALSE,
1188 X_RETURN_STATUS => l_return_status,
1189 X_ERRORCODE => l_errorcode,
1190 X_MSG_COUNT => l_msg_count,
1191 X_MSG_DATA => l_msg_data,
1192 P_CATEGORY_REC => l_category_rec );
1193
1194 END IF;
1195 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1196 THEN
1197 FND_MSG_PUB.COUNT_AND_GET (
1198 p_encoded => 'F'
1199 , p_count => l_msg_count
1200 , p_data => l_msg_data);
1201 FOR K IN 1 .. l_msg_count LOOP
1202 l_messages := l_messages || fnd_msg_pub.get( p_msg_index => k, p_encoded => 'F') || ';';
1203 END LOOP;
1204 FND_MESSAGE.SET_NAME('FND','GENERIC-INTERNAL ERROR');
1205 FND_MESSAGE.SET_TOKEN('ROUTINE','Category Migration');
1206 FND_MESSAGE.SET_TOKEN('REASON',l_messages);
1207 APP_EXCEPTION.RAISE_EXCEPTION;
1208 END IF;
1209 -- END IF;
1210 END Load_Row;
1211
1212 end MTL_CATEGORIES_PKG;