/*****************************************************************************
*
* Filename         - $Workfile:   mws_web.sql  $
*
*  Name	         Date     CR Number        CR Description
*  Yomi Kogbe    19/06/01 15453
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle PL/SQL
*
* Classification   - Commercial In Confidence
*
* (c) Ultra Electronics Limited 2001
*
*****************************************************************************/

/****************************************************************************
*
* Package				  - mws_web
*
* Description			- This Package populate the loading progress screen
                    and the pax and bag count screen for the WEB MWS when
                    connected to the History database. 
*										
*
*****************************************************************************/
CREATE OR REPLACE PACKAGE
mws_web AS

PROCEDURE loading_progress
  (p_flight_id            IN  flight.flight_id%TYPE
  ,p_flight_no_in         IN  flight.flight_no%TYPE
  ,p_time_1               IN  flight.flight_sch_time%TYPE
  ,p_time_2               IN  flight.flight_sch_time%TYPE
  ,p_agent_code           IN  agents.agent_code%TYPE
  ,p_flight_no_out        OUT flts_ldg_prog_by_time_pack.char8
  ,p_flight_status        OUT flts_ldg_prog_by_time_pack.char2
  ,p_flight_destination   OUT flts_ldg_prog_by_time_pack.char3
  ,p_flight_sch_time      OUT flts_ldg_prog_by_time_pack.char4
  ,p_description          OUT flts_ldg_prog_by_time_pack.char7
  ,tf                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,tj                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,ty                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,tr                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,lf                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,lj                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,ly                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,lr                     OUT flts_ldg_prog_by_time_pack.tnumber
  );

FUNCTION loading_progress_2
 (p_flight_id           IN      flight.flight_id%TYPE,
  p_flight_no_in        IN      flight.flight_no%TYPE,
  p_time_1              IN      flight.flight_sch_time%TYPE,
  p_time_2              IN      flight.flight_sch_time%TYPE,
  p_agent_code          IN      agents.agent_code%TYPE
  ) RETURN SYS_REFCURSOR;

PROCEDURE pax_bag_count (p_flight_id              flight.flight_id%TYPE
            ,p_flight_no_in           flight.flight_no%TYPE
            ,p_time_1                 flight.flight_sch_time%TYPE
            ,p_time_2                 flight.flight_sch_time%TYPE
            ,p_agent_code             agents.agent_code%TYPE
            ,p_flight_no_out          OUT flts_px_bg_cnt_by_tim_mws_pack.char8
            ,p_flight_destination     OUT flts_px_bg_cnt_by_tim_mws_pack.char3
            ,p_flight_sch_time        OUT flts_px_bg_cnt_by_tim_mws_pack.char4
            ,p_type                   OUT flts_px_bg_cnt_by_tim_mws_pack.char5
            ,tf                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,tj                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,ty                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,lf                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,lj                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,ly                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,tot                      OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,tr                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,lr                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            );

FUNCTION pax_bag_count_2
        (p_flight_id              flight.flight_id%TYPE,
         p_flight_no_in           flight.flight_no%TYPE,
         p_time_1                 flight.flight_sch_time%TYPE,
         p_time_2                 flight.flight_sch_time%TYPE,
         p_agent_code             agents.agent_code%TYPE
         ) RETURN SYS_REFCURSOR;

FUNCTION mws_get_user_bag_tag (
      p_tag            IN  VARCHAR2,
      p_user_pref      IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER	DEFAULT	1 )
      RETURN               VARCHAR2;

FUNCTION mws_build_user_bag_tag (
      p_t1             IN  VARCHAR2,
      p_t2a            IN  VARCHAR2,
      p_t2b            IN  VARCHAR2,
      p_t3             IN  NUMBER,
      p_user_pref      IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER	DEFAULT	1 )
      RETURN               VARCHAR2;

FUNCTION mws_get_numeric_bag_tag (
      p_tag            IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER	DEFAULT	1 )
      RETURN               VARCHAR2;

PROCEDURE mws_validate_bag_tag (
      p_tag            IN  VARCHAR2,
      a_bag_tag	       OUT VARCHAR2,
      n_bag_tag        OUT VARCHAR2,
      retval           OUT NUMBER );
      
PROCEDURE validate_partial_bag_tag (
      p_partial_tag    IN  VARCHAR2,
      p_numeric_tag    OUT VARCHAR2 );

/******************************************************************************
 *
 * FUNCTION pax_stopped_bag_count
 *
 * Description:
 *   Returns a count of how many stopped bags a given passenger has.
 *
 * Parameters:
 *   p_pax_id - pax_id of pax to count bags for
 *
 ******************************************************************************/
FUNCTION pax_stopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER;

/******************************************************************************
 *
 * FUNCTION pax_unstopped_bag_count
 *
 * Description:
 *   Returns a count of how many non-stopped (clear) bags a given passenger has.
 *
 * Parameters:
 *   p_pax_id - pax_id of pax to count bags for
 *
 ******************************************************************************/
FUNCTION pax_unstopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER;

END mws_web;
/
CREATE OR REPLACE
PACKAGE BODY mws_web AS

PROCEDURE loading_progress
  (p_flight_id            IN  flight.flight_id%TYPE
  ,p_flight_no_in         IN  flight.flight_no%TYPE
  ,p_time_1               IN  flight.flight_sch_time%TYPE
  ,p_time_2               IN  flight.flight_sch_time%TYPE
  ,p_agent_code           IN  agents.agent_code%TYPE
  ,p_flight_no_out        OUT flts_ldg_prog_by_time_pack.char8
  ,p_flight_status        OUT flts_ldg_prog_by_time_pack.char2
  ,p_flight_destination   OUT flts_ldg_prog_by_time_pack.char3
  ,p_flight_sch_time      OUT flts_ldg_prog_by_time_pack.char4
  ,p_description          OUT flts_ldg_prog_by_time_pack.char7
  ,tf                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,tj                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,ty                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,tr                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,lf                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,lj                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,ly                     OUT flts_ldg_prog_by_time_pack.tnumber
  ,lr                     OUT flts_ldg_prog_by_time_pack.tnumber
  ) AS
  v_error_number          NUMBER;
  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  * PURPOSE                                                                                     *
  *                                                                                             *
  * Find the loaded bag counts for a flight split between Local (l) and Transfer (t) and then   *
  * divided by class: first (f), business (j) and economy (y).                                  *
  * tf is first class transfer bags                                                             *
  * ty is economy class local bags and so on ...                                                *
  *                                                                                             *
  * PROCESS                                                               METHOD     TYPE       *
  *                                                                                             *
  * Find out what the loading progress is                                                       *
  * using flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time                Procedure  External   *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */



    flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time
      (p_flight_id 
      ,p_flight_no_in
      ,p_time_1    
      ,p_time_2    
      ,p_agent_code
      ,v_error_number
      ,p_flight_no_out
      ,p_flight_status
      ,p_flight_destination
      ,p_flight_sch_time
      ,p_description
      ,tf         
      ,tj         
      ,ty         
      ,tr         
      ,lf         
      ,lj         
      ,ly         
      ,lr         
      );
  END loading_progress;

FUNCTION loading_progress_2
 (p_flight_id           IN      flight.flight_id%TYPE,
  p_flight_no_in        IN      flight.flight_no%TYPE,
  p_time_1              IN      flight.flight_sch_time%TYPE,
  p_time_2              IN      flight.flight_sch_time%TYPE,
  p_agent_code          IN      agents.agent_code%TYPE)
  RETURN SYS_REFCURSOR IS
  l_flight_no_out          flts_ldg_prog_by_time_pack.char8;
  l_flight_status          flts_ldg_prog_by_time_pack.char2;
  l_flight_destination     flts_ldg_prog_by_time_pack.char3;
  l_flight_sch_time        flts_ldg_prog_by_time_pack.char4;
  l_description            flts_ldg_prog_by_time_pack.char7;
  tf                       flts_ldg_prog_by_time_pack.tnumber;
  tj                       flts_ldg_prog_by_time_pack.tnumber;
  ty                       flts_ldg_prog_by_time_pack.tnumber;
  tr                       flts_ldg_prog_by_time_pack.tnumber;
  lf                       flts_ldg_prog_by_time_pack.tnumber;
  lj                       flts_ldg_prog_by_time_pack.tnumber;
  ly                       flts_ldg_prog_by_time_pack.tnumber;
  lr                       flts_ldg_prog_by_time_pack.tnumber;
  l_results_tab           loading_prog_by_time_tab := loading_prog_by_time_tab();
  l_cursor           SYS_REFCURSOR;
  v_error_number          NUMBER;

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                            *
  * PURPOSE                                                                                    *
  *                                                                                            *
  * Find the loaded bag counts for a flight split between Local (l) and Transfer (t) and then  *
  * divided by class: first (f), business (j) and economy (y).                                 *
  * tf is first class transfer bags                                                            *
  * ty is economy class local bags and so on ...                                               *
  * This function returns the data in a ref cursor.                                            *
  *                                                                                            *
  * PROCESS                                                   METHOD     TYPE                  *
  *                                                                                            *
  * Find out what the loading progress is                                                      *
  * using flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time    Function  External               *
  *                                                                                            *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *  */

BEGIN
  
  flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time(p_flight_id
                                                  ,p_flight_no_in
                                                  ,p_time_1
                                                  ,p_time_2
                                                  ,p_agent_code
                                                  ,v_error_number
                                                  ,l_flight_no_out
                                                  ,l_flight_status
                                                  ,l_flight_destination
                                                  ,l_flight_sch_time
                                                  ,l_description
                                                  ,tf
                                                  ,tj
                                                  ,ty
                                                  ,tr
                                                  ,lf
                                                  ,lj
                                                  ,ly
                                                  ,lr);

    FOR n IN nvl(l_flight_no_out.FIRST, 1)..nvl(l_flight_no_out.LAST, 0) LOOP
   
      BEGIN
     
        --make room in sql table.
        l_results_tab.extend(1);
        --use constructor to populate.
        l_results_tab(n) := loading_prog_by_time_rec(
                                            l_flight_no_out(n),
                                            l_flight_status(n),
                                            l_flight_destination(n),
                                            l_flight_sch_time(n),
                                            l_description(n),
                                            tf(n),
                                            tj(n),
                                            ty(n),
                                            tr(n),
                                            lf(n),
                                            lj(n),
                                            ly(n),
                                            lr(n)                                       
                                            );
                                            
      EXCEPTION WHEN no_data_found THEN
       NULL; --don't worry about gaps in the sql tables.
       
      END;
    
   END LOOP;
  
   OPEN l_cursor FOR
   SELECT *
   FROM TABLE(CAST(l_results_tab AS loading_prog_by_time_tab));
 
   RETURN l_cursor;
  
END loading_progress_2;

PROCEDURE pax_bag_count(p_flight_id              flight.flight_id%TYPE
            ,p_flight_no_in           flight.flight_no%TYPE
            ,p_time_1                 flight.flight_sch_time%TYPE
            ,p_time_2                 flight.flight_sch_time%TYPE
            ,p_agent_code             agents.agent_code%TYPE
            ,p_flight_no_out          OUT flts_px_bg_cnt_by_tim_mws_pack.char8
            ,p_flight_destination     OUT flts_px_bg_cnt_by_tim_mws_pack.char3
            ,p_flight_sch_time        OUT flts_px_bg_cnt_by_tim_mws_pack.char4
            ,p_type                   OUT flts_px_bg_cnt_by_tim_mws_pack.char5
            ,tf                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,tj                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,ty                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,lf                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,lj                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,ly                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,tot                      OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,tr                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ,lr                       OUT flts_px_bg_cnt_by_tim_mws_pack.tnumber
            ) AS
  v_flight_status          flts_px_bg_cnt_by_tim_mws_pack.char2;
  v_error_number           NUMBER DEFAULT 0;
BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  * PURPOSE                                                                                     *
  *                                                                                             *
  * Find the pax and bag counts for a flight by time split between Local (l) and Transfer (t)   *
  * and then divided by class: first (f), business (j) and economy (y).                         *
  * tf is first class transfer bags                                                             *
  * ty is economy class local bags and so on ...                                                *
  *                                                                                             *
  * PROCESS                                                               METHOD     TYPE       *
  *                                                                                             *
  * Find out what the loading progress is                                                       *
  * using flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws   Procedure  External   *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
      flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws
        (p_flight_id
        ,p_flight_no_in
        ,p_time_1
        ,p_time_2
        ,p_agent_code
        ,v_error_number
        ,p_flight_no_out
        ,v_flight_status
        ,p_flight_destination
        ,p_flight_sch_time
        ,p_type
        ,tf
        ,tj
        ,ty
        ,lf
        ,lj
        ,ly
        ,tot
        ,tr
        ,lr
        );
        p_flight_no_out(p_flight_no_out.COUNT) := '        ';
END pax_bag_count;

FUNCTION pax_bag_count_2
        (p_flight_id              flight.flight_id%TYPE,
         p_flight_no_in           flight.flight_no%TYPE,
         p_time_1                 flight.flight_sch_time%TYPE,
         p_time_2                 flight.flight_sch_time%TYPE,
         p_agent_code             agents.agent_code%TYPE) 
         RETURN SYS_REFCURSOR IS
         
         l_flight_no_out          flts_px_bg_cnt_by_tim_mws_pack.char8;
         l_flight_destination     flts_px_bg_cnt_by_tim_mws_pack.char3;
         l_flight_sch_time        flts_px_bg_cnt_by_tim_mws_pack.char4;
         l_type                   flts_px_bg_cnt_by_tim_mws_pack.char5;
         tf                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         lf                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         tj                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         lj                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         ty                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         ly                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         tr                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         lr                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         tot                      flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         v_flight_status          flts_px_bg_cnt_by_tim_mws_pack.char2;
         v_error_number           NUMBER DEFAULT 0;
         l_results_tab            flts_pax_bag_count_by_time_tab := flts_pax_bag_count_by_time_tab();
         l_cursor                 SYS_REFCURSOR;

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  * PURPOSE                                                                                     *
  *                                                                                             *
  * Find the pax and bag counts for a flight by time split between Local (l) and Transfer (t)   *
  * and then divided by class: first (f), business (j) and economy (y).                         *
  * tf is first class transfer bags                                                             *
  * ty is economy class local bags and so on ...                                                *
  * This function returns the data in a ref cursor                                              *
  *                                                                                             *
  * PROCESS                                                               METHOD     TYPE       *
  *                                                                                             *
  * Find out what the loading progress is                                                       *
  * using flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws   Procedure  External   *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

BEGIN
flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws
        (p_flight_id
        ,p_flight_no_in
        ,p_time_1
        ,p_time_2
        ,p_agent_code
        ,v_error_number
        ,l_flight_no_out
        ,v_flight_status
        ,l_flight_destination
        ,l_flight_sch_time
        ,l_type
        ,tf
        ,tj
        ,ty
        ,lf
        ,lj
        ,ly
        ,tot
        ,tr
        ,lr
        );
        l_flight_no_out(l_flight_no_out.COUNT) := '        ';
        
 FOR n IN nvl(l_flight_no_out.FIRST, 1)..nvl(l_flight_no_out.LAST, 0) LOOP
   
      BEGIN
     
        --make room in sql table.
        l_results_tab.extend(1);
        --use constructor to populate.
        l_results_tab(n) := flts_pax_bag_count_by_time_rec(
                l_flight_no_out(n),
                l_flight_destination(n),     
                l_flight_sch_time(n),        
                l_type(n),                   
                tf(n),                       
                lf(n),                       
                tj(n),                       
                lj(n),                       
                ty(n),                       
                ly(n),                       
                tr(n),                       
                lr(n),                     
                tot(n)
                );

      EXCEPTION WHEN no_data_found THEN
       NULL; --don't worry about gaps in the sql tables.
       
      END;
    
 END LOOP;
  
   OPEN l_cursor FOR
   SELECT *
   FROM TABLE(CAST(l_results_tab AS flts_pax_bag_count_by_time_tab));
   RETURN l_cursor; 
   
END pax_bag_count_2;

----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
-- 
-- Function:    MWS_GET_USER_BAG_TAG
-- Parameters:  P_TAG            - Tag to be converted
--              P_USER_PREF      - New format required (A/N)
--              P_BAG_MUST_EXIST - Bag Tag must exist in database (Default = TRUE)
-- Returns:     P_TAG converted to requested format or P_TAG on error
----------------------------------------------------------------------------------

FUNCTION mws_get_user_bag_tag (
      p_tag            IN  VARCHAR2,
      p_user_pref      IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER	DEFAULT	1 )
      RETURN               VARCHAR2
AS

  tmptag BAG.BAG_TAG%TYPE; 

BEGIN

  -- 
  -- Purpose:
  --
  -- This function uses the existing GET_USER_BAG_TAG database
  -- function to convert the given TAG to the requested format.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then the original data is returned.
  -- 

  tmptag := get_user_bag_tag(p_tag, p_user_pref, p_bag_must_exist);

  RETURN NVL(tmptag, p_tag);

EXCEPTION

  -- On ANY error - return the original tag
  WHEN OTHERS THEN

    RETURN p_tag;

END mws_get_user_bag_tag;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
-- 
-- Function:    MWS_BUILD_USER_BAG_TAG
-- Parameters:  P_T1             - First character of tag (customer defined)
--              P_T2A            - Alpha BTIC code
--              P_T2B            - Numeric BTIC code
--              P_T3             - Numeric part of bag tag (final 6 digits)
--              P_USER_PREF      - New format required (A/N)
--              P_BAG_MUST_EXIST - Bag Tag must exist in database (Default = TRUE)
-- Returns:     P_TAG converted to requested format or NULL on error
----------------------------------------------------------------------------------

FUNCTION mws_build_user_bag_tag (
      p_t1             IN  VARCHAR2,
      p_t2a            IN  VARCHAR2,
      p_t2b            IN  VARCHAR2,
      p_t3             IN  NUMBER,
      p_user_pref      IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER	DEFAULT	1 )
      RETURN               VARCHAR2
AS 

  tmptag BAG.BAG_TAG%TYPE;

BEGIN

  -- 
  -- Purpose:
  --
  -- This function uses the existing BUILD_USER_BAG_TAG database
  -- function to convert the given components in to a TAG of the requested
  -- format.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then NULL is returned.
  -- 

  tmptag := build_user_bag_tag(p_t1, p_t2a, p_t2b, p_t3, p_user_pref, p_bag_must_exist);

  RETURN tmptag;

EXCEPTION

  -- On ANY error - return NULL
  WHEN OTHERS THEN
     RETURN NULL;

END mws_build_user_bag_tag;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
-- 
-- Function:    MWS_GET_USER_BAG_TAG
-- Parameters:  P_TAG            - Tag to be converted
--              P_BAG_MUST_EXIST - Bag Tag must exist in database (Default = TRUE)
-- Returns:     P_TAG converted to requested format or P_TAG on error
----------------------------------------------------------------------------------

FUNCTION mws_get_numeric_bag_tag (
      p_tag            IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER	DEFAULT	1 )
      RETURN               VARCHAR2
AS 

  tmptag BAG.BAG_TAG%TYPE;

BEGIN

  -- 
  -- Purpose:
  --
  -- This function uses the existing GET_NUMERIC_BAG_TAG database
  -- function to convert the given TAG to numeric format.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then the original data is returned.
  -- 

  tmptag := get_numeric_bag_tag(p_tag, p_bag_must_exist);

  RETURN NVL(tmptag, p_tag);

EXCEPTION

  -- On ANY error - return the original tag
  WHEN OTHERS THEN

    RETURN p_tag;

END mws_get_numeric_bag_tag;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
-- 
-- Function:    MWS_VALIDATE_BAG_TAG
-- Parameters:  P_TAG            - Tag to be converted
--              A_BAG_TAG        - Alphanumeric form of bag tag
--              N_BAG_TAG        - Numeric form of bag tag
--              RETVAL           - Return Status code (0=Success)
-- Returns:     P_TAG converted to both formats.  Both will be set to P_TAG on
--              error.
----------------------------------------------------------------------------------

PROCEDURE mws_validate_bag_tag (
      p_tag            IN  VARCHAR2,
      a_bag_tag	       OUT VARCHAR2,
      n_bag_tag        OUT VARCHAR2,
      retval           OUT NUMBER )
AS

BEGIN

  -- 
  -- Purpose:
  --
  -- This function uses the existing VALIDATE_BAG_TAG database
  -- procedure to convert the given TAG to both alpha and numeric formats.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then the original data is returned for
  -- both formats.
  -- 

  validate_bag_tag(p_tag, a_bag_tag, n_bag_tag, retval);

  IF (retval <> 0) THEN
    a_bag_tag := p_tag;
    n_bag_tag := p_tag;
  END IF;

EXCEPTION

  -- On ANY error - return the original tag
  WHEN OTHERS THEN

    a_bag_tag := p_tag;
    n_bag_tag := p_tag;
    RETURN;

END mws_validate_bag_tag;

----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00870
--
-- Procedure:   VALIDATE_PARTIAL_BAG_TAG
-- Parameters:  P_PARTIAL_TAG    IN  - Section of a bag tag in alpha or numeric form
--              P_NUMERIC_TAG    OUT - Tag converted to numeric format with wildcards
--                                     for any missing characters.
----------------------------------------------------------------------------------

PROCEDURE validate_partial_bag_tag (
      p_partial_tag    IN  VARCHAR2,
      p_numeric_tag    OUT VARCHAR2 ) IS

  tag_invalid    EXCEPTION;
  l_partial_tag  VARCHAR2(10);
  l_numeric_tag  VARCHAR2(10);
  l_tag_len      NUMBER;

BEGIN

  -- Initialise
  l_partial_tag := UPPER(LTRIM(p_partial_tag));
  p_numeric_tag := p_partial_tag;

  -- Check somthing entered
  IF (l_partial_tag IS NULL) THEN

    RAISE tag_invalid;

  END IF;

  Dbms_Output.PUT_LINE('Input Tag - ' || l_partial_tag);

  -- Does the tag contain any non-numeric characters?
  l_tag_len := LENGTH(l_partial_tag);
  IF (LENGTH(TRANSLATE(l_partial_tag, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ')) > 0) THEN

    -- Alpha characters found - pad to length 8 for alphanumeric bag tag
    Dbms_Output.PUT_LINE('Performing Alpha PAD');
    l_partial_tag := rpad(l_partial_tag, 8, '9');

  ELSE

    -- No alpha characters found - pad to length 10 for numeric bag tag
    Dbms_Output.PUT_LINE('Performing Numeric PAD');
    l_partial_tag := rpad(l_partial_tag, 10, '9');

  END IF;

  Dbms_Output.PUT_LINE('Padded Tag - ' || l_partial_tag);

  -- Convert the bag tag to numeric
  l_numeric_tag := mws_get_numeric_bag_tag(l_partial_tag, 0);

  Dbms_Output.PUT_LINE('Converted Tag - ' || l_numeric_tag);

  -- Remove any padding
  IF (l_tag_len < LENGTH(l_partial_tag)) THEN

    Dbms_Output.PUT_LINE('Performing SUBSTR');

    l_numeric_tag := SUBSTR(l_numeric_tag, 1, LENGTH(l_numeric_tag) - (LENGTH(l_partial_tag) - l_tag_len));
    l_numeric_tag := l_numeric_tag || '%';

  END IF;

  -- Convert any ? to a wildcard
  p_numeric_tag := REPLACE(l_numeric_tag, '?', '_');

EXCEPTION

  WHEN tag_invalid THEN

    p_numeric_tag := NULL;

END validate_partial_bag_tag;

-- UAS/FALCP/CA/02658: Count how many stopped bags a given passenger has
FUNCTION pax_stopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER AS
   l_bag_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_bag_count
   FROM bag
   WHERE bag.pax_id = p_pax_id
   AND bag.delete_marker = 0
   AND bag.authority_to_load_brs = 'N';

   RETURN l_bag_count;
END pax_stopped_bag_count;

-- UAS/FALCP/CA/02658: Count how many non-stopped bags a given passenger has
FUNCTION pax_unstopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER AS
   l_bag_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_bag_count
   FROM bag
   WHERE bag.pax_id = p_pax_id
   AND bag.delete_marker = 0
   AND bag.authority_to_load_brs = 'Y';

   RETURN l_bag_count;
END pax_unstopped_bag_count;

END mws_web;
/

