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