Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member197621
Active Contributor

Hi every one.....:)

In this Blog am going to explain the Basic Database design in SAP Business One

B1 having powerful table structures and it is easily to remember.

Topics elaborated in this blog

  1. Basic Knowledge of B1 tables
  2. What is Header table
  3. What is Row table
  4. Important Fields
  5. What is DocEntry
  6. What is BaseEntry
  7. What is LineNum
  8. What is BaseLine
  9. What is BaseType

Basic Knowledge of B1 tables:

To know the table name move the cursor in any field you can see the table and field in the footer. Else you have to enable in View -> System Information

For Example: Move the cursor in the Purchase Order , Vendor field

All the documents are segregate as "Header table"  and "Row table"

Header tables will start with "O"

Row tables ends with "1"

What is Header table:

The value which is entered only one time.

For example Purchase Order Header (OPOR) : In purchase order we will choose only one customer and one posting date , duedate. This kind of information will not be entered more than one times,

What is Row table:

The value which is entered one or multiple times.

For example Purchase Order Row (POR1) : In purchase order we will choose item code multiple times similarly Quantity , price etc.. This kind of information will be stored in row tables.

Important Fields:

  1. Docentry (Primary Key)
    1. BaseEntry (Foreign Key)
  2. LineNum
    1. Baseline
  3. ObjType (Object Type).
    1. Basetype

This 3 fields you have to remember. Its enough to write any report in B1.

What is Docentry:

"Docentry" is unique number , this will generate automatically for all the individual documents. this starts from 1 will increase one by one for each new entry.

for example : Run this query "select docentry from OPOR" you can understand easily.

What is BaseEntry:

Its a Docentry of the base document.

For example : create one purchase order,

Assume this purchase order docentry is 21

(To know the docentry run this query: select max(docentry) from opor)

Now create one Goods Receipt PO based on that Purchase order.

Now run this query : select docentry , baseentry from PDN1 order by docentry desc

Now see the first record, you can see the "21" in baseentry column.

What is LineNum:

Its a Serial number for "Row table" .

For example: if you select 5 items in purchase order , number will start from zero and increase one by one. Next document again will start from zero.

Run this query "select docentry,LineNum from POR1" you can understand easily.

What is BaseLine:

Its a LineNum of the base document.

For example : create one purchase order with 5 items,

Assume this purchase order docentry is 22

(To know the docentry run this query: select max(docentry) from opor)

Now create one Goods Receipt PO based on that Purchase order.

Now run this query : select docentry ,LineNum, baseentry,baseline from PDN1 order by docentry desc

Now see the first record, you can see the "21" in baseentry column.

What is ObjType:

Its a door number of the each document. When ever we write a address we will write a door number same like you have to mention the Objtype.

For example: If you want to mention purchase order 22 , Goods Receipt PO 20

To know the Object type refer the below link,

SAP Business One Form Types and Object Types

Usage:


SELECT *  FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[BaseType]  = 22



Query will retrieve only Goods Receipt PO which is copied from purchase order.

What is BaseType:

Its a ObjType of the base document.

For example : create one purchase order,

Assume this purchase order docentry is 23

(To know the Object Type run this query: select ObjType from opor)

Now create one Goods Receipt PO based on that Purchase order.

Now run this query : select docentry ,LineNum,ObjType, baseentry,baseline, Basetype from PDN1 order by docentry desc

Now you can see the 22 in Base type. If its showing -1 its meaning, Goods Receipt PO is create without any Purchase order

*****************************

Below sample query I have used all the fields,


SELECT t0.docnum [PO No.], T1.ItemCode,T1.Quantity as 'PO Qty',T2.Quantity as 'GRPO Qty'



FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry



left join pdn1 T2 on T2.BaseEntry = T1.DocEntry and T2.BaseLine= T1.LineNum and T2.BaseType=T0.ObjType



where t0.DocType = 'I'



Output of the Query:

List all the Purchase order with Goods Receipt PO.

Any questions or if anyone has any tips for me please post below.

See also:

How to Implement SAP Business One projects successfully

How to count the entries user wise. **Helpful for go live**

Database design in SAP Business One

How to know the current user LOGIN status in 9-Version

6 Comments
Labels in this area