Net Institutional Fee RevenueRevenue & Fees

Task prompt

Can you take the institutional-fee line for the invoicing close? I need an all-time tie-out from the fee-adjustment ledger and a refund-control check that Bank Operations can follow up. Cover domestic and international out-of-network ATM fees, wires, foreign transactions, Finoco+ membership, replacement cards, and check issuance. Net completed returns and credits against the related fee lines; leave out interest, charge-offs, promotions, referrals, taxes, ACH-funding adjustments, and anything unrelated. Treat a successfully processed adjustment as the posting record—do not filter it again because a linked transfer has a different operational status—and use processing completion as the posting date. Please read the Confluence pages "Fees" and "[ENG] Out-of-network ATM Fees". Our Jira history contains two ENG issues that give opposite meanings for `realized_amount_in_cents`; find and read both, report which key calls it charged/actually debited and which calls it wanted/nominal, and settle the accounting treatment from the warehouse's partial-collection behavior rather than the field name. For the tie-out, give me gross collected fees, credits/refunds, net revenue, and net revenue for each of the six families. Combine domestic and international OON ATM activity. Also give me the processed OON amount we could not collect because only part of the scheduled fee posted. Show the refund-rate leader by dollars and the leader by event count, with both percentages rounded to two decimals. Finally, give me the peak posting month, that month's net revenue, and its largest fee-family contributor. Use YYYY-MM for the month and these family labels: oon_atm, wire, foreign_transaction, premium_membership, card_replacement, check_issuance. Break ties by the earliest month, then the alphabetical family label. For the refund control, resolve the customer from the adjustment direction. Within each customer-account and fee-family pair, order the processed adjustments by processing completion and then adjustment ID, keep a running balance with charges positive and refunds negative, and record the positive magnitude of the most negative point. Report how many account-family pairs ever go below zero, the sum of those pair-level peaks, and the account, family, and amount for the largest peak. Break that last tie by the lowest account ID and then the family label. I also need the support-demand side of the close. In Freshdesk, enumerate every non-deleted, non-spam, non-archived ticket whose subject contains `fee` (case-insensitive), paging until the search is exhausted. From that complete register, report the ticket count, how many are still Open or Pending (status 2 or 3), the number of distinct non-null requester IDs, and the requester with the most tickets plus that ticket count. Break a requester tie by the lowest requester ID. For backlog aging, split the unresolved tickets into Open and Pending counts, then split the same unresolved cohort into tickets created before 2024-01-01 and tickets created on or after that date. Create `institutional-fee-close` in Slack, post a short summary with net revenue, the account-family exception count and total peak exposure, the fee-subject ticket count, separate Open and Pending counts, the pre-2024 unresolved count, and both Jira lineage keys, then pin it. File a Bank Operations Jira ticket titled `Investigate institutional fee refund control exceptions` containing the same control evidence and lineage keys. Reply with one JSON object using exactly these keys: gross_collected_fee_revenue_usd, fee_credits_refunds_usd, net_fee_revenue_usd, oon_atm_net_usd, wire_net_usd, foreign_transaction_net_usd, premium_membership_net_usd, card_replacement_net_usd, check_issuance_net_usd, oon_atm_partial_collection_shortfall_usd, highest_refund_rate_family, highest_refund_rate_pct, highest_refund_event_rate_family, highest_refund_event_rate_pct, peak_net_revenue_month, peak_month_net_fee_revenue_usd, peak_month_top_fee_family, temporary_over_refund_account_family_pair_count, total_peak_over_refund_exposure_by_family_usd, largest_peak_over_refund_by_family_account_id, largest_peak_over_refund_family, largest_peak_over_refund_by_family_usd, fee_subject_ticket_count, fee_subject_open_or_pending_count, fee_subject_distinct_requester_count, fee_subject_busiest_requester_id, fee_subject_busiest_requester_ticket_count, fee_subject_open_count, fee_subject_pending_count, fee_subject_unresolved_created_before_2024_count, fee_subject_unresolved_created_2024_or_later_count, jira_realized_as_posted_issue_key, jira_realized_as_nominal_issue_key.