PROMPT CREATE OR REPLACE PACKAGE multi_site

CREATE OR REPLACE PACKAGE multi_site
AS

FUNCTION get_auto_user
  (airport IN site_info.airport_code%TYPE)
   RETURN tagtrak_users.user_id%TYPE;

FUNCTION get_auto_loginname
  (airport IN site_info.airport_code%TYPE)
   RETURN tagtrak_users.loginname%TYPE;

FUNCTION get_auto_location
  (p_airport IN site_info.airport_code%TYPE)
   RETURN location.location_code%TYPE;

FUNCTION get_agent_code
  (p_loginname IN tagtrak_users.loginname%TYPE)
   RETURN tagtrak_users.agent_code%TYPE;

FUNCTION get_auto_user_id
   ( p_loginname    IN tagtrak_users.loginname%TYPE
   )RETURN tagtrak_users.user_id%TYPE;

FUNCTION get_airport_code
   (  p_siteid        IN  site_info.site_id%type
   ) RETURN site_info.airport_code%type;
PRAGMA RESTRICT_REFERENCES(get_airport_code, wnds, wnps, rnps);

FUNCTION get_site_id
   (  p_airport_code  IN  site_info.airport_code%type
   ) RETURN site_info.site_id%type;
PRAGMA RESTRICT_REFERENCES(get_site_id, wnds, wnps, rnps);

FUNCTION get_agent_loc
   ( p_agent_code   IN agents.agent_code%type
   )RETURN site_info.airport_code%type;
PRAGMA RESTRICT_REFERENCES(get_agent_loc, wnds, wnps, rnps);

FUNCTION get_user_loc
   ( p_userid   IN tagtrak_users.user_id%type
   )RETURN site_info.airport_code%type;
PRAGMA RESTRICT_REFERENCES(get_user_loc, wnds, wnps, rnps);

FUNCTION get_flight_loc
   ( p_fid   IN flight.flight_id%type
   )RETURN site_info.airport_code%type;
PRAGMA RESTRICT_REFERENCES(get_flight_loc, wnds, wnps, rnps);

FUNCTION get_loginname_loc
   ( p_name   IN tagtrak_users.loginname%type
   )RETURN site_info.airport_code%type;
PRAGMA RESTRICT_REFERENCES(get_loginname_loc, wnds, wnps, rnps);

FUNCTION get_agent_siteid
   ( p_agent_code   IN agents.agent_code%type
   )RETURN site_info.site_id%type;
PRAGMA RESTRICT_REFERENCES(get_agent_siteid, wnds, wnps, rnps);

FUNCTION get_user_siteid
   ( p_userid   IN tagtrak_users.user_id%type
   )RETURN site_info.site_id%type;
PRAGMA RESTRICT_REFERENCES(get_user_siteid, wnds, wnps, rnps);

FUNCTION get_site_config
   ( p_key   IN site_config.db_item%type,
     psite_id IN site_config.site_id%type
   )RETURN site_config.db_value%type;
PRAGMA RESTRICT_REFERENCES(get_site_config, wnds, wnps, rnps);

FUNCTION get_user_id
  ( p_key    IN site_config.db_value%type
   )RETURN tagtrak_users.user_id%type;
PRAGMA RESTRICT_REFERENCES(get_user_id, wnds, wnps, rnps);

END multi_site;
/
PROMPT CREATE OR REPLACE PACKAGE BODY multi_site

CREATE OR REPLACE PACKAGE BODY multi_site
AS

FUNCTION get_auto_user
   ( airport in site_info.airport_code%TYPE
   )RETURN tagtrak_users.user_id%TYPE
IS
   l_user tagtrak_users.user_id%TYPE;
BEGIN
   SELECT user_id INTO l_user FROM tagtrak_users WHERE loginname = get_auto_loginname(airport);
   RETURN l_user;
EXCEPTION
   WHEN no_data_found THEN
      RETURN 1;   -- Default is TTADMIN user
END get_auto_user;


FUNCTION get_auto_loginname
   ( airport in site_info.airport_code%TYPE
   )RETURN tagtrak_users.loginname%TYPE
IS
   l_login tagtrak_users.loginname%TYPE;
BEGIN
   SELECT loginname INTO l_login FROM tagtrak_users WHERE TRIM(loginname) = 'AUTO' || Upper(airport);
   RETURN l_login;
EXCEPTION
   WHEN no_data_found THEN
      RETURN NULL;
END get_auto_loginname;


FUNCTION get_auto_location
   ( p_airport    IN site_info.airport_code%TYPE
   )RETURN location.location_code%TYPE
IS
   loc  location.location_code%TYPE;
BEGIN
   select  location_code
     into  loc
     from  location
    where  TRIM(location_code)  = 'AU' || p_airport;
    RETURN loc;
EXCEPTION
    when no_data_found then
         loc := NULL;
         RETURN loc;
END get_auto_location;

FUNCTION get_auto_user_id
   ( p_loginname    IN tagtrak_users.loginname%type
   )RETURN tagtrak_users.user_id%type
IS
   userid  tagtrak_users.user_id%type;
BEGIN
   select  user_id
     into  userid
     from  tagtrak_users
    where  loginname  = p_loginname;
    RETURN userid;
EXCEPTION
    when no_data_found then
         userid := NULL;
         RETURN userid;
END get_auto_user_id;

FUNCTION get_agent_code (p_loginname IN tagtrak_users.loginname%TYPE)
  RETURN tagtrak_users.agent_code%TYPE
  IS
  r_agent_code tagtrak_users.agent_code%TYPE;
  BEGIN
       SELECT
           agent_code
       INTO
           r_agent_code
       FROM
           tagtrak_users
       WHERE
           loginname = p_loginname;
       RETURN r_agent_code;
  EXCEPTION
       WHEN no_data_found THEN
            r_agent_code := NULL;
            return r_agent_code;
  END get_agent_code;

FUNCTION get_airport_code
   (  p_siteid        IN  site_info.site_id%type
   )RETURN site_info.airport_code%type
IS
   depcode  site_info.airport_code%type default null;
BEGIN
   select  airport_code
     into  depcode
     from  site_info
    where  site_id = p_siteid;
    RETURN depcode;
EXCEPTION
    when no_data_found then
         RETURN depcode;
    when others then
         RETURN depcode;
END get_airport_code;

FUNCTION get_site_id
   ( p_airport_code  IN  site_info.airport_code%type
   )RETURN site_info.site_id%type
IS
   airport_id  site_info.site_id%type default -502;
BEGIN
   select site_id
     into airport_id
     from site_info
    where airport_code = p_airport_code;
    RETURN airport_id;
EXCEPTION
    when no_data_found then
         RETURN airport_id;
    when others then
         RETURN airport_id;
END get_site_id;

FUNCTION get_agent_loc
   ( p_agent_code   IN agents.agent_code%type
   ) RETURN site_info.airport_code%type
IS
   depcode  site_info.airport_code%type default null;
BEGIN
   select airport_code
     into depcode
     from agents a, site_info si
    where agent_code     = p_agent_code
      and a.site_id      = si.site_id;
    RETURN depcode;
EXCEPTION
    when no_data_found then
         RETURN depcode;
    when others then
          RETURN depcode;
END get_agent_loc;

FUNCTION get_user_loc
   ( p_userid   IN tagtrak_users.user_id%type
   )RETURN site_info.airport_code%type
IS
   depcode  site_info.airport_code%type default null;
BEGIN
   select airport_code
      into depcode
      from tagtrak_users t,
           agents a,
           site_info si
     where t.agent_code = a.agent_code
       and a.site_id    = si.site_id
       and t.user_id    = p_userid;
     RETURN depcode;
EXCEPTION
    when no_data_found then
         RETURN depcode;
    when others then
          RETURN depcode;
END get_user_loc;

FUNCTION get_flight_loc
   ( p_fid   IN flight.flight_id%type
   )RETURN site_info.airport_code%type
IS
   depcode  site_info.airport_code%type default null;
BEGIN
    select airport_code
      into depcode
      from flight f,
           site_info si
     where f.site_id   = si.site_id
       and f.flight_id = p_fid;
     RETURN depcode;
EXCEPTION
    when no_data_found then
         RETURN depcode;
    when others then
          RETURN depcode;
END get_flight_loc;

FUNCTION get_loginname_loc
   ( p_name   IN tagtrak_users.loginname%type
   )RETURN site_info.airport_code%type
IS
   depcode  site_info.airport_code%type default null;
BEGIN
    select airport_code
      into depcode
      from tagtrak_users t,
           agents a,
           site_info si
     where t.loginname   = p_name
       and t.agent_code  = a.agent_code
       and a.site_id     = si.site_id;
     RETURN depcode;
EXCEPTION
    when no_data_found then
         RETURN depcode;
    when others then
          RETURN depcode;
END get_loginname_loc;

FUNCTION get_agent_siteid
   ( p_agent_code   IN agents.agent_code%type
   )RETURN site_info.site_id%type
IS
   vsiteid  agents.site_id%type;
BEGIN
   select site_id
     into vsiteid
     from agents
    where agent_code = p_agent_code;
    RETURN vsiteid;
EXCEPTION
    when no_data_found then
         RETURN vsiteid;
    when others then
         RETURN vsiteid;
END get_agent_siteid;

FUNCTION get_user_siteid
   ( p_userid   IN tagtrak_users.user_id%type
   )RETURN site_info.site_id%type
IS
   vsiteid  agents.site_id%type;
BEGIN
   select site_id
     into vsiteid
     from agents a , tagtrak_users t
    where a.agent_code = t.agent_code
      and t.user_id    = p_userid;
    RETURN vsiteid;
EXCEPTION
    when no_data_found then
         RETURN vsiteid;
    when others then
         RETURN vsiteid;
END get_user_siteid;

FUNCTION get_site_config
   ( p_key    IN site_config.db_item%type,
     psite_id IN site_config.site_id%type
   )RETURN site_config.db_value%type
IS
   ret_value  site_config.db_value%type;
BEGIN
   select  db_value
     into  ret_value
     from  site_config
    where  db_item = p_key
      and  site_id = psite_id;
    RETURN ret_value;
EXCEPTION
    when no_data_found then
         ret_value := NULL;
         RETURN ret_value;
END get_site_config;

FUNCTION get_user_id
   ( p_key    IN site_config.db_value%type
   )RETURN tagtrak_users.user_id%type
IS
   userid  tagtrak_users.user_id%type;
BEGIN
   select  user_id
     into  userid
     from  tagtrak_users
    where  trim(agent_code) = trim(p_key)
      and  trim(loginname)  = trim(p_key)
      and  trim(group_name) = trim(p_key);
    RETURN userid;
EXCEPTION
    when no_data_found then
         userid := NULL;
         RETURN userid;
END get_user_id;

END multi_site;
/
show errors

