[Home] [Help]
PACKAGE BODY: APPS.AMV_C_CHANNELS_PKG
Source
1 package body AMV_C_CHANNELS_PKG as
2 /* $Header: amvtchab.pls 120.1 2005/06/29 10:28:03 appldev ship $ */
3 procedure LOAD_ROW (
4 X_CHANNEL_ID in VARCHAR2,
5 X_OBJECT_VERSION_NUMBER in VARCHAR2,
6 X_CHANNEL_TYPE in VARCHAR2,
7 X_CHANNEL_CATEGORY_ID in VARCHAR2,
8 X_STATUS in VARCHAR2,
9 X_OWNER_USER_ID in VARCHAR2,
10 X_DEFAULT_APPROVER_USER_ID in VARCHAR2,
11 X_EFFECTIVE_START_DATE in VARCHAR2,
12 X_EXPIRATION_DATE in VARCHAR2,
13 X_ACCESS_LEVEL_TYPE in VARCHAR2,
14 X_PUB_NEED_APPROVAL_FLAG in VARCHAR2,
15 X_SUB_NEED_APPROVAL_FLAG in VARCHAR2,
16 X_MATCH_ON_ALL_CRITERIA_FLAG in VARCHAR2,
17 X_MATCH_ON_KEYWORD_FLAG in VARCHAR2,
18 X_MATCH_ON_AUTHOR_FLAG in VARCHAR2,
19 X_MATCH_ON_PERSPECTIVE_FLAG in VARCHAR2,
20 X_MATCH_ON_ITEM_TYPE_FLAG in VARCHAR2,
21 X_MATCH_ON_CONTENT_TYPE_FLAG in VARCHAR2,
22 X_MATCH_ON_TIME_FLAG in VARCHAR2,
23 X_APPLICATION_ID in VARCHAR2,
24 X_EXTERNAL_ACCESS_FLAG in VARCHAR2,
25 X_ITEM_MATCH_COUNT in VARCHAR2,
26 X_LAST_MATCH_TIME in VARCHAR2,
27 X_NOTIFICATION_INTERVAL_TYPE in VARCHAR2,
28 X_LAST_NOTIFICATION_TIME in VARCHAR2,
29 X_ATTRIBUTE_CATEGORY in VARCHAR2,
30 X_ATTRIBUTE1 in VARCHAR2,
31 X_ATTRIBUTE2 in VARCHAR2,
32 X_ATTRIBUTE3 in VARCHAR2,
33 X_ATTRIBUTE4 in VARCHAR2,
34 X_ATTRIBUTE5 in VARCHAR2,
35 X_ATTRIBUTE6 in VARCHAR2,
36 X_ATTRIBUTE7 in VARCHAR2,
37 X_ATTRIBUTE8 in VARCHAR2,
38 X_ATTRIBUTE9 in VARCHAR2,
39 X_ATTRIBUTE10 in VARCHAR2,
40 X_ATTRIBUTE11 in VARCHAR2,
41 X_ATTRIBUTE12 in VARCHAR2,
42 X_ATTRIBUTE13 in VARCHAR2,
43 X_ATTRIBUTE14 in VARCHAR2,
44 X_ATTRIBUTE15 in VARCHAR2,
45 X_CHANNEL_NAME in VARCHAR2,
46 X_DESCRIPTION in VARCHAR2,
47 X_OWNER in VARCHAR2)
48 is
49 l_user_id number := 0;
50 l_channel_id number := 0;
51 l_object_version_number number := 0;
52 l_channel_category_id number := 0;
53 l_owner_user_id number := 0;
54 l_default_approver_user_id number := 0;
55 l_application_id number := 0;
56 l_item_match_count number := 0;
57 l_effective_start_date date;
58 l_expiration_date date;
59 l_last_match_time date;
60 l_last_notification_time date;
61 l_row_id varchar2(64);
62 begin
63 if (X_OWNER = 'SEED') then
64 l_user_id := 1;
65 end if;
66 l_channel_id := to_number(x_channel_id);
67 l_object_version_number := to_number(x_object_version_number);
68 l_channel_category_id := to_number(x_channel_category_id);
69 l_owner_user_id := to_number(x_owner_user_id);
70 l_default_approver_user_id := to_number(x_default_approver_user_id);
71 l_application_id := to_number(x_application_id);
72 l_item_match_count := to_number(x_item_match_count);
73 l_effective_start_date := to_date(x_effective_start_date, 'DD/MM/YYYY');
74 l_expiration_date := to_date(x_expiration_date, 'DD/MM/YYYY');
75 l_last_match_time := to_date(x_last_match_time, 'DD/MM/YYYY');
76 l_last_notification_time :=to_date(x_last_notification_time,'DD/MM/YYYY');
77 --
78 AMV_C_CHANNELS_PKG.UPDATE_ROW (
79 X_CHANNEL_ID => l_channel_id,
80 X_OBJECT_VERSION_NUMBER => l_object_version_number,
81 X_CHANNEL_TYPE => x_channel_type,
82 X_CHANNEL_CATEGORY_ID => l_channel_category_id,
83 X_STATUS => x_status,
84 X_OWNER_USER_ID => l_owner_user_id,
85 X_DEFAULT_APPROVER_USER_ID => l_default_approver_user_id,
86 X_EFFECTIVE_START_DATE => l_effective_start_date,
87 X_EXPIRATION_DATE => l_expiration_date,
88 X_ACCESS_LEVEL_TYPE => x_access_level_type,
89 X_PUB_NEED_APPROVAL_FLAG => x_pub_need_approval_flag,
90 X_SUB_NEED_APPROVAL_FLAG => x_sub_need_approval_flag,
91 X_MATCH_ON_ALL_CRITERIA_FLAG => x_match_on_all_criteria_flag,
92 X_MATCH_ON_KEYWORD_FLAG => x_match_on_keyword_flag,
93 X_MATCH_ON_AUTHOR_FLAG => x_match_on_author_flag,
94 X_MATCH_ON_PERSPECTIVE_FLAG => x_match_on_perspective_flag,
95 X_MATCH_ON_ITEM_TYPE_FLAG => x_match_on_item_type_flag,
96 X_MATCH_ON_CONTENT_TYPE_FLAG => x_match_on_content_type_flag,
97 X_MATCH_ON_TIME_FLAG => x_match_on_time_flag,
98 X_APPLICATION_ID => l_application_id,
99 X_EXTERNAL_ACCESS_FLAG => x_external_access_flag,
100 X_ITEM_MATCH_COUNT => l_item_match_count,
101 X_LAST_MATCH_TIME => l_last_match_time,
102 X_NOTIFICATION_INTERVAL_TYPE => x_notification_interval_type,
103 X_LAST_NOTIFICATION_TIME => l_last_notification_time,
104 X_ATTRIBUTE_CATEGORY => x_attribute_category,
105 X_ATTRIBUTE1 => x_attribute1,
106 X_ATTRIBUTE2 => x_attribute2,
107 X_ATTRIBUTE3 => x_attribute3,
108 X_ATTRIBUTE4 => x_attribute4,
109 X_ATTRIBUTE5 => x_attribute5,
110 X_ATTRIBUTE6 => x_attribute6,
111 X_ATTRIBUTE7 => x_attribute7,
112 X_ATTRIBUTE8 => x_attribute8,
113 X_ATTRIBUTE9 => x_attribute9,
114 X_ATTRIBUTE10 => x_attribute10,
115 X_ATTRIBUTE11 => x_attribute11,
116 X_ATTRIBUTE12 => x_attribute12,
117 X_ATTRIBUTE13 => x_attribute13,
118 X_ATTRIBUTE14 => x_attribute14,
119 X_ATTRIBUTE15 => x_attribute15,
120 X_CHANNEL_NAME => x_channel_name,
121 X_DESCRIPTION => x_description,
122 X_LAST_UPDATE_DATE => sysdate,
123 X_LAST_UPDATED_BY => l_user_id,
124 X_LAST_UPDATE_LOGIN => 0
125 );
126 exception
127 when NO_DATA_FOUND then
128 AMV_C_CHANNELS_PKG.INSERT_ROW (
129 X_ROWID => l_row_id,
130 X_CHANNEL_ID => l_channel_id,
131 X_OBJECT_VERSION_NUMBER => l_object_version_number,
132 X_CHANNEL_TYPE => x_channel_type,
133 X_CHANNEL_CATEGORY_ID => l_channel_category_id,
134 X_STATUS => x_status,
135 X_OWNER_USER_ID => l_owner_user_id,
136 X_DEFAULT_APPROVER_USER_ID => l_default_approver_user_id,
137 X_EFFECTIVE_START_DATE => l_effective_start_date,
138 X_EXPIRATION_DATE => l_expiration_date,
139 X_ACCESS_LEVEL_TYPE => x_access_level_type,
140 X_PUB_NEED_APPROVAL_FLAG => x_pub_need_approval_flag,
141 X_SUB_NEED_APPROVAL_FLAG => x_sub_need_approval_flag,
142 X_MATCH_ON_ALL_CRITERIA_FLAG => x_match_on_all_criteria_flag,
143 X_MATCH_ON_KEYWORD_FLAG => x_match_on_keyword_flag,
144 X_MATCH_ON_AUTHOR_FLAG => x_match_on_author_flag,
145 X_MATCH_ON_PERSPECTIVE_FLAG => x_match_on_perspective_flag,
146 X_MATCH_ON_ITEM_TYPE_FLAG => x_match_on_item_type_flag,
147 X_MATCH_ON_CONTENT_TYPE_FLAG => x_match_on_content_type_flag,
148 X_MATCH_ON_TIME_FLAG => x_match_on_time_flag,
149 X_APPLICATION_ID => l_application_id,
150 X_EXTERNAL_ACCESS_FLAG => x_external_access_flag,
151 X_ITEM_MATCH_COUNT => l_item_match_count,
152 X_LAST_MATCH_TIME => l_last_match_time,
153 X_NOTIFICATION_INTERVAL_TYPE => x_notification_interval_type,
154 X_LAST_NOTIFICATION_TIME => x_last_notification_time,
155 X_ATTRIBUTE_CATEGORY => x_attribute_category,
156 X_ATTRIBUTE1 => x_attribute1,
157 X_ATTRIBUTE2 => x_attribute2,
158 X_ATTRIBUTE3 => x_attribute3,
159 X_ATTRIBUTE4 => x_attribute4,
160 X_ATTRIBUTE5 => x_attribute5,
161 X_ATTRIBUTE6 => x_attribute6,
162 X_ATTRIBUTE7 => x_attribute7,
163 X_ATTRIBUTE8 => x_attribute8,
164 X_ATTRIBUTE9 => x_attribute9,
165 X_ATTRIBUTE10 => x_attribute10,
166 X_ATTRIBUTE11 => x_attribute11,
167 X_ATTRIBUTE12 => x_attribute12,
168 X_ATTRIBUTE13 => x_attribute13,
169 X_ATTRIBUTE14 => x_attribute14,
170 X_ATTRIBUTE15 => x_attribute15,
171 X_CHANNEL_NAME => x_channel_name,
172 X_DESCRIPTION => x_description,
173 X_CREATION_DATE => sysdate,
174 X_CREATED_BY => l_user_id,
175 X_LAST_UPDATE_DATE => sysdate,
176 X_LAST_UPDATED_BY => l_user_id,
177 X_LAST_UPDATE_LOGIN => 0
178 );
179 end LOAD_ROW;
180
181 procedure TRANSLATE_ROW (
182 X_CHANNEL_ID in NUMBER,
183 X_CHANNEL_NAME in VARCHAR2,
184 X_DESCRIPTION in VARCHAR2,
185 X_OWNER in VARCHAR2)
186 is
187 begin
188 update AMV_C_CHANNELS_TL set
189 CHANNEL_NAME = x_channel_name,
190 DESCRIPTION = x_description,
191 LAST_UPDATE_DATE = sysdate,
192 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
193 LAST_UPDATE_LOGIN = 0,
194 SOURCE_LANG = userenv('LANG')
195 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
196 and CHANNEL_ID = x_channel_id;
197 end TRANSLATE_ROW;
198
199 procedure INSERT_ROW (
200 X_ROWID in out NOCOPY VARCHAR2,
201 X_CHANNEL_ID in NUMBER,
202 X_OBJECT_VERSION_NUMBER in NUMBER,
203 X_CHANNEL_TYPE in VARCHAR2,
204 X_CHANNEL_CATEGORY_ID in NUMBER,
205 X_STATUS in VARCHAR2,
206 X_OWNER_USER_ID in NUMBER,
207 X_DEFAULT_APPROVER_USER_ID in NUMBER,
208 X_EFFECTIVE_START_DATE in DATE,
209 X_EXPIRATION_DATE in DATE,
210 X_ACCESS_LEVEL_TYPE in VARCHAR2,
211 X_PUB_NEED_APPROVAL_FLAG in VARCHAR2,
212 X_SUB_NEED_APPROVAL_FLAG in VARCHAR2,
213 X_MATCH_ON_ALL_CRITERIA_FLAG in VARCHAR2,
214 X_MATCH_ON_KEYWORD_FLAG in VARCHAR2,
215 X_MATCH_ON_AUTHOR_FLAG in VARCHAR2,
216 X_MATCH_ON_PERSPECTIVE_FLAG in VARCHAR2,
217 X_MATCH_ON_ITEM_TYPE_FLAG in VARCHAR2,
218 X_MATCH_ON_CONTENT_TYPE_FLAG in VARCHAR2,
219 X_MATCH_ON_TIME_FLAG in VARCHAR2,
220 X_APPLICATION_ID in NUMBER,
221 X_EXTERNAL_ACCESS_FLAG in VARCHAR2,
222 X_ITEM_MATCH_COUNT in NUMBER,
223 X_LAST_MATCH_TIME in DATE,
224 X_NOTIFICATION_INTERVAL_TYPE in VARCHAR2,
225 X_LAST_NOTIFICATION_TIME in DATE,
226 X_ATTRIBUTE_CATEGORY in VARCHAR2,
227 X_ATTRIBUTE1 in VARCHAR2,
228 X_ATTRIBUTE2 in VARCHAR2,
229 X_ATTRIBUTE3 in VARCHAR2,
230 X_ATTRIBUTE4 in VARCHAR2,
231 X_ATTRIBUTE5 in VARCHAR2,
232 X_ATTRIBUTE6 in VARCHAR2,
233 X_ATTRIBUTE7 in VARCHAR2,
234 X_ATTRIBUTE8 in VARCHAR2,
235 X_ATTRIBUTE9 in VARCHAR2,
236 X_ATTRIBUTE10 in VARCHAR2,
237 X_ATTRIBUTE11 in VARCHAR2,
238 X_ATTRIBUTE12 in VARCHAR2,
239 X_ATTRIBUTE13 in VARCHAR2,
240 X_ATTRIBUTE14 in VARCHAR2,
241 X_ATTRIBUTE15 in VARCHAR2,
242 X_CHANNEL_NAME in VARCHAR2,
243 X_DESCRIPTION in VARCHAR2,
244 X_CREATION_DATE in DATE,
245 X_CREATED_BY in NUMBER,
246 X_LAST_UPDATE_DATE in DATE,
247 X_LAST_UPDATED_BY in NUMBER,
248 X_LAST_UPDATE_LOGIN in NUMBER
249 ) is
250 cursor C is select ROWID from AMV_C_CHANNELS_B
251 where CHANNEL_ID = X_CHANNEL_ID
252 ;
253 begin
254 insert into AMV_C_CHANNELS_B (
255 CHANNEL_ID,
256 OBJECT_VERSION_NUMBER,
257 CHANNEL_TYPE,
258 CHANNEL_CATEGORY_ID,
259 STATUS,
260 OWNER_USER_ID,
261 DEFAULT_APPROVER_USER_ID,
262 EFFECTIVE_START_DATE,
263 EXPIRATION_DATE,
264 ACCESS_LEVEL_TYPE,
265 PUB_NEED_APPROVAL_FLAG,
266 SUB_NEED_APPROVAL_FLAG,
267 MATCH_ON_ALL_CRITERIA_FLAG,
268 MATCH_ON_KEYWORD_FLAG,
269 MATCH_ON_AUTHOR_FLAG,
270 MATCH_ON_PERSPECTIVE_FLAG,
271 MATCH_ON_ITEM_TYPE_FLAG,
272 MATCH_ON_CONTENT_TYPE_FLAG,
273 MATCH_ON_TIME_FLAG,
274 APPLICATION_ID,
275 EXTERNAL_ACCESS_FLAG,
276 ITEM_MATCH_COUNT,
277 LAST_MATCH_TIME,
278 NOTIFICATION_INTERVAL_TYPE,
279 LAST_NOTIFICATION_TIME,
280 ATTRIBUTE_CATEGORY,
281 ATTRIBUTE1,
282 ATTRIBUTE2,
283 ATTRIBUTE3,
284 ATTRIBUTE4,
285 ATTRIBUTE5,
286 ATTRIBUTE6,
287 ATTRIBUTE7,
288 ATTRIBUTE8,
289 ATTRIBUTE9,
290 ATTRIBUTE10,
291 ATTRIBUTE11,
292 ATTRIBUTE12,
293 ATTRIBUTE13,
294 ATTRIBUTE14,
295 ATTRIBUTE15,
296 CREATION_DATE,
297 CREATED_BY,
298 LAST_UPDATE_DATE,
299 LAST_UPDATED_BY,
300 LAST_UPDATE_LOGIN
301 ) values (
302 X_CHANNEL_ID,
303 X_OBJECT_VERSION_NUMBER,
304 X_CHANNEL_TYPE,
305 X_CHANNEL_CATEGORY_ID,
306 X_STATUS,
307 X_OWNER_USER_ID,
308 X_DEFAULT_APPROVER_USER_ID,
309 X_EFFECTIVE_START_DATE,
310 X_EXPIRATION_DATE,
311 X_ACCESS_LEVEL_TYPE,
312 X_PUB_NEED_APPROVAL_FLAG,
313 X_SUB_NEED_APPROVAL_FLAG,
314 X_MATCH_ON_ALL_CRITERIA_FLAG,
315 X_MATCH_ON_KEYWORD_FLAG,
316 X_MATCH_ON_AUTHOR_FLAG,
317 X_MATCH_ON_PERSPECTIVE_FLAG,
318 X_MATCH_ON_ITEM_TYPE_FLAG,
319 X_MATCH_ON_CONTENT_TYPE_FLAG,
320 X_MATCH_ON_TIME_FLAG,
321 X_APPLICATION_ID,
322 X_EXTERNAL_ACCESS_FLAG,
323 X_ITEM_MATCH_COUNT,
324 X_LAST_MATCH_TIME,
325 X_NOTIFICATION_INTERVAL_TYPE,
326 X_LAST_NOTIFICATION_TIME,
327 X_ATTRIBUTE_CATEGORY,
328 X_ATTRIBUTE1,
329 X_ATTRIBUTE2,
330 X_ATTRIBUTE3,
331 X_ATTRIBUTE4,
332 X_ATTRIBUTE5,
333 X_ATTRIBUTE6,
334 X_ATTRIBUTE7,
335 X_ATTRIBUTE8,
336 X_ATTRIBUTE9,
337 X_ATTRIBUTE10,
338 X_ATTRIBUTE11,
339 X_ATTRIBUTE12,
340 X_ATTRIBUTE13,
341 X_ATTRIBUTE14,
342 X_ATTRIBUTE15,
343 X_CREATION_DATE,
344 X_CREATED_BY,
345 X_LAST_UPDATE_DATE,
346 X_LAST_UPDATED_BY,
347 X_LAST_UPDATE_LOGIN
348 );
349
350 insert into AMV_C_CHANNELS_TL (
351 CHANNEL_ID,
352 LAST_UPDATE_DATE,
353 LAST_UPDATED_BY,
354 CREATION_DATE,
355 CREATED_BY,
356 LAST_UPDATE_LOGIN,
357 CHANNEL_NAME,
358 DESCRIPTION,
359 LANGUAGE,
360 SOURCE_LANG
361 ) select
362 X_CHANNEL_ID,
363 X_LAST_UPDATE_DATE,
364 X_LAST_UPDATED_BY,
365 X_CREATION_DATE,
366 X_CREATED_BY,
367 X_LAST_UPDATE_LOGIN,
368 X_CHANNEL_NAME,
369 X_DESCRIPTION,
370 L.LANGUAGE_CODE,
371 userenv('LANG')
372 from FND_LANGUAGES L
373 where L.INSTALLED_FLAG in ('I', 'B')
374 and not exists
375 (select NULL
376 from AMV_C_CHANNELS_TL T
377 where T.CHANNEL_ID = X_CHANNEL_ID
378 and T.LANGUAGE = L.LANGUAGE_CODE);
379
380 open c;
381 fetch c into X_ROWID;
385 end if;
382 if (c%notfound) then
383 close c;
384 raise no_data_found;
386 close c;
387
388 end INSERT_ROW;
389
390 procedure LOCK_ROW (
391 X_CHANNEL_ID in NUMBER,
392 X_OBJECT_VERSION_NUMBER in NUMBER,
393 X_CHANNEL_TYPE in VARCHAR2,
394 X_CHANNEL_CATEGORY_ID in NUMBER,
395 X_STATUS in VARCHAR2,
396 X_OWNER_USER_ID in NUMBER,
397 X_DEFAULT_APPROVER_USER_ID in NUMBER,
398 X_EFFECTIVE_START_DATE in DATE,
399 X_EXPIRATION_DATE in DATE,
400 X_ACCESS_LEVEL_TYPE in VARCHAR2,
401 X_PUB_NEED_APPROVAL_FLAG in VARCHAR2,
402 X_SUB_NEED_APPROVAL_FLAG in VARCHAR2,
403 X_MATCH_ON_ALL_CRITERIA_FLAG in VARCHAR2,
404 X_MATCH_ON_KEYWORD_FLAG in VARCHAR2,
405 X_MATCH_ON_AUTHOR_FLAG in VARCHAR2,
406 X_MATCH_ON_PERSPECTIVE_FLAG in VARCHAR2,
407 X_MATCH_ON_ITEM_TYPE_FLAG in VARCHAR2,
408 X_MATCH_ON_CONTENT_TYPE_FLAG in VARCHAR2,
409 X_MATCH_ON_TIME_FLAG in VARCHAR2,
410 X_APPLICATION_ID in NUMBER,
411 X_EXTERNAL_ACCESS_FLAG in VARCHAR2,
412 X_ITEM_MATCH_COUNT in NUMBER,
413 X_LAST_MATCH_TIME in DATE,
414 X_NOTIFICATION_INTERVAL_TYPE in VARCHAR2,
415 X_LAST_NOTIFICATION_TIME in DATE,
416 X_ATTRIBUTE_CATEGORY in VARCHAR2,
417 X_ATTRIBUTE1 in VARCHAR2,
418 X_ATTRIBUTE2 in VARCHAR2,
419 X_ATTRIBUTE3 in VARCHAR2,
420 X_ATTRIBUTE4 in VARCHAR2,
421 X_ATTRIBUTE5 in VARCHAR2,
422 X_ATTRIBUTE6 in VARCHAR2,
423 X_ATTRIBUTE7 in VARCHAR2,
424 X_ATTRIBUTE8 in VARCHAR2,
425 X_ATTRIBUTE9 in VARCHAR2,
426 X_ATTRIBUTE10 in VARCHAR2,
427 X_ATTRIBUTE11 in VARCHAR2,
428 X_ATTRIBUTE12 in VARCHAR2,
429 X_ATTRIBUTE13 in VARCHAR2,
430 X_ATTRIBUTE14 in VARCHAR2,
431 X_ATTRIBUTE15 in VARCHAR2,
432 X_CHANNEL_NAME in VARCHAR2,
433 X_DESCRIPTION in VARCHAR2
434 ) is
435 cursor c is select
436 OBJECT_VERSION_NUMBER,
437 CHANNEL_TYPE,
438 CHANNEL_CATEGORY_ID,
439 STATUS,
440 OWNER_USER_ID,
441 DEFAULT_APPROVER_USER_ID,
442 EFFECTIVE_START_DATE,
443 EXPIRATION_DATE,
444 ACCESS_LEVEL_TYPE,
445 PUB_NEED_APPROVAL_FLAG,
446 SUB_NEED_APPROVAL_FLAG,
447 MATCH_ON_ALL_CRITERIA_FLAG,
448 MATCH_ON_KEYWORD_FLAG,
449 MATCH_ON_AUTHOR_FLAG,
450 MATCH_ON_PERSPECTIVE_FLAG,
451 MATCH_ON_ITEM_TYPE_FLAG,
452 MATCH_ON_CONTENT_TYPE_FLAG,
453 MATCH_ON_TIME_FLAG,
454 APPLICATION_ID,
455 EXTERNAL_ACCESS_FLAG,
456 ITEM_MATCH_COUNT,
457 LAST_MATCH_TIME,
458 NOTIFICATION_INTERVAL_TYPE,
459 LAST_NOTIFICATION_TIME,
460 ATTRIBUTE_CATEGORY,
461 ATTRIBUTE1,
462 ATTRIBUTE2,
463 ATTRIBUTE3,
464 ATTRIBUTE4,
465 ATTRIBUTE5,
466 ATTRIBUTE6,
467 ATTRIBUTE7,
468 ATTRIBUTE8,
469 ATTRIBUTE9,
470 ATTRIBUTE10,
471 ATTRIBUTE11,
472 ATTRIBUTE12,
473 ATTRIBUTE13,
474 ATTRIBUTE14,
475 ATTRIBUTE15
476 from AMV_C_CHANNELS_B
477 where CHANNEL_ID = X_CHANNEL_ID
478 for update of CHANNEL_ID nowait;
479 recinfo c%rowtype;
480
481 cursor c1 is select
482 CHANNEL_NAME,
483 DESCRIPTION,
484 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
485 from AMV_C_CHANNELS_TL
486 where CHANNEL_ID = X_CHANNEL_ID
487 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
488 for update of CHANNEL_ID nowait;
489 begin
490 open c;
491 fetch c into recinfo;
492 if (c%notfound) then
493 close c;
494 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
495 app_exception.raise_exception;
496 end if;
497 close c;
498 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
499 AND (recinfo.CHANNEL_TYPE = X_CHANNEL_TYPE)
500 AND (recinfo.CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID)
501 AND (recinfo.STATUS = X_STATUS)
502 AND (recinfo.OWNER_USER_ID = X_OWNER_USER_ID)
503 AND (recinfo.DEFAULT_APPROVER_USER_ID = X_DEFAULT_APPROVER_USER_ID)
504 AND (recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
505 AND ((recinfo.EXPIRATION_DATE = X_EXPIRATION_DATE)
506 OR ((recinfo.EXPIRATION_DATE is null) AND (X_EXPIRATION_DATE is null)))
507 AND (recinfo.ACCESS_LEVEL_TYPE = X_ACCESS_LEVEL_TYPE)
508 AND (recinfo.PUB_NEED_APPROVAL_FLAG = X_PUB_NEED_APPROVAL_FLAG)
509 AND (recinfo.SUB_NEED_APPROVAL_FLAG = X_SUB_NEED_APPROVAL_FLAG)
510 AND (recinfo.MATCH_ON_ALL_CRITERIA_FLAG = X_MATCH_ON_ALL_CRITERIA_FLAG)
511 AND (recinfo.MATCH_ON_KEYWORD_FLAG = X_MATCH_ON_KEYWORD_FLAG)
512 AND (recinfo.MATCH_ON_AUTHOR_FLAG = X_MATCH_ON_AUTHOR_FLAG)
513 AND (recinfo.MATCH_ON_PERSPECTIVE_FLAG = X_MATCH_ON_PERSPECTIVE_FLAG)
514 AND (recinfo.MATCH_ON_ITEM_TYPE_FLAG = X_MATCH_ON_ITEM_TYPE_FLAG)
515 AND (recinfo.MATCH_ON_CONTENT_TYPE_FLAG = X_MATCH_ON_CONTENT_TYPE_FLAG)
516 AND (recinfo.MATCH_ON_TIME_FLAG = X_MATCH_ON_TIME_FLAG)
517 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
518 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
519 AND ((recinfo.EXTERNAL_ACCESS_FLAG = X_EXTERNAL_ACCESS_FLAG)
520 OR ((recinfo.EXTERNAL_ACCESS_FLAG is null) AND (X_EXTERNAL_ACCESS_FLAG is null)))
521 AND ((recinfo.ITEM_MATCH_COUNT = X_ITEM_MATCH_COUNT)
522 OR ((recinfo.ITEM_MATCH_COUNT is null) AND (X_ITEM_MATCH_COUNT is null)))
523 AND ((recinfo.LAST_MATCH_TIME = X_LAST_MATCH_TIME)
527 AND ((recinfo.LAST_NOTIFICATION_TIME = X_LAST_NOTIFICATION_TIME)
524 OR ((recinfo.LAST_MATCH_TIME is null) AND (X_LAST_MATCH_TIME is null)))
525 AND ((recinfo.NOTIFICATION_INTERVAL_TYPE = X_NOTIFICATION_INTERVAL_TYPE)
526 OR ((recinfo.NOTIFICATION_INTERVAL_TYPE is null) AND (X_NOTIFICATION_INTERVAL_TYPE is null)))
528 OR ((recinfo.LAST_NOTIFICATION_TIME is null) AND (X_LAST_NOTIFICATION_TIME is null)))
529 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
530 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
531 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
532 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
533 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
534 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
535 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
536 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
537 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
538 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
539 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
540 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
541 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
542 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
543 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
544 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
545 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
546 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
547 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
548 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
549 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
550 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
551 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
552 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
553 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
554 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
555 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
556 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
557 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
558 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
559 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
560 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
561 ) then
562 null;
563 else
564 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
565 app_exception.raise_exception;
566 end if;
567
568 for tlinfo in c1 loop
569 if (tlinfo.BASELANG = 'Y') then
570 if ( (tlinfo.CHANNEL_NAME = X_CHANNEL_NAME)
571 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
572 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
573 ) then
574 null;
575 else
576 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
577 app_exception.raise_exception;
578 end if;
579 end if;
580 end loop;
581 return;
582 end LOCK_ROW;
583
584 procedure UPDATE_ROW (
585 X_CHANNEL_ID in NUMBER,
586 X_OBJECT_VERSION_NUMBER in NUMBER,
587 X_CHANNEL_TYPE in VARCHAR2,
588 X_CHANNEL_CATEGORY_ID in NUMBER,
589 X_STATUS in VARCHAR2,
590 X_OWNER_USER_ID in NUMBER,
591 X_DEFAULT_APPROVER_USER_ID in NUMBER,
592 X_EFFECTIVE_START_DATE in DATE,
593 X_EXPIRATION_DATE in DATE,
594 X_ACCESS_LEVEL_TYPE in VARCHAR2,
595 X_PUB_NEED_APPROVAL_FLAG in VARCHAR2,
596 X_SUB_NEED_APPROVAL_FLAG in VARCHAR2,
597 X_MATCH_ON_ALL_CRITERIA_FLAG in VARCHAR2,
598 X_MATCH_ON_KEYWORD_FLAG in VARCHAR2,
599 X_MATCH_ON_AUTHOR_FLAG in VARCHAR2,
600 X_MATCH_ON_PERSPECTIVE_FLAG in VARCHAR2,
601 X_MATCH_ON_ITEM_TYPE_FLAG in VARCHAR2,
602 X_MATCH_ON_CONTENT_TYPE_FLAG in VARCHAR2,
603 X_MATCH_ON_TIME_FLAG in VARCHAR2,
604 X_APPLICATION_ID in NUMBER,
605 X_EXTERNAL_ACCESS_FLAG in VARCHAR2,
606 X_ITEM_MATCH_COUNT in NUMBER,
607 X_LAST_MATCH_TIME in DATE,
608 X_NOTIFICATION_INTERVAL_TYPE in VARCHAR2,
609 X_LAST_NOTIFICATION_TIME in DATE,
610 X_ATTRIBUTE_CATEGORY in VARCHAR2,
611 X_ATTRIBUTE1 in VARCHAR2,
612 X_ATTRIBUTE2 in VARCHAR2,
613 X_ATTRIBUTE3 in VARCHAR2,
614 X_ATTRIBUTE4 in VARCHAR2,
615 X_ATTRIBUTE5 in VARCHAR2,
616 X_ATTRIBUTE6 in VARCHAR2,
617 X_ATTRIBUTE7 in VARCHAR2,
618 X_ATTRIBUTE8 in VARCHAR2,
619 X_ATTRIBUTE9 in VARCHAR2,
620 X_ATTRIBUTE10 in VARCHAR2,
621 X_ATTRIBUTE11 in VARCHAR2,
622 X_ATTRIBUTE12 in VARCHAR2,
623 X_ATTRIBUTE13 in VARCHAR2,
624 X_ATTRIBUTE14 in VARCHAR2,
625 X_ATTRIBUTE15 in VARCHAR2,
626 X_CHANNEL_NAME in VARCHAR2,
627 X_DESCRIPTION in VARCHAR2,
628 X_LAST_UPDATE_DATE in DATE,
629 X_LAST_UPDATED_BY in NUMBER,
630 X_LAST_UPDATE_LOGIN in NUMBER
631 ) is
632 begin
633 update AMV_C_CHANNELS_B set
634 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
635 CHANNEL_TYPE = X_CHANNEL_TYPE,
636 CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID,
637 STATUS = X_STATUS,
638 OWNER_USER_ID = X_OWNER_USER_ID,
639 DEFAULT_APPROVER_USER_ID = X_DEFAULT_APPROVER_USER_ID,
640 EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE,
641 EXPIRATION_DATE = X_EXPIRATION_DATE,
642 ACCESS_LEVEL_TYPE = X_ACCESS_LEVEL_TYPE,
643 PUB_NEED_APPROVAL_FLAG = X_PUB_NEED_APPROVAL_FLAG,
644 SUB_NEED_APPROVAL_FLAG = X_SUB_NEED_APPROVAL_FLAG,
645 MATCH_ON_ALL_CRITERIA_FLAG = X_MATCH_ON_ALL_CRITERIA_FLAG,
646 MATCH_ON_KEYWORD_FLAG = X_MATCH_ON_KEYWORD_FLAG,
647 MATCH_ON_AUTHOR_FLAG = X_MATCH_ON_AUTHOR_FLAG,
648 MATCH_ON_PERSPECTIVE_FLAG = X_MATCH_ON_PERSPECTIVE_FLAG,
652 APPLICATION_ID = X_APPLICATION_ID,
649 MATCH_ON_ITEM_TYPE_FLAG = X_MATCH_ON_ITEM_TYPE_FLAG,
650 MATCH_ON_CONTENT_TYPE_FLAG = X_MATCH_ON_CONTENT_TYPE_FLAG,
651 MATCH_ON_TIME_FLAG = X_MATCH_ON_TIME_FLAG,
653 EXTERNAL_ACCESS_FLAG = X_EXTERNAL_ACCESS_FLAG,
654 ITEM_MATCH_COUNT = X_ITEM_MATCH_COUNT,
655 LAST_MATCH_TIME = X_LAST_MATCH_TIME,
656 NOTIFICATION_INTERVAL_TYPE = X_NOTIFICATION_INTERVAL_TYPE,
657 LAST_NOTIFICATION_TIME = X_LAST_NOTIFICATION_TIME,
658 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
659 ATTRIBUTE1 = X_ATTRIBUTE1,
660 ATTRIBUTE2 = X_ATTRIBUTE2,
661 ATTRIBUTE3 = X_ATTRIBUTE3,
662 ATTRIBUTE4 = X_ATTRIBUTE4,
663 ATTRIBUTE5 = X_ATTRIBUTE5,
664 ATTRIBUTE6 = X_ATTRIBUTE6,
665 ATTRIBUTE7 = X_ATTRIBUTE7,
666 ATTRIBUTE8 = X_ATTRIBUTE8,
667 ATTRIBUTE9 = X_ATTRIBUTE9,
668 ATTRIBUTE10 = X_ATTRIBUTE10,
669 ATTRIBUTE11 = X_ATTRIBUTE11,
670 ATTRIBUTE12 = X_ATTRIBUTE12,
671 ATTRIBUTE13 = X_ATTRIBUTE13,
672 ATTRIBUTE14 = X_ATTRIBUTE14,
673 ATTRIBUTE15 = X_ATTRIBUTE15,
674 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
675 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
676 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
677 where CHANNEL_ID = X_CHANNEL_ID;
678
679 if (sql%notfound) then
680 raise no_data_found;
681 end if;
682
683 update AMV_C_CHANNELS_TL set
684 CHANNEL_NAME = X_CHANNEL_NAME,
685 DESCRIPTION = X_DESCRIPTION,
686 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
687 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
688 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
689 SOURCE_LANG = userenv('LANG')
690 where CHANNEL_ID = X_CHANNEL_ID
691 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
692
693 if (sql%notfound) then
694 raise no_data_found;
695 end if;
696 end UPDATE_ROW;
697
698 procedure DELETE_ROW (
699 X_CHANNEL_ID in NUMBER
700 ) is
701 begin
702 delete from AMV_C_CHANNELS_TL
703 where CHANNEL_ID = X_CHANNEL_ID;
704
705 if (sql%notfound) then
706 raise no_data_found;
707 end if;
708
709 delete from AMV_C_CHANNELS_B
710 where CHANNEL_ID = X_CHANNEL_ID;
711
712 if (sql%notfound) then
713 raise no_data_found;
714 end if;
715 end DELETE_ROW;
716
717 procedure ADD_LANGUAGE
718 is
719 begin
720 delete from AMV_C_CHANNELS_TL T
721 where not exists
722 (select NULL
723 from AMV_C_CHANNELS_B B
724 where B.CHANNEL_ID = T.CHANNEL_ID
725 );
726
727 update AMV_C_CHANNELS_TL T set (
728 CHANNEL_NAME,
729 DESCRIPTION
730 ) = (select
731 B.CHANNEL_NAME,
732 B.DESCRIPTION
733 from AMV_C_CHANNELS_TL B
734 where B.CHANNEL_ID = T.CHANNEL_ID
735 and B.LANGUAGE = T.SOURCE_LANG)
736 where (
737 T.CHANNEL_ID,
738 T.LANGUAGE
739 ) in (select
740 SUBT.CHANNEL_ID,
741 SUBT.LANGUAGE
742 from AMV_C_CHANNELS_TL SUBB, AMV_C_CHANNELS_TL SUBT
743 where SUBB.CHANNEL_ID = SUBT.CHANNEL_ID
744 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
745 and (SUBB.CHANNEL_NAME <> SUBT.CHANNEL_NAME
746 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
747 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
748 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
749 ));
750
751 insert into AMV_C_CHANNELS_TL (
752 CHANNEL_ID,
753 LAST_UPDATE_DATE,
754 LAST_UPDATED_BY,
755 CREATION_DATE,
756 CREATED_BY,
757 LAST_UPDATE_LOGIN,
758 CHANNEL_NAME,
759 DESCRIPTION,
760 LANGUAGE,
761 SOURCE_LANG
762 ) select
763 B.CHANNEL_ID,
764 B.LAST_UPDATE_DATE,
765 B.LAST_UPDATED_BY,
766 B.CREATION_DATE,
767 B.CREATED_BY,
768 B.LAST_UPDATE_LOGIN,
769 B.CHANNEL_NAME,
770 B.DESCRIPTION,
771 L.LANGUAGE_CODE,
772 B.SOURCE_LANG
773 from AMV_C_CHANNELS_TL B, FND_LANGUAGES L
774 where L.INSTALLED_FLAG in ('I', 'B')
775 and B.LANGUAGE = userenv('LANG')
776 and not exists
777 (select NULL
778 from AMV_C_CHANNELS_TL T
779 where T.CHANNEL_ID = B.CHANNEL_ID
780 and T.LANGUAGE = L.LANGUAGE_CODE);
781 end ADD_LANGUAGE;
782
783 end AMV_C_CHANNELS_PKG;