Solved

How to pick the Current Available Credit value of a Customer to a report?

  • 10 May 2024
  • 5 replies
  • 57 views

Userlevel 4
Badge

When the report executes, I want to get the Current Available Credit value of the Customer to the Customer Statement MC report. Please check the below screenshot. 

But when inspecting the relevant DAC(CustomerBalanceSummary) shown in the above, it is Non-existent in the DB. Please check the below screenshot.

 

So can i know how to get this detail to the Customer Statement MC report? Thank you.

icon

Best answer by ejmillar 14 May 2024, 11:49

View original

5 replies

Userlevel 7
Badge +12

Hi @malinthawarnakulasooriya08  Please review the below article, USE AR Balance tables

 

Userlevel 5
Badge +1

There is a generic inquiry named AR-CreditLimitCustomers that will be of help.

The GI should be in your Acumatica instance but I’ve attached it case it’s not.

The following tables will need to bring into the report.

PX.Objects.AR.ARBalancesSharedCredit

PX.Objects.AR.CustomerSharedCredit

Relationship will be on the CustomerID.

 

Remaining Credit within the GI uses the following formula:

=[CustomerSharedCredit.CreditLimit]-([ARBalancesSharedCredit.CurrentBal]+[ARBalancesSharedCredit.UnreleasedBal]+[ARBalancesSharedCredit.TotalOpenOrders]+[ARBalancesSharedCredit.TotalShipped]-[ARBalancesSharedCredit.TotalPrepayments]) 

Userlevel 4
Badge

​Thank you @manikantad18  . I checked the above mentioned articles. In here, I'm trying to get the Current Remaining Credit Value. Thank you @ejmillar  . Your guidance helped me lot. But there is a little issue. By using the “Export as Report ” feature in acumatica, I created a new report and executed it. I used the same formula which was used in the above mentioned GI. But the values are differ from the exact values that are showing in the GI and the Customers’ Financial tab. Please check the below screenshots.

Newly Created report from the GI  by using “Export as Report” feature

  

Customer's Available Credit Value
Value in the GI

@ejmillar in here i’m attaching the rpx file of the newly created report. Any guidance regarding this would be highly appreciated. Thank you.

 

Userlevel 5
Badge +1

Hello,

I’ve amended the attached report.

I investigated the SQL produced by the GI and ARBalances table needed to be added and grouping by CustomerShareCredit.SharedCreditCustomerID and CustomerSharedCredit.CreditLimit needed to be applied.

Userlevel 4
Badge

Thank you so much @ejmillar. Sorry for the late reply. 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved