create or replace package body cdwp_fil
is

/*****************************************************************************************
Purpose         HTML Report of File Definitions for ODWA

Usage

Remarks         required table create table my_blob_table (row_label varchar2(100), contents_blob blob)

Revision history
When        Who                                Construct
  Revision  What
------------------------------------------------------------------------------------------
$revision_history$
05-sep-2003   Kannan Parthasarathy
              -B3073162: DBMS_LOB.LOBMAXSIZE is now a 20 digit number in 10g database.
08-aug-2001   Lucas Jellema
  1.18        - create procedure warn_compressed_file that returns the contents of the htp.buffer
27-jul-2001   Lucas Jellema
  1.17        - use servlet for downloading files
              changes in 
              * function url_download_file
              * procedure form_download_file_request
              
              
12-mar-2001   Lucas Jellema                     
  1.16        - procedure form_download_file_request
                even if g_servlets_enabled = true, still do not rely on servlet for file download
              
              - do not include filename or p_filename in download file form 
01-mar-2001   Lucas Jellema                     
  1.15        - explicitly update file kind; set to non-compressed 
28-feb-2001   Lucas Jellema                     
  1.14        - do not perform download of compressed file; show a warning window instead. 
29-jan-2001   Lucas Jellema                     
  1.13        - use odwapref for host name (URL base) for servlets to support file upload 
19-jan-2001   Lucas Jellema                     
  1.12        Donwload file should not set mime-type
              It should set file size and filename 
10-jan-2001   Lucas Jellema                     
  1.11        - remove copy_file procedure (and dependency on MY_LOB_TABLE
              - add procedure CLEAR_FILE
              - add procedure set_file_Crc 
21-dec-2000   Lucas Jellema                     
  1.10        New: procedure touch_file (autonomous transaction, update of i$sdd_files)
              
              Provide a 'touch file' methd (to set the audit properties) 
20-dec-2000   Lucas Jellema                     
  1.9         New: compare_files
              Changed: update_files (now set CRC to NULL) 
18-dec-2000   Lucas Jellema                     
  1.8         New: servlet_urlbase - public function that returns the base of the URLs required to invoke the servlets supporting the file oriented functionality in ODWA6i. 
22-sep-2000   Lucas Jellema                      
  1.6         Download_File: Support MIME types for Excel files 
10-aug-2000   Lucas Jellema
  1.4         Add code to download files
01-aug-2000 Lucas Jellema
1.3         - add VHV icon in short_list files for immediate access to VHV
26-jul-2000 Lucas Jellema
1.2         Add attributes: Check State and Version Label
- display check state as one of the attributes; add lock flag as well!

25-jul-2000 Lucas Jellema
1.1         Support for dynamically built query, ordered by name, type, size or date modified and Ascending or Descending. Use of generic cdwpbase.get_ivid_list procedure; update c_files cursor to accept only a p_ivid as parameter.
24-jul-2000 Lucas Jellema
1.0         Initial creation for ODWA 6i
14-jul-2000 Lucas Jellema
  1.0       Creation
1.3
- display VHV icon behind all files for immediate access to VHV
1.2
- display check state as one of the attributes; add lock flag as well!
*****************************************************************************************/

--
-- private constants
--
REVISION_LABEL        constant varchar2(30) := '$x.y:: 1.18           $';
PACKAGE_NAME          constant varchar2(30) := 'CDWP_FIL';
   NL_CHAR  constant  char(1) := '
';

g_page_size           number(20):= 30000;   -- how large (in bytes) should be the size of a page when showing the contents of a file

g_servlets_enabled    boolean default false; 
   --
   -- cursor to select properties of one or more table definitions in a specific
   -- application system (owned by or shared)
   --
   cursor c_file
   (      b_file_ivid     in number
   )
   is
   select files.id                    file_id
   ,      files.irid                  file_irid
   ,      files.ivid                  file_ivid
   ,      files.name                  file_name
   ,      files.FILE_SIZE
   ,      files.KIND
   ,      files.OS_PRIVILEGES
   ,      files.OS_TIMESTAMP
   ,      files.OWNER
   ,      files.SHORT_DESCRIPTION
   ,      files.TEXT_FORMAT
   ,      files.contents_blob
   ,      files.date_changed
   from   ci_primary_access_files  files
   where  files.ivid                  = b_file_ivid
   ;

   r_file c_file%rowtype;


-- this function returns the base of URLs required to access the Servlets that run in a WebServer 
-- and implement part of the File Manipulating capabilities of ODWA.
-- The Base URL is used in the hyperlinks for Uploading and Downloading files.
function servlet_urlbase
return varchar2
is
begin
  return odwapref.get_value('servlet_urlbase');
end; -- servlet_urlbase
   
   
-- this function returns the URL that is appropriate for downloading the indicated file
-- in the current environment; that may mean a URL to a PL/SQL package reading straight
-- from the LOB column in the i$sdd_files table or a URL to a servlet can uses the 
-- Java File API to download the file from the Repository to the WebServer file system
function url_download_file
( p_irid          in number
, p_ivid          in number
, p_filename      in varchar2
, p_label         in varchar2
, p_download      in boolean  default true  -- when false that means that the URL may support View File first (resorting to Download for unknown Mime Types)
, p_form_included in boolean  default false -- when true, the HTML page already contains the request form for the Download request; that means the URL itself can be simpler
) return varchar2
is
begin
  if g_servlets_enabled
  then
    if p_form_included
    then
      return htf.anchor
             ( curl =>  'javascript:dlf('''||to_char(p_ivid)||''', '''||p_filename||''')'
             , ctext => p_label
             );
    else
      return htf.anchor
             ( curl =>  servlet_urlbase||'oracle.des.rob.standardServices.DoReposDownload/'||replace(p_filename,' ','+')
             ||'?'
             ||'ivid='||to_char(p_ivid)
             ||chr(38)||'filename='||replace( p_filename,' ','+')
             , ctext => p_label
             );
    end if;  -- p_form_included
  else
  -- for environments without Servlets: 
    if p_form_included
    then
      return htf.anchor
             ( curl =>  'javascript:dlf('''||to_char(p_ivid)||''')'
             , ctext => p_label
             );
    else
      return htf.anchor
             ( curl =>  'cdwp_fil.download_file'
             ||'?'
             ||'p_session_id='||to_char(odwactxt.get_session_id)
             ||chr(38)||'p_ivid='||to_char(p_ivid)
             , ctext => p_label
             );
    end if;  -- p_form_included
  end if;
end; -- url_download_file




-- this function will write the Javascript function to be included in the HEAD section of the HTML
-- page; this function will be called to issue a file download request, using the download_file_request_form
procedure js_download_file_request
is
begin
  if g_servlets_enabled
  then
    htp.p
    (' <SCRIPT LANGUAGE="JavaScript"><!--
    function dlf( ivid, filename)
    {
      document.dlfReqForm.p_ivid.value = ivid;
     // document.dlfReqForm.p_filename.value = filename;
      document.dlfReqForm.submit();
    }
    //--></SCRIPT>
  ');
  else
    htp.p
    (' <SCRIPT LANGUAGE="JavaScript"><!--
    function dlf( ivid)
    {
      document.dlfReqForm.p_ivid.value = ivid;
      document.dlfReqForm.submit();
    }
    //--></SCRIPT>
  ');
  end if;
end; -- js_download_file_request

-- this function return a string containing the HTML definition of a FORM that will 
-- be used to request download of a file. Depending on the environment, the form may
-- serve requests to the PL/SQL download_file procedure or the Servlet DoDownLoad.
procedure form_download_file_request
is
  l_form_html varchar2(4000);
begin
  if g_servlets_enabled -- 1.16  if servlets are enabled, we use them for download!
  then
    l_form_html:= l_form_html
    ||htf.formopen
      ( curl    => servlet_urlbase||'oracle.des.rob.standardServices.DoReposDownload'
      , cmethod => 'POST'
      , cattributes => 'NAME="dlfReqForm"'
      );
    l_form_html:= l_form_html
    ||htf.formhidden(cname => 'ivid')
    ||htf.formhidden(cname => 'filename')
    ;
    l_form_html:= l_form_html
    ||htf.formclose
    ;
  else
    l_form_html:= l_form_html
    ||htf.formopen
      ( curl    => 'cdwp_fil.download_file'
      , cmethod => 'POST'
      , cattributes => 'NAME="dlfReqForm"'
      );
    l_form_html:= l_form_html
    ||htf.formhidden(cname => 'p_session_id' ,cvalue => to_char(odwactxt.get_session_id))
    ||htf.formhidden(cname => 'p_ivid')
   -- ||htf.formhidden(cname => 'p_filename')
    ;
    l_form_html:= l_form_html
    ||htf.formclose
    ;
  end if; -- Servlet or PL/SQL implementation
  htp.p( l_form_html);
end; -- url_download_file


procedure short_list_files
( p_session_id  in   number
, p_app_id      in   number  default -1
)
is
begin
    odwactxt.update_context
    ( p_session_id    => p_session_id
    , p_package_name  => PACKAGE_NAME
    , p_procedure_name=> 'short_list_files'
    );
    odwarprt.short_list_of_type_instances
    ( p_session_id        => p_session_id
    , p_el_type_of        => 'FILES'
    , p_app_irid          => odwactxt.get_folder_irid
    , p_plural_name       => 'Files'
    , p_order_by          => 'name'
    , p_descriptor_column => 'name'
    );
end; -- short_list_files



procedure write_piece_of_file
( p_length in number default g_page_size
, p_offset in number default 1
, p_blob   in blob
) is
begin
  htp.p('<HR WIDTH="100%">');
  htp.p
  ( '<pre>'
  ||htf.escape_sc
    ( utl_raw.cast_to_varchar2
      ( dbms_lob.substr
        ( lob_loc =>   p_blob
        , amount  =>   p_length  -- g_page_size
        , offset  =>   p_offset
        )
      )
    )
   ||'</pre>'
   );

end; -- write_piece_file


procedure show_file
( p_session_id  in   number
, p_irid        in   number  default null
, p_ivid        in   number  default null
, p_page        in   number  default 1
) is

l_first         boolean:= true;
l_blob          blob;
l_toc           varchar2(30000);
l_num_pages     number(5);
   begin -- list_files
     odwactxt.update_context
     ( p_session_id    => p_session_id
     , p_workarea_irid => cdwpbase.ifThenElse
                          ( p_ivid is not null
                          , null
                          , -1  -- is default value that indicates no change to the parameter
                          )
     , p_package_name  => PACKAGE_NAME
     , p_procedure_name=> 'show_file'
     , P_PAC_IRID      => p_irid
     , P_PAC_IvID      => p_ivid
     , P_PAC_TYPE_ID   => 4819
     );
    open c_file( b_file_ivid => nvl( p_ivid, cdwpbase.get_ivid(p_irid)));
    fetch c_file
    into  r_file;
    close c_file;
    odwactxt.update_context
    ( P_PAC_IRID      => r_file.file_irid
    , P_PAC_IvID      => r_file.file_ivid
    , p_new_request   => false
    );
    l_num_pages:= ceil(r_file.file_size/g_page_size);
    htp.htmlOpen;
    htp.headOpen;
    cdwp.write_about(package_name, revision_label);
    cdwp.include_report_styles;
    htp.title
    ( cdwpbase.get_el_name
      ( p_el_id      => odwactxt.get_folder_irid
      , p_el_type_of => 'APP'
      )
    ||' - Files - '
    ||cdwpbase.get_el_name( p_el_id => p_irid, p_el_type_of => 'FILES')
    ||' - page '||to_char(p_page)||'/'||to_char(l_num_pages)
    );
    htp.headClose;
    htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
    -- cdwp.bookmark(r_file.file_name);
    cdwp.menu_bar
    ( p_level => 2
    , p_el_short_name=> 'FILES'
    , p_el_id        => p_irid
    );
    cdwp.heading(3);
    cdwp.p
    ( cdwp.add_images('{'||cdwpbase.get_icon('FILES')||'}')
    ||cdwpbase.get_el_name( p_el_id => p_irid, p_el_type_of => 'FILES')
    ||' - page '||to_char(p_page)||'/'||to_char(l_num_pages)
    , p_style => 'partitle'
    );
    htp.nl;
    cdwp.tableOpen('BORDER="0"');
    odwavrsn.version_status;
    cdwpbase.app_summary
    ( p_el_id               => p_irid
    );
    -- now table of contents/links to all pages
    cdwp.tableRowOpen;
    cdwp.tableDataHeading
    ( 'Pages'
    );
    for i in 1..l_num_pages loop
       l_toc:= l_toc
               ||cdwpbase.ifThenElse
                 ( i > 1
                 , cdwpbase.nbsp(1)
                 ||','
                 )
               || '<A href="cdwp_fil.show_file?p_session_id='||to_char(p_session_id)
                   ||chr(38)||'p_ivid='||to_char(p_ivid)
                   ||chr(38)||'p_page='||to_char(i)
                   ||'">'
                   ||to_char(i)
                   ||' </A>'
               ;
     end loop; -- i in 1..number of pages
     cdwp.tableDataValue
      ( l_toc
      );
     cdwp.tableRowClose;
     cdwp.tableClose;
     write_piece_of_file
     ( p_length => g_page_size
     , p_offset => g_page_size * (p_page - 1) + 1
     , p_blob   => r_file.contents_blob
     );
     htp.bodyClose;
     htp.htmlClose;
     cdwpbase.reset_context;
   end; -- show_file

procedure warn_compressed_file
( p_ivid        in  varchar2
, p_text        out varchar2
) is
  l_ivid number(38) := to_number(p_ivid);
  l_page htp.htbuf_arr;
  l_len number(20):=9999;
  l_text varchar2(32000);
   begin -- list_files
    htp.htmlOpen;
    htp.headOpen;
    cdwp.write_about(package_name, revision_label);
    cdwp.include_report_styles;
    htp.title
    ( ROB_msg.getMsg(Rob_msg.DSP140_CDWP_DOWNLOADFILE, '', '', '', '')
    ||cdwpbase.get_el_name_for_ivid(p_el_ivid =>  l_ivid)
    ||ROB_msg.getMsg(Rob_msg.DSP141_CDWP_WARNINGMSG, '', '', '', '')
    );
    htp.headClose;
    htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
    cdwp.p
    ( cdwpbase.get_el_name_for_ivid(p_el_ivid =>  l_ivid)
    , p_style => 'partitle'
    );
    htp.nl;
    cdwp.tableOpen('BORDER="0"');
    cdwp.tableRowOpen;
    cdwp.tableDataHeading
    ( ROB_msg.getMsg(Rob_msg.DSP142_CDWP_WARNING, '', '', '', '')
    );
     cdwp.tableDataValue
      ( ROB_msg.getMsg(Rob_msg.DSP143_CDWP_ERRORMSG, '', '', '', '')
      );
     cdwp.tableRowClose;
     cdwp.tableRowOpen;
     cdwp.tableDataHeading
     ( ''
     );
      htp.formopen
      ( curl        => ''
      , cmethod     => 'POST'
      , cattributes => 'NAME="buttonForm"'
      );
      htp.tabledata
      ( '<INPUT TYPE=BUTTON onCLick="javascript:history.back()" value="OK"
      ');
     htp.formClose;
     cdwp.tableRowClose;
     cdwp.tableClose;
     htp.bodyClose;
     htp.htmlClose;
    
     htp.get_page(thepage => l_page, irows => l_len);
     for i in 1..l_len loop
       l_text:= l_text||l_page(i);
     end loop;
     -- now remove from the HTP buffer the piece before <HTML
     -- this piece contains the MIME TYPE and CONTENT LENGTH
     p_text:= substr ( l_text, instr(l_text,'<HTML'));
   end; -- warn_compressed_file
   

procedure warn_compressed_file
( p_session_id  in   number
, p_irid        in   number  default null
, p_ivid        in   number  default null
) is

   begin -- list_files
    htp.htmlOpen;
    htp.headOpen;
    cdwp.write_about(package_name, revision_label);
    cdwp.include_report_styles;
    htp.title
    ( cdwpbase.get_el_name
      ( p_el_id      => odwactxt.get_folder_irid
      , p_el_type_of => 'APP'
      )
    ||' - Files - '
    ||cdwpbase.get_el_name( p_el_id => p_irid, p_el_type_of => 'FILES')
    ||ROB_msg.getMsg(Rob_msg.DSP141_CDWP_WARNINGMSG, '', '', '', '')
    );
    htp.headClose;
    htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
    cdwp.heading(3);
    cdwp.p
    ( cdwp.add_images('{'||cdwpbase.get_icon('FILES')||'}')
    ||cdwpbase.get_el_name( p_el_id => p_irid, p_el_type_of => 'FILES')
    , p_style => 'partitle'
    );
    htp.nl;
    cdwp.tableOpen('BORDER="0"');
    cdwp.tableRowOpen;
    cdwp.tableDataHeading
    ( ROB_msg.getMsg(Rob_msg.DSP142_CDWP_WARNING, '', '', '', '')
    );
     cdwp.tableDataValue
      ( ROB_msg.getMsg(Rob_msg.DSP143_CDWP_ERRORMSG, '', '', '', '')
      );
     cdwp.tableRowClose;
     cdwp.tableRowOpen;
     cdwp.tableDataHeading
     ( ''
     );
      htp.formopen
      ( curl        => ''
      , cmethod     => 'POST'
      , cattributes => 'NAME="buttonForm"'
      );
      htp.tabledata
      ( '<INPUT TYPE=BUTTON onCLick="javascript:history.back()" value="OK"
      ');
     htp.formClose;
     cdwp.tableRowClose;
     cdwp.tableClose;
     htp.bodyClose;
     htp.htmlClose;
   end; -- warn_compressed_file

   
   procedure download_file
   ( p_session_id  in   number
   , p_irid        in   number  default null
   , p_ivid        in   number  default null
   ) is
    l_lob       blob;
    l_raw       raw(4096);
    l_file_name i$sdd_files.name%TYPE;  -- varchar2(256)
    l_kind      i$sdd_files.kind%TYPE;
    l_off       number(12):= 1;
    l_amt       number(6) := 4096;
    l_file_size number(10);
    l_lob_size  number(10);

    function find_mime_type
    ( p_file_extension in varchar2
    , p_kind           in varchar2 default 'BLOB'
    ) return varchar2
    is
      l_mime_type varchar2(60):='application/xxx' ;
    begin
        -- 1.17 relocated this piece of code to the beginning to provide default setting for mime types
        -- and also allow refining the mime type value
        if p_kind ='CLOB' -- text file
        then
          l_mime_type:= 'text/xxx';
        else -- l_kind = 'BLOB' : binary file
          l_mime_type:= 'application/xxx';
        end if;

      -- indicate the mime type of the document
      if p_file_extension in ('gif', 'jpg')
      then
        l_mime_type:= 'image/gif';
      elsif p_file_extension in ('jar', 'zip', 'tgz', 'tz', 'arc', 'arj', 'lzh')
      then
        l_mime_type:= 'application/x-compressed';
      elsif p_file_extension in ('doc','dot','rtf')
      then
        l_mime_type:= 'application/ms-word';
      elsif p_file_extension in ('xls', 'xlt', 'xlm', 'xll', 'xlw', 'xla', 'xld')
      then
        l_mime_type:= 'application/vnd.ms-excel';
      elsif p_file_extension in ('ppt', 'pot', 'ppa', 'pps', 'pwz')
      then
        l_mime_type:= 'application/vnd.ms-powerpoint';
      elsif p_file_extension in ('htm','html')
      then
        l_mime_type:= 'text/html';
      elsif p_file_extension in ('pdf')
      then
        l_mime_type:= 'application/pdf';
      else
        -- standard MIME types:
        -- application/pdf
        -- image/gif
        -- application/x-compressed  jar, zip, tgz, tz, arc, arj, lzh,
        -- text/html HTM, HTML,
        -- application/vnd.ms-excel XLS, XLT, XLM, XLL, XLW, XLA, XLD
        -- application/vnd.ms-powerpoint  PPT POT PPA PPS PWZ
        -- application/msword   DOC DOT RTF
        -- audio/wav
        if p_kind ='CLOB' -- text file
        then
          l_mime_type:= 'text/xxx';
        else -- l_kind = 'BLOB' : binary file
          l_mime_type:= 'application/xxx';
        end if;
      end if; -- p_file_extension in ('gif')
      
      return l_mime_type;
    end; -- find_mime_type

   begin
     -- get BLOB handle for File
    select contents_blob
    ,      name
    ,      kind
    ,      file_size
    into   l_lob
    ,      l_file_name
    ,      l_kind
    ,      l_file_size
    from   i$sdd_files
    where  ivid = p_ivid
    ;
    if l_kind in ('X','Y','Z')  -- COMPRESSED FILE
    then
      warn_compressed_file
      ( p_session_id  => odwactxt.get_session_id
      , p_irid        => p_irid
      , p_ivid        => p_ivid
      );
    else
  
      l_lob_size:= dbms_lob.getlength(l_lob);
      --  internally the following call does:
      --  ->  htp.prn('Content-type: '||ccontent_type||NL_CHAR);
      owa_util.mime_header
      ( ccontent_type=> find_mime_type
                        ( p_file_extension => lower( substr
                                              ( l_file_name
                                              , 1 + instr( l_file_name, '.', -1)  -- find first . starting at the end of the filename
                                              ))
                        , p_kind           => l_kind
                        )
      , bclose_header => false
      );
  --    htp.prn('Content-Disposition: inline; filename='||l_file_name||NL_CHAR);
      htp.prn('Content-Disposition: attachment; filename="'||l_file_name||'"'||NL_CHAR);
      htp.prn('Content-Length: '||to_char(l_file_size)||''||NL_CHAR);
          owa_util.http_header_close;
      -- read and stream in chunks of 4Kb (not sure why 4Kb)
      loop
        l_amt:= least( 4096, l_lob_size - l_off + 1);
        dbms_lob.read ( l_lob , l_amt, l_off, l_raw);  -- from l_lob, starting at l_off, length =  l_amt
        htp.prn( utl_raw.cast_to_varchar2( l_raw));
        l_off:= l_off + l_amt;
        if l_off > l_lob_size
        then
          exit;
        end if;
      end loop;
    end if; -- l_kind ='C'
   end; -- download file


   procedure list_files
   ( p_session_id  in   number
   , p_irid        in   number  default null
   , p_ivid        in   number  default null
   ) is

      l_first         boolean;

    procedure icon_bar
    ( p_irid in number
    , p_ivid in number
    ) is
    begin
     odwarprt.init_short_cut_bar;
     cdwp.tableOpen('BORDER="0"');
     cdwp.tableRowOpen;
     if cdwp.exists_mlt
        ( p_id => p_irid
        )
     then
       odwarprt.show_anchor( p_anchor_name => 'files_MLT'||to_char(p_irid), p_gif => 'mlt.gif', p_title =>' Multi Line Text');
     end if;
     cdwp.tableRowClose;
     cdwp.tableClose;
   end; -- icon_bar

   begin -- list_files
     odwactxt.update_context
     ( p_session_id    => p_session_id
     , p_package_name  => PACKAGE_NAME
     , p_procedure_name=> 'list_files'
     , P_PAC_IRID      => p_irid
     , P_PAC_IvID      => p_ivid
     , P_PAC_TYPE_ID   => 4819
     );

     htp.htmlOpen;
     htp.headOpen;
     cdwp.write_about(package_name, revision_label);
     cdwp.include_report_styles;
     if odwactxt.get_one_file = 'Y'
     then
       htp.title
       ( cdwpbase.get_el_name
         ( p_el_id      => odwactxt.get_folder_irid
         , p_el_type_of => 'APP'
         )
       ||' - Files'
       );
     else
       htp.title
       ( cdwpbase.get_el_name
         ( p_el_id      => odwactxt.get_folder_irid
         , p_el_type_of => 'APP'
         )
       ||' - Files - '
       ||cdwpbase.get_el_name
         ( p_el_id => nvl( p_irid, cdwpbase.get_irid( p_ivid))
         , p_el_type_of => 'FILES'
         )
       );
     end if; -- odwactxt.get_one_file = 'Y'
     htp.headClose;
     htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
     if odwactxt.get_one_file = 'Y'
     then
       cdwp.menu_bar
       ( p_level => 2
       , p_el_short_name=> 'FILES'
       , p_el_id        => null
       );
       cdwp.p
       ( 'Files'
       , p_style => 'reptitle'
       );
       htp.nl;
       htp.nl;
       cdwp.tableOpen;
       cdwpbase.get_ivid_list
       ( p_app_id       => odwactxt.get_folder_irid
       , p_ci_view_name => 'CI_PRIMARY_ACCESS_FILES'
       , p_order_by     => 'NAME'
       );
       for i in 1..cdwpbase.g_ividTab.count loop
         open c_file( b_file_ivid => cdwpbase.g_ividTab(i));
         fetch c_file
         into  r_file;
         close c_file;
         cdwp.tableRowOpen;
         cdwp.TableDataHeading(null);
         cdwp.TableDataValue
         ( cdwpbase.local_link
           ( p_bookmark => r_file.file_name
           , p_text     => '{gif}'||r_file.file_name
           , p_el_id    => r_file.file_id
           , p_type_of  => 'FILES'
           )
         );
         cdwp.tableRowClose;
       end loop; -- 1..cdwpbase.g_ividTab.count loop
       cdwp.tableClose;
       htp.nl;
       htp.nl;
     else
        cdwpbase.get_ivid_list
        ( p_ivid  => nvl( p_ivid, cdwpbase.get_ivid( p_irid))
        );
     end if; -- odwactxt.get_one_file = 'Y'
     for i in 1..cdwpbase.g_ividTab.count loop
         open c_file(b_file_ivid => cdwpbase.g_ividTab(i));
         fetch c_file
         into  r_file;
         close c_file;
         odwactxt.update_context
         ( P_PAC_IRID      => r_file.file_irid
         , P_PAC_IvID      => r_file.file_ivid
         , p_new_request   => false
         );

         cdwp.bookmark(r_file.file_name);
         cdwp.menu_bar
         ( p_level => 2
         , p_el_short_name=> 'FILES'
         , p_el_id        => r_file.file_id
         );
         cdwp.heading(3);
         cdwp.p
         ( cdwp.add_images('{'||cdwpbase.get_icon('FILES')||'}')
         ||cdwpbase.get_el_name( p_el_id => r_file.file_irid, p_el_type_of => 'FILES')
         , p_style => 'partitle'
         );
         htp.nl;
         -- begin icon bar
         icon_bar
         ( p_irid => r_file.file_irid
         , p_ivid => r_file.file_ivid
         );
         -- end icon bar
         htp.nl;
         cdwp.tableOpen('BORDER="0"');
         odwavrsn.version_status;
         cdwpbase.app_summary
         ( p_el_id               => r_file.file_id
         );
         cdwp.print_property
         ( p_property_name  => ROB_msg.getMsg(Rob_msg.DSP144_CDWP_FILESIZE, '', '', '', '')
         , p_property_value => to_char(r_file.file_size)||' bytes'
         );
         cdwp.print_property
         ( p_property_name  => ROB_msg.getMsg(Rob_msg.DSP145_CDWP_FILEKIND, '', '', '', '')
         , p_property_value => r_file.kind
         );
         cdwp.print_property
         ( p_property_name  => ROB_msg.getMsg(Rob_msg.DSP146_CDWP_OSPRIV, '', '', '', '')
         , p_property_value => r_file.OS_PRIVILEGES
         );
         cdwp.print_property
         ( p_property_name  => ROB_msg.getMsg(Rob_msg.DSP147_CDWP_OSTIMESTAMP, '', '', '', '')
         , p_property_value => to_char(r_file.OS_timestamp, 'DD-mon-YYYY HH24:MI')
         );
         cdwp.print_property
         ( p_property_name  => ROB_msg.getMsg(Rob_msg.DSP148_CDWP_FILEDESC, '', '', '', '')
         , p_property_value => r_file.short_description
         );
         cdwp.print_property
         ( p_property_name  => ROB_msg.getMsg(Rob_msg.DSP149_CDWP_FILECONTENTS, '', '', '', '')
         , p_property_value => '<A href="cdwp_fil.download_file?p_session_id='||to_char(p_session_id)
                        ||chr(38)||'p_ivid='||to_char(r_file.file_ivid)
                        ||'">'
                        ||ROB_msg.getMsg(Rob_msg.DSP150_CDWP_VIEWDOWNLOADFILE, '', '', '', '')
                        ||' </A>'
         );
         cdwp_txt.display_mlt
         ( p_el_id            => r_file.file_id
          ,p_txt_type         => 'CDIDSC'
          ,p_mlt_prompt_name  => null
          ,p_max_lines        => null
          ,p_el_type          => 'FILES'
         );

         cdwp.tableClose;
         if r_file.kind = 'C' -- text file
         then
           write_piece_of_file
           ( p_length => 10000 --g_page_size
           , p_offset => 1
           , p_blob   => r_file.contents_blob
           );
         end if;  -- r_file.kind = 'C' -- text file
         cdwp.bookmark('files_MLT'||to_char(r_file.file_id)); -- BOOKMARK

         if cdwp.exists_mlt
            ( p_id => r_file.file_id
            , p_not_include1 => 'CDIDSC'
            , p_not_include2 => 'CDWHCL'
            , p_not_include3 => 'LGEXPR'
            )
         then
           hwsp.fontOpen('Book Antiqua');
           htp.bold( cdwp.add_images('{mlt.gif}')
                   ||'Multi Line Text for '
                   ||r_file.file_name
                   );
           hwsp.fontClose;
           htp.nl;
           htp.nl;
           cdwp.tableOpen('BORDER="0"');
           cdwp.tableRowOpen;
           cdwp_txt.display_all_mlt
           ( p_el_id            =>  r_file.file_id
           , p_max_lines        => 3
           , p_do_not_display1  => 'CDIDSC'
           , p_do_not_display2  => 'CDWHCL'
           , p_do_not_display3  => 'LGEXPR'
           , p_el_type          => 'FILES'
           );
           cdwp.tableRowClose;
           cdwp.tableClose;
         end if; -- cdwp.exists_mlt = false
     end loop;  -- i in 1..cdwpbase.g_ividTab.count
     htp.bodyClose;
     htp.htmlClose;
     cdwpbase.reset_context;
   exception
     when others
     then
       close c_file;
   end; -- list_files

-- this procedure will clear the contents of the BLOB column
-- of indicated row in I$SDD_FILES ; the procedure should only be called
-- from the Java Class that uploads files just prior to upload
-- the clear is used because uploading of a smaller file does not remove 
-- the part of the original file beyond the size of the newly uploaded file
procedure clear_file
( p_ivid in varchar2
) is
   cursor c_file
   (      b_file_ivid     in number
   )
   is
   select files.contents_blob
   from   i$sdd_files  files
   where  files.ivid  = b_file_ivid
   for update
   ;
   r_file c_file%rowtype;
   l_lob  blob;
   -- DBMS_LOB.LOBMAXSIZE is now a 20 digit number in 10g database.
   l_amount number(20):= dbms_lob.lobmaxsize;
begin
   jr_context.disable( jr_context.access_rights);
   jr_context.disable( jr_context.check_workarea_context);
   open c_file(b_file_ivid => to_number(p_ivid));
   fetch c_file
   into  r_file;
   close c_file;
   l_lob := r_file.contents_blob;
   dbms_lob.erase(lob_loc => l_lob,amount =>l_amount );
 jr_context.enable( jr_context.access_rights);
 jr_context.enable( jr_context.check_workarea_context);
end; -- clear_file

procedure update_file
( p_ivid   in varchar2
, p_length in number
, p_user   in varchar2
) is
   cursor c_file
   (      b_file_ivid     in number
   )
   is
   select files.contents_blob
   ,      files.file_size
   from   i$sdd_files  files
   where  files.ivid  = b_file_ivid
   for update
   ;
   r_file c_file%rowtype;
   l_lob  blob;
   -- DBMS_LOB.LOBMAXSIZE is now a 20 digit number in 10g database.
   l_amount number(20):= dbms_lob.lobmaxsize;
begin
   jr_context.disable( jr_context.access_rights);
   jr_context.disable( jr_context.check_workarea_context);
   open c_file(b_file_ivid => to_number(p_ivid));
   fetch c_file
   into  r_file;
   close c_file;
   l_lob := r_file.contents_blob;
  update i$sdd_files fil
  set    fil.file_size = p_length
  ,      fil.os_timestamp = sysdate
  ,      fil.crc = null
  ,      fil.kind = jr_file.get_file_registry_lob_kind( fil.name,'win') -- to prevent files from being indicated as compressed while they are not!
  where  ivid = to_number(p_ivid)
  ;
 -- if new file is smaller than the previous one, we should erase the remainder of the lob
 -- trim as well (Solving issue 92)
 if p_length < r_file.file_size
 then
   dbms_lob.trim(lob_loc => l_lob,newlen => p_length );
 end if;
 jr_context.enable( jr_context.access_rights);
 jr_context.enable( jr_context.check_workarea_context);
end; -- update_file

procedure set_file_crc
( p_ivid   in varchar2
, p_crc    in number
) is
begin
  jr_context.disable( jr_context.access_rights);
  jr_context.disable( jr_context.check_workarea_context);
  jr_context.disable( jr_context.NO_CHECKIN_UPDATE);
  update i$sdd_files fil
  set    crc = to_char(p_crc)
  where  ivid = to_number(p_ivid)
  ;
  jr_context.enable( jr_context.access_rights);
  jr_context.enable( jr_context.check_workarea_context);
  jr_context.enable( jr_context.NO_CHECKIN_UPDATE);
end; -- set_file_crc


-- this procedure will touch the indicated file: nothing is changed
-- but the repository audit mechanism is triggered to update the changed_by column value
procedure touch_file
( p_ivid   in number
) is
  pragma autonomous_transaction;
begin
   jr_context.disable( jr_context.access_rights);
   jr_context.disable( jr_context.check_workarea_context);
  jr_context.disable( jr_context.NO_CHECKIN_UPDATE);
  update i$sdd_files fil
  set    fil.name = fil.name
  ,      fil.changed_by = USER
  where  ivid = to_number(p_ivid)
  ;
  commit;
 jr_context.enable( jr_context.access_rights);
 jr_context.enable( jr_context.check_workarea_context);
  jr_context.enable( jr_context.NO_CHECKIN_UPDATE);
end; -- update_file


procedure compare_files
( p_result out number
, p_ivid in varchar2
) is
   cursor c_file
   (      b_file_ivid     in number
   )
   is
   select files.contents_blob
   ,      files.date_changed
   ,      files.file_size
   from   i$sdd_files files
   where  files.ivid  = b_file_ivid
   ;
   r_file c_file%rowtype;
   l_latest_blob blob;
   l_previous_blob blob;
   l_previous_ivid number(38):= jr_version.get_predecessor(i_ivid => to_number(p_ivid));
begin
  open c_file( b_file_ivid => to_number(p_ivid));
  fetch c_file
  into  r_file
  ;
  close c_file;
  l_latest_blob:= r_file.contents_blob;
/*
    htp.p( 'Latest blob changed at  '||to_char(r_file.date_changed, 'dd-mon-yyyy hh24:mmi') );
    htp.p( 'Latest blob size  '||to_char(r_file.file_size) );
    htp.p( 'Previous ivid  '||to_char(l_previous_ivid) );
*/    
  if l_previous_ivid is null
  then
    p_result:= -1;
    raise no_data_found; --p_result :=3;
  else
    r_file.contents_blob:= empty_blob();
    r_file.file_size:= 0;
    open c_file( b_file_ivid => l_previous_ivid);
    fetch c_file
    into  r_file
    ;
  --  htp.p(cdwpbase.ifThenElse(c_file%found, 'FOUND','NOT FOUND'));
    close c_file;
    l_previous_blob:= r_file.contents_blob;
  /*
    htp.p( 'Previous blob changed at  '||to_char(r_file.date_changed, 'dd-mon-yyyy hh24:mmi') );
    htp.p( 'Previous blob size  '||to_char(r_file.file_size) );
    htp.p( 'Piece of previous file '||dbms_lob.substr(lob_loc => l_previous_blob,amount => 20,offset => 1));
    htp.p( '<BR>Piece of latest file '||dbms_lob.substr(lob_loc => l_latest_blob,amount => 20,offset => 1));
    htp.p(dbms_lob.substr(lob_loc => l_previous_blob,amount => 20,offset => 1));
    htp.p('Ivid previous version:'||to_char(jr_version.get_predecessor(i_ivid => to_number(p_ivid))));
    htp.p('Ivid latest version:'||p_ivid);
    htp.p('Size previous version '||to_char(dbms_lob.getlength(lob_loc => l_previous_blob)));
    htp.p('<BR>Size latest version '||to_char(dbms_lob.getlength(lob_loc => l_latest_blob)));
*/    
    p_result:= dbms_lob.compare
               ( lob_1 => l_latest_blob
               , lob_2 => l_previous_blob
--               , amount => dbms_lob.getlength(lob_loc => l_latest_blob)
               );
  --  htp.p('<BR>p_resulkt = '||to_char(p_result));
  end if;      
end; -- compare_files  

function compare_files
( p_ivid in number
) return boolean
is
  l_result number(1);
begin
  compare_files( p_result => l_result, p_ivid => to_char(p_ivid));
  return l_result = 0;
end; -- compare_files  
  


begin
  g_servlets_enabled:= odwapref.get_value('servlets_enabled') = 'Y';
end; -- cdwp_fil
/