
/*------ ATD 371668 ------*/
MERGE into  OPCSISTCAD using dual on (OPCSISTCAD.CODSIS = 'LIF' AND  OPCSISTCAD.CODOPC = '1.33') when not matched THEN
insert (codsis,codopc,nomeopc,ativado,numeroativ,mediauso,numdemeses,usoultmes) VALUES ('LIF','1.33','Demonstrativo Apur.Simples Nac','S',0,0,0,0);
COMMIT;


MERGE into  OPCSISTCAD using dual on (OPCSISTCAD.CODSIS = 'LIF' AND  OPCSISTCAD.CODOPC = '5.10') when not matched THEN
insert (codsis,codopc,nomeopc,ativado,numeroativ,mediauso,numdemeses,usoultmes) VALUES ('LIF','5.10','Faixa Tribut.Simples Nacional','S',0,0,0,0);
COMMIT;

DECLARE v_count NUMBER:=0;
BEGIN
SELECT Count(1) INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'FAIXATRIBUTACAOSIMPLESNACIONAL';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE FAIXATRIBUTACAOSIMPLESNACIONAL(
				ID 				 INT  NOT NULL,
				SEGMENTO 		 NUMERIC(1,0), 
				VALORINICIAL	 NUMERIC(15,2),
				VALORFINAL		 NUMERIC(15,2),
				ALIQUOTA		 NUMERIC(8,5),
				VALORDEDUCAO     NUMERIC(15,2),
				ICMS			 NUMERIC(5,2),
				ISS				 NUMERIC(5,2),
				IPI				 NUMERIC(5,2),
				IRPJ			 NUMERIC(5,2),
				CSLL			 NUMERIC(5,2),
				PIS				 NUMERIC(5,2),
				COFINS			 NUMERIC(5,2),
				ALIQUOTAREDUZIDA NUMERIC(8,5),
				LIMITE           NUMERIC(5,2),
				VIGENCIA TIMESTAMP NOT NULL,
				RUSUARIO INT,
				ATUALIZADOEM TIMESTAMP NOT NULL)';
			

EXECUTE IMMEDIATE 'ALTER TABLE FAIXATRIBUTACAOSIMPLESNACIONAL ADD CONSTRAINT PK_FAIXATRIBUTACAOSIMPLESNACIONAL PRIMARY KEY (ID)';
EXECUTE IMMEDIATE 'CREATE SEQUENCE FAIXATRIBUTACAOSIMPLESNACIONAL_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER';

EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FAIXATRIBUTACAOSIMPLESNACIONAL_I
BEFORE INSERT ON FAIXATRIBUTACAOSIMPLESNACIONAL for each ROW
WHEN (new.ID IS NULL)
BEGIN
SELECT FAIXATRIBUTACAOSIMPLESNACIONAL_SEQ.NEXTVAL INTO :new.ID FROM dual;
END;';END IF;
END;
/
