[Home] [Help]
PACKAGE BODY: APPS.IBY_FREQ_OF_PURCH_PKG
Source
1 PACKAGE BODY iby_freq_of_purch_pkg AS
2 /*$Header: ibyfopb.pls 120.3.12000000.2 2007/09/06 09:55:39 lmallick ship $*/
3
4 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.iby_freq_of_purch_pkg';
5
6 PROCEDURE eval_factor
7 (
8 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
9 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
10 i_instrid IN iby_trxn_summaries_all.payerinstrid%TYPE,
11 i_ccNumber IN iby_trxn_summaries_all.instrnumber%TYPE,
12 i_master_key in iby_payee.master_key%TYPE,
13 o_score out NOCOPY integer
14 )
15 IS
16
17 l_payeeid varchar2(80);
18 l_fromDate date;
19 l_count integer;
20 l_max_purchases int;
21 l_no_of_purchases int;
22 l_purchases_counter INT;
23 l_duration int;
24 l_duration_type varchar2(10);
25 l_ccnum_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
26 l_ccnum_obfs iby_trxn_summaries_all.instrnumber%TYPE;
27
28 CURSOR c_get_config(ci_payeeid varchar2) IS
29 SELECT duration, duration_type, frequency
30 FROM iby_irf_pmt_frequency
31 WHERE ( payeeid = ci_payeeid )
32 OR ( payeeid is null and ci_payeeid is null);
33 --
34 -- get frequency of purchase for all
35 -- credit card payment instruments in any (encoded or
36 -- hard-encrypted) forms
37 --
38
39 CURSOR c_get_fop_instrnum(
40 ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
41 ci_ccNumber varchar2,
42 ci_ccNumHash iby_trxn_summaries_all.instrnum_hash%TYPE,
43 ci_fromDate date
44 ) IS
45 SELECT count(1)
46 FROM iby_trxn_summaries_all tx
47 WHERE ((ci_ccNumber = instrNumber) OR (ci_ccNumHash = instrnum_hash))
48 AND reqdate >= ci_fromDate
49 AND reqType = 'ORAPMTREQ'
50 AND (status IN (0,11,100,111))
51 AND payeeid = ci_payeeid;
52
53 CURSOR c_get_fop_instrid(
54 ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
55 ci_instrid int,
56 ci_fromDate date
57 ) IS
58 SELECT count(1)
59 FROM iby_trxn_summaries_all tx
60 WHERE (ci_instrid = payerInstrid)
61 AND reqdate >= ci_fromDate
62 AND reqType = 'ORAPMTREQ'
63 AND (status IN (0,11,100,111))
64 AND payeeid = ci_payeeid;
65
66 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.eval_factor';
67
68
69 BEGIN
70 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
71
72 if ( c_get_fop_instrnum%isopen ) then
73 close c_get_fop_instrnum;
74 end if;
75 if ( c_get_fop_instrid%isopen ) then
76 close c_get_fop_instrid;
77 end if;
78
79
80 /*
81 ** Check if payee has any configuration in
82 ** freq of purchase table.
83 ** otherwise set l_payeeid to null, so that
84 ** site level configuration will be retrieved.
85 */
86
87 select count(1) into l_count
88 from iby_irf_pmt_frequency
89 where payeeid = i_payeeid;
90
91 if ( l_count = 0 ) then
92 l_payeeid := null;
93 else
94 l_payeeid := i_payeeid;
95 end if;
96
97 /*
98 ** get the duration information;
99 */
100
101 if ( c_get_config%isopen )then
102 close c_get_config;
103 end if;
104
105 open c_get_config(l_payeeid);
106 fetch c_get_config into l_duration, l_duration_type, l_max_purchases;
107 if ( c_get_config%notfound ) then
108 close c_Get_config;
109 raise_application_error(-20000, 'IBY_204230#');
110 end if;
111
112 /*
113 ** get the freq of purchase for the creditcard
114 ** in the duration mentioned.
115 */
116
117 l_fromDate := sysdate;
118 if ( l_duration_type = 'D' ) then
119 l_fromDate := l_fromDate - l_duration;
120 elsif ( l_duration_type = 'W' ) then
121 l_fromDate := l_fromDate - (l_duration * 7);
122 elsif ( l_duration_type = 'M' ) then
123 l_fromDate := add_months(l_fromDate, (-1 * l_duration));
124 end if;
125
126 if (NOT i_ccNumber IS NULL) then
127 l_ccnum_hash := iby_security_pkg.get_hash(i_ccNumber,'F');
128 l_ccnum_obfs := iby_utility_pvt.encode64(i_ccNumber);
129 end if;
130
131 iby_debug_pub.add('earliest from date:=' || TO_CHAR(l_fromDate),
132 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
133
134
135 /*
136 ** select the number of purchases after "fromDate" value
137 */
138
139 l_no_of_purchases := 0;
140 IF (NOT i_instrid IS NULL) THEN
141 OPEN c_get_fop_instrid(i_payeeid,i_instrid,l_fromdate);
142 FETCH c_get_fop_instrid into l_purchases_counter;
143 CLOSE c_get_fop_instrid;
144
145 l_no_of_purchases := l_purchases_counter + l_no_of_purchases;
146 iby_debug_pub.add('matching instr id cnt:=' || l_purchases_counter,
147 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
148 END IF;
149
150 IF (NOT i_ccNumber IS NULL) THEN
151 OPEN c_get_fop_instrnum(i_payeeid,i_ccNumber,l_ccnum_hash,l_fromdate);
152 FETCH c_get_fop_instrnum into l_purchases_counter;
153 CLOSE c_get_fop_instrnum;
154
155 l_no_of_purchases := l_purchases_counter + l_no_of_purchases;
156 iby_debug_pub.add('matching instr id cnt:=' || l_purchases_counter,
157 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
158 END IF;
159
160 iby_debug_pub.add('max purchases:=' || l_max_purchases,
161 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
162
163 if ( l_no_of_purchases >= l_max_purchases ) then
164 /*
165 ** get the value for high risk and return
166 */
167 o_score := iby_risk_scores_pkg.getScore(i_payeeid, 'H');
168 else
169 o_score := 0;
170 end if;
171
172 end eval_factor;
173
174 end iby_freq_of_purch_pkg;
175