/*****************************************************************************
*
* Filename         - $Workfile:   kit_timeout.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       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live schema kit_timeout  package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   kit_timeout.sql  $  $Revision:   1.3  $
*
* Revision History
*
*
****************************************************************************/

PROMPT create or replace package kit_timeout

create or replace package kit_timeout
as
    function get_kit_timeout (
                              p_kit_type in kit.type%type
    ) return number;

    function get_timeout (
                              p_kit_type in kit.type%type,
                              p_userid  IN  tagtrak_users.user_id%type
    ) return number;

    function get_timeout (
                              p_kit_type in kit.type%type,
                              p_agent_code  IN  agents.agent_code%type
    ) return number;


END kit_timeout;
/
PROMPT create or replace package body kit_timeout

create or replace package body kit_timeout
as

/*
** Returns the timeout value from the kit_type table.
*/
    function get_kit_timeout (
                                        p_kit_type in kit.type%type
    ) return number
    is
        out_timeout_value number;
    BEGIN
         SELECT timeout /* in minutes... */
         INTO out_timeout_value
         FROM kit_type
         WHERE RTRIM(type) = p_kit_type;

        return out_timeout_value;
                        
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             out_timeout_value := 0;
             return out_timeout_value;
    end get_kit_timeout;

/*
**  scanner_timeout or mws_timeout depending on the p_kit_type parameter for a user id.
*/
    function get_timeout (
                                        p_kit_type in kit.type%type,
                                        p_userid  IN  tagtrak_users.user_id%type
    ) return number
    is
        timeout number;
    BEGIN
            SELECT  nvl( decode( substr(p_kit_type,1,3) ,
                                'SCA',a.scanner_timeout,
                                'MWS',a.mws_timeout,0),
                      get_kit_timeout ( p_kit_type ) )
            INTO  timeout
            FROM   agents a, tagtrak_users tu 
            WHERE  tu.user_id   = p_userid
                   AND    a.agent_code = tu.agent_code;
        return timeout;

           /* If no rows found, call the kit_timeout.get_kit_timeout (p_kit_type) function. */
           EXCEPTION
             WHEN NO_DATA_FOUND THEN
                 timeout := get_kit_timeout ( p_kit_type );
                 return timeout;

    end get_timeout;

/*
** scanner_timeout or mws_timeout depending on the p_kit_type parameter for a role id.
*/
    function get_timeout (
                          p_kit_type in kit.type%type,
                          p_agent_code  IN  agents.agent_code%type
    ) return number
    is
        timeout number;
    BEGIN
        SELECT  nvl( decode( substr(p_kit_type,1,3) ,
                            'SCA',a.scanner_timeout,
                            'MWS',a.mws_timeout,0),
                  get_kit_timeout ( p_kit_type ) )
        INTO  timeout
        FROM   agents a
        WHERE  a.agent_code = p_agent_code;
        return timeout;


        /*  If no rows found, call the kit_timeout.get_kit_timeout (p_kit_type) function. */
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              timeout := get_kit_timeout ( p_kit_type );
              return timeout;

    end  get_timeout;

END kit_timeout;
/
