1 PACKAGE BODY FII_FDHM_PKG as
2 /* $Header: fiifdhmb.pls 120.5 2006/08/02 23:37:44 juding noship $ */
3
4 --
5 -- PUBLIC PROCEDURES
6 --
7 FUNCTION has_loop ( source IN VARCHAR2,
8 target IN VARCHAR2,
9 value_set_id IN NUMBER) RETURN VARCHAR2
10 IS
11 parent VARCHAR2(60);
12 CURSOR find_parent_cursor (child VARCHAR2, vsid NUMBER) IS
13 SELECT parent_flex_value
14 FROM fii_dim_norm_hierarchy
15 WHERE parent_flex_value_set_id = vsid
16 AND child BETWEEN child_flex_value_low
17 AND child_flex_value_high
18 AND parent_flex_value_set_id = child_flex_value_set_id;
19
20 BEGIN
21 OPEN find_parent_cursor(target, value_set_id);
22 LOOP
23 FETCH find_parent_cursor INTO parent;
24 IF ( find_parent_cursor%NOTFOUND ) THEN
25 CLOSE find_parent_cursor;
26 RETURN('FALSE');
27 ELSIF ( parent = source ) THEN
28 CLOSE find_parent_cursor;
29 RETURN('TRUE');
30 ELSIF ( has_loop(source, parent, value_set_id) = 'TRUE' ) THEN
31 CLOSE find_parent_cursor;
32 RETURN('TRUE');
33 END IF;
34 END LOOP;
35 CLOSE find_parent_cursor;
36 RETURN('FALSE');
37 END has_loop;
38
39 FUNCTION modify_range ( parent IN VARCHAR2,
40 child IN VARCHAR2,
41 range_attr IN VARCHAR2,
42 range_low IN VARCHAR2,
43 range_high IN VARCHAR2,
44 parent_value_set_id IN NUMBER,
45 child_value_set_id IN NUMBER) RETURN INTEGER
46 IS
47 range_size NUMBER := 0;
48 sum_flag VARCHAR2(1);
49 new_bound VARCHAR2(150);
50 BEGIN
51 IF ( range_attr = 'P') THEN
52 sum_flag := 'Y';
53 ELSE
54 sum_flag := 'N';
55 END IF;
56
57 SELECT COUNT(*)
58 INTO range_size
59 FROM FND_FLEX_VALUES
60 WHERE flex_value_set_id = child_value_set_id
61 AND summary_flag = sum_flag
62 AND flex_value BETWEEN range_low AND range_high;
63
64 /* If range is a single valued range, the row can be removed from the
65 norm hierarchy table. */
66 IF ( range_size = 1 ) THEN
67 DELETE FROM FII_DIM_NORM_HIERARCHY
68 WHERE parent_flex_value_set_id = parent_value_set_id
69 AND child_flex_value_set_id = child_value_set_id
70 AND parent_flex_value = parent
71 AND range_attribute = range_attr
72 AND child_flex_value_low = range_low
73 AND child_flex_value_high = range_high;
74
75 IF ( parent_value_set_id = child_value_set_id ) THEN
76 DELETE FND_FLEX_VALUE_NORM_HIERARCHY
77 WHERE flex_value_set_id = parent_value_set_id
78 AND parent_flex_value = parent
79 AND range_attribute = range_attr
80 AND child_flex_value_low = range_low
81 AND child_flex_value_high = range_high;
82 END IF;
83
84 ELSIF ( child = range_low ) THEN
85 /* If the value to be removed from the range was the lower boundary,
86 the lower boundary of the original range has to be adjusted to be
87 the flex value immediately following the value to be removed.*/
88
89 SELECT MIN(flex_value)
90 INTO new_bound
91 FROM fnd_flex_values
92 WHERE flex_value_set_id = child_value_set_id
93 AND summary_flag = sum_flag
94 AND flex_value > child
95 AND flex_value <= range_high
96 ORDER BY flex_value;
97
98 IF ( new_bound IS NOT NULL ) THEN
99 UPDATE FII_DIM_NORM_HIERARCHY
100 SET child_flex_value_low = new_bound
101 WHERE parent_flex_value_set_id = parent_value_set_id
102 AND child_flex_value_set_id = child_value_set_id
103 AND parent_flex_value = parent
104 AND range_attribute = range_attr
105 AND child_flex_value_low = range_low
106 AND child_flex_value_high = range_high;
107
108 IF ( parent_value_set_id = child_value_set_id ) THEN
109 UPDATE FND_FLEX_VALUE_NORM_HIERARCHY
110 SET child_flex_value_low = new_bound
111 WHERE flex_value_set_id = parent_value_set_id
112 AND parent_flex_value = parent
113 AND range_attribute = range_attr
114 AND child_flex_value_low = range_low
115 AND child_flex_value_high = range_high;
116 END IF;
117
118 END IF;
119 ELSIF ( child = range_high ) THEN
120 /* If the value to be removed from the range was the upper boundary,
121 the upper boundary of the original range has to be adjusted to be
122 the flex value immediately before the value to be removed. */
123 SELECT MAX(flex_value)
124 INTO new_bound
125 FROM fnd_flex_values
126 WHERE flex_value_set_id = child_value_set_id
127 AND summary_flag = sum_flag
128 AND flex_value >= range_low
129 AND flex_value < child
130 ORDER BY flex_value;
131
132 IF ( new_bound IS NOT NULL ) THEN
133 UPDATE FII_DIM_NORM_HIERARCHY
134 SET child_flex_value_high = new_bound
135 WHERE parent_flex_value_set_id = parent_value_set_id
136 AND child_flex_value_set_id = child_value_set_id
137 AND parent_flex_value = parent
138 AND range_attribute = range_attr
139 AND child_flex_value_low = range_low
140 AND child_flex_value_high = range_high;
141
142 IF ( parent_value_set_id = child_value_set_id ) THEN
143 UPDATE FND_FLEX_VALUE_NORM_HIERARCHY
144 SET child_flex_value_high = new_bound
145 WHERE flex_value_set_id = parent_value_set_id
146 AND parent_flex_value = parent
147 AND range_attribute = range_attr
148 AND child_flex_value_low = range_low
149 AND child_flex_value_high = range_high;
150 END IF;
151
152 END IF;
153 ELSE
154 /* If the value to be removed falls somewhere between the upper and
155 lower boundaries, the original range has to be removed and 2 new ranges
156 will be created to exclude the value to be removed. */
157
158 DELETE FROM FII_DIM_NORM_HIERARCHY
159 WHERE parent_flex_value_set_id = parent_value_set_id
160 AND child_flex_value_set_id = child_value_set_id
161 AND parent_flex_value = parent
162 AND range_attribute = range_attr
163 AND child_flex_value_low = range_low
164 AND child_flex_value_high = range_high;
165
166 IF ( parent_value_set_id = child_value_set_id ) THEN
167 DELETE FND_FLEX_VALUE_NORM_HIERARCHY
168 WHERE flex_value_set_id = parent_value_set_id
169 AND parent_flex_value = parent
170 AND range_attribute = range_attr
171 AND child_flex_value_low = range_low
172 AND child_flex_value_high = range_high;
173 END IF;
174
175 /* The lower range will contain the same lower bound as the
176 original range, and the upper bound will be the flex value before
177 the value to be removed. */
178 SELECT MAX(flex_value)
179 INTO new_bound
180 FROM fnd_flex_values
181 WHERE flex_value_set_id = child_value_set_id
182 AND summary_flag = sum_flag
183 AND flex_value >= range_low
184 AND flex_value < child
185 ORDER BY flex_value;
186
187 IF ( new_bound IS NOT NULL ) THEN
188 /* If no 'lower' flex value can be found, no new lower range
189 will be created. */
190 INSERT INTO FII_DIM_NORM_HIERARCHY
191 ( parent_flex_value_set_id,
192 child_flex_value_set_id,
193 parent_flex_value,
194 range_attribute,
195 child_flex_value_low,
196 child_flex_value_high,
197 last_update_date,
198 last_updated_by,
199 creation_date,
200 created_by,
201 last_update_login )
202 VALUES
203 ( parent_value_set_id,
204 child_value_set_id,
205 parent,
206 range_attr,
207 range_low,
208 new_bound,
209 SYSDATE,
210 0,
211 SYSDATE,
212 0,
213 fnd_global.login_id);
214
215 IF ( parent_value_set_id = child_value_set_id ) THEN
216 INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
217 ( flex_value_set_id,
218 parent_flex_value,
219 range_attribute,
220 child_flex_value_low,
221 child_flex_value_high,
222 last_update_date,
223 last_updated_by,
224 creation_date,
225 created_by,
226 last_update_login )
227 VALUES
228 ( parent_value_set_id,
229 parent,
230 range_attr,
231 range_low,
232 new_bound,
233 SYSDATE,
234 0,
235 SYSDATE,
236 0,
237 fnd_global.login_id);
238 END IF;
239
240 END IF;
241
242 /* The upper range will contain the same upper bound as the
243 original range, and the lower bound will be the flex value after
244 the value to be removed. */
245 SELECT MIN(flex_value)
246 INTO new_bound
247 FROM fnd_flex_values
248 WHERE flex_value_set_id = child_value_set_id
249 AND summary_flag = sum_flag
250 AND flex_value > child
251 AND flex_value <= range_high
252 ORDER BY flex_value;
253
254 IF ( new_bound IS NOT NULL ) THEN
255 /* If no 'lower' flex value can be found, no new lower range
256 will be created. */
257 INSERT INTO FII_DIM_NORM_HIERARCHY
258 ( parent_flex_value_set_id,
259 child_flex_value_set_id,
260 parent_flex_value,
261 range_attribute,
262 child_flex_value_low,
263 child_flex_value_high,
264 last_update_date,
265 last_updated_by,
266 creation_date,
267 created_by,
268 last_update_login )
269 VALUES
270 ( parent_value_set_id,
271 child_value_set_id,
272 parent,
273 range_attr,
274 new_bound,
275 range_high,
276 SYSDATE,
277 0,
278 SYSDATE,
279 0,
280 fnd_global.login_id );
281
282 IF ( parent_value_set_id = child_value_set_id ) THEN
283 INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
284 ( flex_value_set_id,
285 parent_flex_value,
286 range_attribute,
287 child_flex_value_low,
288 child_flex_value_high,
289 last_update_date,
290 last_updated_by,
291 creation_date,
292 created_by,
293 last_update_login )
294 VALUES
295 ( parent_value_set_id,
296 parent,
297 range_attr,
298 new_bound,
299 range_high,
300 SYSDATE,
301 0,
302 SYSDATE,
303 0,
304 fnd_global.login_id);
305 END IF;
306 END IF;
307 END IF;
308 RETURN (1);
309 EXCEPTION
310 WHEN OTHERS THEN
311 RETURN (0);
312 END modify_range;
313
314
315 FUNCTION has_loop_in_range ( parent IN VARCHAR2,
316 low IN VARCHAR2,
317 high IN VARCHAR2,
318 value_set_id IN NUMBER) RETURN VARCHAR2
319 IS
320 child VARCHAR2(60);
321 CURSOR find_children_cursor (low VARCHAR2, high VARCHAR2, vsid NUMBER) IS
322 SELECT flex_value
323 FROM fnd_flex_values
324 WHERE flex_value_set_id = vsid
325 AND summary_flag = 'Y'
326 AND flex_value BETWEEN low AND high
327 ORDER by flex_value;
328 BEGIN
329 -- 1. if parent is within the range, it has loop
330 if (parent>=low and parent<=high) then
331 return (parent);
332 end if;
333
334 -- 2. if any children inside the range has the parent as children,
335 -- it has loop
336 OPEN find_children_cursor(low, high, value_set_id);
337 LOOP
338 FETCH find_children_cursor INTO child;
339 IF ( find_children_cursor%NOTFOUND ) THEN
340 CLOSE find_children_cursor;
341 RETURN('');
342 ELSIF ( has_loop(child, parent, value_set_id) = 'TRUE' ) THEN
343 CLOSE find_children_cursor;
344 RETURN(child);
345 END IF;
346 END LOOP;
347 CLOSE find_children_cursor;
348 RETURN('');
349 END has_loop_in_range;
350
351 FUNCTION unique_flex_value (
352 f_value IN VARCHAR2,
353 parent_low IN VARCHAR2,
354 value_set_id IN NUMBER) RETURN VARCHAR2
355 IS
356 row_count number := 0;
357 BEGIN
361 FROM fnd_flex_values
358 -- try to find another flex value that is the same as the current one
359 SELECT count(*)
360 INTO row_count
362 WHERE flex_value_set_id = value_set_id
363 AND flex_value = f_value
364 AND ((parent_low IS null) OR
365 (parent_flex_value_low = parent_low));
366 IF ( row_count > 0 ) THEN
367 RETURN ('FALSE');
368 ELSE
369 RETURN ('TRUE');
370 END IF;
371 END unique_flex_value;
372
373 FUNCTION lock_dim_value_sets (dim_short_name VARCHAR2,
374 source_lgr_group_id NUMBER) RETURN VARCHAR2 is
375 lkname varchar2(128);
376 lkhandle varchar2(128);
377 rs_mode constant integer := 6; -- X mode
378 timout constant integer := 2; -- 2 secs timeout
379 expiration_secs constant integer := 864000;
380 lkresult integer;
381 fdvsid NUMBER;
382
383 CURSOR find_dim_value_sets_cur IS
384 SELECT flex_value_set_id1
385 FROM fii_dim_mapping_rules r
386 WHERE r.dimension_short_name = dim_short_name
387 AND flex_value_set_id1 is not null
388 AND r.chart_of_accounts_id in
389 (SELECT chart_of_accounts_id
390 FROM fii_slg_assignments
391 WHERE source_ledger_group_id =source_lgr_group_id)
392 UNION
393 SELECT master_value_set_id flex_value_set_id1
394 FROM fii_financial_dimensions_v m
395 WHERE m.dimension_short_name = dim_short_name
396 ORDER BY flex_value_set_id1;
397
398 BEGIN
399 -- Disable the lock mechanism
400 RETURN ('TRUE');
401
402 OPEN find_dim_value_sets_cur;
403 LOOP
404 FETCH find_dim_value_sets_cur INTO fdvsid;
405
406 IF ( find_dim_value_sets_cur%NOTFOUND ) THEN
407 CLOSE find_dim_value_sets_cur;
408 RETURN ('TRUE');
409 ELSE
410 -- generate the name for the user-defined lock
411 lkname := 'FND_FLEX_AHE_VS_' || to_char(fdvsid);
412
413 -- get Oracle-assigned lock handle
414 dbms_lock.allocate_unique( lkname, lkhandle, expiration_secs );
415
416 -- request a lock, NOT release on commit
417 lkresult := dbms_lock.request( lkhandle, rs_mode, timout, false );
418
419 if ( lkresult = 1 ) then
420 -- Account Hierarchy Editor is locking out value set
421 -- print out appropriate warning message
422 release_value_set_lock(dim_short_name, source_lgr_group_id, fdvsid);
423 -- fnd_message.set_name('FND', 'FLEX-AHE LOCKING VSET');
424 CLOSE find_dim_value_sets_cur;
425 -- app_exception.raise_exception;
426 RETURN ('FALSE');
427 elsif (lkresult <> 0 and lkresult <> 4) then
428 -- lkresult = 0, locking successfully
429 -- lkresult = 4, already own lock
430 release_value_set_lock(dim_short_name, source_lgr_group_id, fdvsid);
431 -- fnd_message.set_name('FND', 'FLEX-AHE DBMS_LOCK ERROR');
432 CLOSE find_dim_value_sets_cur;
433 -- app_exception.raise_exception;
434 RETURN ('FALSE');
435 end if;
436 END IF;
437
438 END LOOP;
439 CLOSE find_dim_value_sets_cur;
440 RETURN ('FALSE');
441
442 END lock_dim_value_sets;
443
444
445 PROCEDURE release_value_set_lock(dim_short_name VARCHAR2, source_lgr_group_id NUMBER,
446 value_set_id NUMBER)is
447 lkname varchar2(128);
448 lkhandle varchar2(128);
449 expiration_secs constant integer := 864000;
450 lkresult integer;
451 fdvsid NUMBER;
452
453 CURSOR find_dim_value_sets_cur IS
454 SELECT flex_value_set_id1
455 FROM fii_dim_mapping_rules r
456 WHERE r.dimension_short_name = dim_short_name
457 AND flex_value_set_id1 is not null
458 AND r.chart_of_accounts_id in
459 (SELECT chart_of_accounts_id
460 FROM fii_slg_assignments
461 WHERE source_ledger_group_id =source_lgr_group_id)
462 AND flex_value_set_id1 < value_set_id
463 UNION
464 SELECT master_value_set_id flex_value_set_id1
465 FROM fii_financial_dimensions_v m
466 WHERE m.dimension_short_name = dim_short_name
467 AND master_value_set_id < value_set_id
468 ORDER BY flex_value_set_id1;
469
470 BEGIN
471 -- Disable the lock mechanism
472 RETURN;
473
474 OPEN find_dim_value_sets_cur;
475 LOOP
476 FETCH find_dim_value_sets_cur INTO fdvsid;
477
478 IF ( find_dim_value_sets_cur%NOTFOUND ) THEN
479 CLOSE find_dim_value_sets_cur;
480 RETURN ;
481 ELSE
482 lkname := 'FND_FLEX_AHE_VS_' || to_char(fdvsid);
483 dbms_lock.allocate_unique( lkname, lkhandle, expiration_secs);
484 lkresult := dbms_lock.release(lkhandle);
485
486 END IF;
487 END LOOP;
488 CLOSE find_dim_value_sets_cur;
489 RETURN;
490 END release_value_set_lock;
491
492
493 FUNCTION release_dimension_lock(dim_short_name VARCHAR2, source_lgr_group_id NUMBER) RETURN VARCHAR2 is
494 lkname varchar2(128);
495 lkhandle varchar2(128);
496 expiration_secs constant integer := 864000;
497 lkresult integer;
498 fdvsid NUMBER;
499 success boolean;
500
501 CURSOR find_dim_value_sets_cur IS
502 SELECT flex_value_set_id1
503 FROM fii_dim_mapping_rules r
507 (SELECT chart_of_accounts_id
504 WHERE r.dimension_short_name = dim_short_name
505 AND flex_value_set_id1 is not null
506 AND r.chart_of_accounts_id in
508 FROM fii_slg_assignments
509 WHERE source_ledger_group_id =source_lgr_group_id)
510 UNION
511 SELECT master_value_set_id flex_value_set_id1
512 FROM fii_financial_dimensions_v m
513 WHERE m.dimension_short_name = dim_short_name
514 ORDER BY flex_value_set_id1;
515
516 BEGIN
517 -- Disable the lock mechanism
518 RETURN ('TRUE');
519
520 success := true;
521
522 OPEN find_dim_value_sets_cur;
523 LOOP
524 FETCH find_dim_value_sets_cur INTO fdvsid;
525
526 IF ( find_dim_value_sets_cur%NOTFOUND ) THEN
527 CLOSE find_dim_value_sets_cur;
528
529 if (success) then
530 RETURN ('TRUE');
531 else
532 RETURN ('FALSE');
533 end if;
534 ELSE
535 lkname := 'FND_FLEX_AHE_VS_' || to_char(fdvsid);
536
537 dbms_lock.allocate_unique( lkname, lkhandle, expiration_secs );
538
539 lkresult := dbms_lock.release(lkhandle);
540
541 if (lkresult <> 0 ) then
542 success := false;
543 end if;
544
545 END IF;
546 END LOOP;
547 CLOSE find_dim_value_sets_cur;
548 RETURN ('FALSE');
549
550 END release_dimension_lock;
551
552
553 PROCEDURE insert_dim_value_sets (dim_short_name VARCHAR2, source_lgr_group_id NUMBER)
554 IS
555 BEGIN
556 INSERT INTO FII_DIM_NORM_HIERARCHY
557 (parent_flex_value_set_id,
558 child_flex_value_set_id,
559 parent_flex_value,
560 range_attribute,
561 child_flex_value_low,
562 child_flex_value_high,
563 last_update_date,
564 last_updated_by,
565 creation_date,
566 created_by,
567 last_update_login )
568 /*
569 SELECT distinct flex_value_set_id,
570 flex_value_set_id,
571 parent_flex_value,
572 range_attribute,
573 child_flex_value_low,
574 child_flex_value_high,
575 last_update_date,
576 last_updated_by,
577 creation_date,
578 created_by,
579 last_update_login
580 FROM FND_FLEX_VALUE_NORM_HIERARCHY
581 WHERE flex_value_set_id in
582 (SELECT r.flex_value_set_id1
583 FROM fii_dim_mapping_rules r
584 WHERE r.dimension_short_name = dim_short_name
585 AND r.chart_of_accounts_id in
586 (SELECT chart_of_accounts_id
587 FROM fii_slg_assignments
588 WHERE source_ledger_group_id =source_lgr_group_id))
589 OR flex_value_set_id =
590 (SELECT master_value_set_id
591 FROM fii_financial_dimensions_v m
592 WHERE m.dimension_short_name= dim_short_name);
593 */
594 SELECT flex_value_set_id,
595 flex_value_set_id,
596 parent_flex_value,
597 range_attribute,
598 child_flex_value_low,
599 child_flex_value_high,
600 last_update_date,
601 last_updated_by,
602 creation_date,
603 created_by,
604 last_update_login
605 FROM FND_FLEX_VALUE_NORM_HIERARCHY,
606 (
607 SELECT r.flex_value_set_id1 vs_id
608 FROM fii_dim_mapping_rules r,
609 (SELECT distinct chart_of_accounts_id
610 FROM fii_slg_assignments
611 WHERE source_ledger_group_id = source_lgr_group_id) s
612 WHERE r.dimension_short_name = dim_short_name
613 AND r.chart_of_accounts_id = s.chart_of_accounts_id
614 union
615 SELECT master_value_set_id vs_id
616 FROM fii_financial_dimensions m
617 WHERE m.dimension_short_name = dim_short_name
618 )
619 WHERE flex_value_set_id = vs_id;
620
621 END insert_dim_value_sets;
622
623
624 FUNCTION flatten_hierarchy (dim_short_name VARCHAR2,
625 source_lgr_group_id NUMBER,
626 user_id IN NUMBER,
627 resp_id IN NUMBER,
628 appl_id IN NUMBER) RETURN VARCHAR2
629 is
630 fdvsid NUMBER;
631 req_id integer;
632 success boolean :=true;
633
634 CURSOR find_dim_value_sets_cur IS
635 SELECT flex_value_set_id1
636 FROM fii_dim_mapping_rules r
637 WHERE r.dimension_short_name = dim_short_name
638 AND r.chart_of_accounts_id in
639 (SELECT chart_of_accounts_id
640 FROM fii_slg_assignments
641 WHERE source_ledger_group_id =source_lgr_group_id)
642 OR flex_value_set_id1 in
643 (SELECT master_value_set_id
647 BEGIN
644 FROM fii_financial_dimensions_v m
645 WHERE m.dimension_short_name= r.dimension_short_name);
646 sid number;
648 FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, appl_id);
649
650 OPEN find_dim_value_sets_cur;
651 LOOP
652 FETCH find_dim_value_sets_cur INTO fdvsid;
653
654 EXIT WHEN find_dim_value_sets_cur%NOTFOUND;
655
656 req_id := fnd_request.submit_request(
657 application => 'FND',
658 program => 'FDFCHY',
659 argument1 => TO_CHAR(fdvsid)
660 );
661
662 if (req_id=0) then
663 success := false;
664 end if;
665
666 END LOOP;
667 CLOSE find_dim_value_sets_cur;
668
669 if (success) then
670 return ('TRUE');
671 else
672 return ('FALSE');
673 end if;
674
675 END flatten_hierarchy;
676
677
678 PROCEDURE insert_tl_records is
679
680 cursor installed_lang_cursor is
681 select LANGUAGE_CODE from FND_LANGUAGES
682 where INSTALLED_FLAG in ('B', 'I');
683
684 lang_code VARCHAR(4);
685
686 BEGIN
687
688 OPEN installed_lang_cursor;
689 LOOP
690 FETCH installed_lang_cursor INTO lang_code;
691 IF ( installed_lang_cursor%NOTFOUND ) THEN
692 CLOSE installed_lang_cursor;
693 RETURN;
694 ELSIF ( lang_code <> userenv('LANG') ) THEN
695 insert into FND_FLEX_VALUES_TL (
696 FLEX_VALUE_ID,
697 LAST_UPDATE_DATE,
698 LAST_UPDATED_BY,
699 CREATION_DATE,
700 CREATED_BY,
701 LAST_UPDATE_LOGIN,
702 DESCRIPTION,
703 FLEX_VALUE_MEANING,
704 LANGUAGE,
705 SOURCE_LANG
706 )
707 (select
708 t1.FLEX_VALUE_ID,
709 t1.LAST_UPDATE_DATE,
710 t1.LAST_UPDATED_BY,
711 t1.CREATION_DATE,
712 t1.CREATED_BY,
713 t1.LAST_UPDATE_LOGIN,
714 t1.DESCRIPTION,
715 t1.FLEX_VALUE_MEANING,
716 lang_code,
717 t1.SOURCE_LANG
718 from fnd_flex_values_tl t1 left outer join
719 fnd_flex_values_tl t2
720 on t1.flex_value_id = t2.flex_value_id
721 and t2.language = lang_code
722 where t1.language = userenv('LANG')
723 and t2.flex_value_id is NULL
724 );
725
726 END IF;
727 END LOOP;
728
729 END insert_tl_records;
730
731 PROCEDURE insert_tl_records_for_id(value_id number) is
732 BEGIN
733
734 insert into FND_FLEX_VALUES_TL(
735 FLEX_VALUE_ID,
736 LAST_UPDATE_DATE,
737 LAST_UPDATED_BY,
738 CREATION_DATE,
739 CREATED_BY,
740 LAST_UPDATE_LOGIN,
741 DESCRIPTION,
742 FLEX_VALUE_MEANING,
743 LANGUAGE,
744 SOURCE_LANG
745 )
746 select
747 t1.FLEX_VALUE_ID,
748 t1.LAST_UPDATE_DATE,
749 t1.LAST_UPDATED_BY,
750 t1.CREATION_DATE,
751 t1.CREATED_BY,
752 t1.LAST_UPDATE_LOGIN,
753 t1.DESCRIPTION,
754 t1.FLEX_VALUE_MEANING,
755 ls.LANGUAGE_CODE,
756 t1.SOURCE_LANG
757 from fnd_flex_values_tl t1, FND_LANGUAGES ls
758 where t1.flex_value_id = value_id
759 and t1.language = userenv('LANG')
760 and ls.INSTALLED_FLAG in ('B', 'I')
761 and ls.LANGUAGE_CODE <> userenv('LANG');
762
763 END insert_tl_records_for_id;
764
765 PROCEDURE delete_tl_records_for_id(value_id number) is
766 BEGIN
767
768 delete from FND_FLEX_VALUES_TL
769 where flex_value_id = value_id
770 and language <> userenv('LANG');
771
772 END delete_tl_records_for_id;
773
774 FUNCTION get_compiled_value_attr(value_set_id NUMBER) RETURN VARCHAR2 is
775 dvalue VARCHAR2(30);
776 cvalue VARCHAR2(2000);
777 ct NUMBER :=0;
778 CURSOR find_value_attr_cur IS
779 SELECT tp.default_value
780 FROM fnd_flex_validation_qualifiers qf,
781 fnd_value_attribute_types tp
782 WHERE flex_value_set_id = value_set_id
783 AND qf.value_attribute_type =tp.value_attribute_type
784 AND qf.segment_attribute_type = tp.segment_attribute_type
785 AND qf.id_flex_code = tp.id_flex_code
786 AND qf.id_flex_application_id = tp.application_id
787 ORDER BY qf.assignment_date, qf.value_attribute_type;
788
789 BEGIN
790 OPEN find_value_attr_cur;
791 LOOP
792 FETCH find_value_attr_cur INTO dvalue;
793 IF ( find_value_attr_cur%NOTFOUND ) THEN
794 CLOSE find_value_attr_cur;
795 RETURN cvalue;
796 ELSIF (ct=0) then
797 cvalue := dvalue;
798 ELSE
799 cvalue := cvalue||fnd_global.newline||dvalue;
800 END IF;
801 ct:= ct+1;
802 END LOOP;
803 CLOSE find_value_attr_cur;
804
805 RETURN cvalue;
806
807 END get_compiled_value_attr;
808
809
810 PROCEDURE launch( dim_short_name IN VARCHAR2,
811 source_ledger_group_id IN NUMBER) IS
812
816 tcfHost VARCHAR2(128) := wfa_html.conv_special_url_chars(
813 sessionCookie VARCHAR2(128) := ICX_CALL.ENCRYPT3(ICX_SEC.getSessionCookie());
814 lang VARCHAR2(128) := ICX_SEC.g_language_code;
815 -- need to escape slash characters in host argument
817 FND_PROFILE.VALUE('TCF:HOST'));
818 tcfPort VARCHAR2(128) := FND_PROFILE.VALUE('TCF:PORT');
819 dbc_file VARCHAR2(128) := fnd_web_config.database_id;
820
821 error VARCHAR2(250) := 'You do not have the required security privileges to launch Financial Dimension Hierarchy Manager. Please contact your System Administrator.';
822
823 BEGIN
824
825 if (icx_sec.validateSession) then
826
827 if (access_test = 'FALSE') then
828 htp.p(error);
829 else
830
831 fnd_applet_launcher.launch(
832 applet_class => 'oracle.apps.fii.fdhm.client.FinDimHierMgr',
833
834 archive_list => 'oracle/apps/fii/jar/fiifdhm.jar'
835 ||',jbodatum111.jar'
836 ||',oracle/apps/fnd/jar/fndtcf.jar'
837 ||',oracle/apps/fnd/jar/fndaroraclnt.jar'
838 ||',oracle/apps/fnd/jar/fndconnectionmanager.jar'
839 ||',oracle/apps/fnd/jar/fndjewtall.jar'
840 ||',oracle/apps/fnd/jar/fndjndi.jar'
841 ||',oracle/apps/fnd/jar/fndswingall.jar'
842 ||',oracle/apps/fnd/jar/jbodomorcl.jar'
843 ||',oracle/apps/fnd/jar/jdev-rt.jar'
844 ||',oracle/apps/fnd/jar/fwk_client.jar'
845 ||',oracle/apps/fnd/jar/jboremote.jar'
846 ||',oracle/apps/fnd/jar/fndctx.jar'
847 ||',oracle/apps/fnd/jar/fndcollections.jar'
848 ||'',
849
850 user_args => '&gp1=host&gv1=' ||
851 tcfHost ||
852 '&gp2=port&gv2=' ||
853 tcfPort ||
854 '&gp3=dbc_file&gv3=' ||
855 dbc_file ||
856 '&gp4=debug&gv4=' ||
857 'N' ||
858 -- SessionCookie and Language should be removed
859 -- after full migration to TCF
860 '&gp5=sessionCookie&gv5=' ||
861 sessionCookie ||
862 '&gp6=language&gv6=' ||
863 lang ||
864 '&gp7=slgId&gv7=' ||
865 source_ledger_group_id ||
866 '&gp8=dim&gv8=' ||
867 dim_short_name ||
868 '',
869
870 title_app => 'SQLGL',
871 title_msg => 'GL_FDHM_PAGE_TITLE',
872 cache => 'off'
873 );
874 end if;
875
876 end if;
877
878 END launch;
879
880 PROCEDURE delete_dim_value_sets(dim_short_name VARCHAR2,
881 source_lgr_group_id NUMBER)
882 IS
883 BEGIN
884 DELETE FROM fii_dim_norm_hierarchy
885 WHERE parent_flex_value_set_id = child_flex_value_set_id
886 AND ( child_flex_value_set_id in
887 (SELECT r.flex_value_set_id1
888 FROM fii_dim_mapping_rules r
889 WHERE r.dimension_short_name = dim_short_name
890 AND r.chart_of_accounts_id in
891 (SELECT chart_of_accounts_id
892 FROM fii_slg_assignments
893 WHERE source_ledger_group_id =source_lgr_group_id))
894 OR child_flex_value_set_id =
895 (SELECT master_value_set_id
896 FROM fii_financial_dimensions_v m
897 WHERE m.dimension_short_name= dim_short_name) );
898
899 END delete_dim_value_sets;
900
901
902 PROCEDURE insert_fnd_norm_hier_rec(
903 parent IN VARCHAR2,
904 child IN VARCHAR2,
905 range_attr IN VARCHAR2,
906 range_low IN VARCHAR2,
907 range_high IN VARCHAR2,
908 value_set_id IN NUMBER)
909 IS
910 BEGIN
911 INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
912 ( flex_value_set_id,
913 parent_flex_value,
914 range_attribute,
915 child_flex_value_low,
916 child_flex_value_high,
917 last_update_date,
918 last_updated_by,
919 creation_date,
920 created_by,
921 last_update_login )
922 VALUES
923 ( value_set_id,
924 parent,
925 range_attr,
926 range_low,
927 range_high,
928 SYSDATE,
929 0,
930 SYSDATE,
931 0,
932 fnd_global.login_id);
933
934 END insert_fnd_norm_hier_rec;
935
936
937 PROCEDURE delete_fnd_norm_hier_rec(
938 parent IN VARCHAR2,
939 child IN VARCHAR2,
940 range_attr IN VARCHAR2,
941 range_low IN VARCHAR2,
942 range_high IN VARCHAR2,
943 value_set_id IN NUMBER)
944 IS
945 BEGIN
946 DELETE FROM FND_FLEX_VALUE_NORM_HIERARCHY
947 WHERE flex_value_set_id = value_set_id
948 AND parent_flex_value=parent
949 AND range_attribute= range_attr
950 AND child_flex_value_low= range_low
951 AND child_flex_value_high = range_high;
952
953 END delete_fnd_norm_hier_rec;
954
955 FUNCTION access_test RETURN VARCHAR2
956 IS
957 BEGIN
958
959 if(fnd_function.test('FII_DIM_FDHM')) then
960 RETURN('TRUE');
961 else
962 RETURN('FALSE');
963 end if;
964 END access_test;
965
966 END FII_FDHM_PKG;
[Home] [Help]