Procedimiento SQL INTERBASE

[laloma]

hola amigos,

tengo la necesidad de hacer un procedimiento en sql interbase con el fin de recorrer una tabla registro por registro para calcular el saldo en cantidad y valor de un kardex de materia prima, el cual no contiene en este momento estos saldo, pero tiene la cantidad y el valor unitario de cada item en cada registro.
alguien podria ayudarme con el codigo del procedimiento ya que la verdad tengo poca experiencia en la programacion de procedimiento almacenados en interbase o firebird.

muchas gracias por su atencion y colaboracion.

[lbuelvas]

Podrias describir mejor las tablas que intervienen en tu necesidad y explica un poco mejor lo que quieres.

[laloma]

gracias por tu respuesta e interes en ayudarme luis fernando.

estoy implementando un sistema contable para un cliente, el cual esta desarrollado en delphi 5.0 sobre interbase 6.0, el cual no contiene un kardex permanente de materia prima, pero se requiere que lo tenga y el desarrollador se niega a hacerlo, por lo cual he decido solucionarlo yo, a nivel de la base de datos por medio de codigo sql (procedimientos, vistas, triggers, etc).

Existe una tabla que contiene todas todas las transacciones, pero solo tiene las columnas de cantidad y valor unitario de la transaccion, haciendo falta las de valor total de la transaccion, saldos en unidades y valor del item hasta cada transaccion.

despues de analizar la situacion, intente lo siguiente mediante la creacion de una vista, y me funciono parcialmente pero algunos saldos los calcula bien y otros mal, creo que debido al ordenamiento y prioridad.

CREATE VIEW VSALDOS_INV_BOD(
CONTEO,
ITEM,
BATCH,
TIPO,
LOCATION,
FECHA,
QTY,
PRIORIDAD,
VALUNIT,
SALDOU,
SALDOV,
SALDOT,
DOC_EXTERNO)
AS
SELECT A.CONTEO,A.ITEM,A.BATCH,A.TIPO,A.LOCATION,A.FECHA,A.QTY,A.PRIORIDAD,A.VALUNIT,
(SELECT SUM(QTY) FROM ITEMACT B WHERE (ITEM=A.ITEM) AND (B.CONTEO <= A.CONTEO) AND (B.FECHA <=A.FECHA) AND (B.LOCATION = A.LOCATION)) AS SALDOU,
(SELECT SUM(TOTPARCIAL) FROM ITEMACT B WHERE (ITEM=A.ITEM) AND (B.CONTEO <= A.CONTEO) AND (B.FECHA <=A.FECHA) AND (B.LOCATION = A.LOCATION)) AS SALDOV,
(SELECT SUM(TOTPARCIAL) FROM ITEMACT B WHERE (ITEM=A.ITEM) AND (B.CONTEO <= A.CONTEO) AND (B.FECHA <=A.FECHA) AND (B.LOCATION = A.LOCATION)) AS SALDOT,
(select e.doc_externo from ipadjuste e inner join ipadjustd d on (e.e = d.e and e.s = d.s and e.tipo = d.tipo and e.batch = d.batch) where d.item = a.item and e.tipo = a.tipo and e.batch =a.batch) AS DOC_EXTERNO
FROM ITEMACT A;

lo del inner join es para traer otro dato que requiero en esta misma tabla, pero esta almacenado en otra.

debido a que con lo anterior no puede obtener unos resultados completamente satisfactorios, tengo planeado hacer lo siguiente:

1. Agregar las columnas faltantes en la tabla.
2. Desarrollar un procedimiento que ejecute los siguientes pasos:
2.1. Seleccionar la tabla ordenada por bodega, item, fecha y prioridad (necesaria en caso de existir registros coincidentes por bodega,item y fecha).
2.2. Crear las variables para acumulacion de los saldos de cantidad y valor
2.3. leer la cantidad y el valor del registro actual.
2.4. Acumular la cantidad y el valor en las variables de saldos.
2.5. Almacenar las variables de los saldos en las respectivas columnas del registro actual.
3. repetir los pasos 2.3, 2.4 y 2.5 por todos y cada uno de los registros hasta el final de la tabla.
4. crear un trigger que ejecute los mismos pasos del punto 3 cada vez que inserte o cambie un registro en la tabla.

si tuviera acceso al codigo fuente de la aplicacion, es realmente sencillo hacerlo en delphi, pero me toco directamente en la base de datos y es ahi donde tengo muy poco conocimiento y experiencia en SQL Interbase.

muchas gracias por tu atencion y disculpame por lo extenso de mi exposicion.

[ninguno]

Hola

Si entiendo bien, lo que quieres es crear unos campos que sean acumulados de los registros anteriores? Es decir, algo así:

1 - 1
1 - 2
2 - 4
1 - 5
.....

Si es eso, la pregunta es, se pueden modificar/borrar registros en esa tabla? Si la respuesta es no, ok, tira para delante. Pero si es sí, ni te plantees hacer lo que quieres ya que en cada modificación/borrado de registro tendrás que modificar desde ese registro hasta el final de la tabla que, si es una tabla pequeña, será algo rápido, pero como tenga millones de registros............ en fin, que yo no lo haría con un campo físico, que para ello se inventaron los procedimientos almacenados y/o las vistas

Por cierto, dices que no tienes acceso al código fuente, es una látima, porque yo dejaria IB6 y pasaría a Firebird 2.X, mucho más estable, robusto y rápido.

[laloma]

hola ninguno, gracias por tu ayuda

1. Es correcta tu apreciacion respecto a lo que quiero hacer.
2. Aunque si existe la posibilidad de eliminar y/o modificar los registros de la tabla, esta opcion es muy esporadica y en caso de llegar a ocurrir, creo que la solucion es simple ya que simplemente el procedimiento de recalculo se aplicaria al grupo de registros filtrados por el codigo del item modificado o eliminado.
3. Lo de la vista lo comparto contigo pero tu sabes que la vistas consumen muchos recursos si hay una gran cantidad de registros en ella. por esa razon preferiria lo del procedimiento y los triggers.
4. respecto al paso de interbase hacia firebird, aunque no lo mencione ya estoy realmente en la 2.1 porque comparto contigo que es mucho mejor. soy fiel a firebird y todo lo que encuentro en interbase lo migro a firebird.

nuevamente gracias.

[lbuelvas]

Empezado por laloma

4. respecto al paso de interbase hacia firebird, aunque no lo mencione ya estoy realmente en la 2.1 porque comparto contigo que es mucho mejor. soy fiel a firebird y todo lo que encuentro en interbase lo migro a firebird.

Hay algo que no comprendo, comentabas que migraste de Interbase a Firebird pero sin el codigo fuente en Delphi del fabricante, debes tener cuidado porque los selects que hacen uniones con otras tablas en ocasiones presentan un problema de "ambiguedad" si uno no diferencia los campos que tienen igual nombre entre varias tablas.

Otra cosa, mantener el saldo por bodega, en la tabla de movimientos aunque te simplifica muchas cosas, te dificulta las modificaciones/eliminaciones en la tabla de movimientos, una alternativa puede ser una tabla de saldos donde aparezcan los campos bodega, fecha, codigo_producto, saldo, podrias considerar esta opcion, con la ventaja que no modificarias tanto la tabla de movimientos.

Si es aceptable que adiciones triggers after / insert /delete /update a la tabla de movimientos para que actualices la tabla de saldos, si el sistema ya tenia triggers, te recomiendo que los hagas independientes y no mezclarlos con los triggers del fabricante original, por aquello de la garantia.

Suponiendo que esa fuera la opcion regalame un tiempo y elaboro un ejercicio completo para reconstruir una tabla de saldos.

[lbuelvas]

Bueno, ya terminé.

Solo coloqué la tabla de movimientos por simplicidad, ustedes pueden imaginar como deseen la tabla de bodegas, productos, las facturas, las entradas, etc.

Vamos a suponer 3 productos (X, Y, Z), dos bodegas (A, B), algunos movimientos de entrada y otros de salida.

El campo tipo de movimiento es E para entrada y S para salida.

Código SQL [-]
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/

CREATE GENERATOR GEN_MOVIMIENTO;

CREATE TABLE MOVIMIENTO (
    ID_MOVIMIENTO     INTEGER NOT NULL,
    CODIGO_BODEGA     VARCHAR(10) NOT NULL,
    CODIGO_PRODUCTO   VARCHAR(10) NOT NULL,
    TIPO_MOVIMIENTO   VARCHAR(1) NOT NULL,
    FECHA_MOVIMIENTO  DATE NOT NULL,
    CANTIDAD          INTEGER NOT NULL,
    VALOR_UNITARIO    NUMERIC(15,2) NOT NULL,
    TOTAL             NUMERIC(15,2) NOT NULL
);
CREATE TABLE SALDO (
    CODIGO_BODEGA    VARCHAR(10) NOT NULL,
    CODIGO_PRODUCTO  VARCHAR(10) NOT NULL,
    FECHA            DATE NOT NULL,
    CANTIDAD         INTEGER NOT NULL
);

/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
ALTER TABLE MOVIMIENTO ADD CONSTRAINT PK_MOVIMIENTO PRIMARY KEY (ID_MOVIMIENTO);
ALTER TABLE SALDO ADD CONSTRAINT PK_SALDO PRIMARY KEY (CODIGO_BODEGA, CODIGO_PRODUCTO, FECHA);

/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/

SET TERM ^ ;

/* Trigger: MOVIMIENTO_BI0 */
CREATE OR ALTER TRIGGER MOVIMIENTO_BI0 FOR MOVIMIENTO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  -- El trigger siempre coloca un valor para el identificador
  new.id_movimiento = gen_id(gen_movimiento, 1);

  -- Calculo del total
  new.total = new.cantidad * new.valor_unitario;
end
^


/* Trigger: MOVIMIENTO_BU0 */
CREATE OR ALTER TRIGGER MOVIMIENTO_BU0 FOR MOVIMIENTO
ACTIVE BEFORE UPDATE POSITION 0
AS
begin.

  -- Calculo del total
  new.total = new.cantidad * new.valor_unitario;
end
^

SET TERM ; ^

/******************************************************************************/
/*                            DATOS                                           */
/******************************************************************************/
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (1, 'A', 'X', 'E', '2008-01-01', 100, 1000, 100000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (2, 'A', 'Y', 'E', '2008-01-01', 200, 400, 80000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (3, 'B', 'Y', 'E', '2008-02-02', 500, 200, 100000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (4, 'B', 'Z', 'E', '2008-02-02', 400, 100, 40000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (5, 'B', 'Z', 'E', '2008-02-02', 200, 150, 30000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (6, 'A', 'Y', 'S', '2008-02-02', 80, 450, 36000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (7, 'A', 'Y', 'S', '2008-02-02', 10, 450, 4500);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (8, 'B', 'Z', 'S', '2008-02-02', 100, 1200, 120000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (9, 'B', 'Y', 'S', '2008-02-02', 5, 450, 2250);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (10, 'A', 'Z', 'E', '2008-03-03', 400, 120, 48000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (11, 'B', 'X', 'E', '2008-03-03', 300, 950, 285000);
INSERT INTO MOVIMIENTO (ID_MOVIMIENTO, CODIGO_BODEGA, CODIGO_PRODUCTO, TIPO_MOVIMIENTO, FECHA_MOVIMIENTO, CANTIDAD, VALOR_UNITARIO, TOTAL) VALUES (12, 'B', 'Z', 'S', '2008-03-03', 100, 130, 13000);

COMMIT WORK;

Este es el procedimiento almacenado

Código SQL [-]
SET TERM ^ ;

CREATE OR ALTER PROCEDURE SP_RECONSTRUIR_SALDOS 
as
declare variable codigo_bodega varchar(10);
declare variable codigo_producto varchar(10);
declare variable fecha_movimiento date;
declare variable tipo_movimiento varchar(1);
declare variable cantidad integer;
declare variable flag_producto varchar(10);
declare variable flag_bodega varchar(10);
declare variable saldo integer;
begin
  -- Procedimiento que reconstruye la tabla de saldos, para que este
  -- procedimietno funcione correctamente debe eliminarse todos los registros
  -- de la tabla saldos.

  -- Se limpian los registros en la tabla de saldos
  delete from saldo;

  -- Inicializacion de variables
  flag_producto = null;
  flag_bodega   = null;

  -- Normalmente en un inventario, en la misma fecha deben considerarse primero
  -- las entradas y luego las salidas, la consulta que se utiliza como el orden
  -- natural de las letras es primero E(ntrada) y luego S(alida) no hay que
  -- hacerle nada especial al order by.
  for select movimiento.codigo_bodega,
             movimiento.codigo_producto,
             movimiento.fecha_movimiento,
             movimiento.tipo_movimiento,
             sum(movimiento.cantidad)
      from movimiento
      group by movimiento.codigo_bodega,
               movimiento.codigo_producto,
               movimiento.fecha_movimiento,
               movimiento.tipo_movimiento
      order by movimiento.codigo_bodega,
               movimiento.codigo_producto,
               movimiento.fecha_movimiento,
               movimiento.tipo_movimiento
     into :codigo_bodega,
          :codigo_producto,
          :fecha_movimiento,
          :tipo_movimiento,
          :cantidad
  do begin
    -- Verifica si hubo cambio de producto o bodega, en cuyo caso se inicializa
    -- una variable para saldo.
    if ((flag_producto is null)            or
        (flag_producto <> codigo_producto) or
        (flag_bodega   is null)            or
        (flag_bodega   <> codigo_bodega))  then
    begin
      flag_producto = codigo_producto;
      flag_bodega   = codigo_bodega;
      saldo         = 0;
    end

    -- Dependiendo de la naturaleza del movimiento se actualiza el saldo
    if (tipo_movimiento = 'E') then
      saldo = saldo + cantidad;
    if (tipo_movimiento = 'S') then
      saldo = saldo - cantidad;

    -- Si no existe un registro para esa bodega, producto y fecha se crea el
    -- registro, de lo contrario se actualiza.
    -- El "select first 1 '*' ..." es una forma personal de verificar si existe
    -- un registro en la tabla con las condiciones contenidas en el where, lo
    -- coloco entre comillas pues el asterisco sin comillas genera más
    -- trafico de datos.
    if (not exists (select first 1 '*'
                    from saldo
                    where saldo.codigo_bodega   = :codigo_bodega
                    and   saldo.codigo_producto = :codigo_producto
                    and   saldo.fecha           = :fecha_movimiento
                    )) then
      insert into saldo
      (
       codigo_bodega,
       codigo_producto,
       fecha,
       cantidad
      )
      values
      (
       :codigo_bodega,
       :codigo_producto,
       :fecha_movimiento,
       :saldo
      );
    else
      -- Habia una registro previo
      update saldo
      set    cantidad = :saldo
      where saldo.codigo_bodega   = :codigo_bodega
      and   saldo.codigo_producto = :codigo_producto
      and   saldo.fecha           = :fecha_movimiento;
    end
end^

SET TERM ; ^

GRANT SELECT,INSERT,DELETE,UPDATE ON SALDO TO PROCEDURE SP_RECONSTRUIR_SALDOS;

GRANT SELECT ON MOVIMIENTO TO PROCEDURE SP_RECONSTRUIR_SALDOS;

GRANT EXECUTE ON PROCEDURE SP_RECONSTRUIR_SALDOS TO SYSDBA;

Ejecuten el procedimiento almacenado para reconstruir la tabla de saldos, hechenle una revisada a ver si hay alguna inconsistencia.

Espero que sea un buen inicio y que les sea de utilidad.

[laloma]

hola luis fernando

lo de la migracion lo hicimos conjuntamente con la casa desarrolladora del software y claro hubo que hacer cambios sintacticos para llevar la base de datos de interbase a firebird.

respecto a la tabla de movimientos te aclaro dos cosas:

la primera es que la posibilidad de modificar o retirar registros, es muy esporadica y en caso de hacerlo, el trigger debe transferir al procedimiento el codigo de item modificado, la bodega y la fecha y de esta manera el proceso se ejecutaria utilizando estos datos como filtro dentro del procedimiento, por lo cual solo afectaria los registros filtrados por codigo y bodega a partir de la fecha hasta el final de registros del item y bodega.

la segunda es que lo que se requiere no es simplemente guardar el saldo del item en la bodega, lo cual no seria un kardex sino simplemente una tabla de saldos, ya que el kardex es mostrar todas las transacciones cronologicas de cada item por bodega una por una con los saldos que genera cada movimiento. como ilustracion de lo anterior es lo mismo que un extracto bancario en el cual te muestran cada transaccion por fecha con el saldo que genera cada una, y si solo te mostrara el saldo inicial y final de la cuenta sin los movimientos saldados individualmente, no habria forma de efectuar una verificacion de los movimientos y sus saldos individuales.

sobre lo de los trigger y/o procedimientos, estamos de acuerdo en que que deben ser y seran independientes.

finalmente te cuento otra cosa, y es que la aplicacion esta concebida para efectuar todos los calculos cada vez que un usuario efectua una consulta del kardex, por lo cual y en un estado de alta concurrencia afecta el rendimiento del sistema por la sobrecarga que esto genera. por esta razon es que he decidido implementar lo del kardex permanente y asi las consultas de los usuarios no tendran que hacer ningun calculo y el select solo tendria que aplicar el filtrado con los parametros seleccionados por los usuarios sin ningun tipo de calculo.

nuevamente gracias por tu interes y colaboracion

Luis Alberto Lopez