CREATE TABLE USR_EAM_RESPONSAVEL
(
   NRINSJURCLIE VARCHAR2(20) NOT NULL,
   NMRAZSOCCLIE VARCHAR2(50) NOT NULL,
   CDPAIS       VARCHAR2(4)  NOT NULL,
   SGESTADO     VARCHAR2(2)  NOT NULL,
   CDMUNICIBGE  VARCHAR2(7)  NOT NULL,
   DSENDECLIE   VARCHAR2(50) NOT NULL,
   NRCEPCLIE    VARCHAR2(15) NOT NULL,
   DTIMPORTACAO DATE,
   USR_CODINTERNO VARCHAR2(20),
   IDSTATUS     VARCHAR2(1),
   ERRO         VARCHAR2(500)
)
/

CREATE TABLE USR_EAM_NOTAFISCAL
(
   CODTRANSACAO   VARCHAR2(44) NOT NULL,
   FILIAL         VARCHAR2(20) NOT NULL,
   NRINSJURNF     VARCHAR2(20) NOT NULL,
   DTVENCIMENTO   DATE         NOT NULL,
   CDSERVICO      VARCHAR2(10) NOT NULL,
   VRSERVICO      FLOAT        NOT NULL,
   USR_CODINTERNO VARCHAR2(20),
   DTIMPORTACAO   DATE,
   IDSTATUS       VARCHAR2(1),
   ERRO           VARCHAR2(500),
	  CODBOLETO  	  	VARCHAR2(20) NOT NULL
)
/

CREATE TABLE USR_EAM_ESTORNO
(
	CODTRANSACAO 	VARCHAR2(44) NOT NULL,
  DTESTORNO   	DATE,
	CODBOLETO  		VARCHAR2(20) NOT NULL,
	ERRO          VARCHAR2(500),
	DTIMPORTACAO  DATE,
	CONSTRAINT PK_USR_EAM_ESTORNO PRIMARY KEY (CODTRANSACAO,CODBOLETO)
)
/
CREATE SEQUENCE CODTRANSACAO_ESTORNO
INCREMENT BY 1 START WITH 1
/
CREATE TABLE USR_EAM_RECEBIMENTO
(
   CODTRANSACAO VARCHAR2(44) NOT NULL,
	CODBOLETO    VARCHAR2(20) NOT NULL,
   BANCO        VARCHAR2(3),
   AGENCIA      VARCHAR2(5),
   CONTCORR     VARCHAR2(20),
   CDTPBAIXAREC VARCHAR2(2),
   IDTIPORECE   INTEGER,
   DATABAIXA    DATE,
   VRBAIXA      FLOAT,
   OBSERVACAO   VARCHAR2(250),
   CODAJUSTE    VARCHAR2(4),
   DTIMPORTACAO DATE,
   DATAVENC     DATE,
   DATAPAGTO    DATE,
   ERRO         VARCHAR2(500),
	CONSTRAINT PL_USR_EAM_RECEBIMENTO PRIMARY KEY (CODTRANSACAO,CODBOLETO)
)
/
CREATE SEQUENCE CODTRANSACAO_RECEBIMENTO
INCREMENT BY 1 START WITH 1
/

CREATE TABLE USR_EAM_NFBAIXAREC
(
   CODTRANSACAO VARCHAR2(44),
   CDFILIAL     VARCHAR2(4),
   NRLANCTONF   VARCHAR2(10),
   NRLANCTOREC  VARCHAR2(10),
   DTBAIXA      DATE,
   CONSTRAINT PK_USR_EAM_NFBAIXAREC PRIMARY KEY (CODTRANSACAO)
)
/

ALTER TABLE USR_EAM_NFBAIXAREC
ADD CONSTRAINT FK_NOTAFISCAL FOREIGN KEY (CDFILIAL, NRLANCTONF)
REFERENCES NOTAFISCAL (CDFILIAL, NRLANCTONF) ON DELETE CASCADE
/

ALTER TABLE USR_EAM_RECEBIMENTO
ADD CONSTRAINT CKC_IDTIPORECE_EAM_RECEB CHECK(IDTIPORECE BETWEEN 1 AND 7)
/

CREATE TABLE USR_EAM_AJUSTE
(
   CODTRANSACAO VARCHAR2(44),
   CODBOLETO VARCHAR2(20),
   VRAJUSTE FLOAT,
  	CODAJUSTE VARCHAR2(4),
  	IDACRESDESC VARCHAR2(1),
  	ERRO VARCHAR2(500),
  	DTIMPORTACAO DATE,
	CONSTRAINT PK_USR_EAM_AJUSTE PRIMARY KEY (CODTRANSACAO,CODBOLETO)
)
/
CREATE SEQUENCE CODTRANSACAO_AJUSTE
INCREMENT BY 1 START WITH 1
/
ALTER TABLE USR_EAM_AJUSTE
ADD CONSTRAINT CKC_IDACRESDESC CHECK(IDACRESDESC in ('A','D'))
/

CREATE TABLE USR_EAM_FUNCIONARIO
(
   NMPESSOA         VARCHAR2(50) NOT NULL,
   DTNASCPESSOA     VARCHAR2(10),
   DSENDERECO       VARCHAR2(150),
   NMBAIRROENDERECO VARCHAR2(100),
   NRCEPENDERECO    VARCHAR2(15),
   NRCPFPESSOA      VARCHAR2(20) NOT NULL,
   NRRGPESSOA       VARCHAR2(20),
   IDSEXOPESSOA     VARCHAR2(1),
   DTEXPORTACAO     DATE
)
/
ALTER TABLE CLIENTE
ADD USR_CODINTERNO VARCHAR2(20)
/
ALTER TABLE BOLETRECEBER ADD USR_CODBOLETO VARCHAR2(20) NULL
/
ALTER TABLE USR_EAM_RESPONSAVEL ADD USR_DTIMP DATE DEFAULT SYSDATE
/
ALTER TABLE USR_EAM_NOTAFISCAL ADD USR_DTIMP DATE DEFAULT SYSDATE
/
ALTER TABLE USR_EAM_ESTORNO ADD USR_DTIMP DATE DEFAULT SYSDATE
/
ALTER TABLE USR_EAM_RECEBIMENTO ADD USR_DTIMP DATE DEFAULT SYSDATE
/
ALTER TABLE USR_EAM_NFBAIXAREC ADD USR_DTIMP DATE DEFAULT SYSDATE
/
ALTER TABLE USR_EAM_AJUSTE ADD USR_DTIMP DATE DEFAULT SYSDATE
/
ALTER TABLE USR_EAM_FUNCIONARIO ADD USR_DTIMP DATE DEFAULT SYSDATE
/
CREATE INDEX USR_EAM_RESPONSAVEL_01 ON USR_EAM_RESPONSAVEL(NRINSJURCLIE, USR_DTIMP ASC)
/
CREATE INDEX USR_EAM_NOTAFISCAL_01 ON USR_EAM_NOTAFISCAL (CODTRANSACAO, USR_DTIMP ASC)
/
CREATE INDEX USR_EAM_RECEBIMENTO_01 ON USR_EAM_RECEBIMENTO (CODTRANSACAO, USR_DTIMP ASC)
/
CREATE INDEX USR_EAM_AJUSTE_01 ON USR_EAM_AJUSTE (CODTRANSACAO, USR_DTIMP ASC)
/
CREATE INDEX USR_EAM_ESTORNO_01 ON USR_EAM_ESTORNO (CODTRANSACAO, USR_DTIMP ASC)
/

ALTER TABLE NOTAFISCAL ADD USR_CODTRANSACAO VARCHAR2(44) NULL
/

-- VERSAO --
BEGIN ATUVEROBJ('ATUALIZACAO_ESTRUTURA_BD','6.25.XXX'); END;
/
