Rem drv: <create type="pkgbodys" pos="-"/>
Rem
Rem $Header: sdk_global_pkgbody.sql 30-jul-2007.01:27:08 jsadras Exp $
Rem
Rem sdk_global_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_global_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    jsadras     07/21/07 - add timezone compare function
Rem    jsadras     07/30/07 - Backport jsadras_bug-5092508 from main
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    jsadras     03/21/05 - Bug:4148621, remove row_exists 
Rem    mfidanbo    03/18/05 - remove tgt_sysdate 
Rem    mfidanbo    12/01/04 - 
Rem    rzazueta    10/29/03 - Add elapsed_time_msec function 
Rem    tjaiswal    10/07/02 - Add api for comp key guid generation
Rem    aholser     09/21/02 - fix createrep
Rem    rpinnama    09/18/02 - Add check overlap
Rem    rpinnama    09/12/02 - Add timezon translation function
Rem    rpinnama    05/16/02 - fix typo.
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_global AS
  FUNCTION ADJUST_TZ(date_in      DATE,
                     src_tzrgn_in VARCHAR2,
      		     dst_tzrgn_in VARCHAR2)
      RETURN DATE AS
  BEGIN
      return CAST(FROM_TZ(CAST(date_in as TIMESTAMP), 
                          src_tzrgn_in) 
                  AT TIME ZONE dst_tzrgn_in 
		  AS DATE);
  END;

  FUNCTION SYSDATE_TZRGN(tzrgn_in VARCHAR2)
      RETURN DATE AS
  BEGIN
    return CAST(systimestamp at time zone tzrgn_in as DATE);
  END;

  FUNCTION SYSDATE_UTC 
      RETURN DATE AS
    l_tmstmptz TIMESTAMP WITH TIME ZONE;
  BEGIN
    SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) INTO l_tmstmptz FROM DUAL;
    return CAST(l_tmstmptz AS DATE);
  END;


  FUNCTION TO_UTC(date_in      DATE, 
                  src_tzrgn_in VARCHAR2)
      RETURN DATE AS
  BEGIN
    return adjust_tz(date_in, src_tzrgn_in, 'GMT');
  END;

  FUNCTION FROM_UTC(utc_date_in  DATE, 
                    dst_tzrgn_in VARCHAR2)
      RETURN DATE AS
  BEGIN
    return adjust_tz(utc_date_in, 'GMT', dst_tzrgn_in);
  END;

  PROCEDURE SET_OVERLAP
      IS PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION SET ERROR_ON_OVERLAP_TIME=TRUE';
    COMMIT;

    -- DBMS_OUTPUT.PUT_LINE('Altered session to over_lap=true');

    EXCEPTION
      WHEN OTHERS THEN
        NULL;
  END;

  PROCEDURE RESET_OVERLAP
      IS PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION SET ERROR_ON_OVERLAP_TIME=FALSE';
    COMMIT;

    -- DBMS_OUTPUT.PUT_LINE('Altered session to over_lap=true');

    EXCEPTION
      WHEN OTHERS THEN
        NULL;
  END;


  FUNCTION CHECK_OVERLAP(date_in DATE, 
                         tzrgn_in VARCHAR2)
      RETURN INTEGER 
      IS PRAGMA AUTONOMOUS_TRANSACTION;
    l_retVal    INTEGER := 0;
    l_tmstmptz   TIMESTAMP WITH TIME ZONE;
  BEGIN


    SET_OVERLAP;

    BEGIN
      -- 
      -- The select into only seems to be working
      -- If select into is replaced with l_tmstmptz := FROM_TZ
      -- the error is not being thrown.
      -- 
      select FROM_TZ(CAST(date_in AS TIMESTAMP), tzrgn_in) 
        INTO l_tmstmptz 
        from dual;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        IF (SQLCODE = -1883) THEN
          l_retVal := -1;
        END IF;
    END;

    RESET_OVERLAP;

    return l_retVal;

  END;

--
-- GET_COMPOSITE_KEY_GUID function
--
-- PURPOSE: This function returns the composite key guid based on the key 
--          part values in the string array - key_part_values_in
--
FUNCTION GET_COMPOSITE_KEY_GUID(key_part_values_in IN SMP_EMD_STRING_ARRAY)
    RETURN mgmt_metrics_composite_keys.composite_key%TYPE
IS

  l_last_not_null INTEGER := 0;
  l_temp_str VARCHAR2(256) := '';
  l_key_part_value_str VARCHAR2(256) := '';
  -- NOTE: initializing this string to 2560(length of ten key values).
  --       this assumes that the num of part key values in a composite
  --       key does not go beyond 10. if it does, the length of this string
  --       needs to be changed accordingly
  l_comb_key_value_str VARCHAR2(2560) := '';
  
BEGIN

  -- if the string array is null, throw exception
  IF( key_part_values_in IS NULL )
  THEN
    RAISE invalid_params;
  END IF;

  -- first figure out the last not null piece    
  FOR i in 1..key_part_values_in.COUNT
  LOOP
    l_temp_str := trim(key_part_values_in(i));
    IF( l_temp_str IS NOT NULL AND 
        length(l_temp_str) > 0 )
    THEN
      l_last_not_null := i;
    END IF;
  END LOOP;  

  -- use the index of last not null piece in constructing the string
  -- that will be used for the composite key guid generation
  FOR i in 1..l_last_not_null
  LOOP
    l_temp_str := trim(key_part_values_in(i));
    IF( l_temp_str IS NULL OR
        length(l_temp_str) = 0 )
    THEN
      l_key_part_value_str := '';
    ELSE      
      l_key_part_value_str := key_part_values_in(i);
    END IF;

    IF( i <> 1 )
    THEN
      l_comb_key_value_str := l_comb_key_value_str || ';';
    END IF;    
    
    l_comb_key_value_str := l_comb_key_value_str || l_key_part_value_str;
    
  END LOOP;  

  -- generate the composite key guid for the string - l_comb_key_value_str
  RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
           input => UTL_RAW.cast_to_raw(l_comb_key_value_str));
  
END GET_COMPOSITE_KEY_GUID;    

  --
  -- ELAPSED_TIME_MSEC function
  --
  -- PURPOSE: This function returns the elapsed time between two timestamps
  --          in milliseconds.
  --
  FUNCTION ELAPSED_TIME_MSEC(start_timestamp_in TIMESTAMP, end_timestamp_in TIMESTAMP)
    RETURN NUMBER
  IS
    l_interval  INTERVAL DAY TO SECOND;
  BEGIN
    l_interval := end_timestamp_in - start_timestamp_in;
    return ( ( ( EXTRACT(DAY FROM l_interval)*24 +
                 EXTRACT(HOUR FROM l_interval) )*60 +
                 EXTRACT(MINUTE FROM l_interval) )*60 +
                 EXTRACT(SECOND FROM l_interval) )*1000;
  END;
  -- is_equivalent function
  --
  -- PURPOSE:This function returns 1 if the timezones are equivalent else 0
  --
  -- In Parameters:
  -- p_timezone1 : First timezone
  -- p_timezone2 : Second timezone
  --
  -- We do not have a straightforward way of finding the timezone equivalence.
  -- What we do instead is check if the cast of sysdate in the two timezones
    -- retrieve the same time.
  -- For Example;
  -- Cast(systimestamp at time zone 'US/Pacific')  =
  --Cast(systimestamp at time zone 'America/Los_Angeles')
  -- .
  -- To make sure the timezones will give same time across Daylight savings, we
  -- could cast the first day of every month in the timezones and make sure they
  -- will return same date.   

  FUNCTION is_equivalent_tz(p_timezone1 IN VARCHAR2,
                            p_timezone2 IN VARCHAR2)
  RETURN NUMBER 
  IS
  l_date DATE := TRUNC(SYSDATE,'YY') ;
  BEGIN
    IF UPPER(p_timezone1) = UPPER(p_timezone2)
    THEN
      RETURN 1 ;
    END IF ;

    FOR i IN 0..11
    LOOP
      l_date := ADD_MONTHS(l_date,0) ;
      IF CAST(l_date at time zone p_timezone1 AS DATE) !=
         CAST(l_date at time zone p_timezone2 AS DATE) 
      THEN
        RETURN 0 ;
      END IF ;
    END LOOP ;
    RETURN 1 ;
  EXCEPTION
  WHEN OTHERS THEN RETURN 0 ;
  END is_equivalent_tz ;

  --

END mgmt_global;
/

SHOW ERRORS;