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