Skip to content
English
Sign in
Contact us
  • There are no suggestions because the search field is empty.

GMC EFT Module 4.23 feature and processing improvements

Overview of GMC EFT Module 4.23 feature and improvements

Summary

The aim of this article is to provide an overview of the functions performed within an Electronic Funds Transfer (EFT) run via the EFT module in GMC. It aims to show what can be expected from both an end user perspective and also some technical detail to assist potential trouble shooting.

The information contained within this overview is relevant to the deployment of GMC EFT module 4.23 onward.

  • There are five steps in performing an EFT run along with some system tasks that happen at the start every time a run is initiated regardless of which options are chosen.
    • Step 0 - This precursor process ensures that AUDDIS information is correct for bank accounts that could be used for the run.
    • Step 1 - Prepare collection data based on existing sales invoices with a specific payment method.
    • Step 2 - Subscription details
      • Prepare to expire subscriptions as necessary.
      • Prepare to roll auto renew subscriptions and prepare data for associated sales invoices to be raised.
    • Step 3 - Generate a failed collection report, which collates members during step 1 and 2, which could prevent collections being made, allowing for data to be updated as necessary.
    • Step 4 - Proceeding to update the Members database/schema with memberships renewing, sales invoices and sales receipts being raised, and marking sales invoices as paid based on the information prepared in steps 1-3.
      Finally, the process will create the BACS output file ready to be sent to the receiving bank for processing via BACS.
    • Step 5 - Completion of the process and mark the process with the appropriate result detail.
  • After steps 1-3 above have been completed, collection reports are available to determine the information prepared. There is also a configurable report available to show what data was parsed and any issues encountered.
  • After step 4 has completed there is another report available to show what was included in the output file itself.
  • Additionally:
    • There are admin actions that can be performed against previous run attempts to allow previous history to be purged from the database. This can be quite a beneficial activity to perform on a regular basis to improve performance of the EFT process.

    • There is a new Admin action that can be performed allowing a successful live run to be reversed. This is ONLY available to super user and therefore under control of Customer Services.

How to process an EFT Collection Run

EFT Trial run process (Steps 0-3)

  • Step 0
    • The underlying actions of this pre-run step is to ensure system settings are configured correctly and that any potential bank accounts that could be used for an EFT run are marked accordingly.
    • This is done by calling the stored procedure DUSP_UpdateBankDetails in the Members database/schema. It makes sure the AuddisAction field holds the correct value based on existing data and in relation to the dates provided when the run is started.
    • If the process is running a SEPA output, it will validate bank accounts are SEPA compliant and set a flag accordingly. This is done by calling the stored procedure DUSP_UpdateBankDetailsForSepa in the Members database/schema.
  • Step 1
    • This step will gather information on already existing and raised sales invoices that are unpaid, have the relevant payment method, and have a due date on or prior and including the sale due cut-off date entered when the process was started. The members details will then be checked to make sure they have an account configured to attempt the collection etc. Any failures will be in the reports generated in Step 3.

      The first process that is completed when calling the stored procedure DUSP_CollectSalesForProcessing in the Members database/schema.
      • This stored procedure then calls one of several other SP’s depending on the relevant params provided when the run started.
      • The underlying result is that the sales invoices in scope will be identified, along with member bank details and inserted into the DD_CollectionSalesDetails table in the DirectDebit database/schema.
      • This process will also have determined if the collection should be attempted if there is a reason why the collection will fail.
    • When this data has been collated, the stored procedure DUSP_ProcessCollectionRunData in the Members database/schema is called.
      • This will parse the data now in the DD_CollectionSalesDetails table, extracting those records that are determined as valid for collection and inserting the information from those records into the DD_CollectionRun table in the DirectDebit database/schema.
      • These records are then available for use during Step4.
  • Step 2
    • This step will attempt to identify subscriptions that are in scope to be rolled forward a period.
    • The subscriptions will have the Auto Renew column set to 1 in the Subscriptions table in the Members database/schema. When these are identified, various other conditions will be applied to those subscriptions such the members current status or if there being a Renewal Stop Date against the record to signify the subscription should no longer be rolled.
    • Based on the result of these tests, records will be identified as either being in scope to potentially be rolled forward or to have the underlying Subscriptions record updated to signify that they should no longer be in scope to roll forward.
    • The main determination process is started by calling the stored procedure DUSP_ProcessSubscriptionsData in the Members database/schema.
    • Based on configuration properties, this calls one of several other SP’s. The underlying concept is it will check various conditions that may be in scope to be pertinent to the subscriptions possibility of being rolled forward. These include:
      • Member Actions – this could invoke the condition that the sub is frozen for example.
      • Products being available to purchase – a Subscription product may not be purchasable based on the price level of the member or the configured time band.
    • Pricing for the subscription product(s) will also be determined where applicable based upon price level etc. including any potential price level override (PLO).
    • The extracted data will be parsed and relevant results inserted into the DD_Subscription and DD_SubscriptionProducts tables in the DirectDebit database/schema. Also
      • Records will be inserted into the DD_CollectionRun table in the DirectDebit database/schema where they are deemed as collectable.
      • Records will be inserted into the DD_FailedCollectionRun table in the DirectDebit database/schema where it has been determined there is an issue where the collection could not be done such as the subscription being set to not collect or the AUDDIS flag indicating that the bank account details are cancelled/rejected.
      • Records will be inserted into the DD_EventLog table in the DirectDebit database/schema for those records identified as Purchase Not Allowed.
  • Step 3
    • This step builds on the data extracted from Steps 1 and 2 by putting failure events into a table for reporting.
    • The stored procedure DUSP_ProcessFailedCollectionRunData in the Members database/schema is called.
      • This will look at the data in scope for Step1 and determine if errors should be reported and inserted into the DD_FailedCollectionRun table in the DirectDebit database/schema. Examples are if the Member is set to not collect sales, has no bank account, bank account has an AUDDIS status indicating it will fail.

EFT Live run process (Steps 4-5)

  • Step 4
    • If the user wants to perform step 4, 'Generate Live File,' they must check 'Produce Live File.' When the user clicks on the process, the system will generate a warning alert asking, "Are you sure you wish to perform a live EFT file collection run?" If the user confirms, the EFT process will begin. If the user clicks 'Cancel,' the process will not start.
    • The step 4 task performs the following against the databases/schemas:
      • Check that we have sufficient next Id’s available in target tables prior to starting to try and ensure we can complete the task without database issues such as primary key violations.
      • Update Subscriptions data in the Members database/schema.
      • Generate SalesInvoices, SalesInvoiceProducts and SalesReceipts in the Members database/schema.
      • Create member specific EventLog entries in the Members database/schema.
      • Mark all generated SalesInvoices as paid.
      • Create the output file ready for sending to the bank.
    • Here is more of a technical overview of the code flow:

      Items in [] are stored procedure names, where all in the Members DB Table references are predominantly in the Members database/schema, but a few are in the DirectDebit database/schema.

      [DUSP_InsertSalesInvoiceForSub] called from code

      Start a transaction
           Update Members.Subscriptions that should no longer be auto renew (in batches as necessary) - failure will rollback transaction
           Insert into Members.SubscriptionInstallments as required (in batches as necessary) - failure will rollback transaction
           Update Members.Subscriptions as required (in batches as necessary) - failure will rollback transaction
           Remove corporate subs from Members.Subscriptions as required (in batches if necessary) - failure will rollback transaction
           Insert corporate subs into Members.Subscriptions as required (in batches if necessary) - failure will rollback transaction
           Remove linked Members.Subscriptions as required (in batches if necessary) - failure will rollback transaction
           Insert linked Members.Subscriptions as required (in batches if necessary) - failure will rollback transaction
           Insert into Members.Work_SalesInvoicesData as required
      Commit transaction

      Calls [DUSP_InsertSalesInvoicesToBeInserted]
           Insert into Members.SalesInvoices as required (in batches if necessary)
           Insert into Members.SalesInvoiceProducts as required (in batches if necessary)

      Insert Members.EventLog entries for renewal stops (in batches if necessary)
      Insert Members.EventLog entries for rolled subscriptions (in batches as necessary) {This is the bit we see showing before/after dates}

      **********
      AT THIS POINT, ALL THE SUBSCRIPTIONS SHOULD HAVE ROLLED
      **********

      [DUSP_CollectSalesForProcessing] called from code
      calls [DUSP_CollectSalesForProcessingWithOutSiteWithRest]
           Insert into DirectDebit.DD_CollectionSalesDetails all invoices that are deemed as collectable for the EFT system targeted based on payment method

      **********
      AT THIS POINT WE SHOULD HAVE ALL THE SALES INFO NEEDED IN DD_CollectionSalesDetails
      **********

      [DUSP_UpdateSalesInvoicesData] called from code

      Start a transaction
           Insert into DirectDebit.DD_FileData - failure will rollback transaction
           Insert into Members.SalesReceipts (in batches if necessary) - failure will rollback transaction
           Insert into Members.SalesReceiptsAllocations (in batches if necessary) - failure will rollback transaction
           Update Members.BankAccounts to set NewInstructionInd to 0 from 1 where required - failure will rollback transaction
           Insert Members.Work_SalesInvoicesTobePaidData
      Commit transaction

      [DUSP_UpdateSalesInvoicesToBePaid] called from code
           Update Members.SalesInvoices to mark SalesInvoices as paid (in batches if necessary)

      **********
      AT THIS POINT EVERYTHING SHOULD BE DONE
      **********
    • Next Ids checks
    • Previously, during the EFT process, a failure sometimes occurred due to a primary key violation of the SalesReceiptID, SalesInvoiceID, and SubscriptionRefs. Upon review, a flaw was identified in the way NextIds were pre-allocated and then used for the inserts. This error occurred during EFT Step 4, causing the EFT to fail.
      • Now, at the start of Step 4 in the EFT run, we check the Next Ids for SalesInvoices, SalesReceipts, and SubscriptionRefs. If we encounter a conflict based on the number of rows to be added, the process log will indicate which table has the conflict and specify the conflicting IDs.
      • If the conflict count exceeds 10, the log will display: "X number of conflicts found in table Range: StartId to EndId."
      • If no conflicts are found, the log will simply state: "No conflict found in NextId in [table name]."
  • Step 5
    Important
    • After running the EFT live run, the generated file will be saved at the default path: C:\inetpub\wwwroot\GC\GladstoneConsole\DirectDebitFile.
    • If you wish to change this path, navigate to GMC, and navigate to Configuration Manager > Configuration.
      1. Select the product ' GMC EFT Module' and App ID 'dd'. Under 'Collection File Settings', you can specify the desired save location in the following format: C:\MRM\Data\Eft\

Reports

Process Logging Report

  • Overview:
    Electronic Funds Transfer (EFT) logging is a new feature designed to assist both user and Gladstone Customer Services to diagnose and troubleshoot issues that occur at different stages of the EFT process. This feature logs relevant information into the database table DD_ProcessActivityLog, and introduces a new report called "Process Logging" under the GMC section "Electronic Funds Transfer".
  • Logging Levels
    • The logs are categorized into three levels to provide varying degrees of detail:
    • System & Errors (Default)
      • System Logs: Capture the time and details of each step performed during the EFT process.
      • Error Logs: Record any errors encountered during an EFT run. It also records the stack trace in the code which can be useful for the identification of the error.
    • Warnings
      • Logs warnings such as reasons why a subscription was not rolled over for a member.
    • Full Reporting
      • Includes logs from System, Errors, and Warnings.

Configuration

  • To set the logging level, navigate to the GMC EFT Module settings under "BACS EFT Process Reporting Settings" and select the desired level:
    • System & Errors: Default level, logs basic system actions and errors.
    • Warnings: Logs warnings along with system actions and errors.
    • Full Reporting: Comprehensive logging, including system actions, errors, and warnings.

New Report: Process Logging

  • The "Process Logging" report is added to provide detailed insights into the EFT process. The report includes the following log details:
    • Date Time: Timestamp of the log entry.
    • Member ID: Identifier of the member involved (if applicable).
    • Step: Indicates the step number (Step 1, 2, 3, or 4) in the EFT process.
    • Event Type: Specifies whether the log entry is a System, Error, or Warning event.

User Interface

  • The "Process Logging" report allows users to:
    • Select Site: Choose the site for which the EFT run was performed.
    • Select EFT Run: Choose a specific EFT run to view its logs.
    • Show Log: Display the log details on the Process Logging screen.
    • Export Options: Export the log details in various formats, including Excel, CSV, and PDF.
    • This detailed logging capability will enable Gladstone Customer Service to trace issues more efficiently, ensuring smoother EFT operations and quicker resolution of any problems encountered.

Administration

  • Overview
    This page enables various actions to be performed, such as purging historic data or potentially reversing a previous successful EFT run to allow data to be modified ahead of a repeat run.

Remove Historical EFT Collection Run data

  • This will allow removal of historical EFT run data to be removed from the database. This is NOT reversable.
  • You can select to retain process runs within a certain time span or a specific number of runs. You can also elect to remove any failed runs which adhere to the chosen retention criteria.

Remove Historical EFT AUDDIS Run data

  • This is very similar to the option above but relates purely to AUDDIS submissions that have been created.

Create a Trial EFT File

  • This feature is used to provide a BACS submission software provider a BACS approved test output file, which can be parsed via their platform and identify any specific configuration requirements from their perspective.
  • This allows the user to create a test output file of the same format that would be generated from a live file BUT this will not actually update the main database as would normally happen as part of Step 4 of an EFT run.


Reversal of a Live EFT Process

  • Summary
    When a user initiates an EFT and it fails, or if an administrator overlooks any details regarding bank account information, Gladstone Customer Services have the capability to reverse a specific EFT process. After Gladstone Customer Services have performed the reversal, the administrator should be able to rerun the entire EFT process. During this new run, the process will consider the sales invoices from the failed or reversed transactions. The new EFT run will consider all previous files and include any additional files, displaying the results accordingly.
  • A new status called "Reversed" has been added, along with a new tab in the Administration page named "Live EFT Process Reversal." In this tab, there are two dropdown menus. When performing a test run, the "Processing Details" dropdown will not display the process ID. However, during a live run, selecting a site from the "Current Site" dropdown will show the live ID, enabling the administrator to reverse the process.
  • How to use reversal
    • Prerequisites:
      • The Gladstone Customer Service user will log-in with admin rights.
    • How to Run Reversal:
      • Log in to GMC
      • Navigate to the EFT Module and initiate a live EFT run
      • Check the Process Status page to determine if the recently executed EFT process live run has failed or completed.
      • Go to the Administration page.
      • Navigate to "Live EFT Process Reversal" tab.
      • Select the current site where you executed the EFT process.
      • Choose the process ID from the drop-down menu under process details.
      • Click the “Reverse live EFT run” button.
      • If you receive the message "Reverse EFT run has completed successfully," it means the EFT process has been reversed. You can confirm this by checking the Process Status page, where the status for the reversed process ID should now be "Reversed." Additionally, verify this on the Process Logging page for the respective process ID.
    • Process Status:
    • Process Logging Page:
    • If you click the button and receive the validation message "Unable to reverse EFT run. See Event Log for more information," check the Event Viewer. This message typically appears when there are no sales invoices associated with the process ID you are attempting to reverse.
    • Windows Event Viewer:
    • If you receive the validation message " Another process is running - please wait for this to complete before starting a Reverse EFT," it means the EFT process is still in progress. Reversal can only occur once the process has either completed or failed.
    •  Store Procedure referenced:
      • DUSP_ReverseEFTRun