[Home] [Help]
PACKAGE BODY: APPS.CSM_PARTY_DOWNLOAD_PUB
Source
1 PACKAGE BODY CSM_PARTY_DOWNLOAD_PUB AS
2 /* $Header: csmpptdb.pls 120.7 2008/02/29 09:06:26 anaraman noship $*/
3
4 PROCEDURE assign_cust_to_user
5 ( p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
7 p_user_id IN NUMBER,
8 p_party_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_party_site_id csm_party_sites_acc.party_site_id%TYPE := NULL;
18 l_return_status VARCHAR2(3000);
19 l_error_message VARCHAR2(3000) := NULL;
20 l_msg_data VARCHAR2(3000);
21 l_sqlerrmsg VARCHAR2(3000);
22 l_sqlerrno VARCHAR2(20);
23
24 CURSOR csr_owner_id(p_user_id IN NUMBER)
25 IS
26 SELECT au.owner_id
27 FROM asg_user au
28 WHERE au.user_id = p_user_id
29 AND au.enabled = 'Y';
30
31 BEGIN
32
33 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER',FND_LOG.LEVEL_EXCEPTION);
34
35 IF FND_API.TO_BOOLEAN (p_init_msg_list)
36
37 THEN
38
39 FND_MSG_PUB.INITIALIZE;
40
41 END IF;
42
43 OPEN csr_owner_id (p_user_id);
44 FETCH csr_owner_id INTO l_owner_id;
45
46 IF (csr_owner_id%NOTFOUND)
47
48 THEN
49
50 FND_MESSAGE.SET_NAME ('CSM', 'CSM_OWNER_NOT_FOUND');
51
52 FND_MSG_PUB.ADD;
53
54 l_error_message := 'NO owner found for the given user - '||p_user_id;
55
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57
58 END IF;
59
60 CLOSE csr_owner_id;
61
62 IF p_operation = 'INSERT'
63
64 THEN
65
66 --calling the package to insert records into CSM_PARTY_ASSIGNMENT table
67
68 csm_party_assignment_pkg.insert_party_assg
69 ( P_USER_ID => p_user_id,
70 P_PARTY_ID => p_party_id,
71 P_OWNER_ID => l_owner_id,
72 P_PARTY_SITE_ID => l_party_site_id,
73 X_RETURN_STATUS => l_return_status,
74 X_ERROR_MESSAGE => l_error_message);
75
76 ELSIF p_operation = 'DELETE'
77
78 THEN
79
80 --calling the package to delete records from CSM_PARTY_ASSIGNMENT table
81
82 csm_party_assignment_pkg.delete_party_assg
83 ( P_USER_ID => p_user_id,
84 P_PARTY_ID => p_party_id,
85 P_OWNER_ID => l_owner_id,
86 P_PARTY_SITE_ID => l_party_site_id,
87 X_RETURN_STATUS => l_return_status,
88 X_ERROR_MESSAGE => l_error_message);
89
90 END IF;
91
92 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS)
93
94 THEN
95
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97
98 END IF;
99
100 IF p_operation IS NULL
101
102 THEN
103
104 CSM_UTIL_PKG.LOG('Value for p_operation is NULL', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER',FND_LOG.LEVEL_EXCEPTION);
105
106 END IF;
107
108 IF (csr_owner_id%ISOPEN)
109
110 THEN
111
112 CLOSE csr_owner_id;
113
114 END IF;
115
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117 x_msg_count := 0;
118 x_error_message := 'Assigning customer to single user through CSM_PARTY_ASSIGNMENT_PKG is done successfully';
119 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER ',FND_LOG.LEVEL_EXCEPTION);
120
121 EXCEPTION
122
123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
124
125 THEN
126
127 FND_MSG_PUB.COUNT_AND_GET
128 ( p_count => x_msg_count,
129 p_data => l_msg_data );
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
132 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER',FND_LOG.LEVEL_EXCEPTION);
133
134 WHEN OTHERS
135
136 THEN
137
138 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_UNKNOWN_ERROR');
139 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
140 FND_MSG_PUB.ADD;
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 l_sqlerrno := TO_CHAR(SQLCODE);
143 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
144 FND_MSG_PUB.COUNT_AND_GET
145 ( p_count => x_msg_count,
146 p_data => l_msg_data );
147 x_error_message := 'Exception in CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER Procedure :'||'while processing the party -'
148 ||p_party_id|| 'for the user -'||p_user_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
149 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_TO_USER',FND_LOG.LEVEL_EXCEPTION);
150
151 END assign_cust_to_user;
152
153 PROCEDURE assign_mul_cust_to_users
154 ( p_api_version_number IN NUMBER,
155 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
156 p_user_id_lst IN l_user_id_tbl_type,
157 p_party_id_lst IN l_party_id_tbl_type,
158 p_operation IN VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_error_message OUT NOCOPY VARCHAR2
162 )
163 IS
164
165 l_user_id_tbl l_user_id_tbl_type;
166 l_party_id_tbl l_party_id_tbl_type;
167 l_user_id asg_user.user_id%TYPE;
168 l_party_id csm_parties_acc.party_id%TYPE;
169 l_owner_id asg_user.owner_id%TYPE := NULL;
170 l_party_site_id csm_party_sites_acc.party_site_id%TYPE := NULL;
171 l_return_status VARCHAR2(3000);
172 l_error_message VARCHAR2(3000) := NULL;
173 l_msg_data VARCHAR2(3000);
174 l_sqlerrmsg VARCHAR2(3000);
175 l_sqlerrno VARCHAR2(20);
176
177 CURSOR csr_owner_id (p_user_id IN NUMBER)
178 IS
179 SELECT au.owner_id
180 FROM asg_user au
181 WHERE au.user_id = p_user_id
182 AND au.enabled = 'Y';
183
184 BEGIN
185
186 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS',FND_LOG.LEVEL_EXCEPTION);
187
188 IF FND_API.TO_BOOLEAN (p_init_msg_list)
189
190 THEN
191
192 FND_MSG_PUB.INITIALIZE;
193
194 END IF;
195
196 IF l_party_id_tbl.COUNT > 0
197
198 THEN
199
200 l_party_id_tbl.DELETE;
201
202 END IF;
203
204 IF l_user_id_tbl.COUNT > 0
205
206 THEN
207
208 l_user_id_tbl.DELETE;
209
210 END IF;
211
212 l_party_id_tbl := p_party_id_lst;
213
214 l_user_id_tbl := p_user_id_lst;
215
216 FOR i IN l_party_id_tbl.FIRST..l_party_id_tbl.LAST LOOP
217
218 l_party_id := l_party_id_tbl(i);
219
220 l_user_id := l_user_id_tbl(i);
221
222 l_owner_id := NULL;
223
224 OPEN csr_owner_id (l_user_id);
225 FETCH csr_owner_id INTO l_owner_id;
226
227 IF (csr_owner_id%NOTFOUND)
228
229 THEN
230
231 FND_MESSAGE.SET_NAME ('CSM', 'CSM_OWNER_NOT_FOUND');
232
233 FND_MSG_PUB.ADD;
234
235 l_error_message := 'NO owner found for the given user - '||l_user_id;
236
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238
239 END IF;
240
241 CLOSE csr_owner_id;
242
243 IF p_operation = 'INSERT'
244
245 THEN
246
247 --calling the package to insert records into CSM_PARTY_ASSIGNMENT table
248
249 csm_party_assignment_pkg.insert_party_assg
250 ( P_USER_ID => l_user_id,
251 P_PARTY_ID => l_party_id,
252 P_OWNER_ID => l_owner_id,
253 P_PARTY_SITE_ID => l_party_site_id,
254 X_RETURN_STATUS => l_return_status,
255 X_ERROR_MESSAGE => l_error_message);
256
257 ELSIF p_operation = 'DELETE'
258
259 THEN
260
261 --calling the package to delete records from CSM_PARTY_ASSIGNMENT table
262
263 csm_party_assignment_pkg.delete_party_assg
264 ( P_USER_ID => l_user_id,
265 P_PARTY_ID => l_party_id,
266 P_OWNER_ID => l_owner_id,
267 P_PARTY_SITE_ID => l_party_site_id,
268 X_RETURN_STATUS => l_return_status,
269 X_ERROR_MESSAGE => l_error_message);
270
271 END IF;
272
273 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS)
274
275 THEN
276
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278
279 END IF;
280
281 END LOOP;
282
283 COMMIT;
284
285 IF p_operation IS NULL
286
287 THEN
288
289 CSM_UTIL_PKG.LOG('Value for p_operation is NULL', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS',FND_LOG.LEVEL_EXCEPTION);
290
291 END IF;
292
293 IF (csr_owner_id%ISOPEN)
294
295 THEN
296
297 CLOSE csr_owner_id;
298
299 END IF;
300
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302 x_msg_count := 0;
303 x_error_message := 'Assigning multiple customers to multiple users through CSM_PARTY_ASSIGNMENT_PKG is done successfully';
304 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS ',FND_LOG.LEVEL_EXCEPTION);
305
306 EXCEPTION
307
308 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
309
310 THEN
311
312 FND_MSG_PUB.COUNT_AND_GET
313 ( p_count => x_msg_count,
314 p_data => l_msg_data );
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
317 ROLLBACK;
318 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS',FND_LOG.LEVEL_EXCEPTION);
319
320 WHEN OTHERS
321
322 THEN
323
324 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_UNKNOWN_ERROR');
325 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
326 FND_MSG_PUB.ADD;
327 ROLLBACK;
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 l_sqlerrno := TO_CHAR(SQLCODE);
330 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
331 FND_MSG_PUB.COUNT_AND_GET
332 ( p_count => x_msg_count,
333 p_data => l_msg_data );
334 x_error_message := 'Exception in CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS Procedure :'||'while processing the party -'
335 ||l_party_id|| 'for the user -'||l_user_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
336 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_TO_USERS',FND_LOG.LEVEL_EXCEPTION);
337
338 END assign_mul_cust_to_users;
339
340 PROCEDURE assign_cust_loc_to_user
341 ( p_api_version_number IN NUMBER,
342 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
343 p_user_id IN NUMBER,
344 p_party_id IN NUMBER,
345 p_location_id IN NUMBER,
346 p_operation IN VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_error_message OUT NOCOPY VARCHAR2
350 )
351 IS
352
353 l_owner_id asg_user.owner_id%TYPE := NULL;
354 l_return_status VARCHAR2(3000);
355 l_error_message VARCHAR2(3000);
356 l_msg_data VARCHAR2(3000);
357 l_sqlerrmsg VARCHAR2(3000) := NULL;
358 l_sqlerrno VARCHAR2(20);
359
360 CURSOR csr_owner_id(p_user_id IN NUMBER)
361 IS
362 SELECT au.owner_id
363 FROM asg_user au
364 WHERE au.user_id = p_user_id
365 AND au.enabled = 'Y';
366
367 BEGIN
368
369 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
370
371 IF FND_API.TO_BOOLEAN (p_init_msg_list)
372
373 THEN
374
375 FND_MSG_PUB.INITIALIZE;
376
377 END IF;
378
379 OPEN csr_owner_id (p_user_id);
380 FETCH csr_owner_id INTO l_owner_id;
381
382 IF (csr_owner_id%NOTFOUND)
383
384 THEN
385
386 FND_MESSAGE.SET_NAME ('CSM', 'CSM_OWNER_NOT_FOUND');
387
388 FND_MSG_PUB.ADD;
389
390 l_error_message := 'NO owner found for the given user - '||p_user_id;
391
392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
393
394 END IF;
395
396 CLOSE csr_owner_id;
397
398 IF p_operation = 'INSERT'
399
400 THEN
401
402 --calling the package to insert records into CSM_PARTY_ASSIGNMENT table
403
404 csm_party_assignment_pkg.insert_party_assg
405 ( P_USER_ID => p_user_id,
406 P_PARTY_ID => p_party_id,
407 P_OWNER_ID => l_owner_id,
408 P_PARTY_SITE_ID => p_location_id,
409 X_RETURN_STATUS => l_return_status,
410 X_ERROR_MESSAGE => l_error_message);
411
412 ELSIF p_operation = 'DELETE'
413
414 THEN
415
416 --calling the package to delete records from CSM_PARTY_ASSIGNMENT table
417
418 csm_party_assignment_pkg.delete_party_assg
419 ( P_USER_ID => p_user_id,
420 P_PARTY_ID => p_party_id,
421 P_OWNER_ID => l_owner_id,
422 P_PARTY_SITE_ID => p_location_id,
423 X_RETURN_STATUS => l_return_status,
424 X_ERROR_MESSAGE => l_error_message);
425
426 END IF;
427
428 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS)
429
430 THEN
431
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433
434 END IF;
435
436 IF p_operation IS NULL
437
438 THEN
439
440 CSM_UTIL_PKG.LOG('Value for p_operation is NULL', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
441
442 END IF;
443
444 IF (csr_owner_id%ISOPEN)
445
446 THEN
447
448 CLOSE csr_owner_id;
449
450 END IF;
451
452 x_return_status := FND_API.G_RET_STS_SUCCESS;
453 x_msg_count := 0;
454 x_error_message := 'Assigning customer with a location to single user through CSM_PARTY_ASSIGNMENT_PKG is done successfully';
455 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
456
457 EXCEPTION
458
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
460
461 THEN
462
463 FND_MSG_PUB.COUNT_AND_GET
464 ( p_count => x_msg_count,
465 p_data => l_msg_data );
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
468 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
469
470 WHEN OTHERS
471
472 THEN
473
474 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_UNKNOWN_ERROR');
475 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
476 FND_MSG_PUB.ADD;
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 l_sqlerrno := TO_CHAR(SQLCODE);
479 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
480 FND_MSG_PUB.COUNT_AND_GET
481 ( p_count => x_msg_count,
482 p_data => l_msg_data );
483 x_error_message := 'Exception in CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER Procedure :'||'while processing the party -'
484 ||p_party_id|| 'for the user -'||p_user_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
485 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
486
487 END assign_cust_loc_to_user;
488
489 PROCEDURE assign_mul_cust_loc_to_users
490 ( p_api_version_number IN NUMBER,
491 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
492 p_user_id_lst IN l_user_id_tbl_type,
493 p_party_id IN NUMBER,
494 p_location_id_lst IN l_party_id_tbl_type,
495 p_operation IN VARCHAR2,
496 x_msg_count OUT NOCOPY NUMBER,
497 x_return_status OUT NOCOPY VARCHAR2,
498 x_error_message OUT NOCOPY VARCHAR2
499 )
500
501 IS
502
503 l_user_id_tbl l_user_id_tbl_type;
504 l_party_site_id_tbl l_party_id_tbl_type;
505 l_user_id asg_user.user_id%TYPE;
506 l_party_id csm_parties_acc.party_id%TYPE;
507 l_location_id csm_party_sites_acc.party_site_id%TYPE;
508 l_owner_id asg_user.owner_id%TYPE := NULL;
509 l_party_site_id csm_party_sites_acc.party_site_id%TYPE;
510 l_return_status VARCHAR2(3000);
511 l_error_message VARCHAR2(3000);
512 l_msg_data VARCHAR2(3000);
513 l_sqlerrmsg VARCHAR2(3000) := NULL;
514 l_sqlerrno VARCHAR2(20);
515
516 CURSOR csr_owner_id (p_user_id IN NUMBER)
517 IS
518 SELECT au.owner_id
519 FROM asg_user au
520 WHERE au.user_id = p_user_id
521 AND au.enabled = 'Y';
522
523 BEGIN
524
525 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USERS Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USERS',FND_LOG.LEVEL_EXCEPTION);
526
527 IF FND_API.TO_BOOLEAN (p_init_msg_list)
528
529 THEN
530
531 FND_MSG_PUB.INITIALIZE;
532
533 END IF;
534
535 IF l_party_site_id_tbl.COUNT > 0
536
537 THEN
538
539 l_party_site_id_tbl.DELETE;
540
541 END IF;
542
543 IF l_user_id_tbl.COUNT > 0
544
545 THEN
546
547 l_user_id_tbl.DELETE;
548
549 END IF;
550
551 l_party_id := p_party_id;
552
553 l_user_id_tbl := p_user_id_lst;
554
555 l_party_site_id_tbl := p_location_id_lst;
556
557 FOR i IN l_party_site_id_tbl.FIRST..l_party_site_id_tbl.LAST LOOP
558
559 l_user_id := l_user_id_tbl(i);
560
561 l_party_site_id := l_party_site_id_tbl(i);
562
563 l_owner_id := NULL;
564
565 OPEN csr_owner_id (l_user_id);
566 FETCH csr_owner_id INTO l_owner_id;
567
568 IF (csr_owner_id%NOTFOUND)
569
570 THEN
571
572 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_OWNER_NOT_FOUND');
573
574 FND_MSG_PUB.ADD;
575
576 l_error_message := 'NO owner found for the given user - '||l_user_id;
577
578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
579
580 END IF;
581
582 CLOSE csr_owner_id;
583
584 IF p_operation = 'INSERT'
585
586 THEN
587
588 --calling the package to insert records into CSM_PARTY_ASSIGNMENT table
589
590 csm_party_assignment_pkg.insert_party_assg
591 ( P_USER_ID => l_user_id,
592 P_PARTY_ID => l_party_id,
593 P_OWNER_ID => l_owner_id,
594 P_PARTY_SITE_ID => l_party_site_id,
595 X_RETURN_STATUS => l_return_status,
596 X_ERROR_MESSAGE => l_error_message);
597
598 ELSIF p_operation = 'DELETE'
599
600 THEN
601
602 --calling the package to delete records from CSM_PARTY_ASSIGNMENT table
603
604 csm_party_assignment_pkg.delete_party_assg
605 ( P_USER_ID => l_user_id,
606 P_PARTY_ID => l_party_id,
607 P_OWNER_ID => l_owner_id,
608 P_PARTY_SITE_ID => l_party_site_id,
609 X_RETURN_STATUS => l_return_status,
610 X_ERROR_MESSAGE => l_error_message);
611
612 END IF;
613
614 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS)
615
616 THEN
617
618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
619
620 END IF;
621
622 END LOOP;
623
624 COMMIT;
625
626 IF p_operation IS NULL
627
628 THEN
629
630 CSM_UTIL_PKG.LOG('Value for p_operation is NULL', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
631
632 END IF;
633
634 IF (csr_owner_id%ISOPEN)
635
636 THEN
637
638 CLOSE csr_owner_id;
639
640 END IF;
641
642 x_return_status := FND_API.G_RET_STS_SUCCESS;
643 x_msg_count := 0;
644 x_error_message := 'Assigning customer with multiple locations to multiple users through CSM_PARTY_ASSIGNMENT_PKG is done successfully';
645 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
646
647 EXCEPTION
648
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
650
651 THEN
652
653 FND_MSG_PUB.COUNT_AND_GET
654 ( p_count => x_msg_count,
655 p_data => l_msg_data );
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
657 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
658 ROLLBACK;
659 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
660
661 WHEN OTHERS
662
663 THEN
664
665 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_UNKNOWN_ERROR');
666 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
667 FND_MSG_PUB.ADD;
668 ROLLBACK;
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 l_sqlerrno := TO_CHAR(SQLCODE);
671 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
672 FND_MSG_PUB.COUNT_AND_GET
673 ( p_count => x_msg_count,
674 p_data => l_msg_data );
675 x_error_message := 'Exception in CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USER Procedure :'||'while processing the party -'
676 ||l_party_id|| 'for the user -'||l_user_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
677 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.ASSIGN_MUL_CUST_LOC_TO_USER',FND_LOG.LEVEL_EXCEPTION);
678
679 END assign_mul_cust_loc_to_users;
680
681 PROCEDURE get_parties_for_user
682 ( p_api_version_number IN NUMBER,
683 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
684 p_user_id IN NUMBER,
685 p_party_id_lst OUT NOCOPY l_party_id_tbl_type,
686 p_operation IN VARCHAR2,
687 x_msg_count OUT NOCOPY NUMBER,
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_error_message OUT NOCOPY VARCHAR2
690 )
691
692 IS
693
694 l_party_id_tbl l_party_id_tbl_type;
695 l_user_id asg_user.user_id%TYPE;
696 l_msg_data VARCHAR2(3000);
697 l_sqlerrmsg VARCHAR2(3000);
698 l_sqlerrno VARCHAR2(20);
699 l_error_message VARCHAR2(3000);
700
701 CURSOR csr_get_party_id (p_user_id IN NUMBER)
702 IS
703 SELECT cpa.party_id
704 FROM csm_party_assignment cpa
705 WHERE cpa.user_id = p_user_id
706 AND cpa.party_site_id in (-1,-2)
707 AND cpa.deleted_flag = 'N';
708
709 BEGIN
710
711 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
712
713 IF FND_API.TO_BOOLEAN (p_init_msg_list)
714
715 THEN
716
717 FND_MSG_PUB.INITIALIZE;
718
719 END IF;
720
721 OPEN csr_get_party_id (p_user_id);
722
723 IF l_party_id_tbl.COUNT > 0
724
725 THEN
726
727 l_party_id_tbl.DELETE;
728
729 END IF;
730
731 FETCH csr_get_party_id BULK COLLECT INTO l_party_id_tbl;
732
733 IF l_party_id_tbl.COUNT = 0
734
735 THEN
736
737 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_FOR_USER_NOT_FOUND');
738
739 FND_MSG_PUB.ADD;
740
741 l_error_message := 'NO Data found for the given user - '||p_user_id;
742
743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
744
745 END IF;
746
747 CLOSE csr_get_party_id;
748
749 IF (csr_get_party_id%ISOPEN)
750
751 THEN
752
753 CLOSE csr_get_party_id;
754
755 END IF;
756
757 p_party_id_lst := l_party_id_tbl;
758
759 x_return_status := FND_API.G_RET_STS_SUCCESS;
760 x_msg_count := 0;
761 x_error_message := 'The party records for the user are fetched successfully';
762 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
763
764 EXCEPTION
765
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
767
768 THEN
769
770 FND_MSG_PUB.COUNT_AND_GET
771 ( p_encoded => 'T',
772 p_count => x_msg_count,
773 p_data => l_msg_data );
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
776 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
777
778 WHEN OTHERS
779
780 THEN
781
782 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_UNKNOWN_ERROR');
783 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
784 FND_MSG_PUB.ADD;
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786 l_sqlerrno := TO_CHAR(SQLCODE);
787 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
788 FND_MSG_PUB.COUNT_AND_GET
789 ( p_count => x_msg_count,
790 p_data => l_msg_data );
791 x_error_message := 'Exception in CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER Procedure :'||'while fetching the parties for the user -'
792 ||p_user_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
793 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTIES_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
794
795 END get_parties_for_user;
796
797 PROCEDURE get_party_locations_for_user
798 ( p_api_version_number IN NUMBER,
799 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
800 p_user_id IN NUMBER,
801 p_party_id IN NUMBER,
802 p_location_id OUT NOCOPY l_party_id_tbl_type,
803 p_operation IN VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_return_status OUT NOCOPY VARCHAR2,
806 x_error_message OUT NOCOPY VARCHAR2
807 )
808
809 IS
810
811 l_party_site_id_tbl l_party_id_tbl_type;
812 l_user_id asg_user.user_id%TYPE;
813 l_party_id csm_parties_acc.party_id%TYPE;
814 l_msg_data VARCHAR2(3000);
815 l_sqlerrmsg VARCHAR2(3000);
816 l_sqlerrno VARCHAR2(20);
817 l_error_message VARCHAR2(3000);
818
819 CURSOR csr_get_party_site_id (p_user_id IN NUMBER, p_party_id IN NUMBER)
820 IS
821 SELECT cpa.party_site_id
822 FROM csm_party_assignment cpa
823 WHERE cpa.user_id = p_user_id
824 AND cpa.party_id = p_party_id
825 AND cpa.party_site_id not in (-1,-2)
826 AND cpa.deleted_flag = 'N';
827
828 BEGIN
829
830 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
831
832 IF FND_API.TO_BOOLEAN (p_init_msg_list)
833
834 THEN
835
836 FND_MSG_PUB.INITIALIZE;
837
838 END IF;
839
840 OPEN csr_get_party_site_id (p_user_id, p_party_id);
841
842 IF l_party_site_id_tbl.COUNT > 0
843
844 THEN
845
846 l_party_site_id_tbl.DELETE;
847
848 END IF;
849
850 FETCH csr_get_party_site_id BULK COLLECT INTO l_party_site_id_tbl;
851
852 IF l_party_site_id_tbl.COUNT = 0
853
854 THEN
855
856 FND_MESSAGE.SET_NAME ('CSM', 'CSM_SITE_FOR_USER_NOT_FOUND');
857
858 FND_MSG_PUB.ADD;
859
860 l_error_message := 'NO Data found for the given user - '||p_user_id;
861
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863
864 END IF;
865
866 CLOSE csr_get_party_site_id ;
867
868 p_location_id := l_party_site_id_tbl;
869
870 IF (csr_get_party_site_id%ISOPEN)
871
872 THEN
873
874 CLOSE csr_get_party_site_id;
875
876 END IF;
877
878 x_return_status := FND_API.G_RET_STS_SUCCESS;
879 x_msg_count := 0;
880 x_error_message := 'The party locations records for the user are fetched successfully';
881 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER Package ', 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
882
883 EXCEPTION
884
885 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
886
887 THEN
888
889 FND_MSG_PUB.COUNT_AND_GET
890 ( p_count => x_msg_count,
891 p_data => l_msg_data );
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 x_error_message := l_error_message || ' - the error message is :'||l_msg_data;
894 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
895
896 WHEN OTHERS
897
898 THEN
899
900 FND_MESSAGE.SET_NAME ('CSM', 'CSM_PARTY_ASSIGN_UNKNOWN_ERROR');
901 FND_MESSAGE.SET_TOKEN ('P_TEXT', SQLCODE || SQLERRM);
902 FND_MSG_PUB.ADD;
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 l_sqlerrno := TO_CHAR(SQLCODE);
905 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
906 FND_MSG_PUB.COUNT_AND_GET
907 ( p_count => x_msg_count,
908 p_data => l_msg_data );
909 x_error_message := 'Exception in CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER Procedure :'||'while fetching the parties and party sites for the user -'
910 ||p_user_id ||':' || l_sqlerrno || ':' || l_sqlerrmsg ||'the error message is :' || l_msg_data;
911 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_DOWNLOAD_PUB.GET_PARTY_LOCATIONS_FOR_USER',FND_LOG.LEVEL_EXCEPTION);
912
913 END get_party_locations_for_user;
914
915 END CSM_PARTY_DOWNLOAD_PUB;