[Home] [Help]
PACKAGE BODY: APPS.PER_VAC_INS
Source
1 Package Body per_vac_ins as
2 /* $Header: pevacrhi.pkb 120.0 2005/05/31 22:51:12 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_vac_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_vacancy_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_vacancy_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 per_vac_ins.g_vacancy_id_i := p_vacancy_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 per_vac_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 per_vac_shd.g_api_dml := true; -- Set the api dml status
86 --
87 -- Insert the row into: per_all_vacancies
88 --
89 insert into per_all_vacancies
90 (vacancy_id
91 ,business_group_id
92 ,position_id
93 ,job_id
94 ,grade_id
95 ,organization_id
96 ,requisition_id
97 ,people_group_id
98 ,location_id
99 ,recruiter_id
100 ,date_from
101 ,name
102 ,comments
103 ,date_to
104 ,description
105 ,number_of_openings
106 ,status
107 ,request_id
108 ,program_application_id
109 ,program_id
110 ,program_update_date
111 ,attribute_category
112 ,attribute1
113 ,attribute2
114 ,attribute3
115 ,attribute4
116 ,attribute5
117 ,attribute6
118 ,attribute7
119 ,attribute8
120 ,attribute9
121 ,attribute10
122 ,attribute11
123 ,attribute12
124 ,attribute13
125 ,attribute14
126 ,attribute15
127 ,attribute16
128 ,attribute17
129 ,attribute18
130 ,attribute19
131 ,attribute20
132 ,vacancy_category
133 ,budget_measurement_type
134 ,budget_measurement_value
135 ,manager_id
136 ,security_method
137 ,primary_posting_id
138 ,assessment_id
139 ,object_version_number
140 )
141 Values
142 (p_rec.vacancy_id
143 ,p_rec.business_group_id
144 ,p_rec.position_id
145 ,p_rec.job_id
146 ,p_rec.grade_id
147 ,p_rec.organization_id
148 ,p_rec.requisition_id
149 ,p_rec.people_group_id
150 ,p_rec.location_id
151 ,p_rec.recruiter_id
152 ,p_rec.date_from
153 ,p_rec.name
154 ,p_rec.comments
155 ,p_rec.date_to
156 ,p_rec.description
157 ,p_rec.number_of_openings
158 ,p_rec.status
159 ,p_rec.request_id
160 ,p_rec.program_application_id
161 ,p_rec.program_id
162 ,p_rec.program_update_date
163 ,p_rec.attribute_category
164 ,p_rec.attribute1
165 ,p_rec.attribute2
166 ,p_rec.attribute3
167 ,p_rec.attribute4
168 ,p_rec.attribute5
169 ,p_rec.attribute6
170 ,p_rec.attribute7
171 ,p_rec.attribute8
172 ,p_rec.attribute9
173 ,p_rec.attribute10
174 ,p_rec.attribute11
175 ,p_rec.attribute12
176 ,p_rec.attribute13
177 ,p_rec.attribute14
178 ,p_rec.attribute15
179 ,p_rec.attribute16
180 ,p_rec.attribute17
181 ,p_rec.attribute18
182 ,p_rec.attribute19
183 ,p_rec.attribute20
184 ,p_rec.vacancy_category
185 ,p_rec.budget_measurement_type
186 ,p_rec.budget_measurement_value
187 ,p_rec.manager_id
188 ,p_rec.security_method
189 ,p_rec.primary_posting_id
190 ,p_rec.assessment_id
191 ,p_rec.object_version_number
192 );
193 --
194 per_vac_shd.g_api_dml := false; -- Unset the api dml status
195 --
196 hr_utility.set_location(' Leaving:'||l_proc, 10);
197 Exception
198 When hr_api.check_integrity_violated then
199 -- A check constraint has been violated
200 per_vac_shd.g_api_dml := false; -- Unset the api dml status
201 per_vac_shd.constraint_error
202 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
203 When hr_api.parent_integrity_violated then
204 -- Parent integrity has been violated
205 per_vac_shd.g_api_dml := false; -- Unset the api dml status
206 per_vac_shd.constraint_error
207 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
208 When hr_api.unique_integrity_violated then
209 -- Unique integrity has been violated
210 per_vac_shd.g_api_dml := false; -- Unset the api dml status
211 per_vac_shd.constraint_error
212 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
213 When Others then
214 per_vac_shd.g_api_dml := false; -- Unset the api dml status
215 Raise;
216 End insert_dml;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |------------------------------< pre_insert >------------------------------|
220 -- ----------------------------------------------------------------------------
221 -- {Start Of Comments}
222 --
223 -- Description:
224 -- This private procedure contains any processing which is required before
225 -- the insert dml. Presently, if the entity has a corresponding primary
226 -- key which is maintained by an associating sequence, the primary key for
227 -- the entity will be populated with the next sequence value in
228 -- preparation for the insert dml.
229 --
230 -- Prerequisites:
231 -- This is an internal procedure which is called from the ins procedure.
232 --
233 -- In Parameters:
234 -- A Pl/Sql record structure.
235 --
236 -- Post Success:
237 -- Processing continues.
238 --
239 -- Post Failure:
240 -- if an error has occurred, an error message and exception will be raised
241 -- but not handled.
242 --
243 -- Developer Implementation Notes:
244 -- Any pre-processing required before the insert dml is issued should be
245 -- coded within this procedure. As stated above, a good example is the
246 -- generation of a primary key number via a corresponding sequence.
247 -- It is important to note that any 3rd party maintenance should be reviewed
248 -- before placing in this procedure.
249 --
250
251 -- Access Status:
252 -- Internal Row Handler Use Only.
253 --
254 -- {End Of Comments}
255 -- ----------------------------------------------------------------------------
256 Procedure pre_insert
257 (p_rec in out nocopy per_vac_shd.g_rec_type
258 ) is
259 --
260 Cursor C_Sel1 is select per_vacancies_s.nextval from sys.dual;
261 --
262 Cursor C_Sel2 is
263 Select null
264 from per_all_vacancies
265 where vacancy_id =
266 per_vac_ins.g_vacancy_id_i;
267 --
268 l_proc varchar2(72) := g_package||'pre_insert';
269 l_exists varchar2(1);
270 --
271 Begin
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 if (per_vac_ins.g_vacancy_id_i is not null) then
275 --
276 -- Verify registered primary key values not already in use
277 --
278 Open C_Sel2;
279 Fetch C_Sel2 into l_exists;
280 if C_Sel2%found then
281 Close C_Sel2;
282 --
283 -- The primary key values are already in use.
284 --
285 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
286 fnd_message.set_token('TABLE_NAME','per_all_vacancies');
287 fnd_message.raise_error;
288 end if;
289 Close C_Sel2;
290 --
291 -- Use registered key values and clear globals
292 --
293 p_rec.vacancy_id :=
294 per_vac_ins.g_vacancy_id_i;
295 per_vac_ins.g_vacancy_id_i := null;
296 Else
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.vacancy_id;
305 Close C_Sel1;
306 end if;
307 --
308 hr_utility.set_location(' Leaving:'||l_proc, 10);
309 End pre_insert;
310 --
311 -- ----------------------------------------------------------------------------
312 -- |-----------------------------< post_insert >------------------------------|
313 -- ----------------------------------------------------------------------------
314 -- {Start Of Comments}
315 --
316 -- Description:
317 -- This private procedure contains any processing which is required after
318 -- the insert dml.
319 --
320 -- Prerequisites:
321 -- This is an internal procedure which is called from the ins procedure.
322 --
323 -- In Parameters:
324 -- A Pl/Sql record structre.
325 --
326 -- Post Success:
327 -- Processing continues.
328 --
329 -- Post Failure:
330 -- if an error has occurred, an error message and exception will be raised
331 -- but not handled.
332 --
333 -- Developer Implementation Notes:
334 -- Any post-processing required after the insert dml is issued should be
335 -- coded within this procedure. It is important to note that any 3rd party
336 -- maintenance should be reviewed before placing in this procedure.
337 --
338 -- Access Status:
339 -- Internal Row Handler Use Only.
340 --
341 -- {End Of Comments}
342 -- ----------------------------------------------------------------------------
343 Procedure post_insert
344 (p_rec in per_vac_shd.g_rec_type
345 ,p_effective_date in date
346 ) is
347 --
348 l_proc varchar2(72) := g_package||'post_insert';
349 --
350 Begin
351 hr_utility.set_location('Entering:'||l_proc, 5);
352 begin
353 --
354 per_vac_rki.after_insert
355 (p_effective_date
356 => p_effective_date
357 ,p_vacancy_id
358 => p_rec.vacancy_id
359 ,p_business_group_id
360 => p_rec.business_group_id
361 ,p_position_id
362 => p_rec.position_id
363 ,p_job_id
364 => p_rec.job_id
365 ,p_grade_id
366 => p_rec.grade_id
367 ,p_organization_id
368 => p_rec.organization_id
369 ,p_requisition_id
370 => p_rec.requisition_id
371 ,p_people_group_id
372 => p_rec.people_group_id
373 ,p_location_id
374 => p_rec.location_id
375 ,p_recruiter_id
376 => p_rec.recruiter_id
377 ,p_date_from
378 => p_rec.date_from
379 ,p_name
380 => p_rec.name
381 ,p_comments
382 => p_rec.comments
383 ,p_date_to
384 => p_rec.date_to
385 ,p_description
386 => p_rec.description
387 ,p_number_of_openings
388 => p_rec.number_of_openings
389 ,p_status
390 => p_rec.status
391 ,p_request_id
392 => p_rec.request_id
393 ,p_program_application_id
394 => p_rec.program_application_id
395 ,p_program_id
396 => p_rec.program_id
400 => p_rec.attribute_category
397 ,p_program_update_date
398 => p_rec.program_update_date
399 ,p_attribute_category
401 ,p_attribute1
402 => p_rec.attribute1
403 ,p_attribute2
404 => p_rec.attribute2
405 ,p_attribute3
406 => p_rec.attribute3
407 ,p_attribute4
408 => p_rec.attribute4
409 ,p_attribute5
410 => p_rec.attribute5
411 ,p_attribute6
412 => p_rec.attribute6
413 ,p_attribute7
414 => p_rec.attribute7
415 ,p_attribute8
416 => p_rec.attribute8
417 ,p_attribute9
418 => p_rec.attribute9
419 ,p_attribute10
420 => p_rec.attribute10
421 ,p_attribute11
422 => p_rec.attribute11
423 ,p_attribute12
424 => p_rec.attribute12
425 ,p_attribute13
426 => p_rec.attribute13
427 ,p_attribute14
428 => p_rec.attribute14
429 ,p_attribute15
430 => p_rec.attribute15
431 ,p_attribute16
432 => p_rec.attribute16
433 ,p_attribute17
434 => p_rec.attribute17
435 ,p_attribute18
436 => p_rec.attribute18
437 ,p_attribute19
438 => p_rec.attribute19
439 ,p_attribute20
440 => p_rec.attribute20
441 ,p_vacancy_category
442 => p_rec.vacancy_category
443 ,p_budget_measurement_type
444 => p_rec.budget_measurement_type
445 ,p_budget_measurement_value
446 => p_rec.budget_measurement_value
447 ,p_manager_id
448 => p_rec.manager_id
449 ,p_security_method
450 => p_rec.security_method
451 ,p_primary_posting_id
452 => p_rec.primary_posting_id
453 ,p_assessment_id
454 => p_rec.assessment_id
455 ,p_object_version_number
456 => p_rec.object_version_number
457 );
458 --
459 exception
460 --
461 when hr_api.cannot_find_prog_unit then
462 --
463 hr_api.cannot_find_prog_unit_error
464 (p_module_name => 'PER_ALL_VACANCIES'
465 ,p_hook_type => 'AI');
466 --
467 end;
468 --
469 hr_utility.set_location(' Leaving:'||l_proc, 10);
470 End post_insert;
471 --
472 -- ----------------------------------------------------------------------------
473 -- |---------------------------------< ins >----------------------------------|
474 -- ----------------------------------------------------------------------------
475 Procedure ins
476 (p_rec in out nocopy per_vac_shd.g_rec_type
477 ,p_effective_date in date
478 ,p_inv_pos_grade_warning out nocopy boolean
479 ,p_inv_job_grade_warning out nocopy boolean
480 ) is
481 --
482 l_proc varchar2(72) := g_package||'ins';
483 --
484 Begin
485 hr_utility.set_location('Entering:'||l_proc, 5);
486 --
487 -- Call the supporting insert validate operations
488 --
489 per_vac_bus.insert_validate
490 (p_rec => p_rec
491 ,p_effective_date => p_effective_date
492 ,p_inv_pos_grade_warning => p_inv_pos_grade_warning
493 ,p_inv_job_grade_warning => p_inv_job_grade_warning
494 );
495 --
496 -- Call the supporting pre-insert operation
497 --
498 per_vac_ins.pre_insert(p_rec);
499 --
500 -- Insert the row
501 --
502 per_vac_ins.insert_dml(p_rec);
503 --
504 -- Call the supporting post-insert operation
505 --
506 per_vac_ins.post_insert
507 (p_rec
508 ,p_effective_date
509 );
510 hr_multi_message.end_validation_set();
511 --
512 hr_utility.set_location('Leaving:'||l_proc, 20);
513 end ins;
514 --
515 -- ----------------------------------------------------------------------------
516 -- |---------------------------------< ins >----------------------------------|
517 -- ----------------------------------------------------------------------------
518 Procedure ins
519 (p_effective_date in date
520 ,p_business_group_id in number
521 ,p_requisition_id in number
522 ,p_date_from in date
523 ,p_name in varchar2
524 ,p_position_id in number default null
525 ,p_job_id in number default null
526 ,p_grade_id in number default null
527 ,p_organization_id in number default null
528 ,p_people_group_id in number default null
529 ,p_location_id in number default null
530 ,p_recruiter_id in number default null
531 ,p_comments in varchar2 default null
532 ,p_date_to in date default null
533 ,p_description in varchar2 default null
534 ,p_number_of_openings in number default null
535 ,p_status in varchar2 default null
536 ,p_request_id in number default null
537 ,p_program_application_id in number default null
538 ,p_program_id in number default null
539 ,p_program_update_date in date default null
540 ,p_attribute_category in varchar2 default null
541 ,p_attribute1 in varchar2 default null
542 ,p_attribute2 in varchar2 default null
543 ,p_attribute3 in varchar2 default null
544 ,p_attribute4 in varchar2 default null
545 ,p_attribute5 in varchar2 default null
546 ,p_attribute6 in varchar2 default null
547 ,p_attribute7 in varchar2 default null
548 ,p_attribute8 in varchar2 default null
549 ,p_attribute9 in varchar2 default null
550 ,p_attribute10 in varchar2 default null
551 ,p_attribute11 in varchar2 default null
552 ,p_attribute12 in varchar2 default null
553 ,p_attribute13 in varchar2 default null
554 ,p_attribute14 in varchar2 default null
555 ,p_attribute15 in varchar2 default null
556 ,p_attribute16 in varchar2 default null
557 ,p_attribute17 in varchar2 default null
558 ,p_attribute18 in varchar2 default null
559 ,p_attribute19 in varchar2 default null
560 ,p_attribute20 in varchar2 default null
561 ,p_vacancy_category in varchar2 default null
562 ,p_budget_measurement_type in varchar2 default null
563 ,p_budget_measurement_value in number default null
564 ,p_manager_id in number default null
565 ,p_security_method in varchar2 default null
566 ,p_primary_posting_id in number default null
567 ,p_assessment_id in number default null
568 ,p_inv_pos_grade_warning out nocopy boolean
569 ,p_inv_job_grade_warning out nocopy boolean
570 ,p_vacancy_id out nocopy number
571 ,p_object_version_number out nocopy number
572 ) is
573 --
574 l_rec per_vac_shd.g_rec_type;
575 l_proc varchar2(72) := g_package||'ins';
576 --
577 Begin
578 hr_utility.set_location('Entering:'||l_proc, 5);
579 --
580 -- Call conversion function to turn arguments into the
581 -- p_rec structure.
582 --
583 l_rec :=
584 per_vac_shd.convert_args
585 (null
586 ,p_business_group_id
587 ,p_position_id
588 ,p_job_id
589 ,p_grade_id
590 ,p_organization_id
591 ,p_requisition_id
592 ,p_people_group_id
593 ,p_location_id
594 ,p_recruiter_id
595 ,p_date_from
596 ,p_name
597 ,p_comments
598 ,p_date_to
599 ,p_description
600 ,p_number_of_openings
601 ,p_status
602 ,p_request_id
603 ,p_program_application_id
604 ,p_program_id
605 ,p_program_update_date
606 ,p_attribute_category
607 ,p_attribute1
608 ,p_attribute2
609 ,p_attribute3
610 ,p_attribute4
611 ,p_attribute5
612 ,p_attribute6
613 ,p_attribute7
614 ,p_attribute8
615 ,p_attribute9
616 ,p_attribute10
617 ,p_attribute11
618 ,p_attribute12
619 ,p_attribute13
620 ,p_attribute14
621 ,p_attribute15
622 ,p_attribute16
623 ,p_attribute17
624 ,p_attribute18
625 ,p_attribute19
626 ,p_attribute20
627 ,p_vacancy_category
628 ,p_budget_measurement_type
629 ,p_budget_measurement_value
630 ,p_manager_id
631 ,p_security_method
632 ,p_primary_posting_id
633 ,p_assessment_id
634 ,null
635 );
636 --
637 -- Having converted the arguments into the per_vac_rec
638 -- plsql record structure we call the corresponding record business process.
639 --
640 per_vac_ins.ins
641 (p_rec => l_rec
642 ,p_effective_date => p_effective_date
643 ,p_inv_pos_grade_warning => p_inv_pos_grade_warning
644 ,p_inv_job_grade_warning => p_inv_job_grade_warning
645 );
646 --
647 -- As the primary key argument(s)
648 -- are specified as an OUT's we must set these values.
649 --
650 p_vacancy_id := l_rec.vacancy_id;
651 p_object_version_number := l_rec.object_version_number;
652 --
653 hr_utility.set_location(' Leaving:'||l_proc, 10);
654 End ins;
655 --
656 end per_vac_ins;