[Home] [Help]
PACKAGE BODY: APPS.HR_ORGANIZATION_UNITS_PKG
Source
1 PACKAGE BODY HR_ORGANIZATION_UNITS_PKG as
2 /* $Header: peoru01t.pkb 120.0 2005/05/31 12:20:37 appldev noship $ */
3 --------------------------------------------------------------------------------
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_business_group_id number(15); -- For validating translation;
6 g_legislation_code varchar2(150); -- For validating translation;
7 --------------------------------------------------------------------------------
8 PROCEDURE chk_repbody_seat_numbers
9 (p_organization_id IN hr_organization_units.organization_id%TYPE
10 ,p_org_information_context IN hr_organization_information.org_information_context%TYPE
11 ,p_org_information6 IN hr_organization_information.org_information6%TYPE
12 ,p_org_information2 IN hr_organization_information.org_information2%TYPE
13 ,p_rowid IN VARCHAR2) IS
14 --
15 CURSOR repbody_max_seats IS
16 SELECT hoi.org_information6
17 FROM hr_organization_information hoi
18 WHERE hoi.organization_id = p_organization_id
19 AND hoi.org_information_context = 'Representative Body';
20 --
21 CURSOR constituency_total_seats1 IS
22 SELECT sum(hoi.org_information2)
23 FROM hr_organization_information hoi
24 WHERE hoi.organization_id = p_organization_id
25 AND hoi.org_information_context = 'RepBody_Constituencies'
26 AND rowid <> p_rowid;
27 --
28 CURSOR constituency_total_seats2 IS
29 SELECT sum(hoi.org_information2)
30 FROM hr_organization_information hoi
31 WHERE hoi.organization_id = p_organization_id
32 AND hoi.org_information_context = 'RepBody_Constituencies';
33 --
34 l_proc VARCHAR2(72) := g_package||'chk_repbody_seat_numbers';
35 l_repbody_max_seats NUMBER :=NULL;
36 l_constituency_total_seats NUMBER :=0;
37 --
38 BEGIN
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 IF p_org_information_context = 'Representative Body' Then
43 --
44 l_repbody_max_seats := p_org_information6;
45 --
46 ELSE
47 --
48 OPEN repbody_max_seats;
49 FETCH repbody_max_seats INTO l_repbody_max_seats;
50 CLOSE repbody_max_seats;
51 --
52 END IF;
53 --
54 hr_utility.set_location(l_proc,20);
55 --
56 IF l_repbody_max_seats IS NOT NULL THEN
57 --
58 IF p_rowid is NULL THEN
59 --
60 OPEN constituency_total_seats2;
61 FETCH constituency_total_seats2 into l_constituency_total_seats;
62 CLOSE constituency_total_seats2;
63 --
64 ELSE
65 --
66 OPEN constituency_total_seats1;
67 FETCH constituency_total_seats1 into l_constituency_total_seats;
68 CLOSE constituency_total_seats1;
69 --
70 END IF;
71 --
72 hr_utility.set_location(l_proc,30);
73 --
74 l_constituency_total_seats := NVL(l_constituency_total_seats,0) + NVL(p_org_information2,0);
75 --
76 IF l_constituency_total_seats > l_repbody_max_seats THEN
77 --
78 hr_utility.set_message(800,'HR_289048_CON_INV_SEAT_NUM');
79 hr_utility.raise_error;
80 --
81 END IF;
82 --
83 END IF;
84 --
85 hr_utility.set_location('Leaving:'|| l_proc, 10);
86 --
87 EXCEPTION
88 WHEN OTHERS THEN
89 RAISE;
90 --
91 END chk_repbody_seat_numbers;
92 --
93 FUNCTION Is_Org_A_Node
94 (p_search_org_id IN hr_organization_units.organization_id%TYPE
95 ,p_organization_structure_id IN per_org_structure_versions_v.organization_structure_id%TYPE)
96 RETURN CHAR IS
97 --
98 Cursor c_get_structure_version Is
99 Select posvv.org_structure_version_id version_id
100 From per_organization_structures_v posv,
101 per_org_structure_versions_v posvv
102 Where posvv.organization_structure_id = posv.organization_structure_id
103 And posv.organization_structure_id = p_organization_structure_id;
104 --
105 Cursor c_orgs_in_hierarchy
106 (p_version_id IN per_org_structure_versions_v.organization_structure_id%TYPE) IS
107 select posev.organization_id_parent org_id
108 from per_org_structure_elements_v posev
109 where posev.org_Structure_version_id = p_version_id
110 UNION
111 select posev.organization_id_child org_id
112 from per_org_structure_elements_v posev
113 where posev.org_Structure_version_id = p_version_id;
114 --
115 v_org_in_hierarchy BOOLEAN := FALSE;
116 v_users_starting_node VARCHAR2(240) := NULL;
117 v_return_message VARCHAR2(5);
118 --
119 v_version_id per_org_structure_versions_v.organization_structure_id%TYPE := NULL;
120 --
121 l_proc VARCHAR2(72) := g_package||'Is_Org_A_Node';
122 --
123 BEGIN
124 --
125 hr_utility.set_location('Entering:'|| l_proc, 10);
126 --
127 OPEN c_get_structure_version;
128 FETCH c_get_structure_version INTO v_version_id;
129 CLOSE c_get_structure_version;
130 --
131 hr_utility.set_location(l_proc,20);
132 --
133 FOR c_rec IN c_orgs_in_hierarchy(v_version_id) LOOP
134 --
135 IF c_rec.org_id = p_search_org_id THEN
136 --
137 v_org_in_hierarchy := TRUE;
138 --
139 END IF;
140 --
141 EXIT WHEN v_org_in_hierarchy;
142 --
143 END LOOP;
144 --
145 hr_utility.set_location(l_proc,30);
146 --
147 IF v_org_in_hierarchy THEN
148 --
149 v_return_message := 'TRUE';
150 --
151 hr_utility.set_location(l_proc,40);
152 --
153 ELSIF NOT v_org_in_hierarchy THEN
154 --
155 v_return_message := 'FALSE';
156 --
157 hr_utility.set_location(l_proc,50);
158 --
159 END IF;
160 --
161 hr_utility.set_location('Leaving'|| l_proc, 60);
162 --
163 RETURN(v_return_message);
164 --
165 END Is_Org_A_Node;
166 --
167 function exists_in_hierarchy(p_org_structure_version_id NUMBER
168 ,p_organization_id NUMBER) return varchar2 is
169 --
170 l_temp VARCHAR2(1) := 'N';
171 begin
172 select 'Y'
173 into l_temp
174 from sys.dual
175 where exists (select null
176 from per_org_structure_elements ose
177 where ose.org_structure_version_id =
178 p_org_structure_version_id
179 and (ose.organization_id_child = p_organization_id
180 or ose.organization_id_parent = p_organization_id));
181 --
182 return l_temp;
183 --
184 exception
185 when no_data_found then
186 return l_temp;
187 when others then
188 raise;
189 end;
190 --
191 function get_parent(p_organization_id NUMBER
192 ,p_org_structure_version_id NUMBER) return NUMBER is
193 --
194 l_parent_id NUMBER :=0;
195 begin
196 select ose.organization_id_parent
197 into l_parent_id
198 from per_org_structure_elements ose
199 where ose.org_structure_version_id = p_org_structure_version_id
200 and ose.organization_id_child = p_organization_id;
201 --
202 --
203 return l_parent_id;
204 exception
205 when no_data_found then
206 return l_parent_id;
207 when others then
208 raise;
209 end;
210
211 procedure form_post_query(p_exists_in_hierarchy in out nocopy VARCHAR2
212 ,p_view_all_orgs VARCHAR2
213 ,p_organization_id NUMBER
214 ,p_org_structure_version_id NUMBER
215 ,p_security_profile_id NUMBER
216 ,p_number_of_subordinates in out nocopy NUMBER) is
217 --
218 begin
219 p_exists_in_hierarchy:= exists_in_hierarchy(p_org_structure_version_id
220 ,p_organization_id);
221 if p_exists_in_hierarchy = 'Y' then
222 p_number_of_subordinates :=
223 per_org_structure_elements_pkg.get_subordinates(p_view_all_orgs
224 ,p_organization_id
225 ,p_org_structure_version_id
226 ,p_security_profile_id);
227 else
228 p_number_of_subordinates := 0;
229 end if;
230 end;
231
232 procedure check_gre(p_org_id NUMBER) is
233 l_dummy varchar2(1);
234 cursor test_loc(p_type VARCHAR2) is
235 select 'Y'
236 from hr_organization_information hoi
237 where hoi.org_INFORMATION_CONTEXT = 'CLASS'
238 and hoi.org_information1 = p_type
239 and hoi.org_information2 = 'Y' -- Bug 3456540
240 and hoi.organization_id = p_org_id;
241 begin
242 open test_loc('HR_LEGAL');
243 fetch test_loc into l_dummy;
244 if test_loc%FOUND then
245 close test_loc;
246 hr_utility.set_message(801,'HR_6612_ORG_LEGAL_NO_LOCATION');
247 hr_utility.raise_error;
248 end if;
249 close test_loc;
250 --
251 open test_loc('HR_ESTAB');
252 fetch test_loc into l_dummy;
253 if test_loc%FOUND then
254 close test_loc;
255 hr_utility.set_message(801,'HR_7342_ORG_RE_NO_LOC');
256 hr_utility.raise_error;
257 end if;
258 close test_loc;
259
260 end;
261 -- -----------------------------------------------------------------------------
262 -- Checks, if organization is also a business group, that another business group
263 -- with the same name does not already exist
264 -- -----------------------------------------------------------------------------
265 PROCEDURE validate_business_group_name
266 (p_organization_id IN NUMBER
267 ,p_name IN VARCHAR2
268 )
269 IS
270 --
271 CURSOR csr_business_group
272 (p_organization_id IN NUMBER
273 )
274 IS
275 SELECT 0
276 FROM hr_organization_information ori
277 WHERE ori.org_information_context = 'CLASS'
278 AND ori.org_information1 = 'HR_BG'
279 AND ori.org_information2 = 'Y'
280 AND ori.organization_id = p_organization_id;
281 l_business_group csr_business_group%ROWTYPE;
282 --
283 CURSOR csr_business_group_name
284 (p_organization_id IN NUMBER
285 ,p_name IN VARCHAR2
286 )
287 IS
288 SELECT 0
289 FROM hr_all_organization_units org
290 ,hr_all_organization_units_tl otl
291 ,hr_organization_information ori
292 WHERE ori.org_information_context = 'CLASS'
293 AND ori.org_information1 = 'HR_BG'
294 AND ori.org_information2 = 'Y'
295 AND ori.organization_id = org.organization_id
296 AND otl.name = p_name
297 AND otl.language = userenv('LANG')
298 AND otl.organization_id = org.organization_id
299 AND org.organization_id <> p_organization_id;
300 l_business_group_name csr_business_group_name%ROWTYPE;
301 --
302 l_is_business_group BOOLEAN := FALSE;
303 --
304 BEGIN
305 --
306 -- Determine if organization is business group
307 --
308 OPEN csr_business_group(p_organization_id);
309 FETCH csr_business_group INTO l_business_group;
310 l_is_business_group := csr_business_group%FOUND;
311 CLOSE csr_business_group;
312 --
313 -- Check business group name does not already exist elsewhere
314 --
315 IF (l_is_business_group) THEN
316 --
317 OPEN csr_business_group_name(p_organization_id,p_name);
318 FETCH csr_business_group_name INTO l_business_group_name;
319 IF (csr_business_group_name%FOUND) THEN
320 CLOSE csr_business_group_name;
321 fnd_message.set_name('PER','HR_6556_ALL_BUS_GROUP_EXISTS');
322 fnd_message.raise_error;
323 ELSE
324 CLOSE csr_business_group_name;
325 END IF;
326 END IF;
327 --
328 END validate_business_group_name;
329 --
330 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
331 X_Organization_Id IN OUT NOCOPY NUMBER,
332 X_Business_Group_Id NUMBER,
333 X_Cost_Allocation_Keyflex_Id NUMBER,
334 X_Location_Id NUMBER,
335 X_Soft_Coding_Keyflex_Id NUMBER,
336 X_Date_From DATE,
337 X_Name VARCHAR2,
338 X_Comments VARCHAR2,
339 X_Date_To DATE,
340 X_Internal_External_Flag VARCHAR2,
341 X_Internal_Address_Line VARCHAR2,
342 X_Type VARCHAR2,
343 X_Security_Profile_Id NUMBER,
344 X_View_All_Orgs VARCHAR2,
345 X_Attribute_Category VARCHAR2,
346 X_Attribute1 VARCHAR2,
347 X_Attribute2 VARCHAR2,
348 X_Attribute3 VARCHAR2,
349 X_Attribute4 VARCHAR2,
350 X_Attribute5 VARCHAR2,
351 X_Attribute6 VARCHAR2,
352 X_Attribute7 VARCHAR2,
353 X_Attribute8 VARCHAR2,
354 X_Attribute9 VARCHAR2,
355 X_Attribute10 VARCHAR2,
356 X_Attribute11 VARCHAR2,
357 X_Attribute12 VARCHAR2,
358 X_Attribute13 VARCHAR2,
359 X_Attribute14 VARCHAR2,
360 X_Attribute15 VARCHAR2,
361 X_Attribute16 VARCHAR2,
362 X_Attribute17 VARCHAR2,
363 X_Attribute18 VARCHAR2,
364 X_Attribute19 VARCHAR2,
365 X_Attribute20 VARCHAR2
366 ) IS
367 CURSOR C IS SELECT rowid FROM HR_ALL_ORGANIZATION_UNITS
368 WHERE organization_id = X_Organization_Id;
369
370 CURSOR C2 IS SELECT hr_organization_units_s.nextval FROM sys.dual;
371
372 BEGIN
373 --
374 validate_business_group_name
375 (p_organization_id => X_Organization_Id
376 ,p_name => X_Name
377 );
378 /*
379 ** Insert the main organization record into the HR_ORGANIZATION_UNITS
380 ** table.
381 */
382 if (X_Organization_Id is NULL) then
383 OPEN C2;
384 FETCH C2 INTO X_Organization_Id;
385 CLOSE C2;
386 end if;
387 INSERT INTO HR_ALL_ORGANIZATION_UNITS(
388 organization_id,
389 business_group_id,
390 cost_allocation_keyflex_id,
391 location_id,
392 soft_coding_keyflex_id,
393 date_from,
394 name,
395 comments,
396 date_to,
397 internal_external_flag,
398 internal_address_line,
399 type,
400 attribute_category,
401 attribute1,
402 attribute2,
403 attribute3,
404 attribute4,
405 attribute5,
406 attribute6,
407 attribute7,
408 attribute8,
409 attribute9,
410 attribute10,
411 attribute11,
412 attribute12,
413 attribute13,
414 attribute14,
415 attribute15,
416 attribute16,
417 attribute17,
418 attribute18,
419 attribute19,
420 attribute20
421 ) VALUES (
422 X_Organization_Id,
423 X_Business_Group_Id,
424 X_Cost_Allocation_Keyflex_Id,
425 X_Location_Id,
426 X_Soft_Coding_Keyflex_Id,
427 X_Date_From,
428 X_Name,
429 X_Comments,
430 X_Date_To,
431 X_Internal_External_Flag,
432 X_Internal_Address_Line,
433 X_Type,
434 X_Attribute_Category,
435 X_Attribute1,
436 X_Attribute2,
437 X_Attribute3,
438 X_Attribute4,
439 X_Attribute5,
440 X_Attribute6,
441 X_Attribute7,
442 X_Attribute8,
443 X_Attribute9,
444 X_Attribute10,
445 X_Attribute11,
446 X_Attribute12,
447 X_Attribute13,
448 X_Attribute14,
449 X_Attribute15,
450 X_Attribute16,
451 X_Attribute17,
452 X_Attribute18,
453 X_Attribute19,
454 X_Attribute20
455
456 );
457 --
458 insert into HR_ALL_ORGANIZATION_UNITS_TL (
459 -- BUSINESS_GROUP_ID,
460 ORGANIZATION_ID,
461 NAME,
462 LANGUAGE,
463 SOURCE_LANG
464 ) select
465 -- X_Business_Group_Id,
466 X_Organization_Id,
467 X_Name,
468 L.LANGUAGE_CODE,
469 userenv('LANG')
470 from FND_LANGUAGES L
471 where L.INSTALLED_FLAG in ('I', 'B')
472 and not exists
473 (select NULL
474 from HR_ALL_ORGANIZATION_UNITS_TL T
475 where
476 -- T.BUSINESS_GROUP_ID = X_Business_Group_Id
477 T.ORGANIZATION_ID = X_Organization_Id
478 and T.LANGUAGE = L.LANGUAGE_CODE);
479 --
480 OPEN C;
481 FETCH C INTO X_Rowid;
482 if (C%NOTFOUND) then
483 CLOSE C;
484 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
485 hr_utility.set_message_token('PROCEDURE','Insert_row');
486 hr_utility.set_message_token('STEP','1');
487 hr_utility.raise_error;
488 end if;
489 CLOSE C;
490
491 if X_View_All_Orgs <> 'Y' then
492 /*
493 ** Set up the secure user information into the PER_ORGANIZATION_LIST
494 ** table. This is so that the org is immediately visible.
495 */
496 hr_security.add_organization(X_Organization_Id,
497 X_Security_Profile_Id);
498 end if;
499
500 END Insert_Row;
501 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
502 X_Organization_Id NUMBER,
503 X_Business_Group_Id NUMBER,
504 X_Cost_Allocation_Keyflex_Id NUMBER,
505 X_Location_Id NUMBER,
506 X_Soft_Coding_Keyflex_Id NUMBER,
507 X_Date_From DATE,
508 X_Name VARCHAR2,
509 X_Comments VARCHAR2,
510 X_Date_To DATE,
511 X_Internal_External_Flag VARCHAR2,
512 X_Internal_Address_Line VARCHAR2,
513 X_Type VARCHAR2,
514 X_Attribute_Category VARCHAR2,
515 X_Attribute1 VARCHAR2,
516 X_Attribute2 VARCHAR2,
517 X_Attribute3 VARCHAR2,
518 X_Attribute4 VARCHAR2,
519 X_Attribute5 VARCHAR2,
520 X_Attribute6 VARCHAR2,
521 X_Attribute7 VARCHAR2,
522 X_Attribute8 VARCHAR2,
523 X_Attribute9 VARCHAR2,
524 X_Attribute10 VARCHAR2,
525 X_Attribute11 VARCHAR2,
526 X_Attribute12 VARCHAR2,
527 X_Attribute13 VARCHAR2,
528 X_Attribute14 VARCHAR2,
529 X_Attribute15 VARCHAR2,
530 X_Attribute16 VARCHAR2,
531 X_Attribute17 VARCHAR2,
532 X_Attribute18 VARCHAR2,
533 X_Attribute19 VARCHAR2,
534 X_Attribute20 VARCHAR2
535 ) IS
536
537 /* CURSOR C IS
538 SELECT *
539 FROM HR_ALL_ORGANIZATION_UNITS
540 WHERE rowid = X_Rowid
541 FOR UPDATE of Organization_Id NOWAIT;
542 */
543 -- cursor is changed to fix the bug 2145187 (second part MLS)
544 -- If condition also is changed to compare name_tl, rather than name
545 -- (Recinfo.name_tl = X_Name)
546 -- OR ( (Recinfo.name_tl IS NULL)
547 -- AND (X_Name IS NULL)))
548 CURSOR C IS
549 SELECT oru.*,orutl.name name_tl
550 FROM HR_ALL_ORGANIZATION_UNITS ORU,
551 HR_ALL_ORGANIZATION_UNITS_TL ORUTL
552 WHERE ORU.rowid = X_Rowid
553 AND ORU.organization_id = ORUTL.organization_id
554 AND ORUTL.language = userenv('LANG')
555 FOR UPDATE of ORU.Organization_Id NOWAIT;
556 Recinfo C%ROWTYPE;
557 --
558 cursor c1 is select
559 NAME,
560 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
561 from HR_ALL_ORGANIZATION_UNITS_TL
562 where
563 -- BUSINESS_GROUP_ID = X_Business_Group_Id
564 ORGANIZATION_ID = X_Organization_Id
565 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
566 for update of ORGANIZATION_ID nowait;
567 --
568 BEGIN
569 OPEN C;
570 FETCH C INTO Recinfo;
571 if (C%NOTFOUND) then
572 CLOSE C;
573 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
574 hr_utility.set_message_token('PROCEDURE','Lock_row');
575 hr_utility.set_message_token('STEP','1');
576 hr_utility.raise_error;
577 end if;
578 CLOSE C;
579 --
580 Recinfo.name_tl := rtrim(Recinfo.name_tl);
581 Recinfo.name := rtrim(Recinfo.name);
582 Recinfo.comments := rtrim(Recinfo.comments);
583 Recinfo.internal_external_flag := rtrim(Recinfo.internal_external_flag);
584 Recinfo.internal_address_line := rtrim(Recinfo.internal_address_line);
585 Recinfo.type := rtrim(Recinfo.type);
586 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
587 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
588 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
589 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
590 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
591 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
592 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
593 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
594 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
595 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
596 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
597 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
598 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
599 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
600 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
601 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
602 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
603 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
604 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
605 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
606 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
607 --
608 if (
609 ( (Recinfo.organization_id = X_Organization_Id)
610 OR ( (Recinfo.organization_id IS NULL)
611 AND (X_Organization_Id IS NULL)))
612 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
613 OR ( (Recinfo.business_group_id IS NULL)
614 AND (X_Business_Group_Id IS NULL)))
615 AND ( (Recinfo.cost_allocation_keyflex_id = X_Cost_Allocation_Keyflex_Id)
616 OR ( (Recinfo.cost_allocation_keyflex_id IS NULL)
617 AND (X_Cost_Allocation_Keyflex_Id IS NULL)))
618 AND ( (Recinfo.location_id = X_Location_Id)
619 OR ( (Recinfo.location_id IS NULL)
620 AND (X_Location_Id IS NULL)))
621 AND ( (Recinfo.soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id)
622 OR ( (Recinfo.soft_coding_keyflex_id IS NULL)
623 AND (X_Soft_Coding_Keyflex_Id IS NULL)))
624 AND ( (Recinfo.date_from = X_Date_From)
625 OR ( (Recinfo.date_from IS NULL)
626 AND (X_Date_From IS NULL)))
627 AND ( (Recinfo.name_tl = X_Name)
628 OR ( (Recinfo.name_tl IS NULL)
629 AND (X_Name IS NULL)))
630 AND ( (Recinfo.comments = X_Comments)
631 OR ( (Recinfo.comments IS NULL)
632 AND (X_Comments IS NULL)))
633 AND ( (Recinfo.date_to = X_Date_To)
634 OR ( (Recinfo.date_to IS NULL)
635 AND (X_Date_To IS NULL)))
636 AND ( (Recinfo.internal_external_flag = X_Internal_External_Flag)
637 OR ( (Recinfo.internal_external_flag IS NULL)
638 AND (X_Internal_External_Flag IS NULL)))
639 AND ( (Recinfo.internal_address_line = X_Internal_Address_Line)
640 OR ( (Recinfo.internal_address_line IS NULL)
641 AND (X_Internal_Address_Line IS NULL)))
642 AND ( (Recinfo.type = X_Type)
643 OR ( (Recinfo.type IS NULL)
644 AND (X_Type IS NULL)))
645 AND ( (Recinfo.attribute_category = X_Attribute_Category)
646 OR ( (Recinfo.attribute_category IS NULL)
647 AND (X_Attribute_Category IS NULL)))
648 AND ( (Recinfo.attribute1 = X_Attribute1)
649 OR ( (Recinfo.attribute1 IS NULL)
650 AND (X_Attribute1 IS NULL)))
651 AND ( (Recinfo.attribute2 = X_Attribute2)
652 OR ( (Recinfo.attribute2 IS NULL)
653 AND (X_Attribute2 IS NULL)))
654 AND ( (Recinfo.attribute3 = X_Attribute3)
655 OR ( (Recinfo.attribute3 IS NULL)
656 AND (X_Attribute3 IS NULL)))
657 AND ( (Recinfo.attribute4 = X_Attribute4)
658 OR ( (Recinfo.attribute4 IS NULL)
659 AND (X_Attribute4 IS NULL)))
660 AND ( (Recinfo.attribute5 = X_Attribute5)
661 OR ( (Recinfo.attribute5 IS NULL)
662 AND (X_Attribute5 IS NULL)))
663 AND ( (Recinfo.attribute6 = X_Attribute6)
664 OR ( (Recinfo.attribute6 IS NULL)
665 AND (X_Attribute6 IS NULL)))
666 AND ( (Recinfo.attribute7 = X_Attribute7)
667 OR ( (Recinfo.attribute7 IS NULL)
668 AND (X_Attribute7 IS NULL)))
669 AND ( (Recinfo.attribute8 = X_Attribute8)
670 OR ( (Recinfo.attribute8 IS NULL)
671 AND (X_Attribute8 IS NULL)))
672 AND ( (Recinfo.attribute9 = X_Attribute9)
673 OR ( (Recinfo.attribute9 IS NULL)
674 AND (X_Attribute9 IS NULL)))
675 AND ( (Recinfo.attribute10 = X_Attribute10)
676 OR ( (Recinfo.attribute10 IS NULL)
677 AND (X_Attribute10 IS NULL)))
678 AND ( (Recinfo.attribute11 = X_Attribute11)
679 OR ( (Recinfo.attribute11 IS NULL)
680 AND (X_Attribute11 IS NULL)))
681 AND ( (Recinfo.attribute12 = X_Attribute12)
682 OR ( (Recinfo.attribute12 IS NULL)
683 AND (X_Attribute12 IS NULL)))
684 AND ( (Recinfo.attribute13 = X_Attribute13)
685 OR ( (Recinfo.attribute13 IS NULL)
686 AND (X_Attribute13 IS NULL)))
687 AND ( (Recinfo.attribute14 = X_Attribute14)
688 OR ( (Recinfo.attribute14 IS NULL)
689 AND (X_Attribute14 IS NULL)))
690 AND ( (Recinfo.attribute15 = X_Attribute15)
691 OR ( (Recinfo.attribute15 IS NULL)
692 AND (X_Attribute15 IS NULL)))
693 AND ( (Recinfo.attribute16 = X_Attribute16)
694 OR ( (Recinfo.attribute16 IS NULL)
695 AND (X_Attribute16 IS NULL)))
696 AND ( (Recinfo.attribute17 = X_Attribute17)
697 OR ( (Recinfo.attribute17 IS NULL)
698 AND (X_Attribute17 IS NULL)))
699 AND ( (Recinfo.attribute18 = X_Attribute18)
700 OR ( (Recinfo.attribute18 IS NULL)
701 AND (X_Attribute18 IS NULL)))
702 AND ( (Recinfo.attribute19 = X_Attribute19)
703 OR ( (Recinfo.attribute19 IS NULL)
704 AND (X_Attribute19 IS NULL)))
705 AND ( (Recinfo.attribute20 = X_Attribute20)
706 OR ( (Recinfo.attribute20 IS NULL)
707 AND (X_Attribute20 IS NULL)))
708 ) then
709 --return;
710 null;
711 else
712 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
713 APP_EXCEPTION.RAISE_EXCEPTION;
714 end if;
715 --
716 for tlinfo in c1 loop
717 if (tlinfo.BASELANG = 'Y') then
718 if ( (tlinfo.NAME = X_NAME)
719 ) then
720 null;
721 else
722 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
723 app_exception.raise_exception;
724 end if;
725 end if;
726 end loop;
727 --
728 return;
729 --
730 END Lock_Row;
731
732 PROCEDURE Update_Row(X_Rowid VARCHAR2,
733 X_Organization_Id NUMBER,
734 X_Business_Group_Id NUMBER,
735 X_Cost_Allocation_Keyflex_Id NUMBER,
736 X_Location_Id NUMBER,
737 X_Soft_Coding_Keyflex_Id NUMBER,
738 X_Date_From DATE,
739 X_Name VARCHAR2,
740 X_Comments VARCHAR2,
741 X_Date_To DATE,
742 X_Internal_External_Flag VARCHAR2,
743 X_Internal_Address_Line VARCHAR2,
744 X_Type VARCHAR2,
745 X_Attribute_Category VARCHAR2,
746 X_Attribute1 VARCHAR2,
747 X_Attribute2 VARCHAR2,
748 X_Attribute3 VARCHAR2,
749 X_Attribute4 VARCHAR2,
750 X_Attribute5 VARCHAR2,
751 X_Attribute6 VARCHAR2,
752 X_Attribute7 VARCHAR2,
753 X_Attribute8 VARCHAR2,
754 X_Attribute9 VARCHAR2,
755 X_Attribute10 VARCHAR2,
756 X_Attribute11 VARCHAR2,
757 X_Attribute12 VARCHAR2,
758 X_Attribute13 VARCHAR2,
759 X_Attribute14 VARCHAR2,
760 X_Attribute15 VARCHAR2,
761 X_Attribute16 VARCHAR2,
762 X_Attribute17 VARCHAR2,
763 X_Attribute18 VARCHAR2,
764 X_Attribute19 VARCHAR2,
765 X_Attribute20 VARCHAR2
766 ) IS
767 BEGIN
768 --
769 validate_business_group_name
770 (p_organization_id => X_Organization_Id
771 ,p_name => X_Name
772 );
773 --
774 UPDATE HR_ALL_ORGANIZATION_UNITS
775 SET
776
777 organization_id = X_Organization_Id,
778 business_group_id = X_Business_Group_Id,
779 cost_allocation_keyflex_id = X_Cost_Allocation_Keyflex_Id,
780 location_id = X_Location_Id,
781 soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id,
782 date_from = X_Date_From,
783 name = X_Name,
784 comments = X_Comments,
785 date_to = X_Date_To,
786 internal_external_flag = X_Internal_External_Flag,
787 internal_address_line = X_Internal_Address_Line,
788 type = X_Type,
789 attribute_category = X_Attribute_Category,
790 attribute1 = X_Attribute1,
791 attribute2 = X_Attribute2,
792 attribute3 = X_Attribute3,
793 attribute4 = X_Attribute4,
794 attribute5 = X_Attribute5,
795 attribute6 = X_Attribute6,
796 attribute7 = X_Attribute7,
797 attribute8 = X_Attribute8,
798 attribute9 = X_Attribute9,
799 attribute10 = X_Attribute10,
800 attribute11 = X_Attribute11,
801 attribute12 = X_Attribute12,
802 attribute13 = X_Attribute13,
803 attribute14 = X_Attribute14,
804 attribute15 = X_Attribute15,
805 attribute16 = X_Attribute16,
806 attribute17 = X_Attribute17,
807 attribute18 = X_Attribute18,
808 attribute19 = X_Attribute19,
809 attribute20 = X_Attribute20
810 WHERE rowid = X_rowid;
811 -- VT 12/14/98 restored rowid usage to create New Business Groups.
812 -- WHERE BUSINESS_GROUP_ID = X_Business_Group_Id
813 -- AND ORGANIZATION_ID = X_Organization_Id;
814
815 if (SQL%NOTFOUND) then
816 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
817 hr_utility.set_message_token('PROCEDURE','Update_Row');
818 hr_utility.set_message_token('STEP','1');
819 hr_utility.raise_error;
820 end if;
821 --
822 update HR_ALL_ORGANIZATION_UNITS_TL set
823 NAME = X_Name,
824 SOURCE_LANG = userenv('LANG')
825 where
826 -- BUSINESS_GROUP_ID = X_Business_Group_Id
827 ORGANIZATION_ID = X_Organization_Id
828 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
829
830 if (sql%notfound) then
831 raise no_data_found;
832 end if;
833 --
834 END Update_Row;
835
836
837
838
839 FUNCTION get_org_class (X_Organization_Id NUMBER, X_Organization_Class VARCHAR2) return boolean is
840
841 cursor csr_org_class is
842 select '1'
843 from HR_ORGANIZATION_INFORMATION
844 where organization_id = X_Organization_Id
845 and org_information1 = X_Organization_Class
846 and org_information_context = 'CLASS';
847
848 l_org_class varchar2(150);
849
850 begin
851 open csr_org_class;
852 fetch csr_org_class into l_org_class;
853 if csr_org_class%found then
854 return(true);
855 else
856 return(false);
857 end if;
858 close csr_org_class;
859
860 end;
861
862 PROCEDURE Validate_delete (X_Organization_Id NUMBER,
863 X_Business_Group_Id Number) IS
864 cursor csr_employer is
865 select '1'
866 from per_collective_agreements_v
867 where employer_organization_id = X_Organization_Id;
868
869 cursor csr_barg_units is
870 select '1'
871 from per_collective_agreements_v
872 where bargaining_organization_id = X_Organization_Id;
873
874 cursor csr_leg_code is
875 select legislation_code
876 from per_business_groups
877 where business_group_id = X_Business_Group_id;
878
879 --
880 -- Cursor removed as part of fix for bug 1858597,
881 --
882 /*cursor csr_leg_pkg(p_pkg_name varchar2) is
883 select '1'
884 from user_source
885 where name = p_pkg_name
886 and rownum < 2;*/
887 --
888 -- Cursor added as part of fix for 1858597
889 --
890 cursor csr_leg_pkg(p_pkg_name varchar2) is
891 select '1'
892 from user_objects
893 where object_name = p_pkg_name
894 and object_type = 'PACKAGE';
895 --
896 l_dummy varchar2(1);
897 l_leg_code varchar2(150);
898 l_cursor number;
899 l_proc_call varchar2(500);
900 l_package_name varchar2(50);
901 l_procedure_name varchar2(50);
902
903 BEGIN
904 --
905 if get_org_class(X_Organization_Id,'EMPLOYER') then
906 --
907 open csr_employer;
908 fetch csr_employer into l_dummy;
909 --
910 if csr_employer%found then
911 --
912 -- Close Cursor added a part of fix for bug 1858597
913 --
914 close csr_employer;
915 --
916 hr_utility.set_message(800,'PER_52852_ORG_DEL_CAGR');
917 hr_utility.raise_error;
918 --
919 end if;
920 --
921 close csr_employer;
922 --
923 end if;
924 --
925 if get_org_class(X_Organization_Id,'BARGAINING_UNIT') then
926 --
927 open csr_barg_units;
928 fetch csr_barg_units into l_dummy;
929 --
930 if csr_barg_units%found then
931 --
932 -- Close Cursor added a part of fix for bug 1858597
933 --
934 close csr_barg_units;
935 hr_utility.set_message(800,'PER_52852_ORG_DEL_CAGR');
936 hr_utility.raise_error;
937 --
938 end if;
939 --
940 close csr_barg_units;
941 --
942 end if;
943 --
944 -- Check the leg code for the business_group
945 --
946 open csr_leg_code;
947 fetch csr_leg_code into l_leg_code;
948 --
949 if csr_leg_code%found then
950 --
951 -- If one exists then we must check whether there exists a legislation
952 -- specific Validate_Delete procedure. This should be named in the format
953 -- PER_XX_VALIDATE_DELETE_PKG.VALIDATE_DELETE
954 -- If it does exist then construct an anonymous PL/SQL block to call
955 -- the procedure, passing the ORG_ID, otherwise do nothing.
956 --
957
958 l_package_name := 'PER_'||l_leg_code||'_VALIDATE_DELETE_PKG';
959 l_procedure_name := 'VALIDATE_DELETE';
960 --
961 -- Close Cursor added a part of fix for bug 1858597
962 --
963 close csr_leg_code;
964 --
965 -- Check package exists
966 --
967 open csr_leg_pkg(l_package_name);
968 fetch csr_leg_pkg into l_dummy;
969 --
970 if csr_leg_pkg%found then
971 --
972 -- Close Cursor added a part of fix for bug 1858597
973 --
974 close csr_leg_pkg;
975 --
976 -- Added as part of fix for bug 1858597
977 --
978 EXECUTE IMMEDIATE 'BEGIN '||
979 l_package_name||'.'||
980 l_procedure_name||
981 '(:X_ORGANIZATION_ID); END;'
982 USING X_Organization_Id;
983 --
984 -- Section commented out as part of fix for bug 1858597
985 --
986 /*l_cursor := dbms_sql.open_cursor;
987 --
988 -- construct an anonymous block with bind variable
989 --
990 l_proc_call := 'BEGIN '||
991 l_package_name||'.'||
992 l_procedure_name||
993 '(:X_ORGANIZATION_ID); END;';
994 --
995 dbms_sql.parse(l_cursor, l_proc_call, dbms_sql.v7);
996 --
997 -- Bind the Org Id into the procedure call
998 --
999 dbms_sql.bind_variable(l_cursor, 'X_ORGANIZATION_ID', X_Organization_id);
1000 --
1001 -- Execute the block
1002 --
1003 l_dummy := dbms_sql.execute(l_cursor);*/
1004 --
1005 end if;
1006 --
1007 end if;
1008 --
1009 END Validate_delete;
1010
1011
1012 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
1013 X_Business_Group_Id NUMBER,
1014 X_Organization_Id NUMBER,
1015 X_View_All_Orgs VARCHAR2) IS
1016 BEGIN
1017 --
1018 -- Delete the Organization from PER_ORGANIZATION_LIST.
1019 --
1020 Validate_delete(X_Organization_Id => X_Organization_Id,
1021 X_Business_Group_Id => X_Business_Group_Id);
1022
1023 --
1024 delete from HR_ALL_ORGANIZATION_UNITS_TL
1025 where
1026 -- BUSINESS_GROUP_ID = X_Business_Group_Id
1027 ORGANIZATION_ID = X_Organization_Id;
1028
1029 if (sql%notfound) then
1030 raise no_data_found;
1031 end if;
1032
1033 --
1034 --
1035 -- Delete the Organization from PER_ORGANIZATION_LIST.
1036 --
1037 if X_View_All_Orgs <> 'Y' then
1038 hr_security.delete_org_from_list(X_Organization_Id);
1039 end if;
1040 --
1041 DELETE FROM HR_ALL_ORGANIZATION_UNITS
1042 WHERE BUSINESS_GROUP_ID = X_Business_Group_Id
1043 AND ORGANIZATION_ID = X_Organization_Id;
1044 --
1045 if (SQL%NOTFOUND) then
1046 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1047 hr_utility.set_message_token('PROCEDURE','Delete_Row');
1048 hr_utility.set_message_token('STEP','1');
1049 hr_utility.raise_error;
1050 end if;
1051 END Delete_Row;
1052 --
1053 PROCEDURE zoom_forms(X_destination IN VARCHAR2
1054 ,X_ORGANIZATION_ID IN NUMBER
1055 ,X_SOB_ID IN OUT NOCOPY NUMBER
1056 ,X_ORG_CODE IN OUT NOCOPY VARCHAR2
1057 ,X_CHART_OF_ACCOUNTS IN OUT NOCOPY NUMBER) IS
1058 --
1059 l_sql_text VARCHAR2(2000);
1060 l_sql_cursor NUMBER;
1061 l_rows_fetched NUMBER;
1062 l_out_int number;
1063 l_out_vc CHAR(30);
1064 l_col_error NUMBER;
1065 l_act_length number :=3;
1066 begin
1067 if X_destination in ('INV_ORGANIZATION_PARAMETERS'
1068 ,'RCV_ORGANIZATION_PARAMETERS'
1069 ,'MRP_ORGANIZATION_PARAMETERS'
1070 ,'WIP_ORGANIZATION_PARAMETERS')
1071 then
1072 l_sql_text := 'select hoi.ORG_INFORMATION1 '
1073 ||'from hr_organization_information hoi '
1074 ||'where hoi.organization_id = '||to_char(X_ORGANIZATION_ID)||' '
1075 ||'and hoi.org_information_context = ''Accounting Information''';
1076 --
1077 -- Open Cursor for Processing Sql statment.
1078 --
1079 l_sql_cursor := dbms_sql.open_cursor;
1080 --
1081 -- Parse SQL statement.
1082 -- uses 1 as NATIVE DATABASE
1083 dbms_sql.parse(l_sql_cursor, l_sql_text, 1);
1084 --
1085 -- Map the local variables to each returned Column
1086 --
1087 dbms_sql.define_column(l_sql_cursor, 1,X_SOB_ID);
1088 --
1089 -- Execute the SQL statement.
1090 --
1091 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
1092 --
1093 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
1094 then
1095 --
1096 -- Extract the select list for the current row into local variables.
1097 --
1098 dbms_sql.column_value(l_sql_cursor, 1,X_SOB_ID);
1099 --
1100 else
1101 fnd_message.set_name('INV','INV_ACCOUNT_BEFORE_INV');
1102 fnd_message.raise_error;
1103 end if;
1104 dbms_sql.close_cursor(l_sql_cursor);
1105 if X_DESTINATION in ('RCV_ORGANIZATION_PARAMETERS'
1106 ,'MRP_ORGANIZATION_PARAMETERS'
1107 ,'WIP_ORGANIZATION_PARAMETERS')
1108 then
1109 l_sql_text := 'select mp.organization_code '
1110 ||'from mtl_parameters mp '
1111 ||'where mp.organization_id = '|| to_char(X_ORGANIZATION_ID);
1112 if X_DESTINATION = 'WIP_ORGANIZATION_PARAMETERS'
1113 -- add extra WIP business rule check.
1114 then
1115 l_sql_text := l_sql_text
1116 ||' and mp.cost_organization_id = mp.organization_id';
1117 end if;
1118 --
1119 -- Open Cursor for Processing Sql statment.
1120 --
1121 l_sql_cursor := dbms_sql.open_cursor;
1122 --
1123 -- Parse SQL statement.
1124 -- uses 1 as NATIVE DATABASE
1125 dbms_sql.parse(l_sql_cursor, l_sql_text, 1);
1126 --
1127 -- Map the local variables to each returned Column
1128 --
1129 dbms_sql.define_column(l_sql_cursor, 1,X_ORG_CODE,l_act_length);
1130 --
1131 -- Execute the SQL statement.
1132 --
1133 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
1134 --
1135 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
1136 then
1137 --
1138 -- Extract the select list for the current row into local variables.
1139 --
1140 dbms_sql.column_value(l_sql_cursor, 1,X_ORG_CODE);
1141 --
1142 else
1143 if X_DESTINATION = 'WIP_ORGANIZATION_PARAMETERS' then
1144 fnd_message.set_name('WIP','WIP_PARAMETERS_NOT_ALLOWED');
1145 else
1146 fnd_message.set_name('INV','INV_ACCOUNT_BEFORE_INV');
1147 end if;
1148 fnd_message.raise_error;
1149 end if;
1150 dbms_sql.close_cursor(l_sql_cursor);
1151 --
1152 if X_DESTINATION = 'RCV_ORGANIZATION_PARAMETERS' then
1153 l_sql_text := 'select gsb.chart_of_accounts_id '
1154 ||' from gl_sets_of_books gsb '
1155 ||' where gsb.set_of_books_id = '||to_char(X_SOB_ID);
1156 --
1157 -- Open Cursor for Processing Sql statment.
1158 --
1159 l_sql_cursor := dbms_sql.open_cursor;
1160 --
1161 -- Parse SQL statement.
1162 -- uses 1 as NATIVE DATABASE
1163 dbms_sql.parse(l_sql_cursor, l_sql_text, 1);
1164 --
1165 -- Map the local variables to each returned Column
1166 --
1167 dbms_sql.define_column(l_sql_cursor, 1,X_CHART_OF_ACCOUNTS);
1168 --
1169 -- Execute the SQL statement.
1170 --
1171 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
1172 --
1173 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
1174 then
1175 --
1176 -- Extract the select list for the current row into local variables.
1177 --
1178 dbms_sql.column_value(l_sql_cursor, 1,X_CHART_OF_ACCOUNTS);
1179 --
1180 else
1181 fnd_message.set_name('INV','INV_ACCOUNT_BEFORE_INV');
1182 fnd_message.raise_error;
1183 end if;
1184 dbms_sql.close_cursor(l_sql_cursor);
1185 end if;
1186 end if;
1187 end if;
1188 end zoom_forms;
1189 --
1190 procedure ADD_LANGUAGE
1191 is
1192 begin
1193 delete from HR_ALL_ORGANIZATION_UNITS_TL T
1194 where not exists
1195 (select NULL
1196 from HR_ALL_ORGANIZATION_UNITS B
1197 where B.ORGANIZATION_ID = T.ORGANIZATION_ID
1198 );
1199
1200 update HR_ALL_ORGANIZATION_UNITS_TL T set (
1201 NAME
1202 ) = (select
1203 B.NAME
1204 from HR_ALL_ORGANIZATION_UNITS_TL B
1205 where B.ORGANIZATION_ID = T.ORGANIZATION_ID
1206 and B.LANGUAGE = T.SOURCE_LANG)
1207 where (
1208 T.ORGANIZATION_ID,
1209 T.LANGUAGE
1210 ) in (select
1211 SUBT.ORGANIZATION_ID,
1212 SUBT.LANGUAGE
1213 from HR_ALL_ORGANIZATION_UNITS_TL SUBB, HR_ALL_ORGANIZATION_UNITS_TL SUBT
1214 where SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
1215 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1216 and (SUBB.NAME <> SUBT.NAME
1217 ));
1218
1219 insert into HR_ALL_ORGANIZATION_UNITS_TL (
1220 ORGANIZATION_ID,
1221 NAME,
1222 LAST_UPDATE_DATE,
1223 LAST_UPDATED_BY,
1224 LAST_UPDATE_LOGIN,
1225 CREATED_BY,
1226 CREATION_DATE,
1227 LANGUAGE,
1228 SOURCE_LANG
1229 ) select
1230 B.ORGANIZATION_ID,
1231 B.NAME,
1232 B.LAST_UPDATE_DATE,
1233 B.LAST_UPDATED_BY,
1234 B.LAST_UPDATE_LOGIN,
1235 B.CREATED_BY,
1236 B.CREATION_DATE,
1237 L.LANGUAGE_CODE,
1238 B.SOURCE_LANG
1239 from HR_ALL_ORGANIZATION_UNITS_TL B, FND_LANGUAGES L
1240 where L.INSTALLED_FLAG in ('I', 'B')
1241 and B.LANGUAGE = userenv('LANG')
1242 and not exists
1243 (select NULL
1244 from HR_ALL_ORGANIZATION_UNITS_TL T
1245 where T.ORGANIZATION_ID = B.ORGANIZATION_ID
1246 and T.LANGUAGE = L.LANGUAGE_CODE);
1247 end ADD_LANGUAGE;
1248 --
1249 --------------------------------------------------------------------------------
1250 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
1251 p_legislation_code IN VARCHAR2) IS
1252 BEGIN
1253 g_business_group_id := p_business_group_id;
1254 g_legislation_code := p_legislation_code;
1255 END;
1256 --------------------------------------------------------------------------------
1257 --------------------------------------------------------------------------------
1258 procedure validate_translation(organization_id IN NUMBER,
1259 language IN VARCHAR2,
1260 name IN VARCHAR2,
1261 p_business_group_id IN NUMBER DEFAULT NULL)
1262 IS
1263 /*
1264
1265 This procedure fails if an organization translation is already present in
1266 the table for a given language. Otherwise, no action is performed. It is
1267 used to ensure uniqueness of translated organization names.
1268
1269 */
1270
1271 --
1272 -- This cursor implements the validation we require,
1273 -- and expects that the various package globals are set before
1274 -- the call to this procedure is made. This is done from the
1275 -- user-named trigger 'TRANSLATIONS' in the form
1276 --
1277 cursor c_translation(p_language IN VARCHAR2,
1278 p_org_name IN VARCHAR2,
1279 p_org_id IN NUMBER,
1280 p_bus_grp_id IN NUMBER)
1281 IS
1282 SELECT 1
1283 FROM hr_all_organization_units_tl orgt,
1284 hr_all_organization_units org
1285 WHERE upper(orgt.name)=upper(p_org_name)
1286 AND orgt.organization_id = org.organization_id
1287 AND orgt.language = p_language
1288 AND (org.organization_id <> p_org_id OR p_org_id IS NULL)
1289 AND (org.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
1290 ;
1291
1292 l_package_name VARCHAR2(80) := 'HR_ORGANIZATION_UNITS_PKG.VALIDATE_TRANSLATION';
1293 l_business_group_id NUMBER := nvl(p_business_group_id, g_business_group_id);
1294
1295 BEGIN
1296 hr_utility.set_location (l_package_name,10);
1297 OPEN c_translation(language, name,organization_id,
1298 l_business_group_id);
1299 hr_utility.set_location (l_package_name,50);
1300 FETCH c_translation INTO g_dummy;
1301
1302 IF c_translation%NOTFOUND THEN
1303 hr_utility.set_location (l_package_name,60);
1304 CLOSE c_translation;
1305 ELSE
1306 hr_utility.set_location (l_package_name,70);
1307 CLOSE c_translation;
1308 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
1309 fnd_message.raise_error;
1310 END IF;
1311 hr_utility.set_location ('Leaving:'||l_package_name,80);
1312 END validate_translation;
1313 --------------------------------------------------------------------------------
1314 END HR_ORGANIZATION_UNITS_PKG;