[Home] [Help]
PACKAGE BODY: APPS.AMS_ACT_LIST_PVT
Source
1 PACKAGE BODY AMS_Act_List_PVT as
2 /* $Header: amsvalsb.pls 120.10.12010000.2 2008/08/11 08:34:12 amlal ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_ACT_LIST_PVT
7 -- Purpose
8 -- History
9 -- NOTE added fix for bug 3817224 on 08/06
10 -- 19-apr-2005 ndadwal code inclusions for Target Group Locking on ScheduleStatus change.
11 -- 17-Aug-2005 bmuthukr With R12 changes.
12 -- End of Comments
13 -- ===============================================================
14
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Act_List_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvalsb.pls';
18 g_list_header_id number;
19 g_count NUMBER := 1;
20 g_remote_list VARCHAR2(1) := 'N';
21 g_message_table AMS_LISTGENERATION_PKG.sql_string;
22 g_message_table_null AMS_LISTGENERATION_PKG.sql_string;
23 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
24 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
26
27 PROCEDURE copy_selections
28 (p_old_header_id in number,
29 p_new_header_id in number,
30 p_list_name IN varchar2,
31 x_msg_count OUT NOCOPY number,
32 x_msg_data OUT NOCOPY varchar2,
33 x_return_status IN OUT NOCOPY VARCHAR2,
34 x_query_id OUT NOCOPY NUMBER
35 );
36
37 PROCEDURE copy_list_queries
38 ( p_old_header_id IN NUMBER,
39 p_new_header_id IN NUMBER,
40 p_list_name IN varchar2,
41 p_old_query_id IN NUMBER,
42 x_msg_count OUT NOCOPY number,
43 x_msg_data OUT NOCOPY varchar2,
44 x_return_status IN OUT NOCOPY VARCHAR2,
45 x_new_query_id OUT NOCOPY NUMBER
46 );
47
48
49 PROCEDURE copy_query_list_params
50 (p_old_query_id in number,
51 p_new_query_id in number,
52 x_msg_count OUT NOCOPY number,
53 x_msg_data OUT NOCOPY varchar2,
54 x_return_status IN OUT NOCOPY VARCHAR2
55 );
56
57 PROCEDURE copy_template_instance(
58 p_query_templ_id in number,
59 p_old_header_id in number,
60 p_new_header_id in number,
61 x_msg_count OUT NOCOPY number,
62 x_msg_data OUT NOCOPY varchar2,
63 x_return_status IN OUT NOCOPY VARCHAR2,
64 x_old_templ_inst_id OUT NOCOPY number,
65 x_new_templ_inst_id OUT NOCOPY number
66 );
67
68 PROCEDURE copy_conditions(
69 p_old_templ_inst_id in number,
70 p_new_templ_inst_id in number,
71 x_msg_count OUT NOCOPY number,
72 x_msg_data OUT NOCOPY varchar2,
73 x_return_status IN OUT NOCOPY VARCHAR2
74 );
75
76 PROCEDURE UPDATE_LIST_STATUS_TO_LOCKED
77 (
78 P_OBJECT_TYPE IN VARCHAR2,
79 P_OBJ_ID IN NUMBER,
80 X_MSG_COUNT OUT NOCOPY NUMBER,
81 X_MSG_DATA OUT NOCOPY VARCHAR2,
82 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
83
84
85 PROCEDURE validate_segment
86 ( p_cell_id IN NUMBER,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2
90 )
91 is
92 l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
93 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
94 l_api_name CONSTANT VARCHAR2(30) := 'Validate Target Group Segment';
95 l_action_rec AMS_ListAction_PVT.action_rec_type ;
96 l_action_id NUMBER;
97 l_cell_list_name VARCHAR2(200);
98
99 l_found VARCHAR2(1) := 'N';
100 l_master_type VARCHAR2(80);
101 l_master_type_id NUMBER;
102 l_source_object_name VARCHAR2(80);
103 l_source_object_pk_field VARCHAR2(80);
104 l_from_position NUMBER;
105 l_from_counter NUMBER;
106 l_end_position NUMBER;
107 l_end_counter NUMBER;
108 l_sql_string VARCHAR2(32767);
109 l_return_status VARCHAR2(1);
110
111 l_count NUMBER;
112 l_string_copy VARCHAR2(32767);
113 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
114 l_length NUMBER;
115
116
117 CURSOR c_get_cell_name IS
118 SELECT cell_name
119 FROM ams_cells_vl
120 WHERE cell_id = p_cell_id;
121
122 l_source_type varchar2(100);
123 l_var varchar2(1);
124
125 BEGIN
126
127 OPEN c_get_cell_name ;
128 FETCH c_get_cell_name into l_cell_list_name;
129 CLOSE c_get_cell_name;
130
131
132 IF (AMS_DEBUG_HIGH_ON) THEN
133 AMS_Utility_PVT.debug_message(l_api_name||': validate ');
134 END IF;
135 AMS_CELL_PVT.get_single_sql(
136 p_api_version => 1.0,
137 p_init_msg_list => FND_API.G_FALSE,
138 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
139 x_return_status => l_return_status,
140 x_msg_count => x_msg_count,
141 x_msg_data => x_msg_data,
142 p_cell_id => p_cell_id,
143 x_sql_string => l_sql_string
144 );
145 IF (AMS_DEBUG_HIGH_ON) THEN
146 AMS_Utility_PVT.debug_message('get_single_sql status:' || l_return_status);
147 END IF;
148 IF l_return_status = FND_API.g_ret_sts_error THEN
149 RAISE FND_API.g_exc_error;
150 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
151 RAISE FND_API.g_exc_unexpected_error;
152 END IF;
153
154 IF (AMS_DEBUG_HIGH_ON) THEN
155 AMS_Utility_PVT.debug_message('sql_string:' || length(l_sql_string));
156 END IF;
157 IF l_sql_string IS NULL OR
158 l_sql_string = ''
159 THEN
160 RAISE FND_API.G_EXC_ERROR;
161 ELSE
162 --l_sql_string := UPPER(l_sql_string);
163
164 IF (AMS_DEBUG_HIGH_ON) THEN
165 AMS_Utility_PVT.debug_message('sql_string2:' || length(l_sql_string));
166 END IF;
167 l_count := 0;
168 l_string_copy := l_sql_string;
169 l_length := length(l_string_copy);
170
171 LOOP
172 l_count := l_count + 1;
173 IF l_length < 1999 THEN
174 l_sql_string_tbl(l_count) := l_string_copy;
175 EXIT;
176 ELSE
177 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
178 l_string_copy := substr(l_string_copy, 2000);
179 END IF;
180 l_length := length(l_string_copy);
181 END LOOP;
182
183 IF (AMS_DEBUG_HIGH_ON) THEN
184 AMS_Utility_PVT.debug_message('validate string:' || length(l_sql_string));
185 END IF;
186 l_found := 'N';
187 AMS_ListGeneration_PKG.validate_sql_string(
188 p_sql_string => l_sql_string_tbl ,
189 p_search_string => 'FROM',
190 p_comma_valid => 'N',
191 x_found => l_found,
192 x_position => l_from_position,
193 x_counter => l_from_counter) ;
194
195 IF (AMS_DEBUG_HIGH_ON) THEN
196 AMS_Utility_PVT.debug_message('FROM:' || l_found);
197 END IF;
198 IF l_found = 'N' THEN
199 FND_MESSAGE.set_name('AMS', 'AMS_LIST_FROM_NOT_FOUND');
200 FND_MSG_PUB.Add;
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203
204 l_found := 'N';
205
206 AMS_ListGeneration_PKG.get_master_types (
207 p_sql_string => l_sql_string_tbl,
208 p_start_length => 1,
209 p_start_counter => 1,
210 p_end_length => l_from_position,
211 p_end_counter => l_from_counter,
212 x_master_type_id=> l_master_type_id,
213 x_master_type=> l_master_type,
214 x_found=> l_found,
215 x_source_object_name => l_source_object_name,
216 x_source_object_pk_field => l_source_object_pk_field);
217
218 IF (AMS_DEBUG_HIGH_ON) THEN
219 AMS_Utility_PVT.debug_message('MASTER_TYPE:' || l_found);
220 END IF;
221 IF nvl(l_found,'N') = 'N' THEN
222 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_MASTER_TYPE');
223 FND_MSG_PUB.Add;
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226 END IF;
227 IF (AMS_DEBUG_HIGH_ON) THEN
228 AMS_Utility_PVT.debug_message('sucess full:' );
229 END IF;
230 x_return_status := FND_API.G_RET_STS_SUCCESS;
231 FND_MSG_PUB.count_and_get(
232 p_encoded => FND_API.g_false,
233 p_count => x_msg_count,
234 p_data => x_msg_data);
235
236
237 EXCEPTION
238 WHEN FND_API.G_EXC_ERROR THEN
239 FND_MESSAGE.set_name('AMS', 'AMS_CELL_CREATE_LIST_ERROR');
240 FND_MSG_PUB.Add;
241 -- Check if reset of the status is required
242 x_return_status := FND_API.G_RET_STS_ERROR ;
243 FND_MSG_PUB.count_and_get(
244 p_encoded => FND_API.g_false,
245 p_count => x_msg_count,
246 p_data => x_msg_data);
247
248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
249 FND_MESSAGE.set_name('AMS', 'AMS_CELL_CREATE_LIST_ERROR');
250 FND_MSG_PUB.Add;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
252 FND_MSG_PUB.count_and_get(
253 p_encoded => FND_API.g_false,
254 p_count => x_msg_count,
255 p_data => x_msg_data);
256
257 WHEN OTHERS THEN
258 FND_MESSAGE.set_name('AMS', 'AMS_CELL_CREATE_LIST_ERROR');
259 FND_MSG_PUB.Add;
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
261 FND_MSG_PUB.count_and_get(
262 p_encoded => FND_API.g_false,
263 p_count => x_msg_count,
264 p_data => x_msg_data);
265 END validate_segment;
266
267 PROCEDURE logger is
268 -- This procedure was written to replace Autonomous Transactions
269 --
270 l_return_status VARCHAR2(1);
271 BEGIN
272 -- Standard Start of API savepoint
273 SAVEPOINT logger_save;
274
275 FORALL I in g_message_table.first .. g_message_table.last
276 INSERT INTO ams_act_logs (
277 activity_log_id
278 ,last_update_date
279 ,last_updated_by
280 ,creation_date
281 ,created_by
282 ,last_update_login
283 ,object_version_number
284 ,act_log_used_by_id
285 ,arc_act_log_used_by
286 ,log_transaction_id
287 ,log_message_text
288 )
289 VALUES (
290 ams_act_logs_s.NEXTVAL
291 ,SYSDATE
292 ,FND_GLOBAL.User_Id
293 ,SYSDATE
294 ,FND_GLOBAL.User_Id
295 ,FND_GLOBAL.Conc_Login_Id
296 ,1
297 ,g_list_header_id
298 ,'LIST'
299 ,ams_act_logs_transaction_id_s.NEXTVAL
300 ,g_message_table(i)
301 ) ;
302 commit;
303 exception
304 -- Logger has failed
305 when others then
306 null;
307 END logger;
308
309
310
311 PROCEDURE WRITE_TO_ACT_LOG(p_msg_data in VARCHAR2,
312 p_arc_log_used_by in VARCHAR2 ,
313 p_log_used_by_id in number,
314 p_level in varchar2 default 'LOW')
315 IS
316 --PRAGMA AUTONOMOUS_TRANSACTION;
317 l_return_status VARCHAR2(1);
318
319 BEGIN
320
321 if nvl(ams_listgeneration_pkg.g_log_level,'HIGH') = 'HIGH' and p_level = 'LOW' then
322 return;
323 end if;
324
325 ams_listgeneration_pkg.write_to_act_log(p_msg_data,p_arc_log_used_by,p_log_used_by_id,P_level);
326
327 /* ams_listgeneration_pkg.g_message_table(ams_listgeneration_pkg.g_count) := p_msg_data;
328 ams_listgeneration_pkg.g_date(ams_listgeneration_pkg.g_count) := sysdate;
329 ams_listgeneration_pkg.g_count := ams_listgeneration_pkg.g_count + 1;
330 */
331
332 -- g_message_table(g_count) := p_msg_data;
333 -- g_count := g_count + 1;
334 /*
335 AMS_UTILITY_PVT.CREATE_LOG(
336 x_return_status => l_return_status,
337 p_arc_log_used_by => 'LIST',
338 p_log_used_by_id => g_list_header_id,
339 p_msg_data => p_msg_data);
340 */
341 -- COMMIT;
342 END WRITE_TO_ACT_LOG;
343
344 -- Hint: Primary key needs to be returned.
345 PROCEDURE Create_Act_List(
346 p_api_version_number IN NUMBER,
347 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
348 p_commit IN VARCHAR2 := FND_API.G_FALSE,
349 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
350 x_return_status OUT NOCOPY VARCHAR2,
351 x_msg_count OUT NOCOPY NUMBER,
352 x_msg_data OUT NOCOPY VARCHAR2,
353 p_act_list_rec IN act_list_rec_type := g_miss_act_list_rec,
354 x_act_list_header_id OUT NOCOPY NUMBER
355 ) IS
356 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Act_List';
357 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
358 l_return_status_full VARCHAR2(1);
359 l_object_version_number NUMBER := 1;
360 l_org_id NUMBER ;
361 l_ACT_LIST_HEADER_ID NUMBER;
362 l_dummy NUMBER;
363
364 -- Get the smallest order number
365 l_min_order number;
366 cursor c_min_order is
367 SELECT nvl(Min(order_number),-1)
368 FROM ams_act_lists
369 WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
370 list_used_by = p_act_list_rec.list_used_by and
371 list_act_type <> 'TARGET';
372
373 -- Get list action type where order num is the smallest
374 Cursor c_min_list_action_type IS
375 SELECT list_action_type
376 FROM ams_act_lists
377 WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
378 list_used_by = p_act_list_rec.list_used_by and
379 list_act_type <> 'TARGET' and
380 order_number = l_min_order;
381 l_action_type varchar2(30);
382
383
384 CURSOR c_id IS
385 SELECT AMS_ACT_LISTS_s.NEXTVAL
386 FROM dual;
387
388 CURSOR c_id_exists (l_id IN NUMBER) IS
389 SELECT 1
390 FROM AMS_ACT_LISTS
391 WHERE ACT_LIST_HEADER_ID = l_id;
392 l_act_list_rec act_list_rec_type := p_act_list_rec;
393
394 cursor c_check_group (cur_group_code in varchar2) is
395 select alg.act_list_group_id
396 from ams_act_list_groups alg,
397 ams_act_lists acl
398 where alg.arc_act_list_used_by = 'TARGET'
399 and alg.group_code = cur_group_code
400 and alg.act_list_used_by_id = acl.list_header_id
401 and acl.list_used_by = 'LIST'
402 and acl.list_used_by_id = p_act_list_rec.list_used_by_id
403 and acl.list_act_type = 'TARGET' ;
404 l_list_group_rec AMS_List_Group_PVT.list_group_rec_type;
405 l_act_list_group_id number;
406 BEGIN
407 -- Standard Start of API savepoint
408 SAVEPOINT CREATE_Act_List_PVT;
409
410 -- Standard call to check for call compatibility.
411 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
412 p_api_version_number,
413 l_api_name,
414 G_PKG_NAME)
415 THEN
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 END IF;
418
419 -- The first action with the smallest order number should be INCLUDE
420
421 open c_min_order;
422 fetch c_min_order into l_min_order;
423 close c_min_order;
424
425 if (l_min_order <> -1 ) and (l_min_order < p_act_list_rec.order_number ) then
426 OPEN c_min_list_action_type;
427 FETCH c_min_list_action_type INTO l_action_type;
428 CLOSE c_min_list_action_type;
429 else
430 l_action_type := p_act_list_rec.list_action_type;
431 end if;
432 IF l_action_type <> FND_API.G_MISS_CHAR
433 AND l_action_type IS NOT NULL THEN
434 IF(l_action_type <>'INCLUDE')THEN
435 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
436 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_FIRST_INCLUDE');
437 FND_MSG_PUB.Add;
438 END IF;
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 RAISE FND_API.G_EXC_ERROR;
441 END IF; --end if l_action_type <>'INCLUDE'
442 END IF;-- end IF l_action_type <> FND_API.G_MISS_CHAR
443
444
445 -- Initialize message list if p_init_msg_list is set to TRUE.
446 IF FND_API.to_Boolean( p_init_msg_list )
447 THEN
448 FND_MSG_PUB.initialize;
449 END IF;
450
451 -- Debug Message
452 IF (AMS_DEBUG_HIGH_ON) THEN
453
454 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'start');
455 END IF;
456
457
458 -- Initialize API return status to SUCCESS
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461 -- Local variable initialization
462
463 IF l_act_list_rec.ACT_LIST_HEADER_ID IS NULL OR
464 l_act_list_rec.ACT_LIST_HEADER_ID = FND_API.g_miss_num THEN
465 LOOP
466 l_dummy := NULL;
467 OPEN c_id;
468 FETCH c_id INTO l_ACT_LIST_HEADER_ID;
469 CLOSE c_id;
470
471 OPEN c_id_exists(l_ACT_LIST_HEADER_ID);
472 FETCH c_id_exists INTO l_dummy;
473 CLOSE c_id_exists;
474 EXIT WHEN l_dummy IS NULL;
475 END LOOP;
476 END IF;
477 x_act_list_header_id := l_act_list_header_id ;
478 l_act_list_rec.ACT_LIST_HEADER_ID := x_act_list_header_id;
479 -- =========================================================================
480 -- Validate Environment
481 -- =========================================================================
482
483 IF FND_GLOBAL.User_Id IS NULL
484 THEN
485 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_USER_PROFILE_MISSING');
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488
489 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
490 THEN
491 -- Debug message
492 IF (AMS_DEBUG_HIGH_ON) THEN
493
494 AMS_Utility_PVT.debug_message('Private API: Validate_Act_List');
495 END IF;
496 -- Invoke validation procedures
497 IF (AMS_DEBUG_HIGH_ON) THEN
498
499 AMS_Utility_PVT.debug_message('1)validate act_list ' || x_return_status );
500 END IF;
501
502 Validate_act_list(
503 p_api_version_number => 1.0,
504 p_init_msg_list => FND_API.G_FALSE,
505 p_validation_level => p_validation_level,
506 p_act_list_rec => l_act_list_rec,
507 x_return_status => x_return_status,
508 x_msg_count => x_msg_count,
509 x_msg_data => x_msg_data);
510
511
512
513 IF (AMS_DEBUG_HIGH_ON) THEN
514
515 AMS_Utility_PVT.debug_message('2)validate act list ->' || x_return_status );
516 END IF;
517 END IF;
518
519 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522
523
524 if l_act_list_rec.list_act_type <> 'TARGET'
525 and l_act_list_rec.group_code is not null then
526 -- Debug Message
527 IF (AMS_DEBUG_HIGH_ON) THEN
528
529 AMS_Utility_PVT.debug_message( 'Private API: Call Group code handler');
530 END IF;
531 l_act_list_group_id := null;
532 open c_check_group (l_act_list_rec.group_code ) ;
533 fetch c_check_group into l_act_list_group_id ;
534 close c_check_group ;
535 l_list_group_rec.group_code := l_act_list_rec.group_code ;
536 l_list_group_rec.act_list_used_by_id := p_act_list_rec.list_used_by_id;
537 l_list_group_rec.arc_act_list_used_by := 'TARGET';
538 l_list_group_rec.last_update_date := sysdate;
539 l_list_group_rec.last_updated_by := fnd_global.user_id;
540 l_list_group_rec.creation_date := sysdate;
541 l_list_group_rec.created_by := fnd_global.user_id;
542 if l_act_list_group_id is null then
543 AMS_List_Group_PVT.Create_List_Group(
544 p_api_version_number => 1.0,
545 p_init_msg_list => FND_API.G_FALSE,
546 p_commit => FND_API.G_FALSE,
547 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
548 x_return_status => x_return_status,
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data,
551 p_list_group_rec => l_list_group_rec,
552 x_act_list_group_id => l_act_list_group_id
553 );
554 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
555 RAISE FND_API.G_EXC_ERROR;
556 END IF;
557 end if;
558 end if;
559
560 -- Invoke table handler(AMS_ACT_LISTS_PKG.Insert_Row)
561 -- Debug Message
562 IF (AMS_DEBUG_HIGH_ON) THEN
563
564 AMS_Utility_PVT.debug_message( 'Private API: Call create table handler');
565 END IF;
566
567 AMS_ACT_LISTS_PKG.Insert_Row(
568 px_act_list_header_id => l_act_list_header_id,
569 p_last_update_date => SYSDATE,
570 p_last_updated_by => FND_GLOBAL.USER_ID,
571 p_creation_date => SYSDATE,
572 p_created_by => FND_GLOBAL.USER_ID,
573 px_object_version_number => l_object_version_number,
574 p_last_update_login => FND_GLOBAL.conc_LOGIN_ID,
575 p_list_header_id => l_act_list_rec.list_header_id,
576 p_group_code => l_act_list_rec.group_code ,
577 p_list_used_by_id => l_act_list_rec.list_used_by_id,
578 p_list_used_by => l_act_list_rec.list_used_by,
579 p_list_act_type => l_act_list_rec.list_act_type,
580 p_list_action_type => l_act_list_rec.list_action_type,
581 p_order_number => l_act_list_rec.order_number
582 );
583
584
585 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
586 RAISE FND_API.G_EXC_ERROR;
587 END IF;
588
589 --
590 -- End of API body
591 --
592 --Inserted vbhandar 04/21 to copy metrics
593 IF (l_act_list_rec.list_act_type = 'LIST') AND
594 (l_act_list_rec.list_action_type = 'INCLUDE') THEN
595
596 Ams_Refreshmetric_Pvt. Copy_Seeded_Metric (
597 p_api_version => 1.0,
598 p_init_msg_list => Fnd_Api.G_FALSE,
599 p_commit => Fnd_Api.G_FALSE,
600 x_return_status => x_return_status,
601 x_msg_count => x_msg_count,
602 x_msg_data => x_msg_data,
603 p_arc_act_metric_used_by=> 'ALIST',
604 p_act_metric_used_by_id => l_act_list_header_id,
605 p_act_metric_used_by_type => null
606 );
607 END IF;
608
609 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 --end vbhandar 04/21 to copy metrics
614
615
616 -- Standard check for p_commit
617 IF FND_API.to_Boolean( p_commit )
618 THEN
619 COMMIT WORK;
620 END IF;
621
622
623 -- Debug Message
624 IF (AMS_DEBUG_HIGH_ON) THEN
625
626 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'end');
627 END IF;
628
629 -- Standard call to get message count and if count is 1, get message info.
630 FND_MSG_PUB.Count_And_Get
631 (p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634 EXCEPTION
635
636 WHEN AMS_Utility_PVT.resource_locked THEN
637 x_return_status := FND_API.g_ret_sts_error;
638 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
639
640 WHEN FND_API.G_EXC_ERROR THEN
641 ROLLBACK TO CREATE_Act_List_PVT;
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 -- Standard call to get message count and if count=1, get the message
644 FND_MSG_PUB.Count_And_Get (
645 p_encoded => FND_API.G_FALSE,
646 p_count => x_msg_count,
647 p_data => x_msg_data
648 );
649
650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
651 ROLLBACK TO CREATE_Act_List_PVT;
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 -- Standard call to get message count and if count=1, get the message
654 FND_MSG_PUB.Count_And_Get (
655 p_encoded => FND_API.G_FALSE,
656 p_count => x_msg_count,
657 p_data => x_msg_data
658 );
659
660 WHEN OTHERS THEN
661 ROLLBACK TO CREATE_Act_List_PVT;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
664 THEN
665 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
666 END IF;
667 -- Standard call to get message count and if count=1, get the message
668 FND_MSG_PUB.Count_And_Get (
669 p_encoded => FND_API.G_FALSE,
670 p_count => x_msg_count,
671 p_data => x_msg_data
672 );
673 End Create_Act_List;
674
675 PROCEDURE init_act_list_Rec (
676 x_act_list_rec OUT NOCOPY act_list_rec_type)
677 IS
678 BEGIN
679 x_act_list_rec.act_list_header_id := FND_API.g_miss_num ;
680 x_act_list_rec.group_code := FND_API.g_miss_char ;
681 x_act_list_rec.last_update_date := FND_API.g_miss_date ;
682 x_act_list_rec.last_updated_by := FND_API.g_miss_num ;
683 x_act_list_rec.creation_date := FND_API.g_miss_date ;
684 x_act_list_rec.created_by := FND_API.g_miss_num ;
685 x_act_list_rec.object_version_number := FND_API.g_miss_num ;
686 x_act_list_rec.last_update_login := FND_API.g_miss_num ;
687 x_act_list_rec.list_header_id := FND_API.g_miss_num ;
688 x_act_list_rec.list_used_by_id := FND_API.g_miss_num ;
689 x_act_list_rec.list_used_by := FND_API.g_miss_char ;
690 x_act_list_rec.list_act_type := FND_API.g_miss_char ;
691 x_act_list_rec.list_action_type := FND_API.g_miss_char ;
692 x_act_list_rec.order_number := FND_API.g_miss_num ;
693
694 END init_act_list_Rec;
695
696 PROCEDURE Complete_act_list_Rec (
697 p_act_list_rec IN act_list_rec_type,
698 x_complete_rec OUT NOCOPY act_list_rec_type)
699 IS
700 l_return_status VARCHAR2(1);
701
702 CURSOR c_complete IS
703 SELECT *
704 FROM ams_act_lists
705 WHERE act_list_header_id = p_act_list_rec.act_list_header_id;
706 l_act_list_rec c_complete%ROWTYPE;
707 BEGIN
708 x_complete_rec := p_act_list_rec;
709
710
711 OPEN c_complete;
712 FETCH c_complete INTO l_act_list_rec;
713 CLOSE c_complete;
714
715 -- act_list_header_id
716 IF p_act_list_rec.act_list_header_id = FND_API.g_miss_num THEN
717 x_complete_rec.act_list_header_id := l_act_list_rec.act_list_header_id;
718 END IF;
719
720 IF p_act_list_rec.group_code = FND_API.g_miss_char THEN
721 x_complete_rec.group_code := l_act_list_rec.group_code;
722 END IF;
723
724 -- last_update_date
725 IF p_act_list_rec.last_update_date = FND_API.g_miss_date THEN
726 x_complete_rec.last_update_date := l_act_list_rec.last_update_date;
727 END IF;
728
729 -- last_updated_by
730 IF p_act_list_rec.last_updated_by = FND_API.g_miss_num THEN
731 x_complete_rec.last_updated_by := l_act_list_rec.last_updated_by;
732 END IF;
733
734 -- creation_date
735 IF p_act_list_rec.creation_date = FND_API.g_miss_date THEN
736 x_complete_rec.creation_date := l_act_list_rec.creation_date;
737 END IF;
738
739 -- created_by
740 IF p_act_list_rec.created_by = FND_API.g_miss_num THEN
741 x_complete_rec.created_by := l_act_list_rec.created_by;
742 END IF;
743
744 -- object_version_number
745 IF p_act_list_rec.object_version_number = FND_API.g_miss_num THEN
746 x_complete_rec.object_version_number := l_act_list_rec.object_version_number;
747 END IF;
748
749 -- last_update_login
750 IF p_act_list_rec.last_update_login = FND_API.g_miss_num THEN
751 x_complete_rec.last_update_login := l_act_list_rec.last_update_login;
752 END IF;
753
754 -- list_header_id
755 IF p_act_list_rec.list_header_id = FND_API.g_miss_num THEN
756 x_complete_rec.list_header_id := l_act_list_rec.list_header_id;
757 END IF;
758
759 -- list_used_by_id
760 IF p_act_list_rec.list_used_by_id = FND_API.g_miss_num THEN
761 x_complete_rec.list_used_by_id := l_act_list_rec.list_used_by_id;
762 END IF;
763
764 -- list_used_by
765 IF p_act_list_rec.list_used_by = FND_API.g_miss_char THEN
766 x_complete_rec.list_used_by := l_act_list_rec.list_used_by;
767 END IF;
768 -- list_act_type
769 IF p_act_list_rec.list_act_type = FND_API.g_miss_char THEN
770 x_complete_rec.list_act_type := l_act_list_rec.list_act_type;
771 END IF;
772 -- list_action_type
773 IF p_act_list_rec.list_action_type = FND_API.g_miss_char THEN
774 x_complete_rec.list_action_type := l_act_list_rec.list_action_type;
775 END IF;
776 -- order number
777 IF p_act_list_rec.order_number = FND_API.g_miss_num THEN
778 x_complete_rec.order_number := l_act_list_rec.order_number;
779 END IF;
780
781
782 -- Note: Developers need to modify the procedure
783 -- to handle any business specific requirements.
784 END Complete_act_list_Rec;
785
786 PROCEDURE Update_Act_List(
787 p_api_version_number IN NUMBER,
788 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
789 p_commit IN VARCHAR2 := FND_API.G_FALSE,
790 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_msg_count OUT NOCOPY NUMBER,
793 x_msg_data OUT NOCOPY VARCHAR2,
794 p_act_list_rec IN act_list_rec_type,
795 x_object_version_number OUT NOCOPY NUMBER
796 )
797
798 IS
799 -- Get the smallest order number
800 l_min_order number;
801 cursor c_min_order is
802 SELECT nvl(Min(order_number),-1)
803 FROM ams_act_lists
804 WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
805 list_used_by = p_act_list_rec.list_used_by and
806 list_act_type <> 'TARGET';
807
808 -- Get list action type where order num is the smallest
809 Cursor c_min_list_action_type IS
810 SELECT list_action_type
811 FROM ams_act_lists
812 WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
813 list_used_by = p_act_list_rec.list_used_by and
814 list_act_type <> 'TARGET' and
815 order_number = l_min_order;
816 l_action_type varchar2(30);
817
818 CURSOR c_get_act_list(cur_act_list_header_id NUMBER) IS
819 SELECT *
820 FROM AMS_ACT_LISTS
821 where act_list_header_id = cur_act_list_header_id ;
822 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Act_List';
823 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
824 cursor c_check_group (cur_group_code in varchar2) is
825 select alg.act_list_group_id
826 from ams_act_list_groups alg,
827 ams_act_lists acl
828 where alg.arc_act_list_used_by = 'TARGET'
829 and alg.group_code = cur_group_code
830 and p_act_list_rec.list_header_id = alg.act_list_used_by_id
831 and acl.list_used_by = 'LIST'
832 and acl.list_used_by_id = p_act_list_rec.list_used_by_id
833 and acl.list_act_type = 'TARGET' ;
834 -- Local Variables
835 l_object_version_number NUMBER;
836 l_ACT_LIST_HEADER_ID NUMBER;
837 l_ref_act_list_rec c_get_Act_List%ROWTYPE ;
838 l_tar_act_list_rec AMS_Act_List_PVT.act_list_rec_type := P_act_list_rec;
839 l_act_list_rec AMS_Act_List_PVT.act_list_rec_type := P_act_list_rec;
840 l_rowid ROWID;
841 l_act_list_group_id number;
842
843 l_list_group_rec AMS_List_Group_PVT.list_group_rec_type;
844 l_action_cnt NUMBER;
845 BEGIN
846 -- Standard Start of API savepoint
847 SAVEPOINT UPDATE_Act_List_PVT;
848 -- Standard call to check for call compatibility.
849 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
850 p_api_version_number,
851 l_api_name,
852 G_PKG_NAME)
853 THEN
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 END IF;
856
857 -- The first action with the smallest order number should be INCLUDE
858 open c_min_order;
859 fetch c_min_order into l_min_order;
860 close c_min_order;
861
862 -- if (l_min_order <> -1 ) and (l_min_order < p_act_list_rec.order_number and l_act_list_header_id <> p_act_list_rec.act_list_header_id ) then
863 if (l_min_order <> -1 ) and (l_min_order < p_act_list_rec.order_number) then
864
865 OPEN c_min_list_action_type;
866 FETCH c_min_list_action_type INTO l_action_type;
867 CLOSE c_min_list_action_type;
868 else
869 l_action_type := p_act_list_rec.list_action_type;
870 end if;
871 IF l_action_type <> FND_API.G_MISS_CHAR
872 AND l_action_type IS NOT NULL THEN
873 IF(l_action_type <>'INCLUDE')THEN
874 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
875 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_FIRST_INCLUDE');
876 FND_MSG_PUB.Add;
877 END IF;
878 x_return_status := FND_API.G_RET_STS_ERROR;
879 RAISE FND_API.G_EXC_ERROR;
880 END IF; --end if l_action_type <>'INCLUDE'
881 END IF;-- end IF l_action_type <> FND_API.G_MISS_CHAR
882
883
884
885
886 -- Initialize message list if p_init_msg_list is set to TRUE.
887 IF FND_API.to_Boolean( p_init_msg_list )
888 THEN
889 FND_MSG_PUB.initialize;
890 END IF;
891
892 -- Debug Message
893 IF (AMS_DEBUG_HIGH_ON) THEN
894
895 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'start');
896 END IF;
897
898 -- Initialize API return status to SUCCESS
899 x_return_status := FND_API.G_RET_STS_SUCCESS;
900
901 -- Debug Message
902 IF (AMS_DEBUG_HIGH_ON) THEN
903
904 AMS_Utility_PVT.debug_message('Private API: - Open Cursor to Select');
905 END IF;
906
907 OPEN c_get_Act_List( l_tar_act_list_rec.act_list_header_id);
908 FETCH c_get_Act_List INTO l_ref_act_list_rec ;
909 If ( c_get_Act_List%NOTFOUND) THEN
910 AMS_Utility_PVT.Error_Message(p_message_name =>
911 'AMS_API_MISSING_UPDATE_TARGET',
912 p_token_name => 'INFO',
913 p_token_value => 'Act_List') ;
914 RAISE FND_API.G_EXC_ERROR;
915 END IF;
916 -- Debug Message
917 IF (AMS_DEBUG_HIGH_ON) THEN
918
919 AMS_Utility_PVT.debug_message('Private API: - Close Cursor');
920 END IF;
921 CLOSE c_get_Act_List;
922
923
924 If (l_tar_act_list_rec.object_version_number is NULL or
925 l_tar_act_list_rec.object_version_number = FND_API.G_MISS_NUM ) Then
926 AMS_Utility_PVT.Error_Message(p_message_name =>
927 'AMS_API_VERSION_MISSING',
928 p_token_name => 'COLUMN',
929 p_token_value => 'Last_Update_Date') ;
930 raise FND_API.G_EXC_ERROR;
931 End if;
932 -- Check Whether record has been changed by someone else
933 If (l_tar_act_list_rec.object_version_number <>
934 l_ref_act_list_rec.object_version_number) Then
935 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
936 p_token_name => 'INFO',
937 p_token_value => 'Act_List') ;
938 raise FND_API.G_EXC_ERROR;
939 End if;
940
941 -- Complete rec
942 Complete_act_list_Rec(
943 p_act_list_rec => p_act_list_rec,
944 x_complete_rec => l_act_list_rec
945 );
946 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
947 THEN
948
949 IF (AMS_DEBUG_HIGH_ON) THEN
950
951
952
953 AMS_Utility_PVT.debug_message('Private API: Validate_Act_List');
954
955 END IF;
956
957 -- Invoke validation procedures
958 Validate_act_list(
959 p_api_version_number => 1.0,
960 p_init_msg_list => FND_API.G_FALSE,
961 p_validation_level => p_validation_level,
962 p_act_list_rec => l_act_list_rec,
963 x_return_status => x_return_status,
964 x_msg_count => x_msg_count,
965 x_msg_data => x_msg_data);
966 END IF;
967
968 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971
972
973
974 if l_act_list_rec.list_act_type <> 'TARGET'
975 and l_act_list_rec.group_code is not null then
976 l_act_list_group_id := null;
977 open c_check_group (l_act_list_rec.group_code ) ;
978 fetch c_check_group into l_act_list_group_id ;
979 close c_check_group ;
980 l_list_group_rec.group_code := l_act_list_rec.group_code ;
981 l_list_group_rec.act_list_used_by_id := l_act_list_rec.list_used_by_id;
982 l_list_group_rec.arc_act_list_used_by := 'TARGET';
983 l_list_group_rec.last_update_date := sysdate;
984 l_list_group_rec.last_updated_by := fnd_global.user_id;
985 l_list_group_rec.creation_date := sysdate;
986 l_list_group_rec.created_by := fnd_global.user_id;
987
988 if l_act_list_group_id is null then
989 AMS_List_Group_PVT.Create_List_Group(
990 p_api_version_number => 1.0,
991 p_init_msg_list => FND_API.G_FALSE,
992 p_commit => FND_API.G_FALSE,
993 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
994 x_return_status => x_return_status,
995 x_msg_count => x_msg_count,
996 x_msg_data => x_msg_data,
997 p_list_group_rec => l_list_group_rec,
998 x_act_list_group_id => l_act_list_group_id
999 );
1000 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1001 RAISE FND_API.G_EXC_ERROR;
1002 END IF;
1003 end if;
1004 end if;
1005
1006 AMS_ACT_LISTS_PKG.Update_Row(
1007 p_act_list_header_id => l_act_list_rec.act_list_header_id,
1008 p_last_update_date => SYSDATE,
1009 p_last_updated_by => FND_GLOBAL.USER_ID,
1010 p_creation_date => SYSDATE,
1011 p_created_by => FND_GLOBAL.USER_ID,
1012 p_object_version_number => p_act_list_rec.object_version_number,
1013 p_last_update_login => FND_GLOBAL.conc_LOGIN_ID,
1014 p_list_header_id => l_act_list_rec.list_header_id,
1015 p_group_code => l_act_list_rec.group_code,
1016 p_list_used_by_id => l_act_list_rec.list_used_by_id,
1017 p_list_used_by => l_act_list_rec.list_used_by,
1018 p_list_act_type => l_act_list_rec.list_act_type,
1019 p_list_action_type => l_act_list_rec.list_action_type,
1020 p_order_number => l_act_list_rec.order_number
1021 );
1022 --
1023 -- End of API body.
1024 --
1025
1026 -- Standard check for p_commit
1027 IF FND_API.to_Boolean( p_commit )
1028 THEN
1029 COMMIT WORK;
1030 END IF;
1031
1032
1033 -- Debug Message
1034 IF (AMS_DEBUG_HIGH_ON) THEN
1035
1036 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'end');
1037 END IF;
1038
1039 -- Standard call to get message count and if count is 1, get message info.
1040 FND_MSG_PUB.Count_And_Get
1041 (p_count => x_msg_count,
1042 p_data => x_msg_data
1043 );
1044 EXCEPTION
1045
1046 WHEN AMS_Utility_PVT.resource_locked THEN
1047 x_return_status := FND_API.g_ret_sts_error;
1048 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1049 WHEN FND_API.G_EXC_ERROR THEN
1050 ROLLBACK TO UPDATE_Act_List_PVT;
1051 x_return_status := FND_API.G_RET_STS_ERROR;
1052 -- Standard call to get message count and if count=1, get the message
1053 FND_MSG_PUB.Count_And_Get (
1054 p_encoded => FND_API.G_FALSE,
1055 p_count => x_msg_count,
1056 p_data => x_msg_data
1057 );
1058
1059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1060 ROLLBACK TO UPDATE_Act_List_PVT;
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062 -- Standard call to get message count and if count=1, get the message
1063 FND_MSG_PUB.Count_And_Get (
1064 p_encoded => FND_API.G_FALSE,
1065 p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068
1069 WHEN OTHERS THEN
1070 ROLLBACK TO UPDATE_Act_List_PVT;
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1072 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1073 THEN
1074 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1075 END IF;
1076 -- Standard call to get message count and if count=1, get the message
1077 FND_MSG_PUB.Count_And_Get (
1078 p_encoded => FND_API.G_FALSE,
1079 p_count => x_msg_count,
1080 p_data => x_msg_data
1081 );
1082 End Update_Act_List;
1083
1084
1085 PROCEDURE Delete_Act_List(
1086 p_api_version_number IN NUMBER,
1087 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1088 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1089 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1090 x_return_status OUT NOCOPY VARCHAR2,
1091 x_msg_count OUT NOCOPY NUMBER,
1092 x_msg_data OUT NOCOPY VARCHAR2,
1093 p_act_list_header_id IN NUMBER,
1094 p_object_version_number IN NUMBER
1095 )
1096
1097 IS
1098 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Act_List';
1099 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1100 l_object_version_number NUMBER;
1101
1102 cursor c1 is
1103 select 'x'
1104 from ams_act_lists a, ams_campaign_schedules_vl b
1105 where act_list_header_id = p_act_list_header_id
1106 and a.list_used_by_id = b.schedule_id
1107 and a.list_used_by = 'CSCH'
1108 and b.status_code = 'ACTIVE'
1109 union
1110 select 'x'
1111 from ams_act_lists a, ams_event_offers_vl b
1112 where p_act_list_header_id = p_act_list_header_id
1113 and a.list_used_by_id = b.event_offer_id
1114 and a.list_used_by in('EVEO','EONE')
1115 and b.system_status_code = 'ACTIVE';
1116 l_char varchar2(1) := 'Y';
1117 BEGIN
1118 open c1;
1119 fetch c1 into l_char ;
1120 close c1;
1121 -- Standard Start of API savepoint
1122 SAVEPOINT DELETE_Act_List_PVT;
1123
1124 -- Standard call to check for call compatibility.
1125 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1126 p_api_version_number,
1127 l_api_name,
1128 G_PKG_NAME)
1129 THEN
1130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1131 END IF;
1132
1133 -- Initialize message list if p_init_msg_list is set to TRUE.
1134 IF FND_API.to_Boolean( p_init_msg_list )
1135 THEN
1136 FND_MSG_PUB.initialize;
1137 END IF;
1138
1139 -- Debug Message
1140 IF (AMS_DEBUG_HIGH_ON) THEN
1141
1142 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'start');
1143 END IF;
1144
1145
1146 -- Initialize API return status to SUCCESS
1147 x_return_status := FND_API.G_RET_STS_SUCCESS;
1148
1149 --
1150 -- Api body
1151 --
1152 -- Debug Message
1153 IF (AMS_DEBUG_HIGH_ON) THEN
1154
1155 AMS_Utility_PVT.debug_message( 'Private API: Calling delete table
1156 handler');
1157 END IF;
1158
1159 -- Invoke table handler(AMS_ACT_LISTS_PKG.Delete_Row)
1160 if l_char = 'x' then
1161 FND_MESSAGE.set_name('AMS', 'AMS_DELETE_TARGET');
1162 FND_MSG_PUB.add;
1163 RAISE FND_API.g_exc_error;
1164 else
1165 AMS_ACT_LISTS_PKG.Delete_Row(
1166 p_ACT_LIST_HEADER_ID => p_ACT_LIST_HEADER_ID);
1167 end if;
1168 --
1169 -- End of API body
1170 --
1171
1172 -- Standard check for p_commit
1173 IF FND_API.to_Boolean( p_commit )
1174 THEN
1175 COMMIT WORK;
1176 END IF;
1177
1178
1179 -- Debug Message
1180 IF (AMS_DEBUG_HIGH_ON) THEN
1181
1182 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'end');
1183 END IF;
1184
1185 -- Standard call to get message count and if count is 1, get message info.
1186 FND_MSG_PUB.Count_And_Get
1187 (p_count => x_msg_count,
1188 p_data => x_msg_data
1189 );
1190 EXCEPTION
1191
1192 WHEN AMS_Utility_PVT.resource_locked THEN
1193 x_return_status := FND_API.g_ret_sts_error;
1194 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 ROLLBACK TO DELETE_Act_List_PVT;
1197 x_return_status := FND_API.G_RET_STS_ERROR;
1198 -- Standard call to get message count and if count=1, get the message
1199 FND_MSG_PUB.Count_And_Get (
1200 p_encoded => FND_API.G_FALSE,
1201 p_count => x_msg_count,
1202 p_data => x_msg_data
1203 );
1204
1205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1206 ROLLBACK TO DELETE_Act_List_PVT;
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1208 -- Standard call to get message count and if count=1, get the message
1209 FND_MSG_PUB.Count_And_Get (
1210 p_encoded => FND_API.G_FALSE,
1211 p_count => x_msg_count,
1212 p_data => x_msg_data
1213 );
1214
1215 WHEN OTHERS THEN
1216 ROLLBACK TO DELETE_Act_List_PVT;
1217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1218 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1219 THEN
1220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1221 END IF;
1222 -- Standard call to get message count and if count=1, get the message
1223 FND_MSG_PUB.Count_And_Get (
1224 p_encoded => FND_API.G_FALSE,
1225 p_count => x_msg_count,
1226 p_data => x_msg_data
1227 );
1228 End Delete_Act_List;
1229
1230
1231
1232 -- Hint: Primary key needs to be returned.
1233 PROCEDURE Lock_Act_List(
1234 p_api_version_number IN NUMBER,
1235 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1236 x_return_status OUT NOCOPY VARCHAR2,
1237 x_msg_count OUT NOCOPY NUMBER,
1238 x_msg_data OUT NOCOPY VARCHAR2,
1239 p_act_list_header_id IN NUMBER,
1240 p_object_version IN NUMBER
1241 )
1242
1243 IS
1244 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Act_List';
1245 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1246 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'||
1247 l_api_name;
1248 l_ACT_LIST_HEADER_ID NUMBER;
1249 CURSOR c_Act_List IS
1250 SELECT ACT_LIST_HEADER_ID
1251 FROM AMS_ACT_LISTS
1252 WHERE ACT_LIST_HEADER_ID = p_ACT_LIST_HEADER_ID
1253 AND object_version_number = p_object_version
1254 FOR UPDATE NOWAIT;
1255
1256 BEGIN
1257
1258 -- Debug Message
1259 IF (AMS_DEBUG_HIGH_ON) THEN
1260
1261 AMS_Utility_PVT.debug_message('Private API: ' || l_api_name || 'start');
1262 END IF;
1263 -- Initialize message list if p_init_msg_list is set to TRUE.
1264 IF FND_API.to_Boolean( p_init_msg_list )
1265 THEN
1266 FND_MSG_PUB.initialize;
1267 END IF;
1268
1269 -- Standard call to check for call compatibility.
1270 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1271 p_api_version_number,
1272 l_api_name,
1273 G_PKG_NAME)
1274 THEN
1275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1276 END IF;
1277
1278
1279 -- Initialize API return status to SUCCESS
1280 x_return_status := FND_API.G_RET_STS_SUCCESS;
1281
1282 IF (AMS_DEBUG_HIGH_ON) THEN
1283
1284
1285
1286 AMS_Utility_PVT.debug_message(l_full_name||': start');
1287
1288 END IF;
1289 OPEN c_Act_List;
1290
1291 FETCH c_Act_List INTO l_ACT_LIST_HEADER_ID;
1292
1293 IF (c_Act_List%NOTFOUND) THEN
1294 CLOSE c_Act_List;
1295 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1296 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1297 FND_MSG_PUB.add;
1298 END IF;
1299 RAISE FND_API.g_exc_error;
1300 END IF;
1301
1302 CLOSE c_Act_List;
1303
1304 -------------------- finish --------------------------
1305 FND_MSG_PUB.count_and_get(
1306 p_encoded => FND_API.g_false,
1307 p_count => x_msg_count,
1308 p_data => x_msg_data);
1309 IF (AMS_DEBUG_HIGH_ON) THEN
1310
1311 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1312 END IF;
1313 EXCEPTION
1314
1315 WHEN AMS_Utility_PVT.resource_locked THEN
1316 x_return_status := FND_API.g_ret_sts_error;
1317 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1318 WHEN FND_API.G_EXC_ERROR THEN
1319 ROLLBACK TO LOCK_Act_List_PVT;
1320 x_return_status := FND_API.G_RET_STS_ERROR;
1321 -- Standard call to get message count and if count=1, get the message
1322 FND_MSG_PUB.Count_And_Get (
1323 p_encoded => FND_API.G_FALSE,
1324 p_count => x_msg_count,
1325 p_data => x_msg_data
1326 );
1327
1328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1329 ROLLBACK TO LOCK_Act_List_PVT;
1330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1331 -- Standard call to get message count and if count=1, get the message
1332 FND_MSG_PUB.Count_And_Get (
1333 p_encoded => FND_API.G_FALSE,
1334 p_count => x_msg_count,
1335 p_data => x_msg_data
1336 );
1337
1338 WHEN OTHERS THEN
1339 ROLLBACK TO LOCK_Act_List_PVT;
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1341 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1342 THEN
1343 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1344 END IF;
1345 -- Standard call to get message count and if count=1, get the message
1346 FND_MSG_PUB.Count_And_Get (
1347 p_encoded => FND_API.G_FALSE,
1348 p_count => x_msg_count,
1349 p_data => x_msg_data
1350 );
1351 End Lock_Act_List;
1352
1353
1354 PROCEDURE check_act_list_uk_items(
1355 p_act_list_rec IN act_list_rec_type,
1356 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1357 x_return_status OUT NOCOPY VARCHAR2)
1358 IS
1359 l_valid_flag VARCHAR2(1);
1360
1361 CURSOR c_check_act_list_uniqueness(
1362 p_list_used_by_id NUMBER,
1363 p_list_used_by VARCHAR,
1364 p_list_act_type VARCHAR,
1365 p_list_header_id NUMBER,
1366 p_act_list_header_id NUMBER)
1367 IS
1368 SELECT count(1) FROM ams_act_lists
1369 WHERE list_used_by_id = p_list_used_by_id
1370 AND list_used_by = p_list_used_by
1371 AND LIST_ACT_TYPE = p_LIST_ACT_TYPE
1372 AND list_header_id = p_list_header_id
1373 AND act_list_header_id <> p_act_list_header_id;
1374
1375 l_count NUMBER;
1376 BEGIN
1377 x_return_status := FND_API.g_ret_sts_success;
1378 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1379 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1380 'AMS_ACT_LISTS',
1381 'ACT_LIST_HEADER_ID = ' || p_act_list_rec.ACT_LIST_HEADER_ID
1382 ||' AND LIST_ACT_TYPE = ' ||''''|| p_act_list_rec.LIST_ACT_TYPE||''''
1383 );
1384 ELSE
1385 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1386 'AMS_ACT_LISTS',
1387 'ACT_LIST_HEADER_ID = ' || p_act_list_rec.ACT_LIST_HEADER_ID
1388 ||' AND LIST_ACT_TYPE = ' ||''''|| p_act_list_rec.LIST_ACT_TYPE||''''
1389 ||' AND ACT_LIST_HEADER_ID <> ' || p_act_list_rec.ACT_LIST_HEADER_ID
1390 );
1391 END IF;
1392
1393 IF l_valid_flag = FND_API.g_false THEN
1394 AMS_Utility_PVT.Error_Message(p_message_name =>
1395 'AMS_ACT_LIST_ID_DUPLICATE');
1396 x_return_status := FND_API.g_ret_sts_error;
1397 RETURN;
1398 END IF;
1399
1400 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1401 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1402 'ams_act_lists',
1403 'list_used_by_id = ' || p_act_list_rec.list_used_by_id||
1404 ' and list_used_by = '||''''||p_act_list_rec.list_used_by||''''
1405 ||' AND LIST_ACT_TYPE = ' || ''''||p_act_list_rec.LIST_ACT_TYPE||''''
1406 ||' and list_header_id = '||p_act_list_rec.list_header_id
1407 ) ;
1408 ELSE
1409 /* dmvincen BUG 3792776: Too many variables for auto binding.
1410 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1411 'ams_act_lists',
1412 'list_used_by_id = ' || p_act_list_rec.list_used_by_id||
1413 ' and list_used_by = '||''''||p_act_list_rec.list_used_by||''''
1414 ||' AND LIST_ACT_TYPE = ' || ''''||p_act_list_rec.LIST_ACT_TYPE||''''
1415 ||' and list_header_id = '||p_act_list_rec.list_header_id
1416 || ' and act_list_header_id <> ' || p_act_list_rec.act_list_header_id
1417 );
1418 */
1419 open c_check_act_list_uniqueness(
1420 p_act_list_rec.list_used_by_id,
1421 p_act_list_rec.list_used_by,
1422 p_act_list_rec.List_act_type,
1423 p_act_list_rec.list_header_id,
1424 p_act_list_rec.act_list_Header_id);
1425 fetch c_check_act_list_uniqueness into l_count;
1426 close c_checK_act_list_uniqueness;
1427 IF l_count >= 1 THEN
1428 l_valid_flag := FND_API.G_FALSE;
1429 ELSE
1430 l_valid_flag := FND_API.G_TRUE;
1431 END IF;
1432 END IF;
1433
1434 IF l_valid_flag = FND_API.g_false THEN
1435 AMS_Utility_PVT.Error_Message(p_message_name =>
1436 'AMS_ACT_LIST_USED_DUP');
1437 x_return_status := FND_API.g_ret_sts_error;
1438 RETURN;
1439 END IF;
1440
1441 END check_act_list_uk_items;
1442
1443 PROCEDURE check_act_list_req_items(
1444 p_act_list_rec IN act_list_rec_type,
1445 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1446 x_return_status OUT NOCOPY VARCHAR2
1447 )
1448 IS
1449 BEGIN
1450 x_return_status := FND_API.g_ret_sts_success;
1451
1452 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1453
1454 IF p_act_list_rec.list_header_id = FND_API.g_miss_num OR
1455 p_act_list_rec.list_header_id IS NULL THEN
1456 AMS_Utility_PVT.Error_Message(p_message_name =>
1457 'AMS_API_MISSING_FIELD');
1458 FND_MESSAGE.set_token('MISS_FIELD',
1459 'LIST_HEADER_ID' );
1460 x_return_status := FND_API.g_ret_sts_error;
1461 RETURN;
1462 END IF;
1463
1464
1465 IF p_act_list_rec.list_used_by_id = FND_API.g_miss_num OR
1466 p_act_list_rec.list_used_by_id IS NULL THEN
1467 AMS_Utility_PVT.Error_Message(p_message_name =>
1468 'AMS_USER_PROFILE_MISSING');
1469 x_return_status := FND_API.g_ret_sts_error;
1470 RETURN;
1471 END IF;
1472
1473
1474 IF p_act_list_rec.list_used_by = FND_API.g_miss_char OR
1475 p_act_list_rec.list_used_by IS NULL THEN
1476 AMS_Utility_PVT.Error_Message(p_message_name =>
1477 'AMS_API_MISSING_FIELD');
1478 FND_MESSAGE.set_token('MISS_FIELD',
1479 'LIST_USED_BY' );
1480 x_return_status := FND_API.g_ret_sts_error;
1481 RETURN;
1482 END IF;
1483 IF p_act_list_rec.list_act_type = FND_API.g_miss_char OR
1484 p_act_list_rec.list_act_type IS NULL THEN
1485 AMS_Utility_PVT.Error_Message(p_message_name =>
1486 'AMS_API_MISSING_FIELD');
1487 FND_MESSAGE.set_token('MISS_FIELD',
1488 'LIST_ACT_TYPE' );
1489 x_return_status := FND_API.g_ret_sts_error;
1490 RETURN;
1491 END IF;
1492
1493
1494 ELSE
1495 /* Update Record */
1496 IF p_act_list_rec.act_list_header_id IS NULL THEN
1497 AMS_Utility_PVT.Error_Message(p_message_name =>
1498 'AMS_API_MISSING_FIELD');
1499 FND_MESSAGE.set_token('MISS_FIELD',
1500 'ACT_LIST_HEADER_ID' );
1501 x_return_status := FND_API.g_ret_sts_error;
1502 RETURN;
1503 END IF;
1504
1505 IF p_act_list_rec.list_header_id IS NULL THEN
1506 AMS_Utility_PVT.Error_Message(p_message_name =>
1507 'AMS_API_RESOURCE_LOCKED');
1508 x_return_status := FND_API.g_ret_sts_error;
1509 RETURN;
1510 END IF;
1511
1512
1513 IF p_act_list_rec.list_used_by_id IS NULL THEN
1514 AMS_Utility_PVT.Error_Message(p_message_name =>
1515 'AMS_API_RESOURCE_LOCKED');
1516 x_return_status := FND_API.g_ret_sts_error;
1517 RETURN;
1518 END IF;
1519
1520
1521 IF p_act_list_rec.list_used_by IS NULL THEN
1522 AMS_Utility_PVT.Error_Message(p_message_name =>
1523 'AMS_API_RESOURCE_LOCKED');
1524 x_return_status := FND_API.g_ret_sts_error;
1525 RETURN;
1526 END IF;
1527
1528 IF p_act_list_rec.list_act_type IS NULL THEN
1529 AMS_Utility_PVT.Error_Message(p_message_name =>
1530 'AMS_API_RESOURCE_LOCKED');
1531 x_return_status := FND_API.g_ret_sts_error;
1532 RETURN;
1533 END IF;
1534
1535
1536 END IF;
1537
1538 END check_act_list_req_items;
1539
1540 PROCEDURE check_act_list_FK_items(
1541 p_act_list_rec IN act_list_rec_type,
1542 x_return_status OUT NOCOPY VARCHAR2
1543 )
1544 IS
1545
1546 l_table_name varchar2(100);
1547 l_pk_name varchar2(100);
1548 l_list_act_type varchar2(60);
1549
1550 BEGIN
1551 x_return_status := FND_API.g_ret_sts_success;
1552
1553 IF p_act_list_rec.list_used_by <> FND_API.g_miss_char THEN
1554 AMS_Utility_PVT.get_qual_table_name_and_pk(
1555 p_sys_qual => p_act_list_rec.list_used_by,
1556 x_return_status => x_return_status,
1557 x_table_name => l_table_name,
1558 x_pk_name => l_pk_name
1559 );
1560
1561 IF x_return_status <> FND_API.g_ret_sts_success THEN
1562 RETURN;
1563 END IF;
1564
1565 IF p_act_list_rec.list_used_by_id <> FND_API.g_miss_num THEN
1566 IF ( AMS_Utility_PVT.Check_FK_Exists(l_table_name
1567 , l_pk_name
1568 , p_act_list_rec.list_used_by_id)
1569 = FND_API.G_TRUE)
1570 THEN
1571 x_return_status := FND_API.G_RET_STS_SUCCESS;
1572
1573 ELSE
1574 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1575 THEN
1576 FND_MESSAGE.set_name('AMS', 'AMS_SCHEDULE_ID_MISSING');
1577 FND_MSG_PUB.Add;
1578 END IF;
1579 x_return_status := FND_API.G_RET_STS_ERROR;
1580 RAISE FND_API.G_EXC_ERROR;
1581 END IF;
1582 END IF;
1583 END IF;
1584 IF p_act_list_rec.list_header_id <> FND_API.g_miss_num THEN
1585
1586 if p_act_list_rec.list_act_type = 'TARGET' or p_act_list_rec.list_act_type = 'EMPLOYEE' then
1587 l_list_act_type := 'LIST';
1588 else
1589 l_list_act_type := p_act_list_rec.list_act_type ;
1590 end if;
1591 AMS_Utility_PVT.get_qual_table_name_and_pk(
1592 p_sys_qual => l_list_act_type,
1593 x_return_status => x_return_status,
1594 x_table_name => l_table_name,
1595 x_pk_name => l_pk_name
1596 );
1597
1598 IF x_return_status <> FND_API.g_ret_sts_success THEN
1599 RETURN;
1600 END IF;
1601
1602 IF ( AMS_Utility_PVT.Check_FK_Exists(l_table_name
1603 , l_pk_name
1604 , p_act_list_rec.list_header_id)
1605 = FND_API.G_TRUE)
1606 THEN
1607 x_return_status := FND_API.G_RET_STS_SUCCESS;
1608
1609 ELSE
1610 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1611 THEN
1612 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ID_MISSING');
1613 FND_MSG_PUB.Add;
1614 END IF;
1615 x_return_status := FND_API.G_RET_STS_ERROR;
1616 RAISE FND_API.G_EXC_ERROR;
1617 END IF;
1618 END IF;
1619
1620 END check_act_list_FK_items;
1621
1622 PROCEDURE check_act_list_Lookup_items(
1623 p_act_list_rec IN act_list_rec_type,
1624 x_return_status OUT NOCOPY VARCHAR2
1625 )
1626 IS
1627 BEGIN
1628 x_return_status := FND_API.g_ret_sts_success;
1629
1630 -- Enter custom code here
1631 IF p_act_list_rec.list_act_type <> 'TARGET' and p_act_list_rec.list_act_type <> 'EMPLOYEE' then
1632 IF p_act_list_rec.list_act_type <> FND_API.g_miss_char THEN
1633 IF AMS_Utility_PVT.check_lookup_exists(
1634 p_lookup_type => 'AMS_LIST_ACT_TYPE',
1635 p_lookup_code => p_act_list_rec.list_act_type
1636 ) = FND_API.g_false
1637 THEN
1638 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1639 THEN
1640 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_TYPE_INVALID');
1641 FND_MESSAGE.set_token('LIST_ACT_TYPE',
1642 p_act_list_rec.list_act_type);
1643 FND_MSG_PUB.add;
1644 END IF;
1645
1646 x_return_status := FND_API.g_ret_sts_error;
1647 RETURN;
1648 END IF;
1649 END IF;
1650 END IF;
1651
1652 -- Check list_action_type
1653 IF p_act_list_rec.list_action_type <> FND_API.g_miss_char THEN
1654 IF AMS_Utility_PVT.check_lookup_exists(
1655 p_lookup_type => 'AMS_LIST_SELECT_ACTION',
1656 p_lookup_code => p_act_list_rec.list_action_type
1657 ) = FND_API.g_false
1658 THEN
1659 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1660 THEN
1661 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_TYPE_INVALID');
1662 FND_MESSAGE.set_token('LIST_ACT_TYPE', p_act_list_rec.list_action_type);
1663 FND_MSG_PUB.add;
1664 END IF;
1665
1666 x_return_status := FND_API.g_ret_sts_error;
1667 RETURN;
1668 END IF;
1669 END IF;
1670 END check_act_list_Lookup_items;
1671
1672 PROCEDURE Check_act_list_Items (
1673 P_act_list_rec IN act_list_rec_type,
1674 p_validation_mode IN VARCHAR2,
1675 x_return_status OUT NOCOPY VARCHAR2
1676 )
1677 IS
1678 BEGIN
1679
1680 x_return_status := FND_API.g_ret_sts_success ;
1681 -- Check Items Uniqueness API calls
1682
1683 IF (AMS_DEBUG_HIGH_ON) THEN
1684
1685
1686
1687 AMS_Utility_PVT.debug_message('validate uk items' );
1688
1689 END IF;
1690 check_act_list_uk_items(
1691 p_act_list_rec => p_act_list_rec,
1692 p_validation_mode => p_validation_mode,
1693 x_return_status => x_return_status);
1694 IF x_return_status <> FND_API.g_ret_sts_success THEN
1695 RETURN;
1696 END IF;
1697
1698 -- Check Items Required/NOT NULL API calls
1699
1700 IF (AMS_DEBUG_HIGH_ON) THEN
1701
1702
1703
1704 AMS_Utility_PVT.debug_message('validate req items' );
1705
1706 END IF;
1707 check_act_list_req_items(
1708 p_act_list_rec => p_act_list_rec,
1709 p_validation_mode => p_validation_mode,
1710 x_return_status => x_return_status);
1711 IF x_return_status <> FND_API.g_ret_sts_success THEN
1712 RETURN;
1713 END IF;
1714 -- Check Items Foreign Keys API calls
1715 IF (AMS_DEBUG_HIGH_ON) THEN
1716
1717 AMS_Utility_PVT.debug_message('validate fk items' );
1718 END IF;
1719
1720 check_act_list_FK_items(
1721 p_act_list_rec => p_act_list_rec,
1722 x_return_status => x_return_status);
1723 IF x_return_status <> FND_API.g_ret_sts_success THEN
1724 RETURN;
1725 END IF;
1726 -- Check Items Lookups
1727
1728 IF (AMS_DEBUG_HIGH_ON) THEN
1729
1730
1731
1732 AMS_Utility_PVT.debug_message('validate lookups items' );
1733
1734 END IF;
1735 check_act_list_Lookup_items(
1736 p_act_list_rec => p_act_list_rec,
1737 x_return_status => x_return_status);
1738 IF x_return_status <> FND_API.g_ret_sts_success THEN
1739 RETURN;
1740 END IF;
1741
1742 IF (AMS_DEBUG_HIGH_ON) THEN
1743
1744 AMS_Utility_PVT.debug_message('validate end of check act list');
1745 END IF;
1746
1747 END Check_act_list_Items;
1748
1749
1750 PROCEDURE Validate_act_list(
1751 p_api_version_number IN NUMBER,
1752 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1753 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1754 p_act_list_rec IN act_list_rec_type,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_count OUT NOCOPY NUMBER,
1757 x_msg_data OUT NOCOPY VARCHAR2
1758 )
1759 IS
1760 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Act_List';
1761 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1762 l_object_version_number NUMBER;
1763 l_act_list_rec AMS_Act_List_PVT.act_list_rec_type;
1764
1765 BEGIN
1766 -- Standard Start of API savepoint
1767 SAVEPOINT VALIDATE_Act_List_;
1768
1769
1770
1771 -- Standard call to check for call compatibility.
1772 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1773 p_api_version_number,
1774 l_api_name,
1775 G_PKG_NAME)
1776 THEN
1777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1778 END IF;
1779
1780 -- Initialize message list if p_init_msg_list is set to TRUE.
1781 IF FND_API.to_Boolean( p_init_msg_list )
1782 THEN
1783 FND_MSG_PUB.initialize;
1784 END IF;
1785
1786 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1787 IF (AMS_DEBUG_HIGH_ON) THEN
1788
1789 AMS_Utility_PVT.debug_message('act_list val->' || p_validation_level );
1790 END IF;
1791 Check_act_list_Items(
1792 p_act_list_rec => p_act_list_rec,
1793 p_validation_mode => JTF_PLSQL_API.g_update,
1794 x_return_status => x_return_status
1795 );
1796
1797 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1798 RAISE FND_API.G_EXC_ERROR;
1799 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1801 END IF;
1802
1803 END IF;
1804
1805 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1806 Validate_act_list_Rec(
1807 p_api_version_number => 1.0,
1808 p_init_msg_list => FND_API.G_FALSE,
1809 x_return_status => x_return_status,
1810 x_msg_count => x_msg_count,
1811 x_msg_data => x_msg_data,
1812 p_act_list_rec => l_act_list_rec);
1813
1814 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1815 RAISE FND_API.G_EXC_ERROR;
1816 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818 END IF;
1819 END IF;
1820
1821
1822
1823 IF (AMS_DEBUG_HIGH_ON) THEN
1824 AMS_Utility_PVT.debug_message('validate act_list after_act_list_rec' || x_return_status );
1825 END IF;
1826
1827 -- Initialize API return status to SUCCESS
1828 x_return_status := FND_API.G_RET_STS_SUCCESS;
1829
1830
1831 EXCEPTION
1832
1833 WHEN AMS_Utility_PVT.resource_locked THEN
1834 x_return_status := FND_API.g_ret_sts_error;
1835 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1836
1837 WHEN FND_API.G_EXC_ERROR THEN
1838 ROLLBACK TO VALIDATE_Act_List_;
1839 x_return_status := FND_API.G_RET_STS_ERROR;
1840 -- Standard call to get message count and if count=1, get the message
1841 FND_MSG_PUB.Count_And_Get (
1842 p_encoded => FND_API.G_FALSE,
1843 p_count => x_msg_count,
1844 p_data => x_msg_data
1845 );
1846
1847 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1848 ROLLBACK TO VALIDATE_Act_List_;
1849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1850 -- Standard call to get message count and if count=1, get the message
1851 FND_MSG_PUB.Count_And_Get (
1852 p_encoded => FND_API.G_FALSE,
1853 p_count => x_msg_count,
1854 p_data => x_msg_data
1855 );
1856
1857 WHEN OTHERS THEN
1858 ROLLBACK TO VALIDATE_Act_List_;
1859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1861 THEN
1862 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1863 END IF;
1864 -- Standard call to get message count and if count=1, get the message
1865 FND_MSG_PUB.Count_And_Get (
1866 p_encoded => FND_API.G_FALSE,
1867 p_count => x_msg_count,
1868 p_data => x_msg_data
1869 );
1870 End Validate_Act_List;
1871
1872
1873 PROCEDURE Validate_act_list_rec(
1874 p_api_version_number IN NUMBER,
1875 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1876 x_return_status OUT NOCOPY VARCHAR2,
1877 x_msg_count OUT NOCOPY NUMBER,
1878 x_msg_data OUT NOCOPY VARCHAR2,
1879 p_act_list_rec IN act_list_rec_type
1880 )
1881 IS
1882 BEGIN
1883 -- Initialize message list if p_init_msg_list is set to TRUE.
1884 IF FND_API.to_Boolean( p_init_msg_list )
1885 THEN
1886 FND_MSG_PUB.initialize;
1887 END IF;
1888
1889 -- Initialize API return status to SUCCESS
1890 x_return_status := FND_API.G_RET_STS_SUCCESS;
1891
1892 -- Hint: Validate data
1893 -- If data not valid
1894 -- THEN
1895 -- x_return_status := FND_API.G_RET_STS_ERROR;
1896
1897 -- Debug Message
1898 IF (AMS_DEBUG_HIGH_ON) THEN
1899
1900 AMS_Utility_PVT.debug_message('Private API: Validate_rec');
1901 END IF;
1902 -- Standard call to get message count and if count is 1, get message info.
1903 FND_MSG_PUB.Count_And_Get
1904 (p_count => x_msg_count,
1905 p_data => x_msg_data
1906 );
1907 END Validate_act_list_Rec;
1908
1909
1910 PROCEDURE create_target_group_list
1911 ( p_api_version IN NUMBER,
1912 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1913 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1914 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1915 p_list_used_by_name in VARCHAR2,
1916 p_list_used_by in VARCHAR2,
1917 p_list_used_by_id in NUMBER,
1918 p_list_type in VARCHAR2 := 'TARGET' ,
1919 p_owner_user_id in NUMBER,
1920 x_return_status OUT NOCOPY VARCHAR2,
1921 x_msg_count OUT NOCOPY NUMBER,
1922 x_msg_data OUT NOCOPY VARCHAR2,
1923 x_list_header_id OUT NOCOPY NUMBER ) IS
1924 l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
1925 l_act_list_rec AMS_Act_List_PVT.act_list_rec_type ;
1926 l_api_name constant varchar2(30) := 'Create_List';
1927 l_api_version CONSTANT NUMBER := 1.0;
1928 l_act_list_header_id number;
1929 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Act_List_PVT';
1930 l_count number;
1931 cursor c1 is
1932 select count(1)
1933 from ams_act_lists
1934 where list_used_by = p_list_used_by
1935 and list_used_by_id = p_list_used_by_id
1936 and list_act_type = 'TARGET' ;
1937
1938 BEGIN
1939 x_return_status := FND_API.G_RET_STS_SUCCESS;
1940 -- Standard call to check for call compatibility.
1941 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1942 p_api_version,
1943 l_api_name,
1944 G_PKG_NAME) THEN
1945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1946 END IF;
1947
1948
1949 -- Initialize message list IF p_init_msg_list is set to TRUE.
1950 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1951 FND_MSG_PUB.initialize;
1952 END IF;
1953
1954 -- Debug Message
1955 /*
1956 IF (AMS_DEBUG_HIGH_ON) THEN
1957 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
1958 FND_MESSAGE.Set_Token('TEXT', 'AMS_ListGeneration_PKG.cerate_list: Start', TRUE);
1959 FND_MSG_PUB.Add;
1960 END IF;
1961 */
1962 -- Initialize API return status to success
1963 x_return_status := FND_API.G_RET_STS_SUCCESS;
1964 -- Perform the database operation
1965
1966 open c1;
1967 fetch c1 into l_count ;
1968 close c1;
1969 if l_count = 0 then
1970 ams_listheader_pvt.init_listheader_rec(l_list_header_rec);
1971 l_list_header_rec.list_name := p_list_used_by_name ;
1972 l_list_header_rec.list_type := 'TARGET';
1973 l_list_header_rec.owner_user_id := p_owner_user_id;
1974 AMS_ListHeader_PVT.Create_Listheader
1975 ( p_api_version => 1.0,
1976 p_init_msg_list => p_init_msg_list,
1977 p_commit => p_commit,
1978 p_validation_level => p_validation_level ,
1979 x_return_status => x_return_status,
1980 x_msg_count => x_msg_count,
1981 x_msg_data => x_msg_data,
1982 p_listheader_rec => l_list_header_rec,
1983 x_listheader_id => x_list_header_id
1984 );
1985
1986 l_act_list_rec.list_header_id := x_list_header_id;
1987 l_act_list_rec.list_used_by := p_list_used_by;
1988 l_act_list_rec.list_used_by_id := p_list_used_by_id;
1989 l_act_list_rec.list_act_type := 'TARGET';
1990
1991 AMS_Act_List_PVT.Create_Act_List(
1992 p_api_version_number => p_api_version,
1993 p_init_msg_list => p_init_msg_list,
1994 p_commit => p_commit,
1995 p_validation_level => p_validation_level,
1996 x_return_status => x_return_status,
1997 x_msg_count => x_msg_count,
1998 x_msg_data => x_msg_data,
1999 p_act_list_rec => l_act_list_rec ,
2000 x_act_list_header_id => l_act_list_header_id
2001 ) ;
2002
2003
2004 if x_return_status <> FND_API.g_ret_sts_success THEN
2005 RAISE FND_API.G_EXC_ERROR;
2006 end if;
2007 end if;
2008
2009 -- Standard check of p_commit.
2010
2011 IF FND_API.To_Boolean ( p_commit ) THEN
2012 COMMIT WORK;
2013 END IF;
2014
2015 -- Success Message
2016 -- MMSG
2017 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2018 THEN
2019 FND_MESSAGE.Set_Name('AMS', 'AMS_API_SUCCESS');
2020 FND_MESSAGE.Set_Token('ROW', 'AMS_ACT_LIST.list_creation: ');
2021 FND_MSG_PUB.Add;
2022 END IF;
2023
2024
2025 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
2026 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2027 FND_MESSAGE.Set_Token('TEXT', 'AMS_ACT_LIST.list_act_creation: END');
2028 FND_MSG_PUB.Add;
2029 END IF; */
2030 FND_MSG_PUB.count_and_get(
2031 p_encoded => FND_API.g_false,
2032 p_count => x_msg_count,
2033 p_data => x_msg_data
2034 );
2035
2036 EXCEPTION
2037 WHEN FND_API.G_EXC_ERROR THEN
2038 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2039 FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
2040 FND_MSG_PUB.Add;
2041 -- Check if reset of the status is required
2042 x_return_status := FND_API.G_RET_STS_ERROR ;
2043 FND_MSG_PUB.count_and_get(
2044 p_encoded => FND_API.g_false,
2045 p_count => x_msg_count,
2046 p_data => x_msg_data);
2047
2048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2049 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2050 FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
2051 FND_MSG_PUB.Add;
2052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2053 FND_MSG_PUB.count_and_get(
2054 p_encoded => FND_API.g_false,
2055 p_count => x_msg_count,
2056 p_data => x_msg_data);
2057
2058 WHEN OTHERS THEN
2059 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2060 FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
2061 FND_MSG_PUB.Add;
2062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2063 FND_MSG_PUB.count_and_get(
2064 p_encoded => FND_API.g_false,
2065 p_count => x_msg_count,
2066 p_data => x_msg_data);
2067 END create_target_group_list;
2068
2069 PROCEDURE Control_Group_Generation(
2070 p_list_header_id IN NUMBER,
2071 p_pct_random IN NUMBER,
2072 p_no_random IN NUMBER,
2073 p_total_rows IN NUMBER,
2074 x_return_status OUT NOCOPY VARCHAR2,
2075 x_msg_count OUT NOCOPY NUMBER,
2076 x_msg_data OUT NOCOPY VARCHAR2) IS
2077 TYPE l_entries_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2078 l_list_entries_id l_entries_table_type ;
2079 l_total_random_rows NUMBER ;
2080 l_list_entry_count number;
2081
2082 CURSOR c_list_entries (p_list_header_id IN number ) is
2083 SELECT list_entry_id
2084 FROM ams_list_entries
2085 WHERE list_header_id = p_list_header_id
2086 AND enabled_flag = 'Y'
2087 ORDER BY randomly_generated_number ;
2088
2089 CURSOR c_get_count (p_list_header_id IN number ) is
2090 SELECT count(1)
2091 FROM ams_list_entries
2092 WHERE list_header_id = p_list_header_id
2093 AND enabled_flag = 'Y';
2094
2095 BEGIN
2096 write_to_act_log('Executing procedure control group generation in the remote instance.','LIST',p_list_header_id,'LOW');
2097
2098 x_return_status := FND_API.G_RET_STS_SUCCESS;
2099
2100
2101 open c_get_count(p_list_header_id);
2102 fetch c_get_count into l_list_entry_count;
2103 close c_get_count;
2104
2105 write_to_act_log(p_pct_random||'% of rows to be generated for control group','LIST',p_list_header_id,'LOW');
2106 write_to_act_log(l_list_entry_count||' are there in the list for control group generation','LIST',p_list_header_id,'LOW');
2107
2108 if nvl(p_pct_random,0) <> 0 then
2109 l_total_random_rows := FLOOR ((l_list_entry_count * p_pct_random) / 100);
2110 else
2111 l_total_random_rows := p_no_random ;
2112 end if;
2113
2114 DBMS_RANDOM.initialize (TO_NUMBER (TO_CHAR (SYSDATE, 'SSSSDD')));
2115
2116 UPDATE ams_list_entries
2117 SET randomly_generated_number = DBMS_RANDOM.random
2118 WHERE list_header_id = p_list_header_id
2119 and enabled_flag = 'Y';
2120
2121 write_to_act_log('Randomly generated number assigned to '||sql%rowcount||' list entries','LIST',p_list_header_id,'LOW');
2122
2123 DBMS_RANDOM.terminate;
2124
2125 OPEN c_list_entries (p_list_header_id);
2126 FETCH c_list_entries BULK COLLECT INTO l_list_entries_id LIMIT l_total_random_rows;
2127 CLOSE c_list_entries;
2128
2129 FORALL i in l_list_entries_id.FIRST .. l_list_entries_id.LAST
2130 UPDATE ams_list_entries
2131 SET part_of_control_group_flag = 'Y',
2132 enabled_flag = 'N'
2133 WHERE list_header_id = p_list_header_id
2134 AND list_entry_id = l_list_entries_id(i);
2135
2136 write_to_act_log(sql%rowcount||' entries made part of the control group for this target group.', 'LIST', g_list_header_id,'HIGH');
2137 write_to_act_log('Procedure control_group_generation executed successfully. ','LIST', g_list_header_id,'LOW');
2138
2139 exception
2140 when others then
2141 write_to_act_log(p_msg_data => 'Error while executing control_group_generation procedure '||sqlcode||' '||sqlerrm,
2142 p_arc_log_used_by => 'LIST',
2143 p_log_used_by_id => g_list_header_id,
2144 p_level=>'HIGH');
2145 x_msg_count := 1;
2146 x_msg_data := 'Error during CG generation'||' '|| sqlcode || '-->'||sqlerrm;
2147 x_return_status := 'E'; -- FND_API.G_RET_STS_ERROR ;
2148 END Control_Group_Generation;
2149
2150 PROCEDURE apply_supp(p_list_header_id in NUMBER,
2151 p_sql_string in VARCHAR2,
2152 p_media_id in NUMBER,
2153 p_source_type in VARCHAR2,
2154 p_supp_type in varchar2,
2155 x_return_status out nocopy varchar2,
2156 x_msg_count out nocopy number,
2157 x_msg_data out nocopy varchar2
2158 ) IS
2159
2160 l_list_header_id NUMBER;
2161
2162 --to get the set of suppression lists
2163 --associated with the TG's execution channel,
2164 --tied to the same datasource as the TG, and
2165 --the status in AVAILABLE or LOCKED
2166 CURSOR c_get_list(l_source_type VARCHAR2, l_media_id NUMBER) is
2167 SELECT acr.list_header_id
2168 FROM ams_list_cont_restrictions acr,
2169 ams_list_headers_all alh
2170 WHERE acr.list_header_id = alh.list_header_id
2171 AND alh.status_code in ('AVAILABLE','LOCKED')
2172 AND alh.list_source_type = l_source_type
2173 AND acr.media_id = l_media_id;
2174
2175 BEGIN
2176 write_to_act_log( p_msg_data => 'Executing procedure apply_supp',p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2177 OPEN c_get_list(p_source_type, p_media_id);
2178 LOOP
2179 fetch c_get_list into l_list_header_id ;
2180 exit when c_get_list%notfound;
2181 if p_supp_type = 'PARTY_ID' then
2182 if ams_listgeneration_pkg.g_remote_list_gen = 'N' then
2183 UPDATE ams_list_entries a
2184 SET a.enabled_flag = 'N', a.MARKED_AS_SUPPRESSED_FLAG = 'Y'
2185 WHERE a.list_header_id = p_list_header_id
2186 AND a.enabled_flag = 'Y'
2187 AND exists (SELECT 'x'
2188 FROM ams_list_entries b
2189 WHERE b.list_header_id = l_list_header_id
2190 AND a.party_id = b.party_id
2191 AND b.enabled_flag = 'Y');
2192 write_to_act_log(p_msg_data => sql%rowcount||' entries disabled for party_id based suppression. Suppression list header id is '||l_list_header_id,
2193 p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2194 else
2195 write_to_act_log(p_msg_data => 'Calling remote api for doing party id based suppression for suppression list'||l_list_header_id,
2196 p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2197 execute immediate
2198 'begin
2199 ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2200 ' end;'
2201 using p_sql_string,
2202 p_list_header_id,
2203 l_list_header_id,
2204 'PARTYIDSUPP',
2205 out x_msg_count,
2206 out x_msg_data,
2207 out x_return_status;
2208 end if;
2209 elsif p_supp_type = 'DEDUPE' then
2210 if nvl(ams_listgeneration_pkg.g_remote_list_gen,'N') = 'N' then
2211 EXECUTE IMMEDIATE p_sql_string using l_list_header_id;
2212 UPDATE ams_list_entries a SET a.enabled_flag = 'N', a.MARKED_AS_SUPPRESSED_FLAG = 'Y'
2213 WHERE a.list_header_id = p_list_header_id
2214 AND a.enabled_flag = 'Y'
2215 AND exists (SELECT 'x'
2216 FROM ams_list_entries b
2217 WHERE b.list_header_id = l_list_header_id
2218 AND b.dedupe_key = a.dedupe_key
2219 AND b.enabled_flag = 'Y');
2220 write_to_act_log(p_msg_data => sql%rowcount||' entries disabled for dedupe rule based suppression. Suppression list header id is '||l_list_header_id,
2221 p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2222 else
2223 write_to_act_log(p_msg_data => 'Calling remote api for doing dedupe rule based suppression for suppression list'||l_list_header_id,
2224 p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2225 execute immediate
2226 'begin
2227 ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2228 ' end;'
2229 using p_sql_string,
2230 p_list_header_id,
2231 l_list_header_id,
2232 'DEDUPERULESUPP',
2233 out x_msg_count,
2234 out x_msg_data,
2235 out x_return_status;
2236 end if;
2237 end if;
2238
2239 END LOOP;
2240 CLOSE c_get_list;
2241 EXCEPTION
2242 when others then
2243 write_to_act_log('Error while executing apply_supp procedure','LIST',p_list_header_id,'HIGH');
2244 END apply_supp;
2245
2246 PROCEDURE check_supp(p_list_used_by varchar2,
2247 p_list_used_by_id number,
2248 p_list_header_id number,
2249 x_return_status out nocopy varchar2,
2250 x_msg_count out nocopy number,
2251 x_msg_data out nocopy varchar2)
2252 IS
2253
2254 l_rule_id NUMBER;
2255 l_media_id NUMBER;
2256 l_campaign_id NUMBER;
2257 l_string VARCHAR2(4000);
2258 i NUMBER := 0;
2259 l_col_string VARCHAR2(2000);
2260 l_column VARCHAR2(60);
2261 l_return_status VARCHAR2(1);
2262 l_source_type VARCHAR2(30);
2263 l_chk_pk_map VARCHAR2(5) := NULL;
2264
2265
2266 --to get the list_rule_id (if any)
2267 --associated with the target group
2268 CURSOR c_get_rule_id is
2269 SELECT am.list_rule_id,
2270 ac.campaign_id,
2271 ac.activity_id
2272 FROM ams_campaign_schedules_b ac,
2273 ams_list_rule_usages am,
2274 ams_list_headers_all al
2275 WHERE al.list_used_by_id = ac.schedule_id
2276 AND am.list_header_id(+) = al.list_header_id
2277 AND ac.schedule_id = p_list_used_by_id
2278 AND al.list_header_id = p_list_header_id ;
2279
2280 --to get the datasource of the target group
2281 CURSOR c_get_list_data_source is
2282 SELECT list_source_type
2283 FROM ams_list_headers_all
2284 WHERE list_header_id = p_list_header_id ;
2285
2286 --to get the columns in ams_list_entries mapped
2287 --to the attributes of the datasource which
2288 --were used to define the de-duplication rule
2289 CURSOR c_rule_field(cur_rule_id number) is
2290 SELECT b.field_column_name
2291 FROM ams_list_rule_fields a,
2292 ams_list_src_fields b
2293 WHERE a.list_rule_id = cur_rule_id
2294 AND a.LIST_SOURCE_FIELD_ID = b.LIST_SOURCE_FIELD_ID;
2295
2296 --this will check if the Data Source's Uniq. Id is mapped
2297 --to the PARTY_ID column in ams_list_ensties
2298 CURSOR c_check_DS_PK_mapping(l_source_type VARCHAR2) is
2299 SELECT 1
2300 FROM ams_list_src_fields f,
2301 ams_list_src_types t
2302 WHERE t.LIST_SOURCE_TYPE_ID = f.LIST_SOURCE_TYPE_ID
2303 AND f.FIELD_COLUMN_NAME = 'PARTY_ID'
2304 AND f.SOURCE_COLUMN_NAME = t.SOURCE_OBJECT_PK_FIELD
2305 AND t.SOURCE_TYPE_CODE = l_source_type;
2306
2307 BEGIN
2308 OPEN c_get_rule_id ;
2309 FETCH c_get_rule_id into l_rule_id, l_campaign_id, l_media_id;
2310 CLOSE c_get_rule_id ;
2311
2312 write_to_act_log( p_msg_data => 'Executing procedure check_supp',p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2313
2314 write_to_act_log( p_msg_data => 'list_header_id = '||p_list_header_id||' , list_used_by_id = '||p_list_used_by_id||' , list_used_by = '||p_list_used_by,
2315 p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level =>'LOW');
2316
2317 write_to_act_log( p_msg_data => 'rule_id = '||l_rule_id||' , campaign_id = '||l_campaign_id||' , media_id = '||l_media_id,
2318 p_arc_log_used_by => 'LIST',
2319 p_log_used_by_id => p_list_header_id,
2320 p_level =>'LOW');
2321
2322 OPEN c_get_list_data_source ;
2323 FETCH c_get_list_data_source INTO l_source_type;
2324 CLOSE c_get_list_data_source ;
2325
2326 write_to_act_log( p_msg_data => 'Source type = '||l_source_type ,p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level=>'LOW');
2327
2328 --Start of Phase I of our Suppression logic ----------------------------------------------------
2329 --Check if the Data Source's Uniq. Id is mapped to party_id in list entries
2330 OPEN c_check_DS_PK_mapping(l_source_type) ;
2331 FETCH c_check_DS_PK_mapping into l_chk_pk_map;
2332 IF c_check_DS_PK_mapping%notfound THEN
2333 l_chk_pk_map := null;
2334 END IF;
2335 CLOSE c_check_DS_PK_mapping ;
2336
2337 -- apply PARTY_ID based suppression now
2338 if (l_chk_pk_map is not null) then
2339 write_to_act_log( p_msg_data => 'Calling apply_supp procedure to perform party_id based suppression',
2340 p_arc_log_used_by => 'LIST',
2341 p_log_used_by_id => p_list_header_id,p_level=>'HIGH');
2342
2343 --call procedure to update ams_list_entries table
2344 --matching the entries of the supprssion list(s)
2345 --using PARTY_ID as the dedupe_key in this case
2346 apply_supp(p_list_header_id, l_string,l_media_id,l_source_type,'PARTY_ID',x_return_status,x_msg_count,x_msg_data);
2347 write_to_act_log( p_msg_data => 'Party_id based suppression done.',
2348 p_arc_log_used_by => 'LIST',
2349 p_log_used_by_id => p_list_header_id,p_level=>'LOW');
2350
2351 else
2352 write_to_act_log( p_msg_data => 'Suppression based on DS Uniq. Id not performed because the column was not mapped to PARTY_ID ',
2353 p_arc_log_used_by => 'LIST',
2354 p_log_used_by_id => p_list_header_id,p_level=>'HIGH');
2355 end if; -- if the DS Uniq. Id is mapped to party_id in list entries
2356
2357 --dedupe rule based suppression
2358 if l_rule_id is not null then
2359 --reset l_string to new value
2360 l_string := 'update ams_list_entries set dedupe_key = ';
2361
2362 open c_rule_field(l_rule_id);
2363 LOOP
2364 fetch c_rule_field into l_column;
2365 exit when c_rule_field%notfound;
2366 write_to_act_log(p_msg_data => 'Column for DeDupe = '||l_column,p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level=>'LOW');
2367 if i = 0 then
2368 l_string := l_string||l_column;
2369 else
2370 l_string := l_string||'||'||'''.'''||'||'||l_column;
2371 end if;
2372 i := 1;
2373 END LOOP;
2374 close c_rule_field;
2375
2376 l_string := l_string||'' ;
2377 l_string := l_string||' where enabled_flag = '||''''||'Y'||''''||' and list_header_id = :b1 ';
2378 write_to_act_log(p_msg_data => 'SQL string with DD keys from the selected DD Rule = '||l_string,p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level=>'LOW');
2379 if ams_listgeneration_pkg.g_remote_list_gen = 'N' then
2380 execute immediate l_string using p_list_header_id;
2381 elsif ams_listgeneration_pkg.g_remote_list_gen = 'Y' then
2382 execute immediate
2383 'begin
2384 ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2385 ' end;'
2386 using l_string,
2387 p_list_header_id,
2388 p_list_header_id,
2389 'UPDDEDUPEKEY',
2390 out x_msg_count,
2391 out x_msg_data,
2392 out x_return_status;
2393 /*execute immediate
2394 'begin
2395 ams_remote_listgen_pkg.apply_suppression'||'@'||ams_listgeneration_pkg.g_database_link||'(:1,:2,:3,:4,:5,:6,:7)'||';'||
2396 ' end;'
2397 using l_string,
2398 p_list_header_id,
2399 null,
2400 'UPDDEDUPEKEY',
2401 out x_msg_count,
2402 out x_msg_data,
2403 out x_return_status;
2404 null;*/
2405 end if;
2406 --call procedure to update ams_list_entries table
2407 --matching the entries of the supprssion list(s)
2408 --using dedupe_keys from the selected De-Dupe Rule
2409 apply_supp(p_list_header_id, l_string,l_media_id,l_source_type,'DEDUPE',x_return_status,x_msg_count,x_msg_data);
2410 --end of Phase II; de-dupe key used from the selected De-dupe rule, if any ---------------------
2411 end if ; --if l_rule_id is not null
2412 EXCEPTION
2413 when others then
2414 write_to_act_log('Error while executing check_supp procedure','LIST',p_list_header_id,'HIGH');
2415 END CHECK_SUPP;
2416
2417 PROCEDURE util_get_source_code(
2418 p_activity_type IN VARCHAR2,
2419 p_activity_id IN NUMBER,
2420 x_return_status OUT NOCOPY VARCHAR2,
2421 x_source_code OUT NOCOPY VARCHAR2 ,
2422 x_source_id OUT NOCOPY NUMBER
2423 )
2424 IS
2425 BEGIN
2426
2427 SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
2428 FROM ams_source_codes
2429 WHERE arc_source_code_for = UPPER(p_activity_type)
2430 AND source_code_for_id = UPPER(p_activity_id)
2431 and active_flag = 'Y';
2432
2433
2434
2435 IF SQL%NOTFOUND THEN
2436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2437 ELSE
2438 x_return_status := FND_API.g_ret_sts_success ;
2439 END IF;
2440
2441
2442 EXCEPTION
2443
2444 WHEN OTHERS THEN
2445 x_source_code := NULL;
2446 x_source_id := NULL;
2447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2448
2449 End;
2450
2451
2452 PROCEDURE get_Source_Code(
2453 x_return_status OUT NOCOPY VARCHAR2,
2454 p_list_used_by in varchar2,
2455 p_list_used_by_id in number,
2456 x_source_code OUT NOCOPY varchar2
2457 ) IS
2458
2459 Cursor c_camp_source_code(cur_list_used_by_id number) is
2460 select sc.campaign_id , sc.use_parent_code_flag
2461 from ams_campaign_schedules_vl sc
2462 where sc.SCHEDULE_ID = cur_list_used_by_id ;
2463
2464 l_source_code_flag varchar2(1) := 'N' ;
2465 l_source_id number;
2466 l_campaign_id number;
2467 l_current_code varchar(30);
2468
2469 Begin
2470
2471 -- Standard Start of API savepoint
2472 SAVEPOINT Update_ListEntry_Source_Code;
2473
2474 -- Debug Message
2475 /* ckapoor
2476 IF (AMS_DEBUG_HIGH_ON) THEN
2477 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2478 FND_MESSAGE.Set_Token('ROW', 'Update_Source_Code: Start', TRUE);
2479 FND_MSG_PUB.Add;
2480 END IF; */
2481
2482 IF (AMS_DEBUG_HIGH_ON) THEN
2483 AMS_Utility_PVT.debug_message('Update_Source_Code:Start');
2484 END IF;
2485
2486
2487 -- Initialize API return status to success
2488 x_return_status := FND_API.G_RET_STS_SUCCESS;
2489
2490 if p_list_used_by = 'CSCH' then
2491 open c_camp_source_code(p_list_used_by_id );
2492 fetch c_camp_source_code into
2493 l_campaign_id , l_source_code_flag ;
2494 close c_camp_source_code;
2495 else
2496 util_get_source_code(
2497 p_activity_type => p_list_used_by ,
2498 p_activity_id => p_list_used_by_id ,
2499 x_return_status => x_return_status,
2500 x_source_code => x_source_code,
2501 x_source_id => l_source_id);
2502 end if;
2503
2504 if l_source_code_flag = 'Y' then
2505 util_get_source_code(
2506 p_activity_type => 'CAMP',
2507 p_activity_id => l_campaign_id,
2508 x_return_status => x_return_status,
2509 x_source_code => x_source_code,
2510 x_source_id => l_source_id);
2511 else
2512 util_get_source_code(
2513 p_activity_type => p_list_used_by ,
2514 p_activity_id => p_list_used_by_id ,
2515 x_return_status => x_return_status,
2516 x_source_code => x_source_code,
2517 x_source_id => l_source_id);
2518 end if;
2519
2520
2521 if x_return_status <> FND_API.g_ret_sts_success THEN
2522 RAISE FND_API.G_EXC_ERROR;
2523 end if;
2524 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2525 THEN
2526 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
2527 FND_MESSAGE.Set_Token('ROW', 'AMS_List_Entry_PVT.Update_ListEntry_Source_Code', TRUE);
2528 FND_MSG_PUB.Add;
2529 END IF;
2530
2531
2532 /* ckapoor IF (AMS_DEBUG_HIGH_ON) THEN
2533 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
2534 FND_MESSAGE.Set_Token('ROW', 'Update_Source_Code: END', TRUE);
2535 FND_MSG_PUB.Add;
2536 END IF; */
2537
2538
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541
2542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2543
2544 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2545 THEN
2546 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,'get_source_code');
2547 END IF;
2548 End get_source_code;
2549
2550 PROCEDURE generate_target_group_list_old
2551 ( p_api_version IN NUMBER,
2552 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2553 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2554 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2555 p_list_used_by in VARCHAR2,
2556 p_list_used_by_id in NUMBER,
2557 x_return_status OUT NOCOPY VARCHAR2,
2558 x_msg_count OUT NOCOPY NUMBER,
2559 x_msg_data OUT NOCOPY VARCHAR2
2560 ) is
2561 l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
2562 l_act_list_rec AMS_ACT_LIST_PVT.act_list_rec_type ;
2563 l_api_name constant varchar2(30) := 'gen_target_group_old';
2564 l_api_version CONSTANT NUMBER := 1.0;
2565 l_act_list_header_id number;
2566 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_ACT_LIST_PVT';
2567 l_count number;
2568 cursor c1 is
2569 select al.list_header_id,
2570 al.ctrl_random_pct_row_selection,
2571 al.ctrl_random_nth_row_selection,
2572 al.generate_control_group_flag, al.generation_type
2573 from ams_act_lists acl,ams_list_headers_all al
2574 where acl.list_used_by = p_list_used_by
2575 and acl.list_used_by_id = p_list_used_by_id
2576 and acl.list_act_type = 'TARGET'
2577 and al.list_header_id = acl.list_header_id ;
2578
2579 l_list_header_id number;
2580 l_ctrl_group_pct number;
2581 l_ctrl_group_row number;
2582 l_generate_control_group_flag varchar2(1);
2583
2584 cursor c2 is
2585 select list_header_id
2586 from ams_act_lists
2587 where list_used_by = p_list_used_by
2588 and list_used_by_id = p_list_used_by_id
2589 and list_act_type <> 'TARGET' ;
2590
2591 l_source_code varchar2(30);
2592 l_generation_type varchar2(30);
2593
2594
2595 cursor c_get_act is
2596 select acl.list_act_type,
2597 acl.list_header_id,
2598 acl.list_used_by_id, acl.act_list_header_id
2599 from ams_act_lists acl
2600 where acl.list_used_by = p_list_used_by
2601 and acl.list_used_by_id = p_list_used_by_id
2602 and acl.list_act_type = 'CELL' ;
2603 l_list_act_type_02 varchar2(30);
2604 l_list_header_id_02 number;
2605 l_list_used_by_id_02 number;
2606 l_act_list_header_id_02 number;
2607 l_std_sql varchar2(32767);
2608 l_include_sql varchar2(32767);
2609 l_parameter_list WF_PARAMETER_LIST_T;
2610 l_new_item_key VARCHAR2(30);
2611 l_tg_status_code varchar2(30);
2612 cursor c_tg_status is
2613 select status_code from ams_list_headers_all
2614 where list_header_id = l_list_header_id;
2615
2616 /* Bug fix: 3799192. Added by rrajesh on 07/30/04. */
2617 l_tca_field_mapped varchar2(1);
2618
2619 cursor c_master_ds_tca_mapped(list_head_id IN NUMBER)
2620 IS
2621 select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
2622 where hd.list_header_id = list_head_id
2623 and hd.LIST_SOURCE_TYPE = ty.source_type_code
2624 and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
2625 and fd.tca_column_id is NOT NULL;
2626
2627 /* Bug fix: 3799192. */
2628
2629 /* added by savio for p1 bug 3817724 */
2630
2631 cursor c_remote_list(list_head_id in number)
2632 IS
2633 select nvl(stypes.remote_flag,'N')
2634 from ams_list_src_types stypes, ams_list_headers_all list
2635 where list.list_source_type = stypes.source_type_code
2636 and list_header_id = list_head_id ;
2637
2638 /* added by savio for p1 bug 3817724 */
2639
2640 l_is_manual varchar2(1) := 'N'; --Added by bmuthukr for bug 3710720
2641
2642
2643 BEGIN
2644
2645 x_return_status := FND_API.G_RET_STS_SUCCESS;
2646 g_remote_list := 'N';
2647
2648 open c1;
2649 fetch c1 into l_list_header_id ,
2650 l_ctrl_group_pct ,
2651 l_ctrl_group_row ,
2652 l_generate_control_group_flag ,
2653 l_generation_type ;
2654 close c1;
2655 g_list_header_id := l_list_header_id ;
2656 ams_listgeneration_pkg.find_log_level(l_list_header_id);
2657
2658 write_to_act_log(p_msg_data => 'Executing generate_target_group_list_old procedure for kicking off target group generation',
2659 p_arc_log_used_by => 'LIST',
2660 p_log_used_by_id => l_list_header_id,
2661 p_level => 'HIGH');
2662
2663 --Added by bmuthukr for bug 3710720
2664 ams_listgeneration_pkg.is_manual(p_list_header_id => l_list_header_id,
2665 x_return_status => x_return_status,
2666 x_msg_count => x_msg_count,
2667 x_msg_data => x_msg_data,
2668 x_is_manual => l_is_manual);
2669 if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
2670 write_to_act_log('Error in executing is_manual procedure', 'LIST', g_list_header_id,'HIGH');
2671 write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
2672 ams_listgeneration_pkg.logger;
2673 return;
2674 end if;
2675
2676 if nvl(l_is_manual,'N') = 'Y' then
2677 write_to_act_log('Either list is a manual list, or incl are based on EMP list. Cannot generate','LIST',l_list_header_id,'HIGH');
2678 ams_listgeneration_pkg.logger;
2679 return;
2680 end if;
2681 --Ends changes.
2682
2683 -- Standard call to check for call compatibility.
2684 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2685 p_api_version,
2686 l_api_name,
2687 G_PKG_NAME) THEN
2688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2689 END IF;
2690
2691 -- Initialize message list IF p_init_msg_list is set to TRUE.
2692 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2693 FND_MSG_PUB.initialize;
2694 END IF;
2695
2696 get_Source_Code(
2697 x_return_status => x_return_status,
2698 p_list_used_by => p_list_used_by ,
2699 p_list_used_by_id => p_list_used_by_id ,
2700 x_source_code => l_source_code );
2701
2702 if x_return_status <> FND_API.g_ret_sts_success THEN
2703 RAISE FND_API.G_EXC_ERROR;
2704 end if;
2705
2706
2707 /* ------------------------- added by savio bug 381 7724 ---------------*/
2708
2709 open c_remote_list(l_list_header_id);
2710 fetch c_remote_list into g_remote_list;
2711 close c_remote_list;
2712
2713 write_to_act_log(p_msg_data => 'Remote list? ' ||g_remote_list,
2714 p_arc_log_used_by => 'LIST',
2715 p_log_used_by_id => l_list_header_id,
2716 p_level => 'LOW');
2717
2718
2719
2720
2721 /* ------------------------- end of added by savio bug 381 7724 ---------------*/
2722
2723 /* Bug fix: 3799192. Added by rrajesh on 07/30/04. If there is no maapping to TCA, update the
2724 status to FAILED and return */
2725 write_to_act_log(p_msg_data => 'Checking if datasource fields are mapped to TCA fields. ',
2726 p_arc_log_used_by => 'TARGET',
2727 p_log_used_by_id => l_list_header_id,
2728 p_level => 'LOW');
2729 open c_master_ds_tca_mapped(l_list_header_id);
2730 fetch c_master_ds_tca_mapped into l_tca_field_mapped;
2731 close c_master_ds_tca_mapped;
2732
2733 /* need to check for mandatory tca mapping only for remote target groups */
2734
2735 if g_remote_list = 'Y' then
2736 if l_tca_field_mapped is NULL THEN
2737 write_to_act_log(p_msg_data => 'Data Source fields are not mapped with tca fields -- Aborting target group generation process ',
2738 p_arc_log_used_by => 'TARGET',
2739 p_log_used_by_id => l_list_header_id,
2740 p_level => 'HIGH');
2741 UPDATE ams_list_headers_all
2742 SET last_generation_success_flag = 'N',
2743 status_code = 'FAILED',
2744 user_status_id = 311,
2745 status_date = sysdate,
2746 last_update_date = sysdate,
2747 main_gen_end_time = sysdate
2748 WHERE list_header_id = l_list_header_id;
2749 -- calling logging program
2750 ams_listgeneration_pkg.logger;
2751 --
2752 RETURN;
2753 end if;
2754 end if ;
2755
2756 /* End Bug fix: 3799192 */
2757 write_to_act_log(p_msg_data => 'Calling ams_listgeneration_pkg.generate_target_group procedure to generate target group',
2758 p_arc_log_used_by => 'TARGET',
2759 p_log_used_by_id => l_list_header_id,
2760 p_level => 'LOW');
2761
2762
2763 AMS_LISTGENERATION_PKG.GENERATE_TARGET_GROUP
2764 ( p_api_version => p_api_version,
2765 p_init_msg_list => FND_API.G_TRUE,
2766 p_commit => FND_API.G_FALSE,
2767 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2768 p_list_header_id => l_list_header_id,
2769 x_return_status => x_return_status,
2770 x_msg_count => x_msg_count,
2771 x_msg_data => x_msg_data );
2772
2773 If x_return_status in (FND_API.g_ret_sts_error,FND_API.g_ret_sts_unexp_error) then
2774 write_to_act_log(p_msg_data => 'Error in generating target group' ,
2775 p_arc_log_used_by => 'LIST',
2776 p_log_used_by_id => g_list_header_id,
2777 p_level => 'HIGH');
2778 end if;
2779
2780 IF x_return_status = FND_API.g_ret_sts_error THEN
2781 RAISE FND_API.g_exc_error;
2782 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2783 RAISE FND_API.g_exc_unexpected_error;
2784 END IF;
2785
2786 write_to_act_log(p_msg_data => 'Procedure AMS_LISTGENERATION_PKG.GENERATE_TARGET_GROUP executed successfully.' ,
2787 p_arc_log_used_by => 'LIST',
2788 p_log_used_by_id => g_list_header_id,
2789 p_level => 'HIGH');
2790
2791
2792 -- Need to move the call to supp and updates for marking party_id duplicates to amsvlgnb.pls starts
2793 /*
2794 write_to_act_log(p_msg_data => 'Marking duplicates based on dedupe key and party id' ,
2795 p_arc_log_used_by => 'LIST',
2796 p_log_used_by_id => g_list_header_id,
2797 p_level => 'HIGH');
2798 UPDATE ams_list_entries a
2799 SET a.enabled_flag = 'N',
2800 a.marked_as_duplicate_flag = 'Y'
2801 WHERE a.list_header_id = l_list_header_id
2802 and a.enabled_flag = 'Y'
2803 AND a.rowid > (SELECT min(b.rowid)
2804 from ams_list_entries b
2805 where b.list_header_id = l_list_header_id
2806 and b.dedupe_key = a.dedupe_key
2807 and b.enabled_flag = 'Y'
2808 );*/
2809
2810 /*
2811 UPDATE ams_list_entries a
2812 SET a.enabled_flag = 'N',
2813 a.marked_as_duplicate_flag = 'Y'
2814 WHERE a.list_header_id = l_list_header_id
2815 and a.enabled_flag = 'Y'
2816 AND a.rowid > (SELECT min(b.rowid)
2817 from ams_list_entries b
2818 where b.list_header_id = l_list_header_id
2819 and b.party_id = a.party_id
2820 and b.enabled_flag = 'Y'
2821 );
2822 */
2823
2824 /* UPDATE ams_list_entries a
2825 SET a.enabled_flag = 'N',
2826 a.marked_as_duplicate_flag = 'Y'
2827 WHERE a.list_header_id =l_list_header_id
2828 and a.enabled_flag = 'Y'
2829 AND a.rowid > (SELECT min(b.rowid)
2830 from ams_list_entries b
2831 where b.list_header_id = l_list_header_id
2832 and b.party_id = a.party_id
2833 and b.enabled_flag = 'Y'
2834 and b.rank = a.rank
2835 );
2836
2837 UPDATE ams_list_entries a
2838 SET a.enabled_flag = 'N',
2839 a.marked_as_duplicate_flag = 'Y'
2840 WHERE a.list_header_id = l_list_header_id
2841 and a.enabled_flag = 'Y'
2842 -- AND a.rowid > (SELECT min(b.rowid)
2843 AND a.rank > (SELECT min(b.rank)
2844 from ams_list_entries b
2845 where b.list_header_id = l_list_header_id
2846 and b.party_id = a.party_id
2847 and b.enabled_flag = 'Y'
2848 );*/
2849 /* if p_list_used_by = 'CSCH' then
2850 write_to_act_log(p_msg_data => 'Calling check_supp procedure' ,
2851 p_arc_log_used_by => 'LIST',
2852 p_log_used_by_id => g_list_header_id,
2853 p_level => 'LOW');
2854 check_supp( p_list_used_by => p_list_used_by,
2855 p_list_used_by_id => p_list_used_by_id ,
2856 p_list_header_id => l_list_header_id );
2857
2858 end if;
2859
2860 UPDATE ams_list_entries a
2861 SET a.enabled_flag = 'N',
2862 a.marked_as_duplicate_flag = 'Y'
2863 WHERE a.list_header_id = l_list_header_id
2864 and a.enabled_flag = 'Y'
2865 AND a.rowid > (SELECT min(b.rowid)
2866 from ams_list_entries b
2867 where b.list_header_id = l_list_header_id
2868 and b.dedupe_key = a.dedupe_key
2869 and b.enabled_flag = 'Y'
2870 );*/
2871 -- Need to move the call to supp and updates for marking party_id duplicates to amsvlgnb.pls ends
2872
2873 --Call will be made from amsvlgnb.pls directly..Not reqd from here any more.
2874
2875
2876 /*select count(1)
2877 into l_count
2878 from ams_list_entries
2879 where list_header_id = l_list_header_id
2880 and enabled_flag = 'Y';
2881
2882 if l_generate_control_group_flag = 'Y' then
2883 write_to_act_log(p_msg_data => 'Calling Control_Group_Generation. No of active entries = '||l_count ,
2884 p_arc_log_used_by => 'LIST',
2885 p_log_used_by_id => g_list_header_id,
2886 p_level => 'HIGH');
2887 Control_Group_Generation(
2888 l_list_header_id ,
2889 l_ctrl_group_pct ,
2890 l_ctrl_group_row ,
2891 l_count,
2892 x_return_status );
2893 write_to_act_log(p_msg_data => 'Control group generated. ' ,
2894 p_arc_log_used_by => 'LIST',
2895 p_log_used_by_id => g_list_header_id,
2896 p_level => 'LOW');
2897 end if;*/
2898
2899 UPDATE ams_list_entries set
2900 source_code = l_source_code ,
2901 arc_list_used_by_source = p_list_used_by ,
2902 source_code_for_id = p_list_used_by_id
2903 where list_header_id = l_list_header_id ;
2904
2905 AMS_LISTGENERATION_PKG.Update_List_Dets(l_list_header_id ,x_return_status ) ;
2906 -- --------------------------------------------------------
2907 -- Business Event for Traffic Cop
2908 -----------------------------------------------------------
2909 open c_tg_status;
2910 fetch c_tg_status into l_tg_status_code;
2911 close c_tg_status;
2912 if l_tg_status_code = 'AVAILABLE' then
2913 write_to_act_log(p_msg_data => 'Business event for traffic cop starts' ,
2914 p_arc_log_used_by => 'LIST',
2915 p_log_used_by_id => g_list_header_id,
2916 p_level =>'LOW');
2917 AMS_Utility_PVT.debug_message('Raise Business event for Target Group -- Start');
2918 -- Raise a business event
2919 l_new_item_key := l_list_header_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
2920 l_parameter_list := WF_PARAMETER_LIST_T();
2921 AMS_Utility_PVT.debug_message('Raise Business event-- after WF_PARAMETER_LIST_T call');
2922 wf_event.AddParameterToList(p_name => 'LIST_HEADER_ID',
2923 p_value => l_list_header_id,
2924 p_parameterlist => l_parameter_list);
2925 AMS_Utility_PVT.debug_message('Raise Business event-- after AddParameterToList call');
2926 wf_event.AddParameterToList(p_name => 'PURGE_FLAG',
2927 p_value => null,
2928 p_parameterlist => l_parameter_list);
2929 AMS_Utility_PVT.debug_message('Raise Business event-- after AddParameterToList call');
2930 AMS_Utility_PVT.debug_message('Raise Business event-- Start');
2931 WF_EVENT.Raise
2932 ( p_event_name => 'oracle.apps.ams.list.PostTargetGroupEvent',
2933 p_event_key => l_new_item_key,
2934 p_parameters => l_parameter_list);
2935 AMS_Utility_PVT.debug_message('Raise Business event for Target Group -- End');
2936 /*write_to_act_log(p_msg_data => 'Business event for traffic cop ends' ,
2937 p_arc_log_used_by => 'LIST',
2938 p_log_used_by_id => g_list_header_id,
2939 p_level =>'LOW');*/
2940 end if;
2941 -- --------------------------------------------------------
2942 IF (AMS_DEBUG_HIGH_ON) THEN
2943 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2944 FND_MESSAGE.Set_Token('TEXT', 'AMS_LISTGENERATION_PKG.cerate_list: Start', TRUE);
2945 FND_MSG_PUB.Add;
2946 END IF;
2947 write_to_act_log(p_msg_data => 'Target Group available ' ,
2948 p_arc_log_used_by => 'LIST',
2949 p_log_used_by_id => g_list_header_id,
2950 p_level=>'HIGH');
2951 ams_listgeneration_pkg.logger;
2952 -- Initialize API return status to success
2953 x_return_status := FND_API.G_RET_STS_SUCCESS;
2954 -- Perform the database operation
2955
2956 -- Standard check of p_commit.
2957
2958 IF FND_API.To_Boolean ( p_commit ) THEN
2959 COMMIT WORK;
2960 END IF;
2961
2962 -- Success Message
2963 -- MMSG
2964 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2965 THEN
2966 FND_MESSAGE.Set_Name('AMS', 'AMS_API_SUCCESS');
2967 FND_MESSAGE.Set_Token('ROW', 'AMS_ACT_LIST.list_creation: ');
2968 FND_MSG_PUB.Add;
2969 END IF;
2970
2971 IF (AMS_DEBUG_HIGH_ON) THEN
2972 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2973 FND_MESSAGE.Set_Token('TEXT', 'AMS_ACT_LIST.list_act_creation: END');
2974 FND_MSG_PUB.Add;
2975 END IF;
2976 FND_MSG_PUB.count_and_get(
2977 p_encoded => FND_API.g_false,
2978 p_count => x_msg_count,
2979 p_data => x_msg_data
2980 );
2981
2982 EXCEPTION
2983 WHEN FND_API.G_EXC_ERROR THEN
2984 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2985 FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
2986 FND_MSG_PUB.Add;
2987 -- Check if reset of the status is required
2988 x_return_status := FND_API.G_RET_STS_ERROR ;
2989 FND_MSG_PUB.count_and_get(
2990 p_encoded => FND_API.g_false,
2991 p_count => x_msg_count,
2992 p_data => x_msg_data);
2993
2994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2995 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2996 FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
2997 FND_MSG_PUB.Add;
2998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2999 FND_MSG_PUB.count_and_get(
3000 p_encoded => FND_API.g_false,
3001 p_count => x_msg_count,
3002 p_data => x_msg_data);
3003
3004 WHEN OTHERS THEN
3005 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
3006 FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
3007 FND_MSG_PUB.Add;
3008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3009 FND_MSG_PUB.count_and_get(
3010 p_encoded => FND_API.g_false,
3011 p_count => x_msg_count,
3012 p_data => x_msg_data);
3013 END generate_target_group_list_old;
3014
3015
3016 PROCEDURE generate_target_group_list
3017 ( p_api_version IN NUMBER,
3018 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3019 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3020 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3021 p_list_used_by in VARCHAR2,
3022 p_list_used_by_id in NUMBER,
3023 x_return_status OUT NOCOPY VARCHAR2,
3024 x_msg_count OUT NOCOPY NUMBER,
3025 x_msg_data OUT NOCOPY VARCHAR2
3026 ) is
3027 cursor c1 is
3028 select al.list_header_id
3029 from ams_act_lists acl,ams_list_headers_all al
3030 where acl.list_used_by = p_list_used_by
3031 and acl.list_used_by_id = p_list_used_by_id
3032 and acl.list_act_type = 'TARGET'
3033 and al.list_header_id = acl.list_header_id ;
3034
3035 l_cell_id number;
3036 cursor check_cell is
3037 select acl.list_header_id
3038 from ams_act_lists acl
3039 where acl.list_used_by = p_list_used_by
3040 and acl.list_used_by_id = p_list_used_by_id
3041 and acl.list_act_type = 'CELL' ;
3042
3043 l_list_header_id number;
3044 l_api_name constant varchar2(30) := 'gen_target_group';
3045 l_api_version CONSTANT NUMBER := 1.0;
3046
3047 CURSOR c_status IS
3048 SELECT status_code
3049 FROM ams_list_headers_all
3050 WHERE list_header_id = l_list_header_id ;
3051
3052 l_status_code varchar2(30);
3053
3054 begin
3055 x_return_status := FND_API.G_RET_STS_SUCCESS;
3056 -- Standard call to check for call compatibility.
3057 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3058 p_api_version,
3059 l_api_name,
3060 G_PKG_NAME) THEN
3061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3062 END IF;
3063
3064
3065 -- Initialize message list IF p_init_msg_list is set to TRUE.
3066 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3067 FND_MSG_PUB.initialize;
3068 END IF;
3069
3070
3071 open c1;
3072 fetch c1 into l_list_header_id ;
3073 close c1;
3074
3075 OPEN c_status ;
3076 FETCH c_status INTO l_status_code;
3077 CLOSE c_status ;
3078 IF l_status_code = 'GENERATING' THEN
3079 FND_MESSAGE.Set_Name('AMS','AMS_GENERATING');
3080 FND_MSG_PUB.Add;
3081 RAISE FND_API.G_EXC_ERROR;
3082 END IF;
3083
3084
3085 open check_cell;
3086 loop
3087 fetch check_cell into l_cell_id ;
3088 exit when check_cell%notfound;
3089 validate_segment
3090 ( p_cell_id => l_cell_id,
3091 x_return_status => x_return_status,
3092 x_msg_count => x_msg_count,
3093 x_msg_data => x_msg_data
3094 );
3095 end loop;
3096 close check_cell;
3097 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3098 RAISE FND_API.G_EXC_ERROR;
3099 END IF;
3100
3101 if l_list_header_id is not null then
3102 AMS_LIST_WF.StartProcess
3103 ( p_list_header_id => l_list_header_id
3104 ,workflowprocess => 'AMSLISTG') ;
3105 end if;
3106 x_return_status := FND_API.G_RET_STS_SUCCESS;
3107 -- Perform the database operation
3108
3109 -- Standard check of p_commit.
3110
3111 IF FND_API.To_Boolean ( p_commit ) THEN
3112 COMMIT WORK;
3113 END IF;
3114
3115 -- Success Message
3116 -- MMSG
3117 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3118 THEN
3119 FND_MESSAGE.Set_Name('AMS', 'AMS_API_SUCCESS');
3120 FND_MESSAGE.Set_Token('ROW', 'AMS_ACT_LIST.list_creation: ');
3121 FND_MSG_PUB.Add;
3122 END IF;
3123
3124 /*
3125 IF (AMS_DEBUG_HIGH_ON) THEN
3126 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
3127 FND_MESSAGE.Set_Token('TEXT', 'AMS_ACT_LIST.list_act_creation: END');
3128 FND_MSG_PUB.Add;
3129 END IF;
3130 */
3131 FND_MSG_PUB.count_and_get(
3132 p_encoded => FND_API.g_false,
3133 p_count => x_msg_count,
3134 p_data => x_msg_data
3135 );
3136 EXCEPTION
3137 WHEN FND_API.G_EXC_ERROR THEN
3138 --FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
3139 --FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
3140 --FND_MSG_PUB.Add;
3141 -- Check if reset of the status is required
3142 x_return_status := FND_API.G_RET_STS_ERROR ;
3143 FND_MSG_PUB.count_and_get(
3144 p_encoded => FND_API.g_false,
3145 p_count => x_msg_count,
3146 p_data => x_msg_data);
3147
3148 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3149 --FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
3150 --FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
3151 --FND_MSG_PUB.Add;
3152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3153 FND_MSG_PUB.count_and_get(
3154 p_encoded => FND_API.g_false,
3155 p_count => x_msg_count,
3156 p_data => x_msg_data);
3157
3158 WHEN OTHERS THEN
3159 --FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
3160 --FND_MESSAGE.Set_Token('TEXT', sqlerrm||' '||sqlcode);
3161 --FND_MSG_PUB.Add;
3162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3163 FND_MSG_PUB.count_and_get(
3164 p_encoded => FND_API.g_false,
3165 p_count => x_msg_count,
3166 p_data => x_msg_data);
3167
3168
3169 END generate_target_group_list;
3170 PROCEDURE check_char(p_input_string in varchar2
3171 ,p_comma_valid in varchar2
3172 ,p_valid_string OUT NOCOPY varchar2) IS
3173 BEGIN
3174 if p_input_string = ' ' then
3175 p_valid_string :='Y';
3176 elsif p_input_string = fnd_global.newline then
3177 p_valid_string :='Y';
3178 elsif p_input_string = fnd_global.tab then
3179 p_valid_string :='Y';
3180 elsif p_input_string = '
3181 ' then
3182 p_valid_string :='Y';
3183 elsif p_input_string = ',' then
3184 if p_comma_valid = 'Y' then
3185 p_valid_string :='Y';
3186 else
3187 p_valid_string :='N';
3188 end if;
3189 else
3190 p_valid_string :='N';
3191 end if;
3192 END;
3193
3194
3195 PROCEDURE validate_sql_string
3196 (p_sql_string in AMS_LISTGENERATION_PKG.sql_string
3197 ,p_search_string in varchar2
3198 ,p_comma_valid in varchar2
3199 ,x_found OUT NOCOPY varchar2
3200 ,x_position OUT NOCOPY number
3201 ,x_counter OUT NOCOPY number
3202 ) IS
3203 l_sql_string_1 varchar2(2000) := ' ';
3204 l_sql_string_2 varchar2(2000) ;
3205 l_concat_string varchar2(4000) ;
3206 l_valid_string varchar2(1) := 'N';
3207 l_position varchar2(200);
3208 BEGIN
3209
3210 /* Searching of the string is done by concatenating the two strings of
3211 2000 each gjoby more expln needed
3212 */
3213 x_found := 'N';
3214 --l_position := 'AMS_ListGeneration_PKG.Validate_sql_string start';
3215 l_sql_string_1 := lpad(l_sql_string_1,2000,' ');
3216
3217 for i in 1 .. p_sql_string.last
3218 loop
3219
3220 l_sql_string_2 := p_sql_string(i);
3221 if p_search_string = 'FROM' then
3222 l_concat_string := upper(l_sql_string_1) || upper(l_sql_string_2);
3223 else
3224 l_concat_string := l_sql_string_1 || l_sql_string_2;
3225 end if;
3226
3227 x_position := instrb(l_concat_string ,p_search_string);
3228 if x_position > 0 then
3229 loop
3230 l_valid_string := 'N' ;
3231 if x_position = 0 then
3232 exit;
3233 else
3234 check_char
3235 (p_input_string=>substrb(l_concat_string, x_position -1, 1)
3236 ,p_comma_valid =>p_comma_valid
3237 ,p_valid_string=> l_valid_string);
3238 if l_valid_string = 'Y' then
3239 check_char
3240 (p_input_string=>substrb(l_concat_string,
3241 x_position + length(p_search_string)
3242 , 1)
3243 ,p_comma_valid =>p_comma_valid
3244 ,p_valid_string=> l_valid_string);
3245 end if;
3246 end if;
3247 if l_valid_string = 'Y' then
3248 if x_position > 2000 then
3249 x_found := 'Y';
3250 x_counter := i;
3251 x_position := x_position - 2000;
3252 exit;
3253 end if;
3254 if x_position < 2001 then
3255 x_found := 'Y';
3256 x_counter := i -1 ;
3257 exit;
3258 end if;
3259 end if;
3260 x_position := instrb(l_concat_string ,
3261 x_position+1,
3262 p_search_string);
3263 end loop;
3264 exit;
3265 end if;
3266 l_sql_string_1 := l_sql_string_2;
3267 end loop;
3268 --l_position := 'AMS_ListGeneration_PKG.Validate_sql_string end';
3269 exception
3270 when others then
3271 write_to_act_log('AMS_ListGeneration_PKG.Error' || sqlerrm,null,null );
3272 END;
3273
3274 PROCEDURE get_condition(p_sql_string in AMS_LISTGENERATION_PKG.sql_string ,
3275 p_search_string in varchar2,
3276 p_comma_valid in varchar2,
3277 x_position OUT NOCOPY number,
3278 x_counter OUT NOCOPY number,
3279 x_found OUT NOCOPY varchar2,
3280 x_sql_string OUT NOCOPY AMS_LISTGENERATION_PKG.sql_string) is
3281 l_where_position number;
3282 l_where_counter number;
3283 l_counter number := 0;
3284 l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3285 begin
3286 validate_sql_string(p_sql_string => p_sql_string ,
3287 p_search_string => p_search_string,
3288 p_comma_valid => 'N',
3289 x_found => x_found,
3290 x_position =>x_position,
3291 x_counter => x_counter) ;
3292
3293 if x_counter > 0 then
3294 for i in x_counter .. p_sql_string.last
3295 loop
3296 l_counter := l_counter +1;
3297 x_sql_string(l_counter) := p_sql_string(i);
3298 if x_counter = i then
3299 x_sql_string(l_counter) := lpad(substrb(x_sql_string(l_counter),
3300 x_position),2000);
3301 end if;
3302 end loop;
3303 end if;
3304 end;
3305
3306 PROCEDURE form_sql_statement(p_select_statement in varchar2,
3307 p_select_add_statement in varchar2,
3308 p_master_type in varchar2,
3309 p_child_types in AMS_LISTGENERATION_PKG.child_type,
3310 p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
3311 p_act_list_header_id in number,
3312 p_action_used_by_id in number,
3313 x_final_string OUT NOCOPY varchar2
3314 ) is
3315 -- child_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3316 l_data_source_types varchar2(2000);
3317 l_field_col_tbl JTF_VARCHAR2_TABLE_100;
3318 l_source_col_tbl JTF_VARCHAR2_TABLE_100;
3319 l_view_tbl JTF_VARCHAR2_TABLE_100;
3320 cursor c_master_source_type is
3321 select source_object_name , source_object_name || '.' || source_object_pk_field
3322 from ams_list_src_types
3323 where source_type_code = p_master_type;
3324 cursor c_child_source_type (l_child_src_type varchar2 )is
3325 select a.source_object_name ,
3326 a.source_object_name || '.' || b.sub_source_type_pk_column
3327 ,b.master_source_type_pk_column
3328 from ams_list_src_types a, ams_list_src_type_assocs b
3329 where a.source_type_code = l_child_src_type
3330 and b.sub_source_type_id = a.list_source_type_id;
3331 l_count number;
3332 l_master_object_name varchar2(4000);
3333 l_child_object_name varchar2(4000);
3334 l_master_primary_key varchar2(1000);
3335 l_child_primary_key varchar2(32767);
3336 l_from_clause varchar2(32767);
3337 l_where_clause varchar2(32767);
3338 l_select_clause varchar2(32767);
3339 l_insert_clause varchar2(32767);
3340 l_final_sql varchar2(32767);
3341 l_insert_sql varchar2(32767);
3342 l_no_of_chunks number;
3343 l_master_fkey Varchar2(1000);
3344 l_dummy_primary_key varchar2(1000);
3345
3346
3347 l_created_by NUMBER; --batoleti added this var. For bug# 6688996
3348 /* batoleti. Bug# 6688996. Added the below cursor */
3349 CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
3350 SELECT created_by
3351 FROM ams_list_headers_all
3352 WHERE list_header_id= x_list_header_id;
3353
3354
3355 begin
3356 WRITE_TO_ACT_LOG('form_sql_statement->p_master_type' || p_master_type,'LIST',g_list_header_id);
3357 open c_master_source_type;
3358 fetch c_master_source_type into l_master_object_name , l_master_primary_key;
3359 close c_master_source_type;
3360 WRITE_TO_ACT_LOG('form_sql_statement->after master' || l_master_object_name,'LIST',g_list_header_id);
3361
3362 l_from_clause := ' FROM ' || l_master_object_name;
3363 l_data_source_types := ' ('|| ''''|| p_master_type ||'''';
3364 l_where_clause := 'where 1 = 1 ';
3365 WRITE_TO_ACT_LOG('form_sql_statement->before child','LIST',g_list_header_id);
3366
3367 l_count := p_child_types.count();
3368 if l_count > 0 then
3369 for i in 1..p_child_types.last
3370 loop
3371 l_data_source_types := l_data_source_types || ','|| ''''
3372 || p_child_types(i)||'''' ;
3373 open c_child_source_type(p_child_types(i));
3374 fetch c_child_source_type into l_child_object_name , l_child_primary_key
3375 ,l_master_fkey;
3376 l_dummy_primary_key := '';
3377 if l_master_fkey is not null then
3378 l_dummy_primary_key := l_master_object_name || '.'|| l_master_fkey;
3379 else
3380 l_dummy_primary_key := l_master_primary_key;
3381 end if;
3382 l_from_clause := l_from_clause || ','|| l_child_object_name ;
3383 l_where_clause := l_where_clause || 'and '
3384 ||l_dummy_primary_key || ' = '
3385 || l_child_primary_key || '(+)';
3386 close c_child_source_type;
3387 end loop;
3388 end if;
3389 WRITE_TO_ACT_LOG('form_sql_statement->after child','LIST',g_list_header_id);
3390 l_data_source_types := l_data_source_types || ') ' ;
3391
3392 EXECUTE IMMEDIATE
3393 'BEGIN
3394 SELECT b.field_column_name ,
3395 c.source_object_name,
3396 b.source_column_name
3397 BULK COLLECT INTO :1 ,:2 ,:3
3398 FROM ams_list_src_fields b, ams_list_src_types c
3399 WHERE b.list_source_type_id = c.list_source_type_id
3400 and b.DE_LIST_SOURCE_TYPE_CODE IN '|| l_data_source_types ||
3401 ' AND b.ROWID >= (SELECT MAX(a.ROWID)
3402 FROM ams_list_src_fields a
3403 WHERE a.field_column_name= b.field_column_name
3404 AND a.DE_LIST_SOURCE_TYPE_CODE IN '
3405 || l_data_source_types || ') ;
3406 END; '
3407 USING OUT l_field_col_tbl ,OUT l_view_tbl , OUT l_source_col_tbl ;
3408 --WRITE_TO_ACT_LOG('imp: p_select_statement' || p_select_statement);
3409 --WRITE_TO_ACT_LOG('imp: p_select_add_statement' || p_select_add_statement);
3410 --WRITE_TO_ACT_LOG('imp: select clause ' || l_select_clause);
3411 for i in 1 .. l_field_col_tbl.last
3412 loop
3413 l_insert_clause := l_insert_clause || ' ,' || l_field_col_tbl(i) ;
3414 l_select_clause := l_select_clause || ' ,' ||
3415 l_view_tbl(i) || '.'||l_source_col_tbl(i) ;
3416 --WRITE_TO_ACT_LOG('imp: select clause'||i||':->' || l_select_clause);
3417 end loop;
3418 --- Change p_select_action_id to 0
3419
3420 -- batoleti coding starts for bug# 6688996
3421 l_created_by := 0;
3422
3423 OPEN cur_get_created_by(g_list_header_id);
3424
3425 FETCH cur_get_created_by INTO l_created_by;
3426 CLOSE cur_get_created_by;
3427
3428 -- batoleti coding ends for bug# 6688996
3429
3430
3431
3432 WRITE_TO_ACT_LOG('form_sql_statement:before insert_sql ','LIST',g_list_header_id);
3433 l_insert_sql := 'insert into ams_list_entries '||
3434 '( LIST_SELECT_ACTION_FROM_NAME, '||
3435 ' LIST_ENTRY_SOURCE_SYSTEM_ID , '||
3436 ' LIST_ENTRY_SOURCE_SYSTEM_TYPE, '||
3437 ' list_select_action_id , '||
3438 ' list_header_id,last_update_date, '||
3439 ' last_updated_by,creation_date,created_by,'||
3440 'list_entry_id, '||
3441 'object_version_number, ' ||
3442 'source_code , ' ||
3443 'source_code_for_id , ' ||
3444 'arc_list_used_by_source , ' ||
3445 'arc_list_select_action_from , ' ||
3446 'pin_code , ' ||
3447 'view_application_id , ' ||
3448 'manually_entered_flag , ' ||
3449 'marked_as_random_flag , ' ||
3450 'marked_as_duplicate_flag , ' ||
3451 'part_of_control_group_flag , ' ||
3452 'exclude_in_triggered_list_flag , ' ||
3453 'enabled_flag ' ||
3454 l_insert_clause || ' ) ' ||
3455
3456 'select ' ||
3457 l_master_primary_key ||','||
3458 l_master_primary_key ||','||
3459 ''''||p_master_type||''''||','||
3460 0 || ',' ||
3461 to_char(g_list_header_id )|| ',' ||''''||
3462 to_char(sysdate )|| ''''||','||
3463 to_char(FND_GLOBAL.login_id )|| ',' ||''''||
3464 to_char(sysdate )|| ''''||','||
3465 to_char(nvl(l_created_by, FND_GLOBAL.login_id) )|| ',' ||
3466 'ams_list_entries_s.nextval' || ','||
3467 1 || ','||
3468 ''''||'NONE' ||'''' || ','||
3469 0 || ',' ||
3470 ''''||'NONE' ||'''' || ','||
3471 ''''||'NONE' ||'''' || ','||
3472 'ams_list_entries_s.currval'|| ','||
3473 530 || ','||
3474 ''''||'N' ||''''|| ','||
3475 ''''||'N' ||''''|| ','||
3476 ''''||'N' ||''''|| ','||
3477 ''''||'N' ||''''|| ','||
3478 ''''||'N' ||''''|| ','||
3479 ''''||'Y' ||''''||
3480 l_select_clause ;
3481
3482 --WRITE_TO_ACT_LOG('form_sql_statement:before final sql ');
3483 l_final_sql := l_insert_sql || ' ' ||
3484 l_from_clause || ' '||
3485 l_where_clause || ' and ' ||
3486 l_master_primary_key|| ' in ( ' ;
3487 x_final_string := l_final_sql;
3488 WRITE_TO_ACT_LOG('form_sql_statement:after final sql ','LIST',g_list_header_id);
3489 WRITE_TO_ACT_LOG('*************************************','LIST',g_list_header_id);
3490 l_no_of_chunks := ceil(length(l_final_sql)/2000 );
3491 for i in 1 ..l_no_of_chunks
3492 loop
3493 WRITE_TO_ACT_LOG(substrb(l_final_sql,(2000*i) - 1999,2000),'LIST',g_list_header_id);
3494 end loop;
3495 WRITE_TO_ACT_LOG('*************************************','LIST',g_list_header_id);
3496 WRITE_TO_ACT_LOG('end','LIST',g_list_header_id);
3497 exception
3498 when others then
3499 write_to_act_log(sqlerrm,'LIST',g_list_header_id );
3500 end form_sql_statement;
3501
3502 PROCEDURE process_insert_sql
3503 (p_select_statement in varchar2,
3504 p_select_add_statement in varchar2,
3505 p_master_type in varchar2,
3506 p_child_types in AMS_LISTGENERATION_PKG.child_type,
3507 p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
3508 p_act_list_header_id in number,
3509 p_action_used_by_id in number,
3510 x_std_sql OUT NOCOPY varchar2 ,
3511 x_include_sql OUT NOCOPY varchar2
3512 ) is
3513 l_final_sql varchar2(32767);
3514 l_insert_sql varchar2(32767);
3515 l_insert_sql1 varchar2(32767);
3516 l_table_name varchar2(80) := ' ams_list_tmp_entries ';
3517 BEGIN
3518 write_to_act_log('process_insert_sql:-->begin<--',null,null);
3519 l_insert_sql := p_select_statement ;
3520 write_to_act_log(l_insert_sql,null,null);
3521 for i in 1 .. p_from_string.last
3522 loop
3523 write_to_act_log(p_from_string(i),null,null);
3524 l_insert_sql := l_insert_sql || p_from_string(i);
3525 end loop;
3526 x_std_sql := l_insert_sql;
3527
3528 --WRITE_TO_ACT_LOG('form_sql_statement->before');
3529 form_sql_statement(p_select_statement ,
3530 p_select_add_statement ,
3531 p_master_type ,
3532 p_child_types ,
3533 p_from_string ,
3534 p_act_list_header_id ,
3535 p_action_used_by_id ,
3536 l_final_sql
3537 ) ;
3538 --WRITE_TO_ACT_LOG('form_sql_statement->after');
3539 x_include_sql := l_final_sql;
3540 --write_to_act_log('process_insert_sql:-->end<--');
3541 exception
3542 when others then
3543 write_to_act_log(sqlerrm ,null,null);
3544 END process_insert_sql;
3545
3546 PROCEDURE process_all_sql (
3547 p_action_used_by_id in number,
3548 p_act_list_header_id in number,
3549 p_incl_object_id in number,
3550 p_sql_string in AMS_LISTGENERATION_PKG.sql_string,
3551 p_primary_key in varchar2,
3552 p_source_object_name in varchar2,
3553 x_msg_count OUT NOCOPY number,
3554 x_msg_data OUT NOCOPY varchar2,
3555 x_return_status IN OUT NOCOPY VARCHAR2,
3556 x_std_sql OUT NOCOPY varchar2 ,
3557 x_include_sql OUT NOCOPY varchar2
3558 ) is
3559 l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3560 --l_sql_string sql_string;
3561 l_where_string AMS_LISTGENERATION_PKG.sql_string;
3562 l_from_string AMS_LISTGENERATION_PKG.sql_string;
3563 l_counter NUMBER := 1;
3564 l_from_position number;
3565 l_from_counter number;
3566 l_end_position number;
3567 l_end_counter number;
3568 l_order_position number;
3569 l_order_counter number;
3570 l_group_position number;
3571 l_group_counter number;
3572 l_found varchar2(1) := 'N';
3573 l_master_type varchar2(80);
3574 l_master_type_id number;
3575 l_source_object_name varchar2(80);
3576 l_source_object_pk_field varchar2(80);
3577 l_child_types AMS_LISTGENERATION_PKG.child_type;
3578 l_select_condition varchar2(2000);
3579 l_select_add_condition varchar2(2000);
3580 l_sql_string_v2 varchar2(4000);
3581 l_no_of_chunks number;
3582 BEGIN
3583 /* Validate Sql String will take all the sql statement fragement and
3584 check if the search string is present. If it is present it will
3585 return the position of fragement and the counter
3586 */
3587 l_sql_string := p_sql_string;
3588 --write_to_act_log('Process_all_sql: start ');
3589 --write_to_act_log('Process_all_sql return status: ' || x_return_status);
3590 l_found := 'N';
3591 validate_sql_string(p_sql_string => l_sql_string ,
3592 p_search_string => 'FROM',
3593 p_comma_valid => 'N',
3594 x_found => l_found,
3595 x_position =>l_from_position,
3596 x_counter => l_from_counter) ;
3597
3598 if l_found = 'N' then
3599 FND_MESSAGE.set_name('AMS', 'AMS_LIST_FROM_NOT_FOUND');
3600 FND_MSG_PUB.Add;
3601 RAISE FND_API.G_EXC_ERROR;
3602 end if;
3603 write_to_act_log('Process_all_sql: FROM Position ->'|| l_from_position ||
3604 '<--FROM Counter ->' || l_from_counter ||
3605 '<--FROM Found ->' || l_found,null,null);
3606 l_found := 'N';
3607 AMS_LISTGENERATION_PKG.get_master_types (p_sql_string => l_sql_string,
3608 p_start_length => 1,
3609 p_start_counter => 1,
3610 p_end_length => l_from_position,
3611 p_end_counter => l_from_counter,
3612 x_master_type_id=> l_master_type_id,
3613 x_master_type=> l_master_type,
3614 x_found=> l_found,
3615 x_source_object_name => l_source_object_name,
3616 x_source_object_pk_field => l_source_object_pk_field);
3617 if l_found = 'N' then
3618 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_MASTER_TYPE');
3619 FND_MSG_PUB.Add;
3620 RAISE FND_API.G_EXC_ERROR;
3621 end if;
3622 --write_to_act_log('Process_all_sql: Master Type->'|| l_master_type ||'<--' );
3623
3624
3625 l_found := 'N';
3626 AMS_LISTGENERATION_PKG.get_child_types (p_sql_string => l_sql_string,
3627 p_start_length => 1,
3628 p_start_counter => 1,
3629 p_end_length => l_from_position,
3630 p_end_counter => l_from_counter,
3631 p_master_type_id=> l_master_type_id,
3632 x_child_types=> l_child_types,
3633 x_found=> l_found);
3634
3635 --for i in 1 .. l_child_types.last
3636 --loop
3637 --write_to_act_log('Process_all_sql: child Type->'|| l_child_types(i) ||'<--' );
3638 -- end loop;
3639 l_found := 'N';
3640 get_condition(p_sql_string => l_sql_string ,
3641 p_search_string => 'FROM',
3642 p_comma_valid => 'N',
3643 x_position =>l_from_position,
3644 x_counter => l_from_counter,
3645 x_found => l_found,
3646 x_sql_string => l_from_string) ;
3647
3648 write_to_act_log('l_from_string'||l_from_string.last,null,null);
3649 for i in 1 .. l_from_string.last
3650 loop
3651 l_no_of_chunks := ceil(length(l_from_string(i))/2000 );
3652 write_to_act_log('l_sql_string chunks'||l_no_of_chunks,null,null);
3653 for j in 1 ..l_no_of_chunks
3654 loop
3655 WRITE_TO_ACT_LOG(i || 'j'||j,'LIST',g_list_header_id);
3656 WRITE_TO_ACT_LOG(substrb(l_from_string(i),(2000*j) - 1999,2000),'LIST',g_list_header_id);
3657 end loop;
3658 end loop;
3659
3660
3661 /* FOR SQL STATEMENTS WHICH ARE NOT FROM THE DERIVING MASTER SOURCE TABLE */
3662 if p_primary_key is not null then
3663 l_source_object_pk_field := p_primary_key;
3664 l_source_object_name := p_source_object_name ;
3665 end if;
3666 l_select_condition := 'SELECT ' ||l_source_object_name||'.'
3667 ||l_source_object_pk_field;
3668 --||'||'||''''
3669 --||l_master_type||'''';
3670 l_select_add_condition := ','||l_source_object_name||'.'
3671 ||l_source_object_pk_field||','||''''
3672 ||l_master_type||'''' ;
3673
3674 write_to_act_log('Process_all_sql: ***********insert sql ***********',null,null);
3675 process_insert_sql(p_select_statement => l_select_condition,
3676 p_select_add_statement => l_select_add_condition,
3677 p_master_type => l_master_type,
3678 p_child_types => l_child_types,
3679 p_from_string => l_from_string ,
3680 p_act_list_header_id => p_act_list_header_id ,
3681 p_action_used_by_id => p_action_used_by_id ,
3682 x_std_sql => x_std_sql,
3683 x_include_sql => x_include_sql
3684 );
3685 write_to_act_log('Process_all_sql: ***********end insert sql->' ,null,null);
3686 --write_to_act_log('Proc_all__sql -> end' ||x_return_status);
3687 EXCEPTION
3688 WHEN FND_API.G_EXC_ERROR THEN
3689 write_to_act_log('Error: AMS_ListGeneration_PKG.process_all_sql: ',null,null);
3690 x_return_status := FND_API.G_RET_STS_ERROR ;
3691 FND_MSG_PUB.count_and_get(
3692 p_encoded => FND_API.g_false,
3693 p_count => x_msg_count,
3694 p_data => x_msg_data
3695 );
3696
3697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3698 write_to_act_log('Error: AMS_ListGeneration_PKG.process_all_sql: ',null,null);
3699 x_return_status := FND_API.G_RET_STS_ERROR ;
3700 FND_MSG_PUB.count_and_get(
3701 p_encoded => FND_API.g_false,
3702 p_count => x_msg_count,
3703 p_data => x_msg_data
3704 );
3705
3706 WHEN OTHERS THEN
3707 write_to_act_log('Error: AMS_ListGeneration_PKG.process_all_sql:' ,null,null);
3708 x_return_status := FND_API.G_RET_STS_ERROR ;
3709 FND_MSG_PUB.count_and_get(
3710 p_encoded => FND_API.g_false,
3711 p_count => x_msg_count,
3712 p_data => x_msg_data
3713 );
3714
3715
3716 END process_all_sql;
3717
3718 PROCEDURE process_cell
3719 (p_action_used_by_id in number,
3720 p_act_list_header_id in number,
3721 p_incl_object_id in number,
3722 x_msg_count OUT NOCOPY number,
3723 x_msg_data OUT NOCOPY varchar2,
3724 x_return_status IN OUT NOCOPY VARCHAR2,
3725 x_std_sql OUT NOCOPY varchar2 ,
3726 x_include_sql OUT NOCOPY varchar2
3727 ) is
3728
3729 ------------------------------------------------------------------------------
3730 -- Given the sql id from ams_list_select_actions it will retrieve the
3731 -- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
3732 -- workbook_name.
3733 ------------------------------------------------------------------------------
3734 l_sql_string AMS_LISTGENERATION_PKG.sql_string;
3735 l_where_string AMS_LISTGENERATION_PKG.sql_string;
3736 l_from_string AMS_LISTGENERATION_PKG.sql_string;
3737 l_counter NUMBER := 1;
3738 l_from_position number;
3739 l_from_counter number;
3740 l_end_position number;
3741 l_end_counter number;
3742 l_order_position number;
3743 l_order_counter number;
3744 l_group_position number;
3745 l_group_counter number;
3746 l_found varchar2(1);
3747 l_master_type varchar2(80);
3748 l_master_type_id number;
3749 l_source_object_name varchar2(80);
3750 l_source_object_pk_field varchar2(80);
3751 l_child_types AMS_LISTGENERATION_PKG.child_type;
3752 l_select_condition varchar2(2000);
3753 l_select_add_condition varchar2(2000);
3754 l_msg_data VARCHAR2(2000);
3755 l_msg_count number;
3756 l_sql_2 DBMS_SQL.VARCHAR2S;
3757 l_sql_string_final varchar2(4000);
3758 j number := 1;
3759 l_no_of_chunks number;
3760 l_final_big_sql VARCHAR2(32767);
3761 l_const_sql VARCHAR2(32767);
3762 BEGIN
3763
3764 --write_to_act_log('AMS_ListGeneration_PKG.Get Comp sql:');
3765 ams_cell_pvt.get_comp_sql(
3766 p_api_version => 1.0,
3767 p_init_msg_list => FND_API.g_false,
3768 p_validation_level => FND_API.g_valid_level_full,
3769 x_return_status => x_return_status,
3770 x_msg_count => x_msg_count ,
3771 x_msg_data =>x_msg_data,
3772 p_cell_id => p_incl_object_id ,
3773 p_party_id_only => FND_API.g_false,
3774 x_sql_tbl => l_sql_2
3775 );
3776 --write_to_act_log('AMS_ListGeneration_PKG.After Comp sql:');
3777
3778 l_sql_string_final := '';
3779 for i in 1 .. l_sql_2.last
3780 loop
3781 --write_to_act_log(l_sql_2(i));
3782 l_sql_string_final := l_sql_string_final || l_sql_2(i);
3783 if length(l_sql_string_final) > 2000 then
3784 l_sql_string(j) := substrb(l_sql_string_final,1,2000);
3785 l_sql_string_final := substrb(l_sql_string_final,2001 ,2000);
3786 j := j+1;
3787 end if;
3788 end loop;
3789 l_sql_string(j) := substrb(l_sql_string_final,1,2000);
3790 if length(l_sql_string_final) > 2000 then
3791 j := j+1;
3792 l_sql_string(j) := substrb(l_sql_string_final,2001 ,2000);
3793 end if;
3794
3795 --write_to_act_log('AMS_ListGeneration_PKG.Process all sql:');
3796 --write_to_act_log('l_sql_string'||l_sql_string.last);
3797 for i in 1 .. l_sql_string.last
3798 loop
3799 l_no_of_chunks := ceil(length(l_sql_string(i))/2000 );
3800 --write_to_act_log('l_sql_string chunks'||l_no_of_chunks);
3801 for j in 1 ..l_no_of_chunks
3802 loop
3803 --WRITE_TO_ACT_LOG(i || 'j'||j);
3804 --WRITE_TO_ACT_LOG(substrb(l_sql_string(i),(2000*j) - 1999,2000),'LIST',g_list_header_id);
3805 null;
3806 end loop;
3807 end loop;
3808
3809 process_all_sql(
3810 p_action_used_by_id => p_action_used_by_id,
3811 p_act_list_header_id => p_act_list_header_id ,
3812 p_incl_object_id => p_incl_object_id,
3813 p_sql_string => l_sql_string ,
3814 p_primary_key => null,
3815 p_source_object_name => null,
3816 x_msg_count => x_msg_count ,
3817 x_msg_data => x_msg_data ,
3818 x_return_status => x_return_status ,
3819 x_std_sql => x_std_sql,
3820 x_include_sql => x_include_sql
3821 );
3822
3823 l_final_big_sql := x_include_sql || x_std_sql ;
3824 l_const_sql := ' minus '||
3825 ' select list_entry_source_system_id ' ||
3826 ' from ams_list_entries ' ||
3827 ' where list_header_id = ' || g_list_header_id ;
3828 l_final_big_sql := l_final_big_sql || l_const_sql || ' )';
3829 --write_to_act_log('l_final_big_sql',null,null);
3830 l_no_of_chunks := ceil(length(l_final_big_sql)/2000 );
3831 for j in 1 ..l_no_of_chunks
3832 loop
3833 --WRITE_TO_ACT_LOG(substrb(l_final_big_sql,(2000*j) - 1999,2000),'LIST',g_list_header_id);
3834 null;
3835 end loop;
3836 -- write_to_act_log('x_include_sql');
3837 l_no_of_chunks := ceil(nvl(length(x_include_sql)/2000,0) );
3838 -- write_to_act_log(l_no_of_chunks,null,null);
3839 for j in 1 ..l_no_of_chunks
3840 loop
3841 null;
3842 -- WRITE_TO_ACT_LOG(substrb(x_include_sql,(2000*j) - 1999,2000));
3843 --write_to_act_log('AMS_ListGeneration_PKG.Process_cell:');
3844 end loop;
3845 --write_to_act_log('AMS_ListGeneration_PKG.Process_cell:');
3846 EXECUTE IMMEDIATE l_final_big_sql;
3847
3848 END process_cell ;
3849 --
3850 PROCEDURE copy_target_group
3851 (p_from_schedule_id in number,
3852 p_to_schedule_id in number,
3853 p_list_used_by in VARCHAR2 DEFAULT 'CSCH',
3854 x_msg_count OUT NOCOPY number,
3855 x_msg_data OUT NOCOPY varchar2,
3856 x_return_status IN OUT NOCOPY VARCHAR2
3857 ) is
3858 BEGIN
3859
3860 copy_target_group
3861 (p_from_schedule_id ,
3862 p_to_schedule_id ,
3863 p_list_used_by ,
3864 FND_API.G_FALSE,
3865 x_msg_count ,
3866 x_msg_data ,
3867 x_return_status );
3868
3869 END copy_target_group;
3870
3871 -- created vbhandar 04/20 to distinguish between repeat and copy target group scenarios
3872 PROCEDURE copy_target_group
3873 (p_from_schedule_id in number,
3874 p_to_schedule_id in number,
3875 p_list_used_by in VARCHAR2 DEFAULT 'CSCH',
3876 p_repeat_flag in VARCHAR2 ,
3877 x_msg_count OUT NOCOPY number,
3878 x_msg_data OUT NOCOPY varchar2,
3879 x_return_status IN OUT NOCOPY VARCHAR2
3880 ) is
3881 l_list_type varchar2(30) := 'TARGET';
3882 l_action_id number;
3883 l_exclude_action_rec ams_listaction_pvt.action_rec_type;
3884 l_api_name CONSTANT VARCHAR2(30) := 'copy_target_group';
3885
3886 --vbhandar modified 05-13-2004 TO fix bug 3621786 added apply traffic cop to select clause
3887 cursor c_get_target_group is
3888 select b.list_name, b.list_header_id, a.list_used_by, a.list_used_by_id,
3889 a.list_act_type, a.group_code, a.list_action_type, a.order_number,b.query_template_id,b.purpose_code,b.APPLY_TRAFFIC_COP,
3890 -- ckapoor R12 copy tg enhancements
3891 b.CTRL_CONF_LEVEL, b.CTRL_REQ_RESP_RATE, b.CTRL_LIMIT_OF_ERROR, b.STATUS_CODE_OLD, b.CTRL_CONC_JOB_ID, b.CTRL_STATUS_CODE, b.CTRL_GEN_MODE, b.APPLY_SUPPRESSION_FLAG,
3892 -- end ckapoor R12 copy tg enhancements
3893 c.schedule_name || ' - ' || c.source_code list_name1
3894 ,b.main_random_pct_row_selection,b.row_selection_type,b.no_of_rows_max_requested --rmbhanda bug#4667513
3895 from ams_act_lists a, ams_list_headers_vl b, ams_campaign_schedules_vl c
3896 where a.list_used_by = p_list_used_by
3897 and a.list_used_by_id = p_from_schedule_id
3898 and a.list_act_type = l_list_type
3899 and a.list_header_id = b.list_header_id
3900 and c.schedule_id = p_from_schedule_id
3901 and c.schedule_id = a.list_used_by_id ;
3902
3903 cursor c_get_target_group_comp is
3904 select a.list_header_id, a.list_used_by, a.list_used_by_id,
3905 a.list_act_type, a.group_code, a.list_action_type, a.order_number
3906 from ams_act_lists a
3907 where a.list_used_by = p_list_used_by
3908 and a.list_used_by_id = p_from_schedule_id
3909 and a.list_act_type <> l_list_type
3910 order by order_number ;
3911
3912 cursor c_get_schedule_details is
3913 select
3914 a.tgrp_exclude_prev_flag
3915 from ams_campaign_schedules_vl a
3916 where a.schedule_id = p_from_schedule_id ;
3917 l_target_group_rec c_get_target_group%rowtype;
3918
3919 l_listheader_rec ams_listheader_pvt.list_header_rec_type;
3920 l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
3921 l_act_list_rec AMS_ACT_LIST_PVT.act_list_rec_type ;
3922 l_last_order_number number ;
3923 l_exclude_flag varchar2(1);
3924
3925 l_return_status VARCHAR2(1);
3926 l_msg_count number;
3927 l_list_header_id number;
3928 l_act_list_header_id number;
3929 l_msg_data VARCHAR2(2000);
3930 j number := 0;
3931
3932
3933 l_old_list_header_id NUMBER;
3934 l_query_temp_id NUMBER;
3935 l_old_query_id NUMBER;
3936 l_query_id NUMBER;
3937 l_templete_type VARCHAR2(30);
3938 l_old_templ_inst_id NUMBER;
3939 l_new_templ_inst_id NUMBER;
3940 l_target_group_found BOOLEAN := TRUE ;
3941 l_purpose_code varchar2(120);
3942 l_list_rule_id NUMBER;
3943
3944 cursor c_new_schedule_details is
3945 select c.SCHEDULE_ID,c.SOURCE_CODE,c.SCHEDULE_NAME,c.schedule_name || ' - ' || c.source_code list_name
3946 from ams_campaign_schedules_vl c
3947 where c.schedule_id = p_to_schedule_id;
3948
3949 CURSOR c_get_query_templete_type(p_templete_id IN NUMBER) is
3950 SELECT template_type
3951 FROM ams_query_template_all
3952 WHERE template_id=p_templete_id;
3953
3954 l_sched_rec c_new_schedule_details%rowtype;
3955
3956 CURSOR c_list_size (p_list_header_id IN NUMBER) is
3957 SELECT no_of_rows_active
3958 FROM ams_list_headers_all
3959 WHERE list_header_id = p_list_header_id;
3960
3961 -- ckapoor R12 dedupe rule copy
3962
3963 CURSOR c_dedupe_rule(p_list_header_id IN NUMBER) is
3964 select list_rule_id from ams_list_rule_usages
3965 where list_header_id = p_list_header_id;
3966 -- ckapoor end R12 dedupe rule copy
3967
3968 l_excluded_list_size NUMBER;
3969
3970 begin
3971 SAVEPOINT copy_target_group_pvt;
3972
3973 IF (AMS_DEBUG_HIGH_ON) THEN
3974 AMS_Utility_PVT.debug_message('Private API:AMS_Act_List_PVT.copy_target_group '||p_from_schedule_id||':::::'||p_to_schedule_id);
3975 END IF;
3976
3977 open c_get_target_group;
3978 fetch c_get_target_group into l_target_group_rec ;
3979 IF c_get_target_group%NOTFOUND THEN
3980 l_target_group_found := FALSE ;
3981 END IF;
3982 close c_get_target_group;
3983
3984 IF AMS_DEBUG_HIGH_ON THEN
3985 IF l_target_group_found then
3986 AMS_Utility_PVT.debug_message('Private API: copy_target_group target group found');
3987 ELSE
3988 AMS_Utility_PVT.debug_message('Private API: copy_target_group target group not found');
3989 END IF;
3990 END IF;
3991
3992
3993 IF l_target_group_found THEN
3994
3995 OPEN c_new_schedule_details;
3996 FETCH c_new_schedule_details INTO l_sched_rec;
3997 CLOSE c_new_schedule_details;
3998
3999 ams_listheader_pvt.init_listheader_rec(l_tmp_listheader_rec);
4000 l_tmp_listheader_rec.list_header_id := l_target_group_rec.list_header_id;
4001 l_old_list_header_id:=l_target_group_rec.list_header_id;
4002 l_query_temp_id := l_target_group_rec.query_template_id;
4003 l_purpose_code := l_target_group_rec.purpose_code;
4004
4005 --vbhandar modified 05-13-2004 TO fix bug 3621786
4006 l_tmp_listheader_rec.APPLY_TRAFFIC_COP := l_target_group_rec.APPLY_TRAFFIC_COP;
4007
4008
4009 ams_listheader_pvt.complete_listheader_rec
4010 (p_listheader_rec =>l_tmp_listheader_rec,
4011 x_complete_rec =>l_listheader_rec);
4012 l_listheader_rec.list_header_id := fnd_api.g_miss_num;
4013 l_listheader_rec.list_name := l_sched_rec.list_name; -- Kiran changed to l_sched_rec.list_name
4014 --l_listheader_rec.list_name := l_listheader_rec.list_name || '_'||
4015 --p_to_schedule_id ;
4016 -- l_listheader_rec.NO_OF_ROWS_DUPLICATES := 0;
4017 --l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED := 0;
4018 --l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED := 0;
4019 l_listheader_rec.request_id := null;
4020 l_listheader_rec.status_code:= null;
4021 l_listheader_rec.status_date:= null;
4022 l_listheader_rec.repeat_exclude_type:= null;
4023 -- l_listheader_rec.row_selection_type:= null; rmbhanda bug#4667513
4024 l_listheader_rec.row_selection_type:= l_target_group_rec.row_selection_type; --rmbhanda bug#4667513
4025 l_listheader_rec.dedupe_during_generation_flag:= null;
4026 l_listheader_rec.last_generation_success_flag:= null;
4027 l_listheader_rec.forecasted_start_date:= null;
4028 l_listheader_rec.forecasted_end_date:= null;
4029 l_listheader_rec.actual_end_date:= null;
4030 l_listheader_rec.sent_out_date:= null;
4031 l_listheader_rec.last_dedupe_date:= null;
4032 l_listheader_rec.last_deduped_by_user_id:= null;
4033 l_listheader_rec.workflow_item_key:= null;
4034 l_listheader_rec.no_of_rows_duplicates:= null;
4035 l_listheader_rec.no_of_rows_min_requested:= null;
4036 --l_listheader_rec.no_of_rows_max_requested:= null; rmbhanda bug#4667513
4037 l_listheader_rec.no_of_rows_max_requested:= l_target_group_rec.no_of_rows_max_requested; --rmbhanda bug#4667513
4038 l_listheader_rec.main_random_pct_row_selection:=l_target_group_rec.main_random_pct_row_selection; --rmbhanda bug#4667513
4039 l_listheader_rec.no_of_rows_in_list:= null;
4040 l_listheader_rec.no_of_rows_in_ctrl_group:= null;
4041 l_listheader_rec.no_of_rows_active:= null;
4042 l_listheader_rec.no_of_rows_inactive:= null;
4043 l_listheader_rec.no_of_rows_manually_entered:= null;
4044 l_listheader_rec.no_of_rows_do_not_call:= null;
4045 l_listheader_rec.no_of_rows_do_not_mail:= null;
4046 l_listheader_rec.no_of_rows_random:= null;
4047 l_listheader_rec.main_gen_start_time:= null;
4048 l_listheader_rec.main_gen_end_time:= null;
4049 l_listheader_rec.archived_by:= null;
4050 l_listheader_rec.archived_date:= null;
4051 l_listheader_rec.sent_out_date :=null;
4052 l_listheader_rec.list_used_by_id :=p_to_schedule_id;
4053 l_listheader_rec.purpose_code := l_purpose_code;
4054
4055 --l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL :=0;
4056 --l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL :=0;
4057 --l_listheader_rec.NO_OF_ROWS_RANDOM :=0;
4058 --vbhandar uncommented 04/02/2004 to fix bug 3550623
4059 l_listheader_rec.NO_OF_ROWS_PREV_CONTACTED :=0;
4060
4061 -- ckapoor R12 copy target group enhancements
4062
4063 l_listheader_rec.CTRL_CONC_JOB_Id := null;
4064 l_listheader_rec.ctrl_status_code := 'DRAFT';
4065 l_listheader_rec.status_code_old := null;
4066
4067
4068 -- ckapoor R12 copy
4069 AMS_ListHeader_PVT.Create_Listheader
4070 ( p_api_version => 1.0,
4071 p_init_msg_list => FND_API.g_false,
4072 p_commit => FND_API.g_false,
4073 p_validation_level => FND_API.g_valid_level_full,
4074 x_return_status => l_return_status,
4075 x_msg_count => l_msg_count,
4076 x_msg_data => l_msg_data,
4077 p_listheader_rec => l_listheader_rec,
4078 x_listheader_id => l_list_header_id
4079 );
4080 UPDATE ams_list_headers_all SET query_template_id = l_query_temp_id
4081 WHERE list_header_id = l_list_header_id;
4082 --for i in 1 .. l_msg_count loop
4083 --FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
4084 --FND_API.G_FALSE,
4085 --l_msg_data,
4086 --l_msg_count);
4087 --end loop;
4088 IF l_return_status = FND_API.g_ret_sts_error THEN
4089 RAISE FND_API.g_exc_error;
4090 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4091 RAISE FND_API.g_exc_unexpected_error;
4092 END IF;
4093
4094 IF (AMS_DEBUG_HIGH_ON) THEN
4095 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done creating header '||l_list_header_id);
4096 END IF;
4097
4098 copy_selections
4099 ( p_old_header_id => l_old_list_header_id,
4100 p_new_header_id => l_list_header_id,
4101 p_list_name => l_listheader_rec.list_name,
4102 x_msg_count => l_msg_count,
4103 x_msg_data => l_msg_data,
4104 x_return_status => l_return_status,
4105 x_query_id =>l_query_id
4106 );
4107
4108 IF l_return_status = FND_API.g_ret_sts_error THEN
4109 RAISE FND_API.g_exc_error;
4110 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4111 RAISE FND_API.g_exc_unexpected_error;
4112 END IF;
4113
4114 IF (AMS_DEBUG_HIGH_ON) THEN
4115 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_selections '||FND_API.G_VALID_LEVEL_FULL);
4116 END IF;
4117
4118
4119 IF l_query_temp_id IS NOT NULL THEN
4120
4121 IF (AMS_DEBUG_HIGH_ON) THEN
4122 AMS_Utility_PVT.debug_message('Private API: copy_target_group, l_query_temp_id : '||l_query_temp_id);
4123 END IF;
4124
4125 OPEN c_get_query_templete_type(l_query_temp_id);
4126 FETCH c_get_query_templete_type INTO l_templete_type;
4127 CLOSE c_get_query_templete_type;
4128
4129 IF (AMS_DEBUG_HIGH_ON) THEN
4130 AMS_Utility_PVT.debug_message('Private API: copy_target_group, template_type'||l_templete_type);
4131 END IF;
4132
4133 IF l_templete_type = 'STANDARD' THEN
4134 copy_template_instance
4135 ( p_query_templ_id => l_query_temp_id,
4136 p_old_header_id => l_old_list_header_id,
4137 p_new_header_id => l_list_header_id,
4138 x_msg_count => l_msg_count,
4139 x_msg_data => l_msg_data,
4140 x_return_status => l_return_status,
4141 x_old_templ_inst_id => l_old_templ_inst_id,
4142 x_new_templ_inst_id => l_new_templ_inst_id
4143 );
4144
4145 IF l_return_status = FND_API.g_ret_sts_error THEN
4146 RAISE FND_API.g_exc_error;
4147 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4148 RAISE FND_API.g_exc_unexpected_error;
4149 END IF;
4150
4151 IF (AMS_DEBUG_HIGH_ON) THEN
4152 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_template_instance');
4153 END IF;
4154
4155 IF (AMS_DEBUG_HIGH_ON) THEN
4156 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_conditions' );
4157 END IF;
4158
4159 END IF;
4160
4161 END IF; --l_query_temp_id IS NOT NULL
4162
4163 -- ckapoor R12 copy dedupe rule
4164
4165 -- cursor to retrieve the rule id
4166
4167 OPEN c_dedupe_rule(l_old_list_header_id);
4168 FETCH c_dedupe_rule INTO l_list_rule_id;
4169 CLOSE c_dedupe_rule;
4170
4171 -- insert a new row in ams_list_rule_usages
4172
4173 if (l_list_rule_id is not null) then
4174
4175 insert into ams_list_rule_usages
4176 (LIST_RULE_USAGE_ID, LIST_HEADER_ID, LIST_RULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
4177 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, ACTIVE_FROM_DATE,
4178 ACTIVE_TO_DATE, PRIORITY, SECURITY_GROUP_ID)
4179 values (
4180 ams_list_rule_usages_s.nextval,
4181 l_list_header_id,
4182 l_list_rule_id,
4183 sysdate,
4184 FND_GLOBAL.User_Id,
4185 sysdate,
4186 FND_GLOBAL.User_Id,
4187 FND_GLOBAL.Conc_Login_Id,
4188 1,
4189 sysdate,
4190 null,
4191 null,
4192 null
4193
4194 );
4195
4196 end if;
4197
4198
4199
4200
4201 -- end ckapoor R12 copy dedupe rule
4202
4203 l_act_list_rec.list_header_id := l_list_header_id;
4204 l_act_list_rec.list_used_by := p_list_used_by ;
4205 l_act_list_rec.list_used_by_id := p_to_schedule_id ;
4206 l_act_list_rec.list_act_type := 'TARGET';
4207
4208 AMS_ACT_LIST_PVT.Create_Act_List(
4209 p_api_version_number => 1.0,
4210 p_init_msg_list => FND_API.g_false,
4211 p_commit => FND_API.g_false,
4212 p_validation_level => FND_API.g_valid_level_full,
4213 x_return_status => l_return_status,
4214 x_msg_count => l_msg_count,
4215 x_msg_data => l_msg_data,
4216 p_act_list_rec => l_act_list_rec ,
4217 x_act_list_header_id => l_act_list_header_id
4218 ) ;
4219
4220 IF l_return_status = FND_API.g_ret_sts_error THEN
4221 RAISE FND_API.g_exc_error;
4222 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4223 RAISE FND_API.g_exc_unexpected_error;
4224 END IF;
4225
4226
4227 IF (AMS_DEBUG_HIGH_ON) THEN
4228 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done Create_Act_List For Target Group ');
4229 END IF;
4230
4231
4232 for l_target_group_rec in c_get_target_group_comp
4233 loop
4234 IF (AMS_DEBUG_HIGH_ON) THEN
4235 AMS_Utility_PVT.debug_message('Private API: copy_target_group, call Create_Act_List For Target Group Components ');
4236 END IF;
4237 j := j + 1;
4238 l_act_list_rec.list_used_by := l_target_group_rec.list_used_by;
4239 l_act_list_rec.list_used_by_id := p_to_schedule_id;
4240 l_act_list_rec.list_act_type := l_target_group_rec.list_act_type;
4241 l_act_list_rec.list_action_type := l_target_group_rec.list_action_type ;
4242 l_act_list_rec.order_number := l_target_group_rec.order_number ;
4243 l_act_list_rec.group_code := l_target_group_rec.group_code ;
4244 IF l_act_list_rec.list_act_type= 'SQL' AND l_templete_type = 'PARAMETERIZED' THEN
4245 l_act_list_rec.list_header_id := l_query_id;
4246 ELSIF l_act_list_rec.list_act_type= 'SQL' AND l_templete_type = 'STANDARD' THEN
4247 l_act_list_rec.list_header_id := l_query_id;
4248 ELSE
4249 l_act_list_rec.list_header_id := l_target_group_rec.list_header_id ;
4250 END IF;
4251 l_last_order_number := l_target_group_rec.order_number ;
4252
4253 IF (AMS_DEBUG_HIGH_ON) THEN
4254 AMS_Utility_PVT.debug_message('l_query_id ' || l_query_id );
4255 AMS_Utility_PVT.debug_message('l_templete_type ' || l_templete_type );
4256 AMS_Utility_PVT.debug_message('l_act_list_rec.list_used_by ' || l_act_list_rec.list_used_by );
4257 AMS_Utility_PVT.debug_message('l_act_list_rec.list_used_by_id ' || l_act_list_rec.list_used_by_id );
4258 AMS_Utility_PVT.debug_message('l_act_list_rec.list_act_type ' || l_act_list_rec.list_act_type );
4259 AMS_Utility_PVT.debug_message('l_act_list_rec.list_action_type ' ||l_act_list_rec.list_action_type );
4260 AMS_Utility_PVT.debug_message('l_act_list_rec.order_number ' || l_act_list_rec.order_number );
4261 AMS_Utility_PVT.debug_message('l_act_list_rec.list_header_id ' || l_act_list_rec.list_header_id );
4262 END IF;
4263
4264 AMS_ACT_LIST_PVT.Create_Act_List(
4265 p_api_version_number => 1.0,
4266 p_init_msg_list => FND_API.g_false,
4267 p_commit => FND_API.g_false,
4268 p_validation_level => FND_API.G_VALID_LEVEL_FULL, --here
4269 x_return_status => l_return_status,
4270 x_msg_count => l_msg_count,
4271 x_msg_data => l_msg_data,
4272 p_act_list_rec => l_act_list_rec ,
4273 x_act_list_header_id => l_act_list_header_id
4274 ) ;
4275
4276 IF l_return_status = FND_API.g_ret_sts_error THEN
4277 RAISE FND_API.g_exc_error;
4278 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4279 RAISE FND_API.g_exc_unexpected_error;
4280 END IF;
4281
4282 IF (AMS_DEBUG_HIGH_ON) THEN
4283 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done second Create_Act_List');
4284 END IF;
4285
4286 if j = 1 then
4287 --bug 4623994
4288 /*for i in 1 .. l_msg_count loop
4289 FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
4290 FND_API.G_FALSE,
4291 l_msg_data,
4292 l_msg_count);
4293 end loop;*/
4294 null;
4295 end if;
4296 end loop;
4297 IF p_repeat_flag = FND_API.G_TRUE then
4298
4299 open c_get_schedule_details;
4300 fetch c_get_schedule_details into l_exclude_flag ;
4301 close c_get_schedule_details ;
4302 if l_exclude_flag = 'Y' then
4303 open c_get_target_group;
4304 fetch c_get_target_group into l_target_group_rec ;
4305 close c_get_target_group;
4306 l_act_list_rec.list_header_id := l_target_group_rec.list_header_id;
4307 l_act_list_rec.list_used_by := l_target_group_rec.list_used_by;
4308 l_act_list_rec.list_used_by_id := p_to_schedule_id;
4309 l_act_list_rec.list_act_type := 'LIST';
4310 l_act_list_rec.list_action_type := 'EXCLUDE' ;
4311 l_act_list_rec.order_number := l_last_order_number +5 ;
4312 l_act_list_rec.group_code := l_target_group_rec.group_code ;
4313 AMS_ACT_LIST_PVT.Create_Act_List(
4314 p_api_version_number => 1.0,
4315 p_init_msg_list => FND_API.g_false,
4316 p_commit => FND_API.g_false,
4317 p_validation_level => FND_API.g_valid_level_full,
4318 x_return_status => l_return_status,
4319 x_msg_count => l_msg_count,
4320 x_msg_data => l_msg_data,
4321 p_act_list_rec => l_act_list_rec ,
4322 x_act_list_header_id => l_act_list_header_id
4323 ) ;
4324 --bug 4623994
4325 /*for i in 1 .. l_msg_count loop
4326 FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
4327 FND_API.G_FALSE,
4328 l_msg_data,
4329 l_msg_count);
4330 end loop;*/
4331
4332 AMS_ListAction_PVT.init_action_rec(l_exclude_action_rec);
4333
4334 --vbhandar added to fix bug 3595605
4335 OPEN c_list_size(l_act_list_rec.list_header_id);
4336 FETCH c_list_size INTO l_excluded_list_size;
4337 CLOSE c_list_size;
4338
4339 IF (AMS_DEBUG_HIGH_ON) THEN
4340 AMS_Utility_PVT.debug_message('Private API: copy_target_group, excluded list size '|| l_excluded_list_size);
4341 END IF;
4342 --vbhandar added to fix bug 3595605
4343
4344 l_exclude_action_rec.list_select_action_id := NULL;
4345 l_exclude_action_rec.order_number := l_act_list_rec.order_number;
4346 l_exclude_action_rec.list_action_type := l_act_list_rec.list_action_type;
4347 l_exclude_action_rec.arc_incl_object_from := 'LIST';
4348 l_exclude_action_rec.arc_action_used_by := 'LIST';
4349 l_exclude_action_rec.action_used_by_id := l_list_header_id;
4350 l_exclude_action_rec.rank := l_act_list_rec.order_number;
4351 l_exclude_action_rec.incl_object_id := l_act_list_rec.list_header_id ;
4352 l_exclude_action_rec.distribution_pct := NULL;
4353 l_exclude_action_rec.no_of_rows_available := l_excluded_list_size; --NULL; vbhandar added to fix bug 3595605
4354 l_exclude_action_rec.no_of_rows_requested := NULL;
4355 l_exclude_action_rec.no_of_rows_used := NULL;
4356 l_exclude_action_rec.description := NULL;
4357 l_exclude_action_rec.no_of_rows_targeted := NULL;
4358
4359 IF (AMS_DEBUG_HIGH_ON) THEN
4360 AMS_Utility_PVT.debug_message('Private API: copy_target_group, CAlling Create List Action in Copy tG');
4361 END IF;
4362
4363 AMS_ListAction_PVT.Create_ListAction
4364 ( 1.0,
4365 FND_API.g_false,
4366 FND_API.g_false ,
4367 FND_API.G_VALID_LEVEL_FULL, --here
4368 l_return_status ,
4369 l_msg_count,
4370 l_msg_data,
4371 l_exclude_action_rec,
4372 l_action_id
4373 ) ;
4374
4375
4376 IF (AMS_DEBUG_HIGH_ON) THEN
4377 AMS_Utility_PVT.debug_message('Private API: copy_target_group, l_returnStatus after create list' ||l_return_status);
4378 END IF;
4379
4380
4381
4382
4383 IF l_return_status = FND_API.g_ret_sts_error THEN
4384 RAISE FND_API.g_exc_error;
4385 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4386 RAISE FND_API.g_exc_unexpected_error;
4387 END IF;
4388 --bug 4623994
4389 /*for i in 1 .. l_msg_count loop
4390 FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
4391 FND_API.G_FALSE,
4392 l_msg_data,
4393 l_msg_count);
4394 end loop;*/
4395 end if;
4396 END IF; -- p_repeat_flag = Y
4397
4398 IF (AMS_DEBUG_HIGH_ON) THEN
4399 AMS_Utility_PVT.debug_message('Private API: copy_target_group,finished');
4400 END IF;
4401 --RAISE FND_API.G_EXC_ERROR;
4402 END IF ; -- if l_target_group_found
4403 EXCEPTION
4404
4405 WHEN AMS_Utility_PVT.resource_locked THEN
4406 x_return_status := FND_API.g_ret_sts_error;
4407 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
4408 WHEN FND_API.G_EXC_ERROR THEN
4409 ROLLBACK TO copy_target_group_pvt;
4410 x_return_status := FND_API.G_RET_STS_ERROR;
4411 -- Standard call to get message count and if count=1, get the message
4412 FND_MSG_PUB.Count_And_Get (
4413 p_encoded => FND_API.G_FALSE,
4414 p_count => x_msg_count,
4415 p_data => x_msg_data
4416 );
4417
4418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4419 ROLLBACK TO copy_target_group_pvt;
4420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4421 -- Standard call to get message count and if count=1, get the message
4422 FND_MSG_PUB.Count_And_Get (
4423 p_encoded => FND_API.G_FALSE,
4424 p_count => x_msg_count,
4425 p_data => x_msg_data
4426 );
4427
4428 WHEN OTHERS THEN
4429 ROLLBACK TO copy_target_group_pvt;
4430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4431 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4432 THEN
4433 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4434 END IF;
4435 -- Standard call to get message count and if count=1, get the message
4436 FND_MSG_PUB.Count_And_Get (
4437 p_encoded => FND_API.G_FALSE,
4438 p_count => x_msg_count,
4439 p_data => x_msg_data
4440 );
4441 END copy_target_group;
4442
4443
4444 PROCEDURE copy_selections
4445 (p_old_header_id in number,
4446 p_new_header_id in number,
4447 p_list_name IN varchar2,
4448 x_msg_count OUT NOCOPY number,
4449 x_msg_data OUT NOCOPY varchar2,
4450 x_return_status IN OUT NOCOPY VARCHAR2,
4451 x_query_id OUT NOCOPY NUMBER
4452 ) is
4453
4454
4455 CURSOR c_select_actions(p_header_id IN number) IS
4456 SELECT LIST_SELECT_ACTION_ID
4457 FROM ams_list_select_actions WHERE action_used_by_id=p_header_id;
4458
4459 CURSOR fetch_list_select_actions(p_header_id NUMBER) IS
4460 SELECT incl_object_id,rank,order_number,description,list_action_type
4461 ,no_of_rows_requested,no_of_rows_available,no_of_rows_used
4462 ,distribution_pct,no_of_rows_targeted,incl_object_name,arc_incl_object_from,arc_action_used_by
4463 FROM ams_list_select_actions
4464 WHERE action_used_by_id =p_header_id
4465 order by order_number;
4466
4467 l_return_status VARCHAR2(1);
4468 l_msg_count number;
4469 l_action_id number;
4470 l_act_list_header_id number;
4471 l_msg_data VARCHAR2(2000);
4472 l_api_version CONSTANT NUMBER := 1.0;
4473 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
4474 l_list_select_action_id NUMBER;
4475 l_listaction_rec ams_listaction_pvt.action_rec_type;
4476 l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
4477 l_action_rec AMS_ListAction_PVT.action_rec_type;
4478
4479 l_list_name VARCHAR (300);
4480 l_new_query_id NUMBER;
4481 l_old_list_header_id NUMBER;
4482 BEGIN
4483
4484 IF (AMS_DEBUG_HIGH_ON) THEN
4485 AMS_Utility_PVT.debug_message('Private API: copy_selections: Begin');
4486 END IF;
4487
4488 OPEN c_select_actions(p_old_header_id);
4489 FETCH c_select_actions INTO l_list_select_action_id;
4490
4491 IF (AMS_DEBUG_HIGH_ON) THEN
4492 AMS_Utility_PVT.debug_message('copy_selections old '||p_old_header_id);
4493 AMS_Utility_PVT.debug_message('copy_selections new '||p_new_header_id);
4494 END IF;
4495
4496 FOR l_list_actions_rec IN fetch_list_select_actions(p_old_header_id)
4497 LOOP
4498
4499 IF (AMS_DEBUG_HIGH_ON) THEN
4500 AMS_Utility_PVT.debug_message('********************copy_selections enter loop');
4501 END IF;
4502
4503 AMS_ListAction_PVT.init_action_rec(l_action_rec);
4504 l_action_rec.list_select_action_id := NULL;
4505 l_action_rec.order_number := l_list_actions_rec.order_number;
4506 l_action_rec.list_action_type := l_list_actions_rec.list_action_type;
4507 -- l_action_rec.incl_object_name := l_list_actions_rec.incl_object_name;
4508 l_action_rec.arc_incl_object_from := l_list_actions_rec.arc_incl_object_from;
4509 l_action_rec.no_of_rows_available := l_list_actions_rec.no_of_rows_available;
4510 l_action_rec.no_of_rows_requested := l_list_actions_rec.no_of_rows_requested;
4511 l_action_rec.no_of_rows_used := l_list_actions_rec.no_of_rows_used;
4512 l_action_rec.distribution_pct := l_list_actions_rec.distribution_pct;
4513 l_action_rec.description := l_list_actions_rec.description;
4514 l_action_rec.arc_action_used_by := 'LIST';
4515 l_action_rec.action_used_by_id := p_new_header_id;
4516 l_action_rec.no_of_rows_targeted := l_list_actions_rec.no_of_rows_targeted;
4517 l_action_rec.rank := l_list_actions_rec.rank;
4518 l_action_rec.incl_object_id := l_list_actions_rec.incl_object_id;
4519
4520 AMS_Utility_PVT.debug_message('Action Rec Incl Object ID first ' || l_action_rec.incl_object_id);
4521 --start
4522 IF (AMS_DEBUG_HIGH_ON) THEN
4523 AMS_Utility_PVT.debug_message('Private API: copy_target_group, begin copy_list_queries 1');
4524 END IF;
4525
4526 IF l_list_actions_rec.arc_incl_object_from = 'SQL' THEN
4527 l_list_name := p_list_name;
4528 copy_list_queries
4529 ( p_old_header_id => l_old_list_header_id,
4530 p_new_header_id => p_new_header_id,
4531 p_list_name => l_list_name,
4532 p_old_query_id => l_list_actions_rec.incl_object_id,
4533 x_msg_count => l_msg_count,
4534 x_msg_data => l_msg_data,
4535 x_return_status => l_return_status,
4536 x_new_query_id => l_new_query_id
4537 );
4538 IF (AMS_DEBUG_HIGH_ON) THEN
4539 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_list_queries 1' || l_new_query_id || ':::');
4540 END IF;
4541
4542 AMS_Utility_PVT.debug_message('Action Rec Incl Object ID second ' || l_action_rec.incl_object_id);
4543 l_action_rec.incl_object_id := l_new_query_id;
4544 x_query_id := l_action_rec.incl_object_id;
4545 IF (AMS_DEBUG_HIGH_ON) THEN
4546 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_list_queries 2');
4547 END IF;
4548
4549
4550 copy_query_list_params
4551 ( p_old_query_id => l_list_actions_rec.incl_object_id,
4552 p_new_query_id => l_new_query_id,
4553 x_msg_count => l_msg_count,
4554 x_msg_data => l_msg_data,
4555 x_return_status => l_return_status
4556 );
4557
4558 IF l_return_status = FND_API.g_ret_sts_error THEN
4559 RAISE FND_API.g_exc_error;
4560 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4561 RAISE FND_API.g_exc_unexpected_error;
4562 END IF;
4563
4564 IF (AMS_DEBUG_HIGH_ON) THEN
4565 AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_query_list_params');
4566 END IF;
4567 END IF ;
4568 --end
4569
4570
4571 IF (AMS_DEBUG_HIGH_ON) THEN
4572 AMS_Utility_PVT.debug_message('CAlling Create List Action in Copy Selections');
4573 END IF;
4574
4575 AMS_ListAction_PVT.Create_ListAction
4576 ( l_api_version,
4577 l_init_msg_list,
4578 FND_API.g_false ,
4579 FND_API.G_VALID_LEVEL_FULL, --here
4580 l_return_status , -------------VBCHANGE--------------------
4581 l_msg_count,-------------VBCHANGE--------------------
4582 l_msg_data,-------------VBCHANGE--------------------
4583 l_action_rec,
4584 l_action_id
4585 ) ;
4586
4587 -------------VBCHANGE--------------------
4588 /* FOR i IN 1 .. l_msg_count LOOP
4589 l_msg_data := FND_MSG_PUB.get(i, FND_API.g_false);
4590 AMS_Utility_PVT.debug_message('(' || i || ') ' || l_msg_data);
4591 END LOOP;*/
4592
4593 IF (AMS_DEBUG_HIGH_ON) THEN
4594 AMS_Utility_PVT.debug_message('Return status from Create_ListAction'||l_return_status);
4595 END IF;
4596 IF l_return_status = FND_API.g_ret_sts_error THEN
4597 RAISE FND_API.g_exc_error;
4598 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4599 RAISE FND_API.g_exc_unexpected_error;
4600 END IF;
4601
4602 -------------VBCHANGE--------------------
4603 UPDATE ams_list_select_actions SET incl_object_name = l_list_actions_rec.incl_object_name
4604 WHERE list_select_action_id = l_action_id;
4605 -------------- x_query_id := l_action_rec.incl_object_id;------------VBCHANGE---------
4606 IF (AMS_DEBUG_HIGH_ON) THEN
4607 AMS_Utility_PVT.debug_message('copy_selections done creation');
4608 END IF;
4609
4610
4611 END LOOP;
4612
4613 END copy_selections;
4614
4615
4616 PROCEDURE copy_list_queries
4617 ( p_old_header_id IN NUMBER,
4618 p_new_header_id IN NUMBER,
4619 p_list_name IN varchar2,
4620 p_old_query_id IN NUMBER,
4621 x_msg_count OUT NOCOPY number,
4622 x_msg_data OUT NOCOPY varchar2,
4623 x_return_status IN OUT NOCOPY VARCHAR2,
4624 x_new_query_id OUT NOCOPY NUMBER
4625 ) is
4626
4627 CURSOR c_queries(p_query_id IN number) IS
4628 SELECT LIST_QUERY_ID
4629 FROM ams_list_queries_all WHERE list_query_id = p_query_id;
4630
4631 --sql_string column is obsolete bug 4604653
4632 CURSOR fetch_list_queries_all(p_query_id NUMBER) IS
4633 SELECT list_query_id,name,type,query,primary_key,source_object_name,act_list_query_used_by_id,arc_act_list_query_used_by,seed_flag,parameterized_flag,admin_flag,query_template_id,query_type
4634 FROM ams_list_queries_vl
4635 WHERE list_query_id = p_query_id;
4636
4637 /*
4638 CURSOR fetch_long_query_val(p_query_id NUMBER) IS
4639 SELECT query
4640 FROM ams_list_queries_all
4641 WHERE list_query_id = p_query_id;
4642 */
4643
4644 long_var LONG;
4645 l_queries_rec fetch_list_queries_all%rowtype;
4646 l_api_version CONSTANT NUMBER := 1.0;
4647 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
4648 l_tmplist_query_rec AMS_List_Query_PVT.list_query_rec_type;
4649 l_list_query_rec AMS_List_Query_PVT.list_query_rec_type;
4650
4651 l_return_status VARCHAR2(1);
4652 l_msg_count number;
4653 l_action_id number;
4654 l_act_list_header_id number;
4655 l_msg_data VARCHAR2(2000);
4656 l_old_list_query_id NUMBER;
4657 l_list_query_id NUMBER;
4658
4659 BEGIN
4660
4661 IF (AMS_DEBUG_HIGH_ON) THEN
4662 AMS_Utility_PVT.debug_message('copy_list_queries p_old_query_id = ' || p_old_query_id);
4663 END IF;
4664
4665 OPEN fetch_list_queries_all(p_old_query_id);
4666 FETCH fetch_list_queries_all INTO l_queries_rec;
4667
4668 IF fetch_list_queries_all%NOTFOUND THEN
4669 null;
4670 ELSE
4671 l_list_query_rec.name := p_list_name;
4672 l_list_query_rec.type := l_queries_rec.type;
4673 l_list_query_rec.sql_string:= l_queries_rec.query;
4674 --sql_string goes into query column via table handler pkg
4675
4676 l_list_query_rec.primary_key :=l_queries_rec.primary_key;
4677 l_list_query_rec.source_object_name := l_queries_rec.source_object_name;
4678
4679 l_list_query_rec.arc_act_list_query_used_by := NULL;
4680 l_list_query_rec.seed_flag :=l_queries_rec.seed_flag;
4681 l_list_query_rec.act_list_query_used_by_id := p_new_header_id;
4682
4683 l_list_query_rec.object_version_number :=1;
4684
4685
4686 AMS_List_Query_PVT.Create_List_Query(
4687 p_api_version_number => 1.0,
4688 p_init_msg_list => FND_API.G_FALSE,
4689 p_commit => FND_API.G_FALSE,
4690 p_validation_level => FND_API.G_VALID_LEVEL_FULL, --here
4691 x_return_status => x_return_status,
4692 x_msg_count => x_msg_count,
4693 x_msg_data => x_msg_data,
4694 p_list_query_rec => l_list_query_rec ,
4695 x_list_query_id => l_list_query_id
4696 );
4697 IF (AMS_DEBUG_HIGH_ON) THEN
4698 AMS_Utility_PVT.debug_message('copy_queries...l_list_query_id '||l_list_query_id );
4699 END IF;
4700
4701 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4702 RAISE FND_API.G_EXC_ERROR;
4703 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4705 END IF;
4706
4707 /*
4708 OPEN fetch_long_query_val(p_old_query_id);
4709 FETCH fetch_long_query_val INTO long_var;
4710 */
4711 UPDATE ams_list_queries_all
4712 SET --query = long_var,
4713 parameterized_flag = l_queries_rec.parameterized_flag,
4714 admin_flag = l_queries_rec.admin_flag,
4715 query_template_id = l_queries_rec.query_template_id,
4716 query_type = l_queries_rec.query_type,
4717 seed_flag = l_queries_rec.seed_flag,
4718 arc_act_list_query_used_by = l_queries_rec.arc_act_list_query_used_by
4719 WHERE list_query_id = l_list_query_id;
4720
4721 END IF;
4722 -- CLOSE fetch_list_queries_all;
4723
4724 x_new_query_id := l_list_query_id;
4725
4726 END copy_list_queries;
4727
4728 PROCEDURE copy_query_list_params
4729 (p_old_query_id in number,
4730 p_new_query_id in number,
4731 x_msg_count OUT NOCOPY number,
4732 x_msg_data OUT NOCOPY varchar2,
4733 x_return_status IN OUT NOCOPY VARCHAR2
4734 ) is
4735
4736 CURSOR fetch_list_query_params(p_query_id NUMBER) IS
4737 SELECT list_query_param_id, list_query_id, parameter_order, parameter_value, last_update_date, last_updated_by, creation_date,
4738 created_by, last_update_login, object_version_number, attb_lov_id, param_value_2, condition_value, parameter_name, display_name
4739 FROM AMS_LIST_QUERIES_PARAM_VL
4740 WHERE list_query_id =p_query_id;
4741
4742 CURSOR c_qlp is
4743 SELECT AMS_LIST_QUERIES_PARAM_s.NEXTVAL FROM dual;
4744
4745
4746 l_api_version CONSTANT NUMBER := 1.0;
4747 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
4748 l_sql VARCHAR2(5000);
4749 l_list_query_param_id NUMBER;
4750 l_return_status VARCHAR2(1);
4751 l_msg_count number;
4752 l_msg_data VARCHAR2(2000);
4753 BEGIN
4754 IF (AMS_DEBUG_HIGH_ON) THEN
4755 AMS_Utility_PVT.debug_message('copy_query_list_params '||p_old_query_id||':::'||p_new_query_id);
4756 END IF;
4757
4758 FOR l_query_param_rec IN fetch_list_query_params(p_old_query_id)
4759 LOOP
4760 OPEN c_qlp;
4761 FETCH c_qlp INTO l_list_query_param_id;
4762 CLOSE c_qlp;
4763
4764 IF (AMS_DEBUG_HIGH_ON) THEN
4765 AMS_Utility_PVT.debug_message('copy_query_list_params in loop' || l_list_query_param_id);
4766 END IF;
4767
4768 AMS_LIST_QUERIES_PARAM_PKG.INSERT_ROW (
4769 X_LIST_QUERY_PARAM_ID => l_list_query_param_id,
4770 X_ATTB_LOV_ID =>l_query_param_rec.attb_lov_id,
4771 X_PARAM_VALUE_2 => l_query_param_rec.param_value_2,
4772 X_CONDITION_VALUE => l_query_param_rec.condition_value,
4773 X_PARAMETER_NAME => l_query_param_rec.parameter_name,
4774 X_LIST_QUERY_ID =>p_new_query_id,
4775 X_PARAMETER_ORDER => l_query_param_rec.parameter_order,
4776 X_PARAMETER_VALUE =>l_query_param_rec.parameter_value,
4777 X_OBJECT_VERSION_NUMBER => 1,
4778 X_DISPLAY_NAME => l_query_param_rec.display_name,
4779 X_CREATION_DATE => SYSDATE,
4780 X_CREATED_BY => FND_GLOBAL.User_Id,
4781 X_LAST_UPDATE_DATE => SYSDATE,
4782 X_LAST_UPDATED_BY => FND_GLOBAL.User_Id,
4783 X_LAST_UPDATE_LOGIN =>FND_GLOBAL.CONC_LOGIN_ID
4784 );
4785
4786 IF (AMS_DEBUG_HIGH_ON) THEN
4787 AMS_Utility_PVT.debug_message('copy_query_list_params in loop' || l_list_query_param_id);
4788 END IF;
4789
4790 END LOOP;
4791 END copy_query_list_params;
4792
4793
4794 PROCEDURE copy_template_instance(
4795 p_query_templ_id in number,
4796 p_old_header_id in number,
4797 p_new_header_id in number,
4798 x_msg_count OUT NOCOPY number,
4799 x_msg_data OUT NOCOPY varchar2,
4800 x_return_status IN OUT NOCOPY VARCHAR2,
4801 x_old_templ_inst_id OUT NOCOPY number,
4802 x_new_templ_inst_id OUT NOCOPY number
4803 )is
4804 CURSOR c_templInst is
4805 SELECT ams_query_template_instance_s.NEXTVAL FROM dual;
4806
4807 CURSOR fetch_templ_instance(p_header_id NUMBER) IS
4808 SELECT template_instance_id, admin_indicator_flag, request_id, view_application_id, instance_used_by, instance_used_by_id
4809 FROM ams_query_template_instance
4810 where instance_used_by_id = p_header_id;
4811
4812
4813 l_api_version CONSTANT NUMBER := 1.0;
4814 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
4815 l_sql VARCHAR2(5000);
4816 l_templ_inst_id NUMBER;
4817 l_return_status VARCHAR2(1);
4818 l_msg_count number;
4819 l_msg_data VARCHAR2(2000);
4820
4821 BEGIN
4822
4823 IF (AMS_DEBUG_HIGH_ON) THEN
4824 AMS_Utility_PVT.debug_message('copy_template_instance '||p_old_header_id||':::'||p_new_header_id);
4825 END IF;
4826
4827 FOR l_templ_inst_rec IN fetch_templ_instance(p_old_header_id)
4828 LOOP
4829
4830 OPEN c_templInst;
4831 FETCH c_templInst INTO l_templ_inst_id;
4832 CLOSE c_templInst;
4833
4834 IF (AMS_DEBUG_HIGH_ON) THEN
4835 AMS_Utility_PVT.debug_message('copy_template_instance in loop '||l_templ_inst_id);
4836 END IF;
4837
4838 x_old_templ_inst_id := l_templ_inst_rec.template_instance_id;
4839 x_new_templ_inst_id := l_templ_inst_id;
4840
4841 AMS_QUERY_TEMP_INST_PKG.INSERT_ROW (
4842 X_TEMPLATE_INSTANCE_ID => l_templ_inst_id,
4843 X_TEMPLATE_ID => p_query_templ_id,
4844 X_ADMIN_INDICATOR_FLAG => l_templ_inst_rec.admin_indicator_flag,
4845 X_OBJECT_VERSION_NUMBER => 1,
4846 X_REQUEST_ID => NULL,
4847 X_VIEW_APPLICATION_ID => l_templ_inst_rec.view_application_id,
4848 X_INSTANCE_USED_BY => 'LIST',
4849 X_INSTANCE_USED_BY_ID => p_new_header_id ,
4850 X_CREATION_DATE => SYSDATE ,
4851 X_CREATED_BY => FND_GLOBAL.USER_ID,
4852 X_LAST_UPDATE_DATE => SYSDATE ,
4853 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
4854 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
4855 );
4856
4857 IF (AMS_DEBUG_HIGH_ON) THEN
4858 AMS_Utility_PVT.debug_message('copy_template_instance in loop done');
4859 END IF;
4860
4861 copy_conditions
4862 ( p_old_templ_inst_id => x_old_templ_inst_id,
4863 p_new_templ_inst_id => l_templ_inst_id,
4864 x_msg_count => l_msg_count,
4865 x_msg_data => l_msg_data,
4866 x_return_status => l_return_status
4867 );
4868
4869 END LOOP;
4870
4871 END copy_template_instance;
4872
4873 PROCEDURE copy_conditions(
4874 p_old_templ_inst_id in number,
4875 p_new_templ_inst_id in number,
4876 x_msg_count OUT NOCOPY number,
4877 x_msg_data OUT NOCOPY varchar2,
4878 x_return_status IN OUT NOCOPY VARCHAR2
4879 )is
4880 CURSOR c_cond_assoc is
4881 SELECT ams_query_temp_inst_cond_asc_s.NEXTVAL FROM dual;
4882
4883 CURSOR c_cond_value is
4884 SELECT ams_query_condition_value_s.NEXTVAL FROM dual;
4885
4886
4887 CURSOR fetch_condition_assoc(p_templ_inst_id NUMBER) IS
4888 SELECT assoc_id ,template_instance_id,query_condition_id,condition_sequence,running_total, delta
4889 FROM ams_query_temp_inst_cond_assoc
4890 where template_instance_id = p_templ_inst_id;
4891
4892 CURSOR fetch_condition_value(p_assoc_id NUMBER) IS
4893 SELECT query_cond_value_id,assoc_id,query_cond_disp_struct_id,value,lov_values_included_flag
4894 FROM ams_query_condition_value
4895 where assoc_id = p_assoc_id;
4896
4897 l_assoc_id NUMBER;
4898 l_old_assoc_id NUMBER;
4899 l_cond_value_id NUMBER;
4900 l_return_status VARCHAR2(1);
4901 l_msg_count number;
4902 l_msg_data VARCHAR2(2000);
4903
4904 BEGIN
4905
4906 IF (AMS_DEBUG_HIGH_ON) THEN
4907 AMS_Utility_PVT.debug_message('copy_conditions '||p_old_templ_inst_id ||'::'||p_new_templ_inst_id);
4908 END IF;
4909
4910
4911 FOR l_cond_assoc_rec IN fetch_condition_assoc(p_old_templ_inst_id)
4912 LOOP
4913
4914 OPEN c_cond_assoc;
4915 FETCH c_cond_assoc INTO l_assoc_id;
4916 CLOSE c_cond_assoc;
4917
4918 l_old_assoc_id := l_cond_assoc_rec.assoc_id;
4919
4920 IF (AMS_DEBUG_HIGH_ON) THEN
4921 AMS_Utility_PVT.debug_message('copy_conditions found assoc'||l_old_assoc_id);
4922 END IF;
4923
4924 ams_query_tmp_assoc_pkg.insert_row (
4925 X_ASSOC_ID => l_assoc_id,
4926 X_TEMPLATE_INSTANCE_ID=>p_new_templ_inst_id,
4927 X_QUERY_CONDITION_ID => l_cond_assoc_rec.query_condition_id ,
4928 X_CONDITION_SEQUENCE => l_cond_assoc_rec.condition_sequence ,
4929 X_RUNNING_TOTAL => l_cond_assoc_rec.running_total ,
4930 X_DELTA => l_cond_assoc_rec.delta ,
4931 X_OBJECT_VERSION_NUMBER =>1,
4932 X_REQUEST_ID =>NULL,
4933 X_CREATION_DATE => SYSDATE ,
4934 X_CREATED_BY => FND_GLOBAL.USER_ID,
4935 X_LAST_UPDATE_DATE => SYSDATE ,
4936 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
4937 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
4938 );
4939
4940 IF (AMS_DEBUG_HIGH_ON) THEN
4941 AMS_Utility_PVT.debug_message('copy_conditions new assoc'||l_assoc_id);
4942 END IF;
4943
4944 FOR l_cond_value_rec IN fetch_condition_value(l_old_assoc_id)
4945 LOOP
4946
4947 IF (AMS_DEBUG_HIGH_ON) THEN
4948 AMS_Utility_PVT.debug_message('copy_conditions found cond values for '||l_old_assoc_id);
4949 END IF;
4950
4951 OPEN c_cond_value;
4952 FETCH c_cond_value INTO l_cond_value_id;
4953 CLOSE c_cond_value;
4954
4955 AMS_QUERY_CONDITION_VALUE_PKG.INSERT_ROW (
4956 X_QUERY_COND_VALUE_ID=>l_cond_value_id,
4957 X_ASSOC_ID => l_assoc_id,
4958 X_QUERY_COND_DISP_STRUCT_ID => l_cond_value_rec.query_cond_disp_struct_id,
4959 X_VALUE =>l_cond_value_rec.value,
4960 X_LOV_VALUES_INCLUDED_FLAG => l_cond_value_rec.lov_values_included_flag,
4961 X_OBJECT_VERSION_NUMBER =>1,
4962 X_REQUEST_ID => NULL,
4963 X_CREATION_DATE => SYSDATE ,
4964 X_CREATED_BY => FND_GLOBAL.USER_ID,
4965 X_LAST_UPDATE_DATE => SYSDATE ,
4966 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
4967 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
4968 );
4969
4970 IF (AMS_DEBUG_HIGH_ON) THEN
4971 AMS_Utility_PVT.debug_message('copy_conditions new cond values '||l_assoc_id||'::::'||l_cond_value_id);
4972 END IF;
4973 END LOOP;
4974 END LOOP;
4975
4976 IF (AMS_DEBUG_HIGH_ON) THEN
4977 AMS_Utility_PVT.debug_message('copy_conditions end ');
4978 END IF;
4979
4980 END copy_conditions;
4981
4982 ------------------------------------------------------------------------------------------------------------------
4983 --------------------------Procedure to INVOKE TARGETGROUP LOCK Begins here----------------------------------------
4984 ------------------------------------------------------------------------------------------------------------------
4985
4986 --===============================================================================================
4987 -- Procedure
4988 -- INVOKE_TARGET_GROUP_LOCK
4989 --
4990 -- PURPOSE
4991 -- This api is called to check for the schedules in ACTIVE State(Campaign or Event).
4992 --
4993 -- ALGORITHM
4994 -- 1. Get All parameter Types
4995 --
4996 -- Any error in any of the API callouts?
4997 -- => a) Set RETURN STATUS to E
4998 --
4999 -- OPEN ISSUES
5000 -- 1. Should we do a explicit exit on Object_type not found.
5001 --
5002 -- HISTORY
5003 -- 19-Apr-2005 ndadwal
5004 --===============================================================================================
5005
5006 FUNCTION INVOKE_TARGET_GROUP_LOCK ( p_subscription_guid IN RAW,
5007 p_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2
5008 IS
5009
5010 --Local Variables
5011
5012 l_obj_id NUMBER;
5013 l_obj_type VARCHAR2(30);
5014 l_old_status_code VARCHAR2(30);
5015 l_new_status_code VARCHAR2(30);
5016 l_related_event_obj_type VARCHAR2(30);
5017 l_related_event_id VARCHAR2(30);
5018 l_msg_count NUMBER;
5019 l_msg_data VARCHAR2(30);
5020 l_return_status VARCHAR2(30);
5021
5022 BEGIN
5023
5024 -- Fetch all values, coming from the business event
5025 l_obj_id := p_event.getValueForParameter('OBJECT_ID');
5026 l_obj_type := p_event.getValueForParameter('OBJECT_TYPE');
5027 l_old_status_code := p_event.getValueForParameter('OLD_STATUS');
5028 l_new_status_code := p_event.getValueForParameter('NEW_STATUS');
5029 l_related_event_obj_type := p_event.getValueForParameter('RELATED_EVENT_OBJECT_TYPE');
5030 l_related_event_id := p_event.getValueForParameter('RELATED_EVENT_OBJECT_ID');
5031
5032 IF (AMS_DEBUG_HIGH_ON) THEN
5033 AMS_Utility_PVT.debug_message('****FUNCTION: INVOKE_TARGET_GROUP_LOCK start****');
5034 END IF;
5035 IF ( l_obj_type = 'CSCH' AND l_new_status_code = 'ACTIVE')
5036 THEN
5037
5038 IF ( ( l_related_event_obj_type IS NOT NULL ) AND ( l_related_event_id IS NOT NULL ) )
5039 THEN
5040
5041 IF (AMS_DEBUG_HIGH_ON) THEN
5042 AMS_Utility_PVT.debug_message('For OneOffEvent Active Schedules');
5043 AMS_Utility_PVT.debug_message('P_OBJECT_TYPE ' || l_related_event_obj_type );
5044 AMS_Utility_PVT.debug_message('P_OBJ_ID ' || l_related_event_id );
5045 AMS_Utility_PVT.debug_message('X_MSG_COUNT' || l_msg_count);
5046 AMS_Utility_PVT.debug_message('X_MSG_DATA' || l_msg_data);
5047 AMS_Utility_PVT.debug_message('X_RETURN_STATUS' || l_return_status);
5048 END IF;
5049 UPDATE_LIST_STATUS_TO_LOCKED(P_OBJECT_TYPE => l_related_event_obj_type ,
5050 P_OBJ_ID => l_related_event_id,
5051 X_MSG_COUNT => l_msg_count,
5052 X_MSG_DATA =>l_msg_data,
5053 X_RETURN_STATUS => l_return_status);
5054
5055
5056
5057 ELSE
5058
5059 IF (AMS_DEBUG_HIGH_ON) THEN
5060 AMS_Utility_PVT.debug_message('For Campaign Active Schedules');
5061 AMS_Utility_PVT.debug_message('P_OBJECT_TYPE ' || l_obj_type );
5062 AMS_Utility_PVT.debug_message('P_OBJ_ID ' || l_obj_id );
5063 AMS_Utility_PVT.debug_message('X_MSG_COUNT' || l_msg_count);
5064 AMS_Utility_PVT.debug_message('X_MSG_DATA' || l_msg_data);
5065 AMS_Utility_PVT.debug_message('X_RETURN_STATUS' || l_return_status);
5066 END IF;
5067 UPDATE_LIST_STATUS_TO_LOCKED(P_OBJECT_TYPE => l_obj_type ,
5068 P_OBJ_ID => l_obj_id,
5069 X_MSG_COUNT => l_msg_count,
5070 X_MSG_DATA =>l_msg_data,
5071 X_RETURN_STATUS => l_return_status);
5072
5073
5074 END IF;
5075
5076 ELSIF (((l_obj_type = 'EONE') OR (l_obj_type = 'EVEO') ) AND (l_new_status_code = 'ACTIVE'))
5077 THEN
5078
5079 IF (AMS_DEBUG_HIGH_ON) THEN
5080 AMS_Utility_PVT.debug_message('For EONE/EVEO Active Schedules');
5081 AMS_Utility_PVT.debug_message('P_OBJECT_TYPE ' || l_obj_type );
5082 AMS_Utility_PVT.debug_message('P_OBJ_ID ' || l_obj_id );
5083 AMS_Utility_PVT.debug_message('X_MSG_COUNT' || l_msg_count);
5084 AMS_Utility_PVT.debug_message('X_MSG_DATA' || l_msg_data);
5085 AMS_Utility_PVT.debug_message('X_RETURN_STATUS' || l_return_status);
5086 END IF;
5087 UPDATE_LIST_STATUS_TO_LOCKED(P_OBJECT_TYPE => l_obj_type ,
5088 P_OBJ_ID => l_obj_id,
5089 X_MSG_COUNT => l_msg_count,
5090 X_MSG_DATA =>l_msg_data,
5091 X_RETURN_STATUS => l_return_status);
5092
5093
5094 END IF;
5095
5096
5097 return 'SUCCESS';
5098
5099 EXCEPTION
5100
5101 WHEN OTHERS THEN
5102
5103 WF_CORE.CONTEXT('AMS_Act_List_PVT', 'INVOKE_TARGET_GROUP_LOCK', p_event.getEventName( ), p_subscription_guid);
5104 WF_EVENT.setErrorInfo(p_event, 'ERROR');
5105
5106 END INVOKE_TARGET_GROUP_LOCK;
5107
5108
5109 -------------------------------------------------------------------------------------------------
5110 --------------------------Procedure to INVOKE TARGETGROUP LOCK Ends here-------------------------
5111 -------------------------------------------------------------------------------------------------
5112
5113 -------------------------------------------------------------------------------------------------
5114 --------------------------Procedure to UPDATE LIST STATUS TO LOCKED Begins here------------------
5115 -------------------------------------------------------------------------------------------------
5116
5117 --===============================================================================================
5118 -- Procedure
5119 -- UPDATE_LIST_STATUS_TO_LOCKED
5120 --
5121 -- PURPOSE
5122 -- This api is called to Lock the Traget Group when the Schedule is in ACTIVE Status
5123 --
5124 -- ALGORITHM
5125 -- a) Take as input type and id e.g. CSCH and schedule id
5126 -- b) It will query AMS_LIST_HEADERS_ALL to check if target group exists for object type and id
5127 -- c) Will do nothing (i.e. will return success) if target group does not exist (since Target Groups are relevant only for certain type of schedules)
5128 -- d) If Target Group is already in LOCKED status do nothing, return;
5129 -- e) If Target Group exists will update the user status id and status code of TG to LOCKED.
5130 -- f) User_Status_Code and User_Status_Id are passed to make it more generic. So we can change the Target Group status as per the requirement and not only to LOCKED status.
5131 --
5132 -- Any error in any of the API callouts?
5133 -- => a) Set RETURN STATUS to E
5134 --
5135 -- OPEN ISSUES
5136 -- 1.
5137 --
5138 -- HISTORY
5139 -- 19-Apr-2005 ndadwal
5140 --===============================================================================================
5141 PROCEDURE UPDATE_LIST_STATUS_TO_LOCKED
5142 (
5143 P_OBJECT_TYPE IN VARCHAR2,
5144 P_OBJ_ID IN NUMBER,
5145 X_MSG_COUNT OUT NOCOPY NUMBER,
5146 X_MSG_DATA OUT NOCOPY VARCHAR2,
5147 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
5148 IS
5149 BEGIN
5150
5151 -- INITIALIZE RETURN STATUS TO SUCCESS
5152 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5153
5154 IF (AMS_DEBUG_HIGH_ON) THEN
5155 AMS_Utility_PVT.debug_message('****FUNCTION: UPDATE_LIST_STATUS_TO_LOCKED start****');
5156
5157 END IF;
5158
5159 UPDATE AMS_LIST_HEADERS_ALL
5160 SET STATUS_CODE = 'LOCKED',
5161 USER_STATUS_ID = (select user_status_id from ams_user_statuses_vl where system_status_type ='AMS_LIST_STATUS' and system_status_code ='LOCKED' and default_flag ='Y' ),
5162 STATUS_DATE = SYSDATE,
5163 LAST_UPDATE_DATE = SYSDATE
5164 WHERE LIST_USED_BY_ID = P_OBJ_ID
5165 AND ARC_LIST_USED_BY = P_OBJECT_TYPE
5166 AND LIST_TYPE = 'TARGET';
5167
5168
5169 IF (AMS_DEBUG_HIGH_ON) THEN
5170 AMS_Utility_PVT.debug_message('TARGET GROUP LOCKED while executing procedure UPDATE_LIST_STATUS_TO_LOCKED');
5171 END IF;
5172
5173
5174 EXCEPTION
5175
5176
5177 WHEN FND_API.G_EXC_ERROR THEN
5178 IF (AMS_DEBUG_HIGH_ON) THEN
5179 AMS_Utility_PVT.debug_message('Exception while executing procedure UPDATE_LIST_STATUS_TO_LOCKED'||' '||sqlerrm||' '||sqlcode);
5180 END IF;
5181
5182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5183 IF (AMS_DEBUG_HIGH_ON) THEN
5184 AMS_Utility_PVT.debug_message('UNEXPECTED_ERROR while executing procedure UPDATE_LIST_STATUS_TO_LOCKED'||' '||sqlerrm||' '||sqlcode);
5185 END IF;
5186
5187
5188 WHEN OTHERS THEN
5189 IF (AMS_DEBUG_HIGH_ON) THEN
5190 AMS_Utility_PVT.debug_message('Other Error while executing procedure UPDATE_LIST_STATUS_TO_LOCKED'||' '||sqlerrm||' '||sqlcode);
5191 END IF;
5192
5193 END UPDATE_LIST_STATUS_TO_LOCKED;
5194
5195 -------------------------------------------------------------------------------------------------
5196 --------------------------Procedure to UPDATE LIST STATUS TO LOCKED Ends here--------------------
5197 -------------------------------------------------------------------------------------------------
5198
5199
5200 END AMS_Act_List_PVT;