[Home] [Help]
PACKAGE BODY: APPS.PER_CAG_INS
Source
1 Package Body per_cag_ins as
2 /* $Header: pecagrhi.pkb 120.1 2006/10/18 08:42:10 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cag_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
24 -- 3) To insert the row into the schema.
21 -- perform dml).
22 -- (Note: Philippe 4/20/99 Removed the need for setting g_api_dml as this is a new
23 -- table and therfore there is no ovn trigger to use it).
25 -- 4) To trap any constraint violations that may have occurred.
26 -- 5) To raise any other errors.
27 --
28 -- Prerequisites:
29 -- This is an internal private procedure which must be called from the ins
30 -- procedure and must have all mandatory attributes set (except the
31 -- object_version_number which is initialised within this procedure).
32 --
33 -- In Parameters:
34 -- A Pl/Sql record structre.
35 --
36 -- Post Success:
37 -- The specified row will be inserted into the schema.
38 --
39 -- Post Failure:
40 -- On the insert dml failure it is important to note that we always reset the
41 -- g_api_dml status to false.
42 -- If a check, unique or parent integrity constraint violation is raised the
43 -- constraint_error procedure will be called.
44 -- If any other error is reported, the error will be raised after the
45 -- g_api_dml status is reset.
46 --
47 -- Developer Implementation Notes:
48 -- None.
49 --
50 -- Access Status:
51 -- Internal Row Handler Use Only.
52 --
53 -- {End Of Comments}
54 -- ----------------------------------------------------------------------------
55 Procedure insert_dml(p_rec in out nocopy per_cag_shd.g_rec_type) 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 --
64 -- Insert the row into: per_collective_agreements
65 --
66 insert into per_collective_agreements
67 ( collective_agreement_id,
68 business_group_id,
69 object_version_number,
70 name,
71 pl_id,
72 status,
73 cag_number,
74 description,
75 start_date,
76 end_date,
77 employer_organization_id,
78 employer_signatory,
79 bargaining_organization_id,
80 bargaining_unit_signatory,
81 jurisdiction,
82 authorizing_body,
83 authorized_date,
84 cag_information_category,
85 cag_information1,
86 cag_information2,
87 cag_information3,
88 cag_information4,
89 cag_information5,
90 cag_information6,
91 cag_information7,
92 cag_information8,
93 cag_information9,
94 cag_information10,
95 cag_information11,
96 cag_information12,
97 cag_information13,
98 cag_information14,
99 cag_information15,
100 cag_information16,
101 cag_information17,
102 cag_information18,
103 cag_information19,
104 cag_information20,
105 attribute_category,
106 attribute1,
107 attribute2,
108 attribute3,
109 attribute4,
110 attribute5,
111 attribute6,
112 attribute7,
113 attribute8,
114 attribute9,
115 attribute10,
116 attribute11,
117 attribute12,
118 attribute13,
119 attribute14,
120 attribute15,
121 attribute16,
122 attribute17,
123 attribute18,
124 attribute19,
125 attribute20
126 )
127 Values
128 ( p_rec.collective_agreement_id,
129 p_rec.business_group_id,
130 p_rec.object_version_number,
131 p_rec.name,
132 p_rec.pl_id,
133 p_rec.status,
134 p_rec.cag_number,
135 p_rec.description,
136 p_rec.start_date,
137 p_rec.end_date,
138 p_rec.employer_organization_id,
139 p_rec.employer_signatory,
140 p_rec.bargaining_organization_id,
141 p_rec.bargaining_unit_signatory,
142 p_rec.jurisdiction,
143 p_rec.authorizing_body,
144 p_rec.authorized_date,
145 p_rec.cag_information_category,
146 p_rec.cag_information1,
147 p_rec.cag_information2,
148 p_rec.cag_information3,
149 p_rec.cag_information4,
150 p_rec.cag_information5,
151 p_rec.cag_information6,
152 p_rec.cag_information7,
153 p_rec.cag_information8,
154 p_rec.cag_information9,
155 p_rec.cag_information10,
156 p_rec.cag_information11,
157 p_rec.cag_information12,
158 p_rec.cag_information13,
159 p_rec.cag_information14,
160 p_rec.cag_information15,
161 p_rec.cag_information16,
162 p_rec.cag_information17,
163 p_rec.cag_information18,
164 p_rec.cag_information19,
165 p_rec.cag_information20,
166 p_rec.attribute_category,
167 p_rec.attribute1,
168 p_rec.attribute2,
169 p_rec.attribute3,
170 p_rec.attribute4,
171 p_rec.attribute5,
172 p_rec.attribute6,
173 p_rec.attribute7,
174 p_rec.attribute8,
175 p_rec.attribute9,
176 p_rec.attribute10,
177 p_rec.attribute11,
178 p_rec.attribute12,
179 p_rec.attribute13,
180 p_rec.attribute14,
181 p_rec.attribute15,
182 p_rec.attribute16,
183 p_rec.attribute17,
184 p_rec.attribute18,
185 p_rec.attribute19,
186 p_rec.attribute20
187 );
188 --
189 --
190 hr_utility.set_location(' Leaving:'||l_proc, 10);
191 Exception
192 When hr_api.check_integrity_violated Then
193 -- A check constraint has been violated
194 per_cag_shd.constraint_error
195 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
196 When hr_api.parent_integrity_violated Then
197 -- Parent integrity has been violated
198 per_cag_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 per_cag_shd.constraint_error
203 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
204 When Others Then
205 Raise;
206 End insert_dml;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |------------------------------< pre_insert >------------------------------|
210 -- ----------------------------------------------------------------------------
211 -- {Start Of Comments}
212 --
213 -- Description:
214 -- This private procedure contains any processing which is required before
215 -- the insert dml. Presently, if the entity has a corresponding primary
216 -- key which is maintained by an associating sequence, the primary key for
217 -- the entity will be populated with the next sequence value in
218 -- preparation for the insert dml.
219 --
220 -- Prerequisites:
224 -- A Pl/Sql record structre.
221 -- This is an internal procedure which is called from the ins procedure.
222 --
223 -- In Parameters:
225 --
226 -- Post Success:
227 -- Processing continues.
228 --
229 -- Post Failure:
230 -- If an error has occurred, an error message and exception will be raised
231 -- but not handled.
232 --
233 -- Developer Implementation Notes:
234 -- Any pre-processing required before the insert dml is issued should be
235 -- coded within this procedure. As stated above, a good example is the
236 -- generation of a primary key number via a corresponding sequence.
237 -- It is important to note that any 3rd party maintenance should be reviewed
238 -- before placing in this procedure.
239 --
240 -- Access Status:
241 -- Internal Row Handler Use Only.
242 --
243 -- {End Of Comments}
244 -- ----------------------------------------------------------------------------
245 Procedure pre_insert(p_rec in out nocopy per_cag_shd.g_rec_type) is
246 --
247 l_proc varchar2(72) := g_package||'pre_insert';
248 --
249 Cursor C_Sel1 is select per_collective_agreements_s.nextval from sys.dual;
250 --
251 Begin
252 hr_utility.set_location('Entering:'||l_proc, 5);
253 --
254 --
255 -- Select the next sequence number
256 --
257 Open C_Sel1;
258 Fetch C_Sel1 Into p_rec.collective_agreement_id;
259 Close C_Sel1;
260 --
261 hr_utility.set_location(' Leaving:'||l_proc, 10);
262 End pre_insert;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |-----------------------------< post_insert >------------------------------|
266 -- ----------------------------------------------------------------------------
267 -- {Start Of Comments}
268 --
269 -- Description:
270 -- This private procedure contains any processing which is required after the
271 -- insert dml.
272 --
273 -- Prerequisites:
274 -- This is an internal procedure which is called from the ins procedure.
275 --
276 -- In Parameters:
277 -- A Pl/Sql record structre.
278 --
279 -- Post Success:
280 -- Processing continues.
281 --
282 -- Post Failure:
283 -- If an error has occurred, an error message and exception will be raised
284 -- but not handled.
285 --
286 -- Developer Implementation Notes:
287 -- Any post-processing required after the insert dml is issued should be
288 -- coded within this procedure. It is important to note that any 3rd party
289 -- maintenance should be reviewed before placing in this procedure.
290 --
291 -- Access Status:
292 -- Internal Row Handler Use Only.
293 --
294 -- {End Of Comments}
295 -- ----------------------------------------------------------------------------
296 Procedure post_insert(p_rec in per_cag_shd.g_rec_type) is
297 --
298 l_proc varchar2(72) := g_package||'post_insert';
299 --
300 Begin
301 hr_utility.set_location('Entering:'||l_proc, 5);
302 --
303 --
304 --
305 -- Start of API User Hook for post_insert.
306 --
307 begin
308 --
309 per_cag_rki.after_insert
310 (p_collective_agreement_id =>p_rec.collective_agreement_id,
311 p_business_group_id =>p_rec.business_group_id,
312 p_object_version_number =>p_rec.object_version_number,
313 p_name =>p_rec.name,
314 p_pl_id =>p_rec.pl_id,
315 p_status =>p_rec.status,
316 p_cag_number =>p_rec.cag_number,
317 p_description =>p_rec.description,
318 p_start_date =>p_rec.start_date,
319 p_end_date =>p_rec.end_date,
320 p_employer_organization_id =>p_rec.employer_organization_id,
321 p_employer_signatory =>p_rec.employer_signatory,
322 p_bargaining_organization_id =>p_rec.bargaining_organization_id,
323 p_bargaining_unit_signatory =>p_rec.bargaining_unit_signatory,
324 p_jurisdiction =>p_rec.jurisdiction,
325 p_authorizing_body =>p_rec.authorizing_body,
326 p_authorized_date =>p_rec.authorized_date,
327 p_cag_information_category =>p_rec.cag_information_category,
328 p_cag_information1 =>p_rec.cag_information1,
329 p_cag_information2 =>p_rec.cag_information2,
330 p_cag_information3 =>p_rec.cag_information3,
331 p_cag_information4 =>p_rec.cag_information4,
332 p_cag_information5 =>p_rec.cag_information5,
333 p_cag_information6 =>p_rec.cag_information6,
334 p_cag_information7 =>p_rec.cag_information7,
335 p_cag_information8 =>p_rec.cag_information8,
336 p_cag_information9 =>p_rec.cag_information9,
337 p_cag_information10 =>p_rec.cag_information10,
338 p_cag_information11 =>p_rec.cag_information11,
339 p_cag_information12 =>p_rec.cag_information12,
340 p_cag_information13 =>p_rec.cag_information13,
341 p_cag_information14 =>p_rec.cag_information14,
342 p_cag_information15 =>p_rec.cag_information15,
343 p_cag_information16 =>p_rec.cag_information16,
344 p_cag_information17 =>p_rec.cag_information17,
345 p_cag_information18 =>p_rec.cag_information18,
346 p_cag_information19 =>p_rec.cag_information19,
347 p_cag_information20 =>p_rec.cag_information20,
348 p_attribute_category =>p_rec.attribute_category,
349 p_attribute1 =>p_rec.attribute1,
350 p_attribute2 =>p_rec.attribute2,
351 p_attribute3 =>p_rec.attribute3,
352 p_attribute4 =>p_rec.attribute4,
353 p_attribute5 =>p_rec.attribute5,
354 p_attribute6 =>p_rec.attribute6,
355 p_attribute7 =>p_rec.attribute7,
356 p_attribute8 =>p_rec.attribute8,
357 p_attribute9 =>p_rec.attribute9,
358 p_attribute10 =>p_rec.attribute10,
359 p_attribute11 =>p_rec.attribute11,
360 p_attribute12 =>p_rec.attribute12,
361 p_attribute13 =>p_rec.attribute13,
362 p_attribute14 =>p_rec.attribute14,
363 p_attribute15 =>p_rec.attribute15,
364 p_attribute16 =>p_rec.attribute16,
365 p_attribute17 =>p_rec.attribute17,
366 p_attribute18 =>p_rec.attribute18,
367 p_attribute19 =>p_rec.attribute19,
368 p_attribute20 =>p_rec.attribute20
369 );
370 --
371 exception
372 --
373 when hr_api.cannot_find_prog_unit then
374 --
375 hr_api.cannot_find_prog_unit_error
376 (p_module_name => 'per_collective_agreements'
377 ,p_hook_type => 'AI');
378 --
379 end;
380 --
381 -- End of API User Hook for post_insert.
382 --
383 --
384 hr_utility.set_location(' Leaving:'||l_proc, 10);
385 End post_insert;
386 --
387 -- ----------------------------------------------------------------------------
388 -- |---------------------------------< ins >----------------------------------|
389 -- ----------------------------------------------------------------------------
390 Procedure ins
391 (
392 p_rec in out nocopy per_cag_shd.g_rec_type
393 ) is
394 --
395 l_proc varchar2(72) := g_package||'ins';
396 --
397 Begin
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 --
400 -- Call the supporting insert validate operations
401 --
402 per_cag_bus.insert_validate(p_rec);
403 --
404 -- Call the supporting pre-insert operation
405 --
406 pre_insert(p_rec);
407 --
408 -- Insert the row
409 --
410 insert_dml(p_rec);
411 --
412 -- Call the supporting post-insert operation
413 --
414 post_insert(p_rec);
415 end ins;
416 --
417 -- ----------------------------------------------------------------------------
418 -- |---------------------------------< ins >----------------------------------|
419 -- ----------------------------------------------------------------------------
420 Procedure ins
421 (
422 p_collective_agreement_id out nocopy number,
423 p_business_group_id in number,
424 p_object_version_number out nocopy number,
425 p_name in varchar2,
426 p_pl_id in number,
427 p_status in varchar2 default null,
428 p_cag_number in number default null,
429 p_description in varchar2 default null,
430 p_start_date in date default null,
431 p_end_date in date default null,
432 p_employer_organization_id in number default null,
433 p_employer_signatory in varchar2 default null,
434 p_bargaining_organization_id in number default null,
435 p_bargaining_unit_signatory in varchar2 default null,
436 p_jurisdiction in varchar2 default null,
437 p_authorizing_body in varchar2 default null,
438 p_authorized_date in date default null,
439 p_cag_information_category in varchar2 default null,
440 p_cag_information1 in varchar2 default null,
441 p_cag_information2 in varchar2 default null,
442 p_cag_information3 in varchar2 default null,
443 p_cag_information4 in varchar2 default null,
444 p_cag_information5 in varchar2 default null,
445 p_cag_information6 in varchar2 default null,
446 p_cag_information7 in varchar2 default null,
447 p_cag_information8 in varchar2 default null,
448 p_cag_information9 in varchar2 default null,
449 p_cag_information10 in varchar2 default null,
450 p_cag_information11 in varchar2 default null,
451 p_cag_information12 in varchar2 default null,
452 p_cag_information13 in varchar2 default null,
453 p_cag_information14 in varchar2 default null,
454 p_cag_information15 in varchar2 default null,
455 p_cag_information16 in varchar2 default null,
456 p_cag_information17 in varchar2 default null,
457 p_cag_information18 in varchar2 default null,
458 p_cag_information19 in varchar2 default null,
459 p_cag_information20 in varchar2 default null,
460 p_attribute_category in varchar2 default null,
461 p_attribute1 in varchar2 default null,
462 p_attribute2 in varchar2 default null,
463 p_attribute3 in varchar2 default null,
464 p_attribute4 in varchar2 default null,
465 p_attribute5 in varchar2 default null,
466 p_attribute6 in varchar2 default null,
467 p_attribute7 in varchar2 default null,
468 p_attribute8 in varchar2 default null,
469 p_attribute9 in varchar2 default null,
470 p_attribute10 in varchar2 default null,
471 p_attribute11 in varchar2 default null,
472 p_attribute12 in varchar2 default null,
473 p_attribute13 in varchar2 default null,
474 p_attribute14 in varchar2 default null,
475 p_attribute15 in varchar2 default null,
476 p_attribute16 in varchar2 default null,
477 p_attribute17 in varchar2 default null,
478 p_attribute18 in varchar2 default null,
479 p_attribute19 in varchar2 default null,
480 p_attribute20 in varchar2 default null
481 ) is
482 --
483 l_rec per_cag_shd.g_rec_type;
484 l_proc varchar2(72) := g_package||'ins';
485 --
486 Begin
487 hr_utility.set_location('Entering:'||l_proc, 5);
488 --
489 -- Call conversion function to turn arguments into the
490 -- p_rec structure.
491 --
492 l_rec :=
493 per_cag_shd.convert_args
494 (
495 null,
496 p_business_group_id,
497 null,
498 p_name,
499 p_pl_id,
500 p_status,
501 p_cag_number,
502 p_description,
503 p_start_date,
504 p_end_date,
505 p_employer_organization_id,
506 p_employer_signatory,
507 p_bargaining_organization_id,
508 p_bargaining_unit_signatory,
509 p_jurisdiction,
510 p_authorizing_body,
511 p_authorized_date,
512 p_cag_information_category,
513 p_cag_information1,
514 p_cag_information2,
515 p_cag_information3,
516 p_cag_information4,
517 p_cag_information5,
518 p_cag_information6,
519 p_cag_information7,
520 p_cag_information8,
521 p_cag_information9,
522 p_cag_information10,
523 p_cag_information11,
524 p_cag_information12,
525 p_cag_information13,
526 p_cag_information14,
527 p_cag_information15,
528 p_cag_information16,
529 p_cag_information17,
530 p_cag_information18,
531 p_cag_information19,
532 p_cag_information20,
533 p_attribute_category,
534 p_attribute1,
535 p_attribute2,
536 p_attribute3,
537 p_attribute4,
538 p_attribute5,
539 p_attribute6,
540 p_attribute7,
541 p_attribute8,
542 p_attribute9,
543 p_attribute10,
544 p_attribute11,
545 p_attribute12,
546 p_attribute13,
547 p_attribute14,
548 p_attribute15,
549 p_attribute16,
550 p_attribute17,
551 p_attribute18,
552 p_attribute19,
553 p_attribute20
554 );
555 --
556 -- Having converted the arguments into the per_cag_rec
557 -- plsql record structure we call the corresponding record business process.
558 --
559 ins(l_rec);
560 --
561 -- As the primary key argument(s)
562 -- are specified as an OUT's we must set these values.
563 --
564 p_collective_agreement_id := l_rec.collective_agreement_id;
565 p_object_version_number := l_rec.object_version_number;
566 --
567 hr_utility.set_location(' Leaving:'||l_proc, 10);
568 End ins;
569 --
570 end per_cag_ins;