DROP TABLE TMP_REGDADOSSPED_PIS
/
DROP TABLE TMP_REGBLOCOM_PIS
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_PIS
(
   CDREGISTRO      VARCHAR2(04)     NOT NULL,
   CDCONTREGI      NUMBER           NOT NULL,
   CDCHAVEREG      VARCHAR2(50)     NOT NULL,
   CDESTAB         VARCHAR2(1000)   NULL,
   CDCAMPO1        VARCHAR2(1000)   NULL,
   CDCAMPO2        VARCHAR2(1000)   NULL,
   CDCAMPO3        VARCHAR2(1000)   NULL,
   CDCAMPO4        VARCHAR2(1000)   NULL,
   CDCAMPO5        VARCHAR2(1000)   NULL,
   CDCAMPO6        VARCHAR2(1000)   NULL,
   CDCAMPO7        VARCHAR2(1000)   NULL,
   CDCAMPO8        VARCHAR2(1000)   NULL,
   CDCAMPO9        VARCHAR2(1000)   NULL,
   CDCAMPO10       VARCHAR2(1000)   NULL,
   NRINSJUREST     VARCHAR2(50)     NULL,
   IDCUMUPISCOFIL  VARCHAR2(1)      NULL,
   NRORG           NUMBER 	         DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO         VARCHAR2(1)      DEFAULT 'S' NOT NULL
		CONSTRAINT CKC_IDATIVO_TMP_REGDADOSSPED 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_TMP_REGDADOSSPED_PIS PRIMARY KEY(CDREGISTRO, CDCONTREGI, CDCHAVEREG)) ON COMMIT PRESERVE ROWS
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGBLOCOM_PIS
(
   CDREGISTRO      VARCHAR2(04)   NOT NULL,
   CDCONTREGI      NUMBER         NOT NULL,
   CDTPCRED        VARCHAR2(04)   NULL,
   VRBCCRED        FLOAT          NULL,
   VRALIQCRED      FLOAT          NULL,
   IDTPALIQ        VARCHAR2(1)    NULL,
   VRCRED          FLOAT          NULL,
   CSTPISCOFINS    VARCHAR2(2)    NULL,
   CDFILIAL        VARCHAR2(4)    NULL,
   CDNATUBCALPCIT  VARCHAR2(2)    NULL,
   VRIMPRETIDO     FLOAT          NULL,
   VRTOTNF         FLOAT          NULL,
   CDNATUREC       VARCHAR(5)     NULL,
   CDESTAB         VARCHAR2(1000) NULL,
   NRINSJUREST     VARCHAR2(50)   NULL,
   CDCONTCTBL      VARCHAR2(20)   NULL,
   IDAJUSTE        VARCHAR2(1)    NULL,
   CDAJUSTE        VARCHAR2(2)    NULL,
   VRAJUSTE        FLOAT          NULL,
   CHAVENOTA       VARCHAR2(25)   NULL,
   IDCUMUPISCOFIL  VARCHAR2(1)    NULL,
   NRORG             NUMBER 	    DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO           VARCHAR2(1)  DEFAULT 'S' NOT NULL
		CONSTRAINT CKC_IDATIVO_TMP_REGBLOCOM_PIS 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_TMP_REGBLOCOM_PIS PRIMARY KEY(CDREGISTRO, CDCONTREGI)) ON COMMIT PRESERVE ROWS
/

DROP TABLE TMP_REGDADOSSPED_PIS_AUX
/

DROP TABLE TMP_REGBLOCOM_PIS_AUX
/

CREATE TABLE TMP_REGDADOSSPED_PIS_AUX AS (SELECT * FROM TMP_REGDADOSSPED_PIS)
/

CREATE TABLE TMP_REGBLOCOM_PIS_AUX AS (SELECT * FROM TMP_REGBLOCOM_PIS)
/

DROP TABLE TMP_REGDADOSSPED_NF_AUX
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_NF_AUX
(
	IDENTRSAIDOP 	VARCHAR2(1 BYTE),
	CDTIPOOPER 		VARCHAR2(2 BYTE),
	CDFILIAL 		VARCHAR2(4 BYTE)
) ON COMMIT DELETE ROWS
/

DROP TABLE TMP_REGDADOSSPED_NF
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_NF
(
	IDENTRSAIDOP	VARCHAR2(1 BYTE),
	CDTIPOOPER		VARCHAR2(2 BYTE),
	CDFILIAL	    	VARCHAR2(4 BYTE),
	CONTCTBL	    	VARCHAR2(20 BYTE)
) ON COMMIT DELETE ROWS
/

DROP TABLE TMP_REGDADOSSPED_ECF_NF
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_ECF_NF AS
   SELECT NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO, NF.CDEMPRESA, NF.CDINSCESTA, NF.IDSISTEMA, NF.IDENTRSAIDOP, NF.CDTIPOOPER
	  FROM NOTAFISCAL NF
	 WHERE 1=0
/

DROP TABLE TMP_REGDADOSSPED_ECF_RE
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_ECF_RE AS
	SELECT RE.CDFILIALVENDA, RE.CDCAIXA, RE.NRSEQVENDA, RE.CDFILIALNOTA, RE.NRLANCTONF
	  FROM RELACNFCEAPROV RE
	 WHERE 1=0
/