You just made my eyes roll back into my head.

I have played around in the Report Designer a bit, mainly for format changes and very easy additions/subtractions. I will have a look at the above and try to replicate. 

Thanks for the steps! 

Sorry if I made your brain explode with all this information 🧠🧠🧠. It’s more complex than format changes but should still be reasonably simple for someone experienced with the report designer. The hard part is figuring out where to get the information, it should be all there in my post. If I had a bit more free time I would have made the changes and posted the report here, maybe another day :)

I’ll be doing a live stream tomorrow at 11:00 AM EST on YouTube to build this in real-time. It will divided in two parts; first, we will look at the table structure and adjust the relations and parameters in the report, and then I’ll explain how you can do the same thing with a Generic Inquiry and bring this to Excel for analysis with Velixo and Acumatica.

Link: 

@byates I don’t know if you managed to join the YouTube live stream or not, but I hit a snag! I’m able to calculate the billed qty. and amount as of a specific date, but unless you check “Show All”, the row disappears from the report once it’s fully billed, regardless of the selected financial period.

Ideally Acumatica would provide some indication in the database of when a PO Receipt was fully billed, but since it is missing, we’ll have to create an aggregate query to calculate this. Unfortunately, it can’t be done directly in the report designer and will require creating a special type of Data Access Class called a “projection” that we will be able to link to to determine whether a PO Receipt is fully billed as of a particular date.

I’ve attached the work in progress to this message.

Here’s a slightly better version, that adds calculation of PPV Amount and handles debit adjustments (reversals of bills) correctly. Note that the issue identified during the YouTube video still remains -- the Show All checkbox is not operational and you see every single receipt ever made. 

@Gabriel Michaud 

Thanks very much for the live stream! I am a little less than half way through. Like you, my Accrual Summary is not tying to the GL summary, so I have taken a bit of a pause to figure that out. Once I get that sorted I will move forward. Very informative video thus far. Many thanks for your efforts here!  

@Gabriel Michaud 

I am seeing some confusing results in the Accrual Report where one receipt has varying results depending on the date selected in the parameters. It appears that the report ran for 01-2021 period is somehow “duplicating” the values. In the example below all numbers are increased by a factor of 14. Seeing similar results on most data but strangely not all. Thoughts?

For example:

Are you using the latest version I posted here? Do you have multiple AP Bills for this line, or anything special that could explain this?

I am using the latest version you posted (thank you again). There are multiple AP bills for these receipts and when looking into that appears to be exactly what is the cause is as there are 14 bills applied against this receipt.

I suspect I missed something in the join condition… will send you a PM

I found the issue -- it happens when you have multiple items on the receipt, with multiple bills each. I had to change the way the subtotaling/totaling is handled. New version is attached to this message.

I found one more issue while comparing the results in the report with the GL in the demo data -- I was using LineAmt instead of TranAmt, which didn't account for line-level discounts. Updated version attached.

Also, the reason why the report doesn’t balance with GL is the demo database is that there are manual IN Receipts done with the INRECEIPT reason code, which is incorrectly configured to post to the accrual account.

An Acumatica user contacted me today with a very similar question and I decided to finish the Generic Inquiry version that I had started working on for the YouTube webcast. This allows people to use it with Velixo and Excel, and compare the balance with the the GL as of a specific period.  I am attaching the spreadsheet and GI for anyone interested.

Hi Gabriel, Thanks for the PO Accrual Summary report. I have imported the latest version, but we still have 2 questions about the results of the report.

1. Are the receipts and returns that have been completely billed out supposed to still show on this report? There are hundreds of lines that have Billed Cost equal to the receipt amount that are still there. This is making it tough to reconcile what still needs to be billed. 
2. The balance of this report is still showing a mismatch of the PO accrual account as of the date of the report. 

Is there anything you would suggest we should do to get this report more usable for my client? 

Thanks, Alan Cheesman

Hello,

I’ m very interested in this GI because I too need a PO Accrual AS OF a certain date (mainly for tying out my financial statements monthly). When I try to import the GI I receive these errors below. Any insight you may have would be helpful.

Acumatica Trace:

 Expand All    Collapse All

Hi Amanda, 

Could you figure out what the issue was with the GI?

Sorry for the delay, for some reason I missed the replies made to this post following my answer!

@acheesman unfortunately yes, it will show lines that have been fully billed. In Excel with Velixo you can easily filter that out, but using the Acumatica RPX report it’s not possible to filter this out with the current design (a significant rework of the report would be needed). Regarding balance mismatch -- can you please go back in time to check if it balances in prior periods, and then try to identify the discrepancy by looking at individual transactions? In my tests the report proved to be accurate, however the PO Accrual account in the sample Acumatica database is littered with unrelated transactions that were incorrectly categorized. That could be your issue

@viwembatsha79 that’s because the GI was designed with a newer version of Acumatica. What version are you on?

On a related note I posted the full Excel sample and GI for our users here: https://help.velixo.com/en/articles/5798455-po-accrual-report

Looks like this has been delivered by Acumatica in 2021 R2: