SAP Business One Procedimientos Almacenados: Reportes Reutilizables - imagen destacada del tutorial

Publicaciones

SAP Business One Procedimientos Almacenados: Reportes Reutilizables

Hector Pincheira

Hector Pincheira

CIO | CTO
Estrategia Digital & Innovación Tecnológica

Artículos

Hay un reporte en tu empresa que nadie quiere tocar. Son doscientas líneas de SQL pegadas dentro del Query Manager, con cinco subconsultas anidadas y un comentario de 2021 que dice «ojo, no cambiar esto». Cada vez que Finanzas pide una variante, alguien duplica la consulta y nace la versión 7. Los SAP Business One procedimientos almacenados existen precisamente para cortar esa espiral: mueven la lógica a la base de datos, donde se escribe una vez, se prueba una vez y se llama desde todas partes.

Por lo tanto, este tutorial no va de escribir una query más bonita. Va de dejar de escribir la misma query doce veces.

Por qué necesitas procedimientos almacenados en SAP Business One

El Query Manager es excelente para explorar. Como repositorio de lógica de negocio, en cambio, no da el ancho: no acepta parámetros con tipo, no permite variables intermedias, no valida nada antes de ejecutar y, sobre todo, no se puede reutilizar desde Crystal Reports, desde un add-on o desde un proceso nocturno sin copiar y pegar el texto completo.

Un procedimiento almacenado resuelve cuatro cosas de golpe. Primero, centraliza: la definición de «margen bruto» vive en un solo objeto y no en catorce consultas que se fueron desincronizando. Segundo, acepta parámetros tipados, de manera que una fecha mal escrita falla al llamar y no devuelve silencio. Tercero, el motor guarda el plan de ejecución, así que la segunda llamada es más rápida que la primera. Y cuarto, permite lógica real: condicionales, tablas temporales, bucles, validaciones.

Hay un beneficio menos obvio y es el que más me importa en la práctica. Cuando la lógica está en un procedimiento, puedes versionarla en Git como cualquier otro código fuente. El Query Manager guarda las consultas dentro de la tabla OUQR, enterradas en la base de datos, sin historial y sin diff. Un archivo .sql en un repositorio tiene autor, fecha y motivo del cambio.

SAP Business One procedimientos almacenados - código SQL en pantalla de desarrollo
La lógica de negocio deja de vivir pegada en una ventana y pasa a ser un objeto de base de datos versionable.

Prerrequisitos antes de crear tu primer procedimiento

  1. Acceso al motor de base de datos: SQL Server Management Studio (SQL Server) o SAP HANA Studio / HANA Database Explorer (HANA).
  2. Usuario con permiso CREATE PROCEDURE sobre el esquema de la base de datos de la compañía. En HANA el esquema se llama como la base (por ejemplo SBODEMOCL).
  3. Una base de pruebas. Nunca el productivo en el primer intento.
  4. Autorización «Ejecutar sentencias SQL que no sean SELECT» en los usuarios de SAP Business One que vayan a llamar el procedimiento desde el Query Manager (Gestión → Inicialización sistema → Autorizaciones).
  5. Versión: los ejemplos están probados en SAP Business One 10.0. Funcionan igual en 9.3.

Tutorial paso a paso: tu primer procedimiento almacenado en SAP Business One

Paso 1: define el problema en una frase

Antes de abrir el editor, escribe qué entra y qué sale. En este caso: entran dos fechas, sale el detalle de ventas por cliente con facturas, neto y margen bruto. Si no puedes escribirlo en una línea, el procedimiento va a salir mal.

Paso 2: escribe y valida el SELECT por separado

Ejecuta primero la consulta sola, con fechas fijas, hasta que los números cuadren contra un reporte conocido de SAP. Recién entonces la envuelves. Depurar un SELECT es fácil; depurar un procedimiento que además tiene parámetros, no tanto.

Paso 3: crea el procedimiento

Conéctate a la base de datos de la compañía (no a SBO-COMMON) y ejecuta el script correspondiente a tu motor. Usa un prefijo propio en el nombre, por ejemplo SP_HP_, para distinguir tus objetos de los nativos de SAP en una futura actualización.

Paso 4: pruébalo con distintos parámetros

Llámalo con un rango normal, con un rango vacío y con las fechas invertidas. El tercer caso es el que suele reventar.

Paso 5: publícalo donde se va a consumir

Desde el Query Manager con una llamada de una línea, desde Crystal Reports como origen de datos, o desde un job programado que deje el resultado en una tabla de usuario.

El código completo: SQL Server y SAP HANA

Procedimiento 1: ventas y margen por cliente

SQL Server

CREATE PROCEDURE SP_HP_VentasPorCliente
    @FechaDesde DATE,
    @FechaHasta DATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        T0."CardCode"                                   AS "Cliente",
        T0."CardName"                                   AS "Nombre",
        COUNT(DISTINCT T0."DocEntry")                   AS "Facturas",
        SUM(T1."LineTotal")                             AS "VentaNeta",
        SUM(T1."Quantity" * T1."StockPrice")            AS "CostoVenta",
        SUM(T1."LineTotal" - T1."Quantity" * T1."StockPrice") AS "MargenBruto"
    FROM OINV T0
    INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
    WHERE T0."DocDate" BETWEEN @FechaDesde AND @FechaHasta
      AND T0."CANCELED" = 'N'
    GROUP BY T0."CardCode", T0."CardName"
    ORDER BY SUM(T1."LineTotal") DESC;
END;

SAP HANA

CREATE PROCEDURE "SP_HP_VentasPorCliente" (
    IN p_FechaDesde DATE,
    IN p_FechaHasta DATE
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
    SELECT
        T0."CardCode"                                   AS "Cliente",
        T0."CardName"                                   AS "Nombre",
        COUNT(DISTINCT T0."DocEntry")                   AS "Facturas",
        SUM(T1."LineTotal")                             AS "VentaNeta",
        SUM(T1."Quantity" * T1."StockPrice")            AS "CostoVenta",
        SUM(T1."LineTotal" - T1."Quantity" * T1."StockPrice") AS "MargenBruto"
    FROM OINV T0
    INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
    WHERE T0."DocDate" BETWEEN :p_FechaDesde AND :p_FechaHasta
      AND T0."CANCELED" = 'N'
    GROUP BY T0."CardCode", T0."CardName"
    ORDER BY SUM(T1."LineTotal") DESC;
END;

Dos detalles que conviene fijar desde ya. En HANA los parámetros se referencian con dos puntos adelante (:p_FechaDesde) y el nombre del procedimiento va entre comillas dobles si quieres conservar mayúsculas y minúsculas. Además, SQL SECURITY INVOKER hace que el procedimiento se ejecute con los permisos de quien lo llama y no de quien lo creó, que es lo que uno quiere en un ERP multiusuario.

Procedimiento 2: días promedio de cobro con parámetro de salida

Este segundo ejemplo concentra casi todas las diferencias de dialecto que te vas a encontrar: cálculo de días entre fechas, manejo de nulos, fecha actual del sistema y parámetro de salida.

SQL Server

CREATE PROCEDURE SP_HP_DiasCobro
    @CardCode  NVARCHAR(15),
    @Meses     INT,
    @DiasProm  DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @DiasProm = ISNULL(AVG(CAST(DATEDIFF(DAY, T0."DocDate", T0."DocDueDate") AS DECIMAL(10,2))), 0)
    FROM OINV T0
    WHERE T0."CardCode" = @CardCode
      AND T0."CANCELED" = 'N'
      AND T0."DocDate" >= DATEADD(MONTH, -@Meses, GETDATE());
    SELECT @CardCode AS "Cliente", @DiasProm AS "DiasPromedioCobro";
END;

SAP HANA

CREATE PROCEDURE "SP_HP_DiasCobro" (
    IN  p_CardCode NVARCHAR(15),
    IN  p_Meses    INTEGER,
    OUT p_DiasProm DECIMAL(10,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
    SELECT IFNULL(AVG(TO_DECIMAL(DAYS_BETWEEN(T0."DocDate", T0."DocDueDate"), 10, 2)), 0)
      INTO p_DiasProm
    FROM OINV T0
    WHERE T0."CardCode" = :p_CardCode
      AND T0."CANCELED" = 'N'
      AND T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, - :p_Meses);
    SELECT :p_CardCode AS "Cliente", :p_DiasProm AS "DiasPromedioCobro" FROM DUMMY;
END;

Fíjate en el FROM DUMMY de la última línea: HANA no acepta un SELECT sin tabla de origen, mientras que SQL Server sí. Es la clase de detalle que te hace perder veinte minutos la primera vez que migras un procedimiento entre motores.

Cómo llamarlo desde el Query Manager

SQL Server

EXEC SP_HP_VentasPorCliente '[%0]', '[%1]';

SAP HANA

CALL "SP_HP_VentasPorCliente" ('[%0]', '[%1]');

Los marcadores [%0] y [%1] hacen que SAP Business One pida las dos fechas al usuario antes de ejecutar. De esta manera, el usuario final recibe una ventana de parámetros limpia y nunca ve las doscientas líneas de SQL que hay detrás. Recuerda que el usuario necesita la autorización de sentencias no-SELECT para que la llamada no sea rechazada.

SAP Business One procedimientos almacenados - consola de base de datos con consultas
Una llamada de una línea desde el Query Manager reemplaza cientos de líneas pegadas por todas partes.

Variaciones y casos de uso reales

Crystal Reports con parámetros. Al conectar un reporte contra un procedimiento, Crystal detecta los parámetros de entrada y arma solo los campos de selección. El filtrado ocurre en el servidor, no en el cliente, por lo que un reporte que antes traía 400.000 filas para mostrar 300 ahora trae 300.

Procesos nocturnos. Un procedimiento que calcula indicadores pesados (rotación, cobertura, cumplimiento) y escribe el resultado en una tabla de usuario (UDT). El dashboard consulta la UDT en milisegundos en lugar de recalcular a cada rato. Para el agendamiento, SQL Server Agent en el mundo Microsoft; en HANA, el planificador del sistema operativo invocando hdbsql.

Validaciones complejas. El clásico SP_TransactionNotification se vuelve ilegible cuando acumula veinte reglas. Una alternativa ordenada es dejar ahí solo el enrutamiento y mover cada regla a su propio procedimiento con nombre descriptivo.

Integraciones. El Service Layer no crea procedimientos, pero un servicio intermedio sí puede llamarlos y exponer el resultado como JSON para una app móvil o un portal de clientes.

Mejores prácticas y errores comunes con procedimientos almacenados en SAP Business One

Nunca escribas en tablas nativas de SAP. Esto me ha costado caro más de una vez: un UPDATE directo sobre OITM o sobre una tabla de documentos rompe la integridad que el ERP mantiene por detrás, invalida el soporte de SAP y puede dejar la base inconsistente de una forma que no se nota hasta el cierre de mes. Tus procedimientos leen de las tablas nativas y escriben, si acaso, en tablas de usuario.

Prefijo propio y siempre. SP_HP_, SP_EMPRESA_, lo que sea. Sin prefijo no hay forma de saber qué objetos son tuyos cuando llega el upgrade.

Un archivo .sql por procedimiento, en el repositorio. El código que solo vive dentro de la base de datos se pierde en la primera restauración apurada.

Documenta en la cabecera. Autor, fecha, propósito y un ejemplo de llamada en comentario. Tu yo de dentro de ocho meses lo va a agradecer.

No uses SELECT *. Si SAP agrega columnas en un Patch Level, tu reporte cambia de forma sin que nadie lo toque.

Cuidado con los nulos. Un AVG sobre un conjunto vacío devuelve NULL, no cero, y eso baja hasta el indicador del directorio convertido en celda en blanco. Envuelve siempre con ISNULL o IFNULL.

Permisos en HANA. El error más frecuente al migrar no es de sintaxis: es que el usuario de la aplicación no tiene EXECUTE sobre el procedimiento. Se otorga con GRANT EXECUTE ON "ESQUEMA"."SP_HP_VentasPorCliente" TO "USUARIO".

Me molesta una decisión de diseño de SAP acá, y lo digo con todas sus letras: el Query Manager guarda las consultas en OUQR sin ningún control de versiones, sin autor y sin fecha de modificación. Para auditar qué procedimientos existen y cuándo cambiaron, hay que ir directo al diccionario del motor.

SQL Server

SELECT
    p."name"            AS "Procedimiento",
    p."create_date"     AS "Creado",
    p."modify_date"     AS "Modificado"
FROM sys.procedures p
WHERE p."name" LIKE 'SP_HP_%' ESCAPE ''
ORDER BY p."modify_date" DESC;

SAP HANA

SELECT
    P."PROCEDURE_NAME"  AS "Procedimiento",
    P."CREATE_TIME"     AS "Creado"
FROM SYS."PROCEDURES" P
WHERE P."SCHEMA_NAME" = CURRENT_SCHEMA
  AND P."PROCEDURE_NAME" LIKE 'SP_HP_%' ESCAPE ''
ORDER BY P."CREATE_TIME" DESC;
SAP Business One procedimientos almacenados - servidor de base de datos corporativo
El plan de ejecución queda en caché: la segunda llamada siempre es más rápida que la primera.

Recursos oficiales de SAP

Conclusión: deja de pegar SQL y empieza a construir activos

Una consulta pegada en una ventana es un gasto que se repite cada vez que alguien la necesita. Un procedimiento almacenado, bien nombrado, documentado y versionado, es un activo de la empresa: se prueba una vez, se consume desde Crystal, desde el Query Manager y desde cualquier integración, y sobrevive a la rotación de personal. La diferencia entre un área de TI que apaga incendios y una que construye capacidad se ve exactamente acá, en si el conocimiento del negocio queda en la cabeza de alguien o queda escrito en un objeto que cualquiera puede ejecutar mañana.

Parte por uno. El reporte de ventas que todos piden los lunes es un buen candidato: tiene parámetros claros, lo consume mucha gente y hoy vive en tres versiones distintas que no cuadran entre sí.

¿Quieres el resto de la serie? En el Círculo Estratégico publico los tutoriales completos con el código probado en SQL Server y HANA, listos para copiar y adaptar a tu base. Revisa la categoría de SAP Business One en hectorpincheira.com y cuéntame en los comentarios qué proceso de tu empresa estás pensando en convertir en procedimiento.

Mas Leídos

QlikSense Hierarchy: plan de cuentas padre-hijo expandido por niveles
QlikSense Hierarchy: el Plan de Cuentas que No Suma
QlikSense Sum TOTAL: participación de cada familia sobre el total
QlikSense Sum TOTAL: el Porcentaje que Cambia al Filtrar
SAP Business One Revalorización de Inventario - imagen destacada del tutorial
SAP Business One Revalorización de Inventario: Corrige Costos Mal Cargados
Sala de sesiones vacía con bancas de madera: el contrapeso que exigía estar presente para ejercerse
El Veto que Se Decía en Persona: tus Decisiones sin Contrapeso
SAP Business One personalizaciones - imagen destacada del tutorial de auditoria pre-upgrade
SAP Business One Personalizaciones: Auditoría Pre-Upgrade
SAP Business One Concentración de Clientes - imagen destacada del tutorial
SAP Business One Concentración de Clientes: Mide tu Riesgo con SQL

ARTICULOS RELACIONADOS