Rem
Rem $Header: webapp_views.sql 02-aug-2005.21:33:24 saramasa Exp $
Rem
Rem webapp_views.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      webapp_views.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    saramasa    08/02/05 - Moved from beta 
Rem    pmaddi      04/12/05 - Adding appropriate comments for transaction perf 
Rem                           views. 
Rem    pmaddi      03/29/05 - 
Rem    pmaddi      03/24/05 - Modifying transaction metrics query to collect 
Rem                           the correct metric value. 
Rem    chyu        03/18/05 - removing the echo off 
Rem    pmaddi      01/30/05 - Adding outer joins againt mgmt_metrics and 
Rem                           mgmt_targets tables for transaction performace 
Rem                           queries. 
Rem    pmaddi      01/26/05 - pmaddi_reports_trans
Rem    pmaddi      12/02/04 - Created
Rem

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


--Purpose: View for Reporting E2E data for rollup time from 7 days till 31 days
create or replace view SYSMAN.MGMT$E2E_1Day AS SELECT * FROM (
SELECT uri, hit_count, total_time, servlet_count, 
servlet_time, jsp_count, jsp_time, ejb_count, ejb_time, jdbc_time,
mt.target_guid,rollup_timestamp
FROM      mgmt_e2e_summary_1day e2e,mgmt_target_assocs mta,
mgmt_target_assoc_defs mdef, mgmt_targets mt
WHERE      
e2e.target_guid = mta.assoc_target_guid
AND source_target_guid = mt.target_guid
AND mta.assoc_guid = mdef.assoc_guid
AND mdef.assoc_def_name = 'depends_on'
AND mdef.scope_target_type = ' '); 

CREATE OR REPLACE PUBLIC SYNONYM MGMT$E2E_1Day FOR SYSMAN.MGMT$E2E_1Day;
GRANT SELECT ON  SYSMAN.MGMT$E2E_1Day TO MGMT_USER;

--Purpose: View for Reporting E2E data for rollup time from 24 hours to  last 7 days
create or replace view SYSMAN.MGMT$E2E_hourly AS SELECT * FROM (
SELECT uri, hit_count, total_time, servlet_count, 
servlet_time, jsp_count, jsp_time, ejb_count, ejb_time, jdbc_time,
mt.target_guid,rollup_timestamp
FROM   mgmt_e2e_summary_1hour e2e,mgmt_target_assocs mta,
mgmt_target_assoc_defs mdef, mgmt_targets mt
WHERE      
e2e.target_guid = mta.assoc_target_guid
AND source_target_guid = mt.target_guid
AND mta.assoc_guid = mdef.assoc_guid
AND mdef.assoc_def_name = 'depends_on'
AND mdef.scope_target_type = ' '); 

CREATE OR REPLACE PUBLIC SYNONYM MGMT$E2E_hourly FOR SYSMAN.MGMT$E2E_hourly;
GRANT SELECT ON  SYSMAN.MGMT$E2E_hourly TO MGMT_USER;


--Purpose: View for Reporting E2E data for rollup time less than 24 hours

create or replace view SYSMAN.MGMT$E2E_Raw AS SELECT * FROM (
SELECT uri, hit_count, total_time, servlet_count, 
servlet_time, jsp_count, jsp_time, ejb_count, ejb_time, jdbc_time,
mt.target_guid,collection_timestamp as rollup_timestamp
FROM  mgmt_e2e_summary e2e,mgmt_target_assocs mta,
mgmt_target_assoc_defs mdef, mgmt_targets mt
WHERE      
e2e.target_guid = mta.assoc_target_guid
AND source_target_guid = mt.target_guid
AND mta.assoc_guid = mdef.assoc_guid
AND mdef.assoc_def_name = 'depends_on'
AND mdef.scope_target_type = ' '); 

CREATE OR REPLACE PUBLIC SYNONYM MGMT$E2E_Raw FOR SYSMAN.MGMT$E2E_Raw;
GRANT SELECT ON  SYSMAN.MGMT$E2E_Raw TO MGMT_USER;


--Purpose of this view is to list out all the transaction names, beacons 
-- along with the transaction specific ('http_response' type) metrics. If there are no metrics available 
-- then the outer join of txn_bcns 'inner table' against mgmt$txn_metrics_raw
-- should ensure listing of all transaction names and beacons for all targets, with the 
-- corresponding metric values as nulls.
create or replace view mgmt$txn_perf_raw as 
SELECT 
			met.target_guid, txn_name, met.target_type, bcn_name, is_representative, participates_avail, 
			met.column_label, txn_guid, met.metric_column, met.value, met.collection_timestamp
	FROM 
		(
			select 
				met.value value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,collection_timestamp
			from 
				mgmt_metrics_raw met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT 
						metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,
						mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_response'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid		
)met, 
	(SELECT b.target_guid target_guid,mt1.target_name as bcn_name,txns.name txn_name,
	   is_representative ,txn_guid,txns.target_guid txn_target_guid,
           b.participates_avail as participates_avail
	      FROM mgmt_bcn_target b,
	      mgmt_bcn_txn_defn txns,
		   mgmt_targets mt1
	     WHERE b.beacon_target_guid = mt1.target_guid
	       AND mt1.target_type = 'oracle_beacon' ) txn_bcns		   
WHERE txn_bcns.target_guid = met.target_guid(+)
AND txn_bcns.txn_target_guid = met.target_guid(+)
AND met.key_part1_value(+) = txn_bcns.txn_name
AND met.key_part2_value(+) = txn_bcns.bcn_name 
AND (met.key_part3_value=' ' or met.key_part3_value is null);

CREATE OR REPLACE PUBLIC SYNONYM mgmt$txn_perf_raw FOR sysman.mgmt$txn_perf_raw ;
GRANT SELECT ON  sysman.mgmt$txn_perf_raw TO MGMT_USER;


--Purpose of this view is to list out all the transaction names, beacons 
-- along with the transaction specific ('http_response' type) metrics. If there are no metrics available 
-- then the outer join of txn_bcns 'inner table' against mgmt$txn_metrics_hourly
-- should return all transaction names and beacons for all targets, with the 
-- corresponding metric values as null.
create or replace view mgmt$txn_perf_hour as 
SELECT 
			met.target_guid, txn_name, met.target_type, bcn_name, is_representative, participates_avail, 
			met.column_label, txn_guid, met.metric_column, met.value, met.collection_timestamp
	FROM 
		(
			select 
				met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1hour met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT 
						metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,
						mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_response'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid		
)met, 
	(SELECT b.target_guid target_guid,mt1.target_name as bcn_name,txns.name txn_name,
	   is_representative ,txn_guid,txns.target_guid txn_target_guid,
           b.participates_avail as participates_avail
	      FROM mgmt_bcn_target b,
	      mgmt_bcn_txn_defn txns,
		   mgmt_targets mt1
	     WHERE b.beacon_target_guid = mt1.target_guid
	       AND mt1.target_type = 'oracle_beacon' ) txn_bcns		   
WHERE txn_bcns.target_guid = met.target_guid(+)
AND txn_bcns.txn_target_guid = met.target_guid(+)
AND met.key_part1_value(+) = txn_bcns.txn_name
AND met.key_part2_value(+) = txn_bcns.bcn_name 
AND (met.key_part3_value=' ' or met.key_part3_value is null);

CREATE OR REPLACE PUBLIC SYNONYM mgmt$txn_perf_hour FOR sysman.mgmt$txn_perf_hour ;
GRANT SELECT ON  sysman.mgmt$txn_perf_hour TO MGMT_USER;

create or replace view mgmt$txn_perf_day as 
SELECT 
			met.target_guid, txn_name, met.target_type, bcn_name, is_representative, participates_avail, 
			met.column_label, txn_guid, met.metric_column, met.value, met.collection_timestamp
	FROM 
		(
			select 
				met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1day met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT 
						metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,
						mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_response'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid		
)met, 
	(SELECT b.target_guid target_guid,mt1.target_name as bcn_name,txns.name txn_name,
	   is_representative ,txn_guid,txns.target_guid txn_target_guid,
           b.participates_avail as participates_avail
	      FROM mgmt_bcn_target b,
	      mgmt_bcn_txn_defn txns,
		   mgmt_targets mt1
	     WHERE b.beacon_target_guid = mt1.target_guid
	       AND mt1.target_type = 'oracle_beacon' ) txn_bcns		   
WHERE txn_bcns.target_guid = met.target_guid(+)
AND txn_bcns.txn_target_guid = met.target_guid(+)
AND met.key_part1_value(+) = txn_bcns.txn_name
AND met.key_part2_value(+) = txn_bcns.bcn_name 
AND (met.key_part3_value=' ' or met.key_part3_value is null);

CREATE OR REPLACE PUBLIC SYNONYM mgmt$txn_perf_day FOR sysman.mgmt$txn_perf_day ;
GRANT SELECT ON  sysman.mgmt$txn_perf_day TO MGMT_USER;

--Purpose of this view is to get all the steps in a transaction
create or replace view mgmt$steps
as
select txns.name as txn_name,bcn_name, steps.name step_name, txns.target_guid,txns.txn_guid
from mgmt_bcn_txn_defn txns,
     mgmt_bcn_step_defn steps,(select b.target_guid target_guid,mt1.target_name as bcn_name
					from mgmt_bcn_target b,mgmt_targets mt1
					where b.beacon_target_guid = mt1.target_guid
					and mt1.target_type = 'oracle_beacon') bcns
where 
steps.txn_guid = txns.txn_guid
and bcns.target_guid = txns.target_guid
and txns.target_guid = steps.target_guid
and txn_type='HTTP';



CREATE OR REPLACE PUBLIC SYNONYM mgmt$steps FOR sysman.mgmt$steps;
GRANT SELECT ON sysman.mgmt$steps TO MGMT_USER;

--Purpose of this function is to concat all the steps in a step group for the tranaction.
create OR replace function concat_steps(target_guid_in in raw,
			txn_guid_in in raw,
			stp_grp_guid_in in raw)
  return varchar2 is
      l_str  varchar2(2000) default null;
      l_sep  varchar2(2) default null;
  begin
      for x in ( select steps.name name from mgmt_bcn_step_defn steps,mgmt_bcn_stepgroup_steps stlink
      where 
      stlink.target_guid = target_guid_in
      AND steps.target_guid = stlink.target_guid
      AND stlink.txn_guid = txn_guid_in
      AND stlink.txn_guid = steps.txn_guid
      AND stepgroup_guid = stp_grp_guid_in
      AND steps.step_guid = stlink.step_guid 
      order by steps.name asc
      ) loop
          l_str := l_str || l_sep || x.name;
	  l_sep := ', ';
      end loop;
      return l_str;
  end;
/

--Purpose of this view is to get all the step groups and the steps involved in a group for the transaction
create or replace view mgmt$step_groups
as
select txns.name as txn_name,bcn_name, 	txns.target_guid,txns.txn_guid,
	stgrp.name || ' ('||concat_steps(txns.target_guid,txns.txn_guid,stepgroup_guid)||')' grp_name
from mgmt_bcn_txn_defn txns,mgmt_bcn_stepgroup_defn stgrp,
     (select b.target_guid target_guid,mt1.target_name as bcn_name
	from mgmt_bcn_target b,mgmt_targets mt1
	where b.beacon_target_guid = mt1.target_guid
	and mt1.target_type = 'oracle_beacon') bcns
where 
bcns.target_guid = txns.target_guid
and stgrp.target_guid = txns.target_guid
and stgrp.txn_guid = txns.txn_guid
and stgrp.target_guid = txns.target_guid
and txn_type='HTTP';
CREATE OR REPLACE PUBLIC SYNONYM mgmt$step_groups FOR sysman.mgmt$step_groups;
GRANT SELECT ON sysman.mgmt$step_groups TO MGMT_USER;


-- Views for Latest Metric Performance details 


create or replace view mgmt$step_metrics_raw as (
	select step_name, value, metric_column,column_label,
	column_label_nlsid,met.target_type target_type,
	txn_name ,bcn_name,txn_guid,txn_steps.target_guid,collection_timestamp
	from mgmt$steps txn_steps, (
      select met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1hour met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,
						mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_page'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid	
      ) met
	where 
	met.key_part1_value(+) = txn_steps.txn_name
	and met.key_part2_value(+) = txn_steps.bcn_name
	and met.key_part3_value(+) = txn_steps.step_name
	and txn_steps.target_guid = met.target_guid(+)	
);

create or replace view mgmt$grp_metrics_raw as (
	select grp_name, value,	metric_column,column_label,
	column_label_nlsid,met.target_type target_type,
	txn_name ,bcn_name,txn_guid,stp_groups.target_guid,collection_timestamp
	from mgmt$step_groups stp_groups, (
      select met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1hour met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_uagroup'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid	
      ) met
	where stp_groups.target_guid = met.target_guid(+)
	and met.key_part1_value(+) = stp_groups.txn_name
	and met.key_part2_value(+) = stp_groups.bcn_name
	and stp_groups.grp_name = met.key_part3_value(+)
);

CREATE OR REPLACE PUBLIC SYNONYM mgmt$step_metrics_raw FOR sysman.mgmt$step_metrics_raw;
GRANT SELECT ON  mgmt$step_metrics_raw TO MGMT_USER;
CREATE OR REPLACE PUBLIC SYNONYM mgmt$grp_metrics_raw FOR sysman.mgmt$grp_metrics_raw;
GRANT SELECT ON  mgmt$grp_metrics_raw TO MGMT_USER;


-- Purpose of this view is to get all the step metrics.
create or replace view mgmt$step_metrics_hourly as (
	select step_name, value,
	 metric_column,column_label,
	column_label_nlsid,met.target_type target_type,
	txn_name ,bcn_name,txn_guid,txn_steps.target_guid,collection_timestamp
	from mgmt$steps txn_steps, (
      select met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1hour met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_page'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid		
      ) met
	where 
	met.key_part1_value(+) = txn_steps.txn_name
	and met.key_part2_value(+) = txn_steps.bcn_name
	and met.key_part3_value(+) = txn_steps.step_name
	and txn_steps.target_guid = met.target_guid(+)	
);

--Purpose of this view is to get all the group metrics.
create or replace view mgmt$grp_metrics_hourly as (
select grp_name,  value,	 metric_column,column_label,
	column_label_nlsid,met.target_type target_type,
	txn_name ,bcn_name,txn_guid,stp_groups.target_guid,
	collection_timestamp	
	from mgmt$step_groups stp_groups, (
      select met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1hour met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_uagroup'
					 ) mtm
		where 
		ck.target_guid = mtm.target_guid
		and met.key_value=ck.composite_key
		and mtm.metric_guid=met.metric_guid
      ) met
	where stp_groups.target_guid = met.target_guid(+)
	and met.key_part1_value(+) = stp_groups.txn_name
	and met.key_part2_value(+) = stp_groups.bcn_name
	and stp_groups.grp_name = met.key_part3_value(+)
);

CREATE OR REPLACE PUBLIC SYNONYM mgmt$step_metrics_hourly FOR sysman.mgmt$step_metrics_hourly;
GRANT SELECT ON  mgmt$step_metrics_hourly TO MGMT_USER;
CREATE OR REPLACE PUBLIC SYNONYM mgmt$grp_metrics_hourly FOR sysman.mgmt$grp_metrics_hourly;
GRANT SELECT ON  mgmt$grp_metrics_hourly TO MGMT_USER;

-- Metric Performance details Views for last 31 days
create or replace view mgmt$step_metrics_daily as (
	select step_name, value,
	 metric_column,column_label,
	column_label_nlsid,met.target_type target_type,
	txn_name ,bcn_name,txn_guid,txn_steps.target_guid,collection_timestamp
	from mgmt$steps txn_steps, (
      select met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1day met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT 
						metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_page') mtm
      ) met
	where 
	met.key_part1_value(+) = txn_steps.txn_name
	and met.key_part2_value(+) = txn_steps.bcn_name
	and met.key_part3_value(+) = txn_steps.step_name
	and txn_steps.target_guid = met.target_guid(+)	
);

create or replace view mgmt$grp_metrics_daily as (
	select grp_name,  value,	 metric_column,column_label,
	column_label_nlsid,met.target_type target_type,
	txn_name ,bcn_name,txn_guid,stp_groups.target_guid,collection_timestamp
	from mgmt$step_groups stp_groups, (
      select 
				met.value_average value,met.target_guid target_guid,
				mtm.target_type target_type,
				metric_column,column_label,column_label_nlsid,
				ck.key_part1_value,ck.key_part2_value,ck.key_part3_value,rollup_timestamp as collection_timestamp
			from 
				mgmt_metrics_1day met,
				mgmt_metrics_composite_keys ck,
				(
					SELECT 
						metric_column,target_guid,metric_guid ,column_label,column_label_nlsid,mt.target_type
					FROM 
						mgmt_metrics mm,
						mgmt_targets mt
					WHERE 
					 mt.type_meta_ver = mm.type_meta_ver
					 and mm.target_type = mt.target_type
					 and (mt.category_prop_1 = mm.category_prop_1 or mm.category_prop_1 = ' ')
					 and (mt.category_prop_2 = mm.category_prop_2 or mm.category_prop_2 = ' ')
					 and (mt.category_prop_3 = mm.category_prop_3 or mm.category_prop_3 = ' ')
					 and (mt.category_prop_4 = mm.category_prop_4 or mm.category_prop_4 = ' ')
					 and (mt.category_prop_5 = mm.category_prop_5 or mm.category_prop_5 = ' ')
					 and metric_name = 'http_uagroup') mtm
      ) met
	where stp_groups.target_guid = met.target_guid(+)
	and met.key_part1_value(+) = stp_groups.txn_name
	and met.key_part2_value(+) = stp_groups.bcn_name
	and stp_groups.grp_name = met.key_part3_value(+)
);

CREATE OR REPLACE PUBLIC SYNONYM mgmt$step_metrics_daily FOR sysman.mgmt$step_metrics_daily;
GRANT SELECT ON  mgmt$step_metrics_daily TO MGMT_USER;

CREATE OR REPLACE PUBLIC SYNONYM mgmt$grp_metrics_daily FOR sysman.mgmt$grp_metrics_daily;
GRANT SELECT ON  mgmt$grp_metrics_daily TO MGMT_USER;

COMMIT;