[Home] [Help]
PACKAGE BODY: APPS.HR_QUEST_FIELDS_API
Source
1 Package Body hr_quest_fields_api as
2 /* $Header: hrqsfapi.pkb 120.1.12020000.3 2013/03/20 11:53:50 bmaheshw ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_quest_fields_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< insert_quest_fields >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure insert_quest_fields
13 ( p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_questionnaire_template_id in number
16 ,p_name in varchar2
17 ,p_type in varchar2
18 ,p_sql_required_flag in varchar2
19 ,p_html_text in CLOB
20 ,p_sql_text in varchar2 default null
21 ,p_field_id out nocopy number
22 ,p_validation_type in varchar2
23 ,p_validation_detail in varchar2
24 ,p_display_sequence in number
25 ,p_object_version_number out nocopy number
26 ) is
27 --
28 -- Declare cursors and local variables
29 --
30 l_effective_date date;
31 l_proc varchar2(72) := g_package||'insert_quest_fields';
32 begin
33 hr_utility.set_location('Entering:'|| l_proc, 10);
34 --
35 -- Issue a savepoint
36 --
37 savepoint insert_quest_fields;
38 --
39 -- Remember IN OUT parameter IN values
40 --
41
42
43 --
44 -- Truncate the time portion from all IN date parameters
45 --
46 l_effective_date := trunc(p_effective_date);
47
48 --
49 -- Call Before Process User Hook
50 --
51 begin
52 hr_quest_fields_bk1.insert_quest_fields_b
53 (
54 p_effective_date =>l_effective_date
55 ,p_questionnaire_template_id =>p_questionnaire_template_id
56 ,p_name =>p_name
57 ,p_type =>p_type
58 ,p_sql_required_flag =>p_sql_required_flag
59 ,p_html_text =>NULL
60 ,p_sql_text =>p_sql_text
61 ,p_validation_type =>p_validation_type
62 ,p_validation_detail =>p_validation_detail
63 ,p_display_sequence =>p_display_sequence
64 );
65 exception
66 when hr_api.cannot_find_prog_unit then
67 hr_api.cannot_find_prog_unit_error
68 (p_module_name => 'insert_quest_fields'
69 ,p_hook_type => 'BP'
70 );
71 end;
72 --
73 -- Validation in addition to Row Handlers
74 --
75
76
77
78 --
79 -- Process Logic
80 --
81 hr_qsf_ins.ins
82 (
83 p_effective_date => l_effective_date
84 ,p_questionnaire_template_id => p_questionnaire_template_id
85 ,p_name => p_name
86 ,p_type => p_type
87 ,p_sql_required_flag => p_sql_required_flag
88 ,p_html_text => p_html_text
89 ,p_sql_text => p_sql_text
90 ,p_field_id => p_field_id
91 ,p_object_version_number => p_object_version_number
92 ,p_validation_type => p_validation_type
93 ,p_validation_detail => p_validation_detail
94 ,p_display_sequence => p_display_sequence
95 );
96
97 --
98 -- Call After Process User Hook
99 --
100 begin
101 hr_quest_fields_bk1.insert_quest_fields_a
102 (
103 p_effective_date =>l_effective_date
104 ,p_questionnaire_template_id =>p_questionnaire_template_id
105 ,p_name =>p_name
106 ,p_type =>p_type
107 ,p_sql_required_flag =>p_sql_required_flag
108 ,p_html_text =>NULL
109 ,p_sql_text =>p_sql_text
110 ,p_field_id =>p_field_id
111 ,p_object_version_number =>p_object_version_number
112 ,p_validation_type => p_validation_type
113 ,p_validation_detail => p_validation_detail
114 ,p_display_sequence => p_display_sequence
115 );
116 exception
117 when hr_api.cannot_find_prog_unit then
118 hr_api.cannot_find_prog_unit_error
119 (p_module_name => 'insert_quest_fields'
120 ,p_hook_type => 'AP'
121 );
122 end;
123 --
124 -- When in validation only mode raise the Validate_Enabled exception
125 --
126 if p_validate then
127 raise hr_api.validate_enabled;
128 end if;
129 --
130 -- Set all IN OUT and OUT parameters with out values
131 --
132
133 --
134 hr_utility.set_location(' Leaving:'||l_proc, 70);
135 exception
136 when hr_api.validate_enabled then
137 --
138 -- As the Validate_Enabled exception has been raised
139 -- we must rollback to the savepoint
140 --
141 rollback to insert_quest_fields;
142 --
143 -- Reset IN OUT parameters and set OUT parameters
144 -- (Any key or derived arguments must be set to null
145 -- when validation only mode is being used.)
146 --
147 p_field_id := null;
148 p_object_version_number := null;
149
150 hr_utility.set_location(' Leaving:'||l_proc, 80);
151 when others then
152 --
153 -- A validation or unexpected error has occured
154 --
155 rollback to insert_quest_fields;
156 --
157 -- Reset IN OUT parameters and set all
158 -- OUT parameters, including warnings, to null
159 --
160 p_field_id := null;
161 p_object_version_number := null;
162
163 hr_utility.set_location(' Leaving:'||l_proc, 90);
164 raise;
165 end insert_quest_fields;
166 --
167
168 --
169 -- ----------------------------------------------------------------------------
170 -- |--------------------------< update_quest_fields >--------------------------|
171 -- ----------------------------------------------------------------------------
172 --
173 procedure update_quest_fields
174 (p_validate in boolean default false
175 ,p_effective_date in date
176 ,p_field_id in number
177 ,p_object_version_number in out nocopy number
178 ,p_questionnaire_template_id in number default hr_api.g_number
179 ,p_name in varchar2 default hr_api.g_varchar2
180 ,p_type in varchar2 default hr_api.g_varchar2
181 ,p_sql_required_flag in varchar2 default hr_api.g_varchar2
182 ,p_html_text in CLOB default hr_api.g_varchar2
183 ,p_sql_text in varchar2 default hr_api.g_varchar2
184 ,p_validation_type in varchar2
185 ,p_validation_detail in varchar2
186 ,p_display_sequence in number
187 ) is
188 --
189 -- Declare cursors and local variables
190 --
191 l_effective_date date;
192 l_object_version_number number;
193 l_proc varchar2(72) := g_package||'update_quest_fields';
194 begin
195 hr_utility.set_location('Entering:'|| l_proc, 10);
196 --
197 -- Issue a savepoint
198 --
199 savepoint update_quest_fields;
200 --
201 -- Remember IN OUT parameter IN values
202 --
203
204
205 --
206 -- Truncate the time portion from all IN date parameters
207 --
208 l_effective_date := trunc(p_effective_date);
209 l_object_version_number:=p_object_version_number;
210 --
211 -- Call Before Process User Hook
212 --
213 begin
214 hr_quest_fields_bk2.update_quest_fields_b
215 (
216 p_effective_date => l_effective_date
217 ,p_field_id => p_field_id
218 ,p_object_version_number => p_object_version_number
219 ,p_questionnaire_template_id => p_questionnaire_template_id
220 ,p_name => p_name
221 ,p_type => p_type
222 ,p_sql_required_flag => p_sql_required_flag
223 ,p_html_text => NULL
224 ,p_sql_text => p_sql_text
225 ,p_validation_type => p_validation_type
226 ,p_validation_detail => p_validation_detail
227 ,p_display_sequence => p_display_sequence
228 );
229 exception
230 when hr_api.cannot_find_prog_unit then
231 hr_api.cannot_find_prog_unit_error
232 (p_module_name => 'update_quest_fields'
233 ,p_hook_type => 'BP'
234 );
235 end;
236 --
237 -- Validation in addition to Row Handlers
238 --
239
240
241 --
242 -- Process Logic
243 --
244 hr_qsf_upd.upd
245 (
246 p_effective_date => p_effective_date
247 ,p_field_id => p_field_id
248 ,p_object_version_number => p_object_version_number
249 ,p_sql_text => p_sql_text
250 ,p_name => p_name
251 ,p_type => p_type
252 ,p_sql_required_flag => p_sql_required_flag
253 ,p_html_text => p_html_text
254 ,p_validation_type => p_validation_type
255 ,p_validation_detail => p_validation_detail
256 ,p_questionnaire_template_id => p_questionnaire_template_id
257 ,p_display_sequence => p_display_sequence
258 );
259
260 --
261 -- Call After Process User Hook
262 --
263 begin
264 hr_quest_fields_bk2.update_quest_fields_a
265 (
266 p_effective_date => l_effective_date
267 ,p_field_id => p_field_id
268 ,p_object_version_number => p_object_version_number
269 ,p_questionnaire_template_id => p_questionnaire_template_id
270 ,p_name => p_name
271 ,p_type => p_type
272 ,p_sql_required_flag => p_sql_required_flag
273 ,p_html_text => NULL
274 ,p_sql_text => p_sql_text
275 ,p_validation_type => p_validation_type
276 ,p_validation_detail => p_validation_detail
277 ,p_display_sequence => p_display_sequence
278 );
279 exception
280 when hr_api.cannot_find_prog_unit then
281 hr_api.cannot_find_prog_unit_error
282 (p_module_name => 'update_quest_fields'
283 ,p_hook_type => 'AP'
284 );
285 end;
286 --
287 -- When in validation only mode raise the Validate_Enabled exception
288 --
289 if p_validate then
290 raise hr_api.validate_enabled;
291 end if;
292 --
293 -- Set all IN OUT and OUT parameters with out values
294 --
295
296 --
297 hr_utility.set_location(' Leaving:'||l_proc, 70);
298 exception
299 when hr_api.validate_enabled then
300 --
301 -- As the Validate_Enabled exception has been raised
302 -- we must rollback to the savepoint
303 --
304 rollback to update_quest_fields;
305 --
306 -- Reset IN OUT parameters and set OUT parameters
307 -- (Any key or derived arguments must be set to null
308 -- when validation only mode is being used.)
309 --
310 p_object_version_number := l_object_version_number;
311
312 hr_utility.set_location(' Leaving:'||l_proc, 80);
313 when others then
314 --
315 -- A validation or unexpected error has occured
316 --
317 rollback to update_quest_fields;
318 --
319 -- Reset IN OUT parameters and set all
320 -- OUT parameters, including warnings, to null
321 --
322
323 p_object_version_number := l_object_version_number;
324
325 hr_utility.set_location(' Leaving:'||l_proc, 90);
326 raise;
327 end update_quest_fields;
328 --
329
330 --
331 -- ----------------------------------------------------------------------------
332 -- |--------------------------< delete_quest_fields >--------------------------|
333 -- ----------------------------------------------------------------------------
334 --
335 procedure delete_quest_fields
336 ( p_validate in boolean default false
337 ,p_field_id in number
338 ,p_object_version_number in number
339 ) is
340 --
341 -- Declare cursors and local variables
342 --
343 Cursor c_hr_que_ans_vals is
344
345 SELECT quest_answer_val_id, object_version_number from HR_QUEST_ANSWER_VALUES where field_id = p_field_id;
346
347
348 l_proc varchar2(72) := g_package||'delete_quest_fields';
349
350 begin
351 hr_utility.set_location('Entering:'|| l_proc, 10);
352 --
353 -- Issue a savepoint
354 --
355 savepoint delete_quest_fields;
356 --
357 -- Remember IN OUT parameter IN values
358 --
359
360
361 --
362 -- Truncate the time portion from all IN date parameters
363 --
364
365 --
366 -- Call Before Process User Hook
367 --
368 begin
369 hr_quest_fields_bk3.delete_quest_fields_b
370 (
371 p_field_id => p_field_id
372 ,p_object_version_number => p_object_version_number
373 );
374 exception
375 when hr_api.cannot_find_prog_unit then
376 hr_api.cannot_find_prog_unit_error
377 (p_module_name => 'delete_quest_fields'
378 ,p_hook_type => 'BP'
379 );
380 end;
381 --
382 -- Validation in addition to Row Handlers
383 --
384
385
386
387 --
388 -- Process Logic
389 --
390
391 -- Deleting in the child table (HR_QUEST_ANSWER_VALUES)
392
393 FOR hr_que_ans_vals_rec in c_hr_que_ans_vals LOOP
394
395 hr_quest_ans_val_api.delete_quest_answer_val
396 (
397 p_validate => false -- as per documentaion always should pass false when one BP calls another BP
398 ,p_quest_answer_val_id => hr_que_ans_vals_rec.quest_answer_val_id
399 ,p_object_version_number => hr_que_ans_vals_rec.object_version_number
400 );
401
402 END LOOP;
403
404 hr_qsf_del.del
405 (
406 p_field_id => p_field_id
407 ,p_object_version_number => p_object_version_number
408 );
409
410 --
411 -- Call After Process User Hook
412 --
413 begin
414 hr_quest_fields_bk3.delete_quest_fields_a
415 (
416 p_field_id =>p_field_id
417 ,p_object_version_number =>p_object_version_number
418 );
419 exception
420 when hr_api.cannot_find_prog_unit then
421 hr_api.cannot_find_prog_unit_error
422 (p_module_name => 'delete_quest_fields'
423 ,p_hook_type => 'AP'
424 );
425 end;
426 --
427 -- When in validation only mode raise the Validate_Enabled exception
428 --
429 if p_validate then
430 raise hr_api.validate_enabled;
431 end if;
432 --
433 -- Set all IN OUT and OUT parameters with out values
434 --
435
436 --
437 hr_utility.set_location(' Leaving:'||l_proc, 70);
438 exception
439 when hr_api.validate_enabled then
440 --
441 -- As the Validate_Enabled exception has been raised
442 -- we must rollback to the savepoint
443 --
444 rollback to delete_quest_fields;
445 --
446 -- Reset IN OUT parameters and set OUT parameters
447 -- (Any key or derived arguments must be set to null
448 -- when validation only mode is being used.)
449 --
450
451 hr_utility.set_location(' Leaving:'||l_proc, 80);
452 when others then
453 --
454 -- A validation or unexpected error has occured
455 --
456 rollback to delete_quest_fields;
457 --
458 -- Reset IN OUT parameters and set all
459 -- OUT parameters, including warnings, to null
460 --
461
462 hr_utility.set_location(' Leaving:'||l_proc, 90);
463 raise;
464
465 end delete_quest_fields;
466 --
467
468 end hr_quest_fields_api;