PROMPT Creating package site_info_pkg body

CREATE OR REPLACE PACKAGE BODY site_info_pkg AS

/******************************************************************************
 *
 * Local Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * PROCEDURE upd_site_info
 *
 ******************************************************************************/

PROCEDURE upd_site_info 
   (p_site_id                   IN site_info.site_id%TYPE,
    p_site_name                 IN site_info.site_name%TYPE,
    p_timezone_id               IN site_info.timezone_id%TYPE,
    p_approved_security_status  IN site_info.approved_security_status%TYPE) IS


BEGIN

   -- Update the specified row

   UPDATE site_info 
     SET site_name = p_site_name, timezone_id = p_timezone_id, approved_security_status = p_approved_security_status 
     WHERE SITE_ID = p_site_id;

END upd_site_info;

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

FUNCTION fids_enabled_for_site (p_site_id IN site_info.site_id%TYPE)
RETURN VARCHAR2 IS

  n NUMBER;

BEGIN

  SELECT 1 INTO n
  FROM flight_info fi, site_info si
  WHERE fi.airport_code = si.airport_code
  AND si.site_id = p_site_id
  AND rownum = 1;
  
  RETURN 'Y';
  
EXCEPTION WHEN no_data_found THEN

  RETURN 'N';
  
END fids_enabled_for_site;

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------


END site_info_pkg;
/

