Transaction Reconciliation Applet

Purpose and Overview

The Transaction Reconciliation Applet is a specialized financial control tool used to verify that every electronic payment recorded in your system has a corresponding entry in your external payment gateway statements (and vice versa). In modern retail and e-commerce, where payments flow through multiple channels like iPay88, GoPay, and 7-Eleven, this applet ensures that no revenue “leaks” through the cracks.

What is the Transaction Reconciliation Applet? (Explained Simply)

Imagine your shop processes 500 Credit Card sales in a single day.

1. Your POS system shows the total as RM 25,000.
2. The bank sends you a statement saying they only processed RM 24,800.

Where is the missing RM 200? The Transaction Reconciliation Applet allows you to upload that bank statement and automatically pinpoint the specific transactions that are missing or mismatched. It eliminates the need for manual spreadsheet comparisons, saving hours of accounting work.

Relationship to Other Applets

This applet sits between your sales operations and your final bookkeeping:

• POS / Sales Applets: These systems generate the “System Transaction Logs” that serve as the internal truth.
• Bank Reconciliation Applet: While this applet focuses on gateways (iPay88, etc.), the Bank Recon applet focuses on final current account settlement.
• General Ledger Applet: Once reconciliation is completed and verified, it ensures the balances in your GL for “Gateway Receivables” are accurate and audit-ready.

Key Features Overview

Who Benefits from This Applet?

Finance & Accounts Teams:

• Automate the reconciliation of high-volume payment gateway statements
• Maintain a clear status of payment channel health (Processing, Reviewed, Approved)
• Rapidly identify and resolve cash-in-hand discrepancies

Internal Auditors:

• Access a complete digital history of every reconciliation ever performed
• Verify that “Disputes” are being followed up on and resolved
• Ensure that the “System Log” for a period perfectly matches the bank’s reality

Operational Managers & HODs:

• Approve completed reconciliation statements through specialized workflows
• Verify the accuracy of branch-level cash management
• Resolve disputes arising from failed gateway transactions or hardware errors

What Problems Does This Solve?

The “High-Volume Nightmare” Problem: Manually checking 10,000 transaction IDs against a PDF statement is impossible to do accurately. This applet takes a 10,000-line CSV and reconciles it in seconds.

The “Missing Settlement” Revenue Leak: Payment gateways occasionally fail to settle a transaction, or a staff member might accidentally record a “Credit Card” sale that was actually “Cash.” This applet flags these errors immediately so they can be fixed before the books are closed.

Unresolved Disputes: Mismatches are often forgotten if they aren’t tracked. By categorizing mismatches as “System Txn Log Disputes” or “PC Txn Disputes,” the applet ensures they stay visible until they are resolved.

Key Features Overview

Key Concepts

Reconciliation Workflow

Reconciliation in this applet follows a structured three-step process:

1. Preparation: Uploading the external statement and defining the opening/closing context.
2. Matching: Running the Auto-Matching engine and manually pairing any leftovers.
3. Approval: Moving the document from “Processing” to “Reviewed and Approved” after verifying disputes.

Document Lifecycle

Reconciliation statements move through the following statuses to ensure accountability:

Quick Start Guide

For Accountants: Performing Auto-Matching

Goal: Quickly reconcile a Month-End iPay88 statement.

1. Navigate: Go to Payment Channel > Add (+).
2. Define Context:
   • Select your Company and Branch.
   • Click the search icon next to Payment Channel System Log to link to your internal records.
   • Enter the Opening/Closing Dates and Balances from your iPay88 portal.
3. Upload: Click the Upload Statement tab.
   • Choose your Import Format (e.g., IPay88).
   • Drag your CSV file into the File Uploader.
4. Match: Click the Auto Matching button at the top right.
5. Review: Check the results. Matched records are hidden from the pending lists; mismatches move to the Report tab.

For Managers: Authorizing a Reconciliation

Goal: Verify a Finance Officer’s work and approve the statement.

1. Open Statement: Look for documents in the listing with the status Ready for review.
2. Verify Totals: Confirm the recorded Closing Balance in the applet matches the physical bank statement.
3. Review Disputes: Go to the Report tab and look at the “System Txn Log Dispute” list. Ask: “Why did we record these 5 sales if the bank says they never received money?”
4. Confirm: If everything is reconciled or explained, change the status to Approved by HOD.

For Admins: Applet Configuration

Goal: Set up the legal entities and permissions.

1. Create Company: Go to Company > Create Company. Fill in the mandatory Company Code, Registration No, and Currency.
2. Configure Formats: Ensure your payment gateway’s CSV format matches the selection in the “Upload Statement” tab.
3. Permissions: Use Settings > Permission Set Listing to grant users access to “Auto Matching” or “Reconcile” buttons.

Document Tabs Overview

The Transaction Recon Applet organizes the workflow into four primary tabs. Use these tabs to move from initial data entry to final approval.

Details Tab

The “Header” of your reconciliation.

• Context: Defines the Company, Branch, and Payment Channel System Log being audited.
• Period: Sets the Opening Date and Closing Date for the reconciliation window.
• Financials: Stores the Opening Balance and Closing Balance as reflected in the payment gateway’s portal.
• Status Management: Track the progress of the document from Processing to Reviewed and Approved.

Deep Dive: Details Fields

• Payment Channel System Log: This links to your internal database of transactions. You must select the correct log (e.g., “Terminal A - iPay88”) to ensure you are comparing the right data.
• Balance Fields: These are mandatory. If the Closing Balance you enter here doesn’t match the sum of your matched transactions, the system will highlight the discrepancy.
• Status Dropdown: This is the workflow control. Changing this triggers the next stage of the financial approval cycle.

Upload Statement Tab

Where external data enters the system.

• File Uploader: Drag and drop your gateway-provided CSV or Excel file here.
• Import Format: Crucial selection. Different gateways (GoPay vs iPay88) have different data columns. Selecting the correct format ensures the “Transaction ID” and “Amount” are read correctly.
• Help Guide: Displays bank-specific format rules to ensure successful uploads.

Manual Matching Tab

The “Human Intervention” workspace for items the computer couldn’t quite match.

• System Txn Log (Sub-tab): Shows internal transactions that haven’t been matched to an external statement yet.
• Payment Channel Txn (Sub-tab): Shows statement lines that haven’t been matched to an internal system record yet.
• Matching Action: Click a row to see details, then use the Matched feature to pair them manually if you’ve verified they represent the same transaction (e.g., if a transaction ID has a prefix in one system but not the other).

Deep Dive: Matching Fields

When reviewing transactions for manual matching, you will see these critical data points:

• Txn ID / Payment ID: The unique identifier from the gateway.
• Ref Num: Your internal reference (e.g., Invoice Number).
• Bank MID: The Merchant Identification Number — useful for businesses with multiple merchant accounts.
• Amount & Currency: Must match exactly for a standard reconciliation.

Report Tab

The dashboard of discrepancies. If something is in this tab, there is a problem that needs investigation.

• System Txn Log Dispute (Sub-tab): Lists transactions that exist in your system but the Payment Gateway has no record of.
  • Common Cause: Technical failure during payment submission or internal data entry error.
• Payment Channel Txn Dispute (Sub-tab): Lists money received by the bank that your system has no record of.
  • Common Cause: Manual payments received outside the app or POS system errors.
• Reconciliation Action: Use the Reconcile button within these reports to finalise an item after its dispute has been resolved.

Configuration & Settings

Company Profile (Company > Create/Edit)

The Transaction Recon applet requires a properly configured Company profile to maintain legal and financial compliance.

System Settings (Settings)

• Permission Set Listing: Controls who can perform sensitive actions like deleting a statement log or approving a reconciliation.
• Organisation Constants: Defines the underlying codes used for Business Registration Numbers and Contact Info mapping across the ERP.

FAQ

Q: What is “Auto Matching” and how does it work?
A: Auto-Matching is an intelligent engine that looks at your uploaded statement and your internal logs. It automatically pairs them if the Transaction ID and Amount are identical. This usually resolves 90-95% of your records instantly.

Q: Why do some transactions show as “Disputes”?
A: A dispute occurs when there is a one-sided entry. Either your system says you received money and the bank doesn’t show it (Missing Funds), or the bank shows money received that you haven’t recorded (Unrecorded Sale).

Q: Can I re-upload a statement if I found an error in the first file?
A: While in “Processing” status, you can reset the statement and re-upload. However, once the status is moved to “Approved”, the data is locked for audit integrity.

Q: What do I do if I have a “Payment Channel Txn Dispute”?
A: This usually means a customer paid you, but no one recorded the sale in your POS or Receipt applet. You should find the missing sale, record it, and then the transaction will be ready to match.

Q: Does this applet support multiple branches?
A: Yes. You can scope your reconciliation to a specific Branch in the Details tab. Each branch can have its own separate reconciliation schedule and HOD approval.

Related Applets

Bank Reconciliation Applet

Purpose: Reconciles high-volume cash and current account bank statements.
Relation: While Transaction Recon focuses on e-wallets/gateways, Bank Recon covers your actual bank accounts.

General Ledger Applet

Purpose: The central bookkeeping record for your entire organization.
Relation: Reconciled data provides “Verified” balances for your gateway receivables in the G/L.

Internal Receipt Voucher Applet

Purpose: Confirms receipt of funds for intercompany or customer transactions.
Relation: Reconciled transactions are often the “Proof of Payment” required to finalize a Receipt Voucher.