Rem
Rem $Header: webapp_views.sql 03-aug-2005.07:40: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/03/05 - saramasa_bug-4528002
Rem    saramasa    08/02/05 - Moved to from v102010
Rem    pmaddi      07/29/05 - header removed 
Rem    saramasa    07/06/05 - saramasa_bug-4435525_col_metrics
Rem    saramasa    07/06/05 - Created
Rem

SET ECHO ON
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;

COMMIT;