[Home] [Help]
PACKAGE BODY: APPS.PAY_PGP_INS
Source
1 Package Body pay_pgp_ins as
2 /* $Header: pypgprhi.pkb 115.0 99/07/17 06:22:02 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pgp_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The functions of this
17 -- procedure are as follows:
18 -- 1) To set and unset the g_api_dml status as required (as we are about to
19 -- perform dml).
20 -- 2) To insert the row into the schema.
21 -- 3) To trap any constraint violations that may have occurred.
22 -- 4) To raise any other errors.
23 --
24 -- Pre Conditions:
25 -- This is an internal private procedure which must be called from the ins
26 -- procedure and must have all mandatory arguments set.
27 --
28 -- In Arguments:
29 -- A Pl/Sql record structre.
30 --
31 -- Post Success:
32 -- The specified row will be inserted into the schema.
33 --
34 -- Post Failure:
35 -- On the insert dml failure it is important to note that we always reset the
36 -- g_api_dml status to false.
37 -- If a check, unique or parent integrity constraint violation is raised the
38 -- constraint_error procedure will be called.
39 -- If any other error is reported, the error will be raised after the
40 -- g_api_dml status is reset.
41 --
42 -- Developer Implementation Notes:
43 -- None.
44 --
45 -- Access Status:
46 -- Internal Table Handler Use Only.
47 --
48 -- {End Of Comments}
49 -- ----------------------------------------------------------------------------
50 Procedure insert_dml(p_rec in out pay_pgp_shd.g_rec_type) is
51 --
52 l_proc varchar2(72) := g_package||'insert_dml';
53 --
54 Begin
55 hr_utility.set_location('Entering:'||l_proc, 5);
56 --
57 pay_pgp_shd.g_api_dml := true; -- Set the api dml status
58 --
59 -- Insert the row into: pay_people_groups
60 --
61 insert into pay_people_groups
62 ( people_group_id,
63 group_name,
64 id_flex_num,
65 summary_flag,
66 enabled_flag,
67 start_date_active,
68 end_date_active,
69 segment1,
70 segment2,
71 segment3,
72 segment4,
73 segment5,
74 segment6,
75 segment7,
76 segment8,
77 segment9,
78 segment10,
79 segment11,
80 segment12,
81 segment13,
82 segment14,
83 segment15,
84 segment16,
85 segment17,
86 segment18,
87 segment19,
88 segment20,
89 segment21,
90 segment22,
91 segment23,
92 segment24,
93 segment25,
94 segment26,
95 segment27,
96 segment28,
97 segment29,
98 segment30
99 )
100 Values
101 ( p_rec.people_group_id,
102 p_rec.group_name,
103 p_rec.id_flex_num,
104 p_rec.summary_flag,
105 p_rec.enabled_flag,
106 p_rec.start_date_active,
107 p_rec.end_date_active,
108 p_rec.segment1,
109 p_rec.segment2,
110 p_rec.segment3,
111 p_rec.segment4,
112 p_rec.segment5,
113 p_rec.segment6,
114 p_rec.segment7,
115 p_rec.segment8,
116 p_rec.segment9,
117 p_rec.segment10,
118 p_rec.segment11,
119 p_rec.segment12,
120 p_rec.segment13,
121 p_rec.segment14,
122 p_rec.segment15,
123 p_rec.segment16,
124 p_rec.segment17,
125 p_rec.segment18,
126 p_rec.segment19,
127 p_rec.segment20,
128 p_rec.segment21,
129 p_rec.segment22,
130 p_rec.segment23,
131 p_rec.segment24,
132 p_rec.segment25,
133 p_rec.segment26,
134 p_rec.segment27,
135 p_rec.segment28,
136 p_rec.segment29,
137 p_rec.segment30
138 );
139 --
140 pay_pgp_shd.g_api_dml := false; -- Unset the api dml status
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 pay_pgp_shd.g_api_dml := false; -- Unset the api dml status
147 pay_pgp_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 pay_pgp_shd.g_api_dml := false; -- Unset the api dml status
152 pay_pgp_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 pay_pgp_shd.g_api_dml := false; -- Unset the api dml status
157 pay_pgp_shd.constraint_error
158 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
159 When Others Then
160 pay_pgp_shd.g_api_dml := false; -- Unset the api dml status
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 -- Pre Conditions:
177 -- This is an internal procedure which is called from the ins procedure.
178 --
179 -- In Arguments:
180 -- A Pl/Sql record structre.
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 Table Handler Use Only.
198 --
199 -- {End Of Comments}
200 -- ----------------------------------------------------------------------------
201 Procedure pre_insert(p_rec in out pay_pgp_shd.g_rec_type) is
202 --
203 l_proc varchar2(72) := g_package||'pre_insert';
204 --
205 Cursor C_Sel1 is select pay_people_groups_s.nextval from sys.dual;
206 --
207 Begin
208 hr_utility.set_location('Entering:'||l_proc, 5);
209 --
210 --
211 -- Select the next sequence number
212 --
213 Open C_Sel1;
214 Fetch C_Sel1 Into p_rec.people_group_id;
215 Close C_Sel1;
216 --
217 --
218 -- initialise the hardcode flexfield specific attributes:
219 --
220 p_rec.summary_flag := 'N';
221 p_rec.enabled_flag := 'Y';
222 p_rec.start_date_active := null;
223 p_rec.end_date_active := null;
224 --
225 -- concatenate the segments for the group_name using a period as a
226 -- separator
227 --
228 p_rec.group_name :=
229 hr_api.return_concat_kf_segments
230 (p_id_flex_num => p_rec.id_flex_num,
231 p_application_id => 801,
232 p_id_flex_code => 'GRP',
233 p_segment1 => p_rec.segment1,
234 p_segment2 => p_rec.segment2,
235 p_segment3 => p_rec.segment3,
236 p_segment4 => p_rec.segment4,
237 p_segment5 => p_rec.segment5,
238 p_segment6 => p_rec.segment6,
239 p_segment7 => p_rec.segment7,
240 p_segment8 => p_rec.segment8,
241 p_segment9 => p_rec.segment9,
242 p_segment10 => p_rec.segment10,
243 p_segment11 => p_rec.segment11,
244 p_segment12 => p_rec.segment12,
245 p_segment13 => p_rec.segment13,
246 p_segment14 => p_rec.segment14,
247 p_segment15 => p_rec.segment15,
248 p_segment16 => p_rec.segment16,
249 p_segment17 => p_rec.segment17,
250 p_segment18 => p_rec.segment18,
251 p_segment19 => p_rec.segment19,
252 p_segment20 => p_rec.segment20,
253 p_segment21 => p_rec.segment21,
254 p_segment22 => p_rec.segment22,
255 p_segment23 => p_rec.segment23,
256 p_segment24 => p_rec.segment24,
257 p_segment25 => p_rec.segment25,
258 p_segment26 => p_rec.segment26,
259 p_segment27 => p_rec.segment27,
260 p_segment28 => p_rec.segment28,
261 p_segment29 => p_rec.segment29,
262 p_segment30 => p_rec.segment30);
263 --
264 hr_utility.set_location(' Leaving:'||l_proc, 10);
265 End pre_insert;
266 --
267 -- ----------------------------------------------------------------------------
268 -- |-----------------------------< post_insert >------------------------------|
269 -- ----------------------------------------------------------------------------
270 -- {Start Of Comments}
271 --
272 -- Description:
273 -- This private procedure contains any processing which is required after the
274 -- insert dml.
275 --
276 -- Pre Conditions:
277 -- This is an internal procedure which is called from the ins procedure.
278 --
279 -- In Arguments:
280 -- A Pl/Sql record structre.
281 --
282 -- Post Success:
283 -- Processing continues.
284 --
285 -- Post Failure:
286 -- If an error has occurred, an error message and exception will be raised
287 -- but not handled.
288 --
289 -- Developer Implementation Notes:
290 -- Any post-processing required after the insert dml is issued should be
291 -- coded within this procedure. It is important to note that any 3rd party
292 -- maintenance should be reviewed before placing in this procedure.
293 --
294 -- Access Status:
295 -- Internal Table Handler Use Only.
296 --
297 -- {End Of Comments}
298 -- ----------------------------------------------------------------------------
299 Procedure post_insert(p_rec in pay_pgp_shd.g_rec_type) is
300 --
301 l_proc varchar2(72) := g_package||'post_insert';
302 --
303 Begin
304 hr_utility.set_location('Entering:'||l_proc, 5);
305 --
306 hr_utility.set_location(' Leaving:'||l_proc, 10);
307 End post_insert;
308 --
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------------< ins >----------------------------------|
311 -- ----------------------------------------------------------------------------
312 Procedure ins
313 (
314 p_rec in out pay_pgp_shd.g_rec_type,
315 p_validate in boolean default false
316 ) is
317 --
318 l_proc varchar2(72) := g_package||'ins';
319 --
320 Begin
321 hr_utility.set_location('Entering:'||l_proc, 5);
322 --
323 -- Determine if the business process is to be validated.
324 --
325 If p_validate then
326 --
327 -- Issue the savepoint.
328 --
329 SAVEPOINT ins_pay_pgp;
330 End If;
331 --
332 -- Call the supporting insert validate operations
333 --
334 pay_pgp_bus.insert_validate(p_rec);
335 --
336 -- Call the supporting pre-insert operation
337 --
338 pre_insert(p_rec);
339 --
340 -- Insert the row
341 --
342 insert_dml(p_rec);
343 --
344 -- Call the supporting post-insert operation
345 --
346 post_insert(p_rec);
347 --
348 -- If we are validating then raise the Validate_Enabled exception
349 --
350 If p_validate then
351 Raise HR_Api.Validate_Enabled;
352 End If;
353 --
354 hr_utility.set_location(' Leaving:'||l_proc, 10);
355 Exception
356 When HR_Api.Validate_Enabled Then
357 --
358 -- As the Validate_Enabled exception has been raised
359 -- we must rollback to the savepoint
360 --
361 ROLLBACK TO ins_pay_pgp;
362 end ins;
363 --
364 -- ----------------------------------------------------------------------------
365 -- |---------------------------------< ins >----------------------------------|
366 -- ----------------------------------------------------------------------------
367 Procedure ins
368 (
369 p_people_group_id out number,
370 p_group_name out varchar2,
371 p_id_flex_num in number,
372 p_summary_flag in varchar2,
373 p_enabled_flag in varchar2,
374 p_start_date_active in date default null,
375 p_end_date_active in date default null,
376 p_segment1 in varchar2 default null,
377 p_segment2 in varchar2 default null,
378 p_segment3 in varchar2 default null,
379 p_segment4 in varchar2 default null,
380 p_segment5 in varchar2 default null,
381 p_segment6 in varchar2 default null,
382 p_segment7 in varchar2 default null,
383 p_segment8 in varchar2 default null,
384 p_segment9 in varchar2 default null,
385 p_segment10 in varchar2 default null,
386 p_segment11 in varchar2 default null,
387 p_segment12 in varchar2 default null,
388 p_segment13 in varchar2 default null,
389 p_segment14 in varchar2 default null,
390 p_segment15 in varchar2 default null,
391 p_segment16 in varchar2 default null,
392 p_segment17 in varchar2 default null,
393 p_segment18 in varchar2 default null,
394 p_segment19 in varchar2 default null,
395 p_segment20 in varchar2 default null,
396 p_segment21 in varchar2 default null,
397 p_segment22 in varchar2 default null,
398 p_segment23 in varchar2 default null,
399 p_segment24 in varchar2 default null,
400 p_segment25 in varchar2 default null,
401 p_segment26 in varchar2 default null,
402 p_segment27 in varchar2 default null,
403 p_segment28 in varchar2 default null,
404 p_segment29 in varchar2 default null,
405 p_segment30 in varchar2 default null,
406 p_validate in boolean default false
407 ) is
408 --
409 l_rec pay_pgp_shd.g_rec_type;
410 l_proc varchar2(72) := g_package||'ins';
411 --
412 Begin
413 hr_utility.set_location('Entering:'||l_proc, 5);
414 --
415 -- Call conversion function to turn arguments into the
416 -- p_rec structure.
417 --
418 l_rec :=
419 pay_pgp_shd.convert_args
420 (
421 null,
422 null,
423 p_id_flex_num,
424 p_summary_flag,
425 p_enabled_flag,
426 p_start_date_active,
427 p_end_date_active,
428 p_segment1,
429 p_segment2,
430 p_segment3,
431 p_segment4,
432 p_segment5,
433 p_segment6,
434 p_segment7,
435 p_segment8,
436 p_segment9,
437 p_segment10,
438 p_segment11,
439 p_segment12,
440 p_segment13,
441 p_segment14,
442 p_segment15,
443 p_segment16,
444 p_segment17,
445 p_segment18,
446 p_segment19,
447 p_segment20,
448 p_segment21,
449 p_segment22,
450 p_segment23,
451 p_segment24,
452 p_segment25,
453 p_segment26,
454 p_segment27,
455 p_segment28,
456 p_segment29,
457 p_segment30
458 );
459 --
460 -- Having converted the arguments into the pay_pgp_rec
461 -- plsql record structure we call the corresponding record business process.
462 --
463 ins(l_rec, p_validate);
464 --
465 -- As the primary key argument(s)
466 -- are specified as an OUT's we must set these values.
467 --
468 p_people_group_id := l_rec.people_group_id;
469 p_group_name := l_rec.group_name;
470 --
471 hr_utility.set_location(' Leaving:'||l_proc, 10);
472 End ins;
473 --
474 -- ----------------------------------------------------------------------------
475 -- |-------------------------------< ins_or_sel >-----------------------------|
476 -- ----------------------------------------------------------------------------
477 procedure ins_or_sel
478 (p_segment1 in varchar2 default null,
479 p_segment2 in varchar2 default null,
480 p_segment3 in varchar2 default null,
481 p_segment4 in varchar2 default null,
482 p_segment5 in varchar2 default null,
483 p_segment6 in varchar2 default null,
484 p_segment7 in varchar2 default null,
485 p_segment8 in varchar2 default null,
486 p_segment9 in varchar2 default null,
487 p_segment10 in varchar2 default null,
488 p_segment11 in varchar2 default null,
489 p_segment12 in varchar2 default null,
490 p_segment13 in varchar2 default null,
491 p_segment14 in varchar2 default null,
492 p_segment15 in varchar2 default null,
493 p_segment16 in varchar2 default null,
494 p_segment17 in varchar2 default null,
495 p_segment18 in varchar2 default null,
496 p_segment19 in varchar2 default null,
497 p_segment20 in varchar2 default null,
498 p_segment21 in varchar2 default null,
499 p_segment22 in varchar2 default null,
500 p_segment23 in varchar2 default null,
501 p_segment24 in varchar2 default null,
502 p_segment25 in varchar2 default null,
503 p_segment26 in varchar2 default null,
504 p_segment27 in varchar2 default null,
505 p_segment28 in varchar2 default null,
506 p_segment29 in varchar2 default null,
507 p_segment30 in varchar2 default null,
508 p_business_group_id in number,
509 p_people_group_id out number,
510 p_group_name out varchar2,
511 p_validate in boolean default false) is
512 --
513 l_people_group_id pay_people_groups.people_group_id%type;
514 l_group_name pay_people_groups.group_name%type;
515 l_id_flex_num pay_people_groups.id_flex_num%type;
516 l_proc varchar2(72) := g_package||'ins_or_sel';
517 --
518 --
519 begin
520 hr_utility.set_location('Entering:'||l_proc, 5);
521 --
522 -- determine if a people group combination exists
523 --
524 pay_pgp_shd.segment_combination_check
525 (p_segment1 => p_segment1,
526 p_segment2 => p_segment2,
527 p_segment3 => p_segment3,
528 p_segment4 => p_segment4,
529 p_segment5 => p_segment5,
530 p_segment6 => p_segment6,
531 p_segment7 => p_segment7,
532 p_segment8 => p_segment8,
533 p_segment9 => p_segment9,
534 p_segment10 => p_segment10,
535 p_segment11 => p_segment11,
536 p_segment12 => p_segment12,
537 p_segment13 => p_segment13,
538 p_segment14 => p_segment14,
539 p_segment15 => p_segment15,
540 p_segment16 => p_segment16,
541 p_segment17 => p_segment17,
542 p_segment18 => p_segment18,
543 p_segment19 => p_segment19,
544 p_segment20 => p_segment20,
545 p_segment21 => p_segment21,
546 p_segment22 => p_segment22,
547 p_segment23 => p_segment23,
548 p_segment24 => p_segment24,
549 p_segment25 => p_segment25,
550 p_segment26 => p_segment26,
551 p_segment27 => p_segment27,
552 p_segment28 => p_segment28,
553 p_segment29 => p_segment29,
554 p_segment30 => p_segment30,
555 p_business_group_id => p_business_group_id,
556 p_people_group_id => l_people_group_id,
557 p_group_name => l_group_name,
558 p_id_flex_num => l_id_flex_num);
559 --
560 -- determine the state of p_people_group_id
561 --
562 -- p_people_group_id
563 -- state meaning
564 -- ===================== =======
565 -- -1 Segment combination does not exist
566 -- null The segment combination is null
567 -- id A segment combination has been found
568 --
569 if (l_people_group_id = -1) then
570 hr_utility.set_location(l_proc, 10);
571 --
572 -- a new combination needs to be inserted
573 --
574 pay_pgp_ins.ins
575 (p_people_group_id => p_people_group_id,
576 p_group_name => p_group_name,
577 p_id_flex_num => l_id_flex_num,
578 p_summary_flag => null,
579 p_enabled_flag => null,
580 p_start_date_active => null,
581 p_end_date_active => null,
582 p_segment1 => p_segment1,
583 p_segment2 => p_segment2,
584 p_segment3 => p_segment3,
585 p_segment4 => p_segment4,
586 p_segment5 => p_segment5,
587 p_segment6 => p_segment6,
588 p_segment7 => p_segment7,
589 p_segment8 => p_segment8,
590 p_segment9 => p_segment9,
591 p_segment10 => p_segment10,
592 p_segment11 => p_segment11,
593 p_segment12 => p_segment12,
594 p_segment13 => p_segment13,
595 p_segment14 => p_segment14,
596 p_segment15 => p_segment15,
597 p_segment16 => p_segment16,
598 p_segment17 => p_segment17,
599 p_segment18 => p_segment18,
600 p_segment19 => p_segment19,
601 p_segment20 => p_segment20,
602 p_segment21 => p_segment21,
603 p_segment22 => p_segment22,
604 p_segment23 => p_segment23,
605 p_segment24 => p_segment24,
606 p_segment25 => p_segment25,
607 p_segment26 => p_segment26,
608 p_segment27 => p_segment27,
609 p_segment28 => p_segment28,
610 p_segment29 => p_segment29,
611 p_segment30 => p_segment30,
612 p_validate => p_validate);
613 elsif l_people_group_id is not null then
614 --
615 -- As the combination already exists we must ensure that the
616 -- we return the primary key
617 --
618 p_people_group_id := l_people_group_id;
619 p_group_name := l_group_name;
620 else
621 --
622 -- The combination must be null therefore we must ensure that the
623 -- we return the primary key as nulls
624 --
625 p_people_group_id := null;
626 p_group_name := null;
627 end if;
628 hr_utility.set_location(' Leaving:'||l_proc, 10);
629 end ins_or_sel;
630 --
631 end pay_pgp_ins;