[Home] [Help]
PACKAGE BODY: APPS.CN_FORMULA_COMMON_PKG
Source
1 PACKAGE BODY cn_formula_common_pkg AS
2 -- $Header: cnfmcomb.pls 120.23 2011/11/27 15:54:45 rnagired ship $
3
4 -- This package contains the procedures of calculation engine, some of which will be called from each formula packages
5
6 -- global variable for this package
7 G_LAST_UPDATE_DATE DATE := sysdate;
8 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
9 G_CREATION_DATE DATE := sysdate;
10 G_CREATED_BY NUMBER := fnd_global.user_id;
11 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
12
13 g_intel_calc_flag VARCHAR2(1);
14 g_calc_type VARCHAR2(30);
15
16 g_precision NUMBER;
17 g_ext_precision NUMBER;
18 api_call_failed EXCEPTION;
19
20 TYPE comm_line_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
21 TYPE comm_status_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
22
23
24 TYPE amt_rate_rec_type is RECORD
25 (amount NUMBER,
26 tier_range NUMBER,
27 rate NUMBER);
28
29 TYPE amt_rate_tbl_type IS TABLE OF amt_rate_rec_type INDEX BY BINARY_INTEGER;
30
31 -- caching rate tables:
32 -- 1. The information about the rate table currently used in calculation is stored in the following global variables
33 -- 2. When there is a dynamic rate dimension, the tiers of this dimension are evaluated and the results are stored in g_dim_tier_table
34 -- 3. Upon each call to get_rates, these global variables are refreshed if necessary
35 -- 4. If the rate table is the same, then only commission rates are refreshed
36
37 -- global variables for caching of multi-dimensional rate table
38 TYPE dim_tier_rec_type IS RECORD
39 (tier_sequence NUMBER,
40 minimum_amount NUMBER,
41 maximum_amount NUMBER,
42 min_exp_id NUMBER,
43 max_exp_id NUMBER,
44 string_value VARCHAR2(30));
45 TYPE dim_tier_tbl_type IS TABLE OF dim_tier_rec_type INDEX BY BINARY_INTEGER;
46 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
47 TYPE str1_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
48
49 -- table for caching dimension tiers
50 g_dim_tier_table dim_tier_tbl_type;
51
52 -- table for caching dimension size
53 g_dim_size_table number_tbl_type;
54
55 -- table for caching dimension type
56 g_dim_type_table str1_tbl_type;
57
58 -- table to mark dynamic dimension tiers
59 g_dynamic_tier_table number_tbl_type;
60
61 -- table for caching the initial position of each dimension's tiers in g_dim_tier_table
62 g_tier_index_table number_tbl_type;
63
64 -- table for caching commission_amounts
65 g_comm_amount_table number_tbl_type;
66
67 -- table for caching rate_tier_ids
68 g_rate_tier_id_table number_tbl_type;
69
70 -- variables to uniquely identify a rate table
71 -- 1) g_srp_plan_assign_id identifies salesrep and compensation plan
72 g_srp_plan_assign_id NUMBER;
73 -- 2) g_rt_quota_asgn_id identifies plan element, formula(main formula or embedded formula), date range and rate table
74 g_rt_quota_asgn_id NUMBER;
75 -- 3) g_rate_schedule_id identifies the rate table
76 g_rate_schedule_id NUMBER;
77 -- 4) g_customized_flag indicates whether comission_amounts are customized
78 g_customized_flag VARCHAR2(1);
79 -- 5) g_period_id tells whether dynamic dimension tiers should be refreshed or not
80 g_period_id NUMBER;
81
82 g_refresh_flag VARCHAR2(1);
83
84 PROCEDURE revert_posting_line (p_commission_line_id NUMBER)
85 IS
86 l_pmt_trans_rec CN_PMT_TRANS_PKG.pmt_trans_rec_type;
87
88 CURSOR get_comm_line_rec IS
89 (SELECT CN_API.G_MISS_ID payment_transaction_id,
90 -1 posting_batch_id,
91 cl.credited_salesrep_id,
92 cl.credited_salesrep_id payee_salesrep_id,
93 cl.quota_id,
94 cl.pay_period_id,
95 pe.incentive_type_code,
96 cl.credit_type_id,
97 NULL, -- payrun_id
98 nvl(cl.commission_amount,0) amount,
102 'N' waive_flag, -- default N
99 nvl(cl.commission_amount,0) payment_amount, -- default
100 'N' hold_flag, -- default N
101 'N' paid_flag, -- default N
103 'N' recoverable_flag, -- default N
104 cl.commission_header_id,
105 cl.commission_line_id,
106 null, -- pay_element_type_id
107 cl.srp_plan_assign_id,
108 cl.processed_date,
109 cl.processed_period_id,
110 cl.quota_rule_id,
111 cl.event_factor,
112 cl.payment_factor,
113 cl.quota_factor,
114 cl.input_achieved,
115 cl.rate_tier_id,
116 cl.payee_line_id,
117 cl.commission_rate,
118 cl.trx_type,
119 cl.role_id,
120 pe.expense_account_id expense_ccid,
121 pe.liability_account_id liability_ccid,
122 NULL, --cl.attribute_category,
123 NULL, --cl.attribute1,
124 null, --cl.attribute2,
125 null, --cl.attribute3,
126 null, --cl.attribute4,
127 null, --cl.attribute5,
128 null, --cl.attribute6,
129 null, --cl.attribute7,
130 null, --cl.attribute8,
131 null, --cl.attribute9,
132 null, --cl.attribute10,
133 null, --cl.attribute11,
134 null, --cl.attribute12,
135 null, --cl.attribute13,
136 null, --cl.attribute14,
137 null, --cl.attribute15
138 cl.org_id,
139 0
140 FROM cn_commission_lines_all cl,
141 cn_quotas_all pe
142 WHERE cl.commission_line_id = p_commission_line_id
143 AND cl.quota_id = pe.quota_id
144 AND cl.srp_payee_assign_id IS NULL)
145 UNION --this is added for assign payees for fixing bug#2495614
146 (SELECT CN_API.G_MISS_ID payment_transaction_id,
147 -1 posting_batch_id,
148 payee.payee_id credited_salesrep_id,
149 payee.payee_id payee_salesrep_id,
150 cl.quota_id,
151 cl.pay_period_id,
152 pe.incentive_type_code,
153 cl.credit_type_id,
154 NULL, -- payrun_id
155 nvl(cl.commission_amount,0) amount,
156 nvl(cl.commission_amount,0) payment_amount, -- default
157 'N' hold_flag, -- default N
158 'N' paid_flag, -- default N
159 'N' waive_flag, -- default N
160 'N' recoverable_flag, -- default N
161 cl.commission_header_id,
162 cl.commission_line_id,
163 null, -- pay_element_type_id
164 cl.srp_plan_assign_id,
165 cl.processed_date,
166 cl.processed_period_id,
167 cl.quota_rule_id,
168 cl.event_factor,
169 cl.payment_factor,
170 cl.quota_factor,
171 cl.input_achieved,
172 cl.rate_tier_id,
173 cl.payee_line_id,
174 cl.commission_rate,
175 cl.trx_type,
176 54,--cl.role_id
177 pe.expense_account_id expense_ccid,
178 pe.liability_account_id liability_ccid,
179 NULL, --cl.attribute_category,
180 NULL, --cl.attribute1,
181 null, --cl.attribute2,
182 null, --cl.attribute3,
183 null, --cl.attribute4,
184 null, --cl.attribute5,
185 null, --cl.attribute6,
186 null, --cl.attribute7,
187 null, --cl.attribute8,
188 null, --cl.attribute9,
189 null, --cl.attribute10,
190 null, --cl.attribute11,
191 null, --cl.attribute12,
192 null, --cl.attribute13,
193 null, --cl.attribute14,
194 null, --cl.attribute15
195 cl.org_id,
196 0
197 FROM cn_commission_lines_all cl,
198 cn_srp_payee_assigns_all payee,
199 cn_quotas_all pe
200 WHERE cl.commission_line_id = p_commission_line_id
201 AND cl.quota_id = pe.quota_id
202 AND cl.srp_payee_assign_id IS NOT NULL
203 AND payee.srp_payee_assign_id = cl.srp_payee_assign_id);
204 BEGIN
205 If (fnd_profile.value('CN_PAY_BY_TRANSACTION') = 'Y') THEN
206 -- Build Payment Record record from Commission Line
207 OPEN get_comm_line_rec;
208 FETCH get_comm_line_rec INTO l_pmt_trans_rec;
209 IF get_comm_line_rec%ROWCOUNT <> 1 THEN
210 CLOSE get_comm_line_rec;
211 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_COMMISSION_LINE');
212 FND_MESSAGE.SET_TOKEN('COMMISSION_LINE_ID', TO_CHAR(p_commission_line_id));
213 FND_MSG_PUB.ADD;
214 RAISE FND_API.G_EXC_ERROR;
215 END IF;
216 CLOSE get_comm_line_rec;
217
218 l_pmt_trans_rec.amount := l_pmt_trans_rec.amount * -1;
219 l_pmt_trans_rec.payment_amount := 0 - l_pmt_trans_rec.payment_amount;
220
221 -- insert record into CN_PAYMENT_TRANSACTIONS
222 CN_PMT_TRANS_PKG.Insert_Record(l_pmt_trans_rec);
223
224 -- make sure it is not reverted twice
225 update cn_commission_lines
226 set posting_status = 'REVERTED',
227 last_update_date = sysdate
228 where commission_line_id = p_commission_line_id;
229 END IF;
230 EXCEPTION
231 WHEN OTHERS THEN
232 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
236 end if;
233 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
234 'cn.plsql.cn_formula_common_pkg.revert_posting_line.exception',
235 sqlerrm);
237 cn_message_pkg.debug('Exception occurs in reversing posted line (ID='|| p_commission_line_id||')');
238 cn_message_pkg.debug(sqlerrm);
239 RAISE;
240 END revert_posting_line;
241
242 PROCEDURE Select_Tier( p_rate_dim_sequence NUMBER,
243 p_quota_achieved NUMBER,
244 p_string_value VARCHAR2,
245 p_direction NUMBER,
246 x_tier_sequence OUT NOCOPY NUMBER)
247 IS
248 l_tier_min NUMBER;
249 l_tier_max NUMBER;
250 BEGIN
251 -- if it is a string_based dimension, then ...
252 IF (g_dim_type_table(p_rate_dim_sequence) = 'S') THEN
253 IF (p_string_value IS NOT NULL) THEN
254 FOR i IN g_tier_index_table(p_rate_dim_sequence)..(g_tier_index_table(p_rate_dim_sequence) + g_dim_size_table(p_rate_dim_sequence) - 1) LOOP
255 IF (g_dim_tier_table(i).string_value = p_string_value) THEN
256 x_tier_sequence := g_dim_tier_table(i).tier_sequence;
257 RETURN;
258 END IF;
259 END LOOP;
260
261 -- if there is no exact match, default to the last tier
262 IF (x_tier_sequence IS NULL) THEN
263 x_tier_sequence := g_dim_size_table(p_rate_dim_sequence);
264 return;
265 END IF;
266 ELSE
267 x_tier_sequence := g_dim_size_table(p_rate_dim_sequence);
268 return;
269 END IF;
270 END IF;
271
272 l_tier_min := g_dim_tier_table(g_tier_index_table(p_rate_dim_sequence)).minimum_amount;
273 l_tier_max := g_dim_tier_table(g_tier_index_table(p_rate_dim_sequence) + g_dim_size_table(p_rate_dim_sequence)- 1).maximum_amount;
274
275 IF (p_quota_achieved >= l_tier_max) THEN
276 x_tier_sequence := g_dim_size_table(p_rate_dim_sequence);
277 ELSIF (p_quota_achieved <= l_tier_min) THEN
278 x_tier_sequence := 1;
279 ELSIF (p_direction > 0) THEN
280 FOR i IN g_tier_index_table(p_rate_dim_sequence)..(g_tier_index_table(p_rate_dim_sequence) + g_dim_size_table(p_rate_dim_sequence) - 1) LOOP
281 IF (p_quota_achieved >= g_dim_tier_table(i).minimum_amount AND p_quota_achieved < g_dim_tier_table(i).maximum_amount) THEN
282 x_tier_sequence := g_dim_tier_table(i).tier_sequence;
283 EXIT;
284 END IF;
285 END LOOP;
286 ELSE
287 FOR i IN g_tier_index_table(p_rate_dim_sequence)..(g_tier_index_table(p_rate_dim_sequence) + g_dim_size_table(p_rate_dim_sequence) - 1) LOOP
288 IF (p_quota_achieved > g_dim_tier_table(i).minimum_amount AND p_quota_achieved <= g_dim_tier_table(i).maximum_amount) THEN
289 x_tier_sequence := g_dim_tier_table(i).tier_sequence;
290 EXIT;
291 END IF;
292 END LOOP;
293 END IF;
294 EXCEPTION
295 WHEN OTHERS THEN
296 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
297 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
298 'cn.plsql.cn_formula_common_pkg.select_tier.exception',
299 sqlerrm);
300 end if;
301 cn_message_pkg.debug('Exception occurs in identifying rate tier:');
302 cn_message_pkg.debug('p_direction=' || p_direction);
303 cn_message_pkg.debug('p_quota_achieved='|| p_quota_achieved);
304 cn_message_pkg.debug(sqlerrm);
305 RAISE;
306 END Select_Tier;
307
308 PROCEDURE get_rate_sequence(p_number_dim NUMBER ,
309 p_mul_input_tbl mul_input_tbl_type,
310 x_rate_sequence OUT NOCOPY NUMBER)
311 IS
312 l_base NUMBER := 1;
313 l_rate_sequence NUMBER := 0;
314 BEGIN
315 FOR i IN REVERSE 1..p_mul_input_tbl.COUNT LOOP
316 IF (i = p_number_dim) THEN
317 l_rate_sequence := l_rate_sequence + p_mul_input_tbl(i).tier_sequence;
318 ELSE
319 l_rate_sequence := l_rate_sequence + (p_mul_input_tbl(i).tier_sequence - 1) * l_base;
320 END IF;
321
322 l_base := l_base * g_dim_size_table(i);
323 END LOOP;
324
325 x_rate_sequence := l_rate_sequence;
326 EXCEPTION
327 WHEN OTHERS THEN
328 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
329 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
330 'cn.plsql.cn_formula_common_pkg.get_rate_sequence.exception',
331 sqlerrm);
332 end if;
333
334 cn_message_pkg.debug('Exception occurs in getting rate sequence: ');
335 cn_message_pkg.debug(sqlerrm);
336 RAISE;
337 END get_rate_sequence;
338
339
340 FUNCTION get_comm_amount(p_rate_sequence NUMBER) RETURN NUMBER IS
341 l_comm_amount NUMBER;
342 BEGIN
343 l_comm_amount := g_comm_amount_table(p_rate_sequence);
344 RETURN l_comm_amount;
345 EXCEPTION
346 WHEN no_data_found THEN
347 RETURN 0;
348 END get_comm_amount;
349
350 FUNCTION get_rate_tier_id(p_rate_sequence NUMBER) RETURN NUMBER IS
351 l_rate_tier_id NUMBER;
352 BEGIN
353 l_rate_tier_id := g_rate_tier_id_table(p_rate_sequence);
354 RETURN l_rate_tier_id;
355 EXCEPTION
356 WHEN no_data_found THEN
357 RETURN -1;
358 END get_rate_tier_id;
359
360 PROCEDURE get_rates( p_salesrep_id NUMBER ,
361 p_srp_plan_assign_id NUMBER ,
362 p_period_id NUMBER ,
363 p_quota_id NUMBER ,
364 p_split_flag VARCHAR2 ,
365 p_itd_flag VARCHAR2,
366 p_processed_date DATE ,
367 p_number_dim NUMBER ,
368 p_mul_input_tbl IN OUT NOCOPY mul_input_tbl_type,
369 p_calc_formula_id NUMBER,
370 x_rate OUT NOCOPY NUMBER,
374 l_rate number;
371 x_rate_tier_id OUT NOCOPY NUMBER,
372 x_tier_split OUT NOCOPY NUMBER ) IS
373
375 l_rate_schedule_id NUMBER(15);
376 l_rt_quota_asgn_id NUMBER(15);
377
378 i pls_integer := 1;
379 n pls_integer := 1;
380 j pls_integer := 1;
381 l_refresh_dim_tier_flag VARCHAR2(1) := 'Y';
382 l_refresh_rate_flag VARCHAR2(1) := 'Y';
383 l_refresh_all_tier_flag VARCHAR2(1) := 'Y';
384
385 l_sql_select VARCHAR2(2000);
386 l_sql_from VARCHAR2(1000);
387 l_where_clause VARCHAR2(1000);
388
389
390 l_split_tbl amt_rate_tbl_type;
391 l_counter NUMBER := 1;
392 l_base_amount NUMBER;
393 l_amount NUMBER;
394 l_to_next_tier NUMBER;
395 l_to_prev_tier NUMBER;
396 l_tier_sequence NUMBER;
397 l_rate_sequence NUMBER;
398 l_split_dim NUMBER;
399 l_sequence_cycle NUMBER;
400
401 l_customized_flag VARCHAR2(1);
402
403 l_comm_amount_table number_tbl_type;
404 l_rate_tier_id_table number_tbl_type;
405 l_rate_sequence_table number_tbl_type;
406
407 CURSOR dims_info IS
408 SELECT dim.number_tier, dim.dim_unit_code, rsd.rate_dimension_id
409 FROM cn_rate_dimensions_all dim,
410 cn_rate_sch_dims_all rsd
411 WHERE rsd.rate_schedule_id = l_rate_schedule_id
412 AND dim.rate_dimension_id = rsd.rate_dimension_id
413 ORDER BY rsd.rate_dim_sequence;
414
415 CURSOR dim_tiers(p_rate_dimension_id NUMBER) IS
416 SELECT tier_sequence, minimum_amount, maximum_amount, min_exp_id, max_exp_id, string_value
417 FROM cn_rate_dim_tiers_all
418 WHERE rate_dimension_id = p_rate_dimension_id
419 ORDER BY tier_sequence;
420
421 CURSOR tier_exp(p_calc_sql_exp_id NUMBER) IS
422 SELECT dbms_lob.substr(sql_select) sql_select,
423 dbms_lob.substr(sql_from) sql_from
424 FROM cn_calc_sql_exps_all
425 WHERE calc_sql_exp_id = p_calc_sql_exp_id;
426
427 CURSOR comm_amounts IS
428 SELECT commission_amount, rate_tier_id, rate_sequence
429 FROM cn_srp_rate_assigns_all
430 WHERE srp_plan_assign_id = p_srp_plan_assign_id
431 AND rt_quota_asgn_id = l_rt_quota_asgn_id
432 ORDER BY rate_sequence;
433
434 CURSOR comm_amounts2 IS
435 SELECT commission_amount, rate_tier_id, rate_sequence
436 FROM cn_rate_tiers_all
437 WHERE rate_schedule_id = l_rate_schedule_id
438 ORDER BY rate_sequence;
439
440 CURSOR split_dim IS
441 SELECT rate_dim_sequence
442 FROM cn_formula_inputs_all
443 WHERE calc_formula_id = p_calc_formula_id
444 AND nvl(split_flag, 'N') <> 'N';
445 BEGIN
446 SELECT rate_schedule_id, rt_quota_asgn_id
447 INTO l_rate_schedule_id, l_rt_quota_asgn_id
448 FROM cn_rt_quota_asgns_all
449 WHERE quota_id = p_quota_id
450 AND (calc_formula_id = p_calc_formula_id OR (calc_formula_id IS NULL AND p_calc_formula_id IS NULL))
451 AND (( end_date IS NOT NULL AND p_processed_date BETWEEN start_date AND end_date)
452 OR (end_date IS NULL AND p_processed_date >= start_date ));
453
454 -- Rate Table Refreshing Rules:
455 -- if (g_srp_plan_assign_id = p_srp_plan_assign_id and g_rt_quota_asgn_id = l_rt_quota_asgn_id) then
456 -- if (g_period_id = p_period_id) then
457 -- no refresh
458 -- else
459 -- refresh dynamic dimension tiers
460 -- g_period_id = p_period_id
461 -- end if;
462 -- elsif (g_rate_schedule_id = l_rate_schedule_id) then
463 -- refresh dynamic dimension tiers and commission amounts
464 -- g_srp_plan_assign_id = p_srp_plan_assign_id
465 -- g_rt_quota_asgn_id = l_rt_quota_asgn_id
466 -- g_period_id
467 -- else
468 -- refresh everything
469 -- end if;
470
471 IF (g_refresh_flag = 'Y') THEN
472 g_refresh_flag := 'N';
473 g_srp_plan_assign_id := p_srp_plan_assign_id;
474 g_rt_quota_asgn_id := l_rt_quota_asgn_id;
475 g_rate_schedule_id := l_rate_schedule_id;
476 g_period_id := p_period_id;
477 ELSIF (g_srp_plan_assign_id = p_srp_plan_assign_id and g_rt_quota_asgn_id = l_rt_quota_asgn_id) THEN
478 IF (g_period_id = p_period_id) THEN
479 -- no refresh
480 l_refresh_all_tier_flag := 'N';
481 l_refresh_dim_tier_flag := 'N';
482 l_refresh_rate_flag := 'N';
483 ELSE
484 g_period_id := p_period_id;
485
486 -- refresh dynamic dimension tiers
487 l_refresh_all_tier_flag := 'N';
488 l_refresh_rate_flag := 'N';
489 END IF;
490 ELSIF (g_rate_schedule_id = l_rate_schedule_id) THEN
491 g_srp_plan_assign_id := p_srp_plan_assign_id;
492 g_rt_quota_asgn_id := l_rt_quota_asgn_id;
493 g_period_id := p_period_id;
494
495 -- refresh dynamic dimension tiers and commission amounts
496 l_refresh_all_tier_flag := 'N';
497 ELSE
498 -- if (g_rate_schedule_id IS NULL OR g_rate_schedule_id <> l_rate_schedule_id) THEN
499 -- refresh everything
500 g_srp_plan_assign_id := p_srp_plan_assign_id;
501 g_rt_quota_asgn_id := l_rt_quota_asgn_id;
502 g_rate_schedule_id := l_rate_schedule_id;
503 g_period_id := p_period_id;
504 END IF;
505
506
507 IF (l_refresh_all_tier_flag = 'Y') THEN
508 g_dim_tier_table.DELETE;
509 g_dim_size_table.DELETE;
510 g_dim_type_table.DELETE;
511 g_tier_index_table.DELETE;
512 g_dynamic_tier_table.DELETE;
513 g_comm_amount_table.DELETE;
514
518 IF (dim.dim_unit_code = 'STRING') then
515 FOR dim IN dims_info LOOP
516 g_dim_size_table(i) := dim.number_tier;
517
519 g_dim_type_table(i) := 'S';
520 ELSE
521 g_dim_type_table(i) := 'N';
522 END IF;
523
524 IF (i = 1) THEN
525 g_tier_index_table(i) := 1;
526 ELSE
527 g_tier_index_table(i) := g_tier_index_table(i-1) + g_dim_size_table(i-1);
528 END IF;
529
530 FOR dim_tier IN dim_tiers(dim.rate_dimension_id) LOOP
531 g_dim_tier_table(n).tier_sequence := dim_tier.tier_sequence;
532
533 IF (dim.dim_unit_code = 'STRING') THEN
534 g_dim_tier_table(n).string_value := dim_tier.string_value;
535 ELSIF (dim.dim_unit_code = 'EXPRESSION') THEN
536 g_dim_tier_table(n).min_exp_id := dim_tier.min_exp_id;
537 g_dim_tier_table(n).max_exp_id := dim_tier.max_exp_id;
538 g_dynamic_tier_table(j) := n;
539 j := j + 1;
540 ELSE
541 g_dim_tier_table(n).minimum_amount := dim_tier.minimum_amount;
542 g_dim_tier_table(n).maximum_amount := dim_tier.maximum_amount;
543 END IF;
544
545 n := n + 1;
546 END LOOP;
547
548 i := i + 1;
549 END LOOP;
550 END IF;
551
552 IF (l_refresh_dim_tier_flag = 'Y') THEN
553 FOR k IN 1..g_dynamic_tier_table.COUNT LOOP
554 IF (k = 1 OR
555 (k > 1 AND g_dim_tier_table(g_dynamic_tier_table(k-1)).max_exp_id <> g_dim_tier_table(g_dynamic_tier_table(k)).min_exp_id))
556 THEN
557 OPEN tier_exp(g_dim_tier_table(g_dynamic_tier_table(k)).min_exp_id);
558 FETCH tier_exp INTO l_sql_select, l_sql_from;
559 CLOSE tier_exp;
560
561 l_where_clause := ':p_srp_plan_assign_id = :p_srp_plan_assign_id and :p_quota_id = :p_quota_id and :p_period_id = :p_period_id';
562 IF (instr(l_sql_from, 'CN_SRP_QUOTA_ASSIGNS', 1, 1) > 0) THEN
563 l_where_clause := l_where_clause || ' and CSQA.srp_plan_assign_id = :p_srp_plan_assign_id and CSQA.quota_id = :p_quota_id';
564 END IF;
565
566 IF (instr(l_sql_from, 'CN_SRP_PERIOD_QUOTAS', 1, 1) > 0) THEN
567 l_where_clause := l_where_clause || ' and CSPQ.srp_plan_assign_id = :p_srp_plan_assign_id' ||
568 ' and CSPQ.quota_id = :p_quota_id and CSPQ.period_id = :p_period_id';
569 END IF;
570
571 IF (instr(l_sql_from, 'CN_PERIOD_QUOTAS', 1, 1) > 0) THEN
572 l_where_clause := l_where_clause || ' and CPQ.quota_id = :p_quota_id and CPQ.period_id = :p_period_id';
573 END IF;
574
575 IF (instr(l_sql_from, 'CN_QUOTAS', 1, 1) > 0) THEN
576 l_where_clause := l_where_clause || ' and CQ.quota_id = :p_quota_id';
577 END IF;
578
579 l_where_clause := ' where ' || l_where_clause;
580
581 execute immediate 'begin select ' || l_sql_select || ' into :x from ' || l_sql_from || l_where_clause || '; end;'
582 using OUT g_dim_tier_table(g_dynamic_tier_table(k)).minimum_amount, p_srp_plan_assign_id, p_quota_id, p_period_id;
583 ELSE
584 g_dim_tier_table(g_dynamic_tier_table(k)).minimum_amount := g_dim_tier_table(g_dynamic_tier_table(k-1)).maximum_amount;
585 END IF;
586
587 -- get maximum_amount
588 OPEN tier_exp(g_dim_tier_table(g_dynamic_tier_table(k)).max_exp_id);
589 FETCH tier_exp INTO l_sql_select, l_sql_from;
590 CLOSE tier_exp;
591
592 l_where_clause := ':p_srp_plan_assign_id = :p_srp_plan_assign_id and :p_quota_id = :p_quota_id and :p_period_id = :p_period_id';
593 IF (instr(l_sql_from, 'CN_SRP_QUOTA_ASSIGNS', 1, 1) > 0) THEN
594 l_where_clause := l_where_clause || ' and CSQA.srp_plan_assign_id = :p_srp_plan_assign_id and CSQA.quota_id = :p_quota_id';
595 END IF;
596
597 IF (instr(l_sql_from, 'CN_SRP_PERIOD_QUOTAS', 1, 1) > 0) THEN
598 l_where_clause := l_where_clause || ' and CSPQ.srp_plan_assign_id = :p_srp_plan_assign_id' ||
599 ' and CSPQ.quota_id = :p_quota_id and CSPQ.period_id = :p_period_id';
600 END IF;
601
602 IF (instr(l_sql_from, 'CN_PERIOD_QUOTAS', 1, 1) > 0) THEN
603 l_where_clause := l_where_clause || ' and CPQ.quota_id = :p_quota_id and CPQ.period_id = :p_period_id';
604 END IF;
605
606 IF (instr(l_sql_from, 'CN_QUOTAS', 1, 1) > 0) THEN
607 l_where_clause := l_where_clause || ' and CQ.quota_id = :p_quota_id';
608 END IF;
609
610 l_where_clause := ' where ' || l_where_clause;
611
612 execute immediate 'begin select ' || l_sql_select || ' into :x from ' || l_sql_from || l_where_clause || '; end;'
613 using OUT g_dim_tier_table(g_dynamic_tier_table(k)).maximum_amount, p_srp_plan_assign_id, p_quota_id, p_period_id;
614 END LOOP;
615 END IF;
616
617 IF (l_refresh_rate_flag = 'Y') THEN
618 g_rate_tier_id_table.DELETE;
619 g_comm_amount_table.DELETE;
620
621 SELECT customized_flag
622 INTO l_customized_flag
623 FROM cn_srp_quota_assigns_all
624 WHERE srp_plan_assign_id = p_srp_plan_assign_id
625 AND quota_id = p_quota_id;
626
627 IF (l_customized_flag = 'Y') then
628 open comm_amounts;
629 FETCH comm_amounts bulk collect INTO l_comm_amount_table, l_rate_tier_id_table, l_rate_sequence_table;
630 CLOSE comm_amounts;
631
632 if l_comm_amount_table.count > 0 then
633 for i in l_comm_amount_table.first..l_comm_amount_table.last loop
634 g_comm_amount_table(l_rate_sequence_table(i)) := l_comm_amount_table(i);
635 g_rate_tier_id_table(l_rate_sequence_table(i)) := l_rate_tier_id_table(i);
636 end loop;
637 end if;
638 ELSE
639 open comm_amounts2;
640 FETCH comm_amounts2 bulk collect INTO l_comm_amount_table, l_rate_tier_id_table, l_rate_sequence_table;
641 CLOSE comm_amounts2;
642
646 g_rate_tier_id_table(l_rate_sequence_table(i)) := l_rate_tier_id_table(i);
643 if l_comm_amount_table.count > 0 then
644 for i in l_comm_amount_table.first..l_comm_amount_table.last loop
645 g_comm_amount_table(l_rate_sequence_table(i)) := l_comm_amount_table(i);
647 end loop;
648 end if;
649 END IF;
650 END IF;
651
652 IF (p_split_flag = 'N') THEN
653 FOR ctr IN 1 .. p_number_dim LOOP
654 select_tier( p_mul_input_tbl(ctr).rate_dim_sequence,
655 p_mul_input_tbl(ctr).base_amount,
656 p_mul_input_tbl(ctr).input_string,
657 p_mul_input_tbl(ctr).amount,
658 p_mul_input_tbl(ctr).tier_sequence);
659 END LOOP ;
660
661 get_rate_sequence(p_number_dim,
662 p_mul_input_tbl,
663 l_rate_sequence);
664
665 x_rate := get_comm_amount(l_rate_sequence);
666 x_rate_tier_id := get_rate_tier_id(l_rate_sequence);
667 x_tier_split := 1;
668 ELSE
669 l_rate := 0;
670
671 FOR ctr IN 1 .. p_number_dim LOOP
672 select_tier(p_mul_input_tbl(ctr).rate_dim_sequence,
673 p_mul_input_tbl(ctr).base_amount,
674 p_mul_input_tbl(ctr).input_string,
675 p_mul_input_tbl(ctr).amount,
676 p_mul_input_tbl(ctr).tier_sequence);
677 END LOOP;
678
679 get_rate_sequence(p_number_dim,
680 p_mul_input_tbl,
681 l_rate_sequence);
682
683 --x_rate_tier_id := g_rate_tier_id_table(l_rate_sequence);
684 x_rate_tier_id := get_rate_tier_id(l_rate_sequence);
685
686 -- clku
687 IF p_calc_formula_id is null THEN
688
689 l_split_dim := 1;
690 ELSE
691 -- get the splitting dimension
692 OPEN split_dim;
693 FETCH split_dim INTO l_split_dim;
694 CLOSE split_dim;
695
696 END IF;
697
698
699 l_tier_sequence := p_mul_input_tbl(l_split_dim).tier_sequence;
700 l_base_amount := p_mul_input_tbl(l_split_dim).base_amount;
701 l_amount := p_mul_input_tbl(l_split_dim).amount;
702 l_counter := 1;
703
704 l_sequence_cycle := 1;
705 FOR idx IN REVERSE (l_split_dim+1)..g_dim_size_table.COUNT LOOP
706 l_sequence_cycle := l_sequence_cycle * g_dim_size_table(idx);
707 END LOOP;
708
709 IF (l_amount > 0) THEN
710 l_to_next_tier := g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount - l_base_amount;
711 WHILE (l_amount > l_to_next_tier AND l_to_next_tier >= 0) LOOP
712 IF (l_tier_sequence < g_dim_size_table(l_split_dim)) THEN
713 l_split_tbl(l_counter).amount := l_to_next_tier;
714 l_split_tbl(l_counter).tier_range :=
715 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount -
716 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount;
717 l_split_tbl(l_counter).rate := get_comm_amount(l_rate_sequence + (l_counter - 1) * l_sequence_cycle);
718 ELSE
719 EXIT;
720 END IF;
721
722 l_base_amount := g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount;
723 l_amount := l_amount - l_to_next_tier;
724 l_tier_sequence := l_tier_sequence + 1;
725 l_counter := l_counter + 1;
726 l_to_next_tier := g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount - l_base_amount;
727 END LOOP;
728 IF (l_amount > 0) THEN
729 l_split_tbl(l_counter).amount := l_amount;
730 l_split_tbl(l_counter).tier_range :=
731 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount -
732 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount;
733 l_split_tbl(l_counter).rate := get_comm_amount(l_rate_sequence + (l_counter - 1) * l_sequence_cycle);
734 END IF;
735 ELSIF (l_amount < 0) THEN
736 l_to_prev_tier := g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount - l_base_amount;
737 WHILE (l_amount < l_to_prev_tier AND l_to_prev_tier <= 0) LOOP
738 IF (l_tier_sequence > 1) THEN
739 l_split_tbl(l_counter).amount := l_to_prev_tier;
740 l_split_tbl(l_counter).tier_range :=
741 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount -
742 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount;
743 l_split_tbl(l_counter).rate := get_comm_amount(l_rate_sequence - (l_counter - 1) * l_sequence_cycle);
744 ELSE
745 EXIT;
746 END IF;
747
748 l_base_amount := g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount;
749 l_amount := l_amount - l_to_prev_tier;
750 l_tier_sequence := l_tier_sequence - 1;
751 l_counter := l_counter + 1;
752 l_to_prev_tier := g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount - l_base_amount;
753 END LOOP;
754 IF (l_amount < 0) THEN
755 l_split_tbl(l_counter).amount := l_amount;
756 l_split_tbl(l_counter).tier_range :=
757 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount -
758 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount;
759 l_split_tbl(l_counter).rate := get_comm_amount(l_rate_sequence - (l_counter - 1) * l_sequence_cycle);
760 END IF;
761 ELSE
762 l_split_tbl(1).amount := l_amount;
763 l_split_tbl(1).tier_range :=
764 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).maximum_amount -
765 g_dim_tier_table(g_tier_index_table(l_split_dim) + l_tier_sequence - 1).minimum_amount;
766 l_split_tbl(1).rate := get_comm_amount(l_rate_sequence);
767 END IF;
768
772 l_rate := l_rate + (l_split_tbl(i).amount/l_split_tbl(i).tier_range) * l_split_tbl(i).rate;
769
770 IF p_split_flag = 'P' THEN
771 FOR i IN 1 .. l_split_tbl.count LOOP
773 END LOOP;
774 ELSIF p_split_flag = 'Y' THEN
775 FOR i IN 1 .. l_split_tbl.count LOOP
776 IF (p_mul_input_tbl(l_split_dim).amount = 0) THEN
777 l_rate := l_rate + l_split_tbl(i).rate;
778 ELSE
779 l_rate := l_rate + (l_split_tbl(i).amount/p_mul_input_tbl(l_split_dim).amount) * l_split_tbl(i).rate;
780 END IF;
781 END LOOP;
782 END IF;
783
784 x_rate := l_rate;
785 x_tier_split := l_split_tbl.COUNT;
786
787 l_split_tbl.delete;
788 END IF;
789
790 IF x_rate IS NULL THEN
791 RAISE no_data_found;
792 END IF;
793 EXCEPTION
794 WHEN OTHERS THEN
795 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
796 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
797 'cn.plsql.cn_formula_common_pkg.get_rates.exception',
798 sqlerrm);
799 end if;
800
801 cn_message_pkg.debug('Exception occurs in getting commission rate: ');
802 cn_message_pkg.debug(sqlerrm);
803 RAISE;
804 END get_rates;
805
806 PROCEDURE delete_itd_trx( p_salesrep_id NUMBER ,
807 p_srp_plan_assign_id NUMBER ,
808 p_quota_id NUMBER ,
809 p_period_id NUMBER ) IS
810 CURSOR l_itd_trx_csr IS
811 SELECT commission_line_id, commission_header_id,
812 posting_status, commission_amount
813 FROM cn_commission_lines_all
814 WHERE credited_salesrep_id = p_salesrep_id
815 AND srp_plan_assign_id = p_srp_plan_assign_id
816 AND processed_period_id = p_period_id
817 AND quota_id = p_quota_id
818 AND trx_type = 'ITD'
819 AND status = 'CALC';
820
821 l_itd_trx l_itd_trx_csr%ROWTYPE;
822 BEGIN
823 OPEN l_itd_trx_csr;
824 FETCH l_itd_trx_csr INTO l_itd_trx;
825
826 IF l_itd_trx_csr%found THEN
827 IF l_itd_trx.posting_status = 'POSTED' THEN
828 revert_posting_line ( l_itd_trx.commission_line_id);
829 END IF;
830
831 DELETE cn_commission_headers_all
832 WHERE commission_header_id = l_itd_trx.commission_header_id;
833
834 DELETE cn_commission_lines_all
835 WHERE commission_line_id = l_itd_trx.commission_line_id;
836
837 UPDATE cn_srp_period_quotas_all
838 SET commission_payed_ptd = commission_payed_ptd - l_itd_trx.commission_amount,
839 commission_payed_itd = commission_payed_itd - l_itd_trx.commission_amount
840 WHERE salesrep_id = p_salesrep_id
841 AND period_id = p_period_id
842 AND srp_plan_assign_id = p_srp_plan_assign_id
843 AND quota_id = p_quota_id;
844 END IF;
845
846 CLOSE l_itd_trx_csr;
847 END delete_itd_trx;
848
849 -- To initialize before going to calculation. Also determine the x_select_status_flag,
850 -- signaling that trxs in which trx_status will be selected to calculate.
851 PROCEDURE calculate_init( p_srp_plan_assign_id NUMBER,
852 p_salesrep_id NUMBER,
853 p_period_id NUMBER,
854 p_quota_id NUMBER,
855 p_start_date DATE ,
856 p_process_all_flag VARCHAR2,
857 p_intel_calc_flag VARCHAR2,
858 p_calc_type VARCHAR2,
859 p_trx_group_code VARCHAR2,
860 p_itd_flag VARCHAR2,
861 p_rollover_flag VARCHAR2,
862 x_commission_payed_ptd OUT NOCOPY NUMBER ,
863 x_commission_payed_itd OUT NOCOPY NUMBER ,
864 x_input_achieved_ptd OUT NOCOPY num_table_type ,
865 x_input_achieved_itd OUT NOCOPY num_table_type ,
866 x_output_achieved_ptd OUT NOCOPY NUMBER ,
867 x_output_achieved_itd OUT NOCOPY NUMBER ,
868 x_perf_achieved_ptd OUT NOCOPY NUMBER ,
869 x_perf_achieved_itd OUT NOCOPY NUMBER ,
870 x_select_status_flag OUT NOCOPY VARCHAR2 )
871 IS
872 l_incremental_flag cn_quotas.incremental_type%TYPE;
873 l_input_achieved NUMBER ;
874 l_output_achieved NUMBER ;
875 l_perf_achieved NUMBER ;
876 l_commission_achieved NUMBER;
877
878 l_credit_type_name cn_credit_types.name%TYPE;
879 l_start_period_id NUMBER;
880 l_end_period_id NUMBER;
881 l_interval_type_id NUMBER;
882 l_org_id NUMBER;
883 l_same_pe_rollover NUMBER;
884 l_source_pe_rollover NUMBER;
885
886 l_commission_payed_itd NUMBER := 0;
887 l_input_achieved_itd NUMBER := 0;
888 l_output_achieved_itd NUMBER := 0;
889 l_perf_achieved_itd NUMBER := 0;
890 l_advance_recovered_itd NUMBER := 0;
891 l_advance_to_rec_itd NUMBER := 0;
892 l_recovery_amount_itd NUMBER := 0;
893 l_comm_pend_itd NUMBER := 0;
894
895 l_srp_period_quota_id NUMBER(15);
896 l_input_achieved_itd_tbl cn_formula_common_pkg.num_table_type;
897
898 CURSOR l_quota_csr (l_quota_id NUMBER ) IS
899 SELECT q.incremental_type, cr.name, q.interval_type_id, q.org_id
900 FROM cn_quotas_all q,
901 cn_credit_types cr
902 WHERE q.quota_id = l_quota_id
903 AND cr.credit_type_id = q.credit_type_id
904 AND cr.org_id = q.org_id;
905
906 CURSOR other_inputs IS
907 select input_sequence,
908 input_achieved_itd,
909 input_achieved_ptd
910 from cn_srp_period_quotas_ext_all
911 where srp_period_quota_id = (select srp_period_quota_id
912 from cn_srp_period_quotas_all
913 where srp_plan_assign_id = p_srp_plan_assign_id
917 order by input_sequence;
914 and quota_id = p_quota_id
915 and salesrep_id = p_salesrep_id
916 and period_id = p_period_id)
918
919 CURSOR periods_cr IS
920 SELECT spq.rowid,
921 spq.srp_period_quota_id,
922 Nvl(spq.commission_payed_ptd,0) commission_payed_ptd,
923 Nvl(spq.input_achieved_ptd,0) input_achieved_ptd,
924 Nvl(spq.output_achieved_ptd,0) output_achieved_ptd,
925 Nvl(spq.perf_achieved_ptd,0) perf_achieved_ptd,
926 Nvl(spq.advance_recovered_ptd,0) advance_recovered_ptd ,
927 Nvl(spq.advance_to_rec_ptd,0) advance_to_rec_ptd,
928 Nvl(spq.recovery_amount_ptd,0) recovery_amount_ptd,
929 Nvl(spq.comm_pend_ptd,0)comm_pend_ptd
930 FROM cn_srp_period_quotas_all spq
931 WHERE salesrep_id = p_salesrep_id
932 AND period_id > l_start_period_id
933 AND quota_id = p_quota_id
934 AND srp_plan_assign_id = p_srp_plan_assign_id
935 AND period_id <= l_end_period_id
936 ORDER BY spq.period_id ASC;
937
938 CURSOR periods_ext(p_srp_period_quota_id NUMBER) IS
939 SELECT nvl(input_achieved_ptd, 0) input_achieved_ptd,
940 nvl(input_achieved_itd, 0) input_achieved_itd,
941 input_sequence
942 FROM cn_srp_period_quotas_ext_all
943 WHERE srp_period_quota_id = p_srp_period_quota_id
944 ORDER BY input_sequence;
945
946 BEGIN
947 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
948 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
949 'cn.plsql.cn_formula_common_pkg.calculate_init.begin',
950 'Beginning of calculate_init ...');
951 end if;
952
953 OPEN l_quota_csr (p_quota_id);
954 FETCH l_quota_csr INTO l_incremental_flag, l_credit_type_name, l_interval_type_id, l_org_id;
955 CLOSE l_quota_csr;
956
957 l_start_period_id := get_start_period_id(p_quota_id, p_period_id);
958 l_end_period_id := get_end_period_id(p_quota_id, p_period_id);
959 cn_api.get_credit_info(l_credit_type_name, g_precision, g_ext_precision, l_org_id );
960
961 if (p_calc_type = 'BONUS' and p_period_id = l_end_period_id ) then
962 update cn_srp_period_quotas_all
963 set commission_payed_itd = 0, commission_payed_ptd = 0
964 where salesrep_id = p_salesrep_id
965 and quota_id = p_quota_id
966 and period_id >= l_start_period_id
967 and period_id < l_end_period_id
968 and srp_plan_assign_id = p_srp_plan_assign_id;
969 elsif (p_calc_type = 'COMMISSION' and p_period_id = l_start_period_id) then
970 -- initialize the following values for the first period of an interval
971 update cn_srp_period_quotas_all
972 set perf_achieved_itd = perf_achieved_ptd,
973 commission_payed_itd = commission_payed_ptd,
974 input_achieved_itd = input_achieved_ptd,
975 output_achieved_itd = output_achieved_ptd,
976 advance_recovered_itd = advance_recovered_ptd,
977 advance_to_rec_itd = advance_to_rec_ptd,
978 recovery_amount_itd = recovery_amount_ptd,
979 comm_pend_itd = comm_pend_ptd
980 where salesrep_id = p_salesrep_id
981 and quota_id = p_quota_id
982 and period_id = l_start_period_id
983 and srp_plan_assign_id = p_srp_plan_assign_id
984 and (nvl(perf_achieved_ptd, 0) <> nvl(perf_achieved_itd, 0) or
985 nvl(commission_payed_ptd, 0) <> nvl(commission_payed_itd, 0) or
986 nvl(input_achieved_ptd, 0) <> nvl(input_achieved_itd, 0) or
987 nvl(output_achieved_ptd, 0) <> nvl(output_achieved_itd, 0) or
988 nvl(advance_recovered_ptd, 0) <> nvl(advance_recovered_itd, 0) or
989 nvl(advance_to_rec_ptd, 0) <> nvl(advance_to_rec_itd, 0) or
990 nvl(recovery_amount_ptd, 0) <> nvl(recovery_amount_itd, 0) or
991 nvl(comm_pend_ptd, 0) <> nvl(comm_pend_itd, 0))
992 return srp_period_quota_id,
993 perf_achieved_itd,
994 commission_payed_itd,
995 input_achieved_itd,
996 output_achieved_itd,
997 advance_recovered_itd,
998 advance_to_rec_itd,
999 recovery_amount_itd,
1000 comm_pend_itd
1001 into l_srp_period_quota_id,
1002 l_perf_achieved_itd,
1003 l_commission_payed_itd,
1004 l_input_achieved_itd,
1005 l_output_achieved_itd,
1006 l_advance_recovered_itd,
1007 l_advance_to_rec_itd,
1008 l_recovery_amount_itd,
1009 l_comm_pend_itd;
1010
1011 if (SQL%found) then
1012 update cn_srp_period_quotas_ext_all
1013 set input_achieved_itd = input_achieved_ptd
1014 where srp_period_quota_id = l_srp_period_quota_id;
1015
1016 for period_ext in periods_ext(l_srp_period_quota_id) loop
1017 l_input_achieved_itd_tbl(period_ext.input_sequence) := period_ext.input_achieved_itd;
1018 end loop;
1019
1020 for period in periods_cr loop
1021 l_commission_payed_itd := l_commission_payed_itd + period.commission_payed_ptd;
1022 l_input_achieved_itd := l_input_achieved_itd + period.input_achieved_ptd;
1023 l_output_achieved_itd := l_output_achieved_itd + period.output_achieved_ptd;
1024 l_perf_achieved_itd := l_perf_achieved_itd + period.perf_achieved_ptd;
1025 l_advance_recovered_itd := l_advance_recovered_itd + period.advance_recovered_ptd;
1026 l_advance_to_rec_itd := l_advance_to_rec_itd + period.advance_to_rec_ptd;
1027 l_recovery_amount_itd :=l_recovery_amount_itd + period.recovery_amount_ptd ;
1028 l_comm_pend_itd := l_comm_pend_itd + period.comm_pend_ptd;
1029
1030 update cn_srp_period_quotas_all
1031 set commission_payed_itd = l_commission_payed_itd,
1032 input_achieved_itd = l_input_achieved_itd,
1033 output_achieved_itd = l_output_achieved_itd,
1034 perf_achieved_itd = l_perf_achieved_itd,
1038 comm_pend_itd = l_comm_pend_itd,
1035 advance_recovered_itd = l_advance_recovered_itd,
1036 advance_to_rec_itd = l_advance_to_rec_itd,
1037 recovery_amount_itd = l_recovery_amount_itd,
1039 LAST_UPDATE_DATE = sysdate,
1040 LAST_UPDATED_BY = fnd_global.user_id,
1041 LAST_UPDATE_LOGIN = fnd_global.login_id
1042 where rowid = period.rowid;
1043
1044 for period_ext in periods_ext(period.srp_period_quota_id) loop
1045 l_input_achieved_itd_tbl(period_ext.input_sequence) :=
1046 l_input_achieved_itd_tbl(period_ext.input_sequence) + period_ext.input_achieved_ptd;
1047
1048 update cn_srp_period_quotas_ext_all
1049 set input_achieved_itd = l_input_achieved_itd_tbl(period_ext.input_sequence),
1050 LAST_UPDATE_DATE = sysdate,
1051 LAST_UPDATED_BY = fnd_global.user_id,
1052 LAST_UPDATE_LOGIN = fnd_global.login_id
1053 where srp_period_quota_id = period.srp_period_quota_id
1054 and input_sequence = period_ext.input_sequence;
1055 end loop;
1056
1057 end loop;
1058 end if;
1059 end if;
1060
1061 IF (p_rollover_flag = 'Y') THEN
1062 -- retrieve the current plan element's rollover target from the previous interval
1063 SELECT nvl(SUM(rollover), 0)
1064 INTO l_same_pe_rollover
1065 FROM cn_srp_period_quotas_all
1066 WHERE srp_plan_assign_id = p_srp_plan_assign_id
1067 AND quota_id = p_quota_id
1068 AND period_id = (SELECT MAX(cal_period_id)
1069 FROM cn_cal_per_int_types_all
1070 WHERE interval_type_id = l_interval_type_id
1071 AND cal_period_id < p_period_id
1072 AND org_id = l_org_id
1073 AND interval_number <> (SELECT interval_number
1074 FROM cn_cal_per_int_types_all
1075 WHERE interval_type_id = l_interval_type_id
1076 AND org_id = l_org_id
1077 AND cal_period_id = p_period_id));
1078
1079 -- retrieve source plan elements' rollover targets and compute the total amount
1080 SELECT SUM(nvl(cspq.rollover, 0) * csrq.rollover / 100)
1081 INTO l_source_pe_rollover
1082 FROM cn_srp_rollover_quotas_all csrq,
1083 cn_srp_period_quotas_all cspq
1084 WHERE csrq.quota_id = p_quota_id
1085 AND csrq.srp_quota_assign_id = (SELECT srp_quota_assign_id
1086 FROM cn_srp_quota_assigns_all
1087 WHERE srp_plan_assign_id = p_srp_plan_assign_id
1088 AND quota_id = p_quota_id)
1089 AND cspq.salesrep_id = p_salesrep_id
1090 AND cspq.quota_id = csrq.source_quota_id
1091 AND cspq.period_id = (SELECT MAX(period_id)
1092 FROM cn_srp_period_quotas_all
1093 WHERE salesrep_id = p_salesrep_id
1094 AND quota_id = csrq.source_quota_id
1095 AND srp_quota_assign_id = cspq.srp_quota_assign_id)
1096 AND cspq.period_id < p_period_id
1097 AND NOT exists (SELECT 1
1098 FROM cn_cal_per_int_types_all ccpit,
1099 cn_srp_period_quotas_all cspq2
1100 WHERE ccpit.org_id = l_org_id
1101 AND ccpit.cal_period_id > (SELECT MAX(period_id)
1102 FROM cn_srp_period_quotas_all
1103 WHERE salesrep_id = p_salesrep_id
1104 AND quota_id = csrq.source_quota_id
1105 AND srp_quota_assign_id = cspq.srp_quota_assign_id)
1106 AND ccpit.cal_period_id < p_period_id
1107 AND cspq2.srp_plan_assign_id = p_srp_plan_assign_id
1108 AND cspq2.quota_id = p_quota_id
1109 AND cspq2.period_id = ccpit.cal_period_id
1110 AND ccpit.interval_type_id = l_interval_type_id
1111 AND ccpit.interval_number <> (SELECT interval_number
1112 FROM cn_cal_per_int_types_all
1113 WHERE interval_type_id = l_interval_type_id
1114 AND cal_period_id = p_period_id
1115 AND org_id = l_org_id))
1116 ;
1117
1118 UPDATE cn_srp_period_quotas_all
1119 SET total_rollover = l_same_pe_rollover + nvl(l_source_pe_rollover, 0)
1120 WHERE srp_plan_assign_id = p_srp_plan_assign_id
1121 AND quota_id = p_quota_id
1122 AND period_id = p_period_id;
1123
1124 END IF;
1125
1126 IF (p_itd_flag = 'Y' AND p_calc_type <> 'FORECAST') THEN
1127 delete_itd_trx( p_salesrep_id, p_srp_plan_assign_id,
1128 p_quota_id, p_period_id );
1129 END IF;
1130
1131 /******************************************************************************
1132 IF allowing no prior adjustment, plan element with individual and accumulative
1133 setting can be calculated incrementally.
1134 ******************************************************************************/
1135 IF (nvl(cn_system_parameters.value('CN_PRIOR_ADJUSTMENT', l_org_id), 'N') = 'N' ) THEN
1136 l_incremental_flag := 'Y';
1137 cn_message_pkg.debug('Profile CN_PRIOR_ADJUSTMENT has value: No ');
1138 END IF;
1139
1140 /******************************************************************************
1141 1). when process_all_flag = 'Y', do non incremental calc.
1142 2). when process_all_flag = 'N' , then
1143 a). if l_incremental_flag = 'Y', do incremental calc
1144 b). else incremental_flag = 'N', do non incremental_calc
1145 for non incremental calc. pick POP, CALC, XCALC
1146 For incremental calculation, only pick trx in 'POP' status
1147 ******************************************************************************/
1148
1149 IF p_process_all_flag = 'Y' OR
1150 (p_process_all_flag = 'N' AND l_incremental_flag = 'N' ) THEN
1151 x_select_status_flag := 'PCX';
1152 ELSE
1153 x_select_status_flag := 'P';
1154 END IF;
1155
1156 cn_message_pkg.debug('Parameters that control the calculation initialization');
1157 cn_message_pkg.debug('--p_process_all_flag: ' || p_process_all_flag);
1158 cn_message_pkg.debug('--l_incremental_flag: ' || l_incremental_flag);
1162 -- initialize ptd value to be 0, ptd = 0;
1159 cn_message_pkg.debug('--p_select_status_flag: ' || x_select_status_flag);
1160 cn_message_pkg.debug('--p_trx_group_code: ' || p_trx_group_code);
1161
1163 x_input_achieved_ptd(1) := 0;
1164 x_output_achieved_ptd := 0;
1165 x_perf_achieved_ptd := 0;
1166 x_commission_payed_ptd := 0;
1167
1168 -- initialize itd value to itd minus ptd, itd = itd -ptd
1169 IF (p_period_id = l_start_period_id) THEN
1170 x_input_achieved_itd(1) := 0;
1171 x_output_achieved_itd := 0;
1172 x_perf_achieved_itd := 0;
1173 x_commission_payed_itd := 0;
1174 ELSE
1175 SELECT Nvl(quota.input_achieved_itd, 0) - Nvl(quota.input_achieved_ptd, 0),
1176 Nvl(quota.output_achieved_itd, 0) - Nvl(quota.output_achieved_ptd, 0),
1177 Nvl(quota.perf_achieved_itd, 0) - Nvl(quota.perf_achieved_ptd, 0),
1178 Nvl(quota.commission_payed_itd, 0) - Nvl(quota.commission_payed_ptd, 0)
1179 INTO x_input_achieved_itd(1), x_output_achieved_itd,
1180 x_perf_achieved_itd, x_commission_payed_itd
1181 FROM cn_srp_period_quotas_all quota
1182 WHERE quota.srp_plan_assign_id = p_srp_plan_assign_id
1183 AND quota.quota_id = p_quota_id
1184 AND quota.salesrep_id = p_salesrep_id
1185 AND quota.period_id = p_period_id;
1186 END IF;
1187
1188 FOR other_input IN other_inputs LOOP
1189 x_input_achieved_ptd(other_input.input_sequence) := 0;
1190 IF (p_period_id = l_start_period_id) THEN
1191 x_input_achieved_itd(other_input.input_sequence) := 0;
1192 ELSE
1193 x_input_achieved_itd(other_input.input_sequence) := nvl(other_input.input_achieved_itd, 0) - nvl(other_input.input_achieved_ptd, 0);
1194 END IF;
1195 END LOOP;
1196
1197 IF p_calc_type = 'COMMISSION' THEN
1198 IF p_process_all_flag = 'Y' THEN
1199 IF p_intel_calc_flag = 'N' THEN
1200 -- need to sum the trx before p_start_date to get ptd
1201 IF p_trx_group_code = 'INDIVIDUAL' THEN
1202 SELECT
1203 SUM(line.input_achieved), SUM(line.output_achieved),
1204 SUM(line.perf_achieved), SUM(line.commission_amount)
1205 INTO l_input_achieved, l_output_achieved,
1206 l_perf_achieved, l_commission_achieved
1207 FROM cn_commission_lines_all line
1208 WHERE line.credited_salesrep_id = p_salesrep_id
1209 AND line.quota_id = p_quota_id
1210 AND line.srp_plan_assign_id = p_srp_plan_assign_id
1211 AND line.status = 'CALC'
1212 AND line.processed_date < p_start_date
1213 AND line.processed_period_id = p_period_id
1214 AND ((g_calc_type ='FORECAST' AND line.trx_type = 'FORECAST')
1215 OR (g_calc_type ='BONUS' AND line.trx_type = 'BONUS')
1216 OR (g_calc_type = 'COMMISSION'
1217 AND line.trx_type NOT IN ('BONUS','FORECAST', 'GRP') ));
1218
1219 x_commission_payed_ptd := Nvl(l_commission_achieved,0);
1220 x_input_achieved_ptd(1) := Nvl(l_input_achieved, 0);
1221 x_output_achieved_ptd := Nvl(l_output_achieved, 0);
1222 x_perf_achieved_ptd := Nvl(l_perf_achieved, 0);
1223
1224 IF (p_period_id = l_start_period_id) THEN
1225 x_commission_payed_itd := x_commission_payed_ptd;
1226 x_input_achieved_itd(1) := x_input_achieved_ptd(1);
1227 x_output_achieved_itd := x_output_achieved_ptd;
1228 x_perf_achieved_itd := x_perf_achieved_ptd;
1229 ELSE
1230 x_commission_payed_itd := x_commission_payed_itd + x_commission_payed_ptd;
1231 x_input_achieved_itd(1) := x_input_achieved_itd(1) + x_input_achieved_ptd(1);
1232 x_output_achieved_itd := x_output_achieved_itd + x_output_achieved_ptd;
1233 x_perf_achieved_itd := x_perf_achieved_itd + x_perf_achieved_ptd ;
1234 END IF;
1235 END IF;
1236 -- else 'group by', itd = itd-ptd, ptd initialized to 0
1237 END IF;
1238 -- ELSIF p_process_all_flag = 'Y' AND p_intel_calc_flag = 'Y' THEN
1239 --- non incremental calc itd = itd -ptd , ptd initialized to 0;
1240
1241 ELSIF p_process_all_flag = 'N' THEN -- must be intel calc
1242 -- case1 : IF l_incremental_flag = 'N', need to recalc every trx
1243 -- non incremental calc THEN itd = itd -ptd, ptd intialized to 0
1244
1245 -- case2 : IF l_incremental_flag = 'Y' and trx_group_code = 'GROUP'
1246 -- itd = itd -ptd, ptd initialized to 0
1247 -- because group by plan element always do a bulk group function
1248 -- when it reaches the end of interval.
1249
1250 -- case3 : IF l_incremental_flag = 'Y' and trx_group_code = 'INDIVIDUAL'
1251 -- itd = itd, ptd = ptd
1252 IF l_incremental_flag = 'Y' AND p_trx_group_code = 'INDIVIDUAL' THEN
1253 cn_message_pkg.debug('p_trx_group_code: ' || p_trx_group_code );
1254
1255 SELECT nvl(quota.input_achieved_itd,0), Nvl( quota.input_achieved_ptd, 0),
1256 nvl(quota.output_achieved_itd,0), Nvl(quota.output_achieved_ptd, 0),
1257 nvl(quota.perf_achieved_itd, 0), Nvl(quota.perf_achieved_ptd, 0),
1258 nvl(quota.commission_payed_itd ,0), Nvl(quota.commission_payed_ptd, 0)
1259 INTO x_input_achieved_itd(1), x_input_achieved_ptd(1),
1260 x_output_achieved_itd, x_output_achieved_ptd,
1261 x_perf_achieved_itd, x_perf_achieved_ptd,
1262 x_commission_payed_itd, x_commission_payed_ptd
1263 FROM cn_srp_period_quotas_all quota
1264 WHERE quota.srp_plan_assign_id = p_srp_plan_assign_id
1265 AND quota.quota_id = P_quota_id
1266 AND quota.salesrep_id = P_salesrep_id
1267 AND quota.period_id = P_period_id;
1268
1269 IF (p_period_id = l_start_period_id) THEN
1270 x_input_achieved_itd(1) := x_input_achieved_ptd(1);
1271 x_output_achieved_itd := x_output_achieved_ptd;
1272 x_perf_achieved_itd := x_perf_achieved_ptd;
1273 x_commission_payed_itd := x_commission_payed_ptd;
1274 END IF;
1275
1276 FOR other_input IN other_inputs LOOP
1280 ELSE
1277 x_input_achieved_ptd(other_input.input_sequence) := nvl(other_input.input_achieved_ptd, 0);
1278 IF (p_period_id = l_start_period_id) THEN
1279 x_input_achieved_itd(other_input.input_sequence) := x_input_achieved_ptd(other_input.input_sequence);
1281 x_input_achieved_itd(other_input.input_sequence) := nvl(other_input.input_achieved_itd, 0);
1282 END IF;
1283 END LOOP;
1284
1285 END IF;
1286 END IF; -- end of p_process_all_flag check
1287
1288 cn_message_pkg.debug('Initialized values');
1289 FOR i IN 1..x_input_achieved_itd.COUNT LOOP
1290 cn_message_pkg.debug('--x_input_achieved_itd: ' || x_input_achieved_itd(i));
1291 END LOOP;
1292
1293 cn_message_pkg.debug('--x_output_achieved_itd: ' || x_output_achieved_itd);
1294 cn_message_pkg.debug('--x_perf_achieved_itd: ' || x_perf_achieved_itd);
1295 cn_message_pkg.debug('--x_commission_payed_itd: ' || x_commission_payed_itd );
1296
1297 FOR i IN 1..x_input_achieved_ptd.COUNT LOOP
1298 cn_message_pkg.debug('--x_input_achieved_ptd: ' || x_input_achieved_ptd(i));
1299 END LOOP;
1300 cn_message_pkg.debug('--x_output_achieved_ptd: ' || x_output_achieved_ptd);
1301 cn_message_pkg.debug('--x_perf_achieved_ptd: ' || x_perf_achieved_ptd);
1302 cn_message_pkg.debug('--x_commission_payed_ptd: ' || x_commission_payed_ptd );
1303
1304 ELSIF p_calc_type = 'BONUS' THEN
1305 -- do nothing since we already initialize ptd =0 and itd = itd - ptd
1306 -- because bonus type formula are guaranteed to be NON-cumulative, INDIVIDUAL, NON-itd
1307 NULL;
1308 END IF;
1309
1310 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1311 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1312 'cn.plsql.cn_formula_common_pkg.calculate_init.end',
1313 'End of calculate_init');
1314 end if;
1315
1316 END calculate_init;
1317
1318
1319
1320 FUNCTION get_last_period_id ( p_quota_id NUMBER, p_period_id NUMBER,p_srp_plan_assign_id NUMBER )
1321 RETURN NUMBER IS
1322 l_end_period_id NUMBER(15);
1323 BEGIN
1324 select max(PERIOD_ID)
1325 INTO l_end_period_id
1326 from cn_srp_period_quotas_all
1327 where QUOTA_ID=p_quota_id
1328 and srp_plan_assign_id=p_srp_plan_assign_id;
1329
1330 RETURN l_end_period_id;
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333
1334 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1335 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1336 'cn.plsql.cn_formula_common_pkg.get_end_period_id.exception',
1337 sqlerrm);
1338 end if;
1339
1340 RETURN NULL ;
1341 END get_last_period_id;
1342
1343 FUNCTION EndOfGroupByInterval( p_quota_id NUMBER, p_period_id NUMBER,p_srp_plan_assign_id NUMBER )
1344 RETURN BOOLEAN IS
1345 l_end_period_id NUMBER(15);
1346 l_last_period_id NUMBER(15);
1347 BEGIN
1348 l_end_period_id := get_end_period_id(p_quota_id, p_period_id);
1349
1350
1351 IF p_period_id = l_end_period_id THEN
1352 RETURN TRUE;
1353 ELSE
1354 l_last_period_id := get_last_period_id(p_quota_id, p_period_id,p_srp_plan_assign_id);
1355
1356 IF p_period_id = l_last_period_id THEN
1357 RETURN TRUE;
1358 ELSE
1359 RETURN FALSE;
1360 END IF;
1361
1362 END IF;
1363
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 RETURN FALSE ;
1367 END EndOfGroupByInterval;
1368
1369
1370
1371
1372
1373
1374
1375 FUNCTION EndOfInterval (p_quota_id NUMBER, p_period_id NUMBER)
1376 RETURN BOOLEAN IS
1377 l_end_period_id NUMBER(15);
1378 BEGIN
1379 l_end_period_id := get_end_period_id(p_quota_id, p_period_id);
1380
1381 IF p_period_id = l_end_period_id THEN
1382 RETURN TRUE;
1383 ELSE
1384 RETURN FALSE;
1385 END IF;
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 RETURN FALSE ;
1389 END EndOfInterval;
1390
1391 -- get the start_period_id for the interval
1392 FUNCTION get_start_period_id ( p_quota_id NUMBER, p_period_id NUMBER )
1393 RETURN NUMBER IS
1394 l_start_period_id NUMBER(15);
1395 BEGIN
1396 SELECT MIN(p2.cal_period_id)
1397 INTO l_start_period_id
1398 FROM cn_cal_per_int_types_all p2
1399 WHERE (p2.interval_type_id, p2.org_id, p2.interval_number) IN
1400 (SELECT p1.interval_type_id, p1.org_id, p1.interval_number
1401 FROM cn_cal_per_int_types_all p1,
1402 cn_quotas_all q
1403 WHERE p1.cal_period_id = p_period_id
1404 AND q.quota_id = p_quota_id
1405 AND p1.org_id = q.org_id
1406 AND p1.interval_type_id = q.interval_type_id);
1407
1408 RETURN l_start_period_id;
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 RETURN NULL ;
1412 END get_start_period_id;
1413
1414 -- get the end_period_id for the interval
1415 FUNCTION get_end_period_id ( p_quota_id NUMBER, p_period_id NUMBER )
1416 RETURN NUMBER IS
1417 l_end_period_id NUMBER(15);
1418 BEGIN
1419 SELECT MAX(p2.cal_period_id)
1420 INTO l_end_period_id
1421 FROM cn_cal_per_int_types_all p2
1422 WHERE (p2.interval_type_id, p2.org_id, p2.interval_number) IN
1423 (SELECT p1.interval_type_id, p1.org_id, p1.interval_number
1424 FROM cn_cal_per_int_types_all p1,
1425 cn_quotas_all q
1429 AND p1.interval_type_id = q.interval_type_id);
1426 WHERE p1.cal_period_id = p_period_id
1427 AND q.quota_id = p_quota_id
1428 AND p1.org_id = q.org_id
1430
1431 RETURN l_end_period_id;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434
1435 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1436 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1437 'cn.plsql.cn_formula_common_pkg.get_end_period_id.exception',
1438 sqlerrm);
1439 end if;
1440
1441 RETURN NULL ;
1442 END get_end_period_id;
1443
1444 FUNCTION get_quarter_start_period_id(p_quota_id NUMBER, p_period_id NUMBER) RETURN NUMBER IS
1445 l_start_period_id NUMBER(15);
1446 BEGIN
1447 select min(a.cal_period_id)
1448 INTO l_start_period_id
1449 from cn_cal_per_int_types_all a,
1450 cn_period_statuses_all b
1451 where (a.interval_type_id, a.org_id) = (select interval_type_id, org_id
1452 from cn_quotas_all
1453 where quota_id = p_quota_id)
1454 and a.interval_number = (select interval_number
1455 from cn_cal_per_int_types_all
1456 where cal_period_id = p_period_id
1457 and (interval_type_id, org_id) = (select interval_type_id, org_id
1458 from cn_quotas_all
1459 where quota_id = p_quota_id))
1460 and a.cal_period_id = b.period_id
1461 and b.quarter_num = (select quarter_num
1462 from cn_period_statuses_all
1463 where period_id = p_period_id
1464 and org_id = (select org_id from cn_quotas_all where quota_id = p_quota_id));
1465
1466 RETURN l_start_period_id;
1467 END get_quarter_start_period_id;
1468
1469 FUNCTION get_quarter_end_period_id(p_quota_id NUMBER, p_period_id NUMBER) RETURN NUMBER IS
1470 l_end_period_id NUMBER(15);
1471 BEGIN
1472 select max(a.cal_period_id)
1473 INTO l_end_period_id
1474 from cn_cal_per_int_types_all a,
1475 cn_period_statuses_all b
1476 where (a.interval_type_id, a.org_id) = (select interval_type_id, org_id
1477 from cn_quotas_all
1478 where quota_id = p_quota_id)
1479 and a.interval_number = (select interval_number from cn_cal_per_int_types_all
1480 where cal_period_id = p_period_id
1481 and (interval_type_id, org_id) = (select interval_type_id, org_id
1482 from cn_quotas_all
1483 where quota_id = p_quota_id))
1484 and a.cal_period_id = b.period_id
1485 and b.quarter_num = (select quarter_num
1486 from cn_period_statuses_all
1487 where period_id = p_period_id
1488 and org_id = (select org_id from cn_quotas_all where quota_id = p_quota_id));
1489
1490 RETURN l_end_period_id;
1491 END get_quarter_end_period_id;
1492
1493
1494 -- To update cn_srp_period_quotas, cn_srp_per_quota_rc, cn_srp_periods after the calculation of a quota is done
1495 PROCEDURE calculate_roll ( p_salesrep_id number,
1496 p_period_id number,
1497 p_quota_id number,
1498 p_srp_plan_assign_id NUMBER,
1499 p_calc_type VARCHAR2,
1500 p_input_achieved_ptd num_table_type,
1501 p_input_achieved_itd num_table_type,
1502 p_output_achieved_ptd number,
1503 p_output_achieved_itd number,
1504 p_perf_achieved_ptd number,
1505 p_perf_achieved_itd NUMBER,
1506 p_rollover NUMBER) IS
1507
1508 l_srp_pe_subledger cn_calc_subledger_pvt.srp_pe_subledger_rec_type;
1509 l_return_status VARCHAR2(1);
1510 l_msg_count NUMBER;
1511 l_msg_data VARCHAR2(2000);
1512 l_api_version NUMBER := 1.0;
1513 l_counter NUMBER;
1514 BEGIN
1515 l_srp_pe_subledger.salesrep_id := p_salesrep_id;
1516 l_srp_pe_subledger.quota_id := p_quota_id;
1517 l_srp_pe_subledger.accu_period_id := p_period_id;
1518 l_srp_pe_subledger.srp_plan_assign_id := p_srp_plan_assign_id;
1519 l_srp_pe_subledger.input_ptd := p_input_achieved_ptd;
1520 l_srp_pe_subledger.input_itd := p_input_achieved_itd;
1521 l_srp_pe_subledger.output_ptd := Nvl(p_output_achieved_ptd, 0);
1522 l_srp_pe_subledger.output_itd := Nvl(p_output_achieved_itd, 0);
1523 l_srp_pe_subledger.perf_ptd := Nvl(p_perf_achieved_ptd, 0);
1524 l_srp_pe_subledger.perf_itd := Nvl(p_perf_achieved_itd, 0);
1525 IF (p_rollover > 0) THEN
1526 l_srp_pe_subledger.rollover := p_rollover;
1527 ELSE
1528 l_srp_pe_subledger.rollover := 0;
1529 END IF;
1530
1531
1532 l_srp_pe_subledger.calc_type := p_calc_type;
1533
1534 cn_calc_subledger_pvt.update_srp_pe_subledger
1535 ( p_api_version => l_api_version,
1536 p_init_msg_list => fnd_api.g_true,
1537 x_return_status => l_return_status,
1538 x_msg_count => l_msg_count,
1539 x_msg_data => l_msg_data,
1540 p_srp_pe_subledger => l_srp_pe_subledger);
1541
1542
1543 FOR l_counter IN 1..l_msg_count LOOP
1544 cn_message_pkg.debug( substr(FND_MSG_PUB.get(p_msg_index => l_counter,
1548 WHEN OTHERS THEN
1545 p_encoded => FND_API.G_FALSE), 1, 249 ));
1546 END LOOP;
1547 EXCEPTION
1549 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1550 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1551 'cn.plsql.cn_formula_common_pkg.calculate_roll.exception',
1552 sqlerrm);
1553 end if;
1554
1555 cn_message_pkg.debug('Exception occurs in calculate_roll ');
1556 cn_message_pkg.debug(sqlerrm);
1557 RAISE;
1558 END calculate_roll;
1559
1560 -- Work as a dispatcher to invoke the corresponding cn_formula_id_pkg.calculate_quota
1561 PROCEDURE calculate_quota( p_srp_plan_assign_id NUMBER,
1562 p_salesrep_id NUMBER,
1563 p_period_id NUMBER,
1564 p_start_date DATE ,
1565 p_quota_id NUMBER,
1566 p_process_all_flag VARCHAR2,
1567 p_intel_calc_flag VARCHAR2 ,
1568 p_calc_type VARCHAR2 ,
1569 p_latest_processed_date OUT NOCOPY DATE ) IS
1570 l_latest_processed_date DATE ;
1571 l_quota_type VARCHAR2(30);
1572 l_formula_id NUMBER(15);
1573 l_formula_name VARCHAR2(30);
1574 l_pe_name VARCHAR2(80);
1575 l_formula_type VARCHAR2(30);
1576 l_role_id NUMBER;
1577 l_credit_type_id NUMBER;
1578 l_bonus_credit_type_id NUMBER;
1579 l_org_id NUMBER;
1580 l_statement VARCHAR2(1000);
1581 l_debug_flag VARCHAR2(1) := fnd_profile.value('CN_DEBUG');
1582 BEGIN
1583 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1584 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1585 'cn.plsql.cn_formula_common_pkg.calculate_quota.begin',
1586 'Begin of Calculate_Quota:'||' p_srp_plan_assign_id ' || p_srp_plan_assign_id ||
1587 ' p_salesrep_id ' || p_salesrep_id ||
1588 ' p_period_id ' || p_period_id ||
1589 ' p_start_date ' || p_start_date ||
1590 ' p_quota_id ' || p_quota_id ||
1591 ' p_intel_calc_flag ' || p_intel_calc_flag ||
1592 ' p_calc_type ' || p_calc_type);
1593 end if;
1594
1595 SELECT q.quota_type_code,
1596 q.calc_formula_id,
1597 q.credit_type_id,
1598 q.bonus_credit_type_id,
1599 nvl(f.name, q.package_name),
1600 nvl(f.formula_type, decode(g_calc_type, 'COMMISSION', 'C', 'B')),
1601 q.org_id,
1602 q.name
1603 INTO l_quota_type,
1604 l_formula_id,
1605 l_credit_type_id,
1606 l_bonus_credit_type_id,
1607 l_formula_name,
1608 l_formula_type,
1609 l_org_id,
1610 l_pe_name
1611 FROM cn_quotas_all q,
1612 cn_calc_formulas_all f
1613 WHERE q.quota_id = p_quota_id
1614 AND q.calc_formula_id = f.calc_formula_id(+)
1615 AND q.org_id = f.org_id(+);
1616
1617 if (l_debug_flag = 'Y') then
1618 select name into l_statement from cn_salesreps where salesrep_id = p_salesrep_id and org_id = l_org_id;
1619 cn_message_pkg.debug('Resource: '||l_statement);
1620 cn_message_pkg.debug('Plan element: '||l_pe_name);
1621 cn_message_pkg.debug('Formula: '||l_formula_name);
1622 cn_message_pkg.debug('Calculation parameters:');
1623 cn_message_pkg.debug('--p_srp_plan_assign_id: ' || p_srp_plan_assign_id);
1624 cn_message_pkg.debug('--p_salesrep_id: ' || p_salesrep_id);
1625 cn_message_pkg.debug('--p_period_id: ' || p_period_id);
1626 cn_message_pkg.debug('--p_start_date: ' || p_start_date);
1627 cn_message_pkg.debug('--p_quota_id: ' || p_quota_id);
1628 cn_message_pkg.debug('--p_intel_calc_flag: ' || p_intel_calc_flag);
1629 cn_message_pkg.debug('--p_calc_type: ' || p_calc_type);
1630 end if;
1631
1632 IF l_quota_type = 'FORMULA' THEN
1633 l_statement := 'begin cn_formula_' || abs(l_formula_id) || '_' || abs(l_org_id) || '_pkg';
1634 ELSIF l_quota_type = 'EXTERNAL' THEN
1635 l_statement := 'begin ' || l_formula_name;
1636 END IF;
1637
1638 l_statement := l_statement || '.calculate_quota(:srp_plan_assign_id, :salesrep_id,'||
1639 ':period_id, :start_date, :quota_id, :process_all_flag, ' ||
1640 ':intel_calc_flag, :calc_type, :credit_type_id, ';
1641
1642 IF l_formula_type = 'C' THEN
1643 l_statement := l_statement || ':latest_processed_date ); end ;' ;
1644 ELSIF l_formula_type = 'B' THEN
1645 l_statement := l_statement || ':role_id, :latest_processed_date ); end ;' ;
1646 END IF;
1647 IF l_formula_type = 'C' THEN
1648 execute immediate l_statement using p_srp_plan_assign_id, p_salesrep_id, p_period_id,
1649 p_start_date, p_quota_id, p_process_all_flag, p_intel_calc_flag, p_calc_type, l_credit_type_id,
1650 IN OUT p_latest_processed_date;
1651
1652 ELSIF l_formula_type = 'B' THEN
1653 SELECT role.role_id INTO l_role_id
1654 FROM cn_srp_plan_assigns_all spa,
1655 cn_srp_roles role
1656 WHERE spa.srp_plan_assign_id = p_srp_plan_assign_id
1657 AND role.srp_role_id = spa.srp_role_id
1658 AND role.org_id = spa.org_id;
1659
1660 execute immediate l_statement using p_srp_plan_assign_id, p_salesrep_id, p_period_id,
1661 p_start_date, p_quota_id, p_process_all_flag, p_intel_calc_flag, p_calc_type,
1662 l_credit_type_id, l_role_id, IN OUT p_latest_processed_date ;
1663 END IF;
1664
1665 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1666 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1667 'cn.plsql.cn_formula_common_pkg.calculate_quota.end',
1668 'End of Calculate_Quota.');
1669 end if;
1670 cn_message_pkg.debug('Finish caculating plan element: '||l_pe_name);
1674 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1671 cn_message_pkg.debug(' ');
1672 EXCEPTION
1673 WHEN OTHERS THEN
1675 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1676 'cn.plsql.cn_formula_common_pkg.calculate_quota.exception',
1677 sqlerrm);
1678 end if;
1679 cn_message_pkg.debug('Excpetion occurs in calculate_quota dispatcher: ');
1680 cn_message_pkg.debug(sqlerrm);
1681 RAISE;
1682 END calculate_quota;
1683
1684
1685 PROCEDURE update_consistency_flag( x_calc_batch_id NUMBER ) IS
1686 BEGIN
1687 UPDATE cn_srp_periods_all
1688 SET consistency_flag = 'Y'
1689 WHERE (salesrep_id, period_id, org_id) IN
1690 ( SELECT batch.salesrep_id, batch.period_id, batch.org_id
1691 FROM cn_process_batches_all batch
1692 WHERE batch.physical_batch_id = x_calc_batch_id);
1693 END update_consistency_flag;
1694
1695
1696 PROCEDURE swap_dates ( p_curr_date IN OUT NOCOPY DATE , p_next_date DATE) IS
1697 BEGIN
1698 IF Nvl(p_next_date, p_curr_date) > p_curr_date THEN
1699 p_curr_date := p_next_date;
1700 END IF;
1701 END swap_dates;
1702
1703 PROCEDURE calculate_batch(p_physical_batch_id NUMBER) IS
1704 l_process_all_flag varchar2(30);
1705 l_salesrep_option VARCHAR2(30);
1706 l_current_processed_date date;
1707 l_next_processed_date date;
1708 l_calc_sub_batch_rec cn_calc_sub_batches_pkg.calc_sub_batch_rec_type;
1709 l_srp_subledger cn_calc_subledger_pvt.srp_subledger_rec_type;
1710 l_msg_data varchar2(3000);
1711 l_return_status VARCHAR2(30);
1712 l_msg_count number(15);
1713 l_counter NUMBER;
1714 l_interval_type_id VARCHAR2(30);
1715 l_calc_sub_batch_id NUMBER(15);
1716 l_quota_sequence pls_integer;
1717 l_org_id NUMBER;
1718
1719 -- select all complete comp plan
1720 CURSOR l_srp_plan_periods_csr IS
1721 SELECT spa.salesrep_id,
1722 spa.srp_plan_assign_id,
1723 prd.period_id,
1724 prd.process_all_flag,
1725 decode(prd.period_id, batch.period_id, batch.start_date, prd.start_date) start_date
1726 FROM cn_process_batches_all batch,
1727 cn_srp_plan_assigns_all spa,
1728 cn_srp_intel_periods_all prd
1729 WHERE batch.physical_batch_id = p_physical_batch_id
1730 AND prd.salesrep_id = batch.salesrep_id
1731 AND prd.period_id BETWEEN batch.period_id AND batch.end_period_id
1732 AND prd.org_id = batch.org_id
1733 AND spa.salesrep_id = batch.salesrep_id
1734 AND spa.org_id = batch.org_id
1735 AND spa.start_date <= prd.end_date
1736 AND nvl(spa.end_date, prd.end_date) >= prd.start_date
1737 ORDER BY spa.salesrep_id, prd.period_id, spa.srp_plan_assign_id;
1738
1739 CURSOR l_srp_quotas_csr(l_srp_plan_assign_id NUMBER,
1740 l_salesrep_id NUMBER,
1741 l_period_id NUMBER) IS
1742 SELECT spq.quota_id,
1743 qa.quota_sequence
1744 FROM cn_srp_period_quotas_all spq,
1745 cn_quota_assigns_all qa,
1746 cn_quotas_all q
1747 WHERE spq.srp_plan_assign_id = l_srp_plan_assign_id
1748 AND spq.salesrep_id = l_salesrep_id
1749 AND spq.period_id = l_period_id
1750 and qa.comp_plan_id = (select comp_plan_id
1751 from cn_srp_plan_assigns_all
1752 where srp_plan_assign_id = l_srp_plan_assign_id)
1753 and qa.quota_id = spq.quota_id
1754 and q.quota_id = spq.quota_id
1755 and q.incentive_type_code = 'COMMISSION'
1756 order by spq.srp_plan_assign_id, qa.quota_sequence;
1757
1758 CURSOR l_check_calc_entry_csr (l_salesrep_id NUMBER, l_period_id NUMBER ) IS
1759 SELECT 1
1760 FROM cn_notify_log_all
1761 WHERE (salesrep_id = l_salesrep_id OR salesrep_id = -1000)
1762 AND period_id = l_period_id
1763 AND revert_state = 'CALC'
1764 AND status = 'INCOMPLETE'
1765 AND quota_id IS NULL
1766 AND org_id = l_org_id;
1767
1768 -- select those commission, not calculate_for_last type which has an entry
1769 -- in notify log. If salesrep_option is 'reps_in_notify_log', then we can do
1770 -- only these plan elements to improve the performance.
1771 CURSOR l_notify_log_csr (l_salesrep_id NUMBER,
1772 l_period_id NUMBER,
1773 l_quota_id number ) IS
1774 SELECT 1
1775 FROM cn_notify_log_all nlog
1776 WHERE nlog.salesrep_id = l_salesrep_id
1777 AND nlog.period_id = l_period_id
1778 AND nlog.status = 'INCOMPLETE'
1779 AND nlog.quota_id = l_quota_id
1780 AND nlog.revert_state IN ('CALC', 'POP');
1781
1782 CURSOR l_bonus_pe_csr IS
1783 SELECT inlv.srp_plan_assign_id, inlv.salesrep_id,
1784 inlv.end_period_id, inlv.end_date, inlv.quota_id, inlv.interval_type_id
1785 FROM (
1786 SELECT spa.srp_plan_assign_id srp_plan_assign_id, batch.salesrep_id salesrep_id, qa.quota_sequence quota_sequence,
1787 batch.end_period_id end_period_id, batch.end_date end_date, pe.quota_id quota_id, pe.interval_type_id interval_type_id
1788 FROM cn_srp_plan_assigns_all spa,
1789 cn_quota_assigns_all qa,
1790 cn_quotas_all pe,
1791 cn_process_batches_all batch
1792 WHERE batch.physical_batch_id = p_physical_batch_id
1793 AND batch.salesrep_id = spa.salesrep_id
1794 AND spa.org_id = batch.org_id
1795 -- find comp plans active on batch.end_date
1796 AND ((spa.end_date IS NOT NULL AND batch.end_date BETWEEN spa.start_date AND spa.end_date)
1797 OR (spa.end_date IS NULL AND batch.end_date >= spa.start_date))
1798 -- find bonus type plan element
1799 AND qa.comp_plan_id = spa.comp_plan_id
1803 OR (l_interval_type_id = -1001 AND pe.interval_type_id = -1001)
1800 AND qa.quota_id = pe.quota_id
1801 AND pe.incentive_type_code = 'BONUS'
1802 AND ((l_interval_type_id = -1000 AND pe.interval_type_id = -1000)
1804 OR (l_interval_type_id = -1002 AND pe.interval_type_id = -1002)
1805 OR (l_interval_type_id = -1003 AND pe.interval_type_id IN (-1000, -1001, -1002)))
1806 -- plan element is effective on batch.end_date
1807 AND ((pe.end_date IS NOT NULL AND batch.end_date BETWEEN pe.start_date AND pe.end_date)
1808 OR (pe.end_date IS NULL AND batch.end_date >= pe.start_date))
1809 -- check if in cn_calc_sub_quotas if that exists
1810 AND (l_calc_sub_batch_id = -1000 OR pe.quota_id IN (SELECT csq.quota_id
1811 FROM cn_calc_sub_quotas csq
1812 WHERE csq.calc_sub_batch_id = l_calc_sub_batch_id))
1813 UNION
1814 SELECT spa.srp_plan_assign_id srp_plan_assign_id, batch.salesrep_id salesrep_id, qa.quota_sequence quota_sequence,
1815 batch.end_period_id end_period_id, batch.end_date end_date, pe.quota_id quota_id, pe.interval_type_id interval_type_id
1816 FROM cn_srp_plan_assigns_all spa,
1817 cn_quota_assigns_all qa,
1818 cn_quotas_all pe,
1819 cn_process_batches_all batch
1820 WHERE batch.physical_batch_id = p_physical_batch_id
1821 AND batch.salesrep_id = spa.salesrep_id
1822 AND spa.org_id = batch.org_id
1823 -- find comp plans active between batch start and end date
1824 AND spa.end_date >= batch.start_date
1825 AND spa.end_date < batch.end_date
1826 -- find bonus type plan element
1827 AND qa.comp_plan_id = spa.comp_plan_id
1828 AND qa.quota_id = pe.quota_id
1829 AND pe.incentive_type_code = 'BONUS'
1830 AND pe.salesreps_enddated_flag = 'Y'
1831 AND ((l_interval_type_id = -1000 AND pe.interval_type_id = -1000)
1832 OR (l_interval_type_id = -1001 AND pe.interval_type_id = -1001)
1833 OR (l_interval_type_id = -1002 AND pe.interval_type_id = -1002)
1834 OR (l_interval_type_id = -1003 AND pe.interval_type_id IN (-1000, -1001, -1002)))
1835 -- plan element is effective on comp_plan.end_date
1836 AND ( (pe.end_date IS NOT NULL AND spa.end_date BETWEEN pe.start_date AND pe.end_date)
1837 OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL))
1838 -- check if in cn_calc_sub_quotas if that exists
1839 AND (l_calc_sub_batch_id = -1000 OR pe.quota_id IN (SELECT csq.quota_id
1840 FROM cn_calc_sub_quotas csq
1841 WHERE csq.calc_sub_batch_id = l_calc_sub_batch_id))) inlv
1842 ORDER BY inlv.salesrep_id, inlv.end_date, inlv.quota_sequence;
1843
1844
1845 BEGIN
1846 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1847 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1848 'cn.plsql.cn_formula_common_pkg.calculate_batch.begin',
1849 'Beginning of calculate_batch ...');
1850 end if;
1851
1852 select org_id into l_org_id
1853 from cn_process_batches_all
1854 where physical_batch_id = p_physical_batch_id and rownum = 1;
1855
1856 -- for maintaining cn_repositories.latest_processed_date
1857 SELECT nvl(latest_processed_date, to_date('01/01/1900', 'DD/MM/YYYY'))
1858 INTO l_current_processed_date
1859 FROM cn_repositories_all
1860 WHERE org_id = l_org_id;
1861
1862 g_refresh_flag := 'Y';
1863
1864 -- populate uplift factors (payment_factor, quota_factor, event_factor) and payee_assigned first
1865 populate_factors(p_physical_batch_id);
1866 commit;
1867
1868 cn_calc_sub_batches_pkg.get_calc_sub_batch( p_physical_batch_id, l_calc_sub_batch_rec);
1869
1870 g_intel_calc_flag := l_calc_sub_batch_rec.intelligent_flag;
1871 l_salesrep_option := l_calc_sub_batch_rec.salesrep_option;
1872 g_calc_type := l_calc_sub_batch_rec.calc_type;
1873 l_calc_sub_batch_id := l_calc_sub_batch_rec.calc_sub_batch_id;
1874 l_interval_type_id := l_calc_sub_batch_rec.interval_type_id;
1875
1876 IF (g_calc_type = 'COMMISSION') THEN
1877 UPDATE cn_commission_lines_all line
1878 SET line.status = 'XCALC',
1879 line.error_reason = 'skip calc with null commission_amount',
1880 last_update_date = sysdate,
1881 last_updated_by = g_last_updated_by,
1882 last_update_login = g_last_update_login
1883 WHERE line.commission_line_id in
1884 (SELECT line2.commission_line_id
1885 FROM cn_process_batches_all batch,
1886 cn_commission_lines_all line2,
1887 cn_commission_headers_all ch
1888 WHERE batch.physical_batch_id = p_physical_batch_id
1889 AND line2.commission_header_id = ch.commission_header_id
1890 AND line2.credited_salesrep_id = batch.salesrep_id
1891 AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
1892 AND line2.processed_date >= batch.start_date
1893 AND line2.status = 'POP'
1894 AND line2.org_id = batch.org_id
1895 AND line2.trx_type NOT IN ('FORECAST', 'BONUS')
1896 AND substr(line2.pre_processed_code, 4, 1) = 'N'
1897 AND ch.commission_amount is null );
1898 commit;
1899
1900 UPDATE cn_commission_lines_all line
1901 SET line.status = 'CALC',
1902 line.commission_amount = (select amthead.commission_amount
1903 from cn_commission_headers_all amthead,
1904 cn_commission_lines_all amtline
1905 where amthead.commission_header_id = amtline.commission_header_id
1906 and amtline.commission_line_id = line.commission_line_id
1910 last_updated_by = g_last_updated_by,
1907 ),
1908 line.credit_type_id = (select credit_type_id from cn_quotas_all where quota_id = line.quota_id),
1909 last_update_date = sysdate,
1911 last_update_login = g_last_update_login
1912 WHERE line.commission_line_id in
1913 (SELECT line2.commission_line_id
1914 FROM cn_process_batches_all batch,
1915 cn_commission_lines_all line2,
1916 cn_commission_headers_all ch
1917 WHERE batch.physical_batch_id = p_physical_batch_id
1918 AND line2.commission_header_id = ch.commission_header_id
1919 AND line2.credited_salesrep_id = batch.salesrep_id
1920 AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
1921 AND line2.processed_date >= batch.start_date
1922 AND line2.org_id = batch.org_id
1923 AND line2.status = 'POP'
1924 AND line2.trx_type NOT IN ('FORECAST', 'BONUS')
1925 AND substr(line2.pre_processed_code, 4, 1) = 'N'
1926 AND ch.commission_amount is not null );
1927
1928 commit;
1929
1930 FOR spp IN l_srp_plan_periods_csr LOOP
1931 l_quota_sequence := 10000;
1932
1933 -- g_intel_calc_flag = 'N' non intelligent calc
1934 -- OR (g_intel_calc_flag = 'Y' AND l_process_all_flag = 'Y')
1935 -- there is revert due to 'COL', 'CLS', 'ROLL', ACTION events
1936 -- OR (g_intel_calc_flag = 'Y' AND l_salesrep_option = 'ALL_REPS' )
1937 -- always guarantee that all plan elements will be picked up.
1938 IF (g_intel_calc_flag = 'N' OR
1939 (g_intel_calc_flag = 'Y' AND spp.process_all_flag = 'Y') OR
1940 (g_intel_calc_flag = 'Y' AND l_salesrep_option = 'ALL_REPS'))
1941 THEN
1942 FOR l_pe IN l_srp_quotas_csr (spp.srp_plan_assign_id, spp.salesrep_id, spp.period_id) LOOP
1943 if (l_quota_sequence > l_pe.quota_sequence) then
1944 l_quota_sequence := l_pe.quota_sequence;
1945 end if;
1946
1947 if (l_pe.quota_sequence > l_quota_sequence) then
1948 l_process_all_flag := 'Y';
1949 else
1950 l_process_all_flag := spp.process_all_flag;
1951 end if;
1952
1953 calculate_quota( spp.srp_plan_assign_id,
1954 spp.salesrep_id,
1955 spp.period_id,
1956 spp.start_date,
1957 l_pe.quota_id,
1958 l_process_all_flag,
1959 g_intel_calc_flag,
1960 g_calc_type,
1961 l_next_processed_date);
1962 swap_dates(l_current_processed_date, l_next_processed_date);
1963
1964 END LOOP;
1965 ELSE --IF (g_intel_calc_flag = 'Y' AND spp.process_all_flag = 'N') THEN
1966 -- in this scenario, we might not have to do all the plan elements depending on the
1967 -- entries in notify_log
1968 -- 1). if 'CALC' with null quota_id exists,
1969 -- get all quotas
1970 -- a). IF quotas with entry with 'POP', 'CALC' event
1971 -- --> guarantee non incremental CALC
1972 -- b). ELSE for those quota with no 'POP', 'CALC' entries
1973 -- --> incremental calc is still possible
1974 -- 2). ELSE only calcualte those quotas with entry 'POP' or 'CALC' plus calculate for last quotas
1975 -- a). quotas with entry with 'POP', 'CALC' event--> non incremental CALC
1976
1977 OPEN l_check_calc_entry_csr(spp.salesrep_id, spp.period_id);
1978 FETCH l_check_calc_entry_csr INTO l_counter;
1979
1980 IF l_check_calc_entry_csr%found THEN
1981 cn_message_pkg.debug('Process plan elements in notify log only (try incremental calc)');
1982 FOR l_pe IN l_srp_quotas_csr(spp.srp_plan_assign_id, spp.salesrep_id, spp.period_id) LOOP
1983 if (l_quota_sequence > l_pe.quota_sequence) then
1984 l_quota_sequence := l_pe.quota_sequence;
1985 end if;
1986
1987 open l_notify_log_csr(spp.salesrep_id, spp.period_id, l_pe.quota_id);
1988 fetch l_notify_log_csr into l_counter;
1989 if (l_notify_log_csr%found or l_pe.quota_sequence > l_quota_sequence) then
1990 l_process_all_flag := 'Y';
1991 else
1992 l_process_all_flag := spp.process_all_flag;
1993 end if;
1994 close l_notify_log_csr;
1995
1996 calculate_quota(
1997 spp.srp_plan_assign_id,
1998 spp.salesrep_id,
1999 spp.period_id,
2000 spp.start_date,
2001 l_pe.quota_id,
2002 l_process_all_flag,
2003 g_intel_calc_flag,
2004 g_calc_type,
2005 l_next_processed_date );
2006 swap_dates ( l_current_processed_date, l_next_processed_date);
2007
2008 END LOOP;
2009 ELSE
2010 cn_message_pkg.debug('Process plan elements in notify log only (Non incremental calc)');
2011 FOR l_pe IN l_srp_quotas_csr(spp.srp_plan_assign_id, spp.salesrep_id, spp.period_id) LOOP
2012 open l_notify_log_csr(spp.salesrep_id, spp.period_id, l_pe.quota_id);
2013 fetch l_notify_log_csr into l_counter;
2014 if (l_notify_log_csr%found or l_pe.quota_sequence > l_quota_sequence) then
2015 calculate_quota(
2016 spp.srp_plan_assign_id,
2017 spp.salesrep_id,
2018 spp.period_id,
2019 spp.start_date,
2020 l_pe.quota_id,
2021 'Y', -- non incremental calc
2022 g_intel_calc_flag,
2023 g_calc_type,
2024 l_next_processed_date );
2028 close l_notify_log_csr;
2025 swap_dates ( l_current_processed_date, l_next_processed_date);
2026
2027 end if;
2029 END LOOP;
2030 END IF;
2031
2032 CLOSE l_check_calc_entry_csr;
2033 END IF;
2034 END LOOP;
2035 ELSIF g_calc_type = 'BONUS' THEN
2036 SELECT COUNT(*) INTO l_counter
2037 FROM cn_calc_sub_quotas
2038 WHERE calc_sub_batch_id = l_calc_sub_batch_id;
2039
2040 -- no particular bonus plan elements are specified
2041 IF l_counter = 0 THEN
2042 l_calc_sub_batch_id := -1000;
2043 END IF;
2044
2045 FOR l_pe IN l_bonus_pe_csr LOOP
2046 IF (l_interval_type_id = -1003 AND l_pe.interval_type_id <> -1000) THEN
2047 -- need to check whether it's the end of the interval
2048 IF cn_proc_batches_pkg.check_end_of_interval(l_pe.end_period_id, l_pe.interval_type_id, l_org_id) THEN
2049 calculate_quota(l_pe.srp_plan_assign_id,
2050 l_pe.salesrep_id,
2051 l_pe.end_period_id,
2052 l_pe.end_date,
2053 l_pe.quota_id,
2054 'Y', -- p_process_all_flag
2055 g_intel_calc_flag,
2056 g_calc_type,
2057 l_next_processed_date);
2058 swap_dates(l_current_processed_date, l_next_processed_date);
2059
2060 END IF;
2061 ELSE
2062 calculate_quota(l_pe.srp_plan_assign_id,
2063 l_pe.salesrep_id,
2064 l_pe.end_period_id,
2065 l_pe.end_date,
2066 l_pe.quota_id,
2067 'Y', -- p_process_all_flag
2068 g_intel_calc_flag,
2069 g_calc_type,
2070 l_next_processed_date);
2071 swap_dates(l_current_processed_date, l_next_processed_date);
2072
2073 END IF;
2074 END LOOP;
2075 END IF ;
2076
2077 IF (g_calc_type = 'COMMISSION') THEN
2078 -- update all leftover 'POP' status trx to be 'XCALC'.
2079 UPDATE cn_commission_lines_all line
2080 SET line.status = 'XCALC',
2081 last_update_date = sysdate,
2082 last_updated_by = g_last_updated_by,
2083 last_update_login = g_last_update_login
2084 WHERE line.commission_line_id IN
2085 (SELECT line2.commission_line_id
2086 FROM cn_process_batches_all batch,
2087 cn_commission_lines_all line2
2088 WHERE batch.physical_batch_id = p_physical_batch_id
2089 AND line2.org_id = batch.org_id
2090 AND line2.credited_salesrep_id = batch.salesrep_id
2091 AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
2092 AND line2.processed_date >= batch.start_date
2093 AND line2.status = 'POP'
2094 AND substr(line2.pre_processed_code, 4, 1) = 'C'
2095 AND trx_type NOT IN ('FORECAST', 'BONUS'));
2096
2097 commit;
2098
2099 -- obsolete the entries in cn_notify_log for single salesrep_id, for all salesrep entries, those are handled in processor
2100 /* UPDATE cn_notify_log_all Log
2101 SET Log.status = 'COMPLETE'
2102 WHERE Log.notify_log_id IN
2103 (SELECT log2.notify_log_id
2104 FROM cn_notify_log_all log2,
2105 cn_process_batches_all batch
2106 WHERE batch.physical_batch_id = p_physical_batch_id
2107 AND log2.org_id = batch.org_id
2108 AND log2.salesrep_id = batch.salesrep_id
2109 AND log2.period_id BETWEEN batch.period_id AND batch.end_period_id
2110 AND log2.status = 'INCOMPLETE'
2111 AND log2.start_date >= batch.start_date);
2112 */
2113 --fix for the Bug 10011406
2114 MERGE INTO cn_notify_log_all log
2115 USING cn_process_batches_all batch
2116 ON ( batch.physical_batch_id = p_physical_batch_id
2117 AND batch.org_id = log.org_id
2118 AND batch.salesrep_id = log.salesrep_id
2119 AND log.period_id BETWEEN batch.period_id AND batch.end_period_id
2120 AND log.start_date >= batch.start_date)
2121 WHEN MATCHED
2122 THEN
2123 UPDATE
2124 SET log.status = 'COMPLETE'
2125 WHERE log.status = 'INCOMPLETE';
2126
2127 commit;
2128
2129 -- Update all records in notify log that are related to Hierarchical changes.
2130 UPDATE cn_notify_log_all Log
2131 SET Log.status = 'COMPLETE'
2132 WHERE Log.notify_log_id IN (
2133 SELECT event.notify_log_id
2134 FROM cn_notify_log_all event
2135 WHERE event.physical_batch_id = p_physical_batch_id
2136 AND event.action IN ('SOURCE_CLS', 'XROLL', 'ROLL_PULL', 'DELETE_ROLL_PULL')
2137 AND event.status = 'INCOMPLETE'
2138 UNION
2139 SELECT event.notify_log_id
2140 FROM cn_notify_log_all event, cn_process_batches_all batch
2141 WHERE batch.physical_batch_id = p_physical_batch_id
2142 AND batch.salesrep_id = event.salesrep_id
2143 AND event.org_id = batch.org_id
2144 AND event.period_id between batch.period_id and batch.end_period_id
2145 AND event.action IN ('PULL', 'PULL_WITHIN', 'PULL_BELOW')
2146 AND event.status = 'INCOMPLETE') ;
2147 commit;
2148
2149 -- reset process_all_flag = 'N' since calculation completed sucessfully
2150 update cn_srp_intel_periods_all a
2151 set a.process_all_flag = 'N'
2152 where a.org_id = l_org_id
2153 and a.salesrep_id in (select salesrep_id from cn_process_batches_all
2157 and salesrep_id = a.salesrep_id)
2154 where physical_batch_id = p_physical_batch_id)
2155 and a.period_id >= (select min(period_id) from cn_process_batches_all
2156 where physical_batch_id = p_physical_batch_id
2158 and a.period_id <= (select max(end_period_id) from cn_process_batches_all
2159 where physical_batch_id = p_physical_batch_id
2160 and salesrep_id = a.salesrep_id);
2161 commit;
2162 END IF;
2163
2164 -- for maintaining cn_repositories.latest_processed_date
2165 UPDATE cn_repositories_all
2166 SET latest_processed_date = l_current_processed_date
2167 WHERE latest_processed_date < l_current_processed_date
2168 AND org_id = l_org_id;
2169
2170 commit;
2171
2172 -- update each salesrep subledger
2173 l_srp_subledger.physical_batch_id := p_physical_batch_id;
2174
2175 cn_calc_subledger_pvt.update_srp_subledger
2176 (p_api_version => 1.0,
2177 p_init_msg_list => fnd_api.g_true,
2178 x_return_status => l_return_status,
2179 x_msg_count => l_msg_count,
2180 x_msg_data => l_msg_data,
2181 p_srp_subledger => l_srp_subledger);
2182
2183 IF l_return_status <> FND_API.g_ret_sts_success THEN
2184 cn_message_pkg.debug('Exception occurs in updating subledgers:');
2185 FOR l_counter IN 1..l_msg_count LOOP
2186 cn_message_pkg.debug(FND_MSG_PUB.get(p_msg_index => l_counter,
2187 p_encoded => FND_API.G_FALSE));
2188 fnd_file.put_line(fnd_file.Log, fnd_msg_pub.get(p_msg_index => l_counter,
2189 p_encoded => FND_API.G_FALSE));
2190 END LOOP;
2191
2192 RAISE api_call_failed;
2193 END IF;
2194
2195 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2196 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2197 'cn.plsql.cn_formula_common_pkg.calculate_batch.end',
2198 'End of calculate_batch ...');
2199 end if;
2200 EXCEPTION
2201 WHEN others THEN
2202 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2203 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2204 'cn.plsql.cn_formula_common_pkg.calculate_batch.exception',
2205 sqlerrm);
2206 end if;
2207
2208 cn_message_pkg.debug('Exception occurs in calculate_batch: ');
2209 cn_message_pkg.rollback_errormsg_commit(sqlerrm);
2210 raise;
2211 END calculate_batch;
2212
2213 -- p_commission_header_id will be the commisson_header_id of the reversal trx created
2214 PROCEDURE handle_reversal_trx ( p_commission_header_id NUMBER) IS
2215
2216 CURSOR c_affected_reps IS
2217 select distinct credited_salesrep_id, processed_date, processed_period_id, org_id
2218 FROM cn_commission_lines_all
2219 WHERE status = 'OBSOLETE'
2220 and posting_status = 'UNPOSTED'
2221 and (commission_header_id = (SELECT reversal_header_id
2222 FROM cn_commission_headers_all
2223 WHERE commission_header_id = p_commission_header_id)
2224 OR commission_header_id = (SELECT parent_header_id
2225 FROM cn_commission_headers_all
2226 WHERE commission_header_id = (SELECT reversal_header_id
2227 FROM cn_commission_headers_all
2228 WHERE commission_header_id = p_commission_header_id)));
2229
2230 CURSOR l_orig_posted_trx IS
2231 SELECT commission_line_id
2232 FROM cn_commission_lines_all
2233 WHERE (commission_header_id = (SELECT reversal_header_id
2234 FROM cn_commission_headers_all
2235 WHERE commission_header_id = p_commission_header_id)
2236 OR commission_header_id = (SELECT parent_header_id
2237 FROM cn_commission_headers_all
2238 WHERE commission_header_id = (SELECT reversal_header_id
2239 FROM cn_commission_headers_all
2240 WHERE commission_header_id = p_commission_header_id)))
2241
2242 AND status = 'CALC'
2243 AND posting_status = 'POSTED';
2244 BEGIN
2245 -- revert all posted trx generated from the original commission_header line
2246 FOR l_line IN l_orig_posted_trx LOOP
2247 revert_posting_line( l_line.commission_line_id );
2248 END LOOP;
2249
2250 -- update all trx generated from the original commission_header line
2251 -- to be obsolete
2252 UPDATE cn_commission_lines_all
2253 SET status = 'OBSOLETE', posting_status = 'UNPOSTED'
2254 WHERE (commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
2255 WHERE commission_header_id = p_commission_header_id)
2256 OR commission_header_id = (SELECT parent_header_id
2257 FROM cn_commission_headers_all
2258 WHERE commission_header_id = (SELECT reversal_header_id
2259 FROM cn_commission_headers_all
2260 WHERE commission_header_id = p_commission_header_id)));
2261
2262 FOR rep IN c_affected_reps LOOP
2263 cn_mark_events_pkg.mark_notify
2264 ( p_salesrep_id => rep.credited_salesrep_id,
2265 p_period_id => rep.processed_period_id,
2266 p_start_date => rep.processed_date,
2267 p_end_date => rep.processed_date,
2268 p_quota_id => NULL,
2269 p_revert_to_state => 'CALC',
2270 p_event_log_id => NULL,
2271 p_org_id => rep.org_id);
2272 end loop;
2276 ( commission_line_id, credited_salesrep_id,
2273
2274 -- create a negative copy of the above lines
2275 INSERT INTO cn_commission_lines_all
2277 processed_period_id, processed_date,
2278 quota_id, credit_type_id, quota_rule_id,
2279 event_factor, payment_factor,
2280 quota_factor, commission_amount,
2281 rate_tier_id, commission_rate,
2282 payee_line_id, status,
2283 trx_type, tier_split,
2284 created_during, created_by,
2285 creation_date, last_updated_by,
2286 last_update_login, last_update_date,
2287 commission_header_id, srp_plan_assign_id,
2288 posting_status, input_achieved,
2289 output_achieved, perf_achieved,
2290 pay_period_id, pending_status,
2291 role_id, pending_date, credited_comp_group_id, org_id )
2292 SELECT cn_commission_lines_s.nextval, line.credited_salesrep_id,
2293 line.processed_period_id, line.processed_date,
2294 line.quota_id, line.credit_type_id, line.quota_rule_id,
2295 line.event_factor, line.payment_factor,
2296 line.quota_factor, -( Nvl(line.commission_amount, 0) ),
2297 line.rate_tier_id, line.commission_rate,
2298 line.commission_line_id, -- specify that it's a negative copy
2299 line.status,
2300 line.trx_type, line.tier_split,
2301 line.created_during, g_created_by,
2302 g_creation_date, g_last_updated_by,
2303 g_last_update_login, sysdate,
2304 p_commission_header_id, line.srp_plan_assign_id,
2305 line.posting_status, -( Nvl(line.input_achieved,0) ),
2306 -( Nvl(line.output_achieved,0)), -( Nvl(line.perf_achieved,0)),
2307 line.pay_period_id, line.pending_status,
2308 line.role_id, line.pending_date, line.credited_comp_group_id, line.org_id
2309 FROM cn_commission_lines_all line
2310 WHERE (line.commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
2311 WHERE commission_header_id = p_commission_header_id)
2312 OR line.commission_header_id = (SELECT parent_header_id
2313 FROM cn_commission_headers_all
2314 WHERE commission_header_id = (SELECT reversal_header_id
2315 FROM cn_commission_headers_all
2316 WHERE commission_header_id = p_commission_header_id)));
2317
2318 -- update commission_header.status to be 'OBSOLETE'
2319 UPDATE cn_commission_headers_all ch SET
2320 ch.status = 'OBSOLETE',
2321 -- clku, update the last updated info
2322 last_update_date = sysdate,
2323 last_updated_by = G_LAST_UPDATED_BY,
2324 last_update_login = G_LAST_UPDATE_LOGIN
2325 WHERE commission_header_id = p_commission_header_id;
2326
2327 UPDATE cn_commission_headers_all ch SET
2328 ch.status = 'OBSOLETE',
2329 -- clku, update the last updated info
2330 last_update_date = sysdate,
2331 last_updated_by = G_LAST_UPDATED_BY,
2332 last_update_login = G_LAST_UPDATE_LOGIN
2333 WHERE commission_header_id
2334 IN (SELECT head.reversal_header_id
2335 FROM cn_commission_headers_all head
2336 WHERE head.commission_header_id = p_commission_header_id );
2337
2338 -- delete sum trx
2339 DELETE FROM cn_commission_headers_all
2340 WHERE commission_header_id = (SELECT parent_header_id
2341 FROM cn_commission_headers_all
2342 WHERE commission_header_id = (SELECT reversal_header_id
2343 FROM cn_commission_headers_all
2344 WHERE commission_header_id = p_commission_header_id));
2345 -- for source trxs of the sum trx, set their parent_header_id = null
2346 UPDATE cn_commission_headers_all
2347 SET parent_header_id = NULL,
2348 -- clku, update the last updated info
2349 last_update_date = sysdate,
2350 last_updated_by = G_LAST_UPDATED_BY,
2351 last_update_login = G_LAST_UPDATE_LOGIN
2352 WHERE parent_header_id = (SELECT parent_header_id
2353 FROM cn_commission_headers_all
2354 WHERE commission_header_id = (SELECT reversal_header_id
2355 FROM cn_commission_headers_all
2356 WHERE commission_header_id = p_commission_header_id));
2357 END handle_reversal_trx;
2358
2359 -- delete/update derived commission_lines
2360 -- it's invoked from classification package to intelligently handle reclassification
2361 -- it's assumed that during CLS phase, a trx with 'OBSOLETE' status would be picked up.
2362 -- so in this procedure, we don't have to worry about that.
2363 PROCEDURE revert_header_lines( p_commission_header_id NUMBER, p_revert_state VARCHAR2) IS
2364 CURSOR l_posted_trxs_csr IS
2365 SELECT commission_line_id
2366 FROM cn_commission_lines_all
2367 WHERE commission_header_id = p_commission_header_id
2368 AND posting_status = 'POSTED'
2369 AND status = 'CALC';
2370
2371 CURSOR l_posted_trxs_csr2 IS
2372 SELECT commission_line_id
2373 FROM cn_commission_lines_all
2374 WHERE commission_header_id = (SELECT parent_header_id
2375 FROM cn_commission_headers_all
2376 WHERE commission_header_id = p_commission_header_id)
2377 AND posting_status = 'POSTED'
2378 AND status = 'CALC';
2379 BEGIN
2380 IF p_revert_state = 'XCLS' THEN
2381 -- revert posted line before deleting any commission lines
2382 FOR l_line IN l_posted_trxs_csr LOOP
2383 revert_posting_line( l_line.commission_line_id);
2384 END LOOP;
2385
2386 FOR l_line IN l_posted_trxs_csr2 LOOP
2387 revert_posting_line( l_line.commission_line_id);
2388 END LOOP;
2389
2390 DELETE cn_commission_lines_all
2391 WHERE commission_header_id = p_commission_header_id;
2392
2393 DELETE cn_commission_lines_all
2394 WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2398 WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2395 WHERE commission_header_id = p_commission_header_id);
2396
2397 DELETE cn_commission_headers_all
2399 WHERE commission_header_id = p_commission_header_id);
2400
2401 UPDATE cn_commission_headers_all
2402 SET parent_header_id = NULL,
2403 last_update_date = sysdate,
2404 last_updated_by = G_LAST_UPDATED_BY,
2405 last_update_login = G_LAST_UPDATE_LOGIN
2406 WHERE parent_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2407 WHERE commission_header_id = p_commission_header_id);
2408
2409 ELSIF p_revert_state = 'ROLL' THEN
2410 -- revert posted line before deleting any commission lines
2411 FOR l_line IN l_posted_trxs_csr LOOP
2412 revert_posting_line( l_line.commission_line_id);
2413 END LOOP;
2414
2415 FOR l_line IN l_posted_trxs_csr2 LOOP
2416 revert_posting_line( l_line.commission_line_id);
2417 END LOOP;
2418
2419 DELETE cn_commission_lines_all
2420 WHERE commission_header_id = p_commission_header_id
2421 AND created_during IN ( 'POP', 'CALC');
2422
2423 DELETE cn_commission_lines_all
2424 WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2425 WHERE commission_header_id = p_commission_header_id);
2426
2427 DELETE cn_commission_headers_all
2428 WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2429 WHERE commission_header_id = p_commission_header_id);
2430
2431 UPDATE cn_commission_headers_all
2432 SET parent_header_id = NULL,
2433 last_update_date = sysdate,
2434 last_updated_by = G_LAST_UPDATED_BY,
2435 last_update_login = G_LAST_UPDATE_LOGIN
2436 WHERE parent_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2437 WHERE commission_header_id = p_commission_header_id);
2438
2439 UPDATE cn_commission_lines_all
2440 SET status = 'ROLL',
2441 posting_status = 'UNPOSTED',
2442 event_factor = NULL,
2443 payment_factor = NULL,
2444 quota_factor = NULL,
2445 rate_tier_id = NULL,
2446 commission_rate = NULL,
2447 tier_split = NULL,
2448 input_achieved = NULL,
2449 output_achieved = NULL,
2450 perf_achieved = NULL,
2451 error_reason = NULL,
2452 srp_payee_assign_id = NULL,
2453 threshold_check_status = NULL,
2454 srp_plan_assign_id = NULL,
2455 quota_id = NULL,
2456 quota_rule_id = NULL,
2457 last_update_date = sysdate,
2458 last_updated_by = g_last_updated_by,
2459 last_update_login = g_last_update_login
2460 WHERE commission_header_id = p_commission_header_id;
2461 END IF;
2462 END revert_header_lines;
2463
2464 Procedure revert_batch_intel_comm (p_physical_batch_id cn_process_batches.physical_batch_id%TYPE ) IS
2465 -- 'COL' event which need to reclassify, will be handled in CLASSIFICATION phase
2466 -- 'CLS' event which only happened when mangerial rollup flag is changed,
2467 -- will be handled here, set process_all_flag = 'Y'
2468 -- 'ROLL' event, handled here set process_all_flag = 'Y'
2469 -- 'POP' event, ??? set process_all_flag = 'Y', THRESHOLD ???
2470 -- 'CALC' event, not handled here, only action 'DELETE' is handled.
2471 -- 'NCALC' event , not handled here, in ROLLUP phase
2472
2473 l_mgr_role_flag jtf_rs_roles_b.manager_flag%TYPE;
2474 l_mem_role_flag jtf_rs_roles_b.member_flag%TYPE;
2475 l_org_id number;
2476
2477 -- cursor to get the old manager_flag
2478 CURSOR role_flag_cur (p_role_id NUMBER)IS
2479 SELECT member_flag, manager_flag
2480 FROM jtf_rs_roles_b
2481 WHERE role_id = p_role_id
2482 AND role_type_code = 'SALES_COMP';
2483
2484 CURSOR l_log_rep_del_actions_csr IS
2485 SELECT batch.salesrep_id, Log.period_id,
2486 Log.start_date, Log.end_date,
2487 Log.revert_state, Log.comp_group_id,
2488 Log.base_salesrep_id, Log.base_comp_group_id,
2489 Log.role_id, Log.action
2490 FROM cn_process_batches_all batch,
2491 cn_notify_log_all Log
2492 WHERE batch.physical_batch_id = p_physical_batch_id
2493 AND log.org_id = batch.org_id
2494 AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
2495 AND Log.salesrep_id = batch.salesrep_id
2496 AND Log.status = 'INCOMPLETE'
2497 AND Log.action IS NOT NULL
2498 AND Log.action IN ('DELETE_TEAM_MEMB', 'DELETE_SOURCE', 'DELETE_DEST_WITHIN', 'DELETE_DEST_XROLL', 'DELETE_DEST')
2499 ORDER BY batch.salesrep_id, Log.period_id, Log.revert_sequence, Log.notify_log_id;
2500
2501 CURSOR l_log_rep_periods_csr IS
2502 SELECT batch.salesrep_id, Log.period_id,
2503 Log.start_date, Log.end_date,
2504 Log.revert_state, Log.comp_group_id,
2505 Log.base_salesrep_id, Log.base_comp_group_id,
2506 Log.quota_id
2507 FROM cn_process_batches_all batch,
2508 cn_notify_log_all Log
2509 WHERE batch.physical_batch_id = p_physical_batch_id
2510 AND log.org_id = batch.org_id
2511 AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
2512 AND ( Log.salesrep_id = batch.salesrep_id
2513 OR Log.salesrep_id = -1000 )
2514 AND Log.status = 'INCOMPLETE'
2515 AND Log.revert_state <> 'NCALC'
2516 ORDER BY batch.salesrep_id, Log.period_id, Log.revert_sequence, Log.notify_log_id;
2517
2518 CURSOR l_pop_lines_csr (l_salesrep_id NUMBER,
2519 l_period_id NUMBER,
2520 l_start_date DATE,
2521 l_end_date DATE,
2522 l_quota_id NUMBER )IS
2523 SELECT line.commission_line_id,
2524 line.posting_status,
2525 line.created_during
2526 FROM cn_commission_lines_all line
2530 AND line.processed_date BETWEEN l_start_date AND l_end_date
2527 WHERE line.credited_salesrep_id = l_salesrep_id
2528 AND line.processed_period_id = l_period_id
2529 AND line.quota_id = l_quota_id
2531 AND line.trx_type NOT IN ('FORECAST', 'BONUS')
2532 AND line.status NOT IN ( 'XPOP', 'OBSOLETE' );
2533
2534 CURSOR l_roll_lines_csr (l_salesrep_id NUMBER,
2535 l_period_id NUMBER,
2536 l_start_date DATE,
2537 l_end_date DATE ) IS
2538 SELECT line.commission_line_id,
2539 line.posting_status,
2540 line.created_during
2541 FROM cn_commission_lines line
2542 WHERE line.credited_salesrep_id = l_salesrep_id
2543 AND line.processed_period_id = l_period_id
2544 AND line.processed_date BETWEEN l_start_date AND l_end_date
2545 AND line.status <> 'OBSOLETE'
2546 AND line.trx_type NOT IN ('FORECAST', 'BONUS')
2547 AND line.org_id = l_org_id;
2548
2549 CURSOR l_cls_posted_lines_csr ( l_salesrep_id NUMBER,
2550 l_period_id NUMBER,
2551 l_start_date DATE,
2552 l_end_date DATE ) IS
2553 SELECT line.commission_line_id
2554 FROM cn_commission_lines_all line
2555 WHERE line.commission_header_id
2556 IN ( SELECT header.commission_header_id
2557 FROM cn_commission_headers_all header
2558 WHERE header.direct_salesrep_id = l_salesrep_id
2559 AND header.processed_period_id = l_period_id
2560 AND header.processed_date BETWEEN l_start_date AND l_end_date
2561 AND header.status = 'ROLL'
2562 AND header.org_id = l_org_id
2563 AND header.trx_type NOT IN ('FORECAST', 'BONUS') )
2564 AND line.posting_status = 'POSTED'
2565 AND line.status = 'CALC';
2566
2567 CURSOR l_itd_grp_trx_csr ( l_salesrep_id NUMBER,
2568 l_period_id NUMBER,
2569 l_quota_id NUMBER,
2570 l_revert_state VARCHAR2 ) IS
2571 SELECT line.commission_line_id, line.posting_status
2572 FROM cn_commission_lines_all line
2573 WHERE line.credited_salesrep_id = l_salesrep_id
2574 AND line.processed_period_id = l_period_id
2575 AND line.trx_type IN ('ITD', 'GRP')
2576 AND line.org_id = l_org_id
2577 AND ((l_revert_state = 'POP' AND line.quota_id = l_quota_id) OR
2578 (l_revert_state = 'CALC' AND (line.quota_id = l_quota_id or l_quota_id is null)) OR
2579 (l_revert_state not in ('POP', 'CALC')));
2580
2581 CURSOR revert_lines_delete_source(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2582 p_start_date DATE, p_end_date DATE, p_role_id NUMBER, p_base_comp_group_id NUMBER,
2583 p_base_salesrep_id NUMBER)
2584 IS
2585 SELECT commission_line_id
2586 FROM cn_commission_lines_all cl
2587 WHERE cl.credited_salesrep_id = p_salesrep_id
2588 AND cl.credited_comp_group_id = p_comp_group_id
2589 AND cl.processed_period_id = p_period_id
2590 and cl.status = 'CALC'
2591 and cl.posting_status = 'POSTED'
2592 and cl.org_id = l_org_id
2593 AND cl.processed_date BETWEEN p_start_date AND p_end_date
2594 AND ((p_role_id IS NOT NULL AND cl.role_id = p_role_id ) OR p_role_id IS NULL)
2595 AND exists ( SELECT 1
2596 FROM cn_commission_headers_all ch
2597 WHERE ch.commission_header_id = cl.commission_header_id
2598 AND ch.comp_group_id = p_base_comp_group_id
2599 AND ( p_base_salesrep_id IS NULL OR ch.direct_salesrep_id = p_base_salesrep_id));
2600
2601 CURSOR revert_lines_del_within(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2602 p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2603 IS
2604 SELECT commission_line_id
2605 FROM cn_commission_lines_all cl
2606 WHERE cl.posting_status = 'POSTED'
2607 and cl.status = 'CALC'
2608 AND cl.credited_comp_group_id = p_comp_group_id
2609 AND cl.processed_period_id = p_period_id
2610 AND cl.processed_date BETWEEN p_start_date AND p_end_date
2611 AND cl.org_id = l_org_id
2612 AND ((p_role_id IS NOT NULL AND cl.role_id = p_role_id
2613 AND ((l_mgr_role_flag = 'N' and l_mem_role_flag = 'N' and cl.credited_salesrep_id = p_salesrep_id)
2614 OR (l_mgr_role_flag = 'N' and l_mem_role_flag = 'Y' and cl.credited_salesrep_id = p_salesrep_id and cl.direct_salesrep_id <> p_salesrep_id))
2615 ) OR (p_role_id IS NULL AND cl.credited_salesrep_id = p_salesrep_id))
2616 AND exists
2617 ( SELECT 1
2618 FROM cn_commission_headers_all ch
2619 WHERE ch.commission_header_id = cl.commission_header_id
2620 AND ch.comp_group_id = p_comp_group_id );
2621
2622
2623
2624 CURSOR revert_lines_delete_dest(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2625 p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2626 IS
2627 SELECT commission_line_id
2628 FROM cn_commission_lines_all cl
2629 WHERE cl.credited_salesrep_id = p_salesrep_id
2630 and cl.posting_status = 'POSTED'
2631 and cl.status = 'CALC'
2632 AND cl.credited_comp_group_id = p_comp_group_id
2633 AND cl.processed_period_id = p_period_id
2634 AND cl.processed_date BETWEEN p_start_date AND p_end_date
2635 AND cl.org_id = l_org_id
2636 AND ( (p_role_id IS NOT NULL AND cl.role_id = p_role_id ) OR p_role_id IS NULL );
2637
2638 CURSOR revert_lines_delete_dest2(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2639 p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2640 IS
2641 SELECT commission_line_id
2642 FROM cn_commission_lines_all cl
2643 WHERE cl.credited_salesrep_id = p_salesrep_id
2644 and cl.posting_status = 'POSTED'
2645 and cl.status = 'CALC'
2646 AND cl.credited_comp_group_id = p_comp_group_id
2647 AND cl.processed_period_id = p_period_id
2648 AND cl.processed_date BETWEEN p_start_date AND p_end_date
2649 AND cl.org_id = l_org_id
2650 AND ( (p_role_id IS NOT NULL AND cl.role_id = p_role_id ) OR p_role_id IS NULL )
2651 AND NOT exists (SELECT 1
2652 FROM cn_srp_comp_groups_v
2653 WHERE comp_group_id = cl.credited_comp_group_id
2654 AND salesrep_id = cl.credited_salesrep_id
2655 AND role_id = cl.role_id
2656 AND org_id = cl.org_id
2657 AND cl.processed_date BETWEEN start_date_active AND Nvl(end_date_active, cl.processed_date));
2658
2659 CURSOR revert_lines_delete_dest3(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2660 p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2661 IS
2662 SELECT commission_line_id
2663 FROM cn_commission_lines_all cl
2664 WHERE cl.credited_salesrep_id = p_salesrep_id
2665 and cl.posting_status = 'POSTED'
2666 and cl.status = 'CALC'
2667 and cl.org_id = l_org_id
2668 AND cl.credited_comp_group_id = p_comp_group_id
2669 AND cl.direct_salesrep_id <> p_salesrep_id
2670 AND cl.processed_period_id = p_period_id
2671 AND cl.processed_date BETWEEN p_start_date AND p_end_date
2672 and not exists (select 1
2673 from cn_srp_comp_groups_v
2674 where comp_group_id = p_comp_group_id
2675 and salesrep_id = cl.credited_salesrep_id
2676 and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date)
2677 and org_id = cl.org_id
2678 and manager_flag = 'Y')
2679 and exists( select 1
2680 from cn_srp_comp_groups_v
2681 where comp_group_id = p_comp_group_id
2682 and salesrep_id = cl.direct_salesrep_id
2683 and org_id = cl.org_id
2684 and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date));
2685
2686 CURSOR revert_lines_delete_team_memb(p_salesrep_id NUMBER, p_period_id NUMBER,
2687 p_start_date DATE, p_end_date DATE) IS
2688 SELECT commission_line_id
2689 from cn_commission_lines_all
2690 where posting_status = 'POSTED'
2691 and (commission_header_id, credited_salesrep_id) in
2692 (select commission_header_id, credited_salesrep_id
2693 FROM cn_commission_lines_all cl
2694 WHERE cl.credited_salesrep_id = p_salesrep_id
2695 AND cl.processed_period_id = p_period_id
2696 AND cl.created_during = 'TROLL'
2697 AND cl.org_id = l_org_id
2698 AND cl.processed_date BETWEEN p_start_date AND p_end_date);
2699
2700 BEGIN
2701 cn_message_pkg.debug('Reversing transactions in physical batch (ID=' || p_physical_batch_id||')');
2702
2703 select org_id into l_org_id
2704 from cn_process_batches_all
2705 where physical_batch_id = p_physical_batch_id
2706 and rownum = 1;
2707
2708 -- mark proces_all_flag = 'Y', means that need to recalculate every transaction
2709 -- in calc phase since some trx has been reverted
2710
2711 -- since 'POP' event only affect a particular quota, we shouldn't mark the whole period
2712 -- will be considered in calculate_batch
2713
2714 UPDATE cn_srp_intel_periods_all
2715 SET process_all_flag = 'Y'
2716 WHERE org_id = l_org_id
2717 AND ( salesrep_id, period_id ) IN
2718 ( SELECT DISTINCT batch.salesrep_id, Log.period_id
2719 FROM cn_process_batches_all batch,
2720 cn_notify_log_all Log
2721 WHERE batch.physical_batch_id = p_physical_batch_id
2722 AND log.org_id = batch.org_id
2723 AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
2724 AND ( Log.salesrep_id = batch.salesrep_id
2725 OR Log.salesrep_id = -1000 )
2726 AND Log.status = 'INCOMPLETE'
2727 AND ( Log.revert_state NOT IN ( 'NCALC', 'CALC', 'POP')
2728 OR ( Log.action IS NOT NULL
2729 AND Log.action IN ('DELETE_TEAM_MEMB', 'DELETE_SOURCE', 'DELETE_DEST_WITHIN', 'DELETE_DEST_XROLL', 'DELETE_DEST') )
2730 )
2731 );
2732
2733 -- handle DELETE actions first
2734 FOR l_log IN l_log_rep_del_actions_csr LOOP
2735 IF l_log.action = 'DELETE_TEAM_MEMB' THEN
2736 FOR line IN revert_lines_delete_team_memb(l_log.salesrep_id,l_log.period_id,l_log.start_date,
2737 l_log.end_date) LOOP
2738 revert_posting_line(line.commission_line_id);
2739 END LOOP;
2740
2741
2742 DELETE cn_commission_lines_all
2743 WHERE (commission_header_id, credited_salesrep_id) in
2744 (select commission_header_id, credited_salesrep_id
2745 from cn_commission_lines_all cl
2746 where cl.credited_salesrep_id = l_log.salesrep_id
2747 AND cl.processed_period_id = l_log.period_id
2748 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2749 AND cl.created_during = 'TROLL'
2750 AND cl.org_id = l_org_id);
2751
2752 ELSIF l_log.action = 'DELETE_SOURCE' THEN
2753 FOR line IN revert_lines_delete_source(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
2754 l_log.end_date,l_log.role_id,l_log.base_comp_group_id, l_log.base_salesrep_id)
2755 LOOP
2756 revert_posting_line(line.commission_line_id);
2757 END LOOP;
2758
2759 DELETE cn_commission_lines_all cl
2760 WHERE cl.credited_salesrep_id = l_log.salesrep_id
2761 AND cl.credited_comp_group_id = l_log.comp_group_id
2762 AND cl.processed_period_id = l_log.period_id
2763 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2764 AND cl.org_id = l_org_id
2765 AND ( (l_log.role_id IS NOT NULL AND cl.role_id = l_log.role_id )
2766 OR l_log.role_id IS NULL )
2767 AND exists
2768 (SELECT 1
2769 FROM cn_commission_headers_all ch
2770 WHERE ch.commission_header_id = cl.commission_header_id
2771 AND ch.comp_group_id = l_log.base_comp_group_id
2772 AND ( l_log.base_salesrep_id IS NULL
2773 OR ch.direct_salesrep_id = l_log.base_salesrep_id)
2774 );
2775 ELSIF l_log.action = 'DELETE_DEST_WITHIN' THEN
2776 l_mem_role_flag := 'N';
2777 l_mgr_role_flag := 'N';
2778
2779 IF l_log.role_id IS NOT NULL THEN
2780 open role_flag_cur(l_log.role_id);
2781 fetch role_flag_cur into l_mem_role_flag, l_mgr_role_flag;
2782 close role_flag_cur;
2783 END IF;
2784
2785 FOR line IN revert_lines_del_within(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
2786 l_log.end_date,l_log.role_id)
2787 LOOP
2788 revert_posting_line(line.commission_line_id);
2789 END LOOP;
2790
2791 DELETE cn_commission_lines_all cl
2792 WHERE cl.credited_comp_group_id = l_log.comp_group_id
2793 AND cl.processed_period_id = l_log.period_id
2794 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2795 AND cl.org_id = l_org_id
2796 AND ((l_log.role_id IS NOT NULL AND
2797 cl.role_id = l_log.role_id AND
2798 ((l_mgr_role_flag = 'N' and l_mem_role_flag = 'N' and cl.credited_salesrep_id = l_log.salesrep_id) OR
2799 (l_mgr_role_flag = 'N' and l_mem_role_flag = 'Y' and cl.credited_salesrep_id = l_log.salesrep_id and cl.direct_salesrep_id <> l_log.salesrep_id))
2800 )OR (l_log.role_id IS NULL and cl.credited_salesrep_id = l_log.salesrep_id))
2801 AND exists
2802 ( SELECT 1
2803 FROM cn_commission_headers_all ch
2804 WHERE ch.commission_header_id = cl.commission_header_id
2805 AND ch.comp_group_id = l_log.comp_group_id );
2806
2807 ELSIF l_log.action = 'DELETE_DEST_XROLL' THEN
2808 FOR line IN revert_lines_delete_dest(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
2809 l_log.end_date,l_log.role_id)
2810 LOOP
2811 revert_posting_line(line.commission_line_id);
2812 END LOOP;
2813
2814 DELETE cn_commission_lines_all cl
2815 WHERE cl.credited_salesrep_id = l_log.salesrep_id
2816 AND cl.credited_comp_group_id = l_log.comp_group_id
2817 AND cl.processed_period_id = l_log.period_id
2818 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2819 AND cl.org_id = l_org_id
2820 AND ( (l_log.role_id IS NOT NULL AND cl.role_id = l_log.role_id )
2821 OR l_log.role_id IS NULL );
2822
2823 UPDATE cn_commission_headers_all ch
2824 SET status = 'XROLL',
2825 last_update_date = sysdate,
2826 last_updated_by = g_last_updated_by,
2827 last_update_login = g_last_update_login
2828 WHERE ch.direct_salesrep_id = l_log.salesrep_id
2829 AND ch.comp_group_id = l_log.comp_group_id
2830 AND ch.processed_period_id = l_log.period_id
2831 AND Nvl(ch.parent_header_id, -1) = -1
2832 AND ch.processed_date BETWEEN l_log.start_date AND l_log.end_date
2833 AND ch.org_id = l_org_id;
2834
2835 ELSIF l_log.action = 'DELETE_DEST' THEN
2836 FOR line IN revert_lines_delete_dest2(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
2837 l_log.end_date,l_log.role_id)
2838 LOOP
2839 revert_posting_line(line.commission_line_id);
2840 END LOOP;
2841
2842 DELETE cn_commission_lines_all cl
2843 WHERE cl.credited_salesrep_id = l_log.salesrep_id
2844 AND cl.credited_comp_group_id = l_log.comp_group_id
2845 AND cl.processed_period_id = l_log.period_id
2846 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2847 AND cl.org_id = l_org_id
2851 WHERE comp_group_id = l_log.comp_group_id
2848 AND ((l_log.role_id IS NOT NULL AND cl.role_id = l_log.role_id ) OR l_log.role_id IS NULL )
2849 AND NOT exists (SELECT 1
2850 FROM cn_srp_comp_groups_v
2852 AND role_id = cl.role_id
2853 AND salesrep_id = cl.credited_salesrep_id
2854 AND org_id = cl.org_id
2855 AND cl.processed_date BETWEEN start_date_active AND Nvl(end_date_active, cl.processed_date));
2856
2857
2858 UPDATE cn_commission_lines_all cl
2859 SET created_during = 'ROLL'
2860 WHERE cl.credited_salesrep_id = l_log.salesrep_id
2861 AND cl.credited_comp_group_id = l_log.comp_group_id
2862 AND cl.processed_period_id = l_log.period_id
2863 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2864 AND cl.created_during = 'POP'
2865 AND org_id = l_org_id
2866 AND NOT exists (SELECT 1
2867 FROM cn_commission_lines_all
2868 WHERE commission_header_id = cl.commission_header_id
2869 AND credited_salesrep_id = cl.credited_salesrep_id
2870 AND credited_comp_group_id = l_log.comp_group_id
2871 AND org_id = cl.org_id
2872 AND created_during = 'ROLL')
2873 AND cl.commission_line_id IN (SELECT MIN(commission_line_id)
2874 FROM cn_commission_lines_all
2875 WHERE credited_salesrep_id = l_log.salesrep_id
2876 AND credited_comp_group_id = l_log.comp_group_id
2877 AND processed_period_id = l_log.period_id
2878 AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2879 AND created_during = 'POP'
2880 AND org_id = l_org_id
2881 GROUP BY commission_header_id);
2882
2883 FOR line IN revert_lines_delete_dest3(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
2884 l_log.end_date,l_log.role_id)
2885 LOOP
2886 revert_posting_line(line.commission_line_id);
2887 END LOOP;
2888
2889 DELETE cn_commission_lines_all cl
2890 WHERE cl.credited_salesrep_id = l_log.salesrep_id
2891 AND cl.credited_comp_group_id = l_log.comp_group_id
2892 AND cl.direct_salesrep_id <> l_log.salesrep_id
2893 AND cl.processed_period_id = l_log.period_id
2894 AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2895 AND cl.org_id = l_org_id
2896 and not exists (select 1
2897 from cn_srp_comp_groups_v
2898 where comp_group_id = l_log.comp_group_id
2899 and salesrep_id = cl.credited_salesrep_id
2900 and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date)
2901 and manager_flag = 'Y'
2902 and org_id = cl.org_id)
2903 and exists( select 1
2904 from cn_srp_comp_groups_v
2905 where comp_group_id = l_log.comp_group_id
2906 and salesrep_id = cl.direct_salesrep_id
2907 and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date)
2908 and org_id = cl.org_id);
2909 END IF;
2910 END LOOP;
2911
2912 -- handle reverts here.
2913 FOR l_log IN l_log_rep_periods_csr LOOP
2914 IF l_log.revert_state = 'POP' THEN
2915 -- 1). delete 'UNPOSTED' and created_during 'CALC'
2916 DELETE cn_commission_lines_all line
2917 WHERE line.credited_salesrep_id = l_log.salesrep_id
2918 AND line.processed_period_id = l_log.period_id
2919 AND line.quota_id = l_log.quota_id
2920 AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
2921 AND line.trx_type NOT IN ('FORECAST', 'BONUS')
2922 AND line.status <> 'OBSOLETE'
2923 AND ( line.posting_status IS NULL OR line.posting_status <> 'POSTED' )
2924 AND line.created_during = 'CALC';
2925
2926 -- 2).take care the following case
2927 -- posted either created_during 'CALC' or not
2928 -- or unposted and not creadted_during 'CALC'
2929 -- ignore 'XPOP' trx here because after repopulating its factor
2930 --- it is still 'XPOP'.
2931 FOR l_line IN l_pop_lines_csr( l_log.salesrep_id, l_log.period_id,
2932 l_log.start_date, l_log.end_date,
2933 l_log.quota_id ) LOOP
2934 IF l_line.posting_status = 'POSTED' THEN
2935 revert_posting_line ( l_line.commission_line_id);
2936 END IF;
2937 END LOOP;
2938
2939 DELETE FROM cn_commission_lines_all
2940 WHERE credited_salesrep_id = l_log.salesrep_id
2941 AND processed_period_id = l_log.period_id
2942 AND quota_id = l_log.quota_id
2943 AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2944 AND trx_type NOT IN ('FORECAST', 'BONUS')
2945 AND status <> 'OBSOLETE'
2946 AND created_during = 'CALC';
2947
2948 UPDATE cn_commission_lines_all
2949 SET status = 'POP', -- and more
2950 posting_status = 'UNPOSTED',
2951 event_factor = NULL,
2952 payment_factor = NULL,
2953 quota_factor = NULL,
2954 commission_amount = NULL,
2955 rate_tier_id = NULL,
2956 commission_rate = NULL,
2957 tier_split = NULL,
2958 input_achieved = NULL,
2959 output_achieved = NULL,
2960 perf_achieved = NULL,
2961 error_reason = NULL,
2962 srp_payee_assign_id = NULL,
2963 threshold_check_status = NULL,
2964 last_update_date = sysdate,
2965 last_updated_by = g_last_updated_by,
2966 last_update_login = g_last_update_login
2967 WHERE credited_salesrep_id = l_log.salesrep_id
2968 AND processed_period_id = l_log.period_id
2969 AND quota_id = l_log.quota_id
2970 AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2971 AND trx_type NOT IN ('FORECAST', 'BONUS')
2972 AND status NOT IN ('XPOP', 'OBSOLETE');
2973
2974 ELSIF l_log.revert_state = 'ROLL' THEN
2975 -- 1). delete trx created during 'POP', 'CALC' and 'UNPOSTED'
2976 DELETE cn_commission_lines_all line
2977 WHERE line.credited_salesrep_id = l_log.salesrep_id
2978 AND line.processed_period_id = l_log.period_id
2979 AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
2980 AND line.trx_type NOT IN ('FORECAST', 'BONUS')
2981 AND line.status <> 'OBSOLETE'
2982 AND ( line.posting_status IS NULL OR line.posting_status <> 'POSTED')
2983 AND line.created_during IN ('POP','CALC')
2984 AND line.org_id = l_org_id;
2985
2986 -- 2). take care the following case
2987 -- posted either created_during 'CALC', 'POP' or not
2988 -- or unposted and not creadted_during 'CALC', 'POP'
2989 FOR l_line IN l_roll_lines_csr ( l_log.salesrep_id, l_log.period_id,
2990 l_log.start_date, l_log.end_date )
2991 LOOP
2992 IF l_line.posting_status = 'POSTED' THEN
2993 revert_posting_line ( l_line.commission_line_id);
2994 END IF;
2995 END LOOP;
2996
2997 DELETE FROM cn_commission_lines_all
2998 WHERE credited_salesrep_id = l_log.salesrep_id
2999 AND processed_period_id = l_log.period_id
3000 AND processed_date BETWEEN l_log.start_date AND l_log.end_date
3001 AND trx_type NOT IN ('FORECAST', 'BONUS')
3002 AND status <> 'OBSOLETE'
3003 AND created_during in ('POP', 'CALC')
3004 AND org_id = l_org_id;
3005
3006 UPDATE cn_commission_lines_all
3007 SET status = 'ROLL', -- and more
3008 posting_status = 'UNPOSTED',
3009 event_factor = NULL,
3010 payment_factor = NULL,
3011 quota_factor = NULL,
3012 commission_amount = NULL,
3013 rate_tier_id = NULL,
3014 commission_rate = NULL,
3015 tier_split = NULL,
3016 input_achieved = NULL,
3017 output_achieved = NULL,
3018 perf_achieved = NULL,
3019 error_reason = NULL,
3020 srp_payee_assign_id = NULL,
3021 threshold_check_status = NULL,
3022 srp_plan_assign_id = NULL,
3023 quota_id = NULL,
3024 quota_rule_id = NULL,
3025 last_update_date = sysdate,
3026 last_updated_by = g_last_updated_by,
3027 last_update_login = g_last_update_login
3028 WHERE credited_salesrep_id = l_log.salesrep_id
3029 AND processed_period_id = l_log.period_id
3030 AND processed_date BETWEEN l_log.start_date AND l_log.end_date
3031 AND trx_type NOT IN ('FORECAST', 'BONUS')
3032 AND status <> 'OBSOLETE'
3033 AND org_id = l_org_id;
3034
3035 ELSIF l_log.revert_state = 'CLS' THEN
3036 -- 1). take care the following case
3037 -- posted either created_during 'CALC', 'POP' , 'ROLL'
3038 FOR l_line IN l_cls_posted_lines_csr ( l_log.salesrep_id, l_log.period_id,
3039 l_log.start_date, l_log.end_date ) LOOP
3040 revert_posting_line ( l_line.commission_line_id);
3041 END LOOP;
3042
3043 -- 2). delete trx created during 'ROLL' 'POP', 'CALC',
3044 -- basically everything from lines table
3045 DELETE cn_commission_lines_all line
3046 WHERE line.org_id = l_org_id
3047 AND line.commission_header_id
3048 IN ( SELECT header.commission_header_id
3049 FROM cn_commission_headers header
3050 WHERE header.direct_salesrep_id = l_log.salesrep_id
3051 AND header.processed_period_id = l_log.period_id
3052 AND header.processed_date BETWEEN l_log.start_date AND l_log.end_date
3053 AND header.status = 'ROLL'
3054 AND header.trx_type NOT IN ('FORECAST', 'BONUS')
3055 AND header.org_id = l_org_id );
3056
3057 -- 3). update header trx status to be 'CLS' ('CLS_SUM' if rolling up summarized trxs)
3058 UPDATE cn_commission_headers_all
3059 SET status = decode(parent_header_id, -1, 'CLS_SUM', 'CLS'),
3060 last_update_date = sysdate,
3061 last_updated_by = g_last_updated_by,
3062 last_update_login = g_last_update_login
3063 WHERE direct_salesrep_id = l_log.salesrep_id
3064 AND processed_period_id = l_log.period_id
3065 AND processed_date BETWEEN l_log.start_date AND l_log.end_date
3066 AND status <> 'OBSOLETE'
3067 AND status = 'ROLL'
3068 AND trx_type NOT IN ('FORECAST', 'BONUS')
3069 AND org_id = l_org_id;
3070 END IF;
3071
3072 IF l_log.revert_state in ('POP', 'ROLL', 'CLS', 'COL', 'CALC') THEN
3073 -- 1). delete 'ITD','GRP' trx created in commission_lines
3074 FOR l_itd_grp_trx IN l_itd_grp_trx_csr ( l_log.salesrep_id,
3075 l_log.period_id,
3076 l_log.quota_id,
3077 l_log.revert_state )
3078 LOOP
3079 IF l_itd_grp_trx.posting_status = 'POSTED' THEN
3080 revert_posting_line( l_itd_grp_trx.commission_line_id);
3081 END IF;
3082
3083 END LOOP;
3084
3085 DELETE FROM cn_commission_lines_all line
3086 WHERE line.credited_salesrep_id =l_log.salesrep_id
3087 AND line.processed_period_id = l_log.period_id
3088 AND line.trx_type IN ('ITD', 'GRP')
3089 AND ((l_log.revert_state = 'POP' AND line.quota_id = l_log.quota_id) OR
3090 (l_log.revert_state = 'CALC' AND (line.quota_id = l_log.quota_id or l_log.quota_id is null)) OR
3094
3091 (l_log.revert_state not in ('POP', 'CALC')))
3092 AND line.org_id = l_org_id;
3093
3095 -- 2). delete 'GRP' trx created in commission_headers
3096 -- need to delete 'GRP' trxs in commission_header since its counterpart in line has been deleted
3097 DELETE cn_commission_headers_all header
3098 WHERE header.direct_salesrep_id = l_log.salesrep_id
3099 --AND header.processed_date BETWEEN l_log.start_date AND l_log.end_date
3100 AND header.processed_period_id = l_log.period_id
3101 AND header.trx_type IN ('ITD', 'GRP')
3102 AND ((l_log.revert_state = 'POP' AND header.quota_id = l_log.quota_id) OR
3103 (l_log.revert_state = 'CALC' AND (header.quota_id = l_log.quota_id or l_log.quota_id is null)) OR
3104 (l_log.revert_state not in ('POP', 'CALC')))
3105 AND header.org_id = l_org_id;
3106
3107 END IF;
3108
3109 END LOOP;
3110 EXCEPTION
3111 WHEN OTHERS THEN
3112 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3113 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3114 'cn.plsql.cn_formula_common_pkg.revert_batch_intel_comm.exception',
3115 sqlerrm);
3116 end if;
3117 cn_message_pkg.debug('Exception occurs in reversing transactions: ');
3118 cn_message_pkg.debug(sqlerrm );
3119 RAISE;
3120 END revert_batch_intel_comm;
3121
3122 -- do non intelligent revert. revert every system generated trxs
3123 -- this procedure will handle both 'FORECAST' and 'COMMISSION' type calculation bonus trx type is handled in different procedure
3124 PROCEDURE Revert_Batch_nonintel(p_batch_id cn_process_batches.physical_batch_id%TYPE,
3125 p_calc_type cn_calc_submission_batches.calc_type%TYPE) IS
3126 CURSOR l_post_lines_csr IS
3127 SELECT line.commission_line_id
3128 FROM cn_commission_lines_all line,
3129 cn_process_batches_all batch
3130 WHERE batch.physical_batch_id = p_batch_id
3131 AND line.org_id = batch.org_id
3132 AND line.credited_salesrep_id = batch.salesrep_id
3133 AND line.status = 'CALC'
3134 AND line.posting_status = 'POSTED'
3135 AND line.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
3136 AND line.processed_date BETWEEN batch.start_date AND batch.end_date
3137 AND ( (p_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS', 'FORECAST' ) )
3138 OR (p_calc_type ='FORECAST' AND line.trx_type = 'FORECAST') );
3139
3140 CURSOR check_unique_tuple IS
3141 SELECT DISTINCT period_id, end_period_id, start_date, end_date
3142 FROM cn_process_batches_all
3143 WHERE physical_batch_id = p_batch_id;
3144
3145 l_period_id NUMBER;
3146 l_end_period_id NUMBER;
3147 l_start_date DATE;
3148 l_end_date DATE;
3149
3150 l_unique_flag VARCHAR2(1) := 'N';
3151 l_org_id NUMBER;
3152
3153 l_user_id NUMBER(15) := fnd_global.user_id ;
3154 l_resp_id NUMBER(15) := fnd_global.resp_id ;
3155 l_login_id NUMBER(15) := fnd_global.login_id ;
3156 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
3157 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
3158 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id ;
3159
3160 BEGIN
3161 cn_message_pkg.debug('Reversing transactions in physical batch (ID='||p_batch_id||')');
3162
3163 select org_id into l_org_id
3164 from cn_process_batches_all
3165 where physical_batch_id = p_batch_id
3166 and rownum = 1;
3167
3168 -- mark process_all_flag = 'Y'
3169 update cn_srp_intel_periods_all a
3170 set a.process_all_flag = 'Y'
3171 where a.org_id = l_org_id
3172 and a.salesrep_id in (select salesrep_id from cn_process_batches_all
3173 where physical_batch_id = p_batch_id)
3174 and a.period_id >= (select min(period_id) from cn_process_batches_all
3175 where physical_batch_id = p_batch_id
3176 and salesrep_id = a.salesrep_id)
3177 and a.period_id <= (select max(end_period_id) from cn_process_batches_all
3178 where physical_batch_id = p_batch_id
3179 and salesrep_id = a.salesrep_id);
3180
3181 -- create reversal of posting line
3182 if (fnd_profile.value('CN_PAY_BY_TRANSACTION') = 'Y') then
3183 FOR l_line IN l_post_lines_csr LOOP
3184 revert_posting_line( l_line.commission_line_id);
3185 END LOOP;
3186 end if;
3187
3188
3189 commit;
3190
3191 -- then delete all possible commisson_lines
3192 OPEN check_unique_tuple;
3193 FETCH check_unique_tuple INTO l_period_id, l_end_period_id, l_start_date, l_end_date;
3194 FETCH check_unique_tuple INTO l_period_id, l_end_period_id, l_start_date, l_end_date;
3195 IF (check_unique_tuple%notfound) THEN
3196 CLOSE check_unique_tuple;
3197 l_unique_flag := 'Y';
3198
3199 loop
3200 DELETE /*+ index(line cn_commission_lines_n7) */ cn_commission_lines_all line
3201 WHERE line.credited_salesrep_id IN (SELECT salesrep_id
3202 FROM cn_process_batches_all
3203 WHERE physical_batch_id = p_batch_id)
3204 AND line.processed_period_id BETWEEN l_period_id AND l_end_period_id
3205 AND line.processed_date BETWEEN l_start_date AND l_end_date
3206 AND line.status <> 'OBSOLETE'
3207 AND line.org_id = l_org_id
3208 AND ((p_calc_type ='FORECAST' AND line.trx_type = 'FORECAST')
3209 OR (p_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS','FORECAST')))
3210 and rownum < 10000;
3211
3212 exit when SQL%rowcount = 0;
3213 commit;
3214 end loop;
3215 ELSE
3219 WHERE del_line.commission_line_id IN
3216 CLOSE check_unique_tuple;
3217 loop
3218 DELETE cn_commission_lines_all del_line
3220 (SELECT line.commission_line_id
3221 FROM cn_commission_lines_all line,
3222 cn_process_batches_all batch
3223 WHERE batch.physical_batch_id = p_batch_id
3224 AND line.org_id = batch.org_id
3225 AND line.credited_salesrep_id = batch.salesrep_id
3226 AND line.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
3227 AND line.processed_date BETWEEN batch.start_date AND batch.end_date
3228 AND line.status <> 'OBSOLETE'
3229 AND ((p_calc_type ='FORECAST' AND line.trx_type = 'FORECAST')
3230 OR (p_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS','FORECAST')) ) )
3231 and rownum < 10000;
3232 exit when SQL%rowcount = 0;
3233 commit;
3234 end loop;
3235
3236 END IF;
3237
3238 -- update commission header line to be reclassified
3239 -- also all other related fields like ????, revenue_class_id, ....
3240 IF p_calc_type = 'COMMISSION' THEN
3241 -- need to delete 'ITD','GRP' trxs in commission_header since its counterpart in line has been deleted
3242 -- bonus trx are all non accumulative, so 'ITD', 'GRP' doesn't apply.
3243 IF (l_unique_flag = 'Y') THEN
3244 delete cn_commission_headers_all ch
3245 where ch.direct_salesrep_id in (select salesrep_id
3246 from cn_process_batches_all
3247 where physical_batch_id = p_batch_id)
3248 and ch.processed_date between l_start_date and l_end_date
3249 AND (ch.trx_type IN ('GRP', 'ITD') OR ch.parent_header_id = -1)
3250 and ch.org_id = l_org_id;
3251 ELSE
3252 DELETE cn_commission_headers_all head
3253 WHERE head.commission_header_id IN
3254 ( SELECT dh.commission_header_id
3255 FROM cn_commission_headers_all dh,
3256 cn_process_batches_all batch
3257 WHERE batch.physical_batch_id = p_batch_id
3258 AND dh.org_id = batch.org_id
3259 AND batch.salesrep_id = dh.direct_salesrep_id
3260 AND dh.processed_date BETWEEN batch.start_date AND batch.end_date
3261 AND (dh.trx_type IN ('GRP', 'ITD') OR dh.parent_header_id = -1));
3262 END IF;
3263 END IF;
3264
3265 IF (l_unique_flag = 'N') THEN
3266 UPDATE cn_commission_headers_all up_header
3267 SET status = 'COL',
3268 revenue_class_id = decode(substr(pre_processed_code,1,1), 'C', NULL, revenue_class_id),
3269 parent_header_id = NULL,
3270 last_update_date = sysdate,
3271 last_updated_by = g_last_updated_by,
3272 last_update_login = g_last_update_login
3273 WHERE up_header.commission_header_id IN
3274 ( SELECT header.commission_header_id
3275 FROM cn_commission_headers_all header,
3276 cn_process_batches_all batch
3277 WHERE batch.physical_batch_id = p_batch_id
3278 AND batch.salesrep_id = header.direct_salesrep_id
3279 AND header.org_id = batch.org_id
3280 AND header.status <> 'OBSOLETE'
3281 AND header.processed_date BETWEEN batch.start_date AND batch.end_date
3282 AND ((p_calc_type ='FORECAST' AND header.trx_type = 'FORECAST')
3283 OR (p_calc_type = 'COMMISSION' AND header.trx_type NOT IN ('BONUS','FORECAST')) ) );
3284 ELSE
3285 UPDATE cn_commission_headers_all
3286 SET status = 'COL',
3287 revenue_class_id = decode(substr(pre_processed_code,1,1), 'C', NULL, revenue_class_id),
3288 parent_header_id = NULL,
3289 last_update_date = sysdate,
3290 last_updated_by = g_last_updated_by,
3291 last_update_login = g_last_update_login
3292 WHERE org_id = l_org_id
3293 AND direct_salesrep_id IN (SELECT salesrep_id
3294 FROM cn_process_batches_all
3295 WHERE physical_batch_id = p_batch_id)
3296 AND processed_date BETWEEN l_start_date AND l_end_date
3297 AND status <> 'OBSOLETE'
3298 AND ((p_calc_type ='FORECAST' AND trx_type = 'FORECAST')
3299 OR (p_calc_type = 'COMMISSION' AND trx_type NOT IN ('BONUS','FORECAST')));
3300 END IF;
3301 END Revert_Batch_nonintel;
3302
3303 -- do non intelligent revert for bonus calc
3304 PROCEDURE Revert_Batch_nonintel_bonus(p_batch_id cn_process_batches.physical_batch_id%TYPE) IS
3305 l_interval_type_id NUMBER;
3306 l_calc_sub_batch_id NUMBER;
3307 l_counter NUMBER;
3308 l_org_id NUMBER;
3309
3310 CURSOR l_sub_batch_csr IS
3311 SELECT calc_sub_batch_id, interval_type_id, org_id
3312 FROM cn_calc_submission_batches_all
3313 WHERE logical_batch_id IN (SELECT logical_batch_id
3314 FROM cn_process_batches_all
3315 WHERE physical_batch_id = p_batch_id
3316 AND rownum = 1);
3317
3318 CURSOR l_quota_count_csr IS
3319 SELECT COUNT(*)
3320 FROM cn_calc_sub_quotas_all
3321 WHERE calc_sub_batch_id = l_calc_sub_batch_id;
3322
3323 CURSOR l_post_lines IS
3324 select cl.commission_line_id
3325 FROM cn_commission_lines_all cl,
3326 cn_process_batches_all batch
3327 WHERE batch.physical_batch_id = p_batch_id
3328 AND batch.salesrep_id = cl.credited_salesrep_id
3329 and cl.org_id = l_org_id
3330 --and cl.processed_period_id between batch.period_id and batch.end_period_id
3331 and cl.processed_date between batch.start_date and batch.end_date
3332 and cl.status = 'CALC'
3333 and cl.posting_status = 'POSTED'
3334 and cl.trx_type = 'BONUS'
3335 and (exists (select 1 from cn_quotas_all
3336 where quota_id = cl.quota_id
3337 and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
3338 and (l_calc_sub_batch_id = -1000 or
3342 BEGIN
3339 cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3340 where calc_sub_batch_id = l_calc_sub_batch_id));
3341
3343 cn_message_pkg.debug('Reversing transactions in physical batch (ID='||p_batch_id||')');
3344
3345 OPEN l_sub_batch_csr;
3346 FETCH l_sub_batch_csr INTO l_calc_sub_batch_id, l_interval_type_id, l_org_id;
3347 CLOSE l_sub_batch_csr;
3348
3349 OPEN l_quota_count_csr;
3350 FETCH l_quota_count_csr INTO l_counter;
3351 CLOSE l_quota_count_csr;
3352
3353 -- no particular bonus plan elements are specified
3354 IF l_counter = 0 THEN
3355 l_calc_sub_batch_id := -1000;
3356 END IF;
3357
3358 for l_post_line in l_post_lines loop
3359 revert_posting_line(l_post_line.commission_line_id);
3360 end loop;
3361
3362 -- delete header lines
3363 delete from cn_commission_headers_all
3364 where commission_header_id in (
3365 select cl.commission_header_id
3366 from cn_commission_lines_all cl,
3367 cn_process_batches_all batch
3368 where batch.physical_batch_id = p_batch_id
3369 and batch.salesrep_id = cl.credited_salesrep_id
3370 and cl.org_id = l_org_id
3371 --and cl.processed_period_id between batch.period_id and batch.end_period_id
3372 and cl.processed_date between batch.start_date and batch.end_date
3373 and cl.trx_type = 'BONUS'
3374 and (exists (select 1 from cn_quotas_all
3375 where quota_id = cl.quota_id
3376 and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
3377 and (l_calc_sub_batch_id = -1000 or
3378 cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3379 where calc_sub_batch_id = l_calc_sub_batch_id)));
3380
3381 -- delete detail lines
3382 delete from cn_commission_lines_all
3383 where commission_line_id in (
3384 select cl.commission_line_id
3385 from cn_commission_lines_all cl,
3386 cn_process_batches_all batch
3387 where batch.physical_batch_id = p_batch_id
3388 and batch.salesrep_id = cl.credited_salesrep_id
3389 and cl.org_id =l_org_id
3390 --and cl.processed_period_id between batch.period_id and batch.end_period_id
3391 and cl.processed_date between batch.start_date and batch.end_date
3392 and cl.trx_type = 'BONUS'
3393 and (exists (select 1 from cn_quotas_all
3394 where quota_id = cl.quota_id
3395 and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
3396 and (l_calc_sub_batch_id = -1000 or
3397 cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3398 where calc_sub_batch_id = l_calc_sub_batch_id)));
3399 END Revert_Batch_nonintel_bonus;
3400
3401
3402 Procedure revert_batch (p_batch_id cn_process_batches.physical_batch_id%TYPE) IS
3403 l_intel_calc_flag VARCHAR2(30);
3404 l_calc_type VARCHAR2(30);
3405 BEGIN
3406
3407 l_intel_calc_flag := cn_calc_sub_batches_pkg.get_intel_calc_flag(p_batch_id);
3408 l_calc_type := cn_calc_sub_batches_pkg.get_calc_type(p_batch_id);
3409
3410 IF l_calc_type = 'COMMISSION' THEN
3411 IF l_intel_calc_flag = 'Y' THEN
3412 revert_batch_intel_comm(p_batch_id);
3413 ELSE
3414 revert_batch_nonintel(p_batch_id, l_calc_type);
3415 END IF;
3416 ELSIF l_calc_type = 'FORECAST' THEN
3417 IF l_intel_calc_flag = 'Y' THEN
3418 null; --revert_batch_intel_f(p_batch_id);
3419 ELSE
3420 revert_batch_nonintel(p_batch_id, l_calc_type);
3421 END IF;
3422 ELSIF l_calc_type = 'BONUS' THEN
3423 revert_batch_nonintel_bonus(p_batch_id);
3424 END IF;
3425 EXCEPTION
3426 when others then
3427 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3428 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3429 'cn.plsql.cn_formula_common_pkg.revert_batch.exception',
3430 sqlerrm);
3431 end if;
3432
3433 cn_message_pkg.debug('Exception occurs in reversing transactions: ');
3434 cn_message_pkg.rollback_errormsg_commit(sqlerrm);
3435 raise;
3436 END revert_batch;
3437
3438 -- create a new group by trx if not existed update the existing group by trx if already existed
3439 -- if already posted, create a reversal first
3440 PROCEDURE update_trx( p_trx_rec_old IN OUT NOCOPY trx_rec_type,
3441 p_trx_rec_new IN OUT NOCOPY trx_rec_type) IS
3442 l_lupd_chg number := 0;
3443 BEGIN
3444 -- do the rounding first
3445 p_trx_rec_new.commission_amount := Round(Nvl(p_trx_rec_new.commission_amount,0),
3446 g_ext_precision);
3447 p_trx_rec_new.commission_rate := Round( Nvl(p_trx_rec_new.commission_rate,0),
3448 CN_GLOBAL_VAR.g_ext_precision);
3449 p_trx_rec_new.input_achieved := Round( Nvl(p_trx_rec_new.input_achieved,0),
3450 CN_GLOBAL_VAR.g_ext_precision);
3451 p_trx_rec_new.output_achieved := Round( Nvl(p_trx_rec_new.output_achieved,0),
3452 CN_GLOBAL_VAR.g_ext_precision);
3453 p_trx_rec_new.perf_achieved := Round( Nvl(p_trx_rec_new.perf_achieved,0),
3454 CN_GLOBAL_VAR.g_ext_precision);
3455 p_trx_rec_new.rate_tier_id := Nvl( p_trx_rec_new.rate_tier_id,0);
3456 p_trx_rec_new.tier_split := Nvl( p_trx_rec_new.tier_split, 0) ;
3457 IF p_trx_rec_old.commission_amount <> p_trx_rec_new.commission_amount
3458 OR p_trx_rec_old.commission_rate <> p_trx_rec_new.commission_rate
3459 OR p_trx_rec_old.rate_tier_id <> p_trx_rec_new.rate_tier_id
3460 OR p_trx_rec_old.tier_split <> p_trx_rec_new.tier_split
3464 OR nvl(p_trx_rec_old.credit_type_id, -999999) <> p_trx_rec_new.credit_type_id
3461 OR p_trx_rec_old.input_achieved <> p_trx_rec_new.input_achieved
3462 OR p_trx_rec_old.output_achieved <> p_trx_rec_new.output_achieved
3463 OR p_trx_rec_old.perf_achieved <> p_trx_rec_new.perf_achieved
3465 THEN
3466 l_lupd_chg :=1;
3467 END IF;
3468
3469 IF p_trx_rec_old.status = 'CALC' AND p_trx_rec_old.posting_status = 'POSTED' THEN
3470 IF p_trx_rec_new.status = 'CALC' THEN
3471 IF l_lupd_chg = 1
3472 THEN
3473 revert_posting_line(p_trx_rec_old.commission_line_id);
3474 END IF;
3475 ELSE -- p_trx_rec_new.status = 'XCALC'
3476 revert_posting_line( p_trx_rec_old.commission_line_id);
3477 END IF;
3478 END IF;
3479
3480 IF l_lupd_chg = 1
3481 OR p_trx_rec_old.status <> p_trx_rec_new.status THEN
3482 l_lupd_chg :=1;
3483 END IF;
3484
3485
3486
3487 UPDATE cn_commission_lines_all
3488 SET commission_amount = p_trx_rec_new.commission_amount,
3489 commission_rate = p_trx_rec_new.commission_rate,
3490 rate_tier_id = p_trx_rec_new.rate_tier_id,
3491 tier_split = p_trx_rec_new.tier_split,
3492 input_achieved = p_trx_rec_new.input_achieved,
3493 output_achieved = p_trx_rec_new.output_achieved,
3494 perf_achieved = p_trx_rec_new.perf_achieved,
3495 status = p_trx_rec_new.status,
3496 credit_type_id = p_trx_rec_new.credit_type_id,
3497 posting_status = decode(posting_status, 'REVERTED', decode(p_trx_rec_new.status, 'CALC', 'UNPOSTED', posting_status), posting_status),
3498 error_reason = p_trx_rec_new.error_reason,
3499 last_update_date = decode(l_lupd_chg,1,sysdate,last_update_date),
3500 last_updated_by = g_last_updated_by,
3501 last_update_login = g_last_update_login
3502 WHERE commission_line_id = p_trx_rec_old.commission_line_id;
3503 END update_trx;
3504
3505 -- create a new trx if not existed, create a line in commission_header first
3506 FUNCTION check_pending_trx ( p_salesrep_id NUMBER,
3507 p_srp_plan_assign_id NUMBER,
3508 p_quota_id NUMBER,
3509 p_period_id NUMBER ) RETURN VARCHAR2 IS
3510 l_start_period_id NUMBER(15);
3511 l_counter NUMBER := 0;
3512
3513 CURSOR l_chk_pending_trx_csr IS
3514 SELECT 1
3515 FROM cn_commission_lines_all
3516 WHERE credited_salesrep_id = p_salesrep_id
3517 AND srp_plan_assign_id = p_srp_plan_assign_id
3518 AND quota_id = p_quota_id
3519 AND processed_period_id BETWEEN l_start_period_id AND p_period_id
3520 AND status = 'CALC'
3521 AND created_during <> 'CALC'
3522 AND pending_status = 'Y';
3523
3524 l_pending_status VARCHAR2(30);
3525 BEGIN
3526 l_start_period_id := get_start_period_id(p_quota_id, p_period_id);
3527
3528 OPEN l_chk_pending_trx_csr;
3529 FETCH l_chk_pending_trx_csr INTO l_counter;
3530 CLOSE l_chk_pending_trx_csr;
3531
3532 IF l_counter = 0 THEN
3533 l_pending_status := 'N';
3534 ELSE
3535 l_pending_status := 'Y';
3536 END IF;
3537
3538 RETURN l_pending_status;
3539 END check_pending_trx;
3540
3541 -- create a new trx if not existed, create a line in commission_header first
3542 PROCEDURE create_new_trx (p_trx_rec trx_rec_type) IS
3543 l_header_id NUMBER;
3544 l_role_id NUMBER;
3545 l_org_id NUMBER;
3546 l_commission_line_id number;
3547 BEGIN
3548 SELECT cn_commission_headers_s.NEXTVAL INTO l_header_id FROM dual;
3549
3550 SELECT role_id, org_id INTO l_role_id, l_org_id
3551 FROM cn_srp_plan_assigns_all
3552 WHERE srp_plan_assign_id = p_trx_rec.srp_plan_assign_id;
3553
3554 INSERT INTO cn_commission_headers_all
3555 (commission_header_id, direct_salesrep_id, processed_date,
3556 processed_period_id, trx_type, status, quota_id,
3557 last_update_date, last_updated_by, creation_date,
3558 created_by, last_update_login, org_id )
3559 VALUES
3560 (l_header_id, p_trx_rec.salesrep_id, p_trx_rec.processed_date ,
3561 p_trx_rec.processed_period_id, p_trx_rec.trx_type, 'ROLL', p_trx_rec.quota_id,
3562 sysdate, g_last_updated_by, g_creation_date,
3563 g_created_by, g_last_update_login, l_org_id);
3564
3565 -- then create a line in commission_lines
3566 -- the pending status is determined by checking all trx in all periods?????
3567
3568 INSERT INTO cn_commission_lines_all
3569 (commission_line_id, credited_salesrep_id, commission_header_id,
3570 quota_id, credit_type_id, srp_plan_assign_id, role_id, status ,
3571 commission_amount, commission_rate, rate_tier_id, tier_split,
3572 input_achieved, output_achieved,
3573 perf_achieved, posting_status, pending_status,
3574 processed_date, processed_period_id, pay_period_id,
3575 trx_type, created_during, error_reason,
3576 last_update_date, last_updated_by, creation_date,
3577 created_by, last_update_login, org_id )
3578 VALUES
3579 (cn_commission_lines_s.NEXTVAL, p_trx_rec.salesrep_id, l_header_id,
3580 p_trx_rec.quota_id, p_trx_rec.credit_type_id, p_trx_rec.srp_plan_assign_id, l_role_id, p_trx_rec.status,
3581 Round(Nvl(p_trx_rec.commission_amount,0), g_ext_precision),
3582 Round(Nvl(p_trx_rec.commission_rate,0), CN_GLOBAL_VAR.g_ext_precision ),
3583 Nvl(p_trx_rec.rate_tier_id, 0), Nvl(p_trx_rec.tier_split, 0),
3584 Round( Nvl(p_trx_rec.input_achieved, 0), CN_GLOBAL_VAR.g_ext_precision),
3585 Round( Nvl(p_trx_rec.output_achieved, 0), CN_GLOBAL_VAR.g_ext_precision),
3586 Round( Nvl(p_trx_rec.perf_achieved, 0 ), CN_GLOBAL_VAR.g_ext_precision),
3587 p_trx_rec.posting_status, p_trx_rec.pending_status,
3591 g_created_by, g_last_update_login, l_org_id)
3588 p_trx_rec.processed_date, p_trx_rec.processed_period_id,p_trx_rec.pay_period_id,
3589 p_trx_rec.trx_type, p_trx_rec.created_during, p_trx_rec.error_reason,
3590 sysdate, g_last_updated_by, g_creation_date,
3592 return commission_line_id into l_commission_line_id;
3593
3594 update cn_commission_lines_all cl
3595 set srp_payee_assign_id = (SELECT spa.srp_payee_assign_id
3596 FROM cn_srp_quota_assigns_all sqa,
3597 cn_srp_payee_assigns_all spa
3598 WHERE sqa.srp_plan_assign_id = cl.srp_plan_assign_id
3599 AND sqa.quota_id = cl.quota_id
3600 AND nvl(spa.delete_flag, 'N') <> 'Y'
3601 AND sqa.srp_quota_assign_id = spa.srp_quota_assign_id
3602 AND cl.processed_date BETWEEN spa.start_date AND nvl(spa.end_date,cl.processed_date))
3603 where cl.commission_line_id = l_commission_line_id;
3604
3605 END create_new_trx;
3606
3607 -- create a new group by trx if not existed update the existing group by trx if already existed
3608 -- if already posted, create a reversal first
3609 PROCEDURE create_update_grp_trx( p_grp_trx_rec IN OUT NOCOPY trx_rec_type) IS
3610 l_commission_line_id NUMBER(15);
3611 l_posting_status VARCHAR2(30);
3612 l_existed BOOLEAN := FALSE;
3613
3614 l_header_id NUMBER(15);
3615
3616 CURSOR l_grp_trx_csr IS
3617 SELECT cl.commission_line_id ,cl.commission_header_id,
3618 -- null reversal_header_id, null reversal_flag,
3619 cl.credited_salesrep_id salesrep_id,
3620 cl.srp_plan_assign_id, cl.quota_id, cl.credit_type_id,
3621 cl.processed_date, cl.processed_period_id,
3622 cl.pay_period_id, cl.commission_amount,
3623 cl.commission_rate, cl.rate_tier_id ,
3624 cl.tier_split, cl.input_achieved ,
3625 cl.output_achieved, cl.perf_achieved,
3626 cl.posting_status, cl.pending_status,
3627 cl.created_during, cl.trx_type,
3628 cl.error_reason, cl.status
3629 FROM cn_commission_lines cl
3630 WHERE cl.credited_salesrep_id = p_grp_trx_rec.salesrep_id
3631 AND cl.quota_id = p_grp_trx_rec.quota_id
3632 AND cl.srp_plan_assign_id = p_grp_trx_rec.srp_plan_assign_id
3633 AND cl.created_during = 'CALC'
3634 AND cl.trx_type = 'GRP'
3635 AND cl.processed_period_id = p_grp_trx_rec.processed_period_id;
3636
3637 l_grp_trx_rec_old trx_rec_type;
3638 BEGIN
3639 OPEN l_grp_trx_csr;
3640 FETCH l_grp_trx_csr INTO l_grp_trx_rec_old;
3641
3642 IF l_grp_trx_csr%found THEN
3643 l_existed := TRUE;
3644 END IF;
3645 CLOSE l_grp_trx_csr;
3646
3647 p_grp_trx_rec.pending_status := check_pending_trx( p_grp_trx_rec.salesrep_id,
3648 p_grp_trx_rec.srp_plan_assign_id,
3649 p_grp_trx_rec.quota_id,
3650 p_grp_trx_rec.processed_period_id );
3651
3652 IF l_existed THEN -- the grp trx is already created
3653 update_trx( l_grp_trx_rec_old, p_grp_trx_rec );
3654 ELSE -- the grp trx is not created yet or has been deleted
3655 create_new_trx( p_grp_trx_rec);
3656 END IF;
3657 END create_update_grp_trx;
3658
3659 -- create a new group by trx if not existed; update the existing group by trx if already existed
3660 -- if already posted, create a reversal first
3661 PROCEDURE create_trx( p_trx_rec IN OUT NOCOPY trx_rec_type) IS
3662 l_msg_count NUMBER;
3663 l_msg_data VARCHAR2(2000);
3664 l_return_status VARCHAR2(30);
3665 l_validation_status VARCHAR2(1);
3666 l_srp_pe_rec cn_srp_validation_pub.srp_pe_rec_type ;
3667 BEGIN
3668 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3669 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3670 'cn.plsql.cn_formula_common_pkg.create_trx.begin',
3671 'Within create_trx.');
3672 end if;
3673
3674 cn_message_pkg.debug('Creating or updating system-generated transaction ');
3675
3676 IF p_trx_rec.trx_type = 'GRP' THEN
3677 create_update_grp_trx(p_trx_rec);
3678 ELSIF p_trx_rec.trx_type = 'ITD' THEN
3679 p_trx_rec.pending_status := check_pending_trx( p_trx_rec.salesrep_id,
3680 p_trx_rec.srp_plan_assign_id,
3681 p_trx_rec.quota_id,
3682 p_trx_rec.processed_period_id );
3683 create_new_trx(p_trx_rec);
3684 ELSIF p_trx_rec.trx_type = 'BONUS' THEN
3685 l_srp_pe_rec.salesrep_id := p_trx_rec.salesrep_id;
3686 l_srp_pe_rec.quota_id := p_trx_rec.quota_id;
3687
3688 IF Nvl(fnd_profile.value('CN_SRP_VALIDATION'), 'N') = 'N' THEN
3689 --no need of validation
3690 p_trx_rec.pending_status := 'N';
3691 ELSE -- need to validate
3692
3693 cn_srp_validation_pub.validate_pe
3694 ( p_api_version => 1.0,
3695 p_init_msg_list => fnd_api.g_true,
3696 p_commit => fnd_api.g_false ,
3697 x_return_status => l_return_status,
3698 x_msg_count => l_msg_count,
3699 x_msg_data => l_msg_data,
3700 p_srp_pe => l_srp_pe_rec,
3701 x_validation_status => l_validation_status );
3702
3703 IF l_return_status <> FND_API.g_ret_sts_success THEN
3704 p_trx_rec.error_reason := Substr( p_trx_rec.error_reason
3705 || ' FAILED TO VALIDATE PENDING_STATUS.'
3706 , 1, 150);
3707 p_trx_rec.pending_status := 'Y';
3708 ELSE
3709 IF l_validation_status = 'Y' THEN
3710 p_trx_rec.pending_status := 'N';
3711 ELSE
3712 p_trx_rec.pending_status := 'Y';
3713 END IF;
3714 END IF;
3715 END IF;
3716 create_new_trx(p_trx_rec);
3717 END IF;
3718 EXCEPTION
3719 WHEN OTHERS THEN
3720 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3721 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3725
3722 'cn.plsql.cn_formula_common_pkg.create_trx.exception',
3723 sqlerrm);
3724 end if;
3726 cn_message_pkg.debug('Exception occurs in creating or updating system-generated transaction: ');
3727 cn_message_pkg.debug(sqlerrm);
3728 RAISE;
3729 END create_trx ;
3730
3731 -- populate the event_factor, payment_factor, quota_factor and payees
3732 PROCEDURE populate_factors (p_physical_batch_id NUMBER) IS
3733 CURSOR salesreps IS
3734 SELECT salesrep_id,
3735 period_id,
3736 end_period_id,
3737 start_date,
3738 end_date,
3739 org_id
3740 FROM cn_process_batches_all
3741 WHERE physical_batch_id = p_physical_batch_id;
3742
3743 l_calc_type VARCHAR2(30);
3744 BEGIN
3745 l_calc_type := cn_calc_sub_batches_pkg.get_calc_type(p_physical_batch_id);
3746
3747 IF (l_calc_type = 'BONUS') THEN
3748 RETURN;
3749 END IF;
3750
3751 FOR salesrep IN salesreps LOOP
3752 UPDATE cn_commission_lines_all cl
3753 SET (payment_factor,quota_factor) =
3754 (SELECT squ.payment_factor/100,
3755 squ.quota_factor/100
3756 FROM cn_srp_quota_rules_all sqr,
3757 cn_srp_rule_uplifts_all squ,
3758 cn_quota_rule_uplifts_all qru
3759 WHERE sqr.srp_plan_assign_id = cl.srp_plan_assign_id
3760 AND sqr.quota_rule_id = cl.quota_rule_id
3761 AND sqr.srp_quota_rule_id = squ.srp_quota_rule_id
3762 AND qru.quota_rule_id = cl.quota_rule_id
3763 AND cl.processed_date BETWEEN qru.start_date AND Nvl(qru.end_date, cl.processed_date)
3764 AND qru.quota_rule_uplift_id = squ.quota_rule_uplift_id),
3765 last_update_date = sysdate,
3766 last_updated_by = g_last_updated_by,
3767 last_update_login = g_last_update_login,
3768 event_factor = (SELECT event_factor/100
3769 FROM cn_trx_factors_all tf
3770 WHERE tf.quota_rule_id = cl.quota_rule_id
3771 AND tf.trx_type = cl.trx_type),
3772 (srp_payee_assign_id) = (SELECT spa.srp_payee_assign_id
3773 FROM cn_srp_quota_assigns_all sqa,
3774 cn_srp_payee_assigns_all spa
3775 WHERE sqa.srp_plan_assign_id = cl.srp_plan_assign_id
3776 AND sqa.quota_id = cl.quota_id
3777 AND nvl(spa.delete_flag, 'N') <> 'Y'
3778 AND sqa.srp_quota_assign_id = spa.srp_quota_assign_id
3779 AND cl.processed_date BETWEEN spa.start_date AND nvl(spa.end_date,cl.processed_date))
3780 WHERE cl.credited_salesrep_id = salesrep.salesrep_id
3781 AND cl.processed_period_id between salesrep.period_id AND salesrep.end_period_id
3782 AND cl.processed_date BETWEEN salesrep.start_date AND salesrep.end_date
3783 AND cl.org_id = salesrep.org_id
3784 AND cl.status = 'POP' -- IN ('POP', 'CALC', 'XCALC')
3785 AND ((l_calc_type = 'COMMISSION'
3786 AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
3787 (l_calc_type = 'FORECAST'
3788 AND cl.trx_type = 'FORECAST'));
3789
3790 END LOOP;
3791 END populate_factors;
3792
3793 FUNCTION check_itd_calc_trx ( p_salesrep_id NUMBER,
3794 p_srp_plan_assign_id NUMBER,
3795 p_period_id NUMBER,
3796 p_quota_id NUMBER ) RETURN BOOLEAN IS
3797 CURSOR l_itd_calc_trx_csr IS
3798 SELECT 1
3799 FROM cn_commission_lines_all
3800 WHERE credited_salesrep_id = p_salesrep_id
3801 AND srp_plan_assign_id = p_srp_plan_assign_id
3802 AND processed_period_id = p_period_id
3803 AND quota_id = p_quota_id
3804 AND status = 'CALC';
3805
3806 l_counter NUMBER := 0;
3807 BEGIN
3808 OPEN l_itd_calc_trx_csr;
3809 FETCH l_itd_calc_trx_csr INTO l_counter;
3810 CLOSE l_itd_calc_trx_csr;
3811
3812 IF l_counter = 0 THEN
3813 RETURN FALSE;
3814 ELSE
3815 RETURN TRUE;
3816 END IF;
3817 END check_itd_calc_trx;
3818
3819 FUNCTION get_pq_itd_target ( p_period_id NUMBER,
3820 p_quota_id NUMBER ) RETURN NUMBER IS
3821 CURSOR l_itd_target_csr IS
3822 SELECT pq.itd_target
3823 FROM cn_period_quotas_all pq
3824 WHERE pq.period_id = p_period_id
3825 AND pq.quota_id = p_quota_id;
3826
3827 x_itd_target NUMBER := 0;
3828 BEGIN
3829 OPEN l_itd_target_csr;
3830 FETCH l_itd_target_csr INTO x_itd_target;
3831 CLOSE l_itd_target_csr;
3832
3833 RETURN x_itd_target;
3834 END get_pq_itd_target;
3835
3836
3837 FUNCTION get_spq_itd_target (p_salesrep_id NUMBER,
3838 p_srp_plan_assign_id NUMBER,
3839 p_period_id NUMBER,
3840 p_quota_id NUMBER ) RETURN NUMBER IS
3841 CURSOR l_itd_target_csr IS
3842 SELECT spq.itd_target
3843 FROM cn_srp_period_quotas_all spq
3844 WHERE spq.period_id = p_period_id
3845 AND spq.quota_id = p_quota_id
3846 AND spq.salesrep_id = p_salesrep_id
3847 AND spq.srp_plan_assign_id = p_srp_plan_assign_id;
3848
3849 x_itd_target NUMBER := 0;
3850 BEGIN
3851 OPEN l_itd_target_csr;
3852 FETCH l_itd_target_csr INTO x_itd_target;
3853 CLOSE l_itd_target_csr;
3854
3855 RETURN x_itd_target;
3856 END get_spq_itd_target;
3857
3858
3859 FUNCTION get_pq_itd_payment ( p_period_id NUMBER,
3860 p_quota_id NUMBER ) RETURN NUMBER IS
3861 CURSOR l_itd_payment_csr IS
3862 SELECT pq.itd_payment
3863 FROM cn_period_quotas_all pq
3864 WHERE pq.period_id = p_period_id
3865 AND pq.quota_id = p_quota_id;
3866
3867 x_itd_payment NUMBER := 0;
3868 BEGIN
3869 OPEN l_itd_payment_csr;
3870 FETCH l_itd_payment_csr INTO x_itd_payment;
3871 CLOSE l_itd_payment_csr;
3872
3873 RETURN x_itd_payment;
3874 END get_pq_itd_payment;
3875
3876
3877 FUNCTION get_spq_itd_payment (p_salesrep_id NUMBER,
3878 p_srp_plan_assign_id NUMBER,
3879 p_period_id NUMBER,
3880 p_quota_id NUMBER ) RETURN NUMBER IS
3881 CURSOR l_itd_payment_csr IS
3882 SELECT spq.itd_payment
3883 FROM cn_srp_period_quotas_all spq
3884 WHERE spq.period_id = p_period_id
3885 AND spq.quota_id = p_quota_id
3886 AND spq.salesrep_id = p_salesrep_id
3887 AND spq.srp_plan_assign_id = p_srp_plan_assign_id;
3888
3889 x_itd_payment NUMBER := 0;
3890 BEGIN
3891 OPEN l_itd_payment_csr;
3892 FETCH l_itd_payment_csr INTO x_itd_payment;
3893 CLOSE l_itd_payment_csr;
3894
3895 RETURN x_itd_payment;
3896 END get_spq_itd_payment;
3897 END cn_formula_common_pkg;