topic Re: Fetching data from SD tables and creating view in Application Development and Automation Discussions

Fetching data from SD tables and creating view

anupam_srivastava2 — Tue, 12 Feb 2008 15:14:53 GMT

Hi All

I want the following fileds in my view.

1.Slaes doc- VBAK-VBELN

2.Created On-VBAK-ERDAT

3.Purch.Order No-VBAK-BSTNK

4.Ref1- VBAK-IHREZ

5.Sold to party- VBAK-KUNNR

6.Billing doc- VBRP-VBELN

7.Material- VBRP-MATNR

8.Originating doc- VBRP-VBELV

9.NetValue VBRP-NETWR

Is it possible to create relation between vbak and vbrp, if not how can I fetch these filed from other tables, relate them and create a view. Actualy I want to create a Sales Invoice, where I want to use Sales doc and Billing doc togather. The fileds which I required, I found in these tables, but I am not clear how I can relate these tables and create a view.

Please guide

cheers

Re: Fetching data from SD tables and creating view

Former Member — Tue, 12 Feb 2008 16:53:48 GMT

Hi,

Go to the sqvi transaction and mention the two tables (VBAK and VBRP)for which u want to create a view.

The fields that are common will get joined automatically u can even remove it if not required.

U can select the fields that are to be displayed in the output.

hence u will have the required output.

Regards,

Sai

Re: Fetching data from SD tables and creating view

valentin_catalin2 — Wed, 13 Feb 2008 09:02:58 GMT

Hi,

You can link VBRK/VBRP to VBAK/VBAP in two ways:

1. Simplest version is to use fields from VBRP: VBRP-AUBEL and VBRP-AUPOS; this fields are containing sales document number and sales document position (I'm not 100% sure what this fields will contain if you have a full SD cycle with lets say contract -> sales order -> delivery -> invoice, if they will contain sales order relation of contract relation; this has to be checked).

Using those fields you should write a select like this:


select
  from vbrk inner join vbrp on vbrk~vbeln = vbrp~vbeln
                inner join vbak on vbrp~aubel = vbak~vbeln
                inner join vbap on vbrp~aubel = vbap~vbeln and
                                   vbrp~vgbel = vbap~vgbel

You should add what fields you want to extract (using this example you have access to all fields form VBRK, VBRP, VBAK and VBAP).

2. Seccond option is to use the VBFA table which contains the document flow (check what contains this table in different examples of document flow to build your logic for extracting data).

I hope this is helpful for you.

BR,

Valentin