[Home] [Help]
PACKAGE BODY: APPS.CSI_II_RELATIONSHIPS_PUB
Source
1 PACKAGE BODY csi_ii_relationships_pub AS
2 /* $Header: csipiirb.pls 120.3 2008/05/30 00:48:27 fli ship $ */
3 -- start of comments
4 -- package name : csi_ii_relationships_pub
5 -- purpose :
6 -- history :
7 -- note :
8 -- END of comments
9
10
11 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_ii_relationships_pub';
12 g_file_name CONSTANT VARCHAR2(12) := 'csipiirb.pls';
13
14
15 PROCEDURE get_relationships
16 (
17 p_api_version IN NUMBER,
18 p_commit IN VARCHAR2,
19 p_init_msg_list IN VARCHAR2,
20 p_validation_level IN NUMBER,
21 p_relationship_query_rec IN csi_datastructures_pub.relationship_query_rec,
22 p_depth IN NUMBER,
23 p_time_stamp IN DATE,
24 p_active_relationship_only IN VARCHAR2,
25 x_relationship_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2
29 )
30 is
31 l_api_name CONSTANT VARCHAR2(30) := 'get_relationships';
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_return_status_full VARCHAR2(1);
34 l_access_flag VARCHAR2(1);
35 i NUMBER := 1;
36 l_flag VARCHAR2(1) :='N';
37 l_debug_level NUMBER;
38
39 BEGIN
40 -- standard start of api SAVEPOINT
41 --SAVEPOINT get_relationships_pub;
42
43 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
44
45 csi_utility_grp.check_ib_active;
46
47 -- standard call TO check for call compatibility.
48 IF NOT fnd_api.compatible_api_call ( l_api_version,
49 p_api_version,
50 l_api_name,
51 g_pkg_name)
52 THEN
53 RAISE fnd_api.g_exc_unexpected_error;
54 END IF;
55
56
57 -- initialize message list IF p_init_msg_list is set TO true.
58 IF fnd_api.to_boolean( p_init_msg_list )
59 THEN
60 fnd_msg_pub.initialize;
61 END IF;
62
63
64
65 -- initialize api return status TO success
66 x_return_status := fnd_api.g_ret_sts_success;
67
68 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
69 -- IF debug_level = 1 THEN dump the PROCEDURE name
70 IF (l_debug_level > 0) THEN
71 CSI_gen_utility_pvt.put_line( 'get_relationships');
72 END IF;
73
74 -- IF the debug level = 2 THEN dump all the parameters values.
75 IF (l_debug_level > 1) THEN
76 CSI_gen_utility_pvt.put_line(
77 p_api_version ||'-'||
78 p_commit ||'-'||
79 p_init_msg_list ||'-'||
80 p_validation_level ||'-'||
81 p_depth ||'_'||
82 p_time_stamp );
83
84 -- dump the relationship query records
85 csi_gen_utility_pvt.dump_rel_query_rec(p_relationship_query_rec);
86 END IF;
87
88 /***** srramakr commented for bug # 3304439
89 -- check for the profile option AND enable trace
90 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
91 -- END enable trace
92 ****/
93
94 --
95 -- api body
96 --
97 -- debug message
98
99 csi_ii_relationships_pvt.get_relationships(
100 p_api_version => p_api_version,
101 p_commit => fnd_api.g_false,
102 p_init_msg_list => p_init_msg_list,
103 p_validation_level => p_validation_level,
104 p_relationship_query_rec => p_relationship_query_rec,
105 p_depth => p_depth,
106 p_time_stamp => p_time_stamp,
107 p_active_relationship_only => p_active_relationship_only,
108 x_relationship_tbl => x_relationship_tbl,
109 x_return_status => x_return_status,
110 x_msg_count => x_msg_count,
111 x_msg_data => x_msg_data
112 );
113
114
115
116
117 -- check return status FROM the above PROCEDURE call
118 IF x_return_status = fnd_api.g_ret_sts_error THEN
119 RAISE fnd_api.g_exc_error;
120 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
121 RAISE fnd_api.g_exc_unexpected_error;
122 END IF;
123
124 --
125 -- END of api body
126 --
127 /***** srramakr commented for bug # 3304439
128 IF (l_flag = 'Y') THEN
129 dbms_session.set_sql_trace(FALSE);
130 END IF;
131 ****/
132
133 -- standard call TO get message count AND IF count is 1, get message info.
134 fnd_msg_pub.count_and_get
135 ( p_count => x_msg_count,
136 p_data => x_msg_data
137 );
138
139 EXCEPTION
140 WHEN fnd_api.g_exc_error THEN
141 -- ROLLBACK TO get_relationships_pub;
142 x_return_status := fnd_api.g_ret_sts_error ;
143 fnd_msg_pub.count_and_get
144 (p_count => x_msg_count ,
145 p_data => x_msg_data
146 );
147
148 WHEN fnd_api.g_exc_unexpected_error THEN
149 -- ROLLBACK TO get_relationships_pub;
150 x_return_status := fnd_api.g_ret_sts_unexp_error ;
151 fnd_msg_pub.count_and_get
152 (p_count => x_msg_count ,
153 p_data => x_msg_data
154 );
155
156 WHEN others THEN
157 -- ROLLBACK TO get_relationships_pub;
158 x_return_status := fnd_api.g_ret_sts_unexp_error ;
159 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
160 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
161 END IF;
162 fnd_msg_pub.count_and_get
163 (p_count => x_msg_count ,
164 p_data => x_msg_data
165 );
166 END get_relationships;
167
168 PROCEDURE create_relationship(
169 p_api_version IN NUMBER,
170 p_commit IN VARCHAR2,
171 p_init_msg_list IN VARCHAR2,
172 p_validation_level IN NUMBER,
173 p_relationship_tbl IN OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
174 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2
178 )
179 is
180 l_api_name CONSTANT VARCHAR2(30) := 'create_relationship';
181 l_api_version CONSTANT NUMBER := 1.0;
182 l_return_status_full VARCHAR2(1);
183 l_access_flag VARCHAR2(1);
184 l_ii_relationship_rec csi_datastructures_pub.ii_relationship_rec;
185 l_line_count NUMBER;
186 l_relationship_id NUMBER;
187 l_object_version_number NUMBER;
188 l_transaction_rec csi_datastructures_pub.transaction_rec :=p_txn_rec;
189 l_flag VARCHAR2(1) :='N';
190 l_debug_level NUMBER;
191 l_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
192 l_dummy_tbl csi_datastructures_pub.ii_relationship_tbl;
193 l_msg_count NUMBER;
194 l_msg_data VARCHAR2(2000);
195 l_msg_index NUMBER;
196 -- Begin Add Code for Siebel Genesis Project
197 l_owner_party_id NUMBER;
198 l_internal_party_id NUMBER;
199 l_raise_bes_event VARCHAR2(1) := nvl(fnd_profile.value('CSI_RAISE_BES_CUST_OWNED_INSTANCES'),'N');
200 l_relationship_exists VARCHAR2(1);
201 l_root_asset_id NUMBER;
202 -- End Add Code for Siebel Genesis Project
203 BEGIN
204 -- standard start of api savepoint
205 SAVEPOINT create_relationship_pub;
206
207 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
208
209 csi_utility_grp.check_ib_active;
210
211 -- standard call TO check for call compatibility.
212 IF NOT fnd_api.compatible_api_call ( l_api_version,
213 p_api_version,
214 l_api_name,
215 g_pkg_name)
216 THEN
217 RAISE fnd_api.g_exc_unexpected_error;
218 END IF;
219
220
221 -- initialize message list IF p_init_msg_list is set TO true.
222 IF fnd_api.to_boolean( p_init_msg_list )
223 THEN
224 fnd_msg_pub.initialize;
225 END IF;
226
227
228
229 -- initialize api return status TO success
230 x_return_status := fnd_api.g_ret_sts_success;
231
232 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
233 -- IF debug_level = 1 THEN dump the PROCEDURE name
234 IF (l_debug_level > 0) THEN
235 CSI_gen_utility_pvt.put_line( 'create_relationship');
236 END IF;
237
238 -- IF the debug level = 2 THEN dump all the parameters values.
239 IF (l_debug_level > 1) THEN
240
241
242 CSI_gen_utility_pvt.put_line(
243 p_api_version ||'-'||
244 p_commit ||'-'||
245 p_init_msg_list ||'-'||
246 p_validation_level );
247
248 -- dump the relationship query records
249 csi_gen_utility_pvt.dump_rel_tbl(p_relationship_tbl);
250 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
251
252 END IF;
253
254 /***** srramakr commented for bug # 3304439
255 -- check for the profile option AND enable trace
256 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
257 -- END enable trace
258 ****/
259
260
261 -- calling private package: create_relationships
262 -- hint: primary key needs TO be returned
263 l_line_count := p_relationship_tbl.count;
264
265 FOR l_count IN 1..l_line_count LOOP
266
267 BEGIN
268 SELECT relationship_id,
269 object_version_number
270 INTO l_relationship_id,
271 l_object_version_number
272 FROM csi_ii_relationships
273 where relationship_id = (select max(relationship_id)
274 from CSI_II_RELATIONSHIPS
275 WHERE object_id=p_relationship_tbl(l_count).object_id
276 AND subject_id=p_relationship_tbl(l_count).subject_id -- sguthiva added for bug 2370120
277 AND nvl(position_reference,fnd_api.g_miss_char) =
278 nvl(p_relationship_tbl(l_count).position_reference,fnd_api.g_miss_char)
279 AND relationship_type_code=p_relationship_tbl(l_count).relationship_type_code
280 AND active_end_date IS NOT NULL);
281 --
282 l_relationship_tbl(1):=p_relationship_tbl(l_count);
283 l_relationship_tbl(1).relationship_id:=l_relationship_id;
284 l_relationship_tbl(1).object_version_number:=l_object_version_number;
285 l_relationship_tbl(1).subject_id:=p_relationship_tbl(l_count).subject_id;
286
287 IF p_relationship_tbl(l_count).active_end_date IS NULL OR
288 p_relationship_tbl(l_count).active_end_date = fnd_api.g_miss_date
289 THEN
290 l_relationship_tbl(1).active_end_date:=NULL;
291 END IF;
292
293 csi_ii_relationships_pvt.update_relationship(
294 p_api_version => p_api_version,
295 p_commit => fnd_api.g_false,
296 p_init_msg_list => p_init_msg_list,
297 p_validation_level => p_validation_level,
298 p_relationship_tbl => l_relationship_tbl,
299 p_txn_rec => p_txn_rec,
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data
303 );
304
305 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
306 l_msg_index := 1;
307 l_msg_count := x_msg_count;
308 WHILE l_msg_count > 0 LOOP
309 x_msg_data := FND_MSG_PUB.GET
310 ( l_msg_index,
311 FND_API.G_FALSE );
312 csi_gen_utility_pvt.put_line( ' Error from Update_relationship PVT..');
313 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
314 l_msg_index := l_msg_index + 1;
315 l_msg_count := l_msg_count - 1;
316 END LOOP;
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 -- l_relationship_tbl:=l_dummy_tbl;
322 l_relationship_tbl(1):=p_relationship_tbl(l_count);
323 csi_ii_relationships_pvt.create_relationship(
324 p_api_version => p_api_version,
325 p_commit => fnd_api.g_false,
326 p_init_msg_list => p_init_msg_list,
327 p_validation_level => p_validation_level,
328 p_relationship_tbl => l_relationship_tbl,
329 p_txn_rec => p_txn_rec,
330 x_return_status => x_return_status,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data
333 );
334
335 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
336 l_msg_index := 1;
337 l_msg_count := x_msg_count;
338 WHILE l_msg_count > 0 LOOP
339 x_msg_data := FND_MSG_PUB.GET
340 ( l_msg_index,
341 FND_API.G_FALSE );
342 csi_gen_utility_pvt.put_line( ' Error from Create_relationship PVT..');
343 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
344 l_msg_index := l_msg_index + 1;
345 l_msg_count := l_msg_count - 1;
346 END LOOP;
347 RAISE FND_API.G_EXC_ERROR;
348 END IF;
349
350 END;
351 p_relationship_tbl(l_count).relationship_id:=l_relationship_tbl(1).relationship_id;
352
353 -- Begin Add Code for Siebel Genesis Project
354 IF l_raise_bes_event = 'Y' THEN
355 BEGIN
356 SELECT owner_party_id
357 INTO l_owner_party_id
358 FROM csi_item_instances
359 WHERE instance_id = p_relationship_tbl(l_count).object_id;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 l_owner_party_id := null;
363 END;
364
365 BEGIN
366 SELECT internal_party_id
367 INTO l_internal_party_id
368 FROM csi_install_parameters;
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 l_internal_party_id := null;
372 END;
373
374 IF l_internal_party_id <> l_owner_party_id THEN
375 csi_gen_utility_pvt.put_line(' Calling CSI_BUSINESS_EVENT_PVT.UPDATE_INSTANCE_EVENT');
376 -- Check if relationships is already been built then allow update
377 BEGIN
378 SELECT 'Y'
379 INTO l_relationship_exists
380 FROM csi_ii_relationships
381 WHERE relationship_type_code = 'COMPONENT-OF'
382 AND nvl(active_end_date, sysdate + 1) >= sysdate
383 AND (object_id = p_relationship_tbl(l_count).object_id OR
384 subject_id = p_relationship_tbl(l_count).object_id);
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 l_relationship_exists := 'N';
388 WHEN TOO_MANY_ROWS THEN
389 l_relationship_exists := 'Y';
390 END;
391
392 IF l_relationship_exists = 'Y' THEN
393 csi_gen_utility_pvt.put_line('In relationships, therefore raise the update instance event');
394 csi_gen_utility_pvt.put_line('The Subject Id '||p_relationship_tbl(l_count).subject_id);
395 csi_gen_utility_pvt.put_line('The Object Id '||p_relationship_tbl(l_count).object_id);
396 csi_gen_utility_pvt.put_line('The relationship_type_code '||p_relationship_tbl(l_count).relationship_type_code);
397
398 l_root_asset_id :=csi_ii_relationships_pvt.Get_Root_Parent(p_relationship_tbl(l_count).subject_id,
399 p_relationship_tbl(l_count).relationship_type_code,
400 p_relationship_tbl(l_count).object_id);
401 END IF;
402 --Bug 6990065, base bug 6916919, by requirement, update event should be raised instead of create event when relationship is created
403 CSI_BUSINESS_EVENT_PVT.UPDATE_INSTANCE_EVENT
404 (p_api_version => p_api_version
405 ,p_commit => fnd_api.g_false
406 ,p_init_msg_list => p_init_msg_list
407 ,p_validation_level => p_validation_level
408 ,p_instance_id => p_relationship_tbl(l_count).subject_id
409 ,p_subject_instance_id => nvl(l_root_asset_id, p_relationship_tbl(l_count).object_id)
410 ,x_return_status => x_return_status
411 ,x_msg_count => x_msg_count
412 ,x_msg_data => x_msg_data
413 );
414
415 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
416 l_msg_index := 1;
417 l_msg_count := x_msg_count;
418
419 WHILE l_msg_count > 0 LOOP
420 x_msg_data := FND_MSG_PUB.GET
421 (l_msg_index,
422 FND_API.G_FALSE );
423 csi_gen_utility_pvt.put_line('Error from CSI_BUSINESS_EVENT.UPDATE_INSTANCE_EVENT');
424 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
425 l_msg_index := l_msg_index + 1;
426 l_msg_count := l_msg_count - 1;
427 END LOOP;
428 RAISE FND_API.G_EXC_ERROR;
429 END IF;
430 END IF;
431 END IF;
432 -- End Add Code for Siebel Genesis Project
433 END LOOP;
434
435 -- Here we call update_version_time to update date_time_stamp of
436 -- version labels created with this transaction_id to sysdate.
437 csi_item_instance_pvt.update_version_time
438 ( p_api_version => p_api_version
439 ,p_commit => fnd_api.g_false
440 ,p_init_msg_list => p_init_msg_list
441 ,p_validation_level => p_validation_level
442 ,p_txn_rec => p_txn_rec
443 ,x_return_status => x_return_status
444 ,x_msg_count => x_msg_count
445 ,x_msg_data => x_msg_data);
446
447 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
448 l_msg_index := 1;
449 l_msg_count := x_msg_count;
450 WHILE l_msg_count > 0 LOOP
451 x_msg_data := FND_MSG_PUB.GET
452 ( l_msg_index,
453 FND_API.G_FALSE );
454 csi_gen_utility_pvt.put_line( ' Error from UPDATE_VERSION_TIME..');
455 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
456 l_msg_index := l_msg_index + 1;
457 l_msg_count := l_msg_count - 1;
458 END LOOP;
459 RAISE FND_API.G_EXC_ERROR;
460 END IF;
461
462
463 -- check return status FROM the above PROCEDURE call
464 IF x_return_status = fnd_api.g_ret_sts_error THEN
465 RAISE fnd_api.g_exc_error;
466 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
467 RAISE fnd_api.g_exc_unexpected_error;
468 END IF;
469
470 --
471 -- END of api body.
472 --
473
474 -- standard check for p_commit
475 IF fnd_api.to_boolean( p_commit )
476 THEN
477 COMMIT WORK;
478 END IF;
479
480 /***** srramakr commented for bug # 3304439
481 IF (l_flag = 'Y') THEN
482 dbms_session.set_sql_trace(FALSE);
483 END IF;
484 ****/
485 -- standard call TO get message count AND IF count is 1, get message info.
486 fnd_msg_pub.count_and_get
487 ( p_count => x_msg_count,
488 p_data => x_msg_data
489 );
490
491 EXCEPTION
492 WHEN fnd_api.g_exc_error THEN
493 ROLLBACK TO create_relationship_pub;
494 x_return_status := fnd_api.g_ret_sts_error ;
495 fnd_msg_pub.count_and_get
496 (p_count => x_msg_count ,
497 p_data => x_msg_data
498 );
499
500 WHEN fnd_api.g_exc_unexpected_error THEN
501 ROLLBACK TO create_relationship_pub;
502 x_return_status := fnd_api.g_ret_sts_unexp_error ;
503 fnd_msg_pub.count_and_get
504 (p_count => x_msg_count ,
505 p_data => x_msg_data
506 );
507
508 WHEN OTHERS THEN
509 ROLLBACK TO create_relationship_pub;
510 x_return_status := fnd_api.g_ret_sts_unexp_error ;
511 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
512 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
513 END IF;
514 fnd_msg_pub.count_and_get
515 (p_count => x_msg_count ,
516 p_data => x_msg_data
517 );
518
519 END create_relationship;
520
521
522 PROCEDURE update_relationship
523 (
524 p_api_version IN NUMBER,
525 p_commit IN VARCHAR2,
526 p_init_msg_list IN VARCHAR2,
527 p_validation_level IN NUMBER,
528 p_relationship_tbl IN csi_datastructures_pub.ii_relationship_tbl,
529 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
530 x_return_status OUT NOCOPY VARCHAR2,
531 x_msg_count OUT NOCOPY NUMBER,
532 x_msg_data OUT NOCOPY VARCHAR2
533 )
534 is
535 l_api_name CONSTANT VARCHAR2(30) := 'update_ii_relationships';
536 l_api_version CONSTANT NUMBER := 1.0;
537 -- local variables
538 l_rowid rowid;
539 l_flag VARCHAR2(1) :='N';
540 l_debug_level NUMBER;
541 l_msg_count NUMBER;
542 l_msg_data VARCHAR2(2000);
543 l_msg_index NUMBER;
544 l_line_count NUMBER; -- Added for replacement bug.
545 l_relationship_tbl csi_datastructures_pub.ii_relationship_tbl:=p_relationship_tbl;
546 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl:=p_relationship_tbl;
547 l_replace_flag VARCHAR2(1);
548 BEGIN
549 -- standard start of api savepoint
550 SAVEPOINT update_relationship_pub;
551
552 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
553
554 csi_utility_grp.check_ib_active;
555
556 -- standard call TO check for call compatibility.
557 IF NOT fnd_api.compatible_api_call ( l_api_version,
558 p_api_version,
559 l_api_name,
560 g_pkg_name)
561 THEN
562 RAISE fnd_api.g_exc_unexpected_error;
563 END IF;
564
565
566 -- initialize message list IF p_init_msg_list is set TO true.
567 IF fnd_api.to_boolean( p_init_msg_list )
568 THEN
569 fnd_msg_pub.initialize;
570 END IF;
571
572
573 -- initialize api return status TO success
574 x_return_status := fnd_api.g_ret_sts_success;
575
576 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
577 -- IF debug_level = 1 THEN dump the PROCEDURE name
578 IF (l_debug_level > 0) THEN
579 CSI_gen_utility_pvt.put_line( 'update_relationship');
580 END IF;
581
582 -- IF the debug level = 2 THEN dump all the parameters values.
583 IF (l_debug_level > 1) THEN
584
585
586 CSI_gen_utility_pvt.put_line(
587 p_api_version ||'-'||
588 p_commit ||'-'||
589 p_init_msg_list ||'-'||
590 p_validation_level );
591
592 -- dump the relationship query records
593 csi_gen_utility_pvt.dump_rel_tbl(p_relationship_tbl);
594 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
595
596 END IF;
597
598 /***** srramakr commented for bug # 3304439
599 -- check for the profile option AND enable trace
600 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
601 -- END enable trace
602 ****/
603
604 -- Added for replacement bug
605 l_line_count := l_relationship_tbl.count;
606
607 FOR l_count IN 1..l_line_count
608 LOOP
609 l_replace_flag:=fnd_api.g_false;
610 IF l_relationship_tbl(l_count).relationship_id IS NOT NULL AND
611 l_relationship_tbl(l_count).relationship_id <> fnd_api.g_miss_num AND
612 l_relationship_tbl(l_count).active_end_date IS NOT NULL AND
613 l_relationship_tbl(l_count).active_end_date <> fnd_api.g_miss_date
614 -- Here I got a record which has an end date.
615 THEN
616 FOR l_search IN 1..l_line_count
617 LOOP
618 IF l_relationship_tbl(l_search).subject_id = l_relationship_tbl(l_count).subject_id AND
619 l_relationship_tbl(l_search).object_id <> l_relationship_tbl(l_count).object_id AND
620 (l_relationship_tbl(l_search).active_end_date IS NULL OR
621 l_relationship_tbl(l_search).active_end_date = fnd_api.g_miss_date OR
622 l_relationship_tbl(l_search).active_end_date > SYSDATE)
623 THEN
624 -- Here I found a record with the same subject, meaning parent swap.
625 l_replace_flag:=fnd_api.g_true;
626 EXIT;
627 END IF;
628 END LOOP;
629
630 END IF;
631
632 IF ((l_relationship_tbl(l_count).relationship_id IS NOT NULL AND
633 l_relationship_tbl(l_count).relationship_id <> fnd_api.g_miss_num)) AND
634 x_return_status = FND_API.G_RET_STS_SUCCESS
635 THEN
636
637 l_rel_tbl.delete;
638 l_rel_tbl(1):=l_relationship_tbl(l_count);
639 csi_gen_utility_pvt.put_line('Value of relationship_id is : '||l_rel_tbl(1).relationship_id);
640 csi_gen_utility_pvt.put_line('Value of replace_flag is :'||l_replace_flag);
641 csi_ii_relationships_pvt.update_relationship(
642 p_api_version => p_api_version,
643 p_commit => fnd_api.g_false,
644 p_init_msg_list => p_init_msg_list,
645 p_validation_level => p_validation_level,
646 p_relationship_tbl => l_rel_tbl,
647 p_replace_flag => l_replace_flag,
648 p_txn_rec => p_txn_rec,
649 x_return_status => x_return_status,
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data
652 );
653 END IF;
654
655 END LOOP;
656 -- End addition for replacement
657
658 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
659 l_msg_index := 1;
660 l_msg_count := x_msg_count;
661 WHILE l_msg_count > 0 LOOP
662 x_msg_data := FND_MSG_PUB.GET
663 ( l_msg_index,
664 FND_API.G_FALSE );
665 csi_gen_utility_pvt.put_line( ' Error from csi_ii_relationships_pvt.update_relationship..');
666 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
667 l_msg_index := l_msg_index + 1;
668 l_msg_count := l_msg_count - 1;
669 END LOOP;
670 RAISE FND_API.G_EXC_ERROR;
671 END IF;
672
673 -- Added for replacement
674
675 FOR l_count IN 1..l_line_count
676 LOOP
677 IF ((l_relationship_tbl(l_count).relationship_id IS NULL OR
678 l_relationship_tbl(l_count).relationship_id = fnd_api.g_miss_num)) AND
679 x_return_status = FND_API.G_RET_STS_SUCCESS
680 THEN
681 l_rel_tbl.delete;
682 l_rel_tbl(1):=l_relationship_tbl(l_count);
683 csi_ii_relationships_pvt.create_relationship(
684 p_api_version => p_api_version,
685 p_commit => fnd_api.g_false,
686 p_init_msg_list => p_init_msg_list,
687 p_validation_level => p_validation_level,
688 p_relationship_tbl => l_rel_tbl,
689 p_txn_rec => p_txn_rec,
690 x_return_status => x_return_status,
691 x_msg_count => x_msg_count,
692 x_msg_data => x_msg_data
693 );
694 END IF;
695 END LOOP;
696
697 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
698 l_msg_index := 1;
699 l_msg_count := x_msg_count;
700 WHILE l_msg_count > 0
701 LOOP
702 x_msg_data := FND_MSG_PUB.GET
703 ( l_msg_index,
704 FND_API.G_FALSE );
705 csi_gen_utility_pvt.put_line( ' Error from csi_ii_relationships_pvt.create_relationship..');
706 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
707 l_msg_index := l_msg_index + 1;
708 l_msg_count := l_msg_count - 1;
709 END LOOP;
710 RAISE FND_API.G_EXC_ERROR;
711 END IF;
712
713 -- End addition for replacement
714
715
716 -- check return status FROM the above PROCEDURE call
717 IF x_return_status = fnd_api.g_ret_sts_error THEN
718 RAISE fnd_api.g_exc_error;
719 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
720 RAISE fnd_api.g_exc_unexpected_error;
721 END IF;
722
723 --
724 -- END of api body
725 --
726
727 -- Here we call update_version_time to update date_time_stamp of
728 -- version labels created with this transaction_id to sysdate.
729 csi_item_instance_pvt.update_version_time
730 ( p_api_version => p_api_version
731 ,p_commit => fnd_api.g_false
732 ,p_init_msg_list => p_init_msg_list
733 ,p_validation_level => p_validation_level
734 ,p_txn_rec => p_txn_rec
735 ,x_return_status => x_return_status
736 ,x_msg_count => x_msg_count
737 ,x_msg_data => x_msg_data);
738
739 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
740 l_msg_index := 1;
741 l_msg_count := x_msg_count;
742 WHILE l_msg_count > 0 LOOP
743 x_msg_data := FND_MSG_PUB.GET
744 ( l_msg_index,
745 FND_API.G_FALSE );
746 csi_gen_utility_pvt.put_line( ' Error from UPDATE_VERSION_TIME..');
747 csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
748 l_msg_index := l_msg_index + 1;
749 l_msg_count := l_msg_count - 1;
750 END LOOP;
751 RAISE FND_API.G_EXC_ERROR;
752 END IF;
753 -- standard check for p_commit
754 IF fnd_api.to_boolean( p_commit )
755 THEN
756 COMMIT WORK;
757 END IF;
758
759 /***** srramakr commented for bug # 3304439
760 IF (l_flag = 'Y') THEN
761 dbms_session.set_sql_trace(FALSE);
762 END IF;
763 ****/
764 -- standard call TO get message count AND IF count is 1, get message info.
765 fnd_msg_pub.count_and_get
766 ( p_count => x_msg_count,
767 p_data => x_msg_data
768 );
769
770 EXCEPTION
771 WHEN fnd_api.g_exc_error THEN
772 ROLLBACK TO update_relationship_pub;
773 x_return_status := fnd_api.g_ret_sts_error ;
774 fnd_msg_pub.count_and_get
775 (p_count => x_msg_count ,
776 p_data => x_msg_data
777 );
778
779 WHEN fnd_api.g_exc_unexpected_error THEN
780 ROLLBACK TO update_relationship_pub;
781 x_return_status := fnd_api.g_ret_sts_unexp_error ;
782 fnd_msg_pub.count_and_get
783 (p_count => x_msg_count ,
784 p_data => x_msg_data
785 );
786
787 WHEN OTHERS THEN
788 ROLLBACK TO update_relationship_pub;
789 x_return_status := fnd_api.g_ret_sts_unexp_error ;
790 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
791 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
792 END IF;
793 fnd_msg_pub.count_and_get
794 (p_count => x_msg_count ,
795 p_data => x_msg_data
796 );
797 END update_relationship;
798
799
800 -- hint: add corresponding delete detail table PROCEDUREs IF it's master-detail relationship.
801 -- the master delete PROCEDURE may NOT be needed depends on different business requirements.
802 PROCEDURE expire_relationship
803 (
804 p_api_version IN NUMBER,
805 p_commit IN VARCHAR2,
806 p_init_msg_list IN VARCHAR2,
807 p_validation_level IN NUMBER,
808 p_relationship_rec IN csi_datastructures_pub.ii_relationship_rec,
809 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
810 x_instance_id_lst OUT NOCOPY csi_datastructures_pub.id_tbl,
811 x_return_status OUT NOCOPY VARCHAR2,
812 x_msg_count OUT NOCOPY NUMBER,
813 x_msg_data OUT NOCOPY VARCHAR2
814 )
815 IS
816 l_api_name CONSTANT VARCHAR2(30) := 'delete_ii_relationships';
817 l_api_version CONSTANT NUMBER := 1.0;
818 l_flag VARCHAR2(1) :='N';
819 l_debug_level NUMBER;
820
821
822 BEGIN
823 -- standard start of api savepoint
824 SAVEPOINT expire_relationship_pub;
825
826 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
827
828 csi_utility_grp.check_ib_active;
829
830 -- standard call TO check for call compatibility.
831 IF NOT fnd_api.compatible_api_call ( l_api_version,
832 p_api_version,
833 l_api_name,
834 g_pkg_name)
835 THEN
836 RAISE fnd_api.g_exc_unexpected_error;
837 END IF;
838
839
840 -- initialize message list IF p_init_msg_list is set TO true.
841 IF fnd_api.to_boolean( p_init_msg_list )
842 THEN
843 fnd_msg_pub.initialize;
844 END IF;
845
846
847 -- initialize api return status TO success
848 x_return_status := fnd_api.g_ret_sts_success;
849
850 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
851 -- IF debug_level = 1 THEN dump the PROCEDURE name
852 IF (l_debug_level > 0) THEN
853 CSI_gen_utility_pvt.put_line( 'expire_relationship');
854 END IF;
855
856 -- IF the debug level = 2 THEN dump all the parameters values.
857 IF (l_debug_level > 1) THEN
858
859
860 CSI_gen_utility_pvt.put_line(
861 p_api_version ||'-'||
862 p_commit ||'-'||
863 p_init_msg_list ||'-'||
864 p_validation_level
865 );
866
867 -- dump the relationship query records
868 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
869 csi_gen_utility_pvt.dump_rel_rec(p_relationship_rec);
870
871 END IF;
872
873 /***** srramakr commented for bug # 3304439
874 -- check for the profile option AND enable trace
875 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
876 -- END enable trace
877 *****/
878
879 csi_ii_relationships_pvt.expire_relationship(
880 p_api_version => p_api_version,
881 p_commit => fnd_api.g_false,
882 p_init_msg_list => p_init_msg_list,
883 p_validation_level => p_validation_level,
884 p_relationship_rec => p_relationship_rec,
885 p_txn_rec => p_txn_rec,
886 x_instance_id_lst => x_instance_id_lst,
887 x_return_status => x_return_status,
888 x_msg_count => x_msg_count,
889 x_msg_data => x_msg_data
890 );
891
892
893
894
895
896 -- check return status FROM the above PROCEDURE call
897 IF x_return_status = fnd_api.g_ret_sts_error THEN
898 RAISE fnd_api.g_exc_error;
899 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
900 RAISE fnd_api.g_exc_unexpected_error;
901 END IF;
902
903
904
905 -- standard check for p_commit
906 IF fnd_api.to_boolean( p_commit )
907 THEN
908 COMMIT WORK;
909 END IF;
910
911 /***** srramakr commented for bug # 3304439
912 IF (l_flag = 'Y') THEN
913 dbms_session.set_sql_trace(FALSE);
914 END IF;
915 *****/
916 -- standard call TO get message count AND IF count is 1, get message info.
917 fnd_msg_pub.count_and_get
918 ( p_count => x_msg_count,
919 p_data => x_msg_data
920 );
921
922 EXCEPTION
923 WHEN fnd_api.g_exc_error THEN
924 ROLLBACK TO expire_relationship_pub;
925 x_return_status := fnd_api.g_ret_sts_error ;
926 fnd_msg_pub.count_and_get
927 (p_count => x_msg_count ,
928 p_data => x_msg_data
929 );
930
931 WHEN fnd_api.g_exc_unexpected_error THEN
932 ROLLBACK TO expire_relationship_pub;
933 x_return_status := fnd_api.g_ret_sts_unexp_error ;
934 fnd_msg_pub.count_and_get
935 (p_count => x_msg_count ,
936 p_data => x_msg_data
937 );
938
939 WHEN OTHERS THEN
940 ROLLBACK TO expire_relationship_pub;
941 x_return_status := fnd_api.g_ret_sts_unexp_error ;
942 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
943 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
944 END IF;
945 fnd_msg_pub.count_and_get
946 (p_count => x_msg_count ,
947 p_data => x_msg_data
948 );
949 END expire_relationship;
950
951
952
953 END csi_ii_relationships_pub;