TABLE JOIN OF BKPF AND LFA1

[Former Member]

I am creating a query and need to join the

Invoice Number (BKPF-BELNER)

Posting Date (BKPF-BUDAT)

Company Number (BKPF-BUKRS)

Supplier Name (LFA1-LIFNR)

Gross Amount (LFA1-WRBTR)

But the BKPF and LFA1 tables does not want to join. Do you know what table I can use to make them join? There must be some link between and invoice and who on the invoice we are paying.

Thanks,

Linda

[Former Member]

The supplier number (LIFNR) is available in the table BSEG.

BKPF-BUKRS = BSEG-BUKRS.

BKPF-BELNR = BSEG-BELNR.

BKPF-GJAHR = BSEG-GJAHR.

Please mark points if the solution was useful.

Regards,

Manoj

[Former Member]

When I try to join BKPF with BSEG I get an error message saying that BSEG can't be used in that join.

[Former Member]

Hi

It can't do a join with a cluster table like BSEG.

If you need to get the vendor items you need to read the BSIK (open item) and BSAK (cleared item) tables.

These two tables are the same fields and here you can find header (BKPF) and item (BSEG) data together.

Invoice Number (BKPF-BELNER) ---> BSAK/BSIK-BELNR

Posting Date (BKPF-BUDAT) ---> BSAK/BSIK-BUDAT

Company Number (BKPF-BUKRS) ---> BSAK/BSIK-BUKRS

Supplier Name (LFA1-LIFNR) ---> BSAK/BSIK-LIFNR

Gross Amount (LFA1-WRBTR) ---> BSAK/BSIK-WRBTR

So if you need to get the items posted in a certain ranges:

DATA: T_ITEMS LIKE STANDARD TABLE OF BSAK WITH HEADER LINE.

* Cleared items
SELECT * FROM BSAK INTO TABLE T_ITEMS
                  WHERE BUKRS = ....
                    AND LIFNR IN S_LIFNR
                    AND BUDAT IN S_BUDAT.
* Open items
SELECT * FROM BSAK APPENDING TABLE T_ITEMS
                  WHERE BUKRS = ....
                    AND LIFNR IN S_LIFNR
                    AND BUDAT IN S_BUDAT.

In this way you can get all data you need without to do any join

Max

[Former Member]

Hi

Don't use the BKPF table to join with LFA1 data

You will get the vendor related AP data from <b>BSIK and BSAK</b> tables

better use them

and join the tables <b>BSIK and LFA1</b> using LIFNR field which is key field in both and fetches the data very fast

<b>Reward points for useful Answers</b>

Regards

Anji

[Former Member]

The BSIK and LFA1 do not have Invoice Date, Invoice Tye, Invoice Number associated with them so I still need to use BKPF unless there is something else I can use?

[Former Member]

the relation of the table's are like this ..

" LFA1-lifnr  ->LFB1-lifnr 
" LFB1-bukrs ->BKPF-bukrs

.

<b>the logic of the program is below </b>

Selections in the Selection Include
   SELECT-OPTIONS: slifnr   FOR lfa1-lifnr, 
                   sbukrs   FOR lfb1-bukrs, 
                   sgjahr   FOR lfc1-gjahr, 
                   sbelnr   FOR bkpf-belnr.
PROGRAM sapdbtest_ldb DEFINING DATABASE test_ldb. 
TABLES: lfa1, 
        lfb1, 
        lfc1, 
        bkpf. 

SELECT * FROM lfa1 
           WHERE lifnr      IN slifnr. 
    PUT lfa1. 
SELECT * FROM lfb1 
           WHERE lifnr      =  lfa1-lifnr 
             AND bukrs      IN sbulrs. 
    PUT lfb1. 
  ENDSELECT

 SELECT * FROM lfc1 
           WHERE lifnr      =  lfa1-lifnr 
             AND bukrs      =  lfb1-bukrs 
             AND gjahr      IN sgjahr. 
    PUT lfc1. 
  ENDSELECT. 


SELECT * FROM bkpf 
           WHERE bukrs      =  lfb1-bukrs 
             AND belnr      IN sbelnr 
             AND gjahr      IN sgjahr. 
    PUT bkpf. 
  ENDSELECT.

reward points if it is usefull .....

Girish