SET SQL DIALECT 3;
CREATE DATABASE 'D:\Proyectos\Ventas2Libre\Base\Ventas2.FDB'
PAGE_SIZE 4096
DEFAULT CHARACTER SET NONE;
/* External Function declarations */
DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
VARCHAR(80) CHARACTER SET NONE, VARCHAR(80) CHARACTER SET NONE
RETURNS VARCHAR(255) CHARACTER SET NONE FREE_IT
ENTRY_POINT 'get_context' MODULE_NAME 'system_module';
DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
VARCHAR(80) CHARACTER SET NONE, VARCHAR(80) CHARACTER SET NONE, VARCHAR(255) CHARACTER SET NONE
RETURNS INTEGER BY VALUE
ENTRY_POINT 'set_context' MODULE_NAME 'system_module';
/* Domain definitions */
CREATE DOMAIN "ACTIVO_INACTIVO" AS CHAR(1) CHARACTER SET ISO8859_1
CHECK (VALUE = 'A' OR VALUE = 'I') NOT NULL;
CREATE DOMAIN "ESTADO_CIVIL" AS CHAR(1) CHARACTER SET ISO8859_1
CHECK (VALUE IN ('C', 'S', 'D', 'V', 'U') OR VALUE IS NULL);
CREATE DOMAIN "MASCULINO_FEMENINO" AS CHAR(1) CHARACTER SET ISO8859_1
CHECK (VALUE IN ('M','F') OR VALUE IS NULL);
CREATE DOMAIN "PORCENTAJE" AS NUMERIC(8, 4)
CHECK (VALUE >= 0 AND VALUE <= 100);
CREATE DOMAIN "SI_NO" AS CHAR(1)
CHECK (VALUE = 'S' OR VALUE = 'N') NOT NULL;
/* Table: APLICACIONES, Owner: SYSDBA */
CREATE TABLE "APLICACIONES"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"DESCRIPCION" VARCHAR(250) CHARACTER SET ISO8859_1 NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
CONSTRAINT "PK_APLICACIONES" PRIMARY KEY ("CLAVE")
);
/* Table: APLICACIONES_PERMISOS, Owner: SYSDBA */
CREATE TABLE "APLICACIONES_PERMISOS"
(
"CLAVE" SMALLINT NOT NULL,
"APLICACION" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"DESCRIPCION" VARCHAR(100) CHARACTER SET ISO8859_1 NOT NULL,
"NOMBRE_ACCION" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
"VENTANA" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
"ORDEN" SMALLINT NOT NULL,
"PERMISO" SMALLINT,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_APLICACIONES_PERMISOS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS"
(
"CLAVE" INTEGER NOT NULL,
"DESCRIPCION_CORTA" VARCHAR(25) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"ETIQUETA" VARCHAR(50) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"MODELO" VARCHAR(15),
"NOTAS" VARCHAR(250) CHARACTER SET ISO8859_1 COLLATE ES_ES,
"CATEGORIA" SMALLINT,
"DEPARTAMENTO" SMALLINT,
"CLASE" SMALLINT,
"IMPUESTO" SMALLINT NOT NULL,
"ESTATUS" ACTIVO_INACTIVO ,
"ES_A_GRANEL" SI_NO NOT NULL,
"ES_JUEGO" SI_NO NOT NULL,
"ES_INVENTARIABLE" SI_NO NOT NULL,
"SE_VENDE" SI_NO NOT NULL,
"SE_COMPRA" SI_NO NOT NULL,
"SE_PRODUCE" SI_NO ,
"UTILIZA_BASCULA" SI_NO NOT NULL,
"UNIDAD_MEDIDA" VARCHAR(15),
"TIEMPO_ELABORACION" SMALLINT,
"TIENE_NUMERO_SERIE" SI_NO NOT NULL,
"TIENE_LOTE" SI_NO NOT NULL,
"DIAS_CADUCIDAD" INTEGER,
"FOTO" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
"FECHA_FOTO" TIMESTAMP,
"CAMBIAR_PRECIO" SI_NO ,
"COSTO" NUMERIC(15,4),
"COSTO_FECHA" TIMESTAMP NOT NULL,
"COSTO_EMPLEADO" SMALLINT NOT NULL,
"UNIDAD_COMPRA" INTEGER,
"GENERAR_BOLETOS" SI_NO NOT NULL,
"TIENDA_EN_LINEA" SI_NO ,
"PESO" NUMERIC(8,4) NOT NULL,
"LARGO" NUMERIC(9,3) NOT NULL,
"ANCHO" NUMERIC(9,3) NOT NULL,
"ALTO" NUMERIC(9,3) NOT NULL,
"COSTO_AUTOMATICO" SI_NO ,
"ULTIMA_VENTA" INTEGER,
"ULTIMA_COMPRA" INTEGER,
"EMPLEADO_REGISTRO" SMALLINT NOT NULL,
"EMPLEADO_MODIFICACION" SMALLINT NOT NULL,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_ARTICULOS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_CARACTERISTICAS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_CARACTERISTICAS"
(
"CLAVE" INTEGER NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"CARACTERISTICA" INTEGER NOT NULL,
"DESCRIPCION" VARCHAR(60) NOT NULL,
CONSTRAINT "PK_ARTICULOS_CARACTERISTICA" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_CATEGORIAS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_CATEGORIAS"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"DESCRIPCION" VARCHAR(200) CHARACTER SET ISO8859_1 NOT NULL,
"EMPLEADO_REGISTRO" SMALLINT NOT NULL,
"EMPLEADO_MODIFICACION" SMALLINT NOT NULL,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_ARTICULOS_CATEGORIAS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_CODIGOS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_CODIGOS"
(
"CLAVE" INTEGER NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"CODIGO" VARCHAR(23) NOT NULL,
"TIPO" CHAR(1) NOT NULL,
"DESCRIPCION" VARCHAR(50) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
CONSTRAINT "PK_ARTICULOS_CODIGOS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_COSTOS_HISTORIAL, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_COSTOS_HISTORIAL"
(
"CLAVE" INTEGER NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"COSTO" NUMERIC(15,4) NOT NULL,
"EMPLEADO" INTEGER NOT NULL,
"FECHA" TIMESTAMP NOT NULL,
CONSTRAINT "PK_ARTICULOS_COSTOS_HISTORI" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_DEPARTAMENTOS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_DEPARTAMENTOS"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"EMPLEADO_REGISTRO" SMALLINT,
"EMPLEADO_MODIFICACION" SMALLINT,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_DEPARTAMENTOS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_JUEGOS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_JUEGOS"
(
"CLAVE" INTEGER NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"COMPONENTE" INTEGER NOT NULL,
"CANTIDAD" NUMERIC(10,4) NOT NULL,
"PRECIO" NUMERIC(10,4) NOT NULL,
CONSTRAINT "PK_ARTICULOS_JUEGOS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_PRECIOS, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_PRECIOS"
(
"CLAVE" INTEGER NOT NULL,
"LISTA_PRECIO" SMALLINT NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"PRECIO" NUMERIC(15,4) NOT NULL,
"EMPLEADO" SMALLINT NOT NULL,
"FECHA" TIMESTAMP NOT NULL,
"PRECIO_AUTOMATICO" SI_NO ,
CONSTRAINT "PK_ARTICULOS_PRECIOS" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_PRECIOS_HISTORIAL, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_PRECIOS_HISTORIAL"
(
"CLAVE" INTEGER NOT NULL,
"LISTA_PRECIO" SMALLINT NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"PRECIO" NUMERIC(15,4) NOT NULL,
"FECHA" TIMESTAMP NOT NULL,
"EMPLEADO" SMALLINT NOT NULL,
CONSTRAINT "PK_ARTICULOS_PRECIOS_HISTORIAL" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_PROVEEDORES, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_PROVEEDORES"
(
"CLAVE" INTEGER NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"PROVEEDOR" SMALLINT NOT NULL,
"TIEMPO_ENTREGA" SMALLINT NOT NULL,
"PREDETERMINADO" SI_NO ,
CONSTRAINT "PK_ARTICULOS_PROVEEDORES" PRIMARY KEY ("CLAVE")
);
/* Table: ARTICULOS_SIMILARES, Owner: SYSDBA */
CREATE TABLE "ARTICULOS_SIMILARES"
(
"CLAVE" INTEGER NOT NULL,
"ARTICULO" INTEGER NOT NULL,
"SIMILAR" INTEGER NOT NULL,
CONSTRAINT "PK_ARTICULOS_SIMILARES" PRIMARY KEY ("CLAVE")
);
/* Table: CLASES, Owner: SYSDBA */
CREATE TABLE "CLASES"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_CLASES" PRIMARY KEY ("CLAVE")
);
/* Table: CLASES_CARACTERISTICAS, Owner: SYSDBA */
CREATE TABLE "CLASES_CARACTERISTICAS"
(
"CLAVE" INTEGER NOT NULL,
"CLASE" SMALLINT NOT NULL,
"ORDEN" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
CONSTRAINT "PK_CLASES_CARACTERISTICAS" PRIMARY KEY ("CLAVE")
);
/* Table: CONTRATOS, Owner: SYSDBA */
CREATE TABLE "CONTRATOS"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) NOT NULL,
"ESTATUS" ACTIVO_INACTIVO ,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_CONTRATOS" PRIMARY KEY ("CLAVE")
);
/* Table: DEPURADOR, Owner: SYSDBA */
CREATE TABLE "DEPURADOR"
(
"TEXTO" VARCHAR(500)
);
/* Table: EMPLEADOS, Owner: SYSDBA */
CREATE TABLE "EMPLEADOS"
(
"CLAVE" SMALLINT NOT NULL,
"NUMERO" INTEGER NOT NULL,
"PERFIL" SMALLINT,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"PATERNO" VARCHAR(25) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"MATERNO" VARCHAR(25) CHARACTER SET ISO8859_1 COLLATE ES_ES_CI_AI,
"PSEUDONIMO" VARCHAR(20) CHARACTER SET ISO8859_1 COLLATE ES_ES_CI_AI,
"NOMBRE_ENTRADA" VARCHAR(20) CHARACTER SET ISO8859_1,
"CONTRASENIA" VARCHAR(20) CHARACTER SET ISO8859_1,
"CALLE" VARCHAR(35) CHARACTER SET ISO8859_1,
"NUMERO_EXTERIOR" VARCHAR(15) CHARACTER SET ISO8859_1,
"NUMERO_INTERIOR" VARCHAR(15) CHARACTER SET ISO8859_1,
"COLONIA" VARCHAR(35) CHARACTER SET ISO8859_1,
"LOCALIDAD" VARCHAR(35) CHARACTER SET ISO8859_1,
"MUNICIPIO" VARCHAR(35) CHARACTER SET ISO8859_1,
"ESTADO" VARCHAR(35) CHARACTER SET ISO8859_1,
"PAIS" VARCHAR(35) CHARACTER SET ISO8859_1,
"CP" CHAR(5) CHARACTER SET ISO8859_1,
"TELEFONO" VARCHAR(30) CHARACTER SET ISO8859_1,
"CELULAR" VARCHAR(30) CHARACTER SET ISO8859_1,
"NEXTEL" VARCHAR(30) CHARACTER SET ISO8859_1,
"CORREO" VARCHAR(80) CHARACTER SET ISO8859_1,
"ESTADO_CIVIL" ESTADO_CIVIL ,
"SEXO" MASCULINO_FEMENINO NOT NULL,
"FECHA_NACIMIENTO" DATE,
"HIJOS" SMALLINT,
"ESCOLARIDAD" SMALLINT,
"FOTO" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
"NOMBRE_EMERGENCIA" VARCHAR(60) CHARACTER SET ISO8859_1,
"TELEFONO_EMERGENCIA" VARCHAR(30) CHARACTER SET ISO8859_1,
"ENTIDAD" SMALLINT NOT NULL,
"EMPRESA" SMALLINT NOT NULL,
"CONTRATO" SMALLINT NOT NULL,
"PUESTO" SMALLINT NOT NULL,
"RFC" VARCHAR(13) CHARACTER SET ISO8859_1,
"CURP" VARCHAR(20) CHARACTER SET ISO8859_1,
"NUMERO_IMSS" VARCHAR(11) CHARACTER SET ISO8859_1,
"CARTILLA_MILITAR" VARCHAR(10) CHARACTER SET ISO8859_1,
"NACIONALIDAD" VARCHAR(15) CHARACTER SET ISO8859_1,
"ESTATUS" ACTIVO_INACTIVO ,
"OTORGAR_DESCUENTO" PORCENTAJE ,
"FECHA_INGRESO" DATE,
"FECHA_CONTRASENIA" TIMESTAMP,
"EMPLEADO_REGISTRO" SMALLINT,
"EMPLEADO_MODIFICACION" SMALLINT,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_EMPLEADOS" PRIMARY KEY ("CLAVE")
);
/* Table: EMPLEADOS_ENTIDADES, Owner: SYSDBA */
CREATE TABLE "EMPLEADOS_ENTIDADES"
(
"EMPLEADO" SMALLINT NOT NULL,
"ENTIDAD" SMALLINT NOT NULL
);
/* Table: EMPLEADOS_PERMISOS, Owner: SYSDBA */
CREATE TABLE "EMPLEADOS_PERMISOS"
(
"CLAVE" INTEGER NOT NULL,
"EMPLEADO" SMALLINT NOT NULL,
"PERMISO" SMALLINT NOT NULL,
"TIPO" CHAR(1) CHARACTER SET ISO8859_1 NOT NULL
);
/* Table: EMPRESAS, Owner: SYSDBA */
CREATE TABLE "EMPRESAS"
(
"CLAVE" SMALLINT NOT NULL,
"NUMERO" SMALLINT NOT NULL,
"RFC" CHAR(13) CHARACTER SET ISO8859_1 NOT NULL,
"NOMBRE" VARCHAR(90) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"NOMBRE_CORTO" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"CALLE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"NUMERO_EXTERIOR" VARCHAR(15) CHARACTER SET ISO8859_1 NOT NULL,
"NUMERO_INTERIOR" VARCHAR(15) CHARACTER SET ISO8859_1,
"COLONIA" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"LOCALIDAD" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"MUNICIPIO" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"ESTADO" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"PAIS" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"CP" CHAR(5) CHARACTER SET ISO8859_1 NOT NULL,
"LOGO" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
"ESTATUS" ACTIVO_INACTIVO NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_EMPRESAS" PRIMARY KEY ("CLAVE")
);
/* Table: ENTIDADES, Owner: SYSDBA */
CREATE TABLE "ENTIDADES"
(
"CLAVE" SMALLINT NOT NULL,
"NUMERO" SMALLINT NOT NULL,
"EMPRESA" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"CALLE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"NUMERO_EXTERIOR" VARCHAR(15) CHARACTER SET ISO8859_1 NOT NULL,
"NUMERO_INTERIOR" VARCHAR(15) CHARACTER SET ISO8859_1,
"CP" CHAR(5) CHARACTER SET ISO8859_1 NOT NULL,
"COLONIA" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"LOCALIDAD" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"MUNICIPIO" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"ESTADO" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"PAIS" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"CORREO" VARCHAR(80) CHARACTER SET ISO8859_1,
"TELEFONO" VARCHAR(30) CHARACTER SET ISO8859_1,
"FAX" VARCHAR(30) CHARACTER SET ISO8859_1,
"SERVIDOR" VARCHAR(100) CHARACTER SET ISO8859_1,
"PUERTO" SMALLINT,
"ESTATUS" ACTIVO_INACTIVO NOT NULL,
"CFD_NO_APROBACION" INTEGER,
"CFD_ANIO_APROBACION" SMALLINT,
"CFD_SERIE" VARCHAR(12) CHARACTER SET ISO8859_1,
"CFD_FOLIO_INICIAL" INTEGER,
"CFD_FOLIO_FINAL" INTEGER,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_ENTIDADES" PRIMARY KEY ("CLAVE")
);
/* Table: ENTIDADES_CAJAS, Owner: SYSDBA */
CREATE TABLE "ENTIDADES_CAJAS"
(
"CLAVE" SMALLINT NOT NULL,
"ENTIDAD" SMALLINT NOT NULL,
"NUMERO" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"ESTATUS" ACTIVO_INACTIVO ,
"SERIE_NOTA" VARCHAR(10) CHARACTER SET ISO8859_1,
"SERIE_FACTURA" VARCHAR(10) CHARACTER SET ISO8859_1,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_CAJAS" PRIMARY KEY ("CLAVE")
);
/* Table: ESCOLARIDAD, Owner: SYSDBA */
CREATE TABLE "ESCOLARIDAD"
(
"CLAVE" SMALLINT NOT NULL,
"NIVEL" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
CONSTRAINT "PK_ESCOLARIDAD" PRIMARY KEY ("CLAVE")
);
/* Table: ESTADOS, Owner: SYSDBA */
CREATE TABLE "ESTADOS"
(
"CLAVE" INTEGER NOT NULL,
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"PAIS" SMALLINT NOT NULL,
CONSTRAINT "PK_ESTADOS" PRIMARY KEY ("CLAVE")
);
/* Table: IMPUESTOS, Owner: SYSDBA */
CREATE TABLE "IMPUESTOS"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"EMPLEADO_REGISTRO" SMALLINT NOT NULL,
"EMPLEADO_MODIFICACION" SMALLINT NOT NULL,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_IMPUESTOS" PRIMARY KEY ("CLAVE")
);
/* Table: IMPUESTOS_DETALLE, Owner: SYSDBA */
CREATE TABLE "IMPUESTOS_DETALLE"
(
"CLAVE" SMALLINT NOT NULL,
"IMPUESTO" SMALLINT NOT NULL,
"ORDEN" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
"PORCENTAJE" NUMERIC(10,6) NOT NULL,
CONSTRAINT "PK_IMPUESTOS_DETALLE" PRIMARY KEY ("CLAVE")
);
/* Table: LISTAS_PRECIOS, Owner: SYSDBA */
CREATE TABLE "LISTAS_PRECIOS"
(
"CLAVE" SMALLINT NOT NULL,
"NUMERO" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"ESTATUS" ACTIVO_INACTIVO ,
"DESCRIPCION" VARCHAR(200) CHARACTER SET ISO8859_1 NOT NULL,
"REF_LISTA_PRECIO" SMALLINT,
"REF_PORCENTAJE" PORCENTAJE ,
"EMPLEADO_REGISTRO" SMALLINT NOT NULL,
"EMPLEADO_MODIFICACION" SMALLINT NOT NULL,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_LISTAS_PRECIOS" PRIMARY KEY ("CLAVE")
);
/* Table: PAISES, Owner: SYSDBA */
CREATE TABLE "PAISES"
(
"CLAVE" INTEGER NOT NULL,
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
CONSTRAINT "PK_PAISES" PRIMARY KEY ("CLAVE")
);
/* Table: PARAMETROS, Owner: SYSDBA */
CREATE TABLE "PARAMETROS"
(
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL,
"VALOR" VARCHAR(100) CHARACTER SET ISO8859_1,
CONSTRAINT "PK_PARAMETROS" PRIMARY KEY ("NOMBRE")
);
/* Table: PERFILES, Owner: SYSDBA */
CREATE TABLE "PERFILES"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_PERFILES" PRIMARY KEY ("CLAVE")
);
/* Table: PERFILES_PERMISOS, Owner: SYSDBA */
CREATE TABLE "PERFILES_PERMISOS"
(
"CLAVE" SMALLINT NOT NULL,
"PERFIL" SMALLINT NOT NULL,
"PERMISO" SMALLINT NOT NULL,
"TIPO" CHAR(1) CHARACTER SET ISO8859_1 NOT NULL,
CONSTRAINT "PK_PERFILES_PERMISOS_1" PRIMARY KEY ("CLAVE")
);
/* Table: PROVEEDORES, Owner: SYSDBA */
CREATE TABLE "PROVEEDORES"
(
"CLAVE" SMALLINT NOT NULL,
"NUMERO" SMALLINT NOT NULL,
"RFC" VARCHAR(13),
"NOMBRE" VARCHAR(70) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"NOMBRE_FISCAL" VARCHAR(70) CHARACTER SET ISO8859_1 COLLATE ES_ES_CI_AI,
"CATEGORIA" SMALLINT,
"COMENTARIO" VARCHAR(250),
"ESTATUS" ACTIVO_INACTIVO ,
"PAGINA_WEB" VARCHAR(80),
"CONTACTO" VARCHAR(70),
"CORREO" VARCHAR(80),
"TELEFONO" VARCHAR(30),
"CELULAR" VARCHAR(30),
"FAX" VARCHAR(30),
"NEXTEL" VARCHAR(30),
"CALLE" VARCHAR(35) NOT NULL,
"NUMERO_EXTERIOR" VARCHAR(15) NOT NULL,
"NUMERO_INTERIOR" VARCHAR(15),
"COLONIA" VARCHAR(35),
"LOCALIDAD" VARCHAR(35),
"MUNICIPIO" VARCHAR(35),
"ESTADO" VARCHAR(35),
"PAIS" VARCHAR(35) NOT NULL,
"CP" CHAR(5),
"DIAS_CREDITO" SMALLINT,
"LIMITE_CREDITO" NUMERIC(15,4),
"DESCUENTO" PORCENTAJE NOT NULL,
"BANCO" VARCHAR(60),
"BANCO_SUCURSAL" VARCHAR(15),
"BANCO_CUENTA" VARCHAR(15),
"BANCO_CLABE" VARCHAR(18),
"BANCO_REFERENCIA" VARCHAR(20),
"ULTIMA_COMPRA" INTEGER,
"EMPLEADO_REGISTRO" SMALLINT NOT NULL,
"EMPLEADO_MODIFICACION" SMALLINT NOT NULL,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_PROVEEDORES" PRIMARY KEY ("CLAVE")
);
/* Table: PROVEEDORES_CATEGORIAS, Owner: SYSDBA */
CREATE TABLE "PROVEEDORES_CATEGORIAS"
(
"CLAVE" SMALLINT NOT NULL,
"NOMBRE" VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"DESCRIPCION" VARCHAR(200) CHARACTER SET ISO8859_1 NOT NULL,
"EMPLEADO_REGISTRO" SMALLINT NOT NULL,
"EMPLEADO_MODIFICACION" SMALLINT NOT NULL,
"FECHA_REGISTRO" TIMESTAMP NOT NULL,
"FECHA_MODIFICACION" TIMESTAMP,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_PROVEEDORES_CATEGORIAS" PRIMARY KEY ("CLAVE")
);
/* Table: PUESTOS, Owner: SYSDBA */
CREATE TABLE "PUESTOS"
(
"CLAVE" SMALLINT NOT NULL,
"ID" VARCHAR(5) CHARACTER SET ISO8859_1,
"NOMBRE" VARCHAR(40) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
"ESTATUS" ACTIVO_INACTIVO ,
"FECHA_MODIFICACION" TIMESTAMP NOT NULL,
"FECHA_IMPORTACION" TIMESTAMP,
CONSTRAINT "PK_PUESTOS" PRIMARY KEY ("CLAVE")
);
/* Index definitions for all user tables */
CREATE INDEX "ARTICULOS_CODIGOS" ON "ARTICULOS_CODIGOS"("CODIGO");
CREATE UNIQUE INDEX "IDX_LISTAS_PRECIOS_1" ON "LISTAS_PRECIOS"("NUMERO");
ALTER TABLE "APLICACIONES_PERMISOS" ADD CONSTRAINT "FK_APLICACIONES_PERMISOS_1" FOREIGN KEY ("APLICACION") REFERENCES "APLICACIONES" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "APLICACIONES_PERMISOS" ADD CONSTRAINT "FK_APLICACIONES_PERMISOS_2" FOREIGN KEY ("PERMISO") REFERENCES "APLICACIONES_PERMISOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_1" FOREIGN KEY ("CATEGORIA") REFERENCES "ARTICULOS_CATEGORIAS" ("CLAVE") ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_2" FOREIGN KEY ("DEPARTAMENTO") REFERENCES "ARTICULOS_DEPARTAMENTOS" ("CLAVE") ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_3" FOREIGN KEY ("CLASE") REFERENCES "CLASES" ("CLAVE") ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_4" FOREIGN KEY ("IMPUESTO") REFERENCES "IMPUESTOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_5" FOREIGN KEY ("EMPLEADO_REGISTRO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_6" FOREIGN KEY ("EMPLEADO_MODIFICACION") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS" ADD CONSTRAINT "FK_ARTICULOS_7" FOREIGN KEY ("COSTO_EMPLEADO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS_CARACTERISTICAS" ADD CONSTRAINT "FK_ARTICULOS_CARACTERISTICA_1" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_CARACTERISTICAS" ADD CONSTRAINT "FK_ARTICULOS_CARACTERISTICA_2" FOREIGN KEY ("CARACTERISTICA") REFERENCES "CLASES_CARACTERISTICAS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_CATEGORIAS" ADD CONSTRAINT "FK_ARTICULOS_CATEGORIAS_1" FOREIGN KEY ("EMPLEADO_REGISTRO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS_CATEGORIAS" ADD CONSTRAINT "FK_ARTICULOS_CATEGORIAS_2" FOREIGN KEY ("EMPLEADO_MODIFICACION") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS_CODIGOS" ADD CONSTRAINT "FK_ARTICULOS_CODIGOS_1" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_COSTOS_HISTORIAL" ADD CONSTRAINT "FK_ARTICULOS_COSTOS_HISTORI_1" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_COSTOS_HISTORIAL" ADD CONSTRAINT "FK_ARTICULOS_COSTOS_HISTORI_2" FOREIGN KEY ("EMPLEADO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_JUEGOS" ADD CONSTRAINT "FK_ARTICULOS_JUEGOS_1" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_JUEGOS" ADD CONSTRAINT "FK_ARTICULOS_JUEGOS_2" FOREIGN KEY ("COMPONENTE") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_PRECIOS" ADD CONSTRAINT "FK_ARTICULOS_PRECIOS_1" FOREIGN KEY ("LISTA_PRECIO") REFERENCES "LISTAS_PRECIOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_PRECIOS" ADD CONSTRAINT "FK_ARTICULOS_PRECIOS_2" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_PRECIOS" ADD CONSTRAINT "FK_ARTICULOS_PRECIOS_3" FOREIGN KEY ("EMPLEADO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS_PRECIOS_HISTORIAL" ADD CONSTRAINT "FK_ARTICULOS_PRECIOS_HIST_1" FOREIGN KEY ("LISTA_PRECIO") REFERENCES "LISTAS_PRECIOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_PRECIOS_HISTORIAL" ADD CONSTRAINT "FK_ARTICULOS_PRECIOS_HIST_2" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_PRECIOS_HISTORIAL" ADD CONSTRAINT "FK_ARTICULOS_PRECIOS_HIST_3" FOREIGN KEY ("EMPLEADO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ARTICULOS_PROVEEDORES" ADD CONSTRAINT "FK_ARTICULOS_PROVEEDORES_1" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_PROVEEDORES" ADD CONSTRAINT "FK_ARTICULOS_PROVEEDORES_2" FOREIGN KEY ("PROVEEDOR") REFERENCES "PROVEEDORES" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_SIMILARES" ADD CONSTRAINT "FK_ARTICULOS_SIMILARES_1" FOREIGN KEY ("ARTICULO") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ARTICULOS_SIMILARES" ADD CONSTRAINT "FK_ARTICULOS_SIMILARES_2" FOREIGN KEY ("SIMILAR") REFERENCES "ARTICULOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "CLASES_CARACTERISTICAS" ADD CONSTRAINT "FK_CLASES_CARACTERISTICAS_1" FOREIGN KEY ("CLASE") REFERENCES "CLASES" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "EMPLEADOS" ADD CONSTRAINT "FK_EMPLEADOS_1" FOREIGN KEY ("EMPRESA") REFERENCES "EMPRESAS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "EMPLEADOS" ADD CONSTRAINT "FK_EMPLEADOS_2" FOREIGN KEY ("ENTIDAD") REFERENCES "ENTIDADES" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "EMPLEADOS" ADD CONSTRAINT "FK_EMPLEADOS_3" FOREIGN KEY ("CONTRATO") REFERENCES "CONTRATOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "EMPLEADOS" ADD CONSTRAINT "FK_EMPLEADOS_4" FOREIGN KEY ("PUESTO") REFERENCES "PUESTOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "EMPLEADOS" ADD CONSTRAINT "FK_EMPLEADOS_5" FOREIGN KEY ("PERFIL") REFERENCES "PERFILES" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "EMPLEADOS" ADD CONSTRAINT "FK_EMPLEADOS_6" FOREIGN KEY ("ESCOLARIDAD") REFERENCES "ESCOLARIDAD" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "EMPLEADOS_ENTIDADES" ADD CONSTRAINT "FK_EMPLEADOS_ENTIDADES_1" FOREIGN KEY ("EMPLEADO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "EMPLEADOS_ENTIDADES" ADD CONSTRAINT "FK_EMPLEADOS_ENTIDADES_2" FOREIGN KEY ("ENTIDAD") REFERENCES "ENTIDADES" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "EMPLEADOS_PERMISOS" ADD CONSTRAINT "FK_EMPLEADOS_PERMISOS_1" FOREIGN KEY ("EMPLEADO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "EMPLEADOS_PERMISOS" ADD CONSTRAINT "FK_EMPLEADOS_PERMISOS_2" FOREIGN KEY ("PERMISO") REFERENCES "APLICACIONES_PERMISOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ENTIDADES" ADD CONSTRAINT "FK_ENTIDADES_1" FOREIGN KEY ("EMPRESA") REFERENCES "EMPRESAS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ENTIDADES_CAJAS" ADD CONSTRAINT "FK_ENTIDADES_CAJAS_1" FOREIGN KEY ("ENTIDAD") REFERENCES "ENTIDADES" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "ESTADOS" ADD CONSTRAINT "FK_ESTADOS_1" FOREIGN KEY ("PAIS") REFERENCES "PAISES" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "IMPUESTOS" ADD CONSTRAINT "FK_IMPUESTOS_1" FOREIGN KEY ("EMPLEADO_REGISTRO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "IMPUESTOS" ADD CONSTRAINT "FK_IMPUESTOS_2" FOREIGN KEY ("EMPLEADO_MODIFICACION") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "IMPUESTOS_DETALLE" ADD CONSTRAINT "FK_IMPUESTOS_DETALLE_1" FOREIGN KEY ("IMPUESTO") REFERENCES "IMPUESTOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "LISTAS_PRECIOS" ADD CONSTRAINT "FK_LISTAS_PRECIOS_1" FOREIGN KEY ("REF_LISTA_PRECIO") REFERENCES "LISTAS_PRECIOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "PERFILES_PERMISOS" ADD CONSTRAINT "FK_PERFILES_PERMISOS_1" FOREIGN KEY ("PERFIL") REFERENCES "PERFILES" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "PERFILES_PERMISOS" ADD CONSTRAINT "FK_PERFILES_PERMISOS_2" FOREIGN KEY ("PERMISO") REFERENCES "APLICACIONES_PERMISOS" ("CLAVE") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "PROVEEDORES_CATEGORIAS" ADD CONSTRAINT "FK_PROVEEDORES_CATEGORIAS_1" FOREIGN KEY ("EMPLEADO_REGISTRO") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "PROVEEDORES_CATEGORIAS" ADD CONSTRAINT "FK_PROVEEDORES_CATEGORIAS_2" FOREIGN KEY ("EMPLEADO_MODIFICACION") REFERENCES "EMPLEADOS" ("CLAVE") ON UPDATE CASCADE ON DELETE NO ACTION;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;
/* Stored procedures */
CREATE PROCEDURE "ACTUALIZAR_PRECIO"
(
"ARTICULO" INTEGER,
"LISTAPRECIO" INTEGER,
"PRECIO" NUMERIC(15, 4),
"EMPLEADO" INTEGER,
"PRECIO_AUTOMATICO" CHAR(1)
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "CALCULAR_IMPORTE_CON_IMPUESTO"
(
"IMPORTE" NUMERIC(15, 4),
"IMPUESTO" INTEGER
)
RETURNS
(
"TOTAL" NUMERIC(15, 4)
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "CALCULAR_IMPORTE_SIN_IMPUESTO"
(
"IMPORTE" NUMERIC(15, 4),
"IMPUESTO" INTEGER
)
RETURNS
(
"TOTAL" NUMERIC(15, 4)
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "ESTABLECER_PARAMETRO"
(
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1,
"VALOR" VARCHAR(100) CHARACTER SET ISO8859_1
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "OBTENER_NUEVA_CLAVE"
(
"NOMBRE_TABLA" VARCHAR(30) CHARACTER SET ISO8859_1
)
RETURNS
(
"CLAVE" INTEGER
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "OBTENER_PARAMETRO"
(
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1,
"VALOR_DEFECTO" VARCHAR(100) CHARACTER SET ISO8859_1
)
RETURNS
(
"VALOR" VARCHAR(100) CHARACTER SET ISO8859_1
)
AS
BEGIN EXIT; END ^
ALTER PROCEDURE "ACTUALIZAR_PRECIO"
(
"ARTICULO" INTEGER,
"LISTAPRECIO" INTEGER,
"PRECIO" NUMERIC(15, 4),
"EMPLEADO" INTEGER,
"PRECIO_AUTOMATICO" CHAR(1)
)
AS
declare variable iclave integer;
begin
-- Verifica si existe el precio
SELECT clave FROM articulos_precios WHERE lista_precio = :ListaPrecio AND articulo = :Articulo INTO :iClave;
IF(iClave is NULL) THEN
BEGIN
-- Si no existe el precio lo inserta
SELECT clave FROM obtener_nueva_clave('ARTICULOS_PRECIOS') INTO :iClave;
INSERT INTO articulos_precios (clave, lista_precio, articulo, precio, empleado, fecha, precio_automatico)
VALUES(:iClave, :ListaPrecio, :Articulo, :Precio, :Empleado, 'NOW', :Precio_Automatico);
END
ELSE
UPDATE articulos_precios SET precio = :Precio, empleado = :Empleado, precio_automatico = :Precio_Automatico
WHERE articulo = :Articulo AND lista_precio = :ListaPrecio;
end
^
ALTER PROCEDURE "CALCULAR_IMPORTE_CON_IMPUESTO"
(
"IMPORTE" NUMERIC(15, 4),
"IMPUESTO" INTEGER
)
RETURNS
(
"TOTAL" NUMERIC(15, 4)
)
AS
DECLARE rPorcentaje numeric(10,6);
BEGIN
Total = Importe;
FOR SELECT SUM(porcentaje) AS porcentaje FROM impuestos_detalle WHERE impuesto = :Impuesto GROUP BY orden INTO :rPorcentaje DO
BEGIN
Total = :Total * (1 + :rPorcentaje / 100);
END
suspend;
END
^
ALTER PROCEDURE "CALCULAR_IMPORTE_SIN_IMPUESTO"
(
"IMPORTE" NUMERIC(15, 4),
"IMPUESTO" INTEGER
)
RETURNS
(
"TOTAL" NUMERIC(15, 4)
)
AS
DECLARE rPorcentaje numeric(10,6);
BEGIN
Total = Importe;
FOR SELECT SUM(porcentaje) AS porcentaje FROM impuestos_detalle WHERE impuesto = :Impuesto GROUP BY orden INTO :rPorcentaje DO
BEGIN
Total = :Total / (1 + :rPorcentaje / 100);
END
suspend;
END
^
ALTER PROCEDURE "ESTABLECER_PARAMETRO"
(
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1,
"VALOR" VARCHAR(100) CHARACTER SET ISO8859_1
)
AS
declare variable svalor varchar(100);
BEGIN
-- Busca el valor de una etiqueta
SELECT valor FROM parametros WHERE nombre = :NOMBRE INTO :sValor;
IF(sValor IS NULL) THEN
-- Si no encuentra la etiqueta la inserta con el valor
INSERT INTO parametros (nombre, valor) VALUES(:NOMBRE, :VALOR);
ELSE
-- Si encuentra la etiqueta le asigna su nuevo valor
UPDATE parametros SET valor = :VALOR WHERE nombre = :NOMBRE;
SUSPEND;
END
^
ALTER PROCEDURE "OBTENER_NUEVA_CLAVE"
(
"NOMBRE_TABLA" VARCHAR(30) CHARACTER SET ISO8859_1
)
RETURNS
(
"CLAVE" INTEGER
)
AS
declare variable tabla varchar(100);
BEGIN
Tabla = 'CLAVE_'||:NOMBRE_TABLA;
-- Busca el nombre de la tabla tratando de recuperar la última clave registrada
SELECT CAST(COALESCE(valor, '0') AS INTEGER) FROM obtener_parametro(:Tabla, '0') INTO :CLAVE;
CLAVE = CLAVE + 1;
-- Se registrada la nueva clave aumentada en uno
EXECUTE PROCEDURE establecer_parametro(:Tabla, CAST(:CLAVE AS VARCHAR(100)));
-- Se regresa la nueva clave de la tabla
SUSPEND;
END
^
ALTER PROCEDURE "OBTENER_PARAMETRO"
(
"NOMBRE" VARCHAR(35) CHARACTER SET ISO8859_1,
"VALOR_DEFECTO" VARCHAR(100) CHARACTER SET ISO8859_1
)
RETURNS
(
"VALOR" VARCHAR(100) CHARACTER SET ISO8859_1
)
AS
BEGIN
-- Busca la etiqueta
SELECT valor FROM parametros WHERE nombre = :NOMBRE INTO :Valor;
IF(Valor IS NULL) THEN
-- Si no encuentra la etiquera la inserta con el valor por defecto
INSERT INTO parametros (nombre, valor) VALUES(:NOMBRE, :VALOR_DEFECTO);
SUSPEND;
END
^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "ARTICULOS_COSTOS_JUEGOS" FOR "ARTICULOS"
ACTIVE AFTER UPDATE POSITION 2
AS
DECLARE iClaveArticulo INTEGER;
begin
-- Cambia el costo de los artículos tipo juego los cuales tienen en su contenido el artículo al que se le cambió el costo
IF(new.costo <> old.costo) THEN
FOR SELECT a.clave FROM articulos a JOIN articulos_juegos j ON a.clave = j.articulo AND j.componente = new.clave
AND a.costo_automatico = 'S' INTO :iClaveArticulo DO
UPDATE articulos SET costo = (SELECT SUM(a.costo * j.cantidad) FROM articulos a JOIN articulos_juegos j ON a.clave = j.componente AND
j.articulo = :iClaveArticulo), costo_fecha = new.costo_fecha, costo_empleado = new.costo_empleado WHERE clave = :iClaveArticulo;
end
^
CREATE TRIGGER "ARTICULOS_COSTOS" FOR "ARTICULOS"
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
begin
IF(old.costo <> new.costo or inserting) THEN
BEGIN
new.costo_fecha = 'NOW';
new.costo_empleado = new.empleado_modificacion;
END
end
^
CREATE TRIGGER "ARTICULOS_COSTOS_HIST" FOR "ARTICULOS"
ACTIVE AFTER INSERT OR UPDATE POSITION 1
AS
DECLARE iClave INTEGER;
begin
-- Si cambia el costo, registra el costo nuevo en el historial
IF (old.costo <> new.costo or inserting) THEN
BEGIN
-- Asigna la nueva fecha para el costo
SELECT clave FROM obtener_nueva_clave('ARTICULOS_COSTOS_HISTORIAL') INTO :iClave;
INSERT INTO articulos_costos_historial (clave, articulo, costo, empleado, fecha)
VALUES(:iClave, new.clave, new.costo, new.costo_empleado, 'NOW');
END
end
^
CREATE TRIGGER "ARTICULOS_PRECIOS_HIST" FOR "ARTICULOS_PRECIOS"
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
DECLARE iClave INTEGER;
BEGIN
-- Si el precio se cambia, registra en el historial de precios el precio anterior
IF(old.precio <> new.precio or inserting) THEN
BEGIN
SELECT clave FROM obtener_nueva_clave('ARTICULOS_PRECIOS_HISTORIAL') INTO :iClave;
INSERT INTO articulos_precios_historial(clave, lista_precio, articulo, precio, empleado, fecha)
VALUES(:iClave, new.lista_precio, new.articulo, new.precio, new.empleado, 'NOW');
END
END
^
CREATE TRIGGER "ARTICULOS_PRECIOS_JUEGOS" FOR "ARTICULOS_PRECIOS"
ACTIVE AFTER INSERT OR UPDATE POSITION 1
AS
DECLARE iClaveArticulo INTEGER;
begin
-- Cambia el precio de los artículos tipo juego los cuales tienen en su contenido el artículo al que se le cambió el precio
IF(old.precio <> new.precio) THEN
BEGIN
FOR SELECT a.articulo FROM articulos_precios a JOIN articulos_juegos j ON a.articulo = j.articulo AND j.componente = new.articulo
AND a.precio_automatico = 'S' and a.lista_precio = new.lista_precio INTO :iClaveArticulo DO
UPDATE articulos_precios SET precio = (SELECT SUM(p.precio * j.cantidad) FROM articulos_precios p
JOIN articulos_juegos j ON p.articulo = j.componente AND j.articulo = :iClaveArticulo AND p.lista_precio = new.lista_precio),
fecha = new.fecha, empleado = new.empleado WHERE articulo = :iClaveArticulo AND lista_precio = new.lista_precio;
END
end
^
CREATE TRIGGER "EMPLEADOS_FECHA_CONTRA" FOR "EMPLEADOS"
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
BEGIN
-- Si cambia la contraseña, se registra la fecha en que se cambió
IF(new.contrasenia <> old.contrasenia or inserting) THEN
new.fecha_contrasenia = 'NOW';
END
^
COMMIT WORK ^
SET TERM ;^
/* Grant Roles for this database */
/* Grant permissions for this database */