INSERT INTO CONFIGURACION (CONF_MODULO, CONF_CATEGORIA, CONF_PROPIEDAD, CONF_VALOR, CONF_POSIBLES, CONF_AYUDA) VALUES ('CRM', 'GENERAL', 'COLOR VENTANA PRINCIPAL REPORT', '0,153,51', '0x00000-0xFFFFFF', 'Indique el color en formato RGB que quiere de fondo en la ventana principal del módulo'); INSERT INTO CONFIGURACION (CONF_MODULO, CONF_CATEGORIA, CONF_PROPIEDAD, CONF_VALOR, CONF_POSIBLES, CONF_AYUDA) VALUES ('CRM', 'GENERAL', 'COLOR FINAL VENTANA PRINCIPAL REPORT', '102,255,51', '0x00000-0xFFFFFF', 'Indique el color en formato RGB que quiere de fondo en la ventana principal del módulo'); INSERT INTO TIPO_DOCUMENTO (TIDO_COD, TIDO_NOMCORTO, TIDO_NOMLARGO, TIDO_MODULO, TIDO_INVE, TIDO_CART, TIDO_CXP, TIDO_CAJA, TIDO_BCOS, TIDO_NOM, TIDO_IMP) VALUES (1000, 'TICKETSR', 'TICKETS CLENTES', 'CARTERA', 'N', 'N', 'N', 'N', 'N', 'N', 'N'); /******************************************************************************/ /*** Generated by IBExpert 2017/12/18 13:00:35 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE REPORT_PERMISOS ( USUARIO_PERMISOS VARCHAR(8) NOT NULL, MODULO_PERMISOS VARCHAR(20) NOT NULL, OPCION_PERMISOS INTEGER NOT NULL, ACCESO_PERMISOS T_BOOLEANO /* T_BOOLEANO = CHAR(1) check (value in ('S','N')) */ ); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_PERMISOS TO PUBLIC; /******************************************************************************/ /*** Generated by IBExpert 2017/12/18 13:01:51 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE REPORT_AREA ( CODIGO_AREA VARCHAR(3) NOT NULL, NOMBRE_AREA T_CONCEPTO NOT NULL /* T_CONCEPTO = VARCHAR(60) */ ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_AREA ADD CONSTRAINT PK_REPORT_AREA PRIMARY KEY (CODIGO_AREA); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_AREA TO PUBLIC; INSERT INTO REPORT_AREA (CODIGO_AREA, NOMBRE_AREA) VALUES ('1', 'ADMINISTRATIVA'); INSERT INTO REPORT_AREA (CODIGO_AREA, NOMBRE_AREA) VALUES ('2', 'COMERCIAL'); INSERT INTO REPORT_AREA (CODIGO_AREA, NOMBRE_AREA) VALUES ('3', 'CONTABLE'); INSERT INTO REPORT_AREA (CODIGO_AREA, NOMBRE_AREA) VALUES ('4', 'LOGISTICA'); /******************************************************************************/ /*** Generated by IBExpert 2017/12/18 14:57:17 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE REPORT_TEMAS ( CODIGO_AREA VARCHAR(3) NOT NULL, CODIGO_TEMA ID NOT NULL /* ID = INTEGER */, NOMBRE_TEMA T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */ ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_TEMAS ADD CONSTRAINT PK_REPORT_TEMA PRIMARY KEY (CODIGO_AREA, CODIGO_TEMA); /******************************************************************************/ /*** Foreign Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_TEMAS ADD CONSTRAINT FK_REPORT_TEMA FOREIGN KEY (CODIGO_AREA) REFERENCES REPORT_AREA (CODIGO_AREA); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_TEMAS TO PUBLIC; INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('1', 1, 'CLIENTE'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('1', 2, 'EMPLEADO'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('2', 1, 'DOCUMENTACIÓN'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('2', 2, 'FACTURACIÓN'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('2', 3, 'SERVICIO'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('3', 1, 'CLIENTE'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('3', 2, 'PROVEEDOR'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('4', 1, 'ALISTAMIENTO(Producto)'); INSERT INTO REPORT_TEMAS (CODIGO_AREA, CODIGO_TEMA, NOMBRE_TEMA) VALUES ('4', 2, 'ENTREGA(Servicio)'); /******************************************************************************/ /*** Generated by IBExpert 2017/12/18 16:15:13 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE REPORT_OBSERVACIONES ( CODIGO_AREA VARCHAR(3) NOT NULL, CODIGO_TEMA VARCHAR(3) NOT NULL, CODIGO_OBSERVACION VARCHAR(3) NOT NULL, NOMBRE_OBSERVACION T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */ ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_OBSERVACIONES ADD CONSTRAINT PK_REPORT_OBSERVACIONES PRIMARY KEY (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: REPORT_OBSERVACIONES_BI */ CREATE OR ALTER TRIGGER REPORT_OBSERVACIONES_BI FOR REPORT_OBSERVACIONES ACTIVE BEFORE INSERT POSITION 0 as begin IF ((NEW.CODIGO_AREA IS NOT NULL) and (new.CODIGO_TEMA is not null)) THEN BEGIN if (not exists (select CODIGO_AREA from REPORT_AREA where CODIGO_AREA = new.CODIGO_AREA)) then EXCEPTION DATO_OBLIGATORIO 'No existe el area del ticket especificada.'; if (not exists (select CODIGO_TEMA from REPORT_TEMAS where CODIGO_AREA = new.CODIGO_AREA and CODIGO_TEMA = new.CODIGO_TEMA)) then EXCEPTION DATO_OBLIGATORIO 'No existe la tema del ticket de especificado.'; END end ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_OBSERVACIONES TO PUBLIC; INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '1', '1', 'HORARIOS DE ATENCION'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '1', '2', 'LOCACIONES INSEGURAS O EN MAL ESTADO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '1', '3', 'MAL SERVICIO AL CLIENTE'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '2', '1', 'INCUMPLIMIENTO A LAS CAPACITACIONES'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '2', '2', 'MAL TRATO DE UN COMPAÑERO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '2', '3', 'MAL TRATO DE JEFE INMEDIATO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('1', '2', '4', 'PAGO TARDIO DE NOMINA'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '1', '1', 'CARTAS DE DISTRIBUCIÓN'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '1', '2', 'CERTIFICACIONES COMERCIALES'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '1', '3', 'COTIZACIONES INCOMPLETAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '1', '4', 'COTIZACIONES PENDIENTES'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '1', '5', 'ENVIÓ INOPORTUNO DE FICHAS TÉCNICAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '1', '6', 'ENVIÓ INOPORTUNO DE INVIMAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '2', '1', 'CIERRES DE FACTURACIÓN'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '2', '2', 'DIFERENCIA DE PRECIOS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '2', '3', 'UNIDADES DE VENTA INADECUADAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '2', '4', 'INFORMACIÓN DESACTUALIZADA DEL CLIENTE'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '3', '1', 'ASESORÍA DE PRODUCTOS DEFICIENTE'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '3', '2', 'AUSENCIA DEL REPRESENTANTE DE VENTAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '3', '3', 'AUSENCIA DE RESPUESTAS A CORREOS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('2', '3', '4', 'MAL DIRECCIONAMIENTO A LAS SOLICITUDES'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('3', '1', '1', 'COBRO DE FACTURAS CANCELADAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('3', '1', '2', 'ENVIO INOPORTUNO DE DOCUMENTACIÓN'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('3', '1', '3', 'MODALIDAD DE PAGO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('3', '1', '4', 'NOTAS NO DESCONTADAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('3', '2', '1', 'INCONSISTENCIA EN APGO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('3', '2', '2', 'TIEMPO PAGO INOPORTUNO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '1', '1', 'PRODUCTO EN MAL ESTADO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '1', '2', 'PRODUCTO NO COINCIDE (DOCUMENTO VS FISICO)'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '1', '3', 'PRODUCTO PARCIAL'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '1', '4', 'PRODUCTO VENCIDO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '1', '5', 'PRODUCTO NO SOLICITADO'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '2', '1', 'INCUMPLIMIENTO EN EL TIEMPO DE ENTREGA'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '2', '2', 'INCUMPLIMIENTO EN CITAS'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '2', '3', 'MAL SERVICIO POR PARTE DEL TRANSPORTADOR'); INSERT INTO REPORT_OBSERVACIONES (CODIGO_AREA, CODIGO_TEMA, CODIGO_OBSERVACION, NOMBRE_OBSERVACION) VALUES ('4', '2', '4', 'MAL SERVICIO POR PARTE DEL OPERARIO LOGISTICO'); --2017/01/24-- /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER PREFIJOS_RI FOR PREFIJOS ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (new.sucu_id is null) then new.sucu_id = 0; if (new.pref_dec1818 is null) then new.pref_dec1818 = 'N'; if (NEW.tido_cod = 1000 ) then EXECUTE PROCEDURE COMPLETA_CEROS8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); else EXECUTE PROCEDURE COMPLETA_CEROS (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); END ^ SET TERM ; ^ /* Privileges of triggers */ GRANT UPDATE, REFERENCES ON PREFIJOS TO TRIGGER PREFIJOS_RI; GRANT EXECUTE ON PROCEDURE COMPLETA_CEROS TO TRIGGER PREFIJOS_RI; /* Privileges of triggers */ GRANT UPDATE, REFERENCES ON PREFIJOS TO TRIGGER PREFIJOS_RI; GRANT EXECUTE ON PROCEDURE COMPLETA_CEROS TO TRIGGER PREFIJOS_RI; SET TERM ^ ; CREATE OR ALTER TRIGGER PREFIJOS_RU FOR PREFIJOS ACTIVE BEFORE UPDATE POSITION 0 AS BEGIN if (new.sucu_id is null) then new.sucu_id = 0; if (NEW.TIDO_COD=1000 ) then EXECUTE PROCEDURE completa_ceros8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); else EXECUTE PROCEDURE COMPLETA_CEROS (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); END ^ SET TERM ; ^ /* Privileges of triggers */ GRANT UPDATE, REFERENCES ON PREFIJOS TO TRIGGER PREFIJOS_RU; GRANT EXECUTE ON PROCEDURE COMPLETA_CEROS TO TRIGGER PREFIJOS_RU; INSERT INTO TIPO_DOCUMENTO (TIDO_COD, TIDO_NOMCORTO, TIDO_NOMLARGO, TIDO_MODULO, TIDO_INVE, TIDO_CART, TIDO_CXP, TIDO_CAJA, TIDO_BCOS, TIDO_NOM, TIDO_IMP) VALUES (1000, 'TICKETSR', 'TICKETS CLENTES', 'VENTAS', 'N', 'N', 'N', 'N', 'N', 'N', 'N'); SET TERM ^ ; CREATE OR ALTER PROCEDURE CONSULTA_PREFIJO_RR ( PREFIJO VARCHAR(20), DOCUMENTO INTEGER) RETURNS ( PREFIJOS VARCHAR(4), CONSECUTIVO VARCHAR(10), AUTOMATICO CHAR(1), ESTADO CHAR(1)) AS DECLARE VARIABLE CANTIDAD INTEGER; BEGIN FOR SELECT count(*) FROM PREFIJOS S WHERE S.PREF_pRE =:PREFIJO AND S.TIDO_COD = :DOCUMENTO INTO :CANTIDAD DO begin if (CANTIDAD >0) then SELECT C.PREF_PRE, C.PREF_ACTUAL, C.PREF_AUTO, C.PREF_ACTIVO FROM PREFIJOS C WHERE C.PREF_PRE =:PREFIJO AND C.TIDO_COD = :DOCUMENTO INTO :PREFIJOS,:CONSECUTIVO, :AUTOMATICO, :ESTADO; ELSE exception PREFIJO_NO_EXISTE ' Es necesario verificar los prefijos en la tabla de PREFIJOS'; suspend; END END^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON PREFIJOS TO PROCEDURE CONSULTA_PREFIJO_RR; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE CONSULTA_PREFIJO_RR TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE CONSULTA_PREFIJO_RR TO SYSDBA; /*20180129*/ SET TERM ^ ; CREATE OR ALTER PROCEDURE CONSULTA_CLIENTES_RR ( CODIGOS VARCHAR(20)) RETURNS ( NIT VARCHAR(20), NOMBRE VARCHAR(60), CODIGO VARCHAR(20), CIU VARCHAR(40), TEL VARCHAR(40), ZONA VARCHAR(2), NOMZONA VARCHAR(60), VEND INTEGER, NOMVEND VARCHAR(60), COBR INTEGER, NOMCOBR VARCHAR(60), ESTADO CHAR(1), CORREO VARCHAR(100), DIRECCION VARCHAR(60)) AS begin FOR SELECT T.TERC_NIT, TERC_NOM, CLIE_COD, TERC_TEL, TERC_CIU, ZONA_COD, VEND_COD, COBR_COD, CLIE_ESTADO , T.TERC_EMAIL, T.TERC_DIR FROM TERCEROS T, CLIENTES C WHERE T.TERC_NIT = C.TERC_NIT AND T.terc_nit= :CODIGOS ORDER BY T.TERC_NIT INTO :NIT, :NOMBRE, :CODIGO, :TEL, :CIU, :ZONA, :VEND, :COBR, :ESTADO, :CORREO, :DIRECCION DO BEGIN SELECT ZONA_NOM FROM ZONAS WHERE ZONA_COD = :ZONA into :NOMZONA; SELECT VEND_NOMBRE FROM VENDEDORES WHERE VEND_COD = :VEND into :NOMVEND; SELECT COBR_NOM FROM COBRADORES WHERE COBR_COD = :COBR into :NOMCOBR; suspend; END end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON TERCEROS TO PROCEDURE CONSULTA_CLIENTES_RR; GRANT SELECT ON CLIENTES TO PROCEDURE CONSULTA_CLIENTES_RR; GRANT SELECT ON ZONAS TO PROCEDURE CONSULTA_CLIENTES_RR; GRANT SELECT ON VENDEDORES TO PROCEDURE CONSULTA_CLIENTES_RR; GRANT SELECT ON COBRADORES TO PROCEDURE CONSULTA_CLIENTES_RR; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE CONSULTA_CLIENTES_RR TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE CONSULTA_CLIENTES_RR TO SYSDBA; /*20180201*/ /******************************************************************************/ /*** Generated by IBExpert 2018/02/01 18:34:17 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_REPORT_TICKETS_ID; CREATE TABLE REPORT_TICKETS ( TICKET_ID T_ID NOT NULL /* T_ID = INTEGER */, PREF_PRE VARCHAR(4), TICKET_NUMERO T_NUMDOC /* T_NUMDOC = VARCHAR(8) */, TERC_NIT T_NIT NOT NULL /* T_NIT = VARCHAR(20) */, TICKET_PRIORIDAD CHAR(5) NOT NULL, TICKET_FECHA DATE NOT NULL, TICKET_NOMBRE VARCHAR(60), TICKET_SUCURSAL VARCHAR(10), TICKET_CONTACTO T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */, TICKET_TELEFONO T_TELEFONO /* T_TELEFONO = VARCHAR(40) */, TICKET_EMAIL VARCHAR(100), TICKET_SUCESO VARCHAR(15), TICKET_AREA T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */, TICKET_TEMA T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */, TICKET_OBSERVACION T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */, TICKET_DESCRIPCION BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL, TICKET_RESPUESTA T_CONCEPTO /* T_CONCEPTO = VARCHAR(60) */, TICKET_ASIGNAR VARCHAR(60), TICKET_ESTADO T_BOOLEANO DEFAULT 'N' /* T_BOOLEANO = CHAR(1) check (value in ('S','N')) */ ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_TICKETS ADD CONSTRAINT PK_REPORT_TICKETS PRIMARY KEY (TICKET_ID); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: REPORT_TICKETS_BI */ CREATE OR ALTER TRIGGER REPORT_TICKETS_BI FOR REPORT_TICKETS ACTIVE BEFORE INSERT POSITION 0 as begin if (new.ticket_id is null) then new.ticket_id = gen_id(GEN_REPORT_TICKETS_ID,1); end ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_TICKETS_PI FOR REPORT_TICKETS ACTIVE BEFORE INSERT POSITION 0 AS declare variable OK CHAR(1); begin OK = 'N'; SELECT 'S' FROM PREFIJOS WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE INTO OK; IF (OK = 'N') THEN exception PREFIJO_NO_EXISTE 'El prefijo ' || new.PREF_PRE || ' no esta registrado.'; end ^ SET TERM ; ^ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; CREATE GENERATOR GEN_REPORT_TICKETS_ID; SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_TICKETS_BI FOR REPORT_TICKETS ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE NUMERO VARCHAR(8); DECLARE VARIABLE AUTOM CHAR(1); DECLARE VARIABLE EXISTE CHAR(1); DECLARE VARIABLE ESTADO CHAR(1); BEGIN /*GENERADOR SI EL CAMPO ID IS NULL*/ IF (NEW.TICKET_ID IS NULL) THEN NEW.TICKET_ID = GEN_ID(GEN_REPORT_TICKETS_ID,1); /* VALIDE EL ESTADO DEL CLIENTE */ BEGIN SELECT C.CLIE_ESTADO FROM CLIENTES C WHERE TERC_NIT = NEW.TERC_NIT INTO :ESTADO; IF (ESTADO <> 'A') THEN EXCEPTION CLIENTE_INACTIVO 'El cliente ' || NEW.TERC_NIT || ' esta inactivo o en No vender.'; END /* Verifique que el numero no existe previamente */ EXISTE = 'N'; SELECT 'S' FROM REPORT_TICKETS WHERE PREF_PRE = NEW.PREF_PRE AND TICKET_NUMERO = NEW.TICKET_NUMERO INTO :EXISTE; IF (EXISTE = 'S') THEN EXCEPTION DOCUMENTO_REGISTRADO 'Ya existe un ticket con el número ' || NEW.PREF_PRE || NEW.TICKET_NUMERO; BEGIN /* Actualice el contador del prefijo */ SELECT PREF_ACTUAL, PREF_AUTO FROM PREFIJOS WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE INTO :NUMERO, :AUTOM; /* ACTUALICE EL NUMERO EN ENCABEZADO */ IF (AUTOM = 'S') THEN BEGIN NEW.TICKET_NUMERO = NUMERO; NUMERO = CAST((CAST(NUMERO AS INTEGER) + 1) AS CHAR(8)); UPDATE PREFIJOS SET PREF_ACTUAL = :NUMERO WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE; END ELSE BEGIN IF (CAST(NUMERO AS INTEGER) = CAST(NEW.TICKET_NUMERO AS INTEGER)) THEN UPDATE PREFIJOS SET PREF_ACTUAL = CAST((CAST(PREF_ACTUAL AS INTEGER) + 1) AS CHAR(8)) WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE; END EXECUTE PROCEDURE COMPLETA_CEROS8(NEW.TICKET_NUMERO) RETURNING_VALUES (NEW.TICKET_NUMERO); END END ^ SET TERM ; ^ INSERT INTO CONFIGURACION (CONF_MODULO, CONF_CATEGORIA, CONF_PROPIEDAD, CONF_VALOR, CONF_POSIBLES, CONF_AYUDA) VALUES ('CRM', 'GENERAL', 'ENVIAR CORREO AL REGISTRAR TICKETS O AL CERRAR TICKETS', 'SI', 'NO,SI', 'Indique si desea enviar un correo por el ticket registrado.'); CREATE SEQUENCE GEN_REPORT_NOTAS_ID; ALTER SEQUENCE GEN_REPORT_NOTAS_ID RESTART WITH 0; /******************************************************************************/ /*** Generated by IBExpert 2018/02/06 19:52:40 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_REPORT_NOTAS_ID; CREATE TABLE REPORT_NOTAS ( NOTAS_ID INTEGER NOT NULL, TIDO_COD INTEGER NOT NULL, NOTAS_IDDOC INTEGER NOT NULL, NOTAS_FECHA DATE, NOTAS_RESPUESTA BLOB SUB_TYPE 1 SEGMENT SIZE 80, NOTAS_ARCHIVO BLOB SUB_TYPE 0 SEGMENT SIZE 80, NOTAS_USUARIO VARCHAR(10) ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_NOTAS ADD CONSTRAINT PK_REPORT_NOTAS PRIMARY KEY (NOTAS_ID); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: REPORT_NOTAS_BI */ CREATE OR ALTER TRIGGER REPORT_NOTAS_BI FOR REPORT_NOTAS ACTIVE BEFORE INSERT POSITION 0 as begin if (new.notas_id is null) then new.notas_id = gen_id(gen_report_notas_id,1); end ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ ALTER TABLE REPORT_TICKETS ADD TICKETS_USUARIO VARCHAR(10); /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; CREATE GENERATOR GEN_REPORT_TICKETS_ID; SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_TICKETS_BI FOR REPORT_TICKETS ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE NUMERO VARCHAR(8); DECLARE VARIABLE AUTOM CHAR(1); DECLARE VARIABLE EXISTE CHAR(1); DECLARE VARIABLE ESTADO CHAR(1); BEGIN /*GENERADOR SI EL CAMPO ID IS NULL*/ IF (NEW.TICKET_ID IS NULL) THEN NEW.TICKET_ID = GEN_ID(GEN_REPORT_TICKETS_ID,1); /* VALIDE EL ESTADO DEL CLIENTE */ BEGIN SELECT C.CLIE_ESTADO FROM CLIENTES C WHERE TERC_NIT = NEW.TERC_NIT INTO :ESTADO; IF (ESTADO <> 'A') THEN EXCEPTION CLIENTE_INACTIVO 'El cliente ' || NEW.TERC_NIT || ' esta inactivo o en No vender.'; END /* Verifique que el numero no existe previamente */ EXISTE = 'N'; SELECT 'S' FROM REPORT_TICKETS WHERE PREF_PRE = NEW.PREF_PRE AND TICKET_NUMERO = NEW.TICKET_NUMERO INTO :EXISTE; IF (EXISTE = 'S') THEN EXCEPTION DOCUMENTO_REGISTRADO 'Ya existe un ticket con el número ' || NEW.PREF_PRE || NEW.TICKET_NUMERO; BEGIN /* Actualice el contador del prefijo */ SELECT PREF_ACTUAL, PREF_AUTO FROM PREFIJOS WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE INTO :NUMERO, :AUTOM; /* ACTUALICE EL NUMERO EN ENCABEZADO */ IF (AUTOM = 'S') THEN BEGIN NEW.TICKET_NUMERO = NUMERO; NUMERO = CAST((CAST(NUMERO AS INTEGER) + 1) AS CHAR(8)); UPDATE PREFIJOS SET PREF_ACTUAL = :NUMERO WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE; END ELSE BEGIN IF (CAST(NUMERO AS INTEGER) = CAST(NEW.TICKET_NUMERO AS INTEGER)) THEN UPDATE PREFIJOS SET PREF_ACTUAL = CAST((CAST(PREF_ACTUAL AS INTEGER) + 1) AS CHAR(8)) WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE; END EXECUTE PROCEDURE COMPLETA_CEROS8(NEW.TICKET_NUMERO) RETURNING_VALUES (NEW.TICKET_NUMERO); END END ^ SET TERM ; ^ ALTER TABLE REPORT_TICKETS ALTER TICKETS_USUARIO TO TICKET_USUARIO; ALTER TABLE REPORT_TICKETS ADD TICKET_ANULADO BOOLEANO DEFAULT 'N' ; /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; CREATE GENERATOR GEN_REPORT_TICKETS_ID; SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_TICKETS_BI FOR REPORT_TICKETS ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE NUMERO VARCHAR(8); DECLARE VARIABLE AUTOM CHAR(1); DECLARE VARIABLE EXISTE CHAR(1); DECLARE VARIABLE ESTADO CHAR(1); BEGIN /*GENERADOR SI EL CAMPO ID IS NULL*/ IF (NEW.TICKET_ID IS NULL) THEN NEW.TICKET_ID = GEN_ID(GEN_REPORT_TICKETS_ID,1); /*VACIO USUARIO*/ IF (NEW.TICKET_USUARIO IS NULL) THEN NEW.TICKET_USUARIO = USER; /* VALIDE EL ESTADO DEL CLIENTE */ BEGIN SELECT C.CLIE_ESTADO FROM CLIENTES C WHERE TERC_NIT = NEW.TERC_NIT INTO :ESTADO; IF (ESTADO <> 'A') THEN EXCEPTION CLIENTE_INACTIVO 'El cliente ' || NEW.TERC_NIT || ' esta inactivo o en No vender.'; END /* Verifique que el numero no existe previamente */ EXISTE = 'N'; SELECT 'S' FROM REPORT_TICKETS WHERE PREF_PRE = NEW.PREF_PRE AND TICKET_NUMERO = NEW.TICKET_NUMERO INTO :EXISTE; IF (EXISTE = 'S') THEN EXCEPTION DOCUMENTO_REGISTRADO 'Ya existe un ticket con el número ' || NEW.PREF_PRE || NEW.TICKET_NUMERO; BEGIN /* Actualice el contador del prefijo */ SELECT PREF_ACTUAL, PREF_AUTO FROM PREFIJOS WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE INTO :NUMERO, :AUTOM; /* ACTUALICE EL NUMERO EN ENCABEZADO */ IF (AUTOM = 'S') THEN BEGIN NEW.TICKET_NUMERO = NUMERO; NUMERO = CAST((CAST(NUMERO AS INTEGER) + 1) AS CHAR(8)); UPDATE PREFIJOS SET PREF_ACTUAL = :NUMERO WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE; END ELSE BEGIN IF (CAST(NUMERO AS INTEGER) = CAST(NEW.TICKET_NUMERO AS INTEGER)) THEN UPDATE PREFIJOS SET PREF_ACTUAL = CAST((CAST(PREF_ACTUAL AS INTEGER) + 1) AS CHAR(8)) WHERE TIDO_COD = 1000 AND PREF_PRE = NEW.PREF_PRE; END EXECUTE PROCEDURE COMPLETA_CEROS8(NEW.TICKET_NUMERO) RETURNING_VALUES (NEW.TICKET_NUMERO); END END ^ SET TERM ; ^ --2018/02/12 ALTER TABLE REPORT_NOTAS ADD TERC_NIT T_NIT; ALTER TABLE REPORT_NOTAS ADD CLSU_COD VARCHAR(10); SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_NOTAS_BI FOR REPORT_NOTAS ACTIVE BEFORE INSERT POSITION 0 as begin if (new.notas_id is null) then new.notas_id = gen_id(gen_report_notas_id,1); if (NEW.notas_usuario IS NULL) then NEW.notas_usuario = USER; if (NEW.NOTAS_FECHA IS NULL) then NEW.NOTAS_FECHA = cast('now' as DATE); end ^ SET TERM ; ^ /******************************************************************************/ /*** Generated by IBExpert 2018/02/20 18:38:26 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE REPORT_LOGO ( LOGO_ID INTEGER NOT NULL, LOGO_IMAGEN BLOB SUB_TYPE 0 SEGMENT SIZE 80 ); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ SET TERM ^ ; CREATE OR ALTER PROCEDURE REPORT_NOTAS_IMPR ( ID INTEGER) RETURNS ( FECHA DATE, RESPUESTA BLOB SUB_TYPE 1 SEGMENT SIZE 80, USUARIO VARCHAR(10), NIT VARCHAR(20), SUCURSAL VARCHAR(10), NOMBRE VARCHAR(60), LOGO BLOB SUB_TYPE 0 SEGMENT SIZE 80) AS begin FOR SELECT NOTAS_FECHA, NOTAS_RESPUESTA, NOTAS_USUARIO, TERC_NIT, CLSU_COD FROM REPORT_NOTAS WHERE NOTAS_IDDOC = :ID INTO :FECHA, :RESPUESTA, :USUARIO, :NIT, :SUCURSAL DO BEGIN SELECT TERC_NOM FROM TERCEROS T WHERE TERC_NIT = :NIT INTO :NOMBRE; SELECT LOGO_IMAGEN FROM REPORT_LOGO INTO :LOGO; suspend; END end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON REPORT_NOTAS TO PROCEDURE REPORT_NOTAS_IMPR; GRANT SELECT ON TERCEROS TO PROCEDURE REPORT_NOTAS_IMPR; GRANT SELECT ON REPORT_LOGO TO PROCEDURE REPORT_NOTAS_IMPR; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_NOTAS_IMPR TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_NOTAS_IMPR TO SYSDBA; SET TERM ^ ; CREATE OR ALTER PROCEDURE REPORT_BUSCADOC ( PREFIJO VARCHAR(4), NUMERO VARCHAR(8), FECHA DATE) RETURNS ( T_ID INTEGER, T_PREF VARCHAR(4), T_NUMERO VARCHAR(8), T_NIT VARCHAR(20), T_PRIORIDAD CHAR(5), T_FECHA DATE, T_NOMBRE VARCHAR(60), T_SUCURSAL VARCHAR(10), T_CONTACTO VARCHAR(60), T_TELEFONO VARCHAR(40), T_EMAIL VARCHAR(100), T_SUCESO VARCHAR(15), T_AREA VARCHAR(60), T_TEMA VARCHAR(60), T_OBSERVACION VARCHAR(60), T_DESCRIPCION BLOB SUB_TYPE 1 SEGMENT SIZE 80, T_RESPUESTA VARCHAR(60), T_ASIGNAR VARCHAR(60), T_ESTADO CHAR(1), T_USUARIO VARCHAR(10), T_ANULADO CHAR(1)) AS BEGIN FOR SELECT TICKET_ID, PREF_PRE, TICKET_NUMERO, TERC_NIT, TICKET_PRIORIDAD, TICKET_FECHA, TICKET_NOMBRE, TICKET_SUCURSAL, TICKET_CONTACTO, TICKET_TELEFONO, TICKET_EMAIL, TICKET_SUCESO, TICKET_AREA, TICKET_TEMA, TICKET_OBSERVACION, TICKET_DESCRIPCION, TICKET_RESPUESTA, TICKET_ASIGNAR, TICKET_ESTADO, TICKET_USUARIO, TICKET_ANULADO FROM REPORT_TICKETS WHERE PREF_PRE = :PREFIJO AND TICKET_NUMERO = :NUMERO AND TICKET_FECHA = :FECHA INTO :T_ID, T_PREF, :T_NUMERO, :T_NIT, :T_PRIORIDAD, :T_FECHA, :T_NOMBRE, :T_SUCURSAL, :T_CONTACTO, :T_TELEFONO, :T_EMAIL, :T_SUCESO, :T_AREA, :T_TEMA, :T_OBSERVACION, :T_DESCRIPCION,:T_RESPUESTA,:T_ASIGNAR, :T_ESTADO, :T_USUARIO, :T_ANULADO DO BEGIN SUSPEND; END END^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON REPORT_TICKETS TO PROCEDURE REPORT_BUSCADOC; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_BUSCADOC TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_BUSCADOC TO SYSDBA; SET TERM ^ ; CREATE OR ALTER PROCEDURE CONSULTA_USUARIO_CORREO ( NOMBRE VARCHAR(10)) RETURNS ( MAIL VARCHAR(100), HOST VARCHAR(100), CONTRASENA VARCHAR(40)) AS begin FOR SELECT USER_EMAIL, USER_MAILSERVER, USER_MAILPASS FROM USUARIO WHERE USER_COD = :NOMBRE INTO :MAIL , :HOST, :CONTRASENA DO BEGIN suspend; END end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON USUARIO TO PROCEDURE CONSULTA_USUARIO_CORREO; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE CONSULTA_USUARIO_CORREO TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE CONSULTA_USUARIO_CORREO TO SYSDBA; /******************************************************************************/ /*** Generated by IBExpert 2018/02/28 15:23:41 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_REPORT_FIRMA_ID; CREATE TABLE REPORT_FIRMA ( ID_FIRMA INTEGER NOT NULL, AREA_FIRMA VARCHAR(60), CARGO_FIRMA VARCHAR(60), DIRECCION_FIRMA VARCHAR(60), TELEFONO_FIRMA VARCHAR(60), FAX_FIRMA VARCHAR(60), CIUDAD_FIRMA VARCHAR(60), PAGINA_FIRMA VARCHAR(80), LOGO_FIRMA BLOB SUB_TYPE 0 SEGMENT SIZE 80, USUARIO_FIRMA VARCHAR(10) ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_FIRMA ADD CONSTRAINT PK_REPORT_FIRMA PRIMARY KEY (ID_FIRMA); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: REPORT_FIRMA_BI */ CREATE OR ALTER TRIGGER REPORT_FIRMA_BI FOR REPORT_FIRMA ACTIVE BEFORE INSERT POSITION 0 as begin if (new.id_firma is null) then new.id_firma = gen_id(gen_report_firma_id,1); end ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /*2018/04/06*/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER PREFIJOS_BU FOR PREFIJOS ACTIVE BEFORE UPDATE POSITION 0 AS BEGIN if (new.sucu_id is null) then new.sucu_id = 0; if (new.tido_cod = 31 ) then EXECUTE PROCEDURE completa_ceros8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); if (new.tido_cod = 1000) then EXECUTE PROCEDURE completa_ceros8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); else EXECUTE PROCEDURE COMPLETA_CEROS (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); END ^ SET TERM ; ^ /* Privileges of triggers */ GRANT UPDATE, REFERENCES ON PREFIJOS TO TRIGGER PREFIJOS_BU; GRANT EXECUTE ON PROCEDURE COMPLETA_CEROS TO TRIGGER PREFIJOS_BU; DROP TRIGGER PREFIJOS_RU; INSERT INTO CONFIGURACION (CONF_MODULO, CONF_CATEGORIA, CONF_PROPIEDAD, CONF_VALOR, CONF_POSIBLES, CONF_AYUDA) VALUES ('FACTURACION', 'GENERAL', 'COLOR FINAL VENTANA PRINCIPAL REPORT', '255,51,51', '0x00000-0xFFFFFF', 'Indique el color en formato RGB que quiere de fondo en la ventana principal del módulo'); INSERT INTO CONFIGURACION (CONF_MODULO, CONF_CATEGORIA, CONF_PROPIEDAD, CONF_VALOR, CONF_POSIBLES, CONF_AYUDA) VALUES ('FACTURACION', 'GENERAL', 'COLOR VENTANA PRINCIPAL REPORT', '102,0,0', '0x00000-0xFFFFFF', 'Indique el color en formato RGB que quiere de fondo en la ventana principal del módulo'); /******************************************************************************/ /*** Generated by IBExpert 2018/04/06 15:54:44 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE REPORT_CONFIGURACION ( MODULO_REPORT VARCHAR(20) NOT NULL, OPCION_REPORT VARCHAR(20) NOT NULL, NOMBRE_REPORT VARCHAR(100) NOT NULL, VALOR_REPORT VARCHAR(100), POSIBLE_REPORT VARCHAR(250), DESCRIPCION_REPORT VARCHAR(250) ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_CONFIGURACION ADD CONSTRAINT PK_REPORT_CONFIGURACION PRIMARY KEY (MODULO_REPORT, OPCION_REPORT, NOMBRE_REPORT); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_CONFIGURACION TO PUBLIC; SET TERM ^ ; CREATE OR ALTER PROCEDURE LEE_CONFIGURACION_REPORT ( MODULO VARCHAR(50), CATEGORIA VARCHAR(50), PROPIEDAD VARCHAR(255)) RETURNS ( VALOR VARCHAR(255)) AS BEGIN SELECT VALOR_REPORT FROM REPORT_CONFIGURACION WHERE MODULO_REPORT = :MODULO AND OPCION_REPORT = :CATEGORIA AND NOMBRE_REPORT = :PROPIEDAD INTO :VALOR; if (VALOR IS NULL) then exception configuracion_no_encontrada 'configuracion ' || :modulo || '-' || :categoria || '-' || :propiedad; SUSPEND; END^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON REPORT_CONFIGURACION TO PROCEDURE LEE_CONFIGURACION_REPORT; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE LEE_CONFIGURACION_REPORT TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE LEE_CONFIGURACION_REPORT TO SYSDBA; SET TERM ^ ; CREATE OR ALTER PROCEDURE REPORT_CRONOLOGICO ( FECINI DATE, FECFIN DATE, PDESDE VARCHAR(4), PHASTA VARCHAR(4), ELIMINADOS CHAR(1), CERRADOS CHAR(1)) RETURNS ( ID INTEGER, PRE VARCHAR(4), NUMERO VARCHAR(8), NIT VARCHAR(20), PRIORIDAD CHAR(5), FECHA DATE, NOMBRE VARCHAR(60), SUCURSAL VARCHAR(10), CONTACTO VARCHAR(60), TELEFONO VARCHAR(40), EMAIL VARCHAR(100), SUCESO VARCHAR(15), AREA VARCHAR(60), TEMA VARCHAR(60), OBSERVACION VARCHAR(60), DESCRIPCION BLOB SUB_TYPE 1 SEGMENT SIZE 80, RESPUESTA VARCHAR(60), ASIGNAR VARCHAR(60), ESTADO CHAR(1), USUARIO VARCHAR(10), ANULADO CHAR(1)) AS BEGIN IF (PDESDE IS NULL AND PHASTA IS NULL) THEN PDESDE='0'; PHASTA='ZZ'; FOR SELECT TICKET_ID, PREF_PRE, TICKET_NUMERO, TERC_NIT, TICKET_PRIORIDAD, TICKET_FECHA, TICKET_NOMBRE, TICKET_SUCURSAL, TICKET_CONTACTO, TICKET_TELEFONO, TICKET_EMAIL, TICKET_SUCESO, TICKET_AREA, TICKET_TEMA, TICKET_OBSERVACION, TICKET_DESCRIPCION, TICKET_RESPUESTA, TICKET_ASIGNAR, TICKET_ESTADO, TICKET_USUARIO, TICKET_ANULADO FROM REPORT_TICKETS WHERE TICKET_FECHA >= :FECINI AND TICKET_FECHA <= :FECFIN AND PREF_PRE >=:PDESDE AND PREF_PRE <=:PHASTA AND ((TICKET_ANULADO = :ELIMINADOS) or (TICKET_ANULADO <> '')) INTO :ID, :PRE, :NUMERO, :NIT, :PRIORIDAD, :FECHA, :NOMBRE,:SUCURSAL, :CONTACTO, :TELEFONO, :EMAIL, :SUCESO, :AREA, :TEMA, :OBSERVACION, :DESCRIPCION, :RESPUESTA, :ASIGNAR, :ESTADO,:USUARIO, :ANULADO DO BEGIN SUSPEND; END END^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON REPORT_TICKETS TO PROCEDURE REPORT_CRONOLOGICO; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_CRONOLOGICO TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_CRONOLOGICO TO SYSDBA; /******************************************************************************/ /*** Generated by IBExpert 2018/04/06 18:35:14 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_ENVIOS_FO; CREATE TABLE REPORT_ENVIOS_FALTANTES ( ITEM_ENVIORP INTEGER NOT NULL, IDDOC_ENVIORP INTEGER, PREF_PRE VARCHAR(4), NUMERO_ENVIORP VARCHAR(6), TERC_NIT NIT /* NIT = VARCHAR(20) */, CLIENTE_ENVIORP NOMBRE /* NOMBRE = VARCHAR(60) */, ESTADO_ENVIORP T_BOOLEANO /* T_BOOLEANO = CHAR(1) check (value in ('S','N')) */, TIPODOC_ENVIORP VARCHAR(10), IDCRUCE_ENVIORP INTEGER, USUARIO_ENVIORP VARCHAR(10), NUMERODOC_ENVIORP VARCHAR(12) ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_ENVIOS_FALTANTES ADD CONSTRAINT PK_REPORT_ENVIOS_FALTANTES PRIMARY KEY (ITEM_ENVIORP); /******************************************************************************/ /*** Foreign Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_ENVIOS_FALTANTES ADD CONSTRAINT FK_REPORT_ENVIOS_FALTANTES FOREIGN KEY (TERC_NIT) REFERENCES CLIENTES (TERC_NIT); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: REPORT_ENVIOS_FALTANTES_BI0 */ CREATE OR ALTER TRIGGER REPORT_ENVIOS_FALTANTES_BI0 FOR REPORT_ENVIOS_FALTANTES ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ITEM_ENVIORP IS NULL) THEN NEW.ITEM_ENVIORP = GEN_ID(GEN_ENVIOS_FO,1); END ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_ENVIOS_FALTANTES TO PUBLIC; /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER FACREPORT_AI FOR FACTURAS ACTIVE AFTER INSERT POSITION 0 AS DECLARE VARIABLE IDFAC INTEGER; DECLARE VARIABLE IDPED INTEGER; DECLARE VARIABLE CRUCE INTEGER; begin SELECT FACT_CONSOLIDA FROM FACTURAS WHERE FACT_ANULADO ='N' AND FACT_CONSOLIDA = NEW.FACT_consolida INTO :IDFAC; FOR SELECT CEFA_IDORI, CEFA_ID FROM CONSOLIDE_FALTANTES WHERE CEFA_ID=:IDFAC INTO :IDPED, :CRUCE DO BEGIN INSERT INTO REPORT_ENVIOS_FALTANTES (IDDOC_ENVIORP, PREF_PRE, NUMERO_ENVIORP, TERC_NIT,CLIENTE_ENVIORP,ESTADO_ENVIORP, TIPODOC_ENVIORP, IDCRUCE_ENVIORP, USUARIO_ENVIORP, NUMERODOC_ENVIORP) SELECT P.pedi_id, P.pref_pre, P.pedi_numero, P.terc_nit,p.pedi_nomterc ,'N', 'F', :CRUCE, USER, NEW.pref_pre||NEW.fact_numero FROM PEDIDOS P WHERE P.tido_cod = 34 AND P.pedi_id = :IDPED; END end ^ SET TERM ; ^ INSERT INTO REPORT_CONFIGURACION (MODULO_REPORT, OPCION_REPORT, NOMBRE_REPORT, VALOR_REPORT, POSIBLE_REPORT, DESCRIPCION_REPORT) VALUES ('TICKETS', 'GENERAL', 'TIPO DE GRADADO PARA EL MODULO', '8', '1,2,3,4,5,6,7,8,9', 'Selecciona un degradado para los colores del ventana 1(Horizontal), 2(Verticall),3(Esquina_1),4(Esquina_2),5(Esquina_3),6(Esquina_4),7(Circular),8(Central),9(Agua).'); INSERT INTO REPORT_CONFIGURACION (MODULO_REPORT, OPCION_REPORT, NOMBRE_REPORT, VALOR_REPORT, POSIBLE_REPORT, DESCRIPCION_REPORT) VALUES ('VENTAS', 'GENERAL', 'LLEVAR CONTROL DE FALTANTES POR FACTURAS', 'SI', 'SI,NO', 'SI:Permite insertar y controlar faltantes por facturas NO:No lleva control.'); INSERT INTO REPORT_CONFIGURACION (MODULO_REPORT, OPCION_REPORT, NOMBRE_REPORT, VALOR_REPORT, POSIBLE_REPORT, DESCRIPCION_REPORT) VALUES ('VENTAS', 'GENERAL', 'LLEVAR CONTROL DE FALTANTES POR REMISIONES', 'SI', 'SI,NO', 'SI:Permite insertar y controlar faltantes por remision NO:No lleva control.'); INSERT INTO REPORT_CONFIGURACION (MODULO_REPORT, OPCION_REPORT, NOMBRE_REPORT, VALOR_REPORT, POSIBLE_REPORT, DESCRIPCION_REPORT) VALUES ('VENTAS', 'GENERAL', 'TIPO DE GRADADO PARA EL MODULO', '2', '1,2,3,4,5,6,7,8,9', 'Selecciona un degradado para los colores del ventana 1(Horizontal), 2(Verticall),3(Esquina_1),4(Esquina_2),5(Esquina_3),6(Esquina_4),7(Circular),8(Central),9(Agua).'); /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER FACREPORT_AI FOR FACTURAS ACTIVE AFTER INSERT POSITION 0 AS DECLARE VARIABLE IDFAC INTEGER; DECLARE VARIABLE IDPED INTEGER; DECLARE VARIABLE CRUCE INTEGER; DECLARE VARIABLE RESP CHAR(2); begin EXECUTE PROCEDURE LEE_CONFIGURACION_REPORT('VENTAS', 'GENERAL', 'LLEVAR CONTROL DE FALTANTES POR FACTURAS') returning_values (:RESP); IF (RESP ='SI') THEN SELECT FACT_CONSOLIDA FROM FACTURAS WHERE FACT_ANULADO ='N' AND FACT_CONSOLIDA = NEW.FACT_consolida INTO :IDFAC; FOR SELECT CEFA_IDORI, CEFA_ID FROM CONSOLIDE_FALTANTES WHERE CEFA_ID=:IDFAC INTO :IDPED, :CRUCE DO BEGIN INSERT INTO REPORT_ENVIOS_FALTANTES (IDDOC_ENVIORP, PREF_PRE, NUMERO_ENVIORP, TERC_NIT,CLIENTE_ENVIORP,ESTADO_ENVIORP, TIPODOC_ENVIORP, IDCRUCE_ENVIORP, USUARIO_ENVIORP, NUMERODOC_ENVIORP) SELECT P.pedi_id, P.pref_pre, P.pedi_numero, P.terc_nit,p.pedi_nomterc ,'N', 'F', :CRUCE, USER, NEW.pref_pre||NEW.fact_numero FROM PEDIDOS P WHERE P.tido_cod = 34 AND P.pedi_id = :IDPED; END end ^ SET TERM ; ^ SET TERM ^ ; CREATE OR ALTER TRIGGER REMREPORT_AI FOR REMISIONES_VENTA ACTIVE AFTER INSERT POSITION 0 AS DECLARE VARIABLE IDREV INTEGER; DECLARE VARIABLE IDPED INTEGER; DECLARE VARIABLE CRUCE INTEGER; DECLARE VARIABLE RESP CHAR(2); begin EXECUTE PROCEDURE LEE_CONFIGURACION_REPORT('VENTAS', 'GENERAL', 'LLEVAR CONTROL DE FALTANTES POR REMISIONES') returning_values (:RESP); IF (RESP ='SI') THEN SELECT REVT_CONSOLIDA FROM REMISIONES_VENTA WHERE REVT_ANULADO ='N' AND REVT_CONSOLIDA = NEW.revt_consolida INTO :IDREV; FOR SELECT CEFA_IDORI, CEFA_ID FROM CONSOLIDE_FALTANTES WHERE CEFA_ID=:IDREV INTO :IDPED, :CRUCE DO BEGIN INSERT INTO REPORT_ENVIOS_FALTANTES (IDDOC_ENVIORP, PREF_PRE, NUMERO_ENVIORP, TERC_NIT,CLIENTE_ENVIORP,ESTADO_ENVIORP, TIPODOC_ENVIORP, IDCRUCE_ENVIORP, USUARIO_ENVIORP, NUMERODOC_ENVIORP) SELECT P.pedi_id, P.pref_pre, P.pedi_numero, P.terc_nit,p.pedi_nomterc ,'N', 'R', :CRUCE, USER, NEW.pref_pre||NEW.revt_numero FROM PEDIDOS P WHERE P.tido_cod = 34 AND P.pedi_id = :IDPED; END end ^ SET TERM ; ^ /******************************************************************************/ /*** Generated by IBExpert 2018/04/08 19:06:56 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE SEQUENCE GEN_REPORT_BANDEJA_ID; ALTER SEQUENCE GEN_REPORT_BANDEJA_ID RESTART WITH 0; CREATE TABLE REPORT_BANDEJA ( ID_BANDEJA INTEGER NOT NULL, DE_BANDEJA VARCHAR(255), PARA_BANDEJA VARCHAR(255), ASUNTO_BANDEJA VARCHAR(255), DESCRIPCION_BANDEJA BLOB SUB_TYPE 1 SEGMENT SIZE 80, HORA_BANDEJA TIME, FECHA_BANDEJA DATE, TIPO_BANDEJA BOOLEANO /* BOOLEANO = CHAR(1) */ ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE REPORT_BANDEJA ADD CONSTRAINT PK_REPORT_BANDEJA PRIMARY KEY (ID_BANDEJA); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_BANDEJA TO PUBLIC; ALTER TABLE REPORT_BANDEJA ADD USUARIO_BANDEJA VARCHAR(10); /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; CREATE GENERATOR GEN_REPORT_BANDEJA_ID; SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_BANDEJA_BI0 FOR REPORT_BANDEJA ACTIVE BEFORE INSERT POSITION 0 AS begin /*GENERADOR SI EL CAMPO ID IS NULL*/ IF (NEW.id_bandeja IS NULL) THEN NEW.id_bandeja = GEN_ID(gen_report_bandeja_id,1); NEW.fecha_bandeja = CAST ('NOW' AS DATE); NEW.hora_bandeja = CAST ('NOW' AS TIME); NEW.usuario_bandeja = USER; end ^ SET TERM ; ^ SET TERM ^ ; CREATE OR ALTER PROCEDURE REPORT_CRONOLOGICO ( FECINI DATE, FECFIN DATE, PDESDE VARCHAR(4), PHASTA VARCHAR(4), NITINI VARCHAR(20), NITFIN VARCHAR(20)) RETURNS ( ID INTEGER, PRE VARCHAR(4), NUMERO VARCHAR(8), NIT VARCHAR(20), PRIORIDAD CHAR(5), FECHA DATE, NOMBRE VARCHAR(60), SUCURSAL VARCHAR(10), CONTACTO VARCHAR(60), TELEFONO VARCHAR(40), EMAIL VARCHAR(100), SUCESO VARCHAR(15), AREA VARCHAR(60), TEMA VARCHAR(60), OBSERVACION VARCHAR(60), DESCRIPCION BLOB SUB_TYPE 1 SEGMENT SIZE 80, RESPUESTA VARCHAR(60), ASIGNAR VARCHAR(60), ESTADO CHAR(1), USUARIO VARCHAR(10), ANULADO CHAR(1), LOGO BLOB SUB_TYPE 0 SEGMENT SIZE 80) AS begin SELECT LOGO_IMAGEN FROM REPORT_LOGO INTO :LOGO; FOR SELECT TICKET_ID, PREF_PRE, TICKET_NUMERO, TERC_NIT, TICKET_PRIORIDAD, TICKET_FECHA, TICKET_NOMBRE, TICKET_SUCURSAL, TICKET_CONTACTO, TICKET_TELEFONO, TICKET_EMAIL, TICKET_SUCESO, TICKET_AREA, TICKET_TEMA, TICKET_OBSERVACION, TICKET_DESCRIPCION, TICKET_RESPUESTA, TICKET_ASIGNAR, TICKET_ESTADO, TICKET_USUARIO, TICKET_ANULADO FROM REPORT_TICKETS WHERE TICKET_FECHA >= :FECINI AND TICKET_FECHA <= :FECFIN AND PREF_PRE >=:PDESDE AND PREF_PRE <=:PHASTA AND TERC_NIT>=:NITINI AND TERC_NIT<=:NITFIN INTO :ID, :PRE, :NUMERO, :NIT, :PRIORIDAD, :FECHA, :NOMBRE,:SUCURSAL, :CONTACTO, :TELEFONO, :EMAIL, :SUCESO, :AREA, :TEMA, :OBSERVACION, :DESCRIPCION, :RESPUESTA, :ASIGNAR, :ESTADO,:USUARIO, :ANULADO DO BEGIN SUSPEND; END END^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON REPORT_LOGO TO PROCEDURE REPORT_CRONOLOGICO; GRANT SELECT ON REPORT_TICKETS TO PROCEDURE REPORT_CRONOLOGICO; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_CRONOLOGICO TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_CRONOLOGICO TO SYSDBA; /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER PREFIJOS_BI FOR PREFIJOS ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (new.sucu_id is null) then new.sucu_id = 0; if (new.pref_dec1818 is null) then new.pref_dec1818 = 'N'; if (NEW.tido_cod = 31 or NEW.tido_cod = 1000 ) then EXECUTE PROCEDURE COMPLETA_CEROS8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); else EXECUTE PROCEDURE COMPLETA_CEROS (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); END ^ SET TERM ; ^ /* Privileges of triggers */ GRANT UPDATE, REFERENCES ON PREFIJOS TO TRIGGER PREFIJOS_BI; GRANT EXECUTE ON PROCEDURE COMPLETA_CEROS TO TRIGGER PREFIJOS_BI; DROP TRIGGER PREFIJOS_RI; /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER PREFIJOS_BU FOR PREFIJOS ACTIVE BEFORE UPDATE POSITION 0 AS BEGIN if (new.sucu_id is null) then new.sucu_id = 0; if (new.tido_cod = 31 ) then EXECUTE PROCEDURE completa_ceros8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); if (new.tido_cod = 1000) then EXECUTE PROCEDURE completa_ceros8 (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); else EXECUTE PROCEDURE COMPLETA_CEROS (NEW.PREF_ACTUAL) returning_values (NEW.PREF_ACTUAL); END ^ SET TERM ; ^ /* Privileges of triggers */ GRANT UPDATE, REFERENCES ON PREFIJOS TO TRIGGER PREFIJOS_BU; GRANT EXECUTE ON PROCEDURE COMPLETA_CEROS TO TRIGGER PREFIJOS_BU; ALTER TABLE REPORT_ENVIOS_FALTANTES ADD FECHA_ENVIORP DATE; /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; CREATE GENERATOR GEN_ENVIOS_FO; SET TERM ^ ; CREATE OR ALTER TRIGGER REPORT_ENVIOS_FALTANTES_BI0 FOR REPORT_ENVIOS_FALTANTES ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ITEM_ENVIORP IS NULL) THEN NEW.ITEM_ENVIORP = GEN_ID(GEN_ENVIOS_FO,1); if (NEW.FECHA_ENVIORP IS NULL) then NEW.fecha_enviorp = cast('now' as DATE); END ^ SET TERM ; ^ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Views ***/ /******************************************************************************/ /* View: ORBIDENTAL_TEMP */ CREATE OR ALTER VIEW REPORT_FALTANTES_TEMP( IDENTIFICACION, DOCUMENTO) AS select IDDOC_ENVIORP, TIPODOC_ENVIORP from REPORT_ENVIOS_FALTANTES where ESTADO_ENVIORP='N' ; /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /* Privileges of users */ GRANT ALL ON REPORT_FALTANTES_TEMP TO PUBLIC; SET TERM ^ ; create or alter procedure REPORT_PENDIENTES ( IDPED integer, DOC char(1)) returns ( CANT numeric(18,4)) AS DECLARE VARIABLE IDP INTEGER; DECLARE VARIABLE ITEM INTEGER; DECLARE VARIABLE UNIDAD VARCHAR(8); DECLARE VARIABLE FACTOR NUMERIC(9,4); DECLARE VARIABLE RESPUESTA CHAR(2); begin for select PEDE_ITEM from PEDIDOS_DETALLE where PEDI_ID = :IDPED ORDER by PEDE_ITEM into :ITEM do begin if (DOC = 'R') then execute procedure FALTANTE(34, 32, :IDPED, :ITEM) returning_values (:CANT, :UNIDAD, :FACTOR); if (DOC = 'F') then execute procedure FALTANTE(34, 31, :IDPED, :ITEM) returning_values (:CANT, :UNIDAD, :FACTOR); if (CANT =0) then RESPUESTA='OK'; suspend; end end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON PEDIDOS_DETALLE TO PROCEDURE REPORT_PENDIENTES; GRANT EXECUTE ON PROCEDURE FALTANTE TO PROCEDURE REPORT_PENDIENTES; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_PENDIENTES TO PROCEDURE REPORT_FALTANTES; GRANT EXECUTE ON PROCEDURE REPORT_PENDIENTES TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_PENDIENTES TO SYSDBA; SET TERM ^ ; CREATE OR ALTER PROCEDURE REPORT_FALTANTES RETURNS ( DATO INTEGER, TOTAL NUMERIC(18,4)) AS DECLARE VARIABLE ID INTEGER; DECLARE VARIABLE ITEM INTEGER; DECLARE VARIABLE DOC CHAR(1); BEGIN FOR SELECT IDENTIFICACION , DOCUMENTO FROM REPORT_FALTANTES_TEMP GROUP BY IDENTIFICACION , DOCUMENTO INTO :ID , :DOC DO BEGIN SELECT SUM(CANT) FROM REPORT_PENDIENTES(:ID,:DOC) INTO :TOTAL; IF (TOTAL =0) THEN UPDATE REPORT_ENVIOS_FALTANTES E SET ESTADO_ENVIORP = 'S' WHERE E.IDDOC_ENVIORP = :ID AND E.TIPODOC_ENVIORP = :DOC; END END^ SET TERM ; ^ /* FOLLOWING GRANT STATETEMENTS ARE GENERATED AUTOMATICALLY */ GRANT SELECT ON REPORT_FALTANTES_TEMP TO PROCEDURE REPORT_FALTANTES; GRANT EXECUTE ON PROCEDURE REPORT_PENDIENTES TO PROCEDURE REPORT_FALTANTES; GRANT SELECT,UPDATE ON REPORT_ENVIOS_FALTANTES TO PROCEDURE REPORT_FALTANTES; /* EXISTING PRIVILEGES ON THIS PROCEDURE */ GRANT EXECUTE ON PROCEDURE REPORT_FALTANTES TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_FALTANTES TO SYSDBA; SET TERM ^ ; create or alter procedure REPORT_ENVIOSF_CORREO ( IDPED integer) returns ( ARTI varchar(20), DES varchar(60), CANT numeric(18,4), UNIDAD varchar(8), BOD varchar(2), PRUNIT numeric(18,2), DTO numeric(9,2), TOTAL numeric(18,2), TERCERO varchar(20), MARCA varchar(30), FACTOR numeric(18,4)) AS DECLARE VARIABLE IDP INTEGER; DECLARE VARIABLE ITEM INTEGER; DECLARE VARIABLE DOC CHAR(1); begin FOR SELECT IDDOC_ENVIORP, TERC_NIT, TIPODOC_ENVIORP FROM REPORT_ENVIOS_FALTANTES WHERE IDDOC_ENVIORP = :IDPED AND ESTADO_ENVIORP = 'N' INTO :IDP, :TERCERO, DOC DO begin for select PEDE_ITEM, PEDE_CODBAR, PEDE_DESC, BODE_COD, PEDE_PRUNIT, PEDE_DTOPORC, PEDE_TOTAL from PEDIDOS_DETALLE where PEDI_ID = :IDP order by PEDE_ITEM into :ITEM, :ARTI, :DES, :BOD, :PRUNIT, :DTO, :TOTAL do begin if (DOC='R') then execute procedure faltante(34, 32, :IDP, :ITEM) returning_values (:CANT, :UNIDAD, :FACTOR); SELECT M.marc_nom FROM ARTICULO A , MARCAS M WHERE A.arti_cod = :ARTI AND A.marc_cod = M.marc_cod INTO :MARCA; if (DOC='F') then execute procedure faltante(34, 31, :IDP, :ITEM) returning_values (:CANT, :UNIDAD, :FACTOR); SELECT M.marc_nom FROM ARTICULO A , MARCAS M WHERE A.arti_cod = :ARTI AND A.marc_cod = M.marc_cod INTO :MARCA; if (CANT <>0) then suspend; end end end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON REPORT_ENVIOS_FALTANTES TO PROCEDURE ENVIOSF_ORBIDENTAL_CORREO; GRANT SELECT ON PEDIDOS_DETALLE TO PROCEDURE ENVIOSF_ORBIDENTAL_CORREO; GRANT EXECUTE ON PROCEDURE FALTANTE TO PROCEDURE ENVIOSF_ORBIDENTAL_CORREO; GRANT SELECT ON ARTICULO TO PROCEDURE ENVIOSF_ORBIDENTAL_CORREO; GRANT SELECT ON MARCAS TO PROCEDURE ENVIOSF_ORBIDENTAL_CORREO; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_ENVIOSF_CORREO TO PROCEDURE REPORT_RESULTADCORREO; GRANT EXECUTE ON PROCEDURE REPORT_ENVIOSF_CORREO TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_ENVIOSF_CORREO TO SYSDBA; SET TERM ^ ; create or alter procedure REPORT_RESULTADCORREO ( IDDOC integer) returns ( TOTAL integer) AS begin SELECT SUM(CANT) FROM REPORT_ENVIOSF_CORREO(:IDDOC) INTO TOTAL; if(TOTAL IS NULL) THEN TOTAL = 0; ELSE TOTAL = :TOTAL; suspend; end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT EXECUTE ON PROCEDURE REPORT_ENVIOSF_CORREO TO PROCEDURE REPORT_RESULTADCORREO; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REPORT_RESULTADCORREO TO "PUBLIC"; GRANT EXECUTE ON PROCEDURE REPORT_RESULTADCORREO TO SYSDBA;