Segue abaixo o script que deve ser executado junto à base de dados de processamento do e-Forms:
-------------------------------------------------------- -- DDL for Type NDD_COMPOSEVALUEOBJECT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_COMPOSEVALUEOBJECT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_COMPOSEVALUEOBJECT" AS OBJECT (doccnpj NUMBER(14,0), docnumber NUMBER(10,0), docserie NUMBER(3,0));'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_COMPOSEVALUEOBJECT_TABLE --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_COMPOSEVALUEOBJECT_TABLE'; IF( lcnt = 0 ) THEN execute immediate ' CREATE OR REPLACE TYPE "NDD_COMPOSEVALUEOBJECT_TABLE" AS TABLE OF NDD_COMPOSEVALUEOBJECT;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_KEYTABLEOBJECT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_KEYTABLEOBJECT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_KEYTABLEOBJECT" AS OBJECT (dockey varchar(50), docprotocol NUMBER(19,0), dckey01 NUMBER(19,0), dckey02 NUMBER(19,0), dckey03 NUMBER(19,0));'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_KEYTABLEOBJECT_TABLE --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_KEYTABLEOBJECT_TABLE'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_KEYTABLEOBJECT_TABLE" AS TABLE OF NDD_KEYTABLEOBJECT;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_NUMBEROBJECT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_NUMBEROBJECT'; IF( lcnt = 0 ) THEN execute immediate ' CREATE OR REPLACE TYPE "NDD_NUMBEROBJECT" AS OBJECT (VNUMBER NUMBER(19,0));'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_NUMBEROBJECT_TABLE --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_NUMBEROBJECT_TABLE'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_NUMBEROBJECT_TABLE" AS TABLE OF ndd_numberobject;'; END IF; END;
/
--================================================================================================================================================--
-------------------------------------------------------- -- DDL for Table TBWEBSERVICEINPUT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEINPUT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBWEBSERVICEINPUT" ( "SERVICEINPUTID" NUMBER(19,0), "HEADER" CLOB, "DOCUMENTDATA" BLOB, "INSERTDATE" TIMESTAMP (6), "SINGLEPROCESS" SMALLINT)'; END IF; END;
/ -------------------------------------------------------- -- ADICIONA NOVA COLUNA singleProcess A TABELA TBWEBSERVICEINPUT caso não exista --------------------------------------------------------
DECLARE column_exists number := 0; BEGIN Select count(*) into column_exists from user_tab_cols where column_name = 'SINGLEPROCESS' and table_name = 'TBWEBSERVICEINPUT';
if (column_exists = 0) then execute immediate 'ALTER TABLE TBWEBSERVICEINPUT ADD (SINGLEPROCESS NUMBER(3))'; end if; END;
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEINPUT -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEINPUT'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEINPUT" MODIFY ("SERVICEINPUTID" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEINPUT" MODIFY ("HEADER" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEINPUT" MODIFY ("DOCUMENTDATA" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEINPUT" ADD CONSTRAINT "TBWEBSERVICEINPUT_PK" PRIMARY KEY ("SERVICEINPUTID") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Sequence TBWEBSERVICEINPUT_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBWEBSERVICEINPUT_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBWEBSERVICEINPUT_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Index TBWEBSERVICEINPUT_PK --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (index_name) INTO lcnt FROM user_indexes where index_name= 'TBWEBSERVICEINPUT_PK'; IF( lcnt = 0 ) THEN execute immediate 'CREATE UNIQUE INDEX "TBWEBSERVICEINPUT_PK" ON "TBWEBSERVICEINPUT" ("SERVICEINPUTID")'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger BI_TBWEBSERVICEINPUT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'BI_TBWEBSERVICEINPUT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER "BI_TBWEBSERVICEINPUT" before INSERT ON "TBWEBSERVICEINPUT" FOR EACH row WHEN (new.SERVICEINPUTID IS NULL) BEGIN SELECT "TBWEBSERVICEINPUT_SEQ".nextval INTO :NEW.SERVICEINPUTID FROM dual; END;'; END IF; END;
/ ALTER TRIGGER "BI_TBWEBSERVICEINPUT" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Table TBWEBSERVICEDOCUMENT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBWEBSERVICEDOCUMENT" ("PROTOCOLID" NUMBER(19,0), "INSERTDATE" TIMESTAMP (6) DEFAULT SYSDATE, "DOCUMENTDATA" CLOB, "SERVICEKIND" NUMBER(2,0))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEDOCUMENT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENT'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("PROTOCOLID" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("INSERTDATE" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("DOCUMENTDATA" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("SERVICEKIND" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" ADD CONSTRAINT "TBWEBSERVICEDOCUMENT_PK" PRIMARY KEY ("PROTOCOLID") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Index TBWEBSERVICEDOCUMENT_PK --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (index_name) INTO lcnt FROM user_indexes where index_name= 'TBWEBSERVICEDOCUMENT_PK'; IF( lcnt = 0 ) THEN execute immediate 'CREATE UNIQUE INDEX "TBWEBSERVICEDOCUMENT_PK" ON "TBWEBSERVICEDOCUMENT" ("PROTOCOLID")'; END IF; END;
/
--=========================================================================================================== -------------------------------------------------------- -- DDL for Table TBWEBSERVICEDOCUMENTCONTROL -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENTCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBWEBSERVICEDOCUMENTCONTROL" ("PROTOCOLID" NUMBER(19,0), "DOCCNPJ" NUMBER(15,0), "DOCNUMBER" NUMBER(10,0), "DOCSERIE" NUMBER(3,0), "INSERTDATE" TIMESTAMP (6) DEFAULT SYSDATE)'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEDOCUMENTCONTROL --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENTCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("PROTOCOLID" NUMBER(19,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("DOCCNPJ" NUMBER(15,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("DOCNUMBER" NUMBER(10,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("DOCSERIE" NUMBER(3,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("INSERTDATE" TIMESTAMP(6) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" ADD CONSTRAINT "TBWEBSERVICEDOCUMENTCONTR_PK" PRIMARY KEY ("DOCCNPJ", "DOCNUMBER", "DOCSERIE") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Index TBWEBSERVICEDOCUMENTCONTR_PK -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT (index_name) INTO lcnt FROM user_indexes where index_name= 'TBWEBSERVICEDOCUMENTCONTR_PK'; IF( lcnt = 0 ) THEN execute immediate 'CREATE UNIQUE INDEX "TBWEBSERVICEDOCUMENTCONTR_PK" ON "TBWEBSERVICEDOCUMENTCONTROL" ("DOCCNPJ", "DOCNUMBER", "DOCSERIE")'; END IF; END;
/ --=========================================================================================================== -------------------------------------------------------- -- DDL for Table TBDOCSEARCHPROCESS -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBDOCSEARCHPROCESS'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBDOCSEARCHPROCESS" ("PROCESSID" NUMBER(20,0), "DOCKEY1" NUMBER(20,0), "DOCKEY2" NUMBER(20,0), "DOCKEY3" NUMBER(20,0), "PROTOCOL" VARCHAR2(20))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBDOCSEARCHPROCESS --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBDOCSEARCHPROCESS'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("PROCESSID" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY1" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY2" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY3" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("PROTOCOL" VARCHAR2(20) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" ADD CONSTRAINT "TBDOCSEARCHPROCESS_PK" PRIMARY KEY ("PROCESSID") ENABLE;'; END IF; END;
/ -------------------------------------------------------- -- DDL for Sequence TBDOCSEARCHPROCESS_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBDOCSEARCHPROCESS_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBDOCSEARCHPROCESS_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger TBDOCSEARCHPROCESS_PK_TRG --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'TBDOCSEARCHPROCESS_PK_TRG'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER TBDOCSEARCHPROCESS_PK_TRG BEFORE INSERT ON TBDOCSEARCHPROCESS FOR EACH ROW WHEN (new.PROCESSID IS NULL) BEGIN SELECT TBDOCSEARCHPROCESS_SEQ.NEXTVAL INTO :new.PROCESSID FROM dual;END;'; END IF; END;
/ ALTER TRIGGER "TBDOCSEARCHPROCESS_PK_TRG" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Table TBWEBSERVICENSUCONTROL -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICENSUCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICENSUCONTROL ("OID" NUMBER(10,0), "CNPJ" VARCHAR2(14), "JOB" VARCHAR2(30), "UF" VARCHAR2(2), "ULTNSU" NUMBER(18,0))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICENSUCONTROL --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICENSUCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("OID" NUMBER(10,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("CNPJ" VARCHAR2(14) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("JOB" VARCHAR2(30) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("UF" VARCHAR2(2) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("ULTNSU" NUMBER(18,0) NOT NULL); ALTER TABLE "TBWEBSERVICENSUCONTROL" ADD CONSTRAINT "TBWEBSERVICENSUCONTROL_PK" PRIMARY KEY ("OID") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Sequence TBWEBSERVICENSUCONTROL_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBWEBSERVICENSUCONTROL_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBWEBSERVICENSUCONTROL_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger TBWEBSERVICENSUCONTROL_PK_TRG --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'TBWEBSERVICENSUCONTROL_PK_TRG'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER TBWEBSERVICENSUCONTROL_PK_TRG BEFORE INSERT ON TBWEBSERVICENSUCONTROL FOR EACH ROW WHEN (new."OID" IS NULL) BEGIN SELECT TBWEBSERVICENSUCONTROL_SEQ.NEXTVAL INTO :new."OID" FROM dual; END;'; END IF; END;
/ ALTER TRIGGER "TBWEBSERVICENSUCONTROL_PK_TRG" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Table TBWEBSERVICEDOCSITUATION -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCSITUATION'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICEDOCSITUATION ("ID" NUMBER(18,0), "SEARCHIN" CLOB, "SEARCHOUT" CLOB, "STATUS" NUMBER(5,0))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEDOCSITUATION --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCSITUATION'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("ID" NUMBER(18,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("SEARCHIN" CLOB NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("SEARCHOUT" CLOB NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("STATUS" NUMBER(5,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" ADD CONSTRAINT "TBWEBSERVICEDOCSIT_PK" PRIMARY KEY ("ID") ENABLE;'; END IF; END;
/ -------------------------------------------------------- -- DDL for Table TBWEBSERVICECOLDCONTROL -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICECOLDCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICECOLDCONTROL ("COLDID" NUMBER(10,0), "DOCQTD" NUMBER(10,0))'; END IF; END; / -------------------------------------------------------- -- DDL for Table TBWEBSERVICECOLDCONTROLCNPJ -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICECOLDCONTROLCNPJ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICECOLDCONTROLCNPJ ("CNPJ" NUMBER(14,0), "STATUS" NUMBER(3,0), "BLOCKDATE" TIMESTAMP(6))'; END IF; END; / -------------------------------------------------------- -- DDL for Table TBWEBSERVICECOLDNSU -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICECOLDNSU'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICECOLDNSU ("COLDID" NUMBER(10,0), "CNPJ" VARCHAR2(30), "NSU" NUMBER(18,0))'; END IF; END; /
-------------------------------------------------------- -- DDL for Sequence TBWEBSERVICEDOCSITUATION_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBWEBSERVICEDOCSITUATION_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBWEBSERVICEDOCSITUATION_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger TBWEBSERVICEDOCSIT_PK_TRG --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'TBWEBSERVICEDOCSIT_PK_TRG'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER TBWEBSERVICEDOCSIT_PK_TRG BEFORE INSERT ON TBWEBSERVICEDOCSITUATION FOR EACH ROW WHEN (new."ID" IS NULL) BEGIN SELECT TBWEBSERVICEDOCSITUATION_SEQ.NEXTVAL INTO :new."ID" FROM dual;END;'; END IF; END;
/ ALTER TRIGGER "TBWEBSERVICEDOCSIT_PK_TRG" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Function CREATECOMPOSEVALUES --------------------------------------------------------
CREATE OR REPLACE FUNCTION "CREATECOMPOSEVALUES" (plist CLOB) RETURN NDD_COMPOSEVALUEOBJECT_TABLE PIPELINED AS v_pos number(19,0); v_nextpos number(19,0); v_valuelen number(19,0); v_docnumber number(10,0); v_doccnpj NUMBER(14,0); v_docserie NUMBER(3,0); v_document VARCHAR2(100); BEGIN v_pos := 0; v_nextpos := 1; v_docnumber := 0; v_docserie := 0; v_doccnpj := 0; WHILE v_nextpos > 0 LOOP v_nextpos := INSTR(plist, ',' , v_pos + 1); v_valuelen := (CASE WHEN v_nextpos > 0 THEN v_nextpos ELSE LENGTH(plist) + 1 END) - v_pos - 1; v_document := SUBSTR(plist, v_pos + 1, v_valuelen); v_doccnpj := TO_NUMBER(SUBSTR(v_document, 1, 14)); v_docnumber := TO_NUMBER(SUBSTR(v_document, 15, 9)); v_docserie := TO_NUMBER(SUBSTR(v_document, 24, 3)); PIPE ROW(ndd_composevalueobject(v_doccnpj,v_docnumber, v_docserie)); V_pos := V_nextpos; END LOOP RETURN; END CREATECOMPOSEVALUES;
/
-------------------------------------------------------- -- DDL for Function CREATEKEYTABLE --------------------------------------------------------
CREATE OR REPLACE FUNCTION "CREATEKEYTABLE" (plist CLOB) RETURN NDD_KEYTABLEOBJECT_TABLE PIPELINED AS v_pos number(19,0); v_nextpos number(19,0); v_valuelen number(19,0); v_documentkey VARCHAR2(100); v_documenttype varchar2(2); v_protocol number(19,0); v_dockey01 number(19,0); v_dockey02 number(19,0); v_dockey03 number(19,0); BEGIN v_pos := 0; v_nextpos := 1; v_documentkey := ''; v_documenttype := ''; v_protocol := 0; v_dockey01 := 0; v_dockey02 := 0; v_dockey03 := 0; WHILE v_nextpos > 0 LOOP v_nextpos := INSTR(plist, ',' , v_pos + 1); v_valuelen := (CASE WHEN v_nextpos > 0 THEN v_nextpos ELSE LENGTH(plist) + 1 END) - v_pos - 1; v_documentkey := SUBSTR(plist, v_pos + 1, v_valuelen); if length(v_documentkey) > 44 then v_protocol := to_number(substr(v_documentkey, 45, length(v_documentkey))); v_documentkey := substr(v_documentkey, 1, 44); else v_protocol := 0; end if; v_documenttype := substr(v_documentkey, 21, 2); if v_documenttype = '55' then v_documentkey := 'NFe' || v_documentkey; else v_documentkey := 'CTe' || v_documentkey; end if; v_dockey01 := TO_NUMBER(SUBSTR(v_documentkey, 4, 18)); v_dockey02 := TO_NUMBER(SUBSTR(v_documentkey, 22, 18)); v_dockey03 := TO_NUMBER(SUBSTR(v_documentkey, 40, 8)); PIPE ROW(ndd_keytableobject(substr(v_documentkey,1,length(v_documentkey)), v_protocol, v_dockey01, v_dockey02, v_dockey03)); V_pos := V_nextpos; END LOOP RETURN; END;
/
-------------------------------------------------------- -- DDL for Function CREATELISTTABLE --------------------------------------------------------
CREATE OR REPLACE FUNCTION "CREATELISTTABLE" (plist CLOB) RETURN ndd_numberobject_table PIPELINED AS
v_pos number(19,0); v_nextpos number(19,0); v_valuelen number(19,0); v_numberval number(19,0); BEGIN v_pos := 0; v_nextpos := 1; v_numberval := 0; WHILE v_nextpos > 0 LOOP v_nextpos := INSTR(plist, ',' , v_pos + 1); v_valuelen := (CASE WHEN v_nextpos > 0 THEN v_nextpos ELSE LENGTH(plist) + 1 END) - v_pos - 1; v_numberval := TO_NUMBER(SUBSTR(plist, v_pos + 1, v_valuelen)); PIPE ROW(ndd_numberobject(v_numberval)); V_pos := V_nextpos; END LOOP RETURN; END;
/ -------------------------------------------------------- -- Altera o tamanho da coluna Job da tabela TBWEBSERVICENSUCONTROL de 30 para 60 --------------------------------------------------------
DECLARE CONTADOR NUMBER; BEGIN SELECT DATA_LENGTH INTO CONTADOR FROM USER_TAB_COLUMNS WHERE table_name = 'TBWEBSERVICENSUCONTROL' AND COLUMN_NAME = 'JOB';
IF CONTADOR = 30 THEN EXECUTE IMMEDIATE 'ALTER TABLE TBWEBSERVICENSUCONTROL MODIFY JOB VARCHAR2(60)'; END IF; END;
/ |
Apos atualização para o patch 62 do WS e necessario atualizar esses scripts:
Oracle:
BEGIN
EXECUTE IMMEDIATE '
ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL ADD
(
CNPJSTR VARCHAR2(14),
CNPJBASE NUMBER(19,0),
CNPJORDEM NUMBER(10,0),
CNPJDV NUMBER(3,0)
)';
END;
/
BEGIN
EXECUTE IMMEDIATE '
ALTER TABLE TBWEBSERVICECOLDCONTROLCNPJ ADD
(
CNPJSTR VARCHAR2(14),
CNPJBASE NUMBER(19,0),
CNPJORDEM NUMBER(10,0),
CNPJDV NUMBER(3,0)
)';
END;
/
BEGIN
EXECUTE IMMEDIATE '
ALTER TABLE TBCOLDSEARCHRESULT ADD
(
CNPJSTR VARCHAR2(14),
CNPJBASE NUMBER(19,0),
CNPJORDEM NUMBER(10,0),
CNPJDV NUMBER(3,0),
ACCESSKEY VARCHAR2(44),
EMISSIONTYPE NUMBER(3,0),
MODEL NUMBER(3,0),
SERIE NUMBER(3,0),
DOCUMENTNUMBER NUMBER(10,0)
)';
END;
/
BEGIN
EXECUTE IMMEDIATE '
ALTER TABLE TBDOCSEARCHPROCESS ADD (
ACCESSKEY VARCHAR2(255),
EMISSIONTYPE NUMBER(5),
MODEL NUMBER(5),
SERIE NUMBER(10),
DOCUMENTNUMBER NUMBER(10)
)';
END;
/
BEGIN
EXECUTE IMMEDIATE '
ALTER TABLE TBSTATISTICSCONSULT ADD (
SERIE NUMBER(10),
DOCUMENTNUMBER NUMBER(10)
)';
END;
/
Apos atualização para o patch 63 do WS e necessario atualizar esses scripts:
ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL DROP CONSTRAINT PK_TBWEBSERVICEDOCUMENTCONTROL;
ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL MODIFY DOCCNPJ NUMBER(19,0) NULL;
DROP FUNCTION CREATEKEYTABLE;
------------
DROP TYPE NDD_KEYTABLEOBJECT_TABLE FORCE;
-----------
DROP TYPE NDD_KEYTABLEOBJECT FORCE;
----------
CREATE TYPE NDD_KEYTABLEOBJECT AS OBJECT
(
DOCKEY VARCHAR2(50),
DOCPROTOCOL NUMBER(19,0),
EMISSIONTYPE NUMBER(5,0),
DOCUMENTNUMBER NUMBER(19,0),
MODEL NUMBER(5,0),
SERIE NUMBER(5,0),
CNPJORDEM NUMBER(19,0),
CNPJBASE NUMBER(19,0),
CNPJDV NUMBER(5,0)
);
-------------
CREATE TYPE NDD_KEYTABLEOBJECT_TABLE
AS TABLE OF NDD_KEYTABLEOBJECT;
-------------
CREATE OR REPLACE FUNCTION CREATEKEYTABLE
(
plist CLOB
)
RETURN NDD_KEYTABLEOBJECT_TABLE PIPELINED
AS
v_pos NUMBER(19,0);
v_nextpos NUMBER(19,0);
v_valuelen NUMBER(19,0);
v_documentkey VARCHAR2(100);
v_documenttype VARCHAR2(2);
v_protocol NUMBER(19,0);
v_emissiontype NUMBER(5,0);
v_documentnumber NUMBER(38,0);
v_model NUMBER(5,0);
v_serie NUMBER(5,0);
v_cnpj VARCHAR2(14);
v_cnpjbase NUMBER(38,0);
v_cnpjordem NUMBER(38,0);
v_cnpjdv NUMBER(5,0);
BEGIN
v_pos := 0;
v_nextpos := 1;
WHILE v_nextpos > 0
LOOP
v_nextpos :=
INSTR(plist, ',', v_pos + 1);
v_valuelen :=
(
CASE
WHEN v_nextpos > 0
THEN v_nextpos
ELSE LENGTH(plist) + 1
END
) - v_pos - 1;
v_documentkey :=
SUBSTR(plist, v_pos + 1, v_valuelen);
----------------------------------------------------------------
-- PROTOCOLO
----------------------------------------------------------------
IF LENGTH(v_documentkey) > 44 THEN
v_protocol :=
TO_NUMBER(
SUBSTR(v_documentkey,45) );
v_documentkey :=
SUBSTR(v_documentkey,1,44);
ELSE
v_protocol := 0;
END IF;
----------------------------------------------------------------
-- MODELO
----------------------------------------------------------------
v_documenttype :=
SUBSTR(v_documentkey,21,2);
v_model :=
TO_NUMBER(v_documenttype);
----------------------------------------------------------------
-- TIPO EMISSAO
----------------------------------------------------------------
v_emissiontype :=
TO_NUMBER(
SUBSTR(v_documentkey,35,1)
);
----------------------------------------------------------------
-- SERIE
----------------------------------------------------------------
v_serie :=
TO_NUMBER(
SUBSTR(v_documentkey,23,3)
);
----------------------------------------------------------------
-- NUMERO DOCUMENTO
----------------------------------------------------------------
v_documentnumber :=
TO_NUMBER(
SUBSTR(v_documentkey,26,9)
);
----------------------------------------------------------------
-- CNPJ
----------------------------------------------------------------
v_cnpj :=
SUBSTR(v_documentkey,7,14);
----------------------------------------------------------------
-- CNPJ BASE
----------------------------------------------------------------
v_cnpjbase :=
TO_NUMBER(ASCII(SUBSTR(v_cnpj,1,1))-48) * TO_NUMBER('271818611107')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,2,1))-48) * TO_NUMBER('6321363049')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,3,1))-48) * TO_NUMBER('147008443')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,4,1))-48) * TO_NUMBER('3418801')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,5,1))-48) * TO_NUMBER('79507')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,6,1))-48) * TO_NUMBER('1849')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,7,1))-48) * TO_NUMBER('43')
+ TO_NUMBER(ASCII(SUBSTR(v_cnpj,8,1))-48);
----------------------------------------------------------------
-- CNPJ ORDEM
----------------------------------------------------------------
v_cnpjordem :=
(
(
(
(ASCII(SUBSTR(v_cnpj,9,1))-48)*43
+(ASCII(SUBSTR(v_cnpj,10,1))-48)
)*43
+(ASCII(SUBSTR(v_cnpj,11,1))-48)
)*43
+(ASCII(SUBSTR(v_cnpj,12,1))-48)
);
----------------------------------------------------------------
-- CNPJ DV
----------------------------------------------------------------
v_cnpjdv :=
(ASCII(SUBSTR(v_cnpj,13,1))-48)*10
+ (ASCII(SUBSTR(v_cnpj,14,1))-48);
----------------------------------------------------------------
-- PREFIXO
----------------------------------------------------------------
IF v_documenttype = '55' THEN
v_documentkey :=
'NFE' || v_documentkey;
ELSE
v_documentkey :=
'CTE' || v_documentkey;
END IF;
----------------------------------------------------------------
-- RETORNO
----------------------------------------------------------------
PIPE ROW
(
NDD_KEYTABLEOBJECT
(
v_documentkey,
v_protocol,
v_emissiontype,
v_documentnumber,
v_model,
v_serie,
v_cnpjordem,
v_cnpjbase,
v_cnpjdv
)
);
v_pos := v_nextpos;
END LOOP;
RETURN;
END;
/* =========================================================
TBWEBSERVICEDOCUMENTCONTROL
========================================================= */
-- Remover chave primária atual
DROP INDEX TBWEBSERVICEDOCUMENTCONTR_PK;
-- Alterar tipo da coluna
ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL
MODIFY (docCnpj NUMBER(14,0) NULL);
-- Recriar chave primária sem docCnpj
CREATE UNIQUE INDEX TBWEBSERVICEDOCUMENTCONTR_PK
ON TBWEBSERVICEDOCUMENTCONTROL (DOCCNPJ, DOCNUMBER, DOCSERIE)
PCTFREE 10
INITRANS 2
MAXTRANS 255
COMPUTE STATISTICS
TABLESPACE USERS;
/* =========================================================
TBWEBSERVICECOLDCONTROLCNPJ
========================================================= */
-- Permitir NULL e alterar tipo da coluna CNPJ
ALTER TABLE TBWEBSERVICECOLDCONTROLCNPJ
MODIFY (CNPJ NUMBER(14,0) NULL);
/* =========================================================
TBSTATISTICSCONSULT
========================================================= */
-- Permitir NULL e alterar tipo da coluna CNPJ
ALTER TABLE TBSTATISTICSCONSULT
MODIFY (CNPJ NUMBER(14,0) NULL);
-- Permitir NULL e alterar tipo das chaves de documento
ALTER TABLE TBSTATISTICSCONSULT
MODIFY (DocumentKey1 NUMBER(19,0) NULL);
ALTER TABLE TBSTATISTICSCONSULT
MODIFY (DocumentKey2 NUMBER(19,0) NULL);
ALTER TABLE TBSTATISTICSCONSULT
MODIFY (DocumentKey3 NUMBER(9) NULL);
Exclusão e criação de indices:
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX DBO.IDX_TBWEBSERVICEDOCCONTROL';
EXCEPTION
WHEN OTHERS THEN
-- ORA-01418: specified index does not exist
IF SQLCODE != -1418 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX DBO.IDX_TBWEBSERVICEDOCCONTROL
ON DBO.TBWEBSERVICEDOCUMENTCONTROL (
CNPJBASE,
CNPJORDEM,
CNPJDV,
DOCNUMBER,
DOCSERIE
)';
EXCEPTION
WHEN OTHERS THEN
-- ORA-00955: name is already used by an existing object
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;
/