Solved

Historical Purchase Accrual Summary


Userlevel 4
Badge

Is there a way to edit the out of the box Purchase Accrual Summary to have a parameter added so it can be run up to a historical date. For example if I wanted to know what the Accrual balance by vendor as of 3/31/2021 can that be added?

icon

Best answer by Gabriel Michaud 23 April 2021, 22:28

View original

19 replies

Userlevel 7
Badge +10

Hi @byates,

I had a look at the report and it relies on the POAccrualStatus table, which itself is not dated or period-sensitive; it only contains the total that has been billed for a particular purchase receipt.

How familiar are you with the report designer? Here are the steps that should be taken to make this work:

  1. Add a Date (or Financial Period) parameter
  2. Add a LEFT JOIN to the APTran table, linking POAccrualStatus to APTran by using the POAccrualType, POAccrualRefNoteID and POAccrualLineNbr fields. In your join condition, you need to have a condition that checks that the APTran.TranDate is < =the new parameter you added in step 1
  3. Replace the Billed Qty. and Billed Cost fields with a SUM of the values in APTran.TranAmt and APTran.Qty fields.
  4. You will also want to filter out POAccrualStatus lines for receipts that have been received after your cutoff date. Add an INNER JOIN join between POAccrualStatus and POReceiptLine - ReceiptType, ReceiptNbr are the two fields you need to join on. In the case of POReceiptLine, you can place this filter in the join itself or in the conditions tab, the result will be the same
Userlevel 4
Badge

You just made my eyes roll back into my head.:drooling_face:

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! 

Userlevel 7
Badge +10

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 :)

Userlevel 7
Badge +10

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: 

 

Userlevel 7
Badge +10

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

Userlevel 7
Badge +10

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. 

Userlevel 4
Badge

@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!  

Userlevel 4
Badge

@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:

 

Userlevel 7
Badge +10

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?

Userlevel 4
Badge

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.

Userlevel 7
Badge +10

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

Userlevel 7
Badge +10

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.

 

 

 

Userlevel 7
Badge +10

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.

Userlevel 7
Badge +10

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.

 

 

Userlevel 1

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

Userlevel 1

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

Error: An unhandled exception has occurred in the function 'MoveNext'. Please see the trace log for more details.  Send
  Raised At: 7/15/2021 10:57:00 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: An unhandled exception has occurred in the function 'MoveNext'. Please see the trace log for more details.  Send
  Raised At: 7/15/2021 10:56:24 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:47:51 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:46:44 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:46:09 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:45:41 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml

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

Error: An unhandled exception has occurred in the function 'MoveNext'. Please see the trace log for more details.  Send
  Raised At: 7/15/2021 10:57:00 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: An unhandled exception has occurred in the function 'MoveNext'. Please see the trace log for more details.  Send
  Raised At: 7/15/2021 10:56:24 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:47:51 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:46:44 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:46:09 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml  
 Details:
Error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.  Send
  Raised At: 7/15/2021 10:45:41 AM Screen: SM.20.80.00 Command: CopyPaste@ImportXml

Hi Amanda, 

 

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

Userlevel 7
Badge +10

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

Userlevel 7
Badge +10

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

 

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