Details | Last modification | View Log | RSS feed
| Rev | Author | Line No. | Line |
|---|---|---|---|
| 1 | espaco | 1 | /*========tur===================================================*/ |
| 2 | /* DBMS name: PostgreSQL 8 */ |
||
| 3 | /* Criado em: 20/02/2020 */ |
||
| 4 | /* Autor: Bruno Lopes Peixoto */ |
||
| 5 | /*==============================================================*/ |
||
| 6 | |||
| 7 | SET search_path = sc_sec; |
||
| 8 | |||
| 9 | /*==============================================================*/ |
||
| 10 | /* Table: sec_parametro */ |
||
| 11 | /*==============================================================*/ |
||
| 12 | CREATE TABLE sec_parametro ( |
||
| 13 | cod_parametro INT8 NOT NULL, |
||
| 14 | dsc_parametro VARCHAR(200) NOT NULL, |
||
| 15 | dsc_valor VARCHAR(20) NOT NULL, |
||
| 16 | ind_ativo BOOL NOT NULL, |
||
| 17 | CONSTRAINT pk_parametro PRIMARY KEY (cod_parametro) |
||
| 18 | ); |
||
| 19 | |||
| 20 | -- set table ownership |
||
| 21 | ALTER TABLE sec_parametro owner TO sc_sec |
||
| 22 | ; |
||
| 23 | |||
| 24 | INSERT INTO sc_sec.sec_parametro(cod_parametro, dsc_parametro, dsc_valor, ind_ativo) VALUES (1, 'TEMPO DE ESTOQUE MÍNIMO PARA COMPRAS (EM DIAS)', '45', TRUE); |
||
| 25 | INSERT INTO sc_sec.sec_parametro(cod_parametro, dsc_parametro, dsc_valor, ind_ativo) VALUES (2, 'TEMPO DE ESTOQUE DE REPOSIÇÃO PARA COMPRAS (EM DIAS)', '60', TRUE); |
||
| 26 | |||
| 27 | CREATE SEQUENCE sq_empresaadquirente |
||
| 28 | INCREMENT 1 |
||
| 29 | minvalue 1 |
||
| 30 | maxvalue 99999999 |
||
| 31 | START 1; |
||
| 32 | |||
| 33 | ALTER TABLE sq_empresaadquirente owner TO sc_sec |
||
| 34 | ; |
||
| 35 | |||
| 36 | /*==============================================================*/ |
||
| 37 | /* Table: sec_empresa_adquirente */ |
||
| 38 | /*==============================================================*/ |
||
| 39 | CREATE TABLE sec_empresa_adquirente ( |
||
| 40 | seq_empresa_adquirente INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_empresaadquirente'::regclass), |
||
| 41 | dsc_empresa_adquirente VARCHAR(120) NOT NULL, |
||
| 42 | ind_ativo BOOL NOT NULL, |
||
| 43 | CONSTRAINT pk_empresaadquirente PRIMARY KEY (seq_empresa_adquirente) |
||
| 44 | ); |
||
| 45 | |||
| 46 | -- set table ownership |
||
| 47 | ALTER TABLE sec_empresa_adquirente owner TO sc_sec |
||
| 48 | ; |
||
| 49 | |||
| 50 | ALTER TABLE sec_taxa ADD COLUMN seq_empresa_adquirente INT8 NULL; |
||
| 51 | ALTER TABLE sec_taxa |
||
| 52 | ADD CONSTRAINT fk_taxa_empresaadquirente FOREIGN KEY (seq_empresa_adquirente) |
||
| 53 | REFERENCES sec_empresa_adquirente (seq_empresa_adquirente) |
||
| 54 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 55 | |||
| 56 | |||
| 57 | CREATE SEQUENCE sq_maquineta |
||
| 58 | INCREMENT 1 |
||
| 59 | minvalue 1 |
||
| 60 | maxvalue 99999999 |
||
| 61 | START 1; |
||
| 62 | |||
| 63 | ALTER TABLE sq_maquineta owner TO sc_sec |
||
| 64 | ; |
||
| 65 | |||
| 66 | /*==============================================================*/ |
||
| 67 | /* Table: sec_maquineta */ |
||
| 68 | /*==============================================================*/ |
||
| 69 | CREATE TABLE sec_maquineta ( |
||
| 70 | seq_maquineta INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_maquineta'::regclass), |
||
| 71 | seq_empresa_adquirente INT8 NOT NULL, |
||
| 72 | seq_pessoa INT8 NOT NULL, |
||
| 73 | dsc_maquineta VARCHAR(120) NOT NULL, |
||
| 74 | dsc_senha VARCHAR(20) NULL, |
||
| 75 | val_aluguel NUMERIC(10,2) NULL, |
||
| 76 | dsc_observacao VARCHAR(400) NULL, |
||
| 77 | ind_ativo BOOL NOT NULL, |
||
| 78 | CONSTRAINT pk_maquineta PRIMARY KEY (seq_maquineta) |
||
| 79 | ); |
||
| 80 | |||
| 81 | ALTER TABLE sec_maquineta |
||
| 82 | ADD CONSTRAINT fk_maquineta_pessoa FOREIGN KEY (seq_pessoa) |
||
| 83 | REFERENCES sec_pessoa (seq_pessoa) |
||
| 84 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 85 | |||
| 86 | -- set table ownership |
||
| 87 | ALTER TABLE sec_maquineta owner TO sc_sec |
||
| 88 | ; |
||
| 89 | ALTER TABLE sec_maquineta |
||
| 90 | ADD CONSTRAINT fk_maquineta_empresaadquirente FOREIGN KEY (seq_empresa_adquirente) |
||
| 91 | REFERENCES sec_empresa_adquirente (seq_empresa_adquirente) |
||
| 92 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 93 | |||
| 94 | ALTER TABLE sec_loja ADD COLUMN seq_maquineta INT8 NULL; |
||
| 95 | ALTER TABLE sec_loja |
||
| 96 | ADD CONSTRAINT fk_loja_maquineta FOREIGN KEY (seq_maquineta) |
||
| 97 | REFERENCES sec_maquineta (seq_maquineta) |
||
| 98 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 99 | |||
| 100 | ALTER TABLE sec_venda ADD COLUMN seq_maquineta INT8 NULL; |
||
| 101 | ALTER TABLE sec_venda |
||
| 102 | ADD CONSTRAINT fk_venda_maquineta FOREIGN KEY (seq_maquineta) |
||
| 103 | REFERENCES sec_maquineta (seq_maquineta) |
||
| 104 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 105 | |||
| 106 | |||
| 107 | UPDATE sec_venda v SET seq_maquineta = NULL; |
||
| 108 | UPDATE sec_venda v SET seq_maquineta = (SELECT l.seq_maquineta FROM sec_loja l WHERE l.seq_loja = v.seq_loja) WHERE seq_maquineta IS NULL; |
||
| 109 | UPDATE sec_venda v SET seq_maquineta = NULL WHERE v.dth_venda > '2020-02-21 00:00:01' AND v.seq_loja = 8; |
||
| 110 | UPDATE sec_venda v SET seq_maquineta = NULL WHERE NOT EXISTS (SELECT * FROM sec_venda_formapagament vf WHERE vf.seq_venda = v.seq_venda AND vf.cod_formapagament IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15)); |
||
| 111 | |||
| 112 | -- ADICIONAR MAQUINETA DA SAFRA |
||
| 113 | UPDATE sec_venda v SET seq_maquineta = (SELECT l.seq_maquineta FROM sec_loja l WHERE l.seq_loja = v.seq_loja) WHERE v.dth_venda > '2020-02-21 00:00:01' AND v.seq_loja = 8; |
||
| 114 | |||
| 115 | |||
| 116 | SET search_path = public; |