1 Package Body ben_cpg_ins as
2 /* $Header: becpgrhi.pkb 120.0 2005/05/28 01:13 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cpg_ins.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_group_per_in_ler_id_i number default null;
15 g_group_pl_id_i number default null;
16 g_group_oipl_id_i number default null;
17 --
18 -- ----------------------------------------------------------------------------
19 -- |------------------------< set_base_key_value >----------------------------|
20 -- ----------------------------------------------------------------------------
21 procedure set_base_key_value
22 (p_group_per_in_ler_id in number
23 ,p_group_pl_id in number
24 ,p_group_oipl_id in number) is
25 --
26 l_proc varchar2(72) := g_package||'set_base_key_value';
27 --
28 Begin
29 if g_debug then
30 hr_utility.set_location('Entering:'||l_proc, 10);
31 end if;
32 --
33 ben_cpg_ins.g_group_per_in_ler_id_i := p_group_per_in_ler_id;
34 ben_cpg_ins.g_group_pl_id_i := p_group_pl_id;
35 ben_cpg_ins.g_group_oipl_id_i := p_group_oipl_id;
36 --
37 if g_debug then
38 hr_utility.set_location(' Leaving:'||l_proc, 20);
39 end if;
40 End set_base_key_value;
41 --
42 --
43 -- ----------------------------------------------------------------------------
44 -- |------------------------------< insert_dml >------------------------------|
45 -- ----------------------------------------------------------------------------
46 -- {Start Of Comments}
47 --
48 -- Description:
49 -- This procedure controls the actual dml insert logic. The processing of
50 -- this procedure are as follows:
51 -- 1) Initialise the object_version_number to 1 if the object_version_number
52 -- is defined as an attribute for this entity.
53 -- 2) To set and unset the g_api_dml status as required (as we are about to
54 -- perform dml).
55 -- 3) To insert the row into the schema.
56 -- 4) To trap any constraint violations that may have occurred.
57 -- 5) To raise any other errors.
58 --
59 -- Prerequisites:
60 -- This is an internal private procedure which must be called from the ins
61 -- procedure and must have all mandatory attributes set (except the
62 -- object_version_number which is initialised within this procedure).
63 --
64 -- In Parameters:
65 -- A Pl/Sql record structre.
66 --
67 -- Post Success:
68 -- The specified row will be inserted into the schema.
69 --
70 -- Post Failure:
71 -- On the insert dml failure it is important to note that we always reset the
72 -- g_api_dml status to false.
73 -- If a check, unique or parent integrity constraint violation is raised the
74 -- constraint_error procedure will be called.
75 -- If any other error is reported, the error will be raised after the
76 -- g_api_dml status is reset.
77 --
78 -- Developer Implementation Notes:
79 -- None.
80 --
81 -- Access Status:
82 -- Internal Row Handler Use Only.
83 --
84 -- {End Of Comments}
85 -- ----------------------------------------------------------------------------
86 Procedure insert_dml
87 (p_rec in out nocopy ben_cpg_shd.g_rec_type
88 ) is
89 --
90 l_proc varchar2(72) := g_package||'insert_dml';
91 --
92 Begin
93 if g_debug then
94 hr_utility.set_location('Entering:'||l_proc, 5);
95 end if;
96 p_rec.object_version_number := 1; -- Initialise the object version
97 --
98 ben_cpg_shd.g_api_dml := true; -- Set the api dml status
99 --
100 -- Insert the row into: ben_cwb_person_groups
101 --
102 insert into ben_cwb_person_groups
103 (group_per_in_ler_id
104 ,group_pl_id
105 ,group_oipl_id
106 ,lf_evt_ocrd_dt
107 ,bdgt_pop_cd
108 ,due_dt
109 ,access_cd
110 ,approval_cd
111 ,approval_date
112 ,approval_comments
113 ,submit_cd
114 ,submit_date
115 ,submit_comments
116 ,dist_bdgt_val
117 ,ws_bdgt_val
118 ,rsrv_val
119 ,dist_bdgt_mn_val
120 ,dist_bdgt_mx_val
121 ,dist_bdgt_incr_val
122 ,ws_bdgt_mn_val
123 ,ws_bdgt_mx_val
124 ,ws_bdgt_incr_val
125 ,rsrv_mn_val
126 ,rsrv_mx_val
127 ,rsrv_incr_val
128 ,dist_bdgt_iss_val
129 ,ws_bdgt_iss_val
130 ,dist_bdgt_iss_date
131 ,ws_bdgt_iss_date
132 ,ws_bdgt_val_last_upd_date
133 ,dist_bdgt_val_last_upd_date
134 ,rsrv_val_last_upd_date
135 ,ws_bdgt_val_last_upd_by
136 ,dist_bdgt_val_last_upd_by
137 ,rsrv_val_last_upd_by
138 ,object_version_number
139 )
140 Values
141 (p_rec.group_per_in_ler_id
142 ,p_rec.group_pl_id
143 ,p_rec.group_oipl_id
144 ,p_rec.lf_evt_ocrd_dt
145 ,p_rec.bdgt_pop_cd
146 ,p_rec.due_dt
147 ,p_rec.access_cd
148 ,p_rec.approval_cd
149 ,p_rec.approval_date
150 ,p_rec.approval_comments
151 ,p_rec.submit_cd
152 ,p_rec.submit_date
153 ,p_rec.submit_comments
154 ,p_rec.dist_bdgt_val
155 ,p_rec.ws_bdgt_val
156 ,p_rec.rsrv_val
157 ,p_rec.dist_bdgt_mn_val
158 ,p_rec.dist_bdgt_mx_val
159 ,p_rec.dist_bdgt_incr_val
160 ,p_rec.ws_bdgt_mn_val
161 ,p_rec.ws_bdgt_mx_val
162 ,p_rec.ws_bdgt_incr_val
163 ,p_rec.rsrv_mn_val
164 ,p_rec.rsrv_mx_val
165 ,p_rec.rsrv_incr_val
166 ,p_rec.dist_bdgt_iss_val
167 ,p_rec.ws_bdgt_iss_val
168 ,p_rec.dist_bdgt_iss_date
169 ,p_rec.ws_bdgt_iss_date
170 ,p_rec.ws_bdgt_val_last_upd_date
171 ,p_rec.dist_bdgt_val_last_upd_date
172 ,p_rec.rsrv_val_last_upd_date
173 ,p_rec.ws_bdgt_val_last_upd_by
174 ,p_rec.dist_bdgt_val_last_upd_by
175 ,p_rec.rsrv_val_last_upd_by
176 ,p_rec.object_version_number
177 );
178 --
179 ben_cpg_shd.g_api_dml := false; -- Unset the api dml status
180 --
181 if g_debug then
182 hr_utility.set_location(' Leaving:'||l_proc, 10);
183 end if;
184 Exception
185 When hr_api.check_integrity_violated Then
186 -- A check constraint has been violated
187 ben_cpg_shd.g_api_dml := false; -- Unset the api dml status
188 ben_cpg_shd.constraint_error
189 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
190 When hr_api.parent_integrity_violated Then
191 -- Parent integrity has been violated
192 ben_cpg_shd.g_api_dml := false; -- Unset the api dml status
193 ben_cpg_shd.constraint_error
194 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
195 When hr_api.unique_integrity_violated Then
196 -- Unique integrity has been violated
197 ben_cpg_shd.g_api_dml := false; -- Unset the api dml status
198 ben_cpg_shd.constraint_error
199 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
200 When Others Then
201 ben_cpg_shd.g_api_dml := false; -- Unset the api dml status
202 Raise;
203 End insert_dml;
204 --
205 -- ----------------------------------------------------------------------------
206 -- |------------------------------< pre_insert >------------------------------|
207 -- ----------------------------------------------------------------------------
208 -- {Start Of Comments}
209 --
210 -- Description:
211 -- This private procedure contains any processing which is required before
212 -- the insert dml. Presently, if the entity has a corresponding primary
213 -- key which is maintained by an associating sequence, the primary key for
214 -- the entity will be populated with the next sequence value in
215 -- preparation for the insert dml.
216 --
217 -- Prerequisites:
218 -- This is an internal procedure which is called from the ins procedure.
219 --
220 -- In Parameters:
221 -- A Pl/Sql record structure.
222 --
223 -- Post Success:
224 -- Processing continues.
225 --
226 -- Post Failure:
227 -- If an error has occurred, an error message and exception will be raised
228 -- but not handled.
229 --
230 -- Developer Implementation Notes:
231 -- Any pre-processing required before the insert dml is issued should be
232 -- coded within this procedure. As stated above, a good example is the
233 -- generation of a primary key number via a corresponding sequence.
234 -- It is important to note that any 3rd party maintenance should be reviewed
235 -- before placing in this procedure.
236 --
237 -- Access Status:
238 -- Internal Row Handler Use Only.
239 --
240 -- {End Of Comments}
241 -- ----------------------------------------------------------------------------
242 Procedure pre_insert
243 (p_rec in out nocopy ben_cpg_shd.g_rec_type
244 ) is
245 --
246 Cursor C_Sel2 is
247 Select null
248 from ben_cwb_person_groups
249 where group_per_in_ler_id =
250 ben_cpg_ins.g_group_per_in_ler_id_i
251 or group_pl_id =
252 ben_cpg_ins.g_group_pl_id_i
253 or group_oipl_id =
254 ben_cpg_ins.g_group_oipl_id_i;
255 --
256 l_proc varchar2(72) := g_package||'pre_insert';
257 l_exists varchar2(1);
258 --
259 Begin
260 if g_debug then
261 hr_utility.set_location('Entering:'||l_proc, 5);
262 end if;
263 --
264 If (ben_cpg_ins.g_group_per_in_ler_id_i is not null or
265 ben_cpg_ins.g_group_pl_id_i is not null or
266 ben_cpg_ins.g_group_oipl_id_i is not null) Then
267 --
268 -- Verify registered primary key values not already in use
269 --
270 Open C_Sel2;
271 Fetch C_Sel2 into l_exists;
272 If C_Sel2%found Then
273 Close C_Sel2;
274 --
275 -- The primary key values are already in use.
276 --
277 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
278 fnd_message.set_token('TABLE_NAME','ben_cwb_person_groups');
279 fnd_message.raise_error;
280 End If;
281 Close C_Sel2;
282 --
283 -- Use registered key values and clear globals
284 --
285 p_rec.group_per_in_ler_id :=
286 ben_cpg_ins.g_group_per_in_ler_id_i;
287 ben_cpg_ins.g_group_per_in_ler_id_i := null;
288 p_rec.group_pl_id :=
289 ben_cpg_ins.g_group_pl_id_i;
290 ben_cpg_ins.g_group_pl_id_i := null;
291 p_rec.group_oipl_id :=
292 ben_cpg_ins.g_group_oipl_id_i;
293 ben_cpg_ins.g_group_oipl_id_i := null;
294 Else
295 -- Commented out the following code as it is not required.
296 null;
297 /* --
298 -- No registerd key values, so select the next sequence number
299 --
300 --
301 -- Select the next sequence number
302 --
303 Open C_Sel1;
304 Fetch C_Sel1 Into p_rec.group_oipl_id;
305 Close C_Sel1; */
306 End If;
307 --
308 if g_debug then
309 hr_utility.set_location(' Leaving:'||l_proc, 10);
310 end if;
311 End pre_insert;
312 --
313 -- ----------------------------------------------------------------------------
317 --
314 -- |-----------------------------< post_insert >------------------------------|
315 -- ----------------------------------------------------------------------------
316 -- {Start Of Comments}
318 -- Description:
319 -- This private procedure contains any processing which is required after
320 -- the insert dml.
321 --
322 -- Prerequisites:
323 -- This is an internal procedure which is called from the ins procedure.
324 --
325 -- In Parameters:
326 -- A Pl/Sql record structre.
327 --
328 -- Post Success:
329 -- Processing continues.
330 --
331 -- Post Failure:
332 -- If an error has occurred, an error message and exception will be raised
333 -- but not handled.
334 --
335 -- Developer Implementation Notes:
336 -- Any post-processing required after the insert dml is issued should be
337 -- coded within this procedure. It is important to note that any 3rd party
338 -- maintenance should be reviewed before placing in this procedure.
339 --
340 -- Access Status:
341 -- Internal Row Handler Use Only.
342 --
343 -- {End Of Comments}
344 -- ----------------------------------------------------------------------------
345 Procedure post_insert
346 (p_rec in ben_cpg_shd.g_rec_type
347 ) is
348 --
349 l_proc varchar2(72) := g_package||'post_insert';
350 --
351 Begin
352 if g_debug then
353 hr_utility.set_location('Entering:'||l_proc, 5);
354 end if;
355 begin
356 --
357 ben_cpg_rki.after_insert
358 (p_group_per_in_ler_id
359 => p_rec.group_per_in_ler_id
360 ,p_group_pl_id
361 => p_rec.group_pl_id
362 ,p_group_oipl_id
363 => p_rec.group_oipl_id
364 ,p_lf_evt_ocrd_dt
365 => p_rec.lf_evt_ocrd_dt
366 ,p_bdgt_pop_cd
367 => p_rec.bdgt_pop_cd
368 ,p_due_dt
369 => p_rec.due_dt
370 ,p_access_cd
371 => p_rec.access_cd
372 ,p_approval_cd
373 => p_rec.approval_cd
374 ,p_approval_date
375 => p_rec.approval_date
376 ,p_approval_comments
377 => p_rec.approval_comments
378 ,p_submit_cd
379 => p_rec.submit_cd
380 ,p_submit_date
381 => p_rec.submit_date
382 ,p_submit_comments
383 => p_rec.submit_comments
384 ,p_dist_bdgt_val
385 => p_rec.dist_bdgt_val
386 ,p_ws_bdgt_val
387 => p_rec.ws_bdgt_val
388 ,p_rsrv_val
389 => p_rec.rsrv_val
390 ,p_dist_bdgt_mn_val
391 => p_rec.dist_bdgt_mn_val
392 ,p_dist_bdgt_mx_val
393 => p_rec.dist_bdgt_mx_val
394 ,p_dist_bdgt_incr_val
395 => p_rec.dist_bdgt_incr_val
396 ,p_ws_bdgt_mn_val
397 => p_rec.ws_bdgt_mn_val
398 ,p_ws_bdgt_mx_val
399 => p_rec.ws_bdgt_mx_val
400 ,p_ws_bdgt_incr_val
401 => p_rec.ws_bdgt_incr_val
402 ,p_rsrv_mn_val
403 => p_rec.rsrv_mn_val
404 ,p_rsrv_mx_val
405 => p_rec.rsrv_mx_val
406 ,p_rsrv_incr_val
407 => p_rec.rsrv_incr_val
408 ,p_dist_bdgt_iss_val
409 => p_rec.dist_bdgt_iss_val
410 ,p_ws_bdgt_iss_val
411 => p_rec.ws_bdgt_iss_val
412 ,p_dist_bdgt_iss_date
413 => p_rec.dist_bdgt_iss_date
414 ,p_ws_bdgt_iss_date
415 => p_rec.ws_bdgt_iss_date
416 ,p_ws_bdgt_val_last_upd_date
417 => p_rec.ws_bdgt_val_last_upd_date
418 ,p_dist_bdgt_val_last_upd_date
419 => p_rec.dist_bdgt_val_last_upd_date
420 ,p_rsrv_val_last_upd_date
421 => p_rec.rsrv_val_last_upd_date
422 ,p_ws_bdgt_val_last_upd_by
423 => p_rec.ws_bdgt_val_last_upd_by
424 ,p_dist_bdgt_val_last_upd_by
425 => p_rec.dist_bdgt_val_last_upd_by
426 ,p_rsrv_val_last_upd_by
427 => p_rec.rsrv_val_last_upd_by
428 ,p_object_version_number
429 => p_rec.object_version_number
430 );
431 --
432 exception
433 --
434 when hr_api.cannot_find_prog_unit then
435 --
436 hr_api.cannot_find_prog_unit_error
437 (p_module_name => 'BEN_CWB_PERSON_GROUPS'
438 ,p_hook_type => 'AI');
439 --
440 end;
441 --
442 if g_debug then
443 hr_utility.set_location(' Leaving:'||l_proc, 10);
444 end if;
445 End post_insert;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |---------------------------------< ins >----------------------------------|
449 -- ----------------------------------------------------------------------------
450 Procedure ins
451 (p_rec in out nocopy ben_cpg_shd.g_rec_type
452 ) is
453 --
454 l_proc varchar2(72) := g_package||'ins';
455 --
456 Begin
457 if g_debug then
458 hr_utility.set_location('Entering:'||l_proc, 5);
459 end if;
460 --
461 -- Call the supporting insert validate operations
462 --
463 ben_cpg_bus.insert_validate
464 (p_rec
465 );
466 --
467 -- Call to raise any errors on multi-message list
468 hr_multi_message.end_validation_set;
469 --
470 -- Call the supporting pre-insert operation
471 --
472 ben_cpg_ins.pre_insert(p_rec);
473 --
474 -- Insert the row
475 --
476 ben_cpg_ins.insert_dml(p_rec);
477 --
478 -- Call the supporting post-insert operation
479 --
480 ben_cpg_ins.post_insert
481 (p_rec
482 );
483 --
484 -- Call to raise any errors on multi-message list
485 hr_multi_message.end_validation_set;
489 end if;
486 --
487 if g_debug then
488 hr_utility.set_location('Leaving:'||l_proc, 20);
490 end ins;
491 --
492 -- ----------------------------------------------------------------------------
493 -- |---------------------------------< ins >----------------------------------|
494 -- ----------------------------------------------------------------------------
495 -- This procedure is asssuming group_per_in_ler_id, group_pl_id, group_oipl_id
496 -- as out parameters. But the values for these parameters are passed by the
497 -- corresponding api, changing the type of the parameters to in out.
498 -- Also modified the code so that it passes the value to overloaded ins
499 Procedure ins
500 (p_group_per_in_ler_id in number
501 ,p_group_pl_id in number
502 ,p_group_oipl_id in number
503 ,p_lf_evt_ocrd_dt in date
504 ,p_bdgt_pop_cd in varchar2 default null
505 ,p_due_dt in date default null
506 ,p_access_cd in varchar2 default null
507 ,p_approval_cd in varchar2 default null
508 ,p_approval_date in date default null
509 ,p_approval_comments in varchar2 default null
510 ,p_submit_cd in varchar2 default null
511 ,p_submit_date in date default null
512 ,p_submit_comments in varchar2 default null
513 ,p_dist_bdgt_val in number default null
514 ,p_ws_bdgt_val in number default null
515 ,p_rsrv_val in number default null
516 ,p_dist_bdgt_mn_val in number default null
517 ,p_dist_bdgt_mx_val in number default null
518 ,p_dist_bdgt_incr_val in number default null
519 ,p_ws_bdgt_mn_val in number default null
520 ,p_ws_bdgt_mx_val in number default null
521 ,p_ws_bdgt_incr_val in number default null
522 ,p_rsrv_mn_val in number default null
523 ,p_rsrv_mx_val in number default null
524 ,p_rsrv_incr_val in number default null
525 ,p_dist_bdgt_iss_val in number default null
526 ,p_ws_bdgt_iss_val in number default null
527 ,p_dist_bdgt_iss_date in date default null
528 ,p_ws_bdgt_iss_date in date default null
529 ,p_ws_bdgt_val_last_upd_date in date default null
530 ,p_dist_bdgt_val_last_upd_date in date default null
531 ,p_rsrv_val_last_upd_date in date default null
532 ,p_ws_bdgt_val_last_upd_by in number default null
533 ,p_dist_bdgt_val_last_upd_by in number default null
534 ,p_rsrv_val_last_upd_by in number default null
535 ,p_object_version_number out nocopy number
536 ) is
537 --
538 l_rec ben_cpg_shd.g_rec_type;
539 l_proc varchar2(72) := g_package||'ins';
540 --
541 Begin
542 if g_debug then
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 end if;
545 --
546 -- Call conversion function to turn arguments into the
547 -- p_rec structure.
548 --
549 l_rec :=
550 ben_cpg_shd.convert_args
551 (p_group_per_in_ler_id
552 ,p_group_pl_id
553 ,p_group_oipl_id
554 ,p_lf_evt_ocrd_dt
555 ,p_bdgt_pop_cd
556 ,p_due_dt
557 ,p_access_cd
558 ,p_approval_cd
559 ,p_approval_date
560 ,p_approval_comments
561 ,p_submit_cd
562 ,p_submit_date
563 ,p_submit_comments
564 ,p_dist_bdgt_val
565 ,p_ws_bdgt_val
566 ,p_rsrv_val
567 ,p_dist_bdgt_mn_val
568 ,p_dist_bdgt_mx_val
569 ,p_dist_bdgt_incr_val
570 ,p_ws_bdgt_mn_val
571 ,p_ws_bdgt_mx_val
572 ,p_ws_bdgt_incr_val
573 ,p_rsrv_mn_val
574 ,p_rsrv_mx_val
575 ,p_rsrv_incr_val
576 ,p_dist_bdgt_iss_val
577 ,p_ws_bdgt_iss_val
578 ,p_dist_bdgt_iss_date
579 ,p_ws_bdgt_iss_date
580 ,p_ws_bdgt_val_last_upd_date
581 ,p_dist_bdgt_val_last_upd_date
582 ,p_rsrv_val_last_upd_date
583 ,p_ws_bdgt_val_last_upd_by
584 ,p_dist_bdgt_val_last_upd_by
585 ,p_rsrv_val_last_upd_by
586 ,null
587 );
588 --
589 -- Having converted the arguments into the ben_cpg_rec
590 -- plsql record structure we call the corresponding record business process.
591 --
592 ben_cpg_ins.ins
593 (l_rec
594 );
595 --
596 p_object_version_number := l_rec.object_version_number;
597 --
598 if g_debug then
599 hr_utility.set_location(' Leaving:'||l_proc, 10);
600 end if;
601 End ins;
602 --
603 end ben_cpg_ins;
[Home] [Help]