/*****************************************************************************
*
* Filename		- holding_pens.sql
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier - UAS/FALCP/SS/01180
*
* Description		- Prodedures for use by scanner/tcmt to process
*                         holding pens for BARNZ 
*
* Language		- Oracle PL/SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2005
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE holding_pens
---------------------------------- PACKAGE SPEC ----------------------------------
CREATE OR REPLACE PACKAGE holding_pens
AS

  /* Constants */
  C_ACTION_ADD        CONSTANT VARCHAR2(6) DEFAULT 'ADD';
  C_ACTION_REMOVE     CONSTANT VARCHAR2(6) DEFAULT 'REMOVE';

  /* Exceptions */
  invalid_action      EXCEPTION;
  invalid_user        EXCEPTION;
  bag_group_exists    EXCEPTION;

  /* Package Variables */
  g_user_airport      site_info.airport_code%TYPE;

  /* Procedures */
  PROCEDURE maintain_holding_pen
           (
            p_user_id     IN      tagtrak_users.user_id%TYPE,
            p_holding_pen IN      bag_group.group_no%TYPE,
            p_action      IN      VARCHAR2,
            p_retcode        OUT  NUMBER
           );

  PROCEDURE add_bag_to_holding_pen
           (
            p_user_id     IN      tagtrak_users.user_id%TYPE,
            p_holding_pen IN      bag_group.group_no%TYPE,
            p_bag_tag     IN      bag.bag_tag%TYPE,
            p_flight_id   IN      flight.flight_id%TYPE,
            p_location    IN      location.location_code%TYPE,
            p_scanner_id  IN      kit.id%TYPE,
            p_bag_count      OUT  NUMBER,
            p_retcode        OUT  NUMBER
           );
                                    
  PROCEDURE remove_bag_from_holding_pen
           (
            p_user_id     IN      tagtrak_users.user_id%TYPE,
            p_holding_pen IN      bag_group.group_no%TYPE,
            p_bag_tag     IN      bag.bag_tag%TYPE,
            p_flight_id   IN      flight.flight_id%TYPE,
            p_location    IN      location.location_code%TYPE,
            p_scanner_id  IN      kit.id%TYPE,
            p_bag_count      OUT  NUMBER,
            p_retcode        OUT  NUMBER
           );
                       
END holding_pens;
/


PROMPT CREATE OR REPLACE PACKAGE BODY holding_pens
---------------------------------- PACKAGE BODY ----------------------------------
CREATE OR REPLACE PACKAGE BODY holding_pens
AS 

  --------- Local Functions ---------
  PROCEDURE log_oracle_error
            (
             p_process_name      IN    VARCHAR2,
             p_err_msg           IN    VARCHAR2,
             p_err_code          IN    NUMBER
            ) IS
  
    PRAGMA         AUTONOMOUS_TRANSACTION;

  BEGIN
   
   INSERT INTO ORACLE_ERROR (PROCESS_NAME,
                             ERRROR_MSG,
                             ERROR_CODE,
                             ERROR_TIME
                            )
                     VALUES ( p_process_name, 
                              p_err_msg,
                              p_err_code,
                              SYSDATE + get_offset.airport_code(g_user_airport) 
                            );
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
       RAISE;
  END log_oracle_error;


  --------- Package Functions ---------

  PROCEDURE maintain_holding_pen
           (
            p_user_id     IN      tagtrak_users.user_id%TYPE,
            p_holding_pen IN      bag_group.group_no%TYPE,
            p_action      IN      VARCHAR2,
            p_retcode        OUT  NUMBER
           ) IS

    l_bag_count       NUMBER;
    l_existing_group  bag_group.group_no%TYPE;

  BEGIN

    -- Initialise
    p_retcode := 0;

    -- Check User is valid
    BEGIN
      SELECT si.airport_code
      INTO   g_user_airport
      FROM   tagtrak_users tu,
             agents a,
             site_info si
      WHERE  tu.agent_code = a.agent_code
        AND  a.site_id = si.site_id
        AND  tu.user_id = p_user_id;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE invalid_user;
    END;

    -- Process Requested Action
    IF (TRIM(p_action) = C_ACTION_ADD) THEN

      -- Check if Bag Group already exists
      -- The TRACKING package does not care whether the group
      -- already exists, and will just return success even if
      -- it does, so we will check before calling the add
      -- function.
      BEGIN
        SELECT group_no
        INTO   l_existing_group
        FROM   bag_group bg
        WHERE  bg.group_no = p_holding_pen;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_existing_group := NULL;
      END;

      -- If it exists then abort
      IF (l_existing_group IS NOT NULL) THEN
        RAISE bag_group_exists;
      END IF;

      -- Add A Holding Pen
      tracking.create_bag_group(p_user_id, p_holding_pen, 'Y', l_bag_count, p_retcode);

    ELSIF (TRIM(p_action) = C_ACTION_REMOVE) THEN

      -- Remove A Holding Pen
      -- The TRACKING package will allow non-empty bag_groups to be
      -- deleted, but this will not be valid for Holding Pens.
      -- The FORCE flag will be set to No to prevent deletion of
      -- non-empty Holding Pens.
      -- The Location and Scanner ID arguments are only required
      -- when recording the Trace record for ungroup operations
      -- for non-empty bag_groups, so these are not needed and will
      -- be set to NULL.
      tracking.remove_bag_group(p_user_id, p_holding_pen, 'N', NULL, NULL, p_retcode);

    ELSE

      -- Invalid Action
      RAISE invalid_action;

    END IF;

  EXCEPTION

    WHEN invalid_user THEN

      p_retcode := pkg_constants.ERROR_USER_UNKNOWN;

    WHEN invalid_action THEN

      p_retcode := pkg_constants.ERROR_INVALID_HP_ACTION;

    WHEN bag_group_exists THEN

      p_retcode := pkg_constants.ERROR_BAG_GROUP_EXISTS;

    WHEN OTHERS THEN

      if (NVL(SQLCODE, 0) = 0) THEN
        p_retcode := pkg_constants.ERROR_GENERAL_EXCEPTION;
        log_oracle_error('HOLDING_PENS.MAINTAIN_HOLDING_PEN', 'GENERAL EXCEPTION', p_retcode);
      else
        p_retcode := SQLCODE;
        log_oracle_error('HOLDING_PENS.MAINTAIN_HOLDING_PEN', SQLERRM, SQLCODE);
      end if;

  END maintain_holding_pen;

  -----------------------------------------------------------------
  PROCEDURE add_bag_to_holding_pen
           (
            p_user_id     IN      tagtrak_users.user_id%TYPE,
            p_holding_pen IN      bag_group.group_no%TYPE,
            p_bag_tag     IN      bag.bag_tag%TYPE,
            p_flight_id   IN      flight.flight_id%TYPE,
            p_location    IN      location.location_code%TYPE,
            p_scanner_id  IN      kit.id%TYPE,
            p_bag_count      OUT  NUMBER,
            p_retcode        OUT  NUMBER
           ) IS
                                    
  BEGIN

    -- Initialise
    p_retcode   := 0;
    p_bag_count := 0;

    -- Check User is valid
    BEGIN
      SELECT si.airport_code
      INTO   g_user_airport
      FROM   tagtrak_users tu,
             agents a,
             site_info si
      WHERE  tu.agent_code = a.agent_code
        AND  a.site_id = si.site_id
        AND  tu.user_id = p_user_id;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE invalid_user;
    END;

    -- Group Bag
    tracking.add_bag_to_group(p_user_id, p_holding_pen, p_bag_tag,
                              p_flight_id, p_location, p_scanner_id,
                              p_bag_count, p_retcode);

  EXCEPTION

    WHEN invalid_user THEN

      p_retcode := pkg_constants.ERROR_USER_UNKNOWN;

    WHEN NO_DATA_FOUND THEN

      p_retcode := pkg_constants.ERROR_UNKNOWN_BAG_GROUP;

    WHEN OTHERS THEN

      if (NVL(SQLCODE, 0) = 0) THEN
        p_retcode := pkg_constants.ERROR_GENERAL_EXCEPTION;
        log_oracle_error('HOLDING_PENS.MAINTAIN_HOLDING_PEN', 'GENERAL EXCEPTION', p_retcode);
      else
        p_retcode := SQLCODE;
        log_oracle_error('HOLDING_PENS.MAINTAIN_HOLDING_PEN', SQLERRM, SQLCODE);
      end if;

  END add_bag_to_holding_pen;

  -----------------------------------------------------------------
  PROCEDURE remove_bag_from_holding_pen
           (
            p_user_id     IN      tagtrak_users.user_id%TYPE,
            p_holding_pen IN      bag_group.group_no%TYPE,
            p_bag_tag     IN      bag.bag_tag%TYPE,
            p_flight_id   IN      flight.flight_id%TYPE,
            p_location    IN      location.location_code%TYPE,
            p_scanner_id  IN      kit.id%TYPE,
            p_bag_count      OUT  NUMBER,
            p_retcode        OUT  NUMBER
           ) IS

  BEGIN

    -- Initialise
    p_retcode   := 0;
    p_bag_count := 0;

    -- Check User is valid
    BEGIN
      SELECT si.airport_code
      INTO   g_user_airport
      FROM   tagtrak_users tu,
             agents a,
             site_info si
      WHERE  tu.agent_code = a.agent_code
        AND  a.site_id = si.site_id
        AND  tu.user_id = p_user_id;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE invalid_user;
    END;

    -- Ungroup Bag
    tracking.remove_bag_from_group(p_user_id, p_holding_pen, p_bag_tag,
                                   p_flight_id, p_location, p_scanner_id,
                                   p_bag_count, p_retcode);

  EXCEPTION

    WHEN invalid_user THEN

      p_retcode := pkg_constants.ERROR_USER_UNKNOWN;

    WHEN NO_DATA_FOUND THEN

      p_retcode := pkg_constants.ERROR_UNKNOWN_BAG_GROUP;

    WHEN OTHERS THEN

      if (NVL(SQLCODE, 0) = 0) THEN
        p_retcode := pkg_constants.ERROR_GENERAL_EXCEPTION;
        log_oracle_error('HOLDING_PENS.MAINTAIN_HOLDING_PEN', 'GENERAL EXCEPTION', p_retcode);
      else
        p_retcode := SQLCODE;
        log_oracle_error('HOLDING_PENS.MAINTAIN_HOLDING_PEN', SQLERRM, SQLCODE);
      end if;

  END remove_bag_from_holding_pen;


END holding_pens;
/



