Skip to main content
Solved

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

  • 10 May 2024
  • 5 replies
  • 60 views

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.

5 replies

Manikanta Dhulipudi
Rank
Userlevel 7
Badge +12

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

 

Acumatica Functional Consultant /vrsn2709@gmail.com /
ejmillar
Rank
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]) 

1 Attachment
Ewan ~ Rubech IT ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
M
Rank
Userlevel 4
Badge +1

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

 

1 Attachment
ejmillar
Rank
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.

1 Attachment
Ewan ~ Rubech IT ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
M
Rank
Userlevel 4
Badge +1

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

Reply


Terms & ConditionsCookie settings