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