relationship in table BKPF and BSAK

[waqar_ahmed]

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

[former_member188827]

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

[waqar_ahmed]

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

[Former Member]

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

[gouravkumar64]

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.

[Former Member]

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.

[Former Member]

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

[RaymondGiuseppi]

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