[Home] [Help]
PACKAGE BODY: APPS.MTL_CATEGORIES_PKG
Source
1 package body MTL_CATEGORIES_PKG as
2 /* $Header: INVICAHB.pls 120.15.12020000.2 2012/08/22 01:56:45 zewhuang 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 ,p_commit => true); -- @ for bug 14248843
669 EXCEPTION
670 WHEN OTHERS THEN
671 NULL;
672 END;
673 --R12: Business Event Enhancement:
674
675 end UPDATE_ROW;
676
677
678 -- ----------------------------------------------------------------------
679 -- Deletion of categories is not supported.
680 -- ----------------------------------------------------------------------
681
682 procedure DELETE_ROW (
683 X_CATEGORY_ID in NUMBER
684 ) is
685 begin
686
687 /*
688 fnd_message.set_name('INV', 'CANNOT_DELETE_RECORD');
689 app_exception.raise_exception;
690 */
691 raise_application_error( -20000, 'CANNOT_DELETE_RECORD' );
692
693 -- This code is for future use when decided to validate
694 -- and delete categories.
695 /*
696 delete from MTL_CATEGORIES_TL
697 where CATEGORY_ID = X_CATEGORY_ID ;
698
699 if (sql%notfound) then
700 raise no_data_found;
701 end if;
702
703 delete from MTL_CATEGORIES_B
704 where CATEGORY_ID = X_CATEGORY_ID ;
705
706 if (sql%notfound) then
707 raise no_data_found;
708 end if;
709 */
710
711 end DELETE_ROW;
712
713
714 procedure ADD_LANGUAGE
715 is
716 begin
717
718 delete from MTL_CATEGORIES_TL T
719 where not exists
720 ( select NULL
721 from MTL_CATEGORIES_B B
722 where B.CATEGORY_ID = T.CATEGORY_ID
723 );
724
725 update MTL_CATEGORIES_TL T set (
726 DESCRIPTION
727 ) = ( select
728 B.DESCRIPTION
729 from MTL_CATEGORIES_TL B
730 where B.CATEGORY_ID = T.CATEGORY_ID
731 and B.LANGUAGE = T.SOURCE_LANG )
732 where (
733 T.CATEGORY_ID,
734 T.LANGUAGE
735 ) in ( select
736 SUBT.CATEGORY_ID,
737 SUBT.LANGUAGE
738 from MTL_CATEGORIES_TL SUBB,
739 MTL_CATEGORIES_TL SUBT
740 where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
741 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
742 and ( SUBB.DESCRIPTION <> SUBT.DESCRIPTION
743 or ( SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
744 or ( SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
745 );
746
747 insert into MTL_CATEGORIES_TL (
748 CREATED_BY,
749 LAST_UPDATE_LOGIN,
750 CATEGORY_ID,
751 DESCRIPTION,
752 LAST_UPDATE_DATE,
753 LAST_UPDATED_BY,
754 CREATION_DATE,
755 LANGUAGE,
756 SOURCE_LANG
757 ) select
758 B.CREATED_BY,
759 B.LAST_UPDATE_LOGIN,
760 B.CATEGORY_ID,
761 B.DESCRIPTION,
762 B.LAST_UPDATE_DATE,
763 B.LAST_UPDATED_BY,
764 B.CREATION_DATE,
765 L.LANGUAGE_CODE,
766 B.SOURCE_LANG
767 from MTL_CATEGORIES_TL B,
768 FND_LANGUAGES L
769 where L.INSTALLED_FLAG in ('I', 'B')
770 and B.LANGUAGE = userenv('LANG')
771 and not exists
772 ( select NULL
773 from MTL_CATEGORIES_TL T
774 where T.CATEGORY_ID = B.CATEGORY_ID
775 and T.LANGUAGE = L.LANGUAGE_CODE );
776
777 end ADD_LANGUAGE;
778
779
780 -- ----------------------------------------------------------------------
781 -- PROCEDURE: Translate_Row PUBLIC
782 --
783 -- PARAMETERS:
784 -- x_<developer key>
785 -- x_<translated columns>
786 -- x_owner user owning the row (SEED or other)
787 --
788 -- COMMENT:
789 -- Called from the FNDLOAD config file in 'NLS' mode to upload
790 -- translations.
791 -- ----------------------------------------------------------------------
792
793 PROCEDURE Translate_Row
794 (
795 x_category_name IN VARCHAR2
796 , x_structure_code IN VARCHAR2 --Bug 6975120
797 , x_description IN VARCHAR2
798 , x_owner IN VARCHAR2
799 , x_upload_to_functional_area IN VARCHAR2
800 , x_application_short_name IN VARCHAR2
801 )
802 IS
803 f_luby number; -- entity owner in file
804 l_category_id NUMBER;
805 l_structure_id NUMBER;
806
807 BEGIN
808 -- Translate owner to file_last_updated_by
809 f_luby := fnd_load_util.owner_id(x_owner);
810
811 -- **********************************
812 -- Get the correct structure based on the parameter passed in
813 -- upload_to_product_rpt. If it is "Y", then the structure
814 -- should be the structure of the default category set of
815 -- product reporting functional area. Else it will be the
816 -- structure of the downloaded category
817 -- **********************************
818
819 BEGIN
820
821 IF x_upload_to_functional_area <> '-1' THEN
822 SELECT B.STRUCTURE_ID
823 INTO l_structure_id
824 FROM MTL_DEFAULT_CATEGORY_SETS A,
825 MTL_CATEGORY_SETS_B B
826 WHERE FUNCTIONAL_AREA_ID = (select lookup_code from mfg_lookups
827 where lookup_type = 'MTL_FUNCTIONAL_AREAS' and upper(meaning) = upper(x_upload_to_functional_area))
828 AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
829 ELSE
830 SELECT ID_FLEX_NUM
831 INTO l_structure_id
832 FROM FND_ID_FLEX_STRUCTURES
833 WHERE APPLICATION_ID = (select application_id from fnd_application
834 where application_short_name =
835 x_application_short_name)
836 AND ID_FLEX_CODE = 'MCAT'
837 AND ID_FLEX_STRUCTURE_CODE = x_structure_code; /* Bug 6975120
838 Replacing x_structure_name with x_structure_code
839 AND LANGUAGE = 'US'; -- userenv('LANG'); Bug 6859576 */
840 END IF;
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN
843 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
844 fnd_message.set_token('ROUTINE','Category Migration');
845 IF x_upload_to_functional_area = 'Y' THEN
846 fnd_message.set_token('REASON','Default category set for ' || x_upload_to_functional_area || ' functional area does not exist');
847 ELSE
848 fnd_message.set_token('REASON','Flex structure does not exist');
849 END IF;
850 app_exception.raise_exception;
851 END;
852
853 -- find out the category_id based on the structure_id and the concat segments
854 BEGIN
855 SELECT category_id
856 INTO l_category_id
857 FROM mtl_categories_kfv
858 WHERE structure_id = l_structure_id
859 AND concatenated_segments = x_category_name;
860 EXCEPTION
861 WHEN NO_DATA_FOUND THEN
862 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
863 fnd_message.set_token('ROUTINE','Category Migration');
864 fnd_message.set_token('REASON','Category does not exist');
865 app_exception.raise_exception;
866 END;
867
868 UPDATE mtl_categories_tl
869 SET description = NVL(x_description, description)
870 , last_update_date = SYSDATE
871 , last_updated_by = f_luby
872 , last_update_login = 0
873 , source_lang = userenv('LANG')
874 WHERE category_id = l_category_id
875 AND userenv('LANG') IN (language, source_lang);
876
877 IF ( SQL%NOTFOUND ) THEN
878 RAISE no_data_found;
879 END IF;
880
881 END Translate_Row;
882
883 -- ----------------------------------------------------------------------
884 -- PROCEDURE: Load_Row PUBLIC
885 --
886 -- PARAMETERS:
887 -- x_<developer key>
888 -- x_<columns>
889 -- x_owner user owning the row (SEED or other)
890 --
891 -- COMMENT:
892 -- Called from the FNDLOAD config file to upload Categories
893 -- ----------------------------------------------------------------------
894
895 PROCEDURE Load_Row
896 (
897 x_CATEGORY_NAME IN MTL_CATEGORIES_KFV.CONCATENATED_SEGMENTS%TYPE
898 ,x_STRUCTURE_CODE IN FND_ID_FLEX_STRUCTURES.ID_FLEX_STRUCTURE_CODE%TYPE --BUG 6975120
899 ,X_SEGMENT1 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
900 ,X_SEGMENT2 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
901 ,X_SEGMENT3 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
902 ,X_SEGMENT4 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
903 ,X_SEGMENT5 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
904 ,X_SEGMENT6 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
905 ,X_SEGMENT7 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
906 ,X_SEGMENT8 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
907 ,X_SEGMENT9 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
908 ,X_SEGMENT10 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
909 ,X_SEGMENT11 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
910 ,X_SEGMENT12 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
911 ,X_SEGMENT13 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
912 ,X_SEGMENT14 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
913 ,X_SEGMENT15 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
914 ,X_SEGMENT16 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
915 ,X_SEGMENT17 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
916 ,X_SEGMENT18 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
917 ,X_SEGMENT19 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
918 ,X_SEGMENT20 IN MTL_CATEGORIES_B.SEGMENT1%TYPE
919 ,X_SUMMARY_FLAG IN MTL_CATEGORIES_B.SUMMARY_FLAG%TYPE
920 ,X_ENABLED_FLAG IN MTL_CATEGORIES_B.ENABLED_FLAG%TYPE
921 ,X_START_DATE_ACTIVE IN MTL_CATEGORIES_B.START_DATE_ACTIVE%TYPE
922 ,X_END_DATE_ACTIVE IN MTL_CATEGORIES_B.END_DATE_ACTIVE%TYPE
923 ,X_DISABLE_DATE IN MTL_CATEGORIES_B.DISABLE_DATE%TYPE
924 ,X_CATEGORY_SET_ID IN MTL_CATEGORY_SETS_B.CATEGORY_SET_ID%TYPE
925 ,X_CATEGORY_SET_NAME IN MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE
926 ,X_OWNER IN VARCHAR2
927 ,X_LAST_UPDATE_DATE IN MTL_CATEGORIES_B.LAST_UPDATE_DATE%TYPE
928 ,X_DESCRIPTION IN MTL_CATEGORIES_TL.DESCRIPTION%TYPE
929 ,X_APPLICATION_SHORT_NAME IN VARCHAR2
930 ,X_UPLOAD_TO_FUNCTIONAL_AREA IN VARCHAR2
931 ) IS
932
933 l_category_set_id MTL_CATEGORY_SETS_B.CATEGORY_SET_ID%TYPE;
934 l_structure_id MTL_CATEGORY_SETS_B.STRUCTURE_ID%TYPE;
935 l_category_id MTL_CATEGORIES_B.CATEGORY_ID%TYPE;
936 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
937 l_new_catg_id NUMBER;
938 l_category_set_name MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE := X_CATEGORY_SET_NAME ;
939 l_return_status VARCHAR2(1);
940 l_errorcode VARCHAR2(10);
941 l_msg_count NUMBER;
942 l_msg_data VARCHAR2(2000);
943 l_messages VARCHAR2(32000) :='';
944 l_segment_array FND_FLEX_EXT.SegmentArray;
945 l_n_segments NUMBER := 0 ;
946 l_delim VARCHAR2(10);
947 l_success BOOLEAN;
948 l_concat_segs VARCHAR2(2000) ;
949 l_flex_status NUMBER;
950 err_text VARCHAR2(2000);
951
952 CURSOR get_segments(l_structure_id NUMBER) is
953 SELECT application_column_name,rownum
954 FROM fnd_id_flex_segments
955 WHERE application_id = (select application_id from fnd_application
956 where application_short_name =
957 x_application_short_name)
958 AND id_flex_code = 'MCAT'
959 AND id_flex_num = l_structure_id
960 AND enabled_flag = 'Y'
961 ORDER BY segment_num ASC;
962
963 /*CURSOR get_category_id(cp_structure_id NUMBER
964 ,cp_concatenated_segs VARCHAR2) IS
965 SELECT CATEGORY_ID
966 FROM MTL_CATEGORIES_B_KFV
967 WHERE structure_id = cp_structure_id
968 AND CONCATENATED_SEGMENTS = cp_concatenated_segs;*/
969
970 begin
971
972 l_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 -- **********************************
975 -- Get the correct structure based on the parameter passed in
976 -- upload_to_functional_area. If the later is passed as an argument,
977 -- then the structure should be the structure of the default
978 -- category set of the functional area passed. Else it will be the
979 -- structure of the downloaded category
980 -- **********************************
981
982 BEGIN
983
984 IF x_upload_to_functional_area <> '-1' THEN
985 SELECT A.CATEGORY_SET_ID, B.STRUCTURE_ID
986 INTO l_category_set_id, l_structure_id
987 FROM MTL_DEFAULT_CATEGORY_SETS A,
988 MTL_CATEGORY_SETS B
989 WHERE FUNCTIONAL_AREA_ID = (select lookup_code from mfg_lookups
990 where lookup_type = 'MTL_FUNCTIONAL_AREAS'
991 and upper(meaning) = upper(x_upload_to_functional_area))
992 AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
993 ELSE
994 SELECT ID_FLEX_NUM
995 INTO l_structure_id
996 FROM FND_ID_FLEX_STRUCTURES
997 WHERE APPLICATION_ID = (select application_id from fnd_application
998 where application_short_name =
999 x_application_short_name)
1000 AND ID_FLEX_CODE = 'MCAT'
1001 AND ID_FLEX_STRUCTURE_CODE = x_structure_code;
1002 /*Bug 6975120 Replacing the x_structure_name with x_structure_code
1003 AND LANGUAGE = userenv('LANG'); */
1004
1005 END IF;
1006
1007 EXCEPTION
1008 WHEN NO_DATA_FOUND THEN
1009 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
1010 fnd_message.set_token('ROUTINE','Category Migration');
1011 if x_upload_to_functional_area = '-1' then
1012 fnd_message.set_token('REASON','Default category set for ' || x_upload_to_functional_area || ' does not exist ');
1013 else
1014 fnd_message.set_token('REASON','Flex structure does not exist ');
1015 end if;
1016 app_exception.raise_exception;
1017 END;
1018
1019 -- IF (l_category_set_id IS NOT NULL)
1020 -- THEN
1021 -- Initialize the recrod
1022 l_category_rec.STRUCTURE_ID := l_structure_id ;
1023 l_category_rec.SEGMENT1 := null;
1024 l_category_rec.SEGMENT2 := null;
1025 l_category_rec.SEGMENT3 := null;
1026 l_category_rec.SEGMENT4 := null;
1027 l_category_rec.SEGMENT5 := null;
1028 l_category_rec.SEGMENT6 := null;
1029 l_category_rec.SEGMENT7 := null;
1030 l_category_rec.SEGMENT8 := null;
1031 l_category_rec.SEGMENT9 := null;
1032 l_category_rec.SEGMENT10 := null;
1033 l_category_rec.SEGMENT11 := null;
1034 l_category_rec.SEGMENT12 := null;
1035 l_category_rec.SEGMENT13 := null;
1036 l_category_rec.SEGMENT14 := null;
1037 l_category_rec.SEGMENT15 := null;
1038 l_category_rec.SEGMENT16 := null;
1039 l_category_rec.SEGMENT17 := null;
1040 l_category_rec.SEGMENT18 := null;
1041 l_category_rec.SEGMENT19 := null;
1042 l_category_rec.SEGMENT20 := null;
1043 l_category_rec.SUMMARY_FLAG := X_SUMMARY_FLAG ;
1044 l_category_rec.ENABLED_FLAG := X_ENABLED_FLAG ;
1045 l_category_rec.START_DATE_ACTIVE := X_START_DATE_ACTIVE ;
1046 l_category_rec.END_DATE_ACTIVE := X_END_DATE_ACTIVE ;
1047 l_category_rec.DISABLE_DATE := X_DISABLE_DATE ;
1048 l_category_rec.DESCRIPTION := X_DESCRIPTION ;
1049
1050 l_category_rec.WEB_STATUS := null; -- Bug #8463906, to avoid saving chr(0) into web_status
1051 l_category_rec.SUPPLIER_ENABLED_FLAG := null; -- Bug #8463906, to avoid saving chr(0) into SUPPLIER_ENABLED_FLAG
1052
1053 -- Looping through the enabled segments in the target instance
1054 -- and setting the values for only those segments those are enabled
1055
1056 FOR c_segments in get_segments(l_structure_id) LOOP
1057 l_n_segments := c_segments.rownum;
1058 IF c_segments.application_column_name = 'SEGMENT1' THEN
1059 l_category_rec.SEGMENT1 := X_SEGMENT1;
1060 l_segment_array(c_segments.rownum):= X_SEGMENT1;
1061 ELSIF c_segments.application_column_name = 'SEGMENT2' THEN
1062 l_category_rec.SEGMENT2 := X_SEGMENT2;
1063 l_segment_array(c_segments.rownum):= X_SEGMENT2;
1064 ELSIF c_segments.application_column_name = 'SEGMENT3' THEN
1065 l_category_rec.SEGMENT3 := X_SEGMENT3;
1066 l_segment_array(c_segments.rownum):= X_SEGMENT3;
1067 ELSIF c_segments.application_column_name = 'SEGMENT4' THEN
1068 l_category_rec.SEGMENT4 := X_SEGMENT4;
1069 l_segment_array(c_segments.rownum):= X_SEGMENT4;
1070 ELSIF c_segments.application_column_name = 'SEGMENT5' THEN
1071 l_category_rec.SEGMENT5 := X_SEGMENT5;
1072 l_segment_array(c_segments.rownum):= X_SEGMENT5;
1073 ELSIF c_segments.application_column_name = 'SEGMENT6' THEN
1074 l_category_rec.SEGMENT6 := X_SEGMENT6;
1075 l_segment_array(c_segments.rownum):= X_SEGMENT6;
1076 ELSIF c_segments.application_column_name = 'SEGMENT7' THEN
1077 l_category_rec.SEGMENT7 := X_SEGMENT7;
1078 l_segment_array(c_segments.rownum):= X_SEGMENT7;
1079 ELSIF c_segments.application_column_name = 'SEGMENT8' THEN
1080 l_category_rec.SEGMENT8 := X_SEGMENT8;
1081 l_segment_array(c_segments.rownum):= X_SEGMENT8;
1082 ELSIF c_segments.application_column_name = 'SEGMENT9' THEN
1083 l_category_rec.SEGMENT9 := X_SEGMENT9;
1084 l_segment_array(c_segments.rownum):= X_SEGMENT9;
1085 ELSIF c_segments.application_column_name = 'SEGMENT10' THEN
1086 l_category_rec.SEGMENT10 := X_SEGMENT10;
1087 l_segment_array(c_segments.rownum):= X_SEGMENT10;
1088 ELSIF c_segments.application_column_name = 'SEGMENT11' THEN
1089 l_category_rec.SEGMENT11 := X_SEGMENT11;
1090 l_segment_array(c_segments.rownum):= X_SEGMENT11;
1091 ELSIF c_segments.application_column_name = 'SEGMENT12' THEN
1092 l_category_rec.SEGMENT12 := X_SEGMENT12;
1093 l_segment_array(c_segments.rownum):= X_SEGMENT12;
1094 ELSIF c_segments.application_column_name = 'SEGMENT13' THEN
1095 l_category_rec.SEGMENT13 := X_SEGMENT13;
1096 l_segment_array(c_segments.rownum):= X_SEGMENT13;
1097 ELSIF c_segments.application_column_name = 'SEGMENT14' THEN
1098 l_category_rec.SEGMENT14 := X_SEGMENT14;
1099 l_segment_array(c_segments.rownum):= X_SEGMENT14;
1100 ELSIF c_segments.application_column_name = 'SEGMENT15' THEN
1101 l_category_rec.SEGMENT15 := X_SEGMENT15;
1102 l_segment_array(c_segments.rownum):= X_SEGMENT15;
1103 ELSIF c_segments.application_column_name = 'SEGMENT16' THEN
1104 l_category_rec.SEGMENT16 := X_SEGMENT16;
1105 l_segment_array(c_segments.rownum):= X_SEGMENT16;
1106 ELSIF c_segments.application_column_name = 'SEGMENT17' THEN
1107 l_category_rec.SEGMENT17 := X_SEGMENT17;
1108 l_segment_array(c_segments.rownum):= X_SEGMENT17;
1109 ELSIF c_segments.application_column_name = 'SEGMENT18' THEN
1110 l_category_rec.SEGMENT18 := X_SEGMENT18;
1111 l_segment_array(c_segments.rownum):= X_SEGMENT18;
1112 ELSIF c_segments.application_column_name = 'SEGMENT19' THEN
1113 l_category_rec.SEGMENT19 := X_SEGMENT19;
1114 l_segment_array(c_segments.rownum):= X_SEGMENT19;
1115 ELSIF c_segments.application_column_name = 'SEGMENT20' THEN
1116 l_category_rec.SEGMENT20 := X_SEGMENT20;
1117 l_segment_array(c_segments.rownum):= X_SEGMENT20;
1118 END IF;
1119 END LOOP; -- loop to get all the enabled segments in the target inst.
1120
1121 l_delim := fnd_flex_ext.get_delimiter('INV','MCAT',l_structure_id);
1122
1123 l_concat_segs := fnd_flex_ext.concatenate_segments(l_n_segments,
1124 l_segment_array,
1125 l_delim);
1126 l_success := fnd_flex_keyval.validate_segs(
1127 operation => 'FIND_COMBINATION',
1128 appl_short_name => 'INV',
1129 key_flex_code => 'MCAT',
1130 structure_number => l_structure_id,
1131 concat_segments => l_concat_segs);
1132
1133 IF (NOT l_success ) THEN
1134
1135 -- First check if the category is disabled as of sysdate
1136 -- If it is, then ignore creating the category
1137 IF ((X_DISABLE_DATE is null OR X_DISABLE_DATE <> '') OR
1138 (X_DISABLE_DATE is not null AND X_DISABLE_DATE > SYSDATE)) THEN
1139
1140 -- Create a Category record
1141 INV_ITEM_CATEGORY_PUB.Create_Category (
1142 P_API_VERSION => 1.0,
1143 P_INIT_MSG_LIST => FND_API.G_FALSE,
1144 P_COMMIT => FND_API.G_FALSE,
1145 X_RETURN_STATUS => l_return_status ,
1146 X_ERRORCODE => l_errorcode,
1147 X_MSG_COUNT => l_msg_count ,
1148 X_MSG_DATA => l_msg_data ,
1149 P_CATEGORY_REC => l_category_rec,
1150 X_CATEGORY_ID => l_new_catg_id ) ;
1151
1152 ELSE
1153 FND_MESSAGE.SET_NAME('FND','GENERIC-INTERNAL ERROR');
1154 FND_MESSAGE.SET_TOKEN('ROUTINE','Category Migration');
1155 FND_MESSAGE.SET_TOKEN('REASON','Disabled category cannot be created');
1156 END IF; -- IF (DISABLE_DATE > SYSDATE) THEN
1157
1158 ELSE
1159
1160 --Bug 7659277
1161 --There is a chance that mtl_categories_b_kfv is not
1162 --prepared before this code runs.
1163 --hence using INVPUOPI.mtl_pr_parse_flex_name to get
1164 --the category id.
1165 /*OPEN get_category_id(l_structure_id,l_concat_segs);
1166 FETCH get_category_id INTO l_category_id;
1167 CLOSE get_category_id;*/
1168 l_flex_status := INVPUOPI.mtl_pr_parse_flex_name (
1169 0,
1170 'MCAT',
1171 l_concat_segs,
1172 l_category_id,
1173 X_CATEGORY_SET_ID,
1174 err_text,
1175 l_structure_id); /*Added l_structure_id for bug 8288281*/
1176 IF(l_flex_status <> 0)
1177 THEN
1178 FND_MESSAGE.SET_NAME('FND','GENERIC-INTERNAL ERROR');
1179 FND_MESSAGE.SET_TOKEN('ROUTINE','Category Migration');
1180 FND_MESSAGE.SET_TOKEN('REASON','Category to be updated not found.');
1181 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1182 END IF;
1183
1184
1185 l_category_rec.CATEGORY_ID := l_category_id ;
1186
1187 INV_ITEM_CATEGORY_PUB.Update_Category (
1188 P_API_VERSION => 1.0,
1189 P_INIT_MSG_LIST => FND_API.G_FALSE,
1190 P_COMMIT => FND_API.G_FALSE,
1191 X_RETURN_STATUS => l_return_status,
1192 X_ERRORCODE => l_errorcode,
1193 X_MSG_COUNT => l_msg_count,
1194 X_MSG_DATA => l_msg_data,
1195 P_CATEGORY_REC => l_category_rec );
1196
1197 END IF;
1198 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1199 THEN
1200 FND_MSG_PUB.COUNT_AND_GET (
1201 p_encoded => 'F'
1202 , p_count => l_msg_count
1203 , p_data => l_msg_data);
1204 FOR K IN 1 .. l_msg_count LOOP
1205 l_messages := l_messages || fnd_msg_pub.get( p_msg_index => k, p_encoded => 'F') || ';';
1206 END LOOP;
1207 FND_MESSAGE.SET_NAME('FND','GENERIC-INTERNAL ERROR');
1208 FND_MESSAGE.SET_TOKEN('ROUTINE','Category Migration');
1209 FND_MESSAGE.SET_TOKEN('REASON',l_messages);
1210 APP_EXCEPTION.RAISE_EXCEPTION;
1211 END IF;
1212 -- END IF;
1213 END Load_Row;
1214
1215 end MTL_CATEGORIES_PKG;