SAP Tables along with related Forms

[rajesh_khater]

Hi,

I have already gone through REFDB.CHM help file and the Query Generator window. But they don't provide complete information.

Given a table name, I want to know which SAP Forms update data in that table, get the documentation of all the fields in that table.

Is there comprehensive documentation available?

If not, I want to know the details of the below tables in particular:

OIVL

IVL1

OIVQ

OIVK

OILM

OADM

Which SAP forms insert or update data in the above tables? I want to go to those forms and enter some data, and check how data is getting stored in the database.

The above tables are used in the view OINM i.e. Warehouse Journal.

Thanks.

EDIT:

1. In the OIVL table, what is the meaning of the different values in TransType field

2. What is the meaning of the fields: OpenStock , Expenses, OpenExp, ExpAlloc, OExpAlloc, CogsVal, SumStock, OpenCogs, etc.

The documentation in REFDB.chm file is very shallow, hence this question.

[Former Member · Accepted solution]

Hi,

To understand all tables in B1 is not a one day job. You have to allow a learning period.

Among all the tables you listed, OADM is the table only be edited through system initialization. It should not be touched by any users.

All other tables are related to inventory. Those tabled would be updated whenever a inventory related transactions are posted.

The transtype is very important. If you search the forum, you will be able to find all their means. Actually, it is also available from Refdb.chm.

Thanks,

Gordon

[rajesh_khater · Accepted solution]

Hi Gordon,

I am prepared to spend a few months effort in learning the ropes of the SAP database. But what is coming in my way is lack of good documentation. Most forum threads suggest to refer REFDB.chm or Query Generator, but they do not provide explanation.

I have read your book "Mastering SQL Queries for SAP Business One" and found it to be quite helpful. Surely, you must have had some source of documentation to be able to master it?

I want to understand which table field is updated using end user input, and from which screen and which field on the screen. Then I should be able to join the pieces together with some experimentation. Of course, it will take months.

When I took to SAP, I assumed that SAP being so widespread will have lots of documentation and books. Surprisingly, there are hardly 3 or 4 books on SAP B1. The documentation is good in many areas, but is lacking in advanced or multiple similar features. In particular, the documentation is lacking in use cases for each feature in SAP B1. It describes the feature to some extent, but not with a supporting use case. Anyway, this is an aside not related to the main topic of this thread.

If you can point me to as many useful links as possible for table and screen relationship, it will be great!

Thanks.

[kothandaraman_nagarajan · Accepted solution]

Hi Rajesh,

To find relationship between tables, you can use the function "Relationship map" in any marketing document. Just right click and then choose "Relationship map"

It will show base document as well as target documents.

Hope helpful.

Thanks & Regards,

Nagarajan

[rajesh_khater · Accepted solution]

Hi Nagarajan,

I know about relationship maps. But they will not help me know which screen field (i.e. user input) will affect which field in the table.

Suppose if I have to write query for item-wise cost (not item-wise, warehouse-wise cost) of inventory, or FIFO cost batch-wise (not item-wise), there are so many tables storing so many amount values like Expenses, OpenExp, ExpAlloc, OExpAlloc, CogsVal, SumStock, OpenCogs, etc. (not just limited to these fields).

It is difficult to write a fully tested query without knowing the relation between table fields and screen controls. We can write query and test with a few transactions, but we will not know whether the query will work in some unexpected flows, and even the end user may be able to detect it only after a long time.

For example, If I write a query for Item Cost, considering only GRPO and Landed Costs, but in a particular scenario, end users also use the Freights feature in documents, then my query result will be wrong.

At least, I need to know each and every field to consider for Qty and Cost of Goods in stock, to write my own queries for reporting in alternative inventory unit (using UDFs).

Especially, database fields which affect Cost of Goods in Stock, since it gets affected from so many transactions, including Goods Receipt, Goods Issue, Freight, Landed Costs, A/P Invoice without GRPO, etc.

[kothandaraman_nagarajan · Accepted solution]

Hi Rajesh,

Yes understand your requirement.

1. Before writing any query, we should understand process and output. Normally based on company, the process is always fixed if there is no new process or requirement. This is customer responsibility inform to change or modify query.

2.  Not sure whether you have checked "How to guides" from SAP Business one customer portal. There are few documentation to understand the process in SAP.

3. You may check below blog to know basic relationship between tables to retrieve any values not only cost.

http://scn.sap.com/community/business-one/blog/2013/11/04/basic-of-database-design-in-sap-business-o...

Thanks & Regards,

Nagarajan

[rajesh_khater · Accepted solution]

The above link is helpful to some extent.

We can very well say that it is customer's responsibility to inform for query modification, but as consultants, we are also supposed to provide reports or queries which will give accurate results no matter what SAP features customer uses or does not use. As an analogy, the inbuilt SAP reports are always expected to be accurate as long as the customer uses any of the standard SAP features. Similarly, our queries should be robust and versatile enough to handles all product usage scenarios.

I will still like to at least know the tables and fields to be considered for computing Item's Landed Cost to the company, including all possible scenarios, like Freight, Landed Costs or other scenarios.

Or do you want me to post a separate thread for this?

[kothandaraman_nagarajan · Accepted solution]

Hi,

Its better post as new discussion.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

You assumption is not true. I was in the same situation as you are now few years ago. I had been learning all available info day by day. If you insist, you will get the result.

[rajesh_khater · Accepted solution]

Yes. my assumption is not true not only for books and documentation, but also for SAP official support for onboarding new partners / partner enablement. Partner enablement support is virtually non-existent in SAP.

Anyway, since you have "been there and done that", you will be the best person to share as many useful links for table and screen relationship documentation as possible.

I come from Tally.ERP 9 background, and it is pretty easy to understand table and screen relationship in Tally. You go to a screen, enter test values in fields, export the document to XML, search for the value you had entered on screen, and the corresponding XML tag is the name of the table field where that user input is stored !! It is as simple as that. Moreover, Tally has shared source code with all developers who can study code for each screen, and the code clearly points out which database table and table field is storing the user input !!

[Former Member · Accepted solution]

Thank you for your comments. I can understand why you are so frastrated. However, Tally is not B1. The advantage/disadvantage of both systems are clear. It is unrealistic to ask SAP learn the way Tally is doing.

I have put in all my hours to understand B1. Hope you can do the same. Nobody but me myself learnt all knowledge as of now.

[kothandaraman_nagarajan]

Hi,

Please close this thread by marking correct/helpful answer.

Thanks & Regards,

Nagarajan

[rajesh_khater]

Done

[kothandaraman_nagarajan]

Hi,

1. If none of above reply is not correct, you can select assumed answer to close this thread.

2. If any one of the answer is correct, choose correct answer to close this thread.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi Rajesh,

1. Please refer OIVL transaction type:

15- Delivery

16 – Sales return

13- A/R invoice

14 – A/R credit memo

132 – Correction invoice

20 – Good receipt PO

21 – Goods return

18-  A/P invoice

19- A/P credit memo

-2 – Opening balance

58 --- Inventory update

59 – Good receipt

60 --- Good issue

67- Inventory transfer

68 --- Work instruction

-1 --- All transactions

162 – Inventory revaluation

69-- Landed costs

310000001 -  Initial quantity

10000071 – Inventory posting

1a)

IVL1:

Stock layer level. Includes all types of valuations, regardless if multiple layers or a single layer is used per document line

OIVQ:

Open entry FIFO layer.

OIVE:

Open exit FIFO Layer

OADM --- Administration table

2. 

OpenStock & SumStock fields are updated when you post Good receipt PO and Good issue. same way try with landed cost and freight transaction

Hope helpful.

Thanks & Regards,

Nagarajan