[Home] [Help]
PACKAGE BODY: APPS.HR_QUESTIONNAIRE_SWI
Source
1 PACKAGE BODY HR_QUESTIONNAIRE_SWI AS
2 /* $Header: hrqstswi.pkb 120.1.12020000.2 2013/01/07 06:26:35 venksund ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'hr_questionnaire_swi.';
7 --
8 --
9 Procedure delete_questionnaire_fields
10 (
11 p_questionnaire_template_id in number
12 );
13 --
14 -- ----------------------------------------------------------------------------
15 -- |-----------------------< update_questionnaire_recs >----------------------|
16 -- ----------------------------------------------------------------------------
17 /*
18 PROCEDURE update_questionnaire_recs(p_effective_date IN DATE
19 ,p_quest_tbl IN OUT NOCOPY HR_QUEST_TABLE
20 ,p_error_message OUT NOCOPY LONG
21 ,p_status OUT NOCOPY VARCHAR2) IS
22 BEGIN
23 FOR I in 1 .. p_quest_tbl.count LOOP
24 IF (not p_quest_tbl(I).questionnaire_template_id = -1) THEN
25 BEGIN
26 hr_qsn_upd.upd(p_quest_tbl(I).questionnaire_template_id
27 ,p_quest_tbl(I).available_flag
28 ,p_quest_tbl(I).object_version_number
29 ,p_effective_date);
30 EXCEPTION WHEN OTHERS THEN
31 p_error_message := SQLERRM||SQLCODE;
32 p_status := 'E';
33 END;
34 END IF;
35 END LOOP;
36 END update_questionnaire_recs;
37 */
38
39 -- ----------------------------------------------------------------------------
40 -- |-----------------------< create_questionnaire >---------------------------|
41 -- ----------------------------------------------------------------------------
42
43 Procedure create_questionnaire
44 (p_questionnaire_template_id in number
45 ,p_name in varchar2
46 ,p_text in CLOB
47 ,p_available_flag in varchar2
48 ,p_business_group_id in number
49 ,p_object_version_number out nocopy number
50 ,p_effective_date in date default hr_api.g_date
51 ,p_validate in number default hr_api.g_false_num
52 ,p_quest_type_code in varchar2
53 ,p_return_status out nocopy varchar2) is
54
55 --
56 -- Variables for API Boolean parameters
57 l_validate boolean;
58 --
59 -- Variables for IN/OUT parameters
60 --
61 -- Other variables
62 l_questionnaire_template_id number;
63 l_proc varchar2(72) := g_package ||'create_questionnaire';
64 Begin
65 hr_utility.set_location(' Entering:' || l_proc,10);
66 --
67 -- Issue a savepoint
68 --
69 savepoint create_questionnaire_swi;
70 --
71 -- Initialise Multiple Message Detection
72 --
73 hr_multi_message.enable_message_list;
74 --
75 -- Remember IN OUT parameter IN values
76 --
77 --
78 -- Convert constant values to their corresponding boolean value
79 --
80 l_validate :=
81 hr_api.constant_to_boolean
82 (p_constant_value => p_validate);
83 --
84 -- Register Surrogate ID or user key values
85 --
86 hr_qsn_ins.set_base_key_value
87 (p_questionnaire_template_id => p_questionnaire_template_id
88 );
89 --
90 -- Call API
91 --
92 hr_questionnaire_api.create_questionnaire
93 (p_questionnaire_template_id => l_questionnaire_template_id
94 ,p_name => p_name
95 ,p_text => p_text
96 ,p_available_flag => p_available_flag
97 ,p_business_group_id => p_business_group_id
98 ,p_object_version_number => p_object_version_number
99 ,p_quest_type_code => p_quest_type_code
100 ,p_effective_date => p_effective_date);
101 --
102 -- Convert API warning boolean parameter values to specific
103 -- messages and add them to Multiple Message List
104 --
105 --
106 -- Convert API non-warning boolean parameter values
107 --
108 --
109 -- Derive the API return status value based on whether
110 -- messages of any type exist in the Multiple Message List.
111 -- Also disable Multiple Message Detection.
112 --
113 If l_validate = TRUE Then
114 rollback to create_questionnaire_swi;
115 End If;
116 --
117 p_return_status := hr_multi_message.get_return_status_disable;
118 hr_utility.set_location(' Leaving:' || l_proc,20);
119 --
120 exception
121 when hr_multi_message.error_message_exist then
122 --
123 -- Catch the Multiple Message List exception which
124 -- indicates API processing has been aborted because
125 -- at least one message exists in the list.
126 --
127 rollback to create_questionnaire_swi;
128 --
129 -- Reset IN OUT parameters and set OUT parameters
130 --
131 p_object_version_number := null;
132 p_return_status := hr_multi_message.get_return_status_disable;
133 hr_utility.set_location(' Leaving:' || l_proc, 30);
134 when others then
135 --
136 -- When Multiple Message Detection is enabled catch
137 -- any Application specific or other unexpected
138 -- exceptions. Adding appropriate details to the
139 -- Multiple Message List. Otherwise re-raise the
140 -- error.
141 --
142 rollback to create_questionnaire_swi;
143 if hr_multi_message.unexpected_error_add(l_proc) then
144 hr_utility.set_location(' Leaving:' || l_proc,40);
145 raise;
146 end if;
147 --
148 -- Reset IN OUT and set OUT parameters
149 --
150 p_object_version_number := null;
151 p_return_status := hr_multi_message.get_return_status_disable;
152 hr_utility.set_location(' Leaving:' || l_proc,50);
153 end create_questionnaire;
154
155 -- ----------------------------------------------------------------------------
156 -- |-----------------------< update_questionnaire >---------------------------|
157 -- ----------------------------------------------------------------------------
158
159 Procedure update_questionnaire
160 (p_questionnaire_template_id in number
161 ,p_object_version_number in out nocopy number
162 ,p_text in CLOB
163 ,p_available_flag in varchar2 default hr_api.g_varchar2
164 ,p_business_group_id in number default hr_api.g_number
165 ,p_effective_date in date default hr_api.g_date
166 ,p_validate in number default hr_api.g_false_num
167 ,p_quest_type_code in varchar2
168 ,p_return_status out nocopy varchar2
169 ) is
170 --
171 -- Variables for API Boolean parameters
172 l_validate boolean;
173 --
174 -- Variables for IN/OUT parameters
175 l_object_version_number number;
176 --
177 -- Other variables
178 l_proc varchar2(72) := g_package ||'update_questionnaire';
179
180 l_text CLOB;
181 l_business_group_id hr_questionnaires.business_group_id%TYPE;
182
183 CURSOR C_Sel1 IS
184 SELECT text, business_group_id
185 FROM HR_QUESTIONNAIRES
186 WHERE questionnaire_template_id = p_questionnaire_template_id;
187
188 Begin
189 hr_utility.set_location(' Entering:' || l_proc,10);
190
191 --
192 -- Issue a savepoint
193 --
194 savepoint update_questionnaire_swi;
195 --
196 -- Initialise Multiple Message Detection
197 --
198 hr_multi_message.enable_message_list;
199 --
200 -- Remember IN OUT parameter IN values
201 --
202 l_object_version_number := p_object_version_number;
203 --
204 -- Convert constant values to their corresponding boolean value
205 --
206 l_validate :=
207 hr_api.constant_to_boolean
208 (p_constant_value => p_validate);
209 --
210 -- Register Surrogate ID or user key values
211 --
212 hr_utility.set_location(' Values :p_available_flag' ||p_available_flag ,11);
213 hr_utility.set_location(' Values :p_object_version_number' ||p_object_version_number ,11);
214 hr_utility.set_location(' Values :p_effective_date' ||p_effective_date ,11);
215 hr_utility.set_location(' Values :p_questionnaire_template_id' ||p_questionnaire_template_id ,11);
216 hr_utility.set_location(' Values :p_quest_type_code' ||p_quest_type_code ,11);
217 hr_utility.set_location(' Values :p_html_text' ||p_text ,11);
218 hr_utility.set_location(' Values :p_business_group_id' ||p_business_group_id ,11);
219
220 --
221 -- Call API
222 -- Before updating the Text, save Original Text in Local Variable.
223 -- OPEN C_Sel1;
224 -- FETCH C_Sel1 into l_text, l_business_group_id;
225 --
226 hr_questionnaire_api.update_questionnaire
227 (p_questionnaire_template_id => p_questionnaire_template_id
228 ,p_text => p_text
229 ,p_available_flag => p_available_flag
230 ,p_business_group_id => p_business_group_id
231 ,p_object_version_number => p_object_version_number
232 ,p_quest_type_code =>p_quest_type_code
233 ,p_effective_date => p_effective_date
234 );
235 --
236 -- Check if the Text is updated, delete all fields.
237 -- From Java Layer the Text value will Not be passed
238 -- in case the Text is not updated.
239 IF NOT (p_text = hr_api.g_varchar2) THEN
240 delete_questionnaire_fields (
241 p_questionnaire_template_id => p_questionnaire_template_id
242 );
243 END IF;
244 -- CLOSE C_Sel1;
245
246 If l_validate = TRUE Then
247 rollback to update_questionnaire_swi;
248 End If;
249 --
250 p_return_status := hr_multi_message.get_return_status_disable;
251 hr_utility.set_location(' Leaving:' || l_proc,20);
252 --
253 exception
254 when hr_multi_message.error_message_exist then
255 --
256 rollback to update_questionnaire_swi;
257 --
258 --
259 p_object_version_number := l_object_version_number;
260 p_return_status := hr_multi_message.get_return_status_disable;
261 hr_utility.set_location(' Leaving:' || l_proc, 30);
262
263 when others then
264 --
265 rollback to update_questionnaire_swi;
266
267 if hr_multi_message.unexpected_error_add(l_proc) then
268 hr_utility.set_location(' Leaving:' || l_proc,40);
269 raise;
270 end if;
271 --
272 p_object_version_number := l_object_version_number;
273 p_return_status := hr_multi_message.get_return_status_disable;
274 hr_utility.set_location(' Leaving:' || l_proc,50);
275
276 end update_questionnaire;
277
278 --
279 -- ----------------------------------------------------------------------------
280 -- |-----------------------< delete_questionnaire >---------------------------|
281 -- ----------------------------------------------------------------------------
282
283 Procedure delete_questionnaire
284 (p_questionnaire_template_id in number
285 ,p_object_version_number in number
286 ,p_validate in number default hr_api.g_false_num
287 ,p_return_status out nocopy varchar2
288 ) is
289
290 --
291 -- Variables for API Boolean parameters
292 l_validate boolean;
293 --
294 -- Variables for IN/OUT parameters
295 --
296 -- Other variables
297 l_proc varchar2(72) := g_package ||'delete_questionnaire';
298
299 Begin
300
301 hr_utility.set_location(' Entering:' || l_proc,10);
302 --
303 -- Issue a savepoint
304 --
305 savepoint delete_questionnaire_swi;
306 --
307 -- Initialise Multiple Message Detection
308 --
309 hr_multi_message.enable_message_list;
310 --
311 -- Remember IN OUT parameter IN values
312 --
313 --
314 -- Convert constant values to their corresponding boolean value
315 --
316 l_validate :=
317 hr_api.constant_to_boolean
318 (p_constant_value => p_validate);
319 --
320
321 -- Register Surrogate ID or user key values
322 --
323 --
324 -- Call API
325 --
326
327 --delete_questionnaire_fields
328 -- (
329 -- p_questionnaire_template_id => p_questionnaire_template_id
330 -- );
331
332 hr_questionnaire_api.delete_questionnaire
333 (
334 p_questionnaire_template_id=>p_questionnaire_template_id
335 ,p_object_version_number=>p_object_version_number
336 ,p_validate=>l_validate
337 );
338
339 --
340 -- Convert API warning boolean parameter values to specific
341 -- messages and add them to Multiple Message List
342 --
343 --
344 -- Convert API non-warning boolean parameter values
345 --
346 --
347 -- Derive the API return status value based on whether
348 -- messages of any type exist in the Multiple Message List.
349 -- Also disable Multiple Message Detection.
350 --
351 If l_validate = TRUE Then
352 rollback to delete_questionnaire_swi;
353 End If;
354 --
355 p_return_status := hr_multi_message.get_return_status_disable;
356 hr_utility.set_location(' Leaving:' || l_proc,20);
357 --
358 exception
359 when hr_multi_message.error_message_exist then
360 --
361 -- Catch the Multiple Message List exception which
362 -- indicates API processing has been aborted because
363 -- at least one message exists in the list.
364 --
365 rollback to delete_questionnaire_swi;
366 --
367 -- Reset IN OUT parameters and set OUT parameters
368 --
369 p_return_status := hr_multi_message.get_return_status_disable;
370 hr_utility.set_location(' Leaving:' || l_proc, 30);
371 when others then
372 --
373 -- When Multiple Message Detection is enabled catch
374 -- any Application specific or other unexpected
375 -- exceptions. Adding appropriate details to the
376 -- Multiple Message List. Otherwise re-raise the
377 -- error.
378 --
379 rollback to delete_questionnaire_swi;
380 if hr_multi_message.unexpected_error_add(l_proc) then
381 hr_utility.set_location(' Leaving:' || l_proc,40);
382 raise;
383 end if;
384 --
385 -- Reset IN OUT and set OUT parameters
386 --
387 p_return_status := hr_multi_message.get_return_status_disable;
388 hr_utility.set_location(' Leaving:' || l_proc,50);
389 end delete_questionnaire;
390
391 Procedure delete_questionnaire_fields
392 (
393 p_questionnaire_template_id in number
394 )
395 IS
396
397 Cursor C_Quest_Fields IS
398 select field_id, object_version_number
399 from hr_quest_fields
400 where questionnaire_template_id = p_questionnaire_template_id;
401
402 Begin
403 -- Call API
404 --
405 For I in C_Quest_Fields Loop
406 Begin
407 hr_qsf_del.del
408 (I.field_id
409 ,I.object_version_number);
410 End;
411 End Loop;
412 --
413 end delete_questionnaire_fields;
414
415
416 END HR_QUESTIONNAIRE_SWI;