[Home] [Help]
PACKAGE BODY: APPS.CN_UPGRADE_PE_FORMULA_PKG
Source
1 PACKAGE BODY cn_upgrade_pe_formula_pkg AS
2 /* $Header: cnuppefb.pls 120.2 2005/09/19 11:47:42 ymao noship $ */
3 api_fail EXCEPTION;
4
5 PROCEDURE get_formula_name (
6 p_quota_id IN NUMBER,
7 x_formula_name OUT NOCOPY VARCHAR2
8 )
9 IS
10 CURSOR l_quota_csr
11 IS
12 SELECT *
13 FROM cn_quotas_all
14 WHERE quota_id = p_quota_id;
15
16 l_quota cn_quotas_all%ROWTYPE;
17 l_formula_name VARCHAR2 (30) := '';
18 BEGIN
19 OPEN l_quota_csr;
20
21 FETCH l_quota_csr
22 INTO l_quota;
23
24 CLOSE l_quota_csr;
25
26 SELECT DECODE (l_quota.quota_type_code,
27 'REVENUE', 'RNQ',
28 'TARGET', 'RQ',
29 'UNIT_BASED_QUOTA', 'UQ',
30 'UNIT_BASED_NON_QUOTA', 'UNQ',
31 'DISCOUNT', 'DIS',
32 'MARGIN', 'MR'
33 )
34 || '_'
35 || DECODE (l_quota.trx_group_code, 'INDIVIDUAL', 'In', 'GROUP', 'Gr', 'In')
36 || '_'
37 || DECODE (l_quota.discount_option_code, 'NONE', 'DiN', 'QUOTA', 'DiQ', 'PAYMENT', 'DiP', 'DiN')
38 || '_'
39 || DECODE (l_quota.payment_type_code, 'FIXED', 'Fix', 'PAYMENT', 'Pay', 'TRANSACTION', 'App', '')
40 || '_'
41 || DECODE (l_quota.cumulative_flag, 'Y', 'CuY', 'N', 'CuN', 'CuN')
42 || '_'
43 || DECODE (l_quota.split_flag, 'Y', 'SY', 'N', 'SN', 'SN')
44 || '_'
45 || DECODE (l_quota.itd_flag, 'Y', 'IY', 'N', 'IN', 'IN')
46 || '_'
47 || TO_CHAR (l_quota.org_id)
48 INTO l_formula_name
49 FROM DUAL;
50
51 x_formula_name := l_formula_name;
52 END get_formula_name;
53
54 FUNCTION get_perf_measure (
55 p_name VARCHAR2,
56 p_org_id NUMBER
57 )
58 RETURN NUMBER
59 IS
60 CURSOR measure IS
61 SELECT calc_sql_exp_id
62 FROM cn_calc_sql_exps_all
63 WHERE NAME = p_name
64 AND ORG_ID = p_org_id;
65
66 l_perf_measure_id NUMBER;
67 l_return_status VARCHAR2 (1);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2 (2000);
70 l_status VARCHAR2 (30);
71 l_sql_select VARCHAR2 (500);
72 l_exp_type_code VARCHAR2 (30);
73 l_ovn NUMBER;
74 BEGIN
75 OPEN measure;
76
77 FETCH measure INTO l_perf_measure_id;
78 IF (measure%NOTFOUND) THEN
79 CLOSE measure;
80
81 SELECT DECODE (p_name,
82 'Revenue', 'ch.transaction_amount',
83 'Quantity', 'ch.quantity',
84 'Discount Percentage/100', 'ch.discount_percentage/100',
85 'Margin Percentage/100', 'ch.margin_percentage/100',
86 'ch.transaction_amount'
87 )
88 INTO l_sql_select
89 FROM DUAL;
90
91 cn_calc_sql_exps_pvt.create_expression (p_api_version => 1.0,
92 p_org_id => p_org_id,
93 p_name => p_name,
94 p_description => p_name,
95 p_expression_disp => UPPER (p_name),
96 p_sql_select => UPPER (l_sql_select),
97 p_sql_from => 'CN_COMMISSION_HEADERS CH',
98 p_piped_expression_disp => '(' || UPPER (p_name) || ')' || '|',
99 p_piped_sql_select => '(' || UPPER (l_sql_select) || ')' || '|',
100 p_piped_sql_from => 'CN_COMMISSION_HEADERS CH|',
101 x_calc_sql_exp_id => l_perf_measure_id,
102 x_exp_type_code => l_exp_type_code,
103 x_status => l_status,
104 x_return_status => l_return_status,
105 x_msg_count => l_msg_count,
106 x_msg_data => l_msg_data,
107 x_object_version_number => l_ovn
108 );
109
110 IF (l_return_status <> fnd_api.g_ret_sts_success)
111 THEN
112 FOR i IN 1 .. l_msg_count
113 LOOP
114 NULL; -- dbms_output.put_line('msg: ' || fnd_msg_pub.get(i, 'F'));
115 END LOOP;
116
117 RAISE api_fail;
118 END IF;
119 ELSE
120 CLOSE measure;
121 END IF;
122
123 RETURN l_perf_measure_id;
124 END get_perf_measure;
125
126 PROCEDURE get_calc_expression (
127 p_org_id NUMBER,
128 p_exp_name VARCHAR2,
129 p_sql_select VARCHAR2,
130 p_sql_from VARCHAR2,
131 p_disp VARCHAR2,
132 p_piped_sql_from VARCHAR2,
133 x_calc_sql_exp_id OUT NOCOPY NUMBER
134 )
135 IS
136 CURSOR expr IS
137 SELECT calc_sql_exp_id
138 FROM cn_calc_sql_exps_all
139 WHERE NAME = p_exp_name
140 AND ORG_ID = p_org_id;
141
142 l_return_status VARCHAR2 (1);
143 l_msg_count NUMBER;
144 l_msg_data VARCHAR2 (2000);
145 l_status VARCHAR2 (30);
146 l_exp_type_code VARCHAR2 (30);
147 l_ovn NUMBER;
148 BEGIN
149 OPEN expr;
150 FETCH expr INTO x_calc_sql_exp_id;
151 IF (expr%NOTFOUND) THEN
152 CLOSE expr;
153
154 cn_calc_sql_exps_pvt.create_expression (p_api_version => 1.0,
155 p_org_id => p_org_id,
156 p_name => p_exp_name,
157 p_description => p_exp_name,
158 p_expression_disp => p_disp,
159 p_sql_select => p_sql_select,
160 p_sql_from => p_sql_from,
161 p_piped_expression_disp => '(' || p_disp || ')' || '|',
162 p_piped_sql_select => '(' || p_sql_select || ')|',
163 p_piped_sql_from => p_piped_sql_from,
164 x_calc_sql_exp_id => x_calc_sql_exp_id,
165 x_exp_type_code => l_exp_type_code,
166 x_status => l_status,
167 x_return_status => l_return_status,
168 x_msg_count => l_msg_count,
169 x_msg_data => l_msg_data,
170 x_object_version_number => l_ovn
171 );
172
173 IF (l_return_status <> fnd_api.g_ret_sts_success)
174 THEN
175 FOR i IN 1 .. l_msg_count
176 LOOP
177 NULL; -- dbms_output.put_line('msg: ' || fnd_msg_pub.get(i, 'F'));
178 END LOOP;
179
180 RAISE api_fail;
181 END IF;
182 ELSE
183 CLOSE expr;
184 END IF;
185 END get_calc_expression;
186
187 FUNCTION get_input (
188 p_name VARCHAR2,
189 p_org_id NUMBER
190 )
191 RETURN NUMBER
192 IS
193 CURSOR input IS
194 SELECT calc_sql_exp_id
195 FROM cn_calc_sql_exps_all
196 WHERE NAME = p_name
197 and org_id = p_org_id;
198
199 l_calc_sql_exp_id NUMBER;
200 l_return_status VARCHAR2 (1);
201 l_msg_count NUMBER;
202 l_msg_data VARCHAR2 (2000);
203 l_status VARCHAR2 (30);
204 l_exp_type_code VARCHAR2 (30);
205 l_ovn NUMBER;
206 BEGIN
207 OPEN input;
208 FETCH input INTO l_calc_sql_exp_id;
209 IF (input%NOTFOUND) THEN
210 CLOSE input;
211
212 cn_calc_sql_exps_pvt.create_expression (p_api_version => 1.0,
213 p_org_id => p_org_id,
214 p_name => p_name,
215 p_description => p_name,
216 p_expression_disp => UPPER (p_name),
217 p_sql_select => 'CH.DISCOUNT_PERCENTAGE/100',
218 p_sql_from => 'CN_COMMISSION_HEADERS CH',
219 p_piped_expression_disp => '(' || UPPER (p_name) || ')' || '|',
220 p_piped_sql_select => '(CH.DISCOUNT_PERCENTAGE/100)|',
221 p_piped_sql_from => 'CN_COMMISSION_HEADERS CH|',
222 x_calc_sql_exp_id => l_calc_sql_exp_id,
223 x_exp_type_code => l_exp_type_code,
224 x_status => l_status,
225 x_return_status => l_return_status,
226 x_msg_count => l_msg_count,
227 x_msg_data => l_msg_data,
228 x_object_version_number => l_ovn
229 );
230
231 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
232 FOR i IN 1 .. l_msg_count
233 LOOP
234 NULL; -- dbms_output.put_line('msg: ' || fnd_msg_pub.get(i, 'F'));
235 END LOOP;
236
237 RAISE api_fail;
238 END IF;
239 ELSE
240 CLOSE input;
241 END IF;
242
243 RETURN l_calc_sql_exp_id;
244 END get_input;
245
246 FUNCTION get_output (
247 p_name VARCHAR2,
248 p_org_id NUMBER
249 )
250 RETURN NUMBER
251 IS
252 CURSOR output IS
253 SELECT calc_sql_exp_id
254 FROM cn_calc_sql_exps_all
255 WHERE NAME = p_name
256 and org_id = p_org_id;
257
258 l_calc_sql_exp_id NUMBER;
259 l_return_status VARCHAR2 (1);
260 l_msg_count NUMBER;
261 l_msg_data VARCHAR2 (2000);
262 l_status VARCHAR2 (30);
263 l_exp_type_code VARCHAR2 (30);
264 l_ovn NUMBER;
265 BEGIN
266 OPEN output;
267 FETCH output INTO l_calc_sql_exp_id;
268 IF (output%NOTFOUND) THEN
269 CLOSE output;
270
271 cn_calc_sql_exps_pvt.create_expression (p_api_version => 1.0,
272 p_org_id => p_org_id,
273 p_name => p_name,
274 p_description => p_name,
275 p_expression_disp => 'RateResult',
276 p_sql_select => 'RateResult',
277 p_sql_from => 'DUAL',
278 p_piped_expression_disp => 'RateResult|',
279 p_piped_sql_select => 'RateResult|',
280 p_piped_sql_from => 'DUAL|',
281 x_calc_sql_exp_id => l_calc_sql_exp_id,
282 x_exp_type_code => l_exp_type_code,
283 x_status => l_status,
284 x_return_status => l_return_status,
285 x_msg_count => l_msg_count,
286 x_msg_data => l_msg_data,
287 x_object_version_number => l_ovn
288 );
289
290 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
291 FOR i IN 1 .. l_msg_count LOOP
292 NULL; -- dbms_output.put_line('msg: ' || fnd_msg_pub.get(i, 'F'));
293 END LOOP;
294
295 RAISE api_fail;
296 END IF;
297 ELSE
298 CLOSE output;
299 END IF;
300
301 RETURN l_calc_sql_exp_id;
302 END get_output;
303
304 FUNCTION check_formula_exist (
305 p_quota_id IN NUMBER
306 )
307 RETURN NUMBER
308 IS
309 l_formula_name VARCHAR2 (30);
310 l_formula_id NUMBER (15);
311
312 CURSOR l_formula_csr
313 IS
314 SELECT f.calc_formula_id
315 FROM cn_calc_formulas_all f,
316 cn_quotas_all q
317 WHERE f.NAME = l_formula_name
318 AND q.quota_id = p_quota_id
319 AND ((q.org_id = f.org_id) OR (q.org_id IS NULL AND f.org_id IS NULL));
320 BEGIN
321 get_formula_name (p_quota_id => p_quota_id,
322 x_formula_name => l_formula_name);
323
324 OPEN l_formula_csr;
325 FETCH l_formula_csr INTO l_formula_id;
326 IF (l_formula_csr%NOTFOUND) THEN
327 CLOSE l_formula_csr;
328 RETURN NULL;
329 ELSE
330 CLOSE l_formula_csr;
331 RETURN l_formula_id;
332 END IF;
333 END check_formula_exist;
334
335 PROCEDURE create_discount_option_formula (
336 p_org_id IN NUMBER,
337 x_formula_id OUT NOCOPY NUMBER
338 )
339 IS
340 l_return_status VARCHAR2 (1);
341 l_msg_count NUMBER;
342 l_msg_data VARCHAR2 (1000);
343 l_status VARCHAR2 (30);
344 l_formula_status VARCHAR2 (30);
345 l_calc_formula_id NUMBER (15);
346 l_process_audit_id NUMBER;
347 l_input_tbl cn_calc_formulas_pvt.input_tbl_type;
348 BEGIN
349 l_input_tbl (1).calc_sql_exp_id := get_input ('discount_percentage/100', p_org_id);
350 l_input_tbl (1).rate_dim_sequence := 1;
354 p_description => '',
351 cn_calc_formulas_pvt.create_formula (p_api_version => 1.0,
352 p_generate_packages => fnd_api.g_false,
353 p_name => 'Discount Option Formula',
355 p_formula_type => 'C',
356 p_trx_group_code => 'INDIVIDUAL',
357 p_number_dim => 1,
358 p_cumulative_flag => 'N',
359 p_itd_flag => 'N',
360 p_split_flag => 'N',
361 p_threshold_all_tier_flag => 'N',
362 p_modeling_flag => 'N',
363 p_perf_measure_id => get_perf_measure ('Discount Percentage', p_org_id),
364 p_output_exp_id => get_output ('RateResult', p_org_id),
365 p_input_tbl => l_input_tbl,
366 p_org_id => p_org_id,
367 x_calc_formula_id => l_calc_formula_id,
368 x_formula_status => l_status,
369 x_return_status => l_return_status,
370 x_msg_count => l_msg_count,
371 x_msg_data => l_msg_data
372 );
373
374 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
375 FOR i IN 1 .. l_msg_count
376 LOOP
377 NULL; --dbms_output.put_line('msg: ' || fnd_msg_pub.get(i, 'F'));
378 END LOOP;
379
380 RAISE api_fail;
381 END IF;
382
383 x_formula_id := l_calc_formula_id;
384 cn_formula_gen_pkg.generate_formula (p_api_version => 1.0,
385 x_return_status => l_return_status,
386 x_msg_count => l_msg_count,
387 x_msg_data => l_msg_data,
388 p_formula_id => x_formula_id,
389 p_org_id => p_org_id,
390 x_process_audit_id => l_process_audit_id
391 );
392 END create_discount_option_formula;
393
394 PROCEDURE create_formula_from_quota (
395 p_quota_id IN NUMBER,
396 x_formula_id OUT NOCOPY NUMBER,
397 x_return_status OUT NOCOPY VARCHAR2
398 )
399 IS
400 l_return_status VARCHAR2 (1);
401 l_msg_count NUMBER;
402 l_msg_data VARCHAR2 (1000);
403 l_status VARCHAR2 (30);
404 l_discount_formula_id NUMBER (15);
405 l_formula_name VARCHAR2 (30);
406 l_calc_formula_id NUMBER (15);
407 l_input_exp_id NUMBER (15);
408 l_output_exp_id NUMBER (15);
409 l_exp_name VARCHAR2 (30);
410 l_sql_select VARCHAR (500);
411 l_sql_from VARCHAR2 (500);
412 l_piped_sql_from VARCHAR2 (500);
413 l_exp_disp VARCHAR2 (500);
414 l_input_tbl cn_calc_formulas_pvt.input_tbl_type;
415 l_perf_measure_name VARCHAR2 (80);
416
417 CURSOR l_quota_csr
418 IS
419 SELECT *
420 FROM cn_quotas_all
421 WHERE quota_id = p_quota_id;
422
423 l_quota cn_quotas_all%ROWTYPE;
424
425 CURSOR l_discount_option_csr(p_org_id number)
426 IS
427 SELECT calc_formula_id
428 FROM cn_calc_formulas_all
429 WHERE NAME = 'Discount Option Formula'
430 AND org_id = p_org_id;
431 BEGIN
432 OPEN l_quota_csr;
433 FETCH l_quota_csr INTO l_quota;
434 CLOSE l_quota_csr;
435
436 -- IF l_quota.org_id IS NOT NULL THEN
437 -- fnd_client_info.set_org_context (l_quota.org_id);
438 -- END IF;
439
440 get_formula_name (p_quota_id => p_quota_id, x_formula_name => l_formula_name);
441
442 SELECT DECODE (l_quota.quota_type_code,
443 'TARGET', 'Revenue',
444 'REVENUE', 'Revenue',
445 'UNIT_BASED_QUOTA', 'Quantity',
446 'UNIT_BASED_NON_QUOTA', 'Quantity',
447 'DISCOUNT', 'Discount Percentage',
448 'MARGIN', 'Margin Percentage',
449 'Revenue'
450 )
451 INTO l_perf_measure_name
452 FROM DUAL;
453
454 IF l_quota.quota_type_code = 'TARGET'
455 THEN
459 l_sql_from := 'CN_COMMISSION_HEADERS CH, CN_SRP_PERIOD_QUOTAS CSPQ';
456 IF l_quota.itd_flag = 'Y'
457 THEN
458 l_sql_select := 'CH.TRANSACTION_AMOUNT/CSPQ.ITD_TARGET';
460 l_piped_sql_from := 'CN_COMMISSION_HEADERS CH|CN_SRP_PERIOD_QUOTAS CSPQ|';
461 l_exp_disp := 'TRANSACTION_AMOUNT/ITD_TARGET';
462 l_exp_name := 'TRX_AMOUNT/ITD_TARGET';
463 ELSE
464 l_sql_select := 'CH.TRANSACTION_AMOUNT/CSQA.TARGET';
465 l_sql_from := 'CN_COMMISSION_HEADERS CH, CN_SRP_QUOTA_ASSIGNS CSQA';
466 l_piped_sql_from := 'CN_COMMISSION_HEADERS CH|CN_SRP_QUOTA_ASSIGNS CSQA|';
467 l_exp_disp := 'TRANSACTION_AMOUNT/TARGET';
468 l_exp_name := 'TRX_AMOUNT/TARGET';
469 END IF;
470 ELSIF l_quota.quota_type_code = 'REVENUE'
471 THEN
472 l_sql_select := 'CH.TRANSACTION_AMOUNT';
473 l_sql_from := 'CN_COMMISSION_HEADERS CH';
474 l_piped_sql_from := 'CN_COMMISSION_HEADERS CH|';
475 l_exp_disp := 'TRANSACTION_AMOUNT';
476 l_exp_name := 'TRX_AMOUNT';
477 ELSIF l_quota.quota_type_code = 'UNIT_BASED_QUOTA'
478 THEN
479 l_sql_select := 'CH.QUANTITY/CSQA.TARGET';
480 l_sql_from := 'CN_COMMISSION_HEADERS CH, CN_SRP_QUOTA_ASSIGNS CSQA';
481 l_piped_sql_from := 'CN_COMMISSION_HEADERS CH|CN_SRP_QUOTA_ASSIGNS CSQA|';
482 l_exp_disp := 'QUANTITY/TARGET';
483 l_exp_name := 'QUANTITY/TARGET';
484 ELSIF l_quota.quota_type_code = 'UNIT_BASED_NON_QUOTA'
485 THEN
486 l_sql_select := 'CH.QUANTITY';
487 l_sql_from := 'CN_COMMISSION_HEADERS CH';
488 l_exp_disp := 'QUANTITY';
489 l_exp_name := 'QUANTITY';
490 ELSIF l_quota.quota_type_code = 'DISCOUNT'
491 THEN
492 l_sql_select := 'CH.DISCOUNT_PERCENTAGE/100';
493 l_sql_from := 'CN_COMMISSION_HEADERS CH';
494 l_piped_sql_from := 'CN_COMMISSION_HEADERS CH|';
495 l_exp_disp := 'DISCOUNT_PERCENTAGE/100';
496 l_exp_name := 'DISCOUNT';
497 ELSIF l_quota.quota_type_code = 'MARGIN'
498 THEN
499 l_sql_select := 'CH.MARGIN_PERCENTAGE/100';
500 l_sql_from := 'CN_COMMISSION_HEADERS CH';
501 l_piped_sql_from := 'CN_COMMISSION_HEADERS CH|';
502 l_exp_disp := 'MARGIN_PERCENTAGE/100';
503 l_exp_name := 'MARGIN';
504 END IF;
505
506 l_sql_select := l_sql_select || '*CL.EVENT_FACTOR*CL.QUOTA_FACTOR';
507 l_sql_from := l_sql_from || ', CN_COMMISSION_LINES CL';
508 l_piped_sql_from := l_piped_sql_from || 'CN_COMMISSION_LINES CL|';
509 l_exp_disp := l_exp_disp || '*EVENT_FACTOR*QUOTA_FACTOR';
510
511 -- Handle discount option
512 IF l_quota.discount_option_code <> 'NONE'
513 THEN
514 OPEN l_discount_option_csr(l_quota.org_id);
515 FETCH l_discount_option_csr INTO l_discount_formula_id;
516 IF l_discount_option_csr%NOTFOUND THEN
517 create_discount_option_formula (l_quota.org_id, l_discount_formula_id);
518 END IF;
519 CLOSE l_discount_option_csr;
520 END IF;
521
522 IF l_quota.discount_option_code = 'QUOTA'
523 THEN
524 l_sql_select :=
525 l_sql_select || '*cn_formula_' || ABS (l_discount_formula_id) || '_' || ABS (l_quota.org_id)
526 || '_pkg.get_result(p_commission_line_id)';
527 l_exp_disp := l_exp_disp || '*Discount Option Formula';
528 l_exp_name := l_exp_name || '_Disc';
529 END IF;
530
531 IF l_quota.trx_group_code = 'GROUP'
532 THEN
533 l_sql_select := 'SUM(' || l_sql_select || ')';
534 l_exp_disp := 'SUM(' || l_exp_disp || ')';
535 l_exp_name := 'SUM(' || l_exp_name || ')';
536 END IF;
537
538 get_calc_expression (l_quota.org_id,
539 l_exp_name,
540 l_sql_select,
541 l_sql_from,
542 l_exp_disp,
543 l_piped_sql_from,
544 l_input_exp_id);
545 -- Building output
546 l_sql_select := 'RateResult';
547 l_sql_from := 'CN_COMMISSION_LINES CL';
548 l_piped_sql_from := 'CN_COMMISSION_LINES CL|';
549 l_exp_disp := 'RateResult';
550 l_exp_name := 'Rate';
551
552 IF l_quota.payment_type_code = 'FIXED'
553 THEN
554 IF l_quota.quota_type_code IN ('UNIT_BASED_QUOTA', 'UNIT_BASED_NON_QUOTA')
555 THEN
556 IF (l_quota.trx_group_code = 'GROUP')
557 THEN
558 l_sql_select := l_sql_select || '*ABS(SUM(CH.QUANTITY))/SUM(CH.QUANTITY)';
559 l_exp_disp := l_exp_disp || '*ABS(SUM(QUANTITY))/SUM(QUANTITY)';
560 l_exp_name := l_exp_name || '*SumQSign';
561 ELSE
562 l_sql_select := l_sql_select || '*CL.PAYMENT_FACTOR*ABS(CH.QUANTITY)/CH.QUANTITY';
563 l_exp_disp := l_exp_disp || '*PAYMENT_FACTOR*ABS(QUANTITY)/QUANTITY';
564 l_exp_name := l_exp_name || '*QSign';
565 END IF;
566
567 l_sql_from := l_sql_from || ', CN_COMMISSION_HEADERS CH';
568 l_piped_sql_from := l_piped_sql_from || 'CN_COMMISSION_HEADERS CH|';
569 ELSIF l_quota.quota_type_code IN ('TARGET', 'REVENUE')
570 THEN
574 l_exp_disp := l_exp_disp || '*ABS(SUM(TRANSACTION_AMOUNT))/SUM(TRANSACTION_AMOUNT)';
571 IF (l_quota.trx_group_code = 'GROUP')
572 THEN
573 l_sql_select := l_sql_select || '*ABS(SUM(CH.TRANSACTION_AMOUNT))/SUM(CH.TRANSACTION_AMOUNT)';
575 l_exp_name := l_exp_name || '*SumRSign';
576 ELSE
577 l_sql_select := l_sql_select || '*CL.PAYMENT_FACTOR*ABS(CH.TRANSACTION_AMOUNT)/CH.TRANSACTION_AMOUNT';
578 l_exp_disp := l_exp_disp || '*PAYMENT_FACTOR*ABS(TRANSACTION_AMOUNT)/TRANSACTION_AMOUNT';
579 l_exp_name := l_exp_name || '*RSign';
580 END IF;
581
582 l_sql_from := l_sql_from || ', CN_COMMISSION_HEADERS CH';
583 l_piped_sql_from := l_piped_sql_from || 'CN_COMMISSION_HEADERS CH|';
584 END IF;
585 ELSIF l_quota.payment_type_code = 'PAYMENT'
586 THEN
587 -- l_sql_select := l_sql_select || '*CSQA.PAYMENT_AMOUNT';
588 l_sql_from := l_sql_from || ', CN_SRP_QUOTA_ASSIGNS CSQA';
589 l_piped_sql_from := l_piped_sql_from || 'CN_SRP_QUOTA_ASSIGNS CSQA|';
590 l_exp_disp := l_exp_disp || '*PAYMENT_AMOUNT';
591 l_exp_name := l_exp_name || '*Payment';
592
596 THEN
593 IF l_quota.quota_type_code IN ('UNIT_BASED_QUOTA', 'UNIT_BASED_NON_QUOTA')
594 THEN
595 IF (l_quota.trx_group_code = 'GROUP')
597 l_sql_select := l_sql_select || '*ABS(SUM(CSQA.PAYMENT_AMOUNT*CH.QUANTITY))/SUM(CH.QUANTITY)';
598 l_exp_disp := l_exp_disp || '*ABS(SUM(QUANTITY))/SUM(QUANTITY)';
599 l_exp_name := l_exp_name || '*SumQSign';
600 ELSE
601 l_sql_select := l_sql_select || '*CSQA.PAYMENT_AMOUNT*CL.PAYMENT_FACTOR*ABS(CH.QUANTITY)/CH.QUANTITY';
602 l_exp_disp := l_exp_disp || '*PAYMENT_FACTOR*ABS(QUANTITY)/QUANTITY';
603 l_exp_name := l_exp_name || '*QSign';
604 END IF;
605
606 l_sql_from := l_sql_from || ', CN_COMMISSION_HEADERS CH';
607 l_piped_sql_from := l_piped_sql_from || 'CN_COMMISSION_HEADERS CH|';
608 ELSIF l_quota.quota_type_code IN ('REVENUE', 'TARGET')
609 THEN
610 IF (l_quota.trx_group_code = 'GROUP')
611 THEN
612 l_sql_select := l_sql_select || '*ABS(SUM(CSQA.PAYMENT_AMOUNT*CH.TRANSACTION_AMOUNT))/SUM(CH.TRANSACTION_AMOUNT)';
613 l_exp_disp := l_exp_disp || '*ABS(SUM(TRANSACTION_AMOUNT))/SUM(TRANSACTION_AMOUNT)';
614 l_exp_name := l_exp_name || '*SumRSign';
615 ELSE
616 l_sql_select := l_sql_select || '*CSQA.PAYMENT_AMOUNT*CL.PAYMENT_FACTOR*ABS(CH.TRANSACTION_AMOUNT)/CH.TRANSACTION_AMOUNT';
617 l_exp_disp := l_exp_disp || '*PAYMENT_FACTOR*ABS(TRANSACTION_AMOUNT)/TRANSACTION_AMOUNT';
618 l_exp_name := l_exp_name || '*RSign';
619 END IF;
620
621 l_sql_from := l_sql_from || ', CN_COMMISSION_HEADERS CH';
622 l_piped_sql_from := l_piped_sql_from || 'CN_COMMISSION_HEADERS CH|';
623 END IF;
624 ELSIF l_quota.payment_type_code = 'TRANSACTION'
625 THEN
626 IF (l_quota.trx_group_code = 'GROUP')
627 THEN
628 IF (l_quota.discount_option_code = 'PAYMENT')
629 THEN
630 l_sql_select :=
631 l_sql_select
632 || '*SUM(CL.PAYMENT_FACTOR*CH.TRANSACTION_AMOUNT*cn_formula_'
633 || ABS (l_discount_formula_id)
634 || '_'
635 || ABS (l_quota.org_id)
636 || '_pkg.get_result(p_commission_line_id))';
637 l_exp_disp := l_exp_disp || '*SUM(PAYMENT_FACTOR*TRANSACTION_AMOUNT*Discount Option Formula)';
638 l_exp_name := l_exp_name || '*SUM(TRX_AMOUNT_Disc)';
639 ELSE
640 l_sql_select := l_sql_select || '*SUM(CL.PAYMENT_FACTOR*CH.TRANSACTION_AMOUNT)';
641 l_exp_disp := l_exp_disp || '*SUM(PAYMENT_FACTOR*TRANSACTION_AMOUNT)';
642 l_exp_name := l_exp_name || '*SUM(TRX_AMOUNT)';
643 END IF;
644 ELSE
645 l_sql_select := l_sql_select || '*CL.PAYMENT_FACTOR*CH.TRANSACTION_AMOUNT';
646 l_exp_disp := l_exp_disp || '*PAYMENT_FACTOR*TRANSACTION_AMOUNT';
647 l_exp_name := l_exp_name || '*TRX_AMOUNT';
648 END IF;
649
650 l_sql_from := l_sql_from || ', CN_COMMISSION_HEADERS CH';
651 l_piped_sql_from := l_piped_sql_from || 'CN_COMMISSION_HEADERS CH|';
652 END IF;
653
654 IF (l_quota.discount_option_code = 'PAYMENT' AND (l_quota.payment_type_code <> 'TRANSACTION' OR l_quota.trx_group_code <> 'GROUP'))
655 THEN
656 l_sql_select :=
657 l_sql_select || '*cn_formula_' || ABS (l_discount_formula_id) || '_' || ABS (l_quota.org_id)
658 || '_pkg.get_result(p_commission_line_id)';
659 l_exp_disp := l_exp_disp || '*Discount Option Formula';
660 l_exp_name := l_exp_name || '_Disc';
661 END IF;
662
663 get_calc_expression (l_quota.org_id,
664 l_exp_name,
665 l_sql_select,
666 l_sql_from,
667 l_exp_disp,
668 l_piped_sql_from,
669 l_output_exp_id);
670 l_input_tbl (1).calc_sql_exp_id := l_input_exp_id;
671 l_input_tbl (1).rate_dim_sequence := 1;
672 cn_calc_formulas_pvt.create_formula (p_api_version => 1.0,
673 p_generate_packages => fnd_api.g_false,
674 p_name => l_formula_name,
675 p_description => '',
676 p_formula_type => 'C',
677 p_trx_group_code => l_quota.trx_group_code,
678 p_number_dim => 1,
679 p_cumulative_flag => l_quota.cumulative_flag,
680 p_itd_flag => l_quota.itd_flag,
681 p_split_flag => l_quota.split_flag,
682 p_threshold_all_tier_flag => 'N',
683 p_modeling_flag => 'N',
684 p_perf_measure_id => get_perf_measure (l_perf_measure_name, l_quota.org_id),
685 p_output_exp_id => l_output_exp_id,
686 p_input_tbl => l_input_tbl,
687 p_org_id => l_quota.org_id,
688 x_calc_formula_id => l_calc_formula_id,
689 x_formula_status => l_status,
690 x_return_status => l_return_status,
691 x_msg_count => l_msg_count,
692 x_msg_data => l_msg_data
693 );
694
695 IF (l_return_status <> fnd_api.g_ret_sts_success)
696 THEN
697 FOR i IN 1 .. l_msg_count
698 LOOP
699 NULL; --dbms_output.put_line('msg: ' || fnd_msg_pub.get(i, 'F'));
700 END LOOP;
704
701
702 RAISE api_fail;
703 END IF;
705 x_formula_id := l_calc_formula_id;
706 x_return_status := l_return_status;
707 END create_formula_from_quota;
708
709 PROCEDURE get_formula_id (
710 p_quota_id IN NUMBER,
711 x_formula_id OUT NOCOPY NUMBER,
712 x_return_status OUT NOCOPY VARCHAR2
713 )
714 IS
715 CURSOR l_quota_csr IS
716 SELECT *
717 FROM cn_quotas_all
718 WHERE quota_id = p_quota_id;
719
720 l_quota cn_quotas_all%ROWTYPE;
721 BEGIN
722 x_return_status := 'S';
723
724 OPEN l_quota_csr;
725 FETCH l_quota_csr INTO l_quota;
726 CLOSE l_quota_csr;
727
728 IF l_quota.quota_type_code IN ('REVENUE', 'TARGET', 'UNIT_BASED_QUOTA', 'UNIT_BASED_NON_QUOTA', 'DISCOUNT', 'MARGIN')
729 THEN
730 x_formula_id := check_formula_exist (p_quota_id);
731
732 IF x_formula_id IS NULL THEN
733 create_formula_from_quota (p_quota_id => p_quota_id, x_formula_id => x_formula_id, x_return_status => x_return_status);
734 END IF;
735 ELSIF l_quota.quota_type_code IN ('MANUAL', 'DRAW') THEN
736 -- non formula type
737 x_formula_id := NULL;
738 ELSE
739 -- Invalid quota_type_code
740 x_formula_id := NULL;
741 x_return_status := 'E';
742 END IF;
743 END get_formula_id;
744 END cn_upgrade_pe_formula_pkg;