create or replace procedure SCNX_SHOW_USERS (expand_user_id IN number := -1)
IS
--
-- SCNX_SHOW_USERS
--
-- Purpose: Display Statisticst About Scanner Users
--
-- MODIFICATION HISTORY
-- Person     Date         Comments
-- ---------  -----------  -------------------------------------------       
-- J.Rimblas  17-JUN-2001  Creation

  c_refresh_seconds  CONSTANT  varchar2(3) := '90';

  i           number;
  j           number;
  l_duration  number;
  l_duration_text  VARCHAR2 (100);
  l_end_time       date;
  l_refresh_msg    VARCHAR2 (100);
  l_cell_color     VARCHAR2 (20);
  l_expand         BOOLEAN := FALSE;

  cursor users_cur
      IS
    select l.user_id, u.user_name, l.application
         , l.start_time, l.end_time
         , decode(l.end_time, null, 'Yes', 'No') logged_on
      from scnx_user.scnx_time_log l
         , fnd_user u
     where u.user_id = l.user_id
       and l.start_time = (select max(ll.start_time)
                             from scnx_user.scnx_time_log ll
                            where ll.user_id = l.user_id)
     order by l.user_id;

  cursor user_details_cur (l_user_id_in number)
  IS
    select *
      from scnx_user.scnx_time_log l
     where user_id = l_user_id_in
     order by l.start_time desc;

BEGIN

   htp.htmlOpen;

   -- This validate will make sure the user is logged in
   -- if icx_sec.validateSession then
   if true then
   /*{*/

   htp.headopen;
   htp.Title('SCNX Users Monitor');

   -- This is to try an avoid reload/refresh problems,
   -- However IE 4.01 still does not support this feature.
   htp.print('<META HTTP-EQUIV="Pragma" CONTENT="no-cache">');
 
   htp.headclose;
   htp.bodyOpen(owt_htp.C_DEFAULT_BG_COLOR,
           'link=' || owt_htp.C_DEFAULT_BG_COLOR
                   || ' alink=' || owt_htp.C_DEFAULT_BG_COLOR
                   || ' vlink=' || owt_htp.C_DEFAULT_BG_COLOR);


   htp.br;

   htp.fontopen(0, 'Arial,Verdana',4);
     htp.strong(owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
             || 'Scanner Users');
   htp.fontclose;
   htp.br;

   HTP.tableopen ('width=80%');
   HTP.tablerowopen;
    owt_htp.cell_filler('width=2%');
    owt_htp.table_header('User');
    owt_htp.table_header('Application', 'align=center');
    owt_htp.table_header('Start Time',  'align=right');
    owt_htp.table_header('End Time',    'align=right');
    owt_htp.table_header('Elapsed',     'align=right');
    owt_htp.table_header('Online',      'align=center');
   HTP.tablerowclose;

   i:=0;
   FOR users_rec IN users_cur
   LOOP
       IF MOD (i, 2) = 0 THEN
            l_cell_color := owt_htp.c_list_color1;
       ELSE
            l_cell_color := owt_htp.c_list_color2;
       END IF;

       i := i + 1;

       if users_rec.user_id = expand_user_id then
          l_expand:= true;
       else
          l_expand:= false;
       end if;

       HTP.tablerowopen;
         if l_expand then
            htp.tabledata( htf.anchor(owt_htp.C_BASE_URL
            || 'scnx_show_users',
            htf.img('/media/aftreeop.gif', calt=>'Close'), 'current'   -- Display TAG
            ), CATTRIBUTES=>'align=center');
--          ), CATTRIBUTES=>'bgcolor=' || owt_htp.C_DEFAULT_BG_COLOR || ' align=center');
         else
            htp.tabledata( htf.anchor(owt_htp.C_BASE_URL
            || 'scnx_show_users?expand_user_id=' || to_char(users_rec.user_id)
            || '#current',
            htf.img('/media/aftreecl.gif', calt=>'Open')
            ), CATTRIBUTES=>'align=center');
--          ), CATTRIBUTES=>'bgcolor=' || owt_htp.C_DEFAULT_BG_COLOR || ' align=center');
         end if;

--      owt_htp.cell_filler('');
        owt_htp.table_data(users_rec.user_name, l_cell_color);
        owt_htp.table_data(users_rec.application, l_cell_color, 'align=center');
        owt_htp.table_data(to_char(users_rec.start_time, 'DD-MON-YYYY HH24:MI'), l_cell_color, 'align=right');
        owt_htp.table_data(to_char(users_rec.end_time,   'DD-MON-YYYY HH24:MI'), l_cell_color, 'align=right');
        if users_rec.end_time is null then
           l_end_time := sysdate;
        else
           l_end_time := users_rec.end_time;
        end if;
        l_duration := l_end_time - users_rec.start_time;
        l_duration_text := to_char(trunc(l_duration * 24)) || ' Hrs '
                        || to_char(trunc(((l_duration * 24) - trunc(l_duration * 24)) * 60)) || ' Min';
        
        owt_htp.table_data(l_duration_text, l_cell_color, 'align=right');

        owt_htp.table_data(users_rec.logged_on, l_cell_color, 'align=center');
       HTP.tablerowclose;

       if l_expand then
          HTP.tablerowopen;
          htp.print('<td colspan=6>');

             HTP.tableopen ('width=100%');
             HTP.tablerowopen;
              owt_htp.cell_filler('width=4%');
              owt_htp.cell_filler('width=10%');
              owt_htp.sub_table_header('Application');
              owt_htp.sub_table_header('Start Time', 'align=right');
              owt_htp.sub_table_header('End Time','align=right');
              owt_htp.sub_table_header('Elapsed','align=right');
             HTP.tablerowclose;

             j:=0;
             for user_detail_rec in user_details_cur(users_rec.user_id) loop
                if mod(j,2) = 0 then
                   l_cell_color := owt_htp.C_SUB_LIST_COLOR1;
                else
                   l_cell_color := owt_htp.C_SUB_LIST_COLOR2;
                end if;

                j:=j+1;
                htp.tableRowOpen;
                   owt_htp.cell_filler();
                   owt_htp.cell_filler();

                   owt_htp.table_data(user_detail_rec.application, l_cell_color, 'align=center');
                   owt_htp.table_data(to_char(user_detail_rec.start_time, 'DD-MON-YYYY HH24:MI'), l_cell_color,'align=right');
                   owt_htp.table_data(to_char(user_detail_rec.end_time, 'DD-MON-YYYY HH24:MI'), l_cell_color, 'align=right');
                   if user_detail_rec.end_time is null then
                      l_end_time := user_detail_rec.start_time;
                   else
                      l_end_time := user_detail_rec.end_time;
                   end if;
                   l_duration := l_end_time - user_detail_rec.start_time;
                   l_duration_text := to_char(trunc(l_duration * 24)) || ' Hrs '
                                   || to_char(trunc(((l_duration * 24) - trunc(l_duration * 24)) * 60)) || ' Min';
        
                   owt_htp.table_data(l_duration_text, l_cell_color, 'align=right');
                htp.tableRowClose;

             end loop;

             IF j = 0 THEN
                owt_htp.cell_filler('');
                owt_htp.cell_filler('');
                -- Make sure to use the number of columns displayed
                owt_htp.table_data('<b>No Details Found</b>', owt_htp.C_SUB_LIST_COLOR1 , ' align="center" bgcolor="'
	                || '" colspan="4"');
             END IF;

             htp.tableClose;

          htp.print('</td>');
          HTP.tablerowclose;
       end if;

   end loop;

   if i = 0 then
      owt_htp.cell_filler();
      owt_htp.create_blank_row ('6');
   end if;  

   HTP.tableclose;
  
   htp.br; 
   htp.br; 

   owt_htp.create_footer;

   HTP.bodyclose;

   end if;  -- sessionValidate
   /*}*/
   HTP.htmlclose;

END; -- Procedure SCNX_SHOW_USERS
/
show errors