[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_MLSUTL_PVT
Source
1 PACKAGE BODY IBY_FNDCPT_MLSUTL_PVT AS
2 /* $Header: ibyfcmub.pls 120.14.12010000.1 2008/07/28 05:40:35 appldev ship $ */
3
4 procedure EXTRACT_INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_EXTRACT_ID in NUMBER,
7 X_DATASOURCE_CODE in VARCHAR2,
8 X_GEN_CODE_LANGUAGE in VARCHAR2,
9 X_GEN_CODE_PACKAGE in VARCHAR2,
10 X_GEN_CODE_ENTRY_POINT in VARCHAR2,
11 X_EXTRACT_CODE in VARCHAR2,
12 X_EXTRACT_VERSION in NUMBER,
13 X_OBJECT_VERSION_NUMBER in NUMBER,
14 X_EXTRACT_DESC in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from IBY_EXTRACTS_B
22 where EXTRACT_ID = X_EXTRACT_ID
23 ;
24 begin
25 insert into IBY_EXTRACTS_B (
26 DATASOURCE_CODE,
27 GEN_CODE_LANGUAGE,
28 GEN_CODE_PACKAGE,
29 GEN_CODE_ENTRY_POINT,
30 EXTRACT_ID,
31 EXTRACT_CODE,
32 EXTRACT_VERSION,
33 OBJECT_VERSION_NUMBER,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_DATASOURCE_CODE,
41 X_GEN_CODE_LANGUAGE,
42 X_GEN_CODE_PACKAGE,
43 X_GEN_CODE_ENTRY_POINT,
44 X_EXTRACT_ID,
45 X_EXTRACT_CODE,
46 X_EXTRACT_VERSION,
47 X_OBJECT_VERSION_NUMBER,
48 X_CREATION_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATE_DATE,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN
53 );
54
55 insert into IBY_EXTRACTS_TL (
56 EXTRACT_DESC,
57 EXTRACT_ID,
58 CREATED_BY,
59 CREATION_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN,
63 OBJECT_VERSION_NUMBER,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_EXTRACT_DESC,
68 X_EXTRACT_ID,
69 X_CREATED_BY,
70 X_CREATION_DATE,
71 X_LAST_UPDATED_BY,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATE_LOGIN,
74 X_OBJECT_VERSION_NUMBER,
75 L.LANGUAGE_CODE,
76 userenv('LANG')
77 from FND_LANGUAGES L
78 where L.INSTALLED_FLAG in ('I', 'B')
79 and not exists
80 (select NULL
81 from IBY_EXTRACTS_TL T
82 where T.EXTRACT_ID = X_EXTRACT_ID
83 and T.LANGUAGE = L.LANGUAGE_CODE);
84
85 open c;
86 fetch c into X_ROWID;
87 if (c%notfound) then
88 close c;
89 raise no_data_found;
90 end if;
91 close c;
92
93 end EXTRACT_INSERT_ROW;
94
95 procedure EXTRACT_UPDATE_ROW (
96 X_EXTRACT_ID in NUMBER,
97 X_DATASOURCE_CODE in VARCHAR2,
98 X_GEN_CODE_LANGUAGE in VARCHAR2,
99 X_GEN_CODE_PACKAGE in VARCHAR2,
100 X_GEN_CODE_ENTRY_POINT in VARCHAR2,
101 X_EXTRACT_CODE in VARCHAR2,
102 X_EXTRACT_VERSION in NUMBER,
103 X_OBJECT_VERSION_NUMBER in NUMBER,
104 X_EXTRACT_DESC in VARCHAR2,
105 X_LAST_UPDATE_DATE in DATE,
106 X_LAST_UPDATED_BY in NUMBER,
107 X_LAST_UPDATE_LOGIN in NUMBER
108 ) is
109 begin
110 update IBY_EXTRACTS_B set
111 DATASOURCE_CODE = X_DATASOURCE_CODE,
112 GEN_CODE_LANGUAGE = X_GEN_CODE_LANGUAGE,
113 GEN_CODE_PACKAGE = X_GEN_CODE_PACKAGE,
114 GEN_CODE_ENTRY_POINT = X_GEN_CODE_ENTRY_POINT,
115 EXTRACT_CODE = X_EXTRACT_CODE,
116 EXTRACT_VERSION = X_EXTRACT_VERSION,
117 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
118 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
119 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
120 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
121 where EXTRACT_ID = X_EXTRACT_ID;
122
123 if (sql%notfound) then
124 raise no_data_found;
125 end if;
126
127 update IBY_EXTRACTS_TL set
128 EXTRACT_DESC = X_EXTRACT_DESC,
129 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
130 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
132 SOURCE_LANG = userenv('LANG')
133 where EXTRACT_ID = X_EXTRACT_ID
134 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
135
136 if (sql%notfound) then
137 raise no_data_found;
138 end if;
139 end EXTRACT_UPDATE_ROW;
140
141 procedure EXTRACT_DELETE_ROW (
142 X_EXTRACT_ID in NUMBER
143 ) is
144 begin
145 delete from IBY_EXTRACTS_TL
146 where EXTRACT_ID = X_EXTRACT_ID;
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151
152 delete from IBY_EXTRACTS_B
153 where EXTRACT_ID = X_EXTRACT_ID;
154
155 if (sql%notfound) then
156 raise no_data_found;
157 end if;
158 end EXTRACT_DELETE_ROW;
159
160 procedure EXTRACT_ADD_LANGUAGE
161 is
162 begin
163 delete from IBY_EXTRACTS_TL T
164 where not exists
165 (select NULL
166 from IBY_EXTRACTS_B B
167 where B.EXTRACT_ID = T.EXTRACT_ID
168 );
169
170 update IBY_EXTRACTS_TL T set (
171 EXTRACT_DESC
172 ) = (select
173 B.EXTRACT_DESC
174 from IBY_EXTRACTS_TL B
175 where B.EXTRACT_ID = T.EXTRACT_ID
176 and B.LANGUAGE = T.SOURCE_LANG)
177 where (
178 T.EXTRACT_ID,
179 T.LANGUAGE
180 ) in (select
181 SUBT.EXTRACT_ID,
182 SUBT.LANGUAGE
183 from IBY_EXTRACTS_TL SUBB, IBY_EXTRACTS_TL SUBT
184 where SUBB.EXTRACT_ID = SUBT.EXTRACT_ID
185 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
186 and (SUBB.EXTRACT_DESC <> SUBT.EXTRACT_DESC
187 or (SUBB.EXTRACT_DESC is null and SUBT.EXTRACT_DESC is not null)
188 or (SUBB.EXTRACT_DESC is not null and SUBT.EXTRACT_DESC is null)
189 ));
190
191 insert into IBY_EXTRACTS_TL (
192 EXTRACT_DESC,
193 EXTRACT_ID,
194 CREATED_BY,
195 CREATION_DATE,
196 LAST_UPDATED_BY,
197 LAST_UPDATE_DATE,
198 LAST_UPDATE_LOGIN,
199 OBJECT_VERSION_NUMBER,
200 LANGUAGE,
201 SOURCE_LANG
202 ) select /*+ ORDERED */
203 B.EXTRACT_DESC,
204 B.EXTRACT_ID,
205 B.CREATED_BY,
206 B.CREATION_DATE,
207 B.LAST_UPDATED_BY,
208 B.LAST_UPDATE_DATE,
209 B.LAST_UPDATE_LOGIN,
210 B.OBJECT_VERSION_NUMBER,
211 L.LANGUAGE_CODE,
212 B.SOURCE_LANG
213 from IBY_EXTRACTS_TL B, FND_LANGUAGES L
214 where L.INSTALLED_FLAG in ('I', 'B')
215 and B.LANGUAGE = userenv('LANG')
216 and not exists
217 (select NULL
218 from IBY_EXTRACTS_TL T
219 where T.EXTRACT_ID = B.EXTRACT_ID
220 and T.LANGUAGE = L.LANGUAGE_CODE);
221 end EXTRACT_ADD_LANGUAGE;
222
223 procedure EXTRACT_LOAD_ROW (
224 X_EXTRACT_ID in NUMBER,
225 X_DATASOURCE_CODE in VARCHAR2,
226 X_GEN_CODE_LANGUAGE in VARCHAR2,
227 X_GEN_CODE_PACKAGE in VARCHAR2,
228 X_GEN_CODE_ENTRY_POINT in VARCHAR2,
229 X_EXTRACT_CODE in VARCHAR2,
230 X_EXTRACT_VERSION in NUMBER,
231 X_OBJECT_VERSION_NUMBER in NUMBER,
232 X_EXTRACT_DESC in VARCHAR2,
233 X_LAST_UPDATE_DATE in DATE,
234 X_OWNER in VARCHAR2)
235 is
236 row_id VARCHAR2(200);
237 begin
238 EXTRACT_UPDATE_ROW (
239 X_EXTRACT_ID,
240 X_DATASOURCE_CODE,
241 X_GEN_CODE_LANGUAGE,
242 X_GEN_CODE_PACKAGE,
243 X_GEN_CODE_ENTRY_POINT,
244 X_EXTRACT_CODE,
245 X_EXTRACT_VERSION,
246 X_OBJECT_VERSION_NUMBER,
247 X_EXTRACT_DESC,
248 X_LAST_UPDATE_DATE,
249 fnd_load_util.owner_id(X_OWNER),
250 fnd_load_util.owner_id(X_OWNER));
251 exception
252 when no_data_found then
253 EXTRACT_INSERT_ROW (
254 row_id,
255 X_EXTRACT_ID,
256 X_DATASOURCE_CODE,
257 X_GEN_CODE_LANGUAGE,
258 X_GEN_CODE_PACKAGE,
259 X_GEN_CODE_ENTRY_POINT,
260 X_EXTRACT_CODE,
261 X_EXTRACT_VERSION,
262 X_OBJECT_VERSION_NUMBER,
263 X_EXTRACT_DESC,
264 X_LAST_UPDATE_DATE,
265 fnd_load_util.owner_id(X_OWNER),
266 X_LAST_UPDATE_DATE,
267 fnd_load_util.owner_id(X_OWNER),
268 fnd_load_util.owner_id(X_OWNER));
269 end;
270
271 procedure EXTRACT_TRANSLATE_ROW (
272 X_EXTRACT_ID in NUMBER,
273 X_OBJECT_VERSION_NUMBER in NUMBER,
274 X_EXTRACT_DESC in VARCHAR2,
275 X_LAST_UPDATE_DATE in DATE,
276 X_OWNER in VARCHAR2)
277 is
278 begin
279 update iby_extracts_tl set
280 EXTRACT_DESC = X_EXTRACT_DESC,
281 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
282 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
283 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
284 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
285 SOURCE_LANG = userenv('LANG')
286 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
287 and EXTRACT_ID = X_EXTRACT_ID;
288 end;
289
290
291 procedure FORMAT_INSERT_ROW (
292 X_ROWID in out nocopy VARCHAR2,
293 X_FORMAT_CODE in VARCHAR2,
294 X_FORMAT_TYPE_CODE in VARCHAR2,
295 X_FORMAT_TEMPLATE_CODE in VARCHAR2,
296 X_EXTRACT_ID in NUMBER,
297 X_REFERENCE_FORMAT_CODE in VARCHAR2,
298 X_ATTRIBUTE_CATEGORY in VARCHAR2,
299 X_ATTRIBUTE1 in VARCHAR2,
300 X_ATTRIBUTE2 in VARCHAR2,
301 X_ATTRIBUTE3 in VARCHAR2,
302 X_ATTRIBUTE4 in VARCHAR2,
303 X_ATTRIBUTE5 in VARCHAR2,
304 X_ATTRIBUTE6 in VARCHAR2,
305 X_ATTRIBUTE7 in VARCHAR2,
306 X_ATTRIBUTE8 in VARCHAR2,
307 X_ATTRIBUTE9 in VARCHAR2,
308 X_ATTRIBUTE10 in VARCHAR2,
309 X_ATTRIBUTE11 in VARCHAR2,
310 X_ATTRIBUTE12 in VARCHAR2,
311 X_ATTRIBUTE13 in VARCHAR2,
312 X_ATTRIBUTE14 in VARCHAR2,
313 X_ATTRIBUTE15 in VARCHAR2,
314 X_OBJECT_VERSION_NUMBER in NUMBER,
315 X_SEEDED_FLAG in VARCHAR2,
316 X_FORMAT_NAME in VARCHAR2,
317 X_CREATION_DATE in DATE,
318 X_CREATED_BY in NUMBER,
319 X_LAST_UPDATE_DATE in DATE,
320 X_LAST_UPDATED_BY in NUMBER,
321 X_LAST_UPDATE_LOGIN in NUMBER
322 ) is
323 cursor C is select ROWID from IBY_FORMATS_B
324 where FORMAT_CODE = X_FORMAT_CODE
325 ;
326 begin
327 insert into IBY_FORMATS_B (
328 FORMAT_CODE,
329 FORMAT_TYPE_CODE,
330 FORMAT_TEMPLATE_CODE,
331 EXTRACT_ID,
332 REFERENCE_FORMAT_CODE,
333 ATTRIBUTE_CATEGORY,
334 ATTRIBUTE1,
335 ATTRIBUTE2,
336 ATTRIBUTE3,
337 ATTRIBUTE4,
338 ATTRIBUTE5,
339 ATTRIBUTE6,
340 ATTRIBUTE7,
341 ATTRIBUTE8,
342 ATTRIBUTE9,
343 ATTRIBUTE10,
344 ATTRIBUTE11,
345 ATTRIBUTE12,
346 ATTRIBUTE13,
347 ATTRIBUTE14,
348 ATTRIBUTE15,
349 OBJECT_VERSION_NUMBER,
350 SEEDED_FLAG,
351 CREATION_DATE,
352 CREATED_BY,
353 LAST_UPDATE_DATE,
354 LAST_UPDATED_BY,
355 LAST_UPDATE_LOGIN
356 ) values (
357 X_FORMAT_CODE,
358 X_FORMAT_TYPE_CODE,
359 X_FORMAT_TEMPLATE_CODE,
360 X_EXTRACT_ID,
361 X_REFERENCE_FORMAT_CODE,
362 X_ATTRIBUTE_CATEGORY,
363 X_ATTRIBUTE1,
364 X_ATTRIBUTE2,
365 X_ATTRIBUTE3,
366 X_ATTRIBUTE4,
367 X_ATTRIBUTE5,
368 X_ATTRIBUTE6,
369 X_ATTRIBUTE7,
370 X_ATTRIBUTE8,
371 X_ATTRIBUTE9,
372 X_ATTRIBUTE10,
373 X_ATTRIBUTE11,
374 X_ATTRIBUTE12,
375 X_ATTRIBUTE13,
376 X_ATTRIBUTE14,
377 X_ATTRIBUTE15,
378 X_OBJECT_VERSION_NUMBER,
379 X_SEEDED_FLAG,
380 X_CREATION_DATE,
381 X_CREATED_BY,
382 X_LAST_UPDATE_DATE,
383 X_LAST_UPDATED_BY,
384 X_LAST_UPDATE_LOGIN
385 );
386
387 insert into IBY_FORMATS_TL (
388 CREATION_DATE,
389 LAST_UPDATED_BY,
390 LAST_UPDATE_DATE,
391 LAST_UPDATE_LOGIN,
392 OBJECT_VERSION_NUMBER,
393 FORMAT_NAME,
394 CREATED_BY,
395 FORMAT_CODE,
396 LANGUAGE,
397 SOURCE_LANG
398 ) select
399 X_CREATION_DATE,
400 X_LAST_UPDATED_BY,
401 X_LAST_UPDATE_DATE,
402 X_LAST_UPDATE_LOGIN,
403 X_OBJECT_VERSION_NUMBER,
404 X_FORMAT_NAME,
405 X_CREATED_BY,
406 X_FORMAT_CODE,
407 L.LANGUAGE_CODE,
408 userenv('LANG')
409 from FND_LANGUAGES L
410 where L.INSTALLED_FLAG in ('I', 'B')
411 and not exists
412 (select NULL
413 from IBY_FORMATS_TL T
414 where T.FORMAT_CODE = X_FORMAT_CODE
415 and T.LANGUAGE = L.LANGUAGE_CODE);
416
417 open c;
418 fetch c into X_ROWID;
419 if (c%notfound) then
420 close c;
421 raise no_data_found;
422 end if;
423 close c;
424
425 end FORMAT_INSERT_ROW;
426
427 procedure FORMAT_UPDATE_ROW (
428 X_FORMAT_CODE in VARCHAR2,
429 X_FORMAT_TYPE_CODE in VARCHAR2,
430 X_FORMAT_TEMPLATE_CODE in VARCHAR2,
431 X_EXTRACT_ID in NUMBER,
432 X_REFERENCE_FORMAT_CODE in VARCHAR2,
433 X_ATTRIBUTE_CATEGORY in VARCHAR2,
434 X_ATTRIBUTE1 in VARCHAR2,
435 X_ATTRIBUTE2 in VARCHAR2,
436 X_ATTRIBUTE3 in VARCHAR2,
437 X_ATTRIBUTE4 in VARCHAR2,
438 X_ATTRIBUTE5 in VARCHAR2,
439 X_ATTRIBUTE6 in VARCHAR2,
440 X_ATTRIBUTE7 in VARCHAR2,
441 X_ATTRIBUTE8 in VARCHAR2,
442 X_ATTRIBUTE9 in VARCHAR2,
443 X_ATTRIBUTE10 in VARCHAR2,
444 X_ATTRIBUTE11 in VARCHAR2,
445 X_ATTRIBUTE12 in VARCHAR2,
446 X_ATTRIBUTE13 in VARCHAR2,
447 X_ATTRIBUTE14 in VARCHAR2,
448 X_ATTRIBUTE15 in VARCHAR2,
449 X_OBJECT_VERSION_NUMBER in NUMBER,
450 X_SEEDED_FLAG in VARCHAR2,
451 X_FORMAT_NAME in VARCHAR2,
452 X_LAST_UPDATE_DATE in DATE,
453 X_LAST_UPDATED_BY in NUMBER,
454 X_LAST_UPDATE_LOGIN in NUMBER
455 ) is
456 begin
457 update IBY_FORMATS_B set
458 FORMAT_TYPE_CODE = X_FORMAT_TYPE_CODE,
459 FORMAT_TEMPLATE_CODE = X_FORMAT_TEMPLATE_CODE,
460 EXTRACT_ID = X_EXTRACT_ID,
461 REFERENCE_FORMAT_CODE = X_REFERENCE_FORMAT_CODE,
462 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
463 ATTRIBUTE1 = X_ATTRIBUTE1,
464 ATTRIBUTE2 = X_ATTRIBUTE2,
465 ATTRIBUTE3 = X_ATTRIBUTE3,
466 ATTRIBUTE4 = X_ATTRIBUTE4,
467 ATTRIBUTE5 = X_ATTRIBUTE5,
468 ATTRIBUTE6 = X_ATTRIBUTE6,
469 ATTRIBUTE7 = X_ATTRIBUTE7,
470 ATTRIBUTE8 = X_ATTRIBUTE8,
471 ATTRIBUTE9 = X_ATTRIBUTE9,
472 ATTRIBUTE10 = X_ATTRIBUTE10,
473 ATTRIBUTE11 = X_ATTRIBUTE11,
474 ATTRIBUTE12 = X_ATTRIBUTE12,
475 ATTRIBUTE13 = X_ATTRIBUTE13,
476 ATTRIBUTE14 = X_ATTRIBUTE14,
477 ATTRIBUTE15 = X_ATTRIBUTE15,
478 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
479 SEEDED_FLAG = X_SEEDED_FLAG,
480 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
481 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
482 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
483 where FORMAT_CODE = X_FORMAT_CODE;
484
485 if (sql%notfound) then
486 raise no_data_found;
487 end if;
488
489 update IBY_FORMATS_TL set
490 FORMAT_NAME = X_FORMAT_NAME,
491 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
492 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
493 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
494 SOURCE_LANG = userenv('LANG')
495 where FORMAT_CODE = X_FORMAT_CODE
496 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
497
498 if (sql%notfound) then
499 raise no_data_found;
500 end if;
501 end FORMAT_UPDATE_ROW;
502
503 procedure FORMAT_DELETE_ROW (
504 X_FORMAT_CODE in VARCHAR2
505 ) is
506 begin
507 delete from IBY_FORMATS_TL
508 where FORMAT_CODE = X_FORMAT_CODE;
509
510 if (sql%notfound) then
511 raise no_data_found;
512 end if;
513
514 delete from IBY_FORMATS_B
515 where FORMAT_CODE = X_FORMAT_CODE;
516
517 if (sql%notfound) then
518 raise no_data_found;
519 end if;
520 end FORMAT_DELETE_ROW;
521
522 procedure FORMAT_ADD_LANGUAGE
523 is
524 begin
525 delete from IBY_FORMATS_TL T
526 where not exists
527 (select NULL
528 from IBY_FORMATS_B B
529 where B.FORMAT_CODE = T.FORMAT_CODE
530 );
531
532 update IBY_FORMATS_TL T set (
533 FORMAT_NAME
534 ) = (select
535 B.FORMAT_NAME
536 from IBY_FORMATS_TL B
537 where B.FORMAT_CODE = T.FORMAT_CODE
538 and B.LANGUAGE = T.SOURCE_LANG)
539 where (
540 T.FORMAT_CODE,
541 T.LANGUAGE
542 ) in (select
543 SUBT.FORMAT_CODE,
544 SUBT.LANGUAGE
545 from IBY_FORMATS_TL SUBB, IBY_FORMATS_TL SUBT
546 where SUBB.FORMAT_CODE = SUBT.FORMAT_CODE
547 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
548 and (SUBB.FORMAT_NAME <> SUBT.FORMAT_NAME
549 ));
550
551 insert into IBY_FORMATS_TL (
552 CREATION_DATE,
553 LAST_UPDATED_BY,
554 LAST_UPDATE_DATE,
555 LAST_UPDATE_LOGIN,
556 OBJECT_VERSION_NUMBER,
557 FORMAT_NAME,
558 CREATED_BY,
559 FORMAT_CODE,
560 LANGUAGE,
561 SOURCE_LANG
562 ) select /*+ ORDERED */
563 B.CREATION_DATE,
564 B.LAST_UPDATED_BY,
565 B.LAST_UPDATE_DATE,
566 B.LAST_UPDATE_LOGIN,
567 B.OBJECT_VERSION_NUMBER,
568 B.FORMAT_NAME,
569 B.CREATED_BY,
570 B.FORMAT_CODE,
571 L.LANGUAGE_CODE,
572 B.SOURCE_LANG
573 from IBY_FORMATS_TL B, FND_LANGUAGES L
574 where L.INSTALLED_FLAG in ('I', 'B')
575 and B.LANGUAGE = userenv('LANG')
576 and not exists
577 (select NULL
578 from IBY_FORMATS_TL T
579 where T.FORMAT_CODE = B.FORMAT_CODE
580 and T.LANGUAGE = L.LANGUAGE_CODE);
581 end FORMAT_ADD_LANGUAGE;
582
583 procedure FORMAT_LOAD_ROW (
584 X_FORMAT_CODE in VARCHAR2,
585 X_FORMAT_TYPE_CODE in VARCHAR2,
586 X_FORMAT_TEMPLATE_CODE in VARCHAR2,
587 X_EXTRACT_ID in NUMBER,
588 X_REFERENCE_FORMAT_CODE in VARCHAR2,
589 X_ATTRIBUTE_CATEGORY in VARCHAR2,
590 X_ATTRIBUTE1 in VARCHAR2,
591 X_ATTRIBUTE2 in VARCHAR2,
592 X_ATTRIBUTE3 in VARCHAR2,
593 X_ATTRIBUTE4 in VARCHAR2,
594 X_ATTRIBUTE5 in VARCHAR2,
595 X_ATTRIBUTE6 in VARCHAR2,
596 X_ATTRIBUTE7 in VARCHAR2,
597 X_ATTRIBUTE8 in VARCHAR2,
598 X_ATTRIBUTE9 in VARCHAR2,
599 X_ATTRIBUTE10 in VARCHAR2,
600 X_ATTRIBUTE11 in VARCHAR2,
601 X_ATTRIBUTE12 in VARCHAR2,
602 X_ATTRIBUTE13 in VARCHAR2,
603 X_ATTRIBUTE14 in VARCHAR2,
604 X_ATTRIBUTE15 in VARCHAR2,
605 X_OBJECT_VERSION_NUMBER in NUMBER,
606 X_SEEDED_FLAG in VARCHAR2,
607 X_FORMAT_NAME in VARCHAR2,
608 X_LAST_UPDATE_DATE in DATE,
609 X_OWNER in VARCHAR2)
610 is
611 row_id VARCHAR2(200);
612 begin
613 FORMAT_UPDATE_ROW(
614 X_FORMAT_CODE,
615 X_FORMAT_TYPE_CODE,
616 X_FORMAT_TEMPLATE_CODE,
617 X_EXTRACT_ID,
618 X_REFERENCE_FORMAT_CODE,
619 X_ATTRIBUTE_CATEGORY,
620 X_ATTRIBUTE1,
621 X_ATTRIBUTE2,
622 X_ATTRIBUTE3,
623 X_ATTRIBUTE4,
624 X_ATTRIBUTE5,
625 X_ATTRIBUTE6,
626 X_ATTRIBUTE7,
627 X_ATTRIBUTE8,
628 X_ATTRIBUTE9,
629 X_ATTRIBUTE10,
630 X_ATTRIBUTE11,
631 X_ATTRIBUTE12,
632 X_ATTRIBUTE13,
633 X_ATTRIBUTE14,
634 X_ATTRIBUTE15,
635 X_OBJECT_VERSION_NUMBER,
636 X_SEEDED_FLAG,
637 X_FORMAT_NAME,
638 X_LAST_UPDATE_DATE,
639 fnd_load_util.owner_id(X_OWNER),
640 fnd_load_util.owner_id(X_OWNER));
641 exception
642 when no_data_found then
643 FORMAT_INSERT_ROW (
644 row_id,
645 X_FORMAT_CODE,
646 X_FORMAT_TYPE_CODE,
647 X_FORMAT_TEMPLATE_CODE,
648 X_EXTRACT_ID,
649 X_REFERENCE_FORMAT_CODE,
650 X_ATTRIBUTE_CATEGORY,
651 X_ATTRIBUTE1,
652 X_ATTRIBUTE2,
653 X_ATTRIBUTE3,
654 X_ATTRIBUTE4,
655 X_ATTRIBUTE5,
656 X_ATTRIBUTE6,
657 X_ATTRIBUTE7,
658 X_ATTRIBUTE8,
659 X_ATTRIBUTE9,
660 X_ATTRIBUTE10,
661 X_ATTRIBUTE11,
662 X_ATTRIBUTE12,
663 X_ATTRIBUTE13,
664 X_ATTRIBUTE14,
665 X_ATTRIBUTE15,
666 X_OBJECT_VERSION_NUMBER,
667 X_SEEDED_FLAG,
668 X_FORMAT_NAME,
669 X_LAST_UPDATE_DATE,
670 fnd_load_util.owner_id(X_OWNER),
671 X_LAST_UPDATE_DATE,
672 fnd_load_util.owner_id(X_OWNER),
673 fnd_load_util.owner_id(X_OWNER));
674 end;
675
676 procedure FORMAT_TRANSLATE_ROW (
677 X_FORMAT_CODE in VARCHAR2,
678 X_OBJECT_VERSION_NUMBER in NUMBER,
679 X_FORMAT_NAME in VARCHAR2,
680 X_LAST_UPDATE_DATE in DATE,
681 X_OWNER in VARCHAR2)
682 is
683 begin
684 update iby_formats_tl set
685 FORMAT_NAME = X_FORMAT_NAME,
686 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
687 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
688 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
689 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
690 SOURCE_LANG = userenv('LANG')
691 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
692 and FORMAT_CODE = X_FORMAT_CODE;
693 end;
694
695
696 procedure BEP_OPT_INSERT_ROW (
697 X_ROWID in out NOCOPY VARCHAR2,
698 X_BEPID in NUMBER,
699 X_ACCOUNT_OPTION_CODE in VARCHAR2,
700 X_ACCOUNT_OPTION_DATATYPE in VARCHAR2,
701 X_OBJECT_VERSION_NUMBER in NUMBER,
702 X_DISPLAY_ORDER in NUMBER,
703 X_ACCOUNT_OPTION_NAME in VARCHAR2,
704 X_CREATION_DATE in DATE,
705 X_CREATED_BY in NUMBER,
706 X_LAST_UPDATE_DATE in DATE,
707 X_LAST_UPDATED_BY in NUMBER,
708 X_LAST_UPDATE_LOGIN in NUMBER,
709 X_SECURED_FLAG in VARCHAR2,
710 X_UNIQUE_PER_BATCH_FLAG in VARCHAR2
711 ) is
712 cursor C is select ROWID from IBY_BEP_ACCT_OPT_NAME_B
713 where BEPID = X_BEPID
714 and ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE
715 ;
716 begin
717 insert into IBY_BEP_ACCT_OPT_NAME_B (
718 BEPID,
719 ACCOUNT_OPTION_CODE,
720 ACCOUNT_OPTION_DATATYPE,
721 OBJECT_VERSION_NUMBER,
722 DISPLAY_ORDER,
723 CREATION_DATE,
724 CREATED_BY,
725 LAST_UPDATE_DATE,
726 LAST_UPDATED_BY,
727 LAST_UPDATE_LOGIN,
728 SECURED_FLAG,
729 UNIQUE_PER_BATCH_FLAG
730 ) values (
731 X_BEPID,
732 X_ACCOUNT_OPTION_CODE,
733 X_ACCOUNT_OPTION_DATATYPE,
734 X_OBJECT_VERSION_NUMBER,
735 X_DISPLAY_ORDER,
736 X_CREATION_DATE,
737 X_CREATED_BY,
738 X_LAST_UPDATE_DATE,
739 X_LAST_UPDATED_BY,
740 X_LAST_UPDATE_LOGIN,
741 X_SECURED_FLAG,
742 X_UNIQUE_PER_BATCH_FLAG
743 );
744
745 insert into IBY_BEP_ACCT_OPT_NAME_TL (
746 ACCOUNT_OPTION_NAME,
747 CREATED_BY,
748 CREATION_DATE,
749 LAST_UPDATED_BY,
750 LAST_UPDATE_DATE,
751 LAST_UPDATE_LOGIN,
752 OBJECT_VERSION_NUMBER,
753 BEPID,
754 ACCOUNT_OPTION_CODE,
755 LANGUAGE,
756 SOURCE_LANG
757 ) select
758 X_ACCOUNT_OPTION_NAME,
759 X_CREATED_BY,
760 X_CREATION_DATE,
761 X_LAST_UPDATED_BY,
762 X_LAST_UPDATE_DATE,
763 X_LAST_UPDATE_LOGIN,
764 X_OBJECT_VERSION_NUMBER,
765 X_BEPID,
766 X_ACCOUNT_OPTION_CODE,
767 L.LANGUAGE_CODE,
768 userenv('LANG')
769 from FND_LANGUAGES L
770 where L.INSTALLED_FLAG in ('I', 'B')
771 and not exists
772 (select NULL
773 from IBY_BEP_ACCT_OPT_NAME_TL T
774 where T.BEPID = X_BEPID
775 and T.ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE
776 and T.LANGUAGE = L.LANGUAGE_CODE);
777
778 open c;
779 fetch c into X_ROWID;
780 if (c%notfound) then
781 close c;
782 raise no_data_found;
783 end if;
784 close c;
785
786 end BEP_OPT_INSERT_ROW;
787
788 procedure BEP_OPT_UPDATE_ROW (
789 X_BEPID in NUMBER,
790 X_ACCOUNT_OPTION_CODE in VARCHAR2,
791 X_ACCOUNT_OPTION_DATATYPE in VARCHAR2,
792 X_OBJECT_VERSION_NUMBER in NUMBER,
793 X_DISPLAY_ORDER in NUMBER,
794 X_ACCOUNT_OPTION_NAME in VARCHAR2,
795 X_LAST_UPDATE_DATE in DATE,
796 X_LAST_UPDATED_BY in NUMBER,
797 X_LAST_UPDATE_LOGIN in NUMBER,
798 X_SECURED_FLAG in VARCHAR2,
799 X_UNIQUE_PER_BATCH_FLAG in VARCHAR2
800 ) is
801 begin
802 update IBY_BEP_ACCT_OPT_NAME_B set
803 ACCOUNT_OPTION_DATATYPE = X_ACCOUNT_OPTION_DATATYPE,
804 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
805 DISPLAY_ORDER = X_DISPLAY_ORDER,
806 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
807 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
808 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
809 SECURED_FLAG = X_SECURED_FLAG,
810 UNIQUE_PER_BATCH_FLAG = X_UNIQUE_PER_BATCH_FLAG
811 where BEPID = X_BEPID
812 and ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE;
813
814 if (sql%notfound) then
815 raise no_data_found;
816 end if;
817
818 update IBY_BEP_ACCT_OPT_NAME_TL set
819 ACCOUNT_OPTION_NAME = X_ACCOUNT_OPTION_NAME,
820 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
821 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
822 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
823 SOURCE_LANG = userenv('LANG')
824 where BEPID = X_BEPID
825 and ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE
826 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
827
828 if (sql%notfound) then
829 raise no_data_found;
830 end if;
831 end BEP_OPT_UPDATE_ROW;
832
833 procedure BEP_OPT_DELETE_ROW (
834 X_BEPID in NUMBER,
835 X_ACCOUNT_OPTION_CODE in VARCHAR2
836 ) is
837 begin
838 delete from IBY_BEP_ACCT_OPT_NAME_TL
839 where BEPID = X_BEPID
840 and ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE;
841
842 if (sql%notfound) then
843 raise no_data_found;
844 end if;
845
846 delete from IBY_BEP_ACCT_OPT_NAME_B
847 where BEPID = X_BEPID
848 and ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE;
849
850 if (sql%notfound) then
851 raise no_data_found;
852 end if;
853 end BEP_OPT_DELETE_ROW;
854
855 procedure BEP_OPT_ADD_LANGUAGE
856 is
857 begin
858 delete from IBY_BEP_ACCT_OPT_NAME_TL T
859 where not exists
860 (select NULL
861 from IBY_BEP_ACCT_OPT_NAME_B B
862 where B.BEPID = T.BEPID
863 and B.ACCOUNT_OPTION_CODE = T.ACCOUNT_OPTION_CODE
864 );
865
866 update IBY_BEP_ACCT_OPT_NAME_TL T set (
867 ACCOUNT_OPTION_NAME
868 ) = (select
869 B.ACCOUNT_OPTION_NAME
870 from IBY_BEP_ACCT_OPT_NAME_TL B
871 where B.BEPID = T.BEPID
872 and B.ACCOUNT_OPTION_CODE = T.ACCOUNT_OPTION_CODE
873 and B.LANGUAGE = T.SOURCE_LANG)
874 where (
875 T.BEPID,
876 T.ACCOUNT_OPTION_CODE,
877 T.LANGUAGE
878 ) in (select
879 SUBT.BEPID,
880 SUBT.ACCOUNT_OPTION_CODE,
881 SUBT.LANGUAGE
882 from IBY_BEP_ACCT_OPT_NAME_TL SUBB, IBY_BEP_ACCT_OPT_NAME_TL SUBT
883 where SUBB.BEPID = SUBT.BEPID
884 and SUBB.ACCOUNT_OPTION_CODE = SUBT.ACCOUNT_OPTION_CODE
885 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
886 and (SUBB.ACCOUNT_OPTION_NAME <> SUBT.ACCOUNT_OPTION_NAME
887 ));
888
889 insert into IBY_BEP_ACCT_OPT_NAME_TL (
890 ACCOUNT_OPTION_NAME,
891 CREATED_BY,
892 CREATION_DATE,
893 LAST_UPDATED_BY,
894 LAST_UPDATE_DATE,
895 LAST_UPDATE_LOGIN,
896 OBJECT_VERSION_NUMBER,
897 BEPID,
898 ACCOUNT_OPTION_CODE,
899 LANGUAGE,
900 SOURCE_LANG
901 ) select /*+ ORDERED */
902 B.ACCOUNT_OPTION_NAME,
903 B.CREATED_BY,
904 B.CREATION_DATE,
905 B.LAST_UPDATED_BY,
906 B.LAST_UPDATE_DATE,
907 B.LAST_UPDATE_LOGIN,
908 B.OBJECT_VERSION_NUMBER,
909 B.BEPID,
910 B.ACCOUNT_OPTION_CODE,
911 L.LANGUAGE_CODE,
912 B.SOURCE_LANG
913 from IBY_BEP_ACCT_OPT_NAME_TL B, FND_LANGUAGES L
914 where L.INSTALLED_FLAG in ('I', 'B')
915 and B.LANGUAGE = userenv('LANG')
916 and not exists
917 (select NULL
918 from IBY_BEP_ACCT_OPT_NAME_TL T
919 where T.BEPID = B.BEPID
920 and T.ACCOUNT_OPTION_CODE = B.ACCOUNT_OPTION_CODE
921 and T.LANGUAGE = L.LANGUAGE_CODE);
922 end BEP_OPT_ADD_LANGUAGE;
923
924 procedure BEP_OPT_LOAD_ROW (
925 X_BEPID in NUMBER,
926 X_ACCOUNT_OPTION_CODE in VARCHAR2,
927 X_ACCOUNT_OPTION_DATATYPE in VARCHAR2,
928 X_OBJECT_VERSION_NUMBER in NUMBER,
929 X_DISPLAY_ORDER in NUMBER,
930 X_ACCOUNT_OPTION_NAME in VARCHAR2,
931 X_OWNER in VARCHAR2,
932 X_SECURED_FLAG in VARCHAR2,
933 X_UNIQUE_PER_BATCH_FLAG in VARCHAR2)
934 is
935 row_id VARCHAR2(200);
936 begin
937 bep_opt_update_row (
938 X_BEPID,
939 X_ACCOUNT_OPTION_CODE,
940 X_ACCOUNT_OPTION_DATATYPE,
941 X_OBJECT_VERSION_NUMBER,
942 X_DISPLAY_ORDER,
943 X_ACCOUNT_OPTION_NAME,
944 trunc(sysdate),
945 fnd_load_util.owner_id(X_OWNER),
946 fnd_load_util.owner_id(X_OWNER),
947 X_SECURED_FLAG,
948 X_UNIQUE_PER_BATCH_FLAG);
949
950 exception
951 when no_data_found then
952 bep_opt_insert_row (
953 row_id,
954 X_BEPID,
955 X_ACCOUNT_OPTION_CODE,
956 X_ACCOUNT_OPTION_DATATYPE,
957 X_OBJECT_VERSION_NUMBER,
958 X_DISPLAY_ORDER,
959 X_ACCOUNT_OPTION_NAME,
960 trunc(sysdate),
961 fnd_load_util.owner_id(X_OWNER),
962 trunc(sysdate),
963 fnd_load_util.owner_id(X_OWNER),
964 fnd_load_util.owner_id(X_OWNER),
965 X_SECURED_FLAG,
966 X_UNIQUE_PER_BATCH_FLAG);
967 end;
968
969 procedure BEP_OPT_TRANSLATE_ROW (
970 X_BEPID in NUMBER,
971 X_ACCOUNT_OPTION_CODE in VARCHAR2,
972 X_OBJECT_VERSION_NUMBER in NUMBER,
973 X_ACCOUNT_OPTION_NAME in VARCHAR2,
974 X_OWNER in VARCHAR2)
975 is
976 begin
977 update iby_bep_acct_opt_name_tl set
978 ACCOUNT_OPTION_NAME = X_ACCOUNT_OPTION_NAME,
979 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
980 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
981 LAST_UPDATE_DATE = trunc(sysdate),
982 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
983 SOURCE_LANG = userenv('LANG')
984 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
985 and BEPID = X_BEPID
986 and ACCOUNT_OPTION_CODE = X_ACCOUNT_OPTION_CODE;
987 end;
988
989
990 procedure SYS_CC_PROF_INSERT_ROW (
991 X_ROWID in out NOCOPY VARCHAR2,
992 X_SYS_CC_PROFILE_CODE in VARCHAR2,
993 X_SETTLEMENT_TRANS_PRTCL_CODE in VARCHAR2,
994 X_SETTLEMENT_ACK_RDR_CODE in VARCHAR2,
995 X_QUERY_FORMAT_CODE in VARCHAR2,
996 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
997 X_QUERY_ACK_RDR_CODE in VARCHAR2,
998 X_INACTIVE_DATE in DATE,
999 X_OBJECT_VERSION_NUMBER in NUMBER,
1000 X_ONLINE_AUTH_FORMAT_CODE in VARCHAR2,
1001 X_ONLINE_AUTH_TRANS_PRTCL_CODE in VARCHAR2,
1002 X_ONLINE_AUTH_ACK_RDR_CODE in VARCHAR2,
1003 X_SETTLEMENT_FORMAT_CODE in VARCHAR2,
1004 X_PAYMENT_SYSTEM_ID in NUMBER,
1005 X_SYS_CC_PROFILE_NAME in VARCHAR2,
1006 X_CREATION_DATE in DATE,
1007 X_CREATED_BY in NUMBER,
1008 X_LAST_UPDATE_DATE in DATE,
1009 X_LAST_UPDATED_BY in NUMBER,
1010 X_LAST_UPDATE_LOGIN in NUMBER
1011 ) is
1012 cursor C is select ROWID from IBY_FNDCPT_SYS_CC_PF_B
1013 where SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE
1014 ;
1015 begin
1016 insert into IBY_FNDCPT_SYS_CC_PF_B (
1017 SETTLEMENT_TRANS_PRTCL_CODE,
1018 SETTLEMENT_ACK_RDR_CODE,
1019 QUERY_FORMAT_CODE,
1020 QUERY_TRANS_PRTCL_CODE,
1021 QUERY_ACK_RDR_CODE,
1022 INACTIVE_DATE,
1023 OBJECT_VERSION_NUMBER,
1024 ONLINE_AUTH_FORMAT_CODE,
1025 ONLINE_AUTH_TRANS_PRTCL_CODE,
1026 ONLINE_AUTH_ACK_RDR_CODE,
1027 SETTLEMENT_FORMAT_CODE,
1028 SYS_CC_PROFILE_CODE,
1029 PAYMENT_SYSTEM_ID,
1030 CREATION_DATE,
1031 CREATED_BY,
1032 LAST_UPDATE_DATE,
1033 LAST_UPDATED_BY,
1034 LAST_UPDATE_LOGIN
1035 ) values (
1036 X_SETTLEMENT_TRANS_PRTCL_CODE,
1037 X_SETTLEMENT_ACK_RDR_CODE,
1038 X_QUERY_FORMAT_CODE,
1039 X_QUERY_TRANS_PRTCL_CODE,
1040 X_QUERY_ACK_RDR_CODE,
1041 X_INACTIVE_DATE,
1042 X_OBJECT_VERSION_NUMBER,
1043 X_ONLINE_AUTH_FORMAT_CODE,
1044 X_ONLINE_AUTH_TRANS_PRTCL_CODE,
1045 X_ONLINE_AUTH_ACK_RDR_CODE,
1046 X_SETTLEMENT_FORMAT_CODE,
1047 X_SYS_CC_PROFILE_CODE,
1048 X_PAYMENT_SYSTEM_ID,
1049 X_CREATION_DATE,
1050 X_CREATED_BY,
1051 X_LAST_UPDATE_DATE,
1052 X_LAST_UPDATED_BY,
1053 X_LAST_UPDATE_LOGIN
1054 );
1055
1056 insert into IBY_FNDCPT_SYS_CC_PF_TL (
1057 SYS_CC_PROFILE_CODE,
1058 LAST_UPDATED_BY,
1059 LAST_UPDATE_DATE,
1060 LAST_UPDATE_LOGIN,
1061 OBJECT_VERSION_NUMBER,
1062 CREATION_DATE,
1063 SYS_CC_PROFILE_NAME,
1064 CREATED_BY,
1065 LANGUAGE,
1066 SOURCE_LANG
1067 ) select
1068 X_SYS_CC_PROFILE_CODE,
1069 X_LAST_UPDATED_BY,
1070 X_LAST_UPDATE_DATE,
1071 X_LAST_UPDATE_LOGIN,
1072 X_OBJECT_VERSION_NUMBER,
1073 X_CREATION_DATE,
1074 X_SYS_CC_PROFILE_NAME,
1075 X_CREATED_BY,
1076 L.LANGUAGE_CODE,
1077 userenv('LANG')
1078 from FND_LANGUAGES L
1079 where L.INSTALLED_FLAG in ('I', 'B')
1080 and not exists
1081 (select NULL
1082 from IBY_FNDCPT_SYS_CC_PF_TL T
1083 where T.SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE
1084 and T.LANGUAGE = L.LANGUAGE_CODE);
1085
1086 open c;
1087 fetch c into X_ROWID;
1088 if (c%notfound) then
1089 close c;
1090 raise no_data_found;
1091 end if;
1092 close c;
1093
1094 end SYS_CC_PROF_INSERT_ROW;
1095
1096 procedure SYS_CC_PROF_UPDATE_ROW (
1097 X_SYS_CC_PROFILE_CODE in VARCHAR2,
1098 X_SETTLEMENT_TRANS_PRTCL_CODE in VARCHAR2,
1099 X_SETTLEMENT_ACK_RDR_CODE in VARCHAR2,
1100 X_QUERY_FORMAT_CODE in VARCHAR2,
1101 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1102 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1103 X_INACTIVE_DATE in DATE,
1104 X_OBJECT_VERSION_NUMBER in NUMBER,
1105 X_ONLINE_AUTH_FORMAT_CODE in VARCHAR2,
1106 X_ONLINE_AUTH_TRANS_PRTCL_CODE in VARCHAR2,
1107 X_ONLINE_AUTH_ACK_RDR_CODE in VARCHAR2,
1108 X_SETTLEMENT_FORMAT_CODE in VARCHAR2,
1109 X_PAYMENT_SYSTEM_ID in NUMBER,
1110 X_SYS_CC_PROFILE_NAME in VARCHAR2,
1111 X_LAST_UPDATE_DATE in DATE,
1112 X_LAST_UPDATED_BY in NUMBER,
1113 X_LAST_UPDATE_LOGIN in NUMBER
1114 ) is
1115 begin
1116 update IBY_FNDCPT_SYS_CC_PF_B set
1117 SETTLEMENT_TRANS_PRTCL_CODE = X_SETTLEMENT_TRANS_PRTCL_CODE,
1118 SETTLEMENT_ACK_RDR_CODE = X_SETTLEMENT_ACK_RDR_CODE,
1119 QUERY_FORMAT_CODE = X_QUERY_FORMAT_CODE,
1120 QUERY_TRANS_PRTCL_CODE = X_QUERY_TRANS_PRTCL_CODE,
1121 QUERY_ACK_RDR_CODE = X_QUERY_ACK_RDR_CODE,
1122 INACTIVE_DATE = X_INACTIVE_DATE,
1123 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1124 ONLINE_AUTH_FORMAT_CODE = X_ONLINE_AUTH_FORMAT_CODE,
1125 ONLINE_AUTH_TRANS_PRTCL_CODE = X_ONLINE_AUTH_TRANS_PRTCL_CODE,
1126 ONLINE_AUTH_ACK_RDR_CODE = X_ONLINE_AUTH_ACK_RDR_CODE,
1127 SETTLEMENT_FORMAT_CODE = X_SETTLEMENT_FORMAT_CODE,
1128 PAYMENT_SYSTEM_ID = X_PAYMENT_SYSTEM_ID,
1129 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1130 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1131 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1132 where SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE;
1133
1134 if (sql%notfound) then
1135 raise no_data_found;
1136 end if;
1137
1138 update IBY_FNDCPT_SYS_CC_PF_TL set
1139 SYS_CC_PROFILE_NAME = X_SYS_CC_PROFILE_NAME,
1140 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1141 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1142 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1143 SOURCE_LANG = userenv('LANG')
1144 where SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE
1145 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1146
1147 if (sql%notfound) then
1148 raise no_data_found;
1149 end if;
1150 end SYS_CC_PROF_UPDATE_ROW;
1151
1152 procedure SYS_CC_PROF_DELETE_ROW (
1153 X_SYS_CC_PROFILE_CODE in VARCHAR2
1154 ) is
1155 begin
1156 delete from IBY_FNDCPT_SYS_CC_PF_TL
1157 where SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE;
1158
1159 if (sql%notfound) then
1160 raise no_data_found;
1161 end if;
1162
1163 delete from IBY_FNDCPT_SYS_CC_PF_B
1164 where SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE;
1165
1166 if (sql%notfound) then
1167 raise no_data_found;
1168 end if;
1169 end SYS_CC_PROF_DELETE_ROW;
1170
1171 procedure SYS_CC_PROF_ADD_LANGUAGE
1172 is
1173 begin
1174 delete from IBY_FNDCPT_SYS_CC_PF_TL T
1175 where not exists
1176 (select NULL
1177 from IBY_FNDCPT_SYS_CC_PF_B B
1178 where B.SYS_CC_PROFILE_CODE = T.SYS_CC_PROFILE_CODE
1179 );
1180
1181 update IBY_FNDCPT_SYS_CC_PF_TL T set (
1182 SYS_CC_PROFILE_NAME
1183 ) = (select
1184 B.SYS_CC_PROFILE_NAME
1185 from IBY_FNDCPT_SYS_CC_PF_TL B
1186 where B.SYS_CC_PROFILE_CODE = T.SYS_CC_PROFILE_CODE
1187 and B.LANGUAGE = T.SOURCE_LANG)
1188 where (
1189 T.SYS_CC_PROFILE_CODE,
1190 T.LANGUAGE
1191 ) in (select
1192 SUBT.SYS_CC_PROFILE_CODE,
1193 SUBT.LANGUAGE
1194 from IBY_FNDCPT_SYS_CC_PF_TL SUBB, IBY_FNDCPT_SYS_CC_PF_TL SUBT
1195 where SUBB.SYS_CC_PROFILE_CODE = SUBT.SYS_CC_PROFILE_CODE
1196 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1197 and (SUBB.SYS_CC_PROFILE_NAME <> SUBT.SYS_CC_PROFILE_NAME
1198 ));
1199
1200 insert into IBY_FNDCPT_SYS_CC_PF_TL (
1201 SYS_CC_PROFILE_CODE,
1202 LAST_UPDATED_BY,
1203 LAST_UPDATE_DATE,
1204 LAST_UPDATE_LOGIN,
1205 OBJECT_VERSION_NUMBER,
1206 CREATION_DATE,
1207 SYS_CC_PROFILE_NAME,
1208 CREATED_BY,
1209 LANGUAGE,
1210 SOURCE_LANG
1211 ) select /*+ ORDERED */
1212 B.SYS_CC_PROFILE_CODE,
1213 B.LAST_UPDATED_BY,
1214 B.LAST_UPDATE_DATE,
1215 B.LAST_UPDATE_LOGIN,
1216 B.OBJECT_VERSION_NUMBER,
1217 B.CREATION_DATE,
1218 B.SYS_CC_PROFILE_NAME,
1219 B.CREATED_BY,
1220 L.LANGUAGE_CODE,
1221 B.SOURCE_LANG
1222 from IBY_FNDCPT_SYS_CC_PF_TL B, FND_LANGUAGES L
1223 where L.INSTALLED_FLAG in ('I', 'B')
1224 and B.LANGUAGE = userenv('LANG')
1225 and not exists
1226 (select NULL
1227 from IBY_FNDCPT_SYS_CC_PF_TL T
1228 where T.SYS_CC_PROFILE_CODE = B.SYS_CC_PROFILE_CODE
1229 and T.LANGUAGE = L.LANGUAGE_CODE);
1230 end SYS_CC_PROF_ADD_LANGUAGE;
1231
1232 procedure SYS_CC_PROF_LOAD_ROW (
1233 X_SYS_CC_PROFILE_CODE in VARCHAR2,
1234 X_SETTLEMENT_TRANS_PRTCL_CODE in VARCHAR2,
1235 X_SETTLEMENT_ACK_RDR_CODE in VARCHAR2,
1236 X_QUERY_FORMAT_CODE in VARCHAR2,
1237 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1238 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1239 X_INACTIVE_DATE in DATE,
1240 X_OBJECT_VERSION_NUMBER in NUMBER,
1241 X_ONLINE_AUTH_FORMAT_CODE in VARCHAR2,
1242 X_ONLINE_AUTH_TRANS_PRTCL_CODE in VARCHAR2,
1243 X_ONLINE_AUTH_ACK_RDR_CODE in VARCHAR2,
1244 X_SETTLEMENT_FORMAT_CODE in VARCHAR2,
1245 X_PAYMENT_SYSTEM_ID in NUMBER,
1246 X_SYS_CC_PROFILE_NAME in VARCHAR2,
1247 X_LAST_UPDATE_DATE in DATE,
1248 X_OWNER in VARCHAR2)
1249 is
1250 row_id VARCHAR2(200);
1251 begin
1252 SYS_CC_PROF_UPDATE_ROW (
1253 X_SYS_CC_PROFILE_CODE,
1254 X_SETTLEMENT_TRANS_PRTCL_CODE,
1255 X_SETTLEMENT_ACK_RDR_CODE,
1256 X_QUERY_FORMAT_CODE,
1257 X_QUERY_TRANS_PRTCL_CODE,
1258 X_QUERY_ACK_RDR_CODE,
1259 X_INACTIVE_DATE,
1260 X_OBJECT_VERSION_NUMBER,
1261 X_ONLINE_AUTH_FORMAT_CODE,
1262 X_ONLINE_AUTH_TRANS_PRTCL_CODE,
1263 X_ONLINE_AUTH_ACK_RDR_CODE,
1264 X_SETTLEMENT_FORMAT_CODE,
1265 X_PAYMENT_SYSTEM_ID,
1266 X_SYS_CC_PROFILE_NAME,
1267 X_LAST_UPDATE_DATE,
1268 fnd_load_util.owner_id(X_OWNER),
1269 fnd_load_util.owner_id(X_OWNER));
1270 exception
1271 when no_data_found then
1272 SYS_CC_PROF_INSERT_ROW (
1273 row_id,
1274 X_SYS_CC_PROFILE_CODE,
1275 X_SETTLEMENT_TRANS_PRTCL_CODE,
1276 X_SETTLEMENT_ACK_RDR_CODE,
1277 X_QUERY_FORMAT_CODE,
1278 X_QUERY_TRANS_PRTCL_CODE,
1279 X_QUERY_ACK_RDR_CODE,
1280 X_INACTIVE_DATE,
1281 X_OBJECT_VERSION_NUMBER,
1282 X_ONLINE_AUTH_FORMAT_CODE,
1283 X_ONLINE_AUTH_TRANS_PRTCL_CODE,
1284 X_ONLINE_AUTH_ACK_RDR_CODE,
1285 X_SETTLEMENT_FORMAT_CODE,
1286 X_PAYMENT_SYSTEM_ID,
1287 X_SYS_CC_PROFILE_NAME,
1288 X_LAST_UPDATE_DATE,
1289 fnd_load_util.owner_id(X_OWNER),
1290 X_LAST_UPDATE_DATE,
1291 fnd_load_util.owner_id(X_OWNER),
1292 fnd_load_util.owner_id(X_OWNER));
1293 end;
1294
1295 procedure SYS_CC_PROF_TRANSLATE_ROW (
1296 X_SYS_CC_PROFILE_CODE in VARCHAR2,
1297 X_OBJECT_VERSION_NUMBER in NUMBER,
1298 X_SYS_CC_PROFILE_NAME in VARCHAR2,
1299 X_LAST_UPDATE_DATE in DATE,
1300 X_OWNER in VARCHAR2)
1301 is
1302 begin
1303 update iby_fndcpt_sys_cc_pf_tl set
1304 SYS_CC_PROFILE_NAME = X_SYS_CC_PROFILE_NAME,
1305 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1306 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
1307 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1308 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
1309 SOURCE_LANG = userenv('LANG')
1310 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1311 and SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE;
1312 end;
1313
1314
1315 procedure SYS_EFT_PROF_INSERT_ROW (
1316 X_ROWID in out nocopy VARCHAR2,
1317 X_SYS_EFT_PROFILE_CODE in VARCHAR2,
1318 X_SETTLE_REQUIRE_VRFY_FLAG in VARCHAR2,
1319 X_PAYMENT_SYSTEM_ID in NUMBER,
1320 X_VERIFY_FORMAT_CODE in VARCHAR2,
1321 X_VERIFY_TRANS_PRTCL_CODE in VARCHAR2,
1322 X_VERIFY_ACK_RDR_CODE in VARCHAR2,
1323 X_FUNDS_XFER_FORMAT_CODE in VARCHAR2,
1324 X_FUNDS_XFER_TRANS_PRTCL_CODE in VARCHAR2,
1325 X_FUNDS_XFER_ACK_RDR_CODE in VARCHAR2,
1326 X_QUERY_FORMAT_CODE in VARCHAR2,
1327 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1328 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1329 X_INACTIVE_DATE in DATE,
1330 X_OBJECT_VERSION_NUMBER in NUMBER,
1331 X_SYS_EFT_PROFILE_NAME in VARCHAR2,
1332 X_CREATION_DATE in DATE,
1333 X_CREATED_BY in NUMBER,
1334 X_LAST_UPDATE_DATE in DATE,
1335 X_LAST_UPDATED_BY in NUMBER,
1336 X_LAST_UPDATE_LOGIN in NUMBER,
1337 X_PAYER_NOTIFICATION_FORMAT IN VARCHAR2,
1338 X_PAYER_NOTIF_DEL_METHOD IN VARCHAR2,
1339 X_GROUP_BY_ORG IN VARCHAR2,
1340 X_GROUP_BY_LEGAL_ENTITY IN VARCHAR2,
1341 X_GROUP_BY_INT_BANK_ACCOUNT IN VARCHAR2,
1342 X_GROUP_BY_SETTLEMENT_CURR IN VARCHAR2,
1343 X_GROUP_BY_SETTLEMENT_DATE IN VARCHAR2,
1344 X_LIMIT_BY_AMT_CURR IN VARCHAR2,
1345 X_LIMIT_BY_EXCH_RATE_TYPE IN VARCHAR2,
1346 X_LIMIT_BY_TOTAL_AMT in NUMBER,
1347 X_LIMIT_BY_SETTLEMENT_NUM in NUMBER,
1348 X_SETTLEMENT_COMPLETE_POINT IN VARCHAR2,
1349 X_OVERRIDE_PAYER_DELIVERY_FLAG IN VARCHAR2,
1350 X_ACCOMPANY_LETTER_FORMAT IN VARCHAR2,
1351 X_AUTOMATIC_PN_SUBMIT_FLAG IN VARCHAR2
1352 ) is
1353 cursor C is select ROWID from IBY_FNDCPT_SYS_EFT_PF_B
1354 where SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE
1355 ;
1356 begin
1357 insert into IBY_FNDCPT_SYS_EFT_PF_B (
1358 SETTLE_REQUIRE_VRFY_FLAG,
1359 SYS_EFT_PROFILE_CODE,
1360 PAYMENT_SYSTEM_ID,
1361 VERIFY_FORMAT_CODE,
1362 VERIFY_TRANS_PRTCL_CODE,
1363 VERIFY_ACK_RDR_CODE,
1364 FUNDS_XFER_FORMAT_CODE,
1365 FUNDS_XFER_TRANS_PRTCL_CODE,
1366 FUNDS_XFER_ACK_RDR_CODE,
1367 QUERY_FORMAT_CODE,
1368 QUERY_TRANS_PRTCL_CODE,
1369 QUERY_ACK_RDR_CODE,
1370 INACTIVE_DATE,
1371 OBJECT_VERSION_NUMBER,
1372 CREATION_DATE,
1373 CREATED_BY,
1374 LAST_UPDATE_DATE,
1375 LAST_UPDATED_BY,
1376 LAST_UPDATE_LOGIN,
1377 PAYER_NOTIFICATION_FORMAT,
1378 PAYER_NOTIFICATION_DEL_METHOD,
1379 GROUP_BY_ORG,
1380 GROUP_BY_LEGAL_ENTITY,
1381 GROUP_BY_INT_BANK_ACCOUNT,
1382 GROUP_BY_SETTLEMENT_CURR,
1383 GROUP_BY_SETTLEMENT_DATE,
1384 LIMIT_BY_AMT_CURR,
1385 LIMIT_BY_EXCH_RATE_TYPE,
1386 LIMIT_BY_TOTAL_AMT,
1387 LIMIT_BY_SETTLEMENT_NUM,
1388 SETTLEMENT_COMPLETE_POINT,
1389 OVERRIDE_PAYER_DELIVERY_FLAG,
1390 ACCOMPANY_LETTER_FORMAT,
1391 AUTOMATIC_PN_SUBMIT_FLAG
1392 ) values (
1393 X_SETTLE_REQUIRE_VRFY_FLAG,
1394 X_SYS_EFT_PROFILE_CODE,
1395 X_PAYMENT_SYSTEM_ID,
1396 X_VERIFY_FORMAT_CODE,
1397 X_VERIFY_TRANS_PRTCL_CODE,
1398 X_VERIFY_ACK_RDR_CODE,
1399 X_FUNDS_XFER_FORMAT_CODE,
1400 X_FUNDS_XFER_TRANS_PRTCL_CODE,
1401 X_FUNDS_XFER_ACK_RDR_CODE,
1402 X_QUERY_FORMAT_CODE,
1403 X_QUERY_TRANS_PRTCL_CODE,
1404 X_QUERY_ACK_RDR_CODE,
1405 X_INACTIVE_DATE,
1406 X_OBJECT_VERSION_NUMBER,
1407 X_CREATION_DATE,
1408 X_CREATED_BY,
1409 X_LAST_UPDATE_DATE,
1410 X_LAST_UPDATED_BY,
1411 X_LAST_UPDATE_LOGIN,
1412 X_PAYER_NOTIFICATION_FORMAT,
1413 X_PAYER_NOTIF_DEL_METHOD,
1414 X_GROUP_BY_ORG,
1415 X_GROUP_BY_LEGAL_ENTITY,
1416 X_GROUP_BY_INT_BANK_ACCOUNT,
1417 X_GROUP_BY_SETTLEMENT_CURR,
1418 X_GROUP_BY_SETTLEMENT_DATE,
1419 X_LIMIT_BY_AMT_CURR,
1420 X_LIMIT_BY_EXCH_RATE_TYPE,
1421 X_LIMIT_BY_TOTAL_AMT,
1422 X_LIMIT_BY_SETTLEMENT_NUM,
1423 X_SETTLEMENT_COMPLETE_POINT,
1424 X_OVERRIDE_PAYER_DELIVERY_FLAG,
1425 X_ACCOMPANY_LETTER_FORMAT,
1426 X_AUTOMATIC_PN_SUBMIT_FLAG
1427 );
1428
1429 insert into IBY_FNDCPT_SYS_EFT_PF_TL (
1430 SYS_EFT_PROFILE_NAME,
1431 CREATED_BY,
1432 CREATION_DATE,
1433 LAST_UPDATED_BY,
1434 LAST_UPDATE_DATE,
1435 LAST_UPDATE_LOGIN,
1436 OBJECT_VERSION_NUMBER,
1437 SYS_EFT_PROFILE_CODE,
1438 LANGUAGE,
1439 SOURCE_LANG
1440 ) select
1441 X_SYS_EFT_PROFILE_NAME,
1442 X_CREATED_BY,
1443 X_CREATION_DATE,
1444 X_LAST_UPDATED_BY,
1445 X_LAST_UPDATE_DATE,
1446 X_LAST_UPDATE_LOGIN,
1447 X_OBJECT_VERSION_NUMBER,
1448 X_SYS_EFT_PROFILE_CODE,
1449 L.LANGUAGE_CODE,
1450 userenv('LANG')
1451 from FND_LANGUAGES L
1452 where L.INSTALLED_FLAG in ('I', 'B')
1453 and not exists
1454 (select NULL
1455 from IBY_FNDCPT_SYS_EFT_PF_TL T
1456 where T.SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE
1457 and T.LANGUAGE = L.LANGUAGE_CODE);
1458
1459 open c;
1460 fetch c into X_ROWID;
1461 if (c%notfound) then
1462 close c;
1463 raise no_data_found;
1464 end if;
1465 close c;
1466
1467 end SYS_EFT_PROF_INSERT_ROW;
1468
1469 procedure SYS_EFT_PROF_UPDATE_ROW (
1470 X_SYS_EFT_PROFILE_CODE in VARCHAR2,
1471 X_SETTLE_REQUIRE_VRFY_FLAG in VARCHAR2,
1472 X_PAYMENT_SYSTEM_ID in NUMBER,
1473 X_VERIFY_FORMAT_CODE in VARCHAR2,
1474 X_VERIFY_TRANS_PRTCL_CODE in VARCHAR2,
1475 X_VERIFY_ACK_RDR_CODE in VARCHAR2,
1476 X_FUNDS_XFER_FORMAT_CODE in VARCHAR2,
1477 X_FUNDS_XFER_TRANS_PRTCL_CODE in VARCHAR2,
1478 X_FUNDS_XFER_ACK_RDR_CODE in VARCHAR2,
1479 X_QUERY_FORMAT_CODE in VARCHAR2,
1480 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1481 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1482 X_INACTIVE_DATE in DATE,
1483 X_OBJECT_VERSION_NUMBER in NUMBER,
1484 X_SYS_EFT_PROFILE_NAME in VARCHAR2,
1485 X_LAST_UPDATE_DATE in DATE,
1486 X_LAST_UPDATED_BY in NUMBER,
1487 X_LAST_UPDATE_LOGIN in NUMBER,
1488 X_PAYER_NOTIFICATION_FORMAT IN VARCHAR2,
1489 X_PAYER_NOTIF_DEL_METHOD IN VARCHAR2,
1490 X_GROUP_BY_ORG IN VARCHAR2,
1491 X_GROUP_BY_LEGAL_ENTITY IN VARCHAR2,
1492 X_GROUP_BY_INT_BANK_ACCOUNT IN VARCHAR2,
1493 X_GROUP_BY_SETTLEMENT_CURR IN VARCHAR2,
1494 X_GROUP_BY_SETTLEMENT_DATE IN VARCHAR2,
1495 X_LIMIT_BY_AMT_CURR IN VARCHAR2,
1496 X_LIMIT_BY_EXCH_RATE_TYPE IN VARCHAR2,
1497 X_LIMIT_BY_TOTAL_AMT in NUMBER,
1498 X_LIMIT_BY_SETTLEMENT_NUM in NUMBER,
1499 X_SETTLEMENT_COMPLETE_POINT IN VARCHAR2,
1500 X_OVERRIDE_PAYER_DELIVERY_FLAG IN VARCHAR2,
1501 X_ACCOMPANY_LETTER_FORMAT IN VARCHAR2,
1502 X_AUTOMATIC_PN_SUBMIT_FLAG IN VARCHAR2
1503 ) is
1504 begin
1505 update IBY_FNDCPT_SYS_EFT_PF_B set
1506 SETTLE_REQUIRE_VRFY_FLAG = X_SETTLE_REQUIRE_VRFY_FLAG,
1507 PAYMENT_SYSTEM_ID = X_PAYMENT_SYSTEM_ID,
1508 VERIFY_FORMAT_CODE = X_VERIFY_FORMAT_CODE,
1509 VERIFY_TRANS_PRTCL_CODE = X_VERIFY_TRANS_PRTCL_CODE,
1510 VERIFY_ACK_RDR_CODE = X_VERIFY_ACK_RDR_CODE,
1511 FUNDS_XFER_FORMAT_CODE = X_FUNDS_XFER_FORMAT_CODE,
1512 FUNDS_XFER_TRANS_PRTCL_CODE = X_FUNDS_XFER_TRANS_PRTCL_CODE,
1513 FUNDS_XFER_ACK_RDR_CODE = X_FUNDS_XFER_ACK_RDR_CODE,
1514 QUERY_FORMAT_CODE = X_QUERY_FORMAT_CODE,
1515 QUERY_TRANS_PRTCL_CODE = X_QUERY_TRANS_PRTCL_CODE,
1516 QUERY_ACK_RDR_CODE = X_QUERY_ACK_RDR_CODE,
1517 INACTIVE_DATE = X_INACTIVE_DATE,
1518 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1519 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1520 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1521 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1522 PAYER_NOTIFICATION_FORMAT = X_PAYER_NOTIFICATION_FORMAT,
1523 PAYER_NOTIFICATION_DEL_METHOD = X_PAYER_NOTIF_DEL_METHOD,
1524 GROUP_BY_ORG = X_GROUP_BY_ORG,
1525 GROUP_BY_LEGAL_ENTITY = X_GROUP_BY_LEGAL_ENTITY,
1526 GROUP_BY_INT_BANK_ACCOUNT = X_GROUP_BY_INT_BANK_ACCOUNT,
1527 GROUP_BY_SETTLEMENT_CURR = X_GROUP_BY_SETTLEMENT_CURR,
1528 GROUP_BY_SETTLEMENT_DATE = X_GROUP_BY_SETTLEMENT_DATE,
1529 LIMIT_BY_AMT_CURR = X_LIMIT_BY_AMT_CURR,
1530 LIMIT_BY_EXCH_RATE_TYPE = X_LIMIT_BY_EXCH_RATE_TYPE,
1531 LIMIT_BY_TOTAL_AMT = X_LIMIT_BY_TOTAL_AMT,
1532 LIMIT_BY_SETTLEMENT_NUM = X_LIMIT_BY_SETTLEMENT_NUM,
1533 SETTLEMENT_COMPLETE_POINT = X_SETTLEMENT_COMPLETE_POINT,
1534 OVERRIDE_PAYER_DELIVERY_FLAG = X_OVERRIDE_PAYER_DELIVERY_FLAG,
1535 ACCOMPANY_LETTER_FORMAT = X_ACCOMPANY_LETTER_FORMAT,
1536 AUTOMATIC_PN_SUBMIT_FLAG = X_AUTOMATIC_PN_SUBMIT_FLAG
1537 where SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE;
1538
1539 if (sql%notfound) then
1540 raise no_data_found;
1541 end if;
1542
1543 update IBY_FNDCPT_SYS_EFT_PF_TL set
1544 SYS_EFT_PROFILE_NAME = X_SYS_EFT_PROFILE_NAME,
1545 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1546 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1547 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1548 SOURCE_LANG = userenv('LANG')
1549 where SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE
1550 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1551
1552 if (sql%notfound) then
1553 raise no_data_found;
1554 end if;
1555 end SYS_EFT_PROF_UPDATE_ROW;
1556
1557 procedure SYS_EFT_PROF_DELETE_ROW (
1558 X_SYS_EFT_PROFILE_CODE in VARCHAR2
1559 ) is
1560 begin
1561 delete from IBY_FNDCPT_SYS_EFT_PF_TL
1562 where SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE;
1563
1564 if (sql%notfound) then
1565 raise no_data_found;
1566 end if;
1567
1568 delete from IBY_FNDCPT_SYS_EFT_PF_B
1569 where SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE;
1570
1571 if (sql%notfound) then
1572 raise no_data_found;
1573 end if;
1574 end SYS_EFT_PROF_DELETE_ROW;
1575
1576 procedure SYS_EFT_PROF_ADD_LANGUAGE
1577 is
1578 begin
1579 delete from IBY_FNDCPT_SYS_EFT_PF_TL T
1580 where not exists
1581 (select NULL
1582 from IBY_FNDCPT_SYS_EFT_PF_B B
1583 where B.SYS_EFT_PROFILE_CODE = T.SYS_EFT_PROFILE_CODE
1584 );
1585
1586 update IBY_FNDCPT_SYS_EFT_PF_TL T set (
1587 SYS_EFT_PROFILE_NAME
1588 ) = (select
1589 B.SYS_EFT_PROFILE_NAME
1590 from IBY_FNDCPT_SYS_EFT_PF_TL B
1591 where B.SYS_EFT_PROFILE_CODE = T.SYS_EFT_PROFILE_CODE
1592 and B.LANGUAGE = T.SOURCE_LANG)
1593 where (
1594 T.SYS_EFT_PROFILE_CODE,
1595 T.LANGUAGE
1596 ) in (select
1597 SUBT.SYS_EFT_PROFILE_CODE,
1598 SUBT.LANGUAGE
1599 from IBY_FNDCPT_SYS_EFT_PF_TL SUBB, IBY_FNDCPT_SYS_EFT_PF_TL SUBT
1600 where SUBB.SYS_EFT_PROFILE_CODE = SUBT.SYS_EFT_PROFILE_CODE
1601 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1602 and (SUBB.SYS_EFT_PROFILE_NAME <> SUBT.SYS_EFT_PROFILE_NAME
1603 ));
1604
1605 insert into IBY_FNDCPT_SYS_EFT_PF_TL (
1606 SYS_EFT_PROFILE_NAME,
1607 CREATED_BY,
1608 CREATION_DATE,
1609 LAST_UPDATED_BY,
1610 LAST_UPDATE_DATE,
1611 LAST_UPDATE_LOGIN,
1612 OBJECT_VERSION_NUMBER,
1613 SYS_EFT_PROFILE_CODE,
1614 LANGUAGE,
1615 SOURCE_LANG
1616 ) select /*+ ORDERED */
1617 B.SYS_EFT_PROFILE_NAME,
1618 B.CREATED_BY,
1619 B.CREATION_DATE,
1620 B.LAST_UPDATED_BY,
1621 B.LAST_UPDATE_DATE,
1622 B.LAST_UPDATE_LOGIN,
1623 B.OBJECT_VERSION_NUMBER,
1624 B.SYS_EFT_PROFILE_CODE,
1625 L.LANGUAGE_CODE,
1626 B.SOURCE_LANG
1627 from IBY_FNDCPT_SYS_EFT_PF_TL B, FND_LANGUAGES L
1628 where L.INSTALLED_FLAG in ('I', 'B')
1629 and B.LANGUAGE = userenv('LANG')
1630 and not exists
1631 (select NULL
1632 from IBY_FNDCPT_SYS_EFT_PF_TL T
1633 where T.SYS_EFT_PROFILE_CODE = B.SYS_EFT_PROFILE_CODE
1634 and T.LANGUAGE = L.LANGUAGE_CODE);
1635 end SYS_EFT_PROF_ADD_LANGUAGE;
1636
1637 procedure SYS_EFT_PROF_LOAD_ROW (
1638 X_SYS_EFT_PROFILE_CODE in VARCHAR2,
1639 X_SETTLE_REQUIRE_VRFY_FLAG in VARCHAR2,
1640 X_PAYMENT_SYSTEM_ID in NUMBER,
1641 X_VERIFY_FORMAT_CODE in VARCHAR2,
1642 X_VERIFY_TRANS_PRTCL_CODE in VARCHAR2,
1643 X_VERIFY_ACK_RDR_CODE in VARCHAR2,
1644 X_FUNDS_XFER_FORMAT_CODE in VARCHAR2,
1645 X_FUNDS_XFER_TRANS_PRTCL_CODE in VARCHAR2,
1646 X_FUNDS_XFER_ACK_RDR_CODE in VARCHAR2,
1647 X_QUERY_FORMAT_CODE in VARCHAR2,
1648 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1649 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1650 X_INACTIVE_DATE in DATE,
1651 X_OBJECT_VERSION_NUMBER in NUMBER,
1652 X_SYS_EFT_PROFILE_NAME in VARCHAR2,
1653 X_LAST_UPDATE_DATE in DATE,
1654 X_OWNER in VARCHAR2,
1655 X_PAYER_NOTIFICATION_FORMAT IN VARCHAR2,
1656 X_PAYER_NOTIF_DEL_METHOD IN VARCHAR2,
1657 X_GROUP_BY_ORG IN VARCHAR2,
1658 X_GROUP_BY_LEGAL_ENTITY IN VARCHAR2,
1659 X_GROUP_BY_INT_BANK_ACCOUNT IN VARCHAR2,
1660 X_GROUP_BY_SETTLEMENT_CURR IN VARCHAR2,
1661 X_GROUP_BY_SETTLEMENT_DATE IN VARCHAR2,
1662 X_LIMIT_BY_AMT_CURR IN VARCHAR2,
1663 X_LIMIT_BY_EXCH_RATE_TYPE IN VARCHAR2,
1664 X_LIMIT_BY_TOTAL_AMT in NUMBER,
1665 X_LIMIT_BY_SETTLEMENT_NUM in NUMBER,
1666 X_SETTLEMENT_COMPLETE_POINT IN VARCHAR2,
1667 X_OVERRIDE_PAYER_DELIVERY_FLAG IN VARCHAR2,
1668 X_ACCOMPANY_LETTER_FORMAT IN VARCHAR2,
1669 X_AUTOMATIC_PN_SUBMIT_FLAG IN VARCHAR2)
1670 is
1671 row_id VARCHAR2(200);
1672 begin
1673 SYS_EFT_PROF_UPDATE_ROW (
1674 X_SYS_EFT_PROFILE_CODE,
1675 X_SETTLE_REQUIRE_VRFY_FLAG,
1676 X_PAYMENT_SYSTEM_ID,
1677 X_VERIFY_FORMAT_CODE,
1678 X_VERIFY_TRANS_PRTCL_CODE,
1679 X_VERIFY_ACK_RDR_CODE,
1680 X_FUNDS_XFER_FORMAT_CODE,
1681 X_FUNDS_XFER_TRANS_PRTCL_CODE,
1682 X_FUNDS_XFER_ACK_RDR_CODE,
1683 X_QUERY_FORMAT_CODE,
1684 X_QUERY_TRANS_PRTCL_CODE,
1685 X_QUERY_ACK_RDR_CODE,
1686 X_INACTIVE_DATE,
1687 X_OBJECT_VERSION_NUMBER,
1688 X_SYS_EFT_PROFILE_NAME,
1689 X_LAST_UPDATE_DATE,
1690 fnd_load_util.owner_id(X_OWNER),
1691 fnd_load_util.owner_id(X_OWNER),
1692 X_PAYER_NOTIFICATION_FORMAT,
1693 X_PAYER_NOTIF_DEL_METHOD,
1694 X_GROUP_BY_ORG,
1695 X_GROUP_BY_LEGAL_ENTITY,
1696 X_GROUP_BY_INT_BANK_ACCOUNT,
1697 X_GROUP_BY_SETTLEMENT_CURR,
1698 X_GROUP_BY_SETTLEMENT_DATE,
1699 X_LIMIT_BY_AMT_CURR,
1700 X_LIMIT_BY_EXCH_RATE_TYPE,
1701 X_LIMIT_BY_TOTAL_AMT,
1702 X_LIMIT_BY_SETTLEMENT_NUM,
1703 X_SETTLEMENT_COMPLETE_POINT,
1704 X_OVERRIDE_PAYER_DELIVERY_FLAG,
1705 X_ACCOMPANY_LETTER_FORMAT,
1706 X_AUTOMATIC_PN_SUBMIT_FLAG
1707 );
1708 exception
1709 when no_data_found then
1710 SYS_EFT_PROF_INSERT_ROW (
1711 row_id,
1712 X_SYS_EFT_PROFILE_CODE,
1713 X_SETTLE_REQUIRE_VRFY_FLAG,
1714 X_PAYMENT_SYSTEM_ID,
1715 X_VERIFY_FORMAT_CODE,
1716 X_VERIFY_TRANS_PRTCL_CODE,
1717 X_VERIFY_ACK_RDR_CODE,
1718 X_FUNDS_XFER_FORMAT_CODE,
1719 X_FUNDS_XFER_TRANS_PRTCL_CODE,
1720 X_FUNDS_XFER_ACK_RDR_CODE,
1721 X_QUERY_FORMAT_CODE,
1722 X_QUERY_TRANS_PRTCL_CODE,
1723 X_QUERY_ACK_RDR_CODE,
1724 X_INACTIVE_DATE,
1725 X_OBJECT_VERSION_NUMBER,
1726 X_SYS_EFT_PROFILE_NAME,
1727 X_LAST_UPDATE_DATE,
1728 fnd_load_util.owner_id(X_OWNER),
1729 X_LAST_UPDATE_DATE,
1730 fnd_load_util.owner_id(X_OWNER),
1731 fnd_load_util.owner_id(X_OWNER),
1732 X_PAYER_NOTIFICATION_FORMAT,
1733 X_PAYER_NOTIF_DEL_METHOD,
1734 X_GROUP_BY_ORG,
1735 X_GROUP_BY_LEGAL_ENTITY,
1736 X_GROUP_BY_INT_BANK_ACCOUNT,
1737 X_GROUP_BY_SETTLEMENT_CURR,
1738 X_GROUP_BY_SETTLEMENT_DATE,
1739 X_LIMIT_BY_AMT_CURR,
1740 X_LIMIT_BY_EXCH_RATE_TYPE,
1741 X_LIMIT_BY_TOTAL_AMT,
1742 X_LIMIT_BY_SETTLEMENT_NUM,
1743 X_SETTLEMENT_COMPLETE_POINT,
1744 X_OVERRIDE_PAYER_DELIVERY_FLAG,
1745 X_ACCOMPANY_LETTER_FORMAT,
1746 X_AUTOMATIC_PN_SUBMIT_FLAG
1747 );
1748 end;
1749
1750 procedure SYS_EFT_PROF_TRANSLATE_ROW (
1751 X_SYS_EFT_PROFILE_CODE in VARCHAR2,
1752 X_OBJECT_VERSION_NUMBER in NUMBER,
1753 X_SYS_EFT_PROFILE_NAME in VARCHAR2,
1754 X_LAST_UPDATE_DATE in DATE,
1755 X_OWNER in VARCHAR2)
1756 is
1757 begin
1758 update iby_fndcpt_sys_eft_pf_tl set
1759 SYS_EFT_PROFILE_NAME = X_SYS_EFT_PROFILE_NAME,
1760 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1761 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
1762 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1763 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
1764 SOURCE_LANG = userenv('LANG')
1765 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1766 and SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE;
1767 end;
1768
1769
1770 procedure SYS_DC_PROF_INSERT_ROW (
1771 X_ROWID in out NOCOPY VARCHAR2,
1772 X_SYS_DC_PROFILE_CODE in VARCHAR2,
1773 X_ONLINE_DEB_FORMAT_CODE in VARCHAR2,
1774 X_ONLINE_DEB_TRANS_PRTCL_CODE in VARCHAR2,
1775 X_ONLINE_DEB_ACK_RDR_CODE in VARCHAR2,
1776 X_SETTLEMENT_REQ_FLAG in VARCHAR2,
1777 X_SETTLEMENT_FORMAT_CODE in VARCHAR2,
1778 X_SETTLEMENT_TRANS_PRTCL_CODE in VARCHAR2,
1779 X_SETTLEMENT_ACK_RDR_CODE in VARCHAR2,
1780 X_QUERY_FORMAT_CODE in VARCHAR2,
1781 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1782 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1783 X_INACTIVE_DATE in DATE,
1784 X_OBJECT_VERSION_NUMBER in NUMBER,
1785 X_PAYMENT_SYSTEM_ID in NUMBER,
1786 X_SYS_DC_PROFILE_NAME in VARCHAR2,
1787 X_CREATION_DATE in DATE,
1788 X_CREATED_BY in NUMBER,
1789 X_LAST_UPDATE_DATE in DATE,
1790 X_LAST_UPDATED_BY in NUMBER,
1791 X_LAST_UPDATE_LOGIN in NUMBER
1792 ) is
1793 cursor C is select ROWID from IBY_FNDCPT_SYS_DC_PF_B
1794 where SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE
1795 ;
1796 begin
1797 insert into IBY_FNDCPT_SYS_DC_PF_B (
1798 ONLINE_DEB_FORMAT_CODE,
1799 ONLINE_DEB_TRANS_PRTCL_CODE,
1800 ONLINE_DEB_ACK_RDR_CODE,
1801 SETTLEMENT_REQ_FLAG,
1802 SETTLEMENT_FORMAT_CODE,
1803 SETTLEMENT_TRANS_PRTCL_CODE,
1804 SETTLEMENT_ACK_RDR_CODE,
1805 QUERY_FORMAT_CODE,
1806 QUERY_TRANS_PRTCL_CODE,
1807 QUERY_ACK_RDR_CODE,
1808 INACTIVE_DATE,
1809 OBJECT_VERSION_NUMBER,
1810 SYS_DC_PROFILE_CODE,
1811 PAYMENT_SYSTEM_ID,
1812 CREATION_DATE,
1813 CREATED_BY,
1814 LAST_UPDATE_DATE,
1815 LAST_UPDATED_BY,
1816 LAST_UPDATE_LOGIN
1817 ) values (
1818 X_ONLINE_DEB_FORMAT_CODE,
1819 X_ONLINE_DEB_TRANS_PRTCL_CODE,
1820 X_ONLINE_DEB_ACK_RDR_CODE,
1821 X_SETTLEMENT_REQ_FLAG,
1822 X_SETTLEMENT_FORMAT_CODE,
1823 X_SETTLEMENT_TRANS_PRTCL_CODE,
1824 X_SETTLEMENT_ACK_RDR_CODE,
1825 X_QUERY_FORMAT_CODE,
1826 X_QUERY_TRANS_PRTCL_CODE,
1827 X_QUERY_ACK_RDR_CODE,
1828 X_INACTIVE_DATE,
1829 X_OBJECT_VERSION_NUMBER,
1830 X_SYS_DC_PROFILE_CODE,
1831 X_PAYMENT_SYSTEM_ID,
1832 X_CREATION_DATE,
1833 X_CREATED_BY,
1834 X_LAST_UPDATE_DATE,
1835 X_LAST_UPDATED_BY,
1836 X_LAST_UPDATE_LOGIN
1837 );
1838
1839 insert into IBY_FNDCPT_SYS_DC_PF_TL (
1840 SYS_DC_PROFILE_CODE,
1841 SYS_DC_PROFILE_NAME,
1842 CREATED_BY,
1843 CREATION_DATE,
1844 LAST_UPDATED_BY,
1845 LAST_UPDATE_DATE,
1846 LAST_UPDATE_LOGIN,
1847 OBJECT_VERSION_NUMBER,
1848 LANGUAGE,
1849 SOURCE_LANG
1850 ) select
1851 X_SYS_DC_PROFILE_CODE,
1852 X_SYS_DC_PROFILE_NAME,
1853 X_CREATED_BY,
1854 X_CREATION_DATE,
1855 X_LAST_UPDATED_BY,
1856 X_LAST_UPDATE_DATE,
1857 X_LAST_UPDATE_LOGIN,
1858 X_OBJECT_VERSION_NUMBER,
1859 L.LANGUAGE_CODE,
1860 userenv('LANG')
1861 from FND_LANGUAGES L
1862 where L.INSTALLED_FLAG in ('I', 'B')
1863 and not exists
1864 (select NULL
1865 from IBY_FNDCPT_SYS_DC_PF_TL T
1866 where T.SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE
1867 and T.LANGUAGE = L.LANGUAGE_CODE);
1868
1869 open c;
1870 fetch c into X_ROWID;
1871 if (c%notfound) then
1872 close c;
1873 raise no_data_found;
1874 end if;
1875 close c;
1876
1877 end SYS_DC_PROF_INSERT_ROW;
1878
1879 procedure SYS_DC_PROF_UPDATE_ROW (
1880 X_SYS_DC_PROFILE_CODE in VARCHAR2,
1881 X_ONLINE_DEB_FORMAT_CODE in VARCHAR2,
1882 X_ONLINE_DEB_TRANS_PRTCL_CODE in VARCHAR2,
1883 X_ONLINE_DEB_ACK_RDR_CODE in VARCHAR2,
1884 X_SETTLEMENT_REQ_FLAG in VARCHAR2,
1885 X_SETTLEMENT_FORMAT_CODE in VARCHAR2,
1886 X_SETTLEMENT_TRANS_PRTCL_CODE in VARCHAR2,
1887 X_SETTLEMENT_ACK_RDR_CODE in VARCHAR2,
1888 X_QUERY_FORMAT_CODE in VARCHAR2,
1889 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
1890 X_QUERY_ACK_RDR_CODE in VARCHAR2,
1891 X_INACTIVE_DATE in DATE,
1892 X_OBJECT_VERSION_NUMBER in NUMBER,
1893 X_PAYMENT_SYSTEM_ID in NUMBER,
1894 X_SYS_DC_PROFILE_NAME in VARCHAR2,
1895 X_LAST_UPDATE_DATE in DATE,
1896 X_LAST_UPDATED_BY in NUMBER,
1897 X_LAST_UPDATE_LOGIN in NUMBER
1898 ) is
1899 begin
1900 update IBY_FNDCPT_SYS_DC_PF_B set
1901 ONLINE_DEB_FORMAT_CODE = X_ONLINE_DEB_FORMAT_CODE,
1902 ONLINE_DEB_TRANS_PRTCL_CODE = X_ONLINE_DEB_TRANS_PRTCL_CODE,
1903 ONLINE_DEB_ACK_RDR_CODE = X_ONLINE_DEB_ACK_RDR_CODE,
1904 SETTLEMENT_REQ_FLAG = X_SETTLEMENT_REQ_FLAG,
1905 SETTLEMENT_FORMAT_CODE = X_SETTLEMENT_FORMAT_CODE,
1906 SETTLEMENT_TRANS_PRTCL_CODE = X_SETTLEMENT_TRANS_PRTCL_CODE,
1907 SETTLEMENT_ACK_RDR_CODE = X_SETTLEMENT_ACK_RDR_CODE,
1908 QUERY_FORMAT_CODE = X_QUERY_FORMAT_CODE,
1909 QUERY_TRANS_PRTCL_CODE = X_QUERY_TRANS_PRTCL_CODE,
1910 QUERY_ACK_RDR_CODE = X_QUERY_ACK_RDR_CODE,
1911 INACTIVE_DATE = X_INACTIVE_DATE,
1912 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1913 PAYMENT_SYSTEM_ID = X_PAYMENT_SYSTEM_ID,
1914 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1915 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1916 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1917 where SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE;
1918
1919 if (sql%notfound) then
1920 raise no_data_found;
1921 end if;
1922
1923 update IBY_FNDCPT_SYS_DC_PF_TL set
1924 SYS_DC_PROFILE_NAME = X_SYS_DC_PROFILE_NAME,
1925 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1926 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1927 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1928 SOURCE_LANG = userenv('LANG')
1929 where SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE
1930 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1931
1932 if (sql%notfound) then
1933 raise no_data_found;
1934 end if;
1935 end SYS_DC_PROF_UPDATE_ROW;
1936
1937 procedure SYS_DC_PROF_DELETE_ROW (
1938 X_SYS_DC_PROFILE_CODE in VARCHAR2
1939 ) is
1940 begin
1941 delete from IBY_FNDCPT_SYS_DC_PF_TL
1942 where SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE;
1943
1944 if (sql%notfound) then
1945 raise no_data_found;
1946 end if;
1947
1948 delete from IBY_FNDCPT_SYS_DC_PF_B
1949 where SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE;
1950
1951 if (sql%notfound) then
1952 raise no_data_found;
1953 end if;
1954 end SYS_DC_PROF_DELETE_ROW;
1955
1956 procedure SYS_DC_PROF_ADD_LANGUAGE
1957 is
1958 begin
1959 delete from IBY_FNDCPT_SYS_DC_PF_TL T
1960 where not exists
1961 (select NULL
1962 from IBY_FNDCPT_SYS_DC_PF_B B
1963 where B.SYS_DC_PROFILE_CODE = T.SYS_DC_PROFILE_CODE
1964 );
1965
1966 update IBY_FNDCPT_SYS_DC_PF_TL T set (
1967 SYS_DC_PROFILE_NAME
1968 ) = (select
1969 B.SYS_DC_PROFILE_NAME
1970 from IBY_FNDCPT_SYS_DC_PF_TL B
1971 where B.SYS_DC_PROFILE_CODE = T.SYS_DC_PROFILE_CODE
1972 and B.LANGUAGE = T.SOURCE_LANG)
1973 where (
1974 T.SYS_DC_PROFILE_CODE,
1975 T.LANGUAGE
1976 ) in (select
1977 SUBT.SYS_DC_PROFILE_CODE,
1978 SUBT.LANGUAGE
1979 from IBY_FNDCPT_SYS_DC_PF_TL SUBB, IBY_FNDCPT_SYS_DC_PF_TL SUBT
1980 where SUBB.SYS_DC_PROFILE_CODE = SUBT.SYS_DC_PROFILE_CODE
1981 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1982 and (SUBB.SYS_DC_PROFILE_NAME <> SUBT.SYS_DC_PROFILE_NAME
1983 ));
1984
1985 insert into IBY_FNDCPT_SYS_DC_PF_TL (
1986 SYS_DC_PROFILE_CODE,
1987 SYS_DC_PROFILE_NAME,
1988 CREATED_BY,
1989 CREATION_DATE,
1990 LAST_UPDATED_BY,
1991 LAST_UPDATE_DATE,
1992 LAST_UPDATE_LOGIN,
1993 OBJECT_VERSION_NUMBER,
1994 LANGUAGE,
1995 SOURCE_LANG
1996 ) select /*+ ORDERED */
1997 B.SYS_DC_PROFILE_CODE,
1998 B.SYS_DC_PROFILE_NAME,
1999 B.CREATED_BY,
2000 B.CREATION_DATE,
2001 B.LAST_UPDATED_BY,
2002 B.LAST_UPDATE_DATE,
2003 B.LAST_UPDATE_LOGIN,
2004 B.OBJECT_VERSION_NUMBER,
2005 L.LANGUAGE_CODE,
2006 B.SOURCE_LANG
2007 from IBY_FNDCPT_SYS_DC_PF_TL B, FND_LANGUAGES L
2008 where L.INSTALLED_FLAG in ('I', 'B')
2009 and B.LANGUAGE = userenv('LANG')
2010 and not exists
2011 (select NULL
2012 from IBY_FNDCPT_SYS_DC_PF_TL T
2013 where T.SYS_DC_PROFILE_CODE = B.SYS_DC_PROFILE_CODE
2014 and T.LANGUAGE = L.LANGUAGE_CODE);
2015 end SYS_DC_PROF_ADD_LANGUAGE;
2016
2017 procedure SYS_DC_PROF_LOAD_ROW (
2018 X_SYS_DC_PROFILE_CODE in VARCHAR2,
2019 X_ONLINE_DEB_FORMAT_CODE in VARCHAR2,
2020 X_ONLINE_DEB_TRANS_PRTCL_CODE in VARCHAR2,
2021 X_ONLINE_DEB_ACK_RDR_CODE in VARCHAR2,
2022 X_SETTLEMENT_REQ_FLAG in VARCHAR2,
2023 X_SETTLEMENT_FORMAT_CODE in VARCHAR2,
2024 X_SETTLEMENT_TRANS_PRTCL_CODE in VARCHAR2,
2025 X_SETTLEMENT_ACK_RDR_CODE in VARCHAR2,
2026 X_QUERY_FORMAT_CODE in VARCHAR2,
2027 X_QUERY_TRANS_PRTCL_CODE in VARCHAR2,
2028 X_QUERY_ACK_RDR_CODE in VARCHAR2,
2029 X_INACTIVE_DATE in DATE,
2030 X_OBJECT_VERSION_NUMBER in NUMBER,
2031 X_PAYMENT_SYSTEM_ID in NUMBER,
2032 X_SYS_DC_PROFILE_NAME in VARCHAR2,
2033 X_LAST_UPDATE_DATE in DATE,
2034 X_OWNER in VARCHAR2)
2035 is
2036 row_id VARCHAR2(200);
2037 begin
2038 SYS_DC_PROF_UPDATE_ROW (
2039 X_SYS_DC_PROFILE_CODE,
2040 X_ONLINE_DEB_FORMAT_CODE,
2041 X_ONLINE_DEB_TRANS_PRTCL_CODE,
2042 X_ONLINE_DEB_ACK_RDR_CODE,
2043 X_SETTLEMENT_REQ_FLAG,
2044 X_SETTLEMENT_FORMAT_CODE,
2045 X_SETTLEMENT_TRANS_PRTCL_CODE,
2046 X_SETTLEMENT_ACK_RDR_CODE,
2047 X_QUERY_FORMAT_CODE,
2048 X_QUERY_TRANS_PRTCL_CODE,
2049 X_QUERY_ACK_RDR_CODE,
2050 X_INACTIVE_DATE,
2051 X_OBJECT_VERSION_NUMBER,
2052 X_PAYMENT_SYSTEM_ID,
2053 X_SYS_DC_PROFILE_NAME,
2054 X_LAST_UPDATE_DATE,
2055 fnd_load_util.owner_id(X_OWNER),
2056 fnd_load_util.owner_id(X_OWNER));
2057 exception
2058 when no_data_found then
2059 SYS_DC_PROF_INSERT_ROW (
2060 row_id,
2061 X_SYS_DC_PROFILE_CODE,
2062 X_ONLINE_DEB_FORMAT_CODE,
2063 X_ONLINE_DEB_TRANS_PRTCL_CODE,
2064 X_ONLINE_DEB_ACK_RDR_CODE,
2065 X_SETTLEMENT_REQ_FLAG,
2066 X_SETTLEMENT_FORMAT_CODE,
2067 X_SETTLEMENT_TRANS_PRTCL_CODE,
2068 X_SETTLEMENT_ACK_RDR_CODE,
2069 X_QUERY_FORMAT_CODE,
2070 X_QUERY_TRANS_PRTCL_CODE,
2071 X_QUERY_ACK_RDR_CODE,
2072 X_INACTIVE_DATE,
2073 X_OBJECT_VERSION_NUMBER,
2074 X_PAYMENT_SYSTEM_ID,
2075 X_SYS_DC_PROFILE_NAME,
2076 X_LAST_UPDATE_DATE,
2077 fnd_load_util.owner_id(X_OWNER),
2078 X_LAST_UPDATE_DATE,
2079 fnd_load_util.owner_id(X_OWNER),
2080 fnd_load_util.owner_id(X_OWNER));
2081 end;
2082
2083 procedure SYS_DC_PROF_TRANSLATE_ROW (
2084 X_SYS_DC_PROFILE_CODE in VARCHAR2,
2085 X_OBJECT_VERSION_NUMBER in NUMBER,
2086 X_SYS_DC_PROFILE_NAME in VARCHAR2,
2087 X_LAST_UPDATE_DATE in DATE,
2088 X_OWNER in VARCHAR2)
2089 is
2090 begin
2091 update iby_fndcpt_sys_dc_pf_tl set
2092 SYS_DC_PROFILE_NAME = X_SYS_DC_PROFILE_NAME,
2093 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
2094 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
2095 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2096 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
2097 SOURCE_LANG = userenv('LANG')
2098 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
2099 and SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE;
2100 end;
2101
2102
2103 procedure USER_CC_PROF_INSERT_ROW (
2104 X_ROWID in out NOCOPY VARCHAR2,
2105 X_USER_CC_PROFILE_CODE in VARCHAR2,
2106 X_OBJECT_VERSION_NUMBER in NUMBER,
2107 X_BEP_ACCOUNT_ID in NUMBER,
2108 X_SYS_CC_PROFILE_CODE in VARCHAR2,
2109 X_INACTIVE_DATE in DATE,
2110 X_QUERY_TRANS_CONFIG_ID in NUMBER,
2111 X_ONLINE_AUTH_TRANS_CONFIG_ID in NUMBER,
2112 X_SETTLEMENT_TRANS_CONFIG_ID in NUMBER,
2113 X_USER_CC_PROFILE_NAME in VARCHAR2,
2114 X_CREATION_DATE in DATE,
2115 X_CREATED_BY in NUMBER,
2116 X_LAST_UPDATE_DATE in DATE,
2117 X_LAST_UPDATED_BY in NUMBER,
2118 X_LAST_UPDATE_LOGIN in NUMBER
2119 ) is
2120 cursor C is select ROWID from IBY_FNDCPT_USER_CC_PF_B
2121 where USER_CC_PROFILE_CODE = X_USER_CC_PROFILE_CODE
2122 ;
2123 begin
2124 insert into IBY_FNDCPT_USER_CC_PF_B (
2125 OBJECT_VERSION_NUMBER,
2126 BEP_ACCOUNT_ID,
2127 USER_CC_PROFILE_CODE,
2128 SYS_CC_PROFILE_CODE,
2129 INACTIVE_DATE,
2130 QUERY_TRANS_CONFIG_ID,
2131 ONLINE_AUTH_TRANS_CONFIG_ID,
2132 SETTLEMENT_TRANS_CONFIG_ID,
2133 CREATION_DATE,
2134 CREATED_BY,
2135 LAST_UPDATE_DATE,
2136 LAST_UPDATED_BY,
2137 LAST_UPDATE_LOGIN
2138 ) values (
2139 X_OBJECT_VERSION_NUMBER,
2140 X_BEP_ACCOUNT_ID,
2141 X_USER_CC_PROFILE_CODE,
2142 X_SYS_CC_PROFILE_CODE,
2143 X_INACTIVE_DATE,
2144 X_QUERY_TRANS_CONFIG_ID,
2145 X_ONLINE_AUTH_TRANS_CONFIG_ID,
2146 X_SETTLEMENT_TRANS_CONFIG_ID,
2147 X_CREATION_DATE,
2148 X_CREATED_BY,
2149 X_LAST_UPDATE_DATE,
2150 X_LAST_UPDATED_BY,
2151 X_LAST_UPDATE_LOGIN
2152 );
2153
2154 insert into IBY_FNDCPT_USER_CC_PF_TL (
2155 CREATION_DATE,
2156 LAST_UPDATED_BY,
2157 LAST_UPDATE_DATE,
2158 LAST_UPDATE_LOGIN,
2159 OBJECT_VERSION_NUMBER,
2160 USER_CC_PROFILE_CODE,
2161 USER_CC_PROFILE_NAME,
2162 CREATED_BY,
2163 LANGUAGE,
2164 SOURCE_LANG
2165 ) select
2166 X_CREATION_DATE,
2167 X_LAST_UPDATED_BY,
2168 X_LAST_UPDATE_DATE,
2169 X_LAST_UPDATE_LOGIN,
2170 X_OBJECT_VERSION_NUMBER,
2171 X_USER_CC_PROFILE_CODE,
2172 X_USER_CC_PROFILE_NAME,
2173 X_CREATED_BY,
2174 L.LANGUAGE_CODE,
2175 userenv('LANG')
2176 from FND_LANGUAGES L
2177 where L.INSTALLED_FLAG in ('I', 'B')
2178 and not exists
2179 (select NULL
2180 from IBY_FNDCPT_USER_CC_PF_TL T
2181 where T.USER_CC_PROFILE_CODE = X_USER_CC_PROFILE_CODE
2182 and T.LANGUAGE = L.LANGUAGE_CODE);
2183
2184 open c;
2185 fetch c into X_ROWID;
2186 if (c%notfound) then
2187 close c;
2188 raise no_data_found;
2189 end if;
2190 close c;
2191
2192 end USER_CC_PROF_INSERT_ROW;
2193
2194 procedure USER_CC_PROF_UPDATE_ROW (
2195 X_USER_CC_PROFILE_CODE in VARCHAR2,
2196 X_OBJECT_VERSION_NUMBER in NUMBER,
2197 X_BEP_ACCOUNT_ID in NUMBER,
2198 X_SYS_CC_PROFILE_CODE in VARCHAR2,
2199 X_INACTIVE_DATE in DATE,
2200 X_QUERY_TRANS_CONFIG_ID in NUMBER,
2201 X_ONLINE_AUTH_TRANS_CONFIG_ID in NUMBER,
2202 X_SETTLEMENT_TRANS_CONFIG_ID in NUMBER,
2203 X_USER_CC_PROFILE_NAME in VARCHAR2,
2204 X_LAST_UPDATE_DATE in DATE,
2205 X_LAST_UPDATED_BY in NUMBER,
2206 X_LAST_UPDATE_LOGIN in NUMBER
2207 ) is
2208 begin
2209 update IBY_FNDCPT_USER_CC_PF_B set
2210 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
2211 BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID,
2212 SYS_CC_PROFILE_CODE = X_SYS_CC_PROFILE_CODE,
2213 INACTIVE_DATE = X_INACTIVE_DATE,
2214 QUERY_TRANS_CONFIG_ID = X_QUERY_TRANS_CONFIG_ID,
2215 ONLINE_AUTH_TRANS_CONFIG_ID = X_ONLINE_AUTH_TRANS_CONFIG_ID,
2216 SETTLEMENT_TRANS_CONFIG_ID = X_SETTLEMENT_TRANS_CONFIG_ID,
2217 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2218 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2219 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
2220 where USER_CC_PROFILE_CODE = X_USER_CC_PROFILE_CODE;
2221
2222 if (sql%notfound) then
2223 raise no_data_found;
2224 end if;
2225
2226 update IBY_FNDCPT_USER_CC_PF_TL set
2227 USER_CC_PROFILE_NAME = X_USER_CC_PROFILE_NAME,
2228 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2229 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2230 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2231 SOURCE_LANG = userenv('LANG')
2232 where USER_CC_PROFILE_CODE = X_USER_CC_PROFILE_CODE
2233 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2234
2235 if (sql%notfound) then
2236 raise no_data_found;
2237 end if;
2238 end USER_CC_PROF_UPDATE_ROW;
2239
2240 procedure USER_CC_PROF_DELETE_ROW (
2241 X_USER_CC_PROFILE_CODE in VARCHAR2
2242 ) is
2243 begin
2244 delete from IBY_FNDCPT_USER_CC_PF_TL
2245 where USER_CC_PROFILE_CODE = X_USER_CC_PROFILE_CODE;
2246
2247 if (sql%notfound) then
2248 raise no_data_found;
2249 end if;
2250
2251 delete from IBY_FNDCPT_USER_CC_PF_B
2252 where USER_CC_PROFILE_CODE = X_USER_CC_PROFILE_CODE;
2253
2254 if (sql%notfound) then
2255 raise no_data_found;
2256 end if;
2257 end USER_CC_PROF_DELETE_ROW;
2258
2259 procedure USER_CC_PROF_ADD_LANGUAGE
2260 is
2261 begin
2262 delete from IBY_FNDCPT_USER_CC_PF_TL T
2263 where not exists
2264 (select NULL
2265 from IBY_FNDCPT_USER_CC_PF_B B
2266 where B.USER_CC_PROFILE_CODE = T.USER_CC_PROFILE_CODE
2267 );
2268
2269 update IBY_FNDCPT_USER_CC_PF_TL T set (
2270 USER_CC_PROFILE_NAME
2271 ) = (select
2272 B.USER_CC_PROFILE_NAME
2273 from IBY_FNDCPT_USER_CC_PF_TL B
2274 where B.USER_CC_PROFILE_CODE = T.USER_CC_PROFILE_CODE
2275 and B.LANGUAGE = T.SOURCE_LANG)
2276 where (
2277 T.USER_CC_PROFILE_CODE,
2278 T.LANGUAGE
2279 ) in (select
2280 SUBT.USER_CC_PROFILE_CODE,
2281 SUBT.LANGUAGE
2282 from IBY_FNDCPT_USER_CC_PF_TL SUBB, IBY_FNDCPT_USER_CC_PF_TL SUBT
2283 where SUBB.USER_CC_PROFILE_CODE = SUBT.USER_CC_PROFILE_CODE
2284 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2285 and (SUBB.USER_CC_PROFILE_NAME <> SUBT.USER_CC_PROFILE_NAME
2286 ));
2287
2288 insert into IBY_FNDCPT_USER_CC_PF_TL (
2289 CREATION_DATE,
2290 LAST_UPDATED_BY,
2291 LAST_UPDATE_DATE,
2292 LAST_UPDATE_LOGIN,
2293 OBJECT_VERSION_NUMBER,
2294 USER_CC_PROFILE_CODE,
2295 USER_CC_PROFILE_NAME,
2296 CREATED_BY,
2297 LANGUAGE,
2298 SOURCE_LANG
2299 ) select /*+ ORDERED */
2300 B.CREATION_DATE,
2301 B.LAST_UPDATED_BY,
2302 B.LAST_UPDATE_DATE,
2303 B.LAST_UPDATE_LOGIN,
2304 B.OBJECT_VERSION_NUMBER,
2305 B.USER_CC_PROFILE_CODE,
2306 B.USER_CC_PROFILE_NAME,
2307 B.CREATED_BY,
2308 L.LANGUAGE_CODE,
2309 B.SOURCE_LANG
2310 from IBY_FNDCPT_USER_CC_PF_TL B, FND_LANGUAGES L
2311 where L.INSTALLED_FLAG in ('I', 'B')
2312 and B.LANGUAGE = userenv('LANG')
2313 and not exists
2314 (select NULL
2315 from IBY_FNDCPT_USER_CC_PF_TL T
2316 where T.USER_CC_PROFILE_CODE = B.USER_CC_PROFILE_CODE
2317 and T.LANGUAGE = L.LANGUAGE_CODE);
2318 end USER_CC_PROF_ADD_LANGUAGE;
2319
2320 procedure USER_EFT_PROF_INSERT_ROW (
2321 X_ROWID in out NOCOPY VARCHAR2,
2322 X_USER_EFT_PROFILE_CODE in VARCHAR2,
2323 X_OBJECT_VERSION_NUMBER in NUMBER,
2324 X_BEP_ACCOUNT_ID in NUMBER,
2325 X_VERIFY_TRANS_CONFIG_ID in NUMBER,
2326 X_SYS_EFT_PROFILE_CODE in VARCHAR2,
2327 X_INACTIVE_DATE in DATE,
2328 X_FUNDS_XFER_TRANS_CONFIG_ID in NUMBER,
2329 X_QUERY_TRANS_CONFIG_ID in NUMBER,
2330 X_USER_EFT_PROFILE_NAME in VARCHAR2,
2331 X_CREATION_DATE in DATE,
2332 X_CREATED_BY in NUMBER,
2333 X_LAST_UPDATE_DATE in DATE,
2334 X_LAST_UPDATED_BY in NUMBER,
2335 X_LAST_UPDATE_LOGIN in NUMBER
2336 ) is
2337 cursor C is select ROWID from IBY_FNDCPT_USER_EFT_PF_B
2338 where USER_EFT_PROFILE_CODE = X_USER_EFT_PROFILE_CODE
2339 ;
2340 begin
2341 insert into IBY_FNDCPT_USER_EFT_PF_B (
2342 OBJECT_VERSION_NUMBER,
2343 BEP_ACCOUNT_ID,
2344 VERIFY_TRANS_CONFIG_ID,
2345 USER_EFT_PROFILE_CODE,
2346 SYS_EFT_PROFILE_CODE,
2347 INACTIVE_DATE,
2348 FUNDS_XFER_TRANS_CONFIG_ID,
2349 QUERY_TRANS_CONFIG_ID,
2350 CREATION_DATE,
2351 CREATED_BY,
2352 LAST_UPDATE_DATE,
2353 LAST_UPDATED_BY,
2354 LAST_UPDATE_LOGIN
2355 ) values (
2356 X_OBJECT_VERSION_NUMBER,
2357 X_BEP_ACCOUNT_ID,
2358 X_VERIFY_TRANS_CONFIG_ID,
2359 X_USER_EFT_PROFILE_CODE,
2360 X_SYS_EFT_PROFILE_CODE,
2361 X_INACTIVE_DATE,
2362 X_FUNDS_XFER_TRANS_CONFIG_ID,
2363 X_QUERY_TRANS_CONFIG_ID,
2364 X_CREATION_DATE,
2365 X_CREATED_BY,
2366 X_LAST_UPDATE_DATE,
2367 X_LAST_UPDATED_BY,
2368 X_LAST_UPDATE_LOGIN
2369 );
2370
2371 insert into IBY_FNDCPT_USER_EFT_PF_TL (
2372 USER_EFT_PROFILE_CODE,
2373 USER_EFT_PROFILE_NAME,
2374 CREATED_BY,
2375 CREATION_DATE,
2376 LAST_UPDATED_BY,
2377 LAST_UPDATE_DATE,
2378 LAST_UPDATE_LOGIN,
2379 OBJECT_VERSION_NUMBER,
2380 LANGUAGE,
2381 SOURCE_LANG
2382 ) select
2383 X_USER_EFT_PROFILE_CODE,
2384 X_USER_EFT_PROFILE_NAME,
2385 X_CREATED_BY,
2386 X_CREATION_DATE,
2387 X_LAST_UPDATED_BY,
2388 X_LAST_UPDATE_DATE,
2389 X_LAST_UPDATE_LOGIN,
2390 X_OBJECT_VERSION_NUMBER,
2391 L.LANGUAGE_CODE,
2392 userenv('LANG')
2393 from FND_LANGUAGES L
2394 where L.INSTALLED_FLAG in ('I', 'B')
2395 and not exists
2396 (select NULL
2397 from IBY_FNDCPT_USER_EFT_PF_TL T
2398 where T.USER_EFT_PROFILE_CODE = X_USER_EFT_PROFILE_CODE
2399 and T.LANGUAGE = L.LANGUAGE_CODE);
2400
2401 open c;
2402 fetch c into X_ROWID;
2403 if (c%notfound) then
2404 close c;
2405 raise no_data_found;
2406 end if;
2407 close c;
2408
2409 end USER_EFT_PROF_INSERT_ROW;
2410
2411 procedure USER_EFT_PROF_UPDATE_ROW (
2412 X_USER_EFT_PROFILE_CODE in VARCHAR2,
2413 X_OBJECT_VERSION_NUMBER in NUMBER,
2414 X_BEP_ACCOUNT_ID in NUMBER,
2415 X_VERIFY_TRANS_CONFIG_ID in NUMBER,
2416 X_SYS_EFT_PROFILE_CODE in VARCHAR2,
2417 X_INACTIVE_DATE in DATE,
2418 X_FUNDS_XFER_TRANS_CONFIG_ID in NUMBER,
2419 X_QUERY_TRANS_CONFIG_ID in NUMBER,
2420 X_USER_EFT_PROFILE_NAME in VARCHAR2,
2421 X_LAST_UPDATE_DATE in DATE,
2422 X_LAST_UPDATED_BY in NUMBER,
2423 X_LAST_UPDATE_LOGIN in NUMBER
2424 ) is
2425 begin
2426 update IBY_FNDCPT_USER_EFT_PF_B set
2427 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
2428 BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID,
2429 VERIFY_TRANS_CONFIG_ID = X_VERIFY_TRANS_CONFIG_ID,
2430 SYS_EFT_PROFILE_CODE = X_SYS_EFT_PROFILE_CODE,
2431 INACTIVE_DATE = X_INACTIVE_DATE,
2432 FUNDS_XFER_TRANS_CONFIG_ID = X_FUNDS_XFER_TRANS_CONFIG_ID,
2433 QUERY_TRANS_CONFIG_ID = X_QUERY_TRANS_CONFIG_ID,
2434 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2435 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2436 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
2437 where USER_EFT_PROFILE_CODE = X_USER_EFT_PROFILE_CODE;
2438
2439 if (sql%notfound) then
2440 raise no_data_found;
2441 end if;
2442
2443 update IBY_FNDCPT_USER_EFT_PF_TL set
2444 USER_EFT_PROFILE_NAME = X_USER_EFT_PROFILE_NAME,
2445 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2446 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2447 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2448 SOURCE_LANG = userenv('LANG')
2449 where USER_EFT_PROFILE_CODE = X_USER_EFT_PROFILE_CODE
2450 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2451
2452 if (sql%notfound) then
2453 raise no_data_found;
2454 end if;
2455 end USER_EFT_PROF_UPDATE_ROW;
2456
2457 procedure USER_EFT_PROF_DELETE_ROW (
2458 X_USER_EFT_PROFILE_CODE in VARCHAR2
2459 ) is
2460 begin
2461 delete from IBY_FNDCPT_USER_EFT_PF_TL
2462 where USER_EFT_PROFILE_CODE = X_USER_EFT_PROFILE_CODE;
2463
2464 if (sql%notfound) then
2465 raise no_data_found;
2466 end if;
2467
2468 delete from IBY_FNDCPT_USER_EFT_PF_B
2469 where USER_EFT_PROFILE_CODE = X_USER_EFT_PROFILE_CODE;
2470
2471 if (sql%notfound) then
2472 raise no_data_found;
2473 end if;
2474 end USER_EFT_PROF_DELETE_ROW;
2475
2476 procedure USER_EFT_PROF_ADD_LANGUAGE
2477 is
2478 begin
2479 delete from IBY_FNDCPT_USER_EFT_PF_TL T
2480 where not exists
2481 (select NULL
2482 from IBY_FNDCPT_USER_EFT_PF_B B
2483 where B.USER_EFT_PROFILE_CODE = T.USER_EFT_PROFILE_CODE
2484 );
2485
2486 update IBY_FNDCPT_USER_EFT_PF_TL T set (
2487 USER_EFT_PROFILE_NAME
2488 ) = (select
2489 B.USER_EFT_PROFILE_NAME
2490 from IBY_FNDCPT_USER_EFT_PF_TL B
2491 where B.USER_EFT_PROFILE_CODE = T.USER_EFT_PROFILE_CODE
2492 and B.LANGUAGE = T.SOURCE_LANG)
2493 where (
2494 T.USER_EFT_PROFILE_CODE,
2495 T.LANGUAGE
2496 ) in (select
2497 SUBT.USER_EFT_PROFILE_CODE,
2498 SUBT.LANGUAGE
2499 from IBY_FNDCPT_USER_EFT_PF_TL SUBB, IBY_FNDCPT_USER_EFT_PF_TL SUBT
2500 where SUBB.USER_EFT_PROFILE_CODE = SUBT.USER_EFT_PROFILE_CODE
2501 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2502 and (SUBB.USER_EFT_PROFILE_NAME <> SUBT.USER_EFT_PROFILE_NAME
2503 ));
2504
2505 insert into IBY_FNDCPT_USER_EFT_PF_TL (
2506 USER_EFT_PROFILE_CODE,
2507 USER_EFT_PROFILE_NAME,
2508 CREATED_BY,
2509 CREATION_DATE,
2510 LAST_UPDATED_BY,
2511 LAST_UPDATE_DATE,
2512 LAST_UPDATE_LOGIN,
2513 OBJECT_VERSION_NUMBER,
2514 LANGUAGE,
2515 SOURCE_LANG
2516 ) select /*+ ORDERED */
2517 B.USER_EFT_PROFILE_CODE,
2518 B.USER_EFT_PROFILE_NAME,
2519 B.CREATED_BY,
2520 B.CREATION_DATE,
2521 B.LAST_UPDATED_BY,
2522 B.LAST_UPDATE_DATE,
2523 B.LAST_UPDATE_LOGIN,
2524 B.OBJECT_VERSION_NUMBER,
2525 L.LANGUAGE_CODE,
2526 B.SOURCE_LANG
2527 from IBY_FNDCPT_USER_EFT_PF_TL B, FND_LANGUAGES L
2528 where L.INSTALLED_FLAG in ('I', 'B')
2529 and B.LANGUAGE = userenv('LANG')
2530 and not exists
2531 (select NULL
2532 from IBY_FNDCPT_USER_EFT_PF_TL T
2533 where T.USER_EFT_PROFILE_CODE = B.USER_EFT_PROFILE_CODE
2534 and T.LANGUAGE = L.LANGUAGE_CODE);
2535 end USER_EFT_PROF_ADD_LANGUAGE;
2536
2537 procedure USER_DC_PROF_INSERT_ROW (
2538 X_ROWID in out NOCOPY VARCHAR2,
2539 X_USER_DC_PROFILE_CODE in VARCHAR2,
2540 X_SETTLEMENT_TRANS_CONFIG_ID in NUMBER,
2541 X_QUERY_TRANS_CONFIG_ID in NUMBER,
2542 X_INACTIVE_DATE in DATE,
2543 X_OBJECT_VERSION_NUMBER in NUMBER,
2544 X_ONLINE_DEB_TRANS_CONFIG_ID in VARCHAR2,
2545 X_BEP_ACCOUNT_ID in NUMBER,
2546 X_SYS_DC_PROFILE_CODE in VARCHAR2,
2547 X_USER_DC_PROFILE_NAME in VARCHAR2,
2548 X_CREATION_DATE in DATE,
2549 X_CREATED_BY in NUMBER,
2550 X_LAST_UPDATE_DATE in DATE,
2551 X_LAST_UPDATED_BY in NUMBER,
2552 X_LAST_UPDATE_LOGIN in NUMBER
2553 ) is
2554 cursor C is select ROWID from IBY_FNDCPT_USER_DC_PF_B
2555 where USER_DC_PROFILE_CODE = X_USER_DC_PROFILE_CODE
2556 ;
2557 begin
2558 insert into IBY_FNDCPT_USER_DC_PF_B (
2559 SETTLEMENT_TRANS_CONFIG_ID,
2560 QUERY_TRANS_CONFIG_ID,
2561 INACTIVE_DATE,
2562 OBJECT_VERSION_NUMBER,
2563 ONLINE_DEB_TRANS_CONFIG_ID,
2564 BEP_ACCOUNT_ID,
2565 SYS_DC_PROFILE_CODE,
2566 USER_DC_PROFILE_CODE,
2567 CREATION_DATE,
2568 CREATED_BY,
2569 LAST_UPDATE_DATE,
2570 LAST_UPDATED_BY,
2571 LAST_UPDATE_LOGIN
2572 ) values (
2573 X_SETTLEMENT_TRANS_CONFIG_ID,
2574 X_QUERY_TRANS_CONFIG_ID,
2575 X_INACTIVE_DATE,
2576 X_OBJECT_VERSION_NUMBER,
2577 X_ONLINE_DEB_TRANS_CONFIG_ID,
2578 X_BEP_ACCOUNT_ID,
2579 X_SYS_DC_PROFILE_CODE,
2580 X_USER_DC_PROFILE_CODE,
2581 X_CREATION_DATE,
2582 X_CREATED_BY,
2583 X_LAST_UPDATE_DATE,
2584 X_LAST_UPDATED_BY,
2585 X_LAST_UPDATE_LOGIN
2586 );
2587
2588 insert into IBY_FNDCPT_USER_DC_PF_TL (
2589 LAST_UPDATE_DATE,
2590 LAST_UPDATE_LOGIN,
2591 OBJECT_VERSION_NUMBER,
2592 CREATED_BY,
2593 CREATION_DATE,
2594 LAST_UPDATED_BY,
2595 USER_DC_PROFILE_CODE,
2596 USER_DC_PROFILE_NAME,
2597 LANGUAGE,
2598 SOURCE_LANG
2599 ) select
2600 X_LAST_UPDATE_DATE,
2601 X_LAST_UPDATE_LOGIN,
2602 X_OBJECT_VERSION_NUMBER,
2603 X_CREATED_BY,
2604 X_CREATION_DATE,
2605 X_LAST_UPDATED_BY,
2606 X_USER_DC_PROFILE_CODE,
2607 X_USER_DC_PROFILE_NAME,
2608 L.LANGUAGE_CODE,
2609 userenv('LANG')
2610 from FND_LANGUAGES L
2611 where L.INSTALLED_FLAG in ('I', 'B')
2612 and not exists
2613 (select NULL
2614 from IBY_FNDCPT_USER_DC_PF_TL T
2615 where T.USER_DC_PROFILE_CODE = X_USER_DC_PROFILE_CODE
2616 and T.LANGUAGE = L.LANGUAGE_CODE);
2617
2618 open c;
2619 fetch c into X_ROWID;
2620 if (c%notfound) then
2621 close c;
2622 raise no_data_found;
2623 end if;
2624 close c;
2625
2626 end USER_DC_PROF_INSERT_ROW;
2627
2628 procedure USER_DC_PROF_UPDATE_ROW (
2629 X_USER_DC_PROFILE_CODE in VARCHAR2,
2630 X_SETTLEMENT_TRANS_CONFIG_ID in NUMBER,
2631 X_QUERY_TRANS_CONFIG_ID in NUMBER,
2632 X_INACTIVE_DATE in DATE,
2633 X_OBJECT_VERSION_NUMBER in NUMBER,
2634 X_ONLINE_DEB_TRANS_CONFIG_ID in VARCHAR2,
2635 X_BEP_ACCOUNT_ID in NUMBER,
2636 X_SYS_DC_PROFILE_CODE in VARCHAR2,
2637 X_USER_DC_PROFILE_NAME in VARCHAR2,
2638 X_LAST_UPDATE_DATE in DATE,
2639 X_LAST_UPDATED_BY in NUMBER,
2640 X_LAST_UPDATE_LOGIN in NUMBER
2641 ) is
2642 begin
2643 update IBY_FNDCPT_USER_DC_PF_B set
2644 SETTLEMENT_TRANS_CONFIG_ID = X_SETTLEMENT_TRANS_CONFIG_ID,
2645 QUERY_TRANS_CONFIG_ID = X_QUERY_TRANS_CONFIG_ID,
2646 INACTIVE_DATE = X_INACTIVE_DATE,
2647 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
2648 ONLINE_DEB_TRANS_CONFIG_ID = X_ONLINE_DEB_TRANS_CONFIG_ID,
2649 BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID,
2650 SYS_DC_PROFILE_CODE = X_SYS_DC_PROFILE_CODE,
2651 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2652 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2653 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
2654 where USER_DC_PROFILE_CODE = X_USER_DC_PROFILE_CODE;
2655
2656 if (sql%notfound) then
2657 raise no_data_found;
2658 end if;
2659
2660 update IBY_FNDCPT_USER_DC_PF_TL set
2661 USER_DC_PROFILE_NAME = X_USER_DC_PROFILE_NAME,
2662 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2663 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2664 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2665 SOURCE_LANG = userenv('LANG')
2666 where USER_DC_PROFILE_CODE = X_USER_DC_PROFILE_CODE
2667 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2668
2669 if (sql%notfound) then
2670 raise no_data_found;
2671 end if;
2672 end USER_DC_PROF_UPDATE_ROW;
2673
2674 procedure USER_DC_PROF_DELETE_ROW (
2675 X_USER_DC_PROFILE_CODE in VARCHAR2
2676 ) is
2677 begin
2678 delete from IBY_FNDCPT_USER_DC_PF_TL
2679 where USER_DC_PROFILE_CODE = X_USER_DC_PROFILE_CODE;
2680
2681 if (sql%notfound) then
2682 raise no_data_found;
2683 end if;
2684
2685 delete from IBY_FNDCPT_USER_DC_PF_B
2686 where USER_DC_PROFILE_CODE = X_USER_DC_PROFILE_CODE;
2687
2688 if (sql%notfound) then
2689 raise no_data_found;
2690 end if;
2691 end USER_DC_PROF_DELETE_ROW;
2692
2693 procedure USER_DC_PROF_ADD_LANGUAGE
2694 is
2695 begin
2696 delete from IBY_FNDCPT_USER_DC_PF_TL T
2697 where not exists
2698 (select NULL
2699 from IBY_FNDCPT_USER_DC_PF_B B
2700 where B.USER_DC_PROFILE_CODE = T.USER_DC_PROFILE_CODE
2701 );
2702
2703 update IBY_FNDCPT_USER_DC_PF_TL T set (
2704 USER_DC_PROFILE_NAME
2705 ) = (select
2706 B.USER_DC_PROFILE_NAME
2707 from IBY_FNDCPT_USER_DC_PF_TL B
2708 where B.USER_DC_PROFILE_CODE = T.USER_DC_PROFILE_CODE
2709 and B.LANGUAGE = T.SOURCE_LANG)
2710 where (
2711 T.USER_DC_PROFILE_CODE,
2712 T.LANGUAGE
2713 ) in (select
2714 SUBT.USER_DC_PROFILE_CODE,
2715 SUBT.LANGUAGE
2716 from IBY_FNDCPT_USER_DC_PF_TL SUBB, IBY_FNDCPT_USER_DC_PF_TL SUBT
2717 where SUBB.USER_DC_PROFILE_CODE = SUBT.USER_DC_PROFILE_CODE
2718 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2719 and (SUBB.USER_DC_PROFILE_NAME <> SUBT.USER_DC_PROFILE_NAME
2720 ));
2721
2722 insert into IBY_FNDCPT_USER_DC_PF_TL (
2723 LAST_UPDATE_DATE,
2724 LAST_UPDATE_LOGIN,
2725 OBJECT_VERSION_NUMBER,
2726 CREATED_BY,
2727 CREATION_DATE,
2728 LAST_UPDATED_BY,
2729 USER_DC_PROFILE_CODE,
2730 USER_DC_PROFILE_NAME,
2731 LANGUAGE,
2732 SOURCE_LANG
2733 ) select /*+ ORDERED */
2734 B.LAST_UPDATE_DATE,
2735 B.LAST_UPDATE_LOGIN,
2736 B.OBJECT_VERSION_NUMBER,
2737 B.CREATED_BY,
2738 B.CREATION_DATE,
2739 B.LAST_UPDATED_BY,
2740 B.USER_DC_PROFILE_CODE,
2741 B.USER_DC_PROFILE_NAME,
2742 L.LANGUAGE_CODE,
2743 B.SOURCE_LANG
2744 from IBY_FNDCPT_USER_DC_PF_TL B, FND_LANGUAGES L
2745 where L.INSTALLED_FLAG in ('I', 'B')
2746 and B.LANGUAGE = userenv('LANG')
2747 and not exists
2748 (select NULL
2749 from IBY_FNDCPT_USER_DC_PF_TL T
2750 where T.USER_DC_PROFILE_CODE = B.USER_DC_PROFILE_CODE
2751 and T.LANGUAGE = L.LANGUAGE_CODE);
2752 end USER_DC_PROF_ADD_LANGUAGE;
2753
2754 procedure PMT_CHANNEL_INSERT_ROW (
2755 X_ROWID in out NOCOPY VARCHAR2,
2756 X_PAYMENT_CHANNEL_CODE in VARCHAR2,
2757 X_ATTRIBUTE13 in VARCHAR2,
2758 X_ATTRIBUTE14 in VARCHAR2,
2759 X_ATTRIBUTE15 in VARCHAR2,
2760 X_OBJECT_VERSION_NUMBER in NUMBER,
2761 X_INSTRUMENT_TYPE in VARCHAR2,
2762 X_INACTIVE_DATE in DATE,
2763 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2764 X_ATTRIBUTE1 in VARCHAR2,
2765 X_ATTRIBUTE2 in VARCHAR2,
2766 X_ATTRIBUTE3 in VARCHAR2,
2767 X_ATTRIBUTE4 in VARCHAR2,
2768 X_ATTRIBUTE5 in VARCHAR2,
2769 X_ATTRIBUTE6 in VARCHAR2,
2770 X_ATTRIBUTE7 in VARCHAR2,
2771 X_ATTRIBUTE8 in VARCHAR2,
2772 X_ATTRIBUTE9 in VARCHAR2,
2773 X_ATTRIBUTE10 in VARCHAR2,
2774 X_ATTRIBUTE11 in VARCHAR2,
2775 X_ATTRIBUTE12 in VARCHAR2,
2776 X_PAYMENT_CHANNEL_NAME in VARCHAR2,
2777 X_DESCRIPTION in VARCHAR2,
2778 X_CREATION_DATE in DATE,
2779 X_CREATED_BY in NUMBER,
2780 X_LAST_UPDATE_DATE in DATE,
2781 X_LAST_UPDATED_BY in NUMBER,
2782 X_LAST_UPDATE_LOGIN in NUMBER
2783 ) is
2784 cursor C is select ROWID from IBY_FNDCPT_PMT_CHNNLS_B
2785 where PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE
2786 ;
2787 begin
2788 insert into IBY_FNDCPT_PMT_CHNNLS_B (
2789 ATTRIBUTE13,
2790 ATTRIBUTE14,
2791 ATTRIBUTE15,
2792 OBJECT_VERSION_NUMBER,
2793 PAYMENT_CHANNEL_CODE,
2794 INSTRUMENT_TYPE,
2795 INACTIVE_DATE,
2796 ATTRIBUTE_CATEGORY,
2797 ATTRIBUTE1,
2798 ATTRIBUTE2,
2799 ATTRIBUTE3,
2800 ATTRIBUTE4,
2801 ATTRIBUTE5,
2802 ATTRIBUTE6,
2803 ATTRIBUTE7,
2804 ATTRIBUTE8,
2805 ATTRIBUTE9,
2806 ATTRIBUTE10,
2807 ATTRIBUTE11,
2808 ATTRIBUTE12,
2809 CREATION_DATE,
2810 CREATED_BY,
2811 LAST_UPDATE_DATE,
2812 LAST_UPDATED_BY,
2813 LAST_UPDATE_LOGIN
2814 ) values (
2815 X_ATTRIBUTE13,
2816 X_ATTRIBUTE14,
2817 X_ATTRIBUTE15,
2818 X_OBJECT_VERSION_NUMBER,
2819 X_PAYMENT_CHANNEL_CODE,
2820 X_INSTRUMENT_TYPE,
2821 X_INACTIVE_DATE,
2822 X_ATTRIBUTE_CATEGORY,
2823 X_ATTRIBUTE1,
2824 X_ATTRIBUTE2,
2825 X_ATTRIBUTE3,
2826 X_ATTRIBUTE4,
2827 X_ATTRIBUTE5,
2828 X_ATTRIBUTE6,
2829 X_ATTRIBUTE7,
2830 X_ATTRIBUTE8,
2831 X_ATTRIBUTE9,
2832 X_ATTRIBUTE10,
2833 X_ATTRIBUTE11,
2834 X_ATTRIBUTE12,
2835 X_CREATION_DATE,
2836 X_CREATED_BY,
2837 X_LAST_UPDATE_DATE,
2838 X_LAST_UPDATED_BY,
2839 X_LAST_UPDATE_LOGIN
2840 );
2841
2842 insert into IBY_FNDCPT_PMT_CHNNLS_TL (
2843 PAYMENT_CHANNEL_CODE,
2844 PAYMENT_CHANNEL_NAME,
2845 DESCRIPTION,
2846 CREATED_BY,
2847 CREATION_DATE,
2848 LAST_UPDATED_BY,
2849 LAST_UPDATE_DATE,
2850 LAST_UPDATE_LOGIN,
2851 OBJECT_VERSION_NUMBER,
2852 LANGUAGE,
2853 SOURCE_LANG
2854 ) select
2855 X_PAYMENT_CHANNEL_CODE,
2856 X_PAYMENT_CHANNEL_NAME,
2857 X_DESCRIPTION,
2858 X_CREATED_BY,
2859 X_CREATION_DATE,
2860 X_LAST_UPDATED_BY,
2861 X_LAST_UPDATE_DATE,
2862 X_LAST_UPDATE_LOGIN,
2863 X_OBJECT_VERSION_NUMBER,
2864 L.LANGUAGE_CODE,
2865 userenv('LANG')
2866 from FND_LANGUAGES L
2867 where L.INSTALLED_FLAG in ('I', 'B')
2868 and not exists
2869 (select NULL
2870 from IBY_FNDCPT_PMT_CHNNLS_TL T
2871 where T.PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE
2872 and T.LANGUAGE = L.LANGUAGE_CODE);
2873
2874 open c;
2875 fetch c into X_ROWID;
2876 if (c%notfound) then
2877 close c;
2878 raise no_data_found;
2879 end if;
2880 close c;
2881
2882 end PMT_CHANNEL_INSERT_ROW;
2883
2884 procedure PMT_CHANNEL_UPDATE_ROW (
2885 X_PAYMENT_CHANNEL_CODE in VARCHAR2,
2886 X_ATTRIBUTE13 in VARCHAR2,
2887 X_ATTRIBUTE14 in VARCHAR2,
2888 X_ATTRIBUTE15 in VARCHAR2,
2889 X_OBJECT_VERSION_NUMBER in NUMBER,
2890 X_INSTRUMENT_TYPE in VARCHAR2,
2891 X_INACTIVE_DATE in DATE,
2892 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2893 X_ATTRIBUTE1 in VARCHAR2,
2894 X_ATTRIBUTE2 in VARCHAR2,
2895 X_ATTRIBUTE3 in VARCHAR2,
2896 X_ATTRIBUTE4 in VARCHAR2,
2897 X_ATTRIBUTE5 in VARCHAR2,
2898 X_ATTRIBUTE6 in VARCHAR2,
2899 X_ATTRIBUTE7 in VARCHAR2,
2900 X_ATTRIBUTE8 in VARCHAR2,
2901 X_ATTRIBUTE9 in VARCHAR2,
2902 X_ATTRIBUTE10 in VARCHAR2,
2903 X_ATTRIBUTE11 in VARCHAR2,
2904 X_ATTRIBUTE12 in VARCHAR2,
2905 X_PAYMENT_CHANNEL_NAME in VARCHAR2,
2906 X_DESCRIPTION in VARCHAR2,
2907 X_LAST_UPDATE_DATE in DATE,
2908 X_LAST_UPDATED_BY in NUMBER,
2909 X_LAST_UPDATE_LOGIN in NUMBER
2910 ) is
2911 begin
2912 update IBY_FNDCPT_PMT_CHNNLS_B set
2913 ATTRIBUTE13 = X_ATTRIBUTE13,
2914 ATTRIBUTE14 = X_ATTRIBUTE14,
2915 ATTRIBUTE15 = X_ATTRIBUTE15,
2916 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
2917 INSTRUMENT_TYPE = X_INSTRUMENT_TYPE,
2918 INACTIVE_DATE = X_INACTIVE_DATE,
2919 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
2920 ATTRIBUTE1 = X_ATTRIBUTE1,
2921 ATTRIBUTE2 = X_ATTRIBUTE2,
2922 ATTRIBUTE3 = X_ATTRIBUTE3,
2923 ATTRIBUTE4 = X_ATTRIBUTE4,
2924 ATTRIBUTE5 = X_ATTRIBUTE5,
2925 ATTRIBUTE6 = X_ATTRIBUTE6,
2926 ATTRIBUTE7 = X_ATTRIBUTE7,
2927 ATTRIBUTE8 = X_ATTRIBUTE8,
2928 ATTRIBUTE9 = X_ATTRIBUTE9,
2929 ATTRIBUTE10 = X_ATTRIBUTE10,
2930 ATTRIBUTE11 = X_ATTRIBUTE11,
2931 ATTRIBUTE12 = X_ATTRIBUTE12,
2932 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2933 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2934 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
2935 where PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE;
2936
2937 if (sql%notfound) then
2938 raise no_data_found;
2939 end if;
2940
2941 update IBY_FNDCPT_PMT_CHNNLS_TL set
2942 PAYMENT_CHANNEL_NAME = X_PAYMENT_CHANNEL_NAME,
2943 DESCRIPTION = X_DESCRIPTION,
2944 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2945 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2946 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2947 SOURCE_LANG = userenv('LANG')
2948 where PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE
2949 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2950
2951 if (sql%notfound) then
2952 raise no_data_found;
2953 end if;
2954 end PMT_CHANNEL_UPDATE_ROW;
2955
2956 procedure PMT_CHANNEL_DELETE_ROW (
2957 X_PAYMENT_CHANNEL_CODE in VARCHAR2
2958 ) is
2959 begin
2960 delete from IBY_FNDCPT_PMT_CHNNLS_TL
2961 where PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE;
2962
2963 if (sql%notfound) then
2964 raise no_data_found;
2965 end if;
2966
2967 delete from IBY_FNDCPT_PMT_CHNNLS_B
2968 where PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE;
2969
2970 if (sql%notfound) then
2971 raise no_data_found;
2972 end if;
2973 end PMT_CHANNEL_DELETE_ROW;
2974
2975 procedure PMT_CHANNEL_ADD_LANGUAGE
2976 is
2977 begin
2978 delete from IBY_FNDCPT_PMT_CHNNLS_TL T
2979 where not exists
2980 (select NULL
2981 from IBY_FNDCPT_PMT_CHNNLS_B B
2982 where B.PAYMENT_CHANNEL_CODE = T.PAYMENT_CHANNEL_CODE
2983 );
2984
2985 update IBY_FNDCPT_PMT_CHNNLS_TL T set (
2986 PAYMENT_CHANNEL_NAME,
2987 DESCRIPTION
2988 ) = (select
2989 B.PAYMENT_CHANNEL_NAME,
2990 B.DESCRIPTION
2991 from IBY_FNDCPT_PMT_CHNNLS_TL B
2992 where B.PAYMENT_CHANNEL_CODE = T.PAYMENT_CHANNEL_CODE
2993 and B.LANGUAGE = T.SOURCE_LANG)
2994 where (
2995 T.PAYMENT_CHANNEL_CODE,
2996 T.LANGUAGE
2997 ) in (select
2998 SUBT.PAYMENT_CHANNEL_CODE,
2999 SUBT.LANGUAGE
3000 from IBY_FNDCPT_PMT_CHNNLS_TL SUBB, IBY_FNDCPT_PMT_CHNNLS_TL SUBT
3001 where SUBB.PAYMENT_CHANNEL_CODE = SUBT.PAYMENT_CHANNEL_CODE
3002 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
3003 and (SUBB.PAYMENT_CHANNEL_NAME <> SUBT.PAYMENT_CHANNEL_NAME
3004 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
3005 ));
3006
3007 insert into IBY_FNDCPT_PMT_CHNNLS_TL (
3008 PAYMENT_CHANNEL_CODE,
3009 PAYMENT_CHANNEL_NAME,
3010 DESCRIPTION,
3011 CREATED_BY,
3012 CREATION_DATE,
3013 LAST_UPDATED_BY,
3014 LAST_UPDATE_DATE,
3015 LAST_UPDATE_LOGIN,
3016 OBJECT_VERSION_NUMBER,
3017 LANGUAGE,
3018 SOURCE_LANG
3019 ) select /*+ ORDERED */
3020 B.PAYMENT_CHANNEL_CODE,
3021 B.PAYMENT_CHANNEL_NAME,
3022 B.DESCRIPTION,
3023 B.CREATED_BY,
3024 B.CREATION_DATE,
3025 B.LAST_UPDATED_BY,
3026 B.LAST_UPDATE_DATE,
3027 B.LAST_UPDATE_LOGIN,
3028 B.OBJECT_VERSION_NUMBER,
3029 L.LANGUAGE_CODE,
3030 B.SOURCE_LANG
3031 from IBY_FNDCPT_PMT_CHNNLS_TL B, FND_LANGUAGES L
3032 where L.INSTALLED_FLAG in ('I', 'B')
3033 and B.LANGUAGE = userenv('LANG')
3034 and not exists
3035 (select NULL
3036 from IBY_FNDCPT_PMT_CHNNLS_TL T
3037 where T.PAYMENT_CHANNEL_CODE = B.PAYMENT_CHANNEL_CODE
3038 and T.LANGUAGE = L.LANGUAGE_CODE);
3039 end PMT_CHANNEL_ADD_LANGUAGE;
3040
3041 procedure PMT_CHANNEL_LOAD_ROW (
3042 X_PAYMENT_CHANNEL_CODE in VARCHAR2,
3043 X_ATTRIBUTE13 in VARCHAR2,
3044 X_ATTRIBUTE14 in VARCHAR2,
3045 X_ATTRIBUTE15 in VARCHAR2,
3046 X_OBJECT_VERSION_NUMBER in NUMBER,
3047 X_INSTRUMENT_TYPE in VARCHAR2,
3048 X_INACTIVE_DATE in DATE,
3049 X_ATTRIBUTE_CATEGORY in VARCHAR2,
3050 X_ATTRIBUTE1 in VARCHAR2,
3051 X_ATTRIBUTE2 in VARCHAR2,
3052 X_ATTRIBUTE3 in VARCHAR2,
3053 X_ATTRIBUTE4 in VARCHAR2,
3054 X_ATTRIBUTE5 in VARCHAR2,
3055 X_ATTRIBUTE6 in VARCHAR2,
3056 X_ATTRIBUTE7 in VARCHAR2,
3057 X_ATTRIBUTE8 in VARCHAR2,
3058 X_ATTRIBUTE9 in VARCHAR2,
3059 X_ATTRIBUTE10 in VARCHAR2,
3060 X_ATTRIBUTE11 in VARCHAR2,
3061 X_ATTRIBUTE12 in VARCHAR2,
3062 X_PAYMENT_CHANNEL_NAME in VARCHAR2,
3063 X_DESCRIPTION in VARCHAR2,
3064 X_LAST_UPDATE_DATE in DATE,
3065 X_OWNER in VARCHAR2)
3066 is
3067 row_id VARCHAR2(200);
3068 begin
3069 pmt_channel_update_row (
3070 X_PAYMENT_CHANNEL_CODE,
3071 X_ATTRIBUTE13,
3072 X_ATTRIBUTE14,
3073 X_ATTRIBUTE15,
3074 X_OBJECT_VERSION_NUMBER,
3075 X_INSTRUMENT_TYPE,
3076 X_INACTIVE_DATE,
3077 X_ATTRIBUTE_CATEGORY,
3078 X_ATTRIBUTE1,
3079 X_ATTRIBUTE2,
3080 X_ATTRIBUTE3,
3081 X_ATTRIBUTE4,
3082 X_ATTRIBUTE5,
3083 X_ATTRIBUTE6,
3084 X_ATTRIBUTE7,
3085 X_ATTRIBUTE8,
3086 X_ATTRIBUTE9,
3087 X_ATTRIBUTE10,
3088 X_ATTRIBUTE11,
3089 X_ATTRIBUTE12,
3090 X_PAYMENT_CHANNEL_NAME,
3091 X_DESCRIPTION,
3092 X_LAST_UPDATE_DATE,
3093 fnd_load_util.owner_id(X_OWNER),
3094 fnd_load_util.owner_id(X_OWNER));
3095
3096 exception
3097 when no_data_found then
3098 pmt_channel_insert_row (
3099 row_id,
3100 X_PAYMENT_CHANNEL_CODE,
3101 X_ATTRIBUTE13,
3102 X_ATTRIBUTE14,
3103 X_ATTRIBUTE15,
3104 X_OBJECT_VERSION_NUMBER,
3105 X_INSTRUMENT_TYPE,
3106 X_INACTIVE_DATE,
3107 X_ATTRIBUTE_CATEGORY,
3108 X_ATTRIBUTE1,
3109 X_ATTRIBUTE2,
3110 X_ATTRIBUTE3,
3111 X_ATTRIBUTE4,
3112 X_ATTRIBUTE5,
3113 X_ATTRIBUTE6,
3114 X_ATTRIBUTE7,
3115 X_ATTRIBUTE8,
3116 X_ATTRIBUTE9,
3117 X_ATTRIBUTE10,
3118 X_ATTRIBUTE11,
3119 X_ATTRIBUTE12,
3120 X_PAYMENT_CHANNEL_NAME,
3121 X_DESCRIPTION,
3122 X_LAST_UPDATE_DATE,
3123 fnd_load_util.owner_id(X_OWNER),
3124 X_LAST_UPDATE_DATE,
3125 fnd_load_util.owner_id(X_OWNER),
3126 fnd_load_util.owner_id(X_OWNER));
3127 end;
3128
3129 procedure PMT_CHANNEL_TRANSLATE_ROW (
3130 X_PAYMENT_CHANNEL_CODE in VARCHAR2,
3131 X_PAYMENT_CHANNEL_NAME in VARCHAR2,
3132 X_DESCRIPTION in VARCHAR2,
3133 X_OBJECT_VERSION_NUMBER in NUMBER,
3134 X_LAST_UPDATE_DATE in DATE,
3135 X_OWNER in VARCHAR2)
3136 is
3137 begin
3138 update iby_fndcpt_pmt_chnnls_tl set
3139 PAYMENT_CHANNEL_NAME = X_PAYMENT_CHANNEL_NAME,
3140 DESCRIPTION = X_DESCRIPTION,
3141 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
3142 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
3143 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
3144 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
3145 SOURCE_LANG = userenv('LANG')
3146 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
3147 and PAYMENT_CHANNEL_CODE = X_PAYMENT_CHANNEL_CODE;
3148 end;
3149
3150
3151 procedure CC_ISSUER_INSERT_ROW (
3152 X_ROWID in out NOCOPY VARCHAR2,
3153 X_CARD_ISSUER_CODE in VARCHAR2,
3154 X_OBJECT_VERSION_NUMBER in NUMBER,
3155 X_CARD_ISSUER_ACCEPTED_FLAG in VARCHAR2,
3156 X_AUTHORIZATION_VALIDITY_PERIO in NUMBER,
3157 X_DIGIT_CHECK_FLAG in VARCHAR2,
3158 X_CARD_ISSUER_NAME in VARCHAR2,
3159 X_DESCRIPTION in VARCHAR2,
3160 X_CREATION_DATE in DATE,
3161 X_CREATED_BY in NUMBER,
3162 X_LAST_UPDATE_DATE in DATE,
3163 X_LAST_UPDATED_BY in NUMBER,
3164 X_LAST_UPDATE_LOGIN in NUMBER
3165 ) is
3166 cursor C is select ROWID from IBY_CREDITCARD_ISSUERS_B
3167 where CARD_ISSUER_CODE = X_CARD_ISSUER_CODE
3168 ;
3169 begin
3170 insert into IBY_CREDITCARD_ISSUERS_B (
3171 OBJECT_VERSION_NUMBER,
3172 CARD_ISSUER_CODE,
3173 CARD_ISSUER_ACCEPTED_FLAG,
3174 AUTHORIZATION_VALIDITY_PERIOD,
3175 DIGIT_CHECK_FLAG,
3176 CREATION_DATE,
3177 CREATED_BY,
3178 LAST_UPDATE_DATE,
3179 LAST_UPDATED_BY,
3180 LAST_UPDATE_LOGIN
3181 ) values (
3182 X_OBJECT_VERSION_NUMBER,
3183 X_CARD_ISSUER_CODE,
3184 X_CARD_ISSUER_ACCEPTED_FLAG,
3185 X_AUTHORIZATION_VALIDITY_PERIO,
3186 X_DIGIT_CHECK_FLAG,
3187 X_CREATION_DATE,
3188 X_CREATED_BY,
3189 X_LAST_UPDATE_DATE,
3190 X_LAST_UPDATED_BY,
3191 X_LAST_UPDATE_LOGIN
3192 );
3193
3194 insert into IBY_CREDITCARD_ISSUERS_TL (
3195 CARD_ISSUER_CODE,
3196 CARD_ISSUER_NAME,
3197 DESCRIPTION,
3198 CREATED_BY,
3199 CREATION_DATE,
3200 LAST_UPDATED_BY,
3201 LAST_UPDATE_DATE,
3202 LAST_UPDATE_LOGIN,
3203 OBJECT_VERSION_NUMBER,
3204 LANGUAGE,
3205 SOURCE_LANG
3206 ) select
3207 X_CARD_ISSUER_CODE,
3208 X_CARD_ISSUER_NAME,
3209 X_DESCRIPTION,
3210 X_CREATED_BY,
3211 X_CREATION_DATE,
3212 X_LAST_UPDATED_BY,
3213 X_LAST_UPDATE_DATE,
3214 X_LAST_UPDATE_LOGIN,
3215 X_OBJECT_VERSION_NUMBER,
3216 L.LANGUAGE_CODE,
3217 userenv('LANG')
3218 from FND_LANGUAGES L
3219 where L.INSTALLED_FLAG in ('I', 'B')
3220 and not exists
3221 (select NULL
3222 from IBY_CREDITCARD_ISSUERS_TL T
3223 where T.CARD_ISSUER_CODE = X_CARD_ISSUER_CODE
3224 and T.LANGUAGE = L.LANGUAGE_CODE);
3225
3226 open c;
3227 fetch c into X_ROWID;
3228 if (c%notfound) then
3229 close c;
3230 raise no_data_found;
3231 end if;
3232 close c;
3233
3234 end CC_ISSUER_INSERT_ROW;
3235
3236 procedure CC_ISSUER_UPDATE_ROW (
3237 X_CARD_ISSUER_CODE in VARCHAR2,
3238 X_OBJECT_VERSION_NUMBER in NUMBER,
3239 X_CARD_ISSUER_ACCEPTED_FLAG in VARCHAR2,
3240 X_AUTHORIZATION_VALIDITY_PERIO in NUMBER,
3241 X_DIGIT_CHECK_FLAG in VARCHAR2,
3242 X_CARD_ISSUER_NAME in VARCHAR2,
3243 X_DESCRIPTION in VARCHAR2,
3244 X_LAST_UPDATE_DATE in DATE,
3245 X_LAST_UPDATED_BY in NUMBER,
3246 X_LAST_UPDATE_LOGIN in NUMBER
3247 ) is
3248 begin
3249 update IBY_CREDITCARD_ISSUERS_B set
3250 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
3251 CARD_ISSUER_ACCEPTED_FLAG = X_CARD_ISSUER_ACCEPTED_FLAG,
3252 AUTHORIZATION_VALIDITY_PERIOD = X_AUTHORIZATION_VALIDITY_PERIO,
3253 DIGIT_CHECK_FLAG = X_DIGIT_CHECK_FLAG,
3254 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
3255 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3256 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
3257 where CARD_ISSUER_CODE = X_CARD_ISSUER_CODE;
3258
3259 if (sql%notfound) then
3260 raise no_data_found;
3261 end if;
3262
3263 update IBY_CREDITCARD_ISSUERS_TL set
3264 CARD_ISSUER_NAME = X_CARD_ISSUER_NAME,
3265 DESCRIPTION = X_DESCRIPTION,
3266 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
3267 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3268 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3269 SOURCE_LANG = userenv('LANG')
3270 where CARD_ISSUER_CODE = X_CARD_ISSUER_CODE
3271 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
3272
3273 if (sql%notfound) then
3274 raise no_data_found;
3275 end if;
3276 end CC_ISSUER_UPDATE_ROW;
3277
3278 procedure CC_ISSUER_DELETE_ROW (
3279 X_CARD_ISSUER_CODE in VARCHAR2
3280 ) is
3281 begin
3282 delete from IBY_CREDITCARD_ISSUERS_TL
3283 where CARD_ISSUER_CODE = X_CARD_ISSUER_CODE;
3284
3285 if (sql%notfound) then
3286 raise no_data_found;
3287 end if;
3288
3289 delete from IBY_CREDITCARD_ISSUERS_B
3290 where CARD_ISSUER_CODE = X_CARD_ISSUER_CODE;
3291
3292 if (sql%notfound) then
3293 raise no_data_found;
3294 end if;
3295 end CC_ISSUER_DELETE_ROW;
3296
3297 procedure CC_ISSUER_ADD_LANGUAGE
3298 is
3299 begin
3300 delete from IBY_CREDITCARD_ISSUERS_TL T
3301 where not exists
3302 (select NULL
3303 from IBY_CREDITCARD_ISSUERS_B B
3304 where B.CARD_ISSUER_CODE = T.CARD_ISSUER_CODE
3305 );
3306
3307 update IBY_CREDITCARD_ISSUERS_TL T set (
3308 CARD_ISSUER_NAME,
3309 DESCRIPTION
3310 ) = (select
3311 B.CARD_ISSUER_NAME,
3312 B.DESCRIPTION
3313 from IBY_CREDITCARD_ISSUERS_TL B
3314 where B.CARD_ISSUER_CODE = T.CARD_ISSUER_CODE
3315 and B.LANGUAGE = T.SOURCE_LANG)
3316 where (
3317 T.CARD_ISSUER_CODE,
3318 T.LANGUAGE
3319 ) in (select
3320 SUBT.CARD_ISSUER_CODE,
3321 SUBT.LANGUAGE
3322 from IBY_CREDITCARD_ISSUERS_TL SUBB, IBY_CREDITCARD_ISSUERS_TL SUBT
3323 where SUBB.CARD_ISSUER_CODE = SUBT.CARD_ISSUER_CODE
3324 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
3325 and (SUBB.CARD_ISSUER_NAME <> SUBT.CARD_ISSUER_NAME
3326 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
3327 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
3328 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
3329 ));
3330
3331 insert into IBY_CREDITCARD_ISSUERS_TL (
3332 CARD_ISSUER_CODE,
3333 CARD_ISSUER_NAME,
3334 DESCRIPTION,
3335 CREATED_BY,
3336 CREATION_DATE,
3337 LAST_UPDATED_BY,
3338 LAST_UPDATE_DATE,
3339 LAST_UPDATE_LOGIN,
3340 OBJECT_VERSION_NUMBER,
3341 LANGUAGE,
3342 SOURCE_LANG
3343 ) select /*+ ORDERED */
3344 B.CARD_ISSUER_CODE,
3345 B.CARD_ISSUER_NAME,
3346 B.DESCRIPTION,
3347 B.CREATED_BY,
3348 B.CREATION_DATE,
3349 B.LAST_UPDATED_BY,
3350 B.LAST_UPDATE_DATE,
3351 B.LAST_UPDATE_LOGIN,
3352 B.OBJECT_VERSION_NUMBER,
3353 L.LANGUAGE_CODE,
3354 B.SOURCE_LANG
3355 from IBY_CREDITCARD_ISSUERS_TL B, FND_LANGUAGES L
3356 where L.INSTALLED_FLAG in ('I', 'B')
3357 and B.LANGUAGE = userenv('LANG')
3358 and not exists
3359 (select NULL
3360 from IBY_CREDITCARD_ISSUERS_TL T
3361 where T.CARD_ISSUER_CODE = B.CARD_ISSUER_CODE
3362 and T.LANGUAGE = L.LANGUAGE_CODE);
3363 end CC_ISSUER_ADD_LANGUAGE;
3364
3365 procedure CC_ISSUER_LOAD_ROW (
3366 X_CARD_ISSUER_CODE in VARCHAR2,
3367 X_OBJECT_VERSION_NUMBER in NUMBER,
3368 X_CARD_ISSUER_ACCEPTED_FLAG in VARCHAR2,
3369 X_AUTHORIZATION_VALIDITY_PERIO in NUMBER,
3370 X_DIGIT_CHECK_FLAG in VARCHAR2,
3371 X_CARD_ISSUER_NAME in VARCHAR2,
3372 X_DESCRIPTION in VARCHAR2,
3373 X_LAST_UPDATE_DATE in DATE,
3374 X_OWNER in VARCHAR2)
3375 is
3376 row_id VARCHAR2(200);
3377 begin
3378 CC_ISSUER_UPDATE_ROW (
3379 X_CARD_ISSUER_CODE,
3380 X_OBJECT_VERSION_NUMBER,
3381 X_CARD_ISSUER_ACCEPTED_FLAG,
3382 X_AUTHORIZATION_VALIDITY_PERIO,
3383 X_DIGIT_CHECK_FLAG,
3384 X_CARD_ISSUER_NAME,
3385 X_DESCRIPTION,
3386 X_LAST_UPDATE_DATE,
3387 fnd_load_util.owner_id(X_OWNER),
3388 fnd_load_util.owner_id(X_OWNER));
3389 exception
3390 when no_data_found then
3391 CC_ISSUER_INSERT_ROW (
3392 row_id,
3393 X_CARD_ISSUER_CODE,
3394 X_OBJECT_VERSION_NUMBER,
3395 X_CARD_ISSUER_ACCEPTED_FLAG,
3396 X_AUTHORIZATION_VALIDITY_PERIO,
3397 X_DIGIT_CHECK_FLAG,
3398 X_CARD_ISSUER_NAME,
3399 X_DESCRIPTION,
3400 X_LAST_UPDATE_DATE,
3401 fnd_load_util.owner_id(X_OWNER),
3402 X_LAST_UPDATE_DATE,
3403 fnd_load_util.owner_id(X_OWNER),
3404 fnd_load_util.owner_id(X_OWNER));
3405 end;
3406
3407 procedure CC_ISSUER_TRANSLATE_ROW (
3408 X_CARD_ISSUER_CODE in VARCHAR2,
3409 X_OBJECT_VERSION_NUMBER in NUMBER,
3410 X_CARD_ISSUER_NAME in VARCHAR2,
3411 X_DESCRIPTION in VARCHAR2,
3412 X_LAST_UPDATE_DATE in DATE,
3413 X_OWNER in VARCHAR2)
3414 is
3415 begin
3416 update iby_creditcard_issuers_tl set
3417 CARD_ISSUER_NAME = X_CARD_ISSUER_NAME,
3418 DESCRIPTION = X_DESCRIPTION,
3419 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
3420 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
3421 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
3422 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
3423 SOURCE_LANG = userenv('LANG')
3424 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
3425 and CARD_ISSUER_CODE = X_CARD_ISSUER_CODE;
3426 end;
3427
3428 END IBY_FNDCPT_MLSUTL_PVT;