1 Package Body ghr_dut_bus as
2 /* $Header: ghdutrhi.pkb 120.0.12000000.1 2007/01/18 13:42:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_dut_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------------< chk_duty_station_id >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- duty_station_id -- PK of record being inserted or updated.
24 -- effective_date -- Effective Date of session
25 -- object_version_number -- Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_duty_station_id(p_duty_station_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_duty_station_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ghr_dut_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_duty_station_id => p_duty_station_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_duty_station_id,hr_api.g_number)
55 <> ghr_dut_shd.g_old_rec.duty_station_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ghr_dut_shd.constraint_error('GHR_DUTY_STATIONS_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_duty_station_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ghr_dut_shd.constraint_error('GHR_DUTY_STATIONS_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_duty_station_id;
78 --
79 --
80 -- ----------------------------------------------------------------------------
81 -- |-----------------------------< chk_duty_station_code >-----------------------------|
82 -- ----------------------------------------------------------------------------
83 --
84 -- Description
85 -- This procedure is used to check that the unique key for the table
86 -- is created properly.
87 --
88 -- Pre Conditions
89 -- None.
90 --
91 -- In Parameters
92 -- duty_station_id -- PK of record being inserted or updated.
93 -- duty_station_code -- Unique Key of the record being inserted or updated
94 -- effective_date -- Effective Date of session
95 -- object_version_number -- Object version number of record being
96 -- inserted or updated.
97 -- Post Success
98 -- Processing continues
99 --
100 -- Post Failure
101 -- Errors handled by the procedure
102 --
103 -- Access Status
104 -- Internal table handler use only.
105 --
106 Procedure chk_duty_station_code(p_duty_station_id in number,
107 p_duty_station_code in varchar2,
108 p_effective_date in date,
109 p_object_version_number in number) is
110 --
111 l_proc varchar2(72) := g_package||'chk_duty_station_code';
112 l_api_updating boolean;
113 l_dummy_flag varchar2(1);
114
115 CURSOR c_duty_station_exists is
116 SELECT '1'
117 FROM ghr_duty_stations_f
118 WHERE duty_station_code = p_duty_station_code
119 AND p_effective_date between effective_start_date
120 and effective_end_date;
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'||l_proc, 5);
125 --
126 l_api_updating := ghr_dut_shd.api_updating
127 (p_effective_date => p_effective_date,
128 p_duty_station_id => p_duty_station_id,
129 p_object_version_number => p_object_version_number);
130 --
131 if (l_api_updating
132 and nvl(p_duty_station_code,hr_api.g_number)
133 <> ghr_dut_shd.g_old_rec.duty_station_code) then
134 --
135 -- raise error as UK has changed
136 --
137 ghr_dut_shd.constraint_error('GHR_DUTY_STATIONS_F_UK');
138 --
139 elsif not l_api_updating then
140 --
141 -- check if duty_station_code already exists
142 --
143 open c_duty_station_exists;
144 fetch c_duty_station_exists into l_dummy_flag;
145 close c_duty_station_exists;
146 IF l_dummy_flag = '1' Then
147 ghr_dut_shd.constraint_error('GHR_DUTY_STATIONS_F_UK');
148 End If;
149 --
150 -- check for cpdf edit#120.00.1. If first 2 characters of dutystation code are alphabets,
151 -- then last 3 characters should be zeroes(000)
152 --
153 IF(
154 SUBSTR(p_duty_station_code,1,1) not in ('0','1','2','3','4','5','6','7','8','9')
155 AND
156 SUBSTR(p_duty_station_code,2,1) not in ('0','1','2','3','4','5','6','7','8','9')
157 ) AND
158 SUBSTR(p_duty_station_code,-3,3) <> '000' then
159 hr_utility.set_message(8301, 'GHR_38829_INVALID_DUTYSTN_CODE');
160 hr_utility.raise_error;
161 end if;
162
163 end if;
164 --
165 hr_utility.set_location('Leaving:'||l_proc, 10);
166 --
167 End chk_duty_station_code;
168 --
169 ----------------------------------------------------------------------------
170 -- |-----------------------------< chk_duty_station_flag >-----------------------------|
171 -- ----------------------------------------------------------------------------
172 --
173 -- Description
174 -- This procedure is used to check that the Duty Station flag is set properly
175 -- or not. If the first two positions of the Duty Station code are numbers,
176 -- and positions 3 through 9 are all zeroes, then the Duty Station Indicator must be "N".
177 --
178 -- Pre Conditions
179 -- None.
180 --
181 -- In Parameters
182 -- duty_station_id PK of record being inserted or updated.
183 -- duty_station_code Duty Station Code of the record
184 -- is_duty_station Duty Station flag to be checked
185 -- effective_date Effective Date of session
186 -- object_version_number Object version number of record being
187 -- inserted or updated.
188 --
189 -- Post Success
190 -- Processing continues
191 --
192 -- Post Failure
193 -- Errors handled by the procedure
194 --
195 -- Access Status
196 -- Internal table handler use only.
197 --
198 Procedure chk_duty_station_flag(p_duty_station_id in number,
199 p_is_duty_station in varchar2,
200 p_duty_station_code in varchar2,
201 p_effective_date in date,
202 p_object_version_number in number) is
203 --
204 l_proc varchar2(72) := g_package||'chk_duty_station_flag';
205 l_api_updating boolean;
206 --
207 Begin
208 --
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 --
211 l_api_updating := ghr_dut_shd.api_updating
212 (p_effective_date => p_effective_date,
213 p_duty_station_id => p_duty_station_id,
214 p_object_version_number => p_object_version_number);
215 --
216 if ( (l_api_updating and
217 nvl(p_is_duty_station,hr_api.g_varchar2) <> ghr_dut_shd.g_old_rec.is_duty_station)
218 OR not l_api_updating
219 ) then
220 --
221 IF p_is_duty_station = 'Y'
222 and substr(p_duty_station_code,1,1) IN ('0','1','2','3','4','5','6','7','8','9')
223 and substr(p_duty_station_code,2,1) IN ('0','1','2','3','4','5','6','7','8','9')
224 and substr(p_duty_station_code,3,7) = '0000000'
225 THEN
226 fnd_message.set_name('GHR','GHR_38821_INVALID_DUTY_STN_IND');
227 fnd_message.raise_error;
228 end if;
229
230 end if;
231 --
232 hr_utility.set_location('Leaving:'||l_proc, 10);
233 --
234 End chk_duty_station_flag;
235 --
236 -- --------------------------------------------------------------------------
237 --|-----------------------------< chk_active_assignments >-------------------|
238 -- --------------------------------------------------------------------------
239 --
240 -- Description
241 -- This procedure is used to check the active assignments with the
242 -- duty station code as on the effective date when user wants to end date
243 -- a duty station. If any assignment exists, user will get the error message
244 -- and process will be terminated.
245 -- Pre Conditions
246 -- None.
247 --
248 -- In Parameters
249 -- duty_station_code -- Duty station Code to be deleted
250 -- effective_date -- Effective Date of session
251 --
252 -- Post Success
253 -- Processing continues
254 --
255 -- Post Failure
256 -- Errors handled by the procedure
257 --
258 -- Access Status
259 -- Internal table handler use only.
260 --
261 procedure chk_active_assignments(p_duty_station_id IN VARCHAR2,
262 p_effective_date IN Date
263 ) IS
264 l_count NUMBER := 0;
265
266 Cursor c_assgnments is
267 select '1'
268 from per_assignments_f paf,hr_location_extra_info hrle
269 where paf.location_id = hrle.location_id
270 and paf.assignment_type = 'E'
271 and p_effective_date between paf.effective_start_date and paf.effective_end_date
272 and hrle.lei_information_category = 'GHR_US_LOC_INFORMATION'
273 and hrle. lei_information3 = p_duty_station_id;
274 Begin
275 FOR c_assgnments_rec IN c_assgnments
276 LOOP
277 l_count := 1;
278 EXIT;
279 END LOOP;
280 IF l_count > 0 then
281 fnd_message.set_name('GHR','GHR_38820_DUT_STN_HAS_ASGNMNT');
282 fnd_message.raise_error;
283 END IF;
284 End chk_active_assignments;
285 --
286 -- ----------------------------------------------------------------------------
287 -- |--------------------------< dt_update_validate >--------------------------|
288 -- ----------------------------------------------------------------------------
289 -- {Start Of Comments}
290 --
291 -- Description:
292 -- This procedure is used for referential integrity of datetracked
293 -- parent entities when a datetrack update operation is taking place
294 -- and where there is no cascading of update defined for this entity.
295 --
296 -- Prerequisites:
297 -- This procedure is called from the update_validate.
298 --
299 -- In Parameters:
300 --
301 -- Post Success:
302 -- Processing continues.
303 --
304 -- Post Failure:
305 --
306 -- Developer Implementation Notes:
307 -- This procedure should not need maintenance unless the HR Schema model
308 -- changes.
309 --
310 -- Access Status:
311 -- Internal Row Handler Use Only.
312 --
313 -- {End Of Comments}
314 -- ----------------------------------------------------------------------------
315 Procedure dt_update_validate
316 (p_datetrack_mode in varchar2,
317 p_validation_start_date in date,
318 p_validation_end_date in date) Is
319 --
320 l_proc varchar2(72) := g_package||'dt_update_validate';
321 l_integrity_error Exception;
322 l_table_name all_tables.table_name%TYPE;
323 --
324 Begin
325 hr_utility.set_location('Entering:'||l_proc, 5);
326 --
327 -- Ensure that the p_datetrack_mode argument is not null
328 --
329 hr_api.mandatory_arg_error
330 (p_api_name => l_proc,
331 p_argument => 'datetrack_mode',
332 p_argument_value => p_datetrack_mode);
333 --
334 -- Only perform the validation if the datetrack update mode is valid
335 --
336 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
337 --
338 --
339 -- Ensure the arguments are not null
340 --
341 hr_api.mandatory_arg_error
342 (p_api_name => l_proc,
343 p_argument => 'validation_start_date',
344 p_argument_value => p_validation_start_date);
345 --
346 hr_api.mandatory_arg_error
350 --
347 (p_api_name => l_proc,
348 p_argument => 'validation_end_date',
349 p_argument_value => p_validation_end_date);
351 --
352 End If;
353 --
354 hr_utility.set_location(' Leaving:'||l_proc, 10);
355 Exception
356 When Others Then
357 --
358 -- An unhandled or unexpected error has occurred which
359 -- we must report
360 --
361 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
362 fnd_message.set_token('PROCEDURE', l_proc);
363 fnd_message.set_token('STEP','15');
364 fnd_message.raise_error;
365 End dt_update_validate;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |--------------------------< dt_delete_validate >--------------------------|
369 -- ----------------------------------------------------------------------------
370 -- {Start Of Comments}
371 --
372 -- Description:
373 -- This procedure is used for referential integrity of datetracked
374 -- child entities when either a datetrack DELETE or ZAP is in operation
375 -- and where there is no cascading of delete defined for this entity.
376 -- For the datetrack mode of DELETE or ZAP we must ensure that no
377 -- datetracked child rows exist between the validation start and end
378 -- dates.
379 --
380 -- Prerequisites:
381 -- This procedure is called from the delete_validate.
382 --
383 -- In Parameters:
384 --
385 -- Post Success:
386 -- Processing continues.
387 --
388 -- Post Failure:
389 -- If a row exists by determining the returning Boolean value from the
390 -- generic dt_api.rows_exist function then we must supply an error via
391 -- the use of the local exception handler l_rows_exist.
392 --
393 -- Developer Implementation Notes:
394 -- This procedure should not need maintenance unless the HR Schema model
395 -- changes.
396 --
397 -- Access Status:
398 -- Internal Row Handler Use Only.
399 --
400 -- {End Of Comments}
401 -- ----------------------------------------------------------------------------
402 Procedure dt_delete_validate
403 (p_duty_station_id in number,
404 p_datetrack_mode in varchar2,
405 p_validation_start_date in date,
406 p_validation_end_date in date) Is
407 --
408 l_proc varchar2(72) := g_package||'dt_delete_validate';
409 l_rows_exist Exception;
410 l_table_name all_tables.table_name%TYPE;
411 --
412 Begin
413 hr_utility.set_location('Entering:'||l_proc, 5);
414 --
415 -- Ensure that the p_datetrack_mode argument is not null
416 --
417 hr_api.mandatory_arg_error
418 (p_api_name => l_proc,
419 p_argument => 'datetrack_mode',
420 p_argument_value => p_datetrack_mode);
421 --
422 -- Only perform the validation if the datetrack mode is either
423 -- DELETE or ZAP
424 --
425 If (p_datetrack_mode = 'DELETE') then
426 --
427 --
431 (p_api_name => l_proc,
428 -- Ensure the arguments are not null
429 --
430 hr_api.mandatory_arg_error
432 p_argument => 'validation_start_date',
433 p_argument_value => p_validation_start_date);
434 --
435 hr_api.mandatory_arg_error
436 (p_api_name => l_proc,
437 p_argument => 'validation_end_date',
438 p_argument_value => p_validation_end_date);
439 --
440 hr_api.mandatory_arg_error
441 (p_api_name => l_proc,
442 p_argument => 'duty_station_id',
443 p_argument_value => p_duty_station_id);
444 --
445 End If;
446 --
447 hr_utility.set_location(' Leaving:'||l_proc, 10);
448 Exception
449 When l_rows_exist Then
450 --
451 -- A referential integrity check was violated therefore
452 -- we must error
453 --
454 fnd_message.set_name('GHR','GHR_38819_DUTY_STATION_EXISTS');
455 fnd_message.raise_error;
456 When Others Then
457 --
458 -- An unhandled or unexpected error has occurred which
459 -- we must report
460 --
461 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
462 fnd_message.set_token('PROCEDURE', l_proc);
463 fnd_message.set_token('STEP','15');
464 fnd_message.raise_error;
465 End dt_delete_validate;
466 --
467 -- ----------------------------------------------------------------------------
468 -- |---------------------------< insert_validate >----------------------------|
469 -- ----------------------------------------------------------------------------
470 Procedure insert_validate
471 (p_rec in ghr_dut_shd.g_rec_type,
472 p_effective_date in date,
473 p_datetrack_mode in varchar2,
474 p_validation_start_date in date,
475 p_validation_end_date in date) is
476 --
477 l_proc varchar2(72) := g_package||'insert_validate';
478 --
479 Begin
480 hr_utility.set_location('Entering:'||l_proc, 5);
481 --
482 chk_duty_station_id
483 (p_duty_station_id => p_rec.duty_station_id,
484 p_effective_date => p_effective_date,
485 p_object_version_number => p_rec.object_version_number);
486 --
487 chk_duty_station_code
488 (p_duty_station_id => p_rec.duty_station_id,
489 p_duty_station_code => p_rec.duty_station_code,
490 p_effective_date => p_effective_date,
491 p_object_version_number => p_rec.object_version_number);
492 --
493 chk_duty_station_flag
494 (p_duty_station_id => p_rec.duty_station_id,
495 p_is_duty_station => p_rec.is_duty_station,
496 p_duty_station_code => p_rec.duty_station_code,
497 p_effective_date => p_effective_date,
498 p_object_version_number => p_rec.object_version_number);
499 --
500 -- hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
501 --
502 hr_utility.set_location(' Leaving:'||l_proc, 10);
503 End insert_validate;
504 --
505 -- ----------------------------------------------------------------------------
506 -- |---------------------------< update_validate >----------------------------|
507 -- ----------------------------------------------------------------------------
508 Procedure update_validate
509 (p_rec in ghr_dut_shd.g_rec_type,
510 p_effective_date in date,
511 p_datetrack_mode in varchar2,
512 p_validation_start_date in date,
513 p_validation_end_date in date) is
514 --
515 l_proc varchar2(72) := g_package||'update_validate';
516 --
517 Begin
518 hr_utility.set_location('Entering:'||l_proc, 5);
519 --
520 -- Call all supporting business operations
521 --
522 chk_duty_station_id
523 (p_duty_station_id => p_rec.duty_station_id,
524 p_effective_date => p_effective_date,
525 p_object_version_number => p_rec.object_version_number);
526 --
527 chk_duty_station_flag
528 (p_duty_station_id => p_rec.duty_station_id,
529 p_is_duty_station => p_rec.is_duty_station,
530 p_duty_station_code => p_rec.duty_station_code,
531 p_effective_date => p_effective_date,
532 p_object_version_number => p_rec.object_version_number);
533 --
534 -- Call the datetrack update integrity operation
535 --
536 dt_update_validate (p_datetrack_mode => p_datetrack_mode,
537 p_validation_start_date => p_validation_start_date,
538 p_validation_end_date => p_validation_end_date);
539 hr_utility.set_location(' Leaving:'||l_proc, 10);
540 End update_validate;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |---------------------------< delete_validate >----------------------------|
544 -- ----------------------------------------------------------------------------
545 Procedure delete_validate
546 (p_rec in ghr_dut_shd.g_rec_type,
547 p_effective_date in date,
548 p_datetrack_mode in varchar2,
549 p_validation_start_date in date,
550 p_validation_end_date in date) is
551 --
552 l_proc varchar2(72) := g_package||'delete_validate';
553 --
554 Begin
555 hr_utility.set_location('Entering:'||l_proc, 5);
556 --
557 -- Call all supporting business operations
558 --
559 chk_active_assignments(p_duty_station_id => p_rec.duty_station_id,
560 p_effective_date => p_effective_date);
561
562 dt_delete_validate
563 (p_datetrack_mode => p_datetrack_mode,
564 p_validation_start_date => p_validation_start_date,
565 p_validation_end_date => p_validation_end_date,
566 p_duty_station_id => p_rec.duty_station_id);
567 --
568 hr_utility.set_location(' Leaving:'||l_proc, 10);
569 End delete_validate;
570 --
571 --
572 -- ---------------------------------------------------------------------------
573 -- |---------------------< return_legislation_code >-------------------------|
574 -- ---------------------------------------------------------------------------
575 --
576 function return_legislation_code
577 (p_duty_station_id in number) return varchar2 is
578 --
579 -- Declare local variables
580 --
581 l_legislation_code varchar2(150);
582 l_proc varchar2(72) := g_package||'return_legislation_code';
583 --
584 begin
585 --
586 hr_utility.set_location('Entering:'|| l_proc, 10);
587 --
588 l_legislation_code := 'US';
589 --
590 hr_utility.set_location(' Leaving:'|| l_proc, 20);
591 --
592 return l_legislation_code;
593 --
594 end return_legislation_code;
595 --
596 end ghr_dut_bus;
[Home] [Help]