[Home] [Help]
PACKAGE BODY: APPS.IES_SURVEY_SUMMARY
Source
1 Package Body IES_SURVEY_SUMMARY AS
2 /* $Header: iessummb.pls 120.1 2005/06/16 11:15:48 appldev $ */
3 /*==========================================================================+
4 | PROCEDURES. |
5 | Compute_Summary. |
6 +==========================================================================*/
7
8
9 --global package variables.
10 g_sqlerrm varchar2(500);
11 g_sqlcode varchar2(500);
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):='IES_SURVEY_SUMMARY';
14 G_FILE_NAME CONSTANT VARCHAR2(12):='iessummb.pls';
15
16 ----------------------------------------------------------------------------------------------------------
17 -- Procedure
18 -- Compute_Summary
19
20 -- PURPOSE
21 -- Summarize IES_QUESTION_DATA and IES_ANSWER_DATA
22 --
23 -- PARAMETERS
24 -- survey_id - survey deployment
25 -- list_entry_id - list entry identifier
26 -- template_id - fulfillment template identifier
27 -- NOTES
28 -- created rrsundar 05/03/2000
29 ---------------------------------------------------------------------------------------------------------
30 Procedure Compute_Summary( p_deployment_id in number := NULL) is
31
32 l_return_number NUMBER := NULL;
33 l_return_status VARCHAR2(1) := NULL;
34 l_msg_count NUMBER := NULL;
35 l_msg_data VARCHAR2(2000) := NULL;
36 l_cycle_id NUMBER := NULL;
37 l_survey_id NUMBER := NULL;
38 l_survey_cycle_id NUMBER := NULL;
39 l_survey_deployment_id NUMBER := NULL;
40 l_survey_status VARCHAR2(30) := NULL;
41 l_deployment_status VARCHAR2(30) := NULL;
42 l_survey_name VARCHAR2(240) := NULL;
43 l_survey_cycle_name VARCHAR2(240) := NULL;
44 l_media_type_code VARCHAR2(240) := NULL;
45 l_list_header_id NUMBER := NULL;
46 l_list_name VARCHAR2(240) := NULL;
47 l_deployment_name VARCHAR2(240) := NULL;
48 l_total_no_sent NUMBER := NULL;
49 l_total_no_errors NUMBER := NULL;
50 l_total_no_responses_recd NUMBER := NULL;
51 l_total_no_abandoned NUMBER := NULL;
52 l_total_no_aborted NUMBER := NULL;
53 l_deploy_date DATE;
54 l_response_end_date DATE;
55 l_deployment_id NUMBER := NULL;
56
57 cursor cdepid is
58 select survey_deployment_id
59 from ies_svy_summary_stats_v
60 where survey_deployment_id = p_deployment_id;
61
62 Begin
63
64 select iscv.survey_id,
65 issv.survey_name,
66 issv.survey_status_code,
67 isdv.survey_cycle_id,
68 iscv.survey_cycle_name,
69 isdv.media_type_code,
70 alsh.list_header_id,
71 isdv.deployment_status_code,
72 alsh.list_name,
73 isdv.deploy_date,
74 isdv.response_end_date,
75 isdv.deployment_name
76 into
77 l_survey_id,
78 l_survey_name,
79 l_survey_status,
80 l_survey_cycle_id,
81 l_survey_cycle_name,
82 l_media_type_code,
83 l_list_header_id,
84 l_deployment_status,
85 l_list_name,
86 l_deploy_date,
87 l_response_end_date,
88 l_deployment_name
89 from ies_svy_deplyments_v isdv, ams_list_headers_all alsh, ies_svy_cycles_v iscv, ies_svy_surveys_v issv
90 where isdv.survey_deployment_id = p_deployment_id
91 and isdv.list_header_id = alsh.list_header_id(+)
92 and isdv.survey_cycle_id = iscv.survey_cycle_id
93 and iscv.survey_id = issv.survey_id;
94
95
96 select count(decode(response_status, 'COMPLETE', 1,0)),
97 count(decode(response_status, 'ABANDONED',1,0)),
98 count(decode(response_status, 'ABORTED', 1, 0))
99 into
100 l_total_no_responses_recd,
101 l_total_no_abandoned,
102 l_total_no_aborted
103 from ies_svy_resp_entries_v
104 where survey_deployment_id = p_deployment_id;
105
106
107 select count(*),
108 sum(decode(error_code, 'YES', 1, 0))
109 into
110 l_total_no_sent,
111 l_total_no_errors
112 from ies_svy_list_entries_v
113 where survey_deployment_id = p_deployment_id;
114
115 if (l_total_no_sent is null) then
116 l_total_no_sent := 0;
117 end if;
118 if (l_total_no_errors is null) then
119 l_total_no_errors := 0;
120 end if;
121
122 open cdepid;
123 fetch cdepid into l_deployment_id;
124 if(cdepid%NOTFOUND)then
125 close cdepid;
126 insert into ies_svy_summary_stats_v
127 (survey_id,
128 survey_cycle_id,
129 survey_deployment_id,
130 survey_deployment_name,
131 survey_name,
132 survey_status,
133 survey_cycle_name,
134 media_type_code,
135 deploy_date,
136 response_end_date,
137 list_header_id,
138 deployment_status,
139 list_name,
140 object_version_number,
141 total_no_sent,
142 total_no_errors,
143 total_no_responses_recd,
144 total_abandoned,
145 total_aborted,
146 refresh_date,
147 f_deletedflag)
148 values
149 (l_survey_id,
150 l_survey_cycle_id,
151 p_deployment_id,
152 l_deployment_name,
153 l_survey_name,
154 l_survey_status,
155 l_survey_cycle_name,
156 l_media_type_code,
157 l_deploy_date,
158 l_response_end_date,
159 l_list_header_id,
160 l_deployment_status,
161 l_list_name,
162 1,
163 nvl(l_total_no_sent,0),
164 nvl(l_total_no_errors,0),
165 nvl(l_total_no_responses_recd,0),
166 nvl(l_total_no_abandoned, 0),
167 nvl(l_total_no_aborted, 0),
168 sysdate,
169 null);
170
171 else
172 update ies_svy_summary_stats_v
173 set total_no_sent = nvl(l_total_no_sent,0) ,
174 total_no_errors = nvl(l_total_no_errors,0),
175 total_no_responses_recd = nvl(l_total_no_responses_recd,0),
176 total_abandoned = nvl(l_total_no_abandoned,0),
177 total_aborted = nvl(l_total_no_aborted, 0),
178 refresh_date = sysdate
179 where survey_deployment_id = p_deployment_id;
180 close cdepid;
181 end if;
182 End;
183
184
185 Procedure Compute_Summary_Non_List( p_deployment_id in number := NULL) is
186
187 l_return_number NUMBER := NULL;
188 l_return_status VARCHAR2(1) := NULL;
189 l_msg_count NUMBER := NULL;
190 l_msg_data VARCHAR2(2000) := NULL;
191 l_cycle_id NUMBER := NULL;
192 l_survey_id NUMBER := NULL;
193 l_survey_cycle_id NUMBER := NULL;
194 l_survey_deployment_id NUMBER := NULL;
195 l_survey_name VARCHAR2(240) := NULL;
196 l_survey_cycle_name VARCHAR2(240) := NULL;
197 l_survey_status VARCHAR2(30) := NULL;
198 l_deployment_status VARCHAR2(30) := NULL;
199 l_media_type_code VARCHAR2(240) := NULL;
200 l_deployment_name VARCHAR2(240) := NULL;
201 l_total_no_sent NUMBER := NULL;
202 l_total_no_errors NUMBER := NULL;
203 l_total_no_responses_recd NUMBER := NULL;
204 l_total_no_abandoned NUMBER := NULL;
205 l_total_no_aborted NUMBER := NULL;
206 l_deploy_date DATE;
207 l_response_end_date DATE;
208 l_deployment_id NUMBER := NULL;
209
210 cursor cdepid is
211 select survey_deployment_id
212 from ies_svy_summary_stats_v
213 where survey_deployment_id = p_deployment_id;
214
215 Begin
216 select iscv.survey_cycle_id,
217 iscv.survey_cycle_name,
218 issv.survey_id,
219 issv.survey_name,
220 issv.survey_status_code,
221 isdv.media_type_code,
222 isdv.deploy_date,
223 isdv.response_end_date,
224 isdv.deployment_name,
225 isdv.deployment_status_code
226 into l_survey_cycle_id,
227 l_survey_cycle_name,
228 l_survey_id,
229 l_survey_name,
230 l_survey_status,
231 l_media_type_code,
232 l_deploy_date,
233 l_response_end_date,
234 l_deployment_name,
235 l_deployment_status
236 from ies_svy_deplyments_v isdv,
237 ies_svy_cycles_v iscv,
238 ies_svy_surveys_v issv
239 where survey_deployment_id = p_deployment_id
240 and isdv.survey_cycle_id = iscv.survey_cycle_id
241 and iscv.survey_id = issv.survey_id;
242
243
244
245 select count(decode(response_status, 'COMPLETE', 1,0)),
246 count(decode(response_status, 'ABANDONED',1,0)),
247 count(decode(response_status, 'ABORTED', 1, 0))
248 into
249 l_total_no_responses_recd,
250 l_total_no_abandoned,
251 l_total_no_aborted
252 from ies_svy_resp_entries_v
253 where survey_deployment_id = p_deployment_id;
254
255 open cdepid;
256 fetch cdepid into l_deployment_id;
257 if(cdepid%NOTFOUND)then
258 close cdepid;
259 insert into ies_svy_summary_stats_v
260 (survey_id,
261 survey_cycle_id,
262 survey_deployment_id,
263 survey_deployment_name,
264 deployment_status,
265 survey_name,
266 survey_status,
267 survey_cycle_name,
268 media_type_code,
269 deploy_date,
270 response_end_date,
271 object_version_number,
272 total_no_errors,
273 total_abandoned,
274 total_aborted,
275 total_no_responses_recd,
276 refresh_date,
277 f_deletedflag)
278 values
279 (l_survey_id,
280 l_survey_cycle_id,
281 p_deployment_id,
282 l_deployment_name,
283 l_deployment_status,
284 l_survey_name,
285 l_survey_status,
286 l_survey_cycle_name,
287 l_media_type_code,
288 l_deploy_date,
289 l_response_end_date,
290 1,
291 nvl(l_total_no_errors,0),
292 nvl(l_total_no_abandoned, 0),
293 nvl(l_total_no_aborted, 0),
294 nvl(l_total_no_responses_recd,0),
295 sysdate,
296 null);
297
298 else
299 update ies_svy_summary_stats_v
300 set total_no_sent = nvl(l_total_no_sent,0) ,
301 total_no_errors = nvl(l_total_no_errors,0),
302 total_no_responses_recd = nvl(l_total_no_responses_recd,0),
303 total_abandoned = nvl(l_total_no_abandoned,0),
304 total_aborted = nvl(l_total_no_aborted, 0),
305 refresh_date = sysdate
306 where survey_deployment_id = p_deployment_id;
307 close cdepid;
308 end if;
309 End;
310
311
312 Procedure Summarize_Survey_Data(
313 ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
314 RETCODE OUT NOCOPY /* file.sql.39 change */ BINARY_INTEGER ,
315 p_cycle_id IN NUMBER)
316 IS
317 l_error_msg VARCHAR2(2000) := NULL;
318 l_retcode NUMBER := 0;
319 l_count NUMBER := 0;
320 l_flag NUMBER := 0;
321
322 cursor deployments is
323 select survey_deployment_id,
324 list_header_id
325 from ies_svy_deplyments_v
326 where survey_cycle_id = p_cycle_id;
327
328 begin
329
330 Check_Question_Type(p_error_msg => l_error_msg,
331 p_retcode => l_retcode,
332 p_cycle_id => p_cycle_id);
333
334 if (l_retcode = -1) then
335 fnd_file.put_line(fnd_file.log, l_error_msg);
336 ERRBUF := l_error_msg;
337 RETCODE := l_retcode;
338 return;
339 end if;
340
341
342 Update_Question_Frequency(p_error_msg => l_error_msg,
343 p_retcode => l_retcode,
344 p_cycle_id => p_cycle_id);
345 if (l_retcode = -1) then
346 fnd_file.put_line(fnd_file.log, l_error_msg);
347 ERRBUF := l_error_msg;
348 RETCODE := l_retcode;
349 return;
350 end if;
351
352
353 for dep_rec in deployments
354 loop
355 if (dep_rec.list_header_id is null) then
356 Compute_Summary_Non_List(dep_rec.survey_deployment_id);
357 else
358 l_flag := 1;
359 Compute_Summary(dep_rec.survey_deployment_id);
360 end if;
361 end loop;
362
363 if (l_flag = 1) then
364 Update_List_Entry_Summ(p_error_msg => l_error_msg,
365 p_retcode => l_retcode,
366 p_cycle_id => p_cycle_id);
367 end if;
368
369
370
371 if (l_retcode = -1) then
372 fnd_file.put_line(fnd_file.log, l_error_msg);
373 ERRBUF := l_error_msg;
374 RETCODE := l_retcode;
375 return;
376 end if;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 ERRBUF := l_error_msg;
381 RETCODE := l_retcode;
382
383 END Summarize_Survey_Data;
384
385 /*==========================================================================+
386 | PROCEDURES. |
387 | Update_Question_Frequency. |
388 | Updates the Summary data for List Based Surveys |
389 +==========================================================================*/
390
391 Procedure Update_Question_Frequency
392 (
393 p_error_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
394 p_retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
395 p_cycle_id IN NUMBER
396 ) IS
397
398 l_error_msg VARCHAR2(2000);
399 l_ret_code NUMBER := 0;
400
401 CURSOR anscountfree_cur IS
402 SELECT
403 issv.survey_id,
404 isdv.survey_deployment_id,
405 ids.dscript_id,
406 iq.panel_id,
407 iqd.question_id,
408 iqd.lookup_id,
409 0 answer_id,
410 count(decode(question_type, 'Checkbox', (decode(freeform_string, '1', 1, '0', 0)), 1)) answer_count
411 FROM
412 ies_svy_resp_entries_v isre,
413 ies_svy_deplyments_v isdv,
414 ies_svy_cycles_v iscv,
415 ies_svy_surveys_v issv,
416 ies_deployed_scripts ids,
417 ies_question_data iqd,
418 ies_questions iq,
419 ies_question_types iqt,
420 ies_panels ip
421 WHERE iscv.survey_cycle_id = P_CYCLE_ID
422 AND isre.survey_deployment_id = isdv.survey_deployment_id
423 AND isdv.survey_cycle_id = iscv.survey_cycle_id
424 AND iscv.survey_id = issv.survey_id
425 AND issv.dscript_id = ids.dscript_id
426 AND ids.active_status = 1
427 AND iqd.transaction_id = isre.transaction_id
428 AND iqd.question_id = iq.question_id
429 AND iq.active_status = 1
430 AND iq.question_type_id = iqt.question_type_id
431 AND iqt.question_type in ('Checkbox', 'Text Entry', 'Text Area')
432 AND iq.panel_id = ip.panel_id
436 issv.survey_id,
433 AND ip.active_status = 1
434 AND iqd.answer_id is null
435 GROUP BY
437 isdv.survey_deployment_id,
438 ids.dscript_id,
439 iq.panel_id,
440 iqd.question_id,
441 iqd.lookup_id,
442 answer_id;
443
444 CURSOR anscount_cur IS
445 select
446 issv.survey_id,
447 isdv.survey_deployment_id,
448 ids.dscript_id,
449 iq.panel_id,
450 iqd.question_id,
451 iqd.lookup_id,
452 ia.answer_id,
453 count(decode(question_type, 'Checkbox', (decode(freeform_string, '1', 1, '0', 0)), 1)) answer_count
454 from
455 ies_svy_resp_entries_v isre,
456 ies_svy_deplyments_v isdv,
457 ies_svy_cycles_v iscv,
458 ies_svy_surveys_v issv,
459 ies_deployed_scripts ids,
460 ies_question_data iqd,
461 ies_questions iq,
462 ies_question_types iqt,
463 ies_panels ip,
464 ies_answers ia
465 where iscv.survey_cycle_id = P_CYCLE_ID
466 and isre.survey_deployment_id = isdv.survey_deployment_id
467 and isdv.survey_cycle_id = iscv.survey_cycle_id
468 and iscv.survey_id = issv.survey_id
469 and issv.dscript_id = ids.dscript_id
470 and ids.active_status = 1
471 and iqd.transaction_id = isre.transaction_id
472 and iqd.question_id = iq.question_id
473 and iq.active_status = 1
474 and iq.question_type_id = iqt.question_type_id
475 AND iqt.question_type in ('Checkbox Group', 'Radio Button', 'Dropdown', 'Multiselect List')
476 and iq.panel_id = ip.panel_id
477 and ip.active_status = 1
478 and iqd.answer_id = ia.answer_id
479 and iqd.answer_id is not null
480 GROUP BY
481 issv.survey_id,
482 isdv.survey_deployment_id,
483 ids.dscript_id,
484 iq.panel_id,
485 iqd.question_id,
486 iqd.lookup_id,
487 ia.answer_id;
488 BEGIN
489 SAVEPOINT Create_Summary;
490
491 FOR anscountfree_rec IN anscountfree_cur LOOP
492 UPDATE ies_svy_ques_data_v
493 SET ANSWER_COUNT = anscountfree_rec.answer_count
494 where survey_id = anscountfree_rec.survey_id
495 and survey_deployment_id = anscountfree_rec.survey_deployment_id
496 and dscript_id = anscountfree_rec.dscript_id
497 and panel_id = anscountfree_rec.panel_id
498 and question_id = anscountfree_rec.question_id
499 and answer_id = anscountfree_rec.answer_id
500 and lookup_id = anscountfree_rec.lookup_id;
501 END LOOP;
502
503 FOR anscount_rec IN anscount_cur LOOP
504 UPDATE ies_svy_ques_data_v
505 SET ANSWER_COUNT = anscount_rec.answer_count
506 where survey_id = anscount_rec.survey_id
507 and survey_deployment_id = anscount_rec.survey_deployment_id
508 and dscript_id = anscount_rec.dscript_id
509 and panel_id = anscount_rec.panel_id
510 and question_id = anscount_rec.question_id
511 and lookup_id = anscount_rec.lookup_id
512 and answer_id = anscount_rec.answer_id;
513 END LOOP;
514 EXCEPTION
515 WHEN OTHERS THEN
516 FND_MESSAGE.SET_NAME('IES', 'IES_SVY_UPDATE_DEPLOY_STATUS');
517 l_error_msg := FND_MESSAGE.GET;
518 fnd_file.put_line(fnd_file.log, l_error_msg);
519 p_error_msg := l_error_msg;
520 p_retcode := -1;
521 ROLLBACK TO Create_Summary;
522 END Update_Question_Frequency;
523
524
525 Procedure Check_Question_Type
526 (
527 p_error_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
528 p_retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
529 p_cycle_id IN NUMBER
530 ) IS
531
532 l_error_msg VARCHAR2(2000);
533 l_ret_code NUMBER := 0;
534 l_ques_type_count NUMBER := 0;
535
536 BEGIN
537
538 SELECT count(question_id)
539 INTO l_ques_type_count
540 FROM IES_SVY_SURVEYS_V a,
541 IES_SVY_CYCLES_V b,
542 IES_DEPLOYED_SCRIPTS c,
543 IES_PANELS d,
544 IES_QUESTIONS e
545 WHERE b.survey_cycle_id = p_cycle_id
546 AND a.survey_id = b.survey_id
547 AND a.dscript_id = c.dscript_id
548 AND c.active_status = 1
549 AND c.dscript_id = d.dscript_id
550 AND d.active_status = 1
551 AND d.panel_id = e.panel_id
552 AND e.question_type_id is not null
553 AND e.active_status = 1;
554
555 IF (l_ques_type_count = 0) THEN
556 FND_MESSAGE.SET_NAME('IES', 'IES_SVY_QUESTION_TYPE_ERROR');
557 l_error_msg := FND_MESSAGE.GET;
558 fnd_file.put_line(fnd_file.log, l_error_msg);
559 p_error_msg := l_error_msg;
560 p_retcode := -1;
561 ELSE
562 p_retcode := 0;
563 END IF;
564
565 END Check_Question_Type;
566
567
568
569 /*==========================================================================+
570 | PROCEDURES. |
571 | Update_List_Entry_Summ. |
572 | Updates the Summary data for List Based Surveys |
573 +==========================================================================*/
574
575 PROCEDURE UPDATE_LIST_ENTRY_SUMM
576 (
577 p_error_msg OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
578 p_retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
579 p_cycle_id IN NUMBER
580 ) IS
581
582 l_error_msg VARCHAR2(2000);
583 l_ret_code NUMBER := 0;
584 l_exist_flag NUMBER := 0;
585
586 CURSOR replist_cur IS
587 SELECT
588 issv.survey_id,
589 issv.survey_name,
590 isdv.survey_cycle_id,
591 iscv.survey_cycle_name,
592 isrv.survey_deployment_id,
593 isdv.deployment_name,
594 trunc(isrv.response_collected_date) response_collected_date,
595 iale.list_header_id,
596 ialh.list_name,
597 count(distinct isrv.survey_list_entry_id) no_of_responses
598 FROM
599 ies_svy_resp_entries_v isrv,
600 ies_svy_list_entries_v islev,
601 ies_svy_cycles_v iscv,
602 ies_svy_deplyments_v isdv,
603 ies_svy_surveys_v issv,
604 ies_ams_list_entries_v iale,
605 ies_ams_list_headers_v ialh
606 WHERE iscv.survey_cycle_id = P_CYCLE_ID
607 AND isrv.survey_list_entry_id = islev.survey_list_entry_id
608 AND isrv.survey_deployment_id = isdv.survey_deployment_id
609 AND isdv.survey_cycle_id = iscv.survey_cycle_id
610 AND iscv.survey_id = issv.survey_id
611 AND islev.list_entry_id = iale.list_entry_id
612 AND iale.list_header_id = ialh.list_header_id
613 GROUP BY
614 iale.list_header_id,
615 ialh.list_name,
616 trunc(isrv.response_collected_date),
617 isdv.deployment_name,
618 isrv.survey_deployment_id,
619 iscv.survey_cycle_name,
620 isdv.survey_cycle_id,
621 issv.survey_name,
622 issv.survey_id;
623 BEGIN
624
625 -- The purpose of this procedure is to create a record to list the no_sent and target_response
626 -- percent in the summary table with a null response date for each list that is used in a
627 -- deployment in a survey.
628
629
630 SAVEPOINT List_Response;
631
632
633 FOR replist_rec IN replist_cur LOOP
634 begin
635 select 1
636 INTO l_exist_flag
637 FROM ies_svy_list_summary_v
638 WHERE list_header_id = replist_rec.list_header_id
639 and survey_deployment_id = replist_rec.survey_deployment_id
640 and response_date = replist_rec.response_collected_date
641 and survey_cycle_id = replist_rec.survey_cycle_id
642 and survey_id = replist_rec.survey_id;
643 exception
644 WHEN NO_DATA_FOUND THEN
645 l_exist_flag := 0;
646 end;
647
648 if l_exist_flag = 0 then
649 INSERT INTO ies_svy_list_summary_v
650 (survey_id,
651 survey_name,
652 survey_cycle_id,
653 survey_cycle_name,
654 survey_deployment_id,
655 deployment_name,
656 target_response_percent,
657 list_header_id,
658 list_name,
659 response_date,
660 no_sent,
661 no_responses,
662 no_errors)
663 values
664 (replist_rec.survey_id,
665 replist_rec.survey_name,
666 replist_rec.survey_cycle_id,
667 replist_rec.survey_cycle_name,
668 replist_rec.survey_deployment_id,
669 replist_rec.deployment_name,
670 0,
671 replist_rec.list_header_id,
672 replist_rec.list_name,
673 replist_rec.response_collected_date,
674 0,
675 replist_rec.no_of_responses,
676 0);
677 else
678 update ies_svy_list_summary_v
679 set no_responses = replist_rec.no_of_responses
680 WHERE
681 list_header_id = replist_rec.list_header_id
682 and survey_deployment_id = replist_rec.survey_deployment_id
683 and response_date = replist_rec.response_collected_date
684 and survey_cycle_id = replist_rec.survey_cycle_id
685 and survey_id = replist_rec.survey_id;
686 l_exist_flag := 0;
687 end if;
688 END LOOP;
689 EXCEPTION
690 WHEN OTHERS THEN
691 FND_MESSAGE.SET_NAME('IES', 'IES_SVY_UPDATE_DEPLOY_STATUS');
692 l_error_msg := FND_MESSAGE.GET;
693 fnd_file.put_line(fnd_file.log, l_error_msg);
694 p_error_msg := l_error_msg;
695 p_retcode := -1;
696 ROLLBACK TO List_Response;
697 END UPDATE_LIST_ENTRY_SUMM;
698
699 END IES_SURVEY_SUMMARY;