/*****************************************************************************
*
* Filename         - $Workfile:   get_bag_flights.sql  $
*
*                     Name              Date      Version     CR Number
*
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2005
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE bag_flights

CREATE OR REPLACE PACKAGE bag_flights IS


  CURSOR c_flights (p_user_id        IN  tagtrak_users.user_id%TYPE,
                    p_bag_tag2       IN  bag.bag_tag2b%TYPE,
                    p_bag_tag3       IN  bag.bag_tag3%TYPE,
                    p_last_flight_id IN  flight.flight_id%TYPE)
  IS
         SELECT     f.flight_id,
                    f.flight_date,
                    f.flight_no
         FROM       bag b,
                    pax p,
                    flight f,
                    flight_access fa,
                    tagtrak_users tu
         WHERE      p.pax_id         =       b.pax_id
           AND      p.flight_id      =       f.flight_id
           AND      fa.flight_id     =       f.flight_id
           AND      fa.agent_code    =       tu.agent_code
           AND      b.delete_marker  =       0
           AND      f.flight_type    =       'O'
           AND      f.departure_time BETWEEN TRUNC(SYSDATE - 1)
                                     AND     TRUNC(SYSDATE + 2) - 1/86400
           AND      tu.user_id       =       p_user_id
           AND      b.bag_tag2b      =       p_bag_tag2
           AND      b.bag_tag3       =       p_bag_tag3
           AND      f.site_id        =       multi_site.get_user_siteid(p_user_id)
           AND      f.flight_id      <       p_last_flight_id
         GROUP BY   f.flight_id,
                    f.flight_date,
                    f.flight_no
         ORDER BY   f.flight_id DESC;

  FUNCTION initialise_cursor (p_user_id        IN  tagtrak_users.user_id%TYPE,
                              p_bag_tag        IN  bag.bag_tag%TYPE,
                              p_last_flight_id IN  flight.flight_id%TYPE       DEFAULT NULL)
                              RETURN               NUMBER;

  PROCEDURE get_next_flight ( p_flight_id      OUT flight.flight_id%TYPE,
                              p_flight_date    OUT flight.flight_date%TYPE,
                              p_flight_no      OUT flight.flight_no%TYPE);

  FUNCTION close_cursor       RETURN               NUMBER;

  FUNCTION any_more           RETURN               NUMBER;

END bag_flights;  -- End of Package BAG_FLIGHTS
/

PROMPT CREATE OR REPLACE PACKAGE BODY bag_flights

CREATE OR REPLACE PACKAGE BODY bag_flights IS


  -----------------------------------------------------------------------------
  FUNCTION initialise_cursor (p_user_id        IN  tagtrak_users.user_id%TYPE,
                              p_bag_tag        IN  bag.bag_tag%TYPE,
                              p_last_flight_id IN  flight.flight_id%TYPE       DEFAULT NULL)
                              RETURN               NUMBER IS

    l_bag_tag1          bag.bag_tag1%TYPE     := NULL;
    l_bag_tag2a         bag.bag_tag2a%TYPE    := NULL;
    l_bag_tag2b         bag.bag_tag2b%TYPE    := NULL;
    l_bag_tag3          bag.bag_tag3%TYPE     := NULL;
    l_bag_tag           bag.bag_tag%TYPE      := NULL;
    l_tag_type          NUMBER                := -1;
    l_last_flight_id    flight.flight_id%TYPE := 999999999;
    l_close_status      NUMBER                := 0;

  BEGIN

    -- If the cursor is already open - close it
    IF (c_flights%ISOPEN) THEN

      l_close_status := close_cursor;
      IF (l_close_status <> pkg_constants.SUCCESS) THEN

        -- Error closing cursor - abort
        RETURN l_close_status;

      END IF;

    END IF;

    -- Check the given tag and extract the tag components
    split_tags(p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tag, l_tag_type, 0);

    -- Were we given a valid bag tag?
    IF (NVL(l_tag_type, 0) < 2 OR l_tag_type > 4) THEN

      -- Invalid Bag Tag - Return Error and do not continue
      RETURN pkg_constants.ERROR_GEN_BAG_TAG;

    END IF;

    -- Check we got a valid numeric BTIC
    IF (l_bag_tag2b IS NULL) THEN

      -- Invalid BTIC - Return Error and do not continue
      RETURN pkg_constants.ERROR_BAG_ISSUER_NOT_KNOWN;

    END IF;

    -- Check if the flight list needs to be restricted
    IF (NVL(p_last_flight_id, 0) <> 0) THEN

      -- Set the Last Flight ID
      l_last_flight_id := p_last_flight_id;

    END IF;

    -- Open the Cursor
    OPEN c_flights(p_user_id, l_bag_tag2b, l_bag_tag3, l_last_flight_id);

    -- Return Success
    RETURN pkg_constants.SUCCESS;

  EXCEPTION

    WHEN OTHERS THEN

        RETURN SQLCODE;

  END initialise_cursor;  -- End of Function INITIALISE_CURSOR


  -----------------------------------------------------------------------------
  PROCEDURE get_next_flight ( p_flight_id      OUT flight.flight_id%TYPE,
                              p_flight_date    OUT flight.flight_date%TYPE,
                              p_flight_no      OUT flight.flight_no%TYPE) IS

  BEGIN

    -- Check that the cursor is open
    IF (NOT c_flights%ISOPEN) THEN

      -- Return No More Flights
      p_flight_id := 0;
      RETURN;

    END IF;

    -- Get the Next Flight ID
    FETCH c_flights INTO p_flight_id, p_flight_date, p_flight_no;

    -- Did we get something?
    IF (c_flights%NOTFOUND) THEN

      -- Return No More Flights
      p_flight_id := 0;
      RETURN;

    END IF;

  EXCEPTION

    WHEN OTHERS THEN

      p_flight_id := SQLCODE;
      RETURN;

  END get_next_flight;  -- End of Procedure GET_NEXT_FLIGHT


  -----------------------------------------------------------------------------
  FUNCTION close_cursor       RETURN               NUMBER IS

  BEGIN

    -- Check that the cursor is open
    IF (NOT c_flights%ISOPEN) THEN

      -- Return No More Flights
      RETURN -1;

    END IF;

    -- Close the Cursor
    CLOSE c_flights;

    -- Return Success
    RETURN pkg_constants.SUCCESS;

  EXCEPTION

    WHEN OTHERS THEN

      RETURN SQLCODE;

  END close_cursor;  -- End of Function CLOSE_CURSOR


  -----------------------------------------------------------------------------
  FUNCTION any_more           RETURN               NUMBER IS

    l_next_flight_id    flight.flight_id%TYPE;
    l_next_flight_date  flight.flight_date%TYPE;
    l_next_flight_no    flight.flight_no%TYPE;

  BEGIN

    -- Check that the cursor is open
    IF (NOT c_flights%ISOPEN) THEN

      -- Return No More Flights
      RETURN 0;

    END IF;

    -- Attempt next fetch
    get_next_flight(l_next_flight_id, l_next_flight_date, l_next_flight_no);

    -- Did we find another flight?
    IF (l_next_flight_id > 0) THEN

      -- Return True
      RETURN 1;

    END IF;

    RETURN 0;

  EXCEPTION

    WHEN OTHERS THEN

      RETURN SQLCODE;

  END any_more;  -- End of Function ANY_MORE


END bag_flights;  -- End of Package Body BAG_FLIGHTS
/
