74 lines
2.8 KiB
SQL
74 lines
2.8 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
DECLARE
|
|
v_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'TB_FLASH_META_PERDA_STG';
|
|
IF v_count = 0 THEN
|
|
EXECUTE IMMEDIATE '
|
|
CREATE TABLE TB_FLASH_META_PERDA_STG (
|
|
COD_EMPRESA NUMBER NOT NULL,
|
|
TIPO_NODO VARCHAR2(30) NOT NULL,
|
|
CODIGO_NODO VARCHAR2(60) NOT NULL,
|
|
CODIGO_NODO_PAI VARCHAR2(60),
|
|
TIPO_NODO_PAI VARCHAR2(30),
|
|
DATA_REFERENCIA DATE NOT NULL,
|
|
ANO_REFERENCIA NUMBER(4,0) NOT NULL,
|
|
MES_REFERENCIA NUMBER(2,0) NOT NULL,
|
|
DIA_REFERENCIA NUMBER(2,0) NOT NULL,
|
|
PERCENTUAL_META NUMBER(10,4),
|
|
VALOR_META NUMBER(18,6),
|
|
OBSERVACAO VARCHAR2(500),
|
|
ORIGEM_META VARCHAR2(100),
|
|
DATA_CARGA DATE DEFAULT SYSDATE,
|
|
CONSTRAINT CK_TB_FLASH_META_PRD_STG_TIPO CHECK (TIPO_NODO IN (''EMPRESA'', ''CATEGORIA'', ''PRODUTO'')),
|
|
CONSTRAINT CK_TB_FLASH_META_PRD_STG_TPAI CHECK (TIPO_NODO_PAI IN (''EMPRESA'', ''CATEGORIA'', ''PRODUTO'') OR TIPO_NODO_PAI IS NULL),
|
|
CONSTRAINT CK_TB_FLASH_META_PRD_STG_MES CHECK (MES_REFERENCIA BETWEEN 1 AND 12),
|
|
CONSTRAINT CK_TB_FLASH_META_PRD_STG_DIA CHECK (DIA_REFERENCIA BETWEEN 1 AND 31)
|
|
)';
|
|
END IF;
|
|
END;
|
|
-- +goose StatementEnd
|
|
-- +goose StatementBegin
|
|
DECLARE
|
|
v_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO v_count FROM user_indexes WHERE index_name = 'IX_TB_FLASH_META_PRD_STG_01';
|
|
IF v_count = 0 THEN
|
|
EXECUTE IMMEDIATE 'CREATE INDEX IX_TB_FLASH_META_PRD_STG_01 ON TB_FLASH_META_PERDA_STG (COD_EMPRESA, DATA_REFERENCIA)';
|
|
END IF;
|
|
END;
|
|
-- +goose StatementEnd
|
|
-- +goose StatementBegin
|
|
DECLARE
|
|
v_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO v_count FROM user_indexes WHERE index_name = 'IX_TB_FLASH_META_PRD_STG_02';
|
|
IF v_count = 0 THEN
|
|
EXECUTE IMMEDIATE 'CREATE INDEX IX_TB_FLASH_META_PRD_STG_02 ON TB_FLASH_META_PERDA_STG (TIPO_NODO, CODIGO_NODO, CODIGO_NODO_PAI)';
|
|
END IF;
|
|
END;
|
|
-- +goose StatementEnd
|
|
-- +goose StatementBegin
|
|
DECLARE
|
|
v_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO v_count FROM user_indexes WHERE index_name = 'IX_TB_FLASH_META_PRD_STG_03';
|
|
IF v_count = 0 THEN
|
|
EXECUTE IMMEDIATE 'CREATE INDEX IX_TB_FLASH_META_PRD_STG_03 ON TB_FLASH_META_PERDA_STG (ORIGEM_META)';
|
|
END IF;
|
|
END;
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DECLARE
|
|
v_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'TB_FLASH_META_PERDA_STG';
|
|
IF v_count > 0 THEN
|
|
EXECUTE IMMEDIATE 'DROP TABLE TB_FLASH_META_PERDA_STG CASCADE CONSTRAINTS PURGE';
|
|
END IF;
|
|
END;
|
|
-- +goose StatementEnd
|