1 PACKAGE BODY GL_JAHE_PKG as
2 /* $Header: glajaheb.pls 120.29 2006/01/19 10:51:38 knag noship $ */
3
4
5 --
6 -- PUBLIC PROCEDURES
7 --
8 FUNCTION has_loop ( source IN VARCHAR2,
9 target IN VARCHAR2,
10 value_set_id IN NUMBER) RETURN VARCHAR2
11 IS
12 parent VARCHAR2(60);
13 CURSOR find_parent_cursor (child VARCHAR2, vsid NUMBER) IS
14 SELECT parent_flex_value
15 FROM fnd_flex_value_norm_hierarchy
16 WHERE flex_value_set_id = vsid
17 AND range_attribute = 'P'
18 AND child BETWEEN child_flex_value_low
19 AND child_flex_value_high;
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
40 FUNCTION access_test RETURN VARCHAR2
41 IS
42 BEGIN
43
44 if(fnd_function.test('GLJAHE')) then
45 RETURN('TRUE');
46 elsif (fnd_function.test('GLJAHESUPER')) then
47 RETURN('TRUE');
48 else
49 RETURN('FALSE');
50 end if;
51 END access_test;
52
53 FUNCTION modify_range ( parent IN VARCHAR2,
54 child IN VARCHAR2,
55 range_attr IN VARCHAR2,
56 range_low IN VARCHAR2,
57 range_high IN VARCHAR2,
58 value_set_id IN NUMBER) RETURN INTEGER
59 IS
60 range_size NUMBER := 0;
61 sum_flag VARCHAR2(1);
62 new_bound VARCHAR2(150);
63 BEGIN
64 IF ( range_attr = 'P') THEN
65 sum_flag := 'Y';
66 ELSE
67 sum_flag := 'N';
68 END IF;
69
70 SELECT COUNT(*)
71 INTO range_size
72 FROM FND_FLEX_VALUES
73 WHERE flex_value_set_id = value_set_id
74 AND summary_flag = sum_flag
75 AND flex_value BETWEEN range_low AND range_high;
76
77 /* If range is a single valued range, the row can be removed from the
78 norm hierarchy table. */
79 IF ( range_size = 1 ) THEN
80 DELETE FND_FLEX_VALUE_NORM_HIERARCHY
81 WHERE flex_value_set_id = value_set_id
82 AND parent_flex_value = parent
83 AND range_attribute = range_attr
84 AND child_flex_value_low = range_low
85 AND child_flex_value_high = range_high;
86 ELSIF ( child = range_low ) THEN
87 /* If the value to be removed from the range was the lower boundary,
88 the lower boundary of the original range has to be adjusted to be
89 the flex value immediately following the value to be removed.*/
90
91 SELECT MIN(flex_value)
92 INTO new_bound
93 FROM fnd_flex_values
94 WHERE flex_value_set_id = value_set_id
95 AND summary_flag = sum_flag
96 AND flex_value > child
97 AND flex_value <= range_high
98 ORDER BY flex_value;
99
100 IF ( new_bound IS NOT NULL ) THEN
101 UPDATE FND_FLEX_VALUE_NORM_HIERARCHY
102 SET child_flex_value_low = new_bound
103 WHERE flex_value_set_id = value_set_id
104 AND parent_flex_value = parent
105 AND range_attribute = range_attr
106 AND child_flex_value_low = range_low
107 AND child_flex_value_high = range_high;
108 END IF;
109 ELSIF ( child = range_high ) THEN
110 /* If the value to be removed from the range was the upper boundary,
111 the upper boundary of the original range has to be adjusted to be
112 the flex value immediately before the value to be removed. */
113 SELECT MAX(flex_value)
114 INTO new_bound
115 FROM fnd_flex_values
116 WHERE flex_value_set_id = value_set_id
117 AND summary_flag = sum_flag
118 AND flex_value >= range_low
119 AND flex_value < child
120 ORDER BY flex_value;
121
122 IF ( new_bound IS NOT NULL ) THEN
123 UPDATE FND_FLEX_VALUE_NORM_HIERARCHY
124 SET child_flex_value_high = new_bound
125 WHERE flex_value_set_id = value_set_id
126 AND parent_flex_value = parent
127 AND range_attribute = range_attr
128 AND child_flex_value_low = range_low
129 AND child_flex_value_high = range_high;
130 END IF;
131 ELSE
132 /* If the value to be removed falls somewhere between the upper and
133 lower boundaries, the original range has to be removed and 2 new ranges
134 will be created to exclude the value to be removed. */
135
136 DELETE FND_FLEX_VALUE_NORM_HIERARCHY
137 WHERE flex_value_set_id = value_set_id
138 AND parent_flex_value = parent
139 AND range_attribute = range_attr
140 AND child_flex_value_low = range_low
141 AND child_flex_value_high = range_high;
142
143 /* The lower range will contain the same lower bound as the
144 original range, and the upper bound will be the flex value before
145 the value to be removed. */
146 SELECT MAX(flex_value)
147 INTO new_bound
148 FROM fnd_flex_values
149 WHERE flex_value_set_id = value_set_id
150 AND summary_flag = sum_flag
151 AND flex_value >= range_low
152 AND flex_value < child
153 ORDER BY flex_value;
154
155 IF ( new_bound IS NOT NULL ) THEN
156 /* If no 'lower' flex value can be found, no new lower range
157 will be created. */
158 INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
159 ( flex_value_set_id,
160 parent_flex_value,
161 range_attribute,
162 child_flex_value_low,
163 child_flex_value_high,
164 last_update_date,
165 last_updated_by,
166 creation_date,
167 created_by,
168 last_update_login )
169 VALUES
170 ( value_set_id,
171 parent,
172 range_attr,
173 range_low,
174 new_bound,
175 SYSDATE,
176 0,
177 SYSDATE,
178 0,
179 0 );
180 END IF;
181
182 /* The upper range will contain the same upper bound as the
183 original range, and the lower bound will be the flex value after
184 the value to be removed. */
185 SELECT MIN(flex_value)
186 INTO new_bound
187 FROM fnd_flex_values
188 WHERE flex_value_set_id = value_set_id
189 AND summary_flag = sum_flag
190 AND flex_value > child
191 AND flex_value <= range_high
192 ORDER BY flex_value;
193
194 IF ( new_bound IS NOT NULL ) THEN
195 /* If no 'lower' flex value can be found, no new lower range
196 will be created. */
197 INSERT INTO FND_FLEX_VALUE_NORM_HIERARCHY
198 ( flex_value_set_id,
199 parent_flex_value,
200 range_attribute,
201 child_flex_value_low,
202 child_flex_value_high,
203 last_update_date,
204 last_updated_by,
205 creation_date,
206 created_by,
207 last_update_login )
208 VALUES
209 ( value_set_id,
210 parent,
211 range_attr,
212 new_bound,
213 range_high,
214 SYSDATE,
215 0,
216 SYSDATE,
217 0,
218 0 );
219 END IF;
220 END IF;
221 RETURN (1);
222 EXCEPTION
223 WHEN OTHERS THEN
224 RETURN (0);
225 END modify_range;
226
227 FUNCTION has_loop_in_range ( parent IN VARCHAR2,
228 low IN VARCHAR2,
229 high IN VARCHAR2,
230 value_set_id IN NUMBER) RETURN VARCHAR2
231 IS
232 child VARCHAR2(60);
233 CURSOR find_children_cursor (low VARCHAR2, high VARCHAR2, vsid NUMBER) IS
234 SELECT flex_value
235 FROM fnd_flex_values
236 WHERE flex_value_set_id = vsid
237 AND summary_flag = 'Y'
238 AND flex_value BETWEEN low AND high
239 ORDER by flex_value;
240 BEGIN
241 -- 1. if parent is within the range, it has loop
242 if (parent>=low and parent<=high) then
243 return (parent);
244 end if;
245
246 -- 2. if any children inside the range has the parent as children,
247 -- it has loop
248 OPEN find_children_cursor(low, high, value_set_id);
249 LOOP
250 FETCH find_children_cursor INTO child;
251 IF ( find_children_cursor%NOTFOUND ) THEN
252 CLOSE find_children_cursor;
253 RETURN('');
254 ELSIF ( has_loop(child, parent, value_set_id) = 'TRUE' ) THEN
255 CLOSE find_children_cursor;
256 RETURN(child);
257 END IF;
258 END LOOP;
259 CLOSE find_children_cursor;
260 RETURN('');
261 END has_loop_in_range;
262
263 PROCEDURE merge_range (
264 parent IN VARCHAR2,
265 value_set_id IN NUMBER) IS
266 low VARCHAR2(60); -- low of current range
267 high VARCHAR2(60); -- high of current range
268 merged_low VARCHAR2(60) := null; -- low of merged range
269 merged_high VARCHAR2(60) := null; -- high of merged range
270 has_value NUMBER := null; -- 1 if there are flex values between ranges
271 -- cursor for fetching current ranges
272 CURSOR child_range (parent VARCHAR2) IS
273 SELECT child_flex_value_low, child_flex_value_high
274 from gl_ahe_detail_ranges_gt
275 where parent_flex_value = parent
276 and status = 'C'
277 order by child_flex_value_low, child_flex_value_high;
278 BEGIN
279 OPEN child_range(parent);
280 FETCH child_range INTO merged_low, merged_high;
281 IF (not child_range%NOTFOUND) THEN
282 LOOP
283 FETCH child_range INTO low, high;
284 EXIT WHEN child_range%NOTFOUND;
285 -- if overlap with previous range, merge it
286 IF (low <= merged_high) THEN
287 merged_high := high;
288 ELSE
289 -- if non-overlap, merge if no flex values between ranges
290 begin
291 -- ACHI 12/20/2001
292 -- we can use a exists clause here also, but as
293 -- a stable quick fix a rownum limiter is used instead
294 SELECT 1
295 INTO has_value
296 FROM fnd_flex_values
297 WHERE flex_value_set_id = value_set_id
298 AND summary_flag = 'N'
299 AND flex_value > merged_high
300 AND flex_value < low
301 AND rownum <= 1;
302
303 -- no exception => found values in between => end of merge
304 INSERT INTO gl_ahe_detail_ranges_gt
305 (parent_flex_value,
306 child_flex_value_low,
307 child_flex_value_high,
308 status)
309 values (parent, merged_low, merged_high, 'M');
310
311 merged_low := low;
312 merged_high := high;
313 exception
314 -- no values found => merge
315 when no_data_found then
316 merged_high := high;
317 end;
318 has_value := null;
319 END IF;
320 END LOOP;
321 INSERT INTO gl_ahe_detail_ranges_gt
322 (parent_flex_value,
323 child_flex_value_low,
324 child_flex_value_high,
325 status)
326 values (parent, merged_low, merged_high, 'M');
327 END IF;
328 CLOSE child_range;
329
330 END merge_range;
331
332 FUNCTION unique_flex_value (
333 f_value IN VARCHAR2,
334 parent_low IN VARCHAR2,
335 value_set_id IN NUMBER) RETURN VARCHAR2
336 IS
337 row_count number := 0;
338 BEGIN
339 -- try to find another flex value that is the same as the current one
340 SELECT count(*)
341 INTO row_count
342 FROM fnd_flex_values
343 WHERE flex_value_set_id = value_set_id
344 AND flex_value = f_value
345 AND ((parent_low IS null) OR
346 (parent_flex_value_low = parent_low));
347 IF ( row_count > 0 ) THEN
348 RETURN('FALSE');
349 ELSE
350 RETURN('TRUE');
351 END IF;
352 END unique_flex_value;
353
354 FUNCTION getCOAClause( user_id IN NUMBER,
355 resp_id IN NUMBER,
356 appl_id IN NUMBER) RETURN VARCHAR2
357 IS
358
359 CURSOR resp (uid number) IS
360 select responsibility_id, responsibility_application_id
361 from fnd_user_resp_groups
362 where user_id = uid
363 -- and responsibility_application_id = 101
364 and (start_date is null or start_date < sysdate)
365 and (end_date is null or end_date > sysdate);
366
367 rid number(15);
368 coaid number(15);
369 result varchar2(200);
370 sobid varchar2(15);
371 appid number(15);
372 accsetid varchar2(15);
373
374 BEGIN
375
376 FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, appl_id);
377 if (FND_FUNCTION.TEST('GLJAHESUPER')) then
378 return 'ALL';
379 else
380 open resp(user_id);
381 result := ',';
382
383 LOOP
384 fetch resp into rid, appid;
385 exit when resp%NOTFOUND;
386 /*
387 sobid := fnd_profile.value_specific('GL_SET_OF_BKS_ID',
388 user_id, rid, appid);
389
390 if (sobid is not null) then
391 select chart_of_accounts_id into coaid
392 from gl_sets_of_books
393 where set_of_books_id = to_number(sobid);
394 */
395 accsetid := fnd_profile.value_specific('GL_ACCESS_SET_ID',
396 user_id, rid, appid);
397
398 if (accsetid is not null) then
399 select chart_of_accounts_id into coaid
400 from gl_access_sets
401 where access_set_id = to_number(accsetid);
402
403 if (instr(result, ','||to_char(coaid)||',') = 0) then
404 result := result || to_char(coaid) || ',';
405 end if;
406 end if;
407 end loop;
408
409 if (length(result) > 1) then
410 result := substr(result, 2, length(result)-2);
411 return result;
412 else
413 return '-1';
414 end if;
415
416 end if;
417
418 END;
419
420 PROCEDURE lock_flex_value_set (fvsid NUMBER) is
421 lkname varchar2(128);
422 lkhandle varchar2(128);
423 rs_mode constant integer := 5;
424 timout constant integer := 2; -- 2 secs timeout
425 expiration_secs constant integer := 864000;
426 lkresult integer;
427 begin
428 -- generate the name for the user-defined lock
429 lkname := 'FND_FLEX_AHE_VS_' || to_char(fvsid);
430
431 -- get Oracle-assigned lock handle
432 dbms_lock.allocate_unique( lkname, lkhandle, expiration_secs );
433
434 -- request a lock in the ROW SHARE mode
435 lkresult := dbms_lock.request( lkhandle, rs_mode, timout, TRUE );
436
437 if ( lkresult = 0 ) then
438 -- locking was successful
439 return;
440 elsif ( lkresult = 1 ) then
441 -- Account Hierarchy Editor is locking out value set
442 -- print out appropriate warning message
443 fnd_message.set_name('FND', 'FLEX-AHE LOCKING VSET');
444 app_exception.raise_exception;
445 else
446 fnd_message.set_name('FND', 'FLEX-AHE DBMS_LOCK ERROR');
447 app_exception.raise_exception;
448 end if;
449
450
451 END lock_flex_value_set;
452
453 PROCEDURE flatten_hierarchy (fvsid NUMBER) is
454
455 req_id integer;
456
457 begin
458
459 req_id := fnd_request.submit_request(
460 application => 'FND',
461 program => 'FDFCHY',
462 argument1 => TO_CHAR(fvsid)
463 );
464 COMMIT;
465
466 END flatten_hierarchy;
467
468 PROCEDURE insert_tl_records (fvsid NUMBER DEFAULT NULL) is
469
470 cursor installed_lang_cursor is
471 select LANGUAGE_CODE from FND_LANGUAGES
472 where INSTALLED_FLAG in ('B', 'I');
473
474 lang_code VARCHAR(4);
475
476 BEGIN
477
478 IF (fvsid IS NOT NULL) THEN
479
480 OPEN installed_lang_cursor;
481 LOOP
482 FETCH installed_lang_cursor INTO lang_code;
483 IF ( installed_lang_cursor%NOTFOUND ) THEN
484 CLOSE installed_lang_cursor;
485 RETURN;
486 ELSIF ( lang_code <> userenv('LANG') ) THEN
487 insert into FND_FLEX_VALUES_TL (
488 FLEX_VALUE_ID,
489 LAST_UPDATE_DATE,
490 LAST_UPDATED_BY,
491 CREATION_DATE,
492 CREATED_BY,
493 LAST_UPDATE_LOGIN,
494 DESCRIPTION,
495 FLEX_VALUE_MEANING,
496 LANGUAGE,
497 SOURCE_LANG
498 )
499 (select
500 T1.FLEX_VALUE_ID,
501 T1.LAST_UPDATE_DATE,
502 T1.LAST_UPDATED_BY,
503 T1.CREATION_DATE,
504 T1.CREATED_BY,
505 T1.LAST_UPDATE_LOGIN,
506 T1.DESCRIPTION,
507 T1.FLEX_VALUE_MEANING,
508 lang_code,
509 T1.SOURCE_LANG
510 from fnd_flex_values_tl T1
511 -- Bug 4775405, add join
512 ,fnd_flex_values B
513 where language = userenv('LANG')
514 and not exists (select NULL
515 from fnd_flex_values_tl T2
516 where T2.language = lang_code
517 and T2.flex_value_id = T1.flex_value_id)
518 -- Bug 4775405, add filter
519 and T1.flex_value_id = B.flex_value_id
520 and B.flex_value_set_id = fvsid
521 );
522
523 insert into FND_FLEX_HIERARCHIES_TL (
524 FLEX_VALUE_SET_ID,
525 HIERARCHY_ID,
526 HIERARCHY_NAME,
527 LANGUAGE,
528 LAST_UPDATE_DATE,
529 LAST_UPDATED_BY,
530 CREATION_DATE,
531 CREATED_BY,
532 LAST_UPDATE_LOGIN,
533 DESCRIPTION,
534 SOURCE_LANG
535 )
536 (select
537 flex_value_set_id,
538 hierarchy_id,
539 hierarchy_name,
540 lang_code,
541 last_update_date,
542 last_updated_by,
543 creation_date,
544 created_by,
545 last_update_login,
546 description,
547 source_lang
548 from fnd_flex_hierarchies_tl T1
549 where language = userenv('LANG')
550 and not exists (select NULL
551 from fnd_flex_hierarchies_tl T2
552 where T2.language = lang_code
553 and T2.hierarchy_id = T1.hierarchy_id
554 -- Bug 4775405, add filter
555 and T2.flex_value_set_id = T1.flex_value_set_id)
556 -- Bug 4775405, add filter
557 and T1.flex_value_set_id = fvsid
558 );
559
560 END IF;
561 END LOOP;
562
563 END IF;
564
565 END insert_tl_records;
566
567 PROCEDURE launch IS
568 /*
569 sessionCookie VARCHAR2(128) := ICX_CALL.ENCRYPT3(ICX_SEC.getSessionCookie());
570 lang VARCHAR2(128) := ICX_SEC.g_language_code;
571 -- need to escape slash characters in host argument
572 tcfHost VARCHAR2(128) := wfa_html.conv_special_url_chars(
573 FND_PROFILE.VALUE('TCF:HOST'));
574 tcfPort VARCHAR2(128) := FND_PROFILE.VALUE('TCF:PORT');
575 dbc_file VARCHAR2(128) := fnd_web_config.database_id;
576
577 error VARCHAR2(250) := 'You do not have the required security privileges to launch Account Hierarchy Manager. Please contact your System Administrator';
578 */
579 BEGIN
580 -- Stubbed out for bug 4467175
581 NULL;
582 /*
583 if(access_test = 'FALSE') then
584 htp.p(error);
585 elsif (icx_sec.validateSession ) then
586
587 fnd_applet_launcher.launch(
588 applet_class => 'oracle.apps.gl.jahe.javaui.client.Jahe',
589
590 archive_list => 'oracle/apps/gl/jar/glahelcl.jar'
591 ||',jbodatum111.jar'
592 ||',oracle/apps/fnd/jar/fndtcf.jar'
593 ||',oracle/apps/fnd/jar/fndaroraclnt.jar'
594 ||',oracle/apps/fnd/jar/fndconnectionmanager.jar'
595 ||',oracle/apps/fnd/jar/fndjewtall.jar'
596 ||',oracle/apps/fnd/jar/fndjndi.jar'
597 ||',oracle/apps/fnd/jar/fndswingall.jar'
598 ||',oracle/apps/fnd/jar/jbodomorcl.jar'
599 ||',oracle/apps/fnd/jar/jdev-rt.jar'
600 ||',oracle/apps/fnd/jar/fwk_client.jar'
601 ||',oracle/apps/fnd/jar/jboremote.jar'
602 ||',oracle/apps/fnd/jar/fndctx.jar'
603 ||',oracle/apps/fnd/jar/fndcollections.jar'
604 ||'',
605
606 user_args => '&gp1=host&gv1=' ||
607 tcfHost ||
608 '&gp2=port&gv2=' ||
609 tcfPort ||
610 '&gp3=dbc_file&gv3=' ||
611 dbc_file ||
612 '&gp4=debug&gv4=' ||
613 'N' ||
614 -- SessionCookie and Language should be removed
615 -- after full migration to TCF
616 '&gp5=sessionCookie&gv5=' ||
617 sessionCookie ||
618 '&gp6=language&gv6=' ||
619 lang ||
620 '',
621
622 title_app => 'SQLGL',
623 title_msg => 'GL_JAHE_PAGE_TITLE',
624 cache => 'off'
625 );
626
627 END IF;
628 */
629 END launch;
630
631 END GL_JAHE_PKG;
[Home] [Help]