Cómo está construida la base de datos de B1.

Casi todos los objetos de B1 son una tabla de cabecera con una o varias tablas de líneas. Las tablas de cabecera y de datos maestros empiezan por O; sus tablas de líneas usan las mismas letras con un número.

  • Datos maestros: OCRD socios de negocio (clave CardCode), OITM artículos (clave ItemCode), OITW stock por artículo y almacén, con OnHand, IsCommited y OnOrder.
  • Documentos: ORDR y RDR1 para pedidos de cliente, ODLN y DLN1 para entregas, OINV e INV1 para facturas de clientes. Compras sigue el mismo patrón, por ejemplo OPOR y POR1 para pedidos de compra.
  • Asientos: OJDT para la cabecera y JDT1 para las líneas, una línea por cuenta con Debit y Credit.

Dos números identifican un documento. DocEntry es la clave interna: las líneas la llevan y todos los vínculos entre documentos la usan. DocNum es el número que ve el usuario, tomado de la serie de numeración. Une por DocEntry, muestra DocNum. ObjType indica qué tipo de objeto es una fila: 17 es un pedido de cliente, 15 una entrega, 13 una factura de clientes, 16 una devolución, 20 una entrada de mercancías (goods receipt PO), 21 una devolución a proveedor. Volverás a ver estos números en los vínculos entre documentos.

Encontrar la tabla y el campo.

No hace falta saberse los nombres de memoria. En el cliente, elige View > System Information. Al pasar después el cursor sobre un campo, la barra de estado muestra la tabla y el campo que hay detrás, por ejemplo ORDR y CardCode en el campo de cliente de un pedido. La referencia de tablas de base de datos del SDK de SAP Business One recoge cada tabla con sus campos, tipos y valores válidos, como O y C para abierto y cerrado.

El Query Generator también ayuda: eliges las tablas a la izquierda y muestra sus campos; los campos que apuntan a otra tabla aparecen en negrita, y si arrastras uno al área de tablas, se añade esa tabla.

Joins: cabeceras, líneas y la cadena de documentos.

Las líneas se unen a su cabecera por DocEntry, los socios de negocio por CardCode y los artículos por ItemCode. Líneas abiertas de pedidos con el nombre del cliente: SELECT T0.DocNum, T0.CardCode, T2.CardName, T1.ItemCode, T1.OpenQty FROM ORDR T0 JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry JOIN OCRD T2 ON T2.CardCode = T0.CardCode WHERE T1.LineStatus = 'O'

Un documento copiado de otro recuerda de dónde vino cada línea: BaseType es el ObjType del documento base, BaseEntry su DocEntry y BaseLine el LineNum de la línea base. Una línea de factura que vino de una entrega se une así: FROM INV1 T0 JOIN DLN1 T1 ON T1.DocEntry = T0.BaseEntry AND T1.LineNum = T0.BaseLine WHERE T0.BaseType = 15. La guía del flujo de documentos explica cómo se copian los documentos.

  • DocStatus y LineStatus: O abierto, C cerrado. Un documento puede estar abierto con algunas líneas cerradas; filtra en el nivel que buscas.
  • OpenQty: la cantidad aún abierta en una línea; Quantity es la cantidad completa.
  • CANCELED: N para un documento normal; en entregas y facturas, Y marca un documento cancelado y C el documento de cancelación. CANCELED = 'N' deja fuera ambos.

Sumar con GROUP BY.

GROUP BY convierte filas en totales, y HAVING filtra sobre esos totales. Clientes con más de 10.000 facturados: SELECT T0.CardCode, SUM(T1.LineTotal) FROM OINV T0 JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry WHERE T0.CANCELED = 'N' GROUP BY T0.CardCode HAVING SUM(T1.LineTotal) > 10000. Para las ventas reales, resta los abonos (ORIN y RIN1) de la misma manera.

  • No sumes campos de cabecera tras unir las líneas. DocTotal está en la cabecera; unido a cinco líneas, cuenta cinco veces. Suma campos de línea, o suma la cabecera sin las líneas.
  • Por mes: YEAR(T0.DocDate) y MONTH(T0.DocDate) en el SELECT y en el GROUP BY funcionan en ambas plataformas.
  • Stock por almacén: SELECT ItemCode, WhsCode, OnHand FROM OITW WHERE OnHand > 0.
  • Partidas abiertas: en JDT1, BalDueDeb y BalDueCred recogen lo que sigue abierto en cada línea, y ShortName el código del socio o de la cuenta. Sumado por ShortName, da el saldo abierto por socio.

SAP HANA junto a SQL Server.

B1 funciona sobre Microsoft SQL Server o sobre SAP HANA. Las tablas y los campos son los mismos; el dialecto SQL no. Las diferencias que encontrarás primero:

  • Nombres: HANA convierte a mayúsculas los nombres sin comillas dobles, y los nombres de campo de B1 mezclan mayúsculas y minúsculas. En HANA escribes SELECT "CardCode" FROM "OCRD"; SQL Server acepta CardCode tal cual.
  • Valores vacíos: ISNULL(x, 0) en SQL Server, IFNULL(x, 0) en HANA. COALESCE funciona en ambos.
  • Primeras filas: SELECT TOP 10 funciona en ambos; HANA también tiene LIMIT 10 al final de la consulta.
  • Fechas: GETDATE(), DATEADD y DATEDIFF en SQL Server; CURRENT_DATE, ADD_DAYS y DAYS_BETWEEN en HANA.
  • Unir texto: + en SQL Server, || en HANA. CONCAT existe en ambos, pero en HANA admite exactamente dos argumentos.
  • Convertir: CAST funciona en ambos; SQL Server añade CONVERT, HANA TO_VARCHAR y TO_DATE.

Una consulta escrita para una plataforma suele fallar en la otra con un error de sintaxis. Si tu empresa puede pasar algún día a HANA, o un partner escribe consultas para varios clientes, quédate en lo posible con lo que entienden ambos.

Consultas en B1: Query Generator, Query Manager y variables.

Tools > Queries > Query Generator construye un SELECT con las tablas y campos que eliges; Tools > Queries > Query Wizard te guía por los mismos pasos. Tras Execute, elige Save en la ventana de resultados y da a la consulta un nombre y una categoría. Tools > Queries > Query Manager muestra las consultas guardadas por categoría; allí abres la estructura de la consulta para editar el SQL directamente. Las categorías se pueden vincular a grupos de autorización, para que no todos los usuarios vean todas las consultas.

Una variable hace que el usuario introduzca un valor al ejecutar la consulta. Escribe [%0] donde va el valor, y [%1], [%2] para más: SELECT T0.DocNum, T0.CardCode, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= [%0].

Estas herramientas son solo para leer. La ayuda lo dice claramente: el Query Generator está pensado para recuperar datos, no para actualizarlos, y el Query Wizard solo recupera datos. Mantenlo así en todas partes, también en una herramienta de base de datos fuera de B1: nunca escribas en las tablas de B1 con UPDATE, INSERT o DELETE. Eso se salta todas las comprobaciones de B1, y los documentos, el stock y los asientos pueden dejar de cuadrar. Los cambios se hacen desde el cliente o con una interfaz que aplica las reglas de B1, como el Service Layer o la DI API.

Lista de comprobación antes de ejecutar una consulta.

  • Solo SELECT. Nada en una consulta guardada modifica datos.
  • Conoce tu plataforma, HANA o SQL Server, y escribe para ella.
  • Une por DocEntry, nunca por DocNum.
  • Filtra por CANCELED y por el estado que buscas: documento o línea.
  • Pon una fecha o un estado en el WHERE: tablas como JDT1 e INV1 crecen cada día.
  • Comprueba un total contra un informe estándar de B1 antes de que alguien confíe en él.
  • Prueba las consultas pesadas primero en una empresa de pruebas, no en producción en horario laboral.
  • Usa categorías y autorizaciones: las consultas también muestran datos personales, como direcciones y empleados.

Cómo lo aborda KoonLearn.

KoonLearn, nuestra simulación de formación para SAP Business One, tiene un módulo de profundización llamado SQL and the Business One database. Escribes tú mismo consultas en un Query Manager reconstruido, sobre las tablas de la empresa de la historia, y cada resultado se compara con las filas esperadas. Sus secciones siguen los temas de esta guía: estructura de tablas, joins y flujo de documentos, agregación, HANA junto a SQL Server, consultas en Business One y reparar consultas rotas. En tu perfil eliges SAP HANA o SQL Server. El módulo termina con un test y un examen de módulo, con un resultado por sección.

Los precios y las opciones están en el portal de clientes. El módulo va más allá del examen: no es preparación para C_TB120 y no da certificado. Todo funciona en el navegador, sin licencia ni base de datos de SAP. El capítulo 03 Compras de la historia principal es gratis sin cuenta: empiézalo en learn.koonsystems.com. KoonLearn no está afiliado a SAP SE.

Fuentes: la documentación de ayuda de SAP Business One 10.0 (Query Generator, Query Generator Window, Query Wizard, Defining Conditions and Relations, Saving a Query, Changing a Saved Query); la referencia de tablas de base de datos de SAP Business One 10.0 (Displaying System Information in the Application; OCRD, OITM, OITW, ORDR/RDR1, ODLN/DLN1, OINV/INV1, OJDT/JDT1); SAP HANA SQL Reference Guide; la referencia de Transact-SQL de Microsoft; el resumen de capítulos de KoonLearn. SAP, SAP HANA y SAP Business One son marcas comerciales de SAP SE; SQL Server es una marca comercial de Microsoft.