Comment la base de données de B1 est construite.

Presque chaque objet de B1 est une table d'en-tête avec une ou plusieurs tables de lignes. Les tables d'en-tête et de données de base commencent par O ; leurs tables de lignes reprennent les mêmes lettres avec un chiffre.

  • Données de base : OCRD partenaires (clé CardCode), OITM articles (clé ItemCode), OITW stock par article et entrepôt, avec OnHand, IsCommited et OnOrder.
  • Documents : ORDR et RDR1 pour les commandes clients, ODLN et DLN1 pour les livraisons, OINV et INV1 pour les factures clients. Les achats suivent le même modèle, par exemple OPOR et POR1 pour les commandes d'achat.
  • Écritures : OJDT pour l'en-tête et JDT1 pour les lignes, une ligne par compte avec Debit et Credit.

Deux numéros identifient un document. DocEntry est la clé interne : les lignes la portent et chaque lien entre documents l'utilise. DocNum est le numéro que voit l'utilisateur, tiré de la série de numérotation. Joignez sur DocEntry, affichez DocNum. ObjType indique le type d'objet d'une ligne : 17 est une commande client, 15 une livraison, 13 une facture client, 16 un retour client, 20 une réception de marchandises (goods receipt PO), 21 un retour fournisseur. Vous retrouverez ces numéros dans les liens entre documents.

Trouver la table et le champ.

Inutile de connaître les noms par cœur. Dans le client, choisissez View > System Information. En passant ensuite le curseur sur un champ, la barre d'état affiche la table et le champ correspondants, par exemple ORDR et CardCode sur le champ client d'une commande. La référence des tables de la base de données dans le SDK de SAP Business One liste chaque table avec ses champs, types et valeurs valides, comme O et C pour ouvert et clôturé.

Le Query Generator aide aussi : vous choisissez les tables à gauche et il affiche leurs champs ; les champs qui renvoient à une autre table sont en gras, et en faire glisser un dans la zone des tables ajoute cette table.

Jointures : en-têtes, lignes et chaîne de documents.

Les lignes se joignent à leur en-tête sur DocEntry, les partenaires sur CardCode, les articles sur ItemCode. Lignes de commandes ouvertes avec le nom du client : 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 document copié d'un autre retient d'où vient chaque ligne : BaseType est l'ObjType du document de base, BaseEntry son DocEntry et BaseLine le LineNum de la ligne de base. Une ligne de facture issue d'une livraison se rejoint ainsi : FROM INV1 T0 JOIN DLN1 T1 ON T1.DocEntry = T0.BaseEntry AND T1.LineNum = T0.BaseLine WHERE T0.BaseType = 15. Le guide du flux de documents explique comment les documents sont copiés.

  • DocStatus et LineStatus : O ouvert, C clôturé. Un document peut être ouvert alors que certaines lignes sont clôturées ; filtrez au niveau voulu.
  • OpenQty : la quantité encore ouverte sur une ligne ; Quantity est la quantité totale.
  • CANCELED : N pour un document normal ; sur les livraisons et factures, Y marque un document annulé et C le document d'annulation. CANCELED = 'N' écarte les deux.

Totaliser avec GROUP BY.

GROUP BY transforme des lignes en totaux, et HAVING filtre sur ces totaux. Clients facturés pour plus de 10 000 : 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. Pour le vrai chiffre d'affaires, soustrayez de la même façon les avoirs (ORIN et RIN1).

  • Ne totalisez pas des champs d'en-tête après une jointure sur les lignes. DocTotal est dans l'en-tête ; joint à cinq lignes, il compte cinq fois. Totalisez des champs de ligne, ou l'en-tête sans les lignes.
  • Par mois : YEAR(T0.DocDate) et MONTH(T0.DocDate) dans le SELECT et le GROUP BY fonctionnent sur les deux plateformes.
  • Stock par entrepôt : SELECT ItemCode, WhsCode, OnHand FROM OITW WHERE OnHand > 0.
  • Postes ouverts : dans JDT1, BalDueDeb et BalDueCred contiennent ce qui reste ouvert sur chaque ligne, et ShortName le code du partenaire ou du compte. Totalisé par ShortName, cela donne le solde ouvert par partenaire.

SAP HANA à côté de SQL Server.

B1 tourne sur Microsoft SQL Server ou sur SAP HANA. Les tables et les champs sont les mêmes ; le dialecte SQL, non. Les différences que vous rencontrerez d'abord :

  • Noms : HANA convertit en majuscules les noms sans guillemets doubles, et les noms de champs de B1 mêlent majuscules et minuscules. Sur HANA, vous écrivez SELECT "CardCode" FROM "OCRD" ; SQL Server accepte CardCode tel quel.
  • Valeurs vides : ISNULL(x, 0) sur SQL Server, IFNULL(x, 0) sur HANA. COALESCE fonctionne sur les deux.
  • Premières lignes : SELECT TOP 10 fonctionne sur les deux ; HANA a aussi LIMIT 10 en fin de requête.
  • Dates : GETDATE(), DATEADD et DATEDIFF sur SQL Server ; CURRENT_DATE, ADD_DAYS et DAYS_BETWEEN sur HANA.
  • Concaténer du texte : + sur SQL Server, || sur HANA. CONCAT existe sur les deux, mais sur HANA il prend exactement deux arguments.
  • Convertir : CAST fonctionne sur les deux ; SQL Server ajoute CONVERT, HANA TO_VARCHAR et TO_DATE.

Une requête écrite pour une plateforme échoue généralement sur l'autre avec une erreur de syntaxe. Si votre entreprise peut passer un jour à HANA, ou si un partenaire écrit des requêtes pour plusieurs clients, tenez-vous autant que possible à ce que les deux comprennent.

Requêtes dans B1 : Query Generator, Query Manager et variables.

Tools > Queries > Query Generator construit un SELECT à partir des tables et champs que vous choisissez ; Tools > Queries > Query Wizard vous guide à travers les mêmes étapes. Après Execute, choisissez Save dans la fenêtre de résultat et donnez à la requête un nom et une catégorie. Tools > Queries > Query Manager affiche les requêtes enregistrées par catégorie ; vous y ouvrez la structure de la requête pour modifier le SQL lui-même. Les catégories peuvent être liées à des groupes d'autorisation, pour que chaque utilisateur ne voie pas toutes les requêtes.

Une variable fait saisir une valeur à l'utilisateur à l'exécution. Écrivez [%0] à l'endroit de la valeur, et [%1], [%2] pour d'autres : SELECT T0.DocNum, T0.CardCode, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= [%0].

Ces outils servent uniquement à lire. L'aide le dit clairement : le Query Generator est conçu pour extraire des données, pas pour les modifier, et le Query Wizard ne fait qu'extraire des données. Gardez cette règle partout, y compris dans un outil de base de données hors de B1 : n'écrivez jamais dans les tables de B1 avec UPDATE, INSERT ou DELETE. Cela contourne tous les contrôles de B1, et documents, stock et écritures peuvent ne plus concorder. Les modifications passent par le client ou par une interface qui applique les règles de B1, comme le Service Layer ou la DI API.

Liste de contrôle avant d'exécuter une requête.

  • Uniquement SELECT. Rien dans une requête enregistrée ne modifie de données.
  • Connaissez votre plateforme, HANA ou SQL Server, et écrivez pour elle.
  • Joignez sur DocEntry, jamais sur DocNum.
  • Filtrez sur CANCELED et sur le statut voulu : document ou ligne.
  • Mettez une date ou un statut dans le WHERE : des tables comme JDT1 et INV1 grossissent chaque jour.
  • Vérifiez un total contre un état standard de B1 avant que quelqu'un s'y fie.
  • Testez les requêtes lourdes d'abord dans une société de test, pas en production pendant les heures de travail.
  • Utilisez catégories et autorisations : les requêtes affichent aussi des données personnelles, comme des adresses et des employés.

Comment KoonLearn s'y prend.

KoonLearn, notre simulation de formation pour SAP Business One, propose un approfondissement intitulé SQL and the Business One database. Vous écrivez vous-même des requêtes dans un Query Manager reconstruit, sur les tables de l'entreprise de l'histoire, et chaque résultat est comparé aux lignes attendues. Ses sections suivent les sujets de ce guide : structure des tables, jointures et flux de documents, agrégation, HANA à côté de SQL Server, requêtes dans Business One, et réparation de requêtes cassées. Dans votre profil, vous choisissez SAP HANA ou SQL Server. L'approfondissement se termine par un quiz et un examen de module, avec un résultat par section.

Les prix et les options sont dans le portail client. L'approfondissement va au-delà de l'examen : ce n'est pas une préparation à C_TB120 et il ne donne pas de certificat. Tout fonctionne dans le navigateur, sans licence ni base de données SAP. Le chapitre 03 Achats de l'histoire principale est gratuit sans compte : commencez-le sur learn.koonsystems.com. KoonLearn n'est pas affilié à SAP SE.

Sources : la documentation d'aide 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 référence des tables de la base de données 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 référence Transact-SQL de Microsoft ; l'aperçu des chapitres de KoonLearn. SAP, SAP HANA et SAP Business One sont des marques de SAP SE ; SQL Server est une marque de Microsoft.