Rem
Rem $Header: ip_serv_pkgbody.sql 17-jul-2007.22:09:51 gankrish Exp $
Rem
Rem ip_serv_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ip_serv_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    gankrish    07/11/07 - Update for DHTML type tests
Rem    saramasa    07/22/06 - Backport saramasa_bug-5389013 from main 
Rem    gankrish    07/11/06 - Backport saramasa_bug-5027421 from main 
Rem    saramasa    07/20/06 - Fix for 5389013 - HTTP_PING metric name is 
Rem                           http_response 
Rem    saramasa    05/12/06 - Trunc date diff, to fix bug# 5027421  
Rem    pmaddi      07/31/05 - pmaddi_bug-4523007
Rem    pmaddi      07/29/05 - header removed 
Rem    pmaddi      07/21/05 - Created
Rem

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

CREATE OR REPLACE PACKAGE BODY ip_svctest AS

PROCEDURE get_service_tests(svc_tests_cursor_out OUT CURSORTYPE,
            target_guid_in IN RAW ) IS
begin
	OPEN svc_tests_cursor_out for
	SELECT distinct mbtd.txn_type, mtm.metric_name, test.label,test.nlsid 
    FROM  mgmt_test_metrics mtm, mgmt_test test, mgmt_bcn_txn_defn mbtd, mgmt_targets mt
    WHERE
	mbtd.target_guid = target_guid_in
	AND mt.target_guid = mbtd.target_guid
	AND mtm.test_type = mbtd.txn_type
	AND test.test_type = mtm.test_type
	and (mtm.test_type<>'DHTML' OR (mtm.test_type='DHTML' and mtm.metric_name IN ('dhtml_response')))
	and (mtm.test_type<>'HTTPPING' OR (mtm.test_type='HTTPPING' and mtm.metric_name IN ('http_response')))
	and (mtm.test_type<>'HTTP' OR (mtm.test_type='HTTP' and (mtm.metric_name IN ('http_response'))));
end get_service_tests;

PROCEDURE get_test_metrics(met_tests_cursor_out OUT CURSORTYPE,
            target_guid_in IN RAW,
						test_types_in IN SMP_EMD_STRING_ARRAY,
            metric_names_in IN SMP_EMD_STRING_ARRAY,
            metric_columns_in IN SMP_EMD_STRING_ARRAY default null) IS
begin
	OPEN met_tests_cursor_out for
	SELECT distinct mm.metric_name, column_label,column_label_nlsid, mm.metric_column, display_order
	FROM mgmt_metrics mm, mgmt_targets mt
   , mgmt_test_mcolumns cols_order
      WHERE 
      mt.target_guid = target_guid_in
      and 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 mm.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_NUMBER
      and mm.metric_name IN (SELECT * FROM TABLE(CAST(metric_names_in AS SMP_EMD_STRING_ARRAY)))
			and cols_order.test_type IN (SELECT * FROM TABLE(CAST(test_types_in AS SMP_EMD_STRING_ARRAY)))
      and cols_order.metric_name  = mm.metric_name 
      and ( (--this case handles web transaction test type
            (metric_columns_in is not null) and (mm.metric_column IN(SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY))))
            )
         or
         (--this case handles all test types other than web transaction test type
            (metric_columns_in is null) and (mm.metric_column NOT LIKE '%status' )
         )
        )
      and cols_order.metric_column = mm.metric_column
      and mm.key_order = 0
      order by display_order asc;
end get_test_metrics;

PROCEDURE get_test_data(test_bcns_summary_cursor_out OUT CURSORTYPE,
							target_guid_in IN RAW,
							txn_guid_in IN RAW default null,
							start_date_in IN DATE,
							end_date_in IN DATE,
							custom_date_type_in IN NUMBER,
							test_type_in IN VARCHAR,
							is_key_tst_type_in in NUMBER,
							is_key_bcn_type_in in NUMBER,
							metric_name_in IN VARCHAR,
			        metric_columns_in IN SMP_EMD_STRING_ARRAY) IS
l_target_date      DATE;
days_in            NUMBER;
begin
  if(custom_date_type_in = 1)  then
           select MGMT_TARGET.SYSDATE_TARGET(target_guid_in) into l_target_date from dual;
           days_in := l_target_date - start_date_in;
  else
           days_in := end_date_in  - start_date_in;
  end if;

  days_in := trunc(days_in); --To Fix the bug 5027421

   if(days_in<=1) then
         OPEN test_bcns_summary_cursor_out for            
			   SELECT txn_name, bcn_name, metric_column,round(avg(value),2) value,txn_guid           
             from (SELECT txn_bcns.target_guid , txn_name, bcn_name, txn_guid, composite_key
             FROM mgmt_metrics_composite_keys ck,(
	             SELECT txns.target_guid, txn_guid,bcn_name, txns.name txn_name 
                 FROM mgmt_bcn_txn_defn txns,  (SELECT b.target_guid, mt1.target_name as bcn_name
																				FROM
																				mgmt_bcn_target b,  mgmt_targets mt1 
																				 WHERE 
																					b.target_guid = target_guid_in
																			    and b.beacon_target_guid = mt1.target_guid 
																					and ((is_key_bcn_type_in<>1) or
																						(is_key_bcn_type_in=1 and participates_avail='Y')
																						)
																					) b
										WHERE 
                     txns.target_guid = target_guid_in
										 and b.target_guid = txns.target_guid
                     and txn_type= test_type_in
                     and ((is_key_tst_type_in<>1) or
                              (
                              (is_key_tst_type_in=1 and is_representative='Y')                              
                              )
                     )
                     and ((txn_guid_in is null) OR (txn_guid = txn_guid_in))
                     )txn_bcns
             WHERE 
                ck.target_guid = target_guid_in
                and ck.target_guid = txn_bcns.target_guid
                and key_part1_value = txn_bcns.txn_name
                and key_part2_value = txn_bcns.bcn_name             								
								and (key_part3_value is null or key_part3_value='')								
             )txn_bcns, (
						 SELECT value, key_value, metric_column
						 FROM 
										(SELECT metric_guid, key_value, value value
											FROM mgmt_metrics_raw met
											WHERE
											met.target_guid = target_guid_in
											and collection_timestamp BETWEEN start_date_in AND end_date_in
					            and value IS NOT NULL
										) met		,(SELECT metric_guid, metric_column 
                      FROM mgmt_metrics mm, mgmt_targets mt
                      WHERE 
											mt.target_guid = target_guid_in
											and metric_name = metric_name_in
											and metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY)))
											and 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 = ' ' )	
                     ) mtm     
										 WHERE met.metric_guid(+) = mtm.metric_guid					 
										 ) met
              WHERE                 
                txn_bcns.composite_key = met.key_value(+)                
            GROUP BY txn_name, bcn_name, metric_column, txn_guid;						
		elsif(days_in >1 and days_in<=7) then
         OPEN test_bcns_summary_cursor_out for
         SELECT txn_name, bcn_name, metric_column,round(avg(value),2) value,txn_guid           
             from (SELECT txn_bcns.target_guid , txn_name, bcn_name, txn_guid, composite_key
             FROM mgmt_metrics_composite_keys ck,(
	             SELECT txns.target_guid, txn_guid,bcn_name, txns.name txn_name 
                 FROM mgmt_bcn_txn_defn txns,  (SELECT b.target_guid, mt1.target_name as bcn_name
																				FROM
																				mgmt_bcn_target b,  mgmt_targets mt1 
																				 WHERE 
																					b.target_guid = target_guid_in
																			    and b.beacon_target_guid = mt1.target_guid 
																					and ((is_key_bcn_type_in<>1) or
																						(is_key_bcn_type_in=1 and participates_avail='Y')
																						)
																					) b
										WHERE 
                     txns.target_guid = target_guid_in
										 and b.target_guid = txns.target_guid
                     and txn_type= test_type_in
                     and ((is_key_tst_type_in<>1) or
                              (
                              (is_key_tst_type_in=1 and is_representative='Y')                              
                              )
                     )
                     and ((txn_guid_in is null) OR (txn_guid = txn_guid_in))
                     )txn_bcns
             WHERE 
                ck.target_guid = target_guid_in
                and ck.target_guid = txn_bcns.target_guid
                and key_part1_value = txn_bcns.txn_name
                and key_part2_value = txn_bcns.bcn_name             								
								and (key_part3_value is null or key_part3_value='')								
             )txn_bcns, (
						 SELECT value, key_value, metric_column
						 FROM 
										(SELECT metric_guid, key_value, value_average value
											FROM mgmt_metrics_1hour met
											WHERE
											met.target_guid = target_guid_in
											and rollup_timestamp BETWEEN start_date_in AND end_date_in
					            and value_average IS NOT NULL
										) met		,(SELECT metric_guid, metric_column 
                      FROM mgmt_metrics mm, mgmt_targets mt
                      WHERE 
											mt.target_guid = target_guid_in
											and metric_name = metric_name_in
											and metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY)))
											and 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 = ' ' )	
                     ) mtm     
										 WHERE met.metric_guid(+) = mtm.metric_guid					 
										 ) met
              WHERE                 
                txn_bcns.composite_key = met.key_value(+)                
            GROUP BY txn_name, bcn_name, metric_column, txn_guid;	
		else
         OPEN test_bcns_summary_cursor_out for
         SELECT txn_name, bcn_name, metric_column,round(avg(value),2) value,txn_guid           
             from (SELECT txn_bcns.target_guid , txn_name, bcn_name, txn_guid, composite_key
             FROM mgmt_metrics_composite_keys ck,(
	             SELECT txns.target_guid, txn_guid,bcn_name, txns.name txn_name 
                 FROM mgmt_bcn_txn_defn txns,  (SELECT b.target_guid, mt1.target_name as bcn_name
																				FROM
																				mgmt_bcn_target b,  mgmt_targets mt1 
																				 WHERE 
																					b.target_guid = target_guid_in
																			    and b.beacon_target_guid = mt1.target_guid 
																					and ((is_key_bcn_type_in<>1) or
																						(is_key_bcn_type_in=1 and participates_avail='Y')
																						)
																					) b
										WHERE 
                     txns.target_guid = target_guid_in
										 and b.target_guid = txns.target_guid
                     and txn_type= test_type_in
                     and ((is_key_tst_type_in<>1) or
                              (
                              (is_key_tst_type_in=1 and is_representative='Y')                              
                              )
                     )
                     and ((txn_guid_in is null) OR (txn_guid = txn_guid_in))
                     )txn_bcns
             WHERE 
                ck.target_guid = target_guid_in
                and ck.target_guid = txn_bcns.target_guid
                and key_part1_value = txn_bcns.txn_name
                and key_part2_value = txn_bcns.bcn_name             								
								and (key_part3_value is null or key_part3_value='')								
             )txn_bcns, (
						 SELECT value, key_value, metric_column
						 FROM 
										(SELECT metric_guid, key_value, value_average value
											FROM mgmt_metrics_1day met
											WHERE
											met.target_guid = target_guid_in
											and rollup_timestamp BETWEEN start_date_in AND end_date_in
					            and value_average  IS NOT NULL
										) met		,(SELECT metric_guid, metric_column 
                      FROM mgmt_metrics mm, mgmt_targets mt
                      WHERE 
											mt.target_guid = target_guid_in
											and metric_name = metric_name_in
											and metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY)))
											and 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 = ' ' )	
                     ) mtm     
										 WHERE met.metric_guid(+) = mtm.metric_guid					 
										 ) met
              WHERE                 
                txn_bcns.composite_key = met.key_value(+)                
            GROUP BY txn_name, bcn_name, metric_column, txn_guid;	
		end if; 
END get_test_data;

end ip_svctest;
/
show errors;