[Home] [Help]
PACKAGE BODY: APPS.PQH_PSU_BUS
Source
1 Package Body pqh_psu_bus as
2 /* $Header: pqpsurhi.pkb 120.0 2005/05/29 02:19 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_psu_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_emp_stat_situation_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_statutory_situation_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , pqh_fr_stat_situations sts
32 where
33 sts.statutory_situation_id = p_statutory_situation_id
34 and pbg.business_group_id = sts.business_group_id;
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 l_legislation_code varchar2(150);
41 --
42 begin
43 --
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Ensure that all the mandatory parameter are not null
47 --
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc
50 ,p_argument => 'statutory_situation_id'
51 ,p_argument_value => p_statutory_situation_id
52 );
53 --
54 open csr_sec_grp;
55 fetch csr_sec_grp into l_security_group_id
56 , l_legislation_code;
57 --
58 if csr_sec_grp%notfound then
59 --
60 close csr_sec_grp;
61 --
62 -- The primary key is invalid therefore we must error
63 --
64 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
65 hr_multi_message.add
66 (p_associated_column1
67 => nvl(p_associated_column1,'STATUTORY_SITUATION_ID')
68 );
69 --
70 else
71 close csr_sec_grp;
72 --
73 -- Set the security_group_id in CLIENT_INFO
74 --
75 hr_api.set_security_group_id
76 (p_security_group_id => l_security_group_id
77 );
78 --
79 -- Set the sessions legislation context in HR_SESSION_DATA
80 --
81 hr_api.set_legislation_context(l_legislation_code);
82 end if;
83 --
84 hr_utility.set_location(' Leaving:'|| l_proc, 20);
85 --
86 end set_security_group_id;
87 --
88 -- ---------------------------------------------------------------------------
89 -- |---------------------< return_legislation_code >-------------------------|
90 -- ---------------------------------------------------------------------------
91 --
92 Function return_legislation_code
93 (p_emp_stat_situation_id in number
94 )
95 Return Varchar2 Is
96 --
97 -- Declare cursor
98 --
99 cursor csr_leg_code is
100 select pbg.legislation_code
101 from per_business_groups_perf pbg
102 , pqh_fr_emp_stat_situations psu
103 , pqh_fr_stat_situations sts
104 where psu.emp_stat_situation_id = p_emp_stat_situation_id
105 and psu.statutory_situation_id = sts.statutory_situation_id
106 and pbg.business_group_id = sts.business_group_id;
107 --
108 -- Declare local variables
109 --
110 l_legislation_code varchar2(150);
111 l_proc varchar2(72) := g_package||'return_legislation_code';
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'|| l_proc, 10);
116 --
117 -- Ensure that all the mandatory parameter are not null
118 --
119 hr_api.mandatory_arg_error
120 (p_api_name => l_proc
121 ,p_argument => 'emp_stat_situation_id'
122 ,p_argument_value => p_emp_stat_situation_id
123 );
124 --
125 if ( nvl(pqh_psu_bus.g_emp_stat_situation_id, hr_api.g_number)
126 = p_emp_stat_situation_id) then
127 --
128 -- The legislation code has already been found with a previous
129 -- call to this function. Just return the value in the global
130 -- variable.
131 --
132 l_legislation_code := pqh_psu_bus.g_legislation_code;
133 hr_utility.set_location(l_proc, 20);
134 else
135 --
136 -- The ID is different to the last call to this function
137 -- or this is the first call to this function.
138 --
139 open csr_leg_code;
140 fetch csr_leg_code into l_legislation_code;
141 --
142 if csr_leg_code%notfound then
143 --
144 -- The primary key is invalid therefore we must error
145 --
146 close csr_leg_code;
147 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
148 fnd_message.raise_error;
149 end if;
150 hr_utility.set_location(l_proc,30);
151 --
152 -- Set the global variables so the values are
153 -- available for the next call to this function.
154 --
155 close csr_leg_code;
156 pqh_psu_bus.g_emp_stat_situation_id := p_emp_stat_situation_id;
157 pqh_psu_bus.g_legislation_code := l_legislation_code;
158 end if;
159 hr_utility.set_location(' Leaving:'|| l_proc, 40);
160 return l_legislation_code;
161 end return_legislation_code;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |------------------------------< chk_df >----------------------------------|
165 -- ----------------------------------------------------------------------------
166 --
167 -- Description:
168 -- Validates all the Descriptive Flexfield values.
169 --
170 -- Prerequisites:
171 -- All other columns have been validated. Must be called as the
172 -- last step from insert_validate and update_validate.
173 --
174 -- In Arguments:
175 -- p_rec
176 --
177 -- Post Success:
178 -- If the Descriptive Flexfield structure column and data values are
179 -- all valid this procedure will end normally and processing will
180 -- continue.
181 --
182 -- Post Failure:
183 -- If the Descriptive Flexfield structure column value or any of
184 -- the data values are invalid then an application error is raised as
185 -- a PL/SQL exception.
186 --
187 -- Access Status:
188 -- Internal Row Handler Use Only.
189 --
190 -- ----------------------------------------------------------------------------
191 procedure chk_df
192 (p_rec in pqh_psu_shd.g_rec_type
193 ) is
194 --
195 l_proc varchar2(72) := g_package || 'chk_df';
196 --
197 begin
198 hr_utility.set_location('Entering:'||l_proc,10);
199 --
200 if ((p_rec.emp_stat_situation_id is not null) and (
201 nvl(pqh_psu_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
202 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
203 nvl(pqh_psu_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
204 nvl(p_rec.attribute1, hr_api.g_varchar2) or
205 nvl(pqh_psu_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
206 nvl(p_rec.attribute2, hr_api.g_varchar2) or
207 nvl(pqh_psu_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
208 nvl(p_rec.attribute3, hr_api.g_varchar2) or
209 nvl(pqh_psu_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
210 nvl(p_rec.attribute4, hr_api.g_varchar2) or
211 nvl(pqh_psu_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
212 nvl(p_rec.attribute5, hr_api.g_varchar2) or
213 nvl(pqh_psu_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
214 nvl(p_rec.attribute6, hr_api.g_varchar2) or
215 nvl(pqh_psu_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
216 nvl(p_rec.attribute7, hr_api.g_varchar2) or
217 nvl(pqh_psu_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
218 nvl(p_rec.attribute8, hr_api.g_varchar2) or
219 nvl(pqh_psu_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute9, hr_api.g_varchar2) or
221 nvl(pqh_psu_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute10, hr_api.g_varchar2) or
223 nvl(pqh_psu_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
224 nvl(p_rec.attribute11, hr_api.g_varchar2) or
225 nvl(pqh_psu_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute12, hr_api.g_varchar2) or
227 nvl(pqh_psu_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute13, hr_api.g_varchar2) or
229 nvl(pqh_psu_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute14, hr_api.g_varchar2) or
231 nvl(pqh_psu_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute15, hr_api.g_varchar2) or
233 nvl(pqh_psu_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
234 nvl(p_rec.attribute16, hr_api.g_varchar2) or
235 nvl(pqh_psu_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
236 nvl(p_rec.attribute17, hr_api.g_varchar2) or
237 nvl(pqh_psu_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
238 nvl(p_rec.attribute18, hr_api.g_varchar2) or
239 nvl(pqh_psu_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
240 nvl(p_rec.attribute19, hr_api.g_varchar2) or
241 nvl(pqh_psu_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
242 nvl(p_rec.attribute20, hr_api.g_varchar2) or
243 nvl(pqh_psu_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
244 nvl(p_rec.attribute21, hr_api.g_varchar2) or
245 nvl(pqh_psu_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
246 nvl(p_rec.attribute22, hr_api.g_varchar2) or
247 nvl(pqh_psu_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
248 nvl(p_rec.attribute23, hr_api.g_varchar2) or
249 nvl(pqh_psu_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
250 nvl(p_rec.attribute24, hr_api.g_varchar2) or
251 nvl(pqh_psu_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
252 nvl(p_rec.attribute25, hr_api.g_varchar2) or
253 nvl(pqh_psu_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
254 nvl(p_rec.attribute26, hr_api.g_varchar2) or
255 nvl(pqh_psu_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute27, hr_api.g_varchar2) or
257 nvl(pqh_psu_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute28, hr_api.g_varchar2) or
259 nvl(pqh_psu_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute29, hr_api.g_varchar2) or
261 nvl(pqh_psu_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
263 or (p_rec.emp_stat_situation_id is null) then
264 --
265 -- Only execute the validation if absolutely necessary:
266 -- a) During update, the structure column value or any
267 -- of the attribute values have actually changed.
268 -- b) During insert.
269 --
270 hr_dflex_utility.ins_or_upd_descflex_attribs
271 (p_appl_short_name => 'PQH'
272 ,p_descflex_name => 'ADDL_EMP_SITUATION_INFO'
273 ,p_attribute_category => p_rec.attribute_category
274 ,p_attribute1_name => 'ATTRIBUTE1'
275 ,p_attribute1_value => p_rec.attribute1
276 ,p_attribute2_name => 'ATTRIBUTE2'
277 ,p_attribute2_value => p_rec.attribute2
278 ,p_attribute3_name => 'ATTRIBUTE3'
279 ,p_attribute3_value => p_rec.attribute3
280 ,p_attribute4_name => 'ATTRIBUTE4'
281 ,p_attribute4_value => p_rec.attribute4
282 ,p_attribute5_name => 'ATTRIBUTE5'
283 ,p_attribute5_value => p_rec.attribute5
284 ,p_attribute6_name => 'ATTRIBUTE6'
285 ,p_attribute6_value => p_rec.attribute6
286 ,p_attribute7_name => 'ATTRIBUTE7'
287 ,p_attribute7_value => p_rec.attribute7
288 ,p_attribute8_name => 'ATTRIBUTE8'
289 ,p_attribute8_value => p_rec.attribute8
290 ,p_attribute9_name => 'ATTRIBUTE9'
291 ,p_attribute9_value => p_rec.attribute9
292 ,p_attribute10_name => 'ATTRIBUTE10'
293 ,p_attribute10_value => p_rec.attribute10
294 ,p_attribute11_name => 'ATTRIBUTE11'
295 ,p_attribute11_value => p_rec.attribute11
296 ,p_attribute12_name => 'ATTRIBUTE12'
297 ,p_attribute12_value => p_rec.attribute12
298 ,p_attribute13_name => 'ATTRIBUTE13'
299 ,p_attribute13_value => p_rec.attribute13
300 ,p_attribute14_name => 'ATTRIBUTE14'
301 ,p_attribute14_value => p_rec.attribute14
302 ,p_attribute15_name => 'ATTRIBUTE15'
303 ,p_attribute15_value => p_rec.attribute15
304 ,p_attribute16_name => 'ATTRIBUTE16'
305 ,p_attribute16_value => p_rec.attribute16
306 ,p_attribute17_name => 'ATTRIBUTE17'
307 ,p_attribute17_value => p_rec.attribute17
308 ,p_attribute18_name => 'ATTRIBUTE18'
309 ,p_attribute18_value => p_rec.attribute18
310 ,p_attribute19_name => 'ATTRIBUTE19'
311 ,p_attribute19_value => p_rec.attribute19
312 ,p_attribute20_name => 'ATTRIBUTE20'
313 ,p_attribute20_value => p_rec.attribute20
314 ,p_attribute21_name => 'ATTRIBUTE21'
315 ,p_attribute21_value => p_rec.attribute21
316 ,p_attribute22_name => 'ATTRIBUTE22'
317 ,p_attribute22_value => p_rec.attribute22
318 ,p_attribute23_name => 'ATTRIBUTE23'
319 ,p_attribute23_value => p_rec.attribute23
320 ,p_attribute24_name => 'ATTRIBUTE24'
321 ,p_attribute24_value => p_rec.attribute24
322 ,p_attribute25_name => 'ATTRIBUTE25'
323 ,p_attribute25_value => p_rec.attribute25
324 ,p_attribute26_name => 'ATTRIBUTE26'
325 ,p_attribute26_value => p_rec.attribute26
326 ,p_attribute27_name => 'ATTRIBUTE27'
327 ,p_attribute27_value => p_rec.attribute27
328 ,p_attribute28_name => 'ATTRIBUTE28'
329 ,p_attribute28_value => p_rec.attribute28
330 ,p_attribute29_name => 'ATTRIBUTE29'
331 ,p_attribute29_value => p_rec.attribute29
332 ,p_attribute30_name => 'ATTRIBUTE30'
333 ,p_attribute30_value => p_rec.attribute30
334 );
335 end if;
336 --
337 hr_utility.set_location(' Leaving:'||l_proc,20);
338 end chk_df;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |-----------------------< chk_non_updateable_args >------------------------|
342 -- ----------------------------------------------------------------------------
343 -- {Start Of Comments}
344 --
345 -- Description:
346 -- This procedure is used to ensure that non updateable attributes have
347 -- not been updated. If an attribute has been updated an error is generated.
348 --
349 -- Pre Conditions:
350 -- g_old_rec has been populated with details of the values currently in
351 -- the database.
352 --
353 -- In Arguments:
354 -- p_rec has been populated with the updated values the user would like the
355 -- record set to.
356 --
357 -- Post Success:
358 -- Processing continues if all the non updateable attributes have not
359 -- changed.
360 --
361 -- Post Failure:
362 -- An application error is raised if any of the non updatable attributes
363 -- have been altered.
364 --
365 -- {End Of Comments}
366 -- ----------------------------------------------------------------------------
367 Procedure chk_non_updateable_args
368 (p_effective_date in date
369 ,p_rec in pqh_psu_shd.g_rec_type
370 ) IS
371 --
372 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
373 --
374 Begin
375 --
376 -- Only proceed with the validation if a row exists for the current
377 -- record in the HR Schema.
378 --
379 IF NOT pqh_psu_shd.api_updating
380 (p_emp_stat_situation_id => p_rec.emp_stat_situation_id
381 ,p_object_version_number => p_rec.object_version_number
382 ) THEN
383 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
384 fnd_message.set_token('PROCEDURE ', l_proc);
385 fnd_message.set_token('STEP ', '5');
386 fnd_message.raise_error;
387 END IF;
388 --
389 --
390 if nvl(p_rec.statutory_situation_id, hr_api.g_number) <>
391 nvl(pqh_psu_shd.g_old_rec.statutory_situation_id
392 ,hr_api.g_number
393 ) then
394 hr_api.argument_changed_error
395 (p_api_name => l_proc
396 ,p_argument => 'STATUTORY_SITUATION_ID'
397 ,p_base_table => pqh_psu_shd.g_tab_nam
398 );
399 end if;
400 if nvl(p_rec.person_id, hr_api.g_number) <>
401 nvl(pqh_psu_shd.g_old_rec.person_id
402 ,hr_api.g_number
403 ) then
404 hr_api.argument_changed_error
405 (p_api_name => l_proc
406 ,p_argument => 'PERSON_ID'
407 ,p_base_table => pqh_psu_shd.g_tab_nam
408 );
409 end if;
410 End chk_non_updateable_args;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |---------------------------< chk_person_id >------------------------------|
414 -- ----------------------------------------------------------------------------
415 Procedure chk_person_id(p_person_id IN NUMBER) IS
416 CURSOR csr_valid_emp(p_person_id IN NUMBER) IS
417 SELECT 'X'
418 FROM per_all_people_f
419 WHERE person_id = p_person_id
420 AND TRUNC(SYSDATE) between effective_start_date and effective_end_date
421 AND PER_INFORMATION15 = '01'
422 AND current_employee_flag = 'Y';
423 l_valid_emp varchar2(10);
424 BEGIN
425 OPEN csr_valid_emp(p_person_id);
426 FETCH csr_valid_emp INTO l_valid_emp;
427 IF csr_valid_emp%NOTFOUND THEN
428 fnd_message.set_name('PQH','FR_PQH_STAT_SIT_INVALID_EMP');
429 hr_multi_message.add
430 (p_associated_column1
431 => 'PERSON_ID'
432 );
433 END IF;
434 CLOSE csr_valid_emp;
435 END;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |-----------------------< chk_statutory_situation >-------------------------|
439 -- ----------------------------------------------------------------------------
440 Procedure chk_statutory_situation(p_statutory_situation_id IN NUMBER) IS
441 CURSOR csr_valid_stat_sit(p_statutory_situation_id IN NUMBER) IS
442 SELECT 'X'
443 FROM pqh_fr_stat_situations
444 WHERE statutory_situation_id = p_statutory_situation_id
445 AND TRUNC(SYSDATE) BETWEEN date_from and NVL(date_to,TRUNC(SYSDATE));
446 l_valid_sit varchar2(10);
447 BEGIN
448 OPEN csr_valid_stat_sit(p_statutory_situation_id);
449 FETCH csr_valid_stat_sit INTO l_valid_sit;
450 IF csr_valid_stat_sit%NOTFOUND THEN
451 fnd_message.set_name('PQH','FR_PQH_STAT_SIT_INVALID_SIT');
452 hr_multi_message.add
453 (p_associated_column1
454 =>'STATUTORY_SITUATION_ID'
455 );
456 END IF;
457 CLOSE csr_valid_stat_sit;
458 END;
459 --
460 -- ----------------------------------------------------------------------------
461 -- |-------------------------< chk_renew_situation >--------------------------|
462 -- ----------------------------------------------------------------------------
463 /* deenath - This procedure has been replaced by new procedure having same name.
464 The new procedure is listed immediately after this commented block.
465 PROCEDURE chk_renew_situation(p_rec IN pqh_psu_shd.g_rec_type) IS
466 --
467 --Cursor to fetch Renew Details for the Situation Id.
468 CURSOR csr_renew_dtls IS
469 SELECT frequency,
470 NVL(renewable_allowed,'N'),
471 NVL(max_no_of_renewals,0),
472 NVL(max_duration_per_renewal,0),
473 NVL(max_tot_continuous_duration,0)
474 FROM pqh_fr_stat_situations
475 WHERE statutory_situation_id = p_rec.statutory_situation_id;
476 --
477 --Cursor to fetch continous renewal duration,
478 CURSOR csr_cont_renewals(p_date DATE) IS
479 SELECT NVL(actual_start_date,provisional_start_date),
480 NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date) days
481 FROM pqh_fr_emp_stat_situations
482 WHERE person_id = p_rec.person_id
483 AND statutory_situation_id = p_rec.statutory_situation_id
484 AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
485 AND((NVL(renewal_flag,'N') = 'N' AND emp_stat_situation_id = p_rec.renew_stat_situation_id)
486 OR (NVL(renewal_flag,'N') = 'Y' AND renew_stat_situation_id = p_rec.renew_stat_situation_id))
487 AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date);
488 --
489 --Variable Declarations.
490 l_renewable VARCHAR2(10);
491 l_freq VARCHAR2(10);
492 l_max_renewals NUMBER(10) := 0;
493 l_no_of_renewals NUMBER(10) := 0;
494 l_max_dur_per_renew NUMBER(10) := 0;
495 l_max_tot_cont_dur NUMBER(10) := 0;
496 l_day_factor NUMBER(10) := 0;
497 l_date DATE;
498 l_days NUMBER(10) := 0;
499 l_cont_days NUMBER(10) := 0;
500 l_duration_days NUMBER(10) := 0;
501 --
502 BEGIN
503 --
504 IF p_rec.renewal_flag = 'Y' THEN
505 --
506 OPEN csr_renew_dtls;
507 FETCH csr_renew_dtls INTO l_freq, l_renewable, l_max_renewals, l_max_dur_per_renew, l_max_tot_cont_dur;
508 CLOSE csr_renew_dtls;
509 --
510 IF l_renewable = 'N' THEN
511 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_NOT_RENEWABLE');
512 HR_MULTI_MESSAGE.add;
513 END IF;
514 --
515 l_no_of_renewals := pqh_fr_stat_sit_util.get_num_renewals(p_rec.emp_stat_situation_id,p_rec.renew_stat_situation_id);
516 --
517 IF l_no_of_renewals >= l_max_renewals THEN
518 FND_MESSAGE.set_name('PQH','FR_PQH_MAX_RENEWALS_REACHED');
519 HR_MULTI_MESSAGE.add;
520 END IF;
521 --
522 IF l_freq = 'BM' THEN
523 l_day_factor := 60;
524 ELSIF l_freq = 'CM' THEN
525 l_day_factor := 30;
526 ELSIF l_freq = 'F' THEN
527 l_day_factor := 14;
528 ELSIF l_freq = 'LM' THEN
529 l_day_factor := 15;
530 ELSIF l_freq = 'Q' THEN
531 l_day_factor := 90;
532 ELSIF l_freq = 'SM' THEN
533 l_day_factor := 15;
534 ELSIF l_freq = 'SY' THEN
535 l_day_factor := 182;
536 ELSIF l_freq = 'W' THEN
537 l_day_factor := 7;
538 ELSIF l_freq = 'Y' THEN
539 l_day_factor := 365;
540 END IF;
541 --
542 l_duration_days := p_rec.provisional_end_date - p_rec.provisional_start_date;
543 --
544 IF l_duration_days > (l_max_dur_per_renew*l_day_factor) THEN
545 FND_MESSAGE.set_name ('PQH','FR_PQH_RENEW_OUT_OF_MAX_RANGE');
546 HR_MULTI_MESSAGE.add;
547 END IF;
548 --
549 l_date := p_rec.provisional_start_date;
550 WHILE l_date IS NOT NULL
551 LOOP
552 OPEN csr_cont_renewals(l_date-1);
553 FETCH csr_cont_renewals INTO l_date,l_days;
554 IF csr_cont_renewals%FOUND THEN
555 l_cont_days := l_cont_days + l_days;
556 END IF;
557 IF csr_cont_renewals%NOTFOUND THEN
558 l_date := NULL;
559 END IF;
560 CLOSE csr_cont_renewals;
561 END LOOP;
562 --
563 IF (l_duration_days+NVL(l_cont_days,0)) > (l_max_tot_cont_dur*l_day_factor) THEN
564 FND_MESSAGE.set_name ('PQH', 'FR_PQH_RENEW_TOT_CONT_RANGE');
565 HR_MULTI_MESSAGE.add;
566 END IF;
567 --
568 END IF;
569 --
570 END;
571 */
572 --
573 -- ----------------------------------------------------------------------------
574 -- |-------------------------< chk_renew_situation >--------------------------|
575 -- ----------------------------------------------------------------------------
576 PROCEDURE chk_renew_situation(p_rec IN pqh_psu_shd.g_rec_type) IS
577 --
578 --Cursor to fetch Renew Details for the Situation Id.
579 CURSOR csr_renew_dtls IS
580 SELECT frequency,
581 NVL(renewable_allowed,'N'),
582 max_no_of_renewals,
583 max_duration_per_renewal,
584 max_tot_continuous_duration
585 FROM pqh_fr_stat_situations
586 WHERE statutory_situation_id = p_rec.statutory_situation_id;
587 --
588 --Cursor to fetch continous renewal duration,
589 CURSOR csr_cont_renewals(p_date DATE) IS
590 SELECT NVL(actual_start_date,provisional_start_date),
591 (NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date)+1) days
592 FROM pqh_fr_emp_stat_situations
593 WHERE person_id = p_rec.person_id
594 AND statutory_situation_id = p_rec.statutory_situation_id
595 AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
596 AND((NVL(renewal_flag,'N') = 'N' AND emp_stat_situation_id = p_rec.renew_stat_situation_id)
597 OR (NVL(renewal_flag,'N') = 'Y' AND renew_stat_situation_id = p_rec.renew_stat_situation_id))
598 AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date);
599 --
600 --Variable Declarations.
601 l_renewable VARCHAR2(10);
602 l_freq VARCHAR2(10);
603 l_max_renewals NUMBER(10);
604 l_no_of_renewals NUMBER(10);
605 l_max_dur_per_renew NUMBER(10);
606 l_max_tot_cont_dur NUMBER(10);
607 l_day_factor NUMBER(10);
608 l_date DATE;
609 l_days NUMBER(10);
610 l_cont_days NUMBER(10);
611 l_duration_days NUMBER(10);
612 l_start_date DATE;
613 l_mdpr DATE;
614 l_mtcd DATE;
615 --
616 BEGIN
617 --
618 IF NVL(p_rec.renewal_flag,'N') = 'Y' THEN
619 --
620 OPEN csr_renew_dtls;
621 FETCH csr_renew_dtls INTO l_freq, l_renewable, l_max_renewals, l_max_dur_per_renew, l_max_tot_cont_dur;
622 CLOSE csr_renew_dtls;
623 --
624 IF l_renewable = 'N' THEN
625 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_NOT_RENEWABLE');
626 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
627 END IF;
628 --
629 l_no_of_renewals := pqh_fr_stat_sit_util.get_num_renewals(p_rec.emp_stat_situation_id,p_rec.renew_stat_situation_id);
630 --
631 IF l_max_renewals IS NOT NULL AND NVL(l_no_of_renewals,0) >= l_max_renewals THEN
632 FND_MESSAGE.set_name('PQH','FR_PQH_MAX_RENEWALS_REACHED');
633 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
634 END IF;
635 --
636 l_start_date := TRUNC(NVL(p_rec.actual_start_date,p_rec.provisional_start_date));
637 --
638 IF l_freq = 'Y' THEN
639 l_mdpr := TRUNC(ADD_MONTHS(l_start_date,l_max_dur_per_renew*12));
640 l_mtcd := TRUNC(ADD_MONTHS(l_start_date,l_max_tot_cont_dur*12));
641 ELSIF l_freq = 'SY' THEN
642 l_mdpr := TRUNC(ADD_MONTHS(l_start_date,l_max_dur_per_renew*6));
643 l_mtcd := TRUNC(ADD_MONTHS(l_start_date,l_max_tot_cont_dur*6));
644 ELSIF l_freq = 'Q' THEN
645 l_mdpr := TRUNC(ADD_MONTHS(l_start_date,l_max_dur_per_renew*3));
646 l_mtcd := TRUNC(ADD_MONTHS(l_start_date,l_max_tot_cont_dur*3));
647 ELSIF l_freq = 'BM' THEN
648 l_mdpr := TRUNC(ADD_MONTHS(l_start_date,l_max_dur_per_renew*2));
649 l_mtcd := TRUNC(ADD_MONTHS(l_start_date,l_max_tot_cont_dur*2));
650 ELSIF l_freq = 'CM' THEN
651 l_mdpr := TRUNC(ADD_MONTHS(l_start_date,l_max_dur_per_renew));
652 l_mtcd := TRUNC(ADD_MONTHS(l_start_date,l_max_tot_cont_dur));
653 ELSIF l_freq = 'LM' THEN
654 l_mdpr := TRUNC(l_start_date+(l_max_dur_per_renew*28));
655 l_mtcd := TRUNC(l_start_date+(l_max_tot_cont_dur*28));
656 ELSIF l_freq = 'F' THEN
657 l_mdpr := TRUNC(l_start_date+(l_max_dur_per_renew*14));
658 l_mtcd := TRUNC(l_start_date+(l_max_tot_cont_dur*14));
659 ELSIF l_freq = 'W' THEN
660 l_mdpr := TRUNC(l_start_date+(l_max_dur_per_renew*7));
661 l_mtcd := TRUNC(l_start_date+(l_max_tot_cont_dur*7));
662 ELSIF l_freq = 'SM' THEN
663 l_mdpr := l_start_date;
664 FOR i IN 1..NVL(l_max_dur_per_renew,0)
665 LOOP
666 l_mdpr := TRUNC(l_mdpr+TRUNC(((ADD_MONTHS(l_mdpr,1)-l_mdpr)/2)));
667 END LOOP;
668 --
669 l_mtcd := l_start_date;
670 FOR i IN 1..NVL(l_max_tot_cont_dur,0)
671 LOOP
672 l_mtcd := TRUNC(l_mtcd+TRUNC(((ADD_MONTHS(l_mtcd,1)-l_mtcd)/2)));
673 END LOOP;
674 --
675 ELSE
676 l_mdpr := HR_GENERAL.end_of_time;
677 l_mtcd := HR_GENERAL.end_of_time;
678 END IF;
679 --
680 IF NVL(l_mdpr,l_start_date) = l_start_date THEN
681 l_mdpr := HR_GENERAL.end_of_time;
682 END IF;
683 --
684 IF NVL(l_mtcd,l_start_date) = l_start_date THEN
685 l_mtcd := HR_GENERAL.end_of_time;
686 END IF;
687 --
688 IF l_max_dur_per_renew IS NOT NULL AND NVL(p_rec.actual_end_date,p_rec.provisional_end_date) >= l_mdpr THEN
689 FND_MESSAGE.set_name ('PQH','FR_PQH_RENEW_OUT_OF_MAX_RANGE');
690 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
691 END IF;
692 --
693 l_cont_days := 0;
694 l_date := p_rec.provisional_start_date;
695 WHILE l_date IS NOT NULL
696 LOOP
697 OPEN csr_cont_renewals(l_date-1);
698 FETCH csr_cont_renewals INTO l_date,l_days;
699 IF csr_cont_renewals%FOUND THEN
700 l_cont_days := l_cont_days+l_days;
701 END IF;
702 IF csr_cont_renewals%NOTFOUND THEN
703 l_date := NULL;
704 END IF;
705 CLOSE csr_cont_renewals;
706 END LOOP;
707 --
708 --Use (l_mtcd-1) because l_mtcd calculation adds one more day.
709 IF l_max_tot_cont_dur IS NOT NULL AND
710 (TRUNC(NVL(p_rec.actual_end_date,p_rec.provisional_end_date)+NVL(l_cont_days,0)) > (l_mtcd-1)) THEN
711 FND_MESSAGE.set_name ('PQH', 'FR_PQH_RENEW_TOT_CONT_RANGE');
712 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
713 END IF;
714 --
715 END IF;
716 --
717 END chk_renew_situation;
718 --
719 -- ---------------------------------------------------------------------------
720 -- ------------------------< chk_dates >--------------------------------------
721 -- --------------------------------------------------------------------------
722 Procedure chk_dates(p_person_id in NUMBER,
723 p_statutory_situation_id IN NUMBER,
724 p_provisional_start_date IN Date,
725 p_provisional_end_date IN Date )
726 IS
727 --
728 Cursor csr_person_info IS
729 Select original_date_of_hire
730 from per_all_people_f
731 where person_id = p_person_id
732 and trunc(sysdate) between effective_start_date and effective_end_date;
733 --
734 Cursor csr_situation_info IS
735 Select nvl(date_to,hr_general.end_of_time)
736 from pqh_fr_stat_situations
737 where statutory_situation_id = p_statutory_situation_id;
738 l_original_hire_date date;
739 l_situ_end_date date;
740 Begin
741 --
742 if (p_provisional_start_date > p_provisional_end_date ) then
743 --
744 fnd_message.set_name('PQH','PQH_FR_NO_GRT_STARTDT');
745 hr_multi_message.add();
746 --
747 End If;
748 Open csr_person_info;
749 Fetch csr_person_info into l_original_hire_date;
750 Close csr_person_info;
751 If (p_provisional_start_date< l_original_hire_date ) Then
752 --
753 fnd_message.set_name('PQH','PQH_FR_NOSIT_BFOR_HIREDT');
754 hr_multi_message.add();
755 --
756 End If;
757 Open csr_situation_info;
758 Fetch csr_situation_info into l_situ_end_date;
759 Close csr_situation_info;
760 If (p_provisional_end_date > l_situ_end_date ) Then
761 --
762 fnd_message.set_name('PQH','PQH_FR_SITU_ENDDT_IS_LESS');
763 hr_multi_message.add;
764 --
765 End If;
766 End;
767 --
768 -- ----------------------------------------------------------------------------
769 -- |-----------------------< chk_situation_dates >----------------------------|
770 -- ----------------------------------------------------------------------------
771 PROCEDURE chk_situation_dates(p_rec IN pqh_psu_shd.g_rec_type)
772 IS
773 --
774 CURSOR csr_default_sit IS
775 SELECT 'x' FROM DUAL
776 WHERE EXISTS(SELECT statutory_situation_id
777 FROM pqh_fr_stat_situations
778 WHERE statutory_situation_id = p_rec.statutory_situation_id
779 AND situation_type = 'IA'
780 AND sub_type = 'IA_N'
781 AND business_group_id = HR_GENERAL.get_business_group_id
782 AND default_flag = 'Y'
783 AND TRUNC(SYSDATE) BETWEEN date_from AND NVL(date_to,HR_GENERAL.end_of_time));
784 --
785 CURSOR csr_overlap_dates(p_provisional_start DATE,
786 p_provisional_end DATE,
787 p_actual_start DATE,
788 p_actual_end DATE,
789 p_person_id NUMBER,
790 p_emp_stat_situation_id NUMBER) IS
791 SELECT 'x'
792 FROM DUAL
793 WHERE EXISTS(SELECT 'x'
794 FROM pqh_fr_emp_stat_situations
795 WHERE person_id = p_person_id
796 AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
797 -- AND NVL(renew_stat_situation_id,-2) <> NVL(p_emp_stat_situation_id,-1) --commented by deenath
798 AND statutory_situation_id <>
799 (SELECT statutory_situation_id
800 FROM pqh_fr_stat_situations_v sit,
801 per_shared_types_vl sh
802 WHERE sh.shared_type_id = type_of_ps
803 AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
804 AND sit.business_group_id = HR_GENERAL.get_business_group_id
805 AND sit.default_flag = 'Y'
806 AND sit.situation_type = 'IA'
807 AND sit.sub_type = 'IA_N'
808 AND TRUNC(SYSDATE) BETWEEN date_from AND NVL(date_to,HR_GENERAL.end_of_time))
809 AND(NVL(p_actual_start,p_provisional_start) BETWEEN NVL(actual_start_date,provisional_start_date)
810 AND NVL(actual_end_date,NVL(provisional_end_date,hr_general.end_of_time))
811 OR NVL(p_actual_end,p_provisional_end) BETWEEN NVL(actual_end_date,provisional_end_date)
812 AND NVL(actual_end_date,NVL(provisional_end_date,hr_general.end_of_time))));
813 --
814 l_default_sit VARCHAR2(10);
815 l_valid VARCHAR2(10);
816 --
817 BEGIN
818 --
819 IF (p_rec.renew_stat_situation_id IS NOT NULL) THEN
820 RETURN;
821 END IF;
822 --
823 IF p_rec.emp_stat_situation_id IS NOT NULL THEN
824 OPEN csr_default_sit;
825 FETCH csr_default_sit INTO l_default_sit;
826 IF csr_default_sit%FOUND THEN
827 CLOSE csr_default_sit;
828 RETURN; --Return because we dont want to check overlaps if Sit being created/updated is Default In Activity Normal Situation
829 END IF;
830 IF csr_default_sit%ISOPEN THEN
831 CLOSE csr_default_sit;
832 END IF;
833 END IF;
834 --
835 OPEN csr_overlap_dates(p_rec.provisional_start_date,p_rec.provisional_end_date,
836 p_rec.actual_start_date,p_rec.actual_end_date,
837 p_rec.person_id,p_rec.emp_stat_situation_id);
838 FETCH csr_overlap_dates INTO l_valid;
839 IF csr_overlap_dates%FOUND THEN
840 CLOSE csr_overlap_dates;
841 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_OVERLAP_DATES');
842 HR_MULTI_MESSAGE.add(p_associated_column1 => 'PROVISIONAL_START_DATE'
843 ,p_associated_column2 => 'PROVISIONAL_END_DATE'
844 ,p_associated_column3 => 'ACTUAL_START_DATE'
845 ,p_associated_column4 => 'ACTUAL_END_DATE');
846 END IF;
847 IF csr_overlap_dates%ISOPEN THEN
848 CLOSE csr_overlap_dates;
849 END IF;
850 --
851 END chk_situation_dates;
852 --
853 -- ----------------------------------------------------------------------------
854 -- |-----------------------< chk_contact_details >----------------------------|
855 -- ----------------------------------------------------------------------------
856 Procedure chk_contact_details(p_rec IN pqh_psu_shd.g_rec_type) IS
857 CURSOR csr_contact_dtls(p_contact_person_id NUMBER,
858 p_person_id NUMBER,
859 p_contact_relationship VARCHAR2) IS
860 SELECT date_start,date_end
861 FROM per_contact_relationships
862 WHERE contact_person_id = p_contact_person_id
863 AND person_id = p_person_id
864 AND contact_type = p_contact_relationship;
865 lr_contact csr_contact_dtls%ROWTYPE;
866 BEGIN
867 If (p_rec.contact_person_id IS NOT NULL and p_rec.contact_relationship IS NOT NULL) Then
868 --
869 OPEN Csr_contact_dtls(p_rec.contact_person_id, p_rec.person_id,p_rec.contact_relationship);
870 FETCH csr_contact_dtls INTO lr_contact.date_start, lr_contact.date_end;
871 IF csr_contact_dtls%NOTFOUND THEN
872 CLOSE csr_contact_dtls;
873 fnd_message.set_name('PQH','FR_PQH_STAT_SIT_INVALID_CNTCT');
874 hr_multi_message.add
875 (p_associated_column1
876 => 'CONTACT_PERSON_ID'
877 ,p_associated_column2
878 => 'CONTACT_RELATIONSHIP'
879 );
880 END IF;
881 IF csr_contact_dtls%ISOPEN THEN
882 CLOSE csr_contact_dtls;
883 END IF;
884 End If;
885 END;
886 --
887 -- ----------------------------------------------------------------------------
888 -- |-----------------------< chk_duration_date >-------------------------------|
889 -- ----------------------------------------------------------------------------
890 /* deenath - This procedure has been replaced by new procedure having same name.
891 The new procedure is listed immediately after this commented block.
892 PROCEDURE chk_duration_date(p_rec IN pqh_psu_shd.g_rec_type)
893 IS
894 --
895 --Cursor to check if Situation exists for Person. Modified by deenath added emp_stat_sit clause.
896 CURSOR csr_emp_stat_situation IS
897 SELECT 'X' x
898 FROM pqh_fr_emp_stat_situations
899 WHERE person_id = p_rec.person_id
900 AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
901 AND statutory_situation_id = p_rec.statutory_situation_id;
902 --
903 --Cursor to get Situation Durations.
904 CURSOR csr_stat_situation IS
905 SELECT frequency,
906 first_period_max_duration,
907 min_duration_per_request,
908 max_duration_per_request,
909 max_duration_whole_career
910 FROM pqh_fr_stat_situations
911 WHERE statutory_situation_id = p_rec.statutory_situation_id;
912 --
913 --Cursor to fetch total duration for the person. Modified by deenath added emp_stat_sit clause.
914 CURSOR csr_duration_days IS
915 SELECT SUM(days) total_duration_days
916 FROM(SELECT NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date) days
917 FROM pqh_fr_emp_stat_situations
918 WHERE person_id = p_rec.person_id
919 AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
920 AND statutory_situation_id = p_rec.statutory_situation_id);
921 --
922 --Variable Declarations
923 lr_previous_exist csr_emp_stat_situation%ROWTYPE;
924 lr_stat_sit_rec csr_stat_situation%ROWTYPE;
925 l_duration_days NUMBER;
926 l_day_factor NUMBER;
927 l_proc VARCHAR2(72) := g_package||'chk_duration_date';
928 l_total_duration_days NUMBER;
929 --
930 BEGIN
931 --
932 HR_UTILITY.set_location ('Entering:' || l_proc, 5);
933 --
934 OPEN csr_emp_stat_situation;
935 FETCH csr_emp_stat_situation INTO lr_previous_exist;
936 CLOSE csr_emp_stat_situation;
937 --
938 OPEN csr_stat_situation;
939 FETCH csr_stat_situation INTO lr_stat_sit_rec;
940 CLOSE csr_stat_situation;
941 --
942 OPEN csr_duration_days;
943 FETCH csr_duration_days INTO l_total_duration_days;
944 CLOSE csr_duration_days;
945 --
946 --Codes and its Equalient Days
947 --BM - 60
948 --CM - 30
949 --F - 14
950 --LM - 15
951 --Q - 90
952 --SM - 15
953 --SY -182
954 --W - 7
955 --Y -365
956 --
957 IF lr_stat_sit_rec.frequency = 'BM' THEN
958 l_day_factor := 60;
959 ELSIF lr_stat_sit_rec.frequency = 'CM' THEN
960 l_day_factor := 30;
961 ELSIF lr_stat_sit_rec.frequency = 'F' THEN
962 l_day_factor := 14;
963 ELSIF lr_stat_sit_rec.frequency = 'LM' THEN
964 l_day_factor := 15;
965 ELSIF lr_stat_sit_rec.frequency = 'Q' THEN
966 l_day_factor := 90;
967 ELSIF lr_stat_sit_rec.frequency = 'SM' THEN
968 l_day_factor := 15;
969 ELSIF lr_stat_sit_rec.frequency = 'SY' THEN
970 l_day_factor := 182;
971 ELSIF lr_stat_sit_rec.frequency = 'W' THEN
972 l_day_factor := 7;
973 ELSIF lr_stat_sit_rec.frequency = 'Y' THEN
974 l_day_factor := 365;
975 END IF;
976 --
977 l_duration_days := p_rec.provisional_end_date - p_rec.provisional_start_date;
978 --
979 --If Situation does not exist.
980 IF lr_previous_exist.x IS NULL THEN
981 IF l_duration_days > (lr_stat_sit_rec.first_period_max_duration*l_day_factor) THEN
982 FND_MESSAGE.set_name ('PQH','FR_PQH_EXCEEDS_FIRST_DURATION');
983 HR_MULTI_MESSAGE.add;
984 END IF;
985 --If Situation exists.
986 ELSIF l_duration_days > (lr_stat_sit_rec.max_duration_per_request*l_day_factor) THEN
987 FND_MESSAGE.set_name ('PQH', 'FR_PQH_PERIOD_OUT_OF_MAX_RANGE');
988 HR_MULTI_MESSAGE.add;
989 END IF;
990 --
991 --Modified by deenath. Moved below condition out of "If Situation Exists" condition.
992 IF l_duration_days < (lr_stat_sit_rec.min_duration_per_request*l_day_factor) THEN
993 FND_MESSAGE.set_name ('PQH', 'FR_PQH_PERIOD_OUT_OF_MIN_RANGE');
994 HR_MULTI_MESSAGE.add;
995 END IF;
996 --
997 IF l_duration_days+NVL(l_total_duration_days,0) > (lr_stat_sit_rec.max_duration_whole_career*l_day_factor) THEN
998 FND_MESSAGE.set_name ('PQH', 'FR_PQH_DURATION_LIMIT_EXCEEDS');
999 HR_MULTI_MESSAGE.add;
1000 END IF;
1001 --
1002 HR_UTILITY.set_location ('Leaving: '||l_proc,5);
1003 --
1004 END chk_duration_date;
1005 */
1006 --
1007 -- ----------------------------------------------------------------------------
1008 -- |-----------------------< chk_duration_date >-------------------------------|
1009 -- ----------------------------------------------------------------------------
1010 PROCEDURE chk_duration_date(p_rec IN pqh_psu_shd.g_rec_type)
1011 IS
1012 --
1013 --Cursor to check if Situation exists for Person. Modified by deenath added emp_stat_sit clause.
1014 CURSOR csr_emp_stat_situation IS
1015 SELECT 'Y'
1016 FROM pqh_fr_emp_stat_situations
1017 WHERE person_id = p_rec.person_id
1018 AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
1019 AND statutory_situation_id = p_rec.statutory_situation_id;
1020 --
1021 --Cursor to get Situation Durations.
1022 CURSOR csr_stat_situation IS
1023 SELECT situation_type,
1024 sub_type,
1025 frequency,
1026 first_period_max_duration,
1027 max_duration_whole_career,
1028 max_duration_per_request,
1029 min_duration_per_request
1030 FROM pqh_fr_stat_situations
1031 WHERE statutory_situation_id = p_rec.statutory_situation_id;
1032 --
1033 --Cursor to fetch total duration for the person. Modified by deenath added emp_stat_sit clause.
1034 CURSOR csr_duration_days IS
1035 SELECT NVL(SUM(days),0) total_duration_days
1036 FROM(SELECT (NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date)+1) days
1037 FROM pqh_fr_emp_stat_situations
1038 WHERE person_id = p_rec.person_id
1039 AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
1040 AND statutory_situation_id = p_rec.statutory_situation_id);
1041 --
1042 --Variable Declarations
1043 l_previous_exist VARCHAR2(01);
1044 lr_stat_sit_rec csr_stat_situation%ROWTYPE;
1045 l_duration_days NUMBER;
1046 l_day_factor NUMBER;
1047 l_proc VARCHAR2(72) := g_package||'chk_duration_date';
1048 l_total_duration_days NUMBER;
1049 l_start_date DATE;
1050 l_fpmd DATE;
1051 l_mdwc DATE;
1052 l_max_dpr DATE;
1053 l_min_dpr DATE;
1054 --
1055 BEGIN
1056 --
1057 HR_UTILITY.set_location ('Entering:' || l_proc, 5);
1058 --
1059 OPEN csr_emp_stat_situation;
1060 FETCH csr_emp_stat_situation INTO l_previous_exist;
1061 CLOSE csr_emp_stat_situation;
1062 --
1063 OPEN csr_stat_situation;
1064 FETCH csr_stat_situation INTO lr_stat_sit_rec;
1065 CLOSE csr_stat_situation;
1066 --
1067 OPEN csr_duration_days;
1068 FETCH csr_duration_days INTO l_total_duration_days;
1069 CLOSE csr_duration_days;
1070 --
1071 IF lr_stat_sit_rec.situation_type = 'IA' AND lr_stat_sit_rec.sub_type = 'IA_N' THEN
1072 RETURN;
1073 END IF;
1074 --
1075 l_start_date := TRUNC(NVL(p_rec.actual_start_date,p_rec.provisional_start_date));
1076 --
1077 IF lr_stat_sit_rec.frequency = 'Y' THEN --Year (12 months)
1078 --
1079 l_fpmd := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.first_period_max_duration*12));
1080 l_mdwc := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_whole_career*12));
1081 l_max_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_per_request*12));
1082 l_min_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.min_duration_per_request*12));
1083 --
1084 ELSIF lr_stat_sit_rec.frequency = 'SY' THEN --Semi Year (6 months)
1085 --
1086 l_fpmd := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.first_period_max_duration*6));
1087 l_mdwc := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_whole_career*6));
1088 l_max_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_per_request*6));
1089 l_min_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.min_duration_per_request*6));
1090 --
1091 ELSIF lr_stat_sit_rec.frequency = 'Q' THEN --Quarter (3 months)
1092 --
1093 l_fpmd := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.first_period_max_duration*3));
1094 l_mdwc := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_whole_career*3));
1095 l_max_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_per_request*3));
1096 l_min_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.min_duration_per_request*3));
1097 --
1098 ELSIF lr_stat_sit_rec.frequency = 'BM' THEN --BiMonthly (2 months)
1099 --
1100 l_fpmd := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.first_period_max_duration*2));
1101 l_mdwc := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_whole_career*2));
1102 l_max_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_per_request*2));
1103 l_min_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.min_duration_per_request*2));
1104 --
1105 ELSIF lr_stat_sit_rec.frequency = 'CM' THEN --Calendar Month (1 month)
1106 --
1107 l_fpmd := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.first_period_max_duration));
1108 l_mdwc := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_whole_career));
1109 l_max_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.max_duration_per_request));
1110 l_min_dpr := TRUNC(ADD_MONTHS(l_start_date,lr_stat_sit_rec.min_duration_per_request));
1111 --
1112 ELSIF lr_stat_sit_rec.frequency = 'LM' THEN --Lunar Month (28 days)
1113 --
1114 l_fpmd := TRUNC(l_start_date+(lr_stat_sit_rec.first_period_max_duration*28));
1115 l_mdwc := TRUNC(l_start_date+(lr_stat_sit_rec.max_duration_whole_career*28));
1116 l_max_dpr := TRUNC(l_start_date+(lr_stat_sit_rec.max_duration_per_request*28));
1117 l_min_dpr := TRUNC(l_start_date+(lr_stat_sit_rec.min_duration_per_request*28));
1118 --
1119 ELSIF lr_stat_sit_rec.frequency = 'F' THEN --Bi Weekly (14 days)
1120 --
1121 l_fpmd := TRUNC(l_start_date+(lr_stat_sit_rec.first_period_max_duration*14));
1122 l_mdwc := TRUNC(l_start_date+(lr_stat_sit_rec.max_duration_whole_career*14));
1123 l_max_dpr := TRUNC(l_start_date+(lr_stat_sit_rec.max_duration_per_request*14));
1124 l_min_dpr := TRUNC(l_start_date+(lr_stat_sit_rec.min_duration_per_request*14));
1125 --
1126 ELSIF lr_stat_sit_rec.frequency = 'W' THEN --Weekly (7 days)
1127 --
1128 l_fpmd := TRUNC(l_start_date+(lr_stat_sit_rec.first_period_max_duration*7));
1129 l_mdwc := TRUNC(l_start_date+(lr_stat_sit_rec.max_duration_whole_career*7));
1130 l_max_dpr := TRUNC(l_start_date+(lr_stat_sit_rec.max_duration_per_request*7));
1131 l_min_dpr := TRUNC(l_start_date+(lr_stat_sit_rec.min_duration_per_request*7));
1132 --
1133 ELSIF lr_stat_sit_rec.frequency = 'SM' THEN --Semi Month
1134 --
1135 l_fpmd := l_start_date;
1136 FOR i IN 1..NVL(lr_stat_sit_rec.first_period_max_duration,0)
1137 LOOP
1138 l_fpmd := TRUNC(l_fpmd+TRUNC(((ADD_MONTHS(l_fpmd,1)-l_fpmd)/2)));
1139 END LOOP;
1140 --
1141 l_mdwc := l_start_date;
1142 FOR i IN 1..NVL(lr_stat_sit_rec.max_duration_whole_career,0)
1143 LOOP
1144 l_mdwc := TRUNC(l_mdwc+TRUNC(((ADD_MONTHS(l_mdwc,1)-l_mdwc)/2)));
1145 END LOOP;
1146 --
1147 l_max_dpr := l_start_date;
1148 FOR i IN 1..NVL(lr_stat_sit_rec.max_duration_per_request,0)
1149 LOOP
1150 l_max_dpr := TRUNC(l_max_dpr+TRUNC(((ADD_MONTHS(l_max_dpr,1)-l_max_dpr)/2)));
1151 END LOOP;
1152 --
1153 l_min_dpr := l_start_date;
1154 FOR i IN 1..NVL(lr_stat_sit_rec.min_duration_per_request,0)
1155 LOOP
1156 l_min_dpr := TRUNC(l_min_dpr+TRUNC(((ADD_MONTHS(l_min_dpr,1)-l_min_dpr)/2)));
1157 END LOOP;
1158 --
1159 ELSE
1160 --
1161 l_fpmd := HR_GENERAL.end_of_time;
1162 l_min_dpr := l_start_date;
1163 l_max_dpr := HR_GENERAL.end_of_time;
1164 l_mdwc := HR_GENERAL.end_of_time;
1165 --
1166 END IF;
1167 --
1168 IF NVL(l_fpmd,l_start_date) = l_start_date THEN
1169 l_fpmd := HR_GENERAL.end_of_time;
1170 END IF;
1171 --
1172 IF NVL(l_mdwc,l_start_date) = l_start_date THEN
1173 l_mdwc := HR_GENERAL.end_of_time;
1174 END IF;
1175 --
1176 IF NVL(l_max_dpr,l_start_date) = l_start_date THEN
1177 l_max_dpr := HR_GENERAL.end_of_time;
1178 END IF;
1179 --
1180 IF NVL(l_min_dpr,HR_GENERAL.end_of_time) = HR_GENERAL.end_of_time THEN
1181 l_min_dpr := l_start_date;
1182 END IF;
1183 --
1184 IF NVL(l_previous_exist,'N') = 'Y' THEN --If Situation exists.
1185 --
1186 IF NVL(p_rec.actual_end_date,p_rec.provisional_end_date) >= l_max_dpr THEN
1187 FND_MESSAGE.set_name('PQH','FR_PQH_PERIOD_OUT_OF_MAX_RANGE');
1188 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
1189 END IF;
1190 --
1191 ELSE --If Situation does not exist.
1192 --
1193 IF NVL(p_rec.actual_end_date,p_rec.provisional_end_date) >= l_fpmd THEN
1194 FND_MESSAGE.set_name ('PQH','FR_PQH_EXCEEDS_FIRST_DURATION');
1195 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
1196 END IF;
1197 --
1198 END IF;
1199 --
1200 --Use (l_min_dpr-1) because Calculated Date is one day more than valid Minimum Start Date.
1201 IF NVL(p_rec.actual_end_date,p_rec.provisional_end_date) < TRUNC(l_min_dpr-1) THEN
1202 FND_MESSAGE.set_name ('PQH','FR_PQH_PERIOD_OUT_OF_MIN_RANGE');
1203 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
1204 END IF;
1205 --
1206 --Use (l_mdwc-1) because Calculated Date is one day more than valid Maximum Duration Whole Career.
1207 IF TRUNC(NVL(p_rec.actual_end_date,p_rec.provisional_end_date)+NVL(l_total_duration_days,0)) > TRUNC(l_mdwc-1) THEN
1208 FND_MESSAGE.set_name ('PQH','FR_PQH_DURATION_LIMIT_EXCEEDS');
1209 FND_MESSAGE.raise_error;--HR_MULTI_MESSAGE.add;
1210 END IF;
1211 --
1212 HR_UTILITY.set_location ('Leaving: '||l_proc,5);
1213 --
1214 END chk_duration_date;
1215 --
1216 -- ----------------------------------------------------------------------------
1217 -- |---------------------------< insert_validate >----------------------------|
1218 -- ----------------------------------------------------------------------------
1219 PROCEDURE insert_validate(p_effective_date IN DATE
1220 ,p_rec IN pqh_psu_shd.g_rec_type)
1221 IS
1222 --
1223 l_proc varchar2(72) := g_package||'insert_validate';
1224 --
1225 BEGIN
1226 --
1227 hr_utility.set_location('Entering:'||l_proc,5);
1228 --
1229 --Call all supporting business operations
1230 chk_person_id(p_rec.person_id);
1231 chk_statutory_situation(p_rec.statutory_situation_id);
1232 chk_renew_situation(p_rec);
1233 chk_situation_dates(p_rec);
1234 chk_contact_details(p_rec);
1235 chk_dates(p_rec.person_id,p_rec.statutory_situation_id,
1236 p_rec.provisional_start_date,p_rec.provisional_end_date);
1237 chk_duration_date(p_rec);
1238 --
1239 pqh_psu_bus.set_security_group_id(p_rec.statutory_situation_id);
1240 --
1241 --Validate Dependent Attributes
1242 pqh_psu_bus.chk_df(p_rec);
1243 --
1244 hr_utility.set_location('Leaving:'||l_proc,10);
1245 --
1246 END insert_validate;
1247 --
1248 -- ----------------------------------------------------------------------------
1249 -- |---------------------------< update_validate >----------------------------|
1250 -- ----------------------------------------------------------------------------
1251 PROCEDURE update_validate(p_effective_date IN DATE
1252 ,p_rec IN pqh_psu_shd.g_rec_type)
1253 IS
1254 --
1255 l_proc varchar2(72) := g_package||'update_validate';
1256 --
1257 BEGIN
1258 --
1259 hr_utility.set_location('Entering:'||l_proc, 5);
1260 --
1261 --Call all supporting business operations
1262 chk_person_id(p_rec.person_id);
1263 chk_statutory_situation(p_rec.statutory_situation_id);
1264 chk_renew_situation(p_rec); --Added by deenath for Renew duration checks.
1265 chk_situation_dates(p_rec);
1266 chk_contact_details(p_rec);
1267 chk_dates(p_rec.person_id,p_rec.statutory_situation_id,
1268 p_rec.provisional_start_date,p_rec.provisional_end_date);
1269 chk_duration_date(p_rec);
1270 --
1271 pqh_psu_bus.set_security_group_id(p_rec.statutory_situation_id);
1272 --
1273 --Validate Dependent Attributes
1274 chk_non_updateable_args(p_effective_date => p_effective_date
1275 ,p_rec => p_rec);
1276 --
1277 pqh_psu_bus.chk_df(p_rec);
1278 --
1279 hr_utility.set_location(' Leaving:'||l_proc, 10);
1280 --
1281 END update_validate;
1282 --
1283 -- ----------------------------------------------------------------------------
1284 -- |---------------------------< delete_validate >----------------------------|
1285 -- ----------------------------------------------------------------------------
1286 PROCEDURE delete_validate(p_rec IN pqh_psu_shd.g_rec_type)
1287 IS
1288 --
1289 l_proc varchar2(72) := g_package||'delete_validate';
1290 --
1291 BEGIN
1292 --
1293 hr_utility.set_location('Entering:'||l_proc, 5);
1294 --
1295 --Call all supporting business operations
1296 hr_utility.set_location(' Leaving:'||l_proc, 10);
1297 --
1298 END delete_validate;
1299 --
1300 end pqh_psu_bus;