[Home] [Help]
PACKAGE BODY: APPS.AMS_CHANNELS_PKG
Source
1 PACKAGE BODY ams_channels_pkg AS
2 /* $Header: amslchab.pls 115.4 2002/11/16 01:44:21 dbiswas ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_CHANNEL_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_CHANNEL_TYPE_CODE in VARCHAR2,
8 X_ORDER_SEQUENCE in NUMBER,
9 X_MANAGED_BY_PERSON_ID in NUMBER,
10 X_OUTBOUND_FLAG in VARCHAR2,
11 X_INBOUND_FLAG in VARCHAR2,
12 X_ACTIVE_FROM_DATE in DATE,
13 X_ACTIVE_TO_DATE in DATE,
14 X_RATING in VARCHAR2,
15 X_PREFERRED_VENDOR_ID in NUMBER,
16 X_PARTY_ID in NUMBER,
17 X_ATTRIBUTE_CATEGORY in VARCHAR2,
18 X_ATTRIBUTE1 in VARCHAR2,
19 X_ATTRIBUTE2 in VARCHAR2,
20 X_ATTRIBUTE3 in VARCHAR2,
21 X_ATTRIBUTE4 in VARCHAR2,
22 X_ATTRIBUTE5 in VARCHAR2,
23 X_ATTRIBUTE6 in VARCHAR2,
24 X_ATTRIBUTE7 in VARCHAR2,
25 X_ATTRIBUTE8 in VARCHAR2,
26 X_ATTRIBUTE9 in VARCHAR2,
27 X_ATTRIBUTE10 in VARCHAR2,
28 X_ATTRIBUTE11 in VARCHAR2,
29 X_ATTRIBUTE12 in VARCHAR2,
30 X_ATTRIBUTE13 in VARCHAR2,
31 X_ATTRIBUTE14 in VARCHAR2,
32 X_ATTRIBUTE15 in VARCHAR2,
33 X_CHANNEL_NAME in VARCHAR2,
34 X_DESCRIPTION in VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER
40 ) is
41 cursor C is select ROWID from AMS_CHANNELS_B
42 where CHANNEL_ID = X_CHANNEL_ID
43 ;
44 begin
45 insert into AMS_CHANNELS_B (
46 CHANNEL_ID,
47 OBJECT_VERSION_NUMBER,
48 CHANNEL_TYPE_CODE,
49 ORDER_SEQUENCE,
50 MANAGED_BY_PERSON_ID,
51 OUTBOUND_FLAG,
52 INBOUND_FLAG,
53 ACTIVE_FROM_DATE,
54 ACTIVE_TO_DATE,
55 RATING,
56 PREFERRED_VENDOR_ID,
57 PARTY_ID ,
58 ATTRIBUTE_CATEGORY,
59 ATTRIBUTE1,
60 ATTRIBUTE2,
61 ATTRIBUTE3,
62 ATTRIBUTE4,
63 ATTRIBUTE5,
64 ATTRIBUTE6,
65 ATTRIBUTE7,
66 ATTRIBUTE8,
67 ATTRIBUTE9,
68 ATTRIBUTE10,
69 ATTRIBUTE11,
70 ATTRIBUTE12,
71 ATTRIBUTE13,
72 ATTRIBUTE14,
73 ATTRIBUTE15,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_DATE,
77 LAST_UPDATED_BY,
78 LAST_UPDATE_LOGIN
79 ) values (
80 X_CHANNEL_ID,
81 X_OBJECT_VERSION_NUMBER,
82 X_CHANNEL_TYPE_CODE,
83 X_ORDER_SEQUENCE,
84 X_MANAGED_BY_PERSON_ID,
85 X_OUTBOUND_FLAG,
86 X_INBOUND_FLAG,
87 X_ACTIVE_FROM_DATE,
88 X_ACTIVE_TO_DATE,
89 X_RATING,
90 X_PREFERRED_VENDOR_ID,
91 X_PARTY_ID,
92 X_ATTRIBUTE_CATEGORY,
93 X_ATTRIBUTE1,
94 X_ATTRIBUTE2,
95 X_ATTRIBUTE3,
96 X_ATTRIBUTE4,
97 X_ATTRIBUTE5,
98 X_ATTRIBUTE6,
99 X_ATTRIBUTE7,
100 X_ATTRIBUTE8,
101 X_ATTRIBUTE9,
102 X_ATTRIBUTE10,
103 X_ATTRIBUTE11,
104 X_ATTRIBUTE12,
105 X_ATTRIBUTE13,
106 X_ATTRIBUTE14,
107 X_ATTRIBUTE15,
108 X_CREATION_DATE,
109 X_CREATED_BY,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN
113 );
114
115 insert into AMS_CHANNELS_TL (
116 CHANNEL_ID,
117 LAST_UPDATE_DATE,
118 LAST_UPDATED_BY,
119 CREATION_DATE,
120 CREATED_BY,
121 LAST_UPDATE_LOGIN,
122 CHANNEL_NAME,
123 DESCRIPTION,
124 LANGUAGE,
125 SOURCE_LANG
126 ) select
127 X_CHANNEL_ID,
128 X_LAST_UPDATE_DATE,
129 X_LAST_UPDATED_BY,
130 X_CREATION_DATE,
131 X_CREATED_BY,
132 X_LAST_UPDATE_LOGIN,
133 X_CHANNEL_NAME,
134 X_DESCRIPTION,
135 L.LANGUAGE_CODE,
136 userenv('LANG')
137 from FND_LANGUAGES L
138 where L.INSTALLED_FLAG in ('I', 'B')
139 and not exists
140 (select NULL
141 from AMS_CHANNELS_TL T
142 where T.CHANNEL_ID = X_CHANNEL_ID
143 and T.LANGUAGE = L.LANGUAGE_CODE);
144
145 open c;
146 fetch c into X_ROWID;
147 if (c%notfound) then
148 close c;
149 raise no_data_found;
150 end if;
151 close c;
152
153 end INSERT_ROW;
154
155 procedure LOCK_ROW (
156 X_CHANNEL_ID in NUMBER,
157 X_OBJECT_VERSION_NUMBER in NUMBER,
158 X_CHANNEL_TYPE_CODE in VARCHAR2,
159 X_ORDER_SEQUENCE in NUMBER,
160 X_MANAGED_BY_PERSON_ID in NUMBER,
161 X_OUTBOUND_FLAG in VARCHAR2,
162 X_INBOUND_FLAG in VARCHAR2,
163 X_ACTIVE_FROM_DATE in DATE,
164 X_ACTIVE_TO_DATE in DATE,
165 X_RATING in VARCHAR2,
166 X_PREFERRED_VENDOR_ID in NUMBER,
167 X_PARTY_ID in NUMBER,
168 X_ATTRIBUTE_CATEGORY in VARCHAR2,
169 X_ATTRIBUTE1 in VARCHAR2,
170 X_ATTRIBUTE2 in VARCHAR2,
171 X_ATTRIBUTE3 in VARCHAR2,
172 X_ATTRIBUTE4 in VARCHAR2,
173 X_ATTRIBUTE5 in VARCHAR2,
174 X_ATTRIBUTE6 in VARCHAR2,
175 X_ATTRIBUTE7 in VARCHAR2,
176 X_ATTRIBUTE8 in VARCHAR2,
177 X_ATTRIBUTE9 in VARCHAR2,
178 X_ATTRIBUTE10 in VARCHAR2,
179 X_ATTRIBUTE11 in VARCHAR2,
180 X_ATTRIBUTE12 in VARCHAR2,
181 X_ATTRIBUTE13 in VARCHAR2,
182 X_ATTRIBUTE14 in VARCHAR2,
183 X_ATTRIBUTE15 in VARCHAR2,
184 X_CHANNEL_NAME in VARCHAR2,
185 X_DESCRIPTION in VARCHAR2
186 ) is
187 cursor c is select
188 OBJECT_VERSION_NUMBER,
189 CHANNEL_TYPE_CODE,
190 ORDER_SEQUENCE,
191 MANAGED_BY_PERSON_ID,
192 OUTBOUND_FLAG,
193 INBOUND_FLAG,
194 ACTIVE_FROM_DATE,
195 ACTIVE_TO_DATE,
196 RATING,
197 PREFERRED_VENDOR_ID,
198 PARTY_ID ,
199 ATTRIBUTE_CATEGORY,
200 ATTRIBUTE1,
201 ATTRIBUTE2,
202 ATTRIBUTE3,
203 ATTRIBUTE4,
204 ATTRIBUTE5,
205 ATTRIBUTE6,
206 ATTRIBUTE7,
207 ATTRIBUTE8,
208 ATTRIBUTE9,
209 ATTRIBUTE10,
210 ATTRIBUTE11,
211 ATTRIBUTE12,
212 ATTRIBUTE13,
213 ATTRIBUTE14,
214 ATTRIBUTE15
215 from AMS_CHANNELS_B
216 where CHANNEL_ID = X_CHANNEL_ID
217 for update of CHANNEL_ID nowait;
218 recinfo c%rowtype;
219
220 cursor c1 is select
221 CHANNEL_NAME,
222 DESCRIPTION,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from AMS_CHANNELS_TL
225 where CHANNEL_ID = X_CHANNEL_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of CHANNEL_ID nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
238 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
239 AND (recinfo.CHANNEL_TYPE_CODE = X_CHANNEL_TYPE_CODE)
240 AND ((recinfo.ORDER_SEQUENCE = X_ORDER_SEQUENCE)
241 OR ((recinfo.ORDER_SEQUENCE is null) AND (X_ORDER_SEQUENCE is null)))
242 AND ((recinfo.MANAGED_BY_PERSON_ID = X_MANAGED_BY_PERSON_ID)
243 OR ((recinfo.MANAGED_BY_PERSON_ID is null) AND (X_MANAGED_BY_PERSON_ID is null)))
244 AND (recinfo.OUTBOUND_FLAG = X_OUTBOUND_FLAG)
245 AND (recinfo.INBOUND_FLAG = X_INBOUND_FLAG)
246 AND (recinfo.ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE)
247 AND ((recinfo.ACTIVE_TO_DATE = X_ACTIVE_TO_DATE)
248 OR ((recinfo.ACTIVE_TO_DATE is null) AND (X_ACTIVE_TO_DATE is null)))
249 AND ((recinfo.RATING = X_RATING)
250 OR ((recinfo.RATING is null) AND (X_RATING is null)))
251 AND ((recinfo.PREFERRED_VENDOR_ID = X_PREFERRED_VENDOR_ID)
252 OR ((recinfo.PREFERRED_VENDOR_ID is null) AND (X_PREFERRED_VENDOR_ID is null)))
253 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
254 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
255 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
256 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
257 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
258 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
259 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
260 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
261 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
262 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
263 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
264 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
265 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
266 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
267 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
268 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
269 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
270 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
271 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
272 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
273 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
274 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
275 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
276 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
277 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
278 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
279 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
280 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
281 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
282 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
283 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
284 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
285 ) then
286 null;
287 else
288 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
289 app_exception.raise_exception;
290 end if;
291
292 for tlinfo in c1 loop
293 if (tlinfo.BASELANG = 'Y') then
294 if ( (tlinfo.CHANNEL_NAME = X_CHANNEL_NAME)
295 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
296 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
297 ) then
298 null;
299 else
300 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
301 app_exception.raise_exception;
302 end if;
303 end if;
304 end loop;
305 return;
306 end LOCK_ROW;
307
308 procedure UPDATE_ROW (
309 X_CHANNEL_ID in NUMBER,
310 X_OBJECT_VERSION_NUMBER in NUMBER,
311 X_CHANNEL_TYPE_CODE in VARCHAR2,
312 X_ORDER_SEQUENCE in NUMBER,
313 X_MANAGED_BY_PERSON_ID in NUMBER,
314 X_OUTBOUND_FLAG in VARCHAR2,
315 X_INBOUND_FLAG in VARCHAR2,
316 X_ACTIVE_FROM_DATE in DATE,
317 X_ACTIVE_TO_DATE in DATE,
318 X_RATING in VARCHAR2,
319 X_PREFERRED_VENDOR_ID in NUMBER,
320 X_PARTY_ID in NUMBER,
321 X_ATTRIBUTE_CATEGORY in VARCHAR2,
322 X_ATTRIBUTE1 in VARCHAR2,
323 X_ATTRIBUTE2 in VARCHAR2,
324 X_ATTRIBUTE3 in VARCHAR2,
325 X_ATTRIBUTE4 in VARCHAR2,
326 X_ATTRIBUTE5 in VARCHAR2,
327 X_ATTRIBUTE6 in VARCHAR2,
328 X_ATTRIBUTE7 in VARCHAR2,
329 X_ATTRIBUTE8 in VARCHAR2,
330 X_ATTRIBUTE9 in VARCHAR2,
331 X_ATTRIBUTE10 in VARCHAR2,
332 X_ATTRIBUTE11 in VARCHAR2,
333 X_ATTRIBUTE12 in VARCHAR2,
334 X_ATTRIBUTE13 in VARCHAR2,
335 X_ATTRIBUTE14 in VARCHAR2,
336 X_ATTRIBUTE15 in VARCHAR2,
337 X_CHANNEL_NAME in VARCHAR2,
338 X_DESCRIPTION in VARCHAR2,
339 X_LAST_UPDATE_DATE in DATE,
340 X_LAST_UPDATED_BY in NUMBER,
341 X_LAST_UPDATE_LOGIN in NUMBER
342 ) is
343 begin
344 update AMS_CHANNELS_B set
345 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
346 CHANNEL_TYPE_CODE = X_CHANNEL_TYPE_CODE,
347 ORDER_SEQUENCE = X_ORDER_SEQUENCE,
348 MANAGED_BY_PERSON_ID = X_MANAGED_BY_PERSON_ID,
349 OUTBOUND_FLAG = X_OUTBOUND_FLAG,
350 INBOUND_FLAG = X_INBOUND_FLAG,
351 ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE,
352 ACTIVE_TO_DATE = X_ACTIVE_TO_DATE,
353 RATING = X_RATING,
354 PREFERRED_VENDOR_ID = X_PREFERRED_VENDOR_ID,
355 PARTY_ID = X_PARTY_ID ,
356 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
357 ATTRIBUTE1 = X_ATTRIBUTE1,
358 ATTRIBUTE2 = X_ATTRIBUTE2,
359 ATTRIBUTE3 = X_ATTRIBUTE3,
360 ATTRIBUTE4 = X_ATTRIBUTE4,
361 ATTRIBUTE5 = X_ATTRIBUTE5,
362 ATTRIBUTE6 = X_ATTRIBUTE6,
363 ATTRIBUTE7 = X_ATTRIBUTE7,
364 ATTRIBUTE8 = X_ATTRIBUTE8,
368 ATTRIBUTE12 = X_ATTRIBUTE12,
365 ATTRIBUTE9 = X_ATTRIBUTE9,
366 ATTRIBUTE10 = X_ATTRIBUTE10,
367 ATTRIBUTE11 = X_ATTRIBUTE11,
369 ATTRIBUTE13 = X_ATTRIBUTE13,
370 ATTRIBUTE14 = X_ATTRIBUTE14,
371 ATTRIBUTE15 = X_ATTRIBUTE15,
372 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
373 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
374 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
375 where CHANNEL_ID = X_CHANNEL_ID;
376
377 if (sql%notfound) then
378 raise no_data_found;
379 end if;
380
381 update AMS_CHANNELS_TL set
382 CHANNEL_NAME = X_CHANNEL_NAME,
383 DESCRIPTION = X_DESCRIPTION,
384 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
385 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
386 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
387 SOURCE_LANG = userenv('LANG')
388 where CHANNEL_ID = X_CHANNEL_ID
389 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
390
391 if (sql%notfound) then
392 raise no_data_found;
393 end if;
394 end UPDATE_ROW;
395
396 procedure DELETE_ROW (
397 X_CHANNEL_ID in NUMBER
398 ) is
399 begin
400 delete from AMS_CHANNELS_TL
401 where CHANNEL_ID = X_CHANNEL_ID;
402
403 if (sql%notfound) then
404 raise no_data_found;
405 end if;
406
407 delete from AMS_CHANNELS_B
408 where CHANNEL_ID = X_CHANNEL_ID;
409
410 if (sql%notfound) then
411 raise no_data_found;
412 end if;
413 end DELETE_ROW;
414
415 procedure ADD_LANGUAGE
416 is
417 begin
418 delete from AMS_CHANNELS_TL T
419 where not exists
420 (select NULL
421 from AMS_CHANNELS_B B
422 where B.CHANNEL_ID = T.CHANNEL_ID
423 );
424
425 update AMS_CHANNELS_TL T set (
426 CHANNEL_NAME,
427 DESCRIPTION
428 ) = (select
429 B.CHANNEL_NAME,
430 B.DESCRIPTION
431 from AMS_CHANNELS_TL B
432 where B.CHANNEL_ID = T.CHANNEL_ID
433 and B.LANGUAGE = T.SOURCE_LANG)
434 where (
435 T.CHANNEL_ID,
436 T.LANGUAGE
437 ) in (select
438 SUBT.CHANNEL_ID,
439 SUBT.LANGUAGE
440 from AMS_CHANNELS_TL SUBB, AMS_CHANNELS_TL SUBT
441 where SUBB.CHANNEL_ID = SUBT.CHANNEL_ID
442 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
443 and (SUBB.CHANNEL_NAME <> SUBT.CHANNEL_NAME
444 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
445 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
446 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
447 ));
448
449 insert into AMS_CHANNELS_TL (
450 CHANNEL_ID,
451 LAST_UPDATE_DATE,
452 LAST_UPDATED_BY,
453 CREATION_DATE,
454 CREATED_BY,
455 LAST_UPDATE_LOGIN,
456 CHANNEL_NAME,
457 DESCRIPTION,
458 LANGUAGE,
459 SOURCE_LANG
460 ) select
461 B.CHANNEL_ID,
462 B.LAST_UPDATE_DATE,
463 B.LAST_UPDATED_BY,
464 B.CREATION_DATE,
465 B.CREATED_BY,
466 B.LAST_UPDATE_LOGIN,
467 B.CHANNEL_NAME,
468 B.DESCRIPTION,
469 L.LANGUAGE_CODE,
470 B.SOURCE_LANG
471 from AMS_CHANNELS_TL B, FND_LANGUAGES L
472 where L.INSTALLED_FLAG in ('I', 'B')
473 and B.LANGUAGE = userenv('LANG')
474 and not exists
475 (select NULL
476 from AMS_CHANNELS_TL T
477 where T.CHANNEL_ID = B.CHANNEL_ID
478 and T.LANGUAGE = L.LANGUAGE_CODE);
479 end ADD_LANGUAGE;
480
481
482 procedure TRANSLATE_ROW(
483 x_channel_id in NUMBER
484 , x_channel_name in VARCHAR2
485 , x_description in VARCHAR2
486 , x_owner in VARCHAR2
487 ) is
488 begin
489 update AMS_CHANNELS_TL set
490 channel_name = nvl(x_channel_name, channel_name),
491 description = nvl(x_description, description),
492 source_lang = userenv('LANG'),
493 last_update_date = sysdate,
494 last_updated_by = decode(x_owner, 'SEED', 1, 0),
495 last_update_login = 0
496 where channel_id = x_channel_id
497 and userenv('LANG') in (language, source_lang);
498 end TRANSLATE_ROW;
499
500 procedure LOAD_ROW(
501 x_channel_ID IN NUMBER,
502 x_channel_TYPE_CODE in VARCHAR2 DEFAULT NULL,
503 X_INBOUND_FLAG in VARCHAR2 DEFAULT 'N',
504 X_OUTBOUND_FLAG in VARCHAR2 DEFAULT 'Y',
505 X_ORDER_SEQUENCE in NUMBER,
506 X_MANAGED_BY_PERSON_ID in NUMBER,
507 X_ACTIVE_FROM_DATE in DATE DEFAULT SYSDATE,
508 X_ACTIVE_TO_DATE in DATE DEFAULT NULL,
509 X_RATING in VARCHAR2,
510 X_PREFERRED_VENDOR_ID in NUMBER,
511 X_PARTY_ID in NUMBER,
512 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
513 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL ,
514 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
515 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
516 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
517 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
518 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
519 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
520 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
521 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
522 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
523 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
524 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
525 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
526 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
527 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
528 x_channel_NAME in VARCHAR2 DEFAULT NULL,
529 X_DESCRIPTION in VARCHAR2 DEFAULT NULL ,
530 X_Owner VARCHAR2
531 ) is
532
533 l_user_id number := 0;
534 l_obj_verno number;
535 l_dummy_char varchar2(1);
539 cursor c_obj_verno is
536 l_row_id varchar2(100);
537 l_chan_id number;
538
540 select object_version_number
541 from AMS_CHANNELS_B
542 where CHANNEL_id = x_channel_ID;
543
544 cursor c_chk_cha_exists is
545 select 'x'
546 from AMS_CHANNELS_B
547 where channel_id = x_channel_ID;
548
549 cursor c_get_chanid is
550 select AMS_CHANNELS_B_S.nextval
551 from dual;
552
553 BEGIN
554
555 if X_OWNER = 'SEED' then
556 l_user_id := 1;
557 end if;
558
559 open c_chk_cha_exists;
560 fetch c_chk_cha_exists into l_dummy_char;
561 if c_chk_cha_exists%notfound
562 then
563 if X_CHANNEL_ID is null
564 then
565 open c_get_chanid;
566 fetch c_get_chanid into l_chan_id;
567 close c_get_chanid;
568 else
569 l_chan_id := X_CHANNEL_ID;
570 end if;
571 l_obj_verno := 1;
572 AMS_CHANNElS_PKG.INSERT_ROW(
573 X_ROWID => l_row_id,
574 x_channel_ID => l_chan_id,
575 X_OBJECT_VERSION_NUMBER => l_obj_verno,
576 x_channel_TYPE_CODE => x_channel_TYPE_CODE,
577 X_INBOUND_FLAG => X_INBOUND_FLAG,
578 X_OUTBOUND_FLAG => X_OUTBOUND_FLAG,
579 X_ORDER_SEQUENCE => X_ORDER_SEQUENCE,
580 X_MANAGED_BY_PERSON_ID => X_MANAGED_BY_PERSON_ID,
581 X_ACTIVE_FROM_DATE => X_ACTIVE_FROM_DATE,
582 X_ACTIVE_TO_DATE => X_ACTIVE_TO_DATE,
583 X_RATING => X_RATING ,
584 X_PREFERRED_VENDOR_ID => X_PREFERRED_VENDOR_ID,
585 X_PARTY_ID => X_PARTY_ID ,
586 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
587 X_ATTRIBUTE1 => X_ATTRIBUTE1,
588 X_ATTRIBUTE2 => X_ATTRIBUTE2,
589 X_ATTRIBUTE3 => X_ATTRIBUTE3,
590 X_ATTRIBUTE4 => X_ATTRIBUTE4,
591 X_ATTRIBUTE5 => X_ATTRIBUTE5,
592 X_ATTRIBUTE6 => X_ATTRIBUTE6,
593 X_ATTRIBUTE7 => X_ATTRIBUTE7,
594 X_ATTRIBUTE8 => X_ATTRIBUTE8,
595 X_ATTRIBUTE9 => X_ATTRIBUTE9,
596 X_ATTRIBUTE10 => X_ATTRIBUTE10,
597 X_ATTRIBUTE11 => X_ATTRIBUTE11,
598 X_ATTRIBUTE12 => X_ATTRIBUTE12,
599 X_ATTRIBUTE13 => X_ATTRIBUTE13,
600 X_ATTRIBUTE14 => X_ATTRIBUTE14,
601 X_ATTRIBUTE15 => X_ATTRIBUTE15,
602 X_CHANNEL_NAME => X_CHANNEL_NAME,
603 X_DESCRIPTION => X_DESCRIPTION,
604 X_CREATION_DATE => SYSDATE,
605 X_CREATED_BY => l_user_id,
606 X_LAST_UPDATE_DATE => SYSDATE,
607 X_LAST_UPDATED_BY => l_user_id,
608 X_LAST_UPDATE_LOGIN => 0
609 );
610 else
611 close c_chk_cha_exists;
612 open c_obj_verno;
613 fetch c_obj_verno into l_obj_verno;
614 close c_obj_verno;
615 AMS_CHANNELS_PKG.UPDATE_ROW(
616 x_channel_ID => x_channel_ID,
617 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
618 x_channel_TYPE_CODE => x_channel_TYPE_CODE,
619 X_INBOUND_FLAG => X_INBOUND_FLAG,
620 X_OUTBOUND_FLAG => X_OUTBOUND_FLAG,
621 X_ORDER_SEQUENCE => X_ORDER_SEQUENCE,
622 X_MANAGED_BY_PERSON_ID => X_MANAGED_BY_PERSON_ID,
623 X_ACTIVE_FROM_DATE => X_ACTIVE_FROM_DATE,
624 X_ACTIVE_TO_DATE => X_ACTIVE_TO_DATE,
625 X_RATING => X_RATING ,
626 X_PREFERRED_VENDOR_ID => X_PREFERRED_VENDOR_ID,
627 X_PARTY_ID => X_PARTY_ID,
628 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
629 X_ATTRIBUTE1 => X_ATTRIBUTE1,
630 X_ATTRIBUTE2 => X_ATTRIBUTE2,
631 X_ATTRIBUTE3 => X_ATTRIBUTE3,
632 X_ATTRIBUTE4 => X_ATTRIBUTE4,
633 X_ATTRIBUTE5 => X_ATTRIBUTE5,
634 X_ATTRIBUTE6 => X_ATTRIBUTE6,
635 X_ATTRIBUTE7 => X_ATTRIBUTE7,
636 X_ATTRIBUTE8 => X_ATTRIBUTE8,
637 X_ATTRIBUTE9 => X_ATTRIBUTE9,
638 X_ATTRIBUTE10 => X_ATTRIBUTE10,
639 X_ATTRIBUTE11 => X_ATTRIBUTE11,
640 X_ATTRIBUTE12 => X_ATTRIBUTE12,
641 X_ATTRIBUTE13 => X_ATTRIBUTE13,
642 X_ATTRIBUTE14 => X_ATTRIBUTE14,
643 X_ATTRIBUTE15 => X_ATTRIBUTE15,
644 x_channel_NAME => x_channel_NAME,
645 X_DESCRIPTION => X_DESCRIPTION,
646 X_LAST_UPDATE_DATE => SYSDATE,
647 X_LAST_UPDATED_BY => l_user_id,
648 X_LAST_UPDATE_LOGIN => 0
649 );
650 end if;
651 END LOAD_ROW;
652 end AMS_CHANNELS_PKG;