[Home] [Help]
PACKAGE BODY: APPS.IBC_ASSOCIATIONS_GRP
Source
1 PACKAGE BODY Ibc_Associations_Grp AS
2 /* $Header: ibcgassb.pls 115.15 2003/11/20 00:22:03 vicho 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;
376 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
373 END IF;
374
375 -- Initialize API return status to 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,
514 Move_Associations (
511 x_msg_data OUT NOCOPY VARCHAR2
512 ) AS
513 BEGIN
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
654 FETCH cur_old_citem INTO l_content_item_id;
651 l_content_item_id := NULL;
652
653 OPEN cur_old_citem;
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 IS NULL AND p_old_citem_version_ids(i) IS NULL)
783 OR
784 (citem_version_id = p_old_citem_version_ids(i))
785 )
786 AND association_type_code = p_assoc_type_codes(i)
787 AND (associated_object_val1 = p_assoc_objects1(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1)
788 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')
789 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')
793 WHERE a.association_type_code = b.association_type_code
790 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')
791 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')
792 ) B
794 AND a.associated_object_val1 = b.associated_object_val1
795 AND NVL(a.associated_object_val2,'1') = NVL(b.associated_object_val2,'1')
796 AND NVL(a.associated_object_val3,'1') = NVL(b.associated_object_val3,'1')
797 AND NVL(a.associated_object_val4,'1') = NVL(b.associated_object_val4,'1')
798 AND NVL(a.associated_object_val5,'1') = NVL(b.associated_object_val5,'1')
799 AND a.CONTENT_ITEM_ID = p_new_content_item_ids(i)
800 AND ((a.citem_version_id IS NULL AND p_new_citem_version_ids(i) IS NULL)
801 OR
802 (citem_version_id = p_new_citem_version_ids(i))
803 )
804 -- By Mistake if the user passes the same old and new Content Item Id and citem ver id
805 -- then Delete should not happen.
806 AND NOT ( (p_new_content_item_ids(i) = p_old_content_item_ids(i)
807 AND
808 p_new_citem_version_ids(i) IS NULL AND p_old_citem_version_ids(i) IS NULL
809 )
810 OR
811 (p_new_content_item_ids(i) = p_old_content_item_ids(i)
812 AND
813 p_new_citem_version_ids(i) = p_old_citem_version_ids(i)
814 )
815 )
816 );
817
818
819
820 FORALL i IN p_old_content_item_ids.FIRST..p_old_content_item_ids.LAST
821 UPDATE IBC_ASSOCIATIONS SET
822 CONTENT_ITEM_ID = p_new_content_item_ids(i)
823 ,CITEM_VERSION_ID = p_new_citem_version_ids(i)
824 ,OBJECT_VERSION_NUMBER = 1
825 ,LAST_UPDATE_DATE = SYSDATE
826 ,LAST_UPDATED_BY = Fnd_Global.user_id
827 ,LAST_UPDATE_LOGIN = Fnd_Global.login_id
828 WHERE CONTENT_ITEM_ID = p_old_content_item_ids(i)
829 AND ((citem_version_id IS NULL AND p_old_citem_version_ids(i) IS NULL)
830 OR
831 (citem_version_id = p_old_citem_version_ids(i))
832 )
833 AND association_type_code = p_assoc_type_codes(i)
834 AND (associated_object_val1 = p_assoc_objects1(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1)
835 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')
836 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')
837 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')
838 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');
839
840 --DBMS_OUT NOCOPYPUT.put_line('Update Successful....');
841 --Will insert them.
842
843 BEGIN
844
845 FOR i IN p_old_content_item_ids.FIRST..p_old_content_item_ids.LAST
846 LOOP
847 IF SQL%BULK_ROWCOUNT(i) = 0 AND p_old_content_item_ids(i) IS NULL THEN
848 BEGIN
849
850 Ibc_Associations_Pkg.insert_row (
851 px_association_id => l_assoc_id
852 ,p_content_item_id => p_new_content_item_ids(i)
853 ,p_citem_version_id => p_new_citem_version_ids(i)
854 ,p_association_type_code => p_assoc_type_codes(i)
855 ,p_associated_object_val1 => p_assoc_objects1(i)
856 ,p_associated_object_val2 => p_assoc_objects2(i)
857 ,p_associated_object_val3 => p_assoc_objects3(i)
858 ,p_associated_object_val4 => p_assoc_objects4(i)
859 ,p_associated_object_val5 => p_assoc_objects5(i)
860 ,p_object_version_number => G_OBJ_VERSION_DEFAULT
861 ,x_rowid => l_rowid
862 );
863 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
864 -- If the User tries to Insert a Duplicate
865 -- this exception will be thrown 'cos there
866 -- is a Unique Index.
867 -- Ignore and proceed with the next Insert
868 NULL;
869 END;
870 END IF;
871 END LOOP;
872 END;
873
874
875 --******************* Real Logic End *********************
876 -- Standard check of p_commit.
877 IF (Fnd_Api.To_Boolean(p_commit)) THEN
878 COMMIT WORK;
879 END IF;
880 -- Standard call to get message count and if count=1, get the message
881 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count,
882 p_data => x_msg_data);
883 EXCEPTION
884 WHEN Fnd_Api.G_EXC_ERROR THEN
885 ROLLBACK TO MOVE_ASSOCIATIONS_PT;
886 x_return_status := Fnd_Api.G_RET_STS_ERROR;
887 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
888 p_data => x_msg_data);
889 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
890 ROLLBACK TO MOVE_ASSOCIATIONS_PT;
891 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
895 ROLLBACK TO MOVE_ASSOCIATIONS_PT;
892 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
893 p_data => x_msg_data);
894 WHEN OTHERS THEN
896 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
897 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
898 THEN
899 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
900 END IF;
901 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
902 p_data => x_msg_data);
903 END Move_Associations;
904
905 PROCEDURE Create_Associations (
906 p_api_version IN NUMBER,
907 p_init_msg_list IN VARCHAR2,
908 p_commit IN VARCHAR2,
909 p_content_item_ids IN JTF_NUMBER_TABLE,
910 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
911 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
912 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
913 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
914 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
915 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_msg_count OUT NOCOPY NUMBER,
918 x_msg_data OUT NOCOPY VARCHAR2
919 ) AS
920 BEGIN
921 Create_Associations (
922 p_api_version => p_api_version,
923 p_init_msg_list => p_init_msg_list,
924 p_commit => p_commit,
925 p_content_item_ids => p_content_item_ids,
926 p_citem_version_ids => NULL,
927 p_assoc_type_codes => p_assoc_type_codes,
928 p_assoc_objects1 => p_assoc_objects1,
929 p_assoc_objects2 => p_assoc_objects2,
930 p_assoc_objects3 => p_assoc_objects3,
931 p_assoc_objects4 => p_assoc_objects4,
932 p_assoc_objects5 => p_assoc_objects5,
933 x_return_status => x_return_status,
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data
936 );
937 EXCEPTION
938 WHEN OTHERS THEN
939 RAISE;
940 END Create_Associations;
941
942 PROCEDURE Create_Associations (
943 p_api_version IN NUMBER,
944 p_init_msg_list IN VARCHAR2,
945 p_commit IN VARCHAR2,
946 p_content_item_ids IN JTF_NUMBER_TABLE,
947 p_citem_version_ids IN JTF_NUMBER_TABLE,
948 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
949 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
950 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
951 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
952 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
953 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
954 x_return_status OUT NOCOPY VARCHAR2,
955 x_msg_count OUT NOCOPY NUMBER,
956 x_msg_data OUT NOCOPY VARCHAR2
957 ) AS
958 --******** local variable for standards **********
959 l_api_name CONSTANT VARCHAR2(30) := 'Create_Associations';
960 l_api_version CONSTANT NUMBER := 1.0;
961 l_row_id VARCHAR2(250);
962 --
963 l_assoc_id NUMBER;
964 l_temp_array_length NUMBER;
965 l_count NUMBER := 1;
966
967 l_content_item_id NUMBER;
968 l_citem_version_id NUMBER;
969 l_assoc_type_code VARCHAR2(100);
970 l_assoc_object1 VARCHAR2(254);
971 l_assoc_object2 VARCHAR2(254);
972 l_assoc_object3 VARCHAR2(254);
973 l_assoc_object4 VARCHAR2(254);
974 l_assoc_object5 VARCHAR2(254);
975
976 BEGIN
977 -- ******************* Standard Begins *******************
978 -- Standard Start of API savepoint
979 SAVEPOINT CREATE_ASSOCIATIONS_PT;
980
981 -- Standard call to check for call compatibility.
982 IF NOT Fnd_Api.Compatible_API_Call (
983 l_api_version,
984 p_api_version,
985 l_api_name,
986 G_PKG_NAME)
987 THEN
988 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
989 END IF;
990 -- Initialize message list if p_init_msg_list is set to TRUE.
991 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
992 Fnd_Msg_Pub.initialize;
993 END IF;
994
995 -- Initialize API return status to success
996 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
997
998 --******************* Real Logic Start *********************
999
1000 l_temp_array_length := p_content_item_ids.COUNT;
1001 IF ( (p_assoc_type_codes.COUNT <> l_temp_array_length) OR
1005 ( (p_assoc_objects4 IS NOT NULL) AND (p_assoc_objects4.COUNT <> l_temp_array_length) ) OR
1002 (p_assoc_objects1.COUNT <> l_temp_array_length) OR
1003 ( (p_assoc_objects2 IS NOT NULL) AND (p_assoc_objects2.COUNT <> l_temp_array_length) ) OR
1004 ( (p_assoc_objects3 IS NOT NULL) AND (p_assoc_objects3.COUNT <> l_temp_array_length) ) OR
1006 ( (p_assoc_objects5 IS NOT NULL) AND (p_assoc_objects5.COUNT <> l_temp_array_length) ) ) THEN
1007 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1008 Fnd_Message.Set_Name('IBC', 'IMPROPER_ARRAY');
1009 Fnd_Msg_Pub.ADD;
1010 END IF;
1011 RAISE Fnd_Api.G_EXC_ERROR;
1012 END IF;
1013
1014
1015 WHILE l_count <= l_temp_array_length LOOP
1016 l_content_item_id := p_content_item_ids(l_count);
1017 l_citem_version_id := NULL;
1018 IF p_citem_version_ids IS NOT NULL THEN
1019 l_citem_version_id := p_citem_version_ids(l_count);
1020 END IF;
1021 l_assoc_type_code := p_assoc_type_codes(l_count);
1022 l_assoc_object1 := p_assoc_objects1(l_count);
1023 l_assoc_object2 := NULL;
1024 l_assoc_object3 := NULL;
1025 l_assoc_object4 := NULL;
1026 l_assoc_object5 := NULL;
1027 IF (p_assoc_objects2 IS NOT NULL) THEN
1028 l_assoc_object2 := p_assoc_objects2(l_count);
1029 END IF;
1030 IF (p_assoc_objects3 IS NOT NULL) THEN
1031 l_assoc_object3 := p_assoc_objects3(l_count);
1032 END IF;
1033 IF (p_assoc_objects4 IS NOT NULL) THEN
1034 l_assoc_object4 := p_assoc_objects4(l_count);
1035 END IF;
1036 IF (p_assoc_objects5 IS NOT NULL) THEN
1037 l_assoc_object5 := p_assoc_objects5(l_count);
1038 END IF;
1039
1040 -- Validate Citem Id
1041 IF (Ibc_Validate_Pvt.isValidCitem(l_content_item_id) = Fnd_Api.g_false) THEN
1042 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1043 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
1044 Fnd_Message.Set_token('CITEM_ID', l_content_item_id);
1045 Fnd_Msg_Pub.ADD;
1046 END IF;
1047 RAISE Fnd_Api.G_EXC_ERROR;
1048 END IF;
1049
1050 -- Validate citem version id
1051 IF l_citem_version_id IS NOT NULL AND
1052 IBC_VALIDATE_PVT.isValidCitemVerForCitem(l_content_item_id, l_citem_version_id) = FND_API.g_false
1053 THEN
1054 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1055 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
1056 Fnd_Message.Set_token('CITEM_VERSION_ID', l_citem_version_id);
1057 Fnd_Msg_Pub.ADD;
1058 END IF;
1059 RAISE Fnd_Api.G_EXC_ERROR;
1060 END IF;
1061
1062
1063
1064 -- Validate Association Type
1065 IF (Ibc_Validate_Pvt.isValidAssocType(l_assoc_type_code) = Fnd_Api.g_false) THEN
1066 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1067 Fnd_Message.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
1068 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_assoc_type_code);
1069 Fnd_Msg_Pub.ADD;
1070 END IF;
1071 RAISE Fnd_Api.G_EXC_ERROR;
1072 END IF;
1073
1074 -- Insert into table
1075 l_assoc_id := NULL;
1076 Ibc_Associations_Pkg.insert_row (
1077 px_association_id => l_assoc_id
1078 ,p_content_item_id => l_content_item_id
1079 ,p_citem_version_id => l_citem_version_id
1080 ,p_association_type_code => l_assoc_type_code
1081 ,p_associated_object_val1 => l_assoc_object1
1082 ,p_associated_object_val2 => l_assoc_object2
1083 ,p_associated_object_val3 => l_assoc_object3
1084 ,p_associated_object_val4 => l_assoc_object4
1085 ,p_associated_object_val5 => l_assoc_object5
1086 ,p_object_version_number => G_OBJ_VERSION_DEFAULT
1087 ,x_rowid => l_row_id
1088 );
1089
1090 -- Log action
1091 Ibc_Utilities_Pvt.log_action(
1092 p_activity => Ibc_Utilities_Pvt.G_ALA_CREATE
1093 ,p_parent_value => l_content_item_id
1094 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
1095 ,p_object_value1 => l_assoc_object1
1096 ,p_object_value2 => l_assoc_object2
1097 ,p_object_value3 => l_assoc_object3
1098 ,p_object_value4 => l_assoc_object4
1099 ,p_object_value5 => l_assoc_object5
1100 ,p_description => 'Created of type '|| l_assoc_type_code ||' with association id '|| l_assoc_id
1101 );
1102
1103 l_count := l_count + 1;
1104 END LOOP;
1105
1106 --******************* Real Logic End *********************
1107 -- Standard check of p_commit.
1108 IF (Fnd_Api.To_Boolean(p_commit)) THEN
1109 COMMIT WORK;
1110 END IF;
1111 -- Standard call to get message count and if count=1, get the message
1112 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1113 p_data => x_msg_data);
1114 EXCEPTION
1115 WHEN Fnd_Api.G_EXC_ERROR THEN
1116 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
1117 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1118 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1119 p_data => x_msg_data);
1120 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1121 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
1122 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1123 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1124 p_data => x_msg_data);
1125 WHEN OTHERS THEN
1126 ROLLBACK TO CREATE_ASSOCIATIONS_PT;
1127 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1128 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1129 THEN
1130 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1131 END IF;
1132 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1133 p_data => x_msg_data);
1134 END Create_Associations;
1135
1139 p_api_version IN NUMBER,
1136
1137
1138 PROCEDURE Delete_Associations (
1140 p_init_msg_list IN VARCHAR2,
1141 p_commit IN VARCHAR2,
1142 p_content_item_ids IN JTF_NUMBER_TABLE,
1143 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1144 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1145 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1146 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1147 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1148 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1149 x_return_status OUT NOCOPY VARCHAR2,
1150 x_msg_count OUT NOCOPY NUMBER,
1151 x_msg_data OUT NOCOPY VARCHAR2
1152 ) AS
1153 BEGIN
1154 Delete_Associations (
1155 p_api_version => p_api_version,
1156 p_init_msg_list => p_init_msg_list,
1157 p_commit => p_commit,
1158 p_content_item_ids => p_content_item_ids,
1159 p_citem_version_ids => NULL,
1160 p_assoc_type_codes => p_assoc_type_codes,
1161 p_assoc_objects1 => p_assoc_objects1,
1162 p_assoc_objects2 => p_assoc_objects2,
1163 p_assoc_objects3 => p_assoc_objects3,
1164 p_assoc_objects4 => p_assoc_objects4,
1165 p_assoc_objects5 => p_assoc_objects5,
1166 x_return_status => x_return_status,
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data
1169 );
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 RAISE;
1173 END Delete_Associations;
1174
1175
1176
1177 PROCEDURE Delete_Associations (
1178 p_api_version IN NUMBER,
1179 p_init_msg_list IN VARCHAR2,
1180 p_commit IN VARCHAR2,
1181 p_content_item_ids IN JTF_NUMBER_TABLE,
1182 p_citem_version_ids IN JTF_NUMBER_TABLE,
1183 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1184 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1185 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1186 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1187 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1188 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1189 x_return_status OUT NOCOPY VARCHAR2,
1190 x_msg_count OUT NOCOPY NUMBER,
1191 x_msg_data OUT NOCOPY VARCHAR2
1192 ) AS
1193 --******** local variable for standards **********
1194 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Associations';
1195 l_api_version CONSTANT NUMBER := 1.0;
1196 --
1197 l_temp_array_length NUMBER;
1198 l_count NUMBER := 1;
1199 l_temp_assoc_id NUMBER;
1200
1201 l_content_item_id NUMBER;
1202 l_citem_version_id NUMBER;
1203 l_assoc_type_code VARCHAR2(100);
1204 l_assoc_object1 VARCHAR2(254);
1205 l_assoc_object2 VARCHAR2(254);
1206 l_assoc_object3 VARCHAR2(254);
1207 l_assoc_object4 VARCHAR2(254);
1208 l_assoc_object5 VARCHAR2(254);
1209 --
1210 CURSOR Get_Assoc_Id IS
1211 SELECT association_id
1212 FROM IBC_ASSOCIATIONS
1213 WHERE content_item_id = l_content_item_id
1214 AND ((citem_version_id IS NULL AND l_citem_version_id IS NULL) OR
1215 (citem_version_id = l_citem_version_id)
1216 )
1217 AND association_type_code = l_assoc_type_code
1218 AND associated_object_val1 = l_assoc_object1
1219 AND NVL(associated_object_val2, '0') = NVL(l_assoc_object2, '0')
1220 AND NVL(associated_object_val3, '0') = NVL(l_assoc_object3, '0')
1221 AND NVL(associated_object_val4, '0') = NVL(l_assoc_object4, '0')
1222 AND NVL(associated_object_val5, '0') = NVL(l_assoc_object5, '0');
1223
1224 BEGIN
1225 -- ******************* Standard Begins *******************
1226 -- Standard Start of API savepoint
1227 SAVEPOINT DELETE_ASSOCIATIONS_PT;
1228
1229 -- Standard call to check for call compatibility.
1230 IF NOT Fnd_Api.Compatible_API_Call (
1231 l_api_version,
1232 p_api_version,
1233 l_api_name,
1234 G_PKG_NAME)
1235 THEN
1236 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238 -- Initialize message list if p_init_msg_list is set to TRUE.
1239 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
1240 Fnd_Msg_Pub.initialize;
1241 END IF;
1242
1243 -- Initialize API return status to success
1244 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1245
1246 --******************* Real Logic Start *********************
1247
1248 l_temp_array_length := p_content_item_ids.COUNT;
1249 IF ( (p_assoc_type_codes.COUNT <> l_temp_array_length) OR
1250 (p_assoc_objects1.COUNT <> l_temp_array_length) OR
1251 ( (p_assoc_objects2 IS NOT NULL) AND (p_assoc_objects2.COUNT <> l_temp_array_length) ) OR
1252 ( (p_assoc_objects3 IS NOT NULL) AND (p_assoc_objects3.COUNT <> l_temp_array_length) ) OR
1253 ( (p_assoc_objects4 IS NOT NULL) AND (p_assoc_objects4.COUNT <> l_temp_array_length) ) OR
1254 ( (p_assoc_objects5 IS NOT NULL) AND (p_assoc_objects5.COUNT <> l_temp_array_length) ) ) THEN
1255 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1256 Fnd_Message.Set_Name('IBC', 'IMPROPER_ARRAY');
1257 Fnd_Msg_Pub.ADD;
1258 END IF;
1259 RAISE Fnd_Api.G_EXC_ERROR;
1260 END IF;
1261
1262 WHILE l_count <= l_temp_array_length LOOP
1263
1264 l_content_item_id := p_content_item_ids(l_count);
1265 l_citem_version_id := NULL;
1266 IF p_citem_version_ids IS NOT NULL THEN
1267 l_citem_version_id := p_citem_version_ids(l_count);
1268 END IF;
1269 l_assoc_type_code := p_assoc_type_codes(l_count);
1270 l_assoc_object1 := p_assoc_objects1(l_count);
1271 l_assoc_object2 := NULL;
1272 l_assoc_object3 := NULL;
1273 l_assoc_object4 := NULL;
1274 l_assoc_object5 := NULL;
1275 IF (p_assoc_objects2 IS NOT NULL) THEN
1276 l_assoc_object2 := p_assoc_objects2(l_count);
1277 END IF;
1278 IF (p_assoc_objects3 IS NOT NULL) THEN
1279 l_assoc_object3 := p_assoc_objects3(l_count);
1280 END IF;
1284 IF (p_assoc_objects5 IS NOT NULL) THEN
1281 IF (p_assoc_objects4 IS NOT NULL) THEN
1282 l_assoc_object4 := p_assoc_objects4(l_count);
1283 END IF;
1285 l_assoc_object5 := p_assoc_objects5(l_count);
1286 END IF;
1287
1288 OPEN Get_Assoc_Id;
1289 FETCH Get_Assoc_Id INTO l_temp_assoc_id;
1290 IF (Get_Assoc_Id%NOTFOUND) THEN
1291 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1292 Fnd_Message.Set_Name('IBC', 'NO_ASSOCIATION_FOUND');
1293 Fnd_Msg_Pub.ADD;
1294 END IF;
1295 CLOSE Get_Assoc_Id;
1296 RAISE Fnd_Api.G_EXC_ERROR;
1297 END IF;
1298 CLOSE Get_Assoc_Id;
1299
1300 -- Delete Entry
1301 Ibc_Associations_Pkg.delete_row(
1302 p_association_id => l_temp_assoc_id
1303 );
1304
1305 -- Log Action
1306 Ibc_Utilities_Pvt.log_action(
1307 p_activity => Ibc_Utilities_Pvt.G_ALA_REMOVE
1308 ,p_parent_value => l_content_item_id
1309 ,p_object_type => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
1310 ,p_object_value1 => l_assoc_object1
1311 ,p_object_value2 => l_assoc_object2
1312 ,p_object_value3 => l_assoc_object3
1313 ,p_object_value4 => l_assoc_object4
1314 ,p_object_value5 => l_assoc_object5
1315 ,p_description => 'Deleting association of type '|| l_assoc_type_code
1316 );
1317
1318 l_count := l_count + 1;
1319 END LOOP;
1320
1321 --******************* Real Logic End *********************
1322 -- Standard check of p_commit.
1323 IF (Fnd_Api.To_Boolean(p_commit)) THEN
1324 COMMIT WORK;
1325 END IF;
1326 -- Standard call to get message count and if count=1, get the message
1327 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1328 p_data => x_msg_data);
1329 EXCEPTION
1330 WHEN Fnd_Api.G_EXC_ERROR THEN
1331 ROLLBACK TO DELETE_ASSOCIATIONS_PT;
1332 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1333 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1334 p_data => x_msg_data);
1335 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1336 ROLLBACK TO DELETE_ASSOCIATIONS_PT;
1337 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1338 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1339 p_data => x_msg_data);
1340 WHEN OTHERS THEN
1341 ROLLBACK TO DELETE_ASSOCIATIONS_PT;
1342 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1343 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1344 THEN
1345 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1346 END IF;
1347 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1348 p_data => x_msg_data);
1349 END Delete_Associations;
1350
1351
1352 PROCEDURE Get_Associations (
1353 p_api_version IN NUMBER,
1354 p_init_msg_list IN VARCHAR2,
1355 p_content_item_id IN NUMBER,
1356 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1357 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1358 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1359 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1360 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1361 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1362 x_assoc_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1363 x_assoc_objects1 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1364 x_assoc_objects2 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1365 x_assoc_objects3 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1366 x_assoc_objects4 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1367 x_assoc_objects5 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1368 x_assoc_names OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
1369 x_assoc_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1370 x_return_status OUT NOCOPY VARCHAR2,
1371 x_msg_count OUT NOCOPY NUMBER,
1372 x_msg_data OUT NOCOPY VARCHAR2
1373 ) AS
1374 BEGIN
1375 Get_Associations (
1376 p_api_version => p_api_version,
1377 p_init_msg_list => p_init_msg_list,
1378 p_content_item_id => p_content_item_id,
1379 p_citem_version_id => NULL,
1380 p_assoc_type_codes => p_assoc_type_codes,
1381 p_assoc_objects1 => p_assoc_objects1,
1382 p_assoc_objects2 => p_assoc_objects2,
1383 p_assoc_objects3 => p_assoc_objects3,
1384 p_assoc_objects4 => p_assoc_objects4,
1385 p_assoc_objects5 => p_assoc_objects5,
1386 x_assoc_type_codes => x_assoc_type_codes,
1387 x_assoc_objects1 => x_assoc_objects1,
1388 x_assoc_objects2 => x_assoc_objects2,
1389 x_assoc_objects3 => x_assoc_objects3,
1390 x_assoc_objects4 => x_assoc_objects4,
1391 x_assoc_objects5 => x_assoc_objects5,
1392 x_assoc_names => x_assoc_names,
1393 x_assoc_codes => x_assoc_codes,
1394 x_return_status => x_return_status,
1395 x_msg_count => x_msg_count,
1396 x_msg_data => x_msg_data
1397 );
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 RAISE;
1401 END Get_Associations;
1402
1403 PROCEDURE Get_Associations (
1404 p_api_version IN NUMBER,
1405 p_init_msg_list IN VARCHAR2,
1406 p_content_item_id IN NUMBER,
1407 p_citem_version_id IN NUMBER,
1408 p_assoc_type_codes IN JTF_VARCHAR2_TABLE_100,
1409 p_assoc_objects1 IN JTF_VARCHAR2_TABLE_300,
1410 p_assoc_objects2 IN JTF_VARCHAR2_TABLE_300,
1411 p_assoc_objects3 IN JTF_VARCHAR2_TABLE_300,
1412 p_assoc_objects4 IN JTF_VARCHAR2_TABLE_300,
1413 p_assoc_objects5 IN JTF_VARCHAR2_TABLE_300,
1414 x_assoc_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1415 x_assoc_objects1 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1416 x_assoc_objects2 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1417 x_assoc_objects3 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1418 x_assoc_objects4 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1419 x_assoc_objects5 OUT NOCOPY JTF_VARCHAR2_TABLE_300,
1420 x_assoc_names OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
1421 x_assoc_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1425 ) AS
1422 x_return_status OUT NOCOPY VARCHAR2,
1423 x_msg_count OUT NOCOPY NUMBER,
1424 x_msg_data OUT NOCOPY VARCHAR2
1426 --******** local variable for standards **********
1427 l_api_name CONSTANT VARCHAR2(30) := 'Get_Associations';
1428 l_api_version CONSTANT NUMBER := 1.0;
1429 --
1430 pre_plsql_block VARCHAR2(10) := 'BEGIN ';
1431 post_plsql_block VARCHAR2(200) := '.Get_Object_Name(:at, :v1, :v2, :v3, :v4, :v5, :xn, :xc, :xret, :xmc, :md); END;';
1432 l_assoc_name VARCHAR2(4000);
1433 l_assoc_code VARCHAR2(100);
1434
1435 l_count NUMBER := 1;
1436 l_assoc_type_code VARCHAR2(100) := NULL;
1437 l_callback_pkg VARCHAR2(30);
1438 l_assoc_object2 VARCHAR2(254) := NULL;
1439 l_assoc_object3 VARCHAR2(254) := NULL;
1440 l_assoc_object4 VARCHAR2(254) := NULL;
1441 l_assoc_object5 VARCHAR2(254) := NULL;
1442 --
1443 CURSOR Get_Assoc IS
1444 SELECT a.ASSOCIATION_TYPE_CODE, a.ASSOCIATED_OBJECT_VAL1, a.ASSOCIATED_OBJECT_VAL2,
1445 a.ASSOCIATED_OBJECT_VAL3, a.ASSOCIATED_OBJECT_VAL4, a.ASSOCIATED_OBJECT_VAL5,
1446 t.CALL_BACK_PKG
1447 FROM IBC_ASSOCIATIONS a, IBC_ASSOCIATION_TYPES_B t
1448 WHERE a.CONTENT_ITEM_ID = p_content_item_id AND
1449 ((a.citem_version_id IS NULL AND p_citem_version_id IS NULL)
1450 OR
1451 (a.citem_version_id = p_citem_version_id)) AND
1452 a.ASSOCIATION_TYPE_CODE = t.ASSOCIATION_TYPE_CODE;
1453
1454 CURSOR Get_CallBack IS
1455 SELECT CALL_BACK_PKG
1456 FROM IBC_ASSOCIATION_TYPES_B
1457 WHERE ASSOCIATION_TYPE_CODE = l_assoc_type_code;
1458
1459 BEGIN
1460 -- ******************* Standard Begins *******************
1461 -- Standard call to check for call compatibility.
1462 IF NOT Fnd_Api.Compatible_API_Call (
1463 l_api_version,
1464 p_api_version,
1465 l_api_name,
1466 G_PKG_NAME)
1467 THEN
1468 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1469 END IF;
1470 -- Initialize message list if p_init_msg_list is set to TRUE.
1471 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
1472 Fnd_Msg_Pub.initialize;
1473 END IF;
1474
1475 -- Initialize API return status to success
1476 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1477 -- ******************* Real Logic Start *********************
1478
1479 -- Validate Citem Id
1480 IF (p_content_item_id IS NOT NULL) THEN
1481 IF (Ibc_Validate_Pvt.isValidCitem(p_content_item_id) = Fnd_Api.g_false) THEN
1482 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1483 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
1484 Fnd_Message.Set_token('CITEM_ID', p_content_item_id);
1485 Fnd_Msg_Pub.ADD;
1486 END IF;
1487 RAISE Fnd_Api.G_EXC_ERROR;
1488 END IF;
1489 END IF;
1490 IF p_citem_version_id IS NOT NULL AND
1491 IBC_VALIDATE_PVT.isValidCitemVerForCitem(p_content_item_id, p_citem_version_id) = FND_API.g_false
1492 THEN
1493 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1494 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
1495 Fnd_Message.Set_token('CITEM_VERSION_ID', p_citem_version_id);
1496 Fnd_Msg_Pub.ADD;
1497 END IF;
1498 RAISE Fnd_Api.G_EXC_ERROR;
1499 END IF;
1500
1501
1502 -- Initialize OUT NOCOPYput parameters
1503 x_assoc_type_codes := JTF_VARCHAR2_TABLE_100();
1504 x_assoc_objects1 := JTF_VARCHAR2_TABLE_300();
1505 x_assoc_objects2 := JTF_VARCHAR2_TABLE_300();
1506 x_assoc_objects3 := JTF_VARCHAR2_TABLE_300();
1507 x_assoc_objects4 := JTF_VARCHAR2_TABLE_300();
1508 x_assoc_objects5 := JTF_VARCHAR2_TABLE_300();
1509 x_assoc_names := JTF_VARCHAR2_TABLE_4000();
1510 x_assoc_codes := JTF_VARCHAR2_TABLE_100();
1511
1512 -- Retrieive Existing Associations
1513 FOR assoc_rec IN Get_Assoc LOOP
1514 IF (assoc_rec.CALL_BACK_PKG IS NULL) THEN
1515 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1516 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_IS_NULL');
1517 Fnd_Message.Set_token('ASSOC_TYPE_CODE', assoc_rec.ASSOCIATION_TYPE_CODE);
1518 Fnd_Msg_Pub.ADD;
1519 END IF;
1520 RAISE Fnd_Api.G_EXC_ERROR;
1521 END IF;
1522
1523 BEGIN
1524 -- Execute CallBack
1525 EXECUTE IMMEDIATE (pre_plsql_block || assoc_rec.CALL_BACK_PKG || post_plsql_block)
1526 USING assoc_rec.ASSOCIATION_TYPE_CODE,
1527 assoc_rec.ASSOCIATED_OBJECT_VAL1, assoc_rec.ASSOCIATED_OBJECT_VAL2,
1528 assoc_rec.ASSOCIATED_OBJECT_VAL3, assoc_rec.ASSOCIATED_OBJECT_VAL4,
1529 assoc_rec.ASSOCIATED_OBJECT_VAL5, OUT l_assoc_name, OUT l_assoc_code,
1530 OUT x_return_status, OUT x_msg_count, OUT x_msg_data;
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 IF (SQLCODE = -6550) THEN
1534 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1535 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_INVALID');
1536 Fnd_Message.Set_token('CALL_BACK_PKG', assoc_rec.CALL_BACK_PKG);
1537 Fnd_Msg_Pub.ADD;
1538 END IF;
1539 RAISE Fnd_Api.G_EXC_ERROR;
1540 ELSE
1541 RAISE;
1542 END IF;
1543 END;
1544
1545 x_assoc_type_codes.EXTEND();
1546 x_assoc_type_codes(l_count) := assoc_rec.ASSOCIATION_TYPE_CODE;
1547 x_assoc_objects1.EXTEND();
1548 x_assoc_objects1(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL1;
1549 x_assoc_objects2.EXTEND();
1550 x_assoc_objects2(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL2;
1551 x_assoc_objects3.EXTEND();
1552 x_assoc_objects3(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL3;
1553 x_assoc_objects4.EXTEND();
1554 x_assoc_objects4(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL4;
1555 x_assoc_objects5.EXTEND();
1559 x_assoc_codes.EXTEND();
1556 x_assoc_objects5(l_count) := assoc_rec.ASSOCIATED_OBJECT_VAL5;
1557 x_assoc_names.EXTEND();
1558 x_assoc_names(l_count) := l_assoc_name;
1560 x_assoc_codes(l_count) := l_assoc_code;
1561
1562 l_count := l_count + 1;
1563 END LOOP;
1564
1565 -- Get Names/Codes for additional input associations
1566 IF (p_assoc_type_codes IS NOT NULL) THEN
1567
1568 FOR i IN 1..p_assoc_type_codes.COUNT LOOP
1569 IF ((l_assoc_type_code IS NULL) OR (p_assoc_type_codes(i) <> l_assoc_type_code)) THEN
1570 l_assoc_type_code := p_assoc_type_codes(i);
1571 OPEN Get_CallBack;
1572 FETCH Get_CallBack INTO l_callback_pkg;
1573 IF (Get_CallBack%NOTFOUND) THEN
1574 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1575 Fnd_Message.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
1576 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_assoc_type_code);
1577 Fnd_Msg_Pub.ADD;
1578 END IF;
1579 RAISE Fnd_Api.G_EXC_ERROR;
1580 END IF;
1581 IF (l_callback_pkg IS NULL) THEN
1582 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1583 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_IS_NULL');
1584 Fnd_Message.Set_token('ASSOC_TYPE_CODE', l_assoc_type_code);
1585 Fnd_Msg_Pub.ADD;
1586 END IF;
1587 RAISE Fnd_Api.G_EXC_ERROR;
1588 END IF;
1589 CLOSE Get_CallBack;
1590 END IF;
1591
1592 IF (p_assoc_objects2 IS NOT NULL) THEN
1593 l_assoc_object2 := p_assoc_objects2(i);
1594 END IF;
1595 IF (p_assoc_objects3 IS NOT NULL) THEN
1596 l_assoc_object3 := p_assoc_objects3(i);
1597 END IF;
1598 IF (p_assoc_objects4 IS NOT NULL) THEN
1599 l_assoc_object4 := p_assoc_objects4(i);
1600 END IF;
1601 IF (p_assoc_objects5 IS NOT NULL) THEN
1602 l_assoc_object5 := p_assoc_objects5(i);
1603 END IF;
1604
1605 BEGIN
1606 -- Execute CallBack
1607 EXECUTE IMMEDIATE (pre_plsql_block || l_callback_pkg || post_plsql_block)
1608 USING l_assoc_type_code,
1609 p_assoc_objects1(i), l_assoc_object2,
1610 l_assoc_object3, l_assoc_object4,
1611 l_assoc_object5, OUT l_assoc_name, OUT l_assoc_code,
1612 OUT x_return_status, OUT x_msg_count, OUT x_msg_data;
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 IF (SQLCODE = -6550) THEN
1616 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1617 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_INVALID');
1618 Fnd_Message.Set_token('CALL_BACK_PKG', l_callback_pkg);
1619 Fnd_Msg_Pub.ADD;
1620 END IF;
1621 RAISE Fnd_Api.G_EXC_ERROR;
1622 ELSE
1623 RAISE;
1624 END IF;
1625 END;
1626
1627 x_assoc_type_codes.EXTEND();
1628 x_assoc_type_codes(l_count) := l_assoc_type_code;
1629 x_assoc_objects1.EXTEND();
1630 x_assoc_objects1(l_count) := p_assoc_objects1(i);
1631 x_assoc_objects2.EXTEND();
1632 x_assoc_objects2(l_count) := l_assoc_object2;
1633 x_assoc_objects3.EXTEND();
1634 x_assoc_objects3(l_count) := l_assoc_object3;
1635 x_assoc_objects4.EXTEND();
1636 x_assoc_objects4(l_count) := l_assoc_object4;
1637 x_assoc_objects5.EXTEND();
1638 x_assoc_objects5(l_count) := l_assoc_object5;
1639 x_assoc_names.EXTEND();
1640 x_assoc_names(l_count) := l_assoc_name;
1641 x_assoc_codes.EXTEND();
1642 x_assoc_codes(l_count) := l_assoc_code;
1643
1644 l_count := l_count + 1;
1645 END LOOP;
1646
1647 END IF;
1648
1649 --******************* Real Logic End *********************
1650
1651 -- Standard call to get message count and if count=1, get the message
1652 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1653 p_data => x_msg_data);
1654 EXCEPTION
1655 WHEN Fnd_Api.G_EXC_ERROR THEN
1656 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1657 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1658 p_data => x_msg_data);
1659 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1660 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1664 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1661 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1662 p_data => x_msg_data);
1663 WHEN OTHERS THEN
1665 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1666 THEN
1667 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1668 END IF;
1669 Fnd_Msg_Pub.Count_And_Get ( p_count => x_msg_count,
1670 p_data => x_msg_data);
1671 END Get_Associations;
1672
1673 FUNCTION Get_Association_NameAndCode(p_content_item_id IN NUMBER,
1674 p_citem_version_id IN NUMBER,
1675 p_assoc_type_code IN VARCHAR2,
1676 p_assoc_object1 IN VARCHAR2,
1677 p_assoc_object2 IN VARCHAR2,
1678 p_assoc_object3 IN VARCHAR2,
1679 p_assoc_object4 IN VARCHAR2,
1680 p_assoc_object5 IN VARCHAR2
1681 )
1682 RETURN VARCHAR2
1683 IS
1684 l_assoc_name VARCHAR2(300);
1685 l_assoc_code VARCHAR2(80);
1686 l_result VARCHAR2(300);
1687 l_return_status VARCHAR2(30);
1688 l_msg_count NUMBER;
1689 l_msg_data VARCHAR2(2000);
1690
1691 pre_plsql_block VARCHAR2(10) := 'BEGIN ';
1692 post_plsql_block VARCHAR2(200) := '.Get_Object_Name(:at, :v1, :v2, :v3, :v4, :v5, :xn, :xc, :xret, :xmc, :md); END;';
1693
1694 CURSOR c_callback(p_assoc_type_code VARCHAR2) IS
1695 SELECT call_back_pkg
1696 FROM ibc_association_types_b
1697 WHERE association_type_code = p_assoc_type_code;
1698
1699 BEGIN
1700
1701 l_result := NULL;
1702
1703 FOR r_callback IN c_callback(p_assoc_type_code) LOOP
1704
1705 BEGIN
1706 -- Execute CallBack
1707 EXECUTE IMMEDIATE (pre_plsql_block || r_callback.CALL_BACK_PKG || post_plsql_block)
1708 USING p_assoc_type_code,
1709 p_assoc_object1, p_assoc_object2,
1710 p_assoc_object3, p_assoc_object4,
1711 p_assoc_object5, OUT l_assoc_name, OUT l_assoc_code,
1712 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 IF (SQLCODE = -6550) THEN
1716 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1717 Fnd_Message.Set_Name('IBC', 'IBC_CALL_BACK_PKG_INVALID');
1718 Fnd_Message.Set_token('CALL_BACK_PKG', r_callback.CALL_BACK_PKG);
1719 Fnd_Msg_Pub.ADD;
1720 END IF;
1721 RAISE Fnd_Api.G_EXC_ERROR;
1722 ELSE
1723 RAISE;
1724 END IF;
1725 END;
1726
1727 END LOOP;
1728
1729 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1730 /*
1731 DBMS_OUTPUT.put_line('Errors FOUND '|| l_msg_data);
1732 for i in 0..l_msg_count loop
1733 DBMS_OUTPUT.put_line(FND_MSG_PUB.get(i,FND_API.G_FALSE));
1734 end loop;
1735 */
1736 l_result := NULL;
1737 ELSE
1738 l_result := l_assoc_code || '|' || l_assoc_name;
1739 END IF;
1740
1741 RETURN l_result;
1742
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 l_result := NULL;
1746 RETURN l_result;
1747
1748 END Get_Association_NameAndCode;
1749
1750
1751 END Ibc_Associations_Grp;