SAP stores all the SD key figures (net value, weight, volume, cost) in the data base as absolute values. In my billing query I had an invoice of 100 and a credit note of 100. Both with positive signs. In the query their sum was 200. Obviously I need to do something.
In the accounting document there is a special field(SHKZG) that indicates whether it is as debit or credit posting. Is there something similar in billing document?
In the billing header table (VBRK) there are no fields that uniquely determine the sign. Only the Billing Type (VBRK-FKART) and Document Category (VBRK-VBTYP) could be used for this purpose. Disadvantage is that you need to know, which types are used and which of them produce negative values.
In the item table (VBRP) there is are promising field VBRP-SHKZG with somewhat strange name ‘Returns Item’. In accounting document the field SHKZG indicates debit and credit. A closer look at VBRP reveals that field SHKZG does identify the sign also in sales documents. Value X indicates that the value is negative. Multiplication with -1 corrects the sign in the report.
As I was unsure of the alternative values (A,B,X) I used QuickViewer to check the values in IDES and a couple of productive ECC systems. Billing types RE, G2, S1 all had value X in this field.
Local Field solution
First I tried to solve the sign problem at query level using Local fields. The formulas for both Billing type (VBRK-FKART) and Returns Item (VBRP-SHKZG) worked fine. The shortage of Local field solution is the need to define them separately in every query.
Additional Field solution
The ideal solution would be to include the definition in the InfoSet, but it requires coding. In Stephan Kaleske’s excellent book ‘SAP Query Reporting’ I learned how this is done using field SHKZG. I am borrowing the code in my query.
You can check the result in the picture above.
Below you find two documents that describe both solutions.