4 pg_debug VARCHAR2(1) := ar_cmgt_util.get_wf_debug_flag;
1 PACKAGE BODY AR_CMGT_SCORING_ENGINE AS
2 /* $Header: ARCMGSEB.pls 120.26 2010/08/24 23:03:56 rravikir noship $ */
3
5
6 PROCEDURE debug (
7 p_message_name IN VARCHAR2 ) IS
8 BEGIN
9 ar_cmgt_util.wf_debug ('SM',p_message_name);
10 END;
11
12 PROCEDURE Calculate_score(
13 p_score_model_id IN NUMBER,
14 p_data_point_id IN NUMBER,
15 p_data_point_value IN VARCHAR2,
16 p_score OUT NOCOPY NUMBER,
17 p_error_msg OUT NOCOPY VARCHAR2,
18 p_resultout OUT NOCOPY VARCHAR2 ) IS
19
20 l_max_score ar_cmgt_score_dtls.scores%TYPE;
21 l_scores ar_cmgt_score_dtls.scores%TYPE;
22 l_weight ar_cmgt_score_weights.weight%TYPE;
23 l_data_point_type VARCHAR2(255);
24 l_data_point_value ar_cmgt_cf_dtls.data_point_value%type;
25 l_date_format VARCHAR2(255);
26 NULL_ZERO_CONVR_IND VARCHAR2(1);
27 BEGIN
28 IF pg_debug = 'Y'
29 THEN
30 debug ( 'In calculate Score (+)');
31 debug ( 'Data Point Id : ' || p_data_point_id );
32 debug ( 'Data Point Value : ' || p_data_point_value );
33 END IF;
34 p_resultout := 0;
35
36 BEGIN
37 SELECT max(scores)
38 INTO l_max_score
39 FROM ar_cmgt_score_dtls
40 WHERE score_model_id = p_score_model_id
41 AND data_point_id = p_data_point_id;
42 --output.put_line('the max score is = ' || l_max_score);
43
44 IF pg_debug = 'Y'
45 THEN
46 debug ( 'l_max_score : ' || l_max_score );
47 END IF;
48
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 p_resultout := 1;
52 p_error_msg := 'Unable to get Max. Score for Data Point '||
53 p_data_point_id;
54 return;
55 WHEN OTHERS THEN
56 p_resultout := 1;
57 p_error_msg := 'Fatal Error while getting Max. Score '|| sqlerrm;
58 return;
59
60 END;
61 IF pg_debug = 'Y'
62 THEN
63 debug ( 'Max Score ' ||l_max_score );
64 END IF;
65 -- in case score model or data point id does not exist in
66 -- score model details
67 IF l_max_score = 0
68 THEN
69 p_score := 0;
70 return;
71 END IF;
72
73 BEGIN
74 SELECT RETURN_DATA_TYPE,RETURN_DATE_FORMAT
75 INTO l_data_point_type,l_date_format
76 FROM AR_CMGT_SCORABLE_DATA_POINTS_V
77 WHERE DATA_POINT_ID = p_data_point_id;
78 --we don not need to check for scorable data points
79 --because the score model will contain only scorable
80 --data points.
81
82 IF pg_debug = 'Y'
83 THEN
84 debug ( 'l_data_point_type : ' || l_data_point_type );
85 debug ( 'l_date_format : ' || l_date_format );
86 END IF;
87
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90
91 p_error_msg := 'No return type and date format defined for data point' ||
92 'Data Point Id:' ||p_data_point_id ||' Data Point value: '||
93 p_data_point_value;
94 IF pg_debug = 'Y'
95 THEN
96 debug ( 'NO_DATA_FOUND --> ' ||p_error_msg );
97 END IF;
98 p_resultout := 1;
99 return;
100
101 WHEN OTHERS THEN
102 p_resultout := 1;
103 p_error_msg := sqlerrm;
104
105 IF pg_debug = 'Y' THEN
106 debug ( 'OTHERS --> ' ||p_error_msg );
107 END IF;
108 return;
109 END;
110
111 IF l_data_point_type = 'N'
112 THEN
113 BEGIN
114 -- kjoshi Changes for score model enhancement
115 --change for selecting 0 in case weight is null i.e. not assigned
116 -- IF the user has choosen convert the null value to zero.
117 IF p_data_point_value IS NULL
118 THEN
119
120 BEGIN
121
122 SELECT NULL_ZERO_FLAG
123 INTO NULL_ZERO_CONVR_IND
124 FROM AR_CMGT_SCORES
125 WHERE SCORE_MODEL_ID = p_score_model_id;
126
127 IF pg_debug = 'Y'
128 THEN
129 debug ( 'NULL_ZERO_CONVR_IND : ' || NULL_ZERO_CONVR_IND );
130 END IF;
131
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134
135 NULL_ZERO_CONVR_IND := 'N';
136
137 WHEN OTHERS THEN
138
139 p_resultout := 1;
140 p_error_msg := sqlerrm;
141 return;
142 END;
143
144 --convert to zero if data point value is null
145
146 IF pg_debug = 'Y'
147 THEN
148 debug ( 'NULL_ZERO_CONVR_IND ==> ' || NULL_ZERO_CONVR_IND );
149 END IF;
150
154
151 IF NULL_ZERO_CONVR_IND = 'Y'
152 AND p_data_point_value IS NULL
153 THEN
155 l_data_point_value := 0;
156
157 ELSIF NULL_ZERO_CONVR_IND = 'N'
158 THEN
159
160 l_data_point_value := p_data_point_value;
161
162 END IF;
163 ELSE
164
165 l_data_point_value := p_data_point_value;
166
167 END IF;
168
169 IF pg_debug = 'Y'
170 THEN
171 debug ( 'l_data_point_value : ' || l_data_point_value );
172 END IF;
173
174 -- Changed the value from 'l_data_point_value' to 'l_num_dp_value' in
175 -- the query.
176 SELECT score.scores, NVL(weight.weight,0)
177 INTO l_scores, l_weight
178 FROM ar_cmgt_score_dtls score,
179 AR_CMGT_SCORE_WEIGHTS weight
180 WHERE score.score_model_id = p_score_model_id
181 AND score.data_point_id = p_data_point_id
182 AND score.score_model_id = weight.score_model_id
183 AND score.data_point_id = weight.data_point_id
184 AND fnd_number.canonical_to_number(l_data_point_value) between
185 fnd_number.canonical_to_number(score.range_from) and fnd_number.canonical_to_number(score.range_to);
186
187 IF pg_debug = 'Y'
188 THEN
189 debug ( 'l_scores : ' || l_scores );
190 debug ( 'l_weight : ' || l_weight );
191 END IF;
192
193 --kjoshi Score model enhancement
194 --changes to evaluate diect score in the case where weights are not assigned.
195 IF l_weight = 0
196 THEN
197 p_score := l_scores;
198 END IF;
199
200 IF l_weight <> 0
201 THEN
202 p_score := round(((l_scores/l_max_score) *(l_weight)),0); -- Fix for Bug 10046101
203 END IF;
204
205
206 IF pg_debug = 'Y'
207 THEN
208 debug ( 'Number Value Score '||p_score );
209 END IF;
210
211 --output.put_line('score calculated = ' ||p_score);
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 p_error_msg := 'Number Data Point values are out of Score Range' ||
215 'Data Point Id:' ||p_data_point_id ||' Data Point value: '|| l_data_point_value;
216 p_score := null;
217 p_resultout := 1;
218 IF pg_debug = 'Y'
219 THEN
220 debug ('l_data_point_type ==> ' || l_data_point_type);
221 debug ( 'NO_DATA_FOUND ==> ' ||p_error_msg );
222 END IF;
223
224 WHEN OTHERS THEN
225 p_resultout := 1;
226 p_error_msg := sqlerrm;
227 IF pg_debug = 'Y'
228 THEN
229 debug ('l_data_point_type ==> ' || l_data_point_type);
230 debug ( 'OTHERS ==> ' ||p_error_msg );
231 END IF;
232
233 return;
234 END;
235 ELSIF l_data_point_type = 'D'
236 THEN
237 BEGIN
238
239 SELECT score.scores, NVL(weight.weight,0)
240 INTO l_scores, l_weight
241 FROM ar_cmgt_score_dtls score,
242 AR_CMGT_SCORE_WEIGHTS weight
243 WHERE score.score_model_id = p_score_model_id
244 AND score.data_point_id = p_data_point_id
245 AND score.score_model_id = weight.score_model_id
246 AND score.data_point_id = weight.data_point_id
247 AND to_date(p_data_point_value,l_date_format) between
248 to_date(score.range_from,l_date_format) AND
249 to_date(score.range_to,l_date_format);
250
251 IF pg_debug = 'Y'
252 THEN
253 debug ( 'l_scores : ' || l_scores );
254 debug ( 'l_weight : ' || l_weight );
255 END IF;
256
257 IF l_weight = 0
258 THEN
259 p_score := l_scores;
260 END IF;
261 IF l_weight <> 0
262 THEN
263 p_score := round(((l_scores/l_max_score) *(l_weight)),0); -- Fix for Bug 10046101
264 END IF;
265 IF pg_debug = 'Y'
266 THEN
267 debug ( 'date Value Score '||p_score );
268 END IF;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 p_error_msg := 'Date Data Point values are out of Score Range' ||
272 'Data Point Id:' ||p_data_point_id ||' Data Point value: '||
273 p_data_point_value;
274 p_score := null;
275 p_resultout := 1;
276
277 IF pg_debug = 'Y'
278 THEN
279 debug ('l_data_point_type ==> ' || l_data_point_type);
280 debug ( 'NO_DATA_FOUND ==> ' ||p_error_msg );
281 END IF;
282 WHEN OTHERS THEN
283 p_resultout := 1;
284 p_error_msg := sqlerrm;
285
286 IF pg_debug = 'Y'
287 THEN
288 debug ('l_data_point_type ==> ' || l_data_point_type);
289 debug ( 'OTHERS ==> ' ||p_error_msg );
290 END IF;
291 return;
292 END;
293 ELSIF l_data_point_type = 'C'
294 THEN
295 BEGIN
296
300 AR_CMGT_SCORE_WEIGHTS weight
297 SELECT score.scores, NVL(weight.weight,0)
298 INTO l_scores, l_weight
299 FROM ar_cmgt_score_dtls score,
301 WHERE score.score_model_id = p_score_model_id
302 AND score.data_point_id = p_data_point_id
303 AND score.score_model_id = weight.score_model_id
304 AND score.data_point_id = weight.data_point_id
305 AND p_data_point_value between score.range_from and score.range_to;
306
307 IF pg_debug = 'Y'
308 THEN
309 debug ( 'l_scores : ' || l_scores );
310 debug ( 'l_weight : ' || l_weight );
311 END IF;
312
313 IF l_weight = 0
314 THEN
315 p_score := l_scores;
316 END IF;
317 IF l_weight <> 0
318 THEN
319 p_score := round(((l_scores/l_max_score) *(l_weight)),0); -- Fix for Bug 10046101
320 END IF;
321 IF pg_debug = 'Y'
322 THEN
323 debug ( 'Char Value Score '||p_score );
324 END IF;
325 EXCEPTION
326 WHEN NO_DATA_FOUND THEN
327 p_error_msg := 'Char Data Point values are out of Score Range' ||
328 'Data Point Id:' ||p_data_point_id ||' Data Point value: '|| p_data_point_value;
329 p_resultout := 1;
330 p_score := null;
331 IF pg_debug = 'Y'
332 THEN
333 debug ('l_data_point_type ==> ' || l_data_point_type);
334 debug ( 'NO_DATA_FOUND ==> ' ||p_error_msg );
335 END IF;
336 WHEN OTHERS THEN
337 p_resultout := 1;
338 p_error_msg := sqlerrm;
339
340 IF pg_debug = 'Y'
341 THEN
342 debug ('l_data_point_type ==> ' || l_data_point_type);
343 debug ( 'OTHERS ==> ' ||p_error_msg );
344 END IF;
345
346 return;
347 END;
348 END IF;
349
350 --p_score := round(((l_scores/l_max_score) *(l_weight)),2);
351 IF pg_debug = 'Y'
352 THEN
353 debug ( 'In calculate Score (-)');
354 END IF;
355 END;
356
357 -- this is a wrapper for calculatescore. This function is getting called
358 -- from sql query to calculate score for individual data points.
359 FUNCTION get_score (
360 p_score_model_id IN NUMBER,
361 p_data_point_id IN NUMBER,
362 p_case_folder_id IN NUMBER,
363 p_data_point_value IN VARCHAR2)
364 return NUMBER IS
365 l_error_msg VARCHAR2(2000);
366 l_resultout VARCHAR2(1);
367 l_score NUMBER;
368 l_result VARCHAR2(1);
369 l_updt_flag VARCHAR2(1);
370 l_category VARCHAR2(20);
371 l_chk_list VARCHAR2(1);
372 l_function_name VARCHAR2(60);
373 l_skip_currency_flag VARCHAR2(1); -- For Bug 8627463
374
375 BEGIN
376 --this flag is for checking if the score calculated is for
377 --additional data point.
378 l_updt_flag :='Y';
379 IF pg_debug = 'Y'
380 THEN
381 debug ( 'In get Score (+)');
382 END IF;
383
384 BEGIN
385
386 -- If the Skip Currency Flag is set at the Scoring model, the case
387 -- folder limit currency and scoring model currency need not be same
388 -- So, we include the currency equality check only if the flag is not
389 -- set (Bug 8627463) -- Start
390 SELECT NVL(SKIP_CURRENCY_TEST_FLAG, 'N')
391 INTO l_skip_currency_flag
392 FROM AR_CMGT_SCORES
393 WHERE SCORE_MODEL_ID = p_score_model_id;
394
395 IF (l_skip_currency_flag = 'N') THEN
396 SELECT 'X'
397 INTO l_result
398 FROM ar_cmgt_scores score, ar_cmgt_case_folders case1
399 WHERE case1.case_folder_id = p_case_folder_id
400 AND score.score_model_id = p_score_model_id
401 AND case1.limit_currency = score.currency
402 AND trunc(sysdate) between trunc(score.start_date) and
403 nvl(trunc(score.end_date), trunc(sysdate));
404
405 IF pg_debug = 'Y' THEN
406 debug ( 'l_result '||l_result );
407 END IF;
408 ELSE
409 SELECT 'X'
410 INTO l_result
411 FROM ar_cmgt_scores score, ar_cmgt_case_folders case1
412 WHERE case1.case_folder_id = p_case_folder_id
413 AND score.score_model_id = p_score_model_id
414 AND trunc(sysdate) between trunc(score.start_date) and
415 nvl(trunc(score.end_date), trunc(sysdate));
416 END IF;
417 -- Bug 8627463 -- End
418
419 EXCEPTION
420 WHEN NO_DATA_FOUND
421 THEN
422 return NULL;
423 WHEN TOO_MANY_ROWS
424 THEN
425 NULL;
426 WHEN OTHERS THEN
427 return NULL;
428 END;
429
430 Calculate_score(
431 p_score_model_id => p_score_model_id,
432 p_data_point_id => p_data_point_id,
433 p_data_point_value => p_data_point_value,
434 p_score => l_score,
435 p_error_msg => l_error_msg,
436 p_resultout => l_resultout);
437
438 /* 9342120 - Added function_name directly to view */
439 SELECT v.data_point_category, v.function_name
440 INTO l_category, l_function_name
444 BEGIN
441 FROM ar_cmgt_scorable_data_points_v v
442 WHERE v.data_point_id = p_data_point_id;
443
445
446 SELECT INCLUDED_IN_CHECKLIST
447 INTO l_chk_list
448 FROM AR_CMGT_CF_DTLS
449 WHERE CASE_FOLDER_ID=p_case_folder_id
450 AND DATA_POINT_ID=p_data_point_id;
451 EXCEPTION
452
453 WHEN TOO_MANY_ROWS
454 THEN
455 return NULL;
456 END;
457
458 IF pg_debug = 'Y'
459 THEN
460 debug ( 'l_category '||l_category );
461 debug ( 'l_chk_list '||l_chk_list );
462 debug ( 'l_score '||l_score );
463 END IF;
464
465 IF l_category ='ADDITIONAL'
466 AND l_chk_list ='Y'
467 AND l_function_name IS NULL
468 THEN
469 l_updt_flag :='N';
470 END IF;
471
472
473 --only update the other data points
474 --i.e. 'ADDITIONAL' data points are
475 --not updated from PLSQL.
476
477 if l_updt_flag ='Y' THEN
478
479 Update ar_cmgt_cf_dtls
480 set data_point_value = p_data_point_value,
481 score = l_score
482 WHERE data_point_id = p_data_point_id
483 AND case_folder_id = p_case_folder_id;
484
485 end if;
486
487 return l_score;
488 /* IF l_score IS NULL
489 THEN
490 return l_score;
491 ELSE
492 return l_score;
493 END IF; */
494 IF pg_debug = 'Y'
495 THEN
496 debug ( 'In get Score (-)');
497 END IF;
498 END;
499
500
501
502 PROCEDURE get_dnb_data_point_value(
503 p_case_folder_id IN NUMBER,
504 p_data_point_id IN NUMBER,
505 p_data_point_value OUT NOCOPY VARCHAR2) IS
506
507 l_source_table_name ar_cmgt_dnb_elements_vl.source_table_name%type;
508 l_source_column_name ar_cmgt_dnb_elements_vl.source_column_name%type;
509 l_source_key ar_cmgt_cf_dnb_dtls.source_key%type;
510 l_source_key_type ar_cmgt_cf_dnb_dtls.source_key_type%type;
511 l_source_key_column_name ar_cmgt_cf_dnb_dtls.source_key_column_name%type;
512 l_source_key_column_type ar_cmgt_cf_dnb_dtls.source_key_column_type_name%type;
513
514 TYPE cur_type IS REF CURSOR;
515 c cur_type;
516
517 queryStr VARCHAR2(2000);
518 BEGIN
519 SELECT source_table_name, source_column_name
520 INTO l_source_table_name, l_source_column_name
521 FROM ar_cmgt_dnb_elements_vl
522 WHERE data_element_id = p_data_point_id;
523
524 SELECT cfd.source_key, cfd.source_key_type, cfd.source_key_column_name,
525 cfd.source_key_column_type_name
526 INTO l_source_key, l_source_key_type, l_source_key_column_name,
527 l_source_key_column_type
528 FROM ar_cmgt_cf_dnb_dtls cfd
529 WHERE cfd.case_folder_id = p_case_folder_id
530 AND cfd.source_table_name = l_source_table_name;
531
532 IF l_source_key_type IS NULL
533 THEN
534 --bug#5072562 changes start**************************************
535 --SQL ID 16039932
536 queryStr := 'SELECT '|| ':l_source_column_name' ||
537 ' FROM '|| ':l_source_table_name' ||
538 ' WHERE '|| ':l_source_key_column_name' || ' = :l_source_key';
539
540 OPEN c FOR queryStr USING l_source_column_name,l_source_table_name,l_source_key_column_name
541 ,l_source_key;
542 --bug#5072562 changes end****************************************
543 LOOP
544 FETCH c INTO p_data_point_value;
545 EXIT WHEN c%NOTFOUND;
546 END LOOP;
547 CLOSE c;
548 ELSE
549 --bug#5072562 changes start**************************************
550 --SQL ID 16039933
551 queryStr := 'SELECT '||':l_source_column_name' ||
552 ' FROM '|| ':l_source_table_name' ||
553 ' WHERE '|| ':l_source_key_column_name' || ' = :l_source_key '||
554 ' AND ' || ':l_source_key_column_type' ||' = || :l_source_key_type';
555
556 OPEN c FOR queryStr USING l_source_column_name,l_source_table_name, l_source_key_column_name,
557 l_source_key_column_type,l_source_key, l_source_key_type;
558 --bug#5072562 changes end****************************************
559
560 LOOP
561 FETCH c INTO p_data_point_value;
562 EXIT WHEN c%NOTFOUND;
563 END LOOP;
564 CLOSE c;
565 END IF;
566 EXCEPTION
567 WHEN no_data_found THEN
568 null;
569 WHEN others THEN
570 raise;
571 END;
572
573 PROCEDURE GET_TOTAL_SCORE(
574 p_case_folder_id IN NUMBER,
575 p_score_model_id IN NUMBER,
576 p_data_point_id IN NUMBER,
580
577 p_score OUT NOCOPY NUMBER,
578 p_error_msg OUT NOCOPY VARCHAR2,
579 p_resultout OUT NOCOPY VARCHAR2) IS
581 l_data_point_value ar_cmgt_cf_dtls.data_point_value%TYPE;
582 l_data_point_id ar_cmgt_data_points_vl.data_point_id%TYPE;
583 l_score_model_id ar_cmgt_scores.score_model_id%TYPE;
584 l_total_score NUMBER := 0;
585 l_score NUMBER := 0;
586 l_updt_flg VARCHAR2(1);
587 l_category VARCHAR2(20);
588 l_function_name VARCHAR2(60);
589 BEGIN
590 l_updt_flg:='Y';
591
592 /* 9342120 - Added function_name to view */
593 SELECT v.data_point_category, v.function_name
594 INTO l_category, l_function_name
595 FROM ar_cmgt_scorable_data_points_v v
596 where v.data_point_id = p_data_point_id;
597
598 IF l_category='ADDITIONAL'
599 AND l_function_name IS NULL
600 THEN
601 l_updt_flg :='N';
602 END IF;
603
604 IF pg_debug = 'Y'
605 THEN
606 debug ( 'GET_TOTAL_SCORE (+)');
607 END IF;
608
609 p_resultout := 0;
610 p_score := 0;
611 -- first get the data point id for Data records
612 IF g_data_case_folder_id IS NULL
613 THEN
614 BEGIN
615 SELECT data1.case_folder_id
616 INTO g_data_case_folder_id
617 FROM ar_cmgt_case_folders data1, ar_cmgt_case_folders case1
618 WHERE data1.type = 'DATA'
619 and case1.case_folder_id = p_case_folder_id
620 and case1.party_id = data1.party_id
621 and case1.cust_account_id = data1.cust_account_id
622 and case1.site_use_id = data1.site_use_id;
623
624 IF pg_debug = 'Y'
625 THEN
626 debug ( 'g_data_case_folder_id '||g_data_case_folder_id );
627 END IF;
628
629 -- update score for the data records to null. This is required
630 -- in because Scoreing model could be different for different
631 -- case folders for the same party account and site combination.
632 -- But the data records will be the same for the same combination.
633 -- So it would be idle to update score to null.
634 UPDATE ar_cmgt_cf_dtls
635 SET score = null,
636 last_updated_by = fnd_global.user_id, last_update_date = sysdate,
637 last_update_login = fnd_global.login_id
638 WHERE case_folder_id = g_data_case_folder_id;
639
640 EXCEPTION
641 WHEN NO_DATA_FOUND THEN
642 g_data_case_folder_id := -99;
643 WHEN OTHERS THEN
644 g_data_case_folder_id := -99;
645 END;
646 END IF;
647 /*IF p_data_point_id < 20000 -- all data points including DNB
648 THEN */
649 BEGIN
650 /* 9342120 - using scorable_data_points_v to
651 accomodate DNB datapoints */
652 SELECT cfd.data_point_value
653 INTO l_data_point_value
654 FROM ar_cmgt_scorable_data_points_v sdp,
655 ar_cmgt_cf_dtls cfd
656 WHERE cfd.case_folder_id = p_case_folder_id
657 AND sdp.data_point_id = cfd.data_point_id (+)
658 AND sdp.data_point_id = p_data_point_id;
659
660 IF pg_debug = 'Y'
661 THEN
662 debug ( 'l_data_point_value '||l_data_point_value );
663 END IF;
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 p_resultout := 1;
668 p_error_msg := 'Fatal Error While getting data point value '||
669 p_data_point_id;
670 IF pg_debug = 'Y'
671 THEN
672 debug ( p_error_msg );
673 END IF;
674
675 return;
676 END;
677
678 -- calling
679 Calculate_score(
680 p_score_model_id => p_score_model_id,
681 p_data_point_id => p_data_point_id,
682 p_data_point_value => l_data_point_value,
683 p_score => l_score,
684 p_error_msg => p_error_msg,
685 p_resultout => p_resultout );
686
687 IF pg_debug = 'Y'
688 THEN
689 debug ( 'l_score '||l_score );
690 debug ( 'p_resultout '||p_resultout );
691 END IF;
692
693 IF p_resultout = 0
694 THEN
695
696 /*Changes Start----------------------------------------------------------------
697 bug#5007954
698 This code is chanded to update only in case the data point category is not
699 ADDITIONAL.*/
700 IF l_updt_flg ='Y'
701 THEN
702 AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS
703 ( p_case_folder_id => p_case_folder_id,
704 p_data_point_id => p_data_point_id,
705 p_data_point_value => l_data_point_value,
706 p_score => l_score,
707 p_errmsg => p_error_msg,
708 p_resultout => p_resultout);
709 END IF;
710
711 /* Changes end------------------------------------------------------------------
712 * bug#5007954
713 */
714 -- update data records too
715 IF g_data_case_folder_id IS NOT NULL AND
716 g_data_case_folder_id <> -99
717 THEN
718 AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS
719 ( p_case_folder_id => g_data_case_folder_id,
720 p_data_point_id => p_data_point_id,
721 p_data_point_value => l_data_point_value,
722 p_score => l_score,
723 p_errmsg => p_error_msg,
724 p_resultout => p_resultout);
725 END IF;
726 p_score := p_score + l_score;
727
728 IF pg_debug = 'Y'
729 THEN
730 debug ( 'p_score '||p_score );
731 END IF;
732 ELSE
733 p_score := null;
734 return;
735 END IF;
736
737 IF pg_debug = 'Y'
738 THEN
739 debug ( 'GET_TOTAL_SCORE (-)');
740 END IF;
741 END;
742
743 /**********************************************************************
744 ** Scoring Formula
745 ** 1. Find out he Score for the value range
746 ** 2. Get the largest possible for that datapoints
747 ** 3. Divide the score with the largest score
748 ** 4. Repeat above steps for each data points
749 ** 5. Add all results of step 3 and multiply by 100.
750 **********************************************************************/
751 PROCEDURE GENERATE_SCORE(
752 p_case_folder_id IN NUMBER,
753 p_score OUT NOCOPY NUMBER,
754 p_error_msg OUT NOCOPY VARCHAR2,
755 p_resultout OUT NOCOPY VARCHAR2) IS
756
757
758 CURSOR cScoreDataPoint IS
759 SELECT distinct score.data_point_id, score.score_model_id
760 FROM ar_cmgt_score_dtls score,
761 ar_cmgt_case_folders case1
762 WHERE case_folder_id = p_case_folder_id
763 AND case1.score_model_id = score.score_model_id;
764 l_total_score NUMBER := 0;
765
766 BEGIN
767 IF pg_debug = 'Y'
768 THEN
769 debug ( 'GENERATE_SCORE Ist (+)');
770 END IF;
771 p_resultout := 0;
772 p_score := 0;
776 -- then the old score need to be updated with the new value.
773 -- update score for the case records to null. This is required
774 -- because Scoreing model can be changed by credit analyst during
775 -- analysis. In case credit analyst change the scoring model
777 -- Also the number of data points could vary from scoring model to
778 -- scoring model.
779 UPDATE ar_cmgt_cf_dtls
780 SET score = null,
781 last_updated_by = fnd_global.user_id,
782 last_update_date = sysdate,
783 last_update_login = fnd_global.login_id
784 WHERE case_folder_id = p_case_folder_id;
785
786 FOR cScoreDataPoint_rec IN cScoreDataPoint
787 LOOP
788
789 get_total_score(
790 p_case_folder_id => p_case_folder_id,
791 p_score_model_id => cScoreDataPoint_rec.score_model_id,
792 p_data_point_id => cScoreDataPoint_rec.data_point_id,
793 p_score => p_score,
794 p_error_msg => p_error_msg,
795 p_resultout => p_resultout);
796
797 IF pg_debug = 'Y'
798 THEN
799 debug ( 'Data Point id '|| cScoreDataPoint_rec.data_point_id);
800 debug ( 'Score '|| p_score);
801 END IF;
802 IF p_resultout <> 0
803 THEN
804 p_score := null;
805 return;
806 END IF;
807
808 l_total_score := l_total_score + nvl(p_score,0);
809 IF pg_debug = 'Y'
810 THEN
811 debug ( ' Total Score '|| l_total_score);
812 END IF;
813
814 END LOOP;
815 p_score := round(l_total_score,0);
816 IF pg_debug = 'Y'
817 THEN
818 debug ( ' Total Score '|| l_total_score);
819 debug ( 'GENERATE_SCORE Ist (-)');
820 END IF;
821 END;
822 /* This procedure is overloaded with Generate_score
823 At this moment this procedure is called from Case folder UI
824 in case CA wants to change the scoring model and generate the
825 new score for the case folder*/
826
827 PROCEDURE GENERATE_SCORE(
828 p_case_folder_id IN NUMBER,
829 p_score_model_id IN NUMBER,
830 p_score OUT NOCOPY NUMBER,
831 p_error_msg OUT NOCOPY VARCHAR2,
832 p_resultout OUT NOCOPY VARCHAR2) IS
833
834 CURSOR cScoreDataPoint IS
835 SELECT distinct score.data_point_id, score.score_model_id
836 FROM ar_cmgt_score_dtls score
837 WHERE score_model_id = p_score_model_id;
838
839 l_total_score NUMBER := 0;
840 l_result VARCHAR2(1);
841 l_skip_currency_flag VARCHAR2(1); -- For Bug 8627463
842 BEGIN
843 IF pg_debug = 'Y' THEN
844 debug ( 'GENERATE_SCORE 2nd (+)');
845 debug ( 'case Folder ID : ' || p_case_folder_id);
846 debug ( 'score model id : ' || p_score_model_id);
847 END IF;
848
849 p_resultout := 0;
850 p_score := 0;
851 --first check whether scoring currency and case folder
852 -- currency is same or not. In case it is different then
853 -- raise an error. Bug 3624543
854 BEGIN
855
856 -- If the Skip Currency Flag is set at the Scoring model, the case
857 -- folder limit currency and scoring model currency need not be same
858 -- So, we include the currency equality check only if the flag is not
859 -- set (Bug 8627463) -- Start
860 SELECT NVL(SKIP_CURRENCY_TEST_FLAG, 'N')
861 INTO l_skip_currency_flag
862 FROM AR_CMGT_SCORES
863 WHERE SCORE_MODEL_ID = p_score_model_id;
864
865 IF ( l_skip_currency_flag = 'N') THEN
866 SELECT 'X'
867 INTO l_result
868 FROM ar_cmgt_scores score, ar_cmgt_case_folders case1
869 WHERE case1.case_folder_id = p_case_folder_id
870 AND score.score_model_id = p_score_model_id
871 AND case1.limit_currency = score.currency
872 AND trunc(sysdate) between trunc(score.start_date) and
873 nvl(trunc(score.end_date), trunc(sysdate));
874 ELSE
875 SELECT 'X'
876 INTO l_result
877 FROM ar_cmgt_scores score, ar_cmgt_case_folders case1
878 WHERE case1.case_folder_id = p_case_folder_id
879 AND score.score_model_id = p_score_model_id
880 AND trunc(sysdate) between trunc(score.start_date) and
881 nvl(trunc(score.end_date), trunc(sysdate));
882 END IF;
883 -- (Bug 8627463) -- End
884
885
886 EXCEPTION
887 WHEN NO_DATA_FOUND
888 THEN
889 p_resultout := 1;
890 return;
891 WHEN TOO_MANY_ROWS
892 THEN
893 NULL;
894 WHEN OTHERS THEN
895 p_resultout := 1;
896 return;
897 END;
898 -- update score for the case records to null. This is required
899 -- because Scoreing model can be changed by credit analyst during
900 -- analysis. In case credit analyst change the scoring model
901 -- then the old score need to be updated with the new value.
902 -- Also the number of data points could vary from scoring model to
903 -- scoring model.
904 /*Changes Start----------------------------------------------------------------
905 * bug#5007954
906 UPDATE ar_cmgt_cf_dtls
907 SET score = null,
908 last_updated_by = fnd_global.user_id,
909 last_update_date = sysdate,
910 last_update_login = fnd_global.login_id
911 WHERE case_folder_id = p_case_folder_id;
912 * Changes end------------------------------------------------------------------
913 * bug#5007954
914 */
915 -- update the scoring model Id in case folder table
916 /* UPDATE ar_cmgt_case_folders
917 set score_model_id = p_score_model_id,
918 last_updated = SYSDATE,
919 last_update_date = sysdate,
920 last_updated_by = fnd_global.user_id,
921 last_update_login = fnd_global.login_id
922 WHERE case_folder_id = p_case_folder_id; */
923
924 FOR cScoreDataPoint_rec IN cScoreDataPoint
925 LOOP
926 get_total_score(
927 p_case_folder_id => p_case_folder_id,
928 p_score_model_id => cScoreDataPoint_rec.score_model_id,
929 p_data_point_id => cScoreDataPoint_rec.data_point_id,
930 p_score => p_score,
931 p_error_msg => p_error_msg,
932 p_resultout => p_resultout);
933
934 IF pg_debug = 'Y'
935 THEN
936 debug ( 'Data Point id '|| cScoreDataPoint_rec.data_point_id);
937 debug ( 'Score '|| p_score);
938 END IF;
939 IF p_resultout <> 0
940 THEN
941 p_score := null;
942 return;
943 END IF;
944 l_total_score := l_total_score + nvl(p_score,0);
945
946 END LOOP;
947 p_score := round(l_total_score,0);
948 IF pg_debug = 'Y'
949 THEN
950 debug ( 'Total score : ' || p_score);
951 debug ( 'GENERATE_SCORE 2nd (-)');
952 END IF;
953 END;
954
955 END AR_CMGT_SCORING_ENGINE;
[Home] [Help]