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