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