Revenue Remittance: Overstated YTD Totals for a Company
Summary
In the context of the ticket WESS-16367, Revenue Year-to-Date (YTD) totals for Company 510 are overstated for the July 2025 check run. The issue affects only ACH owners and appears linked to multiple Release Revenue Payments attempts in late July, which caused duplicate records in the revenue_remittance_ytd_summaries table.
Expected Results | Actual Results |
|---|---|
2025 revenue YTD totals should exactly match the sum of all checks released in the year. | YTD totals are inflated by the July net amount repeated multiple times. Example:
|
The following SQL query can be used to look for duplicates:
select ap.voucher_number, ba.number, rs.* from revenue_remittance_ytd_summaries rs
join roles r on r.anchor_id = rs.payee_role_anchor_id
join business_associates ba on ba.id = r.business_associate_id
join company_codes cc on cc.id = rs.company_code_id
join ap_payments ap on ap.id = rs.ap_payment_id
where
cc.number = '510'
and rs.accounting_date >= '2025-06-01' --Issue started in July + compare to June
Check the file bellow: Flywheel WESS-16367.xlsx
The results show that that duplicates found match the variance provided by the customer:

This is a recurring issue, causing SMEs to delete the records from this table during multiple occasions. Ultimately, each ap_payment_id in revenue_remittance_ytd_summaries only should be present once.
RCA and Fix
Although, the root cause for the has not been found for this ticket, SMEs suspect of performance or server issues. Regardless, whenever this issue happens, we can run the attached delete script to clean up the duplicates in the summaries table.
Rank query/script (finds/deletes all duplicates in company 510 & 7/1/25 accounting):
--FLYWHEEL - WESS-16367 - Revenue YTD Cleanup Script
with rs_ranked as (
select
ap.voucher_number,
rs.*,
ROW_NUMBER() over (partition by rs.ap_payment_id order by rs.id) as row_num
from revenue_remittance_ytd_summaries rs
join company_codes cc on cc.id = rs.company_code_id
join ap_payments ap on ap.id = rs.ap_payment_id
where
cc.number = '510'
and rs.accounting_date = '2025-07-01'
)
--View duplicate records
--select row_num, *
--from rs_ranked
--where row_num > 1
--Delete duplicate records
delete from revenue_remittance_ytd_summaries
where id in (select id from rs_ranked where row_num > 1)
If possible, confirm that the ranking is accurate. For example BA# 563616 (“Original” ID 671083 will be kept, other 5 id’s/records will be deleted because row_num >1):
.png?inst-v=19f27b41-30a9-4256-a5f7-38986b01af36)
Since the root cause analysis (RCA) has not been determined, this issue is likely to happen again.