DROP TABLE USR_SAP_FORNECEDOR;
DROP TABLE USR_SAP_PRODUTO;
DROP TABLE USR_SAP_PRODUTO_SITE;
DROP TABLE USR_SAP_ALT_LISTPRECO;
DROP TABLE USR_SAP_LOG;
DROP TABLE USR_SAP_LISTPRECO;
DROP TABLE USR_SAP_AUTOFORNE;
DROP TABLE USR_SAP_ALT_AUTOFORNE;
DROP TABLE USR_SAP_INVENTESTOQ;
DROP TABLE USR_SAP_PRODFILI;

CREATE TABLE USR_SAP_FORNECEDOR
(
   CDFORNECEDOR VARCHAR2(20) NOT NULL,
   NMRAZAOSOC   VARCHAR2(40) NOT NULL,
   NMFANTFORNE  VARCHAR2(40) NOT NULL,
   DSENDERECO   VARCHAR2(40),
   CDPAIS       VARCHAR2(4)  NOT NULL,
   SGESTADO     VARCHAR2(2)  NOT NULL,
   CDMUNICIPIO  VARCHAR2(7)  NOT NULL,
   CDPROVEESAP  VARCHAR2(20) NOT NULL,
   IDINTERFACE  NUMBER       NOT NULL,
   TPINTERFACE  VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_FORN CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE         NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS          NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_FORN CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM       NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_SAP_FORN CHECK (APPORIGEM IN (1,2)),
   APPDESTINO      NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_FORN CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_PRODUTO
(
   CDPRODUTO   VARCHAR2(20) NOT NULL,
   CDPRODSAP   VARCHAR2(18) NOT NULL,
   NMPRODUTO   VARCHAR2(50) NOT NULL,
   SGUNIDADE   VARCHAR2(5)  NOT NULL,
   IDPRODESTOQ VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_IDPRODESTOQ_SAP CHECK (IDPRODESTOQ IN ('S','N')),
   VRFATOCORR  FLOAT        NOT NULL,
   VRFATOCONV  FLOAT        NOT NULL,
   CDMARCA     VARCHAR2(5)  NOT NULL,
   IDINTERFACE NUMBER       NOT NULL,
   TPINTERFACE VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_PROD CHECK (TPINTERFACE IN ('I','A','E')),
   DTINTERFACEORI  DATE     NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS      NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_PROD CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM   NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_SAP_PROD CHECK (APPORIGEM IN (1,2)),
   APPDESTINO  NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_PROD CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_PRODUTO_SITE
(
   CDPRODSAP   VARCHAR2(18) NOT NULL,
   CDSITESAP   VARCHAR2(4)  NOT NULL,
   SGUNIDADE   VARCHAR2(5)  NOT NULL,
   IDINTERFACE NUMBER       NOT NULL,
   TPINTERFACE VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_PRODF CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE     NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS      NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_PRODF CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM   NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_SAP_PRODF CHECK (APPORIGEM IN (1,2)),
   APPDESTINO  NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_PRODF CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_ALT_LISTPRECO
(
   CDCHAVE      VARCHAR2(53) NOT NULL,
   VRPRECUNIITM FLOAT        NOT NULL,
   IDINTERFACE  NUMBER       NOT NULL,
   TPINTERFACE  VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_LIST CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE     NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS      NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_LIST CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM   NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_SAP_LIST CHECK (APPORIGEM IN (1,2)),
   APPDESTINO  NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_LIST CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_LOG
(
	IDINTERFACE NUMBER        NOT NULL,
	IDSEQLOG    VARCHAR2(1)   NOT NULL
		CONSTRAINT CKC_IDSEQLOG_USR_SAP_LOG CHECK (IDSEQLOG IN ('I','A','E')),
	IDPROCESSO  NUMBER        NOT NULL
		CONSTRAINT CKC_IDPROCESSO_USR_SAP_LOG CHECK (IDPROCESSO BETWEEN 1 AND 11),
	DTLOG       DATE          NOT NULL,
	STATUS      NUMBER        NOT NULL
		CONSTRAINT CKC_STATUS_USR_SAP_LOG CHECK (STATUS IN (0,1)),
	OBSSTATUS   VARCHAR2(250) NOT NULL
)
/

CREATE TABLE USR_SAP_LISTPRECO
(
   CDCHAVE     VARCHAR2(53) NOT NULL,
   CDSITESAP   VARCHAR2(4)  NOT NULL,
   CDPRODSAP   VARCHAR2(18) NOT NULL,
   UOMSAP      VARCHAR2(5)  NOT NULL,
   CDPROVEESAP VARCHAR2(20) NOT NULL,
   NIF_PROVEE  VARCHAR2(20) NOT NULL,
   VRUNITARIO  FLOAT        NOT NULL,
   DTINIVIG    DATE         NOT NULL,
   DTFINVIG    DATE         NOT NULL,
   IDINTERFACE NUMBER       NOT NULL,
   TPINTERFACE VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_LISTP CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE     NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS      NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_LISTP CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM   NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_SAP_LISTP CHECK (APPORIGEM IN (1,2)),
   APPDESTINO  NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_LISTP CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_AUTOFORNE
(
   TIPO_DOCUM      VARCHAR2(4),
   ORDEN           VARCHAR2(10) NOT NULL,
   CDSITESAP       VARCHAR2(4)  NOT NULL,
   CDSITETECFOOD   VARCHAR2(4)  NOT NULL,
   POSICION        VARCHAR2(3),
   NIF_PROVEE      VARCHAR2(20) NOT NULL,
   CDPROVEESAP     VARCHAR2(20) NOT NULL,
   ORG_COMPRA      VARCHAR2(4)  NOT NULL,
   GRUPO_COMPRA    VARCHAR2(3),
   BUYER           VARCHAR2(30) NOT NULL,
   SOCIEDAD        VARCHAR2(4),
   MATERIAL        VARCHAR2(18) NOT NULL,
   UOM_ORDEN       VARCHAR2(5),
   CANTIDAD        FLOAT        NOT NULL,
   FEC_ENTREGA     DATE         NOT NULL,
   PRECIO_UNITARIO FLOAT        NOT NULL,
   MONEDA          VARCHAR2(3),
   STATUS_SAP      VARCHAR2(10),
   IDGERAORDCOMP   VARCHAR2(1),
   IDINTERFACE   NUMBER       NOT NULL,
   TPINTERFACE   VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_AUTF CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE       NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS_TECFOOD  NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_AUTF CHECK (STATUS_TECFOOD IN (0,1,2,3)),
   APPORIGEM     NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_SAP_AUTF CHECK (APPORIGEM IN (1,2)),
   APPDESTINO    NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_AUTF CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_ALT_AUTOFORNE
(
   CDSITESAP     VARCHAR2(4)  NOT NULL,
   CDSITETECFOOD VARCHAR2(4)  NOT NULL,
   ORDEN         VARCHAR2(10) NOT NULL,
   IDINTERFACE   NUMBER       NOT NULL,
   TPINTERFACE   VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_ALT_AUTF CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE       NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS        NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_ALT_AUTF CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM     NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_ALT_AUTF CHECK (APPORIGEM IN (1,2)),
   APPDESTINO    NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_ALT_AUTF CHECK (APPDESTINO IN (1,2))
)
/

CREATE TABLE USR_SAP_INVENTESTOQ
(
   CDPRODUTO   VARCHAR2(18) NOT NULL,
   SGUNIDADE   VARCHAR2(5)  NOT NULL,
   CDSITESAP   VARCHAR2(4)  NOT NULL,
   DTINVENT    DATE         NOT NULL,
   QUANTIDADE  FLOAT        NOT NULL,
   VRUNITARIO  FLOAT        NOT NULL,
   IDINTERFACE NUMBER       NOT NULL,
   TPINTERFACE VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_INVEST CHECK (TPINTERFACE IN ('I','A','E')),	
   DTINTERFACEORI  DATE     NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS      NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_INVEST CHECK (STATUS IN (0,1,2,3)),
   APPORIGEM   NUMBER       NOT NULL
      CONSTRAINT CKC_APPORIGEM_USR_INVEST CHECK (APPORIGEM IN (1,2)),
   APPDESTINO  NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_INVEST CHECK (APPDESTINO IN (1,2))
)
/

ALTER TABLE MARCPROD
ADD CDPRODSAP VARCHAR2(18)
/

ALTER TABLE COLETAPR
ADD USR_DTEXPORTA DATE
/

CREATE SEQUENCE USR_SAP_IDINTERFACE INCREMENT BY 1 MINVALUE 1
/


-- VERSAO --
BEGIN ATUVEROBJ('REGISTROS_INT_SAP','5.45.XXX'); END;
/