DDL Oracle

DDL Oracle

CREATE TABLE galmacen (
    almid number(10) default 0 not null,
    codigo varchar2(6 CHAR) not null,
    nomalm varchar2(40 CHAR) not null,
    tercer varchar2(12 CHAR) not null,
    tipdir varchar2(6 CHAR) default '0' not null,
    empcode varchar2(4 CHAR) not null,
    agrupa varchar2(4 CHAR) not null,
    recint varchar2(6 CHAR) not null,
    delega varchar2(6 CHAR),
    depart varchar2(6 CHAR),
    codpre varchar2(10 CHAR),
    stkneg number(5) default 0 not null,
    stkrup varchar2(1 CHAR) default 'N' not null,
    tipval varchar2(1 CHAR) not null,
    cosuni varchar2(1 CHAR) default 'N' not null,
    costol number(5) default 0 not null,
    fecval date not null,
    aplven number(5) default 0 not null,
    indlot number(5) default 1 not null,
    disdel number(5) default 0 not null,
    reflec number(10),
    capdes number(11,3) default 0.000 not null,
    horini number(5) default 0 not null,
    horfin number(5) default 0 not null,
    tippro varchar2(4 CHAR),
    impre1 varchar2(10 CHAR),
    impre2 varchar2(10 CHAR),
    tipast varchar2(4 CHAR),
    fecalt date default CURRENT_DATE not null,
    fecbaj date,
    fecult date not null,
    feccon date,
    inipot number(5),
    estado varchar2(1 CHAR) not null,
    errnum number(5) default 0 not null,
    wkfnum number(5),
    auxchr1 varchar2(1 CHAR),
    auxchr2 varchar2(1 CHAR),
    auxchr3 varchar2(1 CHAR),
    auxchr4 varchar2(1 CHAR),
    auxchr5 varchar2(1 CHAR),
    auxfec1 date,
    auxfec2 date,
    auxnum1 number(5),
    auxnum2 number(5),
    auxnum3 number(5),
    user_created varchar2(20 CHAR) default user not null,
    date_created timestamp default CURRENT_TIMESTAMP not null,
    user_updated varchar2(20 CHAR) default user not null,
    date_updated timestamp default CURRENT_TIMESTAMP not null
      , CONSTRAINT c_galmacen1
     CHECK (
    (tipval IN ('F', 'L', 'M', 'N'))
 ) DEFERRABLE
      , CONSTRAINT c_galmacen2
     CHECK (
    (cosuni IN ('S', 'N'))
 ) DEFERRABLE
      , CONSTRAINT c_galmacen3
     CHECK (
    (feccon IS NULL OR feccon <= fecval)
 ) DEFERRABLE
      , CONSTRAINT c_galmacen4
     CHECK (
    (feccon IS NULL OR
    (feccon IS NOT NULL AND tipast IS NOT NULL))
 ) DEFERRABLE
      , CONSTRAINT c_galmacen5
     CHECK (
    (fecbaj IS NULL OR fecalt <= fecbaj)
 ) DEFERRABLE
      , CONSTRAINT c_galmacen6
     CHECK (
    (horini <= horfin)
 ) DEFERRABLE
      , CONSTRAINT c_galmacen7
     CHECK (
    ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23))
 ) DEFERRABLE
      , CONSTRAINT c_galmacen8
     CHECK (
    (costol = 0 OR (costol > 0 AND tipval = 'M'))
 ) DEFERRABLE
      , CONSTRAINT c_galmacen9
     CHECK (
    errnum = 0 OR (errnum != 0 AND estado = 'B')
 ) DEFERRABLE
      , CONSTRAINT c_galmacen10
     CHECK (
    wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0)
 ) DEFERRABLE
      , CONSTRAINT c_galmacen11
     CHECK (
      stkrup IN ('N', 'G', 'Z')
 ) DEFERRABLE
);

-- Sequence for serial column. Tabname CRC: 31e1e7a9
create sequence seq_31e1e7a9_almid start with 1 increment by 1;
-- Trigger for sequence. Automatically increases nextvalue of sequence.
create or replace trigger galmacen_ins_seq before insert on galmacen for each row
 declare
    v_newval number(12) := 0;
    v_incval number(12) := 0;
 begin
    if (:new.almid = 0) or (:new.almid is null) then
      select seq_31e1e7a9_almid.nextval into v_newval from dual;
    else
      v_newval := :new.almid;
      v_incval := 1;
      loop
        exit when v_incval >= v_newval;
        select seq_31e1e7a9_almid.nextval into v_incval from dual;
      end loop;
    end if;
    :new.almid := v_newval;
end;
.
run

CREATE INDEX u_galmacen1 ON galmacen(almid);
ALTER TABLE galmacen
 ADD CONSTRAINT u_galmacen1 UNIQUE (almid) DEFERRABLE ;

CREATE INDEX p_galmacen ON galmacen(codigo);
ALTER TABLE galmacen
 ADD CONSTRAINT p_galmacen
 PRIMARY KEY (codigo) DEFERRABLE ;

CREATE  INDEX f_galmacen1 ON galmacen(tercer);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer)
 REFERENCES ctercero(codigo) DEFERRABLE ;

CREATE  INDEX f_galmacen2 ON galmacen(tipast);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast)
 REFERENCES gconasih(codigo) DEFERRABLE ;

CREATE  INDEX f_galmacen3 ON galmacen(agrupa);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa)
 REFERENCES gagrupac(codigo) DEFERRABLE ;

CREATE  INDEX f_galmacen4 ON galmacen(delega,depart);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart)
 REFERENCES gdeparta(delega,depart) DEFERRABLE ;

CREATE  UNIQUE  INDEX f_galmacen5 ON galmacen(tercer,tipdir);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir)
 REFERENCES cterdire(codigo,tipdir) DEFERRABLE ;

CREATE  INDEX f_galmacen6 ON galmacen(recint);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint)
 REFERENCES galmreci(codigo) DEFERRABLE ;

CREATE  INDEX f_galmacen7 ON galmacen(empcode);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode)
 REFERENCES cempresa(empcode) DEFERRABLE ;

CREATE  INDEX f_galmacen8 ON galmacen(tippro);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro)
 REFERENCES gmovprod(codigo) DEFERRABLE ;