Skip to Content

In other BLOGs, I showed some general techniques that you can use to improve the performance of your ABAP code. In this one, I will show some specific techniques that you can use to retrieve accounting document data.

BSEG is a cluster table and in the forums it is often said that you should avoid selecting directly against this table. This is simply incorrect. While BSEG is large and the non-key fields are not known to the underlying database, it is simply a table and if you take proper care in designing a SELECT statement, you will have no performance problems accessing BSEG.

So the trick is in designing the SELECT statement. Because BSEG is a cluster table, the only key fields allowed are those of the primary key:

 

  • BUKRS – Company Code
  • BELNR – Accounting Document Number
  • GJAHR – Fiscal Year
  • BUZEI – Line Item Number

But do you need to know values for all of these fields in order to maximize performance? The short answer is “yes” if performance is truly to be “maximized”. But in normal circumstances, you may not have values for all of these fields immediately at hand. There are two factors that you have to take into account:

The most important fields to fill are the leading primary key fields. If you know the document number, fiscal year and line item number but not the company code, a SELECT using only these fields will give very poor performance. Imagine trying to find an entry in a large dictionary when you know only the second through fourth letters of a word. You would have to scan the entire dictionary. It’s the same sort of situation here.

The SELECT should return as little data as possible. If you know only the company code but not the other key fields, performance will likely still be very bad. I say “likely” because in your system you may have some company codes that have very few documents posted to them while others have many.

In my experience, I have found that when retrieving data from BSEG, it is necessary and sufficient to have the company code and document number. Anything more is icing that you can use if you have it.

But also in my experience, users don’t usually ask for reports based on document numbers. They need to see data based on customers or cost centers or some other similar criteria. If you try to retrieve data from BSEG using the customer number only, performance will be about as bad is possible.

But many standard SAP programs retrieve accounting data based on these sorts of fields. What magic tricks do SAP programmers have at their disposal that we don’t?

No magic, but there are some tricks that you can use. I’ve written a program that shows different ways that you can use to find a document number. In some cases, you need to know beforehand the company code (or controlling area or funds management area). But, reports usually have this as one of the selection criteria. If not, you can use the technique I have shown in of my other BLOGs.

Before I present the program, there are some caveats:

 

  • Most importantly, this is not complete. I’m showing some techniques that I have learned, but there are many more. This is really just to get you started.
  • I’ve used FOR ALL ENTRIES rather than JOINs. There is probably a small performance hit as I’ve shown in another blog, but I find that hit to be generally small and FOR ALL ENTRIES easier to read. Feel free to use JOINs instead.
  • Configuration may be an issue. I don’t think it should be, but it’s possible some of these techniques will not work in all systems.
  • On the other hand, configuration may work to your advantage. It’s possible (at least in earlier versions) to configure FI documents to have the same number as for example, the corresponding billing document.
  • Archiving may also be an issue. If you have archived financial data you may find that you have to read the archive rather than BKPF or BSEG.
  • I have not done any actual SELECTS against BSEG in this program. I have simply handed control to transaction FB03 which does this for me (based on the primary key).

Anyway, here it is:

 

You may have noticed that the program is incomplete. There is only a stub where the logic to retrieve FI documents for a cheque. This is a challenge to you to figure out how to do this. (Hint – table PAYR may be useful). If you try this, please post your results in the comments section of this BLOG. In a week or so, I will post the code that I created to do this. I am assuming that we are using an accounts payable cheque as an example.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Jonathan Bourne
    Rob, an excellent blog with many great tips. Any ideas how to improve the performance when the need arises to report against creation date? (BKPF-CPUDT)

    Thanks for sharing,
    JB

    (0) 
    1. Jonathan Bourne
      Sorry, realised there is an index in 4.7 (BKPF~5) for BUKRS, CPUDT and BSTAT. I’ll use your tip on retrieving the domain values from BSTAT and feed them into the select statement as a range.

      Thanks again!

      (0) 
      1. Former Member
        You make it look so simple 🙂
        Anyways time for a newbie question. You’ve stressed on the importance of using BUKRS. Suppose(as in the case here), there is only one BURKS(=1000), do I still have to mention it in my select statement?

        Regards,
        pk

        (0) 
          1. Former Member
            Well I did, and did not find much of a difference, which is why I was reluctant to ask you this question even though I had read your blog a Long time ago.
            ST05 Transaction(thanks to Siegfried Boes’s Blog) works crazy(or may be Im doing something wrong). Even without a program change, if I continuously run the Trace 4 or 5 times, each time it returns different execution times.
            Confused and Lost.

            pk

            (0) 
            1. Former Member Post author
              Fair enough – did you do this in a development system where there is not much data or a QA system which should have much more?

              ST05 can be a bit tricky to use. There is an “explain” function that can often be used to compare what the database thinks about how the SELECT will work.

              In any event, I think you should always code the full primary key (including BUKRS).

              Rob

              (0) 
  2. Former Member Post author
    That’s correct. And cluster RFBLG also contains some other SAP tables – BSET and BSEC I believe. So the SAP kernel has to retrieve all records from RFBLG for a BUKRS/BELNR/GJAHR combination even though you also specify BUZEI and then sort out what you are looking for.

    Rob

    (0) 
  3. Former Member

    Hi Rob,

    I am working on a SAP environment where we have databases on Oracle 11gR2 version.

    Earlier Application team is used to pull the data from BSEG table through the Application GUI. But now they are not able to pull that data through Application GUI because of the huge volume of the data.

    Now they asked (from DBA)to pull the same  data directly from the database.

    So required your help to pull the data from BSEG(Cluster Table).

    How to pull the data from Cluster Table BSEG ?

    Thanks & Regards,

    Abhishek

    (0) 
    1. Former Member

      This is the same code as above, in a more readable format.

      REPORT ztest_select.

      * Selecting bkpf and bseg and related tables

      *

      * http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg

      *

      * Tables

      ***************************************************************

      TABLES: bkpf, bseg,

               covp,

               csks,

               glpca,

               bsis, bsas, bsid, bsad, bsik, bsak,

               ekbe, aufk,

               vbfa, vbak,

               vapma,

               fmifiit,

               payr.

      * Global Data

      **********************************************************

      TYPES: BEGIN OF doc,

                bukrs TYPE bseg-bukrs,

                belnr TYPE bseg-belnr,

                gjahr TYPE bseg-gjahr,

                buzei TYPE bseg-buzei,

              END   OF doc.

      DATA: doc_int  TYPE TABLE OF doc,

             doc_wa   TYPE          doc,

             w_repid  TYPE sy-repid VALUE sy-repid,

             no_lines TYPE sy-tabix.

      * Selection Screen

      *****************************************************

      PARAMETERS: p_gjahr TYPE covp-refgj OBLIGATORY.

      SELECTION-SCREEN SKIP.

      PARAMETERS: p_kokrs TYPE csks-kokrs OBLIGATORY,

                   p_kostl TYPE csks-kostl,

                   p_prctr TYPE glpca-rprctr,

                   p_aufnr TYPE aufk-aufnr.

      SELECTION-SCREEN SKIP.

      PARAMETERS: p_bukrs TYPE bsis-bukrs OBLIGATORY,

                   p_budat TYPE bkpf-budat,

                   p_ebeln TYPE ekko-ebeln,

                   p_hkont TYPE bsis-hkont,

                   p_lifnr TYPE bsik-lifnr,

                   p_kunnr TYPE bsid-kunnr.

      SELECTION-SCREEN SKIP.

      PARAMETERS: p_vbeln TYPE vbak-vbeln.

      SELECTION-SCREEN SKIP.

      PARAMETERS: p_matnr TYPE vapma-matnr.

      SELECTION-SCREEN SKIP.

      PARAMETERS: p_fikrs TYPE fmifiit-fikrs,

                   p_fistl TYPE fmifiit-fistl,

                   p_fonds TYPE fmifiit-fonds.

      SELECTION-SCREEN ULINE.

      PARAMETERS: p_hbkid TYPE payr-hbkid,

                   p_hktid TYPE payr-hktid,

                   p_rzawe TYPE payr-rzawe,

                   p_chect TYPE payr-chect.

      START-OF-SELECTION.

      * Retrieve document numbers based on different requirements

      * Posting Date

         PERFORM posting_date_actuals

           USING p_bukrs

                 p_budat.

      * Cost Center

         PERFORM cost_center_actuals

           USING p_kokrs

                 p_kostl

                 p_gjahr.

      * GL Account

         PERFORM gl_actuals

           USING p_bukrs

                 p_hkont

                 p_gjahr.

      * Vendor

         PERFORM vendor_actuals

           USING p_bukrs

                 p_lifnr

                 p_gjahr.

      * Customer

         PERFORM customer_actuals

           USING p_bukrs

                 p_kunnr

                 p_gjahr.

      * Purchase Order

         PERFORM po_actuals

           USING p_ebeln.

      * Sales Order

         PERFORM so_actuals

           USING p_vbeln.

      * Order

         PERFORM order_actuals

           USING p_aufnr

                 p_gjahr.

      * Fund/Fund Center

         PERFORM fm_actuals

           USING p_fikrs

                 p_gjahr

                 p_fistl

                 p_fonds.

      * Profit Center

         PERFORM profit_center_actuals

           USING p_kokrs

                 p_prctr

                 p_gjahr.

      * Material

         PERFORM material_actuals

           USING p_matnr

                 p_gjahr.

      * Cheque number

         PERFORM cheque_actuals

           USING p_hbkid

                 p_hktid

                 p_chect.

      *&———————————————————————*

      *&      Form  posting_date_actuals

      *&———————————————————————*

      *       Use one of the secondary indices of BKPF to retrieve the

      *       document number

      *———————————————————————-*

      FORM posting_date_actuals

         USING    bukrs

                  budat.

         DATA: disp_date(10).

         CHECK NOT budat IS INITIAL.

      * Uses index BKPF~2 (4.7)

         SELECT bukrs belnr gjahr

           INTO TABLE doc_int

           UP TO 100 ROWS

           FROM bkpf

           WHERE bukrs = bukrs  AND

      * Normally, you would probably only want normal documents, that is

      * documents with BSTAT = ‘ ‘. So you would change the next line.

      * On the other hand, you might want documents for all values of BSTAT,

      * but not want to hardcode the values. In that case, you can retrieve

      * values from the domain of BSTAT and put them in a range table and

      * use the range table in the next line.

                 bstat IN (‘ ‘, ‘A’, ‘B’, ‘D’, ‘M’, ‘S’, ‘V’, ‘W’, ‘Z’) AND

                 budat = budat.

          CHECK sy-subrc = 0.

          WRITE budat TO disp_date.

          PERFORM display_documents

            TABLES doc_int

            USING ‘Posting date’

                  disp_date

                  space

                  space.

        ENDFORM.                    ” posting_date_actuals

      *&———————————————————————*

      *&      Form  cost_center_actuals

      *&———————————————————————*

      *       Retrieve documents for a cost center

      *———————————————————————-*

      FORM cost_center_actuals

         USING    kokrs

                  kostl

                  gjahr.

         DATA: covp_int TYPE TABLE OF covp,

               disp_cc(10).

         CHECK NOT kostl IS INITIAL.

      * Uses primary index (4.7)

         SELECT SINGLE objnr

           FROM csks

           INTO csks-objnr

           WHERE kokrs = kokrs

             AND kostl = kostl.

          CHECK sy-subrc = 0.

      * COVP is a view. This uses index COEP~1 (4.7)

         SELECT refbk refbn refgj refbz

           FROM covp

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE lednr = ’00’

             AND objnr = csks-objnr

             AND gjahr = gjahr

             AND wrttp IN (’04’, ’11’)

             AND versn = ‘000’.

          CHECK sy-subrc = 0.

          CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

            EXPORTING

              input  = kostl

            IMPORTING

              output = disp_cc.

          PERFORM display_documents

            TABLES doc_int

            USING ‘Cost Center’

                  disp_cc

                  space

                  space.

      ENDFORM.                     ” cost_center_actuals

      *&———————————————————————*

      *&      Form  gl_actuals

      *&———————————————————————*

      *       BKPF and BSEG have a number of secondary index tables. These are

      *       tables that are indexed by GL customer or vendor number and have

      *       data that is in both BKPF and BSEG. These secondary index tables

      *       that have an ‘i’ in the third character of the name contain open

      *       items. Those with an ‘a’ contain cleared items. In practice, you

      *       may only one or the other. In this program I am retrieving both.

      *

      *       Here we get documents related to a GL.

      *———————————————————————-*

      FORM gl_actuals

          USING    bukrs

                   hkont

                   gjahr.

          DATA: disp_gl(10).

          CHECK NOT hkont IS INITIAL.

      * Uses primary index (4.7)

         SELECT bukrs belnr gjahr buzei

           FROM bsis

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE bukrs = bukrs

             AND hkont = hkont

             AND gjahr = gjahr.

      * Uses primary index (4.7)

         SELECT bukrs belnr gjahr buzei

           FROM bsas

           APPENDING TABLE doc_int

           UP TO 100 ROWS

           WHERE bukrs = bukrs

             AND hkont = hkont

             AND gjahr = gjahr.

         DESCRIBE TABLE doc_int LINES no_lines.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

            EXPORTING       input  = hkont

            IMPORTING       output = disp_gl.

         PERFORM display_documents

            TABLES doc_int

            USING ‘GL Account’

                  disp_gl

                  space

                  space.

      ENDFORM.                    ” gl_actuals

      *&———————————————————————*

      *&      Form  vendor_actuals

      *&———————————————————————*

      *       Here we get documents related to a vendor.

      *———————————————————————-*

      FORM vendor_actuals

         USING    bukrs

                  lifnr

                  gjahr.

         DATA: disp_vendor(10).

         CHECK NOT lifnr IS INITIAL.

      * Uses primary index (4.7)

         SELECT bukrs belnr gjahr buzei

           FROM bsik

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE bukrs = bukrs

             AND lifnr = lifnr

             AND gjahr = gjahr.

      * Uses primary index (4.7)

         SELECT bukrs belnr gjahr buzei

           FROM bsak

           APPENDING TABLE doc_int

           UP TO 100 ROWS

           WHERE bukrs = bukrs

             AND lifnr = lifnr

             AND gjahr = gjahr.

         DESCRIBE TABLE doc_int LINES no_lines.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

           EXPORTING       input  = lifnr

           IMPORTING       output = disp_vendor.

         PERFORM display_documents

           TABLES doc_int

           USING ‘Vendor’

                 disp_vendor

                 space

                 space.

      ENDFORM.                    ” vendor_actuals

      *&———————————————————————*

      *&      Form  customer_actuals

      *&———————————————————————*

      *       Here we get documents related to a customer.

      *———————————————————————-*

      FORM customer_actuals

         USING  bukrs

                kunnr

                gjahr.

        DATA: disp_customer(10).

        CHECK NOT kunnr IS INITIAL.

      * Uses primary index (4.7)

         SELECT bukrs belnr gjahr buzei

           FROM bsid

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE bukrs = bukrs

             AND kunnr = kunnr

             AND gjahr = gjahr.

      * Uses primary index (4.7)

         SELECT bukrs belnr gjahr buzei

           FROM bsad

           APPENDING TABLE doc_int

           UP TO 100 ROWS

           WHERE bukrs = bukrs

             AND kunnr = kunnr

             AND gjahr = gjahr.

         DESCRIBE TABLE doc_int LINES no_lines.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

            EXPORTING       input  = kunnr

            IMPORTING       output = disp_customer.

         PERFORM display_documents

           TABLES doc_int

           USING ‘Customer’

                 disp_customer

                 space

                 space.

      ENDFORM.                    ” customer_actuals

      *&———————————————————————*

      *&      Form  po_actuals

      *&———————————————————————*

      *       Table BKPF has a useful index on AWTYP and AWKEY. Here, we use

      *       this to retrieve documents for purchase orders.

      *———————————————————————-*

      FORM po_actuals

         USING    ebeln.

         TYPES: BEGIN OF ekbe_type,

                  belnr TYPE ekbe-belnr,

                  gjahr TYPE ekbe-gjahr,

                  vgabe TYPE ekbe-vgabe,

                END   OF ekbe_type.

         DATA: ekbe_int TYPE TABLE OF ekbe_type,

               ekbe_wa  TYPE          ekbe_type.

         DATA: v_reference    TYPE bkpf-awtyp,           “Reference procedure

               v_objectkey    TYPE bkpf-awkey.           “Object key

         DATA:disp_po(10).

         CHECK NOT ebeln IS INITIAL.

      * Uses primary index (4.7)

         SELECT belnr gjahr

           FROM ekbe

           INTO TABLE ekbe_int

           UP TO 100 ROWS

           WHERE ebeln = ebeln

           AND vgabe IN (‘1’, ‘2’).                      “1 – GR, 2 – IR

         CHECK sy-subrc = 0.

         SORT ekbe_int.

         DELETE ADJACENT DUPLICATES FROM ekbe_int.

         LOOP AT ekbe_int INTO ekbe_wa.

           v_objectkey+00(10) = ekbe_wa-belnr.

           v_objectkey+10(10) = ekbe_wa-gjahr.           “BELNR+YEAR

           IF ekbe_wa-vgabe = ‘1’.

             v_reference = ‘MKPF’.

           ELSE.

             v_reference = ‘RMRP’.

           ENDIF.

      * Uses index BKPF~4 (4.7)

           SELECT SINGLE bukrs belnr gjahr               “Accounting Doc Header

             FROM bkpf

             INTO doc_wa

             WHERE awtyp =  v_reference

               AND awkey =  v_objectkey.

           IF sy-subrc = 0.

             APPEND doc_wa TO doc_int.

           ENDIF.

         ENDLOOP.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

            EXPORTING       input  = ebeln

            IMPORTING       output = disp_po.

         PERFORM display_documents

            TABLES doc_int

            USING ‘Purchase Order’

                  disp_po

                  space

                  space.

      ENDFORM.                    ” po_actuals

      *&———————————————————————*

      *&      Form  so_actuals

      *&———————————————————————*

      *       Use AWTYP and AWKEY to retrieve documents related to sales

      *———————————————————————-*

      FORM so_actuals

        USING    vbeln.

         TYPES: BEGIN OF vbfa_type,

                  vbeln TYPE vbfa-vbeln,

               END   OF vbfa_type.

         DATA: vbfa_int TYPE TABLE OF vbfa_type,

               vbfa_wa  TYPE          vbfa_type.

         DATA: v_reference    TYPE bkpf-awtyp,           “Reference procedure

               v_objectkey    TYPE bkpf-awkey.           “Object key    DATA:disp_so(10).

         CHECK NOT vbeln IS INITIAL.

      * Uses primary index (4.7)

         SELECT vbeln

           FROM vbfa

           INTO TABLE vbfa_int

           UP TO 100 ROWS

           WHERE vbelv   = vbeln

             AND vbtyp_n = ‘P’.                          “Debit memo

          CHECK sy-subrc = 0.

          SORT vbfa_int.

          DELETE ADJACENT DUPLICATES FROM vbfa_int.

          LOOP AT vbfa_int INTO vbfa_wa.

            v_objectkey+00(10) = vbfa_wa-vbeln.           “BELNR

            v_reference        = ‘VBRK’.

      * Uses index BKPF~4 (4.7)

           SELECT SINGLE bukrs belnr gjahr               “Accounting Doc Header

             FROM bkpf

             INTO doc_wa

             WHERE awtyp =  v_reference

               AND awkey =  v_objectkey.

           IF sy-subrc = 0.

             APPEND doc_wa TO doc_int.

           ENDIF.

         ENDLOOP.

         DESCRIBE TABLE doc_int LINES no_lines.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

           EXPORTING       input  = vbeln

           IMPORTING       output = disp_so.

         PERFORM display_documents

           TABLES doc_int

           USING ‘Sales Document’

                 disp_so

                 space

                 space.

      ENDFORM.                    ” so_actuals

      *&———————————————————————*

      *&      Form  order_actuals

      *&———————————————————————*

      *       Retrieve documents related to an order

      *———————————————————————-*

      FORM order_actuals

         USING    aufnr

                  gjahr.

          DATA: disp_order(10).

          CHECK NOT aufnr IS INITIAL.

      * Uses primary index (4.7)

         SELECT SINGLE objnr

           FROM aufk

           INTO aufk-objnr

           WHERE aufnr = aufnr.

          CHECK sy-subrc = 0.

      * COVP is a view. This uses index COEP~1 (4.7)

         SELECT refbk refbn refgj refbz

           FROM covp

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE lednr = ’00’

             AND objnr = aufk-objnr

             AND gjahr = gjahr

             AND wrttp IN (’04’, ’11’)

             AND versn = ‘000’.

          CHECK sy-subrc = 0.

          CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

           EXPORTING       input  = aufnr

           IMPORTING       output = disp_order.

          PERFORM display_documents

           TABLES doc_int

           USING ‘Order’

                 disp_order

                 space

                 space.

      ENDFORM.                    ” order_actuals

      *&———————————————————————*

      *&      Form  FM_actuals

      *&———————————————————————*

      *       Not many institutions use Funds Management, but if you do, this

      *       is how to relate funds management documents to FI documents.

      *———————————————————————-*

      FORM fm_actuals

         USING    fikrs

                  gjahr

                  fistl

                  fonds.

         DATA: disp_cfc(10),

               disp_fund(10).

         CHECK NOT fikrs IS INITIAL AND

               NOT fistl IS INITIAL.

      * Uses index FMIFIIT~3 (4.7)

         SELECT bukrs knbelnr kngjahr knbuzei

           FROM  fmifiit

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE   fistl  = fistl

             AND   fonds  = fonds.

         CHECK sy-subrc = 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

            EXPORTING       input  = fistl

            IMPORTING       output = disp_cfc.

         IF NOT fonds IS INITIAL.

           CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

             EXPORTING         input  = fonds

             IMPORTING         output = disp_fund.

         ENDIF.

         PERFORM display_documents

            TABLES doc_int

            USING ‘Fund Center’

                  disp_cfc

                  ‘Fund’

                  disp_fund.

      ENDFORM.                    ” FM_actuals

      *&———————————————————————*

      *&      Form  profit_center_actuals

      *&———————————————————————*

      *       Retrieve documents related to a profit center

      *———————————————————————-*

      FORM profit_center_actuals

          USING    kokrs

                   prctr

                   gjahr.

        DATA: disp_pc(10).

        CHECK NOT prctr IS INITIAL.

      * This uses index GLPCA~1 (4.7)

        SELECT rbukrs refdocnr refryear refdocln

           FROM glpca

           INTO TABLE doc_int

           UP TO 100 ROWS

           WHERE kokrs  = kokrs

             AND ryear  = gjahr

             AND rprctr = prctr

             AND awtyp  = ‘BKPF’.

        CHECK sy-subrc = 0.

        CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

           EXPORTING       input  = prctr

           IMPORTING       output = disp_pc.

        PERFORM display_documents

           TABLES doc_int

           USING ‘Profit Center’

                 disp_pc

                 space

                 space.

      ENDFORM.                    ” profit_center_actuals

      *&———————————————————————*

      *&      Form  material_actuals

      *&———————————————————————*

      *       Get FI documents for a material.

      *       For purchase orders get:

      *         goods receipts

      *         invoice receipts

      *       For sales orders get:

      *         debit memos

      *———————————————————————-*

      FORM material_actuals

         USING    matnr

                  gjahr.

         TYPES: BEGIN OF ekpo_type,

                  ebeln TYPE ekpo-ebeln,

                  ebelp TYPE ekpo-ebelp,

                END   OF ekpo_type.

         TYPES: BEGIN OF ekbe_type,

                  belnr TYPE ekbe-belnr,

                  gjahr TYPE ekbe-gjahr,

                  vgabe TYPE ekbe-vgabe,

                END   OF ekbe_type.

         TYPES: BEGIN OF vapma_type,

                  vbeln TYPE vapma-vbeln,

                  posnr TYPE vapma-posnr,

                END   OF vapma_type.

         TYPES: BEGIN OF vbfa_type,

                  vbeln TYPE vbfa-vbeln,

                  posnv TYPE vbfa-posnv,

                END   OF vbfa_type.

         DATA: ekpo_int TYPE TABLE OF ekpo_type,

               ekpo_wa  TYPE          ekpo_type.

         DATA: ekbe_int TYPE TABLE OF ekbe_type,

               ekbe_wa  TYPE          ekbe_type.

         DATA: vapma_int TYPE TABLE OF vapma_type,

               vapma_wa  TYPE          vapma_type.

         DATA: v_reference    TYPE bkpf-awtyp,           “Reference procedure

               v_objectkey    TYPE bkpf-awkey.           “Object key

         DATA: vbfa_int TYPE TABLE OF vbfa_type,

               vbfa_wa  TYPE          vbfa_type.

         DATA: disp_mat(10).

         CHECK NOT matnr IS INITIAL.

      * First, find purchase orders for the material

      * This uses index EKPO~1 (4.7)

         SELECT ebeln ebelp

           FROM ekpo

           INTO TABLE ekpo_int

           UP TO 100 ROWS

           WHERE matnr = matnr.

      * Now get the FI document numbers as above

      * Uses primary index (4.7)

         SELECT belnr gjahr vgabe

           FROM ekbe

           INTO TABLE ekbe_int

           UP TO 100 ROWS

           FOR ALL ENTRIES IN ekpo_int

           WHERE ebeln = ekpo_int-ebeln

             AND ebelp = ekpo_int-ebelp

             AND vgabe IN (‘1’, ‘2’).                      “1 – GR, 2 – IR

         CHECK sy-subrc = 0.

         SORT ekbe_int.

         DELETE ADJACENT DUPLICATES FROM ekbe_int.

         LOOP AT ekbe_int INTO ekbe_wa.

           v_objectkey+00(10) = ekbe_wa-belnr.

           v_objectkey+10(10) = ekbe_wa-gjahr.           “BELNR+YEAR

           IF ekbe_wa-vgabe = ‘1’.

             v_reference = ‘MKPF’.

           ELSE.

             v_reference = ‘RMRP’.

           ENDIF.

      * Uses index BKPF~4 (4.7)

           SELECT SINGLE bukrs belnr gjahr               “Accounting Doc Header

             FROM bkpf

             INTO doc_wa

             WHERE awtyp =  v_reference

               AND awkey =  v_objectkey.

           IF sy-subrc = 0.

             APPEND doc_wa TO doc_int.

           ENDIF.

         ENDLOOP.

      * Next get the sales orders for the material

      * Note – I am using an artificial date range here because of archiving

      * in our system. Feel free to remove it.

         SELECT vbeln posnr

           FROM vapma

           INTO TABLE vapma_int

           UP TO 100 ROWS

           WHERE matnr = matnr

             AND audat BETWEEN ‘20070101’ AND ‘20071231’.

      * Uses primary index (4.7)

         SELECT vbeln posnv

           FROM vbfa

           INTO TABLE vbfa_int

           FOR ALL ENTRIES IN vapma_int

           WHERE vbelv   = vapma_int-vbeln

             AND posnv   = vapma_int-posnr

             AND vbtyp_n = ‘P’.                          “Debit memo

         CHECK sy-subrc = 0.

         SORT vbfa_int.

         DELETE ADJACENT DUPLICATES FROM vbfa_int.

         LOOP AT vbfa_int INTO vbfa_wa.

           CLEAR: v_objectkey,

                  v_reference.

           v_objectkey+00(10) = vbfa_wa-vbeln.           “BELNR

           v_reference        = ‘VBRK’.

      * Uses index BKPF~4 (4.7)

           SELECT SINGLE bukrs belnr gjahr               “Accounting Doc Header

             FROM bkpf

             INTO doc_wa

             WHERE awtyp =  v_reference

               AND awkey =  v_objectkey.

           IF sy-subrc = 0.

             APPEND doc_wa TO doc_int.

           ENDIF.

         ENDLOOP.

         DESCRIBE TABLE doc_int LINES no_lines.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

           EXPORTING       input  = matnr

           IMPORTING       output = disp_mat.

         PERFORM display_documents

           TABLES doc_int

           USING ‘Material’

                 disp_mat

                 space

                 space.

      ENDFORM.                    ” material_actuals

      *&———————————————————————*

      *&      Form  cheque_actuals

      *&———————————————————————*

      *       Retrieve FI documents for a cheque. We are assuming Accounts

      *       Payable related.

      *———————————————————————-*

      FORM cheque_actuals

         USING    hbkid

                  hktid

                  chect.

         DATA: belnr TYPE payr-vblnr,

               gjahr TYPE payr-gjahr,

               lifnr TYPE payr-lifnr,

               laufd TYPE payr-laufd,

               disp_cheque(13).

         CHECK NOT p_chect IS INITIAL.

      * Retrieve the cheque data using the primary key (4.7)

      * We are assuming a vendor payment here

         SELECT SINGLE vblnr gjahr lifnr laufd

           FROM  payr

           INTO (belnr, gjahr, lifnr, laufd )

           WHERE  zbukr  = p_bukrs

             AND  hbkid  = p_hbkid

             AND  hktid  = p_hktid

             AND  rzawe  = p_rzawe

             AND  chect  = p_chect.

      * Now get the accounting documents

         SELECT bukrs belnr gjahr

           FROM  bsak

           INTO TABLE doc_int

                WHERE  bukrs  = p_bukrs

                AND    lifnr  = lifnr

                AND    umsks  = ‘ ‘

                AND    umskz  = ‘ ‘

                AND    augdt  = laufd

                AND    augbl  = belnr

                AND    gjahr  = gjahr.

         DESCRIBE TABLE doc_int LINES no_lines.

         CHECK no_lines > 0.

         CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’

           EXPORTING       input  = chect

           IMPORTING       output = disp_cheque.

         PERFORM display_documents

           TABLES doc_int

           USING ‘Cheque number’

                 disp_cheque

                 space

                 space.

      ENDFORM.                    ” cheque_actuals

      *&———————————————————————*

      *&      Form  display_documents

      *&———————————————————————*

      *       At this point, you have the company code, document number and

      *       fiscal year in table DOC_INT. Here, I just use a standard SAP

      *       function module and transaction to display documents and

      *       details, but you could use the same information to retrieve

      *       document data from BKPF and/or BSEG.

      *———————————————————————-*

      FORM display_documents

         TABLES   doc_int  STRUCTURE doc_wa

         USING    doc_source_1

                  source_value_1

                  doc_source_2

                  source_value_2.

         TYPE-POOLS: slis.

         DATA: sel     TYPE  slis_selfield.

         DATA: title   TYPE string.

         CONCATENATE ‘FI line items for’ doc_source_1 source_value_1

                      INTO title SEPARATED BY space.

         IF NOT source_value_2 IS INITIAL.

           CONCATENATE title doc_source_2 source_value_2

                       INTO title SEPARATED BY space.

         ENDIF.

         CALL FUNCTION ‘REUSE_ALV_POPUP_TO_SELECT’

           EXPORTING

              i_title                 = title

              i_selection             = ‘X’

              i_tabname               = ‘BSEG’

              i_structure_name        = ‘BSEG’

              i_callback_user_command = ‘USER_COMMAND’

              i_callback_program      = w_repid

           IMPORTING

              es_selfield             = sel

           TABLES

              t_outtab                = doc_int.

         IF NOT sel IS INITIAL.

            READ TABLE doc_int INDEX sel-tabindex.

            SET PARAMETER ID ‘BUK’ FIELD doc_int-bukrs.

            SET PARAMETER ID ‘BLN’ FIELD doc_int-belnr.

            SET PARAMETER ID ‘GJR’ FIELD doc_int-gjahr.

            CALL TRANSACTION ‘FB03’ AND SKIP FIRST SCREEN.

         ENDIF.

      ENDFORM.                    ” display_documents

      * End of program

      (0) 
  4. Former Member

     

    ” Imagine trying to find an entry in a large dictionary when you know only the second through fourth letters of a word. You would have to scan the entire dictionary. It’s the same sort of situation here”

     

    Actually, you would only need to look in 26 distinct locations.

    (0) 

Leave a Reply