What is the purpose of this article?

This article explains how Eshopbox integrates with ZOHO Billing to automate the invoicing process for unbilled shipping and fulfillment charges. It outlines the steps to generate invoices based on customer billing cycles, manage payments using available wallet balances, and update the invoice statuses. The article also covers how payments are processed (either fully or partially) and recorded in the system, ensuring accurate tracking and real-time updates in both the internal database and Zoho Billing.

Workflow Steps

1. Calculate Last Billing Date and Next Billing Date based on Shipping Billing Cycle

We will store billing information for customers created in Zoho Billing within our system by adding new columns to the existing accounts table:
The following columns will be added:

• shipping_billing_cycle – Stores the customer's billing frequency (Weekly, Fortnightly, or Monthly).

• last_billing_date – Initially set to the subscription_created_at date.

• next_billing_date – Calculated based on the billing cycle.

Create a Webhook in Zoho

To ensure real-time updates of the shipping_billing_cycle for customers in our system, we will create a webhook in Zoho Billing and configure a listener API to handle incoming updates.

1. Create a Webhook in Zoho Billing

• In Zoho Billing, navigate to Settings → Automation → Webhooks.

• Configure a new webhook that triggers on updates to the shipping_billing_cycle field.

• Set the webhook method to POST and provide the endpoint URL of our API.

Sample webhook Payload (POST):

2. Create a Listener API

• Create an API to receive webhook notifications from Zoho.

API Endpoint :

Request Body (Received from Zoho Webhook)

• Extract the relevant details from the request payload, including:

  • customer_id

  • shipping_billing_cycle

• Update the accounts table with the new shipping_billing_cycle value and next_billing_date(Calculate it on the basis of shipping_billing_cycle ).

2. Invoice Creation

A cron job will run daily at a scheduled time, triggering the /createInvoiceCron API to automate invoice generation for customers based on their billing cycle. The process includes:

1. Fetching accounts details for invoice creation.

2. Calculating invoice amounts based on SKU and invoicing category.

3. Creating invoices for Self-Fulfillment and Eshopbox Fulfillment Centers (FCs) using Zoho Billing API.

Detailed Workflow

Step 1: Scheduled Cron Job Execution

• Trigger the API Endpoint:

  • POST /createInvoiceCron

  • Runs daily at a pre-defined schedule.

Step 2: Fetch Accounts Eligible for Invoicing

Fetch accounts where next_billing_date matches the current date (SYSDATE).
SQL Query:

Result: List of customers (customer_id) and their corresponding accounts (account_id) due for invoicing.

Step 3: Task Queue Creation for Invoice Processing

For each eligible account_id, create two Task Queues:

• Self-Fulfillment Invoice Task Queue (Single shipping invoice per account)

• Eshopbox Location Invoice Task Queue (2 invoices(Shipping and Fullfillment) per FC for that account)

Now inside each taskqueue we will calculate the invoice amount.

3.1 Self-Fulfillment Invoice Calculation

• Calculate total invoice amount for all Zoho SKUs in the 'Shipping' category for self-Fullfillment only.

SQL query::

Result: It will fetch the zohoSku and respective invoiced amount for the account_id.

3.2 Eshopbox Location Invoice Calculation

A. Shipping Invoice Calculation for Each Warehouse(Eshopbox Fcs only)

SQL Query

Result: It will fetch invoiced amount for each warehouse and sku for shipping category.

B. Fulfillment Invoice Calculation for Each Warehouse

• Include only 'Fulfillment' category transactions for Eshopbox FCs

SQL Query

Result: It will fetch invoiced amount for each warehouse and sku for Fullfillment category.

Now we will call the Zoho Invoice creation API based on above(step 3) invoice calculation.

Step 4: Make a call to Zoho API for Invoice creation:

Sample Request Body:
Note: The request body should include all the zoho_sku in line_items for a particular invoicing categories.

Sample Response Body:

Step 5: Save invoices details in Invoices table

Extract relevant fields (e.g., invoice_id, invoice_number, status) from the API response and insert them into the Invoices table.

Step 6: Record payment in Invoices_payment table

This step involves recording the payment in the invoices_payment table based on the available unapplied_amount.

Retrieve unapplied_amount from wallet_transaction table using account_id, considering the max wallet_transaction_id for fetching unapplied amount.

SQL Query

• Case 1: Unapplied Amount ≥ Invoiced Amount

  • Pay the full invoiced amount from unapplied_amount.

  • Insert a record into the invoices_payment table with:

    • invoice_id

    • wallet_transaction_id (from the latest wallet transaction)

    • amount_paid (full invoiced amount)

• Case 2: Unapplied Amount < Invoiced Amount (Partial Payment)

  • Pay the entire unapplied_amount from the balance.

  • Insert a record into the invoices_payment table with:

    • invoice_id

    • wallet_transaction_id

    • amount_paid (equal to unapplied_amount)

• Case 3: Unapplied Amount = 0 (No Payment Available)

  • No payment entry is made in the invoices_payment table.

Step 7: Update status in Invoices table

After recording the payment in the invoices_payment table, update the invoice_status in the Invoices table based on the amount paid.

Step 8: Update unapplied_amount in wallet_transaction table

Update the unapplied amount in wallet_transaction table as per the amount_paid in step-5

Step 9: Update the invoice status in zoho

Finally, we will update the invoice status in zoho. This is done by calling a Create Payment API, based on the amount we recorded status will be updated in zoho. For e.g If full amount is recorded then status will change to ‘paid', in case of partial amount recorded it will change to 'partiallly_paid’.

ZOHO API Endpoint:

Sample Request Body:

3. Automated Invoice Payment on Wallet Recharge

To automate invoice payments when a brand's wallet is recharged:

• Implement a Pub/Sub Mechanism:

  • A Pub/Sub topic will be created, where a message is published as soon as the wallet is recharged.

  • A subscriber API will listen to this topic and process the payment updates.

Subscriber API Workflow:

• Retrieve the latest unapplied_amount from the wallet_transaction table by considering the most recent wallet_transaction_id.

• Fetch the list of unpaid or partially paid invoices from the invoice table.

• Process the invoices one by one, prioritizing the oldest invoice first.

• For each invoice:

• Case 1: Unpaid Invoice

  • If invoiced_amount < unapplied_amount, pay the full invoice amount, update its status to paid in invoice table, and record the transaction in invoices_payment, also update the unapplied amount and process next invoice.

  • If invoiced_amount > unapplied_amount, pay the available amount, mark the invoice as partially paid in invoice table, record the transaction in invoices_payment, and update unapplied_amount in wallet_transaction table and update the invoice status in zoho.

• Case 2: Partially Paid Invoice

  • Fetch the previously paid amount from invoices_payment.

  • Calculate the remaining amount to be paid (invoiced_amount - paid_amount).

  • Compare it with unapplied_amount and pay accordingly.

  • Update invoices_payment, invoices, and wallet_transaction tables.

  • Finally, update the status in Zoho using the following API:

Zoho API: