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