[Home] [Help]
PACKAGE BODY: APPS.CN_ROLLUP_PVT
Source
1 PACKAGE BODY cn_rollup_pvt AS
2 --$Header: cnvrollb.pls 120.3 2005/09/12 10:58:57 ymao ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='cn_rollup_pvt';
5
6 -- API name : get_active_role
7 -- Type : Private.
8 -- Pre-reqs : None
9 -- Parameters :
10 -- IN : p_api_version NUMBER Require
11 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
12 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
13 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
14 -- OUT : x_return_status VARCHAR2(1)
15 -- x_msg_count NUMBER
16 -- x_msg_data VARCHAR2(2000)
17 -- Version : Current version 1.0
18 -- Initial version 1.0
19 --
20 -- Notes :
21 --
22 -- End of comments
23
24 PROCEDURE get_active_role
25 ( p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
27 p_commit IN VARCHAR2 := FND_API.G_FALSE,
28 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 p_org_id IN NUMBER,
33 p_srp IN srp_group_rec_type,
34 x_role OUT NOCOPY role_tbl_type)
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'get_active_role';
37 l_api_version CONSTANT NUMBER := 1.0;
38
39 l_counter NUMBER(15);
40
41 CURSOR roles_csr IS
42 SELECT
43 role_id,
44 greatest(start_date_active, p_srp.start_date) start_date,
45 least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active)) end_date,
46 manager_flag
47 FROM cn_srp_comp_groups_v
48 WHERE comp_group_id = p_srp.group_id
49 AND salesrep_id = p_srp.salesrep_id
50 AND org_id = p_org_id
51 AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
52 AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
53 BEGIN
54 -- Standard call to check for call compatibility.
55 IF NOT FND_API.Compatible_API_Call
56 (l_api_version ,
57 p_api_version ,
58 l_api_name ,
59 G_PKG_NAME )
60 THEN
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63
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 -- API body
73
74 l_counter := 0;
75 FOR eachrole IN roles_csr LOOP
76 x_role(l_counter).role_id := eachrole.role_id;
77 x_role(l_counter).manager_flag := eachrole.manager_flag;
78 x_role(l_counter).start_date := eachrole.start_date;
79 x_role(l_counter).end_date := eachrole.end_date;
80 l_counter := l_counter + 1;
81 END LOOP;
82
83 -- End of API body.
84
85 -- Standard call to get message count and if count is 1, get message info.
86 FND_MSG_PUB.Count_And_Get
87 (p_count => x_msg_count ,
88 p_data => x_msg_data );
89 EXCEPTION
90 WHEN FND_API.G_EXC_ERROR THEN
91 x_return_status := FND_API.G_RET_STS_ERROR ;
92 FND_MSG_PUB.Count_And_Get
93 (p_count => x_msg_count ,
94 p_data => x_msg_data );
95 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
97 FND_MSG_PUB.Count_And_Get
98 (p_count => x_msg_count ,
99 p_data => x_msg_data );
100 WHEN OTHERS THEN
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
102 IF FND_MSG_PUB.Check_Msg_Level
103 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
104 THEN
105 FND_MSG_PUB.Add_Exc_Msg
106 (G_PKG_NAME ,
107 l_api_name );
108 END IF;
109 FND_MSG_PUB.Count_And_Get
110 (p_count => x_msg_count ,
111 p_data => x_msg_data );
112
113 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
114 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
115 'cn.plsql.cn_rollup_pvt.get_active_role.exception',
116 sqlerrm);
117 end if;
118
119 END get_active_role;
120
121 -- API name : get_active_group_member
122 -- Type : Private.
123 -- Pre-reqs : None
124 -- Parameters :
125 -- IN : p_api_version NUMBER Require
126 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
127 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
128 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
129 -- OUT : x_return_status VARCHAR2(1)
130 -- x_msg_count NUMBER
131 -- x_msg_data VARCHAR2(2000)
132 --
133 -- Version : Current version 1.0
134 -- Initial version 1.0
135 --
136 -- Notes :
137 --
138 -- End of comments
139
140 PROCEDURE get_active_group_member
141 ( p_api_version IN NUMBER,
142 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
143 p_commit IN VARCHAR2 := FND_API.G_FALSE,
144 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2,
148 p_org_id IN NUMBER,
149 p_group IN group_rec_type,
150 x_group_mem OUT NOCOPY group_mem_tbl_type)
151 IS
152 l_api_name CONSTANT VARCHAR2(30) := 'get_active_group_member';
153 l_api_version CONSTANT NUMBER := 1.0;
154
155 CURSOR group_members_csr IS
156 SELECT
157 salesrep_id,
158 role_id,
159 manager_flag,
160 greatest(start_date_active, p_group.start_date) start_date,
161 least(nvl(end_date_active, p_group.end_date), nvl(p_group.end_date, end_date_active)) end_date
162 FROM cn_srp_comp_groups_v
163 WHERE comp_group_id = p_group.group_id
164 AND org_id = p_org_id
165 AND (end_date_active IS NULL OR p_group.start_date <= end_date_active)
166 AND (p_group.end_date IS NULL OR p_group.end_date >= start_date_active);
167
168 l_counter NUMBER(15);
169 BEGIN
170 -- Standard call to check for call compatibility.
171 IF NOT FND_API.Compatible_API_Call
172 (l_api_version ,
173 p_api_version ,
174 l_api_name ,
175 G_PKG_NAME )
176 THEN
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179
180 -- Initialize message list if p_init_msg_list is set to TRUE.
181 IF FND_API.to_Boolean( p_init_msg_list ) THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184
185 -- Initialize API return status to success
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 -- API body
189
190 l_counter := 0;
191 FOR eachmem IN group_members_csr LOOP
192 x_group_mem(l_counter).salesrep_id := eachmem.salesrep_id;
193 x_group_mem(l_counter).role_id := eachmem.role_id;
194 x_group_mem(l_counter).manager_flag := eachmem.manager_flag;
195 x_group_mem(l_counter).start_date := eachmem.start_date;
196 x_group_mem(l_counter).end_date := eachmem.end_date;
197
198 l_counter := l_counter + 1;
199 END LOOP;
200
201 -- End of API body.
202
203 -- Standard call to get message count and if count is 1, get message info.
204 FND_MSG_PUB.Count_And_Get
205 (p_count => x_msg_count ,
206 p_data => x_msg_data );
207 EXCEPTION
208 WHEN FND_API.G_EXC_ERROR THEN
209 x_return_status := FND_API.G_RET_STS_ERROR ;
210 FND_MSG_PUB.Count_And_Get
211 (p_count => x_msg_count ,
212 p_data => x_msg_data );
213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
215 FND_MSG_PUB.Count_And_Get
216 (p_count => x_msg_count ,
217 p_data => x_msg_data );
218 WHEN OTHERS THEN
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
220 IF FND_MSG_PUB.Check_Msg_Level
221 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
222 THEN
223 FND_MSG_PUB.Add_Exc_Msg
224 (G_PKG_NAME ,
225 l_api_name );
226 END IF;
227 FND_MSG_PUB.Count_And_Get
228 (p_count => x_msg_count ,
229 p_data => x_msg_data );
230
231 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
232 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
233 'cn.plsql.cn_rollup_pvt.get_active_group_member.exception',
234 sqlerrm);
235 end if;
236 END get_active_group_member;
237
238 -- API name : get_active_group_member
239 -- Type : Private.
240 -- Pre-reqs : None
241 --
242 -- Parameters :
243 -- IN : p_api_version NUMBER Require
244 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
245 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
246 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
247 -- OUT : x_return_status VARCHAR2(1)
248 -- x_msg_count NUMBER
249 -- x_msg_data VARCHAR2(2000)
250 -- Version : Current version 1.0
251 -- Initial version 1.0
252 --
253 -- Notes :
254 --
255 -- End of comments
256
257 PROCEDURE get_active_group_member
258 ( p_api_version IN NUMBER,
259 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
260 p_commit IN VARCHAR2 := FND_API.G_FALSE,
261 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2,
265 p_org_id IN NUMBER,
266 p_group IN group_tbl_type,
267 x_group_mem OUT NOCOPY srp_group_tbl_type)
268 IS
269 l_api_name CONSTANT VARCHAR2(30) := 'get_active_group_member';
270 l_api_version CONSTANT NUMBER := 1.0;
271
272 l_counter NUMBER(15);
273
274 l_group_member cn_rollup_pvt.group_mem_tbl_type;
275 BEGIN
276 -- Standard call to check for call compatibility.
277 IF NOT FND_API.Compatible_API_Call
278 (l_api_version ,
279 p_api_version ,
280 l_api_name ,
281 G_PKG_NAME )
282 THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285
286 -- Initialize message list if p_init_msg_list is set to TRUE.
287 IF FND_API.to_Boolean( p_init_msg_list ) THEN
288 FND_MSG_PUB.initialize;
289 END IF;
290
291 -- Initialize API return status to success
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293
294 -- API body
295
296 l_counter := 0;
297 FOR eachgrp IN p_group.first .. p_group.last LOOP
298 get_active_group_member
299 (p_api_version => 1.0,
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data,
303 p_org_id => p_org_id,
304 p_group => p_group(eachgrp),
305 x_group_mem => l_group_member);
306
307 IF l_group_member.COUNT >0 THEN
308 FOR i IN l_group_member.first .. l_group_member.last LOOP
309 x_group_mem(l_counter).salesrep_id := l_group_member(i).salesrep_id;
310 x_group_mem(l_counter).group_id := p_group(eachgrp).group_id;
311 x_group_mem(l_counter).start_date := l_group_member(i).start_date;
312 x_group_mem(l_counter).end_date := l_group_member(i).end_date;
313 x_group_mem(l_counter).level := p_group(eachgrp).level;
314
315 l_counter := l_counter + 1;
316 END LOOP;
317 END IF;
318 END LOOP;
319
320 -- End of API body.
321
322 -- Standard call to get message count and if count is 1, get message info.
323 FND_MSG_PUB.Count_And_Get
324 (p_count => x_msg_count ,
325 p_data => x_msg_data );
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_ERROR ;
329 FND_MSG_PUB.Count_And_Get
330 (p_count => x_msg_count ,
331 p_data => x_msg_data );
332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334 FND_MSG_PUB.Count_And_Get
335 (p_count => x_msg_count ,
336 p_data => x_msg_data );
337 WHEN OTHERS THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
339 IF FND_MSG_PUB.Check_Msg_Level
340 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
341 THEN
342 FND_MSG_PUB.Add_Exc_Msg
343 (G_PKG_NAME ,
344 l_api_name );
345 END IF;
346 FND_MSG_PUB.Count_And_Get
347 (p_count => x_msg_count ,
348 p_data => x_msg_data );
349
350 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
351 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
352 'cn.plsql.cn_rollup_pvt.get_active_group_member.exception',
353 sqlerrm);
354 end if;
355 END get_active_group_member;
356
357 -- API name : get_active_group
358 -- Type : Private.
359 -- Pre-reqs : None
360 -- Usage :
361 --
362 -- Parameters :
363 -- IN : p_api_version NUMBER Require
364 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
365 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
366 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
367 -- OUT : x_return_status VARCHAR2(1)
368 -- x_msg_count NUMBER
369 -- x_msg_data VARCHAR2(2000)
370 --
371 -- Version : Current version 1.0
372 -- Initial version 1.0
373 --
374 -- Notes :
375 --
376 -- End of comments
377
378 PROCEDURE get_active_group
379 ( p_api_version IN NUMBER,
380 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
381 p_commit IN VARCHAR2 := FND_API.G_FALSE,
382 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
383 x_return_status OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 p_org_id IN NUMBER,
387 p_srp IN srp_rec_type,
388 x_active_group OUT NOCOPY active_group_tbl_type)
389 IS
390 l_api_name CONSTANT VARCHAR2(30) := 'get_active_group';
391 l_api_version CONSTANT NUMBER := 1.0;
392
393 CURSOR groups_csr IS
394 SELECT
395 comp_group_id,
396 role_id,
397 manager_flag,
398 greatest(start_date_active, p_srp.start_date) start_date,
399 least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active)) end_date
400 FROM cn_srp_comp_groups_v
404 AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
401 WHERE salesrep_id = p_srp.salesrep_id
402 AND org_id = p_org_id
403 AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
405
406 l_counter NUMBER(15) := 0;
407 BEGIN
408 -- Standard call to check for call compatibility.
409 IF NOT FND_API.Compatible_API_Call
410 (l_api_version ,
411 p_api_version ,
412 l_api_name ,
413 G_PKG_NAME )
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417
418 -- Initialize message list if p_init_msg_list is set to TRUE.
419 IF FND_API.to_Boolean( p_init_msg_list ) THEN
420 FND_MSG_PUB.initialize;
421 END IF;
422
423 -- Initialize API return status to success
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425
426 -- API body
427
428 FOR eachgroup IN groups_csr LOOP
429 x_active_group(l_counter).group_id := eachgroup.comp_group_id;
430 x_active_group(l_counter).role_id := eachgroup.role_id;
431 x_active_group(l_counter).manager_flag := eachgroup.manager_flag;
432 x_active_group(l_counter).start_date := eachgroup.start_date;
433 x_active_group(l_counter).end_date := eachgroup.end_date;
434
435 l_counter := l_counter + 1;
436 END LOOP;
437
438 -- End of API body.
439
440 -- Standard call to get message count and if count is 1, get message info.
441 FND_MSG_PUB.Count_And_Get
442 (p_count => x_msg_count ,
443 p_data => x_msg_data );
444 EXCEPTION
445 WHEN FND_API.G_EXC_ERROR THEN
446 x_return_status := FND_API.G_RET_STS_ERROR ;
447 FND_MSG_PUB.Count_And_Get
448 (p_count => x_msg_count ,
449 p_data => x_msg_data );
450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
452 FND_MSG_PUB.Count_And_Get
453 (p_count => x_msg_count ,
454 p_data => x_msg_data );
455 WHEN OTHERS THEN
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 IF FND_MSG_PUB.Check_Msg_Level
458 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
459 THEN
460 FND_MSG_PUB.Add_Exc_Msg
461 (G_PKG_NAME ,
462 l_api_name );
463 END IF;
464 FND_MSG_PUB.Count_And_Get
465 (p_count => x_msg_count ,
466 p_data => x_msg_data );
467
468 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
469 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
470 'cn.plsql.cn_rollup_pvt.get_active_group.exception',
471 sqlerrm);
472 end if;
473
474 END get_active_group;
475
476 -- API name : get_ancestor_group
477 -- Type : Private.
478 -- Pre-reqs : None
479 --
480 -- Parameters :
481 -- IN : p_api_version NUMBER Require
482 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
483 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
484 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
485 -- OUT : x_return_status VARCHAR2(1)
486 -- x_msg_count NUMBER
487 -- x_msg_data VARCHAR2(2000)
488 --
489 -- Version : Current version 1.0
490 -- Initial version 1.0
491 --
492 -- Notes :
493 --
494 -- End of comments
495
496 PROCEDURE get_ancestor_group
497 ( p_api_version IN NUMBER,
498 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
499 p_commit IN VARCHAR2 := FND_API.G_FALSE,
500 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_group IN group_rec_type,
505 x_group OUT NOCOPY group_tbl_type)
506 IS
507 l_api_name CONSTANT VARCHAR2(30) := 'get_ancestor_group';
508 l_api_version CONSTANT NUMBER := 1.0;
509
510 l_counter NUMBER(15) := 0;
511
512 CURSOR groups_csr IS
513 SELECT
514 parent_group_id,
515 denorm_level,
516 greatest(start_date_active, p_group.start_date) start_date,
517 least(nvl(end_date_active, p_group.end_date), nvl(p_group.end_date, end_date_active)) end_date
518 FROM cn_groups_denorm_v
519 WHERE group_id = p_group.group_id
520 AND (end_date_active IS NULL OR p_group.start_date <= end_date_active)
521 AND (p_group.end_date IS NULL OR p_group.end_date >= start_date_active);
522 BEGIN
523 -- Standard call to check for call compatibility.
524 IF NOT FND_API.Compatible_API_Call
525 (l_api_version ,
526 p_api_version ,
527 l_api_name ,
528 G_PKG_NAME )
529 THEN
530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
531 END IF;
532
533 -- Initialize message list if p_init_msg_list is set to TRUE.
537
534 IF FND_API.to_Boolean( p_init_msg_list ) THEN
535 FND_MSG_PUB.initialize;
536 END IF;
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 -- API body
542
543 FOR eachgroup IN groups_csr LOOP
544 x_group(l_counter).group_id := eachgroup.parent_group_id;
545 x_group(l_counter).start_date := eachgroup.start_date;
546 x_group(l_counter).end_date := eachgroup.end_date;
547 x_group(l_counter).level := eachgroup.denorm_level;
548
549 l_counter := l_counter + 1;
550 END LOOP;
551
552 -- End of API body.
553
554 -- Standard call to get message count and if count is 1, get message info.
555 FND_MSG_PUB.Count_And_Get
556 (p_count => x_msg_count ,
557 p_data => x_msg_data );
558 EXCEPTION
559 WHEN FND_API.G_EXC_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_ERROR ;
561 FND_MSG_PUB.Count_And_Get
562 (p_count => x_msg_count ,
563 p_data => x_msg_data );
564 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
566 FND_MSG_PUB.Count_And_Get
567 (p_count => x_msg_count ,
568 p_data => x_msg_data );
569 WHEN OTHERS THEN
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
571 IF FND_MSG_PUB.Check_Msg_Level
572 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg
575 (G_PKG_NAME ,
576 l_api_name );
577 END IF;
578 FND_MSG_PUB.Count_And_Get
579 (p_count => x_msg_count ,
580 p_data => x_msg_data );
581
582 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
583 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
584 'cn.plsql.cn_rollup_pvt.get_ancestor_group.exception',
585 sqlerrm);
586 end if;
587
588 END get_ancestor_group;
589
590 -- API name : get_descendant_group
591 -- Type : Private.
592 -- Pre-reqs : None
593 --
594 -- Parameters :
595 -- IN : p_api_version NUMBER Require
596 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
597 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
598 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
599 -- OUT : x_return_status VARCHAR2(1)
600 -- x_msg_count NUMBER
601 -- x_msg_data VARCHAR2(2000)
602 -- Version : Current version 1.0
603 -- Initial version 1.0
604 --
605 -- Notes :
606 --
607 -- End of comments
608
609 PROCEDURE get_descendant_group
610 ( p_api_version IN NUMBER,
611 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
612 p_commit IN VARCHAR2 := FND_API.G_FALSE,
613 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
614 x_return_status OUT NOCOPY VARCHAR2,
615 x_msg_count OUT NOCOPY NUMBER,
616 x_msg_data OUT NOCOPY VARCHAR2,
617 p_group IN group_rec_type,
618 x_group OUT NOCOPY group_tbl_type)
619 IS
620 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_group';
621 l_api_version CONSTANT NUMBER := 1.0;
622
623 l_counter NUMBER(15) := 0;
624
625 CURSOR groups_csr IS
626 SELECT
627 group_id,
628 denorm_level,
629 greatest(start_date_active, p_group.start_date) start_date,
630 least(nvl(end_date_active, p_group.end_date), nvl(p_group.end_date, end_date_active)) end_date
631 FROM cn_groups_denorm_v
632 WHERE parent_group_id = p_group.group_id
633 AND (end_date_active IS NULL OR p_group.start_date <= end_date_active)
634 AND (p_group.end_date IS NULL OR p_group.end_date >= start_date_active);
635 BEGIN
636 -- Standard call to check for call compatibility.
637 IF NOT FND_API.Compatible_API_Call
638 (l_api_version ,
639 p_api_version ,
640 l_api_name ,
641 G_PKG_NAME )
642 THEN
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 END IF;
645
646 -- Initialize message list if p_init_msg_list is set to TRUE.
647 IF FND_API.to_Boolean( p_init_msg_list ) THEN
648 FND_MSG_PUB.initialize;
649 END IF;
650
651 -- Initialize API return status to success
652 x_return_status := FND_API.G_RET_STS_SUCCESS;
653
654 -- API body
655
656 FOR eachgroup IN groups_csr LOOP
657 x_group(l_counter).group_id := eachgroup.group_id;
658 x_group(l_counter).start_date := eachgroup.start_date;
659 x_group(l_counter).end_date := eachgroup.end_date;
660 x_group(l_counter).level := eachgroup.denorm_level;
661
662 l_counter := l_counter + 1;
663 END LOOP;
664
665 -- End of API body.
666
667 -- Standard call to get message count and if count is 1, get message info.
668 FND_MSG_PUB.Count_And_Get
672 WHEN FND_API.G_EXC_ERROR THEN
669 (p_count => x_msg_count ,
670 p_data => x_msg_data );
671 EXCEPTION
673 x_return_status := FND_API.G_RET_STS_ERROR ;
674 FND_MSG_PUB.Count_And_Get
675 (p_count => x_msg_count ,
676 p_data => x_msg_data );
677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
679 FND_MSG_PUB.Count_And_Get
680 (p_count => x_msg_count ,
681 p_data => x_msg_data );
682 WHEN OTHERS THEN
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
684 IF FND_MSG_PUB.Check_Msg_Level
685 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
686 THEN
687 FND_MSG_PUB.Add_Exc_Msg
688 (G_PKG_NAME ,
689 l_api_name );
690 END IF;
691 FND_MSG_PUB.Count_And_Get
692 (p_count => x_msg_count ,
693 p_data => x_msg_data );
694
695 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
696 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
697 'cn.plsql.cn_rollup_pvt.get_descendant_group.exception',
698 sqlerrm);
699 end if;
700
701 END get_descendant_group;
702
703 -- API name : get_ancestor_salesrep
704 -- Type : Private.
705 -- Pre-reqs : None
706 --
707 -- Parameters :
708 -- IN : p_api_version NUMBER Require
709 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
710 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
711 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
712 -- OUT : x_return_status VARCHAR2(1)
713 -- x_msg_count NUMBER
714 -- x_msg_data VARCHAR2(2000)
715 --
716 -- Version : Current version 1.0
717 -- Initial version 1.0
718 --
719 -- Notes :
720 --
721 -- End of comments
722
723 PROCEDURE get_ancestor_salesrep
724 ( p_api_version IN NUMBER,
725 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
726 p_commit IN VARCHAR2 := FND_API.G_FALSE,
727 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
728 x_return_status OUT NOCOPY VARCHAR2,
729 x_msg_count OUT NOCOPY NUMBER,
730 x_msg_data OUT NOCOPY VARCHAR2,
731 p_org_id IN NUMBER,
732 p_srp IN srp_group_rec_type,
733 x_srp OUT NOCOPY srp_group_tbl_type)
734 IS
735 l_api_name CONSTANT VARCHAR2(30) := 'get_ancestor_salesrep';
736 l_api_version CONSTANT NUMBER := 1.0;
737
738 l_counter NUMBER(15);
739 l_group group_rec_type;
740 l_ancestor_group group_tbl_type;
741
742 CURSOR managers_csr IS
743 SELECT
744 salesrep_id,
745 greatest(start_date_active, p_srp.start_date) start_date,
746 least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active)) end_date
747 FROM cn_srp_comp_groups_v
748 WHERE comp_group_id = p_srp.group_id
749 AND salesrep_id <> p_srp.salesrep_id
750 AND manager_flag = 'Y'
751 AND org_id = p_org_id
752 AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
753 AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
754
755 CURSOR members_csr(p_group_id NUMBER, p_start_date DATE, p_end_date DATE) IS
756 SELECT
757 salesrep_id,
758 greatest(start_date_active, p_start_date) start_date,
759 least(nvl(end_date_active, p_end_date), nvl(p_end_date, end_date_active)) end_date
760 FROM cn_srp_comp_groups_v
761 WHERE comp_group_id = p_group_id
762 AND org_id = p_org_id
763 AND (end_date_active IS NULL OR p_start_date <= end_date_active)
764 AND (p_end_date IS NULL OR p_end_date >= start_date_active);
765 BEGIN
766 -- Standard call to check for call compatibility.
767 IF NOT FND_API.Compatible_API_Call
768 (l_api_version ,
769 p_api_version ,
770 l_api_name ,
771 G_PKG_NAME )
772 THEN
773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
774 END IF;
775
776 -- Initialize message list if p_init_msg_list is set to TRUE.
777 IF FND_API.to_Boolean( p_init_msg_list ) THEN
778 FND_MSG_PUB.initialize;
779 END IF;
780
781 -- Initialize API return status to success
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783
784 -- API body
785
786 l_counter := 0;
787
788 -- find all managers in this group
789 FOR eachmgr IN managers_csr LOOP
790 x_srp(l_counter).salesrep_id := eachmgr.salesrep_id;
791 x_srp(l_counter).group_id := p_srp.group_id;
792 x_srp(l_counter).start_date := eachmgr.start_date;
793 x_srp(l_counter).end_date := eachmgr.end_date;
794 x_srp(l_counter).level := 0;
795
796 l_counter := l_counter + 1;
797 END LOOP;
798
799 -- loop through each ancestor group.
800 l_group.group_id := p_srp.group_id;
804 get_ancestor_group
801 l_group.start_date := p_srp.start_date;
802 l_group.end_date := p_srp.end_date;
803
805 ( p_api_version => 1.0,
806 x_return_status => x_return_status,
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data,
809 p_group => l_group,
810 x_group => l_ancestor_group);
811
812 IF x_return_status = FND_API.g_ret_sts_error THEN
813 RAISE FND_API.g_exc_error;
814 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
815 RAISE FND_API.g_exc_unexpected_error;
816 END IF;
817
818 IF( l_ancestor_group.COUNT > 0) THEN
819 FOR eachgroup IN l_ancestor_group.first .. l_ancestor_group.last LOOP
820 FOR eachsrp IN members_csr(l_ancestor_group(eachgroup).group_id, l_ancestor_group(eachgroup).start_date, l_ancestor_group(eachgroup).end_date) LOOP
821 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
822 x_srp(l_counter).group_id := l_ancestor_group(eachgroup).group_id;
823 x_srp(l_counter).start_date := eachsrp.start_date;
824 x_srp(l_counter).end_date := eachsrp.end_date;
825 x_srp(l_counter).level := l_ancestor_group(eachgroup).level;
826
827 l_counter := l_counter + 1;
828 END LOOP; -- end of eachsrp
829 END LOOP; -- end of eachgroup
830 END IF;
831
832
833 -- End of API body.
834
835 -- Standard call to get message count and if count is 1, get message info.
836 FND_MSG_PUB.Count_And_Get
837 (p_count => x_msg_count ,
838 p_data => x_msg_data );
839 EXCEPTION
840 WHEN FND_API.G_EXC_ERROR THEN
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 FND_MSG_PUB.Count_And_Get
843 (p_count => x_msg_count ,
844 p_data => x_msg_data );
845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
847 FND_MSG_PUB.Count_And_Get
848 (p_count => x_msg_count ,
849 p_data => x_msg_data );
850 WHEN OTHERS THEN
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
852 IF FND_MSG_PUB.Check_Msg_Level
853 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
854 THEN
855 FND_MSG_PUB.Add_Exc_Msg
856 (G_PKG_NAME ,
857 l_api_name );
858 END IF;
859 FND_MSG_PUB.Count_And_Get
860 (p_count => x_msg_count ,
861 p_data => x_msg_data );
862
863 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
864 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
865 'cn.plsql.cn_rollup_pvt.get_ancestor_salesrep.exception',
866 sqlerrm);
867 end if;
868
869 END get_ancestor_salesrep;
870
871 -- API name : get_descendant_salesrep
872 -- Type : Private.
873 -- Pre-reqs : None
874 --
875 -- Parameters :
876 -- IN : p_api_version NUMBER Require
877 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
878 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
879 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
880 -- OUT : x_return_status VARCHAR2(1)
881 -- x_msg_count NUMBER
882 -- x_msg_data VARCHAR2(2000)
883 --
884 -- Version : Current version 1.0
885 -- Initial version 1.0
886 --
887 -- Notes :
888 --
889 -- End of comments
890
891 PROCEDURE get_descendant_salesrep
892 ( p_api_version IN NUMBER,
893 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
894 p_commit IN VARCHAR2 := FND_API.G_FALSE,
895 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_msg_count OUT NOCOPY NUMBER,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 p_org_id IN NUMBER,
900 p_srp IN srp_group_rec_type,
901 x_srp OUT NOCOPY srp_group_tbl_type)
902 IS
903 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_salesrep';
904 l_api_version CONSTANT NUMBER := 1.0;
905
906 l_counter NUMBER(15) := 0;
907 l_group group_rec_type;
908 l_descendant_group group_tbl_type;
909 l_mgr_flag VARCHAR2(1);
910
911 CURSOR members_csr(p_group_id NUMBER, p_start_date DATE, p_end_date DATE) IS
912 SELECT
913 salesrep_id,
914 greatest(start_date_active, p_start_date) start_date,
915 least(nvl(end_date_active, p_end_date), nvl(p_end_date, end_date_active)) end_date
916 FROM cn_srp_comp_groups_v
917 WHERE comp_group_id = p_group_id
918 AND org_id = p_org_id
919 AND (end_date_active IS NULL OR p_start_date <= end_date_active)
920 AND (p_end_date IS NULL OR p_end_date >= start_date_active);
921
922 CURSOR mgr_check IS
923 SELECT manager_flag
924 FROM cn_srp_comp_groups_v
925 WHERE comp_group_id = p_srp.group_id
926 AND org_id = p_org_id
927 AND salesrep_id = p_srp.salesrep_id
931 -- Standard call to check for call compatibility.
928 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
929 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
930 BEGIN
932 IF NOT FND_API.Compatible_API_Call
933 (l_api_version ,
934 p_api_version ,
935 l_api_name ,
936 G_PKG_NAME )
937 THEN
938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
939 END IF;
940
941 -- Initialize message list if p_init_msg_list is set to TRUE.
942 IF FND_API.to_Boolean( p_init_msg_list ) THEN
943 FND_MSG_PUB.initialize;
944 END IF;
945
946 -- Initialize API return status to success
947 x_return_status := FND_API.G_RET_STS_SUCCESS;
948 -- API body
949
950 -- if p_srp.salesrep_id is a manager, get the other members of his own group
951 OPEN mgr_check;
952 FETCH mgr_check INTO l_mgr_flag;
953 CLOSE mgr_check;
954
955 IF (l_mgr_flag = 'Y') THEN
956 FOR eachmem IN members_csr(p_srp.group_id, p_srp.start_date, p_srp.end_date) LOOP
957 IF eachmem.salesrep_id <> p_srp.salesrep_id THEN
958 x_srp(l_counter).salesrep_id := eachmem.salesrep_id;
959 x_srp(l_counter).group_id := p_srp.group_id;
960 x_srp(l_counter).start_date := eachmem.start_date;
961 x_srp(l_counter).end_date := eachmem.end_date;
962 x_srp(l_counter).level := 0;
963
964 l_counter := l_counter + 1;
965 END IF;
966 END LOOP;
967 END IF;
968
969 -- initialize l_group
970 l_group.group_id := p_srp.group_id;
971 l_group.start_date := p_srp.start_date;
972 l_group.end_date := p_srp.end_date;
973
974 get_descendant_group
975 ( p_api_version => 1.0,
976 x_return_status => x_return_status,
977 x_msg_count => x_msg_count,
978 x_msg_data => x_msg_data,
979 p_group => l_group,
980 x_group => l_descendant_group);
981
982 IF x_return_status = FND_API.g_ret_sts_error THEN
983 RAISE FND_API.g_exc_error;
984 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
985 RAISE FND_API.g_exc_unexpected_error;
986 END IF;
987
988 IF (l_descendant_group.COUNT > 0) THEN
989 FOR eachgroup IN l_descendant_group.first .. l_descendant_group.last LOOP
990 FOR eachsrp IN members_csr(l_descendant_group(eachgroup).group_id, l_descendant_group(eachgroup).start_date,l_descendant_group(eachgroup).end_date) LOOP
991 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
992 x_srp(l_counter).group_id := l_descendant_group(eachgroup).group_id;
993 x_srp(l_counter).start_date := eachsrp.start_date;
994 x_srp(l_counter).end_date := eachsrp.end_date;
995 x_srp(l_counter).level := l_descendant_group(eachgroup).level;
996
997 l_counter := l_counter + 1;
998 END LOOP; -- end of eachsrp
999 END LOOP; -- end of eachgroup
1000 END IF;
1001
1002 -- End of API body.
1003
1004 -- Standard call to get message count and if count is 1, get message info.
1005 FND_MSG_PUB.Count_And_Get
1006 (p_count => x_msg_count ,
1007 p_data => x_msg_data );
1008 EXCEPTION
1009 WHEN FND_API.G_EXC_ERROR THEN
1010 x_return_status := FND_API.G_RET_STS_ERROR ;
1011 FND_MSG_PUB.Count_And_Get
1012 (p_count => x_msg_count ,
1013 p_data => x_msg_data );
1014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1016 FND_MSG_PUB.Count_And_Get
1017 (p_count => x_msg_count ,
1018 p_data => x_msg_data );
1019 WHEN OTHERS THEN
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1021 IF FND_MSG_PUB.Check_Msg_Level
1022 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1023 THEN
1024 FND_MSG_PUB.Add_Exc_Msg
1025 (G_PKG_NAME ,
1026 l_api_name );
1027 END IF;
1028 FND_MSG_PUB.Count_And_Get
1029 (p_count => x_msg_count ,
1030 p_data => x_msg_data );
1031
1032 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1033 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1034 'cn.plsql.cn_rollup_pvt.get_descendant_salesrep.exception',
1035 sqlerrm);
1036 end if;
1037
1038 END get_descendant_salesrep;
1039
1040 -- API name : get_descendant_salesrep
1041 -- Type : Private.
1042 -- Pre-reqs : None
1043 --
1044 -- Parameters :
1045 -- IN : p_api_version NUMBER Require
1046 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
1047 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
1048 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
1049 -- OUT : x_return_status VARCHAR2(1)
1050 -- x_msg_count NUMBER
1051 -- x_msg_data VARCHAR2(2000)
1052 --
1053 -- Version : Current version 1.0
1054 -- Initial version 1.0
1055 --
1056 -- Notes :
1057 --
1058 -- End of comments
1059
1060 PROCEDURE get_descendant_salesrep
1061 ( p_api_version IN NUMBER,
1062 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1063 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1064 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1065 x_return_status OUT NOCOPY VARCHAR2,
1066 x_msg_count OUT NOCOPY NUMBER,
1067 x_msg_data OUT NOCOPY VARCHAR2,
1068 p_org_id IN NUMBER,
1069 p_srp IN srp_rec_type,
1070 x_srp OUT NOCOPY srp_tbl_type)
1071 IS
1072 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_salesrep';
1073 l_api_version CONSTANT NUMBER := 1.0;
1074
1075 l_counter NUMBER(15);
1076 l_temp NUMBER(15);
1077
1078 CURSOR groups_csr IS
1079 SELECT DISTINCT comp_group_id
1080 FROM cn_srp_comp_groups_v
1081 WHERE salesrep_id = p_srp.salesrep_id
1082 AND org_id = p_org_id
1083 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
1084 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
1085
1086 CURSOR members_csr (p_group_id NUMBER) IS
1087 SELECT DISTINCT salesrep_id
1088 FROM cn_srp_comp_groups_v
1089 WHERE comp_group_id = p_group_id
1090 AND salesrep_id <> p_srp.salesrep_id
1091 AND org_id = p_org_id
1092 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
1093 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
1094
1095 CURSOR descendant_csr(p_group_id NUMBER) IS
1096 SELECT DISTINCT salesrep_id
1097 FROM cn_srp_comp_groups_v srp,
1098 cn_groups_denorm_v hier
1099 WHERE srp.comp_group_id = hier.group_id
1100 AND srp.org_id = p_org_id
1101 AND hier.parent_group_id = p_group_id
1102 AND (p_srp.end_date IS NULL OR hier.start_date_active <= p_srp.end_date)
1103 AND (hier.end_date_active IS NULL OR hier.end_date_active >= p_srp.start_date)
1104 AND (p_srp.end_date IS NULL OR srp.start_date_active <= p_srp.end_date)
1105 AND (srp.end_date_active IS NULL OR srp.end_date_active >= p_srp.start_date);
1106 BEGIN
1107 -- Standard call to check for call compatibility.
1108 IF NOT FND_API.Compatible_API_Call
1109 (l_api_version ,
1110 p_api_version ,
1111 l_api_name ,
1112 G_PKG_NAME )
1113 THEN
1114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1115 END IF;
1116
1117 -- Initialize message list if p_init_msg_list is set to TRUE.
1118 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1119 FND_MSG_PUB.initialize;
1120 END IF;
1121
1122 -- Initialize API return status to success
1123 x_return_status := FND_API.G_RET_STS_SUCCESS;
1124 -- API body
1125
1126 l_counter := 0;
1127
1128 FOR eachgroup IN groups_csr LOOP
1129 SELECT COUNT(*)
1130 INTO l_temp
1131 FROM cn_srp_comp_groups_v
1132 WHERE salesrep_id = p_srp.salesrep_id
1133 AND org_id = p_org_id
1134 AND comp_group_id = eachgroup.comp_group_id
1135 AND manager_flag = 'Y'
1136 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
1137 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
1138
1139 IF l_temp > 0 THEN
1140 FOR eachsrp IN members_csr(eachgroup.comp_group_id) LOOP
1141 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
1142
1143 l_counter := l_counter + 1;
1144 END LOOP; -- end of eachsrp
1145 END IF; -- end of l_temp check
1146
1147 FOR eachsrp IN descendant_csr(eachgroup.comp_group_id) LOOP
1148 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
1149
1150 l_counter := l_counter + 1;
1151 END LOOP; -- End of eachsrp
1152 END LOOP; -- End of eachgroup
1153 -- End of API body.
1154
1155 -- Standard call to get message count and if count is 1, get message info.
1156 FND_MSG_PUB.Count_And_Get
1157 (p_count => x_msg_count ,
1158 p_data => x_msg_data );
1159 EXCEPTION
1160 WHEN FND_API.G_EXC_ERROR THEN
1161 x_return_status := FND_API.G_RET_STS_ERROR ;
1162 FND_MSG_PUB.Count_And_Get
1163 (p_count => x_msg_count ,
1164 p_data => x_msg_data );
1165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1167 FND_MSG_PUB.Count_And_Get
1168 (p_count => x_msg_count ,
1169 p_data => x_msg_data );
1170 WHEN OTHERS THEN
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1172 IF FND_MSG_PUB.Check_Msg_Level
1173 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1174 THEN
1175 FND_MSG_PUB.Add_Exc_Msg
1176 (G_PKG_NAME ,
1177 l_api_name );
1178 END IF;
1179 FND_MSG_PUB.Count_And_Get
1180 (p_count => x_msg_count ,
1181 p_data => x_msg_data );
1182
1183 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1184 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1185 'cn.plsql.cn_rollup_pvt.get_descendant_salesrep.exception',
1186 sqlerrm);
1187 end if;
1188
1189 END get_descendant_salesrep;
1190
1191 END cn_rollup_pvt;