[Home] [Help]
PACKAGE BODY: APPS.PER_BPR_BUS
Source
1 Package Body per_bpr_bus as
2 /* $Header: pebprrhi.pkb 115.6 2002/12/02 14:33:23 apholt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_bpr_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_payroll_run_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 procedure set_security_group_id
21 (p_payroll_run_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select inf.org_information14
28 from hr_organization_information inf
29 , per_bf_payroll_runs bpr
30 where bpr.payroll_run_id = p_payroll_run_id
31 and inf.organization_id = bpr.business_group_id
32 and inf.org_information_context || '' = 'Business Group Information';
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error(p_api_name => l_proc,
46 p_argument => 'PAYROLL_RUN_ID',
47 p_argument_value => p_payroll_run_id);
48 --
49 open csr_sec_grp;
50 fetch csr_sec_grp into l_security_group_id;
51 --
52 if csr_sec_grp%notfound then
53 --
54 close csr_sec_grp;
55 --
56 -- The primary key is invalid therefore we must error
57 --
58 fnd_message.set_name('PER','HR_7220_INVALID_PRIMARY_KEY');
59 fnd_message.raise_error;
60 --
61 end if;
62 close csr_sec_grp;
63 --
64 -- Set the security_group_id in CLIENT_INFO
65 --
66 hr_api.set_security_group_id
67 (p_security_group_id => l_security_group_id
68 );
69 --
70 hr_utility.set_location(' Leaving:'|| l_proc, 20);
71 --
72 end set_security_group_id;
73 --
74 -- ---------------------------------------------------------------------------
75 -- |---------------------< return_legislation_code >-------------------------|
76 -- ---------------------------------------------------------------------------
77 --
78 function return_legislation_code
79 (p_payroll_run_id in number) return varchar2 is
80 --
81 -- Declare cursor
82 --
83 cursor csr_leg_code is
84 select pbg.legislation_code
85 from per_business_groups pbg
86 , per_bf_payroll_runs bpr
87 where bpr.payroll_run_id = p_payroll_run_id
88 and pbg.business_group_id = bpr.business_group_id;
89 --
90 -- Declare local variables
91 --
92 l_legislation_code varchar2(150);
93 l_proc varchar2(72) := g_package||'return_legislation_code';
94 --
95 begin
96 --
97 hr_utility.set_location('Entering:'|| l_proc, 10);
98 --
99 -- Ensure that all the mandatory parameter are not null
100 --
101 hr_api.mandatory_arg_error(p_api_name => l_proc,
102 p_argument => 'PAYROLL_RUN_ID',
103 p_argument_value => p_payroll_run_id);
104 --
105 if ( nvl(g_payroll_run_id, hr_api.g_number) = p_payroll_run_id) then
106 --
107 -- The legislation code has already been found with a previous
108 -- call to this function. Just return the value in the global
109 -- variable.
110 --
111 l_legislation_code := g_legislation_code;
112 hr_utility.set_location(l_proc, 20);
113 else
114 --
115 -- The ID is different to the last call to this function
116 -- or this is the first call to this function.
117 --
118 open csr_leg_code;
119 fetch csr_leg_code into l_legislation_code;
120 --
121 if csr_leg_code%notfound then
122 --
123 -- The primary key is invalid therefore we must error
124 --
125 close csr_leg_code;
126 fnd_message.set_name('PER','HR_7220_INVALID_PRIMARY_KEY');
127 fnd_message.raise_error;
128 end if;
129 hr_utility.set_location(l_proc,30);
130 --
131 -- Set the global variables so the values are
132 -- available for the next call to this function.
133 --
134 close csr_leg_code;
135 g_payroll_run_id := p_payroll_run_id;
136 g_legislation_code := l_legislation_code;
137 end if;
138 hr_utility.set_location(' Leaving:'|| l_proc, 40);
139 return l_legislation_code;
140 end return_legislation_code;
141 --
142 -- ----------------------------------------------------------------------------
143 -- |------------------------------< chk_df >----------------------------------|
144 -- ----------------------------------------------------------------------------
145 --
146 -- Description:
147 -- Validates all the Descriptive Flexfield values.
148 --
149 -- Prerequisites:
150 -- All other columns have been validated. Must be called as the
151 -- last step from insert_validate and update_validate.
152 --
153 -- In Arguments:
154 -- p_rec
155 --
156 -- Post Success:
157 -- If the Descriptive Flexfield structure column and data values are
158 -- all valid this procedure will end normally and processing will
159 -- continue.
160 --
161 -- Post Failure:
162 -- If the Descriptive Flexfield structure column value or any of
163 -- the data values are invalid then an application error is raised as
164 -- a PL/SQL exception.
165 --
166 -- Access Status:
167 -- Internal Row Handler Use Only.
168 --
169 -- ----------------------------------------------------------------------------
170 procedure chk_df
171 (p_rec in per_bpr_shd.g_rec_type
172 ) is
173 --
174 l_proc varchar2(72) := g_package || 'chk_df';
175 --
176 begin
177 hr_utility.set_location('Entering:'||l_proc,10);
178 --
179 if ((p_rec.payroll_run_id is not null) and (
180 nvl(per_bpr_shd.g_old_rec.bpr_attribute_category, hr_api.g_varchar2) <>
181 nvl(p_rec.bpr_attribute_category, hr_api.g_varchar2) or
182 nvl(per_bpr_shd.g_old_rec.bpr_attribute1, hr_api.g_varchar2) <>
183 nvl(p_rec.bpr_attribute1, hr_api.g_varchar2) or
184 nvl(per_bpr_shd.g_old_rec.bpr_attribute2, hr_api.g_varchar2) <>
185 nvl(p_rec.bpr_attribute2, hr_api.g_varchar2) or
186 nvl(per_bpr_shd.g_old_rec.bpr_attribute3, hr_api.g_varchar2) <>
187 nvl(p_rec.bpr_attribute3, hr_api.g_varchar2) or
188 nvl(per_bpr_shd.g_old_rec.bpr_attribute4, hr_api.g_varchar2) <>
189 nvl(p_rec.bpr_attribute4, hr_api.g_varchar2) or
190 nvl(per_bpr_shd.g_old_rec.bpr_attribute5, hr_api.g_varchar2) <>
191 nvl(p_rec.bpr_attribute5, hr_api.g_varchar2) or
192 nvl(per_bpr_shd.g_old_rec.bpr_attribute6, hr_api.g_varchar2) <>
193 nvl(p_rec.bpr_attribute6, hr_api.g_varchar2) or
194 nvl(per_bpr_shd.g_old_rec.bpr_attribute7, hr_api.g_varchar2) <>
195 nvl(p_rec.bpr_attribute7, hr_api.g_varchar2) or
196 nvl(per_bpr_shd.g_old_rec.bpr_attribute8, hr_api.g_varchar2) <>
197 nvl(p_rec.bpr_attribute8, hr_api.g_varchar2) or
198 nvl(per_bpr_shd.g_old_rec.bpr_attribute9, hr_api.g_varchar2) <>
199 nvl(p_rec.bpr_attribute9, hr_api.g_varchar2) or
200 nvl(per_bpr_shd.g_old_rec.bpr_attribute10, hr_api.g_varchar2) <>
201 nvl(p_rec.bpr_attribute10, hr_api.g_varchar2) or
202 nvl(per_bpr_shd.g_old_rec.bpr_attribute11, hr_api.g_varchar2) <>
203 nvl(p_rec.bpr_attribute11, hr_api.g_varchar2) or
204 nvl(per_bpr_shd.g_old_rec.bpr_attribute12, hr_api.g_varchar2) <>
205 nvl(p_rec.bpr_attribute12, hr_api.g_varchar2) or
206 nvl(per_bpr_shd.g_old_rec.bpr_attribute13, hr_api.g_varchar2) <>
207 nvl(p_rec.bpr_attribute13, hr_api.g_varchar2) or
208 nvl(per_bpr_shd.g_old_rec.bpr_attribute14, hr_api.g_varchar2) <>
209 nvl(p_rec.bpr_attribute14, hr_api.g_varchar2) or
210 nvl(per_bpr_shd.g_old_rec.bpr_attribute15, hr_api.g_varchar2) <>
211 nvl(p_rec.bpr_attribute15, hr_api.g_varchar2) or
212 nvl(per_bpr_shd.g_old_rec.bpr_attribute16, hr_api.g_varchar2) <>
213 nvl(p_rec.bpr_attribute16, hr_api.g_varchar2) or
214 nvl(per_bpr_shd.g_old_rec.bpr_attribute17, hr_api.g_varchar2) <>
215 nvl(p_rec.bpr_attribute17, hr_api.g_varchar2) or
216 nvl(per_bpr_shd.g_old_rec.bpr_attribute18, hr_api.g_varchar2) <>
217 nvl(p_rec.bpr_attribute18, hr_api.g_varchar2) or
218 nvl(per_bpr_shd.g_old_rec.bpr_attribute19, hr_api.g_varchar2) <>
219 nvl(p_rec.bpr_attribute19, hr_api.g_varchar2) or
220 nvl(per_bpr_shd.g_old_rec.bpr_attribute20, hr_api.g_varchar2) <>
221 nvl(p_rec.bpr_attribute20, hr_api.g_varchar2) or
222 nvl(per_bpr_shd.g_old_rec.bpr_attribute21, hr_api.g_varchar2) <>
223 nvl(p_rec.bpr_attribute21, hr_api.g_varchar2) or
224 nvl(per_bpr_shd.g_old_rec.bpr_attribute22, hr_api.g_varchar2) <>
225 nvl(p_rec.bpr_attribute22, hr_api.g_varchar2) or
226 nvl(per_bpr_shd.g_old_rec.bpr_attribute23, hr_api.g_varchar2) <>
227 nvl(p_rec.bpr_attribute23, hr_api.g_varchar2) or
228 nvl(per_bpr_shd.g_old_rec.bpr_attribute24, hr_api.g_varchar2) <>
229 nvl(p_rec.bpr_attribute24, hr_api.g_varchar2) or
230 nvl(per_bpr_shd.g_old_rec.bpr_attribute25, hr_api.g_varchar2) <>
231 nvl(p_rec.bpr_attribute25, hr_api.g_varchar2) or
232 nvl(per_bpr_shd.g_old_rec.bpr_attribute26, hr_api.g_varchar2) <>
233 nvl(p_rec.bpr_attribute26, hr_api.g_varchar2) or
234 nvl(per_bpr_shd.g_old_rec.bpr_attribute27, hr_api.g_varchar2) <>
235 nvl(p_rec.bpr_attribute27, hr_api.g_varchar2) or
236 nvl(per_bpr_shd.g_old_rec.bpr_attribute28, hr_api.g_varchar2) <>
237 nvl(p_rec.bpr_attribute28, hr_api.g_varchar2) or
238 nvl(per_bpr_shd.g_old_rec.bpr_attribute29, hr_api.g_varchar2) <>
239 nvl(p_rec.bpr_attribute29, hr_api.g_varchar2) or
240 nvl(per_bpr_shd.g_old_rec.bpr_attribute30, hr_api.g_varchar2) <>
241 nvl(p_rec.bpr_attribute30, hr_api.g_varchar2) ))
242 or (p_rec.payroll_run_id is null) then
243 --
244 -- Only execute the validation if absolutely necessary:
245 -- a) During update, the structure column value or any
246 -- of the attribute values have actually changed.
247 -- b) During insert.
248 --
249 hr_dflex_utility.ins_or_upd_descflex_attribs
250 (p_appl_short_name => 'PER'
251 ,p_descflex_name => 'PER_BF_PAYROLL_RUNS'
252 ,p_attribute_category => p_rec.bpr_attribute_category
253 ,p_attribute1_name => 'BPR_ATTRIBUTE1'
254 ,p_attribute1_value => p_rec.bpr_attribute1
255 ,p_attribute2_name => 'BPR_ATTRIBUTE2'
256 ,p_attribute2_value => p_rec.bpr_attribute2
257 ,p_attribute3_name => 'BPR_ATTRIBUTE3'
258 ,p_attribute3_value => p_rec.bpr_attribute3
259 ,p_attribute4_name => 'BPR_ATTRIBUTE4'
260 ,p_attribute4_value => p_rec.bpr_attribute4
261 ,p_attribute5_name => 'BPR_ATTRIBUTE5'
262 ,p_attribute5_value => p_rec.bpr_attribute5
263 ,p_attribute6_name => 'BPR_ATTRIBUTE6'
264 ,p_attribute6_value => p_rec.bpr_attribute6
265 ,p_attribute7_name => 'BPR_ATTRIBUTE7'
266 ,p_attribute7_value => p_rec.bpr_attribute7
267 ,p_attribute8_name => 'BPR_ATTRIBUTE8'
268 ,p_attribute8_value => p_rec.bpr_attribute8
269 ,p_attribute9_name => 'BPR_ATTRIBUTE9'
270 ,p_attribute9_value => p_rec.bpr_attribute9
271 ,p_attribute10_name => 'BPR_ATTRIBUTE10'
272 ,p_attribute10_value => p_rec.bpr_attribute10
273 ,p_attribute11_name => 'BPR_ATTRIBUTE11'
274 ,p_attribute11_value => p_rec.bpr_attribute11
275 ,p_attribute12_name => 'BPR_ATTRIBUTE12'
276 ,p_attribute12_value => p_rec.bpr_attribute12
277 ,p_attribute13_name => 'BPR_ATTRIBUTE13'
278 ,p_attribute13_value => p_rec.bpr_attribute13
279 ,p_attribute14_name => 'BPR_ATTRIBUTE14'
280 ,p_attribute14_value => p_rec.bpr_attribute14
281 ,p_attribute15_name => 'BPR_ATTRIBUTE15'
282 ,p_attribute15_value => p_rec.bpr_attribute15
283 ,p_attribute16_name => 'BPR_ATTRIBUTE16'
284 ,p_attribute16_value => p_rec.bpr_attribute16
285 ,p_attribute17_name => 'BPR_ATTRIBUTE17'
286 ,p_attribute17_value => p_rec.bpr_attribute17
287 ,p_attribute18_name => 'BPR_ATTRIBUTE18'
288 ,p_attribute18_value => p_rec.bpr_attribute18
289 ,p_attribute19_name => 'BPR_ATTRIBUTE19'
290 ,p_attribute19_value => p_rec.bpr_attribute19
291 ,p_attribute20_name => 'BPR_ATTRIBUTE20'
292 ,p_attribute20_value => p_rec.bpr_attribute20
293 ,p_attribute21_name => 'BPR_ATTRIBUTE21'
294 ,p_attribute21_value => p_rec.bpr_attribute21
295 ,p_attribute22_name => 'BPR_ATTRIBUTE22'
296 ,p_attribute22_value => p_rec.bpr_attribute22
297 ,p_attribute23_name => 'BPR_ATTRIBUTE23'
298 ,p_attribute23_value => p_rec.bpr_attribute23
299 ,p_attribute24_name => 'BPR_ATTRIBUTE24'
300 ,p_attribute24_value => p_rec.bpr_attribute24
301 ,p_attribute25_name => 'BPR_ATTRIBUTE25'
302 ,p_attribute25_value => p_rec.bpr_attribute25
303 ,p_attribute26_name => 'BPR_ATTRIBUTE26'
304 ,p_attribute26_value => p_rec.bpr_attribute26
305 ,p_attribute27_name => 'BPR_ATTRIBUTE27'
306 ,p_attribute27_value => p_rec.bpr_attribute27
307 ,p_attribute28_name => 'BPR_ATTRIBUTE28'
308 ,p_attribute28_value => p_rec.bpr_attribute28
309 ,p_attribute29_name => 'BPR_ATTRIBUTE29'
310 ,p_attribute29_value => p_rec.bpr_attribute29
311 ,p_attribute30_name => 'BPR_ATTRIBUTE30'
312 ,p_attribute30_value => p_rec.bpr_attribute30
313 );
314 end if;
315 --
316 hr_utility.set_location(' Leaving:'||l_proc,20);
317 end chk_df;
318 --
319 -- ----------------------------------------------------------------------------
320 -- |----------------------< check_non_updateable_args >-----------------------|
321 -- ----------------------------------------------------------------------------
322 -- {Start Of Comments}
323 --
324 -- Description:
325 -- This procedure is used to ensure that non updateable attributes have
326 -- not been updated. If an attribute has been updated an error is generated.
327 --
328 -- Pre Conditions:
329 -- g_old_rec has been populated with details of the values currently in
330 -- the database.
331 --
332 -- In Arguments:
333 -- p_rec has been populated with the updated values the user would like the
334 -- record set to.
335 --
336 -- Post Success:
337 -- Processing continues if all the non updateable attributes have not
338 -- changed.
339 --
340 -- Post Failure:
344 -- {End Of Comments}
341 -- An application error is raised if any of the non updatable attributes
342 -- have been altered.
343 --
345 -- ----------------------------------------------------------------------------
346 Procedure chk_non_updateable_args(p_rec in per_bpr_shd.g_rec_type) IS
347 --
348 l_proc varchar2(72) := g_package || 'check_non_updateable_args';
349 l_error EXCEPTION;
350 l_argument varchar2(30);
351 --
352 Begin
353 hr_utility.set_location('Entering:'||l_proc,5);
354 --
355 -- Only proceed with the validation if a row exists for the current
356 -- record in the HR Schema.
357 --
358 IF NOT per_bpr_shd.api_updating
359 (p_payroll_run_id => p_rec.payroll_run_id
360 ,p_object_version_number => p_rec.object_version_number
361 ) THEN
362 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
363 hr_utility.set_message_token('PROCEDURE ', l_proc);
364 hr_utility.set_message_token('STEP ', '5');
365 END IF;
366 --
367 hr_utility.set_location(l_proc,10);
368 --
369 IF nvl(p_rec.payroll_id, hr_api.g_number) <>
370 per_bpr_shd.g_old_rec.payroll_id then
371 l_argument:='payroll_id';
372 raise l_error;
373 END IF;
374 hr_utility.set_location(l_proc,20);
375 --
376 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
377 per_bpr_shd.g_old_rec.business_group_id then
378 l_argument:='business_group_id';
379 raise l_error;
380 END IF;
381 hr_utility.set_location(l_proc,30);
382 --
383 --
384 EXCEPTION
385 WHEN l_error THEN
386 hr_api.argument_changed_error
387 (p_api_name => l_proc
388 ,p_argument => l_argument);
389 WHEN OTHERS THEN
390 RAISE;
391 hr_utility.set_location(' Leaving:'||l_proc,20);
392 End chk_non_updateable_args;
393 --
394 -- ----------------------------------------------------------------------------
395 -- |---------------------------< chk_identifier >-----------------------------|
396 -- ----------------------------------------------------------------------------
397 -- Description
398 -- A check to make sure the payroll_identifer is unique within the context
399 -- of the the business_group_id.
400 -- Pre-Conditions
401 -- None
402 -- In Arguments
403 -- p_payroll_identifier
404 -- p_business_group_id
405 -- Post Success
406 -- Process continues if the identifier is unique within the constraints
407 -- laid out above.
408 -- Post Failure
409 -- An application error will be raised and processing terminated if the
410 -- payroll identifier is not unique within the constraints laid out above.
411 --
412 Procedure chk_identifier
413 ( p_payroll_identifier in per_bf_payroll_runs.payroll_identifier%TYPE
414 , p_business_group_id in per_bf_payroll_runs.business_group_id%TYPE
415 , p_payroll_run_id in per_bf_payroll_runs.payroll_run_id%TYPE
416 )
417 is
418 --
419 l_proc varchar2(72) := g_package || 'chk_identifier';
420 --
421 CURSOR c_check_identifier IS
422 SELECT 1
423 FROM per_bf_payroll_runs
424 WHERE( (p_payroll_run_id IS NULL)
425 or (payroll_run_id <> p_payroll_run_id))
426 AND payroll_identifier = p_payroll_identifier
427 AND business_group_id = p_business_group_id ;
428 --
429 l_temp VARCHAR2(1);
430 begin
431 hr_utility.set_location ('Entering:'|| l_proc, 1);
432 --
433 --
434 -- Check that the business_group_id is not null.
435 --
436 hr_api.mandatory_arg_error
437 (p_api_name => l_proc
438 ,p_argument => 'business_group_id'
439 ,p_argument_value => p_business_group_id
440 );
441 --
442 OPEN c_check_identifier;
443 FETCH c_check_identifier INTO l_temp;
444 --
445 IF c_check_identifier%FOUND THEN
446 --
447 -- Another row exists with the same identifier in the same context
448 -- so error.
449 --
450 close c_check_identifier;
451 --
452 per_bpr_shd.constraint_error('PER_BF_PAYROLL_RUNS_UK1');
453 --
454 END IF;
455 --
456 close c_check_identifier;
457 --
458 end chk_identifier;
459 --
460 -- ----------------------------------------------------------------------------
461 -- |---------------------------< chk_run_dates >------------------------------|
462 -- ----------------------------------------------------------------------------
463 -- Description
464 -- One of the following dates must be set:
465 -- PERIOD_START_DATE
466 -- PERIOD_END_DATE
467 -- PROCESSING_DATE
468 -- Processing_date must be greater or equal to period_start_date
469 -- Pre-Conditions
470 -- None
471 --
472 -- In Arguments
473 -- p_period_start_date
474 -- p_period_end_date
475 -- p_processing_date
476 -- p_payroll_run_id
477 -- p_object_version_number
478 --
479 -- Post Success
480 -- Processing continues
481 --
482 -- Post Failure
483 -- An application error will be raised and processing terminated.
484 --
485 PROCEDURE chk_run_dates
486 ( p_period_start_date in per_bf_payroll_runs.period_start_date%TYPE
487 , p_period_end_date in per_bf_payroll_runs.period_end_date%TYPE
491 ) IS
488 , p_processing_date in per_bf_payroll_runs.processing_date%TYPE
489 , p_payroll_run_id in per_bf_payroll_runs.payroll_run_id%TYPE
490 , p_object_version_number in per_bf_payroll_runs.object_version_number%TYPE
492 --
493 l_proc varchar2(72) := g_package||'chk_run_dates';
494 --
495 l_start_date DATE;
496 l_processing_date DATE;
497 l_api_updating BOOLEAN;
498 --
499 BEGIN
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 --
502 l_api_updating := per_bpr_shd.api_updating
503 (p_payroll_run_id => p_payroll_run_id
504 ,p_object_version_number => p_object_version_number
505 );
506 --
507 IF ( (p_period_start_date IS NULL)
508 OR ( l_api_updating
509 AND NVL (per_bpr_shd.g_old_rec.period_start_date, hr_api.g_date)
510 = NVL (p_period_start_date, hr_api.g_date)
511 AND per_bpr_shd.g_old_rec.period_start_date IS NULL
512 )
513 ) THEN
514 --
515 IF ( (p_period_end_date IS NULL)
516 OR ( l_api_updating
517 AND NVL (per_bpr_shd.g_old_rec.period_end_date, hr_api.g_date)
518 = NVL (p_period_end_date, hr_api.g_date)
519 AND per_bpr_shd.g_old_rec.period_end_date IS NULL
520 )
521 ) THEN
522 --
523 IF ( (p_processing_date IS NULL)
524 OR ( l_api_updating
525 AND NVL (per_bpr_shd.g_old_rec.processing_date, hr_api.g_date)
526 = NVL (p_processing_date, hr_api.g_date)
527 AND per_bpr_shd.g_old_rec.processing_date IS NULL
528 )
529 ) THEN
530 --
531 -- All the dates are Null, so raise an error as at least one must be
532 -- set.
533 --
534 hr_utility.set_message(800,'HR_52932_ALL_DATES_NULL');
535 hr_utility.raise_error;
536 END IF;
537 END IF;
538 END IF;
539 --
540 -- Check to make sure that the processing date is later or equal to the
541 -- start date.
542 --
543 -- If inserting and the start date is greater than the processing date ..
544 IF ((NOT l_api_updating AND NVL(p_period_start_date, hr_api.g_sot)
545 > p_processing_date)
546 OR
547 -- If updating and either the start date or processing date have changed
548 ((l_api_updating and nvl(per_bpr_shd.g_old_rec.period_start_date,
549 hr_api.g_date)
550 <> nvl(p_period_start_date, hr_api.g_date))
551 OR
552 (l_api_updating and nvl(per_bpr_shd.g_old_rec.processing_date,
553 hr_api.g_date)
554 <> nvl(p_processing_date, hr_api.g_date)))) THEN
555 --
556 -- Make the dates are equal to their actual value and not
557 -- hr_api.g_date
558 --
559 IF (l_api_updating and p_period_start_date = hr_api.g_date) THEN
560 --
561 l_start_date := NVL(per_bpr_shd.g_old_rec.period_start_date
562 ,hr_api.g_sot);
563 --
564 ELSE
565 --
566 l_start_date := NVL(p_period_start_date, hr_api.g_sot);
567 --
568 END IF;
569 IF (l_api_updating AND p_processing_date = hr_api.g_date) THEN
570 --
571 l_processing_date := NVL(per_bpr_shd.g_old_rec.processing_date
572 ,hr_api.g_eot);
573 --
574 ELSE
575 --
576 l_processing_date := NVL(p_processing_date,hr_api.g_eot);
577 --
578 END IF;
579 --
580 -- Now the actual date exist, perform the check
581 --
582 IF l_start_date > l_processing_date THEN
583 --
584 -- The start date is greater than the processing date
585 hr_utility.set_message(800,'HR_52608_PROC_DATE_PROB');
586 hr_utility.raise_error;
587 --
588 END IF;
589 END IF;
590 --
591 END CHK_RUN_DATES;
592 --
593 -- ----------------------------------------------------------------------------
594 -- |---------------------------< chk_date_start_end >-------------------------|
595 -- ----------------------------------------------------------------------------
596 -- Description
597 -- If both exist, the start date has to be greater than the end date
598 -- and if the end date exists then the start date has to exist.
599 --
600 -- Pre-Conditions
601 -- None
602 -- In Arguments
603 -- p_period_start_date
604 -- p_period_end_date
605 -- Post Success
606 -- Processing continues
607 --
608 -- Post Failure
609 -- An application error will be raised and processing terminated.
610 --
611 PROCEDURE chk_date_start_end
612 ( p_period_start_date in per_bf_payroll_runs.period_start_date%TYPE
613 , p_period_end_date in per_bf_payroll_runs.period_end_date%TYPE
614 , p_payroll_run_id in per_bf_payroll_runs.payroll_run_id%TYPE
615 , p_object_version_number in per_bf_payroll_runs.object_version_number%TYPE
616 ) IS
617 --
618 l_start_date DATE;
619 l_end_date DATE;
620 l_api_updating BOOLEAN;
621 --
622 BEGIN
623 --
624 -- Only proceed with the validation if :
625 -- a) The current g_old_rec is current.
626 -- b) The period_start_date has changed.
627 -- c) The period_end_date has changed.
631 l_api_updating := per_bpr_shd.api_updating
628 -- d) a record is being inserted.
629 --
630 --
632 (p_payroll_run_id => p_payroll_run_id
633 ,p_object_version_number => p_object_version_number
634 );
635 --
636 IF (((l_api_updating and nvl(per_bpr_shd.g_old_rec.period_start_date,
637 hr_api.g_date)
638 <> nvl(p_period_start_date, hr_api.g_date))
639 OR
640 (l_api_updating and nvl(per_bpr_shd.g_old_rec.period_end_date,
641 hr_api.g_date)
642 <> nvl(p_period_end_date, hr_api.g_date))
643 )
644 OR
645 (NOT l_api_updating)) THEN
646 --
647 --
648 -- Make sure the dates are equal to their actual value and not
649 -- hr_api.g_date
650 --
651 IF (l_api_updating and p_period_start_date = hr_api.g_date) THEN
652 --
653 l_start_date := per_bpr_shd.g_old_rec.period_start_date;
654 --
655 ELSE
656 --
657 l_start_date := p_period_start_date;
658 --
659 END IF;
660 IF (l_api_updating AND p_period_end_date = hr_api.g_date) THEN
661 --
662 l_end_date := per_bpr_shd.g_old_rec.period_end_date;
663 --
664 ELSE
665 --
666 l_end_date := p_period_end_date;
667 --
668 END IF;
669 --
670 -- If the period_start_date is greater than the period_end_date, raise an
671 -- error
672 IF ((l_start_date IS NOT NULL) and (l_end_date IS NOT NULL)
673 AND (l_start_date > l_end_date)) THEN
674 --
675 hr_utility.set_message (800,'HR_52933_DATE_START_END');
676 hr_utility.raise_error;
677 --
678 END IF;
679 --
680 IF ((l_start_date IS NULL) and (l_end_date IS NOT NULL))
681 THEN
682 --
683 hr_utility.set_message (800,'HR_52607_DATE_START_NULL_END');
684 hr_utility.raise_error;
685 --
686 END IF;
687 END IF;
688 END chk_date_start_end;
689 --
690 -- ----------------------------------------------------------------------------
691 -- |---------------------------< chk_row_exists >-----------------------------|
692 -- ----------------------------------------------------------------------------
693 --
694 Procedure chk_row_exists
695 (p_payroll_run_id in number
696 ) is
697 --
698 l_exists varchar2(1);
699 l_proc varchar2(72) := g_package || 'chk_row_exists';
700 --
701 -- Cursor to check if row exists in per_bf_processed_assignments for given
702 -- payroll_run_id
703 --
704 cursor csr_payroll_exists is
705 select 'Y'
706 from per_bf_processed_assignments bpa
707 where bpa.payroll_run_id = p_payroll_run_id;
708 --
709 begin
710 hr_utility.set_location('Entering: '||l_proc, 10);
711 --
712 if p_payroll_run_id is not null then
713 open csr_payroll_exists;
714 fetch csr_payroll_exists into l_exists;
715 if csr_payroll_exists%FOUND then
716 -- row exists - raise error by calling contraint error
717 close csr_payroll_exists;
718 per_bpr_shd.constraint_error('PER_BF_PAYROLL_RUNS_REX');
719 end if;
720 close csr_payroll_exists;
721 end if;
722 end chk_row_exists;
723 --
724
725 -- ----------------------------------------------------------------------------
726 -- |---------------------------< insert_validate >----------------------------|
727 -- ----------------------------------------------------------------------------
728 Procedure insert_validate(p_effective_date in date,
729 p_rec in per_bpr_shd.g_rec_type
730 ) is
731 --
732 l_proc varchar2(72) := g_package||'insert_validate';
733 --
734 Begin
735 hr_utility.set_location('Entering:'||l_proc, 5);
736 --
737 -- Call all supporting business operations.
738 --
739 --
740 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
741 --
742 chk_identifier
743 ( p_payroll_identifier => p_rec.payroll_identifier
744 , p_business_group_id => p_rec.business_group_id
745 , p_payroll_run_id => p_rec.payroll_run_id
746 );
747 --
748 chk_run_dates
749 ( p_period_start_date => p_rec.period_start_date
750 , p_period_end_date => p_rec.period_end_date
751 , p_processing_date => p_rec.processing_date
752 , p_payroll_run_id => p_rec.payroll_run_id
753 , p_object_version_number => p_rec.object_version_number
754 ) ;
755 --
756 chk_date_start_end
757 ( p_period_start_date => p_rec.period_start_date
758 , p_period_end_date => p_rec.period_end_date
759 , p_payroll_run_id => p_rec.payroll_run_id
760 , p_object_version_number => p_rec.object_version_number
761 );
762 --
763 -- Validate flexfields
764 -- ===================
765 chk_df(p_rec => p_rec);
766 --
767 hr_utility.set_location(' Leaving:'||l_proc, 10);
768 End insert_validate;
769 --
770 -- ----------------------------------------------------------------------------
771 -- |---------------------------< update_validate >----------------------------|
772 -- ----------------------------------------------------------------------------
773 Procedure update_validate(p_effective_date in date,
774 p_rec in per_bpr_shd.g_rec_type
775 ) is
776 --
777 l_proc varchar2(72) := g_package||'update_validate';
778 --
779 Begin
780 hr_utility.set_location('Entering:'||l_proc, 5);
781 --
782 -- Call all supporting business operations
783 --
784 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
785 --
786 -- VALIDATE IDENTIFIER
787 -- Business Rule Mapping
788 -- =====================
789 -- Rule CHK_IDENTIFIER a
790 --
791 chk_identifier
792 ( p_payroll_identifier => p_rec.payroll_identifier
793 , p_business_group_id => p_rec.business_group_id
794 , p_payroll_run_id => p_rec.payroll_run_id
795 );
796 --
797 chk_run_dates
798 ( p_period_start_date => p_rec.period_start_date
799 , p_period_end_date => p_rec.period_end_date
800 , p_processing_date => p_rec.processing_date
801 , p_payroll_run_id => p_rec.payroll_run_id
802 , p_object_version_number => p_rec.object_version_number
803 ) ;
804 --
805 chk_date_start_end
806 ( p_period_start_date => p_rec.period_start_date
807 , p_period_end_date => p_rec.period_end_date
808 , p_payroll_run_id => p_rec.payroll_run_id
809 , p_object_version_number => p_rec.object_version_number
810 );
811 --
812 chk_non_updateable_args(p_rec => p_rec);
813 --
814 -- Validate flexfields
815 -- ===================
816 chk_df(p_rec => p_rec);
817 --
818 hr_utility.set_location(' Leaving:'||l_proc, 10);
819 End update_validate;
820 --
821 -- ----------------------------------------------------------------------------
822 -- |---------------------------< delete_validate >----------------------------|
823 -- ----------------------------------------------------------------------------
824 Procedure delete_validate(p_rec in per_bpr_shd.g_rec_type) is
825 --
826 l_proc varchar2(72) := g_package||'delete_validate';
827 --
828 Begin
829 hr_utility.set_location('Entering:'||l_proc, 5);
830 --
831 -- Call all supporting business operations
832 --
833 chk_row_exists(p_payroll_run_id => p_rec.payroll_run_id);
834 --
835 hr_utility.set_location(' Leaving:'||l_proc, 10);
836 End delete_validate;
837 --
838 end per_bpr_bus;