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