How Can We Create A Purchasing History Report With Query

Övünç DİNÇ · 21 Ağu 2017

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.

—> 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.

Kod:

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.
[B][/B]
* Modified by Sapofis

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.

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

Kod:

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.