SQL Query Basics in SAP Business One

Packt Publishing

Exploit one of the most powerful features of SAP Business One with this practical guide to mastering SQL Queries. With the skills to quickly acquire business intelligence, your enterprise can gain the competitive edge.

Who can benefit from using SQL Queries in SAP Business One?

There are many different groups of SAP Business One users who may need this tool.

To my knowledge, there is no standard organization chart for Small and Midsized enterprises. Most of them are different. You may often find one person that handles more than one role.

You may check the following list to see if anything applies to you:

Do you need to check specific sales results over certain time periods, for certain areas or certain customers?

Do you want to know who the top vendors from certain locations for certain materials are?

Do you have dynamic updated version of your sales force performance in real time?

Do you often check if approval procedures are exactly matching your expectations?

Have you tried to start building your SQL query but could not get it done properly?

Have you experienced writing SQL query but the results are not always correct or up to your expectations?

Consultant

If you are an SAP Business One consultant, you have probably mastered SQL query already. However, if that is not the case, this would be a great help to extend your consulting power. It will probably become a mandatory skill in the future that any SAP Business One consultant should be able to use SQL query.

Developer

If you are an SAP Business One add-on developer, these skills will be good additions to your capabilities. You may find this useful even in some other development work like coding or programming. Very often you need to embed SQL query to your codes to complete your Software Development Kit (SDK) project.

SAP Business One end user

If you are simply a normal SAP Business One end user, you may need this more. This is because SQL query usage is best applied for the companies who have SAP Business One live data. Only you as the end users know better than anyone else what you are looking for to make Business Intelligence a daily routine job. It is very important for you to have an ability to create a query report so that you can map your requirement by query in a timely manner.

SQL query and related terms

Before going into the details of SQL query, I would like to briefly introduce some basic database concepts because SQL is a database language for managing data in Relational Database Management Systems (RDBMS).

RDBMS

RDBMS is a Database Management System that is based on the relation model. Relational here is a key word for RDBMS. You will find that data is stored in the form of Tables and the relationship among the data is also stored in the form of tables for RDBMS.

Table

Table is a key component within a database. One table or a group of tables represent one kind of data. For example, table OSLP within SAP Business One holds all Sales Employee Data. Tables are two-dimensional data storage place holders. You need to be familiar with their usage and their relationships with each other. If you are familiar with Microsoft Excel, the worksheet in Excel is a kind of two-dimensional table.

Table is also one of the most often used concepts. Relationships between each table may be more important than tables themselves because without relation, nothing could be of any value. One important function within SAP Business One is allowing User Defined Table (UDT). All UDTs start with "@".

Field

A field is the lowest unit holding data within a table. A table can have many fields. It is also called a column. Field and column are interchangeable. A table is comprised of records, and all records have the same structure with specific fields. One important concept in SAP Business One is User Defined Field (UDF). All UDFs start with U_.

SQL

SQL is often referred to as Structured Query Language. It is pronounced as S-Q-L or as the word "Sequel". There are many different revisions and extensions of SQL. The current revision is SQL: 2008, and the first major revision is SQL-92. Most of SQL extensions are built on top of SQL-92.

T-SQL

Since SAP Business One is built on Microsoft SQL Server database, SQL here means Transact-SQL or T-SQL in brief. It is a Microsoft's/Sybase's extension of general meaning for SQL.

Subsets of SQL

There are three main subsets of the SQL language:

Data Control Language (DCL)

Data Definition Language (DDL)

Data Manipulation Language (DML)

Each set of the SQL language has a special purpose:

DCL is used to control access to data in a database such as to grant or revoke specified users' rights to perform specified tasks.

DDL is used to define data structures such as to create, alter, or drop tables.

DML is used to retrieve and manipulate data in the table such as to insert, delete, and update data. Select, however, becomes a special statement belonging to this subset even though it is a read-only command that will not manipulate data at all.

Query

Query is the most common operation in SQL. It could refer to all three SQL subsets.

You have to understand the risks of running any Add, Delete, or Update queries that could potentially alter system tables even if they are User Defined Fields. Only SELECT query is legitimate for SAP Business One system table.

Data dictionary

In order to create working SQL queries, you not only need to know how to write it, but also need to have a clear view regarding the relationship between tables and where to find the information required. As you know, SAP Business One is built on Microsoft SQL Server. Data dictionary is a great tool for creating SQL queries. Before we start, a good Data Dictionary is essential for the database. Fortunately, there is a very good reference called SAP Business One Database Tables Reference readily available through SAP Business One SDK help Centre. You can find the details in the following section.

SAP Business One—Database tables reference

The database tables reference file named REFDB.CHM is the one we are looking for. SDK is usually installed on the same server as the SAP Business One database server. Normally, the file path is: X:\Program Files\SAP\SAP Business One SDK\Help. Here, "X" means the drive where your SAP Business One SDK is installed. The help file looks like this:

In this help file, we will find the same categories as the SAP Business One menu with all 11 modules. The tables related to each module are listed one by one. There are tree structures in the help file if the header tables have row tables. Each table provides a list of all the fields in the table along with their description, type, size, related tables, default value, and constraints.

Naming convention of tables for SAP Business One

To help you understand the previous mentioned data dictionary quickly, we will be going through the naming conventions for the table in SAP Business One.

Three letter words

Most tables for SAP Business One have four letters. The only exceptions are numberending tables, if the numbers are greater than nine. Those tables will have five letters. To understand table names easily, there is a three letter abbreviation in SAP Business One. Some of the commonly used abbreviations are listed as follows:

"O" tables

"A" tables

Most tables starting with "A" may mean historical log tables. A here represents Archive. For example:

AITM: Items—History

ACRD: Business Partners—History

AUSR: Archive Users—History

Document header tables

These are special O tables with the exact same structure. They can be tables related to Sales or Purchase. These are called Marketing Documents. These also include most Inventory transaction tables. Some examples are:

OINV: A/R Invoice Header

OPCH: A/P Invoice Header

OIGN: Goods Receipt Header

Document line tables

All tables ending with a number refer to document line detail tables or subtables for the master table. Numbers here could refer to different properties of the header tables.

INV1: A/R Invoice Row

PCH1: A/P Invoice Row

IGN1: Goods Receipt Row

INV2: A/R Invoice—Row Expense

Important table examples

Some specific tables very important for query building are listed here:

OJDT-Journal Entry: This table includes all financial journal entries no matter whether they are automatically posted or manually posted.

OINM-Warehouse Journal: This table includes all inventory-related transactions. It is a single point to check everything in relation to your inventory (or stock). It becomes a view in the new version. This view must be queried very carefully.

ADOC-Document History: This table includes all document history. However, it is wrongly named in the documentation, "Invoice History" table in the help file.

Table links—the key for the right query

Table links are fundamental for query building. You will see some different links in this section. To understand table links, you need to know more about table structures.

Primary key

Every table has a primary key. Some of the tables have foreign keys too. All those keys are used for the index. Docentry is a typical primary key to link OXXX with XXXn document tables. For example, Docentry is a common key field to link OPOR with POR1, POR2 to POR12.

A primary key can be one or more fields. For a simple table one key field would be good enough. For a complicated table, two or more fields for primary key are not rare.

A primary key has to be unique within the same table. This key will not allow NULL value—that is, an empty field or a field with no data.

Foreign key

A foreign key is usually used to link to some other table's primary key. This field will be updated whenever the other table record has changed.

Although, you could link any fields between tables, if the field is not NULL, you should try to use key link wherever possible in order to increase the database performance.

Example of table links within SAP Business One

To be clearer about the link, here are a few table link examples:

OITM-Items table and ITM1-Items Prices table: These two tables are linked through ItemCode field. Both tables have the same field name to link. It is not one-to-one but one-to-many relationships. One Item Code in item master may have more than one item price associated.

OITT-Product Tree table and ITT1-Product Tree Child Items: These two tables are linked through Code field in OITT and Father field in ITT1. These tables are used for Bill of Materials.

OCRD-Business Partner table and OSLP-Sales Employee table: These two tables are linked through the same name field SlpCode. In the second table, SlpCode is the primary key for OSLP. On the other hand, it is a foreign key in the first table OCRD.

Base tables versus target tables

Base tables and target tables are special linked tables within SAP Business One. They are the most often used linked tables for SQL queries too.

You may find most of them related to "Sales-A/R" and "Purchase-A/P" documents or so-called "Marketing Documents".

(Move the mouse over the image to enlarge.)

Marketing documents may not have base tables or target tables. From the previous screenshot, you could clearly find that the Base Document and Target Document are available to this Sales Order. To get the Base Document, you may click on the "left arrow icon" or use the shortcut key Ctrl+N. To get the Target Document, you may click on the "right arrow icon" or use the shortcut key Ctrl+T. Only when the base table or target table is available to the current document, will you find the menu items and icons in active status. Otherwise, both icons and menu items are grayed out.

From the terms "Base" and "Target", it is clear that the target table can be based upon the base table.

One table could be based on different types of tables:

From this demonstration, you could get a clear picture about the relationship between Base Document (table) and Target Document (table). A specific pair of Purchase Order and Good Receipt PO tables is shown here. This concept applies to all document type tables. Here is a list of commonly used base-target pairs; they are not inclusive. You may find more, but the following are the most frequently used ones:

Base Table

Target Table

OQUT-Sales Quotation

ORDR-Sales Order

OQUT-Sales Quotation

ODLN-Delivery

OQUT-Sales Quotation

OINV-A/R Invoice

ORDR-Sales Order

ODLN-Delivery

ORDR-Sales Order

OINV-A/R Invoice

ODLN-Delivery

ORDN-Returns

ODLN-Delivery

OINV-A/R Invoice

ORDN-Returns

ORIN-A/R Credit Note

ODLN-A/R Invoice

ORIN-A/R Credit Note

OPOR-Purchase Order

OPDN-Goods Receipt PO

OPOR-Purchase Order

OPCH-A/P Invoice

OPDN-Goods Receipt PO

ORPD-Goods return

OPDN-Goods Receipt PO

OPCH-A/P Invoice

ORPD-Goods return

ORPC-A/P Credit Note

OPCH-A/P Invoice

ORPC-A/P Credit Note

I have omitted the details for the link. Actually, you will find that all the links exist on the first child table or so-called row table for the header table, such as QUT1 instead of OQUT.

The linking fields are very clear. For example:

BaseEntry in the target table refers to the base table's DocEntry

BaseType refers to the types of the base table

BaseRef is usually linked to DocNum field in the base table

BaseLine will be the line number in the base line table

TargetEntry in the base table refers to the target table's DocEntry

TargetType refers to the types of the target table

Keeping it simple—The key to build a good query

Simplicity is in need everywhere in the current changing world. Wherever you make things complicated, you may find yourself in an awkward position to compete with others.

My slogan is: simple, simpler, the simplest.

I have a habit in query building: the last step for any new query would be checking to see if it is the simplest one. In this way, "keep it simple" would not only be kept in the already built query, but also helps new queries to be the simplest in the beginning.

By keeping a query as simple as possible, it will ensure that the system performance is not affected. It will also be a great help to the troubleshooting process. A short checklist for simplicity is as follows:

Other queries: Are there any other queries doing a similar job, and if yes, why does the new query need to be built?

Tables: Are there any tables that have not been used for the query?

Fields: Are there any fields that have not been used for the query?

Conditions: Are there any condition overlaps?

The list can be much longer. The meaning behind it is clear: there is a never ending battle to get rid of complications.

When you try this method and it becomes a routine, you will find that query building becomes an enjoyable process.

Summary

In this article, you have been identified to be an appropriate reader who needs this information, supposing that you read through the beginning article and still want to read more. You have been given all the basic concepts such as RDBMS, Table, SQL, T-SQL, SQL Subsets, and Query. You also get the idea of what the strict meanings of "SQL" and "Query" are. By going deeper into discussing table relationships, you gained a bigger picture of SAP Business One's database structure and tables' naming conventions. You also learned about base tables versus target tables in SAP Business One. The "Keep it simple" principle has been emphasized in the last section of the article. You are advised to use it whenever you practice your own queries.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.