[Home] [Help]
PACKAGE BODY: APPS.AMS_LISTHEADER_PVT
Source
1 PACKAGE BODY AMS_ListHeader_PVT as
2 /* $Header: amsvlshb.pls 120.4 2005/12/14 05:30:55 bmuthukr ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_ListHeader_PVT
7 --
8 -- PURPOSE
9 -- Private API for Oracle Marketing(AMS) List Headers
10 --
11 -- Procedures:
12
13 -- Create_ListHeader
14 -- Update_ListHeader
15 -- Delete_ListHeader
16 -- Lock_ListHeader
17
18 -- Validate_ListHeader
19 -- Validate_List_Record
20 -- Validate_List_Items
21 -- Validate_ListStatus
22
23 -- Check_List_Req_Items
24 -- Check_List_uk_items
25 -- Check_List_fk_items
26 -- Check_List_lookup_items
27 -- Check_List_flag_items
28
29 -- Complete_ListHeader_rec
30 -- Init_ListHeader_rec
31 -- Update_Prev_contacted_count
32
33 -- HISTORY
34 -- 05/12/1999 tdonohoe created
35 -- 01/03/2001 gjoby Added validations, for list header type,
36 -- source type, moved the name and description
37 -- to tl tables, cue cards validation
38
39 -- End of Comments
40
41 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_ListHeader_PVT';
42 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvlshb.pls';
43
44 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
45 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
46 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
47
48 -- Start of Comments
49 --
50 -- NAME
51 -- Validate_ListStatus
52 --
53 -- PURPOSE
54 -- This Function validates the changing of a list status.
55 -- NOTES
56 --
57 --
58 -- HISTORY
59 -- 10/13/1999 tdonohoe created.
60 -- 06/30/2000 tdonohoe commented out TEMPLATE list restriction.
61 -- 06/30/2000 tdonohoe commented out status check as there are no STATUS rules in AMS_STATUS_ORDER_RULES.
62 -- 01/18/2001 gjoby check ams_lookups for ams_list_status
63 -- End of Comments
64
65
66
67 PROCEDURE Validate_ListStatus(p_user_status_id IN NUMBER,
68 x_system_status_code OUT NOCOPY VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2) IS
70
71 l_meaning varchar2(80);
72 CURSOR c_system_status_code is
73 SELECT system_status_code
74 FROM ams_user_statuses_vl
75 WHERE user_status_id = p_user_status_id ;
76 BEGIN
77 -- Initialize API/Procedure return status to success
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79 open c_system_status_code ;
80 fetch c_system_status_code into x_system_status_code;
81 if c_system_status_code%notfound then
82 x_return_status := FND_API.g_ret_sts_error;
83 end if;
84 close c_system_status_code ;
85 END Validate_ListStatus;
86
87 ---------------------------------------------------------------------
88 -- PROCEDURE
89 -- check_list_flag_items
90 --
91 -- HISTORY
92 -- 10/12/99 tdonohoe Created.
93 ---------------------------------------------------------------------
94 PROCEDURE check_list_flag_items(
95 p_listheader_rec IN list_header_rec_type,
96 x_return_status OUT NOCOPY VARCHAR2)
97 IS
98 BEGIN
99
100 x_return_status := FND_API.g_ret_sts_success;
101
102 ----------------------- ENABLE_LOG_FLAG ------------------------
103 IF p_listheader_rec.ENABLE_LOG_FLAG <> FND_API.g_miss_char
104 AND p_listheader_rec.ENABLE_LOG_FLAG IS NOT NULL THEN
105 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.ENABLE_LOG_FLAG) = FND_API.g_false THEN
106 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
107 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_LOG_FLAG');
108 FND_MSG_PUB.add;
109 END IF;
110 x_return_status := FND_API.g_ret_sts_error;
111 RETURN;
112 END IF;
113 END IF;
114
115 ----------------------- ENABLE_WORD_REPLACEMENT_FLAG------------------------
116 IF p_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG <> FND_API.g_miss_char
117 AND p_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG IS NOT NULL THEN
118 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG) = FND_API.g_false THEN
119 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
120 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_WORD_FLAG');
121 FND_MSG_PUB.add;
122 END IF;
123
124 x_return_status := FND_API.g_ret_sts_error;
125 RETURN;
126 END IF;
127 END IF;
128
129 ----------------------- ENABLE_PARALLEL_DML_FLAG------------------------
130 IF p_listheader_rec.ENABLE_PARALLEL_DML_FLAG <> FND_API.g_miss_char
131 AND p_listheader_rec.ENABLE_PARALLEL_DML_FLAG IS NOT NULL THEN
132 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.ENABLE_PARALLEL_DML_FLAG) = FND_API.g_false THEN
133 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
134 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_DML_FLAG');
135 FND_MSG_PUB.add;
136 END IF;
137
138 x_return_status := FND_API.g_ret_sts_error;
139 RETURN;
140 END IF;
141 END IF;
142
143 ----------------------- DEDUPE_DURING_GENERATION_FLAG ------------------------
144 IF p_listheader_rec.DEDUPE_DURING_GENERATION_FLAG <> FND_API.g_miss_char
145 AND p_listheader_rec.DEDUPE_DURING_GENERATION_FLAG IS NOT NULL THEN
146 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.DEDUPE_DURING_GENERATION_FLAG) = FND_API.g_false THEN
147 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
148 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_DEDUPE_FLAG');
149 FND_MSG_PUB.add;
150 END IF;
151
152 x_return_status := FND_API.g_ret_sts_error;
153 RETURN;
154 END IF;
155 END IF;
156
157 ----------------------- GENERATE_CONTROL_GROUP_FLAG ------------------------
158 IF p_listheader_rec.GENERATE_CONTROL_GROUP_FLAG <> FND_API.g_miss_char
159 AND p_listheader_rec.GENERATE_CONTROL_GROUP_FLAG IS NOT NULL THEN
160 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.GENERATE_CONTROL_GROUP_FLAG) = FND_API.g_false
161 THEN
162 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
163 THEN
164 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_CONTROL_FLAG');
165 FND_MSG_PUB.add;
166 END IF;
167
168 x_return_status := FND_API.g_ret_sts_error;
169 RETURN;
170 END IF;
171 END IF;
172
173 --------------- LAST_GENERATION_SUCCESS_FLAG ------------------------
174
175 IF p_listheader_rec.LAST_GENERATION_SUCCESS_FLAG <> FND_API.g_miss_char
176 AND p_listheader_rec.LAST_GENERATION_SUCCESS_FLAG IS NOT NULL
177 THEN
178 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.LAST_GENERATION_SUCCESS_FLAG ) = FND_API.g_false
179 THEN
180 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
181 THEN
182 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_GEN_FLAG');
183 FND_MSG_PUB.add;
184 END IF;
185
186 x_return_status := FND_API.g_ret_sts_error;
187 RETURN;
188 END IF;
189 END IF;
190
191
192 --------------- LAST_GENERATION_SUCCESS_FLAG ------------------------
193
194 IF p_listheader_rec.ENABLED_FLAG <> FND_API.g_miss_char
195 AND p_listheader_rec.ENABLED_FLAG IS NOT NULL
196 THEN
197 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.ENABLED_FLAG ) = FND_API.g_false
198 THEN
199 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
200 THEN
201 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_ENABLED_FLAG');
202 FND_MSG_PUB.add;
203 END IF;
204
205 x_return_status := FND_API.g_ret_sts_error;
206 RETURN;
207 END IF;
208 END IF;
209
210
211 --------------- PURGE_FLAG ------------------------
212
213 IF p_listheader_rec.PURGE_FLAG <> FND_API.g_miss_char
214 AND p_listheader_rec.PURGE_FLAG IS NOT NULL
215 THEN
216 IF AMS_Utility_PVT.is_Y_or_N(p_listheader_rec.PURGE_FLAG ) = FND_API.g_false
217 THEN
218 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
219 THEN
220 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_PURGE_FLAG');
221 FND_MSG_PUB.add;
222 END IF;
223
224 x_return_status := FND_API.g_ret_sts_error;
225 RETURN;
226 END IF;
227 END IF;
228 -- check other flags
229
230 END check_list_flag_items;
231
232 ---------------------------------------------------------------------
233 -- PROCEDURE
234 -- check_list_lookup_items
235 --
236 -- HISTORY
237 -- 10/12/99 tdonohoe Created.
238 ---------------------------------------------------------------------
239 PROCEDURE check_list_lookup_items(
240 p_listheader_rec IN list_header_rec_type,
241 x_return_status OUT NOCOPY VARCHAR2
242 )
243 IS
244 x_status_code varchar2(80);
245 l_valid_flag varchar2(1) := 'N';
246 cursor c_check_source_type(cur_list_source_type varchar2) is
247 select 'Y'
248 from ams_list_src_types
249 where list_source_type in ('TARGET','ANALYTICS')
250 and source_type_code = cur_list_source_type
251 and master_source_type_flag = 'Y'
252 and enabled_flag = 'Y' ;
253 BEGIN
254 x_return_status := FND_API.g_ret_sts_success;
255
256
257 ----------------------- LIST STATUS ORDER RULES -----------------------
258
259 IF p_listheader_rec.user_status_id <> FND_API.g_miss_num
260 AND p_listheader_rec.user_status_id IS NOT NULL THEN
261 Validate_ListStatus(p_user_status_id => p_listheader_rec.user_status_id,
262 x_system_status_code => x_status_code,
263 x_return_status => x_return_status) ;
264
265 if x_return_status = FND_API.g_ret_sts_error THEN
266 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
267 FND_MESSAGE.set_name('AMS', 'AMS_LIST_STATUS_INVALID');
268 FND_MSG_PUB.add;
269 END IF;
270 RETURN;
271 end if;
272 END IF;
273
274
275 ----------------------- STATUS_CODE ------------------------
276 IF p_listheader_rec.status_code <> FND_API.g_miss_char
277 AND p_listheader_rec.status_code IS NOT NULL THEN
278 IF AMS_Utility_PVT.check_lookup_exists(
279 p_lookup_type => 'AMS_LIST_STATUS',
280 p_lookup_code => p_listheader_rec.status_code
281 ) = FND_API.g_false THEN
282 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
283 FND_MESSAGE.set_name('AMS', 'AMS_LIST_STATUS_INVALID');
284 FND_MSG_PUB.add;
285 END IF;
286
287 x_return_status := FND_API.g_ret_sts_error;
288 RETURN;
289 END IF;
290 END IF;
291
292 ----------------------- GENERATION_TYPE ------------------------
293 IF p_listheader_rec.generation_type <> FND_API.g_miss_char
294 AND p_listheader_rec.generation_type IS NOT NULL THEN
295
296 IF AMS_Utility_PVT.check_lookup_exists(
297 p_lookup_type => 'AMS_LIST_GENERATION_TYPE',
298 p_lookup_code => p_listheader_rec.generation_type
299 ) = FND_API.g_false THEN
300 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
301 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_GEN_TYPE');
302 FND_MSG_PUB.add;
303 END IF;
304 x_return_status := FND_API.g_ret_sts_error;
305 RETURN;
306 END IF;
307 END IF;
308
309 ----------------------- ROW_SELECTION_TYPE -------------------------
310 IF p_listheader_rec.row_selection_type <> FND_API.g_miss_char
311 AND p_listheader_rec.row_selection_type IS NOT NULL THEN
312 IF AMS_Utility_PVT.check_lookup_exists(
313 p_lookup_type => 'AMS_LIST_ROW_SELECT_TYPE',
314 p_lookup_code => p_listheader_rec.row_selection_type
315 ) = FND_API.g_false THEN
316 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
317 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_ROW_SELECT_TYPE');
318 FND_MSG_PUB.add;
319 END IF;
320 x_return_status := FND_API.g_ret_sts_error;
321 RETURN;
322 END IF;
323 END IF;
324
325 ----------------------- LIST TYPE ------------------------
326 IF p_listheader_rec.list_type <> FND_API.g_miss_char
327 AND p_listheader_rec.list_type IS NOT NULL THEN
328 IF AMS_Utility_PVT.check_lookup_exists(
329 p_lookup_type => 'AMS_LIST_TYPE',
330 p_lookup_code => p_listheader_rec.list_type
331 ) = FND_API.g_false THEN
332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
333 FND_MESSAGE.set_name('AMS', 'AMS_LIST_TYPE_INVALID');
334 FND_MSG_PUB.add;
335 END IF;
336
337 x_return_status := FND_API.g_ret_sts_error;
338 RETURN;
339 END IF;
340 END IF;
341
342 ----------------------- LIST SOURCE TYPE ------------------------
343 -- validate if list type is only std. -- for telesales requirement
344 IF p_listheader_rec.list_type = 'STANDARD' then
345 IF p_listheader_rec.list_source_type <> FND_API.g_miss_char
346 AND p_listheader_rec.list_source_type IS NOT NULL THEN
347 IF p_listheader_rec.list_source_type <> 'EMPLOYEE_LIST' then
348 open c_check_source_type(p_listheader_rec.list_source_type );
349 fetch c_check_source_type into l_valid_flag;
350 close c_check_source_type;
351 IF l_valid_flag = 'N' THEN
352 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
353 FND_MESSAGE.set_name('AMS', 'AMS_LIST_SOURCE_TYPE_INVALID');
354 FND_MSG_PUB.add;
355 END IF;
356
357 x_return_status := FND_API.g_ret_sts_error;
358 RETURN;
359 END IF;
360 END IF;
361 END IF;
362 END IF;
363
364 ----------------------- PURPOSE_CODE ------------------------
365 IF p_listheader_rec.purpose_code <> FND_API.g_miss_char
366 AND p_listheader_rec.purpose_code IS NOT NULL THEN
367 IF AMS_Utility_PVT.check_lookup_exists(
368 p_lookup_type => 'AMS_ACTIVITY_PURPOSES',
369 p_lookup_code => p_listheader_rec.purpose_code
370 ) = FND_API.g_false THEN
371 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
372 FND_MESSAGE.set_name('AMS', 'AMS_LIST_PURPOSE_INVALID');
373 FND_MSG_PUB.add;
374 END IF;
375
376 x_return_status := FND_API.g_ret_sts_error;
377 RETURN;
378 END IF;
379 END IF;
380
381 END check_list_lookup_items;
382
383 ---------------------------------------------------------------------
384 -- PROCEDURE
385 -- check_list_fk_items
386 --
387 -- HISTORY
388 -- 10/12/99 tdonohoe Created.
389 -- 04/24/2000 sugupta modified added fk validation for timezone
390 ---------------------------------------------------------------------
391 PROCEDURE check_list_fk_items(
392 p_listheader_rec IN list_header_rec_type,
393 x_return_status OUT NOCOPY VARCHAR2
394 )
395 IS
396 BEGIN
397
398 x_return_status := FND_API.g_ret_sts_success;
399
400 ----------------------- TIMEZONE_ID ------------------------
401 IF p_listheader_rec.timezone_id <> FND_API.g_miss_num
405 'upgrade_tz_id',
402 AND p_listheader_rec.timezone_id IS NOT NULL THEN
403 IF AMS_Utility_PVT.check_fk_exists(
404 'fnd_timezones_b',
406 p_listheader_rec.timezone_id
407 ) = FND_API.g_false THEN
408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
409 THEN
410 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_TIMEZONE_ID');
411 FND_MSG_PUB.add;
412 END IF;
413
414 x_return_status := FND_API.g_ret_sts_error;
415 RETURN;
416 END IF;
417 END IF;
418 IF p_listheader_rec.owner_user_id <> FND_API.g_miss_num THEN
419 IF AMS_Utility_PVT.check_fk_exists(
420 'jtf_rs_resource_extns',
421 'resource_id',
422 p_listheader_rec.owner_user_id
423 ) = FND_API.g_false
424 THEN
425 AMS_Utility_PVT.Error_Message('AMS_LIST_BAD_OWNER_USER_ID');
426 x_return_status := FND_API.g_ret_sts_error;
427 RETURN;
428 END IF;
429 END IF;
430
431 --------------------- view application_id ------------------------
432 IF p_listheader_rec.view_application_id <> FND_API.g_miss_num THEN
433 IF AMS_Utility_PVT.check_fk_exists(
434 'fnd_application',
435 'application_id',
436 p_listheader_rec.view_application_id
437 ) = FND_API.g_false
438 THEN
439 AMS_Utility_PVT.Error_Message('AMS_LIST_BAD_APPLICATION_ID');
440 x_return_status := FND_API.g_ret_sts_error;
441 RETURN;
442 END IF;
443 END IF;
444
445 --------------------- program application_id ------------------------
446 IF p_listheader_rec.program_application_id <> FND_API.g_miss_num THEN
447 IF AMS_Utility_PVT.check_fk_exists(
448 'fnd_application',
449 'application_id',
450 p_listheader_rec.program_application_id
451 ) = FND_API.g_false
452 THEN
453 AMS_Utility_PVT.Error_Message('AMS_LIST_BAD_APPLICATION_ID');
454 x_return_status := FND_API.g_ret_sts_error;
455 RETURN;
456 END IF;
457 END IF;
458
459 END check_list_fk_items;
460
461
462 ---------------------------------------------------------------------
463 -- PROCEDURE
464 -- check_list_uk_items
465 --
466 -- HISTORY
467 -- 10/12/99 tdonohoe Created.
468 ---------------------------------------------------------------------
469 PROCEDURE check_list_uk_items(
470 p_listheader_rec IN list_header_rec_type,
471 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
472 x_return_status OUT NOCOPY VARCHAR2
473 )
474 IS
475 l_valid_flag VARCHAR2(1);
476 l_dummy_flag varchar2(1) := FND_API.g_true ;
477 cursor c_check_create_mode is
478 select FND_API.g_false
479 from ams_list_headers_vl
480 where list_name = p_listheader_rec.list_name
481 and view_application_id not in (522,691);
482 cursor c_check_update_mode is
483 select FND_API.g_false
484 from ams_list_headers_vl
485 where list_name = p_listheader_rec.list_name
486 and list_header_id <> p_listheader_rec.list_header_id
487 and view_application_id not in (522,691);
488 cursor c_check_update_mode_02 is
489 select FND_API.g_false
490 from ams_list_headers_vl
491 where list_name = p_listheader_rec.list_name
492 and list_header_id <> p_listheader_rec.list_header_id
493 and nvl(purge_flag,'N') = 'N'
494 and view_application_id in (522,691)
495 and owner_user_id = p_listheader_rec.owner_user_id ;
496 cursor c_check_create_mode_02 is
497 select FND_API.g_false
498 from ams_list_headers_vl
499 where list_name = p_listheader_rec.list_name
500 and nvl(purge_flag,'N') = 'N'
501 and view_application_id in (522,691)
502 and owner_user_id = p_listheader_rec.owner_user_id ;
503 BEGIN
504
505 -- Initialize API/Procedure return status to success
506 x_return_status := FND_API.g_ret_sts_success;
507
508 -----------------------------------------------------------------
509 -- For create_listheader, when list_header_id is passed in,
510 -- we need to check if this list_header_id is unique.
511 -----------------------------------------------------------------
512 IF ( p_validation_mode = JTF_PLSQL_API.g_create
513 AND p_listheader_rec.list_header_id IS NOT NULL) THEN
514
515 IF AMS_Utility_PVT.check_uniqueness(
516 'ams_list_headers_all',
517 'list_header_id = ' || p_listheader_rec.list_header_id
518 ) = FND_API.g_false
519 THEN
520 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
521 THEN
522 FND_MESSAGE.set_name('AMS', 'AMS_LIST_PK_EXISTS');
523 FND_MSG_PUB.add;
524 END IF;
525
526 x_return_status := FND_API.g_ret_sts_error;
527 RETURN;
528 END IF;
529 END IF;
530
531 -----------------------------------------------------------------
532 -- Check if list_name is unique. Need to handle create and
533 -- update differently.
534 -----------------------------------------------------------------
535 IF (p_listheader_rec.view_application_id <> 522
536 and p_listheader_rec.view_application_id <> 691 )then
540 close c_check_create_mode;
537 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
538 open c_check_create_mode;
539 fetch c_check_create_mode into l_valid_flag ;
541 ELSE
542 open c_check_update_mode;
543 fetch c_check_update_mode into l_valid_flag ;
544 close c_check_update_mode;
545 END IF;
546 ELSE
547 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
548 open c_check_create_mode_02 ;
549 fetch c_check_create_mode_02 into l_valid_flag ;
550 close c_check_create_mode_02 ;
551 ELSE
552 open c_check_update_mode_02 ;
553 fetch c_check_update_mode_02 into l_valid_flag ;
554 close c_check_update_mode_02 ;
555 END IF;
556 END IF;
557
558 IF l_valid_flag = FND_API.g_false THEN
559 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
560 THEN
561 FND_MESSAGE.set_name('AMS', 'AMS_LIST_DUPLICATE_NAME');
562 FND_MSG_PUB.add;
563 END IF;
564 x_return_status := FND_API.g_ret_sts_error;
565 RETURN;
566 END IF;
567
568 END check_list_uk_items;
569
570
571 -- Start of Comments
572 ---------------------------------------------------------------------
573 -- PROCEDURE
574 -- Check_Req_List_Items
575 --
576 -- HISTORY
577 -- 10/12/99 tdonohoe Created.
578 ---------------------------------------------------------------------
579 -- End of Comments
580
581 PROCEDURE Check_List_Req_Items
582 ( p_listheader_rec IN list_header_rec_type,
583 x_return_status OUT NOCOPY VARCHAR2
584 ) IS
585
586 l_api_name varchar2(30);
587 BEGIN
588 l_api_name := 'Check_list_req_items';
589 -- Initialize API/Procedure return status to success
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591
592 -----------------------------------------------------------------------
593 --The List Name is Mandatory.
594 -----------------------------------------------------------------------
595 IF(p_listheader_rec.list_name IS NULL) THEN
596 IF(FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR))THEN
597 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_NAME_MISSING');
598 FND_MSG_PUB.Add;
599 END IF;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 -- If any errors happen abort API/Procedure.
602 return;
603 END IF;
604
605 -----------------------------------------------------------------------
606 --The List Type is Mandatory.
607 -----------------------------------------------------------------------
608 IF (p_listheader_rec.list_type IS NULL) THEN
609 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
610 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_TYPE_MISSING');
611 FND_MSG_PUB.Add;
612 END IF;
613 x_return_status := FND_API.G_RET_STS_ERROR;
614 return;
615 END IF;
616
617 -----------------------------------------------------------------------
618 --The List source Type is Mandatory.
619 -----------------------------------------------------------------------
620 IF (p_listheader_rec.list_source_type IS NULL) THEN
621 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
622 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_SOURCE_TYPE_MISSING');
623 FND_MSG_PUB.Add;
624 END IF;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 return;
627 END IF;
628
629 -----------------------------------------------------------------------
630 --The List Owner User Id is Mandatory.
631 -----------------------------------------------------------------------
632 IF (p_listheader_rec.owner_user_id IS NULL) THEN
633 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
634 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_OWNER_MISSING');
635 FND_MSG_PUB.Add;
636 END IF;
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 return;
639 END IF;
640
641 -----------------------------------------------------------------------
642 --Not Mandatory but if a value is specified then validate.
643 -----------------------------------------------------------------------
644 IF (p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION IS NOT NULL) AND
645 (p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION <> FND_API.G_MISS_NUM)
646 THEN
647
648 IF ((p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION < 0) OR
649 (p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION >= 100) )THEN
650 IF(FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
651 FND_MESSAGE.set_name('AMS', 'AMS_LIST_RANDOM_PCT');
652 FND_MESSAGE.Set_Token('RAND_VALUE',p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION);
653 FND_MSG_PUB.Add;
654 END IF;
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 RAISE FND_API.G_EXC_ERROR;
657 END IF;
658 END IF;
659
660 -----------------------------------------------------------------------
661 --Not Mandatory but if a value is specified then validate.
662 -----------------------------------------------------------------------
666 IF ((p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION < 0) OR
663 IF (p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION IS NOT NULL) AND
664 (p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION <> FND_API.G_MISS_NUM)
665 THEN
667 (p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION > 100) )THEN
668 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
669 FND_MESSAGE.set_name('AMS', 'AMS_LIST_CONTROL_PCT_INVALID');
670 FND_MSG_PUB.Add;
671 END IF;
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 RAISE FND_API.G_EXC_ERROR;
674 END IF;
675 END IF;
676 EXCEPTION
677 WHEN FND_API.g_exc_error THEN
678 x_return_status := FND_API.g_ret_sts_error;
679 WHEN FND_API.g_exc_unexpected_error THEN
680 x_return_status := FND_API.g_ret_sts_unexp_error ;
681 WHEN OTHERS THEN
682 x_return_status := FND_API.g_ret_sts_unexp_error ;
683 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
684 THEN
685 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
686 END IF;
687 END Check_List_Req_Items;
688
689
690 -- Start of Comments
691 ---------------------------------------------------------------------
692 -- PROCEDURE
693 -- Validate_List_Record
694 --
695 -- PURPOSE
696 -- Validate the list record level business rules.
697 --
698 -- PARAMETERS
699 -- p_listheader_rec: the record to be validated; may contain attributes
700 -- as FND_API.g_miss_char/num/date
701 -- p_complete_rec: the complete record after all "g_miss" items
702 -- have been replaced by current database values
703 ---------------------------------------------------------------------
704 -- End Of Comments
705 PROCEDURE Validate_List_Record
706 ( p_listheader_rec IN list_header_rec_type,
707 p_complete_rec IN list_header_rec_type := NULL,
708 x_return_status OUT NOCOPY VARCHAR2
709
710 ) IS
711 -- Status Local Variables
712 l_return_status VARCHAR2(1); -- Return value from procedures
713 l_listheader_rec list_header_rec_type := p_complete_rec;
714 l_table_name VARCHAR2(30);
715 l_pk_name VARCHAR2(30);
716 l_source_code AMS_SOURCE_CODES.SOURCE_CODE%TYPE;
717 BEGIN
718 -- Debug Message
719 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
720 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
721 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_List_Record: Start', TRUE);
722 FND_MSG_PUB.Add;
723 END IF; */
724
725
726 IF (AMS_DEBUG_HIGH_ON) THEN
727 AMS_Utility_PVT.debug_message('AMS_ListHeader_PVT.Validate_List_Record: Start');
728 END IF;
729
730
731 -- Initialize API return status to success
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733
734
735 IF (p_listheader_rec.forecasted_start_date <> FND_API.g_miss_date) OR
736 (p_listheader_rec.forecasted_end_date <> FND_API.g_miss_date) THEN
737 IF (l_listheader_rec.forecasted_start_date > l_listheader_rec.forecasted_end_date) THEN
738 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
739 FND_MESSAGE.set_name('AMS', 'AMS_LIST_FCAST_RANGE_INVALID');
740 FND_MSG_PUB.Add;
741 END IF;
742 x_return_status := FND_API.G_RET_STS_ERROR;
743 RAISE FND_API.G_EXC_ERROR;
744 END IF;
745 END IF;
746
747
748 --Modified by VB 09/20/2000 instead of using user_entered_start_time we should use main_gen_end_time which is a system date
749
750 IF (p_listheader_rec.sent_out_date <> FND_API.g_miss_date) OR
751 (p_listheader_rec.main_gen_end_time <> FND_API.g_miss_date)
752 THEN
753
754 IF (l_listheader_rec.main_gen_end_time > l_listheader_rec.sent_out_date) THEN
755 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
756 FND_MESSAGE.set_name('AMS', 'AMS_LIST_GEN_START_DATE');
757 FND_MSG_PUB.Add;
758 END IF;
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 -- If any errors happen abort API/Procedure.
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763 END IF;
764
765
766
767 IF (p_listheader_rec.no_of_rows_min_requested <> FND_API.g_miss_num) OR
768 (p_listheader_rec.no_of_rows_max_requested <> FND_API.g_miss_num)
769 THEN
770
771 IF l_listheader_rec.no_of_rows_max_requested < 0 THEN
772 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
773 FND_MESSAGE.set_name('AMS', 'AMS_LIST_MIN_MAX_NEGATIVE');
774 FND_MSG_PUB.Add;
775 END IF;
776 x_return_status := FND_API.G_RET_STS_ERROR;
777 RAISE FND_API.G_EXC_ERROR;
778 END IF;
779
780 IF l_listheader_rec.no_of_rows_min_requested < 0 THEN
781 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
782 FND_MESSAGE.set_name('AMS', 'AMS_LIST_MIN_MAX_NEGATIVE');
783 FND_MSG_PUB.Add;
784 END IF;
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 RAISE FND_API.G_EXC_ERROR;
787 END IF;
788
792 FND_MSG_PUB.Add;
789 IF l_listheader_rec.no_of_rows_max_requested <= l_listheader_rec.no_of_rows_min_requested THEN
790 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
791 FND_MESSAGE.set_name('AMS', 'AMS_LIST_MIN_MAX_RANGE_INVALID');
793 END IF;
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 RAISE FND_API.G_EXC_ERROR;
796 END IF;
797 END IF;
798
799 -----------------------------------------------------------------------
800 --The List must be associated to a valid Marketing Activity.
801 -----------------------------------------------------------------------
802 IF(p_listheader_rec.arc_list_used_by <> FND_API.g_miss_char) THEN
803 IF(l_listheader_rec.arc_list_used_by <> 'NONE')THEN
804 AMS_UTILITY_PVT.get_qual_table_name_and_pk
805 (p_sys_qual => l_listheader_rec.arc_list_used_by,
806 x_return_status => x_return_status,
807 x_table_name => l_table_name,
808 x_pk_name => l_pk_name);
809
810 IF((l_table_name is not null) and (l_pk_name is not null))THEN
811 IF(AMS_UTILITY_PVT.Check_FK_Exists(l_table_name,l_pk_name,p_listheader_rec.list_used_by_id) = FND_API.G_FALSE)THEN
812 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
813 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACTIVITY_INVALID');
814 FND_MESSAGE.Set_Token('LIST_ACTIVITY_CODE',p_listheader_rec.arc_list_used_by);
815 FND_MSG_PUB.Add;
816 END IF;
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 -- If any errors happen abort API/Procedure.
819 RAISE FND_API.G_EXC_ERROR;
820 END IF;
821 ELSE
822 -- Error, check the msg level and added an error message to the
823 -- API message list
824 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)THEN
825 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACTIVTY_INVALID');
826 FND_MESSAGE.Set_Token('LIST_ACTIVITY_CODE',p_listheader_rec.arc_list_used_by);
827 FND_MSG_PUB.Add;
828 END IF;
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 -- If any errors happen abort API/Procedure.
831 RAISE FND_API.G_EXC_ERROR;
832 END IF;
833 END IF;
834 END IF;--The List must be associcated to a valid Marketing Activity.
835
836 -- Success Message
837 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
838 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
839 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_List_Record', TRUE);
840 FND_MSG_PUB.Add;
841 END IF;
842
843
844 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
845 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
846 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_List_Record: END', TRUE);
847 FND_MSG_PUB.Add;
848 END IF; */
849
850 EXCEPTION
851 WHEN FND_API.G_EXC_ERROR THEN
852 x_return_status := FND_API.G_RET_STS_ERROR ;
853
854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
856
857 WHEN OTHERS THEN
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
859
860 END Validate_List_Record;
861
862
863 PROCEDURE Validate_List_PK
864 ( p_listheader_rec IN list_header_rec_type,
865 x_listheader_id OUT NOCOPY NUMBER,
866 x_return_status OUT NOCOPY VARCHAR2
867 ) IS
868
869 l_dummy NUMBER;
870 l_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE;
871
872 CURSOR C (p_list_header_id varchar2) IS
873 SELECT COUNT(1)
874 FROM AMS_LIST_HEADERS_ALL
875 WHERE LIST_HEADER_ID = p_list_header_id;
876
877 -- list header sequence #
878 CURSOR listheader_seq IS
879 SELECT ams_list_headers_all_s.nextval
880 FROM DUAL;
881
882 BEGIN
883
884 -- Initialize API/Procedure return status to success
885 x_return_status := FND_API.G_RET_STS_SUCCESS;
886
887 IF p_listheader_rec.list_header_id IS NOT NULL THEN
888 OPEN C(p_listheader_rec.list_header_id );
889 FETCH C INTO l_dummy;
890 CLOSE C;
891 IF l_dummy >= 1 THEN
892 -- Error, check the msg level and added an error message to the
893 -- API message list
894 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
895 FND_MESSAGE.set_name('AMS', 'AMS_LIST_PK_EXISTS');
896 FND_MESSAGE.Set_Token('LIST_PK',p_listheader_rec.list_header_id, FALSE);
897 FND_MSG_PUB.Add;
898 END IF;
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 ELSE
901 x_listheader_id := p_listheader_rec.list_header_id;
902 END IF;
903
904 ELSE
905 <<Get_PK>>
906 LOOP
907 --------------------------------------------
908 -- open cursor AND fetch into local variable
909 open listheader_seq;
910 --------------------------------------------
911 FETCH listheader_seq INTO l_list_header_id;
912 CLOSE listheader_seq;
913
914 OPEN C(l_list_header_id);
915 FETCH C INTO l_dummy;
916 CLOSE C;
917
918 x_listheader_id := l_list_header_id;
922
919
920 -- exit when unique key found
921 EXIT WHEN l_dummy = 0;
923 END LOOP;
924
925 END IF;
926
927 END Validate_List_PK;
928
929 -- Start of Comments
930 --
931 -- NAME
932 -- Validate_List_Items
933 --
934 -- PURPOSE
935 -- This procedure is to validate list header items
936 --
937 -- NOTES
938 --
939 --
940 -- HISTORY
941 -- 05/18/1999 tdonohoe created
942 -- End of Comments
943
944
945 PROCEDURE Validate_List_Items
946 ( p_listheader_rec IN list_header_rec_type,
947 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
948 x_return_status OUT NOCOPY VARCHAR2
949 ) IS
950
951 BEGIN
952 -- Initialize API/Procedure return status to success
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954
955 check_list_req_items(
956 p_listheader_rec => p_listheader_rec,
957 x_return_status => x_return_status);
958
959 IF x_return_status <> FND_API.g_ret_sts_success THEN
960 RETURN;
961 END IF;
962 check_list_uk_items(
963 p_listheader_rec => p_listheader_rec,
964 p_validation_mode => p_validation_mode,
965 x_return_status => x_return_status);
966
967 IF x_return_status <> FND_API.g_ret_sts_success THEN
968 RETURN;
969 END IF;
970
971 check_list_fk_items(
972 p_listheader_rec => p_listheader_rec,
973 x_return_status => x_return_status);
974
975 IF x_return_status <> FND_API.g_ret_sts_success THEN
976 RETURN;
977 END IF;
978
979 check_list_lookup_items(
980 p_listheader_rec => p_listheader_rec,
981 x_return_status => x_return_status);
982
983 IF x_return_status <> FND_API.g_ret_sts_success THEN
984 RETURN;
985 END IF;
986 check_list_flag_items(
987 p_listheader_rec => p_listheader_rec,
988 x_return_status => x_return_status);
989 IF x_return_status <> FND_API.g_ret_sts_success THEN
990 RETURN;
991 END IF;
992
993 END Validate_List_Items;
994
995
996 -- Start of Comments
997 ---------------------------------------------------------------------
998 -- PROCEDURE
999 -- Validate_ListHeader
1000 --
1001 -- PURPOSE
1002 -- Validate a List Header Record.
1003 --
1004 -- PARAMETERS
1005 -- p_listheader_rec: the list header record to be validated
1006 --
1007 -- NOTES
1008 -- 1. p_listheader_rec_rec should be the complete list header record. There
1009 -- should not be any FND_API.g_miss_char/num/date in it.
1010 ----------------------------------------------------------------------
1011 -- End Of Comments
1012
1013
1014 PROCEDURE Validate_ListHeader
1015 ( p_api_version IN NUMBER,
1016 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1017 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1018 x_return_status OUT NOCOPY VARCHAR2,
1019 x_msg_count OUT NOCOPY NUMBER,
1020 x_msg_data OUT NOCOPY VARCHAR2,
1021 p_listheader_rec IN list_header_rec_type
1022 ) IS
1023
1024 l_api_name CONSTANT VARCHAR2(30) := 'Validate_ListHeader';
1025 l_api_version CONSTANT NUMBER := 1.0;
1026
1027 -- Status Local Variables
1028 l_return_status VARCHAR2(1); -- Return value from procedures
1029 l_listheader_rec list_header_rec_type := p_listheader_rec;
1030 l_default_listhd_rec list_header_rec_type;
1031 l_listheader_id number;
1032
1033 BEGIN
1034 -- Standard call to check for call compatibility.
1035 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1036 p_api_version,
1037 l_api_name,
1038 G_PKG_NAME) THEN
1039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1040 END IF;
1041
1042 -- Initialize message list if p_init_msg_list is set to TRUE.
1043 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1044 FND_MSG_PUB.initialize;
1045 END IF;
1046
1047 -- Debug Message
1048 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1049 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1050 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_ListHeader: Start', TRUE);
1051 FND_MSG_PUB.Add;
1052 END IF; */
1053
1054 IF (AMS_DEBUG_HIGH_ON) THEN
1055 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Validate_listheaders: Start');
1056 END IF;
1057
1058
1059
1060 -- Initialize API return status to success
1061 x_return_status := FND_API.G_RET_STS_SUCCESS;
1062 --
1063 -- API body
1064 --
1065
1066 -- Step 1.
1067 -- Validate all non missing attributes (Item level validation)
1068 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1069 Validate_List_Items
1070 ( p_listheader_rec => l_listheader_rec,
1071 p_validation_mode => JTF_PLSQL_API.g_update,
1072 x_return_status => x_return_status
1073 );
1074 -- If any errors happen abort API.
1078 RAISE FND_API.G_EXC_ERROR;
1075 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1077 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1079 END IF;
1080 END IF;
1081
1082
1083 -- Step 2.
1084 -- Perform cross attribute validation and missing attribute checks. Record
1085 -- level validation.
1086 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1087
1088 Validate_List_Record
1089 ( p_listheader_rec => p_listheader_rec,
1090 p_complete_rec => l_listheader_rec,
1091 x_return_status => x_return_status);
1092
1093 -- If any errors happen abort API.
1094 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1095
1096 RAISE FND_API.G_EXC_ERROR;
1097 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1098
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101 END IF;
1102
1103 --
1104 -- END of API body.
1105 --
1106
1107 -- Success Message
1108 -- MMSG
1109 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1110 THEN
1111 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1112 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_ListHeader', TRUE);
1113 FND_MSG_PUB.Add;
1114 END IF;
1115
1116
1117 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1118 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1119 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_ListHeader: END', TRUE);
1120 FND_MSG_PUB.Add;
1121 END IF; */
1122 -- Standard call to get message count AND IF count is 1, get message info.
1123 FND_MSG_PUB.Count_AND_Get
1124 ( p_count => x_msg_count,
1125 p_data => x_msg_data,
1126 p_encoded => FND_API.G_FALSE
1127 );
1128
1129 EXCEPTION
1130 WHEN FND_API.G_EXC_ERROR THEN
1131 x_return_status := FND_API.G_RET_STS_ERROR ;
1132
1133 FND_MSG_PUB.Count_AND_Get
1134 ( p_count => x_msg_count,
1135 p_data => x_msg_data,
1136 p_encoded => FND_API.G_FALSE
1137 );
1138
1139
1140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1141
1142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1143
1144 FND_MSG_PUB.Count_AND_Get
1145 ( p_count => x_msg_count,
1146 p_data => x_msg_data,
1147 p_encoded => FND_API.G_FALSE
1148 );
1149 WHEN OTHERS THEN
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1151
1152 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1153 THEN
1154 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1155 END IF;
1156
1157 FND_MSG_PUB.Count_AND_Get
1158 ( p_count => x_msg_count,
1159 p_data => x_msg_data,
1160 p_encoded => FND_API.G_FALSE
1161 );
1162 END Validate_ListHeader;
1163
1164
1165
1166
1167 -- Start of Comments
1168 --
1169 -- NAME
1170 -- Create_ListHeader
1171 --
1172 -- PURPOSE
1173 -- This procedure creates a list header record that satisfy caller needs
1174 --
1175 -- NOTES
1176 --
1177 --
1178 -- HISTORY
1179 -- 05/12/1999 tdonohoe created
1180 -- End of Comments
1181 PROCEDURE Create_ListHeader
1182 ( p_api_version IN NUMBER,
1183 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1184 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1185 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_count OUT NOCOPY NUMBER,
1188 x_msg_data OUT NOCOPY VARCHAR2,
1189 p_listheader_rec IN list_header_rec_type,
1190 x_listheader_id OUT NOCOPY NUMBER
1191 ) IS
1192
1193 l_api_name CONSTANT VARCHAR2(30) := 'Create_ListHeader';
1194 l_api_version CONSTANT NUMBER := 1.0;
1195 -- Status Local Variables
1196 l_return_status VARCHAR2(1); -- Return value from procedures
1197 l_listheader_rec list_header_rec_type := p_listheader_rec;
1198 l_listheader_id number;
1199
1200 x_rowid VARCHAR2(30);
1201
1202 l_sqlerrm varchar2(600);
1203 l_sqlcode varchar2(100);
1204
1205 l_list_count NUMBER;
1206 l_main_gen_time DATE := null;
1207
1208 CURSOR c_list_seq IS
1209 SELECT ams_list_headers_all_s.NEXTVAL
1210 FROM DUAL;
1211
1212 CURSOR c_list_count(p_list_header_id IN NUMBER) IS
1213 SELECT COUNT(*)
1214 FROM ams_list_headers_all
1215 WHERE list_header_id = p_list_header_id;
1216 L_OBJECT_TYPE VARCHAR2(10) := 'LIST';
1217
1218 CURSOR c_custom_setup_id(c_list_type in varchar2) IS
1219 SELECT custom_setup_id
1220 FROM ams_custom_setups_b
1221 WHERE object_type = L_OBJECT_TYPE
1222 and activity_type_code = c_list_type
1226 CURSOR c_default_list_user_status_id IS
1223 --AND enabled_flag = 'Y'
1224 ;
1225
1227 SELECT user_status_id
1228 FROM ams_user_statuses_vl
1229 WHERE system_status_type = 'AMS_LIST_STATUS'
1230 AND system_status_code = 'DRAFT'
1231 AND enabled_flag = 'Y'
1232 AND default_flag = 'Y';
1233
1234
1235 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
1236
1237 BEGIN
1238
1239 -- Standard Start of API savepoint
1240 SAVEPOINT Create_listheaders_PVT;
1241
1242 x_return_status := FND_API.G_RET_STS_SUCCESS;
1243 -- Standard call to check for call compatibility.
1244 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1245 p_api_version,
1246 l_api_name,
1247 G_PKG_NAME) THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 END IF;
1250
1251
1252 -- Initialize message list IF p_init_msg_list is set to TRUE.
1253 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1254 FND_MSG_PUB.initialize;
1255 END IF;
1256
1257 -- Debug Message
1258 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1259 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1260 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeaders_PVT.Create_listheaders: Start', TRUE);
1261 FND_MSG_PUB.Add;
1262 END IF; */
1263
1264 IF (AMS_DEBUG_HIGH_ON) THEN
1265 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Create_listheaders: Start');
1266 END IF;
1267
1268
1269 -- Initialize API return status to success
1270 x_return_status := FND_API.G_RET_STS_SUCCESS;
1271 -- Perform the database operation
1272
1273 IF (l_listheader_rec.list_header_id IS NULL OR
1274 l_listheader_rec.list_header_id = FND_API.g_miss_num) THEN LOOP
1275 OPEN c_list_seq;
1276 FETCH c_list_seq INTO l_listheader_rec.list_header_id;
1277 CLOSE c_list_seq;
1278
1279 OPEN c_list_count(l_listheader_rec.list_header_id);
1280 FETCH c_list_count INTO l_list_count;
1281 CLOSE c_list_count;
1282
1283 EXIT WHEN l_list_count = 0;
1284 END LOOP;
1285 END IF;
1286
1287 IF (l_listheader_rec.USER_ENTERED_START_TIME IS NOT NULL
1288 AND l_listheader_rec.TIMEZONE_ID IS NOT NULL
1289 AND l_listheader_rec.TIMEZONE_ID <> FND_API.g_miss_num) THEN
1290
1291 AMS_UTILITY_PVT.Convert_Timezone(
1292 p_init_msg_list => l_init_msg_list,
1293 x_return_status => x_return_status,
1294 x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data,
1296 p_user_tz_id => l_listheader_rec.TIMEZONE_ID,
1297 p_in_time => l_listheader_rec.USER_ENTERED_START_TIME,
1298 p_convert_type => 'SYS',
1299 x_out_time => l_main_gen_time
1300 );
1301
1302 -- If any errors happen abort API.
1303 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1304 RAISE FND_API.G_EXC_ERROR;
1305 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308 END IF;
1309
1310
1311 -- initialize any default values
1312 OPEN c_custom_setup_id(l_listheader_rec.list_type);
1313 FETCH c_custom_setup_id INTO l_listheader_rec.custom_setup_id;
1314 CLOSE c_custom_setup_id;
1315
1316 OPEN c_default_list_user_status_id;
1317 FETCH c_default_list_user_status_id INTO l_listheader_rec.user_status_id;
1318 CLOSE c_default_list_user_status_id;
1319
1320 IF l_listheader_rec.country IS NULL OR l_listheader_rec.country = FND_API.g_miss_num THEN
1321 l_listheader_rec.country := FND_PROFILE.value ('AMS_SRCGEN_USER_CITY');
1322 END IF;
1323
1324 Validate_ListHeader
1325 ( p_api_version => 1.0
1326 ,p_init_msg_list => l_init_msg_list
1327 ,p_validation_level => p_validation_level
1328 ,x_return_status => x_return_status
1329 ,x_msg_count => x_msg_count
1330 ,x_msg_data => x_msg_data
1331 ,p_listheader_rec => l_listheader_rec
1332 );
1333
1334
1335 -- If any errors happen abort API.
1336 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 END IF;
1341 insert into AMS_LIST_HEADERS_ALL (
1342 LIST_HEADER_ID ,
1343 LAST_UPDATE_DATE ,
1344 LAST_UPDATED_BY ,
1345 CREATION_DATE ,
1346 CREATED_BY ,
1347 LAST_UPDATE_LOGIN ,
1348 OBJECT_VERSION_NUMBER ,
1349 REQUEST_ID ,
1350 PROGRAM_ID ,
1351 PROGRAM_APPLICATION_ID ,
1352 PROGRAM_UPDATE_DATE ,
1353 VIEW_APPLICATION_ID ,
1354 LIST_USED_BY_ID ,
1355 ARC_LIST_USED_BY ,
1356 LIST_TYPE ,
1357 STATUS_CODE ,
1358 STATUS_DATE ,
1359 GENERATION_TYPE ,
1360 REPEAT_EXCLUDE_TYPE ,
1361 ROW_SELECTION_TYPE ,
1362 OWNER_USER_ID ,
1363 ACCESS_LEVEL ,
1364 ENABLE_LOG_FLAG ,
1368 GENERATE_CONTROL_GROUP_FLAG ,
1365 ENABLE_WORD_REPLACEMENT_FLAG ,
1366 ENABLE_PARALLEL_DML_FLAG ,
1367 DEDUPE_DURING_GENERATION_FLAG ,
1369 LAST_GENERATION_SUCCESS_FLAG ,
1370 FORECASTED_START_DATE ,
1371 FORECASTED_END_DATE ,
1372 ACTUAL_END_DATE ,
1373 SENT_OUT_DATE ,
1374 DEDUPE_START_DATE ,
1375 LAST_DEDUPE_DATE ,
1376 LAST_DEDUPED_BY_USER_ID ,
1377 WORKFLOW_ITEM_KEY ,
1378 NO_OF_ROWS_DUPLICATES ,
1379 NO_OF_ROWS_MIN_REQUESTED ,
1380 NO_OF_ROWS_MAX_REQUESTED ,
1381 NO_OF_ROWS_IN_LIST ,
1382 NO_OF_ROWS_IN_CTRL_GROUP ,
1383 NO_OF_ROWS_ACTIVE ,
1384 NO_OF_ROWS_INACTIVE ,
1385 NO_OF_ROWS_MANUALLY_ENTERED ,
1386 NO_OF_ROWS_DO_NOT_CALL ,
1387 NO_OF_ROWS_DO_NOT_MAIL ,
1388 NO_OF_ROWS_RANDOM ,
1389 ORG_ID ,
1390 MAIN_GEN_START_TIME ,
1391 MAIN_GEN_END_TIME ,
1392 MAIN_RANDOM_NTH_ROW_SELECTION ,
1393 MAIN_RANDOM_PCT_ROW_SELECTION ,
1394 CTRL_RANDOM_NTH_ROW_SELECTION ,
1395 CTRL_RANDOM_PCT_ROW_SELECTION ,
1396 REPEAT_SOURCE_LIST_HEADER_ID ,
1397 RESULT_TEXT ,
1398 KEYWORDS ,
1399 DESCRIPTION ,
1400 LIST_PRIORITY ,
1401 ASSIGN_PERSON_ID ,
1402 LIST_SOURCE ,
1403 LIST_SOURCE_TYPE ,
1404 LIST_ONLINE_FLAG ,
1405 RANDOM_LIST_ID ,
1406 ENABLED_FLAG ,
1407 ASSIGNED_TO ,
1408 QUERY_ID ,
1409 OWNER_PERSON_ID ,
1410 ARCHIVED_BY ,
1411 ARCHIVED_DATE ,
1412 ATTRIBUTE_CATEGORY ,
1413 ATTRIBUTE1 ,
1414 ATTRIBUTE2 ,
1415 ATTRIBUTE3 ,
1416 ATTRIBUTE4 ,
1417 ATTRIBUTE5 ,
1418 ATTRIBUTE6 ,
1419 ATTRIBUTE7 ,
1420 ATTRIBUTE8 ,
1421 ATTRIBUTE9 ,
1422 ATTRIBUTE10 ,
1423 ATTRIBUTE11 ,
1424 ATTRIBUTE12 ,
1425 ATTRIBUTE13 ,
1426 ATTRIBUTE14 ,
1427 ATTRIBUTE15 ,
1428 TIMEZONE_ID ,
1429 USER_ENTERED_START_TIME ,
1430 USER_STATUS_ID ,
1431 QUANTUM ,
1432 RELEASE_CONTROL_ALG_ID ,
1433 DIALING_METHOD ,
1434 CALLING_CALENDAR_ID ,
1435 RELEASE_STRATEGY ,
1436 CUSTOM_SETUP_ID ,
1437 COUNTRY ,
1438 CALLBACK_PRIORITY_FLAG ,
1439 CALL_CENTER_READY_FLAG ,
1440 PURGE_FLAG ,
1441 QUOTA ,
1442 QUOTA_RESET ,
1443 RECYCLING_ALG_ID ,
1444 PUBLIC_FLAG ,
1445 LIST_CATEGORY ,
1446 no_of_rows_prev_contacted,
1447 APPLY_TRAFFIC_COP,
1448 purpose_code,
1449 CTRL_CONF_LEVEL,
1450 CTRL_REQ_RESP_RATE ,
1451 CTRL_LIMIT_OF_ERROR ,
1452 STATUS_CODE_OLD,
1453 CTRL_CONC_JOB_ID ,
1454 CTRL_STATUS_CODE ,
1455 CTRL_GEN_MODE ,
1456 APPLY_SUPPRESSION_FLAG
1457 ) values (
1458 decode(l_listheader_rec.LIST_HEADER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LIST_HEADER_ID) ,
1459 sysdate,
1460 FND_GLOBAL.user_id,
1461 sysdate,
1462 FND_GLOBAL.user_id,
1463 FND_GLOBAL.conc_login_id,
1464 1,
1465 decode(l_listheader_rec.REQUEST_ID ,FND_API.g_miss_num,null,l_listheader_rec.REQUEST_ID) ,
1466 decode(l_listheader_rec.PROGRAM_ID ,FND_API.g_miss_num,null,l_listheader_rec.PROGRAM_ID) ,
1467 decode(l_listheader_rec.PROGRAM_APPLICATION_ID ,FND_API.g_miss_num,null,l_listheader_rec.PROGRAM_APPLICATION_ID) ,
1468 decode(l_listheader_rec.PROGRAM_UPDATE_DATE ,FND_API.g_miss_date,null,l_listheader_rec.PROGRAM_UPDATE_DATE) ,
1469 decode(l_listheader_rec.VIEW_APPLICATION_ID ,FND_API.g_miss_num,530,nvl(l_listheader_rec.VIEW_APPLICATION_ID,530)) ,
1470 decode(l_listheader_rec.LIST_USED_BY_ID ,FND_API.g_miss_num,0,nvl(l_listheader_rec.LIST_USED_BY_ID,0)) ,
1471 decode(l_listheader_rec.ARC_LIST_USED_BY ,FND_API.g_miss_char,'NONE',nvl(l_listheader_rec.ARC_LIST_USED_BY,'NONE')) ,
1472 decode(l_listheader_rec.LIST_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_TYPE) ,
1473 decode(l_listheader_rec.STATUS_CODE ,FND_API.g_miss_char,'DRAFT',nvl(l_listheader_rec.STATUS_CODE,'DRAFT')) ,
1474 decode(l_listheader_rec.STATUS_DATE ,FND_API.g_miss_date,sysdate,nvl(l_listheader_rec.STATUS_DATE,sysdate)) ,
1475 decode(l_listheader_rec.GENERATION_TYPE ,FND_API.g_miss_char,'STANDARD',nvl(l_listheader_rec.GENERATION_TYPE,'STANDARD')) ,
1476 decode(l_listheader_rec.REPEAT_EXCLUDE_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.REPEAT_EXCLUDE_TYPE) ,
1477 decode(l_listheader_rec.ROW_SELECTION_TYPE ,FND_API.g_miss_char,'STANDARD',nvl(l_listheader_rec.ROW_SELECTION_TYPE,'STANDARD')) ,
1478 decode(l_listheader_rec.OWNER_USER_ID ,FND_API.g_miss_num,null,l_listheader_rec.OWNER_USER_ID) ,
1479 decode(l_listheader_rec.ACCESS_LEVEL ,FND_API.g_miss_char,'USER',nvl(l_listheader_rec.ACCESS_LEVEL,'USER')) ,
1480 decode(l_listheader_rec.ENABLE_LOG_FLAG ,FND_API.g_miss_char,'Y',nvl(l_listheader_rec.ENABLE_LOG_FLAG,'Y')) ,
1481 decode(l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG,'N')) ,
1482 decode(l_listheader_rec.ENABLE_PARALLEL_DML_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.ENABLE_PARALLEL_DML_FLAG,'N')) ,
1483 decode(l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG,'N')) ,
1484 decode(l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG,'N')) ,
1485 decode(l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG,'N')) ,
1489 decode(l_listheader_rec.SENT_OUT_DATE ,FND_API.g_miss_date,null,l_listheader_rec.SENT_OUT_DATE) ,
1486 decode(l_listheader_rec.FORECASTED_START_DATE ,FND_API.g_miss_date,sysdate,nvl(l_listheader_rec.FORECASTED_START_DATE,sysdate)) ,
1487 decode(l_listheader_rec.FORECASTED_END_DATE ,FND_API.g_miss_date,null,l_listheader_rec.FORECASTED_END_DATE) ,
1488 decode(l_listheader_rec.ACTUAL_END_DATE ,FND_API.g_miss_date,null,l_listheader_rec.ACTUAL_END_DATE) ,
1490 decode(l_listheader_rec.DEDUPE_START_DATE ,FND_API.g_miss_date,null,l_listheader_rec.DEDUPE_START_DATE) ,
1491 decode(l_listheader_rec.LAST_DEDUPE_DATE ,FND_API.g_miss_date,null,l_listheader_rec.LAST_DEDUPE_DATE) ,
1492 decode(l_listheader_rec.LAST_DEDUPED_BY_USER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LAST_DEDUPED_BY_USER_ID) ,
1493 decode(l_listheader_rec.WORKFLOW_ITEM_KEY ,FND_API.g_miss_num,null,l_listheader_rec.WORKFLOW_ITEM_KEY) ,
1494 decode(l_listheader_rec.NO_OF_ROWS_DUPLICATES ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DUPLICATES) ,
1495 decode(l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED) ,
1496 decode(l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED) ,
1497 decode(l_listheader_rec.NO_OF_ROWS_IN_LIST ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_IN_LIST) ,
1498 decode(l_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP) ,
1499 decode(l_listheader_rec.NO_OF_ROWS_ACTIVE ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_ACTIVE) ,
1500 decode(l_listheader_rec.NO_OF_ROWS_INACTIVE ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_INACTIVE) ,
1501 decode(l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED) ,
1502 decode(l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL) ,
1503 decode(l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL) ,
1504 decode(l_listheader_rec.NO_OF_ROWS_RANDOM ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_RANDOM) ,
1505 decode(l_listheader_rec.ORG_ID ,FND_API.g_miss_num,
1506 TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)),
1507 nvl(l_listheader_rec.ORG_ID,TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)))) ,
1508 decode(l_listheader_rec.MAIN_GEN_START_TIME ,FND_API.g_miss_date,null,l_listheader_rec.MAIN_GEN_START_TIME) ,
1509 decode(l_listheader_rec.MAIN_GEN_END_TIME ,FND_API.g_miss_date,null,l_listheader_rec.MAIN_GEN_END_TIME) ,
1510 decode(l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION) ,
1511 decode(l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION) ,
1512 decode(l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION) ,
1513 decode(l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION) ,
1514 decode(l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID ,FND_API.g_miss_char,null,l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID) ,
1515 decode(l_listheader_rec.RESULT_TEXT ,FND_API.g_miss_char,null,l_listheader_rec.RESULT_TEXT) ,
1516 decode(l_listheader_rec.KEYWORDS ,FND_API.g_miss_char,null,l_listheader_rec.KEYWORDS) ,
1517 decode(l_listheader_rec.DESCRIPTION ,FND_API.g_miss_char,null,l_listheader_rec.DESCRIPTION) ,
1518 decode(l_listheader_rec.LIST_PRIORITY ,FND_API.g_miss_num,null,l_listheader_rec.LIST_PRIORITY) ,
1519 decode(l_listheader_rec.ASSIGN_PERSON_ID ,FND_API.g_miss_num,null,l_listheader_rec.ASSIGN_PERSON_ID) ,
1520 decode(l_listheader_rec.LIST_SOURCE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_SOURCE) ,
1521 decode(l_listheader_rec.LIST_SOURCE_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_SOURCE_TYPE) ,
1522 decode(l_listheader_rec.LIST_ONLINE_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.LIST_ONLINE_FLAG) ,
1523 decode(l_listheader_rec.RANDOM_LIST_ID ,FND_API.g_miss_num,null,l_listheader_rec.RANDOM_LIST_ID) ,
1524 decode(l_listheader_rec.ENABLED_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.ENABLED_FLAG) ,
1525 decode(l_listheader_rec.ASSIGNED_TO ,FND_API.g_miss_num,null,l_listheader_rec.ASSIGNED_TO) ,
1526 decode(l_listheader_rec.QUERY_ID ,FND_API.g_miss_num,null,l_listheader_rec.QUERY_ID) ,
1527 decode(l_listheader_rec.OWNER_PERSON_ID ,FND_API.g_miss_num,null,l_listheader_rec.OWNER_PERSON_ID) ,
1528 decode(l_listheader_rec.ARCHIVED_BY ,FND_API.g_miss_num,null,l_listheader_rec.ARCHIVED_BY) ,
1529 decode(l_listheader_rec.ARCHIVED_DATE ,FND_API.g_miss_date,null,l_listheader_rec.ARCHIVED_DATE) ,
1530 decode(l_listheader_rec.ATTRIBUTE_CATEGORY ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE_CATEGORY) ,
1531 decode(l_listheader_rec.ATTRIBUTE1 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE1) ,
1532 decode(l_listheader_rec.ATTRIBUTE2 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE2) ,
1533 decode(l_listheader_rec.ATTRIBUTE3 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE3) ,
1534 decode(l_listheader_rec.ATTRIBUTE4 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE4) ,
1535 decode(l_listheader_rec.ATTRIBUTE5 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE5) ,
1536 decode(l_listheader_rec.ATTRIBUTE6 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE6) ,
1537 decode(l_listheader_rec.ATTRIBUTE7 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE7) ,
1538 decode(l_listheader_rec.ATTRIBUTE8 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE8) ,
1539 decode(l_listheader_rec.ATTRIBUTE9 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE9) ,
1540 decode(l_listheader_rec.ATTRIBUTE10 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE10) ,
1541 decode(l_listheader_rec.ATTRIBUTE11 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE11) ,
1545 decode(l_listheader_rec.ATTRIBUTE15 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE15) ,
1542 decode(l_listheader_rec.ATTRIBUTE12 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE12) ,
1543 decode(l_listheader_rec.ATTRIBUTE13 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE13) ,
1544 decode(l_listheader_rec.ATTRIBUTE14 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE14) ,
1546 decode(l_listheader_rec.TIMEZONE_ID ,FND_API.g_miss_num,null,l_listheader_rec.TIMEZONE_ID) ,
1547 decode(l_listheader_rec.USER_ENTERED_START_TIME ,FND_API.g_miss_date,null,l_listheader_rec.USER_ENTERED_START_TIME) ,
1548 -- decode(l_listheader_rec.USER_STATUS_ID ,FND_API.g_miss_num,300,nvl(l_listheader_rec.USER_STATUS_ID,300)) ,
1549 decode(l_listheader_rec.USER_STATUS_ID ,FND_API.g_miss_num,null,l_listheader_rec.USER_STATUS_ID) ,
1550 decode(l_listheader_rec.QUANTUM ,FND_API.g_miss_num,null,l_listheader_rec.QUANTUM) ,
1551 decode(l_listheader_rec.RELEASE_CONTROL_ALG_ID ,FND_API.g_miss_num,null,l_listheader_rec.RELEASE_CONTROL_ALG_ID) ,
1552 decode(l_listheader_rec.DIALING_METHOD ,FND_API.g_miss_char,null,l_listheader_rec.DIALING_METHOD) ,
1553 decode(l_listheader_rec.CALLING_CALENDAR_ID ,FND_API.g_miss_num,null,l_listheader_rec.CALLING_CALENDAR_ID) ,
1554 decode(l_listheader_rec.RELEASE_STRATEGY ,FND_API.g_miss_char,null,l_listheader_rec.RELEASE_STRATEGY) ,
1555 decode(l_listheader_rec.CUSTOM_SETUP_ID ,FND_API.g_miss_num,null,l_listheader_rec.CUSTOM_SETUP_ID) ,
1556 decode(l_listheader_rec.COUNTRY ,FND_API.g_miss_num,null,l_listheader_rec.COUNTRY) ,
1557 decode(l_listheader_rec.CALLBACK_PRIORITY_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.CALLBACK_PRIORITY_FLAG) ,
1558 decode(l_listheader_rec.CALL_CENTER_READY_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.CALL_CENTER_READY_FLAG) ,
1559 decode(l_listheader_rec.PURGE_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.PURGE_FLAG) ,
1560 decode(l_listheader_rec.QUOTA ,FND_API.g_miss_num,null,l_listheader_rec.QUOTA) ,
1561 decode(l_listheader_rec.QUOTA_RESET ,FND_API.g_miss_num,null,l_listheader_rec.QUOTA_RESET) ,
1562 decode(l_listheader_rec.RECYCLING_ALG_ID ,FND_API.g_miss_num,null,l_listheader_rec.RECYCLING_ALG_ID) ,
1563 decode(l_listheader_rec.PUBLIC_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.PUBLIC_FLAG) ,
1564 decode(l_listheader_rec.LIST_CATEGORY ,FND_API.g_miss_char,null,l_listheader_rec.LIST_CATEGORY) ,
1565 decode(l_listheader_rec.NO_OF_ROWS_prev_contacted ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_prev_contacted) ,
1566 decode(l_listheader_rec.APPLY_TRAFFIC_COP ,FND_API.g_miss_char,null,l_listheader_rec.APPLY_TRAFFIC_COP),
1567 decode(l_listheader_rec.PURPOSE_CODE ,FND_API.g_miss_char,'GENERAL',null,'GENERAL',l_listheader_rec.PURPOSE_CODE) ,
1568
1569 -- ckapoor R12 enhancement for copy target group changes
1570 decode(l_listheader_rec.CTRL_CONF_LEVEL ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_CONF_LEVEL) ,
1571 decode(l_listheader_rec.CTRL_REQ_RESP_RATE ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_REQ_RESP_RATE) ,
1572 decode(l_listheader_rec.CTRL_LIMIT_OF_ERROR ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_LIMIT_OF_ERROR) ,
1573 decode(l_listheader_rec.STATUS_CODE_OLD ,FND_API.g_miss_char,null,l_listheader_rec.STATUS_CODE_OLD) ,
1574 decode(l_listheader_rec.CTRL_CONC_JOB_ID ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_CONC_JOB_ID) ,
1575 decode(l_listheader_rec.CTRL_STATUS_CODE ,FND_API.g_miss_char,null,l_listheader_rec.CTRL_STATUS_CODE) ,
1576 decode(l_listheader_rec.CTRL_GEN_MODE ,FND_API.g_miss_char,null, l_listheader_rec.CTRL_GEN_MODE) ,
1577 decode(l_listheader_rec.APPLY_SUPPRESSION_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.APPLY_SUPPRESSION_FLAG)
1578 -- end ckapoor R12 enhancement for copy target group changes
1579
1580 );
1581
1582 insert into AMS_LIST_HEADERS_ALL_TL (
1583 LANGUAGE,
1584 SOURCE_LANG,
1585 LIST_NAME,
1586 DESCRIPTION,
1587 LIST_HEADER_ID,
1588 LAST_UPDATE_DATE,
1589 LAST_UPDATE_BY,
1590 CREATION_DATE,
1591 CREATED_BY,
1592 LAST_UPDATE_LOGIN
1593 ) select
1594 l.language_code,
1595 userenv('LANG'),
1596 decode(l_listheader_rec.LIST_NAME ,FND_API.g_miss_char,null,l_listheader_rec.LIST_NAME) ,
1597 decode(l_listheader_rec.DESCRIPTION ,FND_API.g_miss_char,null,l_listheader_rec.DESCRIPTION) ,
1598 decode(l_listheader_rec.LIST_HEADER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LIST_HEADER_ID) ,
1599 sysdate,
1600 FND_GLOBAL.user_id,
1601 sysdate,
1602 FND_GLOBAL.user_id,
1603 FND_GLOBAL.conc_login_id
1604 from FND_LANGUAGES L
1605 where L.INSTALLED_FLAG in ('I', 'B')
1606 and not exists
1607 (select NULL
1608 from AMS_LIST_HEADERS_ALL_TL T
1609 where T.LIST_HEADER_ID = l_listheader_rec.LIST_HEADER_ID
1610 and T.LANGUAGE = L.LANGUAGE_CODE);
1611
1612
1613 -- set OUT value
1614 x_listheader_id := l_listheader_rec.list_header_id;
1615
1616 -- Standard check of p_commit.
1617 IF FND_API.To_Boolean ( p_commit ) THEN
1618 COMMIT WORK;
1619 END IF;
1620
1621 -- Success Message
1622 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1623 THEN
1624 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1625 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Create_listheaders', TRUE);
1626 FND_MSG_PUB.Add;
1627 END IF;
1628
1629 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1630 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1631 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Create_listheaders: END', TRUE);
1632 FND_MSG_PUB.Add;
1633 END IF; */
1634
1635
1639 p_data => x_msg_data,
1636 -- Standard call to get message count AND IF count is 1, get message info.
1637 FND_MSG_PUB.Count_AND_Get
1638 ( p_count => x_msg_count,
1640 p_encoded => FND_API.G_FALSE
1641 );
1642
1643 EXCEPTION
1644 WHEN FND_API.G_EXC_ERROR THEN
1645 ROLLBACK TO Create_listheaders_PVT;
1646 x_return_status := FND_API.G_RET_STS_ERROR ;
1647
1648 FND_MSG_PUB.Count_AND_Get
1649 ( p_count => x_msg_count,
1650 p_data => x_msg_data,
1651 p_encoded => FND_API.G_FALSE
1652 );
1653
1654
1655 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1656 ROLLBACK TO Create_listheaders_PVT;
1657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1658 FND_MSG_PUB.Count_AND_Get
1659 ( p_count => x_msg_count,
1660 p_data => x_msg_data,
1661 p_encoded => FND_API.G_FALSE
1662 );
1663
1664 WHEN OTHERS THEN
1665 ROLLBACK TO Create_listheaders_PVT;
1666 FND_MESSAGE.set_name('AMS','SQL ERROR ->' || sqlerrm );
1667 FND_MSG_PUB.Add;
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1669 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1670 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1671 END IF;
1672
1673 FND_MSG_PUB.Count_AND_Get
1674 ( p_count => x_msg_count,
1675 p_data => x_msg_data,
1676 p_encoded => FND_API.G_FALSE
1677 );
1678
1679 END Create_listheader;
1680
1681
1682 -- Start of Comments
1683 -----------------------------------------------------------------------------
1684 -- NAME
1685 -- Update_listheader
1686 --
1687 -- PURPOSE
1688 -- This procedure is to update a List Header record that satisfy caller needs
1689 --
1690 -- NOTES
1691 --
1692 --
1693 -- HISTORY
1694 -- 05/12/1999 tdonohoe created
1695 -- 03/02/2000 tdonohoe modified code to update Source Code details on the
1696 -- list entry table when the list association is updated.
1697 -- 02/11/2000 tdonohoe modified code to update STATUS_CODE to LOCKED when the
1698 -- sent out date is populated.
1699 -- End of Comments
1700
1701 PROCEDURE Update_ListHeader
1702 ( p_api_version IN NUMBER,
1703 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1704 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1705 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1706 x_return_status OUT NOCOPY VARCHAR2,
1707 x_msg_count OUT NOCOPY NUMBER,
1708 x_msg_data OUT NOCOPY VARCHAR2,
1709 p_listheader_rec IN list_header_rec_type
1710 ) IS
1711
1712 l_api_name CONSTANT VARCHAR2(30) := 'Update_ListHeader';
1713 l_api_version CONSTANT NUMBER := 1.0;
1714 -- Status Local Variables
1715 l_return_status VARCHAR2(1); -- Return value from procedures
1716 l_listheader_rec list_header_rec_type := p_listheader_rec;
1717 l_sqlerrm varchar2(600);
1718 l_sqlcode varchar2(100);
1719 l_msg_count number;
1720 l_msg_data varchar2(500);
1721 l_item_type varchar2(100) := 'AMSLISTG';
1722 l_item_key varchar2(100);
1723 cursor check_wf
1724 is select item_key
1725 from wf_item_activity_statuses
1726 where item_type = l_item_type
1727 and item_key like p_listheader_rec.list_header_id || '_%'
1728 and activity_status in ('ERROR','ACTIVE');
1729
1730 BEGIN
1731 -- Standard Start of API savepoint
1732 SAVEPOINT Update_listheaders_PVT;
1733 -- Standard call to check for call compatibility.
1734 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1735 p_api_version,
1736 l_api_name,
1737 G_PKG_NAME) THEN
1738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1739 END IF;
1740 -- Initialize message list IF p_init_msg_list is set to TRUE.
1741 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1742 FND_MSG_PUB.initialize;
1743 END IF;
1744 -- Debug Message
1745 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1746 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1747 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Start', TRUE);
1748 FND_MSG_PUB.Add;
1749 END IF; */
1750
1751 IF (AMS_DEBUG_HIGH_ON) THEN
1752 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Start');
1753 END IF;
1754
1755
1756 ----------------------------------------------------------
1757 -- Initialize API return status to success
1758 ----------------------------------------------------------
1759 x_return_status := FND_API.G_RET_STS_SUCCESS;
1760
1761 ----------------------------------------------------------
1762 -- replace g_miss_char/num/date with current column values
1763 ----------------------------------------------------------
1764 complete_listheader_rec(p_listheader_rec, l_listheader_rec);
1765
1766 IF (p_listheader_rec.USER_ENTERED_START_TIME <> fnd_api.g_miss_date
1770 p_listheader_rec.timezone_id <> FND_API.g_miss_num ) then
1767 AND p_listheader_rec.USER_ENTERED_START_TIME is not null)THEN
1768
1769 if (p_listheader_rec.timezone_id is not null AND
1771 AMS_UTILITY_PVT.Convert_Timezone(
1772 p_init_msg_list => p_init_msg_list,
1773 x_return_status => x_return_status,
1774 x_msg_count => x_msg_count,
1775 x_msg_data => x_msg_data,
1776 p_user_tz_id => l_listheader_rec.TIMEZONE_ID,
1777 p_in_time => l_listheader_rec.USER_ENTERED_START_TIME,
1778 p_convert_type => 'SYS',
1779 x_out_time => l_listheader_rec.MAIN_GEN_START_TIME);
1780 end if;
1781
1782 -- If any errors happen abort API.
1783 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1784 RAISE FND_API.G_EXC_ERROR;
1785 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788 END IF;
1789
1790 -- Debug Message
1791 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1792 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1793 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Done Timezone', TRUE);
1794 FND_MSG_PUB.Add;
1795 END IF; */
1796
1797 IF (AMS_DEBUG_HIGH_ON) THEN
1798 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Done timezone');
1799 END IF;
1800
1801
1802
1803 ----------------------- validate ----------------------
1804 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1805 Validate_list_items(
1806 p_listheader_rec => l_listheader_rec,
1807 p_validation_mode => JTF_PLSQL_API.g_update,
1808 x_return_status => x_return_status
1809 );
1810
1811 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1812 RAISE FND_API.g_exc_unexpected_error;
1813 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1814 RAISE FND_API.g_exc_error;
1815 END IF;
1816 END IF;
1817
1818 -- Debug Message
1819 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1820 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1821 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Item Level Validation', TRUE);
1822 FND_MSG_PUB.Add;
1823 END IF; */
1824
1825 IF (AMS_DEBUG_HIGH_ON) THEN
1826 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Item level validation');
1827 END IF;
1828
1829
1830 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1831 Validate_list_record(
1832 p_listheader_rec => p_listheader_rec,
1833 p_complete_rec => l_listheader_rec,
1834 x_return_status => x_return_status
1835 );
1836
1837 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1838 RAISE FND_API.g_exc_unexpected_error;
1839 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1840 RAISE FND_API.g_exc_error;
1841 END IF;
1842 END IF;
1843
1844 -- Perform the database operation
1845 /* ckapoor IF (AMS_DEBUG_LOW_ON) THEN
1846 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1847 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT - update ams_listheaders', TRUE);
1848 FND_MSG_PUB.Add;
1849 END IF; */
1850
1851 IF (AMS_DEBUG_LOW_ON) THEN
1852 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.update ams_listheaders');
1853 END IF;
1854
1855
1856 IF (l_listheader_rec.STATUS_CODE ='DRAFT' OR
1857 l_listheader_rec.STATUS_CODE ='ARCHIVED' OR
1858 l_listheader_rec.STATUS_CODE ='CANCELLED' ) THEN
1859
1860 open check_wf ;
1861 fetch check_wf into l_item_key;
1862 close check_wf ;
1863 if l_item_key is not null then
1864 begin
1865 WF_ENGINE.abortProcess(l_item_type ,
1866 l_item_key);
1867 exception
1868 when no_data_found then
1869 null;
1870 end;
1871 end if;
1872
1873 END IF;
1874
1875 IF(l_listheader_rec.STATUS_CODE ='CANCELLED' ) THEN
1876 -- Delete all existing entries for this list which are in the
1877 -- temporary table.--
1878 --------------------------------------------------------------------------
1879 DELETE FROM ams_list_tmp_entries
1880 WHERE list_header_id = l_listheader_rec.list_header_id;
1881
1882 ----------------------------------------------
1883 --Delete all existing entries for this list.--
1884 ----------------------------------------------
1885 DELETE FROM ams_list_entries
1886 WHERE list_header_id =l_listheader_rec.list_header_id;
1887
1888 ------------------------------------------------------------------------------
1889 --Delete all entries in the ams_list_src_type_usages table. --
1890 --These entries must be refreshed each time that a list is refreshed. --
1891 ------------------------------------------------------------------------------
1892 DELETE FROM ams_list_src_type_usages
1893 WHERE list_header_id =l_listheader_rec.list_header_id;
1894
1895 --clear associations
1896 l_listheader_rec.list_used_by_id:=0;
1900 l_listheader_rec.no_of_rows_active:=NULL;
1897 l_listheader_rec.arc_list_used_by:='NONE';
1898 --set size of list to be NULL
1899 l_listheader_rec.no_of_rows_in_list:=NULL;
1901 l_listheader_rec.no_of_rows_inactive:=NULL;
1902 l_listheader_rec.no_of_rows_duplicates:=NULL;
1903 END IF;
1904
1905
1906 UPDATE ams_list_headers_all
1907 SET
1908 last_update_date = sysdate
1909 ,last_updated_by = FND_GLOBAL.User_Id
1910 ,last_update_login = FND_GLOBAL.Conc_Login_Id
1911 ,object_version_number = l_listheader_rec.object_version_number + 1
1912 ,request_id = l_listheader_rec.request_id
1913 ,program_id = l_listheader_rec.program_id
1914 ,program_application_id = l_listheader_rec.program_application_id
1915 ,quantum = l_listheader_rec.quantum
1916 ,release_control_alg_id = l_listheader_rec.release_control_alg_id
1917 ,dialing_method = l_listheader_rec.dialing_method
1918 ,calling_calendar_id = l_listheader_rec.calling_calendar_id
1919 ,release_strategy = l_listheader_rec.release_strategy
1920 ,custom_setup_id = l_listheader_rec.custom_setup_id
1921 ,country = l_listheader_rec.country
1922 ,purge_flag = l_listheader_rec.PURGE_FLAG
1923 ,public_flag = l_listheader_rec.public_flag
1924 ,list_category = l_listheader_rec.list_category
1925 ,quota = l_listheader_rec.QUOTA
1926 ,quota_reset = l_listheader_rec.QUOTA_RESET
1927 ,recycling_alg_id = l_listheader_rec.RECYCLING_ALG_ID
1928 ,callback_priority_flag = l_listheader_rec.callback_priority_flag
1929 ,call_center_ready_flag = l_listheader_rec.call_center_ready_flag
1930 ,user_status_id = l_listheader_rec.user_status_id
1931 ,program_update_date = l_listheader_rec.program_update_date
1932 --,list_name = l_listheader_rec.list_name
1933 ,list_used_by_id = l_listheader_rec.list_used_by_id
1934 ,arc_list_used_by = l_listheader_rec.arc_list_used_by
1935 ,list_type = l_listheader_rec.list_type
1936 ,status_code = l_listheader_rec.status_code
1937 ,status_date = l_listheader_rec.status_date
1938 ,generation_type = l_listheader_rec.generation_type
1939 ,row_selection_type = l_listheader_rec.row_selection_type
1940 ,owner_user_id = l_listheader_rec.owner_user_id
1941 ,access_level = l_listheader_rec.access_level
1942 ,enable_log_flag = l_listheader_rec.enable_log_flag
1943 ,enable_word_replacement_flag = l_listheader_rec.enable_word_replacement_flag
1944 ,dedupe_during_generation_flag = l_listheader_rec.dedupe_during_generation_flag --added vbhandar 10/12/2000
1945 ,generate_control_group_flag = l_listheader_rec.generate_control_group_flag
1946 ,forecasted_start_date = l_listheader_rec.forecasted_start_date
1947 ,forecasted_end_date = l_listheader_rec.forecasted_end_date
1948 ,actual_end_date = l_listheader_rec.actual_end_date
1949 ,sent_out_date = l_listheader_rec.sent_out_date
1950 ,dedupe_start_date = l_listheader_rec.dedupe_start_date
1951 ,last_dedupe_date = l_listheader_rec.last_dedupe_date
1952 ,last_deduped_by_user_id = l_listheader_rec.last_deduped_by_user_id
1953 ,workflow_item_key = l_listheader_rec.workflow_item_key
1954 ,no_of_rows_duplicates = l_listheader_rec.no_of_rows_duplicates
1955 ,no_of_rows_min_requested = l_listheader_rec.no_of_rows_min_requested
1956 ,no_of_rows_max_requested = l_listheader_rec.no_of_rows_max_requested
1957 ,no_of_rows_in_list = l_listheader_rec.no_of_rows_in_list
1958 ,no_of_rows_in_ctrl_group = l_listheader_rec.no_of_rows_in_ctrl_group
1959 ,no_of_rows_active = l_listheader_rec.no_of_rows_active
1960 ,no_of_rows_inactive = l_listheader_rec.no_of_rows_inactive
1961 ,no_of_rows_manually_entered = l_listheader_rec.no_of_rows_manually_entered
1962 ,no_of_rows_do_not_call = l_listheader_rec.no_of_rows_do_not_call
1963 ,no_of_rows_do_not_mail = l_listheader_rec.no_of_rows_do_not_mail
1964 ,no_of_rows_random = l_listheader_rec.no_of_rows_random
1965 ,timezone_id = l_listheader_rec.timezone_id
1966 ,user_entered_start_time = l_listheader_rec.user_entered_start_time
1967 ,main_gen_start_time = l_listheader_rec.main_gen_start_time
1968 ,main_gen_end_time = l_listheader_rec.main_gen_end_time
1969 ,main_random_nth_row_selection = l_listheader_rec.main_random_nth_row_selection
1970 ,main_random_pct_row_selection = l_listheader_rec.main_random_pct_row_selection
1971 ,ctrl_random_nth_row_selection = l_listheader_rec.ctrl_random_nth_row_selection
1972 ,ctrl_random_pct_row_selection = l_listheader_rec.ctrl_random_pct_row_selection
1973 ,result_text = l_listheader_rec.result_text
1974 ,keywords = l_listheader_rec.keywords
1975 -- ,description = l_listheader_rec.description
1976 ,list_priority = l_listheader_rec.list_priority
1977 ,assign_person_id = l_listheader_rec.assign_person_id
1978 ,list_source = l_listheader_rec.list_source
1979 ,list_source_type = l_listheader_rec.list_source_type
1980 ,list_online_flag = l_listheader_rec.list_online_flag
1981 ,random_list_id = l_listheader_rec.random_list_id
1982 ,enabled_flag = l_listheader_rec.enabled_flag
1983 ,assigned_to = l_listheader_rec.assigned_to
1984 ,query_id = l_listheader_rec.query_id
1985 ,owner_person_id = l_listheader_rec.owner_person_id
1989 ,attribute3 = l_listheader_rec.attribute3
1986 ,attribute_category = l_listheader_rec.attribute_category
1987 ,attribute1 = l_listheader_rec.attribute1
1988 ,attribute2 = l_listheader_rec.attribute2
1990 ,attribute4 = l_listheader_rec.attribute4
1991 ,attribute5 = l_listheader_rec.attribute5
1992 ,attribute6 = l_listheader_rec.attribute6
1993 ,attribute7 = l_listheader_rec.attribute7
1994 ,attribute8 = l_listheader_rec.attribute8
1995 ,attribute9 = l_listheader_rec.attribute9
1996 ,attribute10 = l_listheader_rec.attribute10
1997 ,attribute11 = l_listheader_rec.attribute11
1998 ,attribute12 = l_listheader_rec.attribute12
1999 ,attribute13 = l_listheader_rec.attribute13
2000 ,attribute14 = l_listheader_rec.attribute14
2001 ,attribute15 = l_listheader_rec.attribute15
2002 ,no_of_rows_prev_contacted = l_listheader_rec.no_of_rows_prev_contacted
2003 ,apply_traffic_cop =l_listheader_rec.apply_traffic_cop
2004
2005 -- ckapoor R12 control group enhancements
2006
2007 ,CTRL_CONF_LEVEL =l_listheader_rec.CTRL_CONF_LEVEL
2008 ,CTRL_REQ_RESP_RATE =l_listheader_rec.CTRL_REQ_RESP_RATE
2009 ,CTRL_LIMIT_OF_ERROR =l_listheader_rec.CTRL_LIMIT_OF_ERROR
2010 ,STATUS_CODE_OLD =l_listheader_rec.STATUS_CODE_OLD
2011 ,CTRL_CONC_JOB_ID =l_listheader_rec.CTRL_CONC_JOB_ID
2012 ,CTRL_STATUS_CODE =l_listheader_rec.CTRL_STATUS_CODE
2013 ,CTRL_GEN_MODE =l_listheader_rec.CTRL_GEN_MODE
2014 ,APPLY_SUPPRESSION_FLAG =l_listheader_rec.APPLY_SUPPRESSION_FLAG
2015
2016 -- end ckapoor R12 control group enhancements
2017
2018
2019
2020 WHERE list_header_id = l_listheader_rec.list_header_id;
2021 -- AND object_version_number = l_listheader_rec.object_version_number;
2022
2023 IF (SQL%NOTFOUND)THEN
2024 ------------------------------------------------------------------
2025 -- Error, check the msg level and added an error message to the --
2026 -- API message list. --
2027 ------------------------------------------------------------------
2028 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2029 FND_MESSAGE.set_name('AMS', 'API_UNEXP_ERROR_IN_PROCESSING');
2030 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders API', TRUE);
2031 FND_MSG_PUB.Add;
2032 END IF;
2033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2034 END IF;
2035
2036 update AMS_LIST_HEADERS_ALL_TL set
2037 LIST_NAME = l_listheader_rec.LIST_NAME,
2038 DESCRIPTION = l_listheader_rec.DESCRIPTION,
2039 LAST_UPDATE_DATE = sysdate,
2040 LAST_UPDATE_BY = FND_GLOBAL.user_id,
2041 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
2042 SOURCE_LANG = userenv('LANG')
2043 where LIST_HEADER_ID = l_listheader_rec.LIST_HEADER_ID
2044 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2045
2046 IF (SQL%NOTFOUND)THEN
2047 ------------------------------------------------------------------
2048 -- Error, check the msg level and added an error message to the --
2049 -- API message list. --
2050 ------------------------------------------------------------------
2051 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2052 FND_MESSAGE.set_name('AMS', 'API_UNEXP_ERROR_IN_PROCESSING');
2053 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders API', TRUE);
2054 FND_MSG_PUB.Add;
2055 END IF;
2056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2057 END IF;
2058
2059 --added tdonohoe 03/02/2000
2060 -----------------------------------------------------------------------
2061 --If the List Association has changed then any existing list entries --
2062 --must be updated. --
2063 --The Procedure which is called checks if the list association is new--
2064 --only then will it perfrom an update of the list entries. --
2065 -----------------------------------------------------------------------
2066 IF(l_listheader_rec.ARC_LIST_USED_BY <>'NONE')THEN
2067 AMS_ListEntry_PVT.Update_ListEntry_Source_Code
2068 (p_api_version => 1.0,
2069 p_list_id => l_listheader_rec.list_header_id,
2070 x_return_status => x_return_status,
2071 x_msg_count => l_msg_count,
2072 x_msg_data => l_msg_data);
2073 END IF;
2074 --end added tdonohoe 03/02/2000
2075
2076
2077 -- Standard check of p_commit.
2078 IF FND_API.To_Boolean ( p_commit ) THEN
2079 COMMIT WORK;
2080 END IF;
2081
2082 -- Success Message
2083 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2084 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
2085 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders', TRUE);
2086 FND_MSG_PUB.Add;
2087 END IF;
2088
2089 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
2090 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2091 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: END', TRUE);
2092 FND_MSG_PUB.Add;
2093 END IF; */
2094
2098 p_data => x_msg_data,
2095 -- Standard call to get message count AND IF count is 1, get message info.
2096 FND_MSG_PUB.Count_AND_Get
2097 ( p_count => x_msg_count,
2099 p_encoded => FND_API.G_FALSE
2100 );
2101 EXCEPTION
2102 WHEN FND_API.G_EXC_ERROR THEN
2103 ROLLBACK TO Update_listheaders_PVT;
2104 x_return_status := FND_API.G_RET_STS_ERROR ;
2105 FND_MSG_PUB.Count_AND_Get
2106 ( p_count => x_msg_count,
2107 p_data => x_msg_data,
2108 p_encoded => FND_API.G_FALSE
2109 );
2110
2111
2112 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2113 ROLLBACK TO Update_listheaders_PVT;
2114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2115 FND_MSG_PUB.Count_AND_Get
2116 ( p_count => x_msg_count,
2117 p_data => x_msg_data,
2118 p_encoded => FND_API.G_FALSE
2119 );
2120 WHEN OTHERS THEN
2121 ROLLBACK TO Update_listheaders_PVT;
2122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2123 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2124 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2125 END IF;
2126
2127 FND_MSG_PUB.Count_AND_Get
2128 ( p_count => x_msg_count,
2129 p_data => x_msg_data,
2130 p_encoded => FND_API.G_FALSE
2131 );
2132
2133 END Update_listheader;
2134
2135
2136 -- Start of Comments
2137 --
2138 -- NAME
2139 -- Delete_listheader
2140 --
2141 -- PURPOSE
2142 -- This procedure deletes a list header record that satisfy caller needs
2143 --
2144 -- NOTES
2145 -- Deletes from The following tables Ams_List_Src_Type_Usages,
2146 -- Ams_List_Rule_Usages,
2147 -- Ams_List_Entries,
2148 -- Ams_List_Select_Actions
2149 -- Ams_List_Headers_All.
2150 --
2151 -- HISTORY
2152 -- 05/12/1999 tdonohoe created
2153 -- End of Comments
2154
2155 PROCEDURE Delete_ListHeader
2156 ( p_api_version IN NUMBER,
2157 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2158 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2159 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2160 x_return_status OUT NOCOPY VARCHAR2,
2161 x_msg_count OUT NOCOPY NUMBER,
2162 x_msg_data OUT NOCOPY VARCHAR2,
2163 p_listheader_id IN number) IS
2164
2165 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListHeader';
2166 l_api_version CONSTANT NUMBER := 1.0;
2167
2168 -- Status Local Variables
2169 l_return_status VARCHAR2(1); -- Return value from procedures
2170 l_list_header_id NUMBER := p_listheader_id;
2171 l_return_val VARCHAR2(1);
2172
2173 BEGIN
2174 -- Standard Start of API savepoint
2175 SAVEPOINT Delete_listheader_PVT;
2176
2177 -- Standard call to check for call compatibility.
2178 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2179 p_api_version,
2180 l_api_name,
2181 G_PKG_NAME) THEN
2182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2183 END IF;
2184
2185 -- Initialize message list IF p_init_msg_list is set to TRUE.
2186 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2187 FND_MSG_PUB.initialize;
2188 END IF;
2189
2190 -- Debug Message
2191 IF (AMS_DEBUG_HIGH_ON) THEN
2192 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2193 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheaders: Start', TRUE);
2194 FND_MSG_PUB.Add;
2195 END IF;
2196
2197 -- Initialize API return status to success
2198 x_return_status := FND_API.G_RET_STS_SUCCESS;
2199
2200
2201 -- Check required parameters
2202 IF (l_list_header_id = FND_API.G_MISS_NUM OR l_list_header_id IS NULL )THEN
2203 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2204 FND_MESSAGE.Set_Name('AMS', 'API_INCOMPLETE_INFO');
2205 FND_MESSAGE.Set_Token ('PARAM', 'listheader_id', FALSE);
2206 FND_MSG_PUB.Add;
2207 END IF;
2208 RAISE FND_API.G_EXC_ERROR;
2209 END IF;
2210
2211 -- Perform the database operation
2212
2213 IF (AMS_DEBUG_LOW_ON) THEN
2214 NULL;
2215 END IF;
2216
2217 DELETE FROM ams_list_src_type_usages
2218 WHERE list_header_id = l_list_header_id;
2219
2220 DELETE FROM ams_list_rule_usages
2221 WHERE list_header_id = l_list_header_id;
2222
2223 DELETE FROM ams_list_entries
2224 WHERE list_header_id = l_list_header_id;
2225
2226 DELETE FROM ams_list_select_actions
2227 WHERE list_header_id = l_list_header_id;
2228
2229 DELETE FROM ams_list_headers_all
2230 WHERE list_header_id = l_list_header_id;
2231
2232
2233 -- Standard check of p_commit.
2237
2234 IF FND_API.To_Boolean ( p_commit ) THEN
2235 COMMIT WORK;
2236 END IF;
2238 -- Success Message
2239 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2240 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
2241 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheaders', TRUE);
2242 FND_MSG_PUB.Add;
2243 END IF;
2244
2245
2246 IF (AMS_DEBUG_HIGH_ON) THEN
2247 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2248 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheader: END', TRUE);
2249 FND_MSG_PUB.Add;
2250 END IF;
2251
2252
2253 -- Standard call to get message count AND IF count is 1, get message info.
2254 FND_MSG_PUB.Count_AND_Get
2255 ( p_count => x_msg_count,
2256 p_data => x_msg_data,
2257 p_encoded => FND_API.G_FALSE
2258 );
2259 EXCEPTION
2260 WHEN FND_API.G_EXC_ERROR THEN
2261 ROLLBACK TO Delete_listheader_PVT;
2262 x_return_status := FND_API.G_RET_STS_ERROR ;
2263
2264 FND_MSG_PUB.Count_AND_Get
2265 ( p_count => x_msg_count,
2266 p_data => x_msg_data,
2267 p_encoded => FND_API.G_FALSE
2268 );
2269
2270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2271 ROLLBACK TO Delete_listheader_PVT;
2272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2273
2274 FND_MSG_PUB.Count_AND_Get
2275 ( p_count => x_msg_count,
2276 p_data => x_msg_data,
2277 p_encoded => FND_API.G_FALSE
2278 );
2279
2280
2281 WHEN OTHERS THEN
2282 ROLLBACK TO Delete_listheader_PVT;
2283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2284 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2285 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2286 END IF;
2287
2288 FND_MSG_PUB.Count_AND_Get
2289 ( p_count => x_msg_count,
2290 p_data => x_msg_data,
2291 p_encoded => FND_API.G_FALSE
2292 );
2293
2294 END Delete_listheader;
2295
2296
2297
2298 -- Start of Comments
2299 --
2300 -- NAME
2301 -- Lock_listheader
2302 --
2303 -- PURPOSE
2304 -- This procedure is to lock a list header record that satisfy caller needs
2305 --
2306 -- NOTES
2307 --
2308 --
2309 -- HISTORY
2310 -- 05/13/1999 tdonohoe created
2311 -- End of Comments
2312
2313
2314 PROCEDURE Lock_ListHeader
2315 ( p_api_version IN NUMBER,
2316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2317 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2318 x_return_status OUT NOCOPY VARCHAR2,
2319 x_msg_count OUT NOCOPY NUMBER,
2320 x_msg_data OUT NOCOPY VARCHAR2,
2321 p_listheader_id IN NUMBER,
2322 p_object_version IN NUMBER
2323 ) IS
2324
2325
2326 l_api_name CONSTANT VARCHAR2(30) := 'Lock_ListHeader';
2327 l_api_version CONSTANT NUMBER := 1.0;
2328 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2329
2330 l_list_header_id NUMBER;
2331
2332 CURSOR c_list_header_id IS
2333 SELECT list_header_id
2334 FROM ams_list_headers_all
2335 WHERE list_header_id = p_listheader_id
2336 AND object_version_number = p_object_version
2337 FOR UPDATE OF list_header_id NOWAIT;
2338
2339 BEGIN
2340
2341 -------------------- initialize ------------------------
2342 IF (AMS_DEBUG_HIGH_ON) THEN
2343
2344 AMS_Utility_PVT.debug_message(l_full_name||': start');
2345 END IF;
2346
2347 IF FND_API.to_boolean(p_init_msg_list) THEN
2348 FND_MSG_PUB.initialize;
2349 END IF;
2350
2351 IF NOT FND_API.compatible_api_call(
2352 l_api_version,
2353 p_api_version,
2354 l_api_name,
2355 g_pkg_name
2356 ) THEN
2357 RAISE FND_API.g_exc_unexpected_error;
2358 END IF;
2359
2360 x_return_status := FND_API.G_RET_STS_SUCCESS;
2361
2362 ------------------------ lock -------------------------
2363 IF (AMS_DEBUG_HIGH_ON) THEN
2364
2365 AMS_Utility_PVT.debug_message(l_full_name||': lock');
2366 END IF;
2367
2368 OPEN c_list_header_id;
2369 FETCH c_list_header_id INTO l_list_header_id;
2370 IF (c_list_header_id%NOTFOUND) THEN
2371 CLOSE c_list_header_id;
2372 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2373 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2374 FND_MSG_PUB.add;
2375 END IF;
2376 RAISE FND_API.g_exc_error;
2377 END IF;
2378 CLOSE c_list_header_id;
2379
2380 -------------------- finish --------------------------
2381 FND_MSG_PUB.count_and_get(
2382 p_encoded => FND_API.g_false,
2383 p_count => x_msg_count,
2384 p_data => x_msg_data
2385 );
2386
2390
2387 IF (AMS_DEBUG_HIGH_ON) THEN
2388
2389
2391 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2392
2393 END IF;
2394
2395 EXCEPTION
2396
2397 WHEN AMS_Utility_PVT.resource_locked THEN
2398 x_return_status := FND_API.g_ret_sts_error;
2399 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2400 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
2401 FND_MSG_PUB.add;
2402 END IF;
2403
2404 FND_MSG_PUB.count_and_get(
2405 p_encoded => FND_API.g_false,
2406 p_count => x_msg_count,
2407 p_data => x_msg_data
2408 );
2409
2410 WHEN FND_API.g_exc_error THEN
2411 x_return_status := FND_API.g_ret_sts_error;
2412 FND_MSG_PUB.count_and_get(
2413 p_encoded => FND_API.g_false,
2414 p_count => x_msg_count,
2415 p_data => x_msg_data
2416 );
2417
2418 WHEN FND_API.g_exc_unexpected_error THEN
2419 x_return_status := FND_API.g_ret_sts_unexp_error ;
2420 FND_MSG_PUB.count_and_get(
2421 p_encoded => FND_API.g_false,
2422 p_count => x_msg_count,
2423 p_data => x_msg_data
2424 );
2425
2426 WHEN OTHERS THEN
2427 x_return_status := FND_API.g_ret_sts_unexp_error ;
2428 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2429 THEN
2430 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2431 END IF;
2432
2433 FND_MSG_PUB.count_and_get(
2434 p_encoded => FND_API.g_false,
2435 p_count => x_msg_count,
2436 p_data => x_msg_data
2437 );
2438
2439 END Lock_listheader;
2440
2441
2442 ---------------------------------------------------------------------
2443 -- PROCEDURE
2444 -- Init_ListHeader_rec
2445 --
2446 -- HISTORY
2447 -- 10/11/99 tdonohoe Created.
2448 ---------------------------------------------------------------------
2449 PROCEDURE Init_ListHeader_Rec(
2450 x_listheader_rec OUT NOCOPY list_header_rec_type
2451 )
2452 IS
2453 BEGIN
2454
2455 x_listheader_rec.LIST_HEADER_ID := FND_API.g_miss_num;
2456 x_listheader_rec.LAST_UPDATE_DATE := FND_API.g_miss_date;
2457 x_listheader_rec.LAST_UPDATED_BY := FND_API.g_miss_num;
2458 x_listheader_rec.CREATION_DATE := FND_API.g_miss_date;
2459 x_listheader_rec.CREATED_BY := FND_API.g_miss_num;
2460 x_listheader_rec.LAST_UPDATE_LOGIN := FND_API.g_miss_num;
2461 x_listheader_rec.OBJECT_VERSION_NUMBER := FND_API.g_miss_num;
2462 x_listheader_rec.REQUEST_ID := FND_API.g_miss_num;
2463 x_listheader_rec.PROGRAM_ID := FND_API.g_miss_num;
2464 x_listheader_rec.PROGRAM_APPLICATION_ID := FND_API.g_miss_num;
2465 x_listheader_rec.PROGRAM_UPDATE_DATE := FND_API.g_miss_date;
2466 x_listheader_rec.VIEW_APPLICATION_ID := FND_API.g_miss_num;
2467 x_listheader_rec.LIST_NAME := FND_API.g_miss_char;
2468 x_listheader_rec.LIST_USED_BY_ID := FND_API.g_miss_num;
2469 x_listheader_rec.ARC_LIST_USED_BY := FND_API.g_miss_char;
2470 x_listheader_rec.LIST_TYPE := FND_API.g_miss_char;
2471 x_listheader_rec.STATUS_CODE := FND_API.g_miss_char;
2472 x_listheader_rec.STATUS_DATE := FND_API.g_miss_date;
2473 x_listheader_rec.GENERATION_TYPE := FND_API.g_miss_char;
2474 x_listheader_rec.ROW_SELECTION_TYPE := FND_API.g_miss_char;
2475 x_listheader_rec.OWNER_USER_ID := FND_API.g_miss_num;
2476 x_listheader_rec.ACCESS_LEVEL := FND_API.g_miss_char;
2477 x_listheader_rec.ENABLE_LOG_FLAG := FND_API.g_miss_char;
2478 x_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG := FND_API.g_miss_char;
2479 x_listheader_rec.ENABLE_PARALLEL_DML_FLAG := FND_API.g_miss_char;
2480 x_listheader_rec.DEDUPE_DURING_GENERATION_FLAG := FND_API.g_miss_char;
2481 x_listheader_rec.GENERATE_CONTROL_GROUP_FLAG := FND_API.g_miss_char;
2482 x_listheader_rec.LAST_GENERATION_SUCCESS_FLAG := FND_API.g_miss_char;
2483 x_listheader_rec.FORECASTED_START_DATE := FND_API.g_miss_date;
2484 x_listheader_rec.FORECASTED_END_DATE := FND_API.g_miss_date;
2485 x_listheader_rec.ACTUAL_END_DATE := FND_API.g_miss_date;
2486 x_listheader_rec.SENT_OUT_DATE := FND_API.g_miss_date;
2487 x_listheader_rec.DEDUPE_START_DATE := FND_API.g_miss_date;
2488 x_listheader_rec.LAST_DEDUPE_DATE := FND_API.g_miss_date;
2489 x_listheader_rec.LAST_DEDUPED_BY_USER_ID := FND_API.g_miss_num;
2490 x_listheader_rec.WORKFLOW_ITEM_KEY := FND_API.g_miss_num;
2491 x_listheader_rec.NO_OF_ROWS_DUPLICATES := FND_API.g_miss_num;
2492 x_listheader_rec.NO_OF_ROWS_MIN_REQUESTED := FND_API.g_miss_num;
2493 x_listheader_rec.NO_OF_ROWS_MAX_REQUESTED := FND_API.g_miss_num;
2494 x_listheader_rec.NO_OF_ROWS_IN_LIST := FND_API.g_miss_num;
2495 x_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP := FND_API.g_miss_num;
2496 x_listheader_rec.NO_OF_ROWS_ACTIVE := FND_API.g_miss_num;
2497 x_listheader_rec.NO_OF_ROWS_INACTIVE := FND_API.g_miss_num;
2498 x_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED := FND_API.g_miss_num;
2499 x_listheader_rec.NO_OF_ROWS_DO_NOT_CALL := FND_API.g_miss_num;
2503 x_listheader_rec.TIMEZONE_ID := FND_API.g_miss_num;
2500 x_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL := FND_API.g_miss_num;
2501 x_listheader_rec.NO_OF_ROWS_RANDOM := FND_API.g_miss_num;
2502 x_listheader_rec.ORG_ID := FND_API.g_miss_num;
2504 x_listheader_rec.USER_ENTERED_START_TIME := FND_API.g_miss_date;
2505 x_listheader_rec.MAIN_GEN_START_TIME := FND_API.g_miss_date;
2506 x_listheader_rec.MAIN_GEN_END_TIME := FND_API.g_miss_date;
2507 x_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION := FND_API.g_miss_num;
2508 x_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION := FND_API.g_miss_num;
2509 x_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION := FND_API.g_miss_num;
2510 x_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION := FND_API.g_miss_num;
2511 x_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID := FND_API.g_miss_char;
2512 x_listheader_rec.RESULT_TEXT := FND_API.g_miss_char;
2513 x_listheader_rec.KEYWORDS := FND_API.g_miss_char;
2514 x_listheader_rec.DESCRIPTION := FND_API.g_miss_char;
2515 x_listheader_rec.LIST_PRIORITY := FND_API.g_miss_num;
2516 x_listheader_rec.ASSIGN_PERSON_ID := FND_API.g_miss_num;
2517 x_listheader_rec.LIST_SOURCE := FND_API.g_miss_char;
2518 x_listheader_rec.LIST_SOURCE_TYPE := FND_API.g_miss_char;
2519 x_listheader_rec.LIST_ONLINE_FLAG := FND_API.g_miss_char;
2520 x_listheader_rec.RANDOM_LIST_ID := FND_API.g_miss_num;
2521 x_listheader_rec.ENABLED_FLAG := FND_API.g_miss_char;
2522 x_listheader_rec.ASSIGNED_TO := FND_API.g_miss_num;
2523 x_listheader_rec.QUERY_ID := FND_API.g_miss_num;
2524 x_listheader_rec.OWNER_PERSON_ID := FND_API.g_miss_num;
2525 x_listheader_rec.ATTRIBUTE_CATEGORY := FND_API.g_miss_char;
2526 x_listheader_rec.ATTRIBUTE1 := FND_API.g_miss_char;
2527 x_listheader_rec.ATTRIBUTE2 := FND_API.g_miss_char;
2528 x_listheader_rec.ATTRIBUTE3 := FND_API.g_miss_char;
2529 x_listheader_rec.ATTRIBUTE4 := FND_API.g_miss_char;
2530 x_listheader_rec.ATTRIBUTE5 := FND_API.g_miss_char;
2531 x_listheader_rec.ATTRIBUTE6 := FND_API.g_miss_char;
2532 x_listheader_rec.ATTRIBUTE7 := FND_API.g_miss_char;
2533 x_listheader_rec.ATTRIBUTE8 := FND_API.g_miss_char;
2534 x_listheader_rec.ATTRIBUTE9 := FND_API.g_miss_char;
2535 x_listheader_rec.ATTRIBUTE10 := FND_API.g_miss_char;
2536 x_listheader_rec.ATTRIBUTE11 := FND_API.g_miss_char;
2537 x_listheader_rec.ATTRIBUTE12 := FND_API.g_miss_char;
2538 x_listheader_rec.ATTRIBUTE13 := FND_API.g_miss_char;
2539 x_listheader_rec.ATTRIBUTE14 := FND_API.g_miss_char;
2540 x_listheader_rec.ATTRIBUTE15 := FND_API.g_miss_char;
2541 x_listheader_rec.QUANTUM := FND_API.g_miss_num;
2542 x_listheader_rec.RELEASE_CONTROL_ALG_ID :=FND_API.g_miss_num;
2543 x_listheader_rec.DIALING_METHOD := FND_API.g_miss_char;
2544 x_listheader_rec.CALLING_CALENDAR_ID :=FND_API.g_miss_num;
2545 x_listheader_rec.release_strategy := FND_API.g_miss_char;
2546 x_listheader_rec.custom_setup_id :=FND_API.g_miss_num;
2547 x_listheader_rec.country :=FND_API.g_miss_num;
2548 x_listheader_rec.PURGE_FLAG := FND_API.g_miss_char ;
2549 x_listheader_rec.PUBLIC_FLAG := FND_API.g_miss_char ;
2550 x_listheader_rec.LIST_CATEGORY := FND_API.g_miss_char ;
2551 x_listheader_rec.QUOTA := FND_API.g_miss_num ;
2552 x_listheader_rec.QUOTA_RESET := FND_API.g_miss_num ;
2553 x_listheader_rec.RECYCLING_ALG_ID := FND_API.g_miss_num ;
2554 x_listheader_rec.CALLBACK_PRIORITY_FLAG := FND_API.g_miss_char;
2555 x_listheader_rec.CALL_CENTER_READY_FLAG := FND_API.g_miss_char;
2556 x_listheader_rec.USER_STATUS_ID := FND_API.g_miss_num;
2557 x_listheader_rec.NO_OF_ROWS_prev_contacted := FND_API.g_miss_num;
2558 x_listheader_rec.APPLY_TRAFFIC_COP := FND_API.g_miss_char;
2559
2560 -- ckapoor R12 copy target group enhancement
2561
2562 x_listheader_rec.CTRL_CONF_LEVEL := FND_API.g_miss_num ;
2563 x_listheader_rec.CTRL_REQ_RESP_RATE := FND_API.g_miss_num ;
2564 x_listheader_rec.CTRL_LIMIT_OF_ERROR := FND_API.g_miss_num ;
2565 x_listheader_rec.STATUS_CODE_OLD := FND_API.g_miss_char;
2566 x_listheader_rec.CTRL_CONC_JOB_ID := FND_API.g_miss_num;
2567 x_listheader_rec.CTRL_STATUS_CODE := FND_API.g_miss_char;
2568 x_listheader_rec.CTRL_GEN_MODE := FND_API.g_miss_char;
2569 x_listheader_rec.APPLY_SUPPRESSION_FLAG := FND_API.g_miss_char;
2570
2571 -- end ckapoor
2572
2573 END Init_ListHeader_rec;
2574
2575
2576 ---------------------------------------------------------------------
2577 -- PROCEDURE
2578 -- Complete_ListHeader_rec
2579 --
2580 -- HISTORY
2581 -- 10/11/99 tdonohoe Created.
2582 ---------------------------------------------------------------------
2583 PROCEDURE Complete_ListHeader_rec(
2584 p_listheader_rec IN list_header_rec_type,
2585 x_complete_rec OUT NOCOPY list_header_rec_type
2586 )
2587 IS
2588
2589 CURSOR c_listheader IS
2590 SELECT *
2591 FROM ams_list_headers_vl
2592 WHERE list_header_id = p_listheader_rec.list_header_id;
2593
2594 l_listheader_rec c_listheader%ROWTYPE;
2595
2596 BEGIN
2597
2598 x_complete_rec := p_listheader_rec;
2599 OPEN c_listheader;
2600 FETCH c_listheader INTO l_listheader_rec;
2604 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2601 IF c_listheader%NOTFOUND THEN
2602 CLOSE c_listheader;
2603 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2605 FND_MSG_PUB.add;
2606 END IF;
2607 RAISE FND_API.g_exc_error;
2608 END IF;
2609 CLOSE c_listheader;
2610
2611
2612 IF p_listheader_rec.LIST_HEADER_ID = FND_API.g_miss_num THEN
2613 x_complete_rec.LIST_HEADER_ID := l_listheader_rec.LIST_HEADER_ID;
2614 END IF;
2615 IF p_listheader_rec.LAST_UPDATE_DATE = FND_API.g_miss_date THEN
2616 x_complete_rec.LAST_UPDATE_DATE := l_listheader_rec.LAST_UPDATE_DATE;
2617 END IF;
2618 IF p_listheader_rec.LAST_UPDATED_BY = FND_API.g_miss_num THEN
2619 x_complete_rec.LAST_UPDATED_BY := l_listheader_rec.LAST_UPDATED_BY;
2620 END IF;
2621 IF p_listheader_rec.CREATION_DATE = FND_API.g_miss_date THEN
2622 x_complete_rec.CREATION_DATE := l_listheader_rec.CREATION_DATE;
2623 END IF;
2624 IF p_listheader_rec.CREATED_BY = FND_API.g_miss_num THEN
2625 x_complete_rec.CREATED_BY := l_listheader_rec.CREATED_BY;
2626 END IF;
2627 IF p_listheader_rec.LAST_UPDATE_LOGIN = FND_API.g_miss_num THEN
2628 x_complete_rec.LAST_UPDATE_LOGIN := l_listheader_rec.LAST_UPDATE_LOGIN;
2629 END IF;
2630 IF p_listheader_rec.OBJECT_VERSION_NUMBER = FND_API.g_miss_num THEN
2631 x_complete_rec.OBJECT_VERSION_NUMBER := l_listheader_rec.OBJECT_VERSION_NUMBER ;
2632 END IF;
2633 IF p_listheader_rec.REQUEST_ID = FND_API.g_miss_num THEN
2634 x_complete_rec.REQUEST_ID := l_listheader_rec.REQUEST_ID;
2635 END IF;
2636 IF p_listheader_rec.PROGRAM_ID = FND_API.g_miss_num THEN
2637 x_complete_rec.PROGRAM_ID := l_listheader_rec.PROGRAM_ID ;
2638 END IF;
2639 IF p_listheader_rec.PROGRAM_APPLICATION_ID = FND_API.g_miss_num THEN
2640 x_complete_rec.PROGRAM_APPLICATION_ID := l_listheader_rec.PROGRAM_APPLICATION_ID;
2641 END IF;
2642 IF p_listheader_rec.PROGRAM_UPDATE_DATE = FND_API.g_miss_date THEN
2643 x_complete_rec.PROGRAM_UPDATE_DATE := l_listheader_rec.PROGRAM_UPDATE_DATE;
2644 END IF;
2645 IF p_listheader_rec.VIEW_APPLICATION_ID = FND_API.g_miss_num THEN
2646 x_complete_rec.VIEW_APPLICATION_ID := l_listheader_rec.VIEW_APPLICATION_ID;
2647 END IF;
2648
2649 IF p_listheader_rec.LIST_NAME = FND_API.g_miss_char THEN
2650 x_complete_rec.LIST_NAME := l_listheader_rec.LIST_NAME;
2651 END IF;
2652 IF p_listheader_rec.LIST_USED_BY_ID = FND_API.g_miss_num THEN
2653 x_complete_rec.LIST_USED_BY_ID := l_listheader_rec.LIST_USED_BY_ID ;
2654 END IF;
2655 IF p_listheader_rec.ARC_LIST_USED_BY = FND_API.g_miss_char THEN
2656 x_complete_rec.ARC_LIST_USED_BY := l_listheader_rec.ARC_LIST_USED_BY;
2657 END IF;
2658 IF p_listheader_rec.LIST_TYPE = FND_API.g_miss_char THEN
2659 x_complete_rec.LIST_TYPE := l_listheader_rec.LIST_TYPE;
2660 END IF;
2661
2662 IF p_listheader_rec.STATUS_DATE = FND_API.g_miss_date THEN
2663 x_complete_rec.STATUS_DATE := l_listheader_rec.STATUS_DATE ;
2664 END IF;
2665 IF p_listheader_rec.GENERATION_TYPE = FND_API.g_miss_char THEN
2666 x_complete_rec.GENERATION_TYPE := l_listheader_rec.GENERATION_TYPE;
2667 END IF;
2668 IF p_listheader_rec.ROW_SELECTION_TYPE = FND_API.g_miss_char THEN
2669 x_complete_rec.ROW_SELECTION_TYPE := l_listheader_rec.ROW_SELECTION_TYPE;
2670 END IF;
2671 IF p_listheader_rec.OWNER_USER_ID = FND_API.g_miss_num THEN
2672 x_complete_rec.OWNER_USER_ID := l_listheader_rec.OWNER_USER_ID;
2673 END IF;
2674 IF p_listheader_rec.ACCESS_LEVEL = FND_API.g_miss_char THEN
2675 x_complete_rec.ACCESS_LEVEL := l_listheader_rec.ACCESS_LEVEL;
2676 END IF;
2677 IF p_listheader_rec.ENABLE_LOG_FLAG = FND_API.g_miss_char THEN
2678 x_complete_rec.ENABLE_LOG_FLAG := l_listheader_rec.ENABLE_LOG_FLAG;
2679 END IF;
2680 IF p_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG = FND_API.g_miss_char THEN
2681 x_complete_rec.ENABLE_WORD_REPLACEMENT_FLAG := l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG ;
2682 END IF;
2683 IF p_listheader_rec.ENABLE_PARALLEL_DML_FLAG = FND_API.g_miss_char THEN
2684 x_complete_rec.ENABLE_PARALLEL_DML_FLAG := l_listheader_rec.ENABLE_PARALLEL_DML_FLAG;
2685 END IF;
2686 IF p_listheader_rec.DEDUPE_DURING_GENERATION_FLAG = FND_API.g_miss_char THEN
2687 x_complete_rec.DEDUPE_DURING_GENERATION_FLAG := l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG;
2688 END IF;
2689 IF p_listheader_rec.GENERATE_CONTROL_GROUP_FLAG = FND_API.g_miss_char THEN
2690 x_complete_rec.GENERATE_CONTROL_GROUP_FLAG := l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG ;
2691 END IF;
2692 IF p_listheader_rec.LAST_GENERATION_SUCCESS_FLAG = FND_API.g_miss_char THEN
2693 x_complete_rec.LAST_GENERATION_SUCCESS_FLAG := l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG;
2694 END IF;
2695 IF p_listheader_rec.FORECASTED_START_DATE = FND_API.g_miss_date THEN
2696 x_complete_rec.FORECASTED_START_DATE := l_listheader_rec.FORECASTED_START_DATE ;
2697 END IF;
2698 IF p_listheader_rec.FORECASTED_END_DATE = FND_API.g_miss_date THEN
2699 x_complete_rec.FORECASTED_END_DATE := l_listheader_rec.FORECASTED_END_DATE;
2700 END IF;
2704 IF p_listheader_rec.SENT_OUT_DATE = FND_API.g_miss_date THEN
2701 IF p_listheader_rec.ACTUAL_END_DATE = FND_API.g_miss_date THEN
2702 x_complete_rec.ACTUAL_END_DATE := l_listheader_rec.ACTUAL_END_DATE;
2703 END IF;
2705 x_complete_rec.SENT_OUT_DATE := l_listheader_rec.SENT_OUT_DATE;
2706 END IF;
2707 IF p_listheader_rec.DEDUPE_START_DATE = FND_API.g_miss_date THEN
2708 x_complete_rec.DEDUPE_START_DATE := l_listheader_rec.DEDUPE_START_DATE ;
2709 END IF;
2710 IF p_listheader_rec.LAST_DEDUPE_DATE = FND_API.g_miss_date THEN
2711 x_complete_rec.LAST_DEDUPE_DATE := l_listheader_rec.LAST_DEDUPE_DATE;
2712 END IF;
2713 IF p_listheader_rec.LAST_DEDUPED_BY_USER_ID = FND_API.g_miss_num THEN
2714 x_complete_rec.LAST_DEDUPED_BY_USER_ID := l_listheader_rec.LAST_DEDUPED_BY_USER_ID ;
2715 END IF;
2716 IF p_listheader_rec.WORKFLOW_ITEM_KEY = FND_API.g_miss_num THEN
2717 x_complete_rec.WORKFLOW_ITEM_KEY := l_listheader_rec.WORKFLOW_ITEM_KEY ;
2718 END IF;
2719 IF p_listheader_rec.NO_OF_ROWS_DUPLICATES = FND_API.g_miss_num THEN
2720 x_complete_rec.NO_OF_ROWS_DUPLICATES := l_listheader_rec.NO_OF_ROWS_DUPLICATES;
2721 END IF;
2722 IF p_listheader_rec.NO_OF_ROWS_MIN_REQUESTED = FND_API.g_miss_num THEN
2723 x_complete_rec.NO_OF_ROWS_MIN_REQUESTED := l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED;
2724 END IF;
2725 IF p_listheader_rec.NO_OF_ROWS_MAX_REQUESTED = FND_API.g_miss_num THEN
2726 x_complete_rec.NO_OF_ROWS_MAX_REQUESTED := l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED ;
2727 END IF;
2728 IF p_listheader_rec.NO_OF_ROWS_IN_LIST = FND_API.g_miss_num THEN
2729 x_complete_rec.NO_OF_ROWS_IN_LIST := l_listheader_rec.NO_OF_ROWS_IN_LIST;
2730 END IF;
2731 IF p_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP = FND_API.g_miss_num THEN
2732 x_complete_rec.NO_OF_ROWS_IN_CTRL_GROUP := l_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP;
2733 END IF;
2734 IF p_listheader_rec.NO_OF_ROWS_ACTIVE = FND_API.g_miss_num THEN
2735 x_complete_rec.NO_OF_ROWS_ACTIVE := l_listheader_rec.NO_OF_ROWS_ACTIVE;
2736 END IF;
2737 IF p_listheader_rec.NO_OF_ROWS_INACTIVE = FND_API.g_miss_num THEN
2738 x_complete_rec.NO_OF_ROWS_INACTIVE := l_listheader_rec.NO_OF_ROWS_INACTIVE;
2739 END IF;
2740 IF p_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED = FND_API.g_miss_num THEN
2741 x_complete_rec.NO_OF_ROWS_MANUALLY_ENTERED := l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED;
2742 END IF;
2743 IF p_listheader_rec.NO_OF_ROWS_DO_NOT_CALL = FND_API.g_miss_num THEN
2744 x_complete_rec.NO_OF_ROWS_DO_NOT_CALL := l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL ;
2745 END IF;
2746 IF p_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL = FND_API.g_miss_num THEN
2747 x_complete_rec.NO_OF_ROWS_DO_NOT_MAIL := l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL;
2748 END IF;
2749 IF p_listheader_rec.NO_OF_ROWS_RANDOM = FND_API.g_miss_num THEN
2750 x_complete_rec.NO_OF_ROWS_RANDOM := l_listheader_rec.NO_OF_ROWS_RANDOM;
2751 END IF;
2752 IF p_listheader_rec.ORG_ID = FND_API.g_miss_num THEN
2753 x_complete_rec.ORG_ID := l_listheader_rec.ORG_ID;
2754 END IF;
2755 IF p_listheader_rec.TIMEZONE_ID = FND_API.g_miss_num THEN
2756 x_complete_rec.TIMEZONE_ID := l_listheader_rec.TIMEZONE_ID;
2757 END IF;
2758 IF p_listheader_rec.USER_ENTERED_START_TIME = FND_API.g_miss_date THEN
2759 x_complete_rec.USER_ENTERED_START_TIME := l_listheader_rec.USER_ENTERED_START_TIME ;
2760 END IF;
2761 IF p_listheader_rec.MAIN_GEN_START_TIME = FND_API.g_miss_date THEN
2762 x_complete_rec.MAIN_GEN_START_TIME := l_listheader_rec.MAIN_GEN_START_TIME ;
2763 END IF;
2764 IF p_listheader_rec.MAIN_GEN_END_TIME = FND_API.g_miss_date THEN
2765 x_complete_rec.MAIN_GEN_END_TIME := l_listheader_rec.MAIN_GEN_END_TIME ;
2766 END IF;
2767 IF p_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION = FND_API.g_miss_num THEN
2768 x_complete_rec.MAIN_RANDOM_NTH_ROW_SELECTION := l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION;
2769 END IF;
2770 IF p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION = FND_API.g_miss_num THEN
2771 x_complete_rec.MAIN_RANDOM_PCT_ROW_SELECTION := l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION ;
2772 END IF;
2773 IF p_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION = FND_API.g_miss_num THEN
2774 x_complete_rec.CTRL_RANDOM_NTH_ROW_SELECTION := l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION ;
2775 END IF;
2776 IF p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION = FND_API.g_miss_num THEN
2777 x_complete_rec.CTRL_RANDOM_PCT_ROW_SELECTION := l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION ;
2778 END IF;
2779 IF p_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID = FND_API.g_miss_char THEN
2780 x_complete_rec.REPEAT_SOURCE_LIST_HEADER_ID := l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID;
2781 END IF;
2782 IF p_listheader_rec.RESULT_TEXT = FND_API.g_miss_char THEN
2783 x_complete_rec.RESULT_TEXT := l_listheader_rec.RESULT_TEXT;
2784 END IF;
2785 IF p_listheader_rec.KEYWORDS = FND_API.g_miss_char THEN
2786 x_complete_rec.KEYWORDS := l_listheader_rec.KEYWORDS;
2787 END IF;
2788 IF p_listheader_rec.DESCRIPTION = FND_API.g_miss_char THEN
2789 x_complete_rec.DESCRIPTION := l_listheader_rec.DESCRIPTION ;
2790 END IF;
2794 IF p_listheader_rec.ASSIGN_PERSON_ID = FND_API.g_miss_num THEN
2791 IF p_listheader_rec.LIST_PRIORITY = FND_API.g_miss_num THEN
2792 x_complete_rec.LIST_PRIORITY := l_listheader_rec.LIST_PRIORITY;
2793 END IF;
2795 x_complete_rec.ASSIGN_PERSON_ID := l_listheader_rec.ASSIGN_PERSON_ID;
2796 END IF;
2797 IF p_listheader_rec.LIST_SOURCE = FND_API.g_miss_char THEN
2798 x_complete_rec.LIST_SOURCE := l_listheader_rec.LIST_SOURCE ;
2799 END IF;
2800 IF p_listheader_rec.LIST_SOURCE_TYPE = FND_API.g_miss_char THEN
2801 x_complete_rec.LIST_SOURCE_TYPE := l_listheader_rec.LIST_SOURCE_TYPE;
2802 END IF;
2803 IF p_listheader_rec.LIST_ONLINE_FLAG = FND_API.g_miss_char THEN
2804 x_complete_rec.LIST_ONLINE_FLAG := l_listheader_rec.LIST_ONLINE_FLAG;
2805 END IF;
2806 IF p_listheader_rec.RANDOM_LIST_ID = FND_API.g_miss_num THEN
2807 x_complete_rec.RANDOM_LIST_ID := l_listheader_rec.RANDOM_LIST_ID ;
2808 END IF;
2809 IF p_listheader_rec.ENABLED_FLAG = FND_API.g_miss_char THEN
2810 x_complete_rec.ENABLED_FLAG := l_listheader_rec.ENABLED_FLAG;
2811 END IF;
2812 IF p_listheader_rec.ASSIGNED_TO = FND_API.g_miss_num THEN
2813 x_complete_rec.ASSIGNED_TO := l_listheader_rec.ASSIGNED_TO ;
2814 END IF;
2815 IF p_listheader_rec.QUERY_ID = FND_API.g_miss_num THEN
2816 x_complete_rec.QUERY_ID := l_listheader_rec.QUERY_ID ;
2817 END IF;
2818 IF p_listheader_rec.OWNER_PERSON_ID = FND_API.g_miss_num THEN
2819 x_complete_rec.OWNER_PERSON_ID := l_listheader_rec.OWNER_PERSON_ID;
2820 END IF;
2821 IF p_listheader_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char THEN
2822 x_complete_rec.ATTRIBUTE_CATEGORY := l_listheader_rec.ATTRIBUTE_CATEGORY;
2823 END IF;
2824 IF p_listheader_rec.ATTRIBUTE1 = FND_API.g_miss_char THEN
2825 x_complete_rec.ATTRIBUTE1 := l_listheader_rec.ATTRIBUTE1 ;
2826 END IF;
2827 IF p_listheader_rec.ATTRIBUTE2 = FND_API.g_miss_char THEN
2828 x_complete_rec.ATTRIBUTE2 := l_listheader_rec.ATTRIBUTE2 ;
2829 END IF;
2830 IF p_listheader_rec.ATTRIBUTE3 = FND_API.g_miss_char THEN
2831 x_complete_rec.ATTRIBUTE3 := l_listheader_rec.ATTRIBUTE3;
2832 END IF;
2833 IF p_listheader_rec.ATTRIBUTE4 = FND_API.g_miss_char THEN
2834 x_complete_rec.ATTRIBUTE4 := l_listheader_rec.ATTRIBUTE4;
2835 END IF;
2836 IF p_listheader_rec.ATTRIBUTE5 = FND_API.g_miss_char THEN
2837 x_complete_rec.ATTRIBUTE5 := l_listheader_rec.ATTRIBUTE5 ;
2838 END IF;
2839 IF p_listheader_rec.ATTRIBUTE6 = FND_API.g_miss_char THEN
2840 x_complete_rec.ATTRIBUTE6 := l_listheader_rec.ATTRIBUTE6;
2841 END IF;
2842 IF p_listheader_rec.ATTRIBUTE7 = FND_API.g_miss_char THEN
2843 x_complete_rec.ATTRIBUTE7 := l_listheader_rec.ATTRIBUTE7 ;
2844 END IF;
2845 IF p_listheader_rec.ATTRIBUTE8 = FND_API.g_miss_char THEN
2846 x_complete_rec.ATTRIBUTE8 := l_listheader_rec.ATTRIBUTE8;
2847 END IF;
2848 IF p_listheader_rec.ATTRIBUTE9 = FND_API.g_miss_char THEN
2849 x_complete_rec.ATTRIBUTE9 := l_listheader_rec.ATTRIBUTE9;
2850 END IF;
2851 IF p_listheader_rec.ATTRIBUTE10 = FND_API.g_miss_char THEN
2852 x_complete_rec.ATTRIBUTE10 := l_listheader_rec.ATTRIBUTE10;
2853 END IF;
2854 IF p_listheader_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
2855 x_complete_rec.ATTRIBUTE11 := l_listheader_rec.ATTRIBUTE11 ;
2856 END IF;
2857 IF p_listheader_rec.ATTRIBUTE12 = FND_API.g_miss_char THEN
2858 x_complete_rec.ATTRIBUTE12 := l_listheader_rec.ATTRIBUTE12;
2859 END IF;
2860 IF p_listheader_rec.ATTRIBUTE13 = FND_API.g_miss_char THEN
2861 x_complete_rec.ATTRIBUTE13 := l_listheader_rec.ATTRIBUTE13;
2862 END IF;
2863 IF p_listheader_rec.ATTRIBUTE14 = FND_API.g_miss_char THEN
2864 x_complete_rec.ATTRIBUTE14 := l_listheader_rec.ATTRIBUTE14;
2865 END IF;
2866 IF p_listheader_rec.ATTRIBUTE15 = FND_API.g_miss_char THEN
2867 x_complete_rec.ATTRIBUTE15 := l_listheader_rec.ATTRIBUTE15;
2868 END IF;
2869 IF p_listheader_rec.QUANTUM = FND_API.g_miss_num THEN
2870 x_complete_rec.QUANTUM := l_listheader_rec.QUANTUM ;
2871 END IF;
2872 IF p_listheader_rec.RELEASE_CONTROL_ALG_ID =FND_API.g_miss_num THEN
2873 x_complete_rec.RELEASE_CONTROL_ALG_ID
2874 := l_listheader_rec.RELEASE_CONTROL_ALG_ID ;
2875 END IF;
2876 IF p_listheader_rec.DIALING_METHOD = FND_API.g_miss_char THEN
2877 x_complete_rec.DIALING_METHOD := l_listheader_rec.DIALING_METHOD ;
2878 END IF;
2879 IF p_listheader_rec.RELEASE_CONTROL_ALG_ID =FND_API.g_miss_num THEN
2880 x_complete_rec.RELEASE_CONTROL_ALG_ID := l_listheader_rec.RELEASE_CONTROL_ALG_ID ;
2881 END IF;
2882 IF p_listheader_rec.CALLING_CALENDAR_ID =FND_API.g_miss_num THEN
2883 x_complete_rec.CALLING_CALENDAR_ID := l_listheader_rec.CALLING_CALENDAR_ID ;
2884 END IF;
2885 IF p_listheader_rec.release_strategy = FND_API.g_miss_char THEN
2886 x_complete_rec.release_strategy := l_listheader_rec.release_strategy ;
2887 END IF;
2891 IF p_listheader_rec.country =FND_API.g_miss_num THEN
2888 IF p_listheader_rec.custom_setup_id =FND_API.g_miss_num THEN
2889 x_complete_rec.custom_setup_id := l_listheader_rec.custom_setup_id ;
2890 END IF;
2892 x_complete_rec.country := l_listheader_rec.country ;
2893 END IF;
2894 IF p_listheader_rec.PURGE_FLAG =FND_API.g_miss_char THEN
2895 x_complete_rec.purge_flag := l_listheader_rec.purge_flag ;
2896 END IF;
2897 IF p_listheader_rec.PUBLIC_FLAG =FND_API.g_miss_char THEN
2898 x_complete_rec.PUBLIC_FLAG := l_listheader_rec.PUBLIC_FLAG ;
2899 END IF;
2900 IF p_listheader_rec.LIST_CATEGORY =FND_API.g_miss_char THEN
2901 x_complete_rec.LIST_CATEGORY := l_listheader_rec.LIST_CATEGORY ;
2902 END IF;
2903 IF p_listheader_rec.quota =FND_API.g_miss_num THEN
2904 x_complete_rec.quota := l_listheader_rec.quota ;
2905 END IF;
2906 IF p_listheader_rec.quota_reset =FND_API.g_miss_num THEN
2907 x_complete_rec.quota_reset := l_listheader_rec.quota_reset ;
2908 END IF;
2909 IF p_listheader_rec.recycling_alg_id =FND_API.g_miss_num THEN
2910 x_complete_rec.recycling_alg_id := l_listheader_rec.recycling_alg_id ;
2911 END IF;
2912 IF p_listheader_rec.CALLBACK_PRIORITY_FLAG = FND_API.g_miss_char THEN
2913 x_complete_rec.CALLBACK_PRIORITY_FLAG := l_listheader_rec.CALLBACK_PRIORITY_FLAG ;
2914 END IF;
2915 IF p_listheader_rec.CALL_CENTER_READY_FLAG = FND_API.g_miss_char THEN
2916 x_complete_rec.CALL_CENTER_READY_FLAG := l_listheader_rec.CALL_CENTER_READY_FLAG ;
2917 END IF;
2918 IF p_listheader_rec.USER_STATUS_ID = FND_API.g_miss_num THEN
2919 x_complete_rec.USER_STATUS_ID := l_listheader_rec.USER_STATUS_ID ;
2920 END IF;
2921
2922 IF p_listheader_rec.NO_OF_ROWS_prev_contacted = FND_API.g_miss_num THEN
2923 x_complete_rec.NO_OF_ROWS_prev_contacted := l_listheader_rec.NO_OF_ROWS_prev_contacted;
2924 END IF;
2925
2926 IF p_listheader_rec.APPLY_TRAFFIC_COP = FND_API.g_miss_char THEN
2927 x_complete_rec.APPLY_TRAFFIC_COP := l_listheader_rec.APPLY_TRAFFIC_COP ;
2928 END IF;
2929
2930 x_complete_rec.status_code := AMS_Utility_PVT.get_system_status_code(
2931 x_complete_rec.user_status_id );
2932
2933
2934 -- ckapoor R12 copy target group changes
2935
2936 IF p_listheader_rec.CTRL_CONF_LEVEL =FND_API.g_miss_num THEN
2937 x_complete_rec.CTRL_CONF_LEVEL := l_listheader_rec.CTRL_CONF_LEVEL ;
2938 END IF;
2939 IF p_listheader_rec.CTRL_REQ_RESP_RATE =FND_API.g_miss_num THEN
2940 x_complete_rec.CTRL_REQ_RESP_RATE := l_listheader_rec.CTRL_REQ_RESP_RATE ;
2941 END IF;
2942 IF p_listheader_rec.CTRL_LIMIT_OF_ERROR =FND_API.g_miss_num THEN
2943 x_complete_rec.CTRL_LIMIT_OF_ERROR := l_listheader_rec.CTRL_LIMIT_OF_ERROR ;
2944 END IF;
2945 IF p_listheader_rec.STATUS_CODE_OLD = FND_API.g_miss_char THEN
2946 x_complete_rec.STATUS_CODE_OLD := l_listheader_rec.STATUS_CODE_OLD ;
2947 END IF;
2948 IF p_listheader_rec.CTRL_CONC_JOB_ID = FND_API.g_miss_num THEN
2949 x_complete_rec.CTRL_CONC_JOB_ID := l_listheader_rec.CTRL_CONC_JOB_ID ;
2950 END IF;
2951 IF p_listheader_rec.CTRL_STATUS_CODE = FND_API.g_miss_char THEN
2952 x_complete_rec.CTRL_STATUS_CODE := l_listheader_rec.CTRL_STATUS_CODE ;
2953 END IF;
2954
2955 IF p_listheader_rec.CTRL_GEN_MODE = FND_API.g_miss_char THEN
2956 x_complete_rec.CTRL_GEN_MODE := l_listheader_rec.CTRL_GEN_MODE;
2957 END IF;
2958
2959 IF p_listheader_rec.APPLY_SUPPRESSION_FLAG = FND_API.g_miss_char THEN
2960 x_complete_rec.APPLY_SUPPRESSION_FLAG := l_listheader_rec.APPLY_SUPPRESSION_FLAG ;
2961 END IF;
2962
2963
2964 -- end ckapoor R12 copy target group changes
2965
2966
2967 -- dbms_output.put_line('end ofcomplete list header rec');
2968 END Complete_ListHeader_rec;
2969
2970 PROCEDURE Update_list_header_count(
2971 p_list_header_id IN number,
2972 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2973 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2974 x_return_status OUT NOCOPY VARCHAR2,
2975 x_msg_count OUT NOCOPY NUMBER,
2976 x_msg_data OUT NOCOPY VARCHAR2
2977 )
2978 IS
2979 cursor c_count_list_entries(cur_p_list_header_id number) is
2980 select sum(decode(enabled_flag,'N',0,1)),
2981 sum(decode(enabled_flag,'Y',0,1)),
2982 sum(1),
2983 sum(decode(part_of_control_group_flag,'Y',1,0)),
2984 sum(decode(marked_as_random_flag,'Y',1,0)),
2985 sum(decode(marked_as_duplicate_flag,'Y',1,0)),
2986 sum(decode(manually_entered_flag,
2987 'Y',decode(enabled_flag,'Y','1',0),
2988 0))
2989 from ams_list_entries
2990 where list_header_id = cur_p_list_header_id ;
2991
2992 l_no_of_rows_duplicates number;
2993 l_no_of_rows_in_list number;
2994 l_no_of_rows_active number;
2995 l_no_of_rows_inactive number;
2996 l_no_of_rows_manually_entered number;
2997 l_no_of_rows_in_ctrl_group number;
2998 l_no_of_rows_random number;
2999 l_min_rows number;
3000 l_new_status varchar2(30);
3001 l_new_status_id number;
3002 --l_no_of_rows_prev_contacted number;
3003 BEGIN
3004 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3005 FND_MSG_PUB.initialize;
3006 END IF;
3010 into l_no_of_rows_active ,
3007
3008 open c_count_list_entries(p_list_header_id);
3009 fetch c_count_list_entries
3011 l_no_of_rows_inactive ,
3012 l_no_of_rows_in_list ,
3013 l_no_of_rows_in_ctrl_group ,
3014 l_no_of_rows_random ,
3015 l_no_of_rows_duplicates ,
3016 l_no_of_rows_manually_entered ;
3017 close c_count_list_entries;
3018
3019
3020 SELECT nvl(no_of_rows_min_requested,0)
3021 INTO l_min_rows
3022 FROM ams_list_headers_all
3023 WHERE list_header_id = p_list_header_id;
3024
3025 if l_min_rows > l_no_of_rows_active then
3026 l_new_status := 'DRAFT';
3027 l_new_status_id := 300;
3028 else
3029 l_new_status := 'AVAILABLE';
3030 l_new_status_id := 303;
3031 end if;
3032 update ams_list_headers_all
3033 set no_of_rows_in_list = l_no_of_rows_in_list,
3034 no_of_rows_active = l_no_of_rows_active,
3035 no_of_rows_inactive = l_no_of_rows_inactive,
3036 no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
3037 no_of_rows_random = l_no_of_rows_random,
3038 no_of_rows_duplicates = l_no_of_rows_duplicates,
3039 no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
3040 last_generation_success_flag = decode(l_new_status_id,303,'Y','N'),
3041 status_code = l_new_status,
3042 user_status_id = l_new_status_id,
3043 status_date = sysdate,
3044 last_update_date = sysdate
3045 WHERE list_header_id = p_list_header_id;
3046
3047 IF FND_API.To_Boolean ( p_commit ) THEN
3048 COMMIT WORK;
3049 END IF;
3050
3051
3052 IF (AMS_DEBUG_HIGH_ON) THEN
3053 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3054 FND_MESSAGE.Set_Token('ROW', 'AMS_LIST_HEADER_UPDATE: END');
3055 FND_MSG_PUB.Add;
3056 END IF;
3057
3058
3059 IF (AMS_DEBUG_HIGH_ON) THEN
3060 AMS_Utility_PVT.debug_message('AMS_LIST_HEADER_UPDATE: END');
3061 END IF;
3062
3063
3064 FND_MSG_PUB.count_and_get(
3065 p_encoded => FND_API.g_false,
3066 p_count => x_msg_count,
3067 p_data => x_msg_data
3068 );
3069
3070 EXCEPTION
3071 WHEN FND_API.G_EXC_ERROR THEN
3072 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3073 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3074 FND_MSG_PUB.Add;
3075 -- Check if reset of the status is required
3076 x_return_status := FND_API.G_RET_STS_ERROR ;
3077 FND_MSG_PUB.count_and_get(
3078 p_encoded => FND_API.g_false,
3079 p_count => x_msg_count,
3080 p_data => x_msg_data);
3081
3082 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3083 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3084 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3085 FND_MSG_PUB.Add;
3086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3087 FND_MSG_PUB.count_and_get(
3088 p_encoded => FND_API.g_false,
3089 p_count => x_msg_count,
3090 p_data => x_msg_data);
3091
3092 WHEN OTHERS THEN
3093 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3094 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3095 FND_MSG_PUB.Add;
3096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3097 FND_MSG_PUB.count_and_get(
3098 p_encoded => FND_API.g_false,
3099 p_count => x_msg_count,
3100 p_data => x_msg_data);
3101
3102
3103 END;
3104
3105
3106
3107
3108 ---------------------------------------------------------------------
3109 -- PROCEDURE
3110 -- Update_Prev_contacted_count
3111 --
3112 -- PURPOSE
3113 -- aDDED TO SUPPORT TRIGGER BACKPORT FUNCTIONALITY
3114 -- Gived a schedule id / one off event id or event header id and the last contacted date
3115 -- update all list entries of the related target group or invite list ,
3116 -- who were not contacted earlier, with the last contacted date
3117 -- also populate list header with total number of records with last contacted date populated
3118 -- PARAMETERS
3119 -- p_used_by: eg CSCH/EVEO/EVEH
3120 -- p_used_by : Schedule Id/ or One off Event Id or Event Header Id
3121 -- p_last_contacted_date :last contacted date to be populated in to the list entries table
3122 ---------------------------------------------------------------------
3123
3124
3125 PROCEDURE Update_Prev_contacted_count(
3126 p_used_by_id IN number,
3127 p_used_by IN VARCHAR2,
3128 p_last_contacted_date IN DATE,
3129 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3130 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3131 x_return_status OUT NOCOPY VARCHAR2,
3132 x_msg_count OUT NOCOPY NUMBER,
3133 x_msg_data OUT NOCOPY VARCHAR2
3134 )
3135
3136 IS
3137
3138 cursor c_get_list_header_id(cur_p_used_by varchar2,cur_p_used_by_id number) is
3139 select list_header_id
3140 from ams_act_lists
3141 where LIST_USED_BY_ID = cur_p_used_by_id
3142 and LIST_USED_BY = cur_p_used_by
3143 and LIST_ACT_TYPE ='TARGET';
3147 cursor c_count_list_entries(cur_p_list_header_id number) is
3144
3145
3146
3148 select count(LAST_CONTACTED_DATE)
3149 from ams_list_entries
3150 where list_header_id = cur_p_list_header_id
3151 and enabled_flag = 'Y'
3152 and LAST_CONTACTED_DATE is not null;
3153
3154 l_no_of_rows_prev_contacted number;
3155 l_list_header_id number;
3156
3157 BEGIN
3158 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3159 FND_MSG_PUB.initialize;
3160 END IF;
3161
3162
3163 open c_get_list_header_id(p_used_by,p_used_by_id);
3164 fetch c_get_list_header_id
3165 into l_list_header_id ;
3166 close c_get_list_header_id;
3167
3168 --only update entries which have not been updated already
3169 update ams_list_entries
3170 set LAST_CONTACTED_DATE = p_last_contacted_date,
3171 last_update_date = sysdate
3172 WHERE list_header_id = l_list_header_id
3173 and enabled_flag = 'Y'
3174 and LAST_CONTACTED_DATE is null;
3175
3176
3177 open c_count_list_entries(l_list_header_id);
3178 fetch c_count_list_entries
3179 into l_no_of_rows_prev_contacted ;
3180 close c_count_list_entries;
3181
3182
3183 update ams_list_headers_all
3184 set no_of_rows_prev_contacted = l_no_of_rows_prev_contacted,
3185 last_update_date = sysdate
3186 WHERE list_header_id = l_list_header_id;
3187
3188
3189
3190
3191 IF FND_API.To_Boolean ( p_commit ) THEN
3192 COMMIT WORK;
3193 END IF;
3194
3195
3196 IF (AMS_DEBUG_HIGH_ON) THEN
3197 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3198 FND_MESSAGE.Set_Token('ROW', 'AMS_LIST_HEADER_UPDATE: END');
3199 FND_MSG_PUB.Add;
3200 END IF;
3201
3202
3203
3204 FND_MSG_PUB.count_and_get(
3205 p_encoded => FND_API.g_false,
3206 p_count => x_msg_count,
3207 p_data => x_msg_data
3208 );
3209
3210 EXCEPTION
3211 WHEN FND_API.G_EXC_ERROR THEN
3212 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3213 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3214 FND_MSG_PUB.Add;
3215 -- Check if reset of the status is required
3216 x_return_status := FND_API.G_RET_STS_ERROR ;
3217 FND_MSG_PUB.count_and_get(
3218 p_encoded => FND_API.g_false,
3219 p_count => x_msg_count,
3220 p_data => x_msg_data);
3221
3222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3223 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3224 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3225 FND_MSG_PUB.Add;
3226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3227 FND_MSG_PUB.count_and_get(
3228 p_encoded => FND_API.g_false,
3229 p_count => x_msg_count,
3230 p_data => x_msg_data);
3231
3232 WHEN OTHERS THEN
3233 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3234 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3235 FND_MSG_PUB.Add;
3236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3237 FND_MSG_PUB.count_and_get(
3238 p_encoded => FND_API.g_false,
3239 p_count => x_msg_count,
3240 p_data => x_msg_data);
3241
3242
3243 END;
3244
3245
3246
3247
3248
3249
3250
3251
3252 -- Start of Comments
3253 --
3254 -- NAME
3255 -- Copy_List
3256 --
3257 -- PURPOSE
3258 -- This procedure creates a list header record that satisfy caller needs
3259 --
3260 -- NOTES
3261 --
3262 --
3263 -- HISTORY
3264 -- 05/12/1999 tdonohoe created
3265 -- End of Comments
3266 PROCEDURE Copy_List
3267 ( p_api_version IN NUMBER,
3268 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3269 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3270 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3271 x_return_status OUT NOCOPY VARCHAR2,
3272 x_msg_count OUT NOCOPY NUMBER,
3273 x_msg_data OUT NOCOPY VARCHAR2,
3274 p_source_listheader_id IN NUMBER,
3275 p_listheader_rec IN list_header_rec_type,
3276 p_copy_select_actions IN VARCHAR2 := 'Y',
3277 p_copy_list_queries IN VARCHAR2 := 'Y',
3278 p_copy_list_entries IN VARCHAR2 := 'Y',
3279
3280 x_listheader_id OUT NOCOPY NUMBER
3281 ) IS
3282
3283 l_api_name CONSTANT VARCHAR2(30) := 'Copy_List';
3284 l_api_version CONSTANT NUMBER := 1.0;
3285 -- Status Local Variables
3286 l_return_status VARCHAR2(1); -- Return value from procedures
3287
3288 l_listheader_id number;
3289
3290 x_rowid VARCHAR2(30);
3291
3292 l_sqlerrm varchar2(600);
3293 l_sqlcode varchar2(100);
3294
3295 /*
3296 CURSOR fetch_list_details (list_id NUMBER) IS
3297 SELECT * FROM ams_list_headers_vl
3298 WHERE list_header_id = list_id ;
3299 l_reference_rec fetch_list_details%ROWTYPE;
3300 */
3301
3302 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
3303
3307 BEGIN
3304 l_new_listheader_rec list_header_rec_type := p_listheader_rec;
3305 l_trg_listheader_rec list_header_rec_type;
3306
3308
3309 -- Standard Start of API savepoint
3310 SAVEPOINT Copy_Lists_PVT;
3311
3312 x_return_status := FND_API.G_RET_STS_SUCCESS;
3313 -- Standard call to check for call compatibility.
3314 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3315 p_api_version,
3316 l_api_name,
3317 G_PKG_NAME) THEN
3318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3319 END IF;
3320
3321
3322 -- Initialize message list IF p_init_msg_list is set to TRUE.
3323 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3324 FND_MSG_PUB.initialize;
3325 END IF;
3326
3327 -- Debug Message
3328 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
3329 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3330 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeaders_PVT.Copy_Lists: Start', TRUE);
3331 FND_MSG_PUB.Add;
3332 END IF; */
3333
3334 IF (AMS_DEBUG_HIGH_ON) THEN
3335 AMS_Utility_PVT.debug_message('AMS_ListHeader_PVT.Copy_List: Start');
3336 END IF;
3337
3338 -- Initialize API return status to success
3339 x_return_status := FND_API.G_RET_STS_SUCCESS;
3340
3341
3342 l_new_listheader_rec.list_header_id:= p_source_listheader_id;
3343 ----------------------------------------------------------
3344 -- replace g_miss_char/num/date with current column values
3345 ----------------------------------------------------------
3346
3347 complete_listheader_rec(l_new_listheader_rec, l_trg_listheader_rec);
3348
3349
3350 -- Null fields
3351 l_trg_listheader_rec.list_header_id := FND_API.g_miss_num;
3352
3353 l_trg_listheader_rec.request_id := NULL;
3354 l_trg_listheader_rec.program_id := NULL;
3355 l_trg_listheader_rec.program_application_id := NULL;
3356 l_trg_listheader_rec.program_update_date := NULL;
3357
3358
3359 l_trg_listheader_rec.user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_LIST_STATUS','AVAILABLE');
3360 l_trg_listheader_rec.status_code := 'AVAILABLE';
3361 l_trg_listheader_rec.status_date := SYSDATE;
3362
3363 -- ----------------------------
3364 -- call create api
3365 -- ----------------------------
3366 Create_ListHeader( p_api_version=>l_api_version,
3367 p_init_msg_list=>l_init_msg_list,
3368 p_commit=>p_commit,
3369 p_validation_level=>p_validation_level,
3370 x_return_status=> x_return_status,
3371 x_msg_count=>x_msg_count,
3372 x_msg_data=> x_msg_data,
3373 p_listheader_rec => l_trg_listheader_rec,
3374 x_listheader_id => l_listheader_id );
3375
3376
3377 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3378 RAISE FND_API.G_EXC_ERROR;
3379 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381 END IF;
3382
3383 -- set OUT value
3384 x_listheader_id := l_listheader_id;
3385
3386 /* IF p_copy_select_actions = 'Y' THEN
3387 -- call api to copy select actions
3388 END IF;
3389 */
3390 IF p_copy_list_queries = 'Y' THEN
3391 -- call api to copy list queries
3395 p_commit,
3392 AMS_List_Query_PVT.Copy_List_Queries
3393 ( l_api_version,
3394 p_init_msg_list,
3396 p_validation_level,
3397 p_source_listheader_id,
3398 l_listheader_id,
3399 l_trg_listheader_rec.list_name,
3400 x_return_status,
3401 x_msg_count,
3402 x_msg_data
3403 );
3404
3405 END IF;
3406
3407 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3408 RAISE FND_API.G_EXC_ERROR;
3409 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3411 END IF;
3412
3413
3414 IF p_copy_list_entries = 'Y' THEN
3415 -- call api to copy list entries
3416 AMS_List_Entries_PVT.Copy_List_Entries
3417 (
3418 l_api_version,
3419 p_init_msg_list,
3420 p_commit,
3421 p_validation_level,
3422 x_return_status,
3423 x_msg_count,
3424 x_msg_data,
3425 p_source_listheader_id,
3426 l_listheader_id
3427 );
3428
3429 END IF;
3430
3431 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3432 RAISE FND_API.G_EXC_ERROR;
3433 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3435 END IF;
3436
3437
3438 -- Standard check of p_commit.
3439 IF FND_API.To_Boolean ( p_commit ) THEN
3440 COMMIT WORK;
3441 END IF;
3442
3443 -- Success Message
3444 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3445 THEN
3446 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
3447 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Copy_Lists', TRUE);
3448 FND_MSG_PUB.Add;
3449 END IF;
3450
3451 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
3452 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3453 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Copy_Lists: END', TRUE);
3454 FND_MSG_PUB.Add;
3455 END IF; */
3456
3457
3458 -- Standard call to get message count AND IF count is 1, get message info.
3459 FND_MSG_PUB.Count_AND_Get
3460 ( p_count => x_msg_count,
3461 p_data => x_msg_data,
3462 p_encoded => FND_API.G_FALSE
3463 );
3464
3465 EXCEPTION
3466 WHEN FND_API.G_EXC_ERROR THEN
3467 ROLLBACK TO Copy_Lists_PVT;
3468 x_return_status := FND_API.G_RET_STS_ERROR ;
3469
3470 FND_MSG_PUB.Count_AND_Get
3471 ( p_count => x_msg_count,
3472 p_data => x_msg_data,
3473 p_encoded => FND_API.G_FALSE
3474 );
3475
3476
3477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3478 ROLLBACK TO Copy_Lists_PVT;
3479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3480 FND_MSG_PUB.Count_AND_Get
3481 ( p_count => x_msg_count,
3482 p_data => x_msg_data,
3483 p_encoded => FND_API.G_FALSE
3484 );
3485
3486 WHEN OTHERS THEN
3487 ROLLBACK TO Copy_Lists_PVT;
3488 FND_MESSAGE.set_name('AMS','SQL ERROR ->' || sqlerrm );
3489 FND_MSG_PUB.Add;
3490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3491 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3492 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3493 END IF;
3494
3495 FND_MSG_PUB.Count_AND_Get
3496 ( p_count => x_msg_count,
3497 p_data => x_msg_data,
3498 p_encoded => FND_API.G_FALSE
3499 );
3500
3501 END Copy_List;
3502
3503 END AMS_listheader_PVT;