SQL Queries For SAP Business One
If you are interested in writing your own SQL queries for SAP Business One the following will assist you.
Basics of SQL Writing for SAP Business One
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 acolumn. 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.
The above extract provides the basics of SQL, to read the full article, read it here.
SAP Business One table for SQL Report writing
Administration
| DunningLetters | Dunning levels definition | ODUN |
| Recipients | Message recipient list | AOB1 |
| Messages | Messages | OALR |
| Currencies | Currency codes | OCRN |
| UserPermissionTree | User authorizations for add-on forms | OUPT |
| UserPermissionForms | User permission forms | UPT1 |
| Users | Users of SAP Business One application | OUSR |
| UserPermission | User authorizations | USR3 |
| AddonChecking(SBO-COMMON Database Only runned this table) | Overall Addon Path Defining | SARI |
| User Queries | User Queries | OUQR |
Finance
| Budget | Budget management | OBGT |
| Budget_Lines | Budget item details of an account | BGT1 |
| BudgetDistribution | Budget distribution methods | OBGD |
| BudgetScenarios | Budget scenarios | OBGS |
| ChartOfAccounts | G/L accounts | OACT |
| JournalVouchers | Journal vouchers | OBTD |
| JournalEntries_Lines | Journal transaction – rows | JDT1 |
| JournalEntries | Journal transaction | OJDT |
| SalesTaxAuthorities | Sales tax jurisdictions | OSTA |
| SalesTaxAuthoritiesTypes | Sales tax authority types | OSTT |
| SalesTaxCodes | Inclusive sales tax codes | OSTC |
Marketing Documents and Receipts
(Sales Opportunities, Sales A/R, and Purchasing A/P)
| Document_Lines | Delivery note rows | DLN1 |
| Document_LinesAdditionalExpenses | Delivery note rows – expenses | DLN2 |
| DocumentsAdditionalExpenses | Delivery note – expenses | DLN3 |
| AdditionalExpenses | Defines additional expenses | OEXD |
| Document_Lines | Draft – rows | DRF1 |
| Document_LinesAdditionalExpenses | Draft rows – expenses | DRF2 |
| DocumentsAdditionalExpenses | Draft – expenses | DRF3 |
| Document_Lines | Invoice – rows | INV1 |
| Document_LinesAdditionalExpenses | Invoice rows – expenses | INV2 |
| DocumentsAdditionalExpenses | Invoice – expenses | INV3 |
| Document (oDeliveryNote) | Delivery – header | ODLN |
| Documents | Drafts – header | ODRF |
| Documents (oInvoice) | Invoice – header | OINV |
| Document (oPurchaseInvoices) | AP invoice – header | OPCH |
| Document (oPurchaseDeliveryNotes) | Goods receipt purchase order – header | OPDN |
| Document (oPurchaseOrders) | Purchase order – header | OPOR |
| Document (oQuotation) | Quotation – header | OQUT |
| Document (oReturn) | Returns – header | ORDN |
| Document (oOrder) | Order – header | ORDR |
| Document (oCreditNote) | AR credit memo – header | ORIN |
| Document (oPurchaseCreditNotes) | AP credit memo – header | ORPC |
| Document (oPurchaseReturns) | Goods return – header | ORPD |
| Document_Lines | AP invoice – rows | PCH1 |
| Document_LinesAdditionalExpenses | AP invoice rows – expenses | PCH2 |
| DocumentsAdditionalExpenses | AP invoice – expenses | PCH3 |
| Document_Lines | Goods receipt purchase order – row | PDN1 |
| Document_LinesAdditionalExpenses | Goods receipt purchase order rows – expenses | PDN2 |
| DocumentsAdditionalExpenses | Goods receipt purchase order – expenses | PDN3 |
| Document_Lines | Purchase order – rows | POR1 |
| Document_LinesAdditionalExpenses | Purchase order rows – expenses | POR2 |
| DocumentsAdditionalExpenses | Purchase order – expenses | POR3 |
| Document_Lines | Quotation – rows | QUT1 |
| Document_LinesAdditionalExpenses | Quotation rows – expenses | QUT2 |
| DocumentsAdditionalExpenses | Quotation – expenses | QUT3 |
| Document_Lines | Returns – rows | RDN1 |
| Document_LinesAdditionalExpenses | Return rows – expenses | RDN2 |
| DocumentsAdditionalExpenses | Returns – expenses | RDN3 |
| Document_Lines | Order – rows | RDR1 |
| Document_LinesAdditionalExpenses | Order rows – expenses | RDR2 |
| DocumentsAdditionalExpenses | Order – expenses | RDR3 |
| Document_Lines | Credit memo – rows | RIN1 |
| Document_LinesAdditionalExpenses | Credit memo rows -Expenses | RIN2 |
| DocumentsAdditionalExpenses | Credit memo – expenses | RIN3 |
| Document_Lines | AP credit memo – rows | RPC1 |
| Document_LinesAdditionalExpenses | AP credit memo rows – expenses | RPC2 |
| DocumentsAdditionalExpenses | AP credit memo – expenses | RPC3 |
| Document_Lines | Goods return – rows | RPD1 |
| Document_LinesAdditionalExpenses | Goods return rows – expenses | RPD2 |
| DocumentsAdditionalExpenses | Goods return – expenses | RPD3 |
| BatchNumbers | Batch number for item | OIBT |
| Industries | Industries list for sales opportunities | OOND |
| Relationships | Relationships for sales opportunities | OORL |
| SerialNumbers | Serial numbers for items | OSRI |
| WithholdingTaxData | Withholding tax data | INV5 |
| TaxInvoices (botit_Invoice) | Master header data of a tax invoice of sales document | OTSI |
| TaxInvoice_Lines (botit_Invoice) | Tax invoice of sales document – rows | TSI1 |
| TaxInvoices (botit_Payment) | Master header data of a tax invoice of purchase document | OTPI |
| TaxInvoice_Lines (botit_Payment) | Tax invoice of purchase document – rows | TPI1 |
| TaxInvoices (botit_JournalEntry) | Master header data of a tax invoice of journal entry document | OTXD |
| TaxInvoice_Lines (botit_JournalEntry) | Tax invoice of journal entry document – rows | TXD1 |
Business Partners
| ActivityLocations | Business partner activity locations | OCLO |
| ActivityStatus | Statuses for Task type activities | OCLA |
| ActivityTypes | Business partner activity types | OCLT |
| AlternateCatNum | Customer or vendor catalog number | OSCN |
| BusinessPartners | Business partners | OCRD |
| BPAddresses | Business partner addresses | CRD1 |
| BPPaymentMethods | Business partner payment methods | CRD2 |
| BPAccountReceivablePayble | Business partner account receivable payable | CRD3 |
| BPWithholdingTax | Business partner withholding tax | CRD4 |
| BPPaymentDates | Business partners payment dates | CRD5 |
| BPBankAccounts | Business partners bank accounts | OCRB |
| Contacts | Contact customers and vendors | OCLG |
| ContactEmployees | Contact persons | OCPR |
| SalesOpportunities | Sales opportunities | OOPR |
| SalesOpportunitiesLines | Sales opportunity – rows | OPR1 |
| SalesOpportunitiesPartners | Sales opportunity partners | OPR2 |
| SalesOpportunitiesCompetition | Sales opportunity competition | OPR3 |
| SalesOpportunitiesInterests | Sales opportunity interests range | OPR4 |
| SalesOpportunitiesReasons | Sales opportunity failure reasons | OPR5 |
| Territories | Territory segmentations | OTER |
Inventory and Production
| Document_Lines | Goods issue – rows | IGE1 |
| Document_LinesAdditionalExpenses | Goods issue rows – expenses | IGE2 |
| DocumentsAdditionalExpenses | Goods issue – expenses | IGE3 |
| Document_Lines | Goods receipt – rows | IGN1 |
| Document_LinesAdditionalExpenses | Goods receipt rows – expenses | IGN2 |
| DocumentsAdditionalExpenses | Goods receipt – expenses | IGN3 |
| Document (oInvetoryGenExit) | Goods issue | OIGE |
| Document (oInvetoryGenEntry) | Goods receipt | OIGN |
| StockTransfer | Stock transfers | OWTR |
| StockTransfer_Lines | Stock transfers – rows | WTR1 |
| ProductTrees_Lines | Product tree – child | ITT1 |
| ProductTrees | Product tree | OITT |
| WorkOrders | Work instructions | OWKO |
| WorkOrder_Lines | Work instructions – rows | WKO1 |
| Items_Prices | Items – prices | ITM1 |
| Items | Items | OITM |
| ItemWarehouseInfo | Items – warehouse | OITW |
| ItemGroups | Item groups definition | OITB |
| MaterialRevaluation | Stock revaluation | OMRV |
| MaterialRevaluation_Lines | Stock revaluation – rows | MRV1 |
| PackagesTypes | Package types for deliveries | OPKG |
| PriceLists | Price lists management | OPLN |
| StockTaking | Items – warehouse | OITW |
| SpecialPrices | Special prices | OSPP |
| SpecialPricesDataAreas | Special prices for specified periods | SPP1 |
| SpecialPricesQuantityAreas | Special prices for specified quantities | SPP2 |
| Warehouses | Warehouses definition | OWHS |
MRP
| SalesForecast | Sales forecast | OFCT |
| SalesForecast_Lines | Sales forecast – rows | FCT1 |
Banking
| Payments | Receipt | ORCT |
| Payments | Vendor payment | OVPM |
| Payments_Checks | Receipt – checks | RCT1 |
| Payments_Invoices | Receipt – invoices | RCT2 |
| Payments_CreditCards | Receipt – credit vouchers | RCT3 |
| Payments_Accounts | Receipt – account list | RCT4 |
| Payments_Checks | Payments – check rows | VPM1 |
| Payments_Invoices | Payments – invoices | VPM2 |
| Payments_CreditCards | Payments – credit memos | VPM3 |
| Payments_Accounts | Payments – list of accounts | VPM4 |
| ChecksforPaymentLines | Checks for payment – rows | CHO1 |
| BankPages | External bank statement | OBNK |
| ChecksforPayment | Check for payment | OCHO |
| PaymentTermsTypes | Payment term types | OCTG |
| PaymentRunExport | Payment results table | OPEX |
| PaymentRunExport_Lines | Payment results table – rows | PEX1 |
| BillOfExchange | Bill of exchange table | OBOE |
| BillOfExchangeTransaction | Bill of exchange transactions table | OBOT |
| BillOfExchangeTransaction_Lines | Bill of exchange transactions table – rows | BOT1 |
Service
| ContractTemplates | Contract template (CRM) | OCTT |
| CustomerEquipmentCards | Customer equipment card | OINS |
| KnowledgeBaseSolutions | Knowledge base solution | OSLT |
| Queue | Queues list for assigning to a service calls | OQUE |
| QueueMembers | Queue member users | QUE1 |
| ServiceContracts | Service contract | OCTR |
| ServiceContracts_Lines | Service contract – rows | CTR1 |
| ServiceCalls | Service call | OSCL |
| ServiceCallActivities | Service call activities | OCLG |
| ServiceCallInventoryExpenses | Service call inventory expenses | INV1 |
Human Resources
| EmployeesInfo | Employees information | OHEM |
| EmployeeAbsenceInfo | Employee absence information | HEM1 |
| EmployeeEducationInfo | Employee education | HEM2 |
| EmployeeReviewsInfo | Employee reviews | HEM3 |
| EmployeePrevEmpoymentInfo | Employee previous employment information | HEM4 |
| EmployeeRolesInfo | Employee rolls information | HEM6 |
| Teams | List of teams | OHTM |
| TeamMembers | Employee membership role in a team | HTM1 |