[Home] [Help]
PACKAGE BODY: APPS.FF_GLOBALS_F_PKG
Source
1 PACKAGE BODY FF_GLOBALS_F_PKG as
2 /* $Header: ffglb01t.pkb 120.1.12000000.3 2007/03/08 14:48:22 ajeyam noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 ff_globals_f_pkg
11 Purpose
12 Supports the GLB block in the form FFWSDGLB (Define Globals).
13 Notes
14
15 History
16 11-Mar-94 J.S.Hobbs 40.0 Date created.
17 19-Apr-94 J.S.Hobbs 40.1 Added rtrim to Lock_Row.
18 31-Jan-95 J.S.Hobbs 40.3 Removed aol WHO columns.
19 06-Mar-97 J.Alloun 40.5 Changed all occurances of system.dual
20 to sys.dual for next release requirements.
21 12-Apr-05 Shisriva -- Version 115.1,115.2 for MLS of FF_GLOBALS_F.
22 05-May-05 Shisriva 115.3 Fixes for bug 4350976. Removed the Base
23 Parameters from insert_row procedure.
24 05-May-05 Shisriva 115.4 Fixes for bug 4350976. Changed defualting
25 of parameters in update_row and lock_row.
26 19-Aug-05 A.Rashid 115.5 Added validation and database item
27 generation code.
28 22-Jun-06 mseshadr 115.6 Added Load_Row procedure for lct support
29 08-Mar-07 ajeyam 115.7 Commented out the if condition to check
30 for max_updated_date > last_updated_date
31 on Load_Row procedure for bug 5921008.
32 ============================================================================*/
33 --
34 --For MLS-----------------------------------------------------------------------
35 g_dummy_number number (30);
36 g_business_group_id number(15); -- For validating translation.
37 g_legislation_code varchar2(150); -- For validating translation.
38 --------------------------------------------------------------------------------
39 -----------------------------------------------------------------------------
40 -- Name --
41 -- Insert_Row --
42 -- Purpose --
43 -- Table handler procedure that supports the insert of a global via the --
44 -- Define Global form. --
45 -- Arguments --
46 -- See below. --
47 -- Notes --
48 -- None. --
49 -----------------------------------------------------------------------------
50 --
51 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
52 X_Global_Id IN OUT NOCOPY NUMBER,
53 X_Effective_Start_Date DATE,
54 X_Effective_End_Date DATE,
55 X_Business_Group_Id NUMBER,
56 X_Legislation_Code VARCHAR2,
57 X_Data_Type VARCHAR2,
58 X_Global_Name IN OUT NOCOPY VARCHAR2,
59 X_Global_Description VARCHAR2,
60 X_Global_Value VARCHAR2) IS
61 --
62 CURSOR C IS SELECT rowid FROM ff_globals_f
63 WHERE global_id = X_Global_Id;
64 --
65 CURSOR C2 IS SELECT ff_globals_s.nextval FROM sys.dual;
66 --
67 BEGIN
68 --
69 -- Make sure global name is unique.
70 ffdict.validate_global(X_Global_Name,
71 X_Business_Group_Id,
72 X_Legislation_Code);
73 --
74 if (X_Global_Id is NULL) then
75 OPEN C2;
76 FETCH C2 INTO X_Global_Id;
77 CLOSE C2;
78 end if;
79 --
80 g_dml_status := TRUE;
81 --
82 INSERT INTO ff_globals_f
83 (global_id,
84 effective_start_date,
85 effective_end_date,
86 business_group_id,
87 legislation_code,
88 data_type,
89 global_name,
90 global_description,
91 global_value)
92 VALUES
93 (X_Global_Id,
94 X_Effective_Start_Date,
95 X_Effective_End_Date,
96 X_Business_Group_Id,
97 X_Legislation_Code,
98 X_Data_Type,
99 X_Global_Name,
100 X_Global_Description,
101 X_Global_Value);
102 --
103 -- insert into MLS table (TL)
104 --
105 --For MLS-----------------------------------------------------------------------
106 ff_fgt_ins.ins_tl(userenv('LANG'),X_GLOBAL_ID,
107 X_GLOBAL_NAME,X_GLOBAL_DESCRIPTION);
108 g_dml_status := FALSE;
109 --------------------------------------------------------------------------------
110 --
111 OPEN C;
112 FETCH C INTO X_Rowid;
113 if (C%NOTFOUND) then
114 CLOSE C;
115 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
116 hr_utility.set_message_token('PROCEDURE',
117 'ff_globals_f_pkg.insert_row');
118 hr_utility.set_message_token('STEP','1');
119 hr_utility.raise_error;
120 end if;
121 CLOSE C;
122 --
123 Exception
124 When Others then
125 g_dml_status := FALSE;
126 raise;
127 END Insert_Row;
128 --
129 --
130 -----------------------------------------------------------------------------
131 -- Name --
132 -- Lock_Row --
133 -- Purpose --
134 -- Table handler procedure that supports the insert , update and delete --
135 -- of a formula by applying a lock on a global in the Define Global form.--
136 -- Arguments --
137 -- See below. --
138 -- Notes --
139 -- None. --
140 -----------------------------------------------------------------------------
141 --
142 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
143 X_Global_Id NUMBER,
144 X_Effective_Start_Date DATE,
145 X_Effective_End_Date DATE,
146 X_Business_Group_Id NUMBER,
147 X_Legislation_Code VARCHAR2,
148 X_Data_Type VARCHAR2,
149 X_Global_Name VARCHAR2,
150 X_Global_Description VARCHAR2,
151 X_Global_Value VARCHAR2,
152 X_Base_Global_Name VARCHAR2 default NULL,
153 X_Base_Global_Description VARCHAR2 default NULL) IS
154 --
155 CURSOR C IS SELECT * FROM ff_globals_f
156 WHERE rowid = X_Rowid FOR UPDATE of Global_Id NOWAIT;
157 --
158 Recinfo C%ROWTYPE;
159 --
160 BEGIN
161 --
162 OPEN C;
163 FETCH C INTO Recinfo;
164 if (C%NOTFOUND) then
165 CLOSE C;
166 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
167 hr_utility.set_message_token('PROCEDURE',
168 'ff_globals_f_pkg.lock_row');
169 hr_utility.set_message_token('STEP','1');
170 hr_utility.raise_error;
171 end if;
172 CLOSE C;
173 --
174 -- Remove trailing spaces
175 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
176 Recinfo.data_type := rtrim(Recinfo.data_type);
177 Recinfo.global_name := rtrim(Recinfo.global_name);
178 Recinfo.global_description := rtrim(Recinfo.global_description);
179 Recinfo.global_value := rtrim(Recinfo.global_value);
180 --
181 if ( ( (Recinfo.global_id = X_Global_Id)
182 OR ( (Recinfo.global_id IS NULL)
183 AND (X_Global_Id IS NULL)))
184 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
185 OR ( (Recinfo.effective_start_date IS NULL)
186 AND (X_Effective_Start_Date IS NULL)))
187 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
188 OR ( (Recinfo.effective_end_date IS NULL)
189 AND (X_Effective_End_Date IS NULL)))
190 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
191 OR ( (Recinfo.business_group_id IS NULL)
192 AND (X_Business_Group_Id IS NULL)))
193 AND ( (Recinfo.legislation_code = X_Legislation_Code)
194 OR ( (Recinfo.legislation_code IS NULL)
195 AND (X_Legislation_Code IS NULL)))
196 AND ( (Recinfo.data_type = X_Data_Type)
197 OR ( (Recinfo.data_type IS NULL)
198 AND (X_Data_Type IS NULL)))
199 AND ( (Recinfo.global_name = X_Base_Global_Name)
200 OR ( (Recinfo.global_name IS NULL)
201 AND (X_Base_Global_Name IS NULL)))
202 AND ( (Recinfo.global_description = X_Base_Global_Description)
203 OR ( (Recinfo.global_description IS NULL)
204 AND (X_Base_Global_Description IS NULL)))
205 AND ( (Recinfo.global_value = X_Global_Value)
206 OR ( (Recinfo.global_value IS NULL)
207 AND (X_Global_Value IS NULL)))
208 ) then
209 return;
210 else
211 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
212 APP_EXCEPTION.RAISE_EXCEPTION;
213 end if;
214 --
215 END Lock_Row;
216 --
217 -----------------------------------------------------------------------------
218 -- Name --
219 -- Lock_Row --
220 -- Purpose --
221 -- Table handler procedure that supports the update of a global via the --
222 -- Define Global form. --
223 -- Arguments --
224 -- See below. --
225 -- Notes --
226 -- None. --
227 -----------------------------------------------------------------------------
228 --
229 PROCEDURE Update_Row(X_Rowid VARCHAR2,
230 X_Global_Id NUMBER,
231 X_Effective_Start_Date DATE,
232 X_Effective_End_Date DATE,
233 X_Business_Group_Id NUMBER,
234 X_Legislation_Code VARCHAR2,
235 X_Data_Type VARCHAR2,
236 X_Global_Name VARCHAR2,
237 X_Global_Description VARCHAR2,
238 X_Global_Value VARCHAR2,
239 X_Base_Global_Name VARCHAR2 default hr_api.g_varchar2,
240 X_Base_Global_Description VARCHAR2 default hr_api.g_varchar2) IS
241 --
242 l_global_name varchar2(80);
243 l_global_description varchar2(240);
244 BEGIN
245 --
246 --Fixed for bug 4350976--
247 /* Checking if the Base values of global_name and global_description are of type hr_api.g_varchar2 i.e.
248 the procedure is not being called from the form but from outside then copy the translated
249 values into them.*/
250
251 l_global_name := X_Base_Global_Name;
252 l_global_description := X_Base_Global_Description;
253
254 if(l_global_name = hr_api.g_varchar2) then
255 l_global_name := X_Global_Name;
256 end if;
257 if(l_global_description = hr_api.g_varchar2) then
258 l_global_description := X_Global_Description;
259 end if;
260 ----
261 g_dml_status := TRUE;
262 ----
263 UPDATE ff_globals_f
264 SET global_id = X_Global_Id,
265 effective_start_date = X_Effective_Start_Date,
266 effective_end_date = X_Effective_End_Date,
267 business_group_id = X_Business_Group_Id,
268 legislation_code = X_Legislation_Code,
269 data_type = X_Data_Type,
270 global_name = l_global_name,
271 global_description = l_global_description,
272 global_value = X_Global_Value
273 WHERE rowid = X_rowid;
274 --
275 --For MLS-----------------------------------------------------------------------
276 ff_fgt_upd.upd_tl(userenv('LANG'),X_GLOBAL_ID,
277 X_GLOBAL_NAME,X_GLOBAL_DESCRIPTION);
278 g_dml_status := FALSE;
279 --------------------------------------------------------------------------------
280 ---
281 if (SQL%NOTFOUND) then
282 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
283 hr_utility.set_message_token('PROCEDURE',
284 'ff_globals_f_pkg.update_row');
285 hr_utility.set_message_token('STEP','1');
286 hr_utility.raise_error;
287 end if;
288 --
289 Exception
290 When Others then
291 g_dml_status := FALSE;
292 raise;
293 END Update_Row;
294 --
295 -----------------------------------------------------------------------------
296 -- Name --
297 -- Delete_Row --
298 -- Purpose --
299 -- Table handler procedure that supports the delete of a global via the --
300 -- Define Global form. --
301 -- Arguments --
302 -- See below. --
303 -- Notes --
304 -- None. --
305 -----------------------------------------------------------------------------
306 --
307 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
308 --X_Global_Id NUMBER,-- Extra Columns
309 X_Global_Name VARCHAR2,
310 X_Business_Group_Id NUMBER,
311 X_Legislation_Code VARCHAR2) IS
312 --
313 x_global_id NUMBER(9);
314 BEGIN
315 --
316 -- Validate the delete NB. only ZAP's are allowed in the form so no other
317 -- DT delete validation is required.
318 ffdict.delete_dbitem_check
319 (X_Global_Name,
320 X_Business_Group_Id,
321 X_Legislation_Code);
322
323 --For MLS-----------------------------------------------------------------------
324 select Global_Id into x_global_id from ff_globals_f
325 where rowid = X_Rowid;
326 g_dml_status := TRUE;
327 ff_fgt_del.del_tl(x_global_id);
328 --------------------------------------------------------------------------------
329
330 --
331 DELETE FROM ff_globals_f
332 WHERE rowid = X_Rowid;
333 --
334 g_dml_status := FALSE;
335 --
336 if (SQL%NOTFOUND) then
337 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
338 hr_utility.set_message_token('PROCEDURE',
339 'ff_globals_f_pkg.delete_row');
340 hr_utility.set_message_token('STEP','1');
341 hr_utility.raise_error;
342 end if;
343 --
344 Exception
345 When Others then
346 g_dml_status := FALSE;
347 raise;
348 END Delete_Row;
349
350 ---For MLS----------------------------------------------------------------------
351 procedure ADD_LANGUAGE
352 is
353 begin
354 delete from FF_GLOBALS_F_TL T
355 where not exists
356 (select NULL
357 from FF_GLOBALS_F B
358 where B.GLOBAL_ID = T.GLOBAL_ID
359 );
360 update FF_GLOBALS_F_TL T set (
361 GLOBAL_NAME,
362 GLOBAL_DESCRIPTION
363 ) = (select
364 B.GLOBAL_NAME,
365 B.GLOBAL_DESCRIPTION
366 from FF_GLOBALS_F_TL B
367 where B.GLOBAL_ID = T.GLOBAL_ID
368 and B.LANGUAGE = T.SOURCE_LANG)
369 where (
370 T.GLOBAL_ID,
371 T.LANGUAGE
372 ) in (select
373 SUBT.GLOBAL_ID,
374 SUBT.LANGUAGE
375 from FF_GLOBALS_F_TL SUBB, FF_GLOBALS_F_TL SUBT
376 where SUBB.GLOBAL_ID = SUBT.GLOBAL_ID
377 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
378 and (SUBB.GLOBAL_NAME <> SUBT.GLOBAL_NAME
379 or SUBB.GLOBAL_DESCRIPTION <> SUBT.GLOBAL_DESCRIPTION
380 or (SUBB.GLOBAL_DESCRIPTION is null
381 and SUBT.GLOBAL_DESCRIPTION is not null)
382 or (SUBB.GLOBAL_DESCRIPTION is not null
383 and SUBT.GLOBAL_DESCRIPTION is null)
384 ));
385
386 insert into FF_GLOBALS_F_TL (
387 GLOBAL_ID,
388 GLOBAL_NAME,
389 GLOBAL_DESCRIPTION,
390 LAST_UPDATE_DATE,
391 LAST_UPDATED_BY,
392 LAST_UPDATE_LOGIN,
393 CREATED_BY,
394 CREATION_DATE,
395 LANGUAGE,
396 SOURCE_LANG
397 ) select
398 B.GLOBAL_ID,
399 B.GLOBAL_NAME,
400 B.GLOBAL_DESCRIPTION,
401 B.LAST_UPDATE_DATE,
402 B.LAST_UPDATED_BY,
403 B.LAST_UPDATE_LOGIN,
404 B.CREATED_BY,
405 B.CREATION_DATE,
406 L.LANGUAGE_CODE,
407 B.SOURCE_LANG
408 from FF_GLOBALS_F_TL B, FND_LANGUAGES L
409 where L.INSTALLED_FLAG in ('I', 'B')
410 and B.LANGUAGE = userenv('LANG')
411 and not exists
412 (select NULL
413 from FF_GLOBALS_F_TL T
414 where T.GLOBAL_ID = B.GLOBAL_ID
415 and T.LANGUAGE = L.LANGUAGE_CODE);
416 end ADD_LANGUAGE;
417 ---
418 -----
419 procedure TRANSLATE_ROW ( X_B_GLOBAL_NAME in VARCHAR2,
420 X_B_LEGISLATION_CODE in VARCHAR2,
421 X_GLOBAL_NAME in VARCHAR2,
422 X_GLOBAL_DESCRIPTION in VARCHAR2,
423 X_OWNER in VARCHAR2
424 ) is
425 l_global_id number;
426 l_ue_id number;
427 l_dbi_name varchar2(2000);
428 --
429 cursor csr_global_info
430 (p_b_global_name in varchar2
431 ,p_legislation_code in varchar2
432 ) is
433 select glb.global_id
434 from ff_globals_f glb
435 where glb.global_name = p_b_global_name
436 and glb.business_group_id is null
437 and (glb.legislation_code = p_legislation_code or
438 p_legislation_code is null and glb.legislation_code is null)
439 ;
440 begin
441 --
442 -- Verify that there actually is a GLOBAL value to update.
443 --
444 open csr_global_info
445 (p_b_global_name => upper(x_b_global_name)
446 ,p_legislation_code => x_b_legislation_code
447 );
448 fetch csr_global_info
449 into l_global_id;
450 if csr_global_info%notfound then
451 close csr_global_info;
452
453 --
454 -- Return because the global does not exist.
455 --
456 return;
457 end if;
458 close csr_global_info;
459
460
461 --
462 -- Disable triggers.
463 --
464 g_dml_status := TRUE;
465
466 --
467 -- Update the global value.
468 --
469 UPDATE ff_globals_f_tl
470 SET GLOBAL_NAME = nvl(X_GLOBAL_NAME,GLOBAL_NAME),
471 GLOBAL_DESCRIPTION = nvl(X_GLOBAL_DESCRIPTION,GLOBAL_DESCRIPTION),
472 last_update_date = SYSDATE,
473 last_updated_by = decode(x_owner,'SEED',1,0),
474 last_update_login = 0,
475 source_lang = userenv('LANG')
476 WHERE userenv('LANG') IN (language,source_lang)
477 AND GLOBAL_ID = l_global_id
478 ;
479
480 --
481 -- Update the translated database item rows. Note: translate_row takes
482 -- care of any name conversion and validation.
483 --
484 l_dbi_name := x_global_name;
485 ff_database_items_pkg.translate_row
486 (x_user_name => upper(x_b_global_name)
487 ,x_legislation_code => x_b_legislation_code
488 ,x_translated_user_name => l_dbi_name
489 ,x_description => x_global_description
490 ,x_language => userenv('LANG')
491 ,x_owner => x_owner
492 );
493
494 --
495 -- Re-enable triggers.
496 --
497 g_dml_status := FALSE;
498 exception
499 when others then
500 --
501 -- Re-enable triggers.
502 --
503 g_dml_status := FALSE;
504
505 if csr_global_info%isopen then
506 close csr_global_info;
507 end if;
508
509 raise;
510 end TRANSLATE_ROW;
511 --
512 ---
513 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
514 p_legislation_code IN VARCHAR2) IS
515 BEGIN
516 g_business_group_id := p_business_group_id;
517 g_legislation_code := p_legislation_code;
518 END;
519 --
520 ---
521 procedure validate_translation(global_id IN NUMBER,
522 language IN VARCHAR2,
523 global_name IN VARCHAR2,
524 global_description IN VARCHAR2) IS
525 begin
526 ffdict.validate_tl_global
527 (p_global_id => global_id
528 ,p_glob_name => global_name
529 ,p_bus_grp => g_business_group_id
530 ,p_leg_code => g_legislation_code
531 );
532 end validate_translation;
533 --
534 function return_dml_status
535 return boolean
536 IS
537 begin
538 return g_dml_status;
539 end return_dml_status;
540 --
541 --MLS End------------------------------------------------------------------------------
542
543
544 --Load_row procedure to be called by
545 --ffglbs1.lct
546 PROCEDURE LOAD_ROW ( P_BASE_GLOBAL_NAME VARCHAR2
547 ,P_EFFECTIVE_START_DATE DATE
548 ,P_EFFECTIVE_END_DATE DATE
549 ,P_GLOBAL_VALUE VARCHAR2
550 ,P_DATA_TYPE VARCHAR2
551 ,P_LEGISLATION_CODE VARCHAR2
552 ,P_BASE_GLOBAL_DESCRIPTION VARCHAR2
553 ,P_GLOBAL_NAME_TL VARCHAR2
554 ,P_GLOBAL_DESCRIPTION_TL VARCHAR2
555 ,P_MAX_UPDATE_DATE DATE)is
556
557 cursor csr_glb_exists_for_bg(r_global_name varchar2,r_legislation_code varchar2) is
558 select null
559 from ff_globals_f ffg
560 where ffg.global_name = r_global_name
561 and ffg.business_group_id is not null
562 and exists (select null
563 from per_business_groups pbg
564 where pbg.business_group_id = ffg.business_group_id
565 and pbg.legislation_code = r_legislation_code);
566
567 cursor csr_glb_exists_for_current_leg(r_global_name varchar2,
568 r_legislation_code varchar2)is
569 --opened and used once,distinct not required.
570 --creation_date may vary in seconds
571 Select data_type,global_id,creation_date,created_by,
572 max(trunc(last_update_date)) over(order by last_update_date) lud
573 from ff_globals_f ffg
574 where ffg.global_name=r_global_name
575 and ffg.legislation_code=r_legislation_code;
576
577 l_glb_exists_for_current_leg csr_glb_exists_for_current_leg%rowtype;
578 l_null Varchar2(1);
579 l_new_global_id number(10);
580 l_dummy_global_name varchar2(80);
581
582
583 PROCEDURE global_ins ( P_GLOBAL_ID NUMBER
584 ,P_EFFECTIVE_START_DATE DATE
585 ,P_EFFECTIVE_END_DATE DATE
586 ,P_LEGISLATION_CODE VARCHAR2
587 ,P_DATA_TYPE VARCHAR2
588 ,P_BASE_GLOBAL_NAME VARCHAR2
589 ,P_BASE_GLOBAL_DESCRIPTION VARCHAR2
590 ,P_GLOBAL_VALUE VARCHAR2
591 ,P_LAST_UPDATE_DATE DATE
592 ,P_LAST_UPDATED_BY NUMBER
593 ,P_LAST_UPDATE_LOGIN NUMBER
594 ,P_CREATED_BY NUMBER
595 ,P_CREATION_DATE DATE)
596
597
598 is
599 Begin
600 --Assumption Global has been validated
601 --Idea is to enter a global row alonwith the WHO columns
602 hr_utility.trace('Inserting global '||P_BASE_GLOBAL_NAME||'Date '||P_EFFECTIVE_START_DATE);
603 Insert into FF_GLOBALS_F
604 (GLOBAL_ID
605 ,EFFECTIVE_START_DATE
606 ,EFFECTIVE_END_DATE
607 ,BUSINESS_GROUP_ID
608 ,LEGISLATION_CODE
609 ,DATA_TYPE
610 ,GLOBAL_NAME
611 ,GLOBAL_DESCRIPTION
612 ,GLOBAL_VALUE
613 ,LAST_UPDATE_DATE
614 ,LAST_UPDATED_BY
615 ,LAST_UPDATE_LOGIN
616 ,CREATED_BY
617 ,CREATION_DATE)
618 values
619 (P_GLOBAL_ID
620 ,P_EFFECTIVE_START_DATE
621 ,P_EFFECTIVE_END_DATE
622 ,NULL
623 ,P_LEGISLATION_CODE
624 ,P_DATA_TYPE
625 ,P_BASE_GLOBAL_NAME
626 ,P_BASE_GLOBAL_DESCRIPTION
627 ,P_GLOBAL_VALUE
628 ,P_LAST_UPDATE_DATE
629 ,P_LAST_UPDATED_BY
630 ,P_LAST_UPDATE_LOGIN
631 ,P_CREATED_BY
632 ,P_CREATION_DATE);
633 End global_ins;
634
635 Procedure Global_Ins_Tl(P_GLOBAL_ID Number
636 ,P_GLOBAL_NAME_TL Varchar2
637 ,P_GLOBAL_DESCRIPTION_TL Varchar2
638 ) is
639
640 Begin
641 hr_utility.trace('Inserting tl global '||P_GLOBAL_NAME_TL);
642 ff_fgt_ins.ins_tl( P_LANGUAGE_CODE => userenv('LANG')
643 ,P_GLOBAL_ID => P_GLOBAL_ID
644 ,P_GLOBAL_NAME => P_GLOBAL_NAME_TL
645 ,P_GLOBAL_DESCRIPTION => P_GLOBAL_DESCRIPTION_TL
646 );
647
648
649 End Global_Ins_Tl;
650
651 Procedure Delete_glb_and_child_entities(p_global_id number) is
652 CURSOR csr_global_bsd(r_global_id number) IS
653 select distinct ffu.formula_id
654 from ff_fdi_usages_f ffu
655 where ffu.item_name in (select fdbi.user_name
656 from ff_database_items fdbi,
657 ff_user_entities ffue
658 where fdbi.user_entity_id = ffue.user_entity_id
659 and ffue.creator_id = r_global_id
660 and ffue.creator_type = 'S');
661
662
663 cursor csr_user_entity_id(r_base_global_name varchar2,
664 r_legislation_code varchar2,
665 r_global_id number ) is
666 select user_entity_id
667 from ff_user_entities
668 where creator_id = p_global_id
669 and creator_type = 'S'
670 and user_entity_name = r_base_global_name||'_GLOBAL_UE'
671 and legislation_code = r_legislation_code;
672 Begin
673
674 for i in csr_global_bsd(r_global_id=>p_global_id)
675 loop
676 delete ff_fdi_usages_f where formula_id = i.formula_id;
677 delete ff_compiled_info_f where formula_id =i.formula_id;
678 end loop;
679
680 for i in csr_user_entity_id(r_base_global_name=>p_base_global_name,
681 r_legislation_code=>p_legislation_code,
682 r_global_id =>p_global_id)
683 loop
684 -- Refer to bug 3744555 for not relying on
685 -- ref constraint to delete route_parameter_values
686 delete ff_route_parameter_values
687 where user_entity_id = i.user_entity_id
688 and value =to_char(p_global_id) ;
689
690 delete ff_database_items_tl
691 where user_entity_id = i.user_entity_id;
692
693 delete ff_user_entities
694 where user_entity_id=i.user_entity_id;
695
696 end loop;
697
698 delete ff_globals_f_tl
699 where global_id=p_global_id;
700
701 delete ff_globals_f
702 where global_id=p_global_id;
703 End Delete_glb_and_child_entities;
704
705 BEGIN
706 hr_utility.set_location('Entering:'||'FF_GLOBALS_F_PKG.Load_row',10);
707
708 if not ( (nvl(g_glb_record.global_name,'~nvl~') = P_BASE_GLOBAL_NAME)
709 and (nvl(g_glb_record.legislation_code,'~nvl~')=P_LEGISLATION_CODE)
710 )then
711
712 g_glb_record:=null;
713 --Global from ldt encountered first time
714 --check if it exists for this legislation in ff_globals_f
715
716
717 hr_utility.trace(P_BASE_GLOBAL_NAME||'*20');
718
719
720 open csr_glb_exists_for_current_leg(r_global_name=>P_BASE_GLOBAL_NAME,
721 r_legislation_code=>P_LEGISLATION_CODE);
722 fetch csr_glb_exists_for_current_leg into l_glb_exists_for_current_leg ;
723 close csr_glb_exists_for_current_leg ;
724
725 if l_glb_exists_for_current_leg.data_type is not null then
726 --Global for current legislation found
727 --check for max(last_update_date)
728 --** whole if condition removed for bug 5921008
729 --** if the db is newly created then the last_updated_date will be env.created date,
730 --** but if the last_updated_date is greater than the seeded(ldt) max_updated_date then
731 --** this package won't upload the seeded data, to avoid that we remvoed this if condition
732
733 --** STARTS bug 5921008
734 --** if ( p_max_update_date > l_glb_exists_for_current_leg.lud )then
735
736 --global has been updated since last uploaded
737 --delete all globals and user entities
738 --that match the global_name for this localization
739 --and recreate the globals,user entities etc..
740
741 hr_utility.trace(P_BASE_GLOBAL_NAME||'*30');
742 hr_general.g_data_migrator_mode:='Y';
743
744 --deleting child entities
745 --manually.Hence g_data_migrator_mode is set
746 delete_glb_and_child_entities(p_global_id=>l_glb_exists_for_current_leg.global_id);
747
748 global_ins( P_GLOBAL_ID => l_glb_exists_for_current_leg.global_id
749 ,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
750 ,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
751 ,P_LEGISLATION_CODE => P_LEGISLATION_CODE
752 ,P_DATA_TYPE => P_DATA_TYPE
753 ,P_BASE_GLOBAL_NAME => P_BASE_GLOBAL_NAME
754 ,P_BASE_GLOBAL_DESCRIPTION => P_BASE_GLOBAL_DESCRIPTION
755 ,P_GLOBAL_VALUE => P_GLOBAL_VALUE
756 ,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
757 ,P_LAST_UPDATED_BY => fnd_global.user_id
758 ,P_LAST_UPDATE_LOGIN => fnd_global.login_id
759 ,P_CREATED_BY => l_glb_exists_for_current_leg.created_by
760 ,P_CREATION_DATE => l_glb_exists_for_current_leg.creation_date
761 );
762
763 hr_general.g_data_migrator_mode:='N'; --set back to default values
764
765 Global_Ins_Tl(P_GLOBAL_ID => l_glb_exists_for_current_leg.global_id
766 ,P_GLOBAL_NAME_TL => P_GLOBAL_NAME_TL
767 ,P_GLOBAL_DESCRIPTION_TL => P_GLOBAL_DESCRIPTION_TL
768 );
769
770 ffdict.create_global_dbitem( p_name =>P_BASE_GLOBAL_NAME
771 ,p_data_type =>P_DATA_TYPE
772 ,p_global_id =>l_glb_exists_for_current_leg.global_id
773 ,p_business_group_id =>null
774 ,p_legislation_code =>P_LEGISLATION_CODE
775 ,p_created_by =>l_glb_exists_for_current_leg.created_by
776 ,p_creation_date =>l_glb_exists_for_current_leg.creation_date
777 );
778 g_glb_record.global_id :=l_glb_exists_for_current_leg.global_id;
779 g_glb_record.global_name :=P_BASE_GLOBAL_NAME;
780 g_glb_record.legislation_code :=P_LEGISLATION_CODE;
781 g_glb_record.created_by :=l_glb_exists_for_current_leg.created_by;
782 g_glb_record.creation_date :=l_glb_exists_for_current_leg.creation_date;
783 g_glb_record.global_upload_flag :=true;
784 /*
785 elsif ( p_max_update_date = l_glb_exists_for_current_leg.lud and g_glb_record.global_upload_flag)then
786
787 hr_utility.trace(P_BASE_GLOBAL_NAME||'*40');
788 global_ins( P_GLOBAL_ID => l_glb_exists_for_current_leg.global_id
789 ,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
790 ,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
791 ,P_LEGISLATION_CODE => P_LEGISLATION_CODE
792 ,P_DATA_TYPE => P_DATA_TYPE
793 ,P_BASE_GLOBAL_NAME => P_BASE_GLOBAL_NAME
794 ,P_BASE_GLOBAL_DESCRIPTION => P_BASE_GLOBAL_DESCRIPTION
795 ,P_GLOBAL_VALUE => P_GLOBAL_VALUE
796 ,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
797 ,P_LAST_UPDATED_BY => fnd_global.user_id
798 ,P_LAST_UPDATE_LOGIN => fnd_global.login_id
799 ,P_CREATED_BY => g_glb_record.created_by
800 ,P_CREATION_DATE => g_glb_record.creation_date
801 );
802
803 else
804
805 --p_max_update_date <l_glb_exists_for_current_leg.lud
806 --set the global details in g_glb_record and set flag to ignore
807 --rest of rows that match global_name and legislation_code
808 hr_utility.trace(P_BASE_GLOBAL_NAME||'*50');
809 g_glb_record:=null;
810 g_glb_record.global_name :=P_BASE_GLOBAL_NAME;
811 g_glb_record.legislation_code :=P_LEGISLATION_CODE;
812 g_glb_record.global_upload_flag :=false;
813
814
815
816 end if;
817 */
818 --** ENDS bug 5921008
819
820
821 else --l_glb_exists_for_current_leg.data_type is null here
822
823 --global not found in ff_globals_f for current legislation.
824 --Global needs to be created with last_update_date=P_MAX_UPDATE_DATE
825 --Ensure that there isnt a global with
826 --similar name for a BG that belongs to this leg
827 --ffdict doesnt perform this check
828 Begin
829 hr_utility.trace(P_BASE_GLOBAL_NAME||'*60');
830 open csr_glb_exists_for_bg(r_global_name =>P_BASE_GLOBAL_NAME
831 ,r_legislation_code =>P_LEGISLATION_CODE ) ;
832
833 fetch csr_glb_exists_for_bg into l_null;
834 if csr_glb_exists_for_bg%FOUND then
835 close csr_glb_exists_for_bg;
836 hr_utility.set_message(801,'FF52_NAME_ALREADY_USED');
837 hr_utility.set_message_token('1',P_BASE_GLOBAL_NAME);
838 hr_utility.set_message_token('2','Global Variable');
839 hr_utility.raise_error;
840 end if;
841 close csr_glb_exists_for_bg;
842
843 l_dummy_global_name:=P_BASE_GLOBAL_NAME;
844
845 ffdict.validate_global(p_glob_name => l_dummy_global_name,
846 p_bus_grp => null,
847 p_leg_code => P_LEGISLATION_CODE);
848 Exception
849 When others then
850
851 g_glb_record.global_name :=P_BASE_GLOBAL_NAME;
852 g_glb_record.legislation_code :=P_LEGISLATION_CODE;
853 g_glb_record.global_upload_flag :=false;
854 raise;
855 End;
856
857 -- to disable triggers from firing dml staments
858 hr_utility.trace(P_BASE_GLOBAL_NAME||'*70');
859 hr_general.g_data_migrator_mode:='Y';
860
861 select ff_globals_s.nextval
862 into l_new_global_id
863 from dual;
864
865
866 global_ins( P_GLOBAL_ID => l_new_global_id
867 ,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
868 ,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
869 ,P_LEGISLATION_CODE => P_LEGISLATION_CODE
870 ,P_DATA_TYPE => P_DATA_TYPE
871 ,P_BASE_GLOBAL_NAME => P_BASE_GLOBAL_NAME
872 ,P_BASE_GLOBAL_DESCRIPTION => P_BASE_GLOBAL_DESCRIPTION
873 ,P_GLOBAL_VALUE => P_GLOBAL_VALUE
874 ,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
875 ,P_LAST_UPDATED_BY => fnd_global.user_id
876 ,P_LAST_UPDATE_LOGIN => fnd_global.login_id
877 ,P_CREATED_BY => fnd_global.user_id
878 ,P_CREATION_DATE => sysdate
879 );
880
881 hr_general.g_data_migrator_mode:='N';
882
883
884 Global_Ins_Tl(P_GLOBAL_ID => l_new_global_id
885 ,P_GLOBAL_NAME_TL => P_GLOBAL_NAME_TL
886 ,P_GLOBAL_DESCRIPTION_TL => P_GLOBAL_DESCRIPTION_TL
887 );
888 ffdict.create_global_dbitem(p_name =>P_BASE_GLOBAL_NAME
889 ,p_data_type =>P_DATA_TYPE
890 ,p_global_id =>l_new_global_id
891 ,p_business_group_id =>null
892 ,p_legislation_code =>P_LEGISLATION_CODE
893 ,p_created_by =>fnd_global.user_id
894 ,p_creation_date =>sysdate
895 );
896 g_glb_record.global_id :=l_new_global_id;
897 g_glb_record.global_name :=P_BASE_GLOBAL_NAME;
898 g_glb_record.legislation_code :=P_LEGISLATION_CODE;
899 g_glb_record.created_by :=fnd_global.user_id;
900 g_glb_record.creation_date :=sysdate;
901 g_glb_record.global_upload_flag :=true;
902
903 end if; --l_glb_exists_for_current_leg.data_type is not null then ?
904
905 else --Global has been encountered in ldt before,hence name,id,etc known
906 --hence,only need to do a simple insert into table
907
908 if g_glb_record.global_upload_flag then
909 hr_utility.trace(P_BASE_GLOBAL_NAME||'*80');
910 hr_general.g_data_migrator_mode:='Y';
911 global_ins( P_GLOBAL_ID => g_glb_record.global_id
912 ,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
913 ,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
914 ,P_LEGISLATION_CODE => P_LEGISLATION_CODE
915 ,P_DATA_TYPE => P_DATA_TYPE
916 ,P_BASE_GLOBAL_NAME => P_BASE_GLOBAL_NAME
917 ,P_BASE_GLOBAL_DESCRIPTION => P_BASE_GLOBAL_DESCRIPTION
918 ,P_GLOBAL_VALUE => P_GLOBAL_VALUE
919 ,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
920 ,P_LAST_UPDATED_BY => fnd_global.user_id
921 ,P_LAST_UPDATE_LOGIN => fnd_global.login_id
922 ,P_CREATED_BY => g_glb_record.created_by
923 ,P_CREATION_DATE => g_glb_record.creation_date
924 );
925 hr_general.g_data_migrator_mode:='N';
926 end if;
927 end if;
928
929 hr_utility.set_location('Leaving:'||'FF_GLOBALS_F_PKG.Load_row',100);
930 End Load_row;
931
932
933
934
935
936 END FF_GLOBALS_F_PKG;