[Home] [Help]
PACKAGE BODY: APPS.GHR_AGENCY_APPEALS_API
Source
4 -- Package Variables
1 Package Body ghr_agency_appeals_api as
2 /* $Header: ghcaaapi.pkb 115.1 2003/01/30 16:31:26 asubrahm noship $ */
3 --
5 --
6 g_package varchar2(33) := ' ghr_complaints_appeals_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< Create_agency_appeal>--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_agency_appeal
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_complaint_id in number default null
16 ,p_appeal_date in date default null
17 ,p_reason_for_appeal in varchar2 default null
18 ,p_source_decision_date in date default null
19 ,p_docket_num in varchar2 default null
20 ,p_agency_recvd_req_for_files in date default null
21 ,p_files_due in date default null
22 ,p_files_forwd in date default null
23 ,p_agency_brief_due in date default null
27 ,p_dec_recvd_by_agency in date default null
24 ,p_agency_brief_forwd in date default null
25 ,p_agency_recvd_appellant_brief in date default null
26 ,p_decision_date in date default null
28 ,p_decision in varchar2 default null
29 ,p_dec_forwd_to_org in date default null
30 ,p_agency_rfr_suspense in date default null
31 ,p_request_for_rfr in date default null
32 ,p_rfr_docket_num in varchar2 default null
33 ,p_rfr_requested_by in varchar2 default null
34 ,p_agency_rfr_due in date default null
35 ,p_rfr_forwd_to_org in date default null
36 ,p_org_forwd_rfr_to_agency in date default null
37 ,p_agency_forwd_rfr_ofo in date default null
38 ,p_rfr_decision in varchar2 default null
39 ,p_rfr_decision_date in date default null
40 ,p_agency_recvd_rfr_dec in date default null
41 ,p_rfr_decision_forwd_to_org in date default null
42 ,p_compl_agency_appeal_id out nocopy number
43 ,p_object_version_number out nocopy number
44 ) is
45
46 --
47 -- Declare cursors and local variables
48 --
49
50 l_proc varchar2(72) := g_package||'create_agency_appeal';
51 l_compl_agency_appeal_id number;
52 l_object_version_number number;
53 begin
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55 --
56 -- Issue a savepoint
57 --
58 savepoint create_agency_appeal;
59 hr_utility.set_location(l_proc, 20);
60 --
61 -- Truncate the time portion from all IN date parameters
62 --
63 --
64 -- Call Before Process User Hook
65 --
66 begin
67 ghr_agency_appeals_bk_1.create_agency_appeal_b
68 (p_effective_date => trunc(p_effective_date)
69 ,p_complaint_id => p_complaint_id
70 ,p_appeal_date => p_appeal_date
71 ,p_reason_for_appeal => p_reason_for_appeal
72 ,p_source_decision_date => p_source_decision_date
73 ,p_docket_num => p_docket_num
74 ,p_agency_recvd_req_for_files => p_agency_recvd_req_for_files
75 ,p_files_due => p_files_due
76 ,p_files_forwd => p_files_forwd
77 ,p_agency_brief_due => p_agency_brief_due
78 ,p_agency_brief_forwd => p_agency_brief_forwd
79 ,p_agency_recvd_appellant_brief => p_agency_recvd_appellant_brief
80 ,p_decision_date => p_decision_date
81 ,p_dec_recvd_by_agency => p_dec_recvd_by_agency
82 ,p_decision => p_decision
83 ,p_dec_forwd_to_org => p_dec_forwd_to_org
84 ,p_agency_rfr_suspense => p_agency_rfr_suspense
85 ,p_request_for_rfr => p_request_for_rfr
86 ,p_rfr_docket_num => p_rfr_docket_num
87 ,p_rfr_requested_by => p_rfr_requested_by
88 ,p_agency_rfr_due => p_agency_rfr_due
89 ,p_rfr_forwd_to_org => p_rfr_forwd_to_org
90 ,p_org_forwd_rfr_to_agency => p_org_forwd_rfr_to_agency
91 ,p_agency_forwd_rfr_ofo => p_agency_forwd_rfr_ofo
92 ,p_rfr_decision => p_rfr_decision
93 ,p_rfr_decision_date => p_rfr_decision_date
94 ,p_agency_recvd_rfr_dec => p_agency_recvd_rfr_dec
95 ,p_rfr_decision_forwd_to_org => p_rfr_decision_forwd_to_org
96 );
97 exception
98 when hr_api.cannot_find_prog_unit then
99 hr_api.cannot_find_prog_unit_error
100 (p_module_name => 'create_agency_appeal'
101 ,p_hook_type => 'BP'
102 );
103 end;
104 --
105 -- Validation in addition to Row Handlers
106 --
107
108 hr_utility.set_location(l_proc, 40);
109 --
110 -- Process Logic
111 --
112 ghr_caa_ins.ins
113 (
114 p_effective_date => p_effective_date
115 ,p_complaint_id => p_complaint_id
116 ,p_appeal_date => p_appeal_date
117 ,p_reason_for_appeal => p_reason_for_appeal
118 ,p_source_decision_date => p_source_decision_date
119 ,p_docket_num => p_docket_num
120 ,p_agency_recvd_req_for_files => p_agency_recvd_req_for_files
121 ,p_files_due => p_files_due
122 ,p_files_forwd => p_files_forwd
123 ,p_agency_brief_due => p_agency_brief_due
124 ,p_agency_brief_forwd => p_agency_brief_forwd
125 ,p_agency_recvd_appellant_brief => p_agency_recvd_appellant_brief
126 ,p_decision_date => p_decision_date
127 ,p_dec_recvd_by_agency => p_dec_recvd_by_agency
128 ,p_decision => p_decision
129 ,p_dec_forwd_to_org => p_dec_forwd_to_org
130 ,p_agency_rfr_suspense => p_agency_rfr_suspense
131 ,p_request_for_rfr => p_request_for_rfr
132 ,p_rfr_docket_num => p_rfr_docket_num
133 ,p_rfr_requested_by => p_rfr_requested_by
134 ,p_agency_rfr_due => p_agency_rfr_due
135 ,p_rfr_forwd_to_org => p_rfr_forwd_to_org
136 ,p_org_forwd_rfr_to_agency => p_org_forwd_rfr_to_agency
137 ,p_agency_forwd_rfr_ofo => p_agency_forwd_rfr_ofo
138 ,p_rfr_decision => p_rfr_decision
139 ,p_rfr_decision_date => p_rfr_decision_date
140 ,p_agency_recvd_rfr_dec => p_agency_recvd_rfr_dec
141 ,p_rfr_decision_forwd_to_org => p_rfr_decision_forwd_to_org
145
142 ,p_compl_agency_appeal_id => l_compl_agency_appeal_id
143 ,p_object_version_number => l_object_version_number
144 );
146 hr_utility.set_location(l_proc, 50);
147 --
148 -- Call After Process User Hook
149 --
150 begin
151 ghr_agency_appeals_bk_1.create_agency_appeal_a
152 (p_effective_date => trunc(p_effective_date)
153 ,p_complaint_id => p_complaint_id
154 ,p_appeal_date => p_appeal_date
155 ,p_reason_for_appeal => p_reason_for_appeal
156 ,p_source_decision_date => p_source_decision_date
157 ,p_docket_num => p_docket_num
158 ,p_agency_recvd_req_for_files => p_agency_recvd_req_for_files
159 ,p_files_due => p_files_due
160 ,p_files_forwd => p_files_forwd
161 ,p_agency_brief_due => p_agency_brief_due
162 ,p_agency_brief_forwd => p_agency_brief_forwd
163 ,p_agency_recvd_appellant_brief => p_agency_recvd_appellant_brief
164 ,p_decision_date => p_decision_date
165 ,p_dec_recvd_by_agency => p_dec_recvd_by_agency
166 ,p_decision => p_decision
167 ,p_dec_forwd_to_org => p_dec_forwd_to_org
168 ,p_agency_rfr_suspense => p_agency_rfr_suspense
169 ,p_request_for_rfr => p_request_for_rfr
170 ,p_rfr_docket_num => p_rfr_docket_num
171 ,p_rfr_requested_by => p_rfr_requested_by
172 ,p_agency_rfr_due => p_agency_rfr_due
173 ,p_rfr_forwd_to_org => p_rfr_forwd_to_org
174 ,p_org_forwd_rfr_to_agency => p_org_forwd_rfr_to_agency
175 ,p_agency_forwd_rfr_ofo => p_agency_forwd_rfr_ofo
176 ,p_rfr_decision => p_rfr_decision
177 ,p_rfr_decision_date => p_rfr_decision_date
178 ,p_agency_recvd_rfr_dec => p_agency_recvd_rfr_dec
179 ,p_rfr_decision_forwd_to_org => p_rfr_decision_forwd_to_org
180 ,p_compl_agency_appeal_id => p_compl_agency_appeal_id
181 ,p_object_version_number => l_object_version_number
182 );
183 exception
184 when hr_api.cannot_find_prog_unit then
185 hr_api.cannot_find_prog_unit_error
186 (p_module_name => 'create_agency_appeal'
187 ,p_hook_type => 'AP'
188 );
189 end;
190 --
191 --
192
193 --
194 -- When in validation only mode raise the Validate_Enabled exception
195 --
196 if p_validate then
197 raise hr_api.validate_enabled;
198 end if;
199 --
200 -- Set all output arguments
201 --
202 p_compl_agency_appeal_id := l_compl_agency_appeal_id;
203 p_object_version_number := l_object_version_number;
204 --
205 hr_utility.set_location(' Leaving:'||l_proc, 70);
206 exception
207 when hr_api.validate_enabled then
208 --
209 -- As the Validate_Enabled exception has been raised
210 -- we must rollback to the savepoint
211 --
212 rollback to create_agency_appeal;
213 --
214 -- Only set output warning arguments
215 -- (Any key or derived arguments must be set to null
216 -- when validation only mode is being used.)
217 --
218 p_compl_agency_appeal_id := null;
219 p_object_version_number := null;
220 hr_utility.set_location(' Leaving:'||l_proc, 80);
221 when others then
222 --
223 -- A validation or unexpected error has occured
224 --
225 rollback to create_agency_appeal;
226 p_compl_agency_appeal_id := null;
227 p_object_version_number := null;
228 hr_utility.set_location(' Leaving:'||l_proc, 90);
229 raise;
230 end create_agency_appeal;
231 --
232
233
234 procedure update_agency_appeal
235 (p_validate in boolean default false
236 ,p_effective_date in date
237 ,p_compl_agency_appeal_id in number
238 ,p_object_version_number in out nocopy number
239 ,p_complaint_id in number default hr_api.g_number
240 ,p_appeal_date in date default hr_api.g_date
241 ,p_reason_for_appeal in varchar2 default hr_api.g_varchar2
242 ,p_source_decision_date in date default hr_api.g_date
243 ,p_docket_num in varchar2 default hr_api.g_varchar2
244 ,p_agency_recvd_req_for_files in date default hr_api.g_date
245 ,p_files_due in date default hr_api.g_date
246 ,p_files_forwd in date default hr_api.g_date
247 ,p_agency_brief_due in date default hr_api.g_date
248 ,p_agency_brief_forwd in date default hr_api.g_date
249 ,p_agency_recvd_appellant_brief in date default hr_api.g_date
250 ,p_decision_date in date default hr_api.g_date
251 ,p_dec_recvd_by_agency in date default hr_api.g_date
252 ,p_decision in varchar2 default hr_api.g_varchar2
253 ,p_dec_forwd_to_org in date default hr_api.g_date
254 ,p_agency_rfr_suspense in date default hr_api.g_date
255 ,p_request_for_rfr in date default hr_api.g_date
256 ,p_rfr_docket_num in varchar2 default hr_api.g_varchar2
257 ,p_rfr_requested_by in varchar2 default hr_api.g_varchar2
258 ,p_agency_rfr_due in date default hr_api.g_date
259 ,p_rfr_forwd_to_org in date default hr_api.g_date
260 ,p_org_forwd_rfr_to_agency in date default hr_api.g_date
261 ,p_agency_forwd_rfr_ofo in date default hr_api.g_date
265 ,p_rfr_decision_forwd_to_org in date default hr_api.g_date
262 ,p_rfr_decision in varchar2 default hr_api.g_varchar2
263 ,p_rfr_decision_date in date default hr_api.g_date
264 ,p_agency_recvd_rfr_dec in date default hr_api.g_date
266 )
267
268 is
269 l_proc varchar2(72) := g_package||'update_agency_appeal';
270 l_object_version_number number;
271 begin
272 hr_utility.set_location('Entering:'|| l_proc, 5);
273 --
274 savepoint update_agency_appeal;
275 --
276 -- Truncate the time portion from all IN date parameters
277 --
278 --
279 -- Call Before Process User Hook
280 --
281 begin
282 ghr_agency_appeals_bk_2.update_agency_appeal_b
283 (p_effective_date => trunc(p_effective_date)
284 ,p_compl_agency_appeal_id => p_compl_agency_appeal_id
285 ,p_complaint_id => p_complaint_id
286 ,p_appeal_date => p_appeal_date
287 ,p_reason_for_appeal => p_reason_for_appeal
288 ,p_source_decision_date => p_source_decision_date
289 ,p_docket_num => p_docket_num
290 ,p_agency_recvd_req_for_files => p_agency_recvd_req_for_files
291 ,p_files_due => p_files_due
292 ,p_files_forwd => p_files_forwd
293 ,p_agency_brief_due => p_agency_brief_due
294 ,p_agency_brief_forwd => p_agency_brief_forwd
295 ,p_agency_recvd_appellant_brief => p_agency_recvd_appellant_brief
296 ,p_decision_date => p_decision_date
297 ,p_dec_recvd_by_agency => p_dec_recvd_by_agency
298 ,p_decision => p_decision
299 ,p_dec_forwd_to_org => p_dec_forwd_to_org
300 ,p_agency_rfr_suspense => p_agency_rfr_suspense
301 ,p_request_for_rfr => p_request_for_rfr
302 ,p_rfr_docket_num => p_rfr_docket_num
303 ,p_rfr_requested_by => p_rfr_requested_by
304 ,p_agency_rfr_due => p_agency_rfr_due
305 ,p_rfr_forwd_to_org => p_rfr_forwd_to_org
306 ,p_org_forwd_rfr_to_agency => p_org_forwd_rfr_to_agency
307 ,p_agency_forwd_rfr_ofo => p_agency_forwd_rfr_ofo
308 ,p_rfr_decision => p_rfr_decision
309 ,p_rfr_decision_date => p_rfr_decision_date
310 ,p_agency_recvd_rfr_dec => p_agency_recvd_rfr_dec
311 ,p_rfr_decision_forwd_to_org => p_rfr_decision_forwd_to_org
312 ,p_object_version_number => p_object_version_number
313 );
314 exception
315 when hr_api.cannot_find_prog_unit then
316 hr_api.cannot_find_prog_unit_error
317 (p_module_name => 'update_agency_appeal'
318 ,p_hook_type => 'BP'
319 );
320 end;
321 --
322 -- Validation in addition to Row Handlers
323 --
324 -- Store the original ovn in case we rollback when p_validate is true
325 --
326 l_object_version_number := p_object_version_number;
327
328 hr_utility.set_location(l_proc, 6);
329
330 ghr_caa_upd.upd
331 (p_effective_date => p_effective_date
332 ,p_compl_agency_appeal_id => p_compl_agency_appeal_id
333 ,p_complaint_id => p_complaint_id
334 ,p_appeal_date => p_appeal_date
335 ,p_reason_for_appeal => p_reason_for_appeal
336 ,p_source_decision_date => p_source_decision_date
337 ,p_docket_num => p_docket_num
338 ,p_agency_recvd_req_for_files => p_agency_recvd_req_for_files
339 ,p_files_due => p_files_due
340 ,p_files_forwd => p_files_forwd
341 ,p_agency_brief_due => p_agency_brief_due
342 ,p_agency_brief_forwd => p_agency_brief_forwd
343 ,p_agency_recvd_appellant_brief => p_agency_recvd_appellant_brief
344 ,p_decision_date => p_decision_date
345 ,p_dec_recvd_by_agency => p_dec_recvd_by_agency
346 ,p_decision => p_decision
347 ,p_dec_forwd_to_org => p_dec_forwd_to_org
348 ,p_agency_rfr_suspense => p_agency_rfr_suspense
349 ,p_request_for_rfr => p_request_for_rfr
350 ,p_rfr_docket_num => p_rfr_docket_num
351 ,p_rfr_requested_by => p_rfr_requested_by
352 ,p_agency_rfr_due => p_agency_rfr_due
353 ,p_rfr_forwd_to_org => p_rfr_forwd_to_org
354 ,p_org_forwd_rfr_to_agency => p_org_forwd_rfr_to_agency
355 ,p_agency_forwd_rfr_ofo => p_agency_forwd_rfr_ofo
356 ,p_rfr_decision => p_rfr_decision
357 ,p_rfr_decision_date => p_rfr_decision_date
358 ,p_agency_recvd_rfr_dec => p_agency_recvd_rfr_dec
359 ,p_rfr_decision_forwd_to_org => p_rfr_decision_forwd_to_org
360 ,p_object_version_number => l_object_version_number
361 );
362 --
363 -- Call After Process User Hook
364 --
365 begin
366 ghr_agency_appeals_bk_2.update_agency_appeal_a
367 (p_effective_date => trunc(p_effective_date)
368 ,p_compl_agency_appeal_id => p_compl_agency_appeal_id
369 ,p_complaint_id => p_complaint_id
370 ,p_appeal_date => p_appeal_date
371 ,p_reason_for_appeal => p_reason_for_appeal
372 ,p_source_decision_date => p_source_decision_date
373 ,p_docket_num => p_docket_num
374 ,p_agency_recvd_req_for_files => p_agency_recvd_req_for_files
375 ,p_files_due => p_files_due
376 ,p_files_forwd => p_files_forwd
380 ,p_decision_date => p_decision_date
377 ,p_agency_brief_due => p_agency_brief_due
378 ,p_agency_brief_forwd => p_agency_brief_forwd
379 ,p_agency_recvd_appellant_brief => p_agency_recvd_appellant_brief
381 ,p_dec_recvd_by_agency => p_dec_recvd_by_agency
382 ,p_decision => p_decision
383 ,p_dec_forwd_to_org => p_dec_forwd_to_org
384 ,p_agency_rfr_suspense => p_agency_rfr_suspense
385 ,p_request_for_rfr => p_request_for_rfr
386 ,p_rfr_docket_num => p_rfr_docket_num
387 ,p_rfr_requested_by => p_rfr_requested_by
388 ,p_agency_rfr_due => p_agency_rfr_due
389 ,p_rfr_forwd_to_org => p_rfr_forwd_to_org
390 ,p_org_forwd_rfr_to_agency => p_org_forwd_rfr_to_agency
391 ,p_agency_forwd_rfr_ofo => p_agency_forwd_rfr_ofo
392 ,p_rfr_decision => p_rfr_decision
393 ,p_rfr_decision_date => p_rfr_decision_date
394 ,p_agency_recvd_rfr_dec => p_agency_recvd_rfr_dec
395 ,p_rfr_decision_forwd_to_org => p_rfr_decision_forwd_to_org
396 ,p_object_version_number => l_object_version_number
397 );
398 exception
399 when hr_api.cannot_find_prog_unit then
400 hr_api.cannot_find_prog_unit_error
401 (p_module_name => 'update_agency_appeal'
402 ,p_hook_type => 'AP'
403 );
404 end;
405 --
406 -- When in validation only mode raise the Validate_Enabled exception
407 --
408 if p_validate then
409 raise hr_api.validate_enabled;
410 end if;
411 --
412 -- Set all output arguments
413 --
414 p_object_version_number := l_object_version_number;
415 --
416 hr_utility.set_location(' Leaving:'||l_proc, 70);
417 exception
418 when hr_api.validate_enabled then
419 --
420 -- As the Validate_Enabled exception has been raised
421 -- we must rollback to the savepoint
422 --
423 rollback to update_agency_appeal;
424 --
425 -- Only set output warning arguments
426 -- (Any key or derived arguments must be set to null
427 -- when validation only mode is being used.)
428 --
429 p_object_version_number := l_object_version_number;
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 update_agency_appeal;
436 p_object_version_number := l_object_version_number;
437 hr_utility.set_location(' Leaving:'||l_proc, 90);
438 raise;
439
440 end update_agency_appeal;
441
442 -- ----------------------------------------------------------------------------
443 -- |-----------------------< delete_agency_appeal >----------------------------|
444 -- ----------------------------------------------------------------------------
445 --
446 procedure delete_agency_appeal
447 (p_validate in boolean default false
448 ,p_compl_agency_appeal_id in number
449 ,p_object_version_number in number
450 ) is
451 --
452 -- Declare cursors and local variables
453 --
454 l_proc varchar2(72) := g_package||'delete_agency_appeal';
455 l_exists boolean := false;
456
457 begin
458 hr_utility.set_location('Entering:'|| l_proc, 5);
459 --
460 --
461 savepoint delete_agency_appeal;
462 --
463 -- Truncate the time portion from all IN date parameters
464 --
465 --
466 -- Call Before Process User Hook
467 --
468 begin
469 ghr_agency_appeals_bk_3.delete_agency_appeal_b
470 (p_compl_agency_appeal_id => p_compl_agency_appeal_id
471 ,p_object_version_number => p_object_version_number
472 );
473 exception
474 when hr_api.cannot_find_prog_unit then
475 hr_api.cannot_find_prog_unit_error
476 (p_module_name => 'delete_agency_appeal'
477 ,p_hook_type => 'BP'
478 );
479 end;
480 --
481 -- Validation in addition to Row Handlers
482 --
483
484 -- Process Logic
485 ghr_caa_del.del
486 (p_compl_agency_appeal_id => p_compl_agency_appeal_id
487 ,p_object_version_number => p_object_version_number
488 );
489 --
490 hr_utility.set_location(l_proc, 8);
491 --
492 -- Call After Process User Hook
493 --
494 begin
495 ghr_agency_appeals_bk_3.delete_agency_appeal_a
496 (p_compl_agency_appeal_id => p_compl_agency_appeal_id
497 ,p_object_version_number => p_object_version_number
498 );
499 exception
500 when hr_api.cannot_find_prog_unit then
501 hr_api.cannot_find_prog_unit_error
502 (p_module_name => 'delete_agency_appeal'
503 ,p_hook_type => 'AP'
504 );
505 end;
506 --
507 -- When in validation only mode raise the Validate_Enabled exception
508 --
509 if p_validate then
510 raise hr_api.validate_enabled;
511 end if;
512 --
513 hr_utility.set_location(' Leaving:'||l_proc, 11);
514 exception
515 when hr_api.validate_enabled then
516 -- we must rollback to the savepoint
517 --
518 ROLLBACK TO delete_agency_appeal;
519 --
520 When Others then
521 ROLLBACK TO delete_agency_appeal;
522 raise;
523
524 hr_utility.set_location(' Leaving:'||l_proc, 12);
525 end delete_agency_appeal;
526
527 end ghr_agency_appeals_api;