[Home] [Help]
PACKAGE BODY: APPS.IES_SVY_CREATE_INIT_RECORDS
Source
1 Package Body IES_SVY_CREATE_INIT_RECORDS AS
2 /* $Header: iescrirb.pls 120.1 2006/02/28 12:00:54 prkotha noship $ */
3 ----------------------------------------------------------------------------------------------------------
4 -- Procedure
5 -- Create_Initial_Ques_Freq
6
7 -- PURPOSE
8 -- Create Initial Rows in the question frequency table
9 --
10 -- PARAMETERS
11 -- deployment_id - survey deployment
12 -- NOTES
13 -- created rrsundar 05/17/2001
14 ---------------------------------------------------------------------------------------------------------
15 PROCEDURE CREATE_INITIAL_QUES_FREQ
16 (
17 p_deployment_id IN NUMBER
18 ) IS
19 l_errbuf VARCHAR2(2000);
20 l_retcode NUMBER;
21 l_error_msg VARCHAR2(2000);
22
23 BEGIN
24 SAVEPOINT Create_Initial_Freq;
25 INSERT INTO ies_svy_ques_data_v(
26 survey_name,
27 survey_id,
28 cycle_name,
29 survey_cycle_id,
30 survey_deployment_id,
31 deployment_name,
32 dscript_id,
33 dscript_name,
34 panel_id,
35 panel_name,
36 question_id,
37 question_name,
38 question_type,
39 question_order,
40 lookup_id,
41 answer_id,
42 answer_order,
43 answer_value,
44 answer_display_value,
45 ANSWER_COUNT)
46 (
47 SELECT issv.survey_name,
48 issv.survey_id,
49 iscv.survey_cycle_name,
50 iscv.survey_cycle_id,
51 isdv.survey_deployment_id,
52 isdv.deployment_name,
53 issv.dscript_id,
54 ids.dscript_name,
55 ip.panel_id,
56 ip.panel_label,
57 iq.question_id,
58 iq.question_label,
59 iqt.question_type,
60 iq.question_order,
61 il.lookup_id,
62 ia.answer_id,
63 ia.answer_order,
64 ia.answer_value,
65 ia.answer_display_value,
66 0 answer_count
67 from ies_svy_surveys_v issv,
68 ies_svy_cycles_v iscv,
69 ies_svy_deplyments_v isdv,
70 ies_deployed_scripts ids,
71 ies_panels ip,
72 ies_questions iq,
73 ies_question_types iqt,
74 ies_lookups il,
75 ies_answers ia
76 WHERE issv.dscript_id = ids.dscript_id
77 AND ids.active_status = 1
78 AND ids.dscript_id = ip.dscript_id
79 AND ip.active_status = 1
80 AND ip.panel_id = iq.panel_id
81 AND iq.active_status = 1
82 AND iq.lookup_id is not null
83 AND iq.lookup_id = il.lookup_id
84 AND iq.question_type_id = iqt.question_type_id
85 AND il.lookup_id = ia.lookup_id
86 AND ia.active_status = 1
87 AND isdv.survey_cycle_id = iscv.survey_cycle_id
88 AND iscv.survey_id = issv.survey_id
89 AND isdv.survey_deployment_id = p_deployment_id
90 UNION
91 SELECT issv.survey_name,
92 issv.survey_id,
93 iscv.survey_cycle_name,
94 iscv.survey_cycle_id,
95 isdv.survey_deployment_id,
96 isdv.deployment_name,
97 issv.dscript_id,
98 ids.dscript_name,
99 ip.panel_id,
100 ip.panel_label,
101 iq.question_id,
102 iq.question_label,
103 iqt.question_type,
104 iq.question_order,
105 iq.lookup_id,
106 0 answer_id,
107 0 answer_order,
108 ' ' answer_value,
109 ' ' answer_display_value,
110 0 answer_count
111 from ies_svy_surveys_v issv,
112 ies_svy_cycles_v iscv,
113 ies_svy_deplyments_v isdv,
114 ies_deployed_scripts ids,
115 ies_panels ip,
116 ies_questions iq,
117 ies_question_types iqt
118 WHERE issv.dscript_id = ids.dscript_id
119 AND ids.dscript_id = ip.dscript_id
120 AND ids.active_status = 1
121 AND ip.panel_id = iq.panel_id
122 AND ip.active_status = 1
123 AND not exists (select '*' from ies_answers where ies_answers.lookup_id = iq.lookup_id )
124 AND iq.question_type_id = iqt.question_type_id
125 AND iq.active_status = 1
126 AND isdv.survey_cycle_id = iscv.survey_cycle_id
127 AND isdv.survey_deployment_id = p_deployment_id
128 AND iscv.survey_id = issv.survey_id);
129 Exception
130 WHEN OTHERS THEN
131 FND_MESSAGE.SET_NAME('IES', 'IES_SVY_INIT_QUES_FREQ_ERROR');
132 l_error_msg := FND_MESSAGE.GET;
133 fnd_file.put_line(fnd_file.log, l_error_msg);
134 l_ERRBUF := l_error_msg;
135 l_RETCODE := -1;
136 ROLLBACK to Create_Initial_Freq;
137
138
139 END CREATE_INITIAL_QUES_FREQ;
140
141 ----------------------------------------------------------------------------------------------------------
142 -- Procedure
143 -- Create_Initial_List_Summary
144 -- PURPOSE Create Initial Rows in List Summary
145 --
146 --
147 -- PARAMETERS
148 -- deployment_id - survey deployment
149 -- NOTES
150 -- created rrsundar 05/17/2001
151 ---------------------------------------------------------------------------------------------------------
152 PROCEDURE CREATE_INITIAL_LIST_SUMMARY
153 (
154 p_deployment_id IN NUMBER
155 ) IS
156 l_errbuf VARCHAR2(2000);
157 l_retcode NUMBER;
158 l_error_msg VARCHAR2(2000);
159 BEGIN
160 SAVEPOINT Create_List_Summary;
161
162
163 INSERT INTO ies_svy_list_summary_v
164 (survey_id,
165 survey_name,
166 survey_cycle_id,
167 survey_cycle_name,
168 survey_deployment_id,
169 deployment_name,
170 target_response_percent,
171 list_header_id,
172 list_name,
173 response_date,
174 no_sent,
175 no_responses,
176 no_errors)
177 ( SELECT
178 issv.survey_id,
179 issv.survey_name,
180 isdv.survey_cycle_id,
181 iscv.survey_cycle_name,
182 islev.survey_deployment_id,
183 isdv.deployment_name,
184 isdv.min_responses_for_close,
185 ialh.list_header_id,
186 ialh.list_name,
187 null,
188 count(islev.survey_list_entry_id),
189 0,
190 0
191 FROM
192 ies_svy_list_entries_v islev,
193 ies_svy_cycles_v iscv,
194 ies_svy_deplyments_v isdv,
195 ies_svy_surveys_v issv,
196 ams_list_headers_all ialh,
197 ams_list_entries iale
198 WHERE isdv.survey_deployment_id = p_deployment_id
199 AND islev.survey_deployment_id = isdv.survey_deployment_id
200 AND isdv.survey_cycle_id = iscv.survey_cycle_id
201 AND iscv.survey_id = issv.survey_id
202 AND islev.list_entry_id = iale.list_entry_id
203 AND iale.list_header_id = ialh.list_header_id
204 GROUP BY
205 issv.survey_id,
206 issv.survey_name,
207 isdv.survey_cycle_id,
208 iscv.survey_cycle_name,
209 islev.survey_deployment_id,
210 isdv.deployment_name,
211 isdv.min_responses_for_close,
212 ialh.list_header_id,
213 ialh.list_name,
214 null,
215 0,
216 0
217 );
218 Exception
219 WHEN OTHERS THEN
220 FND_MESSAGE.SET_NAME('IES', 'IES_SVY_INIT_LIST_SUMM_ERROR');
221 l_error_msg := FND_MESSAGE.GET;
222 fnd_file.put_line(fnd_file.log, l_error_msg);
223 l_ERRBUF := l_error_msg;
224 l_RETCODE := -1;
225 ROLLBACK TO Create_List_Summary;
226
227 END CREATE_INITIAL_LIST_SUMMARY;
228
229 ----------------------------------------------------------------------------------------------------------
230 -- Procedure
231 -- CREATE_INITIAL_RECORDS
232
233 -- PURPOSE
234 -- Create Initial Records in Summary Tables.
235 --
236 -- PARAMETERS
237 -- deployment_id - survey deployment
238 -- NOTES
239 -- created vacharya 05/17/2001
240 ---------------------------------------------------------------------------------------------------------
241
242 PROCEDURE CREATE_INITIAL_RECORDS(
243 -- errbuf OUT VARCHAR2 ,
244 -- retcode OUT NUMBER ,
245 p_deployment_id IN NUMBER
246 )
247 IS
248 l_error_msg VARCHAR2(2000);
249 l_list_header_id NUMBER;
250 l_count NUMBER := 0;
251 BEGIN
252 SAVEPOINT CREATE_INITIAL_RECORDS;
253
254 Create_Initial_Ques_Freq(p_deployment_id);
255
256 BEGIN
257 SELECT list_header_id
258 INTO l_list_header_id
259 FROM ies_svy_deplyments_all
260 WHERE SURVEY_DEPLOYMENT_ID = p_deployment_id;
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 RAISE;
264 END;
265
266
267 select 1 into l_count
268 from ies_svy_deplyments_v
269 where survey_deployment_id = p_deployment_id;
270
271 if (l_count > 0) then
272 Create_Initial_List_Summary(p_deployment_id);
273 end if;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 FND_MESSAGE.SET_NAME('IES', 'IES_SVY_ERROR_UPDT_INIT_TABLE');
278 l_error_msg := FND_MESSAGE.GET;
279 fnd_file.put_line(fnd_file.log, l_error_msg);
280 -- ERRBUF := l_error_msg;
281 -- RETCODE := -1;
282 ROLLBACK TO CREATE_INITIAL_RECORDS;
283
284 END CREATE_INITIAL_RECORDS;
285 END IES_SVY_CREATE_INIT_RECORDS;