1 PACKAGE cn_mark_events_pkg AS
2 -- $Header: cnevents.pls 120.2.12010000.3 2008/10/23 15:42:59 venjayar ship $
3
4 --
5 -- Package Body Name
6 -- cn_mark_events
7 -- Purpose
8 --
9 -- History
10 --
11 -- 07/12/1998 Richard Jin Created
12 TYPE srp_period_date_rec_type IS RECORD(
13 salesrep_id NUMBER(15)
14 , period_id NUMBER(15)
15 , start_date DATE
16 , end_date DATE
17 );
18
19 TYPE srp_period_date_tbl_type IS TABLE OF srp_period_date_rec_type
20 INDEX BY BINARY_INTEGER;
21
22 --+
23 -- Name
24 -- Mark_event_trx
25 -- Purpose
26 -- History
27 --+
28 -- 07/12/98 Richard Jin Created
29 -- NOTES
30 PROCEDURE log_event(
31 p_event_name IN VARCHAR2
32 , p_object_name IN VARCHAR2
33 , p_object_id IN NUMBER
34 , p_start_date IN DATE
35 , p_start_date_old IN DATE
36 , p_end_date IN DATE
37 , p_end_date_old IN DATE
38 , x_event_log_id OUT NOCOPY NUMBER
39 , p_org_id IN NUMBER
40 );
41
42 PROCEDURE mark_notify(
43 p_salesrep_id IN NUMBER --required
44 , p_period_id IN NUMBER --required
45 , p_start_date IN DATE --optional
46 , p_end_date IN DATE --optional
47 , p_quota_id IN NUMBER --optional
48 , p_revert_to_state IN VARCHAR2 --required
49 , p_event_log_id IN NUMBER
50 , p_org_id IN NUMBER --required
51 );
52
53 PROCEDURE mark_notify_salesreps(
54 p_salesrep_id IN NUMBER
55 , p_period_id IN NUMBER
56 , p_start_date IN DATE
57 , p_end_date IN DATE
58 , p_revert_to_state IN VARCHAR2
59 , p_event_log_id IN NUMBER
60 , p_comp_group_id IN NUMBER
61 , p_action IN VARCHAR2
62 , p_action_link_id IN NUMBER
63 , p_base_salesrep_id IN NUMBER
64 , p_base_comp_group_id IN NUMBER
65 , p_role_id IN NUMBER DEFAULT NULL
66 , x_action_link_id OUT NOCOPY NUMBER
67 , p_org_id IN NUMBER
68 );
69
70 PROCEDURE mark_event_sys_para(
71 p_event_name IN VARCHAR2
72 , p_object_name IN VARCHAR2
73 , p_object_id IN NUMBER
74 , p_object_id_old IN NUMBER
75 , p_period_set_id IN NUMBER
76 , p_period_type_id IN NUMBER
77 , p_start_date IN DATE
78 , p_start_date_old IN DATE
79 , p_end_date IN DATE
80 , p_end_date_old IN DATE
81 , p_org_id IN NUMBER
82 );
83
84 PROCEDURE mark_event_cls_rule(
85 p_event_name IN VARCHAR2
86 , p_object_name IN VARCHAR2
87 , p_object_id IN NUMBER
88 , p_start_date IN DATE
89 , p_start_date_old IN DATE
90 , p_end_date IN DATE
91 , p_end_date_old IN DATE
92 , p_org_id IN NUMBER
93 );
94
95 PROCEDURE mark_event_trx(
96 x_event_name IN VARCHAR2
97 , x_object_name IN VARCHAR2
98 , x_object_id IN NUMBER
99 , x_processed_period_id_old IN NUMBER
100 , x_processed_period_id_new IN NUMBER
101 , x_rollup_period_id_old IN NUMBER
102 , x_rollup_period_id_new IN NUMBER
103 , p_org_id IN NUMBER
104 );
105
106 FUNCTION check_rev_hier(x_header_hierarchy_id NUMBER, p_org_id NUMBER)
107 RETURN NUMBER;
108
109 FUNCTION check_cls_hier(x_header_hierarchy_id NUMBER, p_org_id NUMBER)
110 RETURN NUMBER;
111
112 PROCEDURE mark_event_rc_hier(
113 p_event_name IN VARCHAR2
114 , p_object_name IN VARCHAR2
115 , p_dim_hierarchy_id IN NUMBER
116 , p_head_hierarchy_id IN NUMBER
117 , p_start_date IN DATE
118 , p_start_date_old IN DATE
119 , p_end_date IN DATE
120 , p_end_date_old IN DATE
121 , p_org_id IN NUMBER
122 );
123
124 PROCEDURE mark_event_cls_hier(
125 p_event_name IN VARCHAR2
126 , p_object_name IN VARCHAR2
127 , p_dim_hierarchy_id IN NUMBER
128 , p_head_hierarchy_id IN NUMBER
129 , p_start_date IN DATE
130 , p_start_date_old IN DATE
131 , p_end_date IN DATE
132 , p_end_date_old IN DATE
133 , p_org_id IN NUMBER
134 );
135
136 -- Start of Comments
137 -- name : mark_event_quota
138 -- Type : None
139 -- Pre-reqs : None.
140 -- Usage : Procedure to Mark the Quota Event
141 -- Parameters :
142 -- IN : p_event_name IN VARCHAR2
143 -- p_object_name IN VARCHAR2
144 -- p_object_id IN NUMBER
145 -- p_start_date IN DATE
146 -- p_start_Date_old IN DATE
147 -- p_end_date IN DATE
148 -- p_end_date_old IN DATE
149 --
150 -- Version : Current version 1.0
151 -- Initial version 1.0
152 --
153 PROCEDURE mark_event_quota(
154 p_event_name VARCHAR2
155 , p_object_name VARCHAR2
156 , p_object_id NUMBER
157 , p_start_date DATE
158 , p_start_date_old DATE
159 , p_end_date DATE
160 , p_end_date_old DATE
161 , p_org_id NUMBER
162 );
163
164 -- Start of Comments
165 -- name : mark_event_trx_factor
166 -- Type : None
167 -- Pre-reqs : None.
168 -- Usage : Procedure to Mark the cn_trx_factors Event
169 -- Parameters :
170 -- IN : p_event_name IN VARCHAR2
171 -- p_object_name IN VARCHAR2
172 -- p_object_id IN NUMBER
173 -- p_start_date IN DATE
174 -- p_start_Date_old IN DATE
175 -- p_end_date IN DATE
176 -- p_end_date_old IN DATE
177 --
178 -- Version : Current version 1.0
179 -- Initial version 1.0
180 --
181 PROCEDURE mark_event_trx_factor(
182 p_event_name VARCHAR2
183 , p_object_name VARCHAR2
184 , p_object_id NUMBER
185 , p_start_date DATE
186 , p_start_date_old DATE
187 , p_end_date DATE
188 , p_end_date_old DATE
189 , p_org_id NUMBER
190 );
191
192 -- Start of Comments
193 -- name : mark_event_rt_quota
194 -- Type : None
195 -- Pre-reqs : None.
196 -- Usage : Procedure to Mark the cn_rt_quota_asgn
197 -- Parameters :
198 -- IN : p_event_name IN VARCHAR2
199 -- p_object_name IN VARCHAR2
200 -- p_object_id IN NUMBER
201 -- p_start_date IN DATE
202 -- p_start_Date_old IN DATE
203 -- p_end_date IN DATE
204 -- p_end_date_old IN DATE
205 --
206 -- Version : Current version 1.0
207 -- Initial version 1.0
208 --
209 PROCEDURE mark_event_rt_quota(
210 p_event_name VARCHAR2
211 , p_object_name VARCHAR2
212 , p_object_id NUMBER
213 , p_start_date DATE
214 , p_start_date_old DATE
215 , p_end_date DATE
216 , p_end_date_old DATE
217 , p_org_id NUMBER
218 );
219
220 --
221 -- Procedure Name
222 -- mark_event_role_plans
223 -- Purpose
224 -- Insert affected salesrep information into cn_event_log and cn_notify_log files
225 -- for recalculation purpose. Called be cn_role_plans_t trigger.
226 -- History
227 -- 09/13/99 Harlen Chen Created
228 PROCEDURE mark_event_role_plans(
229 p_event_name VARCHAR2
230 , p_object_name VARCHAR2
231 , p_object_id NUMBER
232 , p_start_date DATE
233 , p_start_date_old DATE
234 , p_end_date DATE
235 , p_end_date_old DATE
236 , p_org_id NUMBER
237 );
238
239 --
240 -- Procedure Name
241 -- mark_event_srp_paygroup
242 -- Purpose
243 -- Insert affected salesrep information into cn_event_log and cn_notify_log files
244 -- for recalculation purpose. Called from cn_paygroup_pub
245 -- History
246 -- 01/24/03 clku created
247 PROCEDURE mark_event_srp_pay_group(
248 p_event_name VARCHAR2
249 , p_object_name VARCHAR2
250 , p_object_id NUMBER
251 , p_srp_object_id NUMBER
252 , p_start_date DATE
253 , p_start_date_old DATE
254 , p_end_date DATE
255 , p_end_date_old DATE
256 , p_org_id NUMBER
257 );
258
259 -- Procedure Name
260 -- mark_event_srp_roles
261 -- Purpose
262 -- Insert affected salesrep information into cn_event_log and cn_notify_log files
263 -- for recalculation purpose. Called be cn_srp_rolens_t trigger.
264 -- History
265 -- 09/20/99 Harlen Chen Created
266 PROCEDURE mark_event_srp_roles(
267 p_event_name VARCHAR2
268 , p_object_name VARCHAR2
269 , p_object_id NUMBER
270 , p_start_date DATE
271 , p_start_date_old DATE
272 , p_end_date DATE
273 , p_end_date_old DATE
274 , p_org_id NUMBER
275 );
276
277 --Start Of Comments
278 --Purpose
279 --This procedure marks all Sales Reps for Calculation
280 --whenever there is a change in Formula_Status is modified
281 -- Called from cn_calc_formulas_t1 Trigger
282 -- This trigger fires when formula_status is updated
283 -- COMPLETE.
284 -- Event Fired is CHANGE_FORMULA
285 -- History
286 -- 09/19/99 ( Venkata) chalam Krishnan Created
287 --End of Comments
288 PROCEDURE mark_event_formula(
289 p_event_name VARCHAR2
290 , p_object_name VARCHAR2
291 , p_object_id NUMBER
292 , p_start_date DATE
293 , p_start_date_old DATE
294 , p_end_date DATE
295 , p_end_date_old DATE
296 , p_org_id NUMBER
297 );
298
299 --Start Of Comments
300 --Purpose
301 --This procedure marks all Sales Reps for Calculation
302 --whenever there is a change in Rate Dim Tiers
303 --1. Insert Rate Dim Tiers
304 -- Event Fired is CHANGE_RT_INS_DEL
305 --2. Update Rate Dim Tiers
306 -- Event Fired is CHANGE_RT_TIER
307 --3. Delete Rate Dim Tiers
308 -- Event fired is CHANGE_RT_INS_DEL
309 --History
310 --09/19/99 ( Venkata ) chalam Krishnan Created
311 --End of Comments
312 PROCEDURE mark_event_rate_table(
313 p_event_name VARCHAR2
314 , p_object_name VARCHAR2
315 , p_object_id NUMBER
316 , p_start_date DATE
317 , p_start_date_old DATE
318 , p_end_date DATE
319 , p_end_date_old DATE
320 , p_org_id NUMBER
321 );
322
323 --
324 -- Start Of Comments
325 -- Purpose:
326 -- This procedure marks all Sales Reps for Calculation
327 -- whenever there is a change in Rate Tiers (Commission Rates).
328 --
329 -- 1. Insert Rate Dim Tiers
330 -- Event Fired is CHANGE_RT_INS_DEL
331 -- 2. Update Rate Dim Tiers
332 -- Event Fired is CHANGE_RT_TIER
333 -- 3. Delete Rate Dim Tiers
334 -- Event fired is CHANGE_RT_INS_DEL
335 --
336 -- History
337 -- 29/08/08 (venjayar) jVenki Created
338 --
339 -- End of Comments
340 PROCEDURE mark_event_rate_tier_table(
341 p_event_name VARCHAR2
342 , p_object_name VARCHAR2
343 , p_object_id NUMBER
344 , p_dep_object_id NUMBER
345 , p_start_date DATE
346 , p_start_date_old DATE
347 , p_end_date DATE
348 , p_end_date_old DATE
349 , p_org_id NUMBER
350 );
351
352 -- Purpose
353 -- Upon the change of any interval number, mark all the sales reps that might be affected
354 -- in terms of calculation
355 -- History
356 -- created on 9/27/99 by ymao
357 PROCEDURE mark_event_interval_number(
358 p_event_name VARCHAR2
359 , p_object_name VARCHAR2
360 , p_object_id NUMBER
361 , p_start_date DATE
362 , p_start_date_old DATE
363 , p_end_date DATE
364 , p_end_date_old DATE
365 , p_interval_type_id NUMBER
366 , p_old_interval_number NUMBER
367 , p_new_interval_number NUMBER
368 , p_org_id NUMBER
369 );
370
371 PROCEDURE mark_event_int_num_change(x_cal_per_int_type_id NUMBER, x_interval_number NUMBER);
372
373 -- Start of Comments
374 -- name : mark_event_comp_plan
375 -- Type : None
376 -- Pre-reqs : None.
377 -- Usage : Procedure to Mark the Comp Plan Event
378 -- Parameters :
379 -- IN : p_event_name IN VARCHAR2
380 -- p_object_name IN VARCHAR2
381 -- p_object_id IN NUMBER
382 -- p_start_date IN DATE
383 -- p_start_Date_old IN DATE
384 -- p_end_date IN DATE
385 -- p_end_date_old IN DATE
386 --
387 -- Version : Current version 1.0
388 -- Initial version 1.0
389 --
390 PROCEDURE mark_event_comp_plan(
391 p_event_name VARCHAR2
392 , p_object_name VARCHAR2
393 , p_object_id NUMBER
394 , p_start_date DATE
395 , p_start_date_old DATE
396 , p_end_date DATE
397 , p_end_date_old DATE
398 , p_org_id NUMBER
399 );
400
401 -- Procedure Name
402 -- mark_event_srp_quotas
403 -- Purpose
404 -- mark events when cn_srp_quota_assigns is updated
405 -- History
406 -- 09/20/99 Kai Chen Created
407 --
408 PROCEDURE mark_event_srp_quotas(
409 p_event_name VARCHAR2
410 , p_object_name VARCHAR2
411 , p_srp_object_id NUMBER
412 , p_object_id NUMBER
416 , p_end_date_old DATE
413 , p_start_date DATE
414 , p_start_date_old DATE
415 , p_end_date DATE
417 , p_org_id NUMBER
418 );
419
420 -- Procedure Name
421 -- mark_event_srp_uplifts
422 -- Purpose
423 -- mark events when cn_srp_rule_uplifts is updated
424 -- History
425 -- 09/20/99 Kai Chen Created
426 PROCEDURE mark_event_srp_uplifts(
427 p_event_name VARCHAR2
428 , p_object_name VARCHAR2
429 , p_srp_object_id NUMBER
430 , p_object_id NUMBER
431 , p_start_date DATE
432 , p_start_date_old DATE
433 , p_end_date DATE
434 , p_end_date_old DATE
435 , p_org_id NUMBER
436 );
437
438 -- Procedure Name
439 -- mark_event_srp_rate_assigns
440 -- Purpose
441 -- mark events when cn_srp_rate_assigns is updated
442 -- History
443 -- 09/20/99 Kai Chen Created
444 --
445 PROCEDURE mark_event_srp_rate_assigns(
446 p_event_name VARCHAR2
447 , p_object_name VARCHAR2
448 , p_srp_object_id NUMBER
449 , p_object_id NUMBER
450 , p_start_date DATE
451 , p_start_date_old DATE
452 , p_end_date DATE
453 , p_end_date_old DATE
454 , p_org_id NUMBER
455 );
456
457 -- Procedure Name
458 -- mark_event_srp_period_quota
459 -- Purpose
460 -- mark events when cn_srp_period_quotas is updated
461 -- History
462 -- 09/20/99 Kai Chen Created
463 PROCEDURE mark_event_srp_period_quota(
464 p_event_name VARCHAR2
465 , p_object_name VARCHAR2
466 , p_srp_object_id NUMBER
467 , p_object_id NUMBER
468 , p_start_date DATE
469 , p_start_date_old DATE
470 , p_end_date DATE
471 , p_end_date_old DATE
472 , p_org_id NUMBER
473 );
474
475 -- Procedure Name
476 -- mark_event_srp_period_quota
477 -- Purpose
478 -- mark events when cn_srp_period_quotas is updated
479 -- History
480 -- 23/Oct/08 jVenki (venjayar) Created
481 PROCEDURE mark_event_srp_period_quota(
482 p_event_name VARCHAR2
483 , p_object_name VARCHAR2
484 , p_srp_object_id NUMBER
485 , p_object_id NUMBER
486 , p_period_id NUMBER
487 , p_quota_id NUMBER
488 , p_start_date DATE
489 , p_start_date_old DATE
490 , p_end_date DATE
491 , p_end_date_old DATE
492 , p_org_id NUMBER
493 );
494
495 -- Procedure Name
496 -- mark_event_srp_payee_assign
497 -- Purpose
498 -- mark events when cn_srp_payee_assigns is inserted, updated and deleted
499 -- History
500 -- 09/20/99 Kai Chen Created
501 PROCEDURE mark_event_srp_payee_assign(
502 p_event_name VARCHAR2
503 , p_object_name VARCHAR2
504 , p_srp_object_id NUMBER
505 , p_object_id NUMBER
506 , p_start_date DATE
507 , p_start_date_old DATE
508 , p_end_date DATE
509 , p_end_date_old DATE
510 , p_org_id NUMBER
511 );
512
513 PROCEDURE mark_notify_team(
514 p_team_id IN NUMBER
515 , p_team_event_name IN VARCHAR2
516 , p_team_name IN VARCHAR2
517 , p_start_date_active IN DATE
518 , p_end_date_active IN DATE
519 , p_event_log_id IN NUMBER
520 , p_org_id IN NUMBER
521 );
522 END cn_mark_events_pkg;