[Home] [Help]
PACKAGE BODY: APPS.GHR_PDC_BUS
Source
1 Package Body ghr_pdc_bus as
2 /* $Header: ghpdcrhi.pkb 115.6 1999/11/09 22:43:48 pkm ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_pdc_bus.'; -- Global package name
9 --
10
11 --
12 ----------------------------<chk_non_updateable_args>----------------------
13 --
14
15 PROCEDURE CHK_NON_UPDATEABLE_ARGS(p_rec IN ghr_pdc_shd.g_rec_type)
16
17 IS
18
19 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
20 l_error exception;
21 l_argument varchar2(30);
22
23
24 BEGIN
25
26 --
27
28 hr_utility.set_location('Entering:'|| l_proc,10);
29
30 --
31 -- Only proceed with the validation if the row exists for
32 -- the current record in the HR schema.
33 --
34
35 IF not ghr_pdc_shd.api_updating
36 (p_pd_classification_id => p_rec.pd_classification_id,
37 p_object_version_number => p_rec.object_version_number)
38 THEN
39
40 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
41 hr_utility.set_message_token('PROCEDURE',l_proc);
42 hr_utility.set_message_token('STEP','20');
43 END IF;
44
45 --
46 hr_utility.set_location (l_proc, 30);
47 --
48
49 IF NVL(p_rec.position_description_id, hr_api.g_number)
50 <> NVL(ghr_pdc_shd.g_old_rec.position_description_id, hr_api.g_number)
51 THEN
52 l_argument := 'position_description_id';
53 RAISE l_error;
54
55 END IF;
56
57 -- Check with functionals and verify.
58
59
60
61
62 IF NVL(p_rec.class_grade_by, hr_api.g_varchar2)
63 <> NVL(ghr_pdc_shd.g_old_rec.class_grade_by,hr_api.g_varchar2)
64 THEN
65
66 l_argument := 'class_grade_by';
67 RAISE l_error;
68 END IF;
69
70 /* The following lines are commented as we have not completely defined the business rules for classification/certification information. This information cannot be changed once the PD has been classified. */
71
72
73 /* IF NVL(p_rec.official_title, hr_api.g_varchar2)
74 <> NVL(ghr_pdc_shd.g_old_rec.official_title,hr_api.g_varchar2)
75 THEN
76
77 l_argument := 'official_title';
78 RAISE l_error;
79 END IF;
80
81 IF NVL(p_rec.pay_plan, hr_api.g_varchar2)
82 <> NVL(ghr_pdc_shd.g_old_rec.pay_plan,hr_api.g_varchar2)
83 THEN
84
85 l_argument := 'pay_plan';
86 RAISE l_error;
87 END IF;
88
89 IF NVL(p_rec.occupational_code, hr_api.g_varchar2)
90 <> NVL(ghr_pdc_shd.g_old_rec.occupational_code,hr_api.g_varchar2)
91 THEN
92
93 l_argument := 'occupational_code';
94 RAISE l_error;
95 END IF;
96
97 IF NVL(p_rec.grade_level, hr_api.g_varchar2)
98 <> NVL(ghr_pdc_shd.g_old_rec.grade_level,hr_api.g_varchar2)
99 THEN
100
101 l_argument := 'grade_level';
102 RAISE l_error;
103
104 END IF;
105 */
106
107
108 --
109
110 --
111 hr_utility.set_location('Leaving :' || l_proc, 40);
112
113 EXCEPTION
114
115 WHEN l_error THEN
116
117 hr_api.argument_changed_error
118
119 (p_api_name => l_proc,
120 p_argument => l_argument);
121
122 WHEN OTHERS THEN
123
124 RAISE;
125 end chk_non_updateable_args;
126
127 --
128 -----------------------------<chk_grade_level>------------------------------
129 --
130 --
131
132 PROCEDURE chk_grade_level(p_pd_classification_id IN
133 ghr_pd_classifications.pd_classification_id%TYPE,
134 p_grade_level IN ghr_pd_classifications.grade_level%TYPE,
135 p_effective_date IN date,
136 p_object_version_number IN number)
137
138 IS
139
140 l_proc varchar2(72) := g_package||'chk_grade_level';
141 l_api_updating boolean;
142
143
144 BEGIN
145
146 hr_utility.set_location('Entering: '|| l_proc, 10);
147
148 -- Check Mandatory Parameters are set
149
150 hr_api.mandatory_arg_error
151 (p_api_name => l_proc,
152 p_argument => 'effective date',
153 p_argument_value => p_effective_date
154 );
155
156 --
157 -- Only Proceed with the validation if:
158 --
159 -- a) The current g_old_rec is current and
160 -- b) The grade_level value has changed
161 -- c) A record is being inserted.
162 --
163
164 l_api_updating := ghr_pdc_shd.api_updating(
165 p_pd_classification_id => p_pd_classification_id,
166 p_object_version_number => p_object_version_number);
167
168 IF (l_api_updating
169 AND
170 ((nvl(ghr_pdc_shd.g_old_rec.grade_level,hr_api.g_varchar2)
171 <> nvl(p_grade_level,hr_api.g_varchar2))))
172 OR
173 NOT l_api_updating THEN
174
175 --
176 hr_utility.set_location(l_proc,20);
177 --
178 -- If grade level is not null then
179 -- Check if the grade level value exists in fnd_lookups
180 -- Where the look up type is 'GHR_US_GRADE_OR_LEVEL'
181 --
182
183 IF p_grade_level is NOT NULL THEN
184
185 IF hr_api.not_exists_in_hr_lookups
186 (p_effective_date => p_effective_date,
187 p_lookup_type => 'GHR_US_GRADE_OR_LEVEL',
188 p_lookup_code => p_grade_level
189 ) THEN
190
191 -- Error: Invalid Subject to IA Action
192
193 -- New Message
194
195 hr_utility.set_message(8301,'GHR_INVALID_GRADE_OR_LEVEL');
196 hr_utility.raise_error;
197 END IF;
198 hr_utility.set_location(l_proc,30);
199
200 END IF;
201
202 END IF;
203
204 hr_utility.set_location('Leaving: '|| l_proc, 40);
205
206 END chk_grade_level;
207
208 --
209 ----------------------------------------------------------<chk_pay_plan>------------------------------------------------------
210 --
211
212
213 PROCEDURE chk_pay_plan(p_pd_classification_id IN
214 ghr_pd_classifications.pd_classification_id%TYPE,
215 p_pay_plan IN ghr_pd_classifications.pay_plan%TYPE,
216 p_effective_date IN date,
217 p_object_version_number IN number)
218
219 IS
220
221 l_proc varchar2(72) := g_package||'chk_pay_plan';
222 l_api_updating boolean;
223 l_pay_plan ghr_pay_plans.pay_plan%TYPE;
224
225 CURSOR c_pay_plan
226 IS
227 SELECT pay_plan
228 FROM ghr_pay_plans
229 WHERE pay_plan = p_pay_plan;
230
231
232 BEGIN
233
234 hr_utility.set_location('Entering: '|| l_proc, 10);
235
236 -- Check Mandatory Parameters are set
237
238 hr_api.mandatory_arg_error
239 (p_api_name => l_proc,
240 p_argument => 'effective date',
241 p_argument_value => p_effective_date
242 );
243
244 --
245 -- Only Proceed with the validation if:
246 --
247 -- a) The current g_old_rec is current and
248 -- b) The grade_level value has changed
249 -- c) A record is being inserted.
250 --
251
252 l_api_updating := ghr_pdc_shd.api_updating(
253 p_pd_classification_id => p_pd_classification_id,
254 p_object_version_number => p_object_version_number);
255
256 IF (l_api_updating
257 AND
258 ((nvl(ghr_pdc_shd.g_old_rec.pay_plan,hr_api.g_varchar2)
259 <> nvl(p_pay_plan,hr_api.g_varchar2))))
260 OR
261 NOT l_api_updating THEN
262
263 --
264 hr_utility.set_location(l_proc,20);
265 --
266 -- If pay plan is not null then
267 -- Check if the pay plan is in GHR_PAY_PLANS
268 -- This is an extra check apart from the foreign key on the table.
269 --
270
271 IF p_pay_plan is NOT NULL THEN
272
273 OPEN c_pay_plan;
274 FETCH c_pay_plan INTO l_pay_plan;
275 IF c_pay_plan%NOTFOUND THEN
276
277 hr_utility.set_message(8301,'GHR_INVALID_PAY_PLAN');
278 hr_utility.raise_error;
279 END IF;
280 CLOSE c_pay_plan;
281
282 hr_utility.set_location(l_proc,30);
283
284 END IF;
285
286 END IF;
287
288 hr_utility.set_location('Leaving: '|| l_proc, 40);
289
290 END chk_pay_plan;
291
292 --
293 -------------------------------------------------<chk_occupational_code>-----------------------------------------------
294 --
295
296
297 PROCEDURE chk_occupational_code(p_pd_classification_id IN
298 ghr_pd_classifications.pd_classification_id%TYPE,
299 p_occupational_code IN ghr_pd_classifications.occupational_code%TYPE,
300 p_effective_date IN date,
301 p_object_version_number IN number)
302
303 IS
304
305 l_proc varchar2(72) := g_package||'chk_occupational_code';
306 l_api_updating boolean;
307
308
309 BEGIN
310
311
312
313 hr_utility.set_location('Entering: '|| l_proc, 10);
314
315 -- Check Mandatory Parameters are set
316
317 hr_api.mandatory_arg_error
318 (p_api_name => l_proc,
319 p_argument => 'effective date',
320 p_argument_value => p_effective_date
321 );
322
323 --
324 -- Only Proceed with the validation if:
325 --
326 -- a) The current g_old_rec is current and
327 -- b) The grade_level value has changed
328 -- c) A record is being inserted.
329 --
330
331 l_api_updating := ghr_pdc_shd.api_updating(
332 p_pd_classification_id => p_pd_classification_id,
333 p_object_version_number => p_object_version_number);
334
335 IF (l_api_updating
336 AND
337 ((nvl(ghr_pdc_shd.g_old_rec.occupational_code,hr_api.g_varchar2)
338 <> nvl(p_occupational_code,hr_api.g_varchar2))))
339 OR
340 NOT l_api_updating THEN
341
342 --
343 hr_utility.set_location(l_proc,20);
344 --
345 -- If occupational code is not null then
346 -- Check if the occupational code value exists in fnd_lookups
347 -- Where the look up type is 'GHR_US_OCC_SERIES'
348 --
349
350 IF p_occupational_code is NOT NULL THEN
351
352 IF hr_api.not_exists_in_hr_lookups
353 (p_effective_date => p_effective_date,
354 p_lookup_type => 'GHR_US_OCC_SERIES',
355 p_lookup_code => p_occupational_code
356 ) THEN
357
358 -- Error: Invalid Occupational Code
359
360 hr_utility.set_message(8301,'GHR_OCCUPATIONAL_CODE_INVALID');
361 hr_utility.raise_error;
362 END IF;
363 hr_utility.set_location(l_proc,30);
364
365 END IF;
366
367 END IF;
368
369 hr_utility.set_location('Leaving: '|| l_proc, 40);
370
371
372 END CHK_OCCUPATIONAL_CODE;
373
374
375 PROCEDURE CHK_CLASS_GRADE_BY(p_pd_classification_id IN
376 ghr_pd_classifications.pd_classification_id%TYPE,
377 p_class_grade_by IN ghr_pd_classifications.class_grade_by%TYPE,
378 p_effective_date IN date,
379 p_object_version_number IN number)
380
381 IS
382
383 l_proc varchar2(72) := g_package||'chk_class_grade_by';
384 l_api_updating boolean;
385
386
387 BEGIN
388
389
390 hr_utility.set_location('Entering: '|| l_proc, 10);
391
392 -- Check Mandatory Parameters are set
393
394 hr_api.mandatory_arg_error
395 (p_api_name => l_proc,
396 p_argument => 'effective date',
397 p_argument_value => p_effective_date
398 );
399
400
401 --
402 -- Only Proceed with the validation if:
403 --
404 -- a) The current g_old_rec is current and
405 -- b) The class_grade_by value has changed
406 -- c) A record is being inserted.
407 --
408
409 l_api_updating := ghr_pdc_shd.api_updating(
410 p_pd_classification_id => p_pd_classification_id,
411 p_object_version_number => p_object_version_number);
412
413
414 IF (l_api_updating
415 AND
416 ((nvl(ghr_pdc_shd.g_old_rec.class_grade_by,hr_api.g_varchar2)
417 <> nvl(p_class_grade_by,hr_api.g_varchar2))))
418 OR
419 NOT l_api_updating THEN
420
421
422 hr_utility.set_location(l_proc,20);
423 --
424 -- If class_grade_by is not null then
425 -- Check if the grade level value exists in fnd_lookups
426 -- Where the look up type is 'GHR_CLASS_GRADE_BY'
427 --
428
429 IF p_class_grade_by is NOT NULL THEN
430
431
432 IF hr_api.not_exists_in_hr_lookups
433 (p_effective_date => p_effective_date,
434 p_lookup_type => 'GHR_US_CLASS_GRADE_BY',
435 p_lookup_code => p_class_grade_by
436 ) THEN
437
438
439 hr_utility.set_message(8301,'GHR_CLASS_GRADE_BY_INVALID');
440 hr_utility.raise_error;
441
442 END IF;
443 hr_utility.set_location(l_proc,30);
444
445 END IF;
446
447 END IF;
448
449 hr_utility.set_location('Leaving: '|| l_proc, 40);
450
451
452 END CHK_CLASS_GRADE_BY;
453
454
455 -- ----------------------------------------------------------------------------
456 -- |---------------------------< insert_validate >----------------------------|
457 -- ----------------------------------------------------------------------------
458 Procedure insert_validate(p_rec in ghr_pdc_shd.g_rec_type) is
459 --
460 l_proc varchar2(72) := g_package||'insert_validate';
461 l_effective_date date ;
462 --
463 Begin
464
465
466 hr_utility.set_location('Entering:'||l_proc, 5);
467 --
468 -- Set up the CLIENT_INFO
469 --
473 --
470 ghr_utility.set_client_info;
471 --
472 -- Call all supporting business operations
474 --
475 --
476 -- PLEASE VERIFY: Inserted by Dinkar. Karumuri
477
478 SELECT sysdate
479 INTO l_effective_date
480 FROM dual;
481
482
483 -- p_effective_date is currently date_from. Verify it.
484
485 chk_class_grade_by(p_pd_classification_id => p_rec.pd_classification_id,
486 p_class_grade_by => p_rec.class_grade_by,
487 p_effective_date => l_effective_date,
488 p_object_version_number => p_rec.object_version_number);
489
490 chk_occupational_code(p_pd_classification_id => p_rec.pd_classification_id,
491 p_occupational_code => p_rec.occupational_code,
492 p_effective_date => l_effective_date,
493 p_object_version_number => p_rec.object_version_number);
494
495 chk_pay_plan(p_pd_classification_id => p_rec.pd_classification_id,
496 p_pay_plan => p_rec.pay_plan,
497 p_effective_date => l_effective_date,
498 p_object_version_number => p_rec.object_version_number);
499
500 chk_grade_level(p_pd_classification_id => p_rec.pd_classification_id,
501 p_grade_level => p_rec.grade_level,
502 p_effective_date => l_effective_date,
503 p_object_version_number => p_rec.object_version_number);
504
505
506 hr_utility.set_location(' Leaving:'||l_proc, 10);
507 End insert_validate;
508 --
509 -- ----------------------------------------------------------------------------
510 -- |---------------------------< update_validate >----------------------------|
511 -- ----------------------------------------------------------------------------
512 Procedure update_validate(p_rec in ghr_pdc_shd.g_rec_type) is
513 --
514 l_proc varchar2(72) := g_package||'update_validate';
515 l_effective_date date;
516 --
517 Begin
518
519 select sysdate
520 into l_effective_date
521 from dual;
522
523 hr_utility.set_location('Entering:'||l_proc, 5);
524 --
525 -- Set up the CLIENT_INFO
526 --
527 ghr_utility.set_client_info;
528 --
529 -- Call all supporting business operations
530 --
531 --
532 --
533
534 chk_non_updateable_args(p_rec);
535
536 -- Please VERIFY Inserted by Dinkar. Karumuri
537
538 chk_class_grade_by(p_pd_classification_id => p_rec.pd_classification_id,
539 p_class_grade_by => p_rec.class_grade_by,
540 p_effective_date => l_effective_date,
541 p_object_version_number => p_rec.object_version_number);
542
543 chk_occupational_code(p_pd_classification_id => p_rec.pd_classification_id,
544 p_occupational_code => p_rec.occupational_code,
545 p_effective_date => l_effective_date,
546 p_object_version_number => p_rec.object_version_number);
547
548 chk_pay_plan(p_pd_classification_id => p_rec.pd_classification_id,
549 p_pay_plan => p_rec.pay_plan,
550 p_effective_date => l_effective_date,
551 p_object_version_number => p_rec.object_version_number);
552
553 chk_grade_level(p_pd_classification_id => p_rec.pd_classification_id,
554 p_grade_level => p_rec.grade_level,
555 p_effective_date => l_effective_date,
556 p_object_version_number => p_rec.object_version_number);
557
558 hr_utility.set_location(' Leaving:'||l_proc, 10);
559 End update_validate;
560 --
561 -- ----------------------------------------------------------------------------
562 -- |---------------------------< delete_validate >----------------------------|
563 -- ----------------------------------------------------------------------------
564 Procedure delete_validate(p_rec in ghr_pdc_shd.g_rec_type) is
565 --
566 l_proc varchar2(72) := g_package||'delete_validate';
567 --
568 Begin
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 -- Call all supporting business operations
572 --
573 hr_utility.set_location(' Leaving:'||l_proc, 10);
574 End delete_validate;
575 --
576 end ghr_pdc_bus;