[Home] [Help]
PACKAGE BODY: APPS.IBY_PMT_HISTORY_PKG
Source
1 package body iby_pmt_history_pkg as
2 /*$Header: ibypmthb.pls 120.3.12000000.2 2007/09/06 09:54:36 lmallick ship $*/
3 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.iby_pmt_history_pkg';
4 PROCEDURE eval_factor
5 (
6 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
7 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
8 i_payerid IN iby_trxn_summaries_all.payerid%TYPE,
9 i_instrid IN iby_trxn_summaries_all.payerinstrid%TYPE,
10 i_ccNumber IN iby_trxn_summaries_all.instrnumber%TYPE,
11 i_master_key IN iby_payee.master_key%TYPE,
12 o_score OUT NOCOPY INTEGER
13 )
14 IS
15
16 l_fromDate date;
17 l_count integer;
18 l_duration integer;
19 l_duration_type varchar2(10);
20 l_pmt_hist_id integer;
21 l_not_found boolean;
22 l_score varchar2(10);
23 l_payeeid varchar2(80);
24 l_no_of_purchases integer;
25 l_purchases_counter INT;
26 l_ccnum_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
27 l_ccnum_obfs iby_trxn_summaries_all.instrnumber%TYPE;
28
29 cursor c_get_ranges(ci_pmt_hist_id integer) is
30 select frequency_low_range lower_limit,
31 frequency_high_range upper_limit, score
32 from iby_irf_pmt_hist_range
33 where payment_hist_id = ci_pmt_hist_id
34 order by seq;
35
36 cursor c_get_config(ci_payeeid varchar2) is
37 select duration, duration_type, id
38 from iby_irf_pmt_history
39 where ( payeeid is null and ci_payeeid is null )
40 or (payeeid = ci_payeeid);
41
42 --
43 -- divide payment history count query into 3 independent queries
44 -- to avoid expensive, unnecessary queries
45 --
46 CURSOR c_get_history_instrnum
47 (
48 ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
49 ci_ccNum iby_trxn_summaries_all.instrnumber%TYPE,
50 ci_ccNumHash iby_trxn_summaries_all.instrnum_hash%TYPE,
51 ci_fromDate iby_trxn_summaries_all.reqdate%TYPE
52 )
53 IS
54 SELECT count(1)
55 FROM iby_trxn_summaries_all
56 WHERE ((instrnumber = ci_ccNum) OR (instrnum_hash = ci_ccNumHash))
57 AND reqdate >= ci_fromDate
58 AND reqtype = 'ORAPMTREQ'
59 AND (status IN (0,11,10,111))
60 AND (payeeid = ci_payeeid);
61
62 CURSOR c_get_history_payer
63 (
64 ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
65 ci_payerid iby_trxn_summaries_all.payerid%TYPE,
66 ci_fromDate iby_trxn_summaries_all.reqdate%TYPE
67 )
68 IS
69 SELECT count(1)
70 FROM iby_trxn_summaries_all
71 WHERE (payerid = ci_payerid)
72 AND reqdate >= ci_fromDate
73 AND reqtype = 'ORAPMTREQ'
74 AND (status IN (0,11,10,111))
75 AND (payeeid = ci_payeeid);
76
77 CURSOR c_get_history_payerinstr
78 (
79 ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
80 ci_instrid iby_trxn_summaries_all.payerinstrid%TYPE,
81 ci_fromDate iby_trxn_summaries_all.reqdate%TYPE
82 )
83 IS
84 SELECT count(1)
85 FROM iby_trxn_summaries_all
86 WHERE (payerinstrid = ci_instrid)
87 AND reqdate >= ci_fromDate
88 AND reqtype = 'ORAPMTREQ'
89 AND (status IN (0,11,10,111))
90 AND (payeeid = ci_payeeid);
91
92 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.eval_factor';
93
94
95 begin
96 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
97
98 if ( c_get_history_instrnum%isopen ) then
99 close c_get_history_instrnum;
100 end if;
101 if ( c_get_history_payer%isopen ) then
102 close c_get_history_payer;
103 end if;
104 if ( c_get_history_payerinstr%isopen ) then
105 close c_get_history_payerinstr;
106 end if;
107
108
109 select count(1) into l_count
110 from iby_irf_pmt_history
111 where payeeid = i_payeeid;
112
113 if ( l_count = 0 ) then
114 l_payeeid := null;
115 else
116 l_payeeid := i_payeeid;
117 end if;
118
119 /*
120 ** get Payment history configuration information.
121 */
122
123 if ( c_get_config%isopen ) then
124 close c_get_config;
125 end if;
126
127 open c_get_config(l_payeeid);
128 fetch c_get_config into l_duration, l_duration_type, l_pmt_hist_id;
129 if ( c_get_config%notfound) then
130 close c_get_config;
131 raise_application_error(-20000, 'IBY_204234#');
132 end if;
133 close c_get_config;
134
135 l_fromDate := sysdate;
136 if ( l_duration_type = 'D' ) then
137 l_fromDate := l_fromDate - l_duration;
138 elsif ( l_duration_type = 'W' ) then
139 l_fromDate := l_fromDate - (l_duration * 7);
140 elsif ( l_duration_type = 'M' ) then
141 l_fromDate := add_months(l_fromDate, (-1 * l_duration));
142 elsif ( l_duration_type = 'Y' ) then
143 l_fromDate := add_months(l_fromDate, (-1 * (l_duration * 12)));
144 end if;
145
146 if (NOT i_ccNumber IS NULL) then
147 l_ccnum_hash := iby_security_pkg.get_hash(i_ccNumber,'F');
148 l_ccnum_obfs := iby_utility_pvt.encode64(i_ccNumber);
149 end if;
150 iby_debug_pub.add('earliest from date:=' || TO_CHAR(l_fromDate),
151 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
152
153
154 /*
155 ** get the payment history information of either
156 ** based on payerId or CC Number.
157 */
158
159 l_no_of_purchases := 0;
160 IF (NOT i_payerid IS NULL) THEN
161 OPEN c_get_history_payer
162 (i_payeeid,i_payerid,l_fromdate);
163 FETCH c_get_history_payer into l_purchases_counter;
164 CLOSE c_get_history_payer;
165 l_no_of_purchases := l_purchases_counter + l_no_of_purchases;
166 iby_debug_pub.add('matching payer count:=' || l_purchases_counter,
167 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
168 END IF;
169
170 IF (NOT i_instrid IS NULL) THEN
171 OPEN c_get_history_payerinstr
172 (i_payeeid,i_instrid,l_fromdate);
173 FETCH c_get_history_payerinstr into l_purchases_counter;
174 CLOSE c_get_history_payerinstr;
175
176 l_no_of_purchases := l_purchases_counter + l_no_of_purchases;
177 iby_debug_pub.add('matching pyr instr cnt:=' || l_purchases_counter,
178 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
179 END IF;
180
181 IF (NOT i_ccNumber IS NULL) THEN
182 OPEN c_get_history_instrnum
183 (i_payeeid,i_ccNumber,l_ccnum_hash,l_fromDate);
184 FETCH c_get_history_instrnum into l_purchases_counter;
185 CLOSE c_get_history_instrnum;
186
187 l_no_of_purchases := l_purchases_counter + l_no_of_purchases;
188 iby_debug_pub.add('matching cc_num cnt:=' || l_purchases_counter,
189 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
190 END IF;
191
192
193 /*
194 ** compare the history with the configured value
195 ** and reture appropriate score.
196 */
197
198 l_not_found := true;
199
200 if ( c_get_ranges%isopen ) then
201 close c_get_ranges;
202 end if;
203
204 <<l_ranges_loop>>
205 for i in c_get_ranges(l_pmt_hist_id) loop
206 if ( ( ( i.lower_limit = -1 ) or
207 ( i.lower_limit <= l_no_of_purchases ) )
208 and ( ( i.upper_limit = -1 ) or
209 ( l_no_of_purchases < i.upper_limit ) ) )
210 then
211 l_not_found := false;
212 l_score := i.score;
213 exit l_ranges_loop;
214 end if;
215 end loop l_ranges_loop;
216
217 if ( l_not_found ) then
218 raise_application_error(-20000, 'IBY_204235#');
219 end if;
220
221 o_score := iby_risk_scores_pkg.getScore(i_payeeid, l_score);
222 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
223
224 end eval_factor;
225
226 end iby_pmt_history_pkg;
227