[Home] [Help]
PACKAGE BODY: APPS.IBY_RISKYINSTR_PKG
Source
1 package body iby_riskyinstr_pkg as
2 /*$Header: ibyrkinb.pls 120.2 2008/07/14 10:50:04 sugottum ship $*/
3
4
5 procedure delete_allRiskyInstr
6 is
7 begin
8 delete from iby_irf_risky_instr;
9 commit;
10 end;
11
12 /*
13 ** Procedure: add_RiskyInstr
14 ** Purpose: Appends/Adds the vector of RiskyInstr into the table. For
15 ** each risky instrument, if it matches (payeeid,instrtype,and numbers)
16 ** then does nothing, else adds it to table
17 */
18 procedure add_RiskyInstr (i_count in integer,
19 i_riskyinstr in RiskyInstr_Table,
20 o_results out nocopy Result_Table)
21 is
22 i int;
23 l_payeeid varchar2(80);
24 l_instrtype varchar2(80);
25 l_payeecount int;
26 l_instypecount int;
27 l_riskinscount int;
28 lx_cc_number iby_creditcard.ccnumber%TYPE;
29 lx_return_status VARCHAR2(1);
30 lx_msg_count NUMBER;
31 lx_msg_data VARCHAR2(200);
32 l_cc_hash1 iby_irf_risky_instr.cc_number_hash1%TYPE;
33 l_cc_hash2 iby_irf_risky_instr.cc_number_hash2%TYPE;
34 l_cc_number iby_creditcard.ccnumber%TYPE;
35 l_account_no_hash1 iby_irf_risky_instr.acct_number_hash1%TYPE;
36 l_account_no_hash2 iby_irf_risky_instr.acct_number_hash2%TYPE;
37 begin
38 -- initialize the values.
39 i := 1;
40 --dbms_output.put_line('at beginning');
41
42 -- loop through the list of ranges passed and update
43 -- the database.
44 while ( i <= i_count ) loop
45 -- extract the values fromt the database.
46 l_payeeid := i_riskyinstr(i).PayeeID;
47 l_instrtype := i_riskyinstr(i).InstrType;
48
49 SELECT COUNT(-1) INTO l_payeecount
50 FROM iby_payee
51 WHERE payeeid = l_payeeid;
52
53 SELECT COUNT(-1) INTO l_instypecount
54 FROM fnd_lookups
55 WHERE lookup_type = 'IBY_INSTRUMENT_TYPES'
56 and lookup_code = l_instrtype;
57
58 IF ( l_payeecount <> 1 ) THEN
59 o_results(i).success := 0;
60 o_results(i).errmsg := 'IBY_204260';
61 ELSIF (l_instypecount <> 1) then
62 o_results(i).success := 0;
63 o_results(i).errmsg := 'IBY_204261';
64 ELSIF (l_instrtype = 'CREDITCARD' and
65 (i_riskyinstr(i).CreditCard_Num is null or
66 i_riskyinstr(i).CreditCard_Num = '' )) then
67 o_results(i).success := 0;
68 o_results(i).errmsg := 'IBY_204262';
69 ELSIF (l_instrtype = 'BANKACCOUNT' and
70 (i_riskyinstr(i).Routing_Num is null or
71 i_riskyinstr(i).Routing_Num = '' or
72 i_riskyinstr(i).Account_Num is null or
73 i_riskyinstr(i).Account_Num = '')) then
74 o_results(i).success := 0;
75 o_results(i).errmsg := 'IBY_204263';
76 ELSE
77 IF ( l_instrtype = 'CREDITCARD' ) then
78 -- Added for bug# 7228388
79 -- Strip the symbols
80 IBY_CC_VALIDATE.StripCC
81 (1.0, FND_API.G_FALSE, i_riskyinstr(i).CreditCard_Num,
82 IBY_CC_VALIDATE.c_FillerChars,
83 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number);
84 -- Get hash values of the credit number
85 l_cc_hash1 := iby_security_pkg.get_hash
86 (lx_cc_number,FND_API.G_FALSE);
87 l_cc_hash2 := iby_security_pkg.get_hash
88 (lx_cc_number,FND_API.G_TRUE);
89 SELECT COUNT(-1) INTO l_riskinscount
90 FROM iby_irf_risky_instr
91 WHERE payeeid = l_payeeid
92 and instrtype = l_instrtype
93 and cc_number_hash1 = l_cc_hash1
94 and cc_number_hash2 = l_cc_hash2;
95 IF ( l_riskinscount = 0 ) then
96 -- Included hash1 and hash2 values as part of bug#7228388
97 insert into iby_irf_risky_instr
98 (payeeid, instrtype,
99 creditcard_no, object_version_number,
100 last_update_date, last_updated_by,
101 creation_date, created_by, cc_number_hash1,
102 cc_number_hash2)
103 values ( l_payeeid, l_instrtype,
104 null,
105 1, sysdate, fnd_global.user_id,
106 sysdate, fnd_global.user_id, l_cc_hash1, l_cc_hash2);
107 o_results(i).success := 1;
108 if ( SQL%ROWCOUNT = 0 ) then
109 -- raise application error for the range it has failed.
110 o_results(i).success := 0;
111 o_results(i).errmsg := 'IBY_204264';
112 end if;
113 ELSE
114 o_results(i).success := 0;
115 o_results(i).errmsg := 'IBY_204265';
116 END IF;
117
118
119 ELSIF ( l_instrtype = 'BANKACCOUNT') then
120 -- Get the hash values of the account number
121 l_account_no_hash1 := iby_security_pkg.get_hash
122 (i_riskyinstr(i).Account_Num,FND_API.G_FALSE);
123 l_account_no_hash2 := iby_security_pkg.get_hash
124 (i_riskyinstr(i).Account_Num,FND_API.G_TRUE);
125 SELECT COUNT(-1) INTO l_riskinscount
126 FROM iby_irf_risky_instr
127 WHERE payeeid = l_payeeid
128 and instrtype = l_instrtype
129 and routing_no = i_riskyinstr(i).Routing_Num
130 and acct_number_hash1 = l_account_no_hash1
131 and acct_number_hash2 = l_account_no_hash2;
132
133 IF ( l_riskinscount = 0 ) then
134 insert into iby_irf_risky_instr
135 (payeeid, instrtype, routing_no,
136 account_no, object_version_number,
137 last_update_date, last_updated_by,
138 creation_date, created_by,acct_number_hash1,
139 acct_number_hash2)
140 values ( l_payeeid, l_instrtype,
141 i_riskyinstr(i).Routing_Num,
142 null,
143 1, sysdate, fnd_global.user_id,
144 sysdate, fnd_global.user_id,l_account_no_hash1,
145 l_account_no_hash2);
146 o_results(i).success := 1;
147 if ( SQL%ROWCOUNT = 0 ) then
148 -- raise application error for the range it has failed.
149 o_results(i).success := 0;
150 o_results(i).errmsg := 'IBY_204264';
151 end if;
152 ELSE
153 o_results(i).success := 0;
154 o_results(i).errmsg := 'IBY_204265';
155 END IF;
156 END IF;
157 END IF;
161 end;
158 i := i +1;
159 end loop;
160 commit;
162
163 /*
164 ** Procedure: delete_RiskyInstr
165 ** Purpose: Delete the vector of RiskyInstr into the table. For
166 ** each risky instrument, if it matches (payeeid,instrtype,and numbers)
167 ** then delete the entry from table, else does nothing
168 */
169 procedure delete_RiskyInstr (i_count in integer,
170 i_riskyinstr in RiskyInstr_Table,
171 o_results out nocopy Result_Table)
172 is
173 i int;
174 l_payeeid varchar2(80);
175 l_instrtype varchar2(80);
176 l_payeecount int;
177 l_instypecount int;
178 l_riskinscount int;
179 lx_cc_number iby_creditcard.ccnumber%TYPE;
180 lx_return_status VARCHAR2(1);
181 lx_msg_count NUMBER;
182 lx_msg_data VARCHAR2(200);
183 l_cc_hash1 iby_irf_risky_instr.cc_number_hash1%TYPE;
184 l_cc_hash2 iby_irf_risky_instr.cc_number_hash2%TYPE;
185 l_cc_number iby_creditcard.ccnumber%TYPE;
186 l_account_no_hash1 iby_irf_risky_instr.acct_number_hash1%TYPE;
187 l_account_no_hash2 iby_irf_risky_instr.acct_number_hash2%TYPE;
188 begin
189 -- initialize the values.
190 i := 1;
191
192 -- loop through the list of ranges passed and update
193 -- the database.
194 while ( i <= i_count ) loop
195 -- extract the values from the database.
196 l_payeeid := i_riskyinstr(i).PayeeID;
197 l_instrtype := i_riskyinstr(i).InstrType;
198
199 SELECT COUNT(-1) INTO l_payeecount
200 FROM iby_payee
201 WHERE payeeid = l_payeeid;
202
203 SELECT COUNT(-1) INTO l_instypecount
204 FROM fnd_lookups
205 WHERE lookup_type = 'IBY_INSTRUMENT_TYPES'
206 and lookup_code = l_instrtype;
207
208 IF (l_payeecount <> 1) then
209 o_results(i).success := 0;
210 o_results(i).errmsg := 'IBY_204260';
211 ELSIF (l_instypecount <> 1) then
212 o_results(i).success := 0;
213 o_results(i).errmsg := 'IBY_204261';
214 ELSE
215 IF (l_instrtype = 'CREDITCARD') then
216 -- Included hash1 and hash2 values as part of bug#7228388
217 -- Strip symbols from the credit card, if any
218 IBY_CC_VALIDATE.StripCC
219 (1.0, FND_API.G_FALSE, i_riskyinstr(i).CreditCard_Num,
220 IBY_CC_VALIDATE.c_FillerChars,
221 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number);
222 -- Get hash values of the credit number
223 l_cc_hash1 := iby_security_pkg.get_hash
224 (lx_cc_number,FND_API.G_FALSE);
225 l_cc_hash2 := iby_security_pkg.get_hash
226 (lx_cc_number,FND_API.G_TRUE);
227 Delete FROM iby_irf_risky_instr
228 WHERE payeeid = l_payeeid
229 and instrtype = l_instrtype
230 and cc_number_hash1 = l_cc_hash1
231 and cc_number_hash2 = l_cc_hash2;
232 o_results(i).success := 1;
233 if ( SQL%NOTFOUND ) then
234 o_results(i).success := 0;
235 o_results(i).errmsg := 'IBY_204266';
236 end if;
237 ELSIF ( l_instrtype = 'BANKACCOUNT' ) then
238 -- Included hash1 and hash2 values as part of bug#7228187
239 -- Get hash values of the account number
240 l_account_no_hash1 := iby_security_pkg.get_hash
241 (i_riskyinstr(i).Account_Num,FND_API.G_FALSE);
242 l_account_no_hash2 := iby_security_pkg.get_hash
243 (i_riskyinstr(i).Account_Num,FND_API.G_TRUE);
244 Delete FROM iby_irf_risky_instr
245 WHERE payeeid = l_payeeid
246 and instrtype = l_instrtype
247 and routing_no = i_riskyinstr(i).Routing_Num
248 and acct_number_hash1 = l_account_no_hash1
249 and acct_number_hash2 = l_account_no_hash2;
250 o_results(i).success := 1;
251 if ( SQL%NOTFOUND ) then
252 o_results(i).success := 0;
253 o_results(i).errmsg := 'IBY_204266';
254 end if;
255 END IF;
256 END IF;
257 i:= i+1;
258 end loop;
259 commit;
260 end;
261
262 end iby_riskyinstr_pkg;