Blog

SAP Business One Common Table Names and Queries

By Ryan Howe

/* 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 */
/* I have not validated this link */
SELECT * FROM ORCT
INNER JOIN RCT1 ON ORCT.DocNum = RCT1.DocNum

Thanks to Ed Monk of SBOnotes.com

Ryan Howe

Partner, Clients First Business Solutions New Jersey

Ryan Howe is a Certified Public Accountant turned SAP Business One consultant. Leading the SAP Business One practice at Clients First, Ryan has over 23 years’ experience in the financial management and ERP industry, specializing in business analysis, relationship building, and business process optimization. When he’s not helping customers leverage SAP Business One to meet their short and long-term needs, you can find him at any Michigan State sporting event with his family – go Spartans!