Which field is appropriate for relation among MKPF and BKPF

[Former Member]

hi,

i want to make report in which i have to display the relation among the MKPF and its BKPF table. plzz tell me which field is more appropiate for JOIN :- XBLNR or AWKEY.

plzz provide me guidelines for it.

[prasanth_kasturi]

hi

use xblnr

select <ur fields>

from mkpf inner join bkpf

on mkpf_{xblnr = bkpf}xblnr

into table <itab>

where <ur cond>.

regards

prasanth

[Former Member]

hi,

chk this blog:

/people/david.halitsky/blog/2006/12/18/the-sky-above-the-mud-below-bkpf-awkey-when-awtyp-bkpf-mkpf

it says awkey:)

try this:

BKPF-AWTYP = 'MKPF' and

concatenate MKPF-MBLNR & MKPF-MJAHR and pass it to BKPF-AWKEY.

regards,

madhu

[Former Member]

HI MADHUMITA ,

I am using the concept of ITabs where i am using join for it ,

can u plzz provide me who to do it?

can declare it as a variable and can i use it in join statement?

i.e. is it possibel to make 1 variable (having mblnr and mjahr) and den use it in the join statement.

Edited by: abaper2008 on May 30, 2008 9:34 AM

[Former Member]

It is not possible to concatenate and pass it in Join.

you can fetch the data from mkpf and then loop i_mkpf. concatenate the fields and get the data from bkpf. if you are able to find a matching record, populate that into final itab.else, go to next record in i_mkpf.

regards,

madhu

[Former Member]

HI,

can u plzz can plzz check this code and see it in the DEBUG mode whether the data is coming correct or not?

DATA: BEGIN OF ITBKPF OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

XBLNR LIKE MKPF-XBLNR,

BUKRS LIKE BKPF-BUKRS,

BELNR LIKE BKPF-BELNR,

GJAHR LIKE BKPF-GJAHR,

AWTYP LIKE BKPF-AWTYP,

AWKEY LIKE BKPF-AWKEY,

BUDAT LIKE BKPF-BUDAT,

END OF ITBKPF.

SELECT-OPTIONS: P_MBLNR FOR MKPF-MBLNR.

SELECT B_{MBLNR B}MJAHR B_{XBLNR A}BUKRS A_{BELNR A}GJAHR A_{AWTYP A}AWKEY A~BUDAT FROM BKPF AS A

INNER JOIN MKPF AS B ON B_{XBLNR = A}XBLNR INTO TABLE ITBKPF

WHERE A_{AWTYP = 'MKPF' AND A}XBLNR > 0 AND B~MBLNR IN P_MBLNR.

SORT ITBKPF BY BELNR.

Just check in debug mode and plzz tel me is correct or not?

I am confused about it,so plzz try to hlep me.

[Former Member]

Try this:

tables: mkpf.
DATA: BEGIN OF ITBKPF OCCURS 0,
MBLNR LIKE MKPF-MBLNR,
MJAHR LIKE MKPF-MJAHR,
XBLNR LIKE MKPF-XBLNR,
BUKRS LIKE BKPF-BUKRS,
BELNR LIKE BKPF-BELNR,
GJAHR LIKE BKPF-GJAHR,
AWTYP LIKE BKPF-AWTYP,
AWKEY LIKE BKPF-AWKEY,
BUDAT LIKE BKPF-BUDAT,
END OF ITBKPF.
DATA: V_AWKEY(30)
SELECT-OPTIONS: P_MBLNR FOR MKPF-MBLNR.

SELECT MBLNR MJAHR XBLNR BUKRS 
 FROM MKPF
 INTO TABLE ITBKPF
 WHERE MBLNR = P_MBLNR.
 IF SY-SUBRC = 0.
   LOOP AT ITBKPF
     CONCATENATE ITBKPF-MBLNR ITBKPF-MJAHR INTO V_AWKEY.
   SELECT SINGLE BELNR GJAHR BUKRS AWTYP AWKEY BUDAT
     FROM BKPF
     WHERE AWTYP = 'MKPF'
       AND AWKEY = V_AWKEY.
   IF SY-SUBRC = 0.
     ITBKPF-BUKRS = BKPF-BUKRS.
     ...
     MODIFY ITBKPF.
   ENDIF.
 ENDIF.

[Former Member]

Hi,

Check out this code as i use to display the vendors cleared items: -

TABLES: BKPF,MKPF,BSAK.

DATA: BEGIN OF ITBKPF OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

XBLNR LIKE MKPF-XBLNR,

BUKRS LIKE BKPF-BUKRS,

BELNR LIKE BKPF-BELNR,

GJAHR LIKE BKPF-GJAHR,

AWTYP LIKE BKPF-AWTYP,

AWKEY LIKE BKPF-AWKEY,

BUDAT LIKE BKPF-BUDAT,

END OF ITBKPF.

DATA: BEGIN OF ITAB OCCURS 0,

BELNR LIKE BSAK-BELNR,

WRBTR LIKE BSAK-WRBTR,

LIFNR LIKE BSAK-LIFNR,

BUKRS LIKE BSAK-BUKRS,

GJAHR LIKE BSAK-GJAHR,

BUDAT LIKE BSAK-BUDAT,

AUGBL LIKE BSAK-AUGBL,

AUGDT LIKE BSAK-AUGDT,

END OF ITAB.

DATA: BEGIN OF ITFINAL OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

XBLNR LIKE MKPF-XBLNR,

BELNR LIKE BSAK-BELNR,

WRBTR LIKE BSAK-WRBTR,

LIFNR LIKE BSAK-LIFNR,

BUKRS LIKE BSAK-BUKRS,

GJAHR LIKE BSAK-GJAHR,

BUDAT LIKE BSAK-BUDAT,

AUGBL LIKE BSAK-AUGBL,

AUGDT LIKE BSAK-AUGDT,

END OF ITFINAL.

SELECT-OPTIONS: P_MBLNR FOR MKPF-MBLNR.

SELECT B_{MBLNR B}MJAHR B_{XBLNR A}BUKRS A_{BELNR A}GJAHR A_{AWTYP A}AWKEY A~BUDAT FROM BKPF AS A

INNER JOIN MKPF AS B ON B_{XBLNR = A}XBLNR INTO TABLE ITBKPF

WHERE A_{AWTYP = 'MKPF' AND B}XBLNR > 0 AND B~MBLNR IN P_MBLNR.

IF NOT ITBKPF[] IS INITIAL.

SELECT A_{BELNR A}WRBTR A_{LIFNR A}BUKRS A_{GJAHR A}BUDAT A_{AUGBL A}AUGDT FROM BSAK AS A

INTO CORRESPONDING FIELDS OF TABLE ITAB

FOR ALL ENTRIES IN ITBKPF

WHERE A_{BUKRS = ITBKPF-BUKRS AND A}GJAHR = ITBKPF-GJAHR.

ENDIF.

SORT ITAB BY LIFNR.

LOOP AT ITAB.

READ TABLE ITBKPF WITH KEY BUKRS = ITAB-BUKRS. " BINARY SEARCH.

CHECK sy-subrc EQ 0.

ITFINAL-MBLNR = ITBKPF-MBLNR.

ITFINAL-MJAHR = ITBKPF-MJAHR.

ITFINAL-XBLNR = ITBKPF-XBLNR.

ITFINAL-BELNR = ITAB-BELNR.

ITFINAL-WRBTR = ITAB-WRBTR.

ITFINAL-LIFNR = ITAB-LIFNR.

ITFINAL-AUGBL = ITAB-AUGBL.

APPEND ITFINAL.

CLEAR ITFINAL.

ENDLOOP.

LOOP AT ITFINAL.

WRITE: / ITFINAL-MBLNR,ITFINAL-AUGBL,ITFINAL-MJAHR,ITFINAL-BELNR,ITFINAL-WRBTR,ITBKPF-XBLNR,ITFINAL-LIFNR.

ENDLOOP.

Then do tell me whether it is correct or not bcoz i have a doubt that ithe material document is right or not? as by defualt it is showing the material document.

plzz do reply....