[Home] [Help]
PACKAGE BODY: APPS.CS_SYSTEM_LINK_UTIL
Source
1 PACKAGE BODY CS_System_Link_UTIL AS
2 /* $Header: cscsiutb.pls 115.7 2001/01/04 13:57:23 pkm ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables and types
6 -- ---------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_System_Link_UTIL';
8 G_USER CONSTANT VARCHAR2(30) := FND_GLOBAL.USER_ID;
9 ------------------------------------------------------------
10
11 PROCEDURE Associate_System_With_User
12 (
13 p_api_version_number IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_commit IN VARCHAR := FND_API.G_FALSE,
16 p_system_id IN NUMBER,
17 p_party_id IN NUMBER,
18 x_return_status OUT VARCHAR2,
19 x_msg_count OUT NUMBER,
20 x_msg_data OUT VARCHAR2,
21 x_java_msg OUT VARCHAR2
22 ) IS
23
24 l_api_name CONSTANT VARCHAR2(30) := 'Associate_System_With_User';
25 l_link_id NUMBER;
26 l_dummy NUMBER;
27
28 CURSOR c1 IS
29 SELECT party_id
30 FROM CS_SYSTEM_PARTY_LINKS
31 WHERE party_id = p_party_id
32 AND system_id = p_system_id
33 AND end_date is null;
34
35 BEGIN
36 SAVEPOINT Associate_System_User;
37 -- Initialize message list if p_init_msg_list is set to TRUE.
38 IF FND_API.to_Boolean( p_init_msg_list ) THEN
39 FND_MSG_PUB.initialize;
40 END IF;
41
42 OPEN c1;
43 FETCH c1 INTO l_dummy;
44 -- If there is no present link then insert.
45 IF c1%NOTFOUND THEN
46 SELECT CS_SYSTEM_PARTY_LINKS_S1.NEXTVAL
47 INTO l_link_id
48 FROM dual;
49
50 INSERT INTO CS_SYSTEM_PARTY_LINKS
51 (
52 party_id,
53 system_id,
54 start_date,
55 last_update_date,
56 last_updated_by,
57 created_by,
58 creation_date,
59 system_party_link_id,
60 object_version_number
61 )
62 VALUES
63 (
64 p_party_id,
65 p_system_id,
66 sysdate,
67 sysdate,
68 G_USER,
69 G_USER,
70 sysdate,
71 l_link_id,
72 1
73 );
74 -- Standard check of p_commit.
75 IF FND_API.To_Boolean( p_commit ) THEN
76 COMMIT WORK;
77 END IF;
78 END IF;
79 CLOSE c1;
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 CLOSE c1;
86 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
87 FND_MESSAGE.SET_NAME('CS','CS_INSERT_UNEXP_ERR');
88 FND_MSG_PUB.Add;
89 FND_MSG_PUB.Count_And_Get (
90 p_count => x_msg_count ,
91 p_data => x_msg_data
92 );
93 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
94 ROLLBACK TO Associate_System_User;
95
96 END Associate_System_With_User;
97
98 PROCEDURE Disassociate_System_With_User
99 (
100 p_api_version_number IN NUMBER,
101 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
102 p_commit IN VARCHAR := FND_API.G_FALSE,
103 p_system_id IN NUMBER,
104 p_party_id IN NUMBER,
105 x_return_status OUT VARCHAR2,
106 x_msg_count OUT NUMBER,
107 x_msg_data OUT VARCHAR2,
108 x_java_msg OUT VARCHAR2
109 ) IS
110 l_api_name CONSTANT VARCHAR2(30) := 'Disassociate_System_With_User';
111 l_party_id NUMBER;
112 l_system_id NUMBER;
113 l_dummy NUMBER;
114 CURSOR c1 IS
115 SELECT party_id
116 FROM CS_SYSTEM_PARTY_LINKS
117 WHERE party_id = p_party_id
118 AND system_id = p_system_id
119 AND end_date is null;
120
121 BEGIN
122
123 SAVEPOINT Disassociate_System_User;
124
125 -- Initialize message list if p_init_msg_list is set to TRUE.
126 IF FND_API.to_Boolean( p_init_msg_list ) THEN
127 FND_MSG_PUB.initialize;
128 END IF;
129
130 OPEN c1;
131 FETCH c1 INTO l_dummy;
132 -- If there is no present link then insert.
133 IF c1%NOTFOUND THEN
134 x_return_status := FND_API.G_RET_STS_ERROR ;
135 FND_MESSAGE.SET_NAME('CS','CS_NO_LINK_FOUND');
136 FND_MSG_PUB.Add;
137 FND_MSG_PUB.Count_And_Get (
138 p_count => x_msg_count ,
139 p_data => x_msg_data
140 );
141 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
142 ELSE
143 UPDATE CS_SYSTEM_PARTY_LINKS SET
144 end_date = sysdate,
145 last_update_date = sysdate,
146 last_updated_by = G_USER,
147 object_version_number = object_version_number + 1
148 WHERE party_id = p_party_id AND system_id = p_system_id AND end_date is null;
149
150 x_return_status := FND_API.G_RET_STS_SUCCESS;
151 -- Standard check of p_commit.
152 IF FND_API.To_Boolean( p_commit ) THEN
153 COMMIT WORK;
154 END IF;
155 END IF;
156 CLOSE c1;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 CLOSE c1;
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 FND_MESSAGE.SET_NAME('CS','CS_UPDATE_UNEXP_ERR');
163 FND_MSG_PUB.Add;
164 FND_MSG_PUB.Count_And_Get
165 (
166 p_count => x_msg_count ,
167 p_data => x_msg_data
168 );
169 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
170 ROLLBACK TO Disassociate_System_User;
171
172 END Disassociate_System_With_User;
173
174 PROCEDURE Associate_Name_With_User
175 (
176 p_api_version_number IN NUMBER,
177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178 p_commit IN VARCHAR := FND_API.G_FALSE,
179 p_system_name IN VARCHAR2,
180 p_party_id IN NUMBER,
181 x_return_status OUT VARCHAR2,
182 x_msg_count OUT NUMBER,
183 x_msg_data OUT VARCHAR2,
184 x_java_msg OUT VARCHAR2
185 ) IS
186 l_api_name CONSTANT VARCHAR2(30) := 'Associate_Name_With_User';
187 l_system_id NUMBER;
188 CURSOR c1 IS
189 SELECT system_id
190 FROM CS_SYSTEMS_ALL_VL
191 WHERE name = p_system_name;
192
193 BEGIN
194
195 SAVEPOINT Associate_Name_User;
196
197 -- Initialize message list if p_init_msg_list is set to TRUE.
198 IF FND_API.to_Boolean( p_init_msg_list ) THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201
202 OPEN c1;
203 FETCH c1 INTO l_system_id;
204 -- If there is no present link then insert.
205 IF c1%NOTFOUND THEN
206 x_return_status := FND_API.G_RET_STS_ERROR ;
207 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_NOT_EXIST');
208 FND_MSG_PUB.Add;
209 FND_MSG_PUB.Count_And_Get(
210 p_count => x_msg_count ,
211 p_data => x_msg_data
212 );
213 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
214 ELSE
215 Associate_System_With_User (p_api_version_number,
216 p_init_msg_list,
217 p_commit,
218 l_system_id,
219 p_party_id,
220 x_return_status,
221 x_msg_count,
222 x_msg_data,
223 x_java_msg);
224
225 END IF;
226 CLOSE c1;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 CLOSE c1;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_UNEXP_ERR');
233 FND_MSG_PUB.Add;
234 FND_MSG_PUB.Count_And_Get
235 (
236 p_count => x_msg_count ,
237 p_data => x_msg_data
238 );
239 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
240 ROLLBACK TO Associate_Name_User;
241
242 END Associate_Name_With_User;
243
244 PROCEDURE Disassociate_Name_With_User
245 (
246 p_api_version_number IN NUMBER,
247 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
248 p_commit IN VARCHAR := FND_API.G_FALSE,
249 p_system_name IN VARCHAR2,
250 p_party_id IN NUMBER,
251 x_return_status OUT VARCHAR2,
252 x_msg_count OUT NUMBER,
253 x_msg_data OUT VARCHAR2,
254 x_java_msg OUT VARCHAR2
255 ) IS
256 l_api_name CONSTANT VARCHAR2(30) := 'Disassociate_Name_With_User';
257 l_system_id NUMBER;
258 CURSOR c1 IS
259 SELECT system_id
260 FROM CS_SYSTEMS_ALL_VL
261 WHERE name = p_system_name;
262
263 BEGIN
264
265 SAVEPOINT Disassociate_Name_User;
266
267 -- Initialize message list if p_init_msg_list is set to TRUE.
268 IF FND_API.to_Boolean( p_init_msg_list ) THEN
269 FND_MSG_PUB.initialize;
270 END IF;
271
272 OPEN c1;
273 FETCH c1 INTO l_system_id;
274 -- If there is no present link then insert.
275 IF c1%NOTFOUND THEN
276 x_return_status := FND_API.G_RET_STS_ERROR ;
277 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_NOT_EXIST');
278 FND_MSG_PUB.Add;
279 FND_MSG_PUB.Count_And_Get
280 (
281 p_count => x_msg_count ,
282 p_data => x_msg_data
283 );
284 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
285 ELSE
286 Disassociate_System_With_User (p_api_version_number,
287 p_init_msg_list,
288 p_commit,
289 l_system_id,
290 p_party_id,
291 x_return_status,
292 x_msg_count,
293 x_msg_data,
294 x_java_msg);
295
296 END IF;
297 CLOSE c1;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 CLOSE c1;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_UNEXP_ERR');
304 FND_MSG_PUB.Add;
305 FND_MSG_PUB.Count_And_Get
306 (
307 p_count => x_msg_count ,
308 p_data => x_msg_data
309 );
310 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
311 ROLLBACK TO Disassociate_Name_User;
312
313 END Disassociate_Name_With_User;
314
315 PROCEDURE Associate_System_With_SR
316 (
317 p_api_version_number IN NUMBER,
318 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
319 p_commit IN VARCHAR := FND_API.G_FALSE,
320 p_system_id IN NUMBER,
321 p_service_request_id IN NUMBER,
322 x_return_status OUT VARCHAR2,
323 x_msg_count OUT NUMBER,
324 x_msg_data OUT VARCHAR2,
325 x_java_msg OUT VARCHAR2
326 ) IS
327
328 l_api_name CONSTANT VARCHAR2(30) := 'Associate_System_With_SR';
329 l_link_id NUMBER;
330 l_dummy VARCHAR2(1);
331
332 CURSOR c1 IS
333 SELECT 'x'
334 FROM CS_SYSTEM_SR_LINKS
335 WHERE incident_id = p_service_request_id
336 AND system_id = p_system_id
337 AND end_date is null;
338
339 BEGIN
340
341 SAVEPOINT Associate_System_SR;
342
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344 IF FND_API.to_Boolean( p_init_msg_list ) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 OPEN c1;
349 FETCH c1 INTO l_dummy;
350 -- If there is no present link then insert.
351 IF c1%NOTFOUND THEN
352 SELECT CS_SYSTEM_SR_LINKS_S1.NEXTVAL
353 INTO l_link_id
354 FROM dual;
355
356 INSERT INTO CS_SYSTEM_SR_LINKS
357 (
358 incident_id,
359 system_id,
360 start_date,
361 last_update_date,
362 last_updated_by,
363 created_by,
364 creation_date,
365 system_sr_link_id,
369 (
366 object_version_number
367 )
368 VALUES
370 p_service_request_id,
371 p_system_id,
372 sysdate,
373 sysdate,
374 G_USER,
375 G_USER,
376 sysdate,
377 l_link_id,
378 1
379 );
380 -- Standard check of p_commit.
381 IF FND_API.To_Boolean( p_commit ) THEN
382 COMMIT WORK;
383 END IF;
384 END IF;
385 CLOSE c1;
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 CLOSE c1;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 FND_MESSAGE.SET_NAME('CS','CS_INSERT_UNEXP_ERR');
393 FND_MSG_PUB.Add;
394 FND_MSG_PUB.Count_And_Get (
395 p_count => x_msg_count ,
396 p_data => x_msg_data
397 );
398 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
399 ROLLBACK TO Associate_System_SR;
400
401 END Associate_System_With_SR;
402
403 PROCEDURE Disassociate_System_With_SR
404 (
405 p_api_version_number IN NUMBER,
406 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
407 p_commit IN VARCHAR := FND_API.G_FALSE,
408 p_system_id IN NUMBER,
409 p_service_request_id IN NUMBER,
410 x_return_status OUT VARCHAR2,
411 x_msg_count OUT NUMBER,
412 x_msg_data OUT VARCHAR2,
413 x_java_msg OUT VARCHAR2
414 ) IS
415 l_api_name CONSTANT VARCHAR2(30) := 'Disassociate_System_With_SR';
416 l_service_request_id NUMBER;
417 l_system_id NUMBER;
418 l_dummy VARCHAR2(1);
419
420 CURSOR c1 IS
421 SELECT 'x'
422 FROM CS_SYSTEM_SR_LINKS
423 WHERE incident_id = p_service_request_id
424 AND system_id = p_system_id
425 AND end_date is null;
426
427 BEGIN
428
429 SAVEPOINT Disassociate_System_SR;
430
431 -- Initialize message list if p_init_msg_list is set to TRUE.
432 IF FND_API.to_Boolean( p_init_msg_list ) THEN
433 FND_MSG_PUB.initialize;
434 END IF;
435
436 OPEN c1;
437 FETCH c1 INTO l_dummy;
438 -- If there is no present link then insert.
439 IF c1%NOTFOUND THEN
440 x_return_status := FND_API.G_RET_STS_ERROR ;
441 FND_MESSAGE.SET_NAME('CS','CS_NO_LINK_FOUND');
442 FND_MSG_PUB.Add;
443 FND_MSG_PUB.Count_And_Get(
444 p_count => x_msg_count ,
445 p_data => x_msg_data
446 );
447 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
448 ELSE
449 -- Will only update the end_date of the links that have end_date = null
450 UPDATE CS_SYSTEM_SR_LINKS SET
451 end_date = sysdate,
452 last_update_date = sysdate,
453 last_updated_by = G_USER,
454 object_version_number = object_version_number + 1
455 WHERE incident_id = p_service_request_id AND system_id = p_system_id AND end_date is null;
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457 -- Standard check of p_commit.
458 IF FND_API.To_Boolean( p_commit ) THEN
459 COMMIT WORK;
460 END IF;
461 END IF;
462 CLOSE c1;
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 CLOSE c1;
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 FND_MESSAGE.SET_NAME('CS','CS_UPDATE_UNEXP_ERR');
469 FND_MSG_PUB.Add;
470 FND_MSG_PUB.Count_And_Get(
471 p_count => x_msg_count ,
472 p_data => x_msg_data
473 );
474 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
475 ROLLBACK TO Disassociate_System_SR;
476
477
478 END Disassociate_System_With_SR;
479
480 PROCEDURE Associate_Name_With_SR
481 (
482 p_api_version_number IN NUMBER,
483 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
484 p_commit IN VARCHAR := FND_API.G_FALSE,
485 p_system_name IN VARCHAR2,
486 p_service_request_id IN NUMBER,
487 x_return_status OUT VARCHAR2,
488 x_msg_count OUT NUMBER,
489 x_msg_data OUT VARCHAR2,
490 x_java_msg OUT VARCHAR2
491 ) IS
492 l_api_name CONSTANT VARCHAR2(30) := 'Associate_Name_With_SR';
493 l_system_id NUMBER;
494 CURSOR c1 IS
495 SELECT system_id
496 FROM CS_SYSTEMS_ALL_VL
497 WHERE name = p_system_name;
498
499 BEGIN
500
501 SAVEPOINT Associate_Name_SR;
502
503 -- Initialize message list if p_init_msg_list is set to TRUE.
504 IF FND_API.to_Boolean( p_init_msg_list ) THEN
505 FND_MSG_PUB.initialize;
506 END IF;
507
508 OPEN c1;
509 FETCH c1 INTO l_system_id;
510 -- If there is no present link then insert.
511 IF c1%NOTFOUND THEN
512 x_return_status := FND_API.G_RET_STS_ERROR ;
513 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_NOT_EXIST');
514 FND_MSG_PUB.Add;
515 FND_MSG_PUB.Count_And_Get(
516 p_count => x_msg_count ,
517 p_data => x_msg_data
518 );
519 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
520 ELSE
521 Associate_System_With_SR (p_api_version_number,
522 p_init_msg_list,
523 p_commit,
524 l_system_id,
528 x_msg_data,
525 p_service_request_id,
526 x_return_status,
527 x_msg_count,
529 x_java_msg);
530
531 END IF;
532 CLOSE c1;
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 CLOSE c1;
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_UNEXP_ERR');
539 FND_MSG_PUB.Add;
540 FND_MSG_PUB.Count_And_Get
541 (
542 p_count => x_msg_count ,
543 p_data => x_msg_data
544 );
545 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
546 ROLLBACK TO Associate_Name_SR;
547
548 END Associate_Name_With_SR;
549
550 PROCEDURE Disassociate_Name_With_SR
551 (
552 p_api_version_number IN NUMBER,
553 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
554 p_commit IN VARCHAR := FND_API.G_FALSE,
555 p_system_name IN VARCHAR2,
556 p_service_request_id IN NUMBER,
557 x_return_status OUT VARCHAR2,
558 x_msg_count OUT NUMBER,
559 x_msg_data OUT VARCHAR2,
560 x_java_msg OUT VARCHAR2
561 ) IS
562 l_api_name CONSTANT VARCHAR2(30) := 'Disassociate_Name_With_SR';
563 l_system_id NUMBER;
564 CURSOR c1 IS
565 SELECT system_id
566 FROM CS_SYSTEMS_ALL_VL
567 WHERE name = p_system_name;
568
569 BEGIN
570
571 SAVEPOINT Disassociate_Name_SR;
572
573 -- Initialize message list if p_init_msg_list is set to TRUE.
574 IF FND_API.to_Boolean( p_init_msg_list ) THEN
575 FND_MSG_PUB.initialize;
576 END IF;
577
578 OPEN c1;
579 FETCH c1 INTO l_system_id;
580 -- If there is no present link then insert.
581 IF c1%NOTFOUND THEN
582 x_return_status := FND_API.G_RET_STS_ERROR ;
583 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_NOT_EXIST');
584 FND_MSG_PUB.Add;
585 FND_MSG_PUB.Count_And_Get(
586 p_count => x_msg_count ,
587 p_data => x_msg_data
588 );
589 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
590 ELSE
591 Disassociate_System_With_SR (p_api_version_number,
592 p_init_msg_list,
593 p_commit,
594 l_system_id,
595 p_service_request_id,
596 x_return_status,
597 x_msg_count,
598 x_msg_data,
599 x_java_msg);
600
601 END IF;
602 CLOSE c1;
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 CLOSE c1;
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608 FND_MESSAGE.SET_NAME('CS','CS_SYSTEM_NAME_UNEXP_ERR');
609 FND_MSG_PUB.Add;
610 FND_MSG_PUB.Count_And_Get
611 (
612 p_count => x_msg_count ,
613 p_data => x_msg_data
614 );
615 x_java_msg := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
616 ROLLBACK TO Disassociate_Name_SR;
617
618 END Disassociate_Name_With_SR;
619
620
621 END CS_System_Link_UTIL;