Tuesday, August 12, 2003

Importing and converting 600 word documents into a database as clean html

The following script is used to import 600 Word documents into a database as cleaned html.

#!c:\oracle\ora81/Apache/perl/5.00503/bin/MSWin32-x86/perl.exe

#################################################################################
# Requirements:
# ------------
#
# - Perl 5.00503 or later installed and working on your machine
# - change the first line in this script to point to the perl executable
#
#
#
# Modules:
# --------
#
# - you will need (HTML::Parser, HTML::Treebuilder )
# - install instrucitons
#      win32: (you need Visual Studio to install manually. If you are using ActiveState
#             Perl then use the Perl Package Manager they provide else see below)
#          1. go to www.cpan.org browse the modules and find the above mentioned
#             modules.
#          2. download
#          3. Unzip/Untar
#          4. Open a command prompt in the HTML::Parser unziped directory
#          5. type> perl makefile.pl
#          6. type> nmake
#          7. type> nmake test
#          8. type> nmake install
#          9. do the same for the HTML::Treebuilder directory
#
#
#
#
# Description:
# ------------
#
# This script was created to import 600 word documents as html into a Oracle database.
#
# This script is step 2 in an migration process including an vbs (Visual Basic Script)
#
# Step 1: Convert all documents from MS Word to HTML
#
#           Option Explicit
#
#           Dim wordObj, fsObj
#
#           Set wordObj = WScript.CreateObject("Word.Application")
#           Set fsObj = WScript.CreateObject("Scripting.FileSystemObject")
#
#           ' SubRoutine call convert all files in folder to HTML
#           WordToHTML fsObj.GetFolder("..")
#
#           wordObj.Quit
#
#           Set fsObj = Nothing
#           Set wordObj = Nothing
#
#
#
#           '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#           ' Subroutines
#           '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#           Sub WordToHTML(Folder)
#              On Error Resume Next
#              Dim File
#              For Each File In Folder.Files
#                 Set opgaveDoc = wordObj.Documents.Open (File.Path)
#                 wordobj.ActiveDocument.SaveAs replace(replace(replace(Replace(file.path, " ", "_"),"å","aa"),"æ","ae"),"ø","oe") & ".htm", 8
#                 wordobj.ActiveDocument.close
#              Next
#           End Sub
#
#
#
# Step 2: this script
#         - clean the MS HTML using HMTLTidy
#         - locate the data in the cleaned html document
#         - clean data of unneccesary formating
#         - insert the found data in the wanted format (HTML template)
#         - generate SQL statement to insert task into database
#         - execute SQL statement
#         - log the process
#
#################################################################################


#################################################################################
# INCLUDE MODULES
#################################################################################

#use Carp;
#use diagnostics;
use DBI;
use HTML::TreeBuilder;



#################################################################################
# VARABLE DECLARATION
#################################################################################

my $user    = "USERNAME";                    # database username
my $passwd  = "PASSWORD";                    # database password
my $driver  = "Oracle";                   # DBD (DataBase Driver)
my $db_name = "DATABASENAME;                    # database server instance name (tnsname)

my $pathHtmlBackup = ".\\temp\\";         # where to put the original backup file
my $pathLogFile = ".\\";                  # where to put the processing log file
my $pathDocuments = ".\\doc\\";           # path to where the word documents are located

my $doDatabaseUpdate = 1;                 # used during development 0=off, 1=on

my $DBITraceEnabled = 0;                  # If you want DBI tracing: 1=on, 0=off
my $DBITraceLevel = 2;                    # DBI tracing level (1-9). 1 or 2 should be sufficient
my $DBITraceFilePath = "c:\\trace.log";   # Path to where you want the trace file. On win32 remember to escape the \ with
                                          # another \ so "c:\trace.log" would become "c:\\trace.log"



#################################################################################
# SUB ROUTINE DECLARATION
#################################################################################

sub decodeHTMLTask;



#################################################################################
# MAIN SCRIPT FLOW
#################################################################################

print "Connecting to database. Please wait ...\n";

my $dataSource = "dbi:$driver:${db_name}";
my $dbh = DBI->connect($dataSource, $user, $passwd) || die "Error connecting to database: $DBI::errstr\n";
if ($DBITraceEnabled) {$dbh->trace($DBITraceLevel, $DBITraceFilePath);}

# Make sure buffer is big enough for CLOB, BLOB
$dbh->{LongReadLen}=500 * 1024;  # 500KB

# Getting all the html docs from $pathDocuments
@htmlFileList = `dir /b *.htm`;
chop @htmlFileList;

open (LOGFILE, ">migration.log.txt") || die ("Could not open log file");

# Process each an every file with a html extension found in the current directory
foreach $file (@htmlFileList) {

   # Backup the converted word document
   $cmd = `copy $file ${pathHtmlBackup}${file}.org.html`;
   print LOGFILE ("Backup file : copy $file ${pathHtmlBackup}${file}.org.html\n");

   # Run the html file through HTMLTidy which cleans out the html
   $cmd = `TidyDbg.exe -config clean.cfg $file`;
   print LOGFILE ("Clean file  : TidyDbg.exe -config clean.cfg $file\n");

   # Move the cleaned html file
   $cmd = `move $file ${pathHtmlBackup}${file}.clean.html`;
   print LOGFILE ("Move file   : move $file ${pathHtmlBackup}${file}.clean.html\n");

   open (CLEANHTML, "${pathHtmlBackup}${file}.clean.html") || die ("Cannot open file ${pathHtmlBackup}${file}.clean.html\n");
   open (CLEANNOBRHTML, ">${pathHtmlBackup}${file}.clean.nobr.html") || die ("Cannot open file ${pathHtmlBackup}${file}.clean.nobr.html\n");

   # Removing all newline characters (editlive does not like them)

   # Read the file assocsiated with filehandle CLEANHTML into array @lines
   @lines = <CLEANHTML>;

   # Chop of the last character in every item in array
   #chop (@lines);

   # Join the array to a single string
   $line = join ("",@lines);
   $line =~ s/\n/ /g;

   print CLEANNOBRHTML $line;

   close (CLEANNOBRHTML);
   close (CLEANHTML);

   # Build insert statement based on information found in the html file
   &decodeHTMLTask($file);
} # end foreach

close (LOGFILE);


#################################################################################
# SUB ROUTINES
#################################################################################



#################################################################################
# COMMENTS: "decodeHTMLTask"
#---------------------------
#
# INPUT  : FILENAME (TEXT STRING) EX. something.html
# FUNCTION: PARSE THE FILES DOCUMENT TREE STRUCTURE AND GRAB THE INFORMATION
#           WHICH IS NEEDED. BUILD THE SQL INSERT STATEMENT BASED ON THIS
#           INFORMATION.
# OUTPUT  : SQL INSERT STATEMENT
#################################################################################
sub decodeHTMLTask{
   my ($file) = @_;

   my $tree = HTML::TreeBuilder->new;
   $tree->parse_file("${pathHtmlBackup}${file}.clean.nobr.html");

   #$tree->dump;           # Uncomment this line for document tree dump
   print LOGFILE ("Processing  : ${pathHtmlBackup}${file}.clean.nobr.html...\n");
   print ("Processing  : ${pathHtmlBackup}${file}...\n");

   # Get title from html document.
   my $tref = $tree->content->[1]->content->[0]->content->[2]->content->[1]->content->[1];
   my $taskTitle = $tref->as_text;

   # Get stage (PÃ¥ scenen) from html document
   my $tref = $tree->content->[1]->content->[0]->content->[2]->content->[2]->content->[0];
   my $onStage = $tref->as_text;
   # Since we know that stage has to be either onStage or offStage we already know
   # which one it is.

   # Get priority (Need) from html document
   my $tref = $tree->content->[1]->content->[0]->content->[2]->content->[2]->content->[2];
   my $need = $tref->as_text;
   # Since we know that need has to be either need or nice we already know
   # which one it is.

   # Determinig what priority id we need
   if ($onStage eq "x") {
      if ($need eq "x") {
         $priorityId = 1;
      }
      else {
         $priorityId = 2;
      }
   }
   else {
      if ($need eq "x") {
         $priorityId = 3;
      }
      else {
         $priorityId = 4;
      }
   }

   # Get (Hvorfor) from html document
   my $tref = $tree->content->[1]->content->[0]->content->[2]->content->[4]->content->[1];
   my $hvorfor = $tref->as_text;

   # Get (Hvordan) from html document
   my $tref = $tree->content->[1]->content->[0]->content->[2]->content->[6]->content->[0];
   my $hvordan = $tref->as_HTML;

   # We need to remove a start/tags td (tabledata) fomr the hvordan data
   # to be able to use the data in to input template
   $hvordan =~ s/<td colspan=11 valign="top">//ig;
   $hvordan =~ s/<\/td>//ig;

   # Get (Links) from html document
   my $tref = $tree->content->[1]->content->[0]->content->[2]->content->[8]->content->[0]->content->[0];
   my $links = $tref->as_HTML;

   # Clear memory
   $tree->delete;

   $sql = "select task_id_seq.nextval from dual";
   $sth = $dbh->prepare($sql) || die "\nPrepare error: $dbhI::err .... $dbhI::errstr\n";;
   $sth->execute()  || die "\nExecute error: $dbhI::err .... $dbhI::errstr\n";
   $sth->bind_col( 1, \$task_id );
   $sth->fetch();

   # Building the task description text
   my $HTMLTask = "<br><TABLE cellSpacing=0 cellPadding=0 border=1><TR><TD vAlign=top width=571><H5><FONT face=Verdana>Hvorfor</FONT></H5><P><FONT face=Verdana>$hvorfor</FONT></P><P>&nbsp;</P></TD></TR><TR><TD vAlign=top width=571><H5><FONT face=Verdana>Hvordan</FONT></H5><P><FONT face=Verdana>$hvordan</FONT></P><P>&nbsp;</P></TD></TR><TR><TD vAlign=top width=571><H5><FONT face=Verdana>Links</FONT></H5><P><FONT face=Verdana>$links</FONT></P><P>&nbsp;</P></TD></TR></TABLE><br>";
   $HTMLTask =~ s/\n/ /g;

   # SQL statement needed to inser task into database
   my $sqlInsertTask = "
      INSERT INTO TASK (
         task_id,
         gro_id,
         name,
         task_flag,
         text,
         priority_id,
         user_created,
         date_created,
         user_changed,
         date_changed)
      VALUES (
         :task_id,
         task_admin_pack.getPrimgroid(),
         :taskTitle,
         'T',
         :HTMLTask,
         :priorityId,
         'Task massload script',
         sysdate,
         'Task massload script',
         sysdate)
   ";

   $sth = $dbh->prepare($sqlInsertTask) || die "\nPrepare error: $dbhI::err .... $dbhI::errstr\n";;
   $sth->bind_param(":task_id", $task_id);
   $sth->bind_param(":taskTitle", $taskTitle);
   $sth->bind_param(":HTMLTask", $HTMLTask);
   $sth->bind_param(":priorityId", $priorityId);
   $sth->execute()  || die "\nExecute error: $dbhI::err .... $dbhI::errstr\n";

   print LOGFILE ("Execute SQL : INSERT INTO TASK (task_id, gro_id, name, task_flag, text, priority_id, user_created, date_created, user_changed, date_changed) VALUES ($task_id, TASK_admin_pack.getPrimgroid(), '$taskTitle', 'T', '$HTMLTask', '$priorityId', 'Task massload script', sysdate, 'Task massload script', sysdate)\n;");

   # Generate insert statement needed to insert into all search
   my $sqlInsertAllSearch = "
      INSERT INTO all_search (
         as_id,
         table_id,
         all_searchfield,
         com_id,
         gro_id,
         pro_id,
         table_name,
         id_field_name,
         owner_field_name,
         access_table_name)
      values (
         as_seq.nextval,
         :task_id,
         :v_temptext,
         :v_componentID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL)
   ";

   $sth = $dbh->prepare($sqlInsertAllSearch) || die "\nPrepare error: $dbhI::err .... $dbhI::errstr\n";;
 $sth->bind_param(":task_id", $task_id);
 $sth->bind_param(":v_temptext", $taskTitle . " " . $HTMLTask);
 $sth->bind_param(":v_componentID", 42);
   $sth->execute()  || die "\nExecute error: $dbhI::err .... $dbhI::errstr\n";

   print LOGFILE ("Execute SQL : INSERT INTO all_search (as_id, table_id, all_searchfield, com_id, gro_id, pro_id, table_name, id_field_name, owner_field_name, access_table_name) VALUES (as_seq.nextval, $p_taskid,  '$taskTitle $HTMLTask', $v_componentID, $v_groid, NULL, NULL, NULL, NULL, NULL);\n");

}

This is the configuration settings use together with Html Tidy for windows

word-2000: yes
show-body-only: yes
clean: yes
char-encoding: raw
quiet: yes
show-errors: 0
show-warnings: no
drop-empty-paras: no
indent-spaces: 3
new-inline-tags: o:p
doctype: omit
tidy-mark: no
quote-ampersand: no
write-back: yes
force-output: yes

Sunday, May 18, 2003

Menu Dashlet using the page hierachy in Oracle Portal (PL/SQL)

While going through my recently found backup hard disk containing a lot of source code dating back to 1996 I found a few scripts and programs I thought might be worth mentioning.
Op top of the above mentioned I also found this PL/SQL & Javascript tree menu portlet which were written in 2002 and 2003. It is supposed to generate and show the page structure inside Oracle Portal.


First we have the PL/SQL specification for the MENU_PCK_PROVIDER

CREATE OR REPLACE package MENU_PCK_PROVIDER
is
    /**
    * Provider for menu portlet
    *
    * MENU_PCK_PROVIDER
    *
    * Version History:
    * 03 Mar 2003       1.0      Kenneth Thorman      Original Version
    */
    MENU_PORTLET           constant integer := 1;

    /**
    * Get the specification version that this provider adheres to.
    *
    * For the initial release, this function will need to return
    * wwpro_api_provider.API_VERSION_1. The framework calls
    * this method to determine the way in which it should
    * interact with the provider.
    *
    * @param   p_provider_id   A unique identifier for the provider.
    * @returns The API version that this provider adheres to.
    */
    function get_api_version
    (
        p_provider_id in integer
    )
    return integer;

   /**
    * This procedure is called when a provider is registered using
    * the provider registry UI or APIs. This provides the provider
    * an opportunity to perform provider level initializations.
    *
    * @param   p_provider_id   A unique identifier for the provider.
    */
    procedure register_provider
    (
        p_provider_id in integer
    );

   /**
 * This procedure is called  when a provider is deregistered using
    * the provider registry UI or APIs. This provides the
    * providers an opportunity to perform provider level cleanup.
    *
    * @param   p_provider_id   A unique identifier for the provider.
    */
    procedure deregister_provider
    (
        p_provider_id in integer
    );

   /**
    * Permit the provider to perform Login-based processing before any
    * portlets are executed.
    *
    * @param   p_provider_id      A unique identifier for the provider.
    * @param   p_browser_cookies  The cookies that exist at the current
    *                             browser session.
    * @param   p_provider_cookies The cookies that this provider generates.
    */
    procedure do_login
    (
         p_provider_id in integer
        ,p_browser_cookies in wwpro_api_provider.cookie_table
        ,p_provider_cookies out wwpro_api_provider.cookie_table
    );

  /**
    * Get the properties for a portlet.
    *
    * This function returns, in a wwpro_api_provider.portlet_record
    * structure, the details of a specific portlet.
    *
    * @param   p_provider_id    A unique identifier for the provider.
    * @param   p_portlet_id     A unique identifier within the provider
    *                           domain for the portlet.
    * @param   p_language       The language in which to return the
    *                           translatable strings of the portlet.
    * @returns A portlet record (wwpro_api_provider.portlet_record)
    *          that contains the properties of the portlet.
    */
    function get_portlet
    (
         p_provider_id in integer
        ,p_portlet_id in integer
        ,p_language in varchar2
    )
    return wwpro_api_provider.portlet_record;

   /**
    * Return a list of portlets.
    *
    * This function returns, in a wwpro_api_provider.portlet_table
    * structure, a list of portlets and their properties that
    * are available from this provider.
    *
    * This function may be called in 2 modes.  These 2 modes are
    * determined by the value of the p_security_level parameter.
    *
    * If p_security_level is false this method should generate a
    * list of portlets implemented by this provider without
    * respect to any portlet security checks.  The framework will
    * call this method with p_security_level set to false when
    * it performs a refresh of the portlet repository in order to
    * get the complete list of portlets for this provider that
    * will be made available in the portal.
    * If p_security_level is true this method should generate a
    * list of portlets in which a security check is performed
    * for the portlets.  Such a call may be performed by the
    * framework to retrieve the list of portlets to display
    * on the Add Portlets screen.  When generating this
    * list the currently logged on user may be taken into account
    * so that only the portlets that the user can access are
    * returned.
    *
    * @param   p_provider_id    A unique identifier for the provider.
    * @param   p_start_row      The starting index of the table in which the
    *                           portlets are returned into.
    * @param   p_rowcount       The number of portlets returned by this call.
    * @param   p_language       The language in which the return the
    *                           translatable strings into.
    * @param   p_security_level
    * @param   p_type
    * @returns A table of portlets in a portlet table structure
    *          (wwpro_api_provider.portlet_table).
    */
    function get_portlet_list
    (
         p_provider_id in integer
        ,p_start_row in integer
        ,p_rowcount in integer
        ,p_language in varchar2
        ,p_security_level in boolean
        ,p_type in integer
    )
    return wwpro_api_provider.portlet_table;

   /**
    * Return whether this portlet can be viewed by the current user.
    *
    * This method may be called in one of the following 2 situations:
    * 1 - When a request is made to display the portlet in any of the
    *     show modes.  In this case the reference_path field of the
    *     p_portlet_instance record is not null.  This is a situation
    *     in which portlet instance security check should be made.
    * 2 - When the portlet repository is displayed a call is made to
    *     the provider (i.e. is_portlet_runnable) for each portlet
    *     to check whether this portlet should be shown to the user.
    *     This is the situation in which general portlet security
    *     check is performed.  In this case the reference_path field
    *     of the p_portlet_instance record is null.
    *
    * The portlet may use the context package wwctx_api in the
    * implementation of the portlet-specific security mechanism for
    * each situation described above.
    *
    * @param   p_portlet_instance   A portlet instance record that contains
    *                               information for the portlet instance.
    * @returns A boolean value indicating whether the portlet can
    *          be run or not.
    */
    function is_portlet_runnable
    (
        p_portlet_instance in wwpro_api_provider.portlet_instance_record
    )
    return boolean;

   /**
    * Register the usage of a portlet on a page.
    *
    * The framework will call upon this function when the portlet is
    * put on a page.  This provides the portlet an opportunity to perform
    * instance-level initialization such as the establishment of defaults
    * for end-user customization.
    *
    * @param   p_portlet_instance   A portlet instance record that contains
    *                               information for the portlet instance.
    */
    procedure register_portlet
    (
        p_portlet_instance in wwpro_api_provider.portlet_instance_record
    );

   /**
    * Deregister a usage of a portlet on a page.
    *
    * The framework will call upon this function when a portlet is
    * removed from a page.  This provides the portlet an opportunity to
    * perform instance-level cleanup such as the removal of end-user
    * and default customizations.
    *
    * @param   p_portlet_instance   A portlet instance record that contains
    *                               information for the portlet instance.
    */
    procedure deregister_portlet
    (
        p_portlet_instance in wwpro_api_provider.portlet_instance_record
    );

   /**
    * Show the portlet.
    *
    * When called, the portlet should produce the necessary HTML or
    * XML/XSL to render itself for the selected mode.  The page
    * produced should contain the restricted set of HTML Markup that
    * would be legal to place within a cell of a HTML Table (TD
    * element).  For example, it should not have the HEAD or BODY
    * html tags since these will be applied by the Portal framework.
    *
    * The content generated by the portlet should use the tags from
    * the cascading style sheet (CSS).
    *
    * The following are the valid show modes for a portlet:
    *
    * MODE_SHOW - Shows the portlet's content on a page
    * MODE_SHOW_ABOUT - Shows the about content of the portlet on
    *                   a page.  The about content may contain
    *                   the portlet's version number, date, etc.
    * MODE_SHOW_EDIT - Shows the Edit (i.e. Customize) UI
    * MODE_SHOW_HELP - Shows the Help for the portlet
    * MODE_SHOW_EDIT_DEFAULTS - Shows the UI to edit the default
    *                           customizations for the portlet
    * MODE_SHOW_DETAILS - Shows the details page for the portlet
    * MODE_PREVIEW - Shows the preview of the portlet
    *
    * @param   p_portlet_record    A portlet runtime record that contains
    *                              information passed by the framework and
    *                              is used by the portlet.
    */
    procedure show_portlet
    (
        p_portlet_record in out wwpro_api_provider.portlet_runtime_record
    );

    /**
    * Copy a portlets customization and defaults from one portlet
    * to another.
    *
    * This procedure is called when a page is copied and the page
    * contains a portlet implemented by this provider.
    * It is the responsibility of this API to copy the appropriate
    * portlet customizations.  The register_portlet API is called
    * prior to this one.
    *
    * @param p_copy_portlet_info    Source and destination portlet info.
    */
    procedure copy_portlet
    (
         p_copy_portlet_info in wwpro_api_provider.copy_portlet_record
    );

   /**
    * Describe the parameters that the portlet understands.
    *
    * The framework will call upon this function if it needs
    * descriptive information about the parameters to the portlet.
    *
    * @param   p_provider_id    A unique identifier for the provider.
    * @param   p_portlet_id     The id of the portlet.
    * @param   p_language       The language in which to return the
    *                           translatable strings into.
    */
    function describe_portlet_parameters
    (
         p_provider_id in integer
        ,p_portlet_id in integer
        ,p_language in varchar2
    )
    return wwpro_api_provider.portlet_parameter_table;

end MENU_PCK_PROVIDER;
/

PL/SQL body for the MENU_PCK_PROVIDER

CREATE OR REPLACE package body MENU_PCK_PROVIDER
is

    /**
     * Public APIs for the Sample_Provider provider.
     *
     * These methods are required by the provider framework in order
     * to support this provider implementation.
     */

    function get_api_version
    (
        p_provider_id in integer
    )
    return integer
    is
    begin

        return wwpro_api_provider.API_VERSION_1;

    end get_api_version;

    procedure register_provider
    (
        p_provider_id in integer
    )
    is
    begin
        null;
    end register_provider;

    procedure deregister_provider
    (
        p_provider_id in integer
    )
    is
    begin
        null;
    end deregister_provider;

    procedure do_login
    (
         p_provider_id in integer
        ,p_browser_cookies in wwpro_api_provider.cookie_table
        ,p_provider_cookies out wwpro_api_provider.cookie_table
    )
    is
    begin
        null;
    end do_login;

    function get_portlet
    (
         p_provider_id in integer
        ,p_portlet_id in integer
        ,p_language in varchar2
    )
    return wwpro_api_provider.portlet_record
    is
    begin

        if (p_portlet_id = MENU_PORTLET) then
            return menu.menu_pck_portlet.get_portlet_info(
                 p_provider_id  => p_provider_id
                ,p_language     => p_language
                );
        else
            raise wwpro_api_provider.PORTLET_NOT_FOUND_EXCEPTION;
        end if;

    end get_portlet;

    function get_portlet_list
    (
         p_provider_id in integer
        ,p_start_row in integer
        ,p_rowcount in integer
        ,p_language in varchar2
        ,p_security_level in boolean
        ,p_type in integer
    )
    return wwpro_api_provider.portlet_table
    is
        l_portlet_list  wwpro_api_provider.portlet_table;
        l_cnt   number;
    begin

        l_cnt := 0;

        if (menu.menu_pck_portlet.is_runnable(
             p_provider_id     =>  p_provider_id
            ,p_reference_path  =>  null)
        ) then

            l_cnt := l_cnt + 1;
            l_portlet_list(l_cnt) := get_portlet(
                 p_provider_id  => p_provider_id
                ,p_portlet_id   => MENU_PORTLET
                ,p_language     => p_language
                );

        end if;

        return l_portlet_list;

    end get_portlet_list;

    function is_portlet_runnable
    (
        p_portlet_instance in wwpro_api_provider.portlet_instance_record
    )
    return boolean
    is
    begin

        if (p_portlet_instance.portlet_id = MENU_PORTLET) then
            return menu.menu_pck_portlet.is_runnable(
                 p_provider_id     =>  p_portlet_instance.provider_id
                ,p_reference_path  =>  p_portlet_instance.reference_path
                );
        else
            raise wwpro_api_provider.PORTLET_NOT_FOUND_EXCEPTION;
        end if;

    end is_portlet_runnable;

    procedure register_portlet
    (
        p_portlet_instance in wwpro_api_provider.portlet_instance_record
    )
    is
    begin

        if (p_portlet_instance.portlet_id = MENU_PORTLET) then
            menu.menu_pck_portlet.register(p_portlet_instance);
        else
            raise wwpro_api_provider.PORTLET_NOT_FOUND_EXCEPTION;
        end if;

    end register_portlet;

    procedure deregister_portlet
    (
        p_portlet_instance in wwpro_api_provider.portlet_instance_record
    )
    is
    begin

        if (p_portlet_instance.portlet_id = MENU_PORTLET) then
            menu.menu_pck_portlet.deregister(p_portlet_instance);
        else
            raise wwpro_api_provider.PORTLET_NOT_FOUND_EXCEPTION;
        end if;

    end deregister_portlet;

    procedure show_portlet
    (
        p_portlet_record in out wwpro_api_provider.portlet_runtime_record
    )
    is
    begin

        if (p_portlet_record.portlet_id = MENU_PORTLET) then
            menu.menu_pck_portlet.show(p_portlet_record);
        else
            raise wwpro_api_provider.PORTLET_NOT_FOUND_EXCEPTION;
        end if;

    end show_portlet;

    procedure copy_portlet
    (
         p_copy_portlet_info in wwpro_api_provider.copy_portlet_record
    )
    is
    begin
        null;
    end copy_portlet;

    function describe_portlet_parameters
    (
         p_provider_id in integer
        ,p_portlet_id in integer
        ,p_language in varchar2
    )
    return wwpro_api_provider.portlet_parameter_table
    is
    begin

        if (p_portlet_id = MENU_PORTLET) then
            return menu.menu_pck_portlet.describe_parameters(
                 p_provider_id     =>  p_provider_id
                ,p_language        =>  p_language
                );
        else
            raise wwpro_api_provider.PORTLET_NOT_FOUND_EXCEPTION;
        end if;

    end describe_portlet_parameters;

end MENU_PCK_PROVIDER;
/

PL/SQL specification for the MENU_PCK_PORTLET

CREATE OR REPLACE package MENU_PCK_PORTLET
is
  /** PACKAGE
  *
  *  OBJECTIVES.....: Menu portlet
  *
  *  DESCRIBTION....: Javascript menu portlet which creates a menu based on the
  *                   page structure in a pagegroup
  *
  *  RULES..........: n/a
  *
  *  DESIGN REQUESTS: n/a
  *
  *  REVISIONS:
  *  Ver        Date        Author                   Description
  *  ---------  ----------  ---------------          --------------------------
  *  1.0        4-Mar-2003  Kenneth Thorman      1. Created this package.
  *
  *
  *
  */


  /**
   * Get the properties for a portlet.
   *
   * This function returns, in a wwpro_api_provider.portlet_record
   * structure, the details of the Portlet implemented by this
   * package.
   *
   * @param   p_provider_id    A unique identifier for the provider.
   * @param   p_language       The language in which the translatable
   *                           strings are returned.
   * @return  A record structure containing the portlet info.
   *
   * @exception wwpro_api_provider.PROVIDER_SECURITY_EXCEPTION
   *            if is_runnable returns false.
   */
   function get_portlet_info
   (
        p_provider_id in integer
       ,p_language in varchar2
   )
   return wwpro_api_provider.portlet_record;

  /**
   * Return whether this portlet can be viewed by the current user.
   *
   * This method may be called in one of the following 2 situations:
   * <P>
   * 1 - When a request is made to display the portlet in any of the
   *     show modes.  In this case the reference_path field of the
   *     p_portlet_instance record is not null.  This is a situation
   *     in which portlet instance security check should be made.
   * </P>
   * <P>
   * 2 - When the portlet repository is displayed a call is made to
   *     the provider (i.e. is_portlet_runnable) for each portlet
   *     to check whether this portlet should be shown to the user.
   *     This is the situation in which general portlet security
   *     check is performed.  In this case the reference_path field
   *     of the p_portlet_instance record is null.
   * </P>
   * <P>
   * This function simply verifies that the user is logged on -
   * i.e. it is not a public session.
   * </P>
   *
   * @param   p_provider_id    A unique identifier for the provider.
   * @param   p_reference_path A unique identifier for a portlet
   *                           instance on a page.
   * @return  A boolean value indicating whether the portlet can
   *          be run or not.
   */
   function is_runnable
   (
        p_provider_id in integer
       ,p_reference_path in varchar2
   )
   return boolean;

  /**
   * Register the usage of a portlet on a page.
   *
   * The framework will call upon this function when the portlet is
   * put on a page.  This provides the Portlet an opportunity to perform
   * instance-level initialization such as the establishment of defaults
   * for end-user customization.
   *
   * @param   p_portlet_instance   A portlet instance record that contains
   *                               information for the portlet instance.
   */
   procedure register
   (
       p_portlet_instance in wwpro_api_provider.portlet_instance_record
   );

  /**
   * Deregister a usage of a portlet on a page.
   *
   * The framework will call upon this function when a portlet is
   * removed from a page.  This provides the Portlet an opportunity to
   * perform instance-level cleanup such as the removal of end-user
   * and default customizations.
   *
   * @param   p_portlet_instance   A portlet instance record that contains
   *                               information for the portlet instance.
   */
   procedure deregister
   (
       p_portlet_instance in wwpro_api_provider.portlet_instance_record
   );

  /**
   * Show the Database Services Portlet.
   *
   * When called, the portlet should produce the necessary HTML or
   * XML/XSL to visualize itself for the selected mode.  The page
   * produced should contain the restricted set of HTML Markup that
   * would be legal to place within a cell of a HTML Table (TD
   * element).  For example, it should not have the HEAD or BODY
   * html tags since these will be applied by the Portal framework.
   * <P>
   * The content generated by the portlet should use the tags from
   * the cascading style sheet (CSS).
   * </P> <P>
   * The following are the valid show modes for a portlet:
   * </P> <P>
   * MODE_SHOW - Shows the portlet's content on a page
   * </P> <P>
   * MODE_SHOW_ABOUT - Shows the about content of the portlet on
   *                   a page.  The about content may contain
   *                   the portlet's version number, date, etc.
   * </P> <P>
   * MODE_SHOW_EDIT - Shows the Edit (i.e. Customize) UI
   * </P> <P>
   * MODE_SHOW_HELP - Shows the Help for the portlet
   * </P> <P>
   * MODE_SHOW_EDIT_DEFAULTS - Shows the UI to edit the default
   *                           customizations for the portlet
   * </P> <P>
   * MODE_SHOW_DETAILS - Shows the details page for the portlet
   * </P> <P>
   * MODE_PREVIEW - Shows the preview of the portlet
   * </P>
   *
   * @param   p_portlet_record    A portlet runtime record that contains
   *                              information passed by the framework and
   *                              is used by the portlet.
   *
   * @exception wwpro_api_provider.PROVIDER_SECURITY_EXCEPTION
   *            if is_runnable returns false.
   * @exception wwpro_api_provider.PORTLET_EXECUTION_EXCEPTION
   *            Raised if any exception is handled during portlet execution
   *
   */
   procedure show
   (
       p_portlet_record        wwpro_api_provider.portlet_runtime_record
   );

   /**
   * Copy a portlets customization and defaults from one portlet
   * to another.
   *
   * This procedure does nothing!
   *
   * @param p_copy_portlet_info    Source and destination portlet info.
   */
   procedure copy
   (
        p_copy_portlet_info in wwpro_api_provider.copy_portlet_record
   );

  /**
   * Describe the parameters that the Portlet understands.
   *
   * The framework will call upon this function if it needs
   * descriptive information about the parameters to the portlet.
   *
   * @param   p_provider_id    A unique identifier for the provider.
   * @param   p_language       The language in which to return the
   *                           translatable strings into.
   * @return  A record describing the portlet parameters.
   */
   function describe_parameters
   (
        p_provider_id in integer
       ,p_language in varchar2
   )
   return wwpro_api_provider.portlet_parameter_table;


end MENU_PCK_PORTLET;
/

PL/SQL body for the MENU_PCK_PORTLET

CREATE OR REPLACE PACKAGE BODY menu_pck_portlet
IS

/*******************************************************************************
* Portlet specific contants and variables
*******************************************************************************/

   /**
   * DOMAIN                            The domain.
   * SUBDOMAIN                         The subdomain used to retreive certain portlet
   *                                   specific values in portal.
   * Change to:                        Should be changed to the name of the portlet package
   */
   DOMAIN          CONSTANT VARCHAR2 (30)  := util_pck_constants.DOMAIN;
   SUBDOMAIN       CONSTANT VARCHAR2 (32)  := 'MENU';

/*******************************************************************************
* Constants
*******************************************************************************/

   /**
   * @group Preference Store Constants
   *
   * These are constants used to store the customizations in the preference store.
   *
   * PORTLET_PATH                      The portlet path.
   */
   PORTLET_PATH    CONSTANT VARCHAR2 (256) := DOMAIN || '.' || SUBDOMAIN;
   /**
   * @group Customisation actions.
   *
   * These constants define the different actions following customisation.
   *
   */
   ACTION_OK       CONSTANT VARCHAR2 (10)  := util_pck_constants.ACTION_OK;
   ACTION_APPLY    CONSTANT VARCHAR2 (10)  := util_pck_constants.ACTION_APPLY;
   ACTION_CANCEL   CONSTANT VARCHAR2 (10)  := util_pck_constants.ACTION_CANCEL;
   ACTION_CLOSE    CONSTANT VARCHAR2 (10)  := util_pck_constants.ACTION_CLOSE;
   /**
   * @group Logging actions.
   *
   * These constants define the different actions for logging.
   *
   * LOG_UPDATE                        The string 'UPDATE'
   * LOG_INSERT                        The string 'INSERT'
   * LOG_FAILED                        The string 'FAILED'
   */
   LOG_UPDATE      CONSTANT VARCHAR2 (10)  := util_pck_constants.LOG_UPDATE;
   LOG_INSERT      CONSTANT VARCHAR2 (10)  := util_pck_constants.LOG_INSERT;
   LOG_DELETE      CONSTANT VARCHAR2 (10)  := util_pck_constants.LOG_DELETE;
   LOG_FAILED      CONSTANT VARCHAR2 (10)  := util_pck_constants.LOG_FAILED;

/*******************************************************************************
* Variables
*******************************************************************************/

    type Tprefs IS VARRAY(26) OF VARCHAR2(250);
    prefNames Tprefs:= Tprefs('portlet_title', 'page_group_id', 'start_page_id', 'icon_page_path', 'target', 'empty_img', 'vertical_line', 'root_normal', 'root_selected', 'root_opened', 'root_selected_opened', 'node_normal', 'node_selected', 'node_opened', 'node_selected_opened', 'leaf_normal', 'leaf_selected', 'leaf_opened', 'leaf_selected_opened', 'junc_leaf', 'junc_last_leaf', 'junc_closed_node', 'junc_last_closed_node', 'junc_opened_node', 'junc_last_opened_node');
    prefValues Tprefs := Tprefs(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

/*******************************************************************************
* Forward declarations
*******************************************************************************/

   PROCEDURE save_prefs                (p_portlet_record    IN wwpro_api_provider.portlet_runtime_record,
                 p_action            in varchar2);

   PROCEDURE show_main (
      p_portlet_record   IN   wwpro_api_provider.portlet_runtime_record,
      p_portlet          IN   wwpro_api_provider.portlet_record
   );

   PROCEDURE show_edit (
      p_portlet_record   IN   wwpro_api_provider.portlet_runtime_record
   );


   procedure get_user_preference (
       p_portlet_record in wwpro_api_provider.portlet_runtime_record
   );

   procedure get_default_preference (
       p_portlet_record in wwpro_api_provider.portlet_runtime_record
   );


   function get_pref_value (
      p_name in varchar2
   )
   return varchar2;
  
   FUNCTION get_nls_string (
      p_name       IN   VARCHAR2,
      p_language   IN   VARCHAR2
   )
   RETURN VARCHAR2;


/*******************************************************************************
* Main programming logic
*******************************************************************************/

   /**
   * Get the properties for a portlet.
   *
   * This function returns, in a wwpro_api_provider.portlet_record
   * structure, the details of the Portlet implemented by this
   * package.
   *
   */
   FUNCTION get_portlet_info (
      p_provider_id   IN   INTEGER,
      p_language      IN   VARCHAR2
   )
      RETURN wwpro_api_provider.portlet_record
   IS
      l_portlet   wwpro_api_provider.portlet_record;
   BEGIN
      l_portlet.id := menu_pck_provider.menu_portlet;
      l_portlet.provider_id := p_provider_id;
      l_portlet.NAME := SUBDOMAIN;
      l_portlet.TIMEOUT := 10;
      l_portlet.timeout_msg := NULL;
      l_portlet.LANGUAGE := p_language;
      l_portlet.has_show_edit := wwctx_api.is_logged_on;
      l_portlet.has_show_edit_defaults := TRUE;
      l_portlet.has_show_preview := TRUE;
      l_portlet.preference_store_path := DOMAIN || '.' || SUBDOMAIN;
      l_portlet.created_on := TO_DATE ('12112002', 'DDMMYYYY');
      l_portlet.created_by := 'kennethD.O.TthormanA.TgmailD.O.TgmailD.O.Tcom';
      l_portlet.last_updated_on := TO_DATE ('12112002', 'DDMMYYYY');
      l_portlet.last_updated_by := 'kennethD.O.TthormanA.TgmailD.O.TgmailD.O.Tcom';
      --
      -- New Portal 2 values
      --
      l_portlet.api_version := wwpro_api_provider.api_version_1;
      l_portlet.content_type := wwpro_api_provider.content_type_html;
      --
      -- Currently not used by the framework
      --
      l_portlet.implementation_style := NULL;
      l_portlet.implementation_owner := NULL;
      l_portlet.implementation_name := NULL;
      --
      -- Make sure everything else is set first
      -- The title and description returned should be responsive to the language parameter
      --
      l_portlet.title := get_nls_string ('title', p_language);
      l_portlet.description := get_nls_string ('description', p_language);
      RETURN l_portlet;
   END get_portlet_info;

   
   
   /**
   * Show the Portlet.
   *
   * When called, the portlet should produce the necessary HTML or
   * XML/XSL to visualize itself for the selected mode.  The page
   * produced should contain the restricted set of HTML Markup that
   * would be legal to place within a cell of a HTML Table (TD
   * element).  For example, it should not have the HEAD or BODY
   * html tags since these will be applied by the Portal framework.
   *
   * The following are the valid show modes for a portlet:
   *
   * MODE_SHOW                Shows the portlet's content on a page
   *
   * MODE_SHOW_ABOUT          Shows the about content of the portlet on a page. The
   *                          about content may contain the portlet's version number, date, etc.
   *
   * MODE_SHOW_EDIT           Shows the Edit (i.e. Customize) UI
   *
   * MODE_SHOW_HELP           Shows the Help for the portlet
   *
   * MODE_SHOW_EDIT_DEFAULTS  Shows the UI to edit the default customizations for the portlet
   *
   * MODE_SHOW_DETAILS        Shows the details page for the portlet
   *
   * MODE_PREVIEW             Shows the preview of the portlet
   */
   PROCEDURE show (
      p_portlet_record   wwpro_api_provider.portlet_runtime_record
   )
   IS
      l_portlet   wwpro_api_provider.portlet_record;
   l_action   varchar2(50);
   BEGIN
      --
      -- Perform a security check
      --
      IF (NOT is_runnable (
                 p_provider_id => p_portlet_record.provider_id,
                 p_reference_path => p_portlet_record.reference_path
              )
         ) THEN
         wwerr_api_error.ADD (DOMAIN, SUBDOMAIN, 'securityerr', SUBDOMAIN||'.show');
         RAISE wwpro_api_provider.portlet_security_exception;
      END IF;

      l_portlet := get_portlet_info (p_portlet_record.provider_id, p_portlet_record.LANGUAGE);

   --
      -- P_MODE = SHOW
      --
      IF (p_portlet_record.exec_mode = wwpro_api_provider.mode_show) THEN
         show_main (p_portlet_record, l_portlet);
      --
      -- P_MODE = EDIT or EDIT DEFAULTS
      --
      ELSIF (p_portlet_record.exec_mode = wwpro_api_provider.mode_show_edit) OR
            (p_portlet_record.exec_mode = wwpro_api_provider.mode_show_edit_defaults) THEN


   l_action := upper(util_param.GETPARAMETERVALUE('p_action'));
   if l_action in (ACTION_OK,ACTION_APPLY,ACTION_CANCEL) then
                save_prefs(p_portlet_record,l_action);
            else
                show_edit(p_portlet_record);
            end if;

      --
      -- Generic display
      --
      ELSE
         util_pck_show.show (DOMAIN, SUBDOMAIN, p_portlet_record, l_portlet);
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         util_pck_show.show_error;
   END show;




   /**
   * This displays the portlet for a normal show mode.
   *
   * This procedure renders the 'normal' show mode of the portlet.
   *
   * @exception wwpro_api_provider.PORTLET_EXECUTION_EXCEPTION
   *            This is raised as a response to any exception.
   */
   PROCEDURE show_main (
      p_portlet_record   IN   wwpro_api_provider.portlet_runtime_record,
      p_portlet          IN   wwpro_api_provider.portlet_record
   )
   IS
      v_siteid    number;
      v_pageid    number;
      v_siteid_pageid varchar2(100);
      v_commapos  number;
      v_substrlen number;
      v_startpos number;
   v_page_group_id varchar2(100);
   v_start_page_id varchar2(100);
   BEGIN
      --
      -- Retrieve the user customizations.
      --
   get_user_preference(p_portlet_record);
   
      IF (p_portlet_record.has_title_region) THEN
         --
         -- Draw the portlet header and specify what links are available
         -- from that header (i.e. details, customize, help, and about).
         -- The has_title property is set at the page region level.
         --
         wwui_api_portlet.draw_portlet_header (
            p_provider_id => p_portlet_record.provider_id,
            p_portlet_id => p_portlet_record.portlet_id,
            p_title => get_pref_value('portlet_title'),
            p_has_details => FALSE,
            p_has_edit => TRUE,
            p_has_help => TRUE,
            p_has_about => TRUE,
            p_show_collapse => TRUE,
            p_show_remove => FALSE,
            p_is_collapsed => p_portlet_record.is_collapsed,
            p_referencepath => p_portlet_record.reference_path,
            p_back_url => p_portlet_record.page_url
         );
      END IF;

      --
      -- Draw the portlet borders.
      -- The has_border property is set at the page region level.
      --
      wwui_api_portlet.open_portlet (p_portlet_record.has_border);

      --
      -- Is the portlet collapsed? If it is open then render contents
      -- else do nothing
      --
      IF NOT p_portlet_record.is_collapsed THEN

         --
         -- Get siteid and pageid so we can establish the menu root
         -- this is default setting but if the user specifies a pageid and siteid
         -- during customization then it overirdes this default setting
         -- (I could not find an API which returns the page the user is currently
         -- visiting and since it is present in the p_portlet_record.page_url, then
         -- grab it from here. It is not however a nice and clean solution :-(  )
         --
         v_startpos := instr(p_portlet_record.page_url,'_pageid=')+8;
         v_substrlen := ((instr(p_portlet_record.page_url,'&')) - v_startpos);
         v_siteid_pageid := substr(p_portlet_record.page_url,v_startpos,v_substrlen);
--         htp.p('v_siteid_pageid='||v_siteid_pageid||'<br>');

         --
         -- Seperate siteid from pageid
         --
         v_commapos := instr(v_siteid_pageid, ',');
         v_siteid := to_number(substr(v_siteid_pageid, 1, v_commapos-1));
         v_pageid := to_number(substr(v_siteid_pageid, v_commapos+1, length(v_siteid_pageid)));

--         htp.p('siteid:'||v_siteid||', pageid:'||v_pageid||'<br>');
         
         --
         -- Print reference to javascript file which is responsible for the menu
         -- generation
         --
         v_page_group_id := get_pref_value('page_group_id');
   if lower(v_page_group_id) like 'missing%' then
      v_page_group_id := v_siteid;
   end if;
   
   v_start_page_id := get_pref_value('start_page_id');
   if lower(v_start_page_id) like 'missing%' then
      v_start_page_id := 1;
   end if;
    
   htp.p('<style>');
         htp.p('.t0i {font-family: Tahoma, Verdana, Geneva, Arial, Helvetica, sans-serif;font-size: 11px;color: #000000;background-color:;text-decoration: none;}');
         htp.p('.t0im {border:0px;width:19px;height:16px;}');
         htp.p('</style>');
         htp.p('<script language="JavaScript" src="/pls/portal/menu.navigation_menu.show_menu?p_page_group='||v_page_group_id||'&p_start_page_id='||v_start_page_id||'&p_current_page_id='||v_pageid||'&p_language='||wwctx_api.get_nls_language||'"></script>');
         htp.p('<BODY leftmargin="15" marginheight="2" marginwidth="15" topmargin="2">');
         htp.p('<script language="JavaScript" src="/pls/portal/docs/2095.js"></script>');
         htp.p('<script language="JavaScript" src="/pls/portal/menu.navigation_menu.tree_tpl?p_target='||get_pref_value('target')||'&p_empty_img='||get_pref_value('empty_img')||'&p_vertical_line='||get_pref_value('vertical_line')||
                '&p_root_normal='||get_pref_value('root_normal')||'&p_root_selected='||get_pref_value('root_selected')||'&p_root_opened='||get_pref_value('root_opened')||'&p_root_selected_opened='||get_pref_value('root_selected_opened')||
                '&p_node_normal='||get_pref_value('node_normal')||'&p_node_selected='||get_pref_value('node_selected')||'&p_node_opened='||get_pref_value('node_opened')||'&p_node_selected_opened='||get_pref_value('node_selected_opened')||
                '&p_leaf_normal='||get_pref_value('leaf_normal')||'&p_leaf_selected='||get_pref_value('leaf_selected')||'&p_leaf_opened='||get_pref_value('leaf_opened')||'&p_leaf_selected_opened='||get_pref_value('leaf_selected_opened')||
                '&p_junc_leaf='||get_pref_value('junc_leaf')||'&p_junc_last_leaf='||get_pref_value('junc_last_leaf')||'&p_junc_closed_node='||get_pref_value('junc_closed_node')||
                '&p_junc_last_closed_node='||get_pref_value('junc_last_closed_node')||'&p_junc_opened_node='||get_pref_value('junc_opened_node')||
                '&p_junc_last_opened_node='||get_pref_value('junc_last_opened_node')||'"></script>');

         htp.p('<script language="JavaScript">new tree (TREE_ITEMS, tree_tpl);</script>');
         


      END IF;

      --
      -- If the portlet has borders then close them
      --
      IF (p_portlet_record.has_border) THEN
         wwui_api_portlet.close_portlet;
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         util_pck_show.show_error;
   END show_main;

   /**
    * Show the 'Edit' mode for the portlet.
    *
    * This procedure displays the 'edit' or customise mode of show.
    *
    * p_portlet_record        A record containing the portlet details.
    */
   PROCEDURE show_edit (
      p_portlet_record   IN   wwpro_api_provider.portlet_runtime_record
   )
   IS
      l_text_prompt_string    VARCHAR2 (30);
      l_title_prompt_string   VARCHAR2 (30);
      l_button_list    wwui_api_portlet_dialog.button_list;
   
   v_icon_page_path varchar2(300);
      
      cursor c_img_lov (p_parent_path in varchar2) is
   select filename, name
   from wwdoc_document$ doc,
       wwdav$path dav
      where dav.DOC_ID = doc.DAV_ID
   and  dav.PARENTPATH = p_parent_path
   and (upper(name) like '%.GIF' or upper(name) like '%.JPG');

   BEGIN
      --
      --  This procedure renders the customisation and edit default screens.
      --

      --
      --  Depending on whether we are cutomising or showin edit defaults
      --  set some values.
      --
      if (p_portlet_record.exec_mode = wwpro_api_provider.MODE_SHOW_EDIT) then
         get_user_preference(p_portlet_record);
      else
         get_default_preference(p_portlet_record);
      end if;

        htp.p ('<script>function Save(pAction) {');
        htp.p ('    document.services.p_action.value = pAction;');
        htp.p ('    document.services.submit();');
        htp.p ('}</script>');

      l_button_list(1) := portal.wwui_api_portlet_dialog.create_apply_button(
            'Save('''||ACTION_APPLY||''')',p_portlet_record.language);
      l_button_list(2) := portal.wwui_api_portlet_dialog.create_ok_button(
            'Save('''||ACTION_OK||''')',p_portlet_record.language);
      l_button_list(3) := portal.wwui_api_portlet_dialog.create_close_button(
            'Save('''||ACTION_CANCEL||''')',p_portlet_record.language);

      wwui_api_portlet_dialog.open_dialog
        (
             p_title => get_nls_string('customize',p_portlet_record.language)
            ,p_dialog_image => null
            ,p_help_url => null
            ,p_subheader_text => null
            ,p_help_text => null
            ,p_buttons => l_button_list
            ,p_path_text => null
        );
  
  
 
        --
        --  This procedure call creates the <FORM> tags with a set of
        --  standard parameters.  Using this procedure makes the 
        --  customisation page work through the pl/sql http adapter.
        --
      htp.p('<table align="center">');
   wwpro_api_adapter.open_form(p_formattr => 'NAME="services"',
                                  p_prr      => p_portlet_record);

   
   for x in prefNames.first .. prefnames.last loop
      if prefnames(x) in ('portlet_title','page_group_id','start_page_id','target','icon_page_path')  then
   htp.p('<tr><td>'||wwui_api_portlet.portlet_heading(get_nls_string('txt'||prefnames(x),p_portlet_record.language),1)||'</td><td><input type="TEXT" name="'||prefnames(x)||'" value="'||prefValues(x)||'"></td></tr>');
      end if;
   end loop;

   if get_pref_value('icon_page_path') is null then 
    htp.p('<tr><td colspan="2">'||wwui_api_portlet.portlet_text(get_nls_string('txticon_location',p_portlet_record.language),1)||'</td></tr>');
   else

    for x in prefNames.first .. prefnames.last loop
       if prefnames(x) in ('empty_img','vertical_line','root_normal','root_selected','root_opened','root_selected_opened','node_normal','node_selected','node_opened','node_selected_opened','leaf_normal','leaf_selected','leaf_opened','leaf_selected_opened','junc_leaf','junc_last_leaf','junc_closed_node','junc_last_closed_node','junc_opened_node','junc_last_opened_node')  then
    
    htp.p('<tr><td>'||wwui_api_portlet.portlet_heading(get_nls_string('txt'||prefnames(x),p_portlet_record.language),1)||'</td><td><select name="'||prefnames(x)||'">');
    for a in c_img_lov(get_pref_value('icon_page_path')) loop
       htp.p('<option value="'||a.filename||'">'||a.name);
    end loop;
    htp.p('</td></tr>');
       end if;
    end loop;
   end if;   
   htp.p('</table>');
   
      HTP.formclose;
      
   wwui_api_portlet_dialog.close_dialog;
 
   END show_edit;

   /**
    * Save the Customisations
    *
    * If p_action is 'OK' or 'APPLY' then the customisations are stored
    * in the preference store.  Then redirect as appropriate.
    *
    * @param   p_string         The user text to be saved.
    * @param   p_title          The portlet title to be saved.
    * @param   p_action         One of 'OK', 'APPLY' or 'CANCEL'.
    * @param   p_level          Either SYSTEM or USER level.  SYSTEM level
    *                           is for 'edit defaults' functionality.
    * @param   p_portlet_record A record containing the portlet details.
    *
    * @exception INVALID_TEXT_EXCEPTION if text contains illegal characters.
    */
    procedure save_prefs
    (
        p_portlet_record  in wwpro_api_provider.portlet_runtime_record,
  p_action    in varchar2
    )
    is
        l_user           varchar2(100)  := wwctx_api.get_user;
  p_level     varchar2(1);
  p_level_name     varchar2(300);
  l_id number;
        l_names    owa.vc_arr;
        l_values   owa.vc_arr;
    begin
         --
         --  If the OK button or APPLY button is pressed then we need to:
         --    - Load any preferences that already exist
         --    - Validate the string is valid
         --    - For both the portlet title and customisation text:
         --      - If the preference doesn't alread exist create a string and
         --         populate the preference
         --       - If it does exist then update is with the new string
         --    - and finally re-direct to the appropriate page
         --
  
  if p_action in (ACTION_OK, ACTION_APPLY) then
             --
             -- Load the last preferences created by the user, if any, in order
             -- to use the same preference store ids.  If no preferences have
             -- been created yet, they will be created.
             --
   if (p_portlet_record.exec_mode = wwpro_api_provider.mode_show_edit_defaults) then
                get_default_preference (p_portlet_record);
    p_level := WWPRE_API_VALUE.SYSTEM_LEVEL_TYPE;
    p_level_name := NULL;
            else
                get_user_preference (p_portlet_record);
    p_level := WWPRE_API_VALUE.USER_LEVEL_TYPE;
    p_level_name := l_user;
            end if;

   
            wwpro_api_parameters.retrieve(l_names, l_values);

   for w in 1..l_names.count loop --Web parameters
      for p in prefnames.first .. prefnames.last loop  -- Preferences
      --
      -- If the webparameter has the same name as the preference parameter then a match
      --

      if upper(prefNames(p)) = upper(l_names(w)) then
             --htp.p(l_names(w)||':'||l_values(w)||'<br>');
                     --htp.p(prefnames(p)||':'||prefvalues(p)||'<br>');
      --
      -- If there is no existing value the make a new one
      --
        if prefValues(p) is NULL then
      --
                        -- Use the nls add_string api to create a new nls string in
                        -- nls storage.
                        --
      l_id := wwnls_api.add_string(
                                   p_domain     => DOMAIN,
                                   p_sub_domain => SUBDOMAIN,
                                   p_name       => prefNames(p),
                                   p_language   => p_portlet_record.language,
                                   p_text       => l_values(w)
        );

      htp.p(l_id);
      --
                        -- Store the id of the nls string created in the preference
                        -- store in order to be able to locate the nls string.
                        --
                        wwpre_api_value.set_value_as_number(
                           p_path       => PORTLET_PATH || p_portlet_record.reference_path,
                           p_name       => prefNames(p),
                           p_level_type => p_level,
                           p_level_name => p_level_name,
                           p_value      => l_id
                        );
     
     --
     -- string exists in at least one language so update it
     --
     else 
                       --
                       -- Use the nls set_string api to modify the existing nls string
                       -- in nls storage.  If the string exists in a different
                       -- languages this call will create a new string in the new
                       -- language.  If the string exists only in the current
                       -- language this API will overwrite the existing string
                       -- with the new one entered by the user.
                       --
                       --htp.p(wwnls_api.get_id (DOMAIN,SUBDOMAIN,prefNames(p),p_portlet_record.language));
        htp.p(PORTLET_PATH || p_portlet_record.reference_path||'.'||prefNames(p)||wwpre_api_value.get_value_as_number (
                           p_path       =>  PORTLET_PATH || p_portlet_record.reference_path,
            p_name       =>  prefNames(p),
            p_level_type => p_level,
            p_level_name => p_level_name)||prefnames(p)||':'||prefvalues(p)||'<br>');
        
/*        
        wwnls_api.set_string (
                          p_id         => wwpre_api_value.get_value_as_number (
                           p_path       =>  PORTLET_PATH || p_portlet_record.reference_path,
            p_name       =>  prefNames(p),
            p_level_type => p_level,
            p_level_name => p_level_name),
                          p_language   => p_portlet_record.language,
                          p_text       => l_values(w)
        );*/
        end if;
     end if;
       end loop;
     end loop;

     --
           -- Now we have saved the preferences we need to re-direct
           --

            /*if p_action    = ACTION_OK then
                owa_util.redirect_url(curl=>p_portlet_record.back_page_url);
            elsif p_action = ACTION_APPLY then
                owa_util.redirect_url(curl=>p_portlet_record.page_url);
            end if;*/
  end if;

  exception
        when others then
               util_pck_show.SHOW_ERROR;

    end save_prefs;



   /**
    *  Generalized Load the system level default values.
    *
    *  This procedure loads the default preferences from the preference
    *  store.  The default preferences are associated with the portlet
    *  instance.  If no preferences have been set for the portlet
    *  instance it returns the system default preferences.  The system
    *  default preferences are some nls strings used by this portlet.
    *  The preferences are save in the ptl properties runtime array
    *
    * @param     p_language The language being used.
    * @param     p_reference_path This is used to store the preferences.
    *
    * @return    A record containing the preferences.
    */
    procedure get_default_preference
    (
        p_portlet_record in wwpro_api_provider.portlet_runtime_record
    )
    is
  v_temp_nls_string_id number;
    begin


  -- Since we have to do all the following checks fo each and every
  -- preference we might have for a portlet it is easier to do it
  -- in a loop.
      FOR x IN prefNames.FIRST .. prefNames.LAST LOOP


   IF prefValues(x) IS NULL THEN

    -- Try to find any object values for this portlet in the preference store
    v_temp_nls_string_id := wwpre_api_value.get_value_as_number(
                p_path       => PORTLET_PATH || p_portlet_record.reference_path,
                                     p_name       => prefNames(x),
                                     p_level_type => wwpre_api_value.SYSTEM_LEVEL_TYPE
                                  );

    if v_temp_nls_string_id is null then
       -- No object values for this portlet was found.
       -- Load the class values (installation default) 
                prefValues(x) := get_nls_string (prefNames(x), p_portlet_record.language);
          else
                   prefValues(x) := wwnls_api.get_string(v_temp_nls_string_id, p_portlet_record.language);
              end if;
   end if;
    END LOOP;
    end get_default_preference;



   /**
    *  Retrieve the user customized string displayed in the portlet.
    *
    *  Each user can store their customized strings for each portlet instance
    *  in each language.
    *
    * @param     p_language The language being used.
    * @param     p_reference_path This is used to store the preferences.
    *
    * @return    A record containing the preferences.
    */
    procedure get_user_preference
    (
        p_portlet_record in wwpro_api_provider.portlet_runtime_record
    )
    is
  v_temp_nls_string_id number;
        v_need_object_or_class_values boolean := FALSE;
    begin


  -- Since we have to do all the following checks fo each and every
  -- preference we might have for a portlet it is easier to do it
  -- in a loop.
      FOR x IN prefNames.FIRST .. prefNames.LAST LOOP

   -- Try to find any object user customized values for this portlet in the
         -- preference store
   
   v_temp_nls_string_id := wwpre_api_value.get_value_as_number(
               p_path       => PORTLET_PATH || p_portlet_record.reference_path,
                                    p_name       => prefNames(x),
                                       p_level_type => wwpre_api_value.USER_LEVEL_TYPE,
                  p_level_name => wwctx_api.get_user
                                 );

        -- If the value returned above is null it is an indication that the
        -- the user has not created a object user customized string yet.
        if v_temp_nls_string_id is null then
           v_need_object_or_class_values := TRUE;

           prefValues(x) := NULL;

        else
           -- Now load it indirectly from the NLS storage into ptl runtime properties
           prefValues(x) := wwnls_api.get_string(v_temp_nls_string_id, p_portlet_record.language);
        end if;

     END LOOP;

     if v_need_object_or_class_values then
         get_default_preference(p_portlet_record);
     end if;
    end get_user_preference;
    

   /**
    * Register the usage of a portlet on a page.
    *
    * The framework will call upon this function when the portlet is
    * put on a page.  This provides the Portlet an opportunity to perform
    * instance-level initialization such as the establishment of defaults
    * for end-user customization.
    *
    */
   PROCEDURE register (
      p_portlet_instance   IN   wwpro_api_provider.portlet_instance_record
   )
   IS
   BEGIN
      --
      -- Create a path for the portlet instance.  This is used to create
      -- the preferences for the portlet instance in the preference store.
      --
   wwpre_api_name.create_path (p_path => PORTLET_PATH ||  p_portlet_instance.reference_path);
      --
      -- Create the names to store the portlet preferences.
      --
      FOR x IN prefNames.FIRST .. prefNames.LAST LOOP
         wwpre_api_name.create_name (
            p_path => PORTLET_PATH || p_portlet_instance.reference_path,
            p_name => prefNames(x),
            p_description => '',
            p_type_name => 'NLSID',
            p_language => wwctx_api.get_nls_language
         );
      end loop;

   END register;


/*******************************************************************************
* Utility and helper subroutines
*******************************************************************************/

   /**
   * Get value function
   */
   function get_pref_value (
      p_name in varchar2
   )
   return varchar2
   is
   begin
      for a in prefNames.first .. prefNames.last loop
      if lower(prefNames(a)) = lower(p_name) then
      return prefValues(a);
   end if;
   end loop;
   end;

   /**
   * Shortcut/wrapper for portal function wwnls_api.get_string
   */
   FUNCTION get_nls_string (
      p_name       IN   VARCHAR2,
      p_language   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
     v_temp varchar2(1000);
   BEGIN
      v_temp:= wwnls_api.get_string (DOMAIN, SUBDOMAIN, p_name, p_language);
   if v_temp like 'Missing%' then
      return NULL;
   else
      RETURN wwnls_api.get_string (DOMAIN, SUBDOMAIN, p_name, p_language);
   end if;
   END;


/*******************************************************************************
* Portal generic utility and helper subroutines (no custom logic)
*******************************************************************************/

   /**
   * Return whether this portlet can be viewed by the current user.
   *
   * This method may be called in one of the following 2 situations:
   * 1 - When a request is made to display the portlet in any of the
   *     show modes.  In this case the reference_path field of the
   *     p_portlet_instance record is not null.  This is a situation
   *     in which portlet instance security check should be made.
   * 2 - When the portlet repository is displayed a call is made to
   *     the provider (i.e. is_portlet_runnable) for each portlet
   *     to check whether this portlet should be shown to the user.
   *     This is the situation in which general portlet security
   *     check is performed.  In this case the reference_path field
   *     of the p_portlet_instance record is null.
   *
   * This function simply verifies that the user is logged on -
   * i.e. it is not a public session.
   */
   FUNCTION is_runnable (
      p_provider_id      IN   INTEGER,
      p_reference_path   IN   VARCHAR2
   )
      RETURN BOOLEAN
   IS
   BEGIN
      --
      -- Portlet security check.  It allows the portlet to be visible
      -- if the user is logged on, i.e. the current session is not a
      -- public session.
      --
      RETURN wwctx_api.is_logged_on;
   END is_runnable;

   /**
   * Deregister a usage of a portlet on a page.
   *
   * The framework will call upon this function when a portlet is
   * removed from a page.  This provides the Portlet an opportunity to
   * perform instance-level cleanup such as the removal of end-user
   * and default customizations.
   *
   */
   PROCEDURE deregister (
      p_portlet_instance   IN   wwpro_api_provider.portlet_instance_record
   )
   IS
   BEGIN
      --
      -- Delete the path used by the portlet instance.  This will delete
      -- all the names and all the values associated with the path.
      --
      wwpre_api_name.delete_path (p_path => PORTLET_PATH || p_portlet_instance.reference_path);
   EXCEPTION
      WHEN OTHERS THEN
         RAISE;
   END deregister;

   /**
   * Copy a portlets customization and defaults from one portlet
   * to another.
   *
   * This procedure does nothing!
   *
   */
   PROCEDURE copy (
      p_copy_portlet_info   IN   wwpro_api_provider.copy_portlet_record
   )
   IS
   BEGIN
      NULL;
   END copy;

   /**
   * Describe the parameters that the Portlet understands.
   *
   * The framework will call upon this function if it needs
   * descriptive information about the parameters to the portlet.
   *
   */
   FUNCTION describe_parameters (
      p_provider_id   IN   INTEGER,
      p_language      IN   VARCHAR2
   )
      RETURN wwpro_api_provider.portlet_parameter_table
   IS
      l_params_tab   wwpro_api_provider.portlet_parameter_table;
   BEGIN
      RETURN l_params_tab;
   END describe_parameters;
END menu_pck_portlet;
/

PL/SQL specification for the NAVIGATION_MENU

CREATE OR REPLACE package NAVIGATION_MENU
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
is
   --
   procedure show_menu( p_page_group in number default null
                      , p_start_page_id    in number default 1  -- 1 = Root Page
       , p_current_page_id in number default null -- highlight current page in the menu
       , p_language   in varchar2 default 'us'
       );
   --
   procedure tree_tpl( p_target                in varchar2 default '_main',
            p_empty_img             in varchar2 default '1999.gif',
        p_vertical_line         in varchar2 default '2009.gif',
        p_root_normal           in varchar2 default '1998.gif',
        p_root_selected         in varchar2 default '1998.gif',
        p_root_opened           in varchar2 default '1998.gif',
        p_root_selected_opened  in varchar2 default '1998.gif',
        p_node_normal           in varchar2 default '2000.gif',
        p_node_selected         in varchar2 default '2000.gif',
        p_node_opened           in varchar2 default '2001.gif',
        p_node_selected_opened  in varchar2 default '2001.gif',
        p_leaf_normal           in varchar2 default '2006.gif',
        p_leaf_selected         in varchar2 default '2006.gif',
        p_leaf_opened           in varchar2 default '2006.gif',
        p_leaf_selected_opened  in varchar2 default '2006.gif',
        p_junc_leaf             in varchar2 default '2003.gif',
        p_junc_last_leaf        in varchar2 default '2002.gif',
        p_junc_closed_node      in varchar2 default '2008.gif',
        p_junc_last_closed_node in varchar2 default '2007.gif',
        p_junc_opened_node      in varchar2 default '2005.gif',
        p_junc_last_opened_node in varchar2 default '2004.gif'
                     );
   --
end NAVIGATION_MENU;
/

PL/SQL bodyfor the NAVIGATION_MENU

CREATE OR REPLACE package body NAVIGATION_MENU
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
is
   --
   procedure show_menu( p_page_group in number default null
                      , p_start_page_id    in number default 1  -- 1 = Root Page
       , p_current_page_id in number default null -- highlight current page in the menu
                      , p_language   in varchar2 default 'us') is
   --
   TYPE PageCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   page_cv         PageCurTyp;     -- declare cursor variable
   l_page_group    number;
   l_page_id       number;
   l_level_old     number;         -- Previous Level to check for change of level
   l_level         number;         -- Current Level
   l_parent_id     number;         -- Parent Page ID
   l_display_name  varchar2(300);  -- Display Name of Page
   l_stmnt         varchar2(1000); -- Statement for Dynamic SQL
   l_temp          varchar2(300);  -- Temporary variable
   l_counter       number;         -- Temporary counter
   
   v_commapos      number;
   v_baseurl       varchar(300);
   v_pageurl       varchar(3000);
   v_dadurl        varchar(300);
   v_dadpos        number;
   v_temp_row_counter number;
   v_row_counter number;
   BEGIN
      l_stmnt := 'select level '||
                 ',      f.siteid    page_group '         ||
                 ',      f.id        id '                 ||
                 ',      f.title     display_name '       ||
                 ',      f.parentid  parent_id '          ||
                 'from   portal.wwv_user_corners f '      ||
                 'where  f.siteid = :b_page_group '       ||
              'and    f.type = 1 '                     || -- Not Tabs
              'and    f.active = 1 '                   || -- Only active version
                'and    f.language = :b_language '       || -- In the users language
                 'start with       f.id     = :b_page_id '||
                 'connect by prior f.id     = f.parentid '||
                 'and        prior f.siteid = f.siteid '  ||
                 'order siblings by f.title';
      --
      -- If no Page Group given, take the current Page Group
      if p_page_group is null
      then
         l_temp := portal.wwpro_api_parameters.get_value( '_pageid', 'a');
         l_page_group := to_number(substr(l_temp,1,instr(l_temp,',')-1));
      else
         l_page_group := p_page_group;
      end if;

      --
      -- Get common denominators for the urls to the portal pages
      --
      v_pageurl:=portal.wwpob_page_util.get_page_url( 1, l_page_group);
      v_commapos := instr(v_pageurl,',');
      v_baseurl:=substr(v_pageurl,1,v_commapos);

      v_dadpos:=instr(v_pageurl,'&_dad');
      v_dadurl:=substr(v_pageurl,v_dadpos,(length(v_pageurl)-v_dadpos)+1);

      htp.p('var h = "'||v_baseurl||'";');
      htp.p('var d = "'||v_dadurl||'";');

      l_level_old := 0;
      htp.p('var TREE_ITEMS = [');
    --  htp.p(l_stmnt);
   v_temp_row_counter := -1;
      OPEN page_cv FOR l_stmnt USING l_page_group, p_language, p_start_page_id;
      LOOP
         FETCH page_cv INTO l_level
                           , l_page_group
                           , l_page_id
                           , l_display_name
                           , l_parent_id;     -- fetch next row
         EXIT WHEN page_cv%NOTFOUND;          -- exit loop when last row is fetched
         
   -- 4 following lines are needed to highligt the current page
   v_temp_row_counter := v_temp_row_counter + 1;
   if l_page_id = p_current_page_id then
      v_row_counter := v_temp_row_counter;
      end if;
   --
         -- Check for Level change
         if l_level_old = 0
         then
            -- Print the First line
            null;
         elsif l_level > l_level_old  -- Print a Child
         then
            -- End the previous line
            htp.p(',');
         elsif l_level = l_level_old  -- Print a Sibling
         then
            -- First Close the Prevous Line
            htp.p('],');
         elsif l_level < l_level_old -- Close the Parent
         then
            -- First Close the Prevous Line
            htp.p(']');
            -- Then close all parents
            -- If you go from level 4 to 2 you need to close two parent
            for l_counter in 1..(l_level_old-l_level)
            loop
               -- Then close the Parent on a new line
               --htp.prn(rpad(' ',3*(l_level_old-l_counter),' '));
               htp.p('],');
            end loop;
         end if;
         -- Print current page information
         --htp.prn(rpad(' ',3*l_level,' '));
         htp.prn('['||chr(39));
         htp.prn(replace(l_display_name,'''','\''')||chr(39)||',');
         --
         -- Optimizing output
         --
         htp.prn('h+'||chr(39)||l_page_id||chr(39)||'+d');
         l_level_old := l_level;
      END LOOP;
      --
      -- First Close the Prevous Line
      htp.p(']');
      -- Close all parents that need to be closed
      -- If you go from level 4 to 2 you need to close two parent
      for l_counter in 1..(l_level_old-1)
      loop
         -- Then close the Parent on a new line
         --htp.prn(rpad(' ',3*(l_level_old-l_counter),' '));
         htp.p(']');
      end loop;
      -- Close the array
      htp.p('];');
      --
   htp.p('var v_current_page_arr_id = '|| v_row_counter||';');
   
   exception 
     when others then
    htp.p('An error has occured in procedure menu.navigation_menu.show_menu : '||sqlerrm);
   
   end show_menu;
      --
   procedure tree_tpl( p_target                in varchar2 default '_main',
            p_empty_img             in varchar2 default '1999.gif',
        p_vertical_line         in varchar2 default '2009.gif',
        p_root_normal           in varchar2 default '1998.gif',
        p_root_selected         in varchar2 default '1998.gif',
        p_root_opened           in varchar2 default '1998.gif',
        p_root_selected_opened  in varchar2 default '1998.gif',
        p_node_normal           in varchar2 default '2000.gif',
        p_node_selected         in varchar2 default '2000.gif',
        p_node_opened           in varchar2 default '2001.gif',
        p_node_selected_opened  in varchar2 default '2001.gif',
        p_leaf_normal           in varchar2 default '2006.gif',
        p_leaf_selected         in varchar2 default '2006.gif',
        p_leaf_opened           in varchar2 default '2006.gif',
        p_leaf_selected_opened  in varchar2 default '2006.gif',
        p_junc_leaf             in varchar2 default '2003.gif',
        p_junc_last_leaf        in varchar2 default '2002.gif',
        p_junc_closed_node      in varchar2 default '2008.gif',
        p_junc_last_closed_node in varchar2 default '2007.gif',
        p_junc_opened_node      in varchar2 default '2005.gif',
        p_junc_last_opened_node in varchar2 default '2004.gif'
                     ) is
    l_hostname  varchar2(100);  -- Hostname
   BEGIN
 l_hostname := wwpob_page.get_baseurl;
 htp.p(l_hostname);
    htp.p('var tree_tpl = {');
 htp.p('''target'''||' : '''||p_target||''', // name of the frame links will be opened in');
 htp.p('                        // other possible values are: _blank, _parent, _search, _self and _top'); -- Make LOV in Portal to select
    htp.p('');
 htp.p('''icon_e'''||' : '||''''||l_hostname||'docs/'||p_empty_img||''''||', // empty image');
 htp.p('''icon_l'''||' : '||''''||l_hostname||'docs/'||p_vertical_line||''''||',  // vertical line');
    htp.p('');
 htp.p('''icon_48'''||' : '||''''||l_hostname||'docs/'||p_root_normal||''''||',   // root icon normal');
 htp.p('''icon_52'''||' : '||''''||l_hostname||'docs/'||p_root_selected||''''||',   // root icon selected');
 htp.p('''icon_56'''||' : '||''''||l_hostname||'docs/'||p_root_opened||''''||',   // root icon opened');
 htp.p('''icon_60'''||' : '||''''||l_hostname||'docs/'||p_root_selected_opened||''''||',   // root icon selected');
    htp.p('');
 htp.p('''icon_16'''||' : '||''''||l_hostname||'docs/'||p_node_normal||''''||', // node icon normal');
 htp.p('''icon_20'''||' : '||''''||l_hostname||'docs/'||p_node_selected||''''||', // node icon selected');
 htp.p('''icon_24'''||' : '||''''||l_hostname||'docs/'||p_node_opened||''''||', // node icon opened');
 htp.p('''icon_28'''||' : '||''''||l_hostname||'docs/'||p_node_selected_opened||''''||', // node icon selected opened');
    htp.p('');
 htp.p('''icon_0'''||' : '||''''||l_hostname||'docs/'||p_leaf_normal||''''||', // leaf icon normal');
 htp.p('''icon_4'''||' : '||''''||l_hostname||'docs/'||p_leaf_selected||''''||', // leaf icon selected');
 htp.p('''icon_8'''||' : '||''''||l_hostname||'docs/'||p_leaf_opened||''''||', // leaf icon opened');
 htp.p('''icon_12'''||' : '||''''||l_hostname||'docs/'||p_leaf_selected_opened||''''||', // leaf icon selected');
    htp.p('');
 htp.p('''icon_2'''||' : '||''''||l_hostname||'docs/'||p_junc_leaf||''''||', // junction for leaf');
 htp.p('''icon_3'''||' : '||''''||l_hostname||'docs/'||p_junc_last_leaf||''''||',       // junction for last leaf');
 htp.p('''icon_18'''||' : '||''''||l_hostname||'docs/'||p_junc_closed_node||''''||', // junction for closed node');
 htp.p('''icon_19'''||' : '||''''||l_hostname||'docs/'||p_junc_last_closed_node||''''||',       // junctioin for last closed node');
 htp.p('''icon_26'''||' : '||''''||l_hostname||'docs/'||p_junc_opened_node||''''||',// junction for opened node');
 htp.p('''icon_27'''||' : '||''''||l_hostname||'docs/'||p_junc_last_opened_node||''''||'       // junctioin for last opended node');
    htp.p('};');
    htp.p('');
   end tree_tpl;
  --
end NAVIGATION_MENU;
/

Seeding the translations for the package danish/english

--
-- Connect as portal 
--
@@..\unseed.sql menu

set serveroutput on size 1000000
set define off

declare
    l_domain     VARCHAR2(50)  := 'some_domain';
    l_sub_domain VARCHAR2(50) := 'menu';
    l_us         VARCHAR2(2)  := wwnls_api.AMERICAN;
    l_dk         VARCHAR2(2)  := wwnls_api.DANISH;
    l_string_id  integer;
    l_name       VARCHAr2(64);
begin

dbms_output.put_line('Adding NLS strings...');

l_name := 'txttitle';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Menu');

l_name := 'txtdescription';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
   'Javascript menu for traversing pagegroup structures in portal');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Javascript menu som gŸr det muligt at vise side struktur i portal');

l_name := 'txtcustomize';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Customize menu');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Tilpas menuen');

l_name := 'txticon_location';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'You need to specific the path to the page where you have uploaded the menu icons (ex. /PORTAL_DOC/DOC_DESIGNER - "/page_group/page_name/page_name...") . Then click Apply/OK.');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Du skal fŸrst specificere stien til den side hvod du har uploadet menu ikonerne (eks. /PORTAL_DOC/DOC_DESIGNER - "/side_gruppe/sidenavn/sidenavn..."). SŠsubmit siden.');


l_name := 'txtportlet_title';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'The portlet title');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Portletens title');

l_name := 'txtpage_group_id';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Page group');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Side gruppe');

l_name := 'txtstart_page_id';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Root page for menu');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Rod side for menu');


l_name := 'txttarget';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Target');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbnes i');

l_name := 'txticon_page_path';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Path to icon page');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Sti til ikon side');

l_name := 'txtempty_img';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Empty filler icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Tomt/blank ikon');

l_name := 'txtvertical_line';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Vertical line icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Ikon for vertikal linie ');

l_name := 'txtroot_normal';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Root icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Rod ikon');

l_name := 'txtroot_selected';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Root selected icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Valgt rod ikon');

l_name := 'txtroot_opened';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Root opened icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbnet rod ikon');

l_name := 'txtroot_selected_opened';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Root selected opened icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbnet og valft rod ikon');

l_name := 'txtnode_normal';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Folder normal icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Normalt biblioteks ikon');

l_name := 'txtnode_selected';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Folder selected icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Valgt biblioteks ikon');

l_name := 'txtnode_opened';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Folder opened icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbnet folder ikon');

l_name := 'txtnode_selected_opened';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Folder opened and selected icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Valgt og Åbnet folder ikon');

l_name := 'txtleaf_normal';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Leaf normal icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Blad ikon');

l_name := 'txtleaf_selected';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Leaf selected icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Valgt blad ikon');

l_name := 'txtleaf_opened';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Leaf opened icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbnet blad ikon');

l_name := 'txtleaf_selected_opened';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Leaf selected and opened icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Valgt og Åbnet blad ikon');

l_name := 'txtjunc_leaf';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Junction leaf icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'T ikon');

l_name := 'txtjunc_last_leaf';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Junction last leaf icon');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'T ikon');

l_name := 'txtjunc_closed_node';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Junction closed folder');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Lukket T ikon');

l_name := 'txtjunc_last_closed_node';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Junction last closed folder');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Lukket T ikon');

l_name := 'txtjunc_opened_node';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Junction opened folder');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbent T ikon');

l_name := 'txtjunc_last_opened_node';
l_string_id := wwnls_api.add_string(l_domain, l_sub_domain, l_name, l_us,
 'Junction last opened folder');
wwnls_api.set_string(l_domain, l_sub_domain, l_name, l_dk,
 'Åbent T ikon');



commit;
dbms_output.put_line('Seed data successfully installed.');
exception
    when others then
        dbms_output.put_line('ERROR: Could not install seed data.');
        rollback;
end;
/

The modified Tigra Tree Menu javascript

// Title: Tigra Tree
// Description: See the demo at url
// URL: http://www.softcomplex.com/products/tigra_menu_tree/
// Version: 1.1
// Date: 11-12-2002 (mm-dd-yyyy)
// Contact: feedback@softcomplex.com (specify product title in the subject)
// Notes: This script is free. Visit official site for further details.
//
// THIS VERSION IS NOT THE OFFICIAL VERSION VISIT THE ABOVE MENTIONED SITE FOR
// THE OFFICIAL VERSION
//
// Changes made: The menu now has persistance (by using a cookie)
// Changed by  : kennethD.O.TthormanA.TgmailD.O.Tcom
// Changed the : 20-March-2003
//
// Changes compared to official version
// *  = New sub routine
// ** = Modified sub routine

function tree (a_items, a_template) {

 this.a_tpl      = a_template;
 this.a_config   = a_items;
 this.o_root     = this;
 this.a_index    = [];
 this.o_selected = null;
 this.n_depth    = -1;
   this.cookie = document.cookie;                  // *
       
 var o_icone = new Image(),
       o_iconl = new Image();
 o_icone.src = a_template['icon_e'];
 o_iconl.src = a_template['icon_l'];
 a_template['im_e'] = o_icone;
 a_template['im_l'] = o_iconl;
 for (var i = 0; i < 64; i++)
  if (a_template['icon_' + i]) {
   var o_icon = new Image();
   a_template['im_' + i] = o_icon;
   o_icon.src = a_template['icon_' + i];
  }

 this.setCurrState = function (label,persistence) {    // *
                          //this.cookie = document.cookie = "currState=" + persistence;
                          this.cookie = document.cookie = label + "=" + persistence;
                       };

 this.getCurrState = function (label) {               // *
                        //var label = "currState=";
                        label = label + "=";
                        var labelLen = label.length;
                        var cLen = this.cookie.length;
                        var i = 0;
                          while (i < cLen) {
                             var j = i + labelLen;
                             if (this.cookie.substring(i,j) == label) {
                                var cEnd = this.cookie.indexOf(";",j);
                                if (cEnd == -1) {
                                   cEnd = this.cookie.length;
                                }
                                return unescape(this.cookie.substring(j,cEnd));
                              }
                              i++
                           }
                           return ""
                        };
   
 
 this.toggle = function (n_id) {                 // **
                  var o_item = this.a_index[n_id];
                  o_item.open(o_item.b_opened);
                    
                    var temp_cookie = this.getCurrState("currState");

                    // If open insert a flag into the cookie so it remebers
                    if (o_item.b_opened) {
                       temp_cookie = temp_cookie + ':' + o_item.n_id + ':';
                       this.setCurrState("currState",temp_cookie);
                    }
                    // If closed remove the flag in the cookie
                    else {
                       var removeId = new RegExp (":"+n_id+":","g");
                       temp_cookie = temp_cookie.replace(removeId,'');
                       this.setCurrState("currState",temp_cookie);
                    }
                 };


 this.select = function (n_id) { return this.a_index[n_id].select(); };
 this.mout   = function (n_id) { this.a_index[n_id].upstatus(true) };
 this.mover  = function (n_id) { this.a_index[n_id].upstatus() };

 this.a_children = [];

 for (var i = 0; i < a_items.length; i++)
  new tree_item(this, i);

 this.n_id = trees.length;
 trees[this.n_id] = this;

 for (var i = 0; i < this.a_children.length; i++) {
  document.write(this.a_children[i].init());
  this.a_children[i].open();
 }
 
 // Here we are opening the folders which have a open flag in the cookie
 var temp_cookie = this.o_root.getCurrState("currState");    
   if (temp_cookie.length > 0) {
      var re= new RegExp (":","g");
      var cookie_arr = temp_cookie.split(re);

      for (var x = 0; x < cookie_arr.length; x++) {
         var o_item = this.a_index[cookie_arr[x]];
       if (o_item) {
          o_item.open(o_item.b_opened);
       }
    }
   }
   
   // Make the current url bold in the menu tree
   //var active_id = this.o_root.getCurrState("active_id");
   var active_id = v_current_page_arr_id;
   //if (active_id.length > 0) {
      if (get_element('i_txt' + this.o_root.n_id + '_' + active_id)) {
         get_element('i_txt' + this.o_root.n_id + '_' + active_id).style.fontWeight = 'bold';
      }
   //}

}

function tree_item (o_parent, n_order) {

 this.n_depth  = o_parent.n_depth + 1;
 this.a_config = o_parent.a_config[n_order + (this.n_depth ? 2 : 0)];
 if (!this.a_config) return;

 this.o_root    = o_parent.o_root;
 this.o_parent  = o_parent;
 this.n_order   = n_order;
 this.b_opened  = !this.n_depth;

 this.n_id = this.o_root.a_index.length;
 this.o_root.a_index[this.n_id] = this;
 o_parent.a_children[n_order] = this;

 this.a_children = [];

 for (var i = 0; i < this.a_config.length - 2; i++)
  new tree_item(this, i);

 this.get_icon = item_get_icon;
 this.open     = item_open;
 this.select   = item_select;
 this.init     = item_init;
 this.upstatus = item_upstatus;
 this.is_last  = function () { return this.n_order == this.o_parent.a_children.length - 1 };
}

function item_open (b_close) {
 var o_idiv = get_element('i_div' + this.o_root.n_id + '_' + this.n_id);
 if (!o_idiv) return;
   
    
 if (!o_idiv.innerHTML) {
  var a_children = [];
  for (var i = 0; i < this.a_children.length; i++)
   a_children[i]= this.a_children[i].init();
  o_idiv.innerHTML = a_children.join('');
 }
 o_idiv.style.display = (b_close ? 'none' : 'block');
 
 this.b_opened = !b_close;
 var o_jicon = document.images['j_img' + this.o_root.n_id + '_' + this.n_id],
  o_iicon = document.images['i_img' + this.o_root.n_id + '_' + this.n_id];
 if (o_jicon) o_jicon.src = this.get_icon(true);
 if (o_iicon) o_iicon.src = this.get_icon();
 this.upstatus();
}

function item_select (b_deselect) {
 if (!b_deselect) {
  var o_olditem = this.o_root.o_selected;
  this.o_root.o_selected = this;
  if (o_olditem) o_olditem.select(true);
 }
 var o_iicon = document.images['i_img' + this.o_root.n_id + '_' + this.n_id];
 if (o_iicon) o_iicon.src = this.get_icon();
 
 this.o_root.setCurrState("active_id",this.n_id);
   // Make the selected item bold on the new page as well 
 //get_element('i_txt' + this.o_root.n_id + '_' + this.n_id).style.fontWeight = b_deselect ? 'normal' : 'bold';
 
 this.upstatus();
 return Boolean(this.a_config[1]);
}

function item_upstatus (b_clear) {
 window.setTimeout('window.status="' + (b_clear ? '' : this.a_config[0] + (this.a_config[1] ? ' ('+ this.a_config[1] + ')' : '')) + '"', 10);
}

function item_init () {
 var a_offset = [],
     o_current_item = this.o_parent;

 for (var i = this.n_depth; i > 1; i--) {
  a_offset[i] = '<img src="' + this.o_root.a_tpl[o_current_item.is_last() ? 'icon_e' : 'icon_l'] + '" border="0" align="absbottom">';
  o_current_item = o_current_item.o_parent;
 }
       
 return '<table cellpadding="0" cellspacing="0" border="0"><tr><td nowrap>' + (this.n_depth ? a_offset.join('') + (this.a_children.length
  ? '<a href="javascript: trees[' + this.o_root.n_id + '].toggle(' + this.n_id + ')" onmouseover="trees[' + this.o_root.n_id + '].mover(' + this.n_id + ')" onmouseout="trees[' + this.o_root.n_id + '].mout(' + this.n_id + ')"><img src="' + this.get_icon(true) + '" border="0" align="absbottom" name="j_img' + this.o_root.n_id + '_' + this.n_id + '"></a>'
  : '<img src="' + this.get_icon(true) + '" border="0" align="absbottom">') : '') 
  + '<a href="' + this.a_config[1] + '" target="' + this.o_root.a_tpl['target'] + '" onclick="return trees[' + this.o_root.n_id + '].select(' + this.n_id + ')" ondblclick="trees[' + this.o_root.n_id + '].toggle(' + this.n_id + ')" onmouseover="trees[' + this.o_root.n_id + '].mover(' + this.n_id + ')" onmouseout="trees[' + this.o_root.n_id + '].mout(' + this.n_id + ')" class="t' + this.o_root.n_id + 'i" id="i_txt' + this.o_root.n_id + '_' + this.n_id + '"><img src="' + this.get_icon() + '" border="0" align="absbottom" name="i_img' + this.o_root.n_id + '_' + this.n_id + '" class="t' + this.o_root.n_id + 'im">' + this.a_config[0] + '</a></td></tr></table>' + (this.a_children.length ? '<div id="i_div' + this.o_root.n_id + '_' + this.n_id + '" style="display:none"></div>' : '');
   
}

function item_get_icon (b_junction) {
 return this.o_root.a_tpl['icon_' + ((this.n_depth ? 0 : 32) + (this.a_children.length ? 16 : 0) + (this.a_children.length && this.b_opened ? 8 : 0) + (!b_junction && this.o_root.o_selected == this ? 4 : 0) + (b_junction ? 2 : 0) + (b_junction && this.is_last() ? 1 : 0))];
}

var trees = [];
get_element = document.all ?
 function (s_id) { return document.all[s_id] } :
 function (s_id) { return document.getElementById(s_id) };