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