Skip to main content
Skip table of contents

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:

  • Owner: 563616

  • Correct YTD Net: $1,939.56

  • Displayed: $3,197.91 (Check Detail File)

  • Variance: $1,258.35 → $251.67 (July Net) × 5 duplicate entries.

The following SQL query can be used to look for duplicates:

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

Captura de tela 2025-08-12 162655.png

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

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

image-20250804-143451 (1).png

Since the root cause analysis (RCA) has not been determined, this issue is likely to happen again.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.