1 PACKAGE BODY AMS_List_Options_Pvt AS
2 /* $Header: amsvlopb.pls 120.6 2005/12/21 01:43 bmuthukr noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_List_Options_Pvt
7 -- Purpose
8 -- Created to move all the code related to optional processes
9 -- like random list generation, suppression, max size restriction
10 -- control group generation from the list generation engine code.
11 -- History
12 -- Created bmuthukr 19-Jul-2005.
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_LIST_OPTIONS_PVT';
19 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvlopb.pls';
20
21 CURSOR c_get_header_info(p_list_header_id in number) IS
22 SELECT *
23 FROM ams_list_headers_all
24 WHERE list_header_id = p_list_header_id;
25
26 CURSOR c_get_count (p_list_header_id IN number ) is
27 SELECT count(1)
28 FROM ams_list_entries
29 WHERE list_header_id = p_list_header_id
30 AND enabled_flag = 'Y';
31
32 TYPE g_entries_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
33 g_list_entries_id g_entries_table_type ;
34 g_list_entry_count number := 0;
35 g_list_header_info c_get_header_info%ROWTYPE;
36 g_log_level varchar2(100) := null;
37 g_msg_tbl g_msg_tbl_type;
38 g_count number := 0;
39
40 PROCEDURE WRITE_TO_ACT_LOG(p_msg_data in VARCHAR2,
41 p_arc_log_used_by in VARCHAR2 ,
42 p_log_used_by_id in number,
43 p_level in varchar2 default 'LOW')
44 IS
45
46 BEGIN
47 if g_log_level is not null then -- for remote no logging will be done..
48 if g_log_level = 'HIGH' and p_level = 'LOW' then
49 return;
50 end if;
51 g_msg_tbl(g_count) := p_msg_data;
52 g_count := g_count + 1;
53 else
54 --Not doing anything for logging messages from remote DB for now. Will do it later.
55 null;
56 end if;
57
58 exception
59 when others then
60 null; --will add later if reqd..
61 END WRITE_TO_ACT_LOG;
62
63 PROCEDURE CG_Gen_Process(errbuf OUT NOCOPY VARCHAR2,
64 retcode OUT NOCOPY VARCHAR2,
65 p_list_header_id IN NUMBER
66 ) is
67
68 X_RETURN_STATUS VARCHAR2(1);
69 X_MSG_COUNT NUMBER;
70 X_MSG_DATA VARCHAR2(200);
71 x_ctrl_grp_status varchar2(100);
72 begin
73
74 Control_Group_Generation(p_list_header_id => to_number(p_list_header_id),
75 p_log_level => 'LOW',
76 x_ctrl_grp_status => x_ctrl_grp_status,
77 x_return_status => x_return_status ,
78 x_msg_count => x_return_status,
79 x_msg_data => x_msg_data);
80 commit;
81
82 end;
83
84
85 PROCEDURE Control_Group_Generation(
86 p_list_header_id IN NUMBER,
87 p_log_level IN varchar2 DEFAULT NULL,
88 p_msg_tbl OUT NOCOPY AMS_LIST_OPTIONS_PVT.G_MSG_TBL_TYPE,
89 x_ctrl_grp_status OUT NOCOPY VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2) IS
93
94 begin
95 g_log_level := p_log_level;
96
97 Control_Group_Generation(p_list_header_id => p_list_header_id,
98 p_log_level => g_log_level,
99 x_ctrl_grp_status => x_ctrl_grp_status,
100 x_return_status => x_return_status ,
101 x_msg_count => x_return_status,
102 x_msg_data => x_msg_data);
103 commit;
104 p_msg_tbl := g_msg_tbl;
105 g_msg_tbl.delete;
106 exception
107 when others then
108 write_to_act_log('Error while executing control_group_generation '||sqlcode||' '||sqlerrm,'LIST',p_list_header_id,'LOW');
109 x_msg_data := 'Error while executing control_group_generation '||sqlcode||' '||sqlerrm;
110 x_return_status := 'E';
111 end;
112
113 PROCEDURE Control_Group_Generation(
114 p_list_header_id IN NUMBER,
115 p_log_level IN varchar2 DEFAULT NULL,
116 x_ctrl_grp_status OUT NOCOPY VARCHAR2,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2)
120 IS
121
122 l_total_random_rows number := 0;
123
124 CURSOR c_list_entries (p_list_header_id IN number ) is
125 SELECT list_entry_id
126 FROM ams_list_entries
127 WHERE list_header_id = p_list_header_id
128 AND enabled_flag = 'Y'
129 ORDER BY randomly_generated_number ;
130
131 /*cursor c_status_id(p_status_code in varchar2) is
132 select user_status_id
133 from ams_user_Statuses_vl
134 where system_status_code = p_status_code
135 and system_status_type = 'AMS_LIST_STATUS';*/
136
137 l_no_of_rows_duplicates number;
138 l_no_of_rows_in_list number;
139 l_no_of_rows_active number;
140 l_no_of_rows_inactive number;
141 l_no_of_rows_manually_entered number;
142 l_no_of_rows_in_ctrl_group number;
143 l_no_of_rows_random number;
144 l_no_of_rows_used number;
145 l_no_of_rows_suppressed number := 0;
146 l_no_of_rows_fatigued number := 0;
147 l_TCA_FAILED_RECORDS number;
148 l_status_id number;
149 cursor c_count_list_entries(p_list_header_id in number) is
150 select sum(decode(enabled_flag,'N',0,1)),
151 sum(decode(enabled_flag,'Y',0,1)),
152 sum(1),
153 sum(decode(part_of_control_group_flag,'Y',1,0)),
154 sum(decode(marked_as_random_flag,'Y',1,0)),
155 sum(decode(marked_as_duplicate_flag,'Y',1,0)),
156 sum(decode(manually_entered_flag,
157 'Y',decode(enabled_flag,'Y','1',0),
158 0)),
159 sum(decode(MARKED_AS_SUPPRESSED_FLAG,'Y',1,0)),
160 sum(decode(MARKED_AS_FATIGUED_FLAG,'Y',1,0)),
161 sum(decode(TCA_LOAD_STATUS,'ERROR',1,0))
162 from ams_list_entries
163 where list_header_id = p_list_header_id ;
164
165 l_sample_size number := 0;
166 l_status varchar2(100);
167 l_rows number := 0;
168 l_sc varchar2(100);
169 l_status_code varchar2(100) := null;
170
171 BEGIN
172 g_log_level := p_log_level;
173
174 -- fnd_file.put(1, 'Started control group generation procedure.');
175 -- fnd_file.new_line(1,1);
176 x_return_status := 'S';
177
178 open c_get_header_info(p_list_header_id);
179 fetch c_get_header_info into g_list_header_info;
180 close c_get_header_info;
181
182 if g_list_header_info.status_code_old is not null then
183 l_status_code := g_list_header_info.status_code_old;
184 else
185 l_status_code := g_list_header_info.status_code;
186 end if;
187
188 /*open c_status_id(l_status_code);
189 fetch c_status_id into l_status_id;
190 close c_status_id;*/
191
192 if l_status_code = 'DRAFT' then
193 l_status_id := 300;
194 elsif l_status_code = 'AVAILABLE' then
195 l_status_id := 303;
196 elsif l_status_code = 'GENERATING' then
197 l_status_id := 302;
198 elsif l_status_code = 'FAILED' then
199 l_status_id := 311;
200 elsif l_status_code = 'SCHEDULED' then
201 l_status_id := 301;
202 elsif l_status_code = 'ARCHIVED' then
203 l_status_id := 306;
204 elsif l_status_code = 'LOCKED' then
205 l_status_id := 304;
206 end if;
207
208 update ams_list_entries -- need this when the CG is generated seperately..
209 set part_of_control_group_flag = 'N',
210 enabled_flag = 'Y'
211 where part_of_control_group_flag = 'Y'
212 and enabled_flag = 'N'
213 and list_header_id = p_list_header_id;
214
215 if nvl(g_list_header_info.ctrl_gen_mode,'NONE') = 'NONE' then
216 write_to_act_log('CG option not selected.','LIST',p_list_header_id,'LOW');
217 else
218 write_to_act_log('Executing procedure control group generation.','LIST',p_list_header_id,'LOW');
219
220 open c_get_count(p_list_header_id);
221 fetch c_get_count into g_list_entry_count;
222 close c_get_count;
223
224 write_to_act_log(g_list_entry_count ||' are there in the list for control group generation','LIST',p_list_header_id,'LOW');
225
226 if nvl(g_list_header_info.ctrl_gen_mode,'NONE') = 'DEFBYCNT' then
227
228 l_total_random_rows := nvl(g_list_header_info.ctrl_random_nth_row_selection,0);
229 write_to_act_log('Control group generation option is count. No of rows to be made part of control group is '||l_total_random_rows,'LIST',p_list_header_id,'LOW');
230
231 elsif nvl(g_list_header_info.ctrl_gen_mode,'NONE') = 'DEFBYPCT' then
232
233 write_to_act_log('Control group generation option is percentage. % of rows to be made part of control group is '||g_list_header_info.ctrl_random_pct_row_selection,'LIST',p_list_header_id,'LOW');
234 l_total_random_rows := floor((g_list_entry_count * nvl(g_list_header_info.ctrl_random_pct_row_selection,0)) / 100);
235 write_to_act_log('No of rows to be made part of control group is '||l_total_random_rows,'LIST',p_list_header_id,'LOW');
236
237 elsif nvl(g_list_header_info.ctrl_gen_mode,'NONE') = 'DEFBYSAM' then
238
239 l_sample_size := (power(g_list_header_info.ctrl_conf_level,2) * g_list_header_info.ctrl_req_resp_rate * (100 - g_list_header_info.ctrl_req_resp_rate))/(power(g_list_header_info.ctrl_limit_of_error,2));
240 write_to_act_log('Control group generation option is statistical formula. Sample size of the control group is '||l_sample_size,'LIST',p_list_header_id,'LOW');
241 l_total_random_rows := l_sample_size/(1+((l_sample_size-1)/g_list_entry_count));
242 write_to_act_log('No of rows to be made part of control group is '||l_total_random_rows,'LIST',p_list_header_id,'LOW');
243
244 end if;
245
246 write_to_act_log('Total no of rows to be made part of control group is ' || to_char(l_total_random_rows), 'LIST', p_list_header_id,'LOW');
247
248 DBMS_RANDOM.initialize (TO_NUMBER (TO_CHAR (SYSDATE, 'SSSSDD')));
249
250 UPDATE ams_list_entries
251 SET randomly_generated_number = DBMS_RANDOM.random
252 WHERE list_header_id = p_list_header_id
253 AND enabled_flag = 'Y';
254
255 write_to_act_log('Randomly generated number assigned to '||sql%rowcount||' entries','LIST',p_list_header_id,'LOW');
256
257 DBMS_RANDOM.terminate;
258
259 if nvl(l_total_random_rows,0) > 0 then
260 OPEN c_list_entries (p_list_header_id);
261 FETCH c_list_entries BULK COLLECT INTO g_list_entries_id LIMIT l_total_random_rows;
262 CLOSE c_list_entries;
263
264 FORALL i in g_list_entries_id.FIRST .. g_list_entries_id.LAST
265 UPDATE ams_list_entries
266 SET part_of_control_group_flag = 'Y',
267 enabled_flag = 'N'
268 WHERE list_header_id = p_list_header_id
269 AND list_entry_id = g_list_entries_id(i);
270 write_to_act_log(sql%rowcount||' entries made part of the control group for this target group.', 'LIST', p_list_header_id,'HIGH');
271 else
272 write_to_act_log('0 entries made part of the control group for this target group.', 'LIST', p_list_header_id,'HIGH');
273 end if;
274 end if;
275
276 open c_count_list_entries(p_list_header_id);
277 fetch c_count_list_entries
278 into l_no_of_rows_active ,
279 l_no_of_rows_inactive ,
280 l_no_of_rows_in_list ,
281 l_no_of_rows_in_ctrl_group ,
282 l_no_of_rows_random ,
283 l_no_of_rows_duplicates ,
284 l_no_of_rows_manually_entered ,
285 l_no_of_rows_suppressed ,
286 l_no_of_rows_fatigued ,
287 l_TCA_FAILED_RECORDS;
288 close c_count_list_entries;
289
290 update ams_list_headers_all
291 set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
292 no_of_rows_active = nvl(l_no_of_rows_active,0),
293 no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
294 no_of_rows_in_ctrl_group = nvl(l_no_of_rows_in_ctrl_group,0),
295 no_of_rows_random = nvl(l_no_of_rows_random,0),
296 no_of_rows_duplicates = nvl(l_no_of_rows_duplicates,0),
297 no_of_rows_manually_entered = nvl(l_no_of_rows_manually_entered,0),
298 no_of_rows_suppressed = nvl(l_no_of_rows_suppressed,0),
299 no_of_rows_fatigued = nvl(l_no_of_rows_fatigued,0),
300 tca_failed_records = nvl(l_TCA_FAILED_RECORDS,0),
301 ctrl_status_code = decode(ctrl_gen_mode,'NONE','DRAFT','AVAILABLE'),
302 status_code = nvl(status_code_old,status_code),
303 last_update_date = sysdate,
304 user_status_id = l_status_id
305 where list_header_id = p_list_header_id;
306
307 update ams_list_headers_all
308 set status_code_old = null
309 where list_header_id = p_list_header_id;
310
311 -- Bug 4615797. bmuthukr. Need to update the ctrl grp cnt if cg size < cnt given.
312 if nvl(g_list_header_info.ctrl_gen_mode,'NONE') = 'DEFBYCNT' and nvl(g_list_header_info.ctrl_random_nth_row_selection,0) > 0 then
313 if nvl(l_no_of_rows_in_ctrl_group,0) < nvl(g_list_header_info.ctrl_random_nth_row_selection,0) then
314 write_to_act_log('Resetting the control group count to '||l_no_of_rows_in_ctrl_group||' since the given CG size is higher than actual size.', 'LIST', p_list_header_id,'HIGH');
315 update ams_list_headers_all
316 set ctrl_random_nth_row_selection = l_no_of_rows_in_ctrl_group
317 where list_header_id = p_list_header_id;
318 end if;
319 end if;
320
321
322 -- COMMIT;
323
324 exception
325 when others then
329 last_update_date = sysdate,
326 update ams_list_headers_all
327 set ctrl_status_code = 'FAILED',
328 status_code = nvl(status_code_old,status_code),
330 user_status_id = l_status_id
331 where list_header_id = p_list_header_id;
332
333 write_to_act_log(p_msg_data => 'Error while executing control_group_generation procedure '||sqlcode||' '||sqlerrm,
334 p_arc_log_used_by => 'LIST',
335 p_log_used_by_id => p_list_header_id,
336 p_level=>'HIGH');
337 x_msg_count := 1;
338 x_msg_data := 'Error during CG generation'||' '|| sqlcode || '-->'||sqlerrm;
339 x_return_status := 'E'; -- FND_API.G_RET_STS_ERROR ;
340
341 END Control_Group_Generation;
342
343 PROCEDURE random_list_entries (p_list_header_id in number,
344 x_return_status out nocopy varchar2,
345 x_msg_count out nocopy number,
346 x_msg_data out nocopy varchar2) IS
347
348 CURSOR c_list_entries (p_list_header_id IN number ) is
349 SELECT list_entry_id
350 FROM ams_list_entries
351 WHERE list_header_id = p_list_header_id
352 AND marked_as_random_flag = 'Y'
353 AND enabled_flag = 'N'
354 ORDER BY randomly_generated_number ;
355
356 l_total_random_rows number := 0;
357
358 BEGIN
359 write_to_act_log('Random list generation started', 'LIST', p_list_header_id,'LOW');
360
361 x_return_status := 'S';
362
363 open c_get_header_info(p_list_header_id);
364 fetch c_get_header_info into g_list_header_info;
365 close c_get_header_info;
366
367 open c_get_count(p_list_header_id);
368 fetch c_get_count into g_list_entry_count;
369 close c_get_count;
370
371 write_to_act_log(g_list_header_info.main_random_pct_row_selection||' % of rows to be generated randomly ','LIST',p_list_header_id,'LOW');
372 write_to_act_log(g_list_entry_count ||' are there in the list for random list generation','LIST',p_list_header_id,'LOW');
373
374 if nvl(g_list_header_info.main_random_pct_row_selection,0) between 1 and 100 then
375 l_total_random_rows := FLOOR ((g_list_entry_count * g_list_header_info.main_random_pct_row_selection) / 100);
376 else
377 write_to_act_log('Random % should be between 1 and 100. Could nt generate randomly. ' || to_char(l_total_random_rows), 'LIST', p_list_header_id,'HIGH');
378 end if;
379
380 write_to_act_log('Total no of rows to be generated randomly is ' || to_char(l_total_random_rows), 'LIST', p_list_header_id,'LOW');
381
382 DBMS_RANDOM.initialize (TO_NUMBER (TO_CHAR (SYSDATE, 'SSSSDD')));
383
384 UPDATE ams_list_entries
385 SET randomly_generated_number = DBMS_RANDOM.random,
386 marked_as_random_flag = 'Y',
387 enabled_flag = 'N'
388 WHERE list_header_id = p_list_header_id
389 AND enabled_flag = 'Y';
390 write_to_act_log('Randomly generated number assigned to '||sql%rowcount||' list entries','LIST',p_list_header_id,'LOW');
391
392 DBMS_RANDOM.terminate;
393
394 OPEN c_list_entries (p_list_header_id);
395 FETCH c_list_entries BULK COLLECT INTO g_list_entries_id LIMIT l_total_random_rows;
396 CLOSE c_list_entries;
397
398 FORALL i in g_list_entries_id.FIRST .. g_list_entries_id.LAST
399 UPDATE ams_list_entries
400 SET marked_as_random_flag = 'Y',
401 enabled_flag = 'Y'
402 WHERE list_header_id = g_list_header_info.list_header_id
403 AND list_entry_id = g_list_entries_id(i);
404 write_to_act_log(sql%rowcount||' entries generated randomly for this list', 'LIST', p_list_header_id,'HIGH');
405 write_to_act_log('Procedure random_list_entries executed successfully. ','LIST', p_list_header_id,'LOW');
406 exception
407 when others then
408 write_to_act_log('Error occurred while generating entries randomly ' || sqlerrm , 'LIST', p_list_header_id,'HIGH');
409 x_msg_count := 1;
410 x_msg_data := 'Error during random list generation'||' '|| sqlcode || '-->'||sqlerrm;
411 x_return_status := 'E';
412 END random_list_entries;
413
414 PROCEDURE CHECK_MAX_ENTRIES_DIST_PCT
415 (p_list_header_id in number,
416 x_return_status out nocopy varchar2,
417 x_msg_count out nocopy number,
418 x_msg_data out nocopy varchar2) is
419
420 l_sel_excess number := 0;
421
422 -- need to consider only the entries from ams_list_entries..no need to be specific about the selections.
423 CURSOR c_list_entries is
424 SELECT e.list_entry_id
425 FROM ams_list_entries e
426 WHERE e.list_header_id = p_list_header_id
427 AND e.enabled_flag ='Y';
428
429 BEGIN
430
431 x_return_status := 'S';
432
433 open c_get_header_info(p_list_header_id);
434 fetch c_get_header_info into g_list_header_info;
435 close c_get_header_info;
436
437 open c_get_count(p_list_header_id);
438 fetch c_get_count into g_list_entry_count;
439 close c_get_count;
440
441 write_to_act_log('Executing procedure check_max_entries_dist_pct to restrict list size based on max size','LIST', p_list_header_id, 'LOW');
442 write_to_act_log('No of enabled entries is ' || g_list_entry_count , 'LIST', p_list_header_id, 'LOW');
443
444 if (nvl(g_list_header_info.no_of_rows_max_requested,0) > 0 ) then -- already the row selection type is set to MAX.
445 if (g_list_entry_count <= g_list_header_info.no_of_rows_max_requested) then
446 write_to_act_log('No of max entries specified is greater than or equal to the no of available entries. No need to reduce the size.'
447 , 'LIST', p_list_header_id, 'HIGH');
448 else
449 l_sel_excess := g_list_entry_count - g_list_header_info.no_of_rows_max_requested;
450 open c_list_entries;
451 fetch c_list_entries bulk collect into g_list_entries_id limit l_sel_excess ;
452 close c_list_entries;
453 FORALL i in g_list_entries_id.FIRST .. g_list_entries_id.LAST
454 UPDATE ams_list_entries
455 SET enabled_flag = 'N'
456 WHERE list_header_id = p_list_header_id
457 AND list_entry_id = g_list_entries_id(i);
458 write_to_act_log(sql%rowcount||' entries disabled to restrict list size.' , 'LIST', p_list_header_id, 'HIGH');
459 end if;
460 end if;
461 write_to_act_log('Procedure check_max_entries_dist_pct executed.', 'LIST', p_list_header_id, 'HIGH');
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 write_to_act_log('Error while executing procedure check_max_entries_dist_pct '||sqlcode||' '||sqlerrm, 'LIST', p_list_header_id,'HIGH');
466 x_msg_count := 1;
467 x_msg_data := 'Error during executing max size restriction procedure. '||' '|| sqlcode || '-->'||sqlerrm;
468 x_return_status := 'E';
469 END CHECK_MAX_ENTRIES_DIST_PCT;
470
471 procedure apply_size_reduction
472 (p_list_header_id IN number,
473 p_log_level IN varchar2 DEFAULT NULL,
474 p_msg_tbl OUT NOCOPY AMS_LIST_OPTIONS_PVT.G_MSG_TBL_TYPE,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2) is
478
479 BEGIN
480 g_log_level := p_log_level;
481
482 apply_size_reduction(p_list_header_id => p_list_header_id,
483 p_log_level => g_log_level,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data);
487
488 p_msg_tbl := g_msg_tbl;
489 g_msg_tbl.delete;
490 END;
491
492 procedure apply_size_reduction
493 (p_list_header_id IN number,
494 p_log_level IN varchar2 DEFAULT NULL,
495 x_return_status OUT NOCOPY VARCHAR2,
496 x_msg_count OUT NOCOPY NUMBER,
497 x_msg_data OUT NOCOPY VARCHAR2) is
498
499 -- Added by bmuthukr to honor size reduction based on RANDOM/MAX
500 -- options in R12. since this would be applicable for both
501 -- list and target group, we will have this procedure and from
502 -- here either random/max procedure will be called.
503 -- this proc will be called from both generate_list and
504 -- generate_target_group procedures.
505
506 l_null_c varchar2(100) := null;
507 l_null_n number := null;
508
509 begin
510 g_log_level := p_log_level;
511
512 write_to_act_log('Executing procedure apply_size_reduction ','LIST',p_list_header_id,'LOW');
513
514 open c_get_header_info(p_list_header_id);
515 fetch c_get_header_info into g_list_header_info;
516 close c_get_header_info;
517
518
519 if nvl(g_list_header_info.row_selection_type,'x') = 'MAX' then
520 write_to_act_log('Max size option chosen for size reduction','LIST',p_list_header_id,'LOW');
521 --call max
522 check_max_entries_dist_pct(p_list_header_id => p_list_header_id,
523 x_return_status => x_return_status,
524 x_msg_count => x_msg_count,
525 x_msg_data => x_msg_data);
526
527 elsif nvl(g_list_header_info.row_selection_type,'x') = 'RANDOM' then
528 write_to_act_log('Random % option chosen for size reduction','LIST',p_list_header_id,'LOW');
529 --call random
530 random_list_entries(p_list_header_id => p_list_header_id,
531 x_return_status => x_return_status,
532 x_msg_count => x_msg_count,
533 x_msg_data => x_msg_data);
534 elsif nvl(g_list_header_info.row_selection_type,'x') = 'STANDARD' then
535 write_to_act_log('All records option chosen. Hence not restricting the size.','LIST',p_list_header_id,'LOW');
536 end if;
537
538 if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
539 write_to_act_log('Error in generating list entries randomly in remote instance.', 'LIST', p_list_header_id,'HIGH');
540 write_to_act_log('Error '||x_msg_data , 'LIST', p_list_header_id,'HIGH');
541 elsif nvl(x_return_status,'S') = 'S' then
542 write_to_act_log('Size restriction procedure executed successfully.', 'LIST', p_list_header_id,'HIGH');
543 end if;
544
545 exception
546 when others then
547 write_to_act_log('Error while executing procedure apply_size_reduction '||sqlcode||' '||sqlerrm,'LIST',p_list_header_id,'HIGH');
548 x_return_status := 'E';
549 x_msg_data := 'Error while executing procedure apply_size_reduction '||sqlcode||' '||sqlerrm;
550 x_msg_count := 1;
551 end apply_size_reduction;
552
553 END AMS_List_Options_Pvt;
[Home] [Help]