1 PACKAGE BODY HR_FI_VALIDATE_PKG AS
2 /* $Header: pefivald.pkb 120.21 2011/08/05 15:30:48 asudhaka 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 ,p_date_of_birth in date default null
9 ,p_per_information8 in varchar2 default null
10 ) IS
11
12 l_type varchar2(1) := NULL;
13 l_field varchar2(300) := NULL;
14 l_valid_date varchar2(10);
15 l_date date;
16
17 CURSOR c_type IS
18 SELECT 'Y'
19 FROM per_person_types ppt
20 WHERE ppt.system_person_type like 'EMP%'
21 AND ppt.person_type_Id = p_person_type_id;
22
23 BEGIN
24 l_type := NULL;
25 OPEN c_type;
26 FETCH c_type INTO l_type;
27 CLOSE c_type;
28
29
30 --Validate not null fields
31 IF l_type IS NOT NULL THEN
32 /* 12800288 - Supressed the below validation if employee is transferred via Global deployment */
33 IF per_per_bus.g_global_transfer_in_process IS NULL
34 or per_per_bus.g_global_transfer_in_process = false THEN
35
36 IF p_national_identifier IS NULL or p_national_identifier = hr_api.g_varchar2 THEN
37 l_field := hr_general.decode_lookup('FI_FORM_LABELS','PIN');
38 END IF;
39 END IF;
40 END IF;
41
42 IF l_field IS NOT NULL AND fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') in ('ERROR','WARN') THEN
43 fnd_message.set_name('PER', 'HR_376603_FI_MANDATORY_MSG');
44 fnd_message.set_token('NAME',l_field, translate => true );
45 hr_utility.raise_error;
46 END IF;
47 IF p_date_of_birth IS NOT NULL AND p_per_information8 IS NOT NULL THEN
48 BEGIN
49 l_date:= fnd_date.canonical_to_date(p_per_information8);
50 IF p_date_of_birth >= l_date THEN
51 fnd_message.set_name('PER', 'HR_376609_FI_INVALID_DATE');
52 hr_utility.raise_error;
53 END IF;
54 EXCEPTION
55 WHEN OTHERS THEN
56 NULL;
57 END ;
58 END IF;
59 END VALIDATE;
60
61 --Procedure for validating person
62 PROCEDURE person_validate
63 (p_person_type_id in number
64 ,p_first_name in varchar2 default null
65 ,p_national_identifier in varchar2 default null
66 ,p_date_of_birth in date default null
67 ,p_per_information8 in varchar2 default null
68 ) IS
69 BEGIN
70 --
71 -- Added for GSI Bug 5472781
72 --
73 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
74 --
75 validate
76 (p_person_type_id => p_person_type_id
77 ,p_first_name => p_first_name
78 ,p_national_identifier => p_national_identifier
79 ,p_date_of_birth => p_date_of_birth
80 ,p_per_information8 => p_per_information8);
81 --
82 END IF;
83 END person_validate;
84
85 --Procedure for validating applicant
86 PROCEDURE applicant_validate
87 (p_business_group_id in number
88 ,p_person_type_id in number
89 ,p_first_name in varchar2 default null
90 ,p_national_identifier in varchar2 default null
91 ,p_date_of_birth in date default null
92 ,p_per_information8 in varchar2 default null
93 ) IS
94 l_person_type_id number ;
95 BEGIN
96 --
97 -- Added for GSI Bug 5472781
98 --
99 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
100 --
101 per_per_bus.chk_person_type
102 (p_person_type_id => l_person_type_id
103 ,p_business_group_id => p_business_group_id
104 ,p_expected_sys_type => 'APL');
105 --
106 validate
107 (p_person_type_id => l_person_type_id
108 ,p_first_name => p_first_name
109 ,p_national_identifier => p_national_identifier
110 ,p_date_of_birth => p_date_of_birth
111 ,p_per_information8 => p_per_information8
112 );
113 END IF;
114 END applicant_validate;
115
116 --Procedure for validating employee
117 PROCEDURE employee_validate
118 (p_business_group_id in number
119 ,p_person_type_id in number
120 ,p_first_name in varchar2 default null
121 ,p_national_identifier in varchar2 default null
122 ,p_date_of_birth in date default null
123 ,p_per_information8 in varchar2 default null
124 ) IS
125 l_person_type_id number ;
126 BEGIN
127 --
128 -- Added for GSI Bug 5472781
129 --
130 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
131 --
132 per_per_bus.chk_person_type
133 (p_person_type_id => l_person_type_id
134 ,p_business_group_id => p_business_group_id
135 ,p_expected_sys_type => 'EMP'
136 );
137 validate
138 (p_person_type_id => l_person_type_id
139 ,p_first_name => p_first_name
140 ,p_national_identifier => p_national_identifier
141 ,p_date_of_birth => p_date_of_birth
142 ,p_per_information8 => p_per_information8
143 );
144 END IF;
145 END employee_validate;
146
147 --Procedure for validating contact/cwk
148 PROCEDURE contact_cwk_validate
149 (p_business_group_id in number
150 ,p_person_type_id in number
151 ,p_first_name in varchar2 default null
152 ,p_national_identifier in varchar2 default null
153 ,p_date_of_birth in date default null
154 ,p_per_information8 in varchar2 default null
155 ) IS
156 l_person_type_id number ;
157 BEGIN
158 --
159 -- Added for GSI Bug 5472781
160 --
161 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
162 --
163 per_per_bus.chk_person_type
164 (p_person_type_id => l_person_type_id
165 ,p_business_group_id => p_business_group_id
166 ,p_expected_sys_type => 'OTHER'
167 );
168 validate
169 (p_person_type_id => l_person_type_id
170 ,p_first_name => p_first_name
171 ,p_national_identifier => p_national_identifier
172 ,p_date_of_birth => p_date_of_birth
173 ,p_per_information8 => p_per_information8
174 );
175 END IF;
176 END contact_cwk_validate;
177
178 --Procedure for validating qualification insertion
179 PROCEDURE qual_insert_validate
180 (p_business_group_id in number
181 ,p_qua_information_category in varchar2 default null
182 ,p_person_id in number
183 ,p_qua_information1 in varchar2 default null
184 ,p_qua_information2 in varchar2 default null
185 ) IS
186 l_count NUMBER;
187 BEGIN
188 --
189 -- Added for GSI Bug 5472781
190 --
191 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
192 --
193 IF substr(p_qua_information1,1,1) not in ('0','1','2','3','4','5','6','7','9') OR substr(p_qua_information1,2,1) not in ('0','1','2','3','4','5','6','7','8','9') THEN
194 fnd_message.set_name('PER', 'HR_376605_FI_EDUCATION_CODE');
195 hr_utility.raise_error;
196 END IF;
197
198 IF p_qua_information1 is not null and length(p_qua_information1) not in (2,6) THEN
199 fnd_message.set_name('PER', 'HR_376608_FI_EC_INVALID_LENGTH');
200 hr_utility.raise_error;
201 END IF;
202 validate_number(p_qua_information1,NULL,'HR_376605_FI_EDUCATION_CODE');
203 IF p_qua_information2 ='Y' THEN
204 select count(*)
205 into l_count
206 from per_qualifications pq
207 where pq.business_group_id = p_business_group_id
208 and pq.person_id = p_person_id
209 and pq.qua_information_category='FI'
210 and pq.qua_information2='Y';
211 IF l_count > 0 then
212 fnd_message.set_name('PER', 'HR_376606_FI_PREFERRED_LEVEL');
213 hr_utility.raise_error;
214 END IF;
215 END IF ;
216 END IF;
217 END qual_insert_validate;
218
219 --Procedure for validating qualification Update
220 PROCEDURE qual_update_validate
221 (p_qua_information_category in varchar2 default null
222 ,p_qualification_id in number
223 ,p_qua_information1 in varchar2 default null
224 ,p_qua_information2 in varchar2 default null
225 ) IS
226 l_count NUMBER;
227 l_person_id NUMBER;
228 l_business_group_id NUMBER;
229
230 CURSOR c_person_id IS
231 SELECT person_id,business_group_id
232 FROM per_qualifications
233 WHERE qualification_id = p_qualification_id;
234
235 BEGIN
236 --
237 -- Added for GSI Bug 5472781
238 --
239 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
240 --
241 IF substr(p_qua_information1,1,1) not in ('0','1','2','3','4','5','6','7','9') OR substr(p_qua_information1,2,1) not in ('0','1','2','3','4','5','6','7','8','9') THEN
242 fnd_message.set_name('PER', 'HR_376605_FI_EDUCATION_CODE');
243 hr_utility.raise_error;
244 END IF;
245 validate_number(p_qua_information1,NULL,'HR_376605_FI_EDUCATION_CODE');
246 IF p_qua_information1 is not null and length(p_qua_information1) not in (2,6) THEN
247 fnd_message.set_name('PER', 'HR_376608_FI_EC_INVALID_LENGTH');
248 hr_utility.raise_error;
249 END IF;
250 IF p_qua_information2 ='Y' THEN
251 l_person_id := NULL;
252 OPEN c_person_id;
253 FETCH c_person_id INTO l_person_id,l_business_group_id;
254 CLOSE c_person_id;
255
256 select count(*)
257 into l_count
258 from per_qualifications pq
259 where pq.business_group_id = l_business_group_id
260 and pq.person_id = l_person_id
261 and pq.qualification_id <> p_qualification_id
262 and pq.qua_information_category='FI'
263 and pq.qua_information2='Y';
264
265 IF l_count > 0 then
266 fnd_message.set_name('PER', 'HR_376606_FI_PREFERRED_LEVEL');
267 hr_utility.raise_error;
268 END IF;
269
270 END IF;
271 END IF;
272 END qual_update_validate ;
273
274
275
276 -- Procedure for Organisation Local Units
277
278 --___________________________________________VALIDATE_CREATE_ORG_INF_____________________________________________
279
280 PROCEDURE validate_create_org_inf
281 (p_effective_date IN DATE
282 ,p_org_info_type_code IN VARCHAR2
283 ,p_organization_id IN NUMBER
284 ,p_org_information1 IN VARCHAR2 DEFAULT null
285 ,p_org_information2 IN VARCHAR2 DEFAULT null
286 ,p_org_information3 IN VARCHAR2 DEFAULT null
287 ,p_org_information4 IN VARCHAR2 DEFAULT null
288 ,p_org_information5 IN VARCHAR2 DEFAULT null
289 ,p_org_information6 IN VARCHAR2 DEFAULT null
290 ,p_org_information7 IN VARCHAR2 DEFAULT null
291 ,p_org_information8 IN VARCHAR2 DEFAULT null
292 ,p_org_information9 IN VARCHAR2 DEFAULT null
293 ) IS
294 l_length NUMBER;
295 l_pipn_length NUMBER ;
296
297 l_last_two_digits VARCHAR2(2);
298 l_eleventh_digit VARCHAR2(1) ;
299 l_calculated_eleventh_digit VARCHAR2(1);
300 l_warning VARCHAR2(50);
301 l_return VARCHAR2(50);
302 l_field VARCHAR2(300) := NULL;
303
304 l_business_group_id hr_organization_units.business_group_id%TYPE;
305 l_org_information1 hr_organization_information.org_information1%TYPE;
306 l_pension_type hr_organization_information.org_information1%TYPE;
307 l_pension_grp hr_organization_information.org_information1%TYPE;
308
309 l_session_date DATE ;
310 l_count NUMBER ;
311
312 INVALID_POLICY_NUMBER_FORMAT EXCEPTION;
313 INVALID_Y_NUMBER_FORMAT EXCEPTION;
314 INVALID_PP_CUSTOMER_NUM_FORMAT EXCEPTION;
315 /* INVALID_PIP_NUMBER_FORMAT EXCEPTION;*/
316 UNIQUE_LOCAL_UNITS EXCEPTION;
317 UNIQUE_Y_NUMBER EXCEPTION;
318 UNIQUE_DEPT_CODE EXCEPTION;
319 UNIQUE_GROUP_CODE EXCEPTION;
320 UNIQUE_PENSION_TYPE EXCEPTION;
321 UNIQUE_PENSION_INS_NUM EXCEPTION;
322 UNIQUE_LU_PENSION_INS_NUM EXCEPTION;
323 UNIQUE_LE_LC EXCEPTION;
324 UNIQUE_LE_AP EXCEPTION;
325 UNIQUE_LU_AP EXCEPTION;
326 INVALID_DATE EXCEPTION;
327 INVALID_TYEL_GROUP EXCEPTION;
328 UNIQUE_PENSION_USER_PT EXCEPTION;
329 UNIQUE_PG_DTLS_PG EXCEPTION;
330
331
332
333 CURSOR getbgid
334 IS
335 SELECT business_group_id
336 FROM hr_organization_units
337 WHERE organization_id = p_organization_id;
338
339 CURSOR orglocalunit
340 IS
341 SELECT COUNT(*)
342 FROM hr_organization_units o , hr_organization_information hoi ,fnd_sessions s
343 WHERE o.organization_id = hoi.organization_id
344 AND hoi.org_information_context = 'CLASS'
345 AND hoi.org_information1 = 'FI_LOCAL_UNIT'
346 AND o.business_group_id = l_business_group_id
347 AND TO_CHAR(o.organization_id) IN
348 (
349 SELECT hoinf.org_information1
350 FROM hr_organization_units org, hr_organization_information hoinf , fnd_sessions s
351 WHERE org.business_group_id = l_business_group_id
352 AND org.organization_id = hoinf.organization_id
353 AND hoinf.org_information_context = 'FI_LOCAL_UNITS'
354 AND s.session_id = userenv('sessionid')
355 AND s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
356 )
357 AND s.session_id = userenv('sessionid')
358 AND s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
359 AND o.organization_id = p_org_information1;
360
361
362 CURSOR getynumber
363 IS
364 SELECT count(*)
365 FROM hr_organization_information hoi, hr_organization_units ou ,fnd_sessions s
366 WHERE (hoi.org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS' or
367 hoi.org_information_context = 'FI_EXTERNAL_COMPANY_DETAILS')
368 AND ou.organization_id = hoi.organization_id
369 AND ou.organization_id <> nvl(p_organization_id , 0)
370 AND ou.business_group_id = l_business_group_id
371 AND hoi.org_information1 = p_org_information1 ;
372
373 CURSOR c_ins_group_code IS
374 SELECT COUNT(*)
375 FROM hr_organization_information hoi, hr_organization_units ou
376 WHERE (hoi.org_information_context = 'FI_PENSION_TYPES')
377 AND ou.organization_id = hoi.organization_id
378 AND ou.organization_id = nvl(p_organization_id , 0)
379 AND ou.business_group_id = l_business_group_id
380 AND hoi.org_information1 = p_org_information1
381 AND hoi.org_information2 = p_org_information2;
382
383 CURSOR c_ins_tyel_pt IS
384 SELECT COUNT(*)
385 FROM hr_organization_information hoi, hr_organization_units ou
386 WHERE (hoi.org_information_context = 'FI_PENSION_TYPES')
387 AND ou.organization_id = hoi.organization_id
388 AND ou.organization_id = nvl(p_organization_id , 0)
389 AND ou.business_group_id = l_business_group_id
390 AND hoi.org_information1 = p_org_information1
391 AND hoi.org_information2 IS null
392 AND hoi.org_information3 IS null;
393
394
395 CURSOR c_ins_dept_code IS
396 SELECT COUNT(*)
397 FROM hr_organization_information hoi, hr_organization_units ou
398 WHERE (hoi.org_information_context = 'FI_PENSION_DEPARTMENT_CODES')
399 AND ou.organization_id = hoi.organization_id
400 AND ou.organization_id = nvl(p_organization_id , 0)
401 AND ou.business_group_id = l_business_group_id
402 AND hoi.org_information3 = p_org_information3
403 AND hoi.org_information1 = p_org_information1;
404
405 CURSOR c_ins_le_lc IS
406 SELECT COUNT(*)
407 FROM hr_organization_information hoi, hr_organization_units ou
408 WHERE (hoi.org_information_context = 'FI_PENSION_DEPARTMENT_CODES')
409 AND ou.organization_id = hoi.organization_id
410 AND ou.business_group_id = l_business_group_id
411 AND hoi.org_information1 = p_org_information1
412 AND hoi.org_information2 = p_org_information2 ;
413
414
415 CURSOR c_ins_pp_ins_num IS
416 SELECT COUNT(*)
417 FROM hr_organization_information hoi, hr_organization_units ou
418 WHERE (hoi.org_information_context = 'FI_PENSION_PROVIDERS')
419 AND ou.organization_id = hoi.organization_id
420 AND ou.business_group_id = l_business_group_id
421 AND hoi.org_information6 = p_org_information6 ;
422
423
424 CURSOR c_ins_lu_pp_ins_num IS
425 SELECT COUNT(*)
426 FROM hr_organization_information hoi, hr_organization_units ou
427 WHERE (hoi.org_information_context = 'FI_LU_PENSION_PROVIDERS')
428 AND ou.organization_id = hoi.organization_id
429 AND ou.organization_id = nvl(p_organization_id , 0)
430 AND ou.business_group_id = l_business_group_id
431 AND hoi.org_information1 = p_org_information1 ;
432
433
434 CURSOR c_ins_le_ap_dtls IS
435 SELECT COUNT(*)
436 FROM hr_organization_information hoi, hr_organization_units ou
437 WHERE (hoi.org_information_context = 'FI_ACCIDENT_PROVIDERS')
438 AND ou.organization_id = hoi.organization_id
439 AND ou.organization_id = nvl(p_organization_id , 0)
440 AND ou.business_group_id = l_business_group_id
441 AND (( fnd_date.canonical_to_date(p_org_information1) between fnd_date.canonical_to_date(hoi.org_information1) AND
442 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
443 OR ( nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hoi.org_information1) AND
444 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
445 OR ( fnd_date.canonical_to_date(hoi.org_information1) between fnd_date.canonical_to_date(p_org_information1) AND
446 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')))
447 OR ( nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(p_org_information1) AND
448 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY'))));
449
450 CURSOR c_ins_lu_ap_dtls IS
451 SELECT COUNT(*)
452 FROM hr_organization_information hoi, hr_organization_units ou
453 WHERE (hoi.org_information_context = 'FI_LU_ACCIDENT_PROVIDERS')
454 AND ou.organization_id = hoi.organization_id
455 AND ou.organization_id = nvl(p_organization_id , 0)
456 AND ou.business_group_id = l_business_group_id
457 AND (( fnd_date.canonical_to_date(p_org_information1) between fnd_date.canonical_to_date(hoi.org_information1) AND
458 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
459 OR ( nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hoi.org_information1) AND
460 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
461 OR ( fnd_date.canonical_to_date(hoi.org_information1) between fnd_date.canonical_to_date(p_org_information1) AND
462 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')))
463 OR ( nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(p_org_information1) AND
464 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY'))));
465
466 CURSOR c_ins_pp_user_pt IS
467 SELECT COUNT(*)
468 FROM hr_organization_information hoi, hr_organization_units ou
469 WHERE (hoi.org_information_context = 'FI_PENSION_PROVIDERS')
470 AND ou.organization_id = hoi.organization_id
471 AND ou.organization_id = nvl(p_organization_id , 0)
472 AND ou.business_group_id = l_business_group_id
473 AND trim(hoi.org_information9) = trim(p_org_information9) ;
474
475
476 CURSOR c_pg_dtls_pg IS
477 SELECT COUNT(*)
478 FROM hr_organization_information hoi, hr_organization_units ou
479 WHERE (hoi.org_information_context = 'FI_PENSION_GROUP_DETAILS')
480 AND ou.organization_id = hoi.organization_id
481 AND ou.business_group_id = l_business_group_id
482 AND hoi.org_information1 = p_org_information1
483 AND hoi.org_information2 = p_org_information2 ;
484
485
486 BEGIN
487 --
488 -- Added for GSI Bug 5472781
489 --
490 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
491 --
492
493 OPEN getbgid;
494 FETCH getbgid INTO l_business_group_id;
495 CLOSE getbgid;
496
497 IF p_org_info_type_code = 'FI_LOCAL_UNITS'
498 THEN
499 OPEN orglocalunit;
500 FETCH orglocalunit INTO l_count;
501 CLOSE orglocalunit;
502 IF l_count > 0
503 THEN
504 RAISE UNIQUE_LOCAL_UNITS ;
505 END IF ;
506
507
508 END IF ;
509 IF p_org_info_type_code = 'FI_LOCAL_UNIT_DETAILS'
510 THEN -- LOCAL_UNIT_DETAILS
511
512 validate_number(p_org_information1,hr_general.decode_lookup('FI_FORM_LABELS','SUB_DISBURSEMENT'));
513 validate_number(p_org_information2,hr_general.decode_lookup('FI_FORM_LABELS','LOCAL_UNIT_NUMBER'));
514 validate_number(p_org_information4,hr_general.decode_lookup('FI_FORM_LABELS','EMP_ORG_MEMBERSHIP'));
515
516 END IF ; -- end of LOCAL_UNIT_DETAILS
517 --------------------------- Y-Number Unique Bug ----------------------------
518
519 IF p_org_info_type_code = 'FI_EXTERNAL_COMPANY_DETAILS'
520 THEN
521 -- For Y-Number --
522 IF p_org_information1 IS NOT NULL
523 THEN
524
525
526 OPEN getynumber;
527 FETCH getynumber INTO l_count;
528 CLOSE getynumber;
529 IF l_count > 0
530 THEN
531 RAISE UNIQUE_Y_NUMBER ;
532 END IF ;
533
534 IF ( (LENGTH(p_org_information1) =9) AND (SUBSTR(p_org_information1,8,1)='-') )
535 THEN -- length
536 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDDDDD-X')='0'
537 THEN
538 RAISE INVALID_Y_NUMBER_FORMAT;
539 END IF ;
540 ELSE
541 RAISE INVALID_Y_NUMBER_FORMAT;
542 END IF ; -- end of length
543 END IF;
544 -- End Of For Y-Number --
545 END IF;
546 --------------------------- Y-Number Unique Bug ----------------------------
547
548 IF p_org_info_type_code = 'FI_LEGAL_EMPLOYER_DETAILS'
549 THEN -- LEGAL_EMPLOYER_DETAILS
550 -- For Y-Number --
551 IF p_org_information1 IS NOT NULL
552 THEN
553 OPEN getynumber;
554 FETCH getynumber INTO l_count;
555 CLOSE getynumber;
556 IF l_count > 0
557 THEN
558 RAISE UNIQUE_Y_NUMBER ;
559 END IF ;
560
561 IF ( (LENGTH(p_org_information1) =9) AND (SUBSTR(p_org_information1,8,1)='-') )
562 THEN -- length
563 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDDDDD-X')='0'
564 THEN
565 RAISE INVALID_Y_NUMBER_FORMAT;
566 END IF ;
567 ELSE
568 RAISE INVALID_Y_NUMBER_FORMAT;
569 END IF ; -- end of length
570 END IF;
571 -- End Of For Y-Number --
572
573 -- 3900118 To validate whole number
574 validate_number(p_org_information6,hr_general.decode_lookup('FI_FORM_LABELS','EMP_ORG_MEMBERSHIP'));
575 -- 3900118 To validate whole number
576
577
578
579 END IF ; -- end of LEGAL_EMPLOYER_DETAILS
580
581 IF p_org_info_type_code = 'FI_EXTERNAL_COMPANY_DETAILS'
582 THEN -- FI_EXTERNAL_COMPANY_DETAILS
583 -- For Y-Number --
584 IF p_org_information1 IS NOT NULL THEN
585 IF ( (LENGTH(p_org_information1) =9) AND (SUBSTR(p_org_information1,8,1)='-') )
586 THEN -- length
587 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDDDDD-X')='0'
588 THEN
589 RAISE INVALID_Y_NUMBER_FORMAT;
590 END IF ;
591 ELSE
592 RAISE INVALID_Y_NUMBER_FORMAT;
593 END IF ;
594 END IF;
595 -- End Of For Y-Number --
596 -- For PIN --
597 IF p_org_information2 IS NOT NULL
598 THEN
599 BEGIN
600 select EFFECTIVE_DATE into l_session_date from fnd_sessions where SESSION_ID = userenv('SESSIONID');
601 l_return := hr_ni_chk_pkg.validate_national_identifier(p_org_information2,null,null,'WHEN-VALIDATE-RECORD',null,l_business_group_id,'FI',l_session_date,l_warning,null,null,null);
602 EXCEPTION
603 WHEN OTHERS
604 THEN
605 fnd_message.set_name('PAY', 'HR_FI_INVALID_NATIONAL_ID');
606 hr_utility.raise_error;
607 END ;
608 END IF;
609 -- End Of PIN --
610 END IF ; -- end of FI_EXTERNAL_COMPANY_DETAILS
611
612 IF p_org_info_type_code = 'FI_PENSION_TYPES'
613 THEN -- FI_PENSION_TYPES
614
615
616 IF p_org_information1 ='TYEL' THEN
617
618 IF (( p_org_information2 IS NULL AND p_org_information3 IS NOT NULL )
619 OR
620 ( p_org_information2 IS NOT NULL AND p_org_information3 IS NULL ))
621 THEN
622
623 RAISE INVALID_TYEL_GROUP ;
624 -- Group Code
625 validate_number(p_org_information2,hr_general.decode_lookup('FI_FORM_LABELS','GROUP_CODE'),'HR_376633_FI_WHOLE_NUMBER');
626
627
628 END IF;
629
630 IF ( p_org_information2 IS NULL AND p_org_information3 IS NULL )
631 THEN
632
633 OPEN c_ins_tyel_pt;
634 FETCH c_ins_tyel_pt INTO l_count;
635 CLOSE c_ins_tyel_pt;
636 IF l_count > 0 THEN
637 RAISE UNIQUE_PENSION_TYPE ;
638 END IF ;
639
640 END IF;
641
642
643 IF ( p_org_information2 IS NOT NULL )
644 THEN
645
646 OPEN c_ins_group_code;
647 FETCH c_ins_group_code INTO l_count;
648 CLOSE c_ins_group_code;
649 IF l_count > 0 THEN
650 RAISE UNIQUE_GROUP_CODE ;
651 END IF ;
652
653 END IF;
654
655
656 END IF;
657
658 END IF;
659
660 IF p_org_info_type_code = 'FI_PENSION_DEPARTMENT_CODES'
661 THEN -- FI_PENSION_DEPARTMENT_CODES
662 -- Department Code
663 validate_number(p_org_information3,hr_general.decode_lookup('FI_FORM_LABELS','DEPARTMENT_CODE'),'HR_376633_FI_WHOLE_NUMBER');
664
665 OPEN c_ins_dept_code;
666 FETCH c_ins_dept_code INTO l_count;
667 CLOSE c_ins_dept_code;
668 IF l_count > 0 THEN
669 RAISE UNIQUE_DEPT_CODE ;
670 END IF ;
671
672 OPEN c_ins_le_lc;
673 FETCH c_ins_le_lc INTO l_count;
674 CLOSE c_ins_le_lc;
675 IF l_count > 0 THEN
676 RAISE UNIQUE_LE_LC ;
677 END IF ;
678
679 END IF;
680
681 IF p_org_info_type_code = 'FI_PENSION_GROUP_DETAILS' THEN
682
683 OPEN c_pg_dtls_pg;
684 FETCH c_pg_dtls_pg INTO l_count;
685 CLOSE c_pg_dtls_pg;
686 IF l_count > 0 THEN
687 RAISE UNIQUE_PG_DTLS_PG ;
688 END IF ;
689
690 END IF;
691
692
693 IF p_org_info_type_code = 'FI_PENSION_PROVIDERS' THEN
694
695
696 IF p_org_information2 IS NOT NULL THEN
697
698 IF fnd_date.canonical_to_date(p_org_information2) < fnd_date.canonical_to_date(p_org_information1) THEN
699 RAISE INVALID_DATE ;
700 END IF;
701
702 END IF;
703
704 -- For Pension Provider Customer number --
705 IF p_org_information8 IS NOT NULL
706 THEN
707 IF ( (LENGTH(p_org_information8) =11) AND (SUBSTR(p_org_information8,9,1)='-') )
708 THEN -- length
709 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information8,'DDDDDDDD-DD')='0'
710 THEN
711 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
712 ELSE
713 l_last_two_digits := substr(p_org_information8,10,2);
714 IF l_last_two_digits <> '00'
715 THEN
716 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
717 END IF;
718 END IF ;
719 ELSE
720 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
721 END IF ; -- end of length
722 END IF;
723 -- End Of For Pension Provider Customer number --
724
725 OPEN c_ins_pp_ins_num;
726 FETCH c_ins_pp_ins_num INTO l_count;
727 CLOSE c_ins_pp_ins_num;
728 IF l_count > 0 THEN
729 RAISE UNIQUE_PENSION_INS_NUM ;
730 END IF ;
731
732 OPEN c_ins_pp_user_pt;
733 FETCH c_ins_pp_user_pt INTO l_count;
734 CLOSE c_ins_pp_user_pt;
735 IF l_count > 0 THEN
736 RAISE UNIQUE_PENSION_USER_PT ;
737 END IF ;
738
739
740 -- Pension Insurance Policy Number --
741 /* Commented for FI leg changes 2008 - bug fix 7600677
742 IF p_org_information6 IS NOT NULL
743 THEN
744 l_pipn_length := LENGTH (p_org_information6);
745 IF SUBSTR(p_org_information6,3,1) = '-'
746 THEN -- FIRST FORMAT NN-NNNNNNNT
747 IF( (l_pipn_length >=8) AND (l_pipn_length <=11) )
748 THEN -- Length between 8 to 11
749 IF l_pipn_length = 8
750 THEN -- 46-1234M
751 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDX')='0'
752 THEN
753 RAISE INVALID_PIP_NUMBER_FORMAT;
754 END IF;
755 ELSIF l_pipn_length = 9
756 THEN -- 46-12345M
757 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDDX')='0'
758 THEN
759 RAISE INVALID_PIP_NUMBER_FORMAT;
760 END IF;
761
762 ELSIF l_pipn_length = 10
763 THEN -- 46-123456M
764 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDDDX')='0'
765 THEN
766 RAISE INVALID_PIP_NUMBER_FORMAT;
767 END IF;
768
769 ELSIF l_pipn_length = 11
770 THEN -- 46-1234567M
771 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDDDDX')='0'
772 THEN
773 RAISE INVALID_PIP_NUMBER_FORMAT;
774 END IF;
775 END IF;
776
777
778 ELSE -- length less than 8 or greater than 11 wrong format
779 RAISE INVALID_PIP_NUMBER_FORMAT;
780 END IF; -- end of -- Length between 8 to 11
781 ELSE
782 IF SUBSTR(p_org_information6,5,1) = '-'
783 THEN -- SECOND FORMAT NNNN-NNNNT
784 IF l_pipn_length = 10
785 THEN -- 1234-5678M
786 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DDDD-DDDDX')='0'
787 THEN
788 RAISE INVALID_PIP_NUMBER_FORMAT;
789 END IF;
790 ELSE
791 RAISE INVALID_PIP_NUMBER_FORMAT;
792 END IF;
793 ELSE -- Wrong format so raise error
794 RAISE INVALID_PIP_NUMBER_FORMAT;
795 END IF ;
796 END IF; -- end of format check
797 END IF ; */
798
799 -- End Of Pension Insurance Policy Number --
800
801
802 END IF;
803
804
805 IF p_org_info_type_code = 'FI_LU_PENSION_PROVIDERS' THEN
806
807 OPEN c_ins_lu_pp_ins_num;
808 FETCH c_ins_lu_pp_ins_num INTO l_count;
809 CLOSE c_ins_lu_pp_ins_num;
810 IF l_count > 0 THEN
811 RAISE UNIQUE_LU_PENSION_INS_NUM ;
812 END IF ;
813
814 -- Pension Insurance Policy Number --
815 /* Commenting for FI leg changes 2008 - bug fix 7600677
816 IF p_org_information1 IS NOT NULL
817 THEN
818 l_pipn_length := LENGTH (p_org_information1);
819 IF SUBSTR(p_org_information1,3,1) = '-'
820 THEN -- FIRST FORMAT NN-NNNNNNNT
821 IF( (l_pipn_length >=8) AND (l_pipn_length <=11) )
822 THEN -- Length between 8 to 11
823 IF l_pipn_length = 8
824 THEN -- 46-1234M
825 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDX')='0'
826 THEN
827 RAISE INVALID_PIP_NUMBER_FORMAT;
828 END IF;
829 ELSIF l_pipn_length = 9
830 THEN -- 46-12345M
831 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDDX')='0'
832 THEN
833 RAISE INVALID_PIP_NUMBER_FORMAT;
834 END IF;
835 ELSIF l_pipn_length = 10
836 THEN -- 46-123456M
837 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDDDX')='0'
838 THEN
839 RAISE INVALID_PIP_NUMBER_FORMAT;
840 END IF;
841 ELSIF l_pipn_length = 11
842 THEN -- 46-1234567M
843 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDDDDX')='0'
844 THEN
845 RAISE INVALID_PIP_NUMBER_FORMAT;
846 END IF;
847 END IF;
848 ELSE -- length less than 8 or greater than 11 wrong format
849 RAISE INVALID_PIP_NUMBER_FORMAT;
850 END IF; -- end of -- Length between 8 to 11
851 ELSE
852 IF SUBSTR(p_org_information1,5,1) = '-'
853 THEN -- SECOND FORMAT NNNN-NNNNT
854 IF l_pipn_length = 10
855 THEN -- 1234-5678M
856 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDD-DDDDX')='0'
857 THEN
858 RAISE INVALID_PIP_NUMBER_FORMAT;
859 END IF;
860 ELSE
861 RAISE INVALID_PIP_NUMBER_FORMAT;
862 END IF;
863 ELSE -- Wrong format so raise error
864 RAISE INVALID_PIP_NUMBER_FORMAT;
865 END IF ;
866 END IF; -- end of format check
867 END IF ; */
868 -- End Of Pension Insurance Policy Number
869
870 -- For Pension Provider Customer number --
871 IF p_org_information2 IS NOT NULL
872 THEN
873 IF ( (LENGTH(p_org_information2) =11) AND (SUBSTR(p_org_information2,9,1)='-') )
874 THEN -- length
875 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information2,'DDDDDDDD-DD')='0'
876 THEN
877 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
878 ELSE
879 l_last_two_digits := substr(p_org_information2,10,2);
880 IF l_last_two_digits <> '00'
881 THEN
882 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
883 END IF;
884 END IF ;
885 ELSE
886 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
887 END IF ; -- end of length
888 END IF;
889 -- End Of For Pension Provider Customer number --
890
891
892 END IF;
893
894
895 IF p_org_info_type_code = 'FI_ACCIDENT_PROVIDERS' THEN
896
897 IF p_org_information2 IS NOT NULL THEN
898
899 IF fnd_date.canonical_to_date(p_org_information2) < fnd_date.canonical_to_date(p_org_information1) THEN
900 RAISE INVALID_DATE ;
901 END IF;
902
903 END IF;
904
905
906
907 OPEN c_ins_le_ap_dtls ;
908 FETCH c_ins_le_ap_dtls INTO l_count;
909 CLOSE c_ins_le_ap_dtls ;
910 IF l_count > 0 THEN
911 RAISE UNIQUE_LE_AP ;
912 END IF ;
913 /* Commenting the validation for Accident Insurance Policy Number for Enhancement 2008-2009 */
914 /* Please refer Bug 8425533 for further details */
915
916 /* -- For Accident Insurance Policy Number --
917
918 IF p_org_information4 IS NOT NULL
919 THEN
920 IF ( (LENGTH(p_org_information4) =13) AND (SUBSTR(p_org_information4,4,1)='-') AND (SUBSTR(p_org_information4,12,1)='-') )
921 THEN -- length
922 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information4,'DDD-DDDDDDD-X')='0'
923 THEN
924 RAISE INVALID_POLICY_NUMBER_FORMAT;
925 END IF;
926 ELSE
927 RAISE INVALID_POLICY_NUMBER_FORMAT;
928 END IF ;
929 END IF;
930 -- End Of Accident Insurance Policy Number --
931
932 */
933 END IF;
934
935 IF p_org_info_type_code = 'FI_LU_ACCIDENT_PROVIDERS' THEN
936
937 IF p_org_information2 IS NOT NULL THEN
938
939 IF fnd_date.canonical_to_date(p_org_information2) < fnd_date.canonical_to_date(p_org_information1) THEN
940 RAISE INVALID_DATE ;
941 END IF;
942
943 END IF;
944
945 OPEN c_ins_lu_ap_dtls ;
946 FETCH c_ins_lu_ap_dtls INTO l_count;
947 CLOSE c_ins_lu_ap_dtls ;
948 IF l_count > 0 THEN
949 RAISE UNIQUE_LU_AP ;
950 END IF ;
951
952 /* Commenting the validation for Accident Insurance Policy Number for Enhancement 2008-2009 */
953 /* Please refer Bug 8425533 for further details */
954
955 -- For Accident Insurance Policy Number --
956 /*
957
958 IF p_org_information3 IS NOT NULL
959 THEN
960 IF ( (LENGTH(p_org_information3) =13) AND (SUBSTR(p_org_information3,4,1)='-') AND (SUBSTR(p_org_information3,12,1)='-') )
961 THEN -- length
962 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information3,'DDD-DDDDDDD-X')='0'
963 THEN
964 RAISE INVALID_POLICY_NUMBER_FORMAT;
965 END IF;
966 ELSE
967 RAISE INVALID_POLICY_NUMBER_FORMAT;
968 END IF ;
969 END IF;
970 -- End Of Accident Insurance Policy Number --
971
972 */
973
974
975 END IF;
976 END IF;
977
978
979 EXCEPTION
980 WHEN INVALID_POLICY_NUMBER_FORMAT
981 THEN
982
983 l_field := hr_general.decode_lookup('FI_FORM_LABELS','ACC_INS_POLICY');
984 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
985 fnd_message.set_token('NAME',l_field, translate => true );
986 hr_utility.raise_error;
987
988 WHEN INVALID_Y_NUMBER_FORMAT
989 THEN
990
991 l_field := hr_general.decode_lookup('FI_FORM_LABELS','Y_NUMBER');
992 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
993 fnd_message.set_token('NAME',l_field, translate => true );
994 hr_utility.raise_error;
995
996 WHEN INVALID_PP_CUSTOMER_NUM_FORMAT
997 THEN
998 l_field := hr_general.decode_lookup('FI_FORM_LABELS','PEN_PROV_CUST');
999 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
1000 fnd_message.set_token('NAME',l_field, translate => true );
1001 hr_utility.raise_error;
1002
1003 /* WHEN INVALID_PIP_NUMBER_FORMAT - bug fix 7600677
1004 THEN
1005 l_field := hr_general.decode_lookup('FI_FORM_LABELS','PEN_INS_POLICY');
1006 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
1007 fnd_message.set_token('NAME',l_field, translate => true );
1008 hr_utility.raise_error;*/
1009
1010 WHEN UNIQUE_LOCAL_UNITS
1011 THEN
1012
1013 fnd_message.set_name('PER', 'HR_376614_FI_UNIQUE_LOCAL_UNIT');
1014 hr_utility.raise_error;
1015
1016 WHEN UNIQUE_Y_NUMBER
1017 THEN
1018 l_field := hr_general.decode_lookup('FI_FORM_LABELS','Y_NUMBER');
1019 fnd_message.set_name('PER', 'HR_376613_FI_UNIQUE_MSG');
1020 fnd_message.set_token('NAME',l_field, translate => true );
1021 hr_utility.raise_error;
1022 WHEN UNIQUE_DEPT_CODE
1023 THEN
1024
1025 fnd_message.set_name('PAY', 'HR_376630_FI_UNIQUE_DEPT_CODE');
1026 hr_utility.raise_error;
1027
1028 WHEN UNIQUE_GROUP_CODE
1029 THEN
1030
1031 fnd_message.set_name('PAY', 'HR_376632_FI_UNIQUE_GROUP_CODE');
1032 hr_utility.raise_error;
1033
1034 WHEN UNIQUE_PENSION_TYPE
1035 THEN
1036
1037 fnd_message.set_name('PAY', 'HR_376647_FI_UNIQ_PENSION_TYPE');
1038 hr_utility.raise_error;
1039
1040 WHEN UNIQUE_LE_LC
1041 THEN
1042
1043 fnd_message.set_name('PAY', 'HR_376631_FI_UNIQUE_LE_LC');
1044 hr_utility.raise_error;
1045
1046
1047 WHEN UNIQUE_LE_AP
1048 THEN
1049
1050 fnd_message.set_name('PAY', 'HR_376638_FI_UNIQUE_ACC_PROV');
1051 hr_utility.raise_error;
1052 WHEN UNIQUE_LU_AP
1053 THEN
1054
1055 fnd_message.set_name('PAY', 'HR_376645_FI_LU_UNIQ_ACC_PROV');
1056 hr_utility.raise_error;
1057 WHEN INVALID_DATE
1058 THEN
1059
1060 fnd_message.set_name('PAY', 'HR_376639_FI_VALID_DATE');
1061 hr_utility.raise_error;
1062
1063 WHEN INVALID_TYEL_GROUP
1064 THEN
1065
1066 fnd_message.set_name('PAY', 'HR_376648_FI_TEL_GROUP_DTLS');
1067 hr_utility.raise_error;
1068
1069 WHEN UNIQUE_PENSION_INS_NUM
1070 THEN
1071 fnd_message.set_name('PAY', 'HR_376650_FI_UNIQ_PEN_INS_NUM');
1072 hr_utility.raise_error;
1073
1074 WHEN UNIQUE_LU_PENSION_INS_NUM
1075 THEN
1076 fnd_message.set_name('PAY', 'HR_376651_FI_LU_PEN_INS_NUM');
1077 hr_utility.raise_error;
1078
1079 WHEN UNIQUE_PG_DTLS_PG
1080 THEN
1081 fnd_message.set_name('PAY', 'HR_376649_FI_LEL_TAEL_GROUP');
1082 hr_utility.raise_error;
1083
1084 WHEN UNIQUE_PENSION_USER_PT
1085 THEN
1086 fnd_message.set_name('PAY', 'HR_376654_FI_LE_TEL_GROUP_DTLS');
1087 hr_utility.raise_error;
1088
1089
1090 END validate_create_org_inf;
1091
1092 --___________________________________________END OF VALIDATE_CREATE_ORG_INF_____________________________________________
1093
1094 PROCEDURE validate_update_org_inf
1095 (p_effective_date IN DATE
1096 ,p_org_info_type_code IN VARCHAR2
1097 ,p_org_information_id IN NUMBER
1098 ,p_org_information1 IN VARCHAR2 DEFAULT null
1099 ,p_org_information2 IN VARCHAR2 DEFAULT null
1100 ,p_org_information3 IN VARCHAR2 DEFAULT null
1101 ,p_org_information4 IN VARCHAR2 DEFAULT null
1102 ,p_org_information5 IN VARCHAR2 DEFAULT null
1103 ,p_org_information6 IN VARCHAR2 DEFAULT null
1104 ,p_org_information7 IN VARCHAR2 DEFAULT null
1105 ,p_org_information8 IN VARCHAR2 DEFAULT null
1106 ,p_org_information9 IN VARCHAR2 DEFAULT null
1107 ) IS
1108
1109 l_length NUMBER ;
1110 l_pipn_length NUMBER ;
1111 l_count NUMBER ;
1112
1113 l_last_two_digits VARCHAR2(2);
1114 l_eleventh_digit VARCHAR2(1) ;
1115 l_calculated_eleventh_digit VARCHAR2(1);
1116 l_warning VARCHAR2(50);
1117 l_return VARCHAR2(50);
1118 l_field VARCHAR2(300) := NULL;
1119
1120
1121 l_business_group_id hr_organization_units.business_group_id%TYPE;
1122 l_organization_id hr_organization_information.organization_id%TYPE;
1123 l_org_information1 hr_organization_information.org_information1%TYPE;
1124 l_pension_type hr_organization_information.org_information1%TYPE;
1125 l_pension_grp hr_organization_information.org_information1%TYPE;
1126
1127 l_session_date DATE ;
1128
1129 INVALID_POLICY_NUMBER_FORMAT EXCEPTION;
1130 INVALID_Y_NUMBER_FORMAT EXCEPTION;
1131 INVALID_PP_CUSTOMER_NUM_FORMAT EXCEPTION;
1132 /* INVALID_PIP_NUMBER_FORMAT EXCEPTION; */
1133 UNIQUE_LOCAL_UNITS EXCEPTION;
1134 UNIQUE_Y_NUMBER EXCEPTION;
1135 UNIQUE_DEPT_CODE EXCEPTION;
1136 UNIQUE_GROUP_CODE EXCEPTION;
1137 UNIQUE_PENSION_TYPE EXCEPTION;
1138 UNIQUE_PENSION_INS_NUM EXCEPTION;
1139 UNIQUE_LU_PENSION_INS_NUM EXCEPTION;
1140 UNIQUE_LE_LC EXCEPTION;
1141 UNIQUE_LE_AP EXCEPTION;
1142 UNIQUE_LU_AP EXCEPTION;
1143 INVALID_DATE EXCEPTION;
1144 INVALID_TYEL_GROUP EXCEPTION;
1145 UNIQUE_PENSION_USER_PT EXCEPTION;
1146 UNIQUE_PG_DTLS_PG EXCEPTION;
1147
1148 CURSOR getbgid
1149 IS
1150 SELECT business_group_id
1151 FROM hr_organization_units
1152 where organization_id = l_organization_id;
1153
1154 CURSOR getorgid
1155 IS
1156 SELECT organization_id
1157 FROM hr_organization_information
1158 WHERE org_information_id = p_org_information_id;
1159
1160 CURSOR orglocalunit
1161 IS
1162 SELECT COUNT(*)
1163 FROM hr_organization_units o , hr_organization_information hoi ,fnd_sessions s
1164 WHERE o.organization_id = hoi.organization_id
1165 AND hoi.org_information_context = 'CLASS'
1166 AND hoi.org_information1 = 'FI_LOCAL_UNIT'
1167 AND o.business_group_id = l_business_group_id
1168 AND TO_CHAR(o.organization_id) IN
1169 (
1170 SELECT hoinf.org_information1
1171 FROM hr_organization_units org, hr_organization_information hoinf ,fnd_sessions s
1172 WHERE org.business_group_id = l_business_group_id
1173 AND org.organization_id = hoinf.organization_id
1174 AND org.organization_id <> l_organization_id
1175 AND hoinf.org_information_context = 'FI_LOCAL_UNITS'
1176 AND s.session_id = userenv('sessionid')
1177 AND s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
1178 )
1179 AND s.session_id = userenv('sessionid')
1180 AND s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
1181 AND o.organization_id = p_org_information1;
1182
1183 CURSOR getynumber
1184 IS
1185 SELECT COUNT(*)
1186 FROM hr_organization_information hoi, hr_organization_units ou
1187 WHERE (hoi.org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS' or
1188 hoi.org_information_context = 'FI_EXTERNAL_COMPANY_DETAILS')
1189 AND ou.organization_id = hoi.organization_id
1190 AND ou.organization_id <> nvl(l_organization_id , 0)
1191 AND ou.business_group_id = l_business_group_id
1192 AND hoi.org_information1 = p_org_information1 ;
1193
1194 CURSOR c_upd_group_code IS
1195 SELECT COUNT(*)
1196 FROM hr_organization_information hoi, hr_organization_units ou
1197 WHERE (hoi.org_information_context = 'FI_PENSION_TYPES')
1198 AND ou.organization_id = hoi.organization_id
1199 AND ou.organization_id = nvl(l_organization_id , 0)
1200 AND ou.business_group_id = l_business_group_id
1201 AND hoi.org_information1 = p_org_information1
1202 AND hoi.org_information2 = p_org_information2
1203 AND hoi.org_information_id <> p_org_information_id ;
1204
1205 CURSOR c_upd_tyel_pt IS
1206 SELECT COUNT(*)
1207 FROM hr_organization_information hoi, hr_organization_units ou
1208 WHERE (hoi.org_information_context = 'FI_PENSION_TYPES')
1209 AND ou.organization_id = hoi.organization_id
1210 AND ou.organization_id = nvl(l_organization_id , 0)
1211 AND ou.business_group_id = l_business_group_id
1212 AND hoi.org_information1 = p_org_information1
1213 AND hoi.org_information_id <> p_org_information_id
1214 AND hoi.org_information2 IS NULL
1215 AND hoi.org_information3 IS NULL;
1216
1217 CURSOR c_upd_dept_code IS
1218 SELECT COUNT(*)
1219 FROM hr_organization_information hoi, hr_organization_units ou
1220 WHERE (hoi.org_information_context = 'FI_PENSION_DEPARTMENT_CODES')
1221 AND ou.organization_id = hoi.organization_id
1222 AND ou.organization_id = nvl(l_organization_id , 0)
1223 AND ou.business_group_id = l_business_group_id
1224 AND hoi.org_information3 = p_org_information3
1225 AND hoi.org_information1 = p_org_information1
1226 AND hoi.org_information_id <> p_org_information_id ;
1227
1228 CURSOR c_upd_le_lc IS
1229 SELECT COUNT(*)
1230 FROM hr_organization_information hoi, hr_organization_units ou
1231 WHERE (hoi.org_information_context = 'FI_PENSION_DEPARTMENT_CODES')
1232 AND ou.organization_id = hoi.organization_id
1233 AND ou.business_group_id = l_business_group_id
1234 AND hoi.org_information1 = p_org_information1
1235 AND hoi.org_information2 = p_org_information2
1236 AND hoi.org_information_id <> p_org_information_id ;
1237
1238 CURSOR c_upd_pp_ins_num IS
1239 SELECT COUNT(*)
1240 FROM hr_organization_information hoi, hr_organization_units ou
1241 WHERE (hoi.org_information_context = 'FI_PENSION_PROVIDERS')
1242 AND ou.business_group_id = l_business_group_id
1243 AND ou.organization_id = hoi.organization_id
1244 AND hoi.org_information6 = p_org_information6
1245 AND hoi.org_information_id <> p_org_information_id ;
1246
1247 CURSOR c_upd_pp_user_pt IS
1248 SELECT COUNT(*)
1249 FROM hr_organization_information hoi, hr_organization_units ou
1250 WHERE (hoi.org_information_context = 'FI_PENSION_PROVIDERS')
1251 AND ou.business_group_id = l_business_group_id
1252 AND ou.organization_id = hoi.organization_id
1253 AND ou.organization_id = nvl(l_organization_id , 0)
1254 AND trim(hoi.org_information9) = trim(p_org_information9)
1255 AND hoi.org_information_id <> p_org_information_id ;
1256
1257
1258 CURSOR c_upd_lu_pp_ins_num IS
1259 SELECT COUNT(*)
1260 FROM hr_organization_information hoi, hr_organization_units ou
1261 WHERE (hoi.org_information_context = 'FI_LU_PENSION_PROVIDERS')
1262 AND ou.organization_id = hoi.organization_id
1263 AND ou.organization_id = nvl(l_organization_id , 0)
1264 AND ou.business_group_id = l_business_group_id
1265 AND hoi.org_information1 = p_org_information1
1266 AND hoi.org_information_id <> p_org_information_id ;
1267
1268
1269 CURSOR c_upd_le_ap_dtls IS
1270 SELECT COUNT(*)
1271 FROM hr_organization_information hoi, hr_organization_units ou
1272 WHERE (hoi.org_information_context = 'FI_ACCIDENT_PROVIDERS')
1273 AND ou.organization_id = hoi.organization_id
1274 AND ou.organization_id = nvl(l_organization_id , 0)
1275 AND ou.business_group_id = l_business_group_id
1276 AND (( fnd_date.canonical_to_date(p_org_information1) between fnd_date.canonical_to_date(hoi.org_information1) AND
1277 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
1278 OR ( nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hoi.org_information1) AND
1279 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
1280 OR ( fnd_date.canonical_to_date(hoi.org_information1) between fnd_date.canonical_to_date(p_org_information1) AND
1281 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')))
1282 OR ( nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(p_org_information1) AND
1283 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY'))))
1284 AND hoi.org_information_id <> p_org_information_id ;
1285
1286 CURSOR c_upd_lu_ap_dtls IS
1287 SELECT COUNT(*)
1288 FROM hr_organization_information hoi, hr_organization_units ou
1289 WHERE (hoi.org_information_context = 'FI_LU_ACCIDENT_PROVIDERS')
1290 AND ou.organization_id = hoi.organization_id
1291 AND ou.organization_id = nvl(l_organization_id , 0)
1292 AND ou.business_group_id = l_business_group_id
1293 AND (( fnd_date.canonical_to_date(p_org_information1) between fnd_date.canonical_to_date(hoi.org_information1) AND
1294 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
1295 OR ( nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hoi.org_information1) AND
1296 nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')))
1297 OR ( fnd_date.canonical_to_date(hoi.org_information1) between fnd_date.canonical_to_date(p_org_information1) AND
1298 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY')))
1299 OR ( nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(p_org_information1) AND
1300 nvl(fnd_date.canonical_to_date(p_org_information2),to_date('31/12/4712','DD/MM/YYYY'))))
1301 AND hoi.org_information_id <> p_org_information_id ;
1302
1303
1304
1305
1306 CURSOR c_upd_pg_dtls_pg IS
1307 SELECT COUNT(*)
1308 FROM hr_organization_information hoi, hr_organization_units ou
1309 WHERE (hoi.org_information_context = 'FI_PENSION_GROUP_DETAILS')
1310 AND ou.organization_id = hoi.organization_id
1311 AND ou.organization_id = nvl(l_organization_id , 0)
1312 AND ou.business_group_id = l_business_group_id
1313 AND hoi.org_information1 = p_org_information1
1314 AND hoi.org_information2 = p_org_information2
1315 AND hoi.org_information_id <> p_org_information_id ;
1316
1317 BEGIN
1318 --
1319 -- Added for GSI Bug 5472781
1320 --
1321 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
1322 --
1323 OPEN getorgid;
1324 FETCH getorgid INTO l_organization_id;
1325 CLOSE getorgid;
1326
1327 OPEN getbgid;
1328 FETCH getbgid INTO l_business_group_id;
1329 CLOSE getbgid;
1330
1331 IF p_org_info_type_code = 'FI_LOCAL_UNITS'
1332 THEN
1333 OPEN orglocalunit;
1334 FETCH orglocalunit INTO l_count;
1335 CLOSE orglocalunit;
1336 IF l_count > 0
1337 THEN
1338 fnd_message.set_name('PER', 'Update');
1339 hr_utility.raise_error;
1340
1341 RAISE UNIQUE_LOCAL_UNITS ;
1342 END IF ;
1343
1344 END IF;
1345 --------------------------- Y-Number Unique Bug ----------------------------
1346
1347 IF p_org_info_type_code = 'FI_EXTERNAL_COMPANY_DETAILS'
1348 THEN
1349 -- For Y-Number --
1350 IF p_org_information1 IS NOT NULL
1351 THEN
1352 OPEN getynumber;
1353 FETCH getynumber INTO l_count;
1354 CLOSE getynumber;
1355 IF l_count > 0
1356 THEN
1357 RAISE UNIQUE_Y_NUMBER ;
1358 END IF ;
1359
1360 IF ( (LENGTH(p_org_information1) =9) AND (SUBSTR(p_org_information1,8,1)='-') )
1361 THEN -- length
1362 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDDDDD-X')='0'
1363 THEN
1364 RAISE INVALID_Y_NUMBER_FORMAT;
1365 END IF ;
1366 ELSE
1367 RAISE INVALID_Y_NUMBER_FORMAT;
1368 END IF ; -- end of length
1369 END IF;
1370 -- End Of For Y-Number --
1371
1372 END IF;
1373 --------------------------- Y-Number Unique Bug ----------------------------
1374
1375 IF p_org_info_type_code = 'FI_LOCAL_UNIT_DETAILS'
1376 THEN -- LOCAL_UNIT_DETAILS
1377
1378 validate_number(p_org_information1,hr_general.decode_lookup('FI_FORM_LABELS','SUB_DISBURSEMENT'));
1379 validate_number(p_org_information2,hr_general.decode_lookup('FI_FORM_LABELS','LOCAL_UNIT_NUMBER'));
1380 validate_number(p_org_information4,hr_general.decode_lookup('FI_FORM_LABELS','EMP_ORG_MEMBERSHIP'));
1381
1382
1383 END IF ; -- end of LOCAL_UNIT_DETAILS
1384
1385 IF p_org_info_type_code = 'FI_LEGAL_EMPLOYER_DETAILS'
1386 THEN -- LEGAL_EMPLOYER_DETAILS
1387
1388 -- For Y-Number --
1389 IF p_org_information1 IS NOT NULL
1390 THEN
1391 OPEN getynumber;
1392 FETCH getynumber INTO l_count;
1393 CLOSE getynumber;
1394 IF l_count > 0
1395 THEN
1396 RAISE UNIQUE_Y_NUMBER ;
1397 END IF ;
1398
1399 IF ( (LENGTH(p_org_information1) =9) AND (SUBSTR(p_org_information1,8,1)='-') )
1400 THEN -- length
1401 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDDDDD-X')='0'
1402 THEN
1403 RAISE INVALID_Y_NUMBER_FORMAT;
1404 END IF ;
1405 ELSE
1406 RAISE INVALID_Y_NUMBER_FORMAT;
1407 END IF ; -- end of length
1408 END IF;
1409 -- End Of For Y-Number --
1410 --
1411 -- 3900118 To validate whole number
1412 validate_number(p_org_information6,hr_general.decode_lookup('FI_FORM_LABELS','EMP_ORG_MEMBERSHIP'));
1413 -- 3900118 To validate whole number
1414
1415 END IF ; -- end of LEGAL_EMPLOYER_DETAILS
1416
1417 IF p_org_info_type_code = 'FI_EXTERNAL_COMPANY_DETAILS'
1418 THEN -- FI_EXTERNAL_COMPANY_DETAILS
1419
1420 -- For Y-Number --
1421 IF p_org_information1 IS NOT NULL
1422 THEN
1423 IF ( (LENGTH(p_org_information1) =9) AND (SUBSTR(p_org_information1,8,1)='-') )
1424 THEN -- length
1425 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDDDDD-X')='0'
1426 THEN
1427 RAISE INVALID_Y_NUMBER_FORMAT;
1428 END IF ;
1429 ELSE
1430 RAISE INVALID_Y_NUMBER_FORMAT;
1431 END IF ;
1432 END IF;
1433 -- End Of For Y-Number --
1434
1435 -- For PIN --
1436
1437 IF p_org_information2 IS NOT NULL
1438 THEN
1439 BEGIN
1440 SELECT EFFECTIVE_DATE INTO l_session_date FROM fnd_sessions WHERE SESSION_ID = userenv('SESSIONID');
1441 l_return :=hr_ni_chk_pkg.validate_national_identifier(p_org_information2,null,null,'WHEN-VALIDATE-RECORD',null,l_business_group_id,'FI',l_session_date,l_warning,null,null,null);
1442 EXCEPTION
1443 WHEN OTHERS
1444 THEN
1445 fnd_message.set_name('PAY', 'HR_FI_INVALID_NATIONAL_ID');
1446 hr_utility.raise_error;
1447 END ;
1448 END IF;
1449 -- End Of PIN --
1450 END IF; -- end of FI_EXTERNAL_COMPANY_DETAILS
1451
1452 IF p_org_info_type_code = 'FI_PENSION_TYPES'
1453 THEN -- FI_PENSION_TYPES
1454
1455
1456 IF p_org_information1='TYEL' THEN
1457
1458 IF (( p_org_information2 IS NULL AND p_org_information3 IS NOT NULL )
1459 OR
1460 ( p_org_information2 IS NOT NULL AND p_org_information3 IS NULL ))
1461 THEN
1462
1463 RAISE INVALID_TYEL_GROUP ;
1464 -- Group Code
1465 validate_number(p_org_information2,hr_general.decode_lookup('FI_FORM_LABELS','GROUP_CODE'),'HR_376633_FI_WHOLE_NUMBER');
1466
1467
1468 END IF;
1469
1470 IF ( p_org_information2 IS NULL AND p_org_information3 IS NULL )
1471 THEN
1472
1473 OPEN c_upd_tyel_pt;
1474 FETCH c_upd_tyel_pt INTO l_count;
1475 CLOSE c_upd_tyel_pt;
1476 IF l_count > 0 THEN
1477 RAISE UNIQUE_PENSION_TYPE ;
1478 END IF ;
1479
1480
1481 END IF;
1482
1483 IF ( p_org_information2 IS NOT NULL )
1484 THEN
1485
1486 OPEN c_upd_group_code;
1487 FETCH c_upd_group_code INTO l_count;
1488 CLOSE c_upd_group_code;
1489 IF l_count > 0 THEN
1490 RAISE UNIQUE_GROUP_CODE ;
1491 END IF ;
1492
1493 END IF;
1494
1495
1496 END IF;
1497
1498 END IF;
1499
1500 IF p_org_info_type_code = 'FI_PENSION_DEPARTMENT_CODES'
1501 THEN -- FI_PENSION_DEPARTMENT_CODES
1502 IF p_org_information3 IS NOT NULL THEN
1503 -- Department Code
1504 validate_number(p_org_information3,hr_general.decode_lookup('FI_FORM_LABELS','DEPARTMENT_CODE'),'HR_376633_FI_WHOLE_NUMBER');
1505
1506 OPEN c_upd_dept_code;
1507 FETCH c_upd_dept_code INTO l_count;
1508 CLOSE c_upd_dept_code;
1509 IF l_count > 0 THEN
1510 RAISE UNIQUE_DEPT_CODE ;
1511 END IF ;
1512
1513 OPEN c_upd_le_lc;
1514 FETCH c_upd_le_lc INTO l_count;
1515 CLOSE c_upd_le_lc;
1516 IF l_count > 0 THEN
1517 RAISE UNIQUE_LE_LC ;
1518 END IF ;
1519
1520
1521 END IF;
1522 END IF;
1523
1524
1525 IF p_org_info_type_code = 'FI_PENSION_GROUP_DETAILS' THEN
1526
1527 OPEN c_upd_pg_dtls_pg;
1528 FETCH c_upd_pg_dtls_pg INTO l_count;
1529 CLOSE c_upd_pg_dtls_pg;
1530 IF l_count > 0 THEN
1531 RAISE UNIQUE_PG_DTLS_PG ;
1532 END IF ;
1533
1534 END IF;
1535
1536
1537 IF p_org_info_type_code = 'FI_PENSION_PROVIDERS' THEN
1538
1539
1540 IF p_org_information2 IS NOT NULL THEN
1541
1542 IF fnd_date.canonical_to_date(p_org_information2) < fnd_date.canonical_to_date(p_org_information1) THEN
1543 RAISE INVALID_DATE ;
1544 END IF;
1545
1546 END IF;
1547
1548
1549
1550 -- For Pension Provider Customer number --
1551 IF p_org_information8 IS NOT NULL
1552 THEN
1553 IF ( (LENGTH(p_org_information8) =11) AND (SUBSTR(p_org_information8,9,1)='-') )
1554 THEN -- length
1555 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information8,'DDDDDDDD-DD')='0'
1556 THEN
1557 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
1558 ELSE
1559 l_last_two_digits := substr(p_org_information8,10,2);
1560 IF l_last_two_digits <> '00'
1561 THEN
1562 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
1563 END IF;
1564 END IF ;
1565 ELSE
1566 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
1567 END IF ; -- end of length
1568 END IF;
1569 -- End Of For Pension Provider Customer number --
1570
1571
1572
1573 OPEN c_upd_pp_ins_num;
1574 FETCH c_upd_pp_ins_num INTO l_count;
1575 CLOSE c_upd_pp_ins_num;
1576 IF l_count > 0 THEN
1577 RAISE UNIQUE_PENSION_INS_NUM ;
1578 END IF ;
1579
1580 OPEN c_upd_pp_user_pt;
1581 FETCH c_upd_pp_user_pt INTO l_count;
1582 CLOSE c_upd_pp_user_pt;
1583 IF l_count > 0 THEN
1584 RAISE UNIQUE_PENSION_USER_PT ;
1585 END IF ;
1586
1587
1588
1589 -- Pension Insurance Policy Number --
1590 /* Bug fix 7600677
1591 IF p_org_information6 IS NOT NULL
1592 THEN
1593 l_pipn_length := LENGTH (p_org_information6);
1594 IF SUBSTR(p_org_information6,3,1) = '-'
1595 THEN -- FIRST FORMAT NN-NNNNNNNT
1596 IF( (l_pipn_length >=8) AND (l_pipn_length <=11) )
1597 THEN -- Length between 8 to 11
1598 IF l_pipn_length = 8
1599 THEN -- 46-1234M
1600 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDX')='0'
1601 THEN
1602 RAISE INVALID_PIP_NUMBER_FORMAT;
1603 END IF;
1604 ELSIF l_pipn_length = 9
1605 THEN -- 46-12345M
1606 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDDX')='0'
1607 THEN
1608 RAISE INVALID_PIP_NUMBER_FORMAT;
1609 END IF;
1610 ELSIF l_pipn_length = 10
1611 THEN -- 46-123456M
1612 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDDDX')='0'
1613 THEN
1614 RAISE INVALID_PIP_NUMBER_FORMAT;
1615 END IF;
1616 ELSIF l_pipn_length = 11
1617 THEN -- 46-1234567M
1618 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DD-DDDDDDDX')='0'
1619 THEN
1620 RAISE INVALID_PIP_NUMBER_FORMAT;
1621 END IF;
1622 END IF;
1623 ELSE -- length less than 8 or greater than 11 wrong format
1624 RAISE INVALID_PIP_NUMBER_FORMAT;
1625 END IF; -- end of -- Length between 8 to 11
1626 ELSE
1627 IF SUBSTR(p_org_information6,5,1) = '-'
1628 THEN -- SECOND FORMAT NNNN-NNNNT
1629 IF l_pipn_length = 10
1630 THEN -- 1234-5678M
1631 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information6,'DDDD-DDDDX')='0'
1632 THEN
1633 RAISE INVALID_PIP_NUMBER_FORMAT;
1634 END IF;
1635 ELSE
1636 RAISE INVALID_PIP_NUMBER_FORMAT;
1637 END IF;
1638 ELSE -- Wrong format so raise error
1639 RAISE INVALID_PIP_NUMBER_FORMAT;
1640 END IF ;
1641 END IF; -- end of format check
1642 END IF ; */
1643 -- End Of Pension Insurance Policy Number
1644
1645 END IF;
1646
1647 IF p_org_info_type_code = 'FI_LU_PENSION_PROVIDERS' THEN
1648
1649 OPEN c_upd_lu_pp_ins_num;
1650 FETCH c_upd_lu_pp_ins_num INTO l_count;
1651 CLOSE c_upd_lu_pp_ins_num;
1652
1653 IF l_count > 0 THEN
1654 RAISE UNIQUE_LU_PENSION_INS_NUM ;
1655 END IF ;
1656
1657 -- Pension Insurance Policy Number --
1658 /* Bug fix - 7600677
1659 IF p_org_information1 IS NOT NULL
1660 THEN
1661 l_pipn_length := LENGTH (p_org_information1);
1662 IF SUBSTR(p_org_information1,3,1) = '-'
1663 THEN -- FIRST FORMAT NN-NNNNNNNT
1664 IF( (l_pipn_length >=8) AND (l_pipn_length <=11) )
1665 THEN -- Length between 8 to 11
1666 IF l_pipn_length = 8
1667 THEN -- 46-1234M
1668 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDX')='0'
1669 THEN
1670 RAISE INVALID_PIP_NUMBER_FORMAT;
1671 END IF;
1672 ELSIF l_pipn_length = 9
1673 THEN -- 46-12345M
1674 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDDX')='0'
1675 THEN
1676 RAISE INVALID_PIP_NUMBER_FORMAT;
1677 END IF;
1678 ELSIF l_pipn_length = 10
1679 THEN -- 46-123456M
1680 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDDDX')='0'
1681 THEN
1682 RAISE INVALID_PIP_NUMBER_FORMAT;
1683 END IF;
1684 ELSIF l_pipn_length = 11
1685 THEN -- 46-1234567M
1686 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DD-DDDDDDDX')='0'
1687 THEN
1688 RAISE INVALID_PIP_NUMBER_FORMAT;
1689 END IF;
1690 END IF;
1691 ELSE -- length less than 8 or greater than 11 wrong format
1692 RAISE INVALID_PIP_NUMBER_FORMAT;
1693 END IF; -- end of -- Length between 8 to 11
1694 ELSE
1695 IF SUBSTR(p_org_information1,5,1) = '-'
1696 THEN -- SECOND FORMAT NNNN-NNNNT
1697 IF l_pipn_length = 10
1698 THEN -- 1234-5678M
1699 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information1,'DDDD-DDDDX')='0'
1700 THEN
1701 RAISE INVALID_PIP_NUMBER_FORMAT;
1702 END IF;
1703 ELSE
1704 RAISE INVALID_PIP_NUMBER_FORMAT;
1705 END IF;
1706 ELSE -- Wrong format so raise error
1707 RAISE INVALID_PIP_NUMBER_FORMAT;
1708 END IF ;
1709 END IF; -- end of format check
1710 END IF ; */
1711 -- End Of Pension Insurance Policy Number
1712
1713 -- For Pension Provider Customer number --
1714 IF p_org_information2 IS NOT NULL
1715 THEN
1716 IF ( (LENGTH(p_org_information2) =11) AND (SUBSTR(p_org_information2,9,1)='-') )
1717 THEN -- length
1718 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information2,'DDDDDDDD-DD')='0'
1719 THEN
1720 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
1721 ELSE
1722 l_last_two_digits := substr(p_org_information2,10,2);
1723 IF l_last_two_digits <> '00'
1724 THEN
1725 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
1726 END IF;
1727 END IF ;
1728 ELSE
1729 RAISE INVALID_PP_CUSTOMER_NUM_FORMAT;
1730 END IF ; -- end of length
1731 END IF;
1732 -- End Of For Pension Provider Customer number --
1733
1734 END IF;
1735
1736
1737 IF p_org_info_type_code = 'FI_ACCIDENT_PROVIDERS' THEN
1738
1739
1740 IF p_org_information2 IS NOT NULL THEN
1741
1742 IF fnd_date.canonical_to_date(p_org_information2) < fnd_date.canonical_to_date(p_org_information1) THEN
1743 RAISE INVALID_DATE ;
1744 END IF;
1745
1746 END IF;
1747
1748
1749 OPEN c_upd_le_ap_dtls ;
1750 FETCH c_upd_le_ap_dtls INTO l_count;
1751 CLOSE c_upd_le_ap_dtls ;
1752 IF l_count > 0 THEN
1753 RAISE UNIQUE_LE_AP ;
1754 END IF ;
1755
1756 /* Commenting the validation for Accident Insurance Policy Number for Enhancement 2008-2009 */
1757 /* Please refer Bug 8425533 for further details */
1758
1759 /* -- For Accident Insurance Policy Number --
1760 IF p_org_information4 IS NOT NULL
1761 THEN
1762 IF ( (LENGTH(p_org_information4) =13) AND (SUBSTR(p_org_information4,4,1)='-') AND (SUBSTR(p_org_information4,12,1)='-') )
1763 THEN -- length
1764 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information4,'DDD-DDDDDDD-X')='0'
1765 THEN
1766 RAISE INVALID_POLICY_NUMBER_FORMAT;
1767 END IF;
1768 ELSE
1769 RAISE INVALID_POLICY_NUMBER_FORMAT;
1770 END IF ;
1771 END IF;
1772 -- End Of Accident Insurance Policy Number --
1773 */ -- End
1774
1775 END IF;
1776
1777
1778 IF p_org_info_type_code = 'FI_LU_ACCIDENT_PROVIDERS' THEN
1779
1780
1781 IF p_org_information2 IS NOT NULL THEN
1782
1783 IF fnd_date.canonical_to_date(p_org_information2) < fnd_date.canonical_to_date(p_org_information1) THEN
1784 RAISE INVALID_DATE ;
1785 END IF;
1786
1787 END IF;
1788
1789
1790 OPEN c_upd_lu_ap_dtls ;
1791 FETCH c_upd_lu_ap_dtls INTO l_count;
1792 CLOSE c_upd_lu_ap_dtls ;
1793 IF l_count > 0 THEN
1794 RAISE UNIQUE_LU_AP ;
1795 END IF ;
1796 /* Commenting the validation for Accident Insurance Policy Number for Enhancement 2008-2009 */
1797 /* Please refer Bug 8425533 for further details */
1798 /* -- For Accident Insurance Policy Number --
1799 IF p_org_information3 IS NOT NULL
1800 THEN
1801 IF ( (LENGTH(p_org_information3) =13) AND (SUBSTR(p_org_information3,4,1)='-') AND (SUBSTR(p_org_information3,12,1)='-') )
1802 THEN -- length
1803 IF HR_NI_CHK_PKG.CHK_NAT_ID_FORMAT(p_org_information3,'DDD-DDDDDDD-X')='0'
1804 THEN
1805 RAISE INVALID_POLICY_NUMBER_FORMAT;
1806 END IF;
1807 ELSE
1808 RAISE INVALID_POLICY_NUMBER_FORMAT;
1809 END IF ;
1810 END IF;
1811 -- End Of Accident Insurance Policy Number --
1812 */ -- End
1813
1814 END IF;
1815
1816 END IF;
1817
1818 EXCEPTION
1819 WHEN INVALID_POLICY_NUMBER_FORMAT
1820 THEN
1821
1822 l_field := hr_general.decode_lookup('FI_FORM_LABELS','ACC_INS_POLICY');
1823 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
1824 fnd_message.set_token('NAME',l_field, translate => true );
1825 hr_utility.raise_error;
1826
1827 WHEN INVALID_Y_NUMBER_FORMAT
1828 THEN
1829
1830 l_field := hr_general.decode_lookup('FI_FORM_LABELS','Y_NUMBER');
1831 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
1832 fnd_message.set_token('NAME',l_field, translate => true );
1833 hr_utility.raise_error;
1834
1835 WHEN INVALID_PP_CUSTOMER_NUM_FORMAT
1836 THEN
1837 l_field := hr_general.decode_lookup('FI_FORM_LABELS','PEN_PROV_CUST');
1838 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
1839 fnd_message.set_token('NAME',l_field, translate => true );
1840 hr_utility.raise_error;
1841
1842 /* WHEN INVALID_PIP_NUMBER_FORMAT
1843 THEN
1844 l_field := hr_general.decode_lookup('FI_FORM_LABELS','PEN_INS_POLICY');
1845 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
1846 fnd_message.set_token('NAME',l_field, translate => true );
1847 hr_utility.raise_error; */
1848
1849 WHEN UNIQUE_LOCAL_UNITS
1850 THEN
1851 fnd_message.set_name('PER', 'HR_376614_FI_UNIQUE_LOCAL_UNIT');
1852 hr_utility.raise_error;
1853
1854 WHEN UNIQUE_Y_NUMBER
1855 THEN
1856 l_field := hr_general.decode_lookup('FI_FORM_LABELS','Y_NUMBER');
1857 fnd_message.set_name('PER', 'HR_376613_FI_UNIQUE_MSG');
1858 fnd_message.set_token('NAME',l_field, translate => true );
1859 hr_utility.raise_error;
1860 WHEN UNIQUE_DEPT_CODE
1861 THEN
1862
1863 fnd_message.set_name('PAY', 'HR_376630_FI_UNIQUE_DEPT_CODE');
1864 hr_utility.raise_error;
1865
1866 WHEN UNIQUE_GROUP_CODE
1867 THEN
1868
1869 fnd_message.set_name('PAY', 'HR_376632_FI_UNIQUE_GROUP_CODE');
1870 hr_utility.raise_error;
1871
1872 WHEN UNIQUE_PENSION_TYPE
1873 THEN
1874
1875 fnd_message.set_name('PAY', 'HR_376647_FI_UNIQ_PENSION_TYPE');
1876 hr_utility.raise_error;
1877
1878
1879 WHEN UNIQUE_LE_LC
1880 THEN
1881
1882 fnd_message.set_name('PAY', 'HR_376631_FI_UNIQUE_LE_LC');
1883 hr_utility.raise_error;
1884
1885 WHEN UNIQUE_LE_AP
1886 THEN
1887
1888 fnd_message.set_name('PAY', 'HR_376638_FI_UNIQUE_ACC_PROV');
1889 hr_utility.raise_error;
1890
1891 WHEN UNIQUE_LU_AP
1892 THEN
1893
1894 fnd_message.set_name('PAY', 'HR_376645_FI_LU_UNIQ_ACC_PROV');
1895 hr_utility.raise_error;
1896
1897 WHEN INVALID_DATE
1898 THEN
1899
1900 fnd_message.set_name('PAY', 'HR_376639_FI_VALID_DATE');
1901 hr_utility.raise_error;
1902
1903 WHEN INVALID_TYEL_GROUP
1904 THEN
1905
1906 fnd_message.set_name('PAY', 'HR_376648_FI_TEL_GROUP_DTLS');
1907 hr_utility.raise_error;
1908
1909
1910 WHEN UNIQUE_PENSION_INS_NUM
1911 THEN
1912 fnd_message.set_name('PAY', 'HR_376650_FI_UNIQ_PEN_INS_NUM');
1913 hr_utility.raise_error;
1914
1915 WHEN UNIQUE_LU_PENSION_INS_NUM
1916 THEN
1917 fnd_message.set_name('PAY', 'HR_376651_FI_LU_PEN_INS_NUM');
1918 hr_utility.raise_error;
1919
1920
1921 WHEN UNIQUE_PG_DTLS_PG
1922 THEN
1923 fnd_message.set_name('PAY', 'HR_376649_FI_LEL_TAEL_GROUP');
1924 hr_utility.raise_error;
1925
1926 WHEN UNIQUE_PENSION_USER_PT
1927 THEN
1928 fnd_message.set_name('PAY', 'HR_376654_FI_LE_TEL_GROUP_DTLS');
1929 hr_utility.raise_error;
1930
1931 END validate_update_org_inf;
1932
1933 --- End Of validate_update_org_inf
1934
1935
1936 PROCEDURE CREATE_ASG_VALIDATE
1937 (
1938 p_scl_segment12 IN VARCHAR2 DEFAULT NULL
1939 ,p_effective_date IN DATE
1940 ,p_person_id IN NUMBER
1941 ,p_organization_id IN NUMBER )
1942 IS
1943
1944 l_yes_or_no VARCHAR2(10);
1945 l_count NUMBER ;
1946
1947 UNIQUE_RPT_ASG EXCEPTION ;
1948 l_business_group_id hr_organization_units.business_group_id%TYPE;
1949 l_le hr_organization_units.organization_id%type;
1950
1951 CURSOR getbgid
1952 IS
1953 SELECT business_group_id
1954 FROM hr_organization_units
1955 WHERE organization_id = p_organization_id;
1956
1957 CURSOR get_rpt_asg ( p_le NUMBER )
1958 IS
1959 SELECT hsc.segment12
1960 FROM HR_ORGANIZATION_UNITS o1
1961 ,HR_ORGANIZATION_INFORMATION hoi1
1962 ,HR_ORGANIZATION_INFORMATION hoi2
1963 ,PER_ALL_ASSIGNMENTS_F paa
1964 ,HR_SOFT_CODING_KEYFLEX hsc
1965 WHERE o1.business_group_id =l_business_group_id
1966 and o1.organization_id = hoi1.organization_id
1967 and hoi1.org_information_context = 'CLASS'
1968 and hoi1.org_information1 = 'FI_LOCAL_UNIT'
1969 AND nvl(hoi2.organization_id,-999) = p_le
1970 and hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
1971 and o1.organization_id = hoi2.org_information1
1972 and paa.person_id =p_person_id
1973 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
1974 and paa.SOFT_CODING_KEYFLEX_ID=hsc.SOFT_CODING_KEYFLEX_ID
1975 and o1.organization_id = hsc.segment2;
1976
1977
1978 CURSOR csr_le IS
1979 SELECT hoi3.organization_id
1980 FROM HR_ORGANIZATION_UNITS o1
1981 , HR_ORGANIZATION_INFORMATION hoi1
1982 , HR_ORGANIZATION_INFORMATION hoi2
1983 , HR_ORGANIZATION_INFORMATION hoi3
1984 WHERE o1.business_group_id =l_business_group_id
1985 AND hoi1.organization_id = o1.organization_id
1986 AND hoi1.organization_id = p_organization_id
1987 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
1988 AND hoi1.org_information_context = 'CLASS'
1989 AND o1.organization_id = hoi2.org_information1
1990 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
1991 AND hoi2.organization_id = hoi3.organization_id
1992 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1993 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
1994
1995
1996
1997
1998 BEGIN
1999 --
2000 -- Added for GSI Bug 5472781
2001 --
2002 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
2003 --
2004 OPEN getbgid;
2005 FETCH getbgid INTO l_business_group_id;
2006 CLOSE getbgid;
2007
2008 IF upper(p_scl_segment12) = 'Y'
2009 THEN
2010
2011 OPEN csr_le;
2012 FETCH csr_le INTO l_le;
2013 CLOSE csr_le ;
2014
2015
2016 OPEN get_rpt_asg(l_le);
2017 LOOP
2018 FETCH get_rpt_asg INTO l_yes_or_no;
2019 EXIT WHEN get_rpt_asg%NOTFOUND;
2020 IF l_yes_or_no = upper(p_scl_segment12)
2021 THEN
2022 RAISE UNIQUE_RPT_ASG ;
2023 END IF ;
2024 END LOOP ;
2025 CLOSE get_rpt_asg;
2026 END IF ;
2027 END IF;
2028
2029 EXCEPTION
2030 WHEN UNIQUE_RPT_ASG
2031 THEN
2032 fnd_message.set_name('PER', 'HR_376610_FI_UNIQUE_RPT_ASG');
2033 hr_utility.raise_error;
2034
2035 END CREATE_ASG_VALIDATE;
2036
2037 -- End OF Create_Asg_validate
2038
2039 PROCEDURE UPDATE_ASG_VALIDATE
2040 (
2041 p_segment2 IN VARCHAR2
2042 ,p_segment12 IN VARCHAR2
2043 ,p_effective_date IN DATE
2044 ,p_assignment_id IN NUMBER)
2045 IS
2046
2047 l_person_id NUMBER;
2048 l_count NUMBER;
2049 l_yes_or_no VARCHAR2(10);
2050 UNIQUE_RPT_ASG EXCEPTION ;
2051 l_business_group_id hr_organization_units.business_group_id%TYPE;
2052 l_le hr_organization_units.organization_id%type;
2053
2054 CURSOR getbgid
2055 IS
2056 SELECT business_group_id
2057 FROM per_all_assignments_f
2058 where assignment_id = p_assignment_id;
2059
2060
2061 CURSOR get_rpt_asg ( p_le NUMBER )
2062 IS
2063 SELECT hsc.segment12
2064 FROM HR_ORGANIZATION_UNITS o1
2065 ,HR_ORGANIZATION_INFORMATION hoi1
2066 ,HR_ORGANIZATION_INFORMATION hoi2
2067 ,PER_ALL_ASSIGNMENTS_F paa
2068 ,HR_SOFT_CODING_KEYFLEX hsc
2069 WHERE o1.business_group_id =l_business_group_id
2070 and o1.organization_id = hoi1.organization_id
2071 and hoi1.org_information_context = 'CLASS'
2072 and hoi1.org_information1 = 'FI_LOCAL_UNIT'
2073 AND nvl(hoi2.organization_id,-999) = p_le
2074 and hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
2075 and o1.organization_id = hoi2.org_information1
2076 and paa.person_id =l_person_id
2077 and paa.assignment_id <> p_assignment_id
2078 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
2079 and paa.SOFT_CODING_KEYFLEX_ID=hsc.SOFT_CODING_KEYFLEX_ID
2080 and o1.organization_id = hsc.segment2;
2081
2082
2083 CURSOR csr_le IS
2084 SELECT hoi3.organization_id
2085 FROM HR_ORGANIZATION_UNITS o1
2086 , HR_ORGANIZATION_INFORMATION hoi1
2087 , HR_ORGANIZATION_INFORMATION hoi2
2088 , HR_ORGANIZATION_INFORMATION hoi3
2089 WHERE o1.business_group_id =l_business_group_id
2090 AND hoi1.organization_id = o1.organization_id
2091 AND hoi1.organization_id = p_segment2
2092 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
2093 AND hoi1.org_information_context = 'CLASS'
2094 AND o1.organization_id = hoi2.org_information1
2095 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
2096 AND hoi2.organization_id = hoi3.organization_id
2097 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
2098 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
2099
2100
2101
2102 CURSOR c_person
2103 IS
2104 SELECT person_id
2105 FROM per_all_assignments_f a
2106 WHERE a.assignment_id = p_assignment_id
2107 AND p_effective_date BETWEEN a.effective_start_date AND a.effective_end_date;
2108
2109 BEGIN
2110 --
2111 -- Added for GSI Bug 5472781
2112 --
2113 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
2114 --
2115 OPEN c_person;
2116 LOOP
2117 FETCH c_person INTO l_person_id;
2118 EXIT ;
2119 END LOOP ;
2120 CLOSE c_person;
2121
2122 OPEN getbgid;
2123 FETCH getbgid INTO l_business_group_id;
2124 CLOSE getbgid;
2125
2126 IF upper(p_segment12) = 'Y'
2127 THEN
2128
2129 OPEN csr_le;
2130 FETCH csr_le INTO l_le;
2131 CLOSE csr_le ;
2132
2133 OPEN get_rpt_asg(l_le);
2134 LOOP
2135 FETCH get_rpt_asg INTO l_yes_or_no;
2136 EXIT WHEN get_rpt_asg%NOTFOUND;
2137 IF l_yes_or_no = upper(p_segment12)
2138 THEN
2139 RAISE UNIQUE_RPT_ASG ;
2140 END IF ;
2141 END LOOP ;
2142 CLOSE get_rpt_asg;
2143
2144 END IF ;
2145 END IF;
2146 EXCEPTION
2147 WHEN UNIQUE_RPT_ASG
2148 THEN
2149 fnd_message.set_name('PER', 'HR_376610_FI_UNIQUE_RPT_ASG');
2150 hr_utility.raise_error;
2151
2152 END UPDATE_ASG_VALIDATE ;
2153
2154 -- End Of UPDATE_ASG_VALIDATE
2155
2156 --Procedure for validating Termination.
2157 PROCEDURE UPDATE_TERMINATION_VALIDATE
2158 (p_leaving_reason IN VARCHAR2
2159 )IS
2160 BEGIN
2161 --
2162 -- Added for GSI Bug 5472781
2163 --
2164 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
2165 --
2166 IF p_leaving_reason IS NULL THEN
2167 fnd_message.set_name('PER', 'HR_376603_FI_MANDATORY_MSG');
2168 fnd_message.set_token('NAME',hr_general.decode_lookup('FI_FORM_LABELS','L_REASON'), translate => true );
2169 hr_utility.raise_error;
2170 END IF;
2171 END IF;
2172 END UPDATE_TERMINATION_VALIDATE ;
2173
2174
2175
2176 ----
2177 -- Validation While creating the Classifications for Organization
2178
2179 PROCEDURE CREATE_ORG_CLASS_VALIDATE
2180 (P_ORGANIZATION_ID IN NUMBER
2181 ,P_ORG_INFORMATION1 IN VARCHAR2
2182 )IS
2183
2184 l_internal_external_flag hr_organization_units.INTERNAL_EXTERNAL_FLAG%type;
2185 EXT_COMP_EXCEPTION exception;
2186 PEN_PROV_EXCEPTION exception;
2187 ACC_PROV_EXCEPTION exception;
2188
2189 PROV_TAX_EXCEPTION exception;
2190
2191
2192
2193 CURSOR get_int_or_ext_flag
2194 IS
2195 SELECT INTERNAL_EXTERNAL_FLAG
2196 FROM HR_ORGANIZATION_UNITS
2197 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
2198
2199 BEGIN
2200 --
2201 -- Added for GSI Bug 5472781
2202 --
2203 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
2204 --
2205 OPEN get_int_or_ext_flag;
2206 FETCH get_int_or_ext_flag INTO l_internal_external_flag;
2207 CLOSE get_int_or_ext_flag;
2208
2209 IF l_internal_external_flag = 'INT'
2210 THEN
2211 IF P_ORG_INFORMATION1 = 'FI_EXTERNAL_COMPANY'
2212 THEN
2213 RAISE EXT_COMP_EXCEPTION;
2214 -- FOR PENSION PROVIDER
2215
2216 ELSIF P_ORG_INFORMATION1 = 'FR_PENSION'
2217 THEN
2218 RAISE PEN_PROV_EXCEPTION;
2219 -- FOR ACCIDENT PROVIDERS
2220
2221 ELSIF P_ORG_INFORMATION1 = 'ACCIDENT'
2222 THEN
2223 RAISE ACC_PROV_EXCEPTION;
2224 ELSIF P_ORG_INFORMATION1 = 'PROV_TAX_OFFICE'
2225 THEN
2226 RAISE PROV_TAX_EXCEPTION;
2227
2228 END IF;
2229 END IF ;
2230 END IF;
2231 EXCEPTION
2232 WHEN EXT_COMP_EXCEPTION
2233 THEN
2234 fnd_message.set_name('PER', 'HR_376615_FI_EXT_COMPANY');
2235 hr_utility.raise_error;
2236 WHEN PEN_PROV_EXCEPTION
2237 THEN
2238 fnd_message.set_name('PAY', 'HR_376628_FI_PEN_PROVIDER');
2239 hr_utility.raise_error;
2240 WHEN ACC_PROV_EXCEPTION
2241 THEN
2242 fnd_message.set_name('PAY', 'HR_376629_FI_ACC_INS_PROVIDER');
2243 hr_utility.raise_error;
2244
2245 WHEN PROV_TAX_EXCEPTION
2246 THEN
2247 fnd_message.set_name('PAY', 'HR_376635_FI_PROV_TAX_OFFICE');
2248 hr_utility.raise_error;
2249
2250
2251 END CREATE_ORG_CLASS_VALIDATE;
2252 ---
2253
2254
2255 /*
2256 PROCEDURE NAME : VALIDATE_NUMBER
2257 PARAMATERS : p_number -- Number to be Validated.
2258 p_token -- Token to be displayed in MSG
2259 p_message -- Message to be called
2260
2261 PURPOSE : To validate the Number whether it is whole number or not.
2262 To call ur own specific message pass use VALIDATE_NUMBER(XXX,'token','MSG')
2263 To use the message already available i.e.. Invalid format use VALIDATE_NUMBER(XXX,'token',NULL)
2264 Dont pass VALIDATE_NUMBER(XXX,NULL,NULL)
2265 ERRORS HANDLED : Raise ERROR if No is not an whole number
2266 */
2267
2268
2269 PROCEDURE VALIDATE_NUMBER
2270 (p_number IN VARCHAR2
2271 ,p_token IN VARCHAR2
2272 ,p_message IN VARCHAR2 DEFAULT NULL
2273 ) IS
2274
2275 BEGIN
2276 IF p_number IS NOT NULL
2277 THEN
2278 IF instr(p_number,'.') <> '0'
2279 THEN
2280 IF p_token IS NOT NULL AND p_message IS NULL
2281 THEN
2282 fnd_message.set_name('PER', 'HR_376612_FI_INVALID_FORMAT');
2283 fnd_message.set_token('NAME',p_token, translate => true );
2284 hr_utility.raise_error;
2285 ELSIF p_message IS NOT NULL
2286 THEN
2287 fnd_message.set_name('PER', p_message);
2288 fnd_message.set_token('NAME',p_token, translate => true );
2289 hr_utility.raise_error;
2290 END IF ;
2291
2292 END IF;
2293 END IF;
2294
2295 END VALIDATE_NUMBER;
2296
2297 PROCEDURE PERSON_ABSENCE_CREATE
2298 (
2299 p_business_group_id IN number
2300 ,p_abs_information_category IN varchar2
2301 ,p_person_id IN Number
2302 ,p_date_start IN Date
2303 ,p_date_end IN Date
2304 ,p_abs_information1 IN Varchar2 default NULL
2305 ,p_abs_information2 IN Varchar2 default NULL
2306 ,p_abs_information3 IN Varchar2 default NULL
2307 ,p_abs_information4 IN Varchar2 default NULL
2308 ,p_abs_information5 IN Varchar2 default NULL
2309 ) IS
2310
2311 cursor get_start_date(p_person_id number) is
2312 select min(effective_start_date)
2313 from per_all_people_f
2314 where person_id=p_person_id;
2315 l_start_date date;
2316
2317 BEGIN
2318 --
2319 -- Added for GSI Bug 5472781
2320 --
2321 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
2322 --
2323 -- get the values of the person_id profile
2324 fnd_profile.put('PER_PERSON_ID',p_person_id);
2325 if p_abs_information_category='FI_F' THEN
2326 OPEN get_start_date(p_person_id);
2327 FETCH get_start_date into l_start_date;
2328 CLOSE get_start_date;
2329
2330 if fnd_date.canonical_to_date(p_abs_information1) < l_start_date THEN
2331 fnd_message.set_name('PAY', 'HR_376663_FI_MATERNITY_DATE');
2332 hr_utility.raise_error;
2333 END IF;
2334 END IF;
2335 END IF;
2336
2337 END PERSON_ABSENCE_CREATE;
2338
2339 PROCEDURE PERSON_ABSENCE_UPDATE
2340 (
2341 p_absence_attendance_id IN Number
2342 ,p_abs_information_category IN varchar2
2343 ,p_date_start IN Date
2344 ,p_date_end IN Date
2345 ,p_abs_information1 IN Varchar2 default NULL
2346 ,p_abs_information2 IN Varchar2 default NULL
2347 ,p_abs_information3 IN Varchar2 default NULL
2348 ,p_abs_information4 IN Varchar2 default NULL
2349 ,p_abs_information5 IN Varchar2 default NULL
2350 ) IS
2351
2352 cursor get_start_date(l_person_id number) is
2353 select min(effective_start_date)
2354 from per_all_people_f
2355 where person_id=l_person_id;
2356
2357 cursor get_person_id(p_absence_attendance_id in number) is
2358 select person_id
2359 from per_absence_attendances
2360 where absence_attendance_id =p_absence_attendance_id;
2361
2362 l_start_date date;
2363 l_person_id number;
2364
2365 BEGIN
2366 --
2367 -- Added for GSI Bug 5472781
2368 --
2369 IF hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
2370 --
2371 open get_person_id(p_absence_attendance_id);
2372 fetch get_person_id into l_person_id;
2373 close get_person_id;
2374
2375 -- get the values of the person_id profile
2376 fnd_profile.put('PER_PERSON_ID',l_person_id);
2377
2378 if p_abs_information_category='FI_F' THEN
2379 OPEN get_start_date(l_person_id);
2380 FETCH get_start_date into l_start_date;
2381 CLOSE get_start_date;
2382
2383 if fnd_date.canonical_to_date(p_abs_information1) < l_start_date THEN
2384 fnd_message.set_name('PAY', 'HR_376663_FI_MATERNITY_DATE');
2385 hr_utility.raise_error;
2386 END IF;
2387 END IF;
2388 END IF;
2389 END PERSON_ABSENCE_UPDATE;
2390
2391 END HR_FI_VALIDATE_PKG;
[Home] [Help]