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?
Best answer by Gabriel Michaud
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:
- Add a Date (or Financial Period) parameter
- 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
- Replace the Billed Qty. and Billed Cost fields with a SUM of the values in APTran.TranAmt and APTran.Qty fields.
- 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