Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wfsecssb.pls

REM dbdrv: none
REM +=======================================================================+
REM |    Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA     |
REM |                         All rights reserved.                          |
REM +=======================================================================+
REM | FILENAME
REM |   wfsecssb.pls
REM | DESCRIPTION
REM |   PL/SQL body for package:  WFA_SEC
REM |   Web Agent Security implemdetail_entation using web server with 
REM |   single signon feature, which calls the logout procedure from
REM |   this package instead of the one in wfa_html.
REM |
REM | HISTORY
REM |   06 May 2002  kma Creation, based on wfsecwsb.pls 26.8
REM +=======================================================================+
set arraysize 5
set scan off
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

create or replace package body WFA_SEC as
/* $Header: wfsecssb.pls 26.15 2004/07/23 08:29:45 vshanmug ship $ */

--
-- Error (PRIVATE)
--   Print a page with an error message.
--   Errors are retrieved from these sources in order:
--     1. wf_core errors
--     2. Oracle errors
--     3. Unspecified INTERNAL error
--
procedure Error
as

begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title(wf_core.translate('ERROR'));
    htp.headClose;

begin
      wfa_sec.Header(background_only=>TRUE);
    exception
      when others then
        htp.bodyOpen;
    end;

htp.header(nsize=>1, cheader=>wf_core.translate('ERROR'));

htp.bold (wf_core.translate('WFA_NO_LOGIN'));

htp.br;
    htp.br;

wfa_sec.Footer;

htp.htmlClose;

end Error;

--
-- CreateSession
-- 
procedure CreateSession(
    c_user_name     in varchar2,
    c_user_password in varchar2)
is
    colon number;
    uname varchar2(30);
begin
    begin
      colon := instr(c_user_name, ':');
      if (colon = 0) then
        select name into uname 
        from wf_users 
        where name = c_user_name;
      else
        select name into uname 
        from wf_users 
        where orig_system = substr(c_user_name, 1, colon-1)
        and orig_system_id = substr(c_user_name, colon+1) 
        and name = c_user_name;
      end if;
    exception
        when no_data_found then
            wf_core.token('USER', c_user_name);
            wf_core.raise('WFSEC_USER_PASSWORD');
    end;

-- Prepare a cookie for this session 
    owa_util.mime_header('text/html', FALSE);
    owa_cookie.remove('MetaMolari', 'LOGIN', '/');
    owa_cookie.send('WF_SESSION', c_user_name, '', '/');
    owa_util.http_header_close;

exception
    when others then
        wf_core.context('Wfa_Sec', 'CreateSession', c_user_name);
        raise;
end CreateSession;

--
-- GetSession
--
procedure GetSession(user_name out varchar2)
is
  c_territory     VARCHAR2(80);
  c_language      VARCHAR2(80);
  c_date_format   VARCHAR2(40);
  uname           varchar2(320);
  l_gen_redirect_url VARCHAR2(32000);
  l_hostname      VARCHAR2(255);
  l_port          VARCHAR2(10);
  l_base_url  VARCHAR2(4000);
  cookie          owa_cookie.cookie;
  cookie_user     varchar2(320);
begin
  --
  -- Get Remote user 
  --
  uname  := upper(owa_util.get_cgi_env('REMOTE_USER'));
  --
  -- if no remote user exists redirect to SSO login page
  --
  if ( uname is null ) then
        --
        wf_core.raise('WFSEC_NO_SESSION');
        --
  end if;
  -- Bug 2602161 - add check to make sure user logs out will not 
  --   get the previous login session
  begin
    cookie := owa_cookie.get('WF_SESSION');
    cookie_user := cookie.vals(1);
  exception
    when others then
      -- That means it is first time login
      cookie_user := uname;
  end;
  if (cookie_user <> uname) then
    wf_core.raise('WFSEC_NO_SESSION');
  end if;

--<rwunderl:2935910>
  if NOT (WF_DIRECTORY.UserActive(uname)) then
    WF_CORE.Token('NAME', uname);
    WF_CORE.Raise('WFSEC_NO_USER');
  end if;
--</rwunderl:2935910>

user_name := uname;

-- Get the language preference
  c_language := NVL(wf_pref.get_pref (user_name, 'LANGUAGE'), 'AMERICAN');

-- Get the terriory preference
  c_territory := NVL(wf_pref.get_pref (user_name, 'TERRITORY'), 'AMERICA');

-- get the date format preference
  c_date_format := NVL(wf_pref.get_pref (user_name, 'DATEFORMAT'), 'DD-MON-RRRR');

c_language    := ''''||c_language||'''';
  c_territory   := ''''||c_territory||'''';
  c_date_format := ''''||c_date_format||'''';

dbms_session.set_nls('NLS_LANGUAGE'   , c_language);
  dbms_session.set_nls('NLS_TERRITORY'  , c_territory);
  dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format);

exception
    when others then
        wf_core.context('Wfa_Sec', 'GetSession');
        raise;
end GetSession;

--
-- Header
--   Print an html page header 
-- IN
--   background_only - Only set background with no other header
--   disp_find - When defined, Find button is displayed, and the value
--               is the URL the Find button is pointting to.
--
procedure Header(background_only in boolean default FALSE,
                 disp_find in varchar2 default null,
                 page_title in varchar2 default null,
                 inc_lov_applet  in boolean  default TRUE,
                 pseudo_login    in boolean  default FALSE)

-- pseudo_login is used in wfsecicb.sql only.
is
begin

htp.p('<body bgcolor="#CCCCCC">');

if (not background_only) then
       
     /*
     ** Create main table for toolbar and icon
     */
     htp.p('<table width=100% Cellpadding=0 Cellspacing=0 border=0 summary="">');

htp.p('<tr>');

/*
     ** Put some space on the side
     */
     htp.p('<td width=10 id=""></td>');

htp.p('<td id="">');

/*
     ** inner table to define toolbar
     */
     htp.p('<table Cellpadding=0 Cellspacing=0 Border=0 summary="">');

/*
     ** Left rounded icon for toolbar
     */ 
     htp.p('<td rowspan=3 summary=""><img src='||wfa_html.image_loc||'FNDGTBL.gif alt=""></td>');

/*
     ** White line on top of toolbar
     */
     htp.p('<td bgcolor=#ffffff height=1 colspan=3 id=""><img src='||wfa_html.image_loc||'FNDDBPXW.gif alt=""></td>');

/*
     ** Right rounded icon for toolbar
     */
     htp.p('<td rowspan=3 id=""><img src='||wfa_html.image_loc||'FNDGTBR.gif alt=""></td>');

/*
     ** End the table row for the icons that surround the real toolbar
     */
     htp.p('</tr>');

/*
     ** Start the table for the real controls
     */
     htp.p('<tr>');
 
     /*
     ** Always create the home icon
     */
     htp.p('<td bgcolor=#cccccc nowrap height=30 align=middle id="">');

htp.p('<a href="'||wfa_html.base_url||'/wfa_html.home"'||'" TARGET="_top" onMouseOver="window.status='||''''||
              wfa_html.replace_onMouseOver_quotes(wf_core.translate('WFA_HOMETITLE'))||''''||';return true">'||
              '<img src='||wfa_html.image_loc||'FNDHOME.gif border=no align=middle alt=""></a>');

htp.p('<img src='||wfa_html.image_loc||'FNDIWDVD.gif align=absmiddle alt="">');

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

/*
     ** Create the page title.  If no page title was passed then use 
     ** the default product title icon - Oracle Workflow
     */
     htp.p('<td bgcolor=#cccccc nowrap height=30 align=middle id="">');
     
     if (page_title IS NULL) then

htp.p('<B>&nbsp;'||wf_core.translate('WF_WORKFLOW_TITLE')||'&nbsp;</B>');

else

htp.p('<B>&nbsp;'||page_title||'&nbsp;</B>');
 
     end if;

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

/*
     ** Create the dividing line
     */
     htp.p('<td bgcolor=#cccccc nowrap height=30 align=middle id="">');
     htp.p('<img src='||wfa_html.image_loc||'FNDIWDVD.gif border=no align=absmiddle alt="">');

/*
     ** Optionally create the find icon
     */
     if (disp_find is not null) then

htp.p('<a href="'||disp_find||'" TARGET="_top" onMouseOver="window.status='||''''||
              wfa_html.replace_onMouseOver_quotes(wf_core.translate('QUERY_DETAIL'))||''''||';return true">'||
              '<img src='||wfa_html.image_loc||'fndfind.gif border=no align=absmiddle alt="' || WF_CORE.Translate('QUERY_DETAIL') || '"></a>');

htp.p('<img src='||wfa_html.image_loc||'FNDIWDVD.gif border=no align=absmiddle alt="">');

end if;
    
    /*
    ** if running under webdb create logout icon, which points to the private
    ** logout pl/sql packages instead of the one in wfa_html
    */

htp.p('');
/*
    if ((upper(substr(owa_util.get_cgi_env('SERVER_SOFTWARE'),1,12)) = 'ORACLE WEBDB') OR (upper(substr(owa_util.get_cgi_env('SERVER_SOFTWARE'),1,6)) = 'APACHE') OR (instr(upper(owa_util.get_cgi_env('SERVER_SOFTWARE')), 'ORACLE HTTP SERVER')>0)) then */
    if ((instr(upper(owa_util.get_cgi_env('SERVER_SOFTWARE')),'ORACLE') > 0) OR
        (upper(substr(owa_util.get_cgi_env('SERVER_SOFTWARE'),1,6)) = 'APACHE')
       ) then
      htp.p('<a href="'||wfa_html.base_url||'/wfa_sec.logout'||'" TARGET="_top" onMouseOver="window.status='||''''||
              wfa_html.replace_onMouseOver_quotes(wf_core.translate('LOGOUT'))||''''||';return true">'||
              '<img src='||wfa_html.image_loc||'FNDEXIT.gif border=no align=absmiddle alt="' || WF_CORE.Translate('LOGOUT') || '"></a>');
       htp.p('<img src='||wfa_html.image_loc||'FNDIWDVD.gif border=no align=absmiddle alt="">');
    end if;

/*
     ** Create the help icon
     */
     htp.p('<a href="javascript:help_window()" onMouseOver="window.status='||
           ''''||wfa_html.replace_onMouseOver_quotes(wf_core.translate('WFMON_HELP_DETAILS'))||''''||
           ';return true"><img src='||wfa_html.image_loc||'FNDIHELP.gif border=no align=absmiddle alt="' || WF_CORE.Translate('WFMON_HELP_DETAILS') ||
'"></a></td>');
     htp.p('</tr>');

/*
     ** Create the black border under the toolbar and close the icon table
     */
     htp.p('<tr>');
     htp.p('<td bgcolor=#666666 height=1 colspan=3 id=""><img src='||wfa_html.image_loc||'FNDDBPXB.gif alt=""></td></tr></table>');

/*
     ** Close the toolbar table data
     */
     htp.p('</td>');

/*
     ** Create the logo and close the toolbar and logo table
     */
     htp.p('<td rowspan=5 width=100% align=right id=""><img src='||wfa_html.image_loc||'WFLOGO.gif alt=""></td></tr></table>');

/*  
     ** This was a test to see how preloading the applet to 
     ** improve performance would work out.  
     */
/*
     IF (inc_lov_applet = TRUE AND 
        wfa_html.base_url(get_from_resources=>TRUE) != 
          'http://www.your_company.com/wf/owa') THEN

htp.p('<APPLET code="oracle.apps.fnd.wf.LovApplet.class"'||
               ' codebase="/OA_JAVA"'||
               ' archive="/OA_JAVA/oracle/apps/fnd/wf/jar/wflov.jar"'||
               ' width=1 height=1 name=LovApplet>'||
               '<PARAM name=QUERY_PARAMS value=>'||
               '<PARAM name=QUERY_URL value='||
                  wfa_html.base_url(get_from_resources=>TRUE)||
                  '/wfa_html.user_lov>'||
               ' <PARAM name=LONGLIST value=Y>'||
               '<PARAM name=INITIAL_FIND value="">'||
               '<PARAM name=LANGCODESET value=WE8ISO8859P1>'||
               '<PARAM name=title value=LOV></APPLET>');

END IF;
*/
  end if;

exception
  when others then
    wf_core.context('Wfa_Sec', 'Header');
    raise;
end Header;

--
-- Footer
--   Print an html page footer
--
procedure Footer
is
begin
  htp.bodyClose;
exception
  when others then
    wf_core.context('Wfa_Sec', 'Footer');
    raise;
end Footer;

--
-- DetailURL
--   Produce URL for notification detail and response page.
-- IN
--   nid - notification id
-- RETURNS
--   URL of detail and response page for notification.
--
function DetailURL(nid in number) return varchar2
is
begin
  return('wfa_html.detail?nid='||to_char(nid));
exception
  when others then
    Wf_Core.Context('Wfa_Sec', 'DetailURL', to_char(nid));
    raise;
end DetailURL;
--
-- PseudoSession - create ICX psuedo session for the client
--   Creates a temp ICX session for the current user coming into ICX
--   from an email notification with a link to the applications.
--   Session information is typically stored on the web client as an
--   http cookie.  This only applies to ICX so only wfsecicb will
--   have an actual implementation for this function.  The others
--   do nothing except close the http header.
--
--   Added setting of user preference here, so that a French user
--   when viewing a detached notification will still view this in
--   French instead of English.
procedure PseudoSession(IncludeHeader in BOOLEAN default TRUE,
                        user_name     in varchar2 default null)
-- We are only using Include Header Parameter in ICX.
is
  c_territory     VARCHAR2(80);
  c_language      VARCHAR2(80);
  c_date_format   VARCHAR2(40);
  role_info_tbl   wf_directory.wf_local_roles_tbl_type;
begin
  if (IncludeHeader) then
    owa_util.http_header_close;
  end if;

if (user_name is not null) then
    Wf_Directory.GetRoleInfo2(user_name,role_info_tbl);

-- Get the language preference
    c_language := ''''||role_info_tbl(1).language||'''';

-- Get the terriory preference
    c_territory := ''''||role_info_tbl(1).territory||'''';

-- get the date format preference
    c_date_format := ''''||NVL(wf_pref.get_pref(user_name,'DATEFORMAT'),
                     'DD-MON-RRRR')||'''';

dbms_session.set_nls('NLS_LANGUAGE'   , c_language);
    dbms_session.set_nls('NLS_TERRITORY'  , c_territory);
    dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format);
  end if;

exception
  when others then
    Wf_Core.Context('Wfa_Sec', 'PseudoSession');
    raise;
end PseudoSession;

--
-- Create_Help_Syntax
--   Create the javascript necessary to launch the help function
--   Since this is only required for the apps install case
--   I have covered this function with a wfa_sec function.
--   The other wfsec cases are just a stub.
--
procedure Create_Help_Syntax (
p_target in varchar2 default null,
p_language_code in varchar2 default null) IS

begin

null;

exception
  when others then
    Wf_Core.Context('Wfa_Sec', 'Create_Help_Syntax');
    raise;
end Create_Help_Syntax;

--
-- get_role_info
--   Gets role info for the user sources that we know about rather
--   than using the ugly expensive wf_roles view
--
procedure get_role_info (
  role in varchar2,
  name out varchar2,
  display_name out varchar2,
  description out varchar2,
  email_address out varchar2,
  notification_preference out varchar2,
  language out varchar2,
  territory out varchar2,
  orig_system  out varchar2,
  orig_system_id out number) IS
begin
   null;    --Anyway get_role_info2 returns null 
            --so why call the procedure unnecessarily
end  get_role_info ;

--
-- get_role_info2
--   Gets role info2 for the user sources that we know about rather
--   than using the ugly expensive wf_roles view
--

procedure get_role_info2(
  role in varchar2,
  name out NOCOPY varchar2,
  display_name out NOCOPY varchar2,
  description out NOCOPY varchar2,
  email_address out NOCOPY varchar2,
  notification_preference out NOCOPY varchar2,
  language out NOCOPY varchar2,
  territory out NOCOPY varchar2,
  orig_system  out NOCOPY varchar2,
  orig_system_id out NOCOPY number,
  FAX out NOCOPY VARCHAR2,
  STATUS out NOCOPY VARCHAR2,
  EXPIRATION_DATE out NOCOPY DATE
 ) IS

begin
  null;
end  get_role_info2 ;

--
-- ResetCookie
--
--  IN: Name of the cookie to be reset to -1.
--
 
procedure ResetCookie(cookieName in varchar2)
is
BEGIN
 
owa_cookie.send(name=>cookieName, value=>'-1', expires=>'',
               path=>'/');
 
end ResetCookie;                                                                
--
-- GET_PROFILE_VALUE (PRIVATE)
--
function Get_Profile_Value(name varchar2,
                           user_name varchar2)
return varchar2
is
begin
  return null;
exception
  when OTHERS then
    Wf_Core.Context('Wfa_Sec', 'Get_Profile_Value', name, user_name);
    raise;
end Get_Profile_Value;

-- Local_Chr
--   Return specified character in current codeset
-- IN
--   ascii_chr - chr number in US7ASCII
function Local_Chr(
  ascii_chr in number)
return varchar2
is
begin
  if (ascii_chr = 10) then

if (WF_CORE.LOCAL_CS_NL is null) then
          WF_CORE.LOCAL_CS_NL := convert(chr(ascii_chr),
            substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') + 1), 
           'US7ASCII');
      end if;

return WF_CORE.LOCAL_CS_NL;

elsif (ascii_chr = 9) then

if (WF_CORE.LOCAL_CS_TB is null) then
          WF_CORE.LOCAL_CS_TB := convert(chr(ascii_chr),
            substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') + 1), 
           'US7ASCII');
      end if;

return WF_CORE.LOCAL_CS_TB;

else

return(convert(chr(ascii_chr),
             substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') + 1), 
            'US7ASCII'));

end if;

end Local_Chr;

--
-- DirectLogin - Return proper function name for DirectLogin  --Bug: 1566390
-- Also needed to port bug 1838410
--
function DirectLogin (nid in NUMBER) return VARCHAR2
IS
x_mode varchar2(30);
BEGIN

return (NULL);

exception
  when others then
    Wf_Core.Context('Wfa_Sec', 'DirectLogin', to_char(nid));
    raise;
end DirectLogin;

--
-- GetFWKUserName
--   Return current Framework user name
--
function GetFWKUserName
return varchar2
is
begin
  return 'NotUsed';

exception  
  when others then
    Wf_Core.Context('Wfa_Sec', 'GetFWKUserName');
    raise;
end GetFWKUserName;

--
-- Logout 
-- This is a replacement procedure for wfa_html.logout when 
-- single signon feature is activated
--
procedure Logout
is
  l_username      VARCHAR2(30);
  l_protocol      VARCHAR2(10);
  l_hostname      VARCHAR2(255);
  l_port          VARCHAR2(10);
  l_redirect_url  VARCHAR2(4000);
begin
    -- Set redirect URL to Single signon logoff screen
    l_protocol :=  owa_util.get_cgi_env('HTTPS');
      if l_protocol is not null and upper(l_protocol) = 'ON' then
        l_protocol := 'https';
      else
        l_protocol := 'http';
      end if;
    l_hostname := owa_util.get_cgi_env('SERVER_NAME');
    l_port:= owa_util.get_cgi_env('SERVER_PORT');
    l_redirect_url := l_protocol || '://' || l_hostname || ':' || 
      l_port || '/osso_logout';

wfa_sec.GetSession(l_username);
    owa_util.mime_header('text/html', FALSE);
    owa_cookie.send('WDB_GATEWAY_LOGOUT','YES', '', '/');
    owa_util.redirect_url(l_redirect_url);
    owa_util.http_header_close;
    owa_cookie.remove('WF_SESSION', l_username, '/');

exception
  when OTHERS then
    Wf_Core.Context('Wfa_Sec', 'Logout', l_redirect_url, l_username);
    raise;
end Logout;

--
-- DS_Count_Local_Role (PRIVATE)
--   Returns count of a role in local directory service table
-- IN
--   role_name - role to be counted
-- RETURN
--   count of provided role in local directory service table
--
function DS_Count_Local_Role(role_name in varchar2)
return number
is
  cnt number;
begin
  select count(1) into cnt
    from WF_LOCAL_ROLES
   where NAME = role_name
     and ORIG_SYSTEM = 'WF_LOCAL_ROLES'
     and ORIG_SYSTEM_ID = 0;

return(cnt);

exception
 when others then
   WF_CORE.Context('WFA_SEC', 'DS_Count_Local_Role', role_name);
   raise;
end DS_Count_Local_Role;

--
-- DS_Update_Local_Role (PRIVATE)
--   Update old name user/role in local directory service tables with new name
-- IN
--   OldName - original name to be replaced
--   NewName - new name to replace
--
procedure DS_Update_Local_Role(
  OldName in varchar2,
  NewName in varchar2
)
is
begin
   update WF_LOCAL_ROLES
   set    NAME = NewName
   where  NAME = OldName
   and    USER_FLAG = 'Y'
     and  ORIG_SYSTEM = 'WF_LOCAL_USERS'
     and  ORIG_SYSTEM_ID = 0;

-- Update local user roles
   update WF_LOCAL_USER_ROLES
   set    USER_NAME = NewName,
          ROLE_NAME = NewName
   where  USER_NAME = OldName
     and  USER_ORIG_SYSTEM = 'WF_LOCAL_USERS'
     and  USER_ORIG_SYSTEM_ID = 0
     and  ROLE_NAME = OldName;

update WF_LOCAL_USER_ROLES
   set    USER_NAME = NewName
   where  USER_NAME = OldName
     and  USER_ORIG_SYSTEM = 'WF_LOCAL_USERS'
     and  USER_ORIG_SYSTEM_ID = 0;

exception
 when others then
   WF_CORE.Context('WFA_SEC', 'DS_Update_Local_Role', OldName, NewName);
   raise;
end DS_Update_Local_Role;

function GetUser
return varchar2
is
username varchar2(320);
begin
 wfa_sec.GetSession(username);
 return username;
exception
  when others then
  --Incase of exception just return null
  return '';
end;

--
-- user_id
--   Return current user id, in apps, wrapper to  FND_GLOBAL.user_id
--   In standalone, returns -1.
function user_id return number is

begin
  return -1;
end;

--
-- login_id
--   Return current login id, in apps, wrapper to  FND_GLOBAL.login_id
--   In standalone, returns -1.
function login_id return number is
begin
  return -1;
end;

--
-- security_group_id
--   Return current security_group_id, in apps, wrapper to  
--   FND_GLOBAL.security_group_id  In standalone, returns -1.
function security_group_id return number is
begin
  return -1;
end;

--
-- CheckSession
--   Check the cached ICX session id against the current session id to determine
--   if the session has been changed. This function caches the current session id
--   after the check. (STANDALONE IMPLEMENTATION)
-- RETURN
--   boolean - True if session matches, else False
function CheckSession return boolean
is
begin
  return true;
end CheckSession;

end WFA_SEC;
/
-- show errors package body WFA_SEC
commit;
exit

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de