Rem drv: <create type="views" pos="preferences/preferences_views.sql+"/>
Rem
Rem $Header: assoc_views.sql 01-jul-2005.18:43:37 gsbhatia Exp $
Rem
Rem assoc_views.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      assoc_views.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    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ramalhot    01/17/05 - assoc def name changed from member to contains 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    ramalhot    07/13/04 - 
Rem    ramalhot    07/07/04 - ramalhot_assoc_object_creation
Rem    ramalhot    07/06/04 - Created
Rem

CREATE OR REPLACE VIEW MGMT_TARGET_MEMBERSHIPS AS 
    SELECT t1.target_name composite_target_name,
           t1.target_type composite_target_type,
           i.source_target_guid composite_target_guid,
           t2.target_name member_target_name,
           t2.target_type member_target_type,
           i.assoc_target_guid member_target_guid,
           ' ' association
      FROM MGMT_TARGET_ASSOCS i,
           MGMT_TARGET_ASSOC_DEFS a, 
           MGMT_TARGETS t1, 
           MGMT_TARGETS t2    
     WHERE t1.target_guid = i.source_target_guid
       AND t2.target_guid = i.assoc_target_guid
       AND i.assoc_guid = a.assoc_guid
       AND a.assoc_def_name = 'contains'
       AND a.scope_target_type = ' ';

rem
rem  PURPOSE
rem
rem     The MGMT_FLAT_TARGET_MEMBERSHIPS view holds the flattend list of
rem     group targets and composite targets.It exists only for backword 
rem     compatibilty. for better performance, use mgmt_flat_target_assoc table 
rem
rem  COLUMNS
rem
rem     COMPOSITE_TARGET_NAME    - the name of the composite target.  A composite
rem                                target is a grouping of one or more target name
rem                                and target types from the target table.
rem
rem     COMPOSITE_TARGET_TYPE    - the type of the composite target.
rem
rem     COMPOSITE_TARGET_GUID    - the unique id of the composite target
rem
rem     MEMBER_TARGET_NAME       - the name of the target that is a member of this
rem                                composite target.
rem
rem     MEMBER_TARGET_TYPE       - the type of the target that is a member of this
rem                                composite target.
rem
rem     MEMBER_TARGET_GUID       - the unique id of the target that is a member of 
rem                                this composite target
rem
rem     IS_GROUP_MEMB            - "1" indicates that the member target 
rem                                appears as a direct child of a "group" in
rem                                the group hierarchy; "0" means the member
rem                                target is derived from a composite 
rem                                membership.
rem

CREATE OR REPLACE VIEW MGMT_FLAT_TARGET_MEMBERSHIPS AS
  SELECT ct.target_name composite_target_name,
         ct.target_type composite_target_type,
         ct.target_guid composite_target_guid,
         mt.target_name member_target_name,
         mt.target_type member_target_type,
         mt.target_guid member_target_guid,
         (
          SELECT CASE
                   WHEN (count(*) = 0)
                     THEN 0
                   ELSE 1    
                 END
             FROM mgmt_flat_target_assoc f2,
                  mgmt_target_assocs m,
                  mgmt_targets ct,
                  mgmt_type_properties p,
                  mgmt_target_assoc_defs def
            WHERE p.property_name = 'is_group'
              AND m.assoc_guid = def.assoc_guid
              AND def.assoc_def_name = 'contains'
              AND def.scope_target_type = ' '
              AND f2.is_membership = 1
              AND p.target_type   = ct.target_type
              AND ct.target_guid = m.source_target_guid
              AND (m.source_target_guid = f2.assoc_target_guid OR
                   m.source_target_guid = f2.source_target_guid)
              AND a.source_target_guid = f2.source_target_guid
              AND a.assoc_target_guid = m.assoc_target_guid
         )is_group_memb
    FROM mgmt_flat_target_assoc a,
         mgmt_targets ct,
         mgmt_targets mt
   WHERE a.is_membership = 1
     AND ct.target_guid = a.source_target_guid 
     AND mt.target_guid = a.assoc_target_guid;