[Home] [Help]
PACKAGE BODY: APPS.CN_ROLLUP_PVT
Source
1 PACKAGE BODY cn_rollup_pvt AS
2 --$Header: cnvrollb.pls 120.4 2010/08/04 22:08:38 rnagired 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,
148 p_org_id IN NUMBER,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2,
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.
290
287 IF FND_API.to_Boolean( p_init_msg_list ) THEN
288 FND_MSG_PUB.initialize;
289 END IF;
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
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)
404 AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_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;
433 x_active_group(l_counter).end_date := eachgroup.end_date;
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;
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.
534 IF FND_API.to_Boolean( p_init_msg_list ) THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537
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 );
572 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
569 WHEN OTHERS THEN
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
571 IF FND_MSG_PUB.Check_Msg_Level
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
669 (p_count => x_msg_count ,
670 p_data => x_msg_data );
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
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)
713 -- x_msg_count NUMBER
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)
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 --fix for the Bug 9953012
756 CURSOR managers_csr IS
757 SELECT salesrep_id,
758 greatest(start_date_active, p_srp.start_date ) start_date,
759 least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active))end_date
760 FROM
761 (SELECT jrs.salesrep_id,
762 rrl.START_DATE_ACTIVE ,
763 rrl.END_DATE_ACTIVE,
764 jrs.org_id,
765 mem.resource_id,
766 mem.group_id comp_group_id,
767 rol.manager_flag
768 FROM jtf_rs_group_members mem,
769 jtf_rs_role_relations rrl,
770 jtf_rs_roles_b rol,
771 jtf_rs_group_usages usg,
772 jtf_rs_salesreps_mo_v jrs
773 WHERE mem.group_member_id = rrl.role_resource_id
774 AND NVL(rrl.delete_flag, 'N') <> 'Y'
775 AND NVL(mem.delete_flag, 'N') <> 'Y'
776 AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
777 AND rrl.role_id = rol.role_id
778 AND rol.role_type_code = 'SALES_COMP'
779 AND (rol.member_flag='Y'
780 OR rol.manager_flag = 'Y')
781 AND mem.group_id = usg.group_id
782 AND usg.usage ='SALES_COMP'
783 AND jrs.resource_id = mem.resource_id)
784 WHERE comp_group_id = p_srp.group_id
785 AND salesrep_id <> p_srp.salesrep_id
786 AND manager_flag = 'Y'
787 AND org_id = p_org_id
788 AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
789 AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
790
791
792 CURSOR members_csr(p_group_id NUMBER, p_start_date DATE, p_end_date DATE) IS
793 SELECT
794 salesrep_id,
795 greatest(start_date_active, p_start_date) start_date,
796 least(nvl(end_date_active, p_end_date), nvl(p_end_date, end_date_active)) end_date
797 FROM cn_srp_comp_groups_v
798 WHERE comp_group_id = p_group_id
799 AND org_id = p_org_id
800 AND (end_date_active IS NULL OR p_start_date <= end_date_active)
801 AND (p_end_date IS NULL OR p_end_date >= start_date_active);
802 BEGIN
803 -- Standard call to check for call compatibility.
804 IF NOT FND_API.Compatible_API_Call
805 (l_api_version ,
806 p_api_version ,
807 l_api_name ,
808 G_PKG_NAME )
809 THEN
810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
811 END IF;
812
813 -- Initialize message list if p_init_msg_list is set to TRUE.
814 IF FND_API.to_Boolean( p_init_msg_list ) THEN
815 FND_MSG_PUB.initialize;
816 END IF;
817
818 -- Initialize API return status to success
819 x_return_status := FND_API.G_RET_STS_SUCCESS;
820
821 -- API body
822
823 l_counter := 0;
824
825 -- find all managers in this group
826 FOR eachmgr IN managers_csr LOOP
827 x_srp(l_counter).salesrep_id := eachmgr.salesrep_id;
828 x_srp(l_counter).group_id := p_srp.group_id;
829 x_srp(l_counter).start_date := eachmgr.start_date;
830 x_srp(l_counter).end_date := eachmgr.end_date;
831 x_srp(l_counter).level := 0;
832
833 l_counter := l_counter + 1;
834 END LOOP;
835
836 -- loop through each ancestor group.
837 l_group.group_id := p_srp.group_id;
838 l_group.start_date := p_srp.start_date;
839 l_group.end_date := p_srp.end_date;
840
841 get_ancestor_group
842 ( p_api_version => 1.0,
843 x_return_status => x_return_status,
844 x_msg_count => x_msg_count,
848
845 x_msg_data => x_msg_data,
846 p_group => l_group,
847 x_group => l_ancestor_group);
849 IF x_return_status = FND_API.g_ret_sts_error THEN
850 RAISE FND_API.g_exc_error;
851 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
852 RAISE FND_API.g_exc_unexpected_error;
853 END IF;
854
855 IF( l_ancestor_group.COUNT > 0) THEN
856 FOR eachgroup IN l_ancestor_group.first .. l_ancestor_group.last LOOP
857 FOR eachsrp IN members_csr(l_ancestor_group(eachgroup).group_id, l_ancestor_group(eachgroup).start_date, l_ancestor_group(eachgroup).end_date) LOOP
858 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
859 x_srp(l_counter).group_id := l_ancestor_group(eachgroup).group_id;
860 x_srp(l_counter).start_date := eachsrp.start_date;
861 x_srp(l_counter).end_date := eachsrp.end_date;
862 x_srp(l_counter).level := l_ancestor_group(eachgroup).level;
863
864 l_counter := l_counter + 1;
865 END LOOP; -- end of eachsrp
866 END LOOP; -- end of eachgroup
867 END IF;
868
869
870 -- End of API body.
871
872 -- Standard call to get message count and if count is 1, get message info.
873 FND_MSG_PUB.Count_And_Get
874 (p_count => x_msg_count ,
875 p_data => x_msg_data );
876 EXCEPTION
877 WHEN FND_API.G_EXC_ERROR THEN
878 x_return_status := FND_API.G_RET_STS_ERROR ;
879 FND_MSG_PUB.Count_And_Get
880 (p_count => x_msg_count ,
881 p_data => x_msg_data );
882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884 FND_MSG_PUB.Count_And_Get
885 (p_count => x_msg_count ,
886 p_data => x_msg_data );
887 WHEN OTHERS THEN
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
889 IF FND_MSG_PUB.Check_Msg_Level
890 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
891 THEN
892 FND_MSG_PUB.Add_Exc_Msg
893 (G_PKG_NAME ,
894 l_api_name );
895 END IF;
896 FND_MSG_PUB.Count_And_Get
897 (p_count => x_msg_count ,
898 p_data => x_msg_data );
899
900 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
901 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
902 'cn.plsql.cn_rollup_pvt.get_ancestor_salesrep.exception',
903 sqlerrm);
904 end if;
905
906 END get_ancestor_salesrep;
907
908 -- API name : get_descendant_salesrep
909 -- Type : Private.
910 -- Pre-reqs : None
911 --
912 -- Parameters :
913 -- IN : p_api_version NUMBER Require
914 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
915 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
916 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
917 -- OUT : x_return_status VARCHAR2(1)
918 -- x_msg_count NUMBER
919 -- x_msg_data VARCHAR2(2000)
920 --
921 -- Version : Current version 1.0
922 -- Initial version 1.0
923 --
924 -- Notes :
925 --
926 -- End of comments
927
928 PROCEDURE get_descendant_salesrep
929 ( p_api_version IN NUMBER,
930 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
931 p_commit IN VARCHAR2 := FND_API.G_FALSE,
932 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
933 x_return_status OUT NOCOPY VARCHAR2,
934 x_msg_count OUT NOCOPY NUMBER,
935 x_msg_data OUT NOCOPY VARCHAR2,
936 p_org_id IN NUMBER,
937 p_srp IN srp_group_rec_type,
938 x_srp OUT NOCOPY srp_group_tbl_type)
939 IS
940 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_salesrep';
941 l_api_version CONSTANT NUMBER := 1.0;
942
943 l_counter NUMBER(15) := 0;
944 l_group group_rec_type;
945 l_descendant_group group_tbl_type;
946 l_mgr_flag VARCHAR2(1);
947
948 CURSOR members_csr(p_group_id NUMBER, p_start_date DATE, p_end_date DATE) IS
949 SELECT
950 salesrep_id,
951 greatest(start_date_active, p_start_date) start_date,
952 least(nvl(end_date_active, p_end_date), nvl(p_end_date, end_date_active)) end_date
953 FROM cn_srp_comp_groups_v
954 WHERE comp_group_id = p_group_id
955 AND org_id = p_org_id
956 AND (end_date_active IS NULL OR p_start_date <= end_date_active)
957 AND (p_end_date IS NULL OR p_end_date >= start_date_active);
958
959 CURSOR mgr_check IS
960 SELECT manager_flag
961 FROM cn_srp_comp_groups_v
962 WHERE comp_group_id = p_srp.group_id
963 AND org_id = p_org_id
964 AND salesrep_id = p_srp.salesrep_id
965 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
966 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
967 BEGIN
968 -- Standard call to check for call compatibility.
969 IF NOT FND_API.Compatible_API_Call
970 (l_api_version ,
971 p_api_version ,
972 l_api_name ,
973 G_PKG_NAME )
974 THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
979 IF FND_API.to_Boolean( p_init_msg_list ) THEN
976 END IF;
977
978 -- Initialize message list if p_init_msg_list is set to TRUE.
980 FND_MSG_PUB.initialize;
981 END IF;
982
983 -- Initialize API return status to success
984 x_return_status := FND_API.G_RET_STS_SUCCESS;
985 -- API body
986
987 -- if p_srp.salesrep_id is a manager, get the other members of his own group
988 OPEN mgr_check;
989 FETCH mgr_check INTO l_mgr_flag;
990 CLOSE mgr_check;
991
992 IF (l_mgr_flag = 'Y') THEN
993 FOR eachmem IN members_csr(p_srp.group_id, p_srp.start_date, p_srp.end_date) LOOP
994 IF eachmem.salesrep_id <> p_srp.salesrep_id THEN
995 x_srp(l_counter).salesrep_id := eachmem.salesrep_id;
996 x_srp(l_counter).group_id := p_srp.group_id;
997 x_srp(l_counter).start_date := eachmem.start_date;
998 x_srp(l_counter).end_date := eachmem.end_date;
999 x_srp(l_counter).level := 0;
1000
1001 l_counter := l_counter + 1;
1002 END IF;
1003 END LOOP;
1004 END IF;
1005
1006 -- initialize l_group
1007 l_group.group_id := p_srp.group_id;
1008 l_group.start_date := p_srp.start_date;
1009 l_group.end_date := p_srp.end_date;
1010
1011 get_descendant_group
1012 ( p_api_version => 1.0,
1013 x_return_status => x_return_status,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data,
1016 p_group => l_group,
1017 x_group => l_descendant_group);
1018
1019 IF x_return_status = FND_API.g_ret_sts_error THEN
1020 RAISE FND_API.g_exc_error;
1021 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1022 RAISE FND_API.g_exc_unexpected_error;
1023 END IF;
1024
1025 IF (l_descendant_group.COUNT > 0) THEN
1026 FOR eachgroup IN l_descendant_group.first .. l_descendant_group.last LOOP
1027 FOR eachsrp IN members_csr(l_descendant_group(eachgroup).group_id, l_descendant_group(eachgroup).start_date,l_descendant_group(eachgroup).end_date) LOOP
1028 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
1029 x_srp(l_counter).group_id := l_descendant_group(eachgroup).group_id;
1030 x_srp(l_counter).start_date := eachsrp.start_date;
1031 x_srp(l_counter).end_date := eachsrp.end_date;
1032 x_srp(l_counter).level := l_descendant_group(eachgroup).level;
1033
1034 l_counter := l_counter + 1;
1035 END LOOP; -- end of eachsrp
1036 END LOOP; -- end of eachgroup
1037 END IF;
1038
1039 -- End of API body.
1040
1041 -- Standard call to get message count and if count is 1, get message info.
1042 FND_MSG_PUB.Count_And_Get
1043 (p_count => x_msg_count ,
1044 p_data => x_msg_data );
1045 EXCEPTION
1046 WHEN FND_API.G_EXC_ERROR THEN
1047 x_return_status := FND_API.G_RET_STS_ERROR ;
1048 FND_MSG_PUB.Count_And_Get
1049 (p_count => x_msg_count ,
1050 p_data => x_msg_data );
1051 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1053 FND_MSG_PUB.Count_And_Get
1054 (p_count => x_msg_count ,
1055 p_data => x_msg_data );
1056 WHEN OTHERS THEN
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1058 IF FND_MSG_PUB.Check_Msg_Level
1059 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1060 THEN
1061 FND_MSG_PUB.Add_Exc_Msg
1062 (G_PKG_NAME ,
1063 l_api_name );
1064 END IF;
1065 FND_MSG_PUB.Count_And_Get
1066 (p_count => x_msg_count ,
1067 p_data => x_msg_data );
1068
1069 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1070 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1071 'cn.plsql.cn_rollup_pvt.get_descendant_salesrep.exception',
1072 sqlerrm);
1073 end if;
1074
1075 END get_descendant_salesrep;
1076
1077 -- API name : get_descendant_salesrep
1078 -- Type : Private.
1079 -- Pre-reqs : None
1080 --
1081 -- Parameters :
1082 -- IN : p_api_version NUMBER Require
1083 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
1084 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
1085 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
1086 -- OUT : x_return_status VARCHAR2(1)
1087 -- x_msg_count NUMBER
1088 -- x_msg_data VARCHAR2(2000)
1089 --
1090 -- Version : Current version 1.0
1091 -- Initial version 1.0
1092 --
1093 -- Notes :
1094 --
1095 -- End of comments
1096
1097 PROCEDURE get_descendant_salesrep
1098 ( p_api_version IN NUMBER,
1099 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1100 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1101 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1102 x_return_status OUT NOCOPY VARCHAR2,
1103 x_msg_count OUT NOCOPY NUMBER,
1104 x_msg_data OUT NOCOPY VARCHAR2,
1105 p_org_id IN NUMBER,
1106 p_srp IN srp_rec_type,
1107 x_srp OUT NOCOPY srp_tbl_type)
1108 IS
1109 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_salesrep';
1110 l_api_version CONSTANT NUMBER := 1.0;
1111
1112 l_counter NUMBER(15);
1113 l_temp NUMBER(15);
1114
1115 CURSOR groups_csr IS
1116 SELECT DISTINCT comp_group_id
1117 FROM cn_srp_comp_groups_v
1118 WHERE salesrep_id = p_srp.salesrep_id
1119 AND org_id = p_org_id
1120 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
1121 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
1122
1123 CURSOR members_csr (p_group_id NUMBER) IS
1124 SELECT DISTINCT salesrep_id
1125 FROM cn_srp_comp_groups_v
1126 WHERE comp_group_id = p_group_id
1127 AND salesrep_id <> p_srp.salesrep_id
1128 AND org_id = p_org_id
1129 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
1130 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
1131
1132 CURSOR descendant_csr(p_group_id NUMBER) IS
1133 SELECT DISTINCT salesrep_id
1134 FROM cn_srp_comp_groups_v srp,
1135 cn_groups_denorm_v hier
1136 WHERE srp.comp_group_id = hier.group_id
1137 AND srp.org_id = p_org_id
1138 AND hier.parent_group_id = p_group_id
1139 AND (p_srp.end_date IS NULL OR hier.start_date_active <= p_srp.end_date)
1140 AND (hier.end_date_active IS NULL OR hier.end_date_active >= p_srp.start_date)
1141 AND (p_srp.end_date IS NULL OR srp.start_date_active <= p_srp.end_date)
1142 AND (srp.end_date_active IS NULL OR srp.end_date_active >= p_srp.start_date);
1143 BEGIN
1144 -- Standard call to check for call compatibility.
1145 IF NOT FND_API.Compatible_API_Call
1146 (l_api_version ,
1147 p_api_version ,
1148 l_api_name ,
1149 G_PKG_NAME )
1150 THEN
1151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1152 END IF;
1153
1154 -- Initialize message list if p_init_msg_list is set to TRUE.
1155 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1156 FND_MSG_PUB.initialize;
1157 END IF;
1158
1159 -- Initialize API return status to success
1160 x_return_status := FND_API.G_RET_STS_SUCCESS;
1161 -- API body
1162
1163 l_counter := 0;
1164
1165 FOR eachgroup IN groups_csr LOOP
1166 SELECT COUNT(*)
1167 INTO l_temp
1168 FROM cn_srp_comp_groups_v
1169 WHERE salesrep_id = p_srp.salesrep_id
1170 AND org_id = p_org_id
1171 AND comp_group_id = eachgroup.comp_group_id
1172 AND manager_flag = 'Y'
1173 AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
1174 AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
1175
1176 IF l_temp > 0 THEN
1177 FOR eachsrp IN members_csr(eachgroup.comp_group_id) LOOP
1178 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
1179
1180 l_counter := l_counter + 1;
1181 END LOOP; -- end of eachsrp
1182 END IF; -- end of l_temp check
1183
1184 FOR eachsrp IN descendant_csr(eachgroup.comp_group_id) LOOP
1185 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
1186
1187 l_counter := l_counter + 1;
1188 END LOOP; -- End of eachsrp
1189 END LOOP; -- End of eachgroup
1190 -- End of API body.
1191
1192 -- Standard call to get message count and if count is 1, get message info.
1193 FND_MSG_PUB.Count_And_Get
1194 (p_count => x_msg_count ,
1195 p_data => x_msg_data );
1196 EXCEPTION
1197 WHEN FND_API.G_EXC_ERROR THEN
1198 x_return_status := FND_API.G_RET_STS_ERROR ;
1199 FND_MSG_PUB.Count_And_Get
1200 (p_count => x_msg_count ,
1201 p_data => x_msg_data );
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1204 FND_MSG_PUB.Count_And_Get
1205 (p_count => x_msg_count ,
1206 p_data => x_msg_data );
1207 WHEN OTHERS THEN
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1209 IF FND_MSG_PUB.Check_Msg_Level
1210 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1211 THEN
1212 FND_MSG_PUB.Add_Exc_Msg
1213 (G_PKG_NAME ,
1214 l_api_name );
1215 END IF;
1216 FND_MSG_PUB.Count_And_Get
1217 (p_count => x_msg_count ,
1218 p_data => x_msg_data );
1219
1220 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1221 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1222 'cn.plsql.cn_rollup_pvt.get_descendant_salesrep.exception',
1223 sqlerrm);
1224 end if;
1225
1226 END get_descendant_salesrep;
1227
1228 END cn_rollup_pvt;