Queries
/* Business Partners */
SELECT * FROM OCRD
/* Chart of Accounts */
SELECT * FROM OACT
/* Item Master */
SELECT * FROM OITM
/* Terms Codes */
SELECT * FROM OCTG
/* Business Partners and their assigned payment terms */
SELECT OCTG.PymntGroup, OCRD.* FROM OCRD
LEFT OUTER JOIN OCTG ON OCRD.GroupNum = OCTG.GroupNum
/* Employee Master (HR) */
SELECT * FROM OHEM
/* Sales Quotations and their Line Items */
SELECT * FROM OQUT
INNER JOIN QUT1 ON OQUT.DocEntry = QUT1.DocEntry
/* Sales Orders and the Line Items */
SELECT * FROM ORDR
INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry
/* Credit Memos and the Line Items */
SELECT * FROM ORIN
INNER JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry
/* Incomming Payments and the line level Information */
SELECT * FROM ORCT
INNER JOIN RCT1 ON ORCT.DocNum = RCT1.DocNum
Most Common Tables
Master Files:
OITM = Item Master
OCRD = Customer, Vendor, and Lead Master
CRD1 = Customer Master Address
OCRG = Customer Master Group Code
OHEM = Employee Master
OUSR = SBO Users
Document Files:
ORDR = Sales Order Header
RDR1 = Sales Order Detail (Items)
OINV = Invoice Header
INV1 = Invoice Line Details (Items)
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 |
Quelle: http://wiki.scn.sap.com/wiki/display/B1/SAP+Business+One+Tables
THANK YOU………….
This is very nice for newbies like me!!!!
Thanks a lot!!!