05-30-2008 8:08 AM
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.
05-30-2008 8:13 AM
hi
use xblnr
select <ur fields>
from mkpf inner join bkpf
on mkpfxblnr = bkpfxblnr
into table <itab>
where <ur cond>.
regards
prasanth
05-30-2008 8:15 AM
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
05-30-2008 8:34 AM
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
05-30-2008 9:48 AM
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
05-30-2008 9:59 AM
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 BMBLNR BMJAHR BXBLNR ABUKRS ABELNR AGJAHR AAWTYP AAWKEY A~BUDAT FROM BKPF AS A
INNER JOIN MKPF AS B ON BXBLNR = AXBLNR INTO TABLE ITBKPF
WHERE AAWTYP = 'MKPF' AND AXBLNR > 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.
05-30-2008 10:18 AM
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.
05-30-2008 11:10 AM
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 BMBLNR BMJAHR BXBLNR ABUKRS ABELNR AGJAHR AAWTYP AAWKEY A~BUDAT FROM BKPF AS A
INNER JOIN MKPF AS B ON BXBLNR = AXBLNR INTO TABLE ITBKPF
WHERE AAWTYP = 'MKPF' AND BXBLNR > 0 AND B~MBLNR IN P_MBLNR.
IF NOT ITBKPF[] IS INITIAL.
SELECT ABELNR AWRBTR ALIFNR ABUKRS AGJAHR ABUDAT AAUGBL AAUGDT FROM BSAK AS A
INTO CORRESPONDING FIELDS OF TABLE ITAB
FOR ALL ENTRIES IN ITBKPF
WHERE ABUKRS = ITBKPF-BUKRS AND AGJAHR = 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....