REM dbdrv: none /*=======================================================================+ | Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA| | All rights reserved. | +=======================================================================+ | FILENAME | | wfssob.pls | | DESCRIPTION | | WorkFlow Single Sign-On interface | | PL/SQL body for package: WF_SSO | | | | FUNCTION user_change | | | | MODIFICATION LOG: | | 01/2002 JWSMITH BUG 2001012 - Increased my_username, old_email, | | new_email to varchar2(320) | | 10/2002 KMA BUG 2429969 - Update WF_SSO package to reflect | | changes made to FND_USER_PKG (AFSCUSRB.pls 115.42) | *=======================================================================*/ whenever sqlerror exit failure rollback; create or replace package body WF_SSO as /* $Header: wfssob.pls 26.7 2004/06/14 22:24:19 rwunderl ship $ */ ----------------------------------------------------------------------------- /* * FUNCTION: USER_CHANGE * DESCRIPTION: The rule function for WF's subscription on the * oracle.apps.wf.public.user.change event. This function retrieves * the user's information and updates the workflow user table as needed. * (Model after FND_USER_PKG.USER_CHANGE) */ FUNCTION USER_CHANGE ( p_subscription_guid in raw, p_event in out wf_event_t ) return varchar2 is my_ent_type varchar2(50); my_username varchar2(320); my_mode varchar2(256); old_desc varchar2(1000); new_desc varchar2(4000); old_email varchar2(320); new_email varchar2(4000); old_fax varchar2(240); new_fax varchar2(4000); old_ntpref varchar2(8); new_ntpref varchar2(4000); begin -- -- No point re-processing our own changes -- if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'FND_USR') then -- (p_event.GetValueForParameter('CHANGE_SOURCE') = 'FND') return 'SUCCESS'; end if; my_username := p_event.GetEventKey(); my_mode := p_event.GetValueForParameter('CHANGE_TYPE'); if (my_mode = 'DELETE') then begin wf_directory.setadhocuserexpiration(my_username, sysdate); wf_purge.adhocdirectory(); exception when others then null; end; elsif (my_mode in ('ADD','MODIFY','LOAD')) then -- -- First check to see if user exists and get the existing attribute -- values. If the user does not exist, we're done. -- Convert nulls to *NULL* so that old values are either *NULL* -- or an actual value. -- begin select nvl(description, '*NULL*'), nvl(email_address, '*NULL*'), nvl(fax, '*NULL*'), nvl(notification_preference, '*NULL*') into old_desc, old_email, old_fax, old_ntpref from wf_local_roles where user_flag = 'Y' and name = my_username; exception when others then return 'SUCCESS'; end; -- -- Fetch the new values from the attribute cache -- New values can either be *NULL*, *UNKNOWN*, or an actual value -- my_ent_type := wf_entity_mgr.get_entity_type(p_event.GetEventName()); -- NOTE: While we have the ability to distinguish between null -- and "known to be null", the standard apis do not. -- For now, we're must pass null regardless which is -- treated as a "don't change". We do not yet support -- the ability to "null out" an attribute value. new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'DESCRIPTION'); new_email := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'MAIL'); new_fax := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'FACSIMILETELEPHONENUMBER'); new_ntpref := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'ORCLWORKFLOWNOTIFICATIONPREF'); -- -- Determine if there are any changes to the attributes we're -- interested in. And if so, update the user record. -- if (wf_entity_mgr.isChanged(new_desc, old_desc) OR wf_entity_mgr.isChanged(new_email, old_email) OR wf_entity_mgr.isChanged(new_fax, old_fax) OR wf_entity_mgr.isChanged(new_ntpref, old_ntpref)) then -- at least one of the attributes has changed -> update the user -- -- -- NOTE: the following conversions are necessary until we resolve -- null and "null out" -- if (new_desc = '*NULL*' or new_desc = '*UNKNOWN*') then new_desc := null; end if; if (new_email = '*NULL*' or new_email = '*UNKNOWN*') then new_email := null; end if; if (new_fax = '*NULL*' or new_fax = '*UNKNOWN*') then new_fax := null; end if; if (new_ntpref = '*NULL*' or new_ntpref = '*UNKNOWN*') then new_ntpref := null; end if; -- end of conversions -- -- description not available in setadhocuserattr wf_directory.setadhocuserattr( user_name => my_username, display_name => my_username, notification_preference => substrb(new_ntpref,1,8), language => null, territory => null, email_address => substrb(new_email, 1, 320), fax => substrb(new_fax, 1, 240)); end if; end if; return wf_rule.default_rule(p_subscription_guid, p_event); exception when others then wf_core.context('WF_SSO', 'USER_CHANGE', p_event.getEventName(), p_subscription_guid); wf_event.setErrorInfo(p_event, 'ERROR'); return 'ERROR'; end USER_CHANGE; ----------------------------------------------------------------------------- /* * FUNCTION: USER_CREATE_RF * DESCRIPTION: The rule function for WF's 2nd subscription on the * oracle.apps.wf.public.user.change event. This function * retrieves the user's information and creates the * corresponding workflow if the user does not already exist. * (Model after FND_USER_PKG.USER_CREATE_RF) */ FUNCTION USER_CREATE_RF(p_subscription_guid in raw, p_event in out nocopy wf_event_t) return varchar2 is my_ent_type varchar2(50); existing_user varchar2(1); my_username varchar2(320); my_mode varchar2(256); new_desc varchar2(1000); new_email varchar2(320); new_fax varchar2(240); new_ntpref varchar2(4000); begin -- -- No point re-processing our own changes -- if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'FND_USR') then return 'SUCCESS'; end if; -- -- This rule function only creates corresponding fnd users -- for those coming from LDAP -- if (p_event.GetValueForParameter('CHANGE_SOURCE') <> 'LDAP') then return 'SUCCESS'; end if; my_username := p_event.GetEventKey(); begin select 'Y' into existing_user from wf_local_roles where user_flag = 'Y' and name = my_username; return 'SUCCESS'; exception when others then -- user doesn't exist yet, we have work to do -- my_ent_type := wf_entity_mgr.get_entity_type(p_event.GetEventName()); -- -- Fetch the new values from the attribute cache -- New values can either be *NULL*, *UNKNOWN*, or an actual value -- new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'DESCRIPTION'); new_email := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'MAIL'); new_fax := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'FACSIMILETELEPHONENUMBER'); new_ntpref := wf_entity_mgr.get_attribute_value(my_ent_type, my_username, 'ORCLWORKFLOWNOTIFICATIONPREF'); -- -- NOTE: the following conversions are necessary until we resolve -- null and "null out" -- if (new_desc = '*NULL*' or new_desc = '*UNKNOWN*') then new_desc := null; end if; if (new_email = '*NULL*' or new_email = '*UNKNOWN*') then new_email := null; end if; if (new_fax = '*NULL*' or new_fax = '*UNKNOWN*') then new_fax := null; end if; if (new_ntpref = '*NULL*' or new_ntpref = '*UNKNOWN*') then new_ntpref := null; end if; -- end of conversions -- wf_directory.createadhocuser( name => my_username, display_name => my_username, language => null, territory => null, description => substrb(new_desc, 1, 1000), notification_preference => nvl(substrb(new_ntpref,1,8), 'MAILHTML'), email_address => substrb(new_email, 1, 320), fax => substrb(new_fax, 1, 240), status => 'ACTIVE', expiration_date => null); end; return wf_rule.default_rule(p_subscription_guid, p_event); exception when others then wf_core.context('WF_SSO', 'USER_CREATE_RF', p_event.getEventName(), p_subscription_guid); wf_event.setErrorInfo(p_event, 'ERROR'); return 'ERROR'; end USER_CREATE_RF; -------------------------------------------------------------------------- end WF_SSO; / -- show errors package body WF_SSO; commit; exit;