[Home] [Help]
1 package body iby_risk_scores_pkg as
2 /*$Header: ibyriskb.pls 120.2 2005/10/30 05:50:42 appldev ship $*/
3
4
5 function getScore( i_payeeid in varchar2,
6 i_score in varchar2 )
7 return integer
8 is
9
10 l_score integer;
11
12 cursor c_getscore(ci_payeeid in varchar2,
13 ci_code in varchar2)
14 is
15 select value
16 from iby_mappings
17 where ( ( payeeid is null and ci_payeeid is null ) or
18 payeeid = ci_payeeid )
19 and mapping_type = 'IBY_RISK_SCORE_TYPE'
20 and mapping_code = ci_code;
21
22 begin
23
24 -- initialize the variables
25 l_score := 0;
26
27 -- if cursor is already open then close the connection.
28 --
29 if ( c_getScore%isopen ) then
30 close c_getScore;
31 end if;
32
33 -- open the cursor.
34 open c_getScore(i_payeeid, i_score);
35 fetch c_getScore into l_score;
36 -- if values are not found then try to get
37 -- the default values by passing payeeid as null to
38 -- the cursor.
39 if ( c_getScore%notfound ) then
40 close c_getScore;
41 -- bug 4107078. The default payee id has been changed to -99
42 open c_getScore('-99', i_score);
43 fetch c_getScore into l_score;
44 -- if cursor does not return any thing then raise exception.
45 if ( c_getScore%notfound ) then
46 close c_getScore;
47 raise_application_error(-20000, 'IBY_204236#CODE#'
48 || i_score || '#');
49 end if;
50 end if;
51 -- close the cursor in case everything goes smooth.
52 close c_getScore;
53 return l_score;
54 end getScore;
55
56 end iby_risk_scores_pkg;
57
|
|
|
|