[Home] [Help]
PACKAGE BODY: APPS.HR_ORG_INFORMATION_PKG
Source
1 PACKAGE BODY HR_ORG_INFORMATION_PKG as
2 /* $Header: peori01t.pkb 120.2.12010000.5 2008/08/06 09:19:06 ubhat ship $ */
3 PROCEDURE Validate_SIRET (X_SIRET VARCHAR2) IS
4
5 l_rgeflg varchar2(10);
6 l_output varchar2(150);
7 l_siret varchar2(150);
8 l_total number;
9 l_total_impair number;
10 l_total_pair number;
11 l_total_1 number;
12 j number;
13
14 BEGIN
15
16 if (length(X_SIRET) <> 14) then
17 hr_utility.set_message(800,'PER_74846_ORG_INV_SIRET');
18 hr_utility.raise_error;
19 end if;
20
21 l_siret := X_SIRET;
22 begin
23 hr_chkfmt.checkformat(value => l_siret
24 ,format => 'I'
25 ,output => l_output
26 ,minimum => NULL
27 ,maximum => NULL
28 ,nullok => 'Y'
29 ,rgeflg => l_rgeflg
30 ,curcode => NULL);
31 exception
32 when HR_UTILITY.HR_ERROR then
33 hr_utility.set_message(800,'PER_74846_ORG_INV_SIRET');
34 hr_utility.raise_error;
35 end;
36 l_total := 0;
37 l_total_impair := 0;
38 l_total_pair := 0;
39
40 for i in 1..14 loop
41 j:= i + 0.1;
42 if i in (1,3,5,7,9,11,13) then
43 l_total_1 := (to_number(substr(X_SIRET,j,1))*2);
44 if (length(l_total_1) = 2) then
45 l_total_impair := substr(l_total_1,1.1,1) + substr(l_total_1,2.1,1) + l_total_impair;
46 else
47 l_total_impair := l_total_1 + l_total_impair;
48 end if;
49 else
50 l_total_pair := to_number(substr(X_SIRET,j,1)) + l_total_pair;
51 end if;
52 end loop;
53
54 l_total := l_total_pair + l_total_impair;
55
56 if mod (l_total,10) <> 0 then
57 hr_utility.set_message(800,'PER_74846_ORG_INV_SIRET');
58 hr_utility.raise_error;
59 end if;
60
61
62 END Validate_SIRET;
63
64 PROCEDURE Validate_SIREN (X_SIREN VARCHAR2) IS
65
66 l_rgeflg varchar2(10);
67 l_output varchar2(150);
68 l_siren varchar2(150);
69 l_total number;
70 l_total_impair number;
71 l_total_pair number;
72 l_total_1 number;
73 j number;
74
75 BEGIN
76
77 if (length(X_SIREN) <> 9) then
78 hr_utility.set_message(800,'PER_74847_ORG_INV_SIREN');
79 hr_utility.raise_error;
80 end if;
81
82 l_siren := X_SIREN;
83 begin
84 hr_chkfmt.checkformat(value => l_siren
85 ,format => 'I'
86 ,output => l_output
87 ,minimum => NULL
88 ,maximum => NULL
89 ,nullok => 'Y'
90 ,rgeflg => l_rgeflg
91 ,curcode => NULL);
92 exception
93 when HR_UTILITY.HR_ERROR then
94 hr_utility.set_message(800,'PER_74847_ORG_INV_SIREN');
95 hr_utility.raise_error;
96 end;
97
98 l_total := 0;
99 l_total_impair := 0;
100 l_total_pair := 0;
101
102 for i in 1..9 loop
103 j:= i + 0.1;
104 if i in (2,4,6,8) then
105 l_total_1 := (to_number(substr(X_SIREN,j,1))*2);
106 if (length(l_total_1) = 2) then
107 l_total_pair := substr(l_total_1,1.1,1) + substr(l_total_1,2.1,1) + l_total_pair;
108 else
109 l_total_pair := l_total_1 + l_total_pair;
110 end if;
111 else
112 l_total_impair := to_number(substr(X_SIREN,j,1)) + l_total_impair;
113 end if;
114 end loop;
115
116 l_total := l_total_pair + l_total_impair;
117
118 if mod (l_total,10) <> 0 then
119 hr_utility.set_message(800,'PER_74847_ORG_INV_SIREN');
120 hr_utility.raise_error;
121 end if;
122
123
124 END Validate_SIREN;
125
126 /* Bug 6809830 check_duplicate_tax_rules RLN 03/08 */
127
128 PROCEDURE check_duplicate_tax_rules
129 (p_organization_id IN NUMBER
130 ,p_org_information_context IN VARCHAR2
131 ,p_org_information1 IN VARCHAR2
132
133 )
134 IS
135 --
136 CURSOR csr_tax_rules
137 (p_organization_id IN NUMBER
138 ,p_org_information_context IN VARCHAR2
139 ,p_org_information1 IN VARCHAR2
140
141 )
142 IS
143 select 'Y'
144 from hr_organization_information
145 where ORG_INFORMATION_CONTEXT like p_org_information_context
146 and org_information1 = p_org_information1
147 and organization_id = p_organization_id
148 ;
149
150 l_found varchar2(1);
151 --
152
153 BEGIN
154 --
155 --
156 --
157 l_found := 'N';
158 --
159 -- Check tax rule does not exist elsewhere
160 --
161 OPEN csr_tax_rules(p_organization_id
162 ,p_org_information_context
163 ,p_org_information1
164
165 );
166 FETCH csr_tax_rules INTO l_found;
167 IF (csr_tax_rules%FOUND) THEN
168 --CLOSE csr_business_group_name;
169 fnd_message.set_name ('PAY','PAY_75262_US_TAX_RULES_EXIST');
170 fnd_message.raise_error;
171 ELSE
172 CLOSE csr_tax_rules;
173 END IF;
174 --
175
176 --
177 END check_duplicate_tax_rules;
178
179 PROCEDURE validate_business_group_name
180 (p_organization_id IN NUMBER
181 ,p_org_information_context IN VARCHAR2
182 ,p_org_information1 IN VARCHAR2
183 ,p_org_information2 IN VARCHAR2
184 )
185 IS
186 --
187 CURSOR csr_business_group_name
188 (p_organization_id IN NUMBER
189 )
190 IS
191 SELECT 0
192 FROM hr_organization_units org
193 ,per_business_groups bgp
194 WHERE bgp.name = org.name
195 AND bgp.organization_id <> org.organization_id
196 AND org.organization_id = p_organization_id;
197 l_business_group_name csr_business_group_name%ROWTYPE;
198 --
199 BEGIN
200 --
201 -- Determine if defining a business group
202 --
203 IF ( p_org_information_context = 'CLASS'
204 AND p_org_information1 = 'HR_BG'
205 AND p_org_information2 = 'Y') THEN
206 --
207 -- Check business group name does not exist elsewhere
208 --
209 OPEN csr_business_group_name(p_organization_id);
210 FETCH csr_business_group_name INTO l_business_group_name;
211 IF (csr_business_group_name%FOUND) THEN
212 CLOSE csr_business_group_name;
213 fnd_message.set_name('PER','HR_6556_ALL_BUS_GROUP_EXISTS');
214 fnd_message.raise_error;
215 ELSE
216 CLOSE csr_business_group_name;
217 END IF;
218 --
219 END IF;
220 --
221 END validate_business_group_name;
222
223 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
224 X_Org_Information_Id IN OUT NOCOPY NUMBER,
225 X_Org_Information_Context VARCHAR2,
226 X_Organization_Id NUMBER,
227 X_Org_Information1 VARCHAR2,
228 X_Org_Information10 VARCHAR2,
229 X_Org_Information11 VARCHAR2,
230 X_Org_Information12 VARCHAR2,
231 X_Org_Information13 VARCHAR2,
232 X_Org_Information14 VARCHAR2,
233 X_Org_Information15 VARCHAR2,
234 X_Org_Information16 VARCHAR2,
235 X_Org_Information17 VARCHAR2,
236 X_Org_Information18 VARCHAR2,
237 X_Org_Information19 VARCHAR2,
238 X_Org_Information2 VARCHAR2,
239 X_Org_Information20 VARCHAR2,
240 X_Org_Information3 VARCHAR2,
241 X_Org_Information4 VARCHAR2,
242 X_Org_Information5 VARCHAR2,
243 X_Org_Information6 VARCHAR2,
244 X_Org_Information7 VARCHAR2,
245 X_Org_Information8 VARCHAR2,
246 X_Org_Information9 VARCHAR2,
247 X_Attribute_Category VARCHAR2,
248 X_Attribute1 VARCHAR2,
249 X_Attribute2 VARCHAR2,
250 X_Attribute3 VARCHAR2,
251 X_Attribute4 VARCHAR2,
252 X_Attribute5 VARCHAR2,
253 X_Attribute6 VARCHAR2,
254 X_Attribute7 VARCHAR2,
255 X_Attribute8 VARCHAR2,
256 X_Attribute9 VARCHAR2,
257 X_Attribute10 VARCHAR2,
258 X_Attribute11 VARCHAR2,
259 X_Attribute12 VARCHAR2,
260 X_Attribute13 VARCHAR2,
261 X_Attribute14 VARCHAR2,
262 X_Attribute15 VARCHAR2,
263 X_Attribute16 VARCHAR2,
264 X_Attribute17 VARCHAR2,
265 X_Attribute18 VARCHAR2,
266 X_Attribute19 VARCHAR2,
267 X_Attribute20 VARCHAR2
268 ) IS
269
270 CURSOR C (p_org_info_id number)IS SELECT rowid FROM HR_ORGANIZATION_INFORMATION
271 WHERE org_information_id = p_org_info_id;
272
273 CURSOR C2 IS SELECT hr_organization_information_s.nextval FROM sys.dual;
274 l_rowid varchar2(255);
275 l_org_information_id hr_organization_information.organization_id%type;
276 BEGIN
277
278 if (X_Org_Information_Id is NULL) then
279 OPEN C2;
280 FETCH C2 INTO l_Org_Information_Id;
281 CLOSE C2;
282 x_org_information_id := l_org_information_id;
283 end if;
284 hr_utility.set_location('HR_ORGANIZATION_INFORMATION',1);
285
286 if X_org_information_context = 'FR_ESTAB_INFO' then
287 Validate_SIRET(X_SIRET => X_org_information2);
288 elsif X_org_information_context = 'FR_ESTAB_PREV_INFO' then
289 Validate_SIRET(X_SIRET => X_org_information1);
290 end if;
291
292 if X_org_information_context = 'FR_COMP_INFO' then
293 Validate_SIREN(X_SIREN => X_org_information1);
294 elsif X_org_information_context = 'FR_COMP_PREV_INFO' then
295 Validate_SIREN(X_SIREN => X_org_information1);
296 end if;
297
298 validate_business_group_name
299 (p_organization_id => X_Organization_Id
300 ,p_org_information_context => X_Org_Information_Context
301 ,p_org_information1 => X_Org_Information1
302 ,p_org_information2 => X_Org_Information2
303 );
304 if X_Org_Information_Context in ( 'State Tax Rules', 'State Tax Rules 2', 'Local Tax Rules')
305 Then
306 check_duplicate_tax_rules
307 (p_organization_id => X_Organization_Id
308 ,p_org_information_context => X_Org_Information_Context
309 ,p_org_information1 => X_Org_Information1
310 );
311 End if ;
312
313 INSERT INTO HR_ORGANIZATION_INFORMATION(
314 org_information_id,
315 org_information_context,
316 organization_id,
317 org_information1,
318 org_information10,
319 org_information11,
320 org_information12,
321 org_information13,
322 org_information14,
323 org_information15,
324 org_information16,
325 org_information17,
326 org_information18,
327 org_information19,
328 org_information2,
329 org_information20,
330 org_information3,
331 org_information4,
332 org_information5,
333 org_information6,
334 org_information7,
335 org_information8,
336 org_information9,
337 attribute_category,
338 attribute1,
339 attribute2,
340 attribute3,
341 attribute4,
342 attribute5,
343 attribute6,
344 attribute7,
345 attribute8,
346 attribute9,
347 attribute10,
348 attribute11,
349 attribute12,
350 attribute13,
351 attribute14,
352 attribute15,
353 attribute16,
354 attribute17,
355 attribute18,
356 attribute19,
357 attribute20
358 ) VALUES (
359 X_Org_Information_Id,
360 X_Org_Information_Context,
361 X_Organization_Id,
362 X_Org_Information1,
363 X_Org_Information10,
364 X_Org_Information11,
365 X_Org_Information12,
366 X_Org_Information13,
367 X_Org_Information14,
368 X_Org_Information15,
369 X_Org_Information16,
370 X_Org_Information17,
371 X_Org_Information18,
372 X_Org_Information19,
373 X_Org_Information2,
374 X_Org_Information20,
375 X_Org_Information3,
376 X_Org_Information4,
377 X_Org_Information5,
378 X_Org_Information6,
379 X_Org_Information7,
380 X_Org_Information8,
381 X_Org_Information9,
382 X_Attribute_Category,
383 X_Attribute1,
384 X_Attribute2,
385 X_Attribute3,
386 X_Attribute4,
387 X_Attribute5,
388 X_Attribute6,
389 X_Attribute7,
390 X_Attribute8,
391 X_Attribute9,
392 X_Attribute10,
393 X_Attribute11,
394 X_Attribute12,
395 X_Attribute13,
396 X_Attribute14,
397 X_Attribute15,
398 X_Attribute16,
399 X_Attribute17,
400 X_Attribute18,
401 X_Attribute19,
402 X_Attribute20
403
404 );
405 hr_utility.set_location('HR_ORGANIZATION_INFORMATION',2);
406 OPEN C (l_org_information_id);
407 FETCH C INTO l_Rowid;
408 if (C%NOTFOUND) then
409 CLOSE C;
410 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
411 hr_utility.set_message_token('PROCEDURE','Insert_Row');
412 hr_utility.set_message_token('STEP','1');
413 hr_utility.raise_error;
414 end if;
415 CLOSE C;
416 x_rowid := l_rowId;
417 END Insert_Row;
418
419 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
420
421 X_Org_Information_Id NUMBER,
422 X_Org_Information_Context VARCHAR2,
423 X_Organization_Id NUMBER,
424 X_Org_Information1 VARCHAR2,
425 X_Org_Information10 VARCHAR2,
426 X_Org_Information11 VARCHAR2,
427 X_Org_Information12 VARCHAR2,
428 X_Org_Information13 VARCHAR2,
429 X_Org_Information14 VARCHAR2,
430 X_Org_Information15 VARCHAR2,
431 X_Org_Information16 VARCHAR2,
432 X_Org_Information17 VARCHAR2,
433 X_Org_Information18 VARCHAR2,
434 X_Org_Information19 VARCHAR2,
435 X_Org_Information2 VARCHAR2,
436 X_Org_Information20 VARCHAR2,
437 X_Org_Information3 VARCHAR2,
438 X_Org_Information4 VARCHAR2,
439 X_Org_Information5 VARCHAR2,
440 X_Org_Information6 VARCHAR2,
441 X_Org_Information7 VARCHAR2,
442 X_Org_Information8 VARCHAR2,
443 X_Org_Information9 VARCHAR2,
444 X_Attribute_Category VARCHAR2,
445 X_Attribute1 VARCHAR2,
446 X_Attribute2 VARCHAR2,
447 X_Attribute3 VARCHAR2,
448 X_Attribute4 VARCHAR2,
449 X_Attribute5 VARCHAR2,
450 X_Attribute6 VARCHAR2,
451 X_Attribute7 VARCHAR2,
452 X_Attribute8 VARCHAR2,
453 X_Attribute9 VARCHAR2,
454 X_Attribute10 VARCHAR2,
455 X_Attribute11 VARCHAR2,
456 X_Attribute12 VARCHAR2,
457 X_Attribute13 VARCHAR2,
458 X_Attribute14 VARCHAR2,
459 X_Attribute15 VARCHAR2,
460 X_Attribute16 VARCHAR2,
461 X_Attribute17 VARCHAR2,
462 X_Attribute18 VARCHAR2,
463 X_Attribute19 VARCHAR2,
464 X_Attribute20 VARCHAR2
465 ) IS
466 CURSOR C IS
467 SELECT *
468 FROM HR_ORGANIZATION_INFORMATION
469 WHERE rowid = X_Rowid
470 FOR UPDATE of Org_Information_Id NOWAIT;
471 Recinfo C%ROWTYPE;
472 BEGIN
473 OPEN C;
474 FETCH C INTO Recinfo;
475 if (C%NOTFOUND) then
476 CLOSE C;
477 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
478 hr_utility.set_message_token('PROCEDURE','Lock_Row');
479 hr_utility.set_message_token('STEP','1');
480 hr_utility.raise_error;
481 end if;
482 CLOSE C;
483 --
484 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
485 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
486 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
487 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
488 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
489 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
490 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
491 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
492 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
493 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
494 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
495 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
496 Recinfo.org_information_context := rtrim(Recinfo.org_information_context);
497 Recinfo.org_information1 := rtrim(Recinfo.org_information1);
498 Recinfo.org_information10 := rtrim(Recinfo.org_information10);
499 Recinfo.org_information11 := rtrim(Recinfo.org_information11);
500 Recinfo.org_information12 := rtrim(Recinfo.org_information12);
501 Recinfo.org_information13 := rtrim(Recinfo.org_information13);
502 Recinfo.org_information14 := rtrim(Recinfo.org_information14);
503 Recinfo.org_information15 := rtrim(Recinfo.org_information15);
504 Recinfo.org_information16 := rtrim(Recinfo.org_information16);
505 Recinfo.org_information17 := rtrim(Recinfo.org_information17);
506 Recinfo.org_information18 := rtrim(Recinfo.org_information18);
507 Recinfo.org_information19 := rtrim(Recinfo.org_information19);
508 Recinfo.org_information2 := rtrim(Recinfo.org_information2);
509 Recinfo.org_information20 := rtrim(Recinfo.org_information20);
510 Recinfo.org_information3 := rtrim(Recinfo.org_information3);
511 Recinfo.org_information4 := rtrim(Recinfo.org_information4);
512 Recinfo.org_information5 := rtrim(Recinfo.org_information5);
513 Recinfo.org_information6 := rtrim(Recinfo.org_information6);
514 Recinfo.org_information7 := rtrim(Recinfo.org_information7);
515 Recinfo.org_information8 := rtrim(Recinfo.org_information8);
516 Recinfo.org_information9 := rtrim(Recinfo.org_information9);
517 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
518 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
519 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
520 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
521 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
522 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
523 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
524 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
525 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
526 --
527 if (
528 ( (Recinfo.org_information_id = X_Org_Information_Id)
529 OR ( (Recinfo.org_information_id IS NULL)
530 AND (X_Org_Information_Id IS NULL)))
531 AND ( (Recinfo.org_information_context = X_Org_Information_Context)
532 OR ( (Recinfo.org_information_context IS NULL)
533 AND (X_Org_Information_Context IS NULL)))
534 AND ( (Recinfo.organization_id = X_Organization_Id)
535 OR ( (Recinfo.organization_id IS NULL)
536 AND (X_Organization_Id IS NULL)))
537 AND ( (Recinfo.org_information1 = X_Org_Information1)
538 OR ( (Recinfo.org_information1 IS NULL)
539 AND (X_Org_Information1 IS NULL)))
540 AND ( (Recinfo.org_information10 = X_Org_Information10)
541 OR ( (Recinfo.org_information10 IS NULL)
542 AND (X_Org_Information10 IS NULL)))
543 AND ( (Recinfo.org_information11 = X_Org_Information11)
544 OR ( (Recinfo.org_information11 IS NULL)
545 AND (X_Org_Information11 IS NULL)))
546 AND ( (Recinfo.org_information12 = X_Org_Information12)
547 OR ( (Recinfo.org_information12 IS NULL)
548 AND (X_Org_Information12 IS NULL)))
549 AND ( (Recinfo.org_information13 = X_Org_Information13)
550 OR ( (Recinfo.org_information13 IS NULL)
551 AND (X_Org_Information13 IS NULL)))
552 AND ( (Recinfo.org_information14 = X_Org_Information14)
553 OR ( (Recinfo.org_information14 IS NULL)
554 AND (X_Org_Information14 IS NULL)))
555 AND ( (Recinfo.org_information15 = X_Org_Information15)
556 OR ( (Recinfo.org_information15 IS NULL)
557 AND (X_Org_Information15 IS NULL)))
558 AND ( (Recinfo.org_information16 = X_Org_Information16)
559 OR ( (Recinfo.org_information16 IS NULL)
560 AND (X_Org_Information16 IS NULL)))
561 AND ( (Recinfo.org_information17 = X_Org_Information17)
562 OR ( (Recinfo.org_information17 IS NULL)
563 AND (X_Org_Information17 IS NULL)))
564 AND ( (Recinfo.org_information18 = X_Org_Information18)
565 OR ( (Recinfo.org_information18 IS NULL)
566 AND (X_Org_Information18 IS NULL)))
567 AND ( (Recinfo.org_information19 = X_Org_Information19)
568 OR ( (Recinfo.org_information19 IS NULL)
569 AND (X_Org_Information19 IS NULL)))
570 AND ( (Recinfo.org_information2 = X_Org_Information2)
571 OR ( (Recinfo.org_information2 IS NULL)
572 AND (X_Org_Information2 IS NULL)))
573 AND ( (Recinfo.org_information20 = X_Org_Information20)
574 OR ( (Recinfo.org_information20 IS NULL)
575 AND (X_Org_Information20 IS NULL)))
576 AND ( (Recinfo.org_information3 = X_Org_Information3)
577 OR ( (Recinfo.org_information3 IS NULL)
578 AND (X_Org_Information3 IS NULL)))
579 AND ( (Recinfo.org_information4 = X_Org_Information4)
580 OR ( (Recinfo.org_information4 IS NULL)
581 AND (X_Org_Information4 IS NULL)))
582 AND ( (Recinfo.org_information5 = X_Org_Information5)
583 OR ( (Recinfo.org_information5 IS NULL)
584 AND (X_Org_Information5 IS NULL)))
585 AND ( (Recinfo.org_information6 = X_Org_Information6)
586 OR ( (Recinfo.org_information6 IS NULL)
587 AND (X_Org_Information6 IS NULL)))
588 AND ( (Recinfo.org_information7 = X_Org_Information7)
589 OR ( (Recinfo.org_information7 IS NULL)
590 AND (X_Org_Information7 IS NULL)))
591 AND ( (Recinfo.org_information8 = X_Org_Information8)
592 OR ( (Recinfo.org_information8 IS NULL)
593 AND (X_Org_Information8 IS NULL)))
594 AND ( (Recinfo.org_information9 = X_Org_Information9)
595 OR ( (Recinfo.org_information9 IS NULL)
596 AND (X_Org_Information9 IS NULL)))
597 AND ( (Recinfo.attribute_category = X_Attribute_Category)
598 OR ( (Recinfo.attribute_category IS NULL)
599 AND (X_Attribute_Category IS NULL)))
600 AND ( (Recinfo.attribute1 = X_Attribute1)
601 OR ( (Recinfo.attribute1 IS NULL)
602 AND (X_Attribute1 IS NULL)))
603 AND ( (Recinfo.attribute2 = X_Attribute2)
604 OR ( (Recinfo.attribute2 IS NULL)
605 AND (X_Attribute2 IS NULL)))
606 AND ( (Recinfo.attribute3 = X_Attribute3)
607 OR ( (Recinfo.attribute3 IS NULL)
608 AND (X_Attribute3 IS NULL)))
609 AND ( (Recinfo.attribute4 = X_Attribute4)
610 OR ( (Recinfo.attribute4 IS NULL)
611 AND (X_Attribute4 IS NULL)))
612 AND ( (Recinfo.attribute5 = X_Attribute5)
613 OR ( (Recinfo.attribute5 IS NULL)
614 AND (X_Attribute5 IS NULL)))
615 AND ( (Recinfo.attribute6 = X_Attribute6)
616 OR ( (Recinfo.attribute6 IS NULL)
617 AND (X_Attribute6 IS NULL)))
618 AND ( (Recinfo.attribute7 = X_Attribute7)
619 OR ( (Recinfo.attribute7 IS NULL)
620 AND (X_Attribute7 IS NULL)))
621 AND ( (Recinfo.attribute8 = X_Attribute8)
622 OR ( (Recinfo.attribute8 IS NULL)
623 AND (X_Attribute8 IS NULL)))
624 AND ( (Recinfo.attribute9 = X_Attribute9)
625 OR ( (Recinfo.attribute9 IS NULL)
626 AND (X_Attribute9 IS NULL)))
627 AND ( (Recinfo.attribute10 = X_Attribute10)
628 OR ( (Recinfo.attribute10 IS NULL)
629 AND (X_Attribute10 IS NULL)))
630 AND ( (Recinfo.attribute11 = X_Attribute11)
631 OR ( (Recinfo.attribute11 IS NULL)
632 AND (X_Attribute11 IS NULL)))
633 AND ( (Recinfo.attribute12 = X_Attribute12)
634 OR ( (Recinfo.attribute12 IS NULL)
635 AND (X_Attribute12 IS NULL)))
636 AND ( (Recinfo.attribute13 = X_Attribute13)
637 OR ( (Recinfo.attribute13 IS NULL)
638 AND (X_Attribute13 IS NULL)))
639 AND ( (Recinfo.attribute14 = X_Attribute14)
640 OR ( (Recinfo.attribute14 IS NULL)
641 AND (X_Attribute14 IS NULL)))
642 AND ( (Recinfo.attribute15 = X_Attribute15)
643 OR ( (Recinfo.attribute15 IS NULL)
644 AND (X_Attribute15 IS NULL)))
645 AND ( (Recinfo.attribute16 = X_Attribute16)
646 OR ( (Recinfo.attribute16 IS NULL)
647 AND (X_Attribute16 IS NULL)))
648 AND ( (Recinfo.attribute17 = X_Attribute17)
649 OR ( (Recinfo.attribute17 IS NULL)
650 AND (X_Attribute17 IS NULL)))
651 AND ( (Recinfo.attribute18 = X_Attribute18)
652 OR ( (Recinfo.attribute18 IS NULL)
653 AND (X_Attribute18 IS NULL)))
654 AND ( (Recinfo.attribute19 = X_Attribute19)
655 OR ( (Recinfo.attribute19 IS NULL)
656 AND (X_Attribute19 IS NULL)))
657 AND ( (Recinfo.attribute20 = X_Attribute20)
658 OR ( (Recinfo.attribute20 IS NULL)
659 AND (X_Attribute20 IS NULL)))
660 ) then
661 return;
662 else
663 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
664 APP_EXCEPTION.RAISE_EXCEPTION;
665 end if;
666 END Lock_Row;
667
668 PROCEDURE Update_Row(X_Rowid VARCHAR2,
669 X_Org_Information_Id NUMBER,
670 X_Org_Information_Context VARCHAR2,
671 X_Organization_Id NUMBER,
672 X_Org_Information1 VARCHAR2,
673 X_Org_Information10 VARCHAR2,
674 X_Org_Information11 VARCHAR2,
675 X_Org_Information12 VARCHAR2,
676 X_Org_Information13 VARCHAR2,
677 X_Org_Information14 VARCHAR2,
678 X_Org_Information15 VARCHAR2,
679 X_Org_Information16 VARCHAR2,
680 X_Org_Information17 VARCHAR2,
681 X_Org_Information18 VARCHAR2,
682 X_Org_Information19 VARCHAR2,
683 X_Org_Information2 VARCHAR2,
684 X_Org_Information20 VARCHAR2,
685 X_Org_Information3 VARCHAR2,
686 X_Org_Information4 VARCHAR2,
687 X_Org_Information5 VARCHAR2,
688 X_Org_Information6 VARCHAR2,
689 X_Org_Information7 VARCHAR2,
690 X_Org_Information8 VARCHAR2,
691 X_Org_Information9 VARCHAR2,
692 X_Attribute_Category VARCHAR2,
693 X_Attribute1 VARCHAR2,
694 X_Attribute2 VARCHAR2,
695 X_Attribute3 VARCHAR2,
696 X_Attribute4 VARCHAR2,
697 X_Attribute5 VARCHAR2,
698 X_Attribute6 VARCHAR2,
699 X_Attribute7 VARCHAR2,
700 X_Attribute8 VARCHAR2,
701 X_Attribute9 VARCHAR2,
702 X_Attribute10 VARCHAR2,
703 X_Attribute11 VARCHAR2,
704 X_Attribute12 VARCHAR2,
705 X_Attribute13 VARCHAR2,
706 X_Attribute14 VARCHAR2,
707 X_Attribute15 VARCHAR2,
708 X_Attribute16 VARCHAR2,
709 X_Attribute17 VARCHAR2,
710 X_Attribute18 VARCHAR2,
711 X_Attribute19 VARCHAR2,
712 X_Attribute20 VARCHAR2
713 ) IS
714 --
715 -- declare local variables
716 --
717 l_dummy VARCHAR2(1);
718 l_state_code VARCHAR2(2);
719 l_carrier_id VARCHAR2(17);
720 --
721 -- declare cursors
722 --
723 CURSOR get_orig_values IS
724 select
725 org_information1,
726 org_information8
727 from
728 hr_organization_information
729 where
730 org_information_id = X_org_information_id;
731 --
732 CURSOR check_override IS
733 SELECT
734 'x'
735 FROM
736 pay_wc_rates wcr,
737 pay_wc_funds wcf
738 WHERE
739 wcf.carrier_id = l_carrier_id AND
740 wcf.state_code = l_state_code AND
741 wcr.fund_id = wcf.fund_id
742 AND EXISTS
743 ( SELECT 'code referenced in override'
744 FROM per_assignments_f a,
745 fnd_id_flex_structures_vl ifs,
746 hr_soft_coding_keyflex sck
747 WHERE sck.segment1 = to_char(X_organization_id) -- #1683897
748 AND segment8 = to_char(wcr.wc_code)
749 AND ifs.id_flex_structure_name = 'GREs and other data'
750 AND sck.id_flex_num = ifs.id_flex_num
751 AND a.assignment_type = 'E'
752 AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id );
753 --
754 BEGIN
755 --
756 -- US specific validation to check that if the structure being updated is
757 -- 'State Tax Rules' and the segment being updated is WC Carrier then
758 -- check that a WC rate for this carrier is not being referenced by
759 -- an assignment's 'WC Override Code' on the SCL 'GREs and other data'
760 --
761 hr_utility.set_location ('hr_org_information_pkg.update_row', 1);
762 --
763 IF (X_Org_Information_Context = 'State Tax Rules')
764 THEN
765 --
766 hr_utility.set_location ('hr_org_information_pkg.update_row', 2);
767 --
768 -- get original values
769 --
770 OPEN get_orig_values;
771 FETCH get_orig_values into l_state_code, l_carrier_id;
772 CLOSE get_orig_values;
773 --
774 -- check if values have changed
775 --
776 IF ((l_state_code <> X_org_information1) OR
777 (NVL(l_carrier_id, X_org_information8) <> X_org_information8) OR
778 X_org_information8 IS NULL)
779 THEN
780 hr_utility.set_location ('hr_org_information_pkg.update_row', 3);
781 OPEN check_override;
782 FETCH check_override into l_dummy;
783 IF check_override%FOUND
784 THEN
785 hr_utility.set_location ('hr_org_information_pkg.update_row', 4);
786 hr_utility.set_message(800,'HR_51039_ORG_WC_OVRRD_RATE_REF');
787 hr_utility.raise_error;
788 END IF;
789 CLOSE check_override;
790 hr_utility.set_location ('hr_org_information_pkg.update_row', 5);
791 END IF;
792 END IF; -- end US specific validation
793
794 if X_org_information_context = 'FR_ESTAB_INFO' then
795 Validate_SIRET(X_SIRET => X_org_information2);
796 elsif X_org_information_context = 'FR_ESTAB_PREV_INFO' then
797 Validate_SIRET(X_SIRET => X_org_information1);
798 end if;
799
800 if X_org_information_context = 'FR_COMP_INFO' then
801 Validate_SIREN(X_SIREN => X_org_information1);
802 elsif X_org_information_context = 'FR_COMP_PREV_INFO' then
803 Validate_SIREN(X_SIREN => X_org_information1);
804 end if;
805
806 validate_business_group_name
807 (p_organization_id => X_Organization_Id
808 ,p_org_information_context => X_Org_Information_Context
809 ,p_org_information1 => X_Org_Information1
810 ,p_org_information2 => X_Org_Information2
811 );
812
813 if X_Org_Information_Context in ( 'State Tax Rules', 'State Tax Rules 2', 'Local Tax Rules')
814 Then
815 check_duplicate_tax_rules
816 (p_organization_id => X_Organization_Id
817 ,p_org_information_context => X_Org_Information_Context
818 ,p_org_information1 => X_Org_Information1
819 );
820 End if ;
821
822 --
823 UPDATE HR_ORGANIZATION_INFORMATION
824 SET
825 org_information_id = X_Org_Information_Id,
826 org_information_context = X_Org_Information_Context,
827 organization_id = X_Organization_Id,
828 org_information1 = X_Org_Information1,
829 org_information10 = X_Org_Information10,
830 org_information11 = X_Org_Information11,
831 org_information12 = X_Org_Information12,
832 org_information13 = X_Org_Information13,
833 org_information14 = X_Org_Information14,
834 org_information15 = X_Org_Information15,
835 org_information16 = X_Org_Information16,
836 org_information17 = X_Org_Information17,
837 org_information18 = X_Org_Information18,
838 org_information19 = X_Org_Information19,
839 org_information2 = X_Org_Information2,
840 org_information20 = X_Org_Information20,
841 org_information3 = X_Org_Information3,
842 org_information4 = X_Org_Information4,
843 org_information5 = X_Org_Information5,
844 org_information6 = X_Org_Information6,
845 org_information7 = X_Org_Information7,
846 org_information8 = X_Org_Information8,
847 org_information9 = X_Org_Information9,
848 attribute_category = X_Attribute_Category,
849 attribute1 = X_Attribute1,
850 attribute2 = X_Attribute2,
851 attribute3 = X_Attribute3,
852 attribute4 = X_Attribute4,
853 attribute5 = X_Attribute5,
854 attribute6 = X_Attribute6,
855 attribute7 = X_Attribute7,
856 attribute8 = X_Attribute8,
857 attribute9 = X_Attribute9,
858 attribute10 = X_Attribute10,
859 attribute11 = X_Attribute11,
860 attribute12 = X_Attribute12,
861 attribute13 = X_Attribute13,
862 attribute14 = X_Attribute14,
863 attribute15 = X_Attribute15,
864 attribute16 = X_Attribute16,
865 attribute17 = X_Attribute17,
866 attribute18 = X_Attribute18,
867 attribute19 = X_Attribute19,
868 attribute20 = X_Attribute20
869 WHERE rowid = X_rowid;
870
871 if (SQL%NOTFOUND) then
872 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
873 hr_utility.set_message_token('PROCEDURE','Update_Row');
874 hr_utility.set_message_token('STEP','1');
875 hr_utility.raise_error;
876 end if;
877
878 END Update_Row;
879
880
881
882 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
883 BEGIN
884 DELETE FROM HR_ORGANIZATION_INFORMATION
885 WHERE rowid = X_Rowid;
886
887 if (SQL%NOTFOUND) then
888 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
889 hr_utility.set_message_token('PROCEDURE','Delete_Row');
890 hr_utility.set_message_token('STEP','1');
891 hr_utility.raise_error;
892 end if;
893 END Delete_Row;
894
895 END HR_ORG_INFORMATION_PKG;