Rem drv: <migrate type="data_upgrade" version="10.1.0.4" pos="severity_data_upgrade.sql+"/>
Rem
Rem $Header: severity_data_fix_4144032.sql 18-jul-2005.11:00:23 chyu Exp $
Rem
Rem severity_data_fix_4144032.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_data_fix_4144032.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    chyu        07/18/05 - adding the upgrade header 
Rem    rpinnama    01/30/05 - rpinnama_bug-4144032
Rem    rpinnama    01/30/05 - Created
Rem


DECLARE
  l_avl_stat mgmt_availability.current_status%TYPE;
  l_start_time mgmt_availability.start_collection_timestamp%TYPE;
  l_curr_avl_stat mgmt_availability.current_status%TYPE;
  l_last_avl_stat mgmt_availability.current_status%TYPE;
  l_cnt INTEGER  := 0;
  l_blk_cnt INTEGER  := 0;
  l_new_status mgmt_availability.current_status%TYPE;
  CURSOR avail_cursor(c_target_guid RAW) IS
    SELECT a.current_status 
      FROM mgmt_availability a
     WHERE a.target_guid = c_target_guid
       AND a.start_collection_timestamp = (select max(start_collection_timestamp)
                                             FROM mgmt_availability
                                            where target_guid = a.target_guid
                                              AND current_status NOT IN (3, 4, 5))
    order by decode(a.current_status, 3, 1, 4, 1, 5, 1, 2) desc;
BEGIN

  FOR all_tgs IN (select t.target_guid, t.target_name, t.target_type
                    FROM mgmt_targets t, mgmt_metrics m
                   WHERE t.target_type = m.target_type
		     AND t.type_meta_ver = m.type_meta_ver
                     AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
                     AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
                     AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
                     AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
                     AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
                     AND m.metric_name = 'Response'
                     AND m.metric_column = 'Status'
		   ORDER BY t.target_guid )
  LOOP

    -- Get current availability
    BEGIN
      SELECT current_status INTO l_curr_avl_stat
        FROM mgmt_current_availability
       WHERE target_guid = all_tgs.target_guid;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- DBMS_OUTPUT.PUT_LINE('No current avail for target ' ||
        --                  ' Tgt - ' || all_tgs.target_name ||
        --                  ' Typ - ' || all_tgs.target_type );
        l_curr_avl_stat := 6;
    END;

    -- Get last availability transition
    SELECT current_status INTO l_last_avl_stat
      FROM mgmt_availability
     WHERE target_guid = all_tgs.target_guid
       AND end_collection_timestamp IS NULL;

    -- Get blackout status.. Check for atleat one row with blackout_statu = 1
    SELECT count(1) INTO l_blk_cnt
      FROM mgmt_blackout_flat_targets
     WHERE target_guid = all_tgs.target_guid
       AND blackout_status = 1;

    IF  (l_blk_cnt > 0) THEN

      IF ( (l_curr_avl_stat <> 5) AND (l_last_avl_stat <> 5) ) THEN

        -- DBMS_OUTPUT.PUT_LINE('Case 11 : Blackout not started ' ||
        --                  ' Tgt - ' || all_tgs.target_name ||
        --                  ' Typ - ' || all_tgs.target_type ||
        --                  ' Avail - ' || l_last_avl_stat ||
        --                  ' Curr avail - ' || l_curr_avl_stat ||
        --                  ' blk stat - ' || l_blk_cnt);

        NULL;

      ELSIF ( (l_curr_avl_stat = 5) AND (l_last_avl_stat <> 5) ) THEN

        -- DBMS_OUTPUT.PUT_LINE('Case 12 : Fix last avail ' ||
        --                  ' Tgt - ' || all_tgs.target_name ||
        --                  ' Typ - ' || all_tgs.target_type ||
        --                  ' Avail - ' || l_last_avl_stat ||
        --                  ' Curr avail - ' || l_curr_avl_stat ||
        --                  ' blk stat - ' || l_blk_cnt);

        BEGIN
          UPDATE mgmt_availability
	     SET current_status = 5
           WHERE target_guid = all_tgs.target_guid
	     AND current_status = l_last_avl_stat
	     AND end_collection_timestamp IS NULL;
        EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    -- Move the new record by 1 sec to fix the PK violation
            UPDATE mgmt_availability
	       SET current_status = 5,
	           start_collection_timestamp = start_collection_timestamp + (1/(24*60*60))
             WHERE target_guid = all_tgs.target_guid
	       AND current_status = l_last_avl_stat
	       AND end_collection_timestamp IS NULL;
        END;

      ELSIF ( (l_curr_avl_stat <> 5) AND (l_last_avl_stat = 5) ) THEN

        -- DBMS_OUTPUT.PUT_LINE('Case 13 : Fix curr avail ' ||
        --                  ' Tgt - ' || all_tgs.target_name ||
        --                  ' Typ - ' || all_tgs.target_type ||
        --                  ' Avail - ' || l_last_avl_stat ||
        --                  ' Curr avail - ' || l_curr_avl_stat ||
        --                  ' blk stat - ' || l_blk_cnt);

        UPDATE mgmt_current_availability
	   SET current_status = l_last_avl_stat
         WHERE target_guid = all_tgs.target_guid;

      ELSIF ( (l_curr_avl_stat = 5) AND (l_last_avl_stat = 5) ) THEN

        -- Blackout and availability systems are in SYNC.
        NULL;


      END IF;

    ELSE
      -- No blackout
      IF ( (l_curr_avl_stat = 5) AND (l_last_avl_stat = 5) ) THEN

        -- Get the new status
	OPEN avail_cursor(all_tgs.target_guid);
	FETCH avail_cursor INTO l_new_status;

	IF (avail_cursor%NOTFOUND) THEN
	  l_new_status := 6;
	END IF;

	CLOSE avail_cursor;

        -- DBMS_OUTPUT.PUT_LINE('Case 21 : Fix last, curr avail. ' ||
        --                  ' Tgt - ' || all_tgs.target_name ||
        --                  ' Typ - ' || all_tgs.target_type ||
        --                  ' Avail - ' || l_last_avl_stat ||
        --                  ' Curr avail - ' || l_curr_avl_stat ||
        --                  ' New stat ' || l_new_status ||
        --                  ' blk stat - ' || l_blk_cnt);

        UPDATE mgmt_current_availability
	   SET current_status = l_new_status
         WHERE target_guid = all_tgs.target_guid;

        BEGIN
          UPDATE mgmt_availability
	     SET current_status = l_new_status
           WHERE target_guid = all_tgs.target_guid
	     AND current_status = 5
	     AND end_collection_timestamp IS NULL;
        EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    -- Move the new record by 1 sec to fix the PK violation
            UPDATE mgmt_availability
	       SET current_status = l_curr_avl_stat,
	           start_collection_timestamp = start_collection_timestamp + (1/(24*60*60))
             WHERE target_guid = all_tgs.target_guid
	       AND current_status = 5
	       AND end_collection_timestamp IS NULL;
        END;

      ELSIF ( (l_curr_avl_stat = 5) AND (l_last_avl_stat <> 5) ) THEN

        -- DBMS_OUTPUT.PUT_LINE('Case 22 : Fix curr avail ' ||
        --                  ' Tgt - ' || all_tgs.target_guid ||
        --                  ' Typ - ' || all_tgs.target_type ||
        --                  ' Avail - ' || l_last_avl_stat ||
        --                  ' Curr avail - ' || l_curr_avl_stat ||
        --                  ' blk stat - ' || l_blk_cnt);

        UPDATE mgmt_current_availability
	   SET current_status = l_last_avl_stat
         WHERE target_guid = all_tgs.target_guid;

      ELSIF ( (l_curr_avl_stat <> 5) AND (l_last_avl_stat = 5) ) THEN

        -- DBMS_OUTPUT.PUT_LINE('Case 23 : Fix last avail ' ||
        --                  ' Tgt - ' || all_tgs.target_name ||
        --                  ' Typ - ' || all_tgs.target_type ||
        --                  ' Avail - ' || l_last_avl_stat ||
        --                  ' Curr avail - ' || l_curr_avl_stat ||
        --                  ' blk stat - ' || l_blk_cnt);

        BEGIN
          UPDATE mgmt_availability
	     SET current_status = l_curr_avl_stat
           WHERE target_guid = all_tgs.target_guid
	     AND current_status = 5
	     AND end_collection_timestamp IS NULL;
        EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
            UPDATE mgmt_availability
	       SET current_status = l_curr_avl_stat,
	           start_collection_timestamp = start_collection_timestamp + (1/(24*60*60))
             WHERE target_guid = all_tgs.target_guid
	       AND current_status = 5
	       AND end_collection_timestamp IS NULL;
        END;

      ELSE
        -- Blackout and availability systems are in SYNC.
        NULL;

      END IF;

    END IF;

  END LOOP;

END;
/