[Home] [Help]
PACKAGE BODY: APPS.IBY_PAYEE_PKG
Source
1 package body iby_payee_pkg as
2 /*$Header: ibypyeeb.pls 120.1 2005/07/26 17:26:59 rameshsh ship $*/
3
4
5 /*
6 ** Function: payeeNameExists.
7 ** Purpose: Check if any payee Name already exists in the system.
8 */
9 function payeeNameExists (i_name in iby_payee.name%type,
10 i_payeeid in iby_payee.payeeid%type )
11 -- when payeeid is '', we are adding a new one
12 -- otherwise it's update for an existing one
13 return boolean
14 is
15 l_flag boolean := false;
16 l_name iby_payee.name%type;
17
18 -- for modify
19 cursor c_payee(ci_name in iby_payee.name%type,
20 ci_payeeid in iby_payee.payeeid%type) is
21 SELECT name
22 FROM iby_payee
23 WHERE name = ci_name AND
24 payeeid <> ci_payeeid;
25
26 -- for create
27 cursor c_payee2(ci_name in iby_payee.name%type) is
28
29 SELECT name
30 FROM iby_payee
31 WHERE name = ci_name;
32
33 begin
34 if ( c_payee%isopen) then
35 close c_payee;
36 end if;
37
38 if ( c_payee2%isopen) then
39 close c_payee2;
40 end if;
41
42
43 if (i_payeeid is null or i_payeeid = '') then
44 -- create
45 open c_payee2(i_name);
46 fetch c_payee2 into l_name;
47 l_flag := c_payee2%found;
48 close c_payee2;
49 else
50 -- modify
51 open c_payee(i_name, i_payeeid);
52 fetch c_payee into l_name;
53 l_flag := c_payee%found;
54 close c_payee;
55 end if;
56
57 /*
58 ** if payee name already exist then return true otherwise flase.
59 */
60 return l_flag;
61 end payeeNameExists;
62
63
64 /*
65 ** Function: payeeExists.
66 ** Purpose: Check if the specified payeeid and ecappid exists or not.
67 */
68 function payeeExists(i_ecappid in iby_payee.ecappid%type,
69 i_payeeid in iby_payee.payeeid%type)
70 return boolean
71 is
72 l_payeeid iby_payee.payeeid%type;
73 l_flag boolean := false;
74 cursor c_payeeid
75 (ci_payeeid iby_payee.payeeid%type)
76 is
77 SELECT payeeid
78 FROM iby_payee
79 WHERE payeeid = ci_payeeid; -- no longer touches ecappid
80 begin
81 if ( c_payeeid%isopen) then
82 close c_payeeid;
83 end if;
84 /*
85 ** open the cursor, which retrieves all the rows that match the ecappid and
86 ** payeeid.
87 */
88 open c_payeeid( i_payeeid);
89 fetch c_payeeid into l_payeeid;
90 /*
91 ** if payeeid already exist then return true otherwise flase.
92 */
93 l_flag := c_payeeid%found;
94
95 close c_payeeid;
96 return l_flag;
97 end payeeExists;
98
99
100 /*
101 ** Function: payeeActive.
102 ** Purpose: Check if the specified payeeid and ecappid is active or not.
103 */
104 function payeeActive(i_ecappid in iby_payee.ecappid%type,
105 i_payeeid in iby_payee.payeeid%type)
106 return boolean
107 is
108 l_payeeid iby_payee.payeeid%type;
109 l_flag boolean := false;
110 cursor c_payeeid (
111 ci_payeeid iby_payee.payeeid%type)
112 is
113 select payeeid from iby_payee
114 where payeeid = ci_payeeid
115 and upper(activestatus) = 'Y';
116 begin
117 if ( c_payeeid%isopen) then
118 close c_payeeid;
119 end if;
120 /*
121 ** open the cursor, which retrieves all the rows that match the ecappid and
122 ** payeeid.
123 */
124 open c_payeeid( i_payeeid);
125 fetch c_payeeid into l_payeeid;
126 /*
127 ** if payeeid and ecappid already exist then return true otherwise flase.
128 */
129 l_flag := c_payeeid%found;
130
131 close c_payeeid;
132 return l_flag;
133 end payeeActive;
134
135
136 /*
137 ** Procedure: createPayee.
138 ** Purpose: creates a payee object in iby_payee.
139 ** parameters: i_payeeid, id of the payee that is passed by ec application.
140 ** ecappid, id of the ecapplication.
141 */
142 procedure createPayee(i_ecappid in iby_ecapp.ecappid%type,
143 i_payeeid in iby_payee.payeeid%type,
144 i_payeename in iby_payee.name%type,
145 i_supportedOp in iby_payee.supportedOp%type,
146 i_username in iby_payee.username%type,
147 i_password in iby_payee.password%type,
148 i_activestatus in iby_payee.activeStatus%type,
149 i_threshold in iby_payee.threshold%type,
150 i_risk_enabled in iby_payee.risk_enabled%type,
151 i_bepids in JTF_NUMBER_TABLE,
152 i_bepkeys in varchar2,
153 i_bepdefaults in varchar2,
154 i_mcc in number,
155 i_secenable IN iby_payee.security_enabled%TYPE
156 )
157
158 is
159 l_bepid_tab number_tab;
160 l_bepkey_tab varchar_tab;
161 l_bepdefaults_tab varchar_tab;
162 l_id_cnt integer;
163 l_key_cnt integer;
164 l_default_cnt integer;
165
166 mpayeeid number;
167
168
169 BEGIN
170
171 -- check to make sure ecappid is valid
172 if (not iby_ecapp_pkg.ecappExists(i_ecappid)) then
173 raise_application_error(-20000, 'IBY_20550#', FALSE);
174 end if;
175
176 IF (payeeNameExists(i_payeename, '')) THEN
177 raise_application_error(-20000, 'IBY_20514#', FALSE);
178 END IF;
179
180 /*
181 ** call payeeExists to verfify if payee already exists or not. if not
182 ** make an entry into iby_payee table.
183 */
184
185 if (not payeeExists( i_ecappid,i_payeeid) ) then
186
187 select iby_payee_s.NEXTVAL
188 into mpayeeid
189 from dual;
190
191 INSERT INTO iby_payee ( mpayeeid,ecappid, payeeid, name, supportedop,
192 username, password, activestatus,
193 threshold, risk_enabled, mcc_code, security_enabled,
194 last_update_date, last_updated_by,
195 creation_date, created_by,
196 last_update_login, object_version_number)
197 VALUES (mpayeeid, i_ecappid, i_payeeid, i_payeename, i_supportedop,
198 i_username, i_password, i_activestatus,
199 i_threshold, i_risk_enabled, i_mcc, i_secenable,
200 sysdate, fnd_global.user_id,
201 sysdate, fnd_global.user_id,
202 fnd_global.login_id, 1);
203
204 getNumberTables(i_bepids, l_bepid_tab, l_id_cnt);
205 getTables(i_bepkeys,l_bepkey_tab, l_key_cnt);
206 getTables(i_bepdefaults, l_bepdefaults_tab, l_default_cnt);
207
208 for i in 1..l_id_cnt loop
209 iby_bepkeys_pkg.createBEPKey(l_bepid_tab(i), 'PAYEE', i_payeeid, l_bepkey_tab(i), l_bepdefaults_tab(i));
210 end loop;
211
212 else
213 /*DEBUGGING THIS SECTION*/
214 --raise_application_error(-20514, 'Payee Exists', FALSE);
215 raise_application_error(-20000, 'IBY_20514', FALSE);
216 end if;
217 -- end if;
218
219 commit;
220 end createPayee;
221
222
223 /*
224 ** Procedure activatePayee
225 ** Change the active status of the payee
226 **
227 ** ecappid is no longer used
228 */
229 procedure setPayeeStatus(i_ecappid in iby_payee.ecappid%type,
230 i_payeeid in iby_payee.payeeid%type,
231 i_activestatus in iby_payee.activeStatus%type)
232 is
233
234 begin
235 if (payeeExists(i_ecappid, i_payeeid) ) then
236 UPDATE iby_payee
237 SET activestatus = i_activestatus,
238 last_update_date = sysdate,
239 last_updated_by = fnd_global.user_id,
240 last_update_login = fnd_global.login_id
241 WHERE payeeid = i_payeeid;
242 else
243 /*DEBUGGING THIS SECTION*/
244 raise_application_error(-20000, 'IBY_20515#', FALSE);
245 end if;
246
247 end setPayeeStatus;
248
249
250
251 /*
252 ** Procedure: modifyPayee.
253 ** Purpose: creates a payee object in iby_payee.
254 ** parameters: i_payeeid, id of the payee that is passed by ec application.
255 ** ecappid, id of the ecapplication.
256 ** ecappid is no longer used
257 */
258 procedure modifyPayee(i_ecappid in iby_ecapp.ecappid%type,
259 i_payeeid in iby_payee.payeeid%type,
260 i_payeename in iby_payee.name%type,
261 i_supportedOp in iby_payee.supportedOp%type,
262 i_username in iby_payee.username%type,
263 i_password in iby_payee.password%type,
264 i_activestatus in iby_payee.activeStatus%type,
265 i_threshold in iby_payee.threshold%type,
266 i_risk_enabled in iby_payee.risk_enabled%type,
267 i_bepids in JTF_NUMBER_TABLE,
268 i_bepkeys in varchar2,
269 i_bepdefaults in varchar2,
270 i_mcc in number,
271 i_secenable IN iby_payee.security_enabled%TYPE,
272 i_object_version in iby_payee.object_version_number%type
273 )
274 is
275 l_bepid_tab number_tab;
276 l_bepkey_tab varchar_tab;
277 l_bepdefaults_tab varchar_tab;
278 l_id_cnt integer;
279 l_key_cnt integer;
280 l_default_cnt integer;
281
282
283 begin
284 /*
285 ** can only modify existing, active payee
286 */
287
288 if (payeeExists(i_ecappid,i_payeeid)) then
289
290 IF (payeeNameExists(i_payeename, i_payeeid)) THEN
291 raise_application_error(-20000, 'IBY_20514#', FALSE);
292 END IF;
293
294 UPDATE iby_payee
295 SET name= i_payeename,
296 supportedOp = i_supportedOp,
297 username = i_username,
298 password = i_password,
299 activestatus = i_activestatus,
300 threshold = i_threshold,
301 risk_enabled = i_risk_enabled,
302 mcc_code = i_mcc,
303 security_enabled = i_secenable,
304 last_update_date = sysdate,
305 last_updated_by = fnd_global.user_id,
306 last_update_login = fnd_global.login_id,
307 object_version_number = object_version_number + 1
308 WHERE payeeid = i_payeeid
309 AND object_version_number = i_object_version;
310
311 else
312 raise_application_error(-20000, 'IBY_20515#', FALSE);
313 end if;
314 --dbms_output.put_line('Update to payee complete');
315
316 getNumberTables(i_bepids, l_bepid_tab, l_id_cnt);
317 getTables(i_bepkeys,l_bepkey_tab, l_key_cnt);
318 getTables(i_bepdefaults, l_bepdefaults_tab, l_default_cnt);
319
320 iby_bepkeys_pkg.deleteBEPKeys(i_payeeid, 'PAYEE');
321 for i in 1..l_id_cnt loop
322 iby_bepkeys_pkg.createBEPKey(l_bepid_tab(i), 'PAYEE', i_payeeid, l_bepkey_tab(i), l_bepdefaults_tab(i));
323 end loop;
324 commit;
325 end modifyPayee;
326
327
328 /*
329 ** getTables
330 ** looks like it parse an input string delimited with ',' to a table structure
331 */
332 procedure getTables(tableString varchar2, pltable out NOCOPY varchar_tab, counter out NOCOPY integer)
333 is
334 loopindex int := 1;
335 index1 int := 1;
336 index2 int := 1;
337 str varchar2(100);
338 begin
339 if ( length(tableString) is null ) then
340 counter := 0;
341 return;
342 end if;
343 index2 := instrb(tableString,',',index1);
344 while ( index2 <> 0 ) loop
345 pltable(loopindex) := substr(tableString, index1, (index2 - index1 ));
346 index1 := index2 + 1;
347 index2 := instr(tableString,',',index1);
348 loopindex := loopindex + 1;
349 end loop;
350 pltable(loopindex) := substr(tableString, index1);
351 counter := loopindex;
352 end getTables;
353
354 /*
355 ** getNumberTables
356 ** Parses an input JTF_NUMBER_TABLE to a table structure
357 */
358 procedure getNumberTables(tableNumber JTF_NUMBER_TABLE, pltable out NOCOPY number_tab, counter out NOCOPY integer)
359 is
360
361 ddindx binary_integer; indx binary_integer;
362
363 begin
364
365 if (tableNumber is null) then
366 counter := 0;
367 return;
368 end if;
369
370 indx := tableNumber.first;
371 ddindx := 0;
372
373 while true loop
374
375 ddindx := ddindx+1;
376
377 plTable(ddindx) := tableNumber(indx);
378
379 if tableNumber.last = indx
380 then exit;
381 end if;
382 indx := tableNumber.next(indx);
383
384 end loop;
385
386 counter := ddindx;
387
388 end getNumberTables;
389
390 end iby_payee_pkg;