Siguiendo con la introducción de herramientas, ahora le toca el turno a las funciones de la base de datos. Estas sirven principalmente para que nos devuelvan un dato cuando las «llamemos». Nos facilita la vida de forma que reutilizamos código y al igual que las vistas, si hay que hacer algun ajuste, con modificarlo en un sitio lo tendremos aplicado a todo nuestro código.
En esta entrada os voy a enseñar 2 casos concretos de vistas aplicadas a Geinfor que creo que facilitan mucho la vida (por lo menos para mí es así)
NINGUNA DE LAS ACCIONES QUE VOY A EXPLICAR TIENE SOPORTE POR PARTE DE GEINFOR. MUCHO OJO Y BAJO VUESTRA RESPONSABILIDAD
Tras la frase de alerta a navegantes de rigor, vamos al lío.
Recomendaciones/normas de los Gremlins para las funciones
- Comienza todas las vistas por fx_
- La siguiente parte del nombre utilizarla para que todas las relacionadas estén juntas
- La última parte del nombre debe ser descriptiva y intenta no utilizar abreviaturas que solo tú entiendas
- Añade un comentario en la creación de la funcion. ¡Comenta tu código! no es un telegrama y no se cobra por palabras
Tipo función para subconsultas
Cuando estamos cruzando información de varias tablas para crear vistas externas de análisis e informes, sucede siempre que en las tablas de datos tenemos los códigos numéricos pero no los textos que nos gusta leer a los humanos (raritos que somos)
¿Qué pasa en estos casos? Que estamos siempre llamando a un montón de tablas para obtener las descripciones y repitiendo una y otra vez las subselect o la tabla en el where con su join y demás…
Se pueden crear funciones para solo hacer una llamada rápida para que nos devuelva la descripción. Si cogemos de ejemplo esa vista de facturación del post anterior (aquí), vamos a crear una vista para todas las dimensiones de tipo, estado, clase, familia y grupo de articulo.
La estructura de la función es la siguiente
CREATE OR REPLACE FUNTION "NOMBRE_DE_FUNCION" (Parametro1 tipoParametro, Parametron tipoparametro) Returns TipoValorDevolucion
Este «churro» que visto así es muy raro vemos un ejemplo
CREATE OR REPLACE FUNCTION FX_ART_DIMENSIONES(ParamArt VARCHAR(60), ParamDimension VARCHAR(60)) RETURNS VARCHAR(200)
BEGIN
/*DEVUELVE EL TEXTO ASOCIADO A LA DIMENSIÓN DEL ARTICULO
Hay que pasar como parametros el código del artículo y el nombre del campo
Adicionalmente tenemos clasificaciones especiales en funcion de valores de "X" campos
como por ejemplo CLASIFICACIONTEST*/
DECLARE ValorInt VARCHAR(60);
DECLARE ValorResultado VARCHAR(200);
-- Devuelve el txt del campo codigocontable
IF ParamDimension = 'CODIGOCONTABLE'
THEN
SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.CODIGO_CONTABLE FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
SET ValorResultado = (SELECT PLAN_CONTABLE.DENOMINACION FROM PLAN_CONTABLE WHERE PLAN_CONTABLE.CODIGO_CONTABLE=ValorInt AND PLAN_CONTABLE.PLANCONTABLE=0);
END IF;
-- Devuelve el txt del campo Clase
IF ParamDimension = 'CLASE'
THEN
SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.CLASE FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
SET ValorResultado = (SELECT TABLA_CLASE_ARTICULO.DENOMINACION FROM TABLA_CLASE_ARTICULO WHERE TABLA_CLASE_ARTICULO.CODIGO=ValorInt);
END IF;
-- Devuelve el txt del campo Grupo
IF ParamDimension = 'GRUPO'
THEN
SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.GRUPO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
SET ValorResultado = (SELECT TABLA_CARACTERISTICA.DENOMINACION FROM TABLA_CARACTERISTICA WHERE TABLA_CARACTERISTICA.CODIGO=ValorInt);
END IF;
-- Devuelve el txt del campo tipo
IF ParamDimension = 'TIPO'
THEN
SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.TIPO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
SET ValorResultado = (SELECT TIPOS_ARTICULO.DESCRIPCION FROM TIPOS_ARTICULO WHERE TIPOS_ARTICULO.CODIGO=ValorInt);
END IF;
-- Devuelve el txt del campo familia
IF ParamDimension = 'FAMILIAPRODUCTO'
THEN
SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.FAMILIAPRODUCTO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
SET ValorResultado = (SELECT TFAMILIA_PRODUCTOS.DESCRIPCION FROM TFAMILIA_PRODUCTOS WHERE TFAMILIA_PRODUCTOS.CODIGO=ValorInt);
END IF;
-- Devuelve el txt del campo Estado
IF ParamDimension = 'ESTADO'
THEN
SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.ESTADO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
SET ValorResultado = (SELECT T_ESTADOARTICULO.DESCRIPCION FROM T_ESTADOARTICULO WHERE T_ESTADOARTICULO.CODIGO=ValorInt);
END IF;
-- Clasificación de agrupación propio
IF ParamDimension = 'CLASIFICACIONTEST'
THEN
SET ValorResultado = (SELECT
CASE MAESTRO_DE_ARTICULOS.CODIGO_CONTABLE
WHEN '701020000' THEN 'FABRICACIÓN'
WHEN '701030000' THEN 'MIXTO'
WHEN '700010000' THEN 'COMERCIAL'
when '702000000' THEN 'SEMIELABORADO'
WHEN '438000000' THEN 'A CUENTA'
ELSE 'OTROS' END
FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
END IF;
IF ValorResultado IS NULL THEN SET ValorResultado = 'NO DEFINIDO'; END IF;
RETURN (ValorResultado);
END @
Con esta función le estamos diciendo que al llamarla, le pasaremos como parametro el código del articulo y con comilla simple el nombre del campo y nos devolverá la denominación de ese campo.
Un ejemplo sencillo de utilización sería este:
SELECT CODIGO_ARTICULO, DENOMINACION, clase, FX_ART_DIMENSIONES(CODIGO_ARTICULO,'CLASE') AS CLASE_TXT FROM MAESTRO_DE_ARTICULOS
Si esto lo aplicamos a nuestra vista de facturación, nos devolverá los textos que son mucho más legibles
Tipo de función para cálculos
La otra aplicación más utilizada es realizar cálculos y esta podría ser la cantidad Asignada, Pedida y Disponible de un articulo. Vamos a ver el ejemplo de las PEDIDAS
El código sería el siguiente
CREATE OR REPLACE FUNCTION FX_ALM_PEDIDAS(CodArticulo VARCHAR(60), CodAlmacen INTEGER) RETURNS DOUBLE /*La función nos devolverá la cantidad asignada de un articulo para un almacen concreto o para todos los almacenes si pasamos como parametro almacén -1*/ BEGIN DECLARE Pedidas DOUBLE; DECLARE PedidasPED DOUBLE; DECLARE PedidasORD DOUBLE; DECLARE AlmacenMin INTEGER; DECLARE AlmacenMax INTEGER; /*Seleccionar si la operativa es para un almacen o para todos los almacenes según parametro*/ IF CodAlmacen=-1 THEN SET AlmacenMin=0; SET almacenmax=9999; ELSE SET AlmacenMin=CodAlmacen; SET almacenmax=CodAlmacen; END IF; SET PedidasPED = (Select Sum( (lin.cantidad_pedida - lin.cantidad_devuelta - lin.cantidad_entregada) * art.coeficiente ) as Pedidas From Lineas_pedido_provee lin, Cab_pedidos_proveed cab, Maestro_de_articulos art, TABLA_ALMACENES TALM Where cab.SERIE=lin.SERIE AND cab.PEDIDO=lin.NUMERO_DE_PEDIDO AND lin.CODIGO_ARTICULO=art.CODIGO_ARTICULO AND cab.ALMACEN=talm.CODIGO AND lin.SITUACION NOT IN (20,30) AND -- indica que la línea esta cerrada o cancelada -- para asegurarnos que si hay un error de proceso en los pedidos no nos desvirtue la información lin.CANTIDAD_PEDIDA-lin.CANTIDAD_ENTREGADA-lin.CANTIDAD_DEVUELTA>0 AND cab.ALMACEN BETWEEN Almacenmin AND Almacenmax and --todos los pedidos del rango del almacen TALM.NOCALCENDISPONIBLE = 0); IF PedidasPED IS NULL THEN SET PedidasPED = 0; END IF; SET PedidasORD = (Select Sum(O.cantidad_pedida+O.cant_en_desviacion-O.cantidad_realizada) as Pedidas From Ordenes_de_trabajo O, TABLA_ALMACENES TALM Where O.codigo_articulo = CodArticulo and O.estado <> 40 and (O.cantidad_pedida+O.cant_en_desviacion-O.cantidad_realizada) > 0 and O.Almacen BETWEEN Almacenmin AND Almacenmax and O.Almacen = TALM.Codigo And TALM.NOCALCENDISPONIBLE = 0);-- IF PedidasORD IS NULL THEN SET PedidasORD = 0; END IF;-- SET Pedidas = PedidasPED + PedidasORD;-- RETURN (Pedidas); -- END @
De esta forma solo con una línea de llamada a la función tendremos la cantidad pedida de un articulo rápidamente, y si por algún motivo (cambio de criterio de la empresa o cambio de criterio en GEINFOR ERP) solo con ajustar la función tendríamos todo el código actualizado
SELECT CODIGO_ARTICULO, DENOMINACION, clase, FX_ART_DIMENSIONES(CODIGO_ARTICULO,'CLASE') AS CLASE_TXT, FX_ALM_PEDIDAS(CODIGO_ARTICULO,0) AS Pedidas_Alm_0, FX_ALM_PEDIDAS(CODIGO_ARTICULO,-1) AS Pedidas_Todos_alm FROM MAESTRO_DE_ARTICULOS
Cierre de código de funcion
Por si os ha llamado la atención, después del END hay un espacio y una @. Esto es intencionado. Podríamos poner un ; y funcionaría igual, pero más adelante veremos como preparar varias funciones y poder desplegarlas y actualizarlas de forma masiva y para ese apartado es importante que el acabar con @
Conclusión
Si juntamos esta entrada con la anterior de crear vistas podemos hacer unas vistas muy potentes, fáciles de mantener y reutilizar. Espero que os sea útil y no dudéis en comentar o mandar un mensaje con cualquier consulta, aportación o inquietud que tengáis…
… y si algo sale mal… La Culpa de Sistemas 😉
La Culpa de Sistemas 