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