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.
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.
--->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.
---> Secondly , Go to SQ01 and create your query.
---> Select fiedls of 'display and fields appearing on the selection screen'.
---> Finally , you can get all details for PO-->GR-->IR chain.
---> Also , you can authorize end users.
---> Assign infoset to role
---> Go to SU24 , this has to be defined for tcode and authorization objects.
---> Execute (F8)
---> Go to SE93 , define your tcode and link program name to tcode.
Regards.
M.Ozgur Unal
<
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 |