Skip to Content
author's profile photo Former Member
Former Member

Fectching data in a optimised way from BKPF and BSEG.

Hello experts,

i have a requirement of picking up all the documents from BKPF and BSEG based on a creation date modification date dunning date clearing date.

since the dates are spread across both the tables. i am not able to query both in a optimised way.

please provide the suggestion in optimising the same.

regards,

Sri.

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Best Answer
    Posted on Nov 13, 2012 at 01:50 PM

    Try to use the FOR ALL ENTRIES with BYPASSING BUFFER

    Select * FROM BSEG BYPASSING BUFFER

    FOR ALL ENTRIES IN I_BKPF Where Condition.

    If you have more entries you can try the package data option.

    Let me know if there are any issues.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 12, 2012 at 12:07 PM

    Hi,

    Did you consider using Logical DB to extract your data ?

    Check in TCODE SLDB the logical databses for accounting (like BRM )

    -

    Andrei

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 13, 2012 at 08:06 AM

    Hi,

    this is not easy as BSEG is a cluster table (RFBLG) so secondary indexes might range from tricky to not possible.

    Without modifying the DDIC your current option is only to get the fewest number of records from BKPF, then do the join to BSEG and filter. This will be as bad as it sounds.

    Can you specify the exact conditions you need to provide, esp. if there are duplicates for the date values in the logical conditions. Do you have ranges or equal conditions on these date columns?

    It is also important if any conditions on BKPF make selection on BSEG obsolete.

    Do you need recent data (as of today) or are you doing a query to do some analysis on aged data (as in "last month or older"). If you only need aged data, it might be worth createing some kind of matchcode table in a seperate run first, to get the BSEG keys by going to the matchcode table first.

    The idea would be to create the MC table first (once a month) and then, for doing the analysis, doing the query on the MC table in an indexed way.

    Volker

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 13, 2012 at 08:40 AM

    As you require data from items like clearing date or dunning date, did you look for secondary index tables like BSID/BSAD for FI-AR (BSIK/BSAK for FI-AP or BSIS/BSAS for FI-GL) and not BSEG ?

    Regards;

    Raymond

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 13, 2012 at 10:25 PM

    doesn't it sound like a prime requirement for implementing HANA? once you load all line items into memory, performance should be acceptable to any financial user with excel pivot and mdx skills.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 14, 2012 at 02:45 AM

    Hello all,

    Many thanks for your replys.

    My exact requirement is like this.

    This is part of feedback report where data would be sent to a legacy system, Based on the date given on selection screen. Date would be a single value.

    i need to identify documents which are created/updated/cleared/dunned on or after the date entered on the section screen. i require some data from both BKPF and BSEG(only customer or vendor line item) to be passed to legacy system. which forces me to select multiple times from BKPF and BSEG with out key fileds in selection criteria.

    if i use JOIN will it be more optimised.

    Ofcourse views on BKPF and BSEG is not possible.

    As of now i have used the following logic.

    if gt_t001 is not initial.


    select bukrs belnr gjahr xblnr
    bktxt waers awkey hwaer
    into table gt_bkpf
    from bkpf
    for all entries in gt_t001
    where bukrs eq gt_t001-bukrs
    and ( cpudt ge p_date or aedat ge p_date )
    and ( grpid like w_lcode1 or grpid like w_lcode2 ).

    if gt_bkpf is not initial.


    sort gt_bkpf by bukrs belnr gjahr.

    select bukrs belnr gjahr augdt augcp koart
    dmbtr wrbtr zfbdt zterm madat
    into table gt_bseg
    from bseg
    for all entries in gt_bkpf
    where bukrs eq gt_bkpf-bukrs
    and belnr eq gt_bkpf-belnr
    and gjahr eq gt_bkpf-gjahr
    and ( koart eq 'K' or koart eq 'D' ).

    if sy-subrc eq 0.
    sort gt_bseg by bukrs belnr gjahr.
    refresh gt_bkpf.
    endif.

    select bukrs belnr gjahr augdt augcp koart
    dmbtr wrbtr zfbdt zterm madat
    appending table gt_bseg
    from bseg
    for all entries in gt_t001
    where bukrs eq gt_t001-bukrs
    and ( augdt ge p_date or madat ge p_date )
    and ( koart eq 'K' or koart eq 'D' ).

    if gt_bseg is not initial.


    sort gt_bseg by bukrs belnr gjahr.
    delete adjacent duplicates from gt_bseg.

    select bukrs belnr gjahr xblnr
    bktxt waers awkey hwaer
    into table gt_bkpf
    from bkpf
    for all entries in gt_bseg
    where bukrs eq gt_bseg-bukrs
    and belnr eq gt_bseg-belnr
    and gjahr eq gt_bseg-gjahr
    and ( grpid like w_lcode1 or grpid like w_lcode2 ).


    if sy-subrc eq 0.
    sort gt_bkpf by bukrs belnr gjahr.
    endif.


    endif.
    endif.
    endif.

    where gt_t001 contains the company codes, w_lcode1 and w_lcode2 contains some identifiers.

    Regards,

    Sri.

    Add a comment
    10|10000 characters needed characters exceeded

    • Another option just came to my mind:

      Omit

      and ( cpudt ge p_date or aedat ge p_date )

      in the first BKPF select and get the BSEG data with BELNR afterwards.

      If the BSEG select (with the date + BELNR WHERE) delivers a hit

      -> store result

      If the BSEG select (with the date + BELNR WHEREs) delivers no hit

      -> check BKPF cpudt aedat and store result if match

      otherwise:discard both

      You can bookmark this thread to have a reference, for ME telling to discard selected data 🤯

      I ALWAYS recommend to only fetch data from the DB that you really need,

      but you are fighting a full RFBLG scan here, this might be worth to discard some rows.

      Volker

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.