/*
 * $Revision$
 * $Id$
 */

#ifndef _INSSQL_H
#define _INSSQL_H


#define CHECK_SRT_VERHUIZING(tabel, key_veld, ref_key, kindje_tabel, message)  \
 DECLARE                                                                       \
    dummy CHAR;                                                                \
 BEGIN                                                                         \
    IF (:new.key_veld = :old.key_veld) AND (:new.ref_key <> :old.ref_key)      \
    THEN                                                                       \
            SELECT 'x' INTO dummy                                              \
            FROM kindje_tabel                                                  \
            WHERE kindje_tabel.key_veld = :old.key_veld;                       \
            APPLICATION_ERROR(-20000,message);                                 \
    END IF;                                                                    \
 EXCEPTION                                                                     \
    WHEN NO_DATA_FOUND THEN                                                    \
        NULL;                                                                  \
    WHEN TOO_MANY_ROWS THEN                                                    \
        APPLICATION_ERROR(-20000,message);                                     \
 END


#define CHECK_REFERENCE_PLAATS(key_ruimte_veld, key_terrein_veld, message)     \
 BEGIN                                                                         \
    IF :new.key_ruimte_veld IS NULL                                            \
        AND :new.key_terrein_veld IS NULL                                      \
    THEN                                                                       \
         APPLICATION_ERROR(-20000, message);                                   \
    END IF;                                                                    \
 END


#if 1   // Nieuwe CHECK_ATTRIBUTE()
#define CHECK_ATTRIBUTE(fac_kolom, attribute_key)                              \
  IF (:old.fac_kolom <> :new.fac_kolom)                                        \
  OR (:old.fac_kolom IS NOT NULL AND :new.fac_kolom IS NULL)                   \
  OR (:old.fac_kolom IS NULL AND :new.fac_kolom IS NOT NULL)                   \
  THEN                                                                         \
     INS_P_INS_CHECK_ATTRIBUTE(:new.fac_kolom, :new.ins_srtdeel_key,           \
                               attribute_key);                                 \
  END IF

#else
#define CHECK_ATTRIBUTE(kolom_naam, attribuut_key)                             \
  DECLARE~                                                                     \
    tiep         VARCHAR2(1);                                                  \
    format_mask  VARCHAR2(100);                                                \
    max_lengte   NUMBER;                                                       \
    ware_lengte  NUMBER;                                                       \
    verplicht    NUMBER;                                                       \
    min_waarde   NUMBER;                                                       \
    max_waarde   NUMBER;                                                       \
    dec_waarde   NUMBER;                                                       \
    ware_waarde  NUMBER;~                                                      \
  BEGIN~                                                                       \
    SELECT ins_attribute_type, ins_attribute_length, ins_attribute_required,   \
      ins_attribute_min, ins_attribute_max, ins_attribute_dec                  \
    INTO tiep, max_lengte, verplicht, min_waarde, max_waarde, dec_waarde       \
    FROM ins_v_attribute_gegevens                                              \
    WHERE ins_srtdeel_key = :new.ins_srtdeel_key AND                           \
      ins_attribute_key = attribuut_key;~                                      \
/*  Al dan niet aanwezig */                                                    \
    ~                                                                          \
    IF verplicht = 1 AND :new.kolom_naam IS NULL                               \
    THEN                                                                       \
      APPLICATION_ERROR(-20004, to_char(attribuut_key)||                       \
        to_char(:new.ins_srtdeel_key)||'att_m008');~                           \
    ELSIF tiep = 'N'                                                           \
    THEN                                                                       \
/*  Hier moet het number_format gecheckt worden */                             \
      BEGIN~                                                                   \
        ware_waarde := to_number(:new.kolom_naam);                             \
                                                                               \
        IF ware_waarde < min_waarde OR ware_waarde > max_waarde                \
        THEN                                                                   \
/*  Te klein / te groot */                                                     \
          APPLICATION_ERROR(-20004, to_char(attribuut_key)||                   \
            to_char(:new.ins_srtdeel_key)||'att_m009');~                       \
        ELSE                                                                   \
/*  Bepaal het format_mask  */                                                 \
          IF dec_waarde IS NULL OR dec_waarde = 0                              \
          THEN                                                                 \
            format_mask := RPAD('9', max_lengte, '9');~                        \
          ELSE                                                                 \
            format_mask := RPAD('9', max_lengte - dec_waarde, '9')||           \
              'D'||RPAD('9', dec_waarde, '9');~                                \
          END IF;                                                              \
          ~                                                                    \
          :new.kolom_naam := LTRIM(to_char(ware_waarde, format_mask));         \
          IF INSTR(:new.kolom_naam, '#') <> 0                                  \
          THEN                                                                 \
            APPLICATION_ERROR(-20004, to_char(attribuut_key)||                 \
              to_char(:new.ins_srtdeel_key)||'att_m010');~                     \
          END IF;~                                                             \
        END IF;~                                                               \
      EXCEPTION                                                                \
        WHEN others                                                            \
        THEN                                                                   \
          APPLICATION_ERROR(-20004, to_char(attribuut_key)||                   \
            to_char(:new.ins_srtdeel_key)||'att_m010');~                       \
      END;~                                                                    \
                                                                               \
    ELSIF tiep = 'C'                                                           \
    THEN                                                                       \
      ware_lengte := LENGTH(:new.kolom_naam);                                  \
      IF ware_lengte > max_lengte                                              \
      THEN                                                                     \
        APPLICATION_ERROR(-20004, to_char(attribuut_key)||                     \
          to_char(:new.ins_srtdeel_key)||'att_m011');~                         \
      END IF;~                                                                 \
    END IF;                                                                    \
  EXCEPTION                                                                    \
    WHEN no_data_found THEN NULL;                                              \
  END
#endif // CHECK_ATTRIBUTE()


#define CHECK_DISCIPLINE_ATT_VULLING(FacKolom, AttributeKey)                   \
 IF :old.ins_attribute_key = AttributeKey                                      \
 THEN                                                                          \
    DECLARE                                                                    \
        dummy CHAR;                                                            \
    BEGIN                                                                      \
        SELECT 'X'                                                             \
        INTO dummy                                                             \
        FROM ins_v_aanwezigdeel INS_D, ins_srtdeel INS_SD, ins_srtgroep INS_SG \
        WHERE INS_D.ins_srtdeel_key = INS_SD.ins_srtdeel_key                   \
          AND INS_SD.ins_srtgroep_key = INS_SG.ins_srtgroep_key                \
          AND INS_SG.ins_discipline_key = :old.ins_srtinst_keys                \
          AND INS_D.FacKolom IS NOT NULL;                                      \
        APPLICATION_ERROR(-20003, 'att_m022');                                 \
    EXCEPTION                                                                  \
        WHEN NO_DATA_FOUND                                                     \
        THEN NULL;                                                             \
        WHEN TOO_MANY_ROWS                                                     \
        THEN APPLICATION_ERROR(-20003, 'att_m022');                            \
    END;                                                                       \
 END IF

#define CHECK_SRTGROEP_ATT_VULLING(FacKolom, AttributeKey)                     \
 IF :old.ins_attribute_key = AttributeKey                                      \
 THEN                                                                          \
    DECLARE                                                                    \
        dummy CHAR;                                                            \
    BEGIN                                                                      \
        SELECT 'X'                                                             \
        INTO dummy                                                             \
        FROM ins_v_aanwezigdeel INS_D, ins_srtdeel INS_SD                      \
        WHERE INS_D.ins_srtdeel_key = INS_SD.ins_srtdeel_key                   \
          AND INS_SD.ins_srtgroep_key = :old.ins_srtinst_keys                  \
          AND FacKolom IS NOT NULL;                                            \
        APPLICATION_ERROR(-20003, 'att_m022');                                 \
    EXCEPTION                                                                  \
        WHEN NO_DATA_FOUND                                                     \
        THEN NULL;                                                             \
        WHEN TOO_MANY_ROWS                                                     \
        THEN APPLICATION_ERROR(-20003, 'att_m022');                            \
    END;                                                                       \
 END IF

#define CHECK_SRTDEEL_ATT_VULLING(FacKolom, AttributeKey)                      \
 IF :old.ins_attribute_key = AttributeKey                                      \
 THEN                                                                          \
    DECLARE                                                                    \
        dummy CHAR;                                                            \
    BEGIN                                                                      \
        SELECT 'X'                                                             \
        INTO dummy                                                             \
        FROM ins_v_aanwezigdeel                                                \
        WHERE ins_srtdeel_key = :old.ins_srtinst_keys                          \
          AND FacKolom IS NOT NULL;                                            \
        APPLICATION_ERROR(-20003, 'att_m022');                                 \
    EXCEPTION                                                                  \
        WHEN NO_DATA_FOUND                                                     \
        THEN NULL;                                                             \
        WHEN TOO_MANY_ROWS                                                     \
        THEN APPLICATION_ERROR(-20003, 'att_m022');                            \
    END;                                                                       \
 END IF

#define INS_CHECK_NOG_REFERENCES(tabel,tabel_verwijder_veld,reference_view,    \
    reference_verwijder_veld, key_veld, message)                               \
 DECLARE                                                                       \
    dummy CHAR;                                                                \
 BEGIN                                                                         \
    IF :new.tabel_verwijder_veld IS NOT NULLDATUM                              \
            THEN                                                               \
            SELECT 'x' INTO dummy                                              \
            FROM reference_view                                                \
            WHERE reference_view.key_veld = :old.key_veld                      \
              AND reference_view.reference_verwijder_veld IS NULL;             \
            APPLICATION_ERROR(-20000,message);                                 \
    END IF;                                                                    \
 EXCEPTION                                                                     \
    WHEN NO_DATA_FOUND THEN                                                    \
        NULL;                                                                  \
    WHEN TOO_MANY_ROWS THEN                                                    \
        APPLICATION_ERROR(-20000,message);                                     \
 END

#endif // _INSSQL_H
