This Apparel Firm Streamlined Cash, Card, and UPI Payments with Power Automate.

A case on how a multi-location retail business replaced its manual payment reconciliation process with a fully automated Power Automate workflow. 

The Client

The client is a large, women-led retail enterprise with a strong presence in both e-commerce and brick-and-mortar formats. Rooted in traditional values, the organization spans multiple brands across categories such as apparel, lifestyle products, and handcrafted goods. Known for its commitment to Indian heritage, they partner closely with artisan communities and self-help groups, preserving craftsmanship while creating sustainable livelihoods. The group caters to both retail and bulk customers across weddings, festivals, and corporate markets, positioning itself as a leading player in the traditional lifestyle segment. 

Challenges Faced by the Client

The client operates six physical stores across the state. As sarees are sold each day, payments are collected through cash, card, or UPI. All transactions are recorded in their open-source ERP system, Odoo, which is also used for invoicing. Payments are collected in two ways: first, through their Point-of-Sale systems (POS), the machine that handles card and digital transactions; and second, through a separate tool called Amply, which records the physical cash collected by the cashier. 

The goal at day-end is to reconcile the sales recorded in their ERP with the transaction reports from the POS machines to check for any mismatches. But this process was challenging due to: 

1. Manual Matching of Store-Wise Transactions: The POS systems generate separate transaction reports for each store. However, payment gateway platforms combine all transactions into one consolidated file, without denoting which store each transaction came from. To work around this, the team had to manually match each transaction using TIDs (Terminal IDs), unique to the card machines installed in each store.  
2. Different File Formats Across Systems: The team had to deal with multiple file formats across the payment gateways, Amply, and Odoo reports. Each file looked different, with different column names, date formats, and layouts. Because nothing was in a standard format, they couldn’t just compare the numbers directly. They had to spesnd hours cleaning up and adjusting the files before starting the reconciliation. 
3. Complex Payment Scenarios: Not all customer payments were one-time or straightforward. Some customers paid using a mix of methods (part UPI, part cash) or completed the payment in 2 parts on different days. Others used international cards, which included extra charges. These variations made it hard to match what was sold with what was received.  
4. Manual Tracking of Mismatches: When a mismatch was found, the team had to determine the reason (refund, a failed payment, or a delay in settlement) by reviewing all the reports manually. They also had to write down the reason for every issue in the report. This whole process depended on people remembering and cross-checking everything, which made it tiring. 
5. Difficulty Scaling with Store Growth: Aside from handling errors, the team also struggled to update the system. If a new POS Machine/Billing Counter/Store is added, they must manually update the mapping in every reference file. There was no central place to manage these changes, making the workflow hard to maintain as the business grew.  
6. Time-Heavy Process: On average, this process takes 2.5 to 3.5 hours daily. During high sales periods or festivals, the team couldn’t keep up with the volume, delaying financial reporting and increasing the risk of oversight. 

Solutions

To address these reconciliation challenges, we implemented an end-to-end solution using Power Automate integrated with AI. Here is how we tackled each problem: 

• Store-Wise Mapping of Payments Using TID Logic: We used browser automation and APIs to auto-fetch daily transaction reports from Odoo and payment gateways in their original formats (PDF, Excel, etc.). However, these reports do not mention which store processed each transaction — they only list payment amounts without store details. To solve this, we created a centralized TID mapping file that links each TID to its corresponding store. This lets the system automatically match every transaction to the right store, eliminating the need for manual verification.  

• Clear Data Structures Across Sources: We created structured templates for each data source to standardize the reconciliation process. Data from Odoo (Sales), POS (UPI and Card), and Amply (Cash) are auto-downloaded and converted into a single, clean Excel file with consistent columns: Date, Cash, UPI, and Card. This made it easy to compare values across systems without any manual formatting. 

• Handling Complex Transactions with Sum-up Logic: The automation handles real-world payment scenarios like split payments, delayed settlements, and international card charges. It adds up partial payments, accounts for additional international transaction fees, and compares store-wise totals using TID-based mapping. This logic ensures even the most complicated payment combinations are accurately matched and reconciled without human effort. 

• Automatically Flagging Transactional Mismatches: Once reports are standardized, the system automatically compares sales data with payment records using transaction date, amount, and TID. If there is a mismatch for any store or payment type, the relevant rows are flagged in the reconciliation sheet with system-generated remarks (Matched, Not Matched, or Partially Paid), helping the team identify and review issues quickly. 

• Scalability Without Rework: When a new store, counter, or POS machine is added, the team simply updates the TID and store name in the template file. The system picks it up automatically, so no code changes or backend updates are needed, making the setup easy to scale.  

Solutions Benefits

• Faster Verification: Reconciliation now takes 12 minutes instead of 3.5 hours, with no manual formatting or data cleanup.  

• Quicker Detection of Mismatches: The system flags mismatches across UPI, card, and cash values by comparing with Odoo’s data, making it easier to catch underpayments, partial settlements, or missing transactions at the store level. 

• Automatic Email Alerts: The system sends email notifications to the relevant stakeholders whenever there is a mismatch, a data fetch issue, or a processing delay, so they can take prompt action without waiting for manual checks. 

• Simplified Audits: Daily reconciled reports are automatically stored in Dataverse with separate table names. This way, finance teams and auditors can locate required files quickly without manual sorting or renaming. 

Conclusion

With end-to-end automation, our solution replaced a slow, manual reconciliation process with a streamlined system that automatically fetches, compares, and flags payment records across 6 stores. What used to take over 3 hours now takes just 12 minutes, with fewer errors and minimal effort. As a Microsoft Solutions Partner, UB Technology Innovations, Inc. helps retail businesses like this one eliminate manual data work and scale operations smoothly with intelligent automation. 

About Us

UB Technology Innovations, Inc. (UBTI) is a leading global technology solution provider with over 3 decades of experience across all industries, specializing in Capital Markets, Logistics, and Healthcare. We are the preferred Microsoft Solutions Partner backed by a world-class team of Microsoft Certified experts with rich experience in Azure Cloud Platform and Data Analytics.

One of the Top 10 winners of the
Microsoft’s Azure AI & ML Workathon