[Home] [Help]
PACKAGE BODY: APPS.AMS_MANUAL_LIST_GEN
Source
1 PACKAGE BODY AMS_MANUAL_LIST_GEN AS
2 /* $Header: amsvlmlb.pls 120.3.12010000.2 2008/08/11 08:52:06 amlal ship $ */
3
4 g_list_header_id ams_list_headers_all.list_header_id%type;
5 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
6 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
8
9 AMS_LOG_PROCEDURE constant number := FND_LOG.LEVEL_PROCEDURE;
10 AMS_LOG_EXCEPTION constant Number := FND_LOG.LEVEL_EXCEPTION;
11 AMS_LOG_STATEMENT constant Number := FND_LOG.LEVEL_STATEMENT;
12
13 AMS_LOG_PROCEDURE_ON boolean := AMS_UTILITY_PVT.logging_enabled(AMS_LOG_PROCEDURE);
14 AMS_LOG_EXCEPTION_ON boolean := AMS_UTILITY_PVT.logging_enabled(AMS_LOG_EXCEPTION);
15 AMS_LOG_STATEMENT_ON boolean := AMS_UTILITY_PVT.logging_enabled(AMS_LOG_STATEMENT);
16
17 G_module_name constant varchar2(100):='oracle.apps.ams.plsql.'||g_pkg_name;
18
19 PROCEDURE WRITE_TO_ACT_LOG(p_msg_data in VARCHAR2,
20 p_arc_log_used_by in VARCHAR2 ,--DEFAULT 'LIST',
21 p_log_used_by_id in number )--DEFAULT g_list_header_id)
22 IS
23 PRAGMA AUTONOMOUS_TRANSACTION;
24 l_return_status VARCHAR2(1);
25 BEGIN
26 AMS_UTILITY_PVT.CREATE_LOG(
27 x_return_status => l_return_status,
28 p_arc_log_used_by => 'LIST',
29 p_log_used_by_id => g_list_header_id,
30 p_msg_data => p_msg_data);
31 COMMIT;
32 END WRITE_TO_ACT_LOG;
33
34 PROCEDURE form_sql_statement(p_list_header_id in number,
35 p_master_type in varchar2,
36 p_child_types in child_type,
37 x_final_string OUT NOCOPY varchar2
38 ) is
39 -- child_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
40 l_data_source_types varchar2(2000);
41 l_field_col_tbl JTF_VARCHAR2_TABLE_100;
42 l_source_col_tbl JTF_VARCHAR2_TABLE_100;
43 l_view_tbl JTF_VARCHAR2_TABLE_100;
44 cursor c_master_source_type is
45 select source_object_name , source_object_name || '.' || source_object_pk_field
46 from ams_list_src_types
47 where source_type_code = p_master_type;
48 cursor c_child_source_type (l_child_src_type varchar2 )is
49 select a.source_object_name ,
50 a.source_object_name || '.' || b.sub_source_type_pk_column
51 ,b.master_source_type_pk_column
52 from ams_list_src_types a, ams_list_src_type_assocs b
53 where a.source_type_code = l_child_src_type
54 and b.sub_source_type_id = a.list_source_type_id;
55 l_count number;
56 l_master_object_name varchar2(4000);
57 l_child_object_name varchar2(4000);
58 l_master_primary_key varchar2(1000);
59 l_child_primary_key varchar2(32767);
60 l_from_clause varchar2(32767);
61 l_where_clause varchar2(32767);
62 l_select_clause varchar2(32767);
63 l_insert_clause varchar2(32767);
64 l_final_sql varchar2(32767);
65 l_insert_sql varchar2(32767);
66 l_no_of_chunks number;
67 l_master_fkey Varchar2(30);
68 l_dummy_primary_key varchar2(1000);
69
70 l_api_name CONSTANT VARCHAR2(30) := 'master_source_type_view';
71 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
72
73 l_created_by NUMBER; --batoleti added this var. For bug# 6688996
74
75 /* batoleti. Bug# 6688996. Added the below cursor */
76 CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
77 SELECT created_by
78 FROM ams_list_headers_all
79 WHERE list_header_id= x_list_header_id;
80
81
82 begin
83 WRITE_TO_ACT_LOG(' manual list ->p_master_type'
84 || p_master_type,'LIST',p_list_header_id);
85
86 IF (AMS_LOG_PROCEDURE_ON) THEN
87 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||':Start');
88 END IF;
89
90 open c_master_source_type;
91 fetch c_master_source_type into l_master_object_name , l_master_primary_key;
92 close c_master_source_type;
93 WRITE_TO_ACT_LOG('manual list->' || l_master_object_name,'LIST',p_list_header_id);
94 l_from_clause := ' FROM ' || l_master_object_name;
95 l_data_source_types := ' ('|| ''''|| p_master_type ||'''';
96 l_where_clause := 'where 1 = 1 ';
97
98 l_count := p_child_types.count();
99 if l_count > 0 then
100 for i in 1..p_child_types.last
101 loop
102 l_data_source_types := l_data_source_types || ','|| ''''
103 || p_child_types(i)||'''' ;
104 open c_child_source_type(p_child_types(i));
105 fetch c_child_source_type into l_child_object_name ,
106 l_child_primary_key
107 ,l_master_fkey;
108 if l_master_fkey is not null then
109 l_dummy_primary_key := l_master_object_name || '.'|| l_master_fkey;
110 else
111 l_dummy_primary_key := l_master_primary_key;
112 end if;
113 l_from_clause := l_from_clause || ','|| l_child_object_name ;
114 l_where_clause := l_where_clause || 'and '
115 ||l_dummy_primary_key || ' = '
116 || l_child_primary_key || '(+)';
117 close c_child_source_type;
118 end loop;
119 end if;
120 WRITE_TO_ACT_LOG('manual->after child' || l_where_clause,'LIST',p_list_header_id);
121 l_data_source_types := l_data_source_types || ') ' ;
122
123 EXECUTE IMMEDIATE
124 'BEGIN
125 SELECT b.field_column_name ,
126 c.source_object_name,
127 b.source_column_name
128 BULK COLLECT INTO :1 ,:2 ,:3
129 FROM ams_list_src_fields b, ams_list_src_types c
130 WHERE b.list_source_type_id = c.list_source_type_id
131 and b.DE_LIST_SOURCE_TYPE_CODE IN '|| l_data_source_types ||
132 ' AND b.ROWID >= (SELECT MAX(a.ROWID)
133 FROM ams_list_src_fields a
134 WHERE a.field_column_name= b.field_column_name
135 AND a.DE_LIST_SOURCE_TYPE_CODE IN '
136 || l_data_source_types || ') ;
137 END; '
138 USING OUT l_field_col_tbl ,OUT l_view_tbl , OUT l_source_col_tbl ;
139 for i in 1 .. l_field_col_tbl.last
140 loop
141 l_insert_clause := l_insert_clause || ' ,' || l_field_col_tbl(i) ;
142 l_select_clause := l_select_clause || ' ,' ||
143 l_view_tbl(i) || '.'||l_source_col_tbl(i) ;
144 --WRITE_TO_ACT_LOG('imp: select clause'||i||':->' || l_select_clause,'LIST',p_list_header_id);
145 end loop;
146 WRITE_TO_ACT_LOG('manual list:before insert_sql ','LIST',p_list_header_id);
147
148 -- batoleti coding starts for bug# 6688996
149 l_created_by := 0;
150
151 OPEN cur_get_created_by(p_list_header_id);
152
153 FETCH cur_get_created_by INTO l_created_by;
154 CLOSE cur_get_created_by;
155
156 -- batoleti coding ends for bug# 6688996
157
158
159 l_insert_sql := 'insert into ams_list_entries '||
160 '( LIST_SELECT_ACTION_FROM_NAME, '||
161 ' LIST_ENTRY_SOURCE_SYSTEM_ID , '||
162 ' LIST_ENTRY_SOURCe_SYSTEM_TYPE, '||
163 ' list_select_action_id , '||
164 ' rank , '||
165 ' list_header_id,last_update_date, '||
166 ' last_updated_by,creation_date,created_by,'||
167 'list_entry_id, '||
168 'object_version_number, ' ||
169 'source_code , ' ||
170 'source_code_for_id , ' ||
171 'arc_list_used_by_source , ' ||
172 'arc_list_select_action_from , ' ||
173 'pin_code , ' ||
174 'view_application_id , ' ||
175 'manually_entered_flag , ' ||
176 'marked_as_random_flag , ' ||
177 'marked_as_duplicate_flag , ' ||
178 'part_of_control_group_flag , ' ||
179 'exclude_in_triggered_list_flag , ' ||
180 'enabled_flag ' ||
181 l_insert_clause || ' ) ' ||
182 'select ' ||
183 l_master_primary_key ||','||
184 l_master_primary_key ||','||
185 ''''||p_master_type||''''||','||
186 0 || ',' ||1||','||
187 p_list_header_id || ',' ||''''||
188 to_char(sysdate )|| ''''||','||
189 to_char(FND_GLOBAL.login_id )|| ',' ||''''||
190 to_char(sysdate )|| ''''||','||
191 l_created_by|| ',' ||
192 'ams_list_entries_s.nextval' || ','||
193 1 || ','||
194 ''''||'NONE' ||'''' || ','||
195 0 || ',' ||
196 ''''||'NONE' ||'''' || ','||
197 ''''||'NONE' ||'''' || ','||
198 'ams_list_entries_s.currval'|| ','||
199 530 || ','||
200 ''''||'Y' ||''''|| ','||
201 ''''||'N' ||''''|| ','||
202 ''''||'N' ||''''|| ','||
203 ''''||'N' ||''''|| ','||
204 ''''||'N' ||''''|| ','||
205 ''''||'Y' ||''''||
206 l_select_clause ;
207
208 /* commented OUT NOCOPY becuase of performance reasons
209 l_final_sql := l_insert_sql || ' ' ||
210 l_from_clause || ' '||
211 l_where_clause || ' and ' ||
212 l_master_primary_key ||
213 '|| '||''''||p_master_type ||''''|| ' = ' ;
214 */
215 WRITE_TO_ACT_LOG('form_sql_statement:before final sql ','LIST',p_list_header_id);
216 l_final_sql := l_insert_sql || ' ' ||
217 l_from_clause || ' '||
218 l_where_clause || ' and ' ||
219 l_master_primary_key|| ' = ' ;
220 x_final_string := l_final_sql;
221 --WRITE_TO_ACT_LOG('form_sql_statement:after final sql ','LIST',p_list_header_id);
222 l_no_of_chunks := ceil(length(l_final_sql)/2000 );
223 for i in 1 ..l_no_of_chunks
224 loop
225 WRITE_TO_ACT_LOG(substr(l_final_sql,(2000*i) - 1999,2000),'LIST',p_list_header_id);
226 end loop;
227 IF (AMS_LOG_PROCEDURE_ON) THEN
228 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||': END ');
229 END IF;
230
231 exception
232 WHEN OTHERS THEN
233 WRITE_TO_ACT_LOG('Error : '|| sqlerrm,'LIST',p_list_header_id);
234 end form_sql_statement;
235
236 --- manual entries changes start: musman
237 PROCEDURE do_bulk_insert
238 ( p_sql_string IN VARCHAR2
239 ,p_list_header_id in NUMBER
240 ,p_primary_key_tbl IN JTF_NUMBER_TABLE--primary_key_Tbl_Type ,
241 ,x_added_entry_count OUT NOCOPY NUMBER
242 ,x_return_status OUT NOCOPY VARCHAR2
243 ,x_msg_count OUT NOCOPY NUMBER
244 ,x_msg_data OUT NOCOPY VARCHAR2
245 )IS
246
247 l_api_name constant varchar2(30) := 'do_bulk_insert';
248 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
249 BEGIN
250
251 x_return_status := FND_API.G_RET_STS_SUCCESS ;
252 IF (AMS_LOG_PROCEDURE_ON) THEN
253 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||':Start');
254 END IF;
255
256 IF (AMS_LOG_STATEMENT_ON) THEN
257 AMS_UTILITY_PVT.debug_message(
258 AMS_LOG_STATEMENT ,g_module_name||'.'||l_api_name
259 ,' P_list_header_id :'||p_list_header_id||' ,p_primary_key_tbl count :'||p_primary_key_tbl.count);
260 END IF;
261
262 EXECUTE IMMEDIATE
263 'BEGIN
264 FORALL i in :1 .. :2 '
265 || ' ' || p_sql_string || ' :tab(i)
266 and
267 not exists (select 1
268 from ams_list_entries
269 where list_entry_source_system_id = :tab(i)
270 and list_header_id = :5 and enabled_flag=''Y'');
271 :6 := SQL%ROWCOUNT;
272 END; '
273 using p_primary_key_tbl.first,
274 p_primary_key_tbl.last,
275 p_primary_key_tbl,
276 p_list_header_id,
277 OUT x_added_entry_count;
278 --x_added_entry_count := SQL%ROWCOUNT;
279 --WRITE_TO_ACT_LOG('MARZIA ADDED ENTRY SQL%ROWCOUNT' || x_added_entry_count,'LIST',p_list_header_id);
280
281 IF (AMS_LOG_STATEMENT_ON) THEN
282 AMS_UTILITY_PVT.debug_message(
283 AMS_LOG_STATEMENT ,g_module_name||'.'||l_api_name
284 ,' ADDED ENTRY SQL%ROWCOUNT :'||x_added_entry_count);
285 END IF;
286
287 IF (AMS_LOG_PROCEDURE_ON) THEN
288 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||': END ');
289 END IF;
290
291
292 EXCEPTION
293 WHEN FND_API.G_EXC_ERROR THEN
294 x_return_status := FND_API.g_ret_sts_error ;
295 FND_MSG_PUB.Count_AND_Get
296 ( p_count => x_msg_count,
297 p_data => x_msg_data,
298 p_encoded => FND_API.G_FALSE
299 );
300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
301 x_return_status := FND_API.g_ret_sts_unexp_error ;
302 FND_MSG_PUB.Count_AND_Get
303 ( p_count => x_msg_count,
304 p_data => x_msg_data,
305 p_encoded => FND_API.G_FALSE
306 );
307 WHEN OTHERS THEN
308 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
309 IF (AMS_LOG_PROCEDURE_ON) THEN
310 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' In Others exception handling ');
311 END IF;
312
313 IF (AMS_LOG_EXCEPTION_ON) THEN
314 AMS_UTILITY_PVT.debug_message(AMS_LOG_EXCEPTION,g_module_name||'.'||l_api_name,'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
315 END IF; WRITE_TO_ACT_LOG('Error : '|| sqlerrm,'LIST',p_list_header_id);
316
317
318 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
319 THEN
320 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
321 END IF;
322 FND_MSG_PUB.Count_AND_Get
323 ( p_count => x_msg_count,
324 p_data => x_msg_data,
325 p_encoded => FND_API.G_FALSE
326 );
327
328 END do_bulk_insert;
329 --- manual entries changes start: musman
330 PROCEDURE process_manual_list(
331 p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
333 p_commit IN VARCHAR2 := FND_API.g_false,
334 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
335 x_return_status OUT NOCOPY VARCHAR2,
336 x_msg_count OUT NOCOPY NUMBER,
337 x_msg_data OUT NOCOPY VARCHAR2,
338 p_list_header_id in NUMBER,
339 p_primary_key_tbl IN JTF_NUMBER_TABLE,--primary_key_Tbl_Type ,
340 p_master_type in VARCHAR2
341 , x_added_entry_count OUT NOCOPY NUMBER --- manual entries changes added: musman
342 ) IS
343 l_api_name constant varchar2(30) := 'process_manual_list';
344 l_full_name CONSTANT VARCHAR2(100) := g_pkg_name ||'.'|| l_api_name;
345
346 l_list_header_id number;
347 l_api_version CONSTANT NUMBER := 1.0;
348
349 l_child_types child_type ;
350 cursor c_mapping_types(p_master_type varchar2) is
354 AND a.master_source_type_flag = 'Y';
351 SELECT list_source_type_id
352 FROM ams_list_src_types a
353 WHERE a.source_type_code = p_master_type
355 cursor c_mapping_subtypes(p_master_type_id
356 ams_list_src_type_assocs.master_source_type_id%type)is
357 select source_type_code
358 from ams_list_src_types a,
359 ams_list_src_type_assocs b
360 where b.master_source_type_id = p_master_type_id
361 and b.sub_source_type_id = a.list_source_type_id;
362
363 /* bug:4467062 fix:musman
364 cursor c_count_list_entries(cur_p_list_header_id number) is
365 select sum(decode(enabled_flag,'N',0,1)),
366 sum(decode(enabled_flag,'Y',0,1)),
367 sum(1),
368 sum(decode(part_of_control_group_flag,'Y',1,0)),
369 sum(decode(marked_as_random_flag,'Y',1,0)),
370 sum(decode(marked_as_duplicate_flag,'Y',1,0)),
371 sum(decode(manually_entered_flag,
372 'Y',decode(enabled_flag,'Y','1',0),
373 0))
374 from ams_list_entries
375 where list_header_id = cur_p_list_header_id ;
376 */
377 --- manual entries changes added: musman
378 cursor c_get_max_entries
379 is
380 select no_of_rows_max_requested - no_of_rows_active
381 from ams_list_headers_all
382 where list_header_id = p_list_header_id ;
383
384 l_no_of_chunks number;
385 l_master_type_id number;
386 l_source_type_code varchar2(30);
387
388 l_sql_string VARCHAR2(32767);
389 l_min_rows number;
390 l_new_status varchar2(30);
391 l_new_status_id number;
392 l_no_of_rows_duplicates number;
393 l_no_of_rows_in_list number;
394 l_no_of_rows_active number;
395 l_no_of_rows_inactive number;
396 l_no_of_rows_manually_entered number;
397 l_no_of_rows_in_ctrl_group number;
398 l_no_of_rows_random number;
399
400 l_allowed_spaces NUMBER ;
401 p_added_entry_count NUMBER;
402 l_primary_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
403
404 l_added_entry_count NUMBER;
405 l_rows_to_add NUMBER;
406 l_remaining_Spaces NUMBER;
407 l_start_counter NUMBER;
408 l_end_counter NUMBER;
409 j NUMBER;
410
411 --rmbhanda bug#5197904 start
412 t_master_type_id NUMBER;
413 l_child_count number :=0;
414
415 cursor c_mapping_childtypes(p_master_type_id
416 ams_list_src_type_assocs.master_source_type_id%type)
417 IS
418 select source_type_code
419 from ams_list_src_types a,
420 ams_list_src_type_assocs b
421 where b.master_source_type_id = p_master_type_id
422 and b.sub_source_type_id = a.list_source_type_id
423 and b.enabled_flag = 'Y'
424 and a.enabled_flag = 'Y'
425 and exists (select 'x' from ams_list_src_fields
426 where list_source_type_id = b.sub_source_type_id
427 and field_column_name is not null) ;
428
429 p_mapping_childtype_rec c_mapping_childtypes%rowtype;
430 l_no_of_rows_duplicated NUMBER;
431
432 --rmbhanda bug#5197904 end
433
434 BEGIN
435
436 SAVEPOINT process_manual_list;
437 g_list_header_id := p_list_header_id;
438
439 IF FND_API.to_boolean(p_init_msg_list) THEN
440 FND_MSG_PUB.initialize;
441 END IF;
442
443 IF (AMS_LOG_PROCEDURE_ON) THEN
444 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||':Start');
445 END IF;
446
447
448 IF NOT FND_API.compatible_api_call
449 (
450 l_api_version,
451 p_api_version,
452 l_api_name,
453 g_pkg_name
454 )THEN
455 RAISE FND_API.g_exc_unexpected_error;
456 END IF;
457 x_return_status := FND_API.G_RET_STS_SUCCESS;
458
459 update ams_list_entries
460 set marked_flag = null
461 where list_header_id = p_list_header_id ;
462
463 --- manual entries changes start: musman
464 OPEN c_get_max_entries;
465 FETCH c_get_max_entries INTO l_allowed_spaces;
466 CLOSE c_get_max_entries;
467
468 IF (AMS_LOG_STATEMENT_ON) THEN
469 AMS_UTILITY_PVT.debug_message(
470 AMS_LOG_STATEMENT ,g_module_name||'.'||l_api_name
471 ,' l_allowed_spaces :'||l_allowed_spaces);
472 END IF;
473
474 -- if user has not specified the max space, adding all the entries
475 IF l_allowed_spaces IS NULL THEN
476 l_allowed_spaces := p_primary_key_tbl.count;
477 END IF;
478 -- intialize the out param
479 x_added_entry_count := 0;
480
481 -- only if space is allowed then forming the sql
482 IF l_allowed_spaces > 0 THEN
483
484 -- rmbhanda bug#5197904 start - retrieve all the child types for the given master type -no hard codes
485 /*if p_master_type = 'PERSON_LIST' then
486 l_child_types(1) := 'PERSON_PHONE1';
487 end if;
488 if p_master_type = 'ORGANIZATION_CONTACT_LIST' then
489 l_child_types(1) := 'ORGANIZATION_PHONE1';
490 l_child_types(2) := 'ORGANIZATION_LIST';
491 end if;*/
492
493 open c_mapping_types(p_master_type);
494 loop
495 fetch c_mapping_types
496 into t_master_type_id;
500
497 exit when c_mapping_types%notfound;
498 end loop;
499 close c_mapping_types;
501 open c_mapping_childtypes(t_master_type_id);
502 loop
503 fetch c_mapping_childtypes
504 into p_mapping_childtype_rec;
505 exit when c_mapping_childtypes%notfound;
506
507 l_child_count := l_child_count +1;
508 l_child_types(l_child_count) := p_mapping_childtype_rec.source_type_code;
509 end loop;
510 close c_mapping_childtypes;
511
512 --rmbhanda bug#5197904 end
513
514 form_sql_statement( p_list_header_id => p_list_header_id,
515 p_master_type => p_master_type ,
516 p_child_types => l_child_types,
517 x_final_string => l_sql_string
518 ) ;
519 /*
520 EXECUTE IMMEDIATE
521 'BEGIN
522 FORALL i in :1 .. :2 '
523 || ' ' || p_sql_string || ' :tab(i)
524 and
525 not exists (select 1
526 from ams_list_entries
527 where list_entry_source_system_id = :tab(i)
528 and list_header_id = :5 and enabled_flag=''Y'');
529
530 END; '
531 using p_primary_key_tbl.first,
532 p_primary_key_tbl.last,
533 p_primary_key_tbl,
534 p_list_header_id; */
535
536 l_rows_to_add := p_primary_key_tbl.count;
537 IF l_rows_to_add <= l_allowed_spaces THEN
538 do_bulk_insert(
539 p_sql_string => l_sql_string
540 ,p_list_header_id => p_list_header_id
541 ,p_primary_key_tbl => p_primary_key_tbl
542 ,x_added_entry_count => l_added_entry_count
543 ,x_return_status => x_return_status
544 ,x_msg_count => x_msg_count
545 ,x_msg_data => x_msg_data );
546
547 x_added_entry_count := l_added_entry_count;
548
549 ELSIF l_rows_to_add > l_allowed_spaces THEN
550 -- written the logic if the user trying to add entries is larger than allowed space
551 -- trying to form a new pl/sql table of allowed space and doing bulkInsert.if the entries
552 -- are not added due to duplicate, then looping thru the user sent data to the remaining spaces
553 -- and doing bulk insert, until the allowed space is reached or loop through all added entries (p_primary_key_tbl)
554 l_remaining_Spaces := l_allowed_spaces;
555 l_start_counter := 1;
556 l_end_counter := l_remaining_Spaces;
557
558 IF (AMS_LOG_STATEMENT_ON) THEN
559 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name
560 ,'FIRST TIME ::l_remaining_Spaces :'||l_remaining_Spaces||', l_start_counter:'||l_start_counter||',l_end_counter:'||l_end_counter );
561 END IF;
562
563 WHILE l_remaining_Spaces >0 LOOP
564 l_primary_tbl.extend(l_remaining_Spaces);
565 j := 1;
566 for i in l_start_counter..l_end_counter LOOP
567 l_primary_tbl(j) := p_primary_key_tbl(i);
568 j:= J+1;
569 end loop;
570
571 do_bulk_insert(
572 p_sql_string => l_sql_string
573 , p_list_header_id => p_list_header_id
574 , p_primary_key_tbl => l_primary_tbl
575 , x_added_entry_count => l_added_entry_count
576 , x_return_status => x_return_status
577 , x_msg_count => x_msg_count
578 , x_msg_data => x_msg_data );
579
580 x_added_entry_count := x_added_entry_count + l_added_entry_count;
581
582 IF (AMS_LOG_STATEMENT_ON) THEN
583 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name
584 ,'IN THE LOOP before reset ::l_remaining_Spaces :'||l_remaining_Spaces||
585 ', l_start_counter:'||l_start_counter||',l_end_counter:'||l_end_counter );
586 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name
587 ,'x_added_entry_count :'||x_added_entry_count );
588 END IF;
589
590 IF l_remaining_spaces = l_added_entry_count THEN
591 EXIT;
592 ELSE
593 l_remaining_Spaces := l_allowed_spaces - x_added_entry_count;
594 l_start_counter := l_end_counter + 1;
595
596 IF ((l_start_counter > l_rows_to_add)
597 OR (l_remaining_spaces = 0 ))THEN
598 Exit;
599 END IF;
600 --l_end_counter := l_start_counter + (l_remaining_spaces-1);
601 l_end_counter := l_end_counter +l_remaining_Spaces ;
602 l_primary_tbl.delete;
603 l_primary_tbl := JTF_NUMBER_TABLE();
604
605 IF l_end_counter > l_rows_to_add THEN
606 l_end_counter := l_rows_to_add;
607 END IF;
608 END IF;
609 IF (AMS_LOG_STATEMENT_ON) THEN
610 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name
611 ,'IN THE LOOP after reset ::l_remaining_Spaces :'||l_remaining_Spaces||', l_start_counter:'||l_start_counter||',l_end_counter:'||l_end_counter );
612 END IF;
613
614 END LOOP;
615
616 END IF;
617 --- manual entries changes end: musman
618 /* bug:4467062 fix:musman
619 open c_count_list_entries(p_list_header_id);
620 fetch c_count_list_entries
624 l_no_of_rows_in_ctrl_group ,
621 into l_no_of_rows_active ,
622 l_no_of_rows_inactive ,
623 l_no_of_rows_in_list ,
625 l_no_of_rows_random ,
626 l_no_of_rows_duplicates ,
627 l_no_of_rows_manually_entered ;
628 close c_count_list_entries;
629 */
630
631 SELECT nvl(no_of_rows_min_requested,0)
632 INTO l_min_rows
633 FROM ams_list_headers_all
634 WHERE list_header_id = p_list_header_id;
635
636 if l_min_rows > l_no_of_rows_active then
637 l_new_status := 'DRAFT';
638 l_new_status_id := 300;
639 else
640 l_new_status := 'AVAILABLE';
641 l_new_status_id := 303;
642 end if;
643 /* bug:4467062 fix:musman
644 update ams_list_headers_all
645 set no_of_rows_in_list = l_no_of_rows_in_list,
646 no_of_rows_active = l_no_of_rows_active,
647 no_of_rows_inactive = l_no_of_rows_inactive,
648 no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
649 no_of_rows_random = l_no_of_rows_random,
650 no_of_rows_duplicates = l_no_of_rows_duplicates,
651 no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
652 status_code = l_new_status,
653 user_status_id = l_new_status_id,
654 status_date = sysdate
655 WHERE list_header_id = p_list_header_id;
656 */
657
658 -- rmbhanda bug#5197904 start - Mark duplicate entries as disabled.
659
660 UPDATE ams_list_entries a
661 SET a.enabled_flag = 'N',
662 a.marked_as_duplicate_flag = 'Y'
663 WHERE a.list_header_id = p_list_header_id
664 and a.enabled_flag = 'Y'
665 and a.manually_entered_flag ='Y'
666 AND a.rowid > (SELECT min(b.rowid)
667 from ams_list_entries b
668 where b.list_header_id = p_list_header_id
669 and b.party_id = a.party_id
670 and b.enabled_flag = 'Y'
671 and b.manually_entered_flag ='Y'
672 );
673
674 l_no_of_rows_duplicated := sql%rowcount;
675
676 /*update ams_list_headers_all
677 set no_of_rows_in_list = no_of_rows_in_list + x_added_entry_count,
678 no_of_rows_active = no_of_rows_active + x_added_entry_count,
679 no_of_rows_manually_entered = no_of_rows_manually_entered + x_added_entry_count,
680 status_code = l_new_status,
681 user_status_id = l_new_status_id,
682 status_date = sysdate
683 WHERE list_header_id = p_list_header_id; */
684
685 --Update active rows/duplicate records count based on the no. of rows duplicated.
686
687 update ams_list_headers_all
688 set no_of_rows_in_list = no_of_rows_in_list + x_added_entry_count,
689 no_of_rows_active = no_of_rows_active + x_added_entry_count - l_no_of_rows_duplicated ,
690 no_of_rows_manually_entered = no_of_rows_manually_entered + x_added_entry_count,
691 status_code = l_new_status,
692 user_status_id = l_new_status_id,
693 status_date = sysdate,
694 no_of_rows_duplicates = no_of_rows_duplicates + l_no_of_rows_duplicated
695 WHERE list_header_id = p_list_header_id;
696
697 -- rmbhanda bug#5197904 end
698
699
700 END IF;
701
702 IF x_return_status = fnd_api.g_ret_sts_error THEN
703 RAISE FND_API.g_exc_error;
704 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
705 RAISE FND_API.g_exc_unexpected_error;
706 END IF;
707 IF p_commit = FND_API.g_true then
708 COMMIT WORK;
709 END IF;
710
711 FND_MSG_PUB.Count_AND_Get
712 ( p_count => x_msg_count,
713 p_data => x_msg_data,
714 p_encoded => FND_API.G_FALSE );
715
716 IF (AMS_LOG_PROCEDURE_ON) THEN
717 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||': END ');
718 END IF;
719
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR THEN
722 x_return_status := FND_API.g_ret_sts_error ;
723 ROLLBACK TO process_manual_list;
724 FND_MSG_PUB.Count_AND_Get
725 ( p_count => x_msg_count,
726 p_data => x_msg_data,
727 p_encoded => FND_API.G_FALSE
728 );
729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
730 x_return_status := FND_API.g_ret_sts_unexp_error ;
731 ROLLBACK TO process_manual_list;
732 FND_MSG_PUB.Count_AND_Get
733 ( p_count => x_msg_count,
734 p_data => x_msg_data,
735 p_encoded => FND_API.G_FALSE
736 );
737 WHEN OTHERS THEN
738 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
739 ROLLBACK TO process_manual_list;
740 IF (AMS_LOG_PROCEDURE_ON) THEN
741 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name
742 ,' In Others exception handling ');
743 END IF;
744
745 IF (AMS_LOG_EXCEPTION_ON) THEN
746 AMS_UTILITY_PVT.debug_message(AMS_LOG_EXCEPTION,g_module_name||'.'||l_api_name
750 THEN
747 ,'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
748 END IF;
749 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
751 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
752 END IF;
753 FND_MSG_PUB.Count_AND_Get
754 ( p_count => x_msg_count,
755 p_data => x_msg_data,
756 p_encoded => FND_API.G_FALSE
757 );
758
759 END;
760
761 --Wrapper API added for supporting contact list created from OSO
762 --bug 4348939
763 PROCEDURE process_manual_list(
764 p_api_version IN NUMBER,
765 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
766 p_commit IN VARCHAR2 := FND_API.g_false,
767 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
768 x_return_status OUT NOCOPY VARCHAR2,
769 x_msg_count OUT NOCOPY NUMBER,
770 x_msg_data OUT NOCOPY VARCHAR2,
771 p_list_header_id in NUMBER,
772 p_primary_key_tbl IN JTF_NUMBER_TABLE,--primary_key_Tbl_Type ,
773 p_master_type in VARCHAR2
774 -- , x_added_entry_count OUT NOCOPY NUMBER --- manual entries changes added:musman
775 ) IS
776
777 x_added_entry_count NUMBER;
778
779 BEGIN
780
781 process_manual_list(
782 p_api_version,
783 p_init_msg_list,
784 p_commit,
785 p_validation_level,
786 x_return_status,
787 x_msg_count,
788 x_msg_data,
789 p_list_header_id,
790 p_primary_key_tbl,
791 p_master_type,
792 x_added_entry_count --- manual entries changes added:musman
793 );
794
795 END process_manual_list;
796
797
798 PROCEDURE process_employee_list(
799 p_api_version IN NUMBER,
800 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
801 p_commit IN VARCHAR2 := FND_API.g_false,
802 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
803 x_return_status OUT NOCOPY VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_msg_data OUT NOCOPY VARCHAR2,
806 p_list_header_id in NUMBER,
807 p_primary_key_tbl IN primary_key_Tbl_Type ,
808 p_last_name_tbl IN varchar2_Tbl_Type ,
809 p_first_name_tbl IN varchar2_Tbl_Type ,
810 p_email_tbl IN varchar2_Tbl_Type ,
811 p_master_type in VARCHAR2
812 ) IS
813 l_api_name constant varchar2(30) := 'process_employee_list';
814 l_list_header_id number;
815 l_api_version CONSTANT NUMBER := 1.0;
816
817 cursor c_count_list_entries(cur_p_list_header_id number) is
818 select sum(decode(enabled_flag,'N',0,1)),
819 sum(decode(enabled_flag,'Y',0,1)),
820 sum(1),
821 sum(decode(part_of_control_group_flag,'Y',1,0)),
822 sum(decode(marked_as_random_flag,'Y',1,0)),
823 sum(decode(marked_as_duplicate_flag,'Y',1,0)),
824 sum(decode(manually_entered_flag,
825 'Y',decode(enabled_flag,'Y','1',0),
826 0))
827 from ams_list_entries
828 where list_header_id = cur_p_list_header_id ;
829
830 l_master_type_id number;
831 l_source_type_code varchar2(30);
832
833
834 l_min_rows number;
835 l_new_status varchar2(30);
836 l_new_status_id number;
837 l_no_of_rows_duplicates number;
838 l_no_of_rows_in_list number;
839 l_no_of_rows_active number;
840 l_no_of_rows_inactive number;
841 l_no_of_rows_manually_entered number;
842 l_no_of_rows_in_ctrl_group number;
843 l_no_of_rows_random number;
844
845 l_created_by NUMBER; --batoleti added this var. For bug# 6688996
846
847 /* batoleti. Bug# 6688996. Added the below cursor */
848 CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
849 SELECT created_by
850 FROM ams_list_headers_all
851 WHERE list_header_id= x_list_header_id;
852
853
854 BEGIN
855
856 SAVEPOINT process_employee_list;
857
858 IF FND_API.to_boolean(p_init_msg_list) THEN
859 FND_MSG_PUB.initialize;
860 END IF;
861
862 IF NOT FND_API.compatible_api_call
863 (
864 l_api_version,
865 p_api_version,
866 l_api_name,
867 g_pkg_name
868 )
869 THEN
870 RAISE FND_API.g_exc_unexpected_error;
871 END IF;
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873
874 -- batoleti coding starts for bug# 6688996
875 l_created_by := 0;
876
877 OPEN cur_get_created_by(p_list_header_id);
878
879 FETCH cur_get_created_by INTO l_created_by;
880 CLOSE cur_get_created_by;
881
882 -- batoleti coding ends for bug# 6688996
883
884 FORALL I in p_primary_key_tbl.first .. p_primary_key_tbl.last
885 INSERT INTO ams_List_Entries
886 ( list_entry_id ,
887 last_update_date ,
888 last_updated_by ,
889 creation_date ,
890 created_by ,
891 last_update_login ,
892 list_header_id ,
893 list_select_action_id ,
894 arc_list_select_action_from ,
895 list_select_action_from_name ,
896 source_code ,
897 source_code_for_id ,
898 arc_list_used_by_source ,
899 pin_code ,
900 list_entry_source_system_id ,
901 list_entry_source_system_type ,
902 view_application_id ,
903 manually_entered_flag ,
904 marked_as_random_flag ,
905 marked_as_duplicate_flag ,
906 part_of_control_group_flag ,
907 exclude_in_triggered_list_flag ,
908 enabled_flag,
909 marked_flag ,
910 object_version_number,
911 first_name,
912 last_name,
913 email_address
914 )
915 ( select ams_list_entries_s.nextval,
916 sysdate,
917 fnd_global.user_id,
918 sysdate,
919 nvl(l_created_by, fnd_global.user_id),
920 fnd_global.conc_login_id,
921 p_list_header_id,
922 0,
923 'NONE',
924 'NONE',
925 'NONE',
926 0,
927 p_list_header_id,
928 ams_list_entries_s.currval,
929 p_primary_key_tbl(i) ,
930 p_master_type,
931 530,
932 'Y',
933 'N',
934 'N',
935 'N',
936 'N',
937 'Y',
938 'Y',
939 1,
940 p_first_name_tbl(i),
941 p_last_name_tbl(i),
942 p_email_tbl(i)
943 from dual
944 where not exists (select 'x'
945 from ams_list_entries
946 where list_entry_source_system_id = p_primary_key_tbl(i)
947 and list_header_id = p_list_header_id));
948
949
950
951 open c_count_list_entries(p_list_header_id);
952 fetch c_count_list_entries
953 into l_no_of_rows_active ,
954 l_no_of_rows_inactive ,
955 l_no_of_rows_in_list ,
956 l_no_of_rows_in_ctrl_group ,
957 l_no_of_rows_random ,
958 l_no_of_rows_duplicates ,
959 l_no_of_rows_manually_entered ;
960 close c_count_list_entries;
961
962
963 SELECT nvl(no_of_rows_min_requested,0)
964 INTO l_min_rows
965 FROM ams_list_headers_all
966 WHERE list_header_id = p_list_header_id;
967
968 if l_min_rows > l_no_of_rows_active then
969 l_new_status := 'DRAFT';
970 l_new_status_id := 300;
971 else
972 l_new_status := 'AVAILABLE';
973 l_new_status_id := 303;
974 end if;
975 update ams_list_headers_all
976 set no_of_rows_in_list = l_no_of_rows_in_list,
977 no_of_rows_active = l_no_of_rows_active,
978 no_of_rows_inactive = l_no_of_rows_inactive,
979 no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
980 no_of_rows_random = l_no_of_rows_random,
981 no_of_rows_duplicates = l_no_of_rows_duplicates,
982 no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
983 status_code = l_new_status,
984 user_status_id = l_new_status_id,
985 status_date = sysdate
986 WHERE list_header_id = p_list_header_id;
987
988
989 IF x_return_status = fnd_api.g_ret_sts_error THEN
990 RAISE FND_API.g_exc_error;
991 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
992 RAISE FND_API.g_exc_unexpected_error;
993 END IF;
994 IF p_commit = FND_API.g_true then
995 COMMIT WORK;
996 END IF;
997
998 FND_MSG_PUB.Count_AND_Get
999 ( p_count => x_msg_count,
1000 p_data => x_msg_data,
1001 p_encoded => FND_API.G_FALSE );
1002
1003 EXCEPTION
1004 WHEN FND_API.G_EXC_ERROR THEN
1005 x_return_status := FND_API.g_ret_sts_error ;
1006 ROLLBACK TO process_employee_list;
1007 FND_MSG_PUB.Count_AND_Get
1008 ( p_count => x_msg_count,
1009 p_data => x_msg_data,
1010 p_encoded => FND_API.G_FALSE
1011 );
1012 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1013 x_return_status := FND_API.g_ret_sts_unexp_error ;
1014 ROLLBACK TO process_employee_list;
1015 FND_MSG_PUB.Count_AND_Get
1016 ( p_count => x_msg_count,
1017 p_data => x_msg_data,
1018 p_encoded => FND_API.G_FALSE
1019 );
1020 WHEN OTHERS THEN
1021 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1022 ROLLBACK TO process_employee_list;
1023 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1024 THEN
1025 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1026 END IF;
1027 FND_MSG_PUB.Count_AND_Get
1028 ( p_count => x_msg_count,
1029 p_data => x_msg_data,
1030 p_encoded => FND_API.G_FALSE
1031 );
1032
1033 END;
1034 END AMS_MANUAL_LIST_GEN ;