Rem drv: <create type="views" pos="license/mgmt_license_views.sql+"/>
Rem
Rem $Header: sdk_views.sql 08-oct-2007.14:59:26 skini Exp $
Rem
Rem sdk_views.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_views.sql - View window to Mozart Schema
Rem
Rem    DESCRIPTION
Rem      sdk_views provide a window into Mozart Schema for Integrators, DBAs
Rem      and Developers, Vendors etc for their management information needs.
Rem
Rem    NOTES
Rem      Views are grouped into the following areas :
Rem        Central Policies
Rem           Blackouts
Rem           Baselines
Rem           Notifications and Escalations
Rem        Monitoring
Rem           Availability, Metrics, Alerts and Jobs
Rem        Inventory
Rem           Targets, Types, Config and Patches
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)    
Rem    skini       10/08/07 - Perf fix for bug 6496238
Rem    denath      08/11/07 - Fix 6270373.add target display name, type display
Rem                           name to mgmt$target_type view.
Rem    denath      09/01/07 - Backport denath_bug-6270373 from main
Rem    denath      08/24/07 - Backport 6274425 from main.add violation_level in
Rem                           mgmt$alert_history.
Rem    jsadras     05/21/07 - add cycle_guid to alert_history/current
Rem    jsadras     05/18/07 - Bug:4603352, add metric_guid in views
Rem    denath      08/21/07 - Backport jsadras_bug-4603352 from main
Rem    mkm         05/21/07 - Update for er 5893761
Rem    mkatyan     05/28/07 - Backport mkm_bug-5893761 from main
Rem    neearora    05/16/07 - ER 5958873
Rem    groyal      08/25/06 - Changes to config standard-related views
Rem    sthiruna    07/31/06 - Copying 10.2.0.3 Config Standard from EMCORE_MAIN_LINUX                  
Rem    sthiruna    07/17/06 - Adding Not Evaluated Rules column to needed 
Rem                           views 
Rem    aptrived    07/10/06 - Bug#5360576, Adding decode for job status 
Rem    aragarwa    07/04/06 - Removing importance from rulefolder view. 
Rem    skini       07/05/06 - Add hextoraw to mgmt_job 
Rem    sthiruna    06/02/06 - Fix for Bug 5256574. Renaming the view 
Rem                           mgmt$ics_hierarchy to mgmt$cs_hierarchy and 
Rem                           commenting the view creation command 
Rem    sthiruna    06/01/06 - Fix for Bug 5256571.Modified the view mgmt$cs_rules. 
Rem                           Decoded r.test_type 
Rem                           Decoding 'importance_level'in views mgmt$cs_rules, 
Rem                           mgmt$cs_rulefolders 
Rem    chyu        05/06/06 - RCU Compliant 
Rem    aragarwa    05/18/06 - removing dname from compliance hierarchy table 
Rem                           views. 
Rem    niramach    05/09/06 - 10.3 and 11g table unification related changes. 
Rem    neearora    05/03/06 - Bug 4732492. Added hextoraw in 
Rem                           MGMT$TARGET_ASSOCIATIONS view definition 
Rem    spahuja     02/24/06 - Creating views for compliance reports 
Rem    scgrover    02/15/06 - chenge for raw trigger fix 
Rem    asaraswa    07/20/05 - fixing bug 4503724 
Rem    asaraswa    07/13/05 - fixing mgmt$os_components for bug 4491006
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    rpinnama    06/28/05 - Fix mgmt$policies 
Rem    niramach    04/28/05 - Fix for bug 4334335. 
Rem    gbhat       04/23/05 - Add source_obj_guid columns to 
Rem                           mgmt$alert_notif_log and mgmt$alert_annotations 
Rem    rpinnama    04/11/05 - 
Rem    gbhat       04/05/05 - Add type_display_name column to 
Rem                           mgmt$availability_current 
Rem    niramach    03/22/05 - Fix the type_display_name of mgmt$target view. 
Rem    gbhat       03/16/05 - Add view mgmt$target_type_def that is built on 
Rem                           mgmt_target_types. 
Rem    aragarwa    03/15/05 - Adding Installation time and location for other 
Rem                           software on hosts. 
Rem    gbhat       03/08/05 - Add type_display_name to mgmt$alert_history, 
Rem                           mgmt$alert_current and 
Rem                           mgmt$policy_violation_current. 
Rem    niramach    03/01/05 - Add nls support for policy views. 
Rem    gbhat       03/04/05 - Add type_display_name to 
Rem                           mgmt$policy_violation_history 
Rem    ramalhot    02/16/05 - mgmt$target_members modifed 
Rem    niramach    02/03/05 - Change exempt to suppress for views. 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    agor        02/10/05 - one more view for csa report 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    gbhat       01/31/05 - Add PropertyType to target_properties view. 
Rem    agor        01/19/05 - add more views for reports 
Rem    gbhat       01/26/05 - Add policy_evaluation summary view
Rem                           supplied by Ravi Pinnamaneni. 
Rem    ramalhot    01/17/05 - assoc def name changed from member to contains 
Rem    pmodi       01/16/05 - Adding target_name column to 
Rem                           mgmt$target_type_properties 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    gbhat       01/04/05 - Add views for metric_errors 
Rem    gbhat       12/28/04 - Add violation_type to alert_history view. Modify 
Rem                           where clause to restrict on violation type 
Rem    gbhat       12/27/04 - Fix target_metric_settings view to have correct 
Rem                           key_part values 
Rem    gbhat       12/27/04 - Fix for bug 4072480 
Rem    skini       12/22/04 - Add status to BO history 
Rem    skini       12/20/04 - Code Review Comments 
Rem    skini       12/17/04 - Add job and blackout views 
Rem    rpinnama    12/15/04   - 
Rem    streddy     12/13/04   - Add transposed metric support 
Rem    dcawley     12/09/04   - Fix annotation views 
Rem    rpinnama    12/09/04   - Add enabled flag to 
Rem                             mgmt$target_metric_collections 
Rem    gbhat       12/07/04   - Fixes to column counts in commented views. 
Rem    gbhat       12/02/04   - Updates of old views and addition of new views 
Rem    ramalhot    10/27/04  -  assoc view modified 
Rem    gbhat       10/25/04  - Fix errors. 
Rem    gbhat       10/22/04  - Fixes based on high level review of repository 
Rem                            views. 
Rem    vkhizder    10/07/04  - adding view for home properties, and snapshot_guid column
Rem                            to various ecm views 
Rem    jriel       09/30/04  - update notif constants 
Rem    mgoodric    10/05/04 - integrate MGMT$DELTA_xxx views with configHistory 
Rem    xshen       09/21/04 - moving delta_init and delta_dbrollup 
Rem    gbhat       09/19/04 - Deprecate some MGMT$... views in favour of new 
Rem                            CIM views 
Rem    vkhizder    09/08/04 - add target_guid to ecm view 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    ramalhot    08/19/04 - column name changed 
Rem    ramalhot    07/13/04 - assoc view added 
Rem    niramach    06/04/04 - Modifed mgmtmissing_targets to have host 
Rem                            name,oracle home and diagnostic columns for 
Rem                            enhancement 
Rem    sbadrina    05/06/04 -  add MGMT$DELTA_DBROLLUP view 
Rem    xshen       05/04/04 - rm mgmt$delta_tablespaces, mv mgmt$delta_init to sdk
Rem    kchiasso    10/31/03 - editting mgmt$missing_targets and 
Rem                           mgmt$missing_targets_in_groups to exclude hosts 
Rem    vkhizder    10/31/03 - adding more columns to mgmt$software_components 
Rem    tguay       10/30/03 - fix current availability view 
Rem    tguay       10/28/03 - add meta type to target properties view 
Rem    tguay       10/23/03 - change where clause for category queries 
Rem    asaraswa    10/22/03 - 
Rem    asaraswa    10/21/03 - 
Rem    asaraswa    10/21/03 - 
Rem    asaraswa    10/21/03 - adding view to get missing targets for groups 
Rem    tguay       10/21/03 - fix views impacted by meta version and category 
Rem    shuberma    10/09/03 - history view change 
Rem    groyal      10/07/03 - Remove policy-related views 
Rem    streddy     09/22/03 - Added flat membership table views 
Rem    awarkar     09/18/03 - Bug Fix For Issue#2864666 
Rem    groyal      08/26/03 - Modify policy-related views 
Rem    asaraswa    08/21/03 - adding view mgmt$missing_targets to enhance ecm 
Rem                           search page 
Rem    groyal      07/24/03 - Tweak policy rollup views
Rem    asaraswa    07/14/03 - adding new columns to mgmt$os_hw_summary for bug 2899979
Rem    shuberma    07/08/03 - Changing view to call function to get key string instead of using existing
Rem    vkhizder    06/03/03 - adding ecm fmwk sdk view for snapshots
Rem    groyal      06/11/03 - Add nls support for policy
Rem    groyal      06/10/03 - Fix MGMT$DB_POLICY_VIOLATIONS and MGMT$HOST_POLICY_VIOLATIONS
Rem    groyal      06/06/03 - Rename mgmt_rule_def_columns
Rem    groyal      06/04/03 - Reworking policy api slightly
Rem    groyal      05/23/03 - 
Rem    groyal      05/23/03 - 
Rem    shuberma    05/20/03 - Changing policy severity to priority
Rem    groyal      05/06/03 - Get host_name from mgmt_targets
Rem    vkhizder    05/01/03 - changing emd_url to host_name/target_name matching for finding target's host
Rem    groyal      05/01/03 - Add policy-related views
Rem    sgrover     03/19/03 - remove load_timestamp, variance
Rem    shuberma    03/13/03 - Correcting os component history view
Rem    shuberma    02/27/03 - Modifying the delta_fs_...view so not return insert or delete details as separate rows
Rem    goramamo    11/01/02 - mount_time_removal
Rem    shuberma    10/28/02 - Making view for history return a entry even when no non-key values are recorded
Rem    tzimmerm    10/10/02 - Removing views referencing mgmt_availability_1Day tbs 
Rem    kchiasso    09/16/02 - add home_location to view patchsets
Rem    vkhizder    09/19/02 - ecm view modifications
Rem    shuberma    09/03/02 - Updating the kernel param view to reflect new data
Rem    aholser     08/24/02 - fix createrep error
Rem    shuberma    08/21/02 - 
Rem    shuberma    08/21/02 - Adding search views.
Rem    shuberma    08/15/02 - Adding more history views
Rem    tjaiswal    07/09/02 - Obsolete mgmt annotations table
Rem    shuberma    06/25/02 - Adding a view for looking at vendor sw history
Rem    shuberma    06/21/02 - Adding new history views and correcting other history views
Rem    shuberma    06/18/02 - MGMT$TARGET_COMPONENT view has type.  Change 0 -> O
Rem    shuberma    06/13/02 - Correct the MGMT$SOFTWARE_COMPONENT view..
Rem    rpinnama    05/16/02 - Move MGMT$DB_INIT_PARAMS to db/config .
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    shuberma    05/06/02 - Functions used by MGMT_DELTA views no longer use clobs..
Rem    shuberma    04/29/02 - Changing the os patch query for efficiency reasons..
Rem    shuberma    04/16/02 - Using emd_url instead of host_name for join..
Rem    shuberma    04/11/02 - Removing the mgmt$delta_os_summary view since we 
Rem                           currently aren't capturing those deltas..
Rem    shuberma    04/08/02 - Adding view for looking a os patches as a whole..
Rem    shuberma    03/22/02 - Fixing typo in view..
Rem    shuberma    03/14/02 - Adding some new delta views and fixing some olf software views..
Rem    shuberma    03/05/02 - Correcting software views and adding delta views..
Rem    tguay       02/11/02 - Fix for bug 2217275
Rem    rpinnama    01/14/02 - Show Unmonitored state.
Rem    vkhizder    01/04/02 - adding real version to software component
Rem    edemembe    01/08/02 - Metrics are now target independent
Rem    vkhizder    12/20/01 - fixing ECM-related views
Rem    vkhizder    11/15/01 - fixing views due to ECM schema change
Rem    dmshah      11/14/01 - Fixing the sql statement for the view
Rem    dmshah      11/09/01 - Adding the views
Rem    dmshah      11/07/01 - Merged dmshah_unhook_sdkviews
Rem    dmshah      11/07/01 - Created
Rem

-- View Name : MGMT$TARGET
-- Description : Associates Target Name, Target Type, EMD URL, Display Name
--                          Host Name and Last Load Time
  CREATE OR REPLACE VIEW mgmt$target
      (target_name, target_type, target_guid, type_version, type_qualifier1,
       type_qualifier2, type_qualifier3, type_qualifier4, 
       type_qualifier5, emd_url, timezone_region, display_name, 
       host_name, last_metric_load_time,type_display_name)
    AS
      SELECT
          t.target_name, t.target_type, t.target_guid, t.type_meta_ver, 
          t.category_prop_1, t.category_prop_2, t.category_prop_3, 
          t.category_prop_4, t.category_prop_5, t.emd_url, t.timezone_region, 
          t.display_name, t.host_name, t.last_load_time,nvl(tt.type_display_name,
          t.type_display_name) 
        FROM 
            mgmt_targets t, mgmt_target_types tt 
        WHERE
            t.target_type = tt.target_type(+)             
    WITH READ ONLY;

-- View Name : MGMT$TARGET_PROPERTIES
-- Description : Associates Target Name, Target Type, Property name and Value
  CREATE OR REPLACE VIEW mgmt$target_properties
      (target_name, target_type, target_guid, property_name,
	   property_value, property_type)
    AS 
      SELECT 
          t.target_name, t.target_type, t.target_guid,
          p.property_name, p.property_value, p.property_type
        FROM 
            mgmt_targets t,
            mgmt_target_properties p
        WHERE 
            t.target_guid = p.target_guid
    WITH READ ONLY; 

-- View Name : MGMT$TARGET_TYPE
-- Description : Associates Target Name, Target Type, Metric Name, Metric Column
--               Key Column, Metric Type, Metric Label, Column Label,
--               Description and Unit
  CREATE OR REPLACE VIEW mgmt$target_type
      (target_name, target_type, target_guid, type_version, type_qualifier1,
       type_qualifier2, type_qualifier3, type_qualifier4, 
       type_qualifier5, metric_name, metric_column, metric_guid,key_column,
       metric_type, metric_label, column_label, description,description_nlsid,
       unit,unit_nlsid,short_name,short_name_nlsid,display_name,type_display_name)
    AS 
      SELECT 
          t.target_name, t.target_type, t.target_guid,  t.type_meta_ver, 
          t.category_prop_1, t.category_prop_2, t.category_prop_3, 
          t.category_prop_4, t.category_prop_5, metric_name, 
          metric_column, metric_guid,key_column, 
          DECODE (metric_type, 0, 'Number', 1, 'String', 2, 'Table',
                               3, 'Raw', 4, 'External', 5, 'Repository Metric'), 
          metric_label, column_label, description,description_nlsid,unit,
          unit_nlsid,short_name,short_name_nlsid,display_name,type_display_name  
        FROM 
            mgmt_metrics m, mgmt_targets t 
        WHERE 
            t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
    WITH READ ONLY; 

-- View Name : MGMT$TARGET_TYPE_PROPERTIES
-- Description : Associates Target Name, Target Type, Property name and Value
CREATE OR REPLACE VIEW MGMT$TARGET_TYPE_PROPERTIES
      (target_name, target_type, property_name, property_value)
    AS
      SELECT
          t.target_name, tp.target_type, tp.property_name, tp.property_value
        FROM
            mgmt_type_properties tp, 
			mgmt_targets t
	   WHERE t.target_type=tp.target_type
    WITH READ ONLY;

-- View Name : MGMT$TARGET_TYPE_DEF
-- Description : Projection on the mgmt_target_types table.
-- 
  CREATE OR REPLACE VIEW mgmt$target_type_def
      (target_type, type_display_name, target_type_guid,
	  max_type_meta_ver)
	AS
	  SELECT
	      t.target_type, t.type_display_name, t.target_type_guid,
		  t.max_type_meta_ver
		FROM
		    mgmt_target_types t, mgmt_targets tt
		WHERE
		    t.target_type = tt.target_type
	WITH READ ONLY;

-- View Name : MGMT$METRIC_COLLECTION
-- Description : Metric Collection like frequency, upload policy and thresholds
--               per target
  CREATE OR REPLACE VIEW mgmt$metric_collection
       (target_name, target_type, target_guid,
        metric_name, metric_column, key_value,
        collection_frequency, upload_policy, warning_operator,
        warning_threshold, critical_operator, critical_threshold,
        occurence_count, warning_count, critical_count)
     AS
       SELECT   
           t.target_name, t.target_type, t.target_guid,
           m.metric_name, m.metric_column, s.key_value,
           NULL, NULL,
           DECODE (s.warning_operator, 0, 'Greater Than', 1, 'Equal To', 2, 'Less Than',
                                     3, 'Less Than or Equal To', 4, 'Contains', 
                                     5, 'Not Equal', 6, 'Match'),
           s.warning_threshold,
           DECODE (s.critical_operator, 0, 'Greater Than', 1, 'Equal To', 2, 'Less Than',
                                     3, 'Less Than or Equal To', 4, 'Contains', 
                                     5, 'Not Equal', 6, 'Match'),
           s.critical_threshold, s.num_occurences, s.num_warnings, s.num_criticals
         FROM
             mgmt_targets t,
             mgmt_metrics m,
             mgmt_metric_thresholds s
          WHERE t.target_guid = s.target_guid
            and m.metric_guid = s.metric_guid
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
    WITH READ ONLY;

COMMENT ON TABLE MGMT$METRIC_COLLECTION IS
'The MGMT$METRIC_COLLECTION view has been deprecated. Please consult 
the documentation for the MGMT$TARGET_METRIC_COLLECTIONS and 
MGMT$TARGET_METRIC_SETTINGS views for alternatives.';

CREATE OR REPLACE VIEW mgmt$target_metric_collections
      (target_name, target_type, target_guid, metric_name, 
       metric_column, metric_guid, collection_name, is_enabled, 
       is_repository, frequency_code, collection_frequency, upload_policy)
    AS
      SELECT 
	     t.target_name, t.target_type, t.target_guid, m.metric_name, 
	     m.metric_column, m.metric_guid, c.coll_name, c.is_enabled, 
	     m.is_repository, 
             DECODE(c.frequency_code, 1, 'One Time',
                                   2, 'Interval',
                                   3, 'Daily',
                                   4, 'Weekly',
                                   5, 'Monthly',
                                   6, 'Yearly',
                                     'On-Demand'),
             DECODE(c.frequency_code, 1, to_char(c.start_time,'DD-MON-YY HH24:MI'),
                                   2, to_char(interval),
                                   3, to_char(execution_hours)||':'||to_char(execution_minutes),
                                   4, to_char(execution_hours)||':'||to_char(execution_minutes),
                                   5, to_char(execution_hours)||':'||to_char(execution_minutes),
                                   6, to_char(execution_hours)||':'||to_char(execution_minutes),
                                      'On-Demand'),
             c.upload_frequency
        FROM mgmt_targets t, 
             mgmt_metrics m, 
             mgmt_collections c,
             mgmt_collection_metric_tasks cmt
       WHERE t.target_type = m.target_type
         AND t.type_meta_ver = m.type_meta_ver
         AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
         AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
         AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
         AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
         AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
         AND c.object_guid = t.target_guid
         AND m.metric_guid = cmt.metric_guid
     AND c.object_guid = cmt.target_guid
     AND c.coll_name = cmt.coll_name
     AND c.object_type = 2
WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$target_metric_settings
      (target_name, target_type, target_guid, metric_name, metric_column, 
       metric_guid, collection_name, category, key_value, key_value2,
       key_value3, key_value4, key_value5, key_operator, has_active_baseline,
       prevent_override, warning_operator, warning_threshold,
       critical_operator, critical_threshold, occurrence_count,
       warning_action_type, warning_action_job_type, 
       warning_action_job_owner, warning_action_job_name, 
       critical_action_type, critical_action_job_type, 
       critical_action_job_owner, critical_action_job_name)
    AS
      SELECT 
             t.target_name, t.target_type, t.target_guid, m.metric_name, 
             m.metric_column, m.metric_guid, pa.coll_name, 
             func_cats.category_name, pac.key_value, null, null, null, 
             null, pac.key_operator, pac.has_active_baseline, pac.prevent_override, 
             pac.condition_operator, pacp.warn_threshold, 
             pac.condition_operator, pacp.crit_threshold, 
             pac.num_occurrences, 
             DECODE(TRIM(pac.fixit_job),
	              NULL, DECODE(tgt_warn_cas.job_type, 
                                     NULL, 'No-action', 
                                     'Corrective-Action'), 
                     'Agent-Fixit Job'), 
             tgt_warn_cas.job_type, tgt_warn_cas.job_owner, tgt_warn_cas.job_name,
             DECODE(TRIM(pac.fixit_job),
                      NULL, DECODE(tgt_crit_cas.job_type, 
                                     NULL, 'No-action', 
                                     'Corrective-Action'), 
                     'Agent-Fixit Job'), 
             tgt_crit_cas.job_type, tgt_crit_cas.job_owner, tgt_crit_cas.job_name
        FROM mgmt_targets t, 
             mgmt_metrics m, 
             (select target_type, object_guid, category_name
                   from mgmt_category_map catm
                  where class_name = 'Functional'
                    and object_type = 1) func_cats,
             mgmt_policy_assoc pa,
             mgmt_policy_assoc_cfg pac,
             mgmt_policy_assoc_cfg_params pacp,
             (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                     j.job_owner, j.job_name
                FROM mgmt_job j, mgmt_corrective_action ca
               WHERE j.job_id = ca.job_id
                 AND j.is_corrective_action = 1
                 AND ca.ca_scope = 1) tgt_warn_cas,
             (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                     j.job_owner, j.job_name
                FROM mgmt_job j, 
                     mgmt_corrective_action ca
               WHERE j.job_id = ca.job_id
                 AND j.is_corrective_action = 1
                 AND ca.ca_scope = 1) tgt_crit_cas
       WHERE t.target_type = m.target_type
         AND t.type_meta_ver = m.type_meta_ver
	 AND m.num_keys < 2
         AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
         AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
         AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
         AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
         AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
         AND pa.object_guid = t.target_guid
         AND pa.policy_guid = m.metric_guid
	 AND pa.policy_type = 1
         AND pa.object_guid = pac.object_guid
         AND pa.policy_guid = pac.policy_guid
         AND pa.coll_name = pac.coll_name
         AND pac.object_guid = pacp.object_guid
         AND pac.policy_guid = pacp.policy_guid
         AND pac.coll_name = pacp.coll_name
         ANd pac.key_value = pacp.key_value
         AND pac.key_operator = pacp.key_operator
         AND pa.object_type = 2
         AND pacp.param_name = ' ' 
         AND func_cats.object_guid (+) = m.metric_guid
         AND tgt_warn_cas.job_id (+) = pac.warn_action_job_id 
         AND tgt_warn_cas.ca_target_guid (+) = pac.object_guid 
         AND tgt_crit_cas.job_id (+) = pac.crit_action_job_id 
         AND tgt_crit_cas.ca_target_guid (+) = pac.object_guid
 UNION ALL 
      SELECT 
      t.target_name, t.target_type, t.target_guid, m.metric_name, 
          m.metric_column, m.metric_guid, pa.coll_name, 
	  func_cats.category_name, k.key_part1_value, k.key_part2_value, 
	  k.key_part3_value, k.key_part4_value, k.key_part5_value,
	  pac.key_operator, pac.has_active_baseline, pac.prevent_override, 
	  pac.condition_operator, pacp.warn_threshold, 
	  pac.condition_operator, pacp.crit_threshold, 
	  pac.num_occurrences, 
	  DECODE(TRIM(pac.fixit_job), NULL, 
	       DECODE(tgt_warn_cas.job_type, NULL, 'No-action', 
		          'Corrective-Action'), 'Agent-Fixit Job'), 
		  tgt_warn_cas.job_type, tgt_warn_cas.job_owner, tgt_warn_cas.job_name,
	  DECODE(TRIM(pac.fixit_job), NULL, 
	       DECODE(tgt_warn_cas.job_type, NULL, 'No-action', 
		          'Corrective-Action'), 'Agent-Fixit Job'), 
		  tgt_crit_cas.job_type, tgt_crit_cas.job_owner, tgt_crit_cas.job_name
        FROM mgmt_targets t, 
             mgmt_metrics m, 
             mgmt_metrics_composite_keys k,
             (SELECT target_type, object_guid, category_name
                FROM mgmt_category_map catm
               WHERE class_name = 'Functional'
                 AND object_type = 1) func_cats,
             mgmt_policy_assoc pa,
             mgmt_policy_assoc_cfg pac,
             mgmt_policy_assoc_cfg_params pacp,
             (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
			         j.job_owner, j.job_name
                FROM mgmt_job j, 
                     mgmt_corrective_action ca
               WHERE j.job_id = ca.job_id
                 AND j.is_corrective_action = 1
                 AND ca.ca_scope = 1) tgt_warn_cas,
             (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                     j.job_owner, j.job_name
                FROM mgmt_job j, 
                     mgmt_corrective_action ca
               WHERE j.job_id = ca.job_id
                 AND j.is_corrective_action = 1
                 AND ca.ca_scope = 1) tgt_crit_cas
       WHERE t.target_type = m.target_type
         AND t.type_meta_ver = m.type_meta_ver
         AND m.num_keys > 1
         AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
         AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
         AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
         AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
         AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
         AND pa.object_guid = t.target_guid
         AND pa.policy_guid = m.metric_guid
	 AND pa.policy_type = 1
         AND pa.object_type = 2
         AND pa.object_guid = pac.object_guid
         AND pa.policy_guid = pac.policy_guid
         AND pa.coll_name = pac.coll_name
         AND pac.object_guid = pacp.object_guid
         AND pac.policy_guid = pacp.policy_guid
         AND pac.coll_name = pacp.coll_name
         ANd pac.key_value = pacp.key_value
         AND pac.key_operator = pacp.key_operator
         AND pacp.param_name = ' ' 
         AND func_cats.object_guid (+) = m.metric_guid
         AND tgt_warn_cas.job_id (+) = pac.warn_action_job_id 
         AND tgt_warn_cas.ca_target_guid (+) = pac.object_guid 
         AND tgt_crit_cas.job_id (+) = pac.crit_action_job_id 
         AND tgt_crit_cas.ca_target_guid (+) = pac.object_guid
         AND k.target_guid = t.target_guid
         AND pac.key_value = k.composite_key
    WITH READ ONLY;
    
-- View Name : MGMT$METRIC_CATEGORIES
-- Description : Associates a metric name/column with a metric category 
-- class/name
CREATE OR REPLACE VIEW MGMT$METRIC_CATEGORIES
      (target_type, type_version, metric_name, metric_column,metric_guid,
       metric_class_name,metric_category_name,metric_category_nlsid)
    AS
      SELECT DISTINCT c.target_type, c.type_meta_ver, 
             m.metric_name, m.metric_column, m.metric_guid,c.class_name,
             c.category_name,cg.category_name_nlsid
        FROM mgmt_metrics m,
             mgmt_category_map c,
             mgmt_categories cg,
			 mgmt_targets t
       WHERE c.object_type = 1
         AND c.object_guid = m.metric_guid
         AND c.type_meta_ver = m.type_meta_ver         
         AND c.category_name = cg.category_name
         AND c.class_name = cg.class_name 
         AND t.target_type = c.target_type
         AND t.target_type = m.target_type
         AND t.type_meta_ver = m.type_meta_ver
         AND (t.category_prop_1 = m.category_prop_1
			  or m.category_prop_1 = ' ')
         AND (t.category_prop_2 = m.category_prop_2 
		      or m.category_prop_2 = ' ') 
         AND (t.category_prop_3 = m.category_prop_3 
		      or m.category_prop_3 = ' ') 
         AND (t.category_prop_4 = m.category_prop_4 
		      or m.category_prop_4 = ' ') 
         AND (t.category_prop_5 = m.category_prop_5 
		      or m.category_prop_5 = ' ') 
    WITH READ ONLY;

-- View Name : MGMT$TARGET_COMPOSITE
-- Description : Target Composite details
-- NOTE: The current view definition assumes composite relationships are 
-- consistent across type version and type qualifiers.
  CREATE OR REPLACE VIEW mgmt$target_composite
         (composite_name, composite_type, member_target_name,
          member_target_type, association)
      AS
        SELECT
            ct.target_name, ct.target_type,
            t.target_name, t.target_type,' ' 
          FROM 
            mgmt_target_assocs c,
            mgmt_targets t,
            mgmt_targets ct,
            mgmt_target_assoc_defs d
          WHERE d.assoc_guid = c.assoc_guid
            AND ct.target_guid = c.source_target_guid
            AND t.target_guid = c.assoc_target_guid
            AND d.assoc_def_name = 'contains'
            AND d.scope_target_type = ' '
    WITH READ ONLY;

COMMENT ON TABLE MGMT$TARGET_COMPOSITE IS
'The MGMT$TARGET_COMPOSITE view has been deprecated. Please consult 
the documentation for the MGMT$TARGET_MEMBERS and MGMT$TARGET_FLAT_MEMBERS 
views for alternatives.';

CREATE OR REPLACE VIEW MGMT$TARGET_MEMBERS
       (aggregate_target_name, aggregate_target_type,
        aggregate_target_guid, member_target_name,
        member_target_type, member_target_guid)
       AS
         SELECT
             t1.target_name, t1.target_type, t1.target_guid,
             t2.target_name, t2.target_type, t2.target_guid
           FROM
             mgmt_target_assocs a,
             mgmt_targets t1,
             mgmt_targets t2
           WHERE a.assoc_guid = hextoraw('CC038C4568AE2A331C948E492A0401DF')
             AND a.source_target_guid = t1.target_guid
             AND a.assoc_target_guid  = t2.target_guid
   WITH READ ONLY;

CREATE OR REPLACE VIEW MGMT$TARGET_FLAT_MEMBERS 
       (aggregate_target_name, aggregate_target_type,
	    aggregate_target_guid, member_target_name, 
		member_target_type, member_target_guid)
	   AS
         SELECT 
		     t1.target_name, t1.target_type,
             t1.target_guid, t2.target_name,
             t2.target_type, t2.target_guid
        FROM  mgmt_flat_target_assoc a,
              mgmt_targets t1,
              mgmt_targets t2
        WHERE  is_membership = 1
          AND  t1.target_guid = a.source_target_guid
          AND  t2.target_guid = a.assoc_target_guid
   WITH READ ONLY;

-- View Name : MGMT$GROUP_FLAT_MEMBERSHIPS
-- Description : Flattened target memberships table
-- NOTE: The current view definition assumes composite relationships are 
-- consistent across type version and type qualifiers.
CREATE OR REPLACE VIEW MGMT$GROUP_FLAT_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
   FROM  mgmt_flat_target_assoc a,
         mgmt_targets ct,
         mgmt_targets mt
  WHERE  is_membership = 1
    AND  ct.target_guid = a.source_target_guid
    AND  mt.target_guid = a.assoc_target_guid
  WITH READ ONLY;

COMMENT ON TABLE MGMT$GROUP_FLAT_MEMBERSHIPS IS
'The MGMT$GROUP_FLAT_MEMBERSHIPS view has been deprecated. Please consult 
the documentation for the MGMT$TARGET_MEMBERS and MGMT$TARGET_FLAT_MEMBERS 
views for alternatives.';

-- View Name : MGMT$GROUP_DERIVED_MEMBERSHIPS
-- Description : Flattened target memberships table containing
--               members derived from a group.
-- NOTE: The current view definition assumes composite relationships are 
-- consistent across type version and type qualifiers.
CREATE OR REPLACE VIEW MGMT$GROUP_DERIVED_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
   FROM  mgmt_flat_target_assoc assoc,
         mgmt_targets ct,
         mgmt_targets mt
  WHERE  assoc.is_membership = 1
    AND  ct.target_guid = assoc.source_target_guid
    AND  mt.target_guid = assoc.assoc_target_guid
     AND  EXISTS
         ( SELECT 1     
             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 assoc.source_target_guid = f2.source_target_guid
              AND assoc.assoc_target_guid = m.assoc_target_guid)
  WITH READ ONLY;

COMMENT ON TABLE MGMT$GROUP_DERIVED_MEMBERSHIPS IS
'The MGMT$GROUP_DERIVED_MEMBERSHIPS view has been deprecated. Please consult 
the documentation for the MGMT$TARGET_MEMBERS and MGMT$TARGET_FLAT_MEMBERS 
views for alternatives.';

  CREATE OR REPLACE VIEW mgmt$alert_current
       (target_name, target_type, target_guid, violation_guid,
        metric_name, metric_column, metric_guid,metric_label, column_label, 
        key_value, key_value2, key_value3, key_value4, key_value5,
        collection_timestamp, alert_state, violation_level, violation_type, message,
        message_nlsid, message_params, action_message, action_message_nlsid,
        action_message_params, type_display_name)
      AS
        SELECT        
            t.target_name, t.target_type, t.target_guid, c.violation_guid,
            m.metric_name, m.metric_column,c.policy_guid,
            m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 c.key_value
              END,
            c.key_value, null, null, null, null,
            c.collection_timestamp,
            DECODE (c.violation_level, 20, 'Warning', 25, 'Critical', 'Unknown'),
            c.violation_level,
            DECODE (c.violation_type, 0, 'Threshold Violation', 
                                      1, 'Availability',
                                      2, 'Resource',
                                      3, 'Policy Violation',
                                         'Unknown'),
            c.message,c.message_nlsid,c.message_params,c.action_message,
            c.action_message_nlsid,c.action_message_params, 
            tt.type_display_name
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_target_types tt,
            mgmt_current_violation c
          WHERE t.target_guid = c.target_guid
            AND m.metric_guid = c.policy_guid
            and tt.target_type = t.target_type
            and m.num_keys < 2
            and c.violation_type IN (0,1,2)
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid, c.violation_guid,
            m.metric_name, m.metric_column,c.policy_guid,
            m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            k.key_part1_value,  k.key_part2_value,  k.key_part3_value,
            k.key_part4_value,  k.key_part5_value,
            c.collection_timestamp,
            DECODE (c.violation_level, 20, 'Warning', 25, 'Critical', 'Unknown'),
            c.violation_level,
            DECODE (c.violation_type, 0, 'Threshold Violation', 
                                      1, 'Availability',
                                      2, 'Resource',
                                      3, 'Policy Violation',
                                         'Unknown'),
            c.message,c.message_nlsid,c.message_params,c.action_message,
            c.action_message_nlsid,c.action_message_params,
            tt.type_display_name
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_target_types tt,
            mgmt_current_violation c,
            mgmt_metrics_composite_keys k
          WHERE t.target_guid = c.target_guid
            and m.metric_guid = c.policy_guid
            and tt.target_type = t.target_type
            and t.target_type = m.target_type
            and m.num_keys > 1
            and c.violation_type IN (0,1,2)
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and c.target_guid = k.target_guid
            and c.key_value = k.composite_key
    WITH READ ONLY;
    
  CREATE OR REPLACE VIEW mgmt$availability_current
       (target_name, target_type, target_guid, start_timestamp, 
	    availability_status, type_display_name)
      AS
        SELECT        
            t.target_name, t.target_type, t.target_guid, 
			v.start_collection_timestamp, 
            DECODE (v.current_status, 0, 'Target Down', 1, 'Target Up', 
                                      2, 'Metric Error', 
                                      3, 'Agent Down', 4, 'Unreachable',
                                      5, 'Blackout', 6, 'Pending/Unknown'),
			tt.type_display_name
          FROM
            mgmt_targets t,
			mgmt_target_types tt,
            mgmt_current_availability v
          WHERE t.target_guid = v.target_guid
		    AND tt.target_type = t.target_type
    WITH READ ONLY;


  CREATE OR REPLACE VIEW mgmt$availability_history
       (target_name, target_type, target_guid, start_timestamp, end_timestamp, 
        availability_status,severity_guid)
      AS
        SELECT        
            t.target_name, t.target_type, t.target_guid, 
			v.start_collection_timestamp, v.end_collection_timestamp,
            DECODE (v.current_status, 0, 'Target Down', 1, 'Target Up', 
                                      2, 'Metric Error', 
                                      3, 'Agent Down', 4, 'Unreachable',
                                      5, 'Blackout', 6, 'Pending/Unknown'),
            v.severity_guid	
          FROM
            mgmt_targets t,
            mgmt_availability v
          WHERE t.target_guid = v.target_guid
    WITH READ ONLY;

  CREATE OR REPLACE VIEW mgmt$alert_history
       (target_name, target_type, target_guid, violation_guid,violation_level,
        cycle_guid,
        metric_name, metric_column, metric_guid,metric_label, column_label, 
        key_value, key_value2, key_value3, key_value4, key_value5,
        collection_timestamp, alert_state, alert_duration, message,
        message_nlsid,message_params,action_message,action_message_nlsid,
        action_message_params,violation_type, type_display_name, acknowledged, acknowledged_by)
      AS
        SELECT        
            t.target_name, t.target_type, t.target_guid, s.violation_guid,
            s.violation_level,s.cycle_guid,
            m.metric_name, m.metric_column,s.policy_guid,
            m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 s.key_value
              END,
            s.key_value, null, null, null, null,
            s.collection_timestamp,
            DECODE (s.violation_level, 15, 'Clear', 20, 'Warning', 25, 'Critical', 'Unknown'), 
            s.violation_duration, s.message,s.message_nlsid,s.message_params,
            s.action_message,s.action_message_nlsid,s.action_message_params,
            DECODE (s.violation_type, 0, 'Threshold Violation', 
                                      1, 'Availability',
                                      2, 'Resource',
                                      3, 'Policy Violation',
                                         'Unknown'),
            tt.type_display_name,
            s.acknowledged, 
            s.acknowledged_by
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_violations s,
            mgmt_target_types tt
          WHERE t.target_guid = s.target_guid
            and m.metric_guid = s.policy_guid
            and t.target_type = m.target_type
            and tt.target_type = t.target_type
            and t.type_meta_ver = m.type_meta_ver
            and m.num_keys < 2
            and s.violation_type IN (0,1,2)
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid, s.violation_guid,s.violation_level,s.cycle_guid,
            m.metric_name, m.metric_column,s.policy_guid,
            m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            k.key_part1_value,  k.key_part2_value,  k.key_part3_value,
            k.key_part4_value,  k.key_part5_value,
            s.collection_timestamp,
            DECODE (s.violation_level, 15, 'Clear', 20, 'Warning', 25, 'Critical', 'Unknown'), 
            s.violation_duration, s.message,s.message_nlsid,s.message_params,
            s.action_message,s.action_message_nlsid,s.action_message_params,
            DECODE (s.violation_type, 0, 'Threshold Violation', 
                                      1, 'Availability',
                                      2, 'Resource',
                                      3, 'Policy Violation',
                                          'Unknown'),
            tt.type_display_name,
            s.acknowledged, 
            s.acknowledged_by
          FROM
            mgmt_targets t,
            mgmt_metrics m,
                        mgmt_target_types tt,
            mgmt_violations s,
                        mgmt_metrics_composite_keys k
          WHERE t.target_guid = s.target_guid
            and m.metric_guid = s.policy_guid
            and t.target_type = m.target_type
            and tt.target_type = t.target_type
            and t.type_meta_ver = m.type_meta_ver
            and m.num_keys > 1
            and s.violation_type IN (0,1,2)
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and s.target_guid = k.target_guid
            and s.key_value = k.composite_key
    WITH READ ONLY;

  CREATE OR REPLACE VIEW mgmt$metric_details
       (target_name, target_type, target_guid,
	    metric_name, metric_type, metric_column,metric_guid, 
        metric_label, column_label, collection_timestamp, value,
        key_value, key_value2,  key_value3,
        key_value4, key_value5) AS
        SELECT
            t.target_name, t.target_type, t.target_guid, 
            m.metric_name, m.metric_type, m.metric_column,r.metric_guid,
            m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 key_value
              END,
            r.collection_timestamp,
            to_char(r.value),
            key_value, null, null, null, null
         FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_metrics_raw r
          WHERE t.target_guid = r.target_guid
            AND m.metric_guid = r.metric_guid
            and m.num_keys < 2
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_type, m.metric_column,r.metric_guid,
            m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 key_value
              END,
            r.collection_timestamp,
            r.string_value,
            key_value, null, null, null, null
         FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_string_metric_history r
          WHERE t.target_guid = r.target_guid
            AND m.metric_guid = r.metric_guid
            and m.num_keys < 2
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid, 
            m.metric_name, m.metric_type, m.metric_column,r.metric_guid,
            m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            r.collection_timestamp,
            NVL(r.string_value, to_char(r.value)),
            k.key_part1_value,  k.key_part2_value,  k.key_part3_value,
            k.key_part4_value,  k.key_part5_value
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_metrics_raw r,
            mgmt_metrics_composite_keys k
          WHERE t.target_guid = r.target_guid
            AND m.metric_guid = r.metric_guid
            and m.num_keys > 1
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and r.target_guid = k.target_guid
            and r.key_value = k.composite_key
     UNION ALL 
        SELECT
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_type, m.metric_column,r.metric_guid,
            m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            r.collection_timestamp,
            r.string_value,
            k.key_part1_value,  k.key_part2_value,  k.key_part3_value,
            k.key_part4_value,  k.key_part5_value
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_string_metric_history r,
            mgmt_metrics_composite_keys k
          WHERE t.target_guid = r.target_guid
            AND m.metric_guid = r.metric_guid
            and m.num_keys > 1
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and r.target_guid = k.target_guid
            and r.key_value = k.composite_key
    WITH READ ONLY;

  CREATE OR REPLACE VIEW mgmt$metric_current
       (target_name, target_type, target_guid,
        metric_name, metric_type, metric_column,metric_guid, 
        metric_label, column_label, collection_timestamp, value,
        key_value, key_value2,  key_value3,
        key_value4, key_value5) AS
        SELECT
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_type, m.metric_column,c.metric_guid,
            m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 key_value
              END,
            c.collection_timestamp,
            NVL(c.string_value, to_char(c.value)),
            key_value, null, null, null, null
         FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_current_metrics c
          WHERE t.target_guid = c.target_guid
            AND m.metric_guid = c.metric_guid
            and m.num_keys < 2
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_type, m.metric_column,c.metric_guid,
            m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            c.collection_timestamp,
            NVL(c.string_value, to_char(c.value)),
            k.key_part1_value,  k.key_part2_value,  k.key_part3_value,
            k.key_part4_value,  k.key_part5_value
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_current_metrics c,
            mgmt_metrics_composite_keys k
          WHERE t.target_guid = c.target_guid
            AND m.metric_guid = c.metric_guid
            and m.num_keys > 1
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and c.target_guid = k.target_guid
            and c.key_value = k.composite_key
    WITH READ ONLY;

  CREATE OR REPLACE VIEW mgmt$metric_hourly
       (target_name, target_type, target_guid,
        metric_name, metric_column, metric_guid,metric_label, column_label,
        key_value, key_value2, key_value3, key_value4, key_value5, rollup_timestamp,
        sample_count, average, minimum, maximum, standard_deviation)
      AS
        SELECT
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_column,h.metric_guid,
            m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 h.key_value
              END,
            h.key_value, null, null, null, null,
            h.rollup_timestamp, h.sample_count, h.value_average, 
            h.value_minimum, h.value_maximum, h.value_sdev
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_metrics_1hour h
          WHERE t.target_guid = h.target_guid
            AND m.metric_guid = h.metric_guid
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and m.num_keys < 2
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_column,h.metric_guid,
            m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            k.key_part1_value, k.key_part2_value, k.key_part3_value, k.key_part4_value,
            k.key_part5_value, h.rollup_timestamp, h.sample_count, 
            h.value_average, h.value_minimum, h.value_maximum, h.value_sdev
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_metrics_1hour h,
			mgmt_metrics_composite_keys k
          WHERE t.target_guid = h.target_guid
            AND m.metric_guid = h.metric_guid
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and m.num_keys > 1
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and h.target_guid = k.target_guid
            and h.key_value = k.composite_key
    WITH READ ONLY;

  CREATE OR REPLACE VIEW mgmt$metric_daily
       (target_name, target_type, target_guid,
        metric_name, metric_column, metric_guid,
        metric_label, column_label, key_value, key_value2,
		key_value3, key_value4, key_value5, rollup_timestamp,
        sample_count, average, minimum, maximum, standard_deviation)
      AS
        SELECT
            t.target_name, t.target_type, t.target_guid, m.metric_name,
            m.metric_column, d.metric_guid,m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 key_value
              END,
            d.key_value, null, null, null, null,
            d.rollup_timestamp, d.sample_count, d.value_average, 
            d.value_minimum, d.value_maximum, d.value_sdev
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_metrics_1day d
          WHERE t.target_guid = d.target_guid
            AND m.metric_guid = d.metric_guid
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and m.num_keys < 2
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
     UNION ALL
        SELECT
            t.target_name, t.target_type, t.target_guid, m.metric_name,
			m.metric_column, d.metric_guid,m.metric_label, 
                        CASE
                            WHEN m.is_transposed = 0 THEN
                               m.column_label
                            ELSE
                               k.key_part1_value
                            END,
			k.key_part1_value, k.key_part2_value, k.key_part3_value, 
			k.key_part4_value, k.key_part5_value, d.rollup_timestamp, 
			d.sample_count, d.value_average, d.value_minimum, 
			d.value_maximum, d.value_sdev
          FROM
            mgmt_targets t,
            mgmt_metrics m,
            mgmt_metrics_1day d,
			mgmt_metrics_composite_keys k
          WHERE t.target_guid = d.target_guid
            AND m.metric_guid = d.metric_guid
            and t.target_type = m.target_type
            and t.type_meta_ver = m.type_meta_ver
            and m.num_keys > 1
            and (t.category_prop_1 = m.category_prop_1
                 or m.category_prop_1 = ' ')
            and (t.category_prop_2 = m.category_prop_2
                 or m.category_prop_2 = ' ')
            and (t.category_prop_3 = m.category_prop_3
                 or m.category_prop_3 = ' ')
            and (t.category_prop_4 = m.category_prop_4
                 or m.category_prop_4 = ' ')
            and (t.category_prop_5 = m.category_prop_5
                 or m.category_prop_5 = ' ')
            and d.target_guid = k.target_guid
            and d.key_value = k.composite_key
    WITH READ ONLY;


-- View Name : MGMT$DELTA_COMPONENTS
-- Description : Returns Oracle Software component changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_COMPONENTS AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as home_path, 
    k3.value as component_name, 
    k4.value as base_version, 
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as delta_values 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_id_values k4, 
  mgmt_delta_entry e, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_INV_COMPONENTS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'CONTAINER_LOCATION' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'NAME' and 
  i.row_guid = k4.delta_ids_guid and 
  k4.name = 'VERSION' and 
  i.row_guid = e.row_guid and 
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_COMPONENTS IS
  'The MGMT$DELTA_COMPONENTS has been deprecated.';

-- View Name : MGMT$DELTA_COMPONENT_DETAILS
-- Description : Returns Oracle Software component changes in detail
CREATE OR REPLACE VIEW MGMT$DELTA_COMPONENT_DETAILS AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as home_path, 
    k3.value as component_name, 
    k4.value as base_version, 
    v.name as attribute_name, 
    v.old_value as old_value, 
    v.value as new_value 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_id_values k4, 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values v, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_INV_COMPONENTS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'CONTAINER_LOCATION' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'NAME' and 
  i.row_guid = k4.delta_ids_guid and 
  k4.name = 'VERSION' and 
  i.row_guid = e.row_guid and 
  v.delta_entry_guid(+) = e.delta_entry_guid and 
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_COMPONENT_DETAILS IS
  'The view MGMT$DELTA_COMPONENT_DETAILS has been deprecated.';

CREATE OR REPLACE VIEW MGMT$OS_PATCHES AS 
select
  t.target_name as host,
  o.name || ' ' || o.base_version || ' ' || o.update_level || '(' || o.address_length_in_bits || ')' as os_extended,
  o.name || ' ' || o.base_version as os,
  p.name as patch
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_hc_os_summary o,
  mgmt_hc_os_components p
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = o.snapshot_guid and
  s.snapshot_guid = p.snapshot_guid and
  p.type = 'Patch';

-- View Name : MGMT$DELTA_ONEOFF_PATCHES
-- Description : Returns Oracle Software interim patch changes
CREATE OR REPLACE VIEW MGMT$DELTA_ONEOFF_PATCHES AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as home_name, 
    k3.value as patch_id, 
    decode(v.value, null, v.old_value, v.value) as patch_timestamp 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values v, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_INV_PATCHES' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'CONTAINER_LOCATION' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'ID' and 
  i.row_guid = e.row_guid and 
  v.delta_entry_guid(+) = e.delta_entry_guid and 
  v.name(+) = 'TIMESTAMP' and
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_ONEOFF_PATCHES IS
  'The view MGMT$DELTA_ONEOFF_PATCHES has been deprecated.';

-- View Name : MGMT$DELTA_OS_COMPONENTS
-- Description : Returns Operating System component changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_OS_COMPONENTS AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as name, 
    k3.value as type, 
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as delta_values 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_entry e, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_OS_COMPONENTS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'NAME' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'TYPE' and 
  i.row_guid = e.row_guid and 
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_OS_COMPONENTS IS
  'The view MGMT$DELTA_OS_COMPONENTS has been deprecated.';

-- View Name : MGMT$DELTA_OS_COMP_DETAILS
-- Description : Returns Operating System component changes in detail
CREATE OR REPLACE VIEW MGMT$DELTA_OS_COMP_DETAILS AS 
select
    e.delta_time as delta_time,
    e.operation as operation,
    s.new_left_target_name as host_name,
    k2.value as name,
    k3.value as type,
    v.name as attribute_name,
    v.old_value as old_value,
    v.value as new_value
from
  mgmt_delta_ids i,
  mgmt_delta_id_values k2,
  mgmt_delta_id_values k3,
  mgmt_delta_entry e,
  mgmt_delta_entry_values v,
  mgmt_delta_snap s,
  mgmt_targets t 
where
  i.collection_type = 'ECM$HIST_OS_COMPONENTS' and
  i.row_guid = k2.delta_ids_guid and
  k2.name = 'NAME' and
  i.row_guid = k3.delta_ids_guid and
  k3.name = 'TYPE' and
  i.row_guid = e.row_guid and
  v.delta_entry_guid(+) = e.delta_entry_guid and
  v.name(+) = 'VERSION' and
  e.operation <> 'UPDATE' and
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration'
UNION
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as name, 
    k3.value as type, 
    v.name as attribute_name, 
    v.old_value as old_value, 
    v.value as new_value 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values v, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_OS_COMPONENTS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'NAME' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'TYPE' and 
  i.row_guid = e.row_guid and 
  v.delta_entry_guid(+) = e.delta_entry_guid and 
  e.operation = 'UPDATE' and
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';


COMMENT ON TABLE MGMT$DELTA_OS_COMP_DETAILS IS
  'The view MGMT$DELTA_OS_COMP_DETAILS has been deprecated.';

-- View Name : MGMT$DELTA_OS_KERNEL_PARAMS
-- Description : Returns Operating System properties changes in detail
CREATE OR REPLACE VIEW MGMT$DELTA_OS_KERNEL_PARAMS AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k3.value as param_source,
    k2.value as param_name, 
    d.old_value as old_value, 
    d.value as new_value,
    ecm_util.GET_GENERIC_VALS_DATATYPE( d.value ) as datatype
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k3, 
  mgmt_delta_id_values k2, 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values d, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_OS_PROPERTIES' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'TYPE' and
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'NAME' and 
  i.row_guid = e.row_guid and 
  d.DELTA_ENTRY_GUID(+) = e.DELTA_ENTRY_GUID and 
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_OS_KERNEL_PARAMS IS
  'The view MGMT$DELTA_OS_KERNEL_PARAMS has been deprecated.';

-- View Name : MGMT$DELTA_OS_SUMMARY
-- Description : Returns Operating System changes as summary
--CREATE OR REPLACE VIEW MGMT$DELTA_OS_SUMMARY AS 
--select 
--    e.delta_time as delta_time, 
--    e.operation as operation, 
--    s.new_left_target_name as host_name,
--    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as delta_values 
--from 
--  mgmt_delta_ids i, 
--  mgmt_delta_entry e, 
--  mgmt_delta_snap s,
--  mgmt_targets t 
--where 
--  i.collection_type = 'ECM$HIST_OS_SUMMARY' and 
--  i.row_guid = e.row_guid and   
--  t.target_name = s.new_left_target_name and 
--  t.target_type = 'host' and
--  e.delta_guid = s.delta_guid and
--  s.target_type = 'host' and
--  s.snapshot_type = 'host_configuration';

-- View Name : MGMT$DELTA_OS_SUMMARY_DETAILS
-- Description : Returns Operating System changes in detail
--CREATE OR REPLACE VIEW MGMT$DELTA_OS_SUMMARY_DETAILS AS
--select 
--    e.delta_time as delta_time, 
--    e.operation as operation, 
--    s.new_left_target_name as host_name,
--    d.name as attribute_name, 
--    d.old_value as old_value, 
--    d.value as new_value 
--from 
--  mgmt_delta_ids i, 
--  mgmt_delta_entry e, 
--  mgmt_delta_entry_values d, 
--  mgmt_delta_snap s,
--  mgmt_targets t 
--where 
--  i.collection_type = 'ECM$HIST_OS_SUMMARY' and 
--  i.row_guid = e.row_guid and 
--  d.DELTA_ENTRY_GUID(+) = e.DELTA_ENTRY_GUID and 
--  t.target_name = s.new_left_target_name and 
--  t.target_type = 'host' and
--  e.delta_guid = s.delta_guid and
--  s.target_type = 'host' and
--  s.snapshot_type = 'host_configuration';


-- View Name : MGMT$DELTA_PATCHSETS
-- Description : Returns Oracle Software patchset changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_PATCHSETS AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as home_name, 
    k3.value as patchset_name, 
    k4.value as version, 
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as delta_values 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_id_values k4, 
  mgmt_delta_entry e, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_INV_PATCHSETS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'CONTAINER_LOCATION' and
  i.row_guid = k3.delta_ids_guid and
  k3.name = 'NAME' and 
  i.row_guid = k4.delta_ids_guid and 
  k4.name = 'VERSION' and 
  i.row_guid = e.row_guid and 
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_PATCHSETS IS
  'The view MGMT$DELTA_PATCHSETS has been deprecated.';

-- View Name : MGMT$DELTA_PATCHSET_DETAILS
-- Description : Returns Oracle Software patchset changes in detail
CREATE OR REPLACE VIEW  MGMT$DELTA_PATCHSET_DETAILS AS 
select 
    e.delta_time as delta_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as home_name, 
    k3.value as patchset_name, 
    k4.value as version, 
    v.name as attribute_name, 
    v.old_value as old_value, 
    v.value as new_value 
from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_id_values k4, 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values v, 
  mgmt_delta_snap s,
  mgmt_targets t 
where 
  i.collection_type = 'ECM$HIST_INV_PATCHSETS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'CONTAINER_LOCATION' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'NAME' and 
  i.row_guid = k4.delta_ids_guid and 
  k4.name = 'VERSION' and 
  i.row_guid = e.row_guid and 
  v.delta_entry_guid(+) = e.delta_entry_guid and 
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';

COMMENT ON TABLE MGMT$DELTA_PATCHSET_DETAILS IS
  'The view MGMT$DELTA_PATCHSET_DETAILS has been deprecated.';


-- View Name : MGMT$DELTA_VIEW
-- Description : Returns All categories changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_VIEW AS 
select 
    MGMT_DELTA.GET_DELTA_KEY_DISPLAY_STRING( i.row_guid ) as key_path, 
    e.delta_time as delta_time, 
    e.operation as operation, 
    i.COLLECTION_TYPE as COLLECTION_TYPE, 
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as delta_values 
  from 
    mgmt_delta_entry e, 
    mgmt_delta_ids i,
    mgmt_targets t,
    mgmt_ecm_snapshot_metadata md,
    mgmt_ecm_snapshot_md_tables mdt,
    mgmt_delta_snap p
  where 
    i.row_guid = e.row_guid
    and p.delta_guid = e.delta_guid
    and p.new_left_target_name = t.target_name
    and p.target_type = t.target_type
    and p.delta_type = 'HISTORY'
    and p.target_type = md.target_type
    and p.snapshot_type = md.snapshot_type
    and md.kind = 'P'
    and md.history_ui_on = 'Y'
    and md.metadata_id = mdt.metadata_id
    and mdt.name = i.collection_type
    and mdt.history_ui_on = 'Y';

COMMENT ON TABLE mgmt$delta_view IS
  'The view MGMT$DELTA_VIEW has been deprecated.';

-- View Name : MGMT$DELTA_VIEW_DETAILS
-- Description : Returns All categories changes in detail
CREATE OR REPLACE VIEW MGMT$DELTA_VIEW_DETAILS AS 
select
    MGMT_DELTA.GET_DELTA_KEY_DISPLAY_STRING( i.row_guid ) as key_path, 
    e.delta_time as delta_time, 
    e.operation as operation, 
    i.COLLECTION_TYPE as COLLECTION_TYPE, 
    d.name as attribute_name, 
    d.value as new_value, 
    d.old_value as old_value 
  from 
    mgmt_delta_entry e, 
    mgmt_delta_ids i, 
    mgmt_delta_entry_values d,
    mgmt_targets t,
    mgmt_ecm_snapshot_metadata md,
    mgmt_ecm_snapshot_md_tables mdt,
    mgmt_delta_snap p
  where 
    i.row_guid = e.row_guid
    and e.delta_entry_guid = d.delta_entry_guid(+)
    and p.delta_guid = e.delta_guid
    and p.new_left_target_name = t.target_name
    and p.target_type = t.target_type
    and p.delta_type = 'HISTORY'
    and p.target_type = md.target_type
    and p.snapshot_type = md.snapshot_type
    and md.kind = 'P'
    and md.history_ui_on = 'Y'
    and md.metadata_id = mdt.metadata_id
    and mdt.name = i.collection_type
    and mdt.history_ui_on = 'Y'
    AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
          where mdc.metadata_id = md.metadata_id
            AND mdc.table_name = mdt.name
            AND mdc.name = d.name
            AND mdc.history_ui_on = 'Y')
      OR d.name is null);
    
 
COMMENT ON TABLE mgmt$delta_view_details IS
  'The view MGMT$DELTA_VIEW_DETAILS has been deprecated.';
  
CREATE OR REPLACE VIEW MGMT$OS_COMPONENTS AS 
select  
  t.target_name as host,  
  c.name,  
  c.type,  
  c.version,  
  c.description,
  c.installation_date,
  s.snapshot_guid 
from 
  mgmt_targets t, 
  mgmt_ecm_snapshot s, 
  mgmt_hc_os_components c 
where 
  t.target_type = 'host' and 
  t.target_name = s.target_name and 
  s.snapshot_type = 'host_configuration' and 
  s.target_type = 'host' and 
  s.is_current = 'Y' and 
  s.snapshot_guid = c.snapshot_guid;



CREATE OR REPLACE VIEW MGMT$OS_KERNEL_PARAMS AS 
select
  t.target_name as host,
  p.type as source,
  p.name as name,
  p.value as value,
  ecm_util.GET_GENERIC_VALS_DATATYPE( p.value ) as datatype,
  s.snapshot_guid
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_hc_os_properties p
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = p.snapshot_guid;

COMMENT ON TABLE mgmt$os_kernel_params IS
  'The view MGMT$OS_KERNEL_PARAMS has been deprecated.
   It has been replaced by MGMT$OS_PROPERTIES.';

CREATE OR REPLACE VIEW MGMT$OS_PROPERTIES AS 
select
  t.target_name as host,
  p.type as source,
  p.name as name,
  p.value as value,
  ecm_util.GET_GENERIC_VALS_DATATYPE( p.value ) as datatype,
  s.snapshot_guid
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_hc_os_properties p
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = p.snapshot_guid;



CREATE OR REPLACE VIEW MGMT$OS_SUMMARY AS 
select  
  t.target_name as host,  
  o.name,  
  o.vendor_name,  
  o.base_version,  
  o.update_level,  
  o.distributor_version, 
  o.max_swap_space_in_mb,
  s.snapshot_guid
from 
  mgmt_targets t, 
  mgmt_ecm_snapshot s,
  mgmt_hc_os_summary o 
where 
  t.target_type = 'host' and 
  t.target_name = s.target_name and 
  s.snapshot_type = 'host_configuration' and 
  s.target_type = 'host' and 
  s.is_current = 'Y' and 
  s.snapshot_guid = o.snapshot_guid;


CREATE OR REPLACE VIEW MGMT$SOFTWARE_COMPONENTS AS
SELECT
  comp.name as name,
  comp.external_name,
  comp.version as base_version,
  home.patchsets patchsets_in_home,
  decode(patch.version, NULL, comp.version, patch.version) as version,
  home.host_name,
  home.container_location as home_location,
  home.container_name as home_name,
  comp.description,
  comp.installer_version, comp.min_deinstaller_version,
  comp.timestamp as install_timestamp,
  comp.is_top_level,
  home.interim_patches interim_patches_in_home,
  home.bugs_fixed_by_interim_patches,
  home.snapshot_guid
FROM
  (SELECT s.target_name host_name, c.container_guid,
          c.container_location, c.container_name,
          ecm_util.patchsets_in_home(c.container_guid) patchsets,
          ecm_util.interim_patches_in_home(c.container_guid) interim_patches,
          ecm_util.fixed_bugs_in_home(c.container_guid) bugs_fixed_by_interim_patches,
          s.snapshot_guid
    FROM mgmt_inv_container c, mgmt_ecm_snapshot s, mgmt_targets t
    WHERE c.snapshot_guid = s.snapshot_guid
     AND s.is_current = 'Y'
     AND s.snapshot_type = 'host_configuration'
     AND t.target_name = s.target_name
     AND t.target_type = 'host'
  ) home,
  mgmt_inv_component comp,
  mgmt_inv_versioned_patch patch
WHERE home.container_guid = comp.container_guid
  AND comp.component_guid = patch.component_guid(+)
WITH READ ONLY;

CREATE OR REPLACE VIEW MGMT$SOFTWARE_COMPONENT_ONEOFF AS 
SELECT 
  s.target_name as HOST_NAME, 
  h.container_name as HOME_NAME,
  h.container_location as HOME_LOCATION, 
  c.name as COMPONENT_NAME, 
  c.external_name as COMPONENT_EXTERNAL_NAME,
  decode(vpatch.version, NULL, c.version, vpatch.version) as COMPONENT_VERSION,
  c.version as COMPONENT_BASE_VERSION, 
  p.ID as PATCH_ID,
  s.snapshot_guid
FROM 
  mgmt_ecm_snapshot s, 
  mgmt_inv_container h, 
  mgmt_inv_component c, 
  mgmt_inv_component_patch cp, 
  mgmt_inv_patch p,
  mgmt_inv_versioned_patch vpatch, 
  mgmt_targets t 
WHERE s.is_current = 'Y' 
      AND s.snapshot_type = 'host_configuration' 
      AND s.snapshot_guid = h.snapshot_guid 
      AND h.container_guid = c.container_guid 
      AND p.container_guid = h.container_guid 
      AND cp.component_guid = c.component_guid 
      AND cp.patch_guid = p.patch_guid 
      AND c.component_guid = vpatch.component_guid(+)
      AND t.target_name = s.target_name 
      AND t.target_type = 'host';


CREATE OR REPLACE VIEW MGMT$SOFTWARE_COMP_PATCHSET AS
SELECT 
  s.target_name as HOST_NAME, 
  h.container_name as HOME_NAME,
  h.container_location as home_location,
  c.name as component_name, 
  c.version as component_base_version,
  p.version as component_version,
  ps.name as patchset_name,
  ps.version as patchset_version,
  s.snapshot_guid
FROM
  mgmt_ecm_snapshot s,
  mgmt_inv_container h,
  mgmt_inv_versioned_patch p,
  mgmt_inv_patchset ps,
  mgmt_inv_component c,
  mgmt_targets t
WHERE s.is_current = 'Y'
  AND s.snapshot_type = 'host_configuration'
  AND s.snapshot_guid = h.snapshot_guid
  AND h.container_guid = c.container_guid
  AND p.component_guid = c.component_guid
  AND p.patchset_guid = ps.patchset_guid
  AND t.target_name = s.target_name
  AND t.target_type = 'host';


CREATE OR REPLACE VIEW MGMT$SOFTWARE_DEPENDENCIES AS
SELECT 
  s.target_name as host_name,
  cr.container_name as referencer_home_name, 
  cr.container_location as referencer_home_location, 
  er.name referencer_name, 
  er.version as referencer_base_version, 
  cd.container_name as referenced_home_name, 
  cd.container_location as referenced_home_location, 
  ed.name as referenced_name, 
  ed.version as referenced_base_version, 
  d.dependency_type,
  s.snapshot_guid
FROM 
  mgmt_inv_dependency_rule d, 
  mgmt_inv_component er, 
  mgmt_inv_component ed, 
  mgmt_inv_container cd, 
  mgmt_inv_container cr, 
  mgmt_ecm_snapshot s, 
  mgmt_targets t 
WHERE d.dependee_guid = ed.component_guid 
  AND ed.container_guid = cd.container_guid 
  AND cd.snapshot_guid = s.snapshot_guid 
  AND s.is_current = 'Y' 
  AND s.snapshot_type = 'host_configuration' 
  AND d.referencer_guid = er.component_guid 
  AND er.container_guid = cr.container_guid
  AND t.target_name = s.target_name 
  AND t.target_type = 'host';

COMMENT ON COLUMN mgmt$software_dependencies.dependency_type IS
  'The column mgmt$software_dependencies.dependency_type has been deprecated';

CREATE OR REPLACE VIEW MGMT$SOFTWARE_HOMES AS
SELECT 
  t.target_name as host_name, 
  c.container_name as home_name, 
  decode(c.container_type, 'O', 'ORACLE_HOME', 'A', 'APPL_TOP', 
        'I', 'INDEPENDENT', 'UNKNOWN') as home_type,
  c.container_location as home_location,
  s.snapshot_guid
FROM 
  mgmt_targets t, 
  mgmt_ecm_snapshot s, 
  mgmt_inv_container c 
WHERE t.target_type = 'host' 
  AND s.target_name = t.target_name 
  AND s.is_current = 'Y' 
  AND s.snapshot_type = 'host_configuration'
  AND s.snapshot_guid = c.snapshot_guid;

CREATE OR REPLACE VIEW MGMT$SOFTWARE_HOME_PROPERTIES AS
SELECT 
  t.target_name as host_name,
  c.container_name as home_name, 
  decode(c.container_type, 'O', 'ORACLE_HOME', 'A', 'APPL_TOP', 
        'I', 'INDEPENDENT', 'UNKNOWN') as home_type,
  c.container_location as home_location,
  p.property_name,
  p.property_value,
  s.snapshot_guid
FROM   
  mgmt_targets t, 
  mgmt_ecm_snapshot s,  
  mgmt_inv_container c,
  mgmt_inv_container_property p
WHERE t.target_type = 'host' 
  AND s.target_name = t.target_name
  AND s.is_current = 'Y' 
  AND s.snapshot_type = 'host_configuration'
  AND s.snapshot_guid = c.snapshot_guid
  AND c.container_guid = p.container_guid;

CREATE OR REPLACE VIEW MGMT$SOFTWARE_ONEOFF_PATCHES AS
SELECT
  s.target_name as HOST_NAME, 
  c.container_name as HOME_NAME,
  c.container_location as HOME_LOCATION,
  p.id as PATCH_ID, 
  p.timestamp as INSTALL_TIMESTAMP,
  p.description, p.is_rollbackable,
  s.snapshot_guid
FROM 
  mgmt_inv_container c, 
  mgmt_ecm_snapshot s, 
  mgmt_inv_patch p, 
  mgmt_targets t 
WHERE c.snapshot_guid = s.snapshot_guid 
  AND s.is_current = 'Y' 
  AND s.snapshot_type = 'host_configuration' 
  AND c.container_guid = p.container_guid 
  AND t.target_name = s.target_name 
  AND t.target_type = 'host';

CREATE OR REPLACE VIEW MGMT$SOFTWARE_PATCHSETS AS
SELECT
  s.target_name as host_name,
  c.container_name as home_name,
  c.container_location as home_location,
  p.name,
  p.version,
  p.description,
  p.external_name,
  p.installer_version,
  p.min_deinstaller_version,
  p.timestamp as install_timestamp,
  s.snapshot_guid
FROM
  mgmt_inv_container c,
  mgmt_ecm_snapshot s,
  mgmt_inv_patchset p,
  mgmt_targets t
WHERE c.snapshot_guid = s.snapshot_guid
  AND s.is_current = 'Y'
  AND s.snapshot_type = 'host_configuration'
  AND p.container_guid = c.container_guid
  AND t.target_name = s.target_name
  AND t.target_type = 'host';

CREATE OR REPLACE VIEW mgmt$target_components
       (target_name, target_type, target_guid, host_name, home_name, 
	    home_type, home_location,
        component_name, component_external_name, component_version, 
        component_base_version, is_top_level, snapshot_guid)
      AS
        SELECT
            t.target_name, t.target_type, t.target_guid, 
			ho.target_name, h.container_name,
            decode(h.container_type, 'O', 'ORACLE_HOME', 'A', 'APPL_TOP',
                                     'I', 'INDEPENDENT', 'UNKNOWN'),
            h.container_location, c.name, c.external_name,
            decode(vpatch.version, NULL, c.version, vpatch.version),
            c.version, c.is_top_level, s.snapshot_guid
          FROM
             mgmt_targets t,
             mgmt_targets ho,
             mgmt_target_properties p,
             mgmt_ecm_snapshot s,
             mgmt_inv_container h,
             mgmt_inv_component c,
             mgmt_inv_versioned_patch vpatch,
             mgmt_target_type_component_map m
          WHERE ho.target_type = 'host'
            AND ho.target_name = t.host_name
            AND t.target_guid = p.target_guid
            AND p.property_name = 'OracleHome'
            AND s.target_name = ho.target_name
            AND s.is_current = 'Y'
            AND s.snapshot_type = 'host_configuration'
            AND s.snapshot_guid = h.snapshot_guid
            AND h.container_guid = c.container_guid
            AND h.container_location = p.property_value
            AND c.name = m.component_name
            AND m.target_type = t.target_type
            AND c.component_guid = vpatch.component_guid(+);

CREATE OR REPLACE VIEW mgmt$alert_annotations
       (target_name, target_type, target_guid,
	metric_name, metric_column, metric_guid,metric_label, column_label, 
        key_value, key_value2,key_value3, key_value4, key_value5, 
        message, alert_state, violation_level, collection_timestamp, 
        annotation_message, annotation_timestamp, annotated_by,
		source_obj_guid, annotation_type)
       AS
        SELECT 
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_column, v.policy_guid,m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 v.key_value
              END,
            v.key_value, null, null, null, null, v.message, 
            DECODE (v.violation_level, 15, 'Clear', 
                                       20, 'Warning', 
                                       25, 'Critical', v.violation_level), 
            v.violation_level,
            v.collection_timestamp, a.message, a.timestamp,
			a.user_name, a.source_obj_guid,a.annotation_type
         FROM mgmt_annotation a, 
              mgmt_targets t, 
              mgmt_metrics m, 
              mgmt_violations v
         WHERE a.source_obj_type = 1
           AND v.violation_guid = a.source_obj_guid
           AND v.violation_type IN (0,1,2)
           AND m.metric_guid = v.policy_guid
           AND t.target_guid = v.target_guid
           AND m.target_type = t.target_type
           AND m.num_keys < 2
           AND m.type_meta_ver = t.type_meta_ver
           AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 =' ')
           AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 =' ')
           AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 =' ')
           AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 =' ')
           AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 =' ')
	UNION ALL
        SELECT 
            t.target_name, t.target_type, t.target_guid,
            m.metric_name, m.metric_column, v.policy_guid,m.metric_label, 
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 k.key_part1_value
              END,
            k.key_part1_value, k.key_part2_value, k.key_part3_value, 
            k.key_part4_value, k.key_part5_value, v.message,
            DECODE (v.violation_level, 15, 'Clear',
                                       20, 'Warning',
                                       25, 'Critical', v.violation_level),
            v.violation_level,
            v.collection_timestamp, a.message, a.timestamp,
			a.user_name, a.source_obj_guid, a.annotation_type
          FROM mgmt_annotation a, 
               mgmt_targets t, 
	       mgmt_metrics m, 
               mgmt_violations v, 
               mgmt_metrics_composite_keys k
         WHERE a.source_obj_type = 1
           AND v.violation_guid = a.source_obj_guid
           AND v.violation_type IN (0,1,2)
           AND m.metric_guid = v.policy_guid
           AND t.target_guid = v.target_guid
           AND m.target_type = t.target_type
       	   AND m.num_keys > 1
           AND m.type_meta_ver = t.type_meta_ver
           AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 =' ')
           AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 =' ')
           AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 =' ')
           AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 =' ')
           AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 =' ')
           AND v.target_guid = k.target_guid
           AND v.key_value = k.composite_key
    WITH READ ONLY;
  
  
CREATE OR REPLACE VIEW mgmt$policy_viol_annotations
       (target_name, target_type, target_guid,
        policy_name, policy_guid,description, key_value,
	key_value2, key_value3, key_value4, key_value5,
	message, violation_level, collection_timestamp,
	annotation_message, annotation_timestamp, annotated_by)
       AS
         SELECT 
	     t.target_name, t.target_type, t.target_guid,
             p.policy_name, v.policy_guid,p.description, 
             v.key_value, null, null, null, null, v.message, 
             DECODE(v.violation_level, 18, 'Informational',
                                       20, 'Warning',
                                       25, 'Critical', v.violation_level),
             v.collection_timestamp, 
             a.message, a.timestamp , a.user_name 
           FROM mgmt_annotation a, 
                mgmt_targets t, 
                mgmt_policies p, 
                mgmt_violations v
          WHERE a.source_obj_type = 1
            AND v.violation_guid = a.source_obj_guid
            AND v.violation_type = 3
            AND p.policy_guid = v.policy_guid
            AND t.target_guid = v.target_guid
            AND p.target_type = t.target_type
         UNION ALL
         SELECT 
	     t.target_name, t.target_type, t.target_guid,
             p.policy_name, v.policy_guid,p.description,
             k.key_part1_value, k.key_part2_value, k.key_part3_value, 
	     k.key_part4_value, k.key_part5_value, v.message, 
             DECODE(v.violation_level, 18, 'Informational',
                                       20, 'Warning',
                                       25, 'Critical', v.violation_level),
             v.collection_timestamp, 
             a.message, a.timestamp, a.user_name 
           FROM mgmt_annotation a, 
                mgmt_targets t, 
                mgmt_policies p, 
                mgmt_violations v,
                mgmt_metrics_composite_keys k
          WHERE a.source_obj_type = 1
            AND v.violation_guid = a.source_obj_guid
            AND v.violation_type = 3
            AND p.policy_guid = v.policy_guid
            AND t.target_guid = v.target_guid
            AND p.target_type = t.target_type
            AND k.target_guid = v.target_guid
            AND v.key_value = k.composite_key
   WITH READ ONLY;

  
CREATE OR REPLACE VIEW mgmt$job_annotations
       (job_name, job_owner, job_status, occurrence_timestamp,
	 annotation_message, annotation_timestamp, annotated_by)
       AS
         SELECT 
	     j.job_name, j.job_owner,
             decode(sc.newstate, 1, 'SCHEDULED', 2, 'EXECUTING', 
				 3, 'ABORTED', 4, 'FAILED', 
				 5, 'COMPLETED', 6, 'SUSPENDED', 
				 7, 'AGENT DOWN', 8, 'STOPPED', 
				 9, 'SUSPENDED/LOCK', 10, 'SUSPENDED/EVENT', 
				 11, 'SUSPENDED/BLACKOUT', 12, 'STOP PENDING', 
				 13, 'SUSPEND PENDING', 14, 'INACTIVE', 
				 15, 'QUEUED', 16, 'FAILED', 
				 17, 'WAITING', 18, 'SKIPPED', 
				 newstate),
             sc.occurred, a.message, a.timestamp, a.user_name 
           FROM mgmt_annotation a, 
                mgmt_job_state_changes sc,
                mgmt_job j
           WHERE a.source_obj_type = 3
             AND sc.state_change_guid = a.source_obj_guid
             AND j.job_id = sc.job_id
	WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$alert_notif_log 
       (target_name, target_type, target_guid,
	metric_name, metric_column, metric_guid,metric_label,
	column_label, key_value, key_value2, key_value3,
	key_value4, key_value5, message, alert_state, violation_level,
	collection_timestamp, delivery_message, delivery_timestamp,
	source_obj_guid)
       AS
	 SELECT 
	     t.target_name, t.target_type, t.target_guid,
             m.metric_name, m.metric_column, v.policy_guid,m.metric_label,
            CASE
              WHEN m.is_transposed = 0 THEN
                 m.column_label
              ELSE
                 v.key_value
              END,
             v.key_value, null, null, null, null,
             v.message,
             DECODE (v.violation_level, 15, 'Clear',
                                        20, 'Warning',
                                        25, 'Critical', v.violation_level),
             v.violation_level,
             v.collection_timestamp, n.message, n.timestamp,
			 n.source_obj_guid 
           FROM mgmt_notification_log n, 
                mgmt_targets t, 
                mgmt_metrics m, 
                mgmt_violations v
           WHERE n.source_obj_type = 1
             AND v.violation_guid = n.source_obj_guid
             AND v.violation_type IN (0,1,2)
             AND m.metric_guid = v.policy_guid
             AND t.target_guid = v.target_guid
             AND m.target_type = t.target_type
             AND m.num_keys < 2
             AND m.type_meta_ver = t.type_meta_ver
             AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
             AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
             AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
             AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
             AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ')
	  UNION ALL
	     SELECT 
	         t.target_name, t.target_type, t.target_guid,
                 m.metric_name, m.metric_column, v.policy_guid,m.metric_label,
                 CASE
                   WHEN m.is_transposed = 0 THEN
                     m.column_label
                   ELSE
                    k.key_part1_value
                   END,
                 k.key_part1_value, k.key_part2_value, 
	         k.key_part3_value, k.key_part4_value, k.key_part5_value,
	         v.message, 
                 DECODE (v.violation_level, 15, 'Clear',
                                            20, 'Warning',
                                            25, 'Critical', v.violation_level),
                v.violation_level,
             v.collection_timestamp, n.message, n.timestamp, n.source_obj_guid 
           FROM mgmt_notification_log n, 
                mgmt_targets t, 
		mgmt_metrics m, 
                mgmt_violations v,
		mgmt_metrics_composite_keys k
           WHERE n.source_obj_type = 1
             AND v.violation_guid = n.source_obj_guid
             AND v.violation_type IN (0,1,2)
             AND m.metric_guid = v.policy_guid
             AND t.target_guid = v.target_guid
             AND m.target_type = t.target_type
             AND m.num_keys > 1
             AND m.type_meta_ver = t.type_meta_ver
             AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
             AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
             AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
             AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
             AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ')
             AND v.target_guid = k.target_guid
             AND v.key_value = k.composite_key
    WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$policy_viol_notif_log
       (target_name, target_type, target_guid,
        policy_name, policy_guid,description, key_value,
	key_value2, key_value3, key_value4, key_value5,
	violation_message, violation_level, collection_timestamp,
	delivery_message, delivery_timestamp)
       AS
         SELECT 
	     t.target_name, t.target_type, t.target_guid,
             p.policy_name, v.policy_guid,p.description,
             v.key_value, null, null, null, null,
             v.message, 
             DECODE(v.violation_level, 18, 'Informational',
                                       20, 'Warning',
                                       25, 'Critical', v.violation_level),
             v.collection_timestamp,
             n.message, n.timestamp 
           FROM mgmt_notification_log n, 
	        mgmt_targets t, 
		mgmt_policies p, 
		mgmt_violations v
           WHERE n.source_obj_type = 1
             AND v.violation_guid = n.source_obj_guid
             AND v.violation_type = 3
             AND p.policy_guid = v.policy_guid
             AND t.target_guid = v.target_guid
             AND p.target_type = t.target_type
	  UNION ALL
         SELECT 
	     t.target_name, t.target_type, t.target_guid,
             p.policy_name, v.policy_guid,p.description,
             k.key_part1_value, k.key_part2_value, k.key_part3_value, 
             k.key_part4_value, k.key_part5_value,
	     v.message, 
             DECODE(v.violation_level, 18, 'Informational',
                                       20, 'Warning',
                                       25, 'Critical', v.violation_level),
             v.collection_timestamp,
             n.message, n.timestamp 
           FROM mgmt_notification_log n, 
	        mgmt_targets t, 
		mgmt_policies p, 
		mgmt_violations v,
		mgmt_metrics_composite_keys k
           WHERE n.source_obj_type = 1
             AND v.violation_guid = n.source_obj_guid
             AND v.violation_type = 3
             AND p.policy_guid = v.policy_guid
             AND t.target_guid = v.target_guid
             AND p.target_type = t.target_type
             AND k.target_guid = v.target_guid
             AND v.key_value = k.composite_key
   WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$job_notification_log 
       (job_name, job_owner, job_status, occurrence_timestamp,
        delivery_message, delivery_timestamp)
       AS
         SELECT 
	     j.job_name, j.job_owner,
             decode(sc.newstate, 1, 'SCHEDULED', 2, 'EXECUTING', 
				 3, 'ABORTED', 4, 'FAILED', 
				 5, 'COMPLETED', 6, 'SUSPENDED', 
				 7, 'AGENT DOWN', 8, 'STOPPED', 
				 9, 'SUSPENDED/LOCK', 10, 'SUSPENDED/EVENT', 
				 11, 'SUSPENDED/BLACKOUT', 12, 'STOP PENDING', 
				 13, 'SUSPEND PENDING', 14, 'INACTIVE', 
				 15, 'QUEUED', 16, 'FAILED', 
				 17, 'WAITING', 18, 'SKIPPED', 
				 newstate),
             sc.occurred, n.message, n.timestamp 
           FROM mgmt_notification_log n, 
	        mgmt_job_state_changes sc, 
		mgmt_job j
           WHERE n.source_obj_type = 3
             AND sc.state_change_guid = n.source_obj_guid
             AND j.job_id = sc.job_id
	WITH READ ONLY;

-- View Name : MGMT$DELTA_HARDWARE
-- Description : Returns Hardware changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_HARDWARE AS
select d.host_name, d.refresh_time, d.operation, 
    d.description 
from 
((select 
    s.new_left_target_name as host_name,
    e.delta_time as refresh_time, 
    case when e.operation = 'UPDATE' and d.old_value < d.value THEN 'INSERT' 
         when e.operation = 'UPDATE' and d.value < d.old_value THEN 'DELETE' 
         else e.operation end as operation, 
    'CPU: ' || mhz.value || ' mhz ' || impl.value  as description 
  from 
    mgmt_delta_entry e, 
    mgmt_delta_ids i, 
    mgmt_delta_entry_values d, 
    mgmt_delta_id_values mhz, 
    mgmt_delta_id_values impl, 
    mgmt_delta_snap s,
    mgmt_targets t 
  where 
    i.collection_type = 'ECM$HIST_CPU_DETAILS' and 
    i.row_guid = e.row_guid and 
    e.delta_entry_guid = d.delta_entry_guid(+) and 
    mhz.delta_ids_guid = i.row_guid and 
    mhz.name = 'FREQ_IN_MHZ' and 
    impl.delta_ids_guid = i.row_guid and 
    impl.name = 'IMPL' and 
    t.target_name = s.new_left_target_name and 
    t.target_type = 'host' and
    e.delta_guid = s.delta_guid and
    s.target_type = 'host' and
    s.snapshot_type = 'host_configuration')
UNION ALL 
(select 
    s.new_left_target_name as host_name,
    e.delta_time as delta_time, 
    case when e.operation = 'UPDATE' and d.old_value < d.value THEN 'INSERT' 
         when e.operation = 'UPDATE' and d.value < d.old_value THEN 'DELETE' 
         else e.operation end as operation, 
    'IOCARD: ' || mhz.value || ' mhz ' || name.value as description 
  from 
    mgmt_delta_entry e, 
    mgmt_delta_ids i, 
    mgmt_delta_entry_values d, 
    mgmt_delta_id_values mhz, 
    mgmt_delta_id_values name, 
    mgmt_delta_snap s,
    mgmt_targets t 
  where 
    i.collection_type = 'ECM$HIST_IOCARD_DETAILS' and 
    i.row_guid = e.row_guid and 
    e.delta_entry_guid = d.delta_entry_guid(+) and 
    mhz.delta_ids_guid = i.row_guid and 
    mhz.name = 'FREQ_IN_MHZ' and 
    name.delta_ids_guid = i.row_guid and 
    name.name = 'NAME' and 
    t.target_name = s.new_left_target_name and 
    t.target_type = 'host' and
    e.delta_guid = s.delta_guid and
    s.target_type = 'host' and
    s.snapshot_type = 'host_configuration')
UNION ALL 
(select 
    s.new_left_target_name as host_name,
    e.delta_time as delta_time, 
    e.operation as operation, 
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as description 
  from 
    mgmt_delta_entry e, 
    mgmt_delta_ids i, 
    mgmt_delta_snap s,
    mgmt_targets t 
  where 
    (i.collection_type = 'ECM$HIST_HARDWARE') and 
    i.row_guid = e.row_guid and 
    t.target_name = s.new_left_target_name and 
    t.target_type = 'host' and
    e.delta_guid = s.delta_guid and
    s.target_type = 'host' and
    s.snapshot_type = 'host_configuration')) d
/

COMMENT ON TABLE MGMT$DELTA_HARDWARE IS
  'The view MGMT$DELTA_HARDWARE has been deprecated.';

-- View Name : MGMT$DELTA_ORACLE_HOME
-- Description : Returns Oracle home container changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_ORACLE_HOME AS
(select  
  cd.host_name as host_name, 
  cd.DELTA_TIME as delta_time, 
  cd.HOME_PATH home_path, 
  cd.operation as operation, 
  'Product' as type, 
  case cd.operation when 'UPDATE' then cd.COMPONENT_NAME || ' ' || cd.base_version || ': ' ||  
       cd.delta_values else cd.COMPONENT_NAME || ' ' || cd.base_version end as description 
 from mgmt$delta_components cd) 
UNION ALL 
(select  
  p.host_name as host_name, 
  p.DELTA_TIME as delta_time, 
  p.HOME_name home_path, 
  p.operation as operation, 
  'Patch' as type, 
  p.patch_id as description 
 from mgmt$delta_oneoff_patches p) 
UNION ALL 
(select  
  cd.host_name as host_name, 
  cd.DELTA_TIME as delta_time, 
  cd.HOME_name home_path, 
  cd.operation as operation, 
  'Patchset' as type, 
  case cd.operation when 'UPDATE' then cd.patchset_NAME || ' ' || cd.version || ': ' ||  
       cd.delta_values else cd.patchset_NAME || ' ' || cd.version end as description 
 from mgmt$delta_patchsets cd);
 /

COMMENT ON TABLE MGMT$DELTA_ORACLE_HOME IS
  'The view MGMT$DELTA_ORACLE_HOME has been deprecated.';

-- View Name : MGMT$DELTA_VENDOR_SW
-- Description : Returns OS-Registered software changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_VENDOR_SW AS 
select host_name, refresh_time, operation, product, vendor,
    version, location, description
    from
(select
    s.new_left_target_name as host_name,
    e.delta_time as refresh_time,
    e.operation as operation,
    p.value as product,
    v.value as vendor,
    r.value as version,
    l.value as location,
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as description
  from
    mgmt_delta_entry e,
    mgmt_delta_ids i,
    mgmt_delta_id_values p,
    mgmt_delta_id_values v,
    mgmt_delta_id_values r,
    mgmt_delta_id_values l,
    mgmt_delta_snap s,
    mgmt_targets t 
  where
    i.collection_type = 'ECM$HIST_OS_REGISTERED_SW' and
    i.row_guid = e.row_guid and
    p.delta_ids_guid = i.row_guid and
    p.name = 'NAME' and
    v.delta_ids_guid = i.row_guid and
    v.name = 'VENDOR_NAME' and
    l.delta_ids_guid = i.row_guid and
    l.name = 'INSTALLED_LOCATION' and
    r.delta_ids_guid = i.row_guid and
    r.name = 'VERSION' and
    t.target_name = s.new_left_target_name and 
    t.target_type = 'host' and
    e.delta_guid = s.delta_guid and
    s.target_type = 'host' and
    s.snapshot_type = 'host_configuration')
UNION ALL
(select
    s.new_left_target_name as host_name,
    e.delta_time as refresh_time,
    'UPDATE' as operation,
    p.value as product,
    v.value as vendor,
    r.value as version,
    l.value as location,
    case e.operation 
      when 'INSERT' THEN 'Added ' || t.value || ' ' || n.value || ' ' || c.value
      when 'DELETE' THEN 'Removed ' || t.value || ' ' || n.value || ' ' || c.value
      else e.operation || t.value || ' ' || n.value || ' ' || c.value || ': ' ||
      MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation )
    end as description
  from
    mgmt_delta_entry e,
    mgmt_delta_ids i,
    mgmt_delta_id_values p,
    mgmt_delta_id_values v,
    mgmt_delta_id_values r,
    mgmt_delta_id_values l,
    mgmt_delta_id_values n,
    mgmt_delta_id_values t,
    mgmt_delta_id_values c,
    mgmt_delta_snap s,
    mgmt_targets t 
  where
    i.collection_type = 'MGMT_HC_VENDOR_SW_COMPONENTS' and
    i.row_guid = e.row_guid and
    p.delta_ids_guid = i.row_guid and
    p.name = 'SW_NAME' and
    v.delta_ids_guid = i.row_guid and
    v.name = 'VENDOR_NAME' and
    l.delta_ids_guid = i.row_guid and
    l.name = 'SW_INSTALLED_LOCATION' and
    r.delta_ids_guid = i.row_guid and
    r.name = 'SW_VERSION' and
    n.delta_ids_guid = i.row_guid and
    n.name = 'COMPONENT_NAME' and
    t.delta_ids_guid = i.row_guid and
    t.name = 'TYPE' and
    c.delta_ids_guid = i.row_guid and
    c.name = 'VERSION' and
    t.target_name = s.new_left_target_name and 
    t.target_type = 'host' and
    e.delta_guid = s.delta_guid and
    s.target_type = 'host' and
    s.snapshot_type = 'host_configuration');
/

COMMENT ON TABLE MGMT$DELTA_VENDOR_SW IS
  'The view MGMT$DELTA_VENDOR_SW has been deprecated.';

-- View Name : MGMT$DELTA_HOST_CONFIG
-- Description : Returns all Host category changes as summary
CREATE OR REPLACE VIEW MGMT$DELTA_HOST_CONFIG AS 
select
    s.new_left_target_name as host,
    MGMT_DELTA.GET_DELTA_KEY_DISPLAY_STRING( i.row_guid ) as key,
    e.delta_time as refresh_time,
    e.operation as operation,
    i.collection_type as collection_type,
    tab.ui_name as coll_type_text,
    MGMT_DELTA.GET_DELTA_VALUE_DISPLAY_STRING( e.delta_entry_guid, e.operation ) as details
  from
    mgmt_delta_entry e,
    mgmt_delta_snap s,
    mgmt_targets t, 
    mgmt_delta_ids i,
    mgmt_ecm_snapshot_md_tables tab
  where
    i.row_guid = e.row_guid and
    i.collection_type = tab.name and
    t.target_name = s.new_left_target_name and 
    t.target_type = 'host' and
    e.delta_guid = s.delta_guid and
    s.target_type = 'host' and
    s.snapshot_type = 'host_configuration';


COMMENT ON TABLE MGMT$DELTA_HOST_CONFIG IS
  'The view MGMT$DELTA_HOST_CONFIG has been deprecated.';
/


-- View Name : MGMT$DELTA_FS_MOUNT
-- Description : Returns host files system mount info.
CREATE OR REPLACE VIEW MGMT$DELTA_FS_MOUNT AS 
select 
    e.delta_time as refresh_time, 
    e.operation as operation, 
    s.new_left_target_name as host_name,
    k2.value as resource_name, 
    k3.value as type, 
    k4.value as mount_location, 
    case when e.operation = 'INSERT' then  v.value when e.operation = 'DELETE' 
 then v.old_value else v.old_value || '=>' || v.value end as mount_options 
 from 
  mgmt_delta_ids i, 
  mgmt_delta_id_values k2, 
  mgmt_delta_id_values k3, 
  mgmt_delta_id_values k4, 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values v,
  mgmt_delta_snap s,
  mgmt_targets t 
 where 
  i.collection_type = 'ECM$HIST_FS_MOUNT_DETAILS' and 
  i.row_guid = k2.delta_ids_guid and 
  k2.name = 'RESOURCE_NAME' and 
  i.row_guid = k3.delta_ids_guid and 
  k3.name = 'TYPE' and 
  i.row_guid = k4.delta_ids_guid and 
  k4.name = 'MOUNT_LOCATION' and 
  i.row_guid = e.row_guid and 
  v.delta_entry_guid(+) = e.delta_entry_guid and
  t.target_name = s.new_left_target_name and 
  t.target_type = 'host' and
  e.delta_guid = s.delta_guid and
  s.target_type = 'host' and
  s.snapshot_type = 'host_configuration';
/

COMMENT ON TABLE MGMT$DELTA_FS_MOUNT IS
  'The view MGMT$DELTA_FS_MOUNT has been deprecated.';
 
-- View Name : MGMT$SOFTWARE_OTHERS
-- Description : Returns HOST SW info other than Oracle, or OS.
CREATE OR REPLACE VIEW MGMT$SOFTWARE_OTHERS AS 
select 
  s.target_name as host_name, 
  v.name as Software_Name,
  v.vendor_name as Software_vendor,
  v.version as Software_version,
  s.snapshot_guid,
	v.installation_date,
	v.installed_location
from 
  mgmt_ecm_snapshot s,
  mgmt_hc_vendor_sw_summary v,
  mgmt_targets t
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = v.snapshot_guid;
/

-- View Name : MGMT$HW_NIC
-- Description : Host hardware network interface cards.
CREATE OR REPLACE VIEW MGMT$HW_NIC AS select 
  s.target_name as host_name,
  n.name as name,
  n.inet_address as inet_address,
  n.max_transfer_unit as max_transfer_unit,
  n.broadcast_address as broadcast_address,
  n.mask as mask,
  n.flags as flags,
  n.mac_address as mac_address,
  n.hostname_aliases as host_aliases,
  s.snapshot_guid
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_hc_nic_details n
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = n.snapshot_guid;
/

-- View Name : MGMT$OS_FS_MOUNT
-- Description : File system mount details.
CREATE OR REPLACE VIEW MGMT$OS_FS_MOUNT AS select 
  s.target_name as host_name,
  f.resource_name as resource_name,
  f.type as type,
  f.mount_location as mount_location,
  f.mount_options as mount_options,
  s.snapshot_guid
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_hc_fs_mount_details f
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = f.snapshot_guid;
/

-- View Name : MGMT$OS_HW_SUMMARY 
-- Description : Host summary info.
CREATE OR REPLACE VIEW MGMT$OS_HW_SUMMARY AS 
select 
  s.target_name as host_name,
  ss.domain as domain,
  os.name || ' ' || os.base_version || ' ' || os.update_level || '(' || os.address_length_in_bits || ')' as OS_SUMMARY,
  hm.system_config,
  hm.machine_architecture as ma,
  hm.clock_freq_in_mhz as freq,
  hm.memory_size_in_mb as mem,
  hm.local_disk_space_in_gb as disk,
  hm.cpu_count,
  hm.vendor_name,
  os.vendor_name as os_vendor,
  os.distributor_version,
  s.snapshot_guid
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_hc_system_summary ss,
  mgmt_hc_os_summary os,
  mgmt_hc_hardware_master hm
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = ss.snapshot_guid and
  s.snapshot_guid = os.snapshot_guid and 
  s.snapshot_guid = hm.snapshot_guid;
/

-- View Name : MGMT$SOFTWARE_PATCHES_IN_HOMES
-- Description : Returns Home patches and the bugs fixed by them.
CREATE OR REPLACE VIEW MGMT$SOFTWARE_PATCHES_IN_HOMES AS 
select
  s.target_name as host_name,
  h.container_name as home_name,
  h.container_location as home_location,
  p.id as Patch_id,
  ecm_util.concat_col('BUG_NUMBER', 'MGMT_INV_PATCH_FIXED_BUG', 'PATCH_GUID = ''' || p.patch_guid || '''', ',') as bugs_fixed,
  s.snapshot_guid
from
  mgmt_targets t,
  mgmt_ecm_snapshot s,
  mgmt_inv_container h,
  mgmt_inv_patch p
where
  t.target_type = 'host' and
  t.target_name = s.target_name and
  s.snapshot_type = 'host_configuration' and
  s.target_type = 'host' and
  s.is_current = 'Y' and
  s.snapshot_guid = h.snapshot_guid and
  h.container_guid = p.container_guid;
/
  
-- View Name : MGMT$ECM_VISIBLE_SNAPSHOTS
-- Description : This view lists all ECM snapshots visible from 
-- UI to the current EM user
CREATE OR REPLACE VIEW MGMT$ECM_VISIBLE_SNAPSHOTS AS
select SNAPSHOT_GUID as ECM_SNAPSHOT_ID,
  SNAPSHOT_TYPE, START_TIMESTAMP, TARGET_GUID, TARGET_NAME, TARGET_TYPE,
  DISPLAY_TARGET_NAME, DISPLAY_TARGET_TYPE, ELAPSED_TIME, DESCRIPTION,
  IS_CURRENT, MESSAGE, STATUS, CREATOR, SAVED_TIMESTAMP 
from mgmt_ecm_gen_snapshot s where
(exists (select * from mgmt_targets t where s.target_guid = t.target_guid) or
(mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1) or
(s.creator = mgmt_user.get_current_em_user()))
AND
(is_current = 'Y' or is_current = 'N');
/

-- View Name : MGMT$ECM_CURRENT_SNAPSHOTS
-- Description : This view lists all ECM current snapshots and limits
-- access to the snapshots based on the logged in user.
CREATE OR REPLACE VIEW MGMT$ECM_CURRENT_SNAPSHOTS AS
select SNAPSHOT_GUID as ECM_SNAPSHOT_ID,
  SNAPSHOT_TYPE, s.START_TIMESTAMP, s.TARGET_GUID, s.TARGET_NAME, s.TARGET_TYPE,
  s.DISPLAY_TARGET_NAME, s.DISPLAY_TARGET_TYPE, s.ELAPSED_TIME, s.DESCRIPTION,
  s.MESSAGE, STATUS, s.SAVED_TIMESTAMP, t.HOST_NAME
from mgmt_ecm_gen_snapshot s, mgmt_targets t where
s.target_guid = t.target_guid and s.is_current = 'Y';
/

-- VIEW NAME: MGMT$GROUP_MEMBERS
-- PURPOSE: returns all targets that are a part of any group, along with the name and type of the composite target.
--          The end user can just filter this result using the name and type of their group to get a list of targets in a specific group


CREATE OR REPLACE VIEW MGMT$GROUP_MEMBERS AS
SELECT
  m.member_target_name as target_name,
  m.member_target_type as target_type,
  m.member_target_guid as target_guid,
  m.composite_target_name as group_name,
  m.composite_target_type as group_type
FROM
  mgmt$group_flat_memberships m;
/

COMMENT ON TABLE MGMT$GROUP_MEMBERS IS
'The MGMT$GROUP_MEMBERS view has been deprecated. Please consult 
the documentation for the MGMT$TARGET_MEMBERS and MGMT$TARGET_FLAT_MEMBERS 
views for alternatives.';

CREATE OR REPLACE VIEW MGMT$MISSING_TARGETS_IN_GROUPS AS
SELECT
  mt.target_name as target_name,
  mt.target_type as target_type,
  mt.target_guid as target_guid,
  gt.target_name as group_name,
  gt.target_type as group_type,
  mt.host_name as host_name,
  mt.type_display_name as type_display_name,
  home_property.property_value as home,
  NVL2(home_property.property_value, 'no_oui_information_diagnostic', 'target_oracle_home_not_found_diagnostic') as diagnostic
FROM
  mgmt_flat_target_assoc m,
  mgmt$target_components c,
  mgmt_target_properties home_property,
  mgmt_targets gt,
  mgmt_targets mt
WHERE
  m.source_target_guid = gt.target_guid
  and m.assoc_target_guid = mt.target_guid
  and m.is_membership = '1'
  and mt.target_type in ('oracle_database', 'oracle_ias')
  and c.target_name(+) = mt.target_name
  and c.target_type(+) = mt.target_type
  and c.target_name is null
  and c.target_type is null
  and mt.target_guid = home_property.target_guid (+)
  and home_property.property_name (+) = 'OracleHome';
/

CREATE OR REPLACE VIEW MGMT$MISSING_TARGETS AS
SELECT
  t.target_name,
  t.target_type,
  t.target_guid,
  t.host_name,
  t.type_display_name,
  home_property.property_value as home,
  NVL2(home_property.property_value, 'no_oui_information_diagnostic', 'target_oracle_home_not_found_diagnostic') as diagnostic
FROM
  mgmt_targets t,
  mgmt_target_properties home_property,
  mgmt$target_components c
WHERE
  t.target_type in ('oracle_database', 'oracle_ias')
  and c.target_name(+) = t.target_name
  and c.target_type(+) = t.target_type
  and c.target_name is null
  and c.target_type is null
  and t.target_guid = home_property.target_guid (+)
  and home_property.property_name (+) = 'OracleHome'
/

-- ASSOCIATION_NAME   :  The name of the association
-- SOURCE_TARGET_TYPE :  The target type of the target for which 
--                       association is being defined.
-- SOURCE_TARGET_NAME :  the source target name
-- ASSOC_TARGET_TYPE  :  The target type of the associated target.
-- ASSOC_TARGET_NAME :  the associated target name
-- SCOPE_TARGET_TYPE  :  For non-global associations, this defines the
--                       target type in which the association is valid.
-- SCOPE_TARGET_NAME  :  the scope target name
-- ASSOCIATION_TYPE   :  The type of the association.

CREATE OR REPLACE VIEW MGMT$TARGET_ASSOCIATIONS AS 
    SELECT d.assoc_def_name assoc_def_name ,
           t1.target_name source_target_name,
           t1.target_type source_target_type,
           t2.target_name assoc_target_name,
           t2.target_type assoc_target_type,
           t3.target_name scope_target_name,
           t3.target_type scope_target_type,
           d.association_type association_type
      FROM mgmt_targets t1,
           mgmt_targets t2,
           mgmt_targets t3,
           mgmt_target_assocs a,
           mgmt_target_assoc_defs d
     WHERE t1.target_guid = a.source_target_guid
       AND t2.target_guid = a.assoc_target_guid
       AND t3.target_guid = a.scope_target_guid
       AND d.assoc_guid = a.assoc_guid
    UNION ALL    
       SELECT d.assoc_def_name assoc_def_name,
              t1.target_name source_target_name,
              t1.target_type source_target_type,
              t2.target_name assoc_target_name,
              t2.target_type assoc_target_type,
              ' ' scope_target_name,
              ' ' scope_target_type,
              d.association_type association_type
       FROM   mgmt_targets t1,
              mgmt_targets t2,
              mgmt_target_assocs a,
              mgmt_target_assoc_defs d
       WHERE  t1.target_guid = a.source_target_guid
         AND  t2.target_guid = a.assoc_target_guid
         AND  a.scope_target_guid = '0000000000000000'
         AND  d.assoc_guid = a.assoc_guid; 

CREATE OR REPLACE VIEW mgmt$policies
    (target_type, metric_name, metric_guid,policy_name, policy_label_nlsid, policy_guid, 
     author, description,description_nlsid,impact, impact_nlsid,recommendation, 
     recommendation_nlsid,violation_level, condition_type, condition, 
     condition_operator, owner, auto_enabled,category, category_nlsid)
  AS
    SELECT p.target_type, m.metric_name, m.metric_guid,p.policy_name, p.policy_label_nlsid, p.policy_guid, 
           p.author, p.description,p.description_nlsid, p.impact,p.impact_nlsid,
           p.recommendation,p.recommendation_nlsid,p.condition_type, p.condition,
           p.condition_operator, 
           DECODE(p.violation_level, 
                    18, 'Informational',
                    20, 'Warning',
                    25, 'Critical',
                    'Unknown'),
           p.owner, p.auto_enable, func_cats.category_name as category, 
           func_cats.category_name_nlsid as category_nlsid
      FROM mgmt_policies p,
           (SELECT catm.target_type, catm.object_guid, c.category_name, c.category_name_nlsid
              FROM mgmt_categories c, mgmt_category_map catm
             WHERE c.class_name = catm.class_name
               AND c.category_name = catm.category_name
               AND c.class_name = 'Functional'
               AND catm.object_type = 2) func_cats,
           (SELECT distinct metric_guid, metric_name 
              FROM mgmt_metrics) m
     WHERE p.metric_guid = m.metric_guid 
       AND p.policy_type = 2
       AND p.policy_guid = func_cats.object_guid (+)
WITH READ ONLY;


CREATE OR REPLACE VIEW mgmt$policy_parameters
    (target_type, metric_name, policy_name, policy_guid, 
     parameter_name,parameter_name_nlsid, parameter_type)
  AS
    SELECT p.target_type, m.metric_name, p.policy_name, p.policy_guid, 
           pp.param_name,pp.param_name_nlsid, 
           DECODE(pp.param_type, 
	            1, 'NUMERIC',
                    2, 'STRING',
                    'UNKNOWN')
      FROM mgmt_policies p,
           mgmt_policy_parameters pp,
           (SELECT distinct metric_guid, metric_name 
              FROM mgmt_metrics) m
     WHERE p.metric_guid = m.metric_guid 
       AND p.policy_guid = pp.policy_guid
       AND p.policy_type = 2
WITH READ ONLY;




CREATE OR REPLACE VIEW mgmt$policy_violation_ctxt
    (target_type, metric_name, policy_name, policy_guid, column_name, 
     is_hidden, url_link_type, url_link_template)
  AS
    SELECT p.target_type, m.metric_name, p.policy_name, p.policy_guid, 
           pv.column_name, pv.is_hidden, 
           DECODE(pv.url_link_type, 
	            0, 'JSP', 
		    1, 'UIX', 
		    'UNKNOWN'),  
           pv.url_link_template
      FROM mgmt_policies p,
           mgmt_policy_viol_ctxt_def pv,
           (SELECT distinct metric_guid, metric_name 
              FROM mgmt_metrics) m
     WHERE p.metric_guid = m.metric_guid 
       AND p.policy_guid = pv.policy_guid
       AND p.policy_type = 2
WITH READ ONLY;





CREATE OR REPLACE VIEW mgmt$target_policies
    (target_name, target_type, target_guid, policy_name, 
     policy_guid, category, is_enabled)
  AS
    SELECT t.target_name, t.target_type, t.target_guid, p.policy_name, 
           p.policy_guid, func_cats.category_name, pa.is_enabled
      FROM mgmt_policy_assoc pa,
           mgmt_targets t,
           mgmt_policies p,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 2) func_cats
     WHERE pa.object_guid = t.target_guid 
       AND pa.object_type = 2
       AND pa.policy_guid = p.policy_guid
       AND p.policy_type = 2
       AND pa.policy_guid = func_cats.object_guid (+)
WITH READ ONLY;




CREATE OR REPLACE VIEW mgmt$target_policy_settings
    (target_name, target_type, target_guid, policy_name, 
     policy_guid, category,
     key_value, key_value2, key_value3, key_value4, key_value5,
     key_operator, parameter_name, prevent_override, 
     policy_threshold, action_type, action_job_type,
     action_job_name, action_job_owner)
  AS
    SELECT t.target_name, t.target_type, t.target_guid, p.policy_name, 
           p.policy_guid, func_cats.category_name, pac.key_value, 
           null, null, null, null,
           pac.key_operator, pacp.param_name, pac.prevent_override,
           DECODE(p.violation_level, 
	            18, pacp.info_threshold,
                    20, pacp.warn_threshold,
                    25, pacp.crit_threshold,
                    NULL),
           DECODE(TRIM(pac.fixit_job), 
	          NULL,  DECODE( DECODE(p.violation_level, 
                                         18, tgt_info_cas.job_id,
                                         20, tgt_warn_cas.job_id,
                                         25, tgt_crit_cas.job_id),
				  NULL, 'No-action', 
                                  'Corrective-Action'), 
                  'Agent-Fixit job'),
           DECODE(p.violation_level, 18, tgt_info_cas.job_type,
                                     20, tgt_warn_cas.job_type,
                                     25, tgt_crit_cas.job_type),
           DECODE(p.violation_level, 18, tgt_info_cas.job_name,
                                     20, tgt_warn_cas.job_name,
                                     25, tgt_crit_cas.job_name),
           DECODE(p.violation_level, 18, tgt_info_cas.job_owner,
                                     20, tgt_warn_cas.job_owner,
                                     25, tgt_crit_cas.job_owner)
      FROM mgmt_policy_assoc pa,
           mgmt_targets t,
           mgmt_policies p,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 2) func_cats,
           mgmt_policy_assoc_cfg pac,
           mgmt_policy_assoc_cfg_params pacp,
           (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 1) tgt_crit_cas,
           (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 1) tgt_warn_cas,
           (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 1) tgt_info_cas
     WHERE pa.object_guid = t.target_guid 
       AND pa.policy_guid = p.policy_guid
       AND pa.object_guid = pac.object_guid
       AND pa.policy_guid = pac.policy_guid
       AND pa.coll_name   = pac.coll_name
       AND pac.object_guid = pacp.object_guid (+)
       AND pac.policy_guid = pacp.policy_guid (+)
       AND pac.coll_name   = pacp.coll_name   (+)
       ANd pac.key_value   = pacp.key_value   (+)
       AND pac.key_operator = pacp.key_operator (+)
       AND pa.policy_guid = func_cats.object_guid (+)
       AND pa.object_type = 2
       AND p.policy_type = 2    
       AND pac.crit_action_job_id = tgt_crit_cas.job_id (+)
       AND pac.object_guid = tgt_crit_cas.ca_target_guid (+)
       AND pac.warn_action_job_id = tgt_warn_cas.job_id (+)
       AND pac.object_guid = tgt_warn_cas.ca_target_guid (+)
       AND pac.info_action_job_id = tgt_info_cas.job_id (+)
       AND pac.object_guid = tgt_info_cas.ca_target_guid (+)
  UNION ALL
    SELECT t.target_name, t.target_type, t.target_guid, p.policy_name, 
           p.policy_guid, func_cats.category_name, k.key_part1_value, 
           k.key_part2_value, k.key_part3_value, k.key_part4_value, 
           k.key_part5_value, pac.key_operator, pacp.param_name, 
           pac.prevent_override,
           DECODE(p.violation_level, 
	            18, pacp.info_threshold,
                    20, pacp.warn_threshold,
                    25, pacp.crit_threshold,
                    NULL),
           DECODE(TRIM(pac.fixit_job),
	          NULL,  DECODE( DECODE(p.violation_level, 
                                         18, tgt_info_cas.job_id,
                                         20, tgt_warn_cas.job_id,
                                         25, tgt_crit_cas.job_id),
				  NULL, 'No-action', 
                                  'Corrective-Action'), 
                  'Agent-Fixit job'),
           DECODE(p.violation_level, 18, tgt_info_cas.job_type,
                                     20, tgt_warn_cas.job_type,
                                     25, tgt_crit_cas.job_type),
           DECODE(p.violation_level, 18, tgt_info_cas.job_name,
                                     20, tgt_warn_cas.job_name,
                                     25, tgt_crit_cas.job_name),
           DECODE(p.violation_level, 18, tgt_info_cas.job_owner,
                                     20, tgt_warn_cas.job_owner,
                                     25, tgt_crit_cas.job_owner)
      FROM mgmt_policy_assoc pa,
           mgmt_targets t,
           mgmt_policies p,
           mgmt_metrics_composite_keys k,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 2) func_cats,
           mgmt_policy_assoc_cfg pac,
           mgmt_policy_assoc_cfg_params pacp,
           (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 1) tgt_crit_cas,
           (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 1) tgt_warn_cas,
           (SELECT ca.ca_target_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 1) tgt_info_cas
     WHERE pa.object_guid = t.target_guid 
       AND pa.policy_guid = p.policy_guid
       AND pa.object_guid = pac.object_guid
       AND pa.policy_guid = pac.policy_guid
       AND pa.coll_name   = pac.coll_name
       AND pac.object_guid = pacp.object_guid (+)
       AND pac.policy_guid = pacp.policy_guid (+)
       AND pac.coll_name   = pacp.coll_name   (+)
       ANd pac.key_value   = pacp.key_value   (+)
       AND pac.key_operator = pacp.key_operator (+)
       AND pa.policy_guid = func_cats.object_guid (+)
       AND pa.object_type = 2
       AND p.policy_type = 2    
       AND pac.crit_action_job_id = tgt_crit_cas.job_id (+)
       AND pac.object_guid        = tgt_crit_cas.ca_target_guid (+)
       AND pac.warn_action_job_id = tgt_warn_cas.job_id (+)
       AND pac.object_guid        = tgt_warn_cas.ca_target_guid (+)
       AND pac.info_action_job_id = tgt_info_cas.job_id (+)
       AND pac.object_guid        = tgt_info_cas.ca_target_guid (+)
       AND k.target_guid = t.target_guid
       AND pac.key_value = k.composite_key
WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$policy_violation_current
     (target_name, target_type, type_display_name, target_guid, policy_name,
      policy_guid, category, key_value, key_value2, key_value3, key_value4,
      key_value5, collection_timestamp, violation_level,message,message_nlsid,
      message_params,suppress_code, suppress_until, suppress_by)
   AS
     SELECT t.target_name, t.target_type, tt.type_display_name, t.target_guid,
       p.policy_name, p.policy_guid, func_cats.category_name,
       v.key_value, null, null, null, null,
       v.collection_timestamp, v.violation_level, v.message,v.message_nlsid,
       v.message_params,v.exempt_code, v.exempt_until, v.exempt_by
     FROM
       mgmt_targets t,
       mgmt_metrics m,
       mgmt_policies p,
       mgmt_target_types tt,
       mgmt_current_violation v,
       (SELECT target_type, object_guid, category_name
          FROM mgmt_category_map catm
         WHERE class_name = 'Functional'
           AND object_type = 2) func_cats
     WHERE v.target_guid = t.target_guid
       AND v.policy_guid = p.policy_guid
       AND tt.target_type = t.target_type
       AND m.metric_guid = p.metric_guid
       AND m.num_keys < 2
       AND m.target_type = t.target_type
       AND t.type_meta_ver = m.type_meta_ver
       AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
       AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
       AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
       AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
       AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
       AND p.policy_type = 2
       AND v.violation_type = 3
       AND v.policy_guid = func_cats.object_guid (+)
   UNION ALL
   SELECT t.target_name, t.target_type, tt.type_display_name, t.target_guid,
       p.policy_name, p.policy_guid, func_cats.category_name,
       k.key_part1_value, k.key_part2_value, k.key_part3_value,
       k.key_part4_value, k.key_part5_value, v.collection_timestamp,
       v.violation_level, v.message,v.message_nlsid, v.message_params,
       v.exempt_code, v.exempt_until, v.exempt_by
   FROM
       mgmt_targets t,
       mgmt_metrics m,
       mgmt_policies p,
       mgmt_target_types tt,
       mgmt_current_violation v,
       (SELECT target_type, object_guid, category_name
          FROM mgmt_category_map catm
         WHERE class_name = 'Functional'
           AND object_type = 2) func_cats,
       mgmt_metrics_composite_keys k
   WHERE v.target_guid = t.target_guid
     AND v.policy_guid = p.policy_guid
     AND tt.target_type = t.target_type
     AND m.metric_guid = p.metric_guid
     AND m.num_keys > 1
     AND m.target_type = t.target_type
     AND t.type_meta_ver = m.type_meta_ver
     AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
     AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
     AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
     AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
     AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')         
     AND k.target_guid = t.target_guid
     AND v.key_value = k.composite_key
     AND p.policy_type = 2
     AND violation_type = 3
     AND v.policy_guid = func_cats.object_guid (+)
 WITH READ ONLY; 

CREATE OR REPLACE VIEW mgmt$policy_violation_history
    (target_name, target_type, type_display_name, target_guid, policy_name, 
     policy_guid, category, key_value, key_value2, key_value3, key_value4,
     key_value5, collection_timestamp, violation_level, 
     violation_duration, message,message_nlsid,message_params)
  AS
   SELECT t.target_name, t.target_type, t.type_display_name,
          t.target_guid, p.policy_name,
            p.policy_guid, cm.category_name,
            nvl(k.key_part1_value,v.key_value),
            k.key_part2_value, k.key_part3_value, k.key_part4_value,
            k.key_part5_value, v.collection_timestamp, v.violation_level,
            v.violation_duration, v.message,
            v.message_nlsid, v.message_params
       FROM mgmt_violations v,
            mgmt_targets t,
            mgmt_policies p,
            mgmt_category_map cm,
            mgmt_metrics_composite_keys k
      WHERE v.target_guid = t.target_guid
        AND v.policy_guid = p.policy_guid
        AND v.policy_guid = cm.object_guid (+)
        AND v.key_value = k.composite_key (+)
        AND v.target_guid = k.target_guid (+)
        AND p.policy_type = 2
        AND v.violation_type = 3
        AND cm.class_name (+) = 'Functional'
        AND cm.object_type (+) = 2
 WITH READ ONLY; 

CREATE OR REPLACE VIEW mgmt$policy_violation_context
    (target_name, target_type, target_guid, policy_name, 
     policy_guid, category, key_value, key_value2, key_value3, key_value4,
     key_value5, collection_timestamp, column_name, 
     column_value)
  AS
    SELECT t.target_name, t.target_type, t.target_guid, p.policy_name, 
           p.policy_guid, func_cats.category_name, v.key_value, null, null, null, null,
           v.collection_timestamp, vc.column_name, 
           DECODE(vc.column_type, 
                    2, DECODE(vc.column_value, 
                                NULL, NULL, 
                                to_number(vc.column_value)), 
                    column_str_value)
      FROM mgmt_violations v,
           mgmt_violation_context vc, 
           mgmt_targets t,
           mgmt_policies p,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 2) func_cats
     WHERE v.target_guid = t.target_guid 
       AND v.policy_guid = p.policy_guid
       AND v.target_guid = vc.target_guid
       AND v.policy_guid = vc.policy_guid
       AND v.policy_guid = func_cats.object_guid (+)
       AND v.key_value = vc.key_value
       AND v.collection_timestamp = vc.collection_timestamp
       AND p.policy_type = 2
       AND v.violation_type = 3
  UNION ALL
    SELECT t.target_name, t.target_type, t.target_guid, p.policy_name, 
           p.policy_guid, func_cats.category_name, k.key_part1_value, k.key_part2_value, 
           k.key_part3_value, k.key_part4_value, k.key_part5_value,
           v.collection_timestamp, vc.column_name, 
           DECODE(vc.column_type, 
                    2, DECODE(vc.column_value, NULL, NULL, 
                                to_number(vc.column_value)), 
                    column_str_value)
      FROM mgmt_violations v,
           mgmt_violation_context vc, 
           mgmt_targets t,
           mgmt_policies p,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 2) func_cats,
           mgmt_metrics_composite_keys k
     WHERE v.target_guid = t.target_guid 
       AND v.policy_guid = p.policy_guid
       AND v.target_guid = vc.target_guid
       AND v.policy_guid = vc.policy_guid
       AND v.policy_guid = func_cats.object_guid (+)
       AND v.key_value = vc.key_value
       AND v.collection_timestamp = vc.collection_timestamp
       AND k.target_guid = t.target_guid
       AND v.key_value = k.composite_key
       AND p.policy_type = 2
       AND v.violation_type = 3
WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$target_policy_eval_summ
    (target_name, target_type, target_guid, policy_name, policy_guid,
     coll_name, last_evaluation_date, total_violations, 
	 non_suppress_violations, compliance_score)
  AS
    SELECT t.target_name, t.target_type, t.target_guid,
		   p.policy_name, p.policy_guid, aes.coll_name, 
		   aes.last_evaluation_date, aes.total_violations_logged,
		   aes.non_exempt_violations_logged, aes.compliance_score
	  FROM mgmt_targets t, 
	       mgmt_policies p, 
		   mgmt_policy_assoc_eval_summ aes
	 WHERE t.target_guid = aes.target_guid 
	   AND p.policy_guid = aes.policy_guid 
	   AND p.policy_type = 2 
WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$templates
    (target_type, template_name, template_guid, description, owner,
     is_public, created_date, last_updated_date, last_updated_by)
  AS
    SELECT tt.target_type, tt.template_name, tt.template_guid,
           tt.description, tt.owner, tt.is_public, tt.created_date,
           tt.last_updated_date, tt.last_updated_by
      FROM mgmt_templates tt
WITH READ ONLY;


CREATE OR REPLACE VIEW mgmt$template_policy_settings
    (template_name, target_type, template_guid, policy_name, 
     policy_guid, category, key_value, 
     key_value2, key_value3, key_value4, key_value5,
     key_operator, parameter_name, prevent_override, 
     policy_threshold, action_type, action_job_type,
     action_job_name, action_job_owner)
  AS
    SELECT tt.template_name, tt.target_type, tt.template_guid, 
           p.policy_name, p.policy_guid, func_cats.category_name,
           pac.key_value, null, null, null, null,
           pac.key_operator, pacp.param_name, pac.prevent_override, 
           DECODE(p.violation_level, 
                    18, pacp.info_threshold,
                    20, pacp.warn_threshold, 
                    25, pacp.crit_threshold, 
                    NULL),
           DECODE(TRIM(pac.fixit_job),
	          NULL,  DECODE( DECODE(p.violation_level, 
                                         18, tgt_info_cas.job_id,
                                         20, tgt_warn_cas.job_id,
                                         25, tgt_crit_cas.job_id),
				  NULL, 'No-action', 
                                  'Corrective-Action'), 
                  'Agent-Fixit job'),
           DECODE(p.violation_level, 18, tgt_info_cas.job_type,
                                     20, tgt_warn_cas.job_type,
                                     25, tgt_crit_cas.job_type),
           DECODE(p.violation_level, 18, tgt_info_cas.job_name,
                                     20, tgt_warn_cas.job_name,
                                     25, tgt_crit_cas.job_name),
           DECODE(p.violation_level, 18, tgt_info_cas.job_owner,
                                     20, tgt_warn_cas.job_owner,
                                     25, tgt_crit_cas.job_owner)
      FROM mgmt_policy_assoc pa,
           mgmt_templates tt,
           mgmt_policies p,
          (SELECT target_type, object_guid, category_name
             FROM mgmt_category_map catm
            WHERE class_name = 'Functional'
              AND object_type = 2) func_cats,
          mgmt_policy_assoc_cfg pac,
          mgmt_policy_assoc_cfg_params pacp,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 2) tgt_crit_cas,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 2) tgt_warn_cas,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 2) tgt_info_cas
      WHERE pa.object_guid = tt.template_guid 
        AND pa.policy_guid = p.policy_guid
        AND pa.object_guid = pac.object_guid
        AND pa.policy_guid = pac.policy_guid
        AND pa.coll_name   = pac.coll_name
        AND pac.object_guid = pacp.object_guid (+)
        AND pac.policy_guid = pacp.policy_guid (+)
        AND pac.coll_name   = pacp.coll_name   (+)
        AND pac.key_value   = pacp.key_value   (+)
        AND pac.key_operator = pacp.key_operator (+)
        AND pa.policy_guid = func_cats.object_guid (+)
        AND pa.object_type = 3
        AND p.policy_type = 2    
        AND pac.crit_action_job_id = tgt_crit_cas.job_id (+)
        AND pac.object_guid        = tgt_crit_cas.ca_template_guid (+)
        AND pac.warn_action_job_id = tgt_warn_cas.job_id (+)
        AND pac.object_guid        = tgt_warn_cas.ca_template_guid (+)
        AND pac.info_action_job_id = tgt_info_cas.job_id (+)
        AND pac.object_guid        = tgt_info_cas.ca_template_guid (+)
  UNION ALL
    SELECT tt.template_name, tt.target_type, tt.template_guid, 
           p.policy_name, p.policy_guid, func_cats.category_name,
           k.key_part1_value, k.key_part2_value, k.key_part3_value, 
           k.key_part4_value, k.key_part5_value,
           pac.key_operator, pacp.param_name, pac.prevent_override, 
           DECODE(p.violation_level, 
                    18, pacp.info_threshold,
                    20, pacp.warn_threshold, 
                    25, pacp.crit_threshold, 
                    NULL),
           DECODE(TRIM(pac.fixit_job),
	          NULL,  DECODE( DECODE(p.violation_level, 
                                         18, tgt_info_cas.job_id,
                                         20, tgt_warn_cas.job_id,
                                         25, tgt_crit_cas.job_id),
				  NULL, 'No-action', 
                                  'Corrective-Action'), 
                  'Agent-Fixit job'),
           DECODE(p.violation_level, 18, tgt_info_cas.job_type,
                                     20, tgt_warn_cas.job_type,
                                     25, tgt_crit_cas.job_type),
           DECODE(p.violation_level, 18, tgt_info_cas.job_name,
                                     20, tgt_warn_cas.job_name,
                                     25, tgt_crit_cas.job_name),
           DECODE(p.violation_level, 18, tgt_info_cas.job_owner,
                                     20, tgt_warn_cas.job_owner,
                                     25, tgt_crit_cas.job_owner)
      FROM mgmt_policy_assoc pa,
           mgmt_templates tt,
           mgmt_policies p,
           mgmt_metrics_composite_keys k,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 2) func_cats,
           mgmt_policy_assoc_cfg pac,
           mgmt_policy_assoc_cfg_params pacp,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 2) tgt_crit_cas,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 2) tgt_warn_cas,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
             FROM mgmt_job j, mgmt_corrective_action ca
            WHERE j.job_id = ca.job_id
              AND j.is_corrective_action = 1
              AND ca.ca_scope = 2) tgt_info_cas
      WHERE pa.object_guid = tt.template_guid 
        AND pa.policy_guid = p.policy_guid
        AND pa.object_guid = pac.object_guid
        AND pa.policy_guid = pac.policy_guid
        AND pa.coll_name   = pac.coll_name
        AND pac.object_guid = pacp.object_guid (+)
        AND pac.policy_guid = pacp.policy_guid (+)
        AND pac.coll_name   = pacp.coll_name   (+)
        AND pac.key_value   = pacp.key_value   (+)
        AND pac.key_operator = pacp.key_operator (+)
        AND pa.policy_guid = func_cats.object_guid (+)
        AND pa.object_type = 3
        AND p.policy_type = 2    
        AND pac.crit_action_job_id = tgt_crit_cas.job_id (+)
        AND pac.object_guid        = tgt_crit_cas.ca_template_guid (+)
        AND pac.warn_action_job_id = tgt_warn_cas.job_id (+)
        AND pac.object_guid        = tgt_warn_cas.ca_template_guid (+)
        AND pac.info_action_job_id = tgt_info_cas.job_id (+)
        AND pac.object_guid        = tgt_info_cas.ca_template_guid (+)
        AND k.target_guid = pac.object_guid
        AND pac.key_value = k.composite_key
WITH READ ONLY;


CREATE OR REPLACE VIEW mgmt$messages
    (message_id, subsystem, language_code, country_code, message)
  AS
    SELECT message_id, subsystem, language_code, country_code, message
    FROM mgmt_messages
 WITH READ ONLY;


CREATE OR REPLACE VIEW mgmt$template_metriccollection
    (template_name, target_type, template_guid, metric_name, 
     metric_column, metric_guid, collection_name, is_repository, 
     frequency_code, collection_frequency, upload_policy)
  AS
    SELECT tt.template_name, tt.target_type, tt.template_guid, 
           m.metric_name, m.metric_column, m.metric_guid, 
           c.coll_name, m.is_repository, 
           DECODE(c.frequency_code, 1, 'One Time',
                                    2, 'Interval',
                                    3, 'Daily',
                                    4, 'Weekly',
                                    5, 'Monthly',
                                    6, 'Yearly',
                                    'On-Demand'),
           DECODE(c.frequency_code, 
                   1, TO_CHAR(c.start_time,'DD-MON-YY HH24:MI'),
                   2, to_char(interval),
                   3, to_char(execution_hours)||':'||to_char(execution_minutes),
                   4, to_char(execution_hours)||':'||to_char(execution_minutes),
                   5, to_char(execution_hours)||':'||to_char(execution_minutes),
                   6, to_char(execution_hours)||':'||to_char(execution_minutes),
                   'On-Demand'),
           c.upload_frequency
      FROM mgmt_templates tt, 
           mgmt_metrics m, 
           mgmt_collections c,
           mgmt_collection_metric_tasks cmt
     WHERE tt.target_type = m.target_type
       AND c.object_guid = tt.template_guid
       AND m.metric_guid = cmt.metric_guid
       AND c.object_guid = cmt.target_guid
       AND c.coll_name = cmt.coll_name
       AND c.object_type = 3
WITH READ ONLY;


CREATE OR REPLACE VIEW mgmt$template_metric_settings
    (template_name, target_type, template_guid, metric_name, 
     metric_column, metric_guid, collection_name, category, key_value, 
     key_value2, key_value3, key_value4, key_value5,
     key_operator, prevent_override, warning_operator, warning_threshold,
     critical_operator, critical_threshold, occurrence_count, 
     warning_action_type, warning_action_job_type,
     warning_action_job_owner, warning_action_job_name, 
     critical_action_type, critical_action_job_type,
     critical_action_job_owner, critical_action_job_name)
  AS 
    SELECT tt.template_name, tt.target_type, tt.template_guid, 
           m.metric_name, m.metric_column, m.metric_guid, 
           pa.coll_name, func_cats.category_name, pac.key_value, 
           null, null, null, null, pac.key_operator, 
           pac.prevent_override, pac.condition_operator, 
           pacp.warn_threshold, pac.condition_operator, 
           pacp.crit_threshold, pac.num_occurrences, 
           DECODE(TRIM(pac.fixit_job),
                    NULL, DECODE(tgt_warn_cas.job_type, 
                                   NULL, 'No-action', 
                                   'Corrective-Action'), 
                    'Agent-Fixit Job'), 
           tgt_warn_cas.job_type, tgt_warn_cas.job_owner, tgt_warn_cas.job_name,
           DECODE(TRIM(pac.fixit_job),
                    NULL, DECODE(tgt_crit_cas.job_type, 
                                   NULL, 'No-action', 
                                   'Corrective-Action'), 
                    'Agent-Fixit Job'), 
           tgt_crit_cas.job_type, tgt_crit_cas.job_owner, tgt_crit_cas.job_name
      FROM mgmt_templates tt, 
           mgmt_metrics m, 
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 1) func_cats,
             mgmt_policy_assoc pa,
             mgmt_policy_assoc_cfg pac,
             mgmt_policy_assoc_cfg_params pacp,
             (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                     j.job_owner, j.job_name
                FROM mgmt_job j, mgmt_corrective_action ca
               WHERE j.job_id = ca.job_id
                 AND j.is_corrective_action = 1
                 AND ca.ca_scope = 2) tgt_warn_cas,
             (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                     j.job_owner, j.job_name
                FROM mgmt_job j, mgmt_corrective_action ca
               WHERE j.job_id = ca.job_id
                 AND j.is_corrective_action = 1
                 AND ca.ca_scope = 2) tgt_crit_cas
     WHERE tt.target_type = m.target_type
       AND pa.object_guid = tt.template_guid
       AND pa.policy_guid = m.metric_guid
       AND pa.policy_type = 1
       AND pa.object_type = 3
       AND pa.object_guid = pac.object_guid
       AND pa.policy_guid = pac.policy_guid
       AND pa.coll_name = pac.coll_name
       AND pac.object_guid = pacp.object_guid
       AND pac.policy_guid = pacp.policy_guid
       AND pac.coll_name = pacp.coll_name
       AND pac.key_value = pacp.key_value
       AND pac.key_operator = pacp.key_operator
       AND m.num_keys < 2
       AND pacp.param_name = ' ' 
       AND func_cats.object_guid (+) = m.metric_guid
       AND tgt_warn_cas.job_id (+) = pac.warn_action_job_id 
       AND tgt_warn_cas.ca_template_guid (+) = pac.object_guid 
       AND tgt_crit_cas.job_id (+) = pac.crit_action_job_id 
       AND tgt_crit_cas.ca_template_guid (+) = pac.object_guid
  UNION ALL
    SELECT tt.template_name, tt.target_type, tt.template_guid, 
           m.metric_name, m.metric_column, m.metric_guid, 
           pa.coll_name, func_cats.category_name, 
		   k.key_part1_value, k.key_part2_value, k.key_part3_value, 
		   k.key_part4_value, k.key_part5_value, pac.key_operator, 
           pac.prevent_override, pac.condition_operator, 
           pacp.warn_threshold, pac.condition_operator, 
           pacp.crit_threshold, pac.num_occurrences, 
           DECODE(TRIM(pac.fixit_job),
                    NULL, DECODE(tgt_warn_cas.job_type, 
                                   NULL, 'No-action', 
                                   'Corrective-Action'), 
                   'Agent-Fixit Job'), 
           tgt_warn_cas.job_type, tgt_warn_cas.job_owner, tgt_warn_cas.job_name,
           DECODE(TRIM(pac.fixit_job),
                    NULL, DECODE(tgt_crit_cas.job_type, 
                                   NULL, 'No-action', 
                                   'Corrective-Action'), 
                    'Agent-Fixit Job'), 
           tgt_crit_cas.job_type, tgt_crit_cas.job_owner, tgt_crit_cas.job_name
      FROM mgmt_templates tt, 
           mgmt_metrics m, 
           mgmt_metrics_composite_keys k,
           (SELECT target_type, object_guid, category_name
              FROM mgmt_category_map catm
             WHERE class_name = 'Functional'
               AND object_type = 1) func_cats,
           mgmt_policy_assoc pa,
           mgmt_policy_assoc_cfg pac,
           mgmt_policy_assoc_cfg_params pacp,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
              FROM mgmt_job j, mgmt_corrective_action ca
             WHERE j.job_id = ca.job_id
               AND j.is_corrective_action = 1
               AND ca.ca_scope = 2) tgt_warn_cas,
           (SELECT ca.ca_template_guid, j.job_id, j.job_type, 
                   j.job_owner, j.job_name
              FROM mgmt_job j, mgmt_corrective_action ca
             WHERE j.job_id = ca.job_id
               AND j.is_corrective_action = 1
               AND ca.ca_scope = 2) tgt_crit_cas
     WHERE tt.target_type = m.target_type
       AND pa.object_guid = tt.template_guid
       AND pa.policy_guid = m.metric_guid
       AND pa.policy_type = 1
       AND pa.object_guid = pac.object_guid
       AND pa.policy_guid = pac.policy_guid
       AND pa.coll_name = pac.coll_name
       AND pac.object_guid = pacp.object_guid
       AND pac.policy_guid = pacp.policy_guid
       AND pac.coll_name = pacp.coll_name
       AND pac.key_value = pacp.key_value
       AND pac.key_operator = pacp.key_operator
       AND pa.object_type = 3
       AND m.num_keys > 1
       AND pacp.param_name = ' ' 
       AND func_cats.object_guid (+) = m.metric_guid
       AND tgt_warn_cas.job_id (+) = pac.warn_action_job_id 
       AND tgt_warn_cas.ca_template_guid (+) = pac.object_guid 
       AND tgt_crit_cas.job_id (+) = pac.crit_action_job_id 
       AND tgt_crit_cas.ca_template_guid (+) = pac.object_guid
       AND k.target_guid = pac.object_guid
       AND pac.key_value = k.composite_key
WITH READ ONLY;

--------------------- JOB SYSTEM VIEWS --------------------------
-- View Name: MGMT$JOBS
--   Gives basic information about a job and its schedule
-- 
CREATE OR REPLACE VIEW MGMT$JOBS
       (job_name, job_id, job_owner, job_description, job_type,
        target_type, is_library, is_restartable, start_time,
        end_time, timezone_type, timezone_region, schedule_type, interval,
        execution_hours, execution_minutes, months, days)
     AS
       SELECT
           j.job_name, j.job_id, j.job_owner, j.job_description,
           j.job_type, j.target_type, j.is_library, j.restartable,
           s.start_time, s.end_time, 
           DECODE (s.timezone_info, 
                    1, 'Repository',
                    2, 'Agent',
                    3, 'Specified Offset/Region',
                    4, 'Specified Offset/Region',
                    s.timezone_info),
           DECODE(s.timezone_info, 3, s.timezone_offset, 
                  s.timezone_region),
           DECODE (s.frequency_code, 1, 'One Time',
                                     2, 'Interval',
                                     3, 'Daily',
                                     4, 'Weekly',
                                     5, 'Monthy',
                                     6, 'Yearly'),
           s.interval, s.execution_hours, s.execution_minutes,
           s.months, s.days
        FROM
            mgmt_job j,
            mgmt_job_schedule s
        WHERE
            j.schedule_id = s.schedule_id AND
            j.system_job = 0 AND
            j.nested=0 AND
            j.is_corrective_action=0
    WITH READ ONLY;

-- View Name: MGMT$JOB_TARGETS
--   Provides the targets that the job was submitted with
CREATE OR REPLACE VIEW MGMT$JOB_TARGETS
    (job_name, job_owner, job_id, job_type, 
     target_name, target_type, target_guid)
     AS
       SELECT
           j.job_name, j.job_owner, j.job_id, j.job_type, 
           t.target_name, t.target_type, t.target_guid
        FROM
            mgmt_job j,
            mgmt_job_target jt,
            mgmt_targets t
        WHERE
            j.job_id = jt.job_id (+) AND
            HEXTORAW('0000000000000000')=jt.execution_id (+) AND
            jt.target_guid=t.target_guid (+) AND
            j.system_job = 0 AND
            j.nested=0 AND
            j.is_corrective_action=0
    WITH READ ONLY;

-- View Name: MGMT$JOB_EXECUTION_HISTORY
--   Provides a summary of job executions, along with their status
--   and targets for each execution
CREATE OR REPLACE VIEW MGMT$JOB_EXECUTION_HISTORY
    (job_name, job_owner, job_id, job_type, execution_id, scheduled_time, 
     start_time, end_time, status, target_name, target_type, target_guid)
    AS
      SELECT
        j.job_name, j.job_owner, j.job_id, j.job_type,
        e.execution_id, e.scheduled_time, e.start_time, e.end_time,
        DECODE(status, 
                1, 'Scheduled',
                2, 'Running',
                3, 'Failed Initialization',
                4, 'Failed',
                5, 'Succeeded',
                6, 'Suspended By User',
                7, 'Suspended: Agent Unreacheable',
                8, 'Stopped',
                9, 'Suspended on Lock',
               10, 'Suspended on Event',
               11, 'Stop Pending',
               13, 'Suspend Pending',
               14, 'Inactive',
               15, 'Queued',
               16, 'Failed Retried',
               18, 'Skipped', status),
        t.target_name, t.target_type, t.target_guid
      FROM
        MGMT_JOB j,
        MGMT_JOB_EXEC_SUMMARY e,
        MGMT_JOB_TARGET jt,
        MGMT_TARGETS t
      WHERE
        j.job_id=e.job_id AND
        j.is_corrective_action=0 AND
        e.job_id=jt.job_id (+) AND
        e.execution_id=jt.execution_id (+) AND
        jt.target_guid=t.target_guid (+)
      WITH READ ONLY;

-- View Name: MGMT$JOB_STEP_HISTORY
--   Gives step level details of job executions
CREATE OR REPLACE VIEW MGMT$JOB_STEP_HISTORY
    (job_name, job_owner, job_id, execution_id, step_name, 
     start_time, end_time, status, target_name, target_type, target_guid,
     output)
    AS
      SELECT
        j.job_name, j.job_owner, j.job_id, 
        e.execution_id, h.step_name, 
        h.start_time, h.end_time,
        DECODE(h.step_status, 
                1, 'Scheduled',
                2, 'Running',
                3, 'Failed Initialization',
                4, 'Failed',
                5, 'Succeeded',
                6, 'Suspended By User',
                7, 'Suspended: Agent Unreacheable',
                8, 'Stopped',
                9, 'Suspended on Lock',
               10, 'Suspended on Event',
               11, 'Stop Pending',
               13, 'Suspend Pending',
               14, 'Inactive',
               15, 'Queued',
               16, 'Failed Retried',
               18, 'Skipped', status),
        t.target_name, t.target_type, t.target_guid, o.output
      FROM
        MGMT_JOB j,
        MGMT_JOB_EXEC_SUMMARY e,
        MGMT_JOB_HISTORY h,
        MGMT_JOB_OUTPUT o,
        MGMT_JOB_STEP_TARGETS st,
        MGMT_TARGETS t
      WHERE
        j.job_id=e.job_id AND
        j.is_corrective_action=0 AND
        e.execution_id=h.execution_id AND
        h.step_id=st.step_id (+) AND
        h.step_type=1 AND
        st.target_guid=t.target_guid (+) AND
        h.output_id=o.output_id (+) 
      WITH READ ONLY;
-------------------- END JOB SYSTEM VIEWS ---------------------------

------------------- BLACKOUT SYSTEM VIEWS ------------------------
   
-- View Name: MGMT$BLACKOUTS
--    All blackout definitions in the system, along with current schedule
CREATE OR REPLACE VIEW MGMT$BLACKOUTS
    (blackout_name, blackout_guid, reason, description, status,
     created_by, last_start_time, last_end_time, scheduled_time,
     schedule_type, schedule_start_time, schedule_end_time, duration)
    AS
        SELECT b.blackout_name, b.blackout_guid, br.reason, b.blackout_desc,
               DECODE(b.blackout_status,
                      0, 'Scheduled',
                      1, 'Start Processing',
                      2, 'Start Partial',
                      4, 'Started',
                      5, 'Stop Pending',
                      6, 'Stop Failed',
                      7, 'Stop Partial',
                      8, 'Edit Failed',
                      9, 'Edit Partial',
                     10, 'Stopped',
                     11, 'Ended',
                     12, 'Partial Blackout',
                     13, 'Modify Pending', b.blackout_status),
                b.created_by, b.last_start_time, b.last_end_time,
                b.scheduled_time,
                DECODE (s.frequency_code, 1, 'One Time',
                                     2, 'Interval',
                                     3, 'Daily',
                                     4, 'Weekly',
                                     5, 'Monthy',
                                     6, 'Yearly'),
                s.start_time, s.end_time, s.duration
        FROM    MGMT_BLACKOUTS b, MGMT_BLACKOUT_SCHEDULE s,
                MGMT_BLACKOUT_REASON br
        WHERE   b.blackout_guid=s.blackout_guid
        AND     b.reason_id=br.reason_id (+)
        WITH READ ONLY;

-- View Name: MGMT$BLACKOUT_HISTORY
--    All blackout occurrences in the system, along with start time and 
--    end time
CREATE OR REPLACE VIEW MGMT$BLACKOUT_HISTORY
   (blackout_name, created_by, blackout_guid, start_time, end_time,
    target_name, target_type, target_guid, status)
   AS
       SELECT b.blackout_name, b.created_by, b.blackout_guid, 
              decode(h.start_time, null, null,
                     MGMT_GLOBAL.adjust_tz(h.start_time,
                                NVL(s.timezone_region, 
                                ((s.timezone_offset/60)||':'||mod(s.timezone_offset,60))),
                                t.timezone_region)),
              decode(h.end_time, null, null,
                     MGMT_GLOBAL.adjust_tz(h.end_time,
                                NVL(s.timezone_region, 
                                ((s.timezone_offset/60)||':'||mod(s.timezone_offset,60))),
                                t.timezone_region)),
              t.target_name, t.target_type, t.target_guid,
              DECODE(b.blackout_status,
                      0, 'Scheduled',
                      1, 'Start Processing',
                      2, 'Start Partial',
                      4, 'Started',
                      5, 'Stop Pending',
                      6, 'Stop Failed',
                      7, 'Stop Partial',
                      8, 'Edit Failed',
                      9, 'Edit Partial',
                     10, 'Stopped',
                     11, 'Ended',
                     12, 'Partial Blackout',
                     13, 'Modify Pending', b.blackout_status)
       FROM   MGMT_BLACKOUTS b,
              MGMT_BLACKOUT_HISTORY h,
              MGMT_BLACKOUT_FLAT_TARGETS ft,
              MGMT_TARGETS t,
              MGMT_BLACKOUT_SCHEDULE s
       WHERE  b.blackout_guid=h.blackout_guid
       AND    h.blackout_guid=ft.blackout_guid
       AND    ft.target_guid=t.target_guid
       AND    b.blackout_guid=s.blackout_guid
       WITH READ ONLY;


--------------- END BLACKOUT SYSTEM VIEWS ----------------------    

--------------- Metric Error views -----------------------

CREATE OR REPLACE VIEW mgmt$metric_error_history
    (target_name, target_type, target_guid,
     metric_name, metric_guid,metric_label, coll_name,
     collection_timestamp, error_type, error_message)
  AS
    SELECT t.target_name, t.target_type, t.target_guid,
           m.metric_name, me.metric_guid,m.metric_label, me.coll_name,
           me.collection_timestamp, DECODE(me.metric_error_type, 0, 
           'Error', 1, 'Warning', 'Unknown'),
           me.metric_error_message
      FROM mgmt_targets t, 
	       mgmt_metrics m, 
		   mgmt_metric_errors me
     WHERE t.target_guid = me.target_guid
       and m.metric_guid = me.metric_guid
       and t.target_type = m.target_type
       and t.type_meta_ver = m.type_meta_ver
       and (t.category_prop_1 = m.category_prop_1 or m.category_prop_1 = 
' ')
       and (t.category_prop_2 = m.category_prop_2 or m.category_prop_2 = 
' ')
       and (t.category_prop_3 = m.category_prop_3 or m.category_prop_3 = 
' ')
       and (t.category_prop_4 = m.category_prop_4 or m.category_prop_4 = 
' ')
       and (t.category_prop_5 = m.category_prop_5 or m.category_prop_5 = 
' ')
 WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt$metric_error_current
    (target_name, target_type, target_guid,
     metric_name, metric_guid,metric_label, coll_name,
     collection_timestamp, error_type, error_message)
  AS
    SELECT t.target_name, t.target_type, t.target_guid,
           m.metric_name, cme.metric_guid,m.metric_label, cme.coll_name,
           cme.collection_timestamp, DECODE(cme.metric_error_type, 0, 
           'Error', 1, 'Warning', 'Unknown'),
           cme.metric_error_message
      FROM mgmt_targets t, 
	       mgmt_metrics m, 
		   mgmt_current_metric_errors cme
     WHERE t.target_guid = cme.target_guid
       and m.metric_guid = cme.metric_guid
       and t.target_type = m.target_type
       and t.type_meta_ver = m.type_meta_ver
       and (t.category_prop_1 = m.category_prop_1 or m.category_prop_1 = 
' ')
       and (t.category_prop_2 = m.category_prop_2 or m.category_prop_2 = 
' ')
       and (t.category_prop_3 = m.category_prop_3 or m.category_prop_3 = 
' ')
       and (t.category_prop_4 = m.category_prop_4 or m.category_prop_4 = 
' ')
       and (t.category_prop_5 = m.category_prop_5 or m.category_prop_5 = 
' ')
 WITH READ ONLY;

---------------- End Metric Error Views --------------------------

-- View Name : MGMT$ORACLE_SW_ENT_INSTALL 
-- Description : Oracle Software(ias,OCS,DB,etc) Installation Summary.
CREATE OR REPLACE VIEW MGMT$ORACLE_SW_ENT_INSTALL AS 
SELECT 
  map.target_type as target_type,
  map.property_name as pname,
  map.property_value as pvalue,
  component.external_name as external_name,
  case when ps_patch.version is null then component.version else ps_patch.version end as version,
              host.target_name as host_name,
              host.target_guid as htguid,
              container_location,
              case
                when
                  exists
                  (
                    SELECT *
                    FROM mgmt_inv_patch p
                    WHERE p.container_guid = home.container_guid
                  )
                then 1 else 0
              end as num_patched,
              home.container_guid as container_guid
FROM
  mgmt_targets host,
  mgmt_ecm_snapshot snapshot, 
  mgmt_inv_container home,
  mgmt_inv_component component,
  mgmt_inv_versioned_patch ps_patch,
  mgmt_target_type_component_map map
WHERE host.target_type = 'host'
  and host.target_name = snapshot.target_name
  and host.target_type = snapshot.target_type
  and snapshot.snapshot_type = 'host_configuration'
  and snapshot.is_current = 'Y'
  and home.snapshot_guid = snapshot.snapshot_guid
  and component.container_guid = home.container_guid
  and component.component_guid = ps_patch.component_guid(+)
  and map.component_name = component.name 
  and ((map.property_name IS NULL and map.property_value IS NULL));
  
-- View Name : MGMT$ORACLE_SW_ENT_TARGETS 
-- Description : Oracle Software(ias,OCS,DB,etc) Targets Summary.
CREATE OR REPLACE VIEW MGMT$ORACLE_SW_ENT_TARGETS AS 
SELECT target.target_type as target_type,
         target.host_name as host_target_name,
         target.target_name as target_name,
         target.target_guid as target_guid,
         location_property.property_value as home_location
  FROM
         mgmt_targets target,
         mgmt_target_properties location_property
  WHERE
        location_property.target_guid = target.target_guid
        and location_property.property_name = 'OracleHome'
        and (exists (select * from mgmt_target_properties sub_type
                 where target.target_guid = sub_type.target_guid
         ) );

-- View Name : MGMT$ORACLE_SW_GRP_TARGETS 
-- Description : Group Oracle Software(ias,OCS,DB,etc) Targets Summary.
CREATE OR REPLACE VIEW MGMT$ORACLE_SW_GRP_TARGETS AS 
SELECT
      c.external_name as external_name,
      c.version as version, 
      h.container_guid as inst_container_guid,
      t.target_guid as target_guid,
      t.target_type as target_type,
      map.target_type as mtype,
      host_t.target_guid as hguid,
      t.target_name as target_name,
      t_home.property_value as container_location,
      t.host_name as host_name
    FROM
      mgmt_targets t,
      mgmt_targets host_t,
      mgmt_ecm_snapshot s, 
      mgmt_inv_container h, 
      em$inv_component c, 
      mgmt_target_type_component_map map,
      mgmt_target_properties t_home  
  WHERE
      host_t.target_name = t.host_name and 
      s.target_name = host_t.target_name and
      s.target_type = host_t.target_type and
      s.is_current = 'Y' and
      s.snapshot_type = 'host_configuration' and
      s.snapshot_guid = h.snapshot_guid and
      h.container_guid = c.container_guid and
      c.name = map.component_name and
      (map.property_name IS NULL and map.property_value IS NULL) and
      t.target_guid = t_home.target_guid and
      t_home.property_name = 'OracleHome' and
      (exists (select * from mgmt_target_properties sub_type
          where
      t.target_guid = sub_type.target_guid 
                ) ) and
      h.container_location = t_home.property_value(+);

-- View Name : MGMT$ORACLE_SW_GRP_INSTALL 
-- Description : Group Oracle Software(ias,OCS,DB,etc) Install Summary.
CREATE OR REPLACE VIEW MGMT$ORACLE_SW_GRP_INSTALL AS 
SELECT
      c.external_name as external_name,
      c.version as version, 
      h.container_guid as inst_container_guid,
      t.target_guid as target_guid,
      t.target_type as target_type,
      map.target_type as mtype,
      h.container_location as container_location,
      t.target_name as host_name
FROM
      mgmt_targets t,
      mgmt_targets host_t,
      mgmt_ecm_snapshot s, 
      mgmt_inv_container h, 
      em$inv_component c, 
      mgmt_target_type_component_map map
WHERE
      t.target_type = 'host' and
      host_t.target_name = t.host_name and
      s.target_name = host_t.target_name and
      s.target_type = host_t.target_type and
      s.is_current = 'Y' and
      s.snapshot_type = 'host_configuration' and
      s.snapshot_guid = h.snapshot_guid and
      h.container_guid = c.container_guid and
      c.name = map.component_name and
      map.property_name IS NULL and map.property_value IS NULL;

-- View Name : mgmt$csa_client_rule_viols 
-- Description : Client Configuration compliance rule violations.
CREATE OR REPLACE VIEW mgmt$csa_client_rule_viols AS 
SELECT  
  c.appid as application, 
  c.compliance as compliance,  
  c.csaclient as csaclient, 
  c.snapshot_id as snapshotid, 
  ecm_util.concat_col('r.name', 'mgmt$csa_host_rules r', 'r.snapshot_id = ''' || c.snapshot_id || ''' and r.status > 15', ',', 50) as list 
FROM MGMT$CSA_COLLECTIONS c;


-- View Name : mgmt$cs_config_standards
-- Description : Data about the configuration standards
CREATE OR REPLACE VIEW mgmt$cs_config_standards
    (cs_name, cs_name_nlsid, cs_author, cs_version, cs_guid, target_type, description, description_nlsid, keywords)
  AS
   SELECT NVL(nem.message, cs.cs_dname) as cs_name,
	  cs.cs_dname,
          cs.author,
          cs.version,
          cs.cs_guid,
          cs.target_type,
          NVL(dem.message, cs.description) as description,
	  cs.description,
          ecm_util.concat_col(
                 'nvl(kem.message, k.keyword)', 
                 'mgmt_cs_keyword k,
                     (select message, message_id from mgmt_messages
                     where subsystem = ''CONFIG_STD'' and language_code = ''en'' and country_code = '' '') kem', 
                 'k.cs_guid = ''' || cs.cs_guid || '''
                      and k.keyword = kem.message_id(+)',
                  ';') keywords
     FROM mgmt_cs_config_standard cs,
            (select message, message_id from mgmt_messages 
            where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') nem, 
            (select message, message_id from mgmt_messages 
            where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') dem 
     WHERE cs.cs_dname  = nem.message_id(+) 
       AND cs.description = dem.message_id(+) 
WITH READ ONLY;

-- View Name : mgmt$cs_eval_summary_standard
-- Description : Evalation results for targets evaluated against configuration standards
CREATE OR REPLACE VIEW mgmt$cs_eval_summary_standard
    (target_name, target_type, target_guid, cs_name, cs_name_nlsid, cs_guid, cs_author, cs_version,
     last_evaln_timestamp, compliance_score, total_violations, 
     total_non_compliant_rules, total_compliant_rules, total_error_rules, total_unknown_rules)
  AS
    SELECT t.target_name, 
      t.target_type, 
      t.target_guid,
      NVL(nem.message, cs.cs_dname) as cs_name,
      cs.cs_dname as cs_name_nlsid,
      cs.cs_guid as cs_guid, 
      cs.author as cs_author,
      cs.version as cs_version, 
      e.last_evaluation_date as last_evaln_timestamp,
      ROUND(e.compliance_score) as compliance_score,
      (e.crit_violations + e.warn_violations + e.info_violations) as total_violations,
      e.non_compliant_rules as total_non_compliant_rules,
      e.compliant_rules as total_compliant_rules,
      e.error_rules as total_error_rules,
      e.unknown_rules as total_unknown_rules
   FROM mgmt_targets t, 
        mgmt_cs_config_standard cs, 
        mgmt_cs_eval_summ_rqs e,
       (select message, message_id from mgmt_messages 
        where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') nem
  WHERE t.target_guid = e.target_guid 
    AND cs.cs_guid = e.rqs_guid 
    AND cs.cs_dname = nem.message_id(+)
WITH READ ONLY;

--View Name: mgmt$cs_eval_summary_rule
--Description: Contains mapping between a rule and number of violations for all targets evaluated
CREATE OR REPLACE VIEW mgmt$cs_eval_summary_rule
	(target_name, target_type, target_guid, rule_name, rule_name_nlsid, rule_guid, 
	cs_name, cs_name_nlsid, cs_author, cs_version, cs_guid, 
  total_violations, status, importance_level)
AS   
	SELECT t.target_name, t.target_type, t.target_guid,
    NVL(rnem.message, r.rule_dname) as rule_name,
    r.rule_dname as rule_name_nlsid, 
    r.rule_guid as rule_guid,
    NVL(cnem.message, cs.cs_dname) as cs_name,
    cs.cs_dname as cs_name_nlsid, 
    cs.author as cs_author, 
    cs.version as cs_version, 
    cs.cs_guid as cs_guid,
    e.total_violations as total_violations,    
    DECODE(e.status, 0, 'Error', 
                     1, 'Compliant', 
                     2, 'Non-Compliant',
                     3, 'Unknown'), 
    DECODE(r.importance_level, 0, 'Not Scored',
                     1, 'Extremely Low',
                     2, 'Low',
                     3, 'Normal',
                     4, 'High',
                     5, 'Extremely High')
	FROM mgmt_targets t,         
		   mgmt_cs_config_standard cs,
       mgmt_cs_rule r,
       mgmt_cs_eval_summ_rule e,
       (select message, message_id from mgmt_messages
       where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') rnem,
       (select message, message_id from mgmt_messages
       where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') cnem
  WHERE t.target_guid = e.target_guid 
    AND r.rule_guid = e.rule_guid 
    AND r.rule_dname = rnem.message_id(+) 
    AND cs.cs_dname = cnem.message_id(+) 
    AND r.cs_guid = cs.cs_guid
WITH READ ONLY;