1 Package Body ghr_cca_ins as
2 /* $Header: ghccarhi.pkb 115.1 2003/01/30 19:25:04 asubrahm noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_cca_ins.'; -- Global package name
9 --
10 -- The following global variables are only to be used by
11 -- the set_base_key_value and pre_insert procedures.
12 --
13 g_compl_appeal_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_compl_appeal_id in number) is
20 --
21 l_proc varchar2(72) := g_package||'set_base_key_value';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 ghr_cca_ins.g_compl_appeal_id_i := p_compl_appeal_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< insert_dml >------------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic. The processing of
39 -- this procedure are as follows:
40 -- 1) Initialise the object_version_number to 1 if the object_version_number
41 -- is defined as an attribute for this entity.
42 -- 2) To set and unset the g_api_dml status as required (as we are about to
43 -- perform dml).
44 -- 3) To insert the row into the schema.
45 -- 4) To trap any constraint violations that may have occurred.
46 -- 5) To raise any other errors.
47 --
48 -- Prerequisites:
49 -- This is an internal private procedure which must be called from the ins
50 -- procedure and must have all mandatory attributes set (except the
51 -- object_version_number which is initialised within this procedure).
52 --
53 -- In Parameters:
54 -- A Pl/Sql record structre.
55 --
56 -- Post Success:
57 -- The specified row will be inserted into the schema.
58 --
59 -- Post Failure:
60 -- On the insert dml failure it is important to note that we always reset the
61 -- g_api_dml status to false.
62 -- If a check, unique or parent integrity constraint violation is raised the
63 -- constraint_error procedure will be called.
64 -- If any other error is reported, the error will be raised after the
65 -- g_api_dml status is reset.
66 --
67 -- Developer Implementation Notes:
68 -- None.
69 --
70 -- Access Status:
71 -- Internal Row Handler Use Only.
72 --
73 -- {End Of Comments}
74 -- ----------------------------------------------------------------------------
75 Procedure insert_dml
76 (p_rec in out nocopy ghr_cca_shd.g_rec_type
77 ) is
78 --
79 l_proc varchar2(72) := g_package||'insert_dml';
80 --
81 Begin
82 hr_utility.set_location('Entering:'||l_proc, 5);
83 p_rec.object_version_number := 1; -- Initialise the object version
84 --
85 --
86 --
87 -- Insert the row into: ghr_compl_appeals
88 --
89 insert into ghr_compl_appeals
90 (compl_appeal_id
91 ,complaint_id
92 ,appeal_date
93 ,appealed_to
94 ,reason_for_appeal
95 ,source_decision_date
96 ,docket_num
97 ,org_notified_of_appeal
98 ,agency_recvd_req_for_files
99 ,files_due
100 ,files_forwd
101 ,agcy_recvd_appellant_brief
102 ,agency_brief_due
103 ,appellant_brief_forwd_org
104 ,org_forwd_brief_to_agency
105 ,agency_brief_forwd
106 ,decision_date
107 ,dec_recvd_by_agency
108 ,decision
109 ,dec_forwd_to_org
110 ,agency_rfr_suspense
111 ,request_for_rfr
112 ,rfr_docket_num
113 ,rfr_requested_by
114 ,agency_rfr_due
115 ,rfr_forwd_to_org
116 ,org_forwd_rfr_to_agency
117 ,agency_forwd_rfr_ofo
118 ,rfr_decision
119 ,rfr_decision_date
120 ,agency_recvd_rfr_dec
121 ,rfr_decision_forwd_to_org
122 ,object_version_number
123 )
124 Values
125 (p_rec.compl_appeal_id
126 ,p_rec.complaint_id
127 ,p_rec.appeal_date
128 ,p_rec.appealed_to
129 ,p_rec.reason_for_appeal
130 ,p_rec.source_decision_date
131 ,p_rec.docket_num
132 ,p_rec.org_notified_of_appeal
133 ,p_rec.agency_recvd_req_for_files
134 ,p_rec.files_due
135 ,p_rec.files_forwd
136 ,p_rec.agcy_recvd_appellant_brief
137 ,p_rec.agency_brief_due
138 ,p_rec.appellant_brief_forwd_org
139 ,p_rec.org_forwd_brief_to_agency
140 ,p_rec.agency_brief_forwd
141 ,p_rec.decision_date
142 ,p_rec.dec_recvd_by_agency
143 ,p_rec.decision
144 ,p_rec.dec_forwd_to_org
145 ,p_rec.agency_rfr_suspense
146 ,p_rec.request_for_rfr
147 ,p_rec.rfr_docket_num
148 ,p_rec.rfr_requested_by
149 ,p_rec.agency_rfr_due
150 ,p_rec.rfr_forwd_to_org
151 ,p_rec.org_forwd_rfr_to_agency
152 ,p_rec.agency_forwd_rfr_ofo
153 ,p_rec.rfr_decision
154 ,p_rec.rfr_decision_date
155 ,p_rec.agency_recvd_rfr_dec
156 ,p_rec.rfr_decision_forwd_to_org
157 ,p_rec.object_version_number
158 );
159 --
160 --
161 --
162 hr_utility.set_location(' Leaving:'||l_proc, 10);
163 Exception
164 When hr_api.check_integrity_violated Then
165 -- A check constraint has been violated
166 --
167 ghr_cca_shd.constraint_error
168 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
169 When hr_api.parent_integrity_violated Then
170 -- Parent integrity has been violated
171 --
172 ghr_cca_shd.constraint_error
173 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
174 When hr_api.unique_integrity_violated Then
175 -- Unique integrity has been violated
176 --
177 ghr_cca_shd.constraint_error
178 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
179 When Others Then
180 --
181 Raise;
182 End insert_dml;
183 --
184 -- ----------------------------------------------------------------------------
185 -- |------------------------------< pre_insert >------------------------------|
186 -- ----------------------------------------------------------------------------
187 -- {Start Of Comments}
188 --
189 -- Description:
190 -- This private procedure contains any processing which is required before
191 -- the insert dml. Presently, if the entity has a corresponding primary
192 -- key which is maintained by an associating sequence, the primary key for
193 -- the entity will be populated with the next sequence value in
194 -- preparation for the insert dml.
195 --
196 -- Prerequisites:
197 -- This is an internal procedure which is called from the ins procedure.
198 --
199 -- In Parameters:
200 -- A Pl/Sql record structure.
201 --
202 -- Post Success:
203 -- Processing continues.
204 --
205 -- Post Failure:
206 -- If an error has occurred, an error message and exception will be raised
207 -- but not handled.
208 --
209 -- Developer Implementation Notes:
210 -- Any pre-processing required before the insert dml is issued should be
211 -- coded within this procedure. As stated above, a good example is the
212 -- generation of a primary key number via a corresponding sequence.
213 -- It is important to note that any 3rd party maintenance should be reviewed
214 -- before placing in this procedure.
215 --
216 -- Access Status:
217 -- Internal Row Handler Use Only.
218 --
219 -- {End Of Comments}
220 -- ----------------------------------------------------------------------------
221 Procedure pre_insert
222 (p_rec in out nocopy ghr_cca_shd.g_rec_type
223 ) is
224 --
225 Cursor C_Sel1 is select ghr_compl_appeals_s.nextval from sys.dual;
226 --
227 Cursor C_Sel2 is
228 Select null
229 from ghr_compl_appeals
230 where compl_appeal_id =
231 ghr_cca_ins.g_compl_appeal_id_i;
232 --
233 l_proc varchar2(72) := g_package||'pre_insert';
234 l_exists varchar2(1);
235 --
236 Begin
237 hr_utility.set_location('Entering:'||l_proc, 5);
238 --
239 If (ghr_cca_ins.g_compl_appeal_id_i is not null) Then
240 --
241 -- Verify registered primary key values not already in use
242 --
243 Open C_Sel2;
244 Fetch C_Sel2 into l_exists;
245 If C_Sel2%found Then
246 Close C_Sel2;
247 --
248 -- The primary key values are already in use.
249 --
250 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
251 fnd_message.set_token('TABLE_NAME','ghr_compl_appeals');
252 fnd_message.raise_error;
253 End If;
254 Close C_Sel2;
255 --
256 -- Use registered key values and clear globals
257 --
258 p_rec.compl_appeal_id :=
259 ghr_cca_ins.g_compl_appeal_id_i;
260 ghr_cca_ins.g_compl_appeal_id_i := null;
261 Else
262 --
263 -- No registerd key values, so select the next sequence number
264 --
265 --
266 -- Select the next sequence number
267 --
268 Open C_Sel1;
269 Fetch C_Sel1 Into p_rec.compl_appeal_id;
270 Close C_Sel1;
271 End If;
272 --
273 hr_utility.set_location(' Leaving:'||l_proc, 10);
274 End pre_insert;
275 --
276 -- ----------------------------------------------------------------------------
277 -- |-----------------------------< post_insert >------------------------------|
278 -- ----------------------------------------------------------------------------
279 -- {Start Of Comments}
280 --
281 -- Description:
282 -- This private procedure contains any processing which is required after
283 -- the insert dml.
284 --
285 -- Prerequisites:
286 -- This is an internal procedure which is called from the ins procedure.
287 --
288 -- In Parameters:
289 -- A Pl/Sql record structre.
290 --
291 -- Post Success:
292 -- Processing continues.
293 --
294 -- Post Failure:
295 -- If an error has occurred, an error message and exception will be raised
296 -- but not handled.
297 --
298 -- Developer Implementation Notes:
299 -- Any post-processing required after the insert dml is issued should be
300 -- coded within this procedure. It is important to note that any 3rd party
301 -- maintenance should be reviewed before placing in this procedure.
302 --
303 -- Access Status:
304 -- Internal Row Handler Use Only.
305 --
306 -- {End Of Comments}
307 -- ----------------------------------------------------------------------------
308 Procedure post_insert
309 (p_effective_date in date
310 ,p_rec in ghr_cca_shd.g_rec_type
311 ) is
312 --
313 l_proc varchar2(72) := g_package||'post_insert';
314 --
315 Begin
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 begin
318 --
319 ghr_cca_rki.after_insert
320 (p_effective_date => p_effective_date
321 ,p_compl_appeal_id => p_rec.compl_appeal_id
322 ,p_complaint_id => p_rec.complaint_id
323 ,p_appeal_date => p_rec.appeal_date
324 ,p_appealed_to => p_rec.appealed_to
325 ,p_reason_for_appeal => p_rec.reason_for_appeal
326 ,p_source_decision_date => p_rec.source_decision_date
327 ,p_docket_num => p_rec.docket_num
328 ,p_org_notified_of_appeal => p_rec.org_notified_of_appeal
329 ,p_agency_recvd_req_for_files => p_rec.agency_recvd_req_for_files
330 ,p_files_due => p_rec.files_due
331 ,p_files_forwd => p_rec.files_forwd
332 ,p_agcy_recvd_appellant_brief => p_rec.agcy_recvd_appellant_brief
333 ,p_agency_brief_due => p_rec.agency_brief_due
334 ,p_appellant_brief_forwd_org => p_rec.appellant_brief_forwd_org
335 ,p_org_forwd_brief_to_agency => p_rec.org_forwd_brief_to_agency
336 ,p_agency_brief_forwd => p_rec.agency_brief_forwd
337 ,p_decision_date => p_rec.decision_date
338 ,p_dec_recvd_by_agency => p_rec.dec_recvd_by_agency
339 ,p_decision => p_rec.decision
340 ,p_dec_forwd_to_org => p_rec.dec_forwd_to_org
341 ,p_agency_rfr_suspense => p_rec.agency_rfr_suspense
342 ,p_request_for_rfr => p_rec.request_for_rfr
343 ,p_rfr_docket_num => p_rec.rfr_docket_num
344 ,p_rfr_requested_by => p_rec.rfr_requested_by
345 ,p_agency_rfr_due => p_rec.agency_rfr_due
346 ,p_rfr_forwd_to_org => p_rec.rfr_forwd_to_org
347 ,p_org_forwd_rfr_to_agency => p_rec.org_forwd_rfr_to_agency
348 ,p_agency_forwd_rfr_ofo => p_rec.agency_forwd_rfr_ofo
349 ,p_rfr_decision => p_rec.rfr_decision
350 ,p_rfr_decision_date => p_rec.rfr_decision_date
351 ,p_agency_recvd_rfr_dec => p_rec.agency_recvd_rfr_dec
352 ,p_rfr_decision_forwd_to_org => p_rec.rfr_decision_forwd_to_org
353 ,p_object_version_number => p_rec.object_version_number
354 );
355 --
356 exception
357 --
358 when hr_api.cannot_find_prog_unit then
359 --
360 hr_api.cannot_find_prog_unit_error
361 (p_module_name => 'GHR_COMPL_APPEALS'
362 ,p_hook_type => 'AI');
363 --
364 end;
365 --
366 hr_utility.set_location(' Leaving:'||l_proc, 10);
367 End post_insert;
368 --
369 -- ----------------------------------------------------------------------------
370 -- |---------------------------------< ins >----------------------------------|
371 -- ----------------------------------------------------------------------------
372 Procedure ins
373 (p_effective_date in date
374 ,p_rec in out nocopy ghr_cca_shd.g_rec_type
375 ) is
376 --
377 l_proc varchar2(72) := g_package||'ins';
378 --
379 Begin
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 -- Call the supporting insert validate operations
383 --
384 ghr_cca_bus.insert_validate
385 (p_effective_date
386 ,p_rec
387 );
388 --
389 -- Call the supporting pre-insert operation
390 --
391 ghr_cca_ins.pre_insert(p_rec);
392 --
393 -- Insert the row
394 --
395 ghr_cca_ins.insert_dml(p_rec);
396 --
397 -- Call the supporting post-insert operation
398 --
399 ghr_cca_ins.post_insert
400 (p_effective_date
401 ,p_rec
402 );
403 --
404 hr_utility.set_location('Leaving:'||l_proc, 20);
405 end ins;
406 --
407 -- ----------------------------------------------------------------------------
408 -- |---------------------------------< ins >----------------------------------|
409 -- ----------------------------------------------------------------------------
410 Procedure ins
411 (p_effective_date in date
412 ,p_complaint_id in number
413 ,p_appeal_date in date default null
414 ,p_appealed_to in varchar2 default null
415 ,p_reason_for_appeal in varchar2 default null
416 ,p_source_decision_date in date default null
417 ,p_docket_num in varchar2 default null
418 ,p_org_notified_of_appeal in date default null
419 ,p_agency_recvd_req_for_files in date default null
420 ,p_files_due in date default null
421 ,p_files_forwd in date default null
422 ,p_agcy_recvd_appellant_brief in date default null
423 ,p_agency_brief_due in date default null
424 ,p_appellant_brief_forwd_org in date default null
425 ,p_org_forwd_brief_to_agency in date default null
426 ,p_agency_brief_forwd in date default null
427 ,p_decision_date in date default null
428 ,p_dec_recvd_by_agency in date default null
429 ,p_decision in varchar2 default null
430 ,p_dec_forwd_to_org in date default null
431 ,p_agency_rfr_suspense in date default null
432 ,p_request_for_rfr in date default null
433 ,p_rfr_docket_num in varchar2 default null
434 ,p_rfr_requested_by in varchar2 default null
435 ,p_agency_rfr_due in date default null
436 ,p_rfr_forwd_to_org in date default null
437 ,p_org_forwd_rfr_to_agency in date default null
438 ,p_agency_forwd_rfr_ofo in date default null
439 ,p_rfr_decision in varchar2 default null
440 ,p_rfr_decision_date in date default null
441 ,p_agency_recvd_rfr_dec in date default null
442 ,p_rfr_decision_forwd_to_org in date default null
443 ,p_compl_appeal_id out nocopy number
444 ,p_object_version_number out nocopy number
445 ) is
446 --
447 l_rec ghr_cca_shd.g_rec_type;
448 l_proc varchar2(72) := g_package||'ins';
449 --
450 Begin
451 hr_utility.set_location('Entering:'||l_proc, 5);
452 --
453 -- Call conversion function to turn arguments into the
454 -- p_rec structure.
455 --
456 l_rec :=
457 ghr_cca_shd.convert_args
458 (null
459 ,p_complaint_id
460 ,p_appeal_date
461 ,p_appealed_to
462 ,p_reason_for_appeal
463 ,p_source_decision_date
464 ,p_docket_num
465 ,p_org_notified_of_appeal
466 ,p_agency_recvd_req_for_files
467 ,p_files_due
468 ,p_files_forwd
469 ,p_agcy_recvd_appellant_brief
470 ,p_agency_brief_due
471 ,p_appellant_brief_forwd_org
472 ,p_org_forwd_brief_to_agency
473 ,p_agency_brief_forwd
474 ,p_decision_date
475 ,p_dec_recvd_by_agency
476 ,p_decision
477 ,p_dec_forwd_to_org
478 ,p_agency_rfr_suspense
479 ,p_request_for_rfr
480 ,p_rfr_docket_num
481 ,p_rfr_requested_by
482 ,p_agency_rfr_due
483 ,p_rfr_forwd_to_org
484 ,p_org_forwd_rfr_to_agency
485 ,p_agency_forwd_rfr_ofo
486 ,p_rfr_decision
487 ,p_rfr_decision_date
488 ,p_agency_recvd_rfr_dec
489 ,p_rfr_decision_forwd_to_org
490 ,null
491 );
492 --
493 -- Having converted the arguments into the ghr_cca_rec
494 -- plsql record structure we call the corresponding record business process.
495 --
496 ghr_cca_ins.ins
497 (p_effective_date
498 ,l_rec
499 );
500 --
501 -- As the primary key argument(s)
502 -- are specified as an OUT's we must set these values.
503 --
504 p_compl_appeal_id := l_rec.compl_appeal_id;
505 p_object_version_number := l_rec.object_version_number;
506 --
507 hr_utility.set_location(' Leaving:'||l_proc, 10);
508 End ins;
509 --
510 end ghr_cca_ins;
[Home] [Help]