- phpMyAdminStarter (Microcontent)
- Gordon Du
- 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.
- 從零開始：Photoshop CC 2019中文版基礎教程
- Entity Framework Tutorial
- NetSuite OneWorld Implementation 2011 R2
- Photoshop CC 2017 淘寶美工設計實例教程
- Photoshop CC2017圖像處理實例教程
- Choosing an Open Source CMS: Beginner's Guide
- Photoshop CS6數碼照片處理入門到精通
- Professional Azure SQL Database Administration
- 好用，Excel數據處理高手
- 中文版SolidWorks 2018完全實戰技術手冊
- Cinema 4D R20完全學習手冊
- SPSS統計分析與應用
- SAP Business ONE Implementation
- Mastercam軟件應用技術基礎（X2版）
- 中文版AutoCAD 2014機械設計實戰從入門到精通