1 Package Body per_suc_ins as
2 /* $Header: pesucrhi.pkb 120.1 2008/02/05 07:07:45 schowdhu noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_suc_ins.'; -- Global package name
9 g_succession_plan_id number; -------- global value set by swi by calling set base key value
10
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------------< set_base_key_value >------------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- This procedure copies the succesion plan id (primary key) into
19 -- g_succession_plan_id - a global value
20 -- Prerequisites:
21 -- This is an internal private procedure which must be called from the swi
22 -- to set the primary key
23 --
24 -- In Parameters:
25 -- Succession_plan_id - primary key
26 --
27
28 --
29 -- Developer Implementation Notes:
30 -- None.
31 --
32 -- Access Status:
33 -- Internal Row Handler Use Only.
34 --
35 -- {End Of Comments}
36 -- ----------------------------------------------------------------------------
37 Procedure set_base_key_value (p_succession_plan_id number) is
38 --
39 l_proc varchar2(72) := g_package||'set_base_key_value';
40 --
41 Begin
42 hr_utility.set_location('Entering:'||l_proc, 5);
43 --
44 g_succession_plan_id := p_succession_plan_id;
45 End;
46 --
47 -- ----------------------------------------------------------------------------
48 -- |------------------------------< insert_dml >------------------------------|
49 -- ----------------------------------------------------------------------------
50 -- {Start Of Comments}
51 --
52 -- Description:
53 -- This procedure controls the actual dml insert logic. The processing of
54 -- this procedure are as follows:
55 -- 1) Initialise the object_version_number to 1 if the object_version_number
56 -- is defined as an attribute for this entity.
57 -- 2) To set and unset the g_api_dml status as required (as we are about to
58 -- perform dml).
59 -- 3) To insert the row into the schema.
60 -- 4) To trap any constraint violations that may have occurred.
61 -- 5) To raise any other errors.
62 --
63 -- Prerequisites:
64 -- This is an internal private procedure which must be called from the ins
65 -- procedure and must have all mandatory attributes set (except the
66 -- object_version_number which is initialised within this procedure).
67 --
68 -- In Parameters:
69 -- A Pl/Sql record structre.
70 --
71 -- Post Success:
72 -- The specified row will be inserted into the schema.
73 --
74 -- Post Failure:
75 -- On the insert dml failure it is important to note that we always reset the
76 -- g_api_dml status to false.
77 -- If a check, unique or parent integrity constraint violation is raised the
78 -- constraint_error procedure will be called.
79 -- If any other error is reported, the error will be raised after the
80 -- g_api_dml status is reset.
81 --
82 -- Developer Implementation Notes:
83 -- None.
84 --
85 -- Access Status:
86 -- Internal Row Handler Use Only.
87 --
88 -- {End Of Comments}
89 -- ----------------------------------------------------------------------------
90 Procedure insert_dml(p_rec in out nocopy per_suc_shd.g_rec_type) is
91 --
92 l_proc varchar2(72) := g_package||'insert_dml';
93 --
94 Begin
95 hr_utility.set_location('Entering:'||l_proc, 5);
96 p_rec.object_version_number := 1; -- Initialise the object version
97 --
98 per_suc_shd.g_api_dml := true; -- Set the api dml status
99 --
100 -- Insert the row into: per_succession_planning
101 --
102 insert into per_succession_planning
103 ( succession_plan_id,
104 person_id,
105 position_id,
106 business_group_id,
107 start_date,
108 time_scale,
109 end_date,
110 available_for_promotion,
111 manager_comments,
112 object_version_number,
113 attribute_category,
114 attribute1,
115 attribute2,
116 attribute3,
117 attribute4,
118 attribute5,
119 attribute6,
120 attribute7,
121 attribute8,
122 attribute9,
123 attribute10,
124 attribute11,
125 attribute12,
126 attribute13,
127 attribute14,
128 attribute15,
129 attribute16,
130 attribute17,
131 attribute18,
132 attribute19,
133 attribute20,
134 job_id,
135 successee_person_id
136 )
137 Values
138 ( p_rec.succession_plan_id,
139 p_rec.person_id,
140 p_rec.position_id,
141 p_rec.business_group_id,
142 p_rec.start_date,
143 p_rec.time_scale,
144 p_rec.end_date,
145 p_rec.available_for_promotion,
146 p_rec.manager_comments,
147 p_rec.object_version_number,
148 p_rec.attribute_category,
149 p_rec.attribute1,
150 p_rec.attribute2,
151 p_rec.attribute3,
152 p_rec.attribute4,
153 p_rec.attribute5,
154 p_rec.attribute6,
155 p_rec.attribute7,
156 p_rec.attribute8,
157 p_rec.attribute9,
158 p_rec.attribute10,
159 p_rec.attribute11,
160 p_rec.attribute12,
161 p_rec.attribute13,
162 p_rec.attribute14,
163 p_rec.attribute15,
164 p_rec.attribute16,
165 p_rec.attribute17,
166 p_rec.attribute18,
167 p_rec.attribute19,
168 p_rec.attribute20,
169 p_rec.job_id,
170 p_rec.successee_person_id
171 );
172 --
173 per_suc_shd.g_api_dml := false; -- Unset the api dml status
174 --
175 hr_utility.set_location(' Leaving:'||l_proc, 10);
176 Exception
177 When hr_api.check_integrity_violated Then
178 -- A check constraint has been violated
179 per_suc_shd.g_api_dml := false; -- Unset the api dml status
180 per_suc_shd.constraint_error
181 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
182 When hr_api.parent_integrity_violated Then
183 -- Parent integrity has been violated
184 per_suc_shd.g_api_dml := false; -- Unset the api dml status
185 per_suc_shd.constraint_error
186 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
187 When hr_api.unique_integrity_violated Then
188 -- Unique integrity has been violated
189 per_suc_shd.g_api_dml := false; -- Unset the api dml status
190 per_suc_shd.constraint_error
191 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
192 When Others Then
193 per_suc_shd.g_api_dml := false; -- Unset the api dml status
194 Raise;
195 End insert_dml;
196 --
197 -- ----------------------------------------------------------------------------
198 -- |------------------------------< pre_insert >------------------------------|
199 -- ----------------------------------------------------------------------------
200 -- {Start Of Comments}
201 --
202 -- Description:
203 -- This private procedure contains any processing which is required before
204 -- the insert dml. Presently, if the entity has a corresponding primary
205 -- key which is maintained by an associating sequence, the primary key for
206 -- the entity will be populated with the next sequence value in
207 -- preparation for the insert dml.
208 --
209 -- Prerequisites:
210 -- This is an internal procedure which is called from the ins procedure.
211 --
212 -- In Parameters:
213 -- A Pl/Sql record structre.
214 --
215 -- Post Success:
216 -- Processing continues.
217 --
218 -- Post Failure:
219 -- If an error has occurred, an error message and exception will be raised
220 -- but not handled.
221 --
222 -- Developer Implementation Notes:
223 -- Any pre-processing required before the insert dml is issued should be
224 -- coded within this procedure. As stated above, a good example is the
225 -- generation of a primary key number via a corresponding sequence.
226 -- It is important to note that any 3rd party maintenance should be reviewed
227 -- before placing in this procedure.
228 --
229 -- Access Status:
230 -- Internal Row Handler Use Only.
231 --
232 -- {End Of Comments}
233 -- ----------------------------------------------------------------------------
234 Procedure pre_insert(p_rec in out nocopy per_suc_shd.g_rec_type) is
235 --
236 l_proc varchar2(72) := g_package||'pre_insert';
237 --
238 Cursor C_Sel1 is select per_succession_planning_s.nextval from sys.dual;
239 --
240 Begin
241 hr_utility.set_location('Entering:'||l_proc, 5);
242 --
243 if( g_succession_plan_id is null ) then
244 --
245 -- Select the next sequence number
246 --
247 Open C_Sel1;
248 Fetch C_Sel1 Into p_rec.succession_plan_id;
249 Close C_Sel1;
250
251 else
252 p_rec.succession_plan_id := g_succession_plan_id;
253 g_succession_plan_id :=null;
254
255 end if;
256 --
257 hr_utility.set_location(' Leaving:'||l_proc, 10);
258 End pre_insert;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |-----------------------------< post_insert >------------------------------|
262 -- ----------------------------------------------------------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 -- This private procedure contains any processing which is required after the
267 -- insert dml.
268 --
269 -- Prerequisites:
270 -- This is an internal procedure which is called from the ins procedure.
271 --
272 -- In Parameters:
273 -- A Pl/Sql record structre.
274 --
275 -- Post Success:
276 -- Processing continues.
277 --
278 -- Post Failure:
279 -- If an error has occurred, an error message and exception will be raised
280 -- but not handled.
281 --
282 -- Developer Implementation Notes:
283 -- Any post-processing required after the insert dml is issued should be
284 -- coded within this procedure. It is important to note that any 3rd party
285 -- maintenance should be reviewed before placing in this procedure.
286 --
287 -- Access Status:
288 -- Internal Row Handler Use Only.
289 --
290 -- {End Of Comments}
291 -- ----------------------------------------------------------------------------
292 Procedure post_insert(p_rec in per_suc_shd.g_rec_type,
293 p_effective_date in date) is
294 --
295 l_proc varchar2(72) := g_package||'post_insert';
296 --
297 Begin
298 hr_utility.set_location('Entering:'||l_proc, 5);
299 --
300 -- Start of Row Handler User Hook for post_insert.
301 --
302 Begin
303 per_suc_rki.after_insert
304 (
305 p_succession_plan_id => p_rec.succession_plan_id,
306 p_person_id => p_rec.person_id,
307 p_position_id => p_rec.position_id,
308 p_business_group_id => p_rec.business_group_id,
309 p_start_date => p_rec.start_date,
310 p_time_scale => p_rec.time_scale,
311 p_end_date => p_rec.end_date,
312 p_available_for_promotion => p_rec.available_for_promotion,
313 p_manager_comments => p_rec.manager_comments,
314 p_object_version_number => p_rec.object_version_number,
315 p_attribute_category => p_rec.attribute_category,
316 p_attribute1 => p_rec.attribute1,
317 p_attribute2 => p_rec.attribute2,
318 p_attribute3 => p_rec.attribute3,
319 p_attribute4 => p_rec.attribute4,
320 p_attribute5 => p_rec.attribute5,
321 p_attribute6 => p_rec.attribute6,
322 p_attribute7 => p_rec.attribute7,
323 p_attribute8 => p_rec.attribute8,
324 p_attribute9 => p_rec.attribute9,
325 p_attribute10 => p_rec.attribute10,
326 p_attribute11 => p_rec.attribute11,
327 p_attribute12 => p_rec.attribute12,
328 p_attribute13 => p_rec.attribute13,
329 p_attribute14 => p_rec.attribute14,
330 p_attribute15 => p_rec.attribute15,
331 p_attribute16 => p_rec.attribute16,
332 p_attribute17 => p_rec.attribute17,
333 p_attribute18 => p_rec.attribute18,
334 p_attribute19 => p_rec.attribute19,
335 p_attribute20 => p_rec.attribute20,
336 p_effective_date => p_effective_date,
337 p_job_id => p_rec.job_id,
338 p_successee_person_id => p_rec.successee_person_id
339 );
340 exception
341 when hr_api.cannot_find_prog_unit then
342 hr_api.cannot_find_prog_unit_error
343 (p_module_name => 'PER_SUCCESSION_PLANNING'
344 ,p_hook_type => 'AI'
345 );
346 end;
347 --
348 -- End of Row Handler User Hook for post_insert.
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 End post_insert;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------------< ins >----------------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure ins
357 (
358 p_rec in out nocopy per_suc_shd.g_rec_type,
359 p_effective_date in date
360 ) is
361 --
362 l_proc varchar2(72) := g_package||'ins';
363 --
364 Begin
365 hr_utility.set_location('Entering:'||l_proc, 5);
366 --
367 -- Call the supporting insert validate operations
368 --
369 per_suc_bus.insert_validate(p_rec,p_effective_date);
370 --
371 -- Call the supporting pre-insert operation
372 --
373 pre_insert(p_rec);
374 --
375 -- Insert the row
376 --
377 insert_dml(p_rec);
378 --
379 -- Call the supporting post-insert operation
380 --
381 post_insert(p_rec, p_effective_date);
382 end ins;
383 --
384 -- ----------------------------------------------------------------------------
385 -- |---------------------------------< ins >----------------------------------|
386 -- ----------------------------------------------------------------------------
387 Procedure ins
388 (
389 p_succession_plan_id out nocopy number,
390 p_person_id in number,
391 p_position_id in number default null,
392 p_business_group_id in number,
393 p_start_date in date,
394 p_time_scale in varchar2,
395 p_end_date in date default null,
396 p_available_for_promotion in varchar2 default null,
397 p_manager_comments in varchar2 default null,
398 p_object_version_number out nocopy number,
399 p_attribute_category in varchar2 default null,
400 p_attribute1 in varchar2 default null,
401 p_attribute2 in varchar2 default null,
402 p_attribute3 in varchar2 default null,
403 p_attribute4 in varchar2 default null,
404 p_attribute5 in varchar2 default null,
405 p_attribute6 in varchar2 default null,
406 p_attribute7 in varchar2 default null,
407 p_attribute8 in varchar2 default null,
408 p_attribute9 in varchar2 default null,
409 p_attribute10 in varchar2 default null,
410 p_attribute11 in varchar2 default null,
411 p_attribute12 in varchar2 default null,
412 p_attribute13 in varchar2 default null,
413 p_attribute14 in varchar2 default null,
414 p_attribute15 in varchar2 default null,
415 p_attribute16 in varchar2 default null,
416 p_attribute17 in varchar2 default null,
417 p_attribute18 in varchar2 default null,
418 p_attribute19 in varchar2 default null,
419 p_attribute20 in varchar2 default null,
420 p_effective_date in date,
421 p_job_id in number default null,
422 p_successee_person_id in number default null
423 ) is
424 --
425 l_rec per_suc_shd.g_rec_type;
426 l_proc varchar2(72) := g_package||'ins';
427 --
428 Begin
429 hr_utility.set_location('Entering:'||l_proc, 5);
430 --
431 -- Call conversion function to turn arguments into the
432 -- p_rec structure.
433 --
434 l_rec :=
435 per_suc_shd.convert_args
436 (
437 null,
438 p_person_id,
439 p_position_id,
440 p_business_group_id,
441 p_start_date,
442 p_time_scale,
443 p_end_date,
444 p_available_for_promotion,
445 p_manager_comments,
446 null,
447 p_attribute_category,
448 p_attribute1,
449 p_attribute2,
450 p_attribute3,
451 p_attribute4,
452 p_attribute5,
453 p_attribute6,
454 p_attribute7,
455 p_attribute8,
456 p_attribute9,
457 p_attribute10,
458 p_attribute11,
459 p_attribute12,
460 p_attribute13,
461 p_attribute14,
462 p_attribute15,
463 p_attribute16,
464 p_attribute17,
465 p_attribute18,
466 p_attribute19,
467 p_attribute20,
468 p_job_id,
469 p_successee_person_id
470 );
471 --
472 -- Having converted the arguments into the per_suc_rec
473 -- plsql record structure we call the corresponding record business process.
474 --
475 ins(l_rec,p_effective_date);
476 --
477 -- As the primary key argument(s)
478 -- are specified as an OUT's we must set these values.
479 --
480 p_succession_plan_id := l_rec.succession_plan_id;
481 p_object_version_number := l_rec.object_version_number;
482 --
483 hr_utility.set_location(' Leaving:'||l_proc, 10);
484 End ins;
485 --
486 end per_suc_ins;