1 Package Body pqh_sts_ins as
2 /* $Header: pqstsrhi.pkb 120.0 2005/05/29 02:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_sts_ins.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_statutory_situation_id_i number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_statutory_situation_id in number) is
21 --
22 l_proc varchar2(72) := g_package||'set_base_key_value';
23 --
24 Begin
25
26 g_debug := hr_utility.debug_enabled;
27
28 if g_debug then
29 --
30 hr_utility.set_location('Entering:'||l_proc, 10);
31 --
32 end if;
33 --
34 pqh_sts_ins.g_statutory_situation_id_i := p_statutory_situation_id;
35 --
36 if g_debug then
37 --
38 hr_utility.set_location(' Leaving:'||l_proc, 20);
39 --
40 end if;
41
42 End set_base_key_value;
43 --
44 --
45 -- ----------------------------------------------------------------------------
46 -- |------------------------------< insert_dml >------------------------------|
47 -- ----------------------------------------------------------------------------
48 -- {Start Of Comments}
49 --
50 -- Description:
51 -- This procedure controls the actual dml insert logic. The processing of
52 -- this procedure are as follows:
53 -- 1) Initialise the object_version_number to 1 if the object_version_number
54 -- is defined as an attribute for this entity.
55 -- 2) To set and unset the g_api_dml status as required (as we are about to
56 -- perform dml).
57 -- 3) To insert the row into the schema.
58 -- 4) To trap any constraint violations that may have occurred.
59 -- 5) To raise any other errors.
60 --
61 -- Prerequisites:
62 -- This is an internal private procedure which must be called from the ins
63 -- procedure and must have all mandatory attributes set (except the
64 -- object_version_number which is initialised within this procedure).
65 --
66 -- In Parameters:
67 -- A Pl/Sql record structre.
68 --
69 -- Post Success:
70 -- The specified row will be inserted into the schema.
71 --
72 -- Post Failure:
73 -- On the insert dml failure it is important to note that we always reset the
74 -- g_api_dml status to false.
75 -- If a check, unique or parent integrity constraint violation is raised the
76 -- constraint_error procedure will be called.
77 -- If any other error is reported, the error will be raised after the
78 -- g_api_dml status is reset.
79 --
80 -- Developer Implementation Notes:
81 -- None.
82 --
83 -- Access Status:
84 -- Internal Row Handler Use Only.
85 --
86 -- {End Of Comments}
87 -- ----------------------------------------------------------------------------
88 Procedure insert_dml
89 (p_rec in out nocopy pqh_sts_shd.g_rec_type
90 ) is
91 --
92 l_proc varchar2(72) := g_package||'insert_dml';
93 --
94 Begin
95 if g_debug then
96 --
97 hr_utility.set_location('Entering:'||l_proc, 5);
98 --
99 End if;
100
101 p_rec.object_version_number := 1; -- Initialise the object version
102 --
103 --
104 --
105 -- Insert the row into: pqh_fr_stat_situations
106 --
107 insert into pqh_fr_stat_situations
108 (statutory_situation_id
109 ,business_group_id
110 ,situation_name
111 ,type_of_ps
112 ,situation_type
113 ,sub_type
114 ,source
115 ,location
116 ,reason
117 ,default_flag
118 ,date_from
119 ,date_to
120 ,request_type
121 ,employee_agreement_needed
122 ,manager_agreement_needed
123 ,print_arrette
124 ,reserve_position
125 ,allow_progression_flag
126 ,extend_probation_period_flag
127 ,remuneration_paid
128 ,pay_share
129 ,pay_periods
130 ,frequency
131 ,first_period_max_duration
132 ,min_duration_per_request
133 ,max_duration_per_request
134 ,max_duration_whole_career
135 ,renewable_allowed
136 ,max_no_of_renewals
137 ,max_duration_per_renewal
138 ,max_tot_continuous_duration
139 ,object_version_number
140 ,remunerate_assign_status_id
141 )
142 Values
143 (p_rec.statutory_situation_id
144 ,p_rec.business_group_id
145 ,p_rec.situation_name
146 ,p_rec.type_of_ps
147 ,p_rec.situation_type
148 ,p_rec.sub_type
149 ,p_rec.source
150 ,p_rec.location
151 ,p_rec.reason
152 ,p_rec.is_default
153 ,p_rec.date_from
154 ,p_rec.date_to
155 ,p_rec.request_type
156 ,p_rec.employee_agreement_needed
157 ,p_rec.manager_agreement_needed
158 ,p_rec.print_arrette
159 ,p_rec.reserve_position
160 ,p_rec.allow_progressions
161 ,p_rec.extend_probation_period
162 ,p_rec.remuneration_paid
163 ,p_rec.pay_share
164 ,p_rec.pay_periods
165 ,p_rec.frequency
166 ,p_rec.first_period_max_duration
167 ,p_rec.min_duration_per_request
168 ,p_rec.max_duration_per_request
169 ,p_rec.max_duration_whole_career
170 ,p_rec.renewable_allowed
171 ,p_rec.max_no_of_renewals
172 ,p_rec.max_duration_per_renewal
173 ,p_rec.max_tot_continuous_duration
174 ,p_rec.object_version_number
175 ,p_rec.remunerate_assign_status_id
176 );
177 --
178 --
179 --
180 if g_debug then
181 --
182 hr_utility.set_location(' Leaving:'||l_proc, 10);
183 --
184 End if;
185
186 Exception
187 When hr_api.check_integrity_violated Then
188 -- A check constraint has been violated
189 --
190 pqh_sts_shd.constraint_error
191 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
192 When hr_api.parent_integrity_violated Then
193 -- Parent integrity has been violated
194 --
195 pqh_sts_shd.constraint_error
196 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
197 When hr_api.unique_integrity_violated Then
198 -- Unique integrity has been violated
199 --
200 pqh_sts_shd.constraint_error
201 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
202 When Others Then
203 --
204 Raise;
205 End insert_dml;
206 --
207 -- ----------------------------------------------------------------------------
208 -- |------------------------------< pre_insert >------------------------------|
209 -- ----------------------------------------------------------------------------
210 -- {Start Of Comments}
211 --
212 -- Description:
213 -- This private procedure contains any processing which is required before
214 -- the insert dml. Presently, if the entity has a corresponding primary
215 -- key which is maintained by an associating sequence, the primary key for
216 -- the entity will be populated with the next sequence value in
217 -- preparation for the insert dml.
218 --
219 -- Prerequisites:
220 -- This is an internal procedure which is called from the ins procedure.
221 --
222 -- In Parameters:
223 -- A Pl/Sql record structure.
224 --
225 -- Post Success:
226 -- Processing continues.
227 --
228 -- Post Failure:
229 -- If an error has occurred, an error message and exception will be raised
230 -- but not handled.
231 --
232 -- Developer Implementation Notes:
233 -- Any pre-processing required before the insert dml is issued should be
234 -- coded within this procedure. As stated above, a good example is the
235 -- generation of a primary key number via a corresponding sequence.
236 -- It is important to note that any 3rd party maintenance should be reviewed
237 -- before placing in this procedure.
238 --
239 -- Access Status:
240 -- Internal Row Handler Use Only.
241 --
242 -- {End Of Comments}
243 -- ----------------------------------------------------------------------------
244 Procedure pre_insert
245 (p_rec in out nocopy pqh_sts_shd.g_rec_type
246 ) is
247 --
248 Cursor C_Sel1 is select pqh_fr_stat_situations_s.nextval from sys.dual;
249 --
250 Cursor C_Sel2 is
251 Select null
252 from pqh_fr_stat_situations
253 where statutory_situation_id =
254 pqh_sts_ins.g_statutory_situation_id_i;
255 --
256 l_proc varchar2(72) := g_package||'pre_insert';
257 l_exists varchar2(1);
258 --
259 Begin
260 if g_debug then
261 --
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 End if;
265 --
269 --
266 If (pqh_sts_ins.g_statutory_situation_id_i is not null) Then
267 --
268 -- Verify registered primary key values not already in use
270 Open C_Sel2;
271 Fetch C_Sel2 into l_exists;
272 If C_Sel2%found Then
273 Close C_Sel2;
274 --
275 -- The primary key values are already in use.
276 --
277 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
278 fnd_message.set_token('TABLE_NAME','pqh_fr_stat_situations');
279 fnd_message.raise_error;
280 End If;
281 Close C_Sel2;
282 --
283 -- Use registered key values and clear globals
284 --
285 p_rec.statutory_situation_id :=
286 pqh_sts_ins.g_statutory_situation_id_i;
287 pqh_sts_ins.g_statutory_situation_id_i := null;
288 Else
289 --
290 -- No registerd key values, so select the next sequence number
291 --
292 --
293 -- Select the next sequence number
294 --
295 Open C_Sel1;
296 Fetch C_Sel1 Into p_rec.statutory_situation_id;
297 Close C_Sel1;
298 End If;
299 --
300 if g_debug then
301 --
302 hr_utility.set_location(' Leaving:'||l_proc, 10);
303 --
304 End if;
305 End pre_insert;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |-----------------------------< post_insert >------------------------------|
309 -- ----------------------------------------------------------------------------
310 -- {Start Of Comments}
311 --
312 -- Description:
313 -- This private procedure contains any processing which is required after
314 -- the insert dml.
315 --
316 -- Prerequisites:
317 -- This is an internal procedure which is called from the ins procedure.
318 --
319 -- In Parameters:
320 -- A Pl/Sql record structre.
321 --
322 -- Post Success:
323 -- Processing continues.
324 --
325 -- Post Failure:
326 -- If an error has occurred, an error message and exception will be raised
327 -- but not handled.
328 --
329 -- Developer Implementation Notes:
330 -- Any post-processing required after the insert dml is issued should be
331 -- coded within this procedure. It is important to note that any 3rd party
332 -- maintenance should be reviewed before placing in this procedure.
333 --
334 -- Access Status:
335 -- Internal Row Handler Use Only.
336 --
337 -- {End Of Comments}
338 -- ----------------------------------------------------------------------------
339 Procedure post_insert
340 (p_effective_date in date
341 ,p_rec in pqh_sts_shd.g_rec_type
342 ) is
343 --
344 l_proc varchar2(72) := g_package||'post_insert';
345 --
346 Begin
347 if g_debug then
348 --
349 hr_utility.set_location('Entering:'||l_proc, 5);
350 --
351 end if;
352 begin
353 --
354 pqh_sts_rki.after_insert
355 (p_effective_date => p_effective_date
356 ,p_statutory_situation_id
357 => p_rec.statutory_situation_id
358 ,p_business_group_id
359 => p_rec.business_group_id
360 ,p_situation_name
361 => p_rec.situation_name
362 ,p_type_of_ps
363 => p_rec.type_of_ps
364 ,p_situation_type
365 => p_rec.situation_type
366 ,p_sub_type
367 => p_rec.sub_type
368 ,p_source
369 => p_rec.source
370 ,p_is_default
371 => p_rec.is_default
372 ,p_location
373 => p_rec.location
374 ,p_reason
375 => p_rec.reason
376 ,p_date_from
377 => p_rec.date_from
378 ,p_date_to
379 => p_rec.date_to
380 ,p_request_type
381 => p_rec.request_type
382 ,p_employee_agreement_needed
383 => p_rec.employee_agreement_needed
384 ,p_manager_agreement_needed
385 => p_rec.manager_agreement_needed
386 ,p_print_arrette
387 => p_rec.print_arrette
388 ,p_reserve_position
389 => p_rec.reserve_position
390 ,p_allow_progressions
391 => p_rec.allow_progressions
392 ,p_extend_probation_period
393 => p_rec.extend_probation_period
394 ,p_remuneration_paid
395 => p_rec.remuneration_paid
396 ,p_pay_share
397 => p_rec.pay_share
398 ,p_pay_periods
399 => p_rec.pay_periods
400 ,p_frequency
401 => p_rec.frequency
402 ,p_first_period_max_duration
403 => p_rec.first_period_max_duration
404 ,p_min_duration_per_request
405 => p_rec.min_duration_per_request
406 ,p_max_duration_per_request
407 => p_rec.max_duration_per_request
408 ,p_max_duration_whole_career
409 => p_rec.max_duration_whole_career
410 ,p_renewable_allowed
411 => p_rec.renewable_allowed
412 ,p_max_no_of_renewals
413 => p_rec.max_no_of_renewals
414 ,p_max_duration_per_renewal
415 => p_rec.max_duration_per_renewal
416 ,p_max_tot_continuous_duration
417 => p_rec.max_tot_continuous_duration
418 ,p_object_version_number
419 => p_rec.object_version_number
420 ,p_remunerate_assign_status_id
421 => p_rec.remunerate_assign_status_id
422 );
423 --
424 exception
425 --
429 (p_module_name => 'PQH_FR_STAT_SITUATIONS'
426 when hr_api.cannot_find_prog_unit then
427 --
428 hr_api.cannot_find_prog_unit_error
430 ,p_hook_type => 'AI');
431 --
432 end;
433 --
434 if g_debug then
435 --
436 hr_utility.set_location(' Leaving:'||l_proc, 10);
437 --
438 End if;
439 End post_insert;
440 --
441 -- ----------------------------------------------------------------------------
442 -- |---------------------------------< ins >----------------------------------|
443 -- ----------------------------------------------------------------------------
444 Procedure ins
445 (p_effective_date in date
446 ,p_rec in out nocopy pqh_sts_shd.g_rec_type
447 ) is
448 --
449 l_proc varchar2(72) := g_package||'ins';
450 --
451 Begin
452
453 g_debug := hr_utility.debug_enabled;
454
455 if g_debug then
456 --
457 hr_utility.set_location('Entering:'||l_proc, 5);
458 --
459 End if;
460 --
461 -- Call the supporting insert validate operations
462 --
463 pqh_sts_bus.insert_validate
464 (p_effective_date
465 ,p_rec
466 );
467 --
468 -- Call to raise any errors on multi-message list
469 hr_multi_message.end_validation_set;
470 --
471 -- Call the supporting pre-insert operation
472 --
473 pqh_sts_ins.pre_insert(p_rec);
474 --
475 -- Insert the row
476 --
477 pqh_sts_ins.insert_dml(p_rec);
478 --
479 -- Call the supporting post-insert operation
480 --
481 pqh_sts_ins.post_insert
482 (p_effective_date
483 ,p_rec
484 );
485 --
486 -- Call to raise any errors on multi-message list
487 hr_multi_message.end_validation_set;
488 --
489 if g_debug then
490 --
491 hr_utility.set_location('Leaving:'||l_proc, 20);
492 --
493 End if;
494 end ins;
495 --
496 -- ----------------------------------------------------------------------------
497 -- |---------------------------------< ins >----------------------------------|
498 -- ----------------------------------------------------------------------------
499 Procedure ins
500 (p_effective_date in date
501 ,p_business_group_id in number
502 ,p_situation_name in varchar2
503 ,p_type_of_ps in varchar2
504 ,p_situation_type in varchar2
505 ,p_sub_type in varchar2 default null
506 ,p_source in varchar2 default null
507 ,p_location in varchar2 default null
508 ,p_reason in varchar2 default null
509 ,p_is_default in varchar2 default null
510 ,p_date_from in date default null
511 ,p_date_to in date default null
512 ,p_request_type in varchar2 default null
513 ,p_employee_agreement_needed in varchar2 default null
514 ,p_manager_agreement_needed in varchar2 default null
515 ,p_print_arrette in varchar2 default null
516 ,p_reserve_position in varchar2 default null
517 ,p_allow_progressions in varchar2 default null
518 ,p_extend_probation_period in varchar2 default null
519 ,p_remuneration_paid in varchar2 default null
520 ,p_pay_share in number default null
521 ,p_pay_periods in number default null
522 ,p_frequency in varchar2 default null
523 ,p_first_period_max_duration in number default null
524 ,p_min_duration_per_request in number default null
525 ,p_max_duration_per_request in number default null
526 ,p_max_duration_whole_career in number default null
527 ,p_renewable_allowed in varchar2 default null
528 ,p_max_no_of_renewals in number default null
529 ,p_max_duration_per_renewal in number default null
530 ,p_max_tot_continuous_duration in number default null
531 ,p_remunerate_assign_status_id in number default null
532 ,p_statutory_situation_id out nocopy number
533 ,p_object_version_number out nocopy number
534 ) is
535 --
536 l_rec pqh_sts_shd.g_rec_type;
537 l_proc varchar2(72) := g_package||'ins';
538 --
539 Begin
540
541 g_debug := hr_utility.debug_enabled;
542
543 if g_debug then
544 --
545 hr_utility.set_location('Entering:'||l_proc, 5);
546 --
547 End if;
548 --
549 -- Call conversion function to turn arguments into the
550 -- p_rec structure.
551 --
552 l_rec :=
553 pqh_sts_shd.convert_args
554 (null
555 ,p_business_group_id
556 ,p_situation_name
557 ,p_type_of_ps
558 ,p_situation_type
559 ,p_sub_type
560 ,p_source
561 ,p_location
562 ,p_reason
563 ,p_is_default
564 ,trunc(p_date_from)
565 ,trunc(p_date_to)
566 ,p_request_type
567 ,p_employee_agreement_needed
568 ,p_manager_agreement_needed
569 ,p_print_arrette
570 ,p_reserve_position
571 ,p_allow_progressions
572 ,p_extend_probation_period
573 ,p_remuneration_paid
574 ,p_pay_share
575 ,p_pay_periods
576 ,p_frequency
577 ,p_first_period_max_duration
578 ,p_min_duration_per_request
579 ,p_max_duration_per_request
580 ,p_max_duration_whole_career
581 ,p_renewable_allowed
582 ,p_max_no_of_renewals
583 ,p_max_duration_per_renewal
584 ,p_max_tot_continuous_duration
585 ,null
586 ,p_remunerate_assign_status_id
587 );
588 --
589 -- Having converted the arguments into the pqh_sts_rec
590 -- plsql record structure we call the corresponding record business process.
591 --
592 pqh_sts_ins.ins
593 (p_effective_date
594 ,l_rec
595 );
596 --
597 -- As the primary key argument(s)
598 -- are specified as an OUT's we must set these values.
599 --
600 p_statutory_situation_id := l_rec.statutory_situation_id;
601 p_object_version_number := l_rec.object_version_number;
602 --
603 if g_debug then
604 --
605 hr_utility.set_location(' Leaving:'||l_proc, 10);
606 --
607 End if;
608 End ins;
609 --
610 end pqh_sts_ins;
[Home] [Help]