topic Re: relationship in table BKPF and BSAK in Application Development and Automation Discussions

relationship in table BKPF and BSAK

waqar_ahmed — Tue, 12 Nov 2013 11:09:52 GMT

HI All,

Can you someone tell me the right relation between BKPF and BSAK, when I check in SQVI than my query look like this

Select ---------------------

FROM BKPF
          INNER JOIN BSAK
                     ON BKPF~BUKRS = BSAK~BUKRS
                    AND BKPF~BLDAT = BSAK~AUGDT
                    AND BKPF~BELNR = BSAK~AUGBL
                     AND BKPF~GJAHR = BSAK~GJAHR
                   AND BKPF~DBBLG = BSAK~BELNR

BUT I am not getting any data with above condition. Whereas if I see the matching fields of both tables than it should be like below

Select ---------------------
FROM BKPF
          INNER JOIN BSAK
                     ON BKPF~BUKRS = BSAK~BUKRS
                     AND BKPF~GJAHR = BSAK~GJAHR
                     AND BKPF~BELNR = BSAK~BELNR

and this does return records on quality system. Any suggestions which one is right.

regards,

ray

Re: relationship in table BKPF and BSAK

former_member188827 — Tue, 12 Nov 2013 11:15:34 GMT

Second query is correct.i.e.

Select ---------------------

FROM BKPF
          INNER JOIN BSAK
                     ON BKPF~BUKRS = BSAK~BUKRS
                     AND BKPF~GJAHR = BSAK~GJAHR
                     AND BKPF~BELNR = BSAK~BELNR

BKPF is accounting document header table and its key fields are linked to BSAK on basis of Company Code, Document No and Fiscal Year.

Regards

Re: relationship in table BKPF and BSAK

Former Member — Tue, 12 Nov 2013 11:15:58 GMT

Hi Ray,

I just did a where-used-list check for views of BSAK and it returned the following combinations with BKPF.

BKPF_BSAK BW FI: BSAK Extraction Using CPUDT

BKPF_BSAK_AEDAT BW FI: BSAK Extraction using AEDAT

FMKK_BKPF_BSAK View for F4 Help FMKK_F4_AUGBL

Just check the table join conditions to see the data output to correct the join.I was able to see data for the first and third view.

Hope it helps

Regards

Re: relationship in table BKPF and BSAK

gouravkumar64 — Tue, 12 Nov 2013 11:17:51 GMT

BKPF is accounting document header table

BSAK- Cleared items table for Vendor.

So one reason may be ,you are not getting cleared items in quality in your condition & there are all open items as per your condition.

Thanks

Gourav.

Re: relationship in table BKPF and BSAK

Former Member — Tue, 12 Nov 2013 11:20:05 GMT

Hi I think , the second query is right..in the first query it is

'BKPF~DBBLG = BSAK~BELNR' and in the second it is 'BKPF~BELNR = BSAK~BELNR' ...unless the document in DBBLG is a clkeared one,it wont appear in BSAK.

Re: relationship in table BKPF and BSAK

waqar_ahmed — Tue, 12 Nov 2013 11:42:51 GMT

Thanks all, if I see table relationship in SQVI than it shows Inner join on these fields. As per this my first query should be right? Or is there any other way to check relationship between these tables. I am not abap programmer so bit confused on these joins.

Former Member I have checked BKPF_BSAK View and you are right joins are different than it is showing in below diagram.

Any more suggestions

Re: relationship in table BKPF and BSAK

Former Member — Tue, 01 Apr 2014 06:03:16 GMT

In the first query you are joining

BKPF~BLDAT = BSAK~AUGDT which are the Document Date in BKPF and Clearing Date in BSAK not the correct relation

BKPF~BELNR = BSAK~AUGBL which are the Document Number in BKPF and Clearing Document in BSAK (should be BSAK BELNR - Document Number)

BKPF~DBBLG = BSAK~BELNR which are the BKPF Recurring Entry Document Number and BSAK - Document Number.should be BELNR from BKPF

Hence the correct join will be

ON BKPF~BUKRS = BSAK~BUKRS

AND BKPF~GJAHR = BSAK~GJAHR

AND BKPF~BELNR = BSAK~BELNR

Thanks

Divya

Re: relationship in table BKPF and BSAK

RaymondGiuseppi — Tue, 01 Apr 2014 07:04:14 GMT

BSAK is a so named secondary index, a virtual join between BKPF and BSEG (cluster so not allowing actualk joins) for cleared vendor item, so use BUKRS, GJAHR, BELNR for BKPF and add BUZEI for BSEG.

The second JOIN does not link to same records, but link document to Recurring Entry Document Number. You could also consider joining header of a clearing document with cleared items.

So what do you actually want to select ?

Regards,

Raymond