1 PACKAGE BODY hr_no_validate_pkg AS
2 /* $Header: penovald.pkb 120.27 2010/10/25 09:28:15 knadhan noship $ */
3
4 PROCEDURE validate
5 (p_person_type_id in number
6 ,p_first_name in varchar2 default null
7 ,p_national_identifier in varchar2 default null
8 ) IS
9 l_type VARCHAR2(1) := NULL;
10
11 l_field varchar2(300) := NULL;
12 l_valid_date varchar2(10);
13 CURSOR c_type IS
14 SELECT 'Y'
15 FROM per_person_types ppt
16 WHERE ppt.system_person_type like 'EMP%'
17 AND ppt.person_type_Id = p_person_type_id;
18
19 BEGIN
20 l_type := NULL;
21 OPEN c_type;
22 FETCH c_type INTO l_type;
23 CLOSE c_type;
24
25
26 --Validate not null fields
27 --IF p_first_name IS NULL THEN
28 -- l_field := hr_general.decode_lookup('NO_FORM_LABELS','FIRST_NAME');
29 --END IF;
30 IF l_type IS NOT NULL THEN
31 /* 10125436 introduced additional check if emmployee is not transferred via global deployement, if yes suppress the validation */
32 IF per_per_bus.g_global_transfer_in_process IS NULL
33 or per_per_bus.g_global_transfer_in_process = false THEN
34 IF p_national_identifier IS NULL OR p_national_identifier = hr_api.g_varchar2 THEN
35 IF l_field IS NULL THEN
36 l_field := hr_general.decode_lookup('NO_FORM_LABELS','NI');
37 ELSE
38 l_field := l_field||', '||hr_general.decode_lookup('NO_FORM_LABELS','NI');
39 END IF;
40 END IF;
41 END IF;
42
43 --Moved mandatory check for First Name here
44 IF p_first_name IS NULL OR p_first_name = hr_api.g_varchar2 THEN
45 l_field := hr_general.decode_lookup('NO_FORM_LABELS','FIRST_NAME');
46 END IF;
47 END IF;
48
49 /*Bug fix- 4570879 added an additional check fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION')IN ('ERROR','WARN')*/
50 IF l_field IS NOT NULL AND fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION')IN ('ERROR','WARN') THEN
51 fnd_message.set_name('PER', 'HR_376803_NO_MANDATORY_MSG');
52 fnd_message.set_token('NAME',l_field, translate => true );
53 hr_utility.raise_error;
54 END IF;
55
56 END;
57
58 --Procedure for validating person
59 PROCEDURE person_validate
60 (p_person_type_id in number
61 ,p_first_name in varchar2 default null
62 ,p_national_identifier in varchar2 default null
63 ) IS
64 BEGIN
65 --
66 -- Added for GSI Bug 5472781
67 --
68 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
69 --
70 validate
71 (p_person_type_id => p_person_type_id
72 ,p_first_name => p_first_name
73 ,p_national_identifier => p_national_identifier
74 );
75 END IF;
76 END person_validate;
77
78 --Procedure for validating applicant
79 PROCEDURE applicant_validate
80 (p_business_group_id in number
81 ,p_person_type_id in number
82 ,p_first_name in varchar2 default null
83 ,p_national_identifier in varchar2 default null
84 ) IS
85 l_person_type_id number ;
86 BEGIN
87 --
88 -- Added for GSI Bug 5472781
89 --
90 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
91 --
92 per_per_bus.chk_person_type
93 (p_person_type_id => l_person_type_id
94 ,p_business_group_id => p_business_group_id
95 ,p_expected_sys_type => 'APL'
96 );
97 validate
98 (p_person_type_id => l_person_type_id
99 ,p_first_name => p_first_name
100 ,p_national_identifier => p_national_identifier
101 );
102 END IF;
103 END applicant_validate;
104
105 --Procedure for validating employee
106 PROCEDURE employee_validate
107 (p_business_group_id in number
108 ,p_person_type_id in number
109 ,p_first_name in varchar2 default null
110 ,p_national_identifier in varchar2 default null
111 ) IS
112 l_person_type_id number ;
113 BEGIN
114 --
115 -- Added for GSI Bug 5472781
116 --
117 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
118 --
119 per_per_bus.chk_person_type
120 (p_person_type_id => l_person_type_id
121 ,p_business_group_id => p_business_group_id
122 ,p_expected_sys_type => 'EMP'
123 );
124 validate
125 (p_person_type_id => l_person_type_id
126 ,p_first_name => p_first_name
127 ,p_national_identifier => p_national_identifier
128 );
129 END IF;
130 END employee_validate;
131
132 --Procedure for validating contact/cwk
133 PROCEDURE cwk_validate
134 (p_business_group_id in number
135 ,p_person_type_id in number
136 ,p_first_name in varchar2 default null
137 ,p_national_identifier in varchar2 default null
138 ) IS
139 l_person_type_id number ;
140 BEGIN
141 --
142 -- Added for GSI Bug 5472781
143 --
144 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
145 --
146 per_per_bus.chk_person_type
147 (p_person_type_id => l_person_type_id
148 ,p_business_group_id => p_business_group_id
149 ,p_expected_sys_type => 'OTHER'
150 );
151 validate
152 (p_person_type_id => l_person_type_id
153 ,p_first_name => p_first_name
154 ,p_national_identifier => p_national_identifier
155 );
156 END IF;
157 END cwk_validate;
158
159 /* Bug Fix 4463101 */
160
161 /*
162 PROCEDURE validate_create_org_inf
163 (p_org_info_type_code in varchar2
164 ,p_organization_id in number
165 ,p_org_information1 in varchar2
166 ) IS
167
168 */
169
170 PROCEDURE validate_create_org_inf
171 (p_org_info_type_code in varchar2
172 ,p_organization_id in number
173 ,p_org_information1 in varchar2
174 ,p_org_information2 in varchar2
175 ,p_org_information3 in varchar2
176 ,p_org_information4 in varchar2
177 ,p_org_information5 in varchar2
178 ,p_org_information6 in varchar2
179 ,p_org_information7 in varchar2
180 ,p_org_information8 in varchar2
181 ,p_org_information9 in varchar2
182 ,p_org_information10 in varchar2
183 ,p_org_information11 in varchar2
184 ,p_org_information12 in varchar2
185 ,p_org_information13 in varchar2
186 ,p_org_information14 in varchar2
187 ,p_org_information15 in varchar2
188 ,p_org_information16 in varchar2
189 ,p_org_information17 in varchar2
190 ,p_org_information18 in varchar2
191 ,p_org_information19 in varchar2
192 ,p_org_information20 in varchar2
193 ) IS
194
195
196 l_org_information1 hr_organization_information.org_information1%TYPE;
197 l_organization_id hr_organization_units.organization_id%TYPE;
198 l_business_group_id hr_organization_units.business_group_id%TYPE;
199 l_org_information_id hr_organization_information.org_information_id%TYPE;
200 l_org_info_type_code hr_organization_information.org_information_context%TYPE;
201 l_org_id hr_organization_information.org_information_id%TYPE;
202
203
204 /* Bug Fix 4463101 */
205
206 l_curr_start_date DATE;
207 l_curr_end_date DATE;
208 l_start_date DATE;
209 l_end_date DATE;
210 l_overlap_status VARCHAR2(1);
211
212 CURSOR csr_repoting_span IS
213 SELECT 'Y'
214 FROM hr_organization_information hoi
215 WHERE hoi.organization_id = p_organization_id
216 AND hoi.org_information_context = 'NO_EOY_REPORTING_RULE_OVERRIDE'
217 AND hoi.org_information3 = p_org_information3
218 AND (to_number(hoi.org_information1) BETWEEN to_number(p_org_information1)
219 AND to_number(nvl(p_org_information2,'4712'))
220 OR to_number(p_org_information1) BETWEEN to_number(hoi.org_information1)
221 AND to_number(nvl(hoi.org_information2,'4712')));
222 --
223 cursor getbgid is
224 select business_group_id
225 from hr_organization_units
226 where organization_id = p_organization_id;
227
228 /* Performance Bug fix 4892110 - Changed the below cursor to parameterized cursor avoiding fnd_sessions table */
229 cursor orgnum(s_eff_date date) is
230 select orgif.org_information_id from hr_organization_information orgif,hr_organization_units ou
231 where ( orgif.org_information_context = 'NO_LOCAL_UNIT_DETAILS' or orgif.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS')
232 and ou.organization_id = orgif.organization_id
233 and ou.business_group_id = l_business_group_id
234 and orgif.org_information1 = p_org_information1
235 and s_eff_date between ou.date_from and nvl(ou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
236
237 /* Performance Bug fix 4892110 - Changed the below cursor to parameterized cursor avoiding fnd_sessions table */
238 cursor orglocalunit(s_eff_date date) is
239 select o.organization_id
240 from hr_organization_units o , hr_organization_information hoi
241 where o.organization_id = hoi.organization_id
242 and o.business_group_id = l_business_group_id
243 and hoi.org_information_context = 'CLASS'
244 and hoi.org_information1 = 'NO_LOCAL_UNIT'
245 and to_char(o.organization_id) in (
246 select hoinf.org_information1
247 from hr_organization_units org, hr_organization_information hoinf
248 where org.business_group_id = l_business_group_id
249 and org.organization_id = hoinf.organization_id
250 and hoinf.org_information_context = 'NO_LOCAL_UNITS'
251 )
252 and o.organization_id = to_number(p_org_information1)
253 and s_eff_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
254 order by o.name;
255
256 /* Bug Fix 4463101 */
257
258 CURSOR csr_get_exemption_lim_details (p_organization_id NUMBER) IS
259 SELECT hoi.ORG_INFORMATION1 p_org_info1
260 ,hoi.ORG_INFORMATION2 p_org_info2
261 ,hoi.ORG_INFORMATION3 p_org_info3
262 FROM hr_organization_information hoi
263 WHERE hoi.ORGANIZATION_ID = p_organization_id
264 AND hoi.ORG_INFORMATION_CONTEXT = 'NO_NI_EXEMPTION_LIMIT';
265
266 /*Pgopal - Bug 5341353 fix*/
267 CURSOR csr_hourly_salaried IS
268 SELECT
269 hoi.org_information5 hourly_salaried
270 FROM
271 hr_organization_information hoi
272 WHERE
273 hoi.organization_id = p_organization_id
274 AND hoi.org_information_context = 'NO_ABSENCE_PAYMENT_DETAILS';
275
276 CURSOR csr_pay_to_be_adjusted IS
277 SELECT
278 hoi.org_information5 pay_to_be_adjusted
279 FROM
280 hr_organization_information hoi
281 WHERE
282 hoi.organization_id = p_organization_id
283 AND hoi.org_information_context = 'NO_HOLIDAY_PAY_DETAILS';
284
285 -- Pension Validation Bug 6153601, Bug 6166346----
286 CURSOR csr_pension_type_chk IS
287 SELECT hoi.ORG_INFORMATION1 pension_types
288 FROM hr_organization_information hoi
289 WHERE hoi.organization_id = p_organization_id
290 AND hoi.org_information_context = 'NO_PENSION_DETAILS';
291
292
293 CURSOR csr_org_number_chk IS
294 SELECT hoi.ORG_INFORMATION2 org_number
295 FROM hr_organization_information hoi
296 WHERE hoi.organization_id = p_organization_id
297 AND hoi.org_information_context = 'NO_PENSION_DETAILS';
298
299 Cursor csr_pension_type_provider_chk IS
300 SELECT hoi.org_information1,
301 hoi.org_information2
302 FROM hr_organization_information hoi
303 WHERE hoi.organization_id = p_organization_id
304 AND hoi.org_information_context = 'NO_PENSION_PROVIDER'
305 AND hoi.org_information3 = p_org_information3
306 AND ( (fnd_date.canonical_to_date(hoi.org_information1) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
307 AND fnd_date.canonical_to_date(hoi.org_information2) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
308 )
309 OR fnd_date.canonical_to_date(hoi.org_information1) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
310 OR fnd_date.canonical_to_date(hoi.org_information2) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
311 OR (fnd_date.canonical_to_date(p_org_information1) BETWEEN fnd_date.canonical_to_date(hoi.org_information1) AND fnd_date.canonical_to_date(hoi.org_information2)
312 AND fnd_date.canonical_to_date(p_org_information2) BETWEEN fnd_date.canonical_to_date(hoi.org_information1) AND fnd_date.canonical_to_date(hoi.org_information2)
313 )
314 );
315
316 l_csr_pension_type_record csr_pension_type_chk%ROWTYPE;
317 l_csr_org_number_record csr_org_number_chk%ROWTYPE;
318 l_csr_pension_provider_chk csr_pension_type_provider_chk%ROWTYPE;
319 --
320
321 l_hourly_salaried csr_hourly_salaried % rowtype;
322 l_field VARCHAR2(300);
323 l_pay_to_be_adjusted csr_pay_to_be_adjusted%ROWTYPE ;
324 s_effective_date date;
325 BEGIN
326 --
327 -- Added for GSI Bug 5472781
328 --
329 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
330 --
331 /* Performance Bug fix 4892110 - Start */
332 begin
333 SELECT effective_date INTO s_effective_date FROM fnd_sessions
334 WHERE session_id = userenv('sessionid');
335 exception
336 WHEN OTHERS then
337 s_effective_date := null;
338 end;
339 /* Performance Bug fix 4892110 - End */
340
341 open getbgid;
342 fetch getbgid into l_business_group_id;
343 close getbgid;
344
345 IF p_org_info_type_code = 'NO_LOCAL_UNIT_DETAILS' OR p_org_info_type_code = 'NO_LEGAL_EMPLOYER_DETAILS' THEN
346
347
348 /* BUG FIX 4103631 */
349 if length(p_org_information1) <> 9 then
350 fnd_message.set_name('PER','HR_376828_NO_INVALID_ORG_NUM');
351 fnd_message.raise_error;
352 end if;
353 /* BUG FIX 4103631 */
354
355 open orgnum(s_effective_date);
356 loop
357 fetch orgnum into l_org_information_id;
358 exit when orgnum%NOTFOUND;
359
360 -- fetch curr org_id , code--
361 select organization_id,org_information_context,org_information1 into l_org_id, l_org_info_type_code,l_org_information1
362 from hr_organization_information
363 where org_information_id = l_org_information_id;
364
365 -- ignore if same type and same organization_id --
366 IF l_org_information1 = p_org_information1 THEN
367 IF (l_organization_id = l_org_id) and (l_org_info_type_code <> p_org_info_type_code) then
368 null;
369 ELSE
370 fnd_message.set_name('PER','HR_376805_NO_ORG_NUMBER_UNIQUE');
371 fnd_message.raise_error;
372 END IF;
373 END IF;
374 end loop;
375 close orgnum;
376 ELSIF p_org_info_type_code = 'NO_LOCAL_UNITS' THEN
377 open orglocalunit(s_effective_date);
378 fetch orglocalunit into l_org_information1;
379 IF orglocalunit%FOUND THEN
380 fnd_message.set_name('PER','HR_376806_NO_LOCAL_UNIT_MSG');
381 fnd_message.raise_error;
382 END IF;
383 close orglocalunit;
384
385
386
387 -- Bug Fix 4463101 : check for ORG_INFORMATION_CONTEXT
388 ELSIF p_org_info_type_code = 'NO_NI_EXEMPTION_LIMIT' THEN
389
390 -- converting ORG_INFORMATION1 and 2 into dates
391 SELECT fnd_date.canonical_to_date(p_org_information2) INTO l_curr_start_date FROM DUAL ;
392 SELECT fnd_date.canonical_to_date(p_org_information3) INTO l_curr_end_date FROM DUAL ;
393
394
395 -- checking if start date is greater than the end date
396 IF (l_curr_start_date > l_curr_end_date )
397 THEN
398 fnd_message.set_name('PAY','PAY_376859_NO_DATE_EARLY');
399 fnd_message.raise_error;
400
401 ELSE
402
403 -- commenting the validation below to allow exemption limit/economic aid to be entered for overlapping periods
404
405 /* -- now checking with other records of exemption limits entered
406 FOR csr_rec IN csr_get_exemption_lim_details ( p_organization_id ) LOOP
407
408 -- converting ORG_INFORMATION1 and 2 into dates
409 SELECT fnd_date.canonical_to_date(csr_rec.p_org_info2) INTO l_start_date FROM DUAL ;
410 SELECT fnd_date.canonical_to_date(csr_rec.p_org_info3) INTO l_end_date FROM DUAL ;
411
412 IF (l_curr_start_date BETWEEN l_start_date AND l_end_date ) OR
413 (l_curr_end_date BETWEEN l_start_date AND l_end_date ) OR
414 (l_start_date BETWEEN l_curr_start_date AND l_curr_end_date ) OR
415 (l_end_date BETWEEN l_curr_start_date AND l_curr_end_date )
416
417 THEN
418 fnd_message.set_name('PAY','PAY_376858_NO_EXEM_LIMIT_ERR');
419 --fnd_message.set_token('START_DATE',l_curr_start_date);
420 --fnd_message.set_token('END_DATE',l_curr_end_date);
421 fnd_message.raise_error;
422 EXIT ;
423 END IF;
424
425 END LOOP;
426 */
427 /* Bug Fix 4463136 */
428 -- now checking for, the period between start date and end date should be a multiple of bimonthly period
429
430 IF (to_number(to_char(l_curr_start_date,'DD')) <> 1) OR
431 (last_day(l_curr_end_date) <> l_curr_end_date) OR
432 (mod(to_number(to_char(l_curr_start_date,'MM')),2) <> 1) OR
433 (mod(to_number(to_char(l_curr_end_date,'MM')),2) <> 0)
434 THEN
435
436 -- raise error message
437 fnd_message.set_name('PAY','PAY_376860_NO_DATE_BIMONTH');
438 fnd_message.raise_error;
439
440 END IF;
441
442 END IF;
443
444 /* End Bug Fix 4463101 */
445 END IF;
446 --
447 -- Validations for EOY Report Override Rules
448 --
449 IF p_org_info_type_code = 'NO_EOY_REPORTING_RULE_OVERRIDE' THEN
450 --
451 IF to_number(p_org_information2) < to_number(p_org_information1) THEN
452 hr_utility.set_message(801,'PAY_376896_NO_YEAR_RESTRICT');
453 hr_utility.raise_error;
454 END IF;
455 --
456 OPEN csr_repoting_span;
457 FETCH csr_repoting_span INTO l_overlap_status;
458 CLOSE csr_repoting_span;
459 --
460 IF l_overlap_status = 'Y' THEN
461 --
462 fnd_message.set_name(801,'PAY_376893_NO_YEAR_EXISTS');
463 fnd_message.set_token('REP_CODE',p_org_information3);
464 fnd_message.raise_error;
465 --
466 END IF;
467 --
468 IF p_org_information4 IN ('BAL','BAL_CODE_CTX') AND p_org_information5 IS NULL THEN
469 --
470 fnd_message.set_name(801,'PAY_376892_NO_BALANCE_MISSING');
471 fnd_message.raise_error;
472 --
473 END IF;
474 --
475 IF p_org_information4 = 'RRV_ELEMENT' AND p_org_information6 IS NULL THEN
476 --
477 fnd_message.set_name(801,'PAY_376889_NO_ELEMENT_MISSING');
478 fnd_message.raise_error;
479 --
480 END IF;
481 --
482 IF p_org_information4 = 'PROCEDURE' AND p_org_information7 IS NULL THEN
483 --
484 fnd_message.set_name(801,'PAY_376890_NO_PROCEDURE_ABSENT');
485 fnd_message.raise_error;
486 --
487 END IF;
488 --
489 IF p_org_information14 = 'PROCEDURE' AND p_org_information15 IS NULL THEN
490 --
491 fnd_message.set_name(801,'PAY_376891_NO_SUMMATION_PROC');
492 fnd_message.raise_error;
493 --
494 END IF;
495 --
496 END IF;
497 --
498 /*Pgopal - Bug 5341353 fix - Start*/
499 IF p_org_info_type_code = 'NO_HOLIDAY_PAY_DETAILS' THEN
500
501 OPEN csr_hourly_salaried;
502 FETCH csr_hourly_salaried
503 INTO l_hourly_salaried;
504 CLOSE csr_hourly_salaried;
505
506
507 IF(NVL(p_org_information3,'N') = 'N'
508 AND(p_org_information4 IS NOT NULL OR p_org_information5 IS NOT NULL)) THEN
509 fnd_message.set_name('PER', 'HR_376899_NO_HOL_PAY_OVER_60');
510 fnd_message.RAISE_ERROR;
511 END IF;
512
513 IF(NVL(p_org_information3,'N') = 'Y'
514 AND p_org_information4 IS NULL) THEN
515 l_field := hr_general.decode_lookup('NO_FORM_LABELS', 'HOL_PAY_OVER_60_FIXED_PERIOD');
516 fnd_message.set_name('PER', 'HR_376803_NO_MANDATORY_MSG');
517 fnd_message.set_token('NAME', l_field, TRANSLATE => TRUE);
518 fnd_message.RAISE_ERROR;
519 END IF;
520
521 IF(NVL(p_org_information3,'N') = 'Y'
522 AND p_org_information5 IS NULL AND NVL(l_hourly_salaried.hourly_salaried,'S') = 'S') THEN
523 l_field := hr_general.decode_lookup('NO_FORM_LABELS', 'HOL_ADJUST');
524 fnd_message.set_name('PER', 'HR_376803_NO_MANDATORY_MSG');
525 fnd_message.set_token('NAME', l_field, TRANSLATE => TRUE);
526 fnd_message.RAISE_ERROR;
527 END IF;
528
529
530 IF (NVL(l_hourly_salaried.hourly_salaried,'S') = 'H' AND p_org_information5 = 'Y' ) THEN
531 fnd_message.set_name('PER', 'HR_376900_NO_HOL_PAY_TO_BE_ADJ');
532 fnd_message.RAISE_ERROR;
533 END IF ;
534
535 ELSIF p_org_info_type_code = 'NO_ABSENCE_PAYMENT_DETAILS' THEN
536
537 OPEN csr_pay_to_be_adjusted;
538 FETCH csr_pay_to_be_adjusted
539 INTO l_pay_to_be_adjusted;
540 CLOSE csr_pay_to_be_adjusted;
541
542 IF (NVL(p_org_information5,'S') = 'H' AND l_pay_to_be_adjusted.pay_to_be_adjusted = 'Y') THEN
543 fnd_message.set_name('PER', 'HR_376900_NO_HOL_PAY_TO_BE_ADJ');
544 fnd_message.RAISE_ERROR;
545 END IF ;
546 END IF;
547 /*Pgopal - Bug 5341353 fix - End*/
548
549 /*Pgopal - Bug 5341353 fix - End*/
550 -- Pension Validation Bug 6153601 , Bug 6166346----
551 IF p_org_info_type_code = 'NO_PENSION_PROVIDER' THEN
552 IF p_org_information8 > p_org_information9 THEN
553 fnd_message.set_name('PER','PER_376920_NO_PEN_AGE_LMT_CHK');
554 fnd_message.RAISE_ERROR;
555 END IF;
556 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) then
557 fnd_message.set_name('PER','PER_376921_NO_PEN_SD_ED_CHK');
558 fnd_message.RAISE_ERROR;
559 END IF;
560 OPEN csr_pension_type_provider_chk;
561 LOOP
562 FETCH csr_pension_type_provider_chk INTO l_csr_pension_provider_chk;
563 EXIT WHEN csr_pension_type_provider_chk%NOTFOUND;
564 CLOSE csr_pension_type_provider_chk;
565 fnd_message.set_name('PER','PER_376922_NO_PEN_PRV_UNIQUE');
566 fnd_message.RAISE_ERROR;
567 END LOOP;
568 CLOSE csr_pension_type_provider_chk;
569
570 END IF;
571
572
573 IF p_org_info_type_code = 'NO_PENSION_DETAILS' THEN
574 IF to_number(p_org_information2) < 0 THEN
575 fnd_message.set_name('PER','PER_376923_NO_PEN_ORG_NUM');
576 fnd_message.RAISE_ERROR;
577 END IF;
578
579 OPEN csr_pension_type_chk;
580 LOOP
581 FETCH csr_pension_type_chk INTO l_csr_pension_type_record;
582 EXIT WHEN csr_pension_type_chk%NOTFOUND;
583 IF l_csr_pension_type_record.pension_types = p_org_information1 then
584 CLOSE csr_pension_type_chk;
585 fnd_message.set_name('PER','PER_376924_NO_PEN_TYPE_DUP');
586 fnd_message.RAISE_ERROR;
587 End IF;
588 END LOOP;
589 CLOSE csr_pension_type_chk;
590
591 OPEN csr_org_number_chk;
592 LOOP
593 FETCH csr_org_number_chk INTO l_csr_org_number_record;
594 EXIT WHEN csr_org_number_chk%NOTFOUND;
595 IF l_csr_org_number_record.org_number <> p_org_information2 then
596 CLOSE csr_org_number_chk;
597 fnd_message.set_name('PER','PER_376925_NO_PEN_TYPE_ORG');
598 fnd_message.RAISE_ERROR;
599 End IF;
600 END LOOP;
601 CLOSE csr_org_number_chk;
602 END IF;
603 --
604 END IF;
605 END validate_create_org_inf;
606
607
608 /* Bug Fix 4463101 */
609
610 /*
611
612 PROCEDURE validate_update_org_inf
613 (p_org_info_type_code in varchar2
614 ,p_org_information_id in number
615 ,p_org_information1 in varchar2
616 ) IS
617
618 */
619
620 PROCEDURE validate_update_org_inf
621 (p_org_info_type_code in varchar2
622 ,p_org_information_id in number
623 ,p_org_information1 in varchar2
624 ,p_org_information2 in varchar2
625 ,p_org_information3 in varchar2
626 ,p_org_information4 in varchar2
627 ,p_org_information5 in varchar2
628 ,p_org_information6 in varchar2
629 ,p_org_information7 in varchar2
630 ,p_org_information8 in varchar2
631 ,p_org_information9 in varchar2
632 ,p_org_information10 in varchar2
633 ,p_org_information11 in varchar2
634 ,p_org_information12 in varchar2
635 ,p_org_information13 in varchar2
636 ,p_org_information14 in varchar2
637 ,p_org_information15 in varchar2
638 ,p_org_information16 in varchar2
639 ,p_org_information17 in varchar2
640 ,p_org_information18 in varchar2
641 ,p_org_information19 in varchar2
642 ,p_org_information20 in varchar2
643 ) IS
644
645
646 l_org_information1 hr_organization_information.org_information1%TYPE;
647 l_organization_id hr_organization_information.organization_id%TYPE;
648 l_business_group_id hr_organization_units.business_group_id%TYPE;
649 l_org_information_id hr_organization_information.org_information_id%TYPE;
650 l_org_info_type_code hr_organization_information.org_information_context%TYPE;
651 l_org_id hr_organization_information.org_information_id%TYPE;
652
653 /* Bug Fix 4463101 */
654
655 l_curr_start_date DATE;
656 l_curr_end_date DATE;
657 l_start_date DATE;
658 l_end_date DATE;
659 l_overlap_status VARCHAR2(1);
660
661 CURSOR csr_repoting_span(l_organization_id NUMBER) IS
662 SELECT 'Y'
663 FROM hr_organization_information hoi
664 WHERE hoi.organization_id = l_organization_id
665 AND hoi.org_information_context = 'NO_EOY_REPORTING_RULE_OVERRIDE'
666 AND hoi.org_information3 = p_org_information3
667 AND hoi.org_information_id <> p_org_information_id
668 AND (to_number(hoi.org_information1) BETWEEN to_number(p_org_information1)
669 AND to_number(nvl(p_org_information2,'4712'))
670 OR to_number(p_org_information1) BETWEEN to_number(hoi.org_information1)
671 AND to_number(nvl(hoi.org_information2,'4712')));
672
673 cursor getbgid is
674 select business_group_id
675 from hr_organization_units
676 where organization_id = l_organization_id;
677
678 cursor getorgid is
679 select organization_id
680 from hr_organization_information
681 where org_information_id = p_org_information_id;
682 /* Performance Bug fix 4892110 - Changed the below cursor to parameterized cursor avoiding fnd_sessions table */
683 cursor orgnum(s_eff_date date) is
684 select orgif.org_information_id from hr_organization_information orgif,hr_organization_units ou
685 where ( orgif.org_information_context = 'NO_LOCAL_UNIT_DETAILS' or orgif.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS')
686 and ou.organization_id = orgif.organization_id
687 and ou.business_group_id = l_business_group_id
688 and orgif.org_information1 = p_org_information1
689 and s_eff_date between ou.date_from and nvl(ou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
690
691 /* Performance Bug fix 4892110 - Changed the below cursor to parameterized cursor avoiding fnd_sessions table */
692 cursor orglocalunit(s_eff_date date) is
693 select o.organization_id
694 from hr_organization_units o , hr_organization_information hoi
695 where o.organization_id = hoi.organization_id
696 and o.business_group_id = l_business_group_id
697 and hoi.org_information_context = 'CLASS'
698 and hoi.org_information1 = 'NO_LOCAL_UNIT'
699 and to_char(o.organization_id) in (
700 select hoinf.org_information1
701 from hr_organization_units org, hr_organization_information hoinf
702 where org.business_group_id = l_business_group_id
703 and org.organization_id = hoinf.organization_id
704 and org.organization_id <> l_organization_id
705 and hoinf.org_information_context = 'NO_LOCAL_UNITS'
706 )
707 and o.organization_id = to_number(p_org_information1)
708 and s_eff_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
709 order by o.name;
710
711 /* Bug Fix 4463101 */
712
713 CURSOR csr_get_exemption_details (p_organization_id NUMBER , p_org_information_id NUMBER) IS
714 SELECT hoi.ORG_INFORMATION1 p_org_info1
715 ,hoi.ORG_INFORMATION2 p_org_info2
716 ,hoi.ORG_INFORMATION3 p_org_info3
717 FROM hr_organization_information hoi
718 WHERE hoi.ORGANIZATION_ID = p_organization_id
719 AND hoi.ORG_INFORMATION_CONTEXT = 'NO_NI_EXEMPTION_LIMIT'
720 AND hoi.ORG_INFORMATION_ID <> p_org_information_id;
721
722 /*Pgopal - Bug 5341353 fix*/
723 CURSOR csr_hourly_salaried(p_organization_id NUMBER) IS
724 SELECT
725 hoi.org_information5 hourly_salaried
726 FROM
727 hr_organization_information hoi
728 WHERE
729 hoi.organization_id = p_organization_id
730 AND hoi.org_information_context = 'NO_ABSENCE_PAYMENT_DETAILS';
731
732 CURSOR csr_pay_to_be_adjusted(p_organization_id NUMBER) IS
733 SELECT
734 hoi.org_information5 pay_to_be_adjusted
735 FROM
736 hr_organization_information hoi
737 WHERE
738 hoi.organization_id = p_organization_id
739 AND hoi.org_information_context = 'NO_HOLIDAY_PAY_DETAILS';
740
741 -- Pension Validation Bug 6153601 , Bug 6166346----
742 CURSOR csr_pension_type_chk IS
743 SELECT hoi.ORG_INFORMATION1 pension_types
744 FROM hr_organization_information hoi
745 WHERE hoi.ORG_INFORMATION_ID = p_org_information_id
746 AND hoi.org_information_context = 'NO_PENSION_DETAILS';
747
748
749 CURSOR csr_org_number_chk IS
750 SELECT hoi.ORG_INFORMATION2 org_number
751 FROM hr_organization_information hoi
752 WHERE hoi.ORG_INFORMATION_ID = p_org_information_id
753 AND hoi.org_information_context = 'NO_PENSION_DETAILS';
754
755 CURSOR csr_pension_provider_date_chk IS
756 SELECT hoi.org_information1,
757 hoi.org_information2
758 FROM hr_organization_information hoi
759 WHERE hoi.org_information_id = p_org_information_id
760 AND hoi.org_information_context = 'NO_PENSION_PROVIDER';
761
762 Cursor csr_pension_type_provider_chk IS
763 SELECT hoi.org_information1,
764 hoi.org_information2,
765 hoi.org_information_id
766 FROM hr_organization_information hoi
767 WHERE hoi.organization_id = ( SELECT hoi1.organization_id
768 FROM hr_organization_information hoi1
769 WHERE hoi1.org_information_id = p_org_information_id
770 )
771 AND hoi.org_information_context = 'NO_PENSION_PROVIDER'
772 AND hoi.org_information3 = p_org_information3
773 AND ( (fnd_date.canonical_to_date(hoi.org_information1) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
774 AND fnd_date.canonical_to_date(hoi.org_information2) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
775 )
776 OR fnd_date.canonical_to_date(hoi.org_information1) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
777 OR fnd_date.canonical_to_date(hoi.org_information2) BETWEEN fnd_date.canonical_to_date(p_org_information1) AND fnd_date.canonical_to_date(p_org_information2)
778 OR (fnd_date.canonical_to_date(p_org_information1) BETWEEN fnd_date.canonical_to_date(hoi.org_information1) AND fnd_date.canonical_to_date(hoi.org_information2)
779 AND fnd_date.canonical_to_date(p_org_information2) BETWEEN fnd_date.canonical_to_date(hoi.org_information1) AND fnd_date.canonical_to_date(hoi.org_information2)
780 )
781 )
782 AND ( fnd_date.canonical_to_date(hoi.org_information1) <> fnd_date.canonical_to_date(p_org_information1)
783 OR fnd_date.canonical_to_date(hoi.org_information2) <> fnd_date.canonical_to_date(p_org_information2)
784 );
785
786 l_csr_pension_type_record csr_pension_type_chk%ROWTYPE;
787 l_csr_org_number_record csr_org_number_chk%ROWTYPE;
788 l_csr_pension_provider_chk csr_pension_type_provider_chk%ROWTYPE;
789 l_csr_pen_prov_date_chk csr_pension_provider_date_chk%ROWTYPE;
790
791 --
792
793 l_hourly_salaried csr_hourly_salaried % rowtype;
794 l_field VARCHAR2(300);
795 l_pay_to_be_adjusted csr_pay_to_be_adjusted%ROWTYPE ;
796 s_effective_date date;
797 BEGIN
798 --
799 -- Added for GSI Bug 5472781
800 --
801 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
802 --
803 /* Performance Bug fix 4892110 - Start */
804 begin
805 SELECT effective_date INTO s_effective_date FROM fnd_sessions
806 WHERE session_id = userenv('sessionid');
807 exception
808 WHEN OTHERS then
809 s_effective_date := null;
810 end;
811 /* Performance Bug fix 4892110 - End */
812 open getorgid;
813 fetch getorgid into l_organization_id;
814 close getorgid;
815
816 open getbgid;
817 fetch getbgid into l_business_group_id;
818 close getbgid;
819
820 IF p_org_info_type_code = 'NO_LOCAL_UNIT_DETAILS' OR p_org_info_type_code = 'NO_LEGAL_EMPLOYER_DETAILS' THEN
821
822 /* BUG FIX 4103631 */
823 if length(p_org_information1) <> 9 then
824 fnd_message.set_name('PER','HR_376828_NO_INVALID_ORG_NUM');
825 fnd_message.raise_error;
826 end if;
827 /* BUG FIX 4103631 */
828
829
830
831 open orgnum (s_effective_date);
832 loop
833 fetch orgnum into l_org_information_id;
834 exit when orgnum%NOTFOUND;
835
836 -- fetch curr org_id , code--
837 select organization_id,org_information_context,org_information1
838 into l_org_id, l_org_info_type_code,l_org_information1
839 from hr_organization_information
840 where org_information_id = l_org_information_id;
841
842 -- ignore if same type and same organization_id --
843 IF l_org_information1 = p_org_information1 THEN
844 IF (l_organization_id = l_org_id) and (l_org_info_type_code <> p_org_info_type_code) then
845 null;
846
847 -- Bug Fix 5370311 Start : ignore if same record is being updated
848 ELSIF ( l_org_information_id = p_org_information_id ) THEN
849 NULL ;
850 -- Bug Fix 5370311 End
851
852 ELSE
853 fnd_message.set_name('PER','HR_376805_NO_ORG_NUMBER_UNIQUE');
854 fnd_message.raise_error;
855 END IF;
856 END IF;
857 end loop;
858 close orgnum;
859 ELSIF p_org_info_type_code = 'NO_LOCAL_UNITS' THEN
860 open orglocalunit (s_effective_date);
861 fetch orglocalunit into l_org_information1;
862 IF orglocalunit%FOUND THEN
863 fnd_message.set_name('PER','HR_376806_NO_LOCAL_UNIT_MSG');
864 fnd_message.raise_error;
865 END IF;
866 close orglocalunit;
867
868
869 -- Bug Fix 4463101 : check for ORG_INFORMATION_CONTEXT
870 ELSIF p_org_info_type_code = 'NO_NI_EXEMPTION_LIMIT' THEN
871
872 -- converting ORG_INFORMATION1 and 2 into dates
873 SELECT fnd_date.canonical_to_date(p_org_information2) INTO l_curr_start_date FROM DUAL ;
874 SELECT fnd_date.canonical_to_date(p_org_information3) INTO l_curr_end_date FROM DUAL ;
875
876
877 -- checking if start date is greater than the end date
878 IF (l_curr_start_date > l_curr_end_date )
879 THEN
880 fnd_message.set_name('PAY','PAY_376859_NO_DATE_EARLY');
881 fnd_message.raise_error;
882
883 ELSE
884
885 -- commenting the validation below to allow exemption limit/economic aid to be entered for overlapping periods
886 /*
887 -- now checking with other records of exemption limits entered
888 FOR csr_rec IN csr_get_exemption_details ( l_organization_id , p_org_information_id ) LOOP
889
890 -- converting ORG_INFORMATION1 and 2 into dates
891 SELECT fnd_date.canonical_to_date(csr_rec.p_org_info2) INTO l_start_date FROM DUAL ;
892 SELECT fnd_date.canonical_to_date(csr_rec.p_org_info3) INTO l_end_date FROM DUAL ;
893
894 IF (l_curr_start_date BETWEEN l_start_date AND l_end_date ) OR
895 (l_curr_end_date BETWEEN l_start_date AND l_end_date ) OR
896 (l_start_date BETWEEN l_curr_start_date AND l_curr_end_date ) OR
897 (l_end_date BETWEEN l_curr_start_date AND l_curr_end_date )
898
899 THEN
900 fnd_message.set_name('PAY','PAY_376858_NO_EXEM_LIMIT_ERR');
901 --fnd_message.set_token('START_DATE',l_curr_start_date);
902 --fnd_message.set_token('END_DATE',l_curr_end_date);
903 fnd_message.raise_error;
904 EXIT ;
905 END IF;
906
907 END LOOP;
908 */
909 /* Bug Fix 4463136 */
910 -- now checking for, the period between start date and end date should be a multiple of bimonthly period
911
912 IF (to_number(to_char(l_curr_start_date,'DD')) <> 1) OR
913 (last_day(l_curr_end_date) <> l_curr_end_date) OR
914 (mod(to_number(to_char(l_curr_start_date,'MM')),2) <> 1) OR
915 (mod(to_number(to_char(l_curr_end_date,'MM')),2) <> 0)
916 THEN
917
918 -- raise error message
919 fnd_message.set_name('PAY','PAY_376860_NO_DATE_BIMONTH');
920 fnd_message.raise_error;
921
922 END IF;
923
924 END IF;
925 /* End Bug Fix 4463101 */
926
927
928 END IF;
929 --
930 -- Validations on LE Override rules for EOY Audit Report
931 --
932 IF p_org_info_type_code = 'NO_EOY_REPORTING_RULE_OVERRIDE' THEN
933 --
934 IF to_number(p_org_information2) < to_number(p_org_information1) THEN
935 hr_utility.set_message(801,'PAY_376896_NO_YEAR_RESTRICT');
936 hr_utility.raise_error;
937 END IF;
938 --
939 OPEN csr_repoting_span(l_organization_id);
940 FETCH csr_repoting_span INTO l_overlap_status;
941 CLOSE csr_repoting_span;
942 --
943 IF l_overlap_status = 'Y' THEN
944 --
945 fnd_message.set_name(801,'PAY_376883_NO_YEAR_EXISTS');
946 fnd_message.set_token('REP_CODE',p_org_information3);
947 fnd_message.raise_error;
948 --
949 END IF;
950 --
951 IF p_org_information4 IN ('BAL','BAL_CODE_CTX') AND p_org_information5 IS NULL THEN
952 --
953 fnd_message.set_name(801,'PAY_376892_NO_BALANCE_MISSING');
954 fnd_message.raise_error;
955 --
956 END IF;
957 --
958 IF p_org_information4 = 'RRV_ELEMENT' AND p_org_information6 IS NULL THEN
959 --
960 fnd_message.set_name(801,'PAY_376889_NO_ELEMENT_MISSING');
961 fnd_message.raise_error;
962 --
963 END IF;
964 --
965 IF p_org_information4 = 'PROCEDURE' AND p_org_information7 IS NULL THEN
966 --
967 fnd_message.set_name(801,'PAY_376890_NO_PROCEDURE_ABSENT');
968 fnd_message.raise_error;
969 --
970 END IF;
971 --
972 IF p_org_information14 = 'PROCEDURE' AND p_org_information15 IS NULL THEN
973 --
974 fnd_message.set_name(801,'PAY_376891_NO_SUMMATION_PROC');
975 fnd_message.raise_error;
976 --
977 END IF;
978 --
979 END IF;
980 --
981
982 /*Pgopal - Bug 5341353 fix - Start*/
983 IF p_org_info_type_code = 'NO_HOLIDAY_PAY_DETAILS' THEN
984
985 OPEN csr_hourly_salaried(l_organization_id);
986 FETCH csr_hourly_salaried
987 INTO l_hourly_salaried;
988 CLOSE csr_hourly_salaried;
989
990
991 IF(NVL(p_org_information3,'N') = 'N'
992 AND(p_org_information4 IS NOT NULL OR p_org_information5 IS NOT NULL)) THEN
993 fnd_message.set_name('PER', 'HR_376899_NO_HOL_PAY_OVER_60');
994 fnd_message.RAISE_ERROR;
995 END IF;
996
997 IF(NVL(p_org_information3,'N') = 'Y'
998 AND p_org_information4 IS NULL) THEN
999 l_field := hr_general.decode_lookup('NO_FORM_LABELS', 'HOL_PAY_OVER_60_FIXED_PERIOD');
1000 fnd_message.set_name('PER', 'HR_376803_NO_MANDATORY_MSG');
1001 fnd_message.set_token('NAME', l_field, TRANSLATE => TRUE);
1002 fnd_message.RAISE_ERROR;
1003 END IF;
1004
1005 IF(NVL(p_org_information3,'N') = 'Y'
1006 AND p_org_information5 IS NULL AND NVL(l_hourly_salaried.hourly_salaried,'S') = 'S') THEN
1007 l_field := hr_general.decode_lookup('NO_FORM_LABELS', 'HOL_ADJUST');
1008 fnd_message.set_name('PER', 'HR_376803_NO_MANDATORY_MSG');
1009 fnd_message.set_token('NAME', l_field, TRANSLATE => TRUE);
1010 fnd_message.RAISE_ERROR;
1011
1012 END IF;
1013
1014
1015 IF (NVL(l_hourly_salaried.hourly_salaried,'S') = 'H' AND p_org_information5 = 'Y') THEN
1016 fnd_message.set_name('PER', 'HR_376900_NO_HOL_PAY_TO_BE_ADJ');
1017 fnd_message.RAISE_ERROR;
1018 END IF ;
1019
1020 ELSIF p_org_info_type_code = 'NO_ABSENCE_PAYMENT_DETAILS' THEN
1021
1022 OPEN csr_pay_to_be_adjusted(l_organization_id);
1023 FETCH csr_pay_to_be_adjusted
1024 INTO l_pay_to_be_adjusted;
1025 CLOSE csr_pay_to_be_adjusted;
1026
1027 IF (NVL(p_org_information5,'S') = 'H' AND l_pay_to_be_adjusted.pay_to_be_adjusted = 'Y') THEN
1028 fnd_message.set_name('PER', 'HR_376900_NO_HOL_PAY_TO_BE_ADJ');
1029 fnd_message.RAISE_ERROR;
1030 END IF ;
1031
1032 END IF;
1033 /*Pgopal - Bug 5341353 fix - End*/
1034
1035 -- Pension Validation Bug 6153601 , Bug 6166346----
1036 IF p_org_info_type_code = 'NO_PENSION_PROVIDER' THEN
1037 IF p_org_information8 > p_org_information9 THEN
1038 fnd_message.set_name('PER','PER_376920_NO_PEN_AGE_LMT_CHK');
1039 fnd_message.RAISE_ERROR;
1040 END IF;
1041 OPEN csr_pension_provider_date_chk;
1042 LOOP
1043 FETCH csr_pension_provider_date_chk INTO l_csr_pen_prov_date_chk;
1044 EXIT WHEN csr_pension_provider_date_chk%NOTFOUND;
1045 IF fnd_date.canonical_to_date(l_csr_pen_prov_date_chk.org_information1) > fnd_date.canonical_to_date(l_csr_pen_prov_date_chk.org_information2) THEN
1046 CLOSE csr_pension_provider_date_chk;
1047 fnd_message.set_name('PER','PER_376921_NO_PEN_SD_ED_CHK');
1048 fnd_message.RAISE_ERROR;
1049 END IF;
1050 END LOOP;
1051 CLOSE csr_pension_provider_date_chk;
1052
1053 OPEN csr_pension_type_provider_chk;
1054 LOOP
1055 FETCH csr_pension_type_provider_chk INTO l_csr_pension_provider_chk;
1056 EXIT WHEN csr_pension_type_provider_chk%NOTFOUND;
1057 IF l_csr_pension_provider_chk.org_information_id <> p_org_information_id THEN
1058 CLOSE csr_pension_type_provider_chk;
1059 fnd_message.set_name('PER','PER_376922_NO_PEN_PRV_UNIQUE');
1060 fnd_message.RAISE_ERROR;
1061 END IF;
1062 END LOOP;
1063 CLOSE csr_pension_type_provider_chk;
1064 END IF;
1065 IF p_org_info_type_code = 'NO_PENSION_DETAILS' THEN
1066 IF to_number(p_org_information2) < 0 THEN
1067 fnd_message.set_name('PER','PER_376923_NO_PEN_ORG_NUM');
1068 fnd_message.RAISE_ERROR;
1069 END IF;
1070
1071 OPEN csr_pension_type_chk;
1072 LOOP
1073 FETCH csr_pension_type_chk INTO l_csr_pension_type_record;
1074 EXIT WHEN csr_pension_type_chk%NOTFOUND;
1075 IF l_csr_pension_type_record.pension_types = p_org_information1 then
1076 CLOSE csr_pension_type_chk;
1077 fnd_message.set_name('PER','PER_376924_NO_PEN_TYPE_DUP');
1078 fnd_message.RAISE_ERROR;
1079 End IF;
1080 END LOOP;
1081 CLOSE csr_pension_type_chk;
1082
1083 OPEN csr_org_number_chk;
1084 LOOP
1085 FETCH csr_org_number_chk INTO l_csr_org_number_record;
1086 EXIT WHEN csr_org_number_chk%NOTFOUND;
1087 IF l_csr_org_number_record.org_number <> p_org_information2 then
1088 CLOSE csr_org_number_chk;
1089 fnd_message.set_name('PER','PER_376925_NO_PEN_TYPE_ORG');
1090 fnd_message.RAISE_ERROR;
1091 End IF;
1092 END LOOP;
1093 CLOSE csr_org_number_chk;
1094 END IF;
1095 --
1096 END IF;
1097 END validate_update_org_inf;
1098
1099 --Procedure for validating qualification insertion
1100 PROCEDURE qual_insert_validate
1101 (p_business_group_id in number
1102 ,p_qualification_type_id in number
1103 ,p_qua_information_category in varchar2 default null
1104 ,p_person_id in number
1105 ,p_qua_information1 in varchar2 default null
1106 ,p_qua_information2 in varchar2 default null
1107 ) IS
1108 l_count NUMBER;
1109 l_nus2000_code NUMBER;
1110
1111 BEGIN
1112 --
1113 -- Added for GSI Bug 5472781
1114 --
1115 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1116 --
1117
1118 select information1 into l_nus2000_code
1119 from per_qualification_types
1120 where qualification_type_id = p_qualification_type_id;
1121
1122
1123 if p_qua_information1 <> l_nus2000_code then
1124 fnd_message.set_name('PER', 'HR_376822_NO_NUS2000_MISMATCH');
1125 hr_utility.raise_error;
1126 end if;
1127
1128
1129
1130 IF p_qua_information2 ='Y' THEN
1131 select count(*)
1132 into l_count
1133 from per_qualifications pq
1134 where pq.business_group_id = p_business_group_id
1135 and pq.person_id = p_person_id
1136 and pq.qua_information_category='NO'
1137 and pq.qua_information2='Y';
1138 IF l_count > 0 then
1139 fnd_message.set_name('PER', 'HR_376812_NO_HIGHEST_LEVEL');
1140 hr_utility.raise_error;
1141 END IF;
1142 END IF ;
1143 END IF;
1144 END qual_insert_validate;
1145
1146 --Procedure for validating qualification Update
1147 PROCEDURE qual_update_validate
1148 (p_qua_information_category in varchar2 default null
1149 ,p_qualification_id in number
1150 ,p_qualification_type_id in number
1151 ,p_qua_information1 in varchar2 default null
1152 ,p_qua_information2 in varchar2 default null
1153 ) IS
1154 l_count NUMBER;
1155 l_person_id NUMBER;
1156 l_business_group_id NUMBER;
1157 l_nus2000_code NUMBER;
1158
1159 CURSOR c_person_id IS
1160 SELECT person_id,business_group_id
1161 FROM per_qualifications
1162 WHERE qualification_id = p_qualification_id;
1163
1164 /*Bug fix 4950606 : Rewrote the select statement using cursor in qual_update_validate procedure.*/
1165 CURSOR c_information1 IS
1166 SELECT
1167 information1
1168 FROM
1169 per_qualification_types
1170 WHERE
1171 qualification_type_id = p_qualification_type_id;
1172
1173 BEGIN
1174 --
1175 -- Added for GSI Bug 5472781
1176 --
1177 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1178 --
1179 OPEN c_information1;
1180 FETCH c_information1 INTO l_nus2000_code ;
1181 CLOSE c_information1;
1182
1183 if p_qua_information1 <> l_nus2000_code then
1184 fnd_message.set_name('PER', 'HR_376822_NO_NUS2000_MISMATCH');
1185 hr_utility.raise_error;
1186 end if;
1187
1188
1189
1190 IF p_qua_information2 ='Y' THEN
1191 l_person_id := NULL;
1192 OPEN c_person_id;
1193 FETCH c_person_id INTO l_person_id,l_business_group_id;
1194 CLOSE c_person_id;
1195
1196 select count(*)
1197 into l_count
1198 from per_qualifications pq
1199 where pq.business_group_id = l_business_group_id
1200 and pq.person_id = l_person_id
1201 and pq.qualification_id <> p_qualification_id
1202 and pq.qua_information_category='NO'
1203 and pq.qua_information2='Y';
1204
1205 IF l_count > 0 then
1206 fnd_message.set_name('PER', 'HR_376812_NO_HIGHEST_LEVEL');
1207 hr_utility.raise_error;
1208 END IF;
1209
1210 END IF;
1211 END IF;
1212 END qual_update_validate ;
1213
1214
1215 -- Procedure to Validate the Organization Classification
1216 PROCEDURE validate_create_org_cat
1217 (p_organization_id in number
1218 ,p_org_information1 in varchar2
1219 ) IS
1220
1221 l_organization_id hr_organization_units.organization_id%TYPE;
1222 l_business_group_id hr_organization_units.business_group_id%TYPE;
1223 l_int_ext_flag hr_organization_units.internal_external_flag%TYPE;
1224
1225 cursor getbgid is
1226 select business_group_id
1227 from hr_organization_units
1228 where organization_id = p_organization_id;
1229
1230 cursor orgtype is
1231 select ou.internal_external_flag from hr_organization_units ou ,FND_SESSIONS s
1232 where ou.organization_id= p_organization_id
1233 and s.session_id = userenv('sessionid')
1234 and s.effective_date between ou.date_from and nvl(ou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
1235
1236 BEGIN
1237 --
1238 -- Added for GSI Bug 5472781
1239 --
1240 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1241 --
1242 open getbgid;
1243 fetch getbgid into l_business_group_id;
1244 close getbgid;
1245
1246 IF p_org_information1 = 'NO_SOC_SEC_OFFICE' THEN
1247
1248 open orgtype;
1249 fetch orgtype into l_int_ext_flag;
1250 close orgtype;
1251
1252 if l_int_ext_flag='INT' then
1253 fnd_message.set_name('PER','HR_376818_NO_SOC_SEC');
1254 fnd_message.raise_error;
1255 end if;
1256
1257 END IF;
1258 END IF;
1259 END validate_create_org_cat;
1260 -----------------------------------------------------------------------------------------------
1261 --Procedures added to validate Contract End Date against Active Start Date for bug fix 3907853
1262 -----------------------------------------------------------------------------------------------
1263
1264 PROCEDURE create_contract_validate
1265 (p_status in varchar2
1266 ,p_effective_date in date
1267 ,p_ctr_information_category in varchar2 default null
1268 ,p_ctr_information1 in varchar2 default null
1269 ) IS
1270
1271 l_active_start_date date;
1272
1273 BEGIN
1274 --
1275 -- Added for GSI Bug 5472781
1276 --
1277 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1278 --
1279 IF p_status = 'A-ACTIVE' THEN
1280 IF (p_ctr_information1 IS NOT NULL AND p_ctr_information_category ='NO') THEN
1281
1282 IF (fnd_date.canonical_to_date(p_ctr_information1) < p_effective_date) THEN
1283 --
1284 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1285 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','CED'));
1286 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','ASD'));
1287 hr_utility.raise_error;
1288 --
1289 END IF;
1290 END IF;
1291 END IF;
1292 END IF;
1293 END create_contract_validate;
1294
1295 PROCEDURE update_contract_validate
1296 (p_contract_id in number default null
1297 ,p_status in varchar2
1298 ,p_effective_date in date
1299 ,p_ctr_information_category in varchar2 default null
1300 ,p_ctr_information1 in varchar2 default null
1301 ) IS
1302
1303 l_active_start_date date;
1304
1305 BEGIN
1306 --
1307 -- Added for GSI Bug 5472781
1308 --
1309 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1310 --
1311 IF p_status = 'A-ACTIVE' THEN
1312 IF (p_ctr_information1 IS NOT NULL AND p_ctr_information_category ='NO') THEN
1313
1314 l_active_start_date := hr_contract_api.get_active_start_date (p_contract_id,p_effective_date,p_status);
1315
1316 IF (fnd_date.canonical_to_date(p_ctr_information1) < l_active_start_date) THEN
1317 --
1318 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1319 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','CED'));
1320 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','ASD'));
1321 hr_utility.raise_error;
1322 --
1323 END IF;
1324 END IF;
1325 END IF;
1326 END IF;
1327 END update_contract_validate;
1328
1329
1330 --------------------------------------------------------------------------------------------------------
1331 --Procedures added to validate 'Date Reported to Social Security' and 'Date Reported to Labor Inspection'
1332 --against 'Incident Date' for bug fix 3902280
1333 -------------------------------------------------------------------------------------------------------
1334
1335 PROCEDURE workinc_validate
1336 (p_incident_date in date
1337 ,p_inc_information_category in varchar2 default null
1338 ,p_inc_information1 in varchar2 default null
1339 ,p_inc_information2 in varchar2 default null
1340 ) IS
1341
1342 BEGIN
1343 --
1344 -- Added for GSI Bug 5472781
1345 --
1346 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1347 --
1348 IF (p_inc_information1 IS NOT NULL AND p_inc_information_category ='NO' )THEN
1349 IF (fnd_date.canonical_to_date(p_inc_information1) < p_incident_date)
1350 THEN
1351 --
1352 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1353 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','DATE_REPORTED_TO_SSO'));
1354 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','ID'));
1355 hr_utility.raise_error;
1356 --
1357 END IF;
1358 END IF;
1359
1360 IF (p_inc_information2 IS NOT NULL AND p_inc_information_category ='NO') THEN
1361 IF (fnd_date.canonical_to_date(p_inc_information2) < p_incident_date)
1362 THEN
1363 --
1364 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1365 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','DATE_REPORTED_TO_LIA'));
1366 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','ID'));
1367 hr_utility.raise_error;
1368 --
1369 END IF;
1370 END IF;
1371 END IF;
1372 END workinc_validate;
1373
1374 -----------------------------------------------------------------------------------------------------------
1375 --Add validation for 'Retirement Date' and 'Retirement Inquiry Sent'
1376 --against Hire Date of the Employee for bug fix 3907827.
1377 --Also to add validation for 'Retirement Age' against Actual Age of the Employee for bug fix 3907827.
1378 -----------------------------------------------------------------------------------------------------------
1379
1380 PROCEDURE create_asg_validate
1381 ( p_scl_segment12 IN VARCHAR2 DEFAULT NULL
1382 ,p_scl_segment13 IN VARCHAR2 DEFAULT NULL
1383 ,p_scl_segment14 IN VARCHAR2 DEFAULT NULL
1384 ,p_effective_date IN DATE
1385 ,p_person_id IN NUMBER
1386 ) IS
1387
1388 CURSOR get_person_details (p_person_id NUMBER) IS
1389 select papf.date_of_birth, papf.start_date
1390 from per_all_people_f papf
1391 where papf.per_information_category ='NO'
1392 and papf.PERSON_ID = p_person_id
1393 and p_effective_date BETWEEN papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE;
1394
1395 l_dob date;
1396 l_start_date date;
1397 l_age number;
1398
1399 BEGIN
1400 --
1401 -- Added for GSI Bug 5472781
1402 --
1403 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1404 --
1405 open get_person_details(p_person_id);
1406 fetch get_person_details into l_dob,l_start_date;
1407 close get_person_details;
1408
1409 l_age := TRUNC((p_effective_date - l_dob)/365);
1410
1411 --GSI Bug 4584922
1412 IF (p_scl_segment14 <> hr_api.g_varchar2) THEN
1413 IF(l_age > p_scl_segment14 ) THEN
1414 fnd_message.set_name('PER','HR_376821_NO_AGE_LESS');
1415 fnd_message.set_token('AGE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','RA'));
1416 fnd_message.set_token('AGE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','AA'));
1417 hr_utility.raise_error;
1418
1419 END IF;
1420 END IF;
1421
1422 --GSI Bug 4584922
1423 IF(p_scl_segment12 <> hr_api.g_varchar2) THEN
1424 IF( p_scl_segment12 <l_start_date) THEN
1425 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1426 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','RD'));
1427 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','HD'));
1428 hr_utility.raise_error;
1429 END IF;
1430 END IF;
1431
1432 --GSI Bug 4584922
1433 IF(p_scl_segment13 <> hr_api.g_varchar2) THEN
1434 IF( p_scl_segment13 <l_start_date) THEN
1435 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1436 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','RIS'));
1437 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','HD'));
1438 hr_utility.raise_error;
1439 END IF;
1440 END IF;
1441 END IF;
1442 END create_asg_validate;
1443
1444 -------------------------------------------------------------------------------------------------------
1445
1446 PROCEDURE update_asg_validate
1447 ( p_segment12 IN VARCHAR2 DEFAULT NULL
1448 ,p_segment13 IN VARCHAR2 DEFAULT NULL
1449 ,p_segment14 IN VARCHAR2 DEFAULT NULL
1450 ,p_effective_date IN DATE
1451 ,p_assignment_id IN NUMBER
1452 ) IS
1453
1454 CURSOR get_person_details (p_assignment_id NUMBER) IS
1455 select papf.date_of_birth, papf.start_date
1456 from per_all_people_f papf, per_all_assignments_f paaf
1457 where papf.per_information_category ='NO'
1458 and paaf.PERSON_ID = papf.PERSON_ID
1459 and paaf.ASSIGNMENT_ID = p_assignment_id
1460 and p_effective_date BETWEEN paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
1461 and p_effective_date BETWEEN papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE;
1462
1463 l_dob date;
1464 l_start_date date;
1465 l_age number;
1466
1467 BEGIN
1468 --
1469 -- Added for GSI Bug 5472781
1470 --
1471 IF hr_utility.chk_product_install('Oracle Human Resources', 'NO') THEN
1472 --
1473 open get_person_details(p_assignment_id);
1474 fetch get_person_details into l_dob,l_start_date;
1475 close get_person_details;
1476
1477 l_age := TRUNC((p_effective_date - l_dob)/365);
1478
1479 --GSI Bug 4584922
1480 IF(p_segment14 <> hr_api.g_varchar2) THEN
1481 IF(l_age > p_segment14 ) THEN
1482 fnd_message.set_name('PER','HR_376821_NO_AGE_LESS');
1483 fnd_message.set_token('AGE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','RA'));
1484 fnd_message.set_token('AGE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','AA'));
1485 hr_utility.raise_error;
1486
1487 END IF;
1488 END IF;
1489
1490 --GSI Bug 4584922
1491 IF(p_segment12 <> hr_api.g_varchar2) THEN
1492 IF( p_segment12 <l_start_date) THEN
1493 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1494 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','RD'));
1495 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','HD'));
1496 hr_utility.raise_error;
1497 END IF;
1498 END IF;
1499
1500 --GSI Bug 4584922
1501 IF(p_segment13 <> hr_api.g_varchar2) THEN
1502 IF( p_segment13 <l_start_date) THEN
1503 fnd_message.set_name('PER','HR_376820_NO_DATE_EARLY');
1504 fnd_message.set_token('DATE1',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','RIS'));
1505 fnd_message.set_token('DATE2',hr_general.decode_fnd_comm_lookup('NO_FORM_LABELS','HD'));
1506 hr_utility.raise_error;
1507 END IF;
1508 END IF;
1509 END IF;
1510 END update_asg_validate;
1511
1512
1513 --------------------------------------------------------------------------------------------------
1514
1515 PROCEDURE CREATE_ELEMENT_ELE_CODE
1516 (p_information_type IN VARCHAR2
1517 ,p_element_type_id IN NUMBER
1518 ,p_eei_attribute_category IN VARCHAR2
1519 ,p_eei_attribute1 IN VARCHAR2
1520 ,p_eei_attribute2 IN VARCHAR2
1521 ,p_eei_attribute3 IN VARCHAR2
1522 ,p_eei_attribute4 IN VARCHAR2
1523 ,p_eei_attribute5 IN VARCHAR2
1524 ,p_eei_attribute6 IN VARCHAR2
1525 ,p_eei_attribute7 IN VARCHAR2
1526 ,p_eei_attribute8 IN VARCHAR2
1527 ,p_eei_attribute9 IN VARCHAR2
1528 ,p_eei_attribute10 IN VARCHAR2
1529 ,p_eei_attribute11 IN VARCHAR2
1530 ,p_eei_attribute12 IN VARCHAR2
1531 ,p_eei_attribute13 IN VARCHAR2
1532 ,p_eei_attribute14 IN VARCHAR2
1533 ,p_eei_attribute15 IN VARCHAR2
1534 ,p_eei_attribute16 IN VARCHAR2
1535 ,p_eei_attribute17 IN VARCHAR2
1536 ,p_eei_attribute18 IN VARCHAR2
1537 ,p_eei_attribute19 IN VARCHAR2
1538 ,p_eei_attribute20 IN VARCHAR2
1539 ,p_eei_information_category IN VARCHAR2
1540 ,p_eei_information1 IN VARCHAR2
1541 ,p_eei_information2 IN VARCHAR2
1542 ,p_eei_information3 IN VARCHAR2
1543 ,p_eei_information4 IN VARCHAR2
1544 ,p_eei_information5 IN VARCHAR2
1545 ,p_eei_information6 IN VARCHAR2
1546 ,p_eei_information7 IN VARCHAR2
1547 ,p_eei_information8 IN VARCHAR2
1548 ,p_eei_information9 IN VARCHAR2
1549 ,p_eei_information10 IN VARCHAR2
1550 ,p_eei_information11 IN VARCHAR2
1551 ,p_eei_information12 IN VARCHAR2
1552 ,p_eei_information13 IN VARCHAR2
1553 ,p_eei_information14 IN VARCHAR2
1554 ,p_eei_information15 IN VARCHAR2
1555 ,p_eei_information16 IN VARCHAR2
1556 ,p_eei_information17 IN VARCHAR2
1557 ,p_eei_information18 IN VARCHAR2
1558 ,p_eei_information19 IN VARCHAR2
1559 ,p_eei_information20 IN VARCHAR2
1560 ,p_eei_information21 IN VARCHAR2
1561 ,p_eei_information22 IN VARCHAR2
1562 ,p_eei_information23 IN VARCHAR2
1563 ,p_eei_information24 IN VARCHAR2
1564 ,p_eei_information25 IN VARCHAR2
1565 ,p_eei_information26 IN VARCHAR2
1566 ,p_eei_information27 IN VARCHAR2
1567 ,p_eei_information28 IN VARCHAR2
1568 ,p_eei_information29 IN VARCHAR2
1569 ,p_eei_information30 IN VARCHAR2
1570 ) is
1571
1572 CURSOR csr_get_ele_code( p_ele_type_id NUMBER ) IS
1573 SELECT eei_information1, eei_information2
1574 FROM pay_element_type_extra_info petei
1575 WHERE petei.information_type='NO_ELEMENT_CODES'
1576 AND petei.element_type_id = p_ele_type_id ;
1577
1578 CURSOR csr_get_le_details(p_le_id NUMBER) IS
1579 SELECT hou.name NAME
1580 FROM hr_organization_units hou
1581 ,hr_organization_information hoi
1582 WHERE hoi.organization_id = hou.organization_id
1583 AND hoi.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS'
1584 AND hou.organization_id = p_le_id;
1585
1586 CURSOR csr_chk_element_eit_year IS
1587 SELECT 'Y', etei.eei_information3
1588 FROM pay_element_type_extra_info etei
1589 WHERE etei.element_type_id = p_element_type_id
1590 AND etei.eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING'
1591 AND ((to_number(etei.eei_information1)
1592 between to_number(p_eei_information1) and to_number(nvl(p_eei_information2,'4712')))
1593 OR (to_number(p_eei_information1)
1594 between to_number(etei.eei_information1) and to_number(nvl(etei.eei_information2,'4712')))) ;
1595 --
1596 l_year_status varchar2(1);
1597 l_overlap_code pay_element_type_extra_info.eei_information3%TYPE;
1598 rec_get_ele_code csr_get_ele_code%ROWTYPE;
1599 rec_get_le_details csr_get_le_details%ROWTYPE;
1600 --
1601 BEGIN
1602 --
1603 IF p_eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING' THEN
1604 -- Validation to ensure Year of Reporting(EOY) is different then what we already have in Extra Element Info DDF
1605 IF p_eei_information2 IS NOT NULL AND to_number(p_eei_information2) < to_number(p_eei_information1) THEN
1606 hr_utility.set_message(800,'PAY_376896_NO_YEAR_RESTRICT');
1607 hr_utility.raise_error;
1608 END IF;
1609 --
1610 OPEN csr_chk_element_eit_year;
1611 FETCH csr_chk_element_eit_year INTO l_year_status, l_overlap_code;
1612 CLOSE csr_chk_element_eit_year;
1613 --
1614 IF (l_year_status = 'Y') THEN
1615 hr_utility.set_message(801,'PAY_376893_NO_YEAR_EXISTS');
1616 fnd_message.set_token('REP_CODE',l_overlap_code); --p_eei_information3);
1617 hr_utility.raise_error;
1618 END IF;
1619 --
1620 -- Validation to check if single input value is mapped to more than one column in Extra Element Info DDF
1621 IF (p_eei_information4 = p_eei_information6 OR p_eei_information4 = p_eei_information8
1622 OR p_eei_information4 = p_eei_information10 OR p_eei_information4 = p_eei_information12
1623 OR p_eei_information4 = p_eei_information14 OR p_eei_information4 = p_eei_information16) THEN
1624 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1625 hr_utility.raise_error;
1626 END IF;
1627 --
1628 IF (p_eei_information6 = p_eei_information8 OR p_eei_information6 = p_eei_information10
1629 OR p_eei_information6 = p_eei_information12 OR p_eei_information6 = p_eei_information14
1630 OR p_eei_information6 = p_eei_information16) THEN
1631 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1632 hr_utility.raise_error;
1633 END IF;
1634 --
1635 IF (p_eei_information8 = p_eei_information10 OR p_eei_information8 = p_eei_information12
1636 OR p_eei_information8 = p_eei_information14 OR p_eei_information8 = p_eei_information16) THEN
1637 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1638 hr_utility.raise_error;
1639 END IF;
1640 --
1641 IF (p_eei_information10 = p_eei_information12 OR p_eei_information10 = p_eei_information14
1642 OR p_eei_information10 = p_eei_information16) THEN
1643 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1644 hr_utility.raise_error;
1645 END IF;
1646 --
1647 IF (p_eei_information12 = p_eei_information14 OR p_eei_information12 = p_eei_information16) THEN
1648 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1649 hr_utility.raise_error;
1650 END IF;
1651 --
1652 IF (p_eei_information14 = p_eei_information16) THEN
1653 hr_utility.set_message(810,'PAY_376894_NO_DUP_INPUT_VALUE');
1654 hr_utility.raise_error;
1655 END IF;
1656 -- Validation to check if input value is specified without a mapping column in Extra Element Info DDF
1657 IF (p_eei_information5 IS NULL and p_eei_information6 IS NOT NULL)
1658 OR (p_eei_information7 IS NULL and p_eei_information8 IS NOT NULL)
1659 OR (p_eei_information9 IS NULL and p_eei_information10 IS NOT NULL)
1660 OR (p_eei_information11 IS NULL and p_eei_information12 IS NOT NULL)
1661 OR (p_eei_information13 IS NULL and p_eei_information14 IS NOT NULL)
1662 OR (p_eei_information15 IS NULL and p_eei_information16 IS NOT NULL) THEN
1663 hr_utility.set_message(801,'PAY_376895_NO_COL_MAP_MISSING');
1664 hr_utility.raise_error;
1665 END IF;
1666 --
1667 END IF;
1668 --
1669 --
1670 --
1671 IF p_eei_information_category = 'NO_ELEMENT_CODES' THEN
1672
1673 FOR rec_get_ele_code IN csr_get_ele_code(p_element_type_id)
1674 LOOP
1675 IF ( rec_get_ele_code.eei_information2 IS NOT NULL ) THEN
1676
1677 OPEN csr_get_le_details(to_number(rec_get_ele_code.eei_information2));
1678 FETCH csr_get_le_details INTO rec_get_le_details;
1679 CLOSE csr_get_le_details;
1680
1681 IF( p_eei_information2 = rec_get_ele_code.eei_information2) THEN
1682 hr_utility.set_message(801,'PAY_376882_NO_ELE_CODE_LE');
1683 --fnd_message.set_token('LE',rec_get_ele_code.eei_information2);
1684 fnd_message.set_token('LE',rec_get_le_details.name);
1685 hr_utility.raise_error;
1686 ELSIF( p_eei_information2 IS NULL AND p_eei_information1 = rec_get_ele_code.eei_information1) THEN
1687 hr_utility.set_message(801,'PAY_376882_NO_ELE_CODE_LE');
1688 --fnd_message.set_token('LE',rec_get_ele_code.eei_information2);
1689 fnd_message.set_token('LE',rec_get_le_details.name);
1690 hr_utility.raise_error;
1691 END IF;
1692 END IF;
1693
1694 IF( rec_get_ele_code.eei_information2 IS NULL ) THEN
1695 IF( p_eei_information2 IS NULL) THEN
1696 hr_utility.set_message(801,'PAY_376883_NO_ELE_CODE_LE_GLOB');
1697 hr_utility.raise_error;
1698 ELSIF( p_eei_information2 IS NOT NULL AND p_eei_information1 = rec_get_ele_code.eei_information1) THEN
1699 hr_utility.set_message(801,'PAY_376883_NO_ELE_CODE_LE_GLOB');
1700 hr_utility.raise_error;
1701 END IF;
1702 END IF;
1703
1704 END LOOP;
1705
1706 --
1707 END IF;
1708 --
1709 END CREATE_ELEMENT_ELE_CODE;
1710
1711 --------------------------------------------------------------------------------------------------
1712
1713 PROCEDURE UPDATE_ELEMENT_ELE_CODE
1714 (p_element_type_extra_info_id IN NUMBER
1715 ,p_eei_attribute_category IN VARCHAR2
1716 ,p_eei_attribute1 IN VARCHAR2
1717 ,p_eei_attribute2 IN VARCHAR2
1718 ,p_eei_attribute3 IN VARCHAR2
1719 ,p_eei_attribute4 IN VARCHAR2
1720 ,p_eei_attribute5 IN VARCHAR2
1721 ,p_eei_attribute6 IN VARCHAR2
1722 ,p_eei_attribute7 IN VARCHAR2
1723 ,p_eei_attribute8 IN VARCHAR2
1724 ,p_eei_attribute9 IN VARCHAR2
1725 ,p_eei_attribute10 IN VARCHAR2
1726 ,p_eei_attribute11 IN VARCHAR2
1727 ,p_eei_attribute12 IN VARCHAR2
1728 ,p_eei_attribute13 IN VARCHAR2
1729 ,p_eei_attribute14 IN VARCHAR2
1730 ,p_eei_attribute15 IN VARCHAR2
1731 ,p_eei_attribute16 IN VARCHAR2
1732 ,p_eei_attribute17 IN VARCHAR2
1733 ,p_eei_attribute18 IN VARCHAR2
1734 ,p_eei_attribute19 IN VARCHAR2
1735 ,p_eei_attribute20 IN VARCHAR2
1736 ,p_eei_information_category IN VARCHAR2
1737 ,p_eei_information1 IN VARCHAR2
1738 ,p_eei_information2 IN VARCHAR2
1739 ,p_eei_information3 IN VARCHAR2
1740 ,p_eei_information4 IN VARCHAR2
1741 ,p_eei_information5 IN VARCHAR2
1742 ,p_eei_information6 IN VARCHAR2
1743 ,p_eei_information7 IN VARCHAR2
1744 ,p_eei_information8 IN VARCHAR2
1745 ,p_eei_information9 IN VARCHAR2
1746 ,p_eei_information10 IN VARCHAR2
1747 ,p_eei_information11 IN VARCHAR2
1748 ,p_eei_information12 IN VARCHAR2
1749 ,p_eei_information13 IN VARCHAR2
1750 ,p_eei_information14 IN VARCHAR2
1751 ,p_eei_information15 IN VARCHAR2
1752 ,p_eei_information16 IN VARCHAR2
1753 ,p_eei_information17 IN VARCHAR2
1754 ,p_eei_information18 IN VARCHAR2
1755 ,p_eei_information19 IN VARCHAR2
1756 ,p_eei_information20 IN VARCHAR2
1757 ,p_eei_information21 IN VARCHAR2
1758 ,p_eei_information22 IN VARCHAR2
1759 ,p_eei_information23 IN VARCHAR2
1760 ,p_eei_information24 IN VARCHAR2
1761 ,p_eei_information25 IN VARCHAR2
1762 ,p_eei_information26 IN VARCHAR2
1763 ,p_eei_information27 IN VARCHAR2
1764 ,p_eei_information28 IN VARCHAR2
1765 ,p_eei_information29 IN VARCHAR2
1766 ,p_eei_information30 IN VARCHAR2
1767 ,p_object_version_number IN NUMBER
1768 ) is
1769
1770 CURSOR csr_get_element_type_id ( p_element_type_extra_info_id NUMBER )IS
1771 SELECT element_type_id
1772 FROM pay_element_type_extra_info petei
1773 WHERE petei.information_type='NO_ELEMENT_CODES'
1774 AND petei.element_type_extra_info_id = p_element_type_extra_info_id;
1775
1776 CURSOR csr_get_ele_code_le_glob( p_ele_type_id NUMBER
1777 ,p_ele_code VARCHAR2) IS
1778 SELECT eei_information1 , eei_information2
1779 FROM pay_element_type_extra_info petei
1780 WHERE petei.information_type='NO_ELEMENT_CODES'
1781 AND petei.element_type_id = p_ele_type_id
1782 AND petei.eei_information1 = p_ele_code;
1783
1784 CURSOR csr_get_le_details(p_le_id NUMBER) IS
1785 SELECT hou.name NAME
1786 FROM hr_organization_units hou
1787 ,hr_organization_information hoi
1788 WHERE hoi.organization_id = hou.organization_id
1789 AND hoi.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS'
1790 AND hou.organization_id = p_le_id;
1791
1792 CURSOR csr_get_element_type IS
1793 SELECT element_type_id
1794 FROM pay_element_type_extra_info etei
1795 WHERE etei.information_type ='NO_EOY_REPORTING_CODE_MAPPING'
1796 AND etei.element_type_extra_info_id = p_element_type_extra_info_id;
1797
1798 CURSOR csr_chk_element_eit_year(l_element_type_id NUMBER) IS
1799 SELECT 'Y', etei.eei_information3
1800 FROM pay_element_type_extra_info etei
1801 WHERE etei.element_type_id = l_element_type_id
1802 AND etei.eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING'
1803 AND etei.element_type_extra_info_id <> p_element_type_extra_info_id
1804 AND ((to_number(etei.eei_information1)
1805 between to_number(p_eei_information1) and to_number(nvl(p_eei_information2,'4712')))
1806 OR (to_number(p_eei_information1)
1807 between to_number(etei.eei_information1) and to_number(nvl(etei.eei_information2,'4712')))) ;
1808
1809 --
1810 l_year_status varchar2(1);
1811 l_overlap_code pay_element_type_extra_info.eei_information3%TYPE;
1812 rec_get_element_type csr_get_element_type%ROWTYPE;
1813 rec_get_ele_code_le_glob csr_get_ele_code_le_glob%ROWTYPE;
1814 rec_get_element_type_id csr_get_element_type_id%ROWTYPE;
1815 rec_get_le_details csr_get_le_details%ROWTYPE;
1816
1817 l_element_type_id NUMBER;
1818 --
1819 BEGIN
1820 --
1821 IF p_eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING' THEN
1822 -- Validation to ensure Year of Reporting(EOY) is different then what we already have in Extra Element Info DDF
1823 IF to_number(p_eei_information2) < to_number(p_eei_information1) THEN
1824 hr_utility.set_message(801,'PAY_376896_NO_YEAR_RESTRICT');
1825 hr_utility.raise_error;
1826 END IF;
1827 --
1828 OPEN csr_get_element_type;
1829 FETCH csr_get_element_type INTO rec_get_element_type;
1830 CLOSE csr_get_element_type;
1831 --
1832 OPEN csr_chk_element_eit_year(rec_get_element_type.element_type_id);
1833 FETCH csr_chk_element_eit_year INTO l_year_status, l_overlap_code;
1834 CLOSE csr_chk_element_eit_year;
1835 --
1836 IF (l_year_status = 'Y') THEN
1837 hr_utility.set_message(801,'PAY_376893_NO_YEAR_EXISTS');
1838 fnd_message.set_token('REP_CODE', l_overlap_code); --p_eei_information3);
1839 hr_utility.raise_error;
1840 END IF;
1841 --
1842 -- Validation to check if single input value is mapped to more than one column in Extra Element Info DDF
1843 IF (p_eei_information4 = p_eei_information6 OR p_eei_information4 = p_eei_information8
1844 OR p_eei_information4 = p_eei_information10 OR p_eei_information4 = p_eei_information12
1845 OR p_eei_information4 = p_eei_information14 OR p_eei_information4 = p_eei_information16) THEN
1846 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1847 hr_utility.raise_error;
1848 END IF;
1849 --
1850 IF (p_eei_information6 = p_eei_information8 OR p_eei_information6 = p_eei_information10
1851 OR p_eei_information6 = p_eei_information12 OR p_eei_information6 = p_eei_information14
1852 OR p_eei_information6 = p_eei_information16) THEN
1853 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1854 hr_utility.raise_error;
1855 END IF;
1856 --
1857 IF (p_eei_information8 = p_eei_information10 OR p_eei_information8 = p_eei_information12
1858 OR p_eei_information8 = p_eei_information14 OR p_eei_information8 = p_eei_information16) THEN
1859 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1860 hr_utility.raise_error;
1861 END IF;
1862 --
1863 IF (p_eei_information10 = p_eei_information12 OR p_eei_information10 = p_eei_information14
1864 OR p_eei_information10 = p_eei_information16) THEN
1865 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1866 hr_utility.raise_error;
1867 END IF;
1868 --
1869 IF (p_eei_information12 = p_eei_information14 OR p_eei_information12 = p_eei_information16) THEN
1870 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1871 hr_utility.raise_error;
1872 END IF;
1873 --
1874 IF (p_eei_information14 = p_eei_information16) THEN
1875 hr_utility.set_message(801,'PAY_376894_NO_DUP_INPUT_VALUE');
1876 hr_utility.raise_error;
1877 END IF;
1878 -- Validation to check if input value is specified without a mapping column in Extra Element Info DDF
1879 IF (p_eei_information5 IS NULL and p_eei_information6 IS NOT NULL)
1880 OR (p_eei_information7 IS NULL and p_eei_information8 IS NOT NULL)
1881 OR (p_eei_information9 IS NULL and p_eei_information10 IS NOT NULL)
1882 OR (p_eei_information11 IS NULL and p_eei_information12 IS NOT NULL)
1883 OR (p_eei_information13 IS NULL and p_eei_information14 IS NOT NULL)
1884 OR (p_eei_information15 IS NULL and p_eei_information16 IS NOT NULL) THEN
1885 hr_utility.set_message(801,'PAY_376895_NO_COL_MAP_MISSING');
1886 hr_utility.raise_error;
1887 END IF;
1888 --
1889 END IF;
1890 --
1891 -- EOY Report Validations @ Extra Element Info DDF
1892 --
1893 IF p_eei_information_category = 'NO_ELEMENT_CODES' THEN
1894
1895 OPEN csr_get_element_type_id ( p_element_type_extra_info_id );
1896 FETCH csr_get_element_type_id INTO rec_get_element_type_id;
1897 CLOSE csr_get_element_type_id;
1898
1899 l_element_type_id := rec_get_element_type_id.element_type_id;
1900
1901
1902 -- Validation to ensure element code once entered cannot be entered again on that Legal Employer or globally.
1903 OPEN csr_get_ele_code_le_glob(l_element_type_id , p_eei_information1);
1904 FETCH csr_get_ele_code_le_glob INTO rec_get_ele_code_le_glob;
1905
1906 IF ( csr_get_ele_code_le_glob%FOUND AND rec_get_ele_code_le_glob.eei_information2 is NOT NULL) THEN
1907
1908 OPEN csr_get_le_details(to_number(rec_get_ele_code_le_glob.eei_information2));
1909 FETCH csr_get_le_details INTO rec_get_le_details;
1910 CLOSE csr_get_le_details;
1911
1912 hr_utility.set_message(801,'PAY_376882_NO_ELE_CODE_LE');
1913 --fnd_message.set_token('LE',rec_get_ele_code_le_glob.eei_information2);
1914 fnd_message.set_token('LE',rec_get_le_details.name);
1915 hr_utility.raise_error;
1916 END IF;
1917
1918
1919 IF ( csr_get_ele_code_le_glob%FOUND AND rec_get_ele_code_le_glob.eei_information2 IS NULL) THEN
1920 hr_utility.set_message(801,'PAY_376883_NO_ELE_CODE_LE_GLOB');
1921 hr_utility.raise_error;
1922 END IF;
1923
1924 CLOSE csr_get_ele_code_le_glob;
1925
1926 --
1927 END IF;
1928 --
1929 END UPDATE_ELEMENT_ELE_CODE;
1930
1931 -----------------------------------------------------------------------------------------------
1932
1933 PROCEDURE update_ele_entry_bp
1934 ( p_effective_date IN DATE
1935 ) IS
1936 --
1937 CURSOR csr_get_session_id IS
1938 SELECT 1
1939 FROM fnd_sessions
1940 WHERE session_id = userenv('sessionid');
1941 --
1942 l_sess_row csr_get_session_id%ROWTYPE;
1943 BEGIN
1944 --
1945 OPEN csr_get_session_id;
1946 FETCH csr_get_session_id INTO l_sess_row;
1947 IF csr_get_session_id%NOTFOUND THEN
1948 INSERT INTO fnd_sessions (session_id, effective_date)
1949 VALUES (userenv('sessionid'), trunc(p_effective_date));
1950 END IF;
1951 CLOSE csr_get_session_id;
1952 --
1953 END;
1954
1955
1956 PROCEDURE create_ele_entry_bp
1957 ( p_effective_date IN DATE
1958 ) IS
1959 --
1960 CURSOR csr_get_session_id IS
1961 SELECT 1
1962 FROM fnd_sessions
1963 WHERE session_id = userenv('sessionid');
1964 --
1965 l_sess_row csr_get_session_id%ROWTYPE;
1966 BEGIN
1967 --
1968 OPEN csr_get_session_id;
1969 FETCH csr_get_session_id INTO l_sess_row;
1970 IF csr_get_session_id%NOTFOUND THEN
1971 INSERT INTO fnd_sessions (session_id, effective_date)
1972 VALUES (userenv('sessionid'), trunc(p_effective_date));
1973 END IF;
1974 CLOSE csr_get_session_id;
1975 --
1976 END;
1977
1978 PROCEDURE update_ele_entry_ap
1979 ( p_effective_date IN DATE
1980 ) IS
1981 BEGIN
1982 DELETE FROM fnd_sessions WHERE session_id = userenv('sessionid');
1983 END;
1984 --
1985 PROCEDURE create_ele_entry_ap
1986 ( p_effective_date IN DATE
1987 ) IS
1988 BEGIN
1989 DELETE FROM fnd_sessions WHERE session_id = userenv('sessionid');
1990 END;
1991
1992 -----------------------------------------------------------------------------------------------
1993
1994
1995 END hr_no_validate_pkg;
[Home] [Help]