[Home] [Help]
PACKAGE BODY: APPS.CN_MARK_EVENTS_PKG
Source
1 PACKAGE BODY cn_mark_events_pkg AS
2 -- $Header: cneventb.pls 120.17.12020000.2 2013/04/16 06:10:45 ndamala 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 -- rnagaraj, bug 8568515 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
385 );
386 END IF;
387
388 IF l_update_flag THEN
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;
511 END IF;
512
513 IF l_chk_calc_lower_events_csr%ISOPEN THEN
514 CLOSE l_chk_calc_lower_events_csr;
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
604 FROM cn_srp_plan_assigns_all
605 WHERE salesrep_id = p_salesrep_id
606 AND org_id = p_org_id))
607 AND (
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
708 p_org_id => p_org_id
709 );
710 END LOOP;
711 ELSE
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
871 FROM cn_notify_log_all
872 WHERE period_id = p_period_id
873 AND org_id = p_org_id
874 AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
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
1007 AND revert_state = p_revert_to_state
1008 AND status = 'INCOMPLETE'
1009 AND quota_id IS NOT NULL;
1010
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'
1142 AND org_id = p_org_id;
1143
1144 l_period_id NUMBER(15);
1145 BEGIN
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
1281 , p_start_date IN DATE
1282 , p_start_date_old IN DATE
1283 , p_end_date IN DATE
1284 , p_end_date_old 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(
1429 fnd_log.level_procedure
1430 , 'cn.plsql.cn_mark_events_pkg.mark_event_cls_rule.begin'
1431 , 'Beginning of mark_event_cls_rule ...'
1432 );
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
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
1583 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
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
1714 );
1715 END LOOP;
1716
1717 l_date_range_over_tbl := l_date_range_null_tbl;
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
1847 , DECODE(
1848 acc.period_id
1849 , l_end_period_id, NVL(l_end_date, acc.end_date)
1850 , 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'
1963 , p_event_log_id => l_event_log_id
1964 , p_mode => 'NEW'
1965 , p_org_id => p_org_id
1966 );
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 -- modified for bug 15897219
2039 l_start_period_id := cn_general_utils.get_acc_fromperiod_id(l_start_date, p_org_id);
2040 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2041
2042 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id
2043 , p_start_date_old, p_end_date_old) LOOP
2044 cn_mark_events_pkg.mark_notify(
2045 p_salesrep_id => affected_recs.salesrep_id
2046 , p_period_id => affected_recs.period_id
2047 , p_start_date => affected_recs.start_date
2048 , p_end_date => affected_recs.end_date
2049 , p_quota_id => affected_recs.quota_id
2050 , p_revert_to_state => 'POP'
2051 , p_event_log_id => l_event_log_id
2052 , p_org_id => p_org_id
2053 );
2054
2055 IF (dependent_pe_tbl.COUNT > 0) THEN
2056 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2057 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
2058 FETCH l_pe_cursor INTO temp_quota_id;
2059
2060 IF l_pe_cursor%FOUND THEN
2061 cn_mark_events_pkg.mark_notify(
2062 p_salesrep_id => affected_recs.salesrep_id
2063 , p_period_id => affected_recs.period_id
2064 , p_start_date => affected_recs.start_date
2065 , p_end_date => affected_recs.end_date
2066 , p_quota_id => dependent_pe_tbl(i)
2067 , p_revert_to_state => 'POP'
2068 , p_event_log_id => l_event_log_id
2069 , p_org_id => p_org_id
2070 );
2071 END IF;
2072
2073 CLOSE l_pe_cursor;
2074 END LOOP;
2075 END IF; -- If (dependent_pe_tbl.count > 0)
2076 END LOOP;
2077 ELSIF p_event_name = 'CHANGE_QUOTA_UPLIFT_DATE' THEN
2078 --
2079 -- Update Uplift Start Date and End Date
2080 --
2081 cn_api.get_date_range_diff(
2082 a_start_date => p_start_date
2083 , a_end_date => p_end_date
2084 , b_start_date => p_start_date_old
2085 , b_end_date => p_end_date_old
2086 , x_date_range_tbl => l_date_range_rec_tbl
2087 );
2088
2089 FOR i IN 1 .. l_date_range_rec_tbl.COUNT LOOP
2090 l_start_period_id :=
2091 cn_api.get_acc_period_id(l_date_range_rec_tbl(i).start_date, p_org_id);
2092 l_end_period_id := cn_api.get_acc_period_id(l_date_range_rec_tbl(i).end_date, p_org_id);
2093
2094 FOR affected_period_recs IN affected_srp_period_curs(
2095 l_start_period_id
2096 , l_end_period_id
2097 , l_date_range_rec_tbl(i).start_date
2098 , l_date_range_rec_tbl(i).end_date
2099 ) LOOP
2100 cn_mark_events_pkg.mark_notify(
2101 p_salesrep_id => affected_period_recs.salesrep_id
2102 , p_period_id => affected_period_recs.period_id
2103 , p_start_date => affected_period_recs.start_date
2104 , p_end_date => affected_period_recs.end_date
2105 , p_quota_id => affected_period_recs.quota_id
2106 , p_revert_to_state => 'POP'
2107 , p_event_log_id => l_event_log_id
2108 , p_org_id => p_org_id
2109 );
2110
2111 IF (dependent_pe_tbl.COUNT > 0) THEN
2112 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2113 OPEN l_pe_cursor(affected_period_recs.salesrep_id, affected_period_recs.period_id, dependent_pe_tbl(i));
2114 FETCH l_pe_cursor INTO temp_quota_id;
2115
2116 IF l_pe_cursor%FOUND THEN
2117 cn_mark_events_pkg.mark_notify(
2118 p_salesrep_id => affected_period_recs.salesrep_id
2119 , p_period_id => affected_period_recs.period_id
2120 , p_start_date => affected_period_recs.start_date
2121 , p_end_date => affected_period_recs.end_date
2122 , p_quota_id => dependent_pe_tbl(i)
2123 , p_revert_to_state => 'POP'
2124 , p_event_log_id => l_event_log_id
2125 , p_org_id => p_org_id
2126 );
2127 END IF;
2128
2129 CLOSE l_pe_cursor;
2130 END LOOP;
2131 END IF; -- If (dependent_pe_tbl.count > 0)
2132 END LOOP;
2133 END LOOP;
2134 END IF;
2135
2136 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2137 fnd_log.STRING(
2138 fnd_log.level_procedure
2139 , 'cn.plsql.cn_mark_events_pkg.mark_event_quota.end'
2140 , 'End of mark_event_quota.'
2141 );
2142 END IF;
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2146 fnd_log.STRING(
2147 fnd_log.level_unexpected
2148 , 'cn.plsql.cn_mark_events_pkg.mark_event_quota.exception'
2149 , SQLERRM
2150 );
2151 END IF;
2152
2153 RAISE;
2154 END mark_event_quota;
2155
2156 -- Start of Comments
2157 -- name : mark_event_rt_quota
2158 -- Type : None
2159 -- Pre-reqs : None.
2160 -- Usage : Procedure to Mark the cn_rt_quota_asgn
2161 -- Parameters :
2162 -- IN : p_event_name IN VARCHAR2
2163 -- p_object_name IN VARCHAR2
2164 -- p_object_id IN NUMBER
2165 -- p_start_date IN DATE
2166 -- p_start_Date_old IN DATE
2167 -- p_end_date IN DATE
2168 -- p_end_date_old IN DATE
2169 --
2170 -- Version : Current version 1.0
2171 -- Initial version 1.0
2172 --
2173 -- Case 7: whenever there is a change in the start date and end date of
2174 -- CN_RT_QUOTA_ASGNS, it marks the affected period records
2175 -- for that quota.
2176 -- called from trigger
2177 --
2178 -- Case 8: whenever there is insert/delete the cn_rt_quota_asgns
2179 -- it marks the affected salesrep for that quotas.
2180 -- called from trigger
2181 --
2182 PROCEDURE mark_event_rt_quota(
2183 p_event_name VARCHAR2
2184 , p_object_name VARCHAR2
2185 , p_object_id NUMBER
2186 , p_start_date DATE
2187 , p_start_date_old DATE
2188 , p_end_date DATE
2189 , p_end_date_old DATE
2190 , p_org_id NUMBER
2191 ) IS
2192 l_event_log_id NUMBER;
2193 l_date_range_rec_tbl cn_api.date_range_tbl_type;
2194 l_start_period_id NUMBER(15);
2195 l_end_period_id NUMBER(15);
2196
2197 CURSOR affected_srp_period_curs(l_start_period_id NUMBER, l_end_period_id NUMBER) IS
2198 -- modified by rjin 11/10/1999 add distinct
2199 SELECT DISTINCT spq.salesrep_id
2200 , spq.period_id
2201 , spq.quota_id
2202 FROM cn_srp_period_quotas_all spq
2203 , cn_srp_intel_periods_all sip
2204 , cn_period_statuses_all acc
2205 WHERE spq.quota_id = p_object_id
2206 AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2207 AND sip.salesrep_id = spq.salesrep_id
2208 AND sip.period_id = spq.period_id
2209 AND sip.org_id = spq.org_id
2210 AND sip.processing_status_code <> 'CLEAN'
2211 AND acc.period_id = spq.period_id
2212 AND acc.org_id = spq.org_id
2213 AND acc.period_status IN('O', 'F');
2214
2215 CURSOR l_quota_dates_csr IS
2216 SELECT start_date
2217 , end_date
2218 FROM cn_quotas_all
2219 WHERE quota_id = p_object_id;
2220
2221 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
2222 SELECT quota_id
2223 FROM cn_srp_period_quotas_all
2224 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
2225
2226 temp_quota_id cn_quotas.quota_id%TYPE;
2227 l_return_status VARCHAR2(50);
2228 l_msg_count NUMBER;
2229 l_msg_data VARCHAR2(2000);
2230 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
2231 BEGIN
2232 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2233 RETURN;
2234 END IF;
2235
2236 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2237 fnd_log.STRING(
2238 fnd_log.level_procedure
2239 , 'cn.plsql.cn_mark_events_pkg.mark_event_rt_quota.begin'
2240 , 'Beginning of mark_event_rt_quota ...'
2241 );
2242 END IF;
2243
2244 cn_mark_events_pkg.log_event(
2245 p_event_name => p_event_name
2246 , p_object_name => p_object_name
2247 , p_object_id => p_object_id
2248 , p_start_date => p_start_date
2249 , p_start_date_old => p_start_date_old
2250 , p_end_date => p_end_date
2251 , p_end_date_old => p_end_date_old
2252 , x_event_log_id => l_event_log_id
2253 , p_org_id => p_org_id
2254 );
2255
2256 -- clku, move get_parent_plan_elts outside the period/salesrep loop
2257 IF (p_object_id IS NOT NULL) THEN
2258 cn_calc_sql_exps_pvt.get_parent_plan_elts(
2259 p_api_version => 1.0
2260 , p_node_type => 'P'
2261 , p_init_msg_list => 'T'
2262 , p_node_id => p_object_id
2263 , x_plan_elt_id_tbl => dependent_pe_tbl
2264 , x_return_status => l_return_status
2265 , x_msg_count => l_msg_count
2266 , x_msg_data => l_msg_data
2267 );
2268 END IF;
2269
2270 --
2271 -- Check the Event Name
2272 --
2273 IF p_event_name = 'CHANGE_QUOTA_CALC' THEN
2274 -- 1. update cn_trx_factors.event_factor
2275 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2276 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2277
2278 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id) LOOP
2279 cn_mark_events_pkg.mark_notify(
2280 p_salesrep_id => affected_recs.salesrep_id
2281 , p_period_id => affected_recs.period_id
2282 , p_start_date => NULL
2283 , p_end_date => NULL
2284 , p_quota_id => affected_recs.quota_id
2285 , p_revert_to_state => 'CALC'
2286 , p_event_log_id => l_event_log_id
2287 , p_org_id => p_org_id
2288 );
2289
2290 IF (dependent_pe_tbl.COUNT > 0) THEN
2291 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2292 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
2293 FETCH l_pe_cursor INTO temp_quota_id;
2294
2295 IF l_pe_cursor%FOUND THEN
2296 cn_mark_events_pkg.mark_notify(
2297 p_salesrep_id => affected_recs.salesrep_id
2298 , p_period_id => affected_recs.period_id
2299 , p_start_date => NULL
2300 , p_end_date => NULL
2301 , p_quota_id => dependent_pe_tbl(i)
2302 , p_revert_to_state => 'CALC'
2303 , p_event_log_id => l_event_log_id
2304 , p_org_id => p_org_id
2305 );
2306 END IF;
2307
2308 CLOSE l_pe_cursor;
2309 END LOOP;
2310 END IF; -- If (dependent_pe_tbl.count > 0)
2311 END LOOP;
2312 ELSIF p_event_name = 'CHANGE_QUOTA_RT_DATE' THEN
2313 --
2314 -- Update rt_quota Assigns Start Date, End Date
2315 --
2316 cn_api.get_date_range_diff(
2317 a_start_date => p_start_date
2318 , a_end_date => p_end_date
2319 , b_start_date => p_start_date_old
2320 , b_end_date => p_end_date_old
2321 , x_date_range_tbl => l_date_range_rec_tbl
2322 );
2323
2324 FOR i IN 1 .. l_date_range_rec_tbl.COUNT LOOP
2325 l_start_period_id :=
2326 cn_api.get_acc_period_id(l_date_range_rec_tbl(i).start_date, p_org_id);
2327 l_end_period_id := cn_api.get_acc_period_id(l_date_range_rec_tbl(i).end_date, p_org_id);
2328
2329 FOR affected_period_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id) LOOP
2330 cn_mark_events_pkg.mark_notify(
2331 p_salesrep_id => affected_period_recs.salesrep_id
2332 , p_period_id => affected_period_recs.period_id
2333 , p_start_date => NULL
2334 , p_end_date => NULL
2335 , p_quota_id => affected_period_recs.quota_id
2336 , p_revert_to_state => 'CALC'
2337 , p_event_log_id => l_event_log_id
2338 , p_org_id => p_org_id
2339 );
2340
2341 IF (dependent_pe_tbl.COUNT > 0) THEN
2342 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2343 OPEN l_pe_cursor(affected_period_recs.salesrep_id, affected_period_recs.period_id, dependent_pe_tbl(i));
2344 FETCH l_pe_cursor INTO temp_quota_id;
2345
2346 IF l_pe_cursor%FOUND THEN
2347 cn_mark_events_pkg.mark_notify(
2348 p_salesrep_id => affected_period_recs.salesrep_id
2349 , p_period_id => affected_period_recs.period_id
2350 , p_start_date => NULL
2351 , p_end_date => NULL
2352 , p_quota_id => dependent_pe_tbl(i)
2353 , p_revert_to_state => 'CALC'
2354 , p_event_log_id => l_event_log_id
2355 , p_org_id => p_org_id
2356 );
2357 END IF;
2358
2359 CLOSE l_pe_cursor;
2360 END LOOP;
2361 END IF; -- If (dependent_pe_tbl.count > 0)
2362 END LOOP;
2363 END LOOP;
2364 END IF;
2365
2366 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2367 fnd_log.STRING(
2368 fnd_log.level_procedure
2369 , 'cn.plsql.cn_mark_events_pkg.mark_event_rt_quota.end'
2370 , 'End of mark_event_rt_quota.'
2371 );
2372 END IF;
2373 EXCEPTION
2374 WHEN OTHERS THEN
2375 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2376 fnd_log.STRING(
2377 fnd_log.level_unexpected
2378 , 'cn.plsql.cn_mark_events_pkg.mark_event_rt_quota.exception'
2379 , SQLERRM
2380 );
2381 END IF;
2382
2383 RAISE;
2384 END mark_event_rt_quota;
2385
2386 -- Start of Comments
2387 -- name : mark_event_trx_factor
2388 -- Type : None
2389 -- Pre-reqs : None.
2390 -- Usage : Procedure to Mark the cn_trx_factors Event
2391 -- Parameters :
2392 -- IN : p_event_name IN VARCHAR2
2393 -- p_object_name IN VARCHAR2
2394 -- p_object_id IN NUMBER
2395 -- p_start_date IN DATE
2396 -- p_start_Date_old IN DATE
2397 -- p_end_date IN DATE
2398 -- p_end_date_old IN DATE
2399 --
2400 -- Version : Current version 1.0
2401 -- Initial version 1.0
2402 -- Case 9: whenever there is update in the cn_trx_factors
2403 -- it marks the affected salesrep for that quotas.
2404 -- called from trigger
2405 --
2406 PROCEDURE mark_event_trx_factor(
2407 p_event_name VARCHAR2
2408 , p_object_name VARCHAR2
2409 , p_object_id NUMBER
2410 , p_start_date DATE
2411 , p_start_date_old DATE
2412 , p_end_date DATE
2413 , p_end_date_old DATE
2414 , p_org_id NUMBER
2415 ) IS
2416 l_event_log_id NUMBER;
2417 l_start_period_id NUMBER(15);
2418 l_end_period_id NUMBER(15);
2419 l_start_date DATE;
2420 l_end_date DATE;
2421
2422 CURSOR affected_srp_period_curs(
2423 l_start_period_id NUMBER
2424 , l_end_period_id NUMBER
2425 , l_start_date DATE
2426 , l_end_date DATE
2427 ) IS
2428 -- modified by rjin 11/10/1999 add distinct
2429 SELECT DISTINCT spq.salesrep_id
2430 , spq.period_id
2431 , spq.quota_id
2432 , DECODE(acc.period_id, l_start_period_id, l_start_date, acc.start_date)
2433 start_date
2434 , DECODE(
2435 acc.period_id
2436 , l_end_period_id, NVL(l_end_date, acc.end_date)
2437 , acc.end_date
2438 ) end_date
2439 FROM cn_srp_period_quotas_all spq
2440 , cn_srp_intel_periods_all sip
2441 , cn_period_statuses_all acc
2442 WHERE spq.quota_id = p_object_id
2443 AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2444 AND sip.salesrep_id = spq.salesrep_id
2445 AND sip.period_id = spq.period_id
2446 AND sip.org_id = spq.org_id
2447 AND sip.processing_status_code <> 'CLEAN'
2448 AND acc.period_id = spq.period_id
2449 AND acc.org_id = spq.org_id
2450 AND acc.period_status IN('O', 'F');
2451
2452 CURSOR l_quota_dates_csr IS
2453 SELECT start_date
2454 , end_date
2455 FROM cn_quotas_all
2456 WHERE quota_id = p_object_id;
2457
2458 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
2459 SELECT quota_id
2460 FROM cn_srp_period_quotas_all
2461 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
2462
2463 temp_quota_id cn_quotas.quota_id%TYPE;
2464 l_return_status VARCHAR2(50);
2465 l_msg_count NUMBER;
2466 l_msg_data VARCHAR2(2000);
2467 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
2468 BEGIN
2469 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2470 RETURN;
2471 END IF;
2472
2473 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2474 fnd_log.STRING(
2475 fnd_log.level_procedure
2476 , 'cn.plsql.cn_mark_events_pkg.mark_event_trx_factor.begin'
2477 , 'Beginning of mark_event_trx_factor ...'
2478 );
2479 END IF;
2480
2481 cn_mark_events_pkg.log_event(
2482 p_event_name => p_event_name
2483 , p_object_name => p_object_name
2484 , p_object_id => p_object_id
2485 , p_start_date => p_start_date
2486 , p_start_date_old => p_start_date_old
2487 , p_end_date => p_end_date
2488 , p_end_date_old => p_end_date_old
2489 , x_event_log_id => l_event_log_id
2490 , p_org_id => p_org_id
2491 );
2492
2493 --
2494 -- Check the Event Name
2495 --
2496 IF p_event_name = 'CHANGE_QUOTA_POP' THEN
2497 -- 1. update cn_trx_factors.event_factor
2498 OPEN l_quota_dates_csr;
2499 FETCH l_quota_dates_csr INTO l_start_date, l_end_date;
2500 CLOSE l_quota_dates_csr;
2501
2502 l_start_period_id := cn_api.get_acc_period_id(l_start_date, p_org_id);
2503 l_end_period_id := cn_api.get_acc_period_id(l_end_date, p_org_id);
2504
2505 -- clku, move get_parent_plan_elts outside the period/salesrep loop
2506 IF (p_object_id IS NOT NULL) THEN
2507 cn_calc_sql_exps_pvt.get_parent_plan_elts(
2508 p_api_version => 1.0
2509 , p_node_type => 'P'
2510 , p_init_msg_list => 'T'
2511 , p_node_id => p_object_id
2512 , x_plan_elt_id_tbl => dependent_pe_tbl
2513 , x_return_status => l_return_status
2514 , x_msg_count => l_msg_count
2515 , x_msg_data => l_msg_data
2516 );
2517 END IF;
2518
2519 FOR affected_recs IN affected_srp_period_curs(l_start_period_id, l_end_period_id
2520 , l_start_date, l_end_date) LOOP
2521 -- modified by rjin 11/10/1999
2522 -- since all affected period (including subsequent periods)
2523 -- are garaunteed to be marked, so we only need to mark 'NEW'
2524 cn_mark_events_pkg.mark_notify(
2525 p_salesrep_id => affected_recs.salesrep_id
2526 , p_period_id => affected_recs.period_id
2527 , p_start_date => affected_recs.start_date
2528 , p_end_date => affected_recs.end_date
2529 , p_quota_id => affected_recs.quota_id
2530 , p_revert_to_state => 'POP'
2531 , p_event_log_id => l_event_log_id
2532 , p_mode => 'NEW'
2533 , p_org_id => p_org_id
2534 );
2535
2536 IF (dependent_pe_tbl.COUNT > 0) THEN
2537 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
2538 OPEN l_pe_cursor(affected_recs.salesrep_id, affected_recs.period_id, dependent_pe_tbl(i));
2539 FETCH l_pe_cursor INTO temp_quota_id;
2540
2541 IF l_pe_cursor%FOUND THEN
2542 cn_mark_events_pkg.mark_notify(
2543 p_salesrep_id => affected_recs.salesrep_id
2544 , p_period_id => affected_recs.period_id
2545 , p_start_date => affected_recs.start_date
2546 , p_end_date => affected_recs.end_date
2547 , p_quota_id => dependent_pe_tbl(i)
2548 , p_revert_to_state => 'POP'
2549 , p_event_log_id => l_event_log_id
2550 , p_mode => 'NEW'
2551 , p_org_id => p_org_id
2552 );
2553 END IF;
2554
2555 CLOSE l_pe_cursor;
2556 END LOOP;
2557 END IF; -- If (dependent_pe_tbl.count > 0)
2558 END LOOP;
2559 END IF;
2560
2561 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2562 fnd_log.STRING(
2563 fnd_log.level_procedure
2564 , 'cn.plsql.cn_mark_events_pkg.mark_event_trx_factor.end'
2565 , 'End of mark_event_trx_factor.'
2566 );
2567 END IF;
2568 EXCEPTION
2569 WHEN OTHERS THEN
2570 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2571 fnd_log.STRING(
2572 fnd_log.level_unexpected
2573 , 'cn.plsql.cn_mark_events_pkg.mark_event_trx_factor.exception'
2574 , SQLERRM
2575 );
2576 END IF;
2577
2578 RAISE;
2579 END mark_event_trx_factor;
2580
2581 --
2582 -- Procedure Name
2583 -- mark_event_role_plans
2584 -- Purpose
2585 -- Insert affected salesrep information into cn_event_log and cn_notify_log
2586 -- for recalculation purpose.
2587 -- Calls log_event, mark_notify.
2588 -- Called by cn_role_plans_t trigger.
2589 -- History
2590 -- 09/13/99 Harlen Chen Created
2591 PROCEDURE mark_event_role_plans(
2592 p_event_name VARCHAR2
2593 , p_object_name VARCHAR2
2594 , p_object_id NUMBER
2595 , p_start_date DATE
2596 , p_start_date_old DATE
2597 , p_end_date DATE
2598 , p_end_date_old DATE
2599 , p_org_id NUMBER
2600 ) IS
2601 l_role_id cn_role_plans.role_id%TYPE;
2602 l_event_log_id NUMBER;
2603 l_start_period_id NUMBER(15);
2604 l_end_period_id NUMBER(15);
2605 l_date_range_tbl cn_api.date_range_tbl_type;
2606
2607 CURSOR affected_srp_period(l_s_date DATE, l_e_date DATE) IS
2608 -- for CHANGE_SRP_ROLE_PLAN
2609 -- use the start_date/end_date info to restrict the periods affected.
2610
2611 -- clku perf fix for bug 3628870, removed the hintsto avoid FTS
2612 SELECT sr.salesrep_id salesrep_id
2613 , acc.period_id period_id
2614 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2615 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2616 end_date
2617 FROM cn_srp_roles sr, cn_srp_intel_periods intel, cn_period_statuses acc
2618 WHERE sr.role_id = l_role_id
2619 AND sr.org_id = p_org_id
2620 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2621 AND acc.period_status = 'O'
2622 AND acc.org_id = p_org_id
2623 AND intel.salesrep_id = sr.salesrep_id
2624 AND intel.period_id = acc.period_id
2625 AND intel.org_id = p_org_id
2626 AND intel.processing_status_code <> 'CLEAN';
2627 BEGIN
2628 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2629 RETURN;
2630 END IF;
2631
2632 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2633 fnd_log.STRING(
2634 fnd_log.level_procedure
2635 , 'cn.plsql.cn_mark_events_pkg.mark_event_role_plans.begin'
2636 , 'Beginning of mark_event_role_plans ...'
2637 );
2638 END IF;
2639
2640 l_role_id := p_object_id;
2641 cn_mark_events_pkg.log_event(
2642 p_event_name
2643 , p_object_name
2644 , p_object_id
2645 , p_start_date
2646 , p_start_date_old
2647 , p_end_date
2648 , p_end_date_old
2649 , l_event_log_id
2650 , p_org_id
2651 );
2652
2653 IF p_event_name = 'CHANGE_SRP_ROLE_PLAN' THEN
2654 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2655 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2656
2657 FOR l_rec IN affected_srp_period(p_start_date_old, p_end_date_old) LOOP
2658 -- For ROLL events, pass in start_date/end_date, pass null to p_quota_id
2659 cn_mark_events_pkg.mark_notify(
2660 l_rec.salesrep_id
2661 , l_rec.period_id
2662 , l_rec.start_date
2663 , l_rec.end_date
2664 , NULL
2665 , -- p_quota_id
2666 'ROLL'
2667 , l_event_log_id
2668 , p_org_id
2669 );
2670 END LOOP;
2671 ELSIF p_event_name = 'CHANGE_SRP_ROLE_PLAN_DATE' THEN
2672 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
2673 , l_date_range_tbl);
2674
2675 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
2676 --bug fix 6890504 raj
2677 l_start_period_id :=
2678 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date - 1, p_org_id);
2679 l_end_period_id := cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
2680
2681 FOR l_rec IN affected_srp_period(l_date_range_tbl(l_ctr).start_date - 1
2682 , l_date_range_tbl(l_ctr).end_date) LOOP
2683 -- ROLL events : pass in start_date/end_date, pass null to p_quota_id
2684 cn_mark_events_pkg.mark_notify(
2685 l_rec.salesrep_id
2686 , l_rec.period_id
2687 , l_rec.start_date
2688 , l_rec.end_date
2689 , NULL
2690 , -- p_quota_id
2691 'ROLL'
2692 , l_event_log_id
2693 , p_org_id
2694 );
2695 END LOOP;
2696 END LOOP;
2697 END IF;
2698
2699 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2700 fnd_log.STRING(
2701 fnd_log.level_procedure
2702 , 'cn.plsql.cn_mark_events_pkg.mark_event_role_plans.end'
2703 , 'End of mark_event_role_plans.'
2704 );
2705 END IF;
2706 EXCEPTION
2707 WHEN OTHERS THEN
2708 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2709 fnd_log.STRING(
2710 fnd_log.level_unexpected
2711 , 'cn.plsql.cn_mark_events_pkg.mark_event_role_plans.exception'
2712 , SQLERRM
2713 );
2714 END IF;
2715
2716 RAISE;
2717 END mark_event_role_plans;
2718
2719 --
2720 -- Procedure Name
2721 -- mark_event_srp_paygroup
2722 -- Purpose
2723 -- Insert affected salesrep information into cn_event_log and cn_notify_log files
2724 -- for recalculation purpose. Called from cn_paygroup_pub
2725 -- History
2726 -- 01/24/03 clku created
2727 PROCEDURE mark_event_srp_pay_group(
2728 p_event_name VARCHAR2
2729 , p_object_name VARCHAR2
2730 , p_object_id NUMBER
2731 , p_srp_object_id NUMBER
2732 , p_start_date DATE
2733 , p_start_date_old DATE
2734 , p_end_date DATE
2735 , p_end_date_old DATE
2736 , p_org_id NUMBER
2737 ) IS
2738 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2739 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
2740 l_event_log_id NUMBER;
2741 l_start_period_id NUMBER(15);
2742 l_end_period_id NUMBER(15);
2743 l_date_range_tbl cn_api.date_range_tbl_type;
2744
2745 CURSOR affected_srp_period(l_s_date DATE, l_e_date DATE) IS
2746 -- for CHANGE_SRP_ROLE_PLAN
2747 -- use the start_date/end_date info to restrict the periods affected.
2748 -- clku, perf fix for bug 3628870, removed hints to avoid FTS
2749 SELECT intel.salesrep_id salesrep_id
2750 , acc.period_id period_id
2751 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2752 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2753 end_date
2754 FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2755 WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2756 AND acc.org_id = p_org_id
2757 AND acc.period_status = 'O'
2758 AND intel.salesrep_id = l_salesrep_id
2759 AND intel.period_id = acc.period_id
2760 AND intel.org_id = acc.org_id
2761 AND intel.processing_status_code <> 'CLEAN';
2762 BEGIN
2763 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2764 RETURN;
2765 END IF;
2766
2767 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2768 fnd_log.STRING(
2769 fnd_log.level_procedure
2770 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_group.begin'
2771 , 'Beginning of mark_event_spr_pay_group ...'
2772 );
2773 END IF;
2774
2775 l_pay_group_id := p_object_id;
2776 l_salesrep_id := p_srp_object_id;
2777 cn_mark_events_pkg.log_event(
2778 p_event_name
2779 , p_object_name
2780 , p_object_id
2781 , p_start_date
2782 , p_start_date_old
2783 , p_end_date
2784 , p_end_date_old
2785 , l_event_log_id
2786 , p_org_id
2787 );
2788
2789 IF p_event_name = 'CHANGE_SRP_PAY_GROUP' THEN
2790 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2791 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2792
2793 FOR l_rec IN affected_srp_period(p_start_date_old, p_end_date_old) LOOP
2794 -- For ROLL events, pass in start_date/end_date, pass null to p_quota_id
2795 cn_mark_events_pkg.mark_notify(
2796 l_rec.salesrep_id
2797 , l_rec.period_id
2798 , l_rec.start_date
2799 , l_rec.end_date
2800 , NULL
2801 , -- p_quota_id
2802 'ROLL'
2803 , l_event_log_id
2804 , p_org_id
2805 );
2806 END LOOP;
2807 ELSIF p_event_name = 'CHANGE_SRP_PAY_GROUP_DATE' THEN
2808 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
2809 , l_date_range_tbl);
2810
2811 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
2812 l_start_period_id :=
2813 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date - 1, p_org_id);
2814 l_end_period_id := cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
2815
2816 FOR l_rec IN affected_srp_period(l_date_range_tbl(l_ctr).start_date - 1
2817 , l_date_range_tbl(l_ctr).end_date) LOOP
2818 -- ROLL events : pass in start_date/end_date, pass null to p_quota_id
2819 cn_mark_events_pkg.mark_notify(
2820 l_rec.salesrep_id
2821 , l_rec.period_id
2822 , l_rec.start_date
2823 , l_rec.end_date
2824 , NULL
2825 , -- p_quota_id
2826 'ROLL'
2827 , l_event_log_id
2828 , p_org_id
2829 );
2830 END LOOP;
2831 END LOOP;
2832 END IF;
2833
2834 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2835 fnd_log.STRING(
2836 fnd_log.level_procedure
2837 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_group.end'
2838 , 'End of mark_event_srp_pay_group.'
2839 );
2840 END IF;
2841 EXCEPTION
2842 WHEN OTHERS THEN
2843 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2844 fnd_log.STRING(
2845 fnd_log.level_unexpected
2846 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_group.exception'
2847 , SQLERRM
2848 );
2849 END IF;
2850
2851 RAISE;
2852 END mark_event_srp_pay_group;
2853
2854 --
2855 -- Procedure Name
2856 -- mark_event_srp_roles
2857 -- Purpose
2858 -- Insert affected salesrep information into cn_event_log and cn_notify_log
2859 -- for recalculation purpose.
2860 -- Calls log_event, mark_notify.
2861 -- Called by cn_srp_rolens_t trigger.
2862 -- History
2863 -- 09/20/99 Harlen Chen Created
2864 PROCEDURE mark_event_srp_roles(
2865 p_event_name VARCHAR2
2866 , p_object_name VARCHAR2
2867 , p_object_id NUMBER
2868 , p_start_date DATE
2869 , p_start_date_old DATE
2870 , p_end_date DATE
2871 , p_end_date_old DATE
2872 , p_org_id NUMBER
2873 ) IS
2874 l_salesrep_id cn_srp_roles.salesrep_id%TYPE;
2875 l_event_log_id NUMBER;
2876 l_start_period_id NUMBER(15);
2877 l_end_period_id NUMBER(15);
2878 l_date_range_tbl cn_api.date_range_tbl_type;
2879
2880 CURSOR affected_srp_period(l_s_date DATE, l_e_date DATE) IS
2881 -- for CHANGE_SRP_ROLE_PLAN
2882 -- use the start_date/end_date info to restrict the periods affected.
2883 SELECT l_salesrep_id salesrep_id
2884 , acc.period_id period_id
2885 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2886 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2887 end_date
2888 FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2889 WHERE acc.org_id = p_org_id
2890 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2891 AND acc.period_status = 'O'
2892 AND intel.salesrep_id = l_salesrep_id
2893 AND intel.period_id = acc.period_id
2894 AND intel.org_id = acc.org_id
2895 AND intel.processing_status_code <> 'CLEAN';
2896 BEGIN
2897 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
2898 RETURN;
2899 END IF;
2900
2901 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2902 fnd_log.STRING(
2903 fnd_log.level_procedure
2904 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_roles.begin'
2905 , 'Beginning of mark_event_srp_roles ...'
2906 );
2907 END IF;
2908
2909 l_salesrep_id := p_object_id;
2910 cn_mark_events_pkg.log_event(
2911 p_event_name
2912 , p_object_name
2913 , p_object_id
2914 , p_start_date
2915 , p_start_date_old
2916 , p_end_date
2917 , p_end_date_old
2918 , l_event_log_id
2919 , p_org_id
2920 );
2921
2922 IF p_event_name = 'CHANGE_SRP_ROLE_PLAN' THEN
2923 l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
2924 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
2925
2926 FOR l_rec IN affected_srp_period(p_start_date_old, p_end_date_old) LOOP
2927 -- For ROLL events, pass in start_date/end_date, pass null to p_quota_id
2928
2929 -- modified by rjin 11/10/1999
2930 -- since all affected period (including subsequent periods)
2931 -- are garaunteed to be marked, so we only need to mark 'NEW'
2932 mark_notify(
2933 p_salesrep_id => l_rec.salesrep_id
2934 , p_period_id => l_rec.period_id
2935 , p_start_date => l_rec.start_date
2936 , p_end_date => l_rec.end_date
2937 , p_quota_id => NULL
2938 , p_revert_to_state => 'ROLL'
2939 , p_event_log_id => l_event_log_id
2940 , p_mode => 'NEW'
2941 , p_org_id => p_org_id
2942 );
2943 END LOOP;
2944 ELSIF p_event_name = 'CHANGE_SRP_ROLE_PLAN_DATE' THEN
2945 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
2946 , l_date_range_tbl);
2947
2948 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
2949 l_start_period_id :=
2950 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date, p_org_id);
2951 l_end_period_id := cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
2952
2953 FOR l_rec IN affected_srp_period(l_date_range_tbl(l_ctr).start_date
2954 , l_date_range_tbl(l_ctr).end_date) LOOP
2955 -- ROLL events : pass in start_date/end_date, pass null to p_quota_id
2956 cn_mark_events_pkg.mark_notify(
2957 l_rec.salesrep_id
2958 , l_rec.period_id
2959 , l_rec.start_date
2960 , l_rec.end_date
2961 , NULL
2962 , -- p_quota_id
2963 'ROLL'
2964 , l_event_log_id
2965 , p_org_id
2966 );
2967 END LOOP;
2968 END LOOP;
2969 END IF;
2970
2971 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2972 fnd_log.STRING(
2973 fnd_log.level_procedure
2974 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_roles.end'
2975 , 'End of mark_event_srp_roles.'
2976 );
2977 END IF;
2978 EXCEPTION
2979 WHEN OTHERS THEN
2980 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2981 fnd_log.STRING(
2982 fnd_log.level_unexpected
2983 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_roles.exception'
2984 , SQLERRM
2985 );
2986 END IF;
2987
2988 RAISE;
2989 END mark_event_srp_roles;
2990
2991 --
2992 --Start Of Comments
2993 --Purpose
2994 --This procedure marks all Sales Reps for Calculation
2995 --whenever there is a change in Formula_Status is modified
2996 -- Called from cn_calc_formulas_t1 Trigger
2997 -- This trigger fires when formula_status is updated
2998 -- COMPLETE.
2999 -- Event Fired is CHANGE_FORMULA
3000 -- History
3001 -- 09/19/99 ( Venkata) chalam Krishnan Created
3002 --End of Comments
3003 PROCEDURE mark_event_formula(
3004 p_event_name VARCHAR2
3005 , p_object_name VARCHAR2
3006 , p_object_id NUMBER
3007 , p_start_date DATE
3008 , p_start_date_old DATE
3009 , p_end_date DATE
3010 , p_end_date_old DATE
3011 , p_org_id NUMBER
3012 ) IS
3013 CURSOR affected_srp_period_quotas IS
3014 -- modified by rjin 11/10/1999 add distinct
3015 SELECT DISTINCT spq.salesrep_id
3016 , spq.period_id
3017 , spq.quota_id
3018 FROM cn_quotas_all cq, cn_srp_period_quotas_all spq
3019 , cn_srp_intel_periods_all intel
3020 WHERE cq.calc_formula_id = p_object_id
3021 AND cq.org_id = p_org_id
3022 AND spq.quota_id = cq.quota_id
3023 AND intel.salesrep_id = spq.salesrep_id
3024 AND intel.period_id = spq.period_id
3025 AND intel.org_id = spq.org_id
3026 AND intel.processing_status_code <> 'CLEAN'
3027 ORDER BY spq.quota_id;
3028
3029 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3030 SELECT quota_id
3031 FROM cn_srp_period_quotas_all
3032 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3033
3034 temp_quota_id cn_quotas.quota_id%TYPE;
3035 l_return_status VARCHAR2(50);
3036 l_msg_count NUMBER;
3037 l_msg_data VARCHAR2(2000);
3038 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3039 l_latest_quota_id NUMBER := 0;
3040 l_event_log_id NUMBER;
3041 BEGIN
3042 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3043 RETURN;
3044 END IF;
3045
3046 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3047 fnd_log.STRING(
3048 fnd_log.level_procedure
3049 , 'cn.plsql.cn_mark_events_pkg.mark_event_formula.begin'
3050 , 'Beginning of mark_event_formula ...'
3051 );
3052 END IF;
3053
3054 cn_mark_events_pkg.log_event(
3055 p_event_name => p_event_name
3056 , p_object_name => p_object_name
3057 , p_object_id => p_object_id
3058 , p_start_date => p_start_date
3059 , p_start_date_old => p_start_date_old
3060 , p_end_date => p_end_date
3061 , p_end_date_old => p_end_date_old
3062 , x_event_log_id => l_event_log_id
3063 , p_org_id => p_org_id
3064 );
3065
3066 IF (p_event_name = 'CHANGE_FORMULA') THEN
3067 FOR srp_quota IN affected_srp_period_quotas LOOP
3068 IF l_latest_quota_id <> srp_quota.quota_id THEN
3069 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3070 p_api_version => 1.0
3071 , p_node_type => 'P'
3072 , p_init_msg_list => 'T'
3073 , p_node_id => srp_quota.quota_id
3074 , x_plan_elt_id_tbl => dependent_pe_tbl
3075 , x_return_status => l_return_status
3076 , x_msg_count => l_msg_count
3077 , x_msg_data => l_msg_data
3078 );
3079 l_latest_quota_id := srp_quota.quota_id;
3080 END IF;
3081
3082 -- modified by rjin 11/10/1999
3083 -- since all affected period (including subsequent periods)
3084 -- are garaunteed to be marked, so we only need to mark 'NEW'
3085 mark_notify(
3086 p_salesrep_id => srp_quota.salesrep_id
3087 , p_period_id => srp_quota.period_id
3088 , p_start_date => NULL
3089 , p_end_date => NULL
3090 , p_quota_id => srp_quota.quota_id
3091 , p_revert_to_state => 'CALC'
3092 , p_event_log_id => l_event_log_id
3093 , p_mode => 'NEW'
3094 , p_org_id => p_org_id
3095 );
3096
3097 IF (dependent_pe_tbl.COUNT > 0) THEN
3098 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3099 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3100 FETCH l_pe_cursor INTO temp_quota_id;
3101
3102 IF l_pe_cursor%FOUND THEN
3103 cn_mark_events_pkg.mark_notify(
3104 p_salesrep_id => srp_quota.salesrep_id
3105 , p_period_id => srp_quota.period_id
3106 , p_start_date => NULL
3107 , p_end_date => NULL
3108 , p_quota_id => dependent_pe_tbl(i)
3109 , p_revert_to_state => 'CALC'
3110 , p_event_log_id => l_event_log_id
3111 , p_mode => 'NEW'
3112 , p_org_id => p_org_id
3113 );
3114 END IF;
3115
3116 CLOSE l_pe_cursor;
3117 END LOOP;
3118 END IF; -- If (dependent_pe_tbl.count > 0)
3119 END LOOP;
3120 END IF;
3121
3122 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3123 fnd_log.STRING(
3124 fnd_log.level_procedure
3125 , 'cn.plsql.cn_mark_events_pkg.mark_event_formula.end'
3126 , 'End of mark_event_formula.'
3127 );
3128 END IF;
3129 EXCEPTION
3130 WHEN OTHERS THEN
3131 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3132 fnd_log.STRING(
3133 fnd_log.level_unexpected
3134 , 'cn.plsql.cn_mark_events_pkg.mark_event_formula.exception'
3135 , SQLERRM
3136 );
3137 END IF;
3138
3139 RAISE;
3140 END mark_event_formula;
3141
3142 --
3143 --Start Of Comments
3144 --Purpose
3145 --This procedure marks all Sales Reps for Calculation
3146 --whenever there is a change in Rate Dim Tiers
3147 --1. Insert Rate Dim Tiers
3148 -- Event Fired is CHANGE_RT_INS_DEL
3149 --2. Update Rate Dim Tiers
3150 -- Event Fired is CHANGE_RT_TIER
3151 --3. Delete Rate Dim Tiers
3152 -- Event fired is CHANGE_RT_INS_DEL
3153 --History
3154 --09/19/99 ( Venkata ) chalam Krishnan Created
3155 --End of Comments
3156 PROCEDURE mark_event_rate_table(
3157 p_event_name VARCHAR2
3158 , p_object_name VARCHAR2
3159 , p_object_id NUMBER
3160 , p_start_date DATE
3161 , p_start_date_old DATE
3162 , p_end_date DATE
3163 , p_end_date_old DATE
3164 , p_org_id NUMBER
3165 ) IS
3166 CURSOR affected_srp_period_quotas IS
3167 SELECT DISTINCT spq.salesrep_id
3168 , spq.period_id
3169 , spq.quota_id
3170 FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3171 WHERE spq.quota_id IN(
3172 SELECT rt_assign.quota_id
3173 FROM cn_rate_sch_dims_all rt, cn_rt_quota_asgns_all rt_assign
3174 WHERE rt.rate_dimension_id = p_object_id
3175 AND rt_assign.rate_schedule_id = rt.rate_schedule_id)
3176 AND intel.salesrep_id = spq.salesrep_id
3177 AND intel.period_id = spq.period_id
3178 AND intel.org_id = spq.org_id
3179 AND intel.processing_status_code <> 'CLEAN'
3180 ORDER BY spq.quota_id;
3181
3182 l_event_log_id NUMBER;
3183
3184 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3185 SELECT quota_id
3186 FROM cn_srp_period_quotas_all
3187 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3188
3189 temp_quota_id cn_quotas.quota_id%TYPE;
3190 l_return_status VARCHAR2(50);
3191 l_msg_count NUMBER;
3192 l_msg_data VARCHAR2(2000);
3193 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3194 l_latest_quota_id NUMBER := 0;
3195 BEGIN
3196 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3197 RETURN;
3198 END IF;
3199
3200 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3201 fnd_log.STRING(
3202 fnd_log.level_procedure
3203 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_table.begin'
3204 , 'Beginning of mark_event_rate_table ...'
3205 );
3206 END IF;
3207
3208 cn_mark_events_pkg.log_event(
3209 p_event_name => p_event_name
3210 , p_object_name => p_object_name
3211 , p_object_id => p_object_id
3212 , p_start_date => p_start_date
3213 , p_start_date_old => p_start_date_old
3214 , p_end_date => p_end_date
3215 , p_end_date_old => p_end_date_old
3216 , x_event_log_id => l_event_log_id
3217 , p_org_id => p_org_id
3218 );
3219
3220 IF (p_event_name IN('CHANGE_RT_TIER', 'CHANGE_RT_TIER_INS_DEL')) THEN
3221 FOR srp_quota IN affected_srp_period_quotas LOOP
3222 IF l_latest_quota_id <> srp_quota.quota_id THEN
3223 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3224 p_api_version => 1.0
3225 , p_node_type => 'P'
3226 , p_init_msg_list => 'T'
3227 , p_node_id => srp_quota.quota_id
3228 , x_plan_elt_id_tbl => dependent_pe_tbl
3229 , x_return_status => l_return_status
3230 , x_msg_count => l_msg_count
3231 , x_msg_data => l_msg_data
3232 );
3233 l_latest_quota_id := srp_quota.quota_id;
3234 END IF;
3235
3236 -- modified by rjin 11/10/1999
3237 -- since all affected period (including subsequent periods)
3238 -- are garaunteed to be marked, so we only need to mark 'NEW'
3239 mark_notify(
3240 p_salesrep_id => srp_quota.salesrep_id
3241 , p_period_id => srp_quota.period_id
3242 , p_start_date => NULL
3243 , p_end_date => NULL
3244 , p_quota_id => srp_quota.quota_id
3245 , p_revert_to_state => 'CALC'
3246 , p_event_log_id => l_event_log_id
3247 , p_mode => 'NEW'
3248 , p_org_id => p_org_id
3249 );
3250
3251 IF (dependent_pe_tbl.COUNT > 0) THEN
3252 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3253 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3254 FETCH l_pe_cursor INTO temp_quota_id;
3255
3256 IF l_pe_cursor%FOUND THEN
3257 cn_mark_events_pkg.mark_notify(
3258 p_salesrep_id => srp_quota.salesrep_id
3259 , p_period_id => srp_quota.period_id
3260 , p_start_date => NULL
3261 , p_end_date => NULL
3262 , p_quota_id => dependent_pe_tbl(i)
3263 , p_revert_to_state => 'CALC'
3264 , p_event_log_id => l_event_log_id
3265 , p_mode => 'NEW'
3266 , p_org_id => p_org_id
3267 );
3268 END IF;
3269
3270 CLOSE l_pe_cursor;
3271 END LOOP;
3272 END IF; -- If (dependent_pe_tbl.count > 0)
3273 END LOOP;
3274 END IF;
3275
3276 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3277 fnd_log.STRING(
3278 fnd_log.level_procedure
3279 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_table.end'
3280 , 'End of mark_event_rate_table.'
3281 );
3282 END IF;
3283 EXCEPTION
3284 WHEN OTHERS THEN
3285 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3286 fnd_log.STRING(
3287 fnd_log.level_unexpected
3288 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_table.exception'
3289 , SQLERRM
3290 );
3291 END IF;
3292
3293 RAISE;
3294 END mark_event_rate_table;
3295
3296 --
3297 -- Start Of Comments
3298 -- Purpose:
3299 -- This procedure marks all Sales Reps for Calculation
3300 -- whenever there is a change in Rate Tiers (Commission Rates).
3301 --
3302 -- 1. Insert Rate Dim Tiers
3303 -- Event Fired is CHANGE_RT_INS_DEL
3304 -- 2. Update Rate Dim Tiers
3305 -- Event Fired is CHANGE_RT_TIER
3306 -- 3. Delete Rate Dim Tiers
3307 -- Event fired is CHANGE_RT_INS_DEL
3308 --
3309 -- History
3310 -- 29/08/08 (venjayar) jVenki Created
3311 --
3312 -- End of Comments
3313 PROCEDURE mark_event_rate_tier_table(
3314 p_event_name VARCHAR2
3315 , p_object_name VARCHAR2
3316 , p_object_id NUMBER
3317 , p_dep_object_id NUMBER
3318 , p_start_date DATE
3319 , p_start_date_old DATE
3320 , p_end_date DATE
3321 , p_end_date_old DATE
3322 , p_org_id NUMBER
3323 ) IS
3324 CURSOR affected_srp_period_quotas IS
3325 SELECT DISTINCT spq.salesrep_id
3326 , spq.period_id
3327 , spq.quota_id
3328 FROM cn_srp_period_quotas spq, cn_srp_intel_periods intel
3329 WHERE spq.quota_id IN(
3330 SELECT rt_assign.quota_id
3331 FROM cn_rt_quota_asgns rt_assign
3332 WHERE rt_assign.rate_schedule_id = p_dep_object_id)
3333 AND intel.salesrep_id = spq.salesrep_id
3334 AND intel.period_id = spq.period_id
3335 AND intel.processing_status_code <> 'CLEAN'
3336 ORDER BY spq.quota_id;
3337
3338 l_event_log_id NUMBER;
3339
3340 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3341 SELECT quota_id
3342 FROM cn_srp_period_quotas
3343 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3344
3345 temp_quota_id cn_quotas.quota_id%TYPE;
3346 l_return_status VARCHAR2(50);
3347 l_msg_count NUMBER;
3348 l_msg_data VARCHAR2(2000);
3349 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3350 l_latest_quota_id NUMBER := 0;
3351 BEGIN
3352 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3353 RETURN;
3354 END IF;
3355
3356 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3357 fnd_log.STRING(
3358 fnd_log.level_procedure
3359 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_tier_table.begin'
3360 , 'Beginning of mark_event_rate_tier_table...'
3361 );
3362 END IF;
3363
3364 cn_mark_events_pkg.log_event(
3365 p_event_name => p_event_name
3366 , p_object_name => p_object_name
3367 , p_object_id => p_object_id
3368 , p_start_date => p_start_date
3369 , p_start_date_old => p_start_date_old
3370 , p_end_date => p_end_date
3371 , p_end_date_old => p_end_date_old
3372 , x_event_log_id => l_event_log_id
3373 , p_org_id => p_org_id
3374 );
3375
3376 IF (p_event_name IN('CHANGE_RT_TIER', 'CHANGE_RT_TIER_INS_DEL')) THEN
3377 FOR srp_quota IN affected_srp_period_quotas LOOP
3378 IF l_latest_quota_id <> srp_quota.quota_id THEN
3379 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3380 p_api_version => 1.0
3381 , p_node_type => 'P'
3382 , p_init_msg_list => 'T'
3383 , p_node_id => srp_quota.quota_id
3384 , x_plan_elt_id_tbl => dependent_pe_tbl
3385 , x_return_status => l_return_status
3386 , x_msg_count => l_msg_count
3387 , x_msg_data => l_msg_data
3388 );
3389 l_latest_quota_id := srp_quota.quota_id;
3390 END IF;
3391
3392 -- since all affected period (including subsequent periods)
3393 -- are garaunteed to be marked, so we only need to mark 'NEW'
3394 mark_notify(
3395 p_salesrep_id => srp_quota.salesrep_id
3396 , p_period_id => srp_quota.period_id
3397 , p_start_date => NULL
3398 , p_end_date => NULL
3399 , p_quota_id => srp_quota.quota_id
3400 , p_revert_to_state => 'CALC'
3401 , p_event_log_id => l_event_log_id
3402 , p_mode => 'NEW'
3403 , p_org_id => p_org_id
3404 );
3405
3406 IF (dependent_pe_tbl.COUNT > 0) THEN
3407 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3408 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3409 FETCH l_pe_cursor INTO temp_quota_id;
3410
3411 IF l_pe_cursor%FOUND THEN
3412 cn_mark_events_pkg.mark_notify(
3413 p_salesrep_id => srp_quota.salesrep_id
3414 , p_period_id => srp_quota.period_id
3415 , p_start_date => NULL
3416 , p_end_date => NULL
3417 , p_quota_id => dependent_pe_tbl(i)
3418 , p_revert_to_state => 'CALC'
3419 , p_event_log_id => l_event_log_id
3420 , p_mode => 'NEW'
3421 , p_org_id => p_org_id
3422 );
3423 END IF;
3424
3425 CLOSE l_pe_cursor;
3426 END LOOP;
3427 END IF; -- If (dependent_pe_tbl.count > 0)
3428 END LOOP;
3429 END IF;
3430
3431 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3432 fnd_log.STRING(
3433 fnd_log.level_procedure
3434 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_tier_table.end'
3435 , 'End of mark_event_rate_tier_table.'
3436 );
3437 END IF;
3438 EXCEPTION
3439 WHEN OTHERS THEN
3440 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3441 fnd_log.STRING(
3442 fnd_log.level_unexpected
3443 , 'cn.plsql.cn_mark_events_pkg.mark_event_rate_tier_table.exception'
3444 , SQLERRM
3445 );
3446 END IF;
3447
3448 RAISE;
3449 END mark_event_rate_tier_table;
3450
3451
3452 -- Purpose
3453 -- the auxiliary procedure for mark_event_interval_number
3454 -- History
3455 -- created on 9/27/1999 by ymao
3456 PROCEDURE mark_notify_interval_number(
3457 p_event_name VARCHAR2
3458 , p_interval_type_id NUMBER
3459 , p_period_id NUMBER
3460 , p_start_date DATE
3461 , p_end_date DATE
3462 , p_event_log_id NUMBER
3463 , p_org_id NUMBER
3464 ) IS
3465 CURSOR affected_srp_period_quotas IS
3466 SELECT DISTINCT spq.salesrep_id
3467 , spq.period_id
3468 , spq.quota_id
3469 FROM cn_quotas_all q, cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3470 WHERE q.interval_type_id = p_interval_type_id
3471 AND q.org_id = p_org_id
3472 AND p_end_date >= q.start_date
3473 AND (q.end_date IS NULL OR p_start_date <= q.end_date)
3474 AND spq.quota_id = q.quota_id
3475 AND spq.period_id = p_period_id
3476 AND intel.salesrep_id = spq.salesrep_id
3477 AND intel.period_id = p_period_id
3478 AND intel.org_id = spq.org_id
3479 AND intel.processing_status_code <> 'CLEAN'
3480 ORDER BY spq.quota_id;
3481
3482 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3483 SELECT quota_id
3484 FROM cn_srp_period_quotas_all
3485 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3486
3487 temp_quota_id cn_quotas.quota_id%TYPE;
3488 l_return_status VARCHAR2(50);
3489 l_msg_count NUMBER;
3490 l_msg_data VARCHAR2(2000);
3491 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3492 l_latest_quota_id NUMBER := 0;
3493 BEGIN
3494 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3495 fnd_log.STRING(
3496 fnd_log.level_procedure
3497 , 'cn.plsql.cn_mark_events_pkg.mark_notify_interval_number.begin'
3498 , 'Beginning of mark_notify_interval_number ...'
3499 );
3500 END IF;
3501
3502 IF (p_event_name = 'CHANGE_PERIOD_INTERVAL_NUMBER') THEN
3503 FOR srp_quota IN affected_srp_period_quotas LOOP
3504 IF l_latest_quota_id <> srp_quota.quota_id THEN
3505 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3506 p_api_version => 1.0
3507 , p_node_type => 'P'
3508 , p_init_msg_list => 'T'
3509 , p_node_id => srp_quota.quota_id
3510 , x_plan_elt_id_tbl => dependent_pe_tbl
3511 , x_return_status => l_return_status
3512 , x_msg_count => l_msg_count
3513 , x_msg_data => l_msg_data
3514 );
3515 l_latest_quota_id := srp_quota.quota_id;
3516 END IF;
3517
3518 -- modified by rjin 11/10/1999
3519 -- since all affected period (including subsequent periods)
3520 -- are garaunteed to be marked, so we only need to mark 'NEW'
3521 mark_notify(
3522 p_salesrep_id => srp_quota.salesrep_id
3523 , p_period_id => srp_quota.period_id
3524 , p_start_date => p_start_date
3525 , --NULL,
3526 p_end_date => p_end_date
3527 , --NULL,
3528 p_quota_id => srp_quota.quota_id
3529 , p_revert_to_state => 'CALC'
3530 , p_event_log_id => p_event_log_id
3531 , p_mode => 'NEW'
3532 , p_org_id => p_org_id
3533 );
3534
3535 IF (dependent_pe_tbl.COUNT > 0) THEN
3536 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3537 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3538 FETCH l_pe_cursor INTO temp_quota_id;
3539
3540 IF l_pe_cursor%FOUND THEN
3541 cn_mark_events_pkg.mark_notify(
3542 p_salesrep_id => srp_quota.salesrep_id
3543 , p_period_id => srp_quota.period_id
3544 , p_start_date => p_start_date
3545 , p_end_date => p_end_date
3546 , p_quota_id => dependent_pe_tbl(i)
3547 , p_revert_to_state => 'CALC'
3548 , p_event_log_id => p_event_log_id
3549 , p_mode => 'NEW'
3550 , p_org_id => p_org_id
3551 );
3552 END IF;
3553
3554 CLOSE l_pe_cursor;
3555 END LOOP;
3556 END IF; -- If (dependent_pe_tbl.count > 0)
3557 END LOOP;
3558 END IF;
3559
3560 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3561 fnd_log.STRING(
3562 fnd_log.level_procedure
3563 , 'cn.plsql.cn_mark_events_pkg.mark_notify_interval_number.end'
3564 , 'End of mark_notify_interval_number.'
3565 );
3566 END IF;
3567 EXCEPTION
3568 WHEN OTHERS THEN
3569 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3570 fnd_log.STRING(
3571 fnd_log.level_unexpected
3572 , 'cn.plsql.cn_mark_events_pkg.mark_notify_interval_number.exception'
3573 , SQLERRM
3574 );
3575 END IF;
3576
3577 RAISE;
3578 END mark_notify_interval_number;
3579
3580 -- Purpose
3581 -- Upon the change of any interval number, mark all the sales reps that might be affected
3582 -- in terms of calculation
3583 -- History
3584 -- created on 9/27/99 by ymao
3585 PROCEDURE mark_event_interval_number(
3586 p_event_name VARCHAR2
3587 , p_object_name VARCHAR2
3588 , p_object_id NUMBER
3589 , p_start_date DATE
3590 , p_start_date_old DATE
3591 , p_end_date DATE
3592 , p_end_date_old DATE
3593 , p_interval_type_id NUMBER
3594 , p_old_interval_number NUMBER
3595 , p_new_interval_number NUMBER
3596 , p_org_id NUMBER
3597 ) IS
3598 l_event_log_id NUMBER(15);
3599
3600 CURSOR affected_periods IS
3601 SELECT cpit.cal_period_id
3602 , ps.start_date
3603 , ps.end_date
3604 FROM cn_cal_per_int_types_all cpit, cn_period_statuses_all ps
3605 WHERE (
3606 cpit.interval_number = p_old_interval_number
3607 OR cpit.interval_number = p_new_interval_number
3608 )
3609 AND cpit.interval_type_id = p_interval_type_id
3610 AND cpit.org_id = p_org_id
3611 AND ps.period_id = cpit.cal_period_id
3612 AND ps.org_id = cpit.org_id;
3613 BEGIN
3614 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3615 RETURN;
3616 END IF;
3617
3618 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3619 fnd_log.STRING(
3620 fnd_log.level_procedure
3621 , 'cn.plsql.cn_mark_events_pkg.mark_event_interval_number.begin'
3622 , 'Beginning of mark_event_interval_number ...'
3623 );
3624 END IF;
3625
3626 cn_mark_events_pkg.log_event(
3627 p_event_name
3628 , p_object_name
3629 , p_object_id
3630 , p_start_date
3631 , p_start_date_old
3632 , p_end_date
3633 , p_end_date_old
3634 , l_event_log_id
3635 , p_org_id
3636 );
3637
3638 -- get all the periods which are affected and call mark_event_interval_number for all of them
3639 FOR affected_period IN affected_periods LOOP
3640 mark_notify_interval_number(
3641 p_event_name
3642 , p_interval_type_id
3643 , affected_period.cal_period_id
3644 , affected_period.start_date
3645 , affected_period.end_date
3646 , l_event_log_id
3647 , p_org_id
3648 );
3649 END LOOP;
3650
3651 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3652 fnd_log.STRING(
3653 fnd_log.level_procedure
3654 , 'cn.plsql.cn_mark_events_pkg.mark_event_interval_number.end'
3655 , 'End of mark_event_interval_number.'
3656 );
3657 END IF;
3658 EXCEPTION
3659 WHEN OTHERS THEN
3660 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3661 fnd_log.STRING(
3662 fnd_log.level_unexpected
3663 , 'cn.plsql.cn_mark_events_pkg.mark_event_interval_number.exception'
3664 , SQLERRM
3665 );
3666 END IF;
3667
3668 RAISE;
3669 END mark_event_interval_number;
3670
3671 PROCEDURE mark_event_int_num_change(x_cal_per_int_type_id NUMBER, x_interval_number NUMBER) IS
3672 CURSOR c IS
3673 SELECT cal_period_id
3674 , interval_number
3675 , interval_type_id
3676 , org_id
3677 FROM cn_cal_per_int_types_all
3678 WHERE cal_per_int_type_id = x_cal_per_int_type_id
3679 FOR UPDATE OF cal_per_int_type_id NOWAIT;
3680
3681 rec c%ROWTYPE;
3682
3683 CURSOR NAME(p_org_id NUMBER) IS
3684 SELECT NAME
3685 FROM cn_interval_types_all_tl
3686 WHERE interval_type_id = rec.interval_type_id AND org_id = p_org_id;
3687
3688 l_object_name VARCHAR2(80);
3689
3690 CURSOR dates(p_org_id NUMBER) IS
3691 SELECT start_date
3692 , end_date
3693 FROM cn_period_statuses_all
3694 WHERE period_id = rec.cal_period_id AND org_id = p_org_id;
3695
3696 l_start_date DATE;
3697 l_end_date DATE;
3698 BEGIN
3699 OPEN c;
3700 FETCH c INTO rec;
3701 CLOSE c;
3702
3703 -- mark the "CHANGE_PERIOD_INTERVAL_NUMBER" event for intelligent calculation
3704 IF (rec.interval_number <> x_interval_number AND fnd_profile.VALUE('CN_MARK_EVENTS') = 'Y') THEN
3705 -- get the object name which is the name of the interval type here.
3706 OPEN NAME(rec.org_id);
3707 FETCH NAME INTO l_object_name;
3708 CLOSE NAME;
3709
3710 -- get the start_date and end_date of the corresponding period
3711 OPEN dates(rec.org_id);
3712 FETCH dates INTO l_start_date, l_end_date;
3713 CLOSE dates;
3714
3715 cn_mark_events_pkg.mark_event_interval_number(
3716 'CHANGE_PERIOD_INTERVAL_NUMBER'
3717 , l_object_name
3718 , rec.interval_type_id
3719 , NULL
3720 , l_start_date
3721 , NULL
3722 , l_end_date
3723 , rec.interval_type_id
3724 , rec.interval_number
3725 , x_interval_number
3726 , rec.org_id
3727 );
3728 END IF;
3729 END mark_event_int_num_change;
3730
3731 PROCEDURE mark_event_comp_plan(
3732 p_event_name VARCHAR2
3733 , p_object_name VARCHAR2
3734 , p_object_id NUMBER
3735 , p_start_date DATE
3736 , p_start_date_old DATE
3737 , p_end_date DATE
3738 , p_end_date_old DATE
3739 , p_org_id NUMBER
3740 ) IS
3741 l_event_log_id NUMBER;
3742 l_start_period_id NUMBER;
3743 l_end_period_id NUMBER;
3744
3745 -- 1. update cn_comp_plans
3746 -- 2. insert/delete cn_quota_assigns
3747 --
3748 -- bug 37709654, added hints suggested by perf team to reduce buffer gets
3749 CURSOR affected_srp_curs(l_start_period_id NUMBER, l_end_period_id NUMBER) IS
3750 SELECT /*+ LEADING(SPA) */
3751 DISTINCT spa.salesrep_id
3752 , acc.period_id
3753 , acc.start_date
3754 , acc.end_date
3755 FROM cn_srp_plan_assigns_all spa
3756 , cn_srp_intel_periods_all intel
3757 , cn_period_statuses_all acc
3758 WHERE spa.comp_plan_id = p_object_id -- comp_plan_id
3759 AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
3760 AND acc.org_id = spa.org_id
3761 AND (
3762 (
3763 spa.start_date < acc.start_date
3764 AND (spa.end_date IS NULL OR acc.start_date <= spa.end_date)
3765 )
3766 OR (spa.start_date BETWEEN acc.start_date AND acc.end_date)
3767 )
3768 AND EXISTS(
3769 SELECT 1
3770 FROM cn_srp_period_quotas_all spq
3771 WHERE spa.srp_plan_assign_id = spq.srp_plan_assign_id
3772 AND spq.period_id = acc.period_id)
3773 AND intel.salesrep_id = spa.salesrep_id
3774 AND intel.period_id = acc.period_id
3775 AND intel.org_id = spa.org_id
3776 AND acc.period_status IN('O', 'F')
3777 AND intel.processing_status_code <> 'CLEAN';
3778 BEGIN
3779 --
3780 -- Log the Event for the comp plan events or any changes in the
3781 -- plan Assigns
3782 --
3783 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3784 RETURN;
3785 END IF;
3786
3787 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3788 fnd_log.STRING(
3789 fnd_log.level_procedure
3790 , 'cn.plsql.cn_mark_events_pkg.mark_event_comp_plan.begin'
3791 , 'Beginning of mark_event_comp_plan ...'
3792 );
3793 END IF;
3794
3795 cn_mark_events_pkg.log_event(
3796 p_event_name => p_event_name
3797 , p_object_name => p_object_name
3798 , p_object_id => p_object_id
3799 , p_start_date => p_start_date
3800 , p_start_date_old => p_start_date_old
3801 , p_end_date => p_end_date
3802 , p_end_date_old => p_end_date_old
3803 , x_event_log_id => l_event_log_id
3804 , p_org_id => p_org_id
3805 );
3806 -- l_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
3807 l_start_period_id := cn_general_utils.get_acc_fromperiod_id(p_start_date_old, p_org_id);
3808 l_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
3809
3810 IF p_event_name = 'CHANGE_COMP_PLAN' OR p_event_name = 'CHANGE_COMP_PLAN_OVERLAP' THEN
3811 FOR affected_recs IN affected_srp_curs(l_start_period_id, l_end_period_id) LOOP
3812 cn_mark_events_pkg.mark_notify(
3813 p_salesrep_id => affected_recs.salesrep_id
3814 , p_period_id => affected_recs.period_id
3815 , p_start_date => affected_recs.start_date
3816 , p_end_date => affected_recs.end_date
3817 , p_quota_id => NULL
3818 , p_revert_to_state => 'ROLL'
3819 , p_event_log_id => l_event_log_id
3820 , p_mode => 'NEW'
3821 , p_org_id => p_org_id
3822 );
3823 END LOOP;
3824 END IF;
3825
3826 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3827 fnd_log.STRING(
3828 fnd_log.level_procedure
3829 , 'cn.plsql.cn_mark_events_pkg.mark_event_comp_plan.end'
3830 , 'End of mark_event_comp_plan.'
3831 );
3832 END IF;
3833 EXCEPTION
3834 WHEN OTHERS THEN
3835 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3836 fnd_log.STRING(
3837 fnd_log.level_unexpected
3838 , 'cn.plsql.cn_mark_events_pkg.mark_event_comp_plan.exception'
3839 , SQLERRM
3840 );
3841 END IF;
3842
3843 RAISE;
3844 END mark_event_comp_plan;
3845
3846 --
3847 -- Procedure Name
3848 -- mark_event_srp_quotas
3849 -- Purpose
3850 -- mark events when cn_srp_quota_assigns is updated
3851 -- History
3852 -- 09/20/99 Kai Chen Created
3853 PROCEDURE mark_event_srp_quotas(
3854 p_event_name VARCHAR2
3855 , p_object_name VARCHAR2
3856 , p_srp_object_id NUMBER
3857 , p_object_id NUMBER
3858 , p_start_date DATE
3859 , p_start_date_old DATE
3860 , p_end_date DATE
3861 , p_end_date_old DATE
3862 , p_org_id NUMBER
3863 ) IS
3864 -- x_srp_object_id --> p_srp_quota_assign_id
3865 -- clku, perf fix 3628870, use cn_srp_period_quotas instead of cn_srp_period_quotas_v
3866 -- to remove MJC
3867 CURSOR affected_srp_period_quotas IS
3868 SELECT spq.salesrep_id
3869 , spq.period_id
3870 , spq.quota_id
3871 FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods intel
3872 WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
3873 AND intel.salesrep_id = spq.salesrep_id
3874 AND intel.period_id = spq.period_id
3875 AND intel.org_id = spq.org_id
3876 -- scannane, bug 7154503, Notify log table update
3877 -- rnagaraj, bug 8568515
3878 AND intel.processing_status_code <> 'CLEAN'
3879 ORDER BY spq.quota_id;
3880
3881 l_event_log_id NUMBER(15);
3882
3883 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
3884 SELECT quota_id
3885 FROM cn_srp_period_quotas_all
3886 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
3887
3888 temp_quota_id cn_quotas.quota_id%TYPE;
3889 l_return_status VARCHAR2(50);
3890 l_msg_count NUMBER;
3891 l_msg_data VARCHAR2(2000);
3892 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
3893 l_latest_quota_id NUMBER := 0;
3894 BEGIN
3895 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
3896 RETURN;
3897 END IF;
3898
3899 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3900 fnd_log.STRING(
3901 fnd_log.level_procedure
3902 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_quotas.begin'
3903 , 'Beginning of mark_event_srp_quotas ...'
3904 );
3905 END IF;
3906
3907 cn_mark_events_pkg.log_event(
3908 p_event_name
3909 , p_object_name
3910 , p_object_id
3911 , p_start_date
3912 , p_start_date_old
3913 , p_end_date
3914 , p_end_date_old
3915 , l_event_log_id
3916 , p_org_id
3917 );
3918
3919 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
3920 FOR srp_quota IN affected_srp_period_quotas LOOP
3921 IF l_latest_quota_id <> srp_quota.quota_id THEN
3922 cn_calc_sql_exps_pvt.get_parent_plan_elts(
3923 p_api_version => 1.0
3924 , p_node_type => 'P'
3925 , p_init_msg_list => 'T'
3926 , p_node_id => srp_quota.quota_id
3927 , x_plan_elt_id_tbl => dependent_pe_tbl
3928 , x_return_status => l_return_status
3929 , x_msg_count => l_msg_count
3930 , x_msg_data => l_msg_data
3931 );
3932 l_latest_quota_id := srp_quota.quota_id;
3933 END IF;
3934
3935 -- modified by rjin 11/10/1999
3936 -- since all affected period (including subsequent periods)
3937 -- are garaunteed to be marked, so we only need to mark 'NEW'
3938 cn_mark_events_pkg.mark_notify(
3939 p_salesrep_id => srp_quota.salesrep_id
3940 , p_period_id => srp_quota.period_id
3941 , p_start_date => NULL
3942 , p_end_date => NULL
3943 , p_quota_id => srp_quota.quota_id
3944 , p_revert_to_state => 'CALC'
3945 , p_event_log_id => l_event_log_id
3946 , p_mode => 'NEW'
3947 , p_org_id => p_org_id
3948 );
3949
3950 IF (dependent_pe_tbl.COUNT > 0) THEN
3951 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
3952 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
3953 FETCH l_pe_cursor INTO temp_quota_id;
3954
3955 IF l_pe_cursor%FOUND THEN
3956 cn_mark_events_pkg.mark_notify(
3957 p_salesrep_id => srp_quota.salesrep_id
3958 , p_period_id => srp_quota.period_id
3959 , p_start_date => NULL
3960 , p_end_date => NULL
3961 , p_quota_id => dependent_pe_tbl(i)
3962 , p_revert_to_state => 'CALC'
3963 , p_event_log_id => l_event_log_id
3964 , p_mode => 'NEW'
3965 , p_org_id => p_org_id
3966 );
3967 END IF;
3968
3969 CLOSE l_pe_cursor;
3970 END LOOP;
3971 END IF; -- If (dependent_pe_tbl.count > 0)
3972 END LOOP;
3973 END IF;
3974
3975 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3976 fnd_log.STRING(
3977 fnd_log.level_procedure
3978 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_quotas.end'
3979 , 'End of mark_event_srp_quotas.'
3980 );
3981 END IF;
3982 EXCEPTION
3983 WHEN OTHERS THEN
3984 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3985 fnd_log.STRING(
3986 fnd_log.level_unexpected
3987 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_quotas.exception'
3988 , SQLERRM
3989 );
3990 END IF;
3991
3992 RAISE;
3993 END mark_event_srp_quotas;
3994
3995 -- Procedure Name
3996 -- mark_event_srp_uplifts
3997 -- Purpose
3998 -- mark events when cn_srp_rule_uplifts is updated
3999 -- History
4000 -- 09/20/99 Kai Chen Created
4001 PROCEDURE mark_event_srp_uplifts(
4002 p_event_name VARCHAR2
4003 , p_object_name VARCHAR2
4004 , p_srp_object_id NUMBER
4005 , p_object_id NUMBER
4006 , p_start_date DATE
4007 , p_start_date_old DATE
4008 , p_end_date DATE
4009 , p_end_date_old DATE
4010 , p_org_id NUMBER
4011 ) IS
4012 -- x_srp_object_id --> p_srp_quota_rule_id
4013 CURSOR affected_srp_period_quotas(
4014 l_start_period_id NUMBER
4015 , l_end_period_id NUMBER
4016 , l_s_date DATE
4017 , l_e_date DATE
4018 ) IS
4019 SELECT spq.salesrep_id
4020 , spq.period_id
4021 , spq.quota_id
4022 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
4023 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
4024 end_date
4025 FROM cn_srp_quota_rules_all rule
4026 , cn_srp_period_quotas_all spq
4027 , cn_period_statuses_all acc
4028 , cn_srp_intel_periods_all intel
4029 WHERE rule.srp_quota_rule_id = p_srp_object_id --p_srp_quota_rule_id
4030 AND spq.srp_plan_assign_id = rule.srp_plan_assign_id
4031 AND spq.srp_quota_assign_id = rule.srp_quota_assign_id
4032 AND acc.period_id = spq.period_id
4033 AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
4034 AND acc.period_status = 'O'
4035 AND acc.org_id = spq.org_id
4036 AND intel.salesrep_id = spq.salesrep_id
4037 AND intel.period_id = spq.period_id
4038 AND intel.org_id = spq.org_id
4039 AND intel.processing_status_code <> 'CLEAN'
4040 ORDER BY spq.quota_id;
4041
4042 l_event_log_id NUMBER(15);
4043 l_temp_start_date DATE;
4044 l_temp_end_date DATE;
4045 l_temp_start_period_id NUMBER(15);
4046 l_temp_end_period_id NUMBER(15);
4047
4048 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4049 SELECT quota_id
4050 FROM cn_srp_period_quotas_all
4051 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4052
4053 temp_quota_id cn_quotas.quota_id%TYPE;
4054 l_return_status VARCHAR2(50);
4055 l_msg_count NUMBER;
4056 l_msg_data VARCHAR2(2000);
4057 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4058 l_latest_quota_id NUMBER := 0;
4059 BEGIN
4060 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4061 RETURN;
4062 END IF;
4063
4064 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4065 fnd_log.STRING(
4066 fnd_log.level_procedure
4067 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_uplifts.begin'
4068 , 'Beginning of mark_event_srp_uplifts ...'
4069 );
4070 END IF;
4071
4072 cn_mark_events_pkg.log_event(
4073 p_event_name
4074 , p_object_name
4075 , p_object_id
4076 , p_start_date
4077 , p_start_date_old
4078 , p_end_date
4079 , p_end_date_old
4080 , l_event_log_id
4081 , p_org_id
4082 );
4083 l_temp_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
4084 l_temp_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
4085
4086 IF p_event_name = 'CHANGE_SRP_QUOTA_POP' THEN
4087 FOR srp_quota IN affected_srp_period_quotas(
4088 l_temp_start_period_id
4089 , l_temp_end_period_id
4090 , p_start_date_old
4091 , p_end_date_old
4092 ) LOOP
4093 IF l_latest_quota_id <> srp_quota.quota_id THEN
4094 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4095 p_api_version => 1.0
4096 , p_node_type => 'P'
4097 , p_init_msg_list => 'T'
4098 , p_node_id => srp_quota.quota_id
4099 , x_plan_elt_id_tbl => dependent_pe_tbl
4100 , x_return_status => l_return_status
4101 , x_msg_count => l_msg_count
4102 , x_msg_data => l_msg_data
4103 );
4104 l_latest_quota_id := srp_quota.quota_id;
4105 END IF;
4106
4107 cn_mark_events_pkg.mark_notify(
4108 srp_quota.salesrep_id
4109 , srp_quota.period_id
4110 , srp_quota.start_date
4111 , srp_quota.end_date
4112 , srp_quota.quota_id
4113 , 'POP'
4114 , l_event_log_id
4115 , p_org_id
4116 );
4117
4118 IF (dependent_pe_tbl.COUNT > 0) THEN
4119 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4120 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4121 FETCH l_pe_cursor INTO temp_quota_id;
4122
4123 IF l_pe_cursor%FOUND THEN
4124 cn_mark_events_pkg.mark_notify(
4125 p_salesrep_id => srp_quota.salesrep_id
4126 , p_period_id => srp_quota.period_id
4127 , p_start_date => srp_quota.start_date
4128 , p_end_date => srp_quota.end_date
4129 , p_quota_id => dependent_pe_tbl(i)
4130 , p_revert_to_state => 'POP'
4131 , p_event_log_id => l_event_log_id
4132 , p_org_id => p_org_id
4133 );
4134 END IF;
4135
4136 CLOSE l_pe_cursor;
4137 END LOOP;
4138 END IF; -- If (dependent_pe_tbl.count > 0)
4139 END LOOP;
4140 END IF;
4141
4142 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4143 fnd_log.STRING(
4144 fnd_log.level_procedure
4145 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_uplifts.end'
4146 , 'End of mark_event_srp_uplifts.'
4147 );
4148 END IF;
4149 EXCEPTION
4150 WHEN OTHERS THEN
4151 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4152 fnd_log.STRING(
4153 fnd_log.level_unexpected
4154 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_uplifts.exception'
4155 , SQLERRM
4156 );
4157 END IF;
4158
4159 RAISE;
4160 END mark_event_srp_uplifts;
4161
4162 -- Procedure Name
4163 -- mark_event_srp_rate_assigns
4164 -- Purpose
4165 -- mark events when cn_srp_rate_assigns is updated
4166 -- History
4167 -- 09/20/99 Kai Chen Created
4168 PROCEDURE mark_event_srp_rate_assigns(
4169 p_event_name VARCHAR2
4170 , p_object_name VARCHAR2
4171 , p_srp_object_id NUMBER
4172 , p_object_id NUMBER
4173 , p_start_date DATE
4174 , p_start_date_old DATE
4175 , p_end_date DATE
4176 , p_end_date_old DATE
4177 , p_org_id NUMBER
4178 ) IS
4179 -- x_srp_object_id --> p_srp_quota_assign_id
4180 CURSOR affected_srp_period_quotas(l_srp_start_period_id NUMBER, l_srp_end_period_id NUMBER) IS
4181 SELECT spq.salesrep_id
4182 , spq.period_id
4183 , spq.quota_id
4184 FROM cn_srp_period_quotas_all spq
4185 , cn_period_statuses_all acc
4186 , cn_srp_intel_periods_all intel
4187 WHERE spq.srp_quota_assign_id = p_srp_object_id
4188 AND acc.period_id = spq.period_id
4189 AND acc.org_id = spq.org_id
4190 AND (acc.period_id BETWEEN l_srp_start_period_id AND l_srp_end_period_id)
4191 AND acc.period_status = 'O'
4192 AND intel.salesrep_id = spq.salesrep_id
4193 AND intel.period_id = spq.period_id
4194 AND intel.org_id = spq.org_id
4195 AND intel.processing_status_code <> 'CLEAN'
4196 ORDER BY spq.quota_id;
4197
4198 -- very similiar to the mark_event_srp_rule_uplift when figuring out
4199 -- the affected srp/period/quota
4200 -- only difference is that this time we go to cn_rt_quota_asgns to
4201 -- get the start_date/ end_date
4202 l_event_log_id NUMBER(15);
4203 l_temp_start_date DATE;
4204 l_temp_end_date DATE;
4205 l_temp_start_period_id NUMBER(15);
4206 l_temp_end_period_id NUMBER(15);
4207 l_srp_start_period_id NUMBER(15);
4208 l_srp_end_period_id NUMBER(15);
4209
4210 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4211 SELECT quota_id
4212 FROM cn_srp_period_quotas_all
4213 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4214
4215 temp_quota_id cn_quotas.quota_id%TYPE;
4216 l_return_status VARCHAR2(50);
4217 l_msg_count NUMBER;
4218 l_msg_data VARCHAR2(2000);
4219 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4220 l_latest_quota_id NUMBER := 0;
4221 BEGIN
4222 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4223 RETURN;
4224 END IF;
4225
4226 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4227 fnd_log.STRING(
4228 fnd_log.level_procedure
4229 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_rate_assigns.begin'
4230 , 'Beginning of mark_event_srp_rate_assigns ...'
4231 );
4232 END IF;
4233
4234 cn_mark_events_pkg.log_event(
4235 p_event_name
4236 , p_object_name
4237 , p_object_id
4238 , p_start_date
4239 , p_start_date_old
4240 , p_end_date
4241 , p_end_date_old
4242 , l_event_log_id
4243 , p_org_id
4244 );
4245 l_srp_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
4246 l_srp_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
4247
4248 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
4249 FOR srp_quota IN affected_srp_period_quotas(l_srp_start_period_id, l_srp_end_period_id) LOOP
4250 IF l_latest_quota_id <> srp_quota.quota_id THEN
4251 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4252 p_api_version => 1.0
4253 , p_node_type => 'P'
4254 , p_init_msg_list => 'T'
4255 , p_node_id => srp_quota.quota_id
4256 , x_plan_elt_id_tbl => dependent_pe_tbl
4257 , x_return_status => l_return_status
4258 , x_msg_count => l_msg_count
4259 , x_msg_data => l_msg_data
4260 );
4261 l_latest_quota_id := srp_quota.quota_id;
4262 END IF;
4263
4264 cn_mark_events_pkg.mark_notify(
4265 srp_quota.salesrep_id
4266 , srp_quota.period_id
4267 , NULL
4268 , NULL
4269 , srp_quota.quota_id
4270 , 'CALC'
4271 , l_event_log_id
4272 , p_org_id
4273 );
4274
4275 IF (dependent_pe_tbl.COUNT > 0) THEN
4276 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4277 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4278 FETCH l_pe_cursor INTO temp_quota_id;
4279
4280 IF l_pe_cursor%FOUND THEN
4281 cn_mark_events_pkg.mark_notify(
4282 p_salesrep_id => srp_quota.salesrep_id
4283 , p_period_id => srp_quota.period_id
4284 , p_start_date => NULL
4285 , p_end_date => NULL
4286 , p_quota_id => dependent_pe_tbl(i)
4287 , p_revert_to_state => 'CALC'
4288 , p_event_log_id => l_event_log_id
4289 , p_org_id => p_org_id
4290 );
4291 END IF;
4292
4293 CLOSE l_pe_cursor;
4294 END LOOP;
4295 END IF; -- If (dependent_pe_tbl.count > 0)
4296 END LOOP;
4297 END IF;
4298
4299 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4300 fnd_log.STRING(
4301 fnd_log.level_procedure
4302 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_rate_assigns.end'
4303 , 'End of mark_event_srp_rate_assigns.'
4304 );
4305 END IF;
4306 EXCEPTION
4307 WHEN OTHERS THEN
4308 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4309 fnd_log.STRING(
4310 fnd_log.level_unexpected
4311 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_rate_assigns.exception'
4312 , SQLERRM
4313 );
4314 END IF;
4315
4316 RAISE;
4317 END mark_event_srp_rate_assigns;
4318
4319 -- Procedure Name
4320 -- mark_event_srp_period_quota
4321 -- Purpose
4322 -- mark events when cn_srp_period_quotas is updated
4323 -- History
4324 -- 09/20/99 Kai Chen Created
4325 PROCEDURE mark_event_srp_period_quota(
4326 p_event_name VARCHAR2
4327 , p_object_name VARCHAR2
4328 , p_srp_object_id NUMBER
4329 , p_object_id NUMBER
4330 , p_start_date DATE
4331 , p_start_date_old DATE
4332 , p_end_date DATE
4333 , p_end_date_old DATE
4334 , p_org_id NUMBER
4335 ) IS
4336 -- p_srp_object_id --> srp_period_quota_Id
4337 CURSOR affected_srp_period_quotas IS
4338 SELECT spq.salesrep_id
4339 , spq.period_id
4340 , spq.quota_id
4341 FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
4342 WHERE spq.srp_period_quota_id = p_srp_object_id -- p_srp_period_quota_id
4343 AND intel.salesrep_id = spq.salesrep_id
4344 AND intel.period_id = spq.period_id
4345 AND intel.org_id = spq.org_id
4346 -- scannane, bug 7154503, Notify log table update
4347 -- rnagaraj, bug 8568515
4348 AND intel.processing_status_code <> 'CLEAN'
4349 ORDER BY spq.quota_id;
4350
4351 l_event_log_id NUMBER(15);
4352
4353 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4354 SELECT quota_id
4355 FROM cn_srp_period_quotas_all
4356 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4357
4358 temp_quota_id cn_quotas.quota_id%TYPE;
4359 l_return_status VARCHAR2(50);
4360 l_msg_count NUMBER;
4361 l_msg_data VARCHAR2(2000);
4362 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4363 l_latest_quota_id NUMBER := 0;
4364 BEGIN
4365 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4366 RETURN;
4367 END IF;
4368
4369 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4370 fnd_log.STRING(
4371 fnd_log.level_procedure
4372 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.begin'
4373 , 'Beginning of mark_event_srp_period_quota ...'
4374 );
4375 END IF;
4376
4377 cn_mark_events_pkg.log_event(
4378 p_event_name
4379 , p_object_name
4380 , p_object_id
4381 , p_start_date
4382 , p_start_date_old
4383 , p_end_date
4384 , p_end_date_old
4385 , l_event_log_id
4386 , p_org_id
4387 );
4388
4389 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
4390 FOR srp_quota IN affected_srp_period_quotas LOOP
4391 IF l_latest_quota_id <> srp_quota.quota_id THEN
4392 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4393 p_api_version => 1.0
4394 , p_node_type => 'P'
4395 , p_init_msg_list => 'T'
4396 , p_node_id => srp_quota.quota_id
4397 , x_plan_elt_id_tbl => dependent_pe_tbl
4398 , x_return_status => l_return_status
4399 , x_msg_count => l_msg_count
4400 , x_msg_data => l_msg_data
4401 );
4402 END IF;
4403
4404 cn_mark_events_pkg.mark_notify(
4405 srp_quota.salesrep_id
4406 , srp_quota.period_id
4407 , NULL
4408 , NULL
4409 , srp_quota.quota_id
4410 , 'CALC'
4411 , l_event_log_id
4412 , p_org_id
4413 );
4414
4415 IF (dependent_pe_tbl.COUNT > 0) THEN
4416 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4417 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4418 FETCH l_pe_cursor INTO temp_quota_id;
4419
4420 IF l_pe_cursor%FOUND THEN
4421 cn_mark_events_pkg.mark_notify(
4422 p_salesrep_id => srp_quota.salesrep_id
4423 , p_period_id => srp_quota.period_id
4424 , p_start_date => NULL
4425 , p_end_date => NULL
4426 , p_quota_id => dependent_pe_tbl(i)
4427 , p_revert_to_state => 'CALC'
4428 , p_event_log_id => l_event_log_id
4429 , p_org_id => p_org_id
4430 );
4431 END IF;
4432
4433 CLOSE l_pe_cursor;
4434 END LOOP;
4435 END IF; -- If (dependent_pe_tbl.count > 0)
4436 END LOOP;
4437 END IF;
4438
4439 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4440 fnd_log.STRING(
4441 fnd_log.level_procedure
4442 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.end'
4443 , 'End of mark_event_srp_period_quota.'
4444 );
4445 END IF;
4446 EXCEPTION
4447 WHEN OTHERS THEN
4448 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4449 fnd_log.STRING(
4450 fnd_log.level_unexpected
4451 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.exception'
4452 , SQLERRM
4453 );
4454 END IF;
4455
4456 RAISE;
4457 END mark_event_srp_period_quota;
4458
4459 -- Procedure Name
4460 -- mark_event_srp_period_quota
4461 -- Purpose
4462 -- mark events when cn_srp_period_quotas is updated
4463 -- History
4464 -- 23/Oct/08 venjayar Created
4465 PROCEDURE mark_event_srp_period_quota(
4466 p_event_name VARCHAR2
4467 , p_object_name VARCHAR2
4468 , p_srp_object_id NUMBER
4469 , p_object_id NUMBER
4470 , p_period_id NUMBER
4471 , p_quota_id NUMBER
4472 , p_start_date DATE
4473 , p_start_date_old DATE
4474 , p_end_date DATE
4475 , p_end_date_old DATE
4476 , p_org_id NUMBER
4477 ) IS
4478 l_return_status VARCHAR2(50);
4479 l_msg_count NUMBER;
4480 l_msg_data VARCHAR2(2000);
4481 l_event_log_id NUMBER(15);
4482 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4483 BEGIN
4484 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4485 RETURN;
4486 END IF;
4487
4488 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4489 fnd_log.STRING(
4490 fnd_log.level_procedure
4491 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.begin'
4492 , 'Beginning of mark_event_srp_period_quota ...'
4493 );
4494 END IF;
4495
4496 cn_mark_events_pkg.log_event(
4497 p_event_name
4498 , p_object_name
4499 , p_object_id
4500 , p_start_date
4501 , p_start_date_old
4502 , p_end_date
4503 , p_end_date_old
4504 , l_event_log_id
4505 , p_org_id
4506 );
4507
4508 IF p_event_name = 'CHANGE_SRP_QUOTA_CALC' THEN
4509 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4510 p_api_version => 1.0
4511 , p_node_type => 'P'
4512 , p_init_msg_list => 'T'
4513 , p_node_id => p_quota_id
4514 , x_plan_elt_id_tbl => dependent_pe_tbl
4515 , x_return_status => l_return_status
4516 , x_msg_count => l_msg_count
4517 , x_msg_data => l_msg_data
4518 );
4519
4520 cn_mark_events_pkg.mark_notify(
4521 p_salesrep_id => p_object_id
4522 , p_period_id => p_period_id
4523 , p_start_date => NULL
4524 , p_end_date => NULL
4525 , p_quota_id => p_quota_id
4526 , p_revert_to_state => 'CALC'
4527 , p_event_log_id => l_event_log_id
4528 , p_org_id => p_org_id
4529 );
4530
4531 -- We have to raise Notification Events even for the Dependent Plan Elements
4532 -- which are affected because of this change and it should be
4533 -- done only if the Dependent PE is valid for the Resource in that Period.
4534 -- In order to do that check, we have to validate against
4535 -- CN_SRP_PERIOD_QUOTAS_ALL. But since this code is executed as part of
4536 -- Trigger on the same table, we will run into ORA 04091 - Mutating Trigger.
4537 --
4538 -- Either we have to change the entire architecture of moving away from
4539 -- trigger and have table handlers and fire the events from there. Though
4540 -- it is a good approach.. its not possible to do such a big change now.
4541 --
4542 -- Since this code is executed as part of EO, we can surely expect that
4543 -- the trigger is always called for a single row only and thus even
4544 -- a statement level trigger will work and we wont run in Mutating Trigger
4545 -- Issue. But we wont be able to use :NEW and :OLD.
4546 --
4547 -- Thinking more about.. a Plan Element can be dependent on other PE's only
4548 -- if the Plan Elements are part of the same Compensation Plan. Thus, we
4549 -- be sure that the Dependent Plan Elements has to be part of the Same
4550 -- Compensation Plan and thus it is valid for the Resource. So temporarily
4551 -- removed the check. If this conclusion is wrong, then the code has to be
4552 -- implemented as ONE ROW LEVEL TRIGGER which will capture the Dependent PE's
4553 -- and another STATEMENT LEVEL TRIGGER which will do the validation and
4554 -- notify Dependent PE's.
4555 --
4556 IF (dependent_pe_tbl.COUNT > 0) THEN
4557 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4558 cn_mark_events_pkg.mark_notify(
4559 p_salesrep_id => p_object_id
4560 , p_period_id => p_period_id
4561 , p_start_date => NULL
4562 , p_end_date => NULL
4563 , p_quota_id => dependent_pe_tbl(i)
4564 , p_revert_to_state => 'CALC'
4565 , p_event_log_id => l_event_log_id
4566 , p_org_id => p_org_id
4567 );
4568 END LOOP;
4569 END IF; -- If (dependent_pe_tbl.count > 0)
4570 END IF;
4571
4572 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4573 fnd_log.STRING(
4574 fnd_log.level_procedure
4575 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.end'
4576 , 'End of mark_event_srp_period_quota.'
4577 );
4578 END IF;
4579 EXCEPTION
4580 WHEN OTHERS THEN
4581 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4582 fnd_log.STRING(
4583 fnd_log.level_unexpected
4584 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_period_quota.exception'
4585 , SQLERRM
4586 );
4587 END IF;
4588
4589 RAISE;
4590 END mark_event_srp_period_quota;
4591
4592
4593 -- Procedure Name
4594 -- mark_event_srp_payee_assign
4595 -- Purpose
4596 -- mark events when cn_srp_payee_assigns is inserted, updated and deleted
4597 -- History
4598 -- 09/20/99 Kai Chen Created
4599 PROCEDURE mark_event_srp_payee_assign(
4600 p_event_name VARCHAR2
4601 , p_object_name VARCHAR2
4602 , p_srp_object_id NUMBER
4603 , p_object_id NUMBER
4604 , p_start_date DATE
4605 , p_start_date_old DATE
4606 , p_end_date DATE
4607 , p_end_date_old DATE
4608 , p_org_id NUMBER
4609 ) IS
4610 -- p_object_id --> p_srp_quota_assign_id
4611
4612 -- CHANGE_SRP_QUOTA_POP
4613 -- need to use the start_date/end_date info to restrict affected periods
4614 CURSOR affected_srp_period_quotas(
4615 l_start_period_id NUMBER
4616 , l_end_period_id NUMBER
4617 , l_s_date DATE
4618 , l_e_date DATE
4619 ) IS
4620 SELECT spq.salesrep_id
4621 , spq.period_id
4622 , spq.quota_id
4623 , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
4624 , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
4625 end_date
4626 FROM cn_srp_period_quotas_all spq
4627 , cn_period_statuses_all acc
4628 , cn_srp_intel_periods_all intel
4629 WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
4630 AND acc.period_id = spq.period_id
4631 AND acc.org_id = spq.org_id
4632 AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
4633 AND acc.period_status = 'O'
4634 AND intel.salesrep_id = spq.salesrep_id
4635 AND intel.period_id = spq.period_id
4636 AND intel.processing_status_code <> 'CLEAN'
4637 AND intel.org_id = spq.org_id
4638 ORDER BY spq.quota_id;
4639
4640 l_event_log_id NUMBER(15);
4641 l_temp_start_period_id NUMBER(15);
4642 l_temp_end_period_id NUMBER(15);
4643 l_date_range_tbl cn_api.date_range_tbl_type;
4644
4645 CURSOR l_pe_cursor(l_salesrep_id NUMBER, l_period_id NUMBER, l_quota_id NUMBER) IS
4646 SELECT quota_id
4647 FROM cn_srp_period_quotas_all
4648 WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
4649
4650 temp_quota_id cn_quotas.quota_id%TYPE;
4651 l_return_status VARCHAR2(50);
4652 l_msg_count NUMBER;
4653 l_msg_data VARCHAR2(2000);
4654 dependent_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;
4655 l_latest_quota_id NUMBER := 0;
4656 BEGIN
4657 IF fnd_profile.VALUE('CN_MARK_EVENTS') <> 'Y' THEN
4658 RETURN;
4659 END IF;
4660
4661 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4662 fnd_log.STRING(
4663 fnd_log.level_procedure
4664 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_payee_assigns.begin'
4665 , 'Beginning of mark_event_srp_payee_assigns ...'
4666 );
4667 END IF;
4668
4669 cn_mark_events_pkg.log_event(
4670 p_event_name
4671 , p_object_name
4672 , p_object_id
4673 , p_start_date
4674 , p_start_date_old
4675 , p_end_date
4676 , p_end_date_old
4677 , l_event_log_id
4678 , p_org_id
4679 );
4680
4681 IF p_event_name = 'CHANGE_SRP_QUOTA_POP' THEN
4682 l_temp_start_period_id := cn_api.get_acc_period_id(p_start_date_old, p_org_id);
4683 l_temp_end_period_id := cn_api.get_acc_period_id(p_end_date_old, p_org_id);
4684
4685 FOR srp_quota IN affected_srp_period_quotas(
4686 l_temp_start_period_id
4687 , l_temp_end_period_id
4688 , p_start_date_old
4689 , p_end_date_old
4690 ) LOOP
4691 IF l_latest_quota_id <> srp_quota.quota_id THEN
4692 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4693 p_api_version => 1.0
4694 , p_node_type => 'P'
4695 , p_init_msg_list => 'T'
4696 , p_node_id => srp_quota.quota_id
4697 , x_plan_elt_id_tbl => dependent_pe_tbl
4698 , x_return_status => l_return_status
4699 , x_msg_count => l_msg_count
4700 , x_msg_data => l_msg_data
4701 );
4702 l_latest_quota_id := srp_quota.quota_id;
4703 END IF;
4704
4705 -- modified by rjin 11/10/1999
4706 -- since change payee assign doesn't affect subsequent period
4707 -- so we only need to mark 'NEW'
4708 mark_notify(
4709 p_salesrep_id => srp_quota.salesrep_id
4710 , p_period_id => srp_quota.period_id
4711 , p_start_date => srp_quota.start_date
4712 , --NULL,
4713 p_end_date => srp_quota.end_date
4714 , --NULL,
4715 p_quota_id => srp_quota.quota_id
4716 , p_revert_to_state => 'POP'
4717 , p_event_log_id => l_event_log_id
4718 , p_mode => 'NEW'
4719 , p_org_id => p_org_id
4720 );
4721
4722 IF (dependent_pe_tbl.COUNT > 0) THEN
4723 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4724 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4725 FETCH l_pe_cursor INTO temp_quota_id;
4726
4727 IF l_pe_cursor%FOUND THEN
4728 cn_mark_events_pkg.mark_notify(
4729 p_salesrep_id => srp_quota.salesrep_id
4730 , p_period_id => srp_quota.period_id
4731 , p_start_date => srp_quota.start_date
4732 , p_end_date => srp_quota.end_date
4733 , p_quota_id => dependent_pe_tbl(i)
4734 , p_revert_to_state => 'POP'
4735 , p_event_log_id => l_event_log_id
4736 , p_mode => 'NEW'
4737 , p_org_id => p_org_id
4738 );
4739 END IF;
4740
4741 CLOSE l_pe_cursor;
4742 END LOOP;
4743 END IF; -- If (dependent_pe_tbl.count > 0)
4744 END LOOP;
4745 ELSIF p_event_name = 'CHANGE_SRP_QUOTA_PAYEE_DATE' THEN
4746 cn_api.get_date_range_diff(p_start_date, p_end_date, p_start_date_old, p_end_date_old
4747 , l_date_range_tbl);
4748
4749 FOR l_ctr IN 1 .. l_date_range_tbl.COUNT LOOP
4750 l_temp_start_period_id :=
4751 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).start_date, p_org_id);
4752 l_temp_end_period_id :=
4753 cn_api.get_acc_period_id(l_date_range_tbl(l_ctr).end_date, p_org_id);
4754
4755 FOR srp_quota IN affected_srp_period_quotas(
4756 l_temp_start_period_id
4757 , l_temp_end_period_id
4758 , l_date_range_tbl(l_ctr).start_date
4759 , l_date_range_tbl(l_ctr).end_date
4760 ) LOOP
4761 IF l_latest_quota_id <> srp_quota.quota_id THEN
4762 cn_calc_sql_exps_pvt.get_parent_plan_elts(
4763 p_api_version => 1.0
4764 , p_node_type => 'P'
4765 , p_init_msg_list => 'T'
4766 , p_node_id => srp_quota.quota_id
4767 , x_plan_elt_id_tbl => dependent_pe_tbl
4768 , x_return_status => l_return_status
4769 , x_msg_count => l_msg_count
4770 , x_msg_data => l_msg_data
4771 );
4772 l_latest_quota_id := srp_quota.quota_id;
4773 END IF;
4774
4775 -- modified by rjin 11/10/1999
4776 -- since change payee assign doesn't affect subsequent period
4777 -- so we only need to mark 'NEW'
4778 mark_notify(
4779 p_salesrep_id => srp_quota.salesrep_id
4780 , p_period_id => srp_quota.period_id
4781 , p_start_date => srp_quota.start_date
4782 , --NULL,
4783 p_end_date => srp_quota.end_date
4784 , --NULL,
4785 p_quota_id => srp_quota.quota_id
4786 , p_revert_to_state => 'POP'
4787 , p_event_log_id => l_event_log_id
4788 , p_mode => 'NEW'
4789 , p_org_id => p_org_id
4790 );
4791
4792 IF (dependent_pe_tbl.COUNT > 0) THEN
4793 FOR i IN 0 ..(dependent_pe_tbl.COUNT - 1) LOOP
4794 OPEN l_pe_cursor(srp_quota.salesrep_id, srp_quota.period_id, dependent_pe_tbl(i));
4795 FETCH l_pe_cursor INTO temp_quota_id;
4796
4797 IF l_pe_cursor%FOUND THEN
4798 cn_mark_events_pkg.mark_notify(
4799 p_salesrep_id => srp_quota.salesrep_id
4800 , p_period_id => srp_quota.period_id
4801 , p_start_date => srp_quota.start_date
4802 , p_end_date => srp_quota.end_date
4803 , p_quota_id => dependent_pe_tbl(i)
4804 , p_revert_to_state => 'POP'
4805 , p_event_log_id => l_event_log_id
4806 , p_mode => 'NEW'
4807 , p_org_id => p_org_id
4808 );
4809 END IF;
4810
4811 CLOSE l_pe_cursor;
4812 END LOOP;
4813 END IF; -- If (dependent_pe_tbl.count > 0)
4814 END LOOP;
4815 END LOOP;
4816 END IF;
4817
4818 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4819 fnd_log.STRING(
4820 fnd_log.level_procedure
4821 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_pay_assign.end'
4822 , 'End of mark_event_srp_payee_assign.'
4823 );
4824 END IF;
4825 EXCEPTION
4826 WHEN OTHERS THEN
4827 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4828 fnd_log.STRING(
4829 fnd_log.level_unexpected
4830 , 'cn.plsql.cn_mark_events_pkg.mark_event_srp_payee_assign.exception'
4831 , SQLERRM
4832 );
4833 END IF;
4834
4835 RAISE;
4836 END mark_event_srp_payee_assign;
4837
4838 -- mark all the reps in a single team
4839 PROCEDURE mark_notify_team(
4840 p_team_id IN NUMBER
4841 , p_team_event_name IN VARCHAR2
4842 , p_team_name IN VARCHAR2
4843 , p_start_date_active IN DATE
4844 , p_end_date_active IN DATE
4845 , p_event_log_id IN NUMBER
4846 , p_org_id IN NUMBER
4847 ) IS
4848 l_event_log_id NUMBER;
4849 l_action_link_id NUMBER;
4850 t_team_name cn_comp_teams.NAME%TYPE;
4851 t_start_date_active cn_comp_teams.start_date_active%TYPE;
4852 t_end_date_active cn_comp_teams.end_date_active%TYPE;
4853 l_revert_state VARCHAR2(30);
4854 l_action VARCHAR2(30);
4855
4856 -- get all the reps in this team
4857 CURSOR c_all_members IS
4858 SELECT salesrep_id
4859 FROM cn_srp_comp_teams_v
4860 WHERE comp_team_id = p_team_id AND org_id = p_org_id;
4861
4862 -- get team info
4863 CURSOR c_team_info(p_team_id NUMBER) IS
4864 SELECT NAME
4865 , start_date_active
4866 , end_date_active
4867 FROM cn_comp_teams
4868 WHERE comp_team_id = p_team_id;
4869
4870 -- cursor to find all periods in the date range for each srp
4871 CURSOR periods(
4872 p_salesrep_id NUMBER
4873 , p_start_date DATE
4874 , p_end_date DATE
4875 , p_action VARCHAR2
4876 , p_revert_state VARCHAR2
4877 ) IS
4878 SELECT p.period_id
4879 , GREATEST(p_start_date, p.start_date) start_date
4880 , DECODE(p_end_date, NULL, p.end_date, LEAST(p_end_date, p.end_date)) end_date
4881 FROM cn_srp_intel_periods_all p
4882 WHERE p.salesrep_id = p_salesrep_id
4883 AND p.org_id = p_org_id
4884 AND (p_end_date IS NULL OR p.start_date <= p_end_date)
4885 AND (p.end_date >= p_start_date);
4886 BEGIN
4887 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4888 fnd_log.STRING(
4889 fnd_log.level_procedure
4890 , 'cn.plsql.cn_mark_events_pkg.mark_notify_team.begin'
4891 , 'Beginning of mark_notify_team ...'
4892 );
4893 END IF;
4894
4895 IF p_team_name IS NULL THEN
4896 OPEN c_team_info(p_team_id);
4897 FETCH c_team_info INTO t_team_name, t_start_date_active, t_end_date_active;
4898 IF (c_team_info%NOTFOUND) THEN
4899 CLOSE c_team_info;
4900 RETURN;
4901 END IF;
4902
4903 CLOSE c_team_info;
4904 ELSE
4905 t_team_name := p_team_name;
4906 t_start_date_active := p_start_date_active;
4907 t_end_date_active := p_end_date_active;
4908 END IF;
4909
4910 IF p_event_log_id IS NULL THEN
4911 cn_mark_events_pkg.log_event(
4912 p_event_name => p_team_event_name
4913 , p_object_name => t_team_name
4914 , p_object_id => p_team_id
4915 , p_start_date => t_start_date_active
4916 , p_start_date_old => NULL
4917 , p_end_date => t_end_date_active
4918 , p_end_date_old => NULL
4919 , x_event_log_id => l_event_log_id
4920 , p_org_id => p_org_id
4921 );
4922 ELSE
4923 l_event_log_id := p_event_log_id;
4924 END IF;
4925
4926 IF p_team_event_name = 'CHANGE_TEAM_ADD_REP' THEN
4927 l_revert_state := 'POP';
4928 l_action := NULL;
4929 ELSE
4930 l_revert_state := 'CALC';
4931 l_action := 'DELETE_TEAM_MEMB';
4932 END IF;
4933
4934 FOR c_mem_rec IN c_all_members LOOP
4935 FOR prd IN periods(
4936 c_mem_rec.salesrep_id
4937 , t_start_date_active
4938 , t_end_date_active
4939 , l_action
4940 , l_revert_state
4941 ) LOOP
4942 cn_mark_events_pkg.mark_notify_salesreps(
4943 p_salesrep_id => c_mem_rec.salesrep_id
4944 , p_comp_group_id => NULL
4945 , p_period_id => prd.period_id
4946 , p_start_date => prd.start_date
4947 , p_end_date => prd.end_date
4948 , p_revert_to_state => l_revert_state
4949 , p_action => l_action
4950 , p_action_link_id => NULL
4951 , p_base_salesrep_id => NULL
4952 , p_base_comp_group_id => NULL
4953 , p_event_log_id => l_event_log_id
4954 , x_action_link_id => l_action_link_id
4955 , p_org_id => p_org_id
4956 );
4957 END LOOP;
4958 END LOOP;
4959
4960 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4961 fnd_log.STRING(
4962 fnd_log.level_procedure
4963 , 'cn.plsql.cn_mark_events_pkg.mark_notify_team.end'
4964 , 'End of mark_notify_team.'
4965 );
4966 END IF;
4967 EXCEPTION
4968 WHEN OTHERS THEN
4969 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4970 fnd_log.STRING(
4971 fnd_log.level_unexpected
4972 , 'cn.plsql.cn_mark_events_pkg.mark_notify_team.exception'
4973 , SQLERRM
4974 );
4975 END IF;
4976
4977 RAISE;
4978 END mark_notify_team;
4979 END cn_mark_events_pkg;