SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Quickly Retrieving FI document Data from BSEG



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> FI
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1640

PostPosted: Tue Nov 20, 2007 2:43 pm    Post subject: Quickly Retrieving FI document Data from BSEG Reply with quote

Quickly Retrieving FI document Data from BSEG
Rob Burbank

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:

Code:
REPORT ztest_select.

* 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.
* Insert your code here

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


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.

Rob Burbank is a programmer/analyst in the Department of Administrative Management Systems at the University of Toronto.

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/7692
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> FI All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.