1 PACKAGE BODY CN_SRP_PERIOD_QUOTAS_PKG as
2 /* $Header: cnsrpqob.pls 120.9 2005/12/20 10:45:06 mblum ship $ */
3
4 --Date Name Description
5 ---------------------------------------------------------------------------+
6 -- 25-JUL-95 P Cook Split the inserts into 3 statements for performance
7 -- Added delete stmnt for source quota type changes
8 -- Fixed locking error by removing period_id comparison.
9 -- Modified update to handle quarters and years
10
11 -- 22-AUG-95 P Cook Removed maintenance of cn_srp_per_quota_rc. This is
12 -- now done in other routines
13 -- 20-NOV-95 P Cook Added who columns.
14
15 -- 07-MAR-96 P Cook Bug:346506. distribute_target modified to distribute
16 -- according to the quotas period_type_code(Interval).
17
18 -- 10-JUN-99 S Kumar Modified extensively like changing from start period
19 -- ids start date and end period id to end date.
20
21 -- 1. insert_record procedure is modified to pass the
22 -- start date and end date, still start period id and
23 -- end period id exists but always null value
24
25 -- 25-AUG-99 Added the performance_goal column in the
26 -- srp_period_quotas and itd_performance_goal.
27 --
28 -- 22-Apr-03 rarajara Fixed the bug #2874991
29 -- 14-Sep-04 jasingh Fixed the Bug# 3848446
30
31 FUNCTION cn_end_date_period(p_end_date DATE, p_org_id NUMBER)
32
33 RETURN cn_acc_period_statuses_v.end_date%TYPE IS
34
35 l_next_end_date cn_acc_period_statuses_v.end_date%TYPE;
36
37 BEGIN
38
39 SELECT end_date
40 INTO l_next_end_date
41 FROM cn_acc_period_statuses_v
42 WHERE p_end_date between start_date and end_date
43 AND org_id = p_org_id;
44
45
46 RETURN l_next_end_date;
47
48 EXCEPTION
49 WHEN no_data_found THEN
50 RETURN NULL;
51 END cn_end_date_period;
52 --bugfix #2874991 starts
53 -- Name
54 --
55 -- Purpose
56 -- Insert period quota for each rep using the quota in a period
57 --
58 -- Notes
59 -- This method is called whenever a quota is made
60 -- active for a new period
61
62 PROCEDURE populate_itd_values (x_start_srp_period_quota_id NUMBER) IS
63
64 l_previous_period_id NUMBER :=0;
65 l_end_period_id NUMBER :=0;
66
67 l_interval_type_id NUMBER :=0;
68 l_start_period_id NUMBER :=0;
69
70 l_salesrep_id NUMBER :=0;
71 l_srp_plan_assign_id NUMBER :=0;
72
73 l_quota_id NUMBER :=0;
74 l_org_id NUMBER :=0;
75
76 l_input_achieved_itd NUMBER :=0;
77 l_output_achieved_itd NUMBER :=0;
78 l_perf_achieved_itd NUMBER :=0;
79 l_commission_payed_itd NUMBER :=0;
80 l_advance_recovered_itd NUMBER :=0;
81 l_advance_to_rec_itd NUMBER :=0;
82 l_recovery_amount_itd NUMBER :=0;
83 l_comm_pend_itd NUMBER :=0;
84 --clku, related to bug 2874991
85 l_itd_target NUMBER := 0;
86 l_itd_payment NUMBER := 0;
87 l_performance_goal_itd NUMBER := 0;
88
89 cursor max_prev_period_csr(p_interval_type_id NUMBER,
90 p_start_period_id NUMBER,
91 p_org_id NUMBER) is
92 SELECT max(cal_period_id) max_cal_period_id
93 FROM cn_cal_per_int_types_all
94 WHERE interval_type_id = p_interval_type_id
95 AND org_id = p_org_id
96 AND cal_period_id < p_start_period_id
97 AND interval_number =
98 (select interval_number from cn_cal_per_int_types_all
99 where cal_period_id = p_start_period_id
100 AND org_id = p_org_id
101 and interval_type_id = p_interval_type_id);
102
103 cursor max_period_csr(p_interval_type_id NUMBER,
104 p_start_period_id NUMBER,
105 p_org_id NUMBER) is
106 SELECT cal_period_id
107 FROM cn_cal_per_int_types_all
108 WHERE interval_type_id = p_interval_type_id
109 AND org_id = p_org_id
110 AND cal_period_id >= p_start_period_id
111 AND interval_number =
112 (select interval_number from cn_cal_per_int_types_all
113 where cal_period_id = p_start_period_id
114 AND org_id = p_org_id
115 and interval_type_id = p_interval_type_id);
116
117 BEGIN
118
119 SELECT period_id, salesrep_id, srp_plan_assign_id, quota_id, org_id
120 INTO l_start_period_id, l_salesrep_id, l_srp_plan_assign_id, l_quota_id, l_org_id
121 FROM cn_srp_period_quotas_all
122 WHERE srp_period_quota_id = x_start_srp_period_quota_id;
123
124 SELECT interval_type_id INTO l_interval_type_id
125 FROM cn_quotas_all WHERE quota_id = l_quota_id;
126
127 OPEN max_prev_period_csr(l_interval_type_id,l_start_period_id,l_org_id);
128 FETCH max_prev_period_csr INTO l_previous_period_id ;
129 if max_prev_period_csr%NOTFOUND then
130 raise NO_DATA_FOUND;
131 END IF;
132 CLOSE max_prev_period_csr;
133
134 IF l_previous_period_id > 0 THEN
135 SELECT
136 nvl(spq.input_achieved_itd,0),
137 nvl(spq.output_achieved_itd,0),
138 nvl(spq.perf_achieved_itd,0),
139 nvl(spq.commission_payed_itd,0),
140 nvl(spq.advance_recovered_itd,0),
141 nvl(spq.advance_to_rec_itd,0),
142 nvl(spq.recovery_amount_itd,0),
143 nvl(spq.comm_pend_itd,0),
144 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
145 nvl(spq.itd_target,0),
146 nvl(spq.itd_payment,0),
147 nvl(spq.performance_goal_itd,0)
148 INTO
149 l_input_achieved_itd,
150 l_output_achieved_itd,
151 l_perf_achieved_itd,
152 l_commission_payed_itd,
153 l_advance_recovered_itd,
154 l_advance_to_rec_itd,
155 l_recovery_amount_itd,
156 l_comm_pend_itd,
157 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
158 l_itd_target,
159 l_itd_payment,
160 l_performance_goal_itd
161 FROM cn_srp_period_quotas_all spq
162 WHERE salesrep_id = l_salesrep_id
163 AND srp_plan_assign_id = l_srp_plan_assign_id
164 AND quota_id = l_quota_id
165 AND period_id = l_previous_period_id;
166
167 FOR i_period_id in max_period_csr(l_interval_type_id,l_start_period_id,l_org_id) LOOP
168 UPDATE cn_srp_period_quotas_all
169 SET input_achieved_itd = l_input_achieved_itd,
170 output_achieved_itd = l_output_achieved_itd,
171 perf_achieved_itd = l_perf_achieved_itd,
172 commission_payed_itd = l_commission_payed_itd,
173 advance_recovered_itd = l_advance_recovered_itd,
174 advance_to_rec_itd = l_advance_to_rec_itd,
175 recovery_amount_itd = l_recovery_amount_itd,
176 comm_pend_itd = l_comm_pend_itd,
177 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
178 itd_target = l_itd_target,
179 itd_payment = l_itd_payment,
180 performance_goal_itd = l_performance_goal_itd
181
182 WHERE salesrep_id = l_salesrep_id
183 AND srp_plan_assign_id = l_srp_plan_assign_id
184 AND quota_id = l_quota_id
185 AND period_id = i_period_id.cal_period_id;
186
187 END LOOP;
188
189 END IF;
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 null;
194 WHEN OTHERS THEN
195 null;
196 END populate_itd_values;
197 --bugfix #2874991 ends
198
199
200 --bugfix #2874991 starts
201 -- Name
202 --
203 -- Purpose
204 -- Insert period quota for each rep using the quota in a period
205 --
206 -- Notes
207 -- This method is called whenever a quota is made
208 -- active for a new period
209
210 PROCEDURE sync_ITD_values (x_quota_id NUMBER) IS
211
212 CURSOR srp_period_quotas(l_srp_quota_assign_id NUMBER,
213 l_interval_number NUMBER,
214 l_period_year NUMBER) IS
215 SELECT spq.srp_period_quota_id srp_period_quota_id,
216 nvl(spq.input_achieved_ptd,0) input_achieved_ptd,
217 nvl(spq.output_achieved_ptd,0) output_achieved_ptd,
218 nvl(spq.perf_achieved_ptd,0) perf_achieved_ptd,
219 nvl(spq.commission_payed_ptd,0) commission_payed_ptd,
220 nvl(spq.advance_recovered_ptd,0) advance_recovered_ptd,
221 nvl(spq.advance_to_rec_ptd,0) advance_to_rec_ptd,
222 nvl(spq.recovery_amount_ptd,0) recovery_amount_ptd,
223 nvl(spq.comm_pend_ptd,0) comm_pend_ptd,
224 nvl(spq.target_amount,0) target_amount,
225 nvl(spq.period_payment,0) period_payment,
226 nvl(spq.performance_goal_ptd,0) performance_goal_ptd
227 FROM
228 cn_srp_period_quotas_all spq,
229 cn_period_statuses_all cp,
230 cn_cal_per_int_types_all cpit,
231 cn_quotas_all cq
232 WHERE spq.quota_id = x_quota_id
233 AND spq.quota_id = cq.quota_id
234 AND spq.period_id = cp.period_id
235 AND spq.org_id = cp.org_id
236 AND spq.period_id = cpit.cal_period_id
237 AND spq.org_id = cpit.org_id
238 AND spq.srp_quota_assign_id = l_srp_quota_assign_id
239 AND cpit.interval_type_id = cq.interval_type_id
240 AND cpit.interval_number = l_interval_number
241 AND cp.period_year = l_period_year
242 ORDER BY spq.period_id;
243
244 pq_rec srp_period_quotas%ROWTYPE;
245
246 -- Get the period quotas that belong to the quota assignment for each
247 -- interval
248
249 CURSOR interval_counts IS
250 SELECT
251 p.srp_quota_assign_id srp_quota_assign_id,
252 count(p.srp_period_quota_id) interval_count,
253 cpit.interval_number interval_number,
254 p.period_year period_year
255 FROM cn_srp_period_quotas_v p,
256 cn_period_statuses cp,
257 cn_cal_per_int_types_all cpit,
258 cn_quotas_all cq
259 WHERE p.quota_id = x_quota_id
260 AND p.quota_id = cq.quota_id
261 AND p.period_id = cp.period_id
262 AND cp.period_status IN ('O', 'F')
263 AND cq.org_id = cp.org_id
264 AND cp.period_id = cpit.cal_period_id
265 AND cp.org_id = cpit.org_id
266 AND cpit.interval_type_id = cq.interval_type_id
267 GROUP BY p.srp_quota_assign_id, cpit.interval_number,p.period_year
268 ;
269
270 interval_rec interval_counts%ROWTYPE;
271
272 l_target_total NUMBER := 0;
273 l_payment_total NUMBER := 0;
274 l_performance_goal_total NUMBER := 0;
275 l_input_achieved_total NUMBER :=0;
276 l_output_achieved_total NUMBER :=0;
277 l_perf_achieved_total NUMBER :=0;
278 l_commission_payed_total NUMBER :=0;
279 l_advance_recovered_total NUMBER :=0;
280 l_advance_to_rec_total NUMBER :=0;
281 l_recovery_amount_total NUMBER :=0;
282 l_comm_pend_total NUMBER :=0;
283
284 BEGIN
285
286 FOR interval_rec IN interval_counts LOOP
287
288 -- Initialize for each interval
289
290 l_target_total := 0;
291 l_payment_total := 0;
292 l_performance_goal_total := 0;
293 l_input_achieved_total :=0;
294 l_output_achieved_total :=0;
295 l_perf_achieved_total :=0;
296 l_commission_payed_total :=0;
297 l_advance_recovered_total :=0;
298 l_advance_to_rec_total :=0;
299 l_recovery_amount_total :=0;
300 l_comm_pend_total:=0;
301
302 -- Now that we know the counts per quarter/year we can divide the
303 -- quota target correctly for each quarter and set the period quota
304 -- target.
305
306 FOR pq_rec IN srp_period_quotas
307 (
308 l_srp_quota_assign_id => interval_rec.srp_quota_assign_id
309 ,l_interval_number => interval_rec.interval_number
310 ,l_period_year => interval_rec.period_year) LOOP
311
312 l_target_total := l_target_total + pq_rec.target_amount;
313 l_payment_total := l_payment_total + pq_rec.period_payment;
314 l_performance_goal_total := l_performance_goal_total
315 + pq_rec.performance_goal_ptd;
316 l_input_achieved_total := l_input_achieved_total + pq_rec.input_achieved_ptd;
317 l_output_achieved_total := l_output_achieved_total + pq_rec.output_achieved_ptd;
318 l_perf_achieved_total := l_perf_achieved_total + pq_rec.perf_achieved_ptd;
319 l_commission_payed_total := l_commission_payed_total + pq_rec.commission_payed_ptd;
320 l_advance_recovered_total := l_advance_recovered_total + pq_rec.advance_recovered_ptd;
321 l_advance_to_rec_total := l_advance_to_rec_total + pq_rec.advance_to_rec_ptd;
322 l_recovery_amount_total := l_recovery_amount_total + pq_rec.recovery_amount_ptd;
323 l_comm_pend_total:= l_comm_pend_total + pq_rec.comm_pend_ptd;
324
325
326 UPDATE cn_srp_period_quotas_all
327 SET
328 itd_target= nvl(l_target_total,0),
329 itd_payment= nvl(l_payment_total,0),
330 performance_goal_itd = nvl(l_performance_goal_total,0),
331 input_achieved_itd = nvl(l_input_achieved_total, 0),
332 output_achieved_itd = nvl(l_output_achieved_total, 0),
333 perf_achieved_itd = nvl(l_perf_achieved_total, 0),
334 commission_payed_itd = nvl(l_commission_payed_total, 0),
335 advance_recovered_itd = nvl(l_advance_recovered_total, 0),
336 advance_to_rec_itd = nvl(l_advance_to_rec_total, 0),
337 recovery_amount_itd = nvl(l_recovery_amount_total, 0),
338 comm_pend_itd = nvl(l_comm_pend_total, 0)
339
340 WHERE srp_period_quota_id = pq_rec.srp_period_quota_id
341 ;
342
343 END LOOP;
344 END LOOP;
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 null;
349
350 END sync_ITD_values;
351
352 -- Name
353 --
354 -- Purpose
355 -- Insert period quota for each rep using the quota in a period
356 --
357 -- Notes Parameters
358 -- o Called once for each new srp plan assignment. x_srp_plan_assign_id
359
360 -- o Called one insert of new quota assignment -+
361 -- once for each srp plan assignment that x_srp_plan_assign_id
362 -- references the comp plan id on the new comp x_quota_id
363 -- plan quota assignment
364 -- The quota_id restriction ensures only the newly
365 -- assigned quota is inserted.
366
367 -- o Called on update of srp plan assign period range x_srp_plan_assign_id
368 -- The not exists subselect is specifically for this
369 -- situation. refer to delete_row procedure for more info
370
371 --
372 -- |-----Plan Assignment Active range---|
373 -- |--------- Comp Plan active Range--------|
374 -- |-----|----------Quota Active Range--|-------|
375
376 -- o All quota types have cn_srp_period_quotas althouhg revenue types
377 -- do not display or allow the user to maintain a target value
378
379 -- New Comments Added on 10/JUN/99
380 -- Start period id and End period is not used any more in the sales comp
381 -- Instead we pass start Date and End Date
382 -- We are not removing the column, we assign default null to
383 -- start_period_id and end period_id
384
385 -- o Called once for each new srp plan assignment. x_srp_plan_assign_id
386 -- o Called one insert of new quota assignment x_srp_plan_assign_id,
387 -- quota_id
388 -- o calling Place 1.cn_srp_quota_assigns_pkg.insert record
389 ---------------------------------------------------------------------------+
390 -- PROCEDURE INSERT_RECORD
391 -- Description:
392 -- CASE 1: Quota period has changed like end date changed from NOT NULL to
393 -- null ( means extending the quota active range ) or
394 -- new end date is greater than old end date
395 -- called from cn_quotas_pkg
396 -- Value passed x_quota_id, x_start_date, x_end_date
397 ---------------------------------------------------------------------------+
398 PROCEDURE Insert_Record
399 (
400 x_srp_plan_assign_id NUMBER
401 ,x_quota_id NUMBER
402 ,x_start_period_id NUMBER
403 ,x_end_period_id NUMBER
404 ,x_start_date DATE := NULL
405 ,x_end_date DATE := NULL ) IS
406
407 l_user_id NUMBER(15);
408 l_resp_id NUMBER(15);
409 l_login_id NUMBER(15);
410
411 x_itd_flag_checked VARCHAR2(1);
412
413 -- Get the itd_flag for each quota
414
415 CURSOR ytd_flag IS
416 SELECT q.quota_id quota_id, q.org_id
417 FROM cn_srp_quota_assigns_all qa
418 ,cn_quotas_all q
419 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
420 -- do not need itd and formula id anymore, bug 2462767,AND q.calc_formula_id = cf.calc_formula_id(+)
421 AND qa.quota_id = q.quota_id;
422
423 -- clku bug 2845024, performance fix, avoid full table scan by avoiding
424 -- is null condition of the cursor.
425 CURSOR srp_period_quota_ids1(l_quota_id NUMBER, l_srp_plan_assign_id NUMBER) is
426 SELECT srp_period_quota_id from cn_srp_period_quotas_all
427 where quota_id = l_quota_id
428 and srp_plan_assign_id = l_srp_plan_assign_id ;
429
430 CURSOR srp_period_quota_ids2(l_quota_id NUMBER) is
431 SELECT srp_period_quota_id from cn_srp_period_quotas_all
432 where quota_id = l_quota_id;
433
434 --bugfix for #2874991 starts
435 cursor start_period_quota_id_csr1(p_srp_plan_assign_id NUMBER,p_quota_id NUMBER,x_start_period_id NUMBER) is
436 Select srp_period_quota_id
437 from cn_srp_period_quotas_all
438 where srp_plan_assign_id = p_srp_plan_assign_id
439 and quota_id = p_quota_id
440 and period_id = x_start_period_id;
441
442
443 cursor start_period_quota_id_csr2(p_quota_id NUMBER,x_start_period_id NUMBER) is
444 Select max(srp_period_quota_id)
445 from cn_srp_period_quotas_all
446 where quota_id = p_quota_id
447 and period_id = x_start_period_id
448 group by srp_plan_assign_id;
449
450 l_srp_start_period_quota_id NUMBER :=0;
451
452 l_count NUMBER :=0;
453 l_value NUMBER;
454
455 TYPE l_start_period_quota_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
456 l_start_period_quota_id_tbl l_start_period_quota_id_type;
457
458 --bugfix for #2874991 ends
459
460 itd_p_rec ytd_flag%ROWTYPE;
461 --clku bug 2845024
462 srp_period_quota_id_rec1 srp_period_quota_ids1%ROWTYPE;
463 srp_period_quota_id_rec2 srp_period_quota_ids2%ROWTYPE;
464
465 -- get number_dim
466 CURSOR get_number_dim(l_quota_id number) IS
467 select ccf.number_dim
468 from cn_quotas_all cq, cn_calc_formulas_all ccf
469 where cq.quota_id = l_quota_id
470 and cq.calc_formula_id = ccf.calc_formula_id;
471
472 l_number_dim NUMBER;
473
474 BEGIN
475
476 l_user_id := fnd_global.user_id;
477 l_resp_id := fnd_global.resp_id;
478 l_login_id := fnd_global.login_id;
479
480 IF x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NULL THEN
481 -- A new plan is assigned to a salesrep
482 -- case 1: callled from cn_srp_quota_assigns_pkg
483
484 FOR itd_p_rec IN ytd_flag LOOP
485
486 -- enhancement, clku, 2431086,we do not check if the PE is ITD or not.
487 --IF (itd_p_rec.itd_flag = 'Y') THEN
488
489 INSERT INTO cn_srp_period_quotas_all
490 ( srp_period_quota_id
491 ,srp_plan_assign_id
492 ,srp_quota_assign_id
493 ,salesrep_id
494 ,period_id
495 ,quota_id
496 ,target_amount
497 ,itd_target
498 ,period_payment
499 ,itd_payment
500 ,performance_goal_ptd
501 ,performance_goal_itd
502 ,commission_payed_ptd
503 ,creation_date
504 ,created_by
505 ,last_update_date
506 ,last_updated_by
507 ,last_update_login
508 ,org_id )
509 SELECT
510 cn_srp_period_quotas_s.nextval
511 ,qa.srp_plan_assign_id
512 ,qa.srp_quota_assign_id
513 ,pa.salesrep_id
514 ,p.period_id
515 ,qa.quota_id
516 ,nvl(pq.period_target,0)
517 ,pq.itd_target
518 ,pq.period_payment
519 ,pq.itd_payment
520 ,pq.performance_goal
521 ,pq.performance_goal_itd
522 ,0
523 ,sysdate
524 ,l_user_id
525 ,sysdate
526 ,l_user_id
527 ,l_login_id
528 ,qa.org_id
529 FROM cn_srp_quota_assigns_all qa
530 ,cn_period_quotas_all pq
531 ,cn_srp_plan_assigns_all pa
532 ,cn_period_statuses p
533 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
534 AND pa.srp_plan_assign_id = x_srp_plan_assign_id
535 AND pa.srp_plan_assign_id = qa.srp_plan_assign_id --bugfix3633222
536 AND qa.quota_id = pq.quota_id
537 AND pq.period_id = p.period_id
538 AND p.period_status IN ('O', 'F')
539 AND pq.org_id = p.org_id
540 AND pq.quota_id = itd_p_rec.quota_id
541 AND greatest(p.start_date, nvl(x_start_date, pa.start_date)) <=
542 least(p.end_date, nvl(x_end_date,
543 Nvl(pa.end_date,p.end_date)))
544 AND NOT EXISTS (SELECT 'srp_period_quota already exists'
545 FROM cn_srp_period_quotas_all spq
546 WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
547 AND spq.period_id = p.period_id)
548 -- bug 2460926, clku, check if all the open period ends before the specified start_date
549 -- 2479359, Nvl(x_start_date, pa.start_date) added to deal with NULL x_start_date
550 AND EXISTS(select r1.end_date
551 from cn_acc_period_statuses_v r1
552 where r1.end_date >= Nvl(x_start_date, pa.start_date)
553 AND r1.org_id = pa.org_id)
554 ;
555
556 --added for bugfix#2874991
557 l_value := null;
558 OPEN start_period_quota_id_csr1(x_srp_plan_assign_id,itd_p_rec.quota_id,x_start_period_id);
559 FETCH start_period_quota_id_csr1 INTO l_value;
560 IF l_value is not null then
561 l_count := l_count+1;
562 l_start_period_quota_id_tbl(l_count) := l_value;
563 END IF;
564 CLOSE start_period_quota_id_csr1;
565 --added for bugfix#2874991 ends here
566
567 -- get number_dim
568 l_number_dim := 0;
569 OPEN get_number_dim(itd_p_rec.quota_id);
570 FETCH get_number_dim INTO l_number_dim;
571 CLOSE get_number_dim;
572
573 --clku bug 2845024
574 IF l_number_dim > 1 then
575 FOR srp_period_quota_id_rec1 IN srp_period_quota_ids1(itd_p_rec.quota_id, x_srp_plan_assign_id) LOOP
576 populate_srp_period_quotas_ext
577 ('INSERT',srp_period_quota_id_rec1.srp_period_quota_id,
578 itd_p_rec.org_id, l_number_dim);
579 END LOOP;
580 END IF;
581
582 END LOOP;
583
584 --bugfix #2874991
585 IF l_start_period_quota_id_tbl.count > 0 THEN
586 FOR counter in 1..l_start_period_quota_id_tbl.count LOOP
587 populate_itd_values(l_start_period_quota_id_tbl(counter));
588 END LOOP;
589 END IF;
590 --added for bugfix#2874991 ends here
591
592 ELSIF (x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NOT NULL) THEN
593
594 -- A new quota has been assigned to a compensation plan
595 -- Check whether itd_flag for this quota is checked
596 -- case 1: called from cn_srp_quota_assigns_pkg.
597
598 -- enhancement, clku, 2431086, we do not check if the PE is ITD or not.
599 --IF x_itd_flag_checked = 'Y' THEN
600
601 INSERT INTO cn_srp_period_quotas_all
602 ( srp_period_quota_id
603 ,srp_plan_assign_id
604 ,srp_quota_assign_id
605 ,salesrep_id
606 ,period_id
607 ,quota_id
608 ,target_amount
609 ,itd_target
610 ,performance_goal_ptd
611 ,performance_goal_itd
612 ,period_payment
613 ,itd_payment
614 ,commission_payed_ptd
615 ,creation_date
616 ,created_by
617 ,last_update_date
618 ,last_updated_by
619 ,last_update_login
620 ,org_id )
621 SELECT
622 cn_srp_period_quotas_s.nextval
623 ,qa.srp_plan_assign_id
624 ,qa.srp_quota_assign_id
625 ,pa.salesrep_id
626 ,p.period_id
627 ,qa.quota_id
628 ,nvl(pq.period_target,0)
629 ,pq.itd_target
630 ,pq.performance_goal
631 ,pq.performance_goal_itd
632 ,pq.period_payment
633 ,pq.itd_payment
634 ,0
635 ,sysdate
636 ,l_user_id
637 ,sysdate
638 ,l_user_id
639 ,l_login_id
640 ,qa.org_id
641 FROM cn_srp_quota_assigns_all qa
642 ,cn_period_quotas_all pq
643 ,cn_srp_plan_assigns_all pa
644 ,cn_period_statuses p
645 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
646 AND pa.srp_plan_assign_id = qa.srp_plan_assign_id
647 AND qa.quota_id = x_quota_id
648 AND greatest(pa.start_date, p.start_date) <=
649 least(nvl(pa.end_date,p.end_date), p.end_date)
650 AND pq.period_id = p.period_id
651 AND p.period_status IN ('O', 'F')
652 AND pq.org_id = p.org_id
653 AND pq.quota_id = qa.quota_id
654 AND NOT EXISTS (SELECT 'srp_period_quota already exists'
655 FROM cn_srp_period_quotas_all spq
656 WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
657 AND spq.period_id = p.period_id)
658 AND EXISTS(select r1.end_date
659 from cn_acc_period_statuses_v r1
660 where r1.end_date > pa.start_date
661 AND r1.org_id = pa.org_id);
662
663 --bugfix #2874991 starts
664 OPEN start_period_quota_id_csr1(x_srp_plan_assign_id,itd_p_rec.quota_id,x_start_period_id);
665 FETCH start_period_quota_id_csr1 INTO l_srp_start_period_quota_id;
666 CLOSE start_period_quota_id_csr1;
667
668 IF l_srp_start_period_quota_id <> 0 THEN
669 populate_itd_values(l_srp_start_period_quota_id);
670 END IF;
671 --bugfix #2874991 ends
672
673 -- get number_dim
674 l_number_dim := 0;
675 OPEN get_number_dim(x_quota_id);
676 FETCH get_number_dim INTO l_number_dim;
677 CLOSE get_number_dim;
678
679 --clku bug 2845024
680 IF l_number_dim > 1 then
681 FOR srp_period_quota_id_rec1 IN srp_period_quota_ids1(x_quota_id, x_srp_plan_assign_id) LOOP
682 populate_srp_period_quotas_ext
683 ('INSERT',srp_period_quota_id_rec1.srp_period_quota_id,
684 itd_p_rec.org_id, l_number_dim);
685 END LOOP;
686 END IF;
687
688 ELSIF x_srp_plan_assign_id IS NULL AND x_quota_id IS NOT NULL THEN
689
690 -- Quota's period range has been changed and we are inserting a
691 -- new set of records based on the period interval
692
693 INSERT INTO cn_srp_period_quotas_all
694 ( srp_period_quota_id
695 ,srp_plan_assign_id
696 ,srp_quota_assign_id
697 ,salesrep_id
698 ,period_id
699 ,quota_id
700 ,target_amount
701 ,itd_target
702 ,period_payment
703 ,itd_payment
704 ,performance_goal_ptd
705 ,performance_goal_itd
706 ,commission_payed_ptd
707 ,creation_date
708 ,created_by
709 ,last_update_date
710 ,last_updated_by
711 ,last_update_login
712 ,org_id )
713 SELECT cn_srp_period_quotas_s.nextval
714 ,qa.srp_plan_assign_id
715 ,qa.srp_quota_assign_id
716 ,pa.salesrep_id
717 ,p.period_id
718 ,qa.quota_id
719 ,0 -- clku, enhancement 2431086, Nvl(q.payment_amount,0)
720 ,0
721 ,0
722 ,0
723 ,0
724 ,0
725 ,0
726 ,sysdate
727 ,l_user_id
728 ,sysdate
729 ,l_user_id
730 ,l_login_id
731 ,qa.org_id
732 FROM cn_srp_quota_assigns_all qa
733 ,cn_quotas_all q
734 ,cn_srp_plan_assigns_all pa
735 ,cn_acc_period_statuses_v p
736 -- bug fix 4042235
737 ,cn_period_statuses p2
738 ,cn_period_statuses p3
739
740 WHERE qa.srp_plan_assign_id = pa.srp_plan_assign_id
741 AND qa.quota_id = x_quota_id
742 AND q.quota_id = x_quota_id
743 AND q.quota_id = qa.quota_id --bugfix#3633222
744 AND p.org_id = qa.org_id
745 AND (
746 -- bug 2150333, changed to improved performance
747 -- set 1: pa.start_date
748 (pa.start_date between p2.start_date and p2.end_date
749 AND pa.org_id = p2.org_id)
750 AND p.start_date >= p2.start_date
751 and p.period_type_id = p2.period_type_id
752 and p.period_set_id = p2.period_set_id
753 -- set 2: pa.end_date
754 -- clku, fixed a date insert issue
755 AND (least(Nvl(pa.end_date,p.end_date), p.end_date) between p3.start_date and p3.end_date)
756 AND p.end_date <= p3.end_date
757 AND p.org_id = p3.org_id
758 and p.period_type_id = p3.period_type_id
759 and p.period_set_id = p3.period_set_id
760 )
761
762
763 AND greatest(p.start_date, x_start_date) <=
764 least(p.end_date, nvl(x_end_date, p.end_date))
765 AND NOT EXISTS (SELECT 'srp_quota_assign already exists'
766 FROM cn_srp_period_quotas_all pq
767 WHERE pq.srp_quota_assign_id = qa.srp_quota_assign_id
768 AND pq.period_id = p.period_id)
769 -- bug 2460926, check if all the open period ends before the specified start_date
770
771 AND EXISTS(select r1.end_date
772 from cn_acc_period_statuses_v r1
773 where r1.end_date > x_start_date
774 AND r1.org_id = pa.org_id)
775 ;
776
777 --bugfix #2874991 starts
778 OPEN start_period_quota_id_csr2(x_quota_id,x_start_period_id);
779 FETCH start_period_quota_id_csr2 BULK COLLECT INTO l_start_period_quota_id_tbl;
780 CLOSE start_period_quota_id_csr2;
781
782 IF l_start_period_quota_id_tbl.count > 0 THEN
783 FOR counter in 1..l_start_period_quota_id_tbl.count LOOP
784 populate_itd_values(l_start_period_quota_id_tbl(counter));
785 END LOOP;
786 END IF;
787 --bugfix #2874991 ends
788
789 -- get number_dim
790 l_number_dim := 0;
791 OPEN get_number_dim(x_quota_id);
792 FETCH get_number_dim INTO l_number_dim;
793 CLOSE get_number_dim;
794
795 --clku bug 2845024
796 IF l_number_dim > 1 then
797 FOR srp_period_quota_id_rec2 IN srp_period_quota_ids2(x_quota_id) LOOP
798 populate_srp_period_quotas_ext
799 ('INSERT',srp_period_quota_id_rec2.srp_period_quota_id,
800 itd_p_rec.org_id, l_number_dim);
801 END LOOP;
802 END IF;
803
804 END IF;
805
806
807 -- End Insert Record.
808
809 END Insert_Record;
810
811 ---------------------------------------------------------------------------+
812 -- PROCEDURE LOCK RECORD
813 ---------------------------------------------------------------------------+
814 PROCEDURE lock_record
815 (
816 x_srp_period_quota_id NUMBER
817 ,x_period_id NUMBER
818 ,x_target_amount NUMBER) IS
819
820 CURSOR c IS
821 SELECT target_amount
822 FROM cn_srp_period_quotas_all
823 WHERE srp_period_quota_id = x_srp_period_quota_id
824 FOR UPDATE OF srp_period_quota_id NOWAIT;
825
826 recinfo c%ROWTYPE;
827
828 BEGIN
829
830 OPEN C;
831 FETCH C INTO Recinfo;
832 IF (c%notfound) THEN
833 CLOSE C;
834 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
835 app_exception.raise_exception;
836 END IF;
837 CLOSE C;
838
839 IF ( recinfo.target_amount = x_target_amount) THEN
840 return;
841 ELSE
842 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
843 app_exception.raise_exception;
844 END IF;
845 -- End Lock Record.
846 END lock_record;
847 ---------------------------------------------------------------------------+
848 -- PROCEDURE UPDATE RECORD
849 ---------------------------------------------------------------------------+
850 PROCEDURE update_record
851 (
852 x_period_target_unit_code VARCHAR2
853 ,x_srp_period_quota_id NUMBER
854 ,x_srp_quota_assign_id NUMBER
855 ,x_period_id NUMBER
856 ,x_target_amount NUMBER
857 ,x_period_payment NUMBER
858 ,x_performance_goal NUMBER
859 ,x_quarter_num NUMBER
860 ,x_period_year NUMBER
861 ,x_quota_type_code VARCHAR2
862 ,x_quota_id NUMBER := NULL -- only for bonus pay
863 ,x_salesrep_id NUMBER := NULL -- only for bonus pay
864 ,x_end_date DATE := NULL -- only for bonus pay
865 ,x_commission_payed_ptd NUMBER := NULL -- only for bonus pay
866 ,x_last_update_date DATE
867 ,x_last_updated_by NUMBER
868 ,x_last_update_login NUMBER) IS
869
870 -- Count the number of periods in each quarter/year combination that the
871 -- quota assignment covers
872 CURSOR quart_counts IS
873 SELECT count(srp_period_quota_id) quart_yr_count
874 ,quarter_num
875 ,period_year
876 FROM cn_srp_period_quotas_v
877 WHERE srp_quota_assign_id = x_srp_quota_assign_id
878 GROUP BY quarter_num,period_year
879 ;
880 quart_rec quart_counts%ROWTYPE;
881
882 -- Count the number of periods in each year that the quota assignment
883 -- covers.
884 CURSOR year_counts IS
885 SELECT count(srp_period_quota_id) year_count
886 ,period_year
887 FROM cn_srp_period_quotas_v
888 WHERE srp_quota_assign_id = x_srp_quota_assign_id
889 GROUP BY period_year
890 ;
891 year_rec year_counts%ROWTYPE;
892
893 CURSOR period_quotas(l_interval_number NUMBER, l_period_year NUMBER) IS
894 SELECT spq.srp_period_quota_id
895 ,spq.target_amount
896 ,spq.period_payment
897 ,spq.performance_goal_ptd
898 FROM cn_srp_period_quotas_v spq,
899 cn_period_statuses cp,
900 cn_cal_per_int_types_all cpit,
901 cn_quotas_all cq
902 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
903 AND spq.quota_id = cq.quota_id
904 AND spq.period_id = cp.period_id
905 AND cp.period_status IN ('O', 'F')
906 AND cq.org_id = cp.org_id
907 AND cp.period_id = cpit.cal_period_id
908 AND cp.org_id = cpit.org_id
909 AND cpit.interval_type_id = cq.interval_type_id
910 AND cpit.interval_number = l_interval_number
911 AND spq.period_year = l_period_year
912 ORDER BY spq.period_id
913 ;
914
915 pq_rec period_quotas%ROWTYPE;
916
917
918 -- Get the period quotas that belong to the quota assignment for each
919 -- interval
920
921 CURSOR interval_counts IS
922 SELECT count(spq.srp_period_quota_id) interval_count,
923 cpit.interval_number interval_number,
924 spq.period_year period_year
925 FROM cn_srp_period_quotas_v spq,
926 cn_period_statuses cp,
927 cn_cal_per_int_types_all cpit,
928 cn_quotas_all cq
929 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
930 AND spq.quota_id = cq.quota_id
931 AND spq.period_id = cp.period_id
932 AND cp.period_status IN ('O', 'F')
933 AND cq.org_id = cp.org_id
934 AND cp.period_id = cpit.cal_period_id
935 AND cp.org_id = cpit.org_id
936 AND cpit.interval_type_id = cq.interval_type_id
937 GROUP BY cpit.interval_number,spq.period_year
938 ;
939
940 interval_rec interval_counts%ROWTYPE;
941
942 -- added for intelligent calculation
943 CURSOR l_get_intel_temp_csr IS
944 SELECT period.target_amount, period.period_payment,
945 srp.name, acc.start_date, acc.end_date, srp.org_id
946 FROM cn_srp_period_quotas_all period, cn_salesreps srp,
947 cn_period_statuses acc
948 WHERE period.srp_period_quota_id = x_srp_period_quota_id
949 AND acc.period_id = period.period_id
950 AND acc.org_id = period.org_id
951 AND acc.period_status IN ('O', 'F')
952 AND srp.salesrep_id = period.salesrep_id
953 AND srp.org_id = period.org_id;
954
955 l_temp_target_amount number;
956 l_temp_period_payment number;
957 l_temp_salesrep_name varchar2(240);
958 l_temp_start_date date;
959 l_temp_end_date date;
960 l_temp_org_id NUMBER;
961 -- end of add
962
963 l_target_total NUMBER;
964 l_payment_total NUMBER;
965 l_performance_goal_total NUMBER;
966 l_commission_payed_total NUMBER;
967
968 l_target_amount NUMBER;
969 l_period_payment NUMBER;
970 l_performance_goal NUMBER;
971 l_commission_payed NUMBER;
972
973 g_ext_precision NUMBER;
974
975 BEGIN
976
977 -- get precision
978 select c.extended_precision INTO g_ext_precision
979 from cn_repositories r, gl_sets_of_books b, fnd_currencies c,
980 cn_srp_period_quotas spq
981 where r.org_id = spq.org_id
982 and r.set_of_books_id = b.set_of_books_id
983 and b.currency_code = c.currency_code
984 AND spq.srp_period_quota_id = x_srp_period_quota_id;
985
986 IF x_quota_type_code IN ('EXTERNAL','FORMULA') THEN
987
988 -- newly added by Kai Chen for intellegent calculaltion
989 -- make event on target_amount and period_amount
990 OPEN l_get_intel_temp_csr;
991 FETCH l_get_intel_temp_csr
992 INTO l_temp_target_amount,
993 l_temp_period_payment,
994 l_temp_salesrep_name,
995 l_temp_start_date,
996 l_temp_end_date,
997 l_temp_org_id;
998 CLOSE l_get_intel_temp_csr;
999 -- end of addition
1000
1001 UPDATE cn_srp_period_quotas_all
1002 SET
1003 target_amount = round(nvl(x_target_amount, 0), g_ext_precision),
1004 period_payment = round(nvl(x_period_payment,0), g_ext_precision),
1005 performance_goal_ptd = round(Nvl(x_performance_goal,0), g_ext_precision)
1006 WHERE srp_period_quota_id = x_srp_period_quota_id
1007 ;
1008
1009 -- newly added for intel calc
1010 IF (l_temp_target_amount <> x_target_amount ) OR
1011 (l_temp_period_payment <> x_period_payment)
1012 THEN
1013 cn_mark_events_pkg.mark_event_srp_period_quota(
1014 'CHANGE_SRP_QUOTA_CALC',
1015 l_temp_salesrep_name,
1016 x_srp_period_quota_id,
1017 NULL,
1018 NULL,
1019 l_temp_start_date,
1020 NULL,
1021 l_temp_end_date,
1022 l_temp_org_id);
1023 END IF;
1024 -- end of addition
1025
1026 FOR interval_rec IN interval_counts LOOP
1027
1028 -- Initialize for each interval
1029
1030 l_target_total := 0;
1031 l_payment_total := 0;
1032 l_performance_goal_total := 0;
1033
1034 -- Now that we know the counts per quarter/year we can divide the
1035 -- quota target correctly for each quarter and set the period quota
1036 -- target.
1037
1038 FOR pq_rec IN period_quotas
1039 (
1040 l_interval_number => interval_rec.interval_number
1041 ,l_period_year => interval_rec.period_year) LOOP
1042
1043 l_target_total := l_target_total + pq_rec.target_amount;
1044 l_payment_total := l_payment_total + pq_rec.period_payment;
1045 l_performance_goal_total := l_performance_goal_total
1046 + pq_rec.performance_goal_ptd;
1047
1048 UPDATE cn_srp_period_quotas_all
1049 SET
1050 itd_target = round(nvl(l_target_total,0), g_ext_precision),
1051 itd_payment = round(nvl(l_payment_total,0), g_ext_precision),
1052 performance_goal_itd = round(nvl(l_performance_goal_total,0),g_ext_precision)
1053 WHERE srp_period_quota_id = pq_rec.srp_period_quota_id
1054 ;
1055
1056 END LOOP;
1057
1058 END LOOP;
1059 ELSE
1060 -- only in the case of bonus at the time payee run Bonus commission update
1061 if x_salesrep_id IS NOT NULL AND
1062 x_end_date IS NOT NULL AND
1063 x_quota_id IS NOT NULL THEN
1064 UPDATE cn_srp_period_quotas_all spq
1065 SET
1066 spq.commission_payed_ptd = x_commission_payed_ptd +spq.commission_payed_ptd
1067 WHERE spq.salesrep_id = x_salesrep_id
1068 AND spq.quota_id = x_quota_id
1069 AND exists
1070 (Select 1
1071 from cn_period_statuses aps
1072 where x_end_date between aps.start_date and aps.end_date
1073 AND aps.period_id = spq.period_id
1074 AND aps.period_status IN ('O', 'F')
1075 AND aps.org_id = spq.org_id)
1076 ;
1077 end if;
1078 END IF;
1079
1080 END Update_Record;
1081
1082
1083 -- Name
1084 --
1085 -- Purpose
1086 -- Delete period quota from each rep using the quota in a period
1087 --
1088 -- When the procedure is called Passed Parameters
1089 -- 1. after delete of srp plan assignment. x_srp_plan_assign_id
1090
1091 -- 2. after update of srp plan assign period range x_srp_plan_assign_id
1092 -- We cannot delete all period quotas and then x_start_date
1093 -- simply resinsert for the new period range x_end_date
1094 -- Note:
1095 -- because we want to keep the original target
1096 -- distribution on the periods that remain.
1097 -- NB This is an oracle internal requirement.29/mar/95
1098
1099 -- 3. after delete of comp plan quota assignment x_srp_plan_assign_id
1100 -- x_quota_id
1101 --
1102 ---------------------------------------------------------------------------+
1103 -- PROCEDURE DELETE_RECORD
1104 -- Description:
1105 -- Case 1: Delete will be called from cn_quotas date range has changed
1106 -- if end date is less than the end date of the old quota date
1107 -- Delete will be called from cn_quotas if start_date <> old_start_dt
1108 -- called from cn_quotas_pkg.
1109 -- Values Passed x_quota_id( M) , x_start_date, x_end_date
1110 --
1111 -- Case 2: Delete will be called from cn_period_quotas if the amount
1112 -- columns get updated and check the srp_plan_assigns customised
1113 -- flag if N then delete delete the srp_period_quotas and re create.
1114 -- called from cn_period_quotas
1115 -- Values Passed x_quota_id (M )
1116 --
1117 -- Case 3: Delete will be called from cn_srp_plan_assigns date range has
1118 -- changed if end date is less than the end date of the old plan date
1119 -- Delete will be called from cn_srp_plan_assigns if start_date
1120 -- <> old_start_dt called from cn_srp_plan_assigns_pkg
1121 -- Values Passed : x_srp_plan_assign_id, x_start_date, x_end_date
1122 --
1123 -- Case 4 Delete srp_plan_assigns Called cn_srp_quota_assigns and
1124 -- cn_srp_quota_assigns make a call here
1125 -- srp_plan_assign_id and/or quota_id
1126 ---------------------------------------------------------------------------+
1127 PROCEDURE Delete_Record
1128 ( x_srp_plan_assign_id NUMBER
1129 ,x_quota_id NUMBER
1130 ,x_start_period_id NUMBER
1131 ,x_end_period_id NUMBER
1132 ,x_start_date DATE := NULL
1133 ,x_end_date DATE := NULL ) IS
1134
1135 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
1136 l_msg_count NUMBER;
1137 l_msg_data VARCHAR2(2000);
1138 l_loading_status VARCHAR2(2000);
1139 l_org_id NUMBER;
1140
1141 CURSOR srp_period_quota_ids(l_quota_id number) IS
1142 SELECT srp_period_quota_id, org_id from cn_srp_period_quotas_all
1143 WHERE quota_id = l_quota_id
1144 AND srp_plan_assign_id = nvl(x_srp_plan_assign_id, srp_plan_assign_id)
1145 AND EXISTS ( SELECT 1 from cn_period_statuses p
1146 WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1147 AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1148 AND cn_srp_period_quotas_all.period_id = p.period_id
1149 AND p.period_status IN ('O', 'F')
1150 AND cn_srp_period_quotas_all.org_id = p.org_id);
1151
1152 l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
1153
1154 CURSOR l_bal_id IS
1155 SELECT salesrep_id, period_id, credit_type_id, role_id,
1156 balance2_dtd, balance3_ctd, srp_period_id
1157 from cn_srp_periods_all
1158 where quota_id = x_quota_id
1159 AND org_id = l_org_id
1160 AND EXISTS ( SELECT 1 from cn_period_statuses p
1161 WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1162 AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1163 AND cn_srp_periods_all.period_id = p.period_id
1164 AND p.period_status IN ('O', 'F')
1165 AND cn_srp_periods_all.org_id = p.org_id)
1166 AND (balance2_dtd <> 0 OR balance3_ctd <> 0)
1167 order by salesrep_id, credit_type_id, role_id, period_id;
1168 l_bal_rec l_bal_id%ROWTYPE;
1169
1170 srp_period_quota_id_rec srp_period_quota_ids%ROWTYPE;
1171
1172 -- get number_dim
1173 CURSOR get_number_dim(l_quota_id number) IS
1174 select ccf.number_dim, cq.org_id
1175 from cn_quotas_all cq, cn_calc_formulas_all ccf
1176 where cq.quota_id = l_quota_id
1177 and cq.calc_formula_id = ccf.calc_formula_id;
1178 l_number_dim NUMBER;
1179
1180 CURSOR get_quotas IS
1181 SELECT quota_id FROM cn_srp_quota_assigns_all
1182 WHERE srp_plan_assign_id = x_srp_plan_assign_id;
1183
1184 l_last_period_id number := -1;
1185 l_last_salesrep_id number := -1;
1186 l_last_ct_id number := -1;
1187 l_last_role_id number := -1;
1188
1189 l_end_date_pd DATE;
1190 BEGIN
1191 -- maybe we don't need to check srp_period_quotas_ext
1192 IF x_quota_id IS NOT NULL THEN
1193 l_number_dim := 0;
1194 OPEN get_number_dim(x_quota_id);
1195 FETCH get_number_dim INTO l_number_dim, l_org_id;
1196 CLOSE get_number_dim;
1197 IF l_number_dim > 1 THEN
1198 FOR srp_period_quota_id_rec IN srp_period_quota_ids(x_quota_id) LOOP
1199 populate_srp_period_quotas_ext('DELETE',srp_period_quota_id_rec.srp_period_quota_id, srp_period_quota_id_rec.org_id);
1200 END LOOP;
1201 END IF;
1202 ELSE
1203 FOR q IN get_quotas LOOP
1204 l_number_dim := 0;
1205 OPEN get_number_dim(q.quota_id);
1206 FETCH get_number_dim INTO l_number_dim, l_org_id;
1207 CLOSE get_number_dim;
1208 IF l_number_dim > 1 THEN
1209 FOR srp_period_quota_id_rec IN srp_period_quota_ids(q.quota_id) LOOP
1210 populate_srp_period_quotas_ext('DELETE',srp_period_quota_id_rec.srp_period_quota_id, srp_period_quota_id_rec.org_id);
1211 END LOOP;
1212 END IF;
1213 END LOOP;
1214 END IF;
1215
1216 IF x_srp_plan_assign_id IS NOT NULL THEN
1217
1218 IF x_quota_id IS NULL THEN
1219
1220 IF x_start_date IS NULL THEN
1221 -- Deleted plan assignment
1222 DELETE FROM cn_srp_period_quotas_all
1223 WHERE srp_plan_assign_id = x_srp_plan_assign_id
1224 ;
1225
1226 ELSE
1227 -- make sure we have the right org ID
1228 SELECT org_id INTO l_org_id
1229 FROM cn_srp_plan_assigns
1230 WHERE srp_plan_assign_id = x_srp_plan_assign_id;
1231
1232 -- get end date period of x_end_date
1233 l_end_date_pd := Trunc(cn_end_date_period(x_end_date, l_org_id));
1234
1235 -- plan assignment range changed
1236 DELETE FROM cn_srp_period_quotas_all
1237 WHERE srp_plan_assign_id = x_srp_plan_assign_id
1238 AND EXISTS
1239 ( SELECT 1 FROM cn_period_statuses p
1240 WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1241 -- following line changed for bug 4424669, 4885986
1242 AND trunc(p.end_date) <= Nvl(l_end_date_pd, p.end_date)
1243 --AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
1244 AND p.period_status IN ('O', 'F')
1245 AND cn_srp_period_quotas_all.period_id = p.period_id
1246 AND cn_srp_period_quotas_all.org_id = p.org_id);
1247 END IF;
1248
1249 ELSE -- Quota id IS NOT NULL
1250 -- quota is no longer assigned to the comp plan
1251 -- same as the start date us null
1252 DELETE FROM cn_srp_period_quotas_all
1253 WHERE srp_plan_assign_id = x_srp_plan_assign_id
1254 AND quota_id = x_quota_id
1255 AND NVL(x_start_period_id, period_id) <= period_id -- Bug 3848446, Fixed by Jagpreet Singh.
1256 ;
1257 END IF;
1258
1259 ELSE -- srp_plan_assign_id is NULL
1260 -- changed to no_sync for bug 4019235
1261 FOR l_bal_rec IN l_bal_id LOOP
1262 l_srp_prd_rec.srp_period_id := l_bal_rec.srp_period_id;
1263 l_srp_prd_rec.del_balance2_dtd := l_bal_rec.balance2_dtd*(-1);
1264 l_srp_prd_rec.del_balance3_ctd := l_bal_rec.balance3_ctd*(-1);
1265 cn_srp_periods_pvt.Update_Delta_Srp_Pds_No_Sync
1266 (p_api_version => 1.0,
1267 x_return_status => l_return_status,
1268 x_msg_count => l_msg_count,
1269 x_msg_data => l_msg_data,
1270 p_del_srp_prd_rec => l_srp_prd_rec,
1271 x_loading_status => l_loading_status);
1272 if l_bal_rec.salesrep_id <> l_last_salesrep_id OR
1273 l_bal_rec.role_id <> l_last_role_id OR
1274 l_bal_rec.credit_type_id <> l_last_ct_id then
1275 if l_last_salesrep_id <> -1 then
1276 cn_srp_periods_pvt.sync_accum_balances_start_pd
1277 (p_salesrep_id => l_last_salesrep_id,
1278 p_org_id => l_org_id,
1279 p_credit_type_id => l_last_ct_id,
1280 p_role_id => l_last_role_id,
1281 p_start_period_id => l_last_period_id);
1282 end if;
1283 l_last_salesrep_id := l_bal_rec.salesrep_id;
1284 l_last_role_id := l_bal_rec.role_id;
1285 l_last_ct_id := l_bal_rec.credit_type_id;
1286 l_last_period_id := l_bal_rec.period_id;
1287 end if;
1288 END LOOP;
1289 if l_last_salesrep_id <> -1 then
1290 cn_srp_periods_pvt.sync_accum_balances_start_pd
1291 (p_salesrep_id => l_last_salesrep_id,
1292 p_org_id => l_org_id,
1293 p_credit_type_id => l_last_ct_id,
1294 p_role_id => l_last_role_id,
1295 p_start_period_id => l_last_period_id);
1296 end if;
1297 -- done with changes for bug 4019235
1298
1299 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1300 THEN
1301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1302 END IF;
1303
1304 -- quota date range changed. remove the old periods in preparation
1305 -- for insert of new range
1306 DELETE FROM cn_srp_period_quotas_all
1307 WHERE quota_id = x_quota_id
1308 AND EXISTS ( SELECT 1 from cn_period_statuses p
1309 WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1310 AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1311 AND cn_srp_period_quotas_all.period_id = p.period_id
1312 AND p.period_status IN ('O', 'F')
1313 AND cn_srp_period_quotas_all.org_id = p.org_id);
1314 END IF;
1315
1316
1317 END Delete_Record;
1318 ---------------------------------------------------------------------------+
1319 -- PROCEDURE BEGIN_RECORD
1320 ---------------------------------------------------------------------------+
1321 PROCEDURE Begin_Record
1322 ( x_operation VARCHAR2
1323 ,x_period_target_unit_code VARCHAR2
1324 ,x_srp_period_quota_id NUMBER
1325 ,x_srp_quota_assign_id NUMBER
1326 ,x_srp_plan_assign_id NUMBER
1327 ,x_quota_id NUMBER
1328 ,x_period_id NUMBER
1329 ,x_target_amount NUMBER
1330 ,x_period_payment NUMBER
1331 ,x_performance_goal NUMBER
1332 ,x_quarter_num NUMBER
1333 ,x_period_year NUMBER
1334 ,x_quota_type_code VARCHAR2
1335 ,x_salesrep_id NUMBER := NULL -- only for bonus pay
1336 ,x_end_date DATE := NULL -- only for
1337 ,x_commission_payed_ptd NUMBER := NULL -- only for bonus pay
1338 ,x_creation_date DATE
1339 ,x_created_by NUMBER
1340 ,x_last_update_date DATE
1341 ,x_last_updated_by NUMBER
1342 ,x_last_update_login NUMBER ) IS
1343 BEGIN
1344
1345 IF x_operation = 'INSERT' THEN
1346 -- insert the record for the given quota and salesrep
1347 Insert_Record( x_srp_plan_assign_id => x_srp_plan_assign_id
1348 ,x_quota_id => x_quota_id
1349 ,x_start_period_id => NULL
1350 ,x_end_period_id => NULL
1351 ,x_start_date => NULL
1352 ,x_end_date => NULL );
1353
1354 ELSIF x_operation = 'UPDATE' THEN
1355 -- Update record
1356 Update_Record ( x_period_target_unit_code => x_period_target_unit_code
1357 ,x_srp_period_quota_id => x_srp_period_quota_id
1358 ,x_srp_quota_assign_id => x_srp_quota_assign_id
1359 ,x_period_id => x_period_id
1360 ,x_target_amount => x_target_amount
1361 ,x_period_payment => x_period_payment
1362 ,x_performance_goal => x_performance_goal
1363 ,x_quarter_num => x_quarter_num
1364 ,x_period_year => x_period_year
1365 ,x_quota_type_code => x_quota_type_code
1366 ,x_quota_id => x_quota_id
1367 ,x_salesrep_id => x_salesrep_id
1368 ,x_end_date => x_end_date
1369 ,x_commission_payed_ptd => x_commission_payed_ptd
1370 ,x_last_update_date => x_last_update_date
1371 ,x_last_updated_by => x_last_updated_by
1372 ,x_last_update_login => x_last_update_login);
1373
1374 ELSIF x_operation = 'LOCK' THEN
1375 -- Lock Record
1376 Lock_Record ( x_srp_period_quota_id => x_srp_period_quota_id
1377 ,x_period_id => x_period_id
1378 ,x_target_amount => x_target_amount);
1379
1380 ELSIF X_Operation = 'DELETE' THEN
1381 -- Delete Record
1382 Delete_Record( x_srp_plan_assign_id => x_srp_plan_assign_id
1383 ,x_quota_id => x_quota_id
1384 ,x_start_period_id => NULL
1385 ,x_end_period_id => NULL
1386 ,x_start_date => NULL
1387 ,x_end_date => NULL );
1388 END IF;
1389
1390 END Begin_Record;
1391
1392 -- Name
1393 -- Populate_srp_period_quotas_ext
1394 -- Purpose
1395 -- Populate cn_srp_period_quota_ext table
1396 -- We take the following operations as parameters:
1397 -- INSERT : insert records in cn_srp_period_quotas_ext if necessary
1398 -- DELETE : delete all records in cn_srp_period_quotas_ext for certain srp_period_quota_id
1399
1400
1401 PROCEDURE populate_srp_period_quotas_ext
1402 (x_operation VARCHAR2,
1403 x_srp_period_quota_id NUMBER,
1404 x_org_id NUMBER,
1405 x_number_dim NUMBER := fnd_api.g_miss_num) IS
1406
1407 l_user_id NUMBER(15);
1408 l_login_id NUMBER(15);
1409 l_number_dim NUMBER(15);
1410
1411 -- only use this if number_dim not used
1412 CURSOR DIM_NUMBER_CUR IS
1413 select ccf.number_dim
1414 from cn_srp_period_quotas_all cspq, cn_quotas_all cq,
1415 cn_calc_formulas_all ccf
1416 where cspq.srp_period_quota_id = x_srp_period_quota_id
1417 and cq.quota_id = cspq.quota_id
1418 and cq.calc_formula_id = ccf.calc_formula_id ;
1419
1420 l_count NUMBER;
1421 BEGIN
1422 l_user_id := fnd_global.user_id;
1423
1424 l_login_id := fnd_global.login_id;
1425
1426 IF x_operation = 'INSERT' THEN
1427 -- get number dim if necessary
1428 IF x_number_dim = fnd_api.g_miss_num THEN
1429 l_number_dim := 0;
1430 OPEN dim_number_cur;
1431 FETCH dim_number_cur INTO l_number_dim;
1432 CLOSE dim_number_cur;
1433 ELSE
1434 l_number_dim := x_number_dim;
1435 END IF;
1436
1437 FOR i_seq in 2..l_number_dim LOOP
1438 insert into cn_srp_period_quotas_ext_all
1439 (
1440 srp_period_quota_ext_id,
1441 srp_period_quota_id,
1442 input_sequence,
1443 created_by,
1444 creation_date,
1445 last_update_login,
1446 last_update_date,
1447 last_updated_by,
1448 org_id)
1449 select cn_srp_period_quotas_ext_s.nextval,
1450 x_srp_period_quota_id,
1451 i_seq,
1452 l_user_id,
1453 sysdate,
1454 l_login_id,
1455 sysdate,
1456 l_user_id,
1457 x_org_id
1458 from dual where not exists ( select 1 from cn_srp_period_quotas_ext_all
1459 where srp_period_quota_id = x_srp_period_quota_id
1460 and input_sequence = i_seq );
1461 END LOOP;
1462 ELSIF x_operation = 'DELETE' THEN
1463 DELETE FROM cn_srp_period_quotas_ext_all
1464 WHERE srp_period_quota_id = x_srp_period_quota_id;
1465 END IF;
1466 END;
1467
1468
1469 -- Name
1470 -- select_summary
1471 -- Purpose
1472 -- Maintain running totals
1473 ---------------------------------------------------------------------------+
1474 -- PROCEDURE SELECT_SUMMARY
1475 ---------------------------------------------------------------------------+
1476 PROCEDURE select_summary
1477 ( x_srp_quota_assign_id NUMBER
1478 ,x_total IN OUT NOCOPY NUMBER
1479 ,x_total_rtot_db IN OUT NOCOPY NUMBER) IS
1480 BEGIN
1481
1482 SELECT nvl(sum(target_amount),0)
1483 INTO x_total
1484 FROM cn_srp_period_quotas_all
1485 WHERE srp_quota_assign_id = x_srp_quota_assign_id
1486 ;
1487 x_total_rtot_db := x_total;
1488
1489 EXCEPTION
1490 WHEN no_data_found THEN null;
1491
1492 END select_summary;
1493
1494 -- Name
1495 -- Period_target
1496 -- Purpose
1497 -- Distribute target/payment amount over periods
1498 -- Notes
1499 --
1500 -- If period_type_code(a.k.a "Interval") = "PERIOD"
1501 -- We do not need to divide up the quota target and distribute it
1502 -- over the srp periods because the entire quota target is applied
1503 -- to each srp period. No math required.
1504 -- If the period_type_code = "QUARTER"
1505 -- We need to apply the quota target to each quarter which means
1506 -- dividing the target by the number of periods in the quarter and
1507 -- assigning that amount to each period
1508 -- e.g. Target = 100 Jan 33.3333
1509 -- Feb 33.3333
1510 -- Mar 33.3333
1511 -- If a quarter has less than 3 periods the target amount
1512 -- will be divided over the reduced number of periods.
1513 -- e.g. Target = 100 Jan 33.3333
1514 -- Feb 33.3333
1515 -- Mar 33.3333
1516 -- Apr 100
1517 -- If the period_type_code = "YEAR"
1518 -- The target will be divided by the number of periods in each year that
1519 -- the quota is active. This deals with situations where the quota is
1520 -- assigned for less than 12 periods in any year.
1521 ---------------------------------------------------------------------------+
1522 -- PROCEDURE DISTRIBUTE_TARGET
1523 ---------------------------------------------------------------------------+
1524 PROCEDURE Distribute_Target
1525 ( x_srp_quota_assign_id NUMBER
1526 ,x_target NUMBER
1527 ,x_period_target_unit_code VARCHAR2) IS
1528
1529 CURSOR period_quotas(l_interval_number NUMBER, l_period_year NUMBER) IS
1530 SELECT spq.srp_period_quota_id
1531 FROM cn_srp_period_quotas_v spq,
1532 cn_period_statuses cp,
1533 cn_cal_per_int_types_all cpit,
1534 cn_quotas_all cq
1535 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
1536 AND spq.quota_id = cq.quota_id
1537 AND spq.period_id = cp.period_id
1538 AND cp.period_status IN ('O', 'F')
1539 AND cq.org_id = cp.org_id
1540 AND cp.period_id = cpit.cal_period_id
1541 AND cp.org_id = cpit.org_id
1542 AND cpit.interval_type_id = cq.interval_type_id
1543 AND cpit.interval_number = l_interval_number
1544 AND spq.period_year = l_period_year
1545 ORDER BY spq.period_id
1546 ;
1547
1548 pq_rec period_quotas%ROWTYPE;
1549
1550
1551 -- Get the period quotas that belong to the quota assignment for each
1552 -- interval
1553
1554 CURSOR interval_counts IS
1555 SELECT count(spq.srp_period_quota_id) interval_count,
1556 cpit.interval_number interval_number,
1557 spq.period_year period_year
1558 FROM cn_srp_period_quotas_v spq,
1559 cn_period_statuses cp,
1560 cn_cal_per_int_types_all cpit,
1561 cn_quotas_all cq
1562 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
1563 AND spq.quota_id = cq.quota_id
1564 AND spq.period_id = cp.period_id
1565 AND cp.period_status IN ('O', 'F')
1566 AND cq.org_id = cp.org_id
1567 AND cp.period_id = cpit.cal_period_id
1568 AND cp.org_id = cpit.org_id
1569 AND cpit.interval_type_id = cq.interval_type_id
1570 GROUP BY cpit.interval_number,spq.period_year
1571 ;
1572
1573 interval_rec interval_counts%ROWTYPE;
1574
1575
1576 l_period_count NUMBER;
1577 l_running_total_target NUMBER;
1578 l_total_periods NUMBER;
1579 l_period_target NUMBER;
1580 l_running_total_payment NUMBER;
1581 l_period_payment NUMBER;
1582
1583 l_running_performance_goal NUMBER;
1584 l_performance_goal NUMBER;
1585
1586 l_srp_quota_assign_id NUMBER(15);
1587 l_quota_target NUMBER;
1588 l_quota_payment NUMBER;
1589 l_quota_performance_goal NUMBER;
1590 l_dist_rule_code VARCHAR2(30);
1591 l_period_type_code VARCHAR2(30);
1592 l_period_performance_goal NUMBER;
1593
1594 g_ext_precision NUMBER;
1595
1596 BEGIN
1597
1598 -- get precision
1599 select c.extended_precision INTO g_ext_precision
1600 from cn_repositories r, gl_sets_of_books b, fnd_currencies c,
1601 cn_srp_quota_assigns sqa
1602 where r.org_id = sqa.org_id
1603 and r.set_of_books_id = b.set_of_books_id
1604 and b.currency_code = c.currency_code
1605 AND sqa.srp_quota_assign_id = x_srp_quota_assign_id;
1606
1607 -- Get quota assignment info for the quota to be distributed
1608 --
1609
1610 SELECT Nvl(qa.target,0)
1611 ,nvl(qa.payment_amount, 0)
1612 ,Nvl(qa.performance_goal,0)
1613 ,qa.period_target_dist_rule_code
1614 ,cn_chk_plan_element_pkg.get_interval_name(q.interval_type_id, q.org_id) period_type_code
1615 INTO l_quota_target
1616 ,l_quota_payment
1617 ,l_performance_goal
1618 ,l_dist_rule_code
1619 ,l_period_type_code
1620 FROM cn_srp_quota_assigns_all qa,
1621 cn_quotas_all q
1622 WHERE qa.srp_quota_assign_id = x_srp_quota_assign_id
1623 AND q.quota_id = qa.quota_id
1624 AND qa.period_target_dist_rule_code <> 'USER_DEFINED'
1625 ;
1626
1627 -- Currently this is the only distribution rule we support
1628
1629 IF l_dist_rule_code = 'EQUAL' THEN
1630
1631
1632 FOR interval_rec IN interval_counts LOOP
1633
1634 -- Initialize for each interval
1635 l_period_count := 0;
1636 l_running_total_target := 0;
1637 l_period_target := 0;
1638 l_running_total_payment := 0;
1639 l_period_payment := 0;
1640 l_running_performance_goal := 0;
1641 l_period_performance_goal := 0;
1642
1643 -- Now that we know the counts per quarter/year we can divide the
1644 -- quota target correctly for each quarter and set the period quota
1645 -- target.
1646
1647 FOR pq_rec IN period_quotas
1648 (
1649 l_interval_number => interval_rec.interval_number
1650 ,l_period_year => interval_rec.period_year) LOOP
1651
1652 l_period_count := l_period_count +1;
1653
1654 l_period_target := ( ( l_quota_target *
1655 (l_period_count / interval_rec.interval_count)
1656 ) - l_running_total_target
1657 );
1658
1659 l_running_total_target := l_running_total_target + l_period_target;
1660
1661 l_period_payment := ( ( l_quota_payment *
1662 (l_period_count / interval_rec.interval_count)
1663 ) - l_running_total_payment
1664 );
1665
1666 l_running_total_payment := l_running_total_payment + l_period_payment;
1667
1668 l_period_performance_goal := ( ( l_performance_goal *
1669 (l_period_count / interval_rec.interval_count)
1670 ) - l_running_performance_goal
1671 );
1672
1673 l_running_performance_goal := l_running_performance_goal +
1674 l_period_performance_goal;
1675
1676 UPDATE cn_srp_period_quotas_all
1677 SET
1678 target_amount = round(nvl(l_period_target, 0), g_ext_precision),
1679 itd_target = round(nvl(l_running_total_target,0), g_ext_precision),
1680 period_payment = round(nvl(l_period_payment,0), g_ext_precision),
1681 itd_payment = round(nvl(l_running_total_payment,0), g_ext_precision),
1682 performance_goal_ptd = round(nvl(l_period_performance_goal,0), g_ext_precision),
1683 performance_goal_itd = round(nvl(l_running_performance_goal,0),g_ext_precision)
1684 WHERE srp_period_quota_id = pq_rec.srp_period_quota_id
1685 ;
1686
1687 END LOOP;
1688
1689 END LOOP;
1690 END IF;
1691
1692 END distribute_target;
1693 --
1694 -- Purpose: synchronize the target / payment of srp_period_quotas table
1695 -- with period_quotas table when customized_flag is changed to
1696 -- 'N' and itd_flag is 'Y'
1697 --
1698 ---------------------------------------------------------------------------+
1699 -- PROCEDURE SYNCH_TARGET
1700 ---------------------------------------------------------------------------+
1701 PROCEDURE synch_target ( x_srp_plan_assign_id NUMBER,
1702 x_quota_id NUMBER) IS
1703
1704 BEGIN
1705
1706 cn_srp_period_quotas_pkg.delete_record
1707 (
1708 x_srp_plan_assign_id => x_srp_plan_assign_id
1709 ,x_quota_id => x_quota_id
1710 ,x_start_period_id => null
1711 ,x_end_period_id => null);
1712
1713 cn_srp_period_quotas_pkg.insert_record
1714 (
1715 x_srp_plan_assign_id => x_srp_plan_assign_id
1716 ,x_quota_id => x_quota_id
1717 ,x_start_period_id => null
1718 ,x_end_period_id => null);
1719
1720 END synch_target;
1721
1722 END CN_SRP_PERIOD_QUOTAS_PKG;
[Home] [Help]