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