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