[Home] [Help]
PACKAGE BODY: APPS.IBC_ASSOCIATIONS_GRP
Source
1 PACKAGE BODY Ibc_Associations_Grp AS
2 /* $Header: ibcgassb.pls 120.2 2012/02/15 06:10:26 rsatyava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBC_ASSOCIATIONS_GRP';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcgassb.pls';
6
7 --------------------------------------------------------------------------------
8 -- Start of comments
9 -- API name : Create_Association
10 -- Type : Group
11 -- Pre-reqs : None
12 -- Function : Create an association mapping between an External object
13 -- and a content item (optionally, a particular version).
14 --------------------------------------------------------------------------------
15 PROCEDURE Create_Association (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2,
18 p_commit IN VARCHAR2,
19 p_assoc_type_code IN VARCHAR2,
20 p_assoc_object1 IN VARCHAR2,
21 p_assoc_object2 IN VARCHAR2,
22 p_assoc_object3 IN VARCHAR2,
23 p_assoc_object4 IN VARCHAR2,
24 p_assoc_object5 IN VARCHAR2,
25 p_content_item_id IN NUMBER,
26 p_citem_version_id IN NUMBER,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2
30 ) AS
31 --******** local variable for standards **********
32 l_api_name CONSTANT VARCHAR2(30) := 'Create_Association';
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_row_id VARCHAR2(250);
35 --
36 l_assoc_id NUMBER;
37 --
38 CURSOR Check_Duplicate_CItem IS
39 SELECT association_id
40 FROM IBC_ASSOCIATIONS
41 WHERE association_type_code = p_assoc_type_code
42 AND associated_object_val1 = p_assoc_object1
43 AND NVL(associated_object_val2, '0') = NVL(p_assoc_object2, '0')
44 AND NVL(associated_object_val3, '0') = NVL(p_assoc_object3, '0')
45 AND NVL(associated_object_val4, '0') = NVL(p_assoc_object4, '0')
46 AND NVL(associated_object_val5, '0') = NVL(p_assoc_object5, '0')
47 AND content_item_id = p_content_item_id
48 AND NVL(citem_version_id, '0') = NVL(p_citem_version_id, '0');
49
50 BEGIN
51 -- ******************* Standard Begins *******************
52 -- Standard Start of API savepoint
53 SAVEPOINT CREATE_ASSOCIATIONS_PT;
54
55 -- Standard call to check for call compatibility.
56 IF NOT Fnd_Api.Compatible_API_Call (
57 l_api_version,
58 p_api_version,
59 l_api_name,
60 G_PKG_NAME)
61 THEN
62 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64 -- Initialize message list if p_init_msg_list is set to TRUE.
65 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
66 Fnd_Msg_Pub.initialize;
67 END IF;
68
69 -- Initialize API return status to success
70 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
71
72 --******************* Real Logic Start *********************
73
74 -- Check for Duplicates
75 OPEN Check_Duplicate_CItem;
76 FETCH Check_Duplicate_CItem INTO l_assoc_id;
77 IF (Check_Duplicate_CItem%FOUND) THEN
78 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
79 Fnd_Message.Set_Name('IBC', 'DUPLICATE_ASSOCIATION');
80 Fnd_Msg_Pub.ADD;
81 END IF;
82 CLOSE Check_Duplicate_CItem;
83 RAISE Fnd_Api.G_EXC_ERROR;
84 END IF;
85 CLOSE Check_Duplicate_CItem;
86
87 -- Validate Association Type
88 IF (Ibc_Validate_Pvt.isValidAssocType(p_assoc_type_code) = Fnd_Api.g_false) THEN
89 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
90 Fnd_Message.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
91 Fnd_Message.Set_token('ASSOC_TYPE_CODE', p_assoc_type_code);
92 Fnd_Msg_Pub.ADD;
93 END IF;
94 RAISE Fnd_Api.G_EXC_ERROR;
95 END IF;
96
97 -- Validate Citem Id
98 IF (Ibc_Validate_Pvt.isValidCitem(p_content_item_id) = Fnd_Api.g_false) THEN
99 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
100 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
101 Fnd_Message.Set_token('CITEM_ID', p_content_item_id);
102 Fnd_Msg_Pub.ADD;
103 END IF;
104 RAISE Fnd_Api.G_EXC_ERROR;
105 END IF;
106
107 -- Validate Citem version id
108 IF (p_citem_version_id IS NOT NULL AND
109 IBC_VALIDATE_PVT.isValidCitemVerForCitem(p_content_item_id, p_citem_version_id) = FND_API.g_false)
110 THEN
111 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
112 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
113 Fnd_Message.Set_token('CITEM_VERSION_ID', p_citem_version_id);
114 Fnd_Msg_Pub.ADD;
115 END IF;
116 RAISE Fnd_Api.G_EXC_ERROR;
117 END IF;
118
119
120 -- Insert into table
121 l_assoc_id := NULL;
122 Ibc_Associations_Pkg.insert_row (
123 px_association_id => l_assoc_id
124 ,p_content_item_id => p_content_item_id
125 ,p_citem_version_id => p_citem_version_id
126 ,p_association_type_code => p_assoc_type_code
127 ,p_associated_object_val1 => p_assoc_object1
128 ,p_associated_object_val2 => p_assoc_object2
129 ,p_associated_object_val3 => p_assoc_object3
130 ,p_associated_object_val4 => p_assoc_object4
131 ,p_associated_object_val5 => p_assoc_object5
132 ,p_object_version_number => G_OBJ_VERSION_DEFAULT
133 ,x_rowid => l_row_id
134 );
135
136 -- Log action
137 Ibc_Utilities_Pvt.log_action(
138 p_activity => Ibc_Utilities_Pvt.G_ALA_CREATE
139 ,p_parent_value => p_content_item_id
140 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
141 ,p_object_value1 => p_assoc_object1
142 ,p_object_value2 => p_assoc_object2
143 ,p_object_value3 => p_assoc_object3
144 ,p_object_value4 => p_assoc_object4
145 ,p_object_value5 => p_assoc_object5
146 ,p_description => 'Created association of type: '|| p_assoc_type_code ||
147 ' with association id: '|| l_assoc_id ||
148 ' citem id: ' || p_content_item_id ||
149 ' citem version id: ' || p_citem_version_id
150 );
151
152 --******************* Real Logic End *********************
153 -- Standard check of p_commit.
154 IF (Fnd_Api.To_Boolean(p_commit)) THEN
155 COMMIT WORK;
156 END IF;
157 -- Standard call to get message count and if count=1, get the message
158 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
159 p_data => x_msg_data);
160 EXCEPTION
161 WHEN Fnd_Api.G_EXC_ERROR THEN
162 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
163 x_return_status := Fnd_Api.G_RET_STS_ERROR;
164 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
165 p_data => x_msg_data);
166 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
167 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
168 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
169 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
170 p_data => x_msg_data);
171 WHEN OTHERS THEN
172 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
173 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
174 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
177 END IF;
178 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
179 p_data => x_msg_data);
180 END Create_Association;
181
182
183
184 --------------------------------------------------------------------------------
185 -- Start of comments
186 -- API name : Delete_Association
187 -- Type : Group
188 -- Pre-reqs : None
189 -- Function : Delete an association mapping between an External object
190 -- and a content item.
191 --------------------------------------------------------------------------------
192 PROCEDURE Delete_Association (
193 p_api_version IN NUMBER,
194 p_init_msg_list IN VARCHAR2,
195 p_commit IN VARCHAR2,
196 p_assoc_type_code IN VARCHAR2,
197 p_assoc_object1 IN VARCHAR2,
198 p_assoc_object2 IN VARCHAR2,
199 p_assoc_object3 IN VARCHAR2,
200 p_assoc_object4 IN VARCHAR2,
201 p_assoc_object5 IN VARCHAR2,
202 p_content_item_id IN NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2
206 ) AS
207 --******** local variable for standards **********
208 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Association';
209 l_api_version CONSTANT NUMBER := 1.0;
210 l_row_id VARCHAR2(250);
211 --
212 l_assoc_id NUMBER;
213 --
214 CURSOR Check_Assoc IS
215 SELECT association_id
216 FROM IBC_ASSOCIATIONS
217 WHERE association_type_code = p_assoc_type_code
218 AND associated_object_val1 = p_assoc_object1
219 AND NVL(associated_object_val2, '0') = NVL(p_assoc_object2, '0')
220 AND NVL(associated_object_val3, '0') = NVL(p_assoc_object3, '0')
221 AND NVL(associated_object_val4, '0') = NVL(p_assoc_object4, '0')
222 AND NVL(associated_object_val5, '0') = NVL(p_assoc_object5, '0')
223 AND content_item_id = p_content_item_id;
224
225 BEGIN
226 -- ******************* Standard Begins *******************
227 -- Standard Start of API savepoint
228 SAVEPOINT CREATE_ASSOCIATIONS_PT;
229
230 -- Standard call to check for call compatibility.
231 IF NOT Fnd_Api.Compatible_API_Call (
232 l_api_version,
233 p_api_version,
234 l_api_name,
235 G_PKG_NAME)
236 THEN
237 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239 -- Initialize message list if p_init_msg_list is set to TRUE.
240 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
241 Fnd_Msg_Pub.initialize;
242 END IF;
243
244 -- Initialize API return status to success
245 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
246
247 --******************* Real Logic Start *********************
248
249 -- Check if association exists
250 OPEN Check_Assoc;
251 FETCH Check_Assoc INTO l_assoc_id;
252 IF (Check_Assoc%NOTFOUND) THEN
253 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
254 Fnd_Message.Set_Name('IBC', 'NO_ASSOCIATION_FOUND');
255 Fnd_Msg_Pub.ADD;
256 END IF;
257 CLOSE Check_Assoc;
258 RAISE Fnd_Api.G_EXC_ERROR;
259 END IF;
260 CLOSE Check_Assoc;
261
262 -- Delete Entry
263 Ibc_Associations_Pkg.delete_row(
264 p_association_id => l_assoc_id
265 );
266
267 -- Log Action
268 Ibc_Utilities_Pvt.log_action(
269 p_activity => Ibc_Utilities_Pvt.G_ALA_REMOVE
270 ,p_parent_value => p_content_item_id
271 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
272 ,p_object_value1 => p_assoc_object1
273 ,p_object_value2 => p_assoc_object2
274 ,p_object_value3 => p_assoc_object3
275 ,p_object_value4 => p_assoc_object4
276 ,p_object_value5 => p_assoc_object5
277 ,p_description => 'Deleted association of type '|| p_assoc_type_code || ' and content item id ' || p_content_item_id
278 );
279
280 --******************* Real Logic End *********************
281 -- Standard check of p_commit.
282 IF (Fnd_Api.To_Boolean(p_commit)) THEN
283 COMMIT WORK;
284 END IF;
285 -- Standard call to get message count and if count=1, get the message
286 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
287 p_data => x_msg_data);
288 EXCEPTION
289 WHEN Fnd_Api.G_EXC_ERROR THEN
290 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
291 x_return_status := Fnd_Api.G_RET_STS_ERROR;
292 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
293 p_data => x_msg_data);
294 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
295 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
296 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
297 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
298 p_data => x_msg_data);
299 WHEN OTHERS THEN
300 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
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 END Delete_Association;
309
310
311 --------------------------------------------------------------------------------
312 -- Start of comments
313 -- API name : Update_Association
314 -- Type : Group
315 -- Pre-reqs : None
316 -- Function : Update an External object's association mapping with
317 -- a content item (optionally, a particular version).
318 --------------------------------------------------------------------------------
319 PROCEDURE Update_Association (
320 p_api_version IN NUMBER,
321 p_init_msg_list IN VARCHAR2,
322 p_commit IN VARCHAR2,
323 p_assoc_type_code IN VARCHAR2,
324 p_assoc_object1 IN VARCHAR2,
325 p_assoc_object2 IN VARCHAR2,
326 p_assoc_object3 IN VARCHAR2,
327 p_assoc_object4 IN VARCHAR2,
328 p_assoc_object5 IN VARCHAR2,
329 p_old_citem_id IN NUMBER,
330 p_new_citem_id IN NUMBER,
331 p_new_citem_ver_id IN NUMBER,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_msg_count OUT NOCOPY NUMBER,
334 x_msg_data OUT NOCOPY VARCHAR2
335 ) AS
336 --******** local variable for standards **********
337 l_api_name CONSTANT VARCHAR2(30) := 'Update_Association';
338 l_api_version CONSTANT NUMBER := 1.0;
339 l_row_id VARCHAR2(250);
340 --
341 l_assoc_id NUMBER;
342 l_content_item_id NUMBER;
343 l_tmp_id NUMBER;
344 --
345 CURSOR Check_Assoc IS
346 SELECT association_id
347 FROM IBC_ASSOCIATIONS
348 WHERE association_type_code = p_assoc_type_code
349 AND associated_object_val1 = p_assoc_object1
350 AND NVL(associated_object_val2, '0') = NVL(p_assoc_object2, '0')
351 AND NVL(associated_object_val3, '0') = NVL(p_assoc_object3, '0')
352 AND NVL(associated_object_val4, '0') = NVL(p_assoc_object4, '0')
353 AND NVL(associated_object_val5, '0') = NVL(p_assoc_object5, '0')
354 AND content_item_id = l_content_item_id;
355
356 BEGIN
357 -- ******************* Standard Begins *******************
358 -- Standard Start of API savepoint
359 SAVEPOINT CREATE_ASSOCIATIONS_PT;
360
361 -- Standard call to check for call compatibility.
362 IF NOT Fnd_Api.Compatible_API_Call (
363 l_api_version,
364 p_api_version,
365 l_api_name,
366 G_PKG_NAME)
367 THEN
368 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
369 END IF;
370 -- Initialize message list if p_init_msg_list is set to TRUE.
371 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
372 Fnd_Msg_Pub.initialize;
373 END IF;
374
375 -- Initialize API return status to success
376 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
377
378 --******************* Real Logic Start *********************
379
380 -- Check if OLD association mapping exists
381 l_content_item_id := p_old_citem_id;
382 OPEN Check_Assoc;
383 FETCH Check_Assoc INTO l_assoc_id;
384 IF (Check_Assoc%NOTFOUND) THEN
385 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
386 Fnd_Message.Set_Name('IBC', 'NO_ASSOCIATION_FOUND');
387 Fnd_Msg_Pub.ADD;
388 END IF;
389 CLOSE Check_Assoc;
390 RAISE Fnd_Api.G_EXC_ERROR;
391 END IF;
392 CLOSE Check_Assoc;
393
394 IF (p_old_citem_id <> p_new_citem_id) THEN
395 -- Check for duplicates with new citem id
396 l_content_item_id := p_new_citem_id;
397 OPEN Check_Assoc;
398 FETCH Check_Assoc INTO l_tmp_id;
399 IF (Check_Assoc%FOUND) THEN
400 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
401 Fnd_Message.Set_Name('IBC', 'DUPLICATE_ASSOCIATION');
402 Fnd_Msg_Pub.ADD;
403 END IF;
404 CLOSE Check_Assoc;
405 RAISE Fnd_Api.G_EXC_ERROR;
406 END IF;
407 CLOSE Check_Assoc;
408
409 -- Validate Citem Id
410 IF (Ibc_Validate_Pvt.isValidCitem(p_new_citem_id) = Fnd_Api.g_false) THEN
411 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
412 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
413 Fnd_Message.Set_token('CITEM_ID', p_new_citem_id);
414 Fnd_Msg_Pub.ADD;
415 END IF;
416 RAISE Fnd_Api.G_EXC_ERROR;
417 END IF;
418 END IF;
419
420 -- Validate Citem version id
421 IF (p_new_citem_ver_id IS NOT NULL AND
422 IBC_VALIDATE_PVT.isValidCitemVerForCitem(p_new_citem_id, p_new_citem_ver_id) = FND_API.g_false)
423 THEN
424 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
425 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
426 Fnd_Message.Set_token('CITEM_VERSION_ID', p_new_citem_ver_id);
427 Fnd_Msg_Pub.ADD;
428 END IF;
429 RAISE Fnd_Api.G_EXC_ERROR;
430 END IF;
431
432 -- UPDATE row
433 Ibc_Associations_Pkg.update_row (
434 p_association_id => l_assoc_id
435 ,p_content_item_id => p_new_citem_id
436 ,p_citem_version_id => p_new_citem_ver_id
437 );
438
439 -- Log Action
440 Ibc_Utilities_Pvt.log_action(
441 p_activity => Ibc_Utilities_Pvt.G_ALA_UPDATE
442 ,p_parent_value => p_new_citem_id
443 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
444 ,p_object_value1 => p_assoc_object1
445 ,p_object_value2 => p_assoc_object2
446 ,p_object_value3 => p_assoc_object3
447 ,p_object_value4 => p_assoc_object4
448 ,p_object_value5 => p_assoc_object5
449 ,p_description => 'Updated association of type '|| p_assoc_type_code ||
450 ' old citem id: ' || p_old_citem_id ||
451 ' new citem id: ' || p_new_citem_id ||
452 ' new version id: ' || p_new_citem_ver_id
453 );
454
455 --******************* Real Logic End *********************
456 -- Standard check of p_commit.
457 IF (Fnd_Api.To_Boolean(p_commit)) THEN
458 COMMIT WORK;
459 END IF;
460 -- Standard call to get message count and if count=1, get the message
461 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
462 p_data => x_msg_data);
463 EXCEPTION
464 WHEN Fnd_Api.G_EXC_ERROR THEN
465 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
466 x_return_status := Fnd_Api.G_RET_STS_ERROR;
467 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
468 p_data => x_msg_data);
469 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
470 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
471 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
472 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
473 p_data => x_msg_data);
474 WHEN OTHERS THEN
475 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
476 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
477 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
478 THEN
479 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
480 END IF;
481 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
482 p_data => x_msg_data);
483 END Update_Association;
484
485
486
487
488
489
490
491
492
493
494
495
496
497 PROCEDURE Move_Associations (
498 p_api_version IN NUMBER,
499 p_init_msg_list IN VARCHAR2,
500 p_commit IN VARCHAR2,
501 p_old_content_item_ids IN JTF_NUMBER_TABLE,
502 p_new_content_item_ids IN JTF_NUMBER_TABLE,
503 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
504 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
505 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
506 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
507 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
508 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
509 x_return_status OUT NOCOPY VARCHAR2,
510 x_msg_count OUT NOCOPY NUMBER,
511 x_msg_data OUT NOCOPY VARCHAR2
512 ) AS
513 BEGIN
514 Move_Associations (
515 p_api_version => p_api_version,
516 p_init_msg_list => p_init_msg_list,
517 p_commit => p_commit,
518 p_old_content_item_ids => p_old_content_item_ids,
519 p_new_content_item_ids => p_new_content_item_ids,
520 p_old_citem_version_ids => JTF_NUMBER_TABLE(p_old_content_item_ids.count),
521 p_new_citem_version_ids => JTF_NUMBER_TABLE(p_old_content_item_ids.count),
522 p_assoc_type_codes => p_assoc_type_codes,
523 p_assoc_objects1 => p_assoc_objects1,
524 p_assoc_objects2 => p_assoc_objects2,
525 p_assoc_objects3 => p_assoc_objects3,
526 p_assoc_objects4 => p_assoc_objects4,
527 p_assoc_objects5 => p_assoc_objects5,
528 x_return_status => x_return_status,
529 x_msg_count => x_msg_count,
530 x_msg_data => x_msg_data
531 );
532 EXCEPTION
533 WHEN OTHERS THEN
534 NULL;
535 END Move_Associations;
536
537 PROCEDURE Move_Associations (
538 p_api_version IN NUMBER,
539 p_init_msg_list IN VARCHAR2,
540 p_commit IN VARCHAR2,
541 p_old_content_item_ids IN JTF_NUMBER_TABLE,
542 p_new_content_item_ids IN JTF_NUMBER_TABLE,
543 p_old_citem_version_ids IN JTF_NUMBER_TABLE,
544 p_new_citem_version_ids IN JTF_NUMBER_TABLE,
545 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
546 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
547 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
548 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
549 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
550 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
551 x_return_status OUT NOCOPY VARCHAR2,
552 x_msg_count OUT NOCOPY NUMBER,
553 x_msg_data OUT NOCOPY VARCHAR2
554 ) AS
555 --******** local variable for standards **********
556 l_api_name CONSTANT VARCHAR2(30) := 'Move_Associations';
557 l_api_version CONSTANT NUMBER := 1.0;
558
559 l_temp_array_length NUMBER;
560 l_content_item_id NUMBER;
561 l_citem_version_id NUMBER;
562 l_association_type_code VARCHAR2(100);
563
564 l_assoc_id NUMBER;
565 l_rowid VARCHAR2(240);
566
567 --
568 -- Could not get this to work for some reason
569 --
570 -- CURSOR c1 IS
571 -- SELECT A.COLUMN_VALUE A_content_item_id
572 -- FROM TABLE(CAST(p_content_item_ids AS JTF_NUMBER_TABLE)) AS A
573 -- WHERE NOT EXISTS (SELECT NULL FROM IBC_CONTENT_ITEMS C
574 -- WHERE a.column_value=c.content_item_id);
575 --
576
577 CURSOR cur_old_citem IS
578 SELECT A.COLUMN_VALUE content_item_id
579 FROM TABLE(CAST(p_old_content_item_ids AS JTF_NUMBER_TABLE)) A
580 MINUS
581 SELECT content_item_id FROM IBC_CONTENT_ITEMS C;
582
583 CURSOR cur_new_citem IS
584 SELECT A.COLUMN_VALUE content_item_id
585 FROM TABLE(CAST(p_new_content_item_ids AS JTF_NUMBER_TABLE)) A
586 MINUS
587 SELECT content_item_id FROM IBC_CONTENT_ITEMS C;
588
589 CURSOR cur_old_citem_version IS
590 SELECT A.COLUMN_VALUE citem_version_id
591 FROM TABLE(CAST(p_old_citem_version_ids AS JTF_NUMBER_TABLE)) A
592 MINUS
593 SELECT citem_version_id FROM IBC_CITEM_VERSIONS_B C;
594
595 CURSOR cur_new_citem_version IS
596 SELECT A.COLUMN_VALUE citem_version_id
597 FROM TABLE(CAST(p_new_citem_version_ids AS JTF_NUMBER_TABLE)) A
598 MINUS
599 SELECT citem_version_id FROM IBC_CITEM_VERSIONS_B C;
600
601
602 CURSOR cur_assoc IS
603 SELECT A.COLUMN_VALUE association_type_code
604 FROM TABLE(CAST(p_assoc_type_codes AS JTF_VARCHAR2_TABLE_100)) A
605 MINUS
606 SELECT association_type_code FROM IBC_ASSOCIATION_TYPES_B C;
607
608 BEGIN
609 -- ******************* Standard Begins *******************
610 -- Standard Start of API savepoint
611 SAVEPOINT MOVE_ASSOCIATIONS_PT;
612
613 -- Standard call to check for call compatibility.
614 IF NOT Fnd_Api.Compatible_API_Call (
615 l_api_version,
616 p_api_version,
617 l_api_name,
618 G_PKG_NAME)
619 THEN
620 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
621 END IF;
622 -- Initialize message list if p_init_msg_list is set to TRUE.
623 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
624 Fnd_Msg_Pub.initialize;
625 END IF;
626
627 -- Initialize API return status to success
628 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
629
630 --******************* Real Logic Start *********************
631
632 l_temp_array_length := p_old_content_item_ids.COUNT;
633 IF ( (p_new_content_item_ids.COUNT <> l_temp_array_length) OR
634 (p_assoc_type_codes.COUNT <> l_temp_array_length) OR
635 (p_assoc_objects1.COUNT <> l_temp_array_length) OR
636 ( (p_assoc_objects2 IS NOT NULL) AND (p_assoc_objects2.COUNT <> l_temp_array_length) ) OR
637 ( (p_assoc_objects3 IS NOT NULL) AND (p_assoc_objects3.COUNT <> l_temp_array_length) ) OR
638 ( (p_assoc_objects4 IS NOT NULL) AND (p_assoc_objects4.COUNT <> l_temp_array_length) ) OR
639 ( (p_assoc_objects5 IS NOT NULL) AND (p_assoc_objects5.COUNT <> l_temp_array_length) ) ) THEN
640 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
641 Fnd_Message.Set_Name('IBC', 'IMPROPER_ARRAY');
642 Fnd_Msg_Pub.ADD;
643 END IF;
644 RAISE Fnd_Api.G_EXC_ERROR;
645 END IF;
646
647 -- Validate Old Citem Id
648 --
649 BEGIN
650
651 l_content_item_id := NULL;
652
653 OPEN cur_old_citem;
654 FETCH cur_old_citem INTO l_content_item_id;
655 CLOSE cur_old_citem;
656 IF l_content_item_id IS NOT NULL THEN
657 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
658 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
659 Fnd_Message.Set_token('CITEM_ID',l_content_item_id);
660 Fnd_Msg_Pub.ADD;
661 END IF;
662 RAISE Fnd_Api.G_EXC_ERROR;
663 END IF;
664
665 END;
666
667 -- Validate New Citem Id
668 --
669 BEGIN
670
671 l_content_item_id := NULL;
672
673 OPEN cur_new_citem;
674 FETCH cur_new_citem INTO l_content_item_id;
675 CLOSE cur_new_citem;
676 IF l_content_item_id IS NOT NULL THEN
677 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
678 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
679 Fnd_Message.Set_token('CITEM_ID',l_content_item_id);
680 Fnd_Msg_Pub.ADD;
681 END IF;
682 RAISE Fnd_Api.G_EXC_ERROR;
683 END IF;
684
685 END;
686
687
688 -- Validate Old Citem Version Id
689 --
690 BEGIN
691
692 l_citem_version_id := NULL;
693
694 OPEN cur_old_citem_version;
695 FETCH cur_old_citem_version INTO l_citem_version_id;
696 CLOSE cur_old_citem_version;
697 IF l_citem_version_id IS NOT NULL THEN
698 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
699 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
700 Fnd_Message.Set_token('CITEM_VERSION_ID',l_citem_version_id);
701 Fnd_Msg_Pub.ADD;
702 END IF;
703 RAISE Fnd_Api.G_EXC_ERROR;
704 END IF;
705
706 END;
707
708 -- Validate New Citem Id
709 --
710 BEGIN
711
712 l_citem_version_id := NULL;
713
714 OPEN cur_new_citem_version;
715 FETCH cur_new_citem_version INTO l_citem_version_id;
716 CLOSE cur_new_citem_version;
717 IF l_citem_version_id IS NOT NULL THEN
718 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
719 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
720 Fnd_Message.Set_token('CITEM_VERSION_ID',l_citem_version_id);
721 Fnd_Msg_Pub.ADD;
722 END IF;
723 RAISE Fnd_Api.G_EXC_ERROR;
724 END IF;
725
726 END;
727
728
729 -- Validate Association Type
730 --
731 BEGIN
732
733 l_association_type_code := NULL;
734
735 OPEN cur_assoc;
736 FETCH cur_assoc INTO l_association_type_code;
737 CLOSE cur_assoc;
738
739 IF (l_association_type_code IS NOT NULL) THEN
740 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
741 Fnd_Message.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
742 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_association_type_code);
743 Fnd_Msg_Pub.ADD;
744 END IF;
745 RAISE Fnd_Api.G_EXC_ERROR;
746 END IF;
747
748 END;
749
750 -- ,association_type_code = p_assoc_type_codes(i)
751 -- ,associated_object_val1 = DECODE(p_assoc_objects1(i),NULL,associated_object_val1,p_assoc_objects1(i))
752 -- ,associated_object_val2 = DECODE(p_assoc_objects2(i),NULL,associated_object_val2,p_assoc_objects2(i))
753 -- ,associated_object_val3 = DECODE(p_assoc_objects3(i),NULL,associated_object_val3,p_assoc_objects3(i))
754 -- ,associated_object_val4 = DECODE(p_assoc_objects4(i),NULL,associated_object_val4,p_assoc_objects4(i))
755 -- ,associated_object_val5 = DECODE(p_assoc_objects5(i),NULL,associated_object_val5,p_assoc_objects5(i))
756 --
757 -- Update
758 --
759 --DBMS_OUT NOCOPYPUT.put_line('Begin Successful....');
760
761 FORALL i IN p_old_content_item_ids.FIRST..p_old_content_item_ids.LAST
762 DELETE FROM IBC_ASSOCIATIONS
763 WHERE ROWID IN (
764 SELECT A.ROWID FROM IBC_ASSOCIATIONS A,
765 (
766 -- The below Select Statement Returns all the rows that will be updated
767 -- in the following Update Statement Which moves an Association from old_content_item_id
768 -- to the New Content Item id.
769 -- When moved if the New Content Item Id already has this association we don't want to error
770 -- OUT NOCOPY but merge the two row. Which means we will have to delete one row.
771 -- All the rows that r going to be updated
772 -- Make sure that the new row is not a Duplicate in the table
773 SELECT
774 association_type_code
775 ,associated_object_val1
776 ,associated_object_val2
777 ,associated_object_val3
778 ,associated_object_val4
779 ,associated_object_val5
780 FROM IBC_ASSOCIATIONS
781 WHERE CONTENT_ITEM_ID = p_old_content_item_ids(i)
782 AND citem_version_id = NVL(p_old_citem_version_ids(i),citem_version_id)
783 AND association_type_code = p_assoc_type_codes(i)
784 AND (associated_object_val1 = p_assoc_objects1(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1)
785 AND (associated_object_val2 = p_assoc_objects2(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects2(i),NULL,NVL(associated_object_val2,'1')) = '1')
786 AND (associated_object_val3 = p_assoc_objects3(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects3(i),NULL,NVL(associated_object_val3,'1')) = '1')
787 AND (associated_object_val4 = p_assoc_objects4(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects4(i),NULL,NVL(associated_object_val4,'1')) = '1')
788 AND (associated_object_val5 = p_assoc_objects5(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects5(i),NULL,NVL(associated_object_val5,'1')) = '1')
789 ) B
790 WHERE a.association_type_code = b.association_type_code
791 AND a.associated_object_val1 = b.associated_object_val1
792 AND NVL(a.associated_object_val2,'1') = NVL(b.associated_object_val2,'1')
793 AND NVL(a.associated_object_val3,'1') = NVL(b.associated_object_val3,'1')
794 AND NVL(a.associated_object_val4,'1') = NVL(b.associated_object_val4,'1')
795 AND NVL(a.associated_object_val5,'1') = NVL(b.associated_object_val5,'1')
796 AND a.CONTENT_ITEM_ID = p_new_content_item_ids(i)
797 AND citem_version_id = NVL(p_new_citem_version_ids(i),citem_version_id)
798 -- By Mistake if the user passes the same old and new Content Item Id and citem ver id
799 -- then Delete should not happen.
800 AND NOT ( (p_new_content_item_ids(i) = p_old_content_item_ids(i)
801 AND
802 p_new_citem_version_ids(i) IS NULL AND p_old_citem_version_ids(i) IS NULL
803 )
804 OR
805 (p_new_content_item_ids(i) = p_old_content_item_ids(i)
806 AND
807 p_new_citem_version_ids(i) = p_old_citem_version_ids(i)
808 )
809 )
810 );
811
812
813
814 FORALL i IN p_old_content_item_ids.FIRST..p_old_content_item_ids.LAST
815 UPDATE IBC_ASSOCIATIONS SET
816 CONTENT_ITEM_ID = p_new_content_item_ids(i)
817 ,CITEM_VERSION_ID = p_new_citem_version_ids(i)
818 ,OBJECT_VERSION_NUMBER = 1
819 ,LAST_UPDATE_DATE = SYSDATE
820 ,LAST_UPDATED_BY = Fnd_Global.user_id
821 ,LAST_UPDATE_LOGIN = Fnd_Global.login_id
822 WHERE CONTENT_ITEM_ID = p_old_content_item_ids(i)
823 AND citem_version_id = NVL(p_old_citem_version_ids(i),citem_version_id)
824 AND association_type_code = p_assoc_type_codes(i)
825 AND (associated_object_val1 = p_assoc_objects1(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1)
826 AND (associated_object_val2 = p_assoc_objects2(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects2(i),NULL,NVL(associated_object_val2,'1')) = '1')
827 AND (associated_object_val3 = p_assoc_objects3(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects3(i),NULL,NVL(associated_object_val3,'1')) = '1')
828 AND (associated_object_val4 = p_assoc_objects4(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects4(i),NULL,NVL(associated_object_val4,'1')) = '1')
829 AND (associated_object_val5 = p_assoc_objects5(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects5(i),NULL,NVL(associated_object_val5,'1')) = '1');
830
831 --DBMS_OUT NOCOPYPUT.put_line('Update Successful....');
832 --Will insert them.
833
834 BEGIN
835
836 FOR i IN p_old_content_item_ids.FIRST..p_old_content_item_ids.LAST
837 LOOP
838 IF SQL%BULK_ROWCOUNT(i) = 0 AND p_old_content_item_ids(i) IS NULL THEN
839 BEGIN
840
841 Ibc_Associations_Pkg.insert_row (
842 px_association_id => l_assoc_id
843 ,p_content_item_id => p_new_content_item_ids(i)
844 ,p_citem_version_id => p_new_citem_version_ids(i)
845 ,p_association_type_code => p_assoc_type_codes(i)
846 ,p_associated_object_val1 => p_assoc_objects1(i)
847 ,p_associated_object_val2 => p_assoc_objects2(i)
848 ,p_associated_object_val3 => p_assoc_objects3(i)
849 ,p_associated_object_val4 => p_assoc_objects4(i)
850 ,p_associated_object_val5 => p_assoc_objects5(i)
851 ,p_object_version_number => G_OBJ_VERSION_DEFAULT
852 ,x_rowid => l_rowid
853 );
854 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
855 -- If the User tries to Insert a Duplicate
856 -- this exception will be thrown 'cos there
857 -- is a Unique Index.
858 -- Ignore and proceed with the next Insert
859 NULL;
860 END;
861 END IF;
862 END LOOP;
863 END;
864
865
866 --******************* Real Logic End *********************
867 -- Standard check of p_commit.
868 IF (Fnd_Api.To_Boolean(p_commit)) THEN
869 COMMIT WORK;
870 END IF;
871 -- Standard call to get message count and if count=1, get the message
872 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count,
873 p_data => x_msg_data);
874 EXCEPTION
875 WHEN Fnd_Api.G_EXC_ERROR THEN
876 ROLLBACK TO MOVE_ASSOCIATIONS_PT;
877 x_return_status := Fnd_Api.G_RET_STS_ERROR;
878 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
879 p_data => x_msg_data);
880 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
881 ROLLBACK TO MOVE_ASSOCIATIONS_PT;
882 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
883 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
884 p_data => x_msg_data);
885 WHEN OTHERS THEN
886 ROLLBACK TO MOVE_ASSOCIATIONS_PT;
887 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
888 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
889 THEN
890 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
891 END IF;
892 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
893 p_data => x_msg_data);
894 END Move_Associations;
895
896 PROCEDURE Create_Associations (
897 p_api_version IN NUMBER,
898 p_init_msg_list IN VARCHAR2,
899 p_commit IN VARCHAR2,
900 p_content_item_ids IN JTF_NUMBER_TABLE,
901 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
902 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
903 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
904 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
905 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
906 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
907 x_return_status OUT NOCOPY VARCHAR2,
908 x_msg_count OUT NOCOPY NUMBER,
909 x_msg_data OUT NOCOPY VARCHAR2
910 ) AS
911 BEGIN
912 Create_Associations (
913 p_api_version => p_api_version,
914 p_init_msg_list => p_init_msg_list,
915 p_commit => p_commit,
916 p_content_item_ids => p_content_item_ids,
917 p_citem_version_ids => NULL,
918 p_assoc_type_codes => p_assoc_type_codes,
919 p_assoc_objects1 => p_assoc_objects1,
920 p_assoc_objects2 => p_assoc_objects2,
921 p_assoc_objects3 => p_assoc_objects3,
922 p_assoc_objects4 => p_assoc_objects4,
923 p_assoc_objects5 => p_assoc_objects5,
924 x_return_status => x_return_status,
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data
927 );
928 EXCEPTION
929 WHEN OTHERS THEN
930 RAISE;
931 END Create_Associations;
932
933 PROCEDURE Create_Associations (
934 p_api_version IN NUMBER,
935 p_init_msg_list IN VARCHAR2,
936 p_commit IN VARCHAR2,
937 p_content_item_ids IN JTF_NUMBER_TABLE,
938 p_citem_version_ids IN JTF_NUMBER_TABLE,
939 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
940 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
941 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
942 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
943 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
944 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
945 x_return_status OUT NOCOPY VARCHAR2,
946 x_msg_count OUT NOCOPY NUMBER,
947 x_msg_data OUT NOCOPY VARCHAR2
948 ) AS
949 --******** local variable for standards **********
950 l_api_name CONSTANT VARCHAR2(30) := 'Create_Associations';
951 l_api_version CONSTANT NUMBER := 1.0;
952 l_row_id VARCHAR2(250);
953 --
954 l_assoc_id NUMBER;
955 l_temp_array_length NUMBER;
956 l_count NUMBER := 1;
957
958 l_content_item_id NUMBER;
959 l_citem_version_id NUMBER;
960 l_assoc_type_code VARCHAR2(100);
961 l_assoc_object1 VARCHAR2(254);
962 l_assoc_object2 VARCHAR2(254);
963 l_assoc_object3 VARCHAR2(254);
964 l_assoc_object4 VARCHAR2(254);
965 l_assoc_object5 VARCHAR2(254);
966
967 --Start:Added for Bug#10409046 Fix
968
969 CURSOR c_citem_ver_id (l_citem_id IN NUMBER) IS
970 SELECT ci.citem_ver_id
971 FROM ibc_citems_v ci
972 WHERE ci.CITEM_ID=l_citem_id;
973
974 l_citem_ver_count NUMBER :=1;
975 l_citem_version_ids JTF_NUMBER_TABLE;
976 --Start:Added for Bug#10409046 Fix
977
978 BEGIN
979 -- ******************* Standard Begins *******************
980 -- Standard Start of API savepoint
981 SAVEPOINT CREATE_ASSOCIATIONS_PT;
982
983 -- Standard call to check for call compatibility.
984 IF NOT Fnd_Api.Compatible_API_Call (
985 l_api_version,
986 p_api_version,
987 l_api_name,
988 G_PKG_NAME)
989 THEN
990 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
991 END IF;
992 -- Initialize message list if p_init_msg_list is set to TRUE.
993 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
994 Fnd_Msg_Pub.initialize;
995 END IF;
996
997 -- Initialize API return status to success
998 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
999
1000 --******************* Real Logic Start *********************
1001
1002 l_temp_array_length := p_content_item_ids.COUNT;
1003 IF ( (p_assoc_type_codes.COUNT <> l_temp_array_length) OR
1004 (p_assoc_objects1.COUNT <> l_temp_array_length) OR
1005 ( (p_assoc_objects2 IS NOT NULL) AND (p_assoc_objects2.COUNT <> l_temp_array_length) ) OR
1006 ( (p_assoc_objects3 IS NOT NULL) AND (p_assoc_objects3.COUNT <> l_temp_array_length) ) OR
1007 ( (p_assoc_objects4 IS NOT NULL) AND (p_assoc_objects4.COUNT <> l_temp_array_length) ) OR
1008 ( (p_assoc_objects5 IS NOT NULL) AND (p_assoc_objects5.COUNT <> l_temp_array_length) ) ) THEN
1009 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1010 Fnd_Message.Set_Name('IBC', 'IMPROPER_ARRAY');
1011 Fnd_Msg_Pub.ADD;
1012 END IF;
1013 RAISE Fnd_Api.G_EXC_ERROR;
1014 END IF;
1015
1016 --Start:Added for Bug#10409046 Fix
1017 IF( p_citem_version_ids IS NULL AND l_temp_array_length <> 0) THEN
1018 l_citem_version_ids := JTF_NUMBER_TABLE();
1019 -- Extend
1020 l_citem_version_ids.extend;
1021 WHILE l_citem_ver_count <= l_temp_array_length LOOP
1022 OPEN c_citem_ver_id(p_content_item_ids(l_citem_ver_count)) ;
1023 FETCH c_citem_ver_id into l_citem_version_id;
1024 IF (c_citem_ver_id%NOTFOUND) THEN
1025 l_citem_version_id :=NULL;
1026 END IF;
1027 l_citem_version_ids(l_citem_ver_count) :=l_citem_version_id;
1028 CLOSE c_citem_ver_id;
1029 l_citem_ver_count := l_citem_ver_count+1;
1030 END LOOP;
1031 END IF;
1032 --End:Added for Bug#10409046 Fix
1033
1034 WHILE l_count <= l_temp_array_length LOOP
1035 l_content_item_id := p_content_item_ids(l_count);
1036 l_citem_version_id := NULL;
1037 IF p_citem_version_ids IS NOT NULL THEN
1038 l_citem_version_id := p_citem_version_ids(l_count);
1039 ELSE IF l_citem_version_ids IS NOT NULL THEN
1040 l_citem_version_id := l_citem_version_ids(l_count);
1041 END IF;
1042 END IF;
1043 l_assoc_type_code := p_assoc_type_codes(l_count);
1044 l_assoc_object1 := p_assoc_objects1(l_count);
1045 l_assoc_object2 := NULL;
1046 l_assoc_object3 := NULL;
1047 l_assoc_object4 := NULL;
1048 l_assoc_object5 := NULL;
1049 IF (p_assoc_objects2 IS NOT NULL) THEN
1050 l_assoc_object2 := p_assoc_objects2(l_count);
1051 END IF;
1052 IF (p_assoc_objects3 IS NOT NULL) THEN
1053 l_assoc_object3 := p_assoc_objects3(l_count);
1054 END IF;
1055 IF (p_assoc_objects4 IS NOT NULL) THEN
1056 l_assoc_object4 := p_assoc_objects4(l_count);
1057 END IF;
1058 IF (p_assoc_objects5 IS NOT NULL) THEN
1059 l_assoc_object5 := p_assoc_objects5(l_count);
1060 END IF;
1061
1062 -- Validate Citem Id
1063 IF (Ibc_Validate_Pvt.isValidCitem(l_content_item_id) = Fnd_Api.g_false) THEN
1064 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1065 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
1066 Fnd_Message.Set_token('CITEM_ID', l_content_item_id);
1067 Fnd_Msg_Pub.ADD;
1068 END IF;
1069 RAISE Fnd_Api.G_EXC_ERROR;
1070 END IF;
1071
1072 -- Validate citem version id
1073 IF l_citem_version_id IS NOT NULL AND
1074 IBC_VALIDATE_PVT.isValidCitemVerForCitem(l_content_item_id, l_citem_version_id) = FND_API.g_false
1075 THEN
1076 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1077 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
1078 Fnd_Message.Set_token('CITEM_VERSION_ID', l_citem_version_id);
1079 Fnd_Msg_Pub.ADD;
1080 END IF;
1081 RAISE Fnd_Api.G_EXC_ERROR;
1082 END IF;
1083
1084
1085
1086 -- Validate Association Type
1087 IF (Ibc_Validate_Pvt.isValidAssocType(l_assoc_type_code) = Fnd_Api.g_false) THEN
1088 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1089 Fnd_Message.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
1090 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_assoc_type_code);
1091 Fnd_Msg_Pub.ADD;
1092 END IF;
1093 RAISE Fnd_Api.G_EXC_ERROR;
1094 END IF;
1095
1096 -- Insert into table
1097 l_assoc_id := NULL;
1098 Ibc_Associations_Pkg.insert_row (
1099 px_association_id => l_assoc_id
1100 ,p_content_item_id => l_content_item_id
1101 ,p_citem_version_id => l_citem_version_id
1102 ,p_association_type_code => l_assoc_type_code
1103 ,p_associated_object_val1 => l_assoc_object1
1104 ,p_associated_object_val2 => l_assoc_object2
1105 ,p_associated_object_val3 => l_assoc_object3
1106 ,p_associated_object_val4 => l_assoc_object4
1107 ,p_associated_object_val5 => l_assoc_object5
1108 ,p_object_version_number => G_OBJ_VERSION_DEFAULT
1109 ,x_rowid => l_row_id
1110 );
1111
1112 -- Log action
1113 Ibc_Utilities_Pvt.log_action(
1114 p_activity => Ibc_Utilities_Pvt.G_ALA_CREATE
1115 ,p_parent_value => l_content_item_id
1116 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
1117 ,p_object_value1 => l_assoc_object1
1118 ,p_object_value2 => l_assoc_object2
1119 ,p_object_value3 => l_assoc_object3
1120 ,p_object_value4 => l_assoc_object4
1121 ,p_object_value5 => l_assoc_object5
1122 ,p_description => 'Created of type '|| l_assoc_type_code ||' with association id '|| l_assoc_id
1123 );
1124
1125 l_count := l_count + 1;
1126 END LOOP;
1127
1128 --******************* Real Logic End *********************
1129 -- Standard check of p_commit.
1130 IF (Fnd_Api.To_Boolean(p_commit)) THEN
1131 COMMIT WORK;
1132 END IF;
1133 -- Standard call to get message count and if count=1, get the message
1134 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1135 p_data => x_msg_data);
1136 EXCEPTION
1137 WHEN Fnd_Api.G_EXC_ERROR THEN
1138 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
1139 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1140 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1141 p_data => x_msg_data);
1142 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1143 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
1144 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1145 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1146 p_data => x_msg_data);
1147 WHEN OTHERS THEN
1148 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
1149 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1150 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1151 THEN
1152 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1153 END IF;
1154 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1155 p_data => x_msg_data);
1156 END Create_Associations;
1157
1158
1159
1160 PROCEDURE Delete_Associations (
1161 p_api_version IN NUMBER,
1162 p_init_msg_list IN VARCHAR2,
1163 p_commit IN VARCHAR2,
1164 p_content_item_ids IN JTF_NUMBER_TABLE,
1165 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1166 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1167 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1168 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1169 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1170 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1171 x_return_status OUT NOCOPY VARCHAR2,
1172 x_msg_count OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2
1174 ) AS
1175 BEGIN
1176 Delete_Associations (
1177 p_api_version => p_api_version,
1178 p_init_msg_list => p_init_msg_list,
1179 p_commit => p_commit,
1180 p_content_item_ids => p_content_item_ids,
1181 p_citem_version_ids => NULL,
1182 p_assoc_type_codes => p_assoc_type_codes,
1183 p_assoc_objects1 => p_assoc_objects1,
1184 p_assoc_objects2 => p_assoc_objects2,
1185 p_assoc_objects3 => p_assoc_objects3,
1186 p_assoc_objects4 => p_assoc_objects4,
1187 p_assoc_objects5 => p_assoc_objects5,
1188 x_return_status => x_return_status,
1189 x_msg_count => x_msg_count,
1190 x_msg_data => x_msg_data
1191 );
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 RAISE;
1195 END Delete_Associations;
1196
1197
1198
1199 PROCEDURE Delete_Associations (
1200 p_api_version IN NUMBER,
1201 p_init_msg_list IN VARCHAR2,
1202 p_commit IN VARCHAR2,
1203 p_content_item_ids IN JTF_NUMBER_TABLE,
1204 p_citem_version_ids IN JTF_NUMBER_TABLE,
1205 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1206 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1207 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1208 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1209 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1210 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1211 x_return_status OUT NOCOPY VARCHAR2,
1212 x_msg_count OUT NOCOPY NUMBER,
1213 x_msg_data OUT NOCOPY VARCHAR2
1214 ) AS
1215 --******** local variable for standards **********
1216 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Associations';
1217 l_api_version CONSTANT NUMBER := 1.0;
1218 --
1219 l_temp_array_length NUMBER;
1220 l_count NUMBER := 1;
1221 l_temp_assoc_id NUMBER;
1222
1223 l_content_item_id NUMBER;
1224 l_citem_version_id NUMBER;
1225 l_assoc_type_code VARCHAR2(100);
1226 l_assoc_object1 VARCHAR2(254);
1227 l_assoc_object2 VARCHAR2(254);
1228 l_assoc_object3 VARCHAR2(254);
1229 l_assoc_object4 VARCHAR2(254);
1230 l_assoc_object5 VARCHAR2(254);
1231 --
1232 CURSOR Get_Assoc_Id IS
1233 SELECT association_id
1234 FROM IBC_ASSOCIATIONS
1235 WHERE content_item_id = l_content_item_id
1236 AND citem_version_id = NVL(l_citem_version_id,citem_version_id)
1237 AND association_type_code = l_assoc_type_code
1238 AND associated_object_val1 = l_assoc_object1
1239 AND NVL(associated_object_val2, '0') = NVL(l_assoc_object2, '0')
1240 AND NVL(associated_object_val3, '0') = NVL(l_assoc_object3, '0')
1241 AND NVL(associated_object_val4, '0') = NVL(l_assoc_object4, '0')
1242 AND NVL(associated_object_val5, '0') = NVL(l_assoc_object5, '0');
1243
1244 BEGIN
1245 -- ******************* Standard Begins *******************
1246 -- Standard Start of API savepoint
1247 SAVEPOINT DELETE_ASSOCIATIONS_PT;
1248
1249 -- Standard call to check for call compatibility.
1250 IF NOT Fnd_Api.Compatible_API_Call (
1251 l_api_version,
1252 p_api_version,
1253 l_api_name,
1254 G_PKG_NAME)
1255 THEN
1256 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1257 END IF;
1258 -- Initialize message list if p_init_msg_list is set to TRUE.
1259 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
1260 Fnd_Msg_Pub.initialize;
1261 END IF;
1262
1263 -- Initialize API return status to success
1264 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1265
1266 --******************* Real Logic Start *********************
1267
1268 l_temp_array_length := p_content_item_ids.COUNT;
1269 IF ( (p_assoc_type_codes.COUNT <> l_temp_array_length) OR
1270 (p_assoc_objects1.COUNT <> l_temp_array_length) OR
1271 ( (p_assoc_objects2 IS NOT NULL) AND (p_assoc_objects2.COUNT <> l_temp_array_length) ) OR
1272 ( (p_assoc_objects3 IS NOT NULL) AND (p_assoc_objects3.COUNT <> l_temp_array_length) ) OR
1273 ( (p_assoc_objects4 IS NOT NULL) AND (p_assoc_objects4.COUNT <> l_temp_array_length) ) OR
1274 ( (p_assoc_objects5 IS NOT NULL) AND (p_assoc_objects5.COUNT <> l_temp_array_length) ) ) THEN
1275 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1276 Fnd_Message.Set_Name('IBC', 'IMPROPER_ARRAY');
1277 Fnd_Msg_Pub.ADD;
1278 END IF;
1279 RAISE Fnd_Api.G_EXC_ERROR;
1280 END IF;
1281
1282 WHILE l_count <= l_temp_array_length LOOP
1283
1284 l_content_item_id := p_content_item_ids(l_count);
1285 l_citem_version_id := NULL;
1286 IF p_citem_version_ids IS NOT NULL THEN
1287 l_citem_version_id := p_citem_version_ids(l_count);
1288 END IF;
1289 l_assoc_type_code := p_assoc_type_codes(l_count);
1290 l_assoc_object1 := p_assoc_objects1(l_count);
1291 l_assoc_object2 := NULL;
1292 l_assoc_object3 := NULL;
1293 l_assoc_object4 := NULL;
1294 l_assoc_object5 := NULL;
1295 IF (p_assoc_objects2 IS NOT NULL) THEN
1296 l_assoc_object2 := p_assoc_objects2(l_count);
1297 END IF;
1298 IF (p_assoc_objects3 IS NOT NULL) THEN
1299 l_assoc_object3 := p_assoc_objects3(l_count);
1300 END IF;
1301 IF (p_assoc_objects4 IS NOT NULL) THEN
1302 l_assoc_object4 := p_assoc_objects4(l_count);
1303 END IF;
1304 IF (p_assoc_objects5 IS NOT NULL) THEN
1305 l_assoc_object5 := p_assoc_objects5(l_count);
1306 END IF;
1307
1308 OPEN Get_Assoc_Id;
1309 FETCH Get_Assoc_Id INTO l_temp_assoc_id;
1310 IF (Get_Assoc_Id%NOTFOUND) THEN
1311 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1312 Fnd_Message.Set_Name('IBC', 'NO_ASSOCIATION_FOUND');
1313 Fnd_Msg_Pub.ADD;
1314 END IF;
1315 CLOSE Get_Assoc_Id;
1316 RAISE Fnd_Api.G_EXC_ERROR;
1317 END IF;
1318 CLOSE Get_Assoc_Id;
1319
1320 -- Delete Entry
1321 Ibc_Associations_Pkg.delete_row(
1322 p_association_id => l_temp_assoc_id
1323 );
1324
1325 -- Log Action
1326 Ibc_Utilities_Pvt.log_action(
1327 p_activity => Ibc_Utilities_Pvt.G_ALA_REMOVE
1328 ,p_parent_value => l_content_item_id
1329 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
1330 ,p_object_value1 => l_assoc_object1
1331 ,p_object_value2 => l_assoc_object2
1332 ,p_object_value3 => l_assoc_object3
1333 ,p_object_value4 => l_assoc_object4
1334 ,p_object_value5 => l_assoc_object5
1335 ,p_description => 'Deleting association of type '|| l_assoc_type_code
1336 );
1337
1338 l_count := l_count + 1;
1339 END LOOP;
1340
1341 --******************* Real Logic End *********************
1342 -- Standard check of p_commit.
1343 IF (Fnd_Api.To_Boolean(p_commit)) THEN
1344 COMMIT WORK;
1345 END IF;
1346 -- Standard call to get message count and if count=1, get the message
1347 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1348 p_data => x_msg_data);
1349 EXCEPTION
1350 WHEN Fnd_Api.G_EXC_ERROR THEN
1351 ROLLBACK TO DELETE_ASSOCIATIONS_PT;
1352 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1353 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1354 p_data => x_msg_data);
1355 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1356 ROLLBACK TO DELETE_ASSOCIATIONS_PT;
1357 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1358 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1359 p_data => x_msg_data);
1360 WHEN OTHERS THEN
1361 ROLLBACK TO DELETE_ASSOCIATIONS_PT;
1362 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1363 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1364 THEN
1365 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1366 END IF;
1367 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1368 p_data => x_msg_data);
1369 END Delete_Associations;
1370
1371
1372 PROCEDURE Get_Associations (
1373 p_api_version IN NUMBER,
1374 p_init_msg_list IN VARCHAR2,
1375 p_content_item_id IN NUMBER,
1376 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1377 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1378 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1379 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1380 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1381 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1382 x_assoc_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1383 x_assoc_objects1 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1384 x_assoc_objects2 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1385 x_assoc_objects3 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1386 x_assoc_objects4 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1387 x_assoc_objects5 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1388 x_assoc_names OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
1389 x_assoc_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1390 x_return_status OUT NOCOPY VARCHAR2,
1391 x_msg_count OUT NOCOPY NUMBER,
1392 x_msg_data OUT NOCOPY VARCHAR2
1393 ) AS
1394 BEGIN
1395 Get_Associations (
1396 p_api_version => p_api_version,
1397 p_init_msg_list => p_init_msg_list,
1398 p_content_item_id => p_content_item_id,
1399 p_citem_version_id => NULL,
1400 p_assoc_type_codes => p_assoc_type_codes,
1401 p_assoc_objects1 => p_assoc_objects1,
1402 p_assoc_objects2 => p_assoc_objects2,
1403 p_assoc_objects3 => p_assoc_objects3,
1404 p_assoc_objects4 => p_assoc_objects4,
1405 p_assoc_objects5 => p_assoc_objects5,
1406 x_assoc_type_codes => x_assoc_type_codes,
1407 x_assoc_objects1 => x_assoc_objects1,
1408 x_assoc_objects2 => x_assoc_objects2,
1409 x_assoc_objects3 => x_assoc_objects3,
1410 x_assoc_objects4 => x_assoc_objects4,
1411 x_assoc_objects5 => x_assoc_objects5,
1412 x_assoc_names => x_assoc_names,
1413 x_assoc_codes => x_assoc_codes,
1414 x_return_status => x_return_status,
1415 x_msg_count => x_msg_count,
1416 x_msg_data => x_msg_data
1417 );
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 RAISE;
1421 END Get_Associations;
1422
1423 PROCEDURE Get_Associations (
1424 p_api_version IN NUMBER,
1425 p_init_msg_list IN VARCHAR2,
1426 p_content_item_id IN NUMBER,
1427 p_citem_version_id IN NUMBER,
1428 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1429 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1430 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1431 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1432 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1433 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1434 x_assoc_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1435 x_assoc_objects1 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1436 x_assoc_objects2 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1437 x_assoc_objects3 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1438 x_assoc_objects4 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1439 x_assoc_objects5 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1440 x_assoc_names OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
1441 x_assoc_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 x_msg_count OUT NOCOPY NUMBER,
1444 x_msg_data OUT NOCOPY VARCHAR2
1445 ) AS
1446 --******** local variable for standards **********
1447 l_api_name CONSTANT VARCHAR2(30) := 'Get_Associations';
1448 l_api_version CONSTANT NUMBER := 1.0;
1449 --
1450 pre_plsql_block VARCHAR2(10) := 'BEGIN ';
1451 post_plsql_block VARCHAR2(200) := '.Get_Object_Name(:at, :v1, :v2, :v3, :v4, :v5, :xn, :xc, :xret, :xmc, :md); END;';
1452 l_assoc_name VARCHAR2(4000);
1453 l_assoc_code VARCHAR2(100);
1454
1455 l_count NUMBER := 1;
1456 l_assoc_type_code VARCHAR2(100) := NULL;
1457 l_callback_pkg VARCHAR2(30);
1458 l_assoc_object2 VARCHAR2(254) := NULL;
1459 l_assoc_object3 VARCHAR2(254) := NULL;
1460 l_assoc_object4 VARCHAR2(254) := NULL;
1461 l_assoc_object5 VARCHAR2(254) := NULL;
1462 --
1463 CURSOR Get_Assoc IS
1464 SELECT a.ASSOCIATION_TYPE_CODE, a.ASSOCIATED_OBJECT_VAL1, a.ASSOCIATED_OBJECT_VAL2,
1465 a.ASSOCIATED_OBJECT_VAL3, a.ASSOCIATED_OBJECT_VAL4, a.ASSOCIATED_OBJECT_VAL5,
1466 t.CALL_BACK_PKG
1467 FROM IBC_ASSOCIATIONS a, IBC_ASSOCIATION_TYPES_B t
1468 WHERE a.CONTENT_ITEM_ID = p_content_item_id AND
1469 a.citem_version_id = NVL(p_citem_version_id,a.citem_version_id) AND
1470 a.ASSOCIATION_TYPE_CODE = t.ASSOCIATION_TYPE_CODE;
1471
1472 CURSOR Get_CallBack IS
1473 SELECT CALL_BACK_PKG
1474 FROM IBC_ASSOCIATION_TYPES_B
1475 WHERE ASSOCIATION_TYPE_CODE = l_assoc_type_code;
1476
1477 BEGIN
1478 -- ******************* Standard Begins *******************
1479 -- Standard call to check for call compatibility.
1480 IF NOT Fnd_Api.Compatible_API_Call (
1481 l_api_version,
1482 p_api_version,
1483 l_api_name,
1484 G_PKG_NAME)
1485 THEN
1486 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1487 END IF;
1488 -- Initialize message list if p_init_msg_list is set to TRUE.
1489 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
1490 Fnd_Msg_Pub.initialize;
1491 END IF;
1492
1493 -- Initialize API return status to success
1494 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1495 -- ******************* Real Logic Start *********************
1496
1497 -- Validate Citem Id
1498 IF (p_content_item_id IS NOT NULL) THEN
1499 IF (Ibc_Validate_Pvt.isValidCitem(p_content_item_id) = Fnd_Api.g_false) THEN
1500 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1501 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
1502 Fnd_Message.Set_token('CITEM_ID', p_content_item_id);
1503 Fnd_Msg_Pub.ADD;
1504 END IF;
1505 RAISE Fnd_Api.G_EXC_ERROR;
1506 END IF;
1507 END IF;
1508 IF p_citem_version_id IS NOT NULL AND
1509 IBC_VALIDATE_PVT.isValidCitemVerForCitem(p_content_item_id, p_citem_version_id) = FND_API.g_false
1510 THEN
1511 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1512 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
1513 Fnd_Message.Set_token('CITEM_VERSION_ID', p_citem_version_id);
1514 Fnd_Msg_Pub.ADD;
1515 END IF;
1516 RAISE Fnd_Api.G_EXC_ERROR;
1517 END IF;
1518
1519
1520 -- Initialize OUT NOCOPYput parameters
1521 x_assoc_type_codes := JTF_VARCHAR2_TABLE_100();
1522 x_assoc_objects1 := JTF_VARCHAR2_TABLE_300();
1523 x_assoc_objects2 := JTF_VARCHAR2_TABLE_300();
1524 x_assoc_objects3 := JTF_VARCHAR2_TABLE_300();
1525 x_assoc_objects4 := JTF_VARCHAR2_TABLE_300();
1526 x_assoc_objects5 := JTF_VARCHAR2_TABLE_300();
1527 x_assoc_names := JTF_VARCHAR2_TABLE_4000();
1528 x_assoc_codes := JTF_VARCHAR2_TABLE_100();
1529
1530 -- Retrieive Existing Associations
1531 FOR assoc_rec IN Get_Assoc LOOP
1532 IF (assoc_rec.CALL_BACK_PKG IS NULL) THEN
1533 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1534 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_IS_NULL');
1535 Fnd_Message.Set_token('ASSOC_TYPE_CODE', assoc_rec.ASSOCIATION_TYPE_CODE);
1536 Fnd_Msg_Pub.ADD;
1537 END IF;
1538 RAISE Fnd_Api.G_EXC_ERROR;
1539 END IF;
1540
1541 BEGIN
1542 -- Execute CallBack
1543 EXECUTE IMMEDIATE (pre_plsql_block || assoc_rec.CALL_BACK_PKG || post_plsql_block)
1544 USING assoc_rec.ASSOCIATION_TYPE_CODE,
1545 assoc_rec.ASSOCIATED_OBJECT_VAL1, assoc_rec.ASSOCIATED_OBJECT_VAL2,
1546 assoc_rec.ASSOCIATED_OBJECT_VAL3, assoc_rec.ASSOCIATED_OBJECT_VAL4,
1547 assoc_rec.ASSOCIATED_OBJECT_VAL5, OUT l_assoc_name, OUT l_assoc_code,
1548 OUT x_return_status, OUT x_msg_count, OUT x_msg_data;
1549 EXCEPTION
1550 WHEN OTHERS THEN
1551 IF (SQLCODE = -6550) THEN
1552 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1553 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_INVALID');
1554 Fnd_Message.Set_token('CALL_BACK_PKG', assoc_rec.CALL_BACK_PKG);
1555 Fnd_Msg_Pub.ADD;
1556 END IF;
1557 RAISE Fnd_Api.G_EXC_ERROR;
1558 ELSE
1559 RAISE;
1560 END IF;
1561 END;
1562
1563 x_assoc_type_codes.EXTEND();
1564 x_assoc_type_codes(l_count) := assoc_rec.ASSOCIATION_TYPE_CODE;
1565 x_assoc_objects1.EXTEND();
1566 x_assoc_objects1(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL1;
1567 x_assoc_objects2.EXTEND();
1568 x_assoc_objects2(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL2;
1569 x_assoc_objects3.EXTEND();
1570 x_assoc_objects3(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL3;
1571 x_assoc_objects4.EXTEND();
1572 x_assoc_objects4(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL4;
1573 x_assoc_objects5.EXTEND();
1574 x_assoc_objects5(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL5;
1575 x_assoc_names.EXTEND();
1576 x_assoc_names(l_count) := l_assoc_name;
1577 x_assoc_codes.EXTEND();
1578 x_assoc_codes(l_count) := l_assoc_code;
1579
1580 l_count := l_count + 1;
1581 END LOOP;
1582
1583 -- Get Names/Codes for additional input associations
1584 IF (p_assoc_type_codes IS NOT NULL) THEN
1585
1586 FOR i IN 1..p_assoc_type_codes.COUNT LOOP
1587 IF ((l_assoc_type_code IS NULL) OR (p_assoc_type_codes(i) <> l_assoc_type_code)) THEN
1588 l_assoc_type_code := p_assoc_type_codes(i);
1589 OPEN Get_CallBack;
1590 FETCH Get_CallBack INTO l_callback_pkg;
1591 IF (Get_CallBack%NOTFOUND) THEN
1592 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1593 Fnd_Message.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
1594 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_assoc_type_code);
1595 Fnd_Msg_Pub.ADD;
1596 END IF;
1597 RAISE Fnd_Api.G_EXC_ERROR;
1598 END IF;
1599 IF (l_callback_pkg IS NULL) THEN
1600 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1601 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_IS_NULL');
1602 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_assoc_type_code);
1603 Fnd_Msg_Pub.ADD;
1604 END IF;
1605 RAISE Fnd_Api.G_EXC_ERROR;
1606 END IF;
1607 CLOSE Get_CallBack;
1608 END IF;
1609
1610 IF (p_assoc_objects2 IS NOT NULL) THEN
1611 l_assoc_object2 := p_assoc_objects2(i);
1612 END IF;
1613 IF (p_assoc_objects3 IS NOT NULL) THEN
1614 l_assoc_object3 := p_assoc_objects3(i);
1615 END IF;
1616 IF (p_assoc_objects4 IS NOT NULL) THEN
1617 l_assoc_object4 := p_assoc_objects4(i);
1618 END IF;
1619 IF (p_assoc_objects5 IS NOT NULL) THEN
1620 l_assoc_object5 := p_assoc_objects5(i);
1621 END IF;
1622
1623 BEGIN
1624 -- Execute CallBack
1625 EXECUTE IMMEDIATE (pre_plsql_block || l_callback_pkg || post_plsql_block)
1626 USING l_assoc_type_code,
1627 p_assoc_objects1(i), l_assoc_object2,
1628 l_assoc_object3, l_assoc_object4,
1629 l_assoc_object5, OUT l_assoc_name, OUT l_assoc_code,
1630 OUT x_return_status, OUT x_msg_count, OUT x_msg_data;
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 IF (SQLCODE = -6550) THEN
1634 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1635 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_INVALID');
1636 Fnd_Message.Set_token('CALL_BACK_PKG', l_callback_pkg);
1637 Fnd_Msg_Pub.ADD;
1638 END IF;
1639 RAISE Fnd_Api.G_EXC_ERROR;
1640 ELSE
1641 RAISE;
1642 END IF;
1643 END;
1644
1645 x_assoc_type_codes.EXTEND();
1646 x_assoc_type_codes(l_count) := l_assoc_type_code;
1647 x_assoc_objects1.EXTEND();
1648 x_assoc_objects1(l_count) := p_assoc_objects1(i);
1649 x_assoc_objects2.EXTEND();
1650 x_assoc_objects2(l_count) := l_assoc_object2;
1651 x_assoc_objects3.EXTEND();
1652 x_assoc_objects3(l_count) := l_assoc_object3;
1653 x_assoc_objects4.EXTEND();
1654 x_assoc_objects4(l_count) := l_assoc_object4;
1655 x_assoc_objects5.EXTEND();
1656 x_assoc_objects5(l_count) := l_assoc_object5;
1657 x_assoc_names.EXTEND();
1658 x_assoc_names(l_count) := l_assoc_name;
1659 x_assoc_codes.EXTEND();
1660 x_assoc_codes(l_count) := l_assoc_code;
1661
1662 l_count := l_count + 1;
1663 END LOOP;
1664
1665 END IF;
1666
1667 --******************* Real Logic End *********************
1668
1669 -- Standard call to get message count and if count=1, get the message
1670 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1671 p_data => x_msg_data);
1672 EXCEPTION
1673 WHEN Fnd_Api.G_EXC_ERROR THEN
1674 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1675 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1676 p_data => x_msg_data);
1677 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1678 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1679 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1680 p_data => x_msg_data);
1681 WHEN OTHERS THEN
1682 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1683 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1684 THEN
1685 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1686 END IF;
1687 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1688 p_data => x_msg_data);
1689 END Get_Associations;
1690
1691 FUNCTION Get_Association_NameAndCode(p_content_item_id IN NUMBER,
1692 p_citem_version_id IN NUMBER,
1693 p_assoc_type_code IN VARCHAR2,
1694 p_assoc_object1 IN VARCHAR2,
1695 p_assoc_object2 IN VARCHAR2,
1696 p_assoc_object3 IN VARCHAR2,
1697 p_assoc_object4 IN VARCHAR2,
1698 p_assoc_object5 IN VARCHAR2
1699 )
1700 RETURN VARCHAR2
1701 IS
1702 l_assoc_name VARCHAR2(300);
1703 l_assoc_code VARCHAR2(80);
1704 l_result VARCHAR2(300);
1705 l_return_status VARCHAR2(30);
1706 l_msg_count NUMBER;
1707 l_msg_data VARCHAR2(2000);
1708
1709 pre_plsql_block VARCHAR2(10) := 'BEGIN ';
1710 post_plsql_block VARCHAR2(200) := '.Get_Object_Name(:at, :v1, :v2, :v3, :v4, :v5, :xn, :xc, :xret, :xmc, :md); END;';
1711
1712 CURSOR c_callback(p_assoc_type_code VARCHAR2) IS
1713 SELECT call_back_pkg
1714 FROM ibc_association_types_b
1715 WHERE association_type_code = p_assoc_type_code;
1716
1717 BEGIN
1718
1719 l_result := NULL;
1720
1721 FOR r_callback IN c_callback(p_assoc_type_code) LOOP
1722
1723 BEGIN
1724 -- Execute CallBack
1725 EXECUTE IMMEDIATE (pre_plsql_block || r_callback.CALL_BACK_PKG || post_plsql_block)
1726 USING p_assoc_type_code,
1727 p_assoc_object1, p_assoc_object2,
1728 p_assoc_object3, p_assoc_object4,
1729 p_assoc_object5, OUT l_assoc_name, OUT l_assoc_code,
1730 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
1731 EXCEPTION
1732 WHEN OTHERS THEN
1733 IF (SQLCODE = -6550) THEN
1734 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1735 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_INVALID');
1736 Fnd_Message.Set_token('CALL_BACK_PKG', r_callback.CALL_BACK_PKG);
1737 Fnd_Msg_Pub.ADD;
1738 END IF;
1739 RAISE Fnd_Api.G_EXC_ERROR;
1740 ELSE
1741 RAISE;
1742 END IF;
1743 END;
1744
1745 END LOOP;
1746
1747 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1748 /*
1749 DBMS_OUTPUT.put_line('Errors FOUND '|| l_msg_data);
1750 for i in 0..l_msg_count loop
1751 DBMS_OUTPUT.put_line(FND_MSG_PUB.get(i,FND_API.G_FALSE));
1752 end loop;
1753 */
1754 l_result := NULL;
1755 ELSE
1756 l_result := l_assoc_code || '|' || l_assoc_name;
1757 END IF;
1758
1759 RETURN l_result;
1760
1761 EXCEPTION
1762 WHEN OTHERS THEN
1763 l_result := NULL;
1764 RETURN l_result;
1765
1766 END Get_Association_NameAndCode;
1767
1768
1769 END Ibc_Associations_Grp;