/*****************************************************************************
*
* Filename         - $Workfile:   alpha_to_numeric.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - FALCP\Segregated\SS\SS01062 BARNZ Hold Baggage Screening
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2005
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   alpha_to_numeric.sql  $  $Revision:   1.00  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
*
****************************************************************************/


PROMPT CREATE OR REPLACE FUNCTION alpha_to_numeric

CREATE OR REPLACE FUNCTION alpha_to_numeric (
        p_tag2a            in  varchar2
)
        return               varchar2
AS 

        ---------------------
        -- Local Variables --
        ---------------------
        tag_type      number(2);
        tag_num       bag.bag_tag%TYPE;
	tag           bag.bag_tag%TYPE;
        l_tag2b       varchar2(3);
        l_tag2a       varchar2(3);                           -- changed to varchar2(3) - DRJ
        l_sqlerr      oracle_error.errror_msg%TYPE;
        l_sqlcode     oracle_error.error_code%TYPE;
        l_proc_name   VARCHAR2(30) := 'alpha_to_numeric';

        ----------------
        -- Exceptions --
        ----------------
        BAG_ISSUER_NOT_KNOWN	EXCEPTION;
        BAG_FORMAT_INVALID	EXCEPTION;
        PRAGMA EXCEPTION_INIT(BAG_ISSUER_NOT_KNOWN, -20041);
        PRAGMA EXCEPTION_INIT(BAG_FORMAT_INVALID, -20042);

BEGIN

        /* Purpose:
        **
        ** Converts the supplied alpha bag tag (2a) to numeric format (2b)
        ** On error - returns p_tag2a unchanged.
        */

        l_tag2a := TRIM(p_tag2a);                                        -- added TRIM - DRJ

        -------------------------
        -- Step 0 - Initialise --
        -------------------------

        -- Sanity Check: Ensure some form of tag has been provided
        if (length(l_tag2a) < 2 OR length(l_tag2a) > 3) then             -- removed ltrim(), added OR - DRJ

                -- This is not the tag alpha component - do not proceed
                raise BAG_FORMAT_INVALID;

        end if;

        SELECT bar_code
        INTO   l_tag2b
        FROM   carrier_lookup
        WHERE  TRIM(alpha_code) = l_tag2a;

        return l_tag2b;

EXCEPTION

        WHEN BAG_FORMAT_INVALID THEN

                 raise;

        WHEN BAG_ISSUER_NOT_KNOWN THEN

                 raise;

        ------------------------------------------------------
        -- An error has occurred - return the tag unchanged --
        ------------------------------------------------------
        WHEN OTHERS THEN

            l_sqlerr := SQLERRM || ' Tag: ' || p_tag2a;         -- added tag to msg - DRJ
            l_sqlcode := SQLCODE;

            add_app_msg.log_oracle_error ( l_proc_name, l_sqlerr, l_sqlcode );
      
            return p_tag2a;

END;
/
show errors





