[Home] [Help]
PACKAGE BODY: APPS.IRC_IOF_INS
Source
1 package body irc_iof_ins as
2 /* $Header: iriofrhi.pkb 120.20 2011/04/08 12:04:09 amikukum ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | private global definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iof_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_offer_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_offer_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 irc_iof_ins.g_offer_id_i := p_offer_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 irc_iof_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 --
86 --
87 -- insert the row into: irc_offers
88 --
89 insert into irc_offers
90 (offer_id
91 ,offer_version
92 ,latest_offer
93 ,offer_status
94 ,discretionary_job_title
95 ,offer_extended_method
96 ,respondent_id
97 ,expiry_date
98 ,proposed_start_date
99 ,offer_letter_tracking_code
100 ,offer_postal_service
101 ,offer_shipping_date
102 ,vacancy_id
103 ,applicant_assignment_id
104 ,offer_assignment_id
105 ,address_id
106 ,template_id
107 ,offer_letter
108 ,offer_letter_file_type
109 ,offer_letter_file_name
110 ,attribute_category
111 ,attribute1
112 ,attribute2
113 ,attribute3
114 ,attribute4
115 ,attribute5
116 ,attribute6
117 ,attribute7
118 ,attribute8
119 ,attribute9
120 ,attribute10
121 ,attribute11
122 ,attribute12
123 ,attribute13
124 ,attribute14
125 ,attribute15
126 ,attribute16
127 ,attribute17
128 ,attribute18
129 ,attribute19
130 ,attribute20
131 ,attribute21
132 ,attribute22
133 ,attribute23
134 ,attribute24
135 ,attribute25
136 ,attribute26
137 ,attribute27
138 ,attribute28
139 ,attribute29
140 ,attribute30
141 ,object_version_number
142 )
143 values
144 (p_rec.offer_id
145 ,p_rec.offer_version
146 ,p_rec.latest_offer
147 ,p_rec.offer_status
148 ,p_rec.discretionary_job_title
149 ,p_rec.offer_extended_method
150 ,p_rec.respondent_id
151 ,p_rec.expiry_date
152 ,p_rec.proposed_start_date
153 ,p_rec.offer_letter_tracking_code
154 ,p_rec.offer_postal_service
155 ,p_rec.offer_shipping_date
156 ,p_rec.vacancy_id
157 ,p_rec.applicant_assignment_id
158 ,p_rec.offer_assignment_id
159 ,p_rec.address_id
160 ,p_rec.template_id
161 ,empty_blob()
162 ,p_rec.offer_letter_file_type
163 ,p_rec.offer_letter_file_name
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.attribute21
186 ,p_rec.attribute22
187 ,p_rec.attribute23
188 ,p_rec.attribute24
189 ,p_rec.attribute25
190 ,p_rec.attribute26
191 ,p_rec.attribute27
192 ,p_rec.attribute28
193 ,p_rec.attribute29
194 ,p_rec.attribute30
195 ,p_rec.object_version_number
196 );
197 --
198 --
199 --
200 hr_utility.set_location(' leaving:'||l_proc, 10);
201 exception
202 when hr_api.check_integrity_violated then
203 -- a check constraint has been violated
204 --
205 irc_iof_shd.constraint_error
206 (p_constraint_name => hr_api.strip_constraint_name(sqlerrm));
207 when hr_api.parent_integrity_violated then
208 -- parent integrity has been violated
209 --
210 irc_iof_shd.constraint_error
211 (p_constraint_name => hr_api.strip_constraint_name(sqlerrm));
212 when hr_api.unique_integrity_violated then
213 -- unique integrity has been violated
214 --
215 irc_iof_shd.constraint_error
216 (p_constraint_name => hr_api.strip_constraint_name(sqlerrm));
217 when others then
218 --
219 raise;
220 end insert_dml;
221 --
222 -- ----------------------------------------------------------------------------
223 -- |------------------------------< pre_insert >------------------------------|
224 -- ----------------------------------------------------------------------------
225 -- {start of comments}
226 --
227 -- description:
228 -- this private procedure contains any processing which is required before
229 -- the insert dml. presently, if the entity has a corresponding primary
230 -- key which is maintained by an associating sequence, the primary key for
231 -- the entity will be populated with the next sequence value in
232 -- preparation for the insert dml.
233 --
234 -- prerequisites:
235 -- this is an internal procedure which is called from the ins procedure.
236 --
237 -- in parameters:
238 -- a pl/sql record structure.
239 --
240 -- post success:
241 -- processing continues.
242 --
243 -- post failure:
244 -- if an error has occurred, an error message and exception will be raised
245 -- but not handled.
246 --
247 -- developer implementation notes:
248 -- any pre-processing required before the insert dml is issued should be
249 -- coded within this procedure. as stated above, a good example is the
250 -- generation of a primary key number via a corresponding sequence.
251 -- it is important to note that any 3rd party maintenance should be reviewed
252 -- before placing in this procedure.
253 --
254 -- access status:
255 -- internal row handler use only.
256 --
257 -- {end of comments}
258 -- ----------------------------------------------------------------------------
259 procedure pre_insert
260 (p_rec in out nocopy irc_iof_shd.g_rec_type
261 ) is
262 --
263 cursor c_sel1 is select irc_offers_s.nextval from sys.dual;
264 --
265 cursor c_sel2 is
266 select null
267 from irc_offers
268 where offer_id =
269 irc_iof_ins.g_offer_id_i;
270 --
271 l_proc varchar2(72) := g_package||'pre_insert';
272 l_exists varchar2(1);
273 --
274 begin
275 hr_utility.set_location('entering:'||l_proc, 5);
276 --
277 if (irc_iof_ins.g_offer_id_i is not null) then
278 --
279 -- verify registered primary key values not already in use
280 --
281 open c_sel2;
282 fetch c_sel2 into l_exists;
283 if c_sel2%found then
284 close c_sel2;
285 --
286 -- the primary key values are already in use.
287 --
288 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
289 fnd_message.set_token('TABLE_NAME','irc_offers');
290 fnd_message.raise_error;
291 end if;
292 close c_sel2;
293 --
294 -- use registered key values and clear globals
295 --
296 p_rec.offer_id :=
297 irc_iof_ins.g_offer_id_i;
298 irc_iof_ins.g_offer_id_i := null;
299 else
300 --
301 -- no registerd key values, so select the next sequence number
302 --
303 --
304 -- select the next sequence number
305 --
306 open c_sel1;
307 fetch c_sel1 into p_rec.offer_id;
308 close c_sel1;
309 end if;
310 --
311 hr_utility.set_location(' leaving:'||l_proc, 10);
312 end pre_insert;
313 --
314 -- ----------------------------------------------------------------------------
315 -- |-----------------------------< post_insert >------------------------------|
316 -- ----------------------------------------------------------------------------
317 -- {start of comments}
318 --
319 -- description:
320 -- this private procedure contains any processing which is required after
321 -- the insert dml.
322 --
323 -- prerequisites:
324 -- this is an internal procedure which is called from the ins procedure.
325 --
326 -- in parameters:
327 -- a pl/sql record structre.
328 --
329 -- post success:
330 -- processing continues.
331 --
332 -- post failure:
333 -- if an error has occurred, an error message and exception will be raised
334 -- but not handled.
335 --
336 -- developer implementation notes:
337 -- any post-processing required after the insert dml is issued should be
338 -- coded within this procedure. it is important to note that any 3rd party
339 -- maintenance should be reviewed before placing in this procedure.
340 --
341 -- access status:
342 -- internal row handler use only.
343 --
344 -- {end of comments}
345 -- ----------------------------------------------------------------------------
346 procedure post_insert
347 (p_effective_date in date
348 ,p_rec in irc_iof_shd.g_rec_type
349 ) is
350 --
351 l_proc varchar2(72) := g_package||'post_insert';
352 --
353 begin
354 hr_utility.set_location('entering:'||l_proc, 5);
355 begin
356 --
357 irc_iof_rki.after_insert
358 (p_effective_date => p_effective_date
359 ,p_offer_id
360 => p_rec.offer_id
361 ,p_offer_version
362 => p_rec.offer_version
363 ,p_latest_offer
364 => p_rec.latest_offer
365 ,p_offer_status
366 => p_rec.offer_status
367 ,p_discretionary_job_title
368 => p_rec.discretionary_job_title
369 ,p_offer_extended_method
370 => p_rec.offer_extended_method
371 ,p_respondent_id
372 => p_rec.respondent_id
373 ,p_expiry_date
374 => p_rec.expiry_date
375 ,p_proposed_start_date
376 => p_rec.proposed_start_date
377 ,p_offer_letter_tracking_code
378 => p_rec.offer_letter_tracking_code
379 ,p_offer_postal_service
380 => p_rec.offer_postal_service
381 ,p_offer_shipping_date
382 => p_rec.offer_shipping_date
383 ,p_vacancy_id
384 => p_rec.vacancy_id
385 ,p_applicant_assignment_id
386 => p_rec.applicant_assignment_id
387 ,p_offer_assignment_id
388 => p_rec.offer_assignment_id
389 ,p_address_id
390 => p_rec.address_id
391 ,p_template_id
392 => p_rec.template_id
393 ,p_offer_letter_file_type
394 => p_rec.offer_letter_file_type
395 ,p_offer_letter_file_name
396 => p_rec.offer_letter_file_name
397 ,p_attribute_category
398 => p_rec.attribute_category
399 ,p_attribute1
400 => p_rec.attribute1
401 ,p_attribute2
402 => p_rec.attribute2
403 ,p_attribute3
404 => p_rec.attribute3
405 ,p_attribute4
406 => p_rec.attribute4
407 ,p_attribute5
408 => p_rec.attribute5
409 ,p_attribute6
410 => p_rec.attribute6
411 ,p_attribute7
412 => p_rec.attribute7
413 ,p_attribute8
414 => p_rec.attribute8
415 ,p_attribute9
416 => p_rec.attribute9
417 ,p_attribute10
418 => p_rec.attribute10
419 ,p_attribute11
420 => p_rec.attribute11
421 ,p_attribute12
422 => p_rec.attribute12
423 ,p_attribute13
424 => p_rec.attribute13
425 ,p_attribute14
426 => p_rec.attribute14
427 ,p_attribute15
428 => p_rec.attribute15
429 ,p_attribute16
430 => p_rec.attribute16
431 ,p_attribute17
432 => p_rec.attribute17
433 ,p_attribute18
434 => p_rec.attribute18
435 ,p_attribute19
436 => p_rec.attribute19
437 ,p_attribute20
438 => p_rec.attribute20
439 ,p_attribute21
440 => p_rec.attribute21
441 ,p_attribute22
442 => p_rec.attribute22
443 ,p_attribute23
444 => p_rec.attribute23
445 ,p_attribute24
446 => p_rec.attribute24
447 ,p_attribute25
448 => p_rec.attribute25
449 ,p_attribute26
450 => p_rec.attribute26
451 ,p_attribute27
452 => p_rec.attribute27
453 ,p_attribute28
454 => p_rec.attribute28
455 ,p_attribute29
456 => p_rec.attribute29
457 ,p_attribute30
458 => p_rec.attribute30
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 => 'irc_offers'
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_effective_date in date
481 ,p_rec in out nocopy irc_iof_shd.g_rec_type
482 ) is
483 --
484 l_proc varchar2(72) := g_package||'ins';
485 --
486 begin
487 hr_utility.set_location('entering:'||l_proc, 5);
488 --
489 -- call the supporting insert validate operations
490 --
491 irc_iof_bus.insert_validate
492 (p_effective_date
493 ,p_rec
494 );
495 --
496 -- call to raise any errors on multi-message list
497 hr_multi_message.end_validation_set;
498 --
499 -- call the supporting pre-insert operation
500 --
501 irc_iof_ins.pre_insert(p_rec);
502 --
503 -- insert the row
504 --
505 irc_iof_ins.insert_dml(p_rec);
506 --
507 -- call the supporting post-insert operation
508 --
509 irc_iof_ins.post_insert
510 (p_effective_date
511 ,p_rec
512 );
513 --
514 -- call to raise any errors on multi-message list
515 hr_multi_message.end_validation_set;
516 --
517 hr_utility.set_location('leaving:'||l_proc, 20);
518 end ins;
519 --
520 -- ----------------------------------------------------------------------------
521 -- |---------------------------------< ins >----------------------------------|
522 -- ----------------------------------------------------------------------------
523 procedure ins
524 (p_effective_date in date
525 ,p_latest_offer in varchar2
526 ,p_applicant_assignment_id in number
527 ,p_offer_assignment_id in number
528 ,p_offer_status in varchar2
529 ,p_discretionary_job_title in varchar2 default null
530 ,p_offer_extended_method in varchar2 default null
531 ,p_respondent_id in number default null
532 ,p_expiry_date in date default null
533 ,p_proposed_start_date in date default null
534 ,p_offer_letter_tracking_code in varchar2 default null
535 ,p_offer_postal_service in varchar2 default null
536 ,p_offer_shipping_date in date default null
537 ,p_address_id in number default null
538 ,p_template_id in number default null
539 ,p_offer_letter_file_type in varchar2 default null
540 ,p_offer_letter_file_name in varchar2 default null
541 ,p_attribute_category in varchar2 default null
542 ,p_attribute1 in varchar2 default null
543 ,p_attribute2 in varchar2 default null
544 ,p_attribute3 in varchar2 default null
545 ,p_attribute4 in varchar2 default null
546 ,p_attribute5 in varchar2 default null
547 ,p_attribute6 in varchar2 default null
548 ,p_attribute7 in varchar2 default null
549 ,p_attribute8 in varchar2 default null
550 ,p_attribute9 in varchar2 default null
551 ,p_attribute10 in varchar2 default null
552 ,p_attribute11 in varchar2 default null
553 ,p_attribute12 in varchar2 default null
554 ,p_attribute13 in varchar2 default null
555 ,p_attribute14 in varchar2 default null
556 ,p_attribute15 in varchar2 default null
557 ,p_attribute16 in varchar2 default null
558 ,p_attribute17 in varchar2 default null
559 ,p_attribute18 in varchar2 default null
560 ,p_attribute19 in varchar2 default null
561 ,p_attribute20 in varchar2 default null
562 ,p_attribute21 in varchar2 default null
563 ,p_attribute22 in varchar2 default null
564 ,p_attribute23 in varchar2 default null
565 ,p_attribute24 in varchar2 default null
566 ,p_attribute25 in varchar2 default null
567 ,p_attribute26 in varchar2 default null
568 ,p_attribute27 in varchar2 default null
569 ,p_attribute28 in varchar2 default null
570 ,p_attribute29 in varchar2 default null
571 ,p_attribute30 in varchar2 default null
572 ,p_vacancy_id in number default null
573 ,p_offer_id out nocopy number
574 ,p_offer_version out nocopy number
575 ,p_object_version_number out nocopy number
576 ) is
577 --
578 l_rec irc_iof_shd.g_rec_type;
579 l_proc varchar2(72) := g_package||'ins';
580 --
581 begin
582 hr_utility.set_location('entering:'||l_proc, 5);
583 --
584 -- call conversion function to turn arguments into the
585 -- p_rec structure.
586 --
587 l_rec :=
588 irc_iof_shd.convert_args
589 (null -- offer_id
590 ,null -- offer_version
591 ,p_latest_offer
592 ,p_offer_status
593 ,p_discretionary_job_title
594 ,p_offer_extended_method
595 ,p_respondent_id
596 ,p_expiry_date
597 ,p_proposed_start_date
598 ,p_offer_letter_tracking_code
599 ,p_offer_postal_service
600 ,p_offer_shipping_date
601 ,p_vacancy_id
602 ,p_applicant_assignment_id
603 ,p_offer_assignment_id
604 ,p_address_id
605 ,p_template_id
606 ,p_offer_letter_file_type
607 ,p_offer_letter_file_name
608 ,p_attribute_category
609 ,p_attribute1
610 ,p_attribute2
611 ,p_attribute3
612 ,p_attribute4
613 ,p_attribute5
614 ,p_attribute6
615 ,p_attribute7
616 ,p_attribute8
617 ,p_attribute9
618 ,p_attribute10
619 ,p_attribute11
620 ,p_attribute12
621 ,p_attribute13
622 ,p_attribute14
623 ,p_attribute15
624 ,p_attribute16
625 ,p_attribute17
626 ,p_attribute18
627 ,p_attribute19
628 ,p_attribute20
629 ,p_attribute21
630 ,p_attribute22
631 ,p_attribute23
632 ,p_attribute24
633 ,p_attribute25
634 ,p_attribute26
635 ,p_attribute27
636 ,p_attribute28
637 ,p_attribute29
638 ,p_attribute30
639 ,null -- object_version_number
640 );
641 --
642 -- having converted the arguments into the irc_iof_rec
643 -- plsql record structure we call the corresponding record business process.
644 --
645 irc_iof_ins.ins
646 (p_effective_date
647 ,l_rec
648 );
649 --
650 -- as the primary key argument(s)
651 -- are specified as an out's we must set these values.
652 --
653 p_offer_id := l_rec.offer_id;
654 p_object_version_number := l_rec.object_version_number;
655 p_offer_version := l_rec.offer_version;
656 --
657 hr_utility.set_location(' leaving:'||l_proc, 10);
658 end ins;
659 --
660 end irc_iof_ins;