[Home] [Help]
PACKAGE BODY: APPS.AMS_LISTHEADER_PVT
Source
1 PACKAGE BODY AMS_ListHeader_PVT as
2 /* $Header: amsvlshb.pls 120.4.12020000.2 2012/12/11 08:25:38 sagpraka 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
200 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)
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
402 AND p_listheader_rec.timezone_id IS NOT NULL THEN
403 IF AMS_Utility_PVT.check_fk_exists(
404 'fnd_timezones_b',
405 'upgrade_tz_id',
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
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 ;
540 close c_check_create_mode;
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;
569
566 END IF;
567
568 END check_list_uk_items;
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 -----------------------------------------------------------------------
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
666 IF ((p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION < 0) OR
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 --
701 -- p_complete_rec: the complete record after all "g_miss" items
698 -- PARAMETERS
699 -- p_listheader_rec: the record to be validated; may contain attributes
700 -- as FND_API.g_miss_char/num/date
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
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');
792 FND_MSG_PUB.Add;
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;
821 ELSE
818 -- If any errors happen abort API/Procedure.
819 RAISE FND_API.G_EXC_ERROR;
820 END IF;
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;
919
920 -- exit when unique key found
921 EXIT WHEN l_dummy = 0;
922
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
982
979 check_list_lookup_items(
980 p_listheader_rec => p_listheader_rec,
981 x_return_status => x_return_status);
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.
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
1078 RAISE FND_API.G_EXC_ERROR;
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,
1128
1125 p_data => x_msg_data,
1126 p_encoded => FND_API.G_FALSE
1127 );
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 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1201
1202
1203 x_rowid VARCHAR2(30);
1204
1205 l_sqlerrm varchar2(600);
1206 l_sqlcode varchar2(100);
1207
1208 l_list_count NUMBER;
1209 l_main_gen_time DATE := null;
1210
1211 CURSOR c_list_seq IS
1212 SELECT ams_list_headers_all_s.NEXTVAL
1213 FROM DUAL;
1214
1215 CURSOR c_list_count(p_list_header_id IN NUMBER) IS
1216 SELECT COUNT(*)
1217 FROM ams_list_headers_all
1218 WHERE list_header_id = p_list_header_id;
1219 L_OBJECT_TYPE VARCHAR2(10) := 'LIST';
1220
1221 CURSOR c_custom_setup_id(c_list_type in varchar2) IS
1222 SELECT custom_setup_id
1223 FROM ams_custom_setups_b
1224 WHERE object_type = L_OBJECT_TYPE
1225 and activity_type_code = c_list_type
1226 --AND enabled_flag = 'Y'
1227 ;
1228
1229 CURSOR c_default_list_user_status_id IS
1230 SELECT user_status_id
1231 FROM ams_user_statuses_vl
1232 WHERE system_status_type = 'AMS_LIST_STATUS'
1233 AND system_status_code = 'DRAFT'
1234 AND enabled_flag = 'Y'
1235 AND default_flag = 'Y';
1236
1237
1238 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
1239 l_org_id NUMBER;
1240
1241 BEGIN
1242
1243 -- Standard Start of API savepoint
1244 SAVEPOINT Create_listheaders_PVT;
1245 l_org_id := fnd_profile.value('DEFAULT_ORG_ID');
1246
1247 x_return_status := FND_API.G_RET_STS_SUCCESS;
1248 -- Standard call to check for call compatibility.
1249 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1250 p_api_version,
1251 l_api_name,
1252 G_PKG_NAME) THEN
1253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1254 END IF;
1255
1256
1257 -- Initialize message list IF p_init_msg_list is set to TRUE.
1258 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1259 FND_MSG_PUB.initialize;
1260 END IF;
1261
1262 -- Debug Message
1263 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1264 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1265 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeaders_PVT.Create_listheaders: Start', TRUE);
1266 FND_MSG_PUB.Add;
1267 END IF; */
1268
1269 IF (AMS_DEBUG_HIGH_ON) THEN
1270 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Create_listheaders: Start');
1271 END IF;
1272
1273
1274 -- Initialize API return status to success
1275 x_return_status := FND_API.G_RET_STS_SUCCESS;
1276 -- Perform the database operation
1277
1278 IF (l_listheader_rec.list_header_id IS NULL OR
1279 l_listheader_rec.list_header_id = FND_API.g_miss_num) THEN LOOP
1280 OPEN c_list_seq;
1281 FETCH c_list_seq INTO l_listheader_rec.list_header_id;
1282 CLOSE c_list_seq;
1283
1284 OPEN c_list_count(l_listheader_rec.list_header_id);
1285 FETCH c_list_count INTO l_list_count;
1286 CLOSE c_list_count;
1287
1288 EXIT WHEN l_list_count = 0;
1289 END LOOP;
1290 END IF;
1291
1292 IF (l_listheader_rec.USER_ENTERED_START_TIME IS NOT NULL
1293 AND l_listheader_rec.TIMEZONE_ID IS NOT NULL
1294 AND l_listheader_rec.TIMEZONE_ID <> FND_API.g_miss_num) THEN
1295
1296 AMS_UTILITY_PVT.Convert_Timezone(
1297 p_init_msg_list => l_init_msg_list,
1298 x_return_status => x_return_status,
1299 x_msg_count => x_msg_count,
1300 x_msg_data => x_msg_data,
1301 p_user_tz_id => l_listheader_rec.TIMEZONE_ID,
1302 p_in_time => l_listheader_rec.USER_ENTERED_START_TIME,
1303 p_convert_type => 'SYS',
1304 x_out_time => l_main_gen_time
1305 );
1306
1307 -- If any errors happen abort API.
1308 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1309 RAISE FND_API.G_EXC_ERROR;
1310 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1312 END IF;
1313 END IF;
1314
1315
1316 -- initialize any default values
1317 OPEN c_custom_setup_id(l_listheader_rec.list_type);
1318 FETCH c_custom_setup_id INTO l_listheader_rec.custom_setup_id;
1319 CLOSE c_custom_setup_id;
1320
1321 OPEN c_default_list_user_status_id;
1322 FETCH c_default_list_user_status_id INTO l_listheader_rec.user_status_id;
1323 CLOSE c_default_list_user_status_id;
1324
1325 IF l_listheader_rec.country IS NULL OR l_listheader_rec.country = FND_API.g_miss_num THEN
1326 l_listheader_rec.country := FND_PROFILE.value ('AMS_SRCGEN_USER_CITY');
1327 END IF;
1328
1329 Validate_ListHeader
1330 ( p_api_version => 1.0
1331 ,p_init_msg_list => l_init_msg_list
1332 ,p_validation_level => p_validation_level
1333 ,x_return_status => x_return_status
1334 ,x_msg_count => x_msg_count
1335 ,x_msg_data => x_msg_data
1336 ,p_listheader_rec => l_listheader_rec
1337 );
1338
1339
1340 -- If any errors happen abort API.
1341 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1342 RAISE FND_API.G_EXC_ERROR;
1343 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346
1347 IF (AMS_DEBUG_HIGH_ON) THEN
1348 Ams_Utility_Pvt.Debug_Message(L_FULL_NAME||': The org id is ' || l_org_id);
1349 END IF;
1350
1351 insert into AMS_LIST_HEADERS_ALL (
1352 LIST_HEADER_ID ,
1353 LAST_UPDATE_DATE ,
1354 LAST_UPDATED_BY ,
1355 CREATION_DATE ,
1356 CREATED_BY ,
1357 LAST_UPDATE_LOGIN ,
1358 OBJECT_VERSION_NUMBER ,
1359 REQUEST_ID ,
1360 PROGRAM_ID ,
1361 PROGRAM_APPLICATION_ID ,
1362 PROGRAM_UPDATE_DATE ,
1363 VIEW_APPLICATION_ID ,
1364 LIST_USED_BY_ID ,
1365 ARC_LIST_USED_BY ,
1366 LIST_TYPE ,
1367 STATUS_CODE ,
1368 STATUS_DATE ,
1369 GENERATION_TYPE ,
1370 REPEAT_EXCLUDE_TYPE ,
1371 ROW_SELECTION_TYPE ,
1372 OWNER_USER_ID ,
1373 ACCESS_LEVEL ,
1374 ENABLE_LOG_FLAG ,
1375 ENABLE_WORD_REPLACEMENT_FLAG ,
1376 ENABLE_PARALLEL_DML_FLAG ,
1377 DEDUPE_DURING_GENERATION_FLAG ,
1378 GENERATE_CONTROL_GROUP_FLAG ,
1379 LAST_GENERATION_SUCCESS_FLAG ,
1380 FORECASTED_START_DATE ,
1381 FORECASTED_END_DATE ,
1382 ACTUAL_END_DATE ,
1383 SENT_OUT_DATE ,
1384 DEDUPE_START_DATE ,
1385 LAST_DEDUPE_DATE ,
1386 LAST_DEDUPED_BY_USER_ID ,
1387 WORKFLOW_ITEM_KEY ,
1388 NO_OF_ROWS_DUPLICATES ,
1389 NO_OF_ROWS_MIN_REQUESTED ,
1390 NO_OF_ROWS_MAX_REQUESTED ,
1391 NO_OF_ROWS_IN_LIST ,
1392 NO_OF_ROWS_IN_CTRL_GROUP ,
1393 NO_OF_ROWS_ACTIVE ,
1394 NO_OF_ROWS_INACTIVE ,
1395 NO_OF_ROWS_MANUALLY_ENTERED ,
1396 NO_OF_ROWS_DO_NOT_CALL ,
1397 NO_OF_ROWS_DO_NOT_MAIL ,
1398 NO_OF_ROWS_RANDOM ,
1399 ORG_ID ,
1400 MAIN_GEN_START_TIME ,
1401 MAIN_GEN_END_TIME ,
1402 MAIN_RANDOM_NTH_ROW_SELECTION ,
1403 MAIN_RANDOM_PCT_ROW_SELECTION ,
1404 CTRL_RANDOM_NTH_ROW_SELECTION ,
1405 CTRL_RANDOM_PCT_ROW_SELECTION ,
1406 REPEAT_SOURCE_LIST_HEADER_ID ,
1407 RESULT_TEXT ,
1408 KEYWORDS ,
1409 DESCRIPTION ,
1410 LIST_PRIORITY ,
1411 ASSIGN_PERSON_ID ,
1412 LIST_SOURCE ,
1413 LIST_SOURCE_TYPE ,
1414 LIST_ONLINE_FLAG ,
1415 RANDOM_LIST_ID ,
1416 ENABLED_FLAG ,
1417 ASSIGNED_TO ,
1418 QUERY_ID ,
1419 OWNER_PERSON_ID ,
1420 ARCHIVED_BY ,
1421 ARCHIVED_DATE ,
1422 ATTRIBUTE_CATEGORY ,
1423 ATTRIBUTE1 ,
1424 ATTRIBUTE2 ,
1425 ATTRIBUTE3 ,
1426 ATTRIBUTE4 ,
1427 ATTRIBUTE5 ,
1428 ATTRIBUTE6 ,
1429 ATTRIBUTE7 ,
1430 ATTRIBUTE8 ,
1431 ATTRIBUTE9 ,
1432 ATTRIBUTE10 ,
1433 ATTRIBUTE11 ,
1434 ATTRIBUTE12 ,
1435 ATTRIBUTE13 ,
1436 ATTRIBUTE14 ,
1437 ATTRIBUTE15 ,
1438 TIMEZONE_ID ,
1439 USER_ENTERED_START_TIME ,
1440 USER_STATUS_ID ,
1441 QUANTUM ,
1442 RELEASE_CONTROL_ALG_ID ,
1443 DIALING_METHOD ,
1444 CALLING_CALENDAR_ID ,
1445 RELEASE_STRATEGY ,
1446 CUSTOM_SETUP_ID ,
1447 COUNTRY ,
1448 CALLBACK_PRIORITY_FLAG ,
1449 CALL_CENTER_READY_FLAG ,
1450 PURGE_FLAG ,
1451 QUOTA ,
1452 QUOTA_RESET ,
1453 RECYCLING_ALG_ID ,
1454 PUBLIC_FLAG ,
1455 LIST_CATEGORY ,
1456 no_of_rows_prev_contacted,
1457 APPLY_TRAFFIC_COP,
1458 purpose_code,
1459 CTRL_CONF_LEVEL,
1460 CTRL_REQ_RESP_RATE ,
1461 CTRL_LIMIT_OF_ERROR ,
1462 STATUS_CODE_OLD,
1463 CTRL_CONC_JOB_ID ,
1464 CTRL_STATUS_CODE ,
1465 CTRL_GEN_MODE ,
1466 APPLY_SUPPRESSION_FLAG
1467 ) values (
1468 decode(l_listheader_rec.LIST_HEADER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LIST_HEADER_ID) ,
1469 sysdate,
1470 FND_GLOBAL.user_id,
1471 sysdate,
1472 FND_GLOBAL.user_id,
1473 FND_GLOBAL.conc_login_id,
1474 1,
1475 decode(l_listheader_rec.REQUEST_ID ,FND_API.g_miss_num,null,l_listheader_rec.REQUEST_ID) ,
1476 decode(l_listheader_rec.PROGRAM_ID ,FND_API.g_miss_num,null,l_listheader_rec.PROGRAM_ID) ,
1477 decode(l_listheader_rec.PROGRAM_APPLICATION_ID ,FND_API.g_miss_num,null,l_listheader_rec.PROGRAM_APPLICATION_ID) ,
1478 decode(l_listheader_rec.PROGRAM_UPDATE_DATE ,FND_API.g_miss_date,null,l_listheader_rec.PROGRAM_UPDATE_DATE) ,
1479 decode(l_listheader_rec.VIEW_APPLICATION_ID ,FND_API.g_miss_num,530,nvl(l_listheader_rec.VIEW_APPLICATION_ID,530)) ,
1480 decode(l_listheader_rec.LIST_USED_BY_ID ,FND_API.g_miss_num,0,nvl(l_listheader_rec.LIST_USED_BY_ID,0)) ,
1481 decode(l_listheader_rec.ARC_LIST_USED_BY ,FND_API.g_miss_char,'NONE',nvl(l_listheader_rec.ARC_LIST_USED_BY,'NONE')) ,
1482 decode(l_listheader_rec.LIST_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_TYPE) ,
1483 decode(l_listheader_rec.STATUS_CODE ,FND_API.g_miss_char,'DRAFT',nvl(l_listheader_rec.STATUS_CODE,'DRAFT')) ,
1484 decode(l_listheader_rec.STATUS_DATE ,FND_API.g_miss_date,sysdate,nvl(l_listheader_rec.STATUS_DATE,sysdate)) ,
1485 decode(l_listheader_rec.GENERATION_TYPE ,FND_API.g_miss_char,'STANDARD',nvl(l_listheader_rec.GENERATION_TYPE,'STANDARD')) ,
1486 decode(l_listheader_rec.REPEAT_EXCLUDE_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.REPEAT_EXCLUDE_TYPE) ,
1487 decode(l_listheader_rec.ROW_SELECTION_TYPE ,FND_API.g_miss_char,'STANDARD',nvl(l_listheader_rec.ROW_SELECTION_TYPE,'STANDARD')) ,
1488 decode(l_listheader_rec.OWNER_USER_ID ,FND_API.g_miss_num,null,l_listheader_rec.OWNER_USER_ID) ,
1489 decode(l_listheader_rec.ACCESS_LEVEL ,FND_API.g_miss_char,'USER',nvl(l_listheader_rec.ACCESS_LEVEL,'USER')) ,
1490 decode(l_listheader_rec.ENABLE_LOG_FLAG ,FND_API.g_miss_char,'Y',nvl(l_listheader_rec.ENABLE_LOG_FLAG,'Y')) ,
1491 decode(l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG,'N')) ,
1492 decode(l_listheader_rec.ENABLE_PARALLEL_DML_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.ENABLE_PARALLEL_DML_FLAG,'N')) ,
1493 decode(l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG,'N')) ,
1494 decode(l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG,'N')) ,
1495 decode(l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG,'N')) ,
1496 decode(l_listheader_rec.FORECASTED_START_DATE ,FND_API.g_miss_date,sysdate,nvl(l_listheader_rec.FORECASTED_START_DATE,sysdate)) ,
1497 decode(l_listheader_rec.FORECASTED_END_DATE ,FND_API.g_miss_date,null,l_listheader_rec.FORECASTED_END_DATE) ,
1498 decode(l_listheader_rec.ACTUAL_END_DATE ,FND_API.g_miss_date,null,l_listheader_rec.ACTUAL_END_DATE) ,
1499 decode(l_listheader_rec.SENT_OUT_DATE ,FND_API.g_miss_date,null,l_listheader_rec.SENT_OUT_DATE) ,
1500 decode(l_listheader_rec.DEDUPE_START_DATE ,FND_API.g_miss_date,null,l_listheader_rec.DEDUPE_START_DATE) ,
1501 decode(l_listheader_rec.LAST_DEDUPE_DATE ,FND_API.g_miss_date,null,l_listheader_rec.LAST_DEDUPE_DATE) ,
1502 decode(l_listheader_rec.LAST_DEDUPED_BY_USER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LAST_DEDUPED_BY_USER_ID) ,
1503 decode(l_listheader_rec.WORKFLOW_ITEM_KEY ,FND_API.g_miss_num,null,l_listheader_rec.WORKFLOW_ITEM_KEY) ,
1504 decode(l_listheader_rec.NO_OF_ROWS_DUPLICATES ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DUPLICATES) ,
1505 decode(l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED) ,
1506 decode(l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED) ,
1507 decode(l_listheader_rec.NO_OF_ROWS_IN_LIST ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_IN_LIST) ,
1508 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) ,
1509 decode(l_listheader_rec.NO_OF_ROWS_ACTIVE ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_ACTIVE) ,
1510 decode(l_listheader_rec.NO_OF_ROWS_INACTIVE ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_INACTIVE) ,
1511 decode(l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED) ,
1512 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) ,
1513 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) ,
1514 decode(l_listheader_rec.NO_OF_ROWS_RANDOM ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_RANDOM) ,
1515 /*decode(l_listheader_rec.ORG_ID ,FND_API.g_miss_num,
1516 TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)),
1517 nvl(l_listheader_rec.ORG_ID,TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)))) ,*/
1518
1519 decode(l_listheader_rec.ORG_ID ,FND_API.g_miss_num,l_org_id,nvl(l_listheader_rec.ORG_ID,l_org_id)),
1520
1521 decode(l_listheader_rec.MAIN_GEN_START_TIME ,FND_API.g_miss_date,null,l_listheader_rec.MAIN_GEN_START_TIME) ,
1522 decode(l_listheader_rec.MAIN_GEN_END_TIME ,FND_API.g_miss_date,null,l_listheader_rec.MAIN_GEN_END_TIME) ,
1523 decode(l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION) ,
1524 decode(l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION) ,
1525 decode(l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION) ,
1526 decode(l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION) ,
1527 decode(l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID ,FND_API.g_miss_char,null,l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID) ,
1528 decode(l_listheader_rec.RESULT_TEXT ,FND_API.g_miss_char,null,l_listheader_rec.RESULT_TEXT) ,
1529 decode(l_listheader_rec.KEYWORDS ,FND_API.g_miss_char,null,l_listheader_rec.KEYWORDS) ,
1530 decode(l_listheader_rec.DESCRIPTION ,FND_API.g_miss_char,null,l_listheader_rec.DESCRIPTION) ,
1531 decode(l_listheader_rec.LIST_PRIORITY ,FND_API.g_miss_num,null,l_listheader_rec.LIST_PRIORITY) ,
1532 decode(l_listheader_rec.ASSIGN_PERSON_ID ,FND_API.g_miss_num,null,l_listheader_rec.ASSIGN_PERSON_ID) ,
1533 decode(l_listheader_rec.LIST_SOURCE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_SOURCE) ,
1534 decode(l_listheader_rec.LIST_SOURCE_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_SOURCE_TYPE) ,
1535 decode(l_listheader_rec.LIST_ONLINE_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.LIST_ONLINE_FLAG) ,
1536 decode(l_listheader_rec.RANDOM_LIST_ID ,FND_API.g_miss_num,null,l_listheader_rec.RANDOM_LIST_ID) ,
1537 decode(l_listheader_rec.ENABLED_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.ENABLED_FLAG) ,
1538 decode(l_listheader_rec.ASSIGNED_TO ,FND_API.g_miss_num,null,l_listheader_rec.ASSIGNED_TO) ,
1539 decode(l_listheader_rec.QUERY_ID ,FND_API.g_miss_num,null,l_listheader_rec.QUERY_ID) ,
1540 decode(l_listheader_rec.OWNER_PERSON_ID ,FND_API.g_miss_num,null,l_listheader_rec.OWNER_PERSON_ID) ,
1541 decode(l_listheader_rec.ARCHIVED_BY ,FND_API.g_miss_num,null,l_listheader_rec.ARCHIVED_BY) ,
1542 decode(l_listheader_rec.ARCHIVED_DATE ,FND_API.g_miss_date,null,l_listheader_rec.ARCHIVED_DATE) ,
1543 decode(l_listheader_rec.ATTRIBUTE_CATEGORY ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE_CATEGORY) ,
1544 decode(l_listheader_rec.ATTRIBUTE1 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE1) ,
1545 decode(l_listheader_rec.ATTRIBUTE2 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE2) ,
1549 decode(l_listheader_rec.ATTRIBUTE6 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE6) ,
1546 decode(l_listheader_rec.ATTRIBUTE3 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE3) ,
1547 decode(l_listheader_rec.ATTRIBUTE4 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE4) ,
1548 decode(l_listheader_rec.ATTRIBUTE5 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE5) ,
1550 decode(l_listheader_rec.ATTRIBUTE7 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE7) ,
1551 decode(l_listheader_rec.ATTRIBUTE8 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE8) ,
1552 decode(l_listheader_rec.ATTRIBUTE9 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE9) ,
1553 decode(l_listheader_rec.ATTRIBUTE10 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE10) ,
1554 decode(l_listheader_rec.ATTRIBUTE11 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE11) ,
1555 decode(l_listheader_rec.ATTRIBUTE12 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE12) ,
1556 decode(l_listheader_rec.ATTRIBUTE13 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE13) ,
1557 decode(l_listheader_rec.ATTRIBUTE14 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE14) ,
1558 decode(l_listheader_rec.ATTRIBUTE15 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE15) ,
1559 decode(l_listheader_rec.TIMEZONE_ID ,FND_API.g_miss_num,null,l_listheader_rec.TIMEZONE_ID) ,
1560 decode(l_listheader_rec.USER_ENTERED_START_TIME ,FND_API.g_miss_date,null,l_listheader_rec.USER_ENTERED_START_TIME) ,
1561 -- decode(l_listheader_rec.USER_STATUS_ID ,FND_API.g_miss_num,300,nvl(l_listheader_rec.USER_STATUS_ID,300)) ,
1562 decode(l_listheader_rec.USER_STATUS_ID ,FND_API.g_miss_num,null,l_listheader_rec.USER_STATUS_ID) ,
1563 decode(l_listheader_rec.QUANTUM ,FND_API.g_miss_num,null,l_listheader_rec.QUANTUM) ,
1564 decode(l_listheader_rec.RELEASE_CONTROL_ALG_ID ,FND_API.g_miss_num,null,l_listheader_rec.RELEASE_CONTROL_ALG_ID) ,
1565 decode(l_listheader_rec.DIALING_METHOD ,FND_API.g_miss_char,null,l_listheader_rec.DIALING_METHOD) ,
1566 decode(l_listheader_rec.CALLING_CALENDAR_ID ,FND_API.g_miss_num,null,l_listheader_rec.CALLING_CALENDAR_ID) ,
1567 decode(l_listheader_rec.RELEASE_STRATEGY ,FND_API.g_miss_char,null,l_listheader_rec.RELEASE_STRATEGY) ,
1568 decode(l_listheader_rec.CUSTOM_SETUP_ID ,FND_API.g_miss_num,null,l_listheader_rec.CUSTOM_SETUP_ID) ,
1569 decode(l_listheader_rec.COUNTRY ,FND_API.g_miss_num,null,l_listheader_rec.COUNTRY) ,
1570 decode(l_listheader_rec.CALLBACK_PRIORITY_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.CALLBACK_PRIORITY_FLAG) ,
1571 decode(l_listheader_rec.CALL_CENTER_READY_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.CALL_CENTER_READY_FLAG) ,
1572 decode(l_listheader_rec.PURGE_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.PURGE_FLAG) ,
1573 decode(l_listheader_rec.QUOTA ,FND_API.g_miss_num,null,l_listheader_rec.QUOTA) ,
1574 decode(l_listheader_rec.QUOTA_RESET ,FND_API.g_miss_num,null,l_listheader_rec.QUOTA_RESET) ,
1575 decode(l_listheader_rec.RECYCLING_ALG_ID ,FND_API.g_miss_num,null,l_listheader_rec.RECYCLING_ALG_ID) ,
1576 decode(l_listheader_rec.PUBLIC_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.PUBLIC_FLAG) ,
1577 decode(l_listheader_rec.LIST_CATEGORY ,FND_API.g_miss_char,null,l_listheader_rec.LIST_CATEGORY) ,
1578 decode(l_listheader_rec.NO_OF_ROWS_prev_contacted ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_prev_contacted) ,
1579 decode(l_listheader_rec.APPLY_TRAFFIC_COP ,FND_API.g_miss_char,null,l_listheader_rec.APPLY_TRAFFIC_COP),
1580 decode(l_listheader_rec.PURPOSE_CODE ,FND_API.g_miss_char,'GENERAL',null,'GENERAL',l_listheader_rec.PURPOSE_CODE) ,
1581
1582 -- ckapoor R12 enhancement for copy target group changes
1583 decode(l_listheader_rec.CTRL_CONF_LEVEL ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_CONF_LEVEL) ,
1584 decode(l_listheader_rec.CTRL_REQ_RESP_RATE ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_REQ_RESP_RATE) ,
1585 decode(l_listheader_rec.CTRL_LIMIT_OF_ERROR ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_LIMIT_OF_ERROR) ,
1586 decode(l_listheader_rec.STATUS_CODE_OLD ,FND_API.g_miss_char,null,l_listheader_rec.STATUS_CODE_OLD) ,
1587 decode(l_listheader_rec.CTRL_CONC_JOB_ID ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_CONC_JOB_ID) ,
1588 decode(l_listheader_rec.CTRL_STATUS_CODE ,FND_API.g_miss_char,null,l_listheader_rec.CTRL_STATUS_CODE) ,
1589 decode(l_listheader_rec.CTRL_GEN_MODE ,FND_API.g_miss_char,null, l_listheader_rec.CTRL_GEN_MODE) ,
1590 decode(l_listheader_rec.APPLY_SUPPRESSION_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.APPLY_SUPPRESSION_FLAG)
1591 -- end ckapoor R12 enhancement for copy target group changes
1592
1593 );
1594
1595 insert into AMS_LIST_HEADERS_ALL_TL (
1596 LANGUAGE,
1597 SOURCE_LANG,
1598 LIST_NAME,
1599 DESCRIPTION,
1600 LIST_HEADER_ID,
1601 LAST_UPDATE_DATE,
1602 LAST_UPDATE_BY,
1603 CREATION_DATE,
1604 CREATED_BY,
1605 LAST_UPDATE_LOGIN
1606 ) select
1607 l.language_code,
1608 userenv('LANG'),
1609 decode(l_listheader_rec.LIST_NAME ,FND_API.g_miss_char,null,l_listheader_rec.LIST_NAME) ,
1610 decode(l_listheader_rec.DESCRIPTION ,FND_API.g_miss_char,null,l_listheader_rec.DESCRIPTION) ,
1611 decode(l_listheader_rec.LIST_HEADER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LIST_HEADER_ID) ,
1612 sysdate,
1613 FND_GLOBAL.user_id,
1614 sysdate,
1615 FND_GLOBAL.user_id,
1616 FND_GLOBAL.conc_login_id
1617 from FND_LANGUAGES L
1618 where L.INSTALLED_FLAG in ('I', 'B')
1619 and not exists
1620 (select NULL
1621 from AMS_LIST_HEADERS_ALL_TL T
1622 where T.LIST_HEADER_ID = l_listheader_rec.LIST_HEADER_ID
1623 and T.LANGUAGE = L.LANGUAGE_CODE);
1624
1625
1626 -- set OUT value
1627 x_listheader_id := l_listheader_rec.list_header_id;
1628
1629 -- Standard check of p_commit.
1630 IF FND_API.To_Boolean ( p_commit ) THEN
1631 COMMIT WORK;
1632 END IF;
1633
1634 -- Success Message
1635 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1636 THEN
1637 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1641
1638 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Create_listheaders', TRUE);
1639 FND_MSG_PUB.Add;
1640 END IF;
1642 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1643 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1644 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Create_listheaders: END', TRUE);
1645 FND_MSG_PUB.Add;
1646 END IF; */
1647
1648
1649 -- Standard call to get message count AND IF count is 1, get message info.
1650 FND_MSG_PUB.Count_AND_Get
1651 ( p_count => x_msg_count,
1652 p_data => x_msg_data,
1653 p_encoded => FND_API.G_FALSE
1654 );
1655
1656 EXCEPTION
1657 WHEN FND_API.G_EXC_ERROR THEN
1658 ROLLBACK TO Create_listheaders_PVT;
1659 x_return_status := FND_API.G_RET_STS_ERROR ;
1660
1661 FND_MSG_PUB.Count_AND_Get
1662 ( p_count => x_msg_count,
1663 p_data => x_msg_data,
1664 p_encoded => FND_API.G_FALSE
1665 );
1666
1667
1668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1669 ROLLBACK TO Create_listheaders_PVT;
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1671 FND_MSG_PUB.Count_AND_Get
1672 ( p_count => x_msg_count,
1673 p_data => x_msg_data,
1674 p_encoded => FND_API.G_FALSE
1675 );
1676
1677 WHEN OTHERS THEN
1678 ROLLBACK TO Create_listheaders_PVT;
1679 FND_MESSAGE.set_name('AMS','SQL ERROR ->' || sqlerrm );
1680 FND_MSG_PUB.Add;
1681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1682 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1683 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1684 END IF;
1685
1686 FND_MSG_PUB.Count_AND_Get
1687 ( p_count => x_msg_count,
1688 p_data => x_msg_data,
1689 p_encoded => FND_API.G_FALSE
1690 );
1691
1692 END Create_listheader;
1693
1694
1695 -- Start of Comments
1696 -----------------------------------------------------------------------------
1697 -- NAME
1698 -- Update_listheader
1699 --
1700 -- PURPOSE
1701 -- This procedure is to update a List Header record that satisfy caller needs
1702 --
1703 -- NOTES
1704 --
1705 --
1706 -- HISTORY
1707 -- 05/12/1999 tdonohoe created
1708 -- 03/02/2000 tdonohoe modified code to update Source Code details on the
1709 -- list entry table when the list association is updated.
1710 -- 02/11/2000 tdonohoe modified code to update STATUS_CODE to LOCKED when the
1711 -- sent out date is populated.
1712 -- End of Comments
1713
1714 PROCEDURE Update_ListHeader
1715 ( p_api_version IN NUMBER,
1716 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1717 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1718 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1719 x_return_status OUT NOCOPY VARCHAR2,
1720 x_msg_count OUT NOCOPY NUMBER,
1721 x_msg_data OUT NOCOPY VARCHAR2,
1722 p_listheader_rec IN list_header_rec_type
1723 ) IS
1724
1725 l_api_name CONSTANT VARCHAR2(30) := 'Update_ListHeader';
1726 l_api_version CONSTANT NUMBER := 1.0;
1727 -- Status Local Variables
1728 l_return_status VARCHAR2(1); -- Return value from procedures
1729 l_listheader_rec list_header_rec_type := p_listheader_rec;
1730 l_sqlerrm varchar2(600);
1731 l_sqlcode varchar2(100);
1732 l_msg_count number;
1733 l_msg_data varchar2(500);
1734 l_item_type varchar2(100) := 'AMSLISTG';
1735 l_item_key varchar2(100);
1736 cursor check_wf
1737 is select item_key
1738 from wf_item_activity_statuses
1739 where item_type = l_item_type
1740 and item_key like p_listheader_rec.list_header_id || '_%'
1741 and activity_status in ('ERROR','ACTIVE');
1742
1743 BEGIN
1744 -- Standard Start of API savepoint
1745 SAVEPOINT Update_listheaders_PVT;
1746 -- Standard call to check for call compatibility.
1747 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1748 p_api_version,
1749 l_api_name,
1750 G_PKG_NAME) THEN
1751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1752 END IF;
1753 -- Initialize message list IF p_init_msg_list is set to TRUE.
1754 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1755 FND_MSG_PUB.initialize;
1756 END IF;
1757 -- Debug Message
1758 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1759 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1760 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Start', TRUE);
1761 FND_MSG_PUB.Add;
1762 END IF; */
1763
1764 IF (AMS_DEBUG_HIGH_ON) THEN
1765 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Start');
1766 END IF;
1767
1768
1769 ----------------------------------------------------------
1770 -- Initialize API return status to success
1771 ----------------------------------------------------------
1772 x_return_status := FND_API.G_RET_STS_SUCCESS;
1773
1774 ----------------------------------------------------------
1775 -- replace g_miss_char/num/date with current column values
1776 ----------------------------------------------------------
1780 AND p_listheader_rec.USER_ENTERED_START_TIME is not null)THEN
1777 complete_listheader_rec(p_listheader_rec, l_listheader_rec);
1778
1779 IF (p_listheader_rec.USER_ENTERED_START_TIME <> fnd_api.g_miss_date
1781
1782 if (p_listheader_rec.timezone_id is not null AND
1783 p_listheader_rec.timezone_id <> FND_API.g_miss_num ) then
1784 AMS_UTILITY_PVT.Convert_Timezone(
1785 p_init_msg_list => p_init_msg_list,
1786 x_return_status => x_return_status,
1787 x_msg_count => x_msg_count,
1788 x_msg_data => x_msg_data,
1789 p_user_tz_id => l_listheader_rec.TIMEZONE_ID,
1790 p_in_time => l_listheader_rec.USER_ENTERED_START_TIME,
1791 p_convert_type => 'SYS',
1792 x_out_time => l_listheader_rec.MAIN_GEN_START_TIME);
1793 end if;
1794
1795 -- If any errors happen abort API.
1796 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1797 RAISE FND_API.G_EXC_ERROR;
1798 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1800 END IF;
1801 END IF;
1802
1803 -- Debug Message
1804 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1805 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1806 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Done Timezone', TRUE);
1807 FND_MSG_PUB.Add;
1808 END IF; */
1809
1810 IF (AMS_DEBUG_HIGH_ON) THEN
1811 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Done timezone');
1812 END IF;
1813
1814
1815
1816 ----------------------- validate ----------------------
1817 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1818 Validate_list_items(
1819 p_listheader_rec => l_listheader_rec,
1820 p_validation_mode => JTF_PLSQL_API.g_update,
1821 x_return_status => x_return_status
1822 );
1823
1824 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1825 RAISE FND_API.g_exc_unexpected_error;
1826 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1827 RAISE FND_API.g_exc_error;
1828 END IF;
1829 END IF;
1830
1831 -- Debug Message
1832 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
1833 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1834 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Item Level Validation', TRUE);
1835 FND_MSG_PUB.Add;
1836 END IF; */
1837
1838 IF (AMS_DEBUG_HIGH_ON) THEN
1839 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Item level validation');
1840 END IF;
1841
1842
1843 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1844 Validate_list_record(
1845 p_listheader_rec => p_listheader_rec,
1846 p_complete_rec => l_listheader_rec,
1847 x_return_status => x_return_status
1848 );
1849
1850 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1851 RAISE FND_API.g_exc_unexpected_error;
1852 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1853 RAISE FND_API.g_exc_error;
1854 END IF;
1855 END IF;
1856
1857 -- Perform the database operation
1858 /* ckapoor IF (AMS_DEBUG_LOW_ON) THEN
1859 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1860 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT - update ams_listheaders', TRUE);
1861 FND_MSG_PUB.Add;
1862 END IF; */
1863
1864 IF (AMS_DEBUG_LOW_ON) THEN
1865 AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.update ams_listheaders');
1866 END IF;
1867
1868
1872
1869 IF (l_listheader_rec.STATUS_CODE ='DRAFT' OR
1870 l_listheader_rec.STATUS_CODE ='ARCHIVED' OR
1871 l_listheader_rec.STATUS_CODE ='CANCELLED' ) THEN
1873 open check_wf ;
1874 fetch check_wf into l_item_key;
1875 close check_wf ;
1876 if l_item_key is not null then
1877 begin
1878 WF_ENGINE.abortProcess(l_item_type ,
1879 l_item_key);
1880 exception
1881 when no_data_found then
1882 null;
1883 end;
1884 end if;
1885
1886 END IF;
1887
1888 IF(l_listheader_rec.STATUS_CODE ='CANCELLED' ) THEN
1889 -- Delete all existing entries for this list which are in the
1890 -- temporary table.--
1891 --------------------------------------------------------------------------
1892 DELETE FROM ams_list_tmp_entries
1893 WHERE list_header_id = l_listheader_rec.list_header_id;
1894
1895 ----------------------------------------------
1896 --Delete all existing entries for this list.--
1897 ----------------------------------------------
1898 DELETE FROM ams_list_entries
1899 WHERE list_header_id =l_listheader_rec.list_header_id;
1900
1901 ------------------------------------------------------------------------------
1902 --Delete all entries in the ams_list_src_type_usages table. --
1903 --These entries must be refreshed each time that a list is refreshed. --
1904 ------------------------------------------------------------------------------
1905 DELETE FROM ams_list_src_type_usages
1906 WHERE list_header_id =l_listheader_rec.list_header_id;
1907
1908 --clear associations
1909 l_listheader_rec.list_used_by_id:=0;
1910 l_listheader_rec.arc_list_used_by:='NONE';
1911 --set size of list to be NULL
1912 l_listheader_rec.no_of_rows_in_list:=NULL;
1913 l_listheader_rec.no_of_rows_active:=NULL;
1914 l_listheader_rec.no_of_rows_inactive:=NULL;
1915 l_listheader_rec.no_of_rows_duplicates:=NULL;
1916 END IF;
1917
1918
1919 UPDATE ams_list_headers_all
1920 SET
1921 last_update_date = sysdate
1922 ,last_updated_by = FND_GLOBAL.User_Id
1923 ,last_update_login = FND_GLOBAL.Conc_Login_Id
1924 ,object_version_number = l_listheader_rec.object_version_number + 1
1925 ,request_id = l_listheader_rec.request_id
1926 ,program_id = l_listheader_rec.program_id
1927 ,program_application_id = l_listheader_rec.program_application_id
1928 ,quantum = l_listheader_rec.quantum
1929 ,release_control_alg_id = l_listheader_rec.release_control_alg_id
1930 ,dialing_method = l_listheader_rec.dialing_method
1931 ,calling_calendar_id = l_listheader_rec.calling_calendar_id
1932 ,release_strategy = l_listheader_rec.release_strategy
1933 ,custom_setup_id = l_listheader_rec.custom_setup_id
1934 ,country = l_listheader_rec.country
1935 ,purge_flag = l_listheader_rec.PURGE_FLAG
1936 ,public_flag = l_listheader_rec.public_flag
1937 ,list_category = l_listheader_rec.list_category
1938 ,quota = l_listheader_rec.QUOTA
1939 ,quota_reset = l_listheader_rec.QUOTA_RESET
1940 ,recycling_alg_id = l_listheader_rec.RECYCLING_ALG_ID
1941 ,callback_priority_flag = l_listheader_rec.callback_priority_flag
1942 ,call_center_ready_flag = l_listheader_rec.call_center_ready_flag
1943 ,user_status_id = l_listheader_rec.user_status_id
1944 ,program_update_date = l_listheader_rec.program_update_date
1945 --,list_name = l_listheader_rec.list_name
1946 ,list_used_by_id = l_listheader_rec.list_used_by_id
1947 ,arc_list_used_by = l_listheader_rec.arc_list_used_by
1948 ,list_type = l_listheader_rec.list_type
1949 ,status_code = l_listheader_rec.status_code
1950 ,status_date = l_listheader_rec.status_date
1951 ,generation_type = l_listheader_rec.generation_type
1952 ,row_selection_type = l_listheader_rec.row_selection_type
1953 ,owner_user_id = l_listheader_rec.owner_user_id
1954 ,access_level = l_listheader_rec.access_level
1955 ,enable_log_flag = l_listheader_rec.enable_log_flag
1956 ,enable_word_replacement_flag = l_listheader_rec.enable_word_replacement_flag
1957 ,dedupe_during_generation_flag = l_listheader_rec.dedupe_during_generation_flag --added vbhandar 10/12/2000
1958 ,generate_control_group_flag = l_listheader_rec.generate_control_group_flag
1959 ,forecasted_start_date = l_listheader_rec.forecasted_start_date
1960 ,forecasted_end_date = l_listheader_rec.forecasted_end_date
1961 ,actual_end_date = l_listheader_rec.actual_end_date
1962 ,sent_out_date = l_listheader_rec.sent_out_date
1963 ,dedupe_start_date = l_listheader_rec.dedupe_start_date
1964 ,last_dedupe_date = l_listheader_rec.last_dedupe_date
1965 ,last_deduped_by_user_id = l_listheader_rec.last_deduped_by_user_id
1966 ,workflow_item_key = l_listheader_rec.workflow_item_key
1967 ,no_of_rows_duplicates = l_listheader_rec.no_of_rows_duplicates
1968 ,no_of_rows_min_requested = l_listheader_rec.no_of_rows_min_requested
1969 ,no_of_rows_max_requested = l_listheader_rec.no_of_rows_max_requested
1970 ,no_of_rows_in_list = l_listheader_rec.no_of_rows_in_list
1971 ,no_of_rows_in_ctrl_group = l_listheader_rec.no_of_rows_in_ctrl_group
1972 ,no_of_rows_active = l_listheader_rec.no_of_rows_active
1973 ,no_of_rows_inactive = l_listheader_rec.no_of_rows_inactive
1974 ,no_of_rows_manually_entered = l_listheader_rec.no_of_rows_manually_entered
1978 ,timezone_id = l_listheader_rec.timezone_id
1975 ,no_of_rows_do_not_call = l_listheader_rec.no_of_rows_do_not_call
1976 ,no_of_rows_do_not_mail = l_listheader_rec.no_of_rows_do_not_mail
1977 ,no_of_rows_random = l_listheader_rec.no_of_rows_random
1979 ,user_entered_start_time = l_listheader_rec.user_entered_start_time
1980 ,main_gen_start_time = l_listheader_rec.main_gen_start_time
1981 ,main_gen_end_time = l_listheader_rec.main_gen_end_time
1982 ,main_random_nth_row_selection = l_listheader_rec.main_random_nth_row_selection
1983 ,main_random_pct_row_selection = l_listheader_rec.main_random_pct_row_selection
1984 ,ctrl_random_nth_row_selection = l_listheader_rec.ctrl_random_nth_row_selection
1985 ,ctrl_random_pct_row_selection = l_listheader_rec.ctrl_random_pct_row_selection
1986 ,result_text = l_listheader_rec.result_text
1987 ,keywords = l_listheader_rec.keywords
1988 -- ,description = l_listheader_rec.description
1989 ,list_priority = l_listheader_rec.list_priority
1990 ,assign_person_id = l_listheader_rec.assign_person_id
1991 ,list_source = l_listheader_rec.list_source
1992 ,list_source_type = l_listheader_rec.list_source_type
1993 ,list_online_flag = l_listheader_rec.list_online_flag
1994 ,random_list_id = l_listheader_rec.random_list_id
1995 ,enabled_flag = l_listheader_rec.enabled_flag
1996 ,assigned_to = l_listheader_rec.assigned_to
1997 ,query_id = l_listheader_rec.query_id
1998 ,owner_person_id = l_listheader_rec.owner_person_id
1999 ,attribute_category = l_listheader_rec.attribute_category
2000 ,attribute1 = l_listheader_rec.attribute1
2001 ,attribute2 = l_listheader_rec.attribute2
2002 ,attribute3 = l_listheader_rec.attribute3
2003 ,attribute4 = l_listheader_rec.attribute4
2004 ,attribute5 = l_listheader_rec.attribute5
2005 ,attribute6 = l_listheader_rec.attribute6
2006 ,attribute7 = l_listheader_rec.attribute7
2007 ,attribute8 = l_listheader_rec.attribute8
2008 ,attribute9 = l_listheader_rec.attribute9
2009 ,attribute10 = l_listheader_rec.attribute10
2010 ,attribute11 = l_listheader_rec.attribute11
2011 ,attribute12 = l_listheader_rec.attribute12
2012 ,attribute13 = l_listheader_rec.attribute13
2013 ,attribute14 = l_listheader_rec.attribute14
2014 ,attribute15 = l_listheader_rec.attribute15
2015 ,no_of_rows_prev_contacted = l_listheader_rec.no_of_rows_prev_contacted
2016 ,apply_traffic_cop =l_listheader_rec.apply_traffic_cop
2017
2018 -- ckapoor R12 control group enhancements
2019
2020 ,CTRL_CONF_LEVEL =l_listheader_rec.CTRL_CONF_LEVEL
2021 ,CTRL_REQ_RESP_RATE =l_listheader_rec.CTRL_REQ_RESP_RATE
2022 ,CTRL_LIMIT_OF_ERROR =l_listheader_rec.CTRL_LIMIT_OF_ERROR
2023 ,STATUS_CODE_OLD =l_listheader_rec.STATUS_CODE_OLD
2024 ,CTRL_CONC_JOB_ID =l_listheader_rec.CTRL_CONC_JOB_ID
2025 ,CTRL_STATUS_CODE =l_listheader_rec.CTRL_STATUS_CODE
2026 ,CTRL_GEN_MODE =l_listheader_rec.CTRL_GEN_MODE
2027 ,APPLY_SUPPRESSION_FLAG =l_listheader_rec.APPLY_SUPPRESSION_FLAG
2028
2029 -- end ckapoor R12 control group enhancements
2030
2031
2032
2033 WHERE list_header_id = l_listheader_rec.list_header_id;
2034 -- AND object_version_number = l_listheader_rec.object_version_number;
2035
2036 IF (SQL%NOTFOUND)THEN
2037 ------------------------------------------------------------------
2038 -- Error, check the msg level and added an error message to the --
2039 -- API message list. --
2040 ------------------------------------------------------------------
2041 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2042 FND_MESSAGE.set_name('AMS', 'API_UNEXP_ERROR_IN_PROCESSING');
2043 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders API', TRUE);
2044 FND_MSG_PUB.Add;
2045 END IF;
2046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2047 END IF;
2048
2049 update AMS_LIST_HEADERS_ALL_TL set
2050 LIST_NAME = l_listheader_rec.LIST_NAME,
2051 DESCRIPTION = l_listheader_rec.DESCRIPTION,
2052 LAST_UPDATE_DATE = sysdate,
2053 LAST_UPDATE_BY = FND_GLOBAL.user_id,
2054 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
2055 SOURCE_LANG = userenv('LANG')
2056 where LIST_HEADER_ID = l_listheader_rec.LIST_HEADER_ID
2057 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2058
2059 IF (SQL%NOTFOUND)THEN
2060 ------------------------------------------------------------------
2061 -- Error, check the msg level and added an error message to the --
2062 -- API message list. --
2063 ------------------------------------------------------------------
2064 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2065 FND_MESSAGE.set_name('AMS', 'API_UNEXP_ERROR_IN_PROCESSING');
2066 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders API', TRUE);
2067 FND_MSG_PUB.Add;
2068 END IF;
2069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2070 END IF;
2071
2072 --added tdonohoe 03/02/2000
2073 -----------------------------------------------------------------------
2074 --If the List Association has changed then any existing list entries --
2075 --must be updated. --
2076 --The Procedure which is called checks if the list association is new--
2077 --only then will it perfrom an update of the list entries. --
2078 -----------------------------------------------------------------------
2082 p_list_id => l_listheader_rec.list_header_id,
2079 IF(l_listheader_rec.ARC_LIST_USED_BY <>'NONE')THEN
2080 AMS_ListEntry_PVT.Update_ListEntry_Source_Code
2081 (p_api_version => 1.0,
2083 x_return_status => x_return_status,
2084 x_msg_count => l_msg_count,
2085 x_msg_data => l_msg_data);
2086 END IF;
2087 --end added tdonohoe 03/02/2000
2088
2089
2090 -- Standard check of p_commit.
2091 IF FND_API.To_Boolean ( p_commit ) THEN
2092 COMMIT WORK;
2093 END IF;
2094
2095 -- Success Message
2096 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2097 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
2098 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders', TRUE);
2099 FND_MSG_PUB.Add;
2100 END IF;
2101
2102 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
2103 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2104 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: END', TRUE);
2105 FND_MSG_PUB.Add;
2106 END IF; */
2107
2108 -- Standard call to get message count AND IF count is 1, get message info.
2109 FND_MSG_PUB.Count_AND_Get
2110 ( p_count => x_msg_count,
2111 p_data => x_msg_data,
2112 p_encoded => FND_API.G_FALSE
2113 );
2114 EXCEPTION
2115 WHEN FND_API.G_EXC_ERROR THEN
2116 ROLLBACK TO Update_listheaders_PVT;
2117 x_return_status := FND_API.G_RET_STS_ERROR ;
2118 FND_MSG_PUB.Count_AND_Get
2119 ( p_count => x_msg_count,
2120 p_data => x_msg_data,
2121 p_encoded => FND_API.G_FALSE
2122 );
2123
2124
2125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2126 ROLLBACK TO Update_listheaders_PVT;
2127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2128 FND_MSG_PUB.Count_AND_Get
2129 ( p_count => x_msg_count,
2130 p_data => x_msg_data,
2131 p_encoded => FND_API.G_FALSE
2132 );
2133 WHEN OTHERS THEN
2134 ROLLBACK TO Update_listheaders_PVT;
2135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2136 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2137 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2138 END IF;
2139
2140 FND_MSG_PUB.Count_AND_Get
2141 ( p_count => x_msg_count,
2142 p_data => x_msg_data,
2143 p_encoded => FND_API.G_FALSE
2144 );
2145
2146 END Update_listheader;
2147
2148
2149 -- Start of Comments
2150 --
2151 -- NAME
2152 -- Delete_listheader
2153 --
2154 -- PURPOSE
2155 -- This procedure deletes a list header record that satisfy caller needs
2156 --
2157 -- NOTES
2158 -- Deletes from The following tables Ams_List_Src_Type_Usages,
2159 -- Ams_List_Rule_Usages,
2160 -- Ams_List_Entries,
2161 -- Ams_List_Select_Actions
2162 -- Ams_List_Headers_All.
2163 --
2164 -- HISTORY
2165 -- 05/12/1999 tdonohoe created
2166 -- End of Comments
2167
2168 PROCEDURE Delete_ListHeader
2169 ( p_api_version IN NUMBER,
2170 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2171 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2172 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2173 x_return_status OUT NOCOPY VARCHAR2,
2174 x_msg_count OUT NOCOPY NUMBER,
2175 x_msg_data OUT NOCOPY VARCHAR2,
2176 p_listheader_id IN number) IS
2177
2178 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListHeader';
2179 l_api_version CONSTANT NUMBER := 1.0;
2180
2181 -- Status Local Variables
2182 l_return_status VARCHAR2(1); -- Return value from procedures
2183 l_list_header_id NUMBER := p_listheader_id;
2184 l_return_val VARCHAR2(1);
2185
2186 BEGIN
2187 -- Standard Start of API savepoint
2188 SAVEPOINT Delete_listheader_PVT;
2189
2190 -- Standard call to check for call compatibility.
2191 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2192 p_api_version,
2193 l_api_name,
2194 G_PKG_NAME) THEN
2195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2196 END IF;
2197
2198 -- Initialize message list IF p_init_msg_list is set to TRUE.
2199 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2200 FND_MSG_PUB.initialize;
2201 END IF;
2202
2203 -- Debug Message
2204 IF (AMS_DEBUG_HIGH_ON) THEN
2205 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2206 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheaders: Start', TRUE);
2207 FND_MSG_PUB.Add;
2208 END IF;
2209
2210 -- Initialize API return status to success
2211 x_return_status := FND_API.G_RET_STS_SUCCESS;
2212
2213
2214 -- Check required parameters
2215 IF (l_list_header_id = FND_API.G_MISS_NUM OR l_list_header_id IS NULL )THEN
2216 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2217 FND_MESSAGE.Set_Name('AMS', 'API_INCOMPLETE_INFO');
2218 FND_MESSAGE.Set_Token ('PARAM', 'listheader_id', FALSE);
2219 FND_MSG_PUB.Add;
2220 END IF;
2224 -- Perform the database operation
2221 RAISE FND_API.G_EXC_ERROR;
2222 END IF;
2223
2225
2226 IF (AMS_DEBUG_LOW_ON) THEN
2227 NULL;
2228 END IF;
2229
2230 DELETE FROM ams_list_src_type_usages
2231 WHERE list_header_id = l_list_header_id;
2232
2233 DELETE FROM ams_list_rule_usages
2234 WHERE list_header_id = l_list_header_id;
2235
2236 DELETE FROM ams_list_entries
2237 WHERE list_header_id = l_list_header_id;
2238
2239 DELETE FROM ams_list_select_actions
2240 WHERE list_header_id = l_list_header_id;
2241
2242 DELETE FROM ams_list_headers_all
2243 WHERE list_header_id = l_list_header_id;
2244
2245
2246 -- Standard check of p_commit.
2247 IF FND_API.To_Boolean ( p_commit ) THEN
2248 COMMIT WORK;
2249 END IF;
2250
2251 -- Success Message
2252 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2253 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
2254 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheaders', TRUE);
2255 FND_MSG_PUB.Add;
2256 END IF;
2257
2258
2259 IF (AMS_DEBUG_HIGH_ON) THEN
2260 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2261 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheader: END', TRUE);
2262 FND_MSG_PUB.Add;
2263 END IF;
2264
2265
2266 -- Standard call to get message count AND IF count is 1, get message info.
2267 FND_MSG_PUB.Count_AND_Get
2268 ( p_count => x_msg_count,
2269 p_data => x_msg_data,
2270 p_encoded => FND_API.G_FALSE
2271 );
2272 EXCEPTION
2273 WHEN FND_API.G_EXC_ERROR THEN
2274 ROLLBACK TO Delete_listheader_PVT;
2275 x_return_status := FND_API.G_RET_STS_ERROR ;
2276
2277 FND_MSG_PUB.Count_AND_Get
2278 ( p_count => x_msg_count,
2279 p_data => x_msg_data,
2280 p_encoded => FND_API.G_FALSE
2281 );
2282
2283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2284 ROLLBACK TO Delete_listheader_PVT;
2285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2286
2287 FND_MSG_PUB.Count_AND_Get
2288 ( p_count => x_msg_count,
2289 p_data => x_msg_data,
2290 p_encoded => FND_API.G_FALSE
2291 );
2292
2293
2294 WHEN OTHERS THEN
2295 ROLLBACK TO Delete_listheader_PVT;
2296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2297 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2298 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2299 END IF;
2300
2301 FND_MSG_PUB.Count_AND_Get
2302 ( p_count => x_msg_count,
2303 p_data => x_msg_data,
2304 p_encoded => FND_API.G_FALSE
2305 );
2306
2307 END Delete_listheader;
2308
2309
2310
2311 -- Start of Comments
2312 --
2313 -- NAME
2314 -- Lock_listheader
2315 --
2316 -- PURPOSE
2317 -- This procedure is to lock a list header record that satisfy caller needs
2318 --
2319 -- NOTES
2320 --
2321 --
2322 -- HISTORY
2323 -- 05/13/1999 tdonohoe created
2324 -- End of Comments
2325
2326
2327 PROCEDURE Lock_ListHeader
2328 ( p_api_version IN NUMBER,
2329 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2330 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2331 x_return_status OUT NOCOPY VARCHAR2,
2332 x_msg_count OUT NOCOPY NUMBER,
2333 x_msg_data OUT NOCOPY VARCHAR2,
2334 p_listheader_id IN NUMBER,
2335 p_object_version IN NUMBER
2336 ) IS
2337
2338
2339 l_api_name CONSTANT VARCHAR2(30) := 'Lock_ListHeader';
2340 l_api_version CONSTANT NUMBER := 1.0;
2341 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2342
2343 l_list_header_id NUMBER;
2344
2345 CURSOR c_list_header_id IS
2346 SELECT list_header_id
2347 FROM ams_list_headers_all
2348 WHERE list_header_id = p_listheader_id
2349 AND object_version_number = p_object_version
2350 FOR UPDATE OF list_header_id NOWAIT;
2351
2352 BEGIN
2353
2354 -------------------- initialize ------------------------
2355 IF (AMS_DEBUG_HIGH_ON) THEN
2356
2357 AMS_Utility_PVT.debug_message(l_full_name||': start');
2358 END IF;
2359
2360 IF FND_API.to_boolean(p_init_msg_list) THEN
2361 FND_MSG_PUB.initialize;
2362 END IF;
2363
2364 IF NOT FND_API.compatible_api_call(
2365 l_api_version,
2366 p_api_version,
2367 l_api_name,
2368 g_pkg_name
2369 ) THEN
2370 RAISE FND_API.g_exc_unexpected_error;
2371 END IF;
2372
2373 x_return_status := FND_API.G_RET_STS_SUCCESS;
2374
2375 ------------------------ lock -------------------------
2376 IF (AMS_DEBUG_HIGH_ON) THEN
2377
2378 AMS_Utility_PVT.debug_message(l_full_name||': lock');
2379 END IF;
2380
2381 OPEN c_list_header_id;
2382 FETCH c_list_header_id INTO l_list_header_id;
2383 IF (c_list_header_id%NOTFOUND) THEN
2384 CLOSE c_list_header_id;
2385 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2386 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2390 END IF;
2387 FND_MSG_PUB.add;
2388 END IF;
2389 RAISE FND_API.g_exc_error;
2391 CLOSE c_list_header_id;
2392
2393 -------------------- finish --------------------------
2394 FND_MSG_PUB.count_and_get(
2395 p_encoded => FND_API.g_false,
2396 p_count => x_msg_count,
2397 p_data => x_msg_data
2398 );
2399
2400 IF (AMS_DEBUG_HIGH_ON) THEN
2401
2402
2403
2404 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2405
2406 END IF;
2407
2408 EXCEPTION
2409
2410 WHEN AMS_Utility_PVT.resource_locked THEN
2411 x_return_status := FND_API.g_ret_sts_error;
2412 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2413 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
2414 FND_MSG_PUB.add;
2415 END IF;
2416
2417 FND_MSG_PUB.count_and_get(
2418 p_encoded => FND_API.g_false,
2419 p_count => x_msg_count,
2420 p_data => x_msg_data
2421 );
2422
2423 WHEN FND_API.g_exc_error THEN
2424 x_return_status := FND_API.g_ret_sts_error;
2425 FND_MSG_PUB.count_and_get(
2426 p_encoded => FND_API.g_false,
2427 p_count => x_msg_count,
2428 p_data => x_msg_data
2429 );
2430
2431 WHEN FND_API.g_exc_unexpected_error THEN
2432 x_return_status := FND_API.g_ret_sts_unexp_error ;
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 WHEN OTHERS THEN
2440 x_return_status := FND_API.g_ret_sts_unexp_error ;
2441 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2442 THEN
2443 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2444 END IF;
2445
2446 FND_MSG_PUB.count_and_get(
2447 p_encoded => FND_API.g_false,
2448 p_count => x_msg_count,
2449 p_data => x_msg_data
2450 );
2451
2452 END Lock_listheader;
2453
2454
2455 ---------------------------------------------------------------------
2456 -- PROCEDURE
2457 -- Init_ListHeader_rec
2458 --
2459 -- HISTORY
2460 -- 10/11/99 tdonohoe Created.
2461 ---------------------------------------------------------------------
2462 PROCEDURE Init_ListHeader_Rec(
2463 x_listheader_rec OUT NOCOPY list_header_rec_type
2464 )
2465 IS
2466 BEGIN
2467
2468 x_listheader_rec.LIST_HEADER_ID := FND_API.g_miss_num;
2469 x_listheader_rec.LAST_UPDATE_DATE := FND_API.g_miss_date;
2470 x_listheader_rec.LAST_UPDATED_BY := FND_API.g_miss_num;
2471 x_listheader_rec.CREATION_DATE := FND_API.g_miss_date;
2472 x_listheader_rec.CREATED_BY := FND_API.g_miss_num;
2473 x_listheader_rec.LAST_UPDATE_LOGIN := FND_API.g_miss_num;
2474 x_listheader_rec.OBJECT_VERSION_NUMBER := FND_API.g_miss_num;
2475 x_listheader_rec.REQUEST_ID := FND_API.g_miss_num;
2476 x_listheader_rec.PROGRAM_ID := FND_API.g_miss_num;
2477 x_listheader_rec.PROGRAM_APPLICATION_ID := FND_API.g_miss_num;
2478 x_listheader_rec.PROGRAM_UPDATE_DATE := FND_API.g_miss_date;
2479 x_listheader_rec.VIEW_APPLICATION_ID := FND_API.g_miss_num;
2480 x_listheader_rec.LIST_NAME := FND_API.g_miss_char;
2481 x_listheader_rec.LIST_USED_BY_ID := FND_API.g_miss_num;
2482 x_listheader_rec.ARC_LIST_USED_BY := FND_API.g_miss_char;
2483 x_listheader_rec.LIST_TYPE := FND_API.g_miss_char;
2484 x_listheader_rec.STATUS_CODE := FND_API.g_miss_char;
2485 x_listheader_rec.STATUS_DATE := FND_API.g_miss_date;
2486 x_listheader_rec.GENERATION_TYPE := FND_API.g_miss_char;
2487 x_listheader_rec.ROW_SELECTION_TYPE := FND_API.g_miss_char;
2488 x_listheader_rec.OWNER_USER_ID := FND_API.g_miss_num;
2489 x_listheader_rec.ACCESS_LEVEL := FND_API.g_miss_char;
2490 x_listheader_rec.ENABLE_LOG_FLAG := FND_API.g_miss_char;
2491 x_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG := FND_API.g_miss_char;
2492 x_listheader_rec.ENABLE_PARALLEL_DML_FLAG := FND_API.g_miss_char;
2493 x_listheader_rec.DEDUPE_DURING_GENERATION_FLAG := FND_API.g_miss_char;
2494 x_listheader_rec.GENERATE_CONTROL_GROUP_FLAG := FND_API.g_miss_char;
2495 x_listheader_rec.LAST_GENERATION_SUCCESS_FLAG := FND_API.g_miss_char;
2496 x_listheader_rec.FORECASTED_START_DATE := FND_API.g_miss_date;
2497 x_listheader_rec.FORECASTED_END_DATE := FND_API.g_miss_date;
2498 x_listheader_rec.ACTUAL_END_DATE := FND_API.g_miss_date;
2499 x_listheader_rec.SENT_OUT_DATE := FND_API.g_miss_date;
2500 x_listheader_rec.DEDUPE_START_DATE := FND_API.g_miss_date;
2501 x_listheader_rec.LAST_DEDUPE_DATE := FND_API.g_miss_date;
2502 x_listheader_rec.LAST_DEDUPED_BY_USER_ID := FND_API.g_miss_num;
2503 x_listheader_rec.WORKFLOW_ITEM_KEY := FND_API.g_miss_num;
2504 x_listheader_rec.NO_OF_ROWS_DUPLICATES := FND_API.g_miss_num;
2505 x_listheader_rec.NO_OF_ROWS_MIN_REQUESTED := FND_API.g_miss_num;
2506 x_listheader_rec.NO_OF_ROWS_MAX_REQUESTED := FND_API.g_miss_num;
2507 x_listheader_rec.NO_OF_ROWS_IN_LIST := FND_API.g_miss_num;
2508 x_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP := FND_API.g_miss_num;
2509 x_listheader_rec.NO_OF_ROWS_ACTIVE := FND_API.g_miss_num;
2510 x_listheader_rec.NO_OF_ROWS_INACTIVE := FND_API.g_miss_num;
2511 x_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED := FND_API.g_miss_num;
2512 x_listheader_rec.NO_OF_ROWS_DO_NOT_CALL := FND_API.g_miss_num;
2516 x_listheader_rec.TIMEZONE_ID := FND_API.g_miss_num;
2513 x_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL := FND_API.g_miss_num;
2514 x_listheader_rec.NO_OF_ROWS_RANDOM := FND_API.g_miss_num;
2515 x_listheader_rec.ORG_ID := FND_API.g_miss_num;
2517 x_listheader_rec.USER_ENTERED_START_TIME := FND_API.g_miss_date;
2518 x_listheader_rec.MAIN_GEN_START_TIME := FND_API.g_miss_date;
2519 x_listheader_rec.MAIN_GEN_END_TIME := FND_API.g_miss_date;
2520 x_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION := FND_API.g_miss_num;
2521 x_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION := FND_API.g_miss_num;
2522 x_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION := FND_API.g_miss_num;
2523 x_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION := FND_API.g_miss_num;
2524 x_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID := FND_API.g_miss_char;
2525 x_listheader_rec.RESULT_TEXT := FND_API.g_miss_char;
2526 x_listheader_rec.KEYWORDS := FND_API.g_miss_char;
2527 x_listheader_rec.DESCRIPTION := FND_API.g_miss_char;
2528 x_listheader_rec.LIST_PRIORITY := FND_API.g_miss_num;
2529 x_listheader_rec.ASSIGN_PERSON_ID := FND_API.g_miss_num;
2530 x_listheader_rec.LIST_SOURCE := FND_API.g_miss_char;
2531 x_listheader_rec.LIST_SOURCE_TYPE := FND_API.g_miss_char;
2532 x_listheader_rec.LIST_ONLINE_FLAG := FND_API.g_miss_char;
2533 x_listheader_rec.RANDOM_LIST_ID := FND_API.g_miss_num;
2534 x_listheader_rec.ENABLED_FLAG := FND_API.g_miss_char;
2535 x_listheader_rec.ASSIGNED_TO := FND_API.g_miss_num;
2536 x_listheader_rec.QUERY_ID := FND_API.g_miss_num;
2537 x_listheader_rec.OWNER_PERSON_ID := FND_API.g_miss_num;
2538 x_listheader_rec.ATTRIBUTE_CATEGORY := FND_API.g_miss_char;
2539 x_listheader_rec.ATTRIBUTE1 := FND_API.g_miss_char;
2540 x_listheader_rec.ATTRIBUTE2 := FND_API.g_miss_char;
2541 x_listheader_rec.ATTRIBUTE3 := FND_API.g_miss_char;
2542 x_listheader_rec.ATTRIBUTE4 := FND_API.g_miss_char;
2543 x_listheader_rec.ATTRIBUTE5 := FND_API.g_miss_char;
2544 x_listheader_rec.ATTRIBUTE6 := FND_API.g_miss_char;
2545 x_listheader_rec.ATTRIBUTE7 := FND_API.g_miss_char;
2546 x_listheader_rec.ATTRIBUTE8 := FND_API.g_miss_char;
2547 x_listheader_rec.ATTRIBUTE9 := FND_API.g_miss_char;
2548 x_listheader_rec.ATTRIBUTE10 := FND_API.g_miss_char;
2549 x_listheader_rec.ATTRIBUTE11 := FND_API.g_miss_char;
2550 x_listheader_rec.ATTRIBUTE12 := FND_API.g_miss_char;
2551 x_listheader_rec.ATTRIBUTE13 := FND_API.g_miss_char;
2552 x_listheader_rec.ATTRIBUTE14 := FND_API.g_miss_char;
2553 x_listheader_rec.ATTRIBUTE15 := FND_API.g_miss_char;
2554 x_listheader_rec.QUANTUM := FND_API.g_miss_num;
2555 x_listheader_rec.RELEASE_CONTROL_ALG_ID :=FND_API.g_miss_num;
2556 x_listheader_rec.DIALING_METHOD := FND_API.g_miss_char;
2557 x_listheader_rec.CALLING_CALENDAR_ID :=FND_API.g_miss_num;
2558 x_listheader_rec.release_strategy := FND_API.g_miss_char;
2559 x_listheader_rec.custom_setup_id :=FND_API.g_miss_num;
2560 x_listheader_rec.country :=FND_API.g_miss_num;
2561 x_listheader_rec.PURGE_FLAG := FND_API.g_miss_char ;
2562 x_listheader_rec.PUBLIC_FLAG := FND_API.g_miss_char ;
2563 x_listheader_rec.LIST_CATEGORY := FND_API.g_miss_char ;
2564 x_listheader_rec.QUOTA := FND_API.g_miss_num ;
2565 x_listheader_rec.QUOTA_RESET := FND_API.g_miss_num ;
2566 x_listheader_rec.RECYCLING_ALG_ID := FND_API.g_miss_num ;
2567 x_listheader_rec.CALLBACK_PRIORITY_FLAG := FND_API.g_miss_char;
2568 x_listheader_rec.CALL_CENTER_READY_FLAG := FND_API.g_miss_char;
2569 x_listheader_rec.USER_STATUS_ID := FND_API.g_miss_num;
2570 x_listheader_rec.NO_OF_ROWS_prev_contacted := FND_API.g_miss_num;
2571 x_listheader_rec.APPLY_TRAFFIC_COP := FND_API.g_miss_char;
2572
2573 -- ckapoor R12 copy target group enhancement
2574
2575 x_listheader_rec.CTRL_CONF_LEVEL := FND_API.g_miss_num ;
2576 x_listheader_rec.CTRL_REQ_RESP_RATE := FND_API.g_miss_num ;
2577 x_listheader_rec.CTRL_LIMIT_OF_ERROR := FND_API.g_miss_num ;
2578 x_listheader_rec.STATUS_CODE_OLD := FND_API.g_miss_char;
2579 x_listheader_rec.CTRL_CONC_JOB_ID := FND_API.g_miss_num;
2580 x_listheader_rec.CTRL_STATUS_CODE := FND_API.g_miss_char;
2581 x_listheader_rec.CTRL_GEN_MODE := FND_API.g_miss_char;
2582 x_listheader_rec.APPLY_SUPPRESSION_FLAG := FND_API.g_miss_char;
2583
2584 -- end ckapoor
2585
2586 END Init_ListHeader_rec;
2587
2588
2589 ---------------------------------------------------------------------
2590 -- PROCEDURE
2591 -- Complete_ListHeader_rec
2592 --
2593 -- HISTORY
2594 -- 10/11/99 tdonohoe Created.
2595 ---------------------------------------------------------------------
2596 PROCEDURE Complete_ListHeader_rec(
2597 p_listheader_rec IN list_header_rec_type,
2598 x_complete_rec OUT NOCOPY list_header_rec_type
2599 )
2600 IS
2601
2602 CURSOR c_listheader IS
2603 SELECT *
2604 FROM ams_list_headers_vl
2605 WHERE list_header_id = p_listheader_rec.list_header_id;
2606
2607 l_listheader_rec c_listheader%ROWTYPE;
2608
2609 BEGIN
2610
2611 x_complete_rec := p_listheader_rec;
2612 OPEN c_listheader;
2613 FETCH c_listheader INTO l_listheader_rec;
2614 IF c_listheader%NOTFOUND THEN
2615 CLOSE c_listheader;
2616 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2617 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2618 FND_MSG_PUB.add;
2619 END IF;
2623
2620 RAISE FND_API.g_exc_error;
2621 END IF;
2622 CLOSE c_listheader;
2624
2625 IF p_listheader_rec.LIST_HEADER_ID = FND_API.g_miss_num THEN
2626 x_complete_rec.LIST_HEADER_ID := l_listheader_rec.LIST_HEADER_ID;
2627 END IF;
2628 IF p_listheader_rec.LAST_UPDATE_DATE = FND_API.g_miss_date THEN
2629 x_complete_rec.LAST_UPDATE_DATE := l_listheader_rec.LAST_UPDATE_DATE;
2630 END IF;
2631 IF p_listheader_rec.LAST_UPDATED_BY = FND_API.g_miss_num THEN
2632 x_complete_rec.LAST_UPDATED_BY := l_listheader_rec.LAST_UPDATED_BY;
2633 END IF;
2634 IF p_listheader_rec.CREATION_DATE = FND_API.g_miss_date THEN
2635 x_complete_rec.CREATION_DATE := l_listheader_rec.CREATION_DATE;
2636 END IF;
2637 IF p_listheader_rec.CREATED_BY = FND_API.g_miss_num THEN
2638 x_complete_rec.CREATED_BY := l_listheader_rec.CREATED_BY;
2639 END IF;
2640 IF p_listheader_rec.LAST_UPDATE_LOGIN = FND_API.g_miss_num THEN
2641 x_complete_rec.LAST_UPDATE_LOGIN := l_listheader_rec.LAST_UPDATE_LOGIN;
2642 END IF;
2643 IF p_listheader_rec.OBJECT_VERSION_NUMBER = FND_API.g_miss_num THEN
2644 x_complete_rec.OBJECT_VERSION_NUMBER := l_listheader_rec.OBJECT_VERSION_NUMBER ;
2645 END IF;
2646 IF p_listheader_rec.REQUEST_ID = FND_API.g_miss_num THEN
2647 x_complete_rec.REQUEST_ID := l_listheader_rec.REQUEST_ID;
2648 END IF;
2649 IF p_listheader_rec.PROGRAM_ID = FND_API.g_miss_num THEN
2650 x_complete_rec.PROGRAM_ID := l_listheader_rec.PROGRAM_ID ;
2651 END IF;
2652 IF p_listheader_rec.PROGRAM_APPLICATION_ID = FND_API.g_miss_num THEN
2653 x_complete_rec.PROGRAM_APPLICATION_ID := l_listheader_rec.PROGRAM_APPLICATION_ID;
2654 END IF;
2655 IF p_listheader_rec.PROGRAM_UPDATE_DATE = FND_API.g_miss_date THEN
2656 x_complete_rec.PROGRAM_UPDATE_DATE := l_listheader_rec.PROGRAM_UPDATE_DATE;
2657 END IF;
2658 IF p_listheader_rec.VIEW_APPLICATION_ID = FND_API.g_miss_num THEN
2659 x_complete_rec.VIEW_APPLICATION_ID := l_listheader_rec.VIEW_APPLICATION_ID;
2660 END IF;
2661
2662 IF p_listheader_rec.LIST_NAME = FND_API.g_miss_char THEN
2663 x_complete_rec.LIST_NAME := l_listheader_rec.LIST_NAME;
2664 END IF;
2665 IF p_listheader_rec.LIST_USED_BY_ID = FND_API.g_miss_num THEN
2666 x_complete_rec.LIST_USED_BY_ID := l_listheader_rec.LIST_USED_BY_ID ;
2667 END IF;
2668 IF p_listheader_rec.ARC_LIST_USED_BY = FND_API.g_miss_char THEN
2669 x_complete_rec.ARC_LIST_USED_BY := l_listheader_rec.ARC_LIST_USED_BY;
2670 END IF;
2671 IF p_listheader_rec.LIST_TYPE = FND_API.g_miss_char THEN
2672 x_complete_rec.LIST_TYPE := l_listheader_rec.LIST_TYPE;
2673 END IF;
2674
2675 IF p_listheader_rec.STATUS_DATE = FND_API.g_miss_date THEN
2676 x_complete_rec.STATUS_DATE := l_listheader_rec.STATUS_DATE ;
2677 END IF;
2678 IF p_listheader_rec.GENERATION_TYPE = FND_API.g_miss_char THEN
2679 x_complete_rec.GENERATION_TYPE := l_listheader_rec.GENERATION_TYPE;
2680 END IF;
2681 IF p_listheader_rec.ROW_SELECTION_TYPE = FND_API.g_miss_char THEN
2682 x_complete_rec.ROW_SELECTION_TYPE := l_listheader_rec.ROW_SELECTION_TYPE;
2683 END IF;
2684 IF p_listheader_rec.OWNER_USER_ID = FND_API.g_miss_num THEN
2685 x_complete_rec.OWNER_USER_ID := l_listheader_rec.OWNER_USER_ID;
2686 END IF;
2687 IF p_listheader_rec.ACCESS_LEVEL = FND_API.g_miss_char THEN
2688 x_complete_rec.ACCESS_LEVEL := l_listheader_rec.ACCESS_LEVEL;
2689 END IF;
2690 IF p_listheader_rec.ENABLE_LOG_FLAG = FND_API.g_miss_char THEN
2691 x_complete_rec.ENABLE_LOG_FLAG := l_listheader_rec.ENABLE_LOG_FLAG;
2692 END IF;
2693 IF p_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG = FND_API.g_miss_char THEN
2694 x_complete_rec.ENABLE_WORD_REPLACEMENT_FLAG := l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG ;
2695 END IF;
2696 IF p_listheader_rec.ENABLE_PARALLEL_DML_FLAG = FND_API.g_miss_char THEN
2697 x_complete_rec.ENABLE_PARALLEL_DML_FLAG := l_listheader_rec.ENABLE_PARALLEL_DML_FLAG;
2698 END IF;
2699 IF p_listheader_rec.DEDUPE_DURING_GENERATION_FLAG = FND_API.g_miss_char THEN
2700 x_complete_rec.DEDUPE_DURING_GENERATION_FLAG := l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG;
2701 END IF;
2702 IF p_listheader_rec.GENERATE_CONTROL_GROUP_FLAG = FND_API.g_miss_char THEN
2703 x_complete_rec.GENERATE_CONTROL_GROUP_FLAG := l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG ;
2704 END IF;
2705 IF p_listheader_rec.LAST_GENERATION_SUCCESS_FLAG = FND_API.g_miss_char THEN
2706 x_complete_rec.LAST_GENERATION_SUCCESS_FLAG := l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG;
2707 END IF;
2708 IF p_listheader_rec.FORECASTED_START_DATE = FND_API.g_miss_date THEN
2709 x_complete_rec.FORECASTED_START_DATE := l_listheader_rec.FORECASTED_START_DATE ;
2710 END IF;
2711 IF p_listheader_rec.FORECASTED_END_DATE = FND_API.g_miss_date THEN
2712 x_complete_rec.FORECASTED_END_DATE := l_listheader_rec.FORECASTED_END_DATE;
2713 END IF;
2714 IF p_listheader_rec.ACTUAL_END_DATE = FND_API.g_miss_date THEN
2715 x_complete_rec.ACTUAL_END_DATE := l_listheader_rec.ACTUAL_END_DATE;
2716 END IF;
2717 IF p_listheader_rec.SENT_OUT_DATE = FND_API.g_miss_date THEN
2718 x_complete_rec.SENT_OUT_DATE := l_listheader_rec.SENT_OUT_DATE;
2719 END IF;
2720 IF p_listheader_rec.DEDUPE_START_DATE = FND_API.g_miss_date THEN
2721 x_complete_rec.DEDUPE_START_DATE := l_listheader_rec.DEDUPE_START_DATE ;
2722 END IF;
2723 IF p_listheader_rec.LAST_DEDUPE_DATE = FND_API.g_miss_date THEN
2724 x_complete_rec.LAST_DEDUPE_DATE := l_listheader_rec.LAST_DEDUPE_DATE;
2725 END IF;
2729 IF p_listheader_rec.WORKFLOW_ITEM_KEY = FND_API.g_miss_num THEN
2726 IF p_listheader_rec.LAST_DEDUPED_BY_USER_ID = FND_API.g_miss_num THEN
2727 x_complete_rec.LAST_DEDUPED_BY_USER_ID := l_listheader_rec.LAST_DEDUPED_BY_USER_ID ;
2728 END IF;
2730 x_complete_rec.WORKFLOW_ITEM_KEY := l_listheader_rec.WORKFLOW_ITEM_KEY ;
2731 END IF;
2732 IF p_listheader_rec.NO_OF_ROWS_DUPLICATES = FND_API.g_miss_num THEN
2733 x_complete_rec.NO_OF_ROWS_DUPLICATES := l_listheader_rec.NO_OF_ROWS_DUPLICATES;
2734 END IF;
2735 IF p_listheader_rec.NO_OF_ROWS_MIN_REQUESTED = FND_API.g_miss_num THEN
2736 x_complete_rec.NO_OF_ROWS_MIN_REQUESTED := l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED;
2737 END IF;
2738 IF p_listheader_rec.NO_OF_ROWS_MAX_REQUESTED = FND_API.g_miss_num THEN
2739 x_complete_rec.NO_OF_ROWS_MAX_REQUESTED := l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED ;
2740 END IF;
2741 IF p_listheader_rec.NO_OF_ROWS_IN_LIST = FND_API.g_miss_num THEN
2742 x_complete_rec.NO_OF_ROWS_IN_LIST := l_listheader_rec.NO_OF_ROWS_IN_LIST;
2743 END IF;
2744 IF p_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP = FND_API.g_miss_num THEN
2745 x_complete_rec.NO_OF_ROWS_IN_CTRL_GROUP := l_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP;
2746 END IF;
2747 IF p_listheader_rec.NO_OF_ROWS_ACTIVE = FND_API.g_miss_num THEN
2748 x_complete_rec.NO_OF_ROWS_ACTIVE := l_listheader_rec.NO_OF_ROWS_ACTIVE;
2749 END IF;
2753 IF p_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED = FND_API.g_miss_num THEN
2750 IF p_listheader_rec.NO_OF_ROWS_INACTIVE = FND_API.g_miss_num THEN
2751 x_complete_rec.NO_OF_ROWS_INACTIVE := l_listheader_rec.NO_OF_ROWS_INACTIVE;
2752 END IF;
2754 x_complete_rec.NO_OF_ROWS_MANUALLY_ENTERED := l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED;
2755 END IF;
2756 IF p_listheader_rec.NO_OF_ROWS_DO_NOT_CALL = FND_API.g_miss_num THEN
2757 x_complete_rec.NO_OF_ROWS_DO_NOT_CALL := l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL ;
2758 END IF;
2759 IF p_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL = FND_API.g_miss_num THEN
2760 x_complete_rec.NO_OF_ROWS_DO_NOT_MAIL := l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL;
2761 END IF;
2762 IF p_listheader_rec.NO_OF_ROWS_RANDOM = FND_API.g_miss_num THEN
2763 x_complete_rec.NO_OF_ROWS_RANDOM := l_listheader_rec.NO_OF_ROWS_RANDOM;
2764 END IF;
2765 IF p_listheader_rec.ORG_ID = FND_API.g_miss_num THEN
2766 x_complete_rec.ORG_ID := l_listheader_rec.ORG_ID;
2767 END IF;
2768 IF p_listheader_rec.TIMEZONE_ID = FND_API.g_miss_num THEN
2769 x_complete_rec.TIMEZONE_ID := l_listheader_rec.TIMEZONE_ID;
2770 END IF;
2771 IF p_listheader_rec.USER_ENTERED_START_TIME = FND_API.g_miss_date THEN
2772 x_complete_rec.USER_ENTERED_START_TIME := l_listheader_rec.USER_ENTERED_START_TIME ;
2773 END IF;
2774 IF p_listheader_rec.MAIN_GEN_START_TIME = FND_API.g_miss_date THEN
2775 x_complete_rec.MAIN_GEN_START_TIME := l_listheader_rec.MAIN_GEN_START_TIME ;
2776 END IF;
2777 IF p_listheader_rec.MAIN_GEN_END_TIME = FND_API.g_miss_date THEN
2778 x_complete_rec.MAIN_GEN_END_TIME := l_listheader_rec.MAIN_GEN_END_TIME ;
2779 END IF;
2780 IF p_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION = FND_API.g_miss_num THEN
2781 x_complete_rec.MAIN_RANDOM_NTH_ROW_SELECTION := l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION;
2782 END IF;
2783 IF p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION = FND_API.g_miss_num THEN
2784 x_complete_rec.MAIN_RANDOM_PCT_ROW_SELECTION := l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION ;
2785 END IF;
2786 IF p_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION = FND_API.g_miss_num THEN
2787 x_complete_rec.CTRL_RANDOM_NTH_ROW_SELECTION := l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION ;
2788 END IF;
2789 IF p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION = FND_API.g_miss_num THEN
2790 x_complete_rec.CTRL_RANDOM_PCT_ROW_SELECTION := l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION ;
2791 END IF;
2792 IF p_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID = FND_API.g_miss_char THEN
2793 x_complete_rec.REPEAT_SOURCE_LIST_HEADER_ID := l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID;
2794 END IF;
2795 IF p_listheader_rec.RESULT_TEXT = FND_API.g_miss_char THEN
2796 x_complete_rec.RESULT_TEXT := l_listheader_rec.RESULT_TEXT;
2797 END IF;
2798 IF p_listheader_rec.KEYWORDS = FND_API.g_miss_char THEN
2799 x_complete_rec.KEYWORDS := l_listheader_rec.KEYWORDS;
2800 END IF;
2801 IF p_listheader_rec.DESCRIPTION = FND_API.g_miss_char THEN
2802 x_complete_rec.DESCRIPTION := l_listheader_rec.DESCRIPTION ;
2803 END IF;
2804 IF p_listheader_rec.LIST_PRIORITY = FND_API.g_miss_num THEN
2805 x_complete_rec.LIST_PRIORITY := l_listheader_rec.LIST_PRIORITY;
2806 END IF;
2807 IF p_listheader_rec.ASSIGN_PERSON_ID = FND_API.g_miss_num THEN
2808 x_complete_rec.ASSIGN_PERSON_ID := l_listheader_rec.ASSIGN_PERSON_ID;
2809 END IF;
2810 IF p_listheader_rec.LIST_SOURCE = FND_API.g_miss_char THEN
2811 x_complete_rec.LIST_SOURCE := l_listheader_rec.LIST_SOURCE ;
2812 END IF;
2813 IF p_listheader_rec.LIST_SOURCE_TYPE = FND_API.g_miss_char THEN
2814 x_complete_rec.LIST_SOURCE_TYPE := l_listheader_rec.LIST_SOURCE_TYPE;
2815 END IF;
2816 IF p_listheader_rec.LIST_ONLINE_FLAG = FND_API.g_miss_char THEN
2817 x_complete_rec.LIST_ONLINE_FLAG := l_listheader_rec.LIST_ONLINE_FLAG;
2818 END IF;
2819 IF p_listheader_rec.RANDOM_LIST_ID = FND_API.g_miss_num THEN
2820 x_complete_rec.RANDOM_LIST_ID := l_listheader_rec.RANDOM_LIST_ID ;
2821 END IF;
2822 IF p_listheader_rec.ENABLED_FLAG = FND_API.g_miss_char THEN
2823 x_complete_rec.ENABLED_FLAG := l_listheader_rec.ENABLED_FLAG;
2824 END IF;
2825 IF p_listheader_rec.ASSIGNED_TO = FND_API.g_miss_num THEN
2826 x_complete_rec.ASSIGNED_TO := l_listheader_rec.ASSIGNED_TO ;
2827 END IF;
2828 IF p_listheader_rec.QUERY_ID = FND_API.g_miss_num THEN
2829 x_complete_rec.QUERY_ID := l_listheader_rec.QUERY_ID ;
2830 END IF;
2831 IF p_listheader_rec.OWNER_PERSON_ID = FND_API.g_miss_num THEN
2832 x_complete_rec.OWNER_PERSON_ID := l_listheader_rec.OWNER_PERSON_ID;
2833 END IF;
2834 IF p_listheader_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char THEN
2835 x_complete_rec.ATTRIBUTE_CATEGORY := l_listheader_rec.ATTRIBUTE_CATEGORY;
2836 END IF;
2837 IF p_listheader_rec.ATTRIBUTE1 = FND_API.g_miss_char THEN
2838 x_complete_rec.ATTRIBUTE1 := l_listheader_rec.ATTRIBUTE1 ;
2839 END IF;
2840 IF p_listheader_rec.ATTRIBUTE2 = FND_API.g_miss_char THEN
2844 x_complete_rec.ATTRIBUTE3 := l_listheader_rec.ATTRIBUTE3;
2841 x_complete_rec.ATTRIBUTE2 := l_listheader_rec.ATTRIBUTE2 ;
2842 END IF;
2843 IF p_listheader_rec.ATTRIBUTE3 = FND_API.g_miss_char THEN
2845 END IF;
2846 IF p_listheader_rec.ATTRIBUTE4 = FND_API.g_miss_char THEN
2847 x_complete_rec.ATTRIBUTE4 := l_listheader_rec.ATTRIBUTE4;
2848 END IF;
2849 IF p_listheader_rec.ATTRIBUTE5 = FND_API.g_miss_char THEN
2850 x_complete_rec.ATTRIBUTE5 := l_listheader_rec.ATTRIBUTE5 ;
2851 END IF;
2852 IF p_listheader_rec.ATTRIBUTE6 = FND_API.g_miss_char THEN
2853 x_complete_rec.ATTRIBUTE6 := l_listheader_rec.ATTRIBUTE6;
2854 END IF;
2855 IF p_listheader_rec.ATTRIBUTE7 = FND_API.g_miss_char THEN
2856 x_complete_rec.ATTRIBUTE7 := l_listheader_rec.ATTRIBUTE7 ;
2857 END IF;
2858 IF p_listheader_rec.ATTRIBUTE8 = FND_API.g_miss_char THEN
2859 x_complete_rec.ATTRIBUTE8 := l_listheader_rec.ATTRIBUTE8;
2860 END IF;
2861 IF p_listheader_rec.ATTRIBUTE9 = FND_API.g_miss_char THEN
2862 x_complete_rec.ATTRIBUTE9 := l_listheader_rec.ATTRIBUTE9;
2863 END IF;
2864 IF p_listheader_rec.ATTRIBUTE10 = FND_API.g_miss_char THEN
2865 x_complete_rec.ATTRIBUTE10 := l_listheader_rec.ATTRIBUTE10;
2866 END IF;
2867 IF p_listheader_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
2868 x_complete_rec.ATTRIBUTE11 := l_listheader_rec.ATTRIBUTE11 ;
2869 END IF;
2870 IF p_listheader_rec.ATTRIBUTE12 = FND_API.g_miss_char THEN
2871 x_complete_rec.ATTRIBUTE12 := l_listheader_rec.ATTRIBUTE12;
2872 END IF;
2873 IF p_listheader_rec.ATTRIBUTE13 = FND_API.g_miss_char THEN
2874 x_complete_rec.ATTRIBUTE13 := l_listheader_rec.ATTRIBUTE13;
2875 END IF;
2876 IF p_listheader_rec.ATTRIBUTE14 = FND_API.g_miss_char THEN
2877 x_complete_rec.ATTRIBUTE14 := l_listheader_rec.ATTRIBUTE14;
2878 END IF;
2879 IF p_listheader_rec.ATTRIBUTE15 = FND_API.g_miss_char THEN
2880 x_complete_rec.ATTRIBUTE15 := l_listheader_rec.ATTRIBUTE15;
2881 END IF;
2882 IF p_listheader_rec.QUANTUM = FND_API.g_miss_num THEN
2883 x_complete_rec.QUANTUM := l_listheader_rec.QUANTUM ;
2884 END IF;
2885 IF p_listheader_rec.RELEASE_CONTROL_ALG_ID =FND_API.g_miss_num THEN
2886 x_complete_rec.RELEASE_CONTROL_ALG_ID
2887 := l_listheader_rec.RELEASE_CONTROL_ALG_ID ;
2888 END IF;
2889 IF p_listheader_rec.DIALING_METHOD = FND_API.g_miss_char THEN
2890 x_complete_rec.DIALING_METHOD := l_listheader_rec.DIALING_METHOD ;
2891 END IF;
2892 IF p_listheader_rec.RELEASE_CONTROL_ALG_ID =FND_API.g_miss_num THEN
2893 x_complete_rec.RELEASE_CONTROL_ALG_ID := l_listheader_rec.RELEASE_CONTROL_ALG_ID ;
2894 END IF;
2895 IF p_listheader_rec.CALLING_CALENDAR_ID =FND_API.g_miss_num THEN
2896 x_complete_rec.CALLING_CALENDAR_ID := l_listheader_rec.CALLING_CALENDAR_ID ;
2897 END IF;
2898 IF p_listheader_rec.release_strategy = FND_API.g_miss_char THEN
2899 x_complete_rec.release_strategy := l_listheader_rec.release_strategy ;
2900 END IF;
2901 IF p_listheader_rec.custom_setup_id =FND_API.g_miss_num THEN
2902 x_complete_rec.custom_setup_id := l_listheader_rec.custom_setup_id ;
2903 END IF;
2904 IF p_listheader_rec.country =FND_API.g_miss_num THEN
2905 x_complete_rec.country := l_listheader_rec.country ;
2906 END IF;
2907 IF p_listheader_rec.PURGE_FLAG =FND_API.g_miss_char THEN
2908 x_complete_rec.purge_flag := l_listheader_rec.purge_flag ;
2909 END IF;
2910 IF p_listheader_rec.PUBLIC_FLAG =FND_API.g_miss_char THEN
2911 x_complete_rec.PUBLIC_FLAG := l_listheader_rec.PUBLIC_FLAG ;
2912 END IF;
2913 IF p_listheader_rec.LIST_CATEGORY =FND_API.g_miss_char THEN
2914 x_complete_rec.LIST_CATEGORY := l_listheader_rec.LIST_CATEGORY ;
2915 END IF;
2916 IF p_listheader_rec.quota =FND_API.g_miss_num THEN
2917 x_complete_rec.quota := l_listheader_rec.quota ;
2918 END IF;
2919 IF p_listheader_rec.quota_reset =FND_API.g_miss_num THEN
2920 x_complete_rec.quota_reset := l_listheader_rec.quota_reset ;
2921 END IF;
2922 IF p_listheader_rec.recycling_alg_id =FND_API.g_miss_num THEN
2923 x_complete_rec.recycling_alg_id := l_listheader_rec.recycling_alg_id ;
2924 END IF;
2925 IF p_listheader_rec.CALLBACK_PRIORITY_FLAG = FND_API.g_miss_char THEN
2926 x_complete_rec.CALLBACK_PRIORITY_FLAG := l_listheader_rec.CALLBACK_PRIORITY_FLAG ;
2927 END IF;
2928 IF p_listheader_rec.CALL_CENTER_READY_FLAG = FND_API.g_miss_char THEN
2929 x_complete_rec.CALL_CENTER_READY_FLAG := l_listheader_rec.CALL_CENTER_READY_FLAG ;
2930 END IF;
2931 IF p_listheader_rec.USER_STATUS_ID = FND_API.g_miss_num THEN
2932 x_complete_rec.USER_STATUS_ID := l_listheader_rec.USER_STATUS_ID ;
2933 END IF;
2934
2935 IF p_listheader_rec.NO_OF_ROWS_prev_contacted = FND_API.g_miss_num THEN
2936 x_complete_rec.NO_OF_ROWS_prev_contacted := l_listheader_rec.NO_OF_ROWS_prev_contacted;
2937 END IF;
2938
2939 IF p_listheader_rec.APPLY_TRAFFIC_COP = FND_API.g_miss_char THEN
2940 x_complete_rec.APPLY_TRAFFIC_COP := l_listheader_rec.APPLY_TRAFFIC_COP ;
2941 END IF;
2942
2943 x_complete_rec.status_code := AMS_Utility_PVT.get_system_status_code(
2944 x_complete_rec.user_status_id );
2945
2946
2947 -- ckapoor R12 copy target group changes
2948
2949 IF p_listheader_rec.CTRL_CONF_LEVEL =FND_API.g_miss_num THEN
2950 x_complete_rec.CTRL_CONF_LEVEL := l_listheader_rec.CTRL_CONF_LEVEL ;
2951 END IF;
2952 IF p_listheader_rec.CTRL_REQ_RESP_RATE =FND_API.g_miss_num THEN
2953 x_complete_rec.CTRL_REQ_RESP_RATE := l_listheader_rec.CTRL_REQ_RESP_RATE ;
2954 END IF;
2958 IF p_listheader_rec.STATUS_CODE_OLD = FND_API.g_miss_char THEN
2955 IF p_listheader_rec.CTRL_LIMIT_OF_ERROR =FND_API.g_miss_num THEN
2956 x_complete_rec.CTRL_LIMIT_OF_ERROR := l_listheader_rec.CTRL_LIMIT_OF_ERROR ;
2957 END IF;
2959 x_complete_rec.STATUS_CODE_OLD := l_listheader_rec.STATUS_CODE_OLD ;
2960 END IF;
2961 IF p_listheader_rec.CTRL_CONC_JOB_ID = FND_API.g_miss_num THEN
2962 x_complete_rec.CTRL_CONC_JOB_ID := l_listheader_rec.CTRL_CONC_JOB_ID ;
2963 END IF;
2964 IF p_listheader_rec.CTRL_STATUS_CODE = FND_API.g_miss_char THEN
2965 x_complete_rec.CTRL_STATUS_CODE := l_listheader_rec.CTRL_STATUS_CODE ;
2966 END IF;
2967
2968 IF p_listheader_rec.CTRL_GEN_MODE = FND_API.g_miss_char THEN
2969 x_complete_rec.CTRL_GEN_MODE := l_listheader_rec.CTRL_GEN_MODE;
2970 END IF;
2971
2972 IF p_listheader_rec.APPLY_SUPPRESSION_FLAG = FND_API.g_miss_char THEN
2973 x_complete_rec.APPLY_SUPPRESSION_FLAG := l_listheader_rec.APPLY_SUPPRESSION_FLAG ;
2974 END IF;
2975
2976
2977 -- end ckapoor R12 copy target group changes
2978
2979
2980 -- dbms_output.put_line('end ofcomplete list header rec');
2981 END Complete_ListHeader_rec;
2982
2983 PROCEDURE Update_list_header_count(
2984 p_list_header_id IN number,
2985 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2986 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2987 x_return_status OUT NOCOPY VARCHAR2,
2988 x_msg_count OUT NOCOPY NUMBER,
2989 x_msg_data OUT NOCOPY VARCHAR2
2990 )
2991 IS
2992 cursor c_count_list_entries(cur_p_list_header_id number) is
2993 select sum(decode(enabled_flag,'N',0,1)),
2994 sum(decode(enabled_flag,'Y',0,1)),
2995 sum(1),
2996 sum(decode(part_of_control_group_flag,'Y',1,0)),
2997 sum(decode(marked_as_random_flag,'Y',1,0)),
2998 sum(decode(marked_as_duplicate_flag,'Y',1,0)),
2999 sum(decode(manually_entered_flag,
3000 'Y',decode(enabled_flag,'Y','1',0),
3001 0))
3002 from ams_list_entries
3003 where list_header_id = cur_p_list_header_id ;
3004
3005 l_no_of_rows_duplicates number;
3006 l_no_of_rows_in_list number;
3007 l_no_of_rows_active number;
3008 l_no_of_rows_inactive number;
3009 l_no_of_rows_manually_entered number;
3010 l_no_of_rows_in_ctrl_group number;
3011 l_no_of_rows_random number;
3012 l_min_rows number;
3013 l_new_status varchar2(30);
3014 l_new_status_id number;
3015 --l_no_of_rows_prev_contacted number;
3016 BEGIN
3017 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3018 FND_MSG_PUB.initialize;
3019 END IF;
3020
3021 open c_count_list_entries(p_list_header_id);
3022 fetch c_count_list_entries
3023 into l_no_of_rows_active ,
3024 l_no_of_rows_inactive ,
3025 l_no_of_rows_in_list ,
3026 l_no_of_rows_in_ctrl_group ,
3027 l_no_of_rows_random ,
3028 l_no_of_rows_duplicates ,
3029 l_no_of_rows_manually_entered ;
3030 close c_count_list_entries;
3031
3032
3033 SELECT nvl(no_of_rows_min_requested,0)
3034 INTO l_min_rows
3035 FROM ams_list_headers_all
3036 WHERE list_header_id = p_list_header_id;
3037
3038 if l_min_rows > l_no_of_rows_active then
3039 l_new_status := 'DRAFT';
3040 l_new_status_id := 300;
3041 else
3042 l_new_status := 'AVAILABLE';
3043 l_new_status_id := 303;
3044 end if;
3045 update ams_list_headers_all
3046 set no_of_rows_in_list = l_no_of_rows_in_list,
3047 no_of_rows_active = l_no_of_rows_active,
3048 no_of_rows_inactive = l_no_of_rows_inactive,
3049 no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
3050 no_of_rows_random = l_no_of_rows_random,
3051 no_of_rows_duplicates = l_no_of_rows_duplicates,
3052 no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
3053 last_generation_success_flag = decode(l_new_status_id,303,'Y','N'),
3054 status_code = l_new_status,
3055 user_status_id = l_new_status_id,
3056 status_date = sysdate,
3057 last_update_date = sysdate
3058 WHERE list_header_id = p_list_header_id;
3062 END IF;
3059
3060 IF FND_API.To_Boolean ( p_commit ) THEN
3061 COMMIT WORK;
3063
3064
3065 IF (AMS_DEBUG_HIGH_ON) THEN
3066 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3067 FND_MESSAGE.Set_Token('ROW', 'AMS_LIST_HEADER_UPDATE: END');
3068 FND_MSG_PUB.Add;
3069 END IF;
3070
3071
3072 IF (AMS_DEBUG_HIGH_ON) THEN
3073 AMS_Utility_PVT.debug_message('AMS_LIST_HEADER_UPDATE: END');
3074 END IF;
3075
3076
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
3083 EXCEPTION
3084 WHEN FND_API.G_EXC_ERROR THEN
3085 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3086 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3087 FND_MSG_PUB.Add;
3088 -- Check if reset of the status is required
3089 x_return_status := FND_API.G_RET_STS_ERROR ;
3090 FND_MSG_PUB.count_and_get(
3091 p_encoded => FND_API.g_false,
3092 p_count => x_msg_count,
3093 p_data => x_msg_data);
3094
3095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3096 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3097 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3098 FND_MSG_PUB.Add;
3099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3100 FND_MSG_PUB.count_and_get(
3101 p_encoded => FND_API.g_false,
3102 p_count => x_msg_count,
3103 p_data => x_msg_data);
3104
3105 WHEN OTHERS THEN
3106 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3107 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3108 FND_MSG_PUB.Add;
3109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3110 FND_MSG_PUB.count_and_get(
3111 p_encoded => FND_API.g_false,
3112 p_count => x_msg_count,
3113 p_data => x_msg_data);
3114
3115
3116 END;
3117
3118
3119
3120
3121 ---------------------------------------------------------------------
3122 -- PROCEDURE
3123 -- Update_Prev_contacted_count
3124 --
3125 -- PURPOSE
3126 -- aDDED TO SUPPORT TRIGGER BACKPORT FUNCTIONALITY
3127 -- Gived a schedule id / one off event id or event header id and the last contacted date
3128 -- update all list entries of the related target group or invite list ,
3129 -- who were not contacted earlier, with the last contacted date
3130 -- also populate list header with total number of records with last contacted date populated
3131 -- PARAMETERS
3132 -- p_used_by: eg CSCH/EVEO/EVEH
3133 -- p_used_by : Schedule Id/ or One off Event Id or Event Header Id
3134 -- p_last_contacted_date :last contacted date to be populated in to the list entries table
3135 ---------------------------------------------------------------------
3136
3137
3138 PROCEDURE Update_Prev_contacted_count(
3139 p_used_by_id IN number,
3140 p_used_by IN VARCHAR2,
3141 p_last_contacted_date IN DATE,
3142 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3143 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3144 x_return_status OUT NOCOPY VARCHAR2,
3145 x_msg_count OUT NOCOPY NUMBER,
3146 x_msg_data OUT NOCOPY VARCHAR2
3147 )
3148
3149 IS
3150
3151 cursor c_get_list_header_id(cur_p_used_by varchar2,cur_p_used_by_id number) is
3152 select list_header_id
3153 from ams_act_lists
3154 where LIST_USED_BY_ID = cur_p_used_by_id
3155 and LIST_USED_BY = cur_p_used_by
3156 and LIST_ACT_TYPE ='TARGET';
3157
3158
3159
3160 cursor c_count_list_entries(cur_p_list_header_id number) is
3161 select count(LAST_CONTACTED_DATE)
3162 from ams_list_entries
3163 where list_header_id = cur_p_list_header_id
3164 and enabled_flag = 'Y'
3165 and LAST_CONTACTED_DATE is not null;
3166
3167 l_no_of_rows_prev_contacted number;
3168 l_list_header_id number;
3169
3170 BEGIN
3171 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3172 FND_MSG_PUB.initialize;
3173 END IF;
3174
3175
3176 open c_get_list_header_id(p_used_by,p_used_by_id);
3177 fetch c_get_list_header_id
3178 into l_list_header_id ;
3179 close c_get_list_header_id;
3180
3181 --only update entries which have not been updated already
3182 update ams_list_entries
3183 set LAST_CONTACTED_DATE = p_last_contacted_date,
3184 last_update_date = sysdate
3185 WHERE list_header_id = l_list_header_id
3186 and enabled_flag = 'Y'
3187 and LAST_CONTACTED_DATE is null;
3188
3189
3190 open c_count_list_entries(l_list_header_id);
3191 fetch c_count_list_entries
3192 into l_no_of_rows_prev_contacted ;
3193 close c_count_list_entries;
3194
3195
3196 update ams_list_headers_all
3197 set no_of_rows_prev_contacted = l_no_of_rows_prev_contacted,
3198 last_update_date = sysdate
3199 WHERE list_header_id = l_list_header_id;
3200
3201
3202
3203
3204 IF FND_API.To_Boolean ( p_commit ) THEN
3205 COMMIT WORK;
3206 END IF;
3207
3208
3209 IF (AMS_DEBUG_HIGH_ON) THEN
3210 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3211 FND_MESSAGE.Set_Token('ROW', 'AMS_LIST_HEADER_UPDATE: END');
3212 FND_MSG_PUB.Add;
3213 END IF;
3214
3215
3216
3217 FND_MSG_PUB.count_and_get(
3218 p_encoded => FND_API.g_false,
3219 p_count => x_msg_count,
3223 EXCEPTION
3220 p_data => x_msg_data
3221 );
3222
3224 WHEN FND_API.G_EXC_ERROR THEN
3225 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3226 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3227 FND_MSG_PUB.Add;
3228 -- Check if reset of the status is required
3229 x_return_status := FND_API.G_RET_STS_ERROR ;
3230 FND_MSG_PUB.count_and_get(
3231 p_encoded => FND_API.g_false,
3232 p_count => x_msg_count,
3233 p_data => x_msg_data);
3234
3235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3236 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3237 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3238 FND_MSG_PUB.Add;
3239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3240 FND_MSG_PUB.count_and_get(
3241 p_encoded => FND_API.g_false,
3242 p_count => x_msg_count,
3243 p_data => x_msg_data);
3244
3245 WHEN OTHERS THEN
3246 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3247 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
3248 FND_MSG_PUB.Add;
3249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3250 FND_MSG_PUB.count_and_get(
3251 p_encoded => FND_API.g_false,
3252 p_count => x_msg_count,
3253 p_data => x_msg_data);
3254
3255
3256 END;
3257
3258
3259
3260
3261
3262
3263
3264
3265 -- Start of Comments
3266 --
3267 -- NAME
3268 -- Copy_List
3269 --
3270 -- PURPOSE
3271 -- This procedure creates a list header record that satisfy caller needs
3272 --
3273 -- NOTES
3274 --
3275 --
3276 -- HISTORY
3277 -- 05/12/1999 tdonohoe created
3278 -- End of Comments
3279 PROCEDURE Copy_List
3280 ( p_api_version IN NUMBER,
3281 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3282 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3283 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3284 x_return_status OUT NOCOPY VARCHAR2,
3285 x_msg_count OUT NOCOPY NUMBER,
3286 x_msg_data OUT NOCOPY VARCHAR2,
3287 p_source_listheader_id IN NUMBER,
3288 p_listheader_rec IN list_header_rec_type,
3289 p_copy_select_actions IN VARCHAR2 := 'Y',
3290 p_copy_list_queries IN VARCHAR2 := 'Y',
3291 p_copy_list_entries IN VARCHAR2 := 'Y',
3292
3293 x_listheader_id OUT NOCOPY NUMBER
3294 ) IS
3295
3296 l_api_name CONSTANT VARCHAR2(30) := 'Copy_List';
3297 l_api_version CONSTANT NUMBER := 1.0;
3298 -- Status Local Variables
3299 l_return_status VARCHAR2(1); -- Return value from procedures
3300
3301 l_listheader_id number;
3302
3303 x_rowid VARCHAR2(30);
3304
3305 l_sqlerrm varchar2(600);
3306 l_sqlcode varchar2(100);
3307
3308 /*
3309 CURSOR fetch_list_details (list_id NUMBER) IS
3310 SELECT * FROM ams_list_headers_vl
3311 WHERE list_header_id = list_id ;
3312 l_reference_rec fetch_list_details%ROWTYPE;
3313 */
3314
3315 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
3316
3317 l_new_listheader_rec list_header_rec_type := p_listheader_rec;
3318 l_trg_listheader_rec list_header_rec_type;
3319
3320 BEGIN
3321
3322 -- Standard Start of API savepoint
3323 SAVEPOINT Copy_Lists_PVT;
3324
3325 x_return_status := FND_API.G_RET_STS_SUCCESS;
3326 -- Standard call to check for call compatibility.
3327 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3328 p_api_version,
3329 l_api_name,
3330 G_PKG_NAME) THEN
3331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3332 END IF;
3333
3334
3335 -- Initialize message list IF p_init_msg_list is set to TRUE.
3336 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3337 FND_MSG_PUB.initialize;
3338 END IF;
3339
3340 -- Debug Message
3341 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
3342 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3343 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeaders_PVT.Copy_Lists: Start', TRUE);
3344 FND_MSG_PUB.Add;
3345 END IF; */
3346
3347 IF (AMS_DEBUG_HIGH_ON) THEN
3348 AMS_Utility_PVT.debug_message('AMS_ListHeader_PVT.Copy_List: Start');
3349 END IF;
3350
3351 -- Initialize API return status to success
3352 x_return_status := FND_API.G_RET_STS_SUCCESS;
3353
3354
3355 l_new_listheader_rec.list_header_id:= p_source_listheader_id;
3356 ----------------------------------------------------------
3357 -- replace g_miss_char/num/date with current column values
3358 ----------------------------------------------------------
3359
3360 complete_listheader_rec(l_new_listheader_rec, l_trg_listheader_rec);
3361
3362
3363 -- Null fields
3364 l_trg_listheader_rec.list_header_id := FND_API.g_miss_num;
3365
3366 l_trg_listheader_rec.request_id := NULL;
3367 l_trg_listheader_rec.program_id := NULL;
3368 l_trg_listheader_rec.program_application_id := NULL;
3369 l_trg_listheader_rec.program_update_date := NULL;
3370
3371
3372 l_trg_listheader_rec.user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_LIST_STATUS','AVAILABLE');
3373 l_trg_listheader_rec.status_code := 'AVAILABLE';
3374 l_trg_listheader_rec.status_date := SYSDATE;
3378 -- ----------------------------
3375
3376 -- ----------------------------
3377 -- call create api
3379 Create_ListHeader( p_api_version=>l_api_version,
3380 p_init_msg_list=>l_init_msg_list,
3381 p_commit=>p_commit,
3382 p_validation_level=>p_validation_level,
3383 x_return_status=> x_return_status,
3384 x_msg_count=>x_msg_count,
3385 x_msg_data=> x_msg_data,
3386 p_listheader_rec => l_trg_listheader_rec,
3387 x_listheader_id => l_listheader_id );
3388
3389
3390 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3391 RAISE FND_API.G_EXC_ERROR;
3392 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3394 END IF;
3395
3396 -- set OUT value
3397 x_listheader_id := l_listheader_id;
3398
3399 /* IF p_copy_select_actions = 'Y' THEN
3400 -- call api to copy select actions
3401 END IF;
3402 */
3403 IF p_copy_list_queries = 'Y' THEN
3404 -- call api to copy list queries
3405 AMS_List_Query_PVT.Copy_List_Queries
3406 ( l_api_version,
3407 p_init_msg_list,
3408 p_commit,
3409 p_validation_level,
3410 p_source_listheader_id,
3411 l_listheader_id,
3412 l_trg_listheader_rec.list_name,
3413 x_return_status,
3414 x_msg_count,
3415 x_msg_data
3416 );
3417
3418 END IF;
3419
3420 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3421 RAISE FND_API.G_EXC_ERROR;
3422 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3424 END IF;
3425
3426
3427 IF p_copy_list_entries = 'Y' THEN
3428 -- call api to copy list entries
3429 AMS_List_Entries_PVT.Copy_List_Entries
3430 (
3431 l_api_version,
3432 p_init_msg_list,
3433 p_commit,
3434 p_validation_level,
3435 x_return_status,
3436 x_msg_count,
3437 x_msg_data,
3438 p_source_listheader_id,
3439 l_listheader_id
3440 );
3441
3442 END IF;
3443
3444 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3445 RAISE FND_API.G_EXC_ERROR;
3446 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3448 END IF;
3449
3450
3451 -- Standard check of p_commit.
3452 IF FND_API.To_Boolean ( p_commit ) THEN
3453 COMMIT WORK;
3454 END IF;
3455
3456 -- Success Message
3457 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3458 THEN
3459 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
3460 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Copy_Lists', TRUE);
3461 FND_MSG_PUB.Add;
3462 END IF;
3463
3464 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
3465 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
3466 FND_MESSAGE.Set_Token('ROW', 'AMS_listheaders_PVT.Copy_Lists: END', TRUE);
3467 FND_MSG_PUB.Add;
3468 END IF; */
3469
3470
3471 -- Standard call to get message count AND IF count is 1, get message info.
3472 FND_MSG_PUB.Count_AND_Get
3473 ( p_count => x_msg_count,
3474 p_data => x_msg_data,
3475 p_encoded => FND_API.G_FALSE
3476 );
3477
3478 EXCEPTION
3479 WHEN FND_API.G_EXC_ERROR THEN
3480 ROLLBACK TO Copy_Lists_PVT;
3481 x_return_status := FND_API.G_RET_STS_ERROR ;
3482
3483 FND_MSG_PUB.Count_AND_Get
3484 ( p_count => x_msg_count,
3485 p_data => x_msg_data,
3486 p_encoded => FND_API.G_FALSE
3487 );
3488
3489
3490 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3491 ROLLBACK TO Copy_Lists_PVT;
3492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3493 FND_MSG_PUB.Count_AND_Get
3494 ( p_count => x_msg_count,
3495 p_data => x_msg_data,
3496 p_encoded => FND_API.G_FALSE
3497 );
3498
3499 WHEN OTHERS THEN
3500 ROLLBACK TO Copy_Lists_PVT;
3501 FND_MESSAGE.set_name('AMS','SQL ERROR ->' || sqlerrm );
3502 FND_MSG_PUB.Add;
3503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3504 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3505 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3506 END IF;
3507
3508 FND_MSG_PUB.Count_AND_Get
3509 ( p_count => x_msg_count,
3510 p_data => x_msg_data,
3511 p_encoded => FND_API.G_FALSE
3512 );
3513
3514 END Copy_List;
3515
3516 END AMS_listheader_PVT;