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