[Home] [Help]
PACKAGE BODY: APPS.CN_MARK_EVENTS_PKG
Source
1 PACKAGE BODY cn_mark_events_pkg AS
2 -- $Header: cneventb.pls 120.7.12010000.4 2008/12/02 08:13:40 venjayar ship $
3
4 -- forward declaration
5 PROCEDURE mark_subsequent_periods(
6 p_salesrep_id IN NUMBER
7 , p_period_id IN NUMBER
8 , p_start_date IN DATE
9 , p_end_date IN DATE
10 , p_quota_id IN NUMBER
11 , p_revert_to_state IN VARCHAR2
12 , p_event_log_id IN NUMBER
13 , p_org_id IN NUMBER
14 );
15
16 --
17 -- Name
18 -- log_event
19 -- Purpose
20 -- This should be the first call in mark_event_*. This procedure will make an
21 -- entry in cn_event_log and return event_log_id which will be used in mark_notify.
22 -- This procedure should be called once for each event.
23 -- History
24 --
25 -- 07/12/98 Richard Jin Created
26 PROCEDURE log_event(
27 p_event_name IN VARCHAR2
28 , p_object_name IN VARCHAR2
29 , p_object_id IN NUMBER
30 , p_start_date IN DATE
31 , p_start_date_old IN DATE
32 , p_end_date IN DATE
33 , p_end_date_old IN DATE
34 , x_event_log_id OUT NOCOPY NUMBER
35 , p_org_id IN NUMBER
36 ) IS
37 BEGIN
38 INSERT INTO cn_event_log_all(
39 event_log_id
40 , event_name
41 , object_name
42 , object_id
43 , start_date
44 , start_date_old
45 , end_date
46 , end_date_old
47 , user_id
48 , event_log_date
49 , status
50 , creation_date
51 , created_by
52 , last_update_date
53 , last_update_login
54 , last_updated_by
55 , org_id
56 )
57 VALUES (
58 cn_event_log_s.NEXTVAL
59 , p_event_name
60 , p_object_name
61 , p_object_id
62 , p_start_date
63 , p_start_date_old
64 , p_end_date
65 , p_end_date_old
66 , fnd_global.user_id
67 , SYSDATE
68 , NULL
69 , SYSDATE
70 , fnd_global.user_id
71 , SYSDATE
72 , fnd_global.login_id
73 , fnd_global.user_id
74 , p_org_id
75 )
76 RETURNING event_log_id INTO x_event_log_id;
77 EXCEPTION
78 WHEN OTHERS THEN
79 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
80 fnd_log.STRING(fnd_log.level_unexpected, 'cn.plsql.cn_mark_events_pkg.log_event.exception'
81 , SQLERRM);
82 END IF;
83
84 RAISE;
85 END log_event;
86
87 --
88 -- Name
89 -- mark_notify_real
90 -- Purpose
91 --
92 -- History
93 --
94 -- 07/12/98 Richard Jin Created
95 PROCEDURE mark_notify_real(
96 p_salesrep_id IN NUMBER
97 , p_period_id IN NUMBER
98 , p_start_date IN DATE
99 , p_end_date IN DATE
100 , p_quota_id IN NUMBER
101 , p_revert_to_state IN VARCHAR2
102 , p_event_log_id IN NUMBER
103 , p_mode IN VARCHAR2
104 , p_org_id IN NUMBER
105 ) IS
106 -- for marking CALC event
107 CURSOR l_chk_calc_lower_events_csr IS
108 SELECT 1
109 FROM cn_notify_log_all
110 WHERE period_id = p_period_id
111 AND org_id = p_org_id
112 AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
113 AND status = 'INCOMPLETE'
114 AND (revert_state IN('COL', 'CLS', 'ROLL') OR(revert_state = 'CALC' AND quota_id IS NULL));
115
116 -- clku, bug 2769655, do not check for 'COL' anymore, need to mark event
117 -- even if there are -1000 'COL' records in notify log
118 CURSOR l_chk_lower_events_csr(l_revert_state VARCHAR2, l_start_date DATE, l_end_date DATE) IS
119 SELECT 1
120 FROM cn_notify_log_all
121 WHERE period_id = p_period_id
122 AND org_id = p_org_id
123 AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
124 AND status = 'INCOMPLETE'
125 AND (
126 (l_revert_state = 'POP' AND revert_state IN('CLS', 'ROLL'))
127 OR (l_revert_state = 'ROLL' AND revert_state IN('CLS'))
128 )
129 AND l_start_date BETWEEN start_date AND end_date
130 AND l_end_date BETWEEN start_date AND end_date;
131
132 -- if there is already a 'CALC' or 'POP' entry for the same quota_id don't mark.
133 CURSOR l_check_calc_quota_entry_csr IS
134 SELECT 1
135 FROM cn_notify_log_all
136 WHERE salesrep_id = p_salesrep_id
137 AND org_id = p_org_id
138 AND period_id = p_period_id
139 AND revert_state IN('CALC', 'POP')
140 AND status = 'INCOMPLETE'
141 AND quota_id = p_quota_id;
142
143 -- for marking POP event
144 CURSOR l_pop_quota_entry_csr IS
145 SELECT notify_log_id
146 , start_date
147 , end_date
148 FROM cn_notify_log_all
149 WHERE salesrep_id = p_salesrep_id
150 AND org_id = p_org_id
151 AND period_id = p_period_id
152 AND revert_state = p_revert_to_state
153 AND status = 'INCOMPLETE'
154 AND quota_id = p_quota_id;
155
156 CURSOR l_roll_entry_csr IS
157 SELECT notify_log_id
158 , start_date
159 , end_date
160 FROM cn_notify_log_all
161 WHERE (salesrep_id = -1000 OR salesrep_id = p_salesrep_id)
162 AND org_id = p_org_id
163 AND period_id = p_period_id
164 AND revert_state = p_revert_to_state
165 AND status = 'INCOMPLETE';
166
167 CURSOR l_roll_all_entry_csr IS
168 SELECT notify_log_id
169 , start_date
170 , end_date
171 FROM cn_notify_log_all
172 WHERE salesrep_id = -1000
173 AND org_id = p_org_id
174 AND period_id = p_period_id
175 AND revert_state = p_revert_to_state
176 AND status = 'INCOMPLETE';
177
178 CURSOR l_cls_all_entry_csr IS
179 SELECT notify_log_id
180 , start_date
181 , end_date
182 FROM cn_notify_log_all
183 WHERE salesrep_id = -1000
184 AND org_id = p_org_id
185 AND period_id = p_period_id
186 AND revert_state = p_revert_to_state
187 AND status = 'INCOMPLETE';
188
189 CURSOR l_col_all_entry_csr IS
190 SELECT notify_log_id
191 , start_date
192 , end_date
193 FROM cn_notify_log_all
194 WHERE salesrep_id = -1000
195 AND org_id = p_org_id
196 AND period_id = p_period_id
197 AND revert_state = p_revert_to_state
198 AND status = 'INCOMPLETE';
199
200 CURSOR l_get_period_dates_csr IS
201 SELECT start_date
202 , end_date
203 FROM cn_period_statuses_all
204 WHERE period_id = p_period_id AND org_id = p_org_id;
205
206 l_counter NUMBER;
207 l_start_date DATE;
208 l_end_date DATE;
209 l_notify_log_id NUMBER(15);
210 l_insert_flag BOOLEAN;
211 l_update_flag BOOLEAN;
212 BEGIN
213 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
214 fnd_log.STRING(
215 fnd_log.level_procedure
216 , 'cn.plsql.cn_mark_events_pkg.mark_notify_real.begin'
217 , 'Beginning of mark_notify_real ...'
218 );
219 END IF;
220
221 l_insert_flag := FALSE;
222 l_update_flag := FALSE;
223
224 IF p_revert_to_state = 'CALC' THEN
225 -- scannane, bug 7154503, Notify log table update
226 l_insert_flag := TRUE;
227
228 OPEN l_chk_calc_lower_events_csr;
229 FETCH l_chk_calc_lower_events_csr INTO l_counter;
230
231 -- if no lower event in 'CLS', 'ROLL',
232 -- or 'CALC' with null quota_id is found, try to mark it
233 IF l_chk_calc_lower_events_csr%NOTFOUND THEN
234 CLOSE l_chk_calc_lower_events_csr;
235
236 -- get start_date, end_date
237 OPEN l_get_period_dates_csr;
238 FETCH l_get_period_dates_csr INTO l_start_date, l_end_date;
239 CLOSE l_get_period_dates_csr;
240
241 IF p_quota_id IS NULL THEN
242 l_insert_flag := TRUE;
243 ELSIF p_quota_id IS NOT NULL THEN
244 -- if there is already a 'CALC' or 'POP' entry for the same quota_id don't mark.
245 -- because a 'POP' will ensure the quota be recalculated regardless of date range
246 -- on that 'POP' entry
247 OPEN l_check_calc_quota_entry_csr;
248 FETCH l_check_calc_quota_entry_csr INTO l_counter;
249
250 -- if 'CALC' with quota_id is not found, then mark it
251 IF l_check_calc_quota_entry_csr%NOTFOUND THEN
252 l_insert_flag := TRUE;
253 END IF;
254
255 CLOSE l_check_calc_quota_entry_csr;
256 END IF;
257 ELSE
258 CLOSE l_chk_calc_lower_events_csr;
259 END IF;
260 ELSIF p_revert_to_state = 'POP' THEN
261 OPEN l_chk_lower_events_csr(p_revert_to_state, p_start_date, p_end_date);
262 FETCH l_chk_lower_events_csr INTO l_counter;
263
264 -- if no lower event 'COL', 'CLS', 'ROLL' with larger or equal date range is found, try to mark it
265 IF l_chk_lower_events_csr%NOTFOUND THEN
266 OPEN l_pop_quota_entry_csr;
267 FETCH l_pop_quota_entry_csr INTO l_notify_log_id, l_start_date, l_end_date;
268
269 IF l_pop_quota_entry_csr%FOUND THEN
270 -- if an entry with quota_id exists, then only do update if needed
271 l_update_flag := TRUE;
272 ELSE -- if no pop_quota entry is found
273 l_insert_flag := TRUE;
274 END IF;
275
276 CLOSE l_pop_quota_entry_csr;
277 END IF;
278
279 CLOSE l_chk_lower_events_csr;
280 ELSIF p_revert_to_state = 'ROLL' THEN
281 OPEN l_chk_lower_events_csr(p_revert_to_state, p_start_date, p_end_date);
282 FETCH l_chk_lower_events_csr INTO l_counter;
283
284 -- if no lower event 'COL' 'CLS' with larger or equal date range is found, try to mark it
285 IF l_chk_lower_events_csr%NOTFOUND THEN
286 cn_message_pkg.DEBUG(' no lower event try to mark it ');
287
288 IF p_salesrep_id = -1000 THEN
289 OPEN l_roll_all_entry_csr;
290 FETCH l_roll_all_entry_csr INTO l_notify_log_id, l_start_date, l_end_date;
291
292 IF l_roll_all_entry_csr%FOUND THEN
293 l_update_flag := TRUE;
294 ELSE
295 l_insert_flag := TRUE;
296 END IF;
297
298 CLOSE l_roll_all_entry_csr;
299 ELSE -- p_salesrep_id is not -1000
300 OPEN l_roll_entry_csr;
301 FETCH l_roll_entry_csr INTO l_notify_log_id, l_start_date, l_end_date;
302
303 IF l_roll_entry_csr%FOUND THEN
304 l_update_flag := TRUE;
305 ELSE
306 l_insert_flag := TRUE;
307 END IF;
308
309 CLOSE l_roll_entry_csr;
310 END IF;
311 END IF;
312
313 CLOSE l_chk_lower_events_csr;
314 ELSIF p_revert_to_state = 'CLS' THEN
315 OPEN l_chk_lower_events_csr(p_revert_to_state, p_start_date, p_end_date);
316 FETCH l_chk_lower_events_csr INTO l_counter;
317
318 -- if no lower event 'COL' with larger or equal date range is found, try to mark it
319 IF l_chk_lower_events_csr%NOTFOUND THEN
320 OPEN l_cls_all_entry_csr;
321 FETCH l_cls_all_entry_csr INTO l_notify_log_id, l_start_date, l_end_date;
322
323 IF l_cls_all_entry_csr%FOUND THEN
324 l_update_flag := TRUE;
325 ELSE
326 l_insert_flag := TRUE;
327 END IF;
328
329 CLOSE l_cls_all_entry_csr;
330 END IF;
331
332 CLOSE l_chk_lower_events_csr;
333 ELSIF p_revert_to_state = 'COL' THEN
334 OPEN l_col_all_entry_csr;
335 FETCH l_col_all_entry_csr INTO l_notify_log_id, l_start_date, l_end_date;
336
337 IF l_col_all_entry_csr%FOUND THEN
338 l_update_flag := TRUE;
339 ELSE
340 l_insert_flag := TRUE;
341 END IF;
342
343 CLOSE l_col_all_entry_csr;
344 END IF;
345
346 IF l_insert_flag THEN
347 INSERT INTO cn_notify_log_all
348 (
349 notify_log_id
350 , salesrep_id
351 , period_id
352 , start_date
353 , end_date
354 , quota_id
355 , revert_state
356 , event_log_id
357 , notify_log_date
358 , status
359 , revert_sequence
360 , creation_date
361 , created_by
362 , last_update_date
363 , last_update_login
364 , last_updated_by
365 , org_id
366 )
367 VALUES (
368 cn_notify_log_s.NEXTVAL
369 , p_salesrep_id
370 , p_period_id
371 , NVL(p_start_date, l_start_date)
372 , NVL(p_end_date, l_end_date)
373 , p_quota_id
374 , p_revert_to_state
375 , p_event_log_id
376 , SYSDATE
377 , 'INCOMPLETE'
378 , DECODE(p_revert_to_state, 'COL', 4, 'CLS', 6, 'ROLL', 8, 'POP', 10, 'CALC', 12)
379 , SYSDATE
380 , fnd_global.user_id
381 , SYSDATE
382 , fnd_global.login_id
383 , fnd_global.user_id
384 , p_org_id
388 IF l_update_flag THEN
385 );
386 END IF;
387
389 IF l_start_date > p_start_date THEN
390 IF l_end_date < p_end_date THEN
391 UPDATE cn_notify_log_all
392 SET start_date = p_start_date
393 , end_date = p_end_date
394 WHERE notify_log_id = l_notify_log_id;
395 ELSE
396 UPDATE cn_notify_log
397 SET start_date = p_start_date
398 WHERE notify_log_id = l_notify_log_id;
399 END IF;
400 ELSIF l_end_date < p_end_date THEN
401 UPDATE cn_notify_log
402 SET end_date = p_end_date
403 WHERE notify_log_id = l_notify_log_id;
404 END IF;
405 END IF;
406
407 IF l_update_flag OR l_insert_flag THEN
408 -- delete higher event entries with date range within date range for the current event
409 IF p_revert_to_state = 'COL' OR p_revert_to_state = 'CLS' OR p_revert_to_state = 'ROLL' THEN
410 IF p_salesrep_id = -1000 THEN
411 DELETE cn_notify_log_all
412 WHERE period_id = p_period_id
413 AND org_id = p_org_id
414 AND status = 'INCOMPLETE'
415 AND (
416 (
417 p_revert_to_state = 'ROLL'
418 AND revert_state = 'POP'
419 AND start_date BETWEEN p_start_date AND p_end_date
420 AND end_date BETWEEN p_start_date AND p_end_date
421 )
422 OR (
423 p_revert_to_state = 'CLS'
424 AND revert_state IN('POP', 'ROLL')
425 AND start_date BETWEEN p_start_date AND p_end_date
426 AND end_date BETWEEN p_start_date AND p_end_date
427 )
428 OR (
429 p_revert_to_state = 'COL'
430 AND revert_state IN('POP', 'ROLL', 'CLS')
431 AND start_date BETWEEN p_start_date AND p_end_date
432 AND end_date BETWEEN p_start_date AND p_end_date
433 )
434 OR revert_state = 'CALC'
435 )
436 AND action IS NULL
437 AND action_link_id IS NULL;
438 ELSE
439 -- only 'ROLL', p_slearep_id <> -1000 comes here
440 DELETE cn_notify_log_all
441 WHERE period_id = p_period_id
442 AND org_id = p_org_id
443 AND salesrep_id = p_salesrep_id
444 AND status = 'INCOMPLETE'
445 AND (
446 (
447 revert_state = 'POP'
448 AND start_date BETWEEN p_start_date AND p_end_date
449 AND end_date BETWEEN p_start_date AND p_end_date
450 )
451 OR revert_state = 'CALC'
452 )
453 AND action IS NULL
454 AND action_link_id IS NULL;
455 END IF;
456 ELSIF p_revert_to_state = 'CALC' AND p_quota_id IS NULL THEN
457 IF p_salesrep_id = -1000 THEN
458 -- delete 'CALC' with null quota for particular salesreps
459 -- and 'CALC' with quota_id
460 DELETE cn_notify_log_all
461 WHERE period_id = p_period_id
462 AND org_id = p_org_id
463 AND salesrep_id <> -1000
464 AND revert_state = p_revert_to_state
465 AND status = 'INCOMPLETE'
466 AND action IS NULL
467 AND action_link_id IS NULL;
468 ELSE
469 -- delete 'CALC' with quota_id
470 NULL;
471 END IF;
472 ELSIF p_revert_to_state = 'POP' THEN
473 -- delete 'CALC' entries with the same quota_id
474 -- since a 'POP' entry ensures that the salesrep be picked up.
475 DELETE cn_notify_log_all
476 WHERE period_id = p_period_id
477 AND org_id = p_org_id
478 AND salesrep_id = p_salesrep_id
479 AND revert_state = 'CALC'
480 AND quota_id = p_quota_id
481 AND status = 'INCOMPLETE'
482 AND action IS NULL
483 AND action_link_id IS NULL;
484 END IF;
485
486 IF p_mode = 'SUBSEQUENT' THEN
487 mark_subsequent_periods(
488 p_salesrep_id
489 , p_period_id
490 , p_start_date
491 , p_end_date
492 , p_quota_id
493 , p_revert_to_state
494 , p_event_log_id
495 , p_org_id
496 );
497 END IF;
498 END IF;
499
500 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
501 fnd_log.STRING(
502 fnd_log.level_procedure
503 , 'cn.plsql.cn_mark_events_pkg.mark_notify_real.end'
504 , 'End of mark_notify_real.'
505 );
506 END IF;
507 EXCEPTION
508 WHEN OTHERS THEN
509 IF l_chk_lower_events_csr%ISOPEN THEN
510 CLOSE l_chk_lower_events_csr;
514 CLOSE l_chk_calc_lower_events_csr;
511 END IF;
512
513 IF l_chk_calc_lower_events_csr%ISOPEN THEN
515 END IF;
516
517 IF l_check_calc_quota_entry_csr%ISOPEN THEN
518 CLOSE l_check_calc_quota_entry_csr;
519 END IF;
520
521 IF l_pop_quota_entry_csr%ISOPEN THEN
522 CLOSE l_pop_quota_entry_csr;
523 END IF;
524
525 IF l_roll_entry_csr%ISOPEN THEN
526 CLOSE l_roll_entry_csr;
527 END IF;
528
529 IF l_roll_all_entry_csr%ISOPEN THEN
530 CLOSE l_roll_all_entry_csr;
531 END IF;
532
533 IF l_cls_all_entry_csr%ISOPEN THEN
534 CLOSE l_cls_all_entry_csr;
535 END IF;
536
537 IF l_col_all_entry_csr%ISOPEN THEN
538 CLOSE l_col_all_entry_csr;
539 END IF;
540
541 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
542 fnd_log.STRING(
543 fnd_log.level_unexpected
544 , 'cn.plsql.cn_mark_events_pkg.mark_notify_real.exception'
545 , SQLERRM
546 );
547 END IF;
548
549 RAISE;
550 END mark_notify_real;
551
552 --
553 -- Name
554 -- mark_subsequent_periods
555 -- Purpose
556 --
557 -- History
558 --
559 -- 07/12/98 Richard Jin Created
560 PROCEDURE mark_subsequent_periods(
561 p_salesrep_id IN NUMBER
562 , --required
563 p_period_id IN NUMBER
564 , --required
565 p_start_date IN DATE
566 , --optional
567 p_end_date IN DATE
568 , --optional
569 p_quota_id IN NUMBER
570 , --optional
571 p_revert_to_state IN VARCHAR2
572 , --required
573 p_event_log_id IN NUMBER
574 , p_org_id IN NUMBER
575 ) IS --required
576 CURSOR l_all_srp_periods_csr IS
577 -- 1). for salesrep_id = -1000 /period_id
578 SELECT DISTINCT cpit2.cal_period_id
579 FROM cn_cal_per_int_types_all cpit1, cn_cal_per_int_types_all cpit2
580 WHERE cpit1.cal_period_id = p_period_id
581 AND cpit1.org_id = p_org_id
582 AND cpit2.interval_type_id = cpit1.interval_type_id
583 AND cpit2.interval_number = cpit1.interval_number
584 AND cpit2.org_id = p_org_id
585 AND cpit2.cal_period_id > p_period_id;
586
587 CURSOR l_single_srp_periods_csr IS
588 -- 2). for salesrep_id/ period_id
589 SELECT DISTINCT cpit2.cal_period_id
590 FROM cn_cal_per_int_types_all cpit2
591 WHERE (cpit2.interval_type_id, cpit2.interval_number) IN(
592 SELECT interval_type_id
593 , interval_number
594 FROM cn_cal_per_int_types_all
595 WHERE cal_period_id = p_period_id
596 AND interval_type_id IN(
597 SELECT DISTINCT q.interval_type_id
598 FROM cn_quotas_all q
599 WHERE q.quota_id IN(
600 SELECT quota_id
601 FROM cn_srp_quota_assigns_all
602 WHERE srp_plan_assign_id IN(
603 SELECT srp_plan_assign_id
607 AND (
604 FROM cn_srp_plan_assigns_all
605 WHERE salesrep_id = p_salesrep_id
606 AND org_id = p_org_id))
608 q.incremental_type = 'N'
609 OR (
610 q.incremental_type = 'Y'
611 AND EXISTS(
612 SELECT 1
613 FROM cn_calc_formulas_all
614 WHERE calc_formula_id =
615 q.calc_formula_id
616 AND org_id = p_org_id
617 AND trx_group_code = 'GROUP')
618 )
619 ))
620 AND org_id = p_org_id)
621 AND cpit2.cal_period_id > p_period_id
622 AND cpit2.org_id = p_org_id
623 AND EXISTS(
624 SELECT 1
625 FROM cn_srp_intel_periods_all
626 WHERE salesrep_id = p_salesrep_id
627 AND period_id = cpit2.cal_period_id
628 AND org_id = p_org_id
629 AND processing_status_code <> 'CLEAN');
630
631 CURSOR l_single_srp_pe_periods_csr IS
632 -- 3). for salesrep_id/ period_id /quota_id
633 SELECT cpit2.cal_period_id
634 FROM cn_cal_per_int_types_all cpit2
635 WHERE (cpit2.interval_type_id, cpit2.interval_number, cpit2.org_id) =
636 (SELECT cpit1.interval_type_id
637 , cpit1.interval_number
638 , cpit1.org_id
639 FROM cn_cal_per_int_types_all cpit1
640 WHERE cpit1.cal_period_id = p_period_id
641 AND cpit1.org_id = p_org_id
642 AND cpit1.interval_type_id =
643 (SELECT interval_type_id
644 FROM cn_quotas_all pe
645 WHERE pe.quota_id = p_quota_id
646 AND (
647 pe.incremental_type = 'N'
648 OR (
649 pe.incremental_type = 'Y'
650 AND EXISTS(
651 SELECT 1
652 FROM cn_calc_formulas_all fm
653 WHERE fm.calc_formula_id = pe.calc_formula_id
654 AND fm.org_id = pe.org_id
655 AND fm.trx_group_code = 'GROUP')
656 )
657 )))
658 AND cpit2.cal_period_id > p_period_id
659 AND EXISTS(
660 SELECT 1
661 FROM cn_srp_intel_periods_all intel
662 WHERE intel.salesrep_id = p_salesrep_id
663 AND intel.org_id = p_org_id
664 AND intel.period_id = cpit2.cal_period_id
665 AND intel.processing_status_code <> 'CLEAN');
666
667 l_revert_to_state VARCHAR2(30);
668 BEGIN
669 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
670 fnd_log.STRING(
671 fnd_log.level_procedure
672 , 'cn.plsql.cn_mark_events_pkg.mark_subsequent_periods.begin'
673 , 'Beginning of mark_subsequent_periods ...'
674 );
675 END IF;
676
677 -- subsequent period will always be marked as 'CALC'
678 l_revert_to_state := 'CALC';
679
680 IF p_salesrep_id = -1000 THEN
681 FOR l_period IN l_all_srp_periods_csr LOOP
682 mark_notify_real(
683 p_salesrep_id => p_salesrep_id
684 , p_period_id => l_period.cal_period_id
685 , p_start_date => NULL
686 , p_end_date => NULL
687 , p_quota_id => p_quota_id
688 , p_revert_to_state => l_revert_to_state
689 , p_event_log_id => p_event_log_id
690 , p_mode => 'NEW'
691 , -- p_mode
692 p_org_id => p_org_id
693 );
694 END LOOP;
695 ELSE
696 IF p_quota_id IS NULL THEN
697 FOR l_period IN l_single_srp_periods_csr LOOP
698 mark_notify_real(
699 p_salesrep_id => p_salesrep_id
700 , p_period_id => l_period.cal_period_id
701 , p_start_date => NULL
702 , p_end_date => NULL
703 , p_quota_id => p_quota_id
704 , p_revert_to_state => l_revert_to_state
705 , p_event_log_id => p_event_log_id
706 , p_mode => 'NEW'
707 , -- p_mode
711 ELSE
708 p_org_id => p_org_id
709 );
710 END LOOP;
712 FOR l_period IN l_single_srp_pe_periods_csr LOOP
713 mark_notify_real(
714 p_salesrep_id => p_salesrep_id
715 , p_period_id => l_period.cal_period_id
716 , p_start_date => NULL
717 , p_end_date => NULL
718 , p_quota_id => p_quota_id
719 , p_revert_to_state => l_revert_to_state
720 , p_event_log_id => p_event_log_id
721 , p_mode => 'NEW'
722 , -- p_mode
723 p_org_id => p_org_id
724 );
725 END LOOP;
726 END IF;
727 END IF;
728
729 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
730 fnd_log.STRING(
731 fnd_log.level_procedure
732 , 'cn.plsql.cn_mark_events_pkg.mark_subsequent_periods.end'
733 , 'End of mark_subsequent_periods.'
734 );
735 END IF;
736 EXCEPTION
737 WHEN OTHERS THEN
738 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
739 fnd_log.STRING(
740 fnd_log.level_unexpected
741 , 'cn.plsql.cn_mark_events_pkg.mark_subsequent_periods.exception'
742 , SQLERRM
743 );
744 END IF;
745
746 RAISE;
747 END mark_subsequent_periods;
748
749 --
750 -- Name
751 -- mark_notify
752 -- Purpose
753 --
754 -- History
755 --
756 -- 07/12/98 Richard Jin Created
757 PROCEDURE mark_notify(
758 p_salesrep_id IN NUMBER
759 , --required
760 p_period_id IN NUMBER
761 , --required
762 p_start_date IN DATE
763 , --optional
764 p_end_date IN DATE
765 , --optional
766 p_quota_id IN NUMBER
767 , --optional
768 p_revert_to_state IN VARCHAR2
769 , --required
770 p_event_log_id IN NUMBER
771 , p_org_id IN NUMBER
772 ) IS --required
773 BEGIN
774 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
775 RETURN;
776 END IF;
777
778 mark_notify_real(
779 p_salesrep_id
780 , p_period_id
781 , p_start_date
782 , p_end_date
783 , p_quota_id
784 , p_revert_to_state
785 , p_event_log_id
786 , 'SUBSEQUENT'
787 , -- p_mode
788 p_org_id
789 );
790 END mark_notify;
791
792 --
793 -- Name
794 -- mark_notify
795 -- Purpose
796 --
797 -- History
798 --
799 -- 07/12/98 Richard Jin Created
800 PROCEDURE mark_notify(
801 p_salesrep_id IN NUMBER
802 , --required
803 p_period_id IN NUMBER
804 , --required
805 p_start_date IN DATE
806 , --optional
807 p_end_date IN DATE
808 , --optional
809 p_quota_id IN NUMBER
810 , --optional
811 p_revert_to_state IN VARCHAR2
812 , --required
813 p_event_log_id IN NUMBER
814 , --required
815 p_mode IN VARCHAR2
816 , p_org_id IN NUMBER
817 ) IS
818 BEGIN
819 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
820 RETURN;
821 END IF;
822
823 mark_notify_real(
824 p_salesrep_id
825 , p_period_id
826 , p_start_date
827 , p_end_date
828 , p_quota_id
829 , p_revert_to_state
830 , p_event_log_id
831 , p_mode
832 , p_org_id
833 );
834 END mark_notify;
835
836 --
837 -- Name
838 -- log_event
839 -- Purpose
840 -- This should be the first call in mark_event_*. This procedure will make an
841 -- entry in cn_event_log and return event_log_id which will be used in mark_notify.
842 -- This procedure should be called once for each event.
843 -- History
844 --
845 -- 07/12/98 Richard Jin Created
846 PROCEDURE mark_notify_salesreps(
847 p_salesrep_id IN NUMBER
848 , p_period_id IN NUMBER
849 , p_start_date IN DATE
850 , p_end_date IN DATE
851 , p_revert_to_state IN VARCHAR2
852 , p_event_log_id IN NUMBER
853 , p_comp_group_id IN NUMBER
854 , p_action IN VARCHAR2
855 , p_action_link_id IN NUMBER
856 , p_base_salesrep_id IN NUMBER
857 , p_base_comp_group_id IN NUMBER
858 , p_role_id IN NUMBER
859 , x_action_link_id OUT NOCOPY NUMBER
860 , p_org_id IN NUMBER
861 ) IS
862 l_notify_log_id NUMBER;
863 l_counter NUMBER;
864 l_counter2 NUMBER;
865 l_insert_flag BOOLEAN := FALSE;
866
867 -- if no action/action_link_id is provided
868 -- check lower event or 'CALC' with no quota_id
869 CURSOR l_chk_calc_lower_events_csr IS
870 SELECT 1
874 AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
871 FROM cn_notify_log_all
872 WHERE period_id = p_period_id
873 AND org_id = p_org_id
875 AND status = 'INCOMPLETE'
876 AND (revert_state IN('COL', 'CLS', 'ROLL') OR(revert_state = 'CALC' AND quota_id IS NULL));
877
878 CURSOR l_existence_check_csr IS
879 SELECT 1
880 FROM cn_notify_log_all
881 WHERE period_id = p_period_id
882 AND org_id = p_org_id
883 AND salesrep_id = p_salesrep_id
884 AND status = 'INCOMPLETE'
885 AND start_date = p_start_date
886 AND end_date = p_end_date
887 AND revert_state = p_revert_to_state
888 AND (p_comp_group_id IS NULL OR comp_group_id = p_comp_group_id)
889 AND NVL(action, 'DEFAULT') = NVL(p_action, 'DEFAULT')
890 AND NVL(action_link_id, -999999) = NVL(p_action_link_id, -999999)
891 AND NVL(base_salesrep_id, -999999) = NVL(p_base_salesrep_id, -999999)
892 AND NVL(base_comp_group_id, -999999) = NVL(p_base_comp_group_id, -999999)
893 AND NVL(role_id, -999999) = NVL(p_role_id, -999999);
894 BEGIN
895 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
896 fnd_log.STRING(
897 fnd_log.level_procedure
898 , 'cn.plsql.cn_mark_events_pkg.mark_notify_salesreps.begin'
899 , 'Beginning of mark_notify_salesreps...'
900 );
901 END IF;
902
903 IF p_revert_to_state = 'NCALC' THEN
904 -- no need to do any comparison, just insert
905 l_insert_flag := TRUE;
906 ELSE
907 IF p_action IS NOT NULL OR p_action_link_id IS NOT NULL THEN
908 -- in this case, start_date, end_date is useful info.
909 -- check the existence of identical incomplete entry
910 OPEN l_existence_check_csr;
911 FETCH l_existence_check_csr INTO l_counter;
912
913 IF l_existence_check_csr%NOTFOUND THEN
914 l_insert_flag := TRUE;
915 END IF;
916
917 CLOSE l_existence_check_csr;
918 ELSE
919 -- no action, no action_link_id
920 -- if there is already a lower event entry
921 -- or a 'CALC' with null quota_id entry, then do nothing
922 -- else do the following
923 OPEN l_chk_calc_lower_events_csr;
924 FETCH l_chk_calc_lower_events_csr INTO l_counter;
925
926 OPEN l_existence_check_csr;
927 FETCH l_existence_check_csr INTO l_counter2;
928
929 IF l_chk_calc_lower_events_csr%NOTFOUND THEN
930 IF l_existence_check_csr%NOTFOUND THEN
931 -- clku change team
932 l_insert_flag := TRUE;
933 END IF;
934 END IF;
935
936 CLOSE l_chk_calc_lower_events_csr;
937
938 CLOSE l_existence_check_csr;
939 END IF;
940 END IF;
941
942 IF l_insert_flag THEN
943 SELECT cn_notify_log_s.NEXTVAL
944 INTO l_notify_log_id
945 FROM DUAL;
946
947 -- insert into cn_notify_log
948 INSERT INTO cn_notify_log_all
949 (
950 notify_log_id
951 , salesrep_id
952 , period_id
953 , start_date
954 , end_date
955 , revert_state
956 , event_log_id
957 , comp_group_id
958 , action
959 , action_link_id
960 , base_salesrep_id
961 , base_comp_group_id
962 , role_id
963 , notify_log_date
964 , status
965 , revert_sequence
966 , creation_date
967 , created_by
968 , last_update_date
969 , last_update_login
970 , last_updated_by
971 , org_id
972 )
973 VALUES (
974 l_notify_log_id
975 , p_salesrep_id
976 , p_period_id
977 , p_start_date
978 , p_end_date
979 , p_revert_to_state
980 , p_event_log_id
981 , p_comp_group_id
982 , p_action
983 , p_action_link_id
984 , p_base_salesrep_id
985 , p_base_comp_group_id
986 , p_role_id
987 , SYSDATE
988 , 'INCOMPLETE'
989 , DECODE(p_revert_to_state, 'COL', 4, 'CLS', 6, 'ROLL', 8, 'POP', 10, 'CALC', 12)
990 , SYSDATE
991 , fnd_global.user_id
992 , SYSDATE
993 , fnd_global.login_id
994 , fnd_global.user_id
995 , p_org_id
996 );
997
998 x_action_link_id := l_notify_log_id;
999
1000 -- need to mark subsequent periods when not 'NCALC'
1001 IF p_revert_to_state <> 'NCALC' THEN
1002 -- delete all 'CALC' entry with not null quota_id
1003 DELETE cn_notify_log_all
1004 WHERE salesrep_id = p_salesrep_id
1005 AND org_id = p_org_id
1006 AND period_id = p_period_id
1010
1007 AND revert_state = p_revert_to_state
1008 AND status = 'INCOMPLETE'
1009 AND quota_id IS NOT NULL;
1011 mark_subsequent_periods(
1012 p_salesrep_id
1013 , p_period_id
1014 , p_start_date
1015 , p_end_date
1016 , NULL
1017 , p_revert_to_state
1018 , p_event_log_id
1019 , p_org_id
1020 );
1021 END IF;
1022 END IF;
1023
1024 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1025 fnd_log.STRING(
1026 fnd_log.level_procedure
1027 , 'cn.plsql.cn_mark_events_pkg.mark_notify_salesreps.end'
1028 , 'End of mark_notify_salesreps.'
1029 );
1030 END IF;
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1034 fnd_log.STRING(
1035 fnd_log.level_unexpected
1036 , 'cn.plsql.cn_mark_events_pkg.mark_notify_salesreps.exception'
1037 , SQLERRM
1038 );
1039 END IF;
1040
1041 RAISE;
1042 END mark_notify_salesreps;
1043
1044 PROCEDURE mark_notify_dates(
1045 p_start_date DATE
1046 , p_end_date DATE
1047 , p_revert_to_state VARCHAR2
1048 , p_event_log_id NUMBER
1049 , p_org_id IN NUMBER
1050 ) IS
1051 l_start_period_id NUMBER(15);
1052 l_end_period_id NUMBER(15);
1053
1054 CURSOR l_date_periods_csr IS
1055 SELECT acc.period_id
1056 , DECODE(acc.period_id, l_start_period_id, p_start_date, acc.start_date) start_date
1057 , DECODE(acc.period_id, l_end_period_id, NVL(p_end_date, acc.end_date), acc.end_date)
1058 end_date
1059 FROM cn_acc_period_statuses_v acc
1060 WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
1061 AND acc.period_status = 'O'
1062 AND acc.org_id = p_org_id
1063 ORDER BY acc.period_id DESC;
1064 BEGIN
1065 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1066 fnd_log.STRING(
1067 fnd_log.level_procedure
1068 , 'cn.plsql.cn_mark_events_pkg.mark_notify_dates.begin'
1069 , 'Beginning of mark_notify_dates ...'
1070 );
1071 END IF;
1072
1073 l_start_period_id := cn_api.get_acc_period_id(p_start_date, p_org_id);
1074 l_end_period_id := cn_api.get_acc_period_id(p_end_date, p_org_id);
1075
1076 FOR l_per IN l_date_periods_csr LOOP
1077 IF l_date_periods_csr%ROWCOUNT = 1 THEN
1078 -- it's the last period, need to mark subsequent periods
1079 mark_notify(
1080 p_salesrep_id => -1000
1081 , p_period_id => l_per.period_id
1082 , p_start_date => l_per.start_date
1083 , p_end_date => l_per.end_date
1084 , p_quota_id => NULL
1085 , p_revert_to_state => p_revert_to_state
1086 , p_event_log_id => p_event_log_id
1087 , p_mode => 'SUBSEQUENT'
1088 , p_org_id => p_org_id
1089 );
1090 ELSE
1091 mark_notify(
1092 p_salesrep_id => -1000
1093 , p_period_id => l_per.period_id
1094 , p_start_date => l_per.start_date
1095 , p_end_date => l_per.end_date
1096 , p_quota_id => NULL
1097 , p_revert_to_state => p_revert_to_state
1098 , p_event_log_id => p_event_log_id
1099 , p_mode => 'NEW'
1100 , p_org_id => p_org_id
1101 );
1102 END IF;
1103 END LOOP;
1104
1105 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1106 fnd_log.STRING(
1107 fnd_log.level_procedure
1108 , 'cn.plsql.cn_mark_events_pkg.mark_notify_dates.end'
1109 , 'End of mark_notify_dates.'
1110 );
1111 END IF;
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1115 fnd_log.STRING(
1116 fnd_log.level_unexpected
1117 , 'cn.plsql.cn_mark_events_pkg.mark_notify_dates.exception'
1118 , SQLERRM
1119 );
1120 END IF;
1121
1122 RAISE;
1123 END mark_notify_dates;
1124
1125 -- overloaded for changes in cn_repositories;
1126 FUNCTION get_period(p_date DATE, p_period_set_id NUMBER, p_period_type_id NUMBER, p_org_id NUMBER)
1127 RETURN NUMBER IS
1128 CURSOR l_date_period_csr IS
1129 SELECT period_id
1130 FROM cn_period_statuses_all
1131 WHERE period_set_id = p_period_set_id
1132 AND period_type_id = p_period_type_id
1133 AND p_date BETWEEN start_date AND end_date
1134 AND org_id = p_org_id;
1135
1136 CURSOR l_null_date_period_csr IS
1137 SELECT MAX(period_id)
1138 FROM cn_period_statuses_all
1139 WHERE period_set_id = p_period_set_id
1140 AND period_type_id = p_period_type_id
1141 AND period_status = 'O'
1145 BEGIN
1142 AND org_id = p_org_id;
1143
1144 l_period_id NUMBER(15);
1146 IF p_date IS NOT NULL THEN
1147 OPEN l_date_period_csr;
1148 FETCH l_date_period_csr INTO l_period_id;
1149 CLOSE l_date_period_csr;
1150
1151 RETURN l_period_id;
1152 ELSE
1153 OPEN l_null_date_period_csr;
1154 FETCH l_null_date_period_csr INTO l_period_id;
1155 CLOSE l_null_date_period_csr;
1156
1157 RETURN l_period_id;
1158 END IF;
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 IF l_date_period_csr%ISOPEN THEN
1162 CLOSE l_date_period_csr;
1163 END IF;
1164
1165 IF l_null_date_period_csr%ISOPEN THEN
1166 CLOSE l_null_date_period_csr;
1167 END IF;
1168
1169 RAISE;
1170 END get_period;
1171
1172 PROCEDURE mark_notify_dates(
1173 p_start_date DATE
1174 , p_end_date DATE
1175 , p_revert_to_state VARCHAR2
1176 , p_event_log_id NUMBER
1177 , p_period_set_id NUMBER
1178 , p_period_type_id NUMBER
1179 , p_org_id NUMBER
1180 ) IS
1181 l_start_period_id NUMBER(15);
1182 l_end_period_id NUMBER(15);
1183
1184 CURSOR l_date_periods_csr IS
1185 SELECT acc.period_id
1186 , DECODE(acc.period_id, l_start_period_id, p_start_date, acc.start_date) start_date
1187 , DECODE(acc.period_id, l_end_period_id, NVL(p_end_date, acc.end_date), acc.end_date)
1188 end_date
1189 FROM cn_period_statuses_all acc
1190 WHERE acc.period_set_id = p_period_set_id
1191 AND acc.period_type_id = p_period_type_id
1192 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
1193 AND acc.period_status = 'O'
1194 AND acc.org_id = p_org_id
1195 ORDER BY acc.period_id DESC;
1196 BEGIN
1197 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1198 fnd_log.STRING(
1199 fnd_log.level_procedure
1200 , 'cn.plsql.cn_mark_events_pkg.mark_notify_dates.begin'
1201 , 'Beginning of mark_notify_dates ...'
1202 );
1203 END IF;
1204
1205 l_start_period_id := get_period(p_start_date, p_period_set_id, p_period_type_id, p_org_id);
1206 l_end_period_id := get_period(p_end_date, p_period_set_id, p_period_type_id, p_org_id);
1207
1208 FOR l_per IN l_date_periods_csr LOOP
1209 IF l_date_periods_csr%ROWCOUNT = 1 THEN
1210 -- it's the last period, need to mark subsequent periods
1211 mark_notify(
1212 p_salesrep_id => -1000
1213 , p_period_id => l_per.period_id
1214 , p_start_date => l_per.start_date
1215 , p_end_date => l_per.end_date
1216 , p_quota_id => NULL
1217 , p_revert_to_state => p_revert_to_state
1218 , p_event_log_id => p_event_log_id
1219 , p_mode => 'SUBSEQUENT'
1220 , p_org_id => p_org_id
1221 );
1222 ELSE
1223 mark_notify(
1224 p_salesrep_id => -1000
1225 , p_period_id => l_per.period_id
1226 , p_start_date => l_per.start_date
1227 , p_end_date => l_per.end_date
1228 , p_quota_id => NULL
1229 , p_revert_to_state => p_revert_to_state
1230 , p_event_log_id => p_event_log_id
1231 , p_mode => 'NEW'
1232 , p_org_id => p_org_id
1233 );
1234 END IF;
1235 END LOOP;
1236
1237 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1238 fnd_log.STRING(
1239 fnd_log.level_procedure
1240 , 'cn.plsql.cn_mark_events_pkg.mark_notify_dates.end'
1241 , 'End of mark_notify_dates.'
1242 );
1243 END IF;
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1247 fnd_log.STRING(
1248 fnd_log.level_unexpected
1249 , 'cn.plsql.cn_mark_events_pkg.mark_notify_dates.exception'
1250 , SQLERRM
1251 );
1252 END IF;
1253
1254 RAISE;
1255 END mark_notify_dates;
1256
1257 --
1258 -- Name
1259 -- Mark_event_sys_para
1260 -- Purpose
1261 -- History
1262 --
1263 -- 07/12/98 Richard Jin Created
1264 -- NOTES
1265 -- Change System Parameters
1266 -- List of events:
1267 -- 1).CHANGE_SYS_PARA_RC: Change the revenue hierarchy used in the system. All open
1268 -- periods will be affected. Revert to 'ROLL'
1269 -- 2).CHANGE_SYS_PARA_SRP: .change the managerial flag
1270 -- used in the system. All open periods will be affected. Revert to 'CLS'.
1271 -- 3).Other change in system parameter will not affect calculation.
1272 -- o Revenue Classes Hierarchy
1273 -- o managerial Rollup
1274 PROCEDURE mark_event_sys_para(
1275 p_event_name IN VARCHAR2
1276 , p_object_name IN VARCHAR2
1277 , p_object_id IN NUMBER
1278 , p_object_id_old IN NUMBER
1279 , p_period_set_id IN NUMBER
1280 , p_period_type_id IN NUMBER
1284 , p_end_date_old IN DATE
1281 , p_start_date IN DATE
1282 , p_start_date_old IN DATE
1283 , p_end_date IN DATE
1285 , p_org_id IN NUMBER
1286 ) IS
1287 CURSOR l_rollup_flag_periods_csr IS
1288 SELECT period_id, start_date, end_date
1289 FROM cn_period_statuses_all
1290 WHERE period_set_id = p_period_set_id
1291 AND period_type_id = p_period_type_id
1292 AND period_status = 'O'
1293 AND org_id = p_org_id;
1294
1295 -- p_object_id --> head_hierarchy_id
1296 CURSOR l_rc_hiers_csr(l_header_id NUMBER) IS
1297 SELECT dim.start_date, dim.end_date
1298 FROM cn_dim_hierarchies_all dim
1299 WHERE dim.header_dim_hierarchy_id = l_header_id AND org_id = p_org_id;
1300
1301 l_event_log_id NUMBER(15);
1302 dummy NUMBER;
1303 BEGIN
1304 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
1305 RETURN;
1306 END IF;
1307
1308 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1309 fnd_log.STRING(
1310 fnd_log.level_procedure
1311 , 'cn.plsql.cn_mark_events_pkg.mark_event_sys_para.begin'
1312 , 'Beginning of mark_event_sys_para ...'
1313 );
1314 END IF;
1315
1316 log_event(
1317 p_event_name
1318 , p_object_name
1319 , p_object_id
1320 , p_start_date
1321 , p_start_date_old
1322 , p_end_date
1323 , p_end_date_old
1324 , l_event_log_id
1325 , p_org_id
1326 );
1327
1328 IF (p_event_name = 'CHANGE_SYS_PARA_RC') THEN
1329 FOR l_hier IN l_rc_hiers_csr(p_object_id) LOOP
1330 mark_notify_dates(
1331 l_hier.start_date
1332 , l_hier.end_date
1333 , 'ROLL'
1334 , l_event_log_id
1335 , p_period_set_id
1336 , p_period_type_id
1337 , p_org_id
1338 );
1339 END LOOP;
1340
1341 FOR l_hier IN l_rc_hiers_csr(p_object_id_old) LOOP
1342 mark_notify_dates(
1343 l_hier.start_date
1344 , l_hier.end_date
1345 , 'ROLL'
1346 , l_event_log_id
1347 , p_period_set_id
1348 , p_period_type_id
1349 , p_org_id
1350 );
1351 END LOOP;
1352 ELSIF(p_event_name = 'CHANGE_SYS_PARA_SRP') THEN
1353 FOR l_per IN l_rollup_flag_periods_csr LOOP
1354 mark_notify(
1355 p_salesrep_id => -1000
1356 , p_period_id => l_per.period_id
1357 , p_start_date => l_per.start_date
1358 , p_end_date => l_per.end_date
1359 , p_quota_id => NULL
1360 , p_revert_to_state => 'CLS'
1361 , p_event_log_id => l_event_log_id
1362 , p_mode => 'NEW'
1363 , p_org_id => p_org_id
1364 );
1365 END LOOP;
1366 END IF;
1367
1368 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1369 fnd_log.STRING(
1370 fnd_log.level_procedure
1371 , 'cn.plsql.cn_mark_events_pkg.mark_event_sys_para.end'
1372 , 'End of mark_event_sys_para.'
1373 );
1374 END IF;
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1378 fnd_log.STRING(
1379 fnd_log.level_unexpected
1380 , 'cn.plsql.cn_mark_events_pkg.mark_event_sys_para.exception'
1381 , SQLERRM
1382 );
1383 END IF;
1384
1385 RAISE;
1386 END mark_event_sys_para;
1387
1388 --
1389 -- Name
1390 -- Mark_event_cls_rule
1391 -- Purpose
1392 -- History
1393 --
1394 -- 07/12/98 Richard Jin Created
1395 -- NOTES
1396 --
1397 -- Change Classification Rules
1398 -- List of events:
1399 -- 1). CHANGE_CLS_RULES: This means the changes inside a ruleset, i.e. change rules
1400 -- hierarchy, change rule attributes, delete a existing ruleset. All transactions
1401 -- classified using this ruleset should be re-classified. Revert to 'COL'.
1402 -- 2). CHANGE_CLS_RULES_DATE: only change effective periods of a ruleset. No other
1403 -- changes. For example, old one effective from Jan-98 to Mar-98 and new one
1404 -- effective from Feb-98 to May-98. The periods affected are Jan-98 , April-98
1405 -- and May-98. All affected periods need to be re-classified. Revert to 'COL'.
1406 --
1407 -- o Insert/Delete/Update a ruleset
1408 -- o Update effective periods of a ruleset
1409 -- o Insert/Delete/Update rules in s ruleset
1410 PROCEDURE mark_event_cls_rule(
1411 p_event_name IN VARCHAR2
1412 , p_object_name IN VARCHAR2
1413 , p_object_id IN NUMBER
1414 , p_start_date IN DATE
1415 , p_start_date_old IN DATE
1416 , p_end_date IN DATE
1417 , p_end_date_old IN DATE
1418 , p_org_id IN NUMBER
1419 ) IS
1420 l_event_log_id NUMBER(15);
1421 l_date_range_tbl cn_api.date_range_tbl_type;
1422 BEGIN
1423 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
1424 RETURN;
1425 END IF;
1426
1427 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1428 fnd_log.STRING(
1432 );
1429 fnd_log.level_procedure
1430 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_rule.begin'
1431 , 'Beginning of mark_event_cls_rule ...'
1433 END IF;
1434
1435 log_event(
1436 p_event_name
1437 , p_object_name
1438 , p_object_id
1439 , p_start_date
1440 , p_start_date_old
1441 , p_end_date
1442 , p_end_date_old
1443 , l_event_log_id
1444 , p_org_id
1445 );
1446
1447 IF (p_event_name = 'CHANGE_CLS_RULES') THEN
1448 mark_notify_dates(p_start_date_old, p_end_date_old, 'COL', l_event_log_id, p_org_id);
1449 ELSIF(p_event_name = 'CHANGE_CLS_RULES_DATE') THEN
1450 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
1451 , l_date_range_tbl);
1452
1453 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
1454 mark_notify_dates(
1455 l_date_range_tbl(l_ctr).start_date
1456 , l_date_range_tbl(l_ctr).end_date
1457 , 'COL'
1458 , l_event_log_id
1459 , p_org_id
1460 );
1461 END LOOP;
1462 END IF;
1463
1464 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1465 fnd_log.STRING(
1466 fnd_log.level_procedure
1467 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_rule.end'
1468 , 'End of mark_event_cls_rule.'
1469 );
1470 END IF;
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1474 fnd_log.STRING(
1475 fnd_log.level_unexpected
1476 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_rule.exception'
1477 , SQLERRM
1478 );
1479 END IF;
1480
1481 RAISE;
1482 END mark_event_cls_rule;
1483
1484 FUNCTION check_rev_hier(x_header_hierarchy_id NUMBER, p_org_id NUMBER)
1485 RETURN NUMBER IS
1486 x_count NUMBER;
1487 BEGIN
1488 SELECT COUNT(*)
1489 INTO x_count
1490 FROM cn_repositories_all
1491 WHERE rev_class_hierarchy_id = x_header_hierarchy_id AND org_id = p_org_id;
1492
1493 IF x_count = 1 THEN
1494 RETURN 1;
1495 ELSE
1496 RETURN 0;
1497 END IF;
1498 END check_rev_hier;
1499
1500 FUNCTION check_cls_hier(x_header_hierarchy_id NUMBER, p_org_id NUMBER)
1501 RETURN NUMBER IS
1502 x_count NUMBER;
1503 BEGIN
1504 SELECT COUNT(*)
1505 INTO x_count
1506 FROM DUAL
1507 WHERE EXISTS(SELECT 1
1508 FROM cn_attribute_rules_all
1509 WHERE dimension_hierarchy_id = x_header_hierarchy_id AND org_id = p_org_id);
1510
1511 IF x_count >= 1 THEN
1512 RETURN 1;
1513 ELSE
1514 RETURN 0;
1515 END IF;
1516 END check_cls_hier;
1517
1518 --
1519 -- Name
1520 -- Mark_event_rc_hier
1521 -- Purpose
1522 -- History
1523 --
1524 -- 07/12/98 Richard Jin Created
1525 -- NOTES
1526 --
1527 -- Change Revenue Class Hierarchy
1528 -- Revenue Class hierarchy refers to the hierarchy defined in system parameter forms.
1529 -- This canbe determined by query:
1530 -- select count(*)
1531 -- from cn_repositories
1532 -- where rev_class_hierarchy_id = x_header_hierarchy_id;
1533 -- If count(*)=1, this head hierarchy is used as revenune class hierarchy and any
1534 -- change in this hierarchy should be marked.
1535 --
1536 -- List of events:
1537 -- 1). CHANGE_RC_HIER: This includes adding or deleting a node or a root in revenue
1538 -- class hierarchy. All transactions need to be re-populated, revert to 'ROLL'.
1539 -- 2). CHANGE_RC_HIER_PERIOD: .change the effective periods of an interval. All
1540 -- affected transactions should be re-populated, revert to 'ROLL'.
1541 -- Change of the name of revenue class hierarchy has no impact on calculation.
1542 -- o Insert/Delete/Update an interval
1543 -- o Insert/Delete a header hierarchy
1544 -- o Insert/Delete/Update hierarchy edges/roots
1545 PROCEDURE mark_event_rc_hier(
1546 p_event_name IN VARCHAR2
1547 , p_object_name IN VARCHAR2
1548 , p_dim_hierarchy_id IN NUMBER
1549 , p_head_hierarchy_id IN NUMBER
1550 , p_start_date IN DATE
1551 , p_start_date_old IN DATE
1552 , p_end_date IN DATE
1553 , p_end_date_old IN DATE
1554 , p_org_id IN NUMBER
1555 ) IS
1556 l_event_log_id NUMBER(15);
1557 l_date_range_tbl cn_api.date_range_tbl_type;
1558 BEGIN
1559 IF fnd_profile.VALUE('CN_MARK_EVENTS') = 'Y' THEN
1560 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1561 fnd_log.STRING(
1562 fnd_log.level_procedure
1563 , 'cn.plsql.cn_mark_events_pkg.mark_event_rc_hier.begin'
1564 , 'Beginning of mark_event_rc_hier ...'
1565 );
1566 END IF;
1567
1568 log_event(
1569 p_event_name
1570 , p_object_name
1571 , p_dim_hierarchy_id
1572 , p_start_date
1573 , p_start_date_old
1574 , p_end_date
1575 , p_end_date_old
1576 , l_event_log_id
1577 , p_org_id
1578 );
1579
1583 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
1580 IF (p_event_name = 'CHANGE_RC_HIER') OR(p_event_name = 'CHANGE_RC_HIER_DELETE') THEN
1581 mark_notify_dates(p_start_date_old, p_end_date_old, 'ROLL', l_event_log_id, p_org_id);
1582 ELSIF(p_event_name = 'CHANGE_RC_HIER_DATE') THEN
1584 , l_date_range_tbl);
1585
1586 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
1587 mark_notify_dates(
1588 l_date_range_tbl(l_ctr).start_date
1589 , l_date_range_tbl(l_ctr).end_date
1590 , 'ROLL'
1591 , l_event_log_id
1592 , p_org_id
1593 );
1594 END LOOP;
1595 END IF;
1596
1597 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1598 fnd_log.STRING(
1599 fnd_log.level_procedure
1600 , 'cn.plsql.cn_mark_events_pkg.mark_event_rc_hier.end'
1601 , 'End of mark_event_rc_hier.'
1602 );
1603 END IF;
1604 END IF;
1605 EXCEPTION
1606 WHEN OTHERS THEN
1607 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1608 fnd_log.STRING(
1609 fnd_log.level_unexpected
1610 , 'cn.plsql.cn_mark_events_pkg.mark_event_rc_hier.exception'
1611 , SQLERRM
1612 );
1613 END IF;
1614
1615 RAISE;
1616 END mark_event_rc_hier;
1617
1618 -- Name
1619 -- Mark_event_cls_hier
1620 -- Purpose
1621 -- History
1622 --
1623 -- 07/12/98 Richard Jin Created
1624 -- NOTES
1625 --
1626 -- Change Hierarchy Used in Classification
1627 -- To determine whether a header hierarchy is used in classification, use the
1628 -- following query:
1629 -- select count(*)
1630 -- from cn_attribute_rules
1631 -- where dimension_hierarchy_id = x_header_hierarchy_id;
1632 -- if count(*) >=1, this header hierarchy is used in classification. Any changes in
1633 -- this hierarchy will affect classification. All affected periods will be
1634 -- re-classified
1635 -- List of events:
1636 -- 1). CHANGE_CLS_HIER: This includes adding or deleting a node or a root in
1637 -- hierarchy. All transactions need to be re-classified, revert to 'CLS'.
1638 -- 2). CHANGE_CLS_HIER_PERIOD: change the effective periods of an interval. All
1639 -- affected transactions should be re-classified, revert to 'CLS'.
1640 -- 3). CHANGE_CLS_HIER_DELETE: change the effective periods of an interval. All
1641 -- affected transactions should be re-classified, revert to 'CLS'.
1642 -- 4). CHANGE_CLS_HIER_INSERT: don't need to mark since the changes will be caught
1643 -- later at edges level when constructing the hierarchy.
1644 --
1645 -- o Insert/Delete/Update an interval
1646 -- o Insert/Delete a header hierarchy
1647 -- o Insert/Delete/Update hierarchy edges/roots
1648 PROCEDURE mark_event_cls_hier(
1649 p_event_name IN VARCHAR2
1650 , p_object_name IN VARCHAR2
1651 , p_dim_hierarchy_id IN NUMBER
1652 , p_head_hierarchy_id IN NUMBER
1653 , p_start_date IN DATE
1654 , p_start_date_old IN DATE
1655 , p_end_date IN DATE
1656 , p_end_date_old IN DATE
1657 , p_org_id IN NUMBER
1658 ) IS
1659 l_event_log_id NUMBER(15);
1660
1661 CURSOR l_ruleset_dates_csr IS
1662 SELECT start_date
1663 , end_date
1664 FROM cn_rulesets_all
1665 WHERE ruleset_status = 'GENERATED'
1666 AND ruleset_id IN(SELECT DISTINCT ruleset_id
1667 FROM cn_attribute_rules_all
1668 WHERE dimension_hierarchy_id = p_head_hierarchy_id
1669 AND org_id = p_org_id);
1670
1671 l_date_range_diff_tbl cn_api.date_range_tbl_type;
1672 l_date_range_over_tbl cn_api.date_range_tbl_type;
1673 l_date_range_null_tbl cn_api.date_range_tbl_type;
1674 BEGIN
1675 IF fnd_profile.VALUE('CN_MARK_EVENTS') = 'Y' THEN
1676 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1677 fnd_log.STRING(
1678 fnd_log.level_procedure
1679 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_hier.begin'
1680 , 'Beginning of mark_event_cls_hier ...'
1681 );
1682 END IF;
1683
1684 log_event(
1685 p_event_name
1686 , p_object_name
1687 , p_dim_hierarchy_id
1688 , p_start_date
1689 , p_start_date_old
1690 , p_end_date
1691 , p_end_date_old
1692 , l_event_log_id
1693 , p_org_id
1694 );
1695
1696 IF (p_event_name = 'CHANGE_CLS_HIER') OR(p_event_name = 'CHANGE_CLS_HIER_DELETE') THEN
1697 FOR l_set IN l_ruleset_dates_csr LOOP
1698 cn_api.get_date_range_overlap(
1699 p_start_date_old
1700 , p_end_date_old
1701 , l_set.start_date
1702 , l_set.end_date
1703 , p_org_id
1704 , l_date_range_over_tbl
1705 );
1706
1707 FOR l_ctr IN 1 .. l_date_range_over_tbl.COUNT LOOP
1708 mark_notify_dates(
1709 l_date_range_over_tbl(l_ctr).start_date
1710 , l_date_range_over_tbl(l_ctr).end_date
1711 , 'COL'
1712 , l_event_log_id
1713 , p_org_id
1717 l_date_range_over_tbl := l_date_range_null_tbl;
1714 );
1715 END LOOP;
1716
1718 END LOOP;
1719 ELSIF(p_event_name = 'CHANGE_CLS_HIER_DATE') THEN
1720 -- first get the date diff before comparing with rulesets date range
1721 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
1722 , l_date_range_diff_tbl);
1723
1724 -- then get the overlap
1725 FOR l_diff_ctr IN 1 .. l_date_range_diff_tbl.COUNT LOOP
1726 FOR l_set IN l_ruleset_dates_csr LOOP
1727 cn_api.get_date_range_overlap(
1728 l_date_range_diff_tbl(l_diff_ctr).start_date
1729 , l_date_range_diff_tbl(l_diff_ctr).end_date
1730 , l_set.start_date
1731 , l_set.end_date
1732 , p_org_id
1733 , l_date_range_over_tbl
1734 );
1735
1736 FOR l_over_ctr IN 1 .. l_date_range_over_tbl.COUNT LOOP
1737 mark_notify_dates(
1738 l_date_range_over_tbl(l_over_ctr).start_date
1739 , l_date_range_over_tbl(l_over_ctr).end_date
1740 , 'COL'
1741 , l_event_log_id
1742 , p_org_id
1743 );
1744 END LOOP;
1745
1746 l_date_range_over_tbl := l_date_range_null_tbl;
1747 END LOOP;
1748 END LOOP;
1749
1750 l_date_range_diff_tbl := l_date_range_null_tbl;
1751 END IF;
1752
1753 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1754 fnd_log.STRING(
1755 fnd_log.level_procedure
1756 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_hier.end'
1757 , 'End of mark_event_cls_hier.'
1758 );
1759 END IF;
1760 END IF;
1761 EXCEPTION
1762 WHEN OTHERS THEN
1763 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1764 fnd_log.STRING(
1765 fnd_log.level_unexpected
1766 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_hier.exception'
1767 , SQLERRM
1768 );
1769 END IF;
1770
1771 RAISE;
1772 END mark_event_cls_hier;
1773
1774 PROCEDURE mark_event_trx(
1775 x_event_name IN VARCHAR2
1776 , x_object_name IN VARCHAR2
1777 , x_object_id IN NUMBER
1778 , x_processed_period_id_old IN NUMBER
1779 , x_processed_period_id_new IN NUMBER
1780 , x_rollup_period_id_old IN NUMBER
1781 , x_rollup_period_id_new IN NUMBER
1782 , p_org_id IN NUMBER
1783 ) IS
1784 BEGIN
1785 NULL;
1786 END mark_event_trx;
1787
1788 -- Mark Event Quota
1789 -- Description Creates the Notify log and mark the event for Plan Element.
1790 -- Plan Element Has different event Names, all will do the same kind of job
1791 -- except the revent to state and the Event Name.
1792 -- Some cases the event name must be same.
1793
1794 -- CASE 1: whenever there IS a change IN the CN_QUOTAS,it marks the
1795 -- It marks the all the Salesrep under that quotas.
1796 --
1797 -- CASE 2: whenever there is a change in the start date and the end date
1798 -- of CN_QUOTAS, it marks the specific salesreps in that period
1799 -- for that quotas.
1800 -- called from trigger
1801 --
1802 -- Case 3: whenever there is a change in the CN_QUOTA_RULES only on revenue
1803 -- class id it marks all the record for that rules and quotas.
1804 -- called from trigger
1805 --
1806 -- Case 4: whenever there is a Insert/Delete in CN_QUOTA_RULES, it marks
1807 -- all the record for that quotas.
1808 -- called from trigger
1809 --
1810 -- Case 5 whenever there is a change in the start date and end date of
1811 -- CN_QUOTA_RULE_UPLIFTS, it marks the affected period records
1812 -- for that quota.
1813 -- called from trigger
1814 --
1815 -- Case 6: whenever there is insert/delete the cn_quota_rule_uplifts
1816 -- it marks the affected salesrep for that quotas.
1817 -- called from trigger
1818 PROCEDURE mark_event_quota(
1819 p_event_name VARCHAR2
1820 , p_object_name VARCHAR2
1821 , p_object_id NUMBER
1822 , p_start_date DATE
1823 , p_start_date_old DATE
1824 , p_end_date DATE
1825 , p_end_date_old DATE
1826 , p_org_id NUMBER
1827 ) IS
1828 l_event_log_id NUMBER;
1829 l_date_range_rec_tbl cn_api.date_range_tbl_type;
1830 l_start_period_id NUMBER(15);
1831 l_end_period_id NUMBER(15);
1832 l_start_date DATE;
1833 l_end_date DATE;
1834
1835 CURSOR affected_srp_period_curs(
1836 l_start_period_id NUMBER
1837 , l_end_period_id NUMBER
1838 , l_start_date DATE
1839 , l_end_date DATE
1840 ) IS
1841 -- modified by rjin 11/10/1999 add distinct
1842 SELECT DISTINCT spq.salesrep_id
1843 , spq.period_id
1844 , spq.quota_id
1845 , DECODE(acc.period_id, l_start_period_id, l_start_date, acc.start_date)
1846 start_date
1850 , acc.end_date
1847 , DECODE(
1848 acc.period_id
1849 , l_end_period_id, NVL(l_end_date, acc.end_date)
1851 ) end_date
1852 FROM cn_srp_period_quotas_all spq
1853 , cn_srp_intel_periods_all sip
1854 , cn_period_statuses_all acc
1855 WHERE spq.quota_id = p_object_id
1856 AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
1857 AND sip.salesrep_id = spq.salesrep_id
1858 AND sip.period_id = spq.period_id
1859 AND sip.org_id = spq.org_id
1860 AND sip.processing_status_code <> 'CLEAN'
1861 AND acc.period_id = spq.period_id
1862 AND acc.org_id = spq.org_id
1863 AND acc.period_status IN('O', 'F');
1864
1865 CURSOR l_quota_dates_csr IS
1866 SELECT start_date
1867 , end_date
1868 FROM cn_quotas_all
1869 WHERE quota_id = p_object_id;
1870
1871 --clku
1872 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
1873 SELECT quota_id
1874 FROM cn_srp_period_quotas_all
1875 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
1876
1877 temp_quota_id cn_quotas.quota_id%TYPE;
1878 l_return_status VARCHAR2(50);
1879 l_msg_count NUMBER;
1880 l_msg_data VARCHAR2(2000);
1881 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
1882 BEGIN
1883 -- Log the Event for the Quota changes or any changes in the
1884 -- Plan Element.
1885 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
1886 RETURN;
1887 END IF;
1888
1889 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1890 fnd_log.STRING(
1891 fnd_log.level_procedure
1892 , 'cn.plsql.cn_mark_events_pkg.mark_event_quota.begin'
1893 , 'Beginning of mark_event_quota ...'
1894 );
1895 END IF;
1896
1897 cn_mark_events_pkg.log_event(
1898 p_event_name => p_event_name
1899 , p_object_name => p_object_name
1900 , p_object_id => p_object_id
1901 , p_start_date => p_start_date
1902 , p_start_date_old => p_start_date_old
1903 , p_end_date => p_end_date
1904 , p_end_date_old => p_end_date_old
1905 , x_event_log_id => l_event_log_id
1906 , p_org_id => p_org_id
1907 );
1908
1909 -- clku, move get_parent_plan_elts outside the period/salesrep loop
1910 IF (p_object_id IS NOT NULL) THEN
1911 cn_calc_sql_exps_pvt.get_parent_plan_elts(
1912 p_api_version => 1.0
1913 , p_node_type => 'P'
1914 , p_init_msg_list => 'T'
1915 , p_node_id => p_object_id
1916 , x_plan_elt_id_tbl => dependent_pe_tbl
1917 , x_return_status => l_return_status
1918 , x_msg_count => l_msg_count
1919 , x_msg_data => l_msg_data
1920 );
1921 END IF;
1922
1923 -- Check the Event Name
1924 IF p_event_name = 'CHANGE_QUOTA_CALC' THEN
1925 -- 1. Update in Quotas
1926 OPEN l_quota_dates_csr;
1927 FETCH l_quota_dates_csr INTO l_start_date, l_end_date;
1928 CLOSE l_quota_dates_csr;
1929
1930 l_start_period_id := cn_api.get_acc_period_id(l_start_date, p_org_id);
1931 l_end_period_id := cn_api.get_acc_period_id(l_end_date, p_org_id);
1932
1933 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id
1934 , l_start_date, l_end_date) LOOP
1935 -- modified by rjin 11/10/1999
1936 -- since all affected period (including subsequent periods)
1937 -- are garaunteed to be marked, so we only need to mark 'NEW'
1938 cn_mark_events_pkg.mark_notify(
1939 p_salesrep_id => affected_recs.salesrep_id
1940 , p_period_id => affected_recs.period_id
1941 , p_start_date => NULL
1942 , p_end_date => NULL
1943 , p_quota_id => affected_recs.quota_id
1944 , p_revert_to_state => 'CALC'
1945 , p_event_log_id => l_event_log_id
1946 , p_mode => 'NEW'
1947 , p_org_id => p_org_id
1948 );
1949
1950 IF (dependent_pe_tbl.COUNT > 0) THEN
1951 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
1952 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
1953 FETCH l_pe_cursor INTO temp_quota_id;
1954
1955 IF l_pe_cursor%FOUND THEN
1956 cn_mark_events_pkg.mark_notify(
1957 p_salesrep_id => affected_recs.salesrep_id
1958 , p_period_id => affected_recs.period_id
1959 , p_start_date => NULL
1960 , p_end_date => NULL
1961 , p_quota_id => dependent_pe_tbl(i)
1962 , p_revert_to_state => 'CALC'
1966 );
1963 , p_event_log_id => l_event_log_id
1964 , p_mode => 'NEW'
1965 , p_org_id => p_org_id
1967 END IF;
1968
1969 CLOSE l_pe_cursor;
1970 END LOOP;
1971 END IF; -- If (dependent_pe_tbl.count > 0)
1972 END LOOP;
1973 ELSIF p_event_name = 'CHANGE_QUOTA_DATE' THEN
1974 cn_api.get_date_range_diff(
1975 a_start_date => p_start_date
1976 , a_end_date => p_end_date
1977 , b_start_date => p_start_date_old
1978 , b_end_date => p_end_date_old
1979 , x_date_range_tbl => l_date_range_rec_tbl
1980 );
1981
1982 FOR i IN 1 .. l_date_range_rec_tbl.COUNT LOOP
1983 l_start_period_id :=
1984 cn_api.get_acc_period_id(l_date_range_rec_tbl(i).start_date, p_org_id);
1985 l_end_period_id := cn_api.get_acc_period_id(l_date_range_rec_tbl(i).end_date, p_org_id);
1986
1987 FOR affected_period_recs IN affected_srp_period_curs(
1988 l_start_period_id
1989 , l_end_period_id
1990 , l_date_range_rec_tbl(i).start_date
1991 , l_date_range_rec_tbl(i).end_date
1992 ) LOOP
1993 cn_mark_events_pkg.mark_notify(
1994 p_salesrep_id => affected_period_recs.salesrep_id
1995 , p_period_id => affected_period_recs.period_id
1996 , p_start_date => affected_period_recs.start_date
1997 , p_end_date => affected_period_recs.end_date
1998 , p_quota_id => NULL
1999 , p_revert_to_state => 'ROLL'
2000 , p_event_log_id => l_event_log_id
2001 , p_org_id => p_org_id
2002 );
2003 END LOOP;
2004 END LOOP;
2005 ELSIF p_event_name IN('CHANGE_QUOTA_ROLL', 'CHANGE_PE_DIRECT_INDIRECT') THEN
2006 --
2007 -- Changes in Quota Rules INSERT/UPDATE/DELETE
2008 --
2009 OPEN l_quota_dates_csr;
2010 FETCH l_quota_dates_csr INTO l_start_date, l_end_date;
2011 CLOSE l_quota_dates_csr;
2012
2013 l_start_period_id := cn_api.get_acc_period_id(l_start_date, p_org_id);
2014 l_end_period_id := cn_api.get_acc_period_id(l_end_date, p_org_id);
2015
2016 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id
2017 , l_start_date, l_end_date) LOOP
2018 -- modified by rjin 11/10/1999
2019 -- since all affected period (including subsequent periods)
2020 -- are garaunteed to be marked, so we only need to mark 'NEW'
2021 cn_mark_events_pkg.mark_notify(
2022 p_salesrep_id => affected_recs.salesrep_id
2023 , p_period_id => affected_recs.period_id
2024 , p_start_date => affected_recs.start_date
2025 , p_end_date => affected_recs.end_date
2026 , p_quota_id => NULL
2027 , p_revert_to_state => 'ROLL'
2028 , p_event_log_id => l_event_log_id
2029 , p_mode => 'NEW'
2030 , p_org_id => p_org_id
2031 );
2032 END LOOP;
2033 ELSIF p_event_name = 'CHANGE_QUOTA_POP' THEN
2034 --
2035 -- 1. Insert/Delete in Rule Uplifts
2036 --
2037 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2038 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2039
2040 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id
2041 , p_start_date_old, p_end_date_old) LOOP
2042 cn_mark_events_pkg.mark_notify(
2043 p_salesrep_id => affected_recs.salesrep_id
2044 , p_period_id => affected_recs.period_id
2045 , p_start_date => affected_recs.start_date
2046 , p_end_date => affected_recs.end_date
2047 , p_quota_id => affected_recs.quota_id
2048 , p_revert_to_state => 'POP'
2049 , p_event_log_id => l_event_log_id
2050 , p_org_id => p_org_id
2051 );
2052
2053 IF (dependent_pe_tbl.COUNT > 0) THEN
2054 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2055 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
2056 FETCH l_pe_cursor INTO temp_quota_id;
2057
2058 IF l_pe_cursor%FOUND THEN
2059 cn_mark_events_pkg.mark_notify(
2060 p_salesrep_id => affected_recs.salesrep_id
2061 , p_period_id => affected_recs.period_id
2062 , p_start_date => affected_recs.start_date
2063 , p_end_date => affected_recs.end_date
2064 , p_quota_id => dependent_pe_tbl(i)
2065 , p_revert_to_state => 'POP'
2066 , p_event_log_id => l_event_log_id
2070
2067 , p_org_id => p_org_id
2068 );
2069 END IF;
2071 CLOSE l_pe_cursor;
2072 END LOOP;
2073 END IF; -- If (dependent_pe_tbl.count > 0)
2074 END LOOP;
2075 ELSIF p_event_name = 'CHANGE_QUOTA_UPLIFT_DATE' THEN
2076 --
2077 -- Update Uplift Start Date and End Date
2078 --
2079 cn_api.get_date_range_diff(
2080 a_start_date => p_start_date
2081 , a_end_date => p_end_date
2082 , b_start_date => p_start_date_old
2083 , b_end_date => p_end_date_old
2084 , x_date_range_tbl => l_date_range_rec_tbl
2085 );
2086
2087 FOR i IN 1 .. l_date_range_rec_tbl.COUNT LOOP
2088 l_start_period_id :=
2089 cn_api.get_acc_period_id(l_date_range_rec_tbl(i).start_date, p_org_id);
2090 l_end_period_id := cn_api.get_acc_period_id(l_date_range_rec_tbl(i).end_date, p_org_id);
2091
2092 FOR affected_period_recs IN affected_srp_period_curs(
2093 l_start_period_id
2094 , l_end_period_id
2095 , l_date_range_rec_tbl(i).start_date
2096 , l_date_range_rec_tbl(i).end_date
2097 ) LOOP
2098 cn_mark_events_pkg.mark_notify(
2099 p_salesrep_id => affected_period_recs.salesrep_id
2100 , p_period_id => affected_period_recs.period_id
2101 , p_start_date => affected_period_recs.start_date
2102 , p_end_date => affected_period_recs.end_date
2103 , p_quota_id => affected_period_recs.quota_id
2104 , p_revert_to_state => 'POP'
2105 , p_event_log_id => l_event_log_id
2106 , p_org_id => p_org_id
2107 );
2108
2109 IF (dependent_pe_tbl.COUNT > 0) THEN
2110 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2111 OPEN l_pe_cursor(affected_period_recs.salesrep_id, affected_period_recs.period_id, dependent_pe_tbl(i));
2112 FETCH l_pe_cursor INTO temp_quota_id;
2113
2114 IF l_pe_cursor%FOUND THEN
2115 cn_mark_events_pkg.mark_notify(
2116 p_salesrep_id => affected_period_recs.salesrep_id
2117 , p_period_id => affected_period_recs.period_id
2118 , p_start_date => affected_period_recs.start_date
2119 , p_end_date => affected_period_recs.end_date
2120 , p_quota_id => dependent_pe_tbl(i)
2121 , p_revert_to_state => 'POP'
2122 , p_event_log_id => l_event_log_id
2123 , p_org_id => p_org_id
2124 );
2125 END IF;
2126
2127 CLOSE l_pe_cursor;
2128 END LOOP;
2129 END IF; -- If (dependent_pe_tbl.count > 0)
2130 END LOOP;
2131 END LOOP;
2132 END IF;
2133
2134 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2135 fnd_log.STRING(
2136 fnd_log.level_procedure
2137 , 'cn.plsql.cn_mark_events_pkg.mark_event_quota.end'
2138 , 'End of mark_event_quota.'
2139 );
2140 END IF;
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2144 fnd_log.STRING(
2145 fnd_log.level_unexpected
2146 , 'cn.plsql.cn_mark_events_pkg.mark_event_quota.exception'
2147 , SQLERRM
2148 );
2149 END IF;
2150
2151 RAISE;
2152 END mark_event_quota;
2153
2154 -- Start of Comments
2155 -- name : mark_event_rt_quota
2156 -- Type : None
2157 -- Pre-reqs : None.
2158 -- Usage : Procedure to Mark the cn_rt_quota_asgn
2159 -- Parameters :
2160 -- IN : p_event_name IN VARCHAR2
2161 -- p_object_name IN VARCHAR2
2162 -- p_object_id IN NUMBER
2163 -- p_start_date IN DATE
2164 -- p_start_Date_old IN DATE
2165 -- p_end_date IN DATE
2166 -- p_end_date_old IN DATE
2167 --
2168 -- Version : Current version 1.0
2169 -- Initial version 1.0
2170 --
2171 -- Case 7: whenever there is a change in the start date and end date of
2172 -- CN_RT_QUOTA_ASGNS, it marks the affected period records
2173 -- for that quota.
2174 -- called from trigger
2175 --
2176 -- Case 8: whenever there is insert/delete the cn_rt_quota_asgns
2177 -- it marks the affected salesrep for that quotas.
2178 -- called from trigger
2179 --
2180 PROCEDURE mark_event_rt_quota(
2181 p_event_name VARCHAR2
2182 , p_object_name VARCHAR2
2183 , p_object_id NUMBER
2184 , p_start_date DATE
2185 , p_start_date_old DATE
2186 , p_end_date DATE
2187 , p_end_date_old DATE
2188 , p_org_id NUMBER
2189 ) IS
2190 l_event_log_id NUMBER;
2194
2191 l_date_range_rec_tbl cn_api.date_range_tbl_type;
2192 l_start_period_id NUMBER(15);
2193 l_end_period_id NUMBER(15);
2195 CURSOR affected_srp_period_curs(l_start_period_id NUMBER, l_end_period_id NUMBER) IS
2196 -- modified by rjin 11/10/1999 add distinct
2197 SELECT DISTINCT spq.salesrep_id
2198 , spq.period_id
2199 , spq.quota_id
2200 FROM cn_srp_period_quotas_all spq
2201 , cn_srp_intel_periods_all sip
2202 , cn_period_statuses_all acc
2203 WHERE spq.quota_id = p_object_id
2204 AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2205 AND sip.salesrep_id = spq.salesrep_id
2206 AND sip.period_id = spq.period_id
2207 AND sip.org_id = spq.org_id
2208 AND sip.processing_status_code <> 'CLEAN'
2209 AND acc.period_id = spq.period_id
2210 AND acc.org_id = spq.org_id
2211 AND acc.period_status IN('O', 'F');
2212
2213 CURSOR l_quota_dates_csr IS
2214 SELECT start_date
2215 , end_date
2216 FROM cn_quotas_all
2217 WHERE quota_id = p_object_id;
2218
2219 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
2220 SELECT quota_id
2221 FROM cn_srp_period_quotas_all
2222 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
2223
2224 temp_quota_id cn_quotas.quota_id%TYPE;
2225 l_return_status VARCHAR2(50);
2226 l_msg_count NUMBER;
2227 l_msg_data VARCHAR2(2000);
2228 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
2229 BEGIN
2230 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2231 RETURN;
2232 END IF;
2233
2234 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2235 fnd_log.STRING(
2236 fnd_log.level_procedure
2237 , 'cn.plsql.cn_mark_events_pkg.mark_event_rt_quota.begin'
2238 , 'Beginning of mark_event_rt_quota ...'
2239 );
2240 END IF;
2241
2242 cn_mark_events_pkg.log_event(
2243 p_event_name => p_event_name
2244 , p_object_name => p_object_name
2245 , p_object_id => p_object_id
2246 , p_start_date => p_start_date
2247 , p_start_date_old => p_start_date_old
2248 , p_end_date => p_end_date
2249 , p_end_date_old => p_end_date_old
2250 , x_event_log_id => l_event_log_id
2251 , p_org_id => p_org_id
2252 );
2253
2254 -- clku, move get_parent_plan_elts outside the period/salesrep loop
2255 IF (p_object_id IS NOT NULL) THEN
2256 cn_calc_sql_exps_pvt.get_parent_plan_elts(
2257 p_api_version => 1.0
2258 , p_node_type => 'P'
2259 , p_init_msg_list => 'T'
2260 , p_node_id => p_object_id
2261 , x_plan_elt_id_tbl => dependent_pe_tbl
2262 , x_return_status => l_return_status
2263 , x_msg_count => l_msg_count
2264 , x_msg_data => l_msg_data
2265 );
2266 END IF;
2267
2268 --
2269 -- Check the Event Name
2270 --
2271 IF p_event_name = 'CHANGE_QUOTA_CALC' THEN
2272 -- 1. update cn_trx_factors.event_factor
2273 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2274 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2275
2276 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id) LOOP
2277 cn_mark_events_pkg.mark_notify(
2278 p_salesrep_id => affected_recs.salesrep_id
2279 , p_period_id => affected_recs.period_id
2280 , p_start_date => NULL
2281 , p_end_date => NULL
2282 , p_quota_id => affected_recs.quota_id
2283 , p_revert_to_state => 'CALC'
2284 , p_event_log_id => l_event_log_id
2285 , p_org_id => p_org_id
2286 );
2287
2288 IF (dependent_pe_tbl.COUNT > 0) THEN
2289 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2290 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
2291 FETCH l_pe_cursor INTO temp_quota_id;
2292
2293 IF l_pe_cursor%FOUND THEN
2294 cn_mark_events_pkg.mark_notify(
2295 p_salesrep_id => affected_recs.salesrep_id
2296 , p_period_id => affected_recs.period_id
2297 , p_start_date => NULL
2298 , p_end_date => NULL
2299 , p_quota_id => dependent_pe_tbl(i)
2300 , p_revert_to_state => 'CALC'
2301 , p_event_log_id => l_event_log_id
2302 , p_org_id => p_org_id
2303 );
2304 END IF;
2305
2306 CLOSE l_pe_cursor;
2307 END LOOP;
2308 END IF; -- If (dependent_pe_tbl.count > 0)
2309 END LOOP;
2313 --
2310 ELSIF p_event_name = 'CHANGE_QUOTA_RT_DATE' THEN
2311 --
2312 -- Update rt_quota Assigns Start Date, End Date
2314 cn_api.get_date_range_diff(
2315 a_start_date => p_start_date
2316 , a_end_date => p_end_date
2317 , b_start_date => p_start_date_old
2318 , b_end_date => p_end_date_old
2319 , x_date_range_tbl => l_date_range_rec_tbl
2320 );
2321
2322 FOR i IN 1 .. l_date_range_rec_tbl.COUNT LOOP
2323 l_start_period_id :=
2324 cn_api.get_acc_period_id(l_date_range_rec_tbl(i).start_date, p_org_id);
2325 l_end_period_id := cn_api.get_acc_period_id(l_date_range_rec_tbl(i).end_date, p_org_id);
2326
2327 FOR affected_period_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id) LOOP
2328 cn_mark_events_pkg.mark_notify(
2329 p_salesrep_id => affected_period_recs.salesrep_id
2330 , p_period_id => affected_period_recs.period_id
2331 , p_start_date => NULL
2332 , p_end_date => NULL
2333 , p_quota_id => affected_period_recs.quota_id
2334 , p_revert_to_state => 'CALC'
2335 , p_event_log_id => l_event_log_id
2336 , p_org_id => p_org_id
2337 );
2338
2339 IF (dependent_pe_tbl.COUNT > 0) THEN
2340 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2341 OPEN l_pe_cursor(affected_period_recs.salesrep_id, affected_period_recs.period_id, dependent_pe_tbl(i));
2342 FETCH l_pe_cursor INTO temp_quota_id;
2343
2344 IF l_pe_cursor%FOUND THEN
2345 cn_mark_events_pkg.mark_notify(
2346 p_salesrep_id => affected_period_recs.salesrep_id
2347 , p_period_id => affected_period_recs.period_id
2348 , p_start_date => NULL
2349 , p_end_date => NULL
2350 , p_quota_id => dependent_pe_tbl(i)
2351 , p_revert_to_state => 'CALC'
2352 , p_event_log_id => l_event_log_id
2353 , p_org_id => p_org_id
2354 );
2355 END IF;
2356
2357 CLOSE l_pe_cursor;
2358 END LOOP;
2359 END IF; -- If (dependent_pe_tbl.count > 0)
2360 END LOOP;
2361 END LOOP;
2362 END IF;
2363
2364 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2365 fnd_log.STRING(
2366 fnd_log.level_procedure
2367 , 'cn.plsql.cn_mark_events_pkg.mark_event_rt_quota.end'
2368 , 'End of mark_event_rt_quota.'
2369 );
2370 END IF;
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2374 fnd_log.STRING(
2375 fnd_log.level_unexpected
2376 , 'cn.plsql.cn_mark_events_pkg.mark_event_rt_quota.exception'
2377 , SQLERRM
2378 );
2379 END IF;
2380
2381 RAISE;
2382 END mark_event_rt_quota;
2383
2384 -- Start of Comments
2385 -- name : mark_event_trx_factor
2386 -- Type : None
2387 -- Pre-reqs : None.
2388 -- Usage : Procedure to Mark the cn_trx_factors Event
2389 -- Parameters :
2390 -- IN : p_event_name IN VARCHAR2
2391 -- p_object_name IN VARCHAR2
2392 -- p_object_id IN NUMBER
2393 -- p_start_date IN DATE
2394 -- p_start_Date_old IN DATE
2395 -- p_end_date IN DATE
2396 -- p_end_date_old IN DATE
2397 --
2398 -- Version : Current version 1.0
2399 -- Initial version 1.0
2400 -- Case 9: whenever there is update in the cn_trx_factors
2401 -- it marks the affected salesrep for that quotas.
2402 -- called from trigger
2403 --
2404 PROCEDURE mark_event_trx_factor(
2405 p_event_name VARCHAR2
2406 , p_object_name VARCHAR2
2407 , p_object_id NUMBER
2408 , p_start_date DATE
2409 , p_start_date_old DATE
2410 , p_end_date DATE
2411 , p_end_date_old DATE
2412 , p_org_id NUMBER
2413 ) IS
2414 l_event_log_id NUMBER;
2415 l_start_period_id NUMBER(15);
2416 l_end_period_id NUMBER(15);
2417 l_start_date DATE;
2418 l_end_date DATE;
2419
2420 CURSOR affected_srp_period_curs(
2421 l_start_period_id NUMBER
2422 , l_end_period_id NUMBER
2423 , l_start_date DATE
2424 , l_end_date DATE
2425 ) IS
2426 -- modified by rjin 11/10/1999 add distinct
2427 SELECT DISTINCT spq.salesrep_id
2428 , spq.period_id
2429 , spq.quota_id
2430 , DECODE(acc.period_id, l_start_period_id, l_start_date, acc.start_date)
2431 start_date
2432 , DECODE(
2433 acc.period_id
2434 , l_end_period_id, NVL(l_end_date, acc.end_date)
2438 , cn_srp_intel_periods_all sip
2435 , acc.end_date
2436 ) end_date
2437 FROM cn_srp_period_quotas_all spq
2439 , cn_period_statuses_all acc
2440 WHERE spq.quota_id = p_object_id
2441 AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2442 AND sip.salesrep_id = spq.salesrep_id
2443 AND sip.period_id = spq.period_id
2444 AND sip.org_id = spq.org_id
2445 AND sip.processing_status_code <> 'CLEAN'
2446 AND acc.period_id = spq.period_id
2447 AND acc.org_id = spq.org_id
2448 AND acc.period_status IN('O', 'F');
2449
2450 CURSOR l_quota_dates_csr IS
2451 SELECT start_date
2452 , end_date
2453 FROM cn_quotas_all
2454 WHERE quota_id = p_object_id;
2455
2456 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
2457 SELECT quota_id
2458 FROM cn_srp_period_quotas_all
2459 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
2460
2461 temp_quota_id cn_quotas.quota_id%TYPE;
2462 l_return_status VARCHAR2(50);
2463 l_msg_count NUMBER;
2464 l_msg_data VARCHAR2(2000);
2465 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
2466 BEGIN
2467 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2468 RETURN;
2469 END IF;
2470
2471 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2472 fnd_log.STRING(
2473 fnd_log.level_procedure
2474 , 'cn.plsql.cn_mark_events_pkg.mark_event_trx_factor.begin'
2475 , 'Beginning of mark_event_trx_factor ...'
2476 );
2477 END IF;
2478
2479 cn_mark_events_pkg.log_event(
2480 p_event_name => p_event_name
2481 , p_object_name => p_object_name
2482 , p_object_id => p_object_id
2483 , p_start_date => p_start_date
2484 , p_start_date_old => p_start_date_old
2485 , p_end_date => p_end_date
2486 , p_end_date_old => p_end_date_old
2487 , x_event_log_id => l_event_log_id
2488 , p_org_id => p_org_id
2489 );
2490
2491 --
2492 -- Check the Event Name
2493 --
2494 IF p_event_name = 'CHANGE_QUOTA_POP' THEN
2495 -- 1. update cn_trx_factors.event_factor
2496 OPEN l_quota_dates_csr;
2497 FETCH l_quota_dates_csr INTO l_start_date, l_end_date;
2498 CLOSE l_quota_dates_csr;
2499
2500 l_start_period_id := cn_api.get_acc_period_id(l_start_date, p_org_id);
2501 l_end_period_id := cn_api.get_acc_period_id(l_end_date, p_org_id);
2502
2503 -- clku, move get_parent_plan_elts outside the period/salesrep loop
2504 IF (p_object_id IS NOT NULL) THEN
2505 cn_calc_sql_exps_pvt.get_parent_plan_elts(
2506 p_api_version => 1.0
2507 , p_node_type => 'P'
2508 , p_init_msg_list => 'T'
2509 , p_node_id => p_object_id
2510 , x_plan_elt_id_tbl => dependent_pe_tbl
2511 , x_return_status => l_return_status
2512 , x_msg_count => l_msg_count
2513 , x_msg_data => l_msg_data
2514 );
2515 END IF;
2516
2517 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id
2518 , l_start_date, l_end_date) LOOP
2519 -- modified by rjin 11/10/1999
2520 -- since all affected period (including subsequent periods)
2521 -- are garaunteed to be marked, so we only need to mark 'NEW'
2522 cn_mark_events_pkg.mark_notify(
2523 p_salesrep_id => affected_recs.salesrep_id
2524 , p_period_id => affected_recs.period_id
2525 , p_start_date => affected_recs.start_date
2526 , p_end_date => affected_recs.end_date
2527 , p_quota_id => affected_recs.quota_id
2528 , p_revert_to_state => 'POP'
2529 , p_event_log_id => l_event_log_id
2530 , p_mode => 'NEW'
2531 , p_org_id => p_org_id
2532 );
2533
2534 IF (dependent_pe_tbl.COUNT > 0) THEN
2535 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2536 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
2537 FETCH l_pe_cursor INTO temp_quota_id;
2538
2539 IF l_pe_cursor%FOUND THEN
2540 cn_mark_events_pkg.mark_notify(
2541 p_salesrep_id => affected_recs.salesrep_id
2542 , p_period_id => affected_recs.period_id
2543 , p_start_date => affected_recs.start_date
2544 , p_end_date => affected_recs.end_date
2545 , p_quota_id => dependent_pe_tbl(i)
2546 , p_revert_to_state => 'POP'
2547 , p_event_log_id => l_event_log_id
2548 , p_mode => 'NEW'
2549 , p_org_id => p_org_id
2550 );
2554 END LOOP;
2551 END IF;
2552
2553 CLOSE l_pe_cursor;
2555 END IF; -- If (dependent_pe_tbl.count > 0)
2556 END LOOP;
2557 END IF;
2558
2559 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2560 fnd_log.STRING(
2561 fnd_log.level_procedure
2562 , 'cn.plsql.cn_mark_events_pkg.mark_event_trx_factor.end'
2563 , 'End of mark_event_trx_factor.'
2564 );
2565 END IF;
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2569 fnd_log.STRING(
2570 fnd_log.level_unexpected
2571 , 'cn.plsql.cn_mark_events_pkg.mark_event_trx_factor.exception'
2572 , SQLERRM
2573 );
2574 END IF;
2575
2576 RAISE;
2577 END mark_event_trx_factor;
2578
2579 --
2580 -- Procedure Name
2581 -- mark_event_role_plans
2582 -- Purpose
2583 -- Insert affected salesrep information into cn_event_log and cn_notify_log
2584 -- for recalculation purpose.
2585 -- Calls log_event, mark_notify.
2586 -- Called by cn_role_plans_t trigger.
2587 -- History
2588 -- 09/13/99 Harlen Chen Created
2589 PROCEDURE mark_event_role_plans(
2590 p_event_name VARCHAR2
2591 , p_object_name VARCHAR2
2592 , p_object_id NUMBER
2593 , p_start_date DATE
2594 , p_start_date_old DATE
2595 , p_end_date DATE
2596 , p_end_date_old DATE
2597 , p_org_id NUMBER
2598 ) IS
2599 l_role_id cn_role_plans.role_id%TYPE;
2600 l_event_log_id NUMBER;
2601 l_start_period_id NUMBER(15);
2602 l_end_period_id NUMBER(15);
2603 l_date_range_tbl cn_api.date_range_tbl_type;
2604
2605 CURSOR affected_srp_period(l_s_date DATE, l_e_date DATE) IS
2606 -- for CHANGE_SRP_ROLE_PLAN
2607 -- use the start_date/end_date info to restrict the periods affected.
2608
2609 -- clku perf fix for bug 3628870, removed the hintsto avoid FTS
2610 SELECT sr.salesrep_id salesrep_id
2611 , acc.period_id period_id
2612 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2613 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2614 end_date
2615 FROM cn_srp_roles sr, cn_srp_intel_periods intel, cn_period_statuses acc
2616 WHERE sr.role_id = l_role_id
2617 AND sr.org_id = p_org_id
2618 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2619 AND acc.period_status = 'O'
2620 AND acc.org_id = p_org_id
2621 AND intel.salesrep_id = sr.salesrep_id
2622 AND intel.period_id = acc.period_id
2623 AND intel.org_id = p_org_id
2624 AND intel.processing_status_code <> 'CLEAN';
2625 BEGIN
2626 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2627 RETURN;
2628 END IF;
2629
2630 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2631 fnd_log.STRING(
2632 fnd_log.level_procedure
2633 , 'cn.plsql.cn_mark_events_pkg.mark_event_role_plans.begin'
2634 , 'Beginning of mark_event_role_plans ...'
2635 );
2636 END IF;
2637
2638 l_role_id := p_object_id;
2639 cn_mark_events_pkg.log_event(
2640 p_event_name
2641 , p_object_name
2642 , p_object_id
2643 , p_start_date
2644 , p_start_date_old
2645 , p_end_date
2646 , p_end_date_old
2647 , l_event_log_id
2648 , p_org_id
2649 );
2650
2651 IF p_event_name = 'CHANGE_SRP_ROLE_PLAN' THEN
2652 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2653 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2654
2655 FOR l_rec IN affected_srp_period(p_start_date_old, p_end_date_old) LOOP
2656 -- For ROLL events, pass in start_date/end_date, pass null to p_quota_id
2657 cn_mark_events_pkg.mark_notify(
2658 l_rec.salesrep_id
2659 , l_rec.period_id
2660 , l_rec.start_date
2661 , l_rec.end_date
2662 , NULL
2663 , -- p_quota_id
2664 'ROLL'
2665 , l_event_log_id
2666 , p_org_id
2667 );
2668 END LOOP;
2669 ELSIF p_event_name = 'CHANGE_SRP_ROLE_PLAN_DATE' THEN
2670 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
2671 , l_date_range_tbl);
2672
2673 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
2674 --bug fix 6890504 raj
2675 l_start_period_id :=
2676 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date - 1, p_org_id);
2677 l_end_period_id := cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
2678
2679 FOR l_rec IN affected_srp_period(l_date_range_tbl(l_ctr).start_date - 1
2680 , l_date_range_tbl(l_ctr).end_date) LOOP
2681 -- ROLL events : pass in start_date/end_date, pass null to p_quota_id
2682 cn_mark_events_pkg.mark_notify(
2683 l_rec.salesrep_id
2684 , l_rec.period_id
2685 , l_rec.start_date
2686 , l_rec.end_date
2687 , NULL
2691 , p_org_id
2688 , -- p_quota_id
2689 'ROLL'
2690 , l_event_log_id
2692 );
2693 END LOOP;
2694 END LOOP;
2695 END IF;
2696
2697 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2698 fnd_log.STRING(
2699 fnd_log.level_procedure
2700 , 'cn.plsql.cn_mark_events_pkg.mark_event_role_plans.end'
2701 , 'End of mark_event_role_plans.'
2702 );
2703 END IF;
2704 EXCEPTION
2705 WHEN OTHERS THEN
2706 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2707 fnd_log.STRING(
2708 fnd_log.level_unexpected
2709 , 'cn.plsql.cn_mark_events_pkg.mark_event_role_plans.exception'
2710 , SQLERRM
2711 );
2712 END IF;
2713
2714 RAISE;
2715 END mark_event_role_plans;
2716
2717 --
2718 -- Procedure Name
2719 -- mark_event_srp_paygroup
2720 -- Purpose
2721 -- Insert affected salesrep information into cn_event_log and cn_notify_log files
2722 -- for recalculation purpose. Called from cn_paygroup_pub
2723 -- History
2724 -- 01/24/03 clku created
2725 PROCEDURE mark_event_srp_pay_group(
2726 p_event_name VARCHAR2
2727 , p_object_name VARCHAR2
2728 , p_object_id NUMBER
2729 , p_srp_object_id NUMBER
2730 , p_start_date DATE
2731 , p_start_date_old DATE
2732 , p_end_date DATE
2733 , p_end_date_old DATE
2734 , p_org_id NUMBER
2735 ) IS
2736 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2737 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
2738 l_event_log_id NUMBER;
2739 l_start_period_id NUMBER(15);
2740 l_end_period_id NUMBER(15);
2741 l_date_range_tbl cn_api.date_range_tbl_type;
2742
2743 CURSOR affected_srp_period(l_s_date DATE, l_e_date DATE) IS
2744 -- for CHANGE_SRP_ROLE_PLAN
2745 -- use the start_date/end_date info to restrict the periods affected.
2746 -- clku, perf fix for bug 3628870, removed hints to avoid FTS
2747 SELECT intel.salesrep_id salesrep_id
2748 , acc.period_id period_id
2749 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2750 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2751 end_date
2752 FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2753 WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2754 AND acc.org_id = p_org_id
2755 AND acc.period_status = 'O'
2756 AND intel.salesrep_id = l_salesrep_id
2757 AND intel.period_id = acc.period_id
2758 AND intel.org_id = acc.org_id
2759 AND intel.processing_status_code <> 'CLEAN';
2760 BEGIN
2761 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2762 RETURN;
2763 END IF;
2764
2765 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2766 fnd_log.STRING(
2767 fnd_log.level_procedure
2768 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_group.begin'
2769 , 'Beginning of mark_event_spr_pay_group ...'
2770 );
2771 END IF;
2772
2773 l_pay_group_id := p_object_id;
2774 l_salesrep_id := p_srp_object_id;
2775 cn_mark_events_pkg.log_event(
2776 p_event_name
2777 , p_object_name
2778 , p_object_id
2779 , p_start_date
2780 , p_start_date_old
2781 , p_end_date
2782 , p_end_date_old
2783 , l_event_log_id
2784 , p_org_id
2785 );
2786
2787 IF p_event_name = 'CHANGE_SRP_PAY_GROUP' THEN
2788 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2789 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2790
2791 FOR l_rec IN affected_srp_period(p_start_date_old, p_end_date_old) LOOP
2792 -- For ROLL events, pass in start_date/end_date, pass null to p_quota_id
2793 cn_mark_events_pkg.mark_notify(
2794 l_rec.salesrep_id
2795 , l_rec.period_id
2796 , l_rec.start_date
2797 , l_rec.end_date
2798 , NULL
2799 , -- p_quota_id
2800 'ROLL'
2801 , l_event_log_id
2802 , p_org_id
2803 );
2804 END LOOP;
2805 ELSIF p_event_name = 'CHANGE_SRP_PAY_GROUP_DATE' THEN
2806 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
2807 , l_date_range_tbl);
2808
2809 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
2810 l_start_period_id :=
2811 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date - 1, p_org_id);
2812 l_end_period_id := cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
2813
2814 FOR l_rec IN affected_srp_period(l_date_range_tbl(l_ctr).start_date - 1
2815 , l_date_range_tbl(l_ctr).end_date) LOOP
2816 -- ROLL events : pass in start_date/end_date, pass null to p_quota_id
2817 cn_mark_events_pkg.mark_notify(
2818 l_rec.salesrep_id
2819 , l_rec.period_id
2820 , l_rec.start_date
2821 , l_rec.end_date
2822 , NULL
2823 , -- p_quota_id
2827 );
2824 'ROLL'
2825 , l_event_log_id
2826 , p_org_id
2828 END LOOP;
2829 END LOOP;
2830 END IF;
2831
2832 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2833 fnd_log.STRING(
2834 fnd_log.level_procedure
2835 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_group.end'
2836 , 'End of mark_event_srp_pay_group.'
2837 );
2838 END IF;
2839 EXCEPTION
2840 WHEN OTHERS THEN
2841 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2842 fnd_log.STRING(
2843 fnd_log.level_unexpected
2844 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_group.exception'
2845 , SQLERRM
2846 );
2847 END IF;
2848
2849 RAISE;
2850 END mark_event_srp_pay_group;
2851
2852 --
2853 -- Procedure Name
2854 -- mark_event_srp_roles
2855 -- Purpose
2856 -- Insert affected salesrep information into cn_event_log and cn_notify_log
2857 -- for recalculation purpose.
2858 -- Calls log_event, mark_notify.
2859 -- Called by cn_srp_rolens_t trigger.
2860 -- History
2861 -- 09/20/99 Harlen Chen Created
2862 PROCEDURE mark_event_srp_roles(
2863 p_event_name VARCHAR2
2864 , p_object_name VARCHAR2
2865 , p_object_id NUMBER
2866 , p_start_date DATE
2867 , p_start_date_old DATE
2868 , p_end_date DATE
2869 , p_end_date_old DATE
2870 , p_org_id NUMBER
2871 ) IS
2872 l_salesrep_id cn_srp_roles.salesrep_id%TYPE;
2873 l_event_log_id NUMBER;
2874 l_start_period_id NUMBER(15);
2875 l_end_period_id NUMBER(15);
2876 l_date_range_tbl cn_api.date_range_tbl_type;
2877
2878 CURSOR affected_srp_period(l_s_date DATE, l_e_date DATE) IS
2879 -- for CHANGE_SRP_ROLE_PLAN
2880 -- use the start_date/end_date info to restrict the periods affected.
2881 SELECT l_salesrep_id salesrep_id
2882 , acc.period_id period_id
2883 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2884 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2885 end_date
2886 FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2887 WHERE acc.org_id = p_org_id
2888 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2889 AND acc.period_status = 'O'
2890 AND intel.salesrep_id = l_salesrep_id
2891 AND intel.period_id = acc.period_id
2892 AND intel.org_id = acc.org_id
2893 AND intel.processing_status_code <> 'CLEAN';
2894 BEGIN
2895 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2896 RETURN;
2897 END IF;
2898
2899 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2900 fnd_log.STRING(
2901 fnd_log.level_procedure
2902 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_roles.begin'
2903 , 'Beginning of mark_event_srp_roles ...'
2904 );
2905 END IF;
2906
2907 l_salesrep_id := p_object_id;
2908 cn_mark_events_pkg.log_event(
2909 p_event_name
2910 , p_object_name
2911 , p_object_id
2912 , p_start_date
2913 , p_start_date_old
2914 , p_end_date
2915 , p_end_date_old
2916 , l_event_log_id
2917 , p_org_id
2918 );
2919
2920 IF p_event_name = 'CHANGE_SRP_ROLE_PLAN' THEN
2921 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2922 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2923
2924 FOR l_rec IN affected_srp_period(p_start_date_old, p_end_date_old) LOOP
2925 -- For ROLL events, pass in start_date/end_date, pass null to p_quota_id
2926
2927 -- modified by rjin 11/10/1999
2928 -- since all affected period (including subsequent periods)
2929 -- are garaunteed to be marked, so we only need to mark 'NEW'
2930 mark_notify(
2931 p_salesrep_id => l_rec.salesrep_id
2932 , p_period_id => l_rec.period_id
2933 , p_start_date => l_rec.start_date
2934 , p_end_date => l_rec.end_date
2935 , p_quota_id => NULL
2936 , p_revert_to_state => 'ROLL'
2937 , p_event_log_id => l_event_log_id
2938 , p_mode => 'NEW'
2939 , p_org_id => p_org_id
2940 );
2941 END LOOP;
2942 ELSIF p_event_name = 'CHANGE_SRP_ROLE_PLAN_DATE' THEN
2943 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
2944 , l_date_range_tbl);
2945
2946 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
2947 l_start_period_id :=
2948 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date, p_org_id);
2949 l_end_period_id := cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
2950
2951 FOR l_rec IN affected_srp_period(l_date_range_tbl(l_ctr).start_date
2952 , l_date_range_tbl(l_ctr).end_date) LOOP
2953 -- ROLL events : pass in start_date/end_date, pass null to p_quota_id
2957 , l_rec.start_date
2954 cn_mark_events_pkg.mark_notify(
2955 l_rec.salesrep_id
2956 , l_rec.period_id
2958 , l_rec.end_date
2959 , NULL
2960 , -- p_quota_id
2961 'ROLL'
2962 , l_event_log_id
2963 , p_org_id
2964 );
2965 END LOOP;
2966 END LOOP;
2967 END IF;
2968
2969 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2970 fnd_log.STRING(
2971 fnd_log.level_procedure
2972 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_roles.end'
2973 , 'End of mark_event_srp_roles.'
2974 );
2975 END IF;
2976 EXCEPTION
2977 WHEN OTHERS THEN
2978 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2979 fnd_log.STRING(
2980 fnd_log.level_unexpected
2981 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_roles.exception'
2982 , SQLERRM
2983 );
2984 END IF;
2985
2986 RAISE;
2987 END mark_event_srp_roles;
2988
2989 --
2990 --Start Of Comments
2991 --Purpose
2992 --This procedure marks all Sales Reps for Calculation
2993 --whenever there is a change in Formula_Status is modified
2994 -- Called from cn_calc_formulas_t1 Trigger
2995 -- This trigger fires when formula_status is updated
2996 -- COMPLETE.
2997 -- Event Fired is CHANGE_FORMULA
2998 -- History
2999 -- 09/19/99 ( Venkata) chalam Krishnan Created
3000 --End of Comments
3001 PROCEDURE mark_event_formula(
3002 p_event_name VARCHAR2
3003 , p_object_name VARCHAR2
3004 , p_object_id NUMBER
3005 , p_start_date DATE
3006 , p_start_date_old DATE
3007 , p_end_date DATE
3008 , p_end_date_old DATE
3009 , p_org_id NUMBER
3010 ) IS
3011 CURSOR affected_srp_period_quotas IS
3012 -- modified by rjin 11/10/1999 add distinct
3013 SELECT DISTINCT spq.salesrep_id
3014 , spq.period_id
3015 , spq.quota_id
3016 FROM cn_quotas_all cq, cn_srp_period_quotas_all spq
3017 , cn_srp_intel_periods_all intel
3018 WHERE cq.calc_formula_id = p_object_id
3019 AND cq.org_id = p_org_id
3020 AND spq.quota_id = cq.quota_id
3021 AND intel.salesrep_id = spq.salesrep_id
3022 AND intel.period_id = spq.period_id
3023 AND intel.org_id = spq.org_id
3024 AND intel.processing_status_code <> 'CLEAN'
3025 ORDER BY spq.quota_id;
3026
3027 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3028 SELECT quota_id
3029 FROM cn_srp_period_quotas_all
3030 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3031
3032 temp_quota_id cn_quotas.quota_id%TYPE;
3033 l_return_status VARCHAR2(50);
3034 l_msg_count NUMBER;
3035 l_msg_data VARCHAR2(2000);
3036 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3037 l_latest_quota_id NUMBER := 0;
3038 l_event_log_id NUMBER;
3039 BEGIN
3040 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3041 RETURN;
3042 END IF;
3043
3044 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3045 fnd_log.STRING(
3046 fnd_log.level_procedure
3047 , 'cn.plsql.cn_mark_events_pkg.mark_event_formula.begin'
3048 , 'Beginning of mark_event_formula ...'
3049 );
3050 END IF;
3051
3052 cn_mark_events_pkg.log_event(
3053 p_event_name => p_event_name
3054 , p_object_name => p_object_name
3055 , p_object_id => p_object_id
3056 , p_start_date => p_start_date
3057 , p_start_date_old => p_start_date_old
3058 , p_end_date => p_end_date
3059 , p_end_date_old => p_end_date_old
3060 , x_event_log_id => l_event_log_id
3061 , p_org_id => p_org_id
3062 );
3063
3064 IF (p_event_name = 'CHANGE_FORMULA') THEN
3065 FOR srp_quota IN affected_srp_period_quotas LOOP
3066 IF l_latest_quota_id <> srp_quota.quota_id THEN
3067 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3068 p_api_version => 1.0
3069 , p_node_type => 'P'
3070 , p_init_msg_list => 'T'
3071 , p_node_id => srp_quota.quota_id
3072 , x_plan_elt_id_tbl => dependent_pe_tbl
3073 , x_return_status => l_return_status
3074 , x_msg_count => l_msg_count
3075 , x_msg_data => l_msg_data
3076 );
3077 l_latest_quota_id := srp_quota.quota_id;
3078 END IF;
3079
3080 -- modified by rjin 11/10/1999
3081 -- since all affected period (including subsequent periods)
3082 -- are garaunteed to be marked, so we only need to mark 'NEW'
3083 mark_notify(
3084 p_salesrep_id => srp_quota.salesrep_id
3085 , p_period_id => srp_quota.period_id
3089 , p_revert_to_state => 'CALC'
3086 , p_start_date => NULL
3087 , p_end_date => NULL
3088 , p_quota_id => srp_quota.quota_id
3090 , p_event_log_id => l_event_log_id
3091 , p_mode => 'NEW'
3092 , p_org_id => p_org_id
3093 );
3094
3095 IF (dependent_pe_tbl.COUNT > 0) THEN
3096 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3097 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3098 FETCH l_pe_cursor INTO temp_quota_id;
3099
3100 IF l_pe_cursor%FOUND THEN
3101 cn_mark_events_pkg.mark_notify(
3102 p_salesrep_id => srp_quota.salesrep_id
3103 , p_period_id => srp_quota.period_id
3104 , p_start_date => NULL
3105 , p_end_date => NULL
3106 , p_quota_id => dependent_pe_tbl(i)
3107 , p_revert_to_state => 'CALC'
3108 , p_event_log_id => l_event_log_id
3109 , p_mode => 'NEW'
3110 , p_org_id => p_org_id
3111 );
3112 END IF;
3113
3114 CLOSE l_pe_cursor;
3115 END LOOP;
3116 END IF; -- If (dependent_pe_tbl.count > 0)
3117 END LOOP;
3118 END IF;
3119
3120 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3121 fnd_log.STRING(
3122 fnd_log.level_procedure
3123 , 'cn.plsql.cn_mark_events_pkg.mark_event_formula.end'
3124 , 'End of mark_event_formula.'
3125 );
3126 END IF;
3127 EXCEPTION
3128 WHEN OTHERS THEN
3129 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3130 fnd_log.STRING(
3131 fnd_log.level_unexpected
3132 , 'cn.plsql.cn_mark_events_pkg.mark_event_formula.exception'
3133 , SQLERRM
3134 );
3135 END IF;
3136
3137 RAISE;
3138 END mark_event_formula;
3139
3140 --
3141 --Start Of Comments
3142 --Purpose
3143 --This procedure marks all Sales Reps for Calculation
3144 --whenever there is a change in Rate Dim Tiers
3145 --1. Insert Rate Dim Tiers
3146 -- Event Fired is CHANGE_RT_INS_DEL
3147 --2. Update Rate Dim Tiers
3148 -- Event Fired is CHANGE_RT_TIER
3149 --3. Delete Rate Dim Tiers
3150 -- Event fired is CHANGE_RT_INS_DEL
3151 --History
3152 --09/19/99 ( Venkata ) chalam Krishnan Created
3153 --End of Comments
3154 PROCEDURE mark_event_rate_table(
3155 p_event_name VARCHAR2
3156 , p_object_name VARCHAR2
3157 , p_object_id NUMBER
3158 , p_start_date DATE
3159 , p_start_date_old DATE
3160 , p_end_date DATE
3161 , p_end_date_old DATE
3162 , p_org_id NUMBER
3163 ) IS
3164 CURSOR affected_srp_period_quotas IS
3165 SELECT DISTINCT spq.salesrep_id
3166 , spq.period_id
3167 , spq.quota_id
3168 FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3169 WHERE spq.quota_id IN(
3170 SELECT rt_assign.quota_id
3171 FROM cn_rate_sch_dims_all rt, cn_rt_quota_asgns_all rt_assign
3172 WHERE rt.rate_dimension_id = p_object_id
3173 AND rt_assign.rate_schedule_id = rt.rate_schedule_id)
3174 AND intel.salesrep_id = spq.salesrep_id
3175 AND intel.period_id = spq.period_id
3176 AND intel.org_id = spq.org_id
3177 AND intel.processing_status_code <> 'CLEAN'
3178 ORDER BY spq.quota_id;
3179
3180 l_event_log_id NUMBER;
3181
3182 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3183 SELECT quota_id
3184 FROM cn_srp_period_quotas_all
3185 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3186
3187 temp_quota_id cn_quotas.quota_id%TYPE;
3188 l_return_status VARCHAR2(50);
3189 l_msg_count NUMBER;
3190 l_msg_data VARCHAR2(2000);
3191 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3192 l_latest_quota_id NUMBER := 0;
3193 BEGIN
3194 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3195 RETURN;
3196 END IF;
3197
3198 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3199 fnd_log.STRING(
3200 fnd_log.level_procedure
3201 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_table.begin'
3202 , 'Beginning of mark_event_rate_table ...'
3203 );
3204 END IF;
3205
3206 cn_mark_events_pkg.log_event(
3207 p_event_name => p_event_name
3208 , p_object_name => p_object_name
3209 , p_object_id => p_object_id
3210 , p_start_date => p_start_date
3211 , p_start_date_old => p_start_date_old
3212 , p_end_date => p_end_date
3213 , p_end_date_old => p_end_date_old
3217
3214 , x_event_log_id => l_event_log_id
3215 , p_org_id => p_org_id
3216 );
3218 IF (p_event_name IN('CHANGE_RT_TIER', 'CHANGE_RT_TIER_INS_DEL')) THEN
3219 FOR srp_quota IN affected_srp_period_quotas LOOP
3220 IF l_latest_quota_id <> srp_quota.quota_id THEN
3221 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3222 p_api_version => 1.0
3223 , p_node_type => 'P'
3224 , p_init_msg_list => 'T'
3225 , p_node_id => srp_quota.quota_id
3226 , x_plan_elt_id_tbl => dependent_pe_tbl
3227 , x_return_status => l_return_status
3228 , x_msg_count => l_msg_count
3229 , x_msg_data => l_msg_data
3230 );
3231 l_latest_quota_id := srp_quota.quota_id;
3232 END IF;
3233
3234 -- modified by rjin 11/10/1999
3235 -- since all affected period (including subsequent periods)
3236 -- are garaunteed to be marked, so we only need to mark 'NEW'
3237 mark_notify(
3238 p_salesrep_id => srp_quota.salesrep_id
3239 , p_period_id => srp_quota.period_id
3240 , p_start_date => NULL
3241 , p_end_date => NULL
3242 , p_quota_id => srp_quota.quota_id
3243 , p_revert_to_state => 'CALC'
3244 , p_event_log_id => l_event_log_id
3245 , p_mode => 'NEW'
3246 , p_org_id => p_org_id
3247 );
3248
3249 IF (dependent_pe_tbl.COUNT > 0) THEN
3250 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3251 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3252 FETCH l_pe_cursor INTO temp_quota_id;
3253
3254 IF l_pe_cursor%FOUND THEN
3255 cn_mark_events_pkg.mark_notify(
3256 p_salesrep_id => srp_quota.salesrep_id
3257 , p_period_id => srp_quota.period_id
3258 , p_start_date => NULL
3259 , p_end_date => NULL
3260 , p_quota_id => dependent_pe_tbl(i)
3261 , p_revert_to_state => 'CALC'
3262 , p_event_log_id => l_event_log_id
3263 , p_mode => 'NEW'
3264 , p_org_id => p_org_id
3265 );
3266 END IF;
3267
3268 CLOSE l_pe_cursor;
3269 END LOOP;
3270 END IF; -- If (dependent_pe_tbl.count > 0)
3271 END LOOP;
3272 END IF;
3273
3274 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3275 fnd_log.STRING(
3276 fnd_log.level_procedure
3277 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_table.end'
3278 , 'End of mark_event_rate_table.'
3279 );
3280 END IF;
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3284 fnd_log.STRING(
3285 fnd_log.level_unexpected
3286 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_table.exception'
3287 , SQLERRM
3288 );
3289 END IF;
3290
3291 RAISE;
3292 END mark_event_rate_table;
3293
3294 --
3295 -- Start Of Comments
3296 -- Purpose:
3297 -- This procedure marks all Sales Reps for Calculation
3298 -- whenever there is a change in Rate Tiers (Commission Rates).
3299 --
3300 -- 1. Insert Rate Dim Tiers
3301 -- Event Fired is CHANGE_RT_INS_DEL
3302 -- 2. Update Rate Dim Tiers
3303 -- Event Fired is CHANGE_RT_TIER
3304 -- 3. Delete Rate Dim Tiers
3305 -- Event fired is CHANGE_RT_INS_DEL
3306 --
3307 -- History
3308 -- 29/08/08 (venjayar) jVenki Created
3309 --
3310 -- End of Comments
3311 PROCEDURE mark_event_rate_tier_table(
3312 p_event_name VARCHAR2
3313 , p_object_name VARCHAR2
3314 , p_object_id NUMBER
3315 , p_dep_object_id NUMBER
3316 , p_start_date DATE
3317 , p_start_date_old DATE
3318 , p_end_date DATE
3319 , p_end_date_old DATE
3320 , p_org_id NUMBER
3321 ) IS
3322 CURSOR affected_srp_period_quotas IS
3323 SELECT DISTINCT spq.salesrep_id
3324 , spq.period_id
3325 , spq.quota_id
3326 FROM cn_srp_period_quotas spq, cn_srp_intel_periods intel
3327 WHERE spq.quota_id IN(
3328 SELECT rt_assign.quota_id
3329 FROM cn_rt_quota_asgns rt_assign
3330 WHERE rt_assign.rate_schedule_id = p_dep_object_id)
3331 AND intel.salesrep_id = spq.salesrep_id
3332 AND intel.period_id = spq.period_id
3333 AND intel.processing_status_code <> 'CLEAN'
3334 ORDER BY spq.quota_id;
3335
3336 l_event_log_id NUMBER;
3337
3338 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3339 SELECT quota_id
3340 FROM cn_srp_period_quotas
3341 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3342
3346 l_msg_data VARCHAR2(2000);
3343 temp_quota_id cn_quotas.quota_id%TYPE;
3344 l_return_status VARCHAR2(50);
3345 l_msg_count NUMBER;
3347 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3348 l_latest_quota_id NUMBER := 0;
3349 BEGIN
3350 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3351 RETURN;
3352 END IF;
3353
3354 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3355 fnd_log.STRING(
3356 fnd_log.level_procedure
3357 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_tier_table.begin'
3358 , 'Beginning of mark_event_rate_tier_table...'
3359 );
3360 END IF;
3361
3362 cn_mark_events_pkg.log_event(
3363 p_event_name => p_event_name
3364 , p_object_name => p_object_name
3365 , p_object_id => p_object_id
3366 , p_start_date => p_start_date
3367 , p_start_date_old => p_start_date_old
3368 , p_end_date => p_end_date
3369 , p_end_date_old => p_end_date_old
3370 , x_event_log_id => l_event_log_id
3371 , p_org_id => p_org_id
3372 );
3373
3374 IF (p_event_name IN('CHANGE_RT_TIER', 'CHANGE_RT_TIER_INS_DEL')) THEN
3375 FOR srp_quota IN affected_srp_period_quotas LOOP
3376 IF l_latest_quota_id <> srp_quota.quota_id THEN
3377 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3378 p_api_version => 1.0
3379 , p_node_type => 'P'
3380 , p_init_msg_list => 'T'
3381 , p_node_id => srp_quota.quota_id
3382 , x_plan_elt_id_tbl => dependent_pe_tbl
3383 , x_return_status => l_return_status
3384 , x_msg_count => l_msg_count
3385 , x_msg_data => l_msg_data
3386 );
3387 l_latest_quota_id := srp_quota.quota_id;
3388 END IF;
3389
3390 -- since all affected period (including subsequent periods)
3391 -- are garaunteed to be marked, so we only need to mark 'NEW'
3392 mark_notify(
3393 p_salesrep_id => srp_quota.salesrep_id
3394 , p_period_id => srp_quota.period_id
3395 , p_start_date => NULL
3396 , p_end_date => NULL
3397 , p_quota_id => srp_quota.quota_id
3398 , p_revert_to_state => 'CALC'
3399 , p_event_log_id => l_event_log_id
3400 , p_mode => 'NEW'
3401 , p_org_id => p_org_id
3402 );
3403
3404 IF (dependent_pe_tbl.COUNT > 0) THEN
3405 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3406 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3407 FETCH l_pe_cursor INTO temp_quota_id;
3408
3409 IF l_pe_cursor%FOUND THEN
3410 cn_mark_events_pkg.mark_notify(
3411 p_salesrep_id => srp_quota.salesrep_id
3412 , p_period_id => srp_quota.period_id
3413 , p_start_date => NULL
3414 , p_end_date => NULL
3415 , p_quota_id => dependent_pe_tbl(i)
3416 , p_revert_to_state => 'CALC'
3417 , p_event_log_id => l_event_log_id
3418 , p_mode => 'NEW'
3419 , p_org_id => p_org_id
3420 );
3421 END IF;
3422
3423 CLOSE l_pe_cursor;
3424 END LOOP;
3425 END IF; -- If (dependent_pe_tbl.count > 0)
3426 END LOOP;
3427 END IF;
3428
3429 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3430 fnd_log.STRING(
3431 fnd_log.level_procedure
3432 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_tier_table.end'
3433 , 'End of mark_event_rate_tier_table.'
3434 );
3435 END IF;
3436 EXCEPTION
3437 WHEN OTHERS THEN
3438 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3439 fnd_log.STRING(
3440 fnd_log.level_unexpected
3441 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_tier_table.exception'
3442 , SQLERRM
3443 );
3444 END IF;
3445
3446 RAISE;
3447 END mark_event_rate_tier_table;
3448
3449
3450 -- Purpose
3451 -- the auxiliary procedure for mark_event_interval_number
3452 -- History
3453 -- created on 9/27/1999 by ymao
3454 PROCEDURE mark_notify_interval_number(
3455 p_event_name VARCHAR2
3456 , p_interval_type_id NUMBER
3457 , p_period_id NUMBER
3458 , p_start_date DATE
3459 , p_end_date DATE
3460 , p_event_log_id NUMBER
3461 , p_org_id NUMBER
3462 ) IS
3463 CURSOR affected_srp_period_quotas IS
3464 SELECT DISTINCT spq.salesrep_id
3465 , spq.period_id
3466 , spq.quota_id
3467 FROM cn_quotas_all q, cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3471 AND (q.end_date IS NULL OR p_start_date <= q.end_date)
3468 WHERE q.interval_type_id = p_interval_type_id
3469 AND q.org_id = p_org_id
3470 AND p_end_date >= q.start_date
3472 AND spq.quota_id = q.quota_id
3473 AND spq.period_id = p_period_id
3474 AND intel.salesrep_id = spq.salesrep_id
3475 AND intel.period_id = p_period_id
3476 AND intel.org_id = spq.org_id
3477 AND intel.processing_status_code <> 'CLEAN'
3478 ORDER BY spq.quota_id;
3479
3480 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3481 SELECT quota_id
3482 FROM cn_srp_period_quotas_all
3483 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3484
3485 temp_quota_id cn_quotas.quota_id%TYPE;
3486 l_return_status VARCHAR2(50);
3487 l_msg_count NUMBER;
3488 l_msg_data VARCHAR2(2000);
3489 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3490 l_latest_quota_id NUMBER := 0;
3491 BEGIN
3492 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3493 fnd_log.STRING(
3494 fnd_log.level_procedure
3495 , 'cn.plsql.cn_mark_events_pkg.mark_notify_interval_number.begin'
3496 , 'Beginning of mark_notify_interval_number ...'
3497 );
3498 END IF;
3499
3500 IF (p_event_name = 'CHANGE_PERIOD_INTERVAL_NUMBER') THEN
3501 FOR srp_quota IN affected_srp_period_quotas LOOP
3502 IF l_latest_quota_id <> srp_quota.quota_id THEN
3503 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3504 p_api_version => 1.0
3505 , p_node_type => 'P'
3506 , p_init_msg_list => 'T'
3507 , p_node_id => srp_quota.quota_id
3508 , x_plan_elt_id_tbl => dependent_pe_tbl
3509 , x_return_status => l_return_status
3510 , x_msg_count => l_msg_count
3511 , x_msg_data => l_msg_data
3512 );
3513 l_latest_quota_id := srp_quota.quota_id;
3514 END IF;
3515
3516 -- modified by rjin 11/10/1999
3517 -- since all affected period (including subsequent periods)
3518 -- are garaunteed to be marked, so we only need to mark 'NEW'
3519 mark_notify(
3520 p_salesrep_id => srp_quota.salesrep_id
3521 , p_period_id => srp_quota.period_id
3522 , p_start_date => p_start_date
3523 , --NULL,
3524 p_end_date => p_end_date
3525 , --NULL,
3526 p_quota_id => srp_quota.quota_id
3527 , p_revert_to_state => 'CALC'
3528 , p_event_log_id => p_event_log_id
3529 , p_mode => 'NEW'
3530 , p_org_id => p_org_id
3531 );
3532
3533 IF (dependent_pe_tbl.COUNT > 0) THEN
3534 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3535 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3536 FETCH l_pe_cursor INTO temp_quota_id;
3537
3538 IF l_pe_cursor%FOUND THEN
3539 cn_mark_events_pkg.mark_notify(
3540 p_salesrep_id => srp_quota.salesrep_id
3541 , p_period_id => srp_quota.period_id
3542 , p_start_date => p_start_date
3543 , p_end_date => p_end_date
3544 , p_quota_id => dependent_pe_tbl(i)
3545 , p_revert_to_state => 'CALC'
3546 , p_event_log_id => p_event_log_id
3547 , p_mode => 'NEW'
3548 , p_org_id => p_org_id
3549 );
3550 END IF;
3551
3552 CLOSE l_pe_cursor;
3553 END LOOP;
3554 END IF; -- If (dependent_pe_tbl.count > 0)
3555 END LOOP;
3556 END IF;
3557
3558 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3559 fnd_log.STRING(
3560 fnd_log.level_procedure
3561 , 'cn.plsql.cn_mark_events_pkg.mark_notify_interval_number.end'
3562 , 'End of mark_notify_interval_number.'
3563 );
3564 END IF;
3565 EXCEPTION
3566 WHEN OTHERS THEN
3567 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3568 fnd_log.STRING(
3569 fnd_log.level_unexpected
3570 , 'cn.plsql.cn_mark_events_pkg.mark_notify_interval_number.exception'
3571 , SQLERRM
3572 );
3573 END IF;
3574
3575 RAISE;
3576 END mark_notify_interval_number;
3577
3578 -- Purpose
3579 -- Upon the change of any interval number, mark all the sales reps that might be affected
3580 -- in terms of calculation
3581 -- History
3582 -- created on 9/27/99 by ymao
3583 PROCEDURE mark_event_interval_number(
3584 p_event_name VARCHAR2
3585 , p_object_name VARCHAR2
3589 , p_end_date DATE
3586 , p_object_id NUMBER
3587 , p_start_date DATE
3588 , p_start_date_old DATE
3590 , p_end_date_old DATE
3591 , p_interval_type_id NUMBER
3592 , p_old_interval_number NUMBER
3593 , p_new_interval_number NUMBER
3594 , p_org_id NUMBER
3595 ) IS
3596 l_event_log_id NUMBER(15);
3597
3598 CURSOR affected_periods IS
3599 SELECT cpit.cal_period_id
3600 , ps.start_date
3601 , ps.end_date
3602 FROM cn_cal_per_int_types_all cpit, cn_period_statuses_all ps
3603 WHERE (
3604 cpit.interval_number = p_old_interval_number
3605 OR cpit.interval_number = p_new_interval_number
3606 )
3607 AND cpit.interval_type_id = p_interval_type_id
3608 AND cpit.org_id = p_org_id
3609 AND ps.period_id = cpit.cal_period_id
3610 AND ps.org_id = cpit.org_id;
3611 BEGIN
3612 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3613 RETURN;
3614 END IF;
3615
3616 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3617 fnd_log.STRING(
3618 fnd_log.level_procedure
3619 , 'cn.plsql.cn_mark_events_pkg.mark_event_interval_number.begin'
3620 , 'Beginning of mark_event_interval_number ...'
3621 );
3622 END IF;
3623
3624 cn_mark_events_pkg.log_event(
3625 p_event_name
3626 , p_object_name
3627 , p_object_id
3628 , p_start_date
3629 , p_start_date_old
3630 , p_end_date
3631 , p_end_date_old
3632 , l_event_log_id
3633 , p_org_id
3634 );
3635
3636 -- get all the periods which are affected and call mark_event_interval_number for all of them
3637 FOR affected_period IN affected_periods LOOP
3638 mark_notify_interval_number(
3639 p_event_name
3640 , p_interval_type_id
3641 , affected_period.cal_period_id
3642 , affected_period.start_date
3643 , affected_period.end_date
3644 , l_event_log_id
3645 , p_org_id
3646 );
3647 END LOOP;
3648
3649 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3650 fnd_log.STRING(
3651 fnd_log.level_procedure
3652 , 'cn.plsql.cn_mark_events_pkg.mark_event_interval_number.end'
3653 , 'End of mark_event_interval_number.'
3654 );
3655 END IF;
3656 EXCEPTION
3657 WHEN OTHERS THEN
3658 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3659 fnd_log.STRING(
3660 fnd_log.level_unexpected
3661 , 'cn.plsql.cn_mark_events_pkg.mark_event_interval_number.exception'
3662 , SQLERRM
3663 );
3664 END IF;
3665
3666 RAISE;
3667 END mark_event_interval_number;
3668
3669 PROCEDURE mark_event_int_num_change(x_cal_per_int_type_id NUMBER, x_interval_number NUMBER) IS
3670 CURSOR c IS
3671 SELECT cal_period_id
3672 , interval_number
3673 , interval_type_id
3674 , org_id
3675 FROM cn_cal_per_int_types_all
3676 WHERE cal_per_int_type_id = x_cal_per_int_type_id
3677 FOR UPDATE OF cal_per_int_type_id NOWAIT;
3678
3679 rec c%ROWTYPE;
3680
3681 CURSOR NAME(p_org_id NUMBER) IS
3682 SELECT NAME
3683 FROM cn_interval_types_all_tl
3684 WHERE interval_type_id = rec.interval_type_id AND org_id = p_org_id;
3685
3686 l_object_name VARCHAR2(80);
3687
3688 CURSOR dates(p_org_id NUMBER) IS
3689 SELECT start_date
3690 , end_date
3691 FROM cn_period_statuses_all
3692 WHERE period_id = rec.cal_period_id AND org_id = p_org_id;
3693
3694 l_start_date DATE;
3695 l_end_date DATE;
3696 BEGIN
3697 OPEN c;
3698 FETCH c INTO rec;
3699 CLOSE c;
3700
3701 -- mark the "CHANGE_PERIOD_INTERVAL_NUMBER" event for intelligent calculation
3702 IF (rec.interval_number <> x_interval_number AND fnd_profile.VALUE('CN_MARK_EVENTS') = 'Y') THEN
3703 -- get the object name which is the name of the interval type here.
3704 OPEN NAME(rec.org_id);
3705 FETCH NAME INTO l_object_name;
3706 CLOSE NAME;
3707
3708 -- get the start_date and end_date of the corresponding period
3709 OPEN dates(rec.org_id);
3710 FETCH dates INTO l_start_date, l_end_date;
3711 CLOSE dates;
3712
3713 cn_mark_events_pkg.mark_event_interval_number(
3714 'CHANGE_PERIOD_INTERVAL_NUMBER'
3715 , l_object_name
3716 , rec.interval_type_id
3717 , NULL
3718 , l_start_date
3719 , NULL
3720 , l_end_date
3721 , rec.interval_type_id
3722 , rec.interval_number
3723 , x_interval_number
3724 , rec.org_id
3725 );
3726 END IF;
3727 END mark_event_int_num_change;
3728
3729 PROCEDURE mark_event_comp_plan(
3730 p_event_name VARCHAR2
3731 , p_object_name VARCHAR2
3732 , p_object_id NUMBER
3733 , p_start_date DATE
3734 , p_start_date_old DATE
3738 ) IS
3735 , p_end_date DATE
3736 , p_end_date_old DATE
3737 , p_org_id NUMBER
3739 l_event_log_id NUMBER;
3740 l_start_period_id NUMBER;
3741 l_end_period_id NUMBER;
3742
3743 -- 1. update cn_comp_plans
3744 -- 2. insert/delete cn_quota_assigns
3745 --
3746 -- bug 37709654, added hints suggested by perf team to reduce buffer gets
3747 CURSOR affected_srp_curs(l_start_period_id NUMBER, l_end_period_id NUMBER) IS
3748 SELECT /*+ LEADING(SPA) */
3749 DISTINCT spa.salesrep_id
3750 , acc.period_id
3751 , acc.start_date
3752 , acc.end_date
3753 FROM cn_srp_plan_assigns_all spa
3754 , cn_srp_intel_periods_all intel
3755 , cn_period_statuses_all acc
3756 WHERE spa.comp_plan_id = p_object_id -- comp_plan_id
3757 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
3758 AND acc.org_id = spa.org_id
3759 AND (
3760 (
3761 spa.start_date < acc.start_date
3762 AND (spa.end_date IS NULL OR acc.start_date <= spa.end_date)
3763 )
3764 OR (spa.start_date BETWEEN acc.start_date AND acc.end_date)
3765 )
3766 AND EXISTS(
3767 SELECT 1
3768 FROM cn_srp_period_quotas_all spq
3769 WHERE spa.srp_plan_assign_id = spq.srp_plan_assign_id
3770 AND spq.period_id = acc.period_id)
3771 AND intel.salesrep_id = spa.salesrep_id
3772 AND intel.period_id = acc.period_id
3773 AND intel.org_id = spa.org_id
3774 AND acc.period_status IN('O', 'F')
3775 AND intel.processing_status_code <> 'CLEAN';
3776 BEGIN
3777 --
3778 -- Log the Event for the comp plan events or any changes in the
3779 -- plan Assigns
3780 --
3781 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3782 RETURN;
3783 END IF;
3784
3785 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3786 fnd_log.STRING(
3787 fnd_log.level_procedure
3788 , 'cn.plsql.cn_mark_events_pkg.mark_event_comp_plan.begin'
3789 , 'Beginning of mark_event_comp_plan ...'
3790 );
3791 END IF;
3792
3793 cn_mark_events_pkg.log_event(
3794 p_event_name => p_event_name
3795 , p_object_name => p_object_name
3796 , p_object_id => p_object_id
3797 , p_start_date => p_start_date
3798 , p_start_date_old => p_start_date_old
3799 , p_end_date => p_end_date
3800 , p_end_date_old => p_end_date_old
3801 , x_event_log_id => l_event_log_id
3802 , p_org_id => p_org_id
3803 );
3804 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
3805 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
3806
3807 IF p_event_name = 'CHANGE_COMP_PLAN' OR p_event_name = 'CHANGE_COMP_PLAN_OVERLAP' THEN
3808 FOR affected_recs IN affected_srp_curs(l_start_period_id, l_end_period_id) LOOP
3809 cn_mark_events_pkg.mark_notify(
3810 p_salesrep_id => affected_recs.salesrep_id
3811 , p_period_id => affected_recs.period_id
3812 , p_start_date => affected_recs.start_date
3813 , p_end_date => affected_recs.end_date
3814 , p_quota_id => NULL
3815 , p_revert_to_state => 'ROLL'
3816 , p_event_log_id => l_event_log_id
3817 , p_mode => 'NEW'
3818 , p_org_id => p_org_id
3819 );
3820 END LOOP;
3821 END IF;
3822
3823 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3824 fnd_log.STRING(
3825 fnd_log.level_procedure
3826 , 'cn.plsql.cn_mark_events_pkg.mark_event_comp_plan.end'
3827 , 'End of mark_event_comp_plan.'
3828 );
3829 END IF;
3830 EXCEPTION
3831 WHEN OTHERS THEN
3832 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3833 fnd_log.STRING(
3834 fnd_log.level_unexpected
3835 , 'cn.plsql.cn_mark_events_pkg.mark_event_comp_plan.exception'
3836 , SQLERRM
3837 );
3838 END IF;
3839
3840 RAISE;
3841 END mark_event_comp_plan;
3842
3843 --
3844 -- Procedure Name
3845 -- mark_event_srp_quotas
3846 -- Purpose
3847 -- mark events when cn_srp_quota_assigns is updated
3848 -- History
3849 -- 09/20/99 Kai Chen Created
3850 PROCEDURE mark_event_srp_quotas(
3851 p_event_name VARCHAR2
3852 , p_object_name VARCHAR2
3853 , p_srp_object_id NUMBER
3854 , p_object_id NUMBER
3855 , p_start_date DATE
3856 , p_start_date_old DATE
3857 , p_end_date DATE
3858 , p_end_date_old DATE
3859 , p_org_id NUMBER
3860 ) IS
3861 -- x_srp_object_id --> p_srp_quota_assign_id
3865 SELECT spq.salesrep_id
3862 -- clku, perf fix 3628870, use cn_srp_period_quotas instead of cn_srp_period_quotas_v
3863 -- to remove MJC
3864 CURSOR affected_srp_period_quotas IS
3866 , spq.period_id
3867 , spq.quota_id
3868 FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods intel
3869 WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
3870 AND intel.salesrep_id = spq.salesrep_id
3871 AND intel.period_id = spq.period_id
3872 AND intel.org_id = spq.org_id
3873 -- scannane, bug 7154503, Notify log table update
3874 -- AND intel.processing_status_code <> 'CLEAN'
3875 ORDER BY spq.quota_id;
3876
3877 l_event_log_id NUMBER(15);
3878
3879 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3880 SELECT quota_id
3881 FROM cn_srp_period_quotas_all
3882 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3883
3884 temp_quota_id cn_quotas.quota_id%TYPE;
3885 l_return_status VARCHAR2(50);
3886 l_msg_count NUMBER;
3887 l_msg_data VARCHAR2(2000);
3888 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3889 l_latest_quota_id NUMBER := 0;
3890 BEGIN
3891 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3892 RETURN;
3893 END IF;
3894
3895 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3896 fnd_log.STRING(
3897 fnd_log.level_procedure
3898 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_quotas.begin'
3899 , 'Beginning of mark_event_srp_quotas ...'
3900 );
3901 END IF;
3902
3903 cn_mark_events_pkg.log_event(
3904 p_event_name
3905 , p_object_name
3906 , p_object_id
3907 , p_start_date
3908 , p_start_date_old
3909 , p_end_date
3910 , p_end_date_old
3911 , l_event_log_id
3912 , p_org_id
3913 );
3914
3915 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
3916 FOR srp_quota IN affected_srp_period_quotas LOOP
3917 IF l_latest_quota_id <> srp_quota.quota_id THEN
3918 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3919 p_api_version => 1.0
3920 , p_node_type => 'P'
3921 , p_init_msg_list => 'T'
3922 , p_node_id => srp_quota.quota_id
3923 , x_plan_elt_id_tbl => dependent_pe_tbl
3924 , x_return_status => l_return_status
3925 , x_msg_count => l_msg_count
3926 , x_msg_data => l_msg_data
3927 );
3928 l_latest_quota_id := srp_quota.quota_id;
3929 END IF;
3930
3931 -- modified by rjin 11/10/1999
3932 -- since all affected period (including subsequent periods)
3933 -- are garaunteed to be marked, so we only need to mark 'NEW'
3934 cn_mark_events_pkg.mark_notify(
3935 p_salesrep_id => srp_quota.salesrep_id
3936 , p_period_id => srp_quota.period_id
3937 , p_start_date => NULL
3938 , p_end_date => NULL
3939 , p_quota_id => srp_quota.quota_id
3940 , p_revert_to_state => 'CALC'
3941 , p_event_log_id => l_event_log_id
3942 , p_mode => 'NEW'
3943 , p_org_id => p_org_id
3944 );
3945
3946 IF (dependent_pe_tbl.COUNT > 0) THEN
3947 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3948 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3949 FETCH l_pe_cursor INTO temp_quota_id;
3950
3951 IF l_pe_cursor%FOUND THEN
3952 cn_mark_events_pkg.mark_notify(
3953 p_salesrep_id => srp_quota.salesrep_id
3954 , p_period_id => srp_quota.period_id
3955 , p_start_date => NULL
3956 , p_end_date => NULL
3957 , p_quota_id => dependent_pe_tbl(i)
3958 , p_revert_to_state => 'CALC'
3959 , p_event_log_id => l_event_log_id
3960 , p_mode => 'NEW'
3961 , p_org_id => p_org_id
3962 );
3963 END IF;
3964
3965 CLOSE l_pe_cursor;
3966 END LOOP;
3967 END IF; -- If (dependent_pe_tbl.count > 0)
3968 END LOOP;
3969 END IF;
3970
3971 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3972 fnd_log.STRING(
3973 fnd_log.level_procedure
3974 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_quotas.end'
3975 , 'End of mark_event_srp_quotas.'
3976 );
3977 END IF;
3978 EXCEPTION
3979 WHEN OTHERS THEN
3980 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3981 fnd_log.STRING(
3982 fnd_log.level_unexpected
3983 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_quotas.exception'
3984 , SQLERRM
3985 );
3989 END mark_event_srp_quotas;
3986 END IF;
3987
3988 RAISE;
3990
3991 -- Procedure Name
3992 -- mark_event_srp_uplifts
3993 -- Purpose
3994 -- mark events when cn_srp_rule_uplifts is updated
3995 -- History
3996 -- 09/20/99 Kai Chen Created
3997 PROCEDURE mark_event_srp_uplifts(
3998 p_event_name VARCHAR2
3999 , p_object_name VARCHAR2
4000 , p_srp_object_id NUMBER
4001 , p_object_id NUMBER
4002 , p_start_date DATE
4003 , p_start_date_old DATE
4004 , p_end_date DATE
4005 , p_end_date_old DATE
4006 , p_org_id NUMBER
4007 ) IS
4008 -- x_srp_object_id --> p_srp_quota_rule_id
4009 CURSOR affected_srp_period_quotas(
4010 l_start_period_id NUMBER
4011 , l_end_period_id NUMBER
4012 , l_s_date DATE
4013 , l_e_date DATE
4014 ) IS
4015 SELECT spq.salesrep_id
4016 , spq.period_id
4017 , spq.quota_id
4018 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
4019 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
4020 end_date
4021 FROM cn_srp_quota_rules_all rule
4022 , cn_srp_period_quotas_all spq
4023 , cn_period_statuses_all acc
4024 , cn_srp_intel_periods_all intel
4025 WHERE rule.srp_quota_rule_id = p_srp_object_id --p_srp_quota_rule_id
4026 AND spq.srp_plan_assign_id = rule.srp_plan_assign_id
4027 AND spq.srp_quota_assign_id = rule.srp_quota_assign_id
4028 AND acc.period_id = spq.period_id
4029 AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
4030 AND acc.period_status = 'O'
4031 AND acc.org_id = spq.org_id
4032 AND intel.salesrep_id = spq.salesrep_id
4033 AND intel.period_id = spq.period_id
4034 AND intel.org_id = spq.org_id
4035 AND intel.processing_status_code <> 'CLEAN'
4036 ORDER BY spq.quota_id;
4037
4038 l_event_log_id NUMBER(15);
4039 l_temp_start_date DATE;
4040 l_temp_end_date DATE;
4041 l_temp_start_period_id NUMBER(15);
4042 l_temp_end_period_id NUMBER(15);
4043
4044 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4045 SELECT quota_id
4046 FROM cn_srp_period_quotas_all
4047 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4048
4049 temp_quota_id cn_quotas.quota_id%TYPE;
4050 l_return_status VARCHAR2(50);
4051 l_msg_count NUMBER;
4052 l_msg_data VARCHAR2(2000);
4053 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4054 l_latest_quota_id NUMBER := 0;
4055 BEGIN
4056 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4057 RETURN;
4058 END IF;
4059
4060 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4061 fnd_log.STRING(
4062 fnd_log.level_procedure
4063 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_uplifts.begin'
4064 , 'Beginning of mark_event_srp_uplifts ...'
4065 );
4066 END IF;
4067
4068 cn_mark_events_pkg.log_event(
4069 p_event_name
4070 , p_object_name
4071 , p_object_id
4072 , p_start_date
4073 , p_start_date_old
4074 , p_end_date
4075 , p_end_date_old
4076 , l_event_log_id
4077 , p_org_id
4078 );
4079 l_temp_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
4080 l_temp_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
4081
4082 IF p_event_name = 'CHANGE_SRP_QUOTA_POP' THEN
4083 FOR srp_quota IN affected_srp_period_quotas(
4084 l_temp_start_period_id
4085 , l_temp_end_period_id
4086 , p_start_date_old
4087 , p_end_date_old
4088 ) LOOP
4089 IF l_latest_quota_id <> srp_quota.quota_id THEN
4090 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4091 p_api_version => 1.0
4092 , p_node_type => 'P'
4093 , p_init_msg_list => 'T'
4094 , p_node_id => srp_quota.quota_id
4095 , x_plan_elt_id_tbl => dependent_pe_tbl
4096 , x_return_status => l_return_status
4097 , x_msg_count => l_msg_count
4098 , x_msg_data => l_msg_data
4099 );
4100 l_latest_quota_id := srp_quota.quota_id;
4101 END IF;
4102
4103 cn_mark_events_pkg.mark_notify(
4104 srp_quota.salesrep_id
4105 , srp_quota.period_id
4106 , srp_quota.start_date
4107 , srp_quota.end_date
4108 , srp_quota.quota_id
4109 , 'POP'
4110 , l_event_log_id
4111 , p_org_id
4112 );
4113
4114 IF (dependent_pe_tbl.COUNT > 0) THEN
4115 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4116 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4117 FETCH l_pe_cursor INTO temp_quota_id;
4118
4119 IF l_pe_cursor%FOUND THEN
4120 cn_mark_events_pkg.mark_notify(
4121 p_salesrep_id => srp_quota.salesrep_id
4122 , p_period_id => srp_quota.period_id
4123 , p_start_date => srp_quota.start_date
4124 , p_end_date => srp_quota.end_date
4125 , p_quota_id => dependent_pe_tbl(i)
4126 , p_revert_to_state => 'POP'
4127 , p_event_log_id => l_event_log_id
4128 , p_org_id => p_org_id
4129 );
4130 END IF;
4131
4132 CLOSE l_pe_cursor;
4133 END LOOP;
4134 END IF; -- If (dependent_pe_tbl.count > 0)
4135 END LOOP;
4136 END IF;
4137
4138 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4139 fnd_log.STRING(
4140 fnd_log.level_procedure
4141 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_uplifts.end'
4142 , 'End of mark_event_srp_uplifts.'
4143 );
4144 END IF;
4145 EXCEPTION
4146 WHEN OTHERS THEN
4147 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4148 fnd_log.STRING(
4149 fnd_log.level_unexpected
4150 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_uplifts.exception'
4151 , SQLERRM
4152 );
4153 END IF;
4154
4155 RAISE;
4156 END mark_event_srp_uplifts;
4157
4158 -- Procedure Name
4159 -- mark_event_srp_rate_assigns
4160 -- Purpose
4161 -- mark events when cn_srp_rate_assigns is updated
4162 -- History
4163 -- 09/20/99 Kai Chen Created
4164 PROCEDURE mark_event_srp_rate_assigns(
4165 p_event_name VARCHAR2
4166 , p_object_name VARCHAR2
4167 , p_srp_object_id NUMBER
4168 , p_object_id NUMBER
4169 , p_start_date DATE
4170 , p_start_date_old DATE
4171 , p_end_date DATE
4172 , p_end_date_old DATE
4173 , p_org_id NUMBER
4174 ) IS
4175 -- x_srp_object_id --> p_srp_quota_assign_id
4176 CURSOR affected_srp_period_quotas(l_srp_start_period_id NUMBER, l_srp_end_period_id NUMBER) IS
4177 SELECT spq.salesrep_id
4178 , spq.period_id
4179 , spq.quota_id
4180 FROM cn_srp_period_quotas_all spq
4181 , cn_period_statuses_all acc
4182 , cn_srp_intel_periods_all intel
4183 WHERE spq.srp_quota_assign_id = p_srp_object_id
4184 AND acc.period_id = spq.period_id
4185 AND acc.org_id = spq.org_id
4186 AND (acc.period_id BETWEEN l_srp_start_period_id AND l_srp_end_period_id)
4187 AND acc.period_status = 'O'
4188 AND intel.salesrep_id = spq.salesrep_id
4189 AND intel.period_id = spq.period_id
4193
4190 AND intel.org_id = spq.org_id
4191 AND intel.processing_status_code <> 'CLEAN'
4192 ORDER BY spq.quota_id;
4194 -- very similiar to the mark_event_srp_rule_uplift when figuring out
4195 -- the affected srp/period/quota
4196 -- only difference is that this time we go to cn_rt_quota_asgns to
4197 -- get the start_date/ end_date
4198 l_event_log_id NUMBER(15);
4199 l_temp_start_date DATE;
4200 l_temp_end_date DATE;
4201 l_temp_start_period_id NUMBER(15);
4202 l_temp_end_period_id NUMBER(15);
4203 l_srp_start_period_id NUMBER(15);
4204 l_srp_end_period_id NUMBER(15);
4205
4206 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4207 SELECT quota_id
4208 FROM cn_srp_period_quotas_all
4209 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4210
4211 temp_quota_id cn_quotas.quota_id%TYPE;
4212 l_return_status VARCHAR2(50);
4213 l_msg_count NUMBER;
4214 l_msg_data VARCHAR2(2000);
4215 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4216 l_latest_quota_id NUMBER := 0;
4217 BEGIN
4218 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4219 RETURN;
4220 END IF;
4221
4222 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4223 fnd_log.STRING(
4224 fnd_log.level_procedure
4225 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_rate_assigns.begin'
4226 , 'Beginning of mark_event_srp_rate_assigns ...'
4227 );
4228 END IF;
4229
4230 cn_mark_events_pkg.log_event(
4231 p_event_name
4232 , p_object_name
4233 , p_object_id
4234 , p_start_date
4235 , p_start_date_old
4236 , p_end_date
4237 , p_end_date_old
4238 , l_event_log_id
4239 , p_org_id
4240 );
4241 l_srp_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
4242 l_srp_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
4243
4244 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
4245 FOR srp_quota IN affected_srp_period_quotas(l_srp_start_period_id, l_srp_end_period_id) LOOP
4246 IF l_latest_quota_id <> srp_quota.quota_id THEN
4247 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4248 p_api_version => 1.0
4249 , p_node_type => 'P'
4250 , p_init_msg_list => 'T'
4251 , p_node_id => srp_quota.quota_id
4252 , x_plan_elt_id_tbl => dependent_pe_tbl
4253 , x_return_status => l_return_status
4254 , x_msg_count => l_msg_count
4255 , x_msg_data => l_msg_data
4256 );
4257 l_latest_quota_id := srp_quota.quota_id;
4258 END IF;
4259
4260 cn_mark_events_pkg.mark_notify(
4261 srp_quota.salesrep_id
4262 , srp_quota.period_id
4263 , NULL
4264 , NULL
4265 , srp_quota.quota_id
4266 , 'CALC'
4267 , l_event_log_id
4268 , p_org_id
4269 );
4270
4271 IF (dependent_pe_tbl.COUNT > 0) THEN
4272 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4273 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4274 FETCH l_pe_cursor INTO temp_quota_id;
4275
4276 IF l_pe_cursor%FOUND THEN
4277 cn_mark_events_pkg.mark_notify(
4278 p_salesrep_id => srp_quota.salesrep_id
4279 , p_period_id => srp_quota.period_id
4280 , p_start_date => NULL
4281 , p_end_date => NULL
4282 , p_quota_id => dependent_pe_tbl(i)
4283 , p_revert_to_state => 'CALC'
4284 , p_event_log_id => l_event_log_id
4285 , p_org_id => p_org_id
4286 );
4287 END IF;
4288
4289 CLOSE l_pe_cursor;
4290 END LOOP;
4291 END IF; -- If (dependent_pe_tbl.count > 0)
4292 END LOOP;
4293 END IF;
4294
4295 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4296 fnd_log.STRING(
4297 fnd_log.level_procedure
4298 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_rate_assigns.end'
4299 , 'End of mark_event_srp_rate_assigns.'
4300 );
4301 END IF;
4302 EXCEPTION
4303 WHEN OTHERS THEN
4304 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4305 fnd_log.STRING(
4306 fnd_log.level_unexpected
4307 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_rate_assigns.exception'
4308 , SQLERRM
4309 );
4310 END IF;
4311
4312 RAISE;
4313 END mark_event_srp_rate_assigns;
4314
4315 -- Procedure Name
4316 -- mark_event_srp_period_quota
4317 -- Purpose
4318 -- mark events when cn_srp_period_quotas is updated
4319 -- History
4320 -- 09/20/99 Kai Chen Created
4321 PROCEDURE mark_event_srp_period_quota(
4322 p_event_name VARCHAR2
4323 , p_object_name VARCHAR2
4324 , p_srp_object_id NUMBER
4325 , p_object_id NUMBER
4326 , p_start_date DATE
4327 , p_start_date_old DATE
4328 , p_end_date DATE
4329 , p_end_date_old DATE
4330 , p_org_id NUMBER
4331 ) IS
4332 -- p_srp_object_id --> srp_period_quota_Id
4333 CURSOR affected_srp_period_quotas IS
4334 SELECT spq.salesrep_id
4335 , spq.period_id
4336 , spq.quota_id
4337 FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
4338 WHERE spq.srp_period_quota_id = p_srp_object_id -- p_srp_period_quota_id
4339 AND intel.salesrep_id = spq.salesrep_id
4340 AND intel.period_id = spq.period_id
4341 AND intel.org_id = spq.org_id
4342 -- scannane, bug 7154503, Notify log table update
4343 -- AND intel.processing_status_code <> 'CLEAN'
4344 ORDER BY spq.quota_id;
4345
4346 l_event_log_id NUMBER(15);
4347
4348 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4349 SELECT quota_id
4350 FROM cn_srp_period_quotas_all
4351 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4352
4353 temp_quota_id cn_quotas.quota_id%TYPE;
4354 l_return_status VARCHAR2(50);
4355 l_msg_count NUMBER;
4356 l_msg_data VARCHAR2(2000);
4357 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4358 l_latest_quota_id NUMBER := 0;
4359 BEGIN
4360 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4361 RETURN;
4362 END IF;
4363
4364 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4365 fnd_log.STRING(
4366 fnd_log.level_procedure
4367 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.begin'
4368 , 'Beginning of mark_event_srp_period_quota ...'
4369 );
4370 END IF;
4371
4372 cn_mark_events_pkg.log_event(
4373 p_event_name
4374 , p_object_name
4375 , p_object_id
4376 , p_start_date
4377 , p_start_date_old
4378 , p_end_date
4379 , p_end_date_old
4380 , l_event_log_id
4381 , p_org_id
4382 );
4383
4384 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
4385 FOR srp_quota IN affected_srp_period_quotas LOOP
4386 IF l_latest_quota_id <> srp_quota.quota_id THEN
4387 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4388 p_api_version => 1.0
4389 , p_node_type => 'P'
4390 , p_init_msg_list => 'T'
4394 , x_msg_count => l_msg_count
4391 , p_node_id => srp_quota.quota_id
4392 , x_plan_elt_id_tbl => dependent_pe_tbl
4393 , x_return_status => l_return_status
4395 , x_msg_data => l_msg_data
4396 );
4397 END IF;
4398
4399 cn_mark_events_pkg.mark_notify(
4400 srp_quota.salesrep_id
4401 , srp_quota.period_id
4402 , NULL
4403 , NULL
4404 , srp_quota.quota_id
4405 , 'CALC'
4406 , l_event_log_id
4407 , p_org_id
4408 );
4409
4410 IF (dependent_pe_tbl.COUNT > 0) THEN
4411 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4412 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4413 FETCH l_pe_cursor INTO temp_quota_id;
4414
4415 IF l_pe_cursor%FOUND THEN
4416 cn_mark_events_pkg.mark_notify(
4417 p_salesrep_id => srp_quota.salesrep_id
4418 , p_period_id => srp_quota.period_id
4419 , p_start_date => NULL
4420 , p_end_date => NULL
4421 , p_quota_id => dependent_pe_tbl(i)
4422 , p_revert_to_state => 'CALC'
4423 , p_event_log_id => l_event_log_id
4424 , p_org_id => p_org_id
4425 );
4426 END IF;
4427
4428 CLOSE l_pe_cursor;
4429 END LOOP;
4430 END IF; -- If (dependent_pe_tbl.count > 0)
4431 END LOOP;
4432 END IF;
4433
4434 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4435 fnd_log.STRING(
4436 fnd_log.level_procedure
4437 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.end'
4438 , 'End of mark_event_srp_period_quota.'
4439 );
4440 END IF;
4441 EXCEPTION
4442 WHEN OTHERS THEN
4443 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4444 fnd_log.STRING(
4445 fnd_log.level_unexpected
4446 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.exception'
4447 , SQLERRM
4448 );
4449 END IF;
4450
4451 RAISE;
4452 END mark_event_srp_period_quota;
4453
4454 -- Procedure Name
4455 -- mark_event_srp_period_quota
4456 -- Purpose
4457 -- mark events when cn_srp_period_quotas is updated
4458 -- History
4459 -- 23/Oct/08 venjayar Created
4460 PROCEDURE mark_event_srp_period_quota(
4461 p_event_name VARCHAR2
4462 , p_object_name VARCHAR2
4463 , p_srp_object_id NUMBER
4464 , p_object_id NUMBER
4465 , p_period_id NUMBER
4466 , p_quota_id NUMBER
4467 , p_start_date DATE
4468 , p_start_date_old DATE
4469 , p_end_date DATE
4470 , p_end_date_old DATE
4471 , p_org_id NUMBER
4472 ) IS
4473 l_return_status VARCHAR2(50);
4474 l_msg_count NUMBER;
4475 l_msg_data VARCHAR2(2000);
4476 l_event_log_id NUMBER(15);
4477 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4478 BEGIN
4479 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4480 RETURN;
4481 END IF;
4482
4483 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4484 fnd_log.STRING(
4485 fnd_log.level_procedure
4486 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.begin'
4487 , 'Beginning of mark_event_srp_period_quota ...'
4488 );
4489 END IF;
4490
4491 cn_mark_events_pkg.log_event(
4492 p_event_name
4493 , p_object_name
4494 , p_object_id
4495 , p_start_date
4496 , p_start_date_old
4497 , p_end_date
4498 , p_end_date_old
4499 , l_event_log_id
4500 , p_org_id
4501 );
4502
4503 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
4504 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4505 p_api_version => 1.0
4506 , p_node_type => 'P'
4507 , p_init_msg_list => 'T'
4508 , p_node_id => p_quota_id
4509 , x_plan_elt_id_tbl => dependent_pe_tbl
4510 , x_return_status => l_return_status
4511 , x_msg_count => l_msg_count
4512 , x_msg_data => l_msg_data
4513 );
4514
4515 cn_mark_events_pkg.mark_notify(
4516 p_salesrep_id => p_object_id
4517 , p_period_id => p_period_id
4518 , p_start_date => NULL
4519 , p_end_date => NULL
4520 , p_quota_id => p_quota_id
4521 , p_revert_to_state => 'CALC'
4522 , p_event_log_id => l_event_log_id
4523 , p_org_id => p_org_id
4524 );
4525
4526 -- We have to raise Notification Events even for the Dependent Plan Elements
4527 -- which are affected because of this change and it should be
4528 -- done only if the Dependent PE is valid for the Resource in that Period.
4529 -- In order to do that check, we have to validate against
4530 -- CN_SRP_PERIOD_QUOTAS_ALL. But since this code is executed as part of
4531 -- Trigger on the same table, we will run into ORA 04091 - Mutating Trigger.
4532 --
4533 -- Either we have to change the entire architecture of moving away from
4534 -- trigger and have table handlers and fire the events from there. Though
4535 -- it is a good approach.. its not possible to do such a big change now.
4536 --
4540 -- Issue. But we wont be able to use :NEW and :OLD.
4537 -- Since this code is executed as part of EO, we can surely expect that
4538 -- the trigger is always called for a single row only and thus even
4539 -- a statement level trigger will work and we wont run in Mutating Trigger
4541 --
4542 -- Thinking more about.. a Plan Element can be dependent on other PE's only
4543 -- if the Plan Elements are part of the same Compensation Plan. Thus, we
4544 -- be sure that the Dependent Plan Elements has to be part of the Same
4545 -- Compensation Plan and thus it is valid for the Resource. So temporarily
4546 -- removed the check. If this conclusion is wrong, then the code has to be
4547 -- implemented as ONE ROW LEVEL TRIGGER which will capture the Dependent PE's
4548 -- and another STATEMENT LEVEL TRIGGER which will do the validation and
4549 -- notify Dependent PE's.
4550 --
4551 IF (dependent_pe_tbl.COUNT > 0) THEN
4552 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4553 cn_mark_events_pkg.mark_notify(
4554 p_salesrep_id => p_object_id
4555 , p_period_id => p_period_id
4556 , p_start_date => NULL
4557 , p_end_date => NULL
4558 , p_quota_id => dependent_pe_tbl(i)
4559 , p_revert_to_state => 'CALC'
4560 , p_event_log_id => l_event_log_id
4561 , p_org_id => p_org_id
4562 );
4563 END LOOP;
4564 END IF; -- If (dependent_pe_tbl.count > 0)
4565 END IF;
4566
4567 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4568 fnd_log.STRING(
4569 fnd_log.level_procedure
4570 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.end'
4571 , 'End of mark_event_srp_period_quota.'
4572 );
4573 END IF;
4574 EXCEPTION
4575 WHEN OTHERS THEN
4576 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4577 fnd_log.STRING(
4578 fnd_log.level_unexpected
4579 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.exception'
4580 , SQLERRM
4581 );
4582 END IF;
4583
4584 RAISE;
4585 END mark_event_srp_period_quota;
4586
4587
4588 -- Procedure Name
4589 -- mark_event_srp_payee_assign
4590 -- Purpose
4591 -- mark events when cn_srp_payee_assigns is inserted, updated and deleted
4592 -- History
4593 -- 09/20/99 Kai Chen Created
4594 PROCEDURE mark_event_srp_payee_assign(
4595 p_event_name VARCHAR2
4596 , p_object_name VARCHAR2
4597 , p_srp_object_id NUMBER
4598 , p_object_id NUMBER
4599 , p_start_date DATE
4600 , p_start_date_old DATE
4601 , p_end_date DATE
4602 , p_end_date_old DATE
4603 , p_org_id NUMBER
4604 ) IS
4605 -- p_object_id --> p_srp_quota_assign_id
4606
4607 -- CHANGE_SRP_QUOTA_POP
4608 -- need to use the start_date/end_date info to restrict affected periods
4609 CURSOR affected_srp_period_quotas(
4610 l_start_period_id NUMBER
4611 , l_end_period_id NUMBER
4612 , l_s_date DATE
4613 , l_e_date DATE
4614 ) IS
4615 SELECT spq.salesrep_id
4616 , spq.period_id
4617 , spq.quota_id
4618 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
4619 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
4620 end_date
4621 FROM cn_srp_period_quotas_all spq
4622 , cn_period_statuses_all acc
4623 , cn_srp_intel_periods_all intel
4624 WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
4625 AND acc.period_id = spq.period_id
4626 AND acc.org_id = spq.org_id
4627 AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
4628 AND acc.period_status = 'O'
4629 AND intel.salesrep_id = spq.salesrep_id
4630 AND intel.period_id = spq.period_id
4631 AND intel.processing_status_code <> 'CLEAN'
4632 AND intel.org_id = spq.org_id
4633 ORDER BY spq.quota_id;
4634
4635 l_event_log_id NUMBER(15);
4636 l_temp_start_period_id NUMBER(15);
4637 l_temp_end_period_id NUMBER(15);
4638 l_date_range_tbl cn_api.date_range_tbl_type;
4639
4640 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4641 SELECT quota_id
4642 FROM cn_srp_period_quotas_all
4643 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4644
4645 temp_quota_id cn_quotas.quota_id%TYPE;
4646 l_return_status VARCHAR2(50);
4647 l_msg_count NUMBER;
4648 l_msg_data VARCHAR2(2000);
4649 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4650 l_latest_quota_id NUMBER := 0;
4651 BEGIN
4652 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4653 RETURN;
4654 END IF;
4655
4656 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4657 fnd_log.STRING(
4658 fnd_log.level_procedure
4659 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_payee_assigns.begin'
4660 , 'Beginning of mark_event_srp_payee_assigns ...'
4661 );
4662 END IF;
4663
4664 cn_mark_events_pkg.log_event(
4665 p_event_name
4666 , p_object_name
4667 , p_object_id
4668 , p_start_date
4669 , p_start_date_old
4670 , p_end_date
4671 , p_end_date_old
4675
4672 , l_event_log_id
4673 , p_org_id
4674 );
4676 IF p_event_name = 'CHANGE_SRP_QUOTA_POP' THEN
4677 l_temp_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
4678 l_temp_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
4679
4680 FOR srp_quota IN affected_srp_period_quotas(
4681 l_temp_start_period_id
4682 , l_temp_end_period_id
4683 , p_start_date_old
4684 , p_end_date_old
4685 ) LOOP
4686 IF l_latest_quota_id <> srp_quota.quota_id THEN
4687 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4688 p_api_version => 1.0
4689 , p_node_type => 'P'
4690 , p_init_msg_list => 'T'
4691 , p_node_id => srp_quota.quota_id
4692 , x_plan_elt_id_tbl => dependent_pe_tbl
4693 , x_return_status => l_return_status
4694 , x_msg_count => l_msg_count
4695 , x_msg_data => l_msg_data
4696 );
4697 l_latest_quota_id := srp_quota.quota_id;
4698 END IF;
4699
4700 -- modified by rjin 11/10/1999
4701 -- since change payee assign doesn't affect subsequent period
4702 -- so we only need to mark 'NEW'
4703 mark_notify(
4704 p_salesrep_id => srp_quota.salesrep_id
4705 , p_period_id => srp_quota.period_id
4706 , p_start_date => srp_quota.start_date
4707 , --NULL,
4708 p_end_date => srp_quota.end_date
4709 , --NULL,
4710 p_quota_id => srp_quota.quota_id
4711 , p_revert_to_state => 'POP'
4712 , p_event_log_id => l_event_log_id
4713 , p_mode => 'NEW'
4714 , p_org_id => p_org_id
4715 );
4716
4717 IF (dependent_pe_tbl.COUNT > 0) THEN
4718 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4719 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4720 FETCH l_pe_cursor INTO temp_quota_id;
4721
4722 IF l_pe_cursor%FOUND THEN
4723 cn_mark_events_pkg.mark_notify(
4724 p_salesrep_id => srp_quota.salesrep_id
4725 , p_period_id => srp_quota.period_id
4726 , p_start_date => srp_quota.start_date
4727 , p_end_date => srp_quota.end_date
4728 , p_quota_id => dependent_pe_tbl(i)
4729 , p_revert_to_state => 'POP'
4730 , p_event_log_id => l_event_log_id
4731 , p_mode => 'NEW'
4732 , p_org_id => p_org_id
4733 );
4734 END IF;
4735
4736 CLOSE l_pe_cursor;
4737 END LOOP;
4738 END IF; -- If (dependent_pe_tbl.count > 0)
4739 END LOOP;
4740 ELSIF p_event_name = 'CHANGE_SRP_QUOTA_PAYEE_DATE' THEN
4741 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
4742 , l_date_range_tbl);
4743
4744 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
4745 l_temp_start_period_id :=
4746 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date, p_org_id);
4747 l_temp_end_period_id :=
4748 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
4749
4750 FOR srp_quota IN affected_srp_period_quotas(
4751 l_temp_start_period_id
4752 , l_temp_end_period_id
4753 , l_date_range_tbl(l_ctr).start_date
4754 , l_date_range_tbl(l_ctr).end_date
4755 ) LOOP
4756 IF l_latest_quota_id <> srp_quota.quota_id THEN
4757 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4758 p_api_version => 1.0
4759 , p_node_type => 'P'
4760 , p_init_msg_list => 'T'
4761 , p_node_id => srp_quota.quota_id
4762 , x_plan_elt_id_tbl => dependent_pe_tbl
4763 , x_return_status => l_return_status
4764 , x_msg_count => l_msg_count
4765 , x_msg_data => l_msg_data
4766 );
4767 l_latest_quota_id := srp_quota.quota_id;
4768 END IF;
4769
4770 -- modified by rjin 11/10/1999
4771 -- since change payee assign doesn't affect subsequent period
4772 -- so we only need to mark 'NEW'
4773 mark_notify(
4774 p_salesrep_id => srp_quota.salesrep_id
4775 , p_period_id => srp_quota.period_id
4776 , p_start_date => srp_quota.start_date
4777 , --NULL,
4778 p_end_date => srp_quota.end_date
4779 , --NULL,
4780 p_quota_id => srp_quota.quota_id
4781 , p_revert_to_state => 'POP'
4782 , p_event_log_id => l_event_log_id
4783 , p_mode => 'NEW'
4784 , p_org_id => p_org_id
4785 );
4786
4787 IF (dependent_pe_tbl.COUNT > 0) THEN
4788 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4789 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4790 FETCH l_pe_cursor INTO temp_quota_id;
4791
4792 IF l_pe_cursor%FOUND THEN
4796 , p_start_date => srp_quota.start_date
4793 cn_mark_events_pkg.mark_notify(
4794 p_salesrep_id => srp_quota.salesrep_id
4795 , p_period_id => srp_quota.period_id
4797 , p_end_date => srp_quota.end_date
4798 , p_quota_id => dependent_pe_tbl(i)
4799 , p_revert_to_state => 'POP'
4800 , p_event_log_id => l_event_log_id
4801 , p_mode => 'NEW'
4802 , p_org_id => p_org_id
4803 );
4804 END IF;
4805
4806 CLOSE l_pe_cursor;
4807 END LOOP;
4808 END IF; -- If (dependent_pe_tbl.count > 0)
4809 END LOOP;
4810 END LOOP;
4811 END IF;
4812
4813 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4814 fnd_log.STRING(
4815 fnd_log.level_procedure
4816 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_assign.end'
4817 , 'End of mark_event_srp_payee_assign.'
4818 );
4819 END IF;
4820 EXCEPTION
4821 WHEN OTHERS THEN
4822 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4823 fnd_log.STRING(
4824 fnd_log.level_unexpected
4825 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_payee_assign.exception'
4826 , SQLERRM
4827 );
4828 END IF;
4829
4830 RAISE;
4831 END mark_event_srp_payee_assign;
4832
4833 -- mark all the reps in a single team
4834 PROCEDURE mark_notify_team(
4835 p_team_id IN NUMBER
4836 , p_team_event_name IN VARCHAR2
4837 , p_team_name IN VARCHAR2
4838 , p_start_date_active IN DATE
4839 , p_end_date_active IN DATE
4840 , p_event_log_id IN NUMBER
4841 , p_org_id IN NUMBER
4842 ) IS
4843 l_event_log_id NUMBER;
4844 l_action_link_id NUMBER;
4845 t_team_name cn_comp_teams.NAME%TYPE;
4846 t_start_date_active cn_comp_teams.start_date_active%TYPE;
4847 t_end_date_active cn_comp_teams.end_date_active%TYPE;
4848 l_revert_state VARCHAR2(30);
4849 l_action VARCHAR2(30);
4850
4851 -- get all the reps in this team
4852 CURSOR c_all_members IS
4853 SELECT salesrep_id
4854 FROM cn_srp_comp_teams_v
4855 WHERE comp_team_id = p_team_id AND org_id = p_org_id;
4856
4857 -- get team info
4858 CURSOR c_team_info(p_team_id NUMBER) IS
4859 SELECT NAME
4860 , start_date_active
4861 , end_date_active
4862 FROM cn_comp_teams
4863 WHERE comp_team_id = p_team_id;
4864
4865 -- cursor to find all periods in the date range for each srp
4866 CURSOR periods(
4867 p_salesrep_id NUMBER
4868 , p_start_date DATE
4869 , p_end_date DATE
4870 , p_action VARCHAR2
4871 , p_revert_state VARCHAR2
4872 ) IS
4873 SELECT p.period_id
4874 , GREATEST(p_start_date, p.start_date) start_date
4875 , DECODE(p_end_date, NULL, p.end_date, LEAST(p_end_date, p.end_date)) end_date
4876 FROM cn_srp_intel_periods_all p
4877 WHERE p.salesrep_id = p_salesrep_id
4878 AND p.org_id = p_org_id
4879 AND (p_end_date IS NULL OR p.start_date <= p_end_date)
4880 AND (p.end_date >= p_start_date);
4881 BEGIN
4882 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4883 fnd_log.STRING(
4884 fnd_log.level_procedure
4885 , 'cn.plsql.cn_mark_events_pkg.mark_notify_team.begin'
4886 , 'Beginning of mark_notify_team ...'
4887 );
4888 END IF;
4889
4890 IF p_team_name IS NULL THEN
4891 OPEN c_team_info(p_team_id);
4892 FETCH c_team_info INTO t_team_name, t_start_date_active, t_end_date_active;
4893 IF (c_team_info%NOTFOUND) THEN
4894 CLOSE c_team_info;
4895 RETURN;
4896 END IF;
4897
4898 CLOSE c_team_info;
4899 ELSE
4900 t_team_name := p_team_name;
4901 t_start_date_active := p_start_date_active;
4902 t_end_date_active := p_end_date_active;
4903 END IF;
4904
4905 IF p_event_log_id IS NULL THEN
4906 cn_mark_events_pkg.log_event(
4907 p_event_name => p_team_event_name
4908 , p_object_name => t_team_name
4909 , p_object_id => p_team_id
4910 , p_start_date => t_start_date_active
4911 , p_start_date_old => NULL
4912 , p_end_date => t_end_date_active
4913 , p_end_date_old => NULL
4914 , x_event_log_id => l_event_log_id
4915 , p_org_id => p_org_id
4916 );
4917 ELSE
4918 l_event_log_id := p_event_log_id;
4919 END IF;
4920
4921 IF p_team_event_name = 'CHANGE_TEAM_ADD_REP' THEN
4922 l_revert_state := 'POP';
4923 l_action := NULL;
4924 ELSE
4925 l_revert_state := 'CALC';
4926 l_action := 'DELETE_TEAM_MEMB';
4927 END IF;
4928
4929 FOR c_mem_rec IN c_all_members LOOP
4930 FOR prd IN periods(
4931 c_mem_rec.salesrep_id
4932 , t_start_date_active
4933 , t_end_date_active
4934 , l_action
4935 , l_revert_state
4936 ) LOOP
4937 cn_mark_events_pkg.mark_notify_salesreps(
4938 p_salesrep_id => c_mem_rec.salesrep_id
4939 , p_comp_group_id => NULL
4940 , p_period_id => prd.period_id
4941 , p_start_date => prd.start_date
4942 , p_end_date => prd.end_date
4943 , p_revert_to_state => l_revert_state
4944 , p_action => l_action
4945 , p_action_link_id => NULL
4946 , p_base_salesrep_id => NULL
4947 , p_base_comp_group_id => NULL
4948 , p_event_log_id => l_event_log_id
4949 , x_action_link_id => l_action_link_id
4950 , p_org_id => p_org_id
4951 );
4952 END LOOP;
4953 END LOOP;
4954
4955 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4956 fnd_log.STRING(
4957 fnd_log.level_procedure
4958 , 'cn.plsql.cn_mark_events_pkg.mark_notify_team.end'
4959 , 'End of mark_notify_team.'
4960 );
4961 END IF;
4962 EXCEPTION
4963 WHEN OTHERS THEN
4964 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4965 fnd_log.STRING(
4966 fnd_log.level_unexpected
4967 , 'cn.plsql.cn_mark_events_pkg.mark_notify_team.exception'
4968 , SQLERRM
4969 );
4970 END IF;
4971
4972 RAISE;
4973 END mark_notify_team;
4974 END cn_mark_events_pkg;