1 package body fnd_help as
2 /* $Header: AFMLHLPB.pls 120.6 2012/02/28 15:59:49 ctilley 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 -- HELPCONTEXT when false, then help root is taken for the global help content, typically
266 -- represented by FND:LIBRARY.
267 -- When true, then system displays context sensitive help content.
268
269 -----------------------------------------------------------------------------
270 function Get_Url(
271 appsname in varchar2,
272 target in varchar2,
273 HELPSYSTEM in boolean default TRUE,
274 TARGETTYPE in varchar2 default 'TARGET',
275 CONTEXTHELP in boolean default TRUE )
276 return varchar2
277 is
278 help_url varchar2(2000);
279 rapp varchar2(50);
280 language varchar2(50);
281 ora_language varchar2(50);
282 ora_territory varchar2(50);
283 hlc varchar2(100);
284 ind varchar2(2) := '';
285 help_target varchar2(512);
286 delim number;
287 helpAgent varchar2(2000);
288 paramChar varchar2(1);
289 rootVal varchar2(512);
290 begin
291
292 delim := instr(target,'#');
293
294 if (delim = 0) then
295 help_target := target;
296 else
297 help_target := substr(target,1,delim-1) || '%23' || substr(target,delim+1);
298 end if;
299
300 helpAgent := fnd_profile.value('HELP_WEB_AGENT');
301 if(helpAgent is null) then
302 help_url := fnd_profile.value('APPS_SERVLET_AGENT');
303
304 if (help_url is null) then
305 return(null);
306 end if;
307
308 if ( length(help_url) <> instr(help_url,'/',-1) )then
309 help_url := help_url || '/';
310 end if;
311
312 help_url:= help_url||'help';
313 else
314 help_url := helpAgent;
315 end if;
316
317 paramChar := '?';
318
319 hlc := fnd_profile.value('HELP_LOCALIZATION_CODE');
320 if (hlc is not null) then
321 hlc := '@'||hlc;
322 end if;
323
324 if (targettype = 'TARGET') then
325 ind := '@';
326 end if;
327
328 ------------------------------------------------------------
329 -- There is a legacy historical mapping between different --
330 -- applications because some share help bases with others --
331 ------------------------------------------------------------
332 rapp := upper(appsname);
333 if (rapp in ('SQLGL', 'RG')) then
334 rapp := 'GL';
335 -- Bug3770297 Added products PQP,PQH,SSP,HRI
336 elsif (rapp in ('DT', 'FF', 'PAY', 'BEN', 'GHR', 'HR','PQP','PQH','SSP','HRI')) then
337 rapp := 'PER';
338 elsif (rapp = 'SQLAP') then
339 rapp := 'AP';
340 elsif (rapp = 'OFA')then
341 rapp := 'FA';
342 elsif (rapp = 'CST')then
343 rapp := 'BOM';
344 end if;
345
346 select userenv('language')
347 into language
348 from dual;
349
350 language := substr(language,1,instr(language,'.') - 1);
351 ora_language := substr(language, 1,instr(language,'_')-1);
352 ora_territory := substr(language,instr(language,'_')+1);
353
354 if(CONTEXTHELP) then
355 rootVal := fnd_profile.value('HELP_TREE_ROOT');
356 else
357 rootVal := fnd_profile.VALUE_SPECIFIC('HELP_TREE_ROOT', -1, -1, -1, -1, -1);
358 end if;
359
360 return help_url || paramChar ||
361 'locale='||UTL_I18N.MAP_LOCALE_TO_ISO(ora_language,ora_territory)||
362 '&'||'group='||rootVal||':'||fnd_global.current_language|| '&'||'topic='||fnd_global.current_language||hlc||'/'||
363 rapp||'/'||ind||help_target;
364
365 end Get_Url;
366
367 -----------------------------------------------------------------------------
368 -- Get
369 -- Get GFM identifier for help target
370 -- IN
371 -- path - Relative path of target, in the format:
372 -- /<lang>/<app>/<file>
373 -- /<lang>/<app>/@<[app:]target>[,[app:]target,[app:]target...]
374 --
375 -- RETURNS
376 -- returns file_id of the document
377 -----------------------------------------------------------------------------
378 function Get(
379 path in varchar2,
380 file_id out nocopy varchar2)
381 return boolean
382 is
383 lpath varchar2(2000);
384 langpath varchar2(105);
385 langcode varchar2(4);
386 hlc varchar2(100);
387 app varchar2(50);
388 slash number;
389 delim number;
390 ok boolean := FALSE;
391 bad_req exception;
392
393 begin
394 if (path is null) then
395 return false;
396 end if;
397
398 lpath := ltrim(path, '/');
399
400 -- Extract language information --
401 slash := nvl(instr(lpath, '/'), 0);
402 langpath := upper(substr(lpath, 1, slash-1));
403 langcode := GetLangCode(langpath);
404 hlc := GetLocalizationCode(langpath);
405 lpath := substr(lpath, slash+1);
406
407 -- Extract application --
408 slash := nvl(instr(lpath, '/'), 0);
409 app := upper(substr(lpath, 1, slash-1));
410 lpath := substr(lpath, slash+1);
411
412 -----------------------------------------------------------------
413 -- Remainder of lpath is either the target name, file name, or --
414 -- list of targets. --
415 -- --
416 -- If it starts with "@", we know we are dealing with targets, --
417 -- otherwise we have got a file name. --
418 -- --
419 -- Furthermore, we handle lists of targets differently than --
420 -- single target names. --
421 -----------------------------------------------------------------
422 if (substr(lpath, 1, 1) <> '@') then
423 ok := Get_As_File(upper(lpath), app, langpath,file_id);
424 else
425 -- we have a single target --
426 -- synch target translation with load_target() --
427 lpath := upper(translate(substr(lpath, 2),'.','_'));
428
429 -- append the localization code if none specified --
430 if (instr(lpath, '@') = 0) then
431 lpath := lpath||hlc;
432 end if;
433
434 ok := Get_As_Target(lpath, app, langpath,file_id);
435
436 if (ok = FALSE) then
437 -- remove the localization code and try again --
438 delim := instr(lpath, '@');
439 if (delim <> 0) then
440 lpath := substr(lpath, 1, delim-1);
441 ok := Get_As_Target(lpath, app, langpath,file_id);
442 end if;
443 end if;
444 end if;
445
446 if (ok = FALSE) then
447 raise bad_req;
448 end if;
449 return ok;
450 exception
451 when bad_req then
452 Fnd_Message.Set_Name('FND', 'HELP_BAD_TARGET');
453 -- Fnd_Message.Set_Token('TARGET', app||'/'||lpath);
454 Fnd_Message.Set_Token('TARGET', ''); -- Bug 3391291
455 return FALSE;
456 when others then
457 return FALSE;
458 end Get;
459
460 ----------------------------------------------------------------------------
461 -- Help_Search
462 -- Implement search
463 -- IN
464 -- find_string - string to search for
465 -- IN OUT
466 -- results - array of links.
467 --
468 -- This procedure implements the Help Document search and can be called
469 -- by other folks who wish to include help documents in their own search
470 -- results. Takes the search string, parses and reshapes it behave more
471 -- like standard browser searches, finds the matching Help Documents,
472 -- and returns them as an array of links to be displayed by the caller.
473 ----------------------------------------------------------------------------
474 procedure Help_Search(
475 find_string in varchar2 default null,
476 scores in out nocopy results_tab,
477 apps in out nocopy results_tab,
478 titles in out nocopy results_tab,
479 file_names in out nocopy results_tab,
480 langpath in varchar2 default userenv('LANG'),
481 appname in varchar2 default null,
482 lang in varchar2 default null,
483 row_limit in number default null)
484 is
485 selc varchar2(2000) := ' ';
486 andc varchar2(2000) := ' ';
487 mainc varchar2(2000);
488 orderc varchar2(100);
489 pct varchar2(4);
490 title varchar2(256);
491 app varchar2(50);
492 fn varchar2(256);
493 source varchar2(256);
494 langcode varchar2(4);
495 nlslang varchar2(256);
496 appnameClause varchar2(80);
497
498 i number;
499 rows number :=0;
500 cur integer;
501 atg boolean := FALSE;
502
503
504 begin
505
506 if (lang is null) then
507 langcode := GetLangCode(langpath);
508 else
509 langcode := lang;
510 end if;
511
512 if (appname is null) then
513 appnameClause := '';
514 else
515 appnameClause := ' and hd.application = '''||appname||'''';
516 end if;
517
518 -- Set NLS_LANGUAGE if the language code doesn't match userenv('LANG').
519 if (langcode <> userenv('LANG')) then
520 begin
521 select ''''||nls_language||'''' into nlslang from fnd_languages
522 where language_code = langcode;
523 exception
524 when others then
525 nlslang := 'AMERICAN';
526 end;
527 dbms_session.set_nls('NLS_LANGUAGE', nlslang);
528 end if;
529
530
531 mainc := ' hd.title title, '||
532 ' hd.application app, '||
533 ' hd.file_name fn '||
534 ' from fnd_lobs lob, '||
535 ' fnd_help_documents hd '||
536 ' where lob.program_name = ''FND_HELP'' '||
537 ' and upper(lob.file_format) = ''TEXT'' '||
538 ' and hd.file_id = lob.file_id '||
539 ' and hd.language = '''||langcode||''' '||
540 appnameClause ||
541 ' and hd.file_name <> ''ATGRP.GIF'' '||
542 ' and hd.title is not null '||
543 ' and hd.custom_level = '||
544 ' (select /*+no_unnest*/ max(hd2.custom_level) '||
545 ' from fnd_help_documents hd2 '||
546 ' where hd2.file_name = hd.file_name '||
547 ' and hd2.language = hd.language '||
548 ' and hd2.application = hd.application) ';
549
550
551 orderc := ' order by pct desc, title ';
552
553 source := rtrim(find_string, ' ');
554 if (source is NULL) then
555 return;
556 end if;
557
558 ------------------------------------------------------------
559 -- Check for special debug indicator; when TRUE, displays --
560 -- generated where and select clauses --
561 ------------------------------------------------------------
562
563 if (instr(source, 'atgrp') = 1) then
564 atg := TRUE;
565 end if;
566
567 fnd_imutl.parse_search(source, selc, andc, 'LOB.FILE_DATA');
568
569 ------------------------------------------
570 -- Build and execute dynamic SQL cursor --
571 ------------------------------------------
572 cur := dbms_sql.open_cursor;
573 dbms_sql.parse(cur, selc||mainc||andc||orderc, dbms_sql.v7);
574
575 dbms_sql.define_column(cur, 1, pct, 3);
576 dbms_sql.define_column(cur, 2, title, 256);
577 dbms_sql.define_column(cur, 3, app, 50);
578 dbms_sql.define_column(cur, 4, fn, 256);
579
580 rows := dbms_sql.execute(cur);
581
582
583 i := 1;
584 while (TRUE) loop
585 rows := dbms_sql.fetch_rows(cur);
586
587 -- Bug 3315934 Added row_limit to resolve performance issues
588 -- Will display all rows if no limit is specified.
589 if (rows = 0 or (row_limit is not null and i > row_limit)) then
590 exit;
591 end if;
592
593 dbms_sql.column_value(cur, 1, pct);
594 dbms_sql.column_value(cur, 2, title);
595 dbms_sql.column_value(cur, 3, app);
596 dbms_sql.column_value(cur, 4, fn);
597
598 scores(i) := pct;
599 titles(i) := title;
600 apps(i) := app;
601 file_names(i) := fn;
602
603 i := i + 1;
604 end loop;
605 dbms_sql.close_cursor(cur);
606
607 if (atg = TRUE) then
608
609 scores(i) := 0;
610 titles(i) := 'Suprise';
611 apps(i) := 'FND';
612 file_names(i) := 'ATGRP.GIF';
613
614 end if;
615 end Help_Search;
616
617 -----------------------------------------------------------------------------
618 -- Load_Doc
619 -- Load a help document into the database (called by FNDGFH and FNDGFU)
620 --
621 -- If the help document is already there, update it.
622 -- Otherwise, insert a new one.
623 -----------------------------------------------------------------------------
624 procedure Load_Doc (
625 x_file_id in varchar2,
626 x_language in varchar2,
627 x_application in varchar2,
628 x_file_name in varchar2,
629 x_custom_level in varchar2,
630 x_title in varchar2,
631 x_version in varchar2 ) is
632 num_file_id number := to_number(x_file_id);
633 num_custom_level number := to_number(x_custom_level);
634 begin
635 -- Bug 13689517 Adding PREPARE calls for Online patching for seed tables.
636 AD_ZD_SEED.PREPARE('FND_HELP_DOCUMENTS');
637
638 update fnd_help_documents set
639 title = x_title,
640 version = x_version
641 where file_id = num_file_id;
642
643 if SQL%NOTFOUND then
644 insert into fnd_help_documents (
645 file_id,
646 language,
647 application,
648 file_name,
649 custom_level,
650 title,
651 version)
652 values (
653 num_file_id,
654 upper(x_language),
655 upper(x_application),
656 upper(x_file_name),
657 num_custom_level,
658 x_title,
659 x_version);
660 end if;
661 end Load_Doc;
662
663 -----------------------------------------------------------------------------
664 -- load_target
665 -- Load a help target into the database (called by FNDGFH and FNDGFU)
666 --
667 -- x_file_id - the file_id of the owning help document
668 -- x_target_name - the target to load
669 --
670 -- Warning! We are modifying the target here. Therefore, all target
671 -- searches need to do the same modifications in order to
672 -- successfully find the target. Search for keyword 'synch'.
673 -----------------------------------------------------------------------------
674 procedure load_target (
675 x_file_id in varchar2,
676 x_target_name in varchar2 ) is
677 num_file_id number := to_number(x_file_id);
678 begin
679
680 -- Bug 13689517 Adding PREPARE calls for Online patching for seed tables
681 AD_ZD_SEED.PREPARE('FND_HELP_TARGETS');
682
683 insert into fnd_help_targets (file_id, target_name)
684 values (num_file_id, upper(translate(x_target_name,'.','_')));
685
686 exception
687 when dup_val_on_index then
688 return;
689 end load_target;
690
691 -----------------------------------------------------------------------------
692 -- cull_row
693 -- Cleanup obsolete rows from fnd_lobs, fnd_help_documents, and
694 -- fnd_help_targets (called by FNDGFH and FNDGFU)
695 --
696 -- Usual case: a new version for this file_id, language, app, file_name
697 -- and custom level has been created. Therefore, it is desirable
698 -- to remove the obsolete records to avoid querying and storing them.
699 --
700 -- Assumption: We can delete all other versions for this record
701 -- because we will always be adding the newest version.
702 -- That is, we don't have to worry about erroneously
703 -- deleting a newer version.
704 --
705 -- Additionally, since there is no other way to delete obsolete targets
706 -- AND all targets for a document are always loaded, it is safest
707 -- to ALWAYS delete ALL of the targets so that we're always up-to-date.
708 -----------------------------------------------------------------------------
709 procedure cull_row (
710 x_file_id in varchar2,
711 x_language in varchar2,
712 x_application in varchar2,
713 x_file_name in varchar2,
714 x_custom_level in varchar2 )
715 is
716 num_file_id number := to_number(x_file_id);
717 num_custom_level number := to_number(x_custom_level);
718 cursor fileid_cursor is
719 select file_id from fnd_help_documents
720 where upper(language) = upper(x_language)
721 and upper(application) = upper(x_application)
722 and upper(file_name) = upper(x_file_name)
723 and custom_level = num_custom_level
724 and file_id <> num_file_id;
725 begin
726 -- Bug 13689517 Adding PREPARE calls for Online patching for seed tables.
727 -- FND_LOBS has not been designated as a seed table so no PREPARE is needed.
728 AD_ZD_SEED.PREPARE('FND_HELP_TARGETS');
729 AD_ZD_SEED.PREPARE('FND_HELP_DOCUMENTS');
730
731 for f in fileid_cursor loop
732 delete from fnd_help_targets where file_id = f.file_id;
733 delete from fnd_lobs where file_id = f.file_id;
734 delete from fnd_help_documents where file_id = f.file_id;
735 end loop;
736 -----------------------------------------------
737 -- Remove ALL help targets for this document --
738 -----------------------------------------------
739 delete from fnd_help_targets where file_id = x_file_id;
740 end cull_row;
741
742 -----------------------------------------------------------------------------
743 -- delete_doc
744 -- Delete a document from the iHelp system
745 -- IN:
746 -- x_application - Application shortname of file owner
747 -- x_file_name - Name of file to delete
748 -- x_language - Language to delete (null for all)
749 -- x_custom_level - Custom level to delete (null for all)
750 -----------------------------------------------------------------------------
751 procedure delete_doc (
752 x_application in varchar2,
753 x_file_name in varchar2,
754 x_language in varchar2 default null,
755 x_custom_level in varchar2 default null)
756 is
757 num_custom_level number := to_number(x_custom_level);
758 cursor fileid_cursor is
759 select fhd.file_id
760 from fnd_help_documents fhd
761 where upper(fhd.application) = upper(x_application)
762 and upper(fhd.file_name) = upper(x_file_name)
763 and upper(fhd.language) = upper(nvl(x_language, fhd.language))
764 and fhd.custom_level = nvl(num_custom_level,
765 fhd.custom_level);
766 begin
767
768
769 for f in fileid_cursor loop
770 -- Delete all help targets
771 delete from fnd_help_targets fht
772 where fht.file_id = f.file_id;
773
774 -- Help documents...
775 delete from fnd_help_documents fhd
776 where fhd.file_id = f.file_id;
777
778 -- Lobs table...
779 delete from fnd_lobs
780 where file_id = f.file_id;
781
782 end loop;
783
784 end delete_doc;
785
786 end fnd_help;
[Home] [Help]