[Home] [Help]
PACKAGE BODY: APPS.PQP_SHP_INS
Source
1 Package Body pqp_shp_ins as
2 /* $Header: pqshprhi.pkb 115.8 2003/02/17 22:14:48 tmehra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_shp_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 processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory attributes set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- None.
47 --
48 -- Access Status:
49 -- Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml
54 (p_rec in out nocopy pqp_shp_shd.g_rec_type
55 ) is
56 --
57 l_proc varchar2(72) := g_package||'insert_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 p_rec.object_version_number := 1; -- Initialise the object version
62 --
63 pqp_shp_shd.g_api_dml := true; -- Set the api dml status
64 --
65 -- Insert the row into: pqp_service_history_periods
66 --
67 insert into pqp_service_history_periods
68 (service_history_period_id
69 ,business_group_id
70 ,assignment_id
71 ,start_date
72 ,end_date
73 ,employer_name
74 ,employer_address
75 ,employer_type
76 ,employer_subtype
77 ,period_years
78 ,period_days
79 ,description
80 ,continuous_service
81 ,all_assignments
82 ,object_version_number
83 ,shp_attribute_category
84 ,shp_attribute1
85 ,shp_attribute2
86 ,shp_attribute3
87 ,shp_attribute4
88 ,shp_attribute5
89 ,shp_attribute6
90 ,shp_attribute7
91 ,shp_attribute8
92 ,shp_attribute9
93 ,shp_attribute10
94 ,shp_attribute11
95 ,shp_attribute12
96 ,shp_attribute13
97 ,shp_attribute14
98 ,shp_attribute15
99 ,shp_attribute16
100 ,shp_attribute17
101 ,shp_attribute18
102 ,shp_attribute19
103 ,shp_attribute20
104 ,shp_information_category
105 ,shp_information1
106 ,shp_information2
107 ,shp_information3
108 ,shp_information4
109 ,shp_information5
110 ,shp_information6
111 ,shp_information7
112 ,shp_information8
113 ,shp_information9
114 ,shp_information10
115 ,shp_information11
116 ,shp_information12
117 ,shp_information13
121 ,shp_information17
118 ,shp_information14
119 ,shp_information15
120 ,shp_information16
122 ,shp_information18
123 ,shp_information19
124 ,shp_information20
125 )
126 Values
127 (p_rec.service_history_period_id
128 ,p_rec.business_group_id
129 ,p_rec.assignment_id
130 ,p_rec.start_date
131 ,p_rec.end_date
132 ,p_rec.employer_name
133 ,p_rec.employer_address
134 ,p_rec.employer_type
135 ,p_rec.employer_subtype
136 ,p_rec.period_years
137 ,p_rec.period_days
138 ,p_rec.description
139 ,p_rec.continuous_service
140 ,p_rec.all_assignments
141 ,p_rec.object_version_number
142 ,p_rec.shp_attribute_category
143 ,p_rec.shp_attribute1
144 ,p_rec.shp_attribute2
145 ,p_rec.shp_attribute3
146 ,p_rec.shp_attribute4
147 ,p_rec.shp_attribute5
148 ,p_rec.shp_attribute6
149 ,p_rec.shp_attribute7
150 ,p_rec.shp_attribute8
151 ,p_rec.shp_attribute9
152 ,p_rec.shp_attribute10
153 ,p_rec.shp_attribute11
154 ,p_rec.shp_attribute12
155 ,p_rec.shp_attribute13
156 ,p_rec.shp_attribute14
157 ,p_rec.shp_attribute15
158 ,p_rec.shp_attribute16
159 ,p_rec.shp_attribute17
160 ,p_rec.shp_attribute18
161 ,p_rec.shp_attribute19
162 ,p_rec.shp_attribute20
163 ,p_rec.shp_information_category
164 ,p_rec.shp_information1
165 ,p_rec.shp_information2
166 ,p_rec.shp_information3
167 ,p_rec.shp_information4
168 ,p_rec.shp_information5
169 ,p_rec.shp_information6
170 ,p_rec.shp_information7
171 ,p_rec.shp_information8
172 ,p_rec.shp_information9
173 ,p_rec.shp_information10
174 ,p_rec.shp_information11
175 ,p_rec.shp_information12
176 ,p_rec.shp_information13
177 ,p_rec.shp_information14
178 ,p_rec.shp_information15
179 ,p_rec.shp_information16
180 ,p_rec.shp_information17
181 ,p_rec.shp_information18
182 ,p_rec.shp_information19
183 ,p_rec.shp_information20
184 );
185 --
186 pqp_shp_shd.g_api_dml := false; -- Unset the api dml status
187 --
188 hr_utility.set_location(' Leaving:'||l_proc, 10);
189 Exception
190 When hr_api.check_integrity_violated Then
191 -- A check constraint has been violated
192 pqp_shp_shd.g_api_dml := false; -- Unset the api dml status
193 pqp_shp_shd.constraint_error
194 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
195 When hr_api.parent_integrity_violated Then
196 -- Parent integrity has been violated
197 pqp_shp_shd.g_api_dml := false; -- Unset the api dml status
198 pqp_shp_shd.constraint_error
199 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
200 When hr_api.unique_integrity_violated Then
201 -- Unique integrity has been violated
202 pqp_shp_shd.g_api_dml := false; -- Unset the api dml status
203 pqp_shp_shd.constraint_error
204 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
205 When Others Then
206 pqp_shp_shd.g_api_dml := false; -- Unset the api dml status
207 Raise;
208 End insert_dml;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |------------------------------< pre_insert >------------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This private procedure contains any processing which is required before
217 -- the insert dml. Presently, if the entity has a corresponding primary
218 -- key which is maintained by an associating sequence, the primary key for
219 -- the entity will be populated with the next sequence value in
220 -- preparation for the insert dml.
221 --
222 -- Prerequisites:
223 -- This is an internal procedure which is called from the ins procedure.
224 --
225 -- In Parameters:
226 -- A Pl/Sql record structre.
227 --
228 -- Post Success:
229 -- Processing continues.
230 --
231 -- Post Failure:
232 -- If an error has occurred, an error message and exception will be raised
233 -- but not handled.
234 --
235 -- Developer Implementation Notes:
236 -- Any pre-processing required before the insert dml is issued should be
237 -- coded within this procedure. As stated above, a good example is the
238 -- generation of a primary key number via a corresponding sequence.
239 -- It is important to note that any 3rd party maintenance should be reviewed
240 -- before placing in this procedure.
241 --
242 -- Access Status:
243 -- Internal Row Handler Use Only.
244 --
245 -- {End Of Comments}
246 -- ----------------------------------------------------------------------------
247 Procedure pre_insert
248 (p_rec in out nocopy pqp_shp_shd.g_rec_type
249 ) is
250 --
251 l_proc varchar2(72) := g_package||'pre_insert';
252 --
253 Cursor C_Sel1 is select pqp_service_history_periods_s.nextval from sys.dual;
254 --
255 Begin
256 hr_utility.set_location('Entering:'||l_proc, 5);
257 --
258 --
259 -- Select the next sequence number
260 --
261 Open C_Sel1;
262 Fetch C_Sel1 Into p_rec.service_history_period_id;
263 Close C_Sel1;
264 --
265 hr_utility.set_location(' Leaving:'||l_proc, 10);
266 End pre_insert;
267 --
268 -- ----------------------------------------------------------------------------
272 --
269 -- |-----------------------------< post_insert >------------------------------|
270 -- ----------------------------------------------------------------------------
271 -- {Start Of Comments}
273 -- Description:
274 -- This private procedure contains any processing which is required after the
275 -- 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 (
302 p_rec in pqp_shp_shd.g_rec_type
303 ) is
304 --
305 l_proc varchar2(72) := g_package||'post_insert';
306 --
307 Begin
308 hr_utility.set_location('Entering:'||l_proc, 5);
309 begin
310 --
311 pqp_shp_rki.after_insert
312 (
313 p_service_history_period_id
314 => p_rec.service_history_period_id
315 ,p_business_group_id
316 => p_rec.business_group_id
317 ,p_assignment_id
318 => p_rec.assignment_id
319 ,p_start_date
320 => p_rec.start_date
321 ,p_end_date
322 => p_rec.end_date
323 ,p_employer_name
324 => p_rec.employer_name
325 ,p_employer_address
326 => p_rec.employer_address
327 ,p_employer_type
328 => p_rec.employer_type
329 ,p_employer_subtype
330 => p_rec.employer_subtype
331 ,p_period_years
332 => p_rec.period_years
333 ,p_period_days
334 => p_rec.period_days
335 ,p_description
336 => p_rec.description
337 ,p_continuous_service
338 => p_rec.continuous_service
339 ,p_all_assignments
340 => p_rec.all_assignments
341 ,p_object_version_number
342 => p_rec.object_version_number
343 ,p_shp_attribute_category
344 => p_rec.shp_attribute_category
345 ,p_shp_attribute1
346 => p_rec.shp_attribute1
347 ,p_shp_attribute2
348 => p_rec.shp_attribute2
349 ,p_shp_attribute3
350 => p_rec.shp_attribute3
351 ,p_shp_attribute4
352 => p_rec.shp_attribute4
353 ,p_shp_attribute5
354 => p_rec.shp_attribute5
355 ,p_shp_attribute6
356 => p_rec.shp_attribute6
357 ,p_shp_attribute7
358 => p_rec.shp_attribute7
359 ,p_shp_attribute8
360 => p_rec.shp_attribute8
361 ,p_shp_attribute9
362 => p_rec.shp_attribute9
363 ,p_shp_attribute10
364 => p_rec.shp_attribute10
365 ,p_shp_attribute11
366 => p_rec.shp_attribute11
367 ,p_shp_attribute12
368 => p_rec.shp_attribute12
369 ,p_shp_attribute13
370 => p_rec.shp_attribute13
371 ,p_shp_attribute14
372 => p_rec.shp_attribute14
373 ,p_shp_attribute15
374 => p_rec.shp_attribute15
375 ,p_shp_attribute16
376 => p_rec.shp_attribute16
377 ,p_shp_attribute17
378 => p_rec.shp_attribute17
379 ,p_shp_attribute18
380 => p_rec.shp_attribute18
381 ,p_shp_attribute19
382 => p_rec.shp_attribute19
383 ,p_shp_attribute20
384 => p_rec.shp_attribute20
385 ,p_shp_information_category
386 => p_rec.shp_information_category
387 ,p_shp_information1
388 => p_rec.shp_information1
389 ,p_shp_information2
390 => p_rec.shp_information2
391 ,p_shp_information3
392 => p_rec.shp_information3
393 ,p_shp_information4
394 => p_rec.shp_information4
395 ,p_shp_information5
396 => p_rec.shp_information5
397 ,p_shp_information6
398 => p_rec.shp_information6
399 ,p_shp_information7
400 => p_rec.shp_information7
401 ,p_shp_information8
402 => p_rec.shp_information8
403 ,p_shp_information9
404 => p_rec.shp_information9
405 ,p_shp_information10
406 => p_rec.shp_information10
407 ,p_shp_information11
408 => p_rec.shp_information11
409 ,p_shp_information12
410 => p_rec.shp_information12
411 ,p_shp_information13
412 => p_rec.shp_information13
413 ,p_shp_information14
414 => p_rec.shp_information14
415 ,p_shp_information15
416 => p_rec.shp_information15
417 ,p_shp_information16
418 => p_rec.shp_information16
419 ,p_shp_information17
420 => p_rec.shp_information17
421 ,p_shp_information18
422 => p_rec.shp_information18
423 ,p_shp_information19
424 => p_rec.shp_information19
425 ,p_shp_information20
426 => p_rec.shp_information20
427 );
428 --
429 exception
430 --
431 when hr_api.cannot_find_prog_unit then
432 --
436 --
433 hr_api.cannot_find_prog_unit_error
434 (p_module_name => 'PQP_SERVICE_HISTORY_PERIODS'
435 ,p_hook_type => 'AI');
437 end;
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 10);
440 End post_insert;
441 --
442 -- ----------------------------------------------------------------------------
443 -- |---------------------------------< ins >----------------------------------|
444 -- ----------------------------------------------------------------------------
445 Procedure ins
446 (
447 p_rec in out nocopy pqp_shp_shd.g_rec_type
448 ) is
449 --
450 l_proc varchar2(72) := g_package||'ins';
451 --
452 Begin
453 hr_utility.set_location('Entering:'||l_proc, 5);
454 --
455 -- Call the supporting insert validate operations
456 --
457 pqp_shp_bus.insert_validate
458 (
459 p_rec
460 );
461 --
462 -- Call the supporting pre-insert operation
463 --
464 pqp_shp_ins.pre_insert(p_rec);
465 --
466 -- Insert the row
467 --
468 pqp_shp_ins.insert_dml(p_rec);
469 --
470 -- Call the supporting post-insert operation
471 --
472 pqp_shp_ins.post_insert
473 (
474 p_rec
475 );
476 --
477 hr_utility.set_location('Leaving:'||l_proc, 20);
478 end ins;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |---------------------------------< ins >----------------------------------|
482 -- ----------------------------------------------------------------------------
483 Procedure ins
484 (p_business_group_id in number
485 ,p_assignment_id in number
486 ,p_start_date in date default null
487 ,p_end_date in date default null
488 ,p_employer_name in varchar2 default null
489 ,p_employer_address in varchar2 default null
490 ,p_employer_type in varchar2 default null
491 ,p_employer_subtype in varchar2 default null
492 ,p_period_years in number default null
493 ,p_period_days in number default null
494 ,p_description in varchar2 default null
495 ,p_continuous_service in varchar2 default null
496 ,p_all_assignments in varchar2 default null
497 ,p_shp_attribute_category in varchar2 default null
498 ,p_shp_attribute1 in varchar2 default null
499 ,p_shp_attribute2 in varchar2 default null
500 ,p_shp_attribute3 in varchar2 default null
501 ,p_shp_attribute4 in varchar2 default null
502 ,p_shp_attribute5 in varchar2 default null
503 ,p_shp_attribute6 in varchar2 default null
504 ,p_shp_attribute7 in varchar2 default null
505 ,p_shp_attribute8 in varchar2 default null
506 ,p_shp_attribute9 in varchar2 default null
507 ,p_shp_attribute10 in varchar2 default null
508 ,p_shp_attribute11 in varchar2 default null
509 ,p_shp_attribute12 in varchar2 default null
510 ,p_shp_attribute13 in varchar2 default null
511 ,p_shp_attribute14 in varchar2 default null
512 ,p_shp_attribute15 in varchar2 default null
513 ,p_shp_attribute16 in varchar2 default null
514 ,p_shp_attribute17 in varchar2 default null
515 ,p_shp_attribute18 in varchar2 default null
516 ,p_shp_attribute19 in varchar2 default null
517 ,p_shp_attribute20 in varchar2 default null
518 ,p_shp_information_category in varchar2 default null
519 ,p_shp_information1 in varchar2 default null
520 ,p_shp_information2 in varchar2 default null
521 ,p_shp_information3 in varchar2 default null
522 ,p_shp_information4 in varchar2 default null
523 ,p_shp_information5 in varchar2 default null
524 ,p_shp_information6 in varchar2 default null
525 ,p_shp_information7 in varchar2 default null
526 ,p_shp_information8 in varchar2 default null
527 ,p_shp_information9 in varchar2 default null
528 ,p_shp_information10 in varchar2 default null
529 ,p_shp_information11 in varchar2 default null
530 ,p_shp_information12 in varchar2 default null
531 ,p_shp_information13 in varchar2 default null
532 ,p_shp_information14 in varchar2 default null
533 ,p_shp_information15 in varchar2 default null
534 ,p_shp_information16 in varchar2 default null
535 ,p_shp_information17 in varchar2 default null
536 ,p_shp_information18 in varchar2 default null
537 ,p_shp_information19 in varchar2 default null
538 ,p_shp_information20 in varchar2 default null
539 ,p_service_history_period_id out nocopy number
540 ,p_object_version_number out nocopy number
541 ) is
542 --
543 l_rec pqp_shp_shd.g_rec_type;
544 l_proc varchar2(72) := g_package||'ins';
545 --
546 Begin
547 hr_utility.set_location('Entering:'||l_proc, 5);
548 --
549 -- Call conversion function to turn arguments into the
550 -- p_rec structure.
551 --
552 l_rec :=
553 pqp_shp_shd.convert_args
554 (null
555 ,p_business_group_id
556 ,p_assignment_id
557 ,p_start_date
558 ,p_end_date
559 ,p_employer_name
560 ,p_employer_address
561 ,p_employer_type
562 ,p_employer_subtype
563 ,p_period_years
564 ,p_period_days
565 ,p_description
566 ,p_continuous_service
567 ,p_all_assignments
568 ,null
569 ,p_shp_attribute_category
570 ,p_shp_attribute1
571 ,p_shp_attribute2
572 ,p_shp_attribute3
573 ,p_shp_attribute4
574 ,p_shp_attribute5
575 ,p_shp_attribute6
576 ,p_shp_attribute7
577 ,p_shp_attribute8
578 ,p_shp_attribute9
579 ,p_shp_attribute10
580 ,p_shp_attribute11
581 ,p_shp_attribute12
582 ,p_shp_attribute13
583 ,p_shp_attribute14
584 ,p_shp_attribute15
585 ,p_shp_attribute16
586 ,p_shp_attribute17
587 ,p_shp_attribute18
588 ,p_shp_attribute19
589 ,p_shp_attribute20
590 ,p_shp_information_category
591 ,p_shp_information1
592 ,p_shp_information2
593 ,p_shp_information3
594 ,p_shp_information4
595 ,p_shp_information5
596 ,p_shp_information6
597 ,p_shp_information7
598 ,p_shp_information8
599 ,p_shp_information9
600 ,p_shp_information10
601 ,p_shp_information11
602 ,p_shp_information12
603 ,p_shp_information13
604 ,p_shp_information14
605 ,p_shp_information15
606 ,p_shp_information16
607 ,p_shp_information17
608 ,p_shp_information18
609 ,p_shp_information19
610 ,p_shp_information20
611 );
612 --
613 -- Having converted the arguments into the pqp_shp_rec
614 -- plsql record structure we call the corresponding record business process.
615 --
616 pqp_shp_ins.ins
617 (
618 l_rec
619 );
620 --
621 -- As the primary key argument(s)
622 -- are specified as an OUT's we must set these values.
623 --
624 p_service_history_period_id := l_rec.service_history_period_id;
625 p_object_version_number := l_rec.object_version_number;
626 --
627 hr_utility.set_location(' Leaving:'||l_proc, 10);
628 End ins;
629 --
630 end pqp_shp_ins;