PROMPT CREATE OR REPLACE FUNCTION onward_count

CREATE OR REPLACE FUNCTION onward_count
  (p_flight_id       IN flight.flight_id%TYPE
  ,p_pax_class       IN pax.pax_class%TYPE
  ,p_uld_no          IN ulds.uld_no%TYPE
  ,p_no              IN onward_flight.no%TYPE
  ,p_dest            IN onward_flight.dest%TYPE
  ,p_dest_order      IN onward_flight.dest_order%TYPE
  ) RETURN pax.no_of_bags%TYPE IS
  v_count pax.no_of_bags%TYPE;
  BEGIN
    IF p_pax_class IN ('Y','F','C') THEN
      SELECT COUNT(*)
      INTO v_count
      FROM bag_v2 b
      ,onward_flight o
      ,uld_dests	u
      WHERE loaded_status = 'Y'
      AND delete_marker = 0
      AND o.pax_id = b.pax_id
      AND o.dest = p_dest
      AND o.dest_order = p_dest_order
      AND o.no = p_no
      AND u.uld_no = p_uld_no
      AND b.pax_class = p_pax_class
      AND b.uld_no = u.uld_no
      AND b.flight_id = u.flight_id
      AND b.flight_id = p_flight_id
      AND (b.bag_destination = u.uld_destination
		  OR (b.uld_no IN (SELECT hold_no FROM holds)
				AND u.uld_destination = '*'));
    ELSIF p_pax_class = 'O' THEN
      SELECT COUNT(*)
      INTO v_count
      FROM bag_v2 b
      ,onward_flight o
      ,uld_dests	u
      WHERE loaded_status = 'Y'
      AND delete_marker = 0
      AND o.pax_id = b.pax_id
      AND o.dest = p_dest
      AND o.dest_order = p_dest_order
      AND o.no = p_no
      AND u.uld_no = p_uld_no
      AND b.pax_class NOT IN ('Y','F','C')
      AND b.uld_no = u.uld_no
      AND b.flight_id = u.flight_id
      AND b.flight_id = p_flight_id
      AND (b.bag_destination = u.uld_destination
		  OR (b.uld_no IN (SELECT hold_no FROM holds)
				AND u.uld_destination = '*'));
    ELSIF p_pax_class = 'T' THEN
      SELECT COUNT(*)
      INTO v_count
      FROM bag_v2 b
      ,onward_flight o
      ,uld_dests	u
      WHERE loaded_status = 'Y'
      AND delete_marker = 0
      AND o.pax_id = b.pax_id
      AND o.dest = p_dest
      AND o.dest_order = p_dest_order
      AND o.no = p_no
      AND u.uld_no = p_uld_no
      AND b.uld_no = u.uld_no
      AND b.flight_id = u.flight_id
      AND b.flight_id = p_flight_id
      AND (b.bag_destination = u.uld_destination
		  OR (b.uld_no IN (SELECT hold_no FROM holds)
				AND u.uld_destination = '*'));

    END IF;

    RETURN v_count;

  END onward_count;
/
