REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \ REM dbdrv: checkfile:~PROD:~PATH:~FILE REM +=======================================================================+ REM | Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA | REM | All rights reserved. | REM +=======================================================================+ REM | FILENAME REM | wfsecicb.sql REM | DESCRIPTION REM | PL/SQL body for package: WFA_SEC REM | Web Agent Security implementation using Internet Commerce REM | MODIFICATION LOG: REM | 01/2002 JWSMITH BUG 2001012 - Increased wf_session, user, l_user_name, REM | roleid to varchar2(320) REM +=======================================================================+ SET DEFINE OFF SET VERIFY OFF WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; create or replace package body WFA_SEC as /* $Header: wfsecicb.pls 26.35 2004/07/23 15:04:51 vshanmug ship $ */ -- Private global to hold access cookie wf_session varchar2(320) := ''; -- The default behavior is to use ICX profile options instead -- But Exchange has a requirement of using fnd_preferences wf_use_fnd_preferences boolean := null; -- ICX Session Id cache g_session_id number := -1; -- -- Use_Fnd_Preferences (PRIVATE) -- Find out whether we should use FND Preference or not -- Return -- True if Token Resource WF_PREFERENCE is set to FND -- False otherwise or if the above token is not set -- function Use_Fnd_Preferences return boolean is begin if (wf_use_fnd_preferences is null) then if (wf_core.translate('WF_PREFERENCE') = 'FND') then wf_use_fnd_preferences := true; else wf_use_fnd_preferences := false; end if; end if; return wf_use_fnd_preferences; end Use_Fnd_Preferences; -- -- CreateSession -- procedure CreateSession( c_user_name in varchar2, c_user_password in varchar2) is sid number; user varchar2(320); pwd varchar2(255); res varchar2(255); begin user := c_user_name; pwd := c_user_password; sid := 0; -- Validate the user with icx begin res := ICX_SEC.ValidatePassword(user, pwd, sid); exception when others then wf_core.token('USER', c_user_name); wf_core.token('SQLCODE', SQLCODE); wf_core.token('SQLERRM', SQLERRM); wf_core.raise('WFSEC_CREATE_SESSION'); end; if (res <> '0') then wf_core.token('USER', c_user_name); wf_core.raise('WFSEC_USER_PASSWORD'); end if; -- Set the private access global wf_session := c_user_name; exception when others then wf_core.context('Wfa_Sec', 'CreateSession', c_user_name); raise; end CreateSession; -- -- GetSession -- procedure GetSession(user_name out NOCOPY varchar2) is l_user_name varchar2(320); -- used as out parameters cannot be read!! res boolean; begin -- First check if local acccess global has been set if (wfa_sec.wf_session is not null) then l_user_name := wfa_sec.wf_session; else -- Otherwise check the ic cookie for a session begin if (wfa_sec.validate_only = TRUE) then /* GK: ** Do not update the icx_sessions table. If you get a long ** running worklist or any other workflow api, you'll get a ** lock on the sessions table that will lead to db enqueue contention ** across the db. */ res := ICX_SEC.ValidateSession( c_validate_only => 'Y', c_update => FALSE); else res := ICX_SEC.ValidateSession(c_update => FALSE); end if; exception when others then wf_core.token('SQLCODE', SQLCODE); wf_core.token('SQLERRM', SQLERRM); wf_core.raise('WFSEC_GET_SESSION'); end; if (res = FALSE ) then wf_core.raise('WFSEC_NO_SESSION'); end if; l_user_name := ICX_SEC.GetID(99); end if; user_name := l_user_name; 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, disp_find in varchar2, page_title in varchar2, inc_lov_applet in boolean, pseudo_login in boolean) is begin if (background_only) then htp.p(''); else if (disp_find is not null) then htp.p (''); if not (pseudo_login) then icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_help=>'Y', p_disp_find=>Header.disp_find); else icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_mainmenu=>'N', p_disp_menu=>'N'); end if; htp.p('
'); else htp.p (''); if not (pseudo_login) then icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_help=>'Y'); else icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_mainmenu=>'N', p_disp_menu=>'N'); end if; htp.p('
'); 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 icx_admin_sig.footer; 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. -- -- 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, user_name in varchar2) is l_session_id NUMBER := 0; l_result VARCHAR2(5) := '0'; c_territory VARCHAR2(80); c_language VARCHAR2(80); c_date_format VARCHAR2(40); l_user_id NUMBER := to_number(null); role_info_tbl wf_directory.wf_local_roles_tbl_type; begin l_result := ICX_SEC.PseudoSession (l_session_id, IncludeHeader); if (user_name is not null) then Wf_Directory.GetRoleInfo2(user_name,role_info_tbl); -- do not brother to find out the user id if we use fnd_preferences if (Use_FND_Preferences) then l_user_id := to_number(null); else begin -- user_name should be unique, but use rownum just in case select USER_ID into l_user_id from FND_USER where USER_NAME = PseudoSession.user_name and rownum < 2; exception when NO_DATA_FOUND then l_user_id := to_number(null); end; end if; -- Get the language preference c_language := ''''||role_info_tbl(1).language||''''; -- Get the terriory preference c_territory := ''''||role_info_tbl(1).territory||''''; if (l_user_id is not null) then -- get the date format preference c_date_format := ''''||NVL(fnd_profile.value_specific( 'ICX_DATE_FORMAT_MASK',l_user_id, null, null),'DD-MON-RRRR')||''''; else c_date_format := ''''||NVL(wf_pref.get_pref(user_name,'DATEFORMAT'), 'DD-MON-RRRR')||''''; end if; 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, p_language_code in varchar2) IS begin htp.p(''); htp.p(''); 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 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 ) IS l_status varchar2(8); l_fax varchar2(100); l_exp_date date; begin wfa_sec.get_role_info2(role , name ,display_name, description ,email_address,notification_preference,language , territory,orig_system,orig_system_id ,l_fax , l_status,l_exp_date); exception when others then Wf_Core.Context('Wfa_Sec', 'Get_Role_Info', role); raise; 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 prefix VARCHAR2(80); roleid VARCHAR2(320); nlsLang NUMBER; nlsTerr NUMBER; l_langstatus PLS_INTEGER; l_terrstatus PLS_INTEGER; fndUserID NUMBER; begin prefix := SUBSTRB(role, 1, INSTRB(role, ':') - 1); roleid := SUBSTRB(role, INSTRB(role, ':') + 1); if (prefix = 'POS') then select NAME, DISPLAY_NAME, DESCRIPTION, EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, ORIG_SYSTEM, ORIG_SYSTEM_ID, FAX, STATUS, EXPIRATION_DATE into name, display_name, description, email_address, notification_preference, language, territory, orig_system, orig_system_id, FAX, STATUS, EXPIRATION_DATE from WF_LOCAL_ROLES PARTITION (POS) where ORIG_SYSTEM = prefix and ORIG_SYSTEM_ID = to_number(roleid); elsif (prefix = 'ENG_LIST') then SELECT NAME, DISPLAY_NAME, DESCRIPTION, EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, ORIG_SYSTEM, ORIG_SYSTEM_ID, FAX, STATUS, EXPIRATION_DATE into name, display_name, description, email_address, notification_preference, language, territory, orig_system, orig_system_id, FAX, STATUS, EXPIRATION_DATE from WF_LOCAL_ROLES PARTITION (ENG_LIST) where ORIG_SYSTEM = prefix and ORIG_SYSTEM_ID = to_number(roleid); elsif ((SUBSTRB(prefix, 1, 8) = 'FND_RESP') and ((length(prefix) = 8) or --In case we just get 'FND_RESP' (substr(prefix, 9, 9) between '0' and '9'))) then SELECT NAME, DISPLAY_NAME, DESCRIPTION, EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, ORIG_SYSTEM, ORIG_SYSTEM_ID, FAX, STATUS, EXPIRATION_DATE into name, display_name, description, email_address, notification_preference, language, territory, orig_system, orig_system_id, FAX, STATUS, EXPIRATION_DATE from WF_LOCAL_ROLES PARTITION (FND_RESP) where ORIG_SYSTEM = prefix and ORIG_SYSTEM_ID = to_number(roleid); elsif (prefix = 'AMV_CHN') then select NAME, DISPLAY_NAME, DESCRIPTION, EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, ORIG_SYSTEM, ORIG_SYSTEM_ID, FAX, STATUS, EXPIRATION_DATE into name, display_name, description, email_address, notification_preference, language, territory, orig_system, orig_system_id, FAX, STATUS, EXPIRATION_DATE from WF_LOCAL_ROLES PARTITION (AMV_CHN) where ORIG_SYSTEM = prefix and ORIG_SYSTEM_ID = to_number(roleid); elsif (prefix = 'HZ_PARTY') then select NAME, DISPLAY_NAME, DESCRIPTION, EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, ORIG_SYSTEM, ORIG_SYSTEM_ID, FAX, STATUS, EXPIRATION_DATE into name, display_name, description, email_address, notification_preference, language, territory, orig_system, orig_system_id, FAX, STATUS, EXPIRATION_DATE from WF_LOCAL_ROLES PARTITION (HZ_PARTY) where ORIG_SYSTEM = prefix and ORIG_SYSTEM_ID = to_number(roleid); else --Bug 2728955 --Changed the elseif to else this is for --composite names ( eg : ABC:123). This will not fall --in any of the above conditions but has prefix non-null also --Tuned the query to use the partition_id for the prefix null --ORIG_SYSTEM . select NAME, DISPLAY_NAME, DESCRIPTION, EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, ORIG_SYSTEM, ORIG_SYSTEM_ID, FAX, STATUS, EXPIRATION_DATE into name, display_name, description, email_address, notification_preference, language, territory, orig_system, orig_system_id, FAX, STATUS, EXPIRATION_DATE from WF_LOCAL_ROLES where NAME = role and partition_id in (1, 0, 5, 10, 13) and rownum < 2; /* Bug 2728955 */ end if; -- if (orig_system in ('FND_USR', 'PER')) then if (Use_FND_Preferences) then notification_preference := nvl(WF_PREF.get_pref(name,'MAILTYPE'), 'MAILHTML'); language := WF_PREF.get_pref(name, 'LANGUAGE'); territory := WF_PREF.get_pref(name, 'TERRITORY'); else if (orig_system = 'PER') then SELECT USER_ID INTO fndUserID FROM FND_USER WHERE USER_NAME = name; else fndUserID := orig_system_ID; end if; notification_preference := nvl(WF_PREF.get_pref(name,'MAILTYPE'), 'MAILHTML'); language := fnd_profile.value_specific('ICX_LANGUAGE',fndUserID, null, null); territory := fnd_profile.value_specific('ICX_TERRITORY',fndUserID, null, null); end if; end if; --Need to make sure the nls preferences were not null. if ((language is NULL) or (territory is NULL)) then WF_CACHE.GetNLSParameter('BASELANGUAGE', l_langstatus, nlsLang); --Bug 3188230 --Get the base territory aswell WF_CACHE.GetNLSParameter('BASETERRITORY', l_terrstatus, nlsTerr); if ((l_langstatus <> WF_CACHE.task_SUCCESS) OR (l_terrstatus <> WF_CACHE.task_SUCCESS)) then --Where there is language there is territory, so we will go after both. WF_CACHE.NLSParameters(nlsLang).PARAMETER := 'BASELANGUAGE'; WF_CACHE.NLSParameters(nlsTerr).PARAMETER := 'BASETERRITORY'; SELECT NLS_LANGUAGE, NLS_TERRITORY INTO WF_CACHE.NLSParameters(nlsLang).VALUE, WF_CACHE.NLSParameters(nlsTerr).VALUE FROM FND_LANGUAGES WHERE INSTALLED_FLAG = 'B'; end if; language := WF_CACHE.NLSParameters(nlsLang).VALUE; territory := WF_CACHE.NLSParameters(nlsTerr).VALUE; end if; exception when no_data_found then name := ''; display_name := ''; description := ''; notification_preference := ''; language := ''; territory := ''; email_address := ''; orig_system := ''; orig_system_id := to_number(null); fax := ''; status := ''; EXPIRATION_DATE := to_date(null); when others then Wf_Core.Context('Wfa_Sec', 'Get_Role_Info2', role); raise; 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 l_orig_system varchar2(30); l_orig_system_id number; l_user_id number; l_application_id number; result varchar2(32000); begin Wf_Directory.GetRoleOrigSysInfo(user_name, l_orig_system, l_orig_system_id); if (instr(l_orig_system, 'FND_USR') > 0) then result := fnd_profile.value_specific(name=>Get_Profile_Value.name, user_id=>l_orig_system_id); elsif ((SUBSTRB(l_orig_system, 1, 8) = 'FND_RESP') and (length(l_orig_system) > 8) and --Make sure we don't just get --'FND_RESP' (substr(l_orig_system, 9, 9) between '0' and '9')) then l_application_id := substr(l_orig_system, instr(l_orig_system,'FND_RESP')+8); result := fnd_profile.value_specific(name=>Get_Profile_Value.name, responsibility_id=>l_orig_system_id, application_id=>l_application_id); elsif (instr(l_orig_system, 'PER') > 0) then begin --Bug 2358728A --Obtain the user_id based on the unique user_name SELECT USER_ID INTO l_user_id FROM FND_USER WHERE user_name = Get_Profile_Value.user_name; exception when NO_DATA_FOUND then l_user_id := to_number(null); end; if (l_user_id is not null) then result := fnd_profile.value_specific(name=>Get_Profile_Value.name, user_id=>l_user_id); else result := null; end if; else result := null; end if; return result; 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 := Fnd_Global.Local_Chr(ascii_chr); 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 := Fnd_Global.Local_Chr(ascii_chr); end if; return WF_CORE.LOCAL_CS_TB; else return(Fnd_Global.Local_Chr(ascii_chr)); 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 fnd_profile.get('WF_ICX_MODE',x_mode); return ('OracleApps.DF?i_direct=' || WFA_SEC.DetailURL(nid) || '&i_mode=' || nvl(x_mode,'2')); 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 FND_GLOBAL.USER_NAME; exception when others then Wf_Core.Context('Wfa_Sec', 'GetFWKUserName'); raise; end GetFWKUserName; -- -- Logout -- This is a dummy procedure, wfa_html.logout should be used -- unless single signon feature is activated -- procedure Logout is begin return; 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 PARTITION (WF_LOCAL_ROLES) where NAME = role_name and ORIG_SYSTEM in ('WF_LOCAL_ROLES', 'WF_LOCAL_USERS') 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 PARTITION (WF_LOCAL_ROLES) set NAME = NewName where NAME = OldName and ORIG_SYSTEM in ('WF_LOCAL_USERS', 'WF_LOCAL_ROLES') and ORIG_SYSTEM_ID = 0; -- Update local user roles update WF_LOCAL_USER_ROLES PARTITION (WF_LOCAL_ROLES) set USER_NAME = NewName where USER_NAME = OldName and USER_ORIG_SYSTEM = 'WF_LOCAL_USERS' and USER_ORIG_SYSTEM_ID = 0; update WF_LOCAL_USER_ROLES PARTITION (WF_LOCAL_ROLES) set ROLE_NAME = NewName where ROLE_NAME = OldName and ROLE_ORIG_SYSTEM = 'WF_LOCAL_USERS' and ROLE_ORIG_SYSTEM_ID = 0; update WF_LOCAL_USER_ROLES PARTITION (WF_LOCAL_ROLES) set ROLE_NAME = NewName where ROLE_NAME = OldName and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES' and ROLE_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 username := wfa_sec.GetFWKUserName; 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 FND_GLOBAL.user_id; 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 FND_GLOBAL.login_id; 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 FND_GLOBAL.security_group_id; 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. -- RETURN -- boolean - True if session matches, else False function CheckSession return boolean is begin if (wfa_sec.g_session_id = fnd_session_management.g_session_id) then -- Session has not changed from the previous one or the WF Code executes in the -- background where both are -1. return true; else -- Cache current session id since it has changed wfa_sec.g_session_id := fnd_session_management.g_session_id; return false; end if; end CheckSession; end WFA_SEC; / --show errors package body WFA_SEC; commit; exit;