/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUT ON

DECLARE

  l_sql        varchar2(400);
  l_profile_value     varchar2(400) := '0';

BEGIN

  --
  -- Retrieve the current profile value using dynamic SQL
  --
  l_sql := ' SELECT profile_option_value '
        || ' FROM fnd_profile_option_values fpv, '
        || ' fnd_profile_options_vl fpv1, '
        || ' fnd_user u '
        || ' WHERE UPPER(user_profile_option_name) LIKE UPPER(''Application%SSO%Login%Type%'') '
        || ' AND fpv.profile_option_id=fpv1.profile_option_id '
        || ' AND fpv.level_value=u.user_id '
        || ' AND u.user_name=''GUEST'' ';

  EXECUTE IMMEDIATE l_sql INTO l_profile_value;
  
  IF l_profile_value IS NULL
  THEN
  
  l_profile_value := '0';
  dbms_output.put_line(l_profile_value);
  
  ELSE
  
  dbms_output.put_line(l_profile_value); 
  
  END IF;
  
EXCEPTION

  WHEN others THEN

    dbms_output.put_line(l_profile_value);

END;
/
COMMIT;
EXIT;