1 package body iby_avs_code_pkg as
2 /*$Header: ibyavscb.pls 120.1.12010000.2 2009/08/07 12:58:18 sugottum ship $*/
3
4
5 procedure eval_factor( i_payeeid in varchar2,
6 i_avs_code in varchar2,
7 o_score out nocopy integer )
8 is
9
10 l_score varchar2(10);
11 l_cnt integer;
12 l_payeeid varchar2(80);
13
14 -- Bug# 8768305
15 -- There can be multiple mapping codes for a particular payeeid + mapping_type
16 -- combination..So, included INSTR function to handle this scenario
17 -- If INSTR is not used and if there are mutliple values defined for mapping_code
18 -- then value would be always null
19 cursor c_get_factor_config(ci_payeeid varchar2, ci_avs_code varchar2)
20 is
21 select value
22 from iby_mappings
23 where ( ( payeeid is null and ci_payeeid is null ) or
24 payeeid = ci_payeeid)
25 and mapping_type = 'AVS_CODE_TYPE'
26 and INSTR(mapping_code, ci_avs_code, 1)>0;
27
28 begin
29
30 /*
31 ** check whether this payeeid has any entry in
32 ** for AVScodes.
33 ** if not the set payeeid to null.
34 */
35
36 select count(1) into l_cnt
37 from iby_mappings
38 where mapping_type = 'AVS_CODE_TYPE'
39 and payeeid = i_payeeid;
40
41 if ( l_cnt = 0 ) then
42 l_payeeid := null;
43 else
44 l_payeeid := i_payeeid;
45 end if;
46
47 -- close the cursor if it already open.
48 if ( c_get_factor_config%isopen ) then
49 close c_get_factor_config;
50 end if;
51
52 open c_get_factor_config(l_payeeid, i_avs_code);
53 -- fetch the values
54 fetch c_get_factor_config into l_score;
55 -- if avscode is not present then assign norisk value
56 -- otherwise get the corresponding value by calling
57 -- iby_risk_scores_pkg.getScore method.
58 if ( c_get_factor_config%notfound) then
59 o_score := 0;
60 else
61 o_score := iby_risk_scores_pkg.getScore(i_payeeid, l_score);
62 end if;
63 close c_get_factor_config;
64
65 end eval_factor;
66
67 end iby_avs_code_pkg;
[Home] [Help]