Hoe de database van B1 is opgebouwd.

Bijna elk object in B1 is een koptabel met een of meer regeltabellen. Kop- en stamgegevenstabellen beginnen met een O; hun regeltabellen hebben dezelfde letters met een cijfer.

  • Stamgegevens: OCRD zakenpartners (sleutel CardCode), OITM artikelen (sleutel ItemCode), OITW voorraad per artikel en magazijn, met OnHand, IsCommited en OnOrder.
  • Documenten: ORDR en RDR1 voor verkooporders, ODLN en DLN1 voor leveringen, OINV en INV1 voor verkoopfacturen. Inkoop volgt hetzelfde patroon, bijvoorbeeld OPOR en POR1 voor inkooporders.
  • Journaalposten: OJDT voor de kop en JDT1 voor de regels, één regel per rekening met Debit en Credit.

Twee nummers identificeren een document. DocEntry is de interne sleutel: de regels dragen hem, en elke koppeling tussen documenten gebruikt hem. DocNum is het nummer dat de gebruiker ziet, uit de nummerreeks. Koppel op DocEntry, toon DocNum. ObjType zegt wat voor object een rij is: 17 is een verkooporder, 15 een levering, 13 een verkoopfactuur, 16 een retour, 20 een goederenontvangst (goods receipt PO), 21 een goederenretour. Die nummers kom je weer tegen in de koppelingen tussen documenten.

De tabel en het veld vinden.

Je hoeft de namen niet uit je hoofd te kennen. Kies in de client View > System Information. Beweeg je daarna de cursor over een veld, dan toont de statusbalk de tabel en het veld erachter, bijvoorbeeld ORDR en CardCode op het klantveld van een verkooporder. De databasetabellenreferentie in de SAP Business One SDK geeft elke tabel met zijn velden, typen en geldige waarden, zoals O en C voor open en gesloten.

De Query Generator helpt ook: je kiest links de tabellen en hij toont hun velden; velden die naar een andere tabel wijzen staan vet, en sleep je er een naar het tabellenvak, dan komt die tabel erbij.

Joins: koppen, regels en de documentketen.

Regels koppel je aan hun kop op DocEntry, zakenpartners op CardCode, artikelen op ItemCode. Openstaande verkooporderregels met de klantnaam: 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'

Een document dat van een ander is gekopieerd, onthoudt per regel waar die vandaan kwam: BaseType is het ObjType van het basisdocument, BaseEntry zijn DocEntry en BaseLine het LineNum van de basisregel. Een factuurregel die uit een levering kwam, koppel je zo terug: FROM INV1 T0 JOIN DLN1 T1 ON T1.DocEntry = T0.BaseEntry AND T1.LineNum = T0.BaseLine WHERE T0.BaseType = 15. De gids over de documentstroom legt uit hoe documenten worden gekopieerd.

  • DocStatus en LineStatus: O open, C gesloten. Een document kan open zijn terwijl sommige regels gesloten zijn; filter op het niveau dat je bedoelt.
  • OpenQty: het aantal dat op een regel nog openstaat; Quantity is het volledige aantal.
  • CANCELED: N voor een gewoon document; op leveringen en facturen markeert Y een geannuleerd document en C het annuleringsdocument. CANCELED = 'N' laat beide weg.

Optellen met GROUP BY.

GROUP BY maakt van rijen totalen, en HAVING filtert op die totalen. Klanten met meer dan 10.000 gefactureerd: 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. Voor de echte omzet trek je de creditnota's (ORIN en RIN1) er op dezelfde manier af.

  • Tel geen kopvelden op na een join met de regels. DocTotal staat op de kop; gekoppeld aan vijf regels telt hij vijf keer mee. Tel regelvelden op, of tel de kop op zonder de regels.
  • Per maand: YEAR(T0.DocDate) en MONTH(T0.DocDate) in de SELECT en de GROUP BY werken op beide platforms.
  • Voorraad per magazijn: SELECT ItemCode, WhsCode, OnHand FROM OITW WHERE OnHand > 0.
  • Openstaande posten: in JDT1 staat in BalDueDeb en BalDueCred wat er per regel nog openstaat, en in ShortName de code van de zakenpartner of de rekening. Opgeteld per ShortName geeft dat het open saldo per relatie.

SAP HANA naast SQL Server.

B1 draait op Microsoft SQL Server of op SAP HANA. De tabellen en velden zijn hetzelfde; het SQL-dialect niet. De verschillen die je het eerst tegenkomt:

  • Namen: HANA zet namen zonder dubbele aanhalingstekens om naar hoofdletters, en de veldnamen van B1 hebben gemengde hoofd- en kleine letters. Op HANA schrijf je SELECT "CardCode" FROM "OCRD"; SQL Server neemt CardCode zoals het is.
  • Lege waarden: ISNULL(x, 0) op SQL Server, IFNULL(x, 0) op HANA. COALESCE werkt op beide.
  • Eerste rijen: SELECT TOP 10 werkt op beide; HANA kent ook LIMIT 10 aan het eind van de query.
  • Datums: GETDATE(), DATEADD en DATEDIFF op SQL Server; CURRENT_DATE, ADD_DAYS en DAYS_BETWEEN op HANA.
  • Tekst samenvoegen: + op SQL Server, || op HANA. CONCAT bestaat op beide, maar op HANA neemt het precies twee argumenten.
  • Omzetten: CAST werkt op beide; SQL Server heeft daarnaast CONVERT, HANA TO_VARCHAR en TO_DATE.

Een query die voor het ene platform is geschreven, faalt op het andere meestal met een syntaxfout. Kan je bedrijf ooit naar HANA gaan, of schrijft een partner query's voor meerdere klanten, houd je dan waar het kan aan wat beide begrijpen.

Query's in B1: Query Generator, Query Manager en variabelen.

Tools > Queries > Query Generator bouwt een SELECT uit de tabellen en velden die je kiest; Tools > Queries > Query Wizard loopt dezelfde stappen met je door. Na Execute kies je Save in het resultaatvenster en geef je de query een naam en een categorie. Tools > Queries > Query Manager toont de opgeslagen query's per categorie; daar open je de querystructuur om de SQL zelf te wijzigen. Categorieën kun je aan autorisatiegroepen koppelen, zodat niet elke gebruiker elke query ziet.

Een variabele laat de gebruiker een waarde invullen als de query draait. Schrijf [%0] waar de waarde moet komen, en [%1], [%2] voor meer: SELECT T0.DocNum, T0.CardCode, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= [%0].

Deze hulpmiddelen zijn alleen om te lezen. De help zegt het duidelijk: de Query Generator is bedoeld om gegevens op te halen, niet om ze bij te werken, en de Query Wizard haalt alleen gegevens op. Houd dat overal zo, ook in een databasetool buiten B1: schrijf nooit met UPDATE, INSERT of DELETE in de tabellen van B1. Dat slaat elke controle van B1 over, en documenten, voorraad en journaalposten kunnen niet meer kloppen. Wijzigingen gaan via de client of via een koppeling die de regels van B1 toepast, zoals de Service Layer of de DI API.

Checklist voordat je een query draait.

  • Alleen SELECT. Niets in een opgeslagen query wijzigt gegevens.
  • Weet op welk platform je zit, HANA of SQL Server, en schrijf daarvoor.
  • Koppel op DocEntry, nooit op DocNum.
  • Filter op CANCELED en op de status die je bedoelt: document of regel.
  • Zet een datum of status in de WHERE: tabellen als JDT1 en INV1 groeien elke dag.
  • Controleer een totaal tegen een standaardrapport van B1 voordat iemand erop vertrouwt.
  • Probeer zware query's eerst in een testbedrijf, niet in productie onder werktijd.
  • Gebruik categorieën en autorisaties: query's tonen ook persoonsgegevens, zoals adressen en medewerkers.

Hoe KoonLearn het aanpakt.

KoonLearn, onze trainingssimulatie voor SAP Business One, heeft een verdieping SQL and the Business One database. Je schrijft zelf query's in een nagebouwde Query Manager, op de tabellen van het bedrijf uit het verhaal, en elk resultaat wordt vergeleken met de verwachte rijen. De secties volgen de onderwerpen van deze gids: tabelstructuur, joins en documentstroom, aggregatie, HANA naast SQL Server, query's in Business One, en kapotte query's repareren. In je profiel kies je SAP HANA of SQL Server. De verdieping eindigt met een toets en een module-examen, met een uitslag per sectie.

Prijzen en opties staan in het klantportaal. De verdieping gaat verder dan het examen: het is geen voorbereiding op C_TB120 en geeft geen certificaat. Alles draait in de browser, zonder SAP-licentie of database. Hoofdstuk 03 Inkoop uit het hoofdverhaal is gratis zonder account: begin het op learn.koonsystems.com. KoonLearn is niet verbonden aan SAP SE.

Bronnen: SAP Business One 10.0-helpdocumentatie (Query Generator, Query Generator Window, Query Wizard, Defining Conditions and Relations, Saving a Query, Changing a Saved Query); de databasetabellenreferentie van 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; Microsoft Transact-SQL-referentie; het hoofdstukoverzicht van KoonLearn. SAP, SAP HANA en SAP Business One zijn handelsmerken van SAP SE; SQL Server is een handelsmerk van Microsoft.