CREATE OR REPLACE PROCEDURE P_Check_Formula_AIT
--=============================================================================
-- Copyright 2008, Gloubiboulga
--=============================================================================
-- Purpose     : This procedure check content of Formula AIT for TM existence
-- Input       : The current spacecraft and the formula ait
-- Output      : True if the formula ait contains valid TM, False otherwise
--             : The TM Code in error. '' if no error
-- Restriction : This procedure is used in TUB and TIB triggers on TM table and
--               only if parameter VERIF_TM_FORMULA = 'Y'
--=============================================================================
(as_spacecraft       IN  VARCHAR2,
 as_status_code      IN  VARCHAR2,
 an_sc_vers_id       IN  NUMBER,
 as_formula_ait      IN  VARCHAR2,
 ab_check_formula    OUT BOOLEAN,
 as_tm_error         OUT VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
s_measure_code           VARCHAR2(10);
s_formula_ait            VARCHAR2(240);
length_formula           NUMBER;
pos                      NUMBER;
s_extract_tm             VARCHAR2(10);
counter                  NUMBER;
FOUND                    BOOLEAN;
-- Test if 'extracted' TM from formula AIT exists as a real TM
CURSOR cpk1_tm(var_spacecraft VARCHAR,
               var_status_code VARCHAR,
               var_sc_vers_id NUMBER,
               var_measure_code VARCHAR) IS
  SELECT 1
  FROM   TM
  WHERE  SPACECRAFT = var_spacecraft
  AND    STATUS_CODE = var_status_code
  AND    SC_VERS_ID = var_sc_vers_id
  AND    MEASURE_CODE = var_measure_code
  AND    var_spacecraft IS NOT NULL
  AND    var_status_code IS NOT NULL
  AND    var_sc_vers_id IS NOT NULL
  AND    var_measure_code IS NOT NULL;
BEGIN
    ab_check_formula := TRUE;
    -- Replace specific characters by spaces
    s_formula_ait := as_formula_ait;

    s_formula_ait := REPLACE(s_formula_ait, '(', ' ');
    s_formula_ait := REPLACE(s_formula_ait, ')', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '*', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '/', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '+', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '-', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '&', ' ');
    s_formula_ait := REPLACE(s_formula_ait, 'ORD', ' ');
    s_formula_ait := REPLACE(s_formula_ait, 'OR', ' ');
    s_formula_ait := REPLACE(s_formula_ait, 'AND', ' ');
    s_formula_ait := REPLACE(s_formula_ait, 'NOT', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '<', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '>', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '<=', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '>=', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '_', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '=', ' ');
    s_formula_ait := REPLACE(s_formula_ait, '.', ' ');
    s_formula_ait := REPLACE(s_formula_ait, ':', ' ');
    s_formula_ait := TRIM(s_formula_ait);
    s_formula_ait := s_formula_ait || ' ';

    length_formula := LENGTH(s_formula_ait);
    WHILE (length_formula > 0) AND (ab_check_formula = TRUE)
    LOOP
        s_formula_ait := LTRIM(s_formula_ait);
        pos := INSTR(s_formula_ait, ' ', 1);
        s_extract_tm := SUBSTR(s_formula_ait, 1, pos - 1);

        IF INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', SUBSTR(s_extract_tm, 1, 1)) > 0 THEN
            -- Search existence of the "extracted" TM
            OPEN  cpk1_tm(as_spacecraft,
                          as_status_code,
                          an_sc_vers_id,
                          s_extract_tm);
            FETCH cpk1_tm INTO counter;
            FOUND := cpk1_tm%FOUND;
            IF FOUND = FALSE THEN
                ab_check_formula := FALSE;
            END IF;
            EXIT WHEN cpk1_tm%NOTFOUND;
            CLOSE cpk1_tm;
        END IF;

        -- Extract next TM in formula AIT field
        s_formula_ait := SUBSTR(s_formula_ait, pos);
        length_formula := LENGTH(s_formula_ait);
    END LOOP;
    IF ab_check_formula = TRUE THEN
       as_tm_error := '';
    ELSE
       as_tm_error := s_extract_tm;
    END IF;
    COMMIT;
END P_Check_Formula_AIT;
/

// Check formula_ait content through a stored_proc
Declare PROC_check_formula_ait Procedure For
  P_Check_Formula_AIT (:gstr_datasource.s_spacecraft,
                :gstr_datasource.s_status,
                :gstr_datasource.l_version, 
                :ls_formula_ait)
  USING SQLCA;
EXECUTE PROC_check_formula_ait;
IF SQLCA.sqlcode <> -1 THEN
  FETCH PROC_check_formula_ait INTO :lb_check_formula, :ls_tm_error;
END IF
CLOSE PROC_check_formula_ait;