Skip to main content

Report - Debit and E-Payment Summary

Updated

Introduction

The Debit And E-Payment Summary Report is your go-to tool for understanding everything about your automatic payment runs. Think of it as a detailed health check for your club's debits and electronic payments. 

This report gives you a complete picture of a specific payment day, whether it's in the past or the future. It explains why each contract was or was not included in a debit run, shows the success or failure of payments, and provides summaries to help you spot trends.

This report is perfect for Club Managers, Administrators, and Finance Teams who need to:

  • Reconcile past payment runs and understand why certain payments failed.
  • Forecast future debit runs to see who will be charged and for how much.
  • Investigate specific member accounts and their payment history.
  • Analyze revenue from different payment plans and membership types.

Before You Start: Setting Permissions

To view this report, your employee role needs the correct permission. Here’s how to set it up:

  1. Navigate to PGM -> Clubs -> System Permissions.
  2. Select the Employee Role you want to give access to (e.g., "Club Manager").
  3. A list of all available reports and system permissions will appear. To find the report quickly:
    • Use the search bar: Type "Debit And E-Payment Summary Report".
    • Use the category filter: Select the "Reports" category to narrow down the list.
  4. Check the box next to "Debit And E-Payment Summary Report" to grant access.
  5. Click Save. The selected employee role can now generate the report.

How to Generate the Report

Ready to run the report? Follow these simple steps:

  1.  Navigate to PGM -> Reports -> All. 
  2. You'll see a list of all available reports. To find this one easily:
    • Use the search bar: Type "Debit And E-Payment Summary Report".
    • Use the category filter: Select the category the report is filed under.
  3. Once you've found the report, click on it to open the filters.

Understanding the Filters

This report has two simple filters to help you get the exact information you need.

  • Club: Select the club you want to run the report for.
    • Important: You must select a single club. This report cannot be run for "All Clubs".
    • Example: Select "Westmeadows Gym".
  • Date Until: This is the most important filter! It tells the report which day you're interested in.
    • To review a past debit run, choose the date the debit was processed.
    • To forecast a future debit run, choose a date in the future.
    • Example: To see the results of the September 9th debit run, select "09/09/2025".

Report Output

The report is divided into several tabs (worksheets), each providing a different level of detail. Here’s what you'll find in each one.

DetailedReport

This is the main tab, showing a line-by-line breakdown for every contract and payment source.

  • PaymentSourceClub: The home club of the person whose payment details are being used.
  • PaymentSourceUserId: The system ID for the payment source holder.
  • PaymentSourceUserNumber: The unique member number of the payment source holder.
  • PaymentSourceHolderFullName: The full name of the person whose bank account or credit card is being charged.
  • PaymentSourceHolderEmail: The email address of the payment source holder.
  • PaymentPlanTypeName: The type of payment plan, such as "Membership" or "Class Pack".
  • PaymentPlanCategoryName: The category the payment plan belongs to (if configured), like "Adult Memberships".
  • PaymentPlanName: The specific name of the payment plan (e.g., "Gold Membership Monthly").
  • ContractId: The unique system ID for the contract.
  • TypeOfPaymentPlan: Explains how the plan is set up to be paid. Example: If a plan requires a bank account or credit card, it will show as "DD/CC only". If it's for cash payments, it will show as "Cash only".
  • ContractClubLocation: The club where the contract is based.
  • ContractUserId: The system ID for the person the contract belongs to.
  • ContractUserNumber: The unique member number of the person the contract belongs to.
  • ContractHolderFullName: The full name of the person who holds the contract.
  • ContractStartDate: The date the contract becomes active.
  • ContractEndDate: The date the contract expires. This may be blank for open-ended contracts.
  • CurrentContractStatus: The current status of the contract in the system (e.g., "Active", "Ended").
  • CurrentDebtStage: If the member is in a formal debt collection process, this shows which stage they are in.
  • LastVisit: The date of the member's last check-in to the club.
  • PaymentSourceCreateDate: The date the member's payment details (e.g., credit card) were first added to the system.
  • PaymentSourceType: The method of payment (e.g., "CreditCard", "DirectDebit", "Cash").
  • TotalDebtOnContract: The total outstanding negative balance on the contract up to and including the selected report date.
  • RemainingDebtOnContract: The outstanding negative balance on the contract before the selected report date.
  • OwingOnContractOnDate: The amount that became due exactly on the selected report date.
  • TotalCreditOnContract: The total positive balance on the contract.
  • NextRecurringBillingDay: The next date a scheduled payment is due to be charged.
  • PaymentScheduleName: How often the payment is charged (e.g., "Monthly", "Weekly").
  • ExcludeFromEPayment: Shows if a member is manually excluded from automatic payments. Example: If a member is excluded from e-payments and the exclusion period covers the report date, this will show "TRUE". Otherwise, it will be "FALSE".
  • LastRejection: The date of the most recent failed payment attempt for this payment source.
  • LastProcessingStatus: The status of the last payment attempt (e.g., "Ok", "Error", "Declined").
  • LastRejectionMessage: The error message from the previous failed payment (e.g., "Insufficient Funds").
  • LastConsecutiveRejections: A list of the most recent, uninterrupted chain of failed payments.
  • CountOfConsecutiveRejections: The total number of consecutive failed payments.
  • DirectDebitGroupId: A system ID for grouping debits, if used.
  • Comment: This is the most important column! It gives a plain-language explanation of what happened with the contract for the selected date.
    • "Included in past debit": This means a payment was attempted on the selected date.
    • "Not included in past debit - Contract frozen": The contract was frozen on the selected date, so no payment was attempted.
    • "Not included in past debit - Contract not in debt or credit as it is balanced": The member's balance was zero, so no payment was needed.
    • "Not included in future debit as there are no future recurring payments scheduled": For a future date, this means the contract has no more payments scheduled.
    • "To be included in future debit on [Date] if conditions remain": For a future date, this shows when the next payment is expected to be processed.
  • EpaymentProvider: The name of the payment processing company (e.g., "Stripe", "Ezypay").
  • ProcessingStatus: The final status of the payment sent on the report date ("Ok", "InProgress", "Error", "Declined").
  • ProviderTxId: The unique transaction ID from the payment provider.
  • PaymentProviderRejectionMessage: The specific error message from the payment provider for a failed payment.
  • AmountSent: The total amount that was sent to the payment provider for processing.
  • TotalSuccessful: The portion of the AmountSent that was successfully charged.
  • TotalInProgress: The portion of the AmountSent that is still being processed.
  • TotalRejected: The portion of the AmountSent that failed.
  • Total...Sent / Successful / InProgress / Rejected (by type): These columns (e.g., TotalUserTransactionSent, TotalPosTransactionSent) break down the payment amounts by the type of item being paid for (e.g., membership fees, point-of-sale items).
  • RejectionDate: The date a payment was confirmed as rejected.
  • RejectionCommunicationType: The type of automated message (e.g., email, SMS) sent to the member about the failed payment.
  • RejectionDeliveryStatus: Shows if the rejection message was successfully sent.

Summary Tabs

These tabs appear if an e-payment was processed on the date you selected. They are great for high-level analysis.

DebitSummary

(This tab only appears if you run the report for a future date) A simple summary of total expected debits and credits, grouped by club and payment type.

  • PaymentSourceClub: The name of the club.
  • PaymentSourceType: The payment method ("DirectDebit" or "CreditCard").
  • TotalDebtOnContract / RemainingDebtOnContract / OwingOnContractOnDate / TotalCreditOnContract: Sums of the corresponding columns from the DetailedReport tab.

EPaymentSummary

This provides a breakdown of the payment run totals by provider and club location.

  • EpaymentProvider: The payment processing company.
  • PaymentSourceClub: The club where the payment source holder is a member.
  • ContractClubLocation: The club where the contract is located.
  • AmountSuccessfulFromPaymentSourceClub: Total money successfully collected from members of a specific club.
  • TotalGenerated / Successful / InProgress / Rejected: Sums of all money processed, broken down by status.
  • Total...Percentage: The percentage of the total generated amount that was successful, is in progress, or was rejected.

EPaymentRejections

This tab groups all failed payments by the reason they were rejected, helping you identify common issues.

  • EpaymentProvider: The payment processing company.
  • PaymentProviderRejectionMessage: The error message (e.g., "Insufficient Funds", "Card Expired").
  • TransactionsRejected: The number of payments that failed with this specific message.
  • TotalRejected: The total dollar amount of payments that failed with this message.

RejectionReport

A simplified list of all members who had a payment rejected on the selected date. It's perfect for follow-ups.

  • PaymentSource... columns: Details of the person whose payment failed.
  • TotalRejected: The amount that failed.
  • LastConsecutiveRejections / CountOfConsecutiveRejections: Information on how many times payments have failed in a row.
  • RejectionCommunication... columns: Details on the automated notification sent to the member.

EpaymentByPlanType / EpaymentByPlanName

These tabs break down the payment run totals by the Type of payment plan and the specific Name of the payment plan, allowing you to see which memberships generate the most revenue.

CommentSummary

This powerful summary groups all contracts by the Comment from the DetailedReport. It gives you a quick overview of your entire debit run, such as how many contracts were successfully processed, how many were frozen, and how many were balanced.

PaymentsInProgress

(This tab only appears if there are payments still being processed) This shows a list of all payments that have not yet been confirmed as successful or failed.

DebtCollectionSummary

(This tab only appears if members are in a debt collection process) This summarizes the number of contracts and the total debt for each stage of your debt collection process.

Comments

Of course. The Comment column is the most powerful part of the report, as it provides a plain-language explanation for why each contract was or was not included in a payment run. Its logic is determined by a series of checks performed in a specific order.

Here is a breakdown of every rule that generates the comment.

High-Priority Overrides

These conditions are checked first and will override all other logic if they are met.

  • Included in past debit - New payment source is required to resume payments
    • What it means: A payment was attempted, but the payment provider (e.g., your bank's processor) responded that the member's payment details are no longer active (e.g., a cancelled Direct Debit authority).
    • Why it appears: The SQL checks if the provider's rejection message contains the phrase "payer inactive".
    • Action: The member must provide new, valid payment details to resume automatic payments.
  • Not included in past or future debit - Corporate invoicing contract
    • What it means: This contract is not part of an automatic debit run because it's a corporate account that is billed via invoice.
    • Why it appears: The contract's payment source is set to "Company".
Checks for Non-Recurring Payment Sources

If the contract's payment source is Cash or another non-electronic type, the following comments apply.

  • Not included in past or future debit - Payment source incorrect on contract
    • What it means: There is a setup mismatch. The member's payment plan is configured to require a Direct Debit or Credit Card, but their assigned payment method is Cash. The system cannot attempt a payment.
    • Why it appears: The PaymentSourceType is not 'CreditCard' or 'DirectDebit', but the TypeOfPaymentPlan is 'DD/CC only'.
  • Not included in past or future debit - Upfront contract
    • What it means: The contract was paid in full at the start and has no recurring payments, so it is not included in debit runs. If the contract happens to have a negative balance, the comment will have "- Currently in debt" added to the end.
    • Why it appears: The PaymentSourceType is 'Cash' and the payment plan is marked as an upfront payment.
  • Not included in past or future debit - [Contract Type] contract
    • What it means: The contract is for a free item, like a 'Free Trial' or 'Free Pass', and does not involve payments.
    • Why it appears: The PaymentSourceType is 'Cash' and the PaymentPlanType is 'FreeTrial', 'FreePass', or 'Agreement'.
  • Not included in past or future debit - Cash payment source on contract
    • What it means: This is a general comment for any other contract set to be paid with Cash. It is correctly excluded from electronic payment runs.
    • Why it appears: The PaymentSourceType is 'Cash' and none of the more specific conditions above were met.
If a Payment Was Sent (AmountSent is not null)

This section applies when reviewing a past payment run where the system attempted to charge the member.

  • Included in past debit - Original transaction rolled back...
    • What it means: A payment was sent, but a staff member later cancelled the charge that triggered it (e.g., reversed a membership fee). This is a critical flag, as the member may now be in credit if the payment was successful.
    • Why it appears: A flag in the database (UserTransactionRollBack, UserPosTxRollBack, or InvoiceItemRollback) is set to true.
  • Included in past debit - [User/Class] cancelled class...
    • What it means: A payment was attempted for a class booking, but the class was subsequently cancelled (either by the member or the club). This explains why a payment was processed for something that is no longer occurring.
    • Why it appears: The system detects that a class associated with the payment was cancelled.
  • Included in past debit - Contract freeze created or modified after debit sent
    • What it means: This is a timing issue. The payment was correctly included in the debit run, but a staff member froze the contract after the payment was already sent to the provider.
    • Why it appears: The contract's freeze modification date is later than the date of the payment run.
  • Included in past debit - Contract frozen at time of processing however debt sent
    • What it means: Although the contract was frozen, a payment was still attempted. This is usually the correct behaviour when a frozen contract has a pre-existing outstanding debt.
    • Why it appears: The contract was frozen on the report date, but a payment was still sent.
  • Included in past debit - In Progress
    • What it means: The payment was sent to the provider, but the final status (Success or Failure) has not been confirmed yet.
    • Why it appears: The TotalInProgress amount for the transaction is greater than zero.
  • Included in past debit - Recurring e-payment rejection message...
    • What it means: The payment failed with the exact same error message as the previous failed attempt, indicating a persistent issue (e.g., a card that is consistently over its limit).
    • Why it appears: The LastRejectionMessage matches the current PaymentProviderRejectionMessage.
  • Included in past debit - Recurring e-payment rejection message
    • What it means: A general comment indicating the payment attempt failed.
    • Why it appears: The TotalRejected amount is greater than zero.
  • Included in past debit - Contract discount applied - [Discount Name]
    • What it means: The payment was processed, and this comment provides extra context that a specific discount was applied to the transaction.
    • Why it appears: The system detects that a named discount was used on the charge.
  • Included in past debit
    • What it means: A simple confirmation that the contract was included in the payment run and none of the more specific scenarios above applied.
    • Why it appears: The AmountSent column has a value, and it's the default comment for a successful or processed payment.
If a Payment Was NOT Sent (AmountSent is null)

This section explains why a member was skipped. The logic differs depending on whether you are looking at a past or future date.

Explanations for a Past Date

(When your selected report date is today or in the past)

  • Not included in past debit as the contract has ended...
    • What it means: The contract was already expired on the payment date and was correctly excluded based on your system's settings for handling ended contracts.
    • Why it appears: The contract's Status is 'Ended' and system settings (@includeEndedContracts, @includeEndedContractsIfCurrentActive) confirm it should be excluded.
  • Not Included in past debit - Contract not started
    • What it means: The contract's start date was after the payment run date, so it was correctly excluded.
    • Why it appears: The ContractStartDate is after the selected report date.
  • Not included in past debit - Member excluded from e-payment
    • What it means: The member was intentionally excluded from automatic payments by a staff member.
    • Why it appears: The ExcludeFromEPayment flag is set to 'TRUE'.
  • Not included in past debit - Contract frozen
    • What it means: The contract was frozen on the date of the payment run and was therefore correctly excluded. If the contract has an outstanding balance, the comment will note "but is currently in debt".
    • Why it appears: The contract is detected as being within a freeze period.
  • Not included in past debit - Contract in credit
    • What it means: The member had a positive balance on their account, so no payment was necessary.
    • Why it appears: The TotalCreditOnContract is greater than zero.
  • Not included in past debit - Contract not in debt or credit as it is balanced
    • What it means: The member's account balance was zero, so no payment was needed.
    • Why it appears: The TotalDebtOnContract is zero.
Forecasts for a Future Date

(When your selected report date is in the future)

  • To be included in future debit on [Date] if conditions remain
    • What it means: This is the standard forecast. It indicates that, based on current information, the contract is scheduled for payment on its next billing day.
    • Why it appears: The contract has a NextRecurringBillingDay and no other exclusion rules apply.
  • Not included in future debit on [Date] if conditions remain - Member excluded from e-payment
    • What it means: Forecasts that the contract will be skipped because the member is manually excluded from automatic payments.
    • Why it appears: The ExcludeFromEPayment flag is set to 'TRUE'.
  • To be included in future debit on or after [Date] - Contract frozen
    • What it means: The contract is currently frozen, and this comment forecasts that payments will resume after the freeze ends on the specified date.
    • Why it appears: The contract has a future ContractFreezeEndDate.
  • Not included in future debit as there are no future recurring payments scheduled
    • What it means: The contract (e.g., a 12-month fixed-term membership) has completed its payment schedule and has no more recurring fees due.
    • Why it appears: The NextRecurringBillingDay is null or invalid.
Final Catch-All

Not included in debit - Other/Unknown

  • What it means: This is a fallback message. It appears if a contract was not included in the debit run for a rare reason that doesn't match any of the specific rules above.
  • Why it appears: It is the final ELSE condition in the statement.

Hints & Tips

  • Future vs. Past: The report's Comment column behaves differently depending on the date you select.
    • Past Date: It tells you what happened.
    • Future Date: It tells you what is expected to happen. This is great for fixing issues (like an expired credit card) before the debit run.
  • Duplicate Contracts: You might occasionally see the same ContractId listed twice. This usually happens if a member changes their payment source (e.g., from an old credit card to a new one) and a payment was attempted using the old source on the report date.
  • Start with the CommentSummary: If you're reviewing a large debit run, start with the CommentSummary tab. It’s the fastest way to see a high-level overview and identify any widespread issues.
  • "Included in past debit - Original transaction rolled back...": This comment is important. It means a payment was sent, but an administrator later cancelled the original charge (e.g., a membership fee). This could result in the member having a credit on their account.

Related articles