/*****************************************************************************
*
* Filename         - $Workfile:   def_ldg_strat.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          - UAS/FALCP/SS/1959
*                    UAS/FALCP/CA/1878
*
* Contents         - Live Schema Stored Procedures for retrieving the name of
*                    the default loading strategy, and its code for use by the
*                    scanner software
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/


PROMPT CREATE OR REPLACE PACKAGE def_ldg_strat

CREATE OR REPLACE PACKAGE def_ldg_strat IS
FUNCTION get_def_ldg_strat_name RETURN db_config.db_value%TYPE;
FUNCTION get_def_ldg_strat_scan_code RETURN ldg_strat.ldg_strat_code%TYPE;
END def_ldg_strat;
/


PROMPT CREATE OR REPLACE PACKAGE BODY def_ldg_strat

CREATE OR REPLACE PACKAGE BODY def_ldg_strat IS


/******************************************************************************
 *
 * FUNCTION get_def_ldg_strat_name
 *
 * Description:
 *   Function to retrieve the name of the default loading strategy, as defined
 *   in the DB_CONFIG table
 *
 * Input Parameters:
 *   None
 *
 * Returns:
 *   The name of the default loading strategy, or NULL if it cannot be found
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION get_def_ldg_strat_name RETURN db_config.db_value%TYPE IS
   l_dls_name db_config.db_value%TYPE;
BEGIN
   SELECT db_value INTO l_dls_name
   FROM db_config
   WHERE db_item = 'ldg_strat_def';
   
   RETURN l_dls_name;
   
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END get_def_ldg_strat_name;


/******************************************************************************
 *
 * FUNCTION get_def_ldg_strat_scan_code
 *
 * Description:
 *   Function to retrieve the code representing the default loading strategy, as
 *   used by the scanner. This is defined in the LDG_STRAT table. Note that if
 *   more than one code is defined for a particular strategy, the earliest
 *   (alphabetically) will be returned, ie. 'A' rather than 'L' for LoadAll
 *
 * Input Parameters:
 *   None
 *
 * Returns:
 *   The code representing the default loading strategy, as used by the scanner,
 *   or NULL if it cannot be found
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION get_def_ldg_strat_scan_code RETURN ldg_strat.ldg_strat_code%TYPE IS
   l_dls_name db_config.db_value%TYPE;
   l_dls_code ldg_strat.ldg_strat_code%TYPE;
BEGIN
   -- Select name
   l_dls_name := get_def_ldg_strat_name;
   
   -- Select the appropriate code
   -- Note: The nested select is necessary so that LoadAll returns 'A' as
   -- required by the scanner (it is in the LDG_STRAT table twice)
   SELECT ldg_strat_code INTO l_dls_code
   FROM
      (SELECT ldg_strat_code
      FROM ldg_strat
      WHERE ldg_strat_name = l_dls_name
      ORDER BY ldg_strat_code)
   WHERE ROWNUM = 1;
   
   RETURN l_dls_code;
   
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END get_def_ldg_strat_scan_code;


END def_ldg_strat;
/

