[Home] [Help]
PACKAGE BODY: APPS.CN_PERIOD_QUOTAS_PKG
Source
1 PACKAGE BODY cn_period_quotas_pkg AS
2 /* $Header: cnprdqob.pls 120.3 2006/03/22 05:53:28 chanthon ship $ */
3
4 /* ------------------------------------------------------------------------+
5 | Public Routine Bodies |
6 --------------------------------------------------------------------------*/
7 -- Name
8 --
9 -- Purpose
10 --
11 -- Notes Modified the Package due to changes in the Design
12 -- Modified Date 20-JUL-99 By Kumar Sivasankaran
13 -- Modified Date 14-Sep-04 By Jxsingh, Fixed Bug# 3848446
14 g_precision NUMBER;
15 g_ext_precision NUMBER;
16 g_min_acct_unit NUMBER;
17
18 PROCEDURE get_uid (
19 x_period_quota_id IN OUT NOCOPY NUMBER
20 )
21 IS
22 BEGIN
23 SELECT cn_period_quotas_s.NEXTVAL
24 INTO x_period_quota_id
25 FROM DUAL;
26 END get_uid;
27
28 --| ---------------------------------------------------------------------+
29 --| Function Name : previous_period
30 --| ---------------------------------------------------------------------+
31 FUNCTION previous_period (
32 p_start_date DATE,
33 p_org_id NUMBER
34 )
35 RETURN cn_acc_period_statuses_v.start_date%TYPE
36 IS
37 l_previous_start_date cn_acc_period_statuses_v.start_date%TYPE;
38 BEGIN
39 SELECT MAX (start_date)
40 INTO l_previous_start_date
41 FROM cn_acc_period_statuses_v
42 WHERE TRUNC (start_date) <= TRUNC (p_start_date)
43 AND period_status IN ('F', 'O')
44 AND org_id = p_org_id;
45
46 -- Begin fix of Bug 1942390 hlchen
47 IF (l_previous_start_date IS NULL)
48 THEN
49 SELECT MIN (start_date)
50 INTO l_previous_start_date
51 FROM cn_acc_period_statuses_v
52 WHERE period_status IN ('F', 'O')
53 AND org_id = p_org_id;
54 END IF;
55
56 -- End fix of Bug 1942390 hlchen
57 RETURN l_previous_start_date;
58 EXCEPTION
59 WHEN NO_DATA_FOUND
60 THEN
61 RETURN NULL;
62 END previous_period;
63
64 -- Name Begin Record
65 --
66 -- Purpose: Depending on the Operation the right procudure,
67 -- Insert_record, Delete_Records, Lock_Record or
68 -- Update_Record is called
69 --
70 -- Notes: This is the table handler for the CN_Period_Quotas_Pkg
71 --
72 --
73 PROCEDURE begin_record (
74 x_operation VARCHAR2,
75 x_period_quota_id IN OUT NOCOPY NUMBER,
76 x_period_id NUMBER,
77 x_quota_id NUMBER,
78 x_period_target NUMBER,
79 x_itd_target NUMBER,
80 x_period_payment NUMBER,
81 x_itd_payment NUMBER,
82 x_quarter_num NUMBER,
83 x_period_year NUMBER,
84 x_creation_date DATE,
85 x_last_update_date DATE,
86 x_last_update_login NUMBER,
87 x_last_updated_by NUMBER,
88 x_created_by NUMBER,
89 x_period_type_code VARCHAR2,
90 x_performance_goal NUMBER
91 )
92 IS
93 l_org_id NUMBER;
94 l_varchar VARCHAR2 (1000) := NULL;
95 l_itd_perf_goal NUMBER;
96 l_itd_pmt_amount NUMBER;
97 l_itd_tgt NUMBER;
98 l_object_version_number NUMBER;
99 BEGIN
100 SELECT org_id
101 INTO l_org_id
102 FROM cn_quotas
103 WHERE quota_id = x_quota_id;
104
105 fnd_currency.get_info (cn_global_var.get_currency_code (p_org_id => l_org_id), g_precision, g_ext_precision, g_min_acct_unit);
106
107 IF x_operation = 'INSERT'
108 THEN
109 INSERT_RECORD (x_period_quota_id => x_period_quota_id,
110 p_period_id => x_period_id,
111 p_quota_id => x_quota_id,
112 p_period_target => x_period_target,
113 p_itd_target => x_itd_target,
114 p_period_payment => x_period_payment,
115 p_itd_payment => x_itd_payment,
116 p_quarter_num => x_quarter_num,
117 p_period_year => x_period_year,
118 p_creation_date => x_creation_date,
119 p_last_update_date => x_last_update_date,
120 p_last_update_login => x_last_update_login,
121 p_last_updated_by => x_last_updated_by,
122 p_created_by => x_created_by,
123 p_period_type_code => x_period_type_code,
124 p_performance_goal => x_performance_goal
125 );
126 ELSIF x_operation = 'UPDATE'
127 THEN
128 UPDATE_RECORD (x_period_quota_id,
129 x_quota_id,
130 x_period_id,
131 x_period_target,
132 x_period_payment,
133 x_performance_goal,
134 x_last_update_date,
135 x_last_update_login,
136 x_last_updated_by,
137 l_itd_tgt,
138 l_itd_pmt_amount,
139 l_itd_perf_goal,
140 l_object_version_number
141 );
142 ELSIF x_operation = 'DELETE'
143 THEN
144 DELETE_RECORD (x_quota_id);
145 END IF;
146 END begin_record;
147
148 -- Name : Insert_record
149 --
150 -- Purpose : To insert the record with the different paras
151 --
152 -- Notes: Insert_Record for CN_Period_Quotas
153 --
154 --
155 PROCEDURE INSERT_RECORD (
156 x_period_quota_id IN OUT NOCOPY NUMBER,
157 p_period_id NUMBER,
158 p_quota_id NUMBER,
159 p_period_target NUMBER,
160 p_itd_target NUMBER,
161 p_period_payment NUMBER,
162 p_itd_payment NUMBER,
163 p_quarter_num NUMBER,
164 p_period_year NUMBER,
165 p_creation_date DATE,
166 p_last_update_date DATE,
167 p_last_update_login NUMBER,
168 p_last_updated_by NUMBER,
169 p_created_by NUMBER,
170 p_period_type_code VARCHAR2,
171 p_performance_goal NUMBER
172 )
173 IS
174 CURSOR l_period_quotas_cr
175 IS
176 SELECT --cn_period_quotas_s.nextval,
177 p_period_id c1,
178 p_quota_id c2,
179 p_period_target c3,
180 0 c4,
181 p_period_payment c5,
182 p_performance_goal c6,
183 0 c7,
184 p.quarter_num c8,
185 p.period_year c9,
186 p_creation_date c10,
187 p_last_update_date c11,
188 p_last_update_login c12,
189 p_last_updated_by c13,
190 p_created_by c14,
191 q.org_id org_id
192 FROM cn_acc_period_statuses_v p, cn_quotas q
193 WHERE q.org_id = p.org_id
194 AND q.quota_id = p_quota_id
195 AND p.period_id = p_period_id
196 AND NOT EXISTS (SELECT 'this period_quota already exists'
197 FROM cn_period_quotas pq
198 WHERE pq.period_id = p.period_id
199 AND pq.quota_id = q.quota_id
200 AND pq.org_id = q.org_id);
201
202 l_period_quota l_period_quotas_cr%ROWTYPE;
203 l_pqs NUMBER;
204 BEGIN
205 IF p_quota_id IS NOT NULL
206 THEN
207 FOR l_period_quota IN l_period_quotas_cr
208 LOOP
209 SELECT cn_period_quotas_s.NEXTVAL
210 INTO l_pqs
211 FROM DUAL;
212
213 INSERT INTO cn_period_quotas
214 (period_quota_id,
215 period_id,
216 quota_id,
217 period_target,
218 itd_target,
219 period_payment,
220 performance_goal,
221 itd_payment,
222 quarter_num,
223 period_year,
224 creation_date,
225 last_update_date,
226 last_update_login,
227 last_updated_by,
228 created_by,
229 org_id
230 )
231 SELECT l_pqs,
232 l_period_quota.c1,
233 l_period_quota.c2,
234 l_period_quota.c3,
235 l_period_quota.c4,
236 l_period_quota.c5,
237 l_period_quota.c6,
238 l_period_quota.c7,
239 l_period_quota.c8,
240 l_period_quota.c9,
241 l_period_quota.c10,
242 l_period_quota.c11,
243 l_period_quota.c12,
244 l_period_quota.c13,
245 l_period_quota.c14,
246 l_period_quota.org_id
247 FROM DUAL;
248
249 -- clku, call populate_itd_values to populate itd value
250 -- of the newly inserted records
251 populate_itd_values (l_pqs, l_period_quota.c2);
252 END LOOP;
253 END IF;
254 END INSERT_RECORD;
255
256 -- Name : Insert_record
257 -- Purpose : To insert the records
258 -- Notes: Insert_Record for CN_Period_Quotas
259 PROCEDURE INSERT_RECORD (
260 x_quota_id NUMBER
261 )
262 IS
263 l_user_id NUMBER (15);
264 l_resp_id NUMBER (15);
265 l_login_id NUMBER (15);
266
267 --clku
268 CURSOR l_period_quotas_cr
269 IS
270 SELECT p.period_id c1,
271 q.quota_id c2,
272 0 c3,
273 0 c4,
274 0 c5,
275 0 c6,
276 0 c7,
277 p.quarter_num c8,
278 p.period_year c9,
279 SYSDATE c10,
280 SYSDATE c11,
281 l_login_id c12,
282 l_user_id c13,
283 l_user_id c14,
284 q.org_id
285 FROM cn_quotas q,
286 cn_acc_period_statuses_v p
287 WHERE q.quota_id = x_quota_id
288 AND p.start_date >= previous_period (q.start_date, q.org_id)
289 AND p.end_date <= cn_api.next_period (NVL (q.end_date, p.end_date), q.org_id)
290 AND q.org_id = p.org_id
291 AND NOT EXISTS (SELECT 'this period_quota already exists'
292 FROM cn_period_quotas pq
293 WHERE pq.period_id = p.period_id
294 AND pq.quota_id = q.quota_id
295 AND pq.org_id = q.org_id)
296 -- bug 2460926, check if all the open period ends before the specified start_date
297 AND EXISTS (SELECT r1.end_date
298 FROM cn_acc_period_statuses_v r1
299 WHERE r1.end_date > q.start_date
300 AND r1.org_id = q.org_id);
301
302 l_period_quota l_period_quotas_cr%ROWTYPE;
303 l_pqs NUMBER;
304
305 l_min_date cn_acc_period_statuses_v.start_date%TYPE;
306 l_insert_flag VARCHAR2(1);
307 l_end_date DATE;
308 l_org_id NUMBER;
309
310
311 BEGIN
312 l_user_id := fnd_global.user_id;
313 l_resp_id := fnd_global.resp_id;
314 l_login_id := fnd_global.login_id;
315 l_insert_flag := 'Y';
316
317 select end_date, org_id into l_end_date, l_org_id from
318 cn_quotas_v where quota_id = x_quota_id;
319
320 select min(start_date) into l_min_date from cn_acc_period_statuses_v
321 where period_status IN ('F', 'O') and org_id = l_org_id;
322
323
324 IF (l_end_date IS NOT NULL AND trunc(l_end_date) < trunc(l_min_date)) THEN
325 l_insert_flag := 'N';
326 END IF;
327
328 IF (x_quota_id IS NOT NULL AND l_insert_flag = 'Y') THEN
329
330 FOR l_period_quota IN l_period_quotas_cr
331 LOOP
332 SELECT cn_period_quotas_s.NEXTVAL
333 INTO l_pqs
334 FROM DUAL;
335
336 INSERT INTO cn_period_quotas
337 (period_quota_id,
338 period_id,
339 quota_id,
340 period_target,
341 itd_target,
342 period_payment,
343 performance_goal,
344 itd_payment,
345 quarter_num,
346 period_year,
347 creation_date,
348 last_update_date,
349 last_update_login,
350 last_updated_by,
351 created_by,
352 org_id)
353 SELECT l_pqs,
354 l_period_quota.c1,
355 l_period_quota.c2,
356 l_period_quota.c3,
357 l_period_quota.c4,
358 l_period_quota.c5,
359 l_period_quota.c6,
360 l_period_quota.c7,
361 l_period_quota.c8,
362 l_period_quota.c9,
363 l_period_quota.c10,
364 l_period_quota.c11,
365 l_period_quota.c12,
366 l_period_quota.c13,
367 l_period_quota.c14,
368 l_period_quota.org_id
369 FROM DUAL;
370
371 -- clku, call populate_itd_values to populate itd value
372 -- of the newly inserted records
373 -- populate_itd_values (l_pqs, l_period_quota.c2);
374 END LOOP;
375 END IF;
376 IF x_quota_id IS NOT NULL THEN
377 sync_ITD_values(x_quota_id);
378 END IF;
379
380 END INSERT_RECORD;
381
382 --
383 -- Name: Update Record
384 -- Notes: Update record for the CN_Period_Quotas
385 --,
386 --x_itd_payment NUMBER,
387 --x_quarter_num NUMBER,
388 --x_period_year NUMBER,
389 --x_period_type_code VARCHAR2,
390 PROCEDURE UPDATE_RECORD (
391 p_period_quota_id NUMBER,
392 p_quota_id NUMBER,
393 p_period_id NUMBER,
394 p_period_target NUMBER,
395 p_period_payment NUMBER,
396 p_performance_goal NUMBER,
397 p_last_update_date DATE,
398 p_last_update_login NUMBER,
399 p_last_updated_by NUMBER,
400 x_itd_target OUT NOCOPY NUMBER,
401 x_itd_payment_amount OUT NOCOPY NUMBER,
402 x_itd_performance_amount OUT NOCOPY NUMBER,
403 x_object_version_number OUT NOCOPY NUMBER
404 )
405 IS
406 -- Get the srp_quota_assign info based on this quota
407 CURSOR srp_quota_assigns
408 IS
409 SELECT srp_plan_assign_id
410 FROM cn_srp_quota_assigns
411 WHERE quota_id = p_quota_id AND customized_flag = 'N';
412
413 CURSOR period_quotas (
414 l_interval_number NUMBER,
415 l_period_year NUMBER
416 ) IS
417 SELECT p.period_quota_id,
418 p.period_target,
419 p.period_payment,
420 p.performance_goal
421 FROM cn_period_quotas p,
422 cn_acc_period_statuses_v cp,
423 cn_cal_per_int_types cpit,
424 cn_quotas cq
425 WHERE p.quota_id = p_quota_id
426 AND p.quota_id = cq.quota_id
427 AND cq.org_id = p.org_id
428 AND p.period_id = cp.period_id
429 AND cp.period_id = cpit.cal_period_id
430 AND cpit.interval_type_id = cq.interval_type_id
431 AND cpit.interval_number = l_interval_number
432 AND p.period_year = l_period_year
433 AND cq.org_id = p.org_id
434 AND cq.org_id = cp.org_id
435 AND cq.org_id = cpit.org_id
436 ORDER BY p.period_id;
437
438 -- Get the period quotas that belong to the quota assignment for each interval
439 CURSOR interval_counts
440 IS SELECT COUNT (p.period_quota_id) interval_count,
441 cpit.interval_number interval_number,
442 p.period_year period_year
443 FROM cn_period_quotas p,
444 cn_acc_period_statuses_v cp,
445 cn_cal_per_int_types cpit,
446 cn_quotas cq
447 WHERE p.quota_id = p_quota_id
448 AND p.quota_id = cq.quota_id
449 AND p.period_id = cp.period_id
450 AND cp.period_id = cpit.cal_period_id
451 AND cpit.interval_type_id = cq.interval_type_id
452 AND cq.org_id = p.org_id
453 AND cq.org_id = cp.org_id
454 AND cq.org_id = cpit.org_id
455 GROUP BY cpit.interval_number, p.period_year;
456
457 sqa_rec srp_quota_assigns%ROWTYPE;
458 pq_rec period_quotas%ROWTYPE;
459 interval_rec interval_counts%ROWTYPE;
460 l_target_total NUMBER;
461 l_payment_total NUMBER;
462 l_performance_goal_total NUMBER;
463 l_period_id NUMBER;
464 BEGIN
465 -- get the current ovn
466 SELECT object_version_number
467 INTO x_object_version_number
468 FROM cn_period_quotas
469 WHERE period_quota_id = p_period_quota_id
470 AND period_id = p_period_id AND quota_id = p_quota_id;
471
472 x_object_version_number := NVL (x_object_version_number, 0) + 1;
473
474 UPDATE cn_period_quotas
475 SET period_target = p_period_target,
476 quota_id = p_quota_id,
477 period_id = p_period_id,
478 period_payment = p_period_payment,
479 performance_goal = p_performance_goal,
480 last_update_date = p_last_update_date,
481 last_update_login = p_last_update_login,
482 last_updated_by = p_last_updated_by,
483 object_version_number = x_object_version_number
484 WHERE period_quota_id = p_period_quota_id;
485
486 --UPDATE INTERVAL_TO_DATE COLUMNS
487 FOR interval_rec IN interval_counts
488 LOOP
489 -- Initialize for each interval
490 l_target_total := 0;
491 l_payment_total := 0;
492 l_performance_goal_total := 0;
493
494 -- Now that we know the counts per quarter/year we can divide the
495 -- quota target correctly for each quarter and set the period quota target.
496 FOR pq_rec IN period_quotas (l_interval_number => interval_rec.interval_number, l_period_year => interval_rec.period_year)
497 LOOP
498 l_target_total := l_target_total + pq_rec.period_target;
499 l_payment_total := l_payment_total + pq_rec.period_payment;
500 l_performance_goal_total := l_performance_goal_total + pq_rec.performance_goal;
501 -- null precision bad for business
502 g_ext_precision := NVL (g_ext_precision, 10);
503 l_target_total := ROUND (NVL (l_target_total, 0), g_ext_precision);
504 l_payment_total := ROUND (NVL (l_payment_total, 0), g_ext_precision);
505 l_performance_goal_total := ROUND (NVL (l_performance_goal_total, 0), g_ext_precision);
506
507 UPDATE cn_period_quotas
508 SET itd_target = l_target_total,
509 itd_payment = l_payment_total,
510 performance_goal_itd = l_performance_goal_total
511 WHERE period_quota_id = pq_rec.period_quota_id;
512
513 IF pq_rec.period_quota_id = p_period_quota_id
514 THEN
515 x_itd_target := l_target_total;
516 x_itd_payment_amount := l_payment_total;
517 x_itd_performance_amount := l_performance_goal_total;
518 END IF;
519 END LOOP;
520 END LOOP;
521
522 -- End - Bug# 3848446, Fixed by Jagpreet Singh
523 FOR sqa_rec IN srp_quota_assigns
524 LOOP -- Bug# 3848446, Fixed by Jagpreet Singh
525 cn_srp_period_quotas_pkg.DELETE_RECORD (x_srp_plan_assign_id => sqa_rec.srp_plan_assign_id,
526 x_quota_id => p_quota_id,
527 x_start_period_id => p_period_id,
528 x_end_period_id => NULL
529 );
530 -- Bug# 3848446, Fixed by Jagpreet Singh
531 cn_srp_period_quotas_pkg.INSERT_RECORD (x_srp_plan_assign_id => sqa_rec.srp_plan_assign_id,
532 x_quota_id => p_quota_id,
533 x_start_period_id => p_period_id,
534 x_end_period_id => NULL,
535 x_start_date => NULL,
536 x_end_date => NULL
537 );
538 END LOOP;
539 END UPDATE_RECORD;
540
541 -- Name
542 --
543 -- Purpose
544 --
545 -- Notes
546 --
547 --
548 PROCEDURE DELETE_RECORD (x_quota_id NUMBER)
549 IS
550 period_quotas_count NUMBER;
551 BEGIN
552 SELECT COUNT (*)
553 INTO period_quotas_count
554 FROM cn_period_quotas pq
555 WHERE pq.quota_id = x_quota_id;
556
557 IF period_quotas_count > 0
558 THEN
559 DELETE FROM cn_period_quotas
560 WHERE quota_id = x_quota_id;
561 END IF;
562 END DELETE_RECORD;
563
564 -- Name
565 -- Distribute_Target
566 -- Purpose
567 -- Distribute target/payment amount over periods
568 -- Notes
569 PROCEDURE distribute_target (
570 x_quota_id NUMBER
571 )
572 IS
573
574 l_start_date DATE;
575 l_end_date DATE;
576 l_start_period_id NUMBER;
577 l_end_period_id NUMBER;
578 l_max_date cn_acc_period_statuses_v.end_date%TYPE;
579 l_min_date cn_acc_period_statuses_v.start_date%TYPE;
580 l_delete_all_flag CHAR(1);
581 l_org_id cn_quotas.org_id%TYPE;
582 BEGIN
583 -- get start date, end date for the pe
584 l_delete_all_flag := 'N';
585 select start_date, end_date, org_id
586 into l_start_date, l_end_date, l_org_id from
587 cn_quotas_v where quota_id = x_quota_id;
588 -- max date of open or future entry periods
589 select max(end_date) into l_max_date from cn_acc_period_statuses_v
590 where period_status IN ('F', 'O') and org_id = l_org_id;
591 -- min date of open or future entry periods
592 select min(start_date) into l_min_date from cn_acc_period_statuses_v
593 where period_status IN ('F', 'O') and org_id = l_org_id;
594
595
596 IF (trunc(l_start_date) < trunc(l_min_date)) THEN
597 select min(period_id) into l_start_period_id
598 from cn_acc_period_statuses_v
599 where period_status IN ('F', 'O') and org_id = l_org_id;
600 ELSIF (trunc(l_start_date) > trunc(l_max_date)) THEN
601 -- select max(period_id) into l_start_period_id
602 -- from cn_acc_period_statuses_v
603 -- where period_status IN ('F', 'O');
604 -- delete all records
605 l_delete_all_flag := 'Y';
606 ELSE
607 SELECT period_id
608 INTO l_start_period_id
609 FROM cn_acc_period_statuses_v
610 WHERE l_start_date BETWEEN start_date and end_date
611 AND period_status IN ('F', 'O') and org_id = l_org_id;
612 END IF;
613
614 IF (l_end_date IS NOT NULL) THEN
615 IF (trunc(l_end_date) < trunc(l_min_date)) THEN
616 -- select min(period_id) into l_end_period_id
617 -- from cn_acc_period_statuses_v
618 -- where period_status IN ('F', 'O');
619 -- delete all records
620 l_delete_all_flag := 'Y';
621 ELSIF (trunc(l_end_date) > trunc(l_max_date)) THEN
622 select max(period_id) into l_end_period_id
623 from cn_acc_period_statuses_v
624 where period_status IN ('F', 'O') and org_id = l_org_id;
625 ELSE
626 SELECT period_id
627 INTO l_end_period_id
628 FROM cn_acc_period_statuses_v
629 WHERE l_end_date BETWEEN start_date and end_date
630 AND period_status IN ('F', 'O') and org_id = l_org_id;
631 END IF;
632 END IF;
633 -- l_start_period_id := cn_api.get_acc_period_id(l_start_date);
634 -- IF (l_end_date IS NOT NULL) THEN
635 -- l_end_period_id := cn_api.get_acc_period_id(l_end_date);
636 -- END IF;
637 IF (l_delete_all_flag = 'Y') THEN
638 DELETE_RECORD(x_quota_id);
639 ELSIF (l_end_date IS NOT NULL) THEN
640 delete from cn_period_quotas where quota_id = x_quota_id
641 and (period_id < l_start_period_id OR period_id > l_end_period_id);
642 ELSE
643 delete from cn_period_quotas where quota_id = x_quota_id
644 and period_id < l_start_period_id;
645 END IF;
646
647
648 INSERT_RECORD (x_quota_id);
649 END distribute_target;
650
651 -- Name
652 --
653 -- Purpose
654 -- populate itd values for newly inserted period_quotas
655 --
656 -- Notes
657 -- This method is called whenever a new period quotaa is inserted
658 PROCEDURE populate_itd_values (
659 x_start_period_quota_id NUMBER,
660 x_quota_id NUMBER
661 )
662 IS
663 l_previous_period_id NUMBER := 0;
664 l_end_period_id NUMBER := 0;
665 l_interval_type_id NUMBER := 0;
666 l_start_period_id NUMBER := 0;
667 l_itd_target NUMBER := 0;
668 l_itd_payment NUMBER := 0;
669 l_performance_goal_itd NUMBER := 0;
670 l_org_id NUMBER := 0;
671
672 CURSOR max_prev_period_csr (
673 p_interval_type_id NUMBER,
674 p_start_period_id NUMBER,
675 p_org_id NUMBER
676 )
677 IS
678 SELECT MAX (cal_period_id) max_cal_period_id
679 FROM cn_cal_per_int_types
680 WHERE interval_type_id = p_interval_type_id
681 AND cal_period_id < p_start_period_id
682 AND org_id = p_org_id
683 AND interval_number = (SELECT interval_number
684 FROM cn_cal_per_int_types q
685 WHERE q.cal_period_id = p_start_period_id
686 AND q.interval_type_id = p_interval_type_id
687 AND q.org_id = p_org_id);
688
689 CURSOR max_period_csr (
690 p_interval_type_id NUMBER,
691 p_start_period_id NUMBER,
692 p_org_id NUMBER)
693 IS
694 SELECT cal_period_id
695 FROM cn_cal_per_int_types
696 WHERE interval_type_id = p_interval_type_id
697 AND cal_period_id >= p_start_period_id
698 AND org_id = p_org_id
699 AND interval_number = (SELECT interval_number
700 FROM cn_cal_per_int_types
701 WHERE cal_period_id = p_start_period_id
702 AND interval_type_id = p_interval_type_id
703 AND org_id = p_org_id);
704 BEGIN
705 SELECT period_id
706 INTO l_start_period_id
707 FROM cn_period_quotas
708 WHERE quota_id = x_quota_id
709 AND period_quota_id = x_start_period_quota_id;
710
711 SELECT interval_type_id, org_id
712 INTO l_interval_type_id, l_org_id
713 FROM cn_quotas
714 WHERE quota_id = x_quota_id;
715
716 OPEN max_prev_period_csr (l_interval_type_id, l_start_period_id, l_org_id);
717
718 FETCH max_prev_period_csr
719 INTO l_previous_period_id;
720
721 IF max_prev_period_csr%NOTFOUND
722 THEN
723 RAISE NO_DATA_FOUND;
724 END IF;
725
726 CLOSE max_prev_period_csr;
727
728 IF l_previous_period_id > 0
729 THEN
730 SELECT NVL (pq.itd_target, 0),
731 NVL (pq.itd_payment, 0),
732 NVL (pq.performance_goal_itd, 0)
733 INTO l_itd_target,
734 l_itd_payment,
735 l_performance_goal_itd
736 FROM cn_period_quotas pq
737 WHERE quota_id = x_quota_id AND period_id = l_previous_period_id;
738
739 FOR i_period_id IN max_period_csr (l_interval_type_id, l_start_period_id, l_org_id)
740 LOOP
741 UPDATE cn_period_quotas
742 SET itd_target = l_itd_target,
743 itd_payment = l_itd_payment,
744 performance_goal_itd = l_performance_goal_itd
745 WHERE quota_id = x_quota_id AND period_id = i_period_id.cal_period_id;
746 END LOOP;
747 END IF;
748 EXCEPTION
749 WHEN NO_DATA_FOUND
750 THEN
751 NULL;
752 WHEN OTHERS
753 THEN
754 NULL;
755 END populate_itd_values;
756
757 --clku, helper procedure for synchrozing ITD values of cn_period_quotas table
758 -- called whenever there is change of interval type of PE
759 PROCEDURE sync_itd_values (
760 x_quota_id NUMBER
761 )
762 IS
763 CURSOR period_quotas (
764 l_interval_number NUMBER,
765 l_period_year NUMBER)
766 IS
767 SELECT p.period_quota_id,
768 p.period_target,
769 p.period_payment,
770 p.performance_goal
771 FROM cn_period_quotas p,
772 cn_acc_period_statuses_v cp,
773 cn_cal_per_int_types cpit,
774 cn_quotas cq
775 WHERE p.quota_id = x_quota_id
776 AND p.quota_id = cq.quota_id
777 AND p.period_id = cp.period_id
778 AND cp.period_id = cpit.cal_period_id
779 AND cpit.interval_type_id = cq.interval_type_id
780 AND cpit.interval_number = l_interval_number
781 AND p.period_year = l_period_year
782 AND cq.org_id = p.org_id
783 AND cq.org_id = cp.org_id
784 AND cq.org_id = cpit.org_id
785 ORDER BY p.period_id;
786
787 pq_rec period_quotas%ROWTYPE;
788
789 -- Get the period quotas that belong to the quota assignment for each
790 -- interval
791 CURSOR interval_counts
792 IS
793 SELECT COUNT (p.period_quota_id) interval_count,
794 cpit.interval_number interval_number,
795 p.period_year period_year
796 FROM cn_period_quotas p,
797 cn_acc_period_statuses_v cp,
798 cn_cal_per_int_types cpit,
799 cn_quotas cq
800 WHERE p.quota_id = x_quota_id
801 AND p.quota_id = cq.quota_id
802 AND p.period_id = cp.period_id
803 AND cp.period_id = cpit.cal_period_id
804 AND cpit.interval_type_id = cq.interval_type_id
805 AND cq.org_id = p.org_id
806 AND cq.org_id = cp.org_id
807 AND cq.org_id = cpit.org_id
808 GROUP BY cpit.interval_number, p.period_year;
809
810 interval_rec interval_counts%ROWTYPE;
811 l_target_total NUMBER;
812 l_payment_total NUMBER;
813 l_performance_goal_total NUMBER;
814 BEGIN
815 FOR interval_rec IN interval_counts
816 LOOP
817 -- Initialize for each interval
818 l_target_total := 0;
819 l_payment_total := 0;
820 l_performance_goal_total := 0;
821
822 -- Now that we know the counts per quarter/year we can divide the
823 -- quota target correctly for each quarter and set the period quota
824 -- target.
825 FOR pq_rec IN period_quotas (l_interval_number => interval_rec.interval_number, l_period_year => interval_rec.period_year)
826 LOOP
827 l_target_total := l_target_total + pq_rec.period_target;
828 l_payment_total := l_payment_total + pq_rec.period_payment;
829 l_performance_goal_total := l_performance_goal_total + pq_rec.performance_goal;
830
831 UPDATE cn_period_quotas
832 SET itd_target = NVL (l_target_total, 0),
833 itd_payment = NVL (l_payment_total, 0),
834 performance_goal_itd = NVL (l_performance_goal_total, 0)
835 WHERE period_quota_id = pq_rec.period_quota_id;
836 END LOOP;
837 END LOOP;
838 EXCEPTION
839 WHEN NO_DATA_FOUND
840 THEN
841 NULL;
842 END sync_itd_values;
843 END cn_period_quotas_pkg;