create or replace procedure OWT_SHOW_USERS (expand_user_id IN number := -99
                                          , p_user_name    IN VARCHAR2 := 'ORION'
                                          , p_user_status  IN VARCHAR2 := 'ACTIVE'
                                          , p_res_status   IN VARCHAR2 := 'ACTIVE'
                                          , begin_date     IN VARCHAR2 := NULL
                                          , end_date       IN VARCHAR2 := NULL)
IS
--
-- OWT_SHOW_USERS
--
-- Purpose: Display Application Users and their Responsibilities
--
-- MODIFICATION HISTORY
-- Person     Date         Comments
-- ---------  -----------  -------------------------------------------       
-- J.Rimblas  22-APR-2002  Creation


  i           number;
  j           number;
  l_begin_date     date;
  l_end_date       date;
  l_cell_color     VARCHAR2 (20);
  l_selected       VARCHAR2 (1);
  l_user_name      VARCHAR2 (50);
  l_msg            VARCHAR2 (300);
  l_expand         BOOLEAN := FALSE;

  cursor users_cur
  IS
    select u.user_id, u.user_name, u.description
         , u.start_date, u.end_date
      from fnd_user u
     where u.user_name like l_user_name
       and (
             (p_user_status = 'ACTIVE'
          and u.end_date is null)
          or (p_user_status = 'INACTIVE'
          and u.end_date is not null)
          or (p_user_status = 'BOTH')
       )
     order by user_name;


  cursor user_resp_cur (l_user_id_in number)
  IS
    select r.application_id, r.responsibility_id, substr(r.responsibility_name, 1, 80) responsibility
         , r.start_date, r.end_date
      from fnd_responsibility_vl r
         , fnd_user_resp_groups urg
     where urg.responsibility_application_id = r.application_id
       and urg.responsibility_id = r.responsibility_id
       and urg.user_id = l_user_id_in
       and (
             (p_res_status = 'ACTIVE'
          and r.end_date is null)
          or (p_res_status = 'INACTIVE'
          and r.end_date is not null)
          or (p_res_status = 'BOTH')
       )
     order by r.responsibility_name;

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('OWT Apps Users');

   -- 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;

   -- Set the default dates, and date validation
   l_msg := '';
   if begin_date is not null then
      begin
        l_begin_date := to_date(begin_date, 'DD-MON-YYYY');
        exception
        when others then
           l_msg := '<br><font color="red" face="Verdana" size=3>[' || begin_date || '] is an invalid date</font><br>';
           l_begin_date := trunc(sysdate);
      end;
   end if;

   if end_date is not null then
      begin
        l_end_date := to_date(end_date, 'DD-MON-YYYY');
        exception
        when others then
           l_msg := l_msg || '<br><font color="red" face="Verdana" size=3>[' || end_date || '] is an invalid date</font><br>';
           l_end_date := trunc(sysdate);
      end;
   end if;

   -- Valida User Name
   if p_user_name is not null then
     l_user_name := substr(p_user_name, 1, 50);
   else
     l_user_name := '%';
   end if;

   htp.formopen('owt_show_users', 'GET');

   htp.tableopen('border=0');
   htp.tablerowopen;
     htp.print('<td>');

       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
                 || 'Oracle Apps Users');
       htp.fontclose;
       htp.fontopen(0, 'Verdana',2);
       htp.print(owt_htp.c_nbsp || l_msg);
       htp.fontclose;

     htp.print('</td>');

     htp.print('<td>');

       htp.fontopen(0, 'Arial,Verdana',2);
       htp.print(owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || 'User:');
       htp.formtext('p_user_name', '15', '30', l_user_name);
       htp.fontclose;


   htp.print('</td>');

   htp.print('<td>');
       htp.formsubmit;
   htp.print('</td>');

   htp.tablerowclose;
   htp.tablerowopen;
     htp.tabledata(owt_htp.c_nbsp);

     htp.print('<td valign="top">');

       htp.fontopen(0, 'Arial,Verdana',2);
       htp.print(owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || 'Status:');
       htp.br;
       htp.print(owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || 'User:');
       if p_user_status = 'ACTIVE' then l_selected := 'Y'; else l_selected := ''; end if;
       htp.formradio('p_user_status', 'ACTIVE', l_selected);
       htp.print('Active' || owt_htp.c_nbsp);
       if p_user_status = 'INACTIVE' then l_selected := 'Y'; else l_selected := ''; end if;
       htp.formradio('p_user_status', 'INACTIVE', l_selected);
       htp.print('Inactive' || owt_htp.c_nbsp);
       if p_user_status = 'BOTH' then l_selected := 'Y'; else l_selected := ''; end if;
       htp.formradio('p_user_status', 'BOTH', l_selected);
       htp.print('Both');

       htp.br;

       htp.fontopen(0, 'Arial,Verdana',2);
       htp.print(owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || 'Responsibility:');
       if p_res_status = 'ACTIVE' then l_selected := 'Y'; else l_selected := ''; end if;
       htp.formradio('p_res_status', 'ACTIVE', l_selected);
       htp.print('Active' || owt_htp.c_nbsp);
       if p_res_status = 'INACTIVE' then l_selected := 'Y'; else l_selected := ''; end if;
       htp.formradio('p_res_status', 'INACTIVE', l_selected);
       htp.print('Inactive' || owt_htp.c_nbsp);
       if p_res_status = 'BOTH' then l_selected := 'Y'; else l_selected := ''; end if;
       htp.formradio('p_res_status', 'BOTH', l_selected);
       htp.print('Both' || owt_htp.c_nbsp);
       htp.fontclose;

     htp.print('</td>');
     htp.tabledata(owt_htp.c_nbsp);
   htp.tablerowclose;

   htp.tablerowopen;
     htp.tabledata(owt_htp.c_nbsp);

     htp.print('<td valign="top">');

       htp.fontopen(0, 'Arial,Verdana',2);
       htp.print(owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp || owt_htp.c_nbsp
              || 'Date Range:');
       htp.formtext('begin_date', '11', '12', to_char(l_begin_date, 'DD-MON-YYYY'));
       htp.print('-');
       htp.formtext('end_date',   '11', '12', to_char(l_end_date, 'DD-MON-YYYY'));
       htp.fontclose;
       htp.br;
       htp.centeropen;
       htp.fontopen('red', 'Verdana',1);
       htp.print('(Date range not yet implemented)');
       htp.br;
       htp.print('(Wild card percent allowed for user_name)');
       htp.fontclose;
       htp.centerclose;

     htp.print('</td>');
     htp.tabledata(owt_htp.c_nbsp);
   htp.tablerowclose;

   htp.tableclose;

   htp.formclose;



   HTP.tableopen ('width=90%');
   HTP.tablerowopen;
    owt_htp.cell_filler('width=2%');
    owt_htp.table_header('User');
    owt_htp.table_header('Description');
    owt_htp.table_header('Start Date',  'align=right width="12%"');
    owt_htp.table_header('End Date',    'align=right width="12%"');
   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;
         -- the user_name is used in the close and open link, regardles.
         if l_user_name is null then
           l_msg := null;
         else
           if l_expand then
              l_msg := '?' || 'p_user_name=' || htf.escape_url(l_user_name);
           else
              l_msg := chr(38) || 'p_user_name=' || htf.escape_url(l_user_name);
           end if;
         end if;
         if l_expand then
            htp.tabledata( htf.anchor(owt_htp.C_BASE_URL
            || 'owt_show_users'
            || l_msg
            || chr(38) || 'p_user_status=' || htf.escape_url(p_user_status)
            || chr(38) || 'p_res_status=' || htf.escape_url(p_res_status)
            , htf.img('/media/aftreeop.gif', calt=>'Close'), 'current'   -- Display TAG
            ), CATTRIBUTES=>'align=center');
         else
            htp.tabledata( htf.anchor(owt_htp.C_BASE_URL
            || 'owt_show_users?expand_user_id=' || to_char(users_rec.user_id)
            || l_msg
            || chr(38) || 'p_user_status=' || htf.escape_url(p_user_status)
            || chr(38) || 'p_res_status=' || htf.escape_url(p_res_status)
            || '#current',
            htf.img('/media/aftreecl.gif', calt=>'Open')
            ), CATTRIBUTES=>'align=center');
         end if;

        owt_htp.table_data(users_rec.user_name, l_cell_color);
        owt_htp.table_data(users_rec.description, l_cell_color);
        owt_htp.table_data(to_char(users_rec.start_date, 'DD-MON-YYYY'), l_cell_color, 'align=right');
        owt_htp.table_data(to_char(users_rec.end_date,   'DD-MON-YYYY'), l_cell_color, 'align=right');
       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('Responsability');
              owt_htp.sub_table_header('Start Date', 'align=right');
              owt_htp.sub_table_header('End Date','align=right');
             HTP.tablerowclose;

             j:=0;
             for user_resp_rec in user_resp_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_resp_rec.responsibility, l_cell_color);
                   owt_htp.table_data(to_char(user_resp_rec.start_date, 'DD-MON-YYYY'), l_cell_color,'align=right');
                   owt_htp.table_data(to_char(user_resp_rec.end_date, 'DD-MON-YYYY'), 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="3"');
             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 ('4');
   end if;  

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

   owt_htp.create_footer;

   HTP.bodyclose;

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

END OWT_SHOW_USERS; -- Procedure OWT_SHOW_USERS
/
show errors
exit