Rem drv: <create type="pkgbodys" pos="ping/ping_pkgbodys.sql+"/>
Rem
Rem $Header: preferences_pkgbodys.sql 01-jul-2005.21:25:54 gsbhatia Exp $
Rem
Rem preferences_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      preferences_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    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    yaofeng     04/04/05 - use target name 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    asawant     09/27/04 - Adding constants for well known subtabs 
Rem    vagarwal    09/27/04 - add system folder 
Rem    jpyang      09/20/04 - add Services folder 
Rem    jriel       10/17/02 - add webapp folder
Rem    yaofeng     10/25/02 - Fixed bug 2634470
Rem    rpatti      09/18/02 - change order acc to out-of-box spec
Rem    rpatti      09/17/02 - change folder ids
Rem    rpatti      09/16/02 - resurrecting create_folders
Rem    rpatti      09/11/02 - support custom folders
Rem    rpatti      06/28/02 - remove union all
Rem    rpatti      06/27/02 - sort folder names
Rem    rpatti      06/18/02 - support new system folders
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    rpatti      02/25/02 - use function to get current user
Rem    ancheng     02/05/02 - modify query
Rem    ancheng     02/01/02 - fix duplicate
Rem    ancheng     01/25/02 - fix get_folders_info
Rem    ancheng     01/23/02 - Merged ancheng_folder_git3
Rem    ancheng     01/22/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY emd_pref AS

--
-- Name: get_folders_info
--
-- Package: emd_pref
--
-- Purpose: 
--  Get folder related info
--
PROCEDURE get_folders_info(show_avail_group_tgts_in IN NUMBER,
                           folders_cur_out OUT CURSOR_TYPE) IS

  l_user_name VARCHAR2(256);

BEGIN
  -- get current user name from sys context
  l_user_name := UPPER(mgmt_user.get_current_em_user() );

  -- find all folders info
  IF(show_avail_group_tgts_in = 1)
  THEN
    OPEN folders_cur_out FOR
      SELECT *
        FROM ( (SELECT avail_folders.folder_name, avail_folders.display_order, 
                       targets.target_name, targets.target_type, targets.target_guid
                  FROM (SELECT *
                          FROM mgmt_user_folders fdrs
                         WHERE fdrs.user_name = l_user_name
                           AND (fdrs.target_guid IS NULL OR 
                                EXISTS(
                                  SELECT fdrs.target_guid
                                    FROM mgmt_targets tgts
                                   WHERE fdrs.target_guid = tgts.target_guid) ) ) avail_folders,
                       mgmt_targets targets
                 WHERE avail_folders.user_name = l_user_name
                   AND avail_folders.target_guid = targets.target_guid (+) )
               UNION 
               -- find out the group targets that are visible to the user but not 
               -- in the mgmt_user_folders yet.
               (SELECT targets.target_name AS folder_name, -1 AS display_order, 
                       targets.target_name, targets.target_type, targets.target_guid
                  FROM mgmt_targets targets
                 WHERE targets.target_type = 'composite' AND
                       NOT EXISTS(             
                         SELECT targets.target_guid
                           FROM mgmt_user_folders fdrs
                          WHERE fdrs.user_name = l_user_name
                            AND fdrs.target_guid = targets.target_guid) ) ) folders
       ORDER BY folders.display_order, folders.folder_name;
  ELSE
    OPEN folders_cur_out FOR
        SELECT avail_folders.folder_name, avail_folders.display_order, 
               targets.target_name, targets.target_type, targets.target_guid
          FROM (SELECT *
                  FROM mgmt_user_folders fdrs
                 WHERE fdrs.user_name = l_user_name
                   AND (fdrs.target_guid IS NULL OR 
                        EXISTS(
                          SELECT fdrs.target_guid
                            FROM mgmt_targets tgts
                           WHERE fdrs.target_guid = tgts.target_guid) ) ) avail_folders,
               mgmt_targets targets
         WHERE avail_folders.user_name = l_user_name
           AND avail_folders.target_guid = targets.target_guid (+)
      ORDER BY avail_folders.display_order, avail_folders.folder_name;
  END IF;
           
END get_folders_info;

--
-- Name: set_folders_info
--
-- Package: emd_pref
--
-- Purpose: 
--  Set folder related info
--
PROCEDURE set_folders_info(folders_array_in IN MGMT_FOLDER_ARRAY) IS

  l_user_name VARCHAR2(256);

BEGIN

  -- get the current user name from sys context
  l_user_name := UPPER(mgmt_user.get_current_em_user() );

  -- delete old rows
  DELETE FROM mgmt_user_folders
    WHERE user_name = l_user_name;

  -- insert new rows
  INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    SELECT l_user_name, f.folder_name, f.display_order, f.target_guid
      FROM TABLE(CAST(folders_array_in AS MGMT_FOLDER_ARRAY) ) f;

END set_folders_info;


--
-- Name: manage_user_folders
--
-- Package: emd_pref
--
-- Purpose:
--  manager folder related info - called as a call back when a user is 
--  created/dropped
--
-- IN parameters:
--   user_name_in: VARCHAR2
--                 The user name for whom the folders are created
--   type_in: NUMBER
--            MGMT_USER.USER_CREATED_CALLBACK when user is being created
--            MGMT_USER.USER_DROPPED_CALLBACK when user is being dropped
-- OUT parameters:
--
PROCEDURE manage_user_folders(user_name_in IN VARCHAR2,
                              type_in      IN NUMBER) IS

  l_count INTEGER := 0;

BEGIN

  IF (type_in = MGMT_USER.USER_CREATED_CALLBACK)
  THEN

 -- insert hosts folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, 'Hosts', l_count, '');

    l_count := l_count + 1;

 -- insert Databases folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, MGMT_USER_PREF_DATABASES, l_count, '');

    l_count := l_count + 1;

    -- insert application servers folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, MGMT_USER_PREF_APPSERVER, l_count, '');

    l_count := l_count + 1;

-- insert web applications folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, MGMT_USER_PREF_WEBAPPS, l_count, '');

    l_count := l_count + 1;

    -- insert services folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, MGMT_USER_PREF_SERVICES, l_count, '');

    l_count := l_count + 1;

    -- insert system folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, MGMT_USER_PREF_SYSTEMS, l_count, '');

    l_count := l_count + 1;

    -- insert groups folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, mgmt_user_pref_groups, l_count, '');

    l_count := l_count + 1;

    -- insert All Targets folder
    INSERT INTO mgmt_user_folders(user_name, folder_name, display_order, target_guid)
    VALUES(user_name_in, mgmt_user_pref_all_targets, l_count, '');

    l_count := l_count + 1;

  ELSIF (type_in = MGMT_USER.USER_DROPPED_CALLBACK)
  THEN
    DELETE FROM mgmt_user_folders
     WHERE user_name = user_name_in;

  END IF;

END manage_user_folders;

END emd_pref;
/

SHOW ERRORS;