-- ============================================================
--   TABLE: HEADERINTSAPFOS 
-- ============================================================
CREATE TABLE HEADERINTSAPFOS (
   NUM_SEQ_HEADER VARCHAR2(20),
   COD_EMP_SAP	VARCHAR2(4), 
   COD_UNIDADE_SAP VARCHAR2(4),
   NUM_RNF NUMBER,
   DTA_RNF DATE,
   HRA_RNF VARCHAR2(6),
   DES_ARQUIVO VARCHAR2(100),
   QTD_NOTAS NUMBER(10),
   TIP_LOTE VARCHAR2(20),
   COD_UNIDADE_NL NUMBER,
   DTA_IMPORTACAO	DATE,
   DTA_PROCESS DATE,
   HRA_PROCESS VARCHAR2(6),
   IND_EXCLUSAO NUMBER,
   DTH_GERACAO_ARQ_CLI DATE,
   SEQ_ESCOLA_BRITANICA	NUMBER,
   QTD_NOTAS_ATIVAS NUMBER,
   NUM_HEADER_ORI NUMBER,
   DTA_REPROCESS DATE,
   HRA_REPROCESS VARCHAR2(6),
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_HEADERINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
   )
/

ALTER TABLE HEADERINTSAPFOS ADD CONSTRAINT PK_HEADERINTSAPFOS PRIMARY KEY (NUM_SEQ_HEADER)
/

ALTER TABLE HEADERINTSAPFOS ADD CONSTRAINT UN_HEADERINTSAPFOS_01 UNIQUE (COD_EMP_SAP, COD_UNIDADE_SAP, NUM_RNF)
/

-- ============================================================
--   TABLE: RETHEADINTSAPFOS 
-- ============================================================
CREATE TABLE RETHEADINTSAPFOS(
   NUM_SEQ_HEADER VARCHAR2(20),
   COD_EMP_SAP VARCHAR2(4),
   COD_UNIDADE_SAP VARCHAR2(4),
   NUM_RNF NUMBER,
   DTA_RNF DATE,
   DTA_IMPORTACAO	DATE,
   DTA_PROCESS	DATE,
   HRA_PROCESS	VARCHAR2(06),
   IND_EXCLUSAO NUMBER,
   DTA_REPROCESS DATE,
   HRA_REPROCESS VARCHAR2(06),
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_RETHEADINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

-- ============================================================
--   TABLE: BODYINTSAPFOS 
-- ============================================================
CREATE TABLE BODYINTSAPFOS (
   NUM_SEQ_HEADER VARCHAR2(20),
   NUM_SEQ_CORPO NUMBER,
   NUM_CNPJ_CPF VARCHAR2(14),
   COD_SERIE VARCHAR2(3),
   NUM_NOTA VARCHAR2(20),
   COD_RESULT_NFE NUMBER,
   IND_CONTINGENCIA NUMBER,
   NUM_RECIBO_NFE NUMBER,
   DES_CHAVE_NFE VARCHAR2(50),
   DTA_DANFE DATE,
   HRA_DANFE VARCHAR2(6),
   VLR_TOTAL NUMBER,
   DTA_EMISSAO DATE,
   DTA_LANCAMENTO DATE,
   DTA_VENCIMENTO DATE,
   NUM_NES NUMBER,
   NUM_SEQ_NE_NL NUMBER,
   COD_MAQ_NE_NL NUMBER,
   NUM_SEQ_NS_NL NUMBER,
   COD_MAQ_NS_NL NUMBER,
   NUM_DOC_CONT VARCHAR2(20),
   NUM_DOC_FISC VARCHAR2(20),
   NUM_DOC_RELAC VARCHAR2(20),
   IND_EXCLUSAO NUMBER,
   NUM_SEQ_TRF	NUMBER,
   COD_MAQ_TRF NUMBER,
   COD_OPER	NUMBER,
   NUM_CHAMADO	VARCHAR2(15),
   NUM_REQUISICAO_WEBB NUMBER,
   DTA_REPROCESS DATE,
   HRA_REPROCESS VARCHAR2(6),
   NUM_CNO VARCHAR2(14),
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_BODYINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

ALTER TABLE BODYINTSAPFOS ADD CONSTRAINT PK_BODYINTSAPFOS PRIMARY KEY(NUM_SEQ_HEADER, NUM_SEQ_CORPO)
/

ALTER TABLE BODYINTSAPFOS ADD CONSTRAINT UN_BODYINTSAPFOS UNIQUE(NUM_SEQ_HEADER, NUM_CNPJ_CPF, COD_SERIE, NUM_NOTA)
/

ALTER TABLE BODYINTSAPFOS ADD CONSTRAINT FK_BODYINTSAPFOS_HEADER FOREIGN KEY (NUM_SEQ_HEADER)
   REFERENCES HEADERINTSAPFOS(NUM_SEQ_HEADER)
/

-- ============================================================
--   TABLE: RETBODYINTSAPFOS 
-- ============================================================
CREATE TABLE RETBODYINTSAPFOS(
   NUM_SEQ_HEADER VARCHAR2(20),
   NUM_SEQ_CORPO NUMBER,
   NUM_DOC_CONT VARCHAR2(20),
   NUM_DOC_FISC VARCHAR2(20),
   NUM_DOC_RELAC VARCHAR2(20),
   IND_EXCLUSAO NUMBER,
   NUM_CNPJ_CPF VARCHAR2(14),
   COD_SERIE VARCHAR2(3),
   NUM_NOTA	VARCHAR2(20),
   DTA_VENCIMENTO DATE,
   DTA_REPROCESS DATE,
   HRA_REPROCESS VARCHAR2(06),
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_RETBODYINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

-- ============================================================
--   TABLE: ITEMINTSAPFOS 
-- ============================================================
CREATE TABLE ITEMINTSAPFOS(
   NUM_SEQ_HEADER VARCHAR2(20),
   NUM_SEQ_CORPO NUMBER,
   NUM_SEQ_ITEM NUMBER,
   NUM_ITEM NUMBER,
   TIP_DOCUMENTO VARCHAR2(03),
   VLR_ITEM NUMBER,
   NUM_CFOP	NUMBER,
   VLR_DESCONTO NUMBER,
   VLR_FRETE NUMBER,
   VLR_SEGURO NUMBER,
   VLR_OUTRAS_DESP NUMBER,
   COD_ITEM VARCHAR2(18),
   QTD_ITEM NUMBER,
   VLR_UNITARIO NUMBER,
   NUM_NOTA_ORIGEM VARCHAR2(20),
   COD_SERIE_ORIGEM VARCHAR2(3),
   DES_ITEM	VARCHAR2(50),
   COD_CC VARCHAR2(20),
   COD_NAT_R_PISCOF VARCHAR2(3),
   COD_TP_CR_PISCOF VARCHAR2(3),
   COD_NT_BC_PISCOF VARCHAR2(3),
   COD_NAT_FRETE VARCHAR2(1),
   COD_NBM VARCHAR2(50),
   COD_ITEM_NL	VARCHAR2(20),
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_ITEMINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

ALTER TABLE ITEMINTSAPFOS ADD CONSTRAINT PK_ITEMINTSAPFOS PRIMARY KEY(NUM_SEQ_HEADER, NUM_SEQ_CORPO, NUM_SEQ_ITEM)
/

-- ============================================================
--   TABLE: IMPOSTOINTSAPFOS 
-- ============================================================
CREATE TABLE IMPOSTOINTSAPFOS(
   NUM_SEQ_HEADER VARCHAR2(20),
   NUM_SEQ_CORPO NUMBER,
   NUM_SEQ_ITEM NUMBER,
   NUM_SEQ_IMPOSTO NUMBER,
   TIP_IMPOSTO VARCHAR2(20),
   VLR_BASE	NUMBER,
   VLR_RED_BASE NUMBER,
   VLR_OUTRAS NUMBER,
   PER_IMPOSTO NUMBER,
   VLR_IMPOSTO NUMBER,
   TIP_TRIBUTACAO VARCHAR2(03),
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_IMPOSTOINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

ALTER TABLE IMPOSTOINTSAPFOS ADD CONSTRAINT PK_IMPOSTOINTSAPFOS PRIMARY KEY(NUM_SEQ_HEADER, NUM_SEQ_CORPO, NUM_SEQ_ITEM, NUM_SEQ_IMPOSTO)
/

CREATE OR REPLACE VIEW V_HEADERINTSAPFOS AS
   SELECT NUM_SEQ_HEADER, COD_EMP_SAP, COD_UNIDADE_SAP, NUM_RNF, DTA_RNF, HRA_RNF,
          DES_ARQUIVO, QTD_NOTAS, TIP_LOTE, COD_UNIDADE_NL, DTA_IMPORTACAO, DTA_PROCESS,
          HRA_PROCESS, IND_EXCLUSAO, SEQ_ESCOLA_BRITANICA, DTH_GERACAO_ARQ_CLI, QTD_NOTAS_ATIVAS,
          NUM_HEADER_ORI, DTA_REPROCESS, HRA_REPROCESS
     FROM HEADERINTSAPFOS
/

CREATE OR REPLACE VIEW V_BODYINTSAPFOS AS
   SELECT NUM_SEQ_HEADER, NUM_SEQ_CORPO, NUM_CNPJ_CPF, COD_SERIE, NUM_NOTA, COD_RESULT_NFE, IND_CONTINGENCIA,
          NUM_RECIBO_NFE, DES_CHAVE_NFE, DTA_DANFE, HRA_DANFE, VLR_TOTAL, DTA_EMISSAO, DTA_LANCAMENTO,
          DTA_VENCIMENTO, NUM_NES, NUM_SEQ_NE_NL, COD_MAQ_NE_NL, NUM_SEQ_NS_NL, COD_MAQ_NS_NL,
          NUM_DOC_CONT, NUM_DOC_FISC, NUM_DOC_RELAC, IND_EXCLUSAO, NUM_SEQ_TRF, COD_MAQ_TRF, NUM_REQUISICAO_WEBB,
          DTA_REPROCESS, HRA_REPROCESS, NUM_CNO
     FROM BODYINTSAPFOS
/

CREATE OR REPLACE VIEW V_ITEMINTSAPFOS AS
   SELECT NUM_SEQ_HEADER, NUM_SEQ_CORPO, NUM_SEQ_ITEM, NUM_ITEM, TIP_DOCUMENTO,
          VLR_ITEM, NUM_CFOP, VLR_DESCONTO, VLR_FRETE, VLR_SEGURO, VLR_OUTRAS_DESP,
          COD_ITEM, QTD_ITEM, VLR_UNITARIO, NUM_NOTA_ORIGEM, COD_SERIE_ORIGEM, DES_ITEM,
          COD_CC, COD_NAT_R_PISCOF, COD_TP_CR_PISCOF, COD_NT_BC_PISCOF, COD_NAT_FRETE,
          COD_NBM, COD_ITEM_NL
     FROM ITEMINTSAPFOS
/

CREATE OR REPLACE VIEW V_IMPOSTOINTSAPFOS AS
   SELECT NUM_SEQ_HEADER, NUM_SEQ_CORPO, NUM_SEQ_ITEM, NUM_SEQ_IMPOSTO, TIP_IMPOSTO,
          VLR_BASE, VLR_RED_BASE, VLR_OUTRAS, PER_IMPOSTO, VLR_IMPOSTO, TIP_TRIBUTACAO
     FROM IMPOSTOINTSAPFOS
/

-- ============================================================
--   TABLE: TPDOCINTSAPFOS 
-- ============================================================
CREATE TABLE TPDOCINTSAPFOS(
   CDCFOP VARCHAR2(10) NOT NULL,
   CDNCM VARCHAR2(10) NOT NULL,
   CDCST VARCHAR2(2) NOT NULL,
   CDTPDOC VARCHAR2(03) NOT NULL,
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_TPDOCINTSAPFOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

ALTER TABLE TPDOCINTSAPFOS ADD CONSTRAINT PK_TPDOCINTSAPFOS PRIMARY KEY (CDCFOP, CDNCM, CDCST)
/

-- ============================================================
--   TABLE: CONVERSCODEXFIL 
-- ============================================================
CREATE TABLE CONVERSCODEXFIL(
   CDCODEXTINI VARCHAR2(05) NOT NULL,
   CDCODEXTFIN VARCHAR2(05) NOT NULL,
   CDLETRACORR VARCHAR2(01) NOT NULL,
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_CONVERSCODEXFIL CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
   )
/

ALTER TABLE CONVERSCODEXFIL ADD CONSTRAINT UN_CONVERSCODEXFIL UNIQUE (CDLETRACORR)
/

-- ============================================================
--   TABLE: EMPRESA 
-- ============================================================
ALTER TABLE EMPRESA ADD CDEXTERNO VARCHAR2(20)
/

-- ============================================================
--   TABLE: RELVENDAS 
-- ============================================================
CREATE TABLE RELVENDAS (
   CDFILIAL       VARCHAR2(4)  NOT NULL,
   CDCAIXA        VARCHAR2(3)  NOT NULL,
   DTENTRVENDA    DATE         NOT NULL,
   QTVENDASMSDE   FLOAT        NULL,
   QTVENDASORACLE FLOAT        NULL,
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_RELVENDAS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1',
   	CONSTRAINT PK_RELVENDAS PRIMARY KEY (CDFILIAL, CDCAIXA, DTENTRVENDA)
)
/

ALTER TABLE RELVENDAS
   ADD CONSTRAINT FKVND00672CAIXA FOREIGN KEY (CDFILIAL, CDCAIXA)
      REFERENCES CAIXA (CDFILIAL, CDCAIXA)
/

ALTER TABLE RELVENDAS ADD VRTOTMSDE NUMBER
/

ALTER TABLE RELVENDAS ADD VRTOTORACLE NUMBER
/

ALTER TABLE PARAVEND ADD IDBLOQMAISABERT VARCHAR2(01) DEFAULT 'N' NOT NULL
/

ALTER TABLE PARAVEND ADD CONSTRAINT CKC_PARAVEND_IDBLOQMAISABERT CHECK (IDBLOQMAISABERT IN ('S', 'N'))
/

-- ============================================================
--   TABLE: PERIODORNF 
-- ============================================================
CREATE TABLE PERIODORNF(
   CDEMPRESA VARCHAR2(02) NOT NULL,
   DTINICIAL DATE NOT NULL,
   DTFINAL DATE NOT NULL,
   NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_PERIODORNF CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

ALTER TABLE PERIODORNF ADD CONSTRAINT PK_PERIODORNF PRIMARY KEY (CDEMPRESA)
/

-- ============================================================
--   TABLE: VALIDACAORNF 
-- ============================================================
CREATE TABLE VALIDACAORNF
(
    NRVALIDACAORNF NUMBER,
    DSVALIDACAO VARCHAR2(300),
	NRORG                       NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_VALIDACAORNF CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1'
)
/

ALTER TABLE VALIDACAORNF ADD CONSTRAINT PK_VALIDACAORNF PRIMARY KEY (NRVALIDACAORNF)
/

-- ============================================================
--   TABLE: VENDA 
-- ============================================================
ALTER TABLE VENDA ADD NRHEADERINTSAP VARCHAR2(20)
/

ALTER TABLE VENDA ADD NRBODYINTSAP NUMBER
/

CREATE INDEX VENDA_33 ON VENDA (CDFILIAL, CDCAIXA, DTABERTUR)
/

CREATE INDEX TURCAIXA_01 ON TURCAIXA (CDFILIAL, CDCAIXA, DTABERCAIX)
/

CREATE INDEX TURCAIXA_03 ON TURCAIXA (CDFILIAL, CDCAIXA, DTABERCAIX, CDOPERADOR)