While adding table KONA in existing query, agreement details not displayed

[Former Member]

Hi experts,

When I am changing an existing query through sq02( I am joining table KONA with already existing tables VBRK & VBRP and adding fields , Agreement number & Validity date from & Valid till) , after execution , query displays 3 additional columns of Agreement number, Validity from & Validiy to, but it ceases to display records of those documents in which rebate is accrued. It only displays those documents where rebate basis is present in condition records of billing item data, but zero rebate is accrued.

Any suggestion are highly welcome.

Best Regards

Vimal

[former_member183879 · Accepted solution]

What is the joining condition between VBRK/VBRP and KONA in the query.

It may not work if your objective is to have the list of invoices which have actually contributed to the rebate agreement. It will only tell you the settlement documents for a particular agreement. Anyway, please let me know the joining condition between the invoice and the agreement number.

[former_member183879]

Hi,

As a last resort, you can do the following.

You have to make a custom report (not just a query)

1. Have a selection screen having the rebate agreement number and the Validity period. On execution

1. Find out all invoice documents created for the rebate recipient = payer, rebate material (if it is material rebate) = billed item (This is material for which rebate accrued not the material used for settlement, though both can be same at times) created within the validity period.

For all these invoices, find out the value of the rebate condition type and its value. This value has to be totalled and considered as the total value accrued for the agreement. The invoice numbers are the invoices which accrued to the agreement.

You have to use the tables KONA, and the KOTE series of tables VBRK, VBRP, KONV etc.

You should also look on the performance side of the report also. May be it will be time consuming.

[Former Member]

Hi,

Thanks for your help, but my requirement is to generate the report which gives details of all the billing documents which have items on which rebate is accrued. selection criteria includes time period, payer, sales org etc. material is not among the selection criteris though it is an output field.

[Former Member]

Hi Vimal

Try this, maybe it can help

Pass the rebate value through KONP table to get KNUMH (Condition record number) and KSCHL (Condition Type) and KONA for valid from and valid to and rebate recipient etc..

Pass both these values or multiples of these values through KONV table and fetch the Condition record number KNUMV.

Now pass KNUMV values through VBRK and fetch the VBELN. Pass the VBELN through VBRP to get the materials, invoiced quantity etc etc. A simple query would not suffice. You need to build a report for this.

Thanks

Indranil

[Former Member]

Thanks for your wonderful analysis. Now I know that KONA will not be used but still not sure how to fetch the desired rebate details in billing documents. how can we connect S060 with VBRK/VBRP? Also can you please guide how a particular record can be fetched from condition table? Probably tis information may lead me to base query in the right way.

Moreover the requirement is such that rebate accrued should be displayed for each item in billing document and also the concerned rebate agreement number & validity dates.

Edited by: Vimal Chandra on Oct 25, 2010 2:24 PM

[former_member183879]

Hi Vimal,

Unfortunately, it is not possible to find out the list of invoices which have accrued to a rebate agreement. This is true not just for rebate for also for credit management as an example. If you want the list of sales orders which have resulted in the open order value in FD 32 for the credit customer, it is not possible.

This is true for every other functionality which uses the Infostructure update instead of normal table update. It is because infostructure update is always cumulative and table update is always record specific. When infostructure update gets a new value, the old value is erased. Hence it is not possible to find out the entire list of entries which contributed to the total value and we can only see the total value.

To sum it up, it is not possible to find out all the invoices which have resulted in the accrual value of a particular rebate agreement. Also it is not possible to find out all the sales orders, which resulted in the open order value of a credit customer in his credit master data in FD32. If this has to be done, it has to be done manually by using a complex logic.

If you want to find out the invoices relevant for rebate agreement, you need to find out all the invoices created within a timespan, find out all the partners, material etc. Then find out all agreements relevant for this period (or involving this period) involving the customer (payer/sold to party) and the material. Then match both the entries to get what you want. That is lot of work and even after that, accuracy of data is in question.

[Former Member]

Hi SNavneetha,

VBRP's sales deal field is automatically joined with Agreement number field in KONA table when I have inserted KONA table .

So, it should pick rebate agreement no. corresponding to billing document item. I am not clear , why billing documents for which rebate has been accrued are not getting displayed, once I have joined KONA and appended 3 fields (Agreement number, Validity from , Validity to). whereas , if I again revert the changes, i.e. delete the table KONA from join conditions, those documents are displayed again.

[former_member183879]

Hi Vimal,

The joint using VBRP-KNUMA-AG is incorrect. This field is not the one related to the rebate agreements, but it is related to sales promotions.

There is another field called VBRK-KNUMA which actually stores the rebate agreement number of all the credit memos, which were created as settlements for the respective rebate agreements.

There is no relationship between the billing documents and the rebate agreements. At times, even retrospective rebate agreements also happen. During any invoice creation with the rebate agreements, the value of the rebate condition type in the invoice will get updated only in S060. This is the only link. Otherwise there is no link between the invoice and the agreement under which this invoice falls in. Finally any update needs to be seen only in S060 and not in VBRK or in KONA.

KONA will only give agreement related details, VBRK will give only invoice related details. The link between KONA and VBRK is established only when a settlement is created for an agreement, not in any other situation.

Considering these facts, you should base your query. I dont know you can make S060 an infostructure in a query. Also S060 will give only cumulative values and not the value of individual invoices.

Hope this information helps you