Skip to main content

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.

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

 


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]-(iARBalancesSharedCredit.CurrentBal]+nARBalancesSharedCredit.UnreleasedBal]+eARBalancesSharedCredit.TotalOpenOrders]+rARBalancesSharedCredit.TotalShipped]-iARBalancesSharedCredit.TotalPrepayments]) 


​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.

 


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.


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


Reply