跑腿帮送

書名： phpMyAdminStarter (Microcontent)
作者名： Gordon Du
本章字數(shù)： 645字
更新時間： 2021-04-02 19:18:26

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 number-ending 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:

ADM: Administration
ATC: Attachments
CPR: Contact Persons
CRD: Business Partners
DLN: Delivery Notes
HEM: Employees
INV: Sales Invoices
ITM: Items
ITT: Product Trees (Bill of Materials)
OPR: Sales Opportunities
PCH: Purchase Invoices
PDN: Goods Receipt PO
POR: Purchase Orders
QUT: Sales Quotations
RDR: Sales Orders
RIN: Sales Credit Notes
RPC: Purchase Credit Notes
SLP: Sales Employees
USR: Users
WOR: Production Orders
WTR: Stock Transfers

"O" tables

All tables starting with "O" refer to master tables. O here represents Object. For example:

OITM: Items Master
OCRD: Business Partners Master
OSLP: Sales Employee

"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.

官术网_书友最值得收藏!

phpMyAdminStarter (Microcontent)