cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Query for AR Aging Report

Former Member
0 Kudos

Hello Gurus-

I am trying to build a query for Aging report, and would like to include something like this:

Customer-Reference-Payment terms-Due date-Current-31-60days-61-90days-91-120days-over120days-toatal amount.

I have an infoset which includes BSID, KNA1, KNB1. I have maintained local fields:

Due Date = Baseline date + cash discount days

Statement Date = My input date.

Current = Condition (DUEDTE >= STATDTE - 30) formula (Amount)

31 - 60 Days = Condition (DUEDTE >= STATDTE - 60 AND DUEDTE < STATDTE - 30) formula (Amount)

61 - 90 Days = similar to above

91 - 120 Days = similar to above

Amount = - 1 * (If Debit credit indicator = "H" then "Amount in local currency") otherwise "Amt in Loc.Cur"

Over 120 Days = similar to above

When I enter a statement date of today it works hunky dory...it all looks good...my current column looks good and do the rest. When i want to run this query as of couple of months ago (if today is 12/19/08 and i wanted to run my aging report for 10/31/08) it should give me aging as of my statement date 10/31/08 but it does not. It calculates the total amount as of today. I know because of local field "Amount" where i gave amount in local currency, but how would I tell that it should pick up amount in local currency till the statement date?

Any input in this regards is highly helpful.

Thanks,

RNarayan

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Ram,

It is damn similar to what we had. You should also include BSAD not just BSID. Please note that as customer invoices get paid they move from BSID to BSAD.

Here the clearing date becomes important. So include all customer invoices (open and cleared i.e. BSID and BSAD). But make some assumptions like say go back to 5 years not more for system performance.

Create a condition saying if the report run date is more than the invoice posting date but is less than the clearing date then the invoice should show as open.

Further more, there could be partially paid invoices, in which case you need to go back to the original invoice and report the original due date based on invoice reference.

Regards

Sharabh

Former Member
0 Kudos

Hi Sharabh-

Thanks for your quick reply. I dont know how to code in the logic for "Create a condition saying if the report run date is more than the invoice posting date but is less than the clearing date then the invoice should show as open." Can you explain a little further?

Thanks for your help!

RNarayan

Former Member
0 Kudos

Hi Ram,

On your aging report you want to see invoices which are still not paid (or still open) as of a certain date. This date is "key date". You need to define a variable for key date (which is normally system date if you run today) but it could be a past date.

So key date is let us say Oct 31st (X) and the system date is Dec 19th. When you run the report as of Oct 31st all invoices that are posted prior to Oct 31st (posting date less than X) and are still in BSID will show on your report. There is no issue here.

Let us say an invoice was created on Oct 25th (Y) and was paid on Nov 15th (Z). The clearing date is Z. That means if you run the aging report today this invoice will not show.

But you want to run the report on Oct 31st. This means X is greater than Y but is less than Z.

Clearing date and posting date are part of BSAD. Report run date is the user input and is a variable.

With the above concept and with the help of an ABAP programmer you should be able to solve the problem.

Regards

Sharabh

Former Member
0 Kudos

Thanks Sharabh. I will try that.

Regards,

RNarayan

Former Member
0 Kudos

Hi Sharabh-

Can this logic be achieved using SQ01 query or are we supposed to have a ABAP report?

My current report is created using SQ01 infoset query. After looking at your logic, i am a little confused if this thing can be achieved using it.

Thanks,

RNarayan.

Former Member
0 Kudos

Hi Ram,

ABAP report program will be required.

Regards

Sharabh

Former Member
0 Kudos

It is not possible through SQ01. You will need an ABAP report program.

Former Member
0 Kudos

Hi Sharabh,

My question is regarding the product costing. When we run a cost estimate for any In- house produced material, it picks up the cost of raw materials and manufacturing cost from internal activity allocation. Normally we assign price control V (Moving average price) for raw materials due to their market volatility. We also assign price control S (Standard Price) for the finished material. Now how can we standardize the cost of the finished material, when its components have moving price?

Apart from the above, we run cost estimate generally once in a year in even the largest organizations. Would it be accurate to have standard price control for finished material whole the year, when the ingrdient price keep varying? So how can we synchronize the cost in a dynamic market cnditions?

Can you please coment on this?

Regards,

Kishore

Answers (0)