[Home] [Help]
PACKAGE BODY: APPS.IBY_PP_MLSUTL_PVT
Source
1 PACKAGE BODY IBY_PP_MLSUTL_PVT AS
2 /* $Header: ibyppmub.pls 120.10.12010000.1 2008/07/28 05:42:00 appldev ship $ */
3
4 procedure VAL_SET_INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_VALIDATION_SET_CODE in VARCHAR2,
7 X_VALIDATION_LEVEL_CODE in VARCHAR2,
8 X_VALIDATION_CODE_PACKAGE in VARCHAR2,
9 X_VALIDATION_CODE_ENTRY_POINT in VARCHAR2,
10 X_VALIDATION_CODE_LANGUAGE in VARCHAR2,
11 X_OBJECT_VERSION_NUMBER in NUMBER,
12 X_VALIDATION_SET_DISPLAY_NAME in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from IBY_VALIDATION_SETS_B
20 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
21 ;
22 begin
23 insert into IBY_VALIDATION_SETS_B (
24 VALIDATION_SET_CODE,
25 VALIDATION_LEVEL_CODE,
26 VALIDATION_CODE_PACKAGE,
27 VALIDATION_CODE_ENTRY_POINT,
28 VALIDATION_CODE_LANGUAGE,
29 OBJECT_VERSION_NUMBER,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_VALIDATION_SET_CODE,
37 X_VALIDATION_LEVEL_CODE,
38 X_VALIDATION_CODE_PACKAGE,
39 X_VALIDATION_CODE_ENTRY_POINT,
40 X_VALIDATION_CODE_LANGUAGE,
41 X_OBJECT_VERSION_NUMBER,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into IBY_VALIDATION_SETS_TL (
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 OBJECT_VERSION_NUMBER,
56 VALIDATION_SET_CODE,
57 VALIDATION_SET_DISPLAY_NAME,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_CREATED_BY,
62 X_CREATION_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_DATE,
65 X_LAST_UPDATE_LOGIN,
66 X_OBJECT_VERSION_NUMBER,
67 X_VALIDATION_SET_CODE,
68 X_VALIDATION_SET_DISPLAY_NAME,
69 L.LANGUAGE_CODE,
70 userenv('LANG')
71 from FND_LANGUAGES L
72 where L.INSTALLED_FLAG in ('I', 'B')
73 and not exists
74 (select NULL
75 from IBY_VALIDATION_SETS_TL T
76 where T.VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
77 and T.LANGUAGE = L.LANGUAGE_CODE);
78
79 open c;
80 fetch c into X_ROWID;
81 if (c%notfound) then
82 close c;
83 raise no_data_found;
84 end if;
85 close c;
86
87 end VAL_SET_INSERT_ROW;
88
89 procedure VAL_SET_UPDATE_ROW (
90 X_VALIDATION_SET_CODE in VARCHAR2,
91 X_VALIDATION_LEVEL_CODE in VARCHAR2,
92 X_VALIDATION_CODE_PACKAGE in VARCHAR2,
93 X_VALIDATION_CODE_ENTRY_POINT in VARCHAR2,
94 X_VALIDATION_CODE_LANGUAGE in VARCHAR2,
95 X_OBJECT_VERSION_NUMBER in NUMBER,
96 X_VALIDATION_SET_DISPLAY_NAME in VARCHAR2,
97 X_LAST_UPDATE_DATE in DATE,
98 X_LAST_UPDATED_BY in NUMBER,
99 X_LAST_UPDATE_LOGIN in NUMBER
100 ) is
101 begin
102 update IBY_VALIDATION_SETS_B set
103 VALIDATION_LEVEL_CODE = X_VALIDATION_LEVEL_CODE,
104 VALIDATION_CODE_PACKAGE = X_VALIDATION_CODE_PACKAGE,
105 VALIDATION_CODE_ENTRY_POINT = X_VALIDATION_CODE_ENTRY_POINT,
106 VALIDATION_CODE_LANGUAGE = X_VALIDATION_CODE_LANGUAGE,
107 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
108 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
109 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
110 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
111 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
112
113 if (sql%notfound) then
114 raise no_data_found;
115 end if;
116
117 update IBY_VALIDATION_SETS_TL set
118 VALIDATION_SET_DISPLAY_NAME = X_VALIDATION_SET_DISPLAY_NAME,
119 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
120 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
121 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
122 SOURCE_LANG = userenv('LANG')
123 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
124 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
125
126 if (sql%notfound) then
127 raise no_data_found;
128 end if;
129 end VAL_SET_UPDATE_ROW;
130
131 procedure VAL_SET_DELETE_ROW (
132 X_VALIDATION_SET_CODE in VARCHAR2
133 ) is
134 begin
135 delete from IBY_VALIDATION_SETS_TL
136 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
137
138 if (sql%notfound) then
139 raise no_data_found;
140 end if;
141
142 delete from IBY_VALIDATION_SETS_B
143 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
144
145 if (sql%notfound) then
146 raise no_data_found;
147 end if;
148 end VAL_SET_DELETE_ROW;
149
150 procedure VAL_SET_ADD_LANGUAGE
151 is
152 begin
153 delete from IBY_VALIDATION_SETS_TL T
154 where not exists
155 (select NULL
156 from IBY_VALIDATION_SETS_B B
157 where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
158 );
159
160 update IBY_VALIDATION_SETS_TL T set (
161 VALIDATION_SET_DISPLAY_NAME
162 ) = (select
163 B.VALIDATION_SET_DISPLAY_NAME
164 from IBY_VALIDATION_SETS_TL B
165 where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
166 and B.LANGUAGE = T.SOURCE_LANG)
167 where (
168 T.VALIDATION_SET_CODE,
169 T.LANGUAGE
170 ) in (select
171 SUBT.VALIDATION_SET_CODE,
172 SUBT.LANGUAGE
173 from IBY_VALIDATION_SETS_TL SUBB, IBY_VALIDATION_SETS_TL SUBT
174 where SUBB.VALIDATION_SET_CODE = SUBT.VALIDATION_SET_CODE
175 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
176 and (SUBB.VALIDATION_SET_DISPLAY_NAME <> SUBT.VALIDATION_SET_DISPLAY_NAME
177 ));
178
179 insert into IBY_VALIDATION_SETS_TL (
180 CREATED_BY,
181 CREATION_DATE,
182 LAST_UPDATED_BY,
183 LAST_UPDATE_DATE,
184 LAST_UPDATE_LOGIN,
185 OBJECT_VERSION_NUMBER,
186 VALIDATION_SET_CODE,
187 VALIDATION_SET_DISPLAY_NAME,
188 LANGUAGE,
189 SOURCE_LANG
190 ) select /*+ ORDERED */
191 B.CREATED_BY,
192 B.CREATION_DATE,
193 B.LAST_UPDATED_BY,
194 B.LAST_UPDATE_DATE,
195 B.LAST_UPDATE_LOGIN,
196 B.OBJECT_VERSION_NUMBER,
197 B.VALIDATION_SET_CODE,
198 B.VALIDATION_SET_DISPLAY_NAME,
199 L.LANGUAGE_CODE,
200 B.SOURCE_LANG
201 from IBY_VALIDATION_SETS_TL B, FND_LANGUAGES L
202 where L.INSTALLED_FLAG in ('I', 'B')
203 and B.LANGUAGE = userenv('LANG')
204 and not exists
205 (select NULL
206 from IBY_VALIDATION_SETS_TL T
207 where T.VALIDATION_SET_CODE = B.VALIDATION_SET_CODE
208 and T.LANGUAGE = L.LANGUAGE_CODE);
209 end VAL_SET_ADD_LANGUAGE;
210
211 procedure VAL_SET_LOAD_ROW (
212 X_VALIDATION_SET_CODE in VARCHAR2,
213 X_VALIDATION_LEVEL_CODE in VARCHAR2,
214 X_VALIDATION_CODE_PACKAGE in VARCHAR2,
215 X_VALIDATION_CODE_ENTRY_POINT in VARCHAR2,
216 X_VALIDATION_CODE_LANGUAGE in VARCHAR2,
217 X_OBJECT_VERSION_NUMBER in NUMBER,
218 X_VALIDATION_SET_DISPLAY_NAME in VARCHAR2,
219 X_LAST_UPDATE_DATE in DATE,
220 X_OWNER in VARCHAR2)
221 is
222 row_id VARCHAR2(200);
223 begin
224 VAL_SET_UPDATE_ROW (
225 X_VALIDATION_SET_CODE,
226 X_VALIDATION_LEVEL_CODE,
227 X_VALIDATION_CODE_PACKAGE,
228 X_VALIDATION_CODE_ENTRY_POINT,
229 X_VALIDATION_CODE_LANGUAGE,
230 X_OBJECT_VERSION_NUMBER,
231 X_VALIDATION_SET_DISPLAY_NAME,
232 X_LAST_UPDATE_DATE,
233 fnd_load_util.owner_id(X_OWNER),
234 fnd_load_util.owner_id(X_OWNER));
235 exception
236 when no_data_found then
237 VAL_SET_INSERT_ROW (
238 row_id,
239 X_VALIDATION_SET_CODE,
240 X_VALIDATION_LEVEL_CODE,
241 X_VALIDATION_CODE_PACKAGE,
242 X_VALIDATION_CODE_ENTRY_POINT,
243 X_VALIDATION_CODE_LANGUAGE,
244 X_OBJECT_VERSION_NUMBER,
245 X_VALIDATION_SET_DISPLAY_NAME,
246 X_LAST_UPDATE_DATE,
247 fnd_load_util.owner_id(X_OWNER),
248 X_LAST_UPDATE_DATE,
249 fnd_load_util.owner_id(X_OWNER),
250 fnd_load_util.owner_id(X_OWNER));
251 end;
252
253 procedure VAL_SET_TRANSLATE_ROW (
254 X_VALIDATION_SET_CODE in VARCHAR2,
255 X_OBJECT_VERSION_NUMBER in NUMBER,
256 X_VALIDATION_SET_DISPLAY_NAME in VARCHAR2,
257 X_LAST_UPDATE_DATE in DATE,
258 X_OWNER in VARCHAR2)
259 is
260 begin
261 update iby_validation_sets_tl set
262 VALIDATION_SET_DISPLAY_NAME = X_VALIDATION_SET_DISPLAY_NAME,
263 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
264 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
265 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
266 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
267 SOURCE_LANG = userenv('LANG')
268 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
269 and VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
270 end;
271
272
273 procedure VAL_PARAM_INSERT_ROW (
274 X_ROWID in out NOCOPY VARCHAR2,
275 X_VALIDATION_SET_CODE in VARCHAR2,
276 X_VALIDATION_PARAMETER_CODE in VARCHAR2,
277 X_VALIDATION_PARAMETER_TYPE in VARCHAR2,
278 X_VALIDATION_PARAM_DISPLAY_ORD in NUMBER,
279 X_OBJECT_VERSION_NUMBER in NUMBER,
280 X_VALIDATION_PARAM_DISPLAY_NAM in VARCHAR2,
281 X_CREATION_DATE in DATE,
282 X_CREATED_BY in NUMBER,
283 X_LAST_UPDATE_DATE in DATE,
284 X_LAST_UPDATED_BY in NUMBER,
285 X_LAST_UPDATE_LOGIN in NUMBER
286 ) is
287 cursor C is select ROWID from IBY_VALIDATION_PARAMS_B
288 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
289 and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE
290 ;
291 begin
292 insert into IBY_VALIDATION_PARAMS_B (
293 VALIDATION_SET_CODE,
294 VALIDATION_PARAMETER_CODE,
295 VALIDATION_PARAMETER_TYPE,
296 VALIDATION_PARAM_DISPLAY_ORDER,
297 OBJECT_VERSION_NUMBER,
298 CREATION_DATE,
299 CREATED_BY,
300 LAST_UPDATE_DATE,
301 LAST_UPDATED_BY,
302 LAST_UPDATE_LOGIN
303 ) values (
304 X_VALIDATION_SET_CODE,
305 X_VALIDATION_PARAMETER_CODE,
306 X_VALIDATION_PARAMETER_TYPE,
307 X_VALIDATION_PARAM_DISPLAY_ORD,
308 X_OBJECT_VERSION_NUMBER,
309 X_CREATION_DATE,
310 X_CREATED_BY,
311 X_LAST_UPDATE_DATE,
312 X_LAST_UPDATED_BY,
313 X_LAST_UPDATE_LOGIN
314 );
315
316 insert into IBY_VALIDATION_PARAMS_TL (
317 VALIDATION_SET_CODE,
318 VALIDATION_PARAMETER_CODE,
319 VALIDATION_PARAM_DISPLAY_NAME,
320 CREATED_BY,
321 CREATION_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_DATE,
324 LAST_UPDATE_LOGIN,
325 OBJECT_VERSION_NUMBER,
326 LANGUAGE,
327 SOURCE_LANG
328 ) select
329 X_VALIDATION_SET_CODE,
330 X_VALIDATION_PARAMETER_CODE,
331 X_VALIDATION_PARAM_DISPLAY_NAM,
332 X_CREATED_BY,
333 X_CREATION_DATE,
334 X_LAST_UPDATED_BY,
335 X_LAST_UPDATE_DATE,
336 X_LAST_UPDATE_LOGIN,
337 X_OBJECT_VERSION_NUMBER,
338 L.LANGUAGE_CODE,
339 userenv('LANG')
340 from FND_LANGUAGES L
341 where L.INSTALLED_FLAG in ('I', 'B')
342 and not exists
343 (select NULL
344 from IBY_VALIDATION_PARAMS_TL T
345 where T.VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
346 and T.VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE
347 and T.LANGUAGE = L.LANGUAGE_CODE);
348
349 open c;
350 fetch c into X_ROWID;
351 if (c%notfound) then
352 close c;
353 raise no_data_found;
354 end if;
355 close c;
356
357 end VAL_PARAM_INSERT_ROW;
358
359 procedure VAL_PARAM_UPDATE_ROW (
360 X_VALIDATION_SET_CODE in VARCHAR2,
361 X_VALIDATION_PARAMETER_CODE in VARCHAR2,
362 X_VALIDATION_PARAMETER_TYPE in VARCHAR2,
363 X_VALIDATION_PARAM_DISPLAY_ORD in NUMBER,
364 X_OBJECT_VERSION_NUMBER in NUMBER,
365 X_VALIDATION_PARAM_DISPLAY_NAM in VARCHAR2,
366 X_LAST_UPDATE_DATE in DATE,
367 X_LAST_UPDATED_BY in NUMBER,
368 X_LAST_UPDATE_LOGIN in NUMBER
369 ) is
370 begin
371 update IBY_VALIDATION_PARAMS_B set
372 VALIDATION_PARAMETER_TYPE = X_VALIDATION_PARAMETER_TYPE,
373 VALIDATION_PARAM_DISPLAY_ORDER = X_VALIDATION_PARAM_DISPLAY_ORD,
374 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
375 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
376 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
378 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
379 and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
380
381 if (sql%notfound) then
382 raise no_data_found;
383 end if;
384
385 update IBY_VALIDATION_PARAMS_TL set
386 VALIDATION_PARAM_DISPLAY_NAME = X_VALIDATION_PARAM_DISPLAY_NAM,
387 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
388 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
389 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
390 SOURCE_LANG = userenv('LANG')
391 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
392 and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE
393 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
394
395 if (sql%notfound) then
396 raise no_data_found;
397 end if;
398 end VAL_PARAM_UPDATE_ROW;
399
400 procedure VAL_PARAM_DELETE_ROW (
401 X_VALIDATION_SET_CODE in VARCHAR2,
402 X_VALIDATION_PARAMETER_CODE in VARCHAR2
403 ) is
404 begin
405 delete from IBY_VALIDATION_PARAMS_TL
406 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
407 and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
408
409 if (sql%notfound) then
410 raise no_data_found;
411 end if;
412
413 delete from IBY_VALIDATION_PARAMS_B
414 where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
415 and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
416
417 if (sql%notfound) then
418 raise no_data_found;
419 end if;
420 end VAL_PARAM_DELETE_ROW;
421
422 procedure VAL_PARAM_ADD_LANGUAGE
423 is
424 begin
425 delete from IBY_VALIDATION_PARAMS_TL T
426 where not exists
427 (select NULL
431 );
428 from IBY_VALIDATION_PARAMS_B B
429 where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
430 and B.VALIDATION_PARAMETER_CODE = T.VALIDATION_PARAMETER_CODE
432
433 update IBY_VALIDATION_PARAMS_TL T set (
434 VALIDATION_PARAM_DISPLAY_NAME
435 ) = (select
436 B.VALIDATION_PARAM_DISPLAY_NAME
437 from IBY_VALIDATION_PARAMS_TL B
438 where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
439 and B.VALIDATION_PARAMETER_CODE = T.VALIDATION_PARAMETER_CODE
440 and B.LANGUAGE = T.SOURCE_LANG)
441 where (
442 T.VALIDATION_SET_CODE,
443 T.VALIDATION_PARAMETER_CODE,
444 T.LANGUAGE
445 ) in (select
446 SUBT.VALIDATION_SET_CODE,
447 SUBT.VALIDATION_PARAMETER_CODE,
448 SUBT.LANGUAGE
449 from IBY_VALIDATION_PARAMS_TL SUBB, IBY_VALIDATION_PARAMS_TL SUBT
450 where SUBB.VALIDATION_SET_CODE = SUBT.VALIDATION_SET_CODE
451 and SUBB.VALIDATION_PARAMETER_CODE = SUBT.VALIDATION_PARAMETER_CODE
452 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
453 and (SUBB.VALIDATION_PARAM_DISPLAY_NAME <> SUBT.VALIDATION_PARAM_DISPLAY_NAME
454 ));
455
456 insert into IBY_VALIDATION_PARAMS_TL (
457 VALIDATION_SET_CODE,
458 VALIDATION_PARAMETER_CODE,
459 VALIDATION_PARAM_DISPLAY_NAME,
460 CREATED_BY,
461 CREATION_DATE,
462 LAST_UPDATED_BY,
463 LAST_UPDATE_DATE,
464 LAST_UPDATE_LOGIN,
465 OBJECT_VERSION_NUMBER,
466 LANGUAGE,
467 SOURCE_LANG
468 ) select /*+ ORDERED */
469 B.VALIDATION_SET_CODE,
470 B.VALIDATION_PARAMETER_CODE,
471 B.VALIDATION_PARAM_DISPLAY_NAME,
472 B.CREATED_BY,
473 B.CREATION_DATE,
474 B.LAST_UPDATED_BY,
475 B.LAST_UPDATE_DATE,
476 B.LAST_UPDATE_LOGIN,
477 B.OBJECT_VERSION_NUMBER,
478 L.LANGUAGE_CODE,
479 B.SOURCE_LANG
480 from IBY_VALIDATION_PARAMS_TL B, FND_LANGUAGES L
481 where L.INSTALLED_FLAG in ('I', 'B')
482 and B.LANGUAGE = userenv('LANG')
483 and not exists
484 (select NULL
485 from IBY_VALIDATION_PARAMS_TL T
486 where T.VALIDATION_SET_CODE = B.VALIDATION_SET_CODE
487 and T.VALIDATION_PARAMETER_CODE = B.VALIDATION_PARAMETER_CODE
488 and T.LANGUAGE = L.LANGUAGE_CODE);
489 end VAL_PARAM_ADD_LANGUAGE;
490
491 procedure VAL_PARAM_LOAD_ROW (
492 X_VALIDATION_SET_CODE in VARCHAR2,
493 X_VALIDATION_PARAMETER_CODE in VARCHAR2,
494 X_VALIDATION_PARAMETER_TYPE in VARCHAR2,
495 X_VALIDATION_PARAM_DISPLAY_ORD in NUMBER,
496 X_OBJECT_VERSION_NUMBER in NUMBER,
497 X_VALIDATION_PARAM_DISPLAY_NAM in VARCHAR2,
498 X_LAST_UPDATE_DATE in DATE,
499 X_OWNER in VARCHAR2)
500 is
501 row_id VARCHAR2(200);
502 begin
503 VAL_PARAM_UPDATE_ROW (
504 X_VALIDATION_SET_CODE,
505 X_VALIDATION_PARAMETER_CODE,
506 X_VALIDATION_PARAMETER_TYPE,
507 X_VALIDATION_PARAM_DISPLAY_ORD,
508 X_OBJECT_VERSION_NUMBER,
509 X_VALIDATION_PARAM_DISPLAY_NAM,
510 X_LAST_UPDATE_DATE,
511 fnd_load_util.owner_id(X_OWNER),
512 fnd_load_util.owner_id(X_OWNER));
513 exception
514 when no_data_found then
515 VAL_PARAM_INSERT_ROW (
516 row_id,
517 X_VALIDATION_SET_CODE,
518 X_VALIDATION_PARAMETER_CODE,
519 X_VALIDATION_PARAMETER_TYPE,
520 X_VALIDATION_PARAM_DISPLAY_ORD,
521 X_OBJECT_VERSION_NUMBER,
522 X_VALIDATION_PARAM_DISPLAY_NAM,
523 X_LAST_UPDATE_DATE,
524 fnd_load_util.owner_id(X_OWNER),
525 X_LAST_UPDATE_DATE,
526 fnd_load_util.owner_id(X_OWNER),
527 fnd_load_util.owner_id(X_OWNER));
528 end;
529
530 procedure VAL_PARAM_TRANSLATE_ROW (
531 X_VALIDATION_SET_CODE in VARCHAR2,
532 X_VALIDATION_PARAMETER_CODE in VARCHAR2,
533 X_OBJECT_VERSION_NUMBER in NUMBER,
534 X_VALIDATION_PARAM_DISPLAY_NAM in VARCHAR2,
535 X_LAST_UPDATE_DATE in DATE,
536 X_OWNER in VARCHAR2)
537 is
538 begin
539 update iby_validation_params_tl set
540 VALIDATION_PARAM_DISPLAY_NAME = X_VALIDATION_PARAM_DISPLAY_NAM,
541 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
542 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
543 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
544 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
545 SOURCE_LANG = userenv('LANG')
546 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
547 and VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
548 and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
549 end;
550
551
552 procedure TRANS_PROT_INSERT_ROW (
553 X_ROWID in out NOCOPY VARCHAR2,
554 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
555 X_OBJECT_VERSION_NUMBER in NUMBER,
556 X_TRANSMIT_CODE_LANGUAGE in VARCHAR2,
557 X_TRANSMIT_CODE_PACKAGE in VARCHAR2,
558 X_TRANSMIT_CODE_ENTRY_POINT in VARCHAR2,
559 X_TRANSMIT_PROTOCOL_NAME in VARCHAR2,
560 X_CREATION_DATE in DATE,
561 X_CREATED_BY in NUMBER,
562 X_LAST_UPDATE_DATE in DATE,
563 X_LAST_UPDATED_BY in NUMBER,
564 X_LAST_UPDATE_LOGIN in NUMBER
565 ) is
566 cursor C is select ROWID from IBY_TRANSMIT_PROTOCOLS_B
567 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
568 ;
569 begin
570 insert into IBY_TRANSMIT_PROTOCOLS_B (
571 OBJECT_VERSION_NUMBER,
572 TRANSMIT_PROTOCOL_CODE,
573 TRANSMIT_CODE_LANGUAGE,
574 TRANSMIT_CODE_PACKAGE,
575 TRANSMIT_CODE_ENTRY_POINT,
576 CREATION_DATE,
577 CREATED_BY,
578 LAST_UPDATE_DATE,
579 LAST_UPDATED_BY,
580 LAST_UPDATE_LOGIN
581 ) values (
582 X_OBJECT_VERSION_NUMBER,
583 X_TRANSMIT_PROTOCOL_CODE,
584 X_TRANSMIT_CODE_LANGUAGE,
588 X_CREATED_BY,
585 X_TRANSMIT_CODE_PACKAGE,
586 X_TRANSMIT_CODE_ENTRY_POINT,
587 X_CREATION_DATE,
589 X_LAST_UPDATE_DATE,
590 X_LAST_UPDATED_BY,
591 X_LAST_UPDATE_LOGIN
592 );
593
594 insert into IBY_TRANSMIT_PROTOCOLS_TL (
595 TRANSMIT_PROTOCOL_CODE,
596 TRANSMIT_PROTOCOL_NAME,
597 CREATED_BY,
598 CREATION_DATE,
599 LAST_UPDATED_BY,
600 LAST_UPDATE_DATE,
601 LAST_UPDATE_LOGIN,
602 OBJECT_VERSION_NUMBER,
603 LANGUAGE,
604 SOURCE_LANG
605 ) select
606 X_TRANSMIT_PROTOCOL_CODE,
607 X_TRANSMIT_PROTOCOL_NAME,
608 X_CREATED_BY,
609 X_CREATION_DATE,
610 X_LAST_UPDATED_BY,
611 X_LAST_UPDATE_DATE,
612 X_LAST_UPDATE_LOGIN,
613 X_OBJECT_VERSION_NUMBER,
614 L.LANGUAGE_CODE,
615 userenv('LANG')
616 from FND_LANGUAGES L
617 where L.INSTALLED_FLAG in ('I', 'B')
618 and not exists
619 (select NULL
620 from IBY_TRANSMIT_PROTOCOLS_TL T
621 where T.TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
622 and T.LANGUAGE = L.LANGUAGE_CODE);
623
624 open c;
625 fetch c into X_ROWID;
626 if (c%notfound) then
627 close c;
628 raise no_data_found;
629 end if;
630 close c;
631
632 end TRANS_PROT_INSERT_ROW;
633
634 procedure TRANS_PROT_UPDATE_ROW (
635 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
636 X_OBJECT_VERSION_NUMBER in NUMBER,
637 X_TRANSMIT_CODE_LANGUAGE in VARCHAR2,
638 X_TRANSMIT_CODE_PACKAGE in VARCHAR2,
639 X_TRANSMIT_CODE_ENTRY_POINT in VARCHAR2,
640 X_TRANSMIT_PROTOCOL_NAME in VARCHAR2,
641 X_LAST_UPDATE_DATE in DATE,
642 X_LAST_UPDATED_BY in NUMBER,
643 X_LAST_UPDATE_LOGIN in NUMBER
644 ) is
645 begin
646 update IBY_TRANSMIT_PROTOCOLS_B set
647 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
648 TRANSMIT_CODE_LANGUAGE = X_TRANSMIT_CODE_LANGUAGE,
649 TRANSMIT_CODE_PACKAGE = X_TRANSMIT_CODE_PACKAGE,
650 TRANSMIT_CODE_ENTRY_POINT = X_TRANSMIT_CODE_ENTRY_POINT,
651 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
652 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
653 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
654 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
655
656 if (sql%notfound) then
657 raise no_data_found;
658 end if;
659
660 update IBY_TRANSMIT_PROTOCOLS_TL set
661 TRANSMIT_PROTOCOL_NAME = X_TRANSMIT_PROTOCOL_NAME,
662 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
663 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
664 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
665 SOURCE_LANG = userenv('LANG')
666 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
667 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
668
669 if (sql%notfound) then
670 raise no_data_found;
671 end if;
672 end TRANS_PROT_UPDATE_ROW;
673
674 procedure TRANS_PROT_DELETE_ROW (
675 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2
676 ) is
677 begin
678 delete from IBY_TRANSMIT_PROTOCOLS_TL
679 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
680
681 if (sql%notfound) then
682 raise no_data_found;
683 end if;
684
685 delete from IBY_TRANSMIT_PROTOCOLS_B
686 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
687
688 if (sql%notfound) then
689 raise no_data_found;
690 end if;
691 end TRANS_PROT_DELETE_ROW;
692
693 procedure TRANS_PROT_ADD_LANGUAGE
694 is
695 begin
696 delete from IBY_TRANSMIT_PROTOCOLS_TL T
697 where not exists
698 (select NULL
699 from IBY_TRANSMIT_PROTOCOLS_B B
700 where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
701 );
702
703 update IBY_TRANSMIT_PROTOCOLS_TL T set (
704 TRANSMIT_PROTOCOL_NAME
705 ) = (select
706 B.TRANSMIT_PROTOCOL_NAME
707 from IBY_TRANSMIT_PROTOCOLS_TL B
708 where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
709 and B.LANGUAGE = T.SOURCE_LANG)
710 where (
711 T.TRANSMIT_PROTOCOL_CODE,
712 T.LANGUAGE
713 ) in (select
714 SUBT.TRANSMIT_PROTOCOL_CODE,
715 SUBT.LANGUAGE
716 from IBY_TRANSMIT_PROTOCOLS_TL SUBB, IBY_TRANSMIT_PROTOCOLS_TL SUBT
717 where SUBB.TRANSMIT_PROTOCOL_CODE = SUBT.TRANSMIT_PROTOCOL_CODE
718 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
719 and (SUBB.TRANSMIT_PROTOCOL_NAME <> SUBT.TRANSMIT_PROTOCOL_NAME
720 ));
721
722 insert into IBY_TRANSMIT_PROTOCOLS_TL (
723 TRANSMIT_PROTOCOL_CODE,
724 TRANSMIT_PROTOCOL_NAME,
725 CREATED_BY,
726 CREATION_DATE,
727 LAST_UPDATED_BY,
728 LAST_UPDATE_DATE,
729 LAST_UPDATE_LOGIN,
730 OBJECT_VERSION_NUMBER,
731 LANGUAGE,
732 SOURCE_LANG
733 ) select /*+ ORDERED */
734 B.TRANSMIT_PROTOCOL_CODE,
735 B.TRANSMIT_PROTOCOL_NAME,
736 B.CREATED_BY,
737 B.CREATION_DATE,
738 B.LAST_UPDATED_BY,
739 B.LAST_UPDATE_DATE,
740 B.LAST_UPDATE_LOGIN,
741 B.OBJECT_VERSION_NUMBER,
742 L.LANGUAGE_CODE,
743 B.SOURCE_LANG
744 from IBY_TRANSMIT_PROTOCOLS_TL B, FND_LANGUAGES L
745 where L.INSTALLED_FLAG in ('I', 'B')
746 and B.LANGUAGE = userenv('LANG')
747 and not exists
748 (select NULL
749 from IBY_TRANSMIT_PROTOCOLS_TL T
750 where T.TRANSMIT_PROTOCOL_CODE = B.TRANSMIT_PROTOCOL_CODE
751 and T.LANGUAGE = L.LANGUAGE_CODE);
752 end TRANS_PROT_ADD_LANGUAGE;
753
754 procedure TRANS_PROT_LOAD_ROW (
755 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
756 X_OBJECT_VERSION_NUMBER in NUMBER,
760 X_TRANSMIT_PROTOCOL_NAME in VARCHAR2,
757 X_TRANSMIT_CODE_LANGUAGE in VARCHAR2,
758 X_TRANSMIT_CODE_PACKAGE in VARCHAR2,
759 X_TRANSMIT_CODE_ENTRY_POINT in VARCHAR2,
761 X_LAST_UPDATE_DATE in DATE,
762 X_OWNER in VARCHAR2)
763 is
764 row_id VARCHAR2(200);
765 begin
766 TRANS_PROT_UPDATE_ROW (
767 X_TRANSMIT_PROTOCOL_CODE,
768 X_OBJECT_VERSION_NUMBER,
769 X_TRANSMIT_CODE_LANGUAGE,
770 X_TRANSMIT_CODE_PACKAGE,
771 X_TRANSMIT_CODE_ENTRY_POINT,
772 X_TRANSMIT_PROTOCOL_NAME,
773 X_LAST_UPDATE_DATE,
774 fnd_load_util.owner_id(X_OWNER),
775 fnd_load_util.owner_id(X_OWNER));
776 exception
777 when no_data_found then
778 TRANS_PROT_INSERT_ROW (
779 row_id,
780 X_TRANSMIT_PROTOCOL_CODE,
781 X_OBJECT_VERSION_NUMBER,
782 X_TRANSMIT_CODE_LANGUAGE,
783 X_TRANSMIT_CODE_PACKAGE,
784 X_TRANSMIT_CODE_ENTRY_POINT,
785 X_TRANSMIT_PROTOCOL_NAME,
786 X_LAST_UPDATE_DATE,
787 fnd_load_util.owner_id(X_OWNER),
788 X_LAST_UPDATE_DATE,
789 fnd_load_util.owner_id(X_OWNER),
790 fnd_load_util.owner_id(X_OWNER));
791 end;
792
793 procedure TRANS_PROT_TRANSLATE_ROW (
794 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
795 X_OBJECT_VERSION_NUMBER in NUMBER,
796 X_TRANSMIT_PROTOCOL_NAME in VARCHAR2,
797 X_LAST_UPDATE_DATE in DATE,
798 X_OWNER in VARCHAR2)
799 is
800 begin
801 update iby_transmit_protocols_tl set
802 TRANSMIT_PROTOCOL_NAME = X_TRANSMIT_PROTOCOL_NAME,
803 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
804 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
805 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
806 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
807 SOURCE_LANG = userenv('LANG')
808 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
809 and TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
810 end;
811
812
813 procedure TRANS_PARAM_INSERT_ROW (
814 X_ROWID in out NOCOPY VARCHAR2,
815 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
816 X_TRANSMIT_PARAMETER_CODE in VARCHAR2,
817 X_TRANSMIT_PARAMETER_TYPE in VARCHAR2,
818 X_MANDATORY_FLAG in VARCHAR2,
819 X_DISPLAY_ORDER in NUMBER,
820 X_DYNAMIC_FLAG in VARCHAR2,
821 X_DYN_CODE_LANGUAGE in VARCHAR2,
822 X_DYN_CODE_PACKAGE in VARCHAR2,
823 X_DYN_CODE_ENTRY_POINT in VARCHAR2,
824 X_SECURED_FLAG in VARCHAR2,
825 X_OBJECT_VERSION_NUMBER in NUMBER,
826 X_TRANSMIT_PARAMETER_NAME in VARCHAR2,
827 X_CREATION_DATE in DATE,
828 X_CREATED_BY in NUMBER,
829 X_LAST_UPDATE_DATE in DATE,
830 X_LAST_UPDATED_BY in NUMBER,
831 X_LAST_UPDATE_LOGIN in NUMBER
832 ) is
833 cursor C is select ROWID from IBY_TRANSMIT_PARAMETERS_B
834 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
835 and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE
836 ;
837 begin
838 insert into IBY_TRANSMIT_PARAMETERS_B (
839 TRANSMIT_PARAMETER_CODE,
840 TRANSMIT_PARAMETER_TYPE,
841 TRANSMIT_PROTOCOL_CODE,
842 MANDATORY_FLAG,
843 DISPLAY_ORDER,
844 DYNAMIC_FLAG,
845 DYN_CODE_LANGUAGE,
846 DYN_CODE_PACKAGE,
847 DYN_CODE_ENTRY_POINT,
848 SECURED_FLAG,
849 OBJECT_VERSION_NUMBER,
850 CREATION_DATE,
851 CREATED_BY,
852 LAST_UPDATE_DATE,
853 LAST_UPDATED_BY,
854 LAST_UPDATE_LOGIN
855 ) values (
856 X_TRANSMIT_PARAMETER_CODE,
857 X_TRANSMIT_PARAMETER_TYPE,
858 X_TRANSMIT_PROTOCOL_CODE,
859 X_MANDATORY_FLAG,
860 X_DISPLAY_ORDER,
861 X_DYNAMIC_FLAG,
862 X_DYN_CODE_LANGUAGE,
863 X_DYN_CODE_PACKAGE,
864 X_DYN_CODE_ENTRY_POINT,
865 X_SECURED_FLAG,
866 X_OBJECT_VERSION_NUMBER,
867 X_CREATION_DATE,
868 X_CREATED_BY,
869 X_LAST_UPDATE_DATE,
870 X_LAST_UPDATED_BY,
871 X_LAST_UPDATE_LOGIN
872 );
873
874 insert into IBY_TRANSMIT_PARAMETERS_TL (
875 TRANSMIT_PARAMETER_CODE,
876 TRANSMIT_PROTOCOL_CODE,
877 TRANSMIT_PARAMETER_NAME,
878 CREATED_BY,
879 CREATION_DATE,
880 LAST_UPDATED_BY,
881 LAST_UPDATE_DATE,
882 LAST_UPDATE_LOGIN,
883 OBJECT_VERSION_NUMBER,
884 LANGUAGE,
885 SOURCE_LANG
886 ) select
887 X_TRANSMIT_PARAMETER_CODE,
888 X_TRANSMIT_PROTOCOL_CODE,
889 X_TRANSMIT_PARAMETER_NAME,
890 X_CREATED_BY,
891 X_CREATION_DATE,
892 X_LAST_UPDATED_BY,
893 X_LAST_UPDATE_DATE,
894 X_LAST_UPDATE_LOGIN,
895 X_OBJECT_VERSION_NUMBER,
896 L.LANGUAGE_CODE,
897 userenv('LANG')
898 from FND_LANGUAGES L
899 where L.INSTALLED_FLAG in ('I', 'B')
900 and not exists
901 (select NULL
902 from IBY_TRANSMIT_PARAMETERS_TL T
903 where T.TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
904 and T.TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE
905 and T.LANGUAGE = L.LANGUAGE_CODE);
906
907 open c;
908 fetch c into X_ROWID;
909 if (c%notfound) then
910 close c;
911 raise no_data_found;
912 end if;
913 close c;
914
915 end TRANS_PARAM_INSERT_ROW;
916
917 procedure TRANS_PARAM_UPDATE_ROW (
918 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
919 X_TRANSMIT_PARAMETER_CODE in VARCHAR2,
920 X_TRANSMIT_PARAMETER_TYPE in VARCHAR2,
921 X_MANDATORY_FLAG in VARCHAR2,
922 X_DISPLAY_ORDER in NUMBER,
923 X_DYNAMIC_FLAG in VARCHAR2,
924 X_DYN_CODE_LANGUAGE in VARCHAR2,
925 X_DYN_CODE_PACKAGE in VARCHAR2,
926 X_DYN_CODE_ENTRY_POINT in VARCHAR2,
927 X_SECURED_FLAG in VARCHAR2,
931 X_LAST_UPDATED_BY in NUMBER,
928 X_OBJECT_VERSION_NUMBER in NUMBER,
929 X_TRANSMIT_PARAMETER_NAME in VARCHAR2,
930 X_LAST_UPDATE_DATE in DATE,
932 X_LAST_UPDATE_LOGIN in NUMBER
933 ) is
934 begin
935 update IBY_TRANSMIT_PARAMETERS_B set
936 TRANSMIT_PARAMETER_TYPE = X_TRANSMIT_PARAMETER_TYPE,
937 MANDATORY_FLAG = X_MANDATORY_FLAG,
938 DISPLAY_ORDER = X_DISPLAY_ORDER,
939 DYNAMIC_FLAG = X_DYNAMIC_FLAG,
940 DYN_CODE_LANGUAGE = X_DYN_CODE_LANGUAGE,
941 DYN_CODE_PACKAGE = X_DYN_CODE_PACKAGE,
942 DYN_CODE_ENTRY_POINT = X_DYN_CODE_ENTRY_POINT,
943 SECURED_FLAG = X_SECURED_FLAG,
944 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
945 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
946 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
947 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
948 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
949 and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
950
951 if (sql%notfound) then
952 raise no_data_found;
953 end if;
954
955 update IBY_TRANSMIT_PARAMETERS_TL set
956 TRANSMIT_PARAMETER_NAME = X_TRANSMIT_PARAMETER_NAME,
957 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
958 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
959 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
960 SOURCE_LANG = userenv('LANG')
961 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
962 and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE
963 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
964
965 if (sql%notfound) then
966 raise no_data_found;
967 end if;
968 end TRANS_PARAM_UPDATE_ROW;
969
970 procedure TRANS_PARAM_DELETE_ROW (
971 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
972 X_TRANSMIT_PARAMETER_CODE in VARCHAR2
973 ) is
974 begin
975 delete from IBY_TRANSMIT_PARAMETERS_TL
976 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
977 and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
978
979 if (sql%notfound) then
980 raise no_data_found;
981 end if;
982
983 delete from IBY_TRANSMIT_PARAMETERS_B
984 where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
985 and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
986
987 if (sql%notfound) then
988 raise no_data_found;
989 end if;
990 end TRANS_PARAM_DELETE_ROW;
991
992 procedure TRANS_PARAM_ADD_LANGUAGE
993 is
994 begin
995 delete from IBY_TRANSMIT_PARAMETERS_TL T
996 where not exists
997 (select NULL
998 from IBY_TRANSMIT_PARAMETERS_B B
999 where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
1000 and B.TRANSMIT_PARAMETER_CODE = T.TRANSMIT_PARAMETER_CODE
1001 );
1002
1003 update IBY_TRANSMIT_PARAMETERS_TL T set (
1004 TRANSMIT_PARAMETER_NAME
1005 ) = (select
1006 B.TRANSMIT_PARAMETER_NAME
1007 from IBY_TRANSMIT_PARAMETERS_TL B
1008 where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
1009 and B.TRANSMIT_PARAMETER_CODE = T.TRANSMIT_PARAMETER_CODE
1010 and B.LANGUAGE = T.SOURCE_LANG)
1011 where (
1012 T.TRANSMIT_PROTOCOL_CODE,
1013 T.TRANSMIT_PARAMETER_CODE,
1014 T.LANGUAGE
1015 ) in (select
1016 SUBT.TRANSMIT_PROTOCOL_CODE,
1017 SUBT.TRANSMIT_PARAMETER_CODE,
1018 SUBT.LANGUAGE
1019 from IBY_TRANSMIT_PARAMETERS_TL SUBB, IBY_TRANSMIT_PARAMETERS_TL SUBT
1020 where SUBB.TRANSMIT_PROTOCOL_CODE = SUBT.TRANSMIT_PROTOCOL_CODE
1021 and SUBB.TRANSMIT_PARAMETER_CODE = SUBT.TRANSMIT_PARAMETER_CODE
1022 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1023 and (SUBB.TRANSMIT_PARAMETER_NAME <> SUBT.TRANSMIT_PARAMETER_NAME
1024 ));
1025
1026 insert into IBY_TRANSMIT_PARAMETERS_TL (
1027 TRANSMIT_PARAMETER_CODE,
1028 TRANSMIT_PROTOCOL_CODE,
1029 TRANSMIT_PARAMETER_NAME,
1030 CREATED_BY,
1031 CREATION_DATE,
1032 LAST_UPDATED_BY,
1033 LAST_UPDATE_DATE,
1034 LAST_UPDATE_LOGIN,
1035 OBJECT_VERSION_NUMBER,
1036 LANGUAGE,
1037 SOURCE_LANG
1038 ) select /*+ ORDERED */
1039 B.TRANSMIT_PARAMETER_CODE,
1040 B.TRANSMIT_PROTOCOL_CODE,
1041 B.TRANSMIT_PARAMETER_NAME,
1042 B.CREATED_BY,
1043 B.CREATION_DATE,
1044 B.LAST_UPDATED_BY,
1045 B.LAST_UPDATE_DATE,
1046 B.LAST_UPDATE_LOGIN,
1047 B.OBJECT_VERSION_NUMBER,
1048 L.LANGUAGE_CODE,
1049 B.SOURCE_LANG
1050 from IBY_TRANSMIT_PARAMETERS_TL B, FND_LANGUAGES L
1051 where L.INSTALLED_FLAG in ('I', 'B')
1052 and B.LANGUAGE = userenv('LANG')
1053 and not exists
1054 (select NULL
1055 from IBY_TRANSMIT_PARAMETERS_TL T
1056 where T.TRANSMIT_PROTOCOL_CODE = B.TRANSMIT_PROTOCOL_CODE
1057 and T.TRANSMIT_PARAMETER_CODE = B.TRANSMIT_PARAMETER_CODE
1058 and T.LANGUAGE = L.LANGUAGE_CODE);
1059 end TRANS_PARAM_ADD_LANGUAGE;
1060
1061 procedure TRANS_PARAM_LOAD_ROW (
1062 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
1063 X_TRANSMIT_PARAMETER_CODE in VARCHAR2,
1064 X_TRANSMIT_PARAMETER_TYPE in VARCHAR2,
1065 X_MANDATORY_FLAG in VARCHAR2,
1066 X_DISPLAY_ORDER in NUMBER,
1067 X_DYNAMIC_FLAG in VARCHAR2,
1068 X_DYN_CODE_LANGUAGE in VARCHAR2,
1069 X_DYN_CODE_PACKAGE in VARCHAR2,
1070 X_DYN_CODE_ENTRY_POINT in VARCHAR2,
1071 X_SECURED_FLAG in VARCHAR2,
1072 X_OBJECT_VERSION_NUMBER in NUMBER,
1073 X_TRANSMIT_PARAMETER_NAME in VARCHAR2,
1074 X_LAST_UPDATE_DATE in DATE,
1075 X_OWNER in VARCHAR2)
1076 is
1077 row_id VARCHAR2(200);
1078 begin
1079 TRANS_PARAM_UPDATE_ROW (
1080 X_TRANSMIT_PROTOCOL_CODE,
1081 X_TRANSMIT_PARAMETER_CODE,
1082 X_TRANSMIT_PARAMETER_TYPE,
1083 X_MANDATORY_FLAG,
1084 X_DISPLAY_ORDER,
1085 X_DYNAMIC_FLAG,
1086 X_DYN_CODE_LANGUAGE,
1090 X_OBJECT_VERSION_NUMBER,
1087 X_DYN_CODE_PACKAGE,
1088 X_DYN_CODE_ENTRY_POINT,
1089 X_SECURED_FLAG,
1091 X_TRANSMIT_PARAMETER_NAME,
1092 X_LAST_UPDATE_DATE,
1093 fnd_load_util.owner_id(X_OWNER),
1094 fnd_load_util.owner_id(X_OWNER));
1095 exception
1096 when no_data_found then
1097 TRANS_PARAM_INSERT_ROW (
1098 row_id,
1099 X_TRANSMIT_PROTOCOL_CODE,
1100 X_TRANSMIT_PARAMETER_CODE,
1101 X_TRANSMIT_PARAMETER_TYPE,
1102 X_MANDATORY_FLAG,
1103 X_DISPLAY_ORDER,
1104 X_DYNAMIC_FLAG,
1105 X_DYN_CODE_LANGUAGE,
1106 X_DYN_CODE_PACKAGE,
1107 X_DYN_CODE_ENTRY_POINT,
1108 X_SECURED_FLAG,
1109 X_OBJECT_VERSION_NUMBER,
1110 X_TRANSMIT_PARAMETER_NAME,
1111 X_LAST_UPDATE_DATE,
1112 fnd_load_util.owner_id(X_OWNER),
1113 X_LAST_UPDATE_DATE,
1114 fnd_load_util.owner_id(X_OWNER),
1115 fnd_load_util.owner_id(X_OWNER));
1116 end;
1117
1118 procedure TRANS_PARAM_TRANSLATE_ROW (
1119 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
1120 X_TRANSMIT_PARAMETER_CODE in VARCHAR2,
1121 X_OBJECT_VERSION_NUMBER in NUMBER,
1122 X_TRANSMIT_PARAMETER_NAME in VARCHAR2,
1123 X_LAST_UPDATE_DATE in DATE,
1124 X_OWNER in VARCHAR2)
1125 is
1126 begin
1127 update iby_transmit_parameters_tl set
1128 TRANSMIT_PARAMETER_NAME = X_TRANSMIT_PARAMETER_NAME,
1129 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1130 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
1131 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1132 LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
1133 SOURCE_LANG = userenv('LANG')
1134 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1135 and TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
1136 and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
1137 end;
1138
1139
1140 procedure TRANS_CONFIG_INSERT_ROW (
1141 X_ROWID in out NOCOPY VARCHAR2,
1142 X_TRANSMIT_CONFIGURATION_ID in NUMBER,
1143 X_OBJECT_VERSION_NUMBER in NUMBER,
1144 X_TUNNELING_TRANS_CONFIG_ID in NUMBER,
1145 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
1146 X_INACTIVE_DATE in DATE,
1147 X_TRANSMIT_CONFIGURATION_NAME in VARCHAR2,
1148 X_CREATION_DATE in DATE,
1149 X_CREATED_BY in NUMBER,
1150 X_LAST_UPDATE_DATE in DATE,
1151 X_LAST_UPDATED_BY in NUMBER,
1152 X_LAST_UPDATE_LOGIN in NUMBER
1153 ) is
1154 cursor C is select ROWID from IBY_TRANSMIT_CONFIGS_B
1155 where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID
1156 ;
1157 begin
1158 insert into IBY_TRANSMIT_CONFIGS_B (
1159 OBJECT_VERSION_NUMBER,
1160 TUNNELING_TRANS_CONFIG_ID,
1161 TRANSMIT_CONFIGURATION_ID,
1162 TRANSMIT_PROTOCOL_CODE,
1163 INACTIVE_DATE,
1164 CREATION_DATE,
1165 CREATED_BY,
1166 LAST_UPDATE_DATE,
1167 LAST_UPDATED_BY,
1168 LAST_UPDATE_LOGIN
1169 ) values (
1170 X_OBJECT_VERSION_NUMBER,
1171 X_TUNNELING_TRANS_CONFIG_ID,
1172 X_TRANSMIT_CONFIGURATION_ID,
1173 X_TRANSMIT_PROTOCOL_CODE,
1174 X_INACTIVE_DATE,
1175 X_CREATION_DATE,
1176 X_CREATED_BY,
1177 X_LAST_UPDATE_DATE,
1178 X_LAST_UPDATED_BY,
1179 X_LAST_UPDATE_LOGIN
1180 );
1181
1182 insert into IBY_TRANSMIT_CONFIGS_TL (
1183 CREATION_DATE,
1184 LAST_UPDATED_BY,
1185 LAST_UPDATE_DATE,
1186 LAST_UPDATE_LOGIN,
1187 OBJECT_VERSION_NUMBER,
1188 TRANSMIT_CONFIGURATION_NAME,
1189 CREATED_BY,
1190 TRANSMIT_CONFIGURATION_ID,
1191 LANGUAGE,
1192 SOURCE_LANG
1193 ) select
1194 X_CREATION_DATE,
1195 X_LAST_UPDATED_BY,
1196 X_LAST_UPDATE_DATE,
1197 X_LAST_UPDATE_LOGIN,
1198 X_OBJECT_VERSION_NUMBER,
1199 X_TRANSMIT_CONFIGURATION_NAME,
1200 X_CREATED_BY,
1201 X_TRANSMIT_CONFIGURATION_ID,
1202 L.LANGUAGE_CODE,
1203 userenv('LANG')
1204 from FND_LANGUAGES L
1205 where L.INSTALLED_FLAG in ('I', 'B')
1206 and not exists
1207 (select NULL
1208 from IBY_TRANSMIT_CONFIGS_TL T
1209 where T.TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID
1210 and T.LANGUAGE = L.LANGUAGE_CODE);
1211
1212 open c;
1213 fetch c into X_ROWID;
1214 if (c%notfound) then
1215 close c;
1216 raise no_data_found;
1217 end if;
1218 close c;
1219
1220 end TRANS_CONFIG_INSERT_ROW;
1221
1222 procedure TRANS_CONFIG_UPDATE_ROW (
1223 X_TRANSMIT_CONFIGURATION_ID in NUMBER,
1224 X_OBJECT_VERSION_NUMBER in NUMBER,
1225 X_TUNNELING_TRANS_CONFIG_ID in NUMBER,
1226 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
1227 X_INACTIVE_DATE in DATE,
1228 X_TRANSMIT_CONFIGURATION_NAME in VARCHAR2,
1229 X_LAST_UPDATE_DATE in DATE,
1230 X_LAST_UPDATED_BY in NUMBER,
1231 X_LAST_UPDATE_LOGIN in NUMBER
1232 ) is
1233 begin
1234 update IBY_TRANSMIT_CONFIGS_B set
1235 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
1236 TUNNELING_TRANS_CONFIG_ID = X_TUNNELING_TRANS_CONFIG_ID,
1237 TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE,
1238 INACTIVE_DATE = X_INACTIVE_DATE,
1239 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1240 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1241 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1242 where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID;
1243
1244 if (sql%notfound) then
1245 raise no_data_found;
1246 end if;
1247
1248 update IBY_TRANSMIT_CONFIGS_TL set
1249 TRANSMIT_CONFIGURATION_NAME = X_TRANSMIT_CONFIGURATION_NAME,
1250 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1251 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1252 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1253 SOURCE_LANG = userenv('LANG')
1254 where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID
1255 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1256
1257 if (sql%notfound) then
1258 raise no_data_found;
1259 end if;
1260 end TRANS_CONFIG_UPDATE_ROW;
1261
1262 procedure TRANS_CONFIG_DELETE_ROW (
1263 X_TRANSMIT_CONFIGURATION_ID in NUMBER
1264 ) is
1265 begin
1266 delete from IBY_TRANSMIT_CONFIGS_TL
1267 where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID;
1268
1269 if (sql%notfound) then
1270 raise no_data_found;
1271 end if;
1272
1273 delete from IBY_TRANSMIT_CONFIGS_B
1274 where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID;
1275
1276 if (sql%notfound) then
1277 raise no_data_found;
1278 end if;
1279 end TRANS_CONFIG_DELETE_ROW;
1280
1281 procedure TRANS_CONFIG_ADD_LANGUAGE
1282 is
1283 begin
1284 delete from IBY_TRANSMIT_CONFIGS_TL T
1285 where not exists
1286 (select NULL
1287 from IBY_TRANSMIT_CONFIGS_B B
1288 where B.TRANSMIT_CONFIGURATION_ID = T.TRANSMIT_CONFIGURATION_ID
1289 );
1290
1291 update IBY_TRANSMIT_CONFIGS_TL T set (
1292 TRANSMIT_CONFIGURATION_NAME
1293 ) = (select
1294 B.TRANSMIT_CONFIGURATION_NAME
1295 from IBY_TRANSMIT_CONFIGS_TL B
1296 where B.TRANSMIT_CONFIGURATION_ID = T.TRANSMIT_CONFIGURATION_ID
1297 and B.LANGUAGE = T.SOURCE_LANG)
1298 where (
1299 T.TRANSMIT_CONFIGURATION_ID,
1300 T.LANGUAGE
1301 ) in (select
1302 SUBT.TRANSMIT_CONFIGURATION_ID,
1303 SUBT.LANGUAGE
1304 from IBY_TRANSMIT_CONFIGS_TL SUBB, IBY_TRANSMIT_CONFIGS_TL SUBT
1305 where SUBB.TRANSMIT_CONFIGURATION_ID = SUBT.TRANSMIT_CONFIGURATION_ID
1306 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1307 and (SUBB.TRANSMIT_CONFIGURATION_NAME <> SUBT.TRANSMIT_CONFIGURATION_NAME
1308 ));
1309
1310 insert into IBY_TRANSMIT_CONFIGS_TL (
1311 CREATION_DATE,
1312 LAST_UPDATED_BY,
1313 LAST_UPDATE_DATE,
1314 LAST_UPDATE_LOGIN,
1315 OBJECT_VERSION_NUMBER,
1316 TRANSMIT_CONFIGURATION_NAME,
1317 CREATED_BY,
1318 TRANSMIT_CONFIGURATION_ID,
1319 LANGUAGE,
1320 SOURCE_LANG
1321 ) select /*+ ORDERED */
1322 B.CREATION_DATE,
1323 B.LAST_UPDATED_BY,
1324 B.LAST_UPDATE_DATE,
1325 B.LAST_UPDATE_LOGIN,
1326 B.OBJECT_VERSION_NUMBER,
1327 B.TRANSMIT_CONFIGURATION_NAME,
1328 B.CREATED_BY,
1329 B.TRANSMIT_CONFIGURATION_ID,
1330 L.LANGUAGE_CODE,
1331 B.SOURCE_LANG
1332 from IBY_TRANSMIT_CONFIGS_TL B, FND_LANGUAGES L
1333 where L.INSTALLED_FLAG in ('I', 'B')
1334 and B.LANGUAGE = userenv('LANG')
1335 and not exists
1336 (select NULL
1337 from IBY_TRANSMIT_CONFIGS_TL T
1338 where T.TRANSMIT_CONFIGURATION_ID = B.TRANSMIT_CONFIGURATION_ID
1339 and T.LANGUAGE = L.LANGUAGE_CODE);
1340 end TRANS_CONFIG_ADD_LANGUAGE;
1341
1342
1343 END IBY_PP_MLSUTL_PVT;