http://Bj83.de

SAP Business One – Common Tables and Queries

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

Quelle: http://www.clientsfirst-us.com/blog/sap/sap-business-one/sap-business-one-common-table-names-and-queries/

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

DunningLettersDunning levels definitionODUN
RecipientsMessage recipient listAOB1
MessagesMessagesOALR
CurrenciesCurrency codesOCRN
UserPermissionTreeUser authorizations for add-on formsOUPT
UserPermissionFormsUser permission formsUPT1
UsersUsers of SAP Business One applicationOUSR
UserPermissionUser authorizationsUSR3
AddonChecking(SBO-COMMON Database Only runned this table)Overall Addon Path DefiningSARI
User QueriesUser QueriesOUQR

Finance

BudgetBudget managementOBGT
Budget_LinesBudget item details of an accountBGT1
BudgetDistributionBudget distribution methodsOBGD
BudgetScenariosBudget scenariosOBGS
ChartOfAccountsG/L accountsOACT
JournalVouchersJournal vouchersOBTD
JournalEntries_LinesJournal transaction – rowsJDT1
JournalEntriesJournal transactionOJDT
SalesTaxAuthoritiesSales tax jurisdictionsOSTA
SalesTaxAuthoritiesTypesSales tax authority typesOSTT
SalesTaxCodesInclusive sales tax codesOSTC

Marketing Documents and Receipts
(Sales Opportunities, Sales A/R, and Purchasing A/P)

Document_LinesDelivery note rowsDLN1
Document_LinesAdditionalExpensesDelivery note rows – expensesDLN2
DocumentsAdditionalExpensesDelivery note – expensesDLN3
AdditionalExpensesDefines additional expensesOEXD
Document_LinesDraft – rowsDRF1
Document_LinesAdditionalExpensesDraft rows – expensesDRF2
DocumentsAdditionalExpensesDraft – expensesDRF3
Document_LinesInvoice – rowsINV1
Document_LinesAdditionalExpensesInvoice rows – expensesINV2
DocumentsAdditionalExpensesInvoice – expensesINV3
Document (oDeliveryNote)Delivery – headerODLN
DocumentsDrafts – headerODRF
Documents (oInvoice)Invoice – headerOINV
Document (oPurchaseInvoices)AP invoice – headerOPCH
Document (oPurchaseDeliveryNotes)Goods receipt purchase order – headerOPDN
Document (oPurchaseOrders)Purchase order – headerOPOR
Document (oQuotation)Quotation – headerOQUT
Document (oReturn)Returns – headerORDN
Document (oOrder)Order – headerORDR
Document (oCreditNote)AR credit memo – headerORIN
Document (oPurchaseCreditNotes)AP credit memo – headerORPC
Document (oPurchaseReturns)Goods return – headerORPD
Document_LinesAP invoice – rowsPCH1
Document_LinesAdditionalExpensesAP invoice rows – expensesPCH2
DocumentsAdditionalExpensesAP invoice – expensesPCH3
Document_LinesGoods receipt purchase order – rowPDN1
Document_LinesAdditionalExpensesGoods receipt purchase order rows – expensesPDN2
DocumentsAdditionalExpensesGoods receipt purchase order – expensesPDN3
Document_LinesPurchase order – rowsPOR1
Document_LinesAdditionalExpensesPurchase order rows – expensesPOR2
DocumentsAdditionalExpensesPurchase order – expensesPOR3
Document_LinesQuotation – rowsQUT1
Document_LinesAdditionalExpensesQuotation rows – expensesQUT2
DocumentsAdditionalExpensesQuotation – expensesQUT3
Document_LinesReturns – rowsRDN1
Document_LinesAdditionalExpensesReturn rows – expensesRDN2
DocumentsAdditionalExpensesReturns – expensesRDN3
Document_LinesOrder – rowsRDR1
Document_LinesAdditionalExpensesOrder rows – expensesRDR2
DocumentsAdditionalExpensesOrder – expensesRDR3
Document_LinesCredit memo – rowsRIN1
Document_LinesAdditionalExpensesCredit memo rows -ExpensesRIN2
DocumentsAdditionalExpensesCredit memo – expensesRIN3
Document_LinesAP credit memo – rowsRPC1
Document_LinesAdditionalExpensesAP credit memo rows – expensesRPC2
DocumentsAdditionalExpensesAP credit memo – expensesRPC3
Document_LinesGoods return – rowsRPD1
Document_LinesAdditionalExpensesGoods return rows – expensesRPD2
DocumentsAdditionalExpensesGoods return – expensesRPD3
BatchNumbersBatch number for itemOIBT
IndustriesIndustries list for sales opportunitiesOOND
RelationshipsRelationships for sales opportunitiesOORL
SerialNumbersSerial numbers for itemsOSRI
WithholdingTaxDataWithholding tax dataINV5
TaxInvoices (botit_Invoice)Master header data of a tax invoice of sales documentOTSI
TaxInvoice_Lines (botit_Invoice)Tax invoice of sales document – rowsTSI1
TaxInvoices (botit_Payment)Master header data of a tax invoice of purchase documentOTPI
TaxInvoice_Lines (botit_Payment)Tax invoice of purchase document – rowsTPI1
TaxInvoices (botit_JournalEntry)Master header data of a tax invoice of journal entry documentOTXD
TaxInvoice_Lines (botit_JournalEntry)Tax invoice of journal entry document – rowsTXD1

Business Partners

ActivityLocationsBusiness partner activity locationsOCLO
ActivityStatusStatuses for Task type activitiesOCLA
ActivityTypesBusiness partner activity typesOCLT
AlternateCatNumCustomer or vendor catalog numberOSCN
BusinessPartnersBusiness partnersOCRD
BPAddressesBusiness partner addressesCRD1
BPPaymentMethodsBusiness partner payment methodsCRD2
BPAccountReceivablePaybleBusiness partner account receivable payableCRD3
BPWithholdingTaxBusiness partner withholding taxCRD4
BPPaymentDatesBusiness partners payment datesCRD5
BPBankAccountsBusiness partners bank accountsOCRB
ContactsContact customers and vendorsOCLG
ContactEmployeesContact personsOCPR
SalesOpportunitiesSales opportunitiesOOPR
SalesOpportunitiesLinesSales opportunity – rowsOPR1
SalesOpportunitiesPartnersSales opportunity partnersOPR2
SalesOpportunitiesCompetitionSales opportunity competitionOPR3
SalesOpportunitiesInterestsSales opportunity interests rangeOPR4
SalesOpportunitiesReasonsSales opportunity failure reasonsOPR5
TerritoriesTerritory segmentationsOTER

Inventory and Production

Document_LinesGoods issue – rowsIGE1
Document_LinesAdditionalExpensesGoods issue rows – expensesIGE2
DocumentsAdditionalExpensesGoods issue – expensesIGE3
Document_LinesGoods receipt – rowsIGN1
Document_LinesAdditionalExpensesGoods receipt rows – expensesIGN2
DocumentsAdditionalExpensesGoods receipt – expensesIGN3
Document (oInvetoryGenExit)Goods issueOIGE
Document (oInvetoryGenEntry)Goods receiptOIGN
StockTransferStock transfersOWTR
StockTransfer_LinesStock transfers – rowsWTR1
ProductTrees_LinesProduct tree – childITT1
ProductTreesProduct treeOITT
WorkOrdersWork instructionsOWKO
WorkOrder_LinesWork instructions – rowsWKO1
Items_PricesItems – pricesITM1
ItemsItemsOITM
ItemWarehouseInfoItems – warehouseOITW
ItemGroupsItem groups definitionOITB
MaterialRevaluationStock revaluationOMRV
MaterialRevaluation_LinesStock revaluation – rowsMRV1
PackagesTypesPackage types for deliveriesOPKG
PriceListsPrice lists managementOPLN
StockTakingItems – warehouseOITW
SpecialPricesSpecial pricesOSPP
SpecialPricesDataAreasSpecial prices for specified periodsSPP1
SpecialPricesQuantityAreasSpecial prices for specified quantitiesSPP2
WarehousesWarehouses definitionOWHS

MRP

SalesForecastSales forecastOFCT
SalesForecast_LinesSales forecast – rowsFCT1

Banking

PaymentsReceiptORCT
PaymentsVendor paymentOVPM
Payments_ChecksReceipt – checksRCT1
Payments_InvoicesReceipt – invoicesRCT2
Payments_CreditCardsReceipt – credit vouchersRCT3
Payments_AccountsReceipt – account listRCT4
Payments_ChecksPayments – check rowsVPM1
Payments_InvoicesPayments – invoicesVPM2
Payments_CreditCardsPayments – credit memosVPM3
Payments_AccountsPayments – list of accountsVPM4
ChecksforPaymentLinesChecks for payment – rowsCHO1
BankPagesExternal bank statementOBNK
ChecksforPaymentCheck for paymentOCHO
PaymentTermsTypesPayment term typesOCTG
PaymentRunExportPayment results tableOPEX
PaymentRunExport_LinesPayment results table – rowsPEX1
BillOfExchangeBill of exchange tableOBOE
BillOfExchangeTransactionBill of exchange transactions tableOBOT
BillOfExchangeTransaction_LinesBill of exchange transactions table – rowsBOT1

Service

ContractTemplatesContract template (CRM)OCTT
CustomerEquipmentCardsCustomer equipment cardOINS
KnowledgeBaseSolutionsKnowledge base solutionOSLT
QueueQueues list for assigning to a service callsOQUE
QueueMembersQueue member usersQUE1
ServiceContractsService contractOCTR
ServiceContracts_LinesService contract – rowsCTR1
ServiceCallsService callOSCL
ServiceCallActivitiesService call activitiesOCLG
ServiceCallInventoryExpensesService call inventory expensesINV1

Human Resources

EmployeesInfoEmployees informationOHEM
EmployeeAbsenceInfoEmployee absence informationHEM1
EmployeeEducationInfoEmployee educationHEM2
EmployeeReviewsInfoEmployee reviewsHEM3
EmployeePrevEmpoymentInfoEmployee previous employment informationHEM4
EmployeeRolesInfoEmployee rolls informationHEM6
TeamsList of teamsOHTM
TeamMembersEmployee membership role in a teamHTM1

Quelle: http://wiki.scn.sap.com/wiki/display/B1/SAP+Business+One+Tables

2 comments for “SAP Business One – Common Tables and Queries

  1. VIJAY
    02/01/2017 at 08:11

    THANK YOU………….

  2. Bing
    10/08/2017 at 03:59

    This is very nice for newbies like me!!!!
    Thanks a lot!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: