[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_HIER_PROC_PVT
Source
1 PACKAGE body cn_srp_hier_proc_pvt AS
2 /*$Header: cnvsrhrb.pls 115.16 2002/11/21 21:19:11 hlchen ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='cn_srp_hier_proc_pvt';
5 G_LAST_UPDATE_DATE DATE := Sysdate;
6 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
7 G_CREATION_DATE DATE := Sysdate;
8 G_CREATED_BY NUMBER := fnd_global.user_id;
9 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
10
11 --{{{ find_end_date
12 FUNCTION find_end_date (p_date1 date,
13 p_date2 date) RETURN date IS
14 BEGIN
15 IF p_date1 IS NULL THEN
16 RETURN p_date2;
17 ELSIF p_date2 IS NULL THEN
18 RETURN p_date1;
19 ELSE
20 RETURN least(p_date1 , p_date2);
21 END IF;
22 END find_end_date;
23 --}}}
24
25 --{{{ get_ancestor_group
26 -- API name : get_ancestor_group
27 -- Type : Private.
28 -- Pre-reqs : None
29 -- Usage :
30 --
31 -- Desc :
32 --
33 --
34 --
35 -- Parameters :
36 -- IN : p_api_version IN NUMBER Required
37 -- p_init_msg_list IN VARCHAR2 Optional
38 -- Default = FND_API.G_FALSE
39 -- p_commit IN VARCHAR2 Optional
40 -- Default = FND_API.G_FALSE
41 -- p_validation_level IN NUMBER Optional
42 -- Default = FND_API.G_VALID_LEVEL_FULL
43 -- p_group IN input_group_type Required
44 -- OUT : x_return_status OUT VARCHAR2(1)
45 -- x_msg_count OUT NUMBER
46 -- x_msg_data OUT VARCHAR2(2000)
47 -- x_group OUT group_tbl_type
48 -- Version : Current version 1.0
49 -- Initial version 1.0
50 --
51 -- Notes :
52 --
53 -- End of comments
54
55 PROCEDURE get_ancestor_group
56 ( p_api_version IN NUMBER,
57 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
58 p_commit IN VARCHAR2 := FND_API.G_FALSE,
59 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2,
63 p_group IN input_group_type,
64 x_group IN OUT NOCOPY group_tbl_type,
65 p_level IN number := 0) IS
66
67 l_api_name CONSTANT VARCHAR2(30) := 'get_ancestor_group';
68 l_api_version CONSTANT NUMBER := 1.0;
69
70 l_counter NUMBER(15) := 0;
71 l_group input_group_type;
72
73 CURSOR groups_csr IS
74 SELECT parent_comp_group_id parent_group_id,
75 trunc(start_date_active) start_date, trunc(end_date_active) end_date
76 FROM cn_qm_group_hier
77 WHERE comp_group_id = p_group.group_id;
78
79 BEGIN
80 -- Standard Start of API savepoint
81 SAVEPOINT get_ancestor_group;
82
83 -- Standard call to check for call compatibility.
84 IF NOT FND_API.Compatible_API_Call
85 (l_api_version ,
86 p_api_version ,
87 l_api_name ,
88 G_PKG_NAME )
89 THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_Boolean( p_init_msg_list ) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97
98 -- Initialize API return status to success
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100 -- API body
101 l_counter := x_group.count;
102 FOR eachgroup IN groups_csr LOOP
103
104 IF (eachgroup.start_date <= p_group.effective_date AND
105 (eachgroup.end_date IS NULL
106 OR eachgroup.end_date >= p_group.effective_date)) THEN
107
108 l_counter := x_group.count + 1;
109 x_group(l_counter).group_id := eachgroup.parent_group_id;
110 x_group(l_counter).start_date := eachgroup.start_date;
111 x_group(l_counter).end_date := eachgroup.end_date;
112 x_group(l_counter).hier_level := p_level+1;
113
114 l_group.group_id := eachgroup.parent_group_id;
115 l_group.effective_date := p_group.effective_date;
116
117 get_ancestor_group
118 ( p_api_version => 1.0,
119 x_return_status => x_return_status,
120 x_msg_count => x_msg_count,
121 x_msg_data => x_msg_data,
122 p_group => l_group,
123 x_group => x_group,
124 p_level => p_level + 1);
125
126 END IF;
127
128 END LOOP;
129
130 -- End of API body.
131
132 -- Standard check of p_commit.
133 IF FND_API.To_Boolean( p_commit ) THEN
134 COMMIT WORK;
135 END IF;
136
137 -- Standard call to get message count and if count is 1, get message info.
138 FND_MSG_PUB.Count_And_Get
139 (p_count => x_msg_count ,
140 p_data => x_msg_data );
141
142 EXCEPTION
143 WHEN FND_API.G_EXC_ERROR THEN
144 ROLLBACK TO get_ancestor_group;
145 x_return_status := FND_API.G_RET_STS_ERROR ;
146 FND_MSG_PUB.Count_And_Get
147 (p_count => x_msg_count ,
148 p_data => x_msg_data );
149
150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
151 ROLLBACK TO get_ancestor_group;
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
153 FND_MSG_PUB.Count_And_Get
154 (p_count => x_msg_count ,
155 p_data => x_msg_data );
156
157 WHEN OTHERS THEN
158 ROLLBACK TO get_ancestor_group;
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
160 IF FND_MSG_PUB.Check_Msg_Level
161 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
162 THEN
163 FND_MSG_PUB.Add_Exc_Msg
164 (G_PKG_NAME ,
165 l_api_name );
166 END IF;
167 FND_MSG_PUB.Count_And_Get
168 (p_count => x_msg_count ,
169 p_data => x_msg_data );
170
171 END get_ancestor_group;
172 --}}}
173
174 --{{{ get_descendant_group
175 -- API name : get_descendant_group
176 -- Type : Private.
177 -- Pre-reqs : None
178 -- Usage :
179 --
180 -- Desc :
181 --
182 --
183 --
184 -- Parameters :
185 -- IN : p_api_version IN NUMBER Required
186 -- p_init_msg_list IN VARCHAR2 Optional
187 -- Default = FND_API.G_FALSE
188 -- p_commit IN VARCHAR2 Optional
189 -- Default = FND_API.G_FALSE
190 -- p_validation_level IN NUMBER Optional
191 -- Default = FND_API.G_VALID_LEVEL_FULL
192 -- p_group IN input_group_type Required
193 -- OUT : x_return_status OUT VARCHAR2(1)
194 -- x_msg_count OUT NUMBER
195 -- x_msg_data OUT VARCHAR2(2000)
196 -- x_group OUT group_tbl_type
197 -- Version : Current version 1.0
198 -- Initial version 1.0
199 --
200 -- Notes :
201 --
202 -- End of comments
203
204 PROCEDURE get_descendant_group
205 ( p_api_version IN NUMBER,
206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
207 p_commit IN VARCHAR2 := FND_API.G_FALSE,
208 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 p_group IN input_group_type,
213 x_group IN OUT NOCOPY group_tbl_type,
214 p_level IN number) IS
215
216 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_group';
217 l_api_version CONSTANT NUMBER := 1.0;
218
219 l_counter NUMBER(15) := 0;
220 l_group input_group_type;
221
222 CURSOR groups_csr IS
223 SELECT comp_group_id group_id,
224 trunc(start_date_active) start_date,
225 trunc(end_date_active) end_date
226 FROM cn_qm_group_hier
227 WHERE parent_comp_group_id = p_group.group_id;
228
229 BEGIN
230 -- Standard Start of API savepoint
231 SAVEPOINT get_descendant_group;
232
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.Compatible_API_Call
235 (l_api_version ,
236 p_api_version ,
237 l_api_name ,
238 G_PKG_NAME )
239 THEN
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242
243 -- Initialize message list if p_init_msg_list is set to TRUE.
244 IF FND_API.to_Boolean( p_init_msg_list ) THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247
248 -- Initialize API return status to success
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250 -- API body
251 l_counter := x_group.count;
252 FOR eachgroup IN groups_csr LOOP
253
254 IF (eachgroup.start_date <= p_group.effective_date AND
255 (eachgroup.end_date IS NULL
256 OR eachgroup.end_date >= p_group.effective_date)) THEN
257
258 l_counter := x_group.count + 1;
259 x_group(l_counter).group_id := eachgroup.group_id;
260 x_group(l_counter).start_date := eachgroup.start_date;
261 x_group(l_counter).end_date := eachgroup.end_date;
262 x_group(l_counter).hier_level := p_level + 1;
263
264 l_group.group_id := eachgroup.group_id;
265 l_group.effective_date := p_group.effective_date;
266
267 get_descendant_group
268 ( p_api_version => 1.0,
269 x_return_status => x_return_status,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data,
272 p_group => l_group,
273 x_group => x_group,
274 p_level => p_level + 1);
275
276 END IF;
277
278 END LOOP;
279
280 -- End of API body.
281
282 -- Standard check of p_commit.
283 IF FND_API.To_Boolean( p_commit ) THEN
284 COMMIT WORK;
285 END IF;
286
287 -- Standard call to get message count and if count is 1, get message info.
288 FND_MSG_PUB.Count_And_Get
289 (p_count => x_msg_count ,
290 p_data => x_msg_data );
291
292 EXCEPTION
293 WHEN FND_API.G_EXC_ERROR THEN
294 ROLLBACK TO get_descendant_group;
295 x_return_status := FND_API.G_RET_STS_ERROR ;
296 FND_MSG_PUB.Count_And_Get
297 (p_count => x_msg_count ,
298 p_data => x_msg_data );
299
300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
301 ROLLBACK TO get_descendant_group;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
303 FND_MSG_PUB.Count_And_Get
304 (p_count => x_msg_count ,
305 p_data => x_msg_data );
306
307 WHEN OTHERS THEN
308 ROLLBACK TO get_descendant_group;
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
310 IF FND_MSG_PUB.Check_Msg_Level
311 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
312 THEN
313 FND_MSG_PUB.Add_Exc_Msg
314 (G_PKG_NAME ,
315 l_api_name );
316 END IF;
317 FND_MSG_PUB.Count_And_Get
318 (p_count => x_msg_count ,
319 p_data => x_msg_data );
320
321 END get_descendant_group;
322 --}}}
323
324 --{{{ get_managers
325 -- Start of comments
326 -- API name : Get_Managers
327 -- Type : Private.
328 -- Function :
329 -- Pre-reqs : None.
330 -- Parameters :
331 -- IN : p_api_version IN NUMBER Required
332 -- p_init_msg_list IN VARCHAR2 Optional
333 -- Default = FND_API.G_FALSE
334 -- p_commit IN VARCHAR2 Optional
335 -- Default = FND_API.G_FALSE
336 -- p_validation_level IN NUMBER Optional
337 -- Default = FND_API.G_VALID_LEVEL_FULL
338 -- p_salesrep_id IN NUMBER Required
339 -- p_comp_group_id IN NUMBER Required
340 -- p_effective_date IN DATE Required
341 -- OUT : x_return_status OUT VARCHAR2(1)
342 -- x_msg_count OUT NUMBER
343 -- x_msg_data OUT VARCHAR2(2000)
344 -- x_salesrep_tbl OUT srp_tbl_type
345 -- x_returned_rows OUT INTEGER
346 -- Version : Current version 1.0
347 -- Initial version 1.0
348 --
349 -- Notes : Note text
350 --
351 -- End of comments
352 PROCEDURE Get_Managers
353 (p_api_version IN NUMBER ,
354 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
355 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
356 p_validation_level IN NUMBER :=
357 FND_API.G_VALID_LEVEL_FULL ,
358 p_salesrep_id IN number ,
359 p_comp_group_id IN number ,
360 p_effective_date IN date ,
361 x_return_status OUT NOCOPY VARCHAR2 ,
362 x_msg_count OUT NOCOPY NUMBER ,
363 x_msg_data OUT NOCOPY VARCHAR2 ,
364 x_salesrep_tbl OUT NOCOPY srp_role_group_tbl_type ,
365 x_returned_rows OUT NOCOPY integer ) IS
366
367 l_api_name CONSTANT VARCHAR2(30) := 'Get_Managers';
368 l_api_version CONSTANT NUMBER := 1.0;
369 l_comp_group_id number := 0;
370 l_count number := 0;
371
372 CURSOR l_srp_role_id_csr IS
373 SELECT srp_role_id, comp_group_id, trunc(start_date_active) start_date_active,
374 trunc(end_date_active) end_date_active
375 FROM cn_qm_srp_groups
376 WHERE srp_id = p_salesrep_id
377 AND manager_flag = 'N';
378
379 CURSOR l_mgr_role_id_csr IS
380 SELECT manager_srp_id, comp_group_id, trunc(start_date_active) start_date_active,
381 trunc(end_date_active) end_date_active, role_id
382 FROM cn_qm_mgr_groups
383 WHERE comp_group_id = l_comp_group_id;
384
385 CURSOR l_parent_grp_srp_csr IS
386 SELECT srp_id, comp_group_id, trunc(start_date_active) start_date_active,
387 trunc(end_date_active) end_date_active, role_id
388 FROM cn_qm_srp_groups
389 WHERE comp_group_id = l_comp_group_id;
390
391 CURSOR l_mgr_group_csr is
392 SELECT 1
393 FROM cn_qm_mgr_groups
394 WHERE manager_srp_id = p_salesrep_id;
395
396 CURSOR l_parent_group_csr is
397 SELECT parent_comp_group_id
398 FROM cn_qm_group_hier
399 WHERE comp_group_id = l_comp_group_id
400 AND trunc(start_date_active) <= p_effective_date
401 AND (trunc(end_date_active) >= p_effective_date
402 OR end_date_active IS NULL);
403
404 BEGIN
405 -- Standard Start of API savepoint
406 SAVEPOINT get_managers_pvt;
407 -- Standard call to check for call compatibility.
408 IF NOT FND_API.Compatible_API_Call
409 (l_api_version ,
410 p_api_version ,
411 l_api_name ,
412 G_PKG_NAME )
413 THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 END IF;
416 -- Initialize message list if p_init_msg_list is set to TRUE.
417 IF FND_API.to_Boolean( p_init_msg_list ) THEN
418 FND_MSG_PUB.initialize;
419 END IF;
420 -- Initialize API return status to success
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422 -- API body
423 -- select the group_member_ids for this srp
424 FOR eachrow in l_srp_role_id_csr LOOP
425 -- filter the rows that contain the effective date
426 IF (eachrow.start_date_active <= p_effective_date and
427 (eachrow.end_date_active >= p_effective_date or
428 eachrow.end_date_active is null)) THEN
429 -- get the manager group member ids for each row
430 -- get the srp ids for each of those groups
431 l_comp_group_id := eachrow.comp_group_id;
432 FOR eachassgn in l_mgr_role_id_csr LOOP
433 IF (eachassgn.start_date_active <= p_effective_date and
434 (eachassgn.end_date_active >= p_effective_date or
435 eachassgn.end_date_active is null)) THEN
436 l_count := l_count + 1;
437 x_salesrep_tbl(l_count).salesrep_id := eachassgn.manager_srp_id;
438 x_salesrep_tbl(l_count).group_id := eachassgn.comp_group_id;
439 x_salesrep_tbl(l_count).role_id := eachassgn.role_id;
440 x_salesrep_tbl(l_count).start_date := eachassgn.start_date_active;
441 x_salesrep_tbl(l_count).end_date := eachassgn.end_date_active;
442 END IF;
443 END LOOP;
444 END IF;
445 END LOOP;
446
447 -- we need to handle the fact that this person could be a manager of the
448 -- group; which means that we have to get all the salesreps and managers
449 -- from the parent group
450 IF (l_count = 0) THEN
451 FOR eachrow in l_mgr_group_csr LOOP
452 l_count := l_count + 1;
453 END LOOP;
454 IF (l_count > 0) THEN
455 l_count := 0;
456 l_comp_group_id := p_comp_group_id;
457 -- this means that the salesrep in a manager in the group
458 OPEN l_parent_group_csr;
459 FETCH l_parent_group_csr INTO l_comp_group_id;
460 CLOSE l_parent_group_csr;
461
462 --make sure that we do not return a wrong row
463 --with the person as manager FOR himself
464 IF (l_comp_group_id = p_comp_group_id) THEN
465 RETURN;
466 END IF;
467
468 -- salesreps in parent groups do not count as managers
469 -- so this section is commented out
470 --FOR eachsrpassgn IN l_parent_grp_srp_csr LOOP
471 -- l_count := l_count + 1;
472 -- x_salesrep_tbl(l_count) := eachsrpassgn.srp_id;
473 --END LOOP;
474 FOR eachmgrassgn IN l_mgr_role_id_csr LOOP
475 l_count := l_count + 1;
476 x_salesrep_tbl(l_count).salesrep_id := eachmgrassgn.manager_srp_id;
477 x_salesrep_tbl(l_count).group_id := eachmgrassgn.comp_group_id;
478 x_salesrep_tbl(l_count).role_id := eachmgrassgn.role_id;
479 x_salesrep_tbl(l_count).start_date := eachmgrassgn.start_date_active;
480 x_salesrep_tbl(l_count).end_date := eachmgrassgn.end_date_active;
481 END LOOP;
482 END IF;
483 END IF;
484
485 -- End of API body.
486 -- Standard check of p_commit.
487 IF FND_API.To_Boolean( p_commit ) THEN
488 COMMIT WORK;
489 END IF;
490 -- Standard call to get message count and if count is 1, get message info.
491 FND_MSG_PUB.Count_And_Get
492 (p_count => x_msg_count ,
493 p_data => x_msg_data ,
494 p_encoded => FND_API.G_FALSE );
495 EXCEPTION
496 WHEN FND_API.G_EXC_ERROR THEN
497 ROLLBACK TO get_managers_pvt;
498 x_return_status := FND_API.G_RET_STS_ERROR ;
499 FND_MSG_PUB.Count_And_Get
500 (p_count => x_msg_count ,
501 p_data => x_msg_data ,
502 p_encoded => FND_API.G_FALSE );
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 ROLLBACK TO get_managers_pvt;
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
506 FND_MSG_PUB.Count_And_Get
507 (p_count => x_msg_count ,
508 p_data => x_msg_data ,
509 p_encoded => FND_API.G_FALSE );
510 WHEN OTHERS THEN
511 ROLLBACK TO get_managers_pvt;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
513 IF FND_MSG_PUB.Check_Msg_Level
514 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
515 THEN
516 FND_MSG_PUB.Add_Exc_Msg
517 (G_PKG_NAME ,
518 l_api_name );
519 END IF;
520 FND_MSG_PUB.Count_And_Get
521 (p_count => x_msg_count ,
522 p_data => x_msg_data ,
523 p_encoded => FND_API.G_FALSE );
524 END Get_Managers;
525 --}}}
526
527 --{{{ get_salesreps
528 -- Start of comments
529 -- API name : Get_Salesreps
530 -- Type : Private.
531 -- Function :
532 -- Pre-reqs : None.
533 -- Parameters :
534 -- IN : p_api_version IN NUMBER Required
535 -- p_init_msg_list IN VARCHAR2 Optional
536 -- Default = FND_API.G_FALSE
537 -- p_commit IN VARCHAR2 Optional
538 -- Default = FND_API.G_FALSE
539 -- p_validation_level IN NUMBER Optional
540 -- Default = FND_API.G_VALID_LEVEL_FULL
541 -- p_salesrep_id IN NUMBER Required
542 -- p_comp_group_id IN NUMBER Required
543 -- p_effective_date IN DATE Required
544 -- OUT : x_return_status OUT VARCHAR2(1)
545 -- x_msg_count OUT NUMBER
546 -- x_msg_data OUT VARCHAR2(2000)
547 -- x_salesrep_tbl OUT srp_tbl_type
548 -- x_returned_rows OUT INTEGER
549 -- Version : Current version 1.0
550 -- Initial version 1.0
551 --
552 -- Notes : Note text
553 --
554 -- End of comments
555 PROCEDURE Get_Salesreps
556 (p_api_version IN NUMBER ,
557 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
558 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
559 p_validation_level IN NUMBER :=
560 FND_API.G_VALID_LEVEL_FULL ,
561 p_salesrep_id IN number ,
562 p_comp_group_id IN number ,
563 p_effective_date IN date ,
564 p_return_current IN varchar2 := 'N' ,
565 x_return_status OUT NOCOPY VARCHAR2 ,
566 x_msg_count OUT NOCOPY NUMBER ,
567 x_msg_data OUT NOCOPY VARCHAR2 ,
568 x_salesrep_tbl OUT NOCOPY srp_role_group_tbl_type ,
569 x_returned_rows OUT NOCOPY integer ) IS
570
571 l_api_name CONSTANT VARCHAR2(30) := 'Get_Salesreps';
572 l_api_version CONSTANT NUMBER := 1.0;
573 l_comp_group_id number := 0;
574 l_count number := 0;
575
576 CURSOR l_mgr_role_id_csr IS
577 SELECT srp_role_id, comp_group_id, trunc(start_date_active) start_date_active,
578 trunc(end_date_active) end_date_active, role_id, manager_srp_id
579 FROM cn_qm_mgr_groups
580 WHERE manager_srp_id = p_salesrep_id
581 AND comp_group_id = p_comp_group_id;
582
583 CURSOR l_srp_role_id_csr IS
584 SELECT srp_id, comp_group_id, trunc(start_date_active) start_date_active,
585 trunc(end_date_active) end_date_active, role_id
586 FROM cn_qm_srp_groups
587 WHERE comp_group_id = l_comp_group_id;
588
589 BEGIN
590 -- Standard Start of API savepoint
591 SAVEPOINT get_salesreps_pvt;
592 -- Standard call to check for call compatibility.
593 IF NOT FND_API.Compatible_API_Call
594 (l_api_version ,
595 p_api_version ,
596 l_api_name ,
597 G_PKG_NAME )
598 THEN
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 END IF;
601 -- Initialize message list if p_init_msg_list is set to TRUE.
602 IF FND_API.to_Boolean( p_init_msg_list ) THEN
603 FND_MSG_PUB.initialize;
604 END IF;
605 -- Initialize API return status to success
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607 -- API body
608 -- select the group_member_ids for this srp
609 FOR eachrow in l_mgr_role_id_csr LOOP
610 -- filter the rows that contain the effective date
611 IF (eachrow.start_date_active <= p_effective_date and
612 (eachrow.end_date_active >= p_effective_date or
613 eachrow.end_date_active is null)) THEN
614 -- get the manager group member ids for each row
615 -- get the srp ids for each of those groups
616 IF (p_return_current = 'Y') THEN
617 l_count := l_count + 1;
618 x_salesrep_tbl(l_count).salesrep_id := p_salesrep_id;
619 x_salesrep_tbl(l_count).group_id := eachrow.comp_group_id;
620 x_salesrep_tbl(l_count).role_id := eachrow.role_id;
621 x_salesrep_tbl(l_count).start_date := eachrow.start_date_active;
622 x_salesrep_tbl(l_count).end_date := eachrow.end_date_active;
623 END IF;
624 l_comp_group_id := p_comp_group_id;
625 FOR eachassgn in l_srp_role_id_csr LOOP
626 IF (eachassgn.start_date_active <= p_effective_date and
627 (eachassgn.end_date_active >= p_effective_date or
628 eachassgn.end_date_active is null)) THEN
629 l_count := l_count + 1;
630 x_salesrep_tbl(l_count).salesrep_id := eachassgn.srp_id;
631 x_salesrep_tbl(l_count).group_id := eachassgn.comp_group_id;
632 x_salesrep_tbl(l_count).role_id := eachassgn.role_id;
633 x_salesrep_tbl(l_count).start_date := eachassgn.start_date_active;
634 x_salesrep_tbl(l_count).end_date := eachassgn.end_date_active;
635 x_salesrep_tbl(l_count).mgr_srp_id := p_salesrep_id;
636 END IF;
637 END LOOP;
638 END IF;
639 END LOOP;
640
641 -- End of API body.
642 -- Standard check of p_commit.
643 IF FND_API.To_Boolean( p_commit ) THEN
644 COMMIT WORK;
645 END IF;
646 -- Standard call to get message count and if count is 1, get message info.
647 FND_MSG_PUB.Count_And_Get
648 (p_count => x_msg_count ,
649 p_data => x_msg_data ,
650 p_encoded => FND_API.G_FALSE );
651 EXCEPTION
652 WHEN FND_API.G_EXC_ERROR THEN
653 ROLLBACK TO get_salesreps_pvt;
654 x_return_status := FND_API.G_RET_STS_ERROR ;
655 FND_MSG_PUB.Count_And_Get
656 (p_count => x_msg_count ,
657 p_data => x_msg_data ,
658 p_encoded => FND_API.G_FALSE );
659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
660 ROLLBACK TO get_salesreps_pvt;
661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
662 FND_MSG_PUB.Count_And_Get
663 (p_count => x_msg_count ,
664 p_data => x_msg_data ,
665 p_encoded => FND_API.G_FALSE );
666 WHEN OTHERS THEN
667 ROLLBACK TO get_salesreps_pvt;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
669 IF FND_MSG_PUB.Check_Msg_Level
670 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
671 THEN
672 FND_MSG_PUB.Add_Exc_Msg
673 (G_PKG_NAME ,
674 l_api_name );
675 END IF;
676 FND_MSG_PUB.Count_And_Get
677 (p_count => x_msg_count ,
678 p_data => x_msg_data ,
679 p_encoded => FND_API.G_FALSE );
680 END Get_Salesreps;
681 --}}}
682
683 --{{{ get_ancestor_salesreps
684
685 -- Start of comments
686 -- API name : Get_Ancestor_Salesreps
687 -- Type : Private.
688 -- Function :
689 -- Pre-reqs : None.
690 -- Parameters :
691 -- IN : p_api_version IN NUMBER Required
692 -- p_init_msg_list IN VARCHAR2 Optional
693 -- Default = FND_API.G_FALSE
694 -- p_commit IN VARCHAR2 Optional
695 -- Default = FND_API.G_FALSE
696 -- p_validation_level IN NUMBER Optional
697 -- Default = FND_API.G_VALID_LEVEL_FULL
698 -- p_srp IN srp_group_rec_type Required
699 -- p_effective_date IN DATE Required
700 -- OUT : x_return_status OUT VARCHAR2(1)
701 -- x_msg_count OUT NUMBER
702 -- x_msg_data OUT VARCHAR2(2000)
703 -- x_srp OUT srp_role_group_tbl_type
704 -- x_returned_rows OUT INTEGER
705 -- Version : Current version 1.0
706 -- Initial version 1.0
707 --
708 -- Notes : Note text
709 --
710 -- End of comments
711 PROCEDURE Get_Ancestor_Salesreps
712 (p_api_version IN NUMBER,
713 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
714 p_commit IN VARCHAR2 := FND_API.G_FALSE,
715 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
716 p_srp IN srp_group_rec_type,
717 x_return_status OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_msg_data OUT NOCOPY VARCHAR2,
720 x_srp OUT NOCOPY srp_role_group_tbl_type,
721 x_returned_rows OUT NOCOPY number) IS
722
723 l_api_name CONSTANT VARCHAR2(30) := 'get_ancestor_salesreps';
724 l_api_version CONSTANT NUMBER := 1.0;
725
726 l_counter NUMBER(15);
727 l_group input_group_type;
728 l_ancestor_group group_tbl_type;
729 l_srp_tbl srp_role_group_tbl_type;
730 l_returned_rows number;
731
732 CURSOR managers_csr(p_group_id NUMBER) IS
733 SELECT manager_srp_id salesrep_id, role_id ,comp_group_id,
734 trunc(start_date_active) start_date_active,
735 trunc(end_date_active) end_date_active
736 FROM cn_qm_mgr_groups
737 WHERE comp_group_id = p_group_id
738 AND manager_srp_id <> p_srp.salesrep_id
739 AND manager_flag = 'Y';
740
741 CURSOR members_csr(p_group_id NUMBER) IS
742 SELECT srp_id salesrep_id, role_id, comp_group_id,
743 trunc(start_date_active) start_date, trunc(end_date_active) end_date
744 FROM cn_qm_mgr_srp_groups
745 WHERE comp_group_id = p_group_id;
746
747 BEGIN
748 -- Standard Start of API savepoint
749 SAVEPOINT get_ancestor_salesrep;
750
751 -- Standard call to check for call compatibility.
752 IF NOT FND_API.Compatible_API_Call
753 (l_api_version ,
754 p_api_version ,
755 l_api_name ,
756 G_PKG_NAME )
757 THEN
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759 END IF;
760
761 -- Initialize message list if p_init_msg_list is set to TRUE.
762 IF FND_API.to_Boolean( p_init_msg_list ) THEN
763 FND_MSG_PUB.initialize;
764 END IF;
765
766 -- Initialize API return status to success
767 x_return_status := FND_API.G_RET_STS_SUCCESS;
768 -- API body
769
770 l_counter := 0;
771
772 -- find all managers in this group
773
774 FOR eachmgr IN managers_csr(p_srp.group_id) LOOP
775
776 IF (eachmgr.start_date_active <= p_srp.effective_date AND
777 (eachmgr.end_date_active IS NULL
778 OR eachmgr.end_date_active >= p_srp.effective_date)) THEN
779
780 get_managers(p_api_version => 1.0,
781 p_salesrep_id => eachmgr.salesrep_id,
782 p_comp_group_id => p_srp.group_id,
783 p_effective_date => p_srp.effective_date,
784 x_return_status => x_return_status,
785 x_msg_count => x_msg_count,
786 x_msg_data => x_msg_data,
787 x_salesrep_tbl => l_srp_tbl,
788 x_returned_rows => l_returned_rows);
789 IF (l_srp_tbl.count > 0) THEN
790 FOR i in l_srp_tbl.first .. l_srp_tbl.last LOOP
791 l_counter := l_counter + 1;
792 x_srp(l_counter).salesrep_id := eachmgr.salesrep_id;
793 x_srp(l_counter).group_id := p_srp.group_id;
794 x_srp(l_counter).role_id := eachmgr.role_id;
795 x_srp(l_counter).start_date := eachmgr.start_date_active;
796 x_srp(l_counter).end_date := eachmgr.end_date_active;
797 x_srp(l_counter).mgr_srp_id := l_srp_tbl(i).salesrep_id;
798 END LOOP;
799 END IF;
800 END IF;
801
802 END LOOP;
803
804 -- loop through reach ancestor group.
805 l_group.group_id := p_srp.group_id;
806 l_group.effective_date := p_srp.effective_date;
807
808 get_ancestor_group
809 ( p_api_version => 1.0,
810 x_return_status => x_return_status,
811 x_msg_count => x_msg_count,
812 x_msg_data => x_msg_data,
813 p_group => l_group,
814 x_group => l_ancestor_group);
815
816 IF x_return_status = FND_API.g_ret_sts_error THEN
817
818 RAISE FND_API.g_exc_error;
819
820 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
821
822 RAISE FND_API.g_exc_unexpected_error;
823
824 END IF;
825
826 IF( l_ancestor_group.COUNT > 0) THEN
827 FOR eachgroup IN l_ancestor_group.first .. l_ancestor_group.last LOOP
828
829 FOR eachsrp IN members_csr(l_ancestor_group(eachgroup).group_id) LOOP
830
831 IF (eachsrp.start_date <= p_srp.effective_date AND
832 (eachsrp.end_date IS NULL
833 OR eachsrp.end_date >= p_srp.effective_date)) THEN
834
835 l_srp_tbl.DELETE;
836 get_managers(p_api_version => 1.0,
837 p_salesrep_id => eachsrp.salesrep_id,
838 p_comp_group_id => l_ancestor_group(eachgroup).group_id,
839 p_effective_date => p_srp.effective_date,
840 x_return_status => x_return_status,
841 x_msg_count => x_msg_count,
842 x_msg_data => x_msg_data,
843 x_salesrep_tbl => l_srp_tbl,
844 x_returned_rows => l_returned_rows);
845
846 IF (l_srp_tbl.count > 0) THEN
847 FOR i in l_srp_tbl.first .. l_srp_tbl.last LOOP
848 l_counter := l_counter + 1;
849 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
850 x_srp(l_counter).group_id :=
851 l_ancestor_group(eachgroup).group_id;
852 x_srp(l_counter).role_id := eachsrp.role_id;
853 x_srp(l_counter).start_date := eachsrp.start_date;
854 x_srp(l_counter).end_date := eachsrp.end_date;
855 x_srp(l_counter).mgr_srp_id := l_srp_tbl(i).salesrep_id;
856 END LOOP;
857 END IF;
858
859
860 END IF; -- end of check date_range_overlap
861
862 END LOOP; -- end of eachsrp
863
864 END LOOP; -- end of eachgroup
865 END IF;
866
867
868 -- End of API body.
869
870 -- Standard check of p_commit.
871 IF FND_API.To_Boolean( p_commit ) THEN
872 COMMIT WORK;
873 END IF;
874
875 -- Standard call to get message count and if count is 1, get message info.
876 FND_MSG_PUB.Count_And_Get
877 (p_count => x_msg_count ,
878 p_data => x_msg_data );
879
880 EXCEPTION
881 WHEN FND_API.G_EXC_ERROR THEN
882 ROLLBACK TO get_ancestor_salesrep;
883 x_return_status := FND_API.G_RET_STS_ERROR ;
884 FND_MSG_PUB.Count_And_Get
885 (p_count => x_msg_count ,
886 p_data => x_msg_data );
887
888 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
889 ROLLBACK TO get_ancestor_salesrep;
890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
891 FND_MSG_PUB.Count_And_Get
892 (p_count => x_msg_count ,
893 p_data => x_msg_data );
894
895 WHEN OTHERS THEN
896 ROLLBACK TO get_ancestor_salesrep;
897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
898 IF FND_MSG_PUB.Check_Msg_Level
899 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
900 THEN
901 FND_MSG_PUB.Add_Exc_Msg
902 (G_PKG_NAME ,
903 l_api_name );
904 END IF;
905 FND_MSG_PUB.Count_And_Get
906 (p_count => x_msg_count ,
907 p_data => x_msg_data );
908
909 END Get_Ancestor_Salesreps;
910
911 --}}}
912
913 --{{{ get_descendant_salesreps
914
915 -- Start of comments
916 -- API name : Get_Descendant_Salesreps
917 -- Type : Private.
918 -- Function :
919 -- Pre-reqs : None.
920 -- Parameters :
921 -- IN : p_api_version IN NUMBER Required
922 -- p_init_msg_list IN VARCHAR2 Optional
923 -- Default = FND_API.G_FALSE
924 -- p_commit IN VARCHAR2 Optional
925 -- Default = FND_API.G_FALSE
926 -- p_validation_level IN NUMBER Optional
927 -- Default = FND_API.G_VALID_LEVEL_FULL
928 -- p_srp IN srp_group_rec_type Required
929 -- p_effective_date IN DATE Required
930 -- OUT : x_return_status OUT VARCHAR2(1)
931 -- x_msg_count OUT NUMBER
932 -- x_msg_data OUT VARCHAR2(2000)
933 -- x_srp OUT srp_role_group_tbl_type
934 -- x_returned_rows OUT INTEGER
935 -- Version : Current version 1.0
936 -- Initial version 1.0
937 --
938 -- Notes : Note text
939 --
940 -- End of comments
941 PROCEDURE Get_Descendant_Salesreps
942 (p_api_version IN NUMBER,
943 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
944 p_commit IN VARCHAR2 := FND_API.G_FALSE,
945 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
946 p_srp IN srp_group_rec_type,
947 p_return_current IN varchar2 := 'Y',
948 x_return_status OUT NOCOPY VARCHAR2,
949 x_msg_count OUT NOCOPY NUMBER,
950 x_msg_data OUT NOCOPY VARCHAR2,
951 x_srp OUT NOCOPY srp_role_group_tbl_type,
952 x_returned_rows OUT NOCOPY number) IS
953
954 l_api_name CONSTANT VARCHAR2(30) := 'get_descendant_salesreps';
955 l_api_version CONSTANT NUMBER := 1.0;
956
957 l_counter NUMBER(15) := 0;
958 l_group input_group_type;
959 l_descendant_group group_tbl_type;
960 l_mgr_flag VARCHAR2(1) := 'N';
961 l_srp_tbl srp_role_group_tbl_type;
962 l_returned_rows number;
963
964 CURSOR members_csr(p_group_id NUMBER) IS
965 SELECT srp_id salesrep_id, trunc(start_date_active) start_date,
966 trunc(end_date_active) end_date, role_id, comp_group_id, manager_flag
967 FROM cn_qm_mgr_srp_groups
968 WHERE comp_group_id = p_group_id
969 ORDER BY manager_flag DESC;
970
971 CURSOR mgr_check IS
972 SELECT manager_flag
973 FROM cn_qm_mgr_groups
974 WHERE comp_group_id = p_srp.group_id
975 AND manager_srp_id = p_srp.salesrep_id
976 AND (trunc(start_date_active) <= p_srp.effective_date)
977 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
978
979 BEGIN
980 -- Standard Start of API savepoint
981 SAVEPOINT get_descendant_salesreps;
982
983 -- Standard call to check for call compatibility.
984 IF NOT FND_API.Compatible_API_Call
985 (l_api_version ,
986 p_api_version ,
987 l_api_name ,
988 G_PKG_NAME )
989 THEN
990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
991 END IF;
992
993 -- Initialize message list if p_init_msg_list is set to TRUE.
994 IF FND_API.to_Boolean( p_init_msg_list ) THEN
995 FND_MSG_PUB.initialize;
996 END IF;
997
998 -- Initialize API return status to success
999 x_return_status := FND_API.G_RET_STS_SUCCESS;
1000 -- API body
1001
1002 IF (p_return_current = 'Y') THEN
1003 FOR eachmem IN members_csr(p_srp.group_id) LOOP
1004 IF (eachmem.start_date <= p_srp.effective_date AND
1005 (eachmem.end_date IS NULL
1006 OR eachmem.end_date >= p_srp.effective_date))
1007 AND eachmem.salesrep_id = p_srp.salesrep_id THEN
1008
1009 l_counter := l_counter + 1;
1010 x_srp(l_counter).salesrep_id := eachmem.salesrep_id;
1011 x_srp(l_counter).group_id := p_srp.group_id;
1012 x_srp(l_counter).role_id := eachmem.role_id;
1013 x_srp(l_counter).start_date := eachmem.start_date;
1014 x_srp(l_counter).end_date := eachmem.end_date;
1015 x_srp(l_counter).mgr_srp_id := p_srp.salesrep_id;
1016 x_srp(l_counter).hier_level := 0;
1017 END IF;
1018 END LOOP;
1019 END IF;
1020
1021
1022 -- if p_srp.salesrep_id is a manager, get the other members of his own group
1023 OPEN mgr_check;
1024 FETCH mgr_check INTO l_mgr_flag;
1025 CLOSE mgr_check;
1026
1027 IF (l_mgr_flag = 'Y') THEN
1028 FOR eachmem IN members_csr(p_srp.group_id) LOOP
1029 IF (eachmem.start_date <= p_srp.effective_date AND
1030 (eachmem.end_date IS NULL
1031 OR eachmem.end_date >= p_srp.effective_date))
1032 AND eachmem.salesrep_id <> p_srp.salesrep_id THEN
1033
1034 l_counter := l_counter + 1;
1035 x_srp(l_counter).salesrep_id := eachmem.salesrep_id;
1036 x_srp(l_counter).group_id := p_srp.group_id;
1037 x_srp(l_counter).role_id := eachmem.role_id;
1038 x_srp(l_counter).start_date := eachmem.start_date;
1039 x_srp(l_counter).end_date := eachmem.end_date;
1040 x_srp(l_counter).mgr_srp_id := p_srp.salesrep_id;
1041 x_srp(l_counter).hier_level := 0;
1042 END IF;
1043 END LOOP;
1044
1045
1046 -- initialize l_group
1047 l_group.group_id := p_srp.group_id;
1048 l_group.effective_date := p_srp.effective_date;
1049
1050 get_descendant_group
1051 ( p_api_version => 1.0,
1052 x_return_status => x_return_status,
1053 x_msg_count => x_msg_count,
1054 x_msg_data => x_msg_data,
1055 p_group => l_group,
1056 x_group => l_descendant_group,
1057 p_level => 0);
1058
1059 IF x_return_status = FND_API.g_ret_sts_error THEN
1060 RAISE FND_API.g_exc_error;
1061 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1062 RAISE FND_API.g_exc_unexpected_error;
1063 END IF;
1064
1065 IF (l_descendant_group.COUNT > 0) THEN
1066 FOR eachgroup IN l_descendant_group.first .. l_descendant_group.last LOOP
1067 FOR eachsrp IN members_csr(l_descendant_group(eachgroup).group_id) LOOP
1068 IF (eachsrp.start_date <= p_srp.effective_date AND
1069 (eachsrp.end_date IS NULL
1070 OR eachsrp.end_date >= p_srp.effective_date)) THEN
1071
1072 l_counter := l_counter + 1;
1073 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
1074 x_srp(l_counter).group_id :=
1075 l_descendant_group(eachgroup).group_id;
1076 x_srp(l_counter).role_id := eachsrp.role_id;
1077 x_srp(l_counter).start_date := eachsrp.start_date;
1078 x_srp(l_counter).end_date := eachsrp.end_date;
1079 x_srp(l_counter).mgr_srp_id := eachsrp.salesrep_id;
1080 x_srp(l_counter).hier_level := l_descendant_group(eachgroup).hier_level;
1081 END IF; -- end of check date_range_overlap
1082 END LOOP; -- end of eachsrp
1083 END LOOP; -- end of eachgroup
1084 END IF;
1085
1086 END IF; -- end of IF (l_mgr_flag = 'Y') THEN
1087
1088 -- End of API body.
1089
1090 -- Standard check of p_commit.
1091 IF FND_API.To_Boolean( p_commit ) THEN
1092 COMMIT WORK;
1093 END IF;
1094
1095 -- Standard call to get message count and if count is 1, get message info.
1096 FND_MSG_PUB.Count_And_Get
1097 (p_count => x_msg_count ,
1098 p_data => x_msg_data );
1099
1100 EXCEPTION
1101 WHEN FND_API.G_EXC_ERROR THEN
1102 ROLLBACK TO get_descendant_salesreps;
1103 x_return_status := FND_API.G_RET_STS_ERROR ;
1104 FND_MSG_PUB.Count_And_Get
1105 (p_count => x_msg_count ,
1106 p_data => x_msg_data );
1107
1108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1109 ROLLBACK TO get_descendant_salesreps;
1110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1111 FND_MSG_PUB.Count_And_Get
1112 (p_count => x_msg_count ,
1113 p_data => x_msg_data );
1114
1115 WHEN OTHERS THEN
1116 ROLLBACK TO get_descendant_salesreps;
1117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1118 IF FND_MSG_PUB.Check_Msg_Level
1119 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1120 THEN
1121 FND_MSG_PUB.Add_Exc_Msg
1122 (G_PKG_NAME ,
1123 l_api_name );
1124 END IF;
1125 FND_MSG_PUB.Count_And_Get
1126 (p_count => x_msg_count ,
1127 p_data => x_msg_data );
1128
1129 END Get_Descendant_Salesreps;
1130
1131 --}}}
1132 --{{{ get_descendant_group_mbrs
1133
1134 -- Start of comments
1135 -- API name : Get_Descendant_group_mbrs
1136 -- Type : Private.
1137 -- Function :
1138 -- Pre-reqs : None.
1139 -- Parameters :
1140 -- IN : p_api_version IN NUMBER Required
1141 -- p_init_msg_list IN VARCHAR2 Optional
1142 -- Default = FND_API.G_FALSE
1143 -- p_commit IN VARCHAR2 Optional
1144 -- Default = FND_API.G_FALSE
1145 -- p_validation_level IN NUMBER Optional
1146 -- Default = FND_API.G_VALID_LEVEL_FULL
1147 -- p_srp IN srp_group_rec_type Required
1148 -- p_effective_date IN DATE Required
1149 -- OUT : x_return_status OUT VARCHAR2(1)
1150 -- x_msg_count OUT NUMBER
1151 -- x_msg_data OUT VARCHAR2(2000)
1152 -- x_srp OUT group_mbr_tbl_type
1153 -- x_returned_rows OUT INTEGER
1154 -- Version : Current version 1.0
1155 -- Initial version 1.0
1156 --
1157 -- Notes : Note text
1158 --
1159 -- End of comments
1160 PROCEDURE Get_Descendant_group_mbrs
1161 (p_api_version IN NUMBER,
1162 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1163 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1164 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1165 p_srp IN srp_group_rec_type,
1166 p_return_current IN varchar2 := 'Y',
1167 p_level IN number := 0,
1168 p_first_level_only IN varchar2 := 'N',
1169 x_return_status OUT NOCOPY VARCHAR2,
1170 x_msg_count OUT NOCOPY NUMBER,
1171 x_msg_data OUT NOCOPY VARCHAR2,
1172 x_srp IN OUT NOCOPY group_mbr_tbl_type,
1173 x_returned_rows OUT NOCOPY number) IS
1174
1175 l_api_name CONSTANT VARCHAR2(30) := 'Get_Descendant_group_mbrs';
1176 l_api_version CONSTANT NUMBER := 1.0;
1177
1178 l_counter NUMBER(15) := 0;
1179
1180 l_srp srp_group_rec_type;
1181
1182 l_mgr_srp_id NUMBER;
1183 l_returned_rows number;
1184 l_mgr_flag VARCHAR2(1);
1185
1186 CURSOR groups_csr(p_group_id NUMBER, i_level NUMBER) IS
1187 SELECT
1188 g.group_id group_id,
1189 trunc(g.start_date_active) start_date,
1190 trunc(g.end_date_active) end_date
1191 FROM jtf_rs_groups_vl g, jtf_rs_group_usages u
1192 WHERE
1193 g.group_id = p_group_id
1194 AND g.group_id = u.group_id
1195 AND u.usage='SF_PLANNING'
1196 AND i_level = 0
1197 UNION ALL
1198 SELECT r.group_id group_id,
1199 trunc(r.start_date_active) start_date,
1200 trunc(r.end_date_active) end_date
1201 FROM jtf_rs_grp_relations r,
1202 jtf_rs_group_usages u1,
1203 jtf_rs_group_usages u2
1204 WHERE
1205 u1.group_id = r.group_id
1206 AND u2.group_id = r.related_group_id
1207 AND u1.usage='SF_PLANNING'
1208 AND u2.usage='SF_PLANNING'
1209 AND r.delete_flag = 'N'
1210 AND r.related_group_id = p_group_id
1211 ;
1212
1213 CURSOR grp_members_csr(p_group_id NUMBER) IS
1214 SELECT DISTINCT jrs.salesrep_id salesrep_id
1215 FROM jtf_rs_group_members jgm,
1216 jtf_rs_salesreps_mo_v jrs
1217 WHERE jgm.group_id = p_group_id
1218 AND jgm.resource_id = jrs.resource_id
1219 AND nvl(jgm.delete_flag,'N') = 'N'
1220 ;
1221
1222 CURSOR grp_mgr(p_group_id NUMBER) IS
1223 SELECT NVL(MAX(manager_srp_id),0) -- will return null if grp has no manager
1224 FROM cn_qm_mgr_groups
1225 WHERE comp_group_id = p_group_id
1226 AND (trunc(start_date_active) <= p_srp.effective_date)
1227 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1228
1229 CURSOR mgr_check IS
1230 SELECT manager_flag
1231 FROM cn_qm_mgr_groups
1232 WHERE comp_group_id = p_srp.group_id
1233 AND manager_srp_id = p_srp.salesrep_id
1234 AND (trunc(start_date_active) <= p_srp.effective_date)
1235 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1236
1237
1238 BEGIN
1239 -- Standard Start of API savepoint
1240 SAVEPOINT Get_Descendant_group_mbrs;
1241
1242
1243 -- Standard call to check for call compatibility.
1244 IF NOT FND_API.Compatible_API_Call
1245 (l_api_version ,
1246 p_api_version ,
1247 l_api_name ,
1248 G_PKG_NAME )
1249 THEN
1250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1251 END IF;
1252
1253 -- Initialize message list if p_init_msg_list is set to TRUE.
1254 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1255 FND_MSG_PUB.initialize;
1256 END IF;
1257
1258 -- Initialize API return status to success
1259 x_return_status := FND_API.G_RET_STS_SUCCESS;
1260 -- API body
1261
1262 -- If salesrep is not a manager then dont proceed
1263 OPEN mgr_check;
1264 FETCH mgr_check INTO l_mgr_flag;
1265 CLOSE mgr_check;
1266
1267 IF (l_mgr_flag = 'Y') THEN
1268
1269
1270 l_counter := x_srp.count;
1271
1272 -- dbms_output.put_line('lcount = ' || to_char(l_counter));
1273
1274
1275 FOR eachgroup IN groups_csr(p_srp.group_id, p_level) LOOP
1276
1277 -- dbms_output.put_line('par ' || to_char(p_srp.group_id) || 'chi ' || to_char(eachgroup.group_id));
1278
1279
1280 IF (eachgroup.start_date <= p_srp.effective_date AND
1281 (eachgroup.end_date IS NULL
1282 OR eachgroup.end_date >= p_srp.effective_date)) THEN
1283
1284 FOR each_grp_member IN grp_members_csr (eachgroup.group_id) LOOP
1285
1286 l_counter := x_srp.count + 1;
1287
1288 OPEN grp_mgr(eachgroup.group_id);
1289 FETCH grp_mgr INTO l_mgr_srp_id;
1290 CLOSE grp_mgr;
1291
1292
1293 IF l_mgr_srp_id = each_grp_member.salesrep_id THEN
1294
1295 x_srp(l_counter).group_id := eachgroup.group_id;
1296 x_srp(l_counter).salesrep_id := each_grp_member.salesrep_id;
1297 x_srp(l_counter).mgr_srp_id := p_srp.salesrep_id;
1298 x_srp(l_counter).hier_level := p_level + 1;
1299
1300 -- dbms_output.put_line(' ++mgr=' || to_char( p_srp.salesrep_id) || 'srp=' || to_char(each_grp_member.salesrep_id));
1301
1302 ELSE
1303 x_srp(l_counter).group_id := eachgroup.group_id;
1304 x_srp(l_counter).salesrep_id := each_grp_member.salesrep_id;
1305 x_srp(l_counter).mgr_srp_id := l_mgr_srp_id;
1306 x_srp(l_counter).hier_level := p_level + 1;
1307
1308 -- dbms_output.put_line(' +-mgr=' || to_char( l_mgr_srp_id) || 'srp=' || to_char(each_grp_member.salesrep_id));
1309 END IF;
1310
1311 END LOOP;
1312
1313 l_srp.group_id := eachgroup.group_id;
1314 l_srp.effective_date := p_srp.effective_date;
1315 l_srp.salesrep_id := l_mgr_srp_id;
1316
1317 -- dbms_output.put_line('GRP=' || to_char(l_srp.group_id) || ' LEV=' || to_char(p_level));
1318
1319 IF l_srp.group_id <> p_srp.group_id
1320 AND p_first_level_only = 'N' THEN
1321
1322 get_descendant_group_mbrs
1323 ( p_api_version => 1.0,
1324 x_return_status => x_return_status,
1325 x_msg_count => x_msg_count,
1326 x_msg_data => x_msg_data,
1327 p_srp => l_srp,
1328 x_srp => x_srp,
1329 p_level => p_level + 1,
1330 x_returned_rows => x_returned_rows);
1331
1332 END IF;
1333
1334 END IF;
1335
1336 END LOOP;
1337
1338 END IF; -- If salesrep is a manager
1339
1340 -- End of API body.
1341
1342 -- Standard check of p_commit.
1343 IF FND_API.To_Boolean( p_commit ) THEN
1344 COMMIT WORK;
1345 END IF;
1346
1347 -- Standard call to get message count and if count is 1, get message info.
1348 FND_MSG_PUB.Count_And_Get
1349 (p_count => x_msg_count ,
1350 p_data => x_msg_data );
1351
1352 EXCEPTION
1353 WHEN FND_API.G_EXC_ERROR THEN
1354 ROLLBACK TO Get_Descendant_group_mbrs;
1355 x_return_status := FND_API.G_RET_STS_ERROR ;
1356 FND_MSG_PUB.Count_And_Get
1357 (p_count => x_msg_count ,
1358 p_data => x_msg_data );
1359
1360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1361 ROLLBACK TO Get_Descendant_group_mbrs;
1362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1363 FND_MSG_PUB.Count_And_Get
1364 (p_count => x_msg_count ,
1365 p_data => x_msg_data );
1366
1367 WHEN OTHERS THEN
1368 ROLLBACK TO Get_Descendant_group_mbrs;
1369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1370 IF FND_MSG_PUB.Check_Msg_Level
1371 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1372 THEN
1373 FND_MSG_PUB.Add_Exc_Msg
1374 (G_PKG_NAME ,
1375 l_api_name );
1376 END IF;
1377 FND_MSG_PUB.Count_And_Get
1378 (p_count => x_msg_count ,
1379 p_data => x_msg_data );
1380
1381 END Get_Descendant_group_mbrs;
1382
1383 -- ***********************************
1384 -- TBD : MO
1385 -- ***********************************
1386 PROCEDURE Get_MO_Descendant_group_mbrs
1387 (p_api_version IN NUMBER,
1388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1389 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1390 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1391 p_srp IN srp_group_rec_type,
1392 p_return_current IN varchar2 := 'Y',
1393 p_level IN number := 0,
1394 p_first_level_only IN varchar2 := 'N',
1395 p_is_multiorg IN VARCHAR2 := 'N',
1396 x_return_status OUT NOCOPY VARCHAR2,
1397 x_msg_count OUT NOCOPY NUMBER,
1398 x_msg_data OUT NOCOPY VARCHAR2,
1399 x_srp IN OUT NOCOPY group_mbr_tbl_type,
1400 x_returned_rows OUT NOCOPY number) IS
1401
1402 l_api_name CONSTANT VARCHAR2(30) := 'Get_Descendant_group_mbrs';
1403 l_api_version CONSTANT NUMBER := 1.0;
1404
1405 l_counter NUMBER(15) := 0;
1406
1407 l_srp srp_group_rec_type;
1408
1409 l_mgr_srp_id NUMBER;
1410 l_returned_rows number;
1411 l_mgr_flag VARCHAR2(1);
1412
1413 CURSOR groups_csr(p_group_id NUMBER, i_level NUMBER) IS
1414 SELECT
1415 g.group_id group_id,
1416 trunc(g.start_date_active) start_date,
1417 trunc(g.end_date_active) end_date
1418 FROM jtf_rs_groups_vl g, jtf_rs_group_usages u
1419 WHERE
1420 g.group_id = p_group_id
1421 AND g.group_id = u.group_id
1422 AND u.usage='SF_PLANNING'
1423 AND i_level = 0
1424 UNION ALL
1425 SELECT r.group_id group_id,
1426 trunc(r.start_date_active) start_date,
1427 trunc(r.end_date_active) end_date
1428 FROM jtf_rs_grp_relations r,
1429 jtf_rs_group_usages u1,
1430 jtf_rs_group_usages u2
1431 WHERE
1432 u1.group_id = r.group_id
1433 AND u2.group_id = r.related_group_id
1434 AND u1.usage='SF_PLANNING'
1435 AND u2.usage='SF_PLANNING'
1436 AND r.delete_flag = 'N'
1437 AND r.related_group_id = p_group_id
1438 ;
1439
1440 CURSOR grp_members_csr(p_group_id NUMBER) IS
1441 SELECT DISTINCT jrs.salesrep_id salesrep_id
1442 FROM jtf_rs_group_members jgm,
1443 jtf_rs_salesreps_mo_v jrs
1444 WHERE jgm.group_id = p_group_id
1445 AND jgm.resource_id = jrs.resource_id
1446 AND nvl(jgm.delete_flag,'N') = 'N'
1447 ;
1448
1449 CURSOR grp_mgr(p_group_id NUMBER) IS
1450 SELECT NVL(MAX(manager_srp_id),0) -- will return null if grp has no manager
1451 FROM cn_qm_mgr_groups
1452 WHERE comp_group_id = p_group_id
1453 AND (trunc(start_date_active) <= p_srp.effective_date)
1454 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1455
1456 CURSOR mgr_check IS
1457 SELECT manager_flag
1458 FROM cn_qm_mgr_groups
1459 WHERE comp_group_id = p_srp.group_id
1460 AND manager_srp_id = p_srp.salesrep_id
1461 AND (trunc(start_date_active) <= p_srp.effective_date)
1462 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1463
1464 -- Added Multi Orged based Cursors
1465
1466 CURSOR mo_grp_mgr IS
1467 SELECT NVL(MAX(jrs.salesrep_id),0) manager_srp_id
1468 FROM
1469 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
1470 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
1471 cn_srp_role_dtls srd
1472 WHERE
1473 jg.group_id = jgm.group_id
1474 and jgm.manager_flag = 'Y'
1475 and jrs.resource_id = jgm.resource_id
1476 and u.group_id = jgm.group_id
1477 and u.usage = 'SF_PLANNING'
1478 and jrr.role_resource_type = 'RS_INDIVIDUAL'
1479 and jrr.role_resource_id = jrs.resource_id
1480 and jrr.role_id = jgm.role_id
1481 and jrr.role_id = jr.role_id
1482 and jr.role_type_code = 'SALES_COMP'
1483 and jrr.delete_flag <> 'Y'
1484 and jrr.start_date_active <= jgm.start_date_active
1485 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
1486 AND jrs.SALESREP_ID > 0
1487 AND srd.srp_role_id = jrr.role_relate_id
1488 AND jgm.group_id = p_srp.group_id
1489 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
1490 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);
1491
1492 CURSOR mo_mgr_check IS
1493 SELECT jgm.manager_flag manager_flag
1494 FROM
1495 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
1496 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
1497 cn_srp_role_dtls srd
1498 WHERE
1499 jg.group_id = jgm.group_id
1500 and jgm.manager_flag = 'Y'
1501 and jrs.resource_id = jgm.resource_id
1502 and u.group_id = jgm.group_id
1503 and u.usage = 'SF_PLANNING'
1504 and jrr.role_resource_type = 'RS_INDIVIDUAL'
1505 and jrr.role_resource_id = jrs.resource_id
1506 and jrr.role_id = jgm.role_id
1507 and jrr.role_id = jr.role_id
1508 and jr.role_type_code = 'SALES_COMP'
1509 and jrr.delete_flag <> 'Y'
1510 and jrr.start_date_active <= jgm.start_date_active
1511 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
1512 AND jrs.SALESREP_ID > 0
1513 AND srd.srp_role_id = jrr.role_relate_id
1514 AND jgm.group_id = p_srp.group_id
1515 AND jrs.salesrep_id = p_srp.salesrep_id
1516 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
1517 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);
1518
1519 TYPE RC_TYPE IS REF CURSOR;
1520 mgr_check_rc RC_TYPE;
1521 grp_mgr_rc RC_TYPE;
1522
1523
1524 BEGIN
1525 -- Standard Start of API savepoint
1526 SAVEPOINT Get_MO_Descendant_group_mbrs;
1527
1528
1529 -- Standard call to check for call compatibility.
1530 IF NOT FND_API.Compatible_API_Call
1531 (l_api_version ,
1532 p_api_version ,
1533 l_api_name ,
1534 G_PKG_NAME )
1535 THEN
1536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1537 END IF;
1538
1539 -- Initialize message list if p_init_msg_list is set to TRUE.
1540 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1541 FND_MSG_PUB.initialize;
1542 END IF;
1543
1544 -- Initialize API return status to success
1545 x_return_status := FND_API.G_RET_STS_SUCCESS;
1546 -- API body
1547
1548 -- If salesrep is not a manager then dont proceed
1549 --OPEN mgr_check;
1550 --FETCH mgr_check INTO l_mgr_flag;
1551 --CLOSE mgr_check;
1552
1553 IF p_is_multiorg = 'Y' THEN
1554 OPEN mgr_check_rc FOR SELECT jgm.manager_flag manager_flag
1555 FROM
1556 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
1557 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
1558 cn_srp_role_dtls srd
1559 WHERE
1560 jg.group_id = jgm.group_id
1561 and jgm.manager_flag = 'Y'
1562 and jrs.resource_id = jgm.resource_id
1563 and u.group_id = jgm.group_id
1564 and u.usage = 'SF_PLANNING'
1565 and jrr.role_resource_type = 'RS_INDIVIDUAL'
1566 and jrr.role_resource_id = jrs.resource_id
1567 and jrr.role_id = jgm.role_id
1568 and jrr.role_id = jr.role_id
1569 and jr.role_type_code = 'SALES_COMP'
1570 and jrr.delete_flag <> 'Y'
1571 and jrr.start_date_active <= jgm.start_date_active
1572 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
1573 AND jrs.SALESREP_ID > 0
1574 AND srd.srp_role_id = jrr.role_relate_id
1575 AND jgm.group_id = p_srp.group_id
1576 AND jrs.salesrep_id = p_srp.salesrep_id
1577 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
1578 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);
1579 ELSE
1580 OPEN mgr_check_rc FOR SELECT manager_flag
1581 FROM cn_qm_mgr_groups
1582 WHERE comp_group_id = p_srp.group_id
1583 AND manager_srp_id = p_srp.salesrep_id
1584 AND (trunc(start_date_active) <= p_srp.effective_date)
1585 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1586 END IF;
1587
1588 LOOP
1589 FETCH mgr_check_rc INTO l_mgr_flag;
1590 EXIT WHEN mgr_check_rc%NOTFOUND;
1591 END LOOP;
1592 CLOSE mgr_check_rc;
1593
1594 IF (l_mgr_flag = 'Y') THEN
1595
1596
1597 l_counter := x_srp.count;
1598
1599 -- dbms_output.put_line('lcount = ' || to_char(l_counter));
1600
1601
1602 FOR eachgroup IN groups_csr(p_srp.group_id, p_level) LOOP
1603
1604 -- dbms_output.put_line('par ' || to_char(p_srp.group_id) || 'chi ' || to_char(eachgroup.group_id));
1605
1606
1607 IF (eachgroup.start_date <= p_srp.effective_date AND
1608 (eachgroup.end_date IS NULL
1609 OR eachgroup.end_date >= p_srp.effective_date)) THEN
1610
1611 FOR each_grp_member IN grp_members_csr (eachgroup.group_id) LOOP
1612
1613 l_counter := x_srp.count + 1;
1614
1615 -- OPEN grp_mgr(eachgroup.group_id);
1616 -- FETCH grp_mgr INTO l_mgr_srp_id;
1617 -- CLOSE grp_mgr;
1618 IF p_is_multiorg = 'Y' THEN
1619 OPEN grp_mgr_rc FOR SELECT NVL(MAX(jrs.salesrep_id),0) manager_srp_id
1620 FROM
1621 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
1622 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
1623 cn_srp_role_dtls srd
1624 WHERE
1625 jg.group_id = jgm.group_id
1626 and jgm.manager_flag = 'Y'
1627 and jrs.resource_id = jgm.resource_id
1628 and u.group_id = jgm.group_id
1629 and u.usage = 'SF_PLANNING'
1630 and jrr.role_resource_type = 'RS_INDIVIDUAL'
1631 and jrr.role_resource_id = jrs.resource_id
1632 and jrr.role_id = jgm.role_id
1633 and jrr.role_id = jr.role_id
1634 and jr.role_type_code = 'SALES_COMP'
1635 and jrr.delete_flag <> 'Y'
1636 and jrr.start_date_active <= jgm.start_date_active
1637 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
1638 AND jrs.SALESREP_ID > 0
1639 AND srd.srp_role_id = jrr.role_relate_id
1640 AND jgm.group_id = eachgroup.group_id
1641 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
1642 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);
1643 ELSE
1644 OPEN grp_mgr_rc FOR SELECT NVL(MAX(manager_srp_id),0)
1645 FROM cn_qm_mgr_groups
1646 WHERE comp_group_id = eachgroup.group_id
1647 AND (trunc(start_date_active) <= p_srp.effective_date)
1648 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1649 END IF;
1650
1651 LOOP
1652 FETCH grp_mgr_rc INTO l_mgr_srp_id;
1653 EXIT WHEN grp_mgr_rc%NOTFOUND;
1654 END LOOP;
1655 CLOSE grp_mgr_rc;
1656
1657
1658 IF l_mgr_srp_id = each_grp_member.salesrep_id THEN
1659
1660 x_srp(l_counter).group_id := eachgroup.group_id;
1661 x_srp(l_counter).salesrep_id := each_grp_member.salesrep_id;
1662 x_srp(l_counter).mgr_srp_id := p_srp.salesrep_id;
1663 x_srp(l_counter).hier_level := p_level + 1;
1664
1665 -- dbms_output.put_line(' ++mgr=' || to_char( p_srp.salesrep_id) || 'srp=' || to_char(each_grp_member.salesrep_id));
1666
1667 ELSE
1668 x_srp(l_counter).group_id := eachgroup.group_id;
1669 x_srp(l_counter).salesrep_id := each_grp_member.salesrep_id;
1670 x_srp(l_counter).mgr_srp_id := l_mgr_srp_id;
1671 x_srp(l_counter).hier_level := p_level + 1;
1672
1673 -- dbms_output.put_line(' +-mgr=' || to_char( l_mgr_srp_id) || 'srp=' || to_char(each_grp_member.salesrep_id));
1674 END IF;
1675
1676 END LOOP;
1677
1678 l_srp.group_id := eachgroup.group_id;
1679 l_srp.effective_date := p_srp.effective_date;
1680 l_srp.salesrep_id := l_mgr_srp_id;
1681
1682 -- dbms_output.put_line('GRP=' || to_char(l_srp.group_id) || ' LEV=' || to_char(p_level));
1683
1684 IF l_srp.group_id <> p_srp.group_id
1685 AND p_first_level_only = 'N' THEN
1686
1687 get_mo_descendant_group_mbrs
1688 ( p_api_version => 1.0,
1689 x_return_status => x_return_status,
1690 x_msg_count => x_msg_count,
1691 x_msg_data => x_msg_data,
1692 p_srp => l_srp,
1693 p_is_multiorg => p_is_multiorg,
1694 x_srp => x_srp,
1695 p_level => p_level + 1,
1696 x_returned_rows => x_returned_rows);
1697
1698 END IF;
1699
1700 END IF;
1701
1702 END LOOP;
1703
1704 END IF; -- If salesrep is a manager
1705
1706 -- End of API body.
1707
1708 -- Standard check of p_commit.
1709 IF FND_API.To_Boolean( p_commit ) THEN
1710 COMMIT WORK;
1711 END IF;
1712
1713 -- Standard call to get message count and if count is 1, get message info.
1714 FND_MSG_PUB.Count_And_Get
1715 (p_count => x_msg_count ,
1716 p_data => x_msg_data );
1717
1718 EXCEPTION
1719 WHEN FND_API.G_EXC_ERROR THEN
1720 ROLLBACK TO Get_MO_Descendant_group_mbrs;
1721 x_return_status := FND_API.G_RET_STS_ERROR ;
1722 FND_MSG_PUB.Count_And_Get
1723 (p_count => x_msg_count ,
1724 p_data => x_msg_data );
1725
1726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1727 ROLLBACK TO Get_MO_Descendant_group_mbrs;
1728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1729 FND_MSG_PUB.Count_And_Get
1730 (p_count => x_msg_count ,
1731 p_data => x_msg_data );
1732
1733 WHEN OTHERS THEN
1734 ROLLBACK TO Get_MO_Descendant_group_mbrs;
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1736 IF FND_MSG_PUB.Check_Msg_Level
1737 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1738 THEN
1739 FND_MSG_PUB.Add_Exc_Msg
1740 (G_PKG_NAME ,
1741 l_api_name );
1742 END IF;
1743 FND_MSG_PUB.Count_And_Get
1744 (p_count => x_msg_count ,
1745 p_data => x_msg_data );
1746
1747 END Get_MO_Descendant_group_mbrs;
1748
1749
1750 --}}}
1751
1752
1753 --{{{ get_desc_role_info
1754
1755 -- Start of comments
1756 -- API name : Get_Desc_role_info
1757 -- Type : Private.
1758 -- Function :
1759 -- Pre-reqs : None.
1760 -- Parameters :
1761 -- IN : p_api_version IN NUMBER Required
1762 -- p_init_msg_list IN VARCHAR2 Optional
1763 -- Default = FND_API.G_FALSE
1764 -- p_commit IN VARCHAR2 Optional
1765 -- Default = FND_API.G_FALSE
1766 -- p_validation_level IN NUMBER Optional
1767 -- Default = FND_API.G_VALID_LEVEL_FULL
1768 -- p_srp IN srp_group_rec_type Required
1769 -- p_effective_date IN DATE Required
1770 -- OUT : x_return_status OUT VARCHAR2(1)
1771 -- x_msg_count OUT NUMBER
1772 -- x_msg_data OUT VARCHAR2(2000)
1773 -- x_srp OUT srp_role_role_tbl_type
1774 -- x_returned_rows OUT INTEGER
1775 -- Version : Current version 1.0
1776 -- Initial version 1.0
1777 --
1778 -- Notes : Note text
1779 --
1780 -- End of comments
1781 PROCEDURE Get_desc_role_info
1782 (p_api_version IN NUMBER,
1783 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1784 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1785 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
1786 p_srp IN srp_group_rec_type,
1787 p_return_current IN varchar2 := 'Y',
1788 x_return_status OUT NOCOPY VARCHAR2,
1789 x_msg_count OUT NOCOPY NUMBER,
1790 x_msg_data OUT NOCOPY VARCHAR2,
1791 x_srp OUT NOCOPY srp_role_info_tbl_type,
1792 x_returned_rows OUT NOCOPY number) IS
1793
1794 l_api_name CONSTANT VARCHAR2(30) := 'get_desc_role_info';
1795 l_api_version CONSTANT NUMBER := 1.0;
1796
1797 l_counter NUMBER(15) := 0;
1798 l_group input_group_type;
1799 l_descendant_group group_tbl_type;
1800 l_mgr_flag VARCHAR2(1) := 'N';
1801 l_srp_tbl srp_role_group_tbl_type;
1802 l_returned_rows number;
1803
1804
1805 CURSOR members_csr(p_group_id NUMBER) IS
1806 SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
1807 g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
1808 org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
1809 FROM cn_qm_mgr_srp_groups g,
1810 cn_srp_role_dtls_v srd
1811 WHERE g.comp_group_id = p_group_id
1812 AND g.srp_role_id = srd.srp_role_id
1813 AND (trunc(srd.start_date) <= p_srp.effective_date)
1814 AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
1815 AND (trunc(g.start_date_active) <= p_srp.effective_date)
1816 AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
1817 AND srd.job_title_id <> -99
1818 ORDER BY g.manager_flag DESC;
1819
1820 CURSOR mgr_check IS
1821 SELECT manager_flag
1822 FROM cn_qm_mgr_groups
1823 WHERE comp_group_id = p_srp.group_id
1824 AND manager_srp_id = p_srp.salesrep_id
1825 AND (trunc(start_date_active) <= p_srp.effective_date)
1826 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
1827
1828 BEGIN
1829 -- Standard Start of API savepoint
1830 SAVEPOINT get_desc_role_info;
1831
1832 -- Standard call to check for call compatibility.
1833 IF NOT FND_API.Compatible_API_Call
1834 (l_api_version ,
1835 p_api_version ,
1836 l_api_name ,
1837 G_PKG_NAME )
1838 THEN
1839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1840 END IF;
1841
1842 -- Initialize message list if p_init_msg_list is set to TRUE.
1843 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1844 FND_MSG_PUB.initialize;
1845 END IF;
1846
1847 -- Initialize API return status to success
1848 x_return_status := FND_API.G_RET_STS_SUCCESS;
1849 -- API body
1850
1851 -- if p_srp.salesrep_id is a manager, get the other members of his own group
1852
1853 OPEN mgr_check;
1854 FETCH mgr_check INTO l_mgr_flag;
1855 CLOSE mgr_check;
1856
1857
1858 IF l_mgr_flag = 'Y' THEN
1859
1860
1861 l_counter := 0;
1862
1863 FOR eachmem IN members_csr(p_srp.group_id) LOOP
1864 IF (eachmem.start_date <= p_srp.effective_date AND
1865 (eachmem.end_date IS NULL
1866 OR eachmem.end_date >= p_srp.effective_date))
1867 AND
1868 (
1869 (p_return_current = 'N' AND eachmem.srp_id <> p_srp.salesrep_id )
1870 OR (p_return_current = 'Y')
1871 )
1872 THEN
1873
1874
1875 l_counter := l_counter + 1;
1876
1877
1878 x_srp(l_counter).srp_role_id := eachmem.srp_role_id ;
1879 x_srp(l_counter).srp_id := eachmem.srp_id ;
1880 x_srp(l_counter).overlay_flag := eachmem.overlay_flag ;
1881 x_srp(l_counter).non_std_flag := eachmem.non_std_flag ;
1882 x_srp(l_counter).role_id := eachmem.role_id ;
1883 x_srp(l_counter).role_name := eachmem.role_name ;
1884 x_srp(l_counter).job_title_id := eachmem.job_title_id ;
1885 x_srp(l_counter).job_discretion := eachmem.job_discretion ;
1886 x_srp(l_counter).status := eachmem.status ;
1887 x_srp(l_counter).plan_activate_status := eachmem.plan_activate_status;
1888 x_srp(l_counter).club_eligible_flag := eachmem.club_eligible_flag;
1889 x_srp(l_counter).org_code := eachmem.org_code ;
1890 x_srp(l_counter).start_date := eachmem.start_date ;
1891 x_srp(l_counter).end_date := eachmem.end_date ;
1892 x_srp(l_counter).group_id := eachmem.group_id ;
1893
1894
1895 END IF;
1896 END LOOP;
1897
1898
1899
1900 -- initialize l_group
1901 l_group.group_id := p_srp.group_id;
1902 l_group.effective_date := p_srp.effective_date;
1903
1904 get_descendant_group
1905 ( p_api_version => 1.0,
1906 x_return_status => x_return_status,
1907 x_msg_count => x_msg_count,
1908 x_msg_data => x_msg_data,
1909 p_group => l_group,
1910 x_group => l_descendant_group,
1911 p_level => 0);
1912
1913
1914 IF x_return_status = FND_API.g_ret_sts_error THEN
1915 RAISE FND_API.g_exc_error;
1916 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1917 RAISE FND_API.g_exc_unexpected_error;
1918 END IF;
1919
1920
1921 IF (l_descendant_group.COUNT > 0) THEN
1922 FOR eachgroup IN l_descendant_group.first .. l_descendant_group.last LOOP
1923 FOR eachsrp IN members_csr(l_descendant_group(eachgroup).group_id) LOOP
1924 IF (eachsrp.start_date <= p_srp.effective_date AND
1925 (eachsrp.end_date IS NULL
1926 OR eachsrp.end_date >= p_srp.effective_date)) THEN
1927
1928 l_counter := l_counter + 1;
1929
1930 x_srp(l_counter).srp_role_id := eachsrp.srp_role_id ;
1931 x_srp(l_counter).srp_id := eachsrp.srp_id ;
1932 x_srp(l_counter).overlay_flag := eachsrp.overlay_flag ;
1933 x_srp(l_counter).non_std_flag := eachsrp.non_std_flag ;
1934 x_srp(l_counter).role_id := eachsrp.role_id ;
1935 x_srp(l_counter).role_name := eachsrp.role_name ;
1936 x_srp(l_counter).job_title_id := eachsrp.job_title_id ;
1937 x_srp(l_counter).job_discretion := eachsrp.job_discretion ;
1938 x_srp(l_counter).status := eachsrp.status ;
1939 x_srp(l_counter).plan_activate_status := eachsrp.plan_activate_status;
1940 x_srp(l_counter).club_eligible_flag := eachsrp.club_eligible_flag;
1941 x_srp(l_counter).org_code := eachsrp.org_code ;
1942 x_srp(l_counter).start_date := eachsrp.start_date ;
1943 x_srp(l_counter).end_date := eachsrp.end_date ;
1944 x_srp(l_counter).group_id := eachsrp.group_id ;
1945
1946 END IF; -- end of check date_range_overlap
1947 END LOOP; -- end of eachsrp
1948 END LOOP; -- end of eachgroup
1949 END IF;
1950
1951 END IF;
1952
1953 -- End of API body.
1954
1955 -- Standard check of p_commit.
1956 IF FND_API.To_Boolean( p_commit ) THEN
1957 COMMIT WORK;
1958 END IF;
1959
1960 -- Standard call to get message count and if count is 1, get message info.
1961 FND_MSG_PUB.Count_And_Get
1962 (p_count => x_msg_count ,
1963 p_data => x_msg_data );
1964
1965 EXCEPTION
1966 WHEN FND_API.G_EXC_ERROR THEN
1967 ROLLBACK TO get_desc_role_info;
1968 x_return_status := FND_API.G_RET_STS_ERROR ;
1969 FND_MSG_PUB.Count_And_Get
1970 (p_count => x_msg_count ,
1971 p_data => x_msg_data );
1972
1973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1974 ROLLBACK TO get_desc_role_info;
1975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1976 FND_MSG_PUB.Count_And_Get
1977 (p_count => x_msg_count ,
1978 p_data => x_msg_data );
1979
1980 WHEN OTHERS THEN
1981 ROLLBACK TO get_desc_role_info;
1982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1983 IF FND_MSG_PUB.Check_Msg_Level
1984 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1985 THEN
1986 FND_MSG_PUB.Add_Exc_Msg
1987 (G_PKG_NAME ,
1988 l_api_name );
1989 END IF;
1990 FND_MSG_PUB.Count_And_Get
1991 (p_count => x_msg_count ,
1992 p_data => x_msg_data );
1993
1994 END Get_Desc_role_info;
1995
1996
1997 -- ***********************************
1998 -- TBD : MO
1999 -- ***********************************
2000 PROCEDURE Get_MO_desc_role_info
2001 (p_api_version IN NUMBER,
2002 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2003 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2004 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
2005 p_srp IN srp_group_rec_type,
2006 p_return_current IN varchar2 := 'Y',
2007 p_is_multiorg IN VARCHAR2 := 'N',
2008 x_return_status OUT NOCOPY VARCHAR2,
2009 x_msg_count OUT NOCOPY NUMBER,
2010 x_msg_data OUT NOCOPY VARCHAR2,
2011 x_srp OUT NOCOPY srp_role_info_tbl_type,
2012 x_returned_rows OUT NOCOPY number) IS
2013
2014 l_api_name CONSTANT VARCHAR2(30) := 'Get_MO_desc_role_info';
2015 l_api_version CONSTANT NUMBER := 1.0;
2016
2017 l_counter NUMBER(15) := 0;
2018 l_group input_group_type;
2019 l_descendant_group group_tbl_type;
2020 l_mgr_flag VARCHAR2(1) := 'N';
2021 l_srp_tbl srp_role_group_tbl_type;
2022 l_returned_rows number;
2023
2024
2025 CURSOR members_csr(p_group_id NUMBER) IS
2026 SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
2027 g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
2028 org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
2029 FROM cn_qm_mgr_srp_groups g,
2030 cn_srp_role_dtls_v srd
2031 WHERE g.comp_group_id = p_group_id
2032 AND g.srp_role_id = srd.srp_role_id
2033 AND (trunc(srd.start_date) <= p_srp.effective_date)
2034 AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
2035 AND (trunc(g.start_date_active) <= p_srp.effective_date)
2036 AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
2037 AND srd.job_title_id <> -99
2038 ORDER BY g.manager_flag DESC;
2039
2040 CURSOR mgr_check IS
2041 SELECT manager_flag
2042 FROM cn_qm_mgr_groups
2043 WHERE comp_group_id = p_srp.group_id
2044 AND manager_srp_id = p_srp.salesrep_id
2045 AND (trunc(start_date_active) <= p_srp.effective_date)
2046 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
2047
2048 -- COLLAPSED cn_qm_mgr_srp_groups
2049 CURSOR mo_members_csr(p_group_id NUMBER) IS
2050 SELECT srdd.srp_role_id, srdd.srp_id, srdd.job_title_id, srdd.overlay_flag, srdd.non_std_flag,
2051 srdd.role_id role_id,srdd.job_discretion, srdd.status, srdd.plan_activate_status, srdd.club_eligible_flag,
2052 srdd.org_code,trunc(srdd.start_date) start_date, trunc(srdd.end_date) end_date,
2053 jgm.role_name role_name,jgm.group_id group_id
2054 FROM
2055 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
2056 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u ,
2057 cn_srp_role_dtls_v srdd
2058 WHERE
2059 jg.group_id = jgm.group_id
2060 and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
2061 and jrs.resource_id = jgm.resource_id
2062 and u.group_id = jgm.group_id
2063 and u.usage = 'SF_PLANNING'
2064 and jrr.role_resource_type = 'RS_INDIVIDUAL'
2065 and jrr.role_resource_id = jrs.resource_id
2066 and jrr.role_id = jgm.role_id
2067 and jrr.role_id = jr.role_id
2068 and jr.role_type_code = 'SALES_COMP'
2069 and jrr.delete_flag <> 'Y'
2070 and jrr.start_date_active <= jgm.start_date_active
2071 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
2072 AND jrs.SALESREP_ID > 0
2073 AND srdd.srp_role_id = jrr.role_relate_id
2074 AND jgm.group_id = p_group_id -- Added before this.
2075 AND jrr.role_relate_id = srdd.srp_role_id
2076 AND (trunc(srdd.start_date) <= p_srp.effective_date)
2077 AND (srdd.end_date IS NULL OR trunc(srdd.end_date) >= p_srp.effective_date)
2078 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
2079 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date)
2080 AND srdd.job_title_id <> -99
2081 ORDER BY jgm.manager_flag DESC;
2082
2083 -- COLLAPSED cn_qm_mgr_srp_groups
2084 CURSOR mo_mgr_check IS
2085 SELECT jgm.manager_flag manager_flag
2086 FROM
2087 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
2088 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
2089 cn_srp_role_dtls srd
2090 WHERE
2091 jg.group_id = jgm.group_id
2092 and jgm.manager_flag = 'Y'
2093 and jrs.resource_id = jgm.resource_id
2094 and u.group_id = jgm.group_id
2095 and u.usage = 'SF_PLANNING'
2096 and jrr.role_resource_type = 'RS_INDIVIDUAL'
2097 and jrr.role_resource_id = jrs.resource_id
2098 and jrr.role_id = jgm.role_id
2099 and jrr.role_id = jr.role_id
2100 and jr.role_type_code = 'SALES_COMP'
2101 and jrr.delete_flag <> 'Y'
2102 and jrr.start_date_active <= jgm.start_date_active
2103 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
2104 AND jrs.SALESREP_ID > 0
2105 AND srd.srp_role_id = jrr.role_relate_id
2106 AND jgm.group_id = p_srp.group_id
2107 AND jrs.salesrep_id = p_srp.salesrep_id
2108 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
2109 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);
2110
2111 TYPE RC_TYPE IS REF CURSOR;
2112 TYPE RET_RC_TYPE IS REF CURSOR RETURN members_csr%ROWTYPE;
2113 member_csr_rec members_csr%ROWTYPE;
2114 eachmem members_csr%ROWTYPE;
2115 eachsrp members_csr%ROWTYPE;
2116
2117 mgr_check_rc RC_TYPE;
2118 members_csr_rc RET_RC_TYPE;
2119 BEGIN
2120 -- Standard Start of API savepoint
2121 SAVEPOINT Get_MO_desc_role_info;
2122
2123 -- Standard call to check for call compatibility.
2124 IF NOT FND_API.Compatible_API_Call
2125 (l_api_version ,
2126 p_api_version ,
2127 l_api_name ,
2128 G_PKG_NAME )
2129 THEN
2130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2131 END IF;
2132
2133 -- Initialize message list if p_init_msg_list is set to TRUE.
2134 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2135 FND_MSG_PUB.initialize;
2136 END IF;
2137
2138 -- Initialize API return status to success
2139 x_return_status := FND_API.G_RET_STS_SUCCESS;
2140 -- API body
2141
2142 -- if p_srp.salesrep_id is a manager, get the other members of his own group
2143 /*
2144 OPEN mgr_check;
2145 FETCH mgr_check INTO l_mgr_flag;
2146 CLOSE mgr_check;
2147 */
2148
2149 IF p_is_multiorg = 'Y' THEN
2150 OPEN mgr_check_rc FOR
2151 SELECT jgm.manager_flag manager_flag
2152 FROM
2153 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
2154 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u,
2155 cn_srp_role_dtls srd
2156 WHERE
2157 jg.group_id = jgm.group_id
2158 and jgm.manager_flag = 'Y'
2159 and jrs.resource_id = jgm.resource_id
2160 and u.group_id = jgm.group_id
2161 and u.usage = 'SF_PLANNING'
2162 and jrr.role_resource_type = 'RS_INDIVIDUAL'
2163 and jrr.role_resource_id = jrs.resource_id
2164 and jrr.role_id = jgm.role_id
2165 and jrr.role_id = jr.role_id
2166 and jr.role_type_code = 'SALES_COMP'
2167 and jrr.delete_flag <> 'Y'
2168 and jrr.start_date_active <= jgm.start_date_active
2169 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
2170 AND jrs.SALESREP_ID > 0
2171 AND srd.srp_role_id = jrr.role_relate_id
2172 AND jgm.group_id = p_srp.group_id
2173 AND jrs.salesrep_id = p_srp.salesrep_id
2174 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
2175 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date);
2176 ELSE
2177 OPEN mgr_check_rc FOR SELECT manager_flag
2178 FROM cn_qm_mgr_groups
2179 WHERE comp_group_id = p_srp.group_id
2180 AND manager_srp_id = p_srp.salesrep_id
2181 AND (trunc(start_date_active) <= p_srp.effective_date)
2182 AND (end_date_active IS NULL OR trunc(end_date_active) >= p_srp.effective_date);
2183 END IF;
2184
2185 -- Cursor mgr_check_rc already opened above
2186 LOOP
2187 FETCH mgr_check_rc INTO l_mgr_flag;
2188 EXIT WHEN mgr_check_rc%NOTFOUND;
2189 END LOOP;
2190 CLOSE mgr_check_rc;
2191
2192
2193 IF l_mgr_flag = 'Y' THEN
2194
2195 IF p_is_multiorg = 'Y' THEN
2196 OPEN members_csr_rc FOR SELECT srdd.srp_role_id, srdd.srp_id, srdd.job_title_id, srdd.overlay_flag, srdd.non_std_flag,
2197 srdd.role_id role_id,jgm.role_name role_name,srdd.job_discretion, srdd.status, srdd.plan_activate_status, srdd.club_eligible_flag,
2198 srdd.org_code,trunc(srdd.start_date) start_date, trunc(srdd.end_date) end_date,
2199 jgm.group_id group_id
2200 FROM
2201 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
2202 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u ,
2203 cn_srp_role_dtls_v srdd
2204 WHERE
2205 jg.group_id = jgm.group_id
2206 and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
2207 and jrs.resource_id = jgm.resource_id
2208 and u.group_id = jgm.group_id
2209 and u.usage = 'SF_PLANNING'
2210 and jrr.role_resource_type = 'RS_INDIVIDUAL'
2211 and jrr.role_resource_id = jrs.resource_id
2212 and jrr.role_id = jgm.role_id
2213 and jrr.role_id = jr.role_id
2214 and jr.role_type_code = 'SALES_COMP'
2215 and jrr.delete_flag <> 'Y'
2216 and jrr.start_date_active <= jgm.start_date_active
2217 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
2218 AND jrs.SALESREP_ID > 0
2219 AND srdd.srp_role_id = jrr.role_relate_id
2220 AND jgm.group_id = p_srp.group_id -- Added before this.
2221 AND jrr.role_relate_id = srdd.srp_role_id
2222 AND (trunc(srdd.start_date) <= p_srp.effective_date)
2223 AND (srdd.end_date IS NULL OR trunc(srdd.end_date) >= p_srp.effective_date)
2224 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
2225 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date)
2226 AND srdd.job_title_id <> -99
2227 ORDER BY jgm.manager_flag DESC;
2228 ELSE
2229 OPEN members_csr_rc FOR SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
2230 g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
2231 org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
2232 FROM cn_qm_mgr_srp_groups g,
2233 cn_srp_role_dtls_v srd
2234 WHERE g.comp_group_id = p_srp.group_id
2235 AND g.srp_role_id = srd.srp_role_id
2236 AND (trunc(srd.start_date) <= p_srp.effective_date)
2237 AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
2238 AND (trunc(g.start_date_active) <= p_srp.effective_date)
2239 AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
2240 AND srd.job_title_id <> -99
2241 ORDER BY g.manager_flag DESC;
2242 END IF;
2243
2244 l_counter := 0;
2245 LOOP
2246 FETCH members_csr_rc INTO eachmem;
2247 EXIT WHEN members_csr_rc%NOTFOUND;
2248
2249 IF (eachmem.start_date <= p_srp.effective_date AND
2250 (eachmem.end_date IS NULL
2251 OR eachmem.end_date >= p_srp.effective_date))
2252 AND
2253 (
2254 (p_return_current = 'N' AND eachmem.srp_id <> p_srp.salesrep_id )
2255 OR (p_return_current = 'Y')
2256 )
2257 THEN
2258
2259
2260 l_counter := l_counter + 1;
2261
2262
2263 x_srp(l_counter).srp_role_id := eachmem.srp_role_id ;
2264 x_srp(l_counter).srp_id := eachmem.srp_id ;
2265 x_srp(l_counter).overlay_flag := eachmem.overlay_flag ;
2266 x_srp(l_counter).non_std_flag := eachmem.non_std_flag ;
2267 x_srp(l_counter).role_id := eachmem.role_id ;
2268 x_srp(l_counter).role_name := eachmem.role_name ;
2269 x_srp(l_counter).job_title_id := eachmem.job_title_id ;
2270 x_srp(l_counter).job_discretion := eachmem.job_discretion ;
2271 x_srp(l_counter).status := eachmem.status ;
2272 x_srp(l_counter).plan_activate_status := eachmem.plan_activate_status;
2273 x_srp(l_counter).club_eligible_flag := eachmem.club_eligible_flag;
2274 x_srp(l_counter).org_code := eachmem.org_code ;
2275 x_srp(l_counter).start_date := eachmem.start_date ;
2276 x_srp(l_counter).end_date := eachmem.end_date ;
2277 x_srp(l_counter).group_id := eachmem.group_id ;
2278
2279
2280 END IF;
2281 END LOOP;
2282 CLOSE members_csr_rc;
2283
2284
2285 -- initialize l_group
2286 l_group.group_id := p_srp.group_id;
2287 l_group.effective_date := p_srp.effective_date;
2288
2289 get_descendant_group
2290 ( p_api_version => 1.0,
2291 x_return_status => x_return_status,
2292 x_msg_count => x_msg_count,
2293 x_msg_data => x_msg_data,
2294 p_group => l_group,
2295 x_group => l_descendant_group,
2296 p_level => 0);
2297
2298
2299 IF x_return_status = FND_API.g_ret_sts_error THEN
2300 RAISE FND_API.g_exc_error;
2301 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2302 RAISE FND_API.g_exc_unexpected_error;
2303 END IF;
2304
2305
2306 IF (l_descendant_group.COUNT > 0) THEN
2307 FOR eachgroup IN l_descendant_group.first .. l_descendant_group.last LOOP
2308 IF p_is_multiorg = 'Y' THEN
2309 OPEN members_csr_rc FOR SELECT srdd.srp_role_id, srdd.srp_id, srdd.job_title_id, srdd.overlay_flag, srdd.non_std_flag,
2310 srdd.role_id role_id,jgm.role_name role_name,srdd.job_discretion, srdd.status, srdd.plan_activate_status, srdd.club_eligible_flag,
2311 srdd.org_code,trunc(srdd.start_date) start_date, trunc(srdd.end_date) end_date,
2312 jgm.group_id group_id
2313 FROM
2314 jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs,
2315 jtf_rs_roles_b jr,jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u ,
2316 cn_srp_role_dtls_v srdd
2317 WHERE
2318 jg.group_id = jgm.group_id
2319 and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
2320 and jrs.resource_id = jgm.resource_id
2321 and u.group_id = jgm.group_id
2322 and u.usage = 'SF_PLANNING'
2323 and jrr.role_resource_type = 'RS_INDIVIDUAL'
2324 and jrr.role_resource_id = jrs.resource_id
2325 and jrr.role_id = jgm.role_id
2326 and jrr.role_id = jr.role_id
2327 and jr.role_type_code = 'SALES_COMP'
2328 and jrr.delete_flag <> 'Y'
2329 and jrr.start_date_active <= jgm.start_date_active
2330 and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active)
2331 AND jrs.SALESREP_ID > 0
2332 AND srdd.srp_role_id = jrr.role_relate_id
2333 AND jgm.group_id = l_descendant_group(eachgroup).group_id -- Added before this.
2334 AND jrr.role_relate_id = srdd.srp_role_id
2335 AND (trunc(srdd.start_date) <= p_srp.effective_date)
2336 AND (srdd.end_date IS NULL OR trunc(srdd.end_date) >= p_srp.effective_date)
2337 AND (trunc(jgm.start_date_active) <= p_srp.effective_date)
2338 AND (jgm.end_date_active IS NULL OR trunc(jgm.end_date_active) >= p_srp.effective_date)
2339 AND srdd.job_title_id <> -99
2340 ORDER BY jgm.manager_flag DESC;
2341 ELSE
2342 OPEN members_csr_rc FOR SELECT srd.srp_role_id, srd.srp_id, job_title_id, overlay_flag, non_std_flag, srd.role_id role_id,
2343 g.role_name role_name, job_discretion, status, plan_activate_status, club_eligible_flag,
2344 org_code, trunc(start_date) start_date, trunc(end_date) end_date, g.comp_group_id group_id
2345 FROM cn_qm_mgr_srp_groups g,
2346 cn_srp_role_dtls_v srd
2347 WHERE g.comp_group_id = l_descendant_group(eachgroup).group_id
2348 AND g.srp_role_id = srd.srp_role_id
2349 AND (trunc(srd.start_date) <= p_srp.effective_date)
2350 AND (srd.end_date IS NULL OR trunc(srd.end_date) >= p_srp.effective_date)
2351 AND (trunc(g.start_date_active) <= p_srp.effective_date)
2352 AND (g.end_date_active IS NULL OR trunc(g.end_date_active) >= p_srp.effective_date)
2353 AND srd.job_title_id <> -99
2354 ORDER BY g.manager_flag DESC;
2355 END IF;
2356
2357 LOOP
2358 FETCH members_csr_rc INTO eachsrp;
2359 EXIT WHEN members_csr_rc%NOTFOUND;
2360 IF (eachsrp.start_date <= p_srp.effective_date AND
2361 (eachsrp.end_date IS NULL
2362 OR eachsrp.end_date >= p_srp.effective_date)) THEN
2363
2364 l_counter := l_counter + 1;
2365 x_srp(l_counter).srp_role_id := eachsrp.srp_role_id ;
2366 x_srp(l_counter).srp_id := eachsrp.srp_id ;
2367 x_srp(l_counter).overlay_flag := eachsrp.overlay_flag ;
2368 x_srp(l_counter).non_std_flag := eachsrp.non_std_flag ;
2369 x_srp(l_counter).role_id := eachsrp.role_id ;
2370 x_srp(l_counter).role_name := eachsrp.role_name ;
2371 x_srp(l_counter).job_title_id := eachsrp.job_title_id ;
2372 x_srp(l_counter).job_discretion := eachsrp.job_discretion ;
2373 x_srp(l_counter).status := eachsrp.status ;
2374 x_srp(l_counter).plan_activate_status := eachsrp.plan_activate_status;
2375 x_srp(l_counter).club_eligible_flag := eachsrp.club_eligible_flag;
2376 x_srp(l_counter).org_code := eachsrp.org_code ;
2377 x_srp(l_counter).start_date := eachsrp.start_date ;
2378 x_srp(l_counter).end_date := eachsrp.end_date ;
2379 x_srp(l_counter).group_id := eachsrp.group_id ;
2380 END IF; -- end of check date_range_overlap
2381 END LOOP; -- end of eachsrp
2382 CLOSE members_csr_rc;
2383 END LOOP; -- end of eachgroup
2384 END IF;
2385
2386 END IF;
2387
2388 -- End of API body.
2389
2390 -- Standard check of p_commit.
2391 IF FND_API.To_Boolean( p_commit ) THEN
2392 COMMIT WORK;
2393 END IF;
2394
2395 -- Standard call to get message count and if count is 1, get message info.
2396 FND_MSG_PUB.Count_And_Get
2397 (p_count => x_msg_count ,
2398 p_data => x_msg_data );
2399
2400 EXCEPTION
2401 WHEN FND_API.G_EXC_ERROR THEN
2402 ROLLBACK TO Get_MO_desc_role_info;
2403 x_return_status := FND_API.G_RET_STS_ERROR ;
2404 FND_MSG_PUB.Count_And_Get
2405 (p_count => x_msg_count ,
2406 p_data => x_msg_data );
2407
2408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2409 ROLLBACK TO Get_MO_desc_role_info;
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2411 FND_MSG_PUB.Count_And_Get
2412 (p_count => x_msg_count ,
2413 p_data => x_msg_data );
2414
2415 WHEN OTHERS THEN
2416 ROLLBACK TO Get_MO_desc_role_info;
2417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2418 IF FND_MSG_PUB.Check_Msg_Level
2419 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2420 THEN
2421 FND_MSG_PUB.Add_Exc_Msg
2422 (G_PKG_NAME ,
2423 l_api_name );
2424 END IF;
2425 FND_MSG_PUB.Count_And_Get
2426 (p_count => x_msg_count ,
2427 p_data => x_msg_data );
2428
2429 END Get_MO_desc_role_info;
2430
2431 --}}}
2432
2433
2434
2435 --{{{ get_all_managers
2436 -- Start of comments
2437 -- API name : Get_All_Managers
2438 -- Type : Private.
2439 -- Function :
2440 -- Pre-reqs : None.
2441 -- Parameters :
2442 -- IN : p_api_version IN NUMBER Required
2443 -- p_init_msg_list IN VARCHAR2 Optional
2444 -- Default = FND_API.G_FALSE
2445 -- p_commit IN VARCHAR2 Optional
2446 -- Default = FND_API.G_FALSE
2447 -- p_validation_level IN NUMBER Optional
2448 -- Default = FND_API.G_VALID_LEVEL_FULL
2449 -- p_srp IN srp_group_rec_type Required
2450 -- p_effective_date IN DATE Required
2451 -- OUT : x_return_status OUT VARCHAR2(1)
2452 -- x_msg_count OUT NUMBER
2453 -- x_msg_data OUT VARCHAR2(2000)
2454 -- x_srp OUT srp_role_group_tbl_type
2455 -- x_returned_rows OUT INTEGER
2456 -- Version : Current version 1.0
2457 -- Initial version 1.0
2458 --
2459 -- Notes : Note text
2460 --
2461 -- End of comments
2462 PROCEDURE Get_All_Managers
2463 (p_api_version IN NUMBER,
2464 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2465 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2466 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
2467 p_srp IN srp_group_rec_type,
2468 x_return_status OUT NOCOPY VARCHAR2,
2469 x_msg_count OUT NOCOPY NUMBER,
2470 x_msg_data OUT NOCOPY VARCHAR2,
2471 x_srp OUT NOCOPY srp_role_group_tbl_type,
2472 x_returned_rows OUT NOCOPY number) IS
2473
2474 l_api_name CONSTANT VARCHAR2(30) := 'get_all_managers';
2475 l_api_version CONSTANT NUMBER := 1.0;
2476
2477 l_comp_group_id number := 0;
2478 l_counter NUMBER(15);
2479 l_group input_group_type;
2480 l_ancestor_group group_tbl_type;
2481 l_srp_tbl srp_role_group_tbl_type;
2482 l_returned_rows number;
2483
2484 CURSOR l_mgr_group_csr is
2485 SELECT 1
2486 FROM cn_qm_mgr_groups
2487 WHERE manager_srp_id = p_srp.salesrep_id;
2488
2489 CURSOR l_parent_group_csr(p_group_id NUMBER) is
2490 SELECT parent_comp_group_id
2491 FROM cn_qm_group_hier
2492 WHERE comp_group_id = p_group_id
2493 AND trunc(start_date_active) <= p_srp.effective_date
2494 AND (trunc(end_date_active) >= p_srp.effective_date
2495 OR end_date_active IS NULL);
2496
2497 CURSOR managers_csr(p_group_id NUMBER) IS
2498 SELECT manager_srp_id salesrep_id, role_id ,comp_group_id,
2499 trunc(start_date_active) start_date_active,
2500 trunc(end_date_active) end_date_active
2501 FROM cn_qm_mgr_groups
2502 WHERE comp_group_id = p_group_id
2503 AND manager_srp_id <> p_srp.salesrep_id
2504 AND manager_flag = 'Y';
2505
2506 BEGIN
2507 -- Standard Start of API savepoint
2508 SAVEPOINT get_all_managers;
2509
2510 -- Standard call to check for call compatibility.
2511 IF NOT FND_API.Compatible_API_Call
2512 (l_api_version ,
2513 p_api_version ,
2514 l_api_name ,
2515 G_PKG_NAME )
2516 THEN
2517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2518 END IF;
2519
2520 -- Initialize message list if p_init_msg_list is set to TRUE.
2521 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2522 FND_MSG_PUB.initialize;
2523 END IF;
2524
2525 -- Initialize API return status to success
2526 x_return_status := FND_API.G_RET_STS_SUCCESS;
2527 -- API body
2528
2529 l_counter := 0;
2530
2531 -- find IF this salesrep is a manager
2532 OPEN l_mgr_group_csr;
2533 FETCH l_mgr_group_csr INTO l_counter;
2534 CLOSE l_mgr_group_csr;
2535
2536 IF (l_counter <> 0) THEN
2537 OPEN l_parent_group_csr(p_srp.group_id);
2538 FETCH l_parent_group_csr INTO l_comp_group_id;
2539 CLOSE l_parent_group_csr;
2540 ELSE
2541 l_comp_group_id := p_srp.group_id;
2542 END IF;
2543
2544 l_counter := 0;
2545 -- find all managers in this group
2546
2547 FOR eachmgr IN managers_csr(l_comp_group_id) LOOP
2548
2549 IF (eachmgr.start_date_active <= p_srp.effective_date AND
2550 (eachmgr.end_date_active IS NULL
2551 OR eachmgr.end_date_active >= p_srp.effective_date)) THEN
2552
2553 l_counter := l_counter + 1;
2554 x_srp(l_counter).salesrep_id := eachmgr.salesrep_id;
2555 x_srp(l_counter).group_id := l_comp_group_id;
2556 x_srp(l_counter).role_id := eachmgr.role_id;
2557 x_srp(l_counter).start_date := eachmgr.start_date_active;
2558 x_srp(l_counter).end_date := eachmgr.end_date_active;
2559 x_srp(l_counter).mgr_srp_id := 0;
2560 x_srp(l_counter).hier_level := 0;
2561 END IF;
2562
2563 END LOOP;
2564
2565 -- loop through reach ancestor group.
2566 l_group.group_id := l_comp_group_id;
2567 l_group.effective_date := p_srp.effective_date;
2568
2569 get_ancestor_group
2570 ( p_api_version => 1.0,
2571 x_return_status => x_return_status,
2572 x_msg_count => x_msg_count,
2573 x_msg_data => x_msg_data,
2574 p_group => l_group,
2575 x_group => l_ancestor_group,
2576 p_level => 0);
2577
2578 IF x_return_status = FND_API.g_ret_sts_error THEN
2579
2580 RAISE FND_API.g_exc_error;
2581
2582 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2583
2584 RAISE FND_API.g_exc_unexpected_error;
2585
2586 END IF;
2587
2588 IF( l_ancestor_group.COUNT > 0) THEN
2589 FOR eachgroup IN l_ancestor_group.first .. l_ancestor_group.last LOOP
2590
2591 FOR eachsrp IN managers_csr(l_ancestor_group(eachgroup).group_id) LOOP
2592
2593 IF (eachsrp.start_date_active <= p_srp.effective_date AND
2594 (eachsrp.end_date_active IS NULL
2595 OR eachsrp.end_date_active >= p_srp.effective_date)) THEN
2596
2597 l_counter := l_counter + 1;
2598 x_srp(l_counter).salesrep_id := eachsrp.salesrep_id;
2599 x_srp(l_counter).group_id :=
2600 l_ancestor_group(eachgroup).group_id;
2601 x_srp(l_counter).role_id := eachsrp.role_id;
2602 x_srp(l_counter).start_date := eachsrp.start_date_active;
2603 x_srp(l_counter).end_date := eachsrp.end_date_active;
2604 x_srp(l_counter).mgr_srp_id := 0;
2605 x_srp(l_counter).hier_level :=
2606 l_ancestor_group(eachgroup).hier_level;
2607 END IF; -- end of check date_range_overlap
2608 END LOOP; -- end of eachsrp
2609 END LOOP; -- end of eachgroup
2610 END IF;
2611
2612
2613 -- End of API body.
2614
2615 -- Standard check of p_commit.
2616 IF FND_API.To_Boolean( p_commit ) THEN
2617 COMMIT WORK;
2618 END IF;
2619
2620 -- Standard call to get message count and if count is 1, get message info.
2621 FND_MSG_PUB.Count_And_Get
2622 (p_count => x_msg_count ,
2623 p_data => x_msg_data );
2624
2625 EXCEPTION
2626 WHEN FND_API.G_EXC_ERROR THEN
2627 ROLLBACK TO get_all_managers;
2628 x_return_status := FND_API.G_RET_STS_ERROR ;
2629 FND_MSG_PUB.Count_And_Get
2630 (p_count => x_msg_count ,
2631 p_data => x_msg_data );
2632
2633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2634 ROLLBACK TO get_all_managers;
2635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2636 FND_MSG_PUB.Count_And_Get
2637 (p_count => x_msg_count ,
2638 p_data => x_msg_data );
2639
2640 WHEN OTHERS THEN
2641 ROLLBACK TO get_all_managers;
2642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2643 IF FND_MSG_PUB.Check_Msg_Level
2644 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2645 THEN
2646 FND_MSG_PUB.Add_Exc_Msg
2647 (G_PKG_NAME ,
2648 l_api_name );
2649 END IF;
2650 FND_MSG_PUB.Count_And_Get
2651 (p_count => x_msg_count ,
2652 p_data => x_msg_data );
2653
2654 END Get_All_Managers;
2655 --}}}
2656
2657 END cn_srp_hier_proc_pvt;