Skip to Content
author's profile photo Former Member
Former Member

Problem with business partner cumulative balance

Hi

I used to used the business partner balance enqury to quickly tell customers what they were due to pay up to the start of the month. I just looked at the cumulative balance of the last transaction of the previous month and that was it. I know I could run an aged credit report but this was quicker and easier. However since upgrade to 2007A the cumulative balance does not give the amount due for a part paid invoice so this does not work any more.

Question is has anyone writen a fast query or other report that calculates the cumulative balance based on outstanding amount?

Thanks in advance

Derek

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2009 at 04:02 PM

    Hi Derek,

    Try this one:

    SELECT     T0.CARDCODE, MIN(T0.CARDNAME) 'Customer Name', SUM(T0.DOCTOTAL - T0.PAIDTODATE) 'Last Balance'
    FROM        DBO.OINV T0
    WHERE     DOCSTATUS = 'O' AND T0.DOCTOTAL - T0.PAIDTODATE > 1 AND T0.CANCELED = 'N' AND T0.DOCDUEDATE< '[%0]' AND T0.CARDCODE = '[%1]'
    Group By T0.CARDCODE
    UNION ALL
    SELECT     T0.CARDCODE, MIN(T0.CARDNAME) 'Customer Name', -SUM(T0.DOCTOTAL - T0.PAIDTODATE) 'Last Balance'
    FROM        DBO.ORIN T0
    WHERE     DOCSTATUS = 'O' AND T0.DOCTOTAL - T0.PAIDTODATE > 1 AND T0.CANCELED = 'N' AND T0.DOCDUEDATE< '[%0]' AND T0.CARDCODE = '[%1]'
    Group By T0.CARDCODE
    ORDER BY T0.CARDCODE
    

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 23, 2009 at 09:42 AM

    Hi Gordon

    Thanks for your suggestion. I have tried on my 2007A version but get the error:

    1). [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string. 'Service Contracts' (OCTR)

    or

    1). [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string. 'Received Alerts' (OAIB)

    depends on how often I run it. Can you help please?

    thanks in advance

    Derek

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi!

      Run this in SAP B1 Query Manager

      SELECT     T0.CARDCODE, MIN(T0.CARDNAME) 'Customer Name', SUM(T0.DOCTOTAL - T0.PAIDTODATE) 'Last Balance'
      FROM        DBO.OINV T0
      WHERE     DOCSTATUS = 'O' AND T0.DOCTOTAL - T0.PAIDTODATE > 1 AND T0.CANCELED = 'N' AND DATEPART(month,T0.DOCDUEDATE) < DATEPART(month,getdate()) AND T0.CARDCODE = '[%1]'
      Group By T0.CARDCODE
      UNION ALL
      SELECT     T0.CARDCODE, MIN(T0.CARDNAME) 'Customer Name', -SUM(T0.DOCTOTAL - T0.PAIDTODATE) 'Last Balance'
      FROM        DBO.ORIN T0
      WHERE     DOCSTATUS = 'O' AND T0.DOCTOTAL - T0.PAIDTODATE > 1 AND T0.CANCELED = 'N' AND DATEPART(month,T0.DOCDUEDATE) < DATEPART(month,getdate())  AND T0.CARDCODE = '[%1]'
      Group By T0.CARDCODE
      ORDER BY T0.CARDCODE

  • author's profile photo Former Member
    Former Member
    Posted on Sep 23, 2009 at 11:40 AM

    Gordon

    Hi, that works on my setup. But,.. I need to see the actual transactions down the screen with the cumulative open balnce beside them with the date. This is so the user can just eyball the balance at last month end and tell the customer what they should pay. I any learning from your query but don't think I could do this. Can you help again please?

    cheers

    Derek

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 23, 2009 at 06:25 PM

    Thanga / Gordon

    Hi, that works on my setup. But,.. I need to see the actual transactions down the screen with the cumulative open balance beside them with the date. This is so the user can just eyeball the balance at last month end and tell the customer what they should pay. I any learning from your query but don't think I could do this. Can you help again please?

    cheers

    Derek

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Derek,

      why don't you just look at the balance due of a document? If you have an invoice over 100 & the customer has paid or you credited 20, then the balance due is shown in the BP account matrix as 80. In the ageing this is also taken into consideration.

      All the best,

      Kerstin

  • author's profile photo Former Member
    Former Member
    Posted on Oct 05, 2009 at 07:31 PM

    If you need those running totals, better using Database Query in the EXCEL througn ODBC. You could add any formula easily that way.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.