Automated Reconciliation Process for Order Tracking and Payment Providers

Overview & Challenge

The Client, a leading Canadian retailer uses Salesforce Commerce Cloud (SFCC) as their e-commerce platform. SFCC plays a crucial role in managing their online sales and customer interactions. The challenge they faced was to reconcile web orders and integrate data from different payment providers within the SFCC environment.

By leveraging SFCC’s capabilities and integrating it with the solution, the client wanted to streamline the tracking of web orders and data integration from various payment providers. This process would enhance efficiency, facilitate fraud detection, and ensure accurate financial reconciliation between their channels, specifically Salesforce Commerce Cloud (SFCC), and the payment providers.


The main challenge for the Client was to efficiently track web orders within Salesforce Commerce Cloud (SFCC) and retrieve order details using the OCAPI (Open Commerce API) documentation, specifically focusing on Order Search and Order endpoints. In addition, they needed to integrate data from various payment providers to reconcile transactions and identify potential fraud.

Furthermore, they wanted to retrieve a list of orders that met specific search query criteria and store the relevant order details in a database. Another challenge was to compare the retrieved order information with the existing database records to determine if full order details needed to be exported based on changes or updates.

The Client needed a scalable solution that could handle order search queries, pagination, data comparison, and storage effectively.


To address the challenge, we designed and implemented a solution that leveraged the OCAPI documentation for Order Search and Order endpoints. By using SnapLogic, we developed a custom integration layer that interacted with SFCC’s API to retrieve the required order data. The order details were then flattened and stored in a database, allowing for efficient reconciliation and fraud detection processes.

We incorporated pagination support to handle large result sets and implemented logic to compare field values from the Order Search response with existing database records to determine the need for exporting full order details.

Business Outcomes

The automated reconciliation process successfully tracked web orders and integrated data from various payment providers. The benefits achieved include:

Improved Efficiency: By automating the reconciliation process, manual efforts were significantly reduced, enabling faster and more accurate order tracking and financial reconciliation.

Fraud Detection: The streamlined process facilitated the identification of potential fraudulent transactions by comparing and analyzing order details across channels and payment providers.

Enhanced Reconciliation: Accurate and up-to-date order data in the database allowed for efficient reconciliation between SFCC and payment providers, reducing discrepancies and ensuring financial accuracy.

By implementing this automated reconciliation process, the Client achieved seamless integration between SFCC and payment providers, enabling efficient order tracking, fraud detection, and accurate financial reconciliation.