rem ***************************************************************************
rem *                                                                         *
rem *             Copyright (c) Ferranti Air Systems Limited 2001.            *
rem *                           All rights reserved.                          *
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Filename:              pkg73_data_validation.sql                        *
rem * Module Name:           GDD                                              *
rem * Subsystem Name:        Database                                         *
rem * Originating Author:    K. Oborn                                         *
rem * Design Reference:      FAS/FAIMS/SS/212                                 *
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Description:                                                            *
rem *                                                                         *
rem *    This package is a special BRS version of FAIMS pkg73_data_validation *
rem *    that includes a procedure to check the date. The other functions     *
rem *    have been removed because they depend upon tables and other          *
rem *    packages that are not required for BRS.                              *
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Program Identification Array:                                           *
rem *                                                                         *
rem * "%Z%%M% VERSION %I% %E% %U%"
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Modification History:                                                   *
rem *                                                                         *
rem Rel Date/Time      Proj/Id  CA Ref/Details Of Change                      *
rem --- -------------- -------- --------------------------------------------- *
rem ***************************************************************************/

CREATE OR REPLACE PACKAGE BODY
   -- Identifier: "%Z%%M% VERSION %I% %E% %U%"
pkg73_data_validation AS

/******************************************************************************
 *
 * FUNCTION F73_USER_VIEWS_CHECK
 *
 ******************************************************************************/

FUNCTION f73_user_views_check(
   p_view_name IN user_views.view_name%TYPE) RETURN BOOLEAN IS

   -- local declarations

   -- Number of rows returned by check.
   l_count NUMBER;

   -- value to return to calling module
   l_return_value BOOLEAN := TRUE;

BEGIN

   -- check if data has been provided
   IF p_view_name IS NOT NULL THEN

      -- carry out check
      SELECT count(*) INTO l_count FROM user_views
         WHERE view_name = p_view_name;
      IF l_count = 0 THEN
         -- view was not found in USER_VIEWS so return FALSE
         l_return_value := FALSE;
      END IF;
   END IF;

   RETURN l_return_value; -- return result

END f73_user_views_check;

/******************************************************************************
 *
 * FUNCTION F73_USER_TAB_COLUMNS_CHECK
 *
 ******************************************************************************/

FUNCTION f73_user_tab_columns_check(
   p_table_name IN user_tab_columns.table_name%TYPE,
   p_column_name IN user_tab_columns.column_name%TYPE) RETURN BOOLEAN IS

   -- local declarations

   -- Number of rows returned by check.
   l_count NUMBER;

   -- value to return to calling module
   l_return_value BOOLEAN := TRUE;

BEGIN

   -- check if data has been provided
   IF p_table_name IS NOT NULL AND p_column_name IS NOT NULL THEN

      -- carry out check
      SELECT count(*) INTO l_count FROM user_tab_columns
         WHERE table_name = p_table_name AND column_name = p_column_name;

      IF l_count = 0 THEN
         -- column was not found in USER_TAB_COLUMNS so return FALSE
         l_return_value := FALSE;
      END IF;
   END IF;

   RETURN l_return_value; -- return result

END f73_user_tab_columns_check;

/******************************************************************************
 *
 * FUNCTION F73_USER_COL_LIST_CHECK
 *
 ******************************************************************************/

FUNCTION f73_user_col_list_check(
   p_table_name IN user_tab_columns.table_name%TYPE,
   p_column_list IN varchar2) RETURN BOOLEAN IS

   -- local declarations

   -- field from list currently being checked
   l_column_name user_tab_columns.column_name%TYPE;

   -- current character position in list of columns
   l_current_position NUMBER := 1;

   -- position of next delimiter in list of columns
   l_delimiter_position NUMBER;

   -- length of colum list
   l_length_list NUMBER;

   -- delimiter value in list
   l_delimiter VARCHAR2(1) := ',';

   -- value to return to calling module
   l_return_value BOOLEAN := TRUE;

BEGIN

   -- check if data has been provided
   IF p_table_name IS NOT NULL AND p_column_list IS NOT NULL THEN

      -- get length of list of columns
      l_length_list := LENGTHB(p_column_list);

      -- loop round extracting columns from list and checking they exist.
      -- exit at end of list or if column not found in table
      LOOP
         EXIT WHEN l_current_position > l_length_list OR NOT l_return_value;

         -- get position of next delimiter
         l_delimiter_position := INSTRB(p_column_list,l_delimiter,
            l_current_position);

         -- if l_delimiter_position is zero then this is the last column
         IF l_delimiter_position = 0 THEN

            -- Extract the column name.  This is the characters from the
            -- current string position
            l_column_name := SUBSTRB(p_column_list,l_current_position);

            -- Set the current string position to after the end of the string
            -- so we will exit the loop next time arround.
            l_current_position := l_length_list + 1;

         ELSE
            -- this is not the last column

            -- There is another delimiter so select characters upto but not
            -- including delimiter
            l_column_name := SUBSTRB(p_column_list,l_current_position,
               l_delimiter_position-l_current_position);

            -- Set the current string position to the character after
            -- the delimiter
            l_current_position := l_delimiter_position + 1;

         END IF;

         -- check current column exists in USER_TAB_COLUMNS
         l_return_value :=
            f73_user_tab_columns_check(p_table_name,l_column_name);

      END LOOP;

   END IF;

   RETURN l_return_value; -- return result

END f73_user_col_list_check;

/******************************************************************************
 *
 * FUNCTION F73_USER_TABLES_CHECK
 *
 ******************************************************************************/

FUNCTION f73_user_tables_check(p_table_name IN user_tables.table_name%TYPE)
                               RETURN BOOLEAN IS

   -- local declarations

   -- Number of rows returned by check.
   l_count NUMBER;

   -- value to return to calling module
   l_return_value BOOLEAN := TRUE;

BEGIN

   -- check if data has been provided
   IF p_table_name IS NOT NULL THEN

      -- carry out check
      SELECT count(*) INTO l_count FROM user_tables
         WHERE table_name = p_table_name;

      IF l_count = 0 THEN
         -- table was not found in USER_TABLES so return FALSE
         l_return_value := FALSE;
      END IF;
   END IF;

   RETURN l_return_value; -- return result

END f73_user_tables_check;

/******************************************************************************
 *
 * PROCEDURE P73_CHECK_DATE
 *
 ******************************************************************************/

PROCEDURE p73_check_date(p_date        IN DATE,
                         p_column_name IN VARCHAR2,
                         p_system_id   IN language_texts.system_id%TYPE) IS
BEGIN

   IF p_date IS NOT NULL THEN
      -- Date has a value, so check it is valid
      IF NOT ( TRUNC(p_date) BETWEEN TO_DATE('19000101', 'YYYYMMDD') AND
                                     TO_DATE('20371231', 'YYYYMMDD') ) THEN
         -- Date is outside the valid range - raise an error
         p73_raise_appn_error(736901,
            'Date out of range (date, column name, system id)',
            p_date || ', ' || NVL(p_column_name, 'NULL') || ', ' || p_system_id,
            FALSE, p_system_id);
      END IF; -- NOT ( TRUNC(p_date)...
   END IF; -- p_date IS NOT NULL

END p73_check_date;

END pkg73_data_validation;
/
