[Home] [Help]
PACKAGE BODY: APPS.MTL_CATEGORY_SETS_PKG
Source
1 package body MTL_CATEGORY_SETS_PKG as
2 /* $Header: INVICSHB.pls 120.4 2006/06/05 12:08:59 lparihar ship $ */
3
4 -- ----------------------------------------------------------------------
5 -- PROCEDURE: Insert_Row
6 -- ----------------------------------------------------------------------
7
8 procedure INSERT_ROW (
9 X_ROWID in out NOCOPY VARCHAR2,
10 X_CATEGORY_SET_ID in NUMBER,
11 X_CATEGORY_SET_NAME in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_STRUCTURE_ID in NUMBER,
14 X_VALIDATE_FLAG in VARCHAR2,
15 X_MULT_ITEM_CAT_ASSIGN_FLAG IN VARCHAR2,
16 X_CONTROL_LEVEL_UPDT_FLAG IN VARCHAR2 DEFAULT NULL,
17 X_MULT_ITEM_CAT_UPDT_FLAG IN VARCHAR2 DEFAULT NULL,
18 X_VALIDATE_FLAG_UPDT_FLAG IN VARCHAR2 DEFAULT NULL,
19 X_HIERARCHY_ENABLED IN VARCHAR2 DEFAULT NULL,
20 X_CONTROL_LEVEL in NUMBER,
21 X_DEFAULT_CATEGORY_ID in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_LOGIN in NUMBER
27 -- X_REQUEST_ID in NUMBER,
28 ) is
29
30 cursor C is
31 select ROWID
32 from MTL_CATEGORY_SETS_B
33 where CATEGORY_SET_ID = X_CATEGORY_SET_ID ;
34
35 begin
36
37 insert into MTL_CATEGORY_SETS_B (
38 CATEGORY_SET_ID,
39 STRUCTURE_ID,
40 VALIDATE_FLAG,
41 MULT_ITEM_CAT_ASSIGN_FLAG,
42 CONTROL_LEVEL_UPDATEABLE_FLAG,
43 MULT_ITEM_CAT_UPDATEABLE_FLAG,
44 VALIDATE_FLAG_UPDATEABLE_FLAG,
45 HIERARCHY_ENABLED,
46 CONTROL_LEVEL,
47 DEFAULT_CATEGORY_ID,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_LOGIN
53 -- REQUEST_ID,
54 ) values (
55 X_CATEGORY_SET_ID,
56 X_STRUCTURE_ID,
57 X_VALIDATE_FLAG,
58 X_MULT_ITEM_CAT_ASSIGN_FLAG,
59 DECODE(UPPER(X_CONTROL_LEVEL_UPDT_FLAG),'N','N',NULL),
60 DECODE(UPPER(X_MULT_ITEM_CAT_UPDT_FLAG),'N','N',NULL),
61 DECODE(UPPER(X_VALIDATE_FLAG_UPDT_FLAG),'N','N',NULL),
62 DECODE(UPPER(X_HIERARCHY_ENABLED),'N','N','Y','Y',NULL),
63 X_CONTROL_LEVEL,
64 X_DEFAULT_CATEGORY_ID,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_CREATION_DATE,
68 X_CREATED_BY,
69 X_LAST_UPDATE_LOGIN
70 -- X_REQUEST_ID,
71 );
72
73 insert into MTL_CATEGORY_SETS_TL (
74 CATEGORY_SET_ID,
75 LANGUAGE,
76 SOURCE_LANG,
77 CATEGORY_SET_NAME,
78 DESCRIPTION,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_LOGIN
84 ) select
85 X_CATEGORY_SET_ID,
86 L.LANGUAGE_CODE,
87 userenv('LANG'),
88 X_CATEGORY_SET_NAME,
89 X_DESCRIPTION,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_LAST_UPDATE_LOGIN
95 from FND_LANGUAGES L
96 where L.INSTALLED_FLAG in ('I', 'B')
97 and not exists
98 ( select NULL
99 from MTL_CATEGORY_SETS_TL T
100 where T.CATEGORY_SET_ID = X_CATEGORY_SET_ID
101 and T.LANGUAGE = L.LANGUAGE_CODE );
102
103 open c;
104 fetch c into X_ROWID;
105 if (c%notfound) then
106 close c;
107 raise no_data_found;
108 end if;
109 close c;
110
111 end INSERT_ROW;
112
113 -- ----------------------------------------------------------------------
114 -- PROCEDURE: Lock_Row
115 -- ----------------------------------------------------------------------
116
117 procedure LOCK_ROW (
118 X_CATEGORY_SET_ID in NUMBER,
119 X_CATEGORY_SET_NAME in VARCHAR2,
120 X_DESCRIPTION in VARCHAR2,
121 X_STRUCTURE_ID in NUMBER,
122 X_VALIDATE_FLAG in VARCHAR2,
123 X_MULT_ITEM_CAT_ASSIGN_FLAG in VARCHAR2,
124 X_CONTROL_LEVEL in NUMBER,
125 X_DEFAULT_CATEGORY_ID in NUMBER
126 -- X_REQUEST_ID in NUMBER,
127 ) is
128
129 cursor c is
130 select
131 STRUCTURE_ID,
132 VALIDATE_FLAG,
133 MULT_ITEM_CAT_ASSIGN_FLAG,
134 CONTROL_LEVEL,
135 DEFAULT_CATEGORY_ID
136 -- REQUEST_ID,
137 from MTL_CATEGORY_SETS_B
138 where CATEGORY_SET_ID = X_CATEGORY_SET_ID
139 for update of CATEGORY_SET_ID nowait ;
140
141 recinfo c%rowtype;
142
143 cursor c1 is
144 select
145 CATEGORY_SET_NAME,
146 DESCRIPTION,
147 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
148 from MTL_CATEGORY_SETS_TL
149 where CATEGORY_SET_ID = X_CATEGORY_SET_ID
150 -- Commented out. All translation rows need to be locked.
151 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
152 for update of CATEGORY_SET_ID nowait ;
153
154 begin
155
156 open c;
157 fetch c into recinfo;
158 if (c%notfound) then
159 close c;
160 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
161 app_exception.raise_exception;
162 end if;
163 close c;
164
165 if ( (recinfo.STRUCTURE_ID = X_STRUCTURE_ID)
166 AND (recinfo.VALIDATE_FLAG = X_VALIDATE_FLAG)
167 AND (recinfo.MULT_ITEM_CAT_ASSIGN_FLAG = X_MULT_ITEM_CAT_ASSIGN_FLAG)
168 AND (recinfo.CONTROL_LEVEL = X_CONTROL_LEVEL)
169 AND ((recinfo.DEFAULT_CATEGORY_ID = X_DEFAULT_CATEGORY_ID)
170 OR ((recinfo.DEFAULT_CATEGORY_ID is null) AND (X_DEFAULT_CATEGORY_ID is null)))
171 -- AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
172 -- OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
173 ) then
174 null;
175 else
176 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
177 app_exception.raise_exception;
178 end if;
179
180 for tlinfo in c1 loop
181 if (tlinfo.BASELANG = 'Y') then
182 if ( (tlinfo.CATEGORY_SET_NAME = X_CATEGORY_SET_NAME)
183 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
184 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
185 ) then
186 null;
187 else
188 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
189 app_exception.raise_exception;
190 end if;
191 end if;
192 end loop;
193
194 return;
195
196 end LOCK_ROW;
197
198 -- ----------------------------------------------------------------------
199 -- PROCEDURE: Update_Row
200 -- ----------------------------------------------------------------------
201
202 procedure UPDATE_ROW (
203 X_CATEGORY_SET_ID IN NUMBER,
204 X_CATEGORY_SET_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
205 X_DESCRIPTION IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
206 X_STRUCTURE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
207 X_VALIDATE_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
208 X_MULT_ITEM_CAT_ASSIGN_FLAG IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
209 X_CONTROL_LEVEL_UPDT_FLAG IN VARCHAR2 DEFAULT NULL,
210 X_MULT_ITEM_CAT_UPDT_FLAG IN VARCHAR2 DEFAULT NULL,
211 X_VALIDATE_FLAG_UPDT_FLAG IN VARCHAR2 DEFAULT NULL,
212 X_HIERARCHY_ENABLED IN VARCHAR2 DEFAULT NULL,
213 X_CONTROL_LEVEL IN NUMBER DEFAULT FND_API.G_MISS_NUM,
214 X_DEFAULT_CATEGORY_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
215 X_LAST_UPDATE_DATE IN DATE,
216 X_LAST_UPDATED_BY IN NUMBER,
217 X_LAST_UPDATE_LOGIN IN NUMBER
218
219 ) IS
220 l_miss_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
221 l_miss_num NUMBER := FND_API.G_MISS_NUM;
222 BEGIN
223
224 update MTL_CATEGORY_SETS_B set
225 STRUCTURE_ID = DECODE(X_STRUCTURE_ID,l_miss_num,STRUCTURE_ID,X_STRUCTURE_ID),
226 VALIDATE_FLAG = DECODE(X_VALIDATE_FLAG,l_miss_char,VALIDATE_FLAG,X_VALIDATE_FLAG),
227 MULT_ITEM_CAT_ASSIGN_FLAG = DECODE(X_MULT_ITEM_CAT_ASSIGN_FLAG,l_miss_char,MULT_ITEM_CAT_ASSIGN_FLAG,X_MULT_ITEM_CAT_ASSIGN_FLAG),
228 CONTROL_LEVEL_UPDATEABLE_FLAG = DECODE(UPPER(X_CONTROL_LEVEL_UPDT_FLAG),'N','N',NULL),
229 MULT_ITEM_CAT_UPDATEABLE_FLAG = DECODE(UPPER(X_MULT_ITEM_CAT_UPDT_FLAG),'N','N',NULL),
230 VALIDATE_FLAG_UPDATEABLE_FLAG = DECODE(UPPER(X_VALIDATE_FLAG_UPDT_FLAG),'N','N',NULL),
231 HIERARCHY_ENABLED = DECODE(UPPER(X_HIERARCHY_ENABLED),'N','N','Y','Y',NULL),
232 CONTROL_LEVEL = DECODE(X_CONTROL_LEVEL,l_miss_num,CONTROL_LEVEL,X_CONTROL_LEVEL),
233 DEFAULT_CATEGORY_ID = DECODE(X_DEFAULT_CATEGORY_ID,l_miss_num,DEFAULT_CATEGORY_ID,X_DEFAULT_CATEGORY_ID),
234 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
235 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
236 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
237 where CATEGORY_SET_ID = X_CATEGORY_SET_ID ;
238
239 if (sql%notfound) then
240 raise no_data_found;
241 end if;
242
243 update MTL_CATEGORY_SETS_TL set
244 SOURCE_LANG = userenv('LANG'),
245 CATEGORY_SET_NAME = DECODE(X_CATEGORY_SET_NAME,l_miss_char,CATEGORY_SET_NAME,X_CATEGORY_SET_NAME),
246 DESCRIPTION = DECODE(X_DESCRIPTION,l_miss_char,DESCRIPTION,X_DESCRIPTION),
247 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
248 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
249 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
250 where CATEGORY_SET_ID = X_CATEGORY_SET_ID
251 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
252
253 if (sql%notfound) then
254 raise no_data_found;
255 end if;
256
257 end UPDATE_ROW;
258
259 -- ----------------------------------------------------------------------
260 -- Deletion of categories is not supported.
261 -- ----------------------------------------------------------------------
262
263 procedure DELETE_ROW (
264 X_CATEGORY_SET_ID in NUMBER
265 ) is
266 begin
267
268 raise_application_error( -20000, 'MTL_CATEGORY_SETS_PKG: CANNOT_DELETE_RECORD' );
269
270 -- This code is for future use when decided to validate
271 -- and delete category sets.
272 /*
273 delete from MTL_CATEGORY_SETS_TL
274 where CATEGORY_SET_ID = X_CATEGORY_SET_ID;
275
276 if (sql%notfound) then
277 raise no_data_found;
278 end if;
279
280 delete from MTL_CATEGORY_SETS_B
281 where CATEGORY_SET_ID = X_CATEGORY_SET_ID;
282
283 if (sql%notfound) then
284 raise no_data_found;
285 end if;
286 */
287
288 end DELETE_ROW;
289
290
291 -- ----------------------------------------------------------------------
292 -- PROCEDURE: Add_Language
293 -- ----------------------------------------------------------------------
294
295 procedure ADD_LANGUAGE
296 is
297 begin
298
299 delete from MTL_CATEGORY_SETS_TL T
300 where not exists
301 ( select NULL
302 from MTL_CATEGORY_SETS_B B
303 where B.CATEGORY_SET_ID = T.CATEGORY_SET_ID
304 );
305
306 update MTL_CATEGORY_SETS_TL T set (
307 CATEGORY_SET_NAME,
308 DESCRIPTION
309 ) = ( select
310 B.CATEGORY_SET_NAME,
311 B.DESCRIPTION
312 from MTL_CATEGORY_SETS_TL B
313 where B.CATEGORY_SET_ID = T.CATEGORY_SET_ID
314 and B.LANGUAGE = T.SOURCE_LANG )
315 where (
316 T.CATEGORY_SET_ID,
317 T.LANGUAGE
318 ) in ( select
319 SUBT.CATEGORY_SET_ID,
320 SUBT.LANGUAGE
321 from MTL_CATEGORY_SETS_TL SUBB,
322 MTL_CATEGORY_SETS_TL SUBT
323 where SUBB.CATEGORY_SET_ID = SUBT.CATEGORY_SET_ID
324 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
325 and ( SUBB.CATEGORY_SET_NAME <> SUBT.CATEGORY_SET_NAME
326 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
327 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
328 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
329 );
330
331 insert into MTL_CATEGORY_SETS_TL (
332 CATEGORY_SET_ID,
333 CATEGORY_SET_NAME,
334 DESCRIPTION,
335 LAST_UPDATE_DATE,
336 LAST_UPDATED_BY,
337 CREATION_DATE,
338 CREATED_BY,
339 LAST_UPDATE_LOGIN,
340 LANGUAGE,
341 SOURCE_LANG
342 ) select
343 B.CATEGORY_SET_ID,
344 B.CATEGORY_SET_NAME,
345 B.DESCRIPTION,
346 B.LAST_UPDATE_DATE,
347 B.LAST_UPDATED_BY,
348 B.CREATION_DATE,
349 B.CREATED_BY,
350 B.LAST_UPDATE_LOGIN,
351 L.LANGUAGE_CODE,
352 B.SOURCE_LANG
353 from MTL_CATEGORY_SETS_TL B,
354 FND_LANGUAGES L
355 where L.INSTALLED_FLAG in ('I', 'B')
356 and B.LANGUAGE = userenv('LANG')
357 and not exists
358 ( select NULL
359 from MTL_CATEGORY_SETS_TL T
360 where T.CATEGORY_SET_ID = B.CATEGORY_SET_ID
361 and T.LANGUAGE = L.LANGUAGE_CODE );
362
363 end ADD_LANGUAGE;
364
365
366 -- ----------------------------------------------------------------------
367 -- PROCEDURE: Translate_Row
368 --
369 -- PARAMETERS:
370 -- x_<developer key>
371 -- x_<translated columns>
372 -- x_owner user owning the row (SEED or other)
373 --
374 -- COMMENT:
375 -- Called from the FNDLOAD config file in 'NLS' mode to upload
376 -- translations.
377 -- ----------------------------------------------------------------------
378
379 PROCEDURE Translate_Row
380 (
381 x_category_set_id IN NUMBER
382 , x_category_set_name IN VARCHAR2
383 , x_description IN VARCHAR2
384 , x_owner IN VARCHAR2
385 , x_custom_mode IN VARCHAR2
386 , x_lud IN DATE DEFAULT SYSDATE
387 )
388 IS
389
390 f_luby NUMBER; -- entity owner in file
391 f_ludate DATE; -- entity update date in file
392 db_luby NUMBER; -- entity owner in db
393 db_ludate DATE; -- entity update date in db
394
395 BEGIN
396
397 -- Translate owner to file_last_updated_by
398 f_luby := fnd_load_util.owner_id(x_owner);
399
400 -- Translate char last_update_date to date
401 f_ludate := nvl(to_date(to_char(x_lud,'YYYY/MM/DD'), 'YYYY/MM/DD'), sysdate);
402
403 --5103579: Added rownum clause in translate_row.
404 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
405 INTO db_luby, db_ludate
406 FROM mtl_category_sets_tl
407 WHERE category_set_id = x_category_set_id
408 AND userenv('LANG') IN (language, source_lang)
409 AND ROWNUM = 1;
410
411 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
412 db_ludate, x_custom_mode)) THEN
413
414 UPDATE mtl_category_sets_tl
415 SET category_set_name = NVL(x_category_set_name, category_set_name)
416 , description = NVL(x_description, description)
417 , last_update_date = db_ludate
418 , last_updated_by = db_luby
419 , last_update_login = 0
420 , source_lang = userenv('LANG')
421 WHERE category_set_id = x_category_set_id
422 AND userenv('LANG') IN (language, source_lang);
423
424 END IF;
425
426 END Translate_Row;
427
428
429 -- ----------------------------------------------------------------------
430 -- PROCEDURE: Load_Row
431 --
432 -- PARAMETERS:
433 -- x_<developer key>
434 -- x_<table_data>
435 -- x_owner user owning the row (SEED or other)
436 --
437 -- COMMENT:
438 -- Called from the FNDLOAD config file in 'MLS' mode to upload a
439 -- multi-lingual entity.
440 -- ----------------------------------------------------------------------
441
442 PROCEDURE Load_Row
443 (
444 x_category_set_id IN NUMBER
445 , x_category_set_name IN VARCHAR2
446 , x_description IN VARCHAR2
447 , X_STRUCTURE_ID IN NUMBER
448 , X_VALIDATE_FLAG IN VARCHAR2
449 , X_MULT_ITEM_CAT_ASSIGN_FLAG IN VARCHAR2
450 , X_CONTROL_LEVEL_UPDT_FLAG IN VARCHAR2
451 , X_MULT_ITEM_CAT_UPDT_FLAG IN VARCHAR2
452 , X_VALIDATE_FLAG_UPDT_FLAG IN VARCHAR2
453 , X_HIERARCHY_ENABLED IN VARCHAR2
454 , X_CONTROL_LEVEL IN NUMBER
455 , X_DEFAULT_CATEGORY_ID IN NUMBER
456 , x_owner IN VARCHAR2
457 , x_custom_mode IN VARCHAR2
458 , x_msg_name OUT NOCOPY VARCHAR2
459 , x_lud IN DATE DEFAULT SYSDATE
460 )
461 IS
462
463 l_Rowid VARCHAR2(30);
464 l_Login NUMBER := 0;
465 f_luby NUMBER; -- entity owner in file
466 f_ludate DATE; -- entity update date in file
467 db_luby NUMBER; -- entity owner in db
468 db_ludate DATE; -- entity update date in db
469 db_control_updt_flag MTL_CATEGORY_SETS_B.CONTROL_LEVEL_UPDATEABLE_FLAG%TYPE;
470 db_mult_item_cat_flag MTL_CATEGORY_SETS_B.MULT_ITEM_CAT_UPDATEABLE_FLAG%TYPE;
471 db_validate_updteable_flag MTL_CATEGORY_SETS_B.VALIDATE_FLAG_UPDATEABLE_FLAG%TYPE;
472 db_validate_flag MTL_CATEGORY_SETS_B.VALIDATE_FLAG%TYPE;
473 db_hierarchy_enabled MTL_CATEGORY_SETS_B.HIERARCHY_ENABLED%TYPE;
474 db_mult_item_Cat_assign_flag MTL_CATEGORY_SETS_B.MULT_ITEM_CAT_ASSIGN_FLAG%TYPE;
475 db_control_level MTL_CATEGORY_SETS_B.CONTROL_LEVEL%TYPE;
476 l_mult_item_cat_assign MTL_CATEGORY_SETS_B.MULT_ITEM_CAT_ASSIGN_FLAG%TYPE;
477 l_validate_flag MTL_CATEGORY_SETS_B.VALIDATE_FLAG%TYPE;
478 l_control_level MTL_CATEGORY_SETS_B.CONTROL_LEVEL%TYPE;
479 l_control_flag_changed BOOLEAN;
480 l_mult_item_flag_changed BOOLEAN;
481 l_validate_flag_changed BOOLEAN;
482 l_select VARCHAR2(10);
483
484 BEGIN
485
486 -- Translate owner to file_last_updated_by
487 f_luby := fnd_load_util.owner_id(x_owner);
488
489 -- Translate char last_update_date to date
490 f_ludate := nvl(to_date(to_char(x_lud,'YYYY/MM/DD'), 'YYYY/MM/DD'), sysdate);
491
492 BEGIN
493
494 SELECT LAST_UPDATED_BY,
495 LAST_UPDATE_DATE,
496 CONTROL_LEVEL_UPDATEABLE_FLAG,
497 MULT_ITEM_CAT_UPDATEABLE_FLAG,
498 VALIDATE_FLAG_UPDATEABLE_FLAG,
499 VALIDATE_FLAG,
500 HIERARCHY_ENABLED,
501 MULT_ITEM_CAT_ASSIGN_FLAG,
502 CONTROL_LEVEL
503 INTO db_luby,
504 db_ludate,
505 db_control_updt_flag,
506 db_mult_item_cat_flag,
507 db_validate_updteable_flag,
508 db_validate_flag,
509 db_hierarchy_enabled,
510 db_mult_item_Cat_assign_flag,
511 db_control_level
512 FROM MTL_CATEGORY_SETS_B
513 WHERE CATEGORY_SET_ID = x_category_set_id ;
514
515 l_mult_item_flag_changed := false;
516 l_control_flag_changed := false;
517
518 IF (db_control_updt_flag IS NULL AND X_CONTROL_LEVEL_UPDT_FLAG = 'N')
519 OR (db_control_updt_flag = 'N' AND X_CONTROL_LEVEL_UPDT_FLAG IS NULL)
520 OR (db_control_updt_flag <> 'N' AND X_CONTROL_LEVEL_UPDT_FLAG='N')
521 OR (db_control_updt_flag = 'N' AND X_CONTROL_LEVEL_UPDT_FLAG <>'N')THEN
522 l_control_flag_changed := true;
523 END IF;
524
525
526 IF (db_mult_item_cat_flag IS NULL AND X_MULT_ITEM_CAT_UPDT_FLAG = 'N')
527 OR (db_mult_item_cat_flag = 'N' AND X_MULT_ITEM_CAT_UPDT_FLAG IS NULL)
528 OR (db_mult_item_cat_flag <> 'N' AND X_MULT_ITEM_CAT_UPDT_FLAG ='N')
529 OR (db_mult_item_cat_flag = 'N' AND X_MULT_ITEM_CAT_UPDT_FLAG <>'N') THEN
530 l_mult_item_flag_changed := true;
531 END IF;
532
533 IF (db_validate_updteable_flag IS NULL AND X_VALIDATE_FLAG_UPDT_FLAG = 'N')
534 OR (db_validate_updteable_flag = 'N' AND X_VALIDATE_FLAG_UPDT_FLAG IS NULL)
535 OR (db_validate_updteable_flag <> 'N' AND X_VALIDATE_FLAG_UPDT_FLAG ='N')
536 OR (db_validate_updteable_flag = 'N' AND X_VALIDATE_FLAG_UPDT_FLAG <>'N') THEN
537 l_validate_flag_changed := true;
538 END IF;
539 IF (fnd_load_util.upload_test(f_luby,
540 f_ludate,
541 db_luby,
542 db_ludate,
543 x_custom_mode)) THEN
544 --Bug:3835368
545 IF (NVL(X_VALIDATE_FLAG,NVL(db_validate_flag,'N')) ='N'
546 AND NVL(X_HIERARCHY_ENABLED,NVL(db_hierarchy_enabled,'N')) ='Y') THEN
547 x_msg_name := 'INV_CHG_HIER_ENABLE_ERR';
548 RETURN;
549 ELSIF(NVL(X_HIERARCHY_ENABLED,'N') ='Y' AND NVL(db_hierarchy_enabled,'N')='N' ) THEN
550 BEGIN
551 SELECT NULL INTO l_select
552 FROM MTL_DEFAULT_CATEGORY_SETS
553 WHERE FUNCTIONAL_AREA_ID NOT IN (7,11)
554 AND CATEGORY_SET_ID = x_category_set_id
555 AND ROWNUM = 1;
556 x_msg_name := 'INV_DEF_HIER_ENABLE_ERR';
557 RETURN;
558 EXCEPTION
559 WHEN OTHERS THEN
560 NULL;
561 END;
562 END IF;
563
564 MTL_CATEGORY_SETS_Pkg.Update_Row
565 (
566 x_category_set_id => x_category_set_id
567 , x_category_set_name => x_category_set_name
568 , x_description => x_description
569 , X_STRUCTURE_ID => X_STRUCTURE_ID
570 , X_VALIDATE_FLAG => X_VALIDATE_FLAG
571 , X_MULT_ITEM_CAT_ASSIGN_FLAG => X_MULT_ITEM_CAT_ASSIGN_FLAG
572 , X_CONTROL_LEVEL_UPDT_FLAG => X_CONTROL_LEVEL_UPDT_FLAG
573 , X_MULT_ITEM_CAT_UPDT_FLAG => X_MULT_ITEM_CAT_UPDT_FLAG
574 , X_VALIDATE_FLAG_UPDT_FLAG => X_VALIDATE_FLAG_UPDT_FLAG
575 , X_HIERARCHY_ENABLED => X_HIERARCHY_ENABLED
576 , X_CONTROL_LEVEL => X_CONTROL_LEVEL
577 , X_DEFAULT_CATEGORY_ID => X_DEFAULT_CATEGORY_ID
578 , X_LAST_UPDATE_DATE => db_ludate
579 , X_LAST_UPDATED_BY => db_luby
580 , X_LAST_UPDATE_LOGIN => l_Login
581 );
582 ELSIF (l_control_flag_changed OR l_mult_item_flag_changed OR l_validate_flag_changed) THEN
583
584 --Bug:3835368
585 IF (NVL(X_VALIDATE_FLAG,NVL(db_validate_flag,'N')) ='N'
586 AND NVL(X_HIERARCHY_ENABLED,NVL(db_hierarchy_enabled,'N')) ='Y') THEN
587 x_msg_name := 'INV_CHG_HIER_ENABLE_ERR';
588 RETURN;
589 ELSIF(NVL(X_HIERARCHY_ENABLED,'N') ='Y' AND NVL(db_hierarchy_enabled,'N')='N' ) THEN
590 BEGIN
591 SELECT NULL INTO l_select
592 FROM MTL_DEFAULT_CATEGORY_SETS
593 WHERE FUNCTIONAL_AREA_ID NOT IN (7,11)
594 AND CATEGORY_SET_ID = x_category_set_id
595 AND ROWNUM = 1;
596 x_msg_name := 'INV_DEF_HIER_ENABLE_ERR';
597 RETURN;
598 EXCEPTION
599 WHEN OTHERS THEN
600 NULL;
601 END;
602 END IF;
603 --Bug:4225603 Assigning new values if corresponding values are true.
604 IF l_mult_item_flag_changed THEN
605 l_mult_item_cat_assign := X_MULT_ITEM_CAT_ASSIGN_FLAG;
606 ELSE
607 l_mult_item_cat_assign := db_mult_item_Cat_assign_flag;
608 END IF;
609 IF l_validate_flag_changed THEN
610 l_validate_flag := X_VALIDATE_FLAG;
611 ELSE
612 l_validate_flag := db_validate_flag;
613 END IF;
614 IF l_control_flag_changed THEN
615 l_control_level := X_CONTROL_LEVEL;
616 ELSE
617 l_control_level := db_control_level;
618 END IF;
619 --Bug:4225603 Ended
620
621 MTL_CATEGORY_SETS_Pkg.Update_Row
622 (
623 x_category_set_id => X_CATEGORY_SET_ID
624 , X_CONTROL_LEVEL_UPDT_FLAG => X_CONTROL_LEVEL_UPDT_FLAG
625 , X_MULT_ITEM_CAT_UPDT_FLAG => X_MULT_ITEM_CAT_UPDT_FLAG
626 , X_VALIDATE_FLAG_UPDT_FLAG => X_VALIDATE_FLAG_UPDT_FLAG
627 , X_HIERARCHY_ENABLED => X_HIERARCHY_ENABLED
628 , X_VALIDATE_FLAG => l_validate_flag
629 , X_MULT_ITEM_CAT_ASSIGN_FLAG => l_mult_item_cat_assign
630 , X_CONTROL_LEVEL => l_control_level
631 , X_LAST_UPDATE_DATE => db_ludate
632 , X_LAST_UPDATED_BY => db_luby
633 , X_LAST_UPDATE_LOGIN => l_Login
634 );
635 END IF;
636
637 EXCEPTION
638 WHEN no_data_found THEN
639 MTL_CATEGORY_SETS_Pkg.Insert_Row
640 (
641 X_ROWID => l_Rowid
642 , x_category_set_id => x_category_set_id
643 , x_category_set_name => x_category_set_name
644 , x_description => x_description
645 , X_STRUCTURE_ID => X_STRUCTURE_ID
646 , X_VALIDATE_FLAG => X_VALIDATE_FLAG
647 , X_MULT_ITEM_CAT_ASSIGN_FLAG => X_MULT_ITEM_CAT_ASSIGN_FLAG
648 , X_CONTROL_LEVEL_UPDT_FLAG => X_CONTROL_LEVEL_UPDT_FLAG
649 , X_MULT_ITEM_CAT_UPDT_FLAG => X_MULT_ITEM_CAT_UPDT_FLAG
650 , X_VALIDATE_FLAG_UPDT_FLAG => X_VALIDATE_FLAG_UPDT_FLAG
651 , X_HIERARCHY_ENABLED => X_HIERARCHY_ENABLED
652 , X_CONTROL_LEVEL => X_CONTROL_LEVEL
653 , X_DEFAULT_CATEGORY_ID => X_DEFAULT_CATEGORY_ID
654 , X_CREATION_DATE => f_ludate
655 , X_CREATED_BY => f_luby
656 , X_LAST_UPDATE_DATE => f_ludate
657 , X_LAST_UPDATED_BY => f_luby
658 , X_LAST_UPDATE_LOGIN => l_Login
659 );
660 END;
661
662 END Load_Row;
663 -- ----------------------------------------------------------------------
664 -- PROCEDURE: Load_Row
665 --
666 --
667 -- COMMENT:
668 -- Overloaded procedure
669 -- ----------------------------------------------------------------------
670 PROCEDURE Load_Row
671 (
672 X_CATEGORY_SET_ID IN NUMBER
673 , X_CATEGORY_SET_NAME IN VARCHAR2
674 , X_DESCRIPTION IN VARCHAR2
675 , X_STRUCTURE_CODE IN VARCHAR2
676 , X_VALIDATE_FLAG IN VARCHAR2
677 , X_MULT_ITEM_CAT_ASSIGN_FLAG IN VARCHAR2
678 , X_CONTROL_LEVEL IN NUMBER
679 , X_DEFAULT_CATEGORY_CD IN VARCHAR2
680 , X_OWNER IN VARCHAR2
681 , X_LAST_UPDATE_DATE IN VARCHAR2
682 , X_CONTROL_LEVEL_UPDT_FLAG IN VARCHAR2
683 , X_MULT_ITEM_CAT_UPDT_FLAG IN VARCHAR2
684 , X_VALIDATE_FLAG_UPDT_FLAG IN VARCHAR2
685 , X_HIERARCHY_ENABLED IN VARCHAR2
686 ) IS
687
688 l_structure_Id NUMBER;
689 l_msg_name VARCHAR2(2000);
690 l_Rowid VARCHAR2(300);
691 l_cat_set_id NUMBER;
692 l_default_category_id NUMBER;
693 BEGIN
694
695 SELECT category_set_id, structure_id
696 INTO l_cat_set_id, l_structure_id
697 FROM MTL_CATEGORY_SETS_VL
698 WHERE CATEGORY_SET_NAME = X_CATEGORY_SET_NAME ;
699
700 -- Get default category id from the category code
701 BEGIN
702 SELECT category_id INTO l_default_category_id
703 FROM mtl_categories_b_kfv
704 WHERE concatenated_segments = X_DEFAULT_CATEGORY_CD
705 AND structure_id = l_structure_id;
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 l_default_category_id := null;
709 END;
710
711 -- select sysdate into l_date from dual;
712
713 MTL_CATEGORY_SETS_PKG.Update_Row(
714 X_CATEGORY_SET_ID => l_cat_set_id,
715 X_CATEGORY_SET_NAME => X_CATEGORY_SET_NAME,
716 X_DESCRIPTION => X_DESCRIPTION,
717 X_STRUCTURE_ID => l_structure_id,
718 X_VALIDATE_FLAG => X_VALIDATE_FLAG,
719 X_MULT_ITEM_CAT_ASSIGN_FLAG => X_MULT_ITEM_CAT_ASSIGN_FLAG,
720 X_CONTROL_LEVEL_UPDT_FLAG => X_CONTROL_LEVEL_UPDT_FLAG,
721 X_MULT_ITEM_CAT_UPDT_FLAG => X_MULT_ITEM_CAT_UPDT_FLAG,
722 X_VALIDATE_FLAG_UPDT_FLAG => X_VALIDATE_FLAG_UPDT_FLAG,
723 X_HIERARCHY_ENABLED => X_HIERARCHY_ENABLED,
724 X_CONTROL_LEVEL => X_CONTROL_LEVEL,
725 X_DEFAULT_CATEGORY_ID => l_default_category_id,
726 X_LAST_UPDATE_DATE => SYSDATE,
727 X_LAST_UPDATED_BY => fnd_load_util.owner_id(X_OWNER),
728 X_LAST_UPDATE_LOGIN => 0
729 );
730
731
732 EXCEPTION WHEN no_data_found THEN
733
734 BEGIN
735 -- If category set id is null, then create category set
736 -- with the structure of the source category set.
737 SELECT id_flex_num
738 INTO l_Structure_Id
739 FROM fnd_id_flex_structures_vl
740 WHERE application_id = 401
741 AND id_flex_code = 'MCAT'
742 AND id_flex_structure_code = X_STRUCTURE_CODE;
743
744 select MTL_CATEGORY_SETS_S.nextval into l_cat_set_id from dual;
745
746 -- Get default category id from the category code
747 BEGIN
748 SELECT category_id INTO l_default_category_id
749 FROM mtl_categories_b_kfv
750 WHERE concatenated_segments = X_DEFAULT_CATEGORY_CD
751 AND structure_id = l_structure_id;
752 EXCEPTION
753 WHEN NO_DATA_FOUND THEN
754 l_default_category_id := null;
755 END;
756
757 --Insert the new category set name, but category set id
758 --is sequence generated
759 MTL_CATEGORY_SETS_Pkg.Insert_Row
760 (
761 X_ROWID => l_Rowid
762 , x_category_set_id => l_cat_set_id
763 , x_category_set_name => X_CATEGORY_SET_NAME
764 , x_description => X_DESCRIPTION
765 , X_STRUCTURE_ID => l_structure_Id
766 , X_VALIDATE_FLAG => X_VALIDATE_FLAG
767 , X_MULT_ITEM_CAT_ASSIGN_FLAG => X_MULT_ITEM_CAT_ASSIGN_FLAG
768 , X_CONTROL_LEVEL_UPDT_FLAG => X_CONTROL_LEVEL_UPDT_FLAG
769 , X_MULT_ITEM_CAT_UPDT_FLAG => X_MULT_ITEM_CAT_UPDT_FLAG
770 , X_VALIDATE_FLAG_UPDT_FLAG => X_VALIDATE_FLAG_UPDT_FLAG
771 , X_HIERARCHY_ENABLED => X_HIERARCHY_ENABLED
772 , X_CONTROL_LEVEL => X_CONTROL_LEVEL
773 , X_DEFAULT_CATEGORY_ID => l_default_category_id
774 , X_CREATION_DATE => SYSDATE
775 , X_CREATED_BY => fnd_load_util.owner_id(X_OWNER)
776 , X_LAST_UPDATE_DATE => SYSDATE
777 , X_LAST_UPDATED_BY => fnd_load_util.owner_id(X_OWNER)
778 , X_LAST_UPDATE_LOGIN => 0
779 );
780 end;
781 end;
782
783 end MTL_CATEGORY_SETS_PKG;