[Home] [Help]
PACKAGE BODY: APPS.HR_QUEST_ANS_VAL_SWI
Source
1 Package Body hr_quest_ans_val_swi As
2 /* $Header: hrqsvswi.pkb 120.0.12000000.2 2007/07/17 08:42:39 rapandi ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'hr_quest_ans_val_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------< insert_quest_answer_val >-------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE insert_quest_answer_val
12 (p_quest_answer_val_id in number
13 ,p_questionnaire_answer_id in number
14 ,p_field_id in number
15 ,p_object_version_number out nocopy number
16 ,p_value in varchar2 default hr_api.g_varchar2
17 ,p_validate in number default hr_api.g_false_num
18 ,p_return_status out nocopy varchar2
19 ) is
20 --
21 -- Variables for API Boolean parameters
22 l_validate boolean;
23 --
24 -- Variables for IN/OUT parameters
25 --
26 -- Other variables
27 l_quest_answer_val_id number;
28 l_proc varchar2(72) := g_package ||'insert_quest_answer_val';
29 Begin
30 hr_utility.set_location(' Entering:' || l_proc,10);
31 --
32 -- Issue a savepoint
33 --
34 savepoint insert_quest_answer_val;
35 --
36 -- Initialise Multiple Message Detection
37 --
38 hr_multi_message.enable_message_list;
39 --
40 -- Remember IN OUT parameter IN values
41 --
42 --
43 -- Convert constant values to their corresponding boolean value
44 --
45 l_validate :=
46 hr_api.constant_to_boolean
47 (p_constant_value => p_validate);
48 --
49 -- Register Surrogate ID or user key values
50
51 hr_qsv_ins.set_base_key_value
52 ( p_quest_answer_val_id => p_quest_answer_val_id
53 );
54
55 --
56 --
57 -- Call API
58 --
59 hr_quest_ans_val_api.insert_quest_answer_val
60 (p_quest_answer_val_id => l_quest_answer_val_id
61 ,p_questionnaire_answer_id => p_questionnaire_answer_id
62 ,p_field_id => p_field_id
63 ,p_object_version_number => p_object_version_number
64 ,p_value => p_value
65 );
66 --
67 -- Convert API warning boolean parameter values to specific
68 -- messages and add them to Multiple Message List
69 --
70 --
71 -- Convert API non-warning boolean parameter values
72 --
73 --
74 -- Derive the API return status value based on whether
75 -- messages of any type exist in the Multiple Message List.
76 -- Also disable Multiple Message Detection.
77 --
78 If l_validate = TRUE Then
79 rollback to insert_quest_answer_val;
80 End If;
81 --
82 p_return_status := hr_multi_message.get_return_status_disable;
83 hr_utility.set_location(' Leaving:' || l_proc,20);
84 --
85 exception
86 when hr_multi_message.error_message_exist then
87 --
88 -- Catch the Multiple Message List exception which
89 -- indicates API processing has been aborted because
90 -- at least one message exists in the list.
91 --
92 rollback to insert_quest_answer_val;
93 --
94 -- Reset IN OUT parameters and set OUT parameters
95 --
96 p_object_version_number := null;
97 p_return_status := hr_multi_message.get_return_status_disable;
98 hr_utility.set_location(' Leaving:' || l_proc, 30);
99 when others then
100 --
101 -- When Multiple Message Detection is enabled catch
102 -- any Application specific or other unexpected
103 -- exceptions. Adding appropriate details to the
104 -- Multiple Message List. Otherwise re-raise the
105 -- error.
106 --
107 rollback to insert_quest_answer_val;
108 if hr_multi_message.unexpected_error_add(l_proc) then
109 hr_utility.set_location(' Leaving:' || l_proc,40);
110 raise;
111 end if;
112 --
113 -- Reset IN OUT and set OUT parameters
114 --
115 p_object_version_number := null;
116 p_return_status := hr_multi_message.get_return_status_disable;
117 hr_utility.set_location(' Leaving:' || l_proc,50);
118 end insert_quest_answer_val;
119 -- ----------------------------------------------------------------------------
120 -- |--------------------------< set_base_key_value >--------------------------|
121 -- ----------------------------------------------------------------------------
122 PROCEDURE set_base_key_value
123 (p_quest_answer_val_id in number
124 ,p_return_status out nocopy varchar2
125 ) is
126 --
127 -- Variables for API Boolean parameters
128 --
129 -- Variables for IN/OUT parameters
130 --
131 -- Other variables
132 l_proc varchar2(72) := g_package ||'set_base_key_value';
133 Begin
134 hr_utility.set_location(' Entering:' || l_proc,10);
135 --
136 -- Issue a savepoint
137 --
138 savepoint set_base_key_value_swi;
139 --
140 -- Initialise Multiple Message Detection
141 --
142 hr_multi_message.enable_message_list;
143 --
144 -- Remember IN OUT parameter IN values
145 --
146 --
147 -- Convert constant values to their corresponding boolean value
148 --
149 --
150 -- Register Surrogate ID or user key values
151 --
152 --
153 -- Call API
154 --
155 hr_qsv_ins.set_base_key_value
156 (p_quest_answer_val_id => p_quest_answer_val_id
157 );
158 --
159 -- Convert API warning boolean parameter values to specific
160 -- messages and add them to Multiple Message List
161 --
162 --
163 -- Convert API non-warning boolean parameter values
164 --
165 --
166 -- Derive the API return status value based on whether
167 -- messages of any type exist in the Multiple Message List.
168 -- Also disable Multiple Message Detection.
169 --
170 p_return_status := hr_multi_message.get_return_status_disable;
171 hr_utility.set_location(' Leaving:' || l_proc,20);
172 --
173 exception
174 when hr_multi_message.error_message_exist then
175 --
176 -- Catch the Multiple Message List exception which
177 -- indicates API processing has been aborted because
178 -- at least one message exists in the list.
179 --
180 rollback to set_base_key_value_swi;
181 --
182 -- Reset IN OUT parameters and set OUT parameters
183 --
184 p_return_status := hr_multi_message.get_return_status_disable;
185 hr_utility.set_location(' Leaving:' || l_proc, 30);
186 when others then
187 --
188 -- When Multiple Message Detection is enabled catch
189 -- any Application specific or other unexpected
190 -- exceptions. Adding appropriate details to the
191 -- Multiple Message List. Otherwise re-raise the
192 -- error.
193 --
194 rollback to set_base_key_value_swi;
195 if hr_multi_message.unexpected_error_add(l_proc) then
196 hr_utility.set_location(' Leaving:' || l_proc,40);
197 raise;
198 end if;
199 --
200 -- Reset IN OUT and set OUT parameters
201 --
202 p_return_status := hr_multi_message.get_return_status_disable;
203 hr_utility.set_location(' Leaving:' || l_proc,50);
204 end set_base_key_value;
205 --
206 --
207 -- ----------------------------------------------------------------------------
208 -- |------------------------< delete_quest_answer_val >-----------------------|
209 -- ----------------------------------------------------------------------------
210 PROCEDURE delete_quest_answer_val
211 (p_quest_answer_val_id in number
212 ,p_object_version_number in number
213 ,p_validate in number default hr_api.g_false_num
214 ,p_return_status out nocopy varchar2
215 ) is
216 --
217 -- Variables for API Boolean parameters
218 l_validate boolean;
219 --
220 -- Variables for IN/OUT parameters
221 --
222 -- Other variables
223 l_proc varchar2(72) := g_package ||'delete_quest_answer_val';
224 Begin
225 hr_utility.set_location(' Entering:' || l_proc,10);
226 --
227 -- Issue a savepoint
228 --
229 savepoint delete_quest_answer_val;
230 --
231 -- Initialise Multiple Message Detection
232 --
233 hr_multi_message.enable_message_list;
234 --
235 -- Remember IN OUT parameter IN values
236 --
237 --
238 -- Convert constant values to their corresponding boolean value
239 --
240 l_validate :=
241 hr_api.constant_to_boolean
242 (p_constant_value => p_validate);
243 --
244 -- Register Surrogate ID or user key values
245 --
246 --
247 -- Call API
248 --
249 hr_quest_ans_val_api.delete_quest_answer_val
250 (p_quest_answer_val_id => p_quest_answer_val_id
251 ,p_object_version_number => p_object_version_number
252 );
253 --
254 -- Convert API warning boolean parameter values to specific
255 -- messages and add them to Multiple Message List
256 --
257 --
258 -- Convert API non-warning boolean parameter values
259 --
260 --
261 -- Derive the API return status value based on whether
262 -- messages of any type exist in the Multiple Message List.
263 -- Also disable Multiple Message Detection.
264 --
265 If l_validate = TRUE Then
266 rollback to delete_quest_answer_val;
267 End If;
268 --
269 p_return_status := hr_multi_message.get_return_status_disable;
270 hr_utility.set_location(' Leaving:' || l_proc,20);
271 --
272 exception
273 when hr_multi_message.error_message_exist then
274 --
275 -- Catch the Multiple Message List exception which
276 -- indicates API processing has been aborted because
277 -- at least one message exists in the list.
278 --
279 rollback to delete_quest_answer_val;
280 --
281 -- Reset IN OUT parameters and set OUT parameters
282 --
283 p_return_status := hr_multi_message.get_return_status_disable;
284 hr_utility.set_location(' Leaving:' || l_proc, 30);
285 when others then
286 --
287 -- When Multiple Message Detection is enabled catch
288 -- any Application specific or other unexpected
289 -- exceptions. Adding appropriate details to the
290 -- Multiple Message List. Otherwise re-raise the
291 -- error.
292 --
293 rollback to delete_quest_answer_val;
294 if hr_multi_message.unexpected_error_add(l_proc) then
295 hr_utility.set_location(' Leaving:' || l_proc,40);
296 raise;
297 end if;
298 --
299 -- Reset IN OUT and set OUT parameters
300 --
301 p_return_status := hr_multi_message.get_return_status_disable;
302 hr_utility.set_location(' Leaving:' || l_proc,50);
303 end delete_quest_answer_val;
304 --
305 -- ----------------------------------------------------------------------------
306 -- |------------------------< update_quest_answer_val >-----------------------|
307 -- ----------------------------------------------------------------------------
308 PROCEDURE update_quest_answer_val
309 (p_quest_answer_val_id in number
310 ,p_object_version_number in out nocopy number
311 ,p_value in varchar2 default hr_api.g_varchar2
312 ,p_validate in number default hr_api.g_false_num
313 ,p_return_status out nocopy varchar2
314 ) is
315 --
316 -- Variables for API Boolean parameters
317 l_validate boolean;
318 --
319 -- Variables for IN/OUT parameters
320 l_object_version_number number;
321 --
322 -- Other variables
323 l_proc varchar2(72) := g_package ||'update_quest_answer_val';
324 Begin
325 hr_utility.set_location(' Entering:' || l_proc,10);
326 --
327 -- Issue a savepoint
328 --
329 savepoint update_quest_answer_val;
330 --
331 -- Initialise Multiple Message Detection
332 --
333 hr_multi_message.enable_message_list;
334 --
335 -- Remember IN OUT parameter IN values
336 --
337 l_object_version_number := p_object_version_number;
338 --
339 -- Convert constant values to their corresponding boolean value
340 --
341 l_validate :=
342 hr_api.constant_to_boolean
343 (p_constant_value => p_validate);
344 --
345 -- Register Surrogate ID or user key values
346 --
347 --
348 -- Call API
349 --
350 hr_quest_ans_val_api.update_quest_answer_val
351 (p_quest_answer_val_id => p_quest_answer_val_id
352 ,p_object_version_number => p_object_version_number
353 ,p_value => p_value
354 );
355 --
356 -- Convert API warning boolean parameter values to specific
357 -- messages and add them to Multiple Message List
358 --
359 --
360 -- Convert API non-warning boolean parameter values
361 --
362 --
363 -- Derive the API return status value based on whether
364 -- messages of any type exist in the Multiple Message List.
365 -- Also disable Multiple Message Detection.
366 --
367 If l_validate = TRUE Then
368 rollback to update_quest_answer_val;
369 End If;
370 --
371 p_return_status := hr_multi_message.get_return_status_disable;
372 hr_utility.set_location(' Leaving:' || l_proc,20);
373 --
374 exception
375 when hr_multi_message.error_message_exist then
376 --
377 -- Catch the Multiple Message List exception which
378 -- indicates API processing has been aborted because
379 -- at least one message exists in the list.
380 --
381 rollback to update_quest_answer_val;
382 --
383 -- Reset IN OUT parameters and set OUT parameters
384 --
385 p_object_version_number := l_object_version_number;
386 p_return_status := hr_multi_message.get_return_status_disable;
387 hr_utility.set_location(' Leaving:' || l_proc, 30);
388 when others then
389 --
390 -- When Multiple Message Detection is enabled catch
391 -- any Application specific or other unexpected
392 -- exceptions. Adding appropriate details to the
393 -- Multiple Message List. Otherwise re-raise the
394 -- error.
395 --
396 rollback to update_quest_answer_val;
397 if hr_multi_message.unexpected_error_add(l_proc) then
398 hr_utility.set_location(' Leaving:' || l_proc,40);
399 raise;
400 end if;
401 --
402 -- Reset IN OUT and set OUT parameters
403 --
404 p_object_version_number := l_object_version_number;
405 p_return_status := hr_multi_message.get_return_status_disable;
406 hr_utility.set_location(' Leaving:' || l_proc,50);
407 end update_quest_answer_val;
408
409 -- ----------------------------------------------------------------------------
410 -- |---------------------------< process_api >--------------------------------|
411 -- ----------------------------------------------------------------------------
412
413 Procedure process_api
414 (
415 p_document in CLOB
416 ,p_return_status out nocopy VARCHAR2
417 ,p_validate in number default hr_api.g_false_num
418 ,p_effective_date in date default null
419 )
420 IS
421 l_postState VARCHAR2(2);
422 l_return_status VARCHAR2(1);
423 l_commitElement xmldom.DOMElement;
424 l_object_version_number number;
425 l_parser xmlparser.Parser;
426 l_CommitNode xmldom.DOMNode;
427 l_proc varchar2(72) := g_package || 'process_api';
428
429 BEGIN
430
431 hr_utility.set_location(' Entering:' || l_proc,10);
432 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
433
434 l_parser := xmlparser.newParser;
435 xmlparser.ParseCLOB(l_parser,p_document);
436 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
437
438 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
439
440 l_commitElement := xmldom.makeElement(l_CommitNode);
441 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
442 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
443
444 if l_postState = '0' then
445
446 insert_quest_answer_val
447 ( p_quest_answer_val_id => hr_transaction_swi.getNumberValue(l_CommitNode,'QuestAnswerValId',null)
448 ,p_questionnaire_answer_id => hr_transaction_swi.getNumberValue(l_CommitNode,'QuestionnaireAnswerId',null)
449 ,p_field_id => hr_transaction_swi.getNumberValue(l_CommitNode,'FieldId',null)
450 ,p_object_version_number => l_object_version_number
451 ,p_value => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Value',null)
452 ,p_validate => p_validate
453 ,p_return_status => l_return_status
454 );
455
456 elsif l_postState = '2' then
457
458 update_quest_answer_val
459 ( p_quest_answer_val_id => hr_transaction_swi.getNumberValue(l_CommitNode,'QuestAnswerValId')
460 ,p_object_version_number => l_object_version_number
461 ,p_value => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Value')
462 ,p_validate => p_validate
463 ,p_return_status => l_return_status
464 );
465
466 elsif l_postState = '3' then
467
468 delete_quest_answer_val
469 ( p_quest_answer_val_id => hr_transaction_swi.getNumberValue(l_CommitNode,'QuestAnswerValId')
470 ,p_object_version_number => l_object_version_number
471 ,p_validate => p_validate
472 ,p_return_status => l_return_status
473 );
474
475 p_return_status := l_return_status;
476
477 end if;
478
479 hr_utility.set_location('Exiting:' || l_proc,40);
480
481 END process_api;
482
483 end hr_quest_ans_val_swi;