[Home] [Help]
PACKAGE BODY: APPS.MTL_ITEM_CATALOG_GROUPS_UTIL
Source
1 PACKAGE BODY MTL_ITEM_CATALOG_GROUPS_UTIL as
2 /* $Header: INVICGUB.pls 120.1 2006/01/19 04:16:12 swshukla noship $ */
3
4 PROCEDURE INSERT_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE
5 ,X_ROWID OUT NOCOPY ROWID) IS
6
7 l_return_status VARCHAR2(1); --Bug 4639946
8 BEGIN
9
10 INSERT INTO MTL_ITEM_CATALOG_GROUPS_B (
11 PARENT_CATALOG_GROUP_ID,
12 ITEM_CREATION_ALLOWED_FLAG,
13 ITEM_CATALOG_GROUP_ID,
14 INACTIVE_DATE,
15 SUMMARY_FLAG,
16 ENABLED_FLAG,
17 START_DATE_ACTIVE,
18 END_DATE_ACTIVE,
19 SEGMENT1,
20 SEGMENT2,
21 SEGMENT3,
22 SEGMENT4,
23 SEGMENT5,
24 SEGMENT6,
25 SEGMENT7,
26 SEGMENT8,
27 SEGMENT9,
28 SEGMENT10,
29 SEGMENT11,
30 SEGMENT12,
31 SEGMENT13,
32 SEGMENT14,
33 SEGMENT15,
34 SEGMENT16,
35 SEGMENT17,
36 SEGMENT18,
37 SEGMENT19,
38 SEGMENT20,
39 ATTRIBUTE_CATEGORY,
40 ATTRIBUTE1,
41 ATTRIBUTE2,
42 ATTRIBUTE3,
43 ATTRIBUTE4,
44 ATTRIBUTE5,
45 ATTRIBUTE6,
46 ATTRIBUTE7,
47 ATTRIBUTE8,
48 ATTRIBUTE9,
49 ATTRIBUTE10,
50 ATTRIBUTE11,
51 ATTRIBUTE12,
52 ATTRIBUTE13,
53 ATTRIBUTE14,
54 ATTRIBUTE15,
55 REQUEST_ID,
56 CREATION_DATE,
57 CREATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_LOGIN
61 ) VALUES (
62 P_Catalog_Group_Rec.PARENT_CATALOG_GROUP_ID,
63 NVL(P_Catalog_Group_Rec.ITEM_CREATION_ALLOWED_FLAG,'Y'),
64 P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID,
65 P_Catalog_Group_Rec.INACTIVE_DATE,
66 P_Catalog_Group_Rec.SUMMARY_FLAG,
67 P_Catalog_Group_Rec.ENABLED_FLAG,
68 P_Catalog_Group_Rec.START_DATE_ACTIVE,
69 P_Catalog_Group_Rec.END_DATE_ACTIVE,
70 P_Catalog_Group_Rec.SEGMENT1,
71 P_Catalog_Group_Rec.SEGMENT2,
72 P_Catalog_Group_Rec.SEGMENT3,
73 P_Catalog_Group_Rec.SEGMENT4,
74 P_Catalog_Group_Rec.SEGMENT5,
75 P_Catalog_Group_Rec.SEGMENT6,
76 P_Catalog_Group_Rec.SEGMENT7,
77 P_Catalog_Group_Rec.SEGMENT8,
78 P_Catalog_Group_Rec.SEGMENT9,
79 P_Catalog_Group_Rec.SEGMENT10,
80 P_Catalog_Group_Rec.SEGMENT11,
81 P_Catalog_Group_Rec.SEGMENT12,
82 P_Catalog_Group_Rec.SEGMENT13,
83 P_Catalog_Group_Rec.SEGMENT14,
84 P_Catalog_Group_Rec.SEGMENT15,
85 P_Catalog_Group_Rec.SEGMENT16,
86 P_Catalog_Group_Rec.SEGMENT17,
87 P_Catalog_Group_Rec.SEGMENT18,
88 P_Catalog_Group_Rec.SEGMENT19,
89 P_Catalog_Group_Rec.SEGMENT20,
90 P_Catalog_Group_Rec.ATTRIBUTE_CATEGORY,
91 P_Catalog_Group_Rec.ATTRIBUTE1,
92 P_Catalog_Group_Rec.ATTRIBUTE2,
93 P_Catalog_Group_Rec.ATTRIBUTE3,
94 P_Catalog_Group_Rec.ATTRIBUTE4,
95 P_Catalog_Group_Rec.ATTRIBUTE5,
96 P_Catalog_Group_Rec.ATTRIBUTE6,
97 P_Catalog_Group_Rec.ATTRIBUTE7,
98 P_Catalog_Group_Rec.ATTRIBUTE8,
99 P_Catalog_Group_Rec.ATTRIBUTE9,
100 P_Catalog_Group_Rec.ATTRIBUTE10,
101 P_Catalog_Group_Rec.ATTRIBUTE11,
102 P_Catalog_Group_Rec.ATTRIBUTE12,
103 P_Catalog_Group_Rec.ATTRIBUTE13,
104 P_Catalog_Group_Rec.ATTRIBUTE14,
105 P_Catalog_Group_Rec.ATTRIBUTE15,
106 P_Catalog_Group_Rec.REQUEST_ID,
107 P_Catalog_Group_Rec.CREATION_DATE,
108 P_Catalog_Group_Rec.CREATED_BY,
109 P_Catalog_Group_Rec.LAST_UPDATE_DATE,
110 P_Catalog_Group_Rec.LAST_UPDATED_BY,
111 P_Catalog_Group_Rec.LAST_UPDATE_LOGIN
112 ) RETURNING ROWID INTO X_ROWID;
113
114 INSERT INTO MTL_ITEM_CATALOG_GROUPS_TL (
115 ITEM_CATALOG_GROUP_ID,
116 DESCRIPTION,
117 CREATION_DATE,
118 CREATED_BY,
119 LAST_UPDATE_DATE,
120 LAST_UPDATED_BY,
121 LAST_UPDATE_LOGIN,
122 LANGUAGE,
123 SOURCE_LANG
124 ) SELECT
125 P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID,
126 P_Catalog_Group_Rec.DESCRIPTION,
127 P_Catalog_Group_Rec.CREATION_DATE,
128 P_Catalog_Group_Rec.CREATED_BY,
129 P_Catalog_Group_Rec.LAST_UPDATE_DATE,
130 P_Catalog_Group_Rec.LAST_UPDATED_BY,
131 P_Catalog_Group_Rec.LAST_UPDATE_LOGIN,
132 L.LANGUAGE_CODE,
133 USERENV('LANG')
134 FROM FND_LANGUAGES L
135 WHERE L.INSTALLED_FLAG in ('I', 'B')
136 AND NOT EXISTS (SELECT NULL
137 FROM MTL_ITEM_CATALOG_GROUPS_TL T
138 WHERE T.ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
139 AND T.LANGUAGE = L.LANGUAGE_CODE);
140
141 --Bug: 4639946
142 EXECUTE IMMEDIATE
143 'Begin '||
144 'EGO_BROWSE_PVT.Sync_ICG_Denorm_Hier_Table ( '||
145 ' p_catalog_group_id => :P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID '||
146 ' ,p_old_parent_id => NULL '||
147 ' ,x_return_status => :l_return_status); '||
148 'EXCEPTION '||
149 ' When OTHERS Then '||
150 ' null; '||
151 'End; '
152 USING IN P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID,
153 OUT l_return_status;
154
155 END INSERT_ROW;
156
157 PROCEDURE LOCK_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE) IS
158
159 CURSOR c_get_item_catalog IS
160 SELECT
161 PARENT_CATALOG_GROUP_ID,
162 ITEM_CREATION_ALLOWED_FLAG,
163 INACTIVE_DATE,
164 SUMMARY_FLAG,
165 ENABLED_FLAG,
166 START_DATE_ACTIVE,
167 END_DATE_ACTIVE,
168 SEGMENT1,
169 SEGMENT2,
170 SEGMENT3,
171 SEGMENT4,
172 SEGMENT5,
173 SEGMENT6,
174 SEGMENT7,
175 SEGMENT8,
176 SEGMENT9,
177 SEGMENT10,
178 SEGMENT11,
179 SEGMENT12,
180 SEGMENT13,
181 SEGMENT14,
182 SEGMENT15,
183 SEGMENT16,
184 SEGMENT17,
185 SEGMENT18,
186 SEGMENT19,
187 SEGMENT20,
188 ATTRIBUTE_CATEGORY,
189 ATTRIBUTE1,
190 ATTRIBUTE2,
191 ATTRIBUTE3,
192 ATTRIBUTE4,
193 ATTRIBUTE5,
194 ATTRIBUTE6,
195 ATTRIBUTE7,
196 ATTRIBUTE8,
197 ATTRIBUTE9,
198 ATTRIBUTE10,
199 ATTRIBUTE11,
200 ATTRIBUTE12,
201 ATTRIBUTE13,
202 ATTRIBUTE14,
203 ATTRIBUTE15,
204 REQUEST_ID
205 FROM MTL_ITEM_CATALOG_GROUPS_B
206 WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
207 FOR UPDATE OF ITEM_CATALOG_GROUP_ID NOWAIT;
208
209
210 CURSOR c_get_description_rec IS
211 SELECT
212 DESCRIPTION,
213 DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
214 FROM MTL_ITEM_CATALOG_GROUPS_TL
215 WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
216 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
217 FOR UPDATE OF ITEM_CATALOG_GROUP_ID NOWAIT;
218
219 recinfo c_get_item_catalog%rowtype;
220
221 BEGIN
222
223 OPEN c_get_item_catalog;
224 FETCH c_get_item_catalog INTO recinfo;
225 IF (c_get_item_catalog%NOTFOUND) THEN
226 CLOSE c_get_item_catalog;
227 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
228 Raise FND_API.g_EXC_UNEXPECTED_ERROR;
229 END IF;
230 CLOSE c_get_item_catalog;
231
232 IF (((recinfo.INACTIVE_DATE = P_Catalog_Group_Rec.INACTIVE_DATE)
233 OR ((recinfo.INACTIVE_DATE is null) AND (P_Catalog_Group_Rec.INACTIVE_DATE is null)))
234 AND (recinfo.SUMMARY_FLAG = P_Catalog_Group_Rec.SUMMARY_FLAG)
235 AND (recinfo.ENABLED_FLAG = P_Catalog_Group_Rec.ENABLED_FLAG)
236 AND ((recinfo.START_DATE_ACTIVE = P_Catalog_Group_Rec.START_DATE_ACTIVE)
237 OR ((recinfo.START_DATE_ACTIVE is null) AND (P_Catalog_Group_Rec.START_DATE_ACTIVE is null)))
238 AND ((recinfo.END_DATE_ACTIVE = P_Catalog_Group_Rec.END_DATE_ACTIVE)
239 OR ((recinfo.END_DATE_ACTIVE is null) AND (P_Catalog_Group_Rec.END_DATE_ACTIVE is null)))
240 AND ((recinfo.SEGMENT1 = P_Catalog_Group_Rec.SEGMENT1)
241 OR ((recinfo.SEGMENT1 is null) AND (P_Catalog_Group_Rec.SEGMENT1 is null)))
242 AND ((recinfo.SEGMENT2 = P_Catalog_Group_Rec.SEGMENT2)
243 OR ((recinfo.SEGMENT2 is null) AND (P_Catalog_Group_Rec.SEGMENT2 is null)))
244 AND ((recinfo.SEGMENT3 = P_Catalog_Group_Rec.SEGMENT3)
245 OR ((recinfo.SEGMENT3 is null) AND (P_Catalog_Group_Rec.SEGMENT3 is null)))
246 AND ((recinfo.SEGMENT4 = P_Catalog_Group_Rec.SEGMENT4)
247 OR ((recinfo.SEGMENT4 is null) AND (P_Catalog_Group_Rec.SEGMENT4 is null)))
248 AND ((recinfo.SEGMENT5 = P_Catalog_Group_Rec.SEGMENT5)
249 OR ((recinfo.SEGMENT5 is null) AND (P_Catalog_Group_Rec.SEGMENT5 is null)))
250 AND ((recinfo.SEGMENT6 = P_Catalog_Group_Rec.SEGMENT6)
251 OR ((recinfo.SEGMENT6 is null) AND (P_Catalog_Group_Rec.SEGMENT6 is null)))
252 AND ((recinfo.SEGMENT7 = P_Catalog_Group_Rec.SEGMENT7)
253 OR ((recinfo.SEGMENT7 is null) AND (P_Catalog_Group_Rec.SEGMENT7 is null)))
254 AND ((recinfo.SEGMENT8 = P_Catalog_Group_Rec.SEGMENT8)
255 OR ((recinfo.SEGMENT8 is null) AND (P_Catalog_Group_Rec.SEGMENT8 is null)))
256 AND ((recinfo.SEGMENT9 = P_Catalog_Group_Rec.SEGMENT9)
257 OR ((recinfo.SEGMENT9 is null) AND (P_Catalog_Group_Rec.SEGMENT9 is null)))
258 AND ((recinfo.SEGMENT10 = P_Catalog_Group_Rec.SEGMENT10)
259 OR ((recinfo.SEGMENT10 is null) AND (P_Catalog_Group_Rec.SEGMENT10 is null)))
260 AND ((recinfo.SEGMENT11 = P_Catalog_Group_Rec.SEGMENT11)
261 OR ((recinfo.SEGMENT11 is null) AND (P_Catalog_Group_Rec.SEGMENT11 is null)))
262 AND ((recinfo.SEGMENT12 = P_Catalog_Group_Rec.SEGMENT12)
263 OR ((recinfo.SEGMENT12 is null) AND (P_Catalog_Group_Rec.SEGMENT12 is null)))
264 AND ((recinfo.SEGMENT13 = P_Catalog_Group_Rec.SEGMENT13)
265 OR ((recinfo.SEGMENT13 is null) AND (P_Catalog_Group_Rec.SEGMENT13 is null)))
266 AND ((recinfo.SEGMENT14 = P_Catalog_Group_Rec.SEGMENT14)
267 OR ((recinfo.SEGMENT14 is null) AND (P_Catalog_Group_Rec.SEGMENT14 is null)))
268 AND ((recinfo.SEGMENT15 = P_Catalog_Group_Rec.SEGMENT15)
269 OR ((recinfo.SEGMENT15 is null) AND (P_Catalog_Group_Rec.SEGMENT15 is null)))
270 AND ((recinfo.SEGMENT16 = P_Catalog_Group_Rec.SEGMENT16)
271 OR ((recinfo.SEGMENT16 is null) AND (P_Catalog_Group_Rec.SEGMENT16 is null)))
272 AND ((recinfo.SEGMENT17 = P_Catalog_Group_Rec.SEGMENT17)
273 OR ((recinfo.SEGMENT17 is null) AND (P_Catalog_Group_Rec.SEGMENT17 is null)))
274 AND ((recinfo.SEGMENT18 = P_Catalog_Group_Rec.SEGMENT18)
275 OR ((recinfo.SEGMENT18 is null) AND (P_Catalog_Group_Rec.SEGMENT18 is null)))
276 AND ((recinfo.SEGMENT19 = P_Catalog_Group_Rec.SEGMENT19)
277 OR ((recinfo.SEGMENT19 is null) AND (P_Catalog_Group_Rec.SEGMENT19 is null)))
278 AND ((recinfo.SEGMENT20 = P_Catalog_Group_Rec.SEGMENT20)
279 OR ((recinfo.SEGMENT20 is null) AND (P_Catalog_Group_Rec.SEGMENT20 is null)))
280 AND ((recinfo.ATTRIBUTE_CATEGORY = P_Catalog_Group_Rec.ATTRIBUTE_CATEGORY)
281 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_Catalog_Group_Rec.ATTRIBUTE_CATEGORY is null)))
282 AND ((recinfo.ATTRIBUTE1 = P_Catalog_Group_Rec.ATTRIBUTE1)
283 OR ((recinfo.ATTRIBUTE1 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE1 is null)))
284 AND ((recinfo.ATTRIBUTE2 = P_Catalog_Group_Rec.ATTRIBUTE2)
285 OR ((recinfo.ATTRIBUTE2 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE2 is null)))
286 AND ((recinfo.ATTRIBUTE3 = P_Catalog_Group_Rec.ATTRIBUTE3)
287 OR ((recinfo.ATTRIBUTE3 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE3 is null)))
288 AND ((recinfo.ATTRIBUTE4 = P_Catalog_Group_Rec.ATTRIBUTE4)
289 OR ((recinfo.ATTRIBUTE4 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE4 is null)))
290 AND ((recinfo.ATTRIBUTE5 = P_Catalog_Group_Rec.ATTRIBUTE5)
291 OR ((recinfo.ATTRIBUTE5 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE5 is null)))
292 AND ((recinfo.ATTRIBUTE6 = P_Catalog_Group_Rec.ATTRIBUTE6)
293 OR ((recinfo.ATTRIBUTE6 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE6 is null)))
294 AND ((recinfo.ATTRIBUTE7 = P_Catalog_Group_Rec.ATTRIBUTE7)
295 OR ((recinfo.ATTRIBUTE7 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE7 is null)))
296 AND ((recinfo.ATTRIBUTE8 = P_Catalog_Group_Rec.ATTRIBUTE8)
297 OR ((recinfo.ATTRIBUTE8 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE8 is null)))
298 AND ((recinfo.ATTRIBUTE9 = P_Catalog_Group_Rec.ATTRIBUTE9)
299 OR ((recinfo.ATTRIBUTE9 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE9 is null)))
300 AND ((recinfo.ATTRIBUTE10 = P_Catalog_Group_Rec.ATTRIBUTE10)
301 OR ((recinfo.ATTRIBUTE10 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE10 is null)))
302 AND ((recinfo.ATTRIBUTE11 = P_Catalog_Group_Rec.ATTRIBUTE11)
303 OR ((recinfo.ATTRIBUTE11 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE11 is null)))
304 AND ((recinfo.ATTRIBUTE12 = P_Catalog_Group_Rec.ATTRIBUTE12)
305 OR ((recinfo.ATTRIBUTE12 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE12 is null)))
306 AND ((recinfo.ATTRIBUTE13 = P_Catalog_Group_Rec.ATTRIBUTE13)
307 OR ((recinfo.ATTRIBUTE13 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE13 is null)))
308 AND ((recinfo.ATTRIBUTE14 = P_Catalog_Group_Rec.ATTRIBUTE14)
309 OR ((recinfo.ATTRIBUTE14 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE14 is null)))
310 AND ((recinfo.ATTRIBUTE15 = P_Catalog_Group_Rec.ATTRIBUTE15)
311 OR ((recinfo.ATTRIBUTE15 is null) AND (P_Catalog_Group_Rec.ATTRIBUTE15 is null))))
312 THEN
313 NULL;
314 ELSE
315 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
316 Raise FND_API.g_EXC_UNEXPECTED_ERROR;
317 END IF;
318
319 FOR tlinfo IN c_get_description_rec LOOP
320 IF (tlinfo.BASELANG = 'Y') THEN
321 IF (((tlinfo.DESCRIPTION = P_Catalog_Group_Rec.DESCRIPTION)
322 OR ((tlinfo.DESCRIPTION is null) AND (P_Catalog_Group_Rec.DESCRIPTION is null)))) THEN
323 NULL;
324 ELSE
325 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
326 Raise FND_API.g_EXC_UNEXPECTED_ERROR;
327 END IF;
328 END IF;
329 END LOOP;
330
331 EXCEPTION
332
333 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
334 IF ( c_get_item_catalog%ISOPEN ) THEN
335 CLOSE c_get_item_catalog;
336 END IF;
337 IF ( c_get_description_rec%ISOPEN ) THEN
338 CLOSE c_get_description_rec;
339 END IF;
340 app_exception.raise_exception;
341
342 END LOCK_ROW;
343
344 PROCEDURE UPDATE_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE) IS
345
346 l_old_parent_id NUMBER; --Bug: 4639946
347 l_return_status VARCHAR2(1); --Bug: 4639946
348 BEGIN
349
350 --Bug: 4639946
351 Select PARENT_CATALOG_GROUP_ID into l_old_parent_id
352 From MTL_ITEM_CATALOG_GROUPS_B
353 WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID;
354
355 IF (SQL%NOTFOUND) THEN
356 RAISE no_data_found;
357 END IF;
358
359 UPDATE MTL_ITEM_CATALOG_GROUPS_B
360 SET
361 PARENT_CATALOG_GROUP_ID = P_Catalog_Group_Rec.PARENT_CATALOG_GROUP_ID,
362 ITEM_CREATION_ALLOWED_FLAG = NVL(P_Catalog_Group_Rec.ITEM_CREATION_ALLOWED_FLAG,ITEM_CREATION_ALLOWED_FLAG),
363 INACTIVE_DATE = P_Catalog_Group_Rec.INACTIVE_DATE,
364 SUMMARY_FLAG = P_Catalog_Group_Rec.SUMMARY_FLAG,
365 ENABLED_FLAG = P_Catalog_Group_Rec.ENABLED_FLAG,
366 START_DATE_ACTIVE = P_Catalog_Group_Rec.START_DATE_ACTIVE,
367 END_DATE_ACTIVE = P_Catalog_Group_Rec.END_DATE_ACTIVE,
368 SEGMENT1 = P_Catalog_Group_Rec.SEGMENT1,
369 SEGMENT2 = P_Catalog_Group_Rec.SEGMENT2,
370 SEGMENT3 = P_Catalog_Group_Rec.SEGMENT3,
371 SEGMENT4 = P_Catalog_Group_Rec.SEGMENT4,
372 SEGMENT5 = P_Catalog_Group_Rec.SEGMENT5,
373 SEGMENT6 = P_Catalog_Group_Rec.SEGMENT6,
374 SEGMENT7 = P_Catalog_Group_Rec.SEGMENT7,
375 SEGMENT8 = P_Catalog_Group_Rec.SEGMENT8,
376 SEGMENT9 = P_Catalog_Group_Rec.SEGMENT9,
377 SEGMENT10 = P_Catalog_Group_Rec.SEGMENT10,
378 SEGMENT11 = P_Catalog_Group_Rec.SEGMENT11,
379 SEGMENT12 = P_Catalog_Group_Rec.SEGMENT12,
380 SEGMENT13 = P_Catalog_Group_Rec.SEGMENT13,
381 SEGMENT14 = P_Catalog_Group_Rec.SEGMENT14,
382 SEGMENT15 = P_Catalog_Group_Rec.SEGMENT15,
383 SEGMENT16 = P_Catalog_Group_Rec.SEGMENT16,
384 SEGMENT17 = P_Catalog_Group_Rec.SEGMENT17,
385 SEGMENT18 = P_Catalog_Group_Rec.SEGMENT18,
386 SEGMENT19 = P_Catalog_Group_Rec.SEGMENT19,
387 SEGMENT20 = P_Catalog_Group_Rec.SEGMENT20,
388 ATTRIBUTE_CATEGORY = P_Catalog_Group_Rec.ATTRIBUTE_CATEGORY,
389 ATTRIBUTE1 = P_Catalog_Group_Rec.ATTRIBUTE1,
390 ATTRIBUTE2 = P_Catalog_Group_Rec.ATTRIBUTE2,
391 ATTRIBUTE3 = P_Catalog_Group_Rec.ATTRIBUTE3,
392 ATTRIBUTE4 = P_Catalog_Group_Rec.ATTRIBUTE4,
393 ATTRIBUTE5 = P_Catalog_Group_Rec.ATTRIBUTE5,
394 ATTRIBUTE6 = P_Catalog_Group_Rec.ATTRIBUTE6,
395 ATTRIBUTE7 = P_Catalog_Group_Rec.ATTRIBUTE7,
396 ATTRIBUTE8 = P_Catalog_Group_Rec.ATTRIBUTE8,
397 ATTRIBUTE9 = P_Catalog_Group_Rec.ATTRIBUTE9,
398 ATTRIBUTE10 = P_Catalog_Group_Rec.ATTRIBUTE10,
399 ATTRIBUTE11 = P_Catalog_Group_Rec.ATTRIBUTE11,
400 ATTRIBUTE12 = P_Catalog_Group_Rec.ATTRIBUTE12,
401 ATTRIBUTE13 = P_Catalog_Group_Rec.ATTRIBUTE13,
402 ATTRIBUTE14 = P_Catalog_Group_Rec.ATTRIBUTE14,
403 ATTRIBUTE15 = P_Catalog_Group_Rec.ATTRIBUTE15,
404 REQUEST_ID = P_Catalog_Group_Rec.REQUEST_ID,
405 LAST_UPDATE_DATE = P_Catalog_Group_Rec.LAST_UPDATE_DATE,
406 LAST_UPDATED_BY = P_Catalog_Group_Rec.LAST_UPDATED_BY,
407 LAST_UPDATE_LOGIN = P_Catalog_Group_Rec.LAST_UPDATE_LOGIN
408 WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID;
409
410 IF (SQL%NOTFOUND) THEN
411 RAISE no_data_found;
412 END IF;
413
414
415 UPDATE MTL_ITEM_CATALOG_GROUPS_TL
416 SET
417 DESCRIPTION = P_Catalog_Group_Rec.DESCRIPTION,
418 LAST_UPDATE_DATE = P_Catalog_Group_Rec.LAST_UPDATE_DATE,
419 LAST_UPDATED_BY = P_Catalog_Group_Rec.LAST_UPDATED_BY,
420 LAST_UPDATE_LOGIN = P_Catalog_Group_Rec.LAST_UPDATE_LOGIN,
421 SOURCE_LANG = USERENV('LANG')
422 WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
423 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
424
425 IF (SQL%NOTFOUND) THEN
426 RAISE no_data_found;
427 END IF;
428
429 --Bug: 4639946
430 EXECUTE IMMEDIATE
431 'Begin '||
432 'EGO_BROWSE_PVT.Sync_ICG_Denorm_Hier_Table ( '||
433 ' p_catalog_group_id => :P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID '||
434 ' ,p_old_parent_id => :l_old_parent_id '||
435 ' ,x_return_status => :l_return_status); '||
436 'EXCEPTION '||
437 ' When OTHERS Then '||
438 ' null; '||
439 'End; '
440 USING IN P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID,
441 IN l_old_parent_id,
442 OUT l_return_status;
443
444 END UPDATE_ROW;
445
446 PROCEDURE DELETE_ROW (X_ITEM_CATALOG_GROUP_ID IN MTL_ITEM_CATALOG_GROUPS.ITEM_CATALOG_GROUP_ID%TYPE)
447 IS
448 BEGIN
449
450 DELETE FROM MTL_ITEM_CATALOG_GROUPS_TL
451 WHERE ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID;
452
453 IF (SQL%NOTFOUND) THEN
454 RAISE no_data_found;
455 END IF;
456
457 DELETE FROM MTL_ITEM_CATALOG_GROUPS_B
458 WHERE ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID;
459
460 IF (SQL%NOTFOUND) THEN
461 RAISE no_data_found;
462 END IF;
463
464 END DELETE_ROW;
465
466 PROCEDURE ADD_LANGUAGE IS
467 BEGIN
468
469 DELETE FROM MTL_ITEM_CATALOG_GROUPS_TL T
470 WHERE NOT EXISTS (SELECT NULL
471 FROM MTL_ITEM_CATALOG_GROUPS_B B
472 WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID);
473
474 UPDATE MTL_ITEM_CATALOG_GROUPS_TL T
475 SET (DESCRIPTION) = (SELECT B.DESCRIPTION
476 FROM MTL_ITEM_CATALOG_GROUPS_TL B
477 WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID
478 AND B.LANGUAGE = T.SOURCE_LANG)
479 WHERE ( T.ITEM_CATALOG_GROUP_ID,T.LANGUAGE)
480 IN (SELECT SUBT.ITEM_CATALOG_GROUP_ID,
481 SUBT.LANGUAGE
482 FROM MTL_ITEM_CATALOG_GROUPS_TL SUBB,
483 MTL_ITEM_CATALOG_GROUPS_TL SUBT
484 WHERE SUBB.ITEM_CATALOG_GROUP_ID = SUBT.ITEM_CATALOG_GROUP_ID
485 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
486 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
487 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
488 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)));
489
490 INSERT INTO MTL_ITEM_CATALOG_GROUPS_TL (
491 ITEM_CATALOG_GROUP_ID,
492 DESCRIPTION,
493 CREATION_DATE,
494 CREATED_BY,
495 LAST_UPDATE_DATE,
496 LAST_UPDATED_BY,
497 LAST_UPDATE_LOGIN,
498 LANGUAGE,
499 SOURCE_LANG
500 ) SELECT
501 B.ITEM_CATALOG_GROUP_ID,
502 B.DESCRIPTION,
503 B.CREATION_DATE,
504 B.CREATED_BY,
505 B.LAST_UPDATE_DATE,
506 B.LAST_UPDATED_BY,
507 B.LAST_UPDATE_LOGIN,
508 L.LANGUAGE_CODE,
509 B.SOURCE_LANG
510 FROM MTL_ITEM_CATALOG_GROUPS_TL B,
511 FND_LANGUAGES L
512 WHERE L.INSTALLED_FLAG in ('I', 'B')
513 AND B.LANGUAGE = userenv('LANG')
514 AND NOT EXISTS (SELECT NULL
515 FROM MTL_ITEM_CATALOG_GROUPS_TL T
516 WHERE T.ITEM_CATALOG_GROUP_ID = B.ITEM_CATALOG_GROUP_ID
517 AND T.LANGUAGE = L.LANGUAGE_CODE);
518
519 END ADD_LANGUAGE;
520
521 END MTL_ITEM_CATALOG_GROUPS_UTIL;