Problem in making report

[Former Member]

Hi,

I am making a reprt in i have to make relationship of 3 tables:-

MKPF,BKPF and BSAK.

But i am confused that how to do that. i am able to make relation among BKPF and MKPF. but not able to make among the BSAK.

here's the code for it:-

TABLES: BKPF,MKPF.

DATA: BEGIN OF ITBKPF OCCURS 0,

BUKRS LIKE BKPF-BUKRS,

BELNR LIKE BKPF-BELNR,

GJAHR LIKE BKPF-GJAHR,

AWTYP LIKE BKPF-AWTYP,

AWKEY LIKE BKPF-AWKEY,

END OF ITBKPF.

DATA: BEGIN OF ITMKPF OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

END OF ITMKPF.

DATA: BEGIN OF ITBSAK OCCURS 0,

AUGBL LIKE BSAK-AUGBL,

AUGDT LIKE BSAK-AUGDT,

BELNR LIKE BSAK-BELNR,

WRBTR LIKE BSAK-WRBTR,

LIFNR LIKE BSAK-LIFNR,

END OF ITBSAK.

DATA: BEGIN OF ITFINAL OCCURS 0,

BUKRS LIKE BKPF-BUKRS,

BELNR LIKE BKPF-BELNR,

GJAHR LIKE BKPF-GJAHR,

AWTYP LIKE BKPF-AWTYP,

AWKEY LIKE BKPF-AWKEY,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

AUGBL LIKE BSAK-AUGBL,

AUGDT LIKE BSAK-AUGDT,

WRBTR LIKE BSAK-WRBTR,

LIFNR LIKE BSAK-LIFNR,

END OF ITFINAL.

PARAMETERS: P_BUKRS LIKE BKPF-BUKRS,

P_GJAHR LIKE BKPF-GJAHR.

SELECT A_{BUKRS A}BELNR A_{GJAHR A}AWTYP A~AWKEY INTO TABLE ITBKPF FROM BKPF AS A

WHERE A_{BUKRS = P_BUKRS AND A}GJAHR = P_GJAHR AND A~AWTYP = 'MKPF'.

IF NOT ITBKPF[] IS INITIAL.

SELECT A_{MBLNR A}MJAHR INTO TABLE ITMKPF FROM MKPF AS A FOR ALL ENTRIES IN ITBKPF WHERE A~MBLNR = ITBKPF-AWKEY(10).

ENDIF.

LOOP AT ITBKPF.

READ TABLE ITMKPF WITH KEY MBLNR = ITBKPF-AWKEY(10)

MJAHR = ITBKPF-AWKEY+10(4).

IF SY-SUBRC EQ 0.

WRITE 😕 ITBKPF-BELNR,ITMKPF-MBLNR,ITBKPF-GJAHR.

ENDIF.

ENDLOOP.

TELL ME HOW TO DO IT?

[valter_oliveira]

Hello.

BSAK is table of Accounting Vendors, and in the key it has BUKRS, BELNR and GJAHR, which are the key of table BKPF.

So make BSAK-BUKRS = BKPF-BUKRS, BSAK-BELNR = BKPF-BELNR and BSAK-GJAHR = BKPF-GJAHR. The performance will be fine cause there is an INDEX in bsak whith this fields (index 5).

Best regards.

Valter Oliveira.

[Former Member]

hi,

can u plzz tell me by giving some example of it.

[valter_oliveira]

Hello.

Do something like this.

SELECT AUGBL AUGDT BELNR WRBTR LIFNR 
  INTO TABLE ITBSAK 
  FROM BSAK FOR ALL ENTRIES IN ITBKPF 
 WHERE BUKRS = ITBKPF-BUKRS
   AND BELNR = ITBKPF-BELNR
   AND GJAHR = ITBKPF-GJAHR.

Best regards.

Valter Oliveira.

[former_member156446]

*&---------------------------------------------------------------------*
*& Form get_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form get_data.
*************----------- VARIABLES ---------------**************
data: vl_addrnum type t499s-addrnum,
vl_city1 type adrc-city1.

*************----------- Constants ---------------------**************
constants:cl_ca01(4) value 'CA01'.

***********---------Field symbols ----------------------************
field-symbols: <fs_mseg> type t_mseg.

select a~mblnr a~mjahr a~zeile a~werks a~matnr a~bwart
a~lifnr a~menge a~meins a~lgort a~grund a~sakto
a~ps_psp_pnr a~ablad a~rsnum a~rspos a~kostl
a~umlgo a~wempf a~ebeln a~ebelp a~bukrs a~belnr
a~buzei a~sobkz
b~budat b~xblnr b~bktxt b~xabln b~frbnr b~cputm
c~matkl a~kzvbr a~shkzg
from mseg as a
join mkpf as b
on a~mblnr = b~mblnr and
a~mjahr = b~mjahr
join mara as c
on c~matnr = a~matnr
into table i_mseg
where a~werks in s_werks and
a~bwart in s_bwart and
a~lifnr in s_lifnr and
a~umwrk in s_umwrk and
a~wempf in s_scar and
a~ablad in s_ablad and
a~ebeln in s_ebeln and
a~sjahr in s_yyyy and
b~budat in s_date and
b~cputm in s_time and
b~mjahr in s_yyyy.

if not i_mseg[] is initial.

select pspnr fkokr fkstl posid stort
from prps
into table i_prps
for all entries in i_mseg
where pspnr eq i_mseg-ps_psp_pnr.

select bwart sobkz btext from t156t
into table i_text
for all entries in i_mseg
where bwart eq i_mseg-bwart
and spras eq v_lan
and sobkz eq i_mseg-sobkz
and kzvbr eq i_mseg-kzvbr.

select lifnr name1 from lfa1
into table i_lfa1
for all entries in i_mseg
where lifnr eq i_mseg-lifnr.

endif.

*Awkey is the combination of MBLNR and MJAHR
loop at i_mseg assigning <fs_mseg>.

concatenate <fs_mseg>-mblnr <fs_mseg>-mjahr into <fs_mseg>-awkey.

*To get the Vertex Catagory
move <fs_mseg>-matnr to w_ztfi_product-matnr.
move <fs_mseg>-matkl to w_ztfi_product-matkl.

select single prdtc
from ztfi_product
into w_ztfi_product-prdtc
where matkl eq w_ztfi_product-matkl.

append w_ztfi_product to i_ztfi_product.

endloop.

if not i_mseg[] is initial.
select bukrs belnr gjahr awkey waers
from bkpf
into table i_bkpf
for all entries in i_mseg
where gjahr = i_mseg-mjahr
and bukrs = i_mseg-bukrs
and budat = i_mseg-budat
and awkey = i_mseg-awkey.

select matnr maktx from makt
into table i_makt
for all entries in i_mseg
where matnr = i_mseg-matnr
and spras = v_lan.
endif.

if not i_bkpf[] is initial.

select bukrs " Company Code
belnr " Document Number
gjahr " Fiscal Year
buzei " Line Item
shkzg " Debit / Credit indicator
wrbtr " Amount
sgtxt " Line Item Text
vbund " Trading Partner
kostl " Cost Center
hkont " Account
matnr " Material Number
werks " Plants
txjcd " Tax Jurisdiction
projk " WBS Element
into table i_bseg
from bseg
for all entries in i_bkpf
where bukrs eq i_bkpf-bukrs and
belnr eq i_bkpf-belnr and
gjahr eq i_bkpf-gjahr.

endif.

sort: i_prps by pspnr,
i_bkpf by bukrs awkey gjahr,
i_makt by matnr,
i_bseg by bukrs belnr gjahr,
i_text by bwart sobkz.

*Move to output table
loop at i_mseg into w_mseg.

move-corresponding w_mseg to w_output.

if not w_mseg-lifnr is initial.
select single name1 from lfa1
into w_output-name1
where lifnr = w_mseg-lifnr.
endif.

read table i_prps into w_prps with key pspnr = w_mseg-ps_psp_pnr
binary search.
if sy-subrc eq 0.

move-corresponding w_prps to w_output.

if not w_prps-fkstl is initial and not w_prps-fkokr is initial.

select single ktext from cskt
into w_output-ktext
where spras = sy-langu
and kokrs = w_prps-fkokr
and kostl = w_prps-fkstl
and datbi ge sy-datum.

endif.

select single addrnum from t499s into vl_addrnum
where stand = w_prps-stort.
select single city1 from adrc into vl_city1
where addrnumber = vl_addrnum
and addr_group = cl_ca01.

w_output-city1 = vl_city1.

endif.

read table i_text into w_text with key bwart = w_mseg-bwart
sobkz = w_mseg-sobkz
binary search.
if sy-subrc eq 0.
w_output-btext = w_text-btext.
endif.

read table i_makt into w_makt with key matnr = w_mseg-matnr
binary search.
if sy-subrc eq 0.
w_output-maktx = w_makt-maktx.
endif.

read table i_bkpf into w_bkpf with key bukrs = w_mseg-bukrs
awkey = w_mseg-awkey
gjahr = w_mseg-mjahr
binary search.
if sy-subrc eq 0.

move-corresponding w_bkpf to w_output.

read table i_bseg into w_bseg with key bukrs = w_bkpf-bukrs
belnr = w_bkpf-belnr
gjahr = w_bkpf-gjahr
binary search.
if sy-subrc eq 0.

move-corresponding w_bseg to w_output.

w_output-matnr = w_mseg-matnr.
w_output-werks = w_mseg-werks.

if w_mseg-shkzg = 'H'. " checking the mseg value.
w_output-wrbtr = - w_bseg-wrbtr.
w_output-menge = - w_output-menge.
endif.

perform cal_vertex_tax using w_bseg changing w_output-wmwst.
endif.
endif.

perform format_output using w_output-matnr
w_output-wrbtr w_output-budat
w_output-rsnum w_output-rspos.

append w_output to i_output.
clear: w_output, vl_city1, w_prps,vl_addrnum, w_text, w_makt,
w_bkpf, w_bseg.

endloop.

endform. " get_data

[Former Member]

Hi Valter,

I had done what u said but 1 problem i am facing is that i am not able to display the data whole of it as i have to make loop on 1 itab.

here's d problem:-

LOOP AT ITBKPF.

• LOOP AT ITBSAK.

READ TABLE ITMKPF WITH KEY MBLNR = ITBKPF-AWKEY(10)

MJAHR = ITBKPF-AWKEY+10(4).

READ TABLE ITBSAK WITH KEY BELNR = ITBKPF-BELNR.

ITFINAL-BELNR = ITBKPF-BELNR.

ITFINAL-GJAHR = ITBKPF-GJAHR.

ITFINAL-AWKEY = ITBKPF-AWKEY.

ITFINAL-AUGBL = ITBSAK-AUGBL.

ITFINAL-AUGDT = ITBSAK-AUGDT.

ITFINAL-WRBTR = ITBSAK-WRBTR.

ITFINAL-LIFNR = ITBSAK-LIFNR.

APPEND ITFINAL.

CLEAR ITFINAL.

ENDLOOP.

*ENDLOOP.

LOOP AT ITFINAL.

WRITE: / ITFINAL-BELNR , ITFINAL-GJAHR, ITFINAL-AWKEY,ITFINAL-AUGBL,ITFINAL-AUGDT,ITFINAL-WRBTR,ITFINAL-LIFNR.

ENDLOOP.

In this if i make loop at itbkpf then pny bkpf data is displayed and if

i make loop on the BSAK only its data is displayed.

plzz help me how to sort it out.

[valter_oliveira]

Hello abaper2008.

I'm not sure if I understood your problem, since BSAK data is related to BKPF data. A lifnr os BSAK is related to a BELNR of BKPF, so it's correct (unless you have a different requirement) to loop BKPF only. However, I would add GJAHR in table ITBSAK and change your code like this:

LOOP AT ITBKPF.

  CLEAR ITMKPF.
  READ TABLE ITMKPF 
   WITH KEY MBLNR = ITBKPF-AWKEY(10)
                    MJAHR = ITBKPF-AWKEY+10(4).
  CHECK sy-subrc EQ 0.

  CLEAR ITBSAK.
  READ TABLE ITBSAK WITH KEY BELNR = ITBKPF-BELNR GJAHR = ITBKPF-GJAHR.
  CHECK sy-subrc EQ 0.

  ITFINAL-BELNR = ITBKPF-BELNR.
  ITFINAL-GJAHR = ITBKPF-GJAHR.
  ITFINAL-AWKEY = ITBKPF-AWKEY.
  ITFINAL-AUGBL = ITBSAK-AUGBL.
  ITFINAL-AUGDT = ITBSAK-AUGDT.
  ITFINAL-WRBTR = ITBSAK-WRBTR.
  ITFINAL-LIFNR = ITBSAK-LIFNR.
  APPEND ITFINAL.
  CLEAR ITFINAL.

ENDLOOP.

LOOP AT ITFINAL.
  WRITE: / ITFINAL-BELNR , ITFINAL-GJAHR, ITFINAL-AWKEY,ITFINAL-AUGBL,ITFINAL-AUGDT,ITFINAL-WRBTR,ITFINAL-LIFNR.
ENDLOOP.

Looping both tables would duplicate records.

Best regards.

Valter Oliveira.

[Former Member]

hi VALTER,

i had changed my code as u said but it is showing nothing it just moves to the selection-screen and den just stays dere. what should i do?

can i send u the code which i am using on your email id?

Edited by: abaper2008 on May 27, 2008 12:12 PM

[valter_oliveira]

Yes, you can send by email.

Regards.

Valter Oliveira.

[Former Member]

Do one thing

1. First fetch the records from the first table

2. use for all entries to fetch the reocrds from second table

3. use for all entries to fetch the reocrds from third table

Loop on first table into wa.

read table 2nd into wa2 with key common filed = wa-common field.

read table 3rd into wa3 with key common filed = wa-common field.

pass output values to final table.

endloop.

[Former Member]

in the bkpf and bsak u have a common field BUKRS(company code) sou can relate with that field i hope.

reward if helpful.

[Former Member]

hi,

HEY VALTER I HAD SENEDED EMAIL ON ur email id and

chandrika chireddy i had done what u had said but still it is not giving the output. any body can tellme what to do now to display the output.

Edited by: abaper2008 on May 27, 2008 12:55 PM

[valter_oliveira]

Hello.

What did you put in selection screen? In my system is getting results. Did you read the comments?

Best regards.

Valter Oliveira.

[Former Member]

hi,

can u send me the which are u executing? bcoz in my system it is not

working on my system.

[valter_oliveira]

I'm using in selection screen, a valid bukrs and gjahr. Check in your system, SE16, table BKPF, if with that bukrs and gjahr you are using you get results (with addiction MSEG in AWTYP of course). If you commented check sy-subrc it should appear something.

Remember that with parameters, you cannot leave them empty. Only with SELECT-OPTIONS you can.

Regards.

Valter.

[Former Member]

HI,,

Can send me the code ,even though i am using select-options also.still it is not working.

[Former Member]

can u send me the code what ur executing and wrote?

[Former Member]

HI,

WHATS UR EMAIL ID?