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