1 PACKAGE BODY HR_SE_VALIDATE_PKG AS
2 /* $Header: pesevald.pkb 120.24 2007/07/12 14:02:29 rravi noship $ */
3
4
5 PROCEDURE validate
6 (p_person_type_id in number
7 ,p_first_name in varchar2 default null
8 ,p_national_identifier in varchar2 default null
9 ) AS
10
11 l_field varchar2(300) := NULL;
12 l_valid_date varchar2(10);
13 l_type VARCHAR2(1) := NULL;
14 CURSOR c_type IS
15 SELECT 'Y'
16 FROM per_person_types ppt
17 WHERE ppt.person_type_Id = p_person_type_id
18 AND ppt.system_person_type like 'EMP%';
19
20 BEGIN
21 l_type := NULL;
22 OPEN c_type;
23 FETCH c_type INTO l_type;
24 CLOSE c_type;
25
26
27 --Validate not null fields
28
29 IF l_type IS NOT NULL THEN
30 IF p_first_name IS NULL THEN
31 l_field := hr_general.decode_lookup('SE_FORM_LABELS','FIRST_NAME');
32
33 IF l_field IS NOT NULL THEN
34 fnd_message.set_name('PER', 'HR_377202_SE_MANDATORY_MSG');
35 fnd_message.set_token('NAME',l_field, translate => true );
36 hr_utility.raise_error;
37 END IF;
38
39 END IF;
40
41 IF p_national_identifier IS NULL or p_national_identifier = hr_api.g_varchar2 THEN
42
43 l_field := hr_general.decode_lookup('SE_FORM_LABELS','PIN');
44
45 IF l_field IS NOT NULL AND fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') in ('ERROR','WARN') THEN
46 fnd_message.set_name('PER', 'HR_377202_SE_MANDATORY_MSG');
47 fnd_message.set_token('NAME',l_field, translate => true );
48 hr_utility.raise_error;
49 END IF;
50
51 END IF;
52 END IF;
53
54
55 END;
56
57 --Procedure for validating person
58 PROCEDURE person_validate
59 (p_person_type_id in number
60 ,p_first_name in varchar2 default null
61 ,p_national_identifier in varchar2 default null
62 ) AS
63 BEGIN
64 --
65 -- Added for GSI Bug 5472781
66 --
67 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
68 --
69 validate
70 (p_person_type_id => p_person_type_id
71 ,p_first_name => p_first_name
72 ,p_national_identifier => p_national_identifier
73 );
74 END IF;
75 END person_validate;
76
77 --Procedure for validating applicant
78 PROCEDURE applicant_validate
79 (p_business_group_id in number
80 ,p_person_type_id in number
81 ,p_first_name in varchar2 default null
82 ,p_national_identifier in varchar2 default null
83 ) AS
84 l_person_type_id number ;
85 BEGIN
86 --
87 -- Added for GSI Bug 5472781
88 --
89 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
90 --
91 per_per_bus.chk_person_type
92 (p_person_type_id => l_person_type_id
93 ,p_business_group_id => p_business_group_id
94 ,p_expected_sys_type => 'APL'
95 );
96 validate
97 (p_person_type_id => l_person_type_id
98 ,p_first_name => p_first_name
99 ,p_national_identifier => p_national_identifier
100 );
101 END IF;
102 END applicant_validate;
103
104 --Procedure for validating employee
105 PROCEDURE employee_validate
106 (p_business_group_id in number
107 ,p_person_type_id in number
108 ,p_first_name in varchar2 default null
109 ,p_national_identifier in varchar2 default null
110 ) AS
111 l_person_type_id number ;
112 BEGIN
113 --
114 -- Added for GSI Bug 5472781
115 --
116 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
117 --
118 per_per_bus.chk_person_type
119 (p_person_type_id => l_person_type_id
120 ,p_business_group_id => p_business_group_id
121 ,p_expected_sys_type => 'EMP'
122 );
123 validate
124 (p_person_type_id => l_person_type_id
125 ,p_first_name => p_first_name
126 ,p_national_identifier => p_national_identifier
127 );
128 END IF;
129 END employee_validate;
130
131 --Procedure for validating contact/cwk
132 PROCEDURE contact_cwk_validate
133 (p_business_group_id in number
134 ,p_person_type_id in number
135 ,p_first_name in varchar2 default null
136 ,p_national_identifier in varchar2 default null
137 ) AS
138 l_person_type_id number ;
139 BEGIN
140 --
141 -- Added for GSI Bug 5472781
142 --
143 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
144 --
145 per_per_bus.chk_person_type
146 (p_person_type_id => l_person_type_id
147 ,p_business_group_id => p_business_group_id
148 ,p_expected_sys_type => 'OTHER'
149 );
150 validate
151 (p_person_type_id => l_person_type_id
152 ,p_first_name => p_first_name
153 ,p_national_identifier => p_national_identifier
154 );
155 END IF;
156 END contact_cwk_validate;
157
158 --___________________________________________VALIDATE_CREATE_ORG_INF_____________________________________________
159
160 PROCEDURE validate_create_org_inf
161 (p_org_info_type_code IN VARCHAR2
162 ,p_organization_id IN NUMBER
163 ,p_org_information1 IN VARCHAR2 DEFAULT null
164 ,p_org_information2 IN VARCHAR2 DEFAULT null
165 ,p_org_information3 IN VARCHAR2 DEFAULT null
166 ,p_org_information4 IN VARCHAR2 DEFAULT null
167 ,p_org_information5 IN VARCHAR2 DEFAULT null
168 ,p_org_information6 IN VARCHAR2 DEFAULT null
169 ,p_org_information7 IN VARCHAR2 DEFAULT null
170 ,p_org_information8 IN VARCHAR2 DEFAULT null
171 ,p_org_information9 IN VARCHAR2 DEFAULT null
172 ,p_org_information10 IN VARCHAR2 DEFAULT NULL
173 ,p_org_information11 IN VARCHAR2 DEFAULT NULL
174 ,p_org_information12 IN VARCHAR2 DEFAULT NULL
175 ,p_org_information13 IN VARCHAR2 DEFAULT NULL
176 ,p_org_information14 IN VARCHAR2 DEFAULT NULL
177 ,p_org_information15 IN VARCHAR2 DEFAULT NULL
178 ,p_org_information16 IN VARCHAR2 DEFAULT null
179 ) IS
180
181 l_business_group_id hr_organization_units.business_group_id%TYPE;
182 l_count NUMBER ;
183 l_field VARCHAR2(300) := NULL;
184 l_org_information1 hr_organization_information.org_information1%TYPE;
185 l_main_lc_status hr_organization_information.org_information6%TYPE;
186
187 UNIQUE_AST_NUMBER EXCEPTION;
188 UNIQUE_ORG_NUMBER EXCEPTION;
189 UNIQUE_MAIN_LOCAL_UNIT EXCEPTION;
190 UNIQUE_LOCAL_UNITS EXCEPTION;
191 UNIQUE_LE_AP EXCEPTION;
192 INVALID_DATE EXCEPTION;
193 INVALID_TAX EXCEPTION;
194 INVALID_YEAR EXCEPTION;
195 INVALID_DECIMAL EXCEPTION;
196
197 ENTER_ALL EXCEPTION;
198 ENTER_GROUP_BY EXCEPTION;
199 INVALID_CATEGORY EXCEPTION;
200 INVALID_UPDATE EXCEPTION;
201 INVALID_INSURANCE_NUMBER EXCEPTION;
202 INVALID_VALUE EXCEPTION;
203 INVALID_INSURANCE_DECIMAL EXCEPTION;
204 UNIQUE_YEAR EXCEPTION;
205 INVALID_MEMBERSHIP_NUMBER EXCEPTION;
206 INVALID_MEMBERSHIP_DECIMAL EXCEPTION;
207 INVALID_WORK_NUMBER EXCEPTION;
208 INVALID_WORK_DECIMAL EXCEPTION;
209 INVALID_ASSOCIATION_NUMBER EXCEPTION;
210 INVALID_ASSOCIATION_DECIMAL EXCEPTION;
211 INVALID_AGREEMENT_NUMBER EXCEPTION;
212 INVALID_AGREEMENT_DECIMAL EXCEPTION;
213 INVALID_AGREEMENT_NUM EXCEPTION;
214
215
216 l_token varchar2(300);
217 l_number number;
218 CURSOR getbgid IS
219 SELECT business_group_id
220 FROM hr_all_organization_units
221 WHERE organization_id = p_organization_id;
222 /*
223 CURSOR orglocalunit IS
224 select o.organization_id
225 from hr_organization_units o , hr_organization_information hoi , FND_SESSIONS s
226 where o.organization_id = hoi.organization_id
227 and hoi.org_information_context = 'CLASS'
228 and hoi.org_information1 = 'SE_LOCAL_UNIT'
229 and to_char(o.organization_id) in
230 (
231 select hoinf.org_information1
232 from hr_organization_units org, hr_organization_information hoinf
233 where org.business_group_id = l_business_group_id
234 and org.organization_id = hoinf.organization_id
235 and hoinf.org_information_context = 'SE_LOCAL_UNITS'
236 )
237 and s.session_id = userenv('sessionid')
238 and s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
239 ORDER BY o.name;
240 */
241 CURSOR orglocalunit IS
242 select o.organization_id
243 from hr_organization_units o ,
244 hr_organization_information hoi,
245 hr_organization_information hoinf,
246 FND_SESSIONS s
247 where o.organization_id = hoi.organization_id
248 and o.business_group_id = l_business_group_id
249 and hoi.org_information_context = 'CLASS'
250 and hoi.org_information1 = 'SE_LOCAL_UNIT'
251 and hoinf.org_information_context = 'SE_LOCAL_UNITS'
252 and to_char(o.organization_id) = hoinf.org_information1
253 and to_char(hoi.organization_id) = hoinf.org_information1
254 and s.session_id = userenv('sessionid')
255 and s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
256 ORDER BY o.name;
257 /*
258 CURSOR ast_number IS
259 select count(hoi2.org_information1)
260 from HR_ORGANIZATION_UNITS o1
261 , HR_ORGANIZATION_INFORMATION hoi1
262 , HR_ORGANIZATION_INFORMATION hoi2
263 WHERE o1.business_group_id =l_business_group_id
264 and hoi1.organization_id = o1.organization_id
265 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
266 and hoi1.org_information_context = 'CLASS'
267 and o1.organization_id = hoi2.organization_id
268 and hoi2.org_information1 = p_org_information1
269 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
270 and hoi1.organization_id in
271 (select hoi1.organization_id
272 from HR_ORGANIZATION_UNITS o1
273 , HR_ORGANIZATION_INFORMATION hoi1
274 , HR_ORGANIZATION_INFORMATION hoi2
275 , HR_ORGANIZATION_INFORMATION hoi3
276 WHERE o1.business_group_id =l_business_group_id
277 and hoi1.organization_id = o1.organization_id
278 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
279 and hoi1.org_information_context = 'CLASS'
280 and o1.organization_id = hoi2.org_information1
281 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
282 and hoi2.organization_id = hoi3.organization_id
283 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
284 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
285 AND hoi3.organization_id IN
286 (select hoi3.organization_id
287 from HR_ORGANIZATION_UNITS o1
288 , HR_ORGANIZATION_INFORMATION hoi1
289 , HR_ORGANIZATION_INFORMATION hoi2
290 , HR_ORGANIZATION_INFORMATION hoi3
291 WHERE o1.business_group_id =l_business_group_id
292 and hoi1.organization_id = o1.organization_id
293 and hoi1.organization_id = p_organization_id
294 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
295 and hoi1.org_information_context = 'CLASS'
296 and o1.organization_id = hoi2.org_information1
297 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
298 and hoi2.organization_id = hoi3.organization_id
299 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
300 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ) );
301 */
302
303
304
305 CURSOR FIND_LEGAL_EMPLOYER IS
306 select hoi3.organization_id
307 from HR_ALL_ORGANIZATION_UNITS o1
308 , HR_ORGANIZATION_INFORMATION hoi1
309 , HR_ORGANIZATION_INFORMATION hoi2
310 , HR_ORGANIZATION_INFORMATION hoi3
311 WHERE o1.business_group_id =l_business_group_id
312 and hoi1.organization_id = o1.organization_id
313 and hoi1.organization_id = p_organization_id
314 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
315 and hoi1.org_information_context = 'CLASS'
316 and o1.organization_id = hoi2.org_information1
317 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
318 and hoi2.organization_id = hoi3.organization_id
319 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
320 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
321
322 L_LEGAL_EMPLOYER NUMBER;
323 CURSOR ast_number IS
324 select count(hoi2.org_information1)
325 from HR_ALL_ORGANIZATION_UNITS o1
326 , HR_ORGANIZATION_INFORMATION hoi1
327 , HR_ORGANIZATION_INFORMATION hoi2
328 , HR_ORGANIZATION_INFORMATION hoi3
329 , HR_ORGANIZATION_INFORMATION hoi4
330 WHERE o1.business_group_id =l_business_group_id
331 and hoi1.organization_id = o1.organization_id
332 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
333 and hoi1.org_information_context = 'CLASS'
334 and o1.organization_id = hoi2.organization_id
335 and hoi2.org_information1 = p_org_information1
336 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
337 and hoi2.organization_id = hoi4.org_information1
338 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
339 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
340 and hoi4.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
341 and hoi4.organization_id = hoi3.organization_id
342 and hoi4.organization_id = L_LEGAL_EMPLOYER;
343
344 /* CURSOR main_lc IS
345 select count(hoi2.org_information6)
346 from HR_ORGANIZATION_UNITS o1
347 , HR_ORGANIZATION_INFORMATION hoi1
348 , HR_ORGANIZATION_INFORMATION hoi2
349 WHERE o1.business_group_id =l_business_group_id
350 and hoi1.organization_id = o1.organization_id
351 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
352 and hoi1.org_information_context = 'CLASS'
353 and o1.organization_id = hoi2.organization_id
354 and hoi2.org_information6 = 'Y'
355 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
356 and hoi1.organization_id in
357 (select hoi1.organization_id
358 from HR_ORGANIZATION_UNITS o1
359 , HR_ORGANIZATION_INFORMATION hoi1
360 , HR_ORGANIZATION_INFORMATION hoi2
361 , HR_ORGANIZATION_INFORMATION hoi3
362 WHERE o1.business_group_id =l_business_group_id
363 and hoi1.organization_id = o1.organization_id
364 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
365 and hoi1.org_information_context = 'CLASS'
366 and o1.organization_id = hoi2.org_information1
367 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
368 and hoi2.organization_id = hoi3.organization_id
369 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
370 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
371 AND hoi3.organization_id IN
372 (select hoi3.organization_id
373 from HR_ORGANIZATION_UNITS o1
374 , HR_ORGANIZATION_INFORMATION hoi1
375 , HR_ORGANIZATION_INFORMATION hoi2
376 , HR_ORGANIZATION_INFORMATION hoi3
377 WHERE o1.business_group_id =l_business_group_id
378 and hoi1.organization_id = o1.organization_id
379 and hoi1.organization_id = p_organization_id
380 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
381 and hoi1.org_information_context = 'CLASS'
382 and o1.organization_id = hoi2.org_information1
383 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
384 and hoi2.organization_id = hoi3.organization_id
385 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
386 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ) );*/
387
388 CURSOR main_lc IS
389 select count(hoi2.org_information6)
390 from HR_ALL_ORGANIZATION_UNITS o1
391 , HR_ORGANIZATION_INFORMATION hoi1
392 , HR_ORGANIZATION_INFORMATION hoi2
393 , HR_ORGANIZATION_INFORMATION hoi3
394 , HR_ORGANIZATION_INFORMATION hoi4
395 WHERE o1.business_group_id =l_business_group_id
396 and hoi1.organization_id = o1.organization_id
397 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
398 and hoi1.org_information_context = 'CLASS'
399 and o1.organization_id = hoi2.organization_id
400 and hoi2.org_information6 = 'Y'
401 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
402 and hoi2.organization_id = hoi4.org_information1
403 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
404 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
405 and hoi4.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
406 and hoi4.organization_id = hoi3.organization_id
407 and hoi4.organization_id = L_LEGAL_EMPLOYER;
408
409 /*
410 CURSOR main_lc_le IS
411 select count(hoi2.org_information6)
412 from HR_ORGANIZATION_UNITS o1
413 , HR_ORGANIZATION_INFORMATION hoi1
414 , HR_ORGANIZATION_INFORMATION hoi2
415 WHERE o1.business_group_id =l_business_group_id
416 and hoi1.organization_id = o1.organization_id
417 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
418 and hoi1.org_information_context = 'CLASS'
419 and o1.organization_id = hoi2.organization_id
420 and hoi2.org_information6 = 'Y'
421 and hoi2.organization_id <> P_ORG_INFORMATION1
422 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
423 and hoi1.organization_id in
424 (select hoi1.organization_id
425 from HR_ORGANIZATION_UNITS o1
426 , HR_ORGANIZATION_INFORMATION hoi1
427 , HR_ORGANIZATION_INFORMATION hoi2
428 , HR_ORGANIZATION_INFORMATION hoi3
429 WHERE o1.business_group_id =l_business_group_id
430 and hoi1.organization_id = o1.organization_id
431 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
432 and hoi1.org_information_context = 'CLASS'
433 and o1.organization_id = hoi2.org_information1
434 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
435 and hoi2.organization_id = hoi3.organization_id
436 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
437 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
438 AND hoi3.organization_id = p_organization_id );
439 */
440 CURSOR main_lc_le IS select count(hoi2.org_information6)
441 from HR_ALL_ORGANIZATION_UNITS o1
442 , HR_ORGANIZATION_INFORMATION hoi1
443 , HR_ORGANIZATION_INFORMATION hoi2
444 , HR_ORGANIZATION_INFORMATION hoi3
445 , HR_ORGANIZATION_INFORMATION hoi4
446 WHERE o1.business_group_id =l_business_group_id
447 and hoi1.organization_id = o1.organization_id
448 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
449 and hoi1.org_information_context = 'CLASS'
450 and o1.organization_id = hoi2.organization_id
451 and hoi2.org_information6 = 'Y'
452 and hoi2.organization_id <> P_ORG_INFORMATION1
453 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
454 and o1.organization_id = hoi3.org_information1
455 and hoi3.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
456 and hoi3.organization_id = hoi4.organization_id
457 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
458 and hoi4.org_information1 = 'HR_LEGAL_EMPLOYER'
459 AND hoi4.organization_id = p_organization_id ;
460
461 /*
462 CURSOR ast_number_le IS
463 select count(hoi2.org_information1)
464 from HR_ORGANIZATION_UNITS o1
465 , HR_ORGANIZATION_INFORMATION hoi1
466 , HR_ORGANIZATION_INFORMATION hoi2
467 WHERE o1.business_group_id =l_business_group_id
468 and hoi1.organization_id = o1.organization_id
469 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
470 and hoi1.org_information_context = 'CLASS'
471 and o1.organization_id = hoi2.organization_id
472 and hoi2.organization_id <> P_ORG_INFORMATION1
473 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
474 and hoi2.org_information1 in
475 (select hoi2.org_information1
476 from HR_ORGANIZATION_UNITS o1
477 , HR_ORGANIZATION_INFORMATION hoi1
478 , HR_ORGANIZATION_INFORMATION hoi2
479 WHERE o1.business_group_id =l_business_group_id
480 and hoi1.organization_id = o1.organization_id
481 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
482 and hoi1.org_information_context = 'CLASS'
483 and o1.organization_id = hoi2.organization_id
484 and hoi2.organization_id = P_ORG_INFORMATION1
485 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
486 )
487 and hoi1.organization_id in
488 (select hoi1.organization_id
489 from HR_ORGANIZATION_UNITS o1
490 , HR_ORGANIZATION_INFORMATION hoi1
491 , HR_ORGANIZATION_INFORMATION hoi2
492 , HR_ORGANIZATION_INFORMATION hoi3
493 WHERE o1.business_group_id =l_business_group_id
494 and hoi1.organization_id = o1.organization_id
495 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
496 and hoi1.org_information_context = 'CLASS'
497 and o1.organization_id = hoi2.org_information1
498 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
499 and hoi2.organization_id = hoi3.organization_id
500 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
501 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
502 AND hoi3.organization_id = p_organization_id);
503 */
504 CURSOR ast_number_le IS
505 select count(hoi2.org_information1)
506 from HR_ALL_ORGANIZATION_UNITS o1
507 , HR_ORGANIZATION_INFORMATION hoi1
508 , HR_ORGANIZATION_INFORMATION hoi2
509 , HR_ORGANIZATION_INFORMATION hoi3
510 , HR_ORGANIZATION_INFORMATION hoi4
511 , HR_ORGANIZATION_INFORMATION hoi5
512 WHERE o1.business_group_id =l_business_group_id
513 and hoi1.organization_id = o1.organization_id
514 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
515 and hoi1.org_information_context = 'CLASS'
516 and o1.organization_id = hoi2.organization_id
517 and hoi2.organization_id <> p_org_information1
518 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
519 and o1.organization_id = hoi3.org_information1
520 and hoi3.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
521 and hoi3.organization_id = hoi4.organization_id
522 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
523 and hoi4.org_information1 = 'HR_LEGAL_EMPLOYER'
524 AND hoi4.organization_id = p_organization_id
525 and hoi5.organization_id = p_org_information1
526 and hoi5.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
527 and hoi2.org_information1 = hoi5.org_information1;
528
529 CURSOR tax_lc(l_param1 varchar2,l_param2 number) IS
530 select count(hoi1.org_information1)
531 from HR_ALL_ORGANIZATION_UNITS o1
532 , HR_ORGANIZATION_INFORMATION hoi1
533 , HR_ORGANIZATION_INFORMATION hoi2
534 WHERE o1.business_group_id =l_business_group_id
535 and o1.organization_id=hoi1.organization_id
536 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
537 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
538 and hoi2.ORG_INFORMATION_CONTEXT='SE_TAX_DECLARATION_DETAILS'
539 and hoi2.organization_id = hoi1.organization_id
540 and hoi2.org_information1=l_param1
541 and hoi2.org_information2=l_param2
542 and hoi2.organization_id = p_organization_id;
543
544 CURSOR absence_cat_limit(l_param1 varchar2) IS
545 select count(hoi1.org_information1)
546 from HR_ALL_ORGANIZATION_UNITS o1
547 , HR_ORGANIZATION_INFORMATION hoi1
548 , HR_ORGANIZATION_INFORMATION hoi2
549 WHERE o1.business_group_id =l_business_group_id
550 and o1.organization_id=hoi1.organization_id
551 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
552 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
553 and hoi2.ORG_INFORMATION_CONTEXT='SE_ABSENCE_CATEGORY_LIMIT'
554 and hoi2.organization_id = hoi1.organization_id
555 and hoi2.org_information1=l_param1
556 and hoi2.organization_id = p_organization_id;
557
558 CURSOR org_number IS
559 select count(hoi2.org_information2)
560 from HR_ALL_ORGANIZATION_UNITS o1
561 , HR_ORGANIZATION_INFORMATION hoi1
562 , HR_ORGANIZATION_INFORMATION hoi2
563 WHERE o1.business_group_id =l_business_group_id
564 and hoi1.organization_id = o1.organization_id
565 and hoi1.org_information_context = 'CLASS'
566 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
567 and o1.organization_id = hoi2.organization_id
568 and hoi1.organization_id = hoi2.organization_id
569 and hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS'
570 and hoi2.org_information2 = p_org_information2;
571
572 CURSOR main_lc_status IS
573 select hoi2.org_information6
574 from HR_ALL_ORGANIZATION_UNITS o1
575 , HR_ORGANIZATION_INFORMATION hoi1
576 , HR_ORGANIZATION_INFORMATION hoi2
577 WHERE o1.business_group_id =l_business_group_id
578 and hoi1.organization_id = o1.organization_id
579 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
580 and hoi1.org_information_context = 'CLASS'
581 and o1.organization_id = hoi2.organization_id
582 and hoi1.organization_id = hoi2.organization_id
583 and o1.organization_id = p_org_information1
584 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS';
585
586
587 CURSOR c_ins_le_ap_dtls IS
588 SELECT COUNT(*)
589 FROM hr_organization_information hoi, hr_all_organization_units ou
590 WHERE (hoi.org_information_context = 'SE_COMPANY_MILEAGE_RATES')
591 AND ou.organization_id = hoi.organization_id
592 AND ou.organization_id = nvl(p_organization_id , 0)
593 AND ou.business_group_id = l_business_group_id
594 AND (( fnd_date.canonical_to_date(p_org_information4) between fnd_date.canonical_to_date(hoi.org_information4) AND
595 nvl(fnd_date.canonical_to_date(hoi.org_information5),to_date('31/12/4712','DD/MM/YYYY')))
596 OR ( nvl(fnd_date.canonical_to_date(p_org_information5),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hoi.org_information4) AND
597 nvl(fnd_date.canonical_to_date(hoi.org_information5),to_date('31/12/4712','DD/MM/YYYY')))
598 OR ( fnd_date.canonical_to_date(hoi.org_information4) between fnd_date.canonical_to_date(p_org_information4) AND
599 nvl(fnd_date.canonical_to_date(p_org_information5),to_date('31/12/4712','DD/MM/YYYY')))
600 OR ( nvl(fnd_date.canonical_to_date(hoi.org_information4),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(p_org_information4) AND
601 nvl(fnd_date.canonical_to_date(p_org_information5),to_date('31/12/4712','DD/MM/YYYY'))));
602
603 CURSOR csr_global(csr_v_global VARCHAR2 )IS
604 SELECT nvl(global_value,0) FROM ff_globals_f,
605 FND_SESSIONS s
606 WHERE GLOBAL_NAME=csr_v_global --'SE_EMPLOYER_TAX_PERC'
607 AND legislation_code='SE'
608 and s.effective_date --p_effective_date
609 BETWEEN effective_start_date AND
610 effective_end_date
611 AND s.session_id=userenv('sessionid');
612
613 CURSOR csr_agreement_meaning(csr_v_lookup_code varchar2) is
614 SELECT
615 trim(meaning)
616 FROM hr_lookups
617 WHERE lookup_type='SE_AGREEMENT_CODE'
618 AND lookup_code=TRIM(csr_v_lookup_code);
619
620
621 l_global_value NUMBER;
622 l_agreement_meaning varchar2(50);
623
624 CURSOR csr_year IS
625 SELECT count(*)
626 FROM hr_organization_information hoi, hr_organization_units ou, fnd_sessions s
627 WHERE hoi.org_information_context = 'SE_STD_WORK_DETAILS'
628 AND ou.organization_id = hoi.organization_id
629 AND ou.organization_id = nvl (p_organization_id, 0)
630 AND ou.business_group_id = l_business_group_id
631 AND hoi.org_information1 = p_org_information1
632 AND s.session_id = userenv ('sessionid')
633 AND s.effective_date BETWEEN ou.date_from AND nvl (ou.date_to, TO_DATE ('31/12/4712', 'DD/MM/YYYY'));
634
635
636 BEGIN
637 --
638 -- Added for GSI Bug 5472781
639 --
640 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
641 --
642 OPEN getbgid;
643 FETCH getbgid INTO l_business_group_id;
644 CLOSE getbgid;
645
646 IF p_org_info_type_code = 'SE_LOCAL_UNITS' THEN
647 OPEN orglocalunit;
648 LOOP
649 FETCH orglocalunit into l_org_information1;
650 EXIT WHEN orglocalunit%NOTFOUND;
651 IF l_org_information1 = p_org_information1 then
652 RAISE UNIQUE_LOCAL_UNITS ;
653 END IF ;
654 END LOOP;
655 CLOSE orglocalunit;
656
657
658
659 OPEN ast_number_le;
660 FETCH ast_number_le INTO l_count;
661 CLOSE ast_number_le;
662 IF l_count > 0 THEN
663 RAISE UNIQUE_AST_NUMBER ;
664 END IF ;
665
666 OPEN main_lc_status;
667 FETCH main_lc_status INTO l_main_lc_status;
668 CLOSE main_lc_status;
669
670 IF l_main_lc_status ='Y' then
671 OPEN main_lc_le;
672 FETCH main_lc_le INTO l_count;
673 CLOSE main_lc_le;
674 IF l_count > 0 THEN
675 RAISE UNIQUE_MAIN_LOCAL_UNIT ;
676 END IF ;
677 END IF;
678
679
680 ELSIF p_org_info_type_code = 'SE_LOCAL_UNIT_DETAILS' THEN
681 -- validate for presence of Decimals in AST Number
682 validate_number(p_org_information1,hr_general.decode_lookup('SE_FORM_LABELS','AST'));
683 -- validate for Uniqness of AST Number within a Legal Employer
684 IF p_org_information1 IS NOT NULL THEN
685 OPEN FIND_LEGAL_EMPLOYER;
686 FETCH FIND_LEGAL_EMPLOYER INTO L_LEGAL_EMPLOYER;
687 CLOSE FIND_LEGAL_EMPLOYER;
688
689
690 OPEN ast_number;
691 FETCH ast_number INTO l_count;
692 CLOSE ast_number;
693 IF l_count > 0 THEN
694 RAISE UNIQUE_AST_NUMBER ;
695 END IF ;
696 END IF;
697
698 -- validate for presence of Decimals in CFAR Number
699 validate_number(p_org_information2,hr_general.decode_lookup('SE_FORM_LABELS','CFAR'));
700
701 IF p_org_information6 ='Y' THEN
702 OPEN FIND_LEGAL_EMPLOYER;
703 FETCH FIND_LEGAL_EMPLOYER INTO L_LEGAL_EMPLOYER;
704 CLOSE FIND_LEGAL_EMPLOYER;
705
706 OPEN main_lc;
707 FETCH main_lc INTO l_count;
708 CLOSE main_lc;
709 IF l_count > 0 THEN
710 RAISE UNIQUE_MAIN_LOCAL_UNIT ;
711 END IF ;
712 END IF;
713
714
715
716 ELSIF p_org_info_type_code = 'SE_LEGAL_EMPLOYER_DETAILS' THEN
717 -- validate for presence of Decimals in Confederation Number
718 validate_number(p_org_information1,hr_general.decode_lookup('SE_FORM_LABELS','CONFD'));
719 -- validate for presence of Decimals in Organization Number
720 validate_number(p_org_information2,hr_general.decode_lookup('SE_FORM_LABELS','ORG'));
721
722 -- validate for Uniqness of Organization Number within a Legal Employer
723 IF p_org_information2 IS NOT NULL THEN
724 OPEN org_number;
725 FETCH org_number INTO l_count;
726 CLOSE org_number;
727 IF l_count > 0 THEN
728 RAISE UNIQUE_ORG_NUMBER ;
729 END IF ;
730 IF length(p_org_information6) <7 THEN
731 RAISE INVALID_INSURANCE_NUMBER;
732 END IF;
733
734 IF instr(p_org_information6,'.')>0 THEN
735 RAISE INVALID_INSURANCE_DECIMAL;
736 END IF;
737 --Membership Number
738 IF length(p_org_information9) <7 THEN
739 RAISE INVALID_MEMBERSHIP_NUMBER;
740 END IF;
741 IF instr(p_org_information9,'.')>0 THEN
742 RAISE INVALID_MEMBERSHIP_DECIMAL;
743 END IF;
744
745 END IF;
746 ELSIF p_org_info_type_code ='SE_SALARY_STRUCTURE' THEN
747 --Working Site Number
748 IF length(p_org_information1) <3 THEN
749 RAISE INVALID_WORK_NUMBER;
750 END IF;
751 IF instr(p_org_information1,'.')>0 THEN
752 RAISE INVALID_WORK_DECIMAL;
753 END IF;
754 --Association Number
755 IF length(p_org_information2) <2 THEN
756 RAISE INVALID_ASSOCIATION_NUMBER;
757 END IF;
758 IF instr(p_org_information2,'.')>0 THEN
759 RAISE INVALID_ASSOCIATION_DECIMAL;
760 END IF;
761 --Agreement Number
762 OPEN csr_agreement_meaning(p_org_information3);
763 FETCH csr_agreement_meaning INTO l_agreement_meaning;
764 CLOSE csr_agreement_meaning;
765 BEGIN
766 l_number:=to_number(l_agreement_meaning);
767 EXCEPTION
768 WHEN OTHERS THEN
769 RAISE INVALID_AGREEMENT_NUM;
770 END;
771 /* IF translate(l_agreement_meaning,'_0123456789.','_') IS NOT NULL THEN
772 RAISE INVALID_AGREEMENT_NUM;
773 END IF;*/
774
775 IF length(l_agreement_meaning) <>3 THEN
776 RAISE INVALID_AGREEMENT_NUMBER;
777 END IF;
778 IF instr(l_agreement_meaning,'.')>0 THEN
779 RAISE INVALID_AGREEMENT_DECIMAL;
780 END IF;
781
782 ELSIF p_org_info_type_code = 'SE_COMPANY_MILEAGE_RATES' THEN
783
784 IF fnd_date.canonical_to_date(p_org_information5) < fnd_date.canonical_to_date(p_org_information4) THEN
785 RAISE INVALID_DATE ;
786 END IF;
787
788 OPEN c_ins_le_ap_dtls ;
789 FETCH c_ins_le_ap_dtls INTO l_count;
790 CLOSE c_ins_le_ap_dtls ;
791 IF l_count > 0 THEN
792 RAISE UNIQUE_LE_AP ;
793 END IF ;
794
795 ELSIF p_org_info_type_code='SE_TAX_DECLARATION_DETAILS' THEN
796 IF (p_org_information2 <= 0) OR (p_org_information2 > 4712) THEN
797 RAISE INVALID_YEAR;
798 END IF;
799 IF instr(p_org_information2,'.')>0 THEN
800 RAISE INVALID_DECIMAL;
801 END IF;
802 OPEN tax_lc(p_org_information1,p_org_information2);
803 FETCH tax_lc INTO l_count;
804 CLOSE tax_lc;
805 IF l_count > 0 THEN
806 RAISE INVALID_TAX ;
807 END IF ;
808
809
810 OPEN csr_global('SE_COMPANY_PERC_MAX');
811 FETCH csr_global INTO l_global_value;
812 CLOSE csr_global;
813
814
815 IF p_org_information10>l_global_value THEN
816 RAISE INVALID_VALUE ;
817 END IF;
818 l_global_value:=null;
819
820 OPEN csr_global('SE_EXT_COMPANY_PERC_MAX');
821 FETCH csr_global INTO l_global_value;
822 CLOSE csr_global;
823
824 IF p_org_information12>l_global_value THEN
825 RAISE INVALID_VALUE ;
826 END IF;
827
828 ELSIF p_org_info_type_code='SE_ABSENCE_CATEGORY_LIMIT' THEN
829 OPEN absence_cat_limit(p_org_information1);
830 FETCH absence_cat_limit INTO l_count;
831 CLOSE absence_cat_limit;
832 IF l_count>0 THEN
833 RAISE INVALID_CATEGORY;
834 END IF;
835 ELSIF p_org_info_type_code='SE_INSURANCE_NUMBER' THEN
836 IF length(p_org_information1) <7 THEN
837 RAISE INVALID_INSURANCE_NUMBER;
838 END IF;
839 IF instr(p_org_information1,'.')>0 THEN
840 RAISE INVALID_INSURANCE_DECIMAL;
841 END IF;
842 ELSIF p_org_info_type_code='SE_SOE_ELEMENT_ADD_DETAILS' THEN
843 -- hr_utility.trace_on(null,'vetri');
844 -- hr_utility.trace('Joined ==> ' );
845 -- hr_utility.set_location(' p_org_information3 ' ||p_org_information3 ,10);
846 -- hr_utility.set_location(' p_org_information4 '||p_org_information4 ,10);
847 -- hr_utility.set_location(' p_org_information5 '||p_org_information5 ,10);
848 -- hr_utility.set_location(' p_org_information6 '||p_org_information6 ,10);
849 -- hr_utility.set_location(' p_org_information7 '||p_org_information7 ,10);
850 -- hr_utility.set_location(' p_org_information8 '||p_org_information8 ,10);
851 -- hr_utility.set_location(' p_org_information9 '||p_org_information9 ,10);
852 -- hr_utility.set_location(' p_org_information10 '||p_org_information10 ,10);
853
854 IF p_org_information5 ='I'
855 THEN
856 IF p_org_information7 IS NULL
857 or p_org_information8 is NULL
858 or p_org_information9 IS NULL
859 or p_org_information10 IS NULL
860 THEN
861 Raise ENTER_ALL;
862 END IF;
863 END IF;
864 IF p_org_information4='O'
865 THEN
866 IF p_org_information6 is null
867 then
868 RAISE ENTER_GROUP_BY;
869 END IF;
870 END IF;
871
872 ELSIF p_org_info_type_code= 'SE_STD_WORK_DETAILS' THEN
873 OPEN csr_year;
874 FETCH csr_year INTO l_count;
875 CLOSE csr_year;
876 IF l_count > 0 THEN
877 RAISE UNIQUE_YEAR;
878 END IF;
879
880 END IF ;
881 END IF;
882 EXCEPTION
883 WHEN ENTER_GROUP_BY THEN
884 fnd_message.set_name('PER','HR_377202_SE_MANDATORY_MSG');
885 l_token := hr_general.decode_lookup('SE_FORM_LABELS','GBP');
886 fnd_message.set_token('NAME',l_token, translate => true );
887 hr_utility.raise_error;
888
889 WHEN ENTER_ALL THEN
890 fnd_message.set_name('PER','HR_377202_SE_MANDATORY_MSG');
891 l_token := hr_general.decode_lookup('SE_FORM_LABELS','UNIT') || ','||
892 hr_general.decode_lookup('SE_FORM_LABELS','UP') || ','||
893 hr_general.decode_lookup('SE_FORM_LABELS','AMT')|| ','||
894 hr_general.decode_lookup('SE_FORM_LABELS','GBU');
895 fnd_message.set_token('NAME',l_token, translate => true );
896 hr_utility.raise_error;
897 WHEN UNIQUE_AST_NUMBER THEN
898 fnd_message.set_name('PER', 'HR_377206_SE_UNIQUE_AST_NUMBER');
899 hr_utility.raise_error;
900
901 WHEN UNIQUE_ORG_NUMBER THEN
902 fnd_message.set_name('PER', 'HR_377214_SE_UNIQUE_ORG_NUMBER');
903 hr_utility.raise_error;
904
905 WHEN UNIQUE_MAIN_LOCAL_UNIT THEN
906 fnd_message.set_name('PER', 'HR_377208_SE_MAIN_LOCAL_UNIT');
907 hr_utility.raise_error;
908
909 WHEN UNIQUE_LOCAL_UNITS THEN
910 fnd_message.set_name('PER', 'HR_377210_SE_UNIQUE_LOCAL_UNIT');
911 hr_utility.raise_error;
912
913 WHEN UNIQUE_LE_AP
914 THEN
915 fnd_message.set_name('PAY', 'HR_377227_SE_LE_UNIQ_MILE_RATE');
916 hr_utility.raise_error;
917
918 WHEN INVALID_DATE
919 THEN
920 fnd_message.set_name('PAY', 'HR_377228_SE_VALID_DATE');
921 hr_utility.raise_error;
922
923 WHEN INVALID_TAX
924 THEN
925 fnd_message.set_name('PAY', 'HR_377234_SE_TAX_DECL');
926 hr_utility.raise_error;
927
928 WHEN INVALID_YEAR
929 THEN
930 fnd_message.set_name('PAY', 'HR_377236_SE_INVALID_YEAR');
931 hr_utility.raise_error;
932
933 WHEN INVALID_DECIMAL
934 THEN
935 fnd_message.set_name('PAY', 'HR_377237_SE_INVALID_DECIMAL');
936 hr_utility.raise_error;
937 WHEN INVALID_CATEGORY
938 THEN
939 fnd_message.set_name('PAY', 'HR_377240_SE_INVALID_CATEGORY');
940 hr_utility.raise_error;
941 WHEN INVALID_UPDATE
942 THEN
943 fnd_message.set_name('PAY', 'HR_377241_SE_INVALID_UPDATE');
944 hr_utility.raise_error;
945 WHEN INVALID_INSURANCE_NUMBER
946 THEN
947 fnd_message.set_name('PAY', 'HR_377248_SE_INVALID_INSURANCE');
948 hr_utility.raise_error;
949 WHEN INVALID_VALUE
950 THEN
951 fnd_message.set_name('PAY', 'HR_377249_SE_INVALID_VALUE');
952 fnd_message.set_token('X',to_char(l_global_value));
953 hr_utility.raise_error;
954 WHEN INVALID_INSURANCE_DECIMAL
955 THEN
956 fnd_message.set_name('PAY', 'HR_377250_SE_INVALID_INS_DECIM');
957 hr_utility.raise_error;
958 WHEN UNIQUE_YEAR
959 THEN
960 fnd_message.set_name('PER', 'HR_377270_SE_STD_WORK_YEAR');
961 hr_utility.raise_error;
962 WHEN INVALID_MEMBERSHIP_NUMBER
963 THEN
964 fnd_message.set_name('PAY', 'HR_377263_SE_INVALID_MEMBER');
965 hr_utility.raise_error;
966 WHEN INVALID_MEMBERSHIP_DECIMAL
967 THEN
968 fnd_message.set_name('PAY', 'HR_377264_SE_INVALID_MEM_DECIM');
969 hr_utility.raise_error;
970 WHEN INVALID_WORK_NUMBER
971 THEN
972 fnd_message.set_name('PAY', 'HR_377265_SE_INVALID_WORK');
973 hr_utility.raise_error;
974 WHEN INVALID_WORK_DECIMAL
975 THEN
976 fnd_message.set_name('PAY', 'HR_377266_SE_INVALID_WOR_DECIM');
977 hr_utility.raise_error;
978 WHEN INVALID_ASSOCIATION_NUMBER
979 THEN
980 fnd_message.set_name('PAY', 'HR_377267_SE_INVALID_ASSOCIAT');
981 hr_utility.raise_error;
982 WHEN INVALID_ASSOCIATION_DECIMAL
983 THEN
984 fnd_message.set_name('PAY', 'HR_377268_SE_INVALID_ASS_DECIM');
985 hr_utility.raise_error;
986 WHEN INVALID_AGREEMENT_NUMBER
987 THEN
988 fnd_message.set_name('PAY', 'HR_377261_SE_AGREEMENT_CODE');
989 hr_utility.raise_error;
990 WHEN INVALID_AGREEMENT_DECIMAL
991 THEN
992 fnd_message.set_name('PAY', 'HR_377269_SE_INVALID_AGR_DECIM');
993 hr_utility.raise_error;
994 WHEN INVALID_AGREEMENT_NUM
995 THEN
996 fnd_message.set_name('PAY', 'HR_377271_SE_AGREEMENT_NUMBER');
997 hr_utility.raise_error;
998
999
1000 END validate_create_org_inf;
1001
1002
1003 --___________________________________________END OF VALIDATE_CREATE_ORG_INF_____________________________________________
1004
1005 --___________________________________________VALIDATE_UPDATE_ORG_INF_____________________________________________
1006 PROCEDURE validate_update_org_inf
1007 (p_org_info_type_code IN VARCHAR2
1008 ,p_org_information_id IN NUMBER
1009 ,p_org_information1 IN VARCHAR2 DEFAULT null
1010 ,p_org_information2 IN VARCHAR2 DEFAULT null
1011 ,p_org_information3 IN VARCHAR2 DEFAULT null
1012 ,p_org_information4 IN VARCHAR2 DEFAULT null
1013 ,p_org_information5 IN VARCHAR2 DEFAULT null
1014 ,p_org_information6 IN VARCHAR2 DEFAULT null
1015 ,p_org_information7 IN VARCHAR2 DEFAULT null
1016 ,p_org_information8 IN VARCHAR2 DEFAULT null
1017 ,p_org_information9 IN VARCHAR2 DEFAULT null
1018 ,p_org_information10 IN VARCHAR2 DEFAULT NULL
1019 ,p_org_information11 IN VARCHAR2 DEFAULT NULL
1020 ,p_org_information12 IN VARCHAR2 DEFAULT NULL
1021 ,p_org_information13 IN VARCHAR2 DEFAULT NULL
1022 ,p_org_information14 IN VARCHAR2 DEFAULT NULL
1023 ,p_org_information15 IN VARCHAR2 DEFAULT NULL
1024 ,p_org_information16 IN VARCHAR2 DEFAULT null
1025 ) IS
1026
1027 l_count NUMBER ;
1028 l_business_group_id hr_organization_units.business_group_id%TYPE;
1029 l_org_information1 hr_organization_information.org_information1%TYPE;
1030 l_field VARCHAR2(300) := NULL;
1031 l_organization_id hr_organization_information.organization_id%TYPE;
1032 l_main_lc_status hr_organization_information.org_information6%TYPE;
1033
1034 l_token varchar2(300);
1035 l_prev_holiday_start varchar2(30);
1036 l_prev_holiday_end varchar2(30);
1037 l_prev_Accounting_start varchar2(30);
1038 l_prev_Accounting_end varchar2(30);
1039 l_number number;
1040
1041 UNIQUE_AST_NUMBER EXCEPTION;
1042 UNIQUE_ORG_NUMBER EXCEPTION;
1043 UNIQUE_MAIN_LOCAL_UNIT EXCEPTION;
1044 UNIQUE_LOCAL_UNITS EXCEPTION;
1045 UNIQUE_LE_AP EXCEPTION;
1046 INVALID_DATE EXCEPTION;
1047 INVALID_TAX EXCEPTION;
1048 INVALID_YEAR EXCEPTION;
1049 INVALID_DECIMAL EXCEPTION;
1050 ENTER_ALL EXCEPTION;
1051 ENTER_GROUP_BY EXCEPTION;
1052 INVALID_CATEGORY EXCEPTION;
1053 INVALID_UPDATE EXCEPTION;
1054 INVALID_INSURANCE_NUMBER EXCEPTION;
1055 INVALID_VALUE EXCEPTION;
1056 INVALID_INSURANCE_DECIMAL EXCEPTION;
1057 UNIQUE_YEAR EXCEPTION;
1058 INVALID_MEMBERSHIP_NUMBER EXCEPTION;
1059 INVALID_MEMBERSHIP_DECIMAL EXCEPTION;
1060 INVALID_WORK_NUMBER EXCEPTION;
1061 INVALID_WORK_DECIMAL EXCEPTION;
1062 INVALID_ASSOCIATION_NUMBER EXCEPTION;
1063 INVALID_ASSOCIATION_DECIMAL EXCEPTION;
1064 INVALID_AGREEMENT_NUMBER EXCEPTION;
1065 INVALID_AGREEMENT_DECIMAL EXCEPTION;
1066 INVALID_AGREEMENT_NUM EXCEPTION;
1067 CURSOR getbgid IS
1068 SELECT business_group_id
1069 FROM hr_all_organization_units
1070 where organization_id = l_organization_id;
1071
1072 CURSOR getorgid IS
1073 SELECT organization_id
1074 FROM hr_organization_information
1075 WHERE org_information_id = p_org_information_id;
1076
1077 /*
1078 CURSOR orglocalunit IS
1079 SELECT o.organization_id
1080 FROM hr_organization_units o , hr_organization_information hoi , FND_SESSIONS s
1081 WHERE o.organization_id = hoi.organization_id
1082 AND hoi.org_information_context = 'CLASS'
1083 AND hoi.org_information1 = 'SE_LOCAL_UNIT'
1084 AND to_char(o.organization_id) IN
1085 (
1086 SELECT hoinf.org_information1
1087 FROM hr_organization_units org, hr_organization_information hoinf
1088 WHERE org.business_group_id = l_business_group_id
1089 AND org.organization_id = hoinf.organization_id
1090 AND org.organization_id <> l_organization_id
1091 AND hoinf.org_information_context = 'SE_LOCAL_UNITS'
1092 )
1093 AND s.session_id = userenv('sessionid')
1094 AND s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
1095 ORDER BY o.name;
1096 */
1097
1098 CURSOR orglocalunit IS select o.organization_id
1099 from hr_organization_units o ,
1100 hr_organization_information hoi,
1101 hr_organization_information hoinf,
1102 FND_SESSIONS s
1103 where o.organization_id = hoi.organization_id
1104 and o.business_group_id = l_business_group_id
1105 and hoi.org_information_context = 'CLASS'
1106 and hoi.org_information1 = 'SE_LOCAL_UNIT'
1107 and hoinf.org_information_context = 'SE_LOCAL_UNITS'
1108 AND hoinf.organization_id <> l_organization_id
1109 and to_char(o.organization_id) = hoinf.org_information1
1110 and s.session_id = userenv('sessionid')
1111 and s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY'))
1112 ORDER BY o.name;
1113 /*
1114 CURSOR ast_number IS
1115 select count(hoi2.org_information1)
1116 from HR_ORGANIZATION_UNITS o1
1117 , HR_ORGANIZATION_INFORMATION hoi1
1118 , HR_ORGANIZATION_INFORMATION hoi2
1119 WHERE o1.business_group_id =l_business_group_id
1120 and hoi1.organization_id = o1.organization_id
1121 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1122 and hoi1.org_information_context = 'CLASS'
1123 and o1.organization_id = hoi2.organization_id
1124 and hoi2.org_information1 = p_org_information1
1125 and hoi2.organization_id <> l_organization_id
1126 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1127 and hoi1.organization_id in
1128 (select hoi1.organization_id
1129 from HR_ORGANIZATION_UNITS o1
1130 , HR_ORGANIZATION_INFORMATION hoi1
1131 , HR_ORGANIZATION_INFORMATION hoi2
1132 , HR_ORGANIZATION_INFORMATION hoi3
1133 WHERE o1.business_group_id =l_business_group_id
1134 and hoi1.organization_id = o1.organization_id
1135 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1136 and hoi1.org_information_context = 'CLASS'
1137 and o1.organization_id = hoi2.org_information1
1138 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1139 and hoi2.organization_id = hoi3.organization_id
1140 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1141 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1142 AND hoi3.organization_id IN
1143 (select hoi3.organization_id
1144 from HR_ORGANIZATION_UNITS o1
1145 , HR_ORGANIZATION_INFORMATION hoi1
1146 , HR_ORGANIZATION_INFORMATION hoi2
1147 , HR_ORGANIZATION_INFORMATION hoi3
1148 WHERE o1.business_group_id =l_business_group_id
1149 and hoi1.organization_id = o1.organization_id
1150 and hoi1.organization_id = l_organization_id
1151 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1152 and hoi1.org_information_context = 'CLASS'
1153 and o1.organization_id = hoi2.org_information1
1154 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1155 and hoi2.organization_id = hoi3.organization_id
1156 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1157 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ) );
1158 */
1159 CURSOR FIND_LEGAL_EMPLOYER IS
1160 select hoi3.organization_id
1161 from HR_ALL_ORGANIZATION_UNITS o1
1162 , HR_ORGANIZATION_INFORMATION hoi1
1163 , HR_ORGANIZATION_INFORMATION hoi2
1164 , HR_ORGANIZATION_INFORMATION hoi3
1165 WHERE o1.business_group_id =l_business_group_id
1166 and hoi1.organization_id = o1.organization_id
1167 and hoi1.organization_id = l_organization_id
1168 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1169 and hoi1.org_information_context = 'CLASS'
1170 and o1.organization_id = hoi2.org_information1
1171 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1172 and hoi2.organization_id = hoi3.organization_id
1173 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1174 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
1175
1176 L_LEGAL_EMPLOYER NUMBER;
1177 CURSOR ast_number IS
1178 select count(hoi2.org_information1)
1179 from HR_ALL_ORGANIZATION_UNITS o1
1180 , HR_ORGANIZATION_INFORMATION hoi1
1181 , HR_ORGANIZATION_INFORMATION hoi2
1182 , HR_ORGANIZATION_INFORMATION hoi3
1183 , HR_ORGANIZATION_INFORMATION hoi4
1184 WHERE o1.business_group_id =l_business_group_id
1185 and hoi1.organization_id = o1.organization_id
1186 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1187 and hoi1.org_information_context = 'CLASS'
1188 and o1.organization_id = hoi2.organization_id
1189 and hoi2.org_information1 = p_org_information1
1190 and hoi2.organization_id <> l_organization_id
1191 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1192 and hoi2.organization_id = hoi4.org_information1
1193 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1194 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1195 and hoi4.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1196 and hoi4.organization_id = hoi3.organization_id
1197 and hoi4.organization_id = L_LEGAL_EMPLOYER;
1198
1199
1200 /*
1201 CURSOR main_lc IS
1202 select count(hoi2.org_information6)
1203 from HR_ORGANIZATION_UNITS o1
1204 , HR_ORGANIZATION_INFORMATION hoi1
1205 , HR_ORGANIZATION_INFORMATION hoi2
1206 WHERE o1.business_group_id =l_business_group_id
1207 and hoi1.organization_id = o1.organization_id
1208 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1209 and hoi1.org_information_context = 'CLASS'
1210 and o1.organization_id = hoi2.organization_id
1211 and hoi2.org_information6 = 'Y'
1212 and hoi2.organization_id <> l_organization_id
1213 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1214 and hoi1.organization_id in
1215 (select hoi1.organization_id
1216 from HR_ORGANIZATION_UNITS o1
1217 , HR_ORGANIZATION_INFORMATION hoi1
1218 , HR_ORGANIZATION_INFORMATION hoi2
1219 , HR_ORGANIZATION_INFORMATION hoi3
1220 WHERE o1.business_group_id =l_business_group_id
1221 and hoi1.organization_id = o1.organization_id
1222 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1223 and hoi1.org_information_context = 'CLASS'
1224 and o1.organization_id = hoi2.org_information1
1225 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1226 and hoi2.organization_id = hoi3.organization_id
1227 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1228 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1229 AND hoi3.organization_id IN
1230 (select hoi3.organization_id
1231 from HR_ORGANIZATION_UNITS o1
1232 , HR_ORGANIZATION_INFORMATION hoi1
1233 , HR_ORGANIZATION_INFORMATION hoi2
1234 , HR_ORGANIZATION_INFORMATION hoi3
1235 WHERE o1.business_group_id =l_business_group_id
1236 and hoi1.organization_id = o1.organization_id
1237 and hoi1.organization_id = l_organization_id
1238 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1239 and hoi1.org_information_context = 'CLASS'
1240 and o1.organization_id = hoi2.org_information1
1241 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1242 and hoi2.organization_id = hoi3.organization_id
1243 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1244 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ) );
1245
1246 */ CURSOR main_lc IS
1247 select count(hoi2.org_information6)
1248 from HR_ALL_ORGANIZATION_UNITS o1
1249 , HR_ORGANIZATION_INFORMATION hoi1
1250 , HR_ORGANIZATION_INFORMATION hoi2
1251 , HR_ORGANIZATION_INFORMATION hoi3
1252 , HR_ORGANIZATION_INFORMATION hoi4
1253 WHERE o1.business_group_id =l_business_group_id
1254 and hoi1.organization_id = o1.organization_id
1255 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1256 and hoi1.org_information_context = 'CLASS'
1257 and o1.organization_id = hoi2.organization_id
1258 and hoi2.org_information6 = 'Y'
1259 and hoi2.organization_id <> l_organization_id
1260 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1261 and hoi2.organization_id = hoi4.org_information1
1262 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1263 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1264 and hoi4.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1265 and hoi4.organization_id = hoi3.organization_id
1266 and hoi4.organization_id = L_LEGAL_EMPLOYER;
1267 /*
1268
1269 CURSOR main_lc_le IS
1270 select count(hoi2.org_information6)
1271 from HR_ORGANIZATION_UNITS o1
1272 , HR_ORGANIZATION_INFORMATION hoi1
1273 , HR_ORGANIZATION_INFORMATION hoi2
1274 WHERE o1.business_group_id =l_business_group_id
1275 and hoi1.organization_id = o1.organization_id
1276 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1277 and hoi1.org_information_context = 'CLASS'
1278 and o1.organization_id = hoi2.organization_id
1279 and hoi2.org_information6 = 'Y'
1280 and hoi2.organization_id <> P_ORG_INFORMATION1
1281 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1282 and hoi1.organization_id in
1283 (select hoi1.organization_id
1284 from HR_ORGANIZATION_UNITS o1
1285 , HR_ORGANIZATION_INFORMATION hoi1
1286 , HR_ORGANIZATION_INFORMATION hoi2
1287 , HR_ORGANIZATION_INFORMATION hoi3
1288 WHERE o1.business_group_id =l_business_group_id
1289 and hoi1.organization_id = o1.organization_id
1290 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1291 and hoi1.org_information_context = 'CLASS'
1292 and o1.organization_id = hoi2.org_information1
1293 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1294 and hoi2.organization_id = hoi3.organization_id
1295 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1296 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1297 and hoi2.org_information_id <> p_org_information_id
1298 AND hoi3.organization_id = l_organization_id );*/
1299 CURSOR main_lc_le IS select count(hoi2.org_information6)
1300 from HR_ALL_ORGANIZATION_UNITS o1
1301 , HR_ORGANIZATION_INFORMATION hoi1
1302 , HR_ORGANIZATION_INFORMATION hoi2
1303 , HR_ORGANIZATION_INFORMATION hoi3
1304 , HR_ORGANIZATION_INFORMATION hoi4
1305 WHERE o1.business_group_id =l_business_group_id
1306 and hoi1.organization_id = o1.organization_id
1307 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1308 and hoi1.org_information_context = 'CLASS'
1309 and o1.organization_id = hoi2.organization_id
1310 and hoi2.org_information6 = 'Y'
1311 and hoi2.organization_id <> P_ORG_INFORMATION1
1312 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1313 and o1.organization_id = hoi3.org_information1
1314 and hoi3.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1315 and hoi3.org_information_id <> p_org_information_id
1316 and hoi3.organization_id = hoi4.organization_id
1317 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
1318 and hoi4.org_information1 = 'HR_LEGAL_EMPLOYER'
1319 AND hoi4.organization_id = l_organization_id ;
1320 /*
1321 CURSOR ast_number_le IS
1322 select count(hoi2.org_information1)
1323 from HR_ORGANIZATION_UNITS o1
1324 , HR_ORGANIZATION_INFORMATION hoi1
1325 , HR_ORGANIZATION_INFORMATION hoi2
1326 WHERE o1.business_group_id =l_business_group_id
1327 and hoi1.organization_id = o1.organization_id
1328 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1329 and hoi1.org_information_context = 'CLASS'
1330 and o1.organization_id = hoi2.organization_id
1331 and hoi2.organization_id <> P_ORG_INFORMATION1
1332 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1333 and hoi2.org_information1 in
1334 (select hoi2.org_information1
1335 from HR_ORGANIZATION_UNITS o1
1336 , HR_ORGANIZATION_INFORMATION hoi1
1337 , HR_ORGANIZATION_INFORMATION hoi2
1338 WHERE o1.business_group_id =l_business_group_id
1339 and hoi1.organization_id = o1.organization_id
1340 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1341 and hoi1.org_information_context = 'CLASS'
1342 and o1.organization_id = hoi2.organization_id
1343 and hoi2.organization_id = P_ORG_INFORMATION1
1344 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1345 )
1346 and hoi1.organization_id in
1347 (select hoi1.organization_id
1348 from HR_ORGANIZATION_UNITS o1
1349 , HR_ORGANIZATION_INFORMATION hoi1
1350 , HR_ORGANIZATION_INFORMATION hoi2
1351 , HR_ORGANIZATION_INFORMATION hoi3
1352 WHERE o1.business_group_id =l_business_group_id
1353 and hoi1.organization_id = o1.organization_id
1354 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1355 and hoi1.org_information_context = 'CLASS'
1356 and o1.organization_id = hoi2.org_information1
1357 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1358 and hoi2.organization_id = hoi3.organization_id
1359 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
1360 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
1361 and hoi2.org_information_id <> p_org_information_id
1362 AND hoi3.organization_id = l_organization_id);
1363 */
1364 CURSOR ast_number_le IS
1365 select count(hoi2.org_information1)
1366 from HR_ALL_ORGANIZATION_UNITS o1
1367 , HR_ORGANIZATION_INFORMATION hoi1
1368 , HR_ORGANIZATION_INFORMATION hoi2
1369 , HR_ORGANIZATION_INFORMATION hoi3
1370 , HR_ORGANIZATION_INFORMATION hoi4
1371 , HR_ORGANIZATION_INFORMATION hoi5
1372 WHERE o1.business_group_id =l_business_group_id
1373 and hoi1.organization_id = o1.organization_id
1374 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1375 and hoi1.org_information_context = 'CLASS'
1376 and o1.organization_id = hoi2.organization_id
1377 and hoi2.organization_id <> p_org_information1
1378 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1379 and o1.organization_id = hoi3.org_information1
1380 and hoi3.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
1381 and hoi3.org_information_id <> p_org_information_id
1382 and hoi3.organization_id = hoi4.organization_id
1383 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
1384 and hoi4.org_information1 = 'HR_LEGAL_EMPLOYER'
1385 AND hoi4.organization_id = l_organization_id
1386 and to_char(hoi5.organization_id) = p_org_information1
1387 and hoi5.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
1388 and hoi2.org_information1 = hoi5.org_information1;
1389
1390 CURSOR tax_lc(l_param1 varchar2,l_param2 number) IS
1391 select count(hoi1.org_information1)
1392 from HR_ALL_ORGANIZATION_UNITS o1
1393 ,HR_ORGANIZATION_INFORMATION hoi1
1394 ,HR_ORGANIZATION_INFORMATION hoi2
1395 WHERE o1.business_group_id =l_business_group_id
1396 and o1.organization_id=hoi1.organization_id
1397 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
1398 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1399 and hoi2.ORG_INFORMATION_CONTEXT='SE_TAX_DECLARATION_DETAILS'
1400 and hoi2.organization_id = hoi1.organization_id
1401 and hoi2.org_information1=l_param1
1402 and hoi2.org_information2=l_param2
1403 and hoi2.organization_id = l_organization_id
1404 and hoi2.org_information_id <> p_org_information_id;
1405
1406 CURSOR absence_cat_limit(l_param1 varchar2) IS
1407 select count(hoi1.org_information1)
1408 from HR_ALL_ORGANIZATION_UNITS o1
1409 ,HR_ORGANIZATION_INFORMATION hoi1
1410 ,HR_ORGANIZATION_INFORMATION hoi2
1411 WHERE o1.business_group_id =l_business_group_id
1412 and o1.organization_id=hoi1.organization_id
1413 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
1414 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1415 and hoi2.ORG_INFORMATION_CONTEXT='SE_ABSENCE_CATEGORY_LIMIT'
1416 and hoi2.organization_id = hoi1.organization_id
1417 and hoi2.org_information1=l_param1
1418 and hoi2.organization_id = l_organization_id
1419 and hoi2.org_information_id <> p_org_information_id;
1420
1421 CURSOR org_number IS
1422 select count(hoi2.org_information2)
1423 from HR_ALL_ORGANIZATION_UNITS o1
1424 , HR_ORGANIZATION_INFORMATION hoi1
1425 , HR_ORGANIZATION_INFORMATION hoi2
1426 WHERE o1.business_group_id =l_business_group_id
1427 and hoi1.organization_id = o1.organization_id
1428 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1429 and hoi1.org_information_context = 'CLASS'
1430 and o1.organization_id = hoi2.organization_id
1431 and hoi1.organization_id = hoi2.organization_id
1432 and hoi2.org_information2 = p_org_information2
1433 and o1.organization_id <> l_organization_id
1434 and hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';
1435
1436 CURSOR main_lc_status IS
1437 select hoi2.org_information6
1438 from HR_ALL_ORGANIZATION_UNITS o1
1439 , HR_ORGANIZATION_INFORMATION hoi1
1440 , HR_ORGANIZATION_INFORMATION hoi2
1441 WHERE o1.business_group_id =l_business_group_id
1442 and hoi1.organization_id = o1.organization_id
1443 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
1444 and hoi1.org_information_context = 'CLASS'
1445 and o1.organization_id = hoi2.organization_id
1446 and hoi1.organization_id = hoi2.organization_id
1447 and o1.organization_id = p_org_information1
1448 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS';
1449
1450
1451
1452
1453 CURSOR c_upd_le_ap_dtls IS
1454 SELECT COUNT(*)
1455 FROM hr_organization_information hoi, hr_all_organization_units ou
1456 WHERE (hoi.org_information_context = 'SE_COMPANY_MILEAGE_RATES')
1457 AND ou.organization_id = hoi.organization_id
1458 AND ou.organization_id = nvl(l_organization_id , 0)
1459 AND ou.business_group_id = l_business_group_id
1460 AND (( fnd_date.canonical_to_date(p_org_information4) between fnd_date.canonical_to_date(hoi.org_information4) AND
1461 nvl(fnd_date.canonical_to_date(hoi.org_information5),to_date('31/12/4712','DD/MM/YYYY')))
1462 OR ( nvl(fnd_date.canonical_to_date(p_org_information5),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hoi.org_information4) AND
1463 nvl(fnd_date.canonical_to_date(hoi.org_information5),to_date('31/12/4712','DD/MM/YYYY')))
1464 OR ( fnd_date.canonical_to_date(hoi.org_information4) between fnd_date.canonical_to_date(p_org_information4) AND
1465 nvl(fnd_date.canonical_to_date(p_org_information5),to_date('31/12/4712','DD/MM/YYYY')))
1466 OR ( nvl(fnd_date.canonical_to_date(hoi.org_information5),to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(p_org_information4) AND
1467 nvl(fnd_date.canonical_to_date(p_org_information5),to_date('31/12/4712','DD/MM/YYYY'))))
1468 AND hoi.org_information_id <> p_org_information_id ;
1469
1470 CURSOR c_legal IS
1471 SELECT ORG_INFORMATION1,ORG_INFORMATION2,
1472 ORG_INFORMATION3,ORG_INFORMATION4
1473 FROM hr_organization_information
1474 WHERE org_information_id=p_org_information_id;
1475
1476 CURSOR csr_global(csr_v_global VARCHAR2 )IS
1477 SELECT nvl(global_value,0) FROM ff_globals_f,
1478 FND_SESSIONS s
1479 WHERE GLOBAL_NAME=csr_v_global --'SE_EMPLOYER_TAX_PERC'
1480 AND legislation_code='SE'
1481 and s.effective_date --p_effective_date
1482 BETWEEN effective_start_date AND
1483 effective_end_date
1484 AND s.session_id=userenv('sessionid');
1485
1486 CURSOR csr_agreement_meaning(csr_v_lookup_code varchar2) is
1487 SELECT
1488 trim(meaning)
1489 FROM hr_lookups
1490 WHERE lookup_type='SE_AGREEMENT_CODE'
1491 AND lookup_code=TRIM(csr_v_lookup_code);
1492
1493 l_global_value NUMBER;
1494 l_agreement_meaning varchar2(50);
1495
1496 CURSOR csr_year IS
1497 SELECT count(*)
1498 FROM hr_organization_information hoi, hr_organization_units ou, fnd_sessions s
1499 WHERE hoi.org_information_context = 'SE_STD_WORK_DETAILS'
1500 AND ou.organization_id = hoi.organization_id
1501 AND ou.organization_id = nvl (l_organization_id, 0)
1502 AND ou.business_group_id = l_business_group_id
1503 AND hoi.org_information1 = p_org_information1
1504 AND hoi.org_information_id <> p_org_information_id
1505 AND s.session_id = userenv ('sessionid')
1506 AND s.effective_date BETWEEN ou.date_from AND nvl (ou.date_to, TO_DATE ('31/12/4712', 'DD/MM/YYYY'));
1507
1508 BEGIN
1509 --
1510 -- Added for GSI Bug 5472781
1511 --
1512 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
1513 --
1514
1515 OPEN getorgid;
1516 FETCH getorgid INTO l_organization_id;
1517 CLOSE getorgid;
1518
1519
1520 OPEN getbgid;
1521 FETCH getbgid INTO l_business_group_id;
1522 CLOSE getbgid;
1523
1524 IF p_org_info_type_code = 'SE_LOCAL_UNITS' THEN
1525 OPEN orglocalunit;
1526 LOOP
1527 FETCH orglocalunit into l_org_information1;
1528 EXIT WHEN orglocalunit%NOTFOUND;
1529 IF l_org_information1 = p_org_information1 then
1530 RAISE UNIQUE_LOCAL_UNITS ;
1531 END IF ;
1532 END LOOP;
1533 CLOSE orglocalunit;
1534
1535 OPEN ast_number_le;
1536 FETCH ast_number_le INTO l_count;
1537 CLOSE ast_number_le;
1538 IF l_count > 0 THEN
1539 RAISE UNIQUE_AST_NUMBER ;
1540 END IF ;
1541
1542 OPEN main_lc_status;
1543 FETCH main_lc_status INTO l_main_lc_status;
1544 CLOSE main_lc_status;
1545
1546 IF l_main_lc_status ='Y' then
1547 OPEN main_lc_le;
1548 FETCH main_lc_le INTO l_count;
1549 CLOSE main_lc_le;
1550 IF l_count > 0 THEN
1551 RAISE UNIQUE_MAIN_LOCAL_UNIT ;
1552 END IF ;
1553 END IF;
1554
1555
1556
1557 ELSIF p_org_info_type_code = 'SE_LOCAL_UNIT_DETAILS' THEN
1558 -- validate for presence of Decimals in AST Number
1559 validate_number(p_org_information1,hr_general.decode_lookup('SE_FORM_LABELS','AST'));
1560 -- validate for Uniqness of AST Number within a Legal Employer
1561
1562
1563 IF p_org_information1 IS NOT NULL THEN
1564 OPEN FIND_LEGAL_EMPLOYER;
1565 FETCH FIND_LEGAL_EMPLOYER INTO L_LEGAL_EMPLOYER;
1566 CLOSE FIND_LEGAL_EMPLOYER;
1567
1568 OPEN ast_number;
1569 FETCH ast_number INTO l_count;
1570 CLOSE ast_number;
1571 IF l_count > 0 THEN
1572 RAISE UNIQUE_AST_NUMBER ;
1573 END IF ;
1574 END IF;
1575
1576 -- validate for presence of Decimals in CFAR Number
1577 validate_number(p_org_information2,hr_general.decode_lookup('SE_FORM_LABELS','CFAR'));
1578
1579 IF p_org_information6 ='Y' THEN
1580 OPEN FIND_LEGAL_EMPLOYER;
1581 FETCH FIND_LEGAL_EMPLOYER INTO L_LEGAL_EMPLOYER;
1582 CLOSE FIND_LEGAL_EMPLOYER;
1583
1584 OPEN main_lc;
1585 FETCH main_lc INTO l_count;
1586 CLOSE main_lc;
1587 IF l_count > 0 THEN
1588 RAISE UNIQUE_MAIN_LOCAL_UNIT ;
1589 END IF ;
1590 END IF;
1591
1592
1593 ELSIF p_org_info_type_code = 'SE_LEGAL_EMPLOYER_DETAILS' THEN
1594 -- validate for presence of Decimals in Confederation Number
1595 validate_number(p_org_information1,hr_general.decode_lookup('SE_FORM_LABELS','CONFD'));
1596 -- validate for presence of Decimals in Organization Number
1597 validate_number(p_org_information2,hr_general.decode_lookup('SE_FORM_LABELS','ORG'));
1598
1599 -- validate for Uniqness of Organization Number within a Legal Employer
1600 IF p_org_information2 IS NOT NULL THEN
1601 OPEN org_number;
1602 FETCH org_number INTO l_count;
1603 CLOSE org_number;
1604 IF l_count > 0 THEN
1605 RAISE UNIQUE_ORG_NUMBER ;
1606 END IF ;
1607 IF length(p_org_information6) <7 THEN
1608 RAISE INVALID_INSURANCE_NUMBER;
1609 END IF;
1610
1611 END IF;
1612
1613 IF instr(p_org_information6,'.')>0 THEN
1614 RAISE INVALID_INSURANCE_DECIMAL;
1615 END IF;
1616 --Membership Number
1617 IF length(p_org_information9) <7 THEN
1618 RAISE INVALID_MEMBERSHIP_NUMBER;
1619 END IF;
1620 IF instr(p_org_information9,'.')>0 THEN
1621 RAISE INVALID_MEMBERSHIP_DECIMAL;
1622 END IF;
1623
1624 ELSIF p_org_info_type_code ='SE_SALARY_STRUCTURE' THEN
1625 --Working Site Number
1626 IF length(p_org_information1) <3 THEN
1627 RAISE INVALID_WORK_NUMBER;
1628 END IF;
1629 IF instr(p_org_information1,'.')>0 THEN
1630 RAISE INVALID_WORK_DECIMAL;
1631 END IF;
1632 --Association Number
1633 IF length(p_org_information2) <2 THEN
1634 RAISE INVALID_ASSOCIATION_NUMBER;
1635 END IF;
1636 IF instr(p_org_information2,'.')>0 THEN
1637 RAISE INVALID_ASSOCIATION_DECIMAL;
1638 END IF;
1639 --Agreement Number
1640 OPEN csr_agreement_meaning(p_org_information3);
1641 FETCH csr_agreement_meaning INTO l_agreement_meaning;
1642 CLOSE csr_agreement_meaning;
1643
1644 BEGIN
1645 l_number:=to_number(l_agreement_meaning);
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 RAISE INVALID_AGREEMENT_NUM;
1649 END;
1650 /*IF translate(l_agreement_meaning,'_0123456789.','_') IS NOT NULL THEN
1651 RAISE INVALID_AGREEMENT_NUM;
1652 END IF;*/
1653
1654 IF length(l_agreement_meaning) <>3 THEN
1655 RAISE INVALID_AGREEMENT_NUMBER;
1656 END IF;
1657 IF instr(l_agreement_meaning,'.')>0 THEN
1658 RAISE INVALID_AGREEMENT_DECIMAL;
1659 END IF;
1660
1661
1662 ELSIF p_org_info_type_code = 'SE_COMPANY_MILEAGE_RATES' THEN
1663
1664 IF fnd_date.canonical_to_date(p_org_information5) < fnd_date.canonical_to_date(p_org_information4) THEN
1665 RAISE INVALID_DATE ;
1666 END IF;
1667
1668 OPEN c_upd_le_ap_dtls ;
1669 FETCH c_upd_le_ap_dtls INTO l_count;
1670 CLOSE c_upd_le_ap_dtls ;
1671 IF l_count > 0 THEN
1672 RAISE UNIQUE_LE_AP ;
1673 END IF ;
1674 ELSIF p_org_info_type_code='SE_TAX_DECLARATION_DETAILS' THEN
1675 IF (p_org_information2 <= 0) OR (p_org_information2 > 4712) THEN
1676 RAISE INVALID_YEAR;
1677 END IF;
1678
1679 IF instr(p_org_information2,'.')>0 THEN
1680 RAISE INVALID_DECIMAL;
1681 END IF;
1682
1683 OPEN tax_lc(p_org_information1,p_org_information2);
1684 FETCH tax_lc INTO l_count;
1685 CLOSE tax_lc;
1686 IF l_count > 0 THEN
1687 RAISE INVALID_TAX ;
1688 END IF ;
1689
1690 OPEN csr_global('SE_COMPANY_PERC_MAX');
1691 FETCH csr_global INTO l_global_value;
1692 CLOSE csr_global;
1693
1694
1695 IF p_org_information10>l_global_value THEN
1696 RAISE INVALID_VALUE ;
1697 END IF;
1698 l_global_value:=null;
1699
1700 OPEN csr_global('SE_EXT_COMPANY_PERC_MAX');
1701 FETCH csr_global INTO l_global_value;
1702 CLOSE csr_global;
1703
1704 IF p_org_information12>l_global_value THEN
1705 RAISE INVALID_VALUE ;
1706 END IF;
1707
1708 ELSIF p_org_info_type_code='SE_ABSENCE_CATEGORY_LIMIT' THEN
1709 OPEN absence_cat_limit(p_org_information1);
1710 FETCH absence_cat_limit INTO l_count;
1711 CLOSE absence_cat_limit;
1712 IF l_count>0 THEN
1713 RAISE INVALID_CATEGORY;
1714 END IF;
1715 ELSIF p_org_info_type_code='SE_HOLIDAY_YEAR_DEFN' THEN
1716 IF p_org_information5 ='Y' THEN
1717 OPEN c_legal;
1718 FETCH c_legal INTO l_prev_holiday_start,l_prev_holiday_end,
1719 l_prev_Accounting_start,l_prev_Accounting_end;
1720 CLOSE c_legal;
1721
1722 IF p_org_information1<>l_prev_holiday_start OR p_org_information2<> l_prev_holiday_end
1723 OR p_org_information3<>l_prev_Accounting_start OR p_org_information4<> l_prev_Accounting_end THEN
1724 Raise INVALID_UPDATE;
1725 END IF;
1726
1727 END IF;
1728 ELSIF p_org_info_type_code='SE_INSURANCE_NUMBER' THEN
1729 IF length(p_org_information1) <7 THEN
1730 RAISE INVALID_INSURANCE_NUMBER;
1731 END IF;
1732 IF instr(p_org_information1,'.')>0 THEN
1733 RAISE INVALID_INSURANCE_DECIMAL;
1734 END IF;
1735
1736 ELSIF p_org_info_type_code='SE_SOE_ELEMENT_ADD_DETAILS' THEN
1737 IF p_org_information5 ='I'
1738 THEN
1739 IF p_org_information7 IS NULL
1740 or p_org_information8 is NULL
1741 or p_org_information9 IS NULL
1742 or p_org_information10 IS NULL
1743 THEN
1744 Raise ENTER_ALL;
1745 END IF;
1746 END IF;
1747 IF p_org_information4='O'
1748 THEN
1749 IF p_org_information6 is null
1750 THEN
1751 RAISE ENTER_GROUP_BY;
1752 END IF;
1753 END IF;
1754 ELSIF p_org_info_type_code= 'SE_STD_WORK_DETAILS' THEN
1755 OPEN csr_year;
1756 FETCH csr_year INTO l_count;
1757 CLOSE csr_year;
1758 IF l_count > 0 THEN
1759 RAISE UNIQUE_YEAR;
1760 END IF;
1761
1762 END IF ;
1763 END IF;
1764 EXCEPTION
1765
1766 WHEN ENTER_GROUP_BY THEN
1767 fnd_message.set_name('PER','HR_377202_SE_MANDATORY_MSG');
1768 l_token := hr_general.decode_lookup('SE_FORM_LABELS','GBP');
1769 fnd_message.set_token('NAME',l_token, translate => true );
1770 hr_utility.raise_error;
1771
1772 WHEN ENTER_ALL THEN
1773 fnd_message.set_name('PER','HR_377202_SE_MANDATORY_MSG');
1774 l_token := hr_general.decode_lookup('SE_FORM_LABELS','UNIT') || ','||
1775 hr_general.decode_lookup('SE_FORM_LABELS','UP') || ','||
1776 hr_general.decode_lookup('SE_FORM_LABELS','AMT') || ','||
1777 hr_general.decode_lookup('SE_FORM_LABELS','GBU');
1778 fnd_message.set_token('NAME',l_token, translate => true );
1779 hr_utility.raise_error;
1780 WHEN UNIQUE_AST_NUMBER THEN
1781
1782 fnd_message.set_name('PER', 'HR_377206_SE_UNIQUE_AST_NUMBER');
1783 hr_utility.raise_error;
1784
1785 WHEN UNIQUE_ORG_NUMBER THEN
1786 fnd_message.set_name('PER', 'HR_377214_SE_UNIQUE_ORG_NUMBER');
1787 hr_utility.raise_error;
1788
1789
1790 WHEN UNIQUE_MAIN_LOCAL_UNIT THEN
1791 fnd_message.set_name('PER', 'HR_377208_SE_MAIN_LOCAL_UNIT');
1792 hr_utility.raise_error;
1793
1794 WHEN UNIQUE_LOCAL_UNITS THEN
1795 fnd_message.set_name('PER', 'HR_377210_SE_UNIQUE_LOCAL_UNIT');
1796 hr_utility.raise_error;
1797 WHEN UNIQUE_LE_AP
1798 THEN
1799 fnd_message.set_name('PAY', 'HR_377227_SE_LE_UNIQ_MILE_RATE');
1800 hr_utility.raise_error;
1801
1802 WHEN INVALID_DATE
1803 THEN
1804 fnd_message.set_name('PAY', 'HR_377228_SE_VALID_DATE');
1805 hr_utility.raise_error;
1806 WHEN INVALID_TAX
1807 THEN
1808 fnd_message.set_name('PAY', 'HR_377234_SE_TAX_DECL');
1809 hr_utility.raise_error;
1810
1811 WHEN INVALID_YEAR
1812 THEN
1813 fnd_message.set_name('PAY', 'HR_377236_SE_INVALID_YEAR');
1814 hr_utility.raise_error;
1815
1816 WHEN INVALID_DECIMAL
1817 THEN
1818 fnd_message.set_name('PAY', 'HR_377237_SE_INVALID_DECIMAL');
1819 hr_utility.raise_error;
1820 WHEN INVALID_CATEGORY
1821 THEN
1822 fnd_message.set_name('PAY', 'HR_377240_SE_INVALID_CATEGORY');
1823 hr_utility.raise_error;
1824 WHEN INVALID_UPDATE
1825 THEN
1826 fnd_message.set_name('PAY', 'HR_377241_SE_INVALID_UPDATE');
1827 hr_utility.raise_error;
1828 WHEN INVALID_INSURANCE_NUMBER
1829 THEN
1830 fnd_message.set_name('PAY', 'HR_377248_SE_INVALID_INSURANCE');
1831 hr_utility.raise_error;
1832 WHEN INVALID_VALUE
1833 THEN
1834 fnd_message.set_name('PAY', 'HR_377249_SE_INVALID_VALUE');
1835 fnd_message.set_token('X',to_char(l_global_value));
1836 hr_utility.raise_error;
1837 WHEN INVALID_INSURANCE_DECIMAL
1838 THEN
1839 fnd_message.set_name('PAY', 'HR_377250_SE_INVALID_INS_DECIM');
1840 hr_utility.raise_error;
1841 WHEN UNIQUE_YEAR
1842 THEN
1843 fnd_message.set_name('PER', 'HR_377270_SE_STD_WORK_YEAR');
1844 hr_utility.raise_error;
1845 WHEN INVALID_MEMBERSHIP_NUMBER
1846 THEN
1847 fnd_message.set_name('PAY', 'HR_377263_SE_INVALID_MEMBER');
1848 hr_utility.raise_error;
1849 WHEN INVALID_MEMBERSHIP_DECIMAL
1850 THEN
1851 fnd_message.set_name('PAY', 'HR_377264_SE_INVALID_MEM_DECIM');
1852 hr_utility.raise_error;
1853 WHEN INVALID_WORK_NUMBER
1854 THEN
1855 fnd_message.set_name('PAY', 'HR_377265_SE_INVALID_WORK');
1856 hr_utility.raise_error;
1857 WHEN INVALID_WORK_DECIMAL
1858 THEN
1859 fnd_message.set_name('PAY', 'HR_377266_SE_INVALID_WOR_DECIM');
1860 hr_utility.raise_error;
1861 WHEN INVALID_ASSOCIATION_NUMBER
1862 THEN
1863 fnd_message.set_name('PAY', 'HR_377267_SE_INVALID_ASSOCIAT');
1864 hr_utility.raise_error;
1865 WHEN INVALID_ASSOCIATION_DECIMAL
1866 THEN
1867 fnd_message.set_name('PAY', 'HR_377268_SE_INVALID_ASS_DECIM');
1868 hr_utility.raise_error;
1869 WHEN INVALID_AGREEMENT_NUMBER
1870 THEN
1871 fnd_message.set_name('PAY', 'HR_377261_SE_AGREEMENT_CODE');
1872 hr_utility.raise_error;
1873 WHEN INVALID_AGREEMENT_DECIMAL
1874 THEN
1875 fnd_message.set_name('PAY', 'HR_377269_SE_INVALID_AGR_DECIM');
1876 hr_utility.raise_error;
1877 WHEN INVALID_AGREEMENT_NUM
1878 THEN
1879 fnd_message.set_name('PAY', 'HR_377271_SE_AGREEMENT_NUMBER');
1880 hr_utility.raise_error;
1881
1882
1883
1884 END validate_update_org_inf;
1885
1886 --- End Of validate_update_org_inf
1887
1888
1889
1890 PROCEDURE VALIDATE_NUMBER
1891 (p_number IN VARCHAR2
1892 ,p_token IN VARCHAR2
1893 ,p_message IN VARCHAR2 DEFAULT NULL
1894 ) IS
1895
1896 BEGIN
1897 IF p_number IS NOT NULL
1898 THEN
1899 IF instr(p_number,'.') <> '0'
1900 THEN
1901 IF p_token IS NOT NULL
1902 THEN
1903 fnd_message.set_name('PER', 'HR_377207_SE_INVALID_FORMAT');
1904 fnd_message.set_token('NAME',p_token, translate => true );
1905 hr_utility.raise_error;
1906 ELSE
1907 fnd_message.set_name('PER', p_message);
1908 hr_utility.raise_error;
1909 END IF ;
1910
1911 END IF;
1912 END IF;
1913
1914 END VALIDATE_NUMBER;
1915
1916 ------------------inserted code
1917 -- Validation While creating the Classifications for Organization
1918
1919 PROCEDURE CREATE_ORG_CLASS_VALIDATE
1920 (P_ORGANIZATION_ID IN NUMBER
1921 ,P_ORG_INFORMATION1 IN VARCHAR2
1922 )IS
1923
1924 l_internal_external_flag hr_organization_units.INTERNAL_EXTERNAL_FLAG%type;
1925 INT_EXT_EXCEPTION exception;
1926
1927 CURSOR get_int_or_ext_flag
1928 IS
1929 SELECT INTERNAL_EXTERNAL_FLAG
1930 FROM HR_all_ORGANIZATION_UNITS
1931 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
1932
1933 BEGIN
1934 --
1935 -- Added for GSI Bug 5472781
1936 --
1937 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
1938 --
1939 OPEN get_int_or_ext_flag;
1940 FETCH get_int_or_ext_flag INTO l_internal_external_flag;
1941 CLOSE get_int_or_ext_flag;
1942
1943 IF l_internal_external_flag = 'INT'
1944 THEN
1945 IF P_ORG_INFORMATION1 = 'SE_SOC_SEC_OFFICE'
1946 THEN
1947 RAISE INT_EXT_EXCEPTION;
1948 END IF;
1949 END IF ;
1950 END IF;
1951 EXCEPTION
1952 WHEN INT_EXT_EXCEPTION
1953 THEN
1954 fnd_message.set_name('PER', 'HR_377211_SE_SOC_SEC_OFFICE');
1955 hr_utility.raise_error;
1956 END CREATE_ORG_CLASS_VALIDATE;
1957 ---
1958
1959
1960 PROCEDURE CREATE_ASG_VALIDATE
1961 ( p_scl_segment5 IN VARCHAR2 DEFAULT NULL
1962 ,p_scl_segment6 IN VARCHAR2 DEFAULT NULL
1963 ) is
1964
1965 VALID_NOTIFY_DATE EXCEPTION ;
1966
1967 BEGIN
1968 --
1969 -- Added for GSI Bug 5472781
1970 --
1971 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
1972 --
1973
1974 IF (p_scl_segment5 IS NOT NULL AND p_scl_segment5 <> hr_api.g_varchar2)
1975 AND (p_scl_segment6 IS NOT NULL AND p_scl_segment6 <> hr_api.g_varchar2) THEN
1976 IF fnd_date.canonical_to_date(p_scl_segment5) >= fnd_date.canonical_to_date(p_scl_segment6) THEN
1977 RAISE VALID_NOTIFY_DATE ;
1978 END IF;
1979
1980 END IF;
1981
1982 END IF;
1983 EXCEPTION
1984 WHEN VALID_NOTIFY_DATE
1985 THEN
1986 fnd_message.set_name('PER', 'HR_377212_SE_VALID_NOTIFY_DATE');
1987 hr_utility.raise_error;
1988
1989 END CREATE_ASG_VALIDATE;
1990
1991 -- End OF Create_Asg_validate
1992
1993 PROCEDURE UPDATE_ASG_VALIDATE
1994 (
1995 p_segment5 IN VARCHAR2
1996 ,p_segment6 IN VARCHAR2
1997 ) IS
1998
1999 VALID_NOTIFY_DATE EXCEPTION ;
2000
2001 BEGIN
2002 --
2003 -- Added for GSI Bug 5472781
2004 --
2005 IF hr_utility.chk_product_install('Oracle Human Resources', 'SE') THEN
2006 --
2007
2008 IF (p_segment5 IS NOT NULL AND p_segment5 <> hr_api.g_varchar2)
2009 AND (p_segment6 IS NOT NULL AND p_segment6 <> hr_api.g_varchar2) THEN
2010 IF fnd_date.canonical_to_date(p_segment5) >= fnd_date.canonical_to_date(p_segment6) THEN
2011 RAISE VALID_NOTIFY_DATE ;
2012 END IF;
2013
2014 END IF;
2015 --
2016 END IF;
2017
2018 EXCEPTION
2019 WHEN VALID_NOTIFY_DATE THEN
2020 fnd_message.set_name('PER', 'HR_377212_SE_VALID_NOTIFY_DATE');
2021 hr_utility.raise_error;
2022
2023 END UPDATE_ASG_VALIDATE ;
2024
2025 -- End Of UPDATE_ASG_VALIDATE
2026
2027 END hr_se_validate_pkg;
2028
[Home] [Help]