PROMPT CREATE OR REPLACE FUNCTION  check_btm_exists

CREATE OR REPLACE
FUNCTION  check_btm_exists
  (p_flight_id_scan  bag_trace.flight_id_scan%TYPE
  ,p_bag_tag2a       bag_trace.bag_tag2a%TYPE
  ,p_bag_tag2b       bag_trace.bag_tag2b%TYPE
  ,p_bag_tag3        bag_trace.bag_tag3%TYPE) RETURN BOOLEAN IS
  btm_count NUMBER DEFAULT 0;
  BEGIN
    SELECT COUNT(*)
    INTO btm_count
    FROM bag_trace
    WHERE flight_id_scan = p_flight_id_scan
    AND (bag_tag2a = p_bag_tag2a
      OR bag_tag2b = p_bag_tag2b)
    AND bag_tag3 = p_bag_tag3
    AND trace_action = 'BTM';

    RETURN (btm_count > 0);

  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END check_btm_exists;
/
