Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: prefs_pkgbodys.sql 07-aug-2006.16:08:27 swexler Exp $
Rem
Rem prefs_pkgbodys.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      prefs_pkgbodys.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    swexler     02/21/06 - Created
Rem



CREATE OR REPLACE PACKAGE body DB_PREFERENCES AS




procedure updatePrefs(
        targetName IN VARCHAR2,
        targetType IN VARCHAR2,
        pageName IN VARCHAR2,
        preferenceName IN VARCHAR2,
        preferenceValue IN VARCHAR2) IS
	
  lcount            NUMBER;
  userName          VARCHAR2(64) := ' ';
  
begin

   IF targetName IS NULL THEN
     BEGIN
       updateGlobalPrefs(pageName, preferenceName, preferenceValue);
       RETURN;
     END;
   END IF;


   userName := MGMT_USER.get_current_em_user;

   SELECT count(*) into lcount
    FROM DB_USER_PREFERENCES
     WHERE
      TARGET_NAME = targetName AND
      TARGET_TYPE = targetType AND
      PREFERENCE_NAME = preferenceName AND
      USER_NAME = userName;

   IF lcount = 0 THEN
    INSERT INTO DB_USER_PREFERENCES (
     TARGET_NAME, TARGET_TYPE, PAGE_NAME, USER_NAME,
     PREFERENCE_NAME, PREFERENCE_VALUE)
     VALUES (targetName, targetType, pageName,
             userName, preferenceName, preferenceValue);
             commit;
   ELSE
    UPDATE DB_USER_PREFERENCES 
     SET PREFERENCE_VALUE = preferenceValue
     WHERE 
     TARGET_NAME = targetName AND TARGET_TYPE = targetType
     AND PREFERENCE_NAME = preferenceName AND USER_NAME = userName;
   commit;

   END IF;

end updatePrefs;



procedure updateGlobalPrefs(
        pageName IN VARCHAR2,
        preferenceName IN VARCHAR2,
        preferenceValue IN VARCHAR2) IS


  lcount            NUMBER;
  userName          VARCHAR2(64) := ' ';

  begin

    userName := MGMT_USER.get_current_em_user;

   SELECT count(*) into lcount
    FROM DB_USER_PREFERENCES
     WHERE
      TARGET_NAME IS NULL AND
      PREFERENCE_NAME = preferenceName AND
      USER_NAME = userName;


   IF lcount = 0 THEN
    INSERT INTO DB_USER_PREFERENCES (
     TARGET_NAME, TARGET_TYPE, PAGE_NAME, USER_NAME,
     PREFERENCE_NAME, PREFERENCE_VALUE)
     VALUES (NULL, NULL, pageName,
             userName, preferenceName, preferenceValue);
             commit;
   ELSE
    UPDATE DB_USER_PREFERENCES 
     SET PREFERENCE_VALUE = preferenceValue
     WHERE 
     TARGET_NAME IS NULL AND TARGET_TYPE IS NULL
     AND PREFERENCE_NAME = preferenceName AND USER_NAME = userName;
   commit;

   END IF;

end updateGlobalPrefs;


end DB_PREFERENCES;
/
show errors;