Database/BGT/BGT_TAB.SRC

#ifdef  BGT
/*
 * $Revision$
 * $Id$
 */

/* discipline=project */
CREATE_TABLE(bgt_disc_params,0)
(
    bgt_disc_params_key
        NUMBER (10) PRIMARY KEY NOT NULL,
    bgt_ins_discipline_key
        NUMBER (10)
        CONSTRAINT bgt_r_ins_discipine_key REFERENCES ins_tab_discipline (ins_discipline_key),
    bgt_disc_params_code  -- projectcode
        VARCHAR2 (20 CHAR),
    bgt_disc_params_opdrachtgever
        VARCHAR2 (200 CHAR),
    bgt_disc_params_startdatum
        DATE
);
/* deelproject */
CREATE_TABLE(bgt_project, 0)
(
    bgt_project_key
        NUMBER (10) PRIMARY KEY NOT NULL,
    ins_discipline_key
        NUMBER (10)
        CONSTRAINT bgt_r_ins_discipline_key1 REFERENCES ins_tab_discipline (ins_discipline_key),
    bgt_project_code
        VARCHAR2 (20 CHAR)
        CONSTRAINT bgt_c_bgt_project_code NOT NULL,
    bgt_project_omschrijving
        VARCHAR2 (60 CHAR),
    bgt_project_volgnr
        NUMBER (3),
    bgt_project_prijspeildatum
        DATE,
    bgt_project_info
        VARCHAR2 (4000 CHAR),
    bgt_project_notes
        VARCHAR2 (4000 CHAR),
    bgt_project_aanmaak
        DATE DEFAULT SYSDATE,
    bgt_project_verwijder
        DATE,
    prs_kostenplaats_key
        NUMBER (10)
        CONSTRAINT bgt_r_prs_kostenplaats_key1 REFERENCES prs_kostenplaats (prs_kostenplaats_key),
    CONSTRAINT bgt_u_bgt_project UNIQUE(ins_discipline_key, bgt_project_code, bgt_project_verwijder)
);

CREATE_TABLE(bgt_kostenrubriek,0)
(
    bgt_kostenrubriek_key
        NUMBER (10) PRIMARY KEY NOT NULL,
    bgt_project_key
        NUMBER (10)
        CONSTRAINT bgt_c_bgt_kostenrubriek1 NOT NULL
        CONSTRAINT bgt_r_bgt_project_key1 REFERENCES bgt_project (bgt_project_key),
    bgt_kostenrubriek_code
        VARCHAR2 (20 CHAR),
    bgt_kostenrubriek_oms
        VARCHAR2 (60 CHAR) NOT NULL,
    fin_btwtabelwaarde_key
        NUMBER(10)
        CONSTRAINT bgt_r_fin_btwtabelwaarde_key REFERENCES fin_btwtabelwaarde (fin_btwtabelwaarde_key)
);

ALTER TABLE prs_kostensoortgrp ADD bgt_kostenrubriek_key NUMBER(10) CONSTRAINT prs_r_bgt_kostenrubriek_key1 REFERENCES bgt_kostenrubriek (bgt_kostenrubriek_key);

CREATE_TABLE(bgt_budget,1)
(
    bgt_budget_key
        NUMBER (10) PRIMARY KEY NOT NULL,
    ins_discipline_key
        NUMBER (10)
        CONSTRAINT prs_r_ins_discipline_key1 REFERENCES ins_tab_discipline (ins_discipline_key),  /* ik denk nog not null?*/
    bgt_project_key
        NUMBER (10)
        CONSTRAINT prs_r_bgt_project_key1 REFERENCES bgt_project (bgt_project_key),           /* ik denk nog not null?*/
    bgt_kostenrubriek_key
        NUMBER (10)
        CONSTRAINT prs_r_bgt_kostenrubriek_key2 REFERENCES bgt_kostenrubriek (bgt_kostenrubriek_key),
    prs_kostensoortgrp_key
        NUMBER (10)
        CONSTRAINT prs_r_kostensoortgrp_key2 REFERENCES prs_kostensoortgrp (prs_kostensoortgrp_key),
    prs_kostensoort_key
        NUMBER (10)
        CONSTRAINT prs_r_kostensoort_key2 REFERENCES prs_kostensoort (prs_kostensoort_key),
    prs_kostenplaatsgrp_key
        NUMBER (10)
        CONSTRAINT prs_r_kostenplaatsgrp_key3 REFERENCES prs_kostenplaatsgrp (prs_kostenplaatsgrp_key),
    prs_kostenplaats_key
        NUMBER (10)
        CONSTRAINT prs_r_kostenplaats_key5 REFERENCES prs_kostenplaats (prs_kostenplaats_key),
    bgt_budget_bedrag
        NUMBER (12,2),
    bgt_budget_btwbedrag
        NUMBER (12,2),
    bgt_budget_isreserve
        NUMBER(1)
        DEFAULT (0)
        NOT NULL
        CONSTRAINT bgt_c_bgt_budget_isreserve CHECK(bgt_budget_isreserve IN (0,1)),
    bgt_budget_begin
        DATE,
    bgt_budget_eind
        DATE,
    bgt_budget_aanmaak
        DATE DEFAULT SYSDATE,
    bgt_budget_vervaldatum
        DATE,                                         -- of gewoon verwijderdatum?
    CONSTRAINT bgt_c_bgt_budget1 CHECK (  (bgt_budget_isreserve = 0 AND (   bgt_kostenrubriek_key IS NOT NULL
                                                                        OR (bgt_kostenrubriek_key IS NOT NULL AND prs_kostensoortgrp_key IS NOT NULL)
                                                                        OR (bgt_kostenrubriek_key IS NOT NULL AND prs_kostensoortgrp_key IS NOT NULL AND prs_kostensoort_key IS NOT NULL)
                                                                        OR prs_kostenplaatsgrp_key IS NOT NULL
                                                                        OR (prs_kostenplaatsgrp_key IS NOT NULL AND prs_kostenplaats_key IS NOT NULL)
                                                                        )
                                          )
                                       OR (bgt_budget_isreserve = 1 AND prs_kostensoortgrp_key IS NULL AND prs_kostenplaats_key IS NULL)
                                       ),
   CONSTRAINT bgt_u_budget UNIQUE (bgt_kostenrubriek_key, prs_kostensoortgrp_key, prs_kostensoort_key)
);

-- under construction
CREATE_TABLE(bgt_budgetmutatie, 1)
(
   bgt_budgetmutatie_key
        NUMBER (10) PRIMARY KEY,
   bgt_budgetmutatie_datum
        DATE NOT NULL,
   bgt_budgetmutatie_omschrijving
        VARCHAR2 (100) NOT NULL,
   prs_perslid_key
        NUMBER (10)
        NOT NULL
        CONSTRAINT bgt_r_prs_perslid_key2 REFERENCES prs_perslid (prs_perslid_key),
   bgt_budget_key_van
        NUMBER (10)
        CONSTRAINT bgt_r_bgt_budget1 REFERENCES bgt_budget (bgt_budget_key),
   bgt_budget_bedrag_van
        NUMBER (12, 2),
   bgt_budget_btwbedrag_van
        NUMBER (12, 2),
   bgt_budget_key_naar
        NUMBER (10)
        CONSTRAINT bgt_r_bgt_budget2 REFERENCES bgt_budget (bgt_budget_key),
   bgt_budget_bedrag_naar
        NUMBER (12, 2),
   bgt_budget_btwbedrag_naar
        NUMBER (12, 2),
   bgt_budgetmutatie_reserve
        NUMBER (1)
        DEFAULT (0)
        NOT NULL,
   bgt_budgetmutatie_code
        VARCHAR2 (2),
   bgt_budgetmutatie_volgnr
        NUMBER (3),
   CONSTRAINT bgt_c_bgt_budget_key1 CHECK(bgt_budget_key_van IS NOT NULL OR bgt_budget_key_naar IS NOT NULL)
);

REGISTERONCE('$Id$')

#endif // BGT