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