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