1 package body iby_bepinfo_pkg as
2 /*$Header: ibybepib.pls 115.18 2002/11/16 00:40:23 jleybovi ship $*/
3
4
5 g_pkg_name CONSTANT VARCHAR2(30) := 'IBY_BEPINFO_PKG';
6
7 /*
8 ** Function:suffixExists.
9 ** Purpose: Check if suffix already exists
10 **
11 */
12 function suffixExists (i_bepsuffix in iby_bepinfo.suffix%type,
13 i_bepid in iby_bepinfo.bepid%type )
14 -- when bepid is -1, we are adding a new one
15 -- otherwise it's update for an existing one
16 return boolean
17
18 is
19 l_flag boolean := false;
20 l_bepsuffix iby_bepinfo.suffix%type;
21
22 cursor c_bep(ci_bepsuffix in iby_bepinfo.suffix%type,
23 ci_bepid in iby_bepinfo.bepid%type) is
24 SELECT suffix
25 FROM iby_bepinfo
26 WHERE suffix = ci_bepsuffix AND
27 bepid <> ci_bepid;
28 begin
29 if ( c_bep%isopen) then
30 close c_bep;
31 end if;
32 /*
33 ** open the cursor, which retrieves all the rows
34 */
35 open c_bep(i_bepsuffix, i_bepid);
36 fetch c_bep into l_bepsuffix;
37 /*
38 ** if bep suffix already exist then return true otherwise flase.
39 */
40 l_flag := c_bep%found;
41
42 close c_bep;
43 return l_flag;
44 end suffixExists;
45
46
47
48 /*
49 ** Function: bepNameExists.
50 ** Purpose: Check if any bep Name already exists in the system.
51 */
52 function bepNameExists (i_bepname in iby_bepinfo.name%type,
53 i_bepid in iby_bepinfo.bepid%type )
54 -- when bepid is -1, we are adding a new one
55 -- otherwise it's update for an existing one
56 return boolean
57 is
58 l_flag boolean := false;
59 l_bepname iby_bepinfo.name%type;
60 cursor c_bep(ci_bepname in iby_bepinfo.name%type,
61 ci_bepid in iby_bepinfo.bepid%type) is
62 SELECT name
63 FROM iby_bepinfo
64 WHERE name = ci_bepname AND
65 bepid <> ci_bepid;
66
67 begin
68 if ( c_bep%isopen) then
69 close c_bep;
70 end if;
71 /*
72 ** open the cursor, which retrieves all the rows
73 */
74 open c_bep(i_bepname, i_bepid);
75 fetch c_bep into l_bepname;
76 /*
77 ** if bep name already exist then return true otherwise flase.
78 */
79 l_flag := c_bep%found;
80
81 close c_bep;
82 return l_flag;
83 end bepNameExists;
84
85 /*
86 ** Procedure: createBEPInfo
87 ** Purpose: Creates an entry in the iby_bepinfo table and assigns an id
88 ** to identify it.
89 ** Parameters: bepname, name of the bep, bepurl, url of the bep
90 ** bepusername, username to be used to logon to bep,
91 ** beppassword, password for bepusername,
92 ** psusername, username to be used by bep when it call ps
93 ** catridge, pspassword, psusernames password.
94 ** login and lout urls for BEP, if any.
95 ** beptype is type of the bep. It could be OFX, creditcard,etc.
96 ** modsupport, cancsupport, identifies whether bep supports them
97 ** or not. srvridimmed, whether a server id is returned
98 ** immediately for a payment or not.
99 ** leadtime is the time that a payment should be scheduled.
100 */
101 procedure createBEPInfo(i_bepname in iby_bepinfo.name%type,
102 i_bepurl in iby_bepinfo.baseurl%type,
103 i_bepsuffix in iby_bepinfo.suffix%type,
104 i_bep_type in iby_bepinfo.bep_type%type,
105 i_bepusername in iby_bepinfo.bepusername%type,
106 i_beppassword in iby_bepinfo.beppassword%type,
107 i_psusername in iby_bepinfo.psusername%type,
108 i_pspassword in iby_bepinfo.pspassword%type,
109 i_adminurl in iby_bepinfo.adminurl%type,
110 i_login in iby_bepinfo.loginurl%type,
111 i_logout in iby_bepinfo.logouturl%type,
112 i_supportedop in iby_bepinfo.supportedOp%type,
113 i_pmtschemeName in JTF_VARCHAR2_TABLE_100,
114 i_leadtime in iby_bepinfo.leadtime%type,
115 i_srvrIdImmed in iby_bepinfo.srvrIdImmed%type,
116 i_holidayfile in iby_bepinfo.holidayfile%type,
117 i_fileSupport in iby_bepinfo.fileSupport%type,
118 i_activestatus in iby_bepinfo.activestatus%type,
119 i_securityscheme in iby_bepinfo.securityscheme%type,
120 i_partyid in NUMBER,
121 i_preNLSLang in iby_beplangs.beplang%type,
122 i_opt1NLSLang in iby_beplangs.beplang%type,
123 i_opt2NLSLang in iby_beplangs.beplang%type,
124 io_bepid in out nocopy iby_bepinfo.bepid%type)
125 is
126 l_instrtype iby_bepinfo.instrtype%type;
127
128 cursor c_bepid is
129 SELECT to_char(iby_bep_s.nextval) FROM dual;
130
131 begin
132
133 /*
134 ** error checking, make BEP doesn't already exist, both BEP NAME, and
135 ** SUFFIX has to be unique, and paymentscheme name has to exist
136 */
137
138 /*
139 ** DB constraints are case sensitive, 'Cybercash' and 'cyBerCash' are
140 ** considered DIFFERENT entries.
141 ** All suffix will be 3-letter code in lower case, ensured in Java layer
142 */
143
144
145 if (bepNameExists(i_bepname, -1)) then
146 --raise_application_error(-20524, 'BEP name already exists...',FALSE);
147 raise_application_error(-20000, 'IBY_20524#', FALSE);
148 end if;
149
150 if (suffixExists(i_bepsuffix, -1)) then
151 raise_application_error(-20000, 'IBY_20525#', FALSE);
152 --raise_application_error(-20525, 'Suffix already exists...',FALSE);
153 end if;
154
155 /* obtain the new bep id
156 ** close the cursor if it is already open.
157 ** open the cursor and get the next sequence number.
158 */
159 if ( c_bepid%isopen ) then
160 close c_bepid;
161 end if;
162 open c_bepid;
163 fetch c_bepid into io_bepid;
164
165 /*
166 ** It's a new BEP, insert information into iby_bepinfo.
167 */
168 INSERT INTO iby_bepinfo ( bepid, name, bepusername, beppassword, psusername,
169 pspassword, baseurl, suffix, bep_type,
170 supportedop, adminurl, loginurl, logouturl,
171 leadtime, srvrIdImmed, holidayfile,
172 filesupport, activestatus,
173 instrtype, securityscheme, party_id,
174 last_update_date, last_updated_by,
175 creation_date, created_by,
176 last_update_login, object_version_number)
177 VALUES ( io_bepid, i_bepname, i_bepusername, i_beppassword, i_psusername,
178 i_pspassword, i_bepurl, i_bepsuffix, i_bep_type, i_supportedop,
179 i_adminurl, i_login, i_logout,
180 i_leadtime, i_srvrIdImmed, i_holidayfile,
181 i_fileSupport, i_activeStatus,
182 l_instrtype, i_securityscheme, i_partyid,
183 sysdate, fnd_global.user_id,
184 sysdate, fnd_global.user_id,
185 fnd_global.login_id, 1);
186
187 close c_bepid;
188
189 -- create pmt scheme info
190 -- Add check to create Payment Scheme only if input is not null
191 -- FZ 5/20/02
192 IF i_pmtschemename IS NOT NULL THEN
193 iby_pmtschemes_pkg.createPmtScheme(io_bepid, i_pmtschemename);
194 END IF;
195
196 --create NLS Languages.
197 iby_beplangs_pkg.createBEPLangs(io_bepid, i_preNLSLang,
198 i_opt1NLSLang, i_opt2NLSLang);
199
200 commit;
201
202 end createBEPInfo;
203
204
205
206
207 -- This wrapper version is to handle FND_API.G_MISS_XXX
208 -- Note the validation in createBEPInfo() is not enough
209 -- We should add more to it later. No validation is done
210 -- in this wrapper.
211 -- FZ 5/20/02
212 procedure create_BEPInfo(
213 i_bepname in iby_bepinfo.name%type,
214 i_bepurl in iby_bepinfo.baseurl%type,
215 i_bepsuffix in iby_bepinfo.suffix%type,
216 i_bep_type in iby_bepinfo.bep_type%type,
217 i_bepusername in iby_bepinfo.bepusername%type,
218 i_beppassword in iby_bepinfo.beppassword%type,
219 i_psusername in iby_bepinfo.psusername%type,
220 i_pspassword in iby_bepinfo.pspassword%type,
221 i_adminurl in iby_bepinfo.adminurl%type,
222 i_login in iby_bepinfo.loginurl%type,
223 i_logout in iby_bepinfo.logouturl%type,
224 i_supportedop in iby_bepinfo.supportedOp%type,
225 i_pmtschemeName in JTF_VARCHAR2_TABLE_100,
226 i_leadtime in iby_bepinfo.leadtime%type,
227 i_srvrIdImmed in iby_bepinfo.srvrIdImmed%type,
228 i_holidayfile in iby_bepinfo.holidayfile%type,
229 i_fileSupport in iby_bepinfo.fileSupport%type,
230 i_activestatus in iby_bepinfo.activestatus%type,
231 i_securityscheme in iby_bepinfo.securityscheme%type,
232 i_partyid in NUMBER,
233 i_preNLSLang in iby_beplangs.beplang%type,
234 i_opt1NLSLang in iby_beplangs.beplang%type,
235 i_opt2NLSLang in iby_beplangs.beplang%type,
236 io_bepid in out nocopy iby_bepinfo.bepid%type)
237
238 is
239
240 l_bepname iby_bepinfo.name%type;
241 l_bepurl iby_bepinfo.baseurl%type;
242 l_bepsuffix iby_bepinfo.suffix%type;
243 l_bep_type iby_bepinfo.bep_type%type;
244 l_bepusername iby_bepinfo.bepusername%type;
245 l_beppassword iby_bepinfo.beppassword%type;
246 l_psusername iby_bepinfo.psusername%type;
247 l_pspassword iby_bepinfo.pspassword%type;
248 l_adminurl iby_bepinfo.adminurl%type;
249 l_login iby_bepinfo.loginurl%type;
250 l_logout iby_bepinfo.logouturl%type;
251 l_supportedop iby_bepinfo.supportedOp%type;
252 l_leadtime iby_bepinfo.leadtime%type;
253 l_srvrIdImmed iby_bepinfo.srvrIdImmed%type DEFAULT 'Y';
254 l_holidayfile iby_bepinfo.holidayfile%type DEFAULT 'N';
255 l_fileSupport iby_bepinfo.fileSupport%type DEFAULT 'N';
256 l_activestatus iby_bepinfo.activestatus%type DEFAULT 'Y';
257 l_securityscheme iby_bepinfo.securityscheme%type;
258 l_partyid NUMBER;
259 l_preNLSLang iby_beplangs.beplang%type;
260 l_opt1NLSLang iby_beplangs.beplang%type;
261 l_opt2NLSLang iby_beplangs.beplang%type;
262
263 begin
264
265 l_bepname := i_bepname;
266 IF l_bepname = FND_API.G_MISS_CHAR THEN
267 l_bepname := NULL;
268 END IF;
269
270 l_bepurl := i_bepurl;
271 IF l_bepurl = FND_API.G_MISS_CHAR THEN
272 l_bepurl := NULL;
273 END IF;
274
275 l_bepsuffix := i_bepsuffix;
276 IF l_bepsuffix = FND_API.G_MISS_CHAR THEN
277 l_bepsuffix := NULL;
278 END IF;
279
280 l_bep_type := i_bep_type;
281 IF l_bep_type = FND_API.G_MISS_CHAR THEN
282 l_bep_type := NULL;
283 END IF;
284
285 l_bepusername := i_bepusername;
286 IF l_bepusername = FND_API.G_MISS_CHAR THEN
287 l_bepusername := NULL;
288 END IF;
289
290 l_beppassword := i_beppassword;
291 IF l_beppassword = FND_API.G_MISS_CHAR THEN
292 l_beppassword := NULL;
293 END IF;
294
295 l_psusername := i_psusername;
296 IF l_psusername = FND_API.G_MISS_CHAR THEN
297 l_psusername := NULL;
298 END IF;
299
300 l_pspassword := i_pspassword;
301 IF l_pspassword = FND_API.G_MISS_CHAR THEN
302 l_pspassword := NULL;
303 END IF;
304
305 l_adminurl := i_adminurl;
306 IF l_adminurl = FND_API.G_MISS_CHAR THEN
307 l_adminurl := NULL;
308 END IF;
309
310 l_login := i_login;
311 IF l_login = FND_API.G_MISS_CHAR THEN
312 l_login := NULL;
313 END IF;
314
315 l_logout := i_logout;
316 IF l_logout = FND_API.G_MISS_CHAR THEN
317 l_logout := NULL;
318 END IF;
319
320 l_supportedop := i_supportedop;
321 IF l_supportedop = FND_API.G_MISS_NUM THEN
322 l_supportedop := NULL;
323 END IF;
324
325 l_leadtime := i_leadtime;
326 IF l_leadtime = FND_API.G_MISS_NUM THEN
327 l_leadtime := NULL;
328 END IF;
329
330 l_srvrIdImmed := i_srvrIdImmed;
331 IF l_srvrIdImmed = FND_API.G_MISS_CHAR THEN
332 l_srvrIdImmed := NULL;
333 END IF;
334
335 l_holidayfile := i_holidayfile;
336 IF l_holidayfile = FND_API.G_MISS_CHAR THEN
337 l_holidayfile := NULL;
338 END IF;
339
340 l_fileSupport := i_fileSupport;
341 IF l_fileSupport = FND_API.G_MISS_CHAR THEN
342 l_fileSupport := NULL;
343 END IF;
344
345 l_activestatus := i_activestatus;
346 IF l_activestatus = FND_API.G_MISS_CHAR THEN
347 l_activestatus := NULL;
348 END IF;
349
350 l_securityscheme := i_securityscheme;
351 IF l_securityscheme = FND_API.G_MISS_NUM THEN
352 l_securityscheme := NULL;
353 END IF;
354
355 l_partyid := i_partyid;
356 IF l_partyid = FND_API.G_MISS_NUM THEN
357 l_partyid := NULL;
358 END IF;
359
360 l_preNLSLang := i_preNLSLang;
361 IF l_preNLSLang = FND_API.G_MISS_CHAR THEN
362 l_preNLSLang := NULL;
363 END IF;
364
365 l_opt1NLSLang := i_opt1NLSLang;
366 IF l_opt1NLSLang = FND_API.G_MISS_CHAR THEN
367 l_opt1NLSLang := NULL;
368 END IF;
369
370 l_opt2NLSLang := i_opt2NLSLang;
371 IF l_opt2NLSLang = FND_API.G_MISS_CHAR THEN
372 l_opt2NLSLang := NULL;
373 END IF;
374
375 IF io_bepid = FND_API.G_MISS_NUM THEN
376 io_bepid := NULL;
377 END IF;
378
379 -- forward the call to the base API
380 createBEPInfo(
381 i_bepname => l_bepname,
382 i_bepurl => l_bepurl,
383 i_bepsuffix => l_bepsuffix,
384 i_bep_type => l_bep_type,
385 i_bepusername => l_bepusername,
386 i_beppassword => l_beppassword,
387 i_psusername => l_psusername,
388 i_pspassword => l_pspassword,
389 i_adminurl => l_adminurl,
390 i_login => l_login,
391 i_logout => l_logout,
392 i_supportedop => l_supportedop,
393 i_pmtschemeName => i_pmtschemeName,
394 i_leadtime => l_leadtime,
395 i_srvrIdImmed => l_srvrIdImmed,
396 i_holidayfile => l_holidayfile,
397 i_fileSupport => l_fileSupport,
398 i_activestatus => l_activestatus,
399 i_securityscheme => l_securityscheme,
400 i_partyid => l_partyid,
401 i_preNLSLang => l_preNLSLang,
402 i_opt1NLSLang => l_opt1NLSLang,
403 i_opt2NLSLang => l_opt2NLSLang,
404 io_bepid => io_bepid
405 );
406
407 end create_BEPInfo;
408
409
410
411
412 /*
413 ** Function: modBEPInfo
414 ** Purpose: modifies the entry in the iby_bepinfo table that matches bepid
415 ** passed with the values specified.
416 ** Parameters: bepid, id of the bep, bepname, name of the bep,
417 ** bepurl, url of the bep
418 ** bepusername, username to be used to logon to bep,
419 ** beppassword, password for bepusername,
420 ** psusername, username to be used by bep when it call ps
421 ** catridge, pspassword, psusernames password.
422 ** beptype is type of the bep. It could be OFX, creditcard,etc.
423 ** modsupport, cancsupport, identifies whether bep supports them
424 ** or not. srvridimmed, whether a server id is returned
425 ** immediately for a payment or not.
426 ** leadtime is the time that a payment should be scheduled.
427 */
428 procedure modBEPInfo(i_bepid in iby_bepinfo.bepid%type,
429 i_bepname in iby_bepinfo.name%type,
430 i_bepurl in iby_bepinfo.baseurl%type,
431 i_bepsuffix in iby_bepinfo.suffix%type,
432 i_bep_type in iby_bepinfo.bep_type%type,
433 i_bepusername in iby_bepinfo.bepusername%type,
434 i_beppassword in iby_bepinfo.beppassword%type,
435 i_psusername in iby_bepinfo.psusername%type,
436 i_pspassword in iby_bepinfo.pspassword%type,
437 i_adminurl in iby_bepinfo.adminurl%type,
438 i_login in iby_bepinfo.loginurl%type,
439 i_logout in iby_bepinfo.logouturl%type,
440 i_supportedop in iby_bepinfo.supportedOp%type,
441 i_pmtschemeName in JTF_VARCHAR2_TABLE_100,
442 i_leadtime in iby_bepinfo.leadtime%type,
443 i_srvrIdImmed in iby_bepinfo.srvrIdImmed%type,
444 i_holidayfile in iby_bepinfo.holidayfile%type,
445 i_fileSupport in iby_bepinfo.fileSupport%type,
446 i_activestatus in iby_bepinfo.activestatus%type,
447 i_securityscheme in iby_bepinfo.securityscheme%type,
448 i_partyid in NUMBER,
449 i_preNLSLang in iby_beplangs.beplang%type,
450 i_opt1NLSLang in iby_beplangs.beplang%type,
451 i_opt2NLSLang in iby_beplangs.beplang%type,
452 i_object_version in iby_bepinfo.object_version_number%type)
453 is
454
455 l_instrtype iby_bepinfo.instrtype%type;
456
457 begin
458
459 -- check for the bep name/suffix uniqueness before update
460 -- this check is not needed in terms of functionality since there are
461 -- unique constraints in DB already, however, it will give better
462 -- clear error messages
463
464 if (bepNameExists(i_bepname, i_bepid)) then
465 --raise_application_error(-20524, 'BEP name already exists...',FALSE);
466 raise_application_error(-20000, 'IBY_20524#', FALSE);
467 end if;
468
469 if (suffixExists(i_bepsuffix, i_bepid)) then
470 raise_application_error(-20000, 'IBY_20525#', FALSE);
471 --raise_application_error(-20525, 'Suffix already exists...',FALSE);
472 end if;
473
474
475 /*
476 ** update the row that matches the bepid;
477 */
478 UPDATE iby_bepinfo
479 SET name = i_bepname,
480 bepusername = i_bepusername,
481 beppassword = i_beppassword,
482 psusername = i_psusername,
483 pspassword = i_pspassword,
484 baseurl = i_bepurl,
485 suffix = i_bepsuffix,
486 bep_type = i_bep_type,
487 adminurl = i_adminurl,
488 loginurl = i_login,
489 logouturl = i_logout,
490 supportedop = i_supportedop,
491 leadtime = i_leadtime,
492 srvrIdImmed = i_srvrIdImmed,
493 holidayfile = i_holidayfile,
494 filesupport = i_fileSupport,
495 activeStatus = i_activeStatus,
496 instrtype = l_instrtype,
497 securityscheme = i_securityscheme,
498 party_id = i_partyid,
499 last_update_date = sysdate,
500 last_updated_by = fnd_global.user_id,
501 last_update_login = fnd_global.login_id,
502 object_version_number = object_version_number + 1
503 WHERE bepid = i_bepid
504 AND object_version_number = i_object_version;
505
506 if ( sql%notfound ) then
507 -- no row matches, invalid bepid or object version number
508 raise_application_error(-20000, 'IBY_20521#', FALSE);
509 -- no need to worry about mulitple matches since 'bepid' is unique
510 end if;
511
512
513 -- replace pmt scheme info
514 iby_pmtschemes_pkg.createPmtScheme(i_bepid, i_pmtschemename);
515
516 -- replace NLS Languages.
517 iby_beplangs_pkg.createBEPLangs(i_bepid, i_preNLSLang,
518 i_opt1NLSLang, i_opt2NLSLang);
519 commit;
520 end modbepinfo;
521
522
523
524
525
526 -- This is similar to modBEPInfo(), however we add code to
527 -- to handle FND_API.G_MISS_XXX
528 -- As we add this as a new independent API, we also add the
529 -- missing param for IBY_BEPINFO.INSTRTYPE
530 -- FZ 5/20/02
531 procedure update_BEPInfo(
532 i_bepid in iby_bepinfo.bepid%type,
533 i_bepname in iby_bepinfo.name%type,
534 i_bepurl in iby_bepinfo.baseurl%type,
535 i_bepsuffix in iby_bepinfo.suffix%type,
536 i_bep_type in iby_bepinfo.bep_type%type,
537 i_bepusername in iby_bepinfo.bepusername%type,
538 i_beppassword in iby_bepinfo.beppassword%type,
539 i_psusername in iby_bepinfo.psusername%type,
540 i_pspassword in iby_bepinfo.pspassword%type,
541 i_adminurl in iby_bepinfo.adminurl%type,
542 i_login in iby_bepinfo.loginurl%type,
543 i_logout in iby_bepinfo.logouturl%type,
544 i_supportedop in iby_bepinfo.supportedOp%type,
545 i_pmtschemeName in JTF_VARCHAR2_TABLE_100,
546 i_leadtime in iby_bepinfo.leadtime%type,
547 i_srvrIdImmed in iby_bepinfo.srvrIdImmed%type,
548 i_holidayfile in iby_bepinfo.holidayfile%type,
549 i_fileSupport in iby_bepinfo.fileSupport%type,
550 i_activestatus in iby_bepinfo.activestatus%type,
551 i_securityscheme in iby_bepinfo.securityscheme%type,
552 i_partyid in NUMBER,
553 i_preNLSLang in iby_beplangs.beplang%type,
554 i_opt1NLSLang in iby_beplangs.beplang%type,
555 i_opt2NLSLang in iby_beplangs.beplang%type,
556 i_object_version in iby_bepinfo.object_version_number%type)
557
558 is
559 l_api_name CONSTANT VARCHAR2(30) := 'update_BEPInfo';
560
561 begin
562
563 -- bepid must not be null or FND_API.G_MISS_NUM
564 IF (i_bepid IS NULL) OR (i_bepid = FND_API.G_MISS_NUM) THEN
565 fnd_message.set_name('IBY', 'IBY_G_INVALID_PARAM_ERR');
566 fnd_message.set_token('API', g_pkg_name || '.' || l_api_name);
567 fnd_message.set_token('PARAM', 'i_bepid: ' || i_bepid);
568 fnd_message.set_token('REASON', 'Incorrect param value');
569 fnd_msg_pub.add;
570
571 RAISE FND_API.G_EXC_ERROR;
572 END IF;
573
574 -- object_version_number must not be null or FND_API.G_MISS_NUM
575 -- for update API
576 IF (i_object_version IS NULL) OR (i_object_version = FND_API.G_MISS_NUM) THEN
577 fnd_message.set_name('IBY', 'IBY_G_INVALID_PARAM_ERR');
578 fnd_message.set_token('API', g_pkg_name || '.' || l_api_name);
579 fnd_message.set_token('PARAM', 'i_object_version: ' || i_object_version);
580 fnd_message.set_token('REASON', 'Incorrect param value');
581 fnd_msg_pub.add;
582
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585
586
587 -- check for the bep name/suffix uniqueness before update
588 -- this check is not needed in terms of functionality since there are
589 -- unique constraints in DB already, however, it will give better
590 -- clear error messages
591
592 -- these validation are still valid, keep them
593 -- however they are incomplete - they do not check against
594 -- FND_API.G_MISS_CHAR for these mandatory columns
595 -- we will add additional checks below
596 -- FZ 5/20/02
597 if (bepNameExists(i_bepname, i_bepid)) then
598 --raise_application_error(-20524, 'BEP name already exists...',FALSE);
599 raise_application_error(-20000, 'IBY_20524#', FALSE);
600 end if;
601
602 if (suffixExists(i_bepsuffix, i_bepid)) then
603 raise_application_error(-20000, 'IBY_20525#', FALSE);
604 --raise_application_error(-20525, 'Suffix already exists...',FALSE);
605 end if;
606
607
608 -- by the new App standard, if input is FND_API.G_MISS_CHAR
609 -- in update API, it means the caller wants to set the field
610 -- to null. However IBY_BEPINFO.NAME is a not null column
611 -- note it's ok for the input param to be null. In that case
612 -- the existing value in db is retained
613 IF i_bepname = FND_API.G_MISS_CHAR THEN
614 fnd_message.set_name('IBY', 'IBY_G_INVALID_PARAM_ERR');
615 fnd_message.set_token('API', g_pkg_name || '.' || l_api_name);
616 fnd_message.set_token('PARAM', 'i_bepname: FND_API.G_MISS_CHAR');
617 fnd_message.set_token('REASON', 'Incorrect param value');
618 fnd_msg_pub.add;
619
620 RAISE FND_API.G_EXC_ERROR;
621 END IF;
622
623 -- by the new App standard, if input is FND_API.G_MISS_CHAR
624 -- in update API, it means the caller wants to set the field
625 -- to null. However IBY_BEPINFO.SUFFIX is a not null column
626 -- note it's ok for the input param to be null. In that case
627 -- the existing value in db is retained
628 IF i_bepsuffix = FND_API.G_MISS_CHAR THEN
629 fnd_message.set_name('IBY', 'IBY_G_INVALID_PARAM_ERR');
630 fnd_message.set_token('API', g_pkg_name || '.' || l_api_name);
631 fnd_message.set_token('PARAM', 'i_bepsuffix: FND_API.G_MISS_CHAR');
632 fnd_message.set_token('REASON', 'Incorrect param value');
633 fnd_msg_pub.add;
634
635 RAISE FND_API.G_EXC_ERROR;
636 END IF;
637
638
639 -- update the row that matches the bepid;
640 -- should use OVN based locking
641 UPDATE IBY_BEPINFO SET
642 name = DECODE(i_bepname, NULL, name, i_bepname),
643 suffix = DECODE(i_bepsuffix, NULL, suffix, i_bepsuffix),
644 bepusername = DECODE(i_bepusername, NULL, bepusername, FND_API.G_MISS_CHAR, NULL, i_bepusername),
645 beppassword = DECODE(i_beppassword, NULL, beppassword, FND_API.G_MISS_CHAR, NULL, i_beppassword),
646 psusername = DECODE(i_psusername, NULL, psusername, FND_API.G_MISS_CHAR, NULL, i_psusername),
647 pspassword = DECODE(i_pspassword, NULL, pspassword, FND_API.G_MISS_CHAR, NULL, i_pspassword),
648 baseurl = DECODE(i_bepurl, NULL, baseurl, FND_API.G_MISS_CHAR, NULL, i_bepurl),
649 bep_type = DECODE(i_bep_type, NULL, bep_type, FND_API.G_MISS_CHAR, NULL, i_bep_type),
650 adminurl = DECODE(i_adminurl, NULL, adminurl, FND_API.G_MISS_CHAR, NULL, i_adminurl),
651 loginurl = DECODE(i_login, NULL, loginurl, FND_API.G_MISS_CHAR, NULL, i_login),
652 logouturl = DECODE(i_logout, NULL, logouturl, FND_API.G_MISS_CHAR, NULL, i_logout),
653 supportedop = DECODE(i_supportedop, NULL, supportedop, FND_API.G_MISS_NUM, NULL, i_supportedop),
654 leadtime = DECODE(i_leadtime, NULL, leadtime, FND_API.G_MISS_NUM, NULL, i_leadtime),
655 srvrIdImmed = DECODE(i_srvrIdImmed, NULL, srvrIdImmed, FND_API.G_MISS_CHAR, NULL, i_srvrIdImmed),
656 holidayfile = DECODE(i_holidayfile, NULL, holidayfile, FND_API.G_MISS_CHAR, NULL, i_holidayfile),
657 filesupport = DECODE(i_fileSupport, NULL, filesupport, FND_API.G_MISS_CHAR, NULL, i_fileSupport),
658 activeStatus = DECODE(i_activeStatus, NULL, activeStatus, FND_API.G_MISS_CHAR, NULL, i_activeStatus),
659 -- instrtype = DECODE(p_instr_type, NULL, instrtype, FND_API.G_MISS_CHAR, NULL, p_instr_type),
660 securityscheme = DECODE(i_securityscheme, NULL, securityscheme, FND_API.G_MISS_NUM, NULL, i_securityscheme),
661 party_id = DECODE(i_partyid, NULL, party_id, FND_API.G_MISS_CHAR, NULL, i_partyid),
662 last_update_date = SYSDATE,
663 last_updated_by = fnd_global.user_id,
664 last_update_login = fnd_global.login_id,
665 object_version_number = object_version_number + 1
666 WHERE bepid = i_bepid
667 AND object_version_number = i_object_version;
668
669 if ( sql%notfound ) then
670 -- no row matches, invalid bepid or object version number
671 raise_application_error(-20000, 'IBY_20521#', FALSE);
672 -- no need to worry about mulitple matches since 'bepid' is unique
673 end if;
674
675 -- right now we don't have an update API for the iby_pmtschemes_pkg
676 -- the logic in modbepinfo() is to simply overwrite the existing
677 -- payment scheme definition. This is too simplistic. We need to
678 -- have a better solution. For now we skip the payment schemes
679 -- and BEP langs update, as there are no business requirement
680 -- at this point.
681
682 -- replace NLS Languages.
683 iby_beplangs_pkg.createBEPLangs(i_bepid, i_preNLSLang,
684 i_opt1NLSLang, i_opt2NLSLang);
685 commit;
686
687 end update_BEPInfo;
688
689
690
691
692 /*
693 ** Procedure Name : getBEPInfo
694 ** Purpose : retrieves Back end processor information table.
695 **
696 ** Parameters:
697 **
698 ** In : i_bepid.
699 ** Out : o_bepname, o_bepurl, o_beptype, o_srvrimm, o_modsupport,
700 ** o_bepusername, o_beppassword, o_psusername, o_pspassword,
701 ** o_login, o_logout.
702 ** o_cancsupport, io_depid
703 **
704 */
705 procedure getBEPInfo(i_bepid in iby_bepinfo.bepid%type,
706 o_bepname out nocopy iby_bepinfo.name%type,
707 o_bepurl out nocopy iby_bepinfo.baseurl%type,
708 o_bepsuffix out nocopy iby_bepinfo.suffix%type,
709 o_bep_type out nocopy iby_bepinfo.bep_type%type,
710 o_bepusername out nocopy iby_bepinfo.bepusername%type,
711 o_beppassword out nocopy iby_bepinfo.beppassword%type,
712 o_psusername out nocopy iby_bepinfo.psusername%type,
713 o_pspassword out nocopy iby_bepinfo.pspassword%type,
714 o_adminurl out nocopy iby_bepinfo.adminurl%type,
715 o_login out nocopy iby_bepinfo.loginurl%type,
716 o_logout out nocopy iby_bepinfo.logouturl%type,
717 o_supportedop out nocopy iby_bepinfo.supportedOp%type,
718 o_pmtschemeName out nocopy JTF_VARCHAR2_TABLE_100,
719 o_leadtime out nocopy iby_bepinfo.leadtime%type,
720 o_srvrIdImmed out nocopy iby_bepinfo.srvrIdImmed%type,
721 o_holidayfile out nocopy iby_bepinfo.holidayfile%type,
722 o_fileSupport out nocopy iby_bepinfo.fileSupport%type,
723 o_activestatus out nocopy iby_bepinfo.activestatus%type,
724 o_securityscheme out nocopy iby_bepinfo.securityscheme%type,
725 o_partyid out nocopy NUMBER,
726 o_preNLSLang out nocopy iby_beplangs.beplang%type,
727 o_opt1NLSLang out nocopy iby_beplangs.beplang%type,
728 o_opt2NLSLang out nocopy iby_beplangs.beplang%type,
729 o_object_version out nocopy iby_bepinfo.object_version_number%type)
730 is
731
732 cursor c_bepinfo(ci_bepid in iby_bepinfo.bepid%type) is
733 SELECT name, baseurl, suffix, bep_type, bepusername, beppassword,
734 supportedop, psusername, pspassword,
735 adminurl, loginurl, logouturl, leadtime, holidayfile,
736 srvridimmed, filesupport, activestatus,
737 securityscheme, party_id, object_version_number
738 FROM iby_bepinfo
739 WHERE bepid = ci_bepid;
740
741 cursor c_preNLSLang(ci_bepid in iby_bepinfo.bepid%type) is
742 SELECT beplang
743 FROM iby_beplangs
744 WHERE bepid = ci_bepid
745 AND preferred = 0;
746
747 cursor c_opt1NLSLang(ci_bepid in iby_bepinfo.bepid%type) is
748 SELECT beplang
749 FROM iby_beplangs
750 WHERE bepid = ci_bepid
751 AND preferred = 1;
752
753 cursor c_opt2NLSLang(ci_bepid in iby_bepinfo.bepid%type) is
754 SELECT beplang
755 FROM iby_beplangs
756 WHERE bepid = ci_bepid
757 AND preferred = 2;
758
759 begin
760 if ( c_bepinfo%isopen ) then
761 close c_bepinfo;
762 end if;
763 open c_bepinfo(i_Bepid);
764 fetch c_bepinfo into o_bepname, o_bepurl, o_bepsuffix, o_bep_type,
765 o_bepusername,
766 o_beppassword,
767 o_supportedop,
768 o_psusername, o_pspassword, o_adminurl,
769 o_login, o_logout, o_leadtime,
770 o_holidayfile, o_srvrIdImmed, o_filesupport,
771 o_activestatus,
772 o_securityscheme, o_partyid,
773 o_object_version;
774
775 if ( c_bepinfo%notfound ) then
776 -- no row matched, invalid bepid or object version number
777 raise_application_error(-20000, 'IBY_20521#', FALSE);
778 end if;
779
780 close c_bepinfo;
781
782 -- get pmtscheme name based on bepid
783 iby_pmtschemes_pkg.getPmtSchemeName(i_bepid, o_pmtschemeName);
784
785 if ( c_preNLSLang%isopen ) then
786 close c_preNLSLang;
787 end if;
788 open c_preNLSLang(i_bepid);
789 fetch c_preNLSLang into o_preNLSLang;
790 close c_preNLSLang;
791
792 if ( c_opt1NLSLang%isopen ) then
793 close c_opt1NLSLang;
794 end if;
795 open c_opt1NLSLang(i_bepid);
796 fetch c_opt1NLSLang into o_opt1NLSLang;
797 close c_opt1NLSLang;
798
799 if ( c_opt2NLSLang%isopen ) then
800 close c_opt2NLSLang;
801 end if;
802 open c_opt2NLSLang(i_bepid);
803 fetch c_opt2NLSLang into o_opt2NLSLang;
804 close c_opt2NLSLang;
805 end;
806
807
808 /*
809 ** Procedure Name : setBEPStatus
810 ** Purpose : Sets BEP status to the given value.
811 **
812 ** Parameters:
813 **
814 ** In : i_bepid, i_status.
815 ** Out : o_flag
816 **
817 */
818 procedure setBEPStatus(i_bepid in iby_bepinfo.bepid%type,
819 i_status in iby_bepinfo.activestatus%type,
820 o_flag out nocopy int)
821 is
822
823 begin
824
825 o_flag := 0;
826
827 UPDATE iby_bepinfo
828 SET activestatus = i_status,
829 last_update_date = sysdate,
830 last_updated_by = fnd_global.user_id,
831 last_update_login = fnd_global.login_id,
832 object_version_number = object_version_number + 1
833 WHERE bepid = i_bepid;
834 -- can't check object version here, as old API didn't ask for
835 -- object_version, simply increment it
836 ---AND object_version_number = i_object_version;
837
838 if ( sql%notfound ) then
839 -- no row matches, invalid bepid or object version number
840 raise_application_error(-20000, 'IBY_20521#', FALSE);
841 -- no need to worry about mulitple matches since 'bepid' is unique
842 end if;
843
844 o_flag := 1;
845 commit;
846 end setBEPStatus;
847
848 /*
849 ** Function: getBepName
850 ** Purpose: return the bep name that matches the id passed
851 */
852
853 function getBEPName(i_id iby_bepinfo.bepid%type)
854 return varchar
855 is
856
857 l_bepname iby_bepinfo.name%type;
858
859 cursor c_bep(ci_id iby_bepinfo.bepid%type)
860 is
861 SELECT name
862 FROM iby_bepinfo
863 WHERE bepid = ci_id;
864
865 BEGIN
866 IF (c_bep%isopen) THEN
867 close c_bep;
868 END IF;
869
870 open c_bep(i_id);
871 fetch c_bep into l_bepname;
872
873 if ( c_bep%notfound ) then
874 l_bepname := null;
875 end if;
876 close c_bep;
877 return l_bepname;
878 end getBEPName;
879
880
881 /*
882 ** Function: getBEPId.
883 ** Purpose: returnt the bep id that matches the name passed.
884 */
885 function getBEPId(i_name iby_bepinfo.name%type)
886 return int
887 is
888
889 l_flag boolean := false;
890 l_bepid iby_bepinfo.bepid%type;
891
892 cursor c_bep(ci_name iby_bepinfo.name%type)
893 is
894 select bepid
895 from iby_bepinfo
896 where name = ci_name;
897
898 begin
899
900 if ( c_bep%isopen) then
901 close c_bep;
902 end if;
903 /*
904 ** open the cursor, which retrieves all the rows
905 */
906 open c_bep(i_name);
907 fetch c_bep into l_bepid;
908 /*
909 ** if bep does not exist then return error value.
910 */
911 if ( c_bep%notfound ) then
912 l_bepid := -99;
913 end if;
914 close c_bep;
915 return l_bepid;
916 end getBEPId;
917
918
919
920 end iby_bepinfo_pkg;
[Home] [Help]