1 PACKAGE per_suc_bus AUTHID CURRENT_USER AS
2 /* $Header: pesucrhi.pkh 120.3 2010/02/13 19:33:54 schowdhu ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-----------------------< chk_succession_plan_id >-------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description
10 -- This procedure is used to check that the primary key for the succession
11 -- planning table is created properly. It should be null on insert and
12 -- should not be able to be updated.
13 --
14 -- Pre Conditions
15 -- None.
16 --
17 -- In Parameters
18 -- succession_plan_id PK of record being inserted or updated.
19 -- object_version_number Object version number of record being
20 -- inserted or updated.
21 --
22 -- Post Success
23 -- Processing continues
24 --
25 -- Post Failure
26 -- Errors handled by the procedure
27 --
28 -- Access Status
29 -- Internal and External use.
30 --
31 -- {End Of Comments}
32 -- ----------------------------------------------------------------------------
33 PROCEDURE chk_succession_plan_id (
34 p_succession_plan_id IN NUMBER,
35 p_object_version_number IN NUMBER
36 );
37
38 --
39 -- ----------------------------------------------------------------------------
40 -- |----------------------------< chk_person_id >-----------------------------|
41 -- ----------------------------------------------------------------------------
42 -- {Start Of Comments}
43 --
44 -- Description
45 -- This procedure is used to check that the person_id exists as of the
46 -- effective date.
47 --
48 -- Pre Conditions
49 -- None.
50 --
51 -- In Parameters
52 -- effective_date effective date
53 -- succession_plan_id PK of record being inserted or updated.
54 -- person_id id of person being inserted.
55 -- object_version_number Object version number of record being
56 -- inserted or updated.
57 --
58 -- Post Success
59 -- Processing continues
60 --
61 -- Post Failure
62 -- Errors handled by the procedure
63 --
64 -- Access Status
65 -- Internal and External use.
66 --
67 -- {End Of Comments}
68 -- ----------------------------------------------------------------------------
69 PROCEDURE chk_person_id (
70 p_effective_date IN DATE,
71 p_succession_plan_id IN NUMBER,
72 p_person_id IN NUMBER,
73 p_object_version_number IN NUMBER
74 );
75
76 --
77 -- ----------------------------------------------------------------------------
78 -- |----------------------------< chk_position_id >---------------------------|
79 -- ----------------------------------------------------------------------------
80 -- {Start Of Comments}
81 --
82 -- Description
83 -- This procedure is used to check that the position_id exists as of
84 -- effective date.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- effective_date effective date
91 -- succession_plan_id PK of record being inserted or updated.
92 -- position_id id of position being inserted.
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 -- Post Success
96 -- Processing continues
97 --
98 -- Post Failure
99 -- Errors handled by the procedure
100 --
101 -- Access Status
102 -- Internal and External use.
103 --
104 -- {End Of Comments}
105 -- ----------------------------------------------------------------------------
106 PROCEDURE chk_position_id (
107 p_effective_date IN DATE,
111 );
108 p_succession_plan_id IN NUMBER,
109 p_position_id IN NUMBER,
110 p_object_version_number IN NUMBER
112
113 --
114 -- ----------------------------------------------------------------------------
115 -- |----------------------------< chk_successee >---------------------------|
116 -- ----------------------------------------------------------------------------
117 -- {Start Of Comments}
118 --
119 -- Description
120 -- This procedure is used to check that the successee_id(job_id or position_id
121 -- or successe_person_id) exists as of effective date.
122 --
123 -- Pre Conditions
124 -- None.
125 --
126 -- In Parameters
127 -- effective_date effective date
128 -- succession_plan_id PK of record being inserted or updated.
129 -- job_id id of job being inserted.
130 -- position_id id of position being inserted.
131 -- successe_person_id id of successee person being inserted.
132 -- object_version_number Object version number of record being
133 -- inserted or updated.
134
135 -- Post Success
136 -- Processing continues
137 --
138 -- Post Failure
139 -- Errors handled by the procedure
140 --
141 -- Access Status
142 -- Internal and External use.
143 --
144 -- {End Of Comments}
145 -- ----------------------------------------------------------------------------
146 PROCEDURE chk_successee (
147 p_effective_date IN DATE,
148 p_succession_plan_id IN NUMBER,
149 p_job_id IN NUMBER,
150 p_position_id IN NUMBER,
151 p_successee_person_id IN NUMBER,
152 p_object_version_number IN NUMBER
153 );
154
155 --
156 -- ----------------------------------------------------------------------------
157 -- |-----------------------------< chk_time_scale >---------------------------|
158 -- ----------------------------------------------------------------------------
159 -- {Start Of Comments}
160 --
161 -- Description
162 -- This procedure is used to check that the time_scale lookup falles within
163 -- the per_time_scales lookup.
164 --
165 -- Pre Conditions
166 -- None.
167 --
168 -- In Parameters
169 -- succession_plan_id PK of record being inserted or updated.
170 -- time_scale time_scale lookup.
171 -- object_version_number Object version number of record being
172 -- inserted or updated.
173 -- effective_date effective date
174 --
175 -- Post Success
176 -- Processing continues
177 --
178 -- Post Failure
179 -- Error handled by procedure
180 --
181 -- Access Status
182 -- Internal and External use.
183 --
184 -- {End Of Comments}
185 -- ----------------------------------------------------------------------------
186 PROCEDURE chk_time_scale (
187 p_succession_plan_id IN NUMBER,
188 p_time_scale IN VARCHAR2,
189 p_object_version_number IN NUMBER,
190 p_effective_date IN DATE
191 );
192
193 --
194 -- ----------------------------------------------------------------------------
195 -- |----------------------------< chk_start_date >----------------------------|
196 -- ----------------------------------------------------------------------------
197 -- {Start Of Comments}
198 --
199 -- Description
200 -- This procedure is used to check that the start_date has been populated.
201 --
202 -- Pre Conditions
203 -- None.
204 --
205 -- In Parameters
206 -- succession_plan_id PK of record being inserted or updated.
207 -- start_date start date of succession plan record.
208 -- object_version_number Object version number of record being
209 -- inserted or updated.
210 -- Post Success
211 -- Processing continues
212 --
213 -- Post Failure
214 -- Errors handled by the procedure
215 --
216 -- Access Status
217 -- Internal and External use.
218 --
219 -- {End Of Comments}
220 -- ----------------------------------------------------------------------------
221 PROCEDURE chk_start_date (
222 p_succession_plan_id IN NUMBER,
223 p_start_date IN DATE,
224 p_object_version_number IN NUMBER
225 );
226
227 --
228 -- ----------------------------------------------------------------------------
229 -- |----------------------------< chk_end_date >------------------------------|
230 -- ----------------------------------------------------------------------------
231 -- {Start Of Comments}
232 --
233 -- Description
234 -- This procedure is used to check that the end date is later than the
235 -- start date.
236 --
237 -- Pre Conditions
238 -- None.
239 --
240 -- In Parameters
241 -- succession_plan_id PK of record being inserted or updated.
242 -- start_date start date of succession plan record.
243 -- end_date end date of succession plan record.
244 -- object_version_number Object version number of record being
245 -- inserted or updated.
246 -- Post Success
247 -- Processing continues
248 --
249 -- Post Failure
250 -- Errors handled by the procedure
251 --
252 -- Access Status
256 -- ----------------------------------------------------------------------------
253 -- Internal and External use.
254 --
255 -- {End Of Comments}
257 PROCEDURE chk_end_date (
258 p_succession_plan_id IN NUMBER,
259 p_start_date IN DATE,
260 p_end_date IN DATE,
261 p_object_version_number IN NUMBER
262 );
263
264 --
265 -- ----------------------------------------------------------------------------
266 -- |----------------------< chk_available_for_promotion >---------------------|
267 -- ----------------------------------------------------------------------------
268 -- {Start Of Comments}
269 --
270 -- Description
271 -- This procedure is used to check that the available_for_promotion field
272 -- falls within the 'YES_NO' lookup.
273 --
274 -- Pre Conditions
275 -- None.
276 --
277 -- In Parameters
278 -- succession_plan_id PK of record being inserted or updated.
279 -- available_for_promotion available_for_promotion lookup.
280 -- object_version_number Object version number of record being
281 -- inserted or updated.
282 -- effective_date effective date
283 --
284 -- Post Success
285 -- Processing continues
286 --
287 -- Post Failure
288 -- Error handled by procedure
289 --
290 -- Access Status
291 -- Internal and External use.
292 --
293 -- {End Of Comments}
294 -- ----------------------------------------------------------------------------
295 PROCEDURE chk_available_for_promotion (
296 p_succession_plan_id IN NUMBER,
297 p_available_for_promotion IN VARCHAR2,
298 p_object_version_number IN NUMBER,
299 p_effective_date IN DATE
300 );
301
302 --
303 -- ----------------------------------------------------------------------------
304 -- |----------------------------< chk_date >----------------------------------|
305 -- ----------------------------------------------------------------------------
306 -- {Start Of Comments}
307 --
308 -- Description
309 -- This procedure is used to check that the record being inserted or
310 -- updated is unique.
311 --
312 -- Pre Conditions
313 -- None.
314 --
315 -- In Parameters
316 -- succession_plan_id PK of record being inserted or updated.
317 -- person_id id of person being inserted.
318 -- position_id id of position being inserted.
319 -- start_date start date of succession plan record
320 -- end_date end date of succession plan record
321 -- object_version_number Object version number of record being
322 -- inserted or updated.
323 -- Post Success
324 -- Processing continues
325 --
326 -- Post Failure
327 -- Errors handled by the procedure
328 --
329 -- Access Status
330 -- Internal and External use.
331 --
332 -- {End Of Comments}
333 -- ----------------------------------------------------------------------------
334 PROCEDURE chk_date (
335 p_succession_plan_id IN NUMBER,
336 p_position_id IN NUMBER,
337 p_person_id IN NUMBER,
338 p_start_date IN DATE,
339 p_end_date IN DATE,
340 p_object_version_number IN NUMBER,
341 p_job_id IN NUMBER,
342 p_successee_person_id IN NUMBER
343 );
344
345 --
346 -- ----------------------------------------------------------------------------
347 -- |---------------------------< insert_validate >----------------------------|
348 -- ----------------------------------------------------------------------------
349 -- {Start Of Comments}
350 --
351 -- Description:
352 -- This procedure controls the execution of all insert business rules
353 -- validation.
354 --
355 -- Prerequisites:
356 -- This private procedure is called from ins procedure.
357 --
358 -- In Parameters:
359 -- A Pl/Sql record structre.
360 --
361 -- Post Success:
362 -- Processing continues.
363 --
364 -- Post Failure:
365 -- If a business rules fails the error will not be handled by this procedure
366 -- unless explicity coded.
367 --
368 -- Developer Implementation Notes:
369 -- For insert, your business rules should be executed from this procedure and
370 -- should ideally (unless really necessary) just be straight procedure or
371 -- function calls. Try and avoid using conditional branching logic.
372 --
373 -- Access Status:
374 -- Internal Row Handler Use Only.
375 --
376 -- {End Of Comments}
377 -- ----------------------------------------------------------------------------
378 PROCEDURE insert_validate (p_rec IN per_suc_shd.g_rec_type, p_effective_date IN DATE);
379
380 --
381 -- ----------------------------------------------------------------------------
382 -- |---------------------------< update_validate >----------------------------|
383 -- ----------------------------------------------------------------------------
384 -- {Start Of Comments}
385 --
386 -- Description:
387 -- This procedure controls the execution of all update business rules
388 -- validation.
389 --
390 -- Prerequisites:
391 -- This private procedure is called from upd procedure.
392 --
393 -- In Parameters:
394 -- A Pl/Sql record structre.
395 --
396 -- Post Success:
397 -- Processing continues.
398 --
399 -- Post Failure:
400 -- If a business rules fails the error will not be handled by this procedure
401 -- unless explicity coded.
402 --
403 -- Developer Implementation Notes:
404 -- For update, your business rules should be executed from this procedure and
405 -- should ideally (unless really necessary) just be straight procedure or
406 -- function calls. Try and avoid using conditional branching logic.
407 --
408 -- Access Status:
409 -- Internal Row Handler Use Only.
410 --
411 -- {End Of Comments}
412 -- ----------------------------------------------------------------------------
413 PROCEDURE update_validate (p_rec IN per_suc_shd.g_rec_type, p_effective_date IN DATE);
414
415 --
416 -- ----------------------------------------------------------------------------
417 -- |---------------------------< delete_validate >----------------------------|
418 -- ----------------------------------------------------------------------------
419 -- {Start Of Comments}
420 --
421 -- Description:
422 -- This procedure controls the execution of all delete business rules
423 -- validation.
424 --
425 -- Prerequisites:
426 -- This private procedure is called from del procedure.
427 --
428 -- In Parameters:
429 -- A Pl/Sql record structre.
430 --
431 -- Post Success:
432 -- Processing continues.
433 --
434 -- Post Failure:
435 -- If a business rules fails the error will not be handled by this procedure
436 -- unless explicity coded.
437 --
438 -- Developer Implementation Notes:
439 -- For delete, your business rules should be executed from this procedure and
440 -- should ideally (unless really necessary) just be straight procedure or
441 -- function calls. Try and avoid using conditional branching logic.
442 --
443 -- Access Status:
444 -- Internal Row Handler Use Only.
445 --
446 -- {End Of Comments}
447 -- ----------------------------------------------------------------------------
448 PROCEDURE delete_validate (p_rec IN per_suc_shd.g_rec_type);
449
450 --
451
452 ---- Bug#3207986 start
453 PROCEDURE chk_person_start_date (
454 p_person_id per_people_f.person_id%TYPE,
455 p_start_date per_assignments_f.effective_start_date%TYPE
456 );
457 ---- Bug#3207986 end
458 END per_suc_bus;