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