1 package body fnd_help as
2 /* $Header: AFMLHLPB.pls 120.3 2006/05/27 12:20:05 skghosh ship $ */
3
4 -----------------------------------------------------------------------------
5 -- GetLangCode (PRIVATE)
6 -- Parse the langpath and return the language code (ie "US")
7 -----------------------------------------------------------------------------
8 function GetLangCode(
9 langpath in varchar2)
10 return varchar2
11 is
12 delim number;
13 langcode varchar2(4);
14 begin
15 delim := instr(langpath, '@');
16 if (delim = 0) then
17 langcode := upper(langpath); -- no localization code tacked on
18 else
19 langcode := upper(substr(langpath, 1, delim-1));
20 end if;
21 return(langcode);
22 end;
23
24 -----------------------------------------------------------------------------
25 -- GetLocalizationCode (PRIVATE)
26 -- Parse the langpath and return the localization code (ie "@UK")
27 -----------------------------------------------------------------------------
28 function GetLocalizationCode(
29 langpath in varchar2)
30 return varchar2
31 is
32 delim number;
33 hlc varchar2(100);
34 begin
35 delim := instr(langpath, '@');
36 if (delim = 0) then
37 hlc := '';
38 else
39 hlc := upper(substr(langpath, delim));
40 end if;
41 return(hlc);
42 end;
43
44 -----------------------------------------------------------------------------
45 -- Get_As_File (PRIVATE)
46 -- Look up document via filename
47 -----------------------------------------------------------------------------
48 function Get_As_File(
49 file in varchar2,
50 app in varchar2,
51 langpath in varchar2,
52 file_id out nocopy varchar2)
53 return boolean
54 is
55 hlc varchar2(100);
56 langcode varchar2(4);
57 results_head varchar2(100);
58 found boolean := FALSE;
59 fid number;
60
61 cursor file_languages is
62 select distinct
63 l.description longlang,
64 hd.language lang,
65 hd.title title,
66 hd.file_name fn
67 from fnd_help_documents hd,
68 fnd_languages_vl l
69 where hd.file_name = file
70 and hd.application = app
71 and l.language_code = hd.language
72 order by l.description;
73
74 begin
75 hlc := GetLocalizationCode(langpath);
76 langcode := GetLangCode(langpath);
77
78 -------------------------------------
79 -- First, look in current language --
80 -------------------------------------
81 begin
82 select hd.file_id into fid
83 from fnd_help_documents hd
84 where hd.application = app
85 and hd.file_name = file
86 and hd.language = langcode
87 and rownum=1
88 and hd.custom_level =
89 (select max(hd2.custom_level)
90 from fnd_help_documents hd2
91 where hd2.file_name = hd.file_name
92 and hd2.language = hd.language
93 and hd2.application = hd.application);
94
95 file_id := fid;
96 return TRUE;
97
98 exception
99 when no_data_found then
100 null;
101 when others then
102 return FALSE;
103 end;
104
105 ----------------------------------------------------------
106 -- If the file is a .GIF file, it may not be translated --
107 -- For this special case (bug 1762401) we'll look in US --
108 ----------------------------------------------------------
109 if (langcode <> 'US' AND substr(file, -3) = 'GIF') then
110 begin
111 select hd.file_id into fid
112 from fnd_help_documents hd
113 where hd.application = app
114 and hd.file_name = file
115 and hd.language = 'US'
116 and rownum=1
117 and hd.custom_level =
118 (select max(hd2.custom_level)
119 from fnd_help_documents hd2
120 where hd2.file_name = hd.file_name
121 and hd2.language = hd.language
122 and hd2.application = hd.application);
123
124 file_id := fid;
125 return TRUE;
126
127 exception
128 when no_data_found then
129 null;
130 when others then
131 return FALSE;
132 end;
133 end if;
134 return FALSE;
135 exception
136 when others then
137 return FALSE;
138 end;
139
140 -----------------------------------------------------------------------------
141 -- Get_As_Target (PRIVATE)
142 -- Look up document via target
143 -----------------------------------------------------------------------------
144 function Get_As_Target(
145 target in varchar2,
146 app in varchar2,
147 langpath in varchar2,
148 file_id out nocopy varchar2)
149 return boolean
150 is
151 results_head varchar2(100);
152 found boolean := FALSE;
153 hlc varchar2(100);
154 langcode varchar2(4) := GetLangCode(langpath);
155 fid number;
156
157 cursor target_languages is
158 select distinct
159 l.description longlang,
160 hd.language lang,
161 hd.title title,
162 hd.file_name fn
163 from fnd_help_targets ht,
164 fnd_help_documents hd,
165 fnd_languages_vl l
166 where ht.file_id = hd.file_id
167 and ht.target_name = target
168 and hd.application = app
169 and l.language_code = hd.language
170 order by l.description;
171
172 cursor current_language is
173 select hd.file_id
174 from fnd_help_targets ht,
175 fnd_help_documents hd
176 where ht.file_id = hd.file_id
177 and ht.target_name = target
178 and hd.language = langcode
179 and hd.application = app
180 and hd.custom_level =
181 (select max(hd2.custom_level)
182 from fnd_help_documents hd2,
183 fnd_help_targets ht2
184 where ht2.target_name = ht.target_name
185 and ht2.file_id = hd2.file_id
186 and hd2.language = hd.language
187 and hd2.application = hd.application)
188 order by hd.file_id desc;
189
190 begin
191 hlc := GetLocalizationCode(langpath);
192 langcode := GetLangCode(langpath);
193
194 -------------------------------------
195 -- First, look in current language --
196 -------------------------------------
197
198 begin
199
200 open current_language;
201 fetch current_language into fid;
202 if (current_language%notfound) then
203 raise no_data_found;
204 end if;
205 close current_language;
206
207 file_id := fid;
208 return TRUE;
209
210 exception
211 when no_data_found then
212 null;
213 end;
214 return FALSE;
215 exception
216 when others then
217 return FALSE;
218 end;
219
220 -----------------------------------------------------------------------------
221 -- GetHTTPLang (PRIVATE)
222 -- Return the lang code for HTML pages that meet ADA Standards
223 -- Produces the same result as the java function
224 -- oracle.apps.fnd.i18n.util.SSOMapper.getHttpLangFromOracle.
225 -----------------------------------------------------------------------------
226 function GetHTTPLang(
227 langpath in varchar2)
228 return varchar2
229 is
230 delim number;
231 langcode varchar2(4);
232 httplang varchar2(30);
233 begin
234
235 delim := instr(langpath, '@');
236 if (delim = 0) then
237 langcode := upper(langpath); -- no localization code tacked on
238 else
239 langcode := upper(substr(langpath, 1, delim-1));
240 end if;
241
242 begin
243 select lower(iso_language)||'-'||iso_territory
244 into httplang
245 from fnd_languages where language_code = langcode;
246 exception when no_data_found then
247 httplang := langcode;
248 end;
249
250 return(httplang);
251 end;
252
253
254 -----------------------------------------------------------------------------
255 -- Get_Url
256 -- Creates the URL to launch the help system with help document based
257 -- on the specified target.
258 -- IN
259 -- appsname - app code for the document's owning application
260 -- target - name of the help target
261 -- helpsystem - indicates whether user wants to launch full help system
262 -- if FALSE, means they just want to fetch the document.
263 -- targettype - specifies whether the target is a help target or filename.
264 -- (valid values are TARGET or FILE)
265 -----------------------------------------------------------------------------
266 function Get_Url(
267 appsname in varchar2,
268 target in varchar2,
269 HELPSYSTEM in boolean default TRUE,
270 TARGETTYPE in varchar2 default 'TARGET')
271 return varchar2
272 is
273 help_url varchar2(2000);
274 rapp varchar2(50);
275 language varchar2(50);
276 ora_language varchar2(50);
277 ora_territory varchar2(50);
278 hlc varchar2(100);
279 ind varchar2(2) := '';
280 help_target varchar2(512);
281 delim number;
282 helpAgent varchar2(2000);
283 paramChar varchar2(1);
284 begin
285
286 delim := instr(target,'#');
287
288 if (delim = 0) then
289 help_target := target;
290 else
291 help_target := substr(target,1,delim-1) || '%23' || substr(target,delim+1);
292 end if;
293
294 helpAgent := fnd_profile.value('HELP_WEB_AGENT');
295 if(helpAgent is null) then
296 help_url := fnd_profile.value('APPS_SERVLET_AGENT');
297
298 if (help_url is null) then
299 return(null);
300 end if;
301
302 if ( length(help_url) <> instr(help_url,'/',-1) )then
303 help_url := help_url || '/';
304 end if;
305
306 help_url:= help_url||'help';
307 else
308 help_url := helpAgent;
309 end if;
310
311 paramChar := '?';
312
313 hlc := fnd_profile.value('HELP_LOCALIZATION_CODE');
314 if (hlc is not null) then
315 hlc := '@'||hlc;
316 end if;
317
318 if (targettype = 'TARGET') then
319 ind := '@';
320 end if;
321
322 ------------------------------------------------------------
323 -- There is a legacy historical mapping between different --
324 -- applications because some share help bases with others --
325 ------------------------------------------------------------
326 rapp := upper(appsname);
327 if (rapp in ('SQLGL', 'RG')) then
328 rapp := 'GL';
329 -- Bug3770297 Added products PQP,PQH,SSP,HRI
330 elsif (rapp in ('DT', 'FF', 'PAY', 'BEN', 'GHR', 'HR','PQP','PQH','SSP','HRI')) then
331 rapp := 'PER';
332 elsif (rapp = 'SQLAP') then
333 rapp := 'AP';
334 elsif (rapp = 'OFA')then
335 rapp := 'FA';
336 elsif (rapp = 'CST')then
337 rapp := 'BOM';
338 end if;
339
340 select userenv('language')
341 into language
342 from dual;
343
344 language := substr(language,1,instr(language,'.') - 1);
345 ora_language := substr(language, 1,instr(language,'_')-1);
346 ora_territory := substr(language,instr(language,'_')+1);
347
348 return help_url || paramChar ||
349 'locale='||UTL_I18N.MAP_LOCALE_TO_ISO(ora_language,ora_territory)||
350 '&'||'group='||fnd_profile.value('HELP_TREE_ROOT')||':'||fnd_global.current_language|| '&'||'topic='||fnd_global.current_language||hlc||'/'||
351 rapp||'/'||ind||help_target;
352
353 end Get_Url;
354
355 -----------------------------------------------------------------------------
356 -- Get
357 -- Get GFM identifier for help target
358 -- IN
359 -- path - Relative path of target, in the format:
360 -- /<lang>/<app>/<file>
361 -- /<lang>/<app>/@<[app:]target>[,[app:]target,[app:]target...]
362 --
363 -- RETURNS
364 -- returns file_id of the document
365 -----------------------------------------------------------------------------
366 function Get(
367 path in varchar2,
368 file_id out nocopy varchar2)
369 return boolean
370 is
371 lpath varchar2(2000);
372 langpath varchar2(105);
373 langcode varchar2(4);
374 hlc varchar2(100);
375 app varchar2(50);
376 slash number;
377 delim number;
378 ok boolean := FALSE;
379 bad_req exception;
380
381 begin
382 if (path is null) then
383 return false;
384 end if;
385
386 lpath := ltrim(path, '/');
387
388 -- Extract language information --
389 slash := nvl(instr(lpath, '/'), 0);
390 langpath := upper(substr(lpath, 1, slash-1));
391 langcode := GetLangCode(langpath);
392 hlc := GetLocalizationCode(langpath);
393 lpath := substr(lpath, slash+1);
394
395 -- Extract application --
396 slash := nvl(instr(lpath, '/'), 0);
397 app := upper(substr(lpath, 1, slash-1));
398 lpath := substr(lpath, slash+1);
399
400 -----------------------------------------------------------------
401 -- Remainder of lpath is either the target name, file name, or --
402 -- list of targets. --
403 -- --
404 -- If it starts with "@", we know we are dealing with targets, --
405 -- otherwise we have got a file name. --
406 -- --
407 -- Furthermore, we handle lists of targets differently than --
408 -- single target names. --
409 -----------------------------------------------------------------
410 if (substr(lpath, 1, 1) <> '@') then
411 ok := Get_As_File(upper(lpath), app, langpath,file_id);
412 else
413 -- we have a single target --
414 -- synch target translation with load_target() --
415 lpath := upper(translate(substr(lpath, 2),'.','_'));
416
417 -- append the localization code if none specified --
418 if (instr(lpath, '@') = 0) then
419 lpath := lpath||hlc;
420 end if;
421
422 ok := Get_As_Target(lpath, app, langpath,file_id);
423
424 if (ok = FALSE) then
428 lpath := substr(lpath, 1, delim-1);
425 -- remove the localization code and try again --
426 delim := instr(lpath, '@');
427 if (delim <> 0) then
429 ok := Get_As_Target(lpath, app, langpath,file_id);
430 end if;
431 end if;
432 end if;
433
434 if (ok = FALSE) then
435 raise bad_req;
436 end if;
437 return ok;
438 exception
439 when bad_req then
440 Fnd_Message.Set_Name('FND', 'HELP_BAD_TARGET');
441 -- Fnd_Message.Set_Token('TARGET', app||'/'||lpath);
442 Fnd_Message.Set_Token('TARGET', ''); -- Bug 3391291
443 return FALSE;
444 when others then
445 return FALSE;
446 end Get;
447
448 ----------------------------------------------------------------------------
449 -- Help_Search
450 -- Implement search
451 -- IN
452 -- find_string - string to search for
453 -- IN OUT
454 -- results - array of links.
455 --
456 -- This procedure implements the Help Document search and can be called
457 -- by other folks who wish to include help documents in their own search
458 -- results. Takes the search string, parses and reshapes it behave more
459 -- like standard browser searches, finds the matching Help Documents,
460 -- and returns them as an array of links to be displayed by the caller.
461 ----------------------------------------------------------------------------
462 procedure Help_Search(
463 find_string in varchar2 default null,
464 scores in out nocopy results_tab,
465 apps in out nocopy results_tab,
466 titles in out nocopy results_tab,
467 file_names in out nocopy results_tab,
468 langpath in varchar2 default userenv('LANG'),
469 appname in varchar2 default null,
470 lang in varchar2 default null,
471 row_limit in number default null)
472 is
473 selc varchar2(2000) := ' ';
474 andc varchar2(2000) := ' ';
475 mainc varchar2(2000);
476 orderc varchar2(100);
477 pct varchar2(4);
478 title varchar2(256);
479 app varchar2(50);
480 fn varchar2(256);
481 source varchar2(256);
482 langcode varchar2(4);
483 nlslang varchar2(256);
484 appnameClause varchar2(80);
485
486 i number;
487 rows number :=0;
488 cur integer;
489 atg boolean := FALSE;
490
491
492 begin
493
494 if (lang is null) then
495 langcode := GetLangCode(langpath);
496 else
497 langcode := lang;
498 end if;
499
500 if (appname is null) then
501 appnameClause := '';
502 else
503 appnameClause := ' and hd.application = '''||appname||'''';
504 end if;
505
506 -- Set NLS_LANGUAGE if the language code doesn't match userenv('LANG').
507 if (langcode <> userenv('LANG')) then
508 begin
509 select ''''||nls_language||'''' into nlslang from fnd_languages
510 where language_code = langcode;
511 exception
512 when others then
513 nlslang := 'AMERICAN';
514 end;
515 dbms_session.set_nls('NLS_LANGUAGE', nlslang);
516 end if;
517
518
519 mainc := ' hd.title title, '||
520 ' hd.application app, '||
521 ' hd.file_name fn '||
522 ' from fnd_lobs lob, '||
523 ' fnd_help_documents hd '||
524 ' where lob.program_name = ''FND_HELP'' '||
525 ' and upper(lob.file_format) = ''TEXT'' '||
526 ' and hd.file_id = lob.file_id '||
527 ' and hd.language = '''||langcode||''' '||
528 appnameClause ||
529 ' and hd.file_name <> ''ATGRP.GIF'' '||
530 ' and hd.title is not null '||
531 ' and hd.custom_level = '||
532 ' (select /*+no_unnest*/ max(hd2.custom_level) '||
533 ' from fnd_help_documents hd2 '||
534 ' where hd2.file_name = hd.file_name '||
535 ' and hd2.language = hd.language '||
536 ' and hd2.application = hd.application) ';
537
538
539 orderc := ' order by pct desc, title ';
540
541 source := rtrim(find_string, ' ');
542 if (source is NULL) then
543 return;
544 end if;
545
546 ------------------------------------------------------------
547 -- Check for special debug indicator; when TRUE, displays --
548 -- generated where and select clauses --
549 ------------------------------------------------------------
550
551 if (instr(source, 'atgrp') = 1) then
552 atg := TRUE;
553 end if;
554
555 fnd_imutl.parse_search(source, selc, andc, 'LOB.FILE_DATA');
556
557 ------------------------------------------
558 -- Build and execute dynamic SQL cursor --
559 ------------------------------------------
560 cur := dbms_sql.open_cursor;
561 dbms_sql.parse(cur, selc||mainc||andc||orderc, dbms_sql.v7);
562
566 dbms_sql.define_column(cur, 4, fn, 256);
563 dbms_sql.define_column(cur, 1, pct, 3);
564 dbms_sql.define_column(cur, 2, title, 256);
565 dbms_sql.define_column(cur, 3, app, 50);
567
568 rows := dbms_sql.execute(cur);
569
570
571 i := 1;
572 while (TRUE) loop
573 rows := dbms_sql.fetch_rows(cur);
574
575 -- Bug 3315934 Added row_limit to resolve performance issues
576 -- Will display all rows if no limit is specified.
577 if (rows = 0 or (row_limit is not null and i > row_limit)) then
578 exit;
579 end if;
580
581 dbms_sql.column_value(cur, 1, pct);
582 dbms_sql.column_value(cur, 2, title);
583 dbms_sql.column_value(cur, 3, app);
584 dbms_sql.column_value(cur, 4, fn);
585
586 scores(i) := pct;
587 titles(i) := title;
588 apps(i) := app;
589 file_names(i) := fn;
590
591 i := i + 1;
592 end loop;
593 dbms_sql.close_cursor(cur);
594
595 if (atg = TRUE) then
596
597 scores(i) := 0;
598 titles(i) := 'Suprise';
599 apps(i) := 'FND';
600 file_names(i) := 'ATGRP.GIF';
601
602 end if;
603 end Help_Search;
604
605 -----------------------------------------------------------------------------
606 -- Load_Doc
607 -- Load a help document into the database (called by FNDGFH and FNDGFU)
608 --
609 -- If the help document is already there, update it.
610 -- Otherwise, insert a new one.
611 -----------------------------------------------------------------------------
612 procedure Load_Doc (
613 x_file_id in varchar2,
614 x_language in varchar2,
615 x_application in varchar2,
616 x_file_name in varchar2,
617 x_custom_level in varchar2,
618 x_title in varchar2,
619 x_version in varchar2 ) is
620 num_file_id number := to_number(x_file_id);
621 num_custom_level number := to_number(x_custom_level);
622 begin
623 update fnd_help_documents set
624 title = x_title,
625 version = x_version
626 where file_id = num_file_id;
627
628 if SQL%NOTFOUND then
629 insert into fnd_help_documents (
630 file_id,
631 language,
632 application,
633 file_name,
634 custom_level,
635 title,
636 version)
637 values (
638 num_file_id,
639 upper(x_language),
640 upper(x_application),
641 upper(x_file_name),
642 num_custom_level,
643 x_title,
644 x_version);
645 end if;
646 end Load_Doc;
647
648 -----------------------------------------------------------------------------
649 -- load_target
650 -- Load a help target into the database (called by FNDGFH and FNDGFU)
651 --
652 -- x_file_id - the file_id of the owning help document
653 -- x_target_name - the target to load
654 --
655 -- Warning! We are modifying the target here. Therefore, all target
656 -- searches need to do the same modifications in order to
657 -- successfully find the target. Search for keyword 'synch'.
658 -----------------------------------------------------------------------------
659 procedure load_target (
660 x_file_id in varchar2,
661 x_target_name in varchar2 ) is
662 num_file_id number := to_number(x_file_id);
663 begin
664 insert into fnd_help_targets (file_id, target_name)
665 values (num_file_id, upper(translate(x_target_name,'.','_')));
666
667 exception
668 when dup_val_on_index then
669 return;
670 end load_target;
671
672 -----------------------------------------------------------------------------
673 -- cull_row
674 -- Cleanup obsolete rows from fnd_lobs, fnd_help_documents, and
675 -- fnd_help_targets (called by FNDGFH and FNDGFU)
676 --
677 -- Usual case: a new version for this file_id, language, app, file_name
678 -- and custom level has been created. Therefore, it is desirable
679 -- to remove the obsolete records to avoid querying and storing them.
680 --
681 -- Assumption: We can delete all other versions for this record
682 -- because we will always be adding the newest version.
683 -- That is, we don't have to worry about erroneously
684 -- deleting a newer version.
685 --
686 -- Additionally, since there is no other way to delete obsolete targets
687 -- AND all targets for a document are always loaded, it is safest
688 -- to ALWAYS delete ALL of the targets so that we're always up-to-date.
689 -----------------------------------------------------------------------------
690 procedure cull_row (
691 x_file_id in varchar2,
692 x_language in varchar2,
693 x_application in varchar2,
694 x_file_name in varchar2,
695 x_custom_level in varchar2 )
696 is
697 num_file_id number := to_number(x_file_id);
698 num_custom_level number := to_number(x_custom_level);
699 cursor fileid_cursor is
700 select file_id from fnd_help_documents
701 where upper(language) = upper(x_language)
702 and upper(application) = upper(x_application)
703 and upper(file_name) = upper(x_file_name)
704 and custom_level = num_custom_level
708 delete from fnd_help_targets where file_id = f.file_id;
705 and file_id <> num_file_id;
706 begin
707 for f in fileid_cursor loop
709 delete from fnd_lobs where file_id = f.file_id;
710 delete from fnd_help_documents where file_id = f.file_id;
711 end loop;
712 -----------------------------------------------
713 -- Remove ALL help targets for this document --
714 -----------------------------------------------
715 delete from fnd_help_targets where file_id = x_file_id;
716 end cull_row;
717
718 -----------------------------------------------------------------------------
719 -- delete_doc
720 -- Delete a document from the iHelp system
721 -- IN:
722 -- x_application - Application shortname of file owner
723 -- x_file_name - Name of file to delete
724 -- x_language - Language to delete (null for all)
725 -- x_custom_level - Custom level to delete (null for all)
726 -----------------------------------------------------------------------------
727 procedure delete_doc (
728 x_application in varchar2,
729 x_file_name in varchar2,
730 x_language in varchar2 default null,
731 x_custom_level in varchar2 default null)
732 is
733 num_custom_level number := to_number(x_custom_level);
734 cursor fileid_cursor is
735 select fhd.file_id
736 from fnd_help_documents fhd
737 where upper(fhd.application) = upper(x_application)
738 and upper(fhd.file_name) = upper(x_file_name)
739 and upper(fhd.language) = upper(nvl(x_language, fhd.language))
740 and fhd.custom_level = nvl(num_custom_level,
741 fhd.custom_level);
742 begin
743
744 for f in fileid_cursor loop
745 -- Delete all help targets
746 delete from fnd_help_targets fht
747 where fht.file_id = f.file_id;
748
749 -- Help documents...
750 delete from fnd_help_documents fhd
751 where fhd.file_id = f.file_id;
752
753 -- Lobs table...
754 delete from fnd_lobs
755 where file_id = f.file_id;
756
757 end loop;
758
759 end delete_doc;
760
761 end fnd_help;
[Home] [Help]