Attention: Confluence is not suitable for the storage of highly confidential data. Please ensure that any data classified as Highly Protected is stored using a more secure platform.
If you have any questions, please refer to the University's data classification guide or contact ict.askcyber@sydney.edu.au
GA Consolidated Receipts EOFY Process
- rommel.ngo
Consolidated Receipts Record Generation
Run Apsona MSR “Recurring Gift Check Transaction”
Change Report filter, Transaction date for the FY that has ended
Export the report to excel and do a pivot. We need the number of transactions and the sum of transactions per month
Row Labels | Sum of Transaction.Amount | Count of Transaction.Amount2 |
2022 |
|
|
Jul | 11,542.53 | 265.00 |
Aug | 10,727.53 | 255.00 |
Sep | 7,789.06 | 257.00 |
Oct | 10,948.03 | 256.00 |
Nov | 10,278.03 | 244.00 |
Dec | 10,328.03 | 243.00 |
2023 |
|
|
Jan | 10,331.03 | 235.00 |
Feb | 10,141.03 | 228.00 |
Mar | 10,231.03 | 230.00 |
Apr | 10,266.03 | 225.00 |
May | 9,941.03 | 220.00 |
Jun | 10,036.03 | 221.00 |
Grand Total | 122,559.39 | 2,879.00 |
Run previews in Receipting utility for each month and enter totals on spreadsheet above and analyze discrepancies:
According to AQ this is how they would derive the figures
#receipts to void
selects the transactions based on the criteria from form
Uses the gifts for those tranactions to select all of the gifts transactions
If any of those transctions are marked as Voided (using isVoided flag on transaction)
Looks at those voided transaction if they are linked to a receipt and that receipt is not marked as voided, then the receipt is counted in # of Receipts to Void
# transactions receipted
Selects the transactions based on the criteria from form
Uses the gifts for those transactions to select all of the gifts transactions
If gift for the transaction is marked as No Receipt false and the gift date is less than or equal to today's date
If the transaction is not voided and the transaction not linked to a receipt row or linked receipt is already voided AND it is running on a selected gift number or a Batch Number (not annual receipts) OR running annual receipts and gift is marked for annual receipt and if running for approval and gift is marked as receipt approved.
Then quids are checked to validate if the quid amount is covered (see explanation below) and then there must be a signator or a valid contact to receive the receipt.)
# new receipts
Selects the transactions based on the criteria from form
Uses the gifts for those transactions to select all of the gifts transactions
If gift for the transaction is marked as No Receipt false and the gift date is less than or equal to today's date
If the transaction is not voided and the transaction not linked to a receipt row or linked receipt is already voided AND it is running on a selected gift number or a Batch Number (not annual receipts) OR running annual receipts and gift is marked for annual receipt and if running for approval and gift is marked as receipt approved.
Then quids are checked to validate if the quid amount is covered (see explanation below) and then there must be a signator or a valid contact to receive the receipt.)
Count is based on the Signator/Contact to receipt, so if 1 Signator has 3 receipts it is only counted as 1.
as per https://sydneyuni.atlassian.net/browse/TAPSS-343 There are two issues that AQ has confirmed
Transactions are being fetched by their Transaction creation date time in UTC
This means that if a transaction is created before 10 am, that gets counted into previous day# of Voided receipt needs fixing and they think it is related to the previous issue where transaction are being fetched based on creation date in UTC time.
Run Receipting utility by month and Create Receipts and compare Email vs Receipting preview:
Note that in UAT, email deliverability is turned off so email confirmation will not be received. In production, ensure that each month has been run completely before running another month to avoid record locking issues. To be sure wait until email confirmation is received before running another.
How to capture receipts that will be voided as per AQ’s instruction
selects the transactions based on the criteria from form
select id,AQB__Gift__c from AQB__Transaction__c where DAY_ONLY(convertTimezone(CreatedDate)) >= 2022-07-01 and DAY_ONLY(convertTimezone(CreatedDate)) <= 2023-06-30export in excel and get unique Gift IDs and use Apsona to report on transactions with these Gift IDs
Uses the gifts for those tranactions to select all of the gifts transactions
If any of those transctions are marked as Voided (using isVoided flag on transaction)
Looks at those voided transaction if they are linked to a receipt and that receipt is not marked as voided, then the receipt is counted in # of Receipts to Void
in the example above for 2021-2023, there will 177 transactions who belong to 67 Receipts to be voided. But as mentioned, this may not tally with AQ’s numbers because of an issue they are already aware and should be in future release. However, using this method/approach, it should be relatively close.
Apsona Receipt proofing / printing / emailing
Main reports:
Non-Email Consolidated Receipts
Sub list report:
Transaction Receipt Table, update filter
Notes:
Annual update needed for running the report:
1- Date filters on the above reports need to be adjusted for the current financial year
2- Template text needs to be updated USYD_Consolidated Receipt Template_Updated.docx for the current financial year.
3- The calculated field that produces the Receipt title for emails needs to be updated to reflect the current financial year.
Duplicates
Apsona report generates duplicates because of duplicate Gift recognition records in Jarvis. These records need to be manually excluded before Merge/Mail. To do so, sort the account id column and exclude the Empty rows.
Generating the file for GA team to review before sending the emails.
Email Consolidated Receipts - No email for review only action is created for generating all the consolidated receipts in one document for the GA team to review. This action does not send any email or attach the document to any object
Explanation of the confusing fields on Jarvis
“Gift/Pledge.Annual Receipt” check box must be ticked for consolidated receipts.
“Recurring Gift.Annual Receipt” Flag must be unchecked for consolidated receipt
“Recurring Gift.Send annual Receipt” (Consolidated) must be checked for consolidated receipt
Non-Email Consolidated Receipts - report updates
Step Filter |
|
|---|---|
Account | |
Gifts / Pledges | |
Gift Recognition Credit | |
Transaction | |
Calculated Step |
Run the mail merge in test mode.
Once the receipts are generated in test mode, compare each recept to the masterfile.
Check for:
The date range is for the correct financial year
The payments are in date order
Donors with 2 pledges should have 2 receipts
Mulit designation pledges should have 1 receipt with both designations
Donors from a household should only be addressed to the legal credit
Joint donors should only have one joint receipt
Soft credit should not be visible
Duplicate receipts should be deleted and GRC checked
Address is populated
Ammend the wording in the email to donors to ensure it’s correct financial year – This needs to be done by the DRS team.
Send a test email to yourself to see the output
Send the email consolidated receipts (may need to manually check for duplicates again before they are sent).
Mail merge settings:
The gifts inbox is CC’d in all the email receipts so you can check the output matches the mail merge.
Create new folders each year and move the emails into the receipts or receipts undeliverable folders:
Try to email undeliverable receipts again manually, if it does not work then mark email as invalid and mail the receipt in the post.
Print and post the non-email consolidated receipts.
Update Print and Sent date on all receipt objects – DRS team.
Reference:
