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.

5 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) 

Leave a Reply