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