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