[Home] [Help]
PACKAGE BODY: APPS.CSM_GROUP_DOWNLOAD_PUB
Source
1 PACKAGE BODY CSM_GROUP_DOWNLOAD_PUB AS
2 /* $Header: csmpgpdb.pls 120.2 2008/02/29 09:16:01 anaraman noship $*/
3
4 PROCEDURE assign_related_group
5 ( p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
7 p_group_id IN NUMBER,
8 p_related_group_id IN NUMBER,
9 p_operation IN VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_error_message OUT NOCOPY VARCHAR2
13 )
14 IS
15
16 l_owner_id asg_user.owner_id%TYPE := NULL;
17 l_rel_owner_id asg_user.owner_id%TYPE := 0;
18 l_rel_user_id asg_user.owner_id%TYPE := NULL;
19 l_rel_grp_type VARCHAR2(1) := 'R';
20 l_return_status VARCHAR2(3000);
21 l_error_message VARCHAR2(3000) := NULL;
22 l_rel_return_status VARCHAR2(3000);
23 l_rel_error_message VARCHAR2(3000) := NULL;
24 l_msg_data VARCHAR2(3000);
25 l_sqlerrmsg VARCHAR2(3000);
26 l_sqlerrno VARCHAR2(20);
27
28 CURSOR csr_grp_owner_id(p_group_id IN NUMBER)
29 IS
30 SELECT au.owner_id
31 FROM asg_user au
32 WHERE au.group_id = p_group_id
33 AND au.user_id = au.owner_id
34 AND au.enabled = 'Y';
35
36 CURSOR csr_rel_grp(p_related_group_id IN NUMBER)
37 IS
38 SELECT au.owner_id
39 FROM asg_user au
40 WHERE au.group_id = p_related_group_id
41 AND au.user_id = au.owner_id
42 AND au.enabled = 'Y';
43
44 BEGIN
45
46 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP Package ', 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP',FND_LOG.LEVEL_EXCEPTION);
47
48 IF FND_API.TO_BOOLEAN (p_init_msg_list)
49
50 THEN
51
52 FND_MSG_PUB.INITIALIZE;
53
54 END IF;
55
56 OPEN csr_grp_owner_id (p_group_id);
57 FETCH csr_grp_owner_id INTO l_owner_id;
58
59 IF (csr_grp_owner_id%NOTFOUND)
60
61 THEN
62
63 FND_MESSAGE.SET_NAME ('CSM', 'CSM_OWNER_NOT_FOUND');
64
65 FND_MSG_PUB.ADD;
66
67 x_error_message := 'NO owner found for the given group - '||p_group_id;
68
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70
71 END IF;
72
73 CLOSE csr_grp_owner_id;
74
75 IF p_operation = 'INSERT'
76
77 THEN
78
79 --calling the package to insert records into CSM_GROUPS table
80
81 csm_group_download_pvt.insert_my_group
82 ( P_USER_ID => l_owner_id,
83 X_RETURN_STATUS => l_return_status,
84 X_ERROR_MESSAGE => l_error_message);
85
86 ELSIF p_operation = 'DELETE'
87
88 THEN
89
90 --calling the package to delete records from CSM_GROUPS table
91
92 csm_group_download_pvt.delete_my_group
93 ( P_USER_ID => l_owner_id,
94 X_RETURN_STATUS => l_return_status,
95 X_ERROR_MESSAGE => l_error_message);
96
97 END IF;
98
99 IF NOT (l_return_status = 'SUCCESS')
100
101 THEN
102
103 x_error_message := l_error_message;
104
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106
107 END IF;
108
109 OPEN csr_rel_grp (p_related_group_id);
110
111 l_rel_user_id := l_owner_id;
112
113 FETCH csr_rel_grp INTO l_rel_owner_id;
114
115 CLOSE csr_rel_grp;
116
117 IF p_operation = 'INSERT'
118
119 THEN
120
121 --calling the package to insert Related groups records into CSM_GROUPS table
122
123 csm_group_download_pvt.insert_group_acc
124 ( P_USER_ID => l_rel_user_id,
125 P_GROUP_ID => p_related_group_id,
126 P_OWNER_ID => l_rel_owner_id,
127 P_GROUP_TYPE => l_rel_grp_type,
128 X_RETURN_STATUS => l_rel_return_status,
129 X_ERROR_MESSAGE => l_rel_error_message);
130
131 ELSIF p_operation = 'DELETE'
132
133 THEN
134
135 --calling the package to insert Related groups records into CSM_GROUPS table
136
137 csm_group_download_pvt.delete_group_acc
138 ( P_USER_ID => l_rel_user_id,
139 P_GROUP_ID => p_related_group_id,
140 X_RETURN_STATUS => l_rel_return_status,
141 X_ERROR_MESSAGE => l_rel_error_message);
142
143 END IF;
144
145 IF NOT (l_return_status = 'SUCCESS')
146
147 THEN
148
149 x_error_message := l_rel_error_message;
150
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152
153 END IF;
154
155 IF p_operation IS NULL
156
157 THEN
158
159 CSM_UTIL_PKG.LOG('Value for p_operation is NULL', 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP',FND_LOG.LEVEL_EXCEPTION);
160
161 END IF;
162
163
164 IF (csr_grp_owner_id%ISOPEN)
165
166 THEN
167
168 CLOSE csr_grp_owner_id;
169
170 END IF;
171
172 IF (csr_rel_grp%ISOPEN)
173
174 THEN
175
176 CLOSE csr_rel_grp;
177
178 END IF;
179
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181 x_msg_count := 0;
182 x_error_message := 'Downloading related group to a group through CSM_GROUP_DOWNLOAD_PVT is done successfully';
183 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP Package ', 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP ',FND_LOG.LEVEL_EXCEPTION);
184
185 EXCEPTION
186
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
188
189 THEN
190
191 FND_MSG_PUB.COUNT_AND_GET
192 ( p_count => x_msg_count,
193 p_data => l_msg_data );
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
195 x_error_message := x_error_message || ' - the error message is :'||l_msg_data;
196 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP',FND_LOG.LEVEL_EXCEPTION);
197
198 WHEN OTHERS
199
200 THEN
201
202 FND_MESSAGE.SET_NAME ('CSM', 'CSM_GROUP_DWNLD_UNKNOWN_ERROR');
203 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
204 FND_MSG_PUB.ADD;
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 l_sqlerrno := TO_CHAR(SQLCODE);
207 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
208 FND_MSG_PUB.COUNT_AND_GET
209 ( p_count => x_msg_count,
210 p_data => l_msg_data );
211 x_error_message := 'Exception in CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP Procedure :'||'while processing the group -'
212 ||p_group_id|| 'and related group:'||p_related_group_id||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
213 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP',FND_LOG.LEVEL_EXCEPTION);
214
215 END assign_related_group;
216
217 PROCEDURE assign_mutiple_related_groups
218 ( p_api_version_number IN NUMBER,
219 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
220 p_group_id IN NUMBER,
221 p_related_group_lst IN l_group_id_tbl_type,
222 p_operation IN VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_error_message OUT NOCOPY VARCHAR2
226 )
227 IS
228
229 l_rel_group_id_tbl l_group_id_tbl_type;
230 l_owner_id asg_user.owner_id%TYPE := NULL;
231 l_rel_user_id asg_user.user_id%TYPE := NULL;
232 l_rel_owner_id asg_user.owner_id%TYPE := 0;
233 l_rel_group_id asg_user.group_id%TYPE := NULL;
234 l_rel_grp_type VARCHAR2(1) := 'R';
235 l_return_status VARCHAR2(3000);
236 l_error_message VARCHAR2(3000) := NULL;
237 l_rel_return_status VARCHAR2(3000);
238 l_rel_error_message VARCHAR2(3000) := NULL;
239 l_msg_data VARCHAR2(3000);
240 l_sqlerrmsg VARCHAR2(3000);
241 l_sqlerrno VARCHAR2(20);
242
243 CURSOR csr_grp_owner_id(p_group_id IN NUMBER)
244 IS
245 SELECT au.owner_id
246 FROM asg_user au
247 WHERE au.group_id = p_group_id
248 AND au.user_id = au.owner_id
249 AND au.enabled = 'Y';
250
251 CURSOR csr_rel_grp(p_group_id IN NUMBER)
252 IS
253 SELECT au.owner_id
254 FROM asg_user au
255 WHERE au.group_id = p_group_id
256 AND au.user_id = au.owner_id
257 AND au.enabled = 'Y';
258
259 BEGIN
260
261 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS Package ', 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_RELATED_GROUP',FND_LOG.LEVEL_EXCEPTION);
262
263 IF FND_API.TO_BOOLEAN (p_init_msg_list)
264
265 THEN
266
267 FND_MSG_PUB.INITIALIZE;
268
269 END IF;
270
271 OPEN csr_grp_owner_id (p_group_id);
272 FETCH csr_grp_owner_id INTO l_owner_id;
273
274 IF (csr_grp_owner_id%NOTFOUND)
275
276 THEN
277
278 FND_MESSAGE.SET_NAME ('CSM', 'CSM_OWNER_NOT_FOUND');
279
280 FND_MSG_PUB.ADD;
281
282 x_error_message := 'NO owner found for the given group - '||p_group_id;
283
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285
286 END IF;
287
288 CLOSE csr_grp_owner_id;
289
290 IF p_operation = 'INSERT'
291
292 THEN
293
294 --calling the package to insert records into CSM_GROUPS table
295
296 csm_group_download_pvt.insert_my_group
297 ( P_USER_ID => l_owner_id,
298 X_RETURN_STATUS => l_return_status,
299 X_ERROR_MESSAGE => l_error_message);
300
301 ELSIF p_operation = 'DELETE'
302
303 THEN
304
305 --calling the package to delete records from CSM_GROUPS table
306
307 csm_group_download_pvt.delete_my_group
308 ( P_USER_ID => l_owner_id,
309 X_RETURN_STATUS => l_return_status,
310 X_ERROR_MESSAGE => l_error_message);
311
312 END IF;
313
314
315 IF NOT (l_return_status = 'SUCCESS')
316
317 THEN
318
319 x_error_message := l_error_message;
320
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322
323 END IF;
324
325 IF l_rel_group_id_tbl.COUNT > 0 THEN
326
327 l_rel_group_id_tbl.DELETE;
328
329 END IF;
330
331 l_rel_group_id_tbl := p_related_group_lst;
332
333 l_rel_user_id := l_owner_id;
334
335 FOR i IN l_rel_group_id_tbl.FIRST..l_rel_group_id_tbl.LAST LOOP
336
337 l_rel_group_id := l_rel_group_id_tbl(i);
338
339 l_rel_owner_id := NULL;
340
341 OPEN csr_rel_grp (l_rel_group_id);
342 FETCH csr_rel_grp INTO l_rel_owner_id;
343
344 CLOSE csr_rel_grp;
345
346 IF p_operation = 'INSERT'
347
348 THEN
349
353 ( P_USER_ID => l_rel_user_id,
350 --calling the package to insert Related groups records into CSM_GROUPS table
351
352 csm_group_download_pvt.insert_group_acc
354 P_GROUP_ID => l_rel_group_id,
355 P_OWNER_ID => l_rel_owner_id,
356 P_GROUP_TYPE => l_rel_grp_type,
357 X_RETURN_STATUS => l_rel_return_status,
358 X_ERROR_MESSAGE => l_rel_error_message);
359
360 ELSIF p_operation = 'DELETE'
361
362 THEN
363
364 --calling the package to insert Related groups records into CSM_GROUPS table
365
366 csm_group_download_pvt.delete_group_acc
367 ( P_USER_ID => l_rel_user_id,
368 P_GROUP_ID => l_rel_group_id,
369 X_RETURN_STATUS => l_rel_return_status,
370 X_ERROR_MESSAGE => l_rel_error_message);
371
372 END IF;
373
374 IF NOT (l_rel_return_status = 'SUCCESS')
375
376 THEN
377
378 x_error_message := l_rel_error_message;
379
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381
382 END IF;
383
384 END LOOP;
385
386 COMMIT;
387
388 IF p_operation IS NULL
389
390 THEN
391
392 CSM_UTIL_PKG.LOG('Value for p_operation is NULL', 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
393
394 END IF;
395
396 IF (csr_grp_owner_id%ISOPEN)
397
398 THEN
399
400 CLOSE csr_grp_owner_id;
401
402 END IF;
403
404 IF (csr_rel_grp%ISOPEN)
405
406 THEN
407
408 CLOSE csr_rel_grp;
409
410 END IF;
411
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413 x_msg_count := 0;
414 x_error_message := 'Downloading related group to a group through CSM_GROUP_DOWNLOAD_PVT is done successfully';
415 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS Package ', 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS ',FND_LOG.LEVEL_EXCEPTION);
416
417 EXCEPTION
418
419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
420
421 THEN
422
423 FND_MSG_PUB.COUNT_AND_GET
424 ( p_count => x_msg_count,
425 p_data => l_msg_data );
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 ROLLBACK;
428 x_error_message := x_error_message || ' - the error message is :'||l_msg_data;
429 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
430
431 WHEN OTHERS
432
433 THEN
434
435 FND_MESSAGE.SET_NAME ('CSM', 'CSM_GROUP_DWNLD_UNKNOWN_ERROR');
436 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
437 FND_MSG_PUB.ADD;
438 ROLLBACK;
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 l_sqlerrno := TO_CHAR(SQLCODE);
441 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
442 FND_MSG_PUB.COUNT_AND_GET
443 ( p_count => x_msg_count,
444 p_data => l_msg_data );
445 x_error_message := 'Exception in CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS Procedure :'||'while processing the group -'
446 ||p_group_id|| 'and related group:'||l_rel_group_id|| ':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
447 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PUB.ASSIGN_MUTIPLE_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
448
449 END assign_mutiple_related_groups;
450
451 PROCEDURE get_related_groups
452 ( p_api_version_number IN NUMBER,
453 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
454 p_group_id IN NUMBER,
455 p_related_group_lst OUT NOCOPY l_group_id_tbl_type,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_return_status OUT NOCOPY VARCHAR2,
458 x_error_message OUT NOCOPY VARCHAR2
459 )
460
461 IS
462
463 l_rel_group_id_tbl l_group_id_tbl_type;
464 l_owner_id asg_user.owner_id%TYPE := NULL;
465 l_msg_data VARCHAR2(3000);
466 l_sqlerrmsg VARCHAR2(3000);
467 l_sqlerrno VARCHAR2(20);
468 l_error_message VARCHAR2(3000);
469
470 CURSOR csr_get_owner_id (p_group_id IN NUMBER)
471 IS
472 SELECT au.owner_id
473 FROM asg_user au
474 WHERE au.group_id = p_group_id
475 AND au.user_id = au.owner_id
476 AND au.enabled = 'Y';
477
478 CURSOR csr_get_rel_grp_id (p_owner_id IN NUMBER)
479 IS
480 SELECT acc.group_id
481 FROM csm_groups acc
482 WHERE acc.user_id = p_owner_id
483 AND acc.group_owner_id <> acc.user_id;
484
485 BEGIN
486
487 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS PACKAGE ', 'CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
488
489 IF FND_API.TO_BOOLEAN (p_init_msg_list)
490
491 THEN
492
493 FND_MSG_PUB.INITIALIZE;
494
495 END IF;
496
497 OPEN csr_get_owner_id (p_group_id);
498 FETCH csr_get_owner_id INTO l_owner_id;
499
500 IF (csr_get_owner_id%NOTFOUND)
501
502 THEN
503
504 FND_MESSAGE.SET_NAME ('CSM', 'CSM_OWNER_NOT_FOUND');
505
506 FND_MSG_PUB.ADD;
507
508 l_error_message := 'NO Data found for the given group - '||p_group_id;
509
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511
512 END IF;
513
517
514 CLOSE csr_get_owner_id;
515
516 IF l_rel_group_id_tbl.COUNT > 0
518 THEN
519
520 l_rel_group_id_tbl.DELETE;
521
522 END IF;
523
524 OPEN csr_get_rel_grp_id (l_owner_id);
525 FETCH csr_get_rel_grp_id BULK COLLECT INTO l_rel_group_id_tbl;
526
527 IF l_rel_group_id_tbl.COUNT = 0
528
529 THEN
530
531 FND_MESSAGE.SET_NAME ('CSM', 'CSM_REL_GROUPS_NOT_FOUND');
532
533 FND_MSG_PUB.ADD;
534
535 x_error_message := 'NO related groups for the given group id - '||p_group_id;
536
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
538
539 END IF;
540
541 CLOSE csr_get_rel_grp_id;
542
543 p_related_group_lst := l_rel_group_id_tbl;
544
545 IF (csr_get_owner_id%ISOPEN)
546
547 THEN
548
549 CLOSE csr_get_owner_id;
550
551 END IF;
552
553 IF (csr_get_rel_grp_id%ISOPEN)
554
555 THEN
556
557 CLOSE csr_get_rel_grp_id;
558
559 END IF;
560
561 x_return_status := FND_API.G_RET_STS_SUCCESS;
562 x_msg_count := 0;
563 x_error_message := 'The related groups for the group id are fetched successfully';
564 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS Package ', 'CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
565
566 EXCEPTION
567
568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
569
570 THEN
571
572 FND_MSG_PUB.COUNT_AND_GET
573 ( p_encoded => 'T',
574 p_count => x_msg_count,
575 p_data => l_msg_data );
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
578 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
579
580 WHEN OTHERS
581
582 THEN
583
584 FND_MESSAGE.SET_NAME ('CSM', 'CSM_GROUP_UNKNOWN_ERROR');
585 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
586 FND_MSG_PUB.ADD;
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 l_sqlerrno := TO_CHAR(SQLCODE);
589 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
590 FND_MSG_PUB.COUNT_AND_GET
591 ( p_count => x_msg_count,
592 p_data => l_msg_data );
593 x_error_message := 'Exception in CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS Procedure :'||'while fetching the related groups for the group -'
594 ||p_group_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
595 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PUB.GET_RELATED_GROUPS',FND_LOG.LEVEL_EXCEPTION);
596
597 END get_related_groups;
598
599 END CSM_GROUP_DOWNLOAD_PUB;