[Home] [Help]
PACKAGE BODY: APPS.IBE_M_IBC_INT_PVT
Source
1 PACKAGE BODY IBE_M_IBC_INT_PVT AS
2 /* $Header: IBEVIBCB.pls 120.0 2005/05/30 02:19:14 appldev noship $ */
3
4 g_label_code CONSTANT VARCHAR2(30) := 'IBE';
5 g_association_type_code CONSTANT VARCHAR2(30) := 'IBE_MEDIA_OBJECT';
6
7 FUNCTION getTransLang(p_citem_version_id IN NUMBER,
8 p_base_language IN VARCHAR2)
9 RETURN VARCHAR2
10 IS
11 CURSOR c_get_langs_csr(c_citem_version_id NUMBER,
12 c_base_lang VARCHAR2) IS
13 SELECT b.description
14 FROM ibc_citem_versions_tl a, fnd_languages_vl b
15 WHERE a.language = b.language_code
16 AND a.source_lang <> c_base_lang
17 AND a.source_lang = a.language
18 AND a.citem_version_id = c_citem_version_id;
19 l_langs VARCHAR2(2600) := NULL;
20 l_lang VARCHAR2(255) := NULL;
21 BEGIN
22 OPEN c_get_langs_csr(p_citem_version_id, p_base_language);
23 LOOP
24 FETCH c_get_langs_csr INTO l_lang;
25 EXIT WHEN c_get_langs_csr%NOTFOUND;
26 l_langs := l_langs || l_lang || ',';
27 END LOOP;
28 CLOSE c_get_langs_csr;
29 IF (l_langs IS NOT NULL) THEN
30 RETURN substr(l_langs,1,length(l_langs)-1);
31 END IF;
32 RETURN NULL;
33 END getTransLang;
34
35 FUNCTION getLang(p_citem_version_id IN NUMBER)
36 RETURN VARCHAR2
37 IS
38 CURSOR c_get_langs_csr(c_citem_version_id NUMBER) IS
39 SELECT b.description
40 FROM ibc_citem_versions_tl a, fnd_languages_vl b
41 WHERE a.language = b.language_code
42 AND a.source_lang = a.language
43 AND a.citem_version_id = c_citem_version_id;
44 l_langs VARCHAR2(2600) := NULL;
45 l_lang VARCHAR2(255) := NULL;
46 BEGIN
47 OPEN c_get_langs_csr(p_citem_version_id);
48 LOOP
49 FETCH c_get_langs_csr INTO l_lang;
50 EXIT WHEN c_get_langs_csr%NOTFOUND;
51 l_langs := l_langs || l_lang || ',';
52 END LOOP;
53 CLOSE c_get_langs_csr;
54 IF (l_langs IS NOT NULL) THEN
55 RETURN substr(l_langs,1,length(l_langs)-1);
56 END IF;
57 RETURN NULL;
58 END getLang;
59
60 FUNCTION getLiveStatus(p_citem_id NUMBER, p_citem_version_id IN NUMBER)
61 RETURN VARCHAR2
62 IS
63 CURSOR c_get_live(c_citem_id NUMBER, c_item_version_id NUMBER) IS
64 SELECT 1
65 FROM ibc_citem_version_labels
66 WHERE citem_version_id = c_item_version_id
67 AND content_item_id = c_citem_id
68 AND label_code = 'IBE';
69 l_temp NUMBER;
70 BEGIN
71 OPEN c_get_live(p_citem_id, p_citem_version_id);
72 FETCH c_get_live INTO l_temp;
73 IF c_get_live%FOUND THEN
74 CLOSE c_get_live;
75 RETURN 'Y';
76 END IF;
77 CLOSE c_get_live;
78 RETURN 'N';
79 END getLiveStatus;
80
81 FUNCTION getLiveVersion(p_citem_id IN NUMBER)
82 RETURN NUMBER
83 IS
84 CURSOR c_get_live_version(c_citem_id NUMBER) IS
85 SELECT b.version_number
86 FROM ibc_citem_version_labels a, ibc_citem_versions_b b
87 WHERE a.citem_version_id = b.citem_version_id
88 AND a.content_item_id = c_citem_id
89 AND a.label_code = 'IBE';
90
91 l_version_number NUMBER := NULL;
92 BEGIN
93 OPEN c_get_live_version(p_citem_id);
94 FETCH c_get_live_version INTO l_version_number;
95 IF c_get_live_version%FOUND THEN
96 CLOSE c_get_live_version;
97 RETURN l_version_number;
98 END IF;
99 CLOSE c_get_live_version;
100 RETURN -1;
101 END getLiveVersion;
102
103 FUNCTION getStore(p_citem_id IN NUMBER)
104 RETURN VARCHAR2
105 IS
106 CURSOR c_get_store(c_item_key VARCHAR2) IS
107 SELECT decode(b.msite_id, 1, 'All', b.msite_name)
108 FROM ibe_dsp_lgl_phys_map a, ibe_msites_vl b
109 WHERE a.msite_id = b.msite_id and b.site_type = 'I'
110 AND a.content_item_key = c_item_key
111 AND a.attachment_id = -1;
112 -- Need to reconsider the length of the l_stores
113 -- as the length of the store name in ibe_msites_vl
114 -- is 4000. but the pl/sql package is only allow
115 -- 240 for now.
116 l_stores VARCHAR2(4000) := NULL;
117 l_store VARCHAR2(4000) := NULL;
118 BEGIN
119 OPEN c_get_store(to_char(p_citem_id));
120 LOOP
121 FETCH c_get_store INTO l_store;
122 EXIT WHEN c_get_store%NOTFOUND;
123 l_stores := l_stores || l_store || ',';
124 END LOOP;
125 CLOSE c_get_store;
126 IF (l_stores IS NOT NULL) THEN
127 RETURN substr(l_stores,1,length(l_stores)-1);
128 END IF;
129 RETURN NULL;
130 END getStore;
131
132 FUNCTION getAvalVersion(p_citem_id IN NUMBER)
133 RETURN NUMBER
134 IS
135 CURSOR c_get_aval_status(c_citem_id NUMBER) IS
136 SELECT MAX(a.version_number)
137 FROM ibc_citem_versions_b a
138 WHERE a.citem_version_status = 'APPROVED'
139 AND a.content_item_id = c_citem_id;
140 l_version_number NUMBER;
141 BEGIN
142 OPEN c_get_aval_status(p_citem_id);
143 FETCH c_get_aval_status INTO l_version_number;
144 IF (c_get_aval_status%FOUND) THEN
145 CLOSE c_get_aval_status;
146 RETURN l_version_number;
147 END IF;
148 CLOSE c_get_aval_status;
149 RETURN -1;
150 END getAvalVersion;
151
152 FUNCTION getAvalVersionId(p_citem_id IN NUMBER)
153 RETURN NUMBER
154 IS
155 CURSOR c_get_aval_status(c_citem_id NUMBER) IS
156 SELECT MAX(a.citem_version_id)
157 FROM ibc_citem_versions_b a
158 WHERE a.citem_version_status = 'APPROVED'
159 AND a.content_item_id = c_citem_id;
160 l_citem_version_id NUMBER;
161 BEGIN
162 OPEN c_get_aval_status(p_citem_id);
163 FETCH c_get_aval_status INTO l_citem_version_id;
164 IF (c_get_aval_status%FOUND) THEN
165 CLOSE c_get_aval_status;
166 RETURN l_citem_version_id;
167 END IF;
168 CLOSE c_get_aval_status;
169 RETURN -1;
170 END getAvalVersionId;
171
172 -- This procedure is used to move IBE label to the latest
173 -- version of the content items.
174 -- All content items must be labeled before calling this procedure.
175 PROCEDURE Batch_Update_Labels(
176 p_api_version IN NUMBER,
177 p_init_msg_list IN VARCHAR2,
178 p_commit IN VARCHAR2,
179 p_content_item_id_tbl IN JTF_NUMBER_TABLE,
180 p_version_number_tbl IN JTF_NUMBER_TABLE,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2)
184 IS
185 l_api_version NUMBER := 1.0;
186 l_api_name VARCHAR2(50) := 'Batch_Update_Labels';
187 l_return_status VARCHAR2(1);
188 l_msg_count NUMBER;
189 l_msg_data VARCHAR2(2000);
190
191 l_label_code VARCHAR2(30) := g_label_code;
192
193 l_debug VARCHAR2(1);
194 BEGIN
195 SAVEPOINT BATCH_UPDATE_LABELS_SAVE;
196 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
197 IF (l_debug = 'Y') THEN
198 IBE_UTIL.debug('IBE_M_IBC_INT_PVT.Batch_Update_Labels Starts +');
199 IBE_UTIL.debug('p_api_version = '||p_api_version);
200 IBE_UTIL.debug('p_init_msg_list = '||p_init_msg_list);
201 IBE_UTIL.debug('p_commit = '||p_commit);
202 IBE_UTIL.debug('p_content_item_id_tbl number:'||p_content_item_id_tbl.Count);
203 IBE_UTIL.debug('p_version_number_tbl:'||p_version_number_tbl.Count);
204 END IF;
205 IF NOT FND_API.Compatible_API_Call(l_api_version,
206 p_api_version,
207 l_api_name,
208 G_PKG_NAME) THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211 IF FND_API.to_Boolean(p_init_msg_list) THEN
212 FND_MSG_PUB.initialize;
213 END IF;
214 -- Initialize API return status to SUCCESS
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 IF (l_debug = 'Y') THEN
218 IBE_UTIL.debug('Calling Ibc_Cv_Label_Grp.Upsert_Cv_Labels starts');
219 IBE_UTIL.debug('p_label_code = '||l_label_code);
220 IBE_UTIL.debug('p_content_item_id_tbl.COUNT = '||p_content_item_id_tbl.COUNT);
221 IF (p_content_item_id_tbl.COUNT > 0) THEN
222 FOR i IN p_content_item_id_tbl.FIRST..p_content_item_id_tbl.LAST LOOP
223 IBE_UTIL.debug('p_content_item_id_tbl '||i||' = '||p_content_item_id_tbl(i));
224 END LOOP;
225 END IF;
226 IBE_UTIL.debug('p_version_number_tbl.COUNT = '||p_version_number_tbl.COUNT);
227 IF (p_version_number_tbl.COUNT > 0) THEN
228 FOR i IN p_version_number_tbl.FIRST..p_version_number_tbl.LAST LOOP
229 IBE_UTIL.debug('p_version_number_tbl '||i||' = '||p_version_number_tbl(i));
230 END LOOP;
231 END IF;
232 END IF;
233 Ibc_Cv_Label_Grp.Upsert_Cv_Labels(
234 p_label_code => l_label_code,
235 p_content_item_ids => p_content_item_id_tbl,
236 p_version_number => p_version_number_tbl,
237 p_commit => FND_API.G_FALSE,
238 p_api_version_number => 1.0,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data);
242 IF (l_debug = 'Y') THEN
243 IBE_UTIL.debug('After calling Ibc_Cv_Label_Grp.Upsert_Cv_Labels:'||x_return_status);
244 END IF;
245 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
246 IF (l_debug = 'Y') THEN
247 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Upsert_Cv_Labels');
248 for i in 1..x_msg_count loop
249 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
250 IBE_UTIL.debug(l_msg_data);
251 end loop;
252 END IF;
253 RAISE FND_API.G_EXC_ERROR;
254 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
255 IF (l_debug = 'Y') THEN
256 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Upsert_Cv_Labels');
257 for i in 1..x_msg_count loop
258 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
259 IBE_UTIL.debug(l_msg_data);
260 end loop;
261 END IF;
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264
265 IF (l_debug = 'Y') THEN
266 IBE_UTIL.debug('Before committing the work:'||p_commit);
267 END IF;
268 IF (FND_API.To_Boolean(p_commit)) THEN
269 COMMIT WORK;
270 END IF;
271 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
272 p_data => x_msg_data,
273 p_encoded => 'F');
274 IF (l_debug = 'Y') THEN
275 IBE_UTIL.debug('IBE_M_IBC_INT_PVT.Batch_Update_Labels Ends +');
276 END IF;
277 EXCEPTION
278 WHEN FND_API.G_EXC_ERROR THEN
279 ROLLBACK TO BATCH_UPDATE_LABELS_SAVE;
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
282 p_data => x_msg_data,
283 p_encoded => 'F');
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 ROLLBACK TO BATCH_UPDATE_LABELS_SAVE;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
288 p_data => x_msg_data,
289 p_encoded => 'F');
290 WHEN OTHERS THEN
291 ROLLBACK TO BATCH_UPDATE_LABELS_SAVE;
292 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
293 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
294 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
295 FND_MESSAGE.Set_Token('REASON', SQLERRM);
296 FND_MSG_PUB.Add;
297 IF (l_debug = 'Y') THEN
298 IBE_UTIL.debug('SQLCODE:'||SQLCODE);
299 IBE_UTIL.debug('SQLERRM:'||SQLERRM);
300 END IF;
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
303 THEN
304 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
305 END IF;
306 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
307 p_data => x_msg_data,
308 p_encoded => 'F');
309 END Batch_Update_Labels;
310
311 PROCEDURE Update_Label_Association(
312 p_api_version IN NUMBER,
313 p_init_msg_list IN VARCHAR2,
314 p_commit IN VARCHAR2,
315 p_old_content_item_id IN NUMBER,
316 p_old_version_number IN NUMBER,
317 p_new_content_item_id IN NUMBER,
318 p_new_version_number IN NUMBER,
319 p_media_object_id IN NUMBER,
320 p_association_type_code IN VARCHAR2,
321 x_return_status OUT NOCOPY VARCHAR2,
322 x_msg_count OUT NOCOPY NUMBER,
323 x_msg_data OUT NOCOPY VARCHAR2)
324 IS
325 l_api_version NUMBER := 1.0;
326 l_api_name VARCHAR2(50) := 'Update_Label_Association';
327 l_return_status VARCHAR2(1);
328 l_msg_count NUMBER;
329 l_msg_data VARCHAR2(2000);
330
331 l_old_item_ids JTF_NUMBER_TABLE;
332 l_new_item_ids JTF_NUMBER_TABLE;
333 l_assoc_type_codes JTF_VARCHAR2_TABLE_100;
334 l_assoc_objects JTF_VARCHAR2_TABLE_300;
335 l_assoc_objects1 JTF_VARCHAR2_TABLE_300;
336 l_move_flag VARCHAR2(1) := 'N';
337
338 l_i NUMBER;
339 l_old_item_id NUMBER;
340 l_content_item_id NUMBER;
341 l_item_version_id NUMBER;
342 l_version_number NUMBER;
343 l_association_type_code VARCHAR2(30);
344 l_label_code VARCHAR2(30) := g_label_code;
345 l_cv_label_rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
346 r_cv_label_rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
347 CURSOR c_get_version_id(c_content_item_id NUMBER,
348 c_version_number NUMBER) IS
349 SELECT citem_version_id
350 FROM ibc_citem_versions_b
351 WHERE content_item_id = c_content_item_id
352 AND version_number = c_version_number;
353
354 l_assoc_val1 VARCHAR2(254);
355 l_temp NUMBER;
356 CURSOR c_get_assoc_objects(c_content_item_id NUMBER,
357 c_label_code VARCHAR2) IS
358 SELECT ASSOCIATED_OBJECT_VAL1
359 FROM ibc_associations
360 WHERE content_item_id = c_content_item_id
361 AND association_type_code = c_label_code;
362
363 CURSOR c_label_flag(c_content_item_id NUMBER,
364 c_label_code VARCHAR2) IS
365 SELECT object_version_number
366 FROM IBC_CITEM_VERSION_LABELS
367 WHERE content_item_id = c_content_item_id
368 AND label_code = c_label_code;
369
370 CURSOR c_associations_flag(c_content_item_id NUMBER,
371 c_association_type_code VARCHAR2) IS
372 SELECT 1
373 FROM IBC_ASSOCIATIONS
374 WHERE content_item_id = c_content_item_id
375 AND association_type_code = c_association_type_code;
376
377 CURSOR c_association_flag(c_content_item_id NUMBER,
378 c_association_type_code VARCHAR2, c_assoc_object VARCHAR2) IS
379 SELECT 1
380 FROM IBC_ASSOCIATIONS
381 WHERE content_item_id = c_content_item_id
382 AND association_type_code = c_association_type_code
383 AND associated_object_val1 = c_assoc_object;
384
385 l_debug VARCHAR2(1);
386 BEGIN
387 SAVEPOINT UPDATE_LABEL_ASSOCIATION_SAVE;
388 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
389 IF (l_debug = 'Y') THEN
390 IBE_UTIL.debug('IBE_M_IBC_INT_PVT.Update_Label_Association starts +');
391 END IF;
392 IF NOT FND_API.Compatible_API_Call(l_api_version,
393 p_api_version,
394 l_api_name,
395 G_PKG_NAME) THEN
396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
397 END IF;
398 IF FND_API.to_Boolean(p_init_msg_list) THEN
399 FND_MSG_PUB.initialize;
400 END IF;
401 x_return_status := FND_API.g_ret_sts_success;
402 l_content_item_id := p_new_content_item_id;
403 l_version_number := p_new_version_number;
404 IF (l_debug = 'Y') THEN
405 IBE_UTIL.debug('Before getting version id');
406 IBE_UTIL.debug('l_content_item_id = '||l_content_item_id);
407 IBE_UTIL.debug('l_version_number = '||l_version_number);
408 END IF;
409 OPEN c_get_version_id(l_content_item_id, l_version_number);
410 FETCH c_get_version_id INTO l_item_version_id;
411 IF (c_get_version_id%NOTFOUND) THEN
412 CLOSE c_get_version_id;
413 IF (l_debug = 'Y') THEN
414 IBE_UTIL.debug('version id is not found');
415 END IF;
416 raise FND_API.G_EXC_ERROR;
417 END IF;
418 CLOSE c_get_version_id;
419 IF (l_debug = 'Y') THEN
420 IBE_UTIL.debug('After getting version id: '||l_item_version_id);
421 END IF;
422 l_association_type_code := NVL(p_association_type_code,
423 g_association_type_code);
424 IF (l_debug = 'Y') THEN
425 IBE_UTIL.debug('l_association_type_code = '||l_association_type_code);
426 END IF;
427 -- Process association
428 IF (p_old_content_item_id IS NULL) THEN
429 IF (l_debug = 'Y') THEN
430 IBE_UTIL.debug('p_old_content_item_id IS NULL');
431 END IF;
432 IF (p_media_object_id IS NULL) THEN
433 IF (l_debug = 'Y') THEN
434 IBE_UTIL.debug('p_media_object_id IS NULL, RAISE ERROR');
435 END IF;
436 raise FND_API.G_EXC_ERROR;
437 END IF;
438 l_old_item_ids := JTF_NUMBER_TABLE();
439 l_old_item_ids.extend(1);
440 l_old_item_ids(1) := NULL;
441 l_new_item_ids := JTF_NUMBER_TABLE();
442 l_new_item_ids.extend(1);
443 l_new_item_ids(1) := p_new_content_item_id;
444 l_assoc_type_codes := JTF_VARCHAR2_TABLE_100();
445 l_assoc_type_codes.extend(1);
446 l_assoc_type_codes(1) := g_association_type_code;
447 l_assoc_objects := JTF_VARCHAR2_TABLE_300();
448 l_assoc_objects.extend(1);
449 l_assoc_objects(1) := TO_CHAR(p_media_object_id);
450 l_assoc_objects1 := JTF_VARCHAR2_TABLE_300();
451 l_assoc_objects1.extend(1);
452 l_assoc_objects1(1) := NULL;
453 IF (l_debug = 'Y') THEN
454 IBE_UTIL.debug('l_old_item_ids(1) is NULL');
455 IBE_UTIL.debug('l_new_item_ids(1) = '||l_new_item_ids(1));
456 IBE_UTIL.debug('l_assoc_type_codes(1) = '||l_assoc_type_codes(1));
457 IBE_UTIL.debug('l_assoc_objects(1) = '||l_assoc_objects(1));
458 IBE_UTIL.debug('l_assoc_objects1(1) IS NULL');
459 END IF;
460 l_move_flag := 'Y';
461 ELSE
462 IF (l_debug = 'Y') THEN
463 IBE_UTIL.debug('p_old_content_item_id = '||p_old_content_item_id);
464 IBE_UTIL.debug('p_new_content_item_id = '||p_new_content_item_id);
465 END IF;
466 IF (p_old_content_item_id <> p_new_content_item_id) THEN
467 IF (l_debug = 'Y') THEN
468 IBE_UTIL.debug('Need to move the label');
469 END IF;
470 l_old_item_ids := JTF_NUMBER_TABLE();
471 l_old_item_ids.extend(1);
472 l_old_item_ids(1) := p_old_content_item_id;
473 l_new_item_ids := JTF_NUMBER_TABLE();
474 l_new_item_ids.extend(1);
475 l_new_item_ids(1) := p_new_content_item_id;
476 l_assoc_type_codes := JTF_VARCHAR2_TABLE_100();
477 l_assoc_type_codes.extend(1);
478 l_assoc_type_codes(1) := g_association_type_code;
479 IF (l_debug = 'Y') THEN
480 IBE_UTIL.debug('l_old_item_ids(1) = '||l_old_item_ids(1));
481 IBE_UTIL.debug('l_new_item_ids(1) = '||l_new_item_ids(1));
482 IBE_UTIL.debug('l_assoc_type_codes(1) = '||l_assoc_type_codes(1));
483 END IF;
484 IF (p_media_object_id IS NULL) THEN
485 IF (l_debug = 'Y') THEN
486 IBE_UTIL.debug('p_media_object_id IS NULL, move all for content item');
487 END IF;
488 --
489 l_assoc_objects := JTF_VARCHAR2_TABLE_300();
490 l_assoc_objects.extend(1);
491 l_assoc_objects(1) := NULL;
492 l_assoc_objects1 := JTF_VARCHAR2_TABLE_300();
493 l_assoc_objects1.extend(1);
494 l_assoc_objects1(1) := NULL;
495 l_move_flag := 'Y';
496 ELSE
497 IF (l_debug = 'Y') THEN
498 IBE_UTIL.debug('p_media_object_id is NOT NULL, move specific for content item');
499 END IF;
500 l_assoc_objects := JTF_VARCHAR2_TABLE_300();
501 l_assoc_objects.extend(1);
502 l_assoc_objects(1) := TO_CHAR(p_media_object_id);
503 IF (l_debug = 'Y') THEN
504 IBE_UTIL.debug('l_assoc_objects(1) = '||l_assoc_objects(1));
505 END IF;
506 l_assoc_objects1 := JTF_VARCHAR2_TABLE_300();
507 l_assoc_objects1.extend(1);
508 l_assoc_objects1(1) := NULL;
509 l_move_flag := 'Y';
510 END IF;
511 END IF;
512 END IF;
513 IF (l_debug = 'Y') THEN
514 IBE_UTIL.debug('l_move_flag = '||l_move_flag);
515 END IF;
516 IF (l_move_flag = 'Y') THEN
517 IF (l_debug = 'Y') THEN
518 IBE_UTIL.debug('Calling Ibc_Associations_Grp.Move_Associations');
519 END IF;
520 Ibc_Associations_Grp.Move_Associations (
521 p_api_version => 1.0,
522 p_init_msg_list => FND_API.G_FALSE,
523 p_commit => FND_API.G_FALSE,
524 p_old_content_item_ids => l_old_item_ids,
525 p_new_content_item_ids => l_new_item_ids,
526 p_assoc_type_codes => l_assoc_type_codes,
527 p_assoc_objects1 => l_assoc_objects,
528 p_assoc_objects2 => l_assoc_objects1,
529 p_assoc_objects3 => l_assoc_objects1,
530 p_assoc_objects4 => l_assoc_objects1,
531 p_assoc_objects5 => l_assoc_objects1,
532 x_return_status => x_return_status,
533 x_msg_count => x_msg_count,
534 x_msg_data => x_msg_data);
535 IF (l_debug = 'Y') THEN
536 IBE_UTIL.debug('After calling Ibc_Associations_Grp.Move_Associations:'
537 ||x_return_status);
538 END IF;
539 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
540 IF (l_debug = 'Y') THEN
541 IBE_UTIL.debug('Error in Ibc_Associations_Grp.Move_Associations');
542 for i in 1..x_msg_count loop
543 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
544 IBE_UTIL.debug(l_msg_data);
545 end loop;
546 END IF;
547 RAISE FND_API.G_EXC_ERROR;
548 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
549 IF (l_debug = 'Y') THEN
550 IBE_UTIL.debug('Error in Ibc_Associations_Grp.Move_Associations');
551 for i in 1..x_msg_count loop
552 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
553 IBE_UTIL.debug(l_msg_data);
554 end loop;
555 END IF;
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END IF;
558 END IF;
559
560 -- Process label
561 IF (l_debug = 'Y') THEN
562 IBE_UTIL.debug('Process label starts');
563 END IF;
564 IF (p_old_content_item_id IS NOT NULL) AND
565 (p_old_content_item_id = p_new_content_item_id) THEN
566 -- Change iStore label to the new version of
567 -- the same content item
568 IF (l_debug = 'Y') THEN
569 IBE_UTIL.debug('Find label version, p_old_content_item_id = p_new_content_item_id');
570 IBE_UTIL.debug('p_old_content_item_id = '||p_old_content_item_id);
571 IBE_UTIL.debug('p_new_content_item_id = '||p_new_content_item_id);
572 IBE_UTIL.debug('l_content_item_id = '||l_content_item_id);
573 IBE_UTIL.debug('l_label_code = '||l_label_code);
574 END IF;
575 OPEN c_label_flag(l_content_item_id,l_label_code);
576 FETCH c_label_flag INTO l_temp;
577 IF (c_label_flag%NOTFOUND) THEN
578 CLOSE c_label_flag;
579 IF (l_debug = 'Y') THEN
580 IBE_UTIL.debug('Cannot find label item, exception');
581 END IF;
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583 END IF;
584 CLOSE c_label_flag;
585 IF (l_debug = 'Y') THEN
586 IBE_UTIL.debug('After finding label version');
587 END IF;
588 l_cv_label_rec.content_item_id := l_content_item_id;
589 l_cv_label_rec.citem_version_id := l_item_version_id;
590 l_cv_label_rec.label_code := l_label_code;
591 l_cv_label_rec.last_updated_by := FND_GLOBAL.user_id;
592 l_cv_label_rec.last_update_date := SYSDATE;
593 l_cv_label_rec.object_version_number := l_temp;
594 l_cv_label_rec.last_update_login := FND_GLOBAL.login_id;
595 IF (l_debug = 'Y') THEN
596 IBE_UTIL.debug('Before calling Ibc_Cv_Label_Grp.Update_CV_Label');
597 IBE_UTIL.debug('l_cv_label_rec.content_item_id = '||l_cv_label_rec.content_item_id);
598 IBE_UTIL.debug('l_cv_label_rec.citem_version_id = '||l_cv_label_rec.citem_version_id);
599 IBE_UTIL.debug('l_cv_label_rec.label_code = '||l_cv_label_rec.label_code);
600 IBE_UTIL.debug('l_cv_label_rec.last_updated_by = '||l_cv_label_rec.last_updated_by);
601 IBE_UTIL.debug('l_cv_label_rec.object_version_number = '
602 ||l_cv_label_rec.object_version_number);
603 END IF;
604 Ibc_Cv_Label_Grp.Update_CV_Label(
605 p_api_version_number => 1.0,
606 P_Init_Msg_List => FND_API.G_FALSE,
607 P_Commit => FND_API.G_FALSE,
608 P_CV_Label_Rec => l_cv_label_rec,
609 x_CV_Label_Rec => r_cv_label_rec,
610 x_return_status => x_return_status,
611 x_msg_count => x_msg_count,
612 x_msg_data => x_msg_data);
613 IF (l_debug = 'Y') THEN
614 IBE_UTIL.debug('After calling Ibc_Cv_Label_Grp.Update_CV_Label:'||x_return_status);
615 END IF;
616 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
617 IF (l_debug = 'Y') THEN
618 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Update_CV_Label');
619 for i in 1..x_msg_count loop
620 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
621 IBE_UTIL.debug(l_msg_data);
622 end loop;
623 END IF;
624 RAISE FND_API.G_EXC_ERROR;
625 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
626 IF (l_debug = 'Y') THEN
627 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Update_CV_Label');
628 for i in 1..x_msg_count loop
629 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
630 IBE_UTIL.debug(l_msg_data);
631 end loop;
632 END IF;
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634 END IF;
635 ELSE
636 IF (p_old_content_item_id IS NOT NULL) THEN
637 -- Check the old content item has association or not
638 -- If not, remove the label
639 IF (l_debug = 'Y') THEN
640 IBE_UTIL.debug('Check the old content item has association or not');
641 END IF;
642 l_temp := 0;
643 l_old_item_id := p_old_content_item_id;
644 IF (l_debug = 'Y') THEN
645 IBE_UTIL.debug('Check association');
646 IBE_UTIL.debug('l_old_item_id = '||l_old_item_id);
647 IBE_UTIL.debug('l_association_type_code = '||l_association_type_code);
648 END IF;
649 OPEN c_associations_flag(l_old_item_id,
650 l_association_type_code);
651 FETCH c_associations_flag INTO l_temp;
652 IF c_associations_flag%NOTFOUND THEN
653 l_temp := 0;
654 END IF;
655 CLOSE c_associations_flag;
656 IF (l_debug = 'Y') THEN
657 IBE_UTIL.debug('After checking old association:'||l_temp);
658 END IF;
659 IF (l_temp <> 1) THEN
660 IF (l_debug = 'Y') THEN
661 IBE_UTIL.debug('Calling Ibc_Cv_Label_Grp.Delete_CV_Label');
662 IBE_UTIL.debug('p_label_code = '||l_label_code);
663 IBE_UTIL.debug('p_content_item_id = '||l_old_item_id);
664 END IF;
665 Ibc_Cv_Label_Grp.Delete_CV_Label(
666 P_Api_Version_Number => 1.0,
667 P_Init_Msg_List => FND_API.G_FALSE,
668 P_Commit => FND_API.G_FALSE,
669 p_label_code => l_label_code,
670 p_content_item_id => l_old_item_id,
671 x_return_status => x_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data);
674 IF (l_debug = 'Y') THEN
675 IBE_UTIL.debug('After calling Ibc_Cv_Label_Grp.Delete_CV_Label:'||x_return_status);
676 END IF;
677 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
678 IF (l_debug = 'Y') THEN
679 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Delete_CV_Label');
680 for i in 1..x_msg_count loop
681 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
682 IBE_UTIL.debug(l_msg_data);
683 end loop;
684 END IF;
685 RAISE FND_API.G_EXC_ERROR;
686 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
687 IF (l_debug = 'Y') THEN
688 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Delete_CV_Label');
689 for i in 1..x_msg_count loop
690 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
691 IBE_UTIL.debug(l_msg_data);
692 end loop;
693 END IF;
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 END IF;
696 END IF;
697 END IF;
698 l_temp := 0;
699 IF (l_debug = 'Y') THEN
700 IBE_UTIL.debug('Before checking label');
701 IBE_UTIL.debug('l_content_item_id = '||l_content_item_id);
702 IBE_UTIL.debug('l_label_code = '||l_label_code);
703 END IF;
704 OPEN c_label_flag(l_content_item_id,l_label_code);
705 FETCH c_label_flag INTO l_temp;
706 IF (c_label_flag%NOTFOUND) THEN
707 l_temp := -1;
708 END IF;
709 CLOSE c_label_flag;
710 IF (l_debug = 'Y') THEN
711 IBE_UTIL.debug('After checking label:'||l_temp);
712 END IF;
713 l_cv_label_rec.content_item_id := l_content_item_id;
714 l_cv_label_rec.citem_version_id := l_item_version_id;
715 l_cv_label_rec.label_code := l_label_code;
716 l_cv_label_rec.last_updated_by := FND_GLOBAL.user_id;
717 l_cv_label_rec.last_update_date := SYSDATE;
718 l_cv_label_rec.object_version_number := l_temp;
719 l_cv_label_rec.last_update_login := FND_GLOBAL.login_id;
720 IF (l_temp >= 0) THEN
721 IF (l_debug = 'Y') THEN
722 IBE_UTIL.debug('Calling Ibc_Cv_Label_Grp.Update_CV_Label');
723 IBE_UTIL.debug('P_CV_Label_Rec.content_item_id = '||l_cv_label_rec.content_item_id);
724 IBE_UTIL.debug('P_CV_Label_Rec.citem_version_id = '||l_cv_label_rec.citem_version_id);
725 IBE_UTIL.debug('P_CV_Label_Rec.label_code = '||l_cv_label_rec.label_code);
726 IBE_UTIL.debug('P_CV_Label_Rec.last_updated_by = '
727 ||l_cv_label_rec.last_updated_by);
728 IBE_UTIL.debug('P_CV_Label_Rec.last_update_date = '
729 ||to_char(l_cv_label_rec.last_update_date,'MM-DD-RRRR HH24:MI:SS'));
730 IBE_UTIL.debug('P_CV_Label_Rec.object_version_number = '
731 ||l_cv_label_rec.object_version_number);
732 IBE_UTIL.debug('P_CV_Label_Rec.last_update_login = '
733 ||l_cv_label_rec.last_update_login);
734 END IF;
735 Ibc_Cv_Label_Grp.Update_CV_Label(
736 p_api_version_number => 1.0,
737 P_Init_Msg_List => FND_API.G_FALSE,
738 P_Commit => FND_API.G_FALSE,
739 P_CV_Label_Rec => l_cv_label_rec,
740 x_CV_Label_Rec => r_cv_label_rec,
741 x_return_status => x_return_status,
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data);
744 IF (l_debug = 'Y') THEN
745 IBE_UTIL.debug('After calling Ibc_Cv_Label_Grp.Update_CV_Label:'||x_return_status);
746 END IF;
747 ELSE
748 l_cv_label_rec.object_version_number := 1;
749 l_cv_label_rec.created_by := FND_GLOBAL.user_id;
750 l_cv_label_rec.creation_date := SYSDATE;
751 IF (l_debug = 'Y') THEN
752 IBE_UTIL.debug('Calling Ibc_Cv_Label_Grp.Update_CV_Label');
753 IBE_UTIL.debug('P_CV_Label_Rec.content_item_id = '||l_cv_label_rec.content_item_id);
754 IBE_UTIL.debug('P_CV_Label_Rec.citem_version_id = '||l_cv_label_rec.citem_version_id);
755 IBE_UTIL.debug('P_CV_Label_Rec.label_code = '||l_cv_label_rec.label_code);
756 IBE_UTIL.debug('P_CV_Label_Rec.last_updated_by = '
757 ||l_cv_label_rec.last_updated_by);
758 IBE_UTIL.debug('P_CV_Label_Rec.last_update_date = '
759 ||to_char(l_cv_label_rec.last_update_date,'MM-DD-RRRR HH24:MI:SS'));
760 IBE_UTIL.debug('P_CV_Label_Rec.object_version_number = '
761 ||l_cv_label_rec.object_version_number);
762 IBE_UTIL.debug('P_CV_Label_Rec.last_update_login = '
763 ||l_cv_label_rec.last_update_login);
764 IBE_UTIL.debug('P_CV_Label_Rec.creation_date = '
765 ||to_char(l_cv_label_rec.creation_date,'MM-DD-RRRR HH24:MI:SS'));
766 IBE_UTIL.debug('P_CV_Label_Rec.created_by = '||l_cv_label_rec.created_by);
767 IBE_UTIL.debug('Calling Ibc_Cv_Label_Grp.Create_CV_Label');
768 END IF;
769 Ibc_Cv_Label_Grp.Create_CV_Label(
770 p_api_version_number => 1.0,
771 P_Init_Msg_List => FND_API.G_FALSE,
772 P_Commit => FND_API.G_FALSE,
773 P_CV_Label_Rec => l_cv_label_rec,
774 x_CV_Label_Rec => r_cv_label_rec,
775 x_return_status => x_return_status,
776 x_msg_count => x_msg_count,
777 x_msg_data => x_msg_data);
778 IF (l_debug = 'Y') THEN
779 IBE_UTIL.debug('After calling Ibc_Cv_Label_Grp.Create_CV_Label:'||x_return_status);
780 END IF;
781 END IF;
782 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
783 IF (l_debug = 'Y') THEN
784 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Update_CV_Label/Create_CV_Label');
785 for i in 1..x_msg_count loop
786 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
787 IBE_UTIL.debug(l_msg_data);
788 end loop;
789 END IF;
790 RAISE FND_API.G_EXC_ERROR;
791 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
792 IF (l_debug = 'Y') THEN
793 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Update_CV_Label/Create_CV_Label');
794 for i in 1..x_msg_count loop
795 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
796 IBE_UTIL.debug(l_msg_data);
797 end loop;
798 END IF;
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801 END IF;
802 IF (l_debug = 'Y') THEN
803 IBE_UTIL.debug('Before committing work:'||p_commit);
804 END IF;
805 IF (FND_API.To_Boolean(p_commit)) THEN
806 COMMIT WORK;
807 END IF;
808 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
809 p_data => x_msg_data,
810 p_encoded => 'F');
811 IF (l_debug = 'Y') THEN
812 IBE_UTIL.debug('IBE_M_IBC_INT_PVT.Update_Label_Association ends +');
813 END IF;
814 EXCEPTION
815 WHEN FND_API.G_EXC_ERROR THEN
816 ROLLBACK TO UPDATE_LABEL_ASSOCIATION_SAVE;
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
819 p_data => x_msg_data,
820 p_encoded => 'F');
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 ROLLBACK TO UPDATE_LABEL_ASSOCIATION_SAVE;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
824 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
825 p_data => x_msg_data,
826 p_encoded => 'F');
827 WHEN OTHERS THEN
828 ROLLBACK TO UPDATE_LABEL_ASSOCIATION_SAVE;
829 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
830 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
831 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
832 FND_MESSAGE.Set_Token('REASON', SQLERRM);
833 FND_MSG_PUB.Add;
834 IF (l_debug = 'Y') THEN
835 IBE_UTIL.debug('SQLCODE:'||SQLCODE);
836 IBE_UTIL.debug('SQLERRM:'||SQLERRM);
837 END IF;
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
840 THEN
841 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
842 END IF;
843 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
844 p_data => x_msg_data,
845 p_encoded => 'F');
846 END Update_Label_Association;
847
848 PROCEDURE Delete_Label_Association(
849 p_api_version IN NUMBER,
850 p_init_msg_list IN VARCHAR2,
851 p_commit IN VARCHAR2,
852 p_content_item_id IN NUMBER,
853 p_version_number IN NUMBER,
854 p_media_object_id IN NUMBER,
855 p_association_type_code IN VARCHAR2,
856 x_return_status OUT NOCOPY VARCHAR2,
857 x_msg_count OUT NOCOPY NUMBER,
858 x_msg_data OUT NOCOPY VARCHAR2)
859 IS
860 l_api_version NUMBER := 1.0;
861 l_api_name VARCHAR2(50) := 'Delete_Label_Association';
862 l_return_status VARCHAR2(1);
863 l_msg_count NUMBER;
864 l_msg_data VARCHAR2(2000);
865
866 l_content_item_id NUMBER;
867 l_association_type_code VARCHAR2(30);
868 l_label_code VARCHAR2(30) := g_label_code;
869 l_temp NUMBER;
870 CURSOR c_associations_flag(c_content_item_id NUMBER,
871 c_association_type_code VARCHAR2) IS
872 SELECT 1
873 FROM IBC_ASSOCIATIONS
874 WHERE content_item_id = c_content_item_id
875 AND association_type_code = c_association_type_code;
876
877 l_debug VARCHAR2(1);
878 BEGIN
879 SAVEPOINT DELETE_LABEL_ASSOCIATION_SAVE;
880 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
881 IF (l_debug = 'Y') THEN
882 IBE_UTIL.debug('IBE_M_IBC_INT_PVT.Delete_Label_Association Starts +');
883 IBE_UTIL.debug('p_api_version = '||p_api_version);
884 IBE_UTIL.debug('p_init_msg_list = '||p_init_msg_list);
885 IBE_UTIL.debug('p_commit = '||p_commit);
886 IBE_UTIL.debug('p_content_item_id = '||p_content_item_id);
887 IBE_UTIL.debug('p_version_number = '||p_version_number);
888 IBE_UTIL.debug('p_media_object_id = '||p_media_object_id);
889 IBE_UTIL.debug('p_association_type_code = '||p_association_type_code);
890 END IF;
891 IF NOT FND_API.Compatible_API_Call(l_api_version,
892 p_api_version,
893 l_api_name,
894 G_PKG_NAME) THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 END IF;
897 IF FND_API.to_Boolean(p_init_msg_list) THEN
898 FND_MSG_PUB.initialize;
899 END IF;
900
901 x_return_status := FND_API.g_ret_sts_success;
902 l_content_item_id := p_content_item_id;
903 l_association_type_code := NVL(p_association_type_code,
904 g_association_type_code);
905 IF (l_debug = 'Y') THEN
906 IBE_UTIL.debug('Calling IBC_CITEM_ADMIN_GRP.delete_association starts');
907 IBE_UTIL.debug('p_content_item_id = '||l_content_item_id);
908 IBE_UTIL.debug('p_association_type_code = '||l_association_type_code);
909 IBE_UTIL.debug('p_associated_object_val1 = '||TO_CHAR(p_media_object_id));
910 END IF;
911 IBC_CITEM_ADMIN_GRP.delete_association(
912 p_content_item_id => l_content_item_id,
913 p_association_type_code => l_association_type_code,
914 p_associated_object_val1 => TO_CHAR(p_media_object_id),
915 p_commit => FND_API.g_false,
916 p_api_version_number => 1.0,
917 p_init_msg_list => FND_API.g_false,
918 x_return_status => x_return_status,
919 x_msg_count => x_msg_count,
920 x_msg_data => x_msg_data);
921 IF (l_debug = 'Y') THEN
922 IBE_UTIL.debug('Calling IBC_CITEM_ADMIN_GRP.delete_association ends:'||x_return_status);
923 END IF;
924 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
925 IF (l_debug = 'Y') THEN
926 IBE_UTIL.debug('Error in IBC_CITEM_ADMIN_GRP.delete_association');
927 for i in 1..x_msg_count loop
928 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
929 IBE_UTIL.debug(l_msg_data);
930 end loop;
931 END IF;
932 RAISE FND_API.G_EXC_ERROR;
933 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
934 IF (l_debug = 'Y') THEN
935 IBE_UTIL.debug('Error in IBC_CITEM_ADMIN_GRP.delete_association');
936 for i in 1..x_msg_count loop
937 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
938 IBE_UTIL.debug(l_msg_data);
939 end loop;
940 END IF;
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942 END IF;
943 l_temp := 0;
944 IF (l_debug = 'Y') THEN
945 IBE_UTIL.debug('Check the association for the content item');
946 IBE_UTIL.debug('l_content_item_id = '||l_content_item_id);
947 IBE_UTIL.debug('l_association_type_code = '||l_association_type_code);
948 END IF;
949 OPEN c_associations_flag(l_content_item_id,
950 l_association_type_code);
951 FETCH c_associations_flag INTO l_temp;
952 IF (c_associations_flag%NOTFOUND) THEN
953 l_temp := 0;
954 END IF;
955 CLOSE c_associations_flag;
956 IF (l_debug = 'Y') THEN
957 IBE_UTIL.debug('After checking association for the content item:'||l_temp);
958 END IF;
959 IF (l_temp <> 1) THEN
960 IF (l_debug = 'Y') THEN
961 IBE_UTIL.debug('Calling Ibc_Cv_Label_Grp.Delete_CV_Label');
962 IBE_UTIL.debug('p_label_code = '||l_label_code);
963 IBE_UTIL.debug('p_content_item_id = '||l_content_item_id);
964 END IF;
965 Ibc_Cv_Label_Grp.Delete_CV_Label(
966 P_Api_Version_Number => 1.0,
967 P_Init_Msg_List => FND_API.G_FALSE,
968 P_Commit => FND_API.G_FALSE,
969 p_label_code => l_label_code,
970 p_content_item_id => l_content_item_id,
971 x_return_status => x_return_status,
972 x_msg_count => x_msg_count,
973 x_msg_data => x_msg_data);
974 IF (l_debug = 'Y') THEN
975 IBE_UTIL.debug('After calling Ibc_Cv_Label_Grp.Delete_CV_Label:'||x_return_status);
976 END IF;
977 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
978 IF (l_debug = 'Y') THEN
979 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Delete_CV_Label');
980 for i in 1..x_msg_count loop
981 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
982 IBE_UTIL.debug(l_msg_data);
983 end loop;
984 END IF;
985 RAISE FND_API.G_EXC_ERROR;
986 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
987 IF (l_debug = 'Y') THEN
988 IBE_UTIL.debug('Error in Ibc_Cv_Label_Grp.Delete_CV_Label');
989 for i in 1..x_msg_count loop
990 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
991 IBE_UTIL.debug(l_msg_data);
992 end loop;
993 END IF;
994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995 END IF;
996 END IF;
997 IF (l_debug = 'Y') THEN
998 IBE_UTIL.debug('Before committing the result:'||p_commit);
999 END IF;
1000 IF (FND_API.To_Boolean(p_commit)) THEN
1001 COMMIT WORK;
1002 END IF;
1003 IF (l_debug = 'Y') THEN
1004 IBE_UTIL.debug('IBE_M_IBC_INT_PVT.Delete_Label_Association ends +');
1005 END IF;
1006 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1007 p_data => x_msg_data,
1008 p_encoded => 'F');
1009 EXCEPTION
1010 WHEN FND_API.G_EXC_ERROR THEN
1011 ROLLBACK TO DELETE_LABEL_ASSOCIATION_SAVE;
1012 x_return_status := FND_API.G_RET_STS_ERROR;
1013 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1014 p_data => x_msg_data,
1015 p_encoded => 'F');
1016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1017 ROLLBACK TO DELETE_LABEL_ASSOCIATION_SAVE;
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1020 p_data => x_msg_data,
1021 p_encoded => 'F');
1022 WHEN OTHERS THEN
1023 ROLLBACK TO DELETE_LABEL_ASSOCIATION_SAVE;
1024 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1025 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1026 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1027 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1028 FND_MSG_PUB.Add;
1029 IF (l_debug = 'Y') THEN
1030 IBE_UTIL.debug('SQLCODE:'||SQLCODE);
1031 IBE_UTIL.debug('SQLERRM:'||SQLERRM);
1032 END IF;
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1035 THEN
1036 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1037 END IF;
1038 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1039 p_data => x_msg_data,
1040 p_encoded => 'F');
1041 END Delete_Label_Association;
1042
1043 PROCEDURE Get_Object_Name(
1044 p_association_type_code IN VARCHAR2,
1045 p_associated_object_val1 IN VARCHAR2,
1046 p_associated_object_val2 IN VARCHAR2,
1047 p_associated_object_val3 IN VARCHAR2,
1048 p_associated_object_val4 IN VARCHAR2,
1049 p_associated_object_val5 IN VARCHAR2,
1050 x_object_name OUT NOCOPY VARCHAR2,
1051 x_object_code OUT NOCOPY VARCHAR2,
1052 x_return_status OUT NOCOPY VARCHAR2,
1053 x_msg_count OUT NOCOPY NUMBER,
1054 x_msg_data OUT NOCOPY VARCHAR2)
1055 IS
1056 l_api_name CONSTANT VARCHAR2(30) := 'Get_Object_Name';
1057 CURSOR c_get_media_obj_csr(c_media_obj_id IN NUMBER) IS
1058 SELECT item_name, access_name
1059 FROM JTF_AMV_ITEMS_VL
1060 WHERE item_id = c_media_obj_id;
1061 BEGIN
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063 IF p_association_type_code = g_association_type_code THEN
1064 OPEN c_get_media_obj_csr(p_associated_object_val1);
1065 FETCH c_get_media_obj_csr INTO x_object_name, x_object_code;
1066 CLOSE c_get_media_obj_csr;
1067 END IF;
1068 EXCEPTION
1069 WHEN FND_API.G_EXC_ERROR THEN
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1072 p_data => x_msg_data);
1073 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1075 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1076 p_data => x_msg_data);
1077 WHEN OTHERS THEN
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1080 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1081 END IF;
1082 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1083 p_data => x_msg_data);
1084 END Get_Object_Name;
1085
1086 END IBE_M_IBC_INT_PVT;