How to link bsak and ekbe tables?

[former_member569532]

Hi experts,

I am using the below statements ,

SELECT * INTO CORRESPONDING FIELDS OF TABLE it_bsak

FROM bsak WHERE bukrs eq '1001' AND augdt IN WP_SETDT

and ( belnr like '52%' or belnr like '57%').

SELECT * FROM ekbe

INTO CORRESPONDING FIELDS OF TABLE wt_ekbe

FOR ALL ENTRIES IN it_bsak

WHERE belnr = it_bsak-belnr

and  gjahr = it_bsak-gjahr.

but this statemant is not returning any value ,but if i go to se16f  I can see the data in ekbe table by using bsak-belnr value.

Kindly anyone advise how to get the data from ekbe with bsak-belnr.

Thanks in advance,

Anusha.B

[anubhab]

Hello Anusha,

          As per your second SELECT query you are passing GJAHR along with BELNR. Did you check in SE16 with these combination or only with BELNR values. Check once with these combination of GJAHR and BELNR if you are getting any data. And please check the below link, it might be helpful.

http://scn.sap.com/thread/525433

Hope this will help you to solve your issue.

Regards,

Anubhab

[Former Member]

Hi,

• Based  on the  first internal table data we need to  select the data from the next database table for that we  use  FOR ALL ENTRIES. FOR ALL ENTRIES retrieves data which matches 
• In your case
• After first Select it_bsak  contain no records
• After second select wt_ekbe contain many records
• pls check this code

example

PARAMETERS p_city TYPE spfli-cityfrom.

TYPES: BEGIN OF entry_tab_type,

         carrid TYPE spfli-carrid,

         connid   TYPE spfli-connid,

       END OF entry_tab_type.

TYPES: BEGIN OF result_tab_type,

         carrid   TYPE sflight-carrid,

         connid   TYPE sflight-connid,

         fldate TYPE sflight-fldate,

       END OF result_tab_type.

DATA: entry_tab  TYPE TABLE OF entry_tab_type,

      result_tab TYPE SORTED TABLE OF result_tab_type

                      WITH UNIQUE KEY carrid connid fldate.

SELECT carrid connid

       FROM spfli

       INTO CORRESPONDING FIELDS OF TABLE entry_tab

       WHERE cityfrom = p_city.

IF entry_tab IS NOT INITIAL. <------------------------------------

  SELECT carrid connid fldate

         FROM sflight

         INTO CORRESPONDING FIELDS OF TABLE result_tab

         FOR ALL ENTRIES IN entry_tab

         WHERE carrid = entry_tab-carrid AND

               connid = entry_tab-connid.

ENDIF.

[Former Member]

Dear Anusha,

but this statemant is not returning any value ,but if i go to se16f  I can see the data in ekbe table by using bsak-belnr value.

Now check it in ekbe using the same bsak-belnr and bsak-gjahr and do let us know.

Regards

Purnand

[Former Member]

Hi Anu,

Debug the program and check wheather  it_bsak getting filled or not.

Make a practice of checking the first internal table wheather it is initial or not  before using for all entries.

Regards,

Anoop

[former_member569532]

Hi All,

Thank you for you replies.

Now I have used the below code to get the data.It is retrieving the data but taking more time.Kindly any one advise how to improve performance.

SELECT belnr gjahr augdt shkzg dmbtr INTO TABLE it_bsak

FROM bsak WHERE bukrs eq '1001' AND augdt IN WP_SETDT

and ( belnr like '52%' or belnr like '57%').





loop at it_bsak into wa_bsak.

  it_belnr-belnr = wa_bsak-belnr.

  it_belnr-gjahr = wa_bsak-gjahr.

  append it_belnr.

  clear wa_bsak.

ENDLOOP.



if it_belnr[] is not INITIAL.

SELECT * FROM ekbe

INTO CORRESPONDING FIELDS OF TABLE wt_ekbe

FOR ALL ENTRIES IN it_belnr

WHERE belnr = it_belnr-belnr

and  gjahr = it_belnr-gjahr

and  vgabe >= '2' and vgabe <= '3'.

endif.

Thanks

Anusha

[Former Member]

Hi,

in this select

SELECT belnr gjahr augdt shkzg dmbtr INTO TABLE it_bsak

FROM bsak WHERE bukrs eq '1001' AND augdt IN WP_SETDT

and ( belnr like '52%' or belnr like '57%').

you could add into where condition gjahr (Fiscal Year).

Cheers

Ivan

[former_member569532]

Hi Ivan,

Thank you for your reply.

There is no problem in selecting the data from bsak ,while selecting the data from EKBE it is taking more time.

Thanks in advance,

Anusha

[Former Member]

Hi,

in this select

SELECT * FROM ekbe

INTO CORRESPONDING FIELDS OF TABLE wt_ekbe

FOR ALL ENTRIES IN it_belnr

WHERE belnr = it_belnr-belnr

and  gjahr = it_belnr-gjahr

and  vgabe >= '2' and vgabe <= '3'.  "for me non clear????

you can try work in this way

SELECT (only field that are necessary) FROM ekbe

INTO TABLE wt_ekbe

FOR ALL ENTRIES IN it_belnr

WHERE belnr = it_belnr-belnr

and  gjahr = it_belnr-gjahr

and vgabe IN (2,3).

vgabe = 2 --> Invoice Receipt

vgabe = 3 --> Subseq. Debit/Credit

Cheers

Ivan

[former_member569532]

Hi Ivan.

Thank you for your suggestion.

I am using the below code still it is taking more time to select.Please suggest how to get the data from  ekbe with the  data from bsak in fast way.

select * from ekbe

INTO  CORRESPONDING FIELDS OF TABLE WT_EKBE

FOR ALL ENTRIES IN IT_BELNR

WHERE BELNR = IT_BELNR-BELNR

AND  GJAHR = IT_BELNR-GJAHR

AND ( BELNR LIKE '52%' OR BELNR LIKE '57%')

and gjahr gt '1999'

AND  VGABE IN (2,3).

ENDIF.

Thanks,

Anusha

[Former Member]

Hi,

read this observation and verify

*select * from ekbe "as i said if possible don't use * but you can use necessary field

select field1 field2 from ekbe (example)

into table WT_EKBE
*INTO  CORRESPONDING FIELDS OF TABLE WT_EKBE
FOR ALL ENTRIES IN IT_BELNR
WHERE BELNR = IT_BELNR-BELNR
AND  GJAHR = IT_BELNR-GJAHR
AND ( BELNR LIKE '52%' OR BELNR LIKE '57%')
and gjahr gt '1999'   "for me not correct

*and gjahr gt '1999'  

AND  VGABE IN (2,3).

Cheers

Ivan

[ThomasZloch]

Your WHERE-condition for EKBE does not make use of a primary or secondary index, if EKBE is very large this will "take more time".

You should try to also include EBELN and EBELP in the WHERE-condition for EKBE. BSAK has EBELN and EBELP as well, I'm just not sure if they are filled with the correct values. You can also try to read BSEG with BUKRS, GJAHR, BELNR and BUZEI as found in BSAK, and get EBELN and EBELP from there in an intermediate step.

Also, sort and remove duplicates from your FAE driver table, in addition to checking if it is empty.

If you disclose your requirement, it might be possible to suggest alternative access paths.

Thomas

[former_member569532]

Hi,

My requirement is users will enter the clearing date from bsak on selection screen,from that

I need to display invoice amt ,invoice date ,po details, project details.

Kindly advise how to solve this issue.

Thanks in advance,

Anusha B

[ThomasZloch]

Try applying what I suggested above. Collect EBELN and EBELP values and use these to access EKBE.

Thomas

[former_member569532]

Hi ,

Thank you for youre reply.In my case bseg and bsak tables are not having the ebeln and ebelp values .

[ThomasZloch]

Then it seems you will have to accept the runtime or talk your system admin into creating a secondary index for EKBE fields MANDT, GJAHR and BELNR.

Thomas

[former_member569532]

I have used package size 1000,then my program performance improved.