Skip to Content

This document explains how can we create a purchasing history report with query. In standart MM Module , there are a lot of reports like ME80FN , ME2* , MB5S etc. They are used to get your requirements. But , customers need to get a lot of details and different variances report formats.However , you can not find a solution for SAP will not display POs in ME80FN that are deleted. For this reasons, we can develop a report to respond all requirements. In generally , this report has to contain PO/SA/Contract –> GR –> IR chain. If customers get this information in report , they would create a lot of report in excel.

In this document , a report will be designed to meet the needs with SAP Query.


Related Tcodes : SQ01 , SQ02 , SQ03 , SQ10 , SU24 , SE93

Related Tables : EKBE , EKKO , EKPO , LFA1 , EKKN , RBKP,RSEG

Helpful documents in SCN :

Making of Boxed Reports for BOMs (using LDBs in Infoset Query)

GOS : Value Addition to your Infoset Query

Query Report Tips Part 2 – Mandatory Selection Field And Authorization Check

Calling Reports from an Infoset Query

Some tips on ABAP query (SQ01) Part 2

How to create Report by using SQVI

Firstly , i create a Infoset to get data.


—> Go to SQ02 tcode create a infoset named ZEKBE or what you want.


Also , there are some extras. For example , reversal of GR or IR can be report also , customers can want to eliminate reversal docoment and its real document. In belowed , ZTK field is added to get a key for reversal documents relations.


1.JPG

 

—> To respond reversal documents key requirement , we can write a code block so we have to read another tables.


Note : Be careful careful , because BELNR –> Document Number of an Invoice Document is repeat again every year.


data : i_mblnr like mseg-mblnr.
tables: rbkp.
data : begin of fat_tab occurs 0,
      menge like ekbe-menge,
      shkzg like ekbe-shkzg,
      xblnr like ekbe-xblnr,
      end of fat_tab.
data s_menge like ekbe-menge.
break ounal.
clear zztk.
clear fat_tab. refresh fat_tab.

if ekbe-bewtp = 'E' and ekbe-bwart = '102' and
  ekbe-belnr ne ekbe-lfbnr.
  zztk = 'X'.
elseif ekbe-bewtp = 'E' and ekbe-bwart = '101'.
  select single mblnr from mseg into i_mblnr
    where smbln = ekbe-belnr and
    smblp = ekbe-buzei and sjahr = ekbe-gjahr.
  if sy-subrc is initial.
    zztk = 'X'.
  else.
endif.

* Modified by M.Ozgur Unal - 14.01.2015

elseif ekbe-bewtp = 'Q'.
  select * from rbkp where belnr = ekbe-belnr
                     and blart = 'TK'.
  endselect.
  if sy-subrc is initial.
    zztk = 'X'.
  else.
    select * from rbkp where stblg = ekbe-belnr
                         and gjahr = ekbe-gjahr.
    endselect.
    if sy-subrc is initial.
      zztk = 'X'.
    endif.
  endif.
endif.

   2.JPG


—>Also , there are another adding fields like ‘Amount of quantity delivery note X unit price ‘ –> ZZDMBTR type P



data: out like ekbe-lsmng.
*if ekpo-bprme ne ekbe-lsmeh.
call function 'ZMM_UNIT_CONV'
  exporting
    i_matnr                 = ekbe-matnr
    i_source_value          = ekbe-lsmng
    i_source_unit           = ekbe-lsmeh
    i_target_unit           = ekpo-bprme
  importing
    e_target_value          = out
*     E_MATNR_SUBRC            =
*   EXCEPTIONS
*     MATNR_INVALID            = 1
*     SOURCE_UOM_INVALID       = 2
*     TARGET_UOM_INVALID       = 3
*     OTHERS                   = 4
          .
if sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
endif.
zzdmbtr = out * ekpo-netpr / ekpo-peinh.
*ENDIF.

 

For ABAPer , Transaction/event type, purchase order history has to known to design report.

1 Goods Receipt
2 Invoice Receipt
3 Subseq. Debit/Credit
4 Down Payment
5 Payment
6 Goods Issue for Stock Transfer
7 Consumption (Subcontracting)
8 Delivery (Stock Transfer)
9 Service Entry Sheet
   A Down Payment Request
  C Down Payment Clearing
  Q Comparison for Internally Posted Material (Only IS-OIL)
  R Return Delivery via Delivery Note
  P Invoice Parking
  V Down Payment Request Clearing


a

s—

Join logic can be designed like belowed.

3.JPG

—> Secondly , Go to SQ01 and create your query.

4.JPG

—> Select fiedls of ‘display and fields appearing on the selection screen’.

5.JPG

—> Finally , you can get all details for PO–>GR–>IR chain.

6.JPG

—> Also , you can authorize end users.

—> Assign infoset to role

7.JPG

—> Go to SU24 , this has to be defined for tcode and authorization objects.

8.JPG

—> Execute (F8)

9.JPG

9.JPG

—> Go to SE93 , define your tcode and link program name to tcode.

10.JPG

   Regards.

   M.Ozgur Unal



<

To report this post you need to login first.

6 Comments

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

      1. Osvaldo Lopez

        Hi Mehmet Ozgur Unal !!

        Only to someone?

        You can bet that will be usefull to many people

        I appreciate the document structure, screenshots, and links to related documents.

        As I said before, this is an excellent document. And again I thank you for sharing it with the community of SAP.

        Greetings,

        Oswald

        (0) 
  1. Manikanthan Venkateswaran

    Thanks a ton Mr Mehmet Ozgur Unal for the wonderful blog.

    Are the ABAP code segments given by you to be incorporated (Must and Should, is it) to get the output suggested by you..

    What if i go without the ABAP bit as SQ01 / SQVI affords me the query feature without depending on an ABAP consultant…

     

    Regards

    V Manikanthan

    (1) 

Leave a Reply