CREATE TABLE LEIAUTEREFDIRF(
   CDANOREF  VARCHAR2(04)  NOT NULL,
   IDESTRUTURA VARCHAR2(10)  NOT NULL,
   NRORG             NUMBER 	     DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 		NOT NULL,
   IDATIVO           VARCHAR2(1)     DEFAULT 'S' 					NOT NULL
      CONSTRAINT CKC_IDATIVO_LEIAUTEREFDIRF 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_LEIAUTEREFDIRF PRIMARY KEY (CDANOREF)
)
/

INSERT INTO LEIAUTEREFDIRF
   (CDANOREF, IDESTRUTURA)
   (SELECT '2021', 'VR4QLM8' 
      FROM DUAL
     WHERE NOT EXISTS (SELECT * FROM LEIAUTEREFDIRF
                        WHERE CDANOREF ='2021'))
/

COMMIT
/

CREATE TABLE RELACCODRECIMP(
   CDIMPOSTO VARCHAR2(02) NOT NULL, 
   CDRECEITA VARCHAR2(04) NOT NULL,
   NRORG             NUMBER 	     DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 		NOT NULL,
   IDATIVO           VARCHAR2(1)     DEFAULT 'S' 					NOT NULL
		CONSTRAINT CKC_IDATIVO_RELACCODRECIMP 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_RELACCODRECIMP PRIMARY KEY (CDIMPOSTO))
/

CREATE INDEX FIS00847_FK ON RELACCODRECIMP (CDIMPOSTO ASC)
/

ALTER TABLE RELACCODRECIMP
   ADD CONSTRAINT FKFIS00847IMPOSTO FOREIGN KEY (CDIMPOSTO)
       REFERENCES IMPOSTO (CDIMPOSTO)
/       

CREATE TABLE RELACCODRECPART(
   NRSEQCDRECPART  NUMBER NOT NULL,
   IDTIPO    VARCHAR2(01) DEFAULT 'F' NOT NULL
		CONSTRAINT CKC_IDTIPO_RELACCODRECPART CHECK (IDTIPO IN ('F','C','L')),
   CDFORNECED VARCHAR2(21) NULL,
   CDCLIENTE  VARCHAR2(21) NULL,
   CDFILIAL   VARCHAR2(04) NULL,   
   CDIMPOSTO  VARCHAR2(02) NOT NULL, 
   CDRECEITA  VARCHAR2(04) NOT NULL,
   NRORG             NUMBER 	     DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 		NOT NULL,
   IDATIVO           VARCHAR2(1)     DEFAULT 'S' 					NOT NULL
		CONSTRAINT CKC_IDATIVO_RELACCODRECPART 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_RELACCODRECPART PRIMARY KEY (NRSEQCDRECPART)
)
/

CREATE INDEX FIS00848_FK ON RELACCODRECPART (CDFORNECED ASC)
/

ALTER TABLE RELACCODRECPART
   ADD CONSTRAINT FKFIS00848FORNECEDOR FOREIGN KEY (CDFORNECED)
       REFERENCES FORNECEDOR (CDFORNECED)
/  

CREATE INDEX FIS00849_FK ON RELACCODRECPART (CDCLIENTE ASC)
/

ALTER TABLE RELACCODRECPART
   ADD CONSTRAINT FKFIS00849CLIENTE FOREIGN KEY (CDCLIENTE)
       REFERENCES CLIENTE (CDCLIENTE)
/  

CREATE INDEX FIS00850_FK ON RELACCODRECPART (CDFILIAL ASC)
/

ALTER TABLE RELACCODRECPART
   ADD CONSTRAINT FKFIS00850FILIAL FOREIGN KEY (CDFILIAL)
       REFERENCES FILIAL (CDFILIAL)
/  

CREATE INDEX FIS00851_FK ON RELACCODRECPART (CDIMPOSTO ASC)
/

ALTER TABLE RELACCODRECPART
   ADD CONSTRAINT FKFIS00851IMPOSTO FOREIGN KEY (CDIMPOSTO)
       REFERENCES IMPOSTO (CDIMPOSTO)
/  

ALTER TABLE CODRECEPADRAO
   ADD IDGERADIRF VARCHAR2(01) DEFAULT 'N' NOT NULL
      CONSTRAINT CKC_IDGERADIRF_CODRECEPADRAO CHECK (IDGERADIRF IN ('S','N'))
/

UPDATE CODRECEPADRAO
SET IDGERADIRF = 'S'
WHERE CDIMPOSTONFE IN ('55','65','75','24','21','22')
AND CDRECEPADRAO IN ('0561','0588','0916','0924','1708','1895','3208','3223','3277',
'3280','3426','3533','3540','3556','3562','3579','3746','3770',
'5204','5273','5557','5565','5706','5936','5944','5952','5960',
'5979','5987','6891','6904','8045','8053','8468','8673','9385')
/

COMMIT
/

ALTER TABLE CODRECEITACONTR
   ADD IDGERADIRF VARCHAR2(01) DEFAULT 'N' NOT NULL
      CONSTRAINT CKC_IDGERADIRF_CODRECEITACONTR CHECK (IDGERADIRF IN ('S','N'))
/

UPDATE CODRECEITACONTR
SET IDGERADIRF = 'S'
WHERE CDIMPOSTO IN ('55','65','75','24','21','22')
AND CDRECEITACONTR IN ('0561','0588','0916','0924','1708','1895','3208','3223','3277',
'3280','3426','3533','3540','3556','3562','3579','3746','3770',
'5204','5273','5557','5565','5706','5936','5944','5952','5960',
'5979','5987','6891','6904','8045','8053','8468','8673','9385')
/

COMMIT
/