PROMPT CREATE OR REPLACE FUNCTION is_it_loaded

CREATE OR REPLACE
FUNCTION is_it_loaded
  (p_flight_id  IN flight.flight_id%TYPE
  ,p_bag_tag2a  IN bag.bag_tag2a%TYPE
  ,p_bag_tag2b  IN bag.bag_tag2b%TYPE
  ,p_bag_tag3   IN bag.bag_tag3%TYPE
  ) RETURN error.error_id%TYPE IS
  l_error_id    error.error_id%TYPE DEFAULT 0;
  BEGIN
    SELECT DECODE(loaded_status,'Y',0,-1012)   
    INTO l_error_id
    FROM bag_v2
    WHERE flight_id = p_flight_id
    AND bag_tag3 = p_bag_tag3
    AND (bag_tag2a = p_bag_tag2a
    OR bag_tag2b = p_bag_tag2b);

    RETURN l_error_id;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      RETURN -1021; -- unresolvable duplicate
    WHEN OTHERS THEN
      RETURN -1012; -- bag_not_loaded
  END;
/
