[Home] [Help]
PACKAGE BODY: APPS.CN_OVER_ASSIGN_PVT
Source
1 PACKAGE BODY cn_over_assign_pvt AS
2 -- $Header: cnvoasgb.pls 115.15 2003/03/20 22:13:36 sbadami ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_OVER_ASSIGN_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvoasgb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12 TYPE quota_amount_rec_type IS RECORD
13 ( quota_category_id cn_srp_quota_cates.quota_category_id%TYPE,
14 quota_amount cn_srp_quota_cates.amount%TYPE,
15 planning_amt cn_srp_quota_cates.planning_amt%TYPE,
16 prorated_amount cn_srp_quota_cates.prorated_amount%TYPE);
17
18 TYPE quota_amount_tbl_type IS TABLE OF quota_amount_rec_type
19 INDEX BY BINARY_INTEGER;
20
21 --======================================================================
22 -- Start of private procedure
23 --======================================================================
24 -- Procedure : add_quota
25 -- ========================
26 PROCEDURE add_quota
27 (p_srp_role_id IN NUMBER,
28 p_start_date IN DATE ,
29 p_end_date IN DATE ,
30 x_quota IN OUT NOCOPY quota_amount_tbl_type) IS
31
32 CURSOR srp_role_info IS
33 SELECT Trunc(start_date) start_date,
34 Nvl(Trunc(end_date),p_end_date) end_date
35 FROM cn_srp_role_dtls_v
36 WHERE srp_role_id = p_srp_role_id;
37
38 CURSOR c_quota_amount
39 (c_srp_role_id NUMBER,
40 c_quota_category_id NUMBER) IS
41 SELECT Nvl(amount,0) amount,Nvl(planning_amt,0) planning_amt,Nvl(prorated_amount,0) prorated_amount
42 FROM cn_srp_quota_cates
43 WHERE srp_role_id = c_srp_role_id
44 AND quota_category_id = c_quota_category_id;
45
46 l_sr_start_date DATE;
47 l_sr_end_date DATE;
48 l_quota_amount cn_srp_quota_cates.amount%TYPE;
49 l_planning_amt cn_srp_quota_cates.planning_amt%TYPE;
50 l_prorated_amount cn_srp_quota_cates.prorated_amount%TYPE;
51 BEGIN
52
53 -- Get plan assign start/end period
54 OPEN srp_role_info;
55 FETCH srp_role_info INTO l_sr_start_date , l_sr_end_date;
56
57 -- cn_qm_mgr_srp_groups(cn_srp_role) end_date cannot be null
58 IF l_sr_end_date IS NULL THEN
59 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
60 FND_MESSAGE.SET_NAME ('CN' , 'CN_QM_SR_ED_NULL');
61 FND_MSG_PUB.Add;
62 END IF;
63 RAISE FND_API.G_EXC_ERROR;
64 END IF;
65
66 IF srp_role_info%notfound THEN
67 -- invalid srp_role_id
68 RAISE FND_API.G_EXC_ERROR;
69 END IF;
70 CLOSE srp_role_info;
71
72 IF x_quota.COUNT > 0 THEN
73 FOR i IN x_quota.first..x_quota.last LOOP
74
75 OPEN c_quota_amount(p_srp_role_id,
76 x_quota(i).quota_category_id);
77 FETCH c_quota_amount INTO l_quota_amount,l_planning_amt,l_prorated_amount;
78
79 IF (c_quota_amount%notfound) THEN
80
81 -- This plan doesn't have this category
82 -- do nothing
83 NULL;
84
85 ELSE
86 -- add pro-rated quota
87 x_quota(i).quota_amount := x_quota(i).quota_amount
88 + Trunc((p_end_date - p_start_date + 1),0)/Trunc((l_sr_end_date - l_sr_start_date + 1),0) * l_quota_amount;
89 x_quota(i).planning_amt := x_quota(i).planning_amt
90 + Trunc((p_end_date - p_start_date + 1),0)/Trunc((l_sr_end_date - l_sr_start_date + 1),0) * l_planning_amt;
91 x_quota(i).prorated_amount := x_quota(i).prorated_amount
92 + Trunc((p_end_date - p_start_date + 1),0)/Trunc((l_sr_end_date - l_sr_start_date + 1),0) * l_prorated_amount;
93 END IF;
94
95
96 CLOSE c_quota_amount;
97
98 END LOOP; -- end of each quota category
99 END IF;
100 END add_quota;
101
102 -- =======================================
103 -- Procedure : down_one_level
104 -- =======================================
105 PROCEDURE down_one_level
106 (p_srp_id IN NUMBER,
107 p_comp_group_id IN NUMBER,
108 p_start_date IN DATE ,
109 p_end_date IN DATE ,
110 p_org_code IN VARCHAR2,
111 p_parent_direct_exist IN NUMBER,
112 x_child_street_exist OUT NOCOPY NUMBER,
113 x_quota IN OUT NOCOPY quota_amount_tbl_type) IS
114
115 CURSOR direct_comp_group
116 (c_parent_comp_group_id NUMBER,c_start_date DATE ,c_end_date DATE ) IS
117 SELECT comp_group_id,
118 Greatest(Trunc(start_date_active), c_start_date) start_date,
119 Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date) end_date
120 FROM cn_qm_group_hier
121 WHERE parent_comp_group_id = c_parent_comp_group_id
122 AND Greatest(Trunc(start_date_active), c_start_date) <=
123 Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date)
124 ;
125
126 CURSOR direct_reps
127 (c_srp_id NUMBER,c_comp_group_id NUMBER,
128 c_start_date DATE ,c_end_date DATE ) IS
129 SELECT
130 msg.qm_mgr_srp_group_id ,
131 msg.srp_id,
132 msg.resource_id ,
133 msg.comp_group_id ,
134 msg.srp_role_id,
135 Greatest(Trunc(msg.start_date_active), c_start_date) start_date,
136 Least(Nvl(Trunc(msg.end_date_active),c_end_date),
137 c_end_date) end_date,
138 msg.manager_flag,
139 msg.member_flag
140 FROM
141 cn_qm_mgr_srp_groups msg
142 WHERE
143 msg.comp_group_id = c_comp_group_id
144 AND Greatest(Trunc(msg.start_date_active), c_start_date) <=
145 Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date)
146 AND msg.srp_id <> c_srp_id
147 ORDER BY msg.manager_flag
148 ;
149
150 CURSOR plan_assign
151 (c_srp_id NUMBER,c_start_date DATE,c_end_date DATE) IS
152
153 SELECT srp_role_id,
154 Greatest(c_start_date,Trunc(start_date)) start_date,
155 Least(Nvl(Trunc(end_date),c_end_date),c_end_date) end_date
156 FROM cn_srp_role_dtls_v
157 WHERE srp_id = c_srp_id
158 AND role_model_id is null -- "CHANGED FOR MODELING IMPACT"
159 AND overlay_flag = 'N'
160 AND Greatest(Trunc(start_date),c_start_date) <=
161 Least(Nvl(Trunc(end_date),c_end_date),c_end_date);
162
163 l_direct_exist NUMBER;
164 l_street_exist NUMBER;
165 l_check NUMBER;
166
167 BEGIN
168
169 l_direct_exist := 0;
170 l_street_exist := 0;
171 x_child_street_exist := 0;
172 -- find all all direct comp group
173 FOR eachcg IN direct_comp_group (p_comp_group_id,p_start_date,p_end_date)
174 LOOP
175
176 -- find out all directs
177 FOR eachdirect IN direct_reps
178 (p_srp_id,eachcg.comp_group_id,
179 eachcg.start_date,eachcg.end_date)LOOP
180 l_check := 0;
181 BEGIN
182 SELECT 1 INTO l_check
183 FROM cn_srp_role_dtls
184 WHERE srp_role_id = eachdirect.srp_role_id
185 AND org_code = p_org_code
186 AND overlay_flag = 'N'
187 ;
188 EXCEPTION
189 WHEN no_data_found THEN
190 l_check := 0;
191 END;
192 IF (l_check = 1) THEN
193 FOR eachplan IN plan_assign
194 (eachdirect.srp_id,eachdirect.start_date,
195 eachdirect.end_date) LOOP
196
197 IF (eachdirect.manager_flag = 'Y') THEN
198 down_one_level(eachdirect.srp_id,
199 eachdirect.comp_group_id,
200 eachdirect.start_date,
201 eachdirect.end_date,
202 p_org_code,
203 l_direct_exist,
204 x_child_street_exist,
205 x_quota);
206 IF x_child_street_exist = 1 THEN
207 l_street_exist := 1;
208 END IF;
209 ELSE
210 -- This is a street rep, add his quota to x_quota
211 -- add quota to x_quota
212 add_quota(eachplan.srp_role_id,
213 eachplan.start_date,
214 eachplan.end_date,
215 x_quota);
216 l_direct_exist := 1;
217 END IF;
218
219 END LOOP;
220 END IF;
221 END LOOP; -- end of eachdirect
222 END LOOP; -- end of eachcg
223
224 IF l_direct_exist = 0 AND p_parent_direct_exist = 0 THEN
225 -- This is a street rep, add his quota to x_quota
226 FOR eachplan IN plan_assign(p_srp_id,p_start_date,p_end_date) LOOP
227 -- add quota to x_quota
228 add_quota(eachplan.srp_role_id,
229 eachplan.start_date,
230 eachplan.end_date,
231 x_quota);
232 l_direct_exist := 1;
233 END LOOP; -- End of eachplan
234 END IF; -- end of street rep check
235
236 SELECT Greatest(l_street_exist,l_direct_exist)
237 INTO x_child_street_exist
238 FROM dual;
239
240 END down_one_level;
241
242 --======================================================================
243 -- End of private procedure
244 --======================================================================
245
246 -- API name : Get_overassign
247 -- Type : Public.
248 -- Pre-reqs :
249 -- Usage :
250 --
251 -- Desc :
252 --
253 --
254 --
255 -- Parameters :
256 -- IN : p_api_version NUMBER Require
257 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
258 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
259 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
260 -- OUT : x_return_status VARCHAR2(1)
261 -- x_msg_count NUMBER
262 -- x_msg_data VARCHAR2(2000)
263 -- IN : p_srp_role_id NUMBER, Required
264 -- p_org_code VARCHAR2, Required
265 -- p_cal_field IN VARCHAR2,
266 -- OUT : x_quota_overassign_tbl quota_overassign_tbl_type
267 --
268 --
269 -- Version : Current version 1.0
270 -- Initial version 1.0
271 --
272 -- Notes :
273 --
274 -- End of comments
275
276 PROCEDURE get_overassign
277 ( p_api_version IN NUMBER,
278 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
279 p_commit IN VARCHAR2 := FND_API.G_FALSE,
280 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 p_qm_mgr_srp_group_id IN NUMBER ,
285 p_org_code IN VARCHAR2,
286 x_quota_overassign_tbl OUT NOCOPY quota_overassign_tbl_type
287
288 ) IS
289
290 l_api_name CONSTANT VARCHAR2(30) := 'Get_Overassign';
291 l_api_version CONSTANT NUMBER :=1.0;
292
293 l_counter NUMBER(15);
294 l_match NUMBER(15);
295
296 l_multi_mgr_cnt NUMBER ;
297
298 l_mgr_quota quota_amount_tbl_type;
299 l_direct_quota quota_amount_tbl_type;
300 l_street_quota quota_amount_tbl_type;
301
302 CURSOR c_srp_info IS
303
304 SELECT srp_role_id,srp_id,role_id,
305 Trunc(start_date_active) start_date,
306 Trunc(end_date_active) end_date,comp_group_id ,
307 manager_flag,member_flag ,group_name
308 FROM cn_qm_mgr_srp_groups
309 WHERE qm_mgr_srp_group_id = p_qm_mgr_srp_group_id ;
310
311 l_srp_info c_srp_info%ROWTYPE;
312
313 -- the prorate_rate is not complete yet. Complete in the code below.
314 -- because cannot join with cn_srp_role_dtls_v here for performance issue
315 CURSOR mgr_srp_role_list
316 (c_comp_group_id NUMBER,c_start_date DATE, c_end_date DATE) IS
317 SELECT msg.srp_role_id,
318 (Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date) -
319 Greatest(Trunc(msg.start_date_active), c_start_date)) prorate_rate
320 FROM cn_qm_mgr_srp_groups msg
321 WHERE msg.manager_flag = 'Y'
322 AND msg.comp_group_id = c_comp_group_id
323 AND Greatest(Trunc(msg.start_date_active), c_start_date) <=
324 Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date)
325 ;
326
327 CURSOR mgr_quota
328 (c_srp_role_id NUMBER,c_prorate_rate NUMBER) IS
329 SELECT sqc.quota_category_id,
330 SUM(Nvl(sqc.amount,0) * c_prorate_rate) amount,
331 SUM(Nvl(sqc.planning_amt,0) * c_prorate_rate) planning_amt,
332 SUM(Nvl(sqc.prorated_amount,0) * c_prorate_rate) prorated_amount
333 FROM cn_srp_quota_cates sqc,cn_quota_categories qc
334 WHERE sqc.quota_category_id = qc.quota_category_id
335 AND qc.TYPE = 'VAR_QUOTA'
336 AND sqc.srp_role_id = c_srp_role_id
337 GROUP BY sqc.quota_category_id
338 ;
339
340 CURSOR direct_comp_group
341 (c_parent_comp_group_id NUMBER,c_start_date DATE ,c_end_date DATE ) IS
342 SELECT comp_group_id,
343 Greatest(Trunc(start_date_active), c_start_date) start_date,
344 Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date) end_date
345 FROM cn_qm_group_hier
346 WHERE parent_comp_group_id = c_parent_comp_group_id
347 AND Greatest(Trunc(start_date_active), c_start_date) <=
348 Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date)
349 ;
350
351 CURSOR direct_reps
352 (c_srp_id NUMBER,c_comp_group_id NUMBER,
353 c_start_date DATE ,c_end_date DATE ) IS
354 SELECT
355 msg.qm_mgr_srp_group_id ,
356 msg.srp_id,
357 msg.resource_id ,
358 msg.comp_group_id ,
359 msg.srp_role_id,
360 Greatest(Trunc(msg.start_date_active), c_start_date) start_date,
361 Least(Nvl(Trunc(msg.end_date_active),c_end_date),
362 c_end_date) end_date,
363 msg.manager_flag,
364 msg.member_flag
365 FROM
366 cn_qm_mgr_srp_groups msg
367 WHERE
368 msg.comp_group_id = c_comp_group_id
369 AND Greatest(Trunc(msg.start_date_active), c_start_date) <=
373 ;
370 Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date)
371 AND msg.srp_id <> c_srp_id
372 ORDER BY msg.manager_flag
374
375 CURSOR plan_assign
376 (c_srp_id NUMBER,c_start_date DATE,c_end_date DATE) IS
377
378 SELECT srp_role_id,
379 Greatest(c_start_date,Trunc(start_date)) start_date,
380 Least(Nvl(Trunc(end_date),c_end_date),c_end_date) end_date
381 FROM cn_srp_role_dtls_v
382 WHERE srp_id = c_srp_id
383 AND role_model_id is null -- "CHANGED FOR MODELING IMPACT"
384 AND overlay_flag = 'N'
385 AND Greatest(Trunc(start_date),c_start_date) <=
386 Least(Nvl(Trunc(end_date),c_end_date),c_end_date);
387
388 l_direct_exist NUMBER;
389 l_street_exist NUMBER;
390 l_check NUMBER;
391
392 BEGIN
393
394 -- Standard call to check for call compatibility.
395 IF NOT FND_API.Compatible_API_Call( l_api_version ,p_api_version ,
396 l_api_name ,G_PKG_NAME ) THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398 END IF;
399
400 -- Initialize message list if p_init_msg_list is set to TRUE.
401 IF FND_API.to_Boolean( p_init_msg_list ) THEN
402 FND_MSG_PUB.initialize;
403 END IF;
404
405 -- Initialize API return status to success
406 x_return_status := FND_API.G_RET_STS_SUCCESS;
407
408 -- get related information about this salesrep
409 OPEN c_srp_info;
410 FETCH c_srp_info INTO l_srp_info;
411
412 -- cn_qm_mgr_srp_groups(cn_srp_role) end_date cannot be null
413 IF l_srp_info.end_date IS NULL THEN
414 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
415 FND_MESSAGE.SET_NAME ('CN' , 'CN_QM_SR_ED_NULL');
416 FND_MSG_PUB.Add;
417 END IF;
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 IF c_srp_info%notfound THEN
422 -- invalid srp_role_id
423 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
424 FND_MESSAGE.SET_NAME ('CN' , 'CN_QM_INVALID_SRPROLE');
425 FND_MSG_PUB.Add;
426 END IF;
427 RAISE FND_API.G_EXC_ERROR;
428 END IF;
429 CLOSE c_srp_info;
430
431 -- Check if this srp is manager
432 IF l_srp_info.manager_flag = 'Y' THEN
433
434 -- get manager's quota for each category
435 l_counter := 0;
436 -- get all manager's srp_role_id,may have multiple manager in same groip
437 FOR eachmgr IN mgr_srp_role_list
438 (l_srp_info.comp_group_id,
439 l_srp_info.start_date,l_srp_info.end_date) LOOP
440 BEGIN
441 SELECT
442 (eachmgr.prorate_rate+1)/
443 (Nvl(Trunc(srd.end_date),l_srp_info.end_date) + 1 -
444 Trunc(srd.start_date)) prorate_rate
445 INTO eachmgr.prorate_rate
446 FROM cn_srp_role_dtls_v srd
447 WHERE srd.srp_role_id = eachmgr.srp_role_id
448 ;
449 END;
450
451 -- for each manager, get his/he quota categories
452 FOR eachq IN mgr_quota
453 (eachmgr.srp_role_id,eachmgr.prorate_rate) LOOP
454 l_check := 0;
455 BEGIN
456 SELECT 1 INTO l_check
457 FROM cn_srp_role_dtls
458 WHERE srp_role_id = eachmgr.srp_role_id
459 AND org_code = p_org_code
460 ;
461 EXCEPTION
462 WHEN no_data_found THEN
463 l_check := 0;
464 END;
465 IF l_check = 0 THEN
466 eachq.amount := 0;
467 eachq.planning_amt := 0;
468 eachq.prorated_amount := 0;
469 END IF;
470
471 l_match := 0;
472 IF l_mgr_quota.COUNT > 0 THEN
473 -- build l_mgr_quota table for existing qc
474 FOR i IN 0 .. l_counter-1 LOOP
475 IF l_mgr_quota(i).quota_category_id=eachq.quota_category_id
476 THEN
477 l_mgr_quota(i).quota_amount:= l_mgr_quota(i).quota_amount
478 + eachq.amount;
479 l_mgr_quota(i).planning_amt:= l_mgr_quota(i).planning_amt
480 + eachq.planning_amt;
481 l_mgr_quota(i).prorated_amount := l_mgr_quota(i).prorated_amount
482 + eachq.prorated_amount;
483
484 l_match := 1;
485 EXIT WHEN l_match = 1;
486 END IF;
487 END LOOP;
488 END IF;
489 -- build l_mgr_quota table for new qc
490 IF l_match = 0 THEN
491 l_mgr_quota(l_counter).quota_category_id
492 := eachq.quota_category_id;
493 l_mgr_quota(l_counter).quota_amount := eachq.amount;
494 l_mgr_quota(l_counter).planning_amt := eachq.planning_amt;
495 l_mgr_quota(l_counter).prorated_amount := eachq.prorated_amount;
496
497 l_direct_quota(l_counter).quota_category_id
498 := eachq.quota_category_id;
499 l_direct_quota(l_counter).quota_amount := 0;
500 l_direct_quota(l_counter).planning_amt := 0;
501 l_direct_quota(l_counter).prorated_amount := 0;
502
503 l_street_quota(l_counter).quota_category_id
504 := eachq.quota_category_id;
505 l_street_quota(l_counter).quota_amount := 0;
506 l_street_quota(l_counter).planning_amt := 0;
507 l_street_quota(l_counter).prorated_amount := 0;
508
509 l_counter := l_counter + 1;
510 END IF;
511 END LOOP; -- end of eachq
512 END LOOP; -- end of eachmgr
513
514 -- check if have any srp in the same group
518 l_srp_info.start_date,l_srp_info.end_date)
515 -- find out all directs
516 FOR eachdirect IN direct_reps
517 (l_srp_info.srp_id,l_srp_info.comp_group_id,
519 LOOP
520 l_check := 0;
521 BEGIN
522 SELECT 1 INTO l_check
523 FROM cn_srp_role_dtls
524 WHERE srp_role_id = eachdirect.srp_role_id
525 AND org_code = p_org_code
526 AND overlay_flag = 'N'
527 ;
528 EXCEPTION
529 WHEN no_data_found THEN
530 l_check := 0;
531 END;
532 IF (l_check = 1) THEN
533 -- need to find out the plan types assigned to the direct for
534 -- the time reporting to this mgr
535 IF (eachdirect.member_flag = 'Y') THEN
536 FOR eachplan IN plan_assign
537 (eachdirect.srp_id,eachdirect.start_date,
538 eachdirect.end_date)
539 LOOP
540 -- add quota to l_direct_quota
541 add_quota(eachplan.srp_role_id,
542 eachplan.start_date,
543 eachplan.end_date,
544 l_direct_quota);
545
546 -- srp in this comp group, treat as street level
547 add_quota(eachplan.srp_role_id,
548 eachplan.start_date,
549 eachplan.end_date,
550 l_street_quota);
551 END LOOP; -- End of eachplan
552 END IF;
553 END IF;
554 END LOOP; -- End of eachdirect
555 l_street_exist := 0;
556 -- find all all direct comp group
557 FOR eachcg IN direct_comp_group
558 (l_srp_info.comp_group_id,
559 l_srp_info.start_date,l_srp_info.end_date)
560 LOOP
561 l_direct_exist := 0;
562
563 -- find out all directs
564 FOR eachdirect IN direct_reps
565 (l_srp_info.srp_id,eachcg.comp_group_id,
566 eachcg.start_date,eachcg.end_date)
567 LOOP
568 l_check := 0;
569 BEGIN
570 SELECT 1 INTO l_check
571 FROM cn_srp_role_dtls
572 WHERE srp_role_id = eachdirect.srp_role_id
573 AND org_code = p_org_code
574 AND overlay_flag = 'N'
575 ;
576 EXCEPTION
577 WHEN no_data_found THEN
578 l_check := 0;
579 END;
580 IF (l_check = 1) THEN
581 -- need to find out the plan types assigned to the direct
582 -- for the time reporting to this mgr
583
584 FOR eachplan IN plan_assign
585 (eachdirect.srp_id,eachdirect.start_date,
586 eachdirect.end_date)
587 LOOP
588 IF (eachdirect.manager_flag = 'Y') THEN
589 -- add quota to l_direct_quota
590 add_quota(eachplan.srp_role_id,
591 eachplan.start_date,
592 eachplan.end_date,
593 l_direct_quota);
594 -- Need to go down to the street level
595 down_one_level(eachdirect.srp_id,
596 eachdirect.comp_group_id,
597 eachdirect.start_date,
598 eachdirect.end_date,
599 p_org_code,
600 l_direct_exist,
601 l_street_exist,
602 l_street_quota);
603 ELSE
604 -- srp in this comp group, treat as street level
605 add_quota(eachplan.srp_role_id,
606 eachplan.start_date,
607 eachplan.end_date,
608 l_street_quota);
609 l_direct_exist := 1;
610 END IF;
611
612 END LOOP; -- End of eachplan
613 END IF;
614
615 END LOOP; -- End of eachdirect
616 END LOOP; -- End of eachcg
617 END IF; -- end if l_srp_info.manager_flag = 'Y'
618
619 -- Check if this srp is street node
620 IF l_srp_info.member_flag = 'Y' THEN
621 -- get manager's quota for each category
622 l_counter := 0;
623 -- for each manager, get his/he quota categories
624 FOR eachq IN mgr_quota
625 (l_srp_info.srp_role_id,1) LOOP
626 l_match := 0;
627 IF l_mgr_quota.COUNT > 0 THEN
628 -- build l_mgr_quota table for existing qc
629 FOR i IN 0 .. l_counter-1 LOOP
630 IF l_mgr_quota(i).quota_category_id=eachq.quota_category_id
631 THEN
632 l_mgr_quota(i).quota_amount:= l_mgr_quota(i).quota_amount
633 + eachq.amount;
634 l_mgr_quota(i).planning_amt:= l_mgr_quota(i).planning_amt
635 + eachq.planning_amt;
636
637 -- ************************************
638 -- SUN : START ADDED FOR Sun
639 -- ************************************
640 l_mgr_quota(i).prorated_amount:= l_mgr_quota(i).prorated_amount
641 + eachq.prorated_amount;
642
643 l_match := 1;
644 EXIT WHEN l_match = 1;
645 END IF;
646 END LOOP;
647 END IF;
648 -- build l_mgr_quota table for new qc
649 IF l_match = 0 THEN
650 l_mgr_quota(l_counter).quota_category_id
651 := eachq.quota_category_id;
652 l_mgr_quota(l_counter).quota_amount := eachq.amount;
653 l_mgr_quota(l_counter).planning_amt := eachq.planning_amt;
654 l_mgr_quota(l_counter).prorated_amount := eachq.prorated_amount;
655
656 l_direct_quota(l_counter).quota_category_id
657 := eachq.quota_category_id;
658 l_direct_quota(l_counter).quota_amount := 0;
662 -- ************************************
659 l_direct_quota(l_counter).planning_amt := 0;
660 -- ************************************
661 -- SUN : START ADDED FOR Sun
663 l_direct_quota(l_counter).prorated_amount := 0;
664
665 l_street_quota(l_counter).quota_category_id
666 := eachq.quota_category_id;
667 l_street_quota(l_counter).quota_amount := 0;
668 l_street_quota(l_counter).planning_amt := 0;
669 -- ************************************
670 -- SUN : START ADDED FOR Sun
671 -- ************************************
672 l_street_quota(l_counter).prorated_amount := 0;
673
674 l_counter := l_counter + 1;
675 END IF;
676 END LOOP; -- end of eachq
677 END IF;
678
679 -- At this point, get everything for calculating overassign
680 IF l_mgr_quota.COUNT > 0 THEN
681 FOR i IN l_mgr_quota.first .. l_mgr_quota.last LOOP
682
683 x_quota_overassign_tbl(i).quota_category_id
684 := l_mgr_quota(i).quota_category_id;
685
686 IF (l_mgr_quota(i).quota_amount IS NULL) OR
687 (l_mgr_quota(i).quota_amount = 0) THEN
688
689 x_quota_overassign_tbl(i).direct_overassign_pct := 0;
690 x_quota_overassign_tbl(i).street_overassign_pct := 0;
691
692 ELSE
693
694 x_quota_overassign_tbl(i).direct_overassign_pct :=
695 (l_direct_quota(i).quota_amount/l_mgr_quota(i).quota_amount)*
696 100;
697 x_quota_overassign_tbl(i).street_overassign_pct :=
698 (l_street_quota(i).quota_amount/l_mgr_quota(i).quota_amount)*
699 100;
700
701 END IF; -- end if zero check for quota amount
702
703 IF (l_mgr_quota(i).planning_amt IS NULL) OR
704 (l_mgr_quota(i).planning_amt = 0) THEN
705
706 x_quota_overassign_tbl(i).direct_pln_oasg_pct := 0;
707 x_quota_overassign_tbl(i).street_pln_oasg_pct := 0;
708
709 ELSE
710
711 x_quota_overassign_tbl(i).direct_pln_oasg_pct :=
712 (l_direct_quota(i).planning_amt/l_mgr_quota(i).planning_amt)*
713 100;
714 x_quota_overassign_tbl(i).street_pln_oasg_pct :=
715 (l_street_quota(i).planning_amt/l_mgr_quota(i).planning_amt)*
716 100;
717
718 END IF; -- end if zero check for planning amount
719
720 -- ************************************
721 -- SUN : START ADDED FOR Sun
722 -- ************************************
723 IF (l_mgr_quota(i).prorated_amount IS NULL) OR
724 (l_mgr_quota(i).prorated_amount = 0) THEN
725
726 x_quota_overassign_tbl(i).direct_pro_oasg_pct := 0;
727 x_quota_overassign_tbl(i).street_pro_oasg_pct := 0;
728
729 ELSE
730
731 x_quota_overassign_tbl(i).direct_pro_oasg_pct :=
732 (l_direct_quota(i).prorated_amount/l_mgr_quota(i).prorated_amount)*
733 100;
734 x_quota_overassign_tbl(i).street_pro_oasg_pct :=
735 (l_street_quota(i).prorated_amount/l_mgr_quota(i).prorated_amount)*
736 100;
737
738 END IF; -- end if zero check for planning amount
739 -- ************************************
740 -- SUN : STOP ADDED FOR Sun
741 -- ************************************
742
743 END LOOP; -- end of each quota category
744 END IF;
745 -- Standard call to get message count and if count is 1 get message info.
746 FND_MSG_PUB.Count_And_Get
747 ( p_count => x_msg_count ,
748 p_data => x_msg_data ,
749 p_encoded => FND_API.G_FALSE
750 );
751
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 x_return_status := FND_API.G_RET_STS_ERROR ;
755 FND_MSG_PUB.Count_And_Get
756 (p_count => x_msg_count ,
757 p_data => x_msg_data ,
758 p_encoded => FND_API.G_FALSE
759 );
760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
762 FND_MSG_PUB.Count_And_Get
763 (p_count => x_msg_count ,
764 p_data => x_msg_data ,
765 p_encoded => FND_API.G_FALSE
766 );
767 WHEN OTHERS THEN
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
769 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
770 THEN
771 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
772 END IF;
773 FND_MSG_PUB.Count_And_Get
774 (p_count => x_msg_count ,
775 p_data => x_msg_data ,
776 p_encoded => FND_API.G_FALSE
777 );
778 END;
779
780 END cn_over_assign_pvt;
781