PROMPT CREATE OR REPLACE TYPE timezone_maintainance

CREATE OR REPLACE TYPE TIMEZONE_MAINTAINANCE AS OBJECT (

/*****************************************************************************  
*  
* Filename- $Workfile$  
*  
* Project Name - UltraTrak Database  
*  
* Source Code Identifier- 0/3/300/8010/112  
*  
* Description - 
*  
* Language - Oracle PL/SQL  
*  
* File History - For the development history run the Show History report in PVCS  
*  
* (c) Ultra Electronics Limited, 2001  
*  
*****************************************************************************/  

  airport_code CHAR (3), 
  offset NUMBER,
  utc_offset NUMBER, 
  daylight_saving_begins DATE,
  daylight_saving_ends DATE, 
  daylight_saving_offset NUMBER,
  MEMBER PROCEDURE select_timezone (p_airport_code VARCHAR2),
  MEMBER PROCEDURE maintain_timezone
)
/

PROMPT CREATE OR REPLACE TYPE BODY timezone_maintainance

CREATE OR REPLACE TYPE BODY timezone_maintainance IS 

/*****************************************************************************
*
* PL*SQL Member Procedure select_timezone
*
* Description - Populates the object types fields from the timezone table
*               for a given airport code.
*
* CR History:
*               Date      Name        CR Number  CR Description
*
*****************************************************************************/

  MEMBER PROCEDURE select_timezone (p_airport_code VARCHAR2) IS
  BEGIN
    SELECT
      airport_code,
      offset,
      utc_offset,
      daylight_saving_begins,
      daylight_saving_ends,
      daylight_saving_offset
    INTO
      self.airport_code,
      self.offset,
      self.utc_offset,
      self.daylight_saving_begins,
      self.daylight_saving_ends,
      self.daylight_saving_offset
    FROM timezone
    WHERE airport_code = p_airport_code;
  END;

/*****************************************************************************
*
* PL*SQL Member Procedure maintain_timezone
*
* Description - Inserts or updates the timezone table with the objects member
*               field values. The only the utc_offset value is supported. The
*               offset value is calculated by a trigger on the table by using
*               the db_config item 'UTC_OFFSET_DIFF'
*
* CR History:
*               Date      Name        CR Number  CR Description
*
*****************************************************************************/

  MEMBER PROCEDURE maintain_timezone IS
  BEGIN
    INSERT INTO timezone (
      airport_code,
      utc_offset,
      daylight_saving_begins,
      daylight_saving_ends,
      daylight_saving_offset
    )
    VALUES (
      self.airport_code,
      self.utc_offset,
      self.daylight_saving_begins,
      self.daylight_saving_ends,
      self.daylight_saving_offset
    );

  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE timezone
      SET
        utc_offset             = self.utc_offset,
        daylight_saving_begins = self.daylight_saving_begins,
        daylight_saving_ends   = self.daylight_saving_ends,
        daylight_saving_offset = self.daylight_saving_offset
      WHERE airport_code = self.airport_code;
  END;
END;
/

