Automated Payment Reconciliation for Hospitality: Accounting Synchronization in VikBooking & Mercado Pago
Learn how to automate hospitality accounting and payment settlement. A technical guide to integrating VikBooking with Mercado Pago Settlement Reports and local tax withholdings across LATAM.
In the hospitality and vacation rental industry, financial management does not end when a guest completes their room booking with a credit card or bank transfer. For hotel accounting departments, boutique resorts, and property management networks, that transaction marks the beginning of an operational bottleneck: payment reconciliation.
When a hotel runs VikBooking on WordPress alongside regional processors such as Mercado Pago, a substantial financial gap emerges between the Gross Booking Value shown in the reservation engine and the actual net funds wired into the hotel’s corporate bank account weeks later. Gateway processing fees (typically 2.99% to 3.49% + VAT), mandatory municipal and federal tax withholdings at the source (ReteFuente, ReteIVA, and ReteICA in Colombia; IIBB gross income tax withholdings in Argentina; ISR/IVA withholdings in Mexico), and instant payout fees create recurring ledger discrepancies.
Many hospitality businesses attempt to reconcile these transactions manually by downloading CSV statements from payment consoles and cross-referencing rows in Excel. This manual approach not only burns dozens of operational hours every month but inevitably produces human reconciliation errors, unmonitored capital leakage, and painful delays during fiscal year-end audits.
In this deep dive for backend engineers, CFOs, and hospitality platform architects, we explore the architecture of automated payment reconciliation, dissect how to consume Mercado Pago’s Balance and Settlement Reports APIs, and implement an automated synchronization pipeline within the VikBooking database.
1. The Financial Equation of Hospitality Bookings in Latin America
To architect an automated reconciliation engine, we must first mathematically model the fund flow of an individual reservation:
┌────────────────────────────────────────────────────────────────────────┐
│ Financial Breakdown of a Reservation │
└────────────────────────────────────────────────────────────────────────┘
[Net Room Rate] $ 1,000,000 COP
+ [Local VAT / Tax 19%] $ 190,000 COP
---------------------------------------------------------
= [Guest Charge (Gross Booking Value)] $ 1,190,000 COP (Recorded in VikBooking)
- [Mercado Pago Fee (3.19% + VAT)] -$ 45,208 COP
- [Income Tax Withholding (ReteFuente)] -$ 17,850 COP
- [VAT Withholding (ReteIVA 15%)] -$ 5,415 COP
- [Industry & Commerce Tax (ReteICA)] -$ 4,926 COP
---------------------------------------------------------
= [Net Liquidated Bank Settlement] $ 1,116,601 COP (Available Operating Cash)If the booking engine records that $1,190,000 COP was charged, but the corporate bank account receives $1,116,601 COP, an accounting gap of $73,399 COP exists. Without software that programmatically itemizes every tax deduction and processing fee tied to the specific booking_id, month-end financial statements will never balance before national tax authorities (DIAN, SAT, or AFIP).
2. Architectural Design: Two-Stage Reconciliation Pipeline
Enterprise payment reconciliation never relies exclusively on synchronous webhooks. It requires a decoupled, two-stage architectural design:
[Stage 1: Real-Time Operational Layer (IPN Webhooks)]
Mercado Pago ──► Webhook Listener ──► Marks Room as PAID in VikBooking
(Prevents Overbooking & Confirms Check-In)
[Stage 2: Deferred Settlement Layer (Batch Reconciliation CLI)]
Cron / Worker ──► Queries /v1/balance/history & Settlement APIs
│
▼ (Correlates External Reference: "VB-4589")
Calculates Gateway Fees & Exact Tax Deductions
│
▼
Updates General Ledger Audit Tables in WordPress- Operational Layer (Real-Time): Validates authorization tokens and transitions the room status to confirmed, ensuring guests receive immediate confirmation and avoiding double bookings.
- Financial Settlement Layer (Daily Batch): Executes on a scheduled cron worker (typically at 02:00 AM) once payment aggregators finalize daily clearing batches. This layer fetches settled transaction IDs, categorizes tax withholdings, and transitions ledger records to RECONCILED.
3. Consuming Mercado Pago’s Balance & Settlement APIs
Mercado Pago exposes the /v1/balance/history endpoint to audit financial movements credited to a merchant account. Unlike the standard transactional payment endpoint (/v1/payments/{id}), this resource details exact clearing timelines, fee breakdowns, and tax retentions:
# Fetch settled financial movements for the past 24-hour cycle
curl -X GET "https://api.mercadopago.com/v1/balance/history?begin_date=2026-09-06T00:00:00Z&end_date=2026-09-06T23:59:59Z" \
-H "Authorization: Bearer TEST-789456123-APP-TOKEN" \
-H "Content-Type: application/json"The returned payload provides an itemized audit trail within the charges_details array:
{
"id": 987654321,
"date_created": "2026-09-06T14:32:10.000-04:00",
"date_approved": "2026-09-06T14:33:05.000-04:00",
"date_released": "2026-09-08T00:00:00.000-04:00",
"external_reference": "VB-4589",
"transaction_amount": 1190000.00,
"net_amount": 1116601.00,
"fee_details": [
{
"type": "mercadopago_fee",
"amount": 45208.00,
"fee_payer": "collector"
}
],
"charges_details": [
{
"type": "tax_withholding",
"name": "rete_fuente",
"amount": 17850.00
},
{
"type": "tax_withholding",
"name": "rete_iva",
"amount": 5415.00
},
{
"type": "tax_withholding",
"name": "rete_ica",
"amount": 4926.00
}
]
}4. WordPress Implementation: Automated WP-CLI Reconciliation Command
Processing high-volume transaction batches via HTTP browser requests exposes systems to PHP max_execution_time timeouts. The industry best practice is encapsulating the reconciliation engine inside a WP-CLI command, triggered reliably by an OS-level Linux cron job (crontab):
<?php
/**
* Plugin Name: DoneAPI - VikBooking & Mercado Pago Reconciliation Engine
* Description: Automated financial reconciliation and settlement ledger for VikBooking reservations.
* Version: 2.0.0
* Author: DoneAPI Engineering Team
*/
if (!defined('ABSPATH')) {
exit;
}
if (defined('WP_CLI') && WP_CLI) {
WP_CLI::add_command('doneapi reconcile-payments', 'DoneAPI_Reconcile_Command');
}
class DoneAPI_Reconcile_Command {
/**
* Executes automated reconciliation between Mercado Pago settlements and VikBooking
*
* ## OPTIONS
* [--days=<days>]
* : Number of days to look back for settled transactions (default: 1).
*
* ## EXAMPLES
* wp doneapi reconcile-payments --days=2
*/
public function __invoke($args, $assoc_args) {
$days = isset($assoc_args['days']) ? intval($assoc_args['days']) : 1;
WP_CLI::line("Starting automated financial reconciliation for the past {$days} days...");
$access_token = defined('MERCADOPAGO_ACCESS_TOKEN') ? MERCADOPAGO_ACCESS_TOKEN : get_option('doneapi_mp_access_token');
if (!$access_token) {
WP_CLI::error("Mercado Pago Access Token is not configured.");
return;
}
$begin_date = gmdate('Y-m-d\TH:i:s\Z', strtotime("-{$days} days"));
$end_date = gmdate('Y-m-d\TH:i:s\Z');
$url = add_query_arg([
'begin_date' => $begin_date,
'end_date' => $end_date,
'status' => 'approved',
], 'https://api.mercadopago.com/v1/payments/search');
$response = wp_remote_get($url, [
'headers' => [
'Authorization' => "Bearer {$access_token}",
'Content-Type' => 'application/json',
],
'timeout' => 30,
]);
if (is_wp_error($response)) {
WP_CLI::error("Failed to connect with Mercado Pago API: " . $response->get_error_message());
return;
}
$body = json_decode(wp_remote_retrieve_body($response), true);
$results = $body['results'] ?? [];
WP_CLI::line("Found " . count($results) . " approved transactions in the selected period.");
global $wpdb;
$reconciled_count = 0;
foreach ($results as $payment) {
$external_ref = $payment['external_reference'] ?? null;
if (!$external_ref || strpos($external_ref, 'VB-') !== 0) {
continue; // Skip non-VikBooking payments
}
$booking_id = intval(str_replace('VB-', '', $external_ref));
$gross_amount = floatval($payment['transaction_amount']);
$net_amount = floatval($payment['net_received_amount'] ?? 0);
$mp_fee = 0.0;
$taxes = 0.0;
if (isset($payment['fee_details'])) {
foreach ($payment['fee_details'] as $fee) {
$mp_fee += floatval($fee['amount']);
}
}
if (isset($payment['charges_details'])) {
foreach ($payment['charges_details'] as $charge) {
if ($charge['type'] === 'tax_withholding') {
$taxes += floatval($charge['amount']);
}
}
}
// Upsert into dedicated reconciliation audit ledger
$table_reconcile = $wpdb->prefix . 'doneapi_hotel_reconciled_ledger';
$wpdb->replace($table_reconcile, [
'booking_id' => $booking_id,
'payment_id' => $payment['id'],
'gross_amount' => $gross_amount,
'gateway_fee' => $mp_fee,
'tax_withheld' => $taxes,
'net_amount' => $net_amount,
'reconciled_at' => current_time('mysql'),
'payment_method' => $payment['payment_method_id'] ?? 'unknown',
'status' => 'RECONCILED'
], ['%d', '%s', '%f', '%f', '%f', '%f', '%s', '%s', '%s']);
// Append internal audit notes directly to VikBooking log
if (class_exists('VikBooking')) {
$audit_note = sprintf(
"Reconciled successfully. Gross: $%.2f | Fee: -$%.2f | Tax: -$%.2f | Bank Net: $%.2f",
$gross_amount, $mp_fee, $taxes, $net_amount
);
VikBooking::addPaymentLog($booking_id, [
'gateway' => 'DoneAPI Reconciler',
'transaction_id' => $payment['id'],
'amount' => $net_amount,
'status' => 'RECONCILED',
'notes' => $audit_note
]);
}
$reconciled_count++;
}
WP_CLI::success("Reconciliation complete. {$reconciled_count} reservations synchronized in accounting ledgers.");
}
}5. Dedicated Accounting Audit Ledger Table
To maintain strict isolation without polluting core WordPress database structures, we implement a dedicated InnoDB audit table optimized for high-precision financial accounting:
CREATE TABLE `wp_doneapi_hotel_reconciled_ledger` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`booking_id` int(11) unsigned NOT NULL,
`payment_id` varchar(64) NOT NULL,
`gross_amount` decimal(12,2) NOT NULL,
`gateway_fee` decimal(12,2) NOT NULL DEFAULT '0.00',
`tax_withheld` decimal(12,2) NOT NULL DEFAULT '0.00',
`net_amount` decimal(12,2) NOT NULL,
`payment_method` varchar(32) NOT NULL,
`status` enum('PENDING','RECONCILED','DISCREPANCY','REFUNDED') NOT NULL DEFAULT 'PENDING',
`reconciled_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_payment` (`payment_id`),
KEY `idx_booking_date` (`booking_id`, `reconciled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;6. Managing Chargebacks, Cancellations & Partial Refunds
In hospitality, date modifications and sudden cancellations are routine operational occurrences. When processing refunds:
- Full Refunds: Mercado Pago reimburses the proportional variable commission, but fixed per-transaction fees may remain uncredited depending on regional terms. The reconciliation worker detects the
refundedwebhook status, flags the ledger entry asREFUNDED, and registers the negative balance adjustment. - Partial Cancellations (No-Show Penalties): If hotel policy retains 50% of the first night as a late cancellation penalty, the reconciliation script records the partial credit and maintains the remaining balance as earned revenue.
7. Official DoneAPI VikBooking Mercado Pago Plugin ($7 USD) & Accounting Advisory
Building a custom payment gateway integration and automatic reconciliation layer from scratch typically demands weeks of development, extensive sandbox testing, and thousands of dollars in engineering overhead.
At DoneAPI, we have packaged this architecture into a production-ready solution accessible to any hotel or vacation property:
- Official VikBooking Mercado Pago Plugin ($7 USD): Instant WordPress setup supporting credit cards, PSE, Pix, and OXXO, featuring cryptographic HMAC validation and automated
external_referenceformatting tailored for automated settlement reconciliation. - Zero Recurring SaaS Commissions: A one-time purchase of $7 USD with lifetime domain licensing and uninhibited source code access.
- ERP & Accounting Connectors: Custom data pipelines to export reconciled ledger records directly into leading platforms like QuickBooks, SAP Business One, Siigo, and Alegra.
💬 Looking to purchase the VikBooking Mercado Pago plugin for $7 USD or need expert guidance automating payment reconciliation for your hospitality chain?
Reach out directly to our engineering team on WhatsApp.
Get the VikBooking Mercado Pago Plugin ($7 USD) or Automate Your Reconciliation
Eliminate manual spreadsheets, audit processing fees and regional tax withholdings, and balance your books to the penny.
8. Conclusion
Automated payment reconciliation is not merely an operational convenience—it is essential to the fiscal compliance and profitability of any modern hospitality enterprise.
By pairing VikBooking with Mercado Pago settlement endpoints, hospitality businesses eliminate accounting ambiguity, calculate regional tax withholdings with mathematical precision, and ensure every collected dollar is audited and accounted for in the general ledger.
