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 Member
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 , due date. 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)

    • BaseEntry (Foreign Key)



  2. LineNum

    • Baseline



  3. ObjType (Object Type).

    • 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

 

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 andT2.BaseType=T0.ObjTypewhere t0.DocType = 'I'

Also one query report name Stock report has been saved in OEC Demo Database.

Another way is, you can find all table field from Query generator. To find field name, press tab in query generator and find necessary table.

For example:

You can create  query for sales order. The required tables are ORDR and RDR1.

  1. Open query generator from Tools

  2. Press tab and find ORDR table and then choose

  3. Same way try to RDR1 table

  4. These tables are linked automatically by Doc.Entry

  5. Choose desired field from both field.


Please refer screen shot for above example:

1 Comment
Labels in this area