/* Tabela: USR_SAP_PRODUTO */

ALTER TABLE USR_SAP_PRODUTO
ADD IDPRODATIVPR VARCHAR2(1)
/

/* Tabela: USR_SAP_FORNECEDOR */
ALTER TABLE USR_SAP_FORNECEDOR ADD IDTPIJURFORN VARCHAR2(1) NULL
/

UPDATE USR_SAP_FORNECEDOR
   SET IDTPIJURFORN = NVL(REGEXP_REPLACE(CDFORNECEDOR, '\d'),'O')
/

COMMIT
/

ALTER TABLE USR_SAP_FORNECEDOR
   ADD CONSTRAINT CKC_IDTPIJURFORN_SAP CHECK (IDTPIJURFORN IN ('F','J','I','O'))
/

ALTER TABLE USR_SAP_FORNECEDOR
MODIFY IDTPIJURFORN NOT NULL
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRINSJURFORN VARCHAR2(20) NULL
/

UPDATE USR_SAP_FORNECEDOR
   SET NRINSJURFORN = REGEXP_REPLACE(CDFORNECEDOR, '\D')
/

COMMIT
/

ALTER TABLE USR_SAP_FORNECEDOR
   MODIFY NRINSJURFORN NOT NULL
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRINSESTFORN VARCHAR2(20) NULL
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDTIPOFORN VARCHAR2(1)
   CONSTRAINT CKC_IDTIPOFORN_SAP CHECK (IDTIPOFORN IN ('N','A'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDFORMPGFORN VARCHAR2(1) DEFAULT 'T' NULL
   CONSTRAINT CKC_IDFORMPGFORN_SAP CHECK (IDFORMPGFORN IN ('E','T','G','X','S','D','R','Q','M'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD CDCSTDIG1ENT VARCHAR2(1)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDSITUCADA VARCHAR2(1)
   CONSTRAINT CKC_IDSITUCADA_SAP CHECK (IDSITUCADA IN ('A', 'I'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRNOTAFORN NUMBER(3)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD CDQUALIF VARCHAR2(2)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD DTVALQUALIF DATE
/

ALTER TABLE USR_SAP_FORNECEDOR ADD CDFORMPGTO VARCHAR2(2)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDTPFORPGTFO VARCHAR2(1)
   CONSTRAINT CKC_IDTPFORPGTFO_SAP CHECK (IDTPFORPGTFO IN ('F','V'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRPARCPGTOFO NUMBER(2)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRDIAPGFIXFO NUMBER(2)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDUTDIAPGTFO VARCHAR2(1)
   CONSTRAINT CKC_IDUTDIAPGTFO_SAP CHECK (IDUTDIAPGTFO IN ('I','E'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDGERAFDTENT VARCHAR2(1)
   CONSTRAINT CKC_IDGERAFDTENT_SAP CHECK (IDGERAFDTENT IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDFORNAUTO VARCHAR2(1)
   CONSTRAINT CKC_IDFORNAUTO_SAP CHECK (IDFORNAUTO IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDENDEMISFOR VARCHAR2(1)
   CONSTRAINT CKC_IDENDEMISFOR_SAP CHECK (IDENDEMISFOR IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDENDCFOPFOR VARCHAR2(1)
   CONSTRAINT CKC_IDENDCFOPFOR_SAP CHECK (IDENDCFOPFOR IN ('P','C','E','X','I'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD DTULTATUFORN DATE
/

ALTER TABLE USR_SAP_FORNECEDOR ADD CDTIPOFORN VARCHAR2(3)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDPRIPUBFORN VARCHAR2(1) DEFAULT 'V' 
   CONSTRAINT CKC_IDPRIPUBFORN_SAP CHECK (IDPRIPUBFORN IN ('V','B'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDAGRUBOLFOR VARCHAR2(1) DEFAULT 'N'
   CONSTRAINT CKC_IDAGRUBOLFOR_SAP CHECK (IDAGRUBOLFOR IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDLIGEXTFORN VARCHAR2(1) DEFAULT 'N'
   CONSTRAINT CKC_IDLIGEXTFORN_SAP CHECK (IDLIGEXTFORN IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD VRPERPIFORN FLOAT
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDFORNINTER VARCHAR2(1)
   CONSTRAINT CKC_IDFORNINTER_SAP CHECK (IDFORNINTER IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDTPENDEFORN VARCHAR2(1)
   CONSTRAINT CKC_IDTPENDEFORN_SAP CHECK (IDTPENDEFORN IN ('P','C','E','X'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRTELEFORN VARCHAR2(25)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRRAMALFORN VARCHAR2(4)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRFAXFORN VARCHAR2(25)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NMCONTFORN VARCHAR2(30)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD DTULTATUENFO DATE
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NMCOMPRZSFOR VARCHAR2(200)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD CDEXFORNECED VARCHAR2(30)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD DSCOMPENDFOR VARCHAR2(200)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD IDUTLALVFORN VARCHAR2(1) DEFAULT 'N'
   CONSTRAINT CKC_IDUTLALVFORN_SAP CHECK (IDUTLALVFORN IN ('S','N'))
/

ALTER TABLE USR_SAP_FORNECEDOR ADD NRALVARAFORN VARCHAR2(100)
/

ALTER TABLE USR_SAP_FORNECEDOR ADD DTALVARAFORN DATE
/

/* Tabela: USR_SAP_CLIENTE */

ALTER TABLE USR_SAP_CLIENTE ADD IDTPIJURCLIE VARCHAR2(1) NULL
/

UPDATE USR_SAP_CLIENTE
   SET IDTPIJURCLIE = NVL(REGEXP_REPLACE(CDCLIENTE, '\d'),'O')
/

COMMIT
/

ALTER TABLE USR_SAP_CLIENTE
   ADD CONSTRAINT CKC_IDTPIJURCLIE_SAP CHECK (IDTPIJURCLIE IN ('F','J','I','O'))
/

ALTER TABLE USR_SAP_CLIENTE
MODIFY IDTPIJURCLIE NOT NULL
/

ALTER TABLE USR_SAP_CLIENTE ADD NRINSJURCLIE VARCHAR2(20) NULL
/

UPDATE USR_SAP_CLIENTE
   SET NRINSJURCLIE = REGEXP_REPLACE(CDCLIENTE, '\D')
/

COMMIT
/

ALTER TABLE USR_SAP_CLIENTE
   MODIFY NRINSJURCLIE NOT NULL
/

ALTER TABLE USR_SAP_CLIENTE ADD NRINSESTCLIE VARCHAR2(20) NULL
/

ALTER TABLE USR_SAP_CLIENTE ADD CDFORMPGTO VARCHAR2(2)
/

ALTER TABLE USR_SAP_CLIENTE ADD IDTPFORPGTCL VARCHAR2(1)
   CONSTRAINT CKC_IDTPFORPGTCL_SAP CHECK (IDTPFORPGTCL IN ('F','V'))
/

ALTER TABLE USR_SAP_CLIENTE ADD NRPARCPGTOCL NUMBER
/

ALTER TABLE USR_SAP_CLIENTE ADD NRDIAPGFIXCL NUMBER
/

ALTER TABLE USR_SAP_CLIENTE ADD IDUTDIAPGTCL VARCHAR2(1)
   CONSTRAINT CKC_IDUTDIAPGTCL_SAP CHECK (IDUTDIAPGTCL IN ('I','E'))
/

ALTER TABLE USR_SAP_CLIENTE ADD CDCSTDIG1CLI VARCHAR2(1)
/

ALTER TABLE USR_SAP_CLIENTE ADD IDENDEMISCLI VARCHAR2(1)
   CONSTRAINT CKC_IDENDEMISCLI_SAP CHECK (IDENDEMISCLI IN ('P','C','E','X','T'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDENDCFOPCLI VARCHAR2(1)
   CONSTRAINT CKC_IDENDCFOPCLI_SAP CHECK (IDENDCFOPCLI IN ('P','C','E','X','T'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDTIPINFCOME VARCHAR2(1)
   CONSTRAINT CKC_IDTIPINFCOME_SAP CHECK (IDTIPINFCOME IN ('S','V','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD DTCADACLIE DATE
/

ALTER TABLE USR_SAP_CLIENTE ADD IDCLIECONTRI VARCHAR2(1)
   CONSTRAINT CKC_IDCLIECONTRI_SAP CHECK (IDCLIECONTRI IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDCLIEATIV VARCHAR2(1)
   CONSTRAINT CKC_IDCLIEATIV_SAP CHECK (IDCLIEATIV IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDSEGCLIP VARCHAR2(1)
   CONSTRAINT CKC_IDSEGCLIP_SAP CHECK (IDSEGCLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDTERCLIP VARCHAR2(1)
   CONSTRAINT CKC_IDTERCLIP_SAP CHECK (IDTERCLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDQUACLIP VARCHAR2(1)
   CONSTRAINT CKC_IDQUACLIP_SAP CHECK (IDQUACLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDQUICLIP VARCHAR2(1)
   CONSTRAINT CKC_IDQUICLIP_SAP CHECK (IDQUICLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDSEXCLIP VARCHAR2(1)
   CONSTRAINT CKC_IDSEXCLIP_SAP CHECK (IDSEXCLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDSABCLIP VARCHAR2(1)
   CONSTRAINT CKC_IDSABCLIP_SAP CHECK (IDSABCLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDDOMCLIP VARCHAR2(1)
   CONSTRAINT CKC_IDDOMCLIP_SAP CHECK (IDDOMCLIP IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDRECINFMKT VARCHAR2(1)
   CONSTRAINT CKC_IDRECINFMKT_SAP CHECK (IDRECINFMKT IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDPRIPUBCLIE VARCHAR2(1) DEFAULT 'V' NULL
   CONSTRAINT CKC_IDPRIPUBCLIE_SAP CHECK (IDPRIPUBCLIE IN ('V','B'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDVERSALDCLI VARCHAR2(1) DEFAULT 'N' NULL
   CONSTRAINT CKC_IDVERSALDCLI_SAP CHECK (IDVERSALDCLI IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDUTSALDCLI VARCHAR2(1) DEFAULT 'N' NULL
   CONSTRAINT CKC_IDUTSALDCLI_SAP CHECK (IDUTSALDCLI IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDCALCDVCLIE VARCHAR2(1) DEFAULT '1' NULL
   CONSTRAINT CKC_IDCALCDVCLIE_SAP CHECK (IDCALCDVCLIE IN ('1','2','3'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDUTSUBCLIEN VARCHAR2(1) DEFAULT 'N' NULL
   CONSTRAINT CKC_IDUTSUBCLIEN_SAP CHECK (IDUTSUBCLIEN IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDLIBGERPER VARCHAR2(1)
   CONSTRAINT CKC_IDLIBGERPER_SAP CHECK (IDLIBGERPER IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDZERSALDCON VARCHAR2(1) DEFAULT 'S' NULL
   CONSTRAINT CKC_IDZERSALDCON_SAP CHECK (IDZERSALDCON IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDGERNFCLIE VARCHAR2(1) DEFAULT '1' NULL
   CONSTRAINT CKC_IDGERNFCLIE_SAP CHECK (IDGERNFCLIE IN ('1','2'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDSITFINCLIE VARCHAR2(1) DEFAULT 'N' NULL
   CONSTRAINT CKC_IDSITFINCLIE_SAP CHECK (IDSITFINCLIE IN ('P','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDLIGEXTCLIE VARCHAR2(1) DEFAULT 'N' NULL
   CONSTRAINT CKC_IDLIGEXTCLIE_SAP CHECK (IDLIGEXTCLIE IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDCLIEAUTO VARCHAR2(1)
   CONSTRAINT CKC_IDCLIEAUTO_SAP CHECK (IDCLIEAUTO IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDGERANFPEDI VARCHAR2(1)
   CONSTRAINT CKC_IDGERANFPEDI_SAP CHECK (IDGERANFPEDI IN ('1','2'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDUTCONSPEDI VARCHAR2(1)
   CONSTRAINT CKC_IDUTCONSPEDI_SAP CHECK (IDUTCONSPEDI IN ('S','N'))
/

ALTER TABLE USR_SAP_CLIENTE ADD IDTPENDECLIE VARCHAR2(1)
   CONSTRAINT CKC_IDTPENDECLIE_SAP CHECK (IDTPENDECLIE IN ('P','C','E','X','T','O','M'))
/

ALTER TABLE USR_SAP_CLIENTE ADD DSCOMPENDCLI VARCHAR2(200)
/

ALTER TABLE USR_SAP_CLIENTE ADD NRTELECLIE VARCHAR2(25)
/

ALTER TABLE USR_SAP_CLIENTE ADD NRRAMALCLIE VARCHAR2(4)
/

ALTER TABLE USR_SAP_CLIENTE ADD NRFAXCLIE VARCHAR2(25)
/

ALTER TABLE USR_SAP_CLIENTE ADD NMCOMPRZSCLI VARCHAR2(200)
/

ALTER TABLE USR_SAP_CLIENTE ADD CDEXCLIENTE VARCHAR2(30)
/

/* Tabela: USR_SAP_CLIENFILIAL */

CREATE TABLE USR_SAP_CLIENFILIAL
(
   CDCLIENTE       VARCHAR2(21) NOT NULL,
   CDFILIAL        VARCHAR2(4)  NOT NULL,
   IDCLIENPRIN     VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_IDCLIENPRIN_SAP CHECK (IDCLIENPRIN IN ('S','N')),
   IDUTILADIANT    VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_IDUTILADIANT_SAP CHECK (IDUTILADIANT IN ('S','N')),
   VRPERATDESP     FLOAT,
   IDUTCONTCLPR    VARCHAR2(1)
      CONSTRAINT CKC_IDUTCONTCLPR_SAP CHECK (IDUTCONTCLPR IN ('S','N')),
   IDPERIODFATC    VARCHAR2(1)
      CONSTRAINT CKC_IDPERIODFATC_SAP CHECK (IDPERIODFATC IN ('1','2','3','4')),
   IDCOREFECLIFILI VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_IDCOREFECLIFILI_SAP CHECK (IDCOREFECLIFILI IN ('U','Q','I')),
   IDCONSMINEFECLI VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_IDCONSMINEFECLI_SAP CHECK (IDCONSMINEFECLI IN ('D','P','I')),   
   DTVENCONTRCLIE  DATE,
   NRDIAAVISOCVEN  FLOAT,
   CDEXCLIENFIL    VARCHAR2(30),
   IDPERMVENDAFOS  VARCHAR2(1)  NOT NULL
      CONSTRAINT CKC_IDPERMVENDAFOS_SAP CHECK (IDPERMVENDAFOS IN ('S','N')),   
   DESPERFAT       VARCHAR2(50),
   IDINTERFACE     NUMBER       NOT NULL,
   TPINTERFACE     VARCHAR2(01) NOT NULL
      CONSTRAINT CKC_TPINTERFACE_USR_SAP_CLFIL CHECK (TPINTERFACE IN ('I','A','E')),
   DTINTERFACEORI  DATE         NOT NULL,
   DTINTERFACEDEST DATE,
   STATUS          NUMBER       NOT NULL
      CONSTRAINT CKC_STATUS_USR_SAP_CLFIL CHECK (STATUS IN (0,1,2)),
   APPORIGEM       NUMBER       NOT NULL,
      CONSTRAINT CKC_APPORIGEM_USR_SAP_CLFIL CHECK (APPORIGEM IN (1,2)),
   APPDESTINO      NUMBER       NOT NULL
      CONSTRAINT CKC_APPDESTINO_USR_SAP_CLFIL CHECK (APPDESTINO IN (1,2))
)
/

DROP SEQUENCE USR_PRODUTO_IDINTERFACE
/

DROP SEQUENCE USR_NOTAFIS_IDINTERFACE
/

DROP SEQUENCE USR_ITPROD_IDINTERFACE
/

DROP SEQUENCE USR_ITPROIMPOS_IDINTERFACE
/

DROP SEQUENCE USR_ITENC_IDINTERFACE
/

DROP SEQUENCE USR_VENC_IDINTERFACE
/

DROP SEQUENCE USR_EXPORTATI_IDINTERFACE
/

DROP SEQUENCE USR_EXPORTATM_IDINTERFACE
/

DROP SEQUENCE USR_EXPORTATC_IDINTERFACE
/

DROP SEQUENCE USR_APURFATUR_IDINTERFACE
/

DROP SEQUENCE USR_ITAPURFATUR_IDINTERFACE
/

CREATE SEQUENCE USR_SAP_IDINTERFACE INCREMENT BY 1 MINVALUE 1
/

ALTER TABLE USR_SAP_NOTAFISCAL ADD DTPROC DATE NULL
/

ALTER TABLE USR_SAP_NOTAFISCAL ADD DTCONTABILTG DATE
/

ALTER TABLE USR_SAP_NOTAFISCAL ADD NRNUMRETG VARCHAR2(10)
/

ALTER TABLE USR_SAP_NFAPROVADA ADD IDSTATUSNFE VARCHAR2(1) NOT NULL
   CONSTRAINT CKC_IDSTATUSNFE_SAP CHECK (IDSTATUSNFE IN ('T','A','D','C','E'))
/

ALTER TABLE USR_SAP_APURFATUR ADD NRNUMRETG VARCHAR2(10)
/

ALTER TABLE USR_SAP_LOG
DROP CONSTRAINT CKC_IDPROCESSO_USR_SAP_LOG
/

ALTER TABLE USR_SAP_LOG
ADD CONSTRAINT CKC_IDPROCESSO_USR_SAP_LOG  CHECK (IDPROCESSO BETWEEN 1 AND 15)
/

ALTER TABLE USR_SAP_NOTAFISCAL
ADD CDFILIALTG VARCHAR2(4)
/

ALTER TABLE USR_SAP_APURFATUR
ADD IDAVISO VARCHAR2(1) DEFAULT 'N'
/

ALTER TABLE USR_SAP_APURFATUR
ADD CONSTRAINT CKC_IDAVISO_USR_APURFATUR CHECK (IDAVISO IN('S', 'N'))
/

ALTER TABLE USR_SAP_APURFATUR
ADD CRCREDITO VARCHAR2(14) -- Campo vai gravar o valor do credito e nao o cd unico
/

ALTER TABLE USR_SAP_APURFATUR
ADD DSOBSERVCRED VARCHAR2(100)
/

ALTER TABLE PARAMFILIAL
ADD IDAVISOCREDSAP VARCHAR2(1) DEFAULT 'N'
/

ALTER TABLE PARAMFILIAL
ADD CONSTRAINT CKC_IDAVISOCREDSAP CHECK (IDAVISOCREDSAP IN('S', 'N'))
/

ALTER TABLE USR_SAP_APURFATUR
ADD CDUNICOAVISO VARCHAR2(14)
/

ALTER TABLE USR_SAP_APURFATUR
MODIFY CRCREDITO FLOAT -- Campo vai gravar o valor do credito e nao o cd unico
/

ALTER TABLE USR_SAP_FILIAL
ADD NMRAZSOCFILI VARCHAR2(40)
/

ALTER TABLE USR_SAP_ITEMPROIMPOS ADD VRADICALIQFECP FLOAT NULL
/
ALTER TABLE USR_SAP_ITEMPROIMPOS ADD VRADICFECP FLOAT NULL
/

ALTER TABLE USR_SAP_NOTAFISCAL ADD CDIBGEORIG  VARCHAR2(10)     NULL
/

ALTER TABLE USR_SAP_NOTAFISCAL ADD CDIBGEDEST  VARCHAR2(10)     NULL
/

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