Rem
Rem $Header: ip_webapp_pkgdef.sql 29-jul-2005.04:34:31 pmaddi Exp $
Rem
Rem ip_webapp_pkgbody.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ip_webapp_pkgbody.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    pmaddi      07/29/05 - header removed 
Rem    pmaddi      07/28/05 - Modifying step groups and stepd function to make 
Rem                           them static sql 
Rem    pmaddi      07/23/05 - 
Rem    saramasa    07/12/05 - sql voilations fix 
Rem    saramasa    07/05/05 - Metric Column Names SQL Injection bug fix 
Rem                           4435525 
Rem    lkoneti     06/30/05 - To add changes for Time Zone 
Rem    lkoneti     06/27/05 - After making TimeZone changes 
Rem    lkoneti     06/26/05 - For TimeZone Change 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    ratadiko    05/18/05 - ratadiko_bug-4374591
Rem    saramasa    05/10/05 - Created
Rem

CREATE OR REPLACE PACKAGE ip_webapp AS

-- Declaring the cursor for later use.
TYPE CURSORTYPE IS REF CURSOR;

max_rows PLS_INTEGER := 1000;
latency_filter PLS_INTEGER := 60000;
err_maxlen CONSTANT INTEGER := 2048;

 PROCEDURE get_metric_list( target_guid_in IN MGMT_TARGETS.target_guid%TYPE,
                            metric_type_in IN VARCHAR2,
                            metric_cursor_out  OUT CURSORTYPE);

PROCEDURE get_metric_thresholds(v_target_guid IN VARCHAR2,
                           v_metric_guid IN VARCHAR2,
                           v_key_value IN VARCHAR2,
                           v_warning_threshold OUT NUMBER,
                           v_critical_threshold OUT NUMBER);
 
  PROCEDURE get_metric_data(v_metric_data_cur OUT CURSORTYPE,
                           v_target_guid IN VARCHAR2,
                           v_metric_guid IN VARCHAR2,
                           v_key_value IN VARCHAR2,
                           v_start_date IN DATE,
                           v_end_date IN DATE,
                           v_timezone IN VARCHAR2);
 
 PROCEDURE get_avail_for_cigar_chart (v_target_guid IN RAW,
                                      v_start_date IN VARCHAR2,
                                      v_end_date IN VARCHAR2, 
                                      v_timezone IN VARCHAR2,
                                      v_status_det_cur OUT CURSORTYPE);
 
 PROCEDURE get_avail_for_pie_chart (v_target_guid IN RAW,
                                    v_start_date IN VARCHAR2,
                                    v_end_date IN VARCHAR2,
                                    v_timezone in VARCHAR2,
                                    v_status_dur_cur OUT CURSORTYPE);
 
function concat_steps(stpgrp_name in VARCHAR,
											target_guid_in IN RAW,
											txn_guid_in IN RAW,
											stp_grp_guid_in IN RAW)
											return varchar2;

PROCEDURE get_slowurl_data( slowurl_cursor_out OUT CURSORTYPE ,
			    target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            view_by_type_in IN VARCHAR ,
                            min_hits_in IN VARCHAR ,
                            orderby_in  IN VARCHAR ,
                            custom_date_type_in IN NUMBER  ) ;

PROCEDURE get_urlperf_data( urlperf_cursor_out OUT CURSORTYPE ,
			    target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            uri_in IN VARCHAR ,
                            custom_date_type_in IN NUMBER  ) ;

PROCEDURE getRowsByObjAndStat(results_out OUT cursorType ,
                            target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            custom_date_type_in IN NUMBER,
                            target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            stat_type_in IN VARCHAR2,
                            filter_name_in IN VARCHAR2,
                            filter_min_hits_in IN INTEGER ) ;


PROCEDURE getResponseTimeseriesForQuries(results_out OUT cursorType,
                            target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            query_name_in IN SMP_EMD_LONG_STRING_ARRAY,
                            custom_date_type_in IN NUMBER) ;

PROCEDURE getWebappWatchListData(watchlist_cursor OUT cursorType,
                                target_guid_in IN RAW,
                                start_date_in IN date,
                                end_date_in IN date,
                                custom_date_type_in IN NUMBER,
                                targetName           IN VARCHAR2,
                                targetType           IN VARCHAR2,              
                                metric_name          IN VARCHAR2
						  );


PROCEDURE getRowsByObjAndStatLoc( results_out OUT cursorType ,
                            target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            custom_date_type_in IN NUMBER ,
                            target_names_in IN SMP_EMD_STRING_ARRAY,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            stat_type_in IN VARCHAR2,
                            filter_name_in IN VARCHAR2,
                            filter_min_hits_in IN INTEGER ) ;

PROCEDURE get_stepgroup_data(grp_detail_cursor_out OUT CURSORTYPE,
												target_guid_in IN RAW,
												txn_guid_in IN RAW,
												start_date_in IN DATE,
												end_date_in IN DATE,
												custom_date_type_in IN NUMBER,
												test_type_in IN VARCHAR,
												metric_name_in IN VARCHAR,
												metric_columns_in IN SMP_EMD_STRING_ARRAY
												);


PROCEDURE get_step_data(step_detail_cursor_out OUT CURSORTYPE,
												target_guid_in IN RAW,
												txn_guid_in IN RAW,
												start_date_in IN DATE,
												end_date_in IN DATE,
												custom_date_type_in IN NUMBER,
												test_type_in IN VARCHAR,
												metric_name_in IN VARCHAR,
												metric_columns_in IN SMP_EMD_STRING_ARRAY
											);

PROCEDURE get_analyze_data_by_url(
                        filter_data_out            OUT CURSORTYPE,
                        target_name_in             IN  VARCHAR2,
                        target_type_in             IN  VARCHAR2,
                        fixed_url_axis_in          IN  VARCHAR2,
                        filter_axis_in             IN  VARCHAR2,
                        min_coll_time_in           IN  DATE,
                        max_coll_time_in           IN  DATE
                        );

end ip_webapp;
/
show errors;