How to exclude Prepayment Type in Customer Statement (AR641500) ?

Hi @dilys 

I have add one condition and able to exclude the prepayments.

Before condition and after conditon

 

Seems to be parenthesis is missing in the last condition?

Thanks @manikantad18 , it’s work ;)

@manikantad18 and @dilys ,

I have the same issue that @dilys was having, and in addition to filtering out Prepayments I also want to filter out Voided Payments.  I see from the above Prepayments are ‘PPM’ and I’ve queried the table and the Voided payments are ‘RPM’.

My setup for the statement report is the same as @manikantad18 has displayed and the Prepayments are still not being excluded from the report. 

Here is a screen shot of how I have set it up:

I initially had a parameter set up exactly as @dilys, but removed it once I saw the solution @manikantad18 offered.

Any thoughts on why my report isn’t filtering with this same solution? Below is a screen shot of the Table Relationships for the ARStatementDetailInfo table in the Build Schema. Perhaps something there is amiss?

One final thing - here is the code for that field on the report:

Any help would be appreciated.

Patrick

Eureka! The last bit I posted was the thing that was preventing the earlier code form working. On the report, the field was using an IF statement. I commented it out and simply went with: 

=Report.ExtToUI('ARStatementDetailInfo.PrintDocType', [ARStatementDetailInfo.PrintDocType])

Voila! It is printing correctly now and excluding the Prepayments and Voided Payments in the report.

Awesome! Thank you for sharing your solution with the community @patrickACA !

@manikantad18 @dilys @patrickACA 

I am looking at this as I have the same need. I was able to remove the prepayments from the detail lines but cannot figure out how to get the again buckets to only reflect what is printing. This is a majorly complicated form, well over my head! 

Have any of you been able to address this? @manikantad18 the documents you attached above have the detail right but the aging is still reflecting the previous balance.

Any help would be tremendously appreciated. Thank you!

I am having this same issue - there is no variable that contains only prepayment amounts, and the totals along the bottom are populated by AgeBalance00, AgeBalance01, etc fields on the DAC. There’s no corresponding prepaymentn DAC field.

Currently I’m trying to extend the DAC and calculate the prepayments by attaching to the processing function for the customer statement screen. I’ll try to update here how that goes.

Has anyone been able to get the age buckets to exclude prepayments from the totals? We are stuck at the same spot.

@BDanhardt70 - Do I know you? lol  Any luck on getting the aging buckets to exclude the prepayments and payments? 

@hayleehicks - Any luck on getting the buckets to exclude prepayment and payments? 

Hi all,

I stumbled through this thread as a client just asked me for the same (exclude prepayments from statements).  As I was reading through the thread and reviewing the DACs used for the statement, it occurred to me that while statements does leverage data from the ARRegister table (like the aging reports), the ARRegister table is not used in the report.  Statements are generated by a calculation process that effectively takes a snapshot of the open documents as of the statement date, then writes the results to the ARStatementDetails table.  

I think that in order to exclude the “Prepayments” from the buckets, I think you would need to change the formulas for the buckets (notice that the report “Sums” to documents which are placed in AgeBalance00, 01, 02, etc…

maybe something like this might work…

=Sum(iif([ARStatementDetailsInfo.DocType] = ‘PPD’, 0, [ARStatementDetailsInfo.AgeBalance00])

You might give this a try (as I may as well).  Very curious if this doesn’t solve the problem.  

I did try using an if/then statement embedded within the Sum as noted in my post above.  Unfortunately, this did not work.  It seems that we have to find a way to cast the prepayments as a 0.00, but I think this can only happen during the preparation process.  I’ve not found a way to make this work yet. 

​@andrewballard - I know you posted this some time back, but I’m wondering if you were able to come-up with a solution for this.  I tried using a formula to cast the PPM docs as “0”, and sum the rest, but that’s failed.  I suppose there may be a way to cast this value.  

I ran a GI on the ARStatementDetailInfo table, and was surprised that all values in AgeBucket columns were 0.00.  Have you figured out how calculate these correctly?

It appears the change has to be made in the prepare statements and regenerate statements.  The program creates a detail record with a blank doc type for the aging buckets totals only and not for each document. Because there is no bucket value for the prepayments you can’t calculate the amounts to deduct from the summary line. 

Hey Dennis, 

Totally agree, and this was my primary conclusion.  I think this is a see/share ideas topic. 🙂

I modified the report to do my own aging and excluded the prepayments.  When you use the Print Statements process (AR503000) with the Action Print Statements, the custom aging totals are correct - or print a statement from anywhere else - but when you select the Action Email Statements, the custom aging totals are always zero.  What could be the the difference in generating the report for printing vs. generating the report for emailing?  Acumatica support will not assist because the report has been customized.