[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_HEADERS_ALL_PKG
Source
1 package body AMS_LIST_HEADERS_ALL_PKG as
2 /* $Header: amstlshb.pls 120.1 2006/06/26 12:19:58 bmuthukr noship $ */
3 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
4 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6
7 procedure INSERT_ROW (
8 X_ROWID in OUT NOCOPY VARCHAR2,
9 X_LIST_HEADER_ID in NUMBER,
10 X_COUNTRY in NUMBER,
11 X_USER_STATUS_ID in NUMBER,
12 X_QUANTUM in NUMBER,
13 X_RELEASE_CONTROL_ALG_ID in NUMBER,
14 X_DIALING_METHOD in VARCHAR2,
15 X_CALLING_CALENDAR_ID in NUMBER,
16 X_RELEASE_STRATEGY in VARCHAR2,
17 X_CALLBACK_PRIORITY_FLAG in VARCHAR2,
18 X_CALL_CENTER_READY_FLAG in VARCHAR2,
19 X_CUSTOM_SETUP_ID in NUMBER,
20 X_OWNER_USER_ID in NUMBER,
21 X_ACCESS_LEVEL in VARCHAR2,
22 X_ENABLE_LOG_FLAG in VARCHAR2,
23 X_ENABLE_WORD_REPLACEMENT_FLAG in VARCHAR2,
24 X_ENABLE_PARALLEL_DML_FLAG in VARCHAR2,
25 X_DEDUPE_DURING_GENERATION_FLA in VARCHAR2,
26 X_GENERATE_CONTROL_GROUP_FLAG in VARCHAR2,
27 X_LAST_GENERATION_SUCCESS_FLAG in VARCHAR2,
28 X_FORECASTED_START_DATE in DATE,
29 X_FORECASTED_END_DATE in DATE,
30 X_ACTUAL_END_DATE in DATE,
31 X_SENT_OUT_DATE in DATE,
32 X_DEDUPE_START_DATE in DATE,
33 X_LAST_DEDUPE_DATE in DATE,
34 X_LAST_DEDUPED_BY_USER_ID in NUMBER,
35 X_WORKFLOW_ITEM_KEY in NUMBER,
36 X_NO_OF_ROWS_DUPLICATES in NUMBER,
37 X_NO_OF_ROWS_MIN_REQUESTED in NUMBER,
38 X_NO_OF_ROWS_MAX_REQUESTED in NUMBER,
39 X_NO_OF_ROWS_IN_LIST in NUMBER,
40 X_NO_OF_ROWS_IN_CTRL_GROUP in NUMBER,
41 X_NO_OF_ROWS_ACTIVE in NUMBER,
42 X_NO_OF_ROWS_INACTIVE in NUMBER,
43 X_NO_OF_ROWS_MANUALLY_ENTERED in NUMBER,
44 X_NO_OF_ROWS_DO_NOT_CALL in NUMBER,
45 X_NO_OF_ROWS_DO_NOT_MAIL in NUMBER,
46 X_NO_OF_ROWS_RANDOM in NUMBER,
47 X_MAIN_GEN_START_TIME in DATE,
48 X_MAIN_GEN_END_TIME in DATE,
49 X_MAIN_RANDOM_NTH_ROW_SELECTIO in NUMBER,
50 X_MAIN_RANDOM_PCT_ROW_SELECTIO in NUMBER,
51 X_CTRL_RANDOM_NTH_ROW_SELECTIO in NUMBER,
52 X_CTRL_RANDOM_PCT_ROW_SELECTIO in NUMBER,
53 X_REPEAT_SOURCE_LIST_HEADER_ID in VARCHAR2,
54 X_RESULT_TEXT in VARCHAR2,
55 X_KEYWORDS in VARCHAR2,
56 X_LIST_PRIORITY in NUMBER,
57 X_ASSIGN_PERSON_ID in NUMBER,
58 X_LIST_SOURCE in VARCHAR2,
59 X_LIST_SOURCE_TYPE in VARCHAR2,
60 X_LIST_ONLINE_FLAG in VARCHAR2,
61 X_RANDOM_LIST_ID in NUMBER,
62 X_ENABLED_FLAG in VARCHAR2,
63 X_ASSIGNED_TO in NUMBER,
64 X_QUERY_ID in NUMBER,
65 X_OWNER_PERSON_ID in NUMBER,
66 X_ARCHIVED_BY in NUMBER,
67 X_ARCHIVED_DATE in DATE,
68 X_ATTRIBUTE_CATEGORY in VARCHAR2,
69 X_ATTRIBUTE1 in VARCHAR2,
70 X_ATTRIBUTE2 in VARCHAR2,
71 X_ATTRIBUTE3 in VARCHAR2,
72 X_ATTRIBUTE4 in VARCHAR2,
73 X_ATTRIBUTE5 in VARCHAR2,
74 X_ATTRIBUTE6 in VARCHAR2,
75 X_ATTRIBUTE7 in VARCHAR2,
76 X_ATTRIBUTE8 in VARCHAR2,
77 X_ATTRIBUTE9 in VARCHAR2,
78 X_ATTRIBUTE10 in VARCHAR2,
79 X_ATTRIBUTE11 in VARCHAR2,
80 X_ATTRIBUTE12 in VARCHAR2,
81 X_ATTRIBUTE13 in VARCHAR2,
82 X_ATTRIBUTE14 in VARCHAR2,
83 X_ATTRIBUTE15 in VARCHAR2,
84 X_TIMEZONE_ID in NUMBER,
85 X_USER_ENTERED_START_TIME in DATE,
86 X_OBJECT_VERSION_NUMBER in NUMBER,
87 X_REQUEST_ID in NUMBER,
88 X_VIEW_APPLICATION_ID in NUMBER,
89 X_LIST_USED_BY_ID in NUMBER,
90 X_ARC_LIST_USED_BY in VARCHAR2,
91 X_LIST_TYPE in VARCHAR2,
92 X_STATUS_CODE in VARCHAR2,
93 X_STATUS_DATE in DATE,
94 X_GENERATION_TYPE in VARCHAR2,
95 X_REPEAT_EXCLUDE_TYPE in VARCHAR2,
96 X_ROW_SELECTION_TYPE in VARCHAR2,
97 X_LIST_NAME in VARCHAR2,
98 X_DESCRIPTION in VARCHAR2,
99 X_CREATION_DATE in DATE,
100 X_CREATED_BY in NUMBER,
101 X_LAST_UPDATE_DATE in DATE,
102 X_LAST_UPDATED_BY in NUMBER,
103 X_LAST_UPDATE_LOGIN in NUMBER,
104 X_NO_OF_ROWS_PREV_CONTACTED in NUMBER,
105 X_APPLY_TRAFFIC_COP in VARCHAR2
106 ) is
107 cursor C is select ROWID from AMS_LIST_HEADERS_ALL
108 where LIST_HEADER_ID = X_LIST_HEADER_ID
109 ;
110 begin
111 insert into AMS_LIST_HEADERS_ALL (
112 COUNTRY,
113 USER_STATUS_ID,
114 QUANTUM,
115 RELEASE_CONTROL_ALG_ID,
116 DIALING_METHOD,
117 CALLING_CALENDAR_ID,
118 RELEASE_STRATEGY,
119 CALLBACK_PRIORITY_FLAG,
120 CALL_CENTER_READY_FLAG,
121 CUSTOM_SETUP_ID,
122 OWNER_USER_ID,
123 ACCESS_LEVEL,
124 ENABLE_LOG_FLAG,
125 ENABLE_WORD_REPLACEMENT_FLAG,
126 ENABLE_PARALLEL_DML_FLAG,
127 DEDUPE_DURING_GENERATION_FLAG,
128 GENERATE_CONTROL_GROUP_FLAG,
129 LAST_GENERATION_SUCCESS_FLAG,
130 FORECASTED_START_DATE,
131 FORECASTED_END_DATE,
132 ACTUAL_END_DATE,
133 SENT_OUT_DATE,
134 DEDUPE_START_DATE,
135 LAST_DEDUPE_DATE,
136 LAST_DEDUPED_BY_USER_ID,
137 WORKFLOW_ITEM_KEY,
138 NO_OF_ROWS_DUPLICATES,
139 NO_OF_ROWS_MIN_REQUESTED,
140 NO_OF_ROWS_MAX_REQUESTED,
141 NO_OF_ROWS_IN_LIST,
142 NO_OF_ROWS_IN_CTRL_GROUP,
143 NO_OF_ROWS_ACTIVE,
144 NO_OF_ROWS_INACTIVE,
145 NO_OF_ROWS_MANUALLY_ENTERED,
146 NO_OF_ROWS_DO_NOT_CALL,
147 NO_OF_ROWS_DO_NOT_MAIL,
148 NO_OF_ROWS_RANDOM,
149 MAIN_GEN_START_TIME,
150 MAIN_GEN_END_TIME,
151 MAIN_RANDOM_NTH_ROW_SELECTION,
152 MAIN_RANDOM_PCT_ROW_SELECTION,
153 CTRL_RANDOM_NTH_ROW_SELECTION,
154 CTRL_RANDOM_PCT_ROW_SELECTION,
155 REPEAT_SOURCE_LIST_HEADER_ID,
156 RESULT_TEXT,
157 KEYWORDS,
158 LIST_PRIORITY,
159 ASSIGN_PERSON_ID,
160 LIST_SOURCE,
161 LIST_SOURCE_TYPE,
162 LIST_ONLINE_FLAG,
163 RANDOM_LIST_ID,
164 ENABLED_FLAG,
165 ASSIGNED_TO,
166 QUERY_ID,
167 OWNER_PERSON_ID,
168 ARCHIVED_BY,
169 ARCHIVED_DATE,
170 ATTRIBUTE_CATEGORY,
171 ATTRIBUTE1,
172 ATTRIBUTE2,
173 ATTRIBUTE3,
174 ATTRIBUTE4,
175 ATTRIBUTE5,
176 ATTRIBUTE6,
177 ATTRIBUTE7,
178 ATTRIBUTE8,
179 ATTRIBUTE9,
180 ATTRIBUTE10,
181 ATTRIBUTE11,
182 ATTRIBUTE12,
183 ATTRIBUTE13,
184 ATTRIBUTE14,
185 ATTRIBUTE15,
186 TIMEZONE_ID,
187 USER_ENTERED_START_TIME,
188 LIST_HEADER_ID,
189 OBJECT_VERSION_NUMBER,
190 REQUEST_ID,
191 VIEW_APPLICATION_ID,
192 LIST_USED_BY_ID,
193 ARC_LIST_USED_BY,
194 LIST_TYPE,
195 STATUS_CODE,
196 STATUS_DATE,
197 GENERATION_TYPE,
198 REPEAT_EXCLUDE_TYPE,
199 ROW_SELECTION_TYPE,
200 CREATION_DATE,
201 CREATED_BY,
202 LAST_UPDATE_DATE,
203 LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN,
205 NO_OF_ROWS_PREV_CONTACTED ,
206 APPLY_TRAFFIC_COP
207 ) values (
208 X_COUNTRY,
209 X_USER_STATUS_ID,
210 X_QUANTUM,
211 X_RELEASE_CONTROL_ALG_ID,
212 X_DIALING_METHOD,
213 X_CALLING_CALENDAR_ID,
214 X_RELEASE_STRATEGY,
215 X_CALLBACK_PRIORITY_FLAG,
216 X_CALL_CENTER_READY_FLAG,
217 X_CUSTOM_SETUP_ID,
218 X_OWNER_USER_ID,
219 X_ACCESS_LEVEL,
220 X_ENABLE_LOG_FLAG,
221 X_ENABLE_WORD_REPLACEMENT_FLAG,
222 X_ENABLE_PARALLEL_DML_FLAG,
223 X_DEDUPE_DURING_GENERATION_FLA,
224 X_GENERATE_CONTROL_GROUP_FLAG,
225 X_LAST_GENERATION_SUCCESS_FLAG,
226 X_FORECASTED_START_DATE,
227 X_FORECASTED_END_DATE,
228 X_ACTUAL_END_DATE,
229 X_SENT_OUT_DATE,
230 X_DEDUPE_START_DATE,
231 X_LAST_DEDUPE_DATE,
232 X_LAST_DEDUPED_BY_USER_ID,
233 X_WORKFLOW_ITEM_KEY,
234 X_NO_OF_ROWS_DUPLICATES,
235 X_NO_OF_ROWS_MIN_REQUESTED,
236 X_NO_OF_ROWS_MAX_REQUESTED,
237 X_NO_OF_ROWS_IN_LIST,
238 X_NO_OF_ROWS_IN_CTRL_GROUP,
239 X_NO_OF_ROWS_ACTIVE,
240 X_NO_OF_ROWS_INACTIVE,
241 X_NO_OF_ROWS_MANUALLY_ENTERED,
242 X_NO_OF_ROWS_DO_NOT_CALL,
243 X_NO_OF_ROWS_DO_NOT_MAIL,
244 X_NO_OF_ROWS_RANDOM,
245 X_MAIN_GEN_START_TIME,
246 X_MAIN_GEN_END_TIME,
247 X_MAIN_RANDOM_NTH_ROW_SELECTIO,
248 X_MAIN_RANDOM_PCT_ROW_SELECTIO,
249 X_CTRL_RANDOM_NTH_ROW_SELECTIO,
250 X_CTRL_RANDOM_PCT_ROW_SELECTIO,
251 X_REPEAT_SOURCE_LIST_HEADER_ID,
252 X_RESULT_TEXT,
253 X_KEYWORDS,
254 X_LIST_PRIORITY,
255 X_ASSIGN_PERSON_ID,
256 X_LIST_SOURCE,
257 X_LIST_SOURCE_TYPE,
258 X_LIST_ONLINE_FLAG,
259 X_RANDOM_LIST_ID,
260 X_ENABLED_FLAG,
261 X_ASSIGNED_TO,
262 X_QUERY_ID,
263 X_OWNER_PERSON_ID,
264 X_ARCHIVED_BY,
265 X_ARCHIVED_DATE,
266 X_ATTRIBUTE_CATEGORY,
267 X_ATTRIBUTE1,
268 X_ATTRIBUTE2,
269 X_ATTRIBUTE3,
270 X_ATTRIBUTE4,
271 X_ATTRIBUTE5,
272 X_ATTRIBUTE6,
273 X_ATTRIBUTE7,
274 X_ATTRIBUTE8,
275 X_ATTRIBUTE9,
276 X_ATTRIBUTE10,
277 X_ATTRIBUTE11,
278 X_ATTRIBUTE12,
279 X_ATTRIBUTE13,
280 X_ATTRIBUTE14,
281 X_ATTRIBUTE15,
282 X_TIMEZONE_ID,
283 X_USER_ENTERED_START_TIME,
284 X_LIST_HEADER_ID,
285 X_OBJECT_VERSION_NUMBER,
286 X_REQUEST_ID,
287 X_VIEW_APPLICATION_ID,
288 X_LIST_USED_BY_ID,
289 X_ARC_LIST_USED_BY,
290 X_LIST_TYPE,
291 X_STATUS_CODE,
292 X_STATUS_DATE,
293 X_GENERATION_TYPE,
294 X_REPEAT_EXCLUDE_TYPE,
295 X_ROW_SELECTION_TYPE,
296 X_CREATION_DATE,
297 X_CREATED_BY,
298 X_LAST_UPDATE_DATE,
299 X_LAST_UPDATED_BY,
300 X_LAST_UPDATE_LOGIN,
301 X_NO_OF_ROWS_PREV_CONTACTED,
302 X_APPLY_TRAFFIC_COP
303 );
304
305 insert into AMS_LIST_HEADERS_ALL_TL (
306 LIST_NAME,
307 DESCRIPTION,
308 LAST_UPDATE_LOGIN,
309 LIST_HEADER_ID,
310 LAST_UPDATE_DATE,
311 LAST_UPDATE_BY,
312 CREATION_DATE,
313 CREATED_BY,
314 LANGUAGE,
315 SOURCE_LANG
316 ) select
317 X_LIST_NAME,
318 X_DESCRIPTION,
319 X_LAST_UPDATE_LOGIN,
320 X_LIST_HEADER_ID,
321 X_LAST_UPDATE_DATE,
322 X_LAST_UPDATED_BY,
323 X_CREATION_DATE,
324 X_CREATED_BY,
325 L.LANGUAGE_CODE,
326 userenv('LANG')
327 from FND_LANGUAGES L
328 where L.INSTALLED_FLAG in ('I', 'B')
329 and not exists
330 (select NULL
331 from AMS_LIST_HEADERS_ALL_TL T
332 where T.LIST_HEADER_ID = X_LIST_HEADER_ID
333 and T.LANGUAGE = L.LANGUAGE_CODE);
334
335 open c;
336 fetch c into X_ROWID;
337 if (c%notfound) then
338 close c;
339 raise no_data_found;
340 end if;
341 close c;
342
343 end INSERT_ROW;
344
345 procedure LOCK_ROW (
346 X_LIST_HEADER_ID in NUMBER,
347 X_COUNTRY in NUMBER,
348 X_USER_STATUS_ID in NUMBER,
349 X_QUANTUM in NUMBER,
350 X_RELEASE_CONTROL_ALG_ID in NUMBER,
351 X_DIALING_METHOD in VARCHAR2,
352 X_CALLING_CALENDAR_ID in NUMBER,
353 X_RELEASE_STRATEGY in VARCHAR2,
354 X_CALLBACK_PRIORITY_FLAG in VARCHAR2,
355 X_CALL_CENTER_READY_FLAG in VARCHAR2,
356 X_CUSTOM_SETUP_ID in NUMBER,
357 X_OWNER_USER_ID in NUMBER,
358 X_ACCESS_LEVEL in VARCHAR2,
359 X_ENABLE_LOG_FLAG in VARCHAR2,
360 X_ENABLE_WORD_REPLACEMENT_FLAG in VARCHAR2,
361 X_ENABLE_PARALLEL_DML_FLAG in VARCHAR2,
362 X_DEDUPE_DURING_GENERATION_FLA in VARCHAR2,
363 X_GENERATE_CONTROL_GROUP_FLAG in VARCHAR2,
364 X_LAST_GENERATION_SUCCESS_FLAG in VARCHAR2,
365 X_FORECASTED_START_DATE in DATE,
366 X_FORECASTED_END_DATE in DATE,
367 X_ACTUAL_END_DATE in DATE,
368 X_SENT_OUT_DATE in DATE,
369 X_DEDUPE_START_DATE in DATE,
370 X_LAST_DEDUPE_DATE in DATE,
371 X_LAST_DEDUPED_BY_USER_ID in NUMBER,
372 X_WORKFLOW_ITEM_KEY in NUMBER,
373 X_NO_OF_ROWS_DUPLICATES in NUMBER,
374 X_NO_OF_ROWS_MIN_REQUESTED in NUMBER,
375 X_NO_OF_ROWS_MAX_REQUESTED in NUMBER,
376 X_NO_OF_ROWS_IN_LIST in NUMBER,
377 X_NO_OF_ROWS_IN_CTRL_GROUP in NUMBER,
378 X_NO_OF_ROWS_ACTIVE in NUMBER,
379 X_NO_OF_ROWS_INACTIVE in NUMBER,
380 X_NO_OF_ROWS_MANUALLY_ENTERED in NUMBER,
381 X_NO_OF_ROWS_DO_NOT_CALL in NUMBER,
382 X_NO_OF_ROWS_DO_NOT_MAIL in NUMBER,
383 X_NO_OF_ROWS_RANDOM in NUMBER,
384 X_MAIN_GEN_START_TIME in DATE,
385 X_MAIN_GEN_END_TIME in DATE,
386 X_MAIN_RANDOM_NTH_ROW_SELECTIO in NUMBER,
387 X_MAIN_RANDOM_PCT_ROW_SELECTIO in NUMBER,
388 X_CTRL_RANDOM_NTH_ROW_SELECTIO in NUMBER,
389 X_CTRL_RANDOM_PCT_ROW_SELECTIO in NUMBER,
390 X_REPEAT_SOURCE_LIST_HEADER_ID in VARCHAR2,
391 X_RESULT_TEXT in VARCHAR2,
392 X_KEYWORDS in VARCHAR2,
393 X_LIST_PRIORITY in NUMBER,
394 X_ASSIGN_PERSON_ID in NUMBER,
395 X_LIST_SOURCE in VARCHAR2,
396 X_LIST_SOURCE_TYPE in VARCHAR2,
397 X_LIST_ONLINE_FLAG in VARCHAR2,
398 X_RANDOM_LIST_ID in NUMBER,
399 X_ENABLED_FLAG in VARCHAR2,
400 X_ASSIGNED_TO in NUMBER,
401 X_QUERY_ID in NUMBER,
402 X_OWNER_PERSON_ID in NUMBER,
403 X_ARCHIVED_BY in NUMBER,
404 X_ARCHIVED_DATE in DATE,
405 X_ATTRIBUTE_CATEGORY in VARCHAR2,
406 X_ATTRIBUTE1 in VARCHAR2,
407 X_ATTRIBUTE2 in VARCHAR2,
408 X_ATTRIBUTE3 in VARCHAR2,
409 X_ATTRIBUTE4 in VARCHAR2,
410 X_ATTRIBUTE5 in VARCHAR2,
411 X_ATTRIBUTE6 in VARCHAR2,
412 X_ATTRIBUTE7 in VARCHAR2,
413 X_ATTRIBUTE8 in VARCHAR2,
414 X_ATTRIBUTE9 in VARCHAR2,
415 X_ATTRIBUTE10 in VARCHAR2,
416 X_ATTRIBUTE11 in VARCHAR2,
417 X_ATTRIBUTE12 in VARCHAR2,
418 X_ATTRIBUTE13 in VARCHAR2,
419 X_ATTRIBUTE14 in VARCHAR2,
420 X_ATTRIBUTE15 in VARCHAR2,
421 X_TIMEZONE_ID in NUMBER,
422 X_USER_ENTERED_START_TIME in DATE,
423 X_OBJECT_VERSION_NUMBER in NUMBER,
424 X_REQUEST_ID in NUMBER,
425 X_VIEW_APPLICATION_ID in NUMBER,
426 X_LIST_USED_BY_ID in NUMBER,
427 X_ARC_LIST_USED_BY in VARCHAR2,
428 X_LIST_TYPE in VARCHAR2,
429 X_STATUS_CODE in VARCHAR2,
430 X_STATUS_DATE in DATE,
431 X_GENERATION_TYPE in VARCHAR2,
432 X_REPEAT_EXCLUDE_TYPE in VARCHAR2,
433 X_ROW_SELECTION_TYPE in VARCHAR2,
434 X_LIST_NAME in VARCHAR2,
435 X_DESCRIPTION in VARCHAR2,
436 X_NO_OF_ROWS_PREV_CONTACTED in NUMBER,
437 X_APPLY_TRAFFIC_COP in VARCHAR2
438 ) is
439 cursor c is select
440 COUNTRY,
441 USER_STATUS_ID,
442 QUANTUM,
443 RELEASE_CONTROL_ALG_ID,
444 DIALING_METHOD,
445 CALLING_CALENDAR_ID,
446 RELEASE_STRATEGY,
447 CALLBACK_PRIORITY_FLAG,
448 CALL_CENTER_READY_FLAG,
449 CUSTOM_SETUP_ID,
450 OWNER_USER_ID,
451 ACCESS_LEVEL,
452 ENABLE_LOG_FLAG,
453 ENABLE_WORD_REPLACEMENT_FLAG,
454 ENABLE_PARALLEL_DML_FLAG,
455 DEDUPE_DURING_GENERATION_FLAG,
456 GENERATE_CONTROL_GROUP_FLAG,
457 LAST_GENERATION_SUCCESS_FLAG,
458 FORECASTED_START_DATE,
459 FORECASTED_END_DATE,
460 ACTUAL_END_DATE,
461 SENT_OUT_DATE,
462 DEDUPE_START_DATE,
463 LAST_DEDUPE_DATE,
464 LAST_DEDUPED_BY_USER_ID,
465 WORKFLOW_ITEM_KEY,
466 NO_OF_ROWS_DUPLICATES,
467 NO_OF_ROWS_MIN_REQUESTED,
468 NO_OF_ROWS_MAX_REQUESTED,
469 NO_OF_ROWS_IN_LIST,
470 NO_OF_ROWS_IN_CTRL_GROUP,
471 NO_OF_ROWS_ACTIVE,
472 NO_OF_ROWS_INACTIVE,
473 NO_OF_ROWS_MANUALLY_ENTERED,
474 NO_OF_ROWS_DO_NOT_CALL,
475 NO_OF_ROWS_DO_NOT_MAIL,
476 NO_OF_ROWS_RANDOM,
477 MAIN_GEN_START_TIME,
478 MAIN_GEN_END_TIME,
479 MAIN_RANDOM_NTH_ROW_SELECTION,
480 MAIN_RANDOM_PCT_ROW_SELECTION,
484 RESULT_TEXT,
481 CTRL_RANDOM_NTH_ROW_SELECTION,
482 CTRL_RANDOM_PCT_ROW_SELECTION,
483 REPEAT_SOURCE_LIST_HEADER_ID,
485 KEYWORDS,
486 LIST_PRIORITY,
487 ASSIGN_PERSON_ID,
488 LIST_SOURCE,
489 LIST_SOURCE_TYPE,
490 LIST_ONLINE_FLAG,
491 RANDOM_LIST_ID,
492 ENABLED_FLAG,
493 ASSIGNED_TO,
494 QUERY_ID,
495 OWNER_PERSON_ID,
496 ARCHIVED_BY,
497 ARCHIVED_DATE,
498 ATTRIBUTE_CATEGORY,
499 ATTRIBUTE1,
500 ATTRIBUTE2,
501 ATTRIBUTE3,
502 ATTRIBUTE4,
503 ATTRIBUTE5,
504 ATTRIBUTE6,
505 ATTRIBUTE7,
506 ATTRIBUTE8,
507 ATTRIBUTE9,
508 ATTRIBUTE10,
509 ATTRIBUTE11,
510 ATTRIBUTE12,
511 ATTRIBUTE13,
512 ATTRIBUTE14,
513 ATTRIBUTE15,
514 TIMEZONE_ID,
515 USER_ENTERED_START_TIME,
516 OBJECT_VERSION_NUMBER,
517 REQUEST_ID,
518 VIEW_APPLICATION_ID,
519 LIST_USED_BY_ID,
520 ARC_LIST_USED_BY,
521 LIST_TYPE,
522 STATUS_CODE,
523 STATUS_DATE,
524 GENERATION_TYPE,
525 REPEAT_EXCLUDE_TYPE,
526 ROW_SELECTION_TYPE,
527 NO_OF_ROWS_PREV_CONTACTED ,
528 APPLY_TRAFFIC_COP
529 from AMS_LIST_HEADERS_ALL
530 where LIST_HEADER_ID = X_LIST_HEADER_ID
531 for update of LIST_HEADER_ID nowait;
532 recinfo c%rowtype;
533
534 cursor c1 is select
535 LIST_NAME,
536 DESCRIPTION,
537 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
538 from AMS_LIST_HEADERS_ALL_TL
539 where LIST_HEADER_ID = X_LIST_HEADER_ID
540 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
541 for update of LIST_HEADER_ID nowait;
542 begin
543 open c;
544 fetch c into recinfo;
545 if (c%notfound) then
546 close c;
547 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
548 app_exception.raise_exception;
549 end if;
550 close c;
551 if ( ((recinfo.COUNTRY = X_COUNTRY)
552 OR ((recinfo.COUNTRY is null) AND (X_COUNTRY is null)))
553 AND ((recinfo.USER_STATUS_ID = X_USER_STATUS_ID)
554 OR ((recinfo.USER_STATUS_ID is null) AND (X_USER_STATUS_ID is null)))
555 AND ((recinfo.QUANTUM = X_QUANTUM)
556 OR ((recinfo.QUANTUM is null) AND (X_QUANTUM is null)))
557 AND ((recinfo.RELEASE_CONTROL_ALG_ID = X_RELEASE_CONTROL_ALG_ID)
558 OR ((recinfo.RELEASE_CONTROL_ALG_ID is null) AND (X_RELEASE_CONTROL_ALG_ID is null)))
559 AND ((recinfo.DIALING_METHOD = X_DIALING_METHOD)
560 OR ((recinfo.DIALING_METHOD is null) AND (X_DIALING_METHOD is null)))
561 AND ((recinfo.CALLING_CALENDAR_ID = X_CALLING_CALENDAR_ID)
562 OR ((recinfo.CALLING_CALENDAR_ID is null) AND (X_CALLING_CALENDAR_ID is null)))
563 AND ((recinfo.RELEASE_STRATEGY = X_RELEASE_STRATEGY)
564 OR ((recinfo.RELEASE_STRATEGY is null) AND (X_RELEASE_STRATEGY is null)))
565 AND ((recinfo.CALLBACK_PRIORITY_FLAG = X_CALLBACK_PRIORITY_FLAG)
566 OR ((recinfo.CALLBACK_PRIORITY_FLAG is null) AND (X_CALLBACK_PRIORITY_FLAG is null)))
567 AND ((recinfo.CALL_CENTER_READY_FLAG = X_CALL_CENTER_READY_FLAG)
568 OR ((recinfo.CALL_CENTER_READY_FLAG is null) AND (X_CALL_CENTER_READY_FLAG is null)))
569 AND ((recinfo.CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID)
570 OR ((recinfo.CUSTOM_SETUP_ID is null) AND (X_CUSTOM_SETUP_ID is null)))
571 AND (recinfo.OWNER_USER_ID = X_OWNER_USER_ID)
572 AND (recinfo.ACCESS_LEVEL = X_ACCESS_LEVEL)
573 AND (recinfo.ENABLE_LOG_FLAG = X_ENABLE_LOG_FLAG)
574 AND (recinfo.ENABLE_WORD_REPLACEMENT_FLAG = X_ENABLE_WORD_REPLACEMENT_FLAG)
575 AND (recinfo.ENABLE_PARALLEL_DML_FLAG = X_ENABLE_PARALLEL_DML_FLAG)
576 AND (recinfo.DEDUPE_DURING_GENERATION_FLAG = X_DEDUPE_DURING_GENERATION_FLA)
577 AND (recinfo.GENERATE_CONTROL_GROUP_FLAG = X_GENERATE_CONTROL_GROUP_FLAG)
578 AND (recinfo.LAST_GENERATION_SUCCESS_FLAG = X_LAST_GENERATION_SUCCESS_FLAG)
579 AND (recinfo.FORECASTED_START_DATE = X_FORECASTED_START_DATE)
580 AND ((recinfo.FORECASTED_END_DATE = X_FORECASTED_END_DATE)
581 OR ((recinfo.FORECASTED_END_DATE is null) AND (X_FORECASTED_END_DATE is null)))
582 AND ((recinfo.ACTUAL_END_DATE = X_ACTUAL_END_DATE)
583 OR ((recinfo.ACTUAL_END_DATE is null) AND (X_ACTUAL_END_DATE is null)))
584 AND ((recinfo.SENT_OUT_DATE = X_SENT_OUT_DATE)
585 OR ((recinfo.SENT_OUT_DATE is null) AND (X_SENT_OUT_DATE is null)))
586 AND ((recinfo.DEDUPE_START_DATE = X_DEDUPE_START_DATE)
587 OR ((recinfo.DEDUPE_START_DATE is null) AND (X_DEDUPE_START_DATE is null)))
588 AND ((recinfo.LAST_DEDUPE_DATE = X_LAST_DEDUPE_DATE)
589 OR ((recinfo.LAST_DEDUPE_DATE is null) AND (X_LAST_DEDUPE_DATE is null)))
590 AND ((recinfo.LAST_DEDUPED_BY_USER_ID = X_LAST_DEDUPED_BY_USER_ID)
591 OR ((recinfo.LAST_DEDUPED_BY_USER_ID is null) AND (X_LAST_DEDUPED_BY_USER_ID is null)))
592 AND ((recinfo.WORKFLOW_ITEM_KEY = X_WORKFLOW_ITEM_KEY)
593 OR ((recinfo.WORKFLOW_ITEM_KEY is null) AND (X_WORKFLOW_ITEM_KEY is null)))
594 AND ((recinfo.NO_OF_ROWS_DUPLICATES = X_NO_OF_ROWS_DUPLICATES)
595 OR ((recinfo.NO_OF_ROWS_DUPLICATES is null) AND (X_NO_OF_ROWS_DUPLICATES is null)))
599 OR ((recinfo.NO_OF_ROWS_MAX_REQUESTED is null) AND (X_NO_OF_ROWS_MAX_REQUESTED is null)))
596 AND ((recinfo.NO_OF_ROWS_MIN_REQUESTED = X_NO_OF_ROWS_MIN_REQUESTED)
597 OR ((recinfo.NO_OF_ROWS_MIN_REQUESTED is null) AND (X_NO_OF_ROWS_MIN_REQUESTED is null)))
598 AND ((recinfo.NO_OF_ROWS_MAX_REQUESTED = X_NO_OF_ROWS_MAX_REQUESTED)
600 AND ((recinfo.NO_OF_ROWS_IN_LIST = X_NO_OF_ROWS_IN_LIST)
601 OR ((recinfo.NO_OF_ROWS_IN_LIST is null) AND (X_NO_OF_ROWS_IN_LIST is null)))
602 AND ((recinfo.NO_OF_ROWS_IN_CTRL_GROUP = X_NO_OF_ROWS_IN_CTRL_GROUP)
603 OR ((recinfo.NO_OF_ROWS_IN_CTRL_GROUP is null) AND (X_NO_OF_ROWS_IN_CTRL_GROUP is null)))
604 AND ((recinfo.NO_OF_ROWS_ACTIVE = X_NO_OF_ROWS_ACTIVE)
605 OR ((recinfo.NO_OF_ROWS_ACTIVE is null) AND (X_NO_OF_ROWS_ACTIVE is null)))
606 AND ((recinfo.NO_OF_ROWS_INACTIVE = X_NO_OF_ROWS_INACTIVE)
607 OR ((recinfo.NO_OF_ROWS_INACTIVE is null) AND (X_NO_OF_ROWS_INACTIVE is null)))
608 AND ((recinfo.NO_OF_ROWS_MANUALLY_ENTERED = X_NO_OF_ROWS_MANUALLY_ENTERED)
609 OR ((recinfo.NO_OF_ROWS_MANUALLY_ENTERED is null) AND (X_NO_OF_ROWS_MANUALLY_ENTERED is null)))
610 AND ((recinfo.NO_OF_ROWS_DO_NOT_CALL = X_NO_OF_ROWS_DO_NOT_CALL)
611 OR ((recinfo.NO_OF_ROWS_DO_NOT_CALL is null) AND (X_NO_OF_ROWS_DO_NOT_CALL is null)))
612 AND ((recinfo.NO_OF_ROWS_DO_NOT_MAIL = X_NO_OF_ROWS_DO_NOT_MAIL)
613 OR ((recinfo.NO_OF_ROWS_DO_NOT_MAIL is null) AND (X_NO_OF_ROWS_DO_NOT_MAIL is null)))
614 AND ((recinfo.NO_OF_ROWS_RANDOM = X_NO_OF_ROWS_RANDOM)
615 OR ((recinfo.NO_OF_ROWS_RANDOM is null) AND (X_NO_OF_ROWS_RANDOM is null)))
616 AND ((recinfo.MAIN_GEN_START_TIME = X_MAIN_GEN_START_TIME)
617 OR ((recinfo.MAIN_GEN_START_TIME is null) AND (X_MAIN_GEN_START_TIME is null)))
618 AND ((recinfo.MAIN_GEN_END_TIME = X_MAIN_GEN_END_TIME)
619 OR ((recinfo.MAIN_GEN_END_TIME is null) AND (X_MAIN_GEN_END_TIME is null)))
620 AND ((recinfo.MAIN_RANDOM_NTH_ROW_SELECTION = X_MAIN_RANDOM_NTH_ROW_SELECTIO)
621 OR ((recinfo.MAIN_RANDOM_NTH_ROW_SELECTION is null) AND (X_MAIN_RANDOM_NTH_ROW_SELECTIO is null)))
622 AND ((recinfo.MAIN_RANDOM_PCT_ROW_SELECTION = X_MAIN_RANDOM_PCT_ROW_SELECTIO)
623 OR ((recinfo.MAIN_RANDOM_PCT_ROW_SELECTION is null) AND (X_MAIN_RANDOM_PCT_ROW_SELECTIO is null)))
624 AND ((recinfo.CTRL_RANDOM_NTH_ROW_SELECTION = X_CTRL_RANDOM_NTH_ROW_SELECTIO)
625 OR ((recinfo.CTRL_RANDOM_NTH_ROW_SELECTION is null) AND (X_CTRL_RANDOM_NTH_ROW_SELECTIO is null)))
626 AND ((recinfo.CTRL_RANDOM_PCT_ROW_SELECTION = X_CTRL_RANDOM_PCT_ROW_SELECTIO)
627 OR ((recinfo.CTRL_RANDOM_PCT_ROW_SELECTION is null) AND (X_CTRL_RANDOM_PCT_ROW_SELECTIO is null)))
628 AND ((recinfo.REPEAT_SOURCE_LIST_HEADER_ID = X_REPEAT_SOURCE_LIST_HEADER_ID)
629 OR ((recinfo.REPEAT_SOURCE_LIST_HEADER_ID is null) AND (X_REPEAT_SOURCE_LIST_HEADER_ID is null)))
630 AND ((recinfo.RESULT_TEXT = X_RESULT_TEXT)
631 OR ((recinfo.RESULT_TEXT is null) AND (X_RESULT_TEXT is null)))
632 AND ((recinfo.KEYWORDS = X_KEYWORDS)
633 OR ((recinfo.KEYWORDS is null) AND (X_KEYWORDS is null)))
634 AND ((recinfo.LIST_PRIORITY = X_LIST_PRIORITY)
635 OR ((recinfo.LIST_PRIORITY is null) AND (X_LIST_PRIORITY is null)))
636 AND ((recinfo.ASSIGN_PERSON_ID = X_ASSIGN_PERSON_ID)
637 OR ((recinfo.ASSIGN_PERSON_ID is null) AND (X_ASSIGN_PERSON_ID is null)))
638 AND ((recinfo.LIST_SOURCE = X_LIST_SOURCE)
639 OR ((recinfo.LIST_SOURCE is null) AND (X_LIST_SOURCE is null)))
640 AND ((recinfo.LIST_SOURCE_TYPE = X_LIST_SOURCE_TYPE)
641 OR ((recinfo.LIST_SOURCE_TYPE is null) AND (X_LIST_SOURCE_TYPE is null)))
642 AND ((recinfo.LIST_ONLINE_FLAG = X_LIST_ONLINE_FLAG)
643 OR ((recinfo.LIST_ONLINE_FLAG is null) AND (X_LIST_ONLINE_FLAG is null)))
644 AND ((recinfo.RANDOM_LIST_ID = X_RANDOM_LIST_ID)
645 OR ((recinfo.RANDOM_LIST_ID is null) AND (X_RANDOM_LIST_ID is null)))
646 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
647 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
648 AND ((recinfo.ASSIGNED_TO = X_ASSIGNED_TO)
649 OR ((recinfo.ASSIGNED_TO is null) AND (X_ASSIGNED_TO is null)))
650 AND ((recinfo.QUERY_ID = X_QUERY_ID)
651 OR ((recinfo.QUERY_ID is null) AND (X_QUERY_ID is null)))
652 AND ((recinfo.OWNER_PERSON_ID = X_OWNER_PERSON_ID)
653 OR ((recinfo.OWNER_PERSON_ID is null) AND (X_OWNER_PERSON_ID is null)))
654 AND ((recinfo.ARCHIVED_BY = X_ARCHIVED_BY)
655 OR ((recinfo.ARCHIVED_BY is null) AND (X_ARCHIVED_BY is null)))
656 AND ((recinfo.ARCHIVED_DATE = X_ARCHIVED_DATE)
657 OR ((recinfo.ARCHIVED_DATE is null) AND (X_ARCHIVED_DATE is null)))
658 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
659 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
660 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
661 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
662 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
663 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
664 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
665 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
666 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
667 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
668 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
669 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
670 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
671 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
675 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
672 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
673 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
674 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
676 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
677 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
678 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
679 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
680 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
681 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
682 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
683 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
684 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
685 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
686 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
687 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
688 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
689 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
690 AND ((recinfo.TIMEZONE_ID = X_TIMEZONE_ID)
691 OR ((recinfo.TIMEZONE_ID is null) AND (X_TIMEZONE_ID is null)))
692 AND ((recinfo.USER_ENTERED_START_TIME = X_USER_ENTERED_START_TIME)
693 OR ((recinfo.USER_ENTERED_START_TIME is null) AND (X_USER_ENTERED_START_TIME is null)))
694 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
695 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
696 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
697 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
698 AND ((recinfo.VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID)
699 OR ((recinfo.VIEW_APPLICATION_ID is null) AND (X_VIEW_APPLICATION_ID is null)))
700 AND (recinfo.LIST_USED_BY_ID = X_LIST_USED_BY_ID)
701 AND (recinfo.ARC_LIST_USED_BY = X_ARC_LIST_USED_BY)
702 AND (recinfo.LIST_TYPE = X_LIST_TYPE)
703 AND (recinfo.STATUS_CODE = X_STATUS_CODE)
704 AND (recinfo.STATUS_DATE = X_STATUS_DATE)
705 AND (recinfo.GENERATION_TYPE = X_GENERATION_TYPE)
706 AND ((recinfo.REPEAT_EXCLUDE_TYPE = X_REPEAT_EXCLUDE_TYPE)
707 OR ((recinfo.REPEAT_EXCLUDE_TYPE is null) AND (X_REPEAT_EXCLUDE_TYPE is null)))
708 AND (recinfo.ROW_SELECTION_TYPE = X_ROW_SELECTION_TYPE)
709 AND ((recinfo.NO_OF_ROWS_prev_contacted = X_NO_OF_ROWS_prev_contacted )
710 OR ((recinfo.NO_OF_ROWS_prev_contacted is null) AND (X_NO_OF_ROWS_prev_contacted is null)))
711 AND ((recinfo.APPLY_TRAFFIC_COP = X_APPLY_TRAFFIC_COP )
712 OR ((recinfo.APPLY_TRAFFIC_COP is null) AND (X_APPLY_TRAFFIC_COP is null)))
713
714 ) then
715 null;
716 else
717 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
718 app_exception.raise_exception;
719 end if;
720
721 for tlinfo in c1 loop
722 if (tlinfo.BASELANG = 'Y') then
723 if ( ((tlinfo.LIST_NAME = X_LIST_NAME)
724 OR ((tlinfo.LIST_NAME is null) AND (X_LIST_NAME is null)))
725 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
726 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
727 ) then
728 null;
729 else
730 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
731 app_exception.raise_exception;
732 end if;
733 end if;
734 end loop;
735 return;
736 end LOCK_ROW;
737
738 procedure UPDATE_ROW (
739 X_LIST_HEADER_ID in NUMBER,
740 X_COUNTRY in NUMBER,
741 X_USER_STATUS_ID in NUMBER,
742 X_QUANTUM in NUMBER,
743 X_RELEASE_CONTROL_ALG_ID in NUMBER,
744 X_DIALING_METHOD in VARCHAR2,
745 X_CALLING_CALENDAR_ID in NUMBER,
746 X_RELEASE_STRATEGY in VARCHAR2,
747 X_CALLBACK_PRIORITY_FLAG in VARCHAR2,
748 X_CALL_CENTER_READY_FLAG in VARCHAR2,
749 X_CUSTOM_SETUP_ID in NUMBER,
750 X_OWNER_USER_ID in NUMBER,
751 X_ACCESS_LEVEL in VARCHAR2,
752 X_ENABLE_LOG_FLAG in VARCHAR2,
753 X_ENABLE_WORD_REPLACEMENT_FLAG in VARCHAR2,
754 X_ENABLE_PARALLEL_DML_FLAG in VARCHAR2,
755 X_DEDUPE_DURING_GENERATION_FLA in VARCHAR2,
756 X_GENERATE_CONTROL_GROUP_FLAG in VARCHAR2,
757 X_LAST_GENERATION_SUCCESS_FLAG in VARCHAR2,
758 X_FORECASTED_START_DATE in DATE,
759 X_FORECASTED_END_DATE in DATE,
760 X_ACTUAL_END_DATE in DATE,
761 X_SENT_OUT_DATE in DATE,
762 X_DEDUPE_START_DATE in DATE,
763 X_LAST_DEDUPE_DATE in DATE,
764 X_LAST_DEDUPED_BY_USER_ID in NUMBER,
765 X_WORKFLOW_ITEM_KEY in NUMBER,
766 X_NO_OF_ROWS_DUPLICATES in NUMBER,
767 X_NO_OF_ROWS_MIN_REQUESTED in NUMBER,
768 X_NO_OF_ROWS_MAX_REQUESTED in NUMBER,
769 X_NO_OF_ROWS_IN_LIST in NUMBER,
770 X_NO_OF_ROWS_IN_CTRL_GROUP in NUMBER,
771 X_NO_OF_ROWS_ACTIVE in NUMBER,
772 X_NO_OF_ROWS_INACTIVE in NUMBER,
773 X_NO_OF_ROWS_MANUALLY_ENTERED in NUMBER,
774 X_NO_OF_ROWS_DO_NOT_CALL in NUMBER,
775 X_NO_OF_ROWS_DO_NOT_MAIL in NUMBER,
776 X_NO_OF_ROWS_RANDOM in NUMBER,
777 X_MAIN_GEN_START_TIME in DATE,
778 X_MAIN_GEN_END_TIME in DATE,
779 X_MAIN_RANDOM_NTH_ROW_SELECTIO in NUMBER,
780 X_MAIN_RANDOM_PCT_ROW_SELECTIO in NUMBER,
781 X_CTRL_RANDOM_NTH_ROW_SELECTIO in NUMBER,
782 X_CTRL_RANDOM_PCT_ROW_SELECTIO in NUMBER,
783 X_REPEAT_SOURCE_LIST_HEADER_ID in VARCHAR2,
784 X_RESULT_TEXT in VARCHAR2,
785 X_KEYWORDS in VARCHAR2,
786 X_LIST_PRIORITY in NUMBER,
787 X_ASSIGN_PERSON_ID in NUMBER,
791 X_RANDOM_LIST_ID in NUMBER,
788 X_LIST_SOURCE in VARCHAR2,
789 X_LIST_SOURCE_TYPE in VARCHAR2,
790 X_LIST_ONLINE_FLAG in VARCHAR2,
792 X_ENABLED_FLAG in VARCHAR2,
793 X_ASSIGNED_TO in NUMBER,
794 X_QUERY_ID in NUMBER,
795 X_OWNER_PERSON_ID in NUMBER,
796 X_ARCHIVED_BY in NUMBER,
797 X_ARCHIVED_DATE in DATE,
798 X_ATTRIBUTE_CATEGORY in VARCHAR2,
799 X_ATTRIBUTE1 in VARCHAR2,
800 X_ATTRIBUTE2 in VARCHAR2,
801 X_ATTRIBUTE3 in VARCHAR2,
802 X_ATTRIBUTE4 in VARCHAR2,
803 X_ATTRIBUTE5 in VARCHAR2,
804 X_ATTRIBUTE6 in VARCHAR2,
805 X_ATTRIBUTE7 in VARCHAR2,
806 X_ATTRIBUTE8 in VARCHAR2,
807 X_ATTRIBUTE9 in VARCHAR2,
808 X_ATTRIBUTE10 in VARCHAR2,
809 X_ATTRIBUTE11 in VARCHAR2,
810 X_ATTRIBUTE12 in VARCHAR2,
811 X_ATTRIBUTE13 in VARCHAR2,
812 X_ATTRIBUTE14 in VARCHAR2,
813 X_ATTRIBUTE15 in VARCHAR2,
814 X_TIMEZONE_ID in NUMBER,
815 X_USER_ENTERED_START_TIME in DATE,
816 X_OBJECT_VERSION_NUMBER in NUMBER,
817 X_REQUEST_ID in NUMBER,
818 X_VIEW_APPLICATION_ID in NUMBER,
819 X_LIST_USED_BY_ID in NUMBER,
820 X_ARC_LIST_USED_BY in VARCHAR2,
821 X_LIST_TYPE in VARCHAR2,
822 X_STATUS_CODE in VARCHAR2,
823 X_STATUS_DATE in DATE,
824 X_GENERATION_TYPE in VARCHAR2,
825 X_REPEAT_EXCLUDE_TYPE in VARCHAR2,
826 X_ROW_SELECTION_TYPE in VARCHAR2,
827 X_LIST_NAME in VARCHAR2,
828 X_DESCRIPTION in VARCHAR2,
829 X_LAST_UPDATE_DATE in DATE,
830 X_LAST_UPDATED_BY in NUMBER,
831 X_LAST_UPDATE_LOGIN in NUMBER,
832 X_NO_OF_ROWS_PREV_CONTACTED in NUMBER,
833 X_APPLY_TRAFFIC_COP in VARCHAR2
834 ) is
835 begin
836 update AMS_LIST_HEADERS_ALL set
837 COUNTRY = X_COUNTRY,
838 USER_STATUS_ID = X_USER_STATUS_ID,
839 QUANTUM = X_QUANTUM,
840 RELEASE_CONTROL_ALG_ID = X_RELEASE_CONTROL_ALG_ID,
841 DIALING_METHOD = X_DIALING_METHOD,
842 CALLING_CALENDAR_ID = X_CALLING_CALENDAR_ID,
843 RELEASE_STRATEGY = X_RELEASE_STRATEGY,
844 CALLBACK_PRIORITY_FLAG = X_CALLBACK_PRIORITY_FLAG,
845 CALL_CENTER_READY_FLAG = X_CALL_CENTER_READY_FLAG,
846 CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID,
847 OWNER_USER_ID = X_OWNER_USER_ID,
848 ACCESS_LEVEL = X_ACCESS_LEVEL,
849 ENABLE_LOG_FLAG = X_ENABLE_LOG_FLAG,
850 ENABLE_WORD_REPLACEMENT_FLAG = X_ENABLE_WORD_REPLACEMENT_FLAG,
851 ENABLE_PARALLEL_DML_FLAG = X_ENABLE_PARALLEL_DML_FLAG,
852 DEDUPE_DURING_GENERATION_FLAG = X_DEDUPE_DURING_GENERATION_FLA,
853 GENERATE_CONTROL_GROUP_FLAG = X_GENERATE_CONTROL_GROUP_FLAG,
854 LAST_GENERATION_SUCCESS_FLAG = X_LAST_GENERATION_SUCCESS_FLAG,
855 FORECASTED_START_DATE = X_FORECASTED_START_DATE,
856 FORECASTED_END_DATE = X_FORECASTED_END_DATE,
857 ACTUAL_END_DATE = X_ACTUAL_END_DATE,
858 SENT_OUT_DATE = X_SENT_OUT_DATE,
859 DEDUPE_START_DATE = X_DEDUPE_START_DATE,
860 LAST_DEDUPE_DATE = X_LAST_DEDUPE_DATE,
861 LAST_DEDUPED_BY_USER_ID = X_LAST_DEDUPED_BY_USER_ID,
862 WORKFLOW_ITEM_KEY = X_WORKFLOW_ITEM_KEY,
863 NO_OF_ROWS_DUPLICATES = X_NO_OF_ROWS_DUPLICATES,
864 NO_OF_ROWS_MIN_REQUESTED = X_NO_OF_ROWS_MIN_REQUESTED,
865 NO_OF_ROWS_MAX_REQUESTED = X_NO_OF_ROWS_MAX_REQUESTED,
866 NO_OF_ROWS_IN_LIST = X_NO_OF_ROWS_IN_LIST,
867 NO_OF_ROWS_IN_CTRL_GROUP = X_NO_OF_ROWS_IN_CTRL_GROUP,
868 NO_OF_ROWS_ACTIVE = X_NO_OF_ROWS_ACTIVE,
869 NO_OF_ROWS_INACTIVE = X_NO_OF_ROWS_INACTIVE,
870 NO_OF_ROWS_MANUALLY_ENTERED = X_NO_OF_ROWS_MANUALLY_ENTERED,
871 NO_OF_ROWS_DO_NOT_CALL = X_NO_OF_ROWS_DO_NOT_CALL,
872 NO_OF_ROWS_DO_NOT_MAIL = X_NO_OF_ROWS_DO_NOT_MAIL,
873 NO_OF_ROWS_RANDOM = X_NO_OF_ROWS_RANDOM,
874 MAIN_GEN_START_TIME = X_MAIN_GEN_START_TIME,
875 MAIN_GEN_END_TIME = X_MAIN_GEN_END_TIME,
876 MAIN_RANDOM_NTH_ROW_SELECTION = X_MAIN_RANDOM_NTH_ROW_SELECTIO,
877 MAIN_RANDOM_PCT_ROW_SELECTION = X_MAIN_RANDOM_PCT_ROW_SELECTIO,
878 CTRL_RANDOM_NTH_ROW_SELECTION = X_CTRL_RANDOM_NTH_ROW_SELECTIO,
879 CTRL_RANDOM_PCT_ROW_SELECTION = X_CTRL_RANDOM_PCT_ROW_SELECTIO,
880 REPEAT_SOURCE_LIST_HEADER_ID = X_REPEAT_SOURCE_LIST_HEADER_ID,
881 RESULT_TEXT = X_RESULT_TEXT,
882 KEYWORDS = X_KEYWORDS,
883 LIST_PRIORITY = X_LIST_PRIORITY,
884 ASSIGN_PERSON_ID = X_ASSIGN_PERSON_ID,
885 LIST_SOURCE = X_LIST_SOURCE,
886 LIST_SOURCE_TYPE = X_LIST_SOURCE_TYPE,
887 LIST_ONLINE_FLAG = X_LIST_ONLINE_FLAG,
888 RANDOM_LIST_ID = X_RANDOM_LIST_ID,
889 ENABLED_FLAG = X_ENABLED_FLAG,
890 ASSIGNED_TO = X_ASSIGNED_TO,
891 QUERY_ID = X_QUERY_ID,
892 OWNER_PERSON_ID = X_OWNER_PERSON_ID,
893 ARCHIVED_BY = X_ARCHIVED_BY,
894 ARCHIVED_DATE = X_ARCHIVED_DATE,
895 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
896 ATTRIBUTE1 = X_ATTRIBUTE1,
897 ATTRIBUTE2 = X_ATTRIBUTE2,
898 ATTRIBUTE3 = X_ATTRIBUTE3,
899 ATTRIBUTE4 = X_ATTRIBUTE4,
900 ATTRIBUTE5 = X_ATTRIBUTE5,
901 ATTRIBUTE6 = X_ATTRIBUTE6,
902 ATTRIBUTE7 = X_ATTRIBUTE7,
903 ATTRIBUTE8 = X_ATTRIBUTE8,
904 ATTRIBUTE9 = X_ATTRIBUTE9,
905 ATTRIBUTE10 = X_ATTRIBUTE10,
906 ATTRIBUTE11 = X_ATTRIBUTE11,
907 ATTRIBUTE12 = X_ATTRIBUTE12,
908 ATTRIBUTE13 = X_ATTRIBUTE13,
909 ATTRIBUTE14 = X_ATTRIBUTE14,
910 ATTRIBUTE15 = X_ATTRIBUTE15,
911 TIMEZONE_ID = X_TIMEZONE_ID,
912 USER_ENTERED_START_TIME = X_USER_ENTERED_START_TIME,
913 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
914 REQUEST_ID = X_REQUEST_ID,
915 VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID,
916 LIST_USED_BY_ID = X_LIST_USED_BY_ID,
917 ARC_LIST_USED_BY = X_ARC_LIST_USED_BY,
921 GENERATION_TYPE = X_GENERATION_TYPE,
918 LIST_TYPE = X_LIST_TYPE,
919 STATUS_CODE = X_STATUS_CODE,
920 STATUS_DATE = X_STATUS_DATE,
922 REPEAT_EXCLUDE_TYPE = X_REPEAT_EXCLUDE_TYPE,
923 ROW_SELECTION_TYPE = X_ROW_SELECTION_TYPE,
924 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
925 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
926 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
927 NO_OF_ROWS_PREV_CONTACTED = X_NO_OF_ROWS_PREV_CONTACTED,
928 APPLY_TRAFFIC_COP = X_APPLY_TRAFFIC_COP
929 where LIST_HEADER_ID = X_LIST_HEADER_ID;
930
931 if (sql%notfound) then
932 raise no_data_found;
933 end if;
934
935 update AMS_LIST_HEADERS_ALL_TL set
936 LIST_NAME = X_LIST_NAME,
937 DESCRIPTION = X_DESCRIPTION,
938 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
939 LAST_UPDATE_BY = X_LAST_UPDATED_BY,
940 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
941 SOURCE_LANG = userenv('LANG')
942 where LIST_HEADER_ID = X_LIST_HEADER_ID
943 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
944
945 if (sql%notfound) then
946 raise no_data_found;
947 end if;
948 end UPDATE_ROW;
949
950 procedure DELETE_ROW (
951 X_LIST_HEADER_ID in NUMBER
952 ) is
953 begin
954 delete from AMS_LIST_HEADERS_ALL_TL
955 where LIST_HEADER_ID = X_LIST_HEADER_ID;
956
957 if (sql%notfound) then
958 raise no_data_found;
959 end if;
960
961 delete from AMS_LIST_HEADERS_ALL
962 where LIST_HEADER_ID = X_LIST_HEADER_ID;
963
964 if (sql%notfound) then
965 raise no_data_found;
966 end if;
967 end DELETE_ROW;
968
969 procedure ADD_LANGUAGE
970 is
971 begin
972 delete from AMS_LIST_HEADERS_ALL_TL T
973 where not exists
974 (select NULL
975 from AMS_LIST_HEADERS_ALL B
976 where B.LIST_HEADER_ID = T.LIST_HEADER_ID
977 );
978
979 update AMS_LIST_HEADERS_ALL_TL T set (
980 LIST_NAME,
981 DESCRIPTION
982 ) = (select
983 B.LIST_NAME,
984 B.DESCRIPTION
985 from AMS_LIST_HEADERS_ALL_TL B
986 where B.LIST_HEADER_ID = T.LIST_HEADER_ID
987 and B.LANGUAGE = T.SOURCE_LANG)
988 where (
989 T.LIST_HEADER_ID,
990 T.LANGUAGE
991 ) in (select
992 SUBT.LIST_HEADER_ID,
993 SUBT.LANGUAGE
994 from AMS_LIST_HEADERS_ALL_TL SUBB, AMS_LIST_HEADERS_ALL_TL SUBT
995 where SUBB.LIST_HEADER_ID = SUBT.LIST_HEADER_ID
996 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
997 and (SUBB.LIST_NAME <> SUBT.LIST_NAME
998 or (SUBB.LIST_NAME is null and SUBT.LIST_NAME is not null)
999 or (SUBB.LIST_NAME is not null and SUBT.LIST_NAME is null)
1000 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1001 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
1002 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
1003 ));
1004
1005 insert into AMS_LIST_HEADERS_ALL_TL (
1006 LIST_NAME,
1007 DESCRIPTION,
1008 LAST_UPDATE_LOGIN,
1009 LIST_HEADER_ID,
1010 LAST_UPDATE_DATE,
1011 LAST_UPDATE_BY,
1012 CREATION_DATE,
1013 CREATED_BY,
1014 LANGUAGE,
1015 SOURCE_LANG
1016 ) select /*+ ORDERED */
1017 B.LIST_NAME,
1018 B.DESCRIPTION,
1019 B.LAST_UPDATE_LOGIN,
1020 B.LIST_HEADER_ID,
1021 B.LAST_UPDATE_DATE,
1022 B.LAST_UPDATE_BY,
1023 B.CREATION_DATE,
1024 B.CREATED_BY,
1025 L.LANGUAGE_CODE,
1026 B.SOURCE_LANG
1027 from AMS_LIST_HEADERS_ALL_TL B, FND_LANGUAGES L
1028 where L.INSTALLED_FLAG in ('I', 'B')
1029 and B.LANGUAGE = userenv('LANG')
1030 and not exists
1031 (select NULL
1032 from AMS_LIST_HEADERS_ALL_TL T
1033 where T.LIST_HEADER_ID = B.LIST_HEADER_ID
1034 and T.LANGUAGE = L.LANGUAGE_CODE);
1035 end ADD_LANGUAGE;
1036
1037 procedure LOAD_ROW (
1038 P_LIST_HEADER_ID in NUMBER ,
1039 P_OWNER in VARCHAR2,
1040 P_COUNTRY in NUMBER,
1041 P_USER_STATUS_ID in NUMBER,
1042 P_QUANTUM in NUMBER,
1043 P_RELEASE_CONTROL_ALG_ID in NUMBER,
1044 P_DIALING_METHOD in VARCHAR2,
1045 P_CALLING_CALENDAR_ID in NUMBER,
1046 P_RELEASE_STRATEGY in VARCHAR2,
1047 P_CALLBACK_PRIORITY_FLAG in VARCHAR2,
1048 P_CALL_CENTER_READY_FLAG in VARCHAR2,
1049 P_CUSTOM_SETUP_ID in NUMBER,
1050 P_OWNER_USER_ID in NUMBER,
1051 P_ACCESS_LEVEL in VARCHAR2,
1052 P_ENABLE_LOG_FLAG in VARCHAR2,
1053 P_ENABLE_WORD_REPLACEMENT_FLAG in VARCHAR2,
1054 P_ENABLE_PARALLEL_DML_FLAG in VARCHAR2,
1055 P_DEDUPE_DURING_GENERATION_FLA in VARCHAR2,
1056 P_GENERATE_CONTROL_GROUP_FLAG in VARCHAR2,
1057 P_LAST_GENERATION_SUCCESS_FLAG in VARCHAR2,
1058 P_FORECASTED_START_DATE in DATE,
1059 P_FORECASTED_END_DATE in DATE,
1060 P_ACTUAL_END_DATE in DATE,
1061 P_SENT_OUT_DATE in DATE,
1062 P_DEDUPE_START_DATE in DATE,
1063 P_LAST_DEDUPE_DATE in DATE,
1064 P_LAST_DEDUPED_BY_USER_ID in NUMBER,
1065 P_WORKFLOW_ITEM_KEY in NUMBER,
1066 P_NO_OF_ROWS_DUPLICATES in NUMBER,
1067 P_NO_OF_ROWS_MIN_REQUESTED in NUMBER,
1068 P_NO_OF_ROWS_MAX_REQUESTED in NUMBER,
1069 P_NO_OF_ROWS_IN_LIST in NUMBER,
1070 P_NO_OF_ROWS_IN_CTRL_GROUP in NUMBER,
1071 P_NO_OF_ROWS_ACTIVE in NUMBER,
1075 P_NO_OF_ROWS_DO_NOT_MAIL in NUMBER,
1072 P_NO_OF_ROWS_INACTIVE in NUMBER,
1073 P_NO_OF_ROWS_MANUALLY_ENTERED in NUMBER,
1074 P_NO_OF_ROWS_DO_NOT_CALL in NUMBER,
1076 P_NO_OF_ROWS_RANDOM in NUMBER,
1077 P_MAIN_GEN_START_TIME in DATE,
1078 P_MAIN_GEN_END_TIME in DATE,
1079 P_MAIN_RANDOM_NTH_ROW_SELECTIO in NUMBER,
1080 P_MAIN_RANDOM_PCT_ROW_SELECTIO in NUMBER,
1081 P_CTRL_RANDOM_NTH_ROW_SELECTIO in NUMBER,
1082 P_CTRL_RANDOM_PCT_ROW_SELECTIO in NUMBER,
1083 P_REPEAT_SOURCE_LIST_HEADER_ID in VARCHAR2,
1084 P_RESULT_TEXT in VARCHAR2,
1085 P_KEYWORDS in VARCHAR2,
1086 P_LIST_PRIORITY in NUMBER,
1087 P_ASSIGN_PERSON_ID in NUMBER,
1088 P_LIST_SOURCE in VARCHAR2,
1089 P_LIST_SOURCE_TYPE in VARCHAR2,
1090 P_LIST_ONLINE_FLAG in VARCHAR2,
1091 P_RANDOM_LIST_ID in NUMBER,
1092 P_ENABLED_FLAG in VARCHAR2,
1093 P_ASSIGNED_TO in NUMBER,
1094 P_QUERY_ID in NUMBER,
1095 P_OWNER_PERSON_ID in NUMBER,
1096 P_ARCHIVED_BY in NUMBER,
1097 P_ARCHIVED_DATE in DATE,
1098 P_ATTRIBUTE_CATEGORY in VARCHAR2,
1099 P_ATTRIBUTE1 in VARCHAR2,
1100 P_ATTRIBUTE2 in VARCHAR2,
1101 P_ATTRIBUTE3 in VARCHAR2,
1102 P_ATTRIBUTE4 in VARCHAR2,
1103 P_ATTRIBUTE5 in VARCHAR2,
1104 P_ATTRIBUTE6 in VARCHAR2,
1105 P_ATTRIBUTE7 in VARCHAR2,
1106 P_ATTRIBUTE8 in VARCHAR2,
1107 P_ATTRIBUTE9 in VARCHAR2,
1108 P_ATTRIBUTE10 in VARCHAR2,
1109 P_ATTRIBUTE11 in VARCHAR2,
1110 P_ATTRIBUTE12 in VARCHAR2,
1111 P_ATTRIBUTE13 in VARCHAR2,
1112 P_ATTRIBUTE14 in VARCHAR2,
1113 P_ATTRIBUTE15 in VARCHAR2,
1114 P_TIMEZONE_ID in NUMBER,
1115 P_USER_ENTERED_START_TIME in DATE,
1116 P_OBJECT_VERSION_NUMBER in NUMBER,
1117 P_REQUEST_ID in NUMBER,
1118 P_VIEW_APPLICATION_ID in NUMBER,
1119 P_LIST_USED_BY_ID in NUMBER,
1120 P_ARC_LIST_USED_BY in VARCHAR2,
1121 P_LIST_TYPE in VARCHAR2,
1122 P_STATUS_CODE in VARCHAR2,
1123 P_STATUS_DATE in DATE,
1124 P_GENERATION_TYPE in VARCHAR2,
1125 P_REPEAT_EXCLUDE_TYPE in VARCHAR2,
1126 P_ROW_SELECTION_TYPE in VARCHAR2,
1127 P_LIST_NAME in VARCHAR2,
1128 P_DESCRIPTION in VARCHAR2,
1129 P_CREATION_DATE in DATE,
1130 P_CREATED_BY in NUMBER,
1131 P_LAST_UPDATE_DATE in DATE,
1132 P_LAST_UPDATED_BY in NUMBER,
1133 P_LAST_UPDATE_LOGIN in NUMBER,
1134 P_NO_OF_ROWS_PREV_CONTACTED in NUMBER,
1135 P_APPLY_TRAFFIC_COP in VARCHAR2,
1136 p_custom_mode in VARCHAR2
1137
1138 ) is
1139 l_user_id number := 0;
1140 l_imptype_id number;
1141 l_obj_verno number;
1142 l_dummy_char varchar2(1);
1143 l_row_id varchar2(100);
1144
1145 cursor c_chk_col_exists is
1146 select 'x'
1147 from ams_list_headers_all
1148 where list_header_id = p_list_header_id;
1149
1150 CURSOR c_obj_verno IS
1151 SELECT object_version_number, last_updated_by
1152 FROM ams_list_headers_all
1153 where list_header_id = p_list_header_id;
1154
1155
1156 l_init_msg_list varchar2(2000) := FND_API.G_FALSE;
1157 l_validation_level NUMBER := 100;
1158 l_commit varchar2(2000) := FND_API.G_TRUE;
1159 l_api_version number := 1.0;
1160 l_api_name constant varchar2(30) := 'load_row';
1161 l_return_status VARCHAR2(1); -- Return value from procedures
1162 l_msg_count number;
1163 x_list_header_id number;
1164 l_msg_data varchar2(500);
1165 l_cnt number := 0;
1166 l_msg_index NUMBER ;
1167 l_final_data varchar2(4000);
1168
1169 l_last_updated_by number;
1170
1171 BEGIN
1172 null;
1173 if p_OWNER = 'SEED' then
1174 l_user_id := 1;
1175 elsif p_OWNER = 'ORACLE' then
1176 l_user_id := 2;
1177 elsif p_OWNER = 'SYSADMIN' THEN
1178 l_user_id := 0;
1179
1180 end if;
1181
1182 open c_chk_col_exists;
1183 fetch c_chk_col_exists into l_dummy_char;
1184 if c_chk_col_exists%notfound then
1185 close c_chk_col_exists;
1186 AMS_LIST_HEADERS_ALL_PKG.INSERT_ROW(
1187 X_ROWID => l_row_id ,
1188 X_LIST_HEADER_ID => P_LIST_HEADER_ID ,
1189 X_COUNTRY => P_COUNTRY ,
1190 X_USER_STATUS_ID => P_USER_STATUS_ID ,
1191 X_QUANTUM => P_QUANTUM ,
1192 X_RELEASE_CONTROL_ALG_ID => P_RELEASE_CONTROL_ALG_ID ,
1193 X_DIALING_METHOD => P_DIALING_METHOD ,
1194 X_CALLING_CALENDAR_ID => P_CALLING_CALENDAR_ID ,
1195 X_RELEASE_STRATEGY => P_RELEASE_STRATEGY ,
1196 X_CALLBACK_PRIORITY_FLAG => P_CALLBACK_PRIORITY_FLAG ,
1197 X_CALL_CENTER_READY_FLAG => P_CALL_CENTER_READY_FLAG ,
1198 X_CUSTOM_SETUP_ID => P_CUSTOM_SETUP_ID ,
1199 X_OWNER_USER_ID => P_OWNER_USER_ID ,
1200 X_ACCESS_LEVEL => P_ACCESS_LEVEL ,
1201 X_ENABLE_LOG_FLAG => P_ENABLE_LOG_FLAG ,
1202 X_ENABLE_WORD_REPLACEMENT_FLAG => P_ENABLE_WORD_REPLACEMENT_FLAG ,
1203 X_ENABLE_PARALLEL_DML_FLAG => P_ENABLE_PARALLEL_DML_FLAG ,
1204 X_DEDUPE_DURING_GENERATION_FLA => P_DEDUPE_DURING_GENERATION_FLA ,
1205 X_GENERATE_CONTROL_GROUP_FLAG => P_GENERATE_CONTROL_GROUP_FLAG ,
1206 X_LAST_GENERATION_SUCCESS_FLAG => P_LAST_GENERATION_SUCCESS_FLAG ,
1207 X_FORECASTED_START_DATE => P_FORECASTED_START_DATE ,
1208 X_FORECASTED_END_DATE => P_FORECASTED_END_DATE ,
1212 X_LAST_DEDUPE_DATE => P_LAST_DEDUPE_DATE ,
1209 X_ACTUAL_END_DATE => P_ACTUAL_END_DATE ,
1210 X_SENT_OUT_DATE => P_SENT_OUT_DATE ,
1211 X_DEDUPE_START_DATE => P_DEDUPE_START_DATE ,
1213 X_LAST_DEDUPED_BY_USER_ID => P_LAST_DEDUPED_BY_USER_ID ,
1214 X_WORKFLOW_ITEM_KEY => P_WORKFLOW_ITEM_KEY ,
1215 X_NO_OF_ROWS_DUPLICATES => P_NO_OF_ROWS_DUPLICATES ,
1216 X_NO_OF_ROWS_MIN_REQUESTED => P_NO_OF_ROWS_MIN_REQUESTED ,
1217 X_NO_OF_ROWS_MAX_REQUESTED => P_NO_OF_ROWS_MAX_REQUESTED ,
1218 X_NO_OF_ROWS_IN_LIST => P_NO_OF_ROWS_IN_LIST ,
1219 X_NO_OF_ROWS_IN_CTRL_GROUP => P_NO_OF_ROWS_IN_CTRL_GROUP ,
1220 X_NO_OF_ROWS_ACTIVE => P_NO_OF_ROWS_ACTIVE ,
1221 X_NO_OF_ROWS_INACTIVE => P_NO_OF_ROWS_INACTIVE ,
1222 X_NO_OF_ROWS_MANUALLY_ENTERED => P_NO_OF_ROWS_MANUALLY_ENTERED ,
1223 X_NO_OF_ROWS_DO_NOT_CALL => P_NO_OF_ROWS_DO_NOT_CALL ,
1224 X_NO_OF_ROWS_DO_NOT_MAIL => P_NO_OF_ROWS_DO_NOT_MAIL ,
1225 X_NO_OF_ROWS_RANDOM => P_NO_OF_ROWS_RANDOM ,
1226 X_MAIN_GEN_START_TIME => P_MAIN_GEN_START_TIME ,
1227 X_MAIN_GEN_END_TIME => P_MAIN_GEN_END_TIME ,
1228 X_MAIN_RANDOM_NTH_ROW_SELECTIO => P_MAIN_RANDOM_NTH_ROW_SELECTIO ,
1229 X_MAIN_RANDOM_PCT_ROW_SELECTIO => P_MAIN_RANDOM_PCT_ROW_SELECTIO ,
1230 X_CTRL_RANDOM_NTH_ROW_SELECTIO => P_CTRL_RANDOM_NTH_ROW_SELECTIO ,
1231 X_CTRL_RANDOM_PCT_ROW_SELECTIO => P_CTRL_RANDOM_PCT_ROW_SELECTIO ,
1232 X_REPEAT_SOURCE_LIST_HEADER_ID => P_REPEAT_SOURCE_LIST_HEADER_ID ,
1233 X_RESULT_TEXT => P_RESULT_TEXT ,
1234 X_KEYWORDS => P_KEYWORDS ,
1235 X_LIST_PRIORITY => P_LIST_PRIORITY ,
1236 X_ASSIGN_PERSON_ID => P_ASSIGN_PERSON_ID ,
1237 X_LIST_SOURCE => P_LIST_SOURCE ,
1238 X_LIST_SOURCE_TYPE => P_LIST_SOURCE_TYPE ,
1239 X_LIST_ONLINE_FLAG => P_LIST_ONLINE_FLAG ,
1240 X_RANDOM_LIST_ID => P_RANDOM_LIST_ID ,
1241 X_ENABLED_FLAG => P_ENABLED_FLAG ,
1242 X_ASSIGNED_TO => P_ASSIGNED_TO ,
1243 X_QUERY_ID => P_QUERY_ID ,
1244 X_OWNER_PERSON_ID => P_OWNER_PERSON_ID ,
1245 X_ARCHIVED_BY => P_ARCHIVED_BY ,
1246 X_ARCHIVED_DATE => P_ARCHIVED_DATE ,
1247 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
1248 X_ATTRIBUTE1 => P_ATTRIBUTE1 ,
1249 X_ATTRIBUTE2 => P_ATTRIBUTE2 ,
1250 X_ATTRIBUTE3 => P_ATTRIBUTE3 ,
1251 X_ATTRIBUTE4 => P_ATTRIBUTE4 ,
1252 X_ATTRIBUTE5 => P_ATTRIBUTE5 ,
1253 X_ATTRIBUTE6 => P_ATTRIBUTE6 ,
1254 X_ATTRIBUTE7 => P_ATTRIBUTE7 ,
1255 X_ATTRIBUTE8 => P_ATTRIBUTE8 ,
1256 X_ATTRIBUTE9 => P_ATTRIBUTE9 ,
1257 X_ATTRIBUTE10 => P_ATTRIBUTE10 ,
1258 X_ATTRIBUTE11 => P_ATTRIBUTE11 ,
1259 X_ATTRIBUTE12 => P_ATTRIBUTE12 ,
1260 X_ATTRIBUTE13 => P_ATTRIBUTE13 ,
1261 X_ATTRIBUTE14 => P_ATTRIBUTE14 ,
1262 X_ATTRIBUTE15 => P_ATTRIBUTE15 ,
1263 X_TIMEZONE_ID => P_TIMEZONE_ID ,
1264 X_USER_ENTERED_START_TIME => P_USER_ENTERED_START_TIME ,
1265 X_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ,
1266 X_REQUEST_ID => P_REQUEST_ID ,
1267 X_VIEW_APPLICATION_ID => P_VIEW_APPLICATION_ID ,
1268 X_LIST_USED_BY_ID => P_LIST_USED_BY_ID ,
1269 X_ARC_LIST_USED_BY => P_ARC_LIST_USED_BY ,
1270 X_LIST_TYPE => P_LIST_TYPE ,
1271 X_STATUS_CODE => P_STATUS_CODE ,
1272 X_STATUS_DATE => P_STATUS_DATE ,
1273 X_GENERATION_TYPE => P_GENERATION_TYPE ,
1274 X_REPEAT_EXCLUDE_TYPE => P_REPEAT_EXCLUDE_TYPE ,
1275 X_ROW_SELECTION_TYPE => P_ROW_SELECTION_TYPE ,
1276 X_LIST_NAME => P_LIST_NAME ,
1277 X_DESCRIPTION => P_DESCRIPTION ,
1278 X_CREATION_DATE => P_CREATION_DATE ,
1279 X_CREATED_BY => P_CREATED_BY ,
1280 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE ,
1281 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY ,
1282 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1283 X_NO_OF_ROWS_PREV_CONTACTED => P_NO_OF_ROWS_PREV_CONTACTED,
1284 X_APPLY_TRAFFIC_COP => P_APPLY_TRAFFIC_COP );
1285 else
1286 close c_chk_col_exists;
1287
1288
1289 OPEN c_obj_verno;
1290 FETCH c_obj_verno INTO l_obj_verno ,l_last_updated_by;
1291 CLOSE c_obj_verno;
1292
1293
1294 if (l_last_updated_by in (1,2,0) OR
1295 NVL(p_custom_mode,'PRESERVE')='FORCE') THEN
1296
1297
1298 AMS_LIST_HEADERS_ALL_PKG.UPDATE_ROW (
1299 X_LIST_HEADER_ID => P_LIST_HEADER_ID ,
1300 X_COUNTRY => P_COUNTRY ,
1301 X_USER_STATUS_ID => P_USER_STATUS_ID ,
1302 X_QUANTUM => P_QUANTUM ,
1303 X_RELEASE_CONTROL_ALG_ID => P_RELEASE_CONTROL_ALG_ID ,
1304 X_DIALING_METHOD => P_DIALING_METHOD ,
1305 X_CALLING_CALENDAR_ID => P_CALLING_CALENDAR_ID ,
1306 X_RELEASE_STRATEGY => P_RELEASE_STRATEGY ,
1307 X_CALLBACK_PRIORITY_FLAG => P_CALLBACK_PRIORITY_FLAG ,
1308 X_CALL_CENTER_READY_FLAG => P_CALL_CENTER_READY_FLAG ,
1309 X_CUSTOM_SETUP_ID => P_CUSTOM_SETUP_ID ,
1310 X_OWNER_USER_ID => P_OWNER_USER_ID ,
1311 X_ACCESS_LEVEL => P_ACCESS_LEVEL ,
1312 X_ENABLE_LOG_FLAG => P_ENABLE_LOG_FLAG ,
1313 X_ENABLE_WORD_REPLACEMENT_FLAG => P_ENABLE_WORD_REPLACEMENT_FLAG ,
1317 X_LAST_GENERATION_SUCCESS_FLAG => P_LAST_GENERATION_SUCCESS_FLAG ,
1314 X_ENABLE_PARALLEL_DML_FLAG => P_ENABLE_PARALLEL_DML_FLAG ,
1315 X_DEDUPE_DURING_GENERATION_FLA => P_DEDUPE_DURING_GENERATION_FLA ,
1316 X_GENERATE_CONTROL_GROUP_FLAG => P_GENERATE_CONTROL_GROUP_FLAG ,
1318 X_FORECASTED_START_DATE => P_FORECASTED_START_DATE ,
1319 X_FORECASTED_END_DATE => P_FORECASTED_END_DATE ,
1320 X_ACTUAL_END_DATE => P_ACTUAL_END_DATE ,
1321 X_SENT_OUT_DATE => P_SENT_OUT_DATE ,
1322 X_DEDUPE_START_DATE => P_DEDUPE_START_DATE ,
1323 X_LAST_DEDUPE_DATE => P_LAST_DEDUPE_DATE ,
1324 X_LAST_DEDUPED_BY_USER_ID => P_LAST_DEDUPED_BY_USER_ID ,
1325 X_WORKFLOW_ITEM_KEY => P_WORKFLOW_ITEM_KEY ,
1326 X_NO_OF_ROWS_DUPLICATES => P_NO_OF_ROWS_DUPLICATES ,
1327 X_NO_OF_ROWS_MIN_REQUESTED => P_NO_OF_ROWS_MIN_REQUESTED ,
1328 X_NO_OF_ROWS_MAX_REQUESTED => P_NO_OF_ROWS_MAX_REQUESTED ,
1329 X_NO_OF_ROWS_IN_LIST => P_NO_OF_ROWS_IN_LIST ,
1330 X_NO_OF_ROWS_IN_CTRL_GROUP => P_NO_OF_ROWS_IN_CTRL_GROUP ,
1331 X_NO_OF_ROWS_ACTIVE => P_NO_OF_ROWS_ACTIVE ,
1332 X_NO_OF_ROWS_INACTIVE => P_NO_OF_ROWS_INACTIVE ,
1333 X_NO_OF_ROWS_MANUALLY_ENTERED => P_NO_OF_ROWS_MANUALLY_ENTERED ,
1334 X_NO_OF_ROWS_DO_NOT_CALL => P_NO_OF_ROWS_DO_NOT_CALL ,
1335 X_NO_OF_ROWS_DO_NOT_MAIL => P_NO_OF_ROWS_DO_NOT_MAIL ,
1336 X_NO_OF_ROWS_RANDOM => P_NO_OF_ROWS_RANDOM ,
1337 X_MAIN_GEN_START_TIME => P_MAIN_GEN_START_TIME ,
1338 X_MAIN_GEN_END_TIME => P_MAIN_GEN_END_TIME ,
1339 X_MAIN_RANDOM_NTH_ROW_SELECTIO => P_MAIN_RANDOM_NTH_ROW_SELECTIO ,
1340 X_MAIN_RANDOM_PCT_ROW_SELECTIO => P_MAIN_RANDOM_PCT_ROW_SELECTIO ,
1341 X_CTRL_RANDOM_NTH_ROW_SELECTIO => P_CTRL_RANDOM_NTH_ROW_SELECTIO ,
1342 X_CTRL_RANDOM_PCT_ROW_SELECTIO => P_CTRL_RANDOM_PCT_ROW_SELECTIO ,
1343 X_REPEAT_SOURCE_LIST_HEADER_ID => P_REPEAT_SOURCE_LIST_HEADER_ID ,
1344 X_RESULT_TEXT => P_RESULT_TEXT ,
1345 X_KEYWORDS => P_KEYWORDS ,
1346 X_LIST_PRIORITY => P_LIST_PRIORITY ,
1347 X_ASSIGN_PERSON_ID => P_ASSIGN_PERSON_ID ,
1348 X_LIST_SOURCE => P_LIST_SOURCE ,
1349 X_LIST_SOURCE_TYPE => P_LIST_SOURCE_TYPE ,
1350 X_LIST_ONLINE_FLAG => P_LIST_ONLINE_FLAG ,
1351 X_RANDOM_LIST_ID => P_RANDOM_LIST_ID ,
1352 X_ENABLED_FLAG => P_ENABLED_FLAG ,
1353 X_ASSIGNED_TO => P_ASSIGNED_TO ,
1354 X_QUERY_ID => P_QUERY_ID ,
1355 X_OWNER_PERSON_ID => P_OWNER_PERSON_ID ,
1356 X_ARCHIVED_BY => P_ARCHIVED_BY ,
1357 X_ARCHIVED_DATE => P_ARCHIVED_DATE ,
1358 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
1359 X_ATTRIBUTE1 => P_ATTRIBUTE1 ,
1360 X_ATTRIBUTE2 => P_ATTRIBUTE2 ,
1361 X_ATTRIBUTE3 => P_ATTRIBUTE3 ,
1362 X_ATTRIBUTE4 => P_ATTRIBUTE4 ,
1363 X_ATTRIBUTE5 => P_ATTRIBUTE5 ,
1364 X_ATTRIBUTE6 => P_ATTRIBUTE6 ,
1365 X_ATTRIBUTE7 => P_ATTRIBUTE7 ,
1366 X_ATTRIBUTE8 => P_ATTRIBUTE8 ,
1367 X_ATTRIBUTE9 => P_ATTRIBUTE9 ,
1368 X_ATTRIBUTE10 => P_ATTRIBUTE10 ,
1369 X_ATTRIBUTE11 => P_ATTRIBUTE11 ,
1370 X_ATTRIBUTE12 => P_ATTRIBUTE12 ,
1371 X_ATTRIBUTE13 => P_ATTRIBUTE13 ,
1372 X_ATTRIBUTE14 => P_ATTRIBUTE14 ,
1373 X_ATTRIBUTE15 => P_ATTRIBUTE15 ,
1374 X_TIMEZONE_ID => P_TIMEZONE_ID ,
1375 X_USER_ENTERED_START_TIME => P_USER_ENTERED_START_TIME ,
1376 X_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ,
1377 X_REQUEST_ID => P_REQUEST_ID ,
1378 X_VIEW_APPLICATION_ID => P_VIEW_APPLICATION_ID ,
1379 X_LIST_USED_BY_ID => P_LIST_USED_BY_ID ,
1380 X_ARC_LIST_USED_BY => P_ARC_LIST_USED_BY ,
1381 X_LIST_TYPE => P_LIST_TYPE ,
1382 X_STATUS_CODE => P_STATUS_CODE ,
1383 X_STATUS_DATE => P_STATUS_DATE ,
1384 X_GENERATION_TYPE => P_GENERATION_TYPE ,
1385 X_REPEAT_EXCLUDE_TYPE => P_REPEAT_EXCLUDE_TYPE ,
1386 X_ROW_SELECTION_TYPE => P_ROW_SELECTION_TYPE ,
1387 X_LIST_NAME => P_LIST_NAME ,
1388 X_DESCRIPTION => P_DESCRIPTION ,
1389 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE ,
1390 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY ,
1391 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
1392 X_NO_OF_ROWS_PREV_CONTACTED => P_NO_OF_ROWS_PREV_CONTACTED,
1393 X_APPLY_TRAFFIC_COP => P_APPLY_TRAFFIC_COP
1394 );
1395 end if;
1396 end if;
1397 END LOAD_ROW;
1398
1399 PROCEDURE translate_row(
1400 x_list_header_id in number,
1401 x_list_name in varchar2,
1402 x_description in varchar2,
1403 x_owner in varchar2,
1404 x_custom_mode in varchar2
1405 ) is
1406
1407 cursor c_last_update_by is
1408 select last_update_by
1409 from ams_list_headers_all_tl
1410 where list_header_id = x_list_header_id
1411 and userenv('LANG') = language;
1412
1413 l_last_update_by number;
1414
1415 begin
1416 open c_last_update_by;
1417 fetch c_last_update_by into l_last_update_by;
1418 close c_last_update_by;
1419
1420 if (l_last_update_by in (1,2,0) OR nvl(x_custom_mode,'PRESERVE')='FORCE') THEN
1421 update ams_list_headers_all_tl
1422 set list_name = nvl(x_list_name, list_name),
1423 description = nvl(x_description,description),
1424 source_lang = userenv('LANG'),
1425 last_update_date = sysdate,
1426 last_update_by = decode(x_owner, 'SEED', 1, 'ORACLE',2, 'SYSADMIN',0, -1),
1427 last_update_login = 0
1428 where list_header_id = x_list_header_id
1429 and userenv('LANG') in (language, source_lang);
1430 end if;
1431
1432 end TRANSLATE_ROW;
1433
1434 end AMS_LIST_HEADERS_ALL_PKG;